This project is read-only.

Dealing with Smart Albums in PSE6

Sep 15, 2008 at 6:41 PM
I like the idea of the "Last 6 Months " Smart Album In PSE6 but the time frame is too long for me.  I see where you can change the name of this "Album" but not where one can change the select that creates it.  I Know I can create a smart album in PSE6 to Select, for example the "Last 6 Days" but I don't know how to parse the blob that holds the info about the select.  As a compromise, my intent is to populate the "Last 6 Months" album in P2G2 and then add a filter in the UI to let the user set the time frame for the upload.

The first part I have done by executing a second select on the catalog.psedb and appending the resulting datatable  to the one created from  primary select in PopulateDtMediaAlbumInfo() (Which I have also modified to include the "search_date_begin" field-  see my previous post) before populating the tree and checking for previous upload.  
First, I initialized more variables near the top of PopulateCollectionTree(
            intHiddenTagId = GetTagId(strTNHidden, strTagTypeNameHidden);
            intRatingMetadataId = getMetadataId(strRatingMetadataIdentifier);
            intNotesMetadataid = getMetadataId(strNotesMetadataIdentifier);
            intCaptionMetadataId = getMetadataId(strCaptionMetadataIdentifier);
            intSmartCollTopLevelId = GetTagId(strTNSmartColl, 0);
            intSmartCollFirstLevelId = GetTagId(intSmartCollTopLevelId, 0);// the "Last 6 Months" node
            /*
            20080912 wcf Note that the name "Last 6 Months" can be changed in PSE6, but I don't see anywhere to change the Select that creates it
            so I have left the treatment here to 6 months
            */
            intCollTopLevelId = GetTagId(strTNColl, 0);

 

ThenI added this procedure (mostly from the P2G2 distro)

        public void GetMostRecentAlbum(ref DataTable dtMostRecent)
        {
            //20080906 wcf Add itemt to the smart album Last 6 Months, then in form, allow user to filter that
            string strEarliestDate = DateTime.Now.AddMonths(-6).ToString("yyyyMMdd") + "T000000";
            string sqlStatement = string.Format(
                "SELECT distinct mt.id as id, filename_search_index, volume_id, media_id, {0} as tag_id, 0 as media_index,  'Last 6 Months' as name, 'smartcollect' as 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 = {1}) 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 = {2}) 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 = {3}) as caption, 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 = {4}) 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) " +
        "AND mt.search_date_begin > '{5}' " +
        "order by mt.search_date_begin desc", intSmartCollFirstLevelId, intRatingMetadataId, intNotesMetadataid, intCaptionMetadataId, intHiddenTagId, strEarliestDate);

            try
            {
                SQLiteDataAdapter daAlbumImage = new SQLiteDataAdapter(sqlStatement, PSEConn);
                daAlbumImage.Fill(dtMostRecent);
            }
            catch (Exception exc)
            {
                MessageBox.Show("Error while excecuting SQL statement: " + sqlStatement +
                    "  Exception: " + exc.Message);
                Trace.WriteLine("Error in PopulateMostRecentAlbum while excecuting SQL Statement: >" + sqlStatement + "< " +
                    " Exception: " + exc.Message);
            }

        }


Finally , just before the line :
 int albumImageCount = dtAlbumImage.Rows.Count;
 in PopulateDtMediaAlbumInfo()

I added this:
            //////////20080911 wcf
            DataTable dtMostRecent = new DataTable();
            GetMostRecentAlbum(ref dtMostRecent);
            // DataTable dtAlbumImage = new DataTable();
            foreach (DataRow rows in dtMostRecent.Rows)
            {
                dtAlbumImage.NewRow();
                dtAlbumImage.ImportRow(rows);
            }
            ////////////


And the "Last 6 Months" album seems to work like the rest.

Sep 15, 2008 at 11:31 PM
Edited Sep 15, 2008 at 11:51 PM
Great. I'm thinking after all these changes, it is probably best you make the patch yourself. I'm looking forward to using these fixes/features now.

-- Edited (Google Chrome made a mess of my origianl reply) --