r2841 - in trunk: . lims_upds programs/us_astfem_sim
Author: gegorbet Date: 2019-11-06 22:52:39 +0000 (Wed, 06 Nov 2019) New Revision: 2841 Added: trunk/lims_upds/ trunk/lims_upds/add_RIProfile.sql trunk/lims_upds/add_autoflow.sql trunk/lims_upds/add_data_desc.sql trunk/lims_upds/add_expDesign.sql trunk/lims_upds/add_extincp.sql trunk/lims_upds/add_imageBuffer_table.sql trunk/lims_upds/add_image_filename.sql trunk/lims_upds/add_manual.sql trunk/lims_upds/add_mrecs.sql trunk/lims_upds/add_noise_desc.sql trunk/lims_upds/add_notice.sql trunk/lims_upds/add_protocol.sql trunk/lims_upds/add_radialcal.sql trunk/lims_upds/add_speedSteps.sql trunk/lims_upds/add_speeds.sql trunk/lims_upds/add_timeState.sql trunk/lims_upds/alter_abstractCenterpiece.sql trunk/lims_upds/alter_abstractCenterpiece2.sql trunk/lims_upds/alter_analyte.sql trunk/lims_upds/alter_aprofile.sql trunk/lims_upds/alter_cluster_auth-2.sql trunk/lims_upds/alter_cluster_auth.sql trunk/lims_upds/alter_cluster_auth_tres.sql trunk/lims_upds/alter_dmga_settings.sql trunk/lims_upds/alter_edited.sql trunk/lims_upds/alter_exptype.sql trunk/lims_upds/alter_gfac_01.sql trunk/lims_upds/alter_hpcreq.sql trunk/lims_upds/alter_hpcreq2.sql trunk/lims_upds/alter_hpcresdat.sql trunk/lims_upds/alter_instrument.sql trunk/lims_upds/alter_instrument_work.sql trunk/lims_upds/alter_metadata.sql trunk/lims_upds/alter_permits.sql trunk/lims_upds/alter_project.sql trunk/lims_upds/alter_reportDocument.sql trunk/lims_upds/alter_ripro_type.sql trunk/lims_upds/count_users.sh trunk/lims_upds/delete_lab2.sh trunk/lims_upds/delete_noise.sql trunk/lims_upds/desc_cluster_auth.sql trunk/lims_upds/drop-spectrum-constraints.sql trunk/lims_upds/drop_manual.sql trunk/lims_upds/dump_project.sh trunk/lims_upds/find_user.sh trunk/lims_upds/getdbpw trunk/lims_upds/list_db_info.sh trunk/lims_upds/list_metadata.sh trunk/lims_upds/list_people.sh trunk/lims_upds/list_people.sql trunk/lims_upds/list_permits.sql trunk/lims_upds/new_jquery.sh trunk/lims_upds/old_jquery.sh trunk/lims_upds/show_bandform.sh trunk/lims_upds/show_latest_users.sh trunk/lims_upds/show_projguid.sh trunk/lims_upds/svn_info.sh trunk/lims_upds/temp.sh trunk/lims_upds/upd_people_lev4.sh trunk/lims_upds/upd_people_procs.sh trunk/lims_upds/update-local.sh trunk/lims_upds/update-report-tables.sql trunk/lims_upds/update-us3-autoflow-procs.sh trunk/lims_upds/update-us3-db.sh trunk/lims_upds/update-us3-hardware-procs.sh trunk/lims_upds/update-us3-lims.sh trunk/lims_upds/update-us3-procs.sh trunk/lims_upds/update_CustomGrid.sql trunk/lims_upds/update_dblist.sh trunk/lims_upds/update_dmga.sql trunk/lims_upds/update_lev4_people.sql trunk/lims_upds/update_model_desc.sql trunk/lims_upds/update_pcsa.sql trunk/lims_upds/update_rotors.sql trunk/lims_upds/update_rotors2.sql trunk/lims_upds/us3_add_autoflow.sh trunk/lims_upds/us3_add_exper.sh trunk/lims_upds/us3_add_extincp.sh trunk/lims_upds/us3_add_extincp.sql trunk/lims_upds/us3_add_manual.sh trunk/lims_upds/us3_add_mrecs.sh trunk/lims_upds/us3_add_mrecsprocs.sh trunk/lims_upds/us3_add_notice.sh trunk/lims_upds/us3_add_proto.sh trunk/lims_upds/us3_add_radialCal_table_cch.sh trunk/lims_upds/us3_add_speeds.sh trunk/lims_upds/us3_add_timestate.sh trunk/lims_upds/us3_alter_analyte.sh trunk/lims_upds/us3_alter_aprofile.sh trunk/lims_upds/us3_alter_bufcomp.sh trunk/lims_upds/us3_alter_hpcanareq.sh trunk/lims_upds/us3_alter_instrument.sh trunk/lims_upds/us3_alter_instrument_cauma3.sh trunk/lims_upds/us3_alter_instrument_cch.sh trunk/lims_upds/us3_alter_metadata.sh trunk/lims_upds/us3_change_bc.sh trunk/lims_upds/us3_count_allusers.sql trunk/lims_upds/us3_drop_manual.sh trunk/lims_upds/us3_exp_procs.sql trunk/lims_upds/us3_insert_bc.sh trunk/lims_upds/us3_model_procs.sql trunk/lims_upds/us3_people_procs.sql trunk/lims_upds/us3_show_bc.sh trunk/lims_upds/us3_timestate_procs.sql trunk/lims_upds/us3_upd_buff_procs.sh trunk/lims_upds/us3_upd_dmga.sh trunk/lims_upds/us3_upd_pcsa.sh trunk/lims_upds/us3_upd_proj_procs.sh trunk/lims_upds/us3_upd_project.sh trunk/lims_upds/us3_upd_proto_procs.sh trunk/lims_upds/us3_upd_solu_procs.sh trunk/lims_upds/us3_update_admin_passwd.sh trunk/lims_upds/us3_update_admin_passwd.sql trunk/lims_upds/us3_update_anabuf.sh trunk/lims_upds/us3_update_clusters.sh trunk/lims_upds/us3_update_exp.sh trunk/lims_upds/us3_update_expdata.sh trunk/lims_upds/us3_update_expdata_t.sh trunk/lims_upds/us3_update_groupcount.sh trunk/lims_upds/us3_update_groupcount.sql trunk/lims_upds/us3_update_model.sh trunk/lims_upds/us3_update_timestate.sh Modified: trunk/programs/us_astfem_sim/us_astfem_sim.cpp Log: new lims updates scripts directory in ~us3/lims/database/lims_upds Added: trunk/lims_upds/add_RIProfile.sql =================================================================== --- trunk/lims_upds/add_RIProfile.sql (rev 0) +++ trunk/lims_upds/add_RIProfile.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,3 @@ +ALTER TABLE experiment + ADD COLUMN RIProfile LONGTEXT NULL + AFTER comment; Added: trunk/lims_upds/add_autoflow.sql =================================================================== --- trunk/lims_upds/add_autoflow.sql (rev 0) +++ trunk/lims_upds/add_autoflow.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,25 @@ +-- ----------------------------------------------------- +-- Table autoflow +-- ----------------------------------------------------- +DROP TABLE IF EXISTS autoflow; + +CREATE TABLE IF NOT EXISTS autoflow ( + ID int(11) NOT NULL AUTO_INCREMENT , + protName varchar(80) NULL, + cellChNum int(10) NULL, + tripleNum int(10) NULL, + duration int(10) NULL, + runName varchar(300) NULL, + expID int(10) NULL, + runID int(10) NULL, + status enum('LIVE_UPDATE','EDITING','ANALYSIS','REPORT') NOT NULL, + dataPath varchar(300) NULL, + optimaName varchar(300) NULL, + runStarted TIMESTAMP NULL, + invID INT NULL, + corrRadii enum('YES', 'NO') NOT NULL, + created TIMESTAMP NULL, + PRIMARY KEY (ID) ) +ENGINE = InnoDB; + + Added: trunk/lims_upds/add_data_desc.sql =================================================================== --- trunk/lims_upds/add_data_desc.sql (rev 0) +++ trunk/lims_upds/add_data_desc.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,7 @@ +-- +-- Add the data description field to the reports. +-- + +ALTER TABLE reportTriple + ADD COLUMN dataDescription VARCHAR(255) NOT NULL DEFAULT '' + AFTER triple; Added: trunk/lims_upds/add_expDesign.sql =================================================================== --- trunk/lims_upds/add_expDesign.sql (rev 0) +++ trunk/lims_upds/add_expDesign.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,3 @@ +ALTER TABLE project + ADD COLUMN expDesign TEXT NULL + AFTER AUC_questions; Added: trunk/lims_upds/add_extincp.sql =================================================================== --- trunk/lims_upds/add_extincp.sql (rev 0) +++ trunk/lims_upds/add_extincp.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,15 @@ +-- ----------------------------------------------------- +-- Table extinctionProfile +-- ----------------------------------------------------- +DROP TABLE IF EXISTS extinctionProfile; + +CREATE TABLE extinctionProfile ( + profileID int(11) NOT NULL AUTO_INCREMENT , + componentID int(11) NOT NULL , + componentType enum( 'Buffer', 'Analyte', 'Sample' ) NOT NULL, + valueType enum( 'absorbance', 'molarExtinction', 'massExtinction' ) NOT NULL, + xml longtext DEFAULT NULL , + PRIMARY KEY (profileID) , + INDEX ndx_component_ID (componentID ASC) ) +ENGINE = InnoDB; + Added: trunk/lims_upds/add_imageBuffer_table.sql =================================================================== --- trunk/lims_upds/add_imageBuffer_table.sql (rev 0) +++ trunk/lims_upds/add_imageBuffer_table.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,25 @@ +-- +-- add_imageBuffer_table.sql +-- +-- creates the table +-- + +CREATE TABLE IF NOT EXISTS imageBuffer ( + imageID int(11) NOT NULL , + bufferID int(11) NOT NULL , + PRIMARY KEY (imageID) , + INDEX ndx_imageBuffer_imageID (imageID ASC) , + INDEX ndx_imageBuffer_bufferID (bufferID ASC) , + CONSTRAINT fk_imageBuffer_imageID + FOREIGN KEY (imageID ) + REFERENCES image (imageID ) + ON DELETE CASCADE + ON UPDATE CASCADE, + CONSTRAINT fk_imageBuffer_bufferID + FOREIGN KEY (bufferID ) + REFERENCES buffer (bufferID ) + ON DELETE CASCADE + ON UPDATE CASCADE) +ENGINE = InnoDB; + + Added: trunk/lims_upds/add_image_filename.sql =================================================================== --- trunk/lims_upds/add_image_filename.sql (rev 0) +++ trunk/lims_upds/add_image_filename.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,7 @@ +-- +-- Add a filename field to the gel images. +-- + +ALTER TABLE image + ADD COLUMN filename VARCHAR(255) NOT NULL DEFAULT '' + AFTER gelPicture; Added: trunk/lims_upds/add_manual.sql =================================================================== --- trunk/lims_upds/add_manual.sql (rev 0) +++ trunk/lims_upds/add_manual.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,3 @@ +ALTER TABLE buffer + ADD COLUMN manual tinyint(1) NOT NULL DEFAULT 0 + AFTER density; Added: trunk/lims_upds/add_mrecs.sql =================================================================== --- trunk/lims_upds/add_mrecs.sql (rev 0) +++ trunk/lims_upds/add_mrecs.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,23 @@ +-- ----------------------------------------------------- +-- Table pcsa_modelrecs +-- ----------------------------------------------------- +DROP TABLE IF EXISTS pcsa_modelrecs ; + +CREATE TABLE IF NOT EXISTS pcsa_modelrecs ( + mrecsID int(11) NOT NULL AUTO_INCREMENT , + editedDataID int(11) NOT NULL DEFAULT 1, + modelID int(11) NOT NULL DEFAULT 0, + mrecsGUID CHAR(36) NOT NULL UNIQUE , + description VARCHAR(160) NULL DEFAULT NULL, -- includes 80 for runID and 80 for other + xml LONGTEXT NULL DEFAULT NULL , + lastUpdated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , + PRIMARY KEY (mrecsID) , + INDEX ndx_mrecs_editedDataID (editedDataID ASC) , + CONSTRAINT fk_mrecs_editDataID + FOREIGN KEY (editedDataID ) + REFERENCES editedData (editedDataID ) + ON DELETE NO ACTION + ON UPDATE CASCADE) +ENGINE = InnoDB; + + Added: trunk/lims_upds/add_noise_desc.sql =================================================================== --- trunk/lims_upds/add_noise_desc.sql (rev 0) +++ trunk/lims_upds/add_noise_desc.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,7 @@ +-- +-- Update the model description to be up to 160 characters +-- + +ALTER TABLE noise + ADD COLUMN description VARCHAR(160) NULL DEFAULT NULL + AFTER noiseType; Added: trunk/lims_upds/add_notice.sql =================================================================== --- trunk/lims_upds/add_notice.sql (rev 0) +++ trunk/lims_upds/add_notice.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,18 @@ +-- ----------------------------------------------------- +-- Table notice +-- ----------------------------------------------------- +DROP TABLE IF EXISTS notice ; + +CREATE TABLE IF NOT EXISTS notice ( + id int(11) NOT NULL AUTO_INCREMENT , + type enum('info','warn','crit'), + revision VARCHAR(12) NULL DEFAULT NULL, + message LONGTEXT NULL DEFAULT NULL , + lastUpdated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , + PRIMARY KEY (id) ) +ENGINE = InnoDB; + +## Grants for us3_notice@% ## +GRANT USAGE ON *.* TO 'us3_notice'@'%' IDENTIFIED BY PASSWORD '*95BD446FAE62A858AB140023019D162269B6B9F6'; +GRANT ALL PRIVILEGES ON `us3_notice`.* TO 'us3_notice'@'%'; + Added: trunk/lims_upds/add_protocol.sql =================================================================== --- trunk/lims_upds/add_protocol.sql (rev 0) +++ trunk/lims_upds/add_protocol.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,46 @@ +-- ----------------------------------------------------- +-- Table protocol +-- ----------------------------------------------------- +DROP TABLE IF EXISTS protocol ; + +CREATE TABLE IF NOT EXISTS protocol ( + protocolID int(11) NOT NULL AUTO_INCREMENT , + protocolGUID char(36) NOT NULL UNIQUE , + description varchar(160) NOT NULL UNIQUE , + xml longtext NOT NULL , + optimaHost varchar(24) NOT NULL , + dateUpdated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , + rotorID int(11) NULL , + speed1 int(11) NULL , + duration float NULL , + usedcells int(11) NULL , + estscans int(11) NULL , + solution1 varchar(80) NULL , + solution2 varchar(80) NULL , + wavelengths int(11) NULL , + PRIMARY KEY (protocolID) ) +ENGINE = InnoDB; + + +-- ----------------------------------------------------- +-- Table prototcolPerson +-- ----------------------------------------------------- +DROP TABLE IF EXISTS protocolPerson ; + +CREATE TABLE IF NOT EXISTS protocolPerson ( + protocolID int(11) NOT NULL , + personID int(11) NOT NULL , + INDEX ndx_protocolPerson_personID (personID ASC) , + INDEX ndx_protocolPerson_protocolID (protocolID ASC) , + CONSTRAINT fk_protocolPerson_personID + FOREIGN KEY (personID ) + REFERENCES people (personID ) + ON DELETE CASCADE + ON UPDATE CASCADE, + CONSTRAINT fk_protocolPerson_protocolID + FOREIGN KEY (protocolID ) + REFERENCES protocol (protocolID ) + ON DELETE NO ACTION + ON UPDATE CASCADE) +ENGINE = InnoDB; + Added: trunk/lims_upds/add_radialcal.sql =================================================================== --- trunk/lims_upds/add_radialcal.sql (rev 0) +++ trunk/lims_upds/add_radialcal.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,15 @@ +-- ----------------------------------------------------- +-- Table radialCalibration +-- ----------------------------------------------------- +DROP TABLE IF EXISTS radialCalibration ; + +CREATE TABLE IF NOT EXISTS radialCalibration ( + radialCalID int(11) NOT NULL AUTO_INCREMENT , + radialCalGUID char(36) NOT NULL UNIQUE , + speed int(11) NOT NULL DEFAULT 0 , + rotorCalID int(11) NOT NULL DEFAULT 0 , + dateUpdated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , + PRIMARY KEY (radialCalID) ) +ENGINE = InnoDB; + + Added: trunk/lims_upds/add_speedSteps.sql =================================================================== --- trunk/lims_upds/add_speedSteps.sql (rev 0) +++ trunk/lims_upds/add_speedSteps.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,32 @@ +-- +-- add_speedSteps.sql +-- +-- creates the table +-- + +CREATE TABLE IF NOT EXISTS speedstep ( + speedstepID int(11) NOT NULL AUTO_INCREMENT , + experimentID int(11) NOT NULL , + scans int(11) NOT NULL , + durationhrs int(11) NOT NULL , + durationmins double NOT NULL , + delayhrs int(11) NOT NULL , + delaymins double NOT NULL , + rotorspeed int(11) NOT NULL , + acceleration int(11) NOT NULL , + accelerflag tinyint(1) DEFAULT 1 , + w2tfirst float NOT NULL , + w2tlast float NOT NULL , + timefirst int(11) NOT NULL , + timelast int(11) NOT NULL , + setspeed int(11) NOT NULL , + avgspeed float NOT NULL , + speedsdev float NOT NULL , + PRIMARY KEY (speedstepID) , + CONSTRAINT fk_speedstep_experimentID + FOREIGN KEY (experimentID) + REFERENCES experiment (experimentID) + ON DELETE CASCADE + ON UPDATE CASCADE) +ENGINE = InnoDB; + Added: trunk/lims_upds/add_speeds.sql =================================================================== --- trunk/lims_upds/add_speeds.sql (rev 0) +++ trunk/lims_upds/add_speeds.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,5 @@ +ALTER TABLE speedstep + ADD COLUMN setspeed int(11) NOT NULL DEFAULT 0 + AFTER timelast, + ADD COLUMN avgspeed float NOT NULL DEFAULT 0.0, + ADD COLUMN speedsdev float NOT NULL DEFAULT 0.0; Added: trunk/lims_upds/add_timeState.sql =================================================================== --- trunk/lims_upds/add_timeState.sql (rev 0) +++ trunk/lims_upds/add_timeState.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,21 @@ +-- +-- add_timeState.sql +-- +-- creates the table +-- + +CREATE TABLE IF NOT EXISTS timestate ( + timestateID int(11) NOT NULL AUTO_INCREMENT , + experimentID int(11) NOT NULL , + filename varchar(255) NOT NULL default '', + definitions longtext, + data longblob, + lastUpdated TIMESTAMP NULL , + PRIMARY KEY (timestateID) , + CONSTRAINT fk_timestate_experimentID + FOREIGN KEY (experimentID) + REFERENCES experiment (experimentID) + ON DELETE CASCADE + ON UPDATE CASCADE) +ENGINE = InnoDB; + Added: trunk/lims_upds/alter_abstractCenterpiece.sql =================================================================== --- trunk/lims_upds/alter_abstractCenterpiece.sql (rev 0) +++ trunk/lims_upds/alter_abstractCenterpiece.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,9 @@ +-- +-- alter_abstractCenterpiece.sql +-- +-- change the fill method +-- + +ALTER TABLE abstractCenterpiece + MODIFY COLUMN loadMethod enum('top', 'side') NULL; + Added: trunk/lims_upds/alter_abstractCenterpiece2.sql =================================================================== --- trunk/lims_upds/alter_abstractCenterpiece2.sql (rev 0) +++ trunk/lims_upds/alter_abstractCenterpiece2.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,12 @@ +-- +-- alter_abstractCenterpiece.sql +-- +-- change the fill method +-- + +ALTER TABLE abstractCenterpiece + MODIFY COLUMN + shape enum( 'standard', 'rectangular', 'circular', 'synthetic', + 'band forming', 'meniscus matching', 'sector' ) + NOT NULL DEFAULT 'standard'; + Added: trunk/lims_upds/alter_analyte.sql =================================================================== --- trunk/lims_upds/alter_analyte.sql (rev 0) +++ trunk/lims_upds/alter_analyte.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,32 @@ +-- +-- Alter/Update analyte and buffer tables for gradientForming and manual +-- + +ALTER TABLE analyte + ADD COLUMN gradientForming tinyint(1) NOT NULL DEFAULT '0' + AFTER molecularWeight; + +ALTER TABLE analyte + CHANGE doubleStranded doubleStranded tinyint(1) NOT NULL default '0'; + +ALTER TABLE analyte + CHANGE complement complement tinyint(1) NOT NULL default '0'; + +ALTER TABLE analyte + CHANGE _3prime _3prime tinyint(1) NOT NULL default '0'; + +ALTER TABLE analyte + CHANGE _5prime _5prime tinyint(1) NOT NULL default '0'; + +ALTER TABLE buffer + ADD COLUMN manual tinyint(1) NOT NULL DEFAULT '0' + AFTER density; + +ALTER TABLE bufferComponent + ADD COLUMN c_range text + AFTER density; + +ALTER TABLE bufferComponent + ADD COLUMN gradientForming tinyint(1) NOT NULL DEFAULT '0' + AFTER c_range; + Added: trunk/lims_upds/alter_aprofile.sql =================================================================== --- trunk/lims_upds/alter_aprofile.sql (rev 0) +++ trunk/lims_upds/alter_aprofile.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,80 @@ +-- +-- Alter analysis profile and related tables; +-- + +-- ----------------------------------------------------- +-- Table protocol (add column) +-- ----------------------------------------------------- +ALTER TABLE protocol + ADD COLUMN aprofileGUID char(36) NULL + AFTER wavelengths; + +-- ----------------------------------------------------- +-- -- Table experiment (replace column) +-- Table experiment (add column) +-- ----------------------------------------------------- +-- ALTER TABLE experiment +-- DROP COLUMN centrifugeProtocol; + +ALTER TABLE experiment + ADD COLUMN protocolGUID char(36) NULL + AFTER RIProfile; + +-- ----------------------------------------------------- +-- Table analysisprofile (new) +-- ----------------------------------------------------- +DROP TABLE IF EXISTS analysisprofile ; + +CREATE TABLE IF NOT EXISTS analysisprofile ( + aprofileID int(11) NOT NULL AUTO_INCREMENT , + aprofileGUID char(36) NOT NULL UNIQUE , + name TEXT NOT NULL , + xml LONGTEXT NOT NULL , + dateUpdated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , + PRIMARY KEY (aprofileID) ) +ENGINE = InnoDB; + +-- ----------------------------------------------------- +-- Table protocolAprofile (new link table) +-- ----------------------------------------------------- +DROP TABLE IF EXISTS protocolAprofile ; + +CREATE TABLE IF NOT EXISTS protocolAprofile ( + protocolID int(11) NOT NULL , + aprofileID int(11) NOT NULL , + INDEX ndx_protocolAprofile_aprofileID (aprofileID ASC) , + INDEX ndx_protocolAprofile_protocolID (protocolID ASC) , + CONSTRAINT fk_protocolAprofile_aprofileID + FOREIGN KEY (aprofileID ) + REFERENCES analysisprofile (aprofileID ) + ON DELETE CASCADE + ON UPDATE CASCADE, + CONSTRAINT fk_protocolAprofile_protocolID + FOREIGN KEY (protocolID ) + REFERENCES protocol (protocolID ) + ON DELETE NO ACTION + ON UPDATE CASCADE) +ENGINE = InnoDB; + +-- ----------------------------------------------------- +-- Table experimentProtocol (new link table) +-- ----------------------------------------------------- +DROP TABLE IF EXISTS experimentProtocol ; + +CREATE TABLE IF NOT EXISTS experimentProtocol ( + experimentID int(11) NOT NULL , + protocolID int(11) NOT NULL , + INDEX ndx_experimentProtocol_protocolID (protocolID ASC) , + INDEX ndx_experimentProtocol_experimentID (experimentID ASC) , + CONSTRAINT fk_experimentProtocol_protocolID + FOREIGN KEY (protocolID ) + REFERENCES protocol (protocolID ) + ON DELETE CASCADE + ON UPDATE CASCADE, + CONSTRAINT fk_experimentProtocol_experimentID + FOREIGN KEY (experimentID ) + REFERENCES experiment (experimentID ) + ON DELETE NO ACTION + ON UPDATE CASCADE) +ENGINE = InnoDB; + Added: trunk/lims_upds/alter_cluster_auth-2.sql =================================================================== --- trunk/lims_upds/alter_cluster_auth-2.sql (rev 0) +++ trunk/lims_upds/alter_cluster_auth-2.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,19 @@ +-- +-- Alter/Update clusterAuthorizations to reflect current clusters +-- + +ALTER TABLE people + CHANGE ClusterAuthorizations clusterAuthorizations varchar(255) NOT NULL; + +ALTER TABLE people + ALTER COLUMN clusterAuthorizations + SET DEFAULT 'stampede:lonestar5:comet:gordon:alamo'; + +UPDATE people + SET clusterAuthorizations='stampede:lonestar5:comet:gordon:alamo' + WHERE userlevel<4; + +UPDATE people + SET clusterAuthorizations='stampede:lonestar5:comet:gordon:alamo:alamo-local' + WHERE userlevel>3; + Added: trunk/lims_upds/alter_cluster_auth.sql =================================================================== --- trunk/lims_upds/alter_cluster_auth.sql (rev 0) +++ trunk/lims_upds/alter_cluster_auth.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,19 @@ +-- +-- Alter/Update clusterAuthorizations to reflect current clusters +-- + +-- ALTER TABLE people +-- CHANGE ClusterAuthorizations clusterAuthorizations varchar(255) NOT NULL; + +ALTER TABLE people + ALTER COLUMN clusterAuthorizations + SET DEFAULT 'lonestar5:stampede2:comet:jetstream'; + +UPDATE people + SET clusterAuthorizations='lonestar5:stampede2:comet:jetstream' + WHERE userlevel<4; + +UPDATE people + SET clusterAuthorizations='lonestar5:stampede2:comet:jetstream:jetstream-local' + WHERE userlevel>3; + Added: trunk/lims_upds/alter_cluster_auth_tres.sql =================================================================== --- trunk/lims_upds/alter_cluster_auth_tres.sql (rev 0) +++ trunk/lims_upds/alter_cluster_auth_tres.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,19 @@ +-- +-- Alter/Update clusterAuthorizations to reflect current clusters +-- + +-- ALTER TABLE people +-- CHANGE ClusterAuthorizations clusterAuthorizations varchar(255) NOT NULL; + +ALTER TABLE people + ALTER COLUMN clusterAuthorizations + SET DEFAULT 'trestles:stampede:lonestar:comet:gordon:alamo'; + +UPDATE people + SET clusterAuthorizations='trestles:stampede:lonestar:comet:gordon:alamo' + WHERE userlevel<4; + +UPDATE people + SET clusterAuthorizations='trestles:stampede:lonestar:comet:gordon:alamo:juropa:jacinto:alamo-local:jacinto-local' + WHERE userlevel>3; + Added: trunk/lims_upds/alter_dmga_settings.sql =================================================================== --- trunk/lims_upds/alter_dmga_settings.sql (rev 0) +++ trunk/lims_upds/alter_dmga_settings.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,44 @@ +-- +-- Alter/Update DMGA_Settings to the current settings columns +-- + +ALTER TABLE DMGA_Settings + CHANGE montecarlo_value mc_iterations int(11) NOT NULL default '1'; + +ALTER TABLE DMGA_Settings + CHANGE demes_value demes int(11) NOT NULL default '31'; + +ALTER TABLE DMGA_Settings + CHANGE genes_value genes int(11) NOT NULL default '100'; + +ALTER TABLE DMGA_Settings + CHANGE generations_value generations int(11) NOT NULL default '100'; + +ALTER TABLE DMGA_Settings + CHANGE crossover_value mutation int(11) NOT NULL default '50'; + +ALTER TABLE DMGA_Settings + CHANGE mutation_value crossover int(11) NOT NULL default '50'; + +ALTER TABLE DMGA_Settings + CHANGE plague_value plague int(11) NOT NULL default '4'; + +ALTER TABLE DMGA_Settings + CHANGE elitism_value elitism int(11) NOT NULL default '2'; + +ALTER TABLE DMGA_Settings + CHANGE migration_value migration int(11) NOT NULL default '3'; + +ALTER TABLE DMGA_Settings + CHANGE seed_value seed int(11) NOT NULL default '0'; + +ALTER TABLE DMGA_Settings + DROP COLUMN regularization_value + +ALTER TABLE DMGA_Settings + DROP COLUMN conc_threshold + +ALTER TABLE DMGA_Settings + ADD COLUMN p_grid int(11) NOT NULL DEFAULT '500' + AFTER migration_ratio; + Added: trunk/lims_upds/alter_edited.sql =================================================================== --- trunk/lims_upds/alter_edited.sql (rev 0) +++ trunk/lims_upds/alter_edited.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,7 @@ +-- +-- Alter the editedData table to permit NULL 'data' column +-- + +ALTER TABLE editedData + MODIFY COLUMN data LONGBLOB NULL DEFAULT NULL; + Added: trunk/lims_upds/alter_exptype.sql =================================================================== --- trunk/lims_upds/alter_exptype.sql (rev 0) +++ trunk/lims_upds/alter_exptype.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,10 @@ +-- +-- alter_abstractCenterpiece.sql +-- +-- change the fill method +-- + +ALTER TABLE experiment + MODIFY COLUMN type ENUM('velocity', 'equilibrium', 'diffusion', 'buoyancy','calibration', 'other') NULL + DEFAULT 'velocity'; + Added: trunk/lims_upds/alter_gfac_01.sql =================================================================== --- trunk/lims_upds/alter_gfac_01.sql (rev 0) +++ trunk/lims_upds/alter_gfac_01.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,10 @@ +-- +-- Alter analysis table for tarfile and status columns +-- + +ALTER TABLE analysis + MODIFY COLUMN tarfile longblob NULL; + +ALTER TABLE analysis + MODIFY COLUMN status enum('SUBMITTED','SUBMIT_TIMEOUT','RUNNING','RUN_TIMEOUT','DATA','DATA_TIMEOUT','COMPLETE','CANCELLED','CANCELED','FAILED','FAILED_DATA','ERROR','FINISHED','DONE') DEFAULT NULL; + Added: trunk/lims_upds/alter_hpcreq.sql =================================================================== --- trunk/lims_upds/alter_hpcreq.sql (rev 0) +++ trunk/lims_upds/alter_hpcreq.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,7 @@ +-- +-- Alter HPCAnalysisRequest for requestXMLFile and method columns +-- + +ALTER TABLE HPCAnalysisRequest + ADD COLUMN analType text NULL; + Added: trunk/lims_upds/alter_hpcreq2.sql =================================================================== --- trunk/lims_upds/alter_hpcreq2.sql (rev 0) +++ trunk/lims_upds/alter_hpcreq2.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,29 @@ +-- +-- Alter HPCAnalysisRequest for analType columns +-- + +UPDATE HPCAnalysisRequest + SET analType = '2DSA' + WHERE analType IS NULL + AND method = '2DSA'; + +UPDATE HPCAnalysisRequest + SET analType = '2DSA-CG' + WHERE analType IS NULL + AND method = '2DSA_CG'; + +UPDATE HPCAnalysisRequest + SET analType = 'GA' + WHERE analType IS NULL + AND method = 'GA'; + +UPDATE HPCAnalysisRequest + SET analType = 'DMGA' + WHERE analType IS NULL + AND method = 'DMGA'; + +UPDATE HPCAnalysisRequest + SET analType = 'PCSA' + WHERE analType IS NULL + AND method = 'PCSA'; + Added: trunk/lims_upds/alter_hpcresdat.sql =================================================================== --- trunk/lims_upds/alter_hpcresdat.sql (rev 0) +++ trunk/lims_upds/alter_hpcresdat.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,7 @@ +-- +-- Alter HPCAnalysisRequest for requestXMLFile and method columns +-- + +ALTER TABLE HPCAnalysisResultData + MODIFY COLUMN HPCAnalysisResultType enum('model','noise','job_stats','mrecs','unknown') NOT NULL default 'unknown'; + Added: trunk/lims_upds/alter_instrument.sql =================================================================== --- trunk/lims_upds/alter_instrument.sql (rev 0) +++ trunk/lims_upds/alter_instrument.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,17 @@ +-- +-- Alter instrument table by adding new column +-- + +ALTER TABLE instrument + ADD COLUMN optimaHost TEXT NULL AFTER radialCalID, + ADD COLUMN optimaPort int(11) NULL AFTER optimaHost, + ADD COLUMN optimaDBname TEXT NULL AFTER optimaPort, + ADD COLUMN optimaDBusername TEXT NULL AFTER optimaDBname, + ADD COLUMN optimaDBpassw BLOB NULL AFTER optimaDBusername, + ADD COLUMN chromaticAB TEXT NULL AFTER optimaDBpassw, + ADD COLUMN selected BOOL DEFAULT false AFTER chromaticAB, + ADD COLUMN opsys1 ENUM('UV/visible', 'Rayleigh Interference', 'Fluorescense', '(not installed)') NOT NULL AFTER selected, + ADD COLUMN opsys2 ENUM('UV/visible', 'Rayleigh Interference', 'Fluorescense', '(not installed)') NOT NULL AFTER opsys1, + ADD COLUMN opsys3 ENUM('UV/visible', 'Rayleigh Interference', 'Fluorescense', '(not installed)') NOT NULL AFTER opsys2, + ADD COLUMN RadCalWvl int(11) NULL AFTER opsys3; + Added: trunk/lims_upds/alter_instrument_work.sql =================================================================== --- trunk/lims_upds/alter_instrument_work.sql (rev 0) +++ trunk/lims_upds/alter_instrument_work.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,41 @@ +-- +-- Alter instrument table by adding new column +-- + + +--ALTER TABLE instrument +-- DROP COLUMN chromaticAB; + + +--ALTER TABLE instrument +-- ADD COLUMN chromaticAB TEXT NULL AFTER optimaDBpassw; + +--ALTER TABLE instrument +-- ADD COLUMN RadCalWvl TEXT NULL AFTER opsys3; + +ALTER TABLE instrument + ADD COLUMN optimaHost TEXT NULL AFTER radialCalID, + ADD COLUMN optimaPort int(11) NULL AFTER optimaHost, + ADD COLUMN optimaDBname TEXT NULL AFTER optimaPort, + ADD COLUMN optimaDBusername TEXT NULL AFTER optimaDBname, + ADD COLUMN optimaDBpassw BLOB NULL AFTER optimaDBusername, + ADD COLUMN chromaticAB TEXT NULL AFTER optimaDBpassw, + ADD COLUMN selected BOOL DEFAULT false AFTER chromaticAB, + ADD COLUMN opsys1 ENUM('UV/visible', 'Rayleigh Interference', 'Fluorescense', '(not installed)') NOT NULL AFTER selected, + ADD COLUMN opsys2 ENUM('UV/visible', 'Rayleigh Interference', 'Fluorescense', '(not installed)') NOT NULL AFTER opsys1, + ADD COLUMN opsys3 ENUM('UV/visible', 'Rayleigh Interference', 'Fluorescense', '(not installed)') NOT NULL AFTER opsys2, + ADD COLUMN RadCalWvl int(11) NULL AFTER opsys3; + + +--ALTER TABLE instrument +-- DROP COLUMN optimaHost, +-- DROP COLUMN optimaPort, +-- DROP COLUMN optimaDBname, +-- DROP COLUMN optimaDBusername, +-- DROP COLUMN optimaDBpassw, +-- DROP COLUMN chromaticAB, +-- DROP COLUMN selected, +-- DROP COLUMN opsys1, +-- DROP COLUMN opsys2, +-- DROP COLUMN opsys3, +-- DROP COLUMN RadCalWvl; Added: trunk/lims_upds/alter_metadata.sql =================================================================== --- trunk/lims_upds/alter_metadata.sql (rev 0) +++ trunk/lims_upds/alter_metadata.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,67 @@ +-- +-- Alter newus3.metadata table by inserting entries +-- + +INSERT INTO metadata SET + institution = 'Monash University', + inst_abbrev = 'Monash', + dbname = 'uslims3_Monash', + dbuser = 'Monash_user', + dbpasswd = 'xbs0uxkz', + dbhost = 'uslims3.latrobe.edu.au', + limshost = 'uslims3.latrobe.edu.au', + secure_user = 'Monash_sec', + secure_pw = '3chspe5h', + admin_fname = 'Sarah', + admin_lname = 'Atkinson', + admin_email = 'sarah.atkinson@monash.edu', + admin_pw = 'us3', + lab_name = 'Biomedical Discovery Institute', + lab_contact = 'Monash University', + location = 'Victoria, Australia', + instrument_name = 'XLI-1', + instrument_serial = '1234', + status = 'completed'; + +INSERT INTO metadata SET + institution = 'LaTrobe University', + inst_abbrev = 'latrobe', + dbname = 'uslims3_latrobe', + dbuser = 'latrobe_user', + dbpasswd = 'xn2n9hb7', + dbhost = 'uslims3.latrobe.edu.au', + limshost = 'uslims3.latrobe.edu.au', + secure_user = 'latrobe_sec', + secure_pw = 'g18htwp0', + admin_fname = 'Tao', + admin_lname = 'Nelson', + admin_email = 'tgnelson@students.latrobe.edu.au', + admin_pw = 'us3', + lab_name = 'Biomolecular Interaction Centre', + lab_contact = 'LaTrobe University', + location = 'Melbource, AU', + instrument_name = 'XLI-1', + instrument_serial = '1234', + status = 'completed'; + +INSERT INTO metadata SET + institution = 'The University of Melbourne', + inst_abbrev = 'unimelb', + dbname = 'uslims3_unimelb', + dbuser = 'unimelb_user', + dbpasswd = 'k1b841ph', + dbhost = 'uslims3.latrobe.edu.au', + limshost = 'uslims3.latrobe.edu.au', + secure_user = 'unimelb_sec', + secure_pw = 'hyqefs9j', + admin_fname = 'Matt', + admin_lname = 'Perugini', + admin_email = 'perugini@unimelb.edu.au', + admin_pw = 'us3', + lab_name = 'Biomolecular Interaction Centre', + lab_contact = 'University of Melbourne', + location = 'Melbource, AU', + instrument_name = 'XLI-1', + instrument_serial = '1234', + status = 'completed'; + Added: trunk/lims_upds/alter_permits.sql =================================================================== --- trunk/lims_upds/alter_permits.sql (rev 0) +++ trunk/lims_upds/alter_permits.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,9 @@ +-- +-- Alter the permits table to handle collaborations too +-- + +ALTER TABLE permits + DROP FOREIGN KEY fk_permits_instrumentID; + +ALTER TABLE permits + MODIFY COLUMN instrumentID INT(11) DEFAULT NULL; Added: trunk/lims_upds/alter_project.sql =================================================================== --- trunk/lims_upds/alter_project.sql (rev 0) +++ trunk/lims_upds/alter_project.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,10 @@ +-- +-- Alter/Update Project table to have lastUpdated field +-- + +ALTER TABLE project + ADD COLUMN lastUpdated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP + AFTER status; + +UPDATE project SET lastUpdated = now(); + Added: trunk/lims_upds/alter_reportDocument.sql =================================================================== --- trunk/lims_upds/alter_reportDocument.sql (rev 0) +++ trunk/lims_upds/alter_reportDocument.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,9 @@ +-- +-- alter_reportDocument.sql +-- +-- change the label field +-- + +ALTER TABLE reportDocument + MODIFY COLUMN label VARCHAR(160) NOT NULL DEFAULT ''; + Added: trunk/lims_upds/alter_ripro_type.sql =================================================================== --- trunk/lims_upds/alter_ripro_type.sql (rev 0) +++ trunk/lims_upds/alter_ripro_type.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,9 @@ +-- +-- alter_ripro_type.sql +-- +-- change the RIProfile type +-- + +ALTER TABLE experiment + MODIFY COLUMN RIProfile LONGTEXT DEFAULT ''; + Added: trunk/lims_upds/count_users.sh =================================================================== --- trunk/lims_upds/count_users.sh (rev 0) +++ trunk/lims_upds/count_users.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,16 @@ +# One time script to update all active US3 databases +# by creating the PCSA method + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +cd ~/lims/database + +##for db in `cat ~us3/database/dblist_upds.txt` +##for db in `cat ~us3/database/dblist_upds.txt` +##for db in uslims3_cauma3 +for db in `cat ~us3/lims/database/dblist.txt` + +do + echo "counting users from $db" + mysql $LOGIN $db < ./us3_count_allusers.sql +done Property changes on: trunk/lims_upds/count_users.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/delete_lab2.sh =================================================================== --- trunk/lims_upds/delete_lab2.sh (rev 0) +++ trunk/lims_upds/delete_lab2.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,15 @@ +# Script to delete second entry in lab table + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +CMD="delete from lab where name='UTHSCSA Lab #2'" + +##for db in uslims3_cauma3 +##for db in `cat dblist_upds.txt` +##for db in uslims3_devel +##for db in uslims3_AUCSOL +for db in `cat dblist.txt` + do + echo "delete from lab table of $db" + mysql ${LOGIN} -sNe "${CMD}" $db +done Property changes on: trunk/lims_upds/delete_lab2.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/delete_noise.sql =================================================================== --- trunk/lims_upds/delete_noise.sql (rev 0) +++ trunk/lims_upds/delete_noise.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,46 @@ +-- +-- Update HPC table constraints to allow underlying data to be deleted, +-- while HPC tables are not +-- + +SET FOREIGN_KEY_CHECKS=0; + +ALTER TABLE HPCDataset + DROP FOREIGN KEY fk_HPCDataset_editedDataID; + +ALTER TABLE HPCDataset + DROP INDEX ndx_HPCDataset_editedDataID; + +ALTER TABLE HPCDataset + MODIFY COLUMN editedDataID INT(11) DEFAULT NULL; + +ALTER TABLE HPCDataset + ADD FOREIGN KEY fk_HPCDataset_editedDataID (editedDataID) + REFERENCES editedData ( editedDataID ) + ON DELETE SET NULL + ON UPDATE NO ACTION; + +ALTER TABLE HPCDataset + ADD INDEX ndx_HPCDataset_editedDataID (editedDataID ASC); + +ALTER TABLE HPCRequestData + DROP FOREIGN KEY fk_HPCRequestData_noiseID; + +ALTER TABLE HPCRequestData + MODIFY COLUMN noiseID INT(11) DEFAULT NULL; + +ALTER TABLE HPCRequestData + ADD FOREIGN KEY fk_HPCRequestData_noiseID (noiseID) + REFERENCES noise (noiseID) + ON DELETE SET NULL + ON UPDATE NO ACTION; + +ALTER TABLE HPCRequestData + ADD INDEX ndx_HPCRequestData_noiseID ( noiseID ASC ); + +SET FOREIGN_KEY_CHECKS=1; + +DELETE noise +FROM noise LEFT JOIN model +ON ( noise.modelID = model.modelID ) +WHERE model.modelID IS NULL; Added: trunk/lims_upds/desc_cluster_auth.sql =================================================================== --- trunk/lims_upds/desc_cluster_auth.sql (rev 0) +++ trunk/lims_upds/desc_cluster_auth.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,2 @@ +DESC people; + Added: trunk/lims_upds/drop-spectrum-constraints.sql =================================================================== --- trunk/lims_upds/drop-spectrum-constraints.sql (rev 0) +++ trunk/lims_upds/drop-spectrum-constraints.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,12 @@ +-- +-- drop_spectrum_constraints.sql +-- +-- erase the constraints that go with the spectrum table; one or the other +-- will always fail +-- + +ALTER TABLE spectrum + DROP FOREIGN KEY fk_spectrum_bufferID; + +ALTER TABLE spectrum + DROP FOREIGN KEY fk_spectrum_analyteID; Added: trunk/lims_upds/drop_manual.sql =================================================================== --- trunk/lims_upds/drop_manual.sql (rev 0) +++ trunk/lims_upds/drop_manual.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1 @@ +ALTER TABLE buffer DROP COLUMN manual; Added: trunk/lims_upds/dump_project.sh =================================================================== --- trunk/lims_upds/dump_project.sh (rev 0) +++ trunk/lims_upds/dump_project.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,10 @@ +# Script to update the list of DBs to be altered + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +##CMD="select * from project where projectID=63" +##CMD="select * from project where projectID=66" +CMD="select * from project where projectID=64" + +mysql ${LOGIN} -sNe "${CMD}" uslims3_HI + Property changes on: trunk/lims_upds/dump_project.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/find_user.sh =================================================================== --- trunk/lims_upds/find_user.sh (rev 0) +++ trunk/lims_upds/find_user.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,26 @@ +#!/bin/bash +# One time script to update all active US3 LIMS code + +cd /data/srv/www/htdocs/uslims3 + +USER=$1 +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +CMD="SELECT fname,lname,country,organization FROM people WHERE lname like '" +CMD="${CMD}${USER}" +CMD="${CMD}%'" + echo "CMD=$CMD" +##CMD='SELECT fname,lname,country,organization FROM people' +##for db in `cat ~us3/lims/database/dblist.txt` +##for db in uslims3_cauma3 +##for db in `cat ~us3/lims/database/dblist_upds.txt` +##for db in `cat ~us3/lims/database/dblist.txt` +for db in `cat ~us3/lims/database/dblist.txt` +do + echo "== Searching $db for $USER ==" + pushd $db + echo $CMD | mysql $LOGIN $db + popd + sleep 1 + +done Property changes on: trunk/lims_upds/find_user.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/getdbpw =================================================================== --- trunk/lims_upds/getdbpw (rev 0) +++ trunk/lims_upds/getdbpw 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,5 @@ +#!/bin/bash +# getdbpw + +read -s -p "Password: " PASSW +export PASSW Property changes on: trunk/lims_upds/getdbpw ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/list_db_info.sh =================================================================== --- trunk/lims_upds/list_db_info.sh (rev 0) +++ trunk/lims_upds/list_db_info.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,14 @@ +# One time script to list country,org information +# for all active US3 databases + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +CMD='SELECT fname,lname,country,organization FROM people WHERE userlevel=3;' +##for db in uslims3_Uni_KN +for db in `cat dblist.txt` + +do + echo "=== DB: $db ===" + echo $CMD | mysql $LOGIN $db +done + Property changes on: trunk/lims_upds/list_db_info.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/list_metadata.sh =================================================================== --- trunk/lims_upds/list_metadata.sh (rev 0) +++ trunk/lims_upds/list_metadata.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,9 @@ +# Script to update the list of DBs to be altered + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +CMD="select * from metadata" +DB="newus3" + +mysql ${LOGIN} -sNe "$CMD" $DB >metadata_list.txt + Property changes on: trunk/lims_upds/list_metadata.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/list_people.sh =================================================================== --- trunk/lims_upds/list_people.sh (rev 0) +++ trunk/lims_upds/list_people.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,12 @@ +# One time script to list people in most-used databases + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +##for db in `cat dblist.txt` +for db in uslims3_CU_Boulder uslims3_NVAX + +do + echo "===========================================================" + echo "Email list from $db" + mysql $LOGIN $db < ./list_people.sql +done Property changes on: trunk/lims_upds/list_people.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/list_people.sql =================================================================== --- trunk/lims_upds/list_people.sql (rev 0) +++ trunk/lims_upds/list_people.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,4 @@ +SELECT email, submitTime +FROM HPCAnalysisRequest +WHERE STR_TO_DATE(submitTime,'%Y-%m-%d') > STR_TO_DATE('2018-09','%Y-%m-%d'); +ORDER BY email, submitTime DESC Added: trunk/lims_upds/list_permits.sql =================================================================== --- trunk/lims_upds/list_permits.sql (rev 0) +++ trunk/lims_upds/list_permits.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,5 @@ +-- +-- List permit table from a database +-- + +SELECT * from permits; Added: trunk/lims_upds/new_jquery.sh =================================================================== --- trunk/lims_upds/new_jquery.sh (rev 0) +++ trunk/lims_upds/new_jquery.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,90 @@ +#!/bin/sh +# new_jquery.sh + +INSTS=" \ +uslims3_AESKU \ +uslims3_Avatar \ +uslims3_BIOCORUW \ +uslims3_BUT \ +uslims3_Beckman \ +uslims3_CAUMA \ +uslims3_ColoState \ +uslims3_Crucell_B \ +uslims3_DRL \ +uslims3_DS \ +uslims3_EPFL \ +uslims3_FAU \ +uslims3_FSU_IMB \ +uslims3_HEXAL \ +uslims3_HHU \ +uslims3_IGBMC_ISB \ +uslims3_IISC \ +uslims3_IMTECH \ +uslims3_IUBloom \ +uslims3_JGU \ +uslims3_KAUST \ +uslims3_LU \ +uslims3_MHH \ +uslims3_MNU \ +uslims3_MPIB \ +uslims3_MPI_KGF \ +uslims3_MPIbpc \ +uslims3_MSSM \ +uslims3_MUSOM \ +uslims3_NCMH \ +uslims3_NIST \ +uslims3_NREL \ +uslims3_NTU1 \ +uslims3_NU \ +uslims3_OUHSC \ +uslims3_Osaka_U \ +uslims3_PSU_BMB \ +uslims3_PU \ +uslims3_RCaH \ +uslims3_Rice \ +uslims3_Roche \ +uslims3_SGG \ +uslims3_Sinica \ +uslims3_TAMU \ +uslims3_TIT \ +uslims3_TJU \ +uslims3_TUM \ +uslims3_UAB \ +uslims3_UCSF \ +uslims3_UCSanDiego \ +uslims3_UIC \ +uslims3_UMICH \ +uslims3_UMassMed \ +uslims3_US3_DEMO \ +uslims3_USTC \ +uslims3_UTAustin \ +uslims3_UU_NL \ +uslims3_UVic \ +uslims3_UW \ +uslims3_UW_Biophys \ +uslims3_U_Regensbg \ +uslims3_Uni_KN \ +uslims3_Univ_Leeds \ +uslims3_UofL \ +uslims3_UofU \ +uslims3_WSU \ +uslims3_YCU \ +uslims3_cauma3d \ +uslims3_uea \ +uslims3_unimelb \ +uslims3_weizmann \ +us3 \ +" +##uslims3_cauma3 + +BDIR="/srv/www/htdocs/uslims3" +CFILE="../../../common/js/jquery-1.11.1.min.js" + +for INST in ${INSTS}; do + JDIR=${BDIR}/${INST}/js + cd ${JDIR} + mv jquery.js jquery-old.js + ln -s ${CFILE} jquery.js + ls -l ${JDIR}/jquery.js +done + Property changes on: trunk/lims_upds/new_jquery.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/old_jquery.sh =================================================================== --- trunk/lims_upds/old_jquery.sh (rev 0) +++ trunk/lims_upds/old_jquery.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,89 @@ +#!/bin/sh +# new_jquery.sh + +INSTS=" \ +uslims3_AESKU \ +uslims3_Avatar \ +uslims3_BIOCORUW \ +uslims3_BUT \ +uslims3_Beckman \ +uslims3_ColoState \ +uslims3_Crucell_B \ +uslims3_DRL \ +uslims3_DS \ +uslims3_EPFL \ +uslims3_FAU \ +uslims3_FSU_IMB \ +uslims3_HEXAL \ +uslims3_HHU \ +uslims3_IGBMC_ISB \ +uslims3_IISC \ +uslims3_IMTECH \ +uslims3_IUBloom \ +uslims3_JGU \ +uslims3_KAUST \ +uslims3_LU \ +uslims3_MHH \ +uslims3_MNU \ +uslims3_MPIB \ +uslims3_MPI_KGF \ +uslims3_MPIbpc \ +uslims3_MSSM \ +uslims3_MUSOM \ +uslims3_NCMH \ +uslims3_NIST \ +uslims3_NREL \ +uslims3_NTU1 \ +uslims3_NU \ +uslims3_OUHSC \ +uslims3_Osaka_U \ +uslims3_PSU_BMB \ +uslims3_PU \ +uslims3_RCaH \ +uslims3_Rice \ +uslims3_Roche \ +uslims3_SGG \ +uslims3_Sinica \ +uslims3_TAMU \ +uslims3_TIT \ +uslims3_TJU \ +uslims3_TUM \ +uslims3_UAB \ +uslims3_UCSF \ +uslims3_UCSanDiego \ +uslims3_UIC \ +uslims3_UMICH \ +uslims3_UMassMed \ +uslims3_US3_DEMO \ +uslims3_USTC \ +uslims3_UTAustin \ +uslims3_UU_NL \ +uslims3_UVic \ +uslims3_UW \ +uslims3_UW_Biophys \ +uslims3_U_Regensbg \ +uslims3_Uni_KN \ +uslims3_Univ_Leeds \ +uslims3_UofL \ +uslims3_UofU \ +uslims3_WSU \ +uslims3_YCU \ +uslims3_cauma3d \ +uslims3_uea \ +uslims3_unimelb \ +uslims3_weizmann \ +us3 \ +" +##uslims3_cauma3 +##uslims3_CAUMA + +BDIR="/srv/www/htdocs/uslims3" + +for INST in ${INSTS}; do + JDIR=${BDIR}/${INST}/js + cd ${JDIR} + mv jquery.js jquery-new.js + mv jquery-old.js jquery.js + ls -l ${JDIR}/jquery.js +done + Property changes on: trunk/lims_upds/old_jquery.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/show_bandform.sh =================================================================== --- trunk/lims_upds/show_bandform.sh (rev 0) +++ trunk/lims_upds/show_bandform.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,12 @@ +# Script to show band-forming centerpiece cell experiments in DBs + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +CMD="select cellID,abstractCenterpieceID,experimentID from cell where abstractCenterpieceID=7 OR abstractCenterpieceID=8;" + +for DB in `cat ~us3/lims/database/dblist.txt` +do + echo "== Searching $DB for band-forming centerpiece ==" + mysql ${LOGIN} -sNe "${CMD}" $DB +done + Property changes on: trunk/lims_upds/show_bandform.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/show_latest_users.sh =================================================================== --- trunk/lims_upds/show_latest_users.sh (rev 0) +++ trunk/lims_upds/show_latest_users.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,9 @@ +# Script to update the list of DBs to be altered + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +CMD="select DISTINCT Investigator_Name, Email from investigators where LastLogin like '2015%'" +CMD="${CMD} Order by Investigator_Name" + +mysql ${LOGIN} -sNe "${CMD}" uslims3_global + Property changes on: trunk/lims_upds/show_latest_users.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/show_projguid.sh =================================================================== --- trunk/lims_upds/show_projguid.sh (rev 0) +++ trunk/lims_upds/show_projguid.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,14 @@ +#!/bin/bash +# Script to count projects with bad GUIDs + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +CMD="SELECT count(*) FROM project WHERE projectGUID LIKE ' %';" + +##for db in `cat ~us3/lims/database/dblist.txt` +##for db in uslims3_cauma3 +for db in `cat ~us3/lims/database/dblist.txt` +do + echo "Examining $db" + mysql ${LOGIN} -sNe "${CMD}" $db +done Property changes on: trunk/lims_upds/show_projguid.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/svn_info.sh =================================================================== --- trunk/lims_upds/svn_info.sh (rev 0) +++ trunk/lims_upds/svn_info.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,19 @@ +# One time script to list 'svn info' output for +# production LIMS,GridCtl directories + +INSTSAMP=uslims3_AUCSOL +US3HOME=`(cd ~us3;pwd)` + +DIRS="${US3HOME}/lims/bin \ + /srv/www/htdocs/uslims3 \ + /srv/www/htdocs/uslims3/${INSTSAMP} \ + /srv/www/htdocs/uslims3/uslims3_newlims \ + /srv/www/htdocs/common \ + /srv/www/htdocs/common/class \ + /srv/www/htdocs/common/class_local \ + " + +for DIR in $DIRS; do + svn info ${DIR}|egrep '^Path:|URL:' +done + Property changes on: trunk/lims_upds/svn_info.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/temp.sh =================================================================== --- trunk/lims_upds/temp.sh (rev 0) +++ trunk/lims_upds/temp.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,52 @@ +# One time script to update stored procedures for all active US3 databases + +#cd /home/us3/database/sql +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" + +for db in \ + uslims3_BIOCORUW \ + uslims3_Beckman \ + uslims3_CAUMA \ + uslims3_ColoState \ + uslims3_EPFL \ + uslims3_FAU \ + uslims3_FSU_IMB \ + uslims3_HHU \ + uslims3_IGBMC_ISB \ + uslims3_IUBloom \ + uslims3_KAUST \ + uslims3_LU \ + uslims3_MPIB \ + uslims3_MSSM \ + uslims3_MUSOM \ + uslims3_NCMH \ + uslims3_NREL \ + uslims3_NU \ + uslims3_Rice \ + uslims3_SGG \ + uslims3_TAMU \ + uslims3_TIT \ + uslims3_UAB \ + uslims3_UCSanDiego \ + uslims3_UIC \ + uslims3_UMICH \ + uslims3_UMassMed \ + uslims3_US3_DEMO \ + uslims3_UTAustin \ + uslims3_UVic \ + uslims3_UW_Biophys \ + uslims3_U_Regensbg \ + uslims3_Uni_KN \ + uslims3_UofL \ + uslims3_WSU \ + uslims3_YCU \ + uslims3_cauma3 \ + uslims3_cauma3d \ + uslims3_unimelb \ + us3 + +do + echo "Updating $db" + mysql $LOGIN $db < ./temp.sql +done Property changes on: trunk/lims_upds/temp.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/upd_people_lev4.sh =================================================================== --- trunk/lims_upds/upd_people_lev4.sh (rev 0) +++ trunk/lims_upds/upd_people_lev4.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,16 @@ +# One time script to update all active US3 databases +# by updating the people w userlevel=4,5 + +. ~us3/lims/database/getdbpw +echo "" +LOGIN="-u root -p$PASSW" +cd ~us3/lims/database +##for db in `cat dblist.txt` +##for db in `cat dblist_upds.txt` + +for db in `cat dblist.txt` +do + echo "Updating $db" + mysql $LOGIN $db < ./update_lev4_people.sql +done + Property changes on: trunk/lims_upds/upd_people_lev4.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/upd_people_procs.sh =================================================================== --- trunk/lims_upds/upd_people_procs.sh (rev 0) +++ trunk/lims_upds/upd_people_procs.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,18 @@ +# One time script to update all active US3 databases +# by updating the protocol stored procedures + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +cd ~us3/lims/database/sql +##for db in uslims3_cauma3 +##for db in uslims3_cauma3 uslims3_CAUMA +##for db in `cat /home/us3/database/dblist.txt` +##for db in `cat /home/us3/database/dblist_upds.txt` +##for db in uslims3_cauma3 uslims3_CAUMA +for db in `cat ~us3/lims/database/dblist.txt` + +do + echo "Updating $db" + mysql $LOGIN $db < ./us3_people_procs.sql +done + Property changes on: trunk/lims_upds/upd_people_procs.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/update-local.sh =================================================================== --- trunk/lims_upds/update-local.sh (rev 0) +++ trunk/lims_upds/update-local.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,17 @@ +#!/bin/bash +# One time script to update all active US3 LIMS code + +cd /home/us3/database + +for d in \ + lims/html \ + sql + +do + echo "Updating $d" + pushd $d + svn up + popd + sleep 2 + +done Property changes on: trunk/lims_upds/update-local.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/update-report-tables.sql =================================================================== --- trunk/lims_upds/update-report-tables.sql (rev 0) +++ trunk/lims_upds/update-report-tables.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,128 @@ +-- +-- update-report-tables.sql +-- +-- update report table structure to go with release of phase 1 US_Report class +-- +SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS; +SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS; +SET @OLD_SQL_MODE=@@SQL_MODE; + +SET UNIQUE_CHECKS = 0; +SET FOREIGN_KEY_CHECKS=0; +SET SQL_MODE='STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER'; + +-- +-- Table structure for table report +-- + +DROP TABLE IF EXISTS report; +CREATE TABLE report ( + reportID int(11) NOT NULL AUTO_INCREMENT, + reportGUID char(36) NOT NULL UNIQUE, + experimentID int(11) NOT NULL, + runID varchar(80) NOT NULL, + title varchar(255) NOT NULL DEFAULT '', + html longtext, + PRIMARY KEY (reportID), + INDEX ndx_report_experimentID (experimentID), + CONSTRAINT fk_report_experimentID + FOREIGN KEY (experimentID) + REFERENCES experiment (experimentID) + ON DELETE CASCADE + ON UPDATE CASCADE +) ENGINE=InnoDB; + +-- +-- Table structure for table reportPerson +-- + +DROP TABLE IF EXISTS reportPerson; +CREATE TABLE reportPerson ( + reportID int(11) NOT NULL, + personID int(11) NOT NULL, + PRIMARY KEY (reportID), + INDEX ndx_reportPerson_personID (personID), + INDEX ndx_reportPerson_reportID (reportID), + CONSTRAINT fk_reportPerson_personID + FOREIGN KEY (personID) + REFERENCES people (personID) + ON DELETE CASCADE + ON UPDATE CASCADE, + CONSTRAINT fk_reportPerson_reportID + FOREIGN KEY (reportID) + REFERENCES report (reportID) + ON DELETE CASCADE + ON UPDATE CASCADE +) ENGINE=InnoDB; + +-- +-- Table structure for table reportTriple +-- + +DROP TABLE IF EXISTS reportDetail; +DROP TABLE IF EXISTS reportTriple; +CREATE TABLE reportTriple ( + reportTripleID int(11) NOT NULL AUTO_INCREMENT, + reportTripleGUID char(36) NOT NULL UNIQUE, + reportID int(11) NOT NULL, + resultID int(11) DEFAULT NULL, + triple varchar(20) NOT NULL DEFAULT '', -- cell/channel/wavelength format + PRIMARY KEY (reportTripleID), + INDEX ndx_reportTriple_reportID (reportID), + CONSTRAINT fk_reportTriple_reportID + FOREIGN KEY (reportID) + REFERENCES report (reportID) + ON DELETE CASCADE + ON UPDATE CASCADE +) ENGINE=InnoDB; + +-- +-- Table structure for table documentLink +-- + +DROP TABLE IF EXISTS documentLink; +CREATE TABLE documentLink ( + documentLinkID int(11) NOT NULL AUTO_INCREMENT, + reportTripleID int(11) NOT NULL, + reportDocumentID int(11) NOT NULL, + PRIMARY KEY (documentLinkID), + INDEX ndx_documentLink_reportTripleID (reportTripleID), + INDEX ndx_documentLink_reportDocumentID (reportDocumentID), + CONSTRAINT fk_documentLink_reportDocumentID + FOREIGN KEY (reportDocumentID) + REFERENCES reportDocument (reportDocumentID) + ON DELETE CASCADE + ON UPDATE CASCADE, + CONSTRAINT fk_documentLink_reportTripleID + FOREIGN KEY (reportTripleID) + REFERENCES reportTriple (reportTripleID) + ON DELETE CASCADE + ON UPDATE CASCADE +) ENGINE=InnoDB; + +-- +-- Table structure for table reportDocument +-- + +DROP TABLE IF EXISTS reportDocument; +CREATE TABLE reportDocument ( + reportDocumentID int(11) NOT NULL AUTO_INCREMENT, + reportDocumentGUID char(36) NOT NULL UNIQUE, + editedDataID int(11) NOT NULL DEFAULT '1', + label varchar(80) NOT NULL DEFAULT '', + filename varchar(255) NOT NULL DEFAULT '', + analysis varchar(20) DEFAULT '2DSA', + subAnalysis varchar(20) DEFAULT 'report', + documentType varchar(20) DEFAULT 'png', + contents longblob, + PRIMARY KEY (reportDocumentID), + INDEX ndx_reportDocument_editedDataID (editedDataID), + CONSTRAINT fk_reportDocument_editedDataID + FOREIGN KEY (editedDataID) + REFERENCES editedData (editedDataID) + ON UPDATE NO ACTION +) ENGINE=InnoDB; + +SET SQL_MODE=@OLD_SQL_MODE; +SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; +SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; Added: trunk/lims_upds/update-us3-autoflow-procs.sh =================================================================== --- trunk/lims_upds/update-us3-autoflow-procs.sh (rev 0) +++ trunk/lims_upds/update-us3-autoflow-procs.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,10 @@ +# One time script to update stored procedures for all active US3 databases +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +cd ~us3/lims/database/sql + +for db in `cat ~us3/lims/database/dblist.txt` +do + echo "Updating $db" + mysql $LOGIN $db < ./us3_hardware_procs.sql +done Property changes on: trunk/lims_upds/update-us3-autoflow-procs.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/update-us3-db.sh =================================================================== --- trunk/lims_upds/update-us3-db.sh (rev 0) +++ trunk/lims_upds/update-us3-db.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,11 @@ +# One time script to update all active US3 databases + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +##for db in uslims3_cauma3 +for db in `cat dblist.txt` +do + echo "Updating $db" + mysql $LOGIN $db < ./alter_ripro_type.sql +done + Property changes on: trunk/lims_upds/update-us3-db.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/update-us3-hardware-procs.sh =================================================================== --- trunk/lims_upds/update-us3-hardware-procs.sh (rev 0) +++ trunk/lims_upds/update-us3-hardware-procs.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,10 @@ +# One time script to update stored procedures for all active US3 databases +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +cd ~us3/lims/database/sql + +for db in `cat ~us3/lims/database/dblist.txt` +do + echo "Updating $db" + mysql $LOGIN $db < ./us3_hardware_procs.sql +done Property changes on: trunk/lims_upds/update-us3-hardware-procs.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/update-us3-lims.sh =================================================================== --- trunk/lims_upds/update-us3-lims.sh (rev 0) +++ trunk/lims_upds/update-us3-lims.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,17 @@ +#!/bin/bash +# One time script to update all active US3 LIMS code + +cd /srv/www/htdocs/uslims3 + +##for db in `cat ~us3/lims/database/dblist.txt` +##for db in uslims3_cauma3 +##for db in `cat ~us3/lims/database/dblist_upds.txt` +for db in `cat ~us3/lims/database/dblist.txt` +do + echo "Updating $db" + pushd $db + svn up + popd + sleep 2 + +done Property changes on: trunk/lims_upds/update-us3-lims.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/update-us3-procs.sh =================================================================== --- trunk/lims_upds/update-us3-procs.sh (rev 0) +++ trunk/lims_upds/update-us3-procs.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,10 @@ +# One time script to update stored procedures for all active US3 databases +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +cd ~us3/lims/database/sql + +for db in `cat ~us3/lims/database/dblist.txt` +do + echo "Updating $db" + mysql $LOGIN $db < ./us3_procedures.sql +done Property changes on: trunk/lims_upds/update-us3-procs.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/update_CustomGrid.sql =================================================================== --- trunk/lims_upds/update_CustomGrid.sql (rev 0) +++ trunk/lims_upds/update_CustomGrid.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,35 @@ +-- +-- update_CustomGrid.sql +-- +-- Adds a couple of items needed to represent the new +-- analysis method known as 2DSA custom grid +-- + +-- Add the new analysis method +ALTER TABLE HPCAnalysisRequest + MODIFY COLUMN method enum('2DSA','2DSA_CG','2DSA_MW','GA','GA_MW','GA_SC') NOT NULL default '2DSA'; + +-- Add table structure for table 2DSA_CG_Settings +DROP TABLE IF EXISTS 2DSA_CG_Settings; + +CREATE TABLE IF NOT EXISTS 2DSA_CG_Settings ( + 2DSA_CG_SettingsID int(11) NOT NULL AUTO_INCREMENT, + HPCAnalysisRequestID int(11) NOT NULL, + CG_modelID int(11) NOT NULL, -- maps to model with 'CustomGrid...' description + uniform_grid int(11) NOT NULL default '6', + mc_iterations int(11) NOT NULL default 1, + tinoise_option tinyint(1) NOT NULL default '0', + regularization int(11) NOT NULL default '0', + meniscus_range double NOT NULL default '0.01', + meniscus_points double NOT NULL default '3', + max_iterations int(11) NOT NULL default 1, + rinoise_option tinyint(1) NOT NULL default '0', + PRIMARY KEY (2DSA_CG_SettingsID), + INDEX ndx_2DSA_CG_Settings_HPCAnalysisRequestID (HPCAnalysisRequestID ASC), + CONSTRAINT fk_2DSA_CG_Settings_HPCAnalysisRequestID + FOREIGN KEY (HPCAnalysisRequestID) + REFERENCES HPCAnalysisRequest (HPCAnalysisRequestID) + ON DELETE CASCADE + ON UPDATE NO ACTION ) +ENGINE=InnoDB; + Added: trunk/lims_upds/update_dblist.sh =================================================================== --- trunk/lims_upds/update_dblist.sh (rev 0) +++ trunk/lims_upds/update_dblist.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,8 @@ +# Script to update the list of DBs to be altered + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" + +mysql ${LOGIN} -sNe "SHOW DATABASES LIKE 'uslims3%'" | grep -v uslims3_global >dblist.txt +echo "us3" >> dblist.txt + Property changes on: trunk/lims_upds/update_dblist.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/update_dmga.sql =================================================================== --- trunk/lims_upds/update_dmga.sql (rev 0) +++ trunk/lims_upds/update_dmga.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,38 @@ +-- +-- update_dmga.sql +-- +-- Adds a couple of items needed to represent the new +-- analysis method known as DMGA +-- + +-- Add the new analysis method +ALTER TABLE HPCAnalysisRequest + MODIFY COLUMN method enum('2DSA','2DSA_CG','2DSA_MW','GA','GA_MW','GA_SC','DMGA') NOT NULL default '2DSA'; + +-- Add table structure for table DMGA_Settings +DROP TABLE IF EXISTS DMGA_Settings; + +CREATE TABLE IF NOT EXISTS DMGA_Settings ( + DMGA_SettingsID int(11) NOT NULL AUTO_INCREMENT, + HPCAnalysisRequestID int(11) NOT NULL, + DC_modelID int(11) NOT NULL, -- maps to model with 'DMGA_Constr..' description + mc_iterations int(11) NOT NULL default '0', + demes int(11) NOT NULL default '31', + population int(11) NOT NULL default '100', + generations int(11) NOT NULL default '100', + mutation int(11) NOT NULL default '50', + crossover int(11) NOT NULL default '50', + plague int(11) NOT NULL default '4', + elitism int(11) NOT NULL default '2', + migration int(11) NOT NULL default '3', + p_grid int(11) NOT NULL default '500', + seed int(11) NOT NULL default '0', + PRIMARY KEY (DMGA_SettingsID), + INDEX ndx_DMGA_Settings_HPCAnalysisRequestID (HPCAnalysisRequestID ASC), + CONSTRAINT fk_DMGA_Settings_HPCAnalysisRequestID + FOREIGN KEY (HPCAnalysisRequestID) + REFERENCES HPCAnalysisRequest (HPCAnalysisRequestID) + ON DELETE CASCADE + ON UPDATE NO ACTION ) +ENGINE=InnoDB; + Added: trunk/lims_upds/update_lev4_people.sql =================================================================== --- trunk/lims_upds/update_lev4_people.sql (rev 0) +++ trunk/lims_upds/update_lev4_people.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,27 @@ +-- +-- Update people with userlevel=4.5 for new address (uthscsa-to-montana) +-- + +-- UPDATE TABLE people + +UPDATE people + SET address='32 Campus Drive', + city='Missoula', + state='MT', + zip='59812', + organization='University of Montana' + WHERE userlevel>3 AND organization LIKE 'UTH%'; + +UPDATE people + SET phone='406-285-1935', email='borries.demeler@umontana.edu' + WHERE lname='Demeler' AND fname='Borries'; + +UPDATE people + SET phone='832-466-9211', email='gegorbet@gmail.com' + WHERE lname='Gorbet'; + +UPDATE people + SET phone='555-555-5555', email='alexsav.science@gmail.com', + fname='Alexey', lname='Savelyev', username='us3savelyev' + WHERE lname='Zollars'; + Added: trunk/lims_upds/update_model_desc.sql =================================================================== --- trunk/lims_upds/update_model_desc.sql (rev 0) +++ trunk/lims_upds/update_model_desc.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,6 @@ +-- +-- Update the model description to be up to 160 characters +-- + +ALTER TABLE model + MODIFY COLUMN description VARCHAR(160) NULL DEFAULT NULL; Added: trunk/lims_upds/update_pcsa.sql =================================================================== --- trunk/lims_upds/update_pcsa.sql (rev 0) +++ trunk/lims_upds/update_pcsa.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,44 @@ +-- +-- update_pcsa.sql +-- +-- Adds a couple of items needed to represent the new +-- analysis method known as PCSA +-- + +-- Add the new analysis method +ALTER TABLE HPCAnalysisRequest + MODIFY COLUMN method enum('2DSA','2DSA_CG','2DSA_MW','GA','GA_MW','GA_SC','DMGA','PCSA') NOT NULL default '2DSA'; + +-- Alter HPCAnalysisRequestData for additional result file types +ALTER TABLE HPCAnalysisResultData + MODIFY COLUMN HPCAnalysisResultType enum('model','noise','job_stats','mrecs','unknown') NOT NULL default 'unknown'; + +-- Add table structure for table PCSA_Settings +DROP TABLE IF EXISTS PCSA_Settings; + +CREATE TABLE IF NOT EXISTS PCSA_Settings ( + PCSA_SettingsID int(11) NOT NULL AUTO_INCREMENT, + HPCAnalysisRequestID int(11) NOT NULL, + curve_type VARCHAR(8) NOT NULL default 'SL', + s_min double NOT NULL default '1', + s_max double NOT NULL default '10', + ff0_min double NOT NULL default '1', + ff0_max double NOT NULL default '4', + vars_count int(11) NOT NULL default 10, + gfit_iterations int(11) NOT NULL default 3, + curves_points int(11) NOT NULL default 200, + thr_deltr_ratio double NOT NULL default 0.0001, + tikreg_option tinyint(1) NOT NULL default 0, + tikreg_alpha double NOT NULL default 0.0, + mc_iterations int(11) NOT NULL default 1, + tinoise_option tinyint(1) NOT NULL default '0', + rinoise_option tinyint(1) NOT NULL default '0', + PRIMARY KEY (PCSA_SettingsID), + INDEX ndx_PCSA_Settings_HPCAnalysisRequestID (HPCAnalysisRequestID ASC), + CONSTRAINT fk_PCSA_Settings_HPCAnalysisRequestID + FOREIGN KEY (HPCAnalysisRequestID) + REFERENCES HPCAnalysisRequest (HPCAnalysisRequestID) + ON DELETE CASCADE + ON UPDATE NO ACTION ) +ENGINE=InnoDB; + Added: trunk/lims_upds/update_rotors.sql =================================================================== --- trunk/lims_upds/update_rotors.sql (rev 0) +++ trunk/lims_upds/update_rotors.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,16 @@ +-- +-- Script to re-establish abstract rotor ID's for default rotors in rotor table +-- + +UPDATE rotor +SET abstractRotorID = 1 +WHERE rotorID = 1; + +UPDATE rotor +SET abstractRotorID = 2 +WHERE rotorID = 2; + +UPDATE rotor +SET abstractRotorID = 3 +WHERE rotorID = 3; + Added: trunk/lims_upds/update_rotors2.sql =================================================================== --- trunk/lims_upds/update_rotors2.sql (rev 0) +++ trunk/lims_upds/update_rotors2.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,8 @@ +-- +-- Script to re-establish abstract rotor ID's for default rotors in rotor table +-- + +SELECT COUNT(*) +FROM rotor +where abstractRotorID IS NULL; + Added: trunk/lims_upds/us3_add_autoflow.sh =================================================================== --- trunk/lims_upds/us3_add_autoflow.sh (rev 0) +++ trunk/lims_upds/us3_add_autoflow.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,16 @@ +# One time script to update all active US3 databases +# by adding the protocol and protocolPerson tables + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +#cd ~us3/database + +##for db in uslims3_cauma3 +##for db in uslims3_cauma3 uslims3_CAUMA +##for db in `cat dblist.txt` +for db in `cat ~us3/lims/database/dblist_cch.txt` + +do + echo "Updating $db" + mysql $LOGIN $db < ./add_autoflow.sql +done Property changes on: trunk/lims_upds/us3_add_autoflow.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_add_exper.sh =================================================================== --- trunk/lims_upds/us3_add_exper.sh (rev 0) +++ trunk/lims_upds/us3_add_exper.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,13 @@ +# One time script to update all active US3 databases +# by creating the new speedstep table +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" + +##for db in uslims3_cauma3 +##for db in `cat /home/us3/database/dblist_upds.txt` +for db in `cat /home/us3/database/dblist.txt` + +do + echo "Updating $db" + mysql $LOGIN $db < ./add_speedSteps.sql +done Property changes on: trunk/lims_upds/us3_add_exper.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_add_extincp.sh =================================================================== --- trunk/lims_upds/us3_add_extincp.sh (rev 0) +++ trunk/lims_upds/us3_add_extincp.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,14 @@ +# One time script to update all active US3 databases +# by adding the extinctionProfile table + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +##for db in uslims3_cauma3 +##for db in uslims3_cauma3 uslims3_CAUMA +##for db in `cat dblist.txt` +for db in uslims3_devel + +do + echo "Updating $db" + mysql $LOGIN $db < ./us3_add_extincp.sql +done Property changes on: trunk/lims_upds/us3_add_extincp.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_add_extincp.sql =================================================================== --- trunk/lims_upds/us3_add_extincp.sql (rev 0) +++ trunk/lims_upds/us3_add_extincp.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,15 @@ +-- ----------------------------------------------------- +-- Table extinctionProfile +-- ----------------------------------------------------- +DROP TABLE IF EXISTS extinctionProfile; + +CREATE TABLE extinctionProfile ( + profileID int(11) NOT NULL AUTO_INCREMENT , + componentID int(11) NOT NULL , + componentType enum( 'Buffer', 'Analyte', 'Sample' ) NOT NULL, + valueType enum( 'absorbance', 'molarExtinction', 'massExtinction' ) NOT NULL, + xml longtext DEFAULT NULL , + PRIMARY KEY (profileID) , + INDEX ndx_component_ID (componentID ASC) ) +ENGINE = InnoDB; + Added: trunk/lims_upds/us3_add_manual.sh =================================================================== --- trunk/lims_upds/us3_add_manual.sh (rev 0) +++ trunk/lims_upds/us3_add_manual.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,11 @@ +# One time script to update all active US3 databases +# by altering/updating ClusterAuthorizations +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" + +for db in `cat dblist.txt` + +do + echo "Updating $db" + mysql $LOGIN $db < ./add_manual.sql +done Property changes on: trunk/lims_upds/us3_add_manual.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_add_mrecs.sh =================================================================== --- trunk/lims_upds/us3_add_mrecs.sh (rev 0) +++ trunk/lims_upds/us3_add_mrecs.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,12 @@ +# One time script to update all active US3 databases +# by adding the pcsa_modelrecs table + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +##for db in uslims3_cauma3 +for db in `cat dblist.txt` + +do + echo "Updating $db" + mysql $LOGIN $db < ./add_mrecs.sql +done Property changes on: trunk/lims_upds/us3_add_mrecs.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_add_mrecsprocs.sh =================================================================== --- trunk/lims_upds/us3_add_mrecsprocs.sh (rev 0) +++ trunk/lims_upds/us3_add_mrecsprocs.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,12 @@ +# One time script to update all active US3 databases +# by updating the expdata stored procedures + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +##for db in `cat dblist.txt` +for db in uslims3_cauma3 + +do + echo "Updating $db" + mysql $LOGIN $db < us3_mrecs_procs.sql +done Property changes on: trunk/lims_upds/us3_add_mrecsprocs.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_add_notice.sh =================================================================== --- trunk/lims_upds/us3_add_notice.sh (rev 0) +++ trunk/lims_upds/us3_add_notice.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,11 @@ +# One time script to update US3 database 'us3_notice' +# by adding the notice table + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +for db in us3_notice + +do + echo "Updating $db" + mysql $LOGIN $db < ./add_notice.sql +done Property changes on: trunk/lims_upds/us3_add_notice.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_add_proto.sh =================================================================== --- trunk/lims_upds/us3_add_proto.sh (rev 0) +++ trunk/lims_upds/us3_add_proto.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,14 @@ +# One time script to update all active US3 databases +# by adding the protocol and protocolPerson tables + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +##for db in uslims3_cauma3 +##for db in uslims3_cauma3 uslims3_CAUMA +##for db in `cat dblist.txt` +for db in uslims3_cauma3 uslims3_CAUMA + +do + echo "Updating $db" + mysql $LOGIN $db < ./add_protocol.sql +done Property changes on: trunk/lims_upds/us3_add_proto.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_add_radialCal_table_cch.sh =================================================================== --- trunk/lims_upds/us3_add_radialCal_table_cch.sh (rev 0) +++ trunk/lims_upds/us3_add_radialCal_table_cch.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,16 @@ +# One time script to update all active US3 databases +# by creating the DMGA method + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +cd ~us3/database + +##for db in `cat ~us3/database/dblist.txt` +##for db in uslims3_cauma3 +##for db in `cat ~us3/database/dblist_upds.txt` +for db in `cat ~us3/lims/database/dblist_cch.txt` + +do + echo "Updating $db" + mysql $LOGIN $db < ./alter_instrument_work.sql +done Property changes on: trunk/lims_upds/us3_add_radialCal_table_cch.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_add_speeds.sh =================================================================== --- trunk/lims_upds/us3_add_speeds.sh (rev 0) +++ trunk/lims_upds/us3_add_speeds.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,12 @@ +# One time script to update all active US3 databases +# by altering/updating ClusterAuthorizations + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +##for db in uslims3_cauma3 +for db in `cat dblist.txt` + +do + echo "Updating $db" + mysql $LOGIN $db < ./add_speeds.sql +done Property changes on: trunk/lims_upds/us3_add_speeds.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_add_timestate.sh =================================================================== --- trunk/lims_upds/us3_add_timestate.sh (rev 0) +++ trunk/lims_upds/us3_add_timestate.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,14 @@ +# One time script to update all active US3 databases +# by creating the new timestate table + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +##for db in `cat /home/us3/database/dblist_upds.txt` +##for db in `cat /home/us3/database/dblist.txt` +##for db in uslims3_cauma3 +for db in `cat /home/us3/database/dblist_upds.txt` + +do + echo "Updating $db" + mysql $LOGIN $db < ./add_timeState.sql +done Property changes on: trunk/lims_upds/us3_add_timestate.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_alter_analyte.sh =================================================================== --- trunk/lims_upds/us3_alter_analyte.sh (rev 0) +++ trunk/lims_upds/us3_alter_analyte.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,16 @@ +# One time script to update all active US3 databases +# by creating the DMGA method + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +cd ~us3/database + +##for db in `cat ~us3/database/dblist.txt` +##for db in uslims3_cauma3 +##for db in `cat ~us3/database/dblist_upds.txt` +for db in `cat ~us3/database/dblist.txt` + +do + echo "Updating $db" + mysql $LOGIN $db < ./alter_analyte.sql +done Property changes on: trunk/lims_upds/us3_alter_analyte.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_alter_aprofile.sh =================================================================== --- trunk/lims_upds/us3_alter_aprofile.sh (rev 0) +++ trunk/lims_upds/us3_alter_aprofile.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,14 @@ +# One time script to update all active US3 databases +# by modifying/adding analysisprofile-related tables + +LOGIN="-u root -p`cat ~/.sec/.pwsq`" +##for db in `cat dblist.txt` +##for db in `cat dblist_upds.txt` +##for db in uslims3_uthealth +for db in uslims3_CCH + +do + echo "Updating $db" + mysql $LOGIN $db < ./alter_aprofile.sql +done + Property changes on: trunk/lims_upds/us3_alter_aprofile.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_alter_bufcomp.sh =================================================================== --- trunk/lims_upds/us3_alter_bufcomp.sh (rev 0) +++ trunk/lims_upds/us3_alter_bufcomp.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,17 @@ +# One time script to update all active US3 databases +# by altering contents of the bufferComponent table + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +cd ~us3/database + +##for db in `cat ~us3/database/dblist.txt` +##for db in uslims3_cauma3 +##for db in `cat ~us3/database/dblist_upds.txt` +for db in uslims3_cauma3 + +do + echo "Updating $db" + mysql $LOGIN $db < sql/us3_buffer_components.sql +done + Property changes on: trunk/lims_upds/us3_alter_bufcomp.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_alter_hpcanareq.sh =================================================================== --- trunk/lims_upds/us3_alter_hpcanareq.sh (rev 0) +++ trunk/lims_upds/us3_alter_hpcanareq.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,19 @@ +# One time script to update all active US3 databases +# by creating the DMGA method + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +cd ~us3/lims/database + +##for db in `cat ~us3/lims/database/dblist.txt` +##for db in uslims3_cauma3 +##for db in `cat ~us3/lims/database/dblist_upds.txt` +##for db in uslims3_cauma3d +##for db in `cat ~us3/lims/database/dblist.txt |grep -v uslims3_cauma3` +for db in uslims3_UPENN +do + echo "Updating $db" + mysql $LOGIN $db < ./alter_hpcreq2.sql +done +## mysql $LOGIN $db < ./alter_hpcreq.sql +## mysql $LOGIN $db < ./alter_hpcreq2.sql Property changes on: trunk/lims_upds/us3_alter_hpcanareq.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_alter_instrument.sh =================================================================== --- trunk/lims_upds/us3_alter_instrument.sh (rev 0) +++ trunk/lims_upds/us3_alter_instrument.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,15 @@ +# One time script to update all active US3 databases +# by creating the DMGA method + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +cd ~us3/lims/database + +##for db in `cat ~us3/database/dblist.txt` +##for db in uslims3_cauma3 +##for db in `cat ~us3/database/dblist_upds.txt` +for db in `cat ~us3/lims/database/dblist_upds.txt` +do + echo "Updating $db" + mysql $LOGIN $db < ./alter_instrument.sql +done Property changes on: trunk/lims_upds/us3_alter_instrument.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_alter_instrument_cauma3.sh =================================================================== --- trunk/lims_upds/us3_alter_instrument_cauma3.sh (rev 0) +++ trunk/lims_upds/us3_alter_instrument_cauma3.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,16 @@ +# One time script to update all active US3 databases +# by creating the DMGA method + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +cd ~us3/database + +##for db in `cat ~us3/database/dblist.txt` +##for db in uslims3_cauma3 +##for db in `cat ~us3/database/dblist_upds.txt` +for db in `cat ~us3/lims/database/dblist_cauma3.txt` + +do + echo "Updating $db" + mysql $LOGIN $db < ./alter_instrument_work.sql +done Property changes on: trunk/lims_upds/us3_alter_instrument_cauma3.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_alter_instrument_cch.sh =================================================================== --- trunk/lims_upds/us3_alter_instrument_cch.sh (rev 0) +++ trunk/lims_upds/us3_alter_instrument_cch.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,16 @@ +# One time script to update all active US3 databases +# by creating the DMGA method + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +cd ~us3/database + +##for db in `cat ~us3/database/dblist.txt` +##for db in uslims3_cauma3 +##for db in `cat ~us3/database/dblist_upds.txt` +for db in `cat ~us3/lims/database/dblist_cch.txt` + +do + echo "Updating $db" + mysql $LOGIN $db < ./alter_instrument_work.sql +done Property changes on: trunk/lims_upds/us3_alter_instrument_cch.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_alter_metadata.sh =================================================================== --- trunk/lims_upds/us3_alter_metadata.sh (rev 0) +++ trunk/lims_upds/us3_alter_metadata.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,6 @@ +# One time script to add entries to newus3.metadata +# by creating the DMGA method + +echo "Updating newus3.metadata" +mysql -u root -p newus3 < ./alter_metadata.sql + Property changes on: trunk/lims_upds/us3_alter_metadata.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_change_bc.sh =================================================================== --- trunk/lims_upds/us3_change_bc.sh (rev 0) +++ trunk/lims_upds/us3_change_bc.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,18 @@ +# One time script to update all active US3 databases +# by changing the Glycerol% bufferComponents entry +# for proper range and range unit + +LOGIN="-u root -p`cat ~/.sec/.pwsq`" +CMD="update bufferComponent set density='0.99823 0.030859 0.2249 6.333e-3 2.505e-5 4.151e-5 0-32 %weight/weight' where bufferComponentID=25" +##CMD="update bufferComponent set density='0.99824 0.0 2.08235 0.14172 -0.26453 0.54073 0-13.694 M' where bufferComponentID=26" +##CMD="select description,density from bufferComponent where description like '%lycero%'" +##for db in `cat dblist.txt` +##for db in uslims3_devel +##for db in uslims3_cauma3 +for db in `cat dblist.txt` + +do + echo "Updating $db" + mysql $LOGIN $db -sNe "${CMD}" +done + Property changes on: trunk/lims_upds/us3_change_bc.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_count_allusers.sql =================================================================== --- trunk/lims_upds/us3_count_allusers.sql (rev 0) +++ trunk/lims_upds/us3_count_allusers.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,7 @@ +-- +-- us3_count_allusers.sql +-- +-- count all users in all databases +-- + +select count(*) from people; Added: trunk/lims_upds/us3_drop_manual.sh =================================================================== --- trunk/lims_upds/us3_drop_manual.sh (rev 0) +++ trunk/lims_upds/us3_drop_manual.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,11 @@ +# One time script to update all active US3 databases +# by altering/updating ClusterAuthorizations + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +for db in `cat dblist.txt` + +do + echo "Updating $db" + mysql $LOGIN $db < ./drop_manual.sql +done Property changes on: trunk/lims_upds/us3_drop_manual.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_exp_procs.sql =================================================================== --- trunk/lims_upds/us3_exp_procs.sql (rev 0) +++ trunk/lims_upds/us3_exp_procs.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,993 @@ +-- +-- us3_exp_procs.sql +-- +-- Script to set up the MySQL stored procedures for the US3 system +-- These are procedures related to the experiment +-- Run as us3admin +-- + +DELIMITER $$ + +-- Verifies that the user has permission to view or modify +-- the specified experiment +DROP FUNCTION IF EXISTS verify_experiment_permission$$ +CREATE FUNCTION verify_experiment_permission( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_experimentID INT ) + RETURNS INT + READS SQL DATA + +BEGIN + DECLARE count_experiments INT; + DECLARE count_permissions INT; + DECLARE status INT; + + CALL config(); + SET status = @ERROR; + SET @US3_LAST_ERROR = 'MySQL: error verifying experiment permission'; + + SELECT COUNT(*) + INTO count_experiments + FROM experiment + WHERE experimentID = p_experimentID; + + SELECT COUNT(*) + INTO count_permissions + FROM experimentPerson + WHERE experimentID = p_experimentID + AND personID = @US3_ID; + + IF ( p_experimentID = 1 ) THEN -- The magic ID that anyone can use temporarily + SET count_permissions = 1; + END IF; + + IF ( count_experiments = 0 ) THEN + SET @US3_LAST_ERRNO = @NO_EXPERIMENT; + SET @US3_LAST_ERROR = 'MySQL: the specified experiment does not exist'; + + SET status = @NO_EXPERIMENT; + + ELSEIF ( verify_userlevel( p_personGUID, p_password, @US3_ADMIN ) = @OK ) THEN + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + + SET status = @OK; + + ELSEIF ( ( verify_user( p_personGUID, p_password ) = @OK ) && + ( count_permissions > 0 ) ) THEN + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + + SET status = @OK; + + ELSE + SET @US3_LAST_ERRNO = @NOTPERMITTED; + SET @US3_LAST_ERROR = 'MySQL: you do not have permission to view or modify this experiment'; + + SET status = @NOTPERMITTED; + + END IF; + + RETURN( status ); + +END$$ + +-- Verifies that the operator has permission to operate the instrument, +-- and that the instrument is in the right lab +DROP FUNCTION IF EXISTS verify_operator_permission$$ +CREATE FUNCTION verify_operator_permission( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_labID INT, + p_instrumentID INT, + p_operatorID INT ) + RETURNS INT + READS SQL DATA + +BEGIN + DECLARE count_instruments INT; + DECLARE count_labs INT; + + CALL config(); + SET @US3_LAST_ERRNO = @ERROR; + SET @US3_LAST_ERROR = 'MySQL: error verifying operator permission'; + + -- Check permits for this instrument and this operator + SELECT COUNT(*) + INTO count_instruments + FROM permits + WHERE personID = p_operatorID + AND instrumentID = p_instrumentID; + + -- Check location of instrument + SELECT COUNT(*) + INTO count_labs + FROM instrument + WHERE instrumentID = p_instrumentID + AND labID = p_labID; + + IF ( verify_user( p_personGUID, p_password ) != @OK ) THEN + SET @US3_LAST_ERRNO = @NOTPERMITTED; + SET @US3_LAST_ERROR = 'MySQL: you do not have permission to use this instrument'; + + ELSEIF ( count_labs < 1 ) THEN + SET @US3_LAST_ERRNO = @BADLABLOCATION; + SET @US3_LAST_ERROR = 'MySQL: that instrument is not located in that lab'; + + ELSEIF ( count_instruments < 1 ) THEN + SET @US3_LAST_ERRNO = @BADOPERATOR; + SET @US3_LAST_ERROR = 'MySQL: operator is not permitted to work on this instrument'; + + ELSE + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + + END IF; + + RETURN( @US3_LAST_ERRNO ); + +END$$ + +-- Returns the count of experiments associated with p_ID +-- If p_ID = 0, retrieves count of all experiments in db +-- Regular user can only get count of his own experiments +DROP FUNCTION IF EXISTS count_experiments$$ +CREATE FUNCTION count_experiments( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_ID INT ) + RETURNS INT + READS SQL DATA + +BEGIN + + DECLARE count_experiments INT; + + CALL config(); + SET count_experiments = 0; + + IF ( verify_userlevel( p_personGUID, p_password, @US3_ADMIN ) = @OK ) THEN + -- This is an admin; he can get more info + IF ( p_ID > 0 ) THEN + SELECT COUNT(*) + INTO count_experiments + FROM experimentPerson + WHERE personID = p_ID; + + ELSE + SELECT COUNT(*) + INTO count_experiments + FROM experimentPerson; + + END IF; + + ELSEIF ( verify_user( p_personGUID, p_password ) = @OK ) THEN + IF ( (p_ID != 0) && (p_ID != @US3_ID) ) THEN + -- Uh oh, can't do that + SET @US3_LAST_ERRNO = @NOTPERMITTED; + SET @US3_LAST_ERROR = 'MySQL: you do not have permission to view those experiments'; + + ELSE + -- This person is asking about his own experiments + -- Ignore p_experimentID and return user's own + SELECT COUNT(*) + INTO count_experiments + FROM experimentPerson + WHERE personID = @US3_ID; + + END IF; + + END IF; + + RETURN( count_experiments ); + +END$$ + +-- INSERTs a new experiment with the specified information +DROP PROCEDURE IF EXISTS new_experiment$$ +CREATE PROCEDURE new_experiment ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_expGUID CHAR(36), + p_projectID INT, + p_runID VARCHAR(80), + p_labID INT, + p_instrumentID INT, + p_operatorID INT, + p_rotorID INT, + p_calibrationID INT, + p_type VARCHAR(30), + p_runType ENUM( 'RA', 'RI', 'IP', 'FI', 'WA', 'WI' ), + p_RIProfile LONGTEXT, + p_runTemp FLOAT, + p_label VARCHAR(80), + p_comment TEXT, + p_centrifugeProtocol TEXT, + p_ownerID INT ) + MODIFIES SQL DATA + +BEGIN + DECLARE l_experimentID INT; + DECLARE l_count_runID INT; + DECLARE duplicate_key TINYINT DEFAULT 0; + + DECLARE CONTINUE HANDLER FOR 1062 + SET duplicate_key = 1; + + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + SET @LAST_INSERT_ID = 0; + + IF ( ( verify_user( p_personGUID, p_password ) = @OK ) && + ( verify_operator_permission( p_personGUID, p_password, + p_labID, p_instrumentID, p_operatorID ) = @OK ) && + ( check_GUID( p_personGUID, p_password, p_expGUID ) = @OK ) ) THEN + -- Can't have duplicate run ID's for this investigator + SELECT COUNT(*) + INTO l_count_runID + FROM experimentPerson p, experiment e + WHERE p.experimentID = e.experimentID + AND e.runID = p_runID + AND p.personID = @US3_ID; + + IF ( l_count_runID > 0 ) THEN + SET @US3_LAST_ERRNO = @DUPFIELD; + SET @US3_LAST_ERROR = "MySQL: Duplicate runID in experiment table"; + + ELSE + IF ( p_runType != 'RI' ) THEN + SET p_RIProfile = ''; + END IF; + + INSERT INTO experiment SET + projectID = p_projectID, + experimentGUID = p_expGUID, + runID = p_runID, + labID = p_labID, + instrumentID = p_instrumentID, + operatorID = p_operatorID, + rotorID = p_rotorID, + rotorCalibrationID = p_calibrationID, + type = p_type, + runType = p_runType, + RIProfile = p_RIProfile, + runTemp = p_runTemp, + label = p_label, + comment = p_comment, + centrifugeProtocol = p_centrifugeProtocol, + dateUpdated = NOW() ; + + IF ( duplicate_key = 1 ) THEN + SET @US3_LAST_ERRNO = @INSERTDUP; + SET @US3_LAST_ERROR = "MySQL: Duplicate entry for experimentGUID field"; + + ELSE + SET @LAST_INSERT_ID = LAST_INSERT_ID(); + + INSERT INTO experimentPerson SET + experimentID = @LAST_INSERT_ID, + personID = p_ownerID; + + END IF; + + END IF; + + END IF; + + SELECT @US3_LAST_ERRNO AS status; + +END$$ + +-- UPDATEs an existing experiment with the specified information +DROP PROCEDURE IF EXISTS update_experiment$$ +CREATE PROCEDURE update_experiment ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_experimentID INT, + p_expGUID CHAR(36), + p_projectID INT, + p_runID VARCHAR(80), + p_labID INT, + p_instrumentID INT, + p_operatorID INT, + p_rotorID INT, + p_calibrationID INT, + p_type VARCHAR(30), + p_runType ENUM( 'RA', 'RI', 'IP', 'FI', 'WA', 'WI' ), + p_RIProfile LONGTEXT, + p_runTemp FLOAT, + p_label VARCHAR(80), + p_comment TEXT, + p_centrifugeProtocol TEXT ) + MODIFIES SQL DATA + +BEGIN + DECLARE l_count_runID INT; + DECLARE l_expGUID CHAR(36); + DECLARE duplicate_key TINYINT DEFAULT 0; + + DECLARE CONTINUE HANDLER FOR 1062 + SET duplicate_key = 1; + + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + + IF ( ( verify_experiment_permission( p_personGUID, p_password, p_experimentID ) = @OK ) && + ( verify_operator_permission( p_personGUID, p_password, + p_labID, p_instrumentID, p_operatorID ) = @OK ) && + ( check_GUID( p_personGUID, p_password, p_expGUID ) = @OK ) ) THEN + -- Let's make sure we don't result in a duplicate run ID's for this investigator + -- through renaming or some such + SELECT COUNT(*) + INTO l_count_runID + FROM experimentPerson p, experiment e + WHERE p.experimentID = e.experimentID + AND e.runID = p_runID + AND e.experimentID != p_experimentID + AND p.personID = @US3_ID; + + IF ( l_count_runID > 0 ) THEN + SET @US3_LAST_ERRNO = @DUPFIELD; + SET @US3_LAST_ERROR = "MySQL: Duplicate runID in experiment table"; + + ELSE + -- Keep MySQL from having trouble with duplicate GUID's + SELECT experimentGUID + INTO l_expGUID + FROM experiment + WHERE experimentID = p_experimentID; + + IF ( p_runType != 'RI' ) THEN + SET p_RIProfile = ''; + END IF; + + IF ( STRCMP( l_expGUID, p_expGUID ) = 0 ) THEN + UPDATE experiment SET + -- don't update experimentGUID here + projectID = p_projectID, + runID = p_runID, + labID = p_labID, + instrumentID = p_instrumentID, + operatorID = p_operatorID, + rotorID = p_rotorID, + rotorCalibrationID = p_calibrationID, + type = p_type, + runType = p_runType, + RIProfile = p_RIProfile, + runTemp = p_runTemp, + label = p_label, + comment = p_comment, + centrifugeProtocol = p_centrifugeProtocol, + dateUpdated = NOW() + WHERE experimentID = p_experimentID; + + ELSE + UPDATE experiment SET + experimentGUID = p_expGUID, + projectID = p_projectID, + runID = p_runID, + labID = p_labID, + instrumentID = p_instrumentID, + operatorID = p_operatorID, + rotorID = p_rotorID, + rotorCalibrationID = p_calibrationID, + type = p_type, + runType = p_runType, + RIProfile = p_RIProfile, + runTemp = p_runTemp, + label = p_label, + comment = p_comment, + centrifugeProtocol = p_centrifugeProtocol, + dateUpdated = NOW() + WHERE experimentID = p_experimentID; + + IF ( duplicate_key = 1 ) THEN + SET @US3_LAST_ERRNO = @INSERTDUP; + SET @US3_LAST_ERROR = "MySQL: Duplicate entry for experimentGUID field"; + + END IF; + + END IF; + + END IF; + + END IF; + + SELECT @US3_LAST_ERRNO AS status; + +END$$ + +-- Returns the experimentID, runID, and type of all experiments associated with p_ID +-- If p_ID = 0, retrieves information about all experiments in db +-- Regular user can only get info about his own experiments +DROP PROCEDURE IF EXISTS get_experiment_desc$$ +CREATE PROCEDURE get_experiment_desc ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_ID INT ) + READS SQL DATA + +BEGIN + + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + + IF ( verify_userlevel( p_personGUID, p_password, @US3_ADMIN ) = @OK ) THEN + -- This is an admin; he can get more info + IF ( count_experiments( p_personGUID, p_password, p_ID ) < 1 ) THEN + SET @US3_LAST_ERRNO = @NOROWS; + SET @US3_LAST_ERROR = 'MySQL: no rows returned'; + + SELECT @US3_LAST_ERRNO AS status; + + ELSE + SELECT @OK AS status; + + IF ( p_ID > 0 ) THEN + SELECT e.experimentID, runID, type, runType, label, + timestamp2UTC( dateUpdated ) AS UTC_dateUpdated + FROM experiment e, experimentPerson p + WHERE e.experimentID = p.experimentID + AND p.personID = p_ID + ORDER BY runID; + + ELSE + SELECT e.experimentID, runID, type, runType, label, + timestamp2UTC( dateUpdated ) AS UTC_dateUpdated + FROM experiment e, experimentPerson p + WHERE e.experimentID = p.experimentID + ORDER BY runID; + + END IF; + + END IF; + + ELSEIF ( verify_user( p_personGUID, p_password ) = @OK ) THEN + IF ( (p_ID != 0) && (p_ID != @US3_ID) ) THEN + -- Uh oh, can't do that + SET @US3_LAST_ERRNO = @NOTPERMITTED; + SET @US3_LAST_ERROR = 'MySQL: you do not have permission to view this experiment'; + + SELECT @US3_LAST_ERRNO AS status; + + ELSEIF ( count_experiments( p_personGUID, p_password, @US3_ID ) < 1 ) THEN + SET @US3_LAST_ERRNO = @NOROWS; + SET @US3_LAST_ERROR = 'MySQL: no rows returned'; + + SELECT @US3_LAST_ERRNO AS status; + + ELSE + -- Ok, user wants his own info + SELECT @OK AS status; + + SELECT e.experimentID, runID, type, runType + FROM experiment e, experimentPerson p + WHERE e.experimentID = p.experimentID + AND p.personID = @US3_ID + ORDER BY runID; + + END IF; + + END IF; + +END$$ + +-- Returns a more complete list of information about one experiment +DROP PROCEDURE IF EXISTS get_experiment_info$$ +CREATE PROCEDURE get_experiment_info ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_experimentID INT ) + READS SQL DATA + +BEGIN + DECLARE count_experiments INT; + + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + + SELECT COUNT(*) + INTO count_experiments + FROM experiment + WHERE experimentID = p_experimentID; + + IF ( verify_experiment_permission( p_personGUID, p_password, p_experimentID ) = @OK ) THEN + IF ( count_experiments = 0 ) THEN + SET @US3_LAST_ERRNO = @NOROWS; + SET @US3_LAST_ERROR = 'MySQL: no rows returned'; + + SELECT @US3_LAST_ERRNO AS status; + + ELSE + SELECT @OK AS status; + + SELECT experimentGUID, projectID, runID, labID, instrumentID, + operatorID, rotorID, rotorCalibrationID, type, runTemp, label, comment, + centrifugeProtocol, timestamp2UTC( dateUpdated ) AS UTC_dateUpdated, + personID, runType, RIProfile + FROM experiment e, experimentPerson ep + WHERE e.experimentID = ep.experimentID + AND e.experimentID = p_experimentID; + + END IF; + + ELSE + SELECT @US3_LAST_ERRNO AS status; + + END IF; + +END$$ + +-- Returns a more complete list of information about one experiment +-- Differs from previous procedure by searching by runID and personID +DROP PROCEDURE IF EXISTS get_experiment_info_by_runID$$ +CREATE PROCEDURE get_experiment_info_by_runID ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_runID VARCHAR(80), + p_ID INT ) + READS SQL DATA + +BEGIN + DECLARE count_experiments INT; + + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + + IF ( verify_userlevel( p_personGUID, p_password, @US3_ADMIN ) = @OK ) THEN + -- This is an admin; he can get more info + SELECT COUNT(*) + INTO count_experiments + FROM experiment exp, experimentPerson ep + WHERE exp.experimentID = ep.experimentID + AND ep.personID = p_ID + AND runID = p_runID; + + -- In this particular case, setting p_ID = 0 isn't particularly useful + -- and can lead to confusion, so let's trap it + IF ( p_ID = 0 ) THEN + SET @US3_LAST_ERRNO = @NO_PERSON; + SET @US3_LAST_ERROR = 'MySQL: No user with that ID exists'; + + SELECT @US3_LAST_ERRNO AS status; + + ELSEIF ( count_experiments = 0 ) THEN + SET @US3_LAST_ERRNO = @NOROWS; + SET @US3_LAST_ERROR = 'MySQL: no rows returned'; + + SELECT @US3_LAST_ERRNO AS status; + + ELSE + SELECT @OK AS status; + + SELECT projectID, ep.experimentID, experimentGUID, labID, instrumentID, + operatorID, rotorID, rotorCalibrationID, type, runTemp, label, comment, + centrifugeProtocol, timestamp2UTC( dateUpdated ) AS UTC_dateUpdated, + personID, runType, RIProfile + FROM experiment exp, experimentPerson ep + WHERE exp.experimentID = ep.experimentID + AND ep.personID = p_ID + AND runID = p_runID; + + END IF; + + ELSEIF ( verify_user( p_personGUID, p_password ) = @OK ) THEN + SELECT COUNT(*) + INTO count_experiments + FROM experiment exp, experimentPerson ep + WHERE exp.experimentID = ep.experimentID + AND ep.personID = @US3_ID + AND runID = p_runID; + + IF ( count_experiments = 0 ) THEN + SET @US3_LAST_ERRNO = @NOROWS; + SET @US3_LAST_ERROR = 'MySQL: no rows returned'; + + SELECT @US3_LAST_ERRNO AS status; + + ELSE + SELECT @OK AS status; + + SELECT projectID, ep.experimentID, experimentGUID, labID, instrumentID, + operatorID, rotorID, rotorCalibrationID, type, runTemp, label, comment, + centrifugeProtocol, timestamp2UTC( dateUpdated ) AS UTC_dateUpdated, + personID, runType, RIProfile + FROM experiment exp, experimentPerson ep + WHERE exp.experimentID = ep.experimentID + AND ep.personID = @US3_ID + AND runID = p_runID; + + END IF; + + ELSE + SELECT @US3_LAST_ERRNO AS status; + + END IF; + +END$$ + +-- DELETEs an experiment, plus information in related tables +DROP PROCEDURE IF EXISTS delete_experiment$$ +CREATE PROCEDURE delete_experiment ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_experimentID INT ) + MODIFIES SQL DATA + +BEGIN + DECLARE no_more_requestIDs TINYINT DEFAULT 0; + DECLARE l_requestID INT; + DECLARE l_reportID INT; + + -- Cursor for iterating through HPC requestID's + DECLARE request_csr CURSOR FOR + SELECT HPCAnalysisRequestID FROM HPCAnalysisRequest + WHERE experimentID = p_experimentID; + + DECLARE CONTINUE HANDLER FOR NOT FOUND + SET no_more_requestIDs = 1; + + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + SET no_more_requestIDs = 0; + + IF ( verify_experiment_permission( p_personGUID, p_password, p_experimentID ) = @OK ) THEN + + -- Make sure records match if they have related tables or not + -- Have to do it in a couple of stages because of the constraints + DELETE noise + FROM rawData + LEFT JOIN editedData ON ( editedData.rawDataID = rawData.rawDataID ) + LEFT JOIN model ON ( model.editedDataID = editedData.editedDataID ) + LEFT JOIN noise ON ( noise.modelID = model.modelID ) + WHERE rawData.experimentID = p_experimentID; + + DELETE model, modelPerson + FROM rawData + LEFT JOIN editedData ON ( editedData.rawDataID = rawData.rawDataID ) + LEFT JOIN model ON ( model.editedDataID = editedData.editedDataID ) + LEFT JOIN modelPerson ON ( modelPerson.modelID = model.modelID ) + WHERE rawData.experimentID = p_experimentID; + + -- Delete all the HPC info + -- Commented out for now, to retain for data mining, etc. + -- OPEN request_csr; + -- request_loop:REPEAT + -- FETCH request_csr INTO l_requestID; + -- IF ( no_more_requestIDs = 0 ) THEN + -- CALL delete_HPCRequest( p_personGUID, p_password, l_requestID ); + -- END IF; + -- UNTIL no_more_requestIDs + -- END REPEAT request_loop; + -- CLOSE request_csr; + -- SET no_more_requestIDs = 0; + + -- Delete any associated reports + SELECT reportID INTO l_reportID + FROM report + WHERE experimentID = p_experimentID; + + CALL delete_report( p_personGUID, p_password, l_reportID ); + + DELETE editedData + FROM rawData + LEFT JOIN editedData ON ( editedData.rawDataID = rawData.rawDataID ) + WHERE rawData.experimentID = p_experimentID; + + DELETE FROM rawData + WHERE experimentID = p_experimentID; + + DELETE FROM experimentPerson + WHERE experimentID = p_experimentID; + + DELETE FROM speedstep + WHERE experimentID = p_experimentID; + + DELETE FROM timestate + WHERE experimentID = p_experimentID; + + DELETE FROM experiment + WHERE experimentID = p_experimentID; + + END IF; + + SELECT @US3_LAST_ERRNO AS status; + +END$$ + +-- DELETEs an HPC request, plus information in related tables +DROP PROCEDURE IF EXISTS delete_HPCRequest$$ +CREATE PROCEDURE delete_HPCRequest ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_requestID INT ) + MODIFIES SQL DATA + +BEGIN + DECLARE l_investigatorGUID CHAR(36); + DECLARE l_method ENUM('2DSA','2DSA_MW','GA','GA_MW','GA_SC','DMGA','PCSA'); + + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + + -- First find out the investigatorGUID, so we can see if this is the user's own data + SELECT investigatorGUID + INTO l_investigatorGUID + FROM HPCAnalysisRequest + WHERE HPCAnalysisRequestID = p_requestID; + + -- Either we're an admin or we're a regular user but it's our own analysis + IF ( ( verify_userlevel( p_personGUID, p_password, @US3_ADMIN ) = @OK ) || + ( ( verify_user( p_personGUID, p_password ) = @OK ) && + ( l_investigatorGUID = p_personGUID ) ) ) THEN + + -- Make sure records match if they have related tables or not + -- Have to do it in a couple of stages because of the constraints + DELETE HPCAnalysisResultData, noise + FROM HPCAnalysisResult + LEFT JOIN HPCAnalysisResultData + ON ( HPCAnalysisResult.HPCAnalysisResultID = HPCAnalysisResultData.HPCAnalysisResultID ) + LEFT JOIN noise + ON ( HPCAnalysisResultData.resultID = noise.noiseID ) + WHERE HPCAnalysisResultData.HPCAnalysisResultType = 'noise' + AND HPCAnalysisResult.HPCAnalysisRequestID = p_requestID; + + DELETE HPCAnalysisResultData, model, modelPerson + FROM HPCAnalysisResult + LEFT JOIN HPCAnalysisResultData + ON ( HPCAnalysisResult.HPCAnalysisResultID = HPCAnalysisResultData.HPCAnalysisResultID ) + LEFT JOIN model ON ( HPCAnalysisResultData.resultID = model.modelID ) + LEFT JOIN modelPerson ON ( model.modelID = modelPerson.modelID ) + WHERE HPCAnalysisResultData.HPCAnalysisResultType = 'model' + AND HPCAnalysisResult.HPCAnalysisRequestID = p_requestID; + + DELETE FROM HPCAnalysisResult + WHERE HPCAnalysisResult.HPCAnalysisRequestID = p_requestID; + + DELETE HPCDataset, HPCRequestData + FROM HPCAnalysisRequest + LEFT JOIN HPCDataset + ON ( HPCAnalysisRequest.HPCAnalysisRequestID = HPCDataset.HPCAnalysisRequestID ) + LEFT JOIN HPCRequestData + ON ( HPCDataset.HPCDatasetID = HPCDataset.HPCDatasetID ) + WHERE HPCAnalysisRequest.HPCAnalysisRequestID = p_requestID; + + IF ( l_method = '2DSA' ) THEN + DELETE FROM 2DSA_Settings + WHERE HPCAnalysisRequestID = p_requestID; + + ELSEIF ( l_method = '2DSA_MW' ) THEN + DELETE FROM 2DSA_MW_Settings + WHERE HPCAnalysisRequestID = p_requestID; + + ELSEIF ( l_method = 'GA_MW' ) THEN + DELETE FROM GA_MW_Settings + WHERE HPCAnalysisRequestID = p_requestID; + + ELSEIF ( l_method = 'GA_SC' ) THEN + DELETE FROM GA_SC_Settings + WHERE HPCAnalysisRequestID = p_requestID; + + ELSEIF ( l_method = 'GA' ) THEN + DELETE GA_Settings, HPCSoluteData + FROM GA_Settings + LEFT JOIN HPCSoluteData + ON ( GA_Settings.GA_SettingsID = HPCSoluteData.GA_SettingsID ) + WHERE HPCAnalysisRequestID = p_requestID; + + ELSEIF ( l_method = 'DMGA' ) THEN + DELETE FROM DMGA_Settings + WHERE HPCAnalysisRequestID = p_requestID; + + ELSEIF ( l_method = 'PCSA' ) THEN + DELETE FROM PCSA_Settings + WHERE HPCAnalysisRequestID = p_requestID; + + END IF; + + -- Finally, the parent HPC table + DELETE FROM HPCAnalysisRequest + WHERE HPCAnalysisRequestID = p_requestID; + + ELSEIF ( ( verify_userlevel( p_personGUID, p_password, @US3_ADMIN ) != @OK ) && + ( ( verify_user( p_personGUID, p_password ) != @OK ) || + ( l_investigatorGUID = p_personGUID ) ) ) THEN + + SET @US3_LAST_ERRNO = @NOTPERMITTED; + SET @US3_LAST_ERROR = 'MySQL: you do not have permission to delete this analysis'; + + END IF; + + SELECT @US3_LAST_ERRNO AS status; + +END$$ + +-- adds a new cell table record, relating an experiment to the cell and centerpiece info +DROP PROCEDURE IF EXISTS new_cell_experiment$$ +CREATE PROCEDURE new_cell_experiment ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_cellGUID CHAR(36), + p_name TEXT, + p_holeNumber INT(11), + p_abstractCenterpieceID INT, + p_experimentID INT ) + MODIFIES SQL DATA + +BEGIN + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS; + SET @@FOREIGN_KEY_CHECKS=0; + + IF ( verify_experiment_permission( p_personGUID, p_password, p_experimentID ) = @OK ) THEN + INSERT INTO cell SET + cellGUID = p_cellGUID, + name = p_name, + holeNumber = p_holeNumber, + experimentID = p_experimentID, + abstractCenterpieceID = p_abstractCenterpieceID, + dateUpdated = NOW(); + + END IF; + + SET @@FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; + + SELECT @US3_LAST_ERRNO AS status; + +END$$ + +-- SELECTs all cell table records relating to an experiment +DROP PROCEDURE IF EXISTS all_cell_experiments$$ +CREATE PROCEDURE all_cell_experiments ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_experimentID INT ) + READS SQL DATA + +BEGIN + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + + IF ( verify_experiment_permission( p_personGUID, p_password, p_experimentID ) = @OK ) THEN + SELECT @OK AS status; + + SELECT cellID, cellGUID, name, holeNumber, abstractCenterpieceID + FROM cell + WHERE experimentID = p_experimentID + ORDER BY dateUpdated DESC; + + ELSE + SELECT @US3_LAST_ERRNO AS status; + + END IF; + +END$$ + +-- DELETEs all cell table records associated with an experiment +DROP PROCEDURE IF EXISTS delete_cell_experiments$$ +CREATE PROCEDURE delete_cell_experiments ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_experimentID INT ) + MODIFIES SQL DATA + +BEGIN + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + + IF ( verify_experiment_permission( p_personGUID, p_password, p_experimentID ) = @OK ) THEN + DELETE FROM cell + WHERE experimentID = p_experimentID; + + END IF; + + SELECT @US3_LAST_ERRNO AS status; + +END$$ + +-- adds a new speedstep table record, relating an experiment to the speedstep +DROP PROCEDURE IF EXISTS new_speedstep$$ +CREATE PROCEDURE new_speedstep ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_experimentID INT(11), + p_scans INT(11), + p_durationhrs INT(11), + p_durationmins DOUBLE, + p_delayhrs INT(11), + p_delaymins DOUBLE, + p_rotorspeed INT(11), + p_acceleration INT(11), + p_accelerflag TINYINT(1), + p_w2tfirst FLOAT, + p_w2tlast FLOAT, + p_timefirst INT(11), + p_timelast INT(11), + p_setspeed INT(11), + p_avgspeed FLOAT, + p_speedsdev FLOAT ) + MODIFIES SQL DATA + +BEGIN + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS; + SET @@FOREIGN_KEY_CHECKS=0; + + IF ( verify_experiment_permission( p_personGUID, p_password, p_experimentID ) = @OK ) THEN + INSERT INTO speedstep SET + experimentID = p_experimentID, + scans = p_scans, + durationhrs = p_durationhrs, + durationmins = p_durationmins, + delayhrs = p_delayhrs, + delaymins = p_delaymins, + rotorspeed = p_rotorspeed, + acceleration = p_acceleration, + accelerflag = p_accelerflag, + w2tfirst = p_w2tfirst, + w2tlast = p_w2tlast, + timefirst = p_timefirst, + timelast = p_timelast, + setspeed = p_setspeed, + avgspeed = p_avgspeed, + speedsdev = p_speedsdev; + + END IF; + + SET @@FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; + + SELECT @US3_LAST_ERRNO AS status; + +END$$ + +-- SELECTs all speedstep table records relating to an experiment +DROP PROCEDURE IF EXISTS all_speedsteps$$ +CREATE PROCEDURE all_speedsteps ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_experimentID INT ) + READS SQL DATA + +BEGIN + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + + IF ( verify_experiment_permission( p_personGUID, p_password, p_experimentID ) = @OK ) THEN + SELECT @OK AS status; + + SELECT speedstepID, scans, durationhrs, durationmins, delayhrs, delaymins, + rotorspeed, acceleration, accelerflag, w2tfirst, w2tlast, timefirst, timelast, + setspeed, avgspeed, speedsdev + FROM speedstep + WHERE experimentID = p_experimentID + ORDER BY speedstepID; + + ELSE + SELECT @US3_LAST_ERRNO AS status; + + END IF; + +END$$ + +-- DELETEs all speedstep table records associated with an experiment +DROP PROCEDURE IF EXISTS delete_speedsteps$$ +CREATE PROCEDURE delete_speedsteps ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_experimentID INT ) + MODIFIES SQL DATA + +BEGIN + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + + IF ( verify_experiment_permission( p_personGUID, p_password, p_experimentID ) = @OK ) THEN + DELETE FROM speedstep + WHERE experimentID = p_experimentID; + + END IF; + + SELECT @US3_LAST_ERRNO AS status; + +END$$ + Added: trunk/lims_upds/us3_insert_bc.sh =================================================================== --- trunk/lims_upds/us3_insert_bc.sh (rev 0) +++ trunk/lims_upds/us3_insert_bc.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,15 @@ +# One time script to update all active US3 databases +# by newly inserting all bufferComponents entries + +LOGIN="-u root -p`cat ~/.sec/.pwsq`" +cd ~us3/lims/database +##for db in `cat dblist.txt` +##for db in uslims3_devel +##for db in uslims3_cauma3 +for db in `cat dblist.txt` + +do + echo "Updating $db" + mysql $LOGIN $db <sql/us3_buffer_components.sql +done + Property changes on: trunk/lims_upds/us3_insert_bc.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_model_procs.sql =================================================================== --- trunk/lims_upds/us3_model_procs.sql (rev 0) +++ trunk/lims_upds/us3_model_procs.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,774 @@ +-- +-- us3_model_procs.sql +-- +-- Script to set up the MySQL stored procedures for the US3 system +-- These are related to the model tables +-- Run as us3admin +-- + +DELIMITER $$ + +-- Verifies that the user has permission to view or modify +-- the specified model +DROP FUNCTION IF EXISTS verify_model_permission$$ +CREATE FUNCTION verify_model_permission( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_modelID INT ) + RETURNS INT + READS SQL DATA + +BEGIN + DECLARE count_models INT; + DECLARE count_permissions INT; + DECLARE status INT; + + CALL config(); + SET status = @ERROR; + SET @US3_LAST_ERROR = 'MySQL: error verifying model permission'; + + SELECT COUNT(*) + INTO count_models + FROM model + WHERE modelID = p_modelID; + + SELECT COUNT(*) + INTO count_permissions + FROM modelPerson + WHERE modelID = p_modelID + AND personID = @US3_ID; + + IF ( count_models = 0 ) THEN + SET @US3_LAST_ERRNO = @NO_MODEL; + SET @US3_LAST_ERROR = 'MySQL: the specified model does not exist'; + + SET status = @NO_MODEL; + + ELSEIF ( verify_userlevel( p_personGUID, p_password, @US3_ADMIN ) = @OK ) THEN + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + + SET status = @OK; + + ELSEIF ( ( verify_user( p_personGUID, p_password ) = @OK ) && + ( count_permissions > 0 ) ) THEN + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + + SET status = @OK; + + ELSE + SET @US3_LAST_ERRNO = @NOTPERMITTED; + SET @US3_LAST_ERROR = 'MySQL: you do not have permission to view or modify this model'; + + SET status = @NOTPERMITTED; + + END IF; + + RETURN( status ); + +END$$ + +-- Returns the count of models associated with p_ID +-- If p_ID = 0, retrieves count of all models in db +-- Regular user can only get count of his own models +DROP FUNCTION IF EXISTS count_models$$ +CREATE FUNCTION count_models( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_ID INT ) + RETURNS INT + READS SQL DATA + +BEGIN + + DECLARE count_models INT; + + CALL config(); + SET count_models = 0; + + IF ( verify_userlevel( p_personGUID, p_password, @US3_ADMIN ) = @OK ) THEN + -- This is an admin; he can get more info + IF ( p_ID > 0 ) THEN + SELECT COUNT(*) + INTO count_models + FROM modelPerson + WHERE personID = p_ID; + + ELSE + SELECT COUNT(*) + INTO count_models + FROM modelPerson; + + END IF; + + ELSEIF ( verify_user( p_personGUID, p_password ) = @OK ) THEN + IF ( (p_ID != 0) && (p_ID != @US3_ID) ) THEN + -- Uh oh, can't do that + SET @US3_LAST_ERRNO = @NOTPERMITTED; + SET @US3_LAST_ERROR = 'MySQL: you do not have permission to view those models'; + + ELSE + -- This person is asking about his own models + -- Ignore p_ID and return user's own + SELECT COUNT(*) + INTO count_models + FROM modelPerson + WHERE personID = @US3_ID; + + END IF; + + END IF; + + RETURN( count_models ); + +END$$ + +-- Returns the count of models associated with p_ID with a given editedDataID +-- p_ID cannot be 0 +-- Admin user can get count of models that belong to others +DROP FUNCTION IF EXISTS count_models_by_editID$$ +CREATE FUNCTION count_models_by_editID( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_ID INT, + p_editID INT ) + RETURNS INT + READS SQL DATA + +BEGIN + + DECLARE count_models INT; + + CALL config(); + SET count_models = 0; + + IF ( p_ID <= 0 ) THEN + -- Gotta have a real ID + RETURN ( 0 ); + + ELSEIF ( verify_userlevel( p_personGUID, p_password, @US3_ADMIN ) = @OK ) THEN + -- This is an admin; he can get more info + SELECT COUNT(*) + INTO count_models + FROM modelPerson, model + WHERE personID = p_ID + AND modelPerson.modelID = model.modelID + AND editedDataID = p_editID; + + ELSEIF ( verify_user( p_personGUID, p_password ) = @OK ) THEN + IF ( (p_ID != 0) && (p_ID != @US3_ID) ) THEN + -- Uh oh, can't do that + SET @US3_LAST_ERRNO = @NOTPERMITTED; + SET @US3_LAST_ERROR = 'MySQL: you do not have permission to view those models'; + + ELSE + -- This person is asking about his own models + -- Ignore p_ID and return user's own + SELECT COUNT(*) + INTO count_models + FROM modelPerson, model + WHERE personID = @US3_ID + AND modelPerson.modelID = model.modelID + AND editedDataID = p_editID; + + END IF; + + END IF; + + RETURN( count_models ); + +END$$ + +-- Returns the count of models associated with p_ID with a given runID +-- p_ID cannot be 0 +-- Admin user can get count of models that belong to others +DROP FUNCTION IF EXISTS count_models_by_runID$$ +CREATE FUNCTION count_models_by_runID( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_ID INT, + p_runID VARCHAR(60) ) + RETURNS INT + READS SQL DATA + +BEGIN + + DECLARE count_models INT; + DECLARE run_pattern VARCHAR(80); + + CALL config(); + SET count_models = 0; + SET run_pattern = CONCAT( p_runID, '.%' ); + + IF ( p_ID <= 0 ) THEN + -- Gotta have a real ID + RETURN ( 0 ); + + ELSEIF ( verify_userlevel( p_personGUID, p_password, @US3_ADMIN ) = @OK ) THEN + -- This is an admin; he can get more info + SELECT COUNT(*) + INTO count_models + FROM modelPerson, model + WHERE personID = p_ID + AND modelPerson.modelID = model.modelID + AND description LIKE run_pattern ; + + ELSEIF ( verify_user( p_personGUID, p_password ) = @OK ) THEN + IF ( (p_ID != 0) && (p_ID != @US3_ID) ) THEN + -- Uh oh, can't do that + SET @US3_LAST_ERRNO = @NOTPERMITTED; + SET @US3_LAST_ERROR = 'MySQL: you do not have permission to view those models'; + + ELSE + -- This person is asking about his own models + -- Ignore p_ID and return user's own + SELECT COUNT(*) + INTO count_models + FROM modelPerson, model + WHERE personID = @US3_ID + AND modelPerson.modelID = model.modelID + AND description LIKE run_pattern ; + + END IF; + + END IF; + + RETURN( count_models ); + +END$$ + +-- INSERTs a new model with the specified information +DROP PROCEDURE IF EXISTS new_model$$ +CREATE PROCEDURE new_model ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_modelGUID CHAR(36), + p_description TEXT, + p_xml LONGTEXT, + p_variance DOUBLE, + p_meniscus DOUBLE, + p_editGUID CHAR(36), + p_ownerID INT ) + MODIFIES SQL DATA + +BEGIN + DECLARE l_modelID INT; + DECLARE l_count_editID INT; + DECLARE l_editID INT; + + DECLARE duplicate_key TINYINT DEFAULT 0; + DECLARE null_field TINYINT DEFAULT 0; + + DECLARE CONTINUE HANDLER FOR 1062 + SET duplicate_key = 1; + + DECLARE CONTINUE HANDLER FOR 1048 + SET null_field = 1; + + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + SET @LAST_INSERT_ID = 0; + + -- Translate editGUID into editedDataID + SET l_editID = 1; -- default to special "unassigned" record + SELECT COUNT(*) + INTO l_count_editID + FROM editedData + WHERE editGUID = p_editGUID; + + IF ( l_count_editID > 0 ) THEN + SELECT editedDataID + INTO l_editID + FROM editedData + WHERE editGUID = p_editGUID + LIMIT 1; + + END IF; + + IF ( ( verify_user( p_personGUID, p_password ) = @OK ) && + ( check_GUID ( p_personGUID, p_password, p_modelGUID ) = @OK ) ) THEN + INSERT INTO model SET + editedDataID = l_editID, + modelGUID = p_modelGUID, + description = p_description, + xml = p_xml, + variance = p_variance, + meniscus = p_meniscus, + lastUpdated = NOW(); + + IF ( duplicate_key = 1 ) THEN + SET @US3_LAST_ERRNO = @INSERTDUP; + SET @US3_LAST_ERROR = "MySQL: Duplicate entry for modelGUID field"; + + ELSEIF ( null_field = 1 ) THEN + SET @US3_LAST_ERRNO = @INSERTNULL; + SET @US3_LAST_ERROR = "MySQL: NULL value for modelGUID field"; + + ELSE + SET @LAST_INSERT_ID = LAST_INSERT_ID(); + + INSERT INTO modelPerson SET + modelID = @LAST_INSERT_ID, + personID = p_ownerID; + + END IF; + + END IF; + + SELECT @US3_LAST_ERRNO AS status; + +END$$ + +-- UPDATEs an existing model with the specified information +DROP PROCEDURE IF EXISTS update_model$$ +CREATE PROCEDURE update_model ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_modelID INT, + p_description TEXT, + p_xml LONGTEXT, + p_variance DOUBLE, + p_meniscus DOUBLE, + p_editGUID CHAR(36) ) + MODIFIES SQL DATA + +BEGIN + DECLARE not_found TINYINT DEFAULT 0; + DECLARE l_count_editID INT; + DECLARE l_editID INT; + + DECLARE CONTINUE HANDLER FOR NOT FOUND + SET not_found = 1; + + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + + -- Translate editGUID into editedDataID + SET l_editID = 1; -- default to special "unassigned" record + + SELECT COUNT(*) -- see if we can find the existing record + INTO l_count_editID + FROM model + WHERE modelID = p_modelID; + + IF ( l_count_editID > 0 ) THEN + SELECT editedDataID -- replace default with what's there already + INTO l_editID + FROM model + WHERE modelID = p_modelID; + + END IF; + + SELECT COUNT(*) -- See if the supplied parameter really does point to a record + INTO l_count_editID + FROM editedData + WHERE editGUID = p_editGUID; + + IF ( l_count_editID > 0 ) THEN + SELECT editedDataID + INTO l_editID + FROM editedData + WHERE editGUID = p_editGUID + LIMIT 1; + + END IF; + + IF ( verify_model_permission( p_personGUID, p_password, p_modelID ) = @OK ) THEN + UPDATE model SET + editedDataID = l_editID, + description = p_description, + xml = p_xml, + variance = p_variance, + meniscus = p_meniscus, + lastUpdated = NOW() + WHERE modelID = p_modelID; + + IF ( not_found = 1 ) THEN + SET @US3_LAST_ERRNO = @NO_MODEL; + SET @US3_LAST_ERROR = "MySQL: No model with that ID exists"; + + ELSE + SET @LAST_INSERT_ID = LAST_INSERT_ID(); + + END IF; + + END IF; + + SELECT @US3_LAST_ERRNO AS status; + +END$$ + +-- Returns the modelID associated with the given modelGUID +DROP PROCEDURE IF EXISTS get_modelID$$ +CREATE PROCEDURE get_modelID ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_modelGUID CHAR(36) ) + READS SQL DATA + +BEGIN + + DECLARE count_models INT; + + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + SET count_models = 0; + + IF ( verify_user( p_personGUID, p_password ) = @OK ) THEN + + SELECT COUNT(*) + INTO count_models + FROM model + WHERE modelGUID = p_modelGUID; + + IF ( TRIM( p_modelGUID ) = '' ) THEN + SET @US3_LAST_ERRNO = @EMPTY; + SET @US3_LAST_ERROR = CONCAT( 'MySQL: The modelGUID parameter to the ', + 'get_modelID function cannot be empty' ); + + ELSEIF ( count_models < 1 ) THEN + SET @US3_LAST_ERRNO = @NOROWS; + SET @US3_LAST_ERROR = 'MySQL: no rows returned'; + + SELECT @US3_LAST_ERRNO AS status; + + ELSE + SELECT @OK AS status; + + SELECT modelID + FROM model + WHERE modelGUID = p_modelGUID; + + END IF; + + END IF; + +END$$ + +-- Returns the modelID and description of all models associated with p_ID +-- If p_ID = 0, retrieves information about all models in db +-- Regular user can only get info about his own models +DROP PROCEDURE IF EXISTS get_model_desc$$ +CREATE PROCEDURE get_model_desc ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_ID INT ) + READS SQL DATA + +BEGIN + + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + + IF ( verify_userlevel( p_personGUID, p_password, @US3_ADMIN ) = @OK ) THEN + -- This is an admin; he can get more info + IF ( count_models( p_personGUID, p_password, p_ID ) < 1 ) THEN + SET @US3_LAST_ERRNO = @NOROWS; + SET @US3_LAST_ERROR = 'MySQL: no rows returned'; + + SELECT @US3_LAST_ERRNO AS status; + + ELSE + SELECT @OK AS status; + + IF ( p_ID > 0 ) THEN + SELECT m.modelID, modelGUID, description, m.variance, m.meniscus, + editGUID, m.editedDataID, + timestamp2UTC( m.lastUpdated ) AS UTC_lastUpdated, + MD5( xml ) AS checksum, LENGTH( xml ) AS size + FROM modelPerson, model m, editedData + WHERE modelPerson.modelID = m.modelID + AND m.editedDataID = editedData.editedDataID + AND modelPerson.personID = @US3_ID + ORDER BY m.modelID DESC; + + ELSE + SELECT m.modelID, modelGUID, description, m.variance, m.meniscus, + editGUID, m.editedDataID, + timestamp2UTC( m.lastUpdated ) AS UTC_lastUpdated, + MD5( xml ) AS checksum, LENGTH( xml ) AS size + FROM modelPerson, model m, editedData + WHERE modelPerson.modelID = m.modelID + AND m.editedDataID = editedData.editedDataID + ORDER BY m.modelID DESC; + + END IF; + + END IF; + + ELSEIF ( verify_user( p_personGUID, p_password ) = @OK ) THEN + IF ( (p_ID != 0) && (p_ID != @US3_ID) ) THEN + -- Uh oh, can't do that + SET @US3_LAST_ERRNO = @NOTPERMITTED; + SET @US3_LAST_ERROR = 'MySQL: you do not have permission to view this model'; + + SELECT @US3_LAST_ERRNO AS status; + + ELSEIF ( count_models( p_personGUID, p_password, @US3_ID ) < 1 ) THEN + SET @US3_LAST_ERRNO = @NOROWS; + SET @US3_LAST_ERROR = 'MySQL: no rows returned'; + + SELECT @US3_LAST_ERRNO AS status; + + ELSE + -- Ok, user wants his own info + SELECT @OK AS status; + + SELECT m.modelID, modelGUID, description, m.variance, m.meniscus, + editGUID, m.editedDataID, + timestamp2UTC( m.lastUpdated ) AS UTC_lastUpdated, + MD5( xml ) AS checksum, LENGTH( xml ) AS size + FROM modelPerson, model m, editedData + WHERE modelPerson.modelID = m.modelID + AND m.editedDataID = editedData.editedDataID + AND modelPerson.personID = @US3_ID + ORDER BY m.modelID DESC; + + + END IF; + + END IF; + +END$$ + +-- Returns the modelID and description of all models associated with p_ID and editID +-- Unlike get_model_desc, in this function p_ID cannot be 0 +-- Admin can view p_ID/editID combinations that belong to anyone +DROP PROCEDURE IF EXISTS get_model_desc_by_editID$$ +CREATE PROCEDURE get_model_desc_by_editID ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_ID INT, + p_editID INT ) + READS SQL DATA + +BEGIN + + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + + IF ( p_ID <= 0 ) THEN + -- Gotta have a real ID + SET @US3_LAST_ERRNO = @EMPTY; + SET @US3_LAST_ERROR = 'MySQL: The ID cannot be 0'; + + SELECT @US3_LAST_ERRNO AS status; + + ELSEIF ( verify_userlevel( p_personGUID, p_password, @US3_ADMIN ) = @OK ) THEN + -- This is an admin; he can get info that belongs to others + IF ( count_models_by_editID( p_personGUID, p_password, p_ID, p_editID ) < 1 ) THEN + SET @US3_LAST_ERRNO = @NOROWS; + SET @US3_LAST_ERROR = 'MySQL: no rows returned'; + + SELECT @US3_LAST_ERRNO AS status; + + ELSE + SELECT @OK AS status; + + SELECT m.modelID, modelGUID, description, m.variance, m.meniscus, + editGUID, m.editedDataID, + timestamp2UTC( m.lastUpdated ) AS UTC_lastUpdated, + MD5( xml ) AS checksum, LENGTH( xml ) AS size + FROM modelPerson, model m, editedData + WHERE personID = p_ID + AND modelPerson.modelID = m.modelID + AND m.editedDataID = p_editID + AND m.editedDataID = editedData.editedDataID + ORDER BY m.modelID DESC; + + END IF; + + ELSEIF ( verify_user( p_personGUID, p_password ) = @OK ) THEN + IF ( (p_ID != 0) && (p_ID != @US3_ID) ) THEN + -- Uh oh, can't do that + SET @US3_LAST_ERRNO = @NOTPERMITTED; + SET @US3_LAST_ERROR = 'MySQL: you do not have permission to view this model'; + + SELECT @US3_LAST_ERRNO AS status; + + ELSEIF ( count_models_by_editID( p_personGUID, p_password, @US3_ID, p_editID ) < 1 ) THEN + SET @US3_LAST_ERRNO = @NOROWS; + SET @US3_LAST_ERROR = 'MySQL: no rows returned'; + + SELECT @US3_LAST_ERRNO AS status; + + ELSE + -- Ok, user wants his own info + SELECT @OK AS status; + + SELECT m.modelID, modelGUID, description, m.variance, m.meniscus, + editGUID, m.editedDataID, + timestamp2UTC( m.lastUpdated ) AS UTC_lastUpdated, + MD5( xml ) AS checksum, LENGTH( xml ) AS size + FROM modelPerson, model m, editedData + WHERE personID = @US3_ID + AND modelPerson.modelID = m.modelID + AND m.editedDataID = p_editID + AND m.editedDataID = editedData.editedDataID + ORDER BY m.modelID DESC; + + END IF; + + END IF; + +END$$ + +-- Returns the modelID and description of all models associated with p_ID and runID +-- Unlike get_model_desc, in this function p_ID cannot be 0 +-- Admin can view p_ID/runID combinations that belong to anyone +DROP PROCEDURE IF EXISTS get_model_desc_by_runID$$ +CREATE PROCEDURE get_model_desc_by_runID ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_ID INT, + p_runID VARCHAR(60) ) + READS SQL DATA + +BEGIN + + DECLARE run_pattern VARCHAR(80); + + CALL config(); + SET run_pattern = CONCAT( p_runID, '.%' ); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + + IF ( p_ID <= 0 ) THEN + -- Gotta have a real ID + SET @US3_LAST_ERRNO = @EMPTY; + SET @US3_LAST_ERROR = 'MySQL: The ID cannot be 0'; + + SELECT @US3_LAST_ERRNO AS status; + + ELSEIF ( verify_userlevel( p_personGUID, p_password, @US3_ADMIN ) = @OK ) THEN + -- This is an admin; he can get info that belongs to others + IF ( count_models_by_runID( p_personGUID, p_password, p_ID, p_runID ) < 1 ) THEN + SET @US3_LAST_ERRNO = @NOROWS; + SET @US3_LAST_ERROR = 'MySQL: no rows returned'; + + SELECT @US3_LAST_ERRNO AS status; + + ELSE + SELECT @OK AS status; + + SELECT m.modelID, modelGUID, description, m.variance, m.meniscus, + editGUID, m.editedDataID, + timestamp2UTC( m.lastUpdated ) AS UTC_lastUpdated, + MD5( xml ) AS checksum, LENGTH( xml ) AS size + FROM modelPerson, model m, editedData + WHERE personID = p_ID + AND modelPerson.modelID = m.modelID + AND m.editedDataID = editedData.editedDataID + AND description LIKE run_pattern + ORDER BY m.modelID DESC; + + END IF; + + ELSEIF ( verify_user( p_personGUID, p_password ) = @OK ) THEN + IF ( (p_ID != 0) && (p_ID != @US3_ID) ) THEN + -- Uh oh, can't do that + SET @US3_LAST_ERRNO = @NOTPERMITTED; + SET @US3_LAST_ERROR = 'MySQL: you do not have permission to view this model'; + + SELECT @US3_LAST_ERRNO AS status; + + ELSEIF ( count_models_by_runID( p_personGUID, p_password, @US3_ID, p_runID ) < 1 ) THEN + SET @US3_LAST_ERRNO = @NOROWS; + SET @US3_LAST_ERROR = 'MySQL: no rows returned'; + + SELECT @US3_LAST_ERRNO AS status; + + ELSE + -- Ok, user wants his own info + SELECT @OK AS status; + + SELECT m.modelID, modelGUID, description, m.variance, m.meniscus, + editGUID, m.editedDataID, + timestamp2UTC( m.lastUpdated ) AS UTC_lastUpdated, + MD5( xml ) AS checksum, LENGTH( xml ) AS size + FROM modelPerson, model m, editedData + WHERE personID = @US3_ID + AND modelPerson.modelID = m.modelID + AND m.editedDataID = editedData.editedDataID + AND description LIKE run_pattern + ORDER BY m.modelID DESC; + + END IF; + + END IF; + +END$$ + +-- Returns a more complete list of information about one model +DROP PROCEDURE IF EXISTS get_model_info$$ +CREATE PROCEDURE get_model_info ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_modelID INT ) + READS SQL DATA + +BEGIN + DECLARE count_models INT; + + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + + SELECT COUNT(*) + INTO count_models + FROM model + WHERE modelID = p_modelID; + + IF ( verify_model_permission( p_personGUID, p_password, p_modelID ) = @OK ) THEN + IF ( count_models = 0 ) THEN + SET @US3_LAST_ERRNO = @NOROWS; + SET @US3_LAST_ERROR = 'MySQL: no rows returned'; + + SELECT @US3_LAST_ERRNO AS status; + + ELSE + SELECT @OK AS status; + + SELECT modelGUID, description, xml, variance, meniscus, personID, + timestamp2UTC( lastUpdated ) AS UTC_lastUpdated, + MD5( xml ) AS checksum, LENGTH( xml ) AS size + FROM model m, modelPerson mp + WHERE m.modelID = mp.modelID + AND m.modelID = p_modelID; + + END IF; + + ELSE + SELECT @US3_LAST_ERRNO AS status; + + END IF; + +END$$ + +-- DELETEs a model, plus information in related tables +DROP PROCEDURE IF EXISTS delete_model$$ +CREATE PROCEDURE delete_model ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_modelID INT ) + MODIFIES SQL DATA + +BEGIN + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + + IF ( verify_model_permission( p_personGUID, p_password, p_modelID ) = @OK ) THEN + + -- Make sure records match if they have related tables or not + -- Have to do it in a couple of stages because of the constraints + DELETE noise + FROM model + LEFT JOIN noise ON ( noise.modelID = model.modelID ) + WHERE model.modelID = p_modelID; + + DELETE FROM modelPerson + WHERE modelID = p_modelID; + + DELETE FROM model + WHERE modelID = p_modelID; + + END IF; + + SELECT @US3_LAST_ERRNO AS status; + +END$$ Added: trunk/lims_upds/us3_people_procs.sql =================================================================== --- trunk/lims_upds/us3_people_procs.sql (rev 0) +++ trunk/lims_upds/us3_people_procs.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,400 @@ +-- +-- us3_people_procs.sql +-- +-- Script to set up the MySQL stored procedures for the US3 system +-- These are procedures related to information about people +-- Run as us3admin +-- + +DELIMITER $$ + +-- Finds the user with the passed email and password, +-- returns ID, firstName, lastName, phone, and userLevel +DROP PROCEDURE IF EXISTS get_user_info$$ +CREATE PROCEDURE get_user_info( p_personGUID CHAR(36), + p_password VARCHAR(80) ) + READS SQL DATA + +BEGIN + CALL config(); + + IF ( verify_user( p_personGUID, p_password ) = @OK ) THEN + SELECT @OK AS status; + + SELECT @US3_ID AS ID, + @FNAME AS firstName, + @LNAME AS lastName, + @PHONE AS phone, + @EMAIL AS email, + @USERLEVEL AS userLevel; + + ELSE + SELECT @US3_LAST_ERRNO AS status; + + + END IF; + +END$$ + +-- Returns of count of all records in the database, optionally +-- matching some text in the last name field +DROP FUNCTION IF EXISTS count_people$$ +CREATE FUNCTION count_people( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_template VARCHAR(30) ) + RETURNS INT + READS SQL DATA + +BEGIN + DECLARE template VARCHAR(40); + DECLARE count_names INT; + + CALL config(); + SET p_template = TRIM( p_template ); + + IF ( LENGTH(p_template) = 0 ) THEN + SELECT COUNT(*) + INTO count_names + FROM people; + + ELSE + SET template = CONCAT('%', p_template, '%'); + + SELECT COUNT(*) + INTO count_names + FROM people + WHERE lname LIKE template + OR fname LIKE template; + + END IF; + + RETURN( count_names ); +END$$ + +-- Lists all ID's and names in the database, optionally matching +-- some text in the last name field +DROP PROCEDURE IF EXISTS get_people$$ +CREATE PROCEDURE get_people( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_template VARCHAR(30) ) + READS SQL DATA + +BEGIN + DECLARE template VARCHAR(40); + + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + SET p_template = TRIM( p_template ); + + IF ( verify_user( p_personGUID, p_password ) = @OK ) THEN + IF ( count_people( p_personGUID, p_password, p_template ) < 1 ) THEN + SET @US3_LAST_ERRNO = @NOROWS; + SET @US3_LAST_ERROR = 'MySQL: no rows returned'; + + SELECT @US3_LAST_ERRNO AS status; + + ELSEIF ( LENGTH(p_template) = 0 ) THEN + SELECT @OK AS status; + + SELECT personID, + lname AS lastName, + fname AS firstName, + organization, + userlevel + FROM people + ORDER BY lname; + + ELSE + SELECT @OK AS status; + + SET template = CONCAT('%', p_template, '%'); + + SELECT personID, + lname AS lastName, + fname AS firstName, + organization, + userlevel + FROM people + WHERE lname LIKE template + OR fname LIKE template + ORDER BY lname, fname; + + END IF; + + ELSE + SELECT @US3_LAST_ERRNO AS status; + + END IF; + +END$$ + +-- Returns a more complete list of information about one user +DROP PROCEDURE IF EXISTS get_person_info$$ +CREATE PROCEDURE get_person_info( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_ID INT ) + READS SQL DATA + +BEGIN + DECLARE count_person INT; + + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + + SELECT COUNT(*) + INTO count_person + FROM people + WHERE personID = p_ID; + + IF ( verify_user( p_personGUID, p_password ) = @OK ) THEN + IF ( count_person = 0 ) THEN + SET @US3_LAST_ERRNO = @NOROWS; + SET @US3_LAST_ERROR = 'MySQL: no rows returned'; + + SELECT @US3_LAST_ERRNO AS status; + + ELSE + SELECT @OK AS status; + + SELECT fname AS firstName, + lname AS lastName, + address, + city, + state, + zip, + phone, + organization, + email, + personGUID + FROM people + WHERE personID = p_ID; + + END IF; + + ELSE + SELECT @US3_LAST_ERRNO AS status; + + END IF; + +END$$ + +-- INSERTs a new person with the specified information +DROP PROCEDURE IF EXISTS new_person$$ +CREATE PROCEDURE new_person ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_fname VARCHAR(30), + p_lname VARCHAR(30), + p_address VARCHAR(255), + p_city VARCHAR(30), + p_state CHAR(2), + p_zip VARCHAR(10), + p_phone VARCHAR(24), + p_new_email VARCHAR(63), + p_new_guid CHAR(36), + p_organization VARCHAR(45), + p_new_password VARCHAR(80) ) + MODIFIES SQL DATA + +BEGIN + DECLARE duplicate_key TINYINT DEFAULT 0; + DECLARE null_field TINYINT DEFAULT 0; + + DECLARE CONTINUE HANDLER FOR 1062 + SET duplicate_key = 1; + + DECLARE CONTINUE HANDLER FOR 1048 + SET null_field = 1; + + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + SET @LAST_INSERT_ID = 0; + + IF ( ( verify_userlevel( p_personGUID, p_password, @US3_ADMIN ) = @OK ) && + ( check_GUID ( p_personGUID, p_password, p_new_guid ) = @OK ) ) THEN + + INSERT INTO people SET + fname = p_fname, + lname = p_lname, + address = p_address, + city = p_city, + state = p_state, + zip = p_zip, + phone = p_phone, + email = p_new_email, + personGUID = p_new_guid, + organization = p_organization, + password = MD5(p_new_password), + activated = true, + signup = NOW(), + lastLogin = NOW(), + clusterAuthorizations = 'bcf:alamo:laredo:lonestar:bigred:steele:queenbee', + userlevel = 0; + + IF ( duplicate_key = 1 ) THEN + SET @US3_LAST_ERRNO = @INSERTDUP; + SET @US3_LAST_ERROR = "MySQL: Duplicate entry for email or personGUID field"; + + ELSEIF ( null_field = 1 ) THEN + SET @US3_LAST_ERRNO = @INSERTNULL; + SET @US3_LAST_ERROR = "MySQL: NULL value for a field that cannot be NULL"; + + ELSE + SET @LAST_INSERT_ID = LAST_INSERT_ID(); + + END IF; + + END IF; + + SELECT @US3_LAST_ERRNO AS status; + +END$$ + +-- UPDATEs a person with the specified information +DROP PROCEDURE IF EXISTS update_person$$ +CREATE PROCEDURE update_person ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_ID INT, + p_fname VARCHAR(30), + p_lname VARCHAR(30), + p_address VARCHAR(255), + p_city VARCHAR(30), + p_state CHAR(2), + p_zip VARCHAR(10), + p_phone VARCHAR(24), + p_new_email VARCHAR(63), + p_organization VARCHAR(45), + p_new_password VARCHAR(80) ) + MODIFIES SQL DATA + +BEGIN + DECLARE duplicate_key TINYINT DEFAULT 0; + DECLARE null_field TINYINT DEFAULT 0; + DECLARE not_found TINYINT DEFAULT 0; + + DECLARE CONTINUE HANDLER FOR 1062 + SET duplicate_key = 1; + + DECLARE CONTINUE HANDLER FOR 1048 + SET null_field = 1; + + DECLARE CONTINUE HANDLER FOR NOT FOUND + SET not_found = 1; + + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + SET @LAST_INSERT_ID = 0; + + -- Legitimate users include people updating their own info, or admins + IF ( ( ( verify_user( p_personGUID, p_password ) = @OK ) && + ( p_ID = @US3_ID ) ) || + ( verify_userlevel( p_personGUID, p_password, @US3_ADMIN ) = @OK ) ) THEN + + UPDATE people SET + fname = p_fname, + lname = p_lname, + address = p_address, + city = p_city, + state = p_state, + zip = p_zip, + phone = p_phone, + email = p_new_email, + organization = p_organization, + password = MD5(p_new_password), + activated = true, + lastLogin = NOW() + WHERE personID = p_ID; + + IF ( duplicate_key = 1 ) THEN + SET @US3_LAST_ERRNO = @INSERTDUP; + SET @US3_LAST_ERROR = "MySQL: Duplicate entry for email field"; + + ELSEIF ( null_field = 1 ) THEN + SET @US3_LAST_ERRNO = @INSERTNULL; + SET @US3_LAST_ERROR = "MySQL: NULL value for email field is not allowed"; + + ELSEIF ( not_found = 1 ) THEN + SET @US3_LAST_ERRNO = @NO_PERSON; + SET @US3_LAST_ERROR = "MySQL: No person with that ID exists"; + + ELSE + SET @LAST_INSERT_ID = LAST_INSERT_ID(); + + END IF; + + END IF; + + SELECT @US3_LAST_ERRNO AS status; + +END$$ + +-- DELETEs a person (marks inactive) +DROP PROCEDURE IF EXISTS delete_person$$ +CREATE PROCEDURE delete_person ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_ID INT ) + MODIFIES SQL DATA + +BEGIN + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + + IF ( verify_userlevel( p_personGUID, p_password, @US3_ADMIN ) = @OK ) THEN + + UPDATE people SET + activated = false, + lastLogin = NOW() + WHERE personID = p_ID; + + SET @LAST_INSERT_ID = LAST_INSERT_ID(); + + END IF; + + SELECT @US3_LAST_ERRNO AS status; + +END$$ + +-- Translate a personGUID into a personID +DROP PROCEDURE IF EXISTS get_personID_from_GUID$$ +CREATE PROCEDURE get_personID_from_GUID ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_lookupGUID CHAR(36) ) + READS SQL DATA + +BEGIN + DECLARE count_person INT; + DECLARE l_personID INT; + + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + + SELECT COUNT(*) + INTO count_person + FROM people + WHERE personGUID = p_lookupGUID; + + IF ( count_person = 0 ) THEN + SET @US3_LAST_ERRNO = @NOROWS; + SET @US3_LAST_ERROR = 'MySQL: no rows returned'; + + SELECT @US3_LAST_ERRNO AS status; + + ELSE + SELECT personID + INTO l_personID + FROM people + WHERE personGUID = p_lookupGUID + LIMIT 1; -- should be only 1 + + SELECT @OK AS status; + + SELECT l_personID AS personID; + + END IF; + +END$$ + Added: trunk/lims_upds/us3_show_bc.sh =================================================================== --- trunk/lims_upds/us3_show_bc.sh (rev 0) +++ trunk/lims_upds/us3_show_bc.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,17 @@ +# One time script to update all active US3 databases +# by changing the Glycerol% bufferComponents entry +# for proper range and range unit + +LOGIN="-u root -p`cat ~/.sec/.pwsq`" +##CMD="update bufferComponent set density='0.99823 0.030859 0.2249 6.333e-3 2.505e-5 4.151e-5 0-32 %weight/weight' where bufferComponentID=25" +CMD="select bufferComponentID,description,density,c_range from bufferComponent where description like '%lycero%'" +##for db in `cat dblist.txt` +##for db in uslims3_cauma3 +##for db in uslims3_devel +for db in `cat dblist.txt` + +do + echo "Updating $db" + mysql $LOGIN $db -sNe "${CMD}" +done + Property changes on: trunk/lims_upds/us3_show_bc.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_timestate_procs.sql =================================================================== --- trunk/lims_upds/us3_timestate_procs.sql (rev 0) +++ trunk/lims_upds/us3_timestate_procs.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,339 @@ +-- +-- us3_timestate_procs.sql +-- +-- Script to set up the MySQL stored procedures for the US3 system +-- These are procedures related to the timestate data +-- Run as us3admin +-- + +DELIMITER $$ + +-- Routines that deal with the timestate for an experiment run + +-- INSERTs new timestate information for an experiment +DROP PROCEDURE IF EXISTS new_timestate$$ +CREATE PROCEDURE new_timestate ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_experimentID INT, + p_filename VARCHAR(255), + p_definitions LONGTEXT ) + MODIFIES SQL DATA + +BEGIN + + DECLARE null_field TINYINT DEFAULT 0; + + DECLARE CONTINUE HANDLER FOR 1048 + SET null_field = 1; + + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + SET @LAST_INSERT_ID = 0; + + IF ( verify_user( p_personGUID, p_password ) = @OK ) THEN + + -- A user is creating a timestate + -- filename is the basename of the .tmst binary file + -- definitions is the accompanying XML as text + INSERT INTO timestate SET + experimentID = p_experimentID, + filename = p_filename, + definitions = p_definitions, + dateUpdated = NOW(); + + IF ( null_field = 1 ) THEN + SET @US3_LAST_ERRNO = @INSERTNULL; + SET @US3_LAST_ERROR = "MySQL: Attempt to insert NULL value in the timestate table"; + + ELSE + SET @LAST_INSERT_ID = LAST_INSERT_ID(); + + -- Don't update @LAST_INSERT_ID, because the user is not interested in this one + + END IF; + + END IF; + + SELECT @US3_LAST_ERRNO AS status; + +END$$ + +-- UPDATEs the existing timestate information for an experiment +DROP PROCEDURE IF EXISTS update_timestate$$ +CREATE PROCEDURE update_timestate ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_timestateID INT, + p_experimentID INT, + p_filename VARCHAR(255), + p_definitions LONGTEXT ) + MODIFIES SQL DATA + +BEGIN + + DECLARE null_field TINYINT DEFAULT 0; + + DECLARE CONTINUE HANDLER FOR 1048 + SET null_field = 1; + + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + + IF ( verify_experiment_permission( p_personGUID, p_password, p_experimentID ) = @OK ) THEN + + -- A user is updating a timestate + UPDATE timestate SET + experimentID = p_experimentID, + filename = p_filename, + definitions = p_definitions, + dateUpdated = NOW() + WHERE timestateID = p_timestateID; + + IF ( null_field = 1 ) THEN + SET @US3_LAST_ERRNO = @INSERTNULL; + SET @US3_LAST_ERROR = "MySQL: Attempt to insert NULL value in the timestate table"; + + END IF; + + END IF; + + SELECT @US3_LAST_ERRNO AS status; + +END$$ + +-- SELECTs the timestate information for a specified timestate ID +DROP PROCEDURE IF EXISTS get_timestate$$ +CREATE PROCEDURE get_timestate ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_timestateID INT ) + READS SQL DATA + +BEGIN + + DECLARE count_timestate INT; + DECLARE l_experimentID INT; + + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + + -- Get information we need to verify ownership + SELECT experimentID + INTO l_experimentID + FROM timestate + WHERE timestateID = p_timestateID; + + SELECT COUNT(*) + INTO count_timestate + FROM timestate + WHERE timestateID = p_timestateID; + + IF ( count_timestate = 0 ) THEN + SET @US3_LAST_ERRNO = @NOROWS; + SET @US3_LAST_ERROR = 'MySQL: no rows returned'; + + SELECT @US3_LAST_ERRNO AS status; + + ELSEIF ( verify_experiment_permission( p_personGUID, p_password, l_experimentID ) = @OK ) THEN + -- This is either an admin, or a person inquiring about his own experiment + SELECT @OK as status; + + SELECT experimentID, filename, definitions, + MD5( data ) AS checksum, LENGTH( data ) AS size, + timestamp2UTC( lastUpdated ) AS UTC_lastUpdated + FROM timestate + WHERE timestateID = p_timestateID; + + ELSE + SELECT @US3_LAST_ERRNO AS status; + + END IF; + +END$$ + +-- SELECTs the timestate information for a specified experiment ID +DROP PROCEDURE IF EXISTS get_experiment_timestate$$ +CREATE PROCEDURE get_experiment_timestate ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_experimentID INT ) + READS SQL DATA + +BEGIN + + DECLARE count_timestate INT; + + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + + SELECT COUNT(*) + INTO count_timestate + FROM timestate + WHERE experimentID = p_experimentID; + + IF ( count_timestate = 0 ) THEN + SET @US3_LAST_ERRNO = @NOROWS; + SET @US3_LAST_ERROR = 'MySQL: no rows returned'; + + SELECT @US3_LAST_ERRNO AS status; + + ELSEIF ( verify_experiment_permission( p_personGUID, p_password, p_experimentID ) = @OK ) THEN + -- This is either an admin, or a person inquiring about his own experiment + SELECT @OK as status; + + SELECT timestateID, filename, definitions, + MD5( data ) AS checksum, LENGTH( data ) AS size, + timestamp2UTC( lastUpdated ) AS UTC_lastUpdated + FROM timestate + WHERE experimentID = p_experimentID; + + ELSE + SELECT @US3_LAST_ERRNO AS status; + + END IF; + +END$$ + +-- DELETEs an individual timestate +DROP PROCEDURE IF EXISTS delete_timestate$$ +CREATE PROCEDURE delete_timestate ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_timestateID INT ) + MODIFIES SQL DATA + +BEGIN + DECLARE count_timestates INT; + DECLARE l_experimentID INT; + + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + + -- Get information we need to verify ownership + SELECT experimentID + INTO l_experimentID + FROM timestate + WHERE timestateID = p_timestateID; + + IF ( verify_experiment_permission( p_personGUID, p_password, l_experimentID ) = @OK ) THEN + + DELETE FROM timestate + WHERE timestateID = p_timestateID; + + END IF; + + SELECT @US3_LAST_ERRNO AS status; + +END$$ + +-- UPDATEs a timestate record with the binary data +DROP PROCEDURE IF EXISTS upload_timestate$$ +CREATE PROCEDURE upload_timestate ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_timestateID INT, + p_data LONGBLOB, + p_checksum CHAR(33) ) + MODIFIES SQL DATA + +BEGIN + DECLARE l_checksum CHAR(33); + DECLARE l_experimentID INT; + DECLARE not_found TINYINT DEFAULT 0; + + DECLARE CONTINUE HANDLER FOR NOT FOUND + SET not_found = 1; + + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + + -- Compare checksum with calculated checksum + SET l_checksum = MD5( p_data ); + SET @DEBUG = CONCAT( l_checksum , ' ', p_checksum ); + + -- Get information we need to verify ownership + SELECT experimentID + INTO l_experimentID + FROM timestate + WHERE timestateID = p_timestateID; + + IF ( l_checksum != p_checksum ) THEN + + -- Checksums don't match; abort + SET @US3_LAST_ERRNO = @BAD_CHECKSUM; + SET @US3_LAST_ERROR = "MySQL: Transmission error, bad checksum"; + + ELSEIF ( verify_experiment_permission( p_personGUID, p_password, l_experimentID ) = @OK ) THEN + + -- This is either an admin, or a person inquiring about his own experiment + UPDATE timestate SET + data = p_data + WHERE timestateID = p_timestateID; + + IF ( not_found = 1 ) THEN + SET @US3_LAST_ERRNO = @NO_RAWDATA; + SET @US3_LAST_ERROR = "MySQL: No raw data with that ID exists"; + + END IF; + + END IF; + + SELECT @US3_LAST_ERRNO AS status; + +END$$ + +-- SELECTs a timestate record of binary data previously saved with upload_timestate +DROP PROCEDURE IF EXISTS download_timestate$$ +CREATE PROCEDURE download_timestate ( p_personGUID CHAR(36), + p_password VARCHAR(80), + p_timestateID INT ) + READS SQL DATA + +BEGIN + DECLARE l_count_timestate INT; + DECLARE l_experimentID INT; + + CALL config(); + SET @US3_LAST_ERRNO = @OK; + SET @US3_LAST_ERROR = ''; + + -- Get information to verify that there are records + SELECT COUNT(*) + INTO l_count_timestate + FROM timestate + WHERE timestateID = p_timestateID; + +SET @DEBUG = CONCAT('timestateID = ', p_timestateID, + 'Count = ', l_count_timestate ); + -- Get information we need to verify ownership + SELECT experimentID + INTO l_experimentID + FROM timestate + WHERE timestateID = p_timestateID; + + IF ( l_count_timestate != 1 ) THEN + -- Probably no rows + SET @US3_LAST_ERRNO = @NOROWS; + SET @US3_LAST_ERROR = 'MySQL: no rows exist with that ID (or too many rows)'; + + SELECT @NOROWS AS status; + + ELSEIF ( verify_experiment_permission( p_personGUID, p_password, l_experimentID ) != @OK ) THEN + + -- verify_experiment_permission must have thrown an error, so pass it on + SELECT @US3_LAST_ERRNO AS status; + + ELSE + + -- This is either an admin, or a person inquiring about his own experiment + SELECT @OK AS status; + + SELECT data, MD5( data ) + FROM timestate + WHERE timestateID = p_timestateID; + + END IF; + +END$$ + Added: trunk/lims_upds/us3_upd_buff_procs.sh =================================================================== --- trunk/lims_upds/us3_upd_buff_procs.sh (rev 0) +++ trunk/lims_upds/us3_upd_buff_procs.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,17 @@ +# One time script to update all active US3 databases +# by updating the buffer stored procedures + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +cd ~us3/database/sql +##for db in uslims3_cauma3 +##for db in `cat /home/us3/database/dblist.txt` +##for db in `cat /home/us3/database/dblist_upds.txt` + +for db in `cat ~us3/database/dblist.txt` + +do + echo "Updating $db" + mysql $LOGIN $db < ./us3_buffer_procs.sql +done + Property changes on: trunk/lims_upds/us3_upd_buff_procs.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_upd_dmga.sh =================================================================== --- trunk/lims_upds/us3_upd_dmga.sh (rev 0) +++ trunk/lims_upds/us3_upd_dmga.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,22 @@ +# One time script to update all active US3 databases +# by creating the DMGA method + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +cd ~us3/database + +for db in uslims3_cauma3 + +do + echo "Updating $db" + mysql $LOGIN $db < ./alter_dmga_settings.sql +done + +##for db in `cat ~us3/database/dblist_upds.txt` +##for db in `cat ~us3/database/dblist.txt` +for db in `cat ~us3/database/dblist_upds.txt` + +do + echo "Updating $db" + mysql $LOGIN $db < ./update_dmga.sql +done Property changes on: trunk/lims_upds/us3_upd_dmga.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_upd_pcsa.sh =================================================================== --- trunk/lims_upds/us3_upd_pcsa.sh (rev 0) +++ trunk/lims_upds/us3_upd_pcsa.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,16 @@ +# One time script to update all active US3 databases +# by creating the PCSA method + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +cd ~us3/database + +##for db in `cat ~us3/database/dblist_upds.txt` +##for db in `cat ~us3/database/dblist_upds.txt` +##for db in uslims3_cauma3 +for db in `cat ~us3/database/dblist.txt` + +do + echo "Updating $db" + mysql $LOGIN $db < ./update_pcsa.sql +done Property changes on: trunk/lims_upds/us3_upd_pcsa.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_upd_proj_procs.sh =================================================================== --- trunk/lims_upds/us3_upd_proj_procs.sh (rev 0) +++ trunk/lims_upds/us3_upd_proj_procs.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,17 @@ +# One time script to update all active US3 databases +# by updating the timestate stored procedures + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +cd ~us3/lims/database/sql +##for db in uslims3_cauma3 +##for db in `cat /home/us3/database/dblist.txt` +##for db in `cat /home/us3/database/dblist_upds.txt` + +for db in `cat ~us3/database/dblist.txt` + +do + echo "Updating $db" + mysql $LOGIN $db < ./us3_proj_procs.sql +done + Property changes on: trunk/lims_upds/us3_upd_proj_procs.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_upd_project.sh =================================================================== --- trunk/lims_upds/us3_upd_project.sh (rev 0) +++ trunk/lims_upds/us3_upd_project.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,12 @@ +# One time script to update all active US3 databases +# by adding lastUpdated field to project table +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" + +##for db in uslims3_cauma3; +for db in `cat dblist.txt` + +do + echo "Updating $db" + mysql $LOGIN $db < ./alter_project.sql +done Property changes on: trunk/lims_upds/us3_upd_project.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_upd_proto_procs.sh =================================================================== --- trunk/lims_upds/us3_upd_proto_procs.sh (rev 0) +++ trunk/lims_upds/us3_upd_proto_procs.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,18 @@ +# One time script to update all active US3 databases +# by updating the protocol stored procedures + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +cd ~us3/lims/database/sql +##for db in uslims3_cauma3 +##for db in uslims3_cauma3 uslims3_CAUMA +##for db in `cat /home/us3/database/dblist.txt` +##for db in `cat /home/us3/database/dblist_upds.txt` + +for db in uslims3_cauma3 uslims3_CAUMA + +do + echo "Updating $db" + mysql $LOGIN $db < ./us3_protocol_procs.sql +done + Property changes on: trunk/lims_upds/us3_upd_proto_procs.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_upd_solu_procs.sh =================================================================== --- trunk/lims_upds/us3_upd_solu_procs.sh (rev 0) +++ trunk/lims_upds/us3_upd_solu_procs.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,18 @@ +# One time script to update all active US3 databases +# by updating the protocol stored procedures + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +cd ~us3/lims/database/sql +##for db in uslims3_cauma3 +##for db in uslims3_cauma3 uslims3_CAUMA +##for db in `cat ~us3/lims/database/dblist.txt` +##for db in `cat ~us3/lims/database/dblist_upds.txt` + +for db in `cat ~us3/lims/database/dblist.txt` + +do + echo "Updating $db" + mysql $LOGIN $db < ./us3_solution_procs.sql +done + Property changes on: trunk/lims_upds/us3_upd_solu_procs.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_update_admin_passwd.sh =================================================================== --- trunk/lims_upds/us3_update_admin_passwd.sh (rev 0) +++ trunk/lims_upds/us3_update_admin_passwd.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,13 @@ +# One time script to update admin password for all active US3 databases + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +cd ~us3/lims/database + +##for db in uslims3_cauma3 +for db in `cat dblist.txt` + +do + echo "Updating $db" + mysql $LOGIN $db < ./us3_update_admin_passwd.sql +done Property changes on: trunk/lims_upds/us3_update_admin_passwd.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_update_admin_passwd.sql =================================================================== --- trunk/lims_upds/us3_update_admin_passwd.sql (rev 0) +++ trunk/lims_upds/us3_update_admin_passwd.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,19 @@ +-- +-- us3_update_admin_passwd.sql +-- +-- Updates the password for all accounts with userlevel >= 4 +-- +-- mysql> select MD5( 'TSv3dberg' ); +-- +----------------------------------+ +-- | MD5( 'TSv3dberg' ) | +-- +----------------------------------+ +-- | 76a6c9a17a598c00343cfc9a04ec5ea2 | +-- +----------------------------------+ +-- 1 row in set (0.00 sec) +-- +-- mysql> +-- + +UPDATE people +SET password = '76a6c9a17a598c00343cfc9a04ec5ea2' +WHERE userlevel >= 4; Added: trunk/lims_upds/us3_update_anabuf.sh =================================================================== --- trunk/lims_upds/us3_update_anabuf.sh (rev 0) +++ trunk/lims_upds/us3_update_anabuf.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,19 @@ +# One time script to update all active US3 databases +# by updating the exp stored procedures + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +cd ~us3/lims/database +sqldir=sql +##sqldir=~gegorbet/work +##for db in uslims3_cauma3 +##for db in `cat dblist.txt` +##for db in `cat dblist_upds.txt` +for db in uslims3_Uni_DuE + +do + echo "Updating $db" + mysql $LOGIN $db < $sqldir/us3_analyte_procs.sql + mysql $LOGIN $db < $sqldir/us3_buffer_procs.sql +done + Property changes on: trunk/lims_upds/us3_update_anabuf.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_update_clusters.sh =================================================================== --- trunk/lims_upds/us3_update_clusters.sh (rev 0) +++ trunk/lims_upds/us3_update_clusters.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,15 @@ +# One time script to update all active US3 databases +# by altering/updating clusterAuthorizations + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +##for db in uslims3_cauma3 +##for db in `cat dblist.txt` +##for db in `cat dblist_clustauth.txt` +##for db in `cat dblist.txt` + +for db in `cat dblist_clustauth.txt` +do + echo "Updating $db" + mysql $LOGIN $db < ./alter_cluster_auth.sql +done Property changes on: trunk/lims_upds/us3_update_clusters.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_update_exp.sh =================================================================== --- trunk/lims_upds/us3_update_exp.sh (rev 0) +++ trunk/lims_upds/us3_update_exp.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,15 @@ +# One time script to update all active US3 databases +# by updating the exp stored procedures + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +##for db in uslims3_cauma3 +##for db in `cat dblist.txt` +##for db in `cat dblist_upds.txt` +for db in `cat dblist.txt` + +do + echo "Updating $db" + mysql $LOGIN $db < sql/us3_exp_procs.sql +done + Property changes on: trunk/lims_upds/us3_update_exp.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_update_expdata.sh =================================================================== --- trunk/lims_upds/us3_update_expdata.sh (rev 0) +++ trunk/lims_upds/us3_update_expdata.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,12 @@ +# One time script to update all active US3 databases +# by updating the expdata stored procedures + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +##for db in uslims3_cauma3 +for db in `cat dblist.txt` + +do + echo "Updating $db" + mysql $LOGIN $db < sql/us3_expdata_procs.sql +done Property changes on: trunk/lims_upds/us3_update_expdata.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_update_expdata_t.sh =================================================================== --- trunk/lims_upds/us3_update_expdata_t.sh (rev 0) +++ trunk/lims_upds/us3_update_expdata_t.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,12 @@ +# One time script to update all active US3 databases +# by updating the expdata stored procedures + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +##for db in `cat dblist.txt` +for db in uslims3_CAUMA + +do + echo "Updating $db" + mysql $LOGIN $db < sql/us3_expdata_procs_test.sql +done Property changes on: trunk/lims_upds/us3_update_expdata_t.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_update_groupcount.sh =================================================================== --- trunk/lims_upds/us3_update_groupcount.sh (rev 0) +++ trunk/lims_upds/us3_update_groupcount.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,13 @@ +# One time script to add mgroupcount field to all active US3 databases + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +cd ~us3/lims/database + +##for db in uslims3_cauma3 +for db in `cat dblist.txt` + +do + echo "Updating $db" + mysql $LOGIN $db < ./us3_update_groupcount.sql +done Property changes on: trunk/lims_upds/us3_update_groupcount.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_update_groupcount.sql =================================================================== --- trunk/lims_upds/us3_update_groupcount.sql (rev 0) +++ trunk/lims_upds/us3_update_groupcount.sql 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,7 @@ +-- +-- Update the HPCAnalysisResult table with the mgroupcount field +-- + +ALTER TABLE HPCAnalysisResult + ADD COLUMN mgroupcount int(11) NOT NULL DEFAULT 1 + AFTER CPUCount; Added: trunk/lims_upds/us3_update_model.sh =================================================================== --- trunk/lims_upds/us3_update_model.sh (rev 0) +++ trunk/lims_upds/us3_update_model.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,15 @@ +# One time script to update all active US3 databases +# by updating the exp stored procedures + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +##for db in uslims3_cauma3 +##for db in `cat dblist.txt` +##for db in `cat dblist_upds.txt` +for db in `cat dblist.txt` + +do + echo "Updating $db" + mysql $LOGIN $db < sql/us3_model_procs.sql +done + Property changes on: trunk/lims_upds/us3_update_model.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Added: trunk/lims_upds/us3_update_timestate.sh =================================================================== --- trunk/lims_upds/us3_update_timestate.sh (rev 0) +++ trunk/lims_upds/us3_update_timestate.sh 2019-11-06 22:52:39 UTC (rev 2841) @@ -0,0 +1,17 @@ +# One time script to update all active US3 databases +# by updating the timestate stored procedures + +. ~us3/lims/database/getdbpw +LOGIN="-u root -p$PASSW" +cd ~us3/lims/database/sql +##for db in uslims3_cauma3 +##for db in `cat /home/us3/database/dblist.txt` +##for db in `cat /home/us3/database/dblist_upds.txt` + +for db in `cat ~us3/database/dblist_upds.txt` + +do + echo "Updating $db" + mysql $LOGIN $db < ./us3_timestate_procs.sql +done + Property changes on: trunk/lims_upds/us3_update_timestate.sh ___________________________________________________________________ Added: svn:executable ## -0,0 +1 ## +* \ No newline at end of property Modified: trunk/programs/us_astfem_sim/us_astfem_sim.cpp =================================================================== --- trunk/programs/us_astfem_sim/us_astfem_sim.cpp 2019-11-06 20:57:34 UTC (rev 2840) +++ trunk/programs/us_astfem_sim/us_astfem_sim.cpp 2019-11-06 22:52:39 UTC (rev 2841) @@ -415,8 +415,10 @@ sim_data_all.channel = 'S'; sim_data_all.description = "Simulation"; - int points = qRound( ( simparams.bottom - simparams.meniscus ) / - simparams.radial_resolution ) + 1; +// int points = qRound( ( simparams.bottom - simparams.meniscus ) / +// simparams.radial_resolution ) + 1; + int points = qCeil( ( simparams.bottom - simparams.meniscus ) / + simparams.radial_resolution ) + 1; sim_data_all.xvalues.resize( points ); // Sets the size of radial grid points array @@ -428,9 +430,9 @@ DbgLv(1) << "SimPar:MAIN:SetP: points" << points << "rad0 radn" << sim_data_all.xvalues[0] << sim_data_all.xvalues[points-1]; - sim_data_all.xvalues[ points - 1 ] = simparams.bottom; -DbgLv(1) << "SimPar:MAIN:SetP: points" << points << "rad0 radn" - << sim_data_all.xvalues[0] << sim_data_all.xvalues[points-1]; +// sim_data_all.xvalues[ points - 1 ] = simparams.bottom; +//DbgLv(1) << "SimPar:MAIN:SetP: points" << points << "rad0 radn" +// << sim_data_all.xvalues[0] << sim_data_all.xvalues[points-1]; // Fill in speed steps with scan times and omega^2t; build raw data double time0 = 0.0; @@ -633,8 +635,10 @@ << "step=" << jd << simparams.speed_step[jd].rotorspeed; // Number of radial grid points on the experimental grid; number of scans - int points = qRound( ( af_params.current_bottom - af_params.current_meniscus ) / - simparams.radial_resolution ) + 1; +// int points = qRound( ( af_params.current_bottom - af_params.current_meniscus ) / +// simparams.radial_resolution ) + 1; + int points = qCeil( ( af_params.current_bottom - af_params.current_meniscus ) / + simparams.radial_resolution ) + 1; int nscans = simparams.speed_step[ jd ].scans; sim_datas[ jd ].xvalues.resize( points ); // Sets the size of radial grid points array @@ -647,7 +651,7 @@ } DbgLv(1) << "astfem_radial_ranges" << sim_datas[jd].xvalues[0] << sim_datas[jd].xvalues[points-1] << af_params.current_meniscus << af_params.current_bottom << jd; - sim_datas[ jd ].xvalues[ points - 1 ] = af_params.current_bottom; +// sim_datas[ jd ].xvalues[ points - 1 ] = af_params.current_bottom; // Set the total size of scans for simulation sim_datas[ jd ].scanData.resize( nscans ); @@ -858,8 +862,10 @@ sim_data_all.scanData[ js ].rvalues.fill( 0.0, kpoint ); // Set the radius values in data sets - int points = qRound( ( simparams.bottom - simparams.meniscus ) / - simparams.radial_resolution ) + 1; +// int points = qRound( ( simparams.bottom - simparams.meniscus ) / +// simparams.radial_resolution ) + 1; + int points = qCeil( ( simparams.bottom - simparams.meniscus ) / + simparams.radial_resolution ) + 1; for ( int jd = 0; jd < nstep; jd++ ) { // Set radius values for current speed's dataset double stretch_fac = stretch( simparams.rotorcoeffs,
participants (1)
-
svn@svn.aucsolutions.com