diff -r 065198191975 -r 5723da102db1 qtinternetradio/irdb/inc/irsqlstr.h --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/qtinternetradio/irdb/inc/irsqlstr.h Fri Sep 17 08:27:59 2010 +0300 @@ -0,0 +1,789 @@ +/* +* Copyright (c) 2009 Nokia Corporation and/or its subsidiary(-ies). +* All rights reserved. +* This component and the accompanying materials are made available +* under the terms of "Eclipse Public License v1.0" +* which accompanies this distribution, and is available +* at the URL "http://www.eclipse.org/legal/epl-v10.html". +* +* Initial Contributors: +* Nokia Corporation - initial contribution. +* +* Description: DDL(Data Definition Language) of Internet Radio Database +* +*/ + +#ifndef IRSQLSTR_H_ +#define IRSQLSTR_H_ + +#include + +const QString IRDBName = "IRDB.db"; + +#ifdef Q_CC_NOKIAX86 + const QString IRDBFile = IRDBName; +#else + const QString IRDBFile = "c:\\private\\2002FFAC\\IRDB.db"; +#endif + + + +const QString IRDBConnectionName("IR"); +const QString IRDBSrhCIdCntFrmChannelInfo("select count(channelId) from channelInfo where channelId ="); +const QString IRDBSrhCIdCntFrmFavorites("select count(channelId) from favorites where channelId ="); +const QString IRDBSrhCIdFrmView("select channelid from IRVIEW_CHANNELINFO "); +const QString IRDBSrhAllFrmView("select * from IRVIEW_CHANNELINFO "); +const QString IRDBInsertIRBuff("insert into IRBuff (channelid, datasource, opt) values("); +const QString IRDBSrhCIdCntFrmSongHistory("select count(channelId) from songHistory where songName ="); +const QString IRDBSrhCIdCntFrmChannelHistory("select count(channelId) from channelHistory where channelId ="); +const QString IRDBSrhCIdMaxFrmChannelInfo("select max(channelId) from channelInfo;"); +const QString IRDBDltRowFrmUrlInfoByCId("delete from urlInfo where channelId ="); +const QString IRDBSrhUserCid("select channelId from IR_VIEW_SRH_USERCID "); +const QString IRDBSrhRecordCntFrmSongHistory("select count(songName) from songHistory "); +/* + * notes + * About two columns in IRBuff table; + * opt: + -- opt = 1, insert + -- opt = 2, update + -- opt = 3, invoke the actions in update trigger; + + * dataSource: + -- dataSource = 1, data for channelHistory + -- dataSource = 2, data for searchRlt + -- dataSource = other, data dont belong to both channelHistory and searchRlt , + come from go to station view or synchronization with iSDS side; + + * trigger recursion + -- until now the sqlite don't support recursive trigger, + -- !!! if in future, the sqlite support the trigger recursion function, + -- the trigger recursion function must be disabled. + * +*/ + + +/************************************************ +* define for table IRBUFF and its triggers; +************************************************/ +/* +* Define for IRBuff table; +* it is a temporary table for storage the row about IRDB; +*/ +const QString CREATE_TABEL_IRBUFF("CREATE TABLE [IRBuff] ( \ +[channelID] integer(4) NOT NULL UNIQUE, \ +[channelName] nvarchar(255), \ +[channelNickName] nvarchar(255), \ +[genreName] nvarchar(255), \ +[genreId] nvarchar(64), \ +[languageName] nvarchar(255), \ +[languageCode] nvarchar(8), \ +[countryName] nvarchar(255), \ +[countryCode] INTEGER(2), \ +[description] nvarchar(255), \ +[shortDesc] nvarchar(255), \ +[lastModified] datetime DEFAULT (datetime(current_timestamp,'localtime')), \ +[musicStoreStatus] numeric(1) DEFAULT (1), \ +[imgUrl] nvarchar(255), \ +[bIcon] BLOB, \ +[sIcon] BLOB, \ +[advertisementUrl] nvarchar(255), \ +[advertisementInUse] nvarchar(255), \ +[expiration] datetime DEFAULT (datetime(current_timestamp,'+10 day','localtime')), \ +[dataSource] numeric(1) NOT NULL, \ +[opt] NUMERIC(1) DEFAULT (1), \ +PRIMARY KEY ([channelID]));"); + + +/* +* Define for IRBuff insert trigger; +*/ +const QString TRI_INSERT_IRBUFF("CREATE TRIGGER [TRI_INSERT_IRBuff] \ +AFTER INSERT ON [IRBuff] FOR EACH ROW \ +BEGIN \ + \ +insert into channelInfo \ +(channelID, channelName, channelNickName, genreName, \ + genreId, languageName, languageCode, countryName, \ + countryCode, description, shortDesc, lastModified, \ + musicStoreStatus) \ +select \ + channelID, channelName, channelNickName, genreName, \ + genreId, languageName, languageCode, countryName, \ + countryCode, description, shortDesc, lastModified, \ + musicStoreStatus \ +from IRBuff \ +where channelId = new.channelId \ +AND new.opt =1;\ +\ +insert into channelHistory(channelID) \ +select channelID from IRBuff \ +where channelId = new.channelID \ +AND new.dataSource = 1 \ +AND new.opt = 1; \ +\ +insert into \ +searchRlt(channelID) \ +select channelID from IRBuff \ +where channelId = new.channelID \ +AND new.dataSource = 2 \ +AND new.opt = 1; \ +\ +insert into \ +img(channelID, imgUrl, bIcon, sIcon) \ +select channelID, imgUrl, bIcon, sIcon from IRBuff \ +where channelId = new.channelId \ +AND new.opt =1;\ +\ +insert into \ +advertisement(channelID, advertisementUrl, advertisementInUse, expiration) \ +select channelID, advertisementUrl, advertisementInUse, expiration from IRBuff \ +where channelId = new.channelId \ +AND new.opt =1;\ +\ +\ +\ +update IRBuff \ +set \ +imgUrl = (select imgUrl from img where channelid = new.channelid), \ +bIcon = (select bIcon from img where channelid = new.channelid), \ +sIcon = (select sIcon from img where channelid = new.channelid), \ +advertisementUrl = (select advertisementUrl from advertisement where channelid = new.channelid), \ +advertisementInUse = (select advertisementInUse from advertisement where channelid = new.channelid), \ +expiration = (select expiration from advertisement where channelid = new.channelid), \ +channelName = (select channelName from channelinfo where channelid = new.channelid), \ +channelNickName = (select channelNickName from channelinfo where channelid = new.channelid), \ +genreName = (select genreName from channelinfo where channelid = new.channelid), \ +genreId = (select genreId from channelinfo where channelid = new.channelid), \ +languageName = (select languageName from channelinfo where channelid = new.channelid), \ +languageCode = (select languageCode from channelinfo where channelid = new.channelid), \ +countryName = (select countryName from channelinfo where channelid = new.channelid), \ +countryCode = (select countryCode from channelinfo where channelid = new.channelid), \ +description = (select description from channelinfo where channelid = new.channelid), \ +shortDesc = (select shortDesc from channelinfo where channelid = new.channelid), \ +musicStoreStatus = (select musicStoreStatus from channelinfo where channelid = new.channelid) \ +where channelId = new.channelID \ +AND opt = 2; \ + \ +\ +END;"); + + +/* +* Define for IRBuff update trigger; +*/ +const QString TRI_UPDATE_IRBUFF("CREATE TRIGGER [TRI_UPDATE_IRBUFF] \ +AFTER UPDATE ON [IRBuff] FOR EACH ROW \ +BEGIN \ +\ +update channelHistory \ +set \ +channelLatestPlayTime = datetime(current_timestamp,'localtime') \ +where \ +new.opt = 3 \ +AND Channelid = new.channelid \ +AND new.datasource = 1; \ +\ +update searchRlt \ +set \ +channelLatestSrhTime = datetime(current_timestamp,'localtime') \ +where \ +new.opt = 3 \ +AND Channelid = new.channelid \ +AND new.datasource = 2; \ +\ +\ +insert into channelHistory(channelID) \ +select channelID from IRBuff \ +where \ +new.opt = 3 \ +AND channelId = new.channelID \ +AND new.dataSource = 1 \ +AND not exists \ +(select channelID from channelHistory where channelID = new.channelID); \ +\ +insert into \ +searchRlt(channelID) \ +select channelID from IRBuff \ +where \ +new.opt = 3 \ +AND channelId = new.channelID \ +AND new.dataSource = 2 \ +AND not exists \ +(select channelID from searchRlt where channelID = new.channelID); \ +\ +\ +UPDATE channelInfo \ +SET \ +channelName = (select channelName from IRBuff where channelId = new.channelId), \ +channelNickName = (select channelNickName from IRBuff where channelId = new.channelId), \ +genreName = (select genreName from IRBuff where channelId = new.channelId), \ +genreId = (select genreId from IRBuff where channelId = new.channelId), \ +languageName = (select languageName from IRBuff where channelId = new.channelId), \ +languageCode = (select languageCode from IRBuff where channelId = new.channelId), \ +countryName = (select countryName from IRBuff where channelId = new.channelId), \ +countryCode = (select countryCode from IRBuff where channelId = new.channelId), \ +description = (select description from IRBuff where channelId = new.channelId), \ +shortDesc = (select shortDesc from IRBuff where channelId = new.channelId), \ +lastModified = (select lastModified from IRBuff where channelId = new.channelId), \ +musicStoreStatus = (select musicStoreStatus from IRBuff where channelId = new.channelId) \ +where \ +new.opt = 3 \ +AND channelId = new.channelId; \ +\ +update img \ +set \ +imgUrl = (select imgUrl from IRBuff where channelId = new.channelId), \ +bIcon = (select bIcon from IRBuff where channelId = new.channelId), \ +sIcon = (select sIcon from IRBuff where channelId = new.channelId) \ +where \ +new.opt = 3 \ +AND channelId = new.channelId; \ +\ +update advertisement \ +set advertisementUrl = (select advertisementUrl from IRBuff where channelId = new.channelId), \ +advertisementInUse = (select advertisementInUse from IRBuff where channelId = new.channelId), \ +expiration = (select expiration from IRBuff where channelId = new.channelId) \ +where \ +new.opt = 3 \ +AND channelId = new.channelId; \ +\ +END;"); + +//above trigger should add handle the case that the channelId exist in channelinfo but unexist in channelHistory. +/* +insert into channelHistory(channelID) \ +select channelID from IRBuff \ +where channelId = new.channelID \ +AND new.dataSource = 1 \ +AND new.opt = 3 \ +AND not exists \ +(select channelID from channelHistory where channelID = new.channelID); \ +*/ + + +/************************************************** +* define for table channelHistory and its triggers; +***************************************************/ +/* +* Define for channelHistory table; +* +* it stores the channels played by IR,the row counts is no more than 100; +*/ +const QString CREATE_TABEL_CHANNELHISTORY("CREATE TABLE [channelHistory] ( \ +[SID] integer NOT NULL PRIMARY KEY UNIQUE, \ +[channelID] integer(4) NOT NULL UNIQUE, \ +[channelLatestPlayTime] datetime DEFAULT (datetime(current_timestamp, 'localtime')));"); + + +/* +* Define for channelHistory insert trigger; +* +* if rows counts > 100, it will trigger delete action. +* +* if one row is added here, +* the corresponsive row with the same channelId may be inserted to channelinfo. +* +* other: +* from pragram logic judge, +* the probability of insert a row into IRBuff +* when the row's opt = 1 while the row's channelId is in channelInfo table +* equals zero +*/ +const QString TRI_INSERT_CHANNELHISTORY("CREATE TRIGGER [TRI_INSERT_channelHistory] \ +AFTER INSERT ON [channelHistory] FOR EACH ROW \ +BEGIN \ +\ +UPDATE channelInfo \ +SET \ +refCnt = refCnt+1 \ +where channelId = new.channelId; \ +\ +delete from channelHistory \ +where channelLatestPlayTime = ( select min(channelLatestPlayTime) from channelHistory) \ +AND (select count(*) from channelHistory)> 100; \ +\ +END;"); + + +/* +* Define for channelHistory delete trigger; +* +* if one row is deleted, +* the refcnt of corresponsive row, it has same channelId in channelInfo, +* will be decreased. +*/ +const QString TRI_DELETE_CHANNELHISTORY("CREATE TRIGGER [TRI_DELETE_channelHistory] \ +AFTER DELETE ON [channelHistory] \ +BEGIN \ +UPDATE channelInfo \ +SET refCnt = refCnt-1 \ +where \ +channelId = OLD.channelId; \ +END;"); + + +/************************************************** +* define for table searchRlt and its triggers; +***************************************************/ +/* +* Definition for searchRlt table; +* +* it stores the channels searched by IR user,the row counts is no more than 100; +*/ +const QString CREATE_TABEL_SEARCHRLT("CREATE TABLE [searchRlt] ( \ +[SID] integer NOT NULL PRIMARY KEY UNIQUE, \ +[channelID] integer(4) NOT NULL UNIQUE, \ +[channelLatestSrhTime] DATETIME DEFAULT (datetime(current_timestamp, 'localtime')));"); + + +/* +* Define for searchRlt insert trigger; +* +* if rows counts > 100, it will trigger delete action. +* +* if one row is added here, +* the corresponsive row with the same channelId may be inserted to channelinfo. +* +*/ +const QString TRI_INSERT_SEARCHRLT("CREATE TRIGGER [TRI_INSERT_searchRlt] \ +AFTER INSERT ON [searchRlt] FOR EACH ROW \ +BEGIN \ +\ +UPDATE channelInfo \ +SET refCnt = refCnt+1 \ +where \ +channelId = new.channelId; \ + \ +delete from searchRlt \ +where SID = ( select min(SID) from searchRlt) AND (select count(*) from searchRlt)> 100; \ +\ +END;"); + + +/* +* Define for searchRlt delete trigger; +* +* if one row is deleted, +* the refcnt of corresponsive row, it has same channelId in channelInfo, +* will be decreased. +*/ +const QString TRI_DELETE_SEARCHRLT("CREATE TRIGGER [TRI_DELETE_searchRlt] \ +AFTER DELETE ON [searchRlt] \ +BEGIN \ +UPDATE channelInfo \ +SET refCnt = refCnt-1 \ +where channelId = OLD.channelId; \ +\ +END;"); + + +/************************************************** +* define for table channelInfo and its triggers; +***************************************************/ +/* +* Definition for channelInfo table; +* it stores the channels infomation; +*/ +const QString CREATE_TABEL_CHANNELINFO("CREATE TABLE channelInfo( \ +[channelID] integer(4) PRIMARY KEY UNIQUE NOT NULL ,\ +[channelName] nvarchar(255) ,\ +[channelNickName] nvarchar(255) ,\ +[genreName] nvarchar(255) ,\ +[genreId] nvarchar(64)), \ +[languageName] nvarchar(255) ,\ +[languageCode] nvarchar(8) ,\ +[countryName] nvarchar(255) ,\ +[countryCode] nvarchar(255) ,\ +[description] nvarchar(255) ,\ +[shortDesc] nvarchar(255) ,\ +[lastModified] datetime DEFAULT (datetime(current_timestamp,'localtime')), \ +[channelType] numeric(1) DEFAULT (1) ,\ +[musicStoreStatus] numeric(1) NOT NULL ,\ +[refCnt] numeric(1) DEFAULT (0));"); + + +/* +* Define for channelInfo Insert trigger; +* the refCnt default value is 1; +*/ +const QString TRI_INSERT_CHANNELINFO("CREATE TRIGGER [TRI_INSERT_channelInfo] \ +AFTER INSERT ON [channelInfo] FOR EACH ROW \ +BEGIN \ +\ +UPDATE channelInfo \ +SET channelType = 0 \ +WHERE channelId = new.channelID \ +AND new.channelID > 4294967295; \ +\ +END;"); + + +/* +* Define for channelInfo update trigger; +* if refCnt default is 0 after update, it will trigger delete action, +* all infomation about this row in IRDB will be removed. +*/ +const QString TRI_UPDATE_CHANNELINFO("CREATE TRIGGER [TRI_UPDATE_channelInfo] \ +AFTER UPDATE ON [channelInfo] FOR EACH ROW \ +BEGIN \ +delete from channelInfo \ +where \ +channelId = new.channelID \ +AND refCnt = 0; \ +END;"); + + +/* +* Definition for channelInfo delete trigger; +* the refCnt default value is 1; +*/ +const QString TRI_DELETE_CHANNELINFO("CREATE TRIGGER [TRI_DELETE_channelInfo] \ +AFTER DELETE ON [channelInfo] FOR EACH ROW \ +BEGIN \ +delete from advertisement where channelID = old.channelID; \ +delete from img where channelID = old.channelID; \ +delete from urlInfo where channelID = old.channelID; \ +END;"); + + + +/************************************************** +* define for table img and its triggers; +***************************************************/ +/* +* Definition for img table; +* it stores the channels logo img infomation; +*/ +const QString CREATE_TABEL_IMG("CREATE TABLE [img] ( \ +[SID] integer NOT NULL PRIMARY KEY UNIQUE, \ +[channelID] integer(4) NOT NULL, \ +[imgUrl] nvarchar(255) DEFAULT ('unavailable'), \ +[bIcon] BLOB, \ +[sIcon] BLOB); "); + + + +/************************************************** +* define for table urlInfo and its triggers; +***************************************************/ +/* +* Definition for urlInfo table; +* it stores the channels url information; +*/ +const QString CREATE_TABEL_URLINFO("CREATE TABLE [urlInfo] ( \ +[SID] integer NOT NULL PRIMARY KEY UNIQUE, \ +[channelUrl] nvarchar(255) DEFAULT ('unavailable'), \ +[channelID] integer(4) NOT NULL, \ +[bitRate] integer(4));"); + + +/************************************************** +* define for table advertisement and its triggers; +***************************************************/ +/* +* Definition for advertisement table; +* it stores the advertisement information; +*/ +const QString CREATE_TABEL_ADVERTISEMENT("CREATE TABLE [advertisement] ( \ +[SID] integer NOT NULL PRIMARY KEY UNIQUE, \ +[channelID] integer(4) NOT NULL, \ +[advertisementUrl] nvarchar(255), \ +[advertisementInUse] nvarchar(255), \ +[expiration] datetime);"); + + +/* +* Definition for advertisement update trigger; +* if there is row in IRbuff, it will be deleted, +* here is last defender for remove rows in IRbuff; +*/ +const QString TRI_INSERT_ADVERTISEMENT("CREATE TRIGGER [TRI_INSERT_advertisement] \ +AFTER INSERT ON [advertisement] FOR EACH ROW \ +BEGIN \ +DELETE FROM IRBuff; \ +END;"); + + +/* +* Definition for advertisement update trigger; +* update channelid in advertisement is forbidden; +* +* if there is row in IRbuff, it will be deleted, +* here is last defender for remove rows in IRbuff; +* +*/ +const QString TRI_UPDATE_ADVERTISEMENT("CREATE TRIGGER [TRI_UPDATE_advertisement] \ +AFTER UPDATE ON advertisement FOR EACH ROW \ +BEGIN \ +update advertisement set channelid = old.channelid where channelid = new.channelid; \ +DELETE FROM IRBuff; \ +END;"); + + + +/************************************************** +* define for table songHistory and its triggers; +***************************************************/ +/* +* Definition for songHistory table; +* it stores the song played information; +* the rows in this table are no more than 100; +*/ +const QString CREATE_TABEL_SONGHISTORY("CREATE TABLE [songHistory]( \ +[SID] integer PRIMARY KEY UNIQUE NOT NULL ,\ +[songName] nvarchar(64) NOT NULL ,\ +[artistName] nvarchar(64) ,\ +[channelID] integer(4) NOT NULL ,\ +[songPlaySeq] LARGEINT DEFAULT (0), \ +[songLatestPlayTime] DATETIME DEFAULT (datetime(current_timestamp,'localtime')) );"); + + +/* +* Definition for songHistory insert trigger; +* +* if the rows are more than 100, the oldest row will be removed; +* after a row insert, the channel's refCnt in channelInfo will be increase; +*/ +const QString TRI_INSERT_SONGHISTORY("CREATE TRIGGER [TRI_INSERT_songHistory] \ +AFTER INSERT ON [songHistory] FOR EACH ROW \ +BEGIN \ +UPDATE channelInfo \ +SET refCnt = refCnt+1 \ +where channelId = new.channelId; \ + \ +update songHistory \ +SET \ +songName = new.songName, \ +channelId = new.channelId, \ +artistName = new.artistName \ +where \ +songName = new.songName \ +AND channelId = new.channelId \ +AND artistName = new.artistName; \ + \ +delete from songHistory \ +where songPlaySeq = ( select min(songPlaySeq) from searchRlt) \ +AND (select count(*) from songHistory)> 100; \ +END;"); + + +/* +* Definition for songHistory delete trigger; +* +* after row is removed, the channel's refCnt in channelInfo will be decreased; +*/ +const QString TRI_DELETE_SONGHISTORY("CREATE TRIGGER [TRI_DELETE_songHistory] \ +AFTER DELETE ON [songHistory] FOR EACH ROW \ +BEGIN \ +UPDATE channelInfo \ +SET refCnt = refCnt-1 \ +where channelId = OLD.channelId; \ +END;"); + + +/* +* Definition for songHistory update trigger; +*/ +const QString TRI_UPDATE_SONGHISTORY("CREATE TRIGGER [TRI_UPDATE_songHistory] \ +AFTER UPDATE ON songHistory FOR EACH ROW \ +BEGIN \ + \ +update songHistory \ +set songPlaySeq = (select max(songPlaySeq) from songHistory) + 1 \ +where songName = new.songName \ +AND channelId = new.channelId \ +AND artistName = new.artistName; \ + \ +END"); + + +/************************************************** +* define for table favorites and its triggers; +***************************************************/ +/* +* Definition for favorites table; +* it stores the information of song user's favorites; +* the rows in this table are no more than 100; +*/ +const QString CREATE_TABEL_FAVORITES("CREATE TABLE [favorites]( \ +[SID] integer PRIMARY KEY UNIQUE NOT NULL ,\ +[ChannelID] integer(4) UNIQUE NOT NULL ,\ +[PlayCount] integer(4) DEFAULT (1),\ +[FavSeq] LARGEINT DEFAULT (0) );"); + + +/* +* Definition for favorites insert trigger; +* +* if the rows are more than 100, the oldest row will be removed; +* after a row insert, the channel's refCnt in channelInfo will be increase; +*/ +const QString TRI_INSERT_FAVORITES("CREATE TRIGGER TRI_INSERT_favorites \ +AFTER INSERT On favorites \ +BEGIN \ +\ +update favorites \ +set channelId = new.channelid \ +where channelid = new.channelid; \ +\ +UPDATE channelInfo \ +SET refCnt = refCnt+1 \ +where channelId = new.channelId; \ + \ +delete from favorites \ +where \ +FavSeq = ( select min(FavSeq) from favorites ) \ +AND (select count(*) from favorites)> 100; \ + \ +END;"); + + +/* +* Definition for songHistory update trigger; +*/ +const QString TRI_UPDATE_FAVORITES("CREATE TRIGGER [TRI_UPDATE_favorites] \ +AFTER UPDATE ON favorites FOR EACH ROW \ +BEGIN \ +update favorites \ +set \ +PlayCount = PlayCount+1, \ +FavSeq = (select max(FavSeq) from favorites) +1 \ +where channelid = old.channelID; \ + \ +update favorites \ +set channelid = old.channelid \ +where channelid = new.channelid; \ +END;"); + + +/* +* Definition for favorites delete trigger; +* +* after row is removed, the channel's refCnt in channelInfo will be decreased; +*/ +const QString TRI_DELETE_FAVORITES("CREATE TRIGGER TRI_DELETE_favorites \ +AFTER DELETE On favorites \ +BEGIN \ +UPDATE channelInfo \ +SET refCnt = refCnt-1 \ +where \ +channelId = OLD.channelId; \ +END;"); + + + +/************************************************** +* define for IRDB VIEW +* channelId is the join key; +***************************************************/ +const QString IRVIEW_CHANNELINFO1(" Create View [IRVIEW_CHANNELINFO1] As \ +select \ +channelInfo.[channelID], \ +channelInfo.[channelName], channelInfo.[channelNickName], channelInfo.[genreName], \ +channelInfo.[genreId], channelInfo.[languageName], channelInfo.[languageCode], \ +channelInfo.[countryName], channelInfo.[countryCode], channelInfo.[description], \ +channelInfo.[shortDesc], channelInfo.[lastModified], channelInfo.[channelType], \ +channelInfo.[musicStoreStatus], \ +img.[imgUrl], img.[bIcon], img.[sIcon] \ +from \ + channelInfo LEFT JOIN img ON channelInfo.channelID = img.channelID "); + + + +const QString IRVIEW_CHANNELINFO(" Create View [IRView_channelinfo] As \ +select \ +IRView_channelinfo1.*, \ +advertisement.[advertisementUrl], advertisement.[advertisementInUse],advertisement.[expiration] \ +from \ +IRView_channelinfo1 LEFT JOIN advertisement ON IRView_channelinfo1.[channelId] = advertisement.[channelID] "); + + +const QString IRVIEW_CHANNELHISTORY(" Create View [IRVIEW_channelHistory] As \ +select \ +IRView_channelinfo.* \ +from \ +channelHistory LEFT JOIN IRView_channelinfo ON channelHistory.[channelId] = IRView_channelinfo.[channelID] "); + +const QString IRVIEW_FAVORITES(" Create View [IRVIEW_favorites] As \ +select \ +IRView_channelinfo.*, favorites.[FavSeq] \ +from \ +favorites LEFT JOIN IRView_channelinfo ON favorites.[channelId] = IRView_channelinfo.[channelID] "); + + +const QString IRVIEW_SEARCHRLT(" Create View [IRVIEW_searchRlt] As \ +select \ +IRView_channelinfo.* \ +from \ +searchRlt LEFT JOIN IRView_channelinfo ON searchRlt.[channelId] = IRView_channelinfo.[channelID] "); + +const QString IRVIEW_SONGHISTORY("Create View [IRVIEW_songHistory] As \ +select \ +IRView_channelinfo.*, songHistory.[songName], songHistory.[artistName], songHistory.[songPlaySeq] \ +from \ +songHistory LEFT JOIN IRView_channelinfo ON songHistory.[channelId] = IRView_channelinfo.[channelID] "); + + +const QString IR_VIEW_SRH_USERCID(" CREATE VIEW [IR_VIEW_SRH_USERCID] As \ +select channelinfo.[channelID], channelNickName, channelUrl, bitRate \ +FROM channelinfo LEFT JOIN urlInfo ON channelinfo.channelID = urlInfo.channelID "); + + +/************************************************** +* define for drop IRDB VIEW, TABLE AND TRIGGER +***************************************************/ +//drop view; +const QString IR_VIEW1_DROP("Drop View If Exists MAIN.[IR_VIEW1];"); +const QString IR_VIEW2_DROP("Drop View If Exists MAIN.[IR_VIEW2];"); +const QString IR_VIEW3_DROP("Drop View If Exists MAIN.[IR_VIEW3];"); +const QString IR_VIEW4_DROP("Drop View If Exists MAIN.[IR_VIEW4];"); +const QString IR_VIEW5_DROP("Drop View If Exists MAIN.[IR_VIEW5];"); +const QString IR_VIEW6_DROP("Drop View If Exists MAIN.[IR_VIEW6];"); +const QString IR_VIEW_ALL_DROP("Drop View If Exists MAIN.[IR_VIEW_ALL];"); + +//drop table; +const QString IR_IRBUFF_DROP("Drop table if exists main.[IRBUFF];"); +const QString IR_CHANNELHISTORY_DROP("Drop table if exists main.[channelHistory];"); +const QString IR_SEARCHRLT_DROP("Drop table if exists main.[searchRlt];"); +const QString IR_CHANNELINFO_DROP("Drop table if exists main.[channelInfo];"); +const QString IR_IMG_DROP("Drop table if exists main.[img];"); +const QString IR_URLINFO_DROP("Drop table if exists main.[urlInfo];"); +const QString IR_ADVERTISEMENT_DROP("Drop table if exists main.[advertisement];"); +const QString IR_SONGHISTORY_DROP("Drop table if exists main.[songHistory];"); +const QString IR_FAVORITES_DROP("Drop table if exists main.[favorites];"); + + + +#endif /* IRSQLSTR_H_ */ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +