Subversion Repositories cheapmusic

#
# Artists
#
DROP TABLE IF EXISTS `artists`;
CREATE TABLE `artists` (
  `id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(1024),
  `realname` VARCHAR(1024),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

CREATE INDEX artistsName on artists(name);

#
# Artist Urls
#
DROP TABLE IF EXISTS `artistUrls`;
CREATE TABLE `artistUrls` (
  `id` INT UNSIGNED NOT NULL,
  `url` VARCHAR(1024)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

CREATE INDEX artistUrlsId on artistUrls(id);

#
# Artist Groups
#
DROP TABLE IF EXISTS `artistGroups`;
CREATE TABLE `artistGroups` (
  `id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(1024)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

CREATE INDEX artistGroupsId on artistGroups(id);
CREATE INDEX artistGroupsName on artistGroups(name);

#
# Artist (Group) Members
#
DROP TABLE IF EXISTS `artistMembers`;
CREATE TABLE `artistMembers` (
  `id` INT UNSIGNED NOT NULL,
  `mid` INT UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

CREATE INDEX artistMembersId on artistMembers(id);
CREATE INDEX artistMembersMId on artistMembers(mid);

#
# Artist Aliases
#
DROP TABLE IF EXISTS `artistAliases`;
CREATE TABLE `artistAliases` (
  `id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(1024)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

CREATE INDEX artistAliasesId on artistAliases(id);
CREATE INDEX artistAliasesName on artistAliases(name);

#
# Artist Name Variations
#
DROP TABLE IF EXISTS `artistVariations`;
CREATE TABLE `artistVariations` (
  `id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(1024)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

CREATE INDEX artistVariationsId on artistVariations(id);
CREATE INDEX artistVariationsName on artistVariations(name);

#
# Artist Profiles
#
DROP TABLE IF EXISTS `artistProfiles`;
CREATE TABLE `artistProfiles` (
  `id` INT UNSIGNED NOT NULL,
  `profile` blob,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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






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