Bug 428545

Summary:	Wish: Writing SQL query in Advanced Search or Access to all fields of the database in Advanced Search
Product:	[Applications] digikam	Reporter:	Johannes <johannes.lists+bugs.kde.org>
Component:	Searches-Advanced	Assignee:	Digikam Developers <digikam-bugs-null>
Status:	REPORTED ---
Severity:	wishlist	CC:	bruno.quilling, caulier.gilles, metzpinguin, shikai.xu
Priority:	NOR
Version First Reported In:	7.2.0
Target Milestone:	---
Platform:	Other
OS:	All
Latest Commit:		Version Fixed/Implemented In:
Sentry Crash Report:

Description Johannes 2020-11-01 09:26:48 UTC

SUMMARY
I would like to directly query the database and let me show digiKam the resulting images.

For example I would like to search for recently added images like
 SELECT * FROM Images ORDER BY id DESC LIMIT 100


Or list all Images in a given directory with more then 5 recognized faces:

SELECT imageid,  COUNT(*) as c, AlbumRoots.specificPath || Albums.relativePath || '/'|| Images.name  AS "Pfad" FROM ImageTagProperties
LEFT JOIN Images ON Images.id = ImageTagProperties.imageid LEFT JOIN Albums ON Albums.id = Images.album LEFT JOIN AlbumRoots On AlbumRoots.id = Albums.albumRoot
WHERE album in ( select id from Albums 
where relativePath like '/ExampleDirectory%')


GROUP BY 
	imageid
HAVING 
	c > 5
ORDER BY 
	c DESC	


Do you see a way how to implement that or is there another way to archive my request?

Comment 1 caulier.gilles 2020-11-01 10:39:52 UTC

Hi,

I'm not favourable to open search tool to direct SQL queries. This can be dangerous as user can break database structure by this way.

Note: with Python, you can to be connected to the database with a stand alone tool and interrogate the database outside digiKam, to list files.

Best

Gilles Caulier

Comment 2 Johannes 2020-11-01 14:52:56 UTC

With python it's possible to query the database and show the resulting files in digiKam? Can you point me to further information about it, please? Thanks a lot!

I understand that direct SQL queries can be dangerous if UPDATE and DELETE are supported. I was wondering if there might be a way to only allow SELECT queries.

Comment 3 caulier.gilles 2020-11-01 16:49:45 UTC

Look this repository on Github for example :

https://github.com/looran/digikuery

Gilles Caulier

Comment 4 Johannes 2020-11-02 05:41:41 UTC

Thanks for your response but I can't figure out how to use that to let digKam display the resulting images of an external query.

Comment 5 Johannes 2021-06-20 19:17:16 UTC

(In reply to caulier.gilles from comment #1)
 
> I'm not favourable to open search tool to direct SQL queries. This can be
> dangerous as user can break database structure by this way.


What about giving users the opportunity to only write the WHERE-part of the SQL directly in digiKam?

This would allow a lot more flexibility which seems to be not that easy to implement in the Advanced Search dialog.

Comment 6 Maik Qualmann 2023-08-24 16:31:08 UTC

*** Bug 473731 has been marked as a duplicate of this bug. ***

Comment 7 Bruno 2024-01-10 17:14:54 UTC

(In reply to Johannes from comment #5)
> (In reply to caulier.gilles from comment #1)
>  
> > I'm not favourable to open search tool to direct SQL queries. This can be
> > dangerous as user can break database structure by this way.
> 
> 
> What about giving users the opportunity to only write the WHERE-part of the
> SQL directly in digiKam?
> 
> This would allow a lot more flexibility which seems to be not that easy to
> implement in the Advanced Search dialog.

I am also interested, due to the current unknown behaviour when combining OR and AND search groups... I'd like to build query with parenthesis and prioritised combination of AND / OR conditions.