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