|
1 /* |
|
2 * Copyright (c) 2009 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 * Description: DDL(Data Definition Language) of Internet Radio Database |
|
13 * |
|
14 */ |
|
15 |
|
16 #ifndef IRSQLSTR_H_ |
|
17 #define IRSQLSTR_H_ |
|
18 |
|
19 #include <QString> |
|
20 |
|
21 const QString IRDBName = "IRDB.db"; |
|
22 |
|
23 #ifdef Q_CC_NOKIAX86 |
|
24 const QString IRDBFile = IRDBName; |
|
25 #else |
|
26 const QString IRDBFile = "c:\\private\\2002FFAC\\IRDB.db"; |
|
27 #endif |
|
28 |
|
29 |
|
30 |
|
31 const QString IRDBConnectionName("IR"); |
|
32 const QString IRDBSrhCIdCntFrmChannelInfo("select count(channelId) from channelInfo where channelId ="); |
|
33 const QString IRDBSrhCIdCntFrmFavorites("select count(channelId) from favorites where channelId ="); |
|
34 const QString IRDBSrhCIdFrmView("select channelid from IRVIEW_CHANNELINFO "); |
|
35 const QString IRDBSrhAllFrmView("select * from IRVIEW_CHANNELINFO "); |
|
36 const QString IRDBInsertIRBuff("insert into IRBuff (channelid, datasource, opt) values("); |
|
37 const QString IRDBSrhCIdCntFrmSongHistory("select count(channelId) from songHistory where songName ="); |
|
38 const QString IRDBSrhCIdCntFrmChannelHistory("select count(channelId) from channelHistory where channelId ="); |
|
39 const QString IRDBSrhCIdMaxFrmChannelInfo("select max(channelId) from channelInfo;"); |
|
40 const QString IRDBDltRowFrmUrlInfoByCId("delete from urlInfo where channelId ="); |
|
41 const QString IRDBSrhUserCid("select channelId from IR_VIEW_SRH_USERCID "); |
|
42 const QString IRDBSrhRecordCntFrmSongHistory("select count(songName) from songHistory "); |
|
43 /* |
|
44 * notes |
|
45 * About two columns in IRBuff table; |
|
46 * opt: |
|
47 -- opt = 1, insert |
|
48 -- opt = 2, update |
|
49 -- opt = 3, invoke the actions in update trigger; |
|
50 |
|
51 * dataSource: |
|
52 -- dataSource = 1, data for channelHistory |
|
53 -- dataSource = 2, data for searchRlt |
|
54 -- dataSource = other, data dont belong to both channelHistory and searchRlt , |
|
55 come from go to station view or synchronization with iSDS side; |
|
56 |
|
57 * trigger recursion |
|
58 -- until now the sqlite don't support recursive trigger, |
|
59 -- !!! if in future, the sqlite support the trigger recursion function, |
|
60 -- the trigger recursion function must be disabled. |
|
61 * |
|
62 */ |
|
63 |
|
64 |
|
65 /************************************************ |
|
66 * define for table IRBUFF and its triggers; |
|
67 ************************************************/ |
|
68 /* |
|
69 * Define for IRBuff table; |
|
70 * it is a temporary table for storage the row about IRDB; |
|
71 */ |
|
72 const QString CREATE_TABEL_IRBUFF("CREATE TABLE [IRBuff] ( \ |
|
73 [channelID] integer(4) NOT NULL UNIQUE, \ |
|
74 [channelName] nvarchar(255), \ |
|
75 [channelNickName] nvarchar(255), \ |
|
76 [genreName] nvarchar(255), \ |
|
77 [genreId] nvarchar(64), \ |
|
78 [languageName] nvarchar(255), \ |
|
79 [languageCode] nvarchar(8), \ |
|
80 [countryName] nvarchar(255), \ |
|
81 [countryCode] INTEGER(2), \ |
|
82 [description] nvarchar(255), \ |
|
83 [shortDesc] nvarchar(255), \ |
|
84 [lastModified] datetime DEFAULT (datetime(current_timestamp,'localtime')), \ |
|
85 [musicStoreStatus] numeric(1) DEFAULT (1), \ |
|
86 [imgUrl] nvarchar(255), \ |
|
87 [bIcon] BLOB, \ |
|
88 [sIcon] BLOB, \ |
|
89 [advertisementUrl] nvarchar(255), \ |
|
90 [advertisementInUse] nvarchar(255), \ |
|
91 [expiration] datetime DEFAULT (datetime(current_timestamp,'+10 day','localtime')), \ |
|
92 [dataSource] numeric(1) NOT NULL, \ |
|
93 [opt] NUMERIC(1) DEFAULT (1), \ |
|
94 PRIMARY KEY ([channelID]));"); |
|
95 |
|
96 |
|
97 /* |
|
98 * Define for IRBuff insert trigger; |
|
99 */ |
|
100 const QString TRI_INSERT_IRBUFF("CREATE TRIGGER [TRI_INSERT_IRBuff] \ |
|
101 AFTER INSERT ON [IRBuff] FOR EACH ROW \ |
|
102 BEGIN \ |
|
103 \ |
|
104 insert into channelInfo \ |
|
105 (channelID, channelName, channelNickName, genreName, \ |
|
106 genreId, languageName, languageCode, countryName, \ |
|
107 countryCode, description, shortDesc, lastModified, \ |
|
108 musicStoreStatus) \ |
|
109 select \ |
|
110 channelID, channelName, channelNickName, genreName, \ |
|
111 genreId, languageName, languageCode, countryName, \ |
|
112 countryCode, description, shortDesc, lastModified, \ |
|
113 musicStoreStatus \ |
|
114 from IRBuff \ |
|
115 where channelId = new.channelId \ |
|
116 AND new.opt =1;\ |
|
117 \ |
|
118 insert into channelHistory(channelID) \ |
|
119 select channelID from IRBuff \ |
|
120 where channelId = new.channelID \ |
|
121 AND new.dataSource = 1 \ |
|
122 AND new.opt = 1; \ |
|
123 \ |
|
124 insert into \ |
|
125 searchRlt(channelID) \ |
|
126 select channelID from IRBuff \ |
|
127 where channelId = new.channelID \ |
|
128 AND new.dataSource = 2 \ |
|
129 AND new.opt = 1; \ |
|
130 \ |
|
131 insert into \ |
|
132 img(channelID, imgUrl, bIcon, sIcon) \ |
|
133 select channelID, imgUrl, bIcon, sIcon from IRBuff \ |
|
134 where channelId = new.channelId \ |
|
135 AND new.opt =1;\ |
|
136 \ |
|
137 insert into \ |
|
138 advertisement(channelID, advertisementUrl, advertisementInUse, expiration) \ |
|
139 select channelID, advertisementUrl, advertisementInUse, expiration from IRBuff \ |
|
140 where channelId = new.channelId \ |
|
141 AND new.opt =1;\ |
|
142 \ |
|
143 \ |
|
144 \ |
|
145 update IRBuff \ |
|
146 set \ |
|
147 imgUrl = (select imgUrl from img where channelid = new.channelid), \ |
|
148 bIcon = (select bIcon from img where channelid = new.channelid), \ |
|
149 sIcon = (select sIcon from img where channelid = new.channelid), \ |
|
150 advertisementUrl = (select advertisementUrl from advertisement where channelid = new.channelid), \ |
|
151 advertisementInUse = (select advertisementInUse from advertisement where channelid = new.channelid), \ |
|
152 expiration = (select expiration from advertisement where channelid = new.channelid), \ |
|
153 channelName = (select channelName from channelinfo where channelid = new.channelid), \ |
|
154 channelNickName = (select channelNickName from channelinfo where channelid = new.channelid), \ |
|
155 genreName = (select genreName from channelinfo where channelid = new.channelid), \ |
|
156 genreId = (select genreId from channelinfo where channelid = new.channelid), \ |
|
157 languageName = (select languageName from channelinfo where channelid = new.channelid), \ |
|
158 languageCode = (select languageCode from channelinfo where channelid = new.channelid), \ |
|
159 countryName = (select countryName from channelinfo where channelid = new.channelid), \ |
|
160 countryCode = (select countryCode from channelinfo where channelid = new.channelid), \ |
|
161 description = (select description from channelinfo where channelid = new.channelid), \ |
|
162 shortDesc = (select shortDesc from channelinfo where channelid = new.channelid), \ |
|
163 musicStoreStatus = (select musicStoreStatus from channelinfo where channelid = new.channelid) \ |
|
164 where channelId = new.channelID \ |
|
165 AND opt = 2; \ |
|
166 \ |
|
167 \ |
|
168 END;"); |
|
169 |
|
170 |
|
171 /* |
|
172 * Define for IRBuff update trigger; |
|
173 */ |
|
174 const QString TRI_UPDATE_IRBUFF("CREATE TRIGGER [TRI_UPDATE_IRBUFF] \ |
|
175 AFTER UPDATE ON [IRBuff] FOR EACH ROW \ |
|
176 BEGIN \ |
|
177 \ |
|
178 update channelHistory \ |
|
179 set \ |
|
180 channelLatestPlayTime = datetime(current_timestamp,'localtime') \ |
|
181 where \ |
|
182 new.opt = 3 \ |
|
183 AND Channelid = new.channelid \ |
|
184 AND new.datasource = 1; \ |
|
185 \ |
|
186 update searchRlt \ |
|
187 set \ |
|
188 channelLatestSrhTime = datetime(current_timestamp,'localtime') \ |
|
189 where \ |
|
190 new.opt = 3 \ |
|
191 AND Channelid = new.channelid \ |
|
192 AND new.datasource = 2; \ |
|
193 \ |
|
194 \ |
|
195 insert into channelHistory(channelID) \ |
|
196 select channelID from IRBuff \ |
|
197 where \ |
|
198 new.opt = 3 \ |
|
199 AND channelId = new.channelID \ |
|
200 AND new.dataSource = 1 \ |
|
201 AND not exists \ |
|
202 (select channelID from channelHistory where channelID = new.channelID); \ |
|
203 \ |
|
204 insert into \ |
|
205 searchRlt(channelID) \ |
|
206 select channelID from IRBuff \ |
|
207 where \ |
|
208 new.opt = 3 \ |
|
209 AND channelId = new.channelID \ |
|
210 AND new.dataSource = 2 \ |
|
211 AND not exists \ |
|
212 (select channelID from searchRlt where channelID = new.channelID); \ |
|
213 \ |
|
214 \ |
|
215 UPDATE channelInfo \ |
|
216 SET \ |
|
217 channelName = (select channelName from IRBuff where channelId = new.channelId), \ |
|
218 channelNickName = (select channelNickName from IRBuff where channelId = new.channelId), \ |
|
219 genreName = (select genreName from IRBuff where channelId = new.channelId), \ |
|
220 genreId = (select genreId from IRBuff where channelId = new.channelId), \ |
|
221 languageName = (select languageName from IRBuff where channelId = new.channelId), \ |
|
222 languageCode = (select languageCode from IRBuff where channelId = new.channelId), \ |
|
223 countryName = (select countryName from IRBuff where channelId = new.channelId), \ |
|
224 countryCode = (select countryCode from IRBuff where channelId = new.channelId), \ |
|
225 description = (select description from IRBuff where channelId = new.channelId), \ |
|
226 shortDesc = (select shortDesc from IRBuff where channelId = new.channelId), \ |
|
227 lastModified = (select lastModified from IRBuff where channelId = new.channelId), \ |
|
228 musicStoreStatus = (select musicStoreStatus from IRBuff where channelId = new.channelId) \ |
|
229 where \ |
|
230 new.opt = 3 \ |
|
231 AND channelId = new.channelId; \ |
|
232 \ |
|
233 update img \ |
|
234 set \ |
|
235 imgUrl = (select imgUrl from IRBuff where channelId = new.channelId), \ |
|
236 bIcon = (select bIcon from IRBuff where channelId = new.channelId), \ |
|
237 sIcon = (select sIcon from IRBuff where channelId = new.channelId) \ |
|
238 where \ |
|
239 new.opt = 3 \ |
|
240 AND channelId = new.channelId; \ |
|
241 \ |
|
242 update advertisement \ |
|
243 set advertisementUrl = (select advertisementUrl from IRBuff where channelId = new.channelId), \ |
|
244 advertisementInUse = (select advertisementInUse from IRBuff where channelId = new.channelId), \ |
|
245 expiration = (select expiration from IRBuff where channelId = new.channelId) \ |
|
246 where \ |
|
247 new.opt = 3 \ |
|
248 AND channelId = new.channelId; \ |
|
249 \ |
|
250 END;"); |
|
251 |
|
252 //above trigger should add handle the case that the channelId exist in channelinfo but unexist in channelHistory. |
|
253 /* |
|
254 insert into channelHistory(channelID) \ |
|
255 select channelID from IRBuff \ |
|
256 where channelId = new.channelID \ |
|
257 AND new.dataSource = 1 \ |
|
258 AND new.opt = 3 \ |
|
259 AND not exists \ |
|
260 (select channelID from channelHistory where channelID = new.channelID); \ |
|
261 */ |
|
262 |
|
263 |
|
264 /************************************************** |
|
265 * define for table channelHistory and its triggers; |
|
266 ***************************************************/ |
|
267 /* |
|
268 * Define for channelHistory table; |
|
269 * |
|
270 * it stores the channels played by IR,the row counts is no more than 100; |
|
271 */ |
|
272 const QString CREATE_TABEL_CHANNELHISTORY("CREATE TABLE [channelHistory] ( \ |
|
273 [SID] integer NOT NULL PRIMARY KEY UNIQUE, \ |
|
274 [channelID] integer(4) NOT NULL UNIQUE, \ |
|
275 [channelLatestPlayTime] datetime DEFAULT (datetime(current_timestamp, 'localtime')));"); |
|
276 |
|
277 |
|
278 /* |
|
279 * Define for channelHistory insert trigger; |
|
280 * |
|
281 * if rows counts > 100, it will trigger delete action. |
|
282 * |
|
283 * if one row is added here, |
|
284 * the corresponsive row with the same channelId may be inserted to channelinfo. |
|
285 * |
|
286 * other: |
|
287 * from pragram logic judge, |
|
288 * the probability of insert a row into IRBuff |
|
289 * when the row's opt = 1 while the row's channelId is in channelInfo table |
|
290 * equals zero |
|
291 */ |
|
292 const QString TRI_INSERT_CHANNELHISTORY("CREATE TRIGGER [TRI_INSERT_channelHistory] \ |
|
293 AFTER INSERT ON [channelHistory] FOR EACH ROW \ |
|
294 BEGIN \ |
|
295 \ |
|
296 UPDATE channelInfo \ |
|
297 SET \ |
|
298 refCnt = refCnt+1 \ |
|
299 where channelId = new.channelId; \ |
|
300 \ |
|
301 delete from channelHistory \ |
|
302 where channelLatestPlayTime = ( select min(channelLatestPlayTime) from channelHistory) \ |
|
303 AND (select count(*) from channelHistory)> 100; \ |
|
304 \ |
|
305 END;"); |
|
306 |
|
307 |
|
308 /* |
|
309 * Define for channelHistory delete trigger; |
|
310 * |
|
311 * if one row is deleted, |
|
312 * the refcnt of corresponsive row, it has same channelId in channelInfo, |
|
313 * will be decreased. |
|
314 */ |
|
315 const QString TRI_DELETE_CHANNELHISTORY("CREATE TRIGGER [TRI_DELETE_channelHistory] \ |
|
316 AFTER DELETE ON [channelHistory] \ |
|
317 BEGIN \ |
|
318 UPDATE channelInfo \ |
|
319 SET refCnt = refCnt-1 \ |
|
320 where \ |
|
321 channelId = OLD.channelId; \ |
|
322 END;"); |
|
323 |
|
324 |
|
325 /************************************************** |
|
326 * define for table searchRlt and its triggers; |
|
327 ***************************************************/ |
|
328 /* |
|
329 * Definition for searchRlt table; |
|
330 * |
|
331 * it stores the channels searched by IR user,the row counts is no more than 100; |
|
332 */ |
|
333 const QString CREATE_TABEL_SEARCHRLT("CREATE TABLE [searchRlt] ( \ |
|
334 [SID] integer NOT NULL PRIMARY KEY UNIQUE, \ |
|
335 [channelID] integer(4) NOT NULL UNIQUE, \ |
|
336 [channelLatestSrhTime] DATETIME DEFAULT (datetime(current_timestamp, 'localtime')));"); |
|
337 |
|
338 |
|
339 /* |
|
340 * Define for searchRlt insert trigger; |
|
341 * |
|
342 * if rows counts > 100, it will trigger delete action. |
|
343 * |
|
344 * if one row is added here, |
|
345 * the corresponsive row with the same channelId may be inserted to channelinfo. |
|
346 * |
|
347 */ |
|
348 const QString TRI_INSERT_SEARCHRLT("CREATE TRIGGER [TRI_INSERT_searchRlt] \ |
|
349 AFTER INSERT ON [searchRlt] FOR EACH ROW \ |
|
350 BEGIN \ |
|
351 \ |
|
352 UPDATE channelInfo \ |
|
353 SET refCnt = refCnt+1 \ |
|
354 where \ |
|
355 channelId = new.channelId; \ |
|
356 \ |
|
357 delete from searchRlt \ |
|
358 where SID = ( select min(SID) from searchRlt) AND (select count(*) from searchRlt)> 100; \ |
|
359 \ |
|
360 END;"); |
|
361 |
|
362 |
|
363 /* |
|
364 * Define for searchRlt delete trigger; |
|
365 * |
|
366 * if one row is deleted, |
|
367 * the refcnt of corresponsive row, it has same channelId in channelInfo, |
|
368 * will be decreased. |
|
369 */ |
|
370 const QString TRI_DELETE_SEARCHRLT("CREATE TRIGGER [TRI_DELETE_searchRlt] \ |
|
371 AFTER DELETE ON [searchRlt] \ |
|
372 BEGIN \ |
|
373 UPDATE channelInfo \ |
|
374 SET refCnt = refCnt-1 \ |
|
375 where channelId = OLD.channelId; \ |
|
376 \ |
|
377 END;"); |
|
378 |
|
379 |
|
380 /************************************************** |
|
381 * define for table channelInfo and its triggers; |
|
382 ***************************************************/ |
|
383 /* |
|
384 * Definition for channelInfo table; |
|
385 * it stores the channels infomation; |
|
386 */ |
|
387 const QString CREATE_TABEL_CHANNELINFO("CREATE TABLE channelInfo( \ |
|
388 [channelID] integer(4) PRIMARY KEY UNIQUE NOT NULL ,\ |
|
389 [channelName] nvarchar(255) ,\ |
|
390 [channelNickName] nvarchar(255) ,\ |
|
391 [genreName] nvarchar(255) ,\ |
|
392 [genreId] nvarchar(64)), \ |
|
393 [languageName] nvarchar(255) ,\ |
|
394 [languageCode] nvarchar(8) ,\ |
|
395 [countryName] nvarchar(255) ,\ |
|
396 [countryCode] nvarchar(255) ,\ |
|
397 [description] nvarchar(255) ,\ |
|
398 [shortDesc] nvarchar(255) ,\ |
|
399 [lastModified] datetime DEFAULT (datetime(current_timestamp,'localtime')), \ |
|
400 [channelType] numeric(1) DEFAULT (1) ,\ |
|
401 [musicStoreStatus] numeric(1) NOT NULL ,\ |
|
402 [refCnt] numeric(1) DEFAULT (0));"); |
|
403 |
|
404 |
|
405 /* |
|
406 * Define for channelInfo Insert trigger; |
|
407 * the refCnt default value is 1; |
|
408 */ |
|
409 const QString TRI_INSERT_CHANNELINFO("CREATE TRIGGER [TRI_INSERT_channelInfo] \ |
|
410 AFTER INSERT ON [channelInfo] FOR EACH ROW \ |
|
411 BEGIN \ |
|
412 \ |
|
413 UPDATE channelInfo \ |
|
414 SET channelType = 0 \ |
|
415 WHERE channelId = new.channelID \ |
|
416 AND new.channelID > 4294967295; \ |
|
417 \ |
|
418 END;"); |
|
419 |
|
420 |
|
421 /* |
|
422 * Define for channelInfo update trigger; |
|
423 * if refCnt default is 0 after update, it will trigger delete action, |
|
424 * all infomation about this row in IRDB will be removed. |
|
425 */ |
|
426 const QString TRI_UPDATE_CHANNELINFO("CREATE TRIGGER [TRI_UPDATE_channelInfo] \ |
|
427 AFTER UPDATE ON [channelInfo] FOR EACH ROW \ |
|
428 BEGIN \ |
|
429 delete from channelInfo \ |
|
430 where \ |
|
431 channelId = new.channelID \ |
|
432 AND refCnt = 0; \ |
|
433 END;"); |
|
434 |
|
435 |
|
436 /* |
|
437 * Definition for channelInfo delete trigger; |
|
438 * the refCnt default value is 1; |
|
439 */ |
|
440 const QString TRI_DELETE_CHANNELINFO("CREATE TRIGGER [TRI_DELETE_channelInfo] \ |
|
441 AFTER DELETE ON [channelInfo] FOR EACH ROW \ |
|
442 BEGIN \ |
|
443 delete from advertisement where channelID = old.channelID; \ |
|
444 delete from img where channelID = old.channelID; \ |
|
445 delete from urlInfo where channelID = old.channelID; \ |
|
446 END;"); |
|
447 |
|
448 |
|
449 |
|
450 /************************************************** |
|
451 * define for table img and its triggers; |
|
452 ***************************************************/ |
|
453 /* |
|
454 * Definition for img table; |
|
455 * it stores the channels logo img infomation; |
|
456 */ |
|
457 const QString CREATE_TABEL_IMG("CREATE TABLE [img] ( \ |
|
458 [SID] integer NOT NULL PRIMARY KEY UNIQUE, \ |
|
459 [channelID] integer(4) NOT NULL, \ |
|
460 [imgUrl] nvarchar(255) DEFAULT ('unavailable'), \ |
|
461 [bIcon] BLOB, \ |
|
462 [sIcon] BLOB); "); |
|
463 |
|
464 |
|
465 |
|
466 /************************************************** |
|
467 * define for table urlInfo and its triggers; |
|
468 ***************************************************/ |
|
469 /* |
|
470 * Definition for urlInfo table; |
|
471 * it stores the channels url information; |
|
472 */ |
|
473 const QString CREATE_TABEL_URLINFO("CREATE TABLE [urlInfo] ( \ |
|
474 [SID] integer NOT NULL PRIMARY KEY UNIQUE, \ |
|
475 [channelUrl] nvarchar(255) DEFAULT ('unavailable'), \ |
|
476 [channelID] integer(4) NOT NULL, \ |
|
477 [bitRate] integer(4));"); |
|
478 |
|
479 |
|
480 /************************************************** |
|
481 * define for table advertisement and its triggers; |
|
482 ***************************************************/ |
|
483 /* |
|
484 * Definition for advertisement table; |
|
485 * it stores the advertisement information; |
|
486 */ |
|
487 const QString CREATE_TABEL_ADVERTISEMENT("CREATE TABLE [advertisement] ( \ |
|
488 [SID] integer NOT NULL PRIMARY KEY UNIQUE, \ |
|
489 [channelID] integer(4) NOT NULL, \ |
|
490 [advertisementUrl] nvarchar(255), \ |
|
491 [advertisementInUse] nvarchar(255), \ |
|
492 [expiration] datetime);"); |
|
493 |
|
494 |
|
495 /* |
|
496 * Definition for advertisement update trigger; |
|
497 * if there is row in IRbuff, it will be deleted, |
|
498 * here is last defender for remove rows in IRbuff; |
|
499 */ |
|
500 const QString TRI_INSERT_ADVERTISEMENT("CREATE TRIGGER [TRI_INSERT_advertisement] \ |
|
501 AFTER INSERT ON [advertisement] FOR EACH ROW \ |
|
502 BEGIN \ |
|
503 DELETE FROM IRBuff; \ |
|
504 END;"); |
|
505 |
|
506 |
|
507 /* |
|
508 * Definition for advertisement update trigger; |
|
509 * update channelid in advertisement is forbidden; |
|
510 * |
|
511 * if there is row in IRbuff, it will be deleted, |
|
512 * here is last defender for remove rows in IRbuff; |
|
513 * |
|
514 */ |
|
515 const QString TRI_UPDATE_ADVERTISEMENT("CREATE TRIGGER [TRI_UPDATE_advertisement] \ |
|
516 AFTER UPDATE ON advertisement FOR EACH ROW \ |
|
517 BEGIN \ |
|
518 update advertisement set channelid = old.channelid where channelid = new.channelid; \ |
|
519 DELETE FROM IRBuff; \ |
|
520 END;"); |
|
521 |
|
522 |
|
523 |
|
524 /************************************************** |
|
525 * define for table songHistory and its triggers; |
|
526 ***************************************************/ |
|
527 /* |
|
528 * Definition for songHistory table; |
|
529 * it stores the song played information; |
|
530 * the rows in this table are no more than 100; |
|
531 */ |
|
532 const QString CREATE_TABEL_SONGHISTORY("CREATE TABLE [songHistory]( \ |
|
533 [SID] integer PRIMARY KEY UNIQUE NOT NULL ,\ |
|
534 [songName] nvarchar(64) NOT NULL ,\ |
|
535 [artistName] nvarchar(64) ,\ |
|
536 [channelID] integer(4) NOT NULL ,\ |
|
537 [songPlaySeq] LARGEINT DEFAULT (0), \ |
|
538 [songLatestPlayTime] DATETIME DEFAULT (datetime(current_timestamp,'localtime')) );"); |
|
539 |
|
540 |
|
541 /* |
|
542 * Definition for songHistory insert trigger; |
|
543 * |
|
544 * if the rows are more than 100, the oldest row will be removed; |
|
545 * after a row insert, the channel's refCnt in channelInfo will be increase; |
|
546 */ |
|
547 const QString TRI_INSERT_SONGHISTORY("CREATE TRIGGER [TRI_INSERT_songHistory] \ |
|
548 AFTER INSERT ON [songHistory] FOR EACH ROW \ |
|
549 BEGIN \ |
|
550 UPDATE channelInfo \ |
|
551 SET refCnt = refCnt+1 \ |
|
552 where channelId = new.channelId; \ |
|
553 \ |
|
554 update songHistory \ |
|
555 SET \ |
|
556 songName = new.songName, \ |
|
557 channelId = new.channelId, \ |
|
558 artistName = new.artistName \ |
|
559 where \ |
|
560 songName = new.songName \ |
|
561 AND channelId = new.channelId \ |
|
562 AND artistName = new.artistName; \ |
|
563 \ |
|
564 delete from songHistory \ |
|
565 where songPlaySeq = ( select min(songPlaySeq) from searchRlt) \ |
|
566 AND (select count(*) from songHistory)> 100; \ |
|
567 END;"); |
|
568 |
|
569 |
|
570 /* |
|
571 * Definition for songHistory delete trigger; |
|
572 * |
|
573 * after row is removed, the channel's refCnt in channelInfo will be decreased; |
|
574 */ |
|
575 const QString TRI_DELETE_SONGHISTORY("CREATE TRIGGER [TRI_DELETE_songHistory] \ |
|
576 AFTER DELETE ON [songHistory] FOR EACH ROW \ |
|
577 BEGIN \ |
|
578 UPDATE channelInfo \ |
|
579 SET refCnt = refCnt-1 \ |
|
580 where channelId = OLD.channelId; \ |
|
581 END;"); |
|
582 |
|
583 |
|
584 /* |
|
585 * Definition for songHistory update trigger; |
|
586 */ |
|
587 const QString TRI_UPDATE_SONGHISTORY("CREATE TRIGGER [TRI_UPDATE_songHistory] \ |
|
588 AFTER UPDATE ON songHistory FOR EACH ROW \ |
|
589 BEGIN \ |
|
590 \ |
|
591 update songHistory \ |
|
592 set songPlaySeq = (select max(songPlaySeq) from songHistory) + 1 \ |
|
593 where songName = new.songName \ |
|
594 AND channelId = new.channelId \ |
|
595 AND artistName = new.artistName; \ |
|
596 \ |
|
597 END"); |
|
598 |
|
599 |
|
600 /************************************************** |
|
601 * define for table favorites and its triggers; |
|
602 ***************************************************/ |
|
603 /* |
|
604 * Definition for favorites table; |
|
605 * it stores the information of song user's favorites; |
|
606 * the rows in this table are no more than 100; |
|
607 */ |
|
608 const QString CREATE_TABEL_FAVORITES("CREATE TABLE [favorites]( \ |
|
609 [SID] integer PRIMARY KEY UNIQUE NOT NULL ,\ |
|
610 [ChannelID] integer(4) UNIQUE NOT NULL ,\ |
|
611 [PlayCount] integer(4) DEFAULT (1),\ |
|
612 [FavSeq] LARGEINT DEFAULT (0) );"); |
|
613 |
|
614 |
|
615 /* |
|
616 * Definition for favorites insert trigger; |
|
617 * |
|
618 * if the rows are more than 100, the oldest row will be removed; |
|
619 * after a row insert, the channel's refCnt in channelInfo will be increase; |
|
620 */ |
|
621 const QString TRI_INSERT_FAVORITES("CREATE TRIGGER TRI_INSERT_favorites \ |
|
622 AFTER INSERT On favorites \ |
|
623 BEGIN \ |
|
624 \ |
|
625 update favorites \ |
|
626 set channelId = new.channelid \ |
|
627 where channelid = new.channelid; \ |
|
628 \ |
|
629 UPDATE channelInfo \ |
|
630 SET refCnt = refCnt+1 \ |
|
631 where channelId = new.channelId; \ |
|
632 \ |
|
633 delete from favorites \ |
|
634 where \ |
|
635 FavSeq = ( select min(FavSeq) from favorites ) \ |
|
636 AND (select count(*) from favorites)> 100; \ |
|
637 \ |
|
638 END;"); |
|
639 |
|
640 |
|
641 /* |
|
642 * Definition for songHistory update trigger; |
|
643 */ |
|
644 const QString TRI_UPDATE_FAVORITES("CREATE TRIGGER [TRI_UPDATE_favorites] \ |
|
645 AFTER UPDATE ON favorites FOR EACH ROW \ |
|
646 BEGIN \ |
|
647 update favorites \ |
|
648 set \ |
|
649 PlayCount = PlayCount+1, \ |
|
650 FavSeq = (select max(FavSeq) from favorites) +1 \ |
|
651 where channelid = old.channelID; \ |
|
652 \ |
|
653 update favorites \ |
|
654 set channelid = old.channelid \ |
|
655 where channelid = new.channelid; \ |
|
656 END;"); |
|
657 |
|
658 |
|
659 /* |
|
660 * Definition for favorites delete trigger; |
|
661 * |
|
662 * after row is removed, the channel's refCnt in channelInfo will be decreased; |
|
663 */ |
|
664 const QString TRI_DELETE_FAVORITES("CREATE TRIGGER TRI_DELETE_favorites \ |
|
665 AFTER DELETE On favorites \ |
|
666 BEGIN \ |
|
667 UPDATE channelInfo \ |
|
668 SET refCnt = refCnt-1 \ |
|
669 where \ |
|
670 channelId = OLD.channelId; \ |
|
671 END;"); |
|
672 |
|
673 |
|
674 |
|
675 /************************************************** |
|
676 * define for IRDB VIEW |
|
677 * channelId is the join key; |
|
678 ***************************************************/ |
|
679 const QString IRVIEW_CHANNELINFO1(" Create View [IRVIEW_CHANNELINFO1] As \ |
|
680 select \ |
|
681 channelInfo.[channelID], \ |
|
682 channelInfo.[channelName], channelInfo.[channelNickName], channelInfo.[genreName], \ |
|
683 channelInfo.[genreId], channelInfo.[languageName], channelInfo.[languageCode], \ |
|
684 channelInfo.[countryName], channelInfo.[countryCode], channelInfo.[description], \ |
|
685 channelInfo.[shortDesc], channelInfo.[lastModified], channelInfo.[channelType], \ |
|
686 channelInfo.[musicStoreStatus], \ |
|
687 img.[imgUrl], img.[bIcon], img.[sIcon] \ |
|
688 from \ |
|
689 channelInfo LEFT JOIN img ON channelInfo.channelID = img.channelID "); |
|
690 |
|
691 |
|
692 |
|
693 const QString IRVIEW_CHANNELINFO(" Create View [IRView_channelinfo] As \ |
|
694 select \ |
|
695 IRView_channelinfo1.*, \ |
|
696 advertisement.[advertisementUrl], advertisement.[advertisementInUse],advertisement.[expiration] \ |
|
697 from \ |
|
698 IRView_channelinfo1 LEFT JOIN advertisement ON IRView_channelinfo1.[channelId] = advertisement.[channelID] "); |
|
699 |
|
700 |
|
701 const QString IRVIEW_CHANNELHISTORY(" Create View [IRVIEW_channelHistory] As \ |
|
702 select \ |
|
703 IRView_channelinfo.* \ |
|
704 from \ |
|
705 channelHistory LEFT JOIN IRView_channelinfo ON channelHistory.[channelId] = IRView_channelinfo.[channelID] "); |
|
706 |
|
707 const QString IRVIEW_FAVORITES(" Create View [IRVIEW_favorites] As \ |
|
708 select \ |
|
709 IRView_channelinfo.*, favorites.[FavSeq] \ |
|
710 from \ |
|
711 favorites LEFT JOIN IRView_channelinfo ON favorites.[channelId] = IRView_channelinfo.[channelID] "); |
|
712 |
|
713 |
|
714 const QString IRVIEW_SEARCHRLT(" Create View [IRVIEW_searchRlt] As \ |
|
715 select \ |
|
716 IRView_channelinfo.* \ |
|
717 from \ |
|
718 searchRlt LEFT JOIN IRView_channelinfo ON searchRlt.[channelId] = IRView_channelinfo.[channelID] "); |
|
719 |
|
720 const QString IRVIEW_SONGHISTORY("Create View [IRVIEW_songHistory] As \ |
|
721 select \ |
|
722 IRView_channelinfo.*, songHistory.[songName], songHistory.[artistName], songHistory.[songPlaySeq] \ |
|
723 from \ |
|
724 songHistory LEFT JOIN IRView_channelinfo ON songHistory.[channelId] = IRView_channelinfo.[channelID] "); |
|
725 |
|
726 |
|
727 const QString IR_VIEW_SRH_USERCID(" CREATE VIEW [IR_VIEW_SRH_USERCID] As \ |
|
728 select channelinfo.[channelID], channelNickName, channelUrl, bitRate \ |
|
729 FROM channelinfo LEFT JOIN urlInfo ON channelinfo.channelID = urlInfo.channelID "); |
|
730 |
|
731 |
|
732 /************************************************** |
|
733 * define for drop IRDB VIEW, TABLE AND TRIGGER |
|
734 ***************************************************/ |
|
735 //drop view; |
|
736 const QString IR_VIEW1_DROP("Drop View If Exists MAIN.[IR_VIEW1];"); |
|
737 const QString IR_VIEW2_DROP("Drop View If Exists MAIN.[IR_VIEW2];"); |
|
738 const QString IR_VIEW3_DROP("Drop View If Exists MAIN.[IR_VIEW3];"); |
|
739 const QString IR_VIEW4_DROP("Drop View If Exists MAIN.[IR_VIEW4];"); |
|
740 const QString IR_VIEW5_DROP("Drop View If Exists MAIN.[IR_VIEW5];"); |
|
741 const QString IR_VIEW6_DROP("Drop View If Exists MAIN.[IR_VIEW6];"); |
|
742 const QString IR_VIEW_ALL_DROP("Drop View If Exists MAIN.[IR_VIEW_ALL];"); |
|
743 |
|
744 //drop table; |
|
745 const QString IR_IRBUFF_DROP("Drop table if exists main.[IRBUFF];"); |
|
746 const QString IR_CHANNELHISTORY_DROP("Drop table if exists main.[channelHistory];"); |
|
747 const QString IR_SEARCHRLT_DROP("Drop table if exists main.[searchRlt];"); |
|
748 const QString IR_CHANNELINFO_DROP("Drop table if exists main.[channelInfo];"); |
|
749 const QString IR_IMG_DROP("Drop table if exists main.[img];"); |
|
750 const QString IR_URLINFO_DROP("Drop table if exists main.[urlInfo];"); |
|
751 const QString IR_ADVERTISEMENT_DROP("Drop table if exists main.[advertisement];"); |
|
752 const QString IR_SONGHISTORY_DROP("Drop table if exists main.[songHistory];"); |
|
753 const QString IR_FAVORITES_DROP("Drop table if exists main.[favorites];"); |
|
754 |
|
755 |
|
756 |
|
757 #endif /* IRSQLSTR_H_ */ |
|
758 |
|
759 |
|
760 |
|
761 |
|
762 |
|
763 |
|
764 |
|
765 |
|
766 |
|
767 |
|
768 |
|
769 |
|
770 |
|
771 |
|
772 |
|
773 |
|
774 |
|
775 |
|
776 |
|
777 |
|
778 |
|
779 |
|
780 |
|
781 |
|
782 |
|
783 |
|
784 |
|
785 |
|
786 |
|
787 |
|
788 |
|
789 |