Exploring the PSE6 Database

Curiosity has got the better of me, and I needed to see a little bit more of the Photoshop Elements 6 (PSE6) database.

You can use standard SQL to poke around, but for simple things I found a great tool in SQLite Expert Personal. It's a simple database exploration tool. The main screen looks like this:




Naturally, I also needed to try some SQL too. This query retrieves the Star rating (from 0 to 5) for all images:

select media.id as recid, media.full_filepath
, mdint.value as rating
from media_table as media
join media_to_metadata_table as mm on media.id = mm.media_id
join metadata_integer_table as mdint on mm.metadata_id = mdint.id
join metadata_description_table as mdesc on mdint.description_id = mdesc.id
where mdesc.identifier = "xmp:Rating"
and mime_type = "image/jpeg"

Another test query is to retrieve the user tags for an image:

select media.id as recid, tt.name
from media_table as media
join tag_to_media_table as t2m on media.id = t2m.media_id
join tag_table as tt on t2m.tag_id = tt.id
where media.id = 276
and type_name like 'user%'

I think that I'll put these to further use soon.