/*

* 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);");

//Create index for speedup DB searches 

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

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

//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);");

_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,TNId,Size,Format,Width,Height,OrigWidth,OrigHeight,Flags,Orientation,ThumbFromPath,Modified) ""VALUES "

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

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

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

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

_LIT8( KThumbnailSelectById, "SELECT * "

        "FROM ThumbnailInfo "

        "JOIN ThumbnailInfoData "

        "ON ThumbnailInfo.RowID = ThumbnailInfoData.RowID "

        "WHERE TNId = :TNId" );

_LIT8( KThumbnailSelectTempById, "SELECT * "

        "FROM TempThumbnailInfo "

        "JOIN TempThumbnailInfoData "

        "ON TempThumbnailInfo.RowID = TempThumbnailInfoData.RowID "

        "WHERE TNId = :TNId" );

//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 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;");

//query by Id

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

        "FROM ThumbnailInfo "

        "JOIN ThumbnailInfoData "

        "ON ThumbnailInfo.RowID = ThumbnailInfoData.RowID "

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

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

        "FROM TempThumbnailInfo "

        "JOIN TempThumbnailInfoData "

        "ON TempThumbnailInfo.RowID = TempThumbnailInfoData.RowID "

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

//query by Idv2

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

        "FROM ThumbnailInfo "

        "JOIN ThumbnailInfoData "

        "ON ThumbnailInfo.RowID = ThumbnailInfoData.RowID "

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

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

        "FROM TempThumbnailInfo "

        "JOIN TempThumbnailInfoData "

        "ON TempThumbnailInfo.RowID = TempThumbnailInfoData.RowID "

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

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

//qyery Path by ID

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

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

//query Path and Modified by ID

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

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

_LIT( KThumbnailSqlParamData, ":Data" );

_LIT( KThumbnailSqlParamFlags, ":Flags" );

_LIT( KThumbnailSqlParamPath, ":Path" );

_LIT( KThumbnailSqlParamWidth, ":Width" );

_LIT( KThumbnailSqlParamHeight, ":Height" );

_LIT( KThumbnailSqlParamOriginalWidth, ":OrigWidth" );

_LIT( KThumbnailSqlParamOriginalHeight, ":OrigHeight" );

_LIT( KThumbnailSqlParamFormat, ":Format" );

_LIT( KThumbnailSqlParamId, ":TNId" );

_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" );

//Delete by path

_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;" );

//Delete by ID

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

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

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

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

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

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

//Update path by Id

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

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

//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" );

//update ID by Path

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

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

//qyery Modification timestamp by ID

//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 Size = :Size AND (TNId = :TNId OR Path = :Path);" );

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

// check rowIDs

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

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

//remove KThumbnailDbFlagBlacklisted flag

#endif // THUMBNAILSQL_H