|
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 _LIT(KMusicCreateTable, "CREATE TABLE Music(" |
|
27 L"UniqueId INTEGER PRIMARY KEY," |
|
28 L"DbFlag INTEGER," |
|
29 L"VolumeId INTEGER," |
|
30 L"Title TEXT COLLATE NOCASE," |
|
31 L"Artist INTEGER," |
|
32 L"Art TEXT," |
|
33 L"Deleted INTEGER DEFAULT 0," |
|
34 L"Location TEXT," |
|
35 L"AlbumTrack INTEGER," |
|
36 L"PlayCount INTEGER DEFAULT 0," |
|
37 L"TimeAdded TEXT," |
|
38 L"TimePlayed TEXT DEFAULT ''," |
|
39 L"Duration INTEGER," |
|
40 L"Sync INTEGER DEFAULT 0," |
|
41 L"Modified INTEGER DEFAULT 0," |
|
42 L"Album INTEGER," |
|
43 L"Genre INTEGER," |
|
44 L"Composer INTEGER," |
|
45 L"ReleaseDate TEXT DEFAULT ''," |
|
46 L"Rating INTEGER," |
|
47 L"Comment TEXT," |
|
48 L"Copyright TEXT," |
|
49 L"Url TEXT," |
|
50 L"DRM INTEGER," |
|
51 L"LastPlayPosition INTEGER DEFAULT 0," |
|
52 L"SampleRate INTEGER," |
|
53 L"BitRate INTEGER," |
|
54 L"NumChannels INTEGER," |
|
55 L"Codec INTEGER," |
|
56 L"MimeType TEXT," |
|
57 L"MTPDrmStatus INTEGER)"); |
|
58 |
|
59 _LIT(KMusicDropTable,"DROP TABLE Music"); |
|
60 _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"); |
|
61 |
|
62 // indexes |
|
63 _LIT(KMusicDeletedIdIndex,"CREATE INDEX IndexMusicDeletedId ON Music(Deleted,UniqueId)"); |
|
64 _LIT(KMusicDeletedTitleIndex,"CREATE INDEX IndexMusicDeletedTitle ON Music(Deleted,Title)"); |
|
65 _LIT(KMusicDeletedArtistTitleIndex,"CREATE INDEX IndexMusicArtistDeletedTitle ON Music(Artist,Deleted,Title)"); |
|
66 _LIT(KMusicDeletedAlbumTitleIndex,"CREATE INDEX IndexMusicAlbumDeletedTitle ON Music(Album,Deleted,Title)"); |
|
67 _LIT(KMusicDeletedAlbumTrackTitleIndex,"CREATE INDEX IndexMusicAlbumDeletedTrackTitle ON Music(Album,Deleted,AlbumTrack,Title)"); |
|
68 _LIT(KMusicDeletedGenreTitleIndex,"CREATE INDEX IndexMusicGenreDeletedTitle ON Music(Genre,Deleted,Title)"); |
|
69 _LIT(KMusicDeletedComposerTitleIndex,"CREATE INDEX IndexMusicComposerDeletedTitle ON Music(Composer,Deleted,Title)"); |
|
70 |
|
71 _LIT(KQueryMusicInsert, "INSERT INTO :dbname.Music(%S) VALUES(%S)"); |
|
72 _LIT(KQueryMusicUpdate, "UPDATE :dbname.Music SET %S WHERE UniqueId=%u"); |
|
73 _LIT(KQueryMusicDelete, "DELETE FROM :dbname.Music WHERE UniqueId=%u"); |
|
74 _LIT(KQueryMusicDeleteUpdate, "UPDATE :dbname.Music SET Deleted=1 WHERE UniqueId=%u"); |
|
75 _LIT(KQueryMusicDeleteForCategory, "UPDATE :dbname.Music SET Deleted=0 WHERE %S=%u"); |
|
76 _LIT(KQueryMusicCleanup, "DELETE FROM :dbname.Music WHERE Deleted=1"); |
|
77 |
|
78 // Note: The no categories queries below only retrieve a limited number of fields from the Music table |
|
79 // as opposed to *. This is an optimization that matches the DBMS version of the plugin and assumes |
|
80 // that the client only requests those fields. From the C++ code perspective there is no difference |
|
81 // in processing as the five fields in question reside at the beginning of the table. If the client |
|
82 // is modified to request more attributes then the queries below will have to be changed to return *. |
|
83 // |
|
84 // As far as performance goes, the queries that only return six fields are a little faster than |
|
85 // the ones that return *, for example, for a * query that takes 2.1 seconds the equivalent |
|
86 // optimized query is faster by more than 100ms. |
|
87 |
|
88 _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"); |
|
89 |
|
90 // LTAN-79N8ND: temporary fix. Symbian 9.4 SQLite cannot resolve column number correctly, |
|
91 // Use column number instead of column name for ORDER BY, needed when FROM uses more than 1 table |
|
92 // calculate column number from SELECT fields, first field is column 1 (not 0) |
|
93 /* |
|
94 _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"); |
|
95 _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"); |
|
96 _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"); |
|
97 _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"); |
|
98 _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"); |
|
99 _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"); |
|
100 _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"); |
|
101 _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"); |
|
102 _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"); |
|
103 _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"); |
|
104 */ |
|
105 _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"); |
|
106 _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"); |
|
107 _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"); |
|
108 _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"); |
|
109 _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"); |
|
110 _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"); |
|
111 _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"); |
|
112 _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"); |
|
113 _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"); |
|
114 _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"); |
|
115 |
|
116 _LIT(KQueryMusicSong, "SELECT UniqueId,DbFlag,VolumeId,Title,0,'',0,Location FROM :dbname.Music WHERE %S"); |
|
117 _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"); |
|
118 |
|
119 // LTAN-79N8ND: temporary fix. Symbian 9.4 SQLite cannot resolve column number correctly, |
|
120 // Use column number instead of column name for ORDER BY, needed when FROM uses more than 1 table |
|
121 // calculate column number from SELECT fields, first field is column 1 (not 0) |
|
122 /* |
|
123 _LIT(KQueryMusicFindAllSortOrderTrack, "ORDER BY Music.AlbumTrack,Music.Title"); |
|
124 _LIT(KQueryMusicFindAllSortOrderTitle, "ORDER BY Music.Title"); |
|
125 */ |
|
126 _LIT(KQueryMusicFindAllSortOrderTrack, "ORDER BY 9, 4"); |
|
127 _LIT(KQueryMusicFindAllSortOrderTitle, "ORDER BY 4"); |
|
128 |
|
129 // no categories queries |
|
130 _LIT(KQueryMusicGetSongNoCategories, "SELECT UniqueId,DbFlag,VolumeId,Title,0,'',0,Location FROM :dbname.Music WHERE Deleted=0 AND UniqueId=%u"); |
|
131 _LIT(KQueryMusicGetAllSongsNoCategories, "SELECT UniqueId,DbFlag,VolumeId,Title,0,'',0,Location FROM :dbname.Music WHERE Deleted=0 ORDER BY Title"); |
|
132 _LIT(KQueryMusicGetSongsForArtistNoCategories, "SELECT UniqueId,DbFlag,VolumeId,Title,0,'',0,Location FROM :dbname.Music WHERE Deleted=0 AND Artist=%u ORDER BY Title"); |
|
133 _LIT(KQueryMusicGetSongsForAlbumNoCategories, "SELECT UniqueId,DbFlag,VolumeId,Title,0,'',0,Location,AlbumTrack FROM :dbname.Music WHERE Deleted=0 AND Album=%u ORDER BY AlbumTrack,Title"); |
|
134 _LIT(KQueryMusicGetSongsForUnknownAlbumNoCategories, "SELECT UniqueId,DbFlag,VolumeId,Title,0,'',0,Location FROM :dbname.Music WHERE Deleted=0 AND Album=%u ORDER BY Title"); |
|
135 _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"); |
|
136 _LIT(KQueryMusicGetSongsForGenreNoCategories, "SELECT UniqueId,DbFlag,VolumeId,Title,0,'',0,Location FROM :dbname.Music WHERE Deleted=0 AND Genre=%u ORDER BY Title"); |
|
137 _LIT(KQueryMusicGetSongsForComposerNoCategories, "SELECT UniqueId,DbFlag,VolumeId,Title,0,'',0,Location FROM :dbname.Music WHERE Deleted=0 AND Composer=%u ORDER BY Title"); |
|
138 _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"); |
|
139 _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"); |
|
140 _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"); |
|
141 |
|
142 _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"); |
|
143 _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"); |
|
144 _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"); |
|
145 _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"); |
|
146 |
|
147 _LIT(KQueryMusicGetUri, "SELECT Location,VolumeId FROM :dbname.Music WHERE Deleted=0 AND UniqueId=%u"); |
|
148 _LIT(KQueryMusicGetTitle, "SELECT Title FROM :dbname.Music WHERE Deleted=0 AND UniqueId=%u"); |
|
149 _LIT(KQueryMusicGetMusicUris, "SELECT UniqueId,Location FROM :dbname.Music WHERE Deleted=0 ORDER BY UniqueId ASC LIMIT %u"); |
|
150 _LIT(KQueryMusicGetMusicUrisFrom, "SELECT UniqueId,Location FROM :dbname.Music WHERE Deleted=0 AND UniqueId > %u ORDER BY UniqueId ASC LIMIT %u"); |
|
151 |
|
152 _LIT(KQueryMusicCount, "SELECT count(*) FROM :dbname.Music WHERE Deleted=0"); |
|
153 _LIT(KQueryMusicAlbum, "SELECT DISTINCT Album FROM :dbname.Music WHERE Deleted=0 AND Artist=%u ORDER BY Album"); |
|
154 _LIT(KQueryMusicVolume, "SELECT VolumeId FROM :dbname.Music WHERE Deleted=0 AND UniqueId=%u"); |
|
155 _LIT(KQueryMusicVolumeAll, "SELECT VolumeId FROM :dbname.Music WHERE UniqueId=%u"); |
|
156 _LIT(KQueryMusicInfo, "SELECT * FROM :dbname.Music WHERE Deleted=0 AND UniqueId=%u"); |
|
157 _LIT(KQueryOtherSongsInArtistAlbum, "SELECT count(*) FROM :dbname.Music WHERE Deleted=0 AND UniqueId<>%u AND Artist=%u AND Album=%u"); |
|
158 |
|
159 _LIT(KQueryMusicDurationAll, "SELECT sum(Duration) FROM :dbname.Music WHERE Deleted=0"); |
|
160 _LIT(KQueryMusicDurationArtist, "SELECT sum(Duration) FROM :dbname.Music WHERE Deleted=0 AND Artist=%u"); |
|
161 _LIT(KQueryMusicDurationAlbum, "SELECT sum(Duration) FROM :dbname.Music WHERE Deleted=0 AND Album=%u"); |
|
162 _LIT(KQueryMusicDurationArtistAlbum, "SELECT sum(Duration) FROM :dbname.Music WHERE Deleted=0 AND Artist=%u AND Album=%u"); |
|
163 _LIT(KQueryMusicDurationGenre, "SELECT sum(Duration) FROM :dbname.Music WHERE Deleted=0 AND Genre=%u"); |
|
164 _LIT(KQueryMusicDurationComposer, "SELECT sum(Duration) FROM :dbname.Music WHERE Deleted=0 AND Composer=%u"); |
|
165 _LIT(KQueryMusicDurationRecentlyPlayed, "SELECT sum(Duration) FROM (SELECT Duration,TimePlayed FROM :dbname.Music WHERE Deleted=0 AND TimePlayed<>'' ORDER BY TimePlayed DESC LIMIT %u)"); |
|
166 _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)"); |
|
167 _LIT(KQueryMusicDurationRecentlyAdded, "SELECT sum(Duration) FROM :dbname.Music WHERE Deleted=0 AND (julianday(TimeAdded) > julianday('now','-7 days'))"); |
|
168 |
|
169 _LIT(KQueryMusicGetDurationRecentlyPlayed, "SELECT Duration,TimePlayed FROM :dbname.Music WHERE Deleted=0 AND TimePlayed<>'' ORDER BY TimePlayed DESC LIMIT %u"); |
|
170 _LIT(KQueryMusicGetDurationMostPlayed, "SELECT Duration,PlayCount,TimePlayed FROM :dbname.Music WHERE Deleted=0 AND PlayCount<>0 ORDER BY PlayCount DESC,TimePlayed DESC LIMIT %u"); |
|
171 _LIT(KQueryMusicGetDurationRecentlyAdded, "SELECT Duration FROM :dbname.Music WHERE Deleted=0 AND (julianday(TimeAdded) > julianday('now','-7 days'))"); |
|
172 |
|
173 // Select songs information from music, playlistsongs and playlistsonginfo via playlistid order by playlistsongs's ordinal |
|
174 _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"); |
|
175 _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"); |
|
176 |
|
177 _LIT(KQuerySongsInArtistAlbum, "SELECT count(*) FROM :dbname.Music WHERE Deleted=0 AND Artist=%u AND Album=%u"); |
|
178 _LIT(KQueryMusicGetArtistForAlbum, "SELECT Artist FROM :dbname.Music,:dbname.Artist WHERE Music.Deleted=0 AND Music.Artist=Artist.UniqueId AND Music.Album=%u"); |
|
179 |
|
180 // == Category table queries ================================================== |
|
181 // |
|
182 _LIT(KCategoryTablePlaceholder, "##table##"); |
|
183 |
|
184 _LIT(KCategoryCreateTable,"CREATE TABLE ##table##(" |
|
185 L"UniqueId INTEGER PRIMARY KEY," |
|
186 L"Name TEXT COLLATE NOCASE," |
|
187 L"SongCount INTEGER)"); |
|
188 |
|
189 _LIT(KCategoryDropTable,"DROP TABLE ##table##"); |
|
190 _LIT(KCategoryCheckTable,"SELECT UniqueId,Name,SongCount FROM ##table##"); |
|
191 _LIT(KCategoryNameIndex,"CREATE INDEX Index##table##Name ON ##table##(Name)"); |
|
192 |
|
193 _LIT(KQueryCategoryInsert, "INSERT INTO :dbname.##table##(UniqueId,Name,SongCount) VALUES(%u,'%S',%u)"); |
|
194 _LIT(KQueryCategoryDelete, "DELETE FROM :dbname.##table## WHERE UniqueId=%u"); |
|
195 |
|
196 _LIT(KQueryCategoryCount, "SELECT count(*) FROM :dbname.##table##"); |
|
197 _LIT(KQueryCategoryItem, "SELECT * FROM :dbname.##table## WHERE UniqueId=%u"); |
|
198 _LIT(KQueryCategoryItemBySongId, "SELECT ##table##.* FROM :dbname.##table##,:dbname.Music WHERE Music.Deleted=0 AND Music.UniqueId=%u AND ##table##.UniqueId=Music.%S"); |
|
199 _LIT(KQueryCategoryAll, "SELECT * FROM :dbname.##table## ORDER BY Name"); |
|
200 _LIT(KQueryCategoryItems, "SELECT * FROM :dbname.##table## WHERE %S ORDER BY Name ASC"); |
|
201 _LIT(KQueryCategorySongCount, "SELECT SongCount FROM :dbname.##table## WHERE UniqueId=%u"); |
|
202 _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"); |
|
203 |
|
204 _LIT(KQueryCategoryIncrementSongCount, "UPDATE :dbname.##table## SET SongCount=SongCount+1 WHERE UniqueId=%u"); |
|
205 _LIT(KQueryCategoryDecrementSongCount, "UPDATE :dbname.##table## SET SongCount=SongCount-1 WHERE UniqueId=%u"); |
|
206 _LIT(KQueryCategoryGetSongCount, "SELECT SongCount FROM :dbname.##table## WHERE UniqueId=%u"); |
|
207 |
|
208 // == Auxiliary table queries ================================================= |
|
209 // |
|
210 _LIT(KAuxiliaryCreateTable, "CREATE TABLE Auxiliary(" |
|
211 L"Id INTEGER," |
|
212 L"Version TEXT," |
|
213 L"TimeRefreshed TEXT," |
|
214 L"TimeSynced TEXT," |
|
215 L"Corrupt INTEGER DEFAULT 0," |
|
216 L"SaveDeletedRecordCount INTEGER DEFAULT 0)"); |
|
217 _LIT(KAuxiliaryDropTable,"DROP TABLE Auxiliary"); |
|
218 _LIT(KAuxiliaryCheckTable, "SELECT Id,Version,TimeRefreshed,TimeSynced,Corrupt,SaveDeletedRecordCount FROM AUXILIARY"); |
|
219 _LIT(KQueryAuxiliaryInsert, "INSERT INTO Auxiliary(Id,Version,Corrupt) VALUES(0,'6.4.0',%u)"); |
|
220 |
|
221 _LIT(KQueryAuxiliarySetTime, "UPDATE :dbname.Auxiliary SET TimeRefreshed='%S', Corrupt=0"); |
|
222 _LIT(KQueryAuxiliaryGetTime, "SELECT TimeRefreshed FROM :dbname.Auxiliary"); |
|
223 _LIT(KQueryAuxiliarySetCorrupt, "UPDATE :dbname.Auxiliary SET Corrupt=%u"); |
|
224 _LIT(KQueryAuxiliaryGetCorrupt, "SELECT Corrupt FROM :dbname.Auxiliary"); |
|
225 _LIT(KQueryAuxiliaryGetCount, "SELECT SaveDeletedRecordCount FROM :dbname.Auxiliary"); |
|
226 _LIT(KQueryAuxiliarySetCount, "UPDATE :dbname.Auxiliary SET SaveDeletedRecordCount=%u"); |
|
227 _LIT(KQueryAuxiliaryGetId, "SELECT Id FROM :dbname.Auxiliary"); |
|
228 _LIT(KQueryAuxiliarySetId, "UPDATE :dbname.Auxiliary SET Id=%u"); |
|
229 |
|
230 // == Playlist table queries ================================================== |
|
231 // |
|
232 _LIT(KPlaylistCreateTable, "CREATE TABLE Playlist(" |
|
233 L"UniqueId INTEGER PRIMARY KEY," |
|
234 L"VolumeId INTEGER," |
|
235 L"DbFlag INTEGER," |
|
236 L"Sync INTEGER," |
|
237 L"Name TEXT COLLATE NOCASE," |
|
238 L"Uri TEXT," |
|
239 L"Time TEXT)"); |
|
240 _LIT(KPlaylistDropTable,"DROP TABLE Playlist"); |
|
241 _LIT(KPlaylistCheckTable, "SELECT UniqueId,Name,Uri,VolumeId,Sync,DbFlag,Time FROM Playlist"); |
|
242 _LIT(KPlaylistNameIndex,"CREATE INDEX IndexPlaylistName ON Playlist(Name)"); |
|
243 |
|
244 _LIT(KQueryPlaylistInsert, "INSERT INTO :dbname.Playlist(UniqueId,Name,Uri,VolumeId,Sync,DbFlag,Time) VALUES(%u,'%S','%S',%u,0,%u,'%S')"); |
|
245 _LIT(KQueryPlaylistUpdate, "UPDATE :dbname.Playlist SET %S WHERE UniqueId=%u"); |
|
246 _LIT(KQueryPlaylistUpdateTime, "UPDATE :dbname.Playlist SET Time='%S' WHERE UniqueId=%u"); |
|
247 _LIT(KQueryPlaylistUpdateTimeForSong, "UPDATE :dbname.Playlist SET Time='%S' WHERE UniqueId IN (SELECT DISTINCT PlaylistId FROM :dbname.PlaylistSongs WHERE SongId=%u)"); |
|
248 _LIT(KQueryPlaylistDeleteAll, "DELETE FROM :dbname.Playlist"); |
|
249 _LIT(KQueryPlaylistDelete, "DELETE FROM :dbname.Playlist WHERE UniqueId=%u"); |
|
250 |
|
251 _LIT(KQueryPlaylistCount, "SELECT count(*) FROM :dbname.Playlist"); |
|
252 _LIT(KQueryPlaylistGetAll, "SELECT * FROM :dbname.Playlist ORDER BY Name ASC"); |
|
253 _LIT(KQueryPlaylistGet, "SELECT * FROM :dbname.Playlist WHERE UniqueId=%u"); |
|
254 _LIT(KQueryPlaylistDuration, "SELECT sum(Music.Duration) FROM :dbname.Music,:dbname.Playlist,:dbname.PlaylistSongs WHERE Music.UniqueId=PlaylistSongs.SongId AND PlaylistSongs.PlaylistId=%u"); |
|
255 _LIT(KQueryPlaylistId, "SELECT UniqueId FROM :dbname.Playlist WHERE Uri='%S'"); |
|
256 _LIT(KQueryLikePlaylistId, "SELECT UniqueId FROM :dbname.Playlist WHERE Uri LIKE '%%%%%S%%%%' ESCAPE '\\'"); |
|
257 _LIT(KQueryPlaylistDriveId, "SELECT VolumeId FROM :dbname.Playlist WHERE UniqueId=%u"); |
|
258 _LIT(KQueryPlaylistItems, "SELECT * FROM :dbname.Playlist WHERE %S ORDER BY Name ASC"); |
|
259 _LIT(KQueryPlaylistGetForSong, "SELECT DISTINCT PlaylistId FROM :dbname.PlaylistSongs WHERE SongId=%u"); |
|
260 _LIT(KQueryPlaylistFileCount, "SELECT count(*) FROM :dbname.Playlist WHERE Uri LIKE '%%.m3u'"); |
|
261 //see "Efficient Use of Symbinan SQL How-To Document" for details about following queries: |
|
262 _LIT(KQueryPlaylistGetFileUris, "SELECT UniqueId,Uri FROM :dbname.Playlist WHERE Uri LIKE '%%%%.m3u' ORDER BY UniqueId ASC LIMIT %u"); |
|
263 _LIT(KQueryPlaylistGetFileUrisFrom, "SELECT UniqueId,Uri FROM :dbname.Playlist WHERE Uri LIKE '%%%%.m3u' AND UniqueId > %u ORDER BY UniqueId ASC LIMIT %u"); |
|
264 |
|
265 // == PlaylistSongs table queries ============================================= |
|
266 // |
|
267 _LIT(KPlaylistSongsCreateTable, "CREATE TABLE PlaylistSongs(" |
|
268 L"UniqueId INTEGER PRIMARY KEY AUTOINCREMENT," |
|
269 L"SongId INTEGER," |
|
270 L"PlaylistId INTEGER," |
|
271 L"Ordinal INTEGER)"); |
|
272 _LIT(KPlaylistSongsDropTable,"DROP TABLE PlaylistSongs"); |
|
273 _LIT(KPlaylistSongsCheckTable, "SELECT UniqueId,SongId,PlaylistId,Ordinal FROM PlaylistSongs"); |
|
274 |
|
275 _LIT(KQueryPlaylistSongsInsert, "INSERT INTO :dbname.PlaylistSongs(PlaylistId,SongId,Ordinal) VALUES(%u,%u,%u)"); |
|
276 _LIT(KQueryPlaylistSongsUpdate, "UPDATE :dbname.PlaylistSongs SET SongId=%u WHERE SongId=%u"); |
|
277 _LIT(KQueryPlaylistSongsUpdatePlaylistId, "UPDATE :dbname.PlaylistSongs SET PlaylistId=%u WHERE PlaylistId=%u"); |
|
278 _LIT(KQueryPlaylistSongsDelete, "DELETE FROM :dbname.PlaylistSongs WHERE SongId=%u"); |
|
279 _LIT(KQueryPlaylistSongsDeleteAll, "DELETE FROM :dbname.PlaylistSongs"); |
|
280 _LIT(KQueryPlaylistSongsDeletePlaylist, "DELETE FROM :dbname.PlaylistSongs WHERE PlaylistId=%u"); |
|
281 _LIT(KQueryPlaylistSongsDeleteSong, "DELETE FROM :dbname.PlaylistSongs WHERE PlaylistId=%u AND SongId=%u"); |
|
282 _LIT(KQueryPlaylistSongsDeleteSongOrdinal, "DELETE FROM :dbname.PlaylistSongs WHERE PlaylistId=%u AND SongId=%u AND Ordinal=%u"); |
|
283 _LIT(KQueryPlaylistSongsDeleteForCategory, "DELETE FROM :dbname.PlaylistSongs WHERE PlaylistSongs.SongId=Music.UniqueId AND Music.%S=%u"); |
|
284 _LIT(KQueryPlaylistSongsDeleteForArtistAlbum, "DELETE FROM :dbname.PlaylistSongs WHERE PlaylistSongs.SongId=Music.UniqueId AND Music.Artist=%u AND Music.Album=%u"); |
|
285 |
|
286 _LIT(KQueryPlaylistSongsCount, "SELECT count(*) FROM :dbname.PlaylistSongs WHERE PlaylistId=%u"); |
|
287 _LIT(KQueryPlaylistSongsPlaylistSongCount, "SELECT count(*) FROM :dbname.PlaylistSongs WHERE PlaylistId=%u AND SongId=%u"); |
|
288 _LIT(KQueryPlaylistSongsSongCount, "SELECT count(*) FROM :dbname.PlaylistSongs WHERE SongId=%u"); |
|
289 _LIT(KQueryPlaylistSongsGetSongs, "SELECT UniqueId,SongId,Ordinal FROM :dbname.PlaylistSongs WHERE PlaylistId=%u ORDER BY Ordinal"); |
|
290 |
|
291 // LTAN-79N8ND: temporary fix. Symbian 9.4 SQLite cannot resolve column number correctly, |
|
292 // Use column number instead of column name for ORDER BY, needed when FROM uses more than 1 table |
|
293 // calculate column number from SELECT fields, first field is column 1 (not 0) |
|
294 //_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"); |
|
295 _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"); |
|
296 _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"); |
|
297 |
|
298 _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"); |
|
299 |
|
300 _LIT(KQueryPlaylistSongsUpdateSongOrdinalAfterDelete, "UPDATE :dbname.PlaylistSongs SET Ordinal=Ordinal-1 WHERE PlaylistId=%u AND Ordinal>%u"); |
|
301 _LIT(KQueryPlaylistSongsUpdateSongOrdinal, "UPDATE :dbname.PlaylistSongs SET Ordinal=%u WHERE UniqueId=%u"); |
|
302 _LIT(KQueryPlaylistSongsMoveSongUp, "UPDATE :dbname.PlaylistSongs SET Ordinal=Ordinal+1 WHERE PlaylistId=%u AND Ordinal>=%u AND Ordinal<%u"); |
|
303 _LIT(KQueryPlaylistSongsMoveSongDown, "UPDATE :dbname.PlaylistSongs SET Ordinal=Ordinal-1 WHERE PlaylistId=%u AND Ordinal<=%u AND Ordinal>%u"); |
|
304 |
|
305 // == PlaylistSongInfo table queries ========================================== |
|
306 // |
|
307 _LIT(KPlaylistSongInfoCreateTable, "CREATE TABLE PlaylistSongInfo(" |
|
308 L"SongId INTEGER PRIMARY KEY," |
|
309 L"VolumeId INTEGER," |
|
310 L"DbFlag INTEGER," |
|
311 L"Uri TEXT," |
|
312 L"Title TEXT COLLATE NOCASE)"); |
|
313 _LIT(KPlaylistSongInfoDropTable,"DROP TABLE PlaylistSongInfo"); |
|
314 _LIT(KPlaylistSongInfoCheckTable, "SELECT SongId,VolumeId,DbFlag,Uri,Title FROM PlaylistSongInfo"); |
|
315 |
|
316 _LIT(KQueryPlaylistSongInfoInsert, "INSERT INTO :dbname.PlaylistSongInfo(SongId,Uri,Title,VolumeId,DbFlag) VALUES(%u,'%S','%S',%u,%u)"); |
|
317 _LIT(KQueryPlaylistSongInfoDelete, "DELETE FROM :dbname.PlaylistSongInfo WHERE SongId=%u"); |
|
318 _LIT(KQueryPlaylistSongInfoDeleteAll, "DELETE FROM :dbname.PlaylistSongInfo"); |
|
319 _LIT(KQueryPlaylistSongInfoCleanup, "DELETE FROM :dbname.PlaylistSongInfo WHERE SongId NOT IN (SELECT DISTINCT SongId FROM :dbname.PlaylistSongs)"); |
|
320 _LIT(KQueryPlaylistSongInfoUpdateTitle, "UPDATE :dbname.PlaylistSongInfo SET Title='%S' WHERE SongId=%u"); |
|
321 _LIT(KQueryPlaylistSongInfoUpdate, "UPDATE :dbname.PlaylistSongInfo SET SongId=%u,Uri='%S',VolumeId=%u WHERE SongId=%u"); |
|
322 _LIT(KQueryPlaylistSongInfoUpdateFlags, "UPDATE :dbname.PlaylistSongInfo SET DbFlag=%u WHERE SongId=%u"); |
|
323 |
|
324 _LIT(KQueryPlaylistSongInfoExists, "SELECT SongId FROM :dbname.PlaylistSongInfo WHERE SongId=%u"); |
|
325 _LIT(KQueryPlaylistSongInfoGet, "SELECT * FROM :dbname.PlaylistSongInfo WHERE SongId=%u"); |
|
326 |
|
327 // == Criteria strings ======================================================== |
|
328 // |
|
329 // Music criteria |
|
330 _LIT(KCriterionMusicUniqueId, "Music.UniqueId=%u"); |
|
331 _LIT(KCriterionMusicNotDeleted, "Music.Deleted=0"); |
|
332 _LIT(KCriterionMusicArtist, "Music.artist=%u"); |
|
333 _LIT(KCriterionMusicAlbum, "Music.album=%u"); |
|
334 _LIT(KCriterionMusicGenre, "Music.genre=%u"); |
|
335 _LIT(KCriterionMusicComposer, "Music.composer=%u"); |
|
336 // SQLite requires LIKE statement to include an ESCAPE clause if an escaped percentage |
|
337 // or underscore is to be used as regular search text instead of pattern matching |
|
338 // characters. See http://www.sqlite.org/lang_expr.html for an explanation. To supprt |
|
339 // partial title matching, our goal is to have the following statement as an example: |
|
340 // SELECT * FROM Music WHERE title LIKE '%xyz%' ESCAPE '\' |
|
341 // To achieve this, we specify the format to be %%%%%S%%%%. The 1st percentage is the |
|
342 // escape character for string formatter to output the 2nd percentage sign. Similiarly |
|
343 // for the 3rd, 4th, 6th, 7th, 8th, and 9th. The 5th percentage instructs string formatter |
|
344 // to substitute a string, i.e. %S. After subsititution, we will have, for example, %%xyz%%. |
|
345 // Because this substituted string will be substituted again into a WHERE clause, we need |
|
346 // to escape the percentage signs in order to get %xyz% as the end result. |
|
347 _LIT(KCriterionMusicTitle, "Music.title LIKE '%%%%%S%%%%' ESCAPE '\\'"); |
|
348 _LIT(KCriterionMusicVolume, "Music.VolumeId=%u"); |
|
349 _LIT(KCriterionMusicLocation, "Music.Location='%S'"); |
|
350 _LIT(KCriterionMusicSync, "Music.Sync=%u"); |
|
351 _LIT(KCriterionMusicDeleted, "Music.Deleted=%u"); |
|
352 _LIT(KCriterionMusicModified, "Music.Modified=%u"); |
|
353 |
|
354 // Category criteria |
|
355 _LIT(KCriterionCategoryUniqueId, "##table##.UniqueId=%u"); |
|
356 _LIT(KCriterionCategoryName, "##table##.Name LIKE '%%%%%S%%%%' ESCAPE '\\'"); |
|
357 |
|
358 // Playlist criteria |
|
359 _LIT(KCriterionPlaylistUniqueId, "UniqueId=%u"); |
|
360 _LIT(KCriterionPlaylistVolumeId, "VolumeId=%u"); |
|
361 _LIT(KCriterionPlaylistTitle, "Name='%S'"); |
|
362 _LIT(KCriterionPlaylistLikeTitle, "Name LIKE '%%%%%S%%%%' ESCAPE '\\'"); |
|
363 _LIT(KCriterionPlaylistUri, "Uri='%S'"); |
|
364 _LIT(KCriterionPlaylistSync, "Sync=%u"); |
|
365 _LIT(KCriterionPlaylistTime, "Time='%S'"); |
|
366 |
|
367 // PlaylistSongs criteria |
|
368 _LIT(KCriterionPlaylistSongId, "PlaylistSongs.SongId=%u"); |
|
369 |
|
370 // == Album table queries ========================================== |
|
371 // |
|
372 _LIT(KAlbumCreateTable,"CREATE TABLE Album(" |
|
373 L"UniqueId INTEGER PRIMARY KEY," |
|
374 L"Name TEXT COLLATE NOCASE," |
|
375 L"SongCount INTEGER," |
|
376 L"Artist INTEGER," |
|
377 L"Art TEXT)"); |
|
378 |
|
379 _LIT(KAlbumCheckTable,"SELECT UniqueId,Name,SongCount,Artist,Art FROM Album"); |
|
380 |
|
381 _LIT(KQueryAlbumInsert, "INSERT INTO :dbname.Album(UniqueId,Name,SongCount,Artist,Art) VALUES(%u,'%S',%u,%u,'%S')"); |
|
382 _LIT(KQueryAlbumUpdate, "UPDATE :dbname.Album SET %S WHERE UniqueId=%u"); |
|
383 |
|
384 _LIT(KQueryAlbumItem, "SELECT Album.*,Artist.Name FROM :dbname.Album,:dbname.Artist WHERE Album.Artist=Artist.UniqueId AND UniqueId=%u"); |
|
385 _LIT(KQueryAlbumAll, "SELECT Album.*,Artist.Name FROM :dbname.Album,:dbname.Artist WHERE Album.Artist=Artist.UniqueId ORDER BY 2"); |
|
386 |
|
387 // == Artist table queries ========================================== |
|
388 // |
|
389 _LIT(KArtistCreateTable,"CREATE TABLE Artist(" |
|
390 L"UniqueId INTEGER PRIMARY KEY," |
|
391 L"Name TEXT COLLATE NOCASE," |
|
392 L"SongCount INTEGER," |
|
393 L"Art TEXT)"); |
|
394 |
|
395 _LIT(KArtistCheckTable,"SELECT UniqueId,Name,SongCount,Art FROM Artist"); |
|
396 |
|
397 _LIT(KQueryArtistInsert, "INSERT INTO :dbname.Artist(UniqueId,Name,SongCount,Art) VALUES(%u,'%S',%u,'%S')"); |
|
398 _LIT(KQueryArtistUpdate, "UPDATE :dbname.Artist SET %S WHERE UniqueId=%u"); |
|
399 |
|
400 _LIT(KQueryArtistAll, "SELECT Artist.* FROM :dbname.Artist ORDER BY 2"); |
|
401 |
|
402 #endif // MPXDBPLUGINQUERIES_H |