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. <newslet...@fotodrachen.de> 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
