Shotwell photo.db sqlite view by tag name

shotwell_photodb_viewbytag.sql

-- Shotwell photo.db SQLITE VIEW to query by tag_name
--
-- Usage after creating the view:
-- SELECT * from v_tagged_photos WHERE tag_name LIKE '%slo%';
--
-- (the view is as hellish as the db layout of Shotwell)
--
-- thx @ https://gist.github.com/ChiChou/97a53caa2c0b49c1991e?permalink_comment_id=4721301#gistcomment-4721301
-- thx @ https://stackoverflow.com/questions/46483859/split-values-in-parts-with-sqlite

CREATE TEMP VIEW IF NOT EXISTS v_tagged_photos
AS 
SELECT tTable.tag_name, pTable.* FROM (
	-- BEGIN hex2dec
	WITH 
		hexchars(key, value) AS (
			VALUES
				('0', 0),('1', 1),('2', 2),('3', 3),('4', 4),('5', 5),('6', 6),('7', 7),('8', 8),('9', 9),
				('A', 10),('B', 11),('C', 12),('D', 13),('E', 14),('F', 15),
				('a', 10),('b', 11),('c', 12),('d', 13),('e', 14),('f', 15)
		),
		-- Get all ids from table
		all_ids AS (
			-- BEGIN TagTable QUERY
			WITH RECURSIVE Splitter AS (
				SELECT
					name,
					SUBSTR(photo_id_list, 1, INSTR(photo_id_list, ',') - 1) AS part,
					SUBSTR(photo_id_list, INSTR(photo_id_list, ',') + 1) AS remainder
				FROM
					(SELECT name, photo_id_list FROM TagTable WHERE name LIKE '%slo%')
				UNION ALL
				SELECT
					name,
					SUBSTR(remainder, 1, INSTR(remainder, ',') - 1) AS part,
					SUBSTR(remainder, INSTR(remainder, ',') + 1) AS remainder
				FROM
					Splitter
				WHERE
					remainder != ''
			)
			SELECT
				name, SUBSTR(part, 6) AS hex_id
			FROM
				Splitter
			WHERE part LIKE 'thumb%'
			-- END TagTable QUERY
		)
		-- Processing each ID
	SELECT
		(
			WITH RECURSIVE
				-- Converting the part of id hex to decimal
				unhex(str, val, weight) AS (
					SELECT all_ids.hex_id, 0, 1
					UNION ALL
					SELECT 
						substr(str, 1, length(str) - 1),
						val + (select value from hexchars where key = substr(str, length(str), 1)) * weight,
						weight * 16
					FROM unhex WHERE length(str) > 0
				)
			SELECT val FROM unhex ORDER BY weight DESC LIMIT 1
		) AS decimal_id,
		name AS tag_name
	FROM
		all_ids
	-- END hex2dec
) tTable
LEFT JOIN PhotoTable pTable
	ON tTable.decimal_id=pTable.id;