# SQLite tools?



## PhilBurton (Mar 18, 2016)

To satisfy my curiosity, I would like to be able to peer inside the Lightroom database,   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.   

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

Phil


----------



## johnbeardy (Mar 18, 2016)

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.


----------



## clee01l (Mar 19, 2016)

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 (Mar 19, 2016)

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!    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?


----------



## johnbeardy (Mar 19, 2016)

PhilBurton said:


> 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


----------



## clee01l (Mar 19, 2016)

Sorry, the earlier link was to the "industrial strength" product. I have Navicat Essentials. While still not free, is more reasonably prices at $32USD.
http://www.navicat.com/store/navicat-essentials#sqlite_essentials


----------



## DGStinner (Mar 19, 2016)

I've used SQLite Browser to view SQLite databases in the past.


----------



## johnbeardy (Mar 19, 2016)

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


----------



## clee01l (Mar 19, 2016)

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.


----------



## Linwood Ferguson (Mar 19, 2016)

PhilBurton said:


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


----------



## clee01l (Mar 19, 2016)

I just remembered something about LR v4.  Included in it were tables for facial recognition which users did not see until LRCC/LR6


----------



## PhilBurton (Mar 19, 2016)

John[/QUOTE]


clee01l said:


> 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 (Mar 19, 2016)

Ferguson said:


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


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.    Of course, now that Adobe is pushing people to the CC plan, there will be more pressure to put out releases, not less. 

Phil


----------



## theWeissGuy (Mar 19, 2016)

PhilBurton said:


> To satisfy my curiosity, I would like to be able to peer inside the Lightroom database,   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.
> 
> 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 (Mar 21, 2016)

PhilBurton said:


> 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.    Of course, now that Adobe is pushing people to the CC plan, there will be more pressure to put out releases, not less.
> 
> 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...


----------



## OogieM (Mar 25, 2016)

PhilBurton said:


> To satisfy my curiosity, I would like to be able to peer inside the Lightroom database,   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.
> 
> 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 (Mar 26, 2016)

OogieM said:


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


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


----------



## darekk (Dec 15, 2016)

You can open the Lightroom catalog files using also Microsoft Access:
www.lightroomqueen.com/community/threads/scripting-and-querying-lightrooms-database.22420
Scripting and querying Lightroom's database


----------

