sql queries digikam
Locked
 
|
Dear list,
Sometimes I use sqlite3 from the cli to pull information directly out of the digikam sqlite database. For instance to generate simple statistics or for batch file renaming. My sql knowledge is very limited. I am looking for the sql queries as generated by digikam when a user performs a search using either the simple or the advanced search option. I found out that 'digikamsearch.cpp' (in the digikam source) is the code responsible for the sqlite queries. However you need to be a C++ debugger (and I am not) to reconstruct the queries from the code. Who can provide me with these sql queries? I assume at least the developpers have them. Thanks and happy digikam using, Jeroen jeroen.leijen.net/photography _______________________________________________ Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
|
|
Hi Jeroen,
as a quick reply: - make a copy of your digkam3.db to a separate place (just to be sure ;-) - sqlite3 digikam3.db sqlite> .output db.dump sqlite> .dump sqlite> .quit - have a look at db.dump Near the end there are the searches. I *think* that these are directly useable ... Best, Arnd On Mon, 2 Jul 2007, depot @ xs4all. nl wrote: > Dear list, > > Sometimes I use sqlite3 from the cli to pull information directly out of the digikam sqlite > database. For instance to generate simple statistics or for batch file renaming. > > My sql knowledge is very limited. I am looking for the sql queries as generated by digikam > when a user performs a search using either the simple or the advanced search option. > I found out that 'digikamsearch.cpp' (in the digikam source) is the code responsible for the > sqlite queries. However you need to be a C++ debugger (and I am not) to reconstruct the queries > from the code. > > Who can provide me with these sql queries? I assume at least the developpers have them. > > Thanks and happy digikam using, > > Jeroen > > jeroen.leijen.net/photography > > _______________________________________________ > Digikam-users mailing list > [hidden email] > https://mail.kde.org/mailman/listinfo/digikam-users > > Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
|
|
Hi Arnd,
Thanks for your response. But it's not quite what I am looking for. The queries (user searches) stored by digikam in the digikam3.db are in the format used by digikam. Eg 'digikamsearch:1%20AND%202?1.key=imagename&1.op=LIKE&1 .val=20&2.key=imagedate&2.op=LT&2.val=2007-07-02&count=2&name=testsearch'); These are not sql queries. I am looking for the sql queries that digikam generates to access the digikam3.db internally when a user performs a search. Regards, Jeroen jeroen.leijen.net/photography On Mon, Jul 02, 2007 at 01:39:23PM +0200, Arnd Baecker wrote: > Hi Jeroen, > > as a quick reply: > - make a copy of your digkam3.db to a separate place > (just to be sure ;-) > - sqlite3 digikam3.db > sqlite> .output db.dump > sqlite> .dump > sqlite> .quit > - have a look at db.dump > Near the end there are the searches. > > I *think* that these are directly useable ... > > Best, Arnd > > On Mon, 2 Jul 2007, depot @ xs4all. nl wrote: > > > Dear list, > > > > Sometimes I use sqlite3 from the cli to pull information directly out of the digikam sqlite > > database. For instance to generate simple statistics or for batch file renaming. > > > > My sql knowledge is very limited. I am looking for the sql queries as generated by digikam > > when a user performs a search using either the simple or the advanced search option. > > I found out that 'digikamsearch.cpp' (in the digikam source) is the code responsible for the > > sqlite queries. However you need to be a C++ debugger (and I am not) to reconstruct the queries > > from the code. > > > > Who can provide me with these sql queries? I assume at least the developpers have them. > > > > Thanks and happy digikam using, > > > > Jeroen > > > > jeroen.leijen.net/photography > > > > _______________________________________________ > > Digikam-users mailing list > > [hidden email] > > https://mail.kde.org/mailman/listinfo/digikam-users > > > > > Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
|
|
On Mon, 2 Jul 2007, depot @ xs4all. nl wrote: > Hi Arnd, > > Thanks for your response. But it's not quite what I am looking for. > > The queries (user searches) stored by digikam in the digikam3.db are in the format used by digikam. > Eg 'digikamsearch:1%20AND%202?1.key=imagename&1.op=LIKE&1 > .val=20&2.key=imagedate&2.op=LT&2.val=2007-07-02&count=2&name=testsearch'); > These are not sql queries. > > I am looking for the sql queries that digikam generates to access the digikam3.db internally > when a user performs a search. Alright, next try (need not be better ;-): Can you compile from source? If so you could just add in digikamsearch.cpp kdWarning() << "QUERY" << sqlQuery << endl; in a line before the two calls m_db.execSql(sqlQuery, &values) This should give you the full search string on the konsole when running searches in digikam. Best, Arnd _______________________________________________ Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
|
|
2007/7/2, Arnd Baecker <[hidden email]>:
to prevent problem with multithreading in digiKam, do not use KDE API directly, but "DWarning()" instaead "kdWarning()" unforget to put #include"ddebug.h" at top of source to compile fine. Gilles _______________________________________________ Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
|
|
In reply to this post by Arnd Baecker
> Alright, next try (need not be better ;-):
> > Can you compile from source? > If so you could just add in digikamsearch.cpp > kdWarning() << "QUERY" << sqlQuery << endl; > in a line before the two calls > m_db.execSql(sqlQuery, &values) > > This should give you the full search string on the konsole > when running searches in digikam. > > Best, Arnd Hi Arnd. Thanks a lot again! I did as you suggested. Even though I don't understand what your code does. This is a sample of the output on the console when I did some searches: digikam: Dirty: / digikam: digikamsearch:1?1.key=album&1.op=EQ&1.val=1&name=Last%20Search&count=1 digikam: digikamsearch:1?1.key=tag&1.op=EQ&1.val=1&name=Last%20Search&count=1 digikam: digikamsearch:1?1.key=tag&1.op=EQ&1.val=1&name=Last%20Search&count=1 digikam: digikamsearch:1?1.key=tag&1.op=EQ&1.val=2&name=Last%20Search&count=1 digikam: digikamsearch:1 AND 2?1.key=tag&1.op=EQ&1.val=2&2.key=album&2.op=EQ&2.val=1&name=Last%20Search&count=2 digikam: digikamsearch:1 AND 2?1.key=tag&1.op=EQ&1.val=2&2.key=imagedate&2.op=GT&2.val=2007-07-02&name=Last%20Search&count=2 digikam: digikamsearch:1 AND 2?1.key=tag&1.op=EQ&1.val=2&2.key=imagedate&2.op=LT&2.val=2007-07-02&name=Last%20Search&count=2 digikam: digikamsearch:1 AND 2?1.key=tag&1.op=EQ&1.val=2&2.key=imagedate&2.op=LT&2.val=2007-07-02&name=Last%20Searchjkj&count=2 digikam: Dirty: / Still it's not what I am looking for. Maybe I was not clear enough with my question. Let me rephrase it. Digikam internally produces sql queries to question the digikam3.db when a user performs actions like filtering on a tag, searching in a date range, finding a specific caption or comment etc. These queries look something like: sqlQuery = "SELECT Images.id, Images.name, Images.dirid, Images.datetime, Albums.url " "FROM Images, Albums LEFT JOIN ImageProperties ON Images.id = Imageproperties.imageid " "WHERE ( "; (taken from digikamsearch.cpp). The above sample is not complete. It's just the header of a generic digikam query. Complete and valid sql queries are produced by digikam on the fly constantly whenever a user sorts, filters or finds his pictures. I am looking for valid and complete sql queries for the most common tasks like the simple search option, selecting on tags, dates, captions etc. If there was a way to monitor all communication from digikam to sqlite3 then it would be easy to find out. Anyone? Regards, Jeroen jeroen.leijen.net/photography _______________________________________________ Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
|
|
In reply to this post by Gilles Caulier-4
> to prevent problem with multithreading in digiKam, do not use KDE API
> directly, but "DWarning()" instaead "kdWarning()" > > unforget to put #include"ddebug.h" at top of source to compile fine. Adding DWarning() instead of kdWaring() gave me a make error: .libs/digikamsearch.o: In function `kio_digikamsearch::special(QMemArray<char> const&)': digikamsearch.cpp:(.text+0x3da6): undefined reference to `DWarning(int)' digikamsearch.cpp:(.text+0x3dbe): undefined reference to `Digikam::Ddbgstream::~Ddbgstream()' collect2: ld returned 1 exit status make[3]: *** [kio_digikamsearch.la] Error 1 Anyway, I am not sure if this is relevant. The suggestion by Arnd didn't help me. Regards, Jeroen jeroen.leijen.net/photography > > Gilles > _______________________________________________ > Digikam-users mailing list > [hidden email] > https://mail.kde.org/mailman/listinfo/digikam-users _______________________________________________ Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
|
|
In reply to this post by depot
2007/7/2, depot @ xs4all. nl <[hidden email]>: > Alright, next try (need not be better ;-): It just put as text the string used to query the database content... This is a sample of the output on the console when I did some searches: no, this is what you want, but you have a just a part of the DB query. Gilles _______________________________________________ Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
|
|
In reply to this post by depot
2007/7/2, depot @ xs4all. nl <[hidden email]>: > to prevent problem with multithreading in digiKam, do not use KDE API ah yes, it's in KIOslave. you don't need to use DWarning() at this place. KIOSlave running in a separate process. There is no problem with multithreading. Gilles _______________________________________________ Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
|
|
In reply to this post by depot
Hi,
On Mon, 2 Jul 2007, depot @ xs4all. nl wrote: > > Alright, next try (need not be better ;-): > > > > Can you compile from source? > > If so you could just add in digikamsearch.cpp > > kdWarning() << "QUERY" << sqlQuery << endl; > > in a line before the two calls > > m_db.execSql(sqlQuery, &values) > > > > This should give you the full search string on the konsole > > when running searches in digikam. > > > > Best, Arnd > > Hi Arnd. Thanks a lot again! > I did as you suggested. Even though I don't understand what your code does. OK, a bit of explanation is needed: The above command will output a string (through the Warning handler) to the konsole which will start with QUERY and then the actual sqlite query string. So you have to look for lines with QUERY ... > This is a sample of the output on the console when I did some searches: > > digikam: Dirty: / > digikam: digikamsearch:1?1.key=album&1.op=EQ&1.val=1&name=Last%20Search&count=1 > digikam: digikamsearch:1?1.key=tag&1.op=EQ&1.val=1&name=Last%20Search&count=1 > digikam: digikamsearch:1?1.key=tag&1.op=EQ&1.val=1&name=Last%20Search&count=1 > digikam: digikamsearch:1?1.key=tag&1.op=EQ&1.val=2&name=Last%20Search&count=1 > digikam: digikamsearch:1 AND > 2?1.key=tag&1.op=EQ&1.val=2&2.key=album&2.op=EQ&2.val=1&name=Last%20Search&count=2 > digikam: digikamsearch:1 AND > 2?1.key=tag&1.op=EQ&1.val=2&2.key=imagedate&2.op=GT&2.val=2007-07-02&name=Last%20Search&count=2 > digikam: digikamsearch:1 AND > 2?1.key=tag&1.op=EQ&1.val=2&2.key=imagedate&2.op=LT&2.val=2007-07-02&name=Last%20Search&count=2 > digikam: digikamsearch:1 AND > 2?1.key=tag&1.op=EQ&1.val=2&2.key=imagedate&2.op=LT&2.val=2007-07-02&name=Last%20Searchjkj&count=2 > digikam: Dirty: / > > Still it's not what I am looking for. Yep, you will have to do a search and look for lines which have the text "QUERY" in them, but they will only appear if you do a search. > Maybe I was not clear enough with my question. Let me rephrase it. Your question was crystal clear - we will get there ... > Digikam internally produces sql queries to question the digikam3.db when a user performs > actions like filtering on a tag, searching in a date range, finding a specific caption or > comment etc. > > These queries look something like: > sqlQuery = "SELECT Images.id, Images.name, Images.dirid, Images.datetime, Albums.url " > "FROM Images, Albums LEFT JOIN ImageProperties ON Images.id = Imageproperties.imageid " > "WHERE ( "; > > (taken from digikamsearch.cpp). > The above sample is not complete. It's just the header of a generic digikam query. Complete and valid sql queries are produced by digikam on > the fly constantly whenever a user sorts, filters or finds his pictures. > > I am looking for valid and complete sql queries for the most common tasks like the > simple search option, selecting on tags, dates, captions etc. > > If there was a way to monitor all communication from digikam to sqlite3 then it would be easy > to find out. > > Anyone? Hej, don't give me up to quickly: I just added the two kdWarning() So that the code looks like ... kdWarning() << "QUERY" << sqlQuery << endl; if (!m_db.execSql(sqlQuery, &values)) ... This gives me for example: kio_digikamsearch: WARNING: QUERYSELECT Images.id, Images.name, Images.dirid, Images.datetime, Albums.url FROM Images, Albums LEFT JOIN ImageProperties ON Images.id = Imageproperties.imageid WHERE ( (Images.id IN (SELECT imageid FROM ImageTags WHERE tagid IN (SELECT id FROM Tags WHERE name NOT LIKE '%A%'))) ) AND (Albums.id=Images.dirid); Does this look better for you? Well, clearly, kdWarning() << "QUERY: " << sqlQuery << endl; if (!m_db.execSql(sqlQuery, &values)) would have been better (i.e. to have a bit of space before the actual query. HTH, Arnd _______________________________________________ Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
|
|
> > Hi Arnd. Thanks a lot again!
> > I did as you suggested. Even though I don't understand what your code does. > > OK, a bit of explanation is needed: > The above command will output a string (through the Warning handler) > to the konsole which will start with > QUERY > and then the actual sqlite query string. > So you have to look for lines with QUERY ... Thanks, that's clear now. > > > Still it's not what I am looking for. > > Yep, you will have to do a search and look for lines > which have the text "QUERY" in them, > but they will only appear if you do a search. > > > Maybe I was not clear enough with my question. Let me rephrase it. > > Your question was crystal clear - we will get there ... > > > > > If there was a way to monitor all communication from digikam to sqlite3 then it would be easy > > to find out. > > > > Anyone? > > Hej, don't give me up to quickly: > > I just added the two kdWarning() > So that the code looks like > ... > kdWarning() << "QUERY" << sqlQuery << endl; > if (!m_db.execSql(sqlQuery, &values)) > ... > > This gives me for example: > > kio_digikamsearch: WARNING: QUERYSELECT Images.id, Images.name, > Images.dirid, Images.datetime, Albums.url FROM Images, Albums LEFT JOIN > ImageProperties ON Images.id = Imageproperties.imageid WHERE ( (Images.id > IN (SELECT imageid FROM ImageTags WHERE tagid IN (SELECT id FROM > Tags WHERE name NOT LIKE '%A%'))) ) AND (Albums.id=Images.dirid); > > Does this look better for you? Perfect! That is what I was aiming at. Now I can use these queries as examples for custom command line sqlite3 queries on my digikam3.db. > > Well, clearly, > kdWarning() << "QUERY: " << sqlQuery << endl; > if (!m_db.execSql(sqlQuery, &values)) > would have been better (i.e. to have a bit of space before > the actual query. > > HTH, Arnd > Thanks again. I will rebuild digikam tomorrow. Am not at my machine right now. Regards, Jeroen jeroen.leijen.net/photography _______________________________________________ Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
Re: sql queries digikam
|
|
In reply to this post by Arnd Baecker
Dnia poniedziałek 02 lipiec 2007, Arnd Baecker napisał:
> I just added the two kdWarning() Thanks. Necessity to dive into structure of digikamdb was the biggest obstacle to update cdigi (+ fact that creation of gallery from album is working - and this is 99.9% of my usage :)) m. _______________________________________________ Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
|
|
In reply to this post by depot
Hi Jeroen,
On Mon, 2 Jul 2007, depot @ xs4all. nl wrote: [...] > Perfect! That is what I was aiming at. Now I can use these queries as examples for custom command > line sqlite3 queries on my digikam3.db. It seems you have a lot of knowledge about sql queries. I wonder whether a short tutorial on using those in the connection with digikam would be helpful (either for the hand-book or the upcoming wiki http://wiki.kde.org/tiki-index.php?page=Digikam ?) On the other hand, for the KDE4 release of digikam, changes in the database will be made at some point, see http://wiki.kde.org/tiki-index.php?page=Digikam%20development%20discussion > Thanks again. I will rebuild digikam tomorrow. Am not at my machine right now. Don't hesitate to ask in case of any problems, Best, Arnd _______________________________________________ Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
«
Return to digikam-users
|
1 view|%1 views
| Free forum by Nabble | Edit this page |