j@318: (cl:in-package #:amuse-charm)
j@318: 
j@318: ;;;=====================================================================
j@318: ;;; CHARM database setup functions
j@318: ;;;=====================================================================
j@318: 
j@318: (defun create-charm-db-tables (database)
j@318:   (%create-constituent-header-table database)
j@318:   (%create-constituent-particle-table database)
j@318:   (%create-constituent-stored-routines database))
j@318: 
j@318: (defun drop-charm-db-tables (database)
j@318:   (%drop-constituent-header-table database)
j@318:   (%drop-constituent-particle-table database)
j@318:   (%drop-constituent-stored-routines database))
j@318: 
j@318: 
j@318: ;;;=====================================================================
j@318: ;;; Helper functions
j@318: ;;;=====================================================================
j@318: 
j@318: (defun %create-constituent-header-table (database)
j@318:   (clsql:execute-command "
j@318: CREATE TABLE charm_constituent_headers (
j@318: constituent_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
j@318: implementation_id SMALLINT UNSIGNED NOT NULL,
j@318: parent_id INT UNSIGNED NOT NULL,
j@318: #start_time INTEGER UNSIGNED NOT NULL,
j@318: #duration INTEGER UNSIGNED NOT NULL,
j@318: ext_properties VARCHAR(255) NOT NULL,
j@318: int_properties VARCHAR(255) NOT NULL,
j@318: owner CHAR(16) NULL,
j@318: version SMALLINT UNSIGNED NOT NULL DEFAULT 1,
j@318: creation_timestamp TIMESTAMP NULL,
j@318: deletion_timestamp TIMESTAMP NULL,
j@318: INDEX impl_comp_index (implementation_id, parent_id))
j@318: engine = innodb;"
j@318: 			 :database database)
j@318:   (%create-constituent-header-triggers database))
j@318: 
j@318: (defun %create-constituent-header-triggers (database)
j@318:   (clsql:execute-command "
j@318: CREATE TRIGGER pre_insert BEFORE INSERT ON charm_constituent_headers
j@318: FOR EACH ROW
j@318: BEGIN
j@318: SET NEW.owner = SUBSTRING_INDEX(USER(),'@',1);
j@318: SET NEW.creation_timestamp = CURRENT_TIMESTAMP;
j@318: #SET NEW.implementation_id = @current_impl;
j@318: END;"
j@318: 			 :database database))
j@318: 
j@318: (defun %create-constituent-particle-table (database)
j@318:   (clsql:execute-command "
j@318: CREATE TABLE charm_constituent_particles (
j@318: constituent_id BIGINT UNSIGNED NOT NULL,
j@318: particle_id BIGINT UNSIGNED NOT NULL,
j@318: particle_type SET('e', 'c') NOT NULL,
j@318: version_added SMALLINT UNSIGNED NOT NULL,
j@318: version_removed SMALLINT UNSIGNED NULL,
j@318: INDEX particle_index (constituent_id))
j@318: engine = innodb;"
j@318: 			 :database database))
j@318: 
j@318: (defun %drop-constituent-header-table (database)
j@318:   (clsql:drop-table "charm_constituent_headers"
j@318: 		    :database database
j@318: 		    :if-does-not-exist :ignore))
j@318: 
j@318: (defun %drop-constituent-particle-table (database)
j@318:   (clsql:drop-table "charm_constituent_particles"
j@318: 		    :database database
j@318: 		    :if-does-not-exist :ignore))
j@318: 
j@318: (defun %drop-constituent-stored-routines (database)
j@318:   (clsql:execute-command "
j@318: DROP FUNCTION constituent_header_exists"
j@318: 			 :database database))
j@318: 
j@318: 
j@318: ;;;=====================================================================
j@318: ;;; Other server-side routines
j@318: ;;;=====================================================================
j@318: 
j@318: (defun %create-constituent-stored-routines (database)
j@318:   (clsql:execute-command "
j@318: CREATE FUNCTION constituent_header_exists (
j@318: #start int unsigned,
j@318: #dur int unsigned,
j@318: impl_id SMALLINT UNSIGNED,
j@318: par_id INT UNSIGNED,
j@318: external varchar(255),
j@318: intrinsic varchar(255))
j@318: RETURNS boolean
j@318: RETURN EXISTS(SELECT constituent_id
j@318: FROM charm_constituent_headers
j@318: WHERE implementation_id = impl_id
j@318: AND parent_id = par_id
j@318: AND ext_properties = external
j@318: AND int_properties = intrinsic);"
j@318: 			 :database database))