annotate 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]>

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