--- a/imagehandlingutilities/thumbnailmanager/thumbnailserver/inc/thumbnailsql.h Fri Mar 19 09:35:30 2010 +0200
+++ b/imagehandlingutilities/thumbnailmanager/thumbnailserver/inc/thumbnailsql.h Fri Apr 16 15:16:16 2010 +0300
@@ -19,7 +19,7 @@
#ifndef THUMBNAILSQL_H
#define THUMBNAILSQL_H
-//main table
+// main table
_LIT8( KThumbnailCreateInfoTable, "CREATE TABLE ThumbnailInfo ("
// Assosiacted object in file system
"Path TEXT COLLATE NOCASE,"
@@ -54,7 +54,7 @@
// If Thumbnail is stored in SQL then Data is NOT NULL
"Data BLOB);");
-//temp table is identical to actual main table except it's not persistent
+// temp table is identical to actual main table except it's not persistent
_LIT8( KThumbnailCreateTempInfoTable, "CREATE TEMP TABLE TempThumbnailInfo ("
"Path TEXT COLLATE NOCASE,"
"TNId INTEGER,"
@@ -75,7 +75,7 @@
// If Thumbnail is stored in SQL then Data is NOT NULL
"Data BLOB);");
-//version table
+// version table
_LIT8( KThumbnailVersionTable, "CREATE TABLE ThumbnailVersion ("
"Major INTEGER,"
"Minor INTEGER,"
@@ -86,60 +86,11 @@
// Assosiacted object in file system
"Path TEXT UNIQUE COLLATE NOCASE);");
-//Create index for speedup DB searches
+// indexes
_LIT8( KThumbnailCreateInfoTableIndex1, "CREATE INDEX idx1 ON ThumbnailInfo(Path, Size);");
_LIT8( KThumbnailCreateDeletedTableIndex, "CREATE INDEX idx4 ON ThumbnailDeleted(Path);");
-_LIT8( KThumbnailMoveFromTempInfoToMainTable, "INSERT INTO ThumbnailInfo SELECT * FROM TempThumbnailInfo;");
-_LIT8( KThumbnailMoveFromTempDataToMainTable, "INSERT INTO ThumbnailInfoData SELECT * FROM TempThumbnailInfoData;");
-
-_LIT8( KThumbnailDeleteFromTempInfoTable, "DELETE FROM TempThumbnailInfo;");
-_LIT8( KThumbnailDeleteFromTempDataTable, "DELETE FROM TempThumbnailInfoData;");
-
-_LIT8( KThumbnailCreateSettingsTable, "CREATE TABLE ThumbnailSettings ("
- "Version INTEGER);" );
-
-_LIT8( KThumbnailDropInfoTable, "DROP TABLE ThumbnailInfo;" );
-_LIT8( KThumbnailDropTempInfoTable, "DROP TABLE TempThumbnailInfo;" );
-
-_LIT8( KThumbnailDropSettingsTable, "DROP TABLE ThumbnailSettings;" );
-
-_LIT8( KThumbnailBeginTransaction, "BEGIN TRANSACTION;" );
-_LIT8( KThumbnailCommitTransaction, "COMMIT;" );
-_LIT8( KThumbnailRollbackTransaction, "ROLLBACK;" );
-
-_LIT8( KThumbnailInsertThumbnailInfoByPathAndId, "INSERT INTO TempThumbnailInfo "
- "(Path,Size,Format,Width,Height,OrigWidth,OrigHeight,Flags,Orientation,ThumbFromPath,Modified) ""VALUES "
- "(:Path,:Size,:Format,:Width,:Height,:OrigWidth,:OrigHeight,:Flags,:Orient,:ThumbFromPath,:Modified);" );
-
-_LIT8( KThumbnailInsertTempThumbnailInfoData, "INSERT INTO TempThumbnailInfoData (Data) VALUES (:Data);" );
-
-_LIT8( KThumbnailSelectSizeByPath, "SELECT Size FROM ThumbnailInfo "
- "WHERE NOT EXISTS (SELECT Path FROM ThumbnailDeleted "
- "WHERE ThumbnailInfo.Path = ThumbnailDeleted.Path) "
- "AND Path = :Path ORDER BY Size DESC;" );
-
-_LIT8( KThumbnailSelectTempSizeByPath, "SELECT Size FROM TempThumbnailInfo WHERE Path = :Path ORDER BY Size DESC;" );
-
-//query by Path
-_LIT8( KThumbnailSelectInfoByPath, "SELECT ThumbnailInfo.Format, ThumbnailInfoData.Data, ThumbnailInfo.Width, ThumbnailInfo.Height, ThumbnailInfo.Flags "
- "FROM ThumbnailInfo "
- "JOIN ThumbnailInfoData "
- "ON ThumbnailInfo.RowID = ThumbnailInfoData.RowID "
- "WHERE NOT EXISTS (SELECT Path FROM ThumbnailDeleted "
- "WHERE ThumbnailInfo.Path = ThumbnailDeleted.Path) "
- "AND ThumbnailInfo.Path = :Path AND ThumbnailInfo.Size = :Size;");
-
-_LIT8( KThumbnailSelectTempInfoByPath, "SELECT TempThumbnailInfo.Format, TempThumbnailInfoData.Data, TempThumbnailInfo.Width, TempThumbnailInfo.Height, TempThumbnailInfo.Flags "
- "FROM TempThumbnailInfo "
- "JOIN TempThumbnailInfoData "
- "ON TempThumbnailInfo.RowID = TempThumbnailInfoData.RowID "
- "WHERE TempThumbnailInfo.Path = :Path AND TempThumbnailInfo.Size = :Size;");
-
-
-_LIT8( KThumbnailSelectSettings, "SELECT Version FROM ThumbnailSettings;" );
-
-
+// parameters
_LIT( KThumbnailSqlParamData, ":Data" );
_LIT( KThumbnailSqlParamFlags, ":Flags" );
_LIT( KThumbnailSqlParamPath, ":Path" );
@@ -163,7 +114,68 @@
_LIT( KThumbnailSqlParamFlag, ":Flag" );
_LIT( KThumbnailSqlParamLimit, ":Limit" );
-//Delete by path
+// transaction
+_LIT8( KThumbnailBeginTransaction, "BEGIN TRANSACTION;" );
+_LIT8( KThumbnailCommitTransaction, "COMMIT;" );
+_LIT8( KThumbnailRollbackTransaction, "ROLLBACK;" );
+
+// version
+_LIT8( KThumbnailInsertToVersion, "INSERT INTO ThumbnailVersion (IMEI, Minor, Major) VALUES (:IMEI, :Minor,:Major);" );
+_LIT8( KThumbnailSelectFromVersion, "SELECT * FROM ThumbnailVersion LIMIT 1" );
+
+// IMEI
+_LIT8( KThumbnailUpdateIMEI, "UPDATE ThumbnailVersion SET IMEI = :IMEI" );
+
+// rowIDs
+_LIT8 ( KGetInfoRowID, "SELECT MAX (ThumbnailInfo.rowID) FROM ThumbnailInfo" );
+_LIT8 ( KGetDataRowID, "SELECT MAX (ThumbnailInfoData.rowID) FROM ThumbnailInfoData" );
+
+// flush
+_LIT8( KThumbnailMoveFromTempInfoToMainTable, "INSERT INTO ThumbnailInfo SELECT * FROM TempThumbnailInfo;");
+_LIT8( KThumbnailMoveFromTempDataToMainTable, "INSERT INTO ThumbnailInfoData SELECT * FROM TempThumbnailInfoData;");
+_LIT8( KThumbnailDeleteFromTempInfoTable, "DELETE FROM TempThumbnailInfo;");
+_LIT8( KThumbnailDeleteFromTempDataTable, "DELETE FROM TempThumbnailInfoData;");
+
+// store thumb
+_LIT8( KThumbnailInsertTempThumbnailInfo, "INSERT INTO TempThumbnailInfo "
+ "(Path,Size,Format,Width,Height,OrigWidth,OrigHeight,Flags,Orientation,ThumbFromPath,Modified) "
+ "VALUES "
+ "(:Path,:Size,:Format,:Width,:Height,:OrigWidth,:OrigHeight,:Flags,:Orient,:ThumbFromPath,:Modified);" );
+
+_LIT8( KThumbnailInsertTempThumbnailInfoData, "INSERT INTO TempThumbnailInfoData (Data) VALUES (:Data);" );
+
+// duplicate check
+_LIT8 ( KThumbnailTempFindDuplicate, "SELECT Path FROM TempThumbnailInfo WHERE Path = :Path AND Size = :Size;" );
+_LIT8 ( KThumbnailFindDuplicate, "SELECT Path FROM ThumbnailInfo WHERE Path = :Path AND Size = :Size;" );
+
+// select size
+_LIT8( KThumbnailSelectSizeByPath, "SELECT Size FROM ThumbnailInfo "
+ "WHERE NOT EXISTS (SELECT Path FROM ThumbnailDeleted "
+ "WHERE ThumbnailInfo.Path = ThumbnailDeleted.Path) "
+ "AND Path = :Path ORDER BY Size DESC;" );
+
+_LIT8( KThumbnailSelectTempSizeByPath, "SELECT Size FROM TempThumbnailInfo WHERE Path = :Path ORDER BY Size DESC;" );
+
+// select timestamp
+_LIT8( KThumbnailSelectModifiedByPath, "SELECT Modified FROM ThumbnailInfo WHERE Path = :Path" );
+_LIT8( KThumbnailSelectTempModifiedByPath, "SELECT Modified FROM TempThumbnailInfo WHERE Path = :Path");
+
+// select thumb
+_LIT8( KThumbnailSelectInfoByPath, "SELECT ThumbnailInfo.Format, ThumbnailInfoData.Data, ThumbnailInfo.Width, ThumbnailInfo.Height, ThumbnailInfo.Flags "
+ "FROM ThumbnailInfo "
+ "JOIN ThumbnailInfoData "
+ "ON ThumbnailInfo.RowID = ThumbnailInfoData.RowID "
+ "WHERE NOT EXISTS (SELECT Path FROM ThumbnailDeleted "
+ "WHERE ThumbnailInfo.Path = ThumbnailDeleted.Path) "
+ "AND ThumbnailInfo.Path = :Path AND ThumbnailInfo.Size = :Size;");
+
+_LIT8( KThumbnailSelectTempInfoByPath, "SELECT TempThumbnailInfo.Format, TempThumbnailInfoData.Data, TempThumbnailInfo.Width, TempThumbnailInfo.Height, TempThumbnailInfo.Flags "
+ "FROM TempThumbnailInfo "
+ "JOIN TempThumbnailInfoData "
+ "ON TempThumbnailInfo.RowID = TempThumbnailInfoData.RowID "
+ "WHERE TempThumbnailInfo.Path = :Path AND TempThumbnailInfo.Size = :Size;");
+
+// delete thumb
_LIT8( KThumbnailSqlSelectRowIDInfoByPath, "SELECT ThumbnailInfo.RowID FROM ThumbnailInfo WHERE Path = :Path;" );
_LIT8( KThumbnailSqlDeleteInfoByPath, "DELETE FROM ThumbnailInfo WHERE ThumbnailInfo.RowID = :RowID;" );
_LIT8( KThumbnailSqlDeleteInfoDataByPath, "DELETE FROM ThumbnailInfoData WHERE ThumbnailInfoData.RowID = :RowID;" );
@@ -171,46 +183,23 @@
_LIT8( KTempThumbnailSqlDeleteInfoByPath, "DELETE FROM TempThumbnailInfo WHERE TempThumbnailInfo.RowID = :RowID;" );
_LIT8( KTempThumbnailSqlDeleteInfoDataByPath, "DELETE FROM TempThumbnailInfoData WHERE TempThumbnailInfoData.RowID = :RowID;" );
-
-// insert to deleted
+// mark deleted
_LIT8( KThumbnailSqlInsertDeleted, "INSERT INTO ThumbnailDeleted (Path) VALUES (:Path);" );
-_LIT8 ( KThumbnailSqlFindDeleted, "SELECT * FROM ThumbnailDeleted WHERE Path = :Path;" );
+_LIT8( KThumbnailSqlFindDeleted, "SELECT * FROM ThumbnailDeleted WHERE Path = :Path;" );
// delete marked
_LIT8( KThumbnailSqlSelectMarked, "SELECT ThumbnailInfo.RowID FROM ThumbnailInfo "
"WHERE EXISTS (SELECT Path FROM ThumbnailDeleted "
"WHERE ThumbnailInfo.Path = ThumbnailDeleted.Path) LIMIT :Limit;" );
+
_LIT8( KThumbnailSqlDeleteInfoByRowID, "DELETE FROM ThumbnailInfo WHERE ThumbnailInfo.RowID = :RowID;" );
_LIT8( KThumbnailSqlDeleteInfoDataByRowID, "DELETE FROM ThumbnailInfoData WHERE ThumbnailInfoData.RowID = :RowID;" );
+
_LIT8( KThumbnailSqlDeleteFromDeleted, "DELETE FROM ThumbnailDeleted "
"WHERE NOT EXISTS (SELECT Path FROM ThumbnailInfo "
"WHERE ThumbnailDeleted.Path = ThumbnailInfo.Path);" );
-
-//version commands
-_LIT8( KThumbnailInsertToVersion, "INSERT INTO ThumbnailVersion (IMEI, Minor, Major) VALUES (:IMEI, :Minor,:Major);" );
-_LIT8( KThumbnailSelectFromVersion, "SELECT * FROM ThumbnailVersion LIMIT 1" );
-
-//reset IDs
-_LIT8( KTempThumbnailResetIDs, "UPDATE TempThumbnailInfo SET TNId = NULL WHERE TNId NOT NULL" );
-_LIT8( KThumbnailResetIDs, "UPDATE ThumbnailInfo SET TNId = NULL WHERE TNId NOT NULL" );
-
-//update IMEI
-_LIT8( KThumbnailUpdateIMEI, "UPDATE ThumbnailVersion SET IMEI = :IMEI" );
-
-//query Modification timestamp by path
-_LIT8( KThumbnailSelectModifiedByPath, "SELECT Modified FROM ThumbnailInfo WHERE Path = :Path" );
-_LIT8( KThumbnailSelectTempModifiedByPath, "SELECT Modified FROM TempThumbnailInfo WHERE Path = :Path");
-
-// query possible duplicates
-_LIT8 ( KTempFindDuplicate, "SELECT Path FROM TempThumbnailInfo WHERE Path = :Path AND Size = :Size;" );
-_LIT8 ( KFindDuplicate, "SELECT Path FROM ThumbnailInfo WHERE Path = :Path AND Size = :Size;" );
-
-// check rowIDs
-_LIT8 ( KGetInfoRowID, "SELECT MAX (ThumbnailInfo.rowID) FROM ThumbnailInfo" );
-_LIT8 ( KGetDataRowID, "SELECT MAX (ThumbnailInfoData.rowID) FROM ThumbnailInfoData" );
-
-//remove KThumbnailDbFlagBlacklisted flag
+// reset blacklisted
_LIT8( KThumbnailTouchBlacklistedRows, "UPDATE ThumbnailInfo SET Modified = 0 WHERE Flags & :Flag" );
// existence check