/*

* Copyright (c) 2008 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 clause definitions

*

*/

#ifndef __CMDMSQLCLAUSEDEF_H__

#define __CMDMSQLCLAUSEDEF_H__

// ---------------------------------------------------------------------------

// SELECT clauses

// ---------------------------------------------------------------------------

//

_LIT8( KSelectDeviceInfoByUDN, "SELECT " 

    "MediaServerID, UDN, Name, SystemUpdateID, VisibleDate, IsActive, FillUsage " 

    "FROM MediaServers " 

    "WHERE UDN = ?"

    );

_LIT8( KSelectAllMediaServers, "SELECT " 

    "UDN, Name, SystemUpdateID, VisibleDate, IsActive, MediaServerID, "

    "CopyCapability, FillUsage, StoreUsage " 

    "FROM MediaServers"

    );

_LIT8( KSelectMediaServerIDByUDN, 

    "SELECT "

    "MediaServerID "

    "FROM MediaServers "

    "WHERE UDN = ?"

    );

_LIT8( KSelectHashValuesByMediaServer, "SELECT " 

    "StartIndex, ItemCount, Hash " 

    "FROM HashValues "

    "WHERE MediaServerID = ?"

    );

_LIT8( KSelectMediaServerUDNByID, 

    "SELECT "

    "UDN "

    "FROM MediaServers "

    "WHERE MediaServerID = ?"

    );       

_LIT8( KSelectStoredByUDN, 

    "SELECT "

    "StoredFile " 

    "FROM Stored " 

    "WHERE MediaServerID = ( " 

    "SELECT MediaServerID "

    "FROM MediaServers "

    "WHERE UDN = ? )"

    );

_LIT8( KIsFileAlreadyStored,

    "SELECT "

    "MediaServerID "

    "FROM MediaServers "

    "WHERE MediaServerID = ( "    

    "SELECT "

    "MediaServerID "

    "FROM Stored "

    "WHERE StoredFile = ? ) "

    "AND "

    "UDN = ?"   

    );  

_LIT8( KSelectID,

    "SELECT "

    "IDValue "

    "FROM IDs "

    "WHERE IDType = ?"

    );

_LIT8( KSelectRuleGroupID,

    "SELECT "

    "RuleGroupID "

    "FROM FillRuleGroup "

    "WHERE Name = ?"

    );

_LIT8( KSelectStoreRuleID,

    "SELECT "

    "StoreRuleId "

    "FROM StoreRules "

    "WHERE Name = ?"

    );

// Loading Fill Rule 1.    

_LIT8( KSelectFillRule,

    "SELECT "

    "RuleGroupID, Name, Method, LimitType, Quantity, Selected, Priority, "

    "TemplateId, MediaType, Status, RealCount, RealSize "

    "FROM FillRuleGroup "

    "WHERE Name = ? "

    "ORDER BY Priority ASC"

    );

// Loading Fill Rule 2.                

_LIT8( KSelectRule,

    "SELECT "

    "RuleID, MetadataField, Operator "

    "FROM Rules "

    "JOIN FillRuleGroup frg ON Rules.RuleGroupID = frg.RuleGroupID "

    "WHERE frg.Name = ?"

    );

// Loading Fill Rule 3.    

_LIT8( KSelectRuleParams, 

    "SELECT "

    "ComparisonFieldData "

    "FROM RuleParams "

    "WHERE RuleID = ( SELECT "

    "RuleID "

    "FROM Rules "

    "WHERE RuleGroupID = ( SELECT "

    "RuleGroupID "

    "FROM FillRuleGroup "

    "WHERE Name = ? ))"

    );

// Loading Fill Rule 3.1    

_LIT8( KSelectRuleParamsByRuleID, 

    "SELECT "

    "ComparisonFieldData, ComparisonFieldIndex "

    "FROM RuleParams "

    "WHERE RuleID = ?"

    );

// Loading Fill Rule 4.    

_LIT8( KSelectMediaServerID, 

    "SELECT "

    "MediaServerID "

    "FROM RuleGroupServers "

    "WHERE RuleGroupID = ( SELECT "

    "RuleGroupID "

    "FROM FillRuleGroup "

    "WHERE Name = ? )"

    );

// Loading Fill Rule 4.1    

_LIT8( KSelectMediaServerIDByRuleGroupID, 

    "SELECT "

    "MediaServerID "

    "FROM RuleGroupServers "

    "WHERE RuleGroupID = ?"

    );

// Loading Fill Rule 5.    

_LIT8( KSelectRuleUDNs, 

    "SELECT "

    "UDN "

    "FROM MediaServers "

    "WHERE MediaServerID = ( SELECT "

    "MediaServerID "

    "FROM RuleGroupServers "

    "WHERE RuleGroupID = ( SELECT "

    "RuleGroupID "

    "FROM FillRuleGroup "

    "WHERE Name = ? ))"

    );

// Loading Fill Rule 5.1    

_LIT8( KSelectRuleUDNsByMediaServerID, 

    "SELECT "

    "UDN "

    "FROM MediaServers "

    "WHERE MediaServerID = ?"

    );

// Loading selected ( or not selected rules )    

_LIT8( KSelectSelectedFillRuleNames, 

    "SELECT "

    "Name "

    "FROM FillRuleGroup "

    "WHERE Selected = ?"

    );        

// Loading all fill rule names

_LIT8( KSelectFillRuleNames, 

    "SELECT "

    "Name "

    "FROM FillRuleGroup"

    );

_LIT8( KSelectFillRuleName, 

    "SELECT Name "

    "FROM FillRuleGroup "

    "WHERE RuleGroupID = ?"

    );

// Loading Store Rule 1.    

_LIT8( KSelectStoreRule,

    "SELECT "

    "StoreRuleID, Name, Selected, Status "

    "FROM StoreRules "

    "WHERE Name = ?"

    );

// Loading Store Rule 2.                

_LIT8( KSelectStoreRuleDefs,

    "SELECT "

    "StoreRuleParamId, MediaType "

    "FROM StoreRuleParams "

    "WHERE StoreRuleID = ( SELECT "

    "StoreRuleID "

    "FROM StoreRules "

    "WHERE Name = ? )"

    );

// Loading Store rule 3.    

_LIT8( KSelectStoreRuleMediaServerID, 

    "SELECT "

    "MediaServerID "

    "FROM StoreRuleServers "

    "WHERE StoreRuleID = ?"

    );

// Loading all store rule names

_LIT8( KSelectStoreRuleNames, 

    "SELECT "

    "Name "

    "FROM StoreRules"

    );        

// Loading selected ( or not selected rules )    

_LIT8( KSelectSelectedStoreRuleNames, 

    "SELECT "

    "Name "

    "FROM StoreRules "

    "WHERE Selected = ?"

    );    

_LIT8( KSelectFillFiles, 

    "SELECT "

    "PrimaryText, SecondaryText, Path, Size, Status, Date, FileId, RefId "

    "FROM FillFiles "

    "WHERE ((Status & ? ) AND ( FillListId = "

    "( SELECT RuleGroupId FROM FillRuleGroup WHERE Name = ? ) ) ) "

    "ORDER BY PrimaryText"

    );

_LIT8( KSelectDeletedFillFiles,

    "SELECT "

    "PrimaryText, SecondaryText, Path, Size, Status, Date, FileId "

    "FROM FillFiles "

    "WHERE ((Status & ? ) AND ( Selected = '2' ) )"

    "ORDER BY PrimaryText"

    );

_LIT8( KSelectAllFillFiles, 

    "SELECT "

    "FileId, PrimaryText, SecondaryText, Path, Uri, ItemId, UpnpClass, "

    "MediaType, Priority, Size, Status, Date, FillListId, MediaServerId, "

    "RefId, Selected, DriveId, DriveNumber "

    "FROM FillFiles "

    "ORDER BY Priority ASC"

    );    

_LIT8( KGetAllFillFiles, 

    "SELECT "

    "FileId, PrimaryText, SecondaryText, Path, Uri, ItemId, UpnpClass, "

    "MediaType, Priority, Size, Status, Date, FillListId, MediaServerId, "

    "RefId, Selected, DriveId, DriveNumber "

    "FROM FillFiles "

    "WHERE ( Status & ? ) "

    "ORDER BY Priority ASC"

    );    

_LIT8( KSelectStoreFiles, 

    "SELECT "

    "PrimaryText, SecondaryText, Path, Size, Date "

    "FROM StoreFiles "

    "WHERE (StoreListId = "

    "( SELECT StoreRuleID FROM StoreRules WHERE Name = ? )) "

    "ORDER BY PrimaryText"

    );

_LIT8( KSelectAllStoreFiles, 

    "SELECT "

    "FileId, PrimaryText, SecondaryText, Path, Size, sfd.Status, "

    "StoreListId, sfd.MediaServerId "

    "FROM StoreFiles JOIN StoreFileServers sfd ON FileId = sfd.Fid"    

    );

_LIT8( KGetAllStoreFiles, 

    "SELECT "

    "PrimaryText, SecondaryText, Path, Size, sfd.Status, "

    "StoreListId, sfd.MediaServerId "

    "FROM StoreFiles JOIN StoreFileServers sfd ON FileId = sfd.Fid "

    "WHERE ( sfd.Status & ? )"

    );         

_LIT8( KSelectLowestPriorityFilled,

    "SELECT "

    "Path, Size, FillListId, FileId "

    "FROM FillFiles "

    "WHERE ( Status & ? ) "

    "AND ( Priority = "

    "( SELECT MAX(Priority) FROM FillFiles "

    "WHERE ( Status & ? AND RefId = '0' AND DriveNumber = ? ) ) )"

    "ORDER BY Priority DESC"

    );

_LIT8( KSelectTransferHistory, 

    "SELECT "

    "DownloadData, UploadData, DownloadTime, UploadTime "

    "FROM MediaServers "

    "WHERE UDN = ?"

    );

// Count all files from selected store lists ( multiple servers included )

_LIT8( KStoreFileCount,

    "SELECT count(*) "

    "FROM StoreRules "

    "JOIN StoreFiles files,  StoreFileServers servers ON files.StoreListId = StoreRuleId "

    "WHERE selected = '1' AND servers.FId = files.FileId  AND servers.status & ?" 

    );

_LIT8( KStoreFileCountNoDuplicates,

    "SELECT count(*) "

    "FROM StoreRules "

    "JOIN StoreFiles files ON files.StoreListId = StoreRuleId "

    "WHERE selected = '1'" 

    );

_LIT8( KFillFileCount,

    "SELECT count(*) "

    "FROM FillFiles "

    "WHERE status & ? AND RefId = '0' AND Selected = '1'"

    );

_LIT8( KImageShrinkingInfo,

    "SELECT Count, Time "

    "FROM ImageShrinkingInfo"

    );

_LIT8( KTranferValues,

    "SELECT Count "

    "FROM TransferInfo "

    "WHERE Service = ?"

    );

_LIT8( KStoreListFileCount,

    "SELECT count(*) "

    "FROM StoreFiles JOIN StoreRules sr ON StoreListId = sr.StoreRuleId "

    "WHERE sr.Name = ?"

    ); 

_LIT8( KFillListFileCount,

    "SELECT count(*) "

    "FROM FillFiles "

    "WHERE (( Status & ?) AND (FillListId = "

    "(SELECT RuleGroupID FROM FillRuleGroup WHERE Name = ?)) AND "

    "( Selected = '1') )"

    );

_LIT8( KGetSyncTime, 

    "SELECT * "

    "FROM SyncTime" 

    );               

_LIT8( KGetProgressInfo,

    "SELECT Trasferred, TotalCount "

    "FROM ProgressInfo" 

    ); 

_LIT8( KGetFillListStatus,

    "SELECT Status "

    "FROM FillRuleGroup "

    "WHERE Name = ?"

    );

_LIT8( KGetDrives, 

    "SELECT DriveNumber, DriveType, DriveName, Size, Quota, UsedQuota, "

    "UniqueId, Active "

    "FROM DrivesInfo "

    "ORDER BY DriveType ASC"

    );

// ---------------------------------------------------------------------------

// INSERT clauses

// ---------------------------------------------------------------------------

//    

// FillRuleGroup table

_LIT8( KAddFillGroup,

    "INSERT INTO FillRuleGroup("

        "RuleGroupID, Name, Method, LimitType, Quantity, "

        "Selected, Priority, TemplateId, MediaType, Status, RealCount, "

        "RealSize )"

        "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

        );

// Rules table

_LIT8( KAddRule,

    "INSERT INTO Rules("

        "RuleID, RuleGroupID, MetadataField, Operator )"

        "VALUES(?, ?, ?, ?)"

        );

_LIT8( KAddRuleParam,

    "INSERT INTO RuleParams("

    "RuleParamID, RuleID, ComparisonFieldData, ComparisonFieldIndex )"

    "VALUES(?, ?, ?, ?)"

    );

_LIT8( KAddRuleGroupServer,

    "INSERT INTO RuleGroupServers("

    "RuleGroupID, MediaServerID )"

    "VALUES(?, ?)"

    );            

_LIT8( KAddMediaServer,

    "INSERT INTO MediaServers("

    "MediaServerID, UDN, Name, SystemUpdateID, VisibleDate, "

    "DownloadData, UploadData, DownloadTime, UploadTime, "

    "IsActive, CopyCapability, FillUsage, StoreUsage )"

    "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

    );

_LIT8( KAddHashValue,

    "INSERT INTO HashValues("

    "MediaServerID, StartIndex, ItemCount, Hash )"

    "VALUES(?, ?, ?, ?)"

    );

_LIT8( KAddStored,

    "INSERT INTO Stored("

    "StoredID, StoredFile, MediaServerID, Date )"

    "VALUES(?, ?, ?, ?)"

    );

_LIT8( KAddIDs,

    "INSERT INTO IDs("

    "IDValue, IDType )"

    "VALUES(?, ?)"

    );

_LIT8( KAddStoreRule,

    "INSERT INTO StoreRules("

    "StoreRuleId, Name, Selected, Status )"

    "VALUES(?, ?, ?, ?)"

    );

_LIT8( KAddStoreRuleParams,

    "INSERT INTO StoreRuleParams("

    "StoreRuleId, StoreRuleParamId, MediaType )"

    "VALUES(?, ?, ?)"

    );    

_LIT8( KAddStoreRuleServers,

    "INSERT INTO StoreRuleServers("

    "StoreRuleId, MediaServerID )"

    "VALUES(?, ?)"

    );

_LIT8( KAddFillFile, 

    "INSERT INTO FillFiles("

    "FileId, PrimaryText, SecondaryText, Path, Uri, ItemId, UpnpClass, "

    "MediaType, Priority, Size, Status, Date, FillListId, MediaServerId, "

    "RefId, Selected, DriveId, DriveNumber ) "

    "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"  

    );

_LIT8( KAddStoreFile, 

    "INSERT INTO StoreFiles("

    "FileId, PrimaryText, SecondaryText, Path, Size, Date, StoreListId) "

    "VALUES(?, ?, ?, ?, ?, ?, ?)"  

    );    

_LIT8( KAddStoreFileServer, 

    "INSERT INTO StoreFileServers("

    "Fid, MediaServerId, Status ) "

    "VALUES(?, ?, ?)"  

    );

_LIT8( KInitShrinkingTime,

    "INSERT INTO ImageShrinkingInfo("

    "Count, Time ) "

    "VALUES(0, 0)"      

    );    

_LIT8( KInitTransferInfo,

     "INSERT INTO TransferInfo("

    "Service, Count, Time ) "

    "VALUES(?,0,0)"    

    );        

_LIT8( KSetSyncTime,

    "INSERT INTO SyncTime("

    "Time ) "

    "VALUES( ? )"

    );

_LIT8( KSetProgressInfo,

    "INSERT INTO ProgressInfo( "

    "Trasferred, TotalCount ) "

    "VALUES( ?, ? )"

    ); 

_LIT8( KSetDrivesInfo,

    "INSERT INTO DrivesInfo("

    "DriveNumber, DriveType, DriveName, Size, Quota, UsedQuota, UniqueId, "

    "Active ) "

    "VALUES( ?, ?, ?, ?, ?, ?, ?, ? )"

    ); 

// count queries

_LIT8( KValidateTableExistence,