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