Overview
Comment:Working migration, remove categories for now
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | dev
Files: files | file ages | folders
SHA1: 916600f4fe74d98ef4c76cccce5f1794133b8b54
User & Date: bohwaz on 2020-12-12 15:36:42
Other Links: branch diff | manifest | tags
Context
2020-12-12
17:20
Refactor of Config class, using AbstractEntity in a key-value model check-in: 04b6151bfe user: bohwaz tags: dev
15:36
Working migration, remove categories for now check-in: 916600f4fe user: bohwaz tags: dev
2020-12-11
01:11
Some kind of progress, or not check-in: cd88e798f2 user: bohwaz tags: dev
Changes

Modified src/include/data/1.1.0_migration.sql from [8c30f24db3] to [6938873928].



















1
2
3
4
5
6
7
8


9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62












63
64



65

66




67

68
69
70
71
72
73
74
75
76
77


















-- Copy wiki pages content
CREATE TEMP TABLE wiki_as_files (hash, size, content, name, path, uri, parent, created, modified, author_id, encrypted, content_id);

INSERT INTO wiki_as_files
	SELECT
		sha1(contenu), LENGTH(contenu), contenu,
		uri || '.skriv', NULL, uri, parent,
		date_creation, date_modification, id_auteur, chiffrement, NULL


	FROM wiki_pages p
	INNER JOIN wiki_revisions r ON r.id_page = p.id AND r.revision = p.revision;

-- Build back path, up to ten levels
UPDATE wiki_as_files waf SET
	path = (SELECT uri FROM wiki_as_files WHERE id = waf.parent) || '/' || path,
	parent = (SELECT parent FROM wiki_as_files WHERE id = waf.parent)
	WHERE parent > 0;

-- Would probably be better with a recursive loop but hey, it works like that
UPDATE wiki_as_files waf SET
	path = (SELECT uri FROM wiki_as_files WHERE id = waf.parent) || '/' || path,
	parent = (SELECT parent FROM wiki_as_files WHERE id = waf.parent)
	WHERE parent > 0;
UPDATE wiki_as_files waf SET
	path = (SELECT uri FROM wiki_as_files WHERE id = waf.parent) || '/' || path,
	parent = (SELECT parent FROM wiki_as_files WHERE id = waf.parent)
	WHERE parent > 0;
UPDATE wiki_as_files waf SET
	path = (SELECT uri FROM wiki_as_files WHERE id = waf.parent) || '/' || path,
	parent = (SELECT parent FROM wiki_as_files WHERE id = waf.parent)
	WHERE parent > 0;
UPDATE wiki_as_files waf SET
	path = (SELECT uri FROM wiki_as_files WHERE id = waf.parent) || '/' || path,
	parent = (SELECT parent FROM wiki_as_files WHERE id = waf.parent)
	WHERE parent > 0;
UPDATE wiki_as_files waf SET
	path = (SELECT uri FROM wiki_as_files WHERE id = waf.parent) || '/' || path,
	parent = (SELECT parent FROM wiki_as_files WHERE id = waf.parent)
	WHERE parent > 0;
UPDATE wiki_as_files waf SET
	path = (SELECT uri FROM wiki_as_files WHERE id = waf.parent) || '/' || path,
	parent = (SELECT parent FROM wiki_as_files WHERE id = waf.parent)
	WHERE parent > 0;
UPDATE wiki_as_files waf SET
	path = (SELECT uri FROM wiki_as_files WHERE id = waf.parent) || '/' || path,
	parent = (SELECT parent FROM wiki_as_files WHERE id = waf.parent)
	WHERE parent > 0;
UPDATE wiki_as_files waf SET
	path = (SELECT uri FROM wiki_as_files WHERE id = waf.parent) || '/' || path,
	parent = (SELECT parent FROM wiki_as_files WHERE id = waf.parent)
	WHERE parent > 0;
UPDATE wiki_as_files waf SET
	path = (SELECT uri FROM wiki_as_files WHERE id = waf.parent) || '/' || path,
	parent = (SELECT parent FROM wiki_as_files WHERE id = waf.parent)
	WHERE parent > 0;
UPDATE wiki_as_files waf SET
	path = (SELECT uri FROM wiki_as_files WHERE id = waf.parent) || '/' || path,
	parent = (SELECT parent FROM wiki_as_files WHERE id = waf.parent)
	WHERE parent > 0;

INSERT INTO files_contents (hash, size, content) SELECT hash, size, content FROM wiki_as_files;
UPDATE wiki_as_files SET content_id = (SELECT fc.id FROM files_contents fc WHERE fc.hash = wiki_as_files.hash);













INSERT INTO files_search (id, content) SELECT (content_id, content) FROM wiki_as_files;




INSERT INTO files (path, name, type, created, modified, content_id, author_id)

	SELECT path, name, CASE WHEN encrypted THEN 'text/encrypted' ELSE 'text/skriv' END,




	created, modified, content_id, author_id FROM wiki_as_files;


DROP TRIGGER wiki_recherche_delete;
DROP TRIGGER wiki_recherche_update;
DROP TRIGGER wiki_recherche_contenu_insert;
DROP TRIGGER wiki_recherche_contenu_chiffre;

DROP TABLE wiki_recherche;

DROP TABLE wiki_pages;
DROP TABLE wiki_revisions;
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

|



|
|
|
>
>



<
|
<
<
<

<
<
<
<
|
|
|
|
|
|
<
<
|
<
<
<
|
<
<
|
|
<
<
<
|
<
<
<
|
<
<
|
|
<
<
|
<
<
<
<
<

|


>
>
>
>
>
>
>
>
>
>
>
>
|

>
>
>
|
>
|
>
>
>
>
|
>










1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

32



33




34
35
36
37
38
39


40



41


42
43



44



45


46
47


48





49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
ALTER TABLE membres_categories RENAME TO membres_categories_old;

.read 1.1.0_schema.sql

INSERT INTO membres_categories
	SELECT id, nom,
		droit_wiki, -- droit_web
		droit_wiki, -- droit_documents
		droit_membres,
		droit_compta,
		droit_inscription,
		droit_connexion,
		droit_config,
		cacher
	FROM membres_categories_old;

DROP TABLE membres_categories_old;

-- Copy wiki pages content
CREATE TEMP TABLE wiki_as_files (old_id, new_id, hash, size, content, name, title, uri, old_parent, new_parent, created, modified, author_id, encrypted, content_id, type, public);

INSERT INTO wiki_as_files
	SELECT
		id, NULL, sha1(contenu), LENGTH(contenu), contenu,
		uri || '.skriv', titre, uri, parent, NULL,
		date_creation, date_modification, id_auteur, chiffrement, NULL,
		CASE WHEN (SELECT 1 FROM wiki_pages pp WHERE pp.parent = p.id LIMIT 1) THEN 1 ELSE 2 END, -- Type, 1 = category, 2 = page
		CASE WHEN droit_lecture = -1 THEN 1 ELSE 0 END -- public
	FROM wiki_pages p
	INNER JOIN wiki_revisions r ON r.id_page = p.id AND r.revision = p.revision;


UPDATE wiki_as_files SET name = 'index.skriv' WHERE type = 1;








-- Build back path, up to ten levels
--UPDATE wiki_as_files waf SET
--	path = (SELECT uri FROM wiki_as_files WHERE id = waf.parent) || '/' || path,
--	parent = (SELECT parent FROM wiki_as_files WHERE id = waf.parent)
--	WHERE parent > 0;



-- Create private folders



INSERT INTO files_folders (id, parent_id, name, system)


	SELECT old_id, old_parent, uri, 0 FROM wiki_as_files WHERE type = 1;




-- Create web folders



INSERT INTO files_folders (id, parent_id, name, system)


	SELECT old_id + 10000, old_parent + 10000, uri, 1 FROM wiki_as_files WHERE type = 1;



UPDATE files_folders SET parent_id = (SELECT CASE WHEN f.system = 0 THEN f.id ELSE f.id + 10000 END FROM files_folders f WHERE f.id = parent_id);






INSERT INTO files_contents (hash, content) SELECT hash, content FROM wiki_as_files;
UPDATE wiki_as_files SET content_id = (SELECT fc.id FROM files_contents fc WHERE fc.hash = wiki_as_files.hash);

INSERT INTO files (hash, folder_id, name, type, created, content_id, author_id, public)
	SELECT
		hash,
		(SELECT CASE WHEN public = 0 THEN f.id ELSE f.id + 10000 END FROM files_folders f WHERE f.id = old_parent),
		name,
		CASE WHEN encrypted THEN 'text/vnd.skriv.encrypted' ELSE 'text/vnd.skriv' END,
		created,
		content_id,
		author_id,
		public
	FROM wiki_as_files;

INSERT INTO files_search (id, content) SELECT new_id, content FROM wiki_as_files WHERE encrypted = 0;

UPDATE wiki_as_files SET new_id = (SELECT id FROM files WHERE hash = wiki_as_files.hash);
UPDATE wiki_as_files SET new_parent = (SELECT new_id FROM wiki_as_files WHERE old_id = wiki_as_files.old_parent);

INSERT INTO web_pages
	SELECT new_id, new_parent, type, 1, uri, title, modified FROM wiki_as_files WHERE public = 1;

INSERT INTO files_links (id, web_page_id)
	SELECT
		id,
		id
	FROM web_pages
	WHERE status = 1;

DROP TRIGGER wiki_recherche_delete;
DROP TRIGGER wiki_recherche_update;
DROP TRIGGER wiki_recherche_contenu_insert;
DROP TRIGGER wiki_recherche_contenu_chiffre;

DROP TABLE wiki_recherche;

DROP TABLE wiki_pages;
DROP TABLE wiki_revisions;

Modified src/include/data/1.1.0_schema.sql from [e3756c8ea0] to [bd697f352a].

1
2
3
4
5
6
7
8
9
10
11
12
13

14
15
16
17
18
19
20
CREATE TABLE IF NOT EXISTS config (
-- Configuration de Garradin
    cle TEXT PRIMARY KEY NOT NULL,
    valeur TEXT
);

CREATE TABLE IF NOT EXISTS membres_categories
-- Catégories de membres
(
    id INTEGER PRIMARY KEY NOT NULL,
    nom TEXT NOT NULL,

    droit_wiki INTEGER NOT NULL DEFAULT 1,

    droit_membres INTEGER NOT NULL DEFAULT 1,
    droit_compta INTEGER NOT NULL DEFAULT 1,
    droit_inscription INTEGER NOT NULL DEFAULT 0,
    droit_connexion INTEGER NOT NULL DEFAULT 1,
    droit_config INTEGER NOT NULL DEFAULT 0,
    cacher INTEGER NOT NULL DEFAULT 0
);












|
>







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE TABLE IF NOT EXISTS config (
-- Configuration de Garradin
    cle TEXT PRIMARY KEY NOT NULL,
    valeur TEXT
);

CREATE TABLE IF NOT EXISTS membres_categories
-- Catégories de membres
(
    id INTEGER PRIMARY KEY NOT NULL,
    nom TEXT NOT NULL,

    droit_web INTEGER NOT NULL DEFAULT 1,
    droit_documents INTEGER NOT NULL DEFAULT 1,
    droit_membres INTEGER NOT NULL DEFAULT 1,
    droit_compta INTEGER NOT NULL DEFAULT 1,
    droit_inscription INTEGER NOT NULL DEFAULT 0,
    droit_connexion INTEGER NOT NULL DEFAULT 1,
    droit_config INTEGER NOT NULL DEFAULT 0,
    cacher INTEGER NOT NULL DEFAULT 0
);
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277



278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304

305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329

330

331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357

---------- FILES ----------------

CREATE TABLE IF NOT EXISTS files
-- Files metadata
(
    id INTEGER NOT NULL PRIMARY KEY,
    folder_id INTEGER NOT NULL REFERENCES files_folders,
    name TEXT NOT NULL, -- file name (eg. image1234.jpeg)
    type TEXT NULL, -- MIME type
    image INTEGER NOT NULL DEFAULT 0, -- 1 = image reconnue
    public INTEGER NOT NULL DEFAULT 0,
    size INTEGER NOT NULL DEFAULT 0,
    hash TEXT NOT NULL, -- Hash SHA1 du contenu du fichier

    storage TEXT NULL, -- Storage medium, NULL means stored in content BLOB
    storage_path TEXT NULL,

    created TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP CHECK (datetime(created) IS NOT NULL AND datetime(created) = created),

    author_id INTEGER NULL REFERENCES membres (id) ON DELETE SET NULL



);

CREATE INDEX IF NOT EXISTS files_path ON files (path);
CREATE INDEX IF NOT EXISTS files_date ON files (datetime);
CREATE INDEX IF NOT EXISTS files_hash ON files (hash);

CREATE TABLE IF NOT EXISTS files_contents
-- Contenu des fichiers
(
    id INTEGER NOT NULL PRIMARY KEY,
    hash TEXT NOT NULL, -- Hash SHA1 du contenu du fichier
    content BLOB NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS files_contents_hash ON files_contents (hash);

CREATE TABLE IF NOT EXISTS files_folders
(
    id INTEGER NOT NULL PRIMARY KEY,
    parent_id INTEGER NULL REFERENCES files_folders(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    system INTEGER NOT NULL DEFAULT 0
);

CREATE VIRTUAL TABLE IF NOT EXISTS files_search USING fts4
-- Search inside files content
(

    id INT PRIMARY KEY NOT NULL REFERENCES files_contents(id),
    title TEXT NULL,
    content TEXT NOT NULL -- Text content
);

CREATE TABLE IF NOT EXISTS files_links
-- This references use of a file outside of the documents module
-- One file can only be linked to one thing
(
    id INTEGER NOT NULL PRIMARY KEY REFERENCES fichiers (id) ON DELETE CASCADE,
    file_id INTEGER NULL REFERENCES fichiers (id) ON DELETE CASCADE,
    user_id INTEGER NULL REFERENCES membres (id) ON DELETE CASCADE,
    transaction_id INTEGER NULL REFERENCES acc_transactions (id) ON DELETE CASCADE,
    config TEXT NULL REFERENCES config (valeur) ON DELETE CASCADE,
    web_page_id INTEGER NULL REFERENCES web_pages (id) ON DELETE CASCADE,
    web_category_id INTEGER NULL REFERENCES web_categories (id) ON DELETE CASCADE,
    -- Make sure that only one is filled
    CHECK ((user_id IS NULL) + (transaction_id IS NULL) + (config IS NULL) + (web_page_id IS NULL) + (web_category_id IS NULL) + (file_id IS NULL) = 1)
);

CREATE UNIQUE INDEX files_links_unique ON files_links (file_id, user_id, transaction_id, config, web_pages, web_category_id);

CREATE TABLE IF NOT EXISTS web_pages
(
    id INTEGER NOT NULL PRIMARY KEY REFERENCES files(id),

    category_id INTEGER NOT NULL REFERENCES web_categories(id) ON DELETE CASCADE,

    title TEXT NOT NULL,
    draft INTEGER NOT NULL DEFAULT 0,
    modified TEXT NULL CHECK (datetime(modified) IS NULL OR datetime(modified) = modified)
);

CREATE TABLE IF NOT EXISTS web_categories
(
    id INTEGER NOT NULL PRIMARY KEY REFERENCES files(id),
    parent_id INTEGER NOT NULL REFERENCES web_categories(id) ON DELETE CASCADE,
    title TEXT NOT NULL
);

CREATE TRIGGER IF NOT EXISTS web_page_insert AFTER INSERT ON web_pages
    BEGIN
        UPDATE files SET public = NEW.draft WHERE id = NEW.id;
    END;

CREATE TRIGGER IF NOT EXISTS web_page_update AFTER UPDATE ON web_pages
    BEGIN
        UPDATE files SET public = NEW.draft WHERE id = NEW.id;
    END;

-- FIXME: rename to english
CREATE TABLE IF NOT EXISTS recherches
-- Recherches enregistrées
(
    id INTEGER NOT NULL PRIMARY KEY,







|












|
>
>
>


<
|



|


|
















>
|








|
|


|

<

|


|




>
|
>
|
|



|
<
<
<
<
<



|


|

|







258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283

284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323

324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341





342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357

---------- FILES ----------------

CREATE TABLE IF NOT EXISTS files
-- Files metadata
(
    id INTEGER NOT NULL PRIMARY KEY,
    folder_id INTEGER NULL REFERENCES files_folders,
    name TEXT NOT NULL, -- file name (eg. image1234.jpeg)
    type TEXT NULL, -- MIME type
    image INTEGER NOT NULL DEFAULT 0, -- 1 = image reconnue
    public INTEGER NOT NULL DEFAULT 0,
    size INTEGER NOT NULL DEFAULT 0,
    hash TEXT NOT NULL, -- Hash SHA1 du contenu du fichier

    storage TEXT NULL, -- Storage medium, NULL means stored in content BLOB
    storage_path TEXT NULL,

    created TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP CHECK (datetime(created) IS NOT NULL AND datetime(created) = created),

    author_id INTEGER NULL REFERENCES membres (id) ON DELETE SET NULL,
    content_id INTEGER NULL REFERENCES files_contents (id) ON DELETE SET NULL,

    CHECK (storage IS NOT NULL OR content_id IS NOT NULL)
);


CREATE INDEX IF NOT EXISTS files_date ON files (created);
CREATE INDEX IF NOT EXISTS files_hash ON files (hash);

CREATE TABLE IF NOT EXISTS files_contents
-- Files contents (if storage backend is SQLite)
(
    id INTEGER NOT NULL PRIMARY KEY,
    hash TEXT NOT NULL,
    content BLOB NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS files_contents_hash ON files_contents (hash);

CREATE TABLE IF NOT EXISTS files_folders
(
    id INTEGER NOT NULL PRIMARY KEY,
    parent_id INTEGER NULL REFERENCES files_folders(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    system INTEGER NOT NULL DEFAULT 0
);

CREATE VIRTUAL TABLE IF NOT EXISTS files_search USING fts4
-- Search inside files content
(
    tokenize=unicode61, -- Available from SQLITE 3.7.13 (2012)
    id INT PRIMARY KEY NOT NULL REFERENCES files(id),
    title TEXT NULL,
    content TEXT NOT NULL -- Text content
);

CREATE TABLE IF NOT EXISTS files_links
-- This references use of a file outside of the documents module
-- One file can only be linked to one thing
(
    id INTEGER NOT NULL PRIMARY KEY REFERENCES files (id) ON DELETE CASCADE,
    file_id INTEGER NULL REFERENCES files (id) ON DELETE CASCADE,
    user_id INTEGER NULL REFERENCES membres (id) ON DELETE CASCADE,
    transaction_id INTEGER NULL REFERENCES acc_transactions (id) ON DELETE CASCADE,
    config TEXT NULL REFERENCES config (cle) ON DELETE CASCADE,
    web_page_id INTEGER NULL REFERENCES web_pages (id) ON DELETE CASCADE,

    -- Make sure that only one is filled
    CHECK ((file_id IS NOT NULL) + (user_id IS NOT NULL) + (transaction_id IS NOT NULL) + (config IS NOT NULL) + (web_page_id IS NOT NULL) = 1)
);

CREATE UNIQUE INDEX files_links_unique ON files_links (file_id, user_id, transaction_id, config, web_page_id);

CREATE TABLE IF NOT EXISTS web_pages
(
    id INTEGER NOT NULL PRIMARY KEY REFERENCES files(id),
    parent_id INTEGER NULL REFERENCES web_pages(id) ON DELETE SET NULL,
    type INTEGER NOT NULL, -- 1 = Category, 2 = Page
    status INTEGER NOT NULL DEFAULT 0, -- 0 = draft, 1 = online
    uri TEXT NOT NULL,
    title TEXT NOT NULL,
    modified TEXT NULL CHECK (datetime(modified) IS NULL OR datetime(modified) = modified)
);

CREATE UNIQUE INDEX web_pages_uri ON web_pages (uri);






CREATE TRIGGER IF NOT EXISTS web_page_insert AFTER INSERT ON web_pages
    BEGIN
        UPDATE files SET public = CASE WHEN NEW.status = 1 THEN 1 ELSE 0 END WHERE id = NEW.id;
    END;

CREATE TRIGGER IF NOT EXISTS web_page_update AFTER UPDATE OF status ON web_pages
    BEGIN
        UPDATE files SET public = CASE WHEN NEW.status = 1 THEN 1 ELSE 0 END WHERE id = NEW.id;
    END;

-- FIXME: rename to english
CREATE TABLE IF NOT EXISTS recherches
-- Recherches enregistrées
(
    id INTEGER NOT NULL PRIMARY KEY,

Modified src/include/lib/Garradin/Entities/Files/File.php from [f6bcbadbb9] to [9ccf80c988].

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
use Garradin\UserException;

class File extends Entity
{
	const TABLE = 'files';

	protected $id;
	protected $path;
	protected $name;
	protected $type;
	protected $image;
	protected $size;
	protected $hash;

	protected $storage;
	protected $storage_path;

	protected $created;
	protected $modified;

	protected $author_id;

	protected $_types = [
		'id'           => 'int',
		'path'         => 'string',
		'name'         => 'string',
		'type'         => '?string',
		'image'        => 'int',
		'size'         => 'int',
		'hash'         => 'string',
		'storage'      => '?string',
		'storage_path' => '?string',







|
















|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
use Garradin\UserException;

class File extends Entity
{
	const TABLE = 'files';

	protected $id;
	protected $folder_id;
	protected $name;
	protected $type;
	protected $image;
	protected $size;
	protected $hash;

	protected $storage;
	protected $storage_path;

	protected $created;
	protected $modified;

	protected $author_id;

	protected $_types = [
		'id'           => 'int',
		'folder_id'    => '?int',
		'name'         => 'string',
		'type'         => '?string',
		'image'        => 'int',
		'size'         => 'int',
		'hash'         => 'string',
		'storage'      => '?string',
		'storage_path' => '?string',
70
71
72
73
74
75
76






















77
78
79
80
81
82
83
		$return = parent::delete();

		// clean up thumbs
		foreach (self::ALLOWED_THUMB_SIZES as $size)
		{
			Static_Cache::remove(sprintf(self::THUMB_CACHE_ID, $this->id(), $size));
		}























		return $return;
	}

	static protected function store(?string $path, string $name, string $source_path = null, $source_content = null): self
	{
		assert($path || $content);







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
		$return = parent::delete();

		// clean up thumbs
		foreach (self::ALLOWED_THUMB_SIZES as $size)
		{
			Static_Cache::remove(sprintf(self::THUMB_CACHE_ID, $this->id(), $size));
		}

		return $return;
	}

	public function save(): bool
	{
		$return = parent::save();

		// Store content in search table
		if ($return && substr($this->type, 0, 5) == 'text/') {
			$content = Files::callStorage('fetch', $this);

			if ($this->type == 'text/html') {
				$content = strip_tags($content);
			}

			if ($this->type == 'text/vnd.skriv.encrypted') {
				$content = 'Contenu chiffré';
			}

			$db->preparedQuery('INSERT OR REPLACE INTO files_search (id, content) VALUES (?, ?);', $this->id(), $content);
		}

		return $return;
	}

	static protected function store(?string $path, string $name, string $source_path = null, $source_content = null): self
	{
		assert($path || $content);

Deleted src/include/lib/Garradin/Entities/Web/Category.php version [c921b48f36].

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
<?php

namespace Garradin\Entities\Web;

use Garradin\Entity;
use Garradin\UserException;

use KD2\DB\EntityManager;

class Category extends Entity
{
	protected $id;
	protected $parent_id;
	protected $title;

	protected $_types = [
		'id'        => 'int',
		'parent_id' => 'int',
		'title'     => 'string',
	];

	protected $_file;

	public function file(): File
	{
		if (null === $this->_file) {
			$this->_file = EM::findOneById(File::class, $this->id);
		}

		return $this->_file;
	}

	public function save()
	{
		$file = $this->file();
		$file->save();

		$this->id($file->id());

		parent::save();
	}
}
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<




















































































Modified src/include/lib/Garradin/Entities/Web/Page.php from [45f124fbff] to [ab2d08746c].

1
2
3
4
5
6
7
8
9
10
11
12

13
14
15
16
17
18
19
20

21
22
23
24
25
26



27
28
29
30
31
32
33
<?php

namespace Garradin\Entities\Web;

use Garradin\Entity;
use Garradin\UserException;

use KD2\DB\EntityManager;

class Page extends Entity
{
	protected $id;

	protected $category_id;
	protected $title;
	protected $draft;
	protected $modified;

	protected $_types = [
		'id'          => 'int',
		'category_id' => 'int',

		'title'       => 'string',
		'draft'       => 'int',
		'modified'    => 'DateTime',
	];

	protected $_file;




	public function file(): File
	{
		if (null === $this->_file) {
			$this->_file = EM::findOneById(File::class, $this->id);
		}













>
|





|
|
>
|
|
|



>
>
>







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
<?php

namespace Garradin\Entities\Web;

use Garradin\Entity;
use Garradin\UserException;

use KD2\DB\EntityManager;

class Page extends Entity
{
	protected $id;
	protected $parent_id;
	protected $status;
	protected $title;
	protected $draft;
	protected $modified;

	protected $_types = [
		'id'        => 'int',
		'parent_id' => 'int',
		'status'    => 'int',
		'title'     => 'string',
		'draft'     => 'int',
		'modified'  => 'DateTime',
	];

	protected $_file;

	const STATUS_ONLINE = 1;
	const STATUS_DRAFT = 0;

	public function file(): File
	{
		if (null === $this->_file) {
			$this->_file = EM::findOneById(File::class, $this->id);
		}

Modified src/include/lib/Garradin/Membres/Session.php from [db8cb5f1d1] to [5a5c51b77f].

87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
	protected function getUserDataForSession($id)
	{
		// Mettre à jour la date de connexion
		$this->db->preparedQuery('UPDATE membres SET date_connexion = datetime() WHERE id = ?;', [$id]);
		$config = Config::getInstance();

		return $this->db->first('SELECT m.*, m.'.$config->get('champ_identite').' AS identite,
			c.droit_connexion, c.droit_wiki, 
			c.droit_membres, c.droit_compta, c.droit_config, c.droit_membres
			FROM membres AS m
			INNER JOIN membres_categories AS c ON m.id_categorie = c.id
			WHERE m.id = ? LIMIT 1;', $id);
	}

	protected function storeRememberMeSelector($selector, $hash, $expiry, $user_id)







|







87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
	protected function getUserDataForSession($id)
	{
		// Mettre à jour la date de connexion
		$this->db->preparedQuery('UPDATE membres SET date_connexion = datetime() WHERE id = ?;', [$id]);
		$config = Config::getInstance();

		return $this->db->first('SELECT m.*, m.'.$config->get('champ_identite').' AS identite,
			c.droit_connexion, c.droit_web, c.droit_documents,
			c.droit_membres, c.droit_compta, c.droit_config, c.droit_membres
			FROM membres AS m
			INNER JOIN membres_categories AS c ON m.id_categorie = c.id
			WHERE m.id = ? LIMIT 1;', $id);
	}

	protected function storeRememberMeSelector($selector, $hash, $expiry, $user_id)

Modified src/include/lib/Garradin/Upgrade.php from [b4ea8189bd] to [4f89931cf7].

100
101
102
103
104
105
106









107
108
109
110
111
112
113
			if (version_compare($v, '1.0.0-beta1', '>=') && version_compare($v, '1.0.0-rc11', '<'))
			{
				// Missing trigger
				$db->beginSchemaUpdate();
				$db->import(ROOT . '/include/data/1.0.0_schema.sql');
				$db->commitSchemaUpdate();
			}










			// Vérification de la cohérence des clés étrangères
			$db->foreignKeyCheck();

			Utils::clearCaches();

			$config->setVersion(garradin_version());







>
>
>
>
>
>
>
>
>







100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
			if (version_compare($v, '1.0.0-beta1', '>=') && version_compare($v, '1.0.0-rc11', '<'))
			{
				// Missing trigger
				$db->beginSchemaUpdate();
				$db->import(ROOT . '/include/data/1.0.0_schema.sql');
				$db->commitSchemaUpdate();
			}

			if (version_compare($v, '1.1.0', '<='))
			{
				// Missing trigger
				$db->beginSchemaUpdate();
				$db->createFunction('sha1', 'sha1');
				$db->import(ROOT . '/include/data/1.1.0_migration.sql');
				$db->commitSchemaUpdate();
			}

			// Vérification de la cohérence des clés étrangères
			$db->foreignKeyCheck();

			Utils::clearCaches();

			$config->setVersion(garradin_version());

Added src/include/lib/Garradin/Web.php version [eebca41282].































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
<?php

namespace Garradin;

class Web
{
    static public function search(string $search, bool $online_only = true): array
    {
        if (strlen($search) > 100) {
            throw new UserException('Recherche trop longue : maximum 100 caractères');
        }

        $where = '';

        if ($online_only) {
        	$where = sprintf('p.status = %d AND ', Page::STATUS_ONLINE);
        }

        $query = sprintf('SELECT
            p.*,
            snippet(files_search, \'<b>\', \'</b>\', \'...\', -1, -50) AS snippet,
            rank(matchinfo(files_search), 0, 1.0, 1.0) AS points
            FROM files_search AS s
            INNER JOIN web_pages AS p USING (id)
            WHERE %s files_search MATCH ?
            ORDER BY points DESC
            LIMIT 0,50;', $where);

        return DB::getInstance()->get($query, $search);
    }
}

Modified src/include/lib/Garradin/Wiki.php from [6ab9be5a8f] to [ebb7e50c24].

196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312

    public function getTitle($id)
    {
        $db = DB::getInstance();
        return $db->firstColumn('SELECT titre FROM wiki_pages WHERE id = ? LIMIT 1;', (int)$id);
    }

    public function getRevision($id, $rev)
    {
        $db = DB::getInstance();
        $champ_id = Config::getInstance()->get('champ_identite');

        return $db->first('SELECT r.revision, r.modification, r.id_auteur, r.contenu,
            strftime(\'%s\', r.date) AS date, LENGTH(r.contenu) AS taille, m.'.$champ_id.' AS nom_auteur,
            r.chiffrement
            FROM wiki_revisions AS r LEFT JOIN membres AS m ON m.id = r.id_auteur
            WHERE r.id_page = ? AND revision = ? LIMIT 1;', (int) $id, (int) $rev);
    }

    public function listRevisions($id)
    {
        $db = DB::getInstance();
        $champ_id = Config::getInstance()->get('champ_identite');

        // FIXME pagination au lieu de bloquer à 1000
        return $db->get('SELECT r.revision, r.modification, r.id_auteur,
            strftime(\'%s\', r.date) AS date, LENGTH(r.contenu) AS taille, m.'.$champ_id.' AS nom_auteur,
            LENGTH(r.contenu) - (SELECT LENGTH(contenu) FROM wiki_revisions WHERE id_page = r.id_page AND revision < r.revision ORDER BY revision DESC LIMIT 1)
            AS diff_taille, r.chiffrement
            FROM wiki_revisions AS r LEFT JOIN membres AS m ON m.id = r.id_auteur
            WHERE r.id_page = ? ORDER BY r.revision DESC LIMIT 1000;', (int) $id);
    }

    public function editRevision($id, $revision_edition = 0, $data)
    {
        $db = DB::getInstance();

        $revision = $db->firstColumn('SELECT revision FROM wiki_pages WHERE id = ?;', (int)$id);

        // ?! L'ID fournit ne correspond à rien ?
        if ($revision === false)
        {
            throw new \RuntimeException('La page demandée n\'existe pas.');
        }

        // Pas de révision
        if ($revision == 0 && !trim($data['contenu']))
        {
            return true;
        }

        // Il faut obligatoirement fournir un ID d'auteur
        if (empty($data['id_auteur']) && $data['id_auteur'] !== null)
        {
            throw new \BadMethodCallException('Aucun ID auteur de fourni.');
        }

        $contenu = $db->firstColumn('SELECT contenu FROM wiki_revisions WHERE revision = ? AND id_page = ?;', (int)$revision, (int)$id);

        // Pas de changement au contenu, pas la peine d'enregistrer une nouvelle révision
        if (trim($contenu) == trim($data['contenu']))
        {
            return true;
        }

        // Révision sur laquelle est basée la nouvelle révision
        // utilisé pour vérifier que le contenu n'a pas été modifié depuis qu'on
        // a chargé la page d'édition
        if ($revision > $revision_edition)
        {
            throw new UserException('La page a été modifiée depuis le début de votre modification.');
        }

        if (empty($data['chiffrement']))
            $data['chiffrement'] = 0;

        if (!isset($data['modification']) || !trim($data['modification']))
            $data['modification'] = null;

        // Incrémentons le numéro de révision
        $revision++;

        $data['id_page'] = $id;
        $data['revision'] = $revision;

        $db->insert('wiki_revisions', $data);
        $db->update('wiki_pages', [
            'revision'          =>  $revision,
            'date_modification' =>  gmdate('Y-m-d H:i:s'),
        ], 'id = :id', ['id' => (int)$id]);

        return true;
    }

    public function search($search)
    {
        if (strlen($search) > 100) {
            throw new UserException('Recherche trop longue : maximum 100 caractères');
        }

        $query = sprintf('SELECT
            p.uri, r.*, snippet(wiki_recherche, \'<b>\', \'</b>\', \'...\', -1, -50) AS snippet,
            rank(matchinfo(wiki_recherche), 0, 1.0, 1.0) AS points
            FROM wiki_recherche AS r INNER JOIN wiki_pages AS p ON p.id = r.id
            WHERE %s AND wiki_recherche MATCH ?
            ORDER BY points DESC LIMIT 0,50;', $this->_getLectureClause('p.'));

        return DB::getInstance()->get($query, $search);
    }

    public function setRestrictionCategorie($id, $droit_wiki)
    {
        $this->restriction_categorie = $id;
        $this->restriction_droit = $droit_wiki;
        return true;
    }








<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







196
197
198
199
200
201
202







































































































203
204
205
206
207
208
209

    public function getTitle($id)
    {
        $db = DB::getInstance();
        return $db->firstColumn('SELECT titre FROM wiki_pages WHERE id = ? LIMIT 1;', (int)$id);
    }








































































































    public function setRestrictionCategorie($id, $droit_wiki)
    {
        $this->restriction_categorie = $id;
        $this->restriction_droit = $droit_wiki;
        return true;
    }

Modified src/templates/admin/_head.tpl from [d27c715ba4] to [6be9088bcc].

83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
                <li class="{if $current == 'acc/years'} current{/if}"><a href="{$admin_url}acc/years/">Exercices &amp; rapports</a></li>
            {if $session->canAccess('compta', Membres::DROIT_ECRITURE)}
                <li class="{if $current == 'acc/charts'} current{/if}"><a href="{$admin_url}acc/charts/">Plans comptables</a></li>
            {/if}
            </ul>
            </li>
        {/if}
        {if $session->canAccess('wiki', Membres::DROIT_ACCES)}
            <li class="wiki{if $current == 'wiki'} current{elseif $current_parent == 'wiki'} current_parent{/if}"><a href="{$admin_url}wiki/"><b class="icn">✎</b><i> Wiki</i></a>
            <ul>
                <li class="wiki list{if $current == 'wiki/recent'} current{/if}"><a href="{$admin_url}wiki/recent.php">Dernières modifications</a>
                <li class="wiki search{if $current == 'wiki/chercher'} current{/if}"><a href="{$admin_url}wiki/chercher.php">Recherche</a>
            </ul>
            </li>
        {/if}
        {if $session->canAccess('config', Membres::DROIT_ADMIN)}
            <li class="main config{if $current == 'config'} current{elseif $current_parent == 'config'} current_parent{/if}"><a href="{$admin_url}config/"><b class="icn">☸</b><i> Configuration</i></a>
        {/if}
        <li class="{if $current == 'mes_infos'} current{elseif $current_parent == 'mes_infos'} current_parent{/if}">
            <a href="{$admin_url}mes_infos.php"><b class="icn">👤</b><i> Mes infos personnelles</i></a>







|
|
<
<
<
<







83
84
85
86
87
88
89
90
91




92
93
94
95
96
97
98
                <li class="{if $current == 'acc/years'} current{/if}"><a href="{$admin_url}acc/years/">Exercices &amp; rapports</a></li>
            {if $session->canAccess('compta', Membres::DROIT_ECRITURE)}
                <li class="{if $current == 'acc/charts'} current{/if}"><a href="{$admin_url}acc/charts/">Plans comptables</a></li>
            {/if}
            </ul>
            </li>
        {/if}
        {if $session->canAccess('web', Membres::DROIT_ACCES)}
            <li class="{if $current == 'web'} current{elseif $current_parent == 'web'} current_parent{/if}"><a href="{$admin_url}web/"><b class="icn">✎</b><i> Site web</i></a>




            </li>
        {/if}
        {if $session->canAccess('config', Membres::DROIT_ADMIN)}
            <li class="main config{if $current == 'config'} current{elseif $current_parent == 'config'} current_parent{/if}"><a href="{$admin_url}config/"><b class="icn">☸</b><i> Configuration</i></a>
        {/if}
        <li class="{if $current == 'mes_infos'} current{elseif $current_parent == 'mes_infos'} current_parent{/if}">
            <a href="{$admin_url}mes_infos.php"><b class="icn">👤</b><i> Mes infos personnelles</i></a>