Author: dsully
Date: Wed Nov 16 10:54:14 2011
New Revision: 33712
URL: http://svn.slimdevices.com/slim?rev=33712&view=rev
Log:
Add up to date MySQL DDL.
Added:
7.7/trunk/server/SQL/mysql/schema_13_down.sql
7.7/trunk/server/SQL/mysql/schema_13_up.sql
7.7/trunk/server/SQL/mysql/schema_14_down.sql
7.7/trunk/server/SQL/mysql/schema_14_up.sql
7.7/trunk/server/SQL/mysql/schema_15_down.sql
7.7/trunk/server/SQL/mysql/schema_15_up.sql
7.7/trunk/server/SQL/mysql/schema_16_down.sql
7.7/trunk/server/SQL/mysql/schema_16_up.sql
7.7/trunk/server/SQL/mysql/schema_17_down.sql
7.7/trunk/server/SQL/mysql/schema_17_up.sql
7.7/trunk/server/SQL/mysql/schema_18_down.sql
7.7/trunk/server/SQL/mysql/schema_18_up.sql
Modified:
7.7/trunk/server/SQL/mysql/schema_10_down.sql
7.7/trunk/server/SQL/mysql/schema_10_up.sql
7.7/trunk/server/SQL/mysql/schema_11_up.sql
7.7/trunk/server/SQL/mysql/schema_1_up.sql
7.7/trunk/server/SQL/mysql/schema_2_up.sql
7.7/trunk/server/SQL/mysql/schema_6_up.sql
Modified: 7.7/trunk/server/SQL/mysql/schema_10_down.sql
URL: http://svn.slimdevices.com/slim/7.7/trunk/server/SQL/mysql/schema_10_down.sql?rev=33712&r1=33711&r2=33712&view=diff
==============================================================================
--- 7.7/trunk/server/SQL/mysql/schema_10_down.sql (original)
+++ 7.7/trunk/server/SQL/mysql/schema_10_down.sql Wed Nov 16 10:54:14 2011
@@ -13,4 +13,4 @@
INDEX playlistIndex (playlist),
FOREIGN KEY (`track`) REFERENCES `tracks` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`playlist`) REFERENCES `tracks` (`id`) ON DELETE CASCADE
-) TYPE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Modified: 7.7/trunk/server/SQL/mysql/schema_10_up.sql
URL: http://svn.slimdevices.com/slim/7.7/trunk/server/SQL/mysql/schema_10_up.sql?rev=33712&r1=33711&r2=33712&view=diff
==============================================================================
--- 7.7/trunk/server/SQL/mysql/schema_10_up.sql (original)
+++ 7.7/trunk/server/SQL/mysql/schema_10_up.sql Wed Nov 16 10:54:14 2011
@@ -12,5 +12,5 @@
INDEX positionIndex (position),
INDEX playlistIndex (playlist),
FOREIGN KEY (`playlist`) REFERENCES `tracks` (`id`) ON DELETE CASCADE
-) TYPE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
Modified: 7.7/trunk/server/SQL/mysql/schema_11_up.sql
URL: http://svn.slimdevices.com/slim/7.7/trunk/server/SQL/mysql/schema_11_up.sql?rev=33712&r1=33711&r2=33712&view=diff
==============================================================================
--- 7.7/trunk/server/SQL/mysql/schema_11_up.sql (original)
+++ 7.7/trunk/server/SQL/mysql/schema_11_up.sql Wed Nov 16 10:54:14 2011
@@ -4,5 +4,5 @@
url TEXT NOT NULL,
timestamp int(10),
filesize int(10)
-) TYPE=InnoDB;
+) ENGINE=InnoDB;
CREATE INDEX scannedUrlIndex ON scanned_files (url(255));
Added: 7.7/trunk/server/SQL/mysql/schema_13_down.sql
URL: http://svn.slimdevices.com/slim/7.7/trunk/server/SQL/mysql/schema_13_down.sql?rev=33712&view=auto
==============================================================================
--- 7.7/trunk/server/SQL/mysql/schema_13_down.sql (added)
+++ 7.7/trunk/server/SQL/mysql/schema_13_down.sql Wed Nov 16 10:54:14 2011
@@ -1,0 +1,4 @@
+
+DROP TABLE IF EXISTS videos;
+
+DROP TABLE IF EXISTS images;
Added: 7.7/trunk/server/SQL/mysql/schema_13_up.sql
URL: http://svn.slimdevices.com/slim/7.7/trunk/server/SQL/mysql/schema_13_up.sql?rev=33712&view=auto
==============================================================================
--- 7.7/trunk/server/SQL/mysql/schema_13_up.sql (added)
+++ 7.7/trunk/server/SQL/mysql/schema_13_up.sql Wed Nov 16 10:54:14 2011
@@ -1,0 +1,45 @@
+DROP TABLE IF EXISTS videos;
+CREATE TABLE videos (
+ id int(10) unsigned NOT NULL auto_increment,
+ hash char(8) NOT NULL,
+ url text NOT NULL,
+ title blob,
+ titlesort text,
+ titlesearch text,
+ video_codec varchar(128),
+ audio_codec varchar(128),
+ mime_type varchar(32),
+ dlna_profile varchar(32),
+ width int(10),
+ height int(10),
+ mtime int(10),
+ added_time int(10),
+ filesize int(10),
+ secs float,
+ bitrate float,
+ channels tinyint(1),
+ INDEX videoURLIndex (url(255)),
+ INDEX videoHashIndex (hash),
+ PRIMARY KEY (id)
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
+
+DROP TABLE IF EXISTS images;
+CREATE TABLE images (
+ id int(10) unsigned NOT NULL auto_increment,
+ hash char(8) NOT NULL,
+ url text NOT NULL,
+ title blob,
+ titlesort text,
+ titlesearch text,
+ image_codec varchar(128),
+ mime_type varchar(32),
+ dlna_profile varchar(32),
+ width int(10),
+ height int(10),
+ mtime int(10),
+ added_time int(10),
+ filesize int(10),
+ INDEX imageURLIndex (url(255)),
+ INDEX imageHashIndex (hash),
+ PRIMARY KEY (id)
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Added: 7.7/trunk/server/SQL/mysql/schema_14_down.sql
URL: http://svn.slimdevices.com/slim/7.7/trunk/server/SQL/mysql/schema_14_down.sql?rev=33712&view=auto
==============================================================================
--- 7.7/trunk/server/SQL/mysql/schema_14_down.sql (added)
+++ 7.7/trunk/server/SQL/mysql/schema_14_down.sql Wed Nov 16 10:54:14 2011
@@ -1,0 +1,1 @@
+-- empty placeholder to skip schema versions defined in 7.7/trunk
Added: 7.7/trunk/server/SQL/mysql/schema_14_up.sql
URL: http://svn.slimdevices.com/slim/7.7/trunk/server/SQL/mysql/schema_14_up.sql?rev=33712&view=auto
==============================================================================
--- 7.7/trunk/server/SQL/mysql/schema_14_up.sql (added)
+++ 7.7/trunk/server/SQL/mysql/schema_14_up.sql Wed Nov 16 10:54:14 2011
@@ -1,0 +1,8 @@
+
+ALTER TABLE tracks ADD added_time int(10) default NULL;
+
+ALTER TABLE tracks ADD updated_time int(10) default NULL;
+
+ALTER TABLE images ADD updated_time int(10) default NULL;
+
+ALTER TABLE videos ADD updated_time int(10) default NULL;
Added: 7.7/trunk/server/SQL/mysql/schema_15_down.sql
URL: http://svn.slimdevices.com/slim/7.7/trunk/server/SQL/mysql/schema_15_down.sql?rev=33712&view=auto
==============================================================================
--- 7.7/trunk/server/SQL/mysql/schema_15_down.sql (added)
+++ 7.7/trunk/server/SQL/mysql/schema_15_down.sql Wed Nov 16 10:54:14 2011
@@ -1,0 +1,1 @@
+-- empty placeholder to skip schema versions defined in 7.7/trunk
Added: 7.7/trunk/server/SQL/mysql/schema_15_up.sql
URL: http://svn.slimdevices.com/slim/7.7/trunk/server/SQL/mysql/schema_15_up.sql?rev=33712&view=auto
==============================================================================
--- 7.7/trunk/server/SQL/mysql/schema_15_up.sql (added)
+++ 7.7/trunk/server/SQL/mysql/schema_15_up.sql Wed Nov 16 10:54:14 2011
@@ -1,0 +1,10 @@
+
+-- to be set to EXIF DateTimeOriginal or similar
+ALTER TABLE images ADD original_time int(10) NOT NULL DEFAULT 0;
+CREATE INDEX imageDateTimeOriginal ON images (original_time);
+
+ALTER TABLE images ADD orientation int(10) NOT NULL DEFAULT 0;
+
+ALTER TABLE images ADD album blob default NULL;
+
+ALTER TABLE videos ADD album blob default NULL;
Added: 7.7/trunk/server/SQL/mysql/schema_16_down.sql
URL: http://svn.slimdevices.com/slim/7.7/trunk/server/SQL/mysql/schema_16_down.sql?rev=33712&view=auto
==============================================================================
(empty)
Added: 7.7/trunk/server/SQL/mysql/schema_16_up.sql
URL: http://svn.slimdevices.com/slim/7.7/trunk/server/SQL/mysql/schema_16_up.sql?rev=33712&view=auto
==============================================================================
--- 7.7/trunk/server/SQL/mysql/schema_16_up.sql (added)
+++ 7.7/trunk/server/SQL/mysql/schema_16_up.sql Wed Nov 16 10:54:14 2011
@@ -1,0 +1,2 @@
+-- re-build tracks table from scratch to make url case insensitive
+-- Not required for MySQL.
Added: 7.7/trunk/server/SQL/mysql/schema_17_down.sql
URL: http://svn.slimdevices.com/slim/7.7/trunk/server/SQL/mysql/schema_17_down.sql?rev=33712&view=auto
==============================================================================
(empty)
Added: 7.7/trunk/server/SQL/mysql/schema_17_up.sql
URL: http://svn.slimdevices.com/slim/7.7/trunk/server/SQL/mysql/schema_17_up.sql?rev=33712&view=auto
==============================================================================
--- 7.7/trunk/server/SQL/mysql/schema_17_up.sql (added)
+++ 7.7/trunk/server/SQL/mysql/schema_17_up.sql Wed Nov 16 10:54:14 2011
@@ -1,0 +1,2 @@
+-- schema_16 recreated tracks but forgot to recreate all the indicies
+-- Not required for MySQL, since we didn't recreate the tracks table.
Added: 7.7/trunk/server/SQL/mysql/schema_18_down.sql
URL: http://svn.slimdevices.com/slim/7.7/trunk/server/SQL/mysql/schema_18_down.sql?rev=33712&view=auto
==============================================================================
(empty)
Added: 7.7/trunk/server/SQL/mysql/schema_18_up.sql
URL: http://svn.slimdevices.com/slim/7.7/trunk/server/SQL/mysql/schema_18_up.sql?rev=33712&view=auto
==============================================================================
--- 7.7/trunk/server/SQL/mysql/schema_18_up.sql (added)
+++ 7.7/trunk/server/SQL/mysql/schema_18_up.sql Wed Nov 16 10:54:14 2011
@@ -1,0 +1,3 @@
+ALTER TABLE tracks ADD dlna_profile varchar(32);
+ALTER TABLE tracks ADD hash char(8);
+CREATE INDEX trackHashIndex ON tracks (hash);
Modified: 7.7/trunk/server/SQL/mysql/schema_1_up.sql
URL: http://svn.slimdevices.com/slim/7.7/trunk/server/SQL/mysql/schema_1_up.sql?rev=33712&r1=33711&r2=33712&view=diff
==============================================================================
--- 7.7/trunk/server/SQL/mysql/schema_1_up.sql (original)
+++ 7.7/trunk/server/SQL/mysql/schema_1_up.sql Wed Nov 16 10:54:14 2011
@@ -10,7 +10,7 @@
name varchar(255) NOT NULL DEFAULT '',
value varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (name)
-) TYPE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
--
-- Table: rescans
@@ -23,7 +23,7 @@
start_time int(10) unsigned,
end_time int(10) unsigned,
PRIMARY KEY (id)
-) TYPE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
--
-- Table: unreadable_tracks
@@ -36,7 +36,7 @@
reason text NOT NULL,
PRIMARY KEY (id),
INDEX unreadableRescanIndex (rescan)
-) TYPE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
--
-- Table: tracks
@@ -106,7 +106,7 @@
PRIMARY KEY (id),
-- UNIQUE KEY (url),
FOREIGN KEY (`album`) REFERENCES `albums` (`id`) ON DELETE CASCADE
-) TYPE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
--
-- Table: playlist_track
@@ -123,7 +123,7 @@
INDEX playlistIndex (playlist),
FOREIGN KEY (`track`) REFERENCES `tracks` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`playlist`) REFERENCES `tracks` (`id`) ON DELETE CASCADE
-) TYPE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
--
-- Table: albums
@@ -154,7 +154,7 @@
INDEX albumsDiscCountIndex (discc),
INDEX albumsArtworkIndex (artwork),
PRIMARY KEY (id)
-) TYPE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
--
-- Table: years
@@ -163,7 +163,7 @@
CREATE TABLE years (
id smallint(5) unsigned,
PRIMARY KEY (id)
-) TYPE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
--
-- Table: contributors
@@ -184,7 +184,7 @@
INDEX contributorsSearchIndex (namesearch(255)),
INDEX contributorsCustomSearchIndex (customsearch(255)),
PRIMARY KEY (id)
-) TYPE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
--
-- Table: contributor_track
@@ -200,7 +200,7 @@
PRIMARY KEY (role,contributor,track),
FOREIGN KEY (`track`) REFERENCES `tracks` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`contributor`) REFERENCES `contributors` (`id`) ON DELETE CASCADE
-) TYPE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
--
-- Table: contributor_album
@@ -216,7 +216,7 @@
PRIMARY KEY (role,contributor,album),
FOREIGN KEY (`album`) REFERENCES `albums` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`contributor`) REFERENCES `contributors` (`id`) ON DELETE CASCADE
-) TYPE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
--
-- Table: genres
@@ -236,7 +236,7 @@
INDEX genreSearchIndex (namesearch(255)),
INDEX genreCustomSearchIndex (customsearch(255)),
PRIMARY KEY (id)
-) TYPE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
--
-- Table: genre_track
@@ -250,7 +250,7 @@
PRIMARY KEY (genre,track),
FOREIGN KEY (`track`) REFERENCES `tracks` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`genre`) REFERENCES `genres` (`id`) ON DELETE CASCADE
-) TYPE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
--
-- Table: comments
@@ -263,7 +263,7 @@
PRIMARY KEY (id),
INDEX trackIndex (track),
FOREIGN KEY (`track`) REFERENCES `tracks` (`id`) ON DELETE CASCADE
-) TYPE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
--
-- Table: pluginversion
@@ -274,4 +274,4 @@
name varchar(255),
version int(10) unsigned,
PRIMARY KEY (id)
-) TYPE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Modified: 7.7/trunk/server/SQL/mysql/schema_2_up.sql
URL: http://svn.slimdevices.com/slim/7.7/trunk/server/SQL/mysql/schema_2_up.sql?rev=33712&r1=33711&r2=33712&view=diff
==============================================================================
--- 7.7/trunk/server/SQL/mysql/schema_2_up.sql (original)
+++ 7.7/trunk/server/SQL/mysql/schema_2_up.sql Wed Nov 16 10:54:14 2011
@@ -10,4 +10,4 @@
CREATE TABLE IF NOT EXISTS years (
id smallint(5) unsigned,
PRIMARY KEY (id)
-) TYPE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Modified: 7.7/trunk/server/SQL/mysql/schema_6_up.sql
URL: http://svn.slimdevices.com/slim/7.7/trunk/server/SQL/mysql/schema_6_up.sql?rev=33712&r1=33711&r2=33712&view=diff
==============================================================================
--- 7.7/trunk/server/SQL/mysql/schema_6_up.sql (original)
+++ 7.7/trunk/server/SQL/mysql/schema_6_up.sql Wed Nov 16 10:54:14 2011
@@ -18,7 +18,7 @@
INDEX trackLastPlayedIndex (lastPlayed),
PRIMARY KEY (id),
UNIQUE KEY (track)
-) TYPE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE tracks DROP rating;
ALTER TABLE tracks DROP playCount;
lmpx.com only provides a reader for public news (NNTP) servers. It is not
affiliated with the servers or forums shown here and is not responsible for
the content of articles, which is written by their respective authors.