comparison implementations/midi-db/db-insert-functions.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)
+
+(defun register-new-collection (collection-name description database)
+  (clsql:with-transaction (:database database)
+    "Error checking needed for duplicates."
+    #.(clsql:locally-enable-sql-reader-syntax)
+    (clsql:insert-records
+     :into "midi_db_collections"
+     :attributes '([collection-name]
+		   [description])
+     :values (list collection-name description)
+     :database database)
+    #.(clsql:locally-disable-sql-reader-syntax)
+    (make-midi-db-collection-identifier ;return new collection identifier
+     (clsql-mysql::mysql-insert-id
+      (clsql-mysql::database-mysql-ptr
+       database)))))
+
+(defvar *import-file* (pathname "/tmp/midi-db.txt"))
+
+(defun import-composition (composition collection-identifier database)
+  #.(clsql:locally-enable-sql-reader-syntax)
+  (let* ((collection-id (collection-id collection-identifier))
+	 (filename (file-namestring (midifile-identifier-pathname
+				     (identifier composition))))
+	 (timebase (midi-timebase composition))
+	 (start (* (timepoint composition) timebase))
+	 (duration (* (duration composition) timebase))
+	 (composition-id))
+    ;; add composition header
+    (clsql:with-transaction (:database database)
+      (clsql:insert-records :into "midi_db_compositions"
+			    :attributes '([collection-id] [filename]
+					  [timebase] [start] [duration])
+			    :values (list collection-id
+					  filename
+					  timebase
+					  start
+					  duration)
+			    :database database)
+      (setf composition-id (clsql-mysql::mysql-insert-id
+			    (clsql-mysql::database-mysql-ptr
+			     database)))
+      #.(clsql:locally-disable-sql-reader-syntax)
+      ;; add tempo, timesig and keysig constituents
+      (%add-tempo-constituents collection-id composition-id
+			       (tempi composition) timebase database)
+      (%add-timesig-constituents collection-id composition-id
+				 (time-signatures composition)
+				 timebase database)
+      (%add-keysig-constituents collection-id composition-id
+				(key-signatures composition) timebase
+				database)
+      (write-to-db-file collection-id composition-id composition)
+      (clsql:execute-command
+       (concatenate 'string
+		    "LOAD DATA LOCAL INFILE '"
+		    (namestring *import-file*)
+		    "' INTO TABLE "
+		    "midi_db_events")
+       :database database)
+      (delete-file *import-file*)
+      (format t "Composition added to db, id: ~A~%" composition-id))))
+
+
+;;;=====================================================================
+;;; All the below are just helper functions.
+;;;=====================================================================
+
+(defun write-to-db-file (collection-id composition-id composition)
+  (let ((timebase (midi-timebase composition)))
+    (with-open-file (stream *import-file*
+			    :direction :output
+			    :if-exists :supersede
+			    :external-format :latin1)
+      (sequence:dosequence (event composition)
+	(format stream
+		"~S	~S	\\N	~{~S	~}1~%" ; 1 is the
+						       ; version number
+		collection-id composition-id
+		(if (amuse-utils:pitchedp event)
+		    (list (midi-track event)
+			  (midi-channel event)
+			  (midi-patch event)
+			  (midi-pitch-number event)
+			  (midi-velocity event)
+			  (* (timepoint event) timebase)
+			  (* (duration event) timebase))
+		    (list (midi-track event)
+			  (midi-channel event)
+			  0 ;FIXME no patch for perc?
+			  (midi-drum-sound event)
+			  (midi-velocity event)
+			  (* (timepoint event) timebase)
+			  (* (duration event) timebase)))))))
+    t)
+
+;;; FIXME: use macros here.
+
+(defun %add-tempo-constituents (collection-id composition-id tempi timebase
+			       database)
+  #.(clsql:locally-enable-sql-reader-syntax)
+  (loop ;with constituent-id
+     for tempo in tempi
+     do (clsql:insert-records :into "midi_db_tempi"
+			      :attributes '([collection-id]
+					    [composition-id]
+					    [start]
+					    [duration]
+					    [microsecs-per-crotchet])
+			      :values (list collection-id
+					    composition-id
+					    (* (timepoint tempo) timebase)
+					    (* (duration tempo) timebase)
+					    (microseconds-per-crotchet
+					     tempo))
+			      :database database))
+  #.(clsql:locally-disable-sql-reader-syntax))
+
+(defun %add-timesig-constituents (collection-id composition-id timesigs
+				  timebase database)
+  #.(clsql:locally-enable-sql-reader-syntax)
+  (loop for timesig in timesigs
+     do (clsql:insert-records :into "midi_db_timesigs"
+			      :attributes '([collection-id]
+					    [composition-id]
+					    [start]
+					    [duration]
+					    [numerator]
+					    [denominator])
+			      :values (list collection-id
+					    composition-id
+					    (* (timepoint timesig) timebase)
+					    (* (duration timesig) timebase)
+					    (time-signature-numerator
+					     timesig)
+					    (time-signature-denominator
+					     timesig))
+			      :database database))
+  #.(clsql:locally-disable-sql-reader-syntax))
+
+(defun %add-keysig-constituents (collection-id composition-id keysigs
+				 timebase database)
+  #.(clsql:locally-enable-sql-reader-syntax)
+  (loop for keysig in keysigs
+     do (clsql:insert-records :into "midi_db_keysigs"
+			      :attributes '([collection-id]
+					    [composition-id]
+					    [start]
+					    [duration]
+					    [mode]
+					    [sharp-count])
+			      :values (list collection-id
+					    composition-id
+					    (* (timepoint keysig) timebase)
+					    (* (duration keysig) timebase)
+					    (key-signature-mode
+					     keysig)
+					    (key-signature-sharps
+					     keysig))
+			      :database database))
+  #.(clsql:locally-disable-sql-reader-syntax))