Community @ The Turning Gate

When I updated Publisher, I had to explicitly choose Dashboard -> Publisher Admin which then showed the messages where the underlying database was being updated (and the same for other updates that update the structure of the SQLite database).

As I have multiple CE4-based sites in play now (three and counting!), I have a staging area where I keep the downloaded packages and then unzip and update the appropriate folders in a staging area.

From there, I use a programme from Scooter Software called Beyond Compare to update Lightroom's plugins or CE4's TTG-BE directories for the individual sites.

Once I'm satisfied the changes haven't broken anything, I use another session in Beyond Compare to sync the local site directory to my web host via FTP (using Mirror->Right) with the following excluded directories to keep it from stomping on CE4's data or uploading phplugins-sample:

.\cgi-bin
phplugins-sample
.\ttg-be\data
.\galleries\*
.\blog

Beyond Compare has a bunch of other options for comparing directories and I use it for maintaining my backups from the laptop to the drive tower (first to a 4GB drive and from there to a 4x4GB RAID-5 array).  I just select the backup session config I want, hit "Auto Sync" and then wander off to do something else whilst it does the backup.  (You might be getting the impression I really love Beyond Compare for file management...if so, you'd be quite correct! )

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...