| 18 |
- |
1 |
#
|
|
|
2 |
# Releases
|
|
|
3 |
#
|
|
|
4 |
DROP TABLE IF EXISTS `releases`;
|
|
|
5 |
CREATE TABLE `releases` (
|
|
|
6 |
`id` INT UNSIGNED NOT NULL,
|
|
|
7 |
`mid` INT UNSIGNED,
|
|
|
8 |
`title` VARCHAR(1024),
|
|
|
9 |
`country` VARCHAR(64),
|
|
|
10 |
`released` YEAR,
|
|
|
11 |
`barcode` VARCHAR(16),
|
|
|
12 |
`quantity` TINYINT UNSIGNED,
|
|
|
13 |
`format` VARCHAR(24),
|
|
|
14 |
`formatExt` VARCHAR(768),
|
|
|
15 |
`formats` VARCHAR(1024),
|
|
|
16 |
`genres` VARCHAR(1024),
|
|
|
17 |
`styles` VARCHAR(1024),
|
|
|
18 |
PRIMARY KEY (`id`)
|
|
|
19 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
|
20 |
|
|
|
21 |
LOAD DATA LOCAL INFILE 'out/releases.load' REPLACE
|
|
|
22 |
INTO TABLE releases
|
|
|
23 |
FIELDS TERMINATED BY ','
|
|
|
24 |
OPTIONALLY ENCLOSED BY '"'
|
|
|
25 |
ESCAPED BY ''
|
|
|
26 |
LINES TERMINATED BY '\n';
|
|
|
27 |
|
|
|
28 |
CREATE INDEX releasesMId on releases(mid);
|
|
|
29 |
CREATE INDEX releasesTitle on releases(title);
|
|
|
30 |
CREATE INDEX releasesBarcode on releases(barcode);
|
|
|
31 |
CREATE INDEX releasesFormat on releases(format);
|
|
|
32 |
|
|
|
33 |
#
|
|
|
34 |
# Release Artists
|
|
|
35 |
#
|
|
|
36 |
DROP TABLE IF EXISTS `releaseArtists`;
|
|
|
37 |
CREATE TABLE `releaseArtists` (
|
|
|
38 |
`id` INT UNSIGNED NOT NULL,
|
|
|
39 |
`aid` INT UNSIGNED NOT NULL
|
|
|
40 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
|
41 |
|
|
|
42 |
LOAD DATA LOCAL INFILE 'out/releaseArtists.load' REPLACE
|
|
|
43 |
INTO TABLE releaseArtists
|
|
|
44 |
FIELDS TERMINATED BY ','
|
|
|
45 |
OPTIONALLY ENCLOSED BY '"'
|
|
|
46 |
ESCAPED BY ''
|
|
|
47 |
LINES TERMINATED BY '\n';
|
|
|
48 |
|
|
|
49 |
CREATE INDEX releaseArtistsId on releaseArtists(id);
|
|
|
50 |
CREATE INDEX releaseArtistsAId on releaseArtists(aid);
|
|
|
51 |
|
|
|
52 |
#
|
|
|
53 |
# Release Labels
|
|
|
54 |
#
|
|
|
55 |
DROP TABLE IF EXISTS `releaseLabels`;
|
|
|
56 |
CREATE TABLE `releaseLabels` (
|
|
|
57 |
`id` INT UNSIGNED NOT NULL,
|
|
|
58 |
`lid` INT UNSIGNED NOT NULL,
|
|
|
59 |
`catno` VARCHAR(128)
|
|
|
60 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
|
61 |
|
|
|
62 |
LOAD DATA LOCAL INFILE 'out/releaseLabels.load' REPLACE
|
|
|
63 |
INTO TABLE releaseLabels
|
|
|
64 |
FIELDS TERMINATED BY ','
|
|
|
65 |
OPTIONALLY ENCLOSED BY '"'
|
|
|
66 |
ESCAPED BY ''
|
|
|
67 |
LINES TERMINATED BY '\n';
|
|
|
68 |
|
|
|
69 |
CREATE INDEX releaseLabelsId on releaseLabels(id);
|
|
|
70 |
CREATE INDEX releaseLabelsLId on releaseLabels(lid);
|
|
|
71 |
CREATE INDEX releaseLabelsCatno on releaseLabels(catno);
|
|
|
72 |
|
|
|
73 |
#
|
|
|
74 |
# Release Tracks
|
|
|
75 |
#
|
|
|
76 |
DROP TABLE IF EXISTS `releaseTracks`;
|
|
|
77 |
CREATE TABLE `releaseTracks` (
|
|
|
78 |
`id` INT UNSIGNED NOT NULL,
|
|
|
79 |
`track` blob,
|
|
|
80 |
PRIMARY KEY (`id`)
|
|
|
81 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
|
82 |
|
|
|
83 |
LOAD DATA LOCAL INFILE 'out/releaseTracks.load' REPLACE
|
|
|
84 |
INTO TABLE releaseTracks
|
|
|
85 |
FIELDS TERMINATED BY ','
|
|
|
86 |
OPTIONALLY ENCLOSED BY '"'
|
|
|
87 |
ESCAPED BY ''
|
|
|
88 |
LINES TERMINATED BY '\n'
|
|
|
89 |
(id, @track_comp_data)
|
|
|
90 |
SET track=COMPRESS(@track_comp_data);
|
|
|
91 |
|
|
|
92 |
|
|
|
93 |
|
|
|
94 |
|
|
|
95 |
|
|
|
96 |
|
|
|
97 |
#SELECT id, name, LENGTH(name) AS mlen FROM releaseVariations ORDER BY mlen DESC LIMIT 1;
|
|
|
98 |
|