1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.
  2. Welcome to Lightroom Forums! We're a friendly bunch, so please feel free to register and join in the conversation. If you're not familiar with forums, you'll find step by step instructions on how to register and post your first thread under Help on the menubar above. You're also welcome to join our newsletter, download our free Lightroom Quick Start eBook and explore our other FAQ resources.
    Dismiss Notice

SQLite tools?

Discussion in 'Extending Lightroom' started by PhilBurton, Mar 18, 2016.

  1. PhilBurton

    PhilBurton Active Member

    Joined:
    Nov 16, 2015
    Messages:
    531
    Location:
    California, USA
    Lightroom Experience:
    Beginner
    Primary OS:
    Windows 10
    Lightroom Version:
    6.7 / CC 2015.7
    Lightroom License:
    Perpetual (Standalone)
    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
     
  2. johnbeardy

    johnbeardy Senior Member Staff Member Moderator Lightroom Guru

    Joined:
    Oct 8, 2007
    Messages:
    4,009
    Location:
    London
    Lightroom Experience:
    Power User
    Primary OS:
    Windows 10
    Lightroom Version:
    6.6 / CC 2015.6
    Lightroom License:
    CC Subscription
    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.
     
  3. clee01l

    clee01l Lightroom Guru Staff Member Lightroom Guru

    Joined:
    Jun 20, 2009
    Messages:
    12,214
    Location:
    Bellaire, TX USA
    Lightroom Experience:
    Power User
    Primary OS:
    Mac 10.12.x Sierra
    Lightroom Version:
    6.8 / CC 2015.8
    Lightroom License:
    CC Subscription
    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.
     
  4. PhilBurton

    PhilBurton Active Member

    Joined:
    Nov 16, 2015
    Messages:
    531
    Location:
    California, USA
    Lightroom Experience:
    Beginner
    Primary OS:
    Windows 10
    Lightroom Version:
    6.7 / CC 2015.7
    Lightroom License:
    Perpetual (Standalone)
    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?
     
  5. johnbeardy

    johnbeardy Senior Member Staff Member Moderator Lightroom Guru

    Joined:
    Oct 8, 2007
    Messages:
    4,009
    Location:
    London
    Lightroom Experience:
    Power User
    Primary OS:
    Windows 10
    Lightroom Version:
    6.6 / CC 2015.6
    Lightroom License:
    CC Subscription
    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
     
  6. clee01l

    clee01l Lightroom Guru Staff Member Lightroom Guru

    Joined:
    Jun 20, 2009
    Messages:
    12,214
    Location:
    Bellaire, TX USA
    Lightroom Experience:
    Power User
    Primary OS:
    Mac 10.12.x Sierra
    Lightroom Version:
    6.8 / CC 2015.8
    Lightroom License:
    CC Subscription
  7. DGStinner

    DGStinner Active Member

    Joined:
    Jul 20, 2015
    Messages:
    282
    Location:
    New Jersey
    Lightroom Experience:
    Intermediate
    Primary OS:
    Mac 10.12.x Sierra
    Lightroom Version:
    6.8 / CC 2015.8
    Lightroom License:
    CC Subscription
  8. johnbeardy

    johnbeardy Senior Member Staff Member Moderator Lightroom Guru

    Joined:
    Oct 8, 2007
    Messages:
    4,009
    Location:
    London
    Lightroom Experience:
    Power User
    Primary OS:
    Windows 10
    Lightroom Version:
    6.6 / CC 2015.6
    Lightroom License:
    CC Subscription
    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
     
  9. clee01l

    clee01l Lightroom Guru Staff Member Lightroom Guru

    Joined:
    Jun 20, 2009
    Messages:
    12,214
    Location:
    Bellaire, TX USA
    Lightroom Experience:
    Power User
    Primary OS:
    Mac 10.12.x Sierra
    Lightroom Version:
    6.8 / CC 2015.8
    Lightroom License:
    CC Subscription
    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.
     
  10. Ferguson

    Ferguson Linwood Ferguson

    Joined:
    Jan 18, 2009
    Messages:
    867
    Location:
    Cape Coral, FL
    Lightroom Experience:
    Intermediate
    Primary OS:
    Windows 10
    Lightroom Version:
    6.8 / CC 2015.8
    Lightroom License:
    CC Subscription
    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. :)
     
  11. clee01l

    clee01l Lightroom Guru Staff Member Lightroom Guru

    Joined:
    Jun 20, 2009
    Messages:
    12,214
    Location:
    Bellaire, TX USA
    Lightroom Experience:
    Power User
    Primary OS:
    Mac 10.12.x Sierra
    Lightroom Version:
    6.8 / CC 2015.8
    Lightroom License:
    CC Subscription
    I just remembered something about LR v4. Included in it were tables for facial recognition which users did not see until LRCC/LR6
     
  12. PhilBurton

    PhilBurton Active Member

    Joined:
    Nov 16, 2015
    Messages:
    531
    Location:
    California, USA
    Lightroom Experience:
    Beginner
    Primary OS:
    Windows 10
    Lightroom Version:
    6.7 / CC 2015.7
    Lightroom License:
    Perpetual (Standalone)
    John[/QUOTE]
    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
     
  13. PhilBurton

    PhilBurton Active Member

    Joined:
    Nov 16, 2015
    Messages:
    531
    Location:
    California, USA
    Lightroom Experience:
    Beginner
    Primary OS:
    Windows 10
    Lightroom Version:
    6.7 / CC 2015.7
    Lightroom License:
    Perpetual (Standalone)
    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
     
  14. theWeissGuy

    theWeissGuy New Member

    Joined:
    Jul 9, 2012
    Messages:
    58
    Location:
    Cambridge, MA
    Lightroom Experience:
    Advanced
    Primary OS:
    Windows 10
    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).
     
  15. tspear

    tspear Senior Member

    Joined:
    Sep 23, 2014
    Messages:
    1,233
    Location:
    Waltham MA
    Lightroom Experience:
    Beginner
    Primary OS:
    Mac 10.11.x El Capitan
    Lightroom Version:
    6.4 / CC 2015.4
    Lightroom License:
    CC Subscription
    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... :)
     
  16. OogieM

    OogieM New Member

    Joined:
    May 28, 2012
    Messages:
    58
    Location:
    Colorado
    Lightroom Experience:
    Beginner
    Primary OS:
    Mac 10.10.x Yosemite
    Lightroom Version:
    4.x
    Lightroom License:
    Perpetual (Standalone)
    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
     
  17. PhilBurton

    PhilBurton Active Member

    Joined:
    Nov 16, 2015
    Messages:
    531
    Location:
    California, USA
    Lightroom Experience:
    Beginner
    Primary OS:
    Windows 10
    Lightroom Version:
    6.7 / CC 2015.7
    Lightroom License:
    Perpetual (Standalone)
    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
     
  18. darekk

    darekk New Member

    Joined:
    Dec 7, 2016
    Messages:
    4
    Location:
    Warsaw, Poland
    Lightroom Experience:
    Intermediate
    Primary OS:
    Windows 10
    Lightroom Version:
    6.7 / CC 2015.7
    Lightroom License:
    Perpetual (Standalone)

Share This Page