/* * File: database.h * Author: savop * * Created on 3. September 2009, 22:20 */ #ifndef _DATABASE_H #define _DATABASE_H #include #include #include "../common.h" #define SQLITE_PRINT_STATEMENTS #define SQLITE_PRINT_FETCHES #define SQLITE_CASCADE_DELETES #define PK_OBJECTS TOSTRING(1) #define PK_RESOURCES TOSTRING(2) #define PK_SEARCHCLASSES TOSTRING(3) #define SQLITE_FIRST_CUSTOMID TOSTRING(100) #define SQLITE_COLUMN_NAME_LENGTH 64 #define SQLITE_TABLE_RESOURCES "Resources" #define SQLITE_TABLE_OBJECTS "Objects" #define SQLITE_TABLE_ITEMS "Items" #define SQLITE_TABLE_CONTAINERS "Containers" #define SQLITE_TABLE_VIDEOITEMS "VideoItems" #define SQLITE_TABLE_AUDIOITEMS "AudioItems" #define SQLITE_TABLE_IMAGEITEMS "ImageItems" #define SQLITE_TABLE_VIDEOBROADCASTS "VideoBroadcasts" #define SQLITE_TABLE_AUDIOBROADCASTS "AudioBroadcasts" #define SQLITE_TABLE_MOVIES "Movies" #define SQLITE_TABLE_PHOTOS "Photos" #define SQLITE_TABLE_ALBUMS "Albums" #define SQLITE_TABLE_PLAYLISTS "Playlists" #define SQLITE_TABLE_SEARCHCLASS "SearchClass" #define SQLITE_TABLE_PRIMARY_KEYS "PrimaryKeys" #define SQLITE_TABLE_SYSTEM "System" #define SQLITE_TABLE_ITEMFINDER "ItemFinder" #define SQLITE_TYPE_TEXT "TEXT" #define SQLITE_TYPE_INTEGER "INTEGER" #define SQLITE_TYPE_BOOL SQLITE_TYPE_INTEGER #define SQLITE_TYPE_DATE SQLITE_TYPE_TEXT #define SQLITE_TYPE_ULONG SQLITE_TYPE_INTEGER #define SQLITE_TYPE_LONG SQLITE_TYPE_INTEGER #define SQLITE_TYPE_UINTEGER SQLITE_TYPE_INTEGER #define SQLITE_TRANSACTION_BEGIN "BEGIN IMMEDIATE TRANSACTION " #define SQLITE_TRANSACTION_END "COMMIT TRANSACTION" #define SQLITE_TRANSACTION_TYPE "ROLLBACK" #define SQLITE_CONFLICT_CLAUSE "ON CONFLICT " SQLITE_TRANSACTION_TYPE #define SQLITE_PRIMARY_KEY SQLITE_TYPE_INTEGER " PRIMARY KEY" #define SQLITE_NOT_NULL "NOT NULL" #define SQLITE_UNIQUE "UNIQUE" #define SQLITE_COL_OBJECTID "ObjectID" #define SQLITE_COL_PARENTID "ParentID" #define SQLITE_COL_TITLE "Title" #define SQLITE_COL_CREATOR "Creator" #define SQLITE_COL_CLASS "Class" #define SQLITE_COL_RESTRICTED "Restricted" #define SQLITE_COL_WRITESTATUS "WriteStatus" #define SQLITE_COL_REFERENCEID "RefID" #define SQLITE_COL_CLASSDERIVED "IncludeDerived" #define SQLITE_COL_SEARCHABLE "Searchable" #define SQLITE_COL_CONTAINER_UID "UpdateID" #define SQLITE_COL_RESOURCEID "ResourceID" #define SQLITE_COL_PROTOCOLINFO "ProtocolInfo" #define SQLITE_COL_CONTENTTYPE "ContentType" #define SQLITE_COL_RESOURCETYPE "ResourceType" #define SQLITE_COL_RESOURCE "Resource" #define SQLITE_COL_SIZE "Size" #define SQLITE_COL_DURATION "Duration" #define SQLITE_COL_BITRATE "Bitrate" #define SQLITE_COL_SAMPLEFREQUENCE "SampleFreq" #define SQLITE_COL_BITSPERSAMPLE "BitsPerSample" #define SQLITE_COL_NOAUDIOCHANNELS "NoAudioChannels" #define SQLITE_COL_COLORDEPTH "ColorDepth" #define SQLITE_COL_RESOLUTION "Resolution" #define SQLITE_COL_GENRE "Genre" #define SQLITE_COL_LONGDESCRIPTION "LongDescription" #define SQLITE_COL_PRODUCER "Producer" #define SQLITE_COL_RATING "Rating" #define SQLITE_COL_ACTOR "Actor" #define SQLITE_COL_DIRECTOR "Director" #define SQLITE_COL_DESCRIPTION "Description" #define SQLITE_COL_PUBLISHER "Publisher" #define SQLITE_COL_LANGUAGE "Language" #define SQLITE_COL_RELATION "Relation" #define SQLITE_COL_STORAGEMEDIUM "StorageMedium" #define SQLITE_COL_DVDREGIONCODE "DVDRegionCode" #define SQLITE_COL_CHANNELNAME "Channelname" #define SQLITE_COL_SCHEDULEDSTARTTIME "ScheduledStartTime" #define SQLITE_COL_SCHEDULEDENDTIME "ScheduledEndTime" #define SQLITE_COL_ICON "Icon" #define SQLITE_COL_REGION "Region" #define SQLITE_COL_CHANNELNR "ChannelNr" #define SQLITE_COL_RIGHTS "Rights" #define SQLITE_COL_RADIOCALLSIGN "CallSign" #define SQLITE_COL_RADIOSTATIONID "StationID" #define SQLITE_COL_RADIOBAND "Band" #define SQLITE_COL_CONTRIBUTOR "Contributor" #define SQLITE_COL_DATE "Date" #define SQLITE_COL_ALBUM "Album" #define SQLITE_COL_ARTIST "Artist" #define SQLITE_COL_DLNA_CONTAINERTYPE "DLNAContainer" #define SQLITE_COL_CHILDCOUNT "ChildCount" #define SQLITE_COL_ITEMFINDER "ItemFastID" #define SQLITE_UPNP_OBJECTID SQLITE_COL_OBJECTID " " SQLITE_TYPE_INTEGER " " SQLITE_NOT_NULL " " SQLITE_CONFLICT_CLAUSE " "\ SQLITE_UNIQUE " " SQLITE_CONFLICT_CLAUSE #define SQLITE_INSERT_TRIGGER(TableA,TableB,Class) "CREATE TRIGGER IF NOT EXISTS "\ TableA "_I_" TableB " "\ "BEFORE INSERT ON "\ TableB " "\ "FOR EACH ROW BEGIN "\ "SELECT CASE "\ "WHEN ("\ "((SELECT " SQLITE_COL_OBJECTID " FROM " TableA " "\ "WHERE " SQLITE_COL_OBJECTID "=NEW." SQLITE_COL_OBJECTID " "\ ") IS NULL) "\ "OR "\ "((SELECT " SQLITE_COL_OBJECTID " FROM " SQLITE_TABLE_OBJECTS " "\ "WHERE " SQLITE_COL_OBJECTID "=NEW." SQLITE_COL_OBJECTID " "\ "AND " SQLITE_COL_CLASS " LIKE '" Class "%') IS NULL) "\ ") THEN "\ "RAISE(" SQLITE_TRANSACTION_TYPE ", "\ "'INSERT on table " TableB " failed due constraint violation "\ "on foreign key " SQLITE_COL_OBJECTID "'"\ ") "\ "END; END;" #define SQLITE_UPDATE_TRIGGER(TableA,TableB,Class) "CREATE TRIGGER IF NOT EXISTS "\ TableA "_U_" TableB " "\ "BEFORE UPDATE ON "\ TableB " "\ "FOR EACH ROW BEGIN "\ "SELECT CASE "\ "WHEN ("\ "((SELECT " SQLITE_COL_OBJECTID " FROM " TableA " "\ "WHERE " SQLITE_COL_OBJECTID "=NEW." SQLITE_COL_OBJECTID " "\ "AND " SQLITE_COL_CLASS " LIKE '" Class "%') IS NULL)"\ ") THEN "\ "RAISE(" SQLITE_TRANSACTION_TYPE ", "\ "'UPDATE on table " TableB " failed due constraint violation "\ "on foreign key " SQLITE_COL_OBJECTID "'"\ ") "\ "END; END;" #define SQLITE_INSERT_REFERENCE_TRIGGER(Table,Column) "CREATE TRIGGER IF NOT EXISTS "\ Table "_I_" Table " "\ "BEFORE INSERT ON " \ Table " " \ "FOR EACH ROW BEGIN "\ "SELECT CASE "\ "WHEN ( "\ "((SELECT " SQLITE_COL_OBJECTID " FROM " Table " "\ "WHERE " SQLITE_COL_OBJECTID " = NEW." Column ") IS NULL) "\ "AND "\ "(NEW." Column "!=-1)"\ ")THEN "\ "RAISE(" SQLITE_TRANSACTION_TYPE ", 'INSERT on table " Table " "\ "violates foreign key \"" Column "\"') "\ "END; END;" #define SQLITE_UPDATE_REFERENCE_TRIGGER(Table,Column) "CREATE TRIGGER IF NOT EXISTS "\ Table "_U_" Table " "\ "BEFORE INSERT ON " \ Table " " \ "FOR EACH ROW BEGIN "\ "SELECT CASE "\ "WHEN ( "\ "((SELECT " SQLITE_COL_OBJECTID " FROM " Table " "\ "WHERE " SQLITE_COL_OBJECTID " = NEW." Column ") IS NULL) "\ "AND "\ "(NEW." Column "!=-1)"\ ")THEN "\ "RAISE(" SQLITE_TRANSACTION_TYPE ", 'UPDATE on table " Table " "\ "violates foreign key \"" Column "\"') "\ "END; END;" #define SQLITE_DELETE_REFERENCE_TRIGGER(Table,Column) "CREATE TRIGGER IF NOT EXISTS "\ Table "_D_" Table " " \ "BEFORE DELETE ON " \ Table " " \ "FOR EACH ROW BEGIN "\ "SELECT CASE "\ "WHEN ("\ "(SELECT " Column " FROM " Table " "\ "WHERE " Column " = OLD." SQLITE_COL_OBJECTID ") IS NOT NULL"\ ")THEN "\ "RAISE(" SQLITE_TRANSACTION_TYPE ", 'DELETE on table " Table " "\ "violates foreign key \"" Column "\"') "\ "END; END;" #ifdef SQLITE_CASCADE_DELETES #define SQLITE_DELETE_TRIGGER(TableA,TableB) "CREATE TRIGGER IF NOT EXISTS "\ TableA "_D_" TableB " "\ "BEFORE DELETE ON "\ TableA " "\ "FOR EACH ROW BEGIN "\ "DELETE FROM " TableB " "\ "WHERE " SQLITE_COL_OBJECTID "=OLD." SQLITE_COL_OBJECTID "; "\ "END;" #define SQLITE_DELETE_PARENT_TRIGGER "CREATE TRIGGER IF NOT EXISTS "\ SQLITE_TABLE_OBJECTS "_D_" SQLITE_TABLE_OBJECTS " " \ "BEFORE DELETE ON " \ SQLITE_TABLE_OBJECTS " " \ "FOR EACH ROW BEGIN "\ "DELETE FROM " SQLITE_TABLE_OBJECTS " "\ "WHERE " SQLITE_COL_PARENTID "=OLD." SQLITE_COL_OBJECTID "; "\ "END;" #else #define SQLITE_DELETE_TRIGGER(TableA,TableB) "CREATE TRIGGER IF NOT EXISTS "\ TableA "_D_" TableB " "\ "BEFORE DELETE ON "\ TableA " "\ "FOR EACH ROW BEGIN "\ "SELECT CASE "\ "WHEN ("\ "(SELECT " SQLITE_COL_OBJECTID " FROM " TableB " "\ "WHERE " SQLITE_COL_OBJECTID "=OLD." SQLITE_COL_OBJECTID ") IS NOT NULL"\ ") THEN "\ "RAISE(" SQLITE_TRANSACTION_TYPE ", "\ "'DELETE on table " TableA " failed due constraint violation "\ "on foreign key " SQLITE_COL_OBJECTID "'"\ ") "\ "END; END;" #define SQLITE_DELETE_PARENT_TRIGGER SQLITE_DELETE_REFERENCE_TRIGGER(SQLITE_TABLE_OBJECTS, SQLITE_COL_PARENTID) #endif /**********************************************\ * * * Primary keys * * * \**********************************************/ #define SQLITE_CREATE_TABLE_PRIMARY_KEYS "CREATE TABLE IF NOT EXISTS "\ SQLITE_TABLE_PRIMARY_KEYS \ "("\ "KeyID " SQLITE_PRIMARY_KEY " " SQLITE_NOT_NULL ","\ "Key " SQLITE_TYPE_INTEGER " " SQLITE_NOT_NULL\ ");"\ "INSERT OR IGNORE INTO "\ SQLITE_TABLE_PRIMARY_KEYS \ "(KeyID, Key) VALUES ("\ PK_OBJECTS "," SQLITE_FIRST_CUSTOMID\ ");"\ "INSERT OR IGNORE INTO "\ SQLITE_TABLE_PRIMARY_KEYS \ "(KeyID, Key) VALUES ("\ PK_RESOURCES ",0"\ ");"\ "INSERT OR IGNORE INTO "\ SQLITE_TABLE_PRIMARY_KEYS \ "(KeyID, Key) VALUES ("\ PK_SEARCHCLASSES ",0"\ ");" #define SQLITE_TRIGGER_UPDATE_OBJECTID "CREATE TRIGGER IF NOT EXISTS "\ SQLITE_TABLE_OBJECTS "_PK_UPDATE "\ "AFTER INSERT ON "\ SQLITE_TABLE_OBJECTS " "\ "BEGIN "\ "UPDATE " SQLITE_TABLE_PRIMARY_KEYS " SET Key=Key+1 WHERE KeyID=" PK_OBJECTS "; "\ "END;" /**********************************************\ * * * System settings * * * \**********************************************/ #define SQLITE_CREATE_TABLE_SYSTEM "CREATE TABLE IF NOT EXISTS "\ SQLITE_TABLE_SYSTEM " "\ "("\ "Key " SQLITE_TYPE_TEXT " " SQLITE_NOT_NULL " " SQLITE_UNIQUE ","\ "Value " SQLITE_TYPE_TEXT " "\ ");" #define SQLITE_TRIGGER_UPDATE_SYSTEM "CREATE TRIGGER IF NOT EXISTS "\ SQLITE_TABLE_SYSTEM "_VALUE_UPDATE "\ "BEFORE UPDATE "\ "ON " SQLITE_TABLE_SYSTEM " "\ "WHEN ((SELECT Key FROM " SQLITE_TABLE_SYSTEM " WHERE Key=NEW.Key) IS NULL) "\ "BEGIN INSERT INTO " SQLITE_TABLE_SYSTEM " (Key) VALUES (NEW.Key); END;" /**********************************************\ * * * Fast item finder * * * \**********************************************/ #define SQLITE_CREATE_TABLE_ITEMFINDER "CREATE TABLE IF NOT EXISTS "\ SQLITE_TABLE_ITEMFINDER " "\ "("\ SQLITE_UPNP_OBJECTID ","\ SQLITE_COL_ITEMFINDER " " SQLITE_TYPE_TEXT " " SQLITE_NOT_NULL " " SQLITE_UNIQUE \ ");" #define SQLITE_TRIGGER_D_OBJECTS_ITEMFINDER SQLITE_DELETE_TRIGGER(SQLITE_TABLE_OBJECTS,\ SQLITE_TABLE_ITEMFINDER) /**********************************************\ * * * Objects * * * \**********************************************/ #define SQLITE_CREATE_TABLE_OBJECTS "CREATE TABLE IF NOT EXISTS "\ SQLITE_TABLE_OBJECTS \ "(" \ SQLITE_COL_OBJECTID " " SQLITE_PRIMARY_KEY " " SQLITE_NOT_NULL " " SQLITE_CONFLICT_CLAUSE "," \ SQLITE_COL_PARENTID " " SQLITE_TYPE_INTEGER " " SQLITE_NOT_NULL " " SQLITE_CONFLICT_CLAUSE "," \ SQLITE_COL_TITLE " " SQLITE_TYPE_TEXT " " SQLITE_NOT_NULL "," \ SQLITE_COL_CREATOR " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_CLASS " " SQLITE_TYPE_TEXT " " SQLITE_NOT_NULL "," \ SQLITE_COL_RESTRICTED " " SQLITE_TYPE_BOOL " " SQLITE_NOT_NULL "," \ SQLITE_COL_WRITESTATUS " " SQLITE_TYPE_INTEGER \ ");" // Trigger for foreign key ParentID #define SQLITE_TRIGGER_D_OBJECTS_OBJECTS SQLITE_DELETE_PARENT_TRIGGER #define SQLITE_TRIGGER_I_OBJECTS_OBJECTS SQLITE_INSERT_REFERENCE_TRIGGER(SQLITE_TABLE_OBJECTS, SQLITE_COL_PARENTID)\ "CREATE TRIGGER IF NOT EXISTS "\ SQLITE_TABLE_OBJECTS "_PI_" SQLITE_TABLE_OBJECTS " "\ "BEFORE INSERT ON "\ SQLITE_TABLE_OBJECTS " " \ "FOR EACH ROW BEGIN "\ "SELECT CASE "\ "WHEN ("\ "((SELECT " SQLITE_COL_PARENTID " FROM " SQLITE_TABLE_OBJECTS " "\ "WHERE " SQLITE_COL_PARENTID "=-1) IS NOT NULL) "\ "AND "\ "(NEW." SQLITE_COL_PARENTID "=-1)"\ ") THEN "\ "RAISE(" SQLITE_TRANSACTION_TYPE ","\ "'INSERT on table " SQLITE_TABLE_OBJECTS " violates constraint. "\ SQLITE_COL_PARENTID " must uniquely be -1') "\ "END; END;" #define SQLITE_TRIGGER_U_OBJECTS_OBJECTS SQLITE_UPDATE_REFERENCE_TRIGGER(SQLITE_TABLE_OBJECTS, SQLITE_COL_PARENTID)\ "CREATE TRIGGER IF NOT EXISTS "\ SQLITE_TABLE_OBJECTS "_PU_" SQLITE_TABLE_OBJECTS " "\ "BEFORE UPDATE ON "\ SQLITE_TABLE_OBJECTS " " \ "FOR EACH ROW BEGIN "\ "SELECT CASE "\ "WHEN ("\ "((SELECT " SQLITE_COL_PARENTID " FROM " SQLITE_TABLE_OBJECTS " "\ "WHERE " SQLITE_COL_PARENTID "=-1 "\ "AND " SQLITE_COL_OBJECTID "!=NEW." SQLITE_COL_OBJECTID " ) IS NOT NULL) "\ "AND "\ "(NEW." SQLITE_COL_PARENTID "=-1) AND (OLD." SQLITE_COL_PARENTID "!=-1) "\ ") THEN "\ "RAISE(" SQLITE_TRANSACTION_TYPE ","\ "'UPDATE on table " SQLITE_TABLE_OBJECTS " violates constraint. "\ SQLITE_COL_PARENTID " must uniquely be -1') "\ "END; END;" /**********************************************\ * * * Items * * * \**********************************************/ #define SQLITE_CREATE_TABLE_ITEMS "CREATE TABLE IF NOT EXISTS "\ SQLITE_TABLE_ITEMS \ "(" \ SQLITE_UPNP_OBJECTID "," \ SQLITE_COL_REFERENCEID " " SQLITE_TYPE_INTEGER " DEFAULT -1" \ ");" // Trigger for foreign key ObjectID #define SQLITE_TRIGGER_D_OBJECT_ITEMS SQLITE_DELETE_TRIGGER(SQLITE_TABLE_OBJECTS,\ SQLITE_TABLE_ITEMS) #define SQLITE_TRIGGER_I_OBJECT_ITEMS SQLITE_INSERT_TRIGGER(SQLITE_TABLE_OBJECTS,\ SQLITE_TABLE_ITEMS,\ UPNP_CLASS_ITEM) #define SQLITE_TRIGGER_U_OBJECT_ITEMS SQLITE_UPDATE_TRIGGER(SQLITE_TABLE_OBJECTS,\ SQLITE_TABLE_ITEMS,\ UPNP_CLASS_ITEM) // Trigger for Reference items #define SQLITE_TRIGGER_I_ITEMS_ITEMS SQLITE_INSERT_REFERENCE_TRIGGER(SQLITE_TABLE_ITEMS, SQLITE_COL_REFERENCEID) #define SQLITE_TRIGGER_U_ITEMS_ITEMS SQLITE_UPDATE_REFERENCE_TRIGGER(SQLITE_TABLE_ITEMS, SQLITE_COL_REFERENCEID) #define SQLITE_TRIGGER_D_ITEMS_ITEMS SQLITE_DELETE_REFERENCE_TRIGGER(SQLITE_TABLE_ITEMS, SQLITE_COL_REFERENCEID) /**********************************************\ * * * Containers * * * \**********************************************/ #define SQLITE_CREATE_TABLE_CONTAINER "CREATE TABLE IF NOT EXISTS "\ SQLITE_TABLE_CONTAINERS \ "(" \ SQLITE_UPNP_OBJECTID "," \ SQLITE_COL_SEARCHABLE " " SQLITE_TYPE_INTEGER ","\ SQLITE_COL_CONTAINER_UID " " SQLITE_TYPE_INTEGER " " SQLITE_NOT_NULL ","\ SQLITE_COL_DLNA_CONTAINERTYPE " " SQLITE_TYPE_TEXT \ ");" #define SQLITE_TRIGGER_D_OBJECT_CONTAINERS SQLITE_DELETE_TRIGGER(SQLITE_TABLE_OBJECTS,\ SQLITE_TABLE_CONTAINERS) #define SQLITE_TRIGGER_I_OBJECT_CONTAINERS SQLITE_INSERT_TRIGGER(SQLITE_TABLE_OBJECTS,\ SQLITE_TABLE_CONTAINERS,\ UPNP_CLASS_CONTAINER) #define SQLITE_TRIGGER_U_OBJECT_CONTAINERS SQLITE_UPDATE_TRIGGER(SQLITE_TABLE_OBJECTS,\ SQLITE_TABLE_CONTAINERS,\ UPNP_CLASS_CONTAINER) /**********************************************\ * * * Video items * * * \**********************************************/ #define SQLITE_CREATE_TABLE_VIDEOITEMS "CREATE TABLE IF NOT EXISTS "\ SQLITE_TABLE_VIDEOITEMS \ "(" \ SQLITE_UPNP_OBJECTID "," \ SQLITE_COL_GENRE " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_LONGDESCRIPTION " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_PRODUCER " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_RATING " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_ACTOR " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_DIRECTOR " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_DESCRIPTION " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_PUBLISHER " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_LANGUAGE " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_RELATION " " SQLITE_TYPE_TEXT \ ");" #define SQLITE_TRIGGER_D_ITEMS_VIDEOITEMS SQLITE_DELETE_TRIGGER(SQLITE_TABLE_ITEMS, SQLITE_TABLE_VIDEOITEMS) #define SQLITE_TRIGGER_U_ITEMS_VIDEOITEMS SQLITE_UPDATE_TRIGGER(SQLITE_TABLE_ITEMS, \ SQLITE_TABLE_VIDEOITEMS, \ UPNP_CLASS_VIDEO) #define SQLITE_TRIGGER_I_ITEMS_VIDEOITEMS SQLITE_INSERT_TRIGGER(SQLITE_TABLE_ITEMS, \ SQLITE_TABLE_VIDEOITEMS, \ UPNP_CLASS_VIDEO) /**********************************************\ * * * Audio items * * * \**********************************************/ #define SQLITE_CREATE_TABLE_AUDIOITEMS "CREATE TABLE IF NOT EXISTS "\ SQLITE_TABLE_AUDIOITEMS \ "(" \ SQLITE_UPNP_OBJECTID "," \ SQLITE_COL_GENRE " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_LONGDESCRIPTION " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_DESCRIPTION " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_PUBLISHER " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_RELATION " " SQLITE_TYPE_TEXT \ ");" #define SQLITE_TRIGGER_D_ITEMS_AUDIOITEMS SQLITE_DELETE_TRIGGER(SQLITE_TABLE_ITEMS, SQLITE_TABLE_AUDIOITEMS) #define SQLITE_TRIGGER_U_ITEMS_AUDIOITEMS SQLITE_UPDATE_TRIGGER(SQLITE_TABLE_ITEMS, \ SQLITE_TABLE_AUDIOITEMS, \ UPNP_CLASS_AUDIO) #define SQLITE_TRIGGER_I_ITEMS_AUDIOITEMS SQLITE_INSERT_TRIGGER(SQLITE_TABLE_ITEMS, \ SQLITE_TABLE_AUDIOITEMS, \ UPNP_CLASS_AUDIO) /**********************************************\ * * * Image items * * * \**********************************************/ #define SQLITE_CREATE_TABLE_IMAGEITEMS "CREATE TABLE IF NOT EXISTS "\ SQLITE_TABLE_IMAGEITEMS \ "("\ SQLITE_UPNP_OBJECTID "," \ SQLITE_COL_LONGDESCRIPTION " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_DESCRIPTION " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_PUBLISHER " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_STORAGEMEDIUM " " SQLITE_TYPE_TEXT ","\ SQLITE_COL_RATING " " SQLITE_TYPE_TEXT ","\ SQLITE_COL_DATE " " SQLITE_TYPE_TEXT ","\ SQLITE_COL_RIGHTS " " SQLITE_TYPE_TEXT\ ");" #define SQLITE_TRIGGER_D_ITEMS_IMAGEITEMS SQLITE_DELETE_TRIGGER(SQLITE_TABLE_ITEMS, SQLITE_TABLE_IMAGEITEMS) #define SQLITE_TRIGGER_U_ITEMS_IMAGEITEMS SQLITE_UPDATE_TRIGGER(SQLITE_TABLE_ITEMS, \ SQLITE_TABLE_IMAGEITEMS, \ UPNP_CLASS_IMAGE) #define SQLITE_TRIGGER_I_ITEMS_IMAGEITEMS SQLITE_INSERT_TRIGGER(SQLITE_TABLE_ITEMS, \ SQLITE_TABLE_IMAGEITEMS, \ UPNP_CLASS_IMAGE) /**********************************************\ * * * Video broadcasts * * * \**********************************************/ #define SQLITE_CREATE_TABLE_VIDEOBROADCASTS "CREATE TABLE IF NOT EXISTS "\ SQLITE_TABLE_VIDEOBROADCASTS \ "("\ SQLITE_UPNP_OBJECTID "," \ SQLITE_COL_ICON " " SQLITE_TYPE_TEXT ","\ SQLITE_COL_REGION " " SQLITE_TYPE_TEXT ","\ SQLITE_COL_CHANNELNR " " SQLITE_TYPE_TEXT ","\ SQLITE_COL_CHANNELNAME " " SQLITE_TYPE_TEXT " " SQLITE_UNIQUE \ ");" #define SQLITE_TRIGGER_D_VIDEOITEMS_VIDEOBROADCASTS SQLITE_DELETE_TRIGGER(SQLITE_TABLE_VIDEOITEMS, SQLITE_TABLE_VIDEOBROADCASTS) #define SQLITE_TRIGGER_U_VIDEOITEMS_VIDEOBROADCASTS SQLITE_UPDATE_TRIGGER(SQLITE_TABLE_VIDEOITEMS,\ SQLITE_TABLE_VIDEOBROADCASTS,\ UPNP_CLASS_VIDEOBC) #define SQLITE_TRIGGER_I_VIDEOITEMS_VIDEOBROADCASTS SQLITE_INSERT_TRIGGER(SQLITE_TABLE_VIDEOITEMS,\ SQLITE_TABLE_VIDEOBROADCASTS,\ UPNP_CLASS_VIDEOBC) /**********************************************\ * * * Audio broadcasts * * * \**********************************************/ #define SQLITE_CREATE_TABLE_AUDIOBROADCASTS "CREATE TABLE IF NOT EXISTS "\ SQLITE_TABLE_AUDIOBROADCASTS \ "("\ SQLITE_UPNP_OBJECTID "," \ SQLITE_COL_REGION " " SQLITE_TYPE_TEXT ","\ SQLITE_COL_RADIOCALLSIGN " " SQLITE_TYPE_TEXT ","\ SQLITE_COL_RADIOSTATIONID " " SQLITE_TYPE_TEXT ","\ SQLITE_COL_RADIOBAND " " SQLITE_TYPE_TEXT ","\ SQLITE_COL_CHANNELNR " " SQLITE_TYPE_INTEGER \ ");" #define SQLITE_TRIGGER_D_AUDIOITEMS_AUDIOBROADCASTS SQLITE_DELETE_TRIGGER(SQLITE_TABLE_AUDIOITEMS, SQLITE_TABLE_AUDIOBROADCASTS) #define SQLITE_TRIGGER_I_AUDIOITEMS_AUDIOBROADCASTS SQLITE_INSERT_TRIGGER(SQLITE_TABLE_AUDIOITEMS,\ SQLITE_TABLE_AUDIOBROADCASTS,\ UPNP_CLASS_AUDIOBC) #define SQLITE_TRIGGER_U_AUDIOITEMS_AUDIOBROADCASTS SQLITE_UPDATE_TRIGGER(SQLITE_TABLE_AUDIOITEMS,\ SQLITE_TABLE_AUDIOBROADCASTS,\ UPNP_CLASS_AUDIOBC) /**********************************************\ * * * Movies * * * \**********************************************/ #define SQLITE_CREATE_TABLE_MOVIES "CREATE TABLE IF NOT EXISTS "\ SQLITE_TABLE_MOVIES \ "("\ SQLITE_UPNP_OBJECTID "," \ SQLITE_COL_STORAGEMEDIUM " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_DVDREGIONCODE " " SQLITE_TYPE_INTEGER "," \ SQLITE_COL_CHANNELNAME " " SQLITE_TYPE_TEXT ","\ SQLITE_COL_SCHEDULEDSTARTTIME " " SQLITE_TYPE_TEXT ","\ SQLITE_COL_SCHEDULEDENDTIME " " SQLITE_TYPE_TEXT\ ");" #define SQLITE_TRIGGER_D_VIDEOITEMS_MOVIES SQLITE_DELETE_TRIGGER(SQLITE_TABLE_VIDEOITEMS, SQLITE_TABLE_MOVIES) #define SQLITE_TRIGGER_I_VIDEOITEMS_MOVIES SQLITE_INSERT_TRIGGER(SQLITE_TABLE_VIDEOITEMS,\ SQLITE_TABLE_MOVIES,\ UPNP_CLASS_MOVIE) #define SQLITE_TRIGGER_U_VIDEOITEMS_MOVIES SQLITE_UPDATE_TRIGGER(SQLITE_TABLE_VIDEOITEMS,\ SQLITE_TABLE_MOVIES,\ UPNP_CLASS_MOVIE) /**********************************************\ * * * Photos * * * \**********************************************/ #define SQLITE_CREATE_TABLE_PHOTOS "CREATE TABLE IF NOT EXISTS "\ SQLITE_TABLE_PHOTOS \ "("\ SQLITE_UPNP_OBJECTID "," \ SQLITE_COL_ALBUM " " SQLITE_TYPE_TEXT\ ");" #define SQLITE_TRIGGER_D_IMAGEITEMS_PHOTOS SQLITE_DELETE_TRIGGER(SQLITE_TABLE_IMAGEITEMS, SQLITE_TABLE_PHOTOS) #define SQLITE_TRIGGER_I_IMAGEITEMS_PHOTOS SQLITE_INSERT_TRIGGER(SQLITE_TABLE_IMAGEITEMS,\ SQLITE_TABLE_PHOTOS,\ UPNP_CLASS_PHOTO) #define SQLITE_TRIGGER_U_IMAGEITEMS_PHOTOS SQLITE_UPDATE_TRIGGER(SQLITE_TABLE_IMAGEITEMS,\ SQLITE_TABLE_PHOTOS,\ UPNP_CLASS_PHOTO) /**********************************************\ * * * Albums * * * \**********************************************/ #define SQLITE_CREATE_TABLE_ALBUMS "CREATE TABLE IF NOT EXISTS "\ SQLITE_TABLE_ALBUMS \ "("\ SQLITE_UPNP_OBJECTID "," \ SQLITE_COL_STORAGEMEDIUM " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_LONGDESCRIPTION " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_DESCRIPTION " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_PUBLISHER " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_CONTRIBUTOR " " SQLITE_TYPE_TEXT ","\ SQLITE_COL_DATE " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_RELATION " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_RIGHTS " " SQLITE_TYPE_TEXT \ ");" #define SQLITE_TRIGGER_D_CONTAINERS_ALBUMS SQLITE_DELETE_TRIGGER(SQLITE_TABLE_CONTAINERS, SQLITE_TABLE_ALBUMS) #define SQLITE_TRIGGER_U_CONTAINERS_ALBUMS SQLITE_UPDATE_TRIGGER(SQLITE_TABLE_CONTAINERS,\ SQLITE_TABLE_ALBUMS,\ UPNP_CLASS_ALBUM) #define SQLITE_TRIGGER_I_CONTAINERS_ALBUMS SQLITE_INSERT_TRIGGER(SQLITE_TABLE_CONTAINERS,\ SQLITE_TABLE_ALBUMS,\ UPNP_CLASS_ALBUM) /**********************************************\ * * * Playlists * * * \**********************************************/ #define SQLITE_CREATE_TABLE_PLAYLISTS "CREATE TABLE IF NOT EXISTS "\ SQLITE_TABLE_PLAYLISTS \ "(" \ SQLITE_UPNP_OBJECTID "," \ SQLITE_COL_ARTIST " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_GENRE " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_LONGDESCRIPTION " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_DESCRIPTION " " SQLITE_TYPE_TEXT ","\ SQLITE_COL_PRODUCER " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_STORAGEMEDIUM " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_CONTRIBUTOR " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_DATE " " SQLITE_TYPE_TEXT ","\ SQLITE_COL_LANGUAGE " " SQLITE_TYPE_TEXT ","\ SQLITE_COL_RIGHTS " " SQLITE_TYPE_TEXT\ ");" #define SQLITE_TRIGGER_D_CONTAINERS_PLAYLISTS SQLITE_DELETE_TRIGGER(SQLITE_TABLE_CONTAINERS, SQLITE_TABLE_PLAYLISTS) #define SQLITE_TRIGGER_I_CONTAINERS_PLAYLISTS SQLITE_INSERT_TRIGGER(SQLITE_TABLE_CONTAINERS,\ SQLITE_TABLE_PLAYLISTS,\ UPNP_CLASS_PLAYLISTCONT) #define SQLITE_TRIGGER_U_CONTAINERS_PLAYLISTS SQLITE_UPDATE_TRIGGER(SQLITE_TABLE_CONTAINERS,\ SQLITE_TABLE_PLAYLISTS,\ UPNP_CLASS_PLAYLISTCONT) /**********************************************\ * * * Search classes * * * \**********************************************/ #define SQLITE_CREATE_TABLE_SEARCHCLASS "CREATE TABLE IF NOT EXISTS "\ SQLITE_TABLE_SEARCHCLASS \ "(" \ SQLITE_COL_OBJECTID " " SQLITE_TYPE_INTEGER " " SQLITE_NOT_NULL "," \ SQLITE_COL_CLASS " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_CLASSDERIVED " " SQLITE_TYPE_BOOL \ ");" #define SQLITE_TRIGGER_D_CONTAINERS_SEARCHCLASSES "CREATE TRIGGER IF NOT EXISTS " \ SQLITE_TABLE_CONTAINERS "_D_" SQLITE_TABLE_SEARCHCLASS " " \ "BEFORE DELETE ON " \ SQLITE_TABLE_CONTAINERS " " \ "FOR EACH ROW BEGIN "\ "DELETE FROM " SQLITE_TABLE_SEARCHCLASS " "\ "WHERE " SQLITE_COL_OBJECTID "= OLD." SQLITE_COL_OBJECTID "; " \ "END;" #define SQLITE_TRIGGER_U_CONTAINERS_SEARCHCLASSES "CREATE TRIGGER IF NOT EXISTS " \ SQLITE_TABLE_CONTAINERS "_U_" SQLITE_TABLE_SEARCHCLASS " " \ "BEFORE UPDATE ON " \ SQLITE_TABLE_SEARCHCLASS " " \ "FOR EACH ROW BEGIN "\ "SELECT CASE "\ "WHEN ("\ "(SELECT " SQLITE_COL_OBJECTID " FROM " SQLITE_TABLE_CONTAINERS " "\ "WHERE " SQLITE_COL_OBJECTID "=NEW." SQLITE_COL_OBJECTID ") IS NULL "\ ") THEN "\ "RAISE (" SQLITE_TRANSACTION_TYPE ", 'UPDATE on table " SQLITE_TABLE_SEARCHCLASS " "\ "violates foreign key constraint \"" SQLITE_COL_OBJECTID "\"') " \ "END; END;" #define SQLITE_TRIGGER_I_CONTAINERS_SEARCHCLASSES "CREATE TRIGGER IF NOT EXISTS " \ SQLITE_TABLE_CONTAINERS "_I_" SQLITE_TABLE_SEARCHCLASS " " \ "BEFORE INSERT ON " \ SQLITE_TABLE_SEARCHCLASS " " \ "FOR EACH ROW BEGIN "\ "SELECT CASE "\ "WHEN ("\ "(SELECT " SQLITE_COL_OBJECTID " FROM " SQLITE_TABLE_CONTAINERS " "\ "WHERE " SQLITE_COL_OBJECTID "=NEW." SQLITE_COL_OBJECTID ") IS NULL "\ ") THEN "\ "RAISE (" SQLITE_TRANSACTION_TYPE ", 'INSERT on table " SQLITE_TABLE_SEARCHCLASS " "\ "violates foreign key constraint \"" SQLITE_COL_OBJECTID "\"') " \ "END; END;" /**********************************************\ * * * Resources * * * \**********************************************/ #define SQLITE_CREATE_TABLE_RESOURCES "CREATE TABLE IF NOT EXISTS "\ SQLITE_TABLE_RESOURCES \ "(" \ SQLITE_COL_RESOURCEID " " SQLITE_PRIMARY_KEY " " SQLITE_NOT_NULL "," \ SQLITE_COL_OBJECTID " " SQLITE_TYPE_INTEGER " " SQLITE_NOT_NULL "," \ SQLITE_COL_PROTOCOLINFO " " SQLITE_TYPE_TEXT " " SQLITE_NOT_NULL "," \ SQLITE_COL_CONTENTTYPE " " SQLITE_TYPE_TEXT " " SQLITE_NOT_NULL "," \ SQLITE_COL_RESOURCETYPE " " SQLITE_TYPE_INTEGER " " SQLITE_NOT_NULL "," \ SQLITE_COL_RESOURCE " " SQLITE_TYPE_TEXT " " SQLITE_NOT_NULL "," \ SQLITE_COL_SIZE " " SQLITE_TYPE_ULONG "," \ SQLITE_COL_DURATION " " SQLITE_TYPE_TEXT "," \ SQLITE_COL_BITRATE " " SQLITE_TYPE_UINTEGER "," \ SQLITE_COL_SAMPLEFREQUENCE " " SQLITE_TYPE_UINTEGER "," \ SQLITE_COL_BITSPERSAMPLE " " SQLITE_TYPE_UINTEGER "," \ SQLITE_COL_NOAUDIOCHANNELS " " SQLITE_TYPE_UINTEGER "," \ SQLITE_COL_COLORDEPTH " " SQLITE_TYPE_UINTEGER "," \ SQLITE_COL_RESOLUTION " " SQLITE_TYPE_TEXT \ ");" #define SQLITE_TRIGGER_D_OBJECT_RESOURCES "CREATE TRIGGER IF NOT EXISTS " \ SQLITE_TABLE_OBJECTS "_D_" SQLITE_TABLE_RESOURCES " " \ "BEFORE DELETE ON " \ SQLITE_TABLE_OBJECTS " " \ "FOR EACH ROW BEGIN "\ "DELETE FROM " SQLITE_TABLE_RESOURCES " "\ "WHERE " SQLITE_COL_OBJECTID "= OLD." SQLITE_COL_OBJECTID "; " \ "END;" #define SQLITE_TRIGGER_I_OBJECT_RESOURCES "CREATE TRIGGER IF NOT EXISTS " \ SQLITE_TABLE_OBJECTS "_I_" SQLITE_TABLE_RESOURCES " " \ "BEFORE INSERT ON " \ SQLITE_TABLE_RESOURCES " " \ "FOR EACH ROW BEGIN "\ "SELECT CASE "\ "WHEN ("\ "(SELECT " SQLITE_COL_OBJECTID " FROM " SQLITE_TABLE_OBJECTS " "\ "WHERE " SQLITE_COL_OBJECTID "=NEW." SQLITE_COL_OBJECTID ") IS NULL"\ ") THEN "\ "RAISE (" SQLITE_TRANSACTION_TYPE ", 'INSERT on table " SQLITE_TABLE_RESOURCES " "\ "violates foreign key constraint \"" SQLITE_COL_OBJECTID "\"') " \ "END; END;" #define SQLITE_TRIGGER_U_OBJECT_RESOURCES "CREATE TRIGGER IF NOT EXISTS " \ SQLITE_TABLE_OBJECTS "_U_" SQLITE_TABLE_RESOURCES " " \ "BEFORE UPDATE ON " \ SQLITE_TABLE_RESOURCES " " \ "FOR EACH ROW BEGIN "\ "SELECT CASE "\ "WHEN ("\ "(SELECT " SQLITE_COL_OBJECTID " FROM " SQLITE_TABLE_OBJECTS " "\ "WHERE " SQLITE_COL_OBJECTID "=NEW." SQLITE_COL_OBJECTID ") IS NULL"\ ") THEN "\ "RAISE (" SQLITE_TRANSACTION_TYPE ", 'INSERT on table " SQLITE_TABLE_RESOURCES " "\ "violates foreign key constraint \"" SQLITE_COL_OBJECTID "\"') " \ "END; END;" class cSQLiteDatabase; class cRow : public cListObject { friend class cSQLiteDatabase; private: int currentCol; int ColCount; char** Columns; char** Values; cRow(); public: virtual ~cRow(); int Count(){ return this->ColCount; } bool fetchColumn(cString* Column, cString* Value); bool fetchColumn(char** Column, char** Value); }; class cRows : public cList { friend class cSQLiteDatabase; private: cRow* mLastRow; cRows(); public: virtual ~cRows(); bool fetchRow(cRow** Row); }; class cSQLiteDatabase { private: bool mAutoCommit; bool mActiveTransaction; cRow* mLastRow; cRows* mRows; sqlite3* mDatabase; static cSQLiteDatabase* mInstance; cSQLiteDatabase(); int initialize(); int initializeTables(); int initializeTriggers(); static int getResultRow(void* DB, int NumCols, char** Values, char** ColNames); public: virtual ~cSQLiteDatabase(); static cSQLiteDatabase* getInstance(); int getResultCount() const { return this->mRows->Count(); } long getLastInsertRowID() const; cRows* getResultRows() const { return this->mRows; } int execStatement(const char* Statement); void startTransaction(); void commitTransaction(); void rollbackTransaction(); void setAutoCommit(bool Commit=true){ this->mAutoCommit = Commit; } }; #endif /* _DATABASE_H */