amarok.sql

CREATE DATABASE amarok;
USE amarok;
CREATE TABLE tags (url VARCHAR(900),dir VARCHAR(900),createdate INTEGER,modifydate INTEGER,album INTEGER,artist INTEGER,composer INTEGER,genre INTEGER,title VARCHAR(255),year INTEGER,comment TEXT,track NUMERIC(4),discnumber INTEGER,bitrate INTEGER,length INTEGER,samplerate INTEGER,filesize INTEGER,filetype INTEGER,sampler BOOL,bpm FLOAT,deviceid INTEGER);
CREATE TABLE album (id INTEGER PRIMARY KEY ,name VARCHAR(255));
CREATE TABLE artist (id INTEGER PRIMARY KEY ,name VARCHAR(255));
CREATE TABLE composer (id INTEGER PRIMARY KEY ,name VARCHAR(255));
CREATE TABLE genre (id INTEGER PRIMARY KEY ,name VARCHAR(255));
CREATE TABLE year (id INTEGER PRIMARY KEY ,name VARCHAR(255));
CREATE TABLE images (path VARCHAR(900),deviceid INTEGER,artist VARCHAR(255),album VARCHAR(255));
CREATE TABLE embed (url VARCHAR(900),deviceid INTEGER,hash VARCHAR(900),description VARCHAR(255));
CREATE TABLE directories (dir VARCHAR(900),deviceid INTEGER,changedate INTEGER);
CREATE TABLE uniqueid (url VARCHAR(900),deviceid INTEGER,uniqueid VARCHAR(32) UNIQUE,dir VARCHAR(900));
CREATE TABLE admin (noption VARCHAR(255), value VARCHAR(255));
CREATE TABLE related_artists (artist VARCHAR(255),suggestion VARCHAR(255),changedate INTEGER );
CREATE TABLE amazon ( asin VARCHAR(20), locale VARCHAR(2), filename VARCHAR(33), refetchdate INTEGER );
CREATE TABLE lyrics (url VARCHAR(900), deviceid INTEGER,lyrics TEXT, uniqueid VARCHAR(32));
CREATE TABLE playlists (playlist VARCHAR(255), url VARCHAR(900), tracknum INTEGER );
CREATE TABLE labels (id INTEGER PRIMARY KEY , name VARCHAR(255), type INTEGER);
CREATE TABLE tags_labels (deviceid INTEGER,url VARCHAR(900), uniqueid VARCHAR(32), labelid INTEGER REFERENCES labels( id ) ON DELETE CASCADE );
CREATE TABLE podcastchannels (url VARCHAR(900) UNIQUE,title VARCHAR(255),weblink VARCHAR(900),image VARCHAR(900),comment TEXT,copyright VARCHAR(255),parent INTEGER,directory VARCHAR(255),autoscan BOOL, fetchtype INTEGER, autotransfer BOOL, haspurge BOOL, purgecount INTEGER );
CREATE TABLE podcastepisodes (id INTEGER PRIMARY KEY , url VARCHAR(900) UNIQUE,localurl VARCHAR(900),parent VARCHAR(900),guid VARCHAR(900),title VARCHAR(255),subtitle VARCHAR(255),composer VARCHAR(255),comment TEXT,filetype VARCHAR(255),createdate VARCHAR(255),length INTEGER,size INTEGER,isNew BOOL );
CREATE TABLE podcastfolders (id INTEGER PRIMARY KEY , name VARCHAR(255),parent INTEGER, isOpen BOOL );
CREATE TABLE statistics (url VARCHAR(900),deviceid INTEGER,createdate INTEGER,accessdate INTEGER,percentage FLOAT,rating INTEGER DEFAULT 0,playcounter INTEGER,uniqueid VARCHAR(32) UNIQUE,deleted BOOL DEFAULT 0,PRIMARY KEY(url, deviceid) );
CREATE TABLE devices (id INTEGER PRIMARY KEY ,type VARCHAR(255),label VARCHAR(255),lastmountpoint VARCHAR(255),uuid VARCHAR(255),servername VARCHAR(255),sharename VARCHAR(255));
CREATE INDEX album_idx ON album( name );
CREATE INDEX artist_idx ON artist( name );
CREATE INDEX composer_idx ON composer( name );
CREATE INDEX genre_idx ON genre( name );
CREATE INDEX year_idx ON year( name );
CREATE UNIQUE INDEX url_tag ON tags( url, deviceid );
CREATE INDEX album_tag ON tags( album );
CREATE INDEX artist_tag ON tags( artist );
CREATE INDEX composer_tag ON tags( composer );
CREATE INDEX genre_tag ON tags( genre );
CREATE INDEX year_tag ON tags( year );
CREATE INDEX sampler_tag ON tags( sampler );
CREATE INDEX images_album ON images( album );
CREATE INDEX images_artist ON images( artist );
CREATE INDEX images_url ON images( path, deviceid );
CREATE UNIQUE INDEX embed_url ON embed( url, deviceid );
CREATE INDEX embed_hash ON embed( hash );
CREATE UNIQUE INDEX directories_dir ON directories( dir, deviceid );
CREATE INDEX uniqueid_uniqueid ON uniqueid( uniqueid );
CREATE INDEX uniqueid_url ON uniqueid( url, deviceid );
CREATE INDEX tags_artist_index ON tags( artist );
CREATE INDEX tags_album_index ON tags( album );
CREATE INDEX tags_deviceid_index ON tags( deviceid );
CREATE INDEX tags_url_index ON tags( url );
CREATE INDEX embed_deviceid_index ON embed( deviceid );
CREATE INDEX embed_url_index ON embed( url );
CREATE INDEX related_artists_artist ON related_artists( artist );
CREATE INDEX url_podchannel ON podcastchannels( url );
CREATE INDEX url_podepisode ON podcastepisodes( url );
CREATE INDEX localurl_podepisode ON podcastepisodes( localurl );
CREATE INDEX url_podfolder ON podcastfolders( id );
CREATE INDEX devices_type ON devices( type );
CREATE INDEX devices_uuid ON devices( uuid );
CREATE INDEX devices_rshare ON devices( servername, sharename );
CREATE UNIQUE INDEX lyrics_url ON lyrics( url, deviceid );
CREATE INDEX lyrics_uniqueid ON lyrics( uniqueid );
CREATE INDEX playlist_playlists ON playlists( playlist );
CREATE INDEX url_playlists ON playlists( url );
CREATE UNIQUE INDEX labels_name ON labels( name, type );
CREATE INDEX tags_labels_uniqueid ON tags_labels( uniqueid );
CREATE INDEX tags_labels_url ON tags_labels( url, deviceid );
CREATE INDEX tags_labels_labelid ON tags_labels( labelid );
CREATE UNIQUE INDEX url_stats ON statistics( deviceid, url );
CREATE INDEX percentage_stats ON statistics( percentage );
CREATE INDEX rating_stats ON statistics( rating );
CREATE INDEX playcounter_stats ON statistics( playcounter );
CREATE INDEX uniqueid_stats ON statistics( uniqueid );