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: |
916600f4fe74d98ef4c76cccce5f1794 |
User & Date: | bohwaz on 2020-12-12 15:36:42 |
Other Links: | branch diff | manifest | tags |
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 | |
Modified src/include/data/1.1.0_migration.sql from [8c30f24db3] to [6938873928].
1 | -- Copy wiki pages content | > > > > > > > > > > > > > > > > > > | | | | > > < | < < < < < < < | | | | | | < < | < < < | < < | | < < < | < < < | < < | | < < | < < < < < | > > > > > > > > > > > > | > > > | > | > > > > | > | 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 | 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, | | > | 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 | ---------- FILES ---------------- CREATE TABLE IF NOT EXISTS files -- Files metadata ( 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 | use Garradin\UserException; class File extends Entity { const TABLE = 'files'; protected $id; | | | | 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].
|
| < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < |
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 | <?php namespace Garradin\Entities\Web; use Garradin\Entity; use Garradin\UserException; use KD2\DB\EntityManager; class Page extends Entity { protected $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 | 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, | | | 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 | public function getTitle($id) { $db = DB::getInstance(); return $db->firstColumn('SELECT titre FROM wiki_pages WHERE id = ? LIMIT 1;', (int)$id); } | < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < | 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 | <li class="{if $current == 'acc/years'} current{/if}"><a href="{$admin_url}acc/years/">Exercices & 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} | | | < < < < | 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 & 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> |
︙ | ︙ |