Mercurial > hg > amuse

comparison implementations/midi-db/db-setup.lisp @ 236:a5d065905f6d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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 <j.forth@gold.ac.uk>
author j.forth <j.forth@gold.ac.uk>
date Thu, 24 Feb 2011 11:23:18 +0000
parents
children
comparison
equal deleted inserted replaced
235:ea45a3d0730c 236:a5d065905f6d
1 (cl:in-package #:amuse-midi-db)
2
3 ;; Creating tables
4
5 (defun create-midi-db-tables (database)
6 (%create-midi-collections-table database)
7 (%create-midi-compositions-table database)
8 (%create-midi-events-table database)
9 (%create-midi-tempi-table database)
10 (%create-midi-timesigs-table database)
11 (%create-midi-keysigs-table database))
12
13 (defun %create-midi-collections-table (database)
14 #.(clsql:locally-enable-sql-reader-syntax)
15 (clsql:create-table "midi_db_collections"
16 '(([|collection-id|] clsql:smallint :unsigned
17 :not-null :auto-increment :primary-key)
18 ([|collection-name|] (clsql:varchar 255)
19 :not-null)
20 ([|description|] (clsql:varchar 255)
21 :not-null))
22 :database database
23 :transactions t)
24 #.(clsql:locally-disable-sql-reader-syntax))
25
26 (defun %create-midi-compositions-table (database)
27 (clsql:execute-command "
28 CREATE TABLE midi_db_compositions (
29 collection_id SMALLINT UNSIGNED NOT NULL,
30 composition_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
31 filename VARCHAR(255) NOT NULL,
32 timebase SMALLINT UNSIGNED NOT NULL,
33 start INT UNSIGNED NOT NULL,
34 duration INT UNSIGNED NOT NULL,
35 owner CHAR(16) NULL,
36 version SMALLINT UNSIGNED NOT NULL DEFAULT 1,
37 creation_timestamp TIMESTAMP NULL,
38 deletion_timestamp TIMESTAMP NULL,
39 INDEX midi_composition_index (
40 collection_id, composition_id, version))
41 engine = innodb;"
42 :database database)
43 (%create-midi-composition-header-triggers database))
44
45 (defun %create-midi-composition-header-triggers (database)
46 (clsql:execute-command "
47 CREATE TRIGGER pre_insert_comp BEFORE INSERT ON midi_db_compositions
48 FOR EACH ROW
49 BEGIN
50 SET NEW.owner = SUBSTRING_INDEX(USER(),'@',1);
51 SET NEW.creation_timestamp = CURRENT_TIMESTAMP;
52 END;"
53 :database database))
54
55 (defun %create-midi-events-table (database)
56 (clsql:execute-command "
57 CREATE TABLE midi_db_events (
58 collection_id SMALLINT UNSIGNED NOT NULL,
59 composition_id SMALLINT UNSIGNED NOT NULL,
60 event_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
61 track SMALLINT UNSIGNED NOT NULL,
62 channel TINYINT UNSIGNED NOT NULL,
63 patch TINYINT UNSIGNED NOT NULL,
64 pitch TINYINT UNSIGNED NOT NULL,
65 velocity TINYINT UNSIGNED NOT NULL,
66 start INTEGER UNSIGNED NOT NULL,
67 duration INTEGER UNSIGNED NOT NULL,
68 version SMALLINT UNSIGNED NOT NULL DEFAULT 1,
69 INDEX collection_composition (collection_id, composition_id))
70 engine = innodb;"
71 :database database))
72
73 (defun %create-midi-tempi-table (database)
74 (clsql:execute-command "
75 CREATE TABLE midi_db_tempi (
76 collection_id SMALLINT UNSIGNED NOT NULL,
77 composition_id SMALLINT UNSIGNED NOT NULL,
78 constituent_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
79 start INT UNSIGNED NOT NULL,
80 duration INT UNSIGNED NOT NULL,
81 microsecs_per_crotchet INTEGER UNSIGNED NOT NULL,
82 version SMALLINT UNSIGNED NOT NULL DEFAULT 1,
83 INDEX collection_composition (collection_id, composition_id, constituent_id))
84 engine = innodb;"
85 :database database))
86
87 (defun %create-midi-timesigs-table (database)
88 (clsql:execute-command "
89 CREATE TABLE midi_db_timesigs (
90 collection_id SMALLINT UNSIGNED NOT NULL,
91 composition_id SMALLINT UNSIGNED NOT NULL,
92 constituent_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
93 start INT UNSIGNED NOT NULL,
94 duration INT UNSIGNED NOT NULL,
95 numerator TINYINT UNSIGNED NOT NULL,
96 denominator TINYINT UNSIGNED NOT NULL,
97 version SMALLINT UNSIGNED NOT NULL DEFAULT 1,
98 INDEX collection_composition (collection_id, composition_id, constituent_id))
99 engine = innodb;"
100 :database database))
101
102 (defun %create-midi-keysigs-table (database)
103 (clsql:execute-command "
104 CREATE TABLE midi_db_keysigs (
105 collection_id SMALLINT UNSIGNED NOT NULL,
106 composition_id SMALLINT UNSIGNED NOT NULL,
107 constituent_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
108 start INT UNSIGNED NOT NULL,
109 duration INT UNSIGNED NOT NULL,
110 mode TINYINT UNSIGNED NOT NULL,
111 sharp_count TINYINT UNSIGNED NOT NULL,
112 version SMALLINT UNSIGNED NOT NULL DEFAULT 1,
113 INDEX collection_composition (collection_id, composition_id, constituent_id))
114 engine = innodb;"
115 :database database))
116
117 ;; Deleting tables
118
119 (defun drop-midi-db-tables (database)
120 (%drop-midi-collections-table database)
121 (%drop-midi-compositions-table database)
122 (%drop-midi-events-table database)
123 (%drop-midi-tempi-table database)
124 (%drop-midi-timesigs-table database)
125 (%drop-midi-keysigs-table database))
126
127 (defun %drop-midi-collections-table (database)
128 (clsql:drop-table "midi_db_collections"
129 :if-does-not-exist :ignore
130 :database database))
131
132 (defun %drop-midi-compositions-table (database)
133 (clsql:drop-table "midi_db_compositions"
134 :if-does-not-exist :ignore
135 :database database))
136
137 (defun %drop-midi-events-table (database)
138 (clsql:drop-table "midi_db_events"
139 :if-does-not-exist :ignore
140 :database database))
141
142 (defun %drop-midi-constituent-headers-table (database)
143 (clsql:drop-table "midi_db_constituent_headers"
144 :if-does-not-exist :ignore
145 :database database))
146
147 (defun %drop-midi-tempi-table (database)
148 (clsql:drop-table "midi_db_tempi"
149 :if-does-not-exist :ignore
150 :database database))
151
152 (defun %drop-midi-timesigs-table (database)
153 (clsql:drop-table "midi_db_timesigs"
154 :if-does-not-exist :ignore
155 :database database))
156
157 (defun %drop-midi-keysigs-table (database)
158 (clsql:drop-table "midi_db_keysigs"
159 :if-does-not-exist :ignore
160 :database database))