Add SQL where statement to collection rules

[wpferguson]

There are some (lots) of collections that can't be built using the current collection rules, such as collecting all images shot in a month irrespective of year as was discussed in https://discuss.pixls.us/t/filtering-collection-on-capture-date-but-by-month-only/51621

Describe the solution you'd like
The ability to write a SQL where clause such as strftime("%m", datetime_taken) = '05', which would be appended to select * from images where . The example given would select all images shot in May.

Alternatives
Perhaps a query builder, but I think that would be horribly difficult to make usable without making it huge.

[TurboGit]

I understand the goal but at the same time we want to be ultra-safe to avoid possible malicious SQL injection or letting a user just crash the DB.

[wpferguson]

That was my first worry

[da-phil]

I like the presented use-case, as I sometimes do that same during my yearly calendar image selection process. I'm usually quite strict when selecting pictures for a certain month, a candidate picture should be ideally captured in the same month it is going to be appear in the calendar +-1month tolerance.

However I also don't think we should expose database access by letting people directly query the database.
What about catering to this use-case and just defining a new collection filter rule "capture month" which let's you select multiple months from an array of month fields?

If folks are happy with this idea, I could create a draft PR as a proof-of-concept.

[wpferguson]

However I also don't think we should expose database access by letting people directly query the database.

I'm talking about only letting them have a where clause on SELECT * from images, not free form SQL statements.

Even so, I have mixed feelings about letting users build partial SQL statements. I don't wont to see a bunch of "please debug my where clause" issues.

I could create a draft PR

This is the flip side of the equation. It could take up to 6 months, if the PR gets accepted, to have a solution. For this particular use case, the user wouldn't see a solution until the end of December. For this use case, there isn't a faster solution. But I don't think it should take 6 months each time a user comes up with a need for a different collection query.

---

So, how about we extend Lua to allow it to create custom collection (still just a where clause) rules? My thoughts:

• if you can figure out how to write Lua code, you should be able to figure out an SQL where clause.
• we can do checks, in the API, on the submitted where clause before passing it to the collections module
• the user can save the custom query as a preset in the collections module
• perhaps we write a where clause builder in Lua.

[pehar1]

I would vote against allowing a direct input of a database query directly in the dt UI. The risk of incorrect entries that could damage the database or the SQL injection of malicious code seems too high to me. Or you would have to implement a parser that details the input to prevent these problems, which would certainly require a lot of programming effort.

Furthermore, very few users are likely to be familiar with SQL syntax, and numerous “issues” due to incorrect entries would certainly be expected.

The idea of such an interface via LUA immediately came to my mind when I read the thread on pixls.us. But I suspect that would also require some effort to extend the current LUA API.

The image information is spread across several tables in the database, numerous queries require (INNER) JOINs. So input should not be limited to SELECT * from images WHERE but also allow queries like

SELECT * FROM images INNER JOIN meta_data ON meta_data.key = 3 AND length(meta_data.value) > 80 AND meta_data.id = images.id

(Select the images that have a description (Xmp.dc.description) with a length of more than 80 characters.)

[da-phil]

This is the flip side of the equation. It could take up to 6 months, if the PR gets accepted, to have a solution. For this particular use case, the user wouldn't see a solution until the end of December. For this use case, there isn't a faster solution. But I don't think it should take 6 months each time a user comes up with a need for a different collection query.

So, how about we extend Lua to allow it to create custom collection (still just a where clause) rules? My thoughts:

* if you can figure out how to write Lua code, you should be able to figure out an SQL where clause.

* we can do checks, in the API, on the submitted where clause before passing it to the collections module

* the user can save the custom query as a preset in the collections module

* perhaps we write a where clause builder in Lua.

You're right, would be great if we could provide an interim solution for folks who are not afraid of getting involved into more technical stuff like writing their own SELECT * from images WHERE ... statements, until such a feature makes it to the next release. Such a lua interface should make sure though that access is read-only, i.e. no DELETE, INSERT or UPDATE statements are possible.
I'm also thinking of how to make such a query interface more accessible and easy to use for folks who are no SQL experts and don't know the internal darktable database layout.
Maybe we could join some of the tables into a wide table, which has most attributes available as columns (i.e.g maaaany columns), then people do not need to start thinking about JOIN statements and knowing all the details about the internal db structure.

In the meantime I'll find some time to add this "capture month" filter rule to both, collections and collection filters.

[da-phil]

This is the flip side of the equation. It could take up to 6 months, if the PR gets accepted, to have a solution.

Little did I know how complicated and convoluted that collection + filter rule code is, now I understand what you meant with 6 months 🤣

[pehar1]

In the meantime I'll find some time to add this "capture month" filter rule to both, collections and collection filters.

I would prefer the more flexible LUA script variant. It would include simple queries such as the year-independent month selection discussed.

[da-phil]

I would prefer the more flexible LUA script variant.

Sure, this is fine for "techy" enthusiasts like us, but I think that an easy to use UI option would be better in the long run, as I believe the use-case is not sooo exotic, at least not more exotic than filtering for EXIF tags.
And even the proposed LUA solution is not straight forward to get right and convenient to use, again not making the user write join statements to just do some basic filtering.

This would be my UI proposal, slim and functional, it allows the selection of multiple months for the query.

[pabl0]

This would be my UI proposal, slim and functional, it allows the selection of multiple months for the query.

I can imagine that some folks might have a use-case for selection of days of the week as well in similar manner.

[pehar1]

Since there seems to be no activity in extending the collection / filter modules I wrote a LUA script which allows execution of arbitrary SQL commands on the database. Following is a short description (taken from the comments of the script) :

PREREQUISITES :

• OS Linux
• sqlite3 installed

SHORT DESCRIPTION :
Installs a module in the left panel of the lighttable and
a button (“Select with SQL query”) in the Selection module.

Function of the widgets in the module (from top to bottom) :

1. Editable textfield with the full path to the database library.db
   on which the queries are executed
2. Editable text field for entering the SQL query
3. Checkbutton to enable the output of the database query result in (5.)
4. Button to start executing the database query (WITHOUT selecting images)
5. Text field for outputting the result of the database query

Function of the “Select with SQL query” button in the Selection module :

• Execution of the database query and selection of the images found by it

EXAMPLE 1 :
Select all images that have a description (Xmp.dc.description) with a length
of more than 80 characters :

SELECT images.id FROM images
	INNER JOIN meta_data
 		ON images.id = meta_data.id
		AND meta_data.key = 3
		AND length(meta_data.value) > 80
 	ORDER BY datetime_taken DESC

EXAMPLE 2 :
Select all images taken in May (of any year) :

 SELECT id FROM images WHERE strftime('%m', datetime(
 	datetime_taken / 1000000 - 62135596800, 'unixepoch')) = '05'

For readability the SQL commands above are formatted.
To execute these examples they must be entered WITHOUT linebreaks !

################################################################################
WARNING :
This tool should only be used by people who are proficient in the query
language SQL and have knowledge of the structure of the database tables.
The correctness of the commands entered is not checked and there is no error
handling. Messages from sqlite3 are output to the standard output channel.

It is possible to damage or destroy the database, making it unusable with
darktable!

ANY USE OF THIS TOOL IS AT YOUR OWN RISK!
################################################################################

If there is interest in this script, I can make it available here, also for discussion and improvement.
But I emphasize again: this script is for “experts” only !

[wpferguson]

I would include another warning reminding the user that the database is opened by darktable and doing anything that causes a write could/would be catastrophic.

[pehar1]

@wpferguson, I have added a warning that any write access would most likely result in damage/destruction of the database.
However, this should not be possible because I open the database in read-only mode within the script.

local command = sqlite_executable .. " -cmd \".timeout 1000\" \"file:" .. database .. "?mode=ro\" \"" .. query .. "\""

Direct SQL queries on the database are an extremely powerful tool, but they can also become a weapon if you are not aware of the consequences of your actions.
For this reason, I am not sure whether I should offer this script to a wider audience. Even on pixls, there are occasionally users who strictly refuse to read any kind of documentation and expect everything to be “intuitive.” Such users can very easily shoot themselves in the foot.

What is your opinion on this? Are you interested in taking a look at the script?