/*

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

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

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

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

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

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

//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

// check rowIDs

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

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

//remove KThumbnailDbFlagBlacklisted flag

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

#endif // THUMBNAILSQL_H