1 /*

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

    15 *

    16 */

    17 

    18 

    19 

    20 

    21 

    22 

    23 #ifndef __CMDMSQLCLAUSEDEF_H__

    24 #define __CMDMSQLCLAUSEDEF_H__

    25 

    26 // ---------------------------------------------------------------------------

    27 // SELECT clauses

    28 // ---------------------------------------------------------------------------

    29 //

    30 _LIT8( KSelectDeviceInfoByUDN, "SELECT " 

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

    32     "FROM MediaServers " 

    33     "WHERE UDN = ?"

    34     );

    35     

    36 _LIT8( KSelectAllMediaServers, "SELECT " 

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

    38     "CopyCapability, FillUsage, StoreUsage " 

    39     "FROM MediaServers"

    40     );

    41         

    42 _LIT8( KSelectMediaServerIDByUDN, 

    43     "SELECT "

    44     "MediaServerID "

    45     "FROM MediaServers "

    46     "WHERE UDN = ?"

    47     );

    48 

    49 _LIT8( KSelectHashValuesByMediaServer, "SELECT " 

    50     "StartIndex, ItemCount, Hash " 

    51     "FROM HashValues "

    52     "WHERE MediaServerID = ?"

    53     );

    54     

    55 _LIT8( KSelectMediaServerUDNByID, 

    56     "SELECT "

    57     "UDN "

    58     "FROM MediaServers "

    59     "WHERE MediaServerID = ?"

    60     );       

    61 

    62 _LIT8( KSelectStoredByUDN, 

    63     "SELECT "

    64     "StoredFile " 

    65     "FROM Stored " 

    66     "WHERE MediaServerID = ( " 

    67     "SELECT MediaServerID "

    68     "FROM MediaServers "

    69     "WHERE UDN = ? )"

    70     );

    71     

    72 _LIT8( KIsFileAlreadyStored,

    73     "SELECT "

    74     "MediaServerID "

    75     "FROM MediaServers "

    76     "WHERE MediaServerID = ( "    

    77     "SELECT "

    78     "MediaServerID "

    79     "FROM Stored "

    80     "WHERE StoredFile = ? ) "

    81     "AND "

    82     "UDN = ?"   

    83     );  

    84 

    85 _LIT8( KSelectID,

    86     "SELECT "

    87     "IDValue "

    88     "FROM IDs "

    89     "WHERE IDType = ?"

    90     );

    91 

    92 _LIT8( KSelectRuleGroupID,

    93     "SELECT "

    94     "RuleGroupID "

    95     "FROM FillRuleGroup "

    96     "WHERE Name = ?"

    97     );

    98     

    99 _LIT8( KSelectStoreRuleID,

   100     "SELECT "

   101     "StoreRuleId "

   102     "FROM StoreRules "

   103     "WHERE Name = ?"

   104     );

   105 

   106 // Loading Fill Rule 1.    

   107 _LIT8( KSelectFillRule,

   108     "SELECT "

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

   110     "TemplateId, MediaType, Status, RealCount, RealSize "

   111     "FROM FillRuleGroup "

   112     "WHERE Name = ? "

   113     "ORDER BY Priority ASC"

   114     );

   115     

   116 // Loading Fill Rule 2.                

   117 _LIT8( KSelectRule,

   118     "SELECT "

   119     "RuleID, MetadataField, Operator "

   120     "FROM Rules "

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

   122     "WHERE frg.Name = ?"

   123     );

   124 

   125 // Loading Fill Rule 3.    

   126 _LIT8( KSelectRuleParams, 

   127     "SELECT "

   128     "ComparisonFieldData "

   129     "FROM RuleParams "

   130     "WHERE RuleID = ( SELECT "

   131     "RuleID "

   132     "FROM Rules "

   133     "WHERE RuleGroupID = ( SELECT "

   134     "RuleGroupID "

   135     "FROM FillRuleGroup "

   136     "WHERE Name = ? ))"

   137     );

   138 

   139 // Loading Fill Rule 3.1    

   140 _LIT8( KSelectRuleParamsByRuleID, 

   141     "SELECT "

   142     "ComparisonFieldData, ComparisonFieldIndex "

   143     "FROM RuleParams "

   144     "WHERE RuleID = ?"

   145     );

   146     

   147 // Loading Fill Rule 4.    

   148 _LIT8( KSelectMediaServerID, 

   149     "SELECT "

   150     "MediaServerID "

   151     "FROM RuleGroupServers "

   152     "WHERE RuleGroupID = ( SELECT "

   153     "RuleGroupID "

   154     "FROM FillRuleGroup "

   155     "WHERE Name = ? )"

   156     );

   157 

   158 // Loading Fill Rule 4.1    

   159 _LIT8( KSelectMediaServerIDByRuleGroupID, 

   160     "SELECT "

   161     "MediaServerID "

   162     "FROM RuleGroupServers "

   163     "WHERE RuleGroupID = ?"

   164     );

   165     

   166 // Loading Fill Rule 5.    

   167 _LIT8( KSelectRuleUDNs, 

   168     "SELECT "

   169     "UDN "

   170     "FROM MediaServers "

   171     "WHERE MediaServerID = ( SELECT "

   172     "MediaServerID "

   173     "FROM RuleGroupServers "

   174     "WHERE RuleGroupID = ( SELECT "

   175     "RuleGroupID "

   176     "FROM FillRuleGroup "

   177     "WHERE Name = ? ))"

   178     );

   179 

   180 // Loading Fill Rule 5.1    

   181 _LIT8( KSelectRuleUDNsByMediaServerID, 

   182     "SELECT "

   183     "UDN "

   184     "FROM MediaServers "

   185     "WHERE MediaServerID = ?"

   186     );

   187     

   188 // Loading selected ( or not selected rules )    

   189 _LIT8( KSelectSelectedFillRuleNames, 

   190     "SELECT "

   191     "Name "

   192     "FROM FillRuleGroup "

   193     "WHERE Selected = ?"

   194     );        

   195 

   196 // Loading all fill rule names

   197 _LIT8( KSelectFillRuleNames, 

   198     "SELECT "

   199     "Name "

   200     "FROM FillRuleGroup"

   201     );

   202 

   203 _LIT8( KSelectFillRuleName, 

   204     "SELECT Name "

   205     "FROM FillRuleGroup "

   206     "WHERE RuleGroupID = ?"

   207     );

   208     

   209 // Loading Store Rule 1.    

   210 _LIT8( KSelectStoreRule,

   211     "SELECT "

   212     "StoreRuleID, Name, Selected, Status "

   213     "FROM StoreRules "

   214     "WHERE Name = ?"

   215     );

   216 

   217 // Loading Store Rule 2.                

   218 _LIT8( KSelectStoreRuleDefs,

   219     "SELECT "

   220     "StoreRuleParamId, MediaType "

   221     "FROM StoreRuleParams "

   222     "WHERE StoreRuleID = ( SELECT "

   223     "StoreRuleID "

   224     "FROM StoreRules "

   225     "WHERE Name = ? )"

   226     );

   227 

   228 // Loading Store rule 3.    

   229 _LIT8( KSelectStoreRuleMediaServerID, 

   230     "SELECT "

   231     "MediaServerID "

   232     "FROM StoreRuleServers "

   233     "WHERE StoreRuleID = ?"

   234     );

   235     

   236 // Loading all store rule names

   237 _LIT8( KSelectStoreRuleNames, 

   238     "SELECT "

   239     "Name "

   240     "FROM StoreRules"

   241     );        

   242 

   243 // Loading selected ( or not selected rules )    

   244 _LIT8( KSelectSelectedStoreRuleNames, 

   245     "SELECT "

   246     "Name "

   247     "FROM StoreRules "

   248     "WHERE Selected = ?"

   249     );    

   250 

   251 _LIT8( KSelectFillFiles, 

   252     "SELECT "

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

   254     "FROM FillFiles "

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

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

   257     "ORDER BY PrimaryText"

   258     );

   259     

   260 _LIT8( KSelectDeletedFillFiles,

   261     "SELECT "

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

   263     "FROM FillFiles "

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

   265     "ORDER BY PrimaryText"

   266     );

   267         

   268 _LIT8( KSelectAllFillFiles, 

   269     "SELECT "

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

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

   272     "RefId, Selected, DriveId, DriveNumber "

   273     "FROM FillFiles "

   274     "ORDER BY Priority ASC"

   275     );    

   276     

   277 _LIT8( KGetAllFillFiles, 

   278     "SELECT "

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

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

   281     "RefId, Selected, DriveId, DriveNumber "

   282     "FROM FillFiles "

   283     "WHERE ( Status & ? ) "

   284     "ORDER BY Priority ASC"

   285     );    

   286         

   287 _LIT8( KSelectStoreFiles, 

   288     "SELECT "

   289     "PrimaryText, SecondaryText, Path, Size, Date "

   290     "FROM StoreFiles "

   291     "WHERE (StoreListId = "

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

   293     "ORDER BY PrimaryText"

   294     );

   295 

   296 _LIT8( KSelectAllStoreFiles, 

   297     "SELECT "

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

   299     "StoreListId, sfd.MediaServerId "

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

   301     );

   302 

   303 _LIT8( KGetAllStoreFiles, 

   304     "SELECT "

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

   306     "StoreListId, sfd.MediaServerId "

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

   308     "WHERE ( sfd.Status & ? )"

   309     );         

   310 

   311 _LIT8( KSelectLowestPriorityFilled,

   312     "SELECT "

   313     "Path, Size, FillListId, FileId "

   314     "FROM FillFiles "

   315     "WHERE ( Status & ? ) "

   316     "AND ( Priority = "

   317     "( SELECT MAX(Priority) FROM FillFiles "

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

   319     "ORDER BY Priority DESC"

   320     );

   321 

   322 _LIT8( KSelectTransferHistory, 

   323     "SELECT "

   324     "DownloadData, UploadData, DownloadTime, UploadTime "

   325     "FROM MediaServers "

   326     "WHERE UDN = ?"

   327     );

   328 

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

   330 _LIT8( KStoreFileCount,

   331     "SELECT count(*) "

   332     "FROM StoreRules "

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

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

   335     );

   336 

   337 _LIT8( KStoreFileCountNoDuplicates,

   338     "SELECT count(*) "

   339     "FROM StoreRules "

   340     "JOIN StoreFiles files ON files.StoreListId = StoreRuleId "

   341     "WHERE selected = '1'" 

   342     );

   343         

   344 _LIT8( KFillFileCount,

   345     "SELECT count(*) "

   346     "FROM FillFiles "

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

   348     );

   349     

   350 _LIT8( KImageShrinkingInfo,

   351     "SELECT Count, Time "

   352     "FROM ImageShrinkingInfo"

   353     );

   354 

   355 _LIT8( KTranferValues,

   356     "SELECT Count "

   357     "FROM TransferInfo "

   358     "WHERE Service = ?"

   359     );

   360 

   361 _LIT8( KStoreListFileCount,

   362     "SELECT count(*) "

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

   364     "WHERE sr.Name = ?"

   365     ); 

   366 

   367 _LIT8( KFillListFileCount,

   368     "SELECT count(*) "

   369     "FROM FillFiles "

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

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

   372     "( Selected = '1') )"

   373     );

   374     

   375 _LIT8( KGetSyncTime, 

   376     "SELECT * "

   377     "FROM SyncTime" 

   378     );               

   379 

   380 _LIT8( KGetProgressInfo,

   381     "SELECT Trasferred, TotalCount "

   382     "FROM ProgressInfo" 

   383     ); 

   384 

   385 _LIT8( KGetFillListStatus,

   386     "SELECT Status "

   387     "FROM FillRuleGroup "

   388     "WHERE Name = ?"

   389     );

   390     

   391 _LIT8( KGetDrives, 

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

   393     "UniqueId, Active "

   394     "FROM DrivesInfo "

   395     "ORDER BY DriveType ASC"

   396     );

   397                                   

   398 // ---------------------------------------------------------------------------

   399 // INSERT clauses

   400 // ---------------------------------------------------------------------------

   401 //    

   402 // FillRuleGroup table

   403 _LIT8( KAddFillGroup,

   404     "INSERT INTO FillRuleGroup("

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

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

   407         "RealSize )"

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

   409         );

   410         

   411 // Rules table

   412 _LIT8( KAddRule,

   413     "INSERT INTO Rules("

   414         "RuleID, RuleGroupID, MetadataField, Operator )"

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

   416         );

   417         

   418 _LIT8( KAddRuleParam,

   419     "INSERT INTO RuleParams("

   420     "RuleParamID, RuleID, ComparisonFieldData, ComparisonFieldIndex )"

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

   422     );

   423     

   424 _LIT8( KAddRuleGroupServer,

   425     "INSERT INTO RuleGroupServers("

   426     "RuleGroupID, MediaServerID )"

   427     "VALUES(?, ?)"

   428     );            

   429 

   430 _LIT8( KAddMediaServer,

   431     "INSERT INTO MediaServers("

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

   433     "DownloadData, UploadData, DownloadTime, UploadTime, "

   434     "IsActive, CopyCapability, FillUsage, StoreUsage )"

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

   436     );

   437 

   438 _LIT8( KAddHashValue,

   439     "INSERT INTO HashValues("

   440     "MediaServerID, StartIndex, ItemCount, Hash )"

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

   442     );

   443     

   444     

   445 _LIT8( KAddStored,

   446     "INSERT INTO Stored("

   447     "StoredID, StoredFile, MediaServerID, Date )"

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

   449     );

   450             

   451 _LIT8( KAddIDs,

   452     "INSERT INTO IDs("

   453     "IDValue, IDType )"

   454     "VALUES(?, ?)"

   455     );

   456     

   457 _LIT8( KAddStoreRule,

   458     "INSERT INTO StoreRules("

   459     "StoreRuleId, Name, Selected, Status )"

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

   461     );

   462     

   463 _LIT8( KAddStoreRuleParams,

   464     "INSERT INTO StoreRuleParams("

   465     "StoreRuleId, StoreRuleParamId, MediaType )"

   466     "VALUES(?, ?, ?)"

   467     );    

   468 

   469 _LIT8( KAddStoreRuleServers,

   470     "INSERT INTO StoreRuleServers("

   471     "StoreRuleId, MediaServerID )"

   472     "VALUES(?, ?)"

   473     );

   474 

   475 _LIT8( KAddFillFile, 

   476     "INSERT INTO FillFiles("

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

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

   479     "RefId, Selected, DriveId, DriveNumber ) "

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

   481     );

   482 

   483 _LIT8( KAddStoreFile, 

   484     "INSERT INTO StoreFiles("

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

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

   487     );    

   488            

   489 _LIT8( KAddStoreFileServer, 

   490     "INSERT INTO StoreFileServers("

   491     "Fid, MediaServerId, Status ) "

   492     "VALUES(?, ?, ?)"  

   493     );

   494         

   495 _LIT8( KInitShrinkingTime,

   496     "INSERT INTO ImageShrinkingInfo("

   497     "Count, Time ) "

   498     "VALUES(0, 0)"      

   499     );    

   500         

   501 _LIT8( KInitTransferInfo,

   502      "INSERT INTO TransferInfo("

   503     "Service, Count, Time ) "

   504     "VALUES(?,0,0)"    

   505     );        

   506 

   507 _LIT8( KSetSyncTime,

   508     "INSERT INTO SyncTime("

   509     "Time ) "

   510     "VALUES( ? )"

   511     );

   512     

   513 _LIT8( KSetProgressInfo,

   514     "INSERT INTO ProgressInfo( "

   515     "Trasferred, TotalCount ) "

   516     "VALUES( ?, ? )"

   517     ); 

   518 

   519 _LIT8( KSetDrivesInfo,

   520     "INSERT INTO DrivesInfo("

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

   522     "Active ) "

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

   524     ); 

   525                             

   526 // count queries

   527    

   528 _LIT8( KValidateTableExistence,

   529     "SELECT count(*)"

   530         " FROM IDs"

   531         );

   532 

   533 // Sum queries

   534 _LIT8( KFillListSize,

   535     "SELECT SUM(Size) "

   536     "FROM FillFiles "

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

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

   539     );

   540     

   541 _LIT8( KStoreListSize,

   542     "SELECT SUM(Size) "

   543     "FROM StoreFiles "

   544     "WHERE ( StoreListId = "

   545     "( SELECT StoreRuleId FROM StoreRules WHERE Name = ? ))"

   546     );    

   547 

   548 _LIT8( KDataAmountFilled,

   549     "SELECT SUM(Size) "

   550     "FROM FillFiles "

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

   552     );

   553 _LIT8( KDataAmountToBeFilled,

   554     "SELECT SUM(Size) "

   555     "FROM FillFiles "

   556     "WHERE (( Status & ?) AND (MediaServerId = ?) AND "

   557     "( Selected = '1'))" 

   558     );

   559 

   560 _LIT8( KDataAmountToBeStored,

   561     "SELECT SUM(Size) "

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

   563     "WHERE ((sfd.MediaServerId = ?) AND (sfd.Status & ?))" 

   564     );

   565 

   566 _LIT8( KTranferTime,

   567     "SELECT Time "

   568     "FROM TransferInfo "

   569     "WHERE Service = ?"

   570     );

   571 

   572 _LIT8( KGetStoreFileCountNoDuplicates, 

   573     "SELECT COUNT( DISTINCT Fid ) "

   574     "FROM StoreFileServers servers "

   575     "JOIN StoreFiles files, StoreRules rules "

   576     "ON (files.FileId = servers.Fid AND rules.StoreRuleId = files.StoreListId) "

   577     "WHERE (servers.Status & ? AND rules.Selected = 1)" 

   578     );    

   579                              

   580 // ---------------------------------------------------------------------------

   581 // UPDATE / REPLACE clauses

   582 // ---------------------------------------------------------------------------

   583 //

   584 _LIT8( KUpdateMediaServerInfo,

   585     "UPDATE MediaServers"

   586         " SET Name = ?, "

   587         "SystemUpdateID = ?, "

   588         "VisibleDate = ?"

   589         " WHERE UDN = ?"

   590         );

   591         

   592 _LIT8( KUpdateFillRule,

   593     "UPDATE FillRuleGroup"

   594     " SET Quantity = ?"

   595     " WHERE Name = ?"

   596     );

   597     

   598 _LIT8( KUpdateID,

   599     "UPDATE IDs"

   600     " SET IDValue = ?"

   601     " WHERE IDType = ?"

   602     );

   603     

   604 // Updating Fill rules selected state

   605 _LIT8( KUpdateFillRuleSelected, 

   606     "UPDATE FillRuleGroup"

   607     " SET Selected = ?"

   608     " WHERE Name = ?"

   609     );

   610 

   611 // Updating Fill rules selected state

   612 _LIT8( KUpdateFillRuleRealInformation, 

   613     "UPDATE FillRuleGroup"

   614     " SET RealCount = ?, RealSize = ?"

   615     " WHERE RuleGroupID = ?"

   616     );      

   617 

   618 // Updating download history

   619 _LIT8( KUpdateDownloadHistory,

   620     "UPDATE MediaServers "

   621     "SET DownloadData = DownloadData + ?, DownloadTime = DownloadTime + ? "

   622     "WHERE MediaServerId = ?"

   623     );

   624         

   625 // Updating upload history        

   626 _LIT8( KUpdateUploadHistory,

   627     "UPDATE MediaServers "

   628     "SET UploadData = UploadData + ?, UploadTime = UploadTime + ? "

   629     "WHERE MediaServerId = ?"

   630     );

   631     

   632 _LIT8( KIncrementImageShrinkingInfo,

   633     "UPDATE ImageShrinkingInfo "

   634     "SET Count = Count + ?, Time = Time + ?"

   635     );    

   636 

   637 _LIT8( KUpdateTransferInfo,

   638     "UPDATE TransferInfo "

   639     "SET Count = ?, Time = ? "

   640     "WHERE Service = ?"

   641     );

   642 

   643 //updates active status, name, fillusage and storeusage

   644 _LIT8( KUpdateMsIsActive,

   645     "UPDATE MediaServers "

   646     "SET IsActive = ?, SystemUpdateID = ?, Name = ?, FillUsage = ?, StoreUsage = ? "

   647     "WHERE UDN = ?"

   648     );

   649     

   650 _LIT8( KInitIsActiveFlags,

   651     "UPDATE MediaServers "

   652     "SET IsActive = '0'"

   653     );    

   654 

   655 _LIT8( KUpdateFileStatus,

   656     "UPDATE FillFiles "

   657     "SET Status = ? "

   658     "WHERE ( ( FileId = ? ) OR ( RefId = ? ) )"

   659     );

   660 

   661 _LIT8( KUpdateFillItemPriorities, 

   662     "UPDATE FillFiles "

   663     "SET Priority = ?, Selected = ? "

   664     "WHERE FillListId = "

   665     "( SELECT RuleGroupID FROM FillRuleGroup "

   666     "WHERE Name = ? )"

   667     );

   668 

   669 //Updates Fill item's status if the status isn't equal to

   670 //ECmFilled    

   671 _LIT8( KUpdateFillItemStatusByRuleName, 

   672     "UPDATE FillFiles "

   673      "SET Status = ? "

   674     "WHERE  FillListId = "

   675     "( SELECT RuleGroupID FROM FillRuleGroup "

   676     "WHERE Name = ? )"

   677     "AND ( Status > '2' OR Status < '2' )"

   678     "AND ( Status != '64' )"

   679     );

   680            

   681 // Updates highest priority item as a target for

   682 // the reference

   683 _LIT8( KUpdateFillItemRef_Zero,

   684     "UPDATE FillFiles "

   685     "SET RefId = '0' WHERE ( Priority = "

   686     "( SELECT MIN( Priority ) FROM FillFiles "

   687     "WHERE Selected = '1' ) )"

   688     );

   689 

   690 _LIT8( KRestoreListItemStatuses, 

   691     "UPDATE FillFiles "

   692     "SET Status = ? "

   693     "WHERE (( FillListId = ? ) AND ( RefId = '0' ) )"

   694     );

   695 

   696 _LIT8( KRestoreFileStatus, 

   697     "UPDATE FillFiles "

   698     "SET Status = ? "

   699     "WHERE ( FileId = ? OR RefId = ? )"

   700     );

   701 

   702 _LIT8( KUpdateSelectionStatus,

   703     "UPDATE FillFiles "

   704     "SET Selected = ?, "

   705     "FillListId = 255, "

   706     "Priority = 255 "

   707     "WHERE FillListId = ?"

   708     );

   709 

   710 _LIT8( KUpdateStoreTransferStatus, 

   711     "UPDATE StoreFileServers SET Status = ? WHERE "

   712     "Fid = ? AND MediaServerId = ?;"

   713     );

   714             

   715 _LIT8( KUpdateFillTransferStatus, 

   716     "UPDATE FillFiles SET Status = ?, Path = ?, "

   717     "DriveId = ?, DriveNumber = ? WHERE "

   718     "MediaServerId =? AND ((FileId =? AND FillListId =?) OR (RefId =?));"

   719     );

   720 

   721 _LIT8( KUpdateFillDriveInfo,

   722     "UPDATE FillFiles "

   723         "SET DriveNumber = ?, "

   724         "DriveId = ? "

   725         "WHERE DriveId = ?"

   726         );

   727 

   728 _LIT8( KUpdateFillDriveAndStatusInfo,

   729     "UPDATE FillFiles "

   730         "SET Path = ?, "

   731         "DriveNumber = ?, "

   732         "DriveId = ?, "

   733         "Status = ? " // Set all to EToBeFilled

   734         "WHERE DriveId = ?"

   735         );

   736                                             

   737 // ---------------------------------------------------------------------------

   738 // REMOVE clauses

   739 // ---------------------------------------------------------------------------

   740 //

   741 _LIT8( KRemoveMediaServersByUDN,

   742     "DELETE FROM MediaServers"

   743     " WHERE UDN = ?"

   744     );

   745     

   746 _LIT8( KRemoveFillRuleByName,

   747     "DELETE FROM FillRuleGroup"

   748     " WHERE Name = ?"

   749     );

   750         

   751 _LIT8( KRemoveRuleByRuleGroupID,

   752     "DELETE FROM Rules"

   753     " WHERE RuleGroupID = (SELECT "

   754     "RuleGroupID "

   755     "FROM FillRuleGroup "

   756     "WHERE Name = ? )"

   757     );

   758 

   759 _LIT8( KRemoveRuleParamsByRuleID,

   760     "DELETE FROM RuleParams"

   761     " WHERE RuleID = (SELECT "

   762     "RuleID "

   763     "FROM Rules "

   764     "WHERE RuleGroupID = (SELECT "

   765     "RuleGroupID "

   766     "FROM FillRuleGroup "

   767     "WHERE Name = ? ) )"

   768     );

   769 

   770 _LIT8( KRemoveRuleGroupServers,

   771     "DELETE FROM RuleGroupServers "

   772     "WHERE RuleGroupID = (SELECT "

   773     "RuleGroupID "

   774     "FROM FillRuleGroup "

   775     "WHERE Name = ? )"

   776     );

   777     

   778 /** Delete all fill rules */ 

   779    

   780 _LIT8( KRemoveFillRuleAll,

   781     "DELETE FROM FillRuleGroup"

   782     );

   783         

   784 _LIT8( KRemoveRuleAll,

   785     "DELETE FROM Rules"

   786     );

   787 

   788 _LIT8( KRemoveRuleParamAll,

   789     "DELETE FROM RuleParams"

   790     );

   791 

   792 _LIT8( KRemoveRuleGroupServerAll,

   793     "DELETE FROM RuleGroupServers"

   794     );

   795     

   796 /** Delete all store rules */

   797 

   798 _LIT8( KRemoveStoreRuleServers,

   799     "DELETE FROM StoreRuleServers"

   800     );

   801         

   802 _LIT8( KRemoveStoreRuleParams,

   803     "DELETE FROM StoreRuleParams"

   804     );

   805 

   806 _LIT8( KRemoveStoreRules,

   807     "DELETE FROM StoreRules"

   808     );

   809 

   810 _LIT8( KRemoveRuleGroupServer,

   811     "DELETE FROM RuleGroupServers "

   812     "WHERE RuleGroupID = ? AND MediaServerID = ?"

   813     );

   814     

   815 _LIT8( KRemoveStoreRuleServer,

   816     "DELETE FROM StoreRuleServers "

   817     "WHERE StoreRuleID = ? AND MediaServerID = ?"

   818     );

   819 

   820 /** Delete All Filled files */

   821 _LIT8( KRemoveFillFiles, 

   822     "DELETE FROM FillFiles"

   823     );

   824     

   825 /** Delete all fill rule's files */

   826 _LIT8( KRemoveFillList, 

   827     "DELETE FROM FillFiles "

   828     "WHERE FillListId = ( SELECT RuleGroupID FROM FillRuleGroup "

   829     "WHERE Name = ?)"

   830     );    

   831     

   832 /** Delete all fillFiles by defined FillListId */

   833 _LIT8( KRemoveFillListItems, 

   834     "DELETE FROM FillFiles "

   835     "WHERE FillListId = ?"

   836     );

   837     

   838 /** Delete Stored file */

   839 _LIT8( KRemoveStoreFile, 

   840     "DELETE FROM StoreFiles "

   841     "WHERE PrimaryText = ?"

   842     );    

   843         

   844 /** Delete All Stored files */

   845 _LIT8( KRemoveStoreFiles, 

   846     "DELETE FROM StoreFiles"

   847     );

   848     

   849 _LIT8( KRemoveStoreFileServers,

   850     "DELETE FROM StoreFileServers"

   851     );    

   852  

   853 /** Delete in active fill rule servers */

   854 _LIT8( KRemoveInActiveFillRuleServers,

   855     "DELETE FROM RuleGroupServers "

   856     "WHERE MediaServerID IN ( SELECT MediaServerID "

   857     "FROM MediaServers WHERE MediaServers.FillUsage = '0' )"

   858     ); 

   859 

   860 /** Delete in active store rule servers */    

   861 _LIT8( KRemoveInActiveStoreRuleServers,

   862     "DELETE FROM StoreRuleServers "

   863     "WHERE MediaServerID IN ( SELECT MediaServerID "

   864     "FROM MediaServers WHERE StoreUsage = '0')"    

   865     );

   866     

   867 /** Delete in active fill files */    

   868 _LIT8( KRemoveInActiveFillFiles,

   869     "DELETE FROM FillFiles "

   870     "WHERE MediaServerId IN ( SELECT MediaServerID "

   871     "FROM MediaServers WHERE FillUsage = '0' )" 

   872     );    

   873 

   874 /** Delete in active store file servers */

   875 _LIT8( KRemoveInActiveStoreFilesServers,

   876     "DELETE FROM StoreFileServers "

   877     "WHERE MediaServerId IN ( SELECT MediaServerID "

   878     "FROM MediaServers WHERE StoreUsage = '0')" 

   879     ); 

   880 

   881 /** Delete in active store files */

   882 _LIT8( KRemoveInActiveStoreFiles,

   883     "DELETE FROM StoreFiles "

   884     "WHERE FileId NOT IN ( SELECT Fid "

   885     "FROM StoreFileServers )"    

   886     );

   887 

   888 _LIT8( KRemoveProgressInfo,

   889     "DELETE FROM ProgressInfo"

   890     );

   891     

   892 _LIT8( KRemoveDrivesInfo,

   893     "DELETE FROM DrivesInfo"

   894     );    

   895 

   896 _LIT8( KRemoveSyncTime,

   897     "DELETE FROM SyncTime"

   898     );

   899 

   900 _LIT8( KRemoveDeletedFillFiles, 

   901     "DELETE FROM FillFiles "

   902     "WHERE Selected = '2'"

   903     );

   904 

   905 _LIT8( KRemoveDeletedMediaServers, 

   906     "DELETE FROM MediaServers "

   907     "WHERE FillUsage = '0' AND StoreUsage = '0'"

   908     );

   909 

   910 _LIT8( KRemoveHash,

   911     "DELETE FROM HashValues "

   912     "WHERE ( ( MediaServerID = ? ) AND ( StartIndex >= ? ) )"

   913     );

   914 

   915 _LIT8( KSetNullRealSizes,

   916     "UPDATE FillRuleGroup SET RealCount = '0', RealSize = '0' "

   917     "WHERE RuleGroupID NOT IN "

   918     "( SELECT FillListId FROM FillFiles ) "

   919     );

   920     

   921 // ---------------------------------------------------------------------------

   922 // TRANSACTION HANDLING

   923 // ---------------------------------------------------------------------------

   924 //

   925 _LIT8(KCmDbBeginTransaction, "BEGIN");

   926 _LIT8(KCmDbSqlCommit, "COMMIT");

   927                                     

   928 #endif // __CMDMSQLCLAUSEDEF_H__

   929 

   930 

   931