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 __CMSQLCLAUSEDEF_H__

    24 #define __CMSQLCLAUSEDEF_H__

    25 

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

    27 // Misc definitions

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

    29 //

    30 

    31 _LIT8( KCmSqlEmpty, "" );

    32 _LIT8( KCmSqlSpace, " " );

    33 _LIT8( KCmSqlLeftBracket, " ( " );

    34 _LIT8( KCmSqlRightBracket, " ) " );

    35 _LIT8( KCmSqlComma, ", " );

    36 _LIT8( KCmSqlDot, "." );

    37 _LIT8( KCmSqlEqual, " = " );

    38 _LIT8( KCmSqlGreater, " >= " );

    39 _LIT8( KCmSqlSmaller, " <= " );

    40 _LIT8( KCmSqlLike, " LIKE " );

    41 _LIT8( KCmSqlSemicolon, ";" );

    42 _LIT8( KCmSqlVariable, "? " );

    43 _LIT8( KCmSqlAppendString, "%S " );

    44 _LIT8( KCmSqlConsistOf, " %%S% " );

    45 _LIT8( KCmSqlBeginsWith, " %S% " );

    46 _LIT8( KCmSqlEndsWith, " %%S " );

    47 _LIT8( KCmSqlAppendInteger, "%d " );

    48 _LIT8( KCmSqlAppendLong, "%ld " );

    49 

    50 _LIT8( KCmSqlDummyTrueCriteria, "1 " );

    51 _LIT8( KCmSqlDummyFalseCriteria, "0 " );

    52 

    53 _LIT8( KCmSqlSelect, "SELECT " );

    54 _LIT8( KCmSqlFrom, "FROM " );

    55 _LIT8( KCmSqlAs, "AS " );

    56 _LIT8( KCmSqlIn, "IN " );

    57 _LIT8( KCmSqlOr, "OR" );

    58 _LIT8( KCmSqlAnd, "AND " );

    59 _LIT8( KCmSqlAll, "* " );

    60 _LIT8( KCmSqlOrderByDate, "ORDER BY Date " );

    61 _LIT8( KCmSqlOrderByRandom, "ORDER BY Random() " );

    62 _LIT8( KCmSqlLimit, "LIMIT " );

    63 _LIT8( KCmSqlOffset, "OFFSET " );

    64 _LIT8( KCmSqlAsc, "ASC ");

    65 _LIT8( KCmSqlDesc, "DESC " );

    66 _LIT8( KCmSqlSortValue, "sortvalue" ); // Used when sorting object

    67                                    // query by property value

    68 

    69 _LIT8( KCmSqlCollateNoCase, " COLLATE NOCASE " ); // case insensitive sort

    70 

    71 _LIT8( KCmSqlBaseSelect, 

    72        "SELECT Title, Date, Size, Uri, DeviceId, ItemId, Id "

    73        "FROM Items WHERE (" 

    74      );

    75                                             

    76 _LIT8( KCmSqlMusicSelect, "SELECT Title, a.Value, Size, Uri, DeviceId, "

    77                           "ItemId, Id FROM Items LEFT JOIN Artists a "

    78                           "ON Items.ArtistId = a.ArtistId LEFT JOIN Albums b "

    79                           "ON Items.AlbumId = b.AlbumId "

    80                           "WHERE ("

    81                           );

    82 

    83 _LIT8( KCmSqlSelectArtistById, "Items.ArtistId = " );

    84 _LIT8( KCmSqlSelectAlbumById, "Items.AlbumId = " );

    85 _LIT8( KCmSqlSelectGenreById, "Items.GenreId = " );

    86 _LIT8( KCmSqlSelectResolutionById, "Items.ResolutionId = " );

    87 _LIT8( KCmSqlSelectUpnpclassById, "Items.UpnpclassId = " );

    88 _LIT8( KCmSqlFilterUpnpProfileId, "Items.ProfileId != " );

    89 

    90 _LIT8( KCmSqlSelectTitle, "Items.Id = " );

    91 _LIT8( KCmSqlSelectDuration, "Items.Duration " );

    92 _LIT8( KCmSqlSelectBitrate, "Items.Bitrate " );

    93 _LIT8( KCmSqlSelectSize, "Items.Size " );

    94 _LIT8( KCmSqlSelectDeviceId, "Items.DeviceId = ");

    95 _LIT8( KCmSqlSelectDate, "Items.Date ");

    96 _LIT8( KCmSqlArtist, "Artists " );

    97 _LIT8( KCmSqlAlbum, "Albums " );

    98 _LIT8( KCmSqlGenre, "Genres " );

    99 _LIT8( KCmSqlUpnpclass, "Upnpclasses " );

   100 _LIT8( KCmSqlResolutions, "Resolutions " );

   101 _LIT8( KCmSqlUpnpProfiles, "UpnpProfiles " );

   102 

   103 _LIT8( KCmSqlArtistId, "ArtistId" );

   104 _LIT8( KCmSqlAlbumId, "AlbumId" );

   105 _LIT8( KCmSqlGenreId, "GenreId" );

   106 _LIT8( KCmSqlProfileId, "ProfileId" );

   107 _LIT8( KCmSqlUpnpclassId, "UpnpclassId" );

   108 _LIT8( KCmSqlResolutionId, "ResolutionId" );

   109 

   110 _LIT8( KCmSqlSelectMediatype, " ( Mediatype = %d ) " );

   111 _LIT8( KCmSqlFreeTextSelectAudio, 

   112     "( Items.Title LIKE \'%%%S%%\' ) OR ( a.Value LIKE \'%%%S%%\' ) OR "

   113     "( b.Value LIKE \'%%%S%%\' ) " );

   114     

   115 _LIT8( KCmSqlFreeTextSelectImageOrVideo, "( Items.Title LIKE \'%%%S%%\' ) " );

   116 

   117 _LIT8(KCmSqlBeginTransaction, "BEGIN;");

   118 _LIT8(KCmSqlCommit, "COMMIT;");

   119 

   120 _LIT8( KCmSqlSelectMaxIndex, "SELECT count(*) FROM %S" );

   121 

   122 _LIT8( KCmSqlSelectMaxPropertyIndex, "SELECT MAX( %S ) FROM %S" );

   123 

   124 _LIT8( KCmSqlSelectMaxItemIndex, "SELECT MAX( Id ) FROM Items;" );

   125 

   126 _LIT8( KCmSqlSelectItemId, "SELECT Id FROM Items WHERE "

   127     "( LOWER(Title) = ? AND Size = ? AND DeviceId = ? )" 

   128     );  

   129 

   130 _LIT8( KCmSqlFilteredPropertySelect, "SELECT Items.Id, Items.Title, "

   131     "Items.ArtistId, b.Value, Items.AlbumId, a.Value, Items.GenreId, "

   132     "c.Value FROM Items LEFT JOIN Artists b "

   133     "ON Items.ArtistId = b.ArtistId LEFT JOIN Albums a "

   134     "ON Items.AlbumId = a.AlbumId LEFT JOIN Genres c "

   135     "ON Items.GenreId = c.GenreId "

   136     "WHERE Items.Mediatype = 2 "

   137     );

   138 

   139 _LIT8( KCmSqlFilteredPropertySelectVideo, "SELECT Id, Title "

   140     "FROM Items WHERE Mediatype = 0 "

   141     );

   142 

   143 _LIT8( KCmSqlWhere,

   144     "WHERE "

   145     );

   146         

   147 // ---------------------------------------------------------------------------

   148 // INSERT STATEMENTS

   149 // ---------------------------------------------------------------------------

   150 //

   151 // Inserting item

   152 _LIT8( KCmSqlInsertItem,

   153     "INSERT INTO Items( " 

   154     "ItemId, Hash, Uri, Title, AlbumArtUri, DeviceId, Duration, Bitrate, "

   155     "Date, HarvestDate, Size, Mediatype, UpnpclassId, ArtistId, AlbumId, "

   156     "GenreId, ResolutionId, ProfileId, SearchId )"

   157     "VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? );"

   158     );

   159 

   160 // Inserting resource

   161 _LIT8( KCmSqlInsertResource,

   162     "INSERT INTO Resources( " 

   163     "ResItemId, ResResolution, ResUri, Size, ResDuration, ResBitrate )"

   164     "VALUES( ?, ?, ?, ?, ?, ? );"

   165     );

   166    

   167 // Inserting artist into artist table

   168 _LIT8( KCmSqlInsertArtist,

   169     "INSERT INTO Artists( " 

   170     "Value ) "

   171     "VALUES( ? );"

   172     );

   173 

   174 // Inserting album into album table

   175 _LIT8( KCmSqlInsertAlbum,

   176     "INSERT INTO Albums( " 

   177     "Value ) "

   178     "VALUES( ? );"

   179     );

   180 

   181 // Inserting genre into genre table    

   182 _LIT8( KCmSqlInsertGenre,

   183     "INSERT INTO Genres( " 

   184     "Value ) "

   185     "VALUES( ? );"

   186     );

   187 

   188 // Inserting upnp class into upnp class table

   189 _LIT8( KCmSqlInsertUpnpclass,

   190     "INSERT INTO Upnpclasses( " 

   191     "Value ) "

   192     "VALUES( ? );"

   193     );

   194     

   195 // Inserting upnp class into upnp class table

   196 _LIT8( KCmSqlInsertUpnpProfile,

   197     "INSERT INTO UpnpProfiles( " 

   198     "Value ) "

   199     "VALUES( ? );"

   200     );

   201         

   202 // Inserting resolution into resolution table

   203 _LIT8( KCmSqlInsertResolution,

   204     "INSERT INTO Resolutions( " 

   205     "Value, Width, Height, PixelCount ) "

   206     "VALUES( ?, ?, ?, ? );"

   207     );        

   208             

   209 // ---------------------------------------------------------------------------

   210 // SELECT STATEMENTS

   211 // ---------------------------------------------------------------------------

   212 //

   213 // Selecting items harvested from the defined server

   214 _LIT8( KCmSqlSelectGenericItem, 

   215     "SELECT Id, ItemId, Hash, SearchId "

   216     "FROM Items "

   217     "WHERE DeviceId = ? "

   218     "ORDER BY Hash;"

   219     );

   220 

   221 _LIT8( KCmSqlSelectPropertyValues,

   222     "SELECT * FROM %S "

   223     "ORDER BY Value;"    

   224     );

   225 

   226 _LIT8( KCmSqlValidateTableExistence,

   227     "SELECT count(*)"

   228         " FROM Items;"

   229         );

   230 

   231 _LIT8( KCmSqlSelectTitleAndId,  

   232     "SELECT Id, Title "

   233     "FROM Items "

   234     "WHERE MediaType = %d "

   235     "ORDER BY Title;"

   236     );

   237 

   238 _LIT8( KCmSqlSelectMediaCount,

   239     "SELECT count(*) FROM Items WHERE Mediatype = ?;"

   240     );            

   241 

   242 _LIT8( KCmSqlSelectLimitHarvestDate,

   243     "SELECT HarvestDate FROM Items WHERE ( Mediatype = ? ) ORDER BY "

   244     "HarvestDate ASC LIMIT ?;"

   245     );

   246     

   247 // ---------------------------------------------------------------------------

   248 // DELETE STATEMENTS

   249 // ---------------------------------------------------------------------------

   250 //

   251 _LIT8( KCmSqlDeleteItem, 

   252     "DELETE FROM Items "

   253     "WHERE Id = ? ;"

   254     );

   255     

   256 _LIT8( KCmSqlDeleteResource, 

   257     "DELETE FROM Resources "

   258     "WHERE ResItemId = ? ;"

   259     );

   260     

   261 _LIT8( KCmSqlDeleteMetadata, 

   262     "DELETE FROM Items WHERE DeviceId = ?;" 

   263     );        

   264 

   265 _LIT8( KCmSqlDeleteOldestMediaItems, 

   266     "DELETE FROM Items WHERE HarvestDate <= ? AND MediaType = ?;"

   267     );

   268     

   269 _LIT8( KCmSqlDeleteUnusedAlbums, 

   270     "DELETE FROM Albums WHERE AlbumId "

   271     "NOT IN ( SELECT AlbumId FROM Items );"

   272     );

   273     

   274 _LIT8( KCmSqlDeleteUnusedArtists, 

   275     "DELETE FROM Artists WHERE ArtistId "

   276     "NOT IN ( SELECT ArtistId FROM Items );"

   277     );

   278     

   279 _LIT8( KCmSqlDeleteUnusedGenres,

   280     "DELETE FROM Genres WHERE GenreId "

   281     "NOT IN ( SELECT GenreId FROM Items );"

   282     );

   283     

   284 _LIT8( KCmSqlDeleteUnusedUpnpclasses,

   285     "DELETE FROM Upnpclasses WHERE UpnpclassId "

   286     "NOT IN ( SELECT UpnpclassId FROM Items );"

   287     );

   288     

   289 _LIT8( KCmSqlDeleteUnusedUpnpProfileIds,

   290     "DELETE FROM UpnpProfiles WHERE ProfileId "

   291     "NOT IN ( SELECT ProfileId FROM Items );"

   292     );

   293                                               

   294 #endif // __CMSQLCLAUSEDEF_H__

   295 

   296 

   297