spacepaste

Paste details

reply | raw

  1.  
  2. DROP DATABASE kodi_music60;
  3. CREATE DATABASE kodi_music60
  4. DEFAULT CHARACTER SET utf8
  5. DEFAULT COLLATE utf8_general_ci;
  7. USE kodi_music60;
  9. CREATE TABLE artist ( idArtist integer primary key,
  10. strArtist varchar(256), strMusicBrainzArtistID text,
  11. strBorn text, strFormed text, strGenres text, strMoods text,
  12. strStyles text, strInstruments text, strBiography text,
  13. strDied text, strDisbanded text, strYearsActive text,
  14. strImage text, strFanart text,
  15. lastScraped varchar(20) default NULL);
  16. INSERT INTO artist (idArtist, strArtist, strMusicBrainzArtistID) VALUES( 1, '[Missing Tag]', 'Artist Tag Missing' );
  18. CREATE TABLE album (idAlbum integer primary key,
  19. strAlbum varchar(256), strMusicBrainzAlbumID text,
  20. strArtists text, strGenres text,
  21. iYear integer, idThumb integer,
  22. bCompilation integer not null default '0',
  23. strMoods text, strStyles text, strThemes text,
  24. strReview text, strImage text, strLabel text,
  25. strType text,
  26. fRating FLOAT NOT NULL DEFAULT 0,
  27. iUserrating INTEGER NOT NULL DEFAULT 0,
  28. lastScraped varchar(20) default NULL,
  29. strReleaseType text,
  30. iVotes INTEGER NOT NULL DEFAULT 0);
  31. CREATE TABLE album_artist (idArtist integer, idAlbum integer, iOrder integer, strArtist text);
  32. CREATE TABLE album_genre (idGenre integer, idAlbum integer, iOrder integer);
  34. CREATE TABLE genre (idGenre integer primary key, strGenre varchar(256));
  35. CREATE TABLE path (idPath integer primary key, strPath varchar(512), strHash text);
  36. CREATE TABLE song (idSong integer primary key,
  37. idAlbum integer, idPath integer,
  38. strArtists text, strGenres text, strTitle varchar(512),
  39. iTrack integer, iDuration integer, iYear integer,
  40. dwFileNameCRC text,
  41. strFileName text, strMusicBrainzTrackID text,
  42. iTimesPlayed integer, iStartOffset integer, iEndOffset integer,
  43. idThumb integer,
  44. lastplayed varchar(20) default NULL,
  45. rating FLOAT NOT NULL DEFAULT 0, userrating INTEGER NOT NULL DEFAULT 0,
  46. comment text, mood text, dateAdded text, votes INTEGER NOT NULL DEFAULT 0);
  47. CREATE TABLE song_artist (idArtist integer, idSong integer, idRole integer, iOrder integer, strArtist text);
  48. CREATE TABLE song_genre (idGenre integer, idSong integer, iOrder integer);
  50. CREATE TABLE role (idRole integer primary key, strRole text);
  51. INSERT INTO role(idRole, strRole) VALUES (1, 'Artist');
  53. CREATE TABLE albuminfosong (idAlbumInfoSong integer primary key, idAlbumInfo integer, iTrack integer, strTitle text, iDuration integer);
  55. CREATE TABLE content (strPath text, strScraperPath text, strContent text, strSettings text);
  56. CREATE TABLE discography (idArtist integer, strAlbum text, strYear text);
  58. CREATE TABLE art(art_id INTEGER PRIMARY KEY, media_id INTEGER, media_type TEXT, type TEXT, url TEXT);
  60. CREATE TABLE cue (idPath integer, strFileName text, strCuesheet text);
  63. CREATE INDEX idxAlbum ON album(strAlbum(255));
  64. CREATE INDEX idxAlbum_1 ON album(bCompilation);
  65. CREATE UNIQUE INDEX idxAlbum_2 ON album(strMusicBrainzAlbumID(36));
  67. CREATE UNIQUE INDEX idxAlbumArtist_1 ON album_artist ( idAlbum, idArtist );
  68. CREATE UNIQUE INDEX idxAlbumArtist_2 ON album_artist ( idArtist, idAlbum );
  70. CREATE UNIQUE INDEX idxAlbumGenre_1 ON album_genre ( idAlbum, idGenre );
  71. CREATE UNIQUE INDEX idxAlbumGenre_2 ON album_genre ( idGenre, idAlbum );
  73. CREATE INDEX idxGenre ON genre(strGenre(255));
  75. CREATE INDEX idxArtist ON artist(strArtist(255));
  76. CREATE UNIQUE INDEX idxArtist1 ON artist(strMusicBrainzArtistID(36));
  78. CREATE INDEX idxPath ON path(strPath(255));
  80. CREATE INDEX idxSong ON song(strTitle(255));
  81. CREATE INDEX idxSong1 ON song(iTimesPlayed);
  82. CREATE INDEX idxSong2 ON song(lastplayed);
  83. CREATE INDEX idxSong3 ON song(idAlbum);
  84. CREATE INDEX idxSong6 ON song( idPath, strFileName(255) );
  85. CREATE UNIQUE INDEX idxSong7 ON song( idAlbum, strMusicBrainzTrackID(36) );
  87. CREATE UNIQUE INDEX idxSongArtist_1 ON song_artist ( idSong, idArtist, idRole );
  88. CREATE INDEX idxSongArtist_2 ON song_artist ( idSong, idRole );
  89. CREATE INDEX idxSongArtist_3 ON song_artist ( idArtist, idRole );
  90. CREATE INDEX idxSongArtist_4 ON song_artist ( idRole );
  92. CREATE UNIQUE INDEX idxSongGenre_1 ON song_genre ( idSong, idGenre );
  93. CREATE UNIQUE INDEX idxSongGenre_2 ON song_genre ( idGenre, idSong );
  95. CREATE INDEX idxRole on role(strRole(255));
  97. CREATE INDEX idxAlbumInfoSong_1 ON albuminfosong ( idAlbumInfo );
  99. CREATE INDEX idxDiscography_1 ON discography ( idArtist );
  101. CREATE INDEX ix_art ON art(media_id, media_type(20), type(20));
  103. CREATE UNIQUE INDEX idxCue ON cue(idPath, strFileName(255));
  105. CREATE TRIGGER tgrDeleteAlbum AFTER delete ON album FOR EACH ROW BEGIN
  106. DELETE FROM song WHERE song.idAlbum = old.idAlbum;
  107. DELETE FROM album_artist WHERE album_artist.idAlbum = old.idAlbum;
  108. DELETE FROM album_genre WHERE album_genre.idAlbum = old.idAlbum;
  109. DELETE FROM albuminfosong WHERE albuminfosong.idAlbumInfo=old.idAlbum;
  110. DELETE FROM art WHERE media_id=old.idAlbum AND media_type='album';
  111. END
  112. CREATE TRIGGER tgrDeleteArtist AFTER delete ON artist FOR EACH ROW BEGIN
  113. DELETE FROM album_artist WHERE album_artist.idArtist = old.idArtist;
  114. DELETE FROM song_artist WHERE song_artist.idArtist = old.idArtist;
  115. DELETE FROM discography WHERE discography.idArtist = old.idArtist;
  116. DELETE FROM art WHERE media_id=old.idArtist AND media_type='artist';
  117. END
  118. CREATE TRIGGER tgrDeleteSong AFTER delete ON song FOR EACH ROW BEGIN
  119. DELETE FROM song_artist WHERE song_artist.idSong = old.idSong;
  120. DELETE FROM song_genre WHERE song_genre.idSong = old.idSong;
  121. DELETE FROM art WHERE media_id=old.idSong AND media_type='song';
  122. END
  123. CREATE TRIGGER tgrDeletePath AFTER delete ON path FOR EACH ROW BEGIN
  124. DELETE FROM cue WHERE cue.idPath = old.idPath;
  125. END
  127. CREATE VIEW songview AS SELECT
  128. song.idSong AS idSong,
  129. song.strArtists AS strArtists,
  130. song.strGenres AS strGenres,
  131. strTitle,
  132. iTrack, iDuration,
  133. song.iYear AS iYear,
  134. strFileName,
  135. strMusicBrainzTrackID,
  136. iTimesPlayed, iStartOffset, iEndOffset,
  137. lastplayed,
  138. song.rating,
  139. song.userrating,
  140. song.votes,
  141. comment,
  142. song.idAlbum AS idAlbum,
  143. strAlbum,
  144. strPath,
  145. album.bCompilation AS bCompilation,
  146. album.strArtists AS strAlbumArtists,
  147. album.strReleaseType AS strAlbumReleaseType,
  148. song.mood as mood,
  149. song.dateAdded as dateAdded
  150. FROM song
  151. JOIN album ON
  152. song.idAlbum=album.idAlbum
  153. JOIN path ON
  154. song.idPath=path.idPath
  156. CREATE VIEW albumview AS SELECT
  157. album.idAlbum AS idAlbum,
  158. strAlbum,
  159. strMusicBrainzAlbumID,
  160. album.strArtists AS strArtists,
  161. album.strGenres AS strGenres,
  162. album.iYear AS iYear,
  163. album.strMoods AS strMoods,
  164. album.strStyles AS strStyles,
  165. strThemes,
  166. strReview,
  167. strLabel,
  168. strType,
  169. album.strImage as strImage,
  170. album.fRating,
  171. album.iUserrating,
  172. album.iVotes,
  173. bCompilation,
  174. (SELECT AVG(song.iTimesPlayed) FROM song WHERE song.idAlbum = album.idAlbum) AS iTimesPlayed,
  175. strReleaseType,
  176. (SELECT MAX(song.dateAdded) FROM song WHERE song.idAlbum = album.idAlbum) AS dateAdded,
  177. (SELECT MAX(song.lastplayed) FROM song WHERE song.idAlbum = album.idAlbum) AS lastplayed
  178. FROM album
  181. CREATE VIEW artistview AS SELECT
  182. idArtist, strArtist,
  183. strMusicBrainzArtistID,
  184. strBorn, strFormed, strGenres,
  185. strMoods, strStyles, strInstruments,
  186. strBiography, strDied, strDisbanded,
  187. strYearsActive, strImage, strFanart,
  188. (SELECT MAX(song.dateAdded) FROM song_artist INNER JOIN song ON song.idSong = song_artist.idSong
  189. WHERE song_artist.idArtist = artist.idArtist) AS dateAdded
  190. FROM artist
  192. CREATE VIEW albumartistview AS SELECT
  193. album_artist.idAlbum AS idAlbum,
  194. album_artist.idArtist AS idArtist,
  195. 0 AS idRole,
  196. 'AlbumArtist' AS strRole,
  197. artist.strArtist AS strArtist,
  198. artist.strMusicBrainzArtistID AS strMusicBrainzArtistID,
  199. album_artist.iOrder AS iOrder
  200. FROM album_artist
  201. JOIN artist ON
  202. album_artist.idArtist = artist.idArtist
  204. CREATE VIEW songartistview AS SELECT
  205. song_artist.idSong AS idSong,
  206. song_artist.idArtist AS idArtist,
  207. song_artist.idRole AS idRole,
  208. role.strRole AS strRole,
  209. artist.strArtist AS strArtist,
  210. artist.strMusicBrainzArtistID AS strMusicBrainzArtistID,
  211. song_artist.iOrder AS iOrder
  212. FROM song_artist
  213. JOIN artist ON
  214. song_artist.idArtist = artist.idArtist
  215. JOIN role ON
  216. song_artist.idRole = role.idRole
  217.  
Powered by spacepaste. Paste removal.