<?php
// This file is part of the Huygens Remote Manager
// Copyright and license notice: see license.txt
// =============================================================================
// Description
// =============================================================================
// This script has the objective of updating the database linked to HRM.
// Moreover the number of the last revision for the database is contained in the
// script (this is the only place where this information can be found).
//
// When you want to change something in the database, that is, to create a new
// database release, it is necessary to insert the modifications in the last part
// of the script and to update the constant DB_LAST_REVISION in versions.inc.
//
// When running the script, three situations are possible:
// 1) a new user of HRM run the script from command line, the database does not
// exist yet. In this case the database $db_name is created. Then all the tables
// are created and the tables with fixed content (ex: boundary_values) are filled.
// The admin user is created in the table username.
// The database is updated to the last revision.
// 2) a user has his database version, but he never run this script: the table
// global_variables does not exist and the revision number of the database is
// unknown. In this case the table global_variable is created and the field
// dbrevision is set to 0; the structure of all the tables is checked and
// eventually corrected; the content of the fixed tables is checked and
// eventually corrected; the number of # in the field value, in the tables
// parameter and task_parameter, is checked and eventually corrected (it should
// be 5). The content of the tables parameter, parameter_setting, task_parameter
// and task_setting is preserved.
// The database is updated to the last revision.
// 3) the user has a database identified by a revision number (field dbrevision
// in the table global_variables). In this case the database is not checked and
// it is simply updated to the last revision.
// Check whether the script is run from the HRM or from bash (from $hrm/setup)
if ( isset( $_GET['action'] ) && $_GET['action'] == 'dbupdate' ) {
include "inc/hrm_config.inc"; // Run from HRM
include "inc/versions.inc";
} else {
include "../inc/hrm_config.inc"; // Run from console
include "../inc/versions.inc";
}
include $adodb;
// Database last revision
$LAST_REVISION = Versions::getDBLastRevision( );
// For test purposes
//$db_name = "hrm-test";
// =============================================================================
// Utility functions
// =============================================================================
// Returns a timestamp
function timestamp() {
return date('l jS \of F Y h:i:s A');
}
// Return a timestamp for a T field in a database
function timestampADODB() {
return date('Y-m-d H:i:s');
}
// Write a message into the log file
function write_to_log($msg) {
global $fh;
fwrite($fh, $msg . "\n");
}
// Write a message into the error log file
function write_to_error($msg) {
global $efh;
fwrite($efh, $msg . "\n");
}
// Write a message to the standard output
function write_message($msg) {
global $interface;
global $message;
if (isset($interface)) {
$message .= $msg . "\n";
}
else echo $msg . "\n";
}
// Return an error message
function error_message($table) {
return "An error occurred while updating table " . $table . ".";
}
// =============================================================================
// Query functions
// =============================================================================
// Create a table with the specified name and fields
function create_table($name, $fields) {
global $datadict;
$sqlarray = $datadict->CreateTableSQL($name, $fields);
$rs = $datadict->ExecuteSQLArray($sqlarray); // return 0 if failed, 1 if executed all but with errors, 2 if executed successfully
if($rs != 2) {
$msg = error_message($name);
write_message($msg);
write_to_error($msg);
return False;
}
$msg = $name . ": has been created\n";
write_to_log($msg);
return True;
}
// Drop the table with the specified name
function drop_table($tabname) {
global $datadict, $db;
$sqlarray = $datadict->DropTableSQL($tabname);
$rs = $datadict->ExecuteSQLArray($sqlarray);
if($rs != 2) {
$msg = error_message($tabname);
write_message($msg);
write_to_error($msg);
return False;
}
$msg = $tabname . ": has been dropped\n";
write_to_log($msg);
return True;
}
// Insert a set of records ($records is a multidimensional associative array) into the table $tabname
function insert_records($records,$tabname) {
global $db;
$keys = array_keys($records);
for($i=0; $i<count($records[$keys[0]]); $i++) {
$record = array();
foreach($keys as $key)
$record[$key] = $records[$key][$i];
$insertSQL = $db->GetInsertSQL($tabname, $record);
if(!$db->Execute($insertSQL)) {
$msg = error_message($tabname);
write_message($msg);
write_to_error($msg);
return False;
}
}
$msg = $tabname . ": records have been inserted.\n";
write_to_log($msg);
return True;
}
// Insert a single record in the table $tabname.
// $array id the record to be insert, $colnames contains the names of the columns of the table.
// $array anb $colnames are simple 1D arrays.
function insert_record($tabname, $array, $colnames) {
global $db;
for($i=0; $i<count($colnames); $i++)
$record[$colnames[$i]] = $array[$i];
$insertSQL = $db->GetInsertSQL($tabname, $record);
if(!$db->Execute($insertSQL)) {
$msg = error_message($tabname);
write_message($msg);
write_to_error($msg);
return False;
}
return True;
}
// Insert a column into the table $tabname
function insert_column($tabname,$flds) {
global $datadict;
$sqlarray = $datadict->AddColumnSQL($tabname, $fields); // NOTE: ADOdb AddColumnSQL, not guaranteed to work under all situations.
$rs = $datadict->ExecuteSQLArray($sqlarray); // return 0 if failed, 1 if executed all but with errors, 2 if executed successfully
if($rs != 2) {
$msg = error_message($tabname);
write_message($msg);
write_to_log($msg);
write_to_error($msg);
return False;
}
return True;
}
// Check the existence and the structure of a table.
// If the table does not exist, it is created;
// if a field is not correct, it is altered;
// if a field does not exist, it is added and the default value for that field is put in the record.
function check_table_existence_and_structure($tabname,$flds) {
global $datadict;
$sqlarray = $datadict->ChangeTableSQL($tabname, $flds);
$rs = $datadict->ExecuteSQLArray($sqlarray); // return 0 if failed, 1 if executed all but with errors, 2 if executed successfully
if($rs != 2) {
$msg = error_message($tabname);
write_message($msg);
write_to_error($msg);
return False;
}
$msg = $tabname . ": existence and the structure have been checked.\n";
write_to_log($msg);
return True;
}
// Update field dbrevision in table global_variables to $n)
function update_dbrevision($n) {
global $db, $current_revision;
$tabname = "global_variables";
$record = array();
$record["value"] = $n;
if (!$db->AutoExecute($tabname, $record, 'UPDATE', "name like 'dbrevision'")) {
$msg = error_message($tabname);
write_message($msg);
write_to_error($msg);
return false;
}
$msg = $tabname . ": dbrevision has been updated to " . $n . ".\n";
return True;
}
// Verify the number of # in the field value, when name = $value.
// This function has been thought to check the content of tables parameter and task_parameter.
function check_number_gates($tabname, $value, $fields_set, $primary_key) {
global $db;
$rs = $db->Execute("SELECT * FROM " . $tabname . " WHERE name = '" . $value . "'");
if($rs) {
while ($row = $rs->FetchRow()) {
$test = substr_count($row[3], '#');
if($test < 5) {
$msg = $tabname . ": value '" . $row[3];
if(strlen($row[3]) != $test) { // in this case there are characters in the field different from #
if(strpos($row[3],'#') != 0) { // the # is not the first character in the field (it should be)
$row[3] = str_pad($row[3],strlen($row[3])+1,'#',STR_PAD_LEFT);
$row[3] = str_pad($row[3],strlen($row[3])+5-$test-1,'#',STR_PAD_RIGHT);
}
else {
$row[3] = str_pad($row[3],strlen($row[3])+5-$test,'#',STR_PAD_RIGHT);
}
}
else { // in the field there are only #, but less then 5
$row[3] = str_pad($row[3],5,'#',STR_PAD_RIGHT);
}
for($i = 0; $i < count($fields_set); $i++) {
$temp[$fields_set[$i]] = $row[$i];
}
if(!$ret = $db->Replace($tabname,$temp,$primary_key,$autoquote=true)) {
$msg = error_message($tabname);
write_message($msg);
write_to_error($msg);
return False;
}
$msg .= "' has be changed in '" . $row[3] . "'\n";
write_to_log($msg);
}
}
}
return True;
}
// Manage ENUM problem derived from ADODataDictionary
// (temporary function; next step: change hrm code concerning ENUM variables /
// waiting for ADODataDictionary correction)
function manage_enum($tabname, $field, $values_string, $default) {
global $db;
if (strcmp($default, 'NULL') != 0)
$SQLquery = "ALTER TABLE " . $tabname ." CHANGE " . $field . " " . $field . " ENUM(" . $values_string . ") DEFAULT '" . $default . "'";
else
$SQLquery = "ALTER TABLE " . $tabname ." CHANGE " . $field . " " . $field . " ENUM(" . $values_string . ")";
if(!$db->Execute($SQLquery)) {
$msg = "An error occurred while updating the table " . $tabname . ".";
write_message($msg);
write_to_error($msg);
return False;
}
return True;
}
// Search a value into a multidimensional array. Return true if the value has been found, false otherwise
function in_array_multi($needle,$haystack) {
$found = false;
foreach($haystack as $value) {
if((is_array($value) && in_array_multi($needle,$value)) || $value == $needle) {
$found = true;
}
}
return $found;
}
// =============================================================================
// Script
// =============================================================================
// -----------------------------------------------------------------------------
// Initialization
// -----------------------------------------------------------------------------
// Open log file
if (isset($logdir)==false) {
echo "<strong>Error: the log directory was not set in the configuration!
Please do it and try again!</strong>";
return;
}
if ( file_exists($logdir)==false) {
echo "<strong>Error: the log directory specified in the configuration does
not exist! Please create it and make sure that the web server uses
has read/write access to it!</strong>";
return;
}
// Define the log and error_log file names
$log_file = $logdir . "/dbupdate.log";
$error_file = $logdir . "/dbupdate_error.log";
// If the log files do not exist, we create them and set the correct file mode
foreach ( array( $log_file, $error_file ) as $currentFile ) {
if ( file_exists($currentFile)==false) {
if (!($fh = @fopen($currentFile, 'a'))) {
echo "<strong>Cannot create file " . $currentFile . ".</strong>";
return;
}
//Close the file
fclose($fh);
// Set the mode to 0666
chmod($currentFile, 0666);
}
}
// Now open the files for use
if (!($fh = @fopen($log_file, 'a'))) {
echo "<strong>Cannot open the dbupdate log file!</strong>";
return;
}
write_to_log(timestamp());
// Open error log file
if (!($efh = @fopen($error_file, 'a'))) { // If the file does not exist, it is created
echo "<strong>Cannot open the dbupdate error file.</strong>";
return;
}
write_to_error(timestamp());
// Check if the database exists; if it does not exist, create it
$dsn = $db_type."://".$db_user.":".$db_password."@".$db_host;
$db = ADONewConnection($dsn);
if(!$db) {
$msg = "Cannot connect to database host.";
write_message($msg);
write_to_error($msg);
return;
}
$datadict = NewDataDictionary($db); // Build a data dictionary
$databases = $db->MetaDatabases();
if (!in_array($db_name, $databases)) {
$createDb = $datadict->CreateDatabase($db_name);
$ret = $datadict->ExecuteSQLArray($createDb);
if(!$ret) {
$msg = "An error occurred in the creation of the HRM database.";
write_message($msg);
write_to_error($msg);
return;
}
$msg = "Executed database creation query.\n";
write_message($msg);
write_to_log($msg);
}
// Connect to the database
$dsn = $db_type."://".$db_user.":".$db_password."@".$db_host."/".$db_name;
$db = ADONewConnection($dsn);
if(!$db) {
$msg = "Cannot connect to the database, probably creation failed.\n".
"Please check that database user '$db_user' exists\n".
"and has privileges to administrate databases.";
write_message($msg);
write_to_error($msg);
return;
}
// Build a data dictionary to automate the creation of tables
$datadict = NewDataDictionary($db);
// Extract the list of existing tables
$tables = $db->MetaTables("TABLES");
// -----------------------------------------------------------------------------
// Read the current database revision
// -----------------------------------------------------------------------------
// Check if the table global_variables exists
if (!in_array("global_variables", $tables)) {
// If the table does not exist, create it
$flds = "
name C(30) KEY,
value C(30) NOTNULL
";
if (!create_table("global_variables", $flds)) {
return;
}
}
// Check if the variable dbrevision exists
$rs = $db->Execute("SELECT * FROM global_variables WHERE name = 'dbrevision'");
if ($rs->EOF) { // If the variable dbrevision does not exist, create it and set its value to 0
$record = array();
$record["name"] = "dbrevision";
$record["value"] = "0";
$insertSQL = $db->GetInsertSQL($rs, $record);
if(!$db->Execute($insertSQL)) {
$msg = "An error occurred while updating the table \"global_variables\".";
write_message($msg);
write_to_error($msg);
return;
}
$current_revision = 0;
$msg = "Initialized database revision to 0.\n";
write_message($msg);
write_to_log($msg);
}
else {
$o = $rs->FetchObj();
$current_revision = $o->value;
}
// -----------------------------------------------------------------------------
// If the current database revision is 0 (new user or user whose database is not
// identified by a recision number), create or check all the tables
// -----------------------------------------------------------------------------
if ($current_revision == 0) {
// Drop and create fixed tables (structure and content)
// -------------------------------------------------------------------------
// -------------------------------------------------------------------------
// NOTE: ENUM is not available as a portable type code, which forces us to
// hardcode the type string in the following descriptions, which in turn
// forces us to use uppercase 'T' and 'F' enum values (because of some
// stupid rule in adodb data dictionary class).
// boundary_values
// -------------------------------------------------------------------------
// Drop table if it exists
$tabname = "boundary_values";
if (in_array($tabname, $tables)) {
if (!drop_table($tabname))
return;
}
// Create table
$flds = ("
parameter C(255) DEFAULT 0 PRIMARY,
min C(30),
max C(30),
min_included C(1) DEFAULT t,
max_included C(1) DEFAULT t,
standard C(30)
");
if (!create_table($tabname, $flds))
return;
// Insert records in table
$records = array("parameter"=>array("PinholeSize","RemoveBackgroundPercent","BackgroundOffsetPercent","ExcitationWavelength",
"EmissionWavelength","CMount","TubeFactor","CCDCaptorSizeX","CCDCaptorSizeY","ZStepSize","TimeInterval",
"SignalNoiseRatio","NumberOfIterations","QualityChangeStoppingCriterion"),
"min"=>array("0","0","0","0","0","0.4","1","1","1","50","0.001","0","1","0"),
"max"=>array(NULL,"100","",NULL,NULL,"1","2","25000","25000","600000",NULL,"100","100",NULL),
"min_included"=>array("f","f","t","f","f","t","t","t","t","t","f","f","t","t"),
"max_included"=>array("t","t","f","t","t","t","t","t","t","t","t","t","t","t"),
"standard"=>array(NULL,NULL,NULL,NULL,NULL,"1","1",NULL,NULL,NULL,NULL,NULL,NULL,NULL));
if(!insert_records($records,$tabname))
return;
// possible_values
// -------------------------------------------------------------------------
// Drop table if it exists
$tabname = "possible_values";
if (in_array($tabname, $tables)) {
if (!drop_table($tabname))
return;
}
// Create table
$flds = "
parameter C(30) NOTNULL DEFAULT 0 PRIMARY,
value C(255) NOTNULL DEFAULT 0 PRIMARY,
translation C(50) DEFAULT NULL,
isDefault C(1) DEFAULT f
";
if (!create_table($tabname, $flds))
return;
// Insert records in table
$records = array("parameter"=>array("IsMultiChannel","IsMultiChannel",
"ImageFileFormat","ImageFileFormat","ImageFileFormat","ImageFileFormat","ImageFileFormat","ImageFileFormat","ImageFileFormat","ImageFileFormat",
"NumberOfChannels","NumberOfChannels","NumberOfChannels","NumberOfChannels",
"ImageGeometry","ImageGeometry","ImageGeometry",
"MicroscopeType","MicroscopeType","MicroscopeType","MicroscopeType",
"ObjectiveMagnification","ObjectiveMagnification","ObjectiveMagnification","ObjectiveMagnification",
"ObjectiveType","ObjectiveType","ObjectiveType",
"SampleMedium","SampleMedium",
"Binning","Binning","Binning","Binning","Binning",
"MicroscopeName","MicroscopeName","MicroscopeName","MicroscopeName","MicroscopeName","MicroscopeName","MicroscopeName","MicroscopeName",
"Resolution","Resolution","Resolution","Resolution","Resolution",
"RemoveNoiseEffectiveness","RemoveNoiseEffectiveness","RemoveNoiseEffectiveness",
"OutputFileFormat","OutputFileFormat","OutputFileFormat","OutputFileFormat","OutputFileFormat",
"ObjectiveMagnification","ObjectiveMagnification",
"PointSpreadFunction","PointSpreadFunction",
"HasAdaptedValues","HasAdaptedValues",
"ImageFileFormat","ImageFileFormat","ImageFileFormat","ImageFileFormat","ImageFileFormat",
"ObjectiveType"),
"value"=>array("True","False",
"dv","stk","tiff-series","tiff-single","ims","lsm","lsm-single","pic",
"1","2","3","4",
"XYZ","XY - time","XYZ - time",
"widefield","multipoint confocal (spinning disk)","single point confocal","two photon",
"10","20","25","40",
"oil","water","air",
"water / buffer","liquid vectashield / 90-10 (v:v) glycerol - PBS ph 7.4",
"1","2","3","4","5",
"Zeiss 510","Zeiss 410","Zeiss Two Photon 1","Zeiss Two Photon 2","Leica DMRA","Leica DMRB","Leica Two Photon 1","Leica Two Photon 2",
"128","256","512","1024","2048",
"1","2","3",
"TIFF 8-bit","TIFF 16-bit","IMS (Imaris Classic)","ICS (Image Cytometry Standard)","OME-XML",
"63","100",
"theoretical","measured",
"True","False",
"ome-xml","tiff","lif","tiff-leica","ics",
"glycerol"),
"translation"=>array("","",
"Delta Vision (*.dv)","Metamorph (*.stk)","Numbered series","single XY plane","Imaris Classic (*.ims)","Zeiss (*.lsm)","Zeiss (*.lsm) single XY plane","Biorad (*.pic)",
"","","","",
"","","",
"widefield","nipkow","confocal","widefield",
"","","","",
"1.515","1.3381","1.0",
"1.339","1.47",
"","","","","",
"","","","","","","","",
"","","","","",
"","","",
"tiff","tiff16","imaris","ics","ome",
"","",
"","",
"","",
"OME-XML (*.ome)","Olympus FluoView","Leica (*.lif)","Leica series","Image Cytometry Standard (*.ics/*.ids)",
"1.4729"),
"isDefault"=>array("f","f",
"f","f","f","f","f","f","f","f",
"f","f","f","f",
"f","f","f",
"f","f","f","f",
"f","f","f","f",
"f","f","f",
"f","f",
"f","f","f","f","f",
"f","f","f","f","f","f","f","f",
"f","f","f","f","f",
"f","f","f",
"f","f","t","f","f",
"f","f",
"f","f",
"f","f",
"f","f","f","f","f",
"f"),
"parameter_key"=>array("IsMultiChannel1","IsMultiChannel2",
"ImageFileFormat1","ImageFileFormat2","ImageFileFormat3","ImageFileFormat4","ImageFileFormat5","ImageFileFormat6","ImageFileFormat7","ImageFileFormat8",
"NumberOfChannels1","NumberOfChannels2","NumberOfChannels3","NumberOfChannels4",
"ImageGeometry1","ImageGeometry2","ImageGeometry3",
"MicroscopeType1","MicroscopeType2","MicroscopeType3","MicroscopeType4",
"ObjectiveMagnification1","ObjectiveMagnification2","ObjectiveMagnification3","ObjectiveMagnification4",
"ObjectiveType1","ObjectiveType2","ObjectiveType3",
"SampleMedium1","SampleMedium2",
"Binning1","Binning2","Binning3","Binning4","Binning5",
"MicroscopeName1","MicroscopeName2","MicroscopeName3","MicroscopeName4","MicroscopeName5","MicroscopeName6","MicroscopeName7","MicroscopeName8",
"Resolution1","Resolution2","Resolution3","Resolution4","Resolution5",
"RemoveNoiseEffectiveness1","RemoveNoiseEffectiveness2","RemoveNoiseEffectiveness3",
"OutputFileFormat1","OutputFileFormat2","OutputFileFormat3","OutputFileFormat4","OutputFileFormat5",
"ObjectiveMagnification1","ObjectiveMagnification2",
"PointSpreadFunction1","PointSpreadFunction2",
"HasAdaptedValues1","HasAdaptedValues2",
"ImageFileFormat1","ImageFileFormat2","ImageFileFormat3","ImageFileFormat4","ImageFileFormat5",
"ObjectiveType"));
if(!insert_records($records,$tabname))
return;
// geometry
// -------------------------------------------------------------------------
// Drop table if it exists
$tabname = "geometry";
if (in_array($tabname, $tables)) {
if (!drop_table($tabname))
return;
}
// Create table
$flds = "
name C(30) KEY DEFAULT 0 PRIMARY,
isThreeDimensional C(1) DEFAULT NULL,
isTimeSeries C(1) DEFAULT NULL
";
if (!create_table($tabname, $flds))
return;
// Insert records in table
$records = array("name"=>array("XYZ","XYZ - time","XY - time"),
"isThreeDimensional"=>array("t","t","f"),
"isTimeSeries"=>array("f","t","t"));
if(!insert_records($records,$tabname))
return;
// file_format
// -------------------------------------------------------------------------
// Drop table if it exists
$tabname = "file_format";
if (in_array($tabname, $tables)) {
if (!drop_table($tabname))
return;
}
// Create table
$flds = "
name C(30) NOTNULL DEFAULT 0 PRIMARY,
isFixedGeometry C(1) NOTNULL DEFAULT t PRIMARY,
isSingleChannel C(1) NOTNULL DEFAULT t PRIMARY,
isVariableChannel C(1) NOTNULL DEFAULT t PRIMARY
";
if (!create_table($tabname, $flds))
return;
// Insert records in table
$records = array("name"=>array("dv","ics","ics2","ims","lif","lsm","lsm-single","ome-xml","pic","stk","tiff","tiff-leica","tiff-series","tiff-single"),
"isFixedGeometry"=>array("f","f","f","f","f","f","t","f","f","f","f","f","f","t"),
"isSingleChannel"=>array("f","f","f","f","f","f","f","f","f","f","f","f","f","f"),
"isVariableChannel"=>array("t","t","t","t","t","t","t","t","t","t","t","t","t","t"));
if(!insert_records($records,$tabname))
return;
// file_extension
// -------------------------------------------------------------------------
// Drop table if it exists
$tabname = "file_extension";
if (in_array($tabname, $tables)) {
if (!drop_table($tabname))
return;
}
// Create table
$flds = "
file_format C(30) NOTNULL DEFAULT 0 PRIMARY,
extension C(4) NOTNULL PRIMARY
";
if (!create_table($tabname, $flds))
return;
// Insert records in table
$records = array("file_format"=>array("dv","ics","ics2","ims","lif","lsm","lsm-single","ome-xml","pic","stk","tiff","tiff-leica","tiff-series","tiff-single",
"tiff","tiff-leica","tiff-series","tiff-single"),
"extension"=>array("dv","ics","ics2","ims","lif","lsm","lsm","ome","pic","stk","tif","tif","tif","tif",
"tiff","tiff","tiff","tiff"));
if(!insert_records($records,$tabname))
return;
// queuemanager
// -------------------------------------------------------------------------
// Drop table if it exists
$tabname = "queuemanager";
if (in_array($tabname, $tables)) {
if (!drop_table($tabname))
return;
}
// Create table
$flds = "
field C(30) NOTNULL DEFAULT 0 PRIMARY,
value C(3) NOTNULL DEFAULT on
";
if (!create_table($tabname, $flds))
return;
// Insert records in table
$records = array("field"=>array("switch"),
"value"=>array("on"));
if(!insert_records($records,$tabname))
return;
// Drop and create fixed tables (create structure only)
// The content is deleted
// -------------------------------------------------------------------------
// -------------------------------------------------------------------------
// job_queue
// -------------------------------------------------------------------------
// Drop table if it exists
$tabname = "job_queue";
if (in_array($tabname, $tables)) {
if (!drop_table($tabname))
return;
}
// Create table
$flds = "
id C(30) NOTNULL DEFAULT 0 PRIMARY,
username C(30) NOTNULL,
queued T DEFAULT NULL,
start T DEFAULT NULL,
stop T DEFAULT NULL,
server C(30) DEFAULT NULL,
process_info C(30) DEFAULT NULL,
status C(8) NOTNULL DEFAULT queued
";
if (!create_table($tabname, $flds))
return;
// job_files
// -------------------------------------------------------------------------
// Drop table if it exists
$tabname = "job_files";
if (in_array($tabname, $tables)) {
if (!drop_table($tabname))
return;
}
// Create table
$flds = "
job C(30) DEFAULT 0 PRIMARY,
owner C(30) DEFAULT 0,
file C(255) DEFAULT 0 PRIMARY
";
if (!create_table($tabname, $flds))
return;
// job_parameter
// -------------------------------------------------------------------------
// Drop table if it exists
$tabname = "job_parameter";
if (in_array($tabname, $tables)) {
if (!drop_table($tabname))
return;
}
// Create table
$flds = "
owner C(30) NOTNULL DEFAULT 0 PRIMARY,
setting C(30) NOTNULL DEFAULT 0 PRIMARY,
name C(30) NOTNULL DEFAULT 0 PRIMARY,
value C(255) DEFAULT NULL
";
if (!create_table($tabname, $flds))
return;
// job_parameter_setting
// -----------------------------------------------------------------------------
// Drop table if it exists
$tabname = "job_parameter_setting";
if (in_array($tabname, $tables)) {
if (!drop_table($tabname))
return;
}
// Create table
$flds = "
owner C(30) NOTNULL DEFAULT 0 PRIMARY,
name C(30) NOTNULL PRIMARY,
standard C(1) DEFAULT t
";
if (!create_table($tabname, $flds))
return;
// job_task_parameter
// -----------------------------------------------------------------------------
// Drop table if it exists
$tabname = "job_task_parameter";
if (in_array($tabname, $tables)) {
if (!drop_table($tabname))
return;
}
// Create table
$flds = "
owner C(30) NOTNULL DEFAULT 0 PRIMARY,
setting C(30) NOTNULL PRIMARY,
name C(30) NOTNULL PRIMARY,
value C(255) DEFAULT NULL
";
if (!create_table($tabname, $flds))
return;
// job_task_setting
// -----------------------------------------------------------------------------
// Drop table if it exists
$tabname = "job_task_setting";
if (in_array($tabname, $tables)) {
if (!drop_table($tabname))
return;
}
// Create table
$flds = "
owner C(30) NOTNULL DEFAULT 0 PRIMARY,
name C(30) NOTNULL PRIMARY,
standard C(1) DEFAULT f
";
if (!create_table($tabname, $flds))
return;
// Check the existence and the structure of the tables with variable contents
// Keep the content
// -------------------------------------------------------------------------
// -------------------------------------------------------------------------
// parameter_setting
// -------------------------------------------------------------------------
$tabname = "parameter_setting";
$flds = "
owner C(30) NOTNULL DEFAULT 0 PRIMARY,
name C(30) NOTNULL PRIMARY,
standard C(1) DEFAULT f
";
if (!in_array($tabname, $tables)) {
if (!create_table($tabname, $flds))
return;
}
// task_setting
// -------------------------------------------------------------------------
$tabname = "task_setting";
//$flds = "
// owner C(30) NOTNULL DEFAULT 0 PRIMARY,
// name C(30) NOTNULL PRIMARY,
// standard \"enum('t','f')\" DEFAULT 'f'
//";
$flds = "
owner C(30) NOTNULL DEFAULT 0 PRIMARY,
name C(30) NOTNULL PRIMARY,
standard C(1) DEFAULT f
";
if (!in_array($tabname, $tables)) {
if (!create_table($tabname, $flds))
return;
}
//if(!check_table_existence_and_structure($tabname,$flds))
// return;
// Manage enum problem
//$values_string = "'t', 'f'";
//if (!manage_enum($tabname, 'standard', $values_string, 'f'))
// return;
// server
// -------------------------------------------------------------------------
$tabname = "server";
$flds = "
name C(60) NOTNULL DEFAULT 0 PRIMARY,
huscript_path C(60) NOTNULL,
status C(12) NOTNULL DEFAULT free,
job C(30) DEFAULT NULL
";
if (!in_array($tabname, $tables)) {
if (!create_table($tabname, $flds))
return;
// Insert records in table
$records = array("name"=>array("localhost"),
"huscript_path"=>array("/usr/local/bin/hucore"),
"status"=>array("free"),
"job"=>array(""));
if(!insert_records($records,$tabname))
return;
}
// username
// -------------------------------------------------------------------------
$tabname = "username";
$defaultTimestamp = timestampADODB();
$flds = "
name C(30) NOTNULL PRIMARY,
password C(255) NOTNULL,
email C(80) NOTNULL,
research_group C(30) NOTNULL,
creation_date T NOTNULL DEFAULT '" . $defaultTimestamp ."',
last_access_date T NOTNULL DEFAULT '" . $defaultTimestamp . "',
status C(10) NOTNULL
";
if (!in_array($tabname, $tables)) {
if (!create_table($tabname, $flds))
return;
}
$rs = $db->Execute("SELECT * FROM username WHERE name = 'admin'");
if($rs->EOF) {
$records = array("name"=>array("admin"),
"password"=>array("e903fece385fd2167780216958310b0d"),
"email"=>array(" "),
"research_group"=>array(" "),
"creation_date"=>array($defaultTimestamp),
"last_access"=>array($defaultTimestamp),
"status"=>array("a")
);
if(!insert_records($records,$tabname))
return;
}
// Check the existence and the structure of the tables with variable contents;
// check the format of the records content (number of #).
// Keep the content
// -------------------------------------------------------------------------
// -------------------------------------------------------------------------
// task_parameter
// -------------------------------------------------------------------------
$tabname = "task_parameter";
$flds = "
owner C(30) NOTNULL DEFAULT 0 PRIMARY,
setting C(30) NOTNULL PRIMARY,
name C(30) NOTNULL PRIMARY,
value C(255) DEFAULT NULL
";
if (!in_array($tabname, $tables)) {
if (!create_table($tabname, $flds))
return;
}
$fields_set = array('owner','setting','name','value');
$primary_key = array('owner','setting','name');
// Verify fields (number of #) where value = 'NumberOfIterationsRange'
if(!check_number_gates($tabname,'NumberOfIterationsRange',$fields_set,$primary_key))
return;
// Verify fields (number of #) where value = 'RemoveBackgroundPercent'
if(!check_number_gates($tabname,'RemoveBackgroundPercent',$fields_set,$primary_key))
return;
// Verify fields (number of #) where value = 'SignalNoiseRatio'
if(!check_number_gates($tabname,'SignalNoiseRatio',$fields_set,$primary_key))
return;
// Verify fields (number of #) where value = 'SignalNoiseRatioRange'
if(!check_number_gates($tabname,'SignalNoiseRatioRange',$fields_set,$primary_key))
return;
// Verify fields (number of #) where value = 'BackgroundOffsetPercent'
if(!check_number_gates($tabname,'BackgroundOffsetPercent',$fields_set,$primary_key))
return;
// Verify fields (number of #) where value = 'BackgroundOffsetRange'
if(!check_number_gates($tabname,'BackgroundOffsetRange',$fields_set,$primary_key))
return;
// parameter
// -------------------------------------------------------------------------
$tabname = "parameter";
$flds = "
owner C(30) NOTNULL DEFAULT 0 PRIMARY,
setting C(30) NOTNULL DEFAULT 0 PRIMARY,
name C(30) NOTNULL DEFAULT 0 PRIMARY,
value C(255) DEFAULT NULL
";
if (!in_array($tabname, $tables)) {
if (!create_table($tabname, $flds))
return;
}
$fields_set = array('owner','setting','name','value');
$primary_key = array('owner','setting','name');
// Verify fields (number of #) where value = 'PSF'
if(!check_number_gates($tabname,'PSF',$fields_set,$primary_key))
return;
// Verify fields (number of #) where value = 'PinholeSize'
if(!check_number_gates($tabname,'PinholeSize',$fields_set,$primary_key))
return;
// Verify fields (number of #) where value = 'EmissionWavelength'
if(!check_number_gates($tabname,'EmissionWavelength',$fields_set,$primary_key))
return;
// Verify fields (number of #) where value = 'ExcitationWavelength'
if(!check_number_gates($tabname,'ExcitationWavelength',$fields_set,$primary_key))
return;
}
// -----------------------------------------------------------------------------
// Update the database to the last revision
// -----------------------------------------------------------------------------
$msg = "Needed database revision is number " . $LAST_REVISION . ".\n";
$msg .= "Current database revision is number " . $current_revision . ".\n";
if( $LAST_REVISION == $current_revision ) {
$msg .= "Nothing to do.\n";
} else {
$msg .= "Updating...\n";
}
write_message($msg);
write_to_log($msg);
// -----------------------------------------------------------------------------
// Update to revision 1
// Description: add qmle algorithm as option
// -----------------------------------------------------------------------------
$n = 1;
if ($current_revision < $n) {
$tabname = "possible_values";
$record = array();
$record["parameter"] = "DeconvolutionAlgorithm";
$record["value"] = "cmle";
$record["translation"] = "Classic Maximum Likelihood Estimation";
$record["isDefault"] = "t";
$insertSQL = $db->GetInsertSQL($tabname, $record);
if(!$db->Execute($insertSQL)) {
$msg = "An error occurred while updating the database to revision " . $n . ".";
write_message($msg);
write_to_error($msg);
return;
}
$record["value"] = "qmle";
$record["translation"] = "Quick Maximum Likelihood Estimation";
$record["isDefault"] = "f";
$insertSQL = $db->GetInsertSQL($tabname, $record);
if(!$db->Execute($insertSQL)) {
$msg = "An error occurred while updating the database to revision " . $n . ".";
write_message($msg);
write_to_error($msg);
return;
}
if(!update_dbrevision($n))
return;
$current_revision = $n;
$msg = "Database successfully updated to revision " . $current_revision . ".";
write_message($msg);
write_to_log($msg);
}
// -----------------------------------------------------------------------------
// Update to revision 2
// Description: add ICS2 as possible output file format
// -----------------------------------------------------------------------------
$n = 2;
if ($current_revision < $n) {
$tabname = "possible_values";
$record = array();
$record["parameter"] = "OutputFileFormat";
$record["value"] = "ICS2 (Image Cytometry Standard 2)";
$record["translation"] = "ics2";
$record["isDefault"] = "F";
$insertSQL = $db->GetInsertSQL($tabname, $record);
if(!$db->Execute($insertSQL)) {
$msg = "An error occurred while updating the database to revision " . $n . ".";
write_message($msg);
write_to_error($msg);
return;
}
if(!update_dbrevision($n))
return;
$current_revision = $n;
$msg = "Database successfully updated to revision " . $current_revision . ".";
write_message($msg);
write_to_log($msg);
}
// -----------------------------------------------------------------------------
// Update to revision 3
// Description: remove psf generation in script (check sample orientation)
// -----------------------------------------------------------------------------
$n = 3;
if ($current_revision < $n) {
$tabname = "possible_values";
$rs = $db->Execute("DELETE FROM " . $tabname . " WHERE parameter = 'CoverslipRelativePosition'");
if(!$rs) {
$msg = "An error occurred while updating the database to revision " . $n . ".";
write_message($msg);
write_to_error($msg);
return;
}
$record = array();
$record["parameter"] = "CoverslipRelativePosition";
$record["value"] = "closest";
$record["translation"] = "Plane 0 is closest to the coverslip";
$record["isDefault"] = "T";
$insertSQL = $db->GetInsertSQL($tabname, $record);
if(!$db->Execute($insertSQL)) {
$msg = "An error occurred while updating the database to revision " . $n . ".";
write_message($msg);
write_to_error($msg);
return;
}
$record["value"] = "farthest";
$record["translation"] = "Plane 0 is farthest from the coverslip";
$record["isDefault"] = "F";
$insertSQL = $db->GetInsertSQL($tabname, $record);
if(!$db->Execute($insertSQL)) {
$msg = "An error occurred while updating the database to revision " . $n . ".";
write_message($msg);
write_to_error($msg);
return;
}
$record["value"] = "ignore";
$record["translation"] = "Do not perform depth-dependent correction";
$insertSQL = $db->GetInsertSQL($tabname, $record);
if(!$db->Execute($insertSQL)) {
$msg = "An error occurred while updating the database to revision " . $n . ".";
write_message($msg);
write_to_error($msg);
return;
}
// Check if the value are correct in parameter (correction respect to the previous version top/bottom/ignore)
$rs = $db->Execute("SELECT * FROM parameter WHERE name = 'CoverslipRelativePosition'");
if($rs) {
while ($row = $rs->FetchRow()) {
if(strcmp($row[2],'CoverslipRelativePosition') == 0) {
if(strcmp($row[3],'top') == 0)
$row[3] = 'closest';
elseif(strcmp($row[3],'bottom') == 0)
$row[3] = 'farthest';
$fields_set = array('owner','setting','name','value');
for($i = 0; $i < count($fields_set); $i++) {
$temp[$fields_set[$i]] = $row[$i];
}
$primary_key = array('owner', 'setting', 'name');
if(!$ret = $db->Replace('parameter',$temp,$primary_key,$autoquote=true)) {
$msg = error_message('parameter');
write_message($msg);
write_to_error($msg);
return False;
}
}
}
}
if(!update_dbrevision($n))
return;
$current_revision = $n;
$msg = "Database successfully updated to revision " . $current_revision . ".";
write_message($msg);
write_to_log($msg);
}
// -----------------------------------------------------------------------------
// Update to revision 4
// Description: support for zvi file format in HRM
// -----------------------------------------------------------------------------
$n = 4;
if ($current_revision < $n) {
$tabname = "file_extension";
$record = array();
$record["file_format"] = "zvi";
$record["extension"] = "zvi";
$insertSQL = $db->GetInsertSQL($tabname, $record);
if(!$db->Execute($insertSQL)) {
$msg = "An error occurred while updating the database to revision " . $n . ".";
write_message($msg);
write_to_error($msg);
return;
}
$tabname = "file_format";
$record = array();
$record["name"] = "zvi";
$record["isFixedGeometry"] = "f";
$record["isSingleChannel"] = "f";
$record["isVariableChannel"] = "t";
$insertSQL = $db->GetInsertSQL($tabname, $record);
if(!$db->Execute($insertSQL)) {
$msg = "An error occurred while updating the database to revision " . $n . ".";
write_message($msg);
write_to_error($msg);
return;
}
$tabname = "possible_values";
$record = array();
$record["parameter"] = "ImageFileFormat";
$record["value"] = "zvi";
$record["translation"] = "Zeiss Vision ZVI (*.zvi)";
$record["isDefault"] = "f";
$insertSQL = $db->GetInsertSQL($tabname, $record);
if(!$db->Execute($insertSQL)) {
$msg = "An error occurred while updating the database to revision " . $n . ".";
write_message($msg);
write_to_error($msg);
return;
}
if(!update_dbrevision($n))
return;
$current_revision = $n;
$msg = "Database successfully updated to revision " . $current_revision . ".";
write_message($msg);
write_to_log($msg);
}
// -----------------------------------------------------------------------------
// Update to revision 5
// Description: modification for Spherical Aberration correction
// -----------------------------------------------------------------------------
$n = 5;
if ($current_revision < $n) {
$tabname = "possible_values";
$rs = $db->Execute("SELECT * FROM " . $tabname . " WHERE parameter = 'CoverslipRelativePosition' AND value = 'ignore'");
if (!($rs->EOF)) {
$rss = $db->Execute("DELETE FROM " . $tabname . " WHERE parameter = 'CoverslipRelativePosition' AND value = 'ignore'");
if(!$rss) {
$msg = "An error occurred while updating the database to revision " . $n . ".\n";
write_message($msg);
write_to_error($msg);
return;
}
}
$rs = $db->Execute("SELECT * FROM " . $tabname . " WHERE parameter = 'PerformAberrationCorrection' AND translation = 'Do not perform depth-dependent correction'");
if (!($rs->EOF)) {
$rss = $db->Execute("DELETE FROM " . $tabname . " WHERE parameter = 'PerformAberrationCorrection' AND translation = 'Do not perform depth-dependent correction'");
if(!$rss) {
$msg = "An error occurred while updating the database to revision " . $n . ".";
write_message($msg);
write_to_error($msg);
return;
}
}
$record = array();
$record["parameter"] = "AberrationCorrectionNecessary";
$record["value"] = "0";
$record["translation"] = "no";
$record["isDefault"] = "T";
$rs = $db->Execute("SELECT * FROM " . $tabname . " WHERE parameter='" . $record["parameter"] . "' AND value='" . $record["value"] . "' AND translation='" . $record["translation"] . "' AND isDefault='" . $record["isDefault"] . "'");
if ($rs->EOF) {
$insertSQL = $db->GetInsertSQL($tabname, $record);
if(!$db->Execute($insertSQL)) {
$msg = "An error occurred while updating the database to revision " . $n . ".";
write_message($msg);
write_to_error($msg);
return;
}
}
$record["value"] = "1";
$record["translation"] = "yes";
$record["isDefault"] = "F";
$rs = $db->Execute("SELECT * FROM " . $tabname . " WHERE parameter='" . $record["parameter"] . "' AND value='" . $record["value"] . "' AND translation='" . $record["translation"] . "' AND isDefault='" . $record["isDefault"] . "'");
if ($rs->EOF) {
$insertSQL = $db->GetInsertSQL($tabname, $record);
if(!$db->Execute($insertSQL)) {
$msg = "An error occurred while updating the database to revision " . $n . ".";
write_message($msg);
write_to_error($msg);
return;
}
}
$record["parameter"] = "PerformAberrationCorrection";
$record["value"] = "1";
$record["translation"] = "Yes, perform depth-dependent correction";
$record["isDefault"] = "f";
$rs = $db->Execute("SELECT * FROM " . $tabname . " WHERE parameter='" . $record["parameter"] . "' AND value='" . $record["value"] . "' AND translation='" . $record["translation"] . "' AND isDefault='" . $record["isDefault"] . "'");
if ($rs->EOF) {
$insertSQL = $db->GetInsertSQL($tabname, $record);
if(!$db->Execute($insertSQL)) {
$msg = "An error occurred while updating the database to revision " . $n . ".";
write_message($msg);
write_to_error($msg);
return;
}
}
$record["parameter"] = "AberrationCorrectionMode";
$record["value"] = "automatic";
$record["translation"] = "Perform automatic correction";
$record["isDefault"] = "T";
$rs = $db->Execute("SELECT * FROM " . $tabname . " WHERE parameter='" . $record["parameter"] . "' AND value='" . $record["value"] . "' AND translation='" . $record["translation"] . "' AND isDefault='" . $record["isDefault"] . "'");
if ($rs->EOF) {
$insertSQL = $db->GetInsertSQL($tabname, $record);
if(!$db->Execute($insertSQL)) {
$msg = "An error occurred while updating the database to revision " . $n . ".";
write_message($msg);
write_to_error($msg);
return;
}
}
$record["value"] = "advanced";
$record["translation"] = "Perform advanced correction";
$record["isDefault"] = "F";
$rs = $db->Execute("SELECT * FROM " . $tabname . " WHERE parameter='" . $record["parameter"] . "' AND value='" . $record["value"] . "' AND translation='" . $record["translation"] . "' AND isDefault='" . $record["isDefault"] . "'");
if ($rs->EOF) {
$insertSQL = $db->GetInsertSQL($tabname, $record);
if(!$db->Execute($insertSQL)) {
$msg = "An error occurred while updating the database to revision " . $n . ".";
write_message($msg);
write_to_error($msg);
return;
}
}
$record["parameter"] = "PSFGenerationDepth";
$record["value"] = "0";
$record["translation"] = "0";
$record["isDefault"] = "T";
$rs = $db->Execute("SELECT * FROM " . $tabname . " WHERE parameter='" . $record["parameter"] . "' AND value='" . $record["value"] . "' AND translation='" . $record["translation"] . "' AND isDefault='" . $record["isDefault"] . "'");
if ($rs->EOF) {
$insertSQL = $db->GetInsertSQL($tabname, $record);
if(!$db->Execute($insertSQL)) {
$msg = "An error occurred while updating the database to revision " . $n . ".";
write_message($msg);
write_to_error($msg);
return;
}
}
$tabname = "boundary_values";
$record = array();
$record["parameter"] = "PSFGenerationDepth";
$record["min"] = "0";
$record["max"] = "NULL";
$record["min_included"] = "T";
$record["max_included"] = "T";
$record["standard"] = "0";
$rs = $db->Execute("SELECT * FROM " . $tabname . " WHERE parameter='" . $record["parameter"] . "' AND min='" . $record["min"] . "' AND max='" . $record["max"] . "' AND min_included='" . $record["min_included"] . "' AND max_included='" . $record["max_included"] . "' AND standard='" . $record["standard"] . "'");
if ($rs->EOF) {
$insertSQL = $db->GetInsertSQL($tabname, $record);
if(!$db->Execute($insertSQL)) {
$msg = "An error occurred while updating the database to revision " . $n . ".";
write_message($msg);
write_to_error($msg);
return;
}
}
if(!update_dbrevision($n))
return;
$current_revision = $n;
$msg = "Database successfully updated to revision " . $current_revision . ".";
write_message($msg);
write_to_log($msg);
}
// -----------------------------------------------------------------------------
// Update to revision 6
// Description: change lenght of text fields (settings name and translation).
// Coorect field in possible_values
// -----------------------------------------------------------------------------
$n = 6;
if ($current_revision < $n) {
$tabname = "parameter";
$flds = "
owner C(30) NOTNULL DEFAULT 0 PRIMARY,
setting C(255) NOTNULL DEFAULT 0 PRIMARY,
name C(30) NOTNULL DEFAULT 0 PRIMARY,
value C(255) DEFAULT NULL
";
$colnames = array("owner","setting","name","value");
$multiarray = $db->GetArray("SELECT * from " . $tabname);
if(!drop_table($tabname))
return;
if(!create_table($tabname, $flds))
return;
foreach($multiarray as $array) {
if(!insert_record($tabname, $array, $colnames))
return;
}
$tabname = "parameter_setting";
$flds = "
owner C(30) NOTNULL DEFAULT 0 PRIMARY,
name C(255) NOTNULL PRIMARY,
standard C(1) DEFAULT f
";
$colnames = array("owner","name","standard");
$multiarray = $db->GetArray("SELECT * from " . $tabname);
if(!drop_table($tabname))
return;
if(!create_table($tabname, $flds))
return;
foreach($multiarray as $array) {
if(!insert_record($tabname, $array, $colnames))
return;
}
$tabname = "task_parameter";
$flds = "
owner C(30) NOTNULL DEFAULT 0 PRIMARY,
setting C(255) NOTNULL PRIMARY,
name C(30) NOTNULL PRIMARY,
value C(255) DEFAULT NULL
";
$colnames = array("owner","setting","name","value");
$multiarray = $db->GetArray("SELECT * from " . $tabname);
if(!drop_table($tabname))
return;
if(!create_table($tabname, $flds))
return;
foreach($multiarray as $array) {
if(!insert_record($tabname, $array, $colnames))
return;
}
$tabname = "task_setting";
$flds = "
owner C(30) NOTNULL DEFAULT 0 PRIMARY,
name C(255) NOTNULL PRIMARY,
standard C(1) DEFAULT f
";
$colnames = array("owner","name","standard");
$multiarray = $db->GetArray("SELECT * from " . $tabname);
if(!drop_table($tabname))
return;
if(!create_table($tabname, $flds))
return;
foreach($multiarray as $array) {
if(!insert_record($tabname, $array, $colnames))
return;
}
$tabname = "possible_values";
$flds = "
parameter C(30) NOTNULL DEFAULT 0 PRIMARY,
value C(255) NOTNULL DEFAULT 0 PRIMARY,
translation C(255) DEFAULT NULL,
isDefault C(1) DEFAULT f
";
$colnames = array("parameter","value","translation","isDefault");
$multiarray = $db->GetArray("SELECT * from " . $tabname);
if(!drop_table($tabname))
return;
if(!create_table($tabname, $flds))
return;
foreach($multiarray as $array) {
if(!insert_record($tabname, $array, $colnames))
return;
}
$record[$colnames[0]] = "PerformAberrationCorrection";
$record[$colnames[1]] = "0";
$record[$colnames[2]] = "No, do not perform depth-dependent correction";
$record[$colnames[3]] = "T";
$array = array("PerformAberrationCorrection","0","No, do not perform depth-dependent correction","t");
$rs = $db->Execute("SELECT * FROM " . $tabname . " WHERE parameter='" . $record["parameter"] . "' AND value='" . $record["value"] . "'");
if (!($rs->EOF)) {
if(!$db->Execute("DELETE FROM " . $tabname . " WHERE parameter='" . $record["parameter"] . "' AND value='" . $record["value"] . "'")) {
$msg = "An error occurred while updating the database to revision " . $n . ".";
write_message($msg);
write_to_log($msg);
write_to_error($msg);
return;
}
}
$rs = $db->Execute("SELECT * FROM " . $tabname . " WHERE parameter='" . $record["parameter"] . "' AND value='" . $record["value"] . "'");
if ($rs->EOF) {
if(!insert_record($tabname, $array, $colnames))
return;
}
$record[$colnames[0]] = "AdvancedCorrectionOptions";
$record[$colnames[1]] = "user";
$record[$colnames[2]] = "Deconvolution with PSF generated at user-defined depth";
$record[$colnames[3]] = "T";
$array = array("AdvancedCorrectionOptions","user","Deconvolution with PSF generated at user-defined depth","T");
$rs = $db->Execute("SELECT * FROM " . $tabname . " WHERE parameter='" . $record[$colnames[0]] . "' AND value='" . $record[$colnames[1]] . "'");
if (!($rs->EOF)) {
if(!$db->Execute("DELETE FROM " . $tabname . " WHERE parameter='" . $record[$colnames[0]] . "' AND value='" . $record[$colnames[1]] . "'")) {
$msg = "An error occurred while updating the database to revision " . $n . ".";
write_message($msg);
write_to_log($msg);
write_to_error($msg);
return;
}
}
$rs = $db->Execute("SELECT * FROM " . $tabname . " WHERE parameter='" . $record["parameter"] . "' AND value='" . $record["value"] . "'");
if ($rs->EOF) {
if(!insert_record($tabname, $array, $colnames))
return;
}
$record[$colnames[1]] = "slice";
$record[$colnames[2]] = "Depth-dependent correction performed slice by slice";
$record[$colnames[3]] = "F";
$array = array("AdvancedCorrectionOptions","slice","Depth-dependent correction performed slice by slice","F");
$rs = $db->Execute("SELECT * FROM " . $tabname . " WHERE parameter='" . $record["parameter"] . "' AND value='" . $record["value"] . "'");
if (!($rs->EOF)) {
if(!$db->Execute("DELETE FROM " . $tabname . " WHERE parameter='" . $record["parameter"] . "' AND value='" . $record["value"] . "'")) {
$msg = "An error occurred while updating the database to revision " . $n . ".";
write_message($msg);
write_to_log($msg);
write_to_error($msg);
return;
}
}
$rs = $db->Execute("SELECT * FROM " . $tabname . " WHERE parameter='" . $record["parameter"] . "' AND value='" . $record["value"] . "'");
if ($rs->EOF) {
if(!insert_record($tabname, $array, $colnames))
return;
}
$record[$colnames[1]] = "few";
$record[$colnames[2]] = "Depth-dependent correction performed on few bricks";
$record[$colnames[3]] = "F";
$array = array("AdvancedCorrectionOptions","few","Depth-dependent correction performed on few bricks","F");
$rs = $db->Execute("SELECT * FROM " . $tabname . " WHERE parameter='" . $record["parameter"] . "' AND value='" . $record["value"] . "'");
if (!($rs->EOF)) {
if(!$db->Execute("DELETE FROM " . $tabname . " WHERE parameter='" . $record["parameter"] . "' AND value='" . $record["value"] . "'")) {
$msg = "An error occurred while updating the database to revision " . $n . ".";
write_message($msg);
write_to_log($msg);
write_to_error($msg);
return;
}
}
$rs = $db->Execute("SELECT * FROM " . $tabname . " WHERE parameter='" . $record["parameter"] . "' AND value='" . $record["value"] . "'");
if ($rs->EOF) {
if(!insert_record($tabname, $array, $colnames))
return;
}
if(!update_dbrevision($n))
return;
$current_revision = $n;
$msg = "Database successfully updated to revision " . $current_revision . ".";
write_message($msg);
write_to_log($msg);
}
// -----------------------------------------------------------------------------
// Update to revision 7
// Description: insert statistics table;
// add 'priority' column in table 'job_queue';
// correct ics2 extension;
// add support for ics2 file format in intput;
// add support for HDF5 file format (in input and output)
// -----------------------------------------------------------------------------
$n = 7;
if ($current_revision < $n) {
// Update tables array
$tables = $db->MetaTables("TABLES");
if (!in_array("statistics", $tables)) {
// If the table does not exist, create it
$flds = "
id C(30) NOTNULL DEFAULT 0 PRIMARY,
owner C(30) DEFAULT 0,
research_group C(30) DEFAULT 0,
start T DEFAULT NULL,
stop T DEFAULT NULL,
ImageFileFormat C(255) DEFAULT 0,
OutputFileFormat C(255) DEFAULT 0,
PointSpreadFunction C(255) DEFAULT 0,
ImageGeometry C(255) DEFAULT 0,
MicroscopeType C(255) DEFAULT 0
";
if (!create_table("statistics", $flds)) {
$msg = "An error occurred while updating the database to revision " . $n . ", statistics table creation.";
write_message($msg);
write_to_log($msg);
write_to_error($msg);
return;
}
}
// Add 'priority' column in table 'job_queue'
$tabname = "job_queue";
if (in_array($tabname, $tables)) {
if (!drop_table($tabname)) {
$msg = "An error occurred while updating the database to revision " . $n . ", job_queue 1.";
write_message($msg);
write_to_log($msg);
write_to_error($msg);
return;
}
}
// Create table
$flds = "
id C(30) NOTNULL DEFAULT 0 PRIMARY,
username C(30) NOTNULL,
queued T DEFAULT NULL,
start T DEFAULT NULL,
stop T DEFAULT NULL,
server C(30) DEFAULT NULL,
process_info C(30) DEFAULT NULL,
status C(8) NOTNULL DEFAULT queued,
priority I NOTNULL DEFAULT 0
";
if (!create_table($tabname, $flds)) {
$msg = "An error occurred while updating the database to revision " . $n . ", job_queue 2.";
write_message($msg);
write_to_log($msg);
write_to_error($msg);
return;
}
// Change ics2 extension in ics
$tabname = "file_extension";
$record = array();
$record["file_format"] = "ics2";
$record["extension"] = "ics";
if (!$db->AutoExecute($tabname, $record, 'UPDATE', "file_format like 'ics2'")) {
$msg = "An error occurred while updating the database to revision " . $n . ", update ics2 format information.";
write_message($msg);
write_to_error($msg);
return false;
}
// Add support for HDF5 file format
$record = array();
$record["file_format"] = "hdf5";
$record["extension"] = "h5";
$rs = $db->Execute("SELECT * FROM " . $tabname . " WHERE file_format='" . $record["file_format"] . "' AND extension='" . $record["extension"] . "'");
if ($rs->EOF) {
$insertSQL = $db->GetInsertSQL($tabname, $record);
if(!$db->Execute($insertSQL)) {
$msg = "An error occurred while updating the database to revision " . $n . ".";
write_message($msg);
write_to_error($msg);
return;
}
}
$tabname = "file_format";
$record = array();
$record["name"] = "hdf5";
$record["isFixedGeometry"] = "f";
$record["isSingleChannel"] = "f";
$record["isVariableChannel"] = "t";
$rs = $db->Execute("SELECT * FROM " . $tabname . " WHERE name='" . $record["name"] . "' AND isFixedGeometry='" . $record["isFixedGeometry"] . "' AND isSingleChannel='" . $record["isSingleChannel"] . "'AND isVariableChannel='" . $record["isVariableChannel"] . "'");
if ($rs->EOF) {
$insertSQL = $db->GetInsertSQL($tabname, $record);
if(!$db->Execute($insertSQL)) {
$msg = "An error occurred while updating the database to revision " . $n . ".";
write_message($msg);
write_to_error($msg);
return;
}
}
$tabname = "possible_values";
$record = array();
$record["parameter"] = "ImageFileFormat";
$record["value"] = "ics2";
$record["translation"] = "Image Cytometry Standard 2 (*.ics/*.ids)";
$record["isDefault"] = "f";
$rs = $db->Execute("SELECT * FROM " . $tabname . " WHERE parameter='" . $record["parameter"] . "' AND value='" . $record["value"] . "'");
if ($rs->EOF) {
$insertSQL = $db->GetInsertSQL($tabname, $record);
if(!$db->Execute($insertSQL)) {
$msg = "An error occurred while updating the database to revision " . $n . ".";
write_message($msg);
write_to_error($msg);
return;
}
}
$record = array();
$record["parameter"] = "ImageFileFormat";
$record["value"] = "hdf5";
$record["translation"] = "SVI HDF5 (.*h5)";
$record["isDefault"] = "f";
$rs = $db->Execute("SELECT * FROM " . $tabname . " WHERE parameter='" . $record["parameter"] . "' AND value='" . $record["value"] . "'");
if ($rs->EOF) {
$insertSQL = $db->GetInsertSQL($tabname, $record);
if(!$db->Execute($insertSQL)) {
$msg = "An error occurred while updating the database to revision " . $n . ".";
write_message($msg);
write_to_error($msg);
return;
}
}
$record = array();
$record["parameter"] = "OutputFileFormat";
$record["value"] = "hdf5";
$record["translation"] = "SVI HDF5";
$record["isDefault"] = "f";
$rs = $db->Execute("SELECT * FROM " . $tabname . " WHERE parameter='" . $record["parameter"] . "' AND value='" . $record["value"] . "'");
if (!($rs->EOF)) {
if(!$db->Execute("DELETE FROM " . $tabname . " WHERE parameter='" . $record["parameter"] . "' AND value='" . $record["value"] . "'")) {
$msg = "An error occurred while updating the database to revision " . $n . ".";
write_message($msg);
write_to_log($msg);
write_to_error($msg);
return;
}
}
$record = array();
$record["parameter"] = "OutputFileFormat";
$record["value"] = "SVI HDF5";
$record["translation"] = "hdf5";
$record["isDefault"] = "f";
$rs = $db->Execute("SELECT * FROM " . $tabname . " WHERE parameter='" . $record["parameter"] . "' AND value='" . $record["value"] . "'");
if ($rs->EOF) {
$insertSQL = $db->GetInsertSQL($tabname, $record);
if(!$db->Execute($insertSQL)) {
$msg = "An error occurred while updating the database to revision " . $n . ".";
write_message($msg);
write_to_error($msg);
return;
}
}
// Check the existence of the server table and the presence of one entry at least
// If there is no entry, insert default values (localhost, /usr/local/bin/hucore, free, NULL)
$tabname = "server";
if (in_array($tabname, $tables)) {
$rs = $db->Execute("SELECT * FROM " . $tabname);
$temp = $rs->RecordCount();
//write_message("Record count = " . $temp);
if($temp == 0) {
$records = array("name"=>array("localhost"),
"huscript_path"=>array("/usr/local/bin/hucore"),
"status"=>array("free"),
"job"=>array(""));
if(!insert_records($records,$tabname)) {
$msg = "An error occurred while updating the database to revision " . $n . ".";
write_message($msg);
write_to_error($msg);
return;
}
}
}
if(!update_dbrevision($n))
return;
$current_revision = $n;
$msg = "Database successfully updated to revision " . $current_revision . ".";
write_message($msg);
write_to_log($msg);
}
//$msg = "\nThe current revision of your HRM database is " . $current_revision . ".";
//write_message($msg);
//write_to_log($msg);
fclose($fh);
return;
?>