diff implementations/midi-db/db-setup.lisp @ 236:a5d065905f6d

Add midi-db.

Ignore-this: c6f4fc32efa4453ddbdc478793eedd52

A basic implementation for working with MIDI files stored in the database.

It is a test case for `versioned' data, but only partially implemented at the moment.

darcs-hash:20100223152703-16a00-4388d2720907d777a1c6c6b3a010885ce0fe06a7.gz

committer: Jamie Forth <[e-mail]>

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/implementations/midi-db/db-setup.lisp	Thu Feb 24 11:23:18 2011 +0000
@@ -0,0 +1,160 @@
+(cl:in-package #:amuse-midi-db)
+
+;; Creating tables
+
+(defun create-midi-db-tables (database)
+  (%create-midi-collections-table database)
+  (%create-midi-compositions-table database)
+  (%create-midi-events-table database)
+  (%create-midi-tempi-table database)
+  (%create-midi-timesigs-table database)
+  (%create-midi-keysigs-table database))
+   
+(defun %create-midi-collections-table (database)
+  #.(clsql:locally-enable-sql-reader-syntax)
+  (clsql:create-table "midi_db_collections"
+		      '(([|collection-id|] clsql:smallint :unsigned
+			 :not-null :auto-increment :primary-key)
+			([|collection-name|] (clsql:varchar 255)
+			 :not-null)
+			([|description|] (clsql:varchar 255)
+			 :not-null))
+		      :database database
+		      :transactions t)
+  #.(clsql:locally-disable-sql-reader-syntax))
+
+(defun %create-midi-compositions-table (database)
+  (clsql:execute-command "
+CREATE TABLE midi_db_compositions (
+collection_id SMALLINT UNSIGNED NOT NULL,
+composition_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
+filename VARCHAR(255) NOT NULL,
+timebase SMALLINT UNSIGNED NOT NULL,
+start INT UNSIGNED NOT NULL,
+duration INT UNSIGNED NOT NULL,
+owner CHAR(16) NULL,
+version SMALLINT UNSIGNED NOT NULL DEFAULT 1,
+creation_timestamp TIMESTAMP NULL,
+deletion_timestamp TIMESTAMP NULL,
+INDEX midi_composition_index (
+collection_id, composition_id, version))
+engine = innodb;"
+			 :database database)
+  (%create-midi-composition-header-triggers database))
+
+(defun %create-midi-composition-header-triggers (database)
+  (clsql:execute-command "
+CREATE TRIGGER pre_insert_comp BEFORE INSERT ON midi_db_compositions
+FOR EACH ROW
+BEGIN
+SET NEW.owner = SUBSTRING_INDEX(USER(),'@',1);
+SET NEW.creation_timestamp = CURRENT_TIMESTAMP;
+END;"
+			 :database database))
+
+(defun %create-midi-events-table (database)
+  (clsql:execute-command "
+CREATE TABLE midi_db_events (
+collection_id SMALLINT UNSIGNED NOT NULL,
+composition_id SMALLINT UNSIGNED NOT NULL,
+event_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
+track SMALLINT UNSIGNED NOT NULL,
+channel TINYINT UNSIGNED NOT NULL,
+patch TINYINT UNSIGNED NOT NULL,
+pitch TINYINT UNSIGNED NOT NULL,
+velocity TINYINT UNSIGNED NOT NULL,
+start INTEGER UNSIGNED NOT NULL,
+duration INTEGER UNSIGNED NOT NULL,
+version SMALLINT UNSIGNED NOT NULL DEFAULT 1,
+INDEX collection_composition (collection_id, composition_id))
+engine = innodb;"
+			 :database database))
+
+(defun %create-midi-tempi-table (database)
+  (clsql:execute-command "
+CREATE TABLE midi_db_tempi (
+collection_id SMALLINT UNSIGNED NOT NULL,
+composition_id SMALLINT UNSIGNED NOT NULL,
+constituent_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
+start INT UNSIGNED NOT NULL,
+duration INT UNSIGNED NOT NULL,
+microsecs_per_crotchet INTEGER UNSIGNED NOT NULL,
+version SMALLINT UNSIGNED NOT NULL DEFAULT 1,
+INDEX collection_composition (collection_id, composition_id, constituent_id))
+engine = innodb;"
+			 :database database))
+
+(defun %create-midi-timesigs-table (database)
+  (clsql:execute-command "
+CREATE TABLE midi_db_timesigs (
+collection_id SMALLINT UNSIGNED NOT NULL,
+composition_id SMALLINT UNSIGNED NOT NULL,
+constituent_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
+start INT UNSIGNED NOT NULL,
+duration INT UNSIGNED NOT NULL,
+numerator TINYINT UNSIGNED NOT NULL,
+denominator TINYINT UNSIGNED NOT NULL,
+version SMALLINT UNSIGNED NOT NULL DEFAULT 1,
+INDEX collection_composition (collection_id, composition_id, constituent_id))
+engine = innodb;"
+			 :database database))
+
+(defun %create-midi-keysigs-table (database)
+  (clsql:execute-command "
+CREATE TABLE midi_db_keysigs (
+collection_id SMALLINT UNSIGNED NOT NULL,
+composition_id SMALLINT UNSIGNED NOT NULL,
+constituent_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
+start INT UNSIGNED NOT NULL,
+duration INT UNSIGNED NOT NULL,
+mode TINYINT UNSIGNED NOT NULL,
+sharp_count TINYINT UNSIGNED NOT NULL,
+version SMALLINT UNSIGNED NOT NULL DEFAULT 1,
+INDEX collection_composition (collection_id, composition_id, constituent_id))
+engine = innodb;"
+			 :database database))
+
+;; Deleting tables
+
+(defun drop-midi-db-tables (database)
+  (%drop-midi-collections-table database)
+  (%drop-midi-compositions-table database)
+  (%drop-midi-events-table database)
+  (%drop-midi-tempi-table database)
+  (%drop-midi-timesigs-table database)
+  (%drop-midi-keysigs-table database))
+
+(defun %drop-midi-collections-table (database)
+  (clsql:drop-table "midi_db_collections"
+		    :if-does-not-exist :ignore
+		    :database database))
+
+(defun %drop-midi-compositions-table (database)
+  (clsql:drop-table "midi_db_compositions"
+		    :if-does-not-exist :ignore
+		    :database database))
+
+(defun %drop-midi-events-table (database)
+  (clsql:drop-table "midi_db_events"
+		    :if-does-not-exist :ignore
+		    :database database))
+
+(defun %drop-midi-constituent-headers-table (database)
+  (clsql:drop-table "midi_db_constituent_headers"
+		    :if-does-not-exist :ignore
+		    :database database))
+
+(defun %drop-midi-tempi-table (database)
+  (clsql:drop-table "midi_db_tempi"
+		    :if-does-not-exist :ignore
+		    :database database))
+
+(defun %drop-midi-timesigs-table (database)
+  (clsql:drop-table "midi_db_timesigs"
+		    :if-does-not-exist :ignore
+		    :database database))
+
+(defun %drop-midi-keysigs-table (database)
+  (clsql:drop-table "midi_db_keysigs"
+		    :if-does-not-exist :ignore
+		    :database database))