|
1 /* |
|
2 * Copyright (c) 2006 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: Hard coded queries for Podcast collection db |
|
15 * |
|
16 */ |
|
17 |
|
18 |
|
19 #ifndef MPXPODCASTDBPLUGINQUERIES_H |
|
20 #define MPXPODCASTDBPLUGINQUERIES_H |
|
21 |
|
22 // Various Collection view duration queries |
|
23 _LIT(KPodcastCreateTable, "CREATE TABLE Podcast(" |
|
24 L"UniqueId INTEGER PRIMARY KEY," |
|
25 L"Deleted INTEGER DEFAULT 0," |
|
26 L"DbFlag INTEGER," |
|
27 L"VolumeId INTEGER," |
|
28 L"IsPlaying INTEGER DEFAULT 0," |
|
29 L"Title TEXT COLLATE NOCASE," |
|
30 L"Location TEXT," |
|
31 L"PublishDate TEXT DEFAULT ''," |
|
32 L"AlbumTrack INTEGER," |
|
33 L"PlayCount INTEGER DEFAULT 0," |
|
34 L"TimeAdded TEXT," |
|
35 L"TimePlayed TEXT DEFAULT ''," |
|
36 L"Duration INTEGER," |
|
37 L"Sync INTEGER DEFAULT 0," |
|
38 L"Modified INTEGER DEFAULT 0," |
|
39 L"Artist INTEGER," |
|
40 L"Album INTEGER," |
|
41 L"Genre INTEGER," |
|
42 L"Composer INTEGER," |
|
43 L"ReleaseDate TEXT DEFAULT ''," |
|
44 L"Rating INTEGER," |
|
45 L"Comment TEXT," |
|
46 L"Copyright TEXT," |
|
47 L"Art TEXT," |
|
48 L"Url TEXT," |
|
49 L"DRM INTEGER," |
|
50 L"LastPlayPosition INTEGER DEFAULT 0," |
|
51 L"SampleRate INTEGER," |
|
52 L"BitRate INTEGER," |
|
53 L"NumChannels INTEGER," |
|
54 L"Codec INTEGER," |
|
55 L"MimeType TEXT)"); |
|
56 |
|
57 _LIT(KPodcastDropTable,"DROP TABLE Podcast"); |
|
58 _LIT(KPodcastCheckTable,"SELECT UniqueId,Deleted,DbFlag,VolumeId,IsPlaying,Title,Location,PublishDate,AlbumTrack,PlayCount,TimeAdded,TimePlayed,Duration,Sync,Modified,Artist,Album,Genre,Composer,ReleaseDate,Rating,Comment,Copyright,Art,Url,DRM,LastPlayPosition,SampleRate,BitRate,NumChannels,Codec,MimeType FROM Podcast"); |
|
59 |
|
60 _LIT(KQueryPodcastInsert, "INSERT INTO :dbname.Podcast(%S) VALUES(%S)"); |
|
61 _LIT(KQueryPodcastUpdate, "UPDATE :dbname.Podcast SET %S WHERE UniqueId=%u"); |
|
62 _LIT(KQueryPodcastSetIsPlaying, "UPDATE :dbname.Podcast SET IsPlaying=%u WHERE UniqueId=%u"); |
|
63 _LIT(KQueryPodcastDelete, "DELETE FROM :dbname.Podcast WHERE UniqueId=%u"); |
|
64 _LIT(KQueryPodcastDeleteUpdate, "UPDATE :dbname.Podcast SET Deleted=1 WHERE UniqueId=%u"); |
|
65 _LIT(KQueryPodcastDeleteForCategory, "UPDATE :dbname.Podcast SET Deleted=0 WHERE %S=%u"); |
|
66 _LIT(KQueryPodcastCleanup, "DELETE FROM :dbname.Podcast WHERE Deleted=1"); |
|
67 |
|
68 _LIT(KQueryPodcastGetTitle, "SELECT Title FROM :dbname.Podcast WHERE Deleted=0 AND UniqueId=%u"); |
|
69 _LIT(KQueryPodcastVolume, "SELECT VolumeId FROM :dbname.Podcast WHERE Deleted=0 AND UniqueId=%u"); |
|
70 _LIT(KQueryPodcastAllVolumeIds, "SELECT VolumeId FROM :dbname.Podcast WHERE Deleted=0"); |
|
71 |
|
72 // LTAN-79N8ND/EVXG-7FABHC: temporary fix. Symbian 9.4 SQLite cannot resolve column number correctly, |
|
73 // Use column number instead of column name for ORDER BY, needed when FROM uses more than 1 table |
|
74 // calculate column number from SELECT fields, first field is column 1 (not 0) |
|
75 /* |
|
76 _LIT(KQueryPodcastFindAll, "SELECT Podcast.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Podcast,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE %S AND Podcast.Album=Album.UniqueId AND Podcast.Artist=Artist.UniqueId AND Podcast.Genre=Genre.UniqueId AND Podcast.Composer=Composer.UniqueId ORDER BY Podcast.Title"); |
|
77 */ |
|
78 _LIT(KQueryPodcastFindAll, "SELECT Podcast.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Podcast,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE %S AND Podcast.Album=Album.UniqueId AND Podcast.Artist=Artist.UniqueId AND Podcast.Genre=Genre.UniqueId AND Podcast.Composer=Composer.UniqueId ORDER BY 6"); |
|
79 |
|
80 _LIT(KQueryPodcastAlbum, "SELECT DISTINCT Album FROM :dbname.Podcast WHERE Deleted=0 AND Artist=%u ORDER BY Album"); |
|
81 _LIT(KQueryPodcastInfo, "SELECT * FROM :dbname.Podcast WHERE Deleted=0 AND UniqueId=%u"); |
|
82 _LIT(KQueryPodcastCategory, "SELECT * FROM :dbname.Podcast WHERE Deleted=0 AND %S=%u"); |
|
83 _LIT(KQueryPodcastCategoryOrdered, "SELECT * FROM :dbname.Podcast WHERE Deleted=0 AND %S=%u ORDER BY PublishDate DESC,Title"); |
|
84 _LIT(KQueryPodcastGetUris, "SELECT UniqueId,Location FROM :dbname.Podcast WHERE Deleted=0 ORDER BY UniqueId ASC LIMIT %u"); |
|
85 _LIT(KQueryPodcastGetUrisFrom, "SELECT UniqueId,Location FROM :dbname.Podcast WHERE Deleted=0 AND UniqueId > %u ORDER BY UniqueId ASC LIMIT %u"); |
|
86 |
|
87 // Note: The no categories queries below only retrieve five fields as opposed to * from the Music table. |
|
88 // This is an optimization that matches the DBMS version of the plugin and assumes that the client |
|
89 // only requests those fields. From the C++ code perspective there is no difference in processing |
|
90 // as the five fields in question reside at the beginning of the table. If the client is modified |
|
91 // to request more attributes then the queries below will have to be changed to return *. |
|
92 // As far as performance goes, the queries that only return five fields are a little faster than |
|
93 // the ones that return *, for example, for a * query that takes 2.1 seconds the equivalent |
|
94 // optimized query is faster by more than 100ms. |
|
95 |
|
96 // categories included |
|
97 _LIT(KQueryPodcastGetEpisode, "SELECT Podcast.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Podcast,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Podcast.Deleted=0 AND Podcast.UniqueId=%u AND Podcast.Album=Album.UniqueId AND Podcast.Artist=Artist.UniqueId AND Podcast.Genre=Genre.UniqueId AND Podcast.Composer=Composer.UniqueId"); |
|
98 |
|
99 // LTAN-79N8ND/EVXG-7FABHC: temporary fix. Symbian 9.4 SQLite cannot resolve column number correctly, |
|
100 // Use column number instead of column name for ORDER BY, needed when FROM uses more than 1 table |
|
101 // calculate column number from SELECT fields, first field is column 1 (not 0) |
|
102 /* |
|
103 _LIT(KQueryPodcastGetAllEpisodes, "SELECT Podcast.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Podcast,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Podcast.Deleted=0 AND Podcast.Album=Album.UniqueId AND Podcast.Artist=Artist.UniqueId AND Podcast.Genre=Genre.UniqueId AND Podcast.Composer=Composer.UniqueId ORDER BY Podcast.Title"); |
|
104 */ |
|
105 _LIT(KQueryPodcastGetAllEpisodes, "SELECT Podcast.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Podcast,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Podcast.Deleted=0 AND Podcast.Album=Album.UniqueId AND Podcast.Artist=Artist.UniqueId AND Podcast.Genre=Genre.UniqueId AND Podcast.Composer=Composer.UniqueId ORDER BY 6"); |
|
106 |
|
107 _LIT(KQueryPodcastGetEpisodesForAlbum, "SELECT Podcast.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Podcast,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Podcast.Deleted=0 AND Podcast.Album=%u AND Podcast.Album=Album.UniqueId AND Podcast.Artist=Artist.UniqueId AND Podcast.Genre=Genre.UniqueId AND Podcast.Composer=Composer.UniqueId ORDER BY PublishDate DESC,Title"); |
|
108 _LIT(KQueryPodcastNotPlayed, "SELECT Podcast.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Podcast,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Podcast.Deleted=0 AND Podcast.PlayCount=0 AND Podcast.LastPlayPosition=0 AND Podcast.IsPlaying=0 AND Podcast.Album=Album.UniqueId AND Podcast.Artist=Artist.UniqueId AND Podcast.Genre=Genre.UniqueId AND Podcast.Composer=Composer.UniqueId ORDER BY PublishDate DESC,Title"); |
|
109 _LIT(KQueryPodcastRecentlyAdded, "SELECT Podcast.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Podcast,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Podcast.Deleted=0 AND julianday(Podcast.TimeAdded)>=julianday('now','start of day','-7 days') AND Podcast.Album=Album.UniqueId AND Podcast.Artist=Artist.UniqueId AND Podcast.Genre=Genre.UniqueId AND Podcast.Composer=Composer.UniqueId ORDER BY TimeAdded DESC,Title"); |
|
110 |
|
111 // LTAN-79N8ND/EVXG-7FABHC: temporary fix. Symbian 9.4 SQLite cannot resolve column number correctly, |
|
112 // Use column number instead of column name for ORDER BY, needed when FROM uses more than 1 table |
|
113 // calculate column number from SELECT fields, first field is column 1 (not 0) |
|
114 /* |
|
115 _LIT(KQueryPodcastToday, "SELECT Podcast.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Podcast,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Podcast.Deleted=0 AND julianday(Podcast.PublishDate)>=julianday('%S','start of day') AND julianday(Podcast.PublishDate)<julianday('%S','start of day','+1 days') AND Podcast.Album=Album.UniqueId AND Podcast.Artist=Artist.UniqueId AND Podcast.Genre=Genre.UniqueId AND Podcast.Composer=Composer.UniqueId ORDER BY Podcast.PublishDate DESC,Podcast.Title"); |
|
116 _LIT(KQueryPodcastYesterday, "SELECT Podcast.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Podcast,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Podcast.Deleted=0 AND julianday(Podcast.PublishDate)>=julianday('%S','start of day','-1 days') AND julianday(Podcast.PublishDate)<julianday('%S','start of day') AND Podcast.Album=Album.UniqueId AND Podcast.Artist=Artist.UniqueId AND Podcast.Genre=Genre.UniqueId AND Podcast.Composer=Composer.UniqueId ORDER BY Podcast.PublishDate DESC,Podcast.Title"); |
|
117 _LIT(KQueryPodcastBetween, "SELECT Podcast.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Podcast,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Podcast.Deleted=0 AND julianday(Podcast.PublishDate)>julianday('%S','start of day','-%u days') AND julianday(Podcast.PublishDate)<=julianday('%S','start of day','-%u days') AND Podcast.Album=Album.UniqueId AND Podcast.Artist=Artist.UniqueId AND Podcast.Genre=Genre.UniqueId AND Podcast.Composer=Composer.UniqueId ORDER BY Podcast.PublishDate DESC,Podcast.Title"); |
|
118 _LIT(KQueryPodcastLastMonth, "SELECT Podcast.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Podcast,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Podcast.Deleted=0 AND julianday(Podcast.PublishDate)>=julianday('%S','start of month', '-1 month') AND julianday(Podcast.PublishDate)<julianday('%S','start of month') AND Podcast.Album=Album.UniqueId AND Podcast.Artist=Artist.UniqueId AND Podcast.Genre=Genre.UniqueId AND Podcast.Composer=Composer.UniqueId ORDER BY Podcast.PublishDate DESC,Podcast.Title"); |
|
119 _LIT(KQueryPodcastEarlier, "SELECT Podcast.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Podcast,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Podcast.Deleted=0 AND julianday(Podcast.PublishDate)<julianday('%S','start of month', '-1 month') AND Podcast.Album=Album.UniqueId AND Podcast.Artist=Artist.UniqueId AND Podcast.Genre=Genre.UniqueId AND Podcast.Composer=Composer.UniqueId ORDER BY Podcast.PublishDate DESC,Podcast.Title"); |
|
120 _LIT(KQueryPodcastUnknown, "SELECT Podcast.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Podcast,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Podcast.Deleted=0 AND (Podcast.PublishDate='' OR julianday(Podcast.PublishDate)>=julianday('%S','start of day','+1 days')) AND Podcast.Album=Album.UniqueId AND Podcast.Artist=Artist.UniqueId AND Podcast.Genre=Genre.UniqueId AND Podcast.Composer=Composer.UniqueId ORDER BY Podcast.Title"); |
|
121 */ |
|
122 _LIT(KQueryPodcastToday, "SELECT Podcast.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Podcast,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Podcast.Deleted=0 AND julianday(Podcast.PublishDate)>=julianday('%S','start of day') AND julianday(Podcast.PublishDate)<julianday('%S','start of day','+1 days') AND Podcast.Album=Album.UniqueId AND Podcast.Artist=Artist.UniqueId AND Podcast.Genre=Genre.UniqueId AND Podcast.Composer=Composer.UniqueId ORDER BY 8 DESC, 6"); |
|
123 _LIT(KQueryPodcastYesterday, "SELECT Podcast.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Podcast,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Podcast.Deleted=0 AND julianday(Podcast.PublishDate)>=julianday('%S','start of day','-1 days') AND julianday(Podcast.PublishDate)<julianday('%S','start of day') AND Podcast.Album=Album.UniqueId AND Podcast.Artist=Artist.UniqueId AND Podcast.Genre=Genre.UniqueId AND Podcast.Composer=Composer.UniqueId ORDER BY 8 DESC, 6"); |
|
124 _LIT(KQueryPodcastBetween, "SELECT Podcast.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Podcast,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Podcast.Deleted=0 AND julianday(Podcast.PublishDate)>julianday('%S','start of day','-%u days') AND julianday(Podcast.PublishDate)<=julianday('%S','start of day','-%u days') AND Podcast.Album=Album.UniqueId AND Podcast.Artist=Artist.UniqueId AND Podcast.Genre=Genre.UniqueId AND Podcast.Composer=Composer.UniqueId ORDER BY 8 DESC, 6"); |
|
125 _LIT(KQueryPodcastLastMonth, "SELECT Podcast.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Podcast,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Podcast.Deleted=0 AND julianday(Podcast.PublishDate)>=julianday('%S','start of month', '-1 month') AND julianday(Podcast.PublishDate)<julianday('%S','start of month') AND Podcast.Album=Album.UniqueId AND Podcast.Artist=Artist.UniqueId AND Podcast.Genre=Genre.UniqueId AND Podcast.Composer=Composer.UniqueId ORDER BY 8 DESC, 6"); |
|
126 _LIT(KQueryPodcastEarlier, "SELECT Podcast.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Podcast,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Podcast.Deleted=0 AND julianday(Podcast.PublishDate)<julianday('%S','start of month', '-1 month') AND Podcast.Album=Album.UniqueId AND Podcast.Artist=Artist.UniqueId AND Podcast.Genre=Genre.UniqueId AND Podcast.Composer=Composer.UniqueId ORDER BY 8 DESC, 6"); |
|
127 _LIT(KQueryPodcastUnknown, "SELECT Podcast.*,Artist.Name,Album.Name,Genre.Name,Composer.Name FROM :dbname.Podcast,:dbname.Artist,:dbname.Album,:dbname.Genre,:dbname.Composer WHERE Podcast.Deleted=0 AND (Podcast.PublishDate='' OR julianday(Podcast.PublishDate)>=julianday('%S','start of day','+1 days')) AND Podcast.Album=Album.UniqueId AND Podcast.Artist=Artist.UniqueId AND Podcast.Genre=Genre.UniqueId AND Podcast.Composer=Composer.UniqueId ORDER BY 6"); |
|
128 |
|
129 // no categories queries |
|
130 _LIT(KQueryPodcastGetEpisodeNoCat, "SELECT * FROM :dbname.Podcast WHERE Deleted=0 AND UniqueId=%u"); |
|
131 _LIT(KQueryPodcastGetAllEpisodesNoCat, "SELECT * FROM :dbname.Podcast WHERE Deleted=0 ORDER BY Title"); |
|
132 _LIT(KQueryPodcastGetEpisodesForAlbumNoCat, "SELECT * FROM :dbname.Podcast WHERE Deleted=0 AND Album=%u ORDER BY PublishDate DESC,Title"); |
|
133 _LIT(KQueryPodcastNotPlayedNoCat, "SELECT * FROM :dbname.Podcast WHERE Deleted=0 AND PlayCount=0 AND LastPlayPosition=0 AND IsPlaying=0 ORDER BY PublishDate DESC,Title"); |
|
134 _LIT(KQueryPodcastRecentlyAddedNoCat, "SELECT * FROM :dbname.Podcast WHERE Deleted=0 AND julianday(TimeAdded)>=julianday('now','start of day','-7 days') ORDER BY TimeAdded DESC,Title"); |
|
135 _LIT(KQueryPodcastTodayNoCat, "SELECT * FROM :dbname.Podcast WHERE Deleted=0 AND julianday(PublishDate)>=julianday('%S','start of day') AND julianday(PublishDate)<julianday('%S','start of day','+1 days') ORDER BY PublishDate DESC,Title"); |
|
136 _LIT(KQueryPodcastYesterdayNoCat, "SELECT * FROM :dbname.Podcast WHERE Deleted=0 AND julianday(PublishDate)>=julianday('%S','start of day','-1 days') AND julianday(PublishDate)<julianday('%S','start of day') ORDER BY PublishDate DESC,Title"); |
|
137 _LIT(KQueryPodcastBetweenNoCat, "SELECT * FROM :dbname.Podcast WHERE Deleted=0 AND julianday(PublishDate)>julianday('%S','start of day','-%u days') AND julianday(PublishDate)<=julianday('%S','start of day','-%u days') ORDER BY PublishDate DESC,Title"); |
|
138 _LIT(KQueryPodcastLastMonthNoCat, "SELECT * FROM :dbname.Podcast WHERE Deleted=0 AND julianday(PublishDate)>=julianday('%S','start of month', '-1 month') AND julianday(PublishDate)<julianday('%S','start of month') ORDER BY PublishDate DESC,Title"); |
|
139 _LIT(KQueryPodcastEarlierNoCat, "SELECT * FROM :dbname.Podcast WHERE Deleted=0 AND julianday(PublishDate)<julianday('%S','start of month', '-1 month') ORDER BY PublishDate DESC,Title"); |
|
140 _LIT(KQueryPodcastUnknownNoCat, "SELECT * FROM :dbname.Podcast WHERE Deleted=0 AND (PublishDate='' OR julianday(PublishDate)>=julianday('%S','start of day','+1 days')) ORDER BY Title"); |
|
141 |
|
142 // count queries |
|
143 _LIT(KQueryPodcastCount, "SELECT count(*) FROM :dbname.Podcast WHERE Deleted=0"); |
|
144 _LIT(KQueryPodcastTodayCount, "SELECT count(*) FROM :dbname.Podcast WHERE Deleted=0 AND julianday(PublishDate)>=julianday('%S','start of day') AND julianday(PublishDate)<julianday('%S','start of day','+1 days')"); |
|
145 _LIT(KQueryPodcastYesterdayCount, "SELECT count(*) FROM :dbname.Podcast WHERE Deleted=0 AND julianday(PublishDate)>=julianday('%S','start of day','-1 days') AND julianday(PublishDate)<julianday('%S','start of day')"); |
|
146 _LIT(KQueryPodcastBetweenCount, "SELECT count(*) FROM :dbname.Podcast WHERE Deleted=0 AND julianday(PublishDate)>julianday('%S','start of day','-%u days') AND julianday(PublishDate)<=julianday('%S','start of day','-%u days')"); |
|
147 _LIT(KQueryPodcastLastMonthCount, "SELECT count(*) FROM :dbname.Podcast WHERE Deleted=0 AND julianday(PublishDate)>=julianday('%S','start of month', '-1 month') AND julianday(PublishDate)<julianday('%S','start of month')"); |
|
148 _LIT(KQueryPodcastEarlierCount, "SELECT count(*) FROM :dbname.Podcast WHERE Deleted=0 AND julianday(PublishDate)<julianday('%S','start of month', '-1 month')"); |
|
149 _LIT(KQueryPodcastUnknownCount, "SELECT count(*) FROM :dbname.Podcast WHERE (PublishDate='' OR julianday(PublishDate)>=julianday('%S','start of day','+1 days')) AND Deleted=0"); |
|
150 _LIT(KQueryPodcastRecentlyAddedCount, "SELECT count(*) FROM :dbname.Podcast WHERE Deleted=0 AND julianday(TimeAdded)>=julianday('now','start of day','-7 days')"); |
|
151 _LIT(KQueryPodcastNotPlayedCount, "SELECT count(*) FROM :dbname.Podcast WHERE Deleted=0 AND PlayCount=0 AND LastPlayPosition=0 AND IsPlaying=0"); |
|
152 |
|
153 // duration queries |
|
154 _LIT(KQueryPodcastEpisodeDuration, "SELECT Duration FROM :dbname.Podcast WHERE Deleted=0 AND UniqueId=%u"); |
|
155 _LIT(KQueryPodcastDurationAll, "SELECT sum(Duration) FROM :dbname.Podcast WHERE Deleted=0"); |
|
156 _LIT(KQueryPodcastDurationAlbum, "SELECT sum(Duration) FROM :dbname.Podcast WHERE Deleted=0 AND Album=%u"); |
|
157 _LIT(KQueryPodcastNotPlayedDuration, "SELECT sum(Duration) FROM :dbname.Podcast WHERE Deleted=0 AND PlayCount=0"); |
|
158 _LIT(KQueryPodcastRecentlyAddedDuration, "SELECT Duration FROM :dbname.Podcast WHERE Deleted=0 AND julianday(TimeAdded)>julianday('now','-7 days')"); |
|
159 |
|
160 // == Category table queries ================================================== |
|
161 // |
|
162 _LIT(KCategoryTablePlaceholder, "##table##"); |
|
163 |
|
164 _LIT(KCategoryCreateTable,"CREATE TABLE ##table##(" |
|
165 L"UniqueId INTEGER PRIMARY KEY," |
|
166 L"Name TEXT COLLATE NOCASE," |
|
167 L"EpisodeCount INTEGER)"); |
|
168 |
|
169 _LIT(KCategoryDropTable,"DROP TABLE ##table##"); |
|
170 _LIT(KCategoryCheckTable,"SELECT UniqueId,Name,EpisodeCount FROM ##table##"); |
|
171 _LIT(KCategoryNameIndex,"CREATE INDEX Index##table##Name ON ##table##(Name)"); |
|
172 |
|
173 _LIT(KQueryCategoryInsert, "INSERT INTO :dbname.##table##(UniqueId,Name,EpisodeCount) VALUES(%u,'%S',%u)"); |
|
174 _LIT(KQueryCategoryDelete, "DELETE FROM :dbname.##table## WHERE UniqueId=%u"); |
|
175 |
|
176 _LIT(KQueryCategoryCount, "SELECT count(*) FROM :dbname.##table##"); |
|
177 _LIT(KQueryCategoryItem, "SELECT * FROM :dbname.##table## WHERE UniqueId=%u"); |
|
178 _LIT(KQueryCategoryItemByEpisodeId, "SELECT ##table##.* FROM :dbname.##table##,:dbname.Podcast WHERE Podcast.Deleted=0 AND Podcast.UniqueId=%u AND ##table##.UniqueId=Podcast.%S"); |
|
179 _LIT(KQueryCategoryAll, "SELECT * FROM :dbname.##table## ORDER BY Name"); |
|
180 _LIT(KQueryCategoryItems, "SELECT * FROM :dbname.##table## WHERE %S ORDER BY Name ASC"); |
|
181 |
|
182 _LIT(KQueryCategoryIncrementEpisodeCount, "UPDATE :dbname.##table## SET EpisodeCount=EpisodeCount+1 WHERE UniqueId=%u"); |
|
183 _LIT(KQueryCategoryDecrementEpisodeCount, "UPDATE :dbname.##table## SET EpisodeCount=EpisodeCount-1 WHERE UniqueId=%u"); |
|
184 _LIT(KQueryCategoryGetEpisodeCount, "SELECT EpisodeCount FROM :dbname.##table## WHERE UniqueId=%u"); |
|
185 |
|
186 // == Auxiliary table queries ================================================= |
|
187 // |
|
188 _LIT(KAuxiliaryCreateTable, "CREATE TABLE Auxiliary(" |
|
189 L"Id INTEGER," |
|
190 L"Version TEXT," |
|
191 L"TimeRefreshed TEXT," |
|
192 L"TimeSynced TEXT," |
|
193 L"Corrupt INTEGER DEFAULT 0," |
|
194 L"SaveDeletedRecordCount INTEGER DEFAULT 0)"); |
|
195 _LIT(KAuxiliaryDropTable,"DROP TABLE Auxiliary"); |
|
196 _LIT(KAuxiliaryCheckTable, "SELECT Id,Version,TimeRefreshed,TimeSynced,Corrupt,SaveDeletedRecordCount FROM AUXILIARY"); |
|
197 _LIT(KQueryAuxiliaryInsert, "INSERT INTO Auxiliary(Id,Version,Corrupt) VALUES(0,'6.0.0',%u)"); |
|
198 |
|
199 _LIT(KQueryAuxiliarySetTime, "UPDATE :dbname.Auxiliary SET TimeRefreshed='%S', Corrupt=0"); |
|
200 _LIT(KQueryAuxiliaryGetTime, "SELECT TimeRefreshed FROM :dbname.Auxiliary"); |
|
201 _LIT(KQueryAuxiliarySetCorrupt, "UPDATE :dbname.Auxiliary SET Corrupt=%u"); |
|
202 _LIT(KQueryAuxiliaryGetCorrupt, "SELECT Corrupt FROM :dbname.Auxiliary"); |
|
203 _LIT(KQueryAuxiliaryGetCount, "SELECT SaveDeletedRecordCount FROM :dbname.Auxiliary"); |
|
204 _LIT(KQueryAuxiliarySetCount, "UPDATE :dbname.Auxiliary SET SaveDeletedRecordCount=%u"); |
|
205 _LIT(KQueryAuxiliaryGetId, "SELECT Id FROM :dbname.Auxiliary"); |
|
206 _LIT(KQueryAuxiliarySetId, "UPDATE :dbname.Auxiliary SET Id=%u"); |
|
207 |
|
208 // Podcast table criteria |
|
209 _LIT(KCriterionPodcastNotDeleted, "Podcast.Deleted=0"); |
|
210 _LIT(KCriterionPodcastUniqueId, "Podcast.UniqueId=%u"); |
|
211 _LIT(KCriterionPodcastAuthor, "Podcast.artist=%u"); |
|
212 _LIT(KCriterionPodcastAlbum, "Podcast.album=%u"); |
|
213 _LIT(KCriterionPodcastGenre, "Podcast.genre=%u"); |
|
214 _LIT(KCriterionPodcastComposer, "Podcast.composer=%u"); |
|
215 // SQLite requires LIKE statement to include an ESCAPE clause if an escaped percentage |
|
216 // or underscore is to be used as regular search text instead of pattern matching |
|
217 // characters. See http://www.sqlite.org/lang_expr.html for an explanation. To supprt |
|
218 // partial title matching, our goal is to have the following statement as an example: |
|
219 // SELECT * FROM Podcast WHERE title LIKE '%xyz%' ESCAPE '\' |
|
220 // To achieve this, we specify the format to be %%%%%S%%%%. The 1st percentage is the |
|
221 // escape character for string formatter to output the 2nd percentage sign. Similiarly |
|
222 // for the 3rd, 4th, 6th, 7th, 8th, and 9th. The 5th percentage instructs string formatter |
|
223 // to substitute a string, i.e. %S. After subsititution, we will have, for example, %%xyz%%. |
|
224 // Because this substituted string will be substituted again into a WHERE clause, we need |
|
225 // to escape the percentage signs in order to get %xyz% as the end result. |
|
226 _LIT(KCriterionPodcastTitle, "Podcast.title LIKE '%%%%%S%%%%' ESCAPE '\\'"); |
|
227 _LIT(KCriterionPodcastVolume, "Podcast.VolumeId=%u"); |
|
228 _LIT(KCriterionPodcastUri, "Podcast.Location='%S'"); |
|
229 _LIT(KCriterionPodcastSynchronized, "Podcast.Sync=%u"); |
|
230 _LIT(KCriterionPodcastDeleted, "Podcast.Deleted=%u"); |
|
231 _LIT(KCriterionPodcastModified, "Podcast.Modified=%u"); |
|
232 _LIT(KCriterionPodcastPublishDateToday, "julianday(Podcast.PublishDate)>=julianday('%S','start of day') AND julianday(Podcast.PublishDate)<julianday('%S','start of day','+1 days')"); |
|
233 _LIT(KCriterionPodcastPublishDateYesterday, "julianday(Podcast.PublishDate)>=julianday('%S','start of day','-1 days') AND julianday(Podcast.PublishDate)<julianday('%S','start of day')"); |
|
234 _LIT(KCriterionPodcastPublishDateBetween, "julianday(Podcast.PublishDate)>julianday('%S','start of day','-%u days') AND julianday(Podcast.PublishDate)<=julianday('%S','start of day','-%u days')"); |
|
235 _LIT(KCriterionPodcastPublishDateLastMonth, "julianday(Podcast.PublishDate)>=julianday('%S','start of month', '-1 month') AND julianday(Podcast.PublishDate)<julianday('%S','start of month')"); |
|
236 _LIT(KCriterionPodcastPublishDateEarlier, "julianday(Podcast.PublishDate)<julianday('%S','start of month', '-1 month')"); |
|
237 _LIT(KCriterionPodcastPublishDateUnknown, "(Podcast.PublishDate='' OR julianday(Podcast.PublishDate)>=julianday('%S','start of day','+1 days'))"); |
|
238 |
|
239 // Category table criteria |
|
240 _LIT(KCriterionCategoryUniqueId, "##table##.UniqueId=%u"); |
|
241 _LIT(KCriterionCategoryName, "##table##.Name LIKE '%%%%%S%%%%' ESCAPE '\\'"); |
|
242 |
|
243 #endif // MPXPODCASTDBPLUGINQUERIES_H |
|
244 |