Mercurial Mercurial > oss > MCL > sf > mw > imghandling / comparison
summary | shortlog | changelog | graph | tags | bookmarks | branches | files | changeset | file | latest | revisions | annotate | diff | comparison | raw | help
Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
imagehandlingutilities/thumbnailmanager/thumbnailserver/inc/thumbnailsql.h
branchRCL_3
changeset 9 dea39715fc05
parent 7 2eb74cf6572e
child 10 ee674526fac5
equal deleted inserted replaced
8:7403edfcf0fb 9:dea39715fc05 
    17 
    17 
    18 
    18 
    19 #ifndef THUMBNAILSQL_H
 
    19 #ifndef THUMBNAILSQL_H
 
    20 #define THUMBNAILSQL_H
 
    20 #define THUMBNAILSQL_H
 
    21 
    21 
    22 //main table
 
    22 // main table
 
    23 _LIT8( KThumbnailCreateInfoTable, "CREATE TABLE ThumbnailInfo ("
 
    23 _LIT8( KThumbnailCreateInfoTable, "CREATE TABLE ThumbnailInfo ("
 
    24         // Assosiacted object in file system
 
    24         // Assosiacted object in file system
 
    25         "Path TEXT COLLATE NOCASE,"
 
    25         "Path TEXT COLLATE NOCASE,"
 
    26         // Associated (MDS) Id 
    26         // Associated (MDS) Id 
    27         "TNId INTEGER,"
 
    27         "TNId INTEGER,"
 
    52         
    52         
    53 _LIT8( KThumbnailCreateInfoDataTable, "CREATE TABLE ThumbnailInfoData ("
 
    53 _LIT8( KThumbnailCreateInfoDataTable, "CREATE TABLE ThumbnailInfoData ("
 
    54         // If Thumbnail is stored in SQL then Data is NOT NULL
 
    54         // If Thumbnail is stored in SQL then Data is NOT NULL
 
    55         "Data BLOB);");
 
    55         "Data BLOB);");
 
    56 
    56 
    57 //temp table is identical to actual main table except it's not persistent
 
    57 // temp table is identical to actual main table except it's not persistent
 
    58 _LIT8( KThumbnailCreateTempInfoTable, "CREATE TEMP TABLE TempThumbnailInfo ("
 
    58 _LIT8( KThumbnailCreateTempInfoTable, "CREATE TEMP TABLE TempThumbnailInfo ("
 
    59         "Path TEXT COLLATE NOCASE,"
 
    59         "Path TEXT COLLATE NOCASE,"
 
    60         "TNId INTEGER,"
 
    60         "TNId INTEGER,"
 
    61         "Size INTEGER,"
 
    61         "Size INTEGER,"
 
    62         "Format INTEGER,"
 
    62         "Format INTEGER,"
 
    73 
    73 
    74 _LIT8( KThumbnailCreateTempInfoDataTable, "CREATE TEMP TABLE TempThumbnailInfoData ("
 
    74 _LIT8( KThumbnailCreateTempInfoDataTable, "CREATE TEMP TABLE TempThumbnailInfoData ("
 
    75         // If Thumbnail is stored in SQL then Data is NOT NULL
 
    75         // If Thumbnail is stored in SQL then Data is NOT NULL
 
    76         "Data BLOB);");
 
    76         "Data BLOB);");
 
    77 
    77 
    78 //version table
 
    78 // version table
 
    79 _LIT8( KThumbnailVersionTable, "CREATE TABLE ThumbnailVersion ("
 
    79 _LIT8( KThumbnailVersionTable, "CREATE TABLE ThumbnailVersion ("
 
    80         "Major INTEGER,"
 
    80         "Major INTEGER,"
 
    81         "Minor INTEGER,"
 
    81         "Minor INTEGER,"
 
    82         "IMEI TEXT COLLATE NOCASE);");
 
    82         "IMEI TEXT COLLATE NOCASE);");
 
    83 
    83 
    84 // deleted thumbs table
 
    84 // deleted thumbs table
 
    85 _LIT8( KThumbnailDeletedTable, "CREATE TABLE ThumbnailDeleted ("
 
    85 _LIT8( KThumbnailDeletedTable, "CREATE TABLE ThumbnailDeleted ("
 
    86         // Assosiacted object in file system
 
    86         // Assosiacted object in file system
 
    87         "Path TEXT UNIQUE COLLATE NOCASE);");
 
    87         "Path TEXT UNIQUE COLLATE NOCASE);");
 
    88 
    88 
    89 //Create index for speedup DB searches 
    89 // indexes
 
    90 _LIT8( KThumbnailCreateInfoTableIndex1, "CREATE INDEX idx1 ON ThumbnailInfo(Path, Size);");
 
    90 _LIT8( KThumbnailCreateInfoTableIndex1, "CREATE INDEX idx1 ON ThumbnailInfo(Path, Size);");
 
    91 _LIT8( KThumbnailCreateDeletedTableIndex, "CREATE INDEX idx4 ON ThumbnailDeleted(Path);");
 
    91 _LIT8( KThumbnailCreateDeletedTableIndex, "CREATE INDEX idx4 ON ThumbnailDeleted(Path);");
 
    92 
    92 
    93 _LIT8( KThumbnailMoveFromTempInfoToMainTable, "INSERT INTO ThumbnailInfo SELECT * FROM TempThumbnailInfo;");
 
    93 // parameters
 
    94 _LIT8( KThumbnailMoveFromTempDataToMainTable, "INSERT INTO ThumbnailInfoData SELECT * FROM TempThumbnailInfoData;");
 
       
    95 
       
    96 _LIT8( KThumbnailDeleteFromTempInfoTable, "DELETE FROM TempThumbnailInfo;");
 
       
    97 _LIT8( KThumbnailDeleteFromTempDataTable, "DELETE FROM TempThumbnailInfoData;");
 
       
    98 
       
    99 _LIT8( KThumbnailCreateSettingsTable, "CREATE TABLE ThumbnailSettings ("
 
       
   100     "Version INTEGER);" );
 
       
   101 
       
   102 _LIT8( KThumbnailDropInfoTable, "DROP TABLE ThumbnailInfo;" );
 
       
   103 _LIT8( KThumbnailDropTempInfoTable, "DROP TABLE TempThumbnailInfo;" );
 
       
   104 
       
   105 _LIT8( KThumbnailDropSettingsTable, "DROP TABLE ThumbnailSettings;" );
 
       
   106 
       
   107 _LIT8( KThumbnailBeginTransaction, "BEGIN TRANSACTION;" );
 
       
   108 _LIT8( KThumbnailCommitTransaction, "COMMIT;" );
 
       
   109 _LIT8( KThumbnailRollbackTransaction, "ROLLBACK;" );
 
       
   110 
       
   111 _LIT8( KThumbnailInsertThumbnailInfoByPathAndId, "INSERT INTO TempThumbnailInfo "
 
       
   112     "(Path,Size,Format,Width,Height,OrigWidth,OrigHeight,Flags,Orientation,ThumbFromPath,Modified) ""VALUES "
 
       
   113     "(:Path,:Size,:Format,:Width,:Height,:OrigWidth,:OrigHeight,:Flags,:Orient,:ThumbFromPath,:Modified);" );
 
       
   114 
       
   115 _LIT8( KThumbnailInsertTempThumbnailInfoData, "INSERT INTO TempThumbnailInfoData (Data) VALUES (:Data);" );
 
       
   116 
       
   117 _LIT8( KThumbnailSelectSizeByPath, "SELECT Size FROM ThumbnailInfo "
 
       
   118         "WHERE NOT EXISTS (SELECT Path FROM ThumbnailDeleted "
 
       
   119         "WHERE ThumbnailInfo.Path = ThumbnailDeleted.Path) "
 
       
   120         "AND Path = :Path ORDER BY Size DESC;" );
 
       
   121 
       
   122 _LIT8( KThumbnailSelectTempSizeByPath, "SELECT Size FROM TempThumbnailInfo WHERE Path = :Path ORDER BY Size DESC;" );
 
       
   123 
       
   124 //query by Path
 
       
   125 _LIT8( KThumbnailSelectInfoByPath, "SELECT ThumbnailInfo.Format, ThumbnailInfoData.Data, ThumbnailInfo.Width, ThumbnailInfo.Height, ThumbnailInfo.Flags "
 
       
   126         "FROM ThumbnailInfo "
 
       
   127         "JOIN ThumbnailInfoData "
 
       
   128         "ON ThumbnailInfo.RowID = ThumbnailInfoData.RowID "
 
       
   129         "WHERE NOT EXISTS (SELECT Path FROM ThumbnailDeleted "
 
       
   130         "WHERE ThumbnailInfo.Path = ThumbnailDeleted.Path) " 
       
   131         "AND ThumbnailInfo.Path = :Path AND ThumbnailInfo.Size = :Size;");
 
       
   132 
       
   133 _LIT8( KThumbnailSelectTempInfoByPath, "SELECT TempThumbnailInfo.Format, TempThumbnailInfoData.Data, TempThumbnailInfo.Width, TempThumbnailInfo.Height, TempThumbnailInfo.Flags "
 
       
   134         "FROM TempThumbnailInfo "
 
       
   135         "JOIN TempThumbnailInfoData "
 
       
   136         "ON TempThumbnailInfo.RowID = TempThumbnailInfoData.RowID "
 
       
   137         "WHERE TempThumbnailInfo.Path = :Path AND TempThumbnailInfo.Size = :Size;");
 
       
   138 
       
   139 
       
   140 _LIT8( KThumbnailSelectSettings, "SELECT Version FROM ThumbnailSettings;" );
 
       
   141 
       
   142 
       
   143 _LIT( KThumbnailSqlParamData, ":Data" );
 
    94 _LIT( KThumbnailSqlParamData, ":Data" );
 
   144 _LIT( KThumbnailSqlParamFlags, ":Flags" );
 
    95 _LIT( KThumbnailSqlParamFlags, ":Flags" );
 
   145 _LIT( KThumbnailSqlParamPath, ":Path" );
 
    96 _LIT( KThumbnailSqlParamPath, ":Path" );
 
   146 _LIT( KThumbnailSqlParamWidth, ":Width" );
 
    97 _LIT( KThumbnailSqlParamWidth, ":Width" );
 
   147 _LIT( KThumbnailSqlParamHeight, ":Height" );
 
    98 _LIT( KThumbnailSqlParamHeight, ":Height" );
 
   161 _LIT( KThumbnailSqlParamThumbFromPath, ":ThumbFromPath" );
 
   112 _LIT( KThumbnailSqlParamThumbFromPath, ":ThumbFromPath" );
 
   162 _LIT( KThumbnailSqlParamModified, ":Modified" );
 
   113 _LIT( KThumbnailSqlParamModified, ":Modified" );
 
   163 _LIT( KThumbnailSqlParamFlag, ":Flag" );
 
   114 _LIT( KThumbnailSqlParamFlag, ":Flag" );
 
   164 _LIT( KThumbnailSqlParamLimit, ":Limit" );
 
   115 _LIT( KThumbnailSqlParamLimit, ":Limit" );
 
   165 
   116 
   166 //Delete by path
 
   117 // transaction 
       
   118 _LIT8( KThumbnailBeginTransaction, "BEGIN TRANSACTION;" );
 
       
   119 _LIT8( KThumbnailCommitTransaction, "COMMIT;" );
 
       
   120 _LIT8( KThumbnailRollbackTransaction, "ROLLBACK;" );
 
       
   121 
       
   122 // version 
       
   123 _LIT8( KThumbnailInsertToVersion, "INSERT INTO ThumbnailVersion (IMEI, Minor, Major) VALUES (:IMEI, :Minor,:Major);" );
 
       
   124 _LIT8( KThumbnailSelectFromVersion, "SELECT * FROM ThumbnailVersion LIMIT 1" );
 
       
   125 
       
   126 // IMEI
 
       
   127 _LIT8( KThumbnailUpdateIMEI, "UPDATE ThumbnailVersion SET IMEI = :IMEI" );
 
       
   128 
       
   129 // rowIDs
 
       
   130 _LIT8 ( KGetInfoRowID, "SELECT MAX (ThumbnailInfo.rowID) FROM ThumbnailInfo" );
 
       
   131 _LIT8 ( KGetDataRowID, "SELECT MAX (ThumbnailInfoData.rowID) FROM ThumbnailInfoData" );
 
       
   132 
       
   133 // flush 
       
   134 _LIT8( KThumbnailMoveFromTempInfoToMainTable, "INSERT INTO ThumbnailInfo SELECT * FROM TempThumbnailInfo;");
 
       
   135 _LIT8( KThumbnailMoveFromTempDataToMainTable, "INSERT INTO ThumbnailInfoData SELECT * FROM TempThumbnailInfoData;");
 
       
   136 _LIT8( KThumbnailDeleteFromTempInfoTable, "DELETE FROM TempThumbnailInfo;");
 
       
   137 _LIT8( KThumbnailDeleteFromTempDataTable, "DELETE FROM TempThumbnailInfoData;");
 
       
   138 
       
   139 // store thumb
 
       
   140 _LIT8( KThumbnailInsertTempThumbnailInfo, "INSERT INTO TempThumbnailInfo "
 
       
   141         "(Path,Size,Format,Width,Height,OrigWidth,OrigHeight,Flags,Orientation,ThumbFromPath,Modified) "
 
       
   142         "VALUES "
 
       
   143         "(:Path,:Size,:Format,:Width,:Height,:OrigWidth,:OrigHeight,:Flags,:Orient,:ThumbFromPath,:Modified);" );
 
       
   144 
       
   145 _LIT8( KThumbnailInsertTempThumbnailInfoData, "INSERT INTO TempThumbnailInfoData (Data) VALUES (:Data);" );
 
       
   146 
       
   147 // duplicate check
 
       
   148 _LIT8 ( KThumbnailTempFindDuplicate, "SELECT Path FROM TempThumbnailInfo WHERE Path = :Path AND Size = :Size;" );
 
       
   149 _LIT8 ( KThumbnailFindDuplicate, "SELECT Path FROM ThumbnailInfo WHERE Path = :Path AND Size = :Size;" );
 
       
   150 
       
   151 // select size
 
       
   152 _LIT8( KThumbnailSelectSizeByPath, "SELECT Size FROM ThumbnailInfo "
 
       
   153         "WHERE NOT EXISTS (SELECT Path FROM ThumbnailDeleted "
 
       
   154         "WHERE ThumbnailInfo.Path = ThumbnailDeleted.Path) "
 
       
   155         "AND Path = :Path ORDER BY Size DESC;" );
 
       
   156 
       
   157 _LIT8( KThumbnailSelectTempSizeByPath, "SELECT Size FROM TempThumbnailInfo WHERE Path = :Path ORDER BY Size DESC;" );
 
       
   158 
       
   159 // select timestamp
 
       
   160 _LIT8( KThumbnailSelectModifiedByPath, "SELECT Modified FROM ThumbnailInfo WHERE Path = :Path"  );
 
       
   161 _LIT8( KThumbnailSelectTempModifiedByPath, "SELECT Modified FROM TempThumbnailInfo WHERE Path = :Path");
 
       
   162 
       
   163 // select thumb
 
       
   164 _LIT8( KThumbnailSelectInfoByPath, "SELECT ThumbnailInfo.Format, ThumbnailInfoData.Data, ThumbnailInfo.Width, ThumbnailInfo.Height, ThumbnailInfo.Flags "
 
       
   165         "FROM ThumbnailInfo "
 
       
   166         "JOIN ThumbnailInfoData "
 
       
   167         "ON ThumbnailInfo.RowID = ThumbnailInfoData.RowID "
 
       
   168         "WHERE NOT EXISTS (SELECT Path FROM ThumbnailDeleted "
 
       
   169         "WHERE ThumbnailInfo.Path = ThumbnailDeleted.Path) " 
       
   170         "AND ThumbnailInfo.Path = :Path AND ThumbnailInfo.Size = :Size;");
 
       
   171 
       
   172 _LIT8( KThumbnailSelectTempInfoByPath, "SELECT TempThumbnailInfo.Format, TempThumbnailInfoData.Data, TempThumbnailInfo.Width, TempThumbnailInfo.Height, TempThumbnailInfo.Flags "
 
       
   173         "FROM TempThumbnailInfo "
 
       
   174         "JOIN TempThumbnailInfoData "
 
       
   175         "ON TempThumbnailInfo.RowID = TempThumbnailInfoData.RowID "
 
       
   176         "WHERE TempThumbnailInfo.Path = :Path AND TempThumbnailInfo.Size = :Size;");
 
       
   177 
       
   178 // delete thumb
 
   167 _LIT8( KThumbnailSqlSelectRowIDInfoByPath, "SELECT ThumbnailInfo.RowID FROM ThumbnailInfo WHERE Path = :Path;" );
 
   179 _LIT8( KThumbnailSqlSelectRowIDInfoByPath, "SELECT ThumbnailInfo.RowID FROM ThumbnailInfo WHERE Path = :Path;" );
 
   168 _LIT8( KThumbnailSqlDeleteInfoByPath, "DELETE FROM ThumbnailInfo WHERE ThumbnailInfo.RowID = :RowID;" );
 
   180 _LIT8( KThumbnailSqlDeleteInfoByPath, "DELETE FROM ThumbnailInfo WHERE ThumbnailInfo.RowID = :RowID;" );
 
   169 _LIT8( KThumbnailSqlDeleteInfoDataByPath, "DELETE FROM ThumbnailInfoData WHERE ThumbnailInfoData.RowID = :RowID;" );
 
   181 _LIT8( KThumbnailSqlDeleteInfoDataByPath, "DELETE FROM ThumbnailInfoData WHERE ThumbnailInfoData.RowID = :RowID;" );
 
   170 _LIT8( KTempThumbnailSqlSelectRowIDInfoByPath, "SELECT TempThumbnailInfo.RowID FROM TempThumbnailInfo WHERE Path = :Path LIMIT 1;" );
 
   182 _LIT8( KTempThumbnailSqlSelectRowIDInfoByPath, "SELECT TempThumbnailInfo.RowID FROM TempThumbnailInfo WHERE Path = :Path LIMIT 1;" );
 
   171 _LIT8( KTempThumbnailSqlDeleteInfoByPath, "DELETE FROM TempThumbnailInfo WHERE TempThumbnailInfo.RowID = :RowID;" );
 
   183 _LIT8( KTempThumbnailSqlDeleteInfoByPath, "DELETE FROM TempThumbnailInfo WHERE TempThumbnailInfo.RowID = :RowID;" );
 
   172 _LIT8( KTempThumbnailSqlDeleteInfoDataByPath, "DELETE FROM TempThumbnailInfoData WHERE TempThumbnailInfoData.RowID = :RowID;" );
 
   184 _LIT8( KTempThumbnailSqlDeleteInfoDataByPath, "DELETE FROM TempThumbnailInfoData WHERE TempThumbnailInfoData.RowID = :RowID;" );
 
   173 
   185 
   174 
   186 // mark deleted
 
   175 // insert to deleted
 
       
   176 _LIT8( KThumbnailSqlInsertDeleted, "INSERT INTO ThumbnailDeleted (Path) VALUES (:Path);" );
 
   187 _LIT8( KThumbnailSqlInsertDeleted, "INSERT INTO ThumbnailDeleted (Path) VALUES (:Path);" );
 
   177 _LIT8 ( KThumbnailSqlFindDeleted, "SELECT * FROM ThumbnailDeleted WHERE Path = :Path;" );
 
   188 _LIT8( KThumbnailSqlFindDeleted, "SELECT * FROM ThumbnailDeleted WHERE Path = :Path;" );
 
   178 
   189 
   179 // delete marked
 
   190 // delete marked
 
   180 _LIT8( KThumbnailSqlSelectMarked, "SELECT ThumbnailInfo.RowID FROM ThumbnailInfo "
 
   191 _LIT8( KThumbnailSqlSelectMarked, "SELECT ThumbnailInfo.RowID FROM ThumbnailInfo "
 
   181         "WHERE EXISTS (SELECT Path FROM ThumbnailDeleted "
 
   192         "WHERE EXISTS (SELECT Path FROM ThumbnailDeleted "
 
   182         "WHERE ThumbnailInfo.Path = ThumbnailDeleted.Path) LIMIT :Limit;" );
 
   193         "WHERE ThumbnailInfo.Path = ThumbnailDeleted.Path) LIMIT :Limit;" );
 
       
   194 
   183 _LIT8( KThumbnailSqlDeleteInfoByRowID, "DELETE FROM ThumbnailInfo WHERE ThumbnailInfo.RowID = :RowID;" );
 
   195 _LIT8( KThumbnailSqlDeleteInfoByRowID, "DELETE FROM ThumbnailInfo WHERE ThumbnailInfo.RowID = :RowID;" );
 
   184 _LIT8( KThumbnailSqlDeleteInfoDataByRowID, "DELETE FROM ThumbnailInfoData WHERE ThumbnailInfoData.RowID = :RowID;" );
 
   196 _LIT8( KThumbnailSqlDeleteInfoDataByRowID, "DELETE FROM ThumbnailInfoData WHERE ThumbnailInfoData.RowID = :RowID;" );
 
       
   197 
   185 _LIT8( KThumbnailSqlDeleteFromDeleted, "DELETE FROM ThumbnailDeleted "
 
   198 _LIT8( KThumbnailSqlDeleteFromDeleted, "DELETE FROM ThumbnailDeleted "
 
   186         "WHERE NOT EXISTS (SELECT Path FROM ThumbnailInfo "
 
   199         "WHERE NOT EXISTS (SELECT Path FROM ThumbnailInfo "
 
   187         "WHERE ThumbnailDeleted.Path = ThumbnailInfo.Path);" );
 
   200         "WHERE ThumbnailDeleted.Path = ThumbnailInfo.Path);" );
 
   188 
   201 
   189 
   202 // reset blacklisted
 
   190 //version commands
 
       
   191 _LIT8( KThumbnailInsertToVersion, "INSERT INTO ThumbnailVersion (IMEI, Minor, Major) VALUES (:IMEI, :Minor,:Major);" );
 
       
   192 _LIT8( KThumbnailSelectFromVersion, "SELECT * FROM ThumbnailVersion LIMIT 1" );
 
       
   193 
       
   194 //reset IDs
 
       
   195 _LIT8( KTempThumbnailResetIDs, "UPDATE TempThumbnailInfo SET TNId = NULL WHERE TNId NOT NULL" );
 
       
   196 _LIT8( KThumbnailResetIDs, "UPDATE ThumbnailInfo SET TNId = NULL WHERE TNId NOT NULL" );
 
       
   197 
       
   198 //update IMEI
 
       
   199 _LIT8( KThumbnailUpdateIMEI, "UPDATE ThumbnailVersion SET IMEI = :IMEI" );
 
       
   200 
       
   201 //query Modification timestamp by path
 
       
   202 _LIT8( KThumbnailSelectModifiedByPath, "SELECT Modified FROM ThumbnailInfo WHERE Path = :Path"  );
 
       
   203 _LIT8( KThumbnailSelectTempModifiedByPath, "SELECT Modified FROM TempThumbnailInfo WHERE Path = :Path");
 
       
   204 
       
   205 // query possible duplicates
 
       
   206 _LIT8 ( KTempFindDuplicate, "SELECT Path FROM TempThumbnailInfo WHERE Path = :Path AND Size = :Size;" );
 
       
   207 _LIT8 ( KFindDuplicate, "SELECT Path FROM ThumbnailInfo WHERE Path = :Path AND Size = :Size;" );
 
       
   208 
       
   209 // check rowIDs
 
       
   210 _LIT8 ( KGetInfoRowID, "SELECT MAX (ThumbnailInfo.rowID) FROM ThumbnailInfo" );
 
       
   211 _LIT8 ( KGetDataRowID, "SELECT MAX (ThumbnailInfoData.rowID) FROM ThumbnailInfoData" );
 
       
   212 
       
   213 //remove KThumbnailDbFlagBlacklisted flag
 
       
   214 _LIT8( KThumbnailTouchBlacklistedRows, "UPDATE ThumbnailInfo SET Modified = 0 WHERE Flags & :Flag" );
 
   203 _LIT8( KThumbnailTouchBlacklistedRows, "UPDATE ThumbnailInfo SET Modified = 0 WHERE Flags & :Flag" );
 
   215 
   204 
   216 // existence check
 
   205 // existence check
 
   217 _LIT8( KThumbnailSelectAllPaths, "SELECT ThumbnailInfo.RowID,Path FROM ThumbnailInfo "
 
   206 _LIT8( KThumbnailSelectAllPaths, "SELECT ThumbnailInfo.RowID,Path FROM ThumbnailInfo "
 
   218         "WHERE NOT EXISTS (SELECT Path FROM ThumbnailDeleted "
 
   207         "WHERE NOT EXISTS (SELECT Path FROM ThumbnailDeleted "
MCL/sf/mw/imghandling