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