/*

* 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 <QString>

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;