view base/database/datasets-functions.lisp @ 330:2fbff655ba47 tip

Removed cpitch-adj and cents SQL columns

(cl:in-package #:amuse-database-admin)

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;; Constructors

(defun make-amuse-dataset-identifier (dataset-id)
  (make-instance 'amuse-dataset-identifier
		 :dataset-id dataset-id))

(defun %make-amuse-dataset (dataset-identifier description
			    composition-identifiers)
  (make-instance 'amuse-dataset
		 :%data composition-identifiers
		 :identifier dataset-identifier
		 :description description))


;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;; Database functions

(defun make-new-dataset (description &optional (database
						*amuse-database*))
  "A dataset is a set of pieces used for a particualar analytical
task. A dataset is not necessarily the same thing as a corpus or
collection (are these things different?). Corpus indicates that a set
of pieces have been curated in some way and in that sense `belong
together'. A dataset is just simply a set of pieces gathered together
to analyse, and the pieces can be from any corpus or backend."
  (let (dataset-id)
    #.(clsql:locally-enable-sql-reader-syntax)
    (clsql:insert-records :into "amuse_datasets"
			  :attributes '([description])
			  :values (list description)
			  :database database)
    #.(clsql:locally-disable-sql-reader-syntax)
    (setf dataset-id (clsql-mysql::mysql-insert-id
		      (clsql-mysql::database-mysql-ptr
		       database)))
    (make-amuse-dataset-identifier dataset-id)))

(defun assign-composition-to-dataset (composition-identifier
				      dataset-identifier
				      &optional (database
						 *amuse-database*))
  (clsql:execute-command (format nil "
INSERT INTO amuse_datasets_join
SET dataset_id := ~S,
implementation_id := (SELECT get_impl_id('~A')),
composition_id := ~S;"
					(dataset-id dataset-identifier)
					(implementation-namestring
					 composition-identifier)
					(composition-id
					 composition-identifier)))
			 :database database)

(defun assign-compositions-to-dataset (composition-identifiers
				       dataset-identifier
				       &optional (database
						  *amuse-database*))
  "This should be done more sensibly."
  (loop for identifier in composition-identifiers
     do (assign-composition-to-dataset identifier
				       dataset-identifier
				       database)))

(defun make-composition-identifiers-from-file (package pathname)
  "This reads a file that contains one id per line, and returns a list
of composition-identifiers (specialised on package). It is useful for
reading files that have been exported from the database, for use with
the above functions."
  (with-open-file (stream pathname :direction :input)
    (loop for id = (read stream nil)
       while id
       collect (make-composition-identifier package id))))

(defun get-dataset (dataset-identifier &optional (database
						  *amuse-database*))
  (let ((dataset-header (clsql:query (format nil "
SELECT description
FROM amuse_datasets
WHERE dataset_id = ~S" (dataset-id dataset-identifier))
				     :database database
				     :flatp t
				     :field-names nil))
	(dataset-rows (clsql:query (format nil "
SELECT implementation_name, composition_id
FROM amuse_datasets_join
LEFT JOIN amuse_implementations
USING (implementation_id)
WHERE dataset_id = ~S"
					   (dataset-id
					    dataset-identifier))
				   :flatp t
				   :field-names nil
				   :database database)))
    (%make-amuse-dataset dataset-identifier (car dataset-header)
			 (%init-dataset-rows dataset-rows))))

(defun %init-dataset-rows (dataset-rows)
  (loop for row in dataset-rows
     collect (make-composition-identifier (find-package
					   (first row)) (second row))
     into composition-identifiers
     finally (return composition-identifiers)))