SQLite tools?

PhilBurton

Lightroom enthusiast (but still learning)
Premium Classic Member
Premium Cloud Member
Joined
Nov 16, 2015
Messages
2,144
Location
California, USA
Lightroom Experience
Intermediate
Lightroom Version
Classic
To satisfy my curiosity, I would like to be able to peer inside the Lightroom database, :geek: but I know nothing about SQLite. Can anyone recommend some tools that I could download so that I can browse and maybe search the LR database. I am not intending to add or modify the database. :eek:

I kind of / sort of / maybe know some SQL statements. How important is SQL command knowledge for what i want to do?

Phil
 
Joined
Oct 8, 2007
Messages
5,453
Location
London
Lightroom Experience
Power User
The coincidence is really funny, Phil, as only today I finally had a look at the sqlite command line tool for a plugin. But as a general tool, only a few hours ago I was discussing a better tool for you, SQLite Manager, which runs as a Firefox add-on. The database is pretty messy but you can easily look around.
 
Joined
Jun 20, 2009
Messages
16,191
Location
Houston, TX USA
Lightroom Experience
Power User
Lightroom Version
Cloud Service
I use Navicat for SQLite. It is GUI Database Manager and will let you poke around with little problem. You don't need to know SQL to view.
Before you access the Catalog file with something besides LR, make a copy of the file and open the copy in Navicat for SQLite.
 

PhilBurton

Lightroom enthusiast (but still learning)
Premium Classic Member
Premium Cloud Member
Joined
Nov 16, 2015
Messages
2,144
Location
California, USA
Lightroom Experience
Intermediate
Lightroom Version
Classic
John and Cletus,

Navicat for SQLite looks very nice, but it is probably overkill for my needs, and in any case, I would not want to spend too much money on this topic. So it looks like I going with the FireFox add-in. I'm one of the dwindling number of people still using that browser.

I downloaded the FireFox add-in, very quick and easy, and then connected to a backup lrcat. Wow, there is a lot going on! :eek2: Somehow I thought that the db structure would be more straightforward, but I guess I was very mistaken. So I exported the db structure and then took a quick look at the SQL code. I needed to open the *.sql file in MS Word to see the file structure. OK, enough for this one time.

Is it necessary for people who write plug-ins to work with the SQLite db?
 
Joined
Oct 8, 2007
Messages
5,453
Location
London
Lightroom Experience
Power User
Is it necessary for people who write plug-ins to work with the SQLite db?
No. In fact it's the opposite, Phil, SQL's almost a waste of time if you're writing a plug-in.
  • You have to figure out the undocumented SQL structure, which changes occasionally and includes blocks of unparsed data, you can't query the open catalogue, you depend on an external command line tool, and your code needs to handle Mac and PC.
  • Plug-ins abstract the underlying data, query the open catalogue in real time, are self-contained, and cross platform.
So you need no knowledge of SQL to write plug-ins.

John
 
Joined
Oct 8, 2007
Messages
5,453
Location
London
Lightroom Experience
Power User
What sorts of things are people looking for in the SQL?

I ask because I've been experimenting with the command line so I can read and search for things that aren't possible in Lr or via the SDK. So for example, I would like to find pictures which have been printed or exported - ie with "Print" or "Export" in the History. Color Mode is another - eg how can I find CMYK images in my catalogue, or those which are 8 bit? Now I've reached the "holy grail" point of being able to query the database using SQLite3, I'd be interested to hear ideas.

John
 
Joined
Jun 20, 2009
Messages
16,191
Location
Houston, TX USA
Lightroom Experience
Power User
Lightroom Version
Cloud Service
I first used it when Publish Services were initially released and there was no way to mark images as published when they got flagged to be republished. This is no longer an issue. Now, about the only time I'll open a catalog in SQLite is to repair some one else's badly tangled catalog. Usually this involves the fields "Relative Path" & "Absolute Path". More often I simply use the Database manager to view the individual tables as it is easily possible to break referential integrity by updating a table and not knowing the ramifications on related tables.
 
Joined
Jan 18, 2009
Messages
2,236
Location
Fort Myers, FL
Lightroom Experience
Advanced
Lightroom Version
Classic
:eek2: Somehow I thought that the db structure would be more straightforward, but I guess I was very mistaken. So I exported the db structure and then took a quick look at the SQL code. I needed to open the *.sql file in MS Word to see the file structure. OK, enough for this one time.
Like most legacy databases, there is good and bad. I found it more good than I expected. The core tables are mostly normalized. You have no documentation, so you have to read between the lines, and sometimes wonder (e.g. GUID's used in addition to integer keys, why?) but you can pretty quickly track through (as an example) the image to the folder to the root, to form the path name to a file. You can also see how it handles root file moves, etc. Things you might think of as the "original" Lightroom are really quite well structured.

The areas where it becomes very confusing are those areas you can tell were added later, and someone said "screw normalization just stuff it in there". You'll find some columns, for example, with metadata stored as XML and value pairs (and sometimes both) all stuffed in a single column, not normalized out at all. This is especially true in publishing related plugins, as I do not think the API provides a mechanism to extend the database, so they just stuff data tables inside of columns.

If you are looking for something simple it is easy. For example, I run a quick report each year showing all galleries with a certain word in the name, in the calendar year, as it represents trips to those locations for tax purposes. Easy. The other day I tried finding where the XMP/IPTC Date Created was, and finally just gave up - it's not stored consistently, and I could never find a query to give the same value all the time as Lightroom (notably its absence, my guess due to some defaulting).

Obviously changing things is a whole different level of commitment than looking around. :)
 

PhilBurton

Lightroom enthusiast (but still learning)
Premium Classic Member
Premium Cloud Member
Joined
Nov 16, 2015
Messages
2,144
Location
California, USA
Lightroom Experience
Intermediate
Lightroom Version
Classic
John[/QUOTE]
Sorry, the earlier link was to the "industrial strength" product. I have Navicat Essentials. While still not free, is more reasonably prices at $32USD.
Navicat Essentials
Cletus,

Thanks. The Navicat has an item about "Personal" licenses at reduced cost, but I could not find out what that cost was. If I get serious about this, I guess I can afford the price for the version you use.

Phil
 

PhilBurton

Lightroom enthusiast (but still learning)
Premium Classic Member
Premium Cloud Member
Joined
Nov 16, 2015
Messages
2,144
Location
California, USA
Lightroom Experience
Intermediate
Lightroom Version
Classic
Like most legacy databases, there is good and bad. I found it more good than I expected. The core tables are mostly normalized. You have no documentation, so you have to read between the lines, and sometimes wonder (e.g. GUID's used in addition to integer keys, why?) but you can pretty quickly track through (as an example) the image to the folder to the root, to form the path name to a file. You can also see how it handles root file moves, etc. Things you might think of as the "original" Lightroom are really quite well structured.

The areas where it becomes very confusing are those areas you can tell were added later, and someone said "screw normalization just stuff it in there". You'll find some columns, for example, with metadata stored as XML and value pairs (and sometimes both) all stuffed in a single column, not normalized out at all. This is especially true in publishing related plugins, as I do not think the API provides a mechanism to extend the database, so they just stuff data tables inside of columns.

If you are looking for something simple it is easy. For example, I run a quick report each year showing all galleries with a certain word in the name, in the calendar year, as it represents trips to those locations for tax purposes. Easy. The other day I tried finding where the XMP/IPTC Date Created was, and finally just gave up - it's not stored consistently, and I could never find a query to give the same value all the time as Lightroom (notably its absence, my guess due to some defaulting).

Obviously changing things is a whole different level of commitment than looking around. :)
Ferguson,

Ahh yes, "technical debt." As this cruft accumulates, is it any wonder that each release comes with new bugs?:( Someone in Adobe needs to be less release-happy and more concerned about putting out a clean release. :mad2: Of course, now that Adobe is pushing people to the CC plan, there will be more pressure to put out releases, not less. :cry:

Phil
 

theWeissGuy

Member
Joined
Jul 9, 2012
Messages
58
Location
Cambridge, MA
Lightroom Experience
Advanced
To satisfy my curiosity, I would like to be able to peer inside the Lightroom database, :geek: but I know nothing about SQLite. Can anyone recommend some tools that I could download so that I can browse and maybe search the LR database. I am not intending to add or modify the database. :eek:

I kind of / sort of / maybe know some SQL statements. How important is SQL command knowledge for what i want to do?

Phil
Check out freeware SQLite Database Browser Portable (DB Browser for SQLite Portable (formerly SQLite Database Browser) | PortableApps.com - Portable software for USB, portable and cloud drives).
 

tspear

Senior Member
Joined
Sep 23, 2014
Messages
1,794
Location
Waltham MA
Lightroom Experience
Beginner
Lightroom Version
Ferguson,

Ahh yes, "technical debt." As this cruft accumulates, is it any wonder that each release comes with new bugs?:( Someone in Adobe needs to be less release-happy and more concerned about putting out a clean release. :mad2: Of course, now that Adobe is pushing people to the CC plan, there will be more pressure to put out releases, not less. :cry:

Phil
Depends on the company. I have consulted to some large companies, when they switched from license models to subscription, the release quality actually increased; and the technical debt was significantly addressed (over time). I also have seen the reverse (I was representing a customer). So, it depends... :)
 
Joined
May 28, 2012
Messages
150
Location
Colorado
Lightroom Experience
Intermediate
Lightroom Version
To satisfy my curiosity, I would like to be able to peer inside the Lightroom database, :geek: but I know nothing about SQLite. Can anyone recommend some tools that I could download so that I can browse and maybe search the LR database. I am not intending to add or modify the database. :eek:

I kind of / sort of / maybe know some SQL statements. How important is SQL command knowledge for what i want to do?

Phil
My LambTracker program is based on an SQLite Database so I have all sorts of tools I use. I am now quite familiar with SQLite databases, their design, structure and how to write fairly complex queries.

The tools I come back to all the time are the SQLite Manager plug-In for Firefox, SQLite Manger from SQLabs » sqlite database management tools and server (confusing that they have the same name, they are different) and very rarely SQLite Studio from sqlitestudio.pl

For learning basic relational database design stuff I loved the book Beginning Database Design by Clare Churcher. (I'd only used hierarchical databases before I developed LambTracker)

Right now my go to reference is The Definitive Guide to SQLite by Grant Allen and Mike Owens.

I have poked at the Lightroom database and at the Quicken 2015 database. By way of comparison here is some info for you on relative complexity of the 3 main SQLite databases I've played with.

Lightroom has 102 tables
LambTracker has 58 tables with another 12 in planning stages
Quicken had 50 tables
 

PhilBurton

Lightroom enthusiast (but still learning)
Premium Classic Member
Premium Cloud Member
Joined
Nov 16, 2015
Messages
2,144
Location
California, USA
Lightroom Experience
Intermediate
Lightroom Version
Classic
My LambTracker program is based on an SQLite Database so I have all sorts of tools I use. I am now quite familiar with SQLite databases, their design, structure and how to write fairly complex queries.

The tools I come back to all the time are the SQLite Manager plug-In for Firefox, SQLite Manger from SQLabs » sqlite database management tools and server (confusing that they have the same name, they are different) and very rarely SQLite Studio from sqlitestudio.pl

For learning basic relational database design stuff I loved the book Beginning Database Design by Clare Churcher. (I'd only used hierarchical databases before I developed LambTracker)

Right now my go to reference is The Definitive Guide to SQLite by Grant Allen and Mike Owens.

I have poked at the Lightroom database and at the Quicken 2015 database. By way of comparison here is some info for you on relative complexity of the 3 main SQLite databases I've played with.

Lightroom has 102 tables
LambTracker has 58 tables with another 12 in planning stages
Quicken had 50 tables
Oogie,
Thanks. I have already used some of the tools recommended in earlier posts, and I've satisfied my curiosity about Lightroom. Especially after JohnBeardy's recommendation, I'm going to stay away from SQL.

I had no idea that Quicken has 50 tables, but I guess I shouldn't be surprised. I am a bit of a Quicken "power user."
Phil
 
Top