kde4@notebook:~/.kde/share/apps/amarok> sqlite3 collection.db
SQLite version 3.4.1
Enter ".help" for instructions
sqlite> .schema
CREATE TABLE admin (key VARCHAR(255), version INTEGER);
CREATE TABLE albums (id INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(255) NOT NULL,artist INTEGER);
CREATE TABLE amazon (asin VARCHAR(20),locale VARCHAR(2),filename VARCHAR(33),refetchdate INTEGER );
CREATE TABLE artists (id INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(255) NOT NULL);
CREATE TABLE composers (id INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(255) NOT NULL);
CREATE TABLE devices (id INTEGER PRIMARY KEY AUTOINCREMENT,type VARCHAR(255),label VARCHAR(255),lastmountpoint VARCHAR(255),uuid VARCHAR(255),servername VARCHAR(255),sharename VARCHAR(255));
CREATE TABLE directories (id INTEGER PRIMARY KEY AUTOINCREMENT,deviceid INTEGER,dir VARCHAR(1024),changedate INTEGER);
CREATE TABLE genres (id INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(255) NOT NULL);
CREATE TABLE jamendo_albums (id INTEGER PRIMARY KEY, name VARCHAR(255),description VARCHAR(1024),popularity FLOAT, cover_url VARCHAR(1024),launch_year Integer, genre VARCHAR(1024),artist_id INTEGER, mp3_torrent_url VARCHAR(1024),ogg_torrent_url VARCHAR(1024) );
CREATE TABLE jamendo_artists (id INTEGER PRIMARY KEY, name VARCHAR(255),description VARCHAR(255),country VARCHAR(255),photo_url VARCHAR(255),jamendo_url VARCHAR(255),home_url VARCHAR(255));
CREATE TABLE jamendo_genre (id INTEGER PRIMARY KEY ,name VARCHAR(255),album_id INTEGER);
CREATE TABLE jamendo_tracks (id INTEGER PRIMARY KEY, name VARCHAR(255),track_number INTEGER,length INTEGER,preview_url VARCHAR(1024),album_id INTEGER,artist_id INTEGER );
CREATE TABLE labels (id INTEGER PRIMARY KEY AUTOINCREMENT,label VARCHAR(255));
CREATE TABLE lyrics (id INTEGER PRIMARY KEY AUTOINCREMENT,url INTEGER,lyrics TEXT);
CREATE TABLE magnatune_albums (id INTEGER PRIMARY KEY ,name VARCHAR(255),year INTEGER,artist_id INTEGER,album_code VARCHAR(255),cover_url VARCHAR(1024),description VARCHAR(1024));
CREATE TABLE magnatune_artists (id INTEGER PRIMARY KEY ,name VARCHAR(255),artist_page VARCHAR(1024),description VARCHAR(255),photo_url VARCHAR(1024));
CREATE TABLE magnatune_genre (id INTEGER PRIMARY KEY ,name VARCHAR(255),album_id INTEGER);
CREATE TABLE magnatune_moods (id INTEGER PRIMARY KEY ,track_id INTEGER,mood VARCHAR(255));
CREATE TABLE magnatune_tracks (id INTEGER PRIMARY KEY ,name VARCHAR(255),track_number INTEGER,length INTEGER,album_id INTEGER,artist_id INTEGER,preview_lofi VARCHAR(1024),preview_ogg VARCHAR(1024),preview_url VARCHAR(1024));
CREATE TABLE opmldirectory_albums (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(255),description VARCHAR(1024),artist_id INTEGER );
CREATE TABLE opmldirectory_artists (id INTEGER PRIMARY KEY, name VARCHAR(255),description VARCHAR(1024));
CREATE TABLE opmldirectory_genre (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(255),album_id INTEGER );
CREATE TABLE opmldirectory_tracks (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(255),track_number INTEGER,length INTEGER,preview_url VARCHAR(1024),album_id INTEGER,artist_id INTEGER );
CREATE TABLE statistics (id INTEGER PRIMARY KEY AUTOINCREMENT,url INTEGER,createdate INTEGER,accessdate INTEGER,score FLOAT,rating INTEGER DEFAULT 0,playcount INTEGER);
CREATE TABLE tracks (id INTEGER PRIMARY KEY AUTOINCREMENT,url INTEGER,artist INTEGER,album INTEGER,genre INTEGER,composer INTEGER,year INTEGER,title VARCHAR(255),comment TEXT,tracknumber INTEGER,discnumber INTEGER,bitrate INTEGER,length INTEGER,samplerate INTEGER,filesize INTEGER,filetype INTEGER,bpm FLOAT,createdate INTEGER,modifydate INTEGER);
CREATE TABLE urls (id INTEGER PRIMARY KEY AUTOINCREMENT,deviceid INTEGER,rpath VARCHAR(1024),directory INTEGER);
CREATE TABLE urls_labels(url INTEGER, label INTEGER);
CREATE TABLE years (id INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(255) NOT NULL);
CREATE INDEX albums_artist ON albums(artist);
CREATE INDEX albums_name ON albums(name);
CREATE UNIQUE INDEX albums_name_artist ON albums(name,artist);
CREATE INDEX amazon_date ON amazon(refetchdate);
CREATE UNIQUE INDEX artists_name ON artists(name);
CREATE UNIQUE INDEX composers_name ON composers(name);
CREATE INDEX devices_rshare ON devices( servername, sharename );
CREATE INDEX devices_type ON devices( type );
CREATE INDEX devices_uuid ON devices( uuid );
CREATE INDEX directories_deviceid ON directories(deviceid);
CREATE UNIQUE INDEX genres_name ON genres(name);
CREATE INDEX jamendo_albums_artist_id ON jamendo_albums(artist_id);
CREATE INDEX jamendo_albums_name ON jamendo_albums(name);
CREATE INDEX jamendo_artists_name ON jamendo_artists(name);
CREATE INDEX jamendo_genre_album_id ON jamendo_genre(album_id);
CREATE INDEX jamendo_genre_name ON jamendo_genre(name);
CREATE INDEX jamendo_tracks_album_id ON jamendo_tracks(album_id);
CREATE INDEX jamendo_tracks_artist_id ON jamendo_tracks(artist_id);
CREATE INDEX jamendo_tracks_id ON jamendo_tracks(id);
CREATE UNIQUE INDEX labels_label ON labels(label);
CREATE UNIQUE INDEX lyrics_url ON lyrics(url);
CREATE INDEX magnatune_albums_artist_id ON magnatune_albums(artist_id);
CREATE INDEX magnatune_albums_name ON magnatune_albums(name);
CREATE INDEX magnatune_artists_name ON magnatune_artists(name);
CREATE INDEX magnatune_genre_album_id ON magnatune_genre(album_id);
CREATE INDEX magnatune_genre_name ON magnatune_genre(name);
CREATE INDEX magnatune_tracks_album_id ON magnatune_tracks(album_id);
CREATE INDEX magnatune_tracks_artist_id ON magnatune_tracks(artist_id);
CREATE INDEX statistics_accessdate ON statistics(accessdate);
CREATE INDEX statistics_createdate ON statistics(createdate);
CREATE INDEX statistics_playcount ON statistics(playcount);
CREATE INDEX statistics_rating ON statistics(rating);
CREATE INDEX statistics_score ON statistics(score);
CREATE UNIQUE INDEX statistics_url ON statistics(url);
CREATE INDEX tracks_album ON tracks(album);
CREATE INDEX tracks_artist ON tracks(artist);
CREATE INDEX tracks_bitrate ON tracks(bitrate);
CREATE INDEX tracks_composer ON tracks(composer);
CREATE INDEX tracks_createdate ON tracks(createdate);
CREATE INDEX tracks_discnumber ON tracks(discnumber);
CREATE INDEX tracks_filesize ON tracks(filesize);
CREATE INDEX tracks_genre ON tracks(genre);
CREATE INDEX tracks_length ON tracks(length);
CREATE INDEX tracks_title ON tracks(title);
CREATE UNIQUE INDEX tracks_url ON tracks(url);
CREATE INDEX tracks_year ON tracks(year);
CREATE UNIQUE INDEX urls_id_rpath ON urls(deviceid, rpath);
CREATE INDEX urlslabels_label ON urls_labels(label);
CREATE INDEX urlslabels_url ON urls_labels(url);
CREATE UNIQUE INDEX years_name ON years(name);
sqlite>