Re: [darktable-user] Easy way to remove imported presets?
Am 30.09.2018 um 14:53 schrieb J Albrecht: Can’t all of this command line gobley-gook nevertheless be done in DB Browser for SQLite? In order to “kill the orphans” (ugghh) one must simply hit a couple of GUI buttons to conveniently delete the necessary rows in both tables, right? If you use the GUI, you will get no orphaned rows. The SQL in my previous Mail will not kill the orphanes but avoid getting orphanes when using the DB-Browser. To kill the orphanes after deleting in table "styles" with DB-Browser use SQL: DELETE FROM style_items WHERE NOT EXISTS (SELECT id FROM styles s WHERE s.id = styleid); -- Frank darktable user mailing list to unsubscribe send a mail to darktable-user+unsubscr...@lists.darktable.org
Re: [darktable-user] Easy way to remove imported presets?
Can’t all of this command line gobley-gook nevertheless be done in DB Browser for SQLite? In order to “kill the orphans” (ugghh) one must simply hit a couple of GUI buttons to conveniently delete the necessary rows in both tables, right? > On 30 Sep 2018, at 13:01, Frank J. wrote: > > 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 > darktable user mailing list to unsubscribe send a mail to darktable-user+unsubscr...@lists.darktable.org
Re: [darktable-user] Easy way to remove imported presets?
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
