diff -r 2e2a89493e2b -r 48dd0f169f0d imagehandlingutilities/thumbnailmanager/thumbnailserver/inc/thumbnailsql.h --- a/imagehandlingutilities/thumbnailmanager/thumbnailserver/inc/thumbnailsql.h Fri Sep 03 10:29:37 2010 +0300 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,215 +0,0 @@ -/* -* Copyright (c) 2006-2007 Nokia Corporation and/or its subsidiary(-ies). -* All rights reserved. -* This component and the accompanying materials are made available -* under the terms of "Eclipse Public License v1.0" -* which accompanies this distribution, and is available -* at the URL "http://www.eclipse.org/legal/epl-v10.html". -* -* Initial Contributors: -* Nokia Corporation - initial contribution. -* -* Contributors: -* -* Description: SQL statements - * -*/ - - -#ifndef THUMBNAILSQL_H -#define THUMBNAILSQL_H - -// main table -_LIT8( KThumbnailCreateInfoTable, "CREATE TABLE ThumbnailInfo (" - // Assosiacted object in file system - "Path TEXT COLLATE NOCASE," - // Associated (MDS) Id - "TNId INTEGER," - // Combined (enumerated) size and classification - "Size INTEGER," - // Format of thumbnail (CFBsbitmap, JPeg...) - "Format INTEGER," - // If thumbnail is in the filesystem then this is NOT NULL and points to such file - "TNPath TEXT COLLATE NOCASE," - // Width of thumbnail - "Width INTEGER," - // Height of thumbnail - "Height INTEGER," - // Width of source - "OrigWidth INTEGER," - // Height of source - "OrigHeight INTEGER," - // Control flags, cropped etc. - "Flags INTEGER," - // Frame number of video TN when user defined - "VideoPosition INTEGER," - // Thumbnail orientation - "Orientation INTEGER," - // Set if thumbnail is actually created from the associated object path - "ThumbFromPath INTEGER," - // Last modified timestamp - "Modified LARGEINT);"); - -_LIT8( KThumbnailCreateInfoDataTable, "CREATE TABLE ThumbnailInfoData (" - // 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 -_LIT8( KThumbnailCreateTempInfoTable, "CREATE TEMP TABLE TempThumbnailInfo (" - "Path TEXT COLLATE NOCASE," - "TNId INTEGER," - "Size INTEGER," - "Format INTEGER," - "TNPath TEXT COLLATE NOCASE," - "Width INTEGER," - "Height INTEGER," - "OrigWidth INTEGER," - "OrigHeight INTEGER," - "Flags INTEGER," - "VideoPosition INTEGER," - "Orientation INTEGER," - "ThumbFromPath INTEGER," - "Modified LARGEINT);"); - -_LIT8( KThumbnailCreateTempInfoDataTable, "CREATE TEMP TABLE TempThumbnailInfoData (" - // If Thumbnail is stored in SQL then Data is NOT NULL - "Data BLOB);"); - -// version table -_LIT8( KThumbnailVersionTable, "CREATE TABLE ThumbnailVersion (" - "Major INTEGER," - "Minor INTEGER," - "IMEI TEXT COLLATE NOCASE);"); - -// deleted thumbs table -_LIT8( KThumbnailDeletedTable, "CREATE TABLE ThumbnailDeleted (" - // Assosiacted object in file system - "Path TEXT UNIQUE COLLATE NOCASE);"); - -// indexes -_LIT8( KThumbnailCreateInfoTableIndex1, "CREATE INDEX idx1 ON ThumbnailInfo(Path, Size);"); - -// parameters -_LIT( KThumbnailSqlParamData, ":Data" ); -_LIT( KThumbnailSqlParamFlags, ":Flags" ); -_LIT( KThumbnailSqlParamPath, ":Path" ); -_LIT( KThumbnailSqlParamNewPath, ":NewPath" ); -_LIT( KThumbnailSqlParamWidth, ":Width" ); -_LIT( KThumbnailSqlParamHeight, ":Height" ); -_LIT( KThumbnailSqlParamOriginalWidth, ":OrigWidth" ); -_LIT( KThumbnailSqlParamOriginalHeight, ":OrigHeight" ); -_LIT( KThumbnailSqlParamFormat, ":Format" ); -_LIT( KThumbnailSqlParamSize, ":Size" ); -_LIT( KThumbnailSqlParamTNPath, ":TNPath" ); -_LIT( KThumbnailSqlParamMajor, ":Major" ); -_LIT( KThumbnailSqlParamMinor, ":Minor" ); -_LIT( KThumbnailSqlParamImei, ":IMEI" ); -_LIT( KThumbnailSqlParamSizeImage, ":SizeImage" ); -_LIT( KThumbnailSqlParamSizeVideo, ":SizeVideo" ); -_LIT( KThumbnailSqlParamSizeAudio, ":SizeAudio" ); -_LIT( KThumbnailSqlParamRowID, ":RowID" ); -_LIT( KThumbnailSqlParamOrientation, ":Orient" ); -_LIT( KThumbnailSqlParamThumbFromPath, ":ThumbFromPath" ); -_LIT( KThumbnailSqlParamModified, ":Modified" ); -_LIT( KThumbnailSqlParamFlag, ":Flag" ); -_LIT( KThumbnailSqlParamLimit, ":Limit" ); - -// 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;" ); -_LIT8( KTempThumbnailSqlSelectRowIDInfoByPath, "SELECT TempThumbnailInfo.RowID FROM TempThumbnailInfo WHERE Path = :Path LIMIT 1;" ); -_LIT8( KTempThumbnailSqlDeleteInfoByPath, "DELETE FROM TempThumbnailInfo WHERE TempThumbnailInfo.RowID = :RowID;" ); -_LIT8( KTempThumbnailSqlDeleteInfoDataByPath, "DELETE FROM TempThumbnailInfoData WHERE TempThumbnailInfoData.RowID = :RowID;" ); - -// mark deleted -_LIT8( KThumbnailSqlInsertDeleted, "INSERT INTO ThumbnailDeleted (Path) VALUES (: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);" ); - -// rename thumb -_LIT8( KThumbnailRename, "UPDATE ThumbnailInfo SET Path = :NewPath WHERE Path = :Path;" ); -_LIT8( KThumbnailTempRename, "UPDATE TempThumbnailInfo SET Path = :NewPath WHERE Path = :Path;" ); - -// reset blacklisted -_LIT8( KThumbnailTouchBlacklistedRows, "UPDATE ThumbnailInfo SET Flags = 4, Modified = 0 WHERE Flags & :Flag" ); - -// existence check -_LIT8( KThumbnailSelectAllPaths, "SELECT ThumbnailInfo.RowID,Path FROM ThumbnailInfo " - "WHERE NOT EXISTS (SELECT Path FROM ThumbnailDeleted " - "WHERE ThumbnailInfo.Path = ThumbnailDeleted.Path) " - "AND ThumbFromPath = 1 AND ThumbnailInfo.RowID > :RowID ORDER BY ThumbnailInfo.RowID ASC LIMIT :Limit;" ); - -#endif // THUMBNAILSQL_H