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 // temp table is identical to actual main table except it's not persistent

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

    59         "Path TEXT COLLATE NOCASE,"

    60         "TNId INTEGER,"

    61         "Size INTEGER,"

    62         "Format INTEGER,"

    63         "TNPath TEXT COLLATE NOCASE,"

    64         "Width INTEGER,"

    65         "Height INTEGER,"

    66         "OrigWidth INTEGER,"

    67         "OrigHeight INTEGER,"

    68         "Flags INTEGER,"

    69         "VideoPosition INTEGER,"

    70         "Orientation INTEGER,"

    71         "ThumbFromPath INTEGER,"

    72         "Modified LARGEINT);");

    73 

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

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

    76         "Data BLOB);");

    77 

    78 // version table

    79 _LIT8( KThumbnailVersionTable, "CREATE TABLE ThumbnailVersion ("

    80         "Major INTEGER,"

    81         "Minor INTEGER,"

    82         "IMEI TEXT COLLATE NOCASE);");

    83 

    84 // deleted thumbs table

    85 _LIT8( KThumbnailDeletedTable, "CREATE TABLE ThumbnailDeleted ("

    86         // Assosiacted object in file system

    87         "Path TEXT UNIQUE COLLATE NOCASE);");

    88 

    89 // indexes

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

    91 

    92 // parameters

    93 _LIT( KThumbnailSqlParamData, ":Data" );

    94 _LIT( KThumbnailSqlParamFlags, ":Flags" );

    95 _LIT( KThumbnailSqlParamPath, ":Path" );

    96 _LIT( KThumbnailSqlParamNewPath, ":NewPath" );

    97 _LIT( KThumbnailSqlParamWidth, ":Width" );

    98 _LIT( KThumbnailSqlParamHeight, ":Height" );

    99 _LIT( KThumbnailSqlParamOriginalWidth, ":OrigWidth" );

   100 _LIT( KThumbnailSqlParamOriginalHeight, ":OrigHeight" );

   101 _LIT( KThumbnailSqlParamFormat, ":Format" );

   102 _LIT( KThumbnailSqlParamSize, ":Size" );

   103 _LIT( KThumbnailSqlParamTNPath, ":TNPath" );

   104 _LIT( KThumbnailSqlParamMajor, ":Major" );

   105 _LIT( KThumbnailSqlParamMinor, ":Minor" );

   106 _LIT( KThumbnailSqlParamImei, ":IMEI" );

   107 _LIT( KThumbnailSqlParamSizeImage, ":SizeImage" );

   108 _LIT( KThumbnailSqlParamSizeVideo, ":SizeVideo" );

   109 _LIT( KThumbnailSqlParamSizeAudio, ":SizeAudio" );

   110 _LIT( KThumbnailSqlParamRowID, ":RowID" );

   111 _LIT( KThumbnailSqlParamOrientation, ":Orient" );

   112 _LIT( KThumbnailSqlParamThumbFromPath, ":ThumbFromPath" );

   113 _LIT( KThumbnailSqlParamModified, ":Modified" );

   114 _LIT( KThumbnailSqlParamFlag, ":Flag" );

   115 _LIT( KThumbnailSqlParamLimit, ":Limit" );

   116 

   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

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

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

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

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

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

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

   185 

   186 // mark deleted

   187 _LIT8( KThumbnailSqlInsertDeleted, "INSERT INTO ThumbnailDeleted (Path) VALUES (:Path);" );

   188 _LIT8( KThumbnailSqlFindDeleted, "SELECT * FROM ThumbnailDeleted WHERE Path = :Path;" );

   189 

   190 // delete marked

   191 _LIT8( KThumbnailSqlSelectMarked, "SELECT ThumbnailInfo.RowID FROM ThumbnailInfo "

   192         "WHERE EXISTS (SELECT Path FROM ThumbnailDeleted "

   193         "WHERE ThumbnailInfo.Path = ThumbnailDeleted.Path) LIMIT :Limit;" );

   194 

   195 _LIT8( KThumbnailSqlDeleteInfoByRowID, "DELETE FROM ThumbnailInfo WHERE ThumbnailInfo.RowID = :RowID;" );

   196 _LIT8( KThumbnailSqlDeleteInfoDataByRowID, "DELETE FROM ThumbnailInfoData WHERE ThumbnailInfoData.RowID = :RowID;" );

   197 

   198 _LIT8( KThumbnailSqlDeleteFromDeleted, "DELETE FROM ThumbnailDeleted "

   199         "WHERE NOT EXISTS (SELECT Path FROM ThumbnailInfo "

   200         "WHERE ThumbnailDeleted.Path = ThumbnailInfo.Path);" );

   201 

   202 // rename thumb

   203 _LIT8( KThumbnailRename, "UPDATE ThumbnailInfo SET Path = :NewPath WHERE Path = :Path;" );

   204 _LIT8( KThumbnailTempRename, "UPDATE TempThumbnailInfo SET Path = :NewPath WHERE Path = :Path;" );

   205 

   206 // reset blacklisted

   207 _LIT8( KThumbnailTouchBlacklistedRows, "UPDATE ThumbnailInfo SET Flags = 4, Modified = 0 WHERE Flags & :Flag" );

   208 

   209 // existence check

   210 _LIT8( KThumbnailSelectAllPaths, "SELECT ThumbnailInfo.RowID,Path FROM ThumbnailInfo "

   211         "WHERE NOT EXISTS (SELECT Path FROM ThumbnailDeleted "

   212         "WHERE ThumbnailInfo.Path = ThumbnailDeleted.Path) "

   213         "AND ThumbFromPath = 1 AND ThumbnailInfo.RowID > :RowID ORDER BY ThumbnailInfo.RowID ASC LIMIT :Limit;" );

   214 

   215 #endif // THUMBNAILSQL_H