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