script.sql 2.81 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35
CREATE TABLE ARTISTS
(
artist   TEXT  ,
artwork TEXT ,
wiki    TEXT,
PRIMARY KEY(artist)
) ;

CREATE TABLE ALBUMS
(
album   TEXT ,
artist  TEXT,
artwork TEXT,
wiki    TEXT,
PRIMARY KEY(album, artist),
FOREIGN KEY(artist) REFERENCES artists(artist)
) ;

CREATE TABLE TAGS
(
tag TEXT NOT NULL,
context TEXT NOT NULL,

PRIMARY KEY(tag, context)
) ;

CREATE TABLE MOODS
(
mood TEXT PRIMARY KEY
) ;


CREATE TABLE PLAYLISTS
(
playlist       TEXT PRIMARY KEY ,
36
adddate DATE NOT NULL
37 38 39 40 41 42 43 44
) ;

CREATE TABLE SOURCES_TYPES
(
id   INTEGER PRIMARY KEY ,
name TEXT NOT NULL
) ;

45 46
CREATE TABLE FOLDERS
(
Camilo Higuita's avatar
Camilo Higuita committed
47
url  TEXT PRIMARY KEY,
48
adddate DATE NOT NULL
49 50
) ;

51 52
CREATE TABLE SOURCES
(
53
url TEXT PRIMARY KEY ,
54 55
sourcetype INTEGER NOT NULL,
FOREIGN KEY(sourcetype) REFERENCES SOURCES_TYPES(id)
56 57 58 59 60
) ;

CREATE TABLE TRACKS
(
url TEXT ,
61
source TEXT  ,
62 63 64 65 66 67
track   INTEGER ,
title   TEXT NOT NULL,
artist  TEXT NOT NULL,
album    TEXT NOT NULL,
duration    INTEGER  ,
comment     TEXT,
68 69 70 71 72
count      INTEGER  ,
fav    INTEGER NOT NULL,
rate       INTEGER NOT NULL,
releasedate DATE ,
adddate     DATE NOT NULL,
73 74
lyrics     TEXT NOT NULL,
genre      TEXT,
75
color        TEXT,
76 77
wiki    TEXT NOT NULL,
PRIMARY KEY (url),
78
FOREIGN KEY(source) REFERENCES SOURCES(url),
79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138
FOREIGN KEY(album, artist) REFERENCES albums(album, artist)
) ;


CREATE TABLE TRACKS_MOODS
(
mood  TEXT NOT NULL ,
url TEXT NOT NULL ,
FOREIGN KEY(mood) REFERENCES MOODS(mood),
FOREIGN KEY(url) REFERENCES TRACKS(url)

) ;

CREATE TABLE TRACKS_TAGS
(
tag  TEXT NOT NULL ,
context TEXT NOT NULL ,
url TEXT NOT NULL ,
PRIMARY KEY (tag, context, url),
FOREIGN KEY(tag, context) REFERENCES TAGS(tag, context),
FOREIGN KEY(url) REFERENCES TRACKS(url)

) ;

CREATE TABLE ARTISTS_TAGS
(
tag  TEXT NOT NULL ,
context TEXT NOT NULL,
artist TEXT NOT NULL ,
PRIMARY KEY (tag, context, artist),
FOREIGN KEY(tag, context) REFERENCES TAGS(tag, context),
FOREIGN KEY(artist) REFERENCES ARTISTS(artist)

) ;

CREATE TABLE ALBUMS_TAGS
(
tag  TEXT NOT NULL ,
context TEXT NOT NULL,
album TEXT NOT NULL ,
artist TEXT NOT NULL,
PRIMARY KEY (tag, context, album, artist),
FOREIGN KEY(tag, context) REFERENCES TAGS(tag, context),
FOREIGN KEY(album, artist) REFERENCES ALBUMS(album, artist)
) ;

CREATE TABLE PLAYLISTS_MOODS
(
playlist  TEXT NOT NULL ,
mood TEXT NOT NULL ,
PRIMARY KEY (playlist, mood),
FOREIGN KEY(playlist) REFERENCES PLAYLISTS(playlist),
FOREIGN KEY(mood) REFERENCES MOODS(mood)

) ;

CREATE TABLE TRACKS_PLAYLISTS
(
playlist TEXT NOT NULL ,
url      TEXT NOT NULL ,
139
adddate DATE NOT NULL,
140 141 142 143 144 145 146 147 148
PRIMARY KEY (playlist, url),
FOREIGN KEY(playlist) REFERENCES PLAYLISTS(playlist),
FOREIGN KEY(url) REFERENCES TRACKS(url)
) ;


CREATE TABLE LOG
(
id INTEGER NOT NULL,
149
adddate DATE NOT NULL,
150 151 152 153 154 155 156 157 158

PRIMARY KEY(id)
);

--First insertions

INSERT INTO SOURCES_TYPES VALUES (1,"LOCAL");
INSERT INTO SOURCES_TYPES VALUES (2,"ONLINE");
INSERT INTO SOURCES_TYPES VALUES (3,"DEVICE");