18 |
18 |
19 #include <QString> |
19 #include <QString> |
20 |
20 |
21 const QString IRDBName = "IRDB.db"; |
21 const QString IRDBName = "IRDB.db"; |
22 |
22 |
23 #ifdef Q_CC_NOKIAX86 |
23 #ifdef PLATSIM_DEBUG_CONF |
24 const QString IRDBFile = IRDBName; |
24 const QString IRDBFile = IRDBName; |
25 #else |
25 #else |
26 const QString IRDBFile = "c:\\private\\2002FFAC\\IRDB.db"; |
26 const QString IRDBFile = "c:\\private\\2002FFAC\\IRDB.db"; |
27 #endif |
27 #endif |
28 |
28 |
29 |
29 |
30 |
30 |
31 const QString IRDBConnectionName("IR"); |
31 const QString IRDBConnectionName("IR"); |
32 const QString IRDBSrhCIdCntFrmChannelInfo("select count(channelId) from channelInfo where channelId ="); |
32 const QString IRDBSrhCIdCntFrmChannelInfo("select count(channelId) from channelInfo where channelId ="); |
33 const QString IRDBSrhCIdCntFrmFavorites("select count(channelId) from favorites where channelId ="); |
33 const QString IRDBSrhCIdCntFrmFavorites("select count(channelId) from favorites where channelId ="); |
34 const QString IRDBSrhCIdFrmView("select channelid from IRVIEW_CHANNELINFO "); |
34 const QString IRDBSrhCIdFrmChannelInfo("select channelid from IRVIEW_CHANNELINFO "); |
35 const QString IRDBSrhAllFrmView("select * from IRVIEW_CHANNELINFO "); |
35 const QString IRDBSrhCIdFrmView("select channelId from IRVIEW_ALL "); |
|
36 const QString IRDBSrhAllFrmView("select * from IRVIEW_ALL "); |
36 const QString IRDBInsertIRBuff("insert into IRBuff (channelid, datasource, opt) values("); |
37 const QString IRDBInsertIRBuff("insert into IRBuff (channelid, datasource, opt) values("); |
37 const QString IRDBSrhCIdCntFrmSongHistory("select count(channelId) from songHistory where songName ="); |
38 const QString IRDBSrhCIdCntFrmSongHistory("select count(channelId) from songHistory where songName ="); |
38 const QString IRDBSrhCIdCntFrmChannelHistory("select count(channelId) from channelHistory where channelId ="); |
39 const QString IRDBSrhCIdCntFrmChannelHistory("select count(channelId) from channelHistory where channelId ="); |
39 const QString IRDBSrhCIdMaxFrmChannelInfo("select max(channelId) from channelInfo;"); |
40 const QString IRDBSrhCIdMaxFrmChannelInfo("select max(channelId) from channelInfo;"); |
40 const QString IRDBDltRowFrmUrlInfoByCId("delete from urlInfo where channelId ="); |
41 const QString IRDBDltRowFrmUrlInfoByCId("delete from urlInfo where channelId ="); |
80 [countryName] nvarchar(255), \ |
81 [countryName] nvarchar(255), \ |
81 [countryCode] INTEGER(2), \ |
82 [countryCode] INTEGER(2), \ |
82 [description] nvarchar(255), \ |
83 [description] nvarchar(255), \ |
83 [shortDesc] nvarchar(255), \ |
84 [shortDesc] nvarchar(255), \ |
84 [lastModified] datetime DEFAULT (datetime(current_timestamp,'localtime')), \ |
85 [lastModified] datetime DEFAULT (datetime(current_timestamp,'localtime')), \ |
85 [musicStoreStatus] numeric(1) DEFAULT (1), \ |
86 [musicStoreStatus] nvarchar(6), \ |
86 [imgUrl] nvarchar(255), \ |
87 [imgUrl] nvarchar(255), \ |
87 [bIcon] BLOB, \ |
88 [bIcon] BLOB, \ |
88 [sIcon] BLOB, \ |
89 [sIcon] BLOB, \ |
89 [advertisementUrl] nvarchar(255), \ |
90 [advertisementUrl] nvarchar(255), \ |
90 [advertisementInUse] nvarchar(255), \ |
91 [advertisementInUse] nvarchar(255), \ |
387 const QString CREATE_TABEL_CHANNELINFO("CREATE TABLE channelInfo( \ |
398 const QString CREATE_TABEL_CHANNELINFO("CREATE TABLE channelInfo( \ |
388 [channelID] integer(4) PRIMARY KEY UNIQUE NOT NULL ,\ |
399 [channelID] integer(4) PRIMARY KEY UNIQUE NOT NULL ,\ |
389 [channelName] nvarchar(255) ,\ |
400 [channelName] nvarchar(255) ,\ |
390 [channelNickName] nvarchar(255) ,\ |
401 [channelNickName] nvarchar(255) ,\ |
391 [genreName] nvarchar(255) ,\ |
402 [genreName] nvarchar(255) ,\ |
392 [genreId] nvarchar(64)), \ |
403 [genreId] nvarchar(64),\ |
393 [languageName] nvarchar(255) ,\ |
404 [languageName] nvarchar(255) ,\ |
394 [languageCode] nvarchar(8) ,\ |
405 [languageCode] nvarchar(8) ,\ |
395 [countryName] nvarchar(255) ,\ |
406 [countryName] nvarchar(255) ,\ |
396 [countryCode] nvarchar(255) ,\ |
407 [countryCode] nvarchar(255) ,\ |
397 [description] nvarchar(255) ,\ |
408 [description] nvarchar(255) ,\ |
398 [shortDesc] nvarchar(255) ,\ |
409 [shortDesc] nvarchar(255) ,\ |
399 [lastModified] datetime DEFAULT (datetime(current_timestamp,'localtime')), \ |
410 [lastModified] datetime DEFAULT (datetime(current_timestamp,'localtime')), \ |
400 [channelType] numeric(1) DEFAULT (1) ,\ |
411 [channelType] numeric(1) DEFAULT (1) ,\ |
401 [musicStoreStatus] numeric(1) NOT NULL ,\ |
412 [musicStoreStatus] nvarchar(6) ,\ |
402 [refCnt] numeric(1) DEFAULT (0));"); |
413 [refCnt] numeric(1) DEFAULT (0));"); |
403 |
414 |
404 |
415 |
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 |
416 |
420 |
417 |
421 /* |
418 /* |
422 * Define for channelInfo update trigger; |
419 * Define for channelInfo update trigger; |
423 * if refCnt default is 0 after update, it will trigger delete action, |
420 * if refCnt default is 0 after update, it will trigger delete action, |
459 [channelID] integer(4) NOT NULL, \ |
456 [channelID] integer(4) NOT NULL, \ |
460 [imgUrl] nvarchar(255) DEFAULT ('unavailable'), \ |
457 [imgUrl] nvarchar(255) DEFAULT ('unavailable'), \ |
461 [bIcon] BLOB, \ |
458 [bIcon] BLOB, \ |
462 [sIcon] BLOB); "); |
459 [sIcon] BLOB); "); |
463 |
460 |
|
461 /* |
|
462 * Definition for trigger img table; |
|
463 * when imgurl is updated(old.value isn't same with new.value), |
|
464 * the logo info in database will be remvoed; |
|
465 */ |
|
466 const QString TRI_UPDATE_IMG("CREATE TRIGGER [IRI_UPDATE_img] \ |
|
467 AFTER UPDATE \ |
|
468 ON [img] \ |
|
469 FOR EACH ROW \ |
|
470 BEGIN \ |
|
471 update img \ |
|
472 set sIcon = NULL, bIcon = NULL \ |
|
473 where new.imgurl <> old.imgurl; \ |
|
474 \ |
|
475 END; "); |
464 |
476 |
465 |
477 |
466 /************************************************** |
478 /************************************************** |
467 * define for table urlInfo and its triggers; |
479 * define for table urlInfo and its triggers; |
468 ***************************************************/ |
480 ***************************************************/ |
560 songName = new.songName \ |
572 songName = new.songName \ |
561 AND channelId = new.channelId \ |
573 AND channelId = new.channelId \ |
562 AND artistName = new.artistName; \ |
574 AND artistName = new.artistName; \ |
563 \ |
575 \ |
564 delete from songHistory \ |
576 delete from songHistory \ |
565 where songPlaySeq = ( select min(songPlaySeq) from searchRlt) \ |
577 where songPlaySeq = ( select min(songPlaySeq) from songHistory) \ |
566 AND (select count(*) from songHistory)> 100; \ |
578 AND (select count(*) from songHistory)> 100; \ |
567 END;"); |
579 END;"); |
568 |
580 |
569 |
581 |
570 /* |
582 /* |
700 |
712 |
701 const QString IRVIEW_CHANNELHISTORY(" Create View [IRVIEW_channelHistory] As \ |
713 const QString IRVIEW_CHANNELHISTORY(" Create View [IRVIEW_channelHistory] As \ |
702 select \ |
714 select \ |
703 IRView_channelinfo.* \ |
715 IRView_channelinfo.* \ |
704 from \ |
716 from \ |
705 channelHistory LEFT JOIN IRView_channelinfo ON channelHistory.[channelId] = IRView_channelinfo.[channelID] "); |
717 channelHistory \ |
|
718 LEFT JOIN \ |
|
719 IRView_channelinfo ON channelHistory.[channelId] = IRView_channelinfo.[channelID] \ |
|
720 order by channelHistory.[channelLatestPlayTime] desc "); |
706 |
721 |
707 const QString IRVIEW_FAVORITES(" Create View [IRVIEW_favorites] As \ |
722 const QString IRVIEW_FAVORITES(" Create View [IRVIEW_favorites] As \ |
708 select \ |
723 select \ |
709 IRView_channelinfo.*, favorites.[FavSeq] \ |
724 IRView_channelinfo.* \ |
710 from \ |
725 from \ |
711 favorites LEFT JOIN IRView_channelinfo ON favorites.[channelId] = IRView_channelinfo.[channelID] "); |
726 favorites LEFT JOIN IRView_channelinfo ON favorites.[channelId] = IRView_channelinfo.[channelID] \ |
|
727 order by favorites.[FavSeq] desc "); |
712 |
728 |
713 |
729 |
714 const QString IRVIEW_SEARCHRLT(" Create View [IRVIEW_searchRlt] As \ |
730 const QString IRVIEW_SEARCHRLT(" Create View [IRVIEW_searchRlt] As \ |
715 select \ |
731 select \ |
716 IRView_channelinfo.* \ |
732 IRView_channelinfo.* \ |
717 from \ |
733 from \ |
718 searchRlt LEFT JOIN IRView_channelinfo ON searchRlt.[channelId] = IRView_channelinfo.[channelID] "); |
734 searchRlt LEFT JOIN IRView_channelinfo ON searchRlt.[channelId] = IRView_channelinfo.[channelID] \ |
|
735 order by searchRlt.[channelLatestSrhTime] asc "); |
719 |
736 |
720 const QString IRVIEW_SONGHISTORY("Create View [IRVIEW_songHistory] As \ |
737 const QString IRVIEW_SONGHISTORY("Create View [IRVIEW_songHistory] As \ |
721 select \ |
738 select \ |
722 IRView_channelinfo.*, songHistory.[songName], songHistory.[artistName], songHistory.[songPlaySeq] \ |
739 IRView_channelinfo.*, songHistory.[songName], songHistory.[artistName] \ |
723 from \ |
740 from \ |
724 songHistory LEFT JOIN IRView_channelinfo ON songHistory.[channelId] = IRView_channelinfo.[channelID] "); |
741 songHistory LEFT JOIN IRView_channelinfo ON songHistory.[channelId] = IRView_channelinfo.[channelID] \ |
|
742 order by songHistory.[songPlaySeq] desc "); |
725 |
743 |
726 |
744 |
727 const QString IR_VIEW_SRH_USERCID(" CREATE VIEW [IR_VIEW_SRH_USERCID] As \ |
745 const QString IR_VIEW_SRH_USERCID(" CREATE VIEW [IR_VIEW_SRH_USERCID] As \ |
728 select channelinfo.[channelID], channelNickName, channelUrl, bitRate \ |
746 select channelinfo.[channelID], channelNickName, channelUrl, bitRate \ |
729 FROM channelinfo LEFT JOIN urlInfo ON channelinfo.channelID = urlInfo.channelID "); |
747 FROM channelinfo LEFT JOIN urlInfo ON channelinfo.channelID = urlInfo.channelID "); |
730 |
748 |
|
749 |
|
750 const QString IR_VIEW_ALL("Create View [IRVIEW_ALL] As \ |
|
751 select \ |
|
752 IRView_channelinfo.*,urlInfo.[channelUrl], urlInfo.[bitRate] \ |
|
753 from \ |
|
754 IRView_channelInfo \ |
|
755 LEFT JOIN \ |
|
756 urlinfo ON IRView_channelinfo.[channelID] = urlInfo.[channelID] "); |
731 |
757 |
732 /************************************************** |
758 /************************************************** |
733 * define for drop IRDB VIEW, TABLE AND TRIGGER |
759 * define for drop IRDB VIEW, TABLE AND TRIGGER |
734 ***************************************************/ |
760 ***************************************************/ |
735 //drop view; |
761 //drop view; |