// eventlot
CREATE TABLE LAB.eventlog
(
id INT NOT NULL DEFAULT SERIAL(1),
record_time timestamp NOT NULL DEFAULT TIMESTAMP,
source VARCHAR(50) NOT NULL,
type VARCHAR(10) NOT NULL,
event VARCHAR(25) NOT NULL,
event_user VARCHAR(50) NOT NULL DEFAULT 'N/A',
computer VARCHAR(50) NOT NULL DEFAULT 'N/A',
event_time timestamp NULL,
comment VARCHAR(2000) NULL,
raw_data LONG NULL,
PRIMARY KEY(id),
CONSTRAINT type IN ('info','warning','error'),
FOREIGN KEY ref2event (event)
REFERENCES LAB.events (event)
)
///////////////////////////////////////////////////
// TABLE LAB.events
CREATE TABLE LAB.events
(
event VARCHAR(25) NOT NULL,
event_name VARCHAR(100) NOT NULL,
event_desc VARCHAR(255) NULL,
audit INT NOT NULL DEFAULT 1,
PRIMARY KEY(event) ,
CONSTRAINT unique_event_name
UNIQUE (event_name)
)
///////////////////////////////////////////////////
// events
insert LAB.events (event, event_name, event_desc)
values ('auto_authorize' , 'auto authorize', 'auto authorize')
insert LAB.events (event, event_name, event_desc)
values ('end_of_sample' , 'end of sample', 'end of sample')
insert LAB.events (event, event_name, event_desc)
values ('inquiry_of_sample' , 'inquiry of sample', 'inquiry of sample')
insert LAB.events (event, event_name, event_desc)
values ('add_usergroup' , 'add user group in system', 'add user group in
system')
insert LAB.events (event, event_name, event_desc)
values ('drop_usergroup' , 'remove user group from system', 'remove user
group from system')
insert LAB.events (event, event_name, event_desc)
values ('add_user' , 'add user in system', 'add user in system')
insert LAB.events (event, event_name, event_desc)
values ('drop_user' , 'remove user from system', 'remove user from system')
insert LAB.events (event, event_name, event_desc)
values ('add_device' , 'add device in system', 'add device in system')
insert LAB.events (event, event_name, event_desc)
values ('add_producer' , 'add producer in system', 'add producer in
system')
insert LAB.events (event, event_name, event_desc)
values ('add_devicetype' , 'add type of device in system', 'add type of
device in system')
insert LAB.events (event, event_name, event_desc)
values ('add_analitgroup' , 'add analit group in system', 'add analit group
in system')
insert LAB.events (event, event_name, event_desc)
values ('add_analit' , 'add analit in system', 'add analit in system')
insert LAB.events (event, event_name, event_desc)
values ('add_rawresult' , 'add raw result from device', 'add raw result
from device')
insert LAB.events (event, event_name, event_desc)
values ('add_task' , 'add task', '')
insert LAB.events (event, event_name, event_desc)
values ('add_test' , 'add test', '')
insert LAB.events (event, event_name, event_desc)
values ('complete_test' , 'complete test', '')
///////////////////////////////////////////////////
// TABLE LAB.lab_specify_event_of_device
CREATE TABLE LAB.specify_event_of_device
(
device_id INT NOT NULL,
event VARCHAR(25) NOT NULL,
audit INT NOT NULL DEFAULT 1,
FOREIGN KEY ref2device (device_id)
REFERENCES LAB.devices (device_id),
FOREIGN KEY ref2event (event)
REFERENCES LAB.events (event)
)
///////////////////////////////////////////////////
// errors (5300 - 5400 eventlog)
5300
5400
///////////////////////////////////////////////////
// proc LAB.add_instrument_event
CREATE DBPROC LAB.add_instrument_event
( IN device_code VARCHAR(25)
, IN type VARCHAR(10)
, IN event VARCHAR(25)
, IN event_time timestamp
, IN comment VARCHAR(2000)
, OUT id INT
)
AS
VAR device_id INT; audit INT; node VARCHAR(50);
SET node = 'N/A';
SET audit = 1;
SET id = -1;
SET device_id = 0;
BEGIN
TRY
SELECT device_id FROM LAB.devices
WHERE device_code = :device_code;
FETCH INTO :device_id;
CATCH
IF $rc = 100 THEN STOP(5301, 'invalid argument: The device ''' ||
device_code || ''' not register in system')
ELSE STOP($rc, $errmsg);
TRY
SELECT audit = DECODE(c.audit, NULL, 1, c.audit)
FROM LAB.events a, LAB.devices b, LAB.specify_event_of_device c
WHERE a.event = c.event (+) and b.device_id = c.device_id (+) and
b.device_id = :device_id and a.event = :event;
FETCH INTO :audit;
CATCH
IF $rc <> 100 THEN STOP($rc, $errmsg);
TRY
SELECT APPLNODEID
FROM SYSDBA.TRANSACTIONS T, DOMAIN.CONNECTPARAMETERS C
WHERE T.SESSION = C.SESSION;
FETCH INTO :node;
CATCH
IF $rc <> 100 THEN STOP($rc, $errmsg);
IF audit = 1 THEN BEGIN
TRY
INSERT LAB.eventlog
( source
, type
, event
, event_user
, computer
, event_time
, comment
)
values
( :device_code
, :type
, :event
, USER
, :node
, :event_time
, :comment
);
SET id = LAB.eventlog.CURRVAL;
CATCH
STOP($rc, $errmsg);
END;
END;
///////////////////////////////////////////////////
// proc LAB.add_event
CREATE DBPROC LAB.add_event
( IN type VARCHAR(10)
, IN event VARCHAR(25)
, IN source VARCHAR(50)
, IN comment VARCHAR(2000)
)
AS
VAR node VARCHAR(50);
SET node = 'N/A';
BEGIN
TRY
SELECT APPLNODEID
FROM SYSDBA.TRANSACTIONS T, DOMAIN.CONNECTPARAMETERS C
WHERE T.SESSION = C.SESSION;
FETCH INTO :node;
CATCH
IF $rc <> 100 THEN STOP($rc, $errmsg);
TRY
INSERT LAB.eventlog(source, type, event, event_user, computer, event_time,
comment)
values(:source, :type, :event, USER, :node, timestamp, :comment);
CATCH
STOP($rc, $errmsg);
END;
//
///////////////////////////////////////////////////
// LAB.analits
CREATE TABLE LAB.analits
(
analit_id INT NOT NULL DEFAULT SERIAL(1),
analit_code VARCHAR(25) NOT NULL,
complex INT NOT NULL DEFAULT 0,
analit_name VARCHAR(100) NOT NULL,
analit_desc VARCHAR(255) NULL,
authorization INT NOT NULL DEFAULT 1,
protocol INT NOT NULL DEFAULT 0,
status INT NOT NULL DEFAULT 0,
created timestamp NOT NULL DEFAULT TIMESTAMP,
modified timestamp NOT NULL DEFAULT TIMESTAMP,
deleted timestamp NULL,
modifier VARCHAR(32) NOT NULL DEFAULT USER,
PRIMARY KEY(analit_id),
CONSTRAINT unique_key
UNIQUE (analit_code, complex)
)
///////////////////////////////////////////////////
// LAB.specify_analit4devicetype
CREATE TABLE LAB.specify_analit4devicetype
(
analit_devicetype INT NOT NULL DEFAULT SERIAL(1),
device_type VARCHAR(25) NOT NULL,
test_name VARCHAR(25) NOT NULL,
analit_id INT NOT NULL,
status INT NOT NULL DEFAULT 0,
created timestamp NOT NULL DEFAULT TIMESTAMP,
modified timestamp NOT NULL DEFAULT TIMESTAMP,
deleted timestamp NULL,
modifier VARCHAR(32) NOT NULL DEFAULT USER,
PRIMARY KEY(analit_devicetype),
CONSTRAINT unique_key
UNIQUE (device_type, analit_id),
FOREIGN KEY ref2test4devicetype (device_type, test_name)
REFERENCES LAB.test4devicetype (device_type, test_name),
FOREIGN KEY ref2devicetypes (device_type)
REFERENCES LAB.devicetypes (device_type),
FOREIGN KEY ref2analit (analit_id)
REFERENCES LAB.analits (analit_id)
)
///////////////////////////////////////////////////
// LAB.specify_analit4device
CREATE TABLE LAB.specify_analit4device
(
analit_device INT NOT NULL DEFAULT SERIAL(1),
analit_devicetype INT NOT NULL,
device_type VARCHAR(25) NOT NULL,
device_id INT NOT NULL,
test_name VARCHAR(25) NOT NULL,
test_code VARCHAR(25) NOT NULL,
status INT NOT NULL DEFAULT 0,
created timestamp NOT NULL DEFAULT TIMESTAMP,
modified timestamp NOT NULL DEFAULT TIMESTAMP,
deleted timestamp NULL,
modifier VARCHAR(32) NOT NULL DEFAULT USER,
PRIMARY KEY(analit_device),
FOREIGN KEY ref2devices (device_id)
REFERENCES LAB.devices (device_id),
FOREIGN KEY ref2analit4devicetype (analit_devicetype)
REFERENCES LAB.specify_analit4devicetype (analit_devicetype),
FOREIGN KEY ref2test4devicetype (device_type, test_name)
REFERENCES LAB.test4devicetype (device_type, test_name)
)
///////////////////////////////////////////////////
// CREATE DBPROC LAB.get_analit4testcode
CREATE DBPROC LAB.get_analit4testcode
( IN device_id INT
, IN test_code VARCHAR(25)
, OUT analit_id INT
, OUT analit_device INT
)
AS
BEGIN
TRY
select
b.analit_id, a.analit_device
from
LAB.specify_analit4device a,
LAB.specify_analit4devicetype b
where
a.analit_devicetype = b.analit_devicetype
and a.device_id = :device_id
and a.test_code = :test_code;
FETCH INTO :analit_id, :analit_device;
CATCH
IF $rc = 100 THEN STOP(5110, 'invalid argument: The test_code ''' ||
test_code || ''' not specified for the device_id ' || device_id)
ELSE STOP($rc, $errmsg);
END;
insert LAB.analits (analit_code, complex, analit_name, analit_desc)
values ('2JL2001', 0, 'bilorubin1', 'bilorubin in the ...')
insert LAB.analits (analit_code, complex, analit_name, analit_desc)
values ('2JL2002', 0, 'bilorubin2', 'bilorubin in the ...')
insert LAB.analits (analit_code, complex, analit_name, analit_desc)
values ('2JL2003', 0, 'bilorubin3', 'bilorubin in the ...')
insert LAB.analits (analit_code, complex, analit_name, analit_desc)
values ('2JL2004', 0, 'bilorubin4', 'bilorubin in the ...')
insert LAB.analits (analit_code, complex, analit_name, analit_desc)
values ('2JL2005', 0, 'trambositiii', 'trambositiii')
//
insert LAB.specify_analit4devicetype (device_type, analit_id, test_name)
values ('ELECSYS2010', 1, 'IGE')
insert LAB.specify_analit4devicetype (device_type, analit_id, test_name)
values ('ELECSYS2010', 2, 'TSH')
insert LAB.specify_analit4devicetype (device_type, analit_id, test_name)
values ('ELECSYS2010', 3, 'FT4')
insert LAB.specify_analit4devicetype (device_type, analit_id, test_name)
values ('ELECSYS2010', 4, 'PRL')
//
insert LAB.specify_analit4device (analit_devicetype, device_type, device_id,
test_name, test_code)
values (1, 'ELECSYS2010', 1, 'IGE', '63')
insert LAB.specify_analit4device (analit_devicetype, device_type, device_id,
test_name, test_code)
values (2, 'ELECSYS2010', 1, 'TSH', '1')
insert LAB.specify_analit4device (analit_devicetype, device_type, device_id,
test_name, test_code)
values (3, 'ELECSYS2010', 1, 'FT4', '3')
insert LAB.specify_analit4device (analit_devicetype, device_type, device_id,
test_name, test_code)
values (4, 'ELECSYS2010', 1, 'PRL', '13')
insert LAB.specify_analit4device (analit_devicetype, device_type, device_id,
test_name, test_code)
values (1, 'ELECSYS2010', 2, 'IGE', '63')
insert LAB.specify_analit4device (analit_devicetype, device_type, device_id,
test_name, test_code)
values (2, 'ELECSYS2010', 2, 'TSH', '1')
insert LAB.specify_analit4device (analit_devicetype, device_type, device_id,
test_name, test_code)
values (3, 'ELECSYS2010', 2, 'FT4', '3')
insert LAB.specify_analit4device (analit_devicetype, device_type, device_id,
test_name, test_code)
values (4, 'ELECSYS2010', 2, 'PRL', '13')
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
