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