1 /*

     2 * Copyright (c) 2006-2007 Nokia Corporation and/or its subsidiary(-ies). 

     3 * All rights reserved.

     4 * This component and the accompanying materials are made available

     5 * under the terms of "Eclipse Public License v1.0"

     6 * which accompanies this distribution, and is available

     7 * at the URL "http://www.eclipse.org/legal/epl-v10.html".

     8 *

     9 * Initial Contributors:

    10 * Nokia Corporation - initial contribution.

    11 *

    12 * Contributors:

    13 *

    14 * Description:  SQL statements

    15  *

    16 */

    17 

    18 

    19 #ifndef THUMBNAILSQL_H

    20 #define THUMBNAILSQL_H

    21 

    22 //main table

    23 _LIT8( KThumbnailCreateInfoTable, "CREATE TABLE ThumbnailInfo ("

    24         // Assosiacted object in file system

    25         "Path TEXT COLLATE NOCASE,"

    26         // Associated (MDS) Id 

    27         "TNId INTEGER,"

    28         // Combined (enumerated) size and classification 

    29         "Size INTEGER,"

    30         // Format of thumbnail (CFBsbitmap, JPeg...)

    31         "Format INTEGER,"

    32         // If thumbnail is in the filesystem then this is NOT NULL and points to such file

    33         "TNPath TEXT COLLATE NOCASE,"

    34         // Width of thumbnail

    35         "Width INTEGER,"

    36         // Height of thumbnail

    37         "Height INTEGER,"

    38         // Width of source

    39         "OrigWidth INTEGER,"

    40         // Height of source

    41         "OrigHeight INTEGER,"

    42         // Control flags, cropped etc.

    43         "Flags INTEGER,"

    44         // Frame number of video TN when user defined

    45         "VideoPosition INTEGER,"

    46         // Thumbnail orientation

    47         "Orientation INTEGER,"

    48         // Set if thumbnail is actually created from the associated object path

    49         "ThumbFromPath INTEGER,"

    50         // Last modified timestamp

    51         "Modified LARGEINT);");

    52         

    53 _LIT8( KThumbnailCreateInfoDataTable, "CREATE TABLE ThumbnailInfoData ("

    54         // If Thumbnail is stored in SQL then Data is NOT NULL

    55         "Data BLOB);");

    56 		

    57 //Create index for speedup DB searches 

    58 _LIT8( KThumbnailCreateInfoTableIndex1, "CREATE INDEX idx1 ON ThumbnailInfo(Path, Size);");

    59 _LIT8( KThumbnailCreateInfoTableIndex2, "CREATE INDEX idx2 ON ThumbnailInfo(TNId, Size);");

    60 

    61 //temp table is identical to actual main table except it's not persistent

    62 _LIT8( KThumbnailCreateTempInfoTable, "CREATE TEMP TABLE TempThumbnailInfo ("

    63         "Path TEXT COLLATE NOCASE,"

    64         "TNId INTEGER,"

    65         "Size INTEGER,"

    66         "Format INTEGER,"

    67         "TNPath TEXT COLLATE NOCASE,"

    68         "Width INTEGER,"

    69         "Height INTEGER,"

    70         "OrigWidth INTEGER,"

    71         "OrigHeight INTEGER,"

    72         "Flags INTEGER,"

    73         "VideoPosition INTEGER,"

    74         "Orientation INTEGER,"

    75         "ThumbFromPath INTEGER,"

    76         "Modified LARGEINT);");

    77 

    78 _LIT8( KThumbnailCreateTempInfoDataTable, "CREATE TEMP TABLE TempThumbnailInfoData ("

    79         // If Thumbnail is stored in SQL then Data is NOT NULL

    80         "Data BLOB);");

    81 

    82 //version table

    83 _LIT8( KThumbnailVersionTable, "CREATE TABLE ThumbnailVersion ("

    84         "Major INTEGER,"

    85         "Minor INTEGER,"

    86         "IMEI TEXT COLLATE NOCASE);");

    87 

    88 _LIT8( KThumbnailMoveFromTempInfoToMainTable, "INSERT INTO ThumbnailInfo SELECT * FROM TempThumbnailInfo;");

    89 _LIT8( KThumbnailMoveFromTempDataToMainTable, "INSERT INTO ThumbnailInfoData SELECT * FROM TempThumbnailInfoData;");

    90 

    91 _LIT8( KThumbnailDeleteFromTempInfoTable, "DELETE FROM TempThumbnailInfo;");

    92 _LIT8( KThumbnailDeleteFromTempDataTable, "DELETE FROM TempThumbnailInfoData;");

    93 

    94 _LIT8( KThumbnailCreateSettingsTable, "CREATE TABLE ThumbnailSettings ("

    95     "Version INTEGER);" );

    96 

    97 _LIT8( KThumbnailDropInfoTable, "DROP TABLE ThumbnailInfo;" );

    98 _LIT8( KThumbnailDropTempInfoTable, "DROP TABLE TempThumbnailInfo;" );

    99 

   100 _LIT8( KThumbnailDropSettingsTable, "DROP TABLE ThumbnailSettings;" );

   101 

   102 _LIT8( KThumbnailBeginTransaction, "BEGIN TRANSACTION;" );

   103 _LIT8( KThumbnailCommitTransaction, "COMMIT;" );

   104 _LIT8( KThumbnailRollbackTransaction, "ROLLBACK;" );

   105 

   106 _LIT8( KThumbnailInsertThumbnailInfoByPathAndId, "INSERT INTO TempThumbnailInfo "

   107     "(Path,TNId,Size,Format,Width,Height,OrigWidth,OrigHeight,Flags,Orientation,ThumbFromPath,Modified) ""VALUES "

   108     "(:Path,:TNId,:Size,:Format,:Width,:Height,:OrigWidth,:OrigHeight,:Flags,:Orient,:ThumbFromPath,:Modified);" );

   109 

   110 _LIT8( KThumbnailInsertTempThumbnailInfoData, "INSERT INTO TempThumbnailInfoData (Data) VALUES (:Data);" );

   111 

   112 _LIT8( KThumbnailSelectSizeByPath, "SELECT Size, TNId FROM ThumbnailInfo WHERE Path = :Path ORDER BY Size DESC;" );

   113 

   114 _LIT8( KThumbnailSelectTempSizeByPath, "SELECT Size, TNId FROM TempThumbnailInfo WHERE Path = :Path ORDER BY Size DESC;" );

   115 

   116 _LIT8( KThumbnailSelectById, "SELECT * "

   117         "FROM ThumbnailInfo "

   118         "JOIN ThumbnailInfoData "

   119         "ON ThumbnailInfo.RowID = ThumbnailInfoData.RowID "

   120         "WHERE TNId = :TNId" );

   121 

   122 _LIT8( KThumbnailSelectTempById, "SELECT * "

   123         "FROM TempThumbnailInfo "

   124         "JOIN TempThumbnailInfoData "

   125         "ON TempThumbnailInfo.RowID = TempThumbnailInfoData.RowID "

   126         "WHERE TNId = :TNId" );

   127 

   128 //query by Path

   129 _LIT8( KThumbnailSelectInfoByPath, "SELECT ThumbnailInfo.Format, ThumbnailInfoData.Data, ThumbnailInfo.Width, ThumbnailInfo.Height, ThumbnailInfo.Flags "

   130         "FROM ThumbnailInfo "

   131         "JOIN ThumbnailInfoData "

   132         "ON ThumbnailInfo.RowID = ThumbnailInfoData.RowID "

   133         "WHERE ThumbnailInfo.Path = :Path AND ThumbnailInfo.Size = :Size;");

   134 

   135 _LIT8( KThumbnailSelectTempInfoByPath, "SELECT TempThumbnailInfo.Format, TempThumbnailInfoData.Data, TempThumbnailInfo.Width, TempThumbnailInfo.Height, TempThumbnailInfo.Flags "

   136         "FROM TempThumbnailInfo "

   137         "JOIN TempThumbnailInfoData "

   138         "ON TempThumbnailInfo.RowID = TempThumbnailInfoData.RowID "

   139         "WHERE TempThumbnailInfo.Path = :Path AND TempThumbnailInfo.Size = :Size;");

   140 

   141 //query by Id

   142 _LIT8( KThumbnailSelectInfoById, "SELECT ThumbnailInfo.Format, ThumbnailInfoData.Data, ThumbnailInfo.Width, ThumbnailInfo.Height, ThumbnailInfo.Flags "

   143         "FROM ThumbnailInfo "

   144         "JOIN ThumbnailInfoData "

   145         "ON ThumbnailInfo.RowID = ThumbnailInfoData.RowID "

   146         "WHERE TNId = :TNId AND Size = :Size;" );

   147 

   148 _LIT8( KThumbnailSelectTempInfoById, "SELECT TempThumbnailInfo.Format, TempThumbnailInfoData.Data, TempThumbnailInfo.Width, TempThumbnailInfo.Height, TempThumbnailInfo.Flags "

   149         "FROM TempThumbnailInfo "

   150         "JOIN TempThumbnailInfoData "

   151         "ON TempThumbnailInfo.RowID = TempThumbnailInfoData.RowID "

   152         "WHERE TNId = :TNId AND Size = :Size;" );

   153 		

   154 //query by Idv2

   155 _LIT8( KThumbnailSelectInfoByIdv2, "SELECT ThumbnailInfo.Format, ThumbnailInfoData.Data, ThumbnailInfo.Width, ThumbnailInfo.Height, ThumbnailInfo.Flags "

   156         "FROM ThumbnailInfo "

   157         "JOIN ThumbnailInfoData "

   158         "ON ThumbnailInfo.RowID = ThumbnailInfoData.RowID "

   159         "WHERE TNId = :TNId AND (Size = :SizeImage OR Size = :SizeVideo OR Size = :SizeAudio);" );

   160 

   161 _LIT8( KThumbnailSelectTempInfoByIdv2, "SELECT TempThumbnailInfo.Format, TempThumbnailInfoData.Data, TempThumbnailInfo.Width, TempThumbnailInfo.Height, TempThumbnailInfo.Flags "

   162         "FROM TempThumbnailInfo "

   163         "JOIN TempThumbnailInfoData "

   164         "ON TempThumbnailInfo.RowID = TempThumbnailInfoData.RowID "

   165         "WHERE TNId = :TNId AND (Size = :SizeImage OR Size = :SizeVideo OR Size = :SizeAudio);" );		

   166 

   167 _LIT8( KThumbnailSelectSettings, "SELECT Version FROM ThumbnailSettings;" );

   168 

   169 //qyery Path by ID

   170 _LIT8( KThumbnailSelectPathByID, "SELECT Path FROM ThumbnailInfo WHERE TNId = :TNId;"  );

   171 _LIT8( KThumbnailSelectTempPathByID, "SELECT Path FROM TempThumbnailInfo WHERE TNId = :TNId;");

   172 

   173 //query Path and Modified by ID

   174 _LIT8( KThumbnailSelectPathModifiedByID, "SELECT Path, Modified FROM ThumbnailInfo WHERE TNId = :TNId;"  );

   175 _LIT8( KThumbnailSelectTempPathModifiedByID, "SELECT Path, Modified FROM TempThumbnailInfo WHERE TNId = :TNId;");

   176 

   177 _LIT( KThumbnailSqlParamData, ":Data" );

   178 _LIT( KThumbnailSqlParamFlags, ":Flags" );

   179 _LIT( KThumbnailSqlParamPath, ":Path" );

   180 _LIT( KThumbnailSqlParamWidth, ":Width" );

   181 _LIT( KThumbnailSqlParamHeight, ":Height" );

   182 _LIT( KThumbnailSqlParamOriginalWidth, ":OrigWidth" );

   183 _LIT( KThumbnailSqlParamOriginalHeight, ":OrigHeight" );

   184 _LIT( KThumbnailSqlParamFormat, ":Format" );

   185 _LIT( KThumbnailSqlParamId, ":TNId" );

   186 _LIT( KThumbnailSqlParamSize, ":Size" );

   187 _LIT( KThumbnailSqlParamTNPath, ":TNPath" );

   188 _LIT( KThumbnailSqlParamMajor, ":Major" );

   189 _LIT( KThumbnailSqlParamMinor, ":Minor" );

   190 _LIT( KThumbnailSqlParamImei, ":IMEI" );

   191 _LIT( KThumbnailSqlParamSizeImage, ":SizeImage" );

   192 _LIT( KThumbnailSqlParamSizeVideo, ":SizeVideo" );

   193 _LIT( KThumbnailSqlParamSizeAudio, ":SizeAudio" );

   194 _LIT( KThumbnailSqlParamRowID, ":RowID" );

   195 _LIT( KThumbnailSqlParamOrientation, ":Orient" );

   196 _LIT( KThumbnailSqlParamThumbFromPath, ":ThumbFromPath" );

   197 _LIT( KThumbnailSqlParamModified, ":Modified" );

   198 _LIT( KThumbnailSqlParamFlag, ":Flag" );

   199 

   200 //Delete by path

   201 _LIT8( KThumbnailSqlSelectRowIDInfoByPath, "SELECT ThumbnailInfo.RowID FROM ThumbnailInfo WHERE Path = :Path;" );

   202 _LIT8( KThumbnailSqlDeleteInfoByPath, "DELETE FROM ThumbnailInfo WHERE ThumbnailInfo.RowID = :RowID;" );

   203 _LIT8( KThumbnailSqlDeleteInfoDataByPath, "DELETE FROM ThumbnailInfoData WHERE ThumbnailInfoData.RowID = :RowID;" );

   204 _LIT8( KTempThumbnailSqlSelectRowIDInfoByPath, "SELECT TempThumbnailInfo.RowID FROM TempThumbnailInfo WHERE Path = :Path LIMIT 1;" );

   205 _LIT8( KTempThumbnailSqlDeleteInfoByPath, "DELETE FROM TempThumbnailInfo WHERE TempThumbnailInfo.RowID = :RowID;" );

   206 _LIT8( KTempThumbnailSqlDeleteInfoDataByPath, "DELETE FROM TempThumbnailInfoData WHERE TempThumbnailInfoData.RowID = :RowID;" );

   207 

   208 

   209 //Delete by ID

   210 _LIT8( KThumbnailSqlSelectRowIDInfoByID, "SELECT ThumbnailInfo.RowID FROM ThumbnailInfo WHERE TNId = :TNId;" );

   211 _LIT8( KThumbnailSqlDeleteInfoByID, "DELETE FROM ThumbnailInfo WHERE ThumbnailInfo.RowID = :RowID;" );

   212 _LIT8( KThumbnailSqlDeleteInfoDataByID, "DELETE FROM ThumbnailInfoData WHERE ThumbnailInfoData.RowID = :RowID;" );

   213 _LIT8( KTempThumbnailSqlSelectRowIDInfoByID, "SELECT TempThumbnailInfo.RowID FROM TempThumbnailInfo WHERE TNId = :TNId LIMIT 1;" );

   214 _LIT8( KTempThumbnailSqlDeleteInfoByID, "DELETE FROM TempThumbnailInfo WHERE TempThumbnailInfo.RowID = :RowID;" );

   215 _LIT8( KTempThumbnailSqlDeleteInfoDataByID, "DELETE FROM TempThumbnailInfoData WHERE TempThumbnailInfoData.RowID = :RowID;" );

   216 

   217 

   218 //Update path by Id

   219 _LIT8( KTempThumbnailSqlUpdateById, "UPDATE TempThumbnailInfo SET Path = :Path WHERE TNId = :TNId" );

   220 _LIT8( KThumbnailSqlUpdateById, "UPDATE ThumbnailInfo SET Path = :Path WHERE TNId = :TNId" );

   221 

   222 //version commands

   223 _LIT8( KThumbnailInsertToVersion, "INSERT INTO ThumbnailVersion (IMEI, Minor, Major) VALUES (:IMEI, :Minor,:Major);" );

   224 _LIT8( KThumbnailSelectFromVersion, "SELECT * FROM ThumbnailVersion LIMIT 1" );

   225 

   226 //reset IDs

   227 _LIT8( KTempThumbnailResetIDs, "UPDATE TempThumbnailInfo SET TNId = NULL WHERE TNId NOT NULL" );

   228 _LIT8( KThumbnailResetIDs, "UPDATE ThumbnailInfo SET TNId = NULL WHERE TNId NOT NULL" );

   229 

   230 //update IMEI

   231 _LIT8( KThumbnailUpdateIMEI, "UPDATE ThumbnailVersion SET IMEI = :IMEI" );

   232 

   233 //update ID by Path

   234 _LIT8( KTempThumbnailUpdateIdByPath, "UPDATE TempThumbnailInfo SET TNId = :TNId WHERE Path = :Path" );

   235 _LIT8( KThumbnailUpdateIdByPath, "UPDATE ThumbnailInfo SET TNId = :TNId WHERE Path = :Path" );

   236 

   237 //qyery Modification timestamp by ID

   238 _LIT8( KThumbnailSelectModifiedByID, "SELECT Modified FROM ThumbnailInfo WHERE TNId = :TNId AND ThumbFromPath = 1 LIMIT 1"  );

   239 _LIT8( KThumbnailSelectTempModifiedByID, "SELECT Modified FROM TempThumbnailInfo WHERE TNId = :TNId AND ThumbFromPath = 1 LIMIT 1");

   240 

   241 //query Modification timestamp by path

   242 _LIT8( KThumbnailSelectModifiedByPath, "SELECT Modified FROM ThumbnailInfo WHERE Path = :Path"  );

   243 _LIT8( KThumbnailSelectTempModifiedByPath, "SELECT Modified FROM TempThumbnailInfo WHERE Path = :Path");

   244 

   245 // query possible duplicates

   246 _LIT8 ( KTempFindDuplicate, "SELECT Path FROM TempThumbnailInfo WHERE Size = :Size AND (TNId = :TNId OR Path = :Path);" );

   247 _LIT8 ( KFindDuplicate, "SELECT Path FROM ThumbnailInfo WHERE Size = :Size AND (TNId = :TNId OR Path = :Path);" );

   248 

   249 // check rowIDs

   250 _LIT8 ( KGetInfoRowID, "SELECT MAX (ThumbnailInfo.rowID) FROM ThumbnailInfo" );

   251 _LIT8 ( KGetDataRowID, "SELECT MAX (ThumbnailInfoData.rowID) FROM ThumbnailInfoData" );

   252 

   253 //remove KThumbnailDbFlagBlacklisted flag

   254 _LIT8( KThumbnailRemoveBlacklistedFlag, "UPDATE ThumbnailInfo SET Flags = Flags & ~:Flag WHERE Flags & :Flag" );

   255 

   256 #endif // THUMBNAILSQL_H