Overview
Comment: | Undo log des requêtes SQL |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | dev |
Files: | files | file ages | folders |
SHA1: |
4f901f656b1045bcdb0630ec536c915d |
User & Date: | bohwaz on 2019-01-09 21:14:13 |
Other Links: | branch diff | manifest | tags |
Context
2019-01-17
| ||
09:57 | Utilisation du compte 890 (au lieu de NULL) comme compte de contre-balance pour les reports à nouveau check-in: e366cee518 user: bohwaz tags: dev | |
2019-01-09
| ||
21:14 | Undo log des requêtes SQL check-in: 4f901f656b user: bohwaz tags: dev | |
11:04 | La mise à jour doit se faire dans un try catch, permettant de nettoyer et revenir en arrière en cas de souci check-in: 24aa10392c user: bohwaz tags: dev | |
Changes
Modified src/include/lib/Garradin/DB.php from [41264a6c51] to [dadf975219].
︙ | ︙ | |||
44 45 46 47 48 49 50 | } public function connect() { if (parent::connect()) { // Activer les contraintes des foreign keys | | | | 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 | } public function connect() { if (parent::connect()) { // Activer les contraintes des foreign keys $this->exec('PRAGMA foreign_keys = ON;'); // 10 secondes $this->db->busyTimeout(10 * 1000); $this->exec('PRAGMA journal_mode = TRUNCATE;'); $this->db->createFunction('transliterate_to_ascii', ['Garradin\Utils', 'transliterateToAscii']); } } public function close() { |
︙ | ︙ | |||
76 77 78 79 80 81 82 | $dir = dirname($file); $sql = preg_replace_callback('/^\.read (.+\.sql)$/m', function ($match) use ($dir) { return file_get_contents($dir . DIRECTORY_SEPARATOR . $match[1]) . "\n"; }, $sql); | | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 | $dir = dirname($file); $sql = preg_replace_callback('/^\.read (.+\.sql)$/m', function ($match) use ($dir) { return file_get_contents($dir . DIRECTORY_SEPARATOR . $match[1]) . "\n"; }, $sql); return $this->exec($sql); } public function deleteUndoTriggers() { $triggers = $this->getAssoc('SELECT name, name FROM sqlite_master WHERE type = \'trigger\' AND name LIKE \'!_%!_log!__t\' ESCAPE \'!\';'); foreach ($triggers as $trigger) { $this->exec(sprintf('DROP TRIGGER %s;', $this->quoteIdentifier($trigger))); } } public function createUndoTriggers() { $this->exec('CREATE TABLE undolog ( seq INTEGER PRIMARY KEY, sql TEXT NOT NULL, date TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, type TEXT NOT NULL, action TEXT NOT NULL );'); // List all tables except SQLite tables $tables = $this->getAssoc('SELECT name, name FROM sqlite_master WHERE type = \'table\' AND name NOT LIKE \'sqlite!_%\' ESCAPE \'!\' AND name NOT LIKE \'wiki!_recherche%\' ESCAPE \'!\' AND name != \'undolog\';'); $query = 'CREATE TRIGGER _%table_log_it AFTER INSERT ON %table BEGIN DELETE FROM undolog WHERE rowid IN (SELECT rowid FROM undolog LIMIT 500,1000); INSERT INTO undolog (type, action, sql) VALUES (\'%table\', \'I\', \'DELETE FROM %table WHERE rowid=\'||new.rowid); END; CREATE TRIGGER _%table_log_ut AFTER UPDATE ON %table BEGIN DELETE FROM undolog WHERE rowid IN (SELECT rowid FROM undolog LIMIT 500,1000); INSERT INTO undolog (type, action, sql) VALUES (\'%table\', \'U\', \'UPDATE %table SET %columns_update WHERE rowid = \'||old.rowid); END; CREATE TRIGGER _%table_log_dt BEFORE DELETE ON %table BEGIN DELETE FROM undolog WHERE rowid IN (SELECT rowid FROM undolog LIMIT 500,1000); INSERT INTO undolog (type, action, sql) VALUES (\'%table\', \'D\', \'INSERT INTO %table (rowid, %columns_list) VALUES(\'||old.rowid||\', %columns_insert)\'); END;'; foreach ($tables as $table) { $columns = $this->getAssoc(sprintf('PRAGMA table_info(%s);', $this->quoteIdentifier($table))); $columns_insert = []; $columns_update = []; foreach ($columns as &$name) { $columns_update[] = sprintf('%s = \'||quote(old.%1$s)||\'', $name); $columns_insert[] = sprintf('\'||quote(old.%s)||\'', $name); } $sql = strtr($query, [ '%table' => $table, '%columns_list' => implode(', ', $columns), '%columns_update' => implode(', ', $columns_update), '%columns_insert' => implode(', ', $columns_insert), ]); $this->exec($sql); } } } |