Subversion Repositories cheapmusic

#
# Releases
#
DROP TABLE IF EXISTS `releases`;
CREATE TABLE `releases` (
  `id` INT UNSIGNED NOT NULL,
  `mid` INT UNSIGNED,
  `title` VARCHAR(1024),
  `country` VARCHAR(64),
  `released` YEAR,
  `barcode` VARCHAR(16),
  `quantity` TINYINT UNSIGNED,
  `format` VARCHAR(24),
  `formatExt` VARCHAR(768),
  `formats` VARCHAR(1024),
  `genres` VARCHAR(1024),
  `styles` VARCHAR(1024),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOAD DATA LOCAL INFILE 'out/releases.load' REPLACE
INTO TABLE releases
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ''
LINES TERMINATED BY '\n';

CREATE INDEX releasesMId on releases(mid);
CREATE INDEX releasesTitle on releases(title);
CREATE INDEX releasesBarcode on releases(barcode);
CREATE INDEX releasesFormat on releases(format);

#
# Release Artists
#
DROP TABLE IF EXISTS `releaseArtists`;
CREATE TABLE `releaseArtists` (
  `id` INT UNSIGNED NOT NULL,
  `aid` INT UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOAD DATA LOCAL INFILE 'out/releaseArtists.load' REPLACE
INTO TABLE releaseArtists
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ''
LINES TERMINATED BY '\n';

CREATE INDEX releaseArtistsId on releaseArtists(id);
CREATE INDEX releaseArtistsAId on releaseArtists(aid);

#
# Release Labels
#
DROP TABLE IF EXISTS `releaseLabels`;
CREATE TABLE `releaseLabels` (
  `id` INT UNSIGNED NOT NULL,
  `lid` INT UNSIGNED NOT NULL,
  `catno` VARCHAR(128)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOAD DATA LOCAL INFILE 'out/releaseLabels.load' REPLACE
INTO TABLE releaseLabels
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ''
LINES TERMINATED BY '\n';

CREATE INDEX releaseLabelsId on releaseLabels(id);
CREATE INDEX releaseLabelsLId on releaseLabels(lid);
CREATE INDEX releaseLabelsCatno on releaseLabels(catno);

#
# Release Tracks
#
DROP TABLE IF EXISTS `releaseTracks`;
CREATE TABLE `releaseTracks` (
  `id` INT UNSIGNED NOT NULL,
  `track` blob,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOAD DATA LOCAL INFILE 'out/releaseTracks.load' REPLACE
INTO TABLE releaseTracks
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ''
LINES TERMINATED BY '\n'
(id, @track_comp_data)
SET track=COMPRESS(@track_comp_data);






#SELECT id, name, LENGTH(name) AS mlen FROM releaseVariations ORDER BY mlen DESC LIMIT 1;