diff -r 7403edfcf0fb -r dea39715fc05 imagehandlingutilities/thumbnailmanager/thumbnailserver/inc/thumbnailsql.h --- a/imagehandlingutilities/thumbnailmanager/thumbnailserver/inc/thumbnailsql.h Mon Mar 15 12:41:55 2010 +0200 +++ b/imagehandlingutilities/thumbnailmanager/thumbnailserver/inc/thumbnailsql.h Wed Mar 31 22:04:49 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