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