Am 25.09.2018 um 17:37 schrieb dt-l...@stefan-klinger.de:
...
If only some of the data is deleted from a database, it might end up
in an inconsistent state. Depending on the assumptions the
application makes about the DB's state, this may break later.
To avoid inconsistences it is possible to use functions of the database.
For this case "N items related to 1 style" I suggest to add a "foreign
key contraint" to ~/.config/darktable/data.db
SQL:
-- At first we need an index:
CREATE UNIQUE INDEX styles_idx ON styles (id);
-- Second we need a foreign-key-constraint:
-- The easy way - like in postgreSQL - does not work in SQLite:
ALTER TABLE style_items
ADD FOREIGN KEY (styleid)
REFERENCES styles (id)
ON DELETE CASCADE;
-- So we have to go the harder way:
-- https://sqlite.org/lang_altertable.html
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
-- SELECT type, sql FROM sqlite_master WHERE tbl_name='style_items';
CREATE TABLE new_style_items (
styleid INTEGER,
num INTEGER,
module INTEGER,
operation VARCHAR(256),
op_params BLOB,
enabled INTEGER,
blendop_params BLOB,
blendop_version INTEGER,
multi_priority INTEGER,
multi_name VARCHAR(256),
FOREIGN KEY(styleid) REFERENCES styles(id) ON DELETE CASCADE
);
INSERT INTO new_style_items
(styleid,num,module,operation,op_params,enabled,
blendop_params,blendop_version,multi_priority,multi_name)
SELECT styleid,num,module,operation,op_params,enabled,
blendop_params,blendop_version,multi_priority,multi_name
FROM style_items;
DROP TABLE style_items;
ALTER TABLE new_style_items RENAME TO style_items;
COMMIT TRANSACTION;
PRAGMA foreign_keys=ON;
-- SQL End
The result: Deleting a row in Table "styles" will also delete all
related items in table "style_items".
No orphaned rows will be left there.
I did not test this change whith my primary database and darktable
software, but only with a copy auf "data.db".
--
Frank
____________________________________________________________________________
darktable user mailing list
to unsubscribe send a mail to darktable-user+unsubscr...@lists.darktable.org
