script.sql 2.81 KB
Newer Older
Camilo Higuita's avatar
Camilo Higuita committed
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
Camilo Higuita's avatar
Camilo Higuita committed
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
fixes  
Camilo Higuita committed
47
url  TEXT PRIMARY KEY,
48
adddate DATE NOT NULL
49 50
) ;

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

CREATE TABLE TRACKS
(
url TEXT ,
61
source TEXT  ,
Camilo Higuita's avatar
Camilo Higuita committed
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,
Camilo Higuita's avatar
Camilo Higuita committed
73 74
lyrics     TEXT NOT NULL,
genre      TEXT,
75
color        TEXT,
Camilo Higuita's avatar
Camilo Higuita committed
76 77
wiki    TEXT NOT NULL,
PRIMARY KEY (url),
78
FOREIGN KEY(source) REFERENCES SOURCES(url),
Camilo Higuita's avatar
Camilo Higuita committed
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,
Camilo Higuita's avatar
Camilo Higuita committed
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,
Camilo Higuita's avatar
Camilo Higuita committed
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");