Removing Albums & Sets - Lost Catalog File

tom1983 · 2015-11-12 04:29:51

Hi,

Following a hard drive corrupting we have lost a catalog file used to upload an album set & some albums via Publisher. I now wish to remove this set and albums within.

Is there a simple way of doing this? I'm aware I can just delete the folders via FTP but this won't remove the entries from the database.

Is it a case of editing the database manually or is there a simpler way?

Any suggestions would be appreciated.

Thanks
Tom

rod barbee · 2015-11-12 05:03:10

I believe you'll need to edit the database manually. It's not too scary
http://ttg-tips-and-tricks.barbeephoto. … -database/

Be sure to backup the database first.
but if it makes you nervous, you can always ask Ben to straighten things out for you

Ben · 2015-11-12 09:44:10

If you're starting afresh then deleting the file ttg-be/data/publisher/master.sq3 will effectively clear out references to all albums and photos.
I suggest grabbing a copy via FTP first, in case you want to restore the old albums.

tom1983 · 2015-11-13 01:19:06

Thanks for the help gents.

Rod - I'm confident editing the album table as I've done it a few times before but am concerned that there will also be entries in the photo, photo_keyword and photo_metadata tables relating to the deleted albums. I'm guessing it won't cause too many problems to leave the unused lines in there but as some of those tables are a couple of million lines it would be good if I could keep them as compact as possible.

Do you have any more information regarding the structure of the database that may help me?

Ben - It's just one set containing 12 albums that needs to be removed. We have other albums totalling 100,000s of pics so it's not practical to start from scratch. Thanks anyway.

Cheers
Tom

rod barbee · 2015-11-13 02:02:52

Do you have any more information regarding the structure of the database that may help me?

I don't. Ben probably does.

Ben · 2015-11-13 06:28:35

In a perfect world, the dependent table entries would be deleted automatically when an album was deleted. As far as I know, SQLite that ships with Apache doesn't support that feature.

Here's how the data is structured:

Albums are linked to their parent Album Sets via the parent_id, so this command returns a list of Album IDs belonging to an Album Set with id of 222222:

select id from album where parent_id=222222;

Albums have an associated set of photo items linked by album.id = photo.album_id. photos in turn have associated photo_rendition, photo_keyword and photo_metadata entries, linked by photo.id = photo_rendition.photo_id, photo_keyword.photo_id and photo_metadata.photo_id respectively.

To delete an album with id of 333333, run these command in this order (especially making sure that the metadata/keyword/rendition deletions are made before the photo deletion, and the photo deletion before the album deletion).

delete from photo_metadata where photo_id in (select id from photo where album_id=333333)

delete from photo_keyword where photo_id in (select id from photo where album_id=333333)

delete from photo_rendition where photo_id in (select id from photo where album_id=333333)

delete from photo where album_id=333333

delete from album where id=333333

To delete an album set and all of it's child albums, photos and related data, assuming that all children albums are directly underneath this album set:

delete from photo_metadata where photo_id in (select id from photo where album_id in (select id from album where parent_id=222222))

delete from photo_keyword where photo_id in (select id from photo where album_id in (select id from album where parent_id=222222))

delete from photo_rendition where photo_id in (select id from photo where album_id in (select id from album where parent_id=222222))

delete from photo where album_id in (select id from album where parent_id=222222)

delete from album where parent_id=222222

As you can probably guess these are dangerous commands, so I would make a cope of ttg-be/data/publisher/master.sq3 prior to running these.

SunFoxNC · 2015-11-14 02:18:14

Looking at the SQLite file in the latest distribution I downloaded, it appears to be a version 3.8.4 database.

Foreign keys have been supported since version 3.6.19 but are turned off by default unless you explicitly issue the foreign keys PRAGMA when you connect to the database. The foreign key reference has the ON DELETE CASCADE option when you create the table which would give you the desired behaviour of allowing you to delete the parent record and the dependent rows in the child table(s) would also be deleted as part of the same transaction.

Unfortunately, SQLite's ALTER TABLE statement doesn't appear to support adding foreign key constraints after the fact so you'd essentially have to rebuild the tables on the fly to add the foreign key constraints and ensure that they enable properly (i.e. no bad data in the child tables). And then you'd need to ensure that you issue the PRAGMA every time you open that database to ensure they're enforced.

I'm hesitant to suggest the other option which would be to create DELETE triggers on the tables but it would at least save you the migration to foreign keys but at the risk of the triggers behaving in unexpected fashions when you start cascading the delete statements from the parent. I wouldn't even consider doing something like this in Oracle, much less SQLite! The only times you'll ever see a trigger in my database will be when I'm auditing row-level changes in a table to identify who changed the row, when they did it, and what they changed.

I'm sure that SQLite was chosen because it's very lightweight and portable/self-contained but it might be worth exploring an option for the data store to be hosted in MySQL with a fallback to SQLite. The obvious advantages are a much richer feature set and TCP/IP connectivity to the remote data store supported by most hosts offering MySQL. I know that's not going to be a trivial exercise to abstract the data layer in PHP (it wouldn't necessarily be trivial even with a full Java/JDBC stack available) so I'd understand if it has been considered and a few seconds later the conclusion was "yeah, right!"

---

BTW, I've been playing with CE4 since June or July and the plug-ins have blown me away with how clever they are. There's definitely been a huge learning curve which you would have to expect when you're dealing with plug-ins with at least 200 or so levers and switches to play with just about every aspect of the generated code but the documentation and the forums have helped me sort out the very few problems I had (which were mostly caused by clicking export before actually reading the documentation!).

The big revelation that made everything fall into place was figuring out after the first CE4 site that it's often easier (for me at least!) to start with a Pages template to use as a baseline to hack the other templates I'm planning on using. If I had one suggestion to make, it'd be nice to have a lightweight utility web engine stripped down to just the basic look and feel (typography, background colours/gradients, link colours, masthead headers/images) common to all of the CE4 plugins with the preview calling out labeled areas such as the masthead/grid/block/etc. and a set of checkboxes for the other CE4 plug-ins. Once that template is styled the way we want, the export would write the .lrtemplate files to a new folder and a user-defined text field for appending to the name of the for the selected CE4 plug-ins with the basic styling information and appropriate skeleton in the .lremplate files. Then all we'd need to do is restart Lightroom and voila...already reasonably hacked templates ready to be customised appropriately (autoindex-specific options, CRG-specific options, etc.) to allow even more mojito time and less stressing over whether the various templates are consistently branded!

I'm now on my third CE4 based site implemented in their own sub-domains and use CE4 Gallery to feed the personal website which is much too large to convert to CE4. I am considering making a clean break for 2016 with CE4 and provide links to the old content now that I'm playing in PHPlugins and about to delve into the custom multi-level menus.

Everyone who has seen the new sites has been blown away with how nice they look and then been shocked when I tell them that they were pretty much generated straight out of Lightroom with very little coding on my part. Then their jaws really drop watching them seamlessly look awesome on the iPhone and then the iPad.

Take a bow guys...between the clever coding and the amazing support here in the forums, I honestly couldn't imagine building a photo-based site any other way and I *AM* a programmer with over 20 years experience who actually likes coding and played around with my own web engines long ago before I found way better ones! But I'm not at all opposed to letting software let me spend more time with the pictures and the cameras...

rod barbee · 2015-11-14 02:57:37

If I had one suggestion to make, it'd be nice to have a lightweight utility web engine stripped down to just the basic look and feel (typography, background colours/gradients, link colours, masthead headers/images) common to all of the CE4 plugins with the preview calling out labeled areas such as the masthead/grid/block/etc.

Matt has created several starter templates to help people get started: http://ce4.theturninggate.net/docs/doku … _downloads
And I've created a sort of map to the several sections of a TTG page:
http://ttg-tips-and-tricks.barbeephoto. … out-areas/
and header and Nav options:
http://ttg-tips-and-tricks.barbeephoto. … n-options/

Not sure if the Lightroom SDK would allow for automatic creation of User Templates or not. But it's not too difficult to copy settings from one web engine template to the template for another web engine, as you've seen.
Though wouldn't it be cool to have a "parent" template that you could change and have all the "child" templates automatically updated.
I don't know how many times I thought I had a design the way I liked it in Pages and then copied those settings to templates for all the other engines only to find out I missed something after I subsequently customized all the other templates.

(for those who've not seen the template sharing technique: http://ce4.theturninggate.net/docs/doku … _templates )

Ben · 2015-11-14 07:18:09

Thanks for the kind words! As a programmer you probably understand that there are quite a few tricks under the hood for all our code to work together between LR and the server.

Our TTG BE framework began with support for both MySQL and SQLite databases. A global constant would tell the back-end to use one or the other. However, that support has since been eroded, with many of the database connections assuming SQLite and bypassing the generic framework (I know). So to bring back MySQL support would require a significant amount of code review and updating.
The main hesitation for MySQL is the need for our customers to setup databases. We've found that any additional steps create a range of support issues. In the case of MySQL, which is configured by control panels of all varieties, it would be difficult to provide consistent advice. SQLite on the other hand requires zero configuration, with database setup being as simple as the backend copying over the default database files.
I realise you mentioned MySQL as an option..
SQLite has proven to be reliable, scalable (not MySQL-clustered-scalable but scalable in the sense of large databases) and fast. We have encountered very few support issues related to reliability - two or three cases where the database file was locked, and one case where the file was corrupt in some way. I was able to solve that by running a command line export then import into a clean database file. That's a pretty good track record for a couple of years with our considerable install base.
The one aspect of SQLite that I'm not fond of is the dependence of a single file, stored under the ttg-be/data directory. It's too easy to accidentally kill it with an overwrite of ttg-be.

Our code needs to work with the lowest common denominator in terms of library support. I'd love to be able to use some of the more modern syntax of PHP, but we can't assume that the code will run across a wide variety of hosts. Likewise for SQLite support for referential integrity and cascading deletes. We don't know what's out there in terms of SQLite versions. The cascading deletes are therefore handled in application code. For example, there is a function to handle deletion of albums, with related photos and other data being zapped as well. That's called when an album is deleted from LR. The functional call is in the controller, so needs abstracting out to an application delegate for more general use, such as cleaning up in cases like this.

If you look at the application upgrade classes, you'll see that adding columns to tables is done by renaming the table, adding a new table with the extra columns, importing the new table with data from the renamed table and deleting the renamed table. Not ideal, but it works. Perhaps not strictly necessary, but the programmer in me wants to keep columns in a meaningful order, rather than adding data columns at the end of the schema after timestamp columns.

Matt would need to chime in on the web engine templates.

tom1983 · 2015-11-17 04:23:05

Thank you for the comprehensive reply Ben, I have a better understanding now how things are structured so should be able to get this tidied. Although the irrelevant entries won't cause any problems I'm a bit fussy about having a "clean" database given its size.

Of course I will ensure it's backed up before messing with it.

Cheers
Tom

Community @ The Turning Gate

#1 2015-11-12 04:29:51

Removing Albums & Sets - Lost Catalog File

#2 2015-11-12 05:03:10

Re: Removing Albums & Sets - Lost Catalog File

#3 2015-11-12 09:44:10

Re: Removing Albums & Sets - Lost Catalog File

#4 2015-11-13 01:19:06

Re: Removing Albums & Sets - Lost Catalog File

#5 2015-11-13 02:02:52

Re: Removing Albums & Sets - Lost Catalog File

#6 2015-11-13 06:28:35

Re: Removing Albums & Sets - Lost Catalog File

#7 2015-11-14 02:18:14

Re: Removing Albums & Sets - Lost Catalog File

#8 2015-11-14 02:57:37

Re: Removing Albums & Sets - Lost Catalog File

#9 2015-11-14 07:18:09

Re: Removing Albums & Sets - Lost Catalog File

#10 2015-11-17 04:23:05

Re: Removing Albums & Sets - Lost Catalog File

Board footer