comparison base/charm/database-setup.lisp @ 253:b5ffec94ae6d

some very sketchy Charm constituent code

+(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))