annotate base/database/charm/database-setup.lisp @ 318:c4e792b9b898

Add some ideas for charm constituents.

Not particularly useful in its own right, but contains some possibly
useful ideas related to the generalisation of db-compositions.

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

;;;=====================================================================
;;; CHARM database setup functions
;;;=====================================================================

(defun create-charm-db-tables (database)
  (%create-constituent-header-table database)
  (%create-constituent-particle-table database)
  (%create-constituent-stored-routines database))

(defun drop-charm-db-tables (database)
  (%drop-constituent-header-table database)
  (%drop-constituent-particle-table database)
  (%drop-constituent-stored-routines database))


;;;=====================================================================
;;; Helper functions
;;;=====================================================================

(defun %create-constituent-header-table (database)
  (clsql:execute-command "
CREATE TABLE charm_constituent_headers (
constituent_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
implementation_id SMALLINT UNSIGNED NOT NULL,
parent_id INT UNSIGNED NOT NULL,
#start_time INTEGER UNSIGNED NOT NULL,
#duration INTEGER UNSIGNED NOT NULL,
ext_properties VARCHAR(255) NOT NULL,
int_properties VARCHAR(255) NOT NULL,
owner CHAR(16) NULL,
version SMALLINT UNSIGNED NOT NULL DEFAULT 1,
creation_timestamp TIMESTAMP NULL,
deletion_timestamp TIMESTAMP NULL,
INDEX impl_comp_index (implementation_id, parent_id))
engine = innodb;"
			 :database database)
  (%create-constituent-header-triggers database))

(defun %create-constituent-header-triggers (database)
  (clsql:execute-command "
CREATE TRIGGER pre_insert BEFORE INSERT ON charm_constituent_headers
FOR EACH ROW
BEGIN
SET NEW.owner = SUBSTRING_INDEX(USER(),'@',1);
SET NEW.creation_timestamp = CURRENT_TIMESTAMP;
#SET NEW.implementation_id = @current_impl;
END;"
			 :database database))

(defun %create-constituent-particle-table (database)
  (clsql:execute-command "
CREATE TABLE charm_constituent_particles (
constituent_id BIGINT UNSIGNED NOT NULL,
particle_id BIGINT UNSIGNED NOT NULL,
particle_type SET('e', 'c') NOT NULL,
version_added SMALLINT UNSIGNED NOT NULL,
version_removed SMALLINT UNSIGNED NULL,
INDEX particle_index (constituent_id))
engine = innodb;"
			 :database database))

(defun %drop-constituent-header-table (database)
  (clsql:drop-table "charm_constituent_headers"
		    :database database
		    :if-does-not-exist :ignore))

(defun %drop-constituent-particle-table (database)
  (clsql:drop-table "charm_constituent_particles"
		    :database database
		    :if-does-not-exist :ignore))

(defun %drop-constituent-stored-routines (database)
  (clsql:execute-command "
DROP FUNCTION constituent_header_exists"
			 :database database))


;;;=====================================================================
;;; Other server-side routines
;;;=====================================================================

(defun %create-constituent-stored-routines (database)
  (clsql:execute-command "
CREATE FUNCTION constituent_header_exists (
#start int unsigned,
#dur int unsigned,
impl_id SMALLINT UNSIGNED,
par_id INT UNSIGNED,
external varchar(255),
intrinsic varchar(255))
RETURNS boolean
RETURN EXISTS(SELECT constituent_id
FROM charm_constituent_headers
WHERE implementation_id = impl_id
AND parent_id = par_id
AND ext_properties = external
AND int_properties = intrinsic);"
			 :database database))