OpenDRS/database-update.sql

-- opendrs-update.sql

-- ---------- version 1.1.0 -------------
-- Update version number
UPDATE config SET name="drs_version" WHERE name="mts_version";
UPDATE config SET value="1.1.0",defaultvalue="1.1.0" WHERE name="drs_version";

-- Add period_eff_help_txt to config table
INSERT INTO config(name,value,defaultvalue)
SELECT * FROM 
(SELECT 'period_eff_help_txt',
'If maintenance issues prevented the training objectives from being achieved and you will have to come back and redo the same training, answer Non-effective. Otherwise, answer Effective.' AS wval,
'If maintenance issues prevented the training objectives from being achieved and you will have to come back and redo the same training, answer Non-effective. Otherwise, answer Effective.' AS dval
)
AS tmp
WHERE NOT EXISTS 
(
SELECT name FROM config WHERE name = 'period_eff_help_txt'
) LIMIT 1
;
-- --------------------------------------

-- ---------- version 1.1.1 -------------
-- Update version number
UPDATE config SET value="1.1.1",defaultvalue="1.1.1" WHERE name="drs_version";

-- Rename the trainers table to devices
RENAME TABLE trainers TO devices;

-- Update default app name
UPDATE config SET defaultvalue="OpenDRS Discrepancy Reporting System" WHERE name="app_name";

-- Update default session directories
UPDATE config SET defaultvalue="/tmp/opendrs/opendrs/sessions" WHERE name="unix_server_session_dir";
UPDATE config SET defaultvalue="c:\Windows\Temp\opendrs\opendrs\sessions" WHERE name="win_server_session_dir";
-- --------------------------------------

-- ---------- version 1.1.2 -------------
-- Update version number
UPDATE config SET value="1.1.2",defaultvalue="1.1.2" WHERE name="drs_version";
-- --------------------------------------

-- ---------- version 1.2.0 -------------
-- Update version number
UPDATE config SET value="1.2.0",defaultvalue="1.2.0" WHERE name="drs_version";

-- Add device_term to config table
INSERT INTO config(name,value,defaultvalue)
SELECT * FROM 
(SELECT 'device_term',
'Device' AS wval,
'Device' AS dval
)
AS tmp
WHERE NOT EXISTS 
(
SELECT name FROM config WHERE name = 'device_term'
) LIMIT 1
;

-- Add discovered_term to config table
INSERT INTO config(name,value,defaultvalue)
SELECT * FROM 
(SELECT 'discovered_term',
'When Discovered' AS wval,
'When Discovered' AS dval
)
AS tmp
WHERE NOT EXISTS 
(
SELECT name FROM config WHERE name = 'discovered_term'
) LIMIT 1
;

-- Add discovered_term_short to config table
INSERT INTO config(name,value,defaultvalue)
SELECT * FROM 
(SELECT 'discovered_term_short',
'When Disc' AS wval,
'When Disc' AS dval
)
AS tmp
WHERE NOT EXISTS 
(
SELECT name FROM config WHERE name = 'discovered_term_short'
) LIMIT 1
;

-- Add disc_drop_data to config table
INSERT INTO config(name,value,defaultvalue)
SELECT * FROM 
(SELECT 'disc_drop_data',
'Name' AS wval,
'Name' AS dval
)
AS tmp
WHERE NOT EXISTS 
(
SELECT name FROM config WHERE name = 'disc_drop_data'
) LIMIT 1
;

-- Add functional_term to config table
INSERT INTO config(name,value,defaultvalue)
SELECT * FROM 
(SELECT 'functional_term',
'Functional' AS wval,
'Functional' AS dval
)
AS tmp
WHERE NOT EXISTS 
(
SELECT `name` FROM `config` WHERE `name` = "functional_term"
) LIMIT 1
;

-- Add functional_term_short to config table
INSERT INTO config(name,value,defaultvalue)
SELECT * FROM 
(SELECT 'functional_term_short',
'Func' AS wval,
'Func' AS dval
)
AS tmp
WHERE NOT EXISTS 
(
SELECT `name` FROM `config` WHERE `name` = "functional_term_short"
) LIMIT 1
;

-- Add functional_yes to config table
INSERT INTO config(name,value,defaultvalue)
SELECT * FROM 
(SELECT 'functional_yes',
'Functional' AS wval,
'Functional' AS dval
)
AS tmp
WHERE NOT EXISTS 
(
SELECT `name` FROM `config` WHERE `name` = "functional_yes"
) LIMIT 1
;

-- Add functional_no to config table
INSERT INTO config(name,value,defaultvalue)
SELECT * FROM 
(SELECT 'functional_no',
'Non-functional' AS wval,
'Non-functional' AS dval
)
AS tmp
WHERE NOT EXISTS 
(
SELECT `name` FROM `config` WHERE `name` = "functional_no"
) LIMIT 1
;

-- Add functional_yes_short to config table
INSERT INTO config(name,value,defaultvalue)
SELECT * FROM 
(SELECT 'functional_yes_short',
'OK' AS wval,
'OK' AS dval
)
AS tmp
WHERE NOT EXISTS 
(
SELECT `name` FROM `config` WHERE `name` = "functional_yes_short"
) LIMIT 1
;

-- Add functional_no_short to config table
INSERT INTO config(name,value,defaultvalue)
SELECT * FROM 
(SELECT 'functional_no_short',
'INOP' AS wval,
'INOP' AS dval
)
AS tmp
WHERE NOT EXISTS 
(
SELECT `name` FROM `config` WHERE `name` = "functional_no_short"
) LIMIT 1
;

-- Rename period_eff_help_txt config to functional_help_txt
UPDATE `config` SET name="functional_help_txt" WHERE name="period_eff_help_txt";

-- Change the default value of functional_help_text
UPDATE `config` SET defaultvalue="If this discrepancy renders the device unusable, answer No. Otherwise, answer Yes." WHERE name="function_help_txt";

-- Rename periods table to discovered
RENAME TABLE `periods` TO `discovered`;

-- Add whendiscoveredname column to discovered table
ALTER TABLE `discovered` ADD COLUMN `whendiscoveredname` text NOT NULL AFTER id;

-- Copy the contents of the id column to the whendiscoveredname column where whendiscoveredname is empty
UPDATE `discovered` SET `whendiscoveredname`=`id` WHERE `whendiscoveredname`="";

-- Rename times column of discovered table to whendiscovereddesc
ALTER TABLE `discovered` CHANGE COLUMN `times` `whendiscovereddesc` text not null;

-- Add auto_increment flag to id column
ALTER TABLE `discovered` CHANGE COLUMN `id` `id` text not null auto_increment;

-- Rename period column of writeups table to discovered
ALTER TABLE `writeups` CHANGE COLUMN `period` `discovered` int not null;

-- Rename period_effective column of writeups table to functional
ALTER TABLE `writeups` CHANGE COLUMN `period_effective` `functional` tinyint(1) not null;
-- --------------------------------------

-- ---------- version 1.3.0 -------------
-- Update version number
UPDATE config SET value="1.3.0",defaultvalue="1.3.0" WHERE name="drs_version";
-- --------------------------------------

-- ---------- version 1.3.1 -------------
-- Update version number
UPDATE config SET value="1.3.1",defaultvalue="1.3.1" WHERE name="drs_version";
-- --------------------------------------

-- ---------- version 1.3.2 -------------
-- Update version number
UPDATE config SET value="1.3.2",defaultvalue="1.3.2" WHERE name="drs_version";
-- --------------------------------------

-- ---------- version 1.3.3 -------------
-- Update version number
UPDATE config SET value="1.3.3",defaultvalue="1.3.3" WHERE name="drs_version";
-- --------------------------------------

-- ---------- version 1.4.0 -------------
-- Update version number
UPDATE config SET value="1.4.0",defaultvalue="1.4.0" WHERE name="drs_version";

-- Add access_mode to config table
INSERT INTO config(name,value,defaultvalue)
SELECT * FROM 
(SELECT 'access_mode',
'trusted' AS wval,
'trusted' AS dval
)
AS tmp
WHERE NOT EXISTS 
(
SELECT `name` FROM `config` WHERE `name` = "access_mode"
) LIMIT 1
;
-- --------------------------------------

-- ---------- version 1.4.1 -------------
-- Update version number
UPDATE config SET value="1.4.1",defaultvalue="1.4.1" WHERE name="drs_version";
-- --------------------------------------

-- ---------- version 1.4.2 -------------
-- Update version number
UPDATE config SET value="1.4.2",defaultvalue="1.4.2" WHERE name="drs_version";
-- --------------------------------------

-- ---------- version 1.4.3 -------------
-- Update version number
UPDATE config SET value="1.4.3",defaultvalue="1.4.3" WHERE name="drs_version";
-- --------------------------------------

-- ---------- version 1.4.4 -------------
-- Update version number
UPDATE config SET value="1.4.4",defaultvalue="1.4.4" WHERE name="drs_version";
-- --------------------------------------

-- ---------- version 1.4.5 -------------
-- Update version number
UPDATE config SET value="1.4.5",defaultvalue="1.4.5" WHERE name="drs_version";
-- --------------------------------------

-- ---------- version 1.4.6 -------------
-- Update version number
UPDATE config SET value="1.4.6",defaultvalue="1.4.6" WHERE name="drs_version";
-- --------------------------------------