53
|
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
|