view implementations/meredith/import->db.lisp @ 330:2fbff655ba47 tip

Removed cpitch-adj and cents SQL columns

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

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; Importing

(defun make-path (base-path file-prefix file-id file-suffix)
  (pathname (concatenate 'string
			 base-path
			 file-prefix
			 (princ-to-string file-id)
			 file-suffix)))

(defun raph-c-file->list-of-lists (path)
  (with-open-file (stream path)
     (loop
	for row-strings = (split-sequence:split-sequence
			   #\space (read-line stream nil)
			   :remove-empty-subseqs t)
	while row-strings
	collect (loop
		   for value in row-strings
		   collect (read-from-string value)
		   into row-objects
		   finally (return row-objects))
	into results
	finally (return (cdr results)))))

(defvar *import-file* (pathname "/tmp/raph-c.txt"))

(defun write-to-db-file (composition-id datapoints)
  (with-open-file (stream *import-file*
			  :direction :output
			  :if-exists :supersede
			  :external-format :latin1)
    (sequence:dosequence (datapoint datapoints)
      (format stream
	      "~S	\\N	~{~S	~}~%"
	      composition-id datapoint)))
  t)

(defun import-meredith-composition (event-lists &key description
				    database)
  "Import a composition into the database. A composition is a list of
lists (dataset in the SIA sense), where each internal list (datapoint)
represents an event. All datapoints are the same cardinality. The
description is a string of up to 255 characters."
  #.(clsql:locally-enable-sql-reader-syntax)
  (clsql:with-transaction
      (:database database)
    (let (composition-id)
      (clsql:insert-records :into "meredith_compositions"
			    :attributes '([description])
			    :values (list description)
			    :database database)
      #.(clsql:locally-disable-sql-reader-syntax)
      (setf composition-id (clsql-mysql::mysql-insert-id
			    (clsql-mysql::database-mysql-ptr
			     database)))
      (write-to-db-file composition-id event-lists)
      (clsql:execute-command
       (concatenate 'string
		    "LOAD DATA LOCAL INFILE '"
		    (namestring *import-file*)
		    "' INTO TABLE "
		    "meredith_events")
       :database database)
      (delete-file *import-file*)
      (format t "composition added to db, id: ~A~%" composition-id)
      (make-meredith-composition-identifier composition-id))))

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;; Import functions for specific datasets.

(defun import-bach-inv+sin (&optional (database *amuse-database*))
  "Imports all of Dave's encodings of Bach's Inventions and Sinfonias,
and assigns them all to a unique dataset."
  (clsql:with-transaction
      (:database database)
    (let ((amuse-dataset-identifier (make-new-dataset
				  "Bach Inventions and Sinfonias"
				  database)))
      (import-bach-inventions database amuse-dataset-identifier)
      (import-bach-sinfonias database amuse-dataset-identifier)
      amuse-dataset-identifier)))

(defun import-bach-inventions (database &optional amuse-dataset-identifier)
  "Imports all of Dave's encodings of Bach's Inventions."
  (let ((base-path "/home/jamie/Music/meredith-data/20060301raph-c/")
	(file-prefix "bachrasmussinventio0")
	(file-suffix "01.raph-c"))
    (loop for file-id from 772 to 786
       for composition-order from 1
       for composition-identifier =
	 (import-meredith-composition
	  (raph-c-file->list-of-lists
	   (make-path base-path file-prefix file-id file-suffix))
	  :description (concatenate 'string
				    "Bach Invention No."
				    (princ-to-string composition-order)
				    " BWV "
				    (princ-to-string file-id))
	  :database database)
       do (when amuse-dataset-identifier
	    (assign-composition-to-dataset
	     composition-identifier amuse-dataset-identifier database)))))

(defun import-bach-sinfonias (database &optional amuse-dataset-identifier)
  "Imports all of Dave's encodings of Bach's Sinfonias."
  (let ((base-path "/home/jamie/Music/meredith-data/20060301raph-c/")
	(file-prefix "bachrasmusssinfonie0")
	(file-suffix "01.raph-c"))
    (loop for file-id from 787 to 801
       for composition-order from 1
       for composition-identifier =
	 (import-meredith-composition
	  (raph-c-file->list-of-lists
	   (make-path base-path file-prefix file-id file-suffix))
	  :description (concatenate 'string
				    "Bach Sinfonia No."
				    (princ-to-string composition-order)
				    " BWV "
				    (princ-to-string file-id))
	  :database database)
       do (when amuse-dataset-identifier
	    (assign-composition-to-dataset
	     composition-identifier amuse-dataset-identifier database)))))