sqlite.sql
[chaz/p5-DBIx-Class-ResultSet-RecursiveUpdate] / t / lib / sqlite.sql
1 --
2 -- Created by SQL::Translator::Producer::SQLite
3 -- Created on Sun Nov 23 13:27:13 2008
4 --
5 BEGIN TRANSACTION;
6
7
8 --
9 -- Table: artist
10 --
11 CREATE TABLE artist (
12 artistid INTEGER PRIMARY KEY NOT NULL,
13 name varchar(100),
14 rank integer NOT NULL DEFAULT '13',
15 charfield char(10)
16 );
17
18
19 --
20 -- Table: artist_undirected_map
21 --
22 CREATE TABLE artist_undirected_map (
23 id1 integer NOT NULL,
24 id2 integer NOT NULL,
25 PRIMARY KEY (id1, id2)
26 );
27
28 CREATE INDEX artist_undirected_map_idx_id1_ ON artist_undirected_map (id1);
29 CREATE INDEX artist_undirected_map_idx_id2_ ON artist_undirected_map (id2);
30
31 --
32 -- Table: cd_artwork
33 --
34 CREATE TABLE cd_artwork (
35 cd_id INTEGER PRIMARY KEY NOT NULL
36 );
37
38 CREATE INDEX cd_artwork_idx_cd_id_cd_artwor ON cd_artwork (cd_id);
39
40 --
41 -- Table: artwork_to_artist
42 --
43 CREATE TABLE artwork_to_artist (
44 artwork_cd_id integer NOT NULL,
45 artist_id integer NOT NULL,
46 PRIMARY KEY (artwork_cd_id, artist_id)
47 );
48
49 CREATE INDEX artwork_to_artist_idx_artist_id_artwork_to_arti ON artwork_to_artist (artist_id);
50 CREATE INDEX artwork_to_artist_idx_artwork_cd_id_artwork_to_ ON artwork_to_artist (artwork_cd_id);
51
52 --
53 -- Table: bookmark
54 --
55 CREATE TABLE bookmark (
56 id INTEGER PRIMARY KEY NOT NULL,
57 link integer NOT NULL
58 );
59
60 CREATE INDEX bookmark_idx_link_bookmark ON bookmark (link);
61
62 --
63 -- Table: books
64 --
65 CREATE TABLE books (
66 id INTEGER PRIMARY KEY NOT NULL,
67 source varchar(100) NOT NULL,
68 owner integer NOT NULL,
69 title varchar(100) NOT NULL,
70 price integer
71 );
72
73
74 --
75 -- Table: cd
76 --
77 CREATE TABLE cd (
78 cdid INTEGER PRIMARY KEY NOT NULL,
79 artist integer NOT NULL,
80 title varchar(100) NOT NULL,
81 year varchar(100) NOT NULL,
82 genreid integer,
83 single_track integer
84 );
85
86 CREATE INDEX cd_idx_artist_cd ON cd (artist);
87 CREATE INDEX cd_idx_genreid_cd ON cd (genreid);
88 CREATE INDEX cd_idx_single_track_cd ON cd (single_track);
89 CREATE UNIQUE INDEX cd_artist_title_cd ON cd (artist, title);
90
91 --
92 -- Table: cd_to_producer
93 --
94 CREATE TABLE cd_to_producer (
95 cd integer NOT NULL,
96 producer integer NOT NULL,
97 PRIMARY KEY (cd, producer)
98 );
99
100 CREATE INDEX cd_to_producer_idx_cd_cd_to_pr ON cd_to_producer (cd);
101 CREATE INDEX cd_to_producer_idx_producer_cd ON cd_to_producer (producer);
102
103 --
104 -- Table: collection
105 --
106 CREATE TABLE collection (
107 collectionid INTEGER PRIMARY KEY NOT NULL,
108 name varchar(100) NOT NULL
109 );
110
111
112 --
113 -- Table: collection_object
114 --
115 CREATE TABLE collection_object (
116 collection integer NOT NULL,
117 object integer NOT NULL,
118 PRIMARY KEY (collection, object)
119 );
120
121 CREATE INDEX collection_object_idx_collection_collection_obj ON collection_object (collection);
122 CREATE INDEX collection_object_idx_object_c ON collection_object (object);
123
124 --
125 -- Table: employee
126 --
127 CREATE TABLE employee (
128 employee_id INTEGER PRIMARY KEY NOT NULL,
129 position integer NOT NULL,
130 group_id integer,
131 group_id_2 integer,
132 name varchar(100)
133 );
134
135
136 --
137 -- Table: event
138 --
139 CREATE TABLE event (
140 id INTEGER PRIMARY KEY NOT NULL,
141 starts_at datetime NOT NULL,
142 created_on timestamp NOT NULL,
143 varchar_date varchar(20),
144 varchar_datetime varchar(20),
145 skip_inflation datetime
146 );
147
148
149 --
150 -- Table: file_columns
151 --
152 CREATE TABLE file_columns (
153 id INTEGER PRIMARY KEY NOT NULL,
154 file varchar(255) NOT NULL
155 );
156
157
158 --
159 -- Table: forceforeign
160 --
161 CREATE TABLE forceforeign (
162 artist INTEGER PRIMARY KEY NOT NULL,
163 cd integer NOT NULL
164 );
165
166 CREATE INDEX forceforeign_idx_artist_forcef ON forceforeign (artist);
167
168 --
169 -- Table: fourkeys
170 --
171 CREATE TABLE fourkeys (
172 foo integer NOT NULL,
173 bar integer NOT NULL,
174 hello integer NOT NULL,
175 goodbye integer NOT NULL,
176 sensors character NOT NULL,
177 PRIMARY KEY (foo, bar, hello, goodbye)
178 );
179
180
181 --
182 -- Table: fourkeys_to_twokeys
183 --
184 CREATE TABLE fourkeys_to_twokeys (
185 f_foo integer NOT NULL,
186 f_bar integer NOT NULL,
187 f_hello integer NOT NULL,
188 f_goodbye integer NOT NULL,
189 t_artist integer NOT NULL,
190 t_cd integer NOT NULL,
191 autopilot character NOT NULL,
192 PRIMARY KEY (f_foo, f_bar, f_hello, f_goodbye, t_artist, t_cd)
193 );
194
195 CREATE INDEX fourkeys_to_twokeys_idx_f_foo_f_bar_f_hello_f_goodbye_ ON fourkeys_to_twokeys (f_foo, f_bar, f_hello, f_goodbye);
196 CREATE INDEX fourkeys_to_twokeys_idx_t_artist_t_cd_fourkeys_to ON fourkeys_to_twokeys (t_artist, t_cd);
197
198 --
199 -- Table: genre
200 --
201 CREATE TABLE genre (
202 genreid INTEGER PRIMARY KEY NOT NULL,
203 name varchar(100) NOT NULL
204 );
205
206 CREATE UNIQUE INDEX genre_name_genre ON genre (name);
207
208 --
209 -- Table: images
210 --
211 CREATE TABLE images (
212 id INTEGER PRIMARY KEY NOT NULL,
213 artwork_id integer NOT NULL,
214 name varchar(100) NOT NULL,
215 data blob
216 );
217
218 CREATE INDEX images_idx_artwork_id_images ON images (artwork_id);
219
220 --
221 -- Table: liner_notes
222 --
223 CREATE TABLE liner_notes (
224 liner_id INTEGER PRIMARY KEY NOT NULL,
225 notes varchar(100) NOT NULL
226 );
227
228 CREATE INDEX liner_notes_idx_liner_id_liner ON liner_notes (liner_id);
229
230 --
231 -- Table: link
232 --
233 CREATE TABLE link (
234 id INTEGER PRIMARY KEY NOT NULL,
235 url varchar(100),
236 title varchar(100)
237 );
238
239
240 --
241 -- Table: lyric_versions
242 --
243 CREATE TABLE lyric_versions (
244 id INTEGER PRIMARY KEY NOT NULL,
245 lyric_id integer NOT NULL,
246 text varchar(100) NOT NULL
247 );
248
249 CREATE INDEX lyric_versions_idx_lyric_id_ly ON lyric_versions (lyric_id);
250
251 --
252 -- Table: lyrics
253 --
254 CREATE TABLE lyrics (
255 lyric_id INTEGER PRIMARY KEY NOT NULL,
256 track_id integer NOT NULL
257 );
258
259 CREATE INDEX lyrics_idx_track_id_lyrics ON lyrics (track_id);
260
261 --
262 -- Table: noprimarykey
263 --
264 CREATE TABLE noprimarykey (
265 foo integer NOT NULL,
266 bar integer NOT NULL,
267 baz integer NOT NULL
268 );
269
270 CREATE UNIQUE INDEX foo_bar_noprimarykey ON noprimarykey (foo, bar);
271
272 --
273 -- Table: onekey
274 --
275 CREATE TABLE onekey (
276 id INTEGER PRIMARY KEY NOT NULL,
277 artist integer NOT NULL,
278 cd integer NOT NULL
279 );
280
281
282 --
283 -- Table: owners
284 --
285 CREATE TABLE owners (
286 ownerid INTEGER PRIMARY KEY NOT NULL,
287 name varchar(100) NOT NULL
288 );
289
290
291 --
292 -- Table: producer
293 --
294 CREATE TABLE producer (
295 producerid INTEGER PRIMARY KEY NOT NULL,
296 name varchar(100) NOT NULL
297 );
298
299 CREATE UNIQUE INDEX prod_name_producer ON producer (name);
300
301 --
302 -- Table: self_ref
303 --
304 CREATE TABLE self_ref (
305 id INTEGER PRIMARY KEY NOT NULL,
306 name varchar(100) NOT NULL
307 );
308
309
310 --
311 -- Table: self_ref_alias
312 --
313 CREATE TABLE self_ref_alias (
314 self_ref integer NOT NULL,
315 alias integer NOT NULL,
316 PRIMARY KEY (self_ref, alias)
317 );
318
319 CREATE INDEX self_ref_alias_idx_alias_self_ ON self_ref_alias (alias);
320 CREATE INDEX self_ref_alias_idx_self_ref_se ON self_ref_alias (self_ref);
321
322 --
323 -- Table: sequence_test
324 --
325 CREATE TABLE sequence_test (
326 pkid1 integer NOT NULL,
327 pkid2 integer NOT NULL,
328 nonpkid integer NOT NULL,
329 name varchar(100),
330 PRIMARY KEY (pkid1, pkid2)
331 );
332
333
334 --
335 -- Table: serialized
336 --
337 CREATE TABLE serialized (
338 id INTEGER PRIMARY KEY NOT NULL,
339 serialized text NOT NULL
340 );
341
342
343 --
344 -- Table: tags
345 --
346 CREATE TABLE tags (
347 tagid INTEGER PRIMARY KEY NOT NULL,
348 cd integer NOT NULL,
349 tag varchar(100) NOT NULL
350 );
351
352 CREATE INDEX tags_idx_cd_tags ON tags (cd);
353
354 --
355 -- Table: track
356 --
357 CREATE TABLE track (
358 trackid INTEGER PRIMARY KEY NOT NULL,
359 cd integer NOT NULL,
360 position integer NOT NULL,
361 title varchar(100) NOT NULL,
362 last_updated_on datetime
363 );
364
365 CREATE INDEX track_idx_cd_track ON track (cd);
366 CREATE UNIQUE INDEX track_cd_position_track ON track (cd, position);
367 CREATE UNIQUE INDEX track_cd_title_track ON track (cd, title);
368
369 --
370 -- Table: treelike
371 --
372 CREATE TABLE treelike (
373 id INTEGER PRIMARY KEY NOT NULL,
374 parent integer,
375 name varchar(100) NOT NULL
376 );
377
378 CREATE INDEX treelike_idx_parent_treelike ON treelike (parent);
379
380 --
381 -- Table: twokeytreelike
382 --
383 CREATE TABLE twokeytreelike (
384 id1 integer NOT NULL,
385 id2 integer NOT NULL,
386 parent1 integer NOT NULL,
387 parent2 integer NOT NULL,
388 name varchar(100) NOT NULL,
389 PRIMARY KEY (id1, id2)
390 );
391
392 CREATE INDEX twokeytreelike_idx_parent1_parent2_twokeytre ON twokeytreelike (parent1, parent2);
393 CREATE UNIQUE INDEX tktlnameunique_twokeytreelike ON twokeytreelike (name);
394
395 --
396 -- Table: twokeys
397 --
398 CREATE TABLE twokeys (
399 artist integer NOT NULL,
400 cd integer NOT NULL,
401 PRIMARY KEY (artist, cd)
402 );
403
404 CREATE INDEX twokeys_idx_artist_twokeys ON twokeys (artist);
405
406 --
407 -- Table: typed_object
408 --
409 CREATE TABLE typed_object (
410 objectid INTEGER PRIMARY KEY NOT NULL,
411 type varchar(100) NOT NULL,
412 value varchar(100) NOT NULL
413 );
414
415
416 COMMIT;
This page took 0.049575 seconds and 4 git commands to generate.