1 /*

     2 * Copyright (c) 2007 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:  SQLite queries for MPX collection db

    15 *

    16 */

    17 

    18 

    19 #ifndef MPXDBPLUGINQUERIES_H

    20 #define MPXDBPLUGINQUERIES_H

    21 

    22 // SQLite queries

    23 

    24 // == Music table queries =====================================================

    25 //

    26 #ifdef ABSTRACTAUDIOALBUM_INCLUDED

    27 _LIT(KMusicCreateTable, "CREATE TABLE Music("

    28     L"UniqueId INTEGER PRIMARY KEY,"

    29     L"DbFlag INTEGER,"

    30     L"VolumeId INTEGER,"

    31     L"Title TEXT COLLATE NOCASE,"

    32     L"Artist INTEGER,"

    33     L"Art TEXT,"

    34     L"Deleted INTEGER DEFAULT 0,"

    35     L"Location TEXT,"

    36     L"AlbumTrack INTEGER,"

    37     L"PlayCount INTEGER DEFAULT 0,"

    38     L"TimeAdded TEXT,"

    39     L"TimePlayed TEXT DEFAULT '',"

    40     L"Duration INTEGER,"

    41     L"Sync INTEGER DEFAULT 0,"

    42     L"Modified INTEGER DEFAULT 0,"

    43     L"Album INTEGER,"

    44     L"Genre INTEGER,"

    45     L"Composer INTEGER,"

    46     L"ReleaseDate TEXT DEFAULT '',"

    47     L"Rating INTEGER,"

    48     L"Comment TEXT,"

    49     L"Copyright TEXT,"

    50     L"Url TEXT,"

    51     L"DRM INTEGER,"

    52     L"LastPlayPosition INTEGER DEFAULT 0,"

    53     L"SampleRate INTEGER,"

    54     L"BitRate INTEGER,"

    55     L"NumChannels INTEGER,"

    56     L"Codec INTEGER,"

    57     L"MimeType TEXT,"

    58     L"MTPDrmStatus INTEGER," 

    59     L"AlbumArtist TEXT,"

    60     L"ContainEmbeddedArt INTEGER DEFAULT 0,"

    61     L"AbstractAlbum INTEGER)");

    62 

    63 #else

    64 _LIT(KMusicCreateTable, "CREATE TABLE Music("

    65     L"UniqueId INTEGER PRIMARY KEY,"

    66     L"DbFlag INTEGER,"

    67     L"VolumeId INTEGER,"

    68     L"Title TEXT COLLATE NOCASE,"

    69     L"Artist INTEGER,"

    70     L"Art TEXT,"

    71     L"Deleted INTEGER DEFAULT 0,"

    72     L"Location TEXT,"

    73     L"AlbumTrack INTEGER,"

    74     L"PlayCount INTEGER DEFAULT 0,"

    75     L"TimeAdded TEXT,"

    76     L"TimePlayed TEXT DEFAULT '',"

    77     L"Duration INTEGER,"

    78     L"Sync INTEGER DEFAULT 0,"

    79     L"Modified INTEGER DEFAULT 0,"

    80     L"Album INTEGER,"

    81     L"Genre INTEGER,"

    82     L"Composer INTEGER,"

    83     L"ReleaseDate TEXT DEFAULT '',"

    84     L"Rating INTEGER,"

    85     L"Comment TEXT,"

    86     L"Copyright TEXT,"

    87     L"Url TEXT,"

    88     L"DRM INTEGER,"

    89     L"LastPlayPosition INTEGER DEFAULT 0,"

    90     L"SampleRate INTEGER,"

    91     L"BitRate INTEGER,"

    92     L"NumChannels INTEGER,"

    93     L"Codec INTEGER,"

    94     L"MimeType TEXT,"

    95     L"MTPDrmStatus INTEGER)");

    96 #endif // ABSTRACTAUDIOALBUM_INCLUDED

    97 

    98 _LIT(KMusicDropTable,"DROP TABLE Music");

    99 #ifdef ABSTRACTAUDIOALBUM_INCLUDED

   100 _LIT(KMusicCheckTable,"SELECT UniqueId,Title,Location,Artist,Album,AlbumTrack,Genre,Composer,Duration,ReleaseDate,TimeAdded,PlayCount,TimePlayed,Rating,Comment,Copyright,Art,DbFlag,Sync,Modified,Deleted,Url,DRM,LastPlayPosition,SampleRate,BitRate,VolumeId,NumChannels,Codec,MimeType,MTPDrmStatus,AlbumArtist,ContainEmbeddedArt,AbstractAlbum FROM Music");

   101 #else

   102 _LIT(KMusicCheckTable,"SELECT UniqueId,Title,Location,Artist,Album,AlbumTrack,Genre,Composer,Duration,ReleaseDate,TimeAdded,PlayCount,TimePlayed,Rating,Comment,Copyright,Art,DbFlag,Sync,Modified,Deleted,Url,DRM,LastPlayPosition,SampleRate,BitRate,VolumeId,NumChannels,Codec,MimeType,MTPDrmStatus FROM Music");

   103 #endif // ABSTRACTAUDIOALBUM_INCLUDED

   104 // indexes

   105 _LIT(KMusicDeletedIdIndex,"CREATE INDEX IndexMusicDeletedId ON Music(Deleted,UniqueId)");

   106 _LIT(KMusicDeletedTitleIndex,"CREATE INDEX IndexMusicDeletedTitle ON Music(Deleted,Title)");

   107 _LIT(KMusicDeletedArtistTitleIndex,"CREATE INDEX IndexMusicArtistDeletedTitle ON Music(Artist,Deleted,Title)");

   108 _LIT(KMusicDeletedAlbumTitleIndex,"CREATE INDEX IndexMusicAlbumDeletedTitle ON Music(Album,Deleted,Title)");

   109 _LIT(KMusicDeletedAlbumTrackTitleIndex,"CREATE INDEX IndexMusicAlbumDeletedTrackTitle ON Music(Album,Deleted,AlbumTrack,Title)");

   110 _LIT(KMusicDeletedGenreTitleIndex,"CREATE INDEX IndexMusicGenreDeletedTitle ON Music(Genre,Deleted,Title)");

   111 _LIT(KMusicDeletedComposerTitleIndex,"CREATE INDEX IndexMusicComposerDeletedTitle ON Music(Composer,Deleted,Title)");

   112 

   113 _LIT(KQueryMusicInsert, "INSERT INTO :dbname.Music(%S) VALUES(%S)");

   114 _LIT(KQueryMusicUpdate, "UPDATE :dbname.Music SET %S WHERE UniqueId=%u");

   115 _LIT(KQueryMusicDelete, "DELETE FROM :dbname.Music WHERE UniqueId=%u");

   116 _LIT(KQueryMusicDeleteUpdate, "UPDATE :dbname.Music SET Deleted=1 WHERE UniqueId=%u");

   117 _LIT(KQueryMusicDeleteForCategory, "UPDATE :dbname.Music SET Deleted=0 WHERE %S=%u");

   118 _LIT(KQueryMusicCleanup, "DELETE FROM :dbname.Music WHERE Deleted=1");

   119 

   120 // Note: The no categories queries below only retrieve a limited number of fields from the Music table

   121 //       as opposed to *. This is an optimization that matches the DBMS version of the plugin and assumes

   122 //       that the client only requests those fields. From the C++ code perspective there is no difference

   123 //       in processing as the five fields in question reside at the beginning of the table. If the client

   124 //       is modified to request more attributes then the queries below will have to be changed to return *.

   125 //

   126 //       As far as performance goes, the queries that only return six fields are a little faster than

   127 //       the ones that return *, for example, for a * query that takes 2.1 seconds the equivalent

   128 //       optimized query is faster by more than 100ms.

   129 

   130 _LIT(KQueryMusicGetSong, "SELECT Music.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Music,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Music.Deleted=0 AND Music.UniqueId=%u AND Music.Album=Album.UniqueId AND Music.Artist=Artist.UniqueId AND Music.Genre=Genre.UniqueId AND Music.Composer=Composer.UniqueId");

   131 

   132 // LTAN-79N8ND: temporary fix.  Symbian 9.4 SQLite cannot resolve column number correctly,

   133 // Use column number instead of column name for ORDER BY, needed when FROM uses more than 1 table

   134 // calculate column number from SELECT fields, first field is column 1 (not 0)

   135 /*

   136 _LIT(KQueryMusicGetAllSongs, "SELECT Music.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Music,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Music.Deleted=0 AND Music.Album=Album.UniqueId AND Music.Artist=Artist.UniqueId AND Music.Genre=Genre.UniqueId AND Music.Composer=Composer.UniqueId ORDER BY Music.Title");

   137 _LIT(KQueryMusicGetSongsForArtist, "SELECT Music.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Music,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Music.Deleted=0 AND Music.Artist=%u AND Music.Album=Album.UniqueId AND Music.Artist=Artist.UniqueId AND Music.Genre=Genre.UniqueId AND Music.Composer=Composer.UniqueId ORDER BY Music.Title");

   138 _LIT(KQueryMusicGetSongsForAlbum, "SELECT Music.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Music,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Music.Deleted=0 AND Music.Album=%u AND Music.Album=Album.UniqueId AND Music.Artist=Artist.UniqueId AND Music.Genre=Genre.UniqueId AND Music.Composer=Composer.UniqueId ORDER BY Music.AlbumTrack, Music.Title");

   139 _LIT(KQueryMusicGetSongsForUnknownAlbum, "SELECT Music.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Music,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Music.Deleted=0 AND Music.Album=%u AND Music.Album=Album.UniqueId AND Music.Artist=Artist.UniqueId AND Music.Genre=Genre.UniqueId AND Music.Composer=Composer.UniqueId ORDER BY Music.Title");

   140 _LIT(KQueryMusicGetSongsForArtistAlbum, "SELECT Music.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Music,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Music.Deleted=0 AND Music.Artist=%u AND Music.Album=%u AND Music.Album=Album.UniqueId AND Music.Artist=Artist.UniqueId AND Music.Genre=Genre.UniqueId AND Music.Composer=Composer.UniqueId ORDER BY Music.AlbumTrack, Music.Title");

   141 _LIT(KQueryMusicGetSongsForGenre, "SELECT Music.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Music,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Music.Deleted=0 AND Music.Genre=%u AND Music.Album=Album.UniqueId AND Music.Artist=Artist.UniqueId AND Music.Genre=Genre.UniqueId AND Music.Composer=Composer.UniqueId ORDER BY Music.Title");

   142 _LIT(KQueryMusicGetSongsForComposer, "SELECT Music.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Music,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Music.Deleted=0 AND Music.Composer=%u AND Music.Album=Album.UniqueId AND Music.Artist=Artist.UniqueId AND Music.Genre=Genre.UniqueId AND Music.Composer=Composer.UniqueId ORDER BY Music.Title");

   143 _LIT(KQueryMusicGetRecentlyPlayed, "SELECT Music.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Music,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Music.Deleted=0 AND Music.TimePlayed<>'' AND Music.Album=Album.UniqueId AND Music.Artist=Artist.UniqueId AND Music.Genre=Genre.UniqueId AND Music.Composer=Composer.UniqueId ORDER BY TimePlayed DESC LIMIT %u");

   144 _LIT(KQueryMusicGetMostPlayed, "SELECT Music.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Music,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Music.Deleted=0 AND Music.PlayCount<>0 AND Music.Album=Album.UniqueId AND Music.Artist=Artist.UniqueId AND Music.Genre=Genre.UniqueId AND Music.Composer=Composer.UniqueId ORDER BY PlayCount DESC, TimePlayed DESC LIMIT %u");

   145 _LIT(KQueryMusicGetRecentlyAdded, "SELECT Music.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Music,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Music.Deleted=0 AND (julianday(Music.TimeAdded)>julianday('now','-7 days')) AND Music.Album=Album.UniqueId AND Music.Artist=Artist.UniqueId AND Music.Genre=Genre.UniqueId AND Music.Composer=Composer.UniqueId ORDER BY Music.TimeAdded DESC");

   146 */

   147 

   148 

   149 #ifdef ABSTRACTAUDIOALBUM_INCLUDED

   150 _LIT(KQueryMusicGetAllSongsMinimum, "SELECT Music.UniqueId,Music.DbFlag,Music.VolumeId,Music.Title,0,Music.Art,Music.Deleted,Music.Location,0,0,'','',0,0,0,0,0,0,0,0,'','','',0,0,0,0,0,0,'',0,'',0,0,Artist.Name FROM :dbname.Music,:dbname.Artist WHERE Deleted=0 AND Music.Artist=Artist.UniqueId ORDER BY MUSIC.Title");                                  

   151 #else

   152 _LIT(KQueryMusicGetAllSongsMinimum, "SELECT Music.UniqueId,Music.DbFlag,Music.VolumeId,Music.Title,0,Music.Art,Music.Deleted,Music.Location,0,0,'','',0,0,0,0,0,0,0,0,'','','',0,0,0,0,0,0,'',0,Artist.Name FROM :dbname.Music,:dbname.Artist WHERE Deleted=0 AND Music.Artist=Artist.UniqueId ORDER BY MUSIC.Title");                                

   153 #endif  

   154 

   155 

   156 _LIT(KQueryMusicGetAllSongs, "SELECT Music.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Music,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Music.Deleted=0 AND Music.Album=Album.UniqueId AND Music.Artist=Artist.UniqueId AND Music.Genre=Genre.UniqueId AND Music.Composer=Composer.UniqueId ORDER BY 4");

   157 _LIT(KQueryMusicGetSongsForArtist, "SELECT Music.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Music,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Music.Deleted=0 AND Music.Artist=%u AND Music.Album=Album.UniqueId AND Music.Artist=Artist.UniqueId AND Music.Genre=Genre.UniqueId AND Music.Composer=Composer.UniqueId ORDER BY 4");

   158 _LIT(KQueryMusicGetSongsForAlbum, "SELECT Music.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Music,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Music.Deleted=0 AND Music.Album=%u AND Music.Album=Album.UniqueId AND Music.Artist=Artist.UniqueId AND Music.Genre=Genre.UniqueId AND Music.Composer=Composer.UniqueId ORDER BY 9, 4");

   159 _LIT(KQueryMusicGetSongsForUnknownAlbum, "SELECT Music.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Music,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Music.Deleted=0 AND Music.Album=%u AND Music.Album=Album.UniqueId AND Music.Artist=Artist.UniqueId AND Music.Genre=Genre.UniqueId AND Music.Composer=Composer.UniqueId ORDER BY 4");

   160 _LIT(KQueryMusicGetSongsForArtistAlbum, "SELECT Music.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Music,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Music.Deleted=0 AND Music.Artist=%u AND Music.Album=%u AND Music.Album=Album.UniqueId AND Music.Artist=Artist.UniqueId AND Music.Genre=Genre.UniqueId AND Music.Composer=Composer.UniqueId ORDER BY 9, 4");

   161 _LIT(KQueryMusicGetSongsForGenre, "SELECT Music.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Music,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Music.Deleted=0 AND Music.Genre=%u AND Music.Album=Album.UniqueId AND Music.Artist=Artist.UniqueId AND Music.Genre=Genre.UniqueId AND Music.Composer=Composer.UniqueId ORDER BY 4");

   162 _LIT(KQueryMusicGetSongsForComposer, "SELECT Music.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Music,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Music.Deleted=0 AND Music.Composer=%u AND Music.Album=Album.UniqueId AND Music.Artist=Artist.UniqueId AND Music.Genre=Genre.UniqueId AND Music.Composer=Composer.UniqueId ORDER BY 4");

   163 _LIT(KQueryMusicGetRecentlyPlayed, "SELECT Music.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Music,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Music.Deleted=0 AND Music.TimePlayed<>'' AND Music.Album=Album.UniqueId AND Music.Artist=Artist.UniqueId AND Music.Genre=Genre.UniqueId AND Music.Composer=Composer.UniqueId ORDER BY 12 DESC LIMIT %u");

   164 _LIT(KQueryMusicGetMostPlayed, "SELECT Music.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Music,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Music.Deleted=0 AND Music.PlayCount<>0 AND Music.Album=Album.UniqueId AND Music.Artist=Artist.UniqueId AND Music.Genre=Genre.UniqueId AND Music.Composer=Composer.UniqueId ORDER BY PlayCount DESC, TimePlayed DESC LIMIT %u");

   165 _LIT(KQueryMusicGetRecentlyAdded, "SELECT Music.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Music,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Music.Deleted=0 AND (julianday(Music.TimeAdded)>julianday('now','-7 days')) AND Music.Album=Album.UniqueId AND Music.Artist=Artist.UniqueId AND Music.Genre=Genre.UniqueId AND Music.Composer=Composer.UniqueId ORDER BY 11 DESC");

   166 #ifdef ABSTRACTAUDIOALBUM_INCLUDED

   167 _LIT(KQueryMusicGetSongsForAbstractAlbum,"SELECT Music.*,AbstractAlbum.Uri FROM :dbname.Music,:dbname.AbstractAlbum WHERE Music.Deleted=0 AND Music.AbstractAlbum=AbstractAlbum.UniqueId AND AbstractAlbum.UniqueId=%u");

   168 #endif // ABSTRACTAUDIOALBUM_INCLUDED

   169 _LIT(KQueryMusicSong, "SELECT UniqueId,DbFlag,VolumeId,Title,0,'',0,Location FROM :dbname.Music WHERE %S");

   170 _LIT(KQueryMusicFindAll, "SELECT Music.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Music,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE %S AND Music.Album=Album.UniqueId AND Music.Artist=Artist.UniqueId AND Music.Genre=Genre.UniqueId AND Music.Composer=Composer.UniqueId %S");

   171 

   172 // LTAN-79N8ND: temporary fix.  Symbian 9.4 SQLite cannot resolve column number correctly,

   173 // Use column number instead of column name for ORDER BY, needed when FROM uses more than 1 table

   174 // calculate column number from SELECT fields, first field is column 1 (not 0)

   175 /*

   176 _LIT(KQueryMusicFindAllSortOrderTrack, "ORDER BY Music.AlbumTrack,Music.Title");

   177 _LIT(KQueryMusicFindAllSortOrderTitle, "ORDER BY Music.Title");

   178 */

   179 _LIT(KQueryMusicFindAllSortOrderTrack, "ORDER BY 9, 4");

   180 _LIT(KQueryMusicFindAllSortOrderTitle, "ORDER BY 4");

   181 

   182 // no categories queries

   183 _LIT(KQueryMusicGetSongNoCategories, "SELECT UniqueId,DbFlag,VolumeId,Title,0,'',0,Location FROM :dbname.Music WHERE Deleted=0 AND UniqueId=%u");

   184 _LIT(KQueryMusicGetAllSongsNoCategories, "SELECT UniqueId,DbFlag,VolumeId,Title,0,'',0,Location FROM :dbname.Music WHERE Deleted=0 ORDER BY Title");

   185 _LIT(KQueryMusicGetSongsForArtistNoCategories, "SELECT UniqueId,DbFlag,VolumeId,Title,0,'',0,Location FROM :dbname.Music WHERE Deleted=0 AND Artist=%u ORDER BY Title");

   186 _LIT(KQueryMusicGetSongsForAlbumNoCategories, "SELECT UniqueId,DbFlag,VolumeId,Title,0,'',0,Location,AlbumTrack FROM :dbname.Music WHERE Deleted=0 AND Album=%u ORDER BY AlbumTrack,Title");

   187 _LIT(KQueryMusicGetSongsForUnknownAlbumNoCategories, "SELECT UniqueId,DbFlag,VolumeId,Title,0,'',0,Location FROM :dbname.Music WHERE Deleted=0 AND Album=%u ORDER BY Title");

   188 _LIT(KQueryMusicGetSongsForArtistAlbumNoCategories, "SELECT UniqueId,DbFlag,VolumeId,Title,0,'',0,Location,AlbumTrack FROM :dbname.Music WHERE Deleted=0 AND Artist=%u AND Album=%u ORDER BY AlbumTrack,Title");

   189 _LIT(KQueryMusicGetSongsForGenreNoCategories, "SELECT UniqueId,DbFlag,VolumeId,Title,0,'',0,Location FROM :dbname.Music WHERE Deleted=0 AND Genre=%u ORDER BY Title");

   190 _LIT(KQueryMusicGetSongsForComposerNoCategories, "SELECT UniqueId,DbFlag,VolumeId,Title,0,'',0,Location FROM :dbname.Music WHERE Deleted=0 AND Composer=%u ORDER BY Title");

   191 _LIT(KQueryMusicGetRecentlyPlayedNoCategories, "SELECT UniqueId,DbFlag,VolumeId,Title,0,'',0,Location,TimePlayed FROM :dbname.Music WHERE Deleted=0 AND TimePlayed<>'' ORDER BY TimePlayed DESC LIMIT %u");

   192 _LIT(KQueryMusicGetMostPlayedNoCategories, "SELECT UniqueId,DbFlag,VolumeId,Title,0,'',0,Location,PlayCount,TimePlayed FROM :dbname.Music WHERE Deleted=0 AND PlayCount<>0 ORDER BY PlayCount DESC, TimePlayed DESC LIMIT %u");

   193 _LIT(KQueryMusicGetRecentlyAddedNoCategories, "SELECT UniqueId,DbFlag,VolumeId,Title,0,'',0,Location,TimeAdded FROM :dbname.Music WHERE Deleted=0 AND (julianday(TimeAdded)>julianday('now','-7 days')) ORDER BY TimeAdded DESC");

   194 

   195 #ifdef ABSTRACTAUDIOALBUM_INCLUDED

   196 _LIT(KQueryMusicGetSongsInBlockAsc, "SELECT Music.UniqueId,Music.DbFlag,Music.VolumeId,Music.Title,Music.Artist,Music.Art,0,'',0,0,'','',0,0,0,0,0,0,0,0,'','','',0,0,0,0,0,0,'',0,'',0,0,Artist.Name FROM :dbname.Music,:dbname.Artist WHERE Deleted=0 AND Music.Artist=Artist.UniqueId AND Title > :title ORDER BY 4 ASC LIMIT :limit");

   197 _LIT(KQueryMusicGetSongsInBlockDsc, "SELECT Music.UniqueId,Music.DbFlag,Music.VolumeId,Music.Title,Music.Artist,Music.Art,0,'',0,0,'','',0,0,0,0,0,0,0,0,'','','',0,0,0,0,0,0,'',0,'',0,0,Artist.Name FROM :dbname.Music,:dbname.Artist WHERE Deleted=0 AND Music.Artist=Artist.UniqueId AND Title < :title ORDER BY 4 DESC LIMIT :limit");

   198 _LIT(KQueryMusicGetSongsAtOffset, "SELECT Music.UniqueId,Music.DbFlag,Music.VolumeId,Music.Title,Music.Artist,Music.Art,0,'',0,0,'','',0,0,0,0,0,0,0,0,'','','',0,0,0,0,0,0,'',0,'',0,0,Artist.Name FROM :dbname.Music,:dbname.Artist WHERE Deleted=0 AND Music.Artist=Artist.UniqueId ORDER BY 4 LIMIT :limit OFFSET :offset");

   199 _LIT(KQueryMusicGetSongsLimited, "Select Music.UniqueId,Music.DbFlag,Music.VolumeId,Music.Title,Music.Artist,Music.Art,0,'',0,0,'','',0,0,0,0,0,0,0,0,'','','',0,0,0,0,0,0,'',0,'',0,0,Artist.Name FROM :dbname.Music,:dbname.Artist WHERE Deleted=0 AND Music.Artist=Artist.UniqueId ORDER BY 4 LIMIT %u");

   200 #else

   201 _LIT(KQueryMusicGetSongsInBlockAsc, "SELECT Music.UniqueId,Music.DbFlag,Music.VolumeId,Music.Title,Music.Artist,Music.Art,0,'',0,0,'','',0,0,0,0,0,0,0,0,'','','',0,0,0,0,0,0,'',0,Artist.Name FROM :dbname.Music,:dbname.Artist WHERE Deleted=0 AND Music.Artist=Artist.UniqueId AND Title > :title ORDER BY 4 ASC LIMIT :limit");

   202 _LIT(KQueryMusicGetSongsInBlockDsc, "SELECT Music.UniqueId,Music.DbFlag,Music.VolumeId,Music.Title,Music.Artist,Music.Art,0,'',0,0,'','',0,0,0,0,0,0,0,0,'','','',0,0,0,0,0,0,'',0,Artist.Name FROM :dbname.Music,:dbname.Artist WHERE Deleted=0 AND Music.Artist=Artist.UniqueId AND Title < :title ORDER BY 4 DESC LIMIT :limit");

   203 _LIT(KQueryMusicGetSongsAtOffset, "SELECT Music.UniqueId,Music.DbFlag,Music.VolumeId,Music.Title,Music.Artist,Music.Art,0,'',0,0,'','',0,0,0,0,0,0,0,0,'','','',0,0,0,0,0,0,'',0,Artist.Name FROM :dbname.Music,:dbname.Artist WHERE Deleted=0 AND Music.Artist=Artist.UniqueId ORDER BY 4 LIMIT :limit OFFSET :offset");

   204 _LIT(KQueryMusicGetSongsLimited, "Select Music.UniqueId,Music.DbFlag,Music.VolumeId,Music.Title,Music.Artist,Music.Art,0,'',0,0,'','',0,0,0,0,0,0,0,0,'','','',0,0,0,0,0,0,'',0,Artist.Name FROM :dbname.Music,:dbname.Artist WHERE Deleted=0 AND Music.Artist=Artist.UniqueId ORDER BY 4 LIMIT %u");

   205 #endif // ABSTRACTAUDIOALBUM_INCLUDED

   206 _LIT(KQueryMusicGetUri, "SELECT Location,VolumeId FROM :dbname.Music WHERE Deleted=0 AND UniqueId=%u");

   207 _LIT(KQueryMusicGetTitle, "SELECT Title FROM :dbname.Music WHERE Deleted=0 AND UniqueId=%u");

   208 _LIT(KQueryMusicGetMusicUris, "SELECT UniqueId,Location FROM :dbname.Music WHERE Deleted=0 ORDER BY UniqueId ASC LIMIT %u");

   209 _LIT(KQueryMusicGetMusicUrisFrom, "SELECT UniqueId,Location FROM :dbname.Music WHERE Deleted=0 AND UniqueId > %u ORDER BY UniqueId ASC LIMIT %u");

   210 

   211 _LIT(KQueryMusicCount, "SELECT count(*) FROM :dbname.Music WHERE Deleted=0");

   212 _LIT(KQueryMusicAlbum, "SELECT DISTINCT Album FROM :dbname.Music WHERE Deleted=0 AND Artist=%u ORDER BY Album");

   213 _LIT(KQueryMusicVolume, "SELECT VolumeId FROM :dbname.Music WHERE Deleted=0 AND UniqueId=%u");

   214 _LIT(KQueryMusicVolumeAll, "SELECT VolumeId FROM :dbname.Music WHERE UniqueId=%u");

   215 _LIT(KQueryMusicInfo, "SELECT * FROM :dbname.Music WHERE Deleted=0 AND UniqueId=%u");

   216 _LIT(KQueryOtherSongsInArtistAlbum, "SELECT count(*) FROM :dbname.Music WHERE Deleted=0 AND UniqueId<>%u AND Artist=%u AND Album=%u");

   217 

   218 _LIT(KQueryMusicDurationAll, "SELECT sum(Duration) FROM :dbname.Music WHERE Deleted=0");

   219 _LIT(KQueryMusicDurationArtist, "SELECT sum(Duration) FROM :dbname.Music WHERE Deleted=0 AND Artist=%u");

   220 _LIT(KQueryMusicDurationAlbum, "SELECT sum(Duration) FROM :dbname.Music WHERE Deleted=0 AND Album=%u");

   221 _LIT(KQueryMusicDurationArtistAlbum, "SELECT sum(Duration) FROM :dbname.Music WHERE Deleted=0 AND Artist=%u AND Album=%u");

   222 _LIT(KQueryMusicDurationGenre, "SELECT sum(Duration) FROM :dbname.Music WHERE Deleted=0 AND Genre=%u");

   223 _LIT(KQueryMusicDurationComposer, "SELECT sum(Duration) FROM :dbname.Music WHERE Deleted=0 AND Composer=%u");

   224 _LIT(KQueryMusicDurationRecentlyPlayed, "SELECT sum(Duration) FROM (SELECT Duration,TimePlayed FROM :dbname.Music WHERE Deleted=0 AND TimePlayed<>'' ORDER BY TimePlayed DESC LIMIT %u)");

   225 _LIT(KQueryMusicDurationMostPlayed, "SELECT sum(Duration) FROM (SELECT Duration,PlayCount,TimePlayed FROM :dbname.Music WHERE Deleted=0 AND PlayCount<>0 ORDER BY PlayCount DESC,TimePlayed DESC LIMIT %u)");

   226 _LIT(KQueryMusicDurationRecentlyAdded, "SELECT sum(Duration) FROM :dbname.Music WHERE Deleted=0 AND (julianday(TimeAdded) > julianday('now','-7 days'))");

   227 

   228 _LIT(KQueryMusicGetDurationRecentlyPlayed, "SELECT Duration,TimePlayed FROM :dbname.Music WHERE Deleted=0 AND TimePlayed<>'' ORDER BY TimePlayed DESC LIMIT %u");

   229 _LIT(KQueryMusicGetDurationMostPlayed, "SELECT Duration,PlayCount,TimePlayed FROM :dbname.Music WHERE Deleted=0 AND PlayCount<>0 ORDER BY PlayCount DESC,TimePlayed DESC LIMIT %u");

   230 _LIT(KQueryMusicGetDurationRecentlyAdded, "SELECT Duration FROM :dbname.Music WHERE Deleted=0 AND (julianday(TimeAdded) > julianday('now','-7 days'))");

   231 

   232 // Select songs information from music, playlistsongs and playlistsonginfo via playlistid order by playlistsongs's ordinal

   233 _LIT(KQueryMusicGetAllSongsbyPl,"select mu.UniqueId,mu.DbFlag,mu.VolumeId,mu.Title,mu.Artist,mu.Art,0,mu.Location,pl.UniqueId as PlUId, pl.ordinal from :dbname.music as mu,:pldbname.playlistsongs as pl where mu.Deleted=0 AND mu.uniqueid = pl.songid and pl.playlistid =%u ORDER BY 10");

   234 #ifdef ABSTRACTAUDIOALBUM_INCLUDED

   235 _LIT(KQueryMusicGetAllSongsInfobyPl,"SELECT Music.*,Artist.Name,Album.Name,Genre.Name,Composer.Name,pl.UniqueId as PlUId,pl.ordinal FROM :dbname.Music,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer,:pldbname.playlistsongs as pl WHERE Music.Deleted=0 AND Music.Album=Album.UniqueId AND Music.Artist=Artist.UniqueId AND Music.Genre=Genre.UniqueId AND Music.Composer=Composer.UniqueId AND Music.UniqueId=pl .songid and pl .PlaylistId=%u ORDER BY 40");

   236 #else

   237 _LIT(KQueryMusicGetAllSongsInfobyPl,"SELECT Music.*,Artist.Name,Album.Name,Genre.Name,Composer.Name,pl.UniqueId as PlUId,pl.ordinal FROM :dbname.Music,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer,:pldbname.playlistsongs as pl WHERE Music.Deleted=0 AND Music.Album=Album.UniqueId AND Music.Artist=Artist.UniqueId AND Music.Genre=Genre.UniqueId AND Music.Composer=Composer.UniqueId AND Music.UniqueId=pl .songid and pl .PlaylistId=%u ORDER BY 37");

   238 #endif

   239 _LIT(KQuerySongsInArtistAlbum, "SELECT count(*) FROM :dbname.Music WHERE Deleted=0 AND Artist=%u AND Album=%u");

   240 _LIT(KQueryMusicGetArtistForAlbum, "SELECT Artist FROM :dbname.Music,:dbname.Artist WHERE Music.Deleted=0 AND Music.Artist=Artist.UniqueId AND Music.Album=%u");

   241 _LIT(KQueryMusicGetAlbumartForAlbum, "SELECT Art FROM :dbname.Music WHERE Music.Deleted=0 AND Music.Album=%u");

   242 // == Category table queries ==================================================

   243 //

   244 _LIT(KCategoryTablePlaceholder, "##table##");

   245 

   246 _LIT(KCategoryCreateTable,"CREATE TABLE ##table##("

   247     L"UniqueId INTEGER PRIMARY KEY,"

   248     L"Name TEXT COLLATE NOCASE,"

   249     L"SongCount INTEGER)");

   250 

   251 _LIT(KCategoryDropTable,"DROP TABLE ##table##");

   252 _LIT(KCategoryCheckTable,"SELECT UniqueId,Name,SongCount FROM ##table##");

   253 _LIT(KCategoryNameIndex,"CREATE INDEX Index##table##Name ON ##table##(Name)");

   254 

   255 _LIT(KQueryCategoryInsert, "INSERT INTO :dbname.##table##(UniqueId,Name,SongCount) VALUES(%u,'%S',%u)");

   256 _LIT(KQueryCategoryDelete, "DELETE FROM :dbname.##table## WHERE UniqueId=%u");

   257 

   258 _LIT(KQueryCategoryCount, "SELECT count(*) FROM :dbname.##table##");

   259 _LIT(KQueryCategoryItem, "SELECT * FROM :dbname.##table## WHERE UniqueId=%u");

   260 _LIT(KQueryCategoryItemBySongId, "SELECT ##table##.* FROM :dbname.##table##,:dbname.Music WHERE Music.Deleted=0 AND Music.UniqueId=%u AND ##table##.UniqueId=Music.%S");

   261 _LIT(KQueryCategoryAll, "SELECT * FROM :dbname.##table## ORDER BY Name");

   262 _LIT(KQueryCategoryItems, "SELECT * FROM :dbname.##table## WHERE %S ORDER BY Name ASC");

   263 _LIT(KQueryCategorySongCount, "SELECT SongCount FROM :dbname.##table## WHERE UniqueId=%u");

   264 _LIT(KQueryCategorySubcategoryItems, "SELECT Album.* FROM :dbname.Album,:dbname.Music WHERE Music.Deleted=0 AND Music.Artist=%u AND Album.UniqueId=Music.Album ORDER BY Name");

   265 

   266 _LIT(KQueryCategoryIncrementSongCount, "UPDATE :dbname.##table## SET SongCount=SongCount+1 WHERE UniqueId=%u");

   267 _LIT(KQueryCategoryDecrementSongCount, "UPDATE :dbname.##table## SET SongCount=SongCount-1 WHERE UniqueId=%u");

   268 _LIT(KQueryCategoryGetSongCount, "SELECT SongCount FROM :dbname.##table## WHERE UniqueId=%u");

   269 

   270 // == Auxiliary table queries =================================================

   271 //

   272 _LIT(KAuxiliaryCreateTable, "CREATE TABLE Auxiliary("

   273     L"Id INTEGER,"

   274     L"Version TEXT,"

   275     L"TimeRefreshed TEXT,"

   276     L"TimeSynced TEXT,"

   277     L"Corrupt INTEGER DEFAULT 0,"

   278     L"SaveDeletedRecordCount INTEGER DEFAULT 0)");

   279 _LIT(KAuxiliaryDropTable,"DROP TABLE Auxiliary");

   280 _LIT(KAuxiliaryCheckTable, "SELECT Id,Version,TimeRefreshed,TimeSynced,Corrupt,SaveDeletedRecordCount FROM AUXILIARY");

   281 #ifdef ABSTRACTAUDIOALBUM_INCLUDED

   282 _LIT(KQueryAuxiliaryInsert, "INSERT INTO Auxiliary(Id,Version,Corrupt) VALUES(0,'6.7.0',%u)");

   283 #else

   284 _LIT(KQueryAuxiliaryInsert, "INSERT INTO Auxiliary(Id,Version,Corrupt) VALUES(0,'6.7.1',%u)");

   285 #endif // ABSTRACTAUDIOALBUM_INCLUDED

   286 _LIT(KQueryAuxiliarySetTime, "UPDATE :dbname.Auxiliary SET TimeRefreshed='%S', Corrupt=0");

   287 _LIT(KQueryAuxiliaryGetTime, "SELECT TimeRefreshed FROM :dbname.Auxiliary");

   288 _LIT(KQueryAuxiliarySetCorrupt, "UPDATE :dbname.Auxiliary SET Corrupt=%u");

   289 _LIT(KQueryAuxiliaryGetCorrupt, "SELECT Corrupt FROM :dbname.Auxiliary");

   290 _LIT(KQueryAuxiliaryGetCount, "SELECT SaveDeletedRecordCount FROM :dbname.Auxiliary");

   291 _LIT(KQueryAuxiliarySetCount, "UPDATE :dbname.Auxiliary SET SaveDeletedRecordCount=%u");

   292 _LIT(KQueryAuxiliaryGetId, "SELECT Id FROM :dbname.Auxiliary");

   293 _LIT(KQueryAuxiliarySetId, "UPDATE :dbname.Auxiliary SET Id=%u");

   294 

   295 // == Playlist table queries ==================================================

   296 //

   297 _LIT(KPlaylistCreateTable, "CREATE TABLE Playlist("

   298     L"UniqueId INTEGER PRIMARY KEY,"

   299     L"VolumeId INTEGER,"

   300     L"DbFlag INTEGER,"

   301     L"Sync INTEGER,"

   302     L"Name TEXT COLLATE NOCASE,"

   303     L"Uri TEXT,"

   304     L"Time TEXT)");

   305 _LIT(KPlaylistDropTable,"DROP TABLE Playlist");

   306 _LIT(KPlaylistCheckTable, "SELECT UniqueId,Name,Uri,VolumeId,Sync,DbFlag,Time FROM Playlist");

   307 _LIT(KPlaylistNameIndex,"CREATE INDEX IndexPlaylistName ON Playlist(Name)");

   308 

   309 _LIT(KQueryPlaylistInsert, "INSERT INTO :dbname.Playlist(UniqueId,Name,Uri,VolumeId,Sync,DbFlag,Time) VALUES(%u,'%S','%S',%u,0,%u,'%S')");

   310 _LIT(KQueryPlaylistUpdate, "UPDATE :dbname.Playlist SET %S WHERE UniqueId=%u");

   311 _LIT(KQueryPlaylistUpdateTime, "UPDATE :dbname.Playlist SET Time='%S' WHERE UniqueId=%u");

   312 _LIT(KQueryPlaylistUpdateTimeForSong, "UPDATE :dbname.Playlist SET Time='%S' WHERE UniqueId IN (SELECT DISTINCT PlaylistId FROM :dbname.PlaylistSongs WHERE SongId=%u)");

   313 _LIT(KQueryPlaylistDeleteAll, "DELETE FROM :dbname.Playlist");

   314 _LIT(KQueryPlaylistDelete, "DELETE FROM :dbname.Playlist WHERE UniqueId=%u");

   315 

   316 _LIT(KQueryPlaylistCount, "SELECT count(*) FROM :dbname.Playlist");

   317 _LIT(KQueryPlaylistGetAll, "SELECT * FROM :dbname.Playlist ORDER BY Name ASC");

   318 _LIT(KQueryPlaylistGet, "SELECT * FROM :dbname.Playlist WHERE UniqueId=%u");

   319 _LIT(KQueryPlaylistDuration, "SELECT sum(Music.Duration) FROM :dbname.Music,:dbname.Playlist,:dbname.PlaylistSongs WHERE Music.UniqueId=PlaylistSongs.SongId AND PlaylistSongs.PlaylistId=%u");

   320 _LIT(KQueryPlaylistId, "SELECT UniqueId FROM :dbname.Playlist WHERE Uri='%S'");

   321 _LIT(KQueryLikePlaylistId, "SELECT UniqueId FROM :dbname.Playlist WHERE Uri LIKE '%%%%%S%%%%' ESCAPE '\\'");

   322 _LIT(KQueryPlaylistDriveId, "SELECT VolumeId FROM :dbname.Playlist WHERE UniqueId=%u");

   323 _LIT(KQueryPlaylistItems, "SELECT * FROM :dbname.Playlist WHERE %S ORDER BY Name ASC");

   324 _LIT(KQueryPlaylistGetForSong, "SELECT DISTINCT PlaylistId FROM :dbname.PlaylistSongs WHERE SongId=%u");

   325 _LIT(KQueryPlaylistFileCount, "SELECT count(*) FROM :dbname.Playlist WHERE Uri LIKE '%%.m3u'");

   326 //see "Efficient Use of Symbinan SQL How-To Document" for details about following queries:

   327 _LIT(KQueryPlaylistGetFileUris, "SELECT UniqueId,Uri FROM :dbname.Playlist WHERE Uri LIKE '%%%%.m3u' ORDER BY UniqueId ASC LIMIT %u");

   328 _LIT(KQueryPlaylistGetFileUrisFrom, "SELECT UniqueId,Uri FROM :dbname.Playlist WHERE Uri LIKE '%%%%.m3u' AND UniqueId > %u ORDER BY UniqueId ASC LIMIT %u");

   329 

   330 // == PlaylistSongs table queries =============================================

   331 //

   332 _LIT(KPlaylistSongsCreateTable, "CREATE TABLE PlaylistSongs("

   333     L"UniqueId INTEGER PRIMARY KEY AUTOINCREMENT,"

   334     L"SongId INTEGER,"

   335     L"PlaylistId INTEGER,"

   336     L"Ordinal INTEGER)");

   337 _LIT(KPlaylistSongsDropTable,"DROP TABLE PlaylistSongs");

   338 _LIT(KPlaylistSongsCheckTable, "SELECT UniqueId,SongId,PlaylistId,Ordinal FROM PlaylistSongs");

   339 

   340 _LIT(KQueryPlaylistSongsInsert, "INSERT INTO :dbname.PlaylistSongs(PlaylistId,SongId,Ordinal) VALUES(%u,%u,%u)");

   341 _LIT(KQueryPlaylistSongsUpdate, "UPDATE :dbname.PlaylistSongs SET SongId=%u WHERE SongId=%u");

   342 _LIT(KQueryPlaylistSongsUpdatePlaylistId, "UPDATE :dbname.PlaylistSongs SET PlaylistId=%u WHERE PlaylistId=%u");

   343 _LIT(KQueryPlaylistSongsDelete, "DELETE FROM :dbname.PlaylistSongs WHERE SongId=%u");

   344 _LIT(KQueryPlaylistSongsDeleteAll, "DELETE FROM :dbname.PlaylistSongs");

   345 _LIT(KQueryPlaylistSongsDeletePlaylist, "DELETE FROM :dbname.PlaylistSongs WHERE PlaylistId=%u");

   346 _LIT(KQueryPlaylistSongsDeleteSong, "DELETE FROM :dbname.PlaylistSongs WHERE PlaylistId=%u AND SongId=%u");

   347 _LIT(KQueryPlaylistSongsDeleteSongOrdinal, "DELETE FROM :dbname.PlaylistSongs WHERE PlaylistId=%u AND SongId=%u AND Ordinal=%u");

   348 _LIT(KQueryPlaylistSongsDeleteForCategory, "DELETE FROM :dbname.PlaylistSongs WHERE PlaylistSongs.SongId=Music.UniqueId AND Music.%S=%u");

   349 _LIT(KQueryPlaylistSongsDeleteForArtistAlbum, "DELETE FROM :dbname.PlaylistSongs WHERE PlaylistSongs.SongId=Music.UniqueId AND Music.Artist=%u AND Music.Album=%u");

   350 

   351 _LIT(KQueryPlaylistSongsCount, "SELECT count(*) FROM :dbname.music as m,:pldbname.Playlistsongs as pl WHERE pl.PlaylistId=%u AND m.UniqueId=pl.SongId AND m.Deleted=0");

   352 _LIT(KQueryPlaylistSongsPlaylistSongCount, "SELECT count(*) FROM :dbname.PlaylistSongs WHERE PlaylistId=%u AND SongId=%u");

   353 _LIT(KQueryPlaylistSongsSongCount, "SELECT count(*) FROM :dbname.PlaylistSongs WHERE SongId=%u");

   354 _LIT(KQueryPlaylistSongsGetSongs, "SELECT UniqueId,SongId,Ordinal FROM :dbname.PlaylistSongs WHERE PlaylistId=%u ORDER BY Ordinal");

   355 

   356 // LTAN-79N8ND: temporary fix.  Symbian 9.4 SQLite cannot resolve column number correctly,

   357 // Use column number instead of column name for ORDER BY, needed when FROM uses more than 1 table

   358 // calculate column number from SELECT fields, first field is column 1 (not 0)

   359 //_LIT(KQueryPlaylistSongsGetSong, "SELECT PlaylistSongs.UniqueId,PlaylistSongs.SongId,PlaylistSongs.PlaylistId,PlaylistSongs.Ordinal,PlaylistSongInfo.VolumeId,PlaylistSongInfo.DbFlag,PlaylistSongInfo.Uri,PlaylistSongInfo.Title FROM :dbname.PlaylistSongs,:dbname.PlaylistSongInfo WHERE PlaylistSongs.PlaylistId=%u AND PlaylistSongs.SongId=%u AND PlaylistSongs.SongId=PlaylistSongInfo.SongId ORDER BY PlaylistSongs.Ordinal");

   360 _LIT(KQueryPlaylistSongsGetSong, "SELECT PlaylistSongs.UniqueId,PlaylistSongs.SongId,PlaylistSongs.PlaylistId,PlaylistSongs.Ordinal,PlaylistSongInfo.VolumeId,PlaylistSongInfo.DbFlag,PlaylistSongInfo.Uri,PlaylistSongInfo.Title FROM :dbname.PlaylistSongs,:dbname.PlaylistSongInfo WHERE PlaylistSongs.PlaylistId=%u AND PlaylistSongs.SongId=%u AND PlaylistSongs.SongId=PlaylistSongInfo.SongId ORDER BY 4");

   361 _LIT(KQueryPlaylistSongsGetSongsInfo, " SELECT PlaylistSongs.*,PlaylistSongInfo.VolumeId,PlaylistSongInfo.DbFlag,PlaylistSongInfo.Uri,PlaylistSongInfo.Title FROM :dbname.PlaylistSongs,:dbname.PlaylistSongInfo WHERE PlaylistSongs.SongId=PlaylistSongInfo.SongId AND PlaylistSongs.PlaylistId=%u ORDER BY 4");

   362 

   363 _LIT(KQueryPlaylistSongsGetSongInfo, "SELECT PlaylistSongs.UniqueId,PlaylistSongs.SongId,PlaylistSongs.PlaylistId,PlaylistSongs.Ordinal,PlaylistSongInfo.VolumeId,PlaylistSongInfo.DbFlag,PlaylistSongInfo.Uri,PlaylistSongInfo.Title FROM :dbname.PlaylistSongs,:dbname.PlaylistSongInfo WHERE PlaylistSongs.UniqueId=%u AND PlaylistSongs.SongId=%u AND PlaylistSongs.SongId=PlaylistSongInfo.SongId");

   364 

   365 _LIT(KQueryPlaylistSongsUpdateSongOrdinalAfterDelete, "UPDATE :dbname.PlaylistSongs SET Ordinal=Ordinal-1 WHERE PlaylistId=%u AND Ordinal>%u");

   366 _LIT(KQueryPlaylistSongsUpdateSongOrdinal, "UPDATE :dbname.PlaylistSongs SET Ordinal=%u WHERE UniqueId=%u");

   367 _LIT(KQueryPlaylistSongsMoveSongUp, "UPDATE :dbname.PlaylistSongs SET Ordinal=Ordinal+1 WHERE PlaylistId=%u AND Ordinal>=%u AND Ordinal<%u");

   368 _LIT(KQueryPlaylistSongsMoveSongDown, "UPDATE :dbname.PlaylistSongs SET Ordinal=Ordinal-1 WHERE PlaylistId=%u AND Ordinal<=%u AND Ordinal>%u");

   369 

   370 // == PlaylistSongInfo table queries ==========================================

   371 //

   372 _LIT(KPlaylistSongInfoCreateTable, "CREATE TABLE PlaylistSongInfo("

   373     L"SongId INTEGER PRIMARY KEY,"

   374     L"VolumeId INTEGER,"

   375     L"DbFlag INTEGER,"

   376     L"Uri TEXT,"

   377     L"Title TEXT COLLATE NOCASE)");

   378 _LIT(KPlaylistSongInfoDropTable,"DROP TABLE PlaylistSongInfo");

   379 _LIT(KPlaylistSongInfoCheckTable, "SELECT SongId,VolumeId,DbFlag,Uri,Title FROM PlaylistSongInfo");

   380 

   381 _LIT(KQueryPlaylistSongInfoInsert, "INSERT INTO :dbname.PlaylistSongInfo(SongId,Uri,Title,VolumeId,DbFlag) VALUES(%u,'%S','%S',%u,%u)");

   382 _LIT(KQueryPlaylistSongInfoDelete, "DELETE FROM :dbname.PlaylistSongInfo WHERE SongId=%u");

   383 _LIT(KQueryPlaylistSongInfoDeleteAll, "DELETE FROM :dbname.PlaylistSongInfo");

   384 _LIT(KQueryPlaylistSongInfoCleanup, "DELETE FROM :dbname.PlaylistSongInfo WHERE SongId NOT IN (SELECT DISTINCT SongId FROM :dbname.PlaylistSongs)");

   385 _LIT(KQueryPlaylistSongInfoUpdateTitle, "UPDATE :dbname.PlaylistSongInfo SET Title='%S' WHERE SongId=%u");

   386 _LIT(KQueryPlaylistSongInfoUpdate, "UPDATE :dbname.PlaylistSongInfo SET SongId=%u,Uri='%S',VolumeId=%u WHERE SongId=%u");

   387 _LIT(KQueryPlaylistSongInfoUpdateFlags, "UPDATE :dbname.PlaylistSongInfo SET DbFlag=%u WHERE SongId=%u");

   388 

   389 _LIT(KQueryPlaylistSongInfoExists, "SELECT SongId FROM :dbname.PlaylistSongInfo WHERE SongId=%u");

   390 _LIT(KQueryPlaylistSongInfoGet, "SELECT * FROM :dbname.PlaylistSongInfo WHERE SongId=%u");

   391 

   392 // == Criteria strings ========================================================

   393 //

   394 // Music criteria

   395 _LIT(KCriterionMusicUniqueId, "Music.UniqueId=%u");

   396 _LIT(KCriterionMusicNotDeleted, "Music.Deleted=0");

   397 _LIT(KCriterionMusicArtist, "Music.artist=%u");

   398 _LIT(KCriterionMusicAlbum, "Music.album=%u");

   399 _LIT(KCriterionMusicGenre, "Music.genre=%u");

   400 _LIT(KCriterionMusicComposer, "Music.composer=%u");

   401 // SQLite requires LIKE statement to include an ESCAPE clause if an escaped percentage

   402 // or underscore is to be used as regular search text instead of pattern matching

   403 // characters. See http://www.sqlite.org/lang_expr.html for an explanation. To supprt

   404 // partial title matching, our goal is to have the following statement as an example:

   405 //      SELECT * FROM Music WHERE title LIKE '%xyz%' ESCAPE '\'

   406 // To achieve this, we specify the format to be %%%%%S%%%%. The 1st percentage is the

   407 // escape character for string formatter to output the 2nd percentage sign. Similiarly

   408 // for the 3rd, 4th, 6th, 7th, 8th, and 9th. The 5th percentage instructs string formatter

   409 // to substitute a string, i.e. %S. After subsititution, we will have, for example, %%xyz%%.

   410 // Because this substituted string will be substituted again into a WHERE clause, we need

   411 // to escape the percentage signs in order to get %xyz% as the end result.

   412 _LIT(KCriterionMusicTitle, "Music.title LIKE '%%%%%S%%%%' ESCAPE '\\'");

   413 _LIT(KCriterionMusicVolume, "Music.VolumeId=%u");

   414 _LIT(KCriterionMusicLocation, "Music.Location='%S'");

   415 _LIT(KCriterionMusicSync, "Music.Sync=%u");

   416 _LIT(KCriterionMusicDeleted, "Music.Deleted=%u");

   417 _LIT(KCriterionMusicModified, "Music.Modified=%u");

   418 

   419 // Category criteria

   420 _LIT(KCriterionCategoryUniqueId, "##table##.UniqueId=%u");

   421 _LIT(KCriterionCategoryName, "##table##.Name LIKE '%%%%%S%%%%' ESCAPE '\\'");

   422 

   423 // Playlist criteria

   424 _LIT(KCriterionPlaylistUniqueId, "UniqueId=%u");

   425 _LIT(KCriterionPlaylistVolumeId, "VolumeId=%u");

   426 _LIT(KCriterionPlaylistTitle, "Name='%S'");

   427 _LIT(KCriterionPlaylistLikeTitle, "Name LIKE '%%%%%S%%%%' ESCAPE '\\'");

   428 _LIT(KCriterionPlaylistUri, "Uri='%S'");

   429 _LIT(KCriterionPlaylistSync, "Sync=%u");

   430 _LIT(KCriterionPlaylistTime, "Time='%S'");

   431 

   432 // PlaylistSongs criteria

   433 _LIT(KCriterionPlaylistSongId, "PlaylistSongs.SongId=%u");

   434 

   435 // == Album table queries ==========================================

   436 //

   437 _LIT(KAlbumCreateTable,"CREATE TABLE Album("

   438     L"UniqueId INTEGER PRIMARY KEY,"

   439     L"ArtistName TEXT COLLATE NOCASE,"

   440     L"Name TEXT COLLATE NOCASE,"

   441     L"SongCount INTEGER,"

   442 	L"Art TEXT)");

   443 

   444 _LIT(KAlbumCheckTable,"SELECT UniqueId,ArtistName,Name,SongCount,Art FROM Album");

   445 

   446 _LIT(KQueryAlbumInsert, "INSERT INTO :dbname.Album(UniqueId,ArtistName,Name,SongCount,Art) VALUES(%u,'%S','%S',%u,'%S')");

   447 _LIT(KQueryAlbumUpdate, "UPDATE :dbname.Album SET %S WHERE UniqueId=%u");

   448 

   449 _LIT(KQueryAlbumItem, "SELECT Album.*,Artist.Name FROM :dbname.Album,:dbname.Artist WHERE Album.Artist=Artist.UniqueId AND UniqueId=%u");

   450 _LIT(KQueryAlbumAll, "SELECT Album.* FROM :dbname.Album ORDER BY ArtistName, Name");

   451 _LIT(KCriterionArtistName, "ArtistName='%S'");

   452 _LIT(KCriterionArt, "Art='%S'");

   453 

   454 // == Artist table queries ==========================================

   455 //

   456 _LIT(KArtistCreateTable,"CREATE TABLE Artist("

   457     L"UniqueId INTEGER PRIMARY KEY,"

   458     L"Name TEXT COLLATE NOCASE,"

   459     L"SongCount INTEGER,"

   460     L"Art TEXT)");

   461 

   462 _LIT(KArtistCheckTable,"SELECT UniqueId,Name,SongCount,Art FROM Artist");

   463 

   464 _LIT(KQueryArtistInsert, "INSERT INTO :dbname.Artist(UniqueId,Name,SongCount,Art) VALUES(%u,'%S',%u,'%S')");

   465 _LIT(KQueryArtistUpdate, "UPDATE :dbname.Artist SET %S WHERE UniqueId=%u");

   466 

   467 _LIT(KQueryArtistAll, "SELECT Artist.* FROM :dbname.Artist ORDER BY 2");

   468 _LIT(KQueryArtistName, "SELECT Artist.Name FROM :dbname.Artist WHERE UniqueId=%u");

   469 

   470 #ifdef ABSTRACTAUDIOALBUM_INCLUDED

   471 // == AbstractAlbum table queries ==========================================

   472 //

   473 _LIT(KAbstractAlbumCreateTable,"CREATE TABLE AbstractAlbum("

   474     L"UniqueId INTEGER PRIMARY KEY,"

   475     L"Uri TEXT,"

   476     L"Name TEXT COLLATE NOCASE,"

   477     L"AlbumArtist TEXT,"

   478     L"SongCount INTEGER,"

   479     L"VolumeId INTEGER)");

   480 

   481 _LIT(KAbstractAlbumCheckTable,"SELECT UniqueId,Uri,Name,AlbumArtist,SongCount,VolumeId FROM AbstractAlbum");

   482 _LIT(KCriterionAbstractAlbumVolumeId, "VolumeId=%u");

   483 _LIT(KQueryAbstractAlbumInsert, "INSERT INTO :dbname.AbstractAlbum(UniqueId,Uri,Name,AlbumArtist,SongCount,VolumeId) VALUES(%u,'%S','%S','%S',%u,%u)");

   484 _LIT(KQueryAbstractAlbumUpdate, "UPDATE :dbname.AbstractAlbum SET %S WHERE UniqueId=%u");

   485 _LIT(KCriterionCategoryVolumeId, "SELECT VolumeId FROM :dbname.AbstractAlbum WHERE UniqueId=%u");

   486 _LIT(KQueryAbstractAlbumsWithNoSong, "SELECT * FROM :dbname.AbstractAlbum WHERE SongCount=0");

   487 #endif // ABSTRACTAUDIOALBUM_INCLUDED

   488 #endif // MPXDBPLUGINQUERIES_H