view implementations/midi-db/db-insert-functions.lisp @ 330:2fbff655ba47 tip

Removed cpitch-adj and cents SQL columns

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