script.sql 2.78 KB
Newer Older
Camilo Higuita's avatar
Camilo Higuita committed
1 2 3 4 5 6
CREATE TABLE ARTISTS
(
artist   TEXT  ,
artwork TEXT ,
wiki    TEXT,
PRIMARY KEY(artist)
7
);
Camilo Higuita's avatar
Camilo Higuita committed
8 9 10 11 12 13 14 15 16

CREATE TABLE ALBUMS
(
album   TEXT ,
artist  TEXT,
artwork TEXT,
wiki    TEXT,
PRIMARY KEY(album, artist),
FOREIGN KEY(artist) REFERENCES artists(artist)
17
);
Camilo Higuita's avatar
Camilo Higuita committed
18 19 20 21 22 23 24

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

PRIMARY KEY(tag, context)
25
);
Camilo Higuita's avatar
Camilo Higuita committed
26 27 28 29

CREATE TABLE MOODS
(
mood TEXT PRIMARY KEY
30
);
Camilo Higuita's avatar
Camilo Higuita committed
31 32 33

CREATE TABLE PLAYLISTS
(
34
playlist TEXT PRIMARY KEY,
35
adddate DATE NOT NULL
36
);
Camilo Higuita's avatar
Camilo Higuita committed
37 38 39

CREATE TABLE SOURCES_TYPES
(
40
id INTEGER PRIMARY KEY,
Camilo Higuita's avatar
Camilo Higuita committed
41
name TEXT NOT NULL
42
);
Camilo Higuita's avatar
Camilo Higuita committed
43

44 45
CREATE TABLE FOLDERS
(
46
url TEXT PRIMARY KEY,
47
adddate DATE NOT NULL
48
);
49

Camilo Higuita's avatar
Camilo Higuita committed
50 51
CREATE TABLE SOURCES
(
52
url TEXT PRIMARY KEY ,
53 54
sourcetype INTEGER NOT NULL,
FOREIGN KEY(sourcetype) REFERENCES SOURCES_TYPES(id)
55
);
Camilo Higuita's avatar
Camilo Higuita committed
56 57 58 59

CREATE TABLE TRACKS
(
url TEXT ,
60
source TEXT  ,
Camilo Higuita's avatar
Camilo Higuita committed
61 62 63 64 65 66
track   INTEGER ,
title   TEXT NOT NULL,
artist  TEXT NOT NULL,
album    TEXT NOT NULL,
duration    INTEGER  ,
comment     TEXT,
67 68 69 70 71
count      INTEGER  ,
fav    INTEGER NOT NULL,
rate       INTEGER NOT NULL,
releasedate DATE ,
adddate     DATE NOT NULL,
Camilo Higuita's avatar
Camilo Higuita committed
72 73
lyrics     TEXT NOT NULL,
genre      TEXT,
74
color        TEXT,
Camilo Higuita's avatar
Camilo Higuita committed
75 76
wiki    TEXT NOT NULL,
PRIMARY KEY (url),
77
FOREIGN KEY(source) REFERENCES SOURCES(url),
Camilo Higuita's avatar
Camilo Higuita committed
78
FOREIGN KEY(album, artist) REFERENCES albums(album, artist)
79
);
Camilo Higuita's avatar
Camilo Higuita committed
80 81 82 83 84 85 86

CREATE TABLE TRACKS_MOODS
(
mood  TEXT NOT NULL ,
url TEXT NOT NULL ,
FOREIGN KEY(mood) REFERENCES MOODS(mood),
FOREIGN KEY(url) REFERENCES TRACKS(url)
87
);
Camilo Higuita's avatar
Camilo Higuita committed
88 89 90 91 92 93 94 95 96

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)
97
);
Camilo Higuita's avatar
Camilo Higuita committed
98 99 100 101 102 103 104 105 106

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)
107
);
Camilo Higuita's avatar
Camilo Higuita committed
108 109 110 111 112 113 114 115 116 117

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)
118
);
Camilo Higuita's avatar
Camilo Higuita committed
119 120 121 122 123 124 125 126

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)
127
);
Camilo Higuita's avatar
Camilo Higuita committed
128 129 130 131 132

CREATE TABLE TRACKS_PLAYLISTS
(
playlist TEXT NOT NULL ,
url      TEXT NOT NULL ,
133
adddate DATE NOT NULL,
Camilo Higuita's avatar
Camilo Higuita committed
134 135 136
PRIMARY KEY (playlist, url),
FOREIGN KEY(playlist) REFERENCES PLAYLISTS(playlist),
FOREIGN KEY(url) REFERENCES TRACKS(url)
137
);
Camilo Higuita's avatar
Camilo Higuita committed
138 139 140 141 142


CREATE TABLE LOG
(
id INTEGER NOT NULL,
143
adddate DATE NOT NULL,
Camilo Higuita's avatar
Camilo Higuita committed
144 145 146 147 148 149 150 151
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");