Select Statement in PopulateDtMediaAlbumInfo()

Developer
Sep 15, 2008 at 3:46 PM
Edited Sep 15, 2008 at 6:01 PM
I had considered trying to write an interface between PSE and Gallery2 and was so pleased to find Pix2Gallery2.  Thanks so much for the great work!
I previously posted a suggested change to the select statement to find all albums and have read your (Shaggs) comment.  I am afraid I don't know how to upload a patch, but if you can point me to a How To I will attempt to do so ( ir you can make the simple change required).

This post has to do with the Select used to read all of the media info.
The Select in the distro returns about 65000 lines from my catalog.psedb including many entries which are unwanted because they are hidden or not the selected item in a version set or they refer to an upload event.  The following select returns only the lines we are interested in (34000 lines in my case), including the correct drive id (or URI if the files are on a shared network drive).

            string sqlStatement = string.Format(
"SELECT mt.id, mt.filename_search_index, mt.volume_id, t2m.media_id,  " +
"t2m.tag_id, t2m.media_index,  tt.name, tt.type_name, " +
"(select  drive_path_if_builtin as drive_location from volume_table where id = mt.volume_id AND drive_path_if_builtin > '' " +
"union select  serial || '/' as drive_location from volume_table where id = mt.volume_id AND drive_path_if_builtin = '') ||  mt.full_filepath as location, " +
"( SELECT metadata_integer_table.value FROM media_to_metadata_table INNER JOIN metadata_integer_table  " +
"ON media_to_metadata_table.metadata_id = metadata_integer_table.id WHERE media_to_metadata_table.media_id = mt.id  " +
"AND metadata_integer_table.description_id = {0}) as rating , " +
"(SELECT mst.value FROM media_to_metadata_table  mtm INNER JOIN metadata_string_table mst ON mtm.metadata_id=mst.id  " +
"WHERE mtm.media_id = mt.id AND mst.description_id = {1}) as notes, " +
"(SELECT mst.value FROM media_to_metadata_table  mtm INNER JOIN metadata_string_table mst ON mtm.metadata_id=mst.id  " +
"WHERE mtm.media_id = mt.id AND mst.description_id = {2}) as caption,mt.search_date_begin " +
"FROM media_table mt  " +
"INNER JOIN tag_to_media_table t2m on mt.id=t2m.media_id " +
"INNER JOIN tag_table tt on t2m.tag_id=tt.id " +
"where (tt.type_name = 'collection'  " +
"OR tt.type_name = 'user'  " +
"OR tt.type_name = 'collection_ns'  " +
"OR tt.type_name like 'user_p%'  " +
"OR tt.type_name like 'user_event%'  " +
"OR tt.type_name like 'user_misc%'  " +
"OR tt.type_name = 'smartcollect' ) " +
"AND (SELECT  media_id FROM tag_to_media_table WHERE  media_id = mt.id  AND tag_id = {3}) is null  " +
"AND ((SELECT media_index  FROM version_stack_to_media_table WHERE media_id = mt.id) is  null OR " +
"(SELECT media_index  FROM version_stack_to_media_table WHERE media_id = mt.id) = 0) " +
"order by tag_id, media_index", intRatingMetadataId, intNotesMetadataid, intCaptionMetadataId, intHiddenTagId);


This allows some serious  simplification of the code in PSE6Interface.cs
Note: I also added "search_date_begin" field to use in populating the "Last 6 Months" album

Developer
Sep 15, 2008 at 10:26 PM
Edited Sep 15, 2008 at 10:49 PM
Hi again winful,

Well, as your currently doing more development on this then I am, it may be easier if you join the project and do a patch yourself. The basic steps for this are:
1) Join project (I'm not 100% sure this is necessary though). I have asked the project head guy to auth you if you try.
2) Install the codeplex client (http://www.codeplex.com/CodePlexClient)
3) Download the Pix2Gallery2 project via codeplex (I think the command is cpc checkout Pix2Gallery2) . Further instructions for this can be found at http://www.codeplex.com/CodePlexClient/Wiki/View.aspx?title=Workflow&referringTitle=Home
4) Modify/add files in visual studio
5) Use the "cpc syncup" command so codeplex can find your new/modified files. (this is outlined on this page - http://www.codeplex.com/CodePlexClient/Wiki/View.aspx?title=HowToContribute&referringTitle=Home)
6) Use the makepatch command to create the patch (cpc makepatch C:\MyPatch.xml).
7) Upload patch.

I'm glad someone else has taken an interest updating Pix2Gallery2 (PSE component), as work/study commitments leaves me with little time for this at the moment).

-- Edited (Google Chrome made a mess of my post)
Developer
Sep 16, 2008 at 6:45 PM
Thanks for the info.  I notice that the codeplex client you reference is no longer being supported and it was suggested using the Tortoisesvn client  which I am trying with Visualsvn.  I have uploaded the simple patch to the select clause as a test (and discovered that I had referenced the wrong select in my first post- should be the one at line 668 not 683).  I mistakenly thought that the "Add" button next to the Workitem dialog box was to add a second  reference there, so I didn't reference that correctly. 

Please let me know if there are any problems before I attempt the more involved patches.
Thanks.
Developer
Sep 16, 2008 at 9:52 PM
Ahh ok. I guess I keep using codeplex cause that was the recommended client when I started on this.

It may take a while for a new build to get made - I dont build the releases - another guy (Ted) does that.