view implementations/meredith/database-setup.lisp @ 310:f99fd6a7bbfc

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

(cl:in-package #:amuse-meredith)

(defun create-meredith-tables (database)
  (%create-compositions-table database)
  (%create-events-table database))

(defun drop-meredith-tables (database)
  (%drop-compositions-table database)
  (%drop-events-table database))


;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;; Helper functions

(defun %create-compositions-table (database)
  #.(clsql:locally-enable-sql-reader-syntax)
  (clsql:create-table "meredith_compositions"
		      '(([|composition-id|] clsql:smallint :unsigned
			 :not-null :auto-increment :primary-key)
			([|description|] (clsql:varchar 255)
			 :not-null))
		      :database database
		      :transactions t)
  #.(clsql:locally-disable-sql-reader-syntax))

(defun %create-events-table (database)
  #.(clsql:locally-enable-sql-reader-syntax)
  (clsql:create-table "meredith_events"
		'(([|composition-id|] clsql:smallint :unsigned
		   :not-null)
		  ([|event-id|] clsql:smallint :unsigned
		   :not-null :auto-increment :unique)
		  ([|tatum-on|] clsql:smallint :unsigned :not-null)
		  ([|pitch-name|] (clsql:varchar 32) :not-null)
		  ([|tatum-dur|] clsql:smallint :unsigned :not-null)
		  ([|voice|] clsql:smallint :unsigned :not-null)
		  ([|tactus-on|] clsql::float :unsigned :not-null)
		  ([|tactus-dur|] clsql::float :unsigned :not-null)
		  ([|crot-on|] clsql::float :unsigned :not-null)
		  ([|crot-dur|] clsql::float :unsigned :not-null)
		  ([|midi-note-number|] clsql:smallint :unsigned :not-null)
		  ([|chrom-pitch|] clsql:smallint :unsigned :not-null)
		  ([|morph-pitch|] clsql:smallint :unsigned :not-null)
		  ([|tatum-on-ms|] clsql:smallint :unsigned :not-null)
		  ([|tatum-dur-ms|] clsql:smallint :unsigned :not-null)
		  ([|beat-on-ms|] clsql:smallint :unsigned :not-null)
		  ([|beat-dur-ms|] clsql:smallint :unsigned :not-null)
		  ([|crot-on-ms|] clsql:smallint :unsigned :not-null)
		  ([|crot-dur-ms|] clsql:smallint :unsigned :not-null))
		:constraints '("PRIMARY KEY (composition_id, event_id)")
		:database database
		:transactions t)
  #.(clsql:locally-disable-sql-reader-syntax))

(defun %drop-compositions-table (database)
  (clsql:drop-table "meredith_compositions"
		    :database database))

(defun %drop-events-table (database)
  (clsql:drop-table "meredith_events"
		    :database database))