picture-db
picture-db stores metadata of photo collections in a sqlite database.
This allows querying photo metadata via SQL.
Usage
Create or update database
picture-db index
picture-db index /path/to/photo/collection
Run SQL queries
picture-db sql 'SELECT * FROM pictures'
picture-db sql 'SELECT path FROM pictures WHERE rating >= 4'
picture-db sql 'SELECT path FROM pictures NATURAL JOIN picture_tags WHERE tag = "food"'
picture-db sql 'SELECT tag, COUNT(*) FROM picture_tags GROUP BY tag ORDER BY COUNT(*) DESC'
picture-db photoprism \
--url 'http://localhost:8080' --user 'admin' --pass 'admin' \
'SELECT json_path->>6 AS album, path FROM pictures WHERE rating >= 4'
picture-db photoprism \
--url 'http://localhost:8080' --user 'admin' --pass 'admin' \
'SELECT "Favorites" AS album, path, SUBSTR(path, 44) AS photoprism_path FROM pictures WHERE rating = 5'
Using a configuration file
picture-db --config picturedb.json ...
Table Schema
CREATE TABLE `pictures` (
`path` text NOT NULL,
`json_path` text NOT NULL,
`dir` text NOT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
`make` text,
`model` text,
`date_time_original` datetime,
`rating` integer,
PRIMARY KEY (`path`)
);
CREATE TABLE `picture_tags` (
`path` text NOT NULL,
`tag` text NOT NULL,
PRIMARY KEY (`path`,`tag`),
CONSTRAINT `fk_pictures_tags` FOREIGN KEY (`path`) REFERENCES `pictures`(`path`) ON DELETE CASCADE
);