/*

* 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:      Includes SQL clauses for creating and dropping tables/indexes

*

*/

#ifndef __CMSQLMAINTENANCECLAUSES_H__

#define __CMSQLMAINTENANCECLAUSES_H__

///////////////////////////////////////////////////////////////////

// DROP CLAUSES

///////////////////////////////////////////////////////////////////

_LIT8( KCmSqlDropArtists, "DROP TABLE Artists;");

_LIT8( KCmSqlDropGenres, "DROP TABLE Genres;" );

_LIT8( KCmSqlDropAlbums, "DROP TABLE Albums;" );

_LIT8( KCmSqlDropUpnpProfiles, "DROP TABLE UpnpProfiles;" );

_LIT8( KCmSqlDropUpnpClasses, "DROP TABLE Upnpclasses;" );

_LIT8( KCmSqlDropResolutions, "DROP TABLE Resolutions;" );

_LIT8( KCmSqlDropResources, "DROP TABLE Resources;" );

_LIT8( KCmSqlDropItems, "DROP TABLE Items;" );

///////////////////////////////////////////////////////////////////

// CREATE CLAUSES FOR TABLES

///////////////////////////////////////////////////////////////////

_LIT8( KCmSqlCreateArtists, 

    "CREATE TABLE Artists("

        "ArtistId           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"

        "Value              TEXT NOT NULL UNIQUE);"

        );

_LIT8( KCmSqlCreateGenres, 

    "CREATE TABLE Genres("

        "GenreId           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"

        "Value             TEXT NOT NULL UNIQUE);"

        );

_LIT8( KCmSqlCreateAlbums, 

    "CREATE TABLE Albums("

        "AlbumId           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"

        "Value             TEXT NOT NULL UNIQUE);"

        );

_LIT8( KCmSqlCreateResolutions, 

    "CREATE TABLE Resolutions("

        "ResolutionId       INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"

        "Value              TEXT NOT NULL UNIQUE,"

        "Width              INTEGER,"

        "Height             INTEGER,"

        "PixelCount         INTEGER);"

        );

_LIT8( KCmSqlCreateUpnpclasses, 

    "CREATE TABLE Upnpclasses("

        "UpnpclassId        INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"

        "Value              TEXT NOT NULL UNIQUE);"

        );            

_LIT8( KCmSqlCreateUpnpProfiles, 

    "CREATE TABLE UpnpProfiles("

        "ProfileId          INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"

        "Value              TEXT NOT NULL UNIQUE);"

        );                         

_LIT8( KCmSqlCreateResources, 

    "CREATE TABLE Resources("

        "ResourceId       INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"

        "ResItemId        INTEGER NOT NULL REFERENCES Items( Id ),"

        "ResResolution    INTEGER REFERENCES Resolutions( ResolutionId ),"

        "ResUri           TEXT NOT NULL,"

        "Size             INTEGER,"

        "ResDuration      INTEGER,"

        "ResBitrate       INTEGER);"

        );

_LIT8( KCmSqlCreateItems, 

    "CREATE TABLE Items("

        "Id                 INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"

        "ItemId             TEXT NOT NULL,"

        "Hash               TEXT NOT NULL,"

        "Uri                TEXT NOT NULL,"

        "Title              TEXT NOT NULL,"

        "AlbumArtUri        TEXT,"

        "DeviceId           INTEGER NOT NULL,"

        "Duration           INTEGER,"

        "Bitrate            INTEGER,"

        "Date               DATETIME,"

        "HarvestDate        DATETIME,"

        "Size               INTEGER,"

        "Mediatype          INTEGER,"

        "SearchId           INTEGER,"

        "UpnpclassId        INTEGER REFERENCES Upnpclasses( UpnpclassId ),"

        "ArtistId           INTEGER REFERENCES Artists( ArtistId ),"

        "AlbumId            INTEGER REFERENCES Albums( AlbumId ),"

        "GenreId            INTEGER REFERENCES Genres( GenreId ),"

        "ProfileId          INTEGER REFERENCES UpnpProfiles( ProfileId ),"

        "ResolutionId       INTEGER REFERENCES Resolutions( ResolutionId ) );"

        );

///////////////////////////////////////////////////////////////////

// INDEXES

///////////////////////////////////////////////////////////////////

_LIT8( KCmSqlCreateItemsIdIndex, 

    "CREATE INDEX ItemsId_Index ON Items(Id);"

    );

_LIT8( KCmSqlCreateItemTitleIndex, 

    "CREATE INDEX Item_Title_Index ON Items(Title);"

    );

_LIT8( KCmSqlCreateItemDateIndex, 

    "CREATE INDEX Item_Date_Index ON Items(Date);"

    );        

_LIT8( KCmSqlCreateItemHarvestDateIndex, 

    "CREATE INDEX Item_HarvestDate_Index ON Items(HarvestDate);"

    ); 

_LIT8( KCmSqlCreateItemArtistIndex, 

    "CREATE INDEX Item_Artist_Index ON Items(ArtistId);"

    );

_LIT8( KCmSqlCreateItemAlbumIndex, 

    "CREATE INDEX Item_Album_Index ON Items(AlbumId);"

    );

_LIT8( KCmSqlCreateItemGenreIndex, 

    "CREATE INDEX Item_Genre_Index ON Items(GenreId);"

    );

_LIT8( KCmSqlCreateItemUpnpclassIndex, 

    "CREATE INDEX Item_Upnpclass_Index ON Items(UpnpclassId);"

    );

_LIT8( KCmSqlCreateItemUpnpProfileIndex, 

    "CREATE INDEX Item_UpnpProfile_Index ON Items(ProfileId);"

    );                         

_LIT8( KCmSqlCreateItemResolutionIndex, 

    "CREATE INDEX Item_Resolution_Index ON Items(ResolutionId);"

    );

_LIT8( KCmSqlCreateItemMediatypeIndex, 

    "CREATE INDEX Item_Mediatype_Index ON Items(Mediatype);" 

    );

_LIT8( KCmSqlCreateAlbumIdIndex,

    "CREATE INDEX AlbumId_Index ON Albums(AlbumId);" 

    );

_LIT8( KCmSqlCreateArtistIdIndex,

    "CREATE INDEX ArtistId_Index ON Artists(ArtistId);" 

    ); 

_LIT8( KCmSqlCreateGenreIdIndex,

    "CREATE INDEX GenreId_Index ON Genres(GenreId);" 

    );

// Dropping indexes

_LIT8( KCmSqlDropItemsIdIndex, 

    "DROP INDEX ItemsId_Index;"

    );

_LIT8( KCmSqlDropItemTitleIndex,

    "DROP INDEX Item_Title_Index;"

    );

_LIT8( KCmSqlDropItemDateIndex,

    "DROP INDEX Item_Date_Index;"

    ); 

_LIT8( KCmSqlDropItemHarvestDateIndex,

    "DROP INDEX Item_HarvestDate_Index;"

    );             

_LIT8( KCmSqlDropItemArtistIndex, 

    "DROP INDEX Item_Artist_Index;"

    ); 

_LIT8( KCmSqlDropItemAlbumIndex, 

    "DROP INDEX Item_Album_Index;"

    );                

_LIT8( KCmSqlDropItemGenreIndex, 

    "DROP INDEX Item_Genre_Index;"

    ); 

_LIT8( KCmSqlDropItemUpnpclassIndex, 

    "DROP INDEX Item_Upnpclass_Index;"

    );

_LIT8( KCmSqlDropItemUpnpProfileIndex, 

    "DROP INDEX Item_UpnpProfile_Index;"

    );                 

_LIT8( KCmSqlDropItemResolutionIndex, 

    "DROP INDEX Item_Resolution_Index;"

    );

_LIT8( KCmSqlDropItemMediatypeIndex, 

    "DROP INDEX Item_Mediatype_Index;" 

    );

_LIT8( KCmSqlDropAlbumIdIndex,

    "DROP INDEX AlbumId_Index;" 

    );

_LIT8( KCmSqlDropArtistIdIndex,

    "DROP INDEX ArtistId_Index;" 

    ); 

_LIT8( KCmSqlDropGenreIdIndex,

    "DROP INDEX GenreId_Index;" 

    );

#endif // __CMSQLMAINTENANCECLAUSES_H__