root/branches/0.3/sql/setupdb-sqlite.sql

Revision 6, 9.1 kB (checked in by travis, 3 years ago)

Import

Line 
1 CREATE TABLE artists (
2    id         INTEGER PRIMARY KEY,
3    name       VARCHAR(128) NOT NULL UNIQUE,
4    date_added TIMESTAMP,
5    sync       BOOLEAN,
6    fuzzy_name VARCHAR(128) NOT NULL,
7    sort_name  VARCHAR(128) NOT NULL
8 );
9
10 CREATE TABLE albums (
11    id           INTEGER PRIMARY KEY,
12    name         VARCHAR(128) NOT NULL,
13    artist_id    INTEGER UNSIGNED NOT NULL REFERENCES artists(id),
14    year         YEAR(4),
15    date_added   TIMESTAMP,
16    sync         BOOLEAN,
17    fuzzy_name   VARCHAR(128) NOT NULL,
18    small_cover  VARCHAR(255) UNIQUE,
19    medium_cover VARCHAR(255) UNIQUE,
20    large_cover  VARCHAR(255) UNIQUE,
21    compilation  BOOLEAN NOT NULL
22 );
23
24 CREATE TABLE tracks (
25    id           INTEGER PRIMARY KEY,
26    name         VARCHAR(128) NOT NULL,
27    path         VARCHAR(255) NOT NULL UNIQUE,
28    artist_id    INTEGER UNSIGNED NOT NULL REFERENCES artists(id),
29    album_id     INTEGER UNSIGNED NOT NULL REFERENCES albums(id),
30    track_num    SMALLINT UNSIGNED,
31    track_total  SMALLINT UNSIGNED,
32    time         INTEGER NOT NULL,
33    date_added   DATE NOT NULL,
34    sync         BOOLEAN,
35    bitrate      SMALLINT UNSIGNED NOT NULL,
36    vbr          BOOLEAN,
37    sample_freq  MEDIUMINT UNSIGNED NOT NULL,
38    mode         VARCHAR(30),
39    audio_type   VARCHAR(3),
40    tag_version  VARCHAR(15),
41    play_count   INT UNSIGNED NOT NULL,
42    play_date    TIMESTAMP,
43    genre_id     TINYINT UNSIGNED REFERENCES genres(id),
44    file_size    INTEGER UNSIGNED NOT NULL,
45    year         YEAR(4),
46    fuzzy_name   VARCHAR(128) NOT NULL,
47    mod_time     INTEGER UNSIGNED NOT NULL
48 );
49
50 CREATE TABLE playlists (
51    id            INTEGER PRIMARY KEY,
52    name          VARCHAR(64) NOT NULL UNIQUE,
53    current_track INTEGER UNSIGNED NOT NULL
54 );
55 INSERT INTO playlists VALUES (0, "Playlist", 0);
56
57 CREATE TABLE playlist_tracks (
58    pid INTEGER UNSIGNED NOT NULL REFERENCES playlists(id),
59    tid INTEGER UNSIGNED NOT NULL REFERENCES tracks(id)
60 );
61
62 CREATE TABLE meta_data (
63    sync_timestamp TIMESTAMP NOT NULL UNIQUE,
64    archive_size   VARCHAR(20),
65    artist_count   INTEGER UNSIGNED NOT NULL DEFAULT 0,
66    album_count    INTEGER UNSIGNED NOT NULL DEFAULT 0,
67    track_count    INTEGER UNSIGNED NOT NULL DEFAULT 0,
68    total_time     INTEGER UNSIGNED NOT NULL DEFAULT 0
69 );
70
71 CREATE TABLE ui (
72    main_window_pos_x  INTEGER UNSIGNED,
73    main_window_pos_y  INTEGER UNSIGNED,
74    main_window_width  INTEGER UNSIGNED NOT NULL DEFAULT 640,
75    main_window_height INTEGER UNSIGNED NOT NULL DEFAULT 480
76 );
77 INSERT INTO ui VALUES (NULL, NULL, 640, 480);
78
79 /* Table for genre mapping.  Includes ID3 v1.1 genres (0 <= genre.id <= 79) as
80    well as WinAmp extenstions (80 <= genre.id <= 125) */
81 CREATE TABLE genres (
82    id   TINYINT UNSIGNED NOT NULL PRIMARY KEY,
83    name VARCHAR(30) NOT NULL default ''
84 );
85
86 INSERT INTO genres VALUES (0, "Blues");
87 INSERT INTO genres VALUES (1, "Classic Rock");
88 INSERT INTO genres VALUES (2, "Country");
89 INSERT INTO genres VALUES (3, "Dance");
90 INSERT INTO genres VALUES (4, "Disco");
91 INSERT INTO genres VALUES (5, "Funk");
92 INSERT INTO genres VALUES (6, "Grunge");
93 INSERT INTO genres VALUES (7, "Hip-Hop");
94 INSERT INTO genres VALUES (8, "Jazz");
95 INSERT INTO genres VALUES (9, "Metal");
96 INSERT INTO genres VALUES (10, "New Age");
97 INSERT INTO genres VALUES (11, "Oldies");
98 INSERT INTO genres VALUES (12, "Other");
99 INSERT INTO genres VALUES (13, "Pop");
100 INSERT INTO genres VALUES (14, "R&B");
101 INSERT INTO genres VALUES (15, "Rap");
102 INSERT INTO genres VALUES (16, "Reggae");
103 INSERT INTO genres VALUES (17, "Rock");
104 INSERT INTO genres VALUES (18, "Techno");
105 INSERT INTO genres VALUES (19, "Industrial");
106 INSERT INTO genres VALUES (20, "Alternative");
107 INSERT INTO genres VALUES (21, "Ska");
108 INSERT INTO genres VALUES (22, "Death Metal");
109 INSERT INTO genres VALUES (23, "Pranks");
110 INSERT INTO genres VALUES (24, "Soundtrack");
111 INSERT INTO genres VALUES (25, "Euro-Techno");
112 INSERT INTO genres VALUES (26, "Ambient");
113 INSERT INTO genres VALUES (27, "Trip-Hop");
114 INSERT INTO genres VALUES (28, "Vocal");
115 INSERT INTO genres VALUES (29, "Jazz+Funk");
116 INSERT INTO genres VALUES (30, "Fusion");
117 INSERT INTO genres VALUES (31, "Trance");
118 INSERT INTO genres VALUES (32, "Classical");
119 INSERT INTO genres VALUES (33, "Instrumental");
120 INSERT INTO genres VALUES (34, "Acid");
121 INSERT INTO genres VALUES (35, "House");
122 INSERT INTO genres VALUES (36, "Game");
123 INSERT INTO genres VALUES (37, "Sound Clip");
124 INSERT INTO genres VALUES (38, "Gospel");
125 INSERT INTO genres VALUES (39, "Noise");
126 INSERT INTO genres VALUES (40, "AlternRock");
127 INSERT INTO genres VALUES (41, "Bass");
128 INSERT INTO genres VALUES (42, "Soul");
129 INSERT INTO genres VALUES (43, "Punk");
130 INSERT INTO genres VALUES (44, "Space");
131 INSERT INTO genres VALUES (45, "Meditative");
132 INSERT INTO genres VALUES (46, "Instrumental Pop");
133 INSERT INTO genres VALUES (47, "Instrumental Rock");
134 INSERT INTO genres VALUES (48, "Ethnic");
135 INSERT INTO genres VALUES (49, "Gothic");
136 INSERT INTO genres VALUES (50, "Darkwave");
137 INSERT INTO genres VALUES (51, "Techno-Industrial");
138 INSERT INTO genres VALUES (52, "Electronic");
139 INSERT INTO genres VALUES (53, "Pop-Folk");
140 INSERT INTO genres VALUES (54, "Eurodance");
141 INSERT INTO genres VALUES (55, "Dream");
142 INSERT INTO genres VALUES (56, "Southern Rock");
143 INSERT INTO genres VALUES (57, "Comedy");
144 INSERT INTO genres VALUES (58, "Cult");
145 INSERT INTO genres VALUES (59, "Gangsta Rap");
146 INSERT INTO genres VALUES (60, "Top 40");
147 INSERT INTO genres VALUES (61, "Christian Rap");
148 INSERT INTO genres VALUES (62, "Pop/Funk");
149 INSERT INTO genres VALUES (63, "Jungle");
150 INSERT INTO genres VALUES (64, "Native American");
151 INSERT INTO genres VALUES (65, "Cabaret");
152 INSERT INTO genres VALUES (66, "New Wave");
153 INSERT INTO genres VALUES (67, "Psychadelic");
154 INSERT INTO genres VALUES (68, "Rave");
155 INSERT INTO genres VALUES (69, "Showtunes");
156 INSERT INTO genres VALUES (70, "Trailer");
157 INSERT INTO genres VALUES (71, "Lo-Fi");
158 INSERT INTO genres VALUES (72, "Tribal");
159 INSERT INTO genres VALUES (73, "Acid Punk");
160 INSERT INTO genres VALUES (74, "Acid Jazz");
161 INSERT INTO genres VALUES (75, "Polka");
162 INSERT INTO genres VALUES (76, "Retro");
163 INSERT INTO genres VALUES (77, "Musical");
164 INSERT INTO genres VALUES (78, "Rock & Roll");
165 INSERT INTO genres VALUES (79, "Hard Rock");
166 INSERT INTO genres VALUES (80, "Folk");
167 INSERT INTO genres VALUES (81, "Folk-Rock");
168 INSERT INTO genres VALUES (82, "National Folk");
169 INSERT INTO genres VALUES (83, "Swing");
170 INSERT INTO genres VALUES (84, "Fast Fusion");
171 INSERT INTO genres VALUES (85, "Bebob");
172 INSERT INTO genres VALUES (86, "Latin");
173 INSERT INTO genres VALUES (87, "Revival");
174 INSERT INTO genres VALUES (88, "Celtic");
175 INSERT INTO genres VALUES (89, "Bluegrass");
176 INSERT INTO genres VALUES (90, "Avantgarde");
177 INSERT INTO genres VALUES (91, "Gothic Rock");
178 INSERT INTO genres VALUES (92, "Progressive Rock");
179 INSERT INTO genres VALUES (93, "Psychedelic Rock");
180 INSERT INTO genres VALUES (94, "Symphonic Rock");
181 INSERT INTO genres VALUES (95, "Slow Rock");
182 INSERT INTO genres VALUES (96, "Big Band");
183 INSERT INTO genres VALUES (97, "Chorus");
184 INSERT INTO genres VALUES (98, "Easy Listening");
185 INSERT INTO genres VALUES (99, "Acoustic");
186 INSERT INTO genres VALUES (100, "Humour");
187 INSERT INTO genres VALUES (101, "Speech");
188 INSERT INTO genres VALUES (102, "Chanson");
189 INSERT INTO genres VALUES (103, "Opera");
190 INSERT INTO genres VALUES (104, "Chamber Music");
191 INSERT INTO genres VALUES (105, "Sonata");
192 INSERT INTO genres VALUES (106, "Symphony");
193 INSERT INTO genres VALUES (107, "Booty Bass");
194 INSERT INTO genres VALUES (108, "Primus");
195 INSERT INTO genres VALUES (109, "Porn Groove");
196 INSERT INTO genres VALUES (110, "Satire");
197 INSERT INTO genres VALUES (111, "Slow Jam");
198 INSERT INTO genres VALUES (112, "Club");
199 INSERT INTO genres VALUES (113, "Tango");
200 INSERT INTO genres VALUES (114, "Samba");
201 INSERT INTO genres VALUES (115, "Folklore");
202 INSERT INTO genres VALUES (116, "Ballad");
203 INSERT INTO genres VALUES (117, "Power Ballad");
204 INSERT INTO genres VALUES (118, "Rhythmic Soul");
205 INSERT INTO genres VALUES (119, "Freestyle");
206 INSERT INTO genres VALUES (120, "Duet");
207 INSERT INTO genres VALUES (121, "Punk Rock");
208 INSERT INTO genres VALUES (122, "Drum Solo");
209 INSERT INTO genres VALUES (123, "A capella");
210 INSERT INTO genres VALUES (124, "Euro-House");
211 INSERT INTO genres VALUES (125, "Dance Hall");
212 INSERT INTO genres VALUES (126, "Goa");
213 INSERT INTO genres VALUES (127, "Drum & Bass");
214 INSERT INTO genres VALUES (128, "Club-House");
215 INSERT INTO genres VALUES (129, "Hardcore");
216 INSERT INTO genres VALUES (130, "Terror");
217 INSERT INTO genres VALUES (131, "Indie");
218 INSERT INTO genres VALUES (132, "BritPop");
219 INSERT INTO genres VALUES (133, "Negerpunk");
220 INSERT INTO genres VALUES (134, "Polsk Punk");
221 INSERT INTO genres VALUES (135, "Beat");
222 INSERT INTO genres VALUES (136, "Christian Gangsta Rap");
223 INSERT INTO genres VALUES (137, "Heavy Metal");
224 INSERT INTO genres VALUES (138, "Black Metal");
225 INSERT INTO genres VALUES (139, "Crossover");
226 INSERT INTO genres VALUES (140, "Contemporary Christian");
227 INSERT INTO genres VALUES (141, "Christian Rock");
228 INSERT INTO genres VALUES (142, "Merengue");
229 INSERT INTO genres VALUES (143, "Salsa");
230 INSERT INTO genres VALUES (144, "Thrash Metal");
231 INSERT INTO genres VALUES (145, "Anime");
232 INSERT INTO genres VALUES (146, "JPop");
233 INSERT INTO genres VALUES (147, "Synthpop");
234 INSERT INTO genres VALUES (255, "Unknown");
Note: See TracBrowser for help on using the browser.