# MySQL Navigator Xport
# Database: mjuk
# mjuk@localhost

# CREATE DATABASE mjuk;
# USE mjuk;


#
# Table structure for table 'DatabaseVersion'
#
CREATE TABLE `MjukDatabaseVersion` (
  `version_mysql` int(10) unsigned NOT NULL,
  `version_postgress` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=ucs2;

#
# Table structure for table 'Album'
#
CREATE TABLE `Album` (
  `albumId` int(10) unsigned NOT NULL auto_increment,
  `name` tinytext NOT NULL,
  `copyrightYear` year,
  `releaseDate` date,
  `releaseDatePrecision` enum("Y","YM","YMD"),

  PRIMARY KEY  (`albumId`)
) ENGINE=InnoDB DEFAULT CHARSET=ucs2;

#
# Table structure for table 'Genre'
#

CREATE TABLE `Genre` (
  `genreId` int(10) unsigned NOT NULL auto_increment,
  `name` tinytext NOT NULL,
  `description` tinytext,

  PRIMARY KEY  (`genreId`)
) ENGINE=InnoDB DEFAULT CHARSET=ucs2;



#
# Table structure for table 'Location'
#

CREATE TABLE `Location` (
  `locationId` int(10) unsigned NOT NULL auto_increment,
  `name` tinytext NOT NULL,
  `description` tinytext NOT NULL,
  `access` enum("public","private"),

  PRIMARY KEY  (`locationId`)
) ENGINE=InnoDB DEFAULT CHARSET=ucs2;



#
# Table structure for table 'Privs'
#

CREATE TABLE `Privs` (
  `privId` int(10) unsigned NOT NULL auto_increment,
  `name` tinytext NOT NULL,

  PRIMARY KEY  (`privId`)
) ENGINE=InnoDB DEFAULT CHARSET=ucs2;

#
# Table structure for table 'Users'
#

CREATE TABLE `Users` (
  `userId` int(10) unsigned NOT NULL auto_increment,
  `username` tinytext NOT NULL,
  `password` tinytext NOT NULL,
  `name` tinytext,
  `mail` tinytext,
  `defaultLocation` int(10) unsigned,

  INDEX defaultLocation_ind (defaultLocation),

  PRIMARY KEY (`userId`),
  FOREIGN KEY (`defaultLocation`) REFERENCES Location(`locationId`)

) ENGINE=InnoDB DEFAULT CHARSET=ucs2;


#
# Table structure for table 'GenreFunction'
#

CREATE TABLE `GenreFunction` (
  `genreFunctionId` int(10) unsigned NOT NULL auto_increment,
  `genreId` int(10) unsigned NOT NULL,
  `userId` int(10) unsigned NOT NULL,
  `rate` tinyint(4),
  `lastUpdate` timestamp,
  `updateFrequency` time, 
  `lambdaFunction` text,

  INDEX genreId_ind (genreId),
  INDEX userId_ind (userId),

  PRIMARY KEY  (`genreFunctionId`),
  FOREIGN KEY (`genreId`) REFERENCES Genre(`genreId`),
  FOREIGN KEY (`userId`) REFERENCES Users(`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=ucs2;


#
# Table structure for table 'MimeType'
#

CREATE TABLE `MimeType` (
  `mimeId` int(10) unsigned NOT NULL auto_increment,
  `main` tinytext,
  `sub` tinytext,

  PRIMARY KEY  (`mimeId`)
) ENGINE=InnoDB DEFAULT CHARSET=ucs2;


#
# Table structure for table 'Track'
#

CREATE TABLE `Track` (
  `trackId` int(10) unsigned NOT NULL auto_increment,
  `recordId` int(10) unsigned,
  `trackno` tinyint(4),
  `title` text,
  `length` time,
  `copyrightYear` year,
  `releaseDate` date,
  `releaseDatePrecision`enum("Y","YM","YMD"),
  `URI` text,
  `fail` bool default 0,
  `gain` float,
  `peak` float,
  `manualGain` float,
  `manualPeak` float,
  `quality` int,
  `codecVersion`int,
  `codecVendor` text,
  `fileTime` datetime,
  `recordUpdateTime` timestamp,
  `mimeId` int(10) unsigned,
  `musicBrainzTRMId` char(36),
  `musicBrainzTrackId` char(36),

  PRIMARY KEY  (`trackId`)
) ENGINE=InnoDB DEFAULT CHARSET=ucs2;

#
# Table structure for table 'Record'
#

CREATE TABLE `Record` (
  `recordId` int(10) unsigned NOT NULL auto_increment,
  `albumId` int(10) unsigned NOT NULL,
  `volume` tinyint(4),
  `length` time,
  `gain` float,
  `peak` float,
  `cddbId` text,
  `name` text,

  PRIMARY KEY  (`recordId`)
) ENGINE=InnoDB DEFAULT CHARSET=ucs2;

#
# Table structure for table 'Artist'
#

CREATE TABLE `Artist` (
  `artistId` int(10) unsigned NOT NULL auto_increment,
  `name` tinytext NOT NULL,
  `homepage` text,

  PRIMARY KEY  (`artistId`)
) ENGINE=InnoDB DEFAULT CHARSET=ucs2;


#
# Table structure for table 'GenreMapping'
#

CREATE TABLE `GenreMapping` (
  `genreMappingId` int(10) unsigned NOT NULL auto_increment,
  `genreId` int(10) unsigned NOT NULL,
  `referenceType` enum("artist","album","track"),
  `referenceId` int(10) unsigned NOT NULL,
  `userId` int(10) unsigned,

  INDEX genreId_ind (genreId),
  INDEX userId_ind (userId),

  PRIMARY KEY (`genreMappingId`),
  FOREIGN KEY (`genreId`) REFERENCES Genre(`genreId`),
  FOREIGN KEY (`userId`) REFERENCES Users(`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=ucs2;


#
# Table structure for table 'UserLog'
#

CREATE TABLE `UserLog` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `userId` int(10) unsigned NOT NULL,
  `locationId` int(10) unsigned NOT NULL,
  `logintime` timestamp(14) NOT NULL,
  `logouttime` timestamp(14) NOT NULL,

  INDEX userId_ind (userId),
  INDEX locationId_ind (locationId),

  PRIMARY KEY  (`id`),
  FOREIGN KEY (`userId`) REFERENCES Users(`userId`),
  FOREIGN KEY (`locationId`) REFERENCES Location(`locationId`)
) ENGINE=InnoDB DEFAULT CHARSET=ucs2;

#
# Table structure for table 'UserPriv'
#

CREATE TABLE `UserPriv` (
  `userId` int(10) unsigned NOT NULL,
  `privId` int(10) unsigned NOT NULL,

  INDEX userId_ind (userId),
  INDEX privid_ind (privId),

  FOREIGN KEY (`userId`) REFERENCES Users(`userId`),
  FOREIGN KEY (`privId`) REFERENCES Privs(`privId`)
) ENGINE=InnoDB DEFAULT CHARSET=ucs2;

#
# Table structure for table 'UserRate'
#

CREATE TABLE `UserRate` (
  `userRateId` int(10) unsigned NOT NULL auto_increment,
  `userId` int(10) unsigned NOT NULL,
  `referenceType` enum("genre","artist","album","track"),
  `referenceId` int(10) unsigned NOT NULL,
  `rate` tinyint(4) default NULL,

  INDEX userId_ind (userId),

  PRIMARY KEY  (`userRateId`),
  FOREIGN KEY (`userId`) REFERENCES Users(`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=ucs2;

#
# Table structure for table 'TrackLog'
#

CREATE TABLE `TrackLog` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `trackId` int(10) unsigned NOT NULL,
  `locationId` int(10) unsigned NOT NULL,
  `time` timestamp(14) NOT NULL,
  `interruptedBy` int(10) unsigned,

  INDEX trackId_ind (trackId),
  INDEX locationId_ind (locationId),
  INDEX interruptedBy_ind (interruptedBy),

  PRIMARY KEY (`id`),
  FOREIGN KEY (`trackId`) REFERENCES Track(`trackId`),
  FOREIGN KEY (`locationId`) REFERENCES Location(`locationId`),
  FOREIGN KEY (`InterruptedBy`) REFERENCES Users(`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=ucs2;

#
# Table structure for table 'TrackArtist'
#

CREATE TABLE `TrackArtist` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `trackId` int(10) unsigned NOT NULL,
  `artistId` int(10) unsigned NOT NULL,

  INDEX trackId_ind (trackId),
  INDEX artistId_ind (artistId),

  PRIMARY KEY  (`id`),
  FOREIGN KEY (`trackId`) REFERENCES Track(`trackId`),
  FOREIGN KEY (`artistId`) REFERENCES Artist(`artistId`)
) ENGINE=InnoDB DEFAULT CHARSET=ucs2;

#
# Table structure for table 'MemberArtist'
#

CREATE TABLE `MemberArtist` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `artistId` int(10) unsigned NOT NULL,
  `groupId` int(10) unsigned NOT NULL,

  INDEX artist_ind (artistId),
  INDEX group_ind (groupId),

  PRIMARY KEY  (`id`),
  FOREIGN KEY (`artistId`) REFERENCES Artist(`artistId`),
  FOREIGN KEY (`groupId`) REFERENCES Artist(`artistId`)
) ENGINE=InnoDB DEFAULT CHARSET=ucs2;


#
# Table structure for table 'AlbumArtist'
#

CREATE TABLE `AlbumArtist` (
  `id`       int(10) unsigned NOT NULL auto_increment,
  `albumId`  int(10) unsigned not null,
  `artistId` int(10) unsigned not null,

  INDEX albumId_ind (albumId),
  INDEX artistId_ind (artistId),

  PRIMARY KEY  (id),
  FOREIGN KEY (albumId) REFERENCES Album(albumId),
  FOREIGN KEY (artistId) REFERENCES Artist(artistId)
) ENGINE=InnoDB DEFAULT CHARSET=ucs2;

#
# Table structure for table 'UserLocation'
#

CREATE TABLE `UserLocation` (
  `id`       int(10) unsigned NOT NULL auto_increment,
  `userId`  int(10) unsigned not null,
  `locationId` int(10) unsigned not null,

  INDEX userId_ind (userId),
  INDEX locationId_ind (locationId),

  PRIMARY KEY  (id),
  FOREIGN KEY (userId) REFERENCES Users(userId),
  FOREIGN KEY (locationId) REFERENCES Location(locationId)
) ENGINE=InnoDB DEFAULT CHARSET=ucs2;

#
# Table structure for table 'Query'
#

CREATE TABLE `Query` (
  `queryId` int(10) unsigned NOT NULL auto_increment,
  `name`    tinytext NOT NULL,
  `userId`  int(10) unsigned NOT NULL,
  `result`  enum("track","album","artist","genre"),

  INDEX userId_ind (userId),

  PRIMARY KEY (queryId),
  FOREIGN KEY (userId) REFERENCES Users(userId)
) ENGINE=InnoDB DEFAULT CHARSET=ucs2;

#
# Table structure for table 'QueryCondition'
#

CREATE TABLE `QueryCondition` (
  `id`      int(10) unsigned NOT NULL auto_increment,
  `queryId` int(10) unsigned NOT NULL,
  `source`  tinytext NOT NULL,
  `operator` tinytext NOT NULL,
  `argument` tinytext NOT NULL,

  INDEX queryId_ind (queryId),

  PRIMARY KEY (id),
  FOREIGN KEY (queryId) REFERENCES Query(queryId)
) ENGINE=InnoDB DEFAULT CHARSET=ucs2;