/*

* Copyright (c) 2008 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.

*

* Contributors:

*

* Description:      SQL clause definitions

*

*/

#ifndef __CMSQLCLAUSEDEF_H__

#define __CMSQLCLAUSEDEF_H__

// ---------------------------------------------------------------------------

// Misc definitions

// ---------------------------------------------------------------------------

//

_LIT8( KCmSqlEmpty, "" );

_LIT8( KCmSqlSpace, " " );

_LIT8( KCmSqlLeftBracket, " ( " );

_LIT8( KCmSqlRightBracket, " ) " );

_LIT8( KCmSqlComma, ", " );

_LIT8( KCmSqlDot, "." );

_LIT8( KCmSqlEqual, " = " );

_LIT8( KCmSqlGreater, " >= " );

_LIT8( KCmSqlSmaller, " <= " );

_LIT8( KCmSqlLike, " LIKE " );

_LIT8( KCmSqlSemicolon, ";" );

_LIT8( KCmSqlVariable, "? " );

_LIT8( KCmSqlAppendString, "%S " );

_LIT8( KCmSqlConsistOf, " %%S% " );

_LIT8( KCmSqlBeginsWith, " %S% " );

_LIT8( KCmSqlEndsWith, " %%S " );

_LIT8( KCmSqlAppendInteger, "%d " );

_LIT8( KCmSqlAppendLong, "%ld " );

_LIT8( KCmSqlDummyTrueCriteria, "1 " );

_LIT8( KCmSqlDummyFalseCriteria, "0 " );

_LIT8( KCmSqlSelect, "SELECT " );

_LIT8( KCmSqlFrom, "FROM " );

_LIT8( KCmSqlAs, "AS " );

_LIT8( KCmSqlIn, "IN " );

_LIT8( KCmSqlOr, "OR" );

_LIT8( KCmSqlAnd, "AND " );

_LIT8( KCmSqlAll, "* " );

_LIT8( KCmSqlOrderByDate, "ORDER BY Date " );

_LIT8( KCmSqlOrderByRandom, "ORDER BY Random() " );

_LIT8( KCmSqlLimit, "LIMIT " );

_LIT8( KCmSqlOffset, "OFFSET " );

_LIT8( KCmSqlAsc, "ASC ");

_LIT8( KCmSqlDesc, "DESC " );

_LIT8( KCmSqlSortValue, "sortvalue" ); // Used when sorting object

                                   // query by property value

_LIT8( KCmSqlCollateNoCase, " COLLATE NOCASE " ); // case insensitive sort

_LIT8( KCmSqlBaseSelect, 

       "SELECT Title, Date, Size, Uri, DeviceId, ItemId, Id "

       "FROM Items WHERE (" 

     );

_LIT8( KCmSqlMusicSelect, "SELECT Title, a.Value, Size, Uri, DeviceId, "

                          "ItemId, Id FROM Items LEFT JOIN Artists a "

                          "ON Items.ArtistId = a.ArtistId LEFT JOIN Albums b "

                          "ON Items.AlbumId = b.AlbumId "

                          "WHERE ("

                          );

_LIT8( KCmSqlSelectArtistById, "Items.ArtistId = " );

_LIT8( KCmSqlSelectAlbumById, "Items.AlbumId = " );

_LIT8( KCmSqlSelectGenreById, "Items.GenreId = " );

_LIT8( KCmSqlSelectResolutionById, "Items.ResolutionId = " );

_LIT8( KCmSqlSelectUpnpclassById, "Items.UpnpclassId = " );

_LIT8( KCmSqlFilterUpnpProfileId, "Items.ProfileId != " );

_LIT8( KCmSqlSelectTitle, "Items.Id = " );

_LIT8( KCmSqlSelectDuration, "Items.Duration " );

_LIT8( KCmSqlSelectBitrate, "Items.Bitrate " );

_LIT8( KCmSqlSelectSize, "Items.Size " );

_LIT8( KCmSqlSelectDeviceId, "Items.DeviceId = ");

_LIT8( KCmSqlSelectDate, "Items.Date ");

_LIT8( KCmSqlArtist, "Artists " );

_LIT8( KCmSqlAlbum, "Albums " );

_LIT8( KCmSqlGenre, "Genres " );

_LIT8( KCmSqlUpnpclass, "Upnpclasses " );

_LIT8( KCmSqlResolutions, "Resolutions " );

_LIT8( KCmSqlUpnpProfiles, "UpnpProfiles " );

_LIT8( KCmSqlArtistId, "ArtistId" );

_LIT8( KCmSqlAlbumId, "AlbumId" );

_LIT8( KCmSqlGenreId, "GenreId" );

_LIT8( KCmSqlProfileId, "ProfileId" );

_LIT8( KCmSqlUpnpclassId, "UpnpclassId" );

_LIT8( KCmSqlResolutionId, "ResolutionId" );

_LIT8( KCmSqlSelectMediatype, " ( Mediatype = %d ) " );

_LIT8( KCmSqlFreeTextSelectAudio, 

    "( Items.Title LIKE \'%%%S%%\' ) OR ( a.Value LIKE \'%%%S%%\' ) OR "

    "( b.Value LIKE \'%%%S%%\' ) " );

_LIT8( KCmSqlFreeTextSelectImageOrVideo, "( Items.Title LIKE \'%%%S%%\' ) " );

_LIT8(KCmSqlBeginTransaction, "BEGIN;");

_LIT8(KCmSqlCommit, "COMMIT;");

_LIT8( KCmSqlSelectMaxIndex, "SELECT count(*) FROM %S" );

_LIT8( KCmSqlSelectMaxPropertyIndex, "SELECT MAX( %S ) FROM %S" );

_LIT8( KCmSqlSelectMaxItemIndex, "SELECT MAX( Id ) FROM Items;" );

_LIT8( KCmSqlSelectItemId, "SELECT Id FROM Items WHERE "

    "( LOWER(Title) = ? AND Size = ? AND DeviceId = ? )" 

    );  

_LIT8( KCmSqlFilteredPropertySelect, "SELECT Items.Id, Items.Title, "

    "Items.ArtistId, b.Value, Items.AlbumId, a.Value, Items.GenreId, "

    "c.Value FROM Items LEFT JOIN Artists b "

    "ON Items.ArtistId = b.ArtistId LEFT JOIN Albums a "

    "ON Items.AlbumId = a.AlbumId LEFT JOIN Genres c "

    "ON Items.GenreId = c.GenreId "

    "WHERE Items.Mediatype = 2 "

    );

_LIT8( KCmSqlFilteredPropertySelectVideo, "SELECT Id, Title "

    "FROM Items WHERE Mediatype = 0 "

    );

_LIT8( KCmSqlWhere,

    "WHERE "

    );

// ---------------------------------------------------------------------------

// INSERT STATEMENTS

// ---------------------------------------------------------------------------

//

// Inserting item

_LIT8( KCmSqlInsertItem,

    "INSERT INTO Items( " 

    "ItemId, Hash, Uri, Title, AlbumArtUri, DeviceId, Duration, Bitrate, "

    "Date, HarvestDate, Size, Mediatype, UpnpclassId, ArtistId, AlbumId, "

    "GenreId, ResolutionId, ProfileId, SearchId )"

    "VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? );"

    );

// Inserting resource

_LIT8( KCmSqlInsertResource,

    "INSERT INTO Resources( " 

    "ResItemId, ResResolution, ResUri, Size, ResDuration, ResBitrate )"

    "VALUES( ?, ?, ?, ?, ?, ? );"

    );

// Inserting artist into artist table

_LIT8( KCmSqlInsertArtist,

    "INSERT INTO Artists( " 

    "Value ) "

    "VALUES( ? );"

    );

// Inserting album into album table

_LIT8( KCmSqlInsertAlbum,

    "INSERT INTO Albums( " 

    "Value ) "

    "VALUES( ? );"

    );

// Inserting genre into genre table    

_LIT8( KCmSqlInsertGenre,

    "INSERT INTO Genres( " 

    "Value ) "

    "VALUES( ? );"

    );

// Inserting upnp class into upnp class table

_LIT8( KCmSqlInsertUpnpclass,

    "INSERT INTO Upnpclasses( " 

    "Value ) "

    "VALUES( ? );"

    );

// Inserting upnp class into upnp class table

_LIT8( KCmSqlInsertUpnpProfile,

    "INSERT INTO UpnpProfiles( " 

    "Value ) "

    "VALUES( ? );"

    );

// Inserting resolution into resolution table

_LIT8( KCmSqlInsertResolution,

    "INSERT INTO Resolutions( " 

    "Value, Width, Height, PixelCount ) "

    "VALUES( ?, ?, ?, ? );"

    );        

// ---------------------------------------------------------------------------

// SELECT STATEMENTS

// ---------------------------------------------------------------------------

//

// Selecting items harvested from the defined server

_LIT8( KCmSqlSelectGenericItem, 

    "SELECT Id, ItemId, Hash, SearchId "

    "FROM Items "

    "WHERE DeviceId = ? "

    "ORDER BY Hash;"

    );

_LIT8( KCmSqlSelectPropertyValues,

    "SELECT * FROM %S "

    "ORDER BY Value;"    

    );

_LIT8( KCmSqlValidateTableExistence,

    "SELECT count(*)"

        " FROM Items;"

        );

_LIT8( KCmSqlSelectTitleAndId,  

    "SELECT Id, Title "

    "FROM Items "

    "WHERE MediaType = %d "

    "ORDER BY Title;"

    );

_LIT8( KCmSqlSelectMediaCount,

    "SELECT count(*) FROM Items WHERE Mediatype = ?;"

    );            

_LIT8( KCmSqlSelectLimitHarvestDate,

    "SELECT HarvestDate FROM Items WHERE ( Mediatype = ? ) ORDER BY "

    "HarvestDate ASC LIMIT ?;"

    );

// ---------------------------------------------------------------------------

// DELETE STATEMENTS

// ---------------------------------------------------------------------------

//

_LIT8( KCmSqlDeleteItem, 

    "DELETE FROM Items "

    "WHERE Id = ? ;"

    );

_LIT8( KCmSqlDeleteResource, 

    "DELETE FROM Resources "

    "WHERE ResItemId = ? ;"

    );

_LIT8( KCmSqlDeleteMetadata, 

    "DELETE FROM Items WHERE DeviceId = ?;" 

    );        

_LIT8( KCmSqlDeleteOldestMediaItems, 

    "DELETE FROM Items WHERE HarvestDate <= ? AND MediaType = ?;"

    );

_LIT8( KCmSqlDeleteUnusedAlbums, 

    "DELETE FROM Albums WHERE AlbumId "

    "NOT IN ( SELECT AlbumId FROM Items );"

    );

_LIT8( KCmSqlDeleteUnusedArtists, 

    "DELETE FROM Artists WHERE ArtistId "

    "NOT IN ( SELECT ArtistId FROM Items );"

    );

_LIT8( KCmSqlDeleteUnusedGenres,

    "DELETE FROM Genres WHERE GenreId "

    "NOT IN ( SELECT GenreId FROM Items );"

    );

_LIT8( KCmSqlDeleteUnusedUpnpclasses,

    "DELETE FROM Upnpclasses WHERE UpnpclassId "

    "NOT IN ( SELECT UpnpclassId FROM Items );"

    );

_LIT8( KCmSqlDeleteUnusedUpnpProfileIds,

    "DELETE FROM UpnpProfiles WHERE ProfileId "

    "NOT IN ( SELECT ProfileId FROM Items );"

    );

#endif // __CMSQLCLAUSEDEF_H__