view implementations/midi-db/db-setup.lisp @ 315:d1ff5dad3f8d

Improve printing of list-collections.

Ignore-this: 3073e6c1462cf98ca5752179ab6ecb1d

darcs-hash:20100414191443-16a00-e2f9962b0cea652890be18297fdbd4bf7d533c84.gz

(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))