Commits

Dan Connolly committed 5d35e71 Draft

explored iPhotoMain.db esp. cameras

Comments (0)

Files changed (2)

iphoto-db-explore.sql

+-- albums
+select primaryKey, name, uid
+from sqalbum;
+
+select *
+-- primaryKey, corename, filterArrayData
+
+from sqfilteralbum;
+
+select a.primaryKey albumKey, a.name album_name
+           , datetime(ph.photoDate +  julianday('2000-01-01 00:00:00')) as date -- factor out photos view?
+	   	   , ph.archiveFilename
+	   , ph.caption
+	   , ph.comments
+from albumsphotosjoin aph
+join sqalbum a on a.primaryKey = aph.sqAlbum
+join sqphotoinfo ph on ph.primaryKey = aph.sqPhotoInfo
+order by albumKey desc;
+
+-- faces... separate db.
+-- places
+
+
+select
+p.primaryKey, dpn.string, p.latitude, p.longitude, p.parent
+
+from sqplace p
+left join sqplacename dpn
+   on dpn.place = p.defaultName
+  and dpn.language='en'
+ ;
+
+ select * from sqplace;
+ 
+ select * from sqplacename
+ where language='en';
+ select * from squserplace;
+ 
+ select distinct language from sqplacename;
+ 
+-- cameras
+select qty,
+ datetime(min_date +  julianday('2000-01-01 00:00:00')) min_date,
+ datetime(max_date +  julianday('2000-01-01 00:00:00')) max_date,
+ cameraModel from (
+select count(*) qty, min(photoDate) min_date, max(photoDate) max_date, cameraModel
+ from
+sqphotoinfo
+where photoDate >  julianday('1993-01-01') - julianday('2000-01-01 00:00:00')
+group by cameraModel
+)
+order by 1 desc;
+
+-- photos by month and camera model
+select count(*), m, cameraModel from (
+select substr(t, 1, 7) m, cameraModel from (
+select datetime(photoDate + julianday('2000-01-01 00:00:00')) t, cameraModel
+from sqphotoinfo
+)
+)
+group by m, cameraModel
+-- having count(*) > 20
+order by  m, 1 desc
+;
+
+select count(*), pfx from (
+select substr(archiveFilename, 1, 3) pfx
+from sqphotoinfo
+)
+group by pfx
+order by 1 desc;
+
+
+-- photos
+
+select ph.primaryKey
+          , ph.cameraModel -- seems to be null for scans. oops... and MVC
+	   , ph.ranking
+	   -- ack http://tumblr.maccodemonkey.com/post/232335632/date-format-used-in-iphotomain-db-database
+	   , datetime(ph.photoDate +  julianday('2000-01-01 00:00:00')) as date
+	   , archiveFilename
+	   , ph.caption
+	   , ph.comments
+	   , ph.originalEvent
+	   , ph.event
+	   -- location
+	   , e.name
+	   , fi.imageWidth
+	   , fi.imageHeight
+	   , fi.rotation
+	   , fi.fileSize
+	   , fi.*
+	   , f.relativePath
+	   , f.aliasPath
+from sqphotoinfo ph
+left join sqevent e
+            on ph.event = e.primaryKey
+left join sqfileimage fi
+on fi.photoKey = ph.primaryKey
+left join sqfileinfo f
+on f.primaryKey = fi.sqFileInfo
+-- where cameraModel = 'SLIMLINE X5'
+-- where cameraModel = 'Canon PowerShot SD1100 IS'
+-- where archiveFilename like 'MVC%'
+order by
+-- archivefilename
+-- ranking desc,
+-- primaryKey
+ photoDate
+;
+
+-- keywords
+select kw.title
+           , datetime(ph.photoDate +  julianday('2000-01-01 00:00:00')) as date -- factor out photos view?
+	   	   , archiveFilename
+	   , ph.caption
+	   , ph.comments
+	   , f.*
+from keywordsphotosjoin kwph
+join sqkeyword kw on kw.primaryKey = kwph.sqKeyword
+join sqphotoinfo ph on ph.primaryKey = kwph.sqPhotoInfo
+left join sqfileimage fi
+on fi.photoKey = ph.primaryKey
+left join sqfileinfo f
+on f.primaryKey = fi.sqFileInfo
+where kw.title in ('Movie')
+;
+
+select * from (
+select count(*), kwph.sqKeyword
+from keywordsphotosjoin kwph
+group by kwph.sqKeyword
+) kwph
+join sqkeyword kw on kw.primaryKey = kwph.sqKeyword
+order by title;
+
 class MusicManager(hMedia):
     thead = ['<table>',
              '<tr><th>Name</th><th>Time</th><th>Artist</th>',
-             '<th>Album</th><th>Track</th><th>Genre</th></tr>']
+             '<th>Album</th><th>Track</th><th>Released</th><th>Genre</th></tr>']
 
     def __init__(self, doc):
         hMedia.__init__(self, doc)
         return (('<tr><td>%(fn)s</td><td>%(duration)s</td>'
                  '<td>%(contributor)s</td>'
                  '<td>%(album)s</td><td>%(position)s</td>'
+                 '<td>%(published)s</td>'
                  '<td>%(category)s</td></tr>') % item
                  ).encode('utf-8')