|
Page 2 of 4
The Internals
As a picture is worth a thousand words, this diagram shows the process:
(click in diagram to enlarge)
As the diagram shows, upon the user pressing 'submit' or 'save', the details around the Admission of a Patient, code named event 'A01', get mapped to records on the PatientEvent table. As the monitoring of the patient progresses, another clerk invokes another part of the GUI application to enter the details of ,say, the patient's Diagnosis.If a surgical procedure is also required, that's yet another entry point of information gathering taking place at some point in the future.Of course there's many other entry points but the picture of a patient's staying at the Hospital completes when a record is inserted into the PatientVisit table, the point where the information is deemed mature and ready for collection.
Practically when a record makes its way into the database, an Insert DML operation is taking place behind the scenes.That act can be intercepted by placing Rules, or Triggers.In our case, rule RU_insert_PatientVisit gets fired AFTER the row gets inserted into table PatientVisit.The rule extracts the values of PatientVisit's composite Primary Key, patient_id, event_type and event_id (the values are those prefixed with 'new.' ) off the record just entered and with those at hand calls the stored procedure DB_insert_PatientVisit filling its three parameters with the corresponding values :
create rule ru_insert_patientvisit
after insert onpatientvisit
execute procedure db_insert_patientvisit (
patient_id = new.patient_id,
event_type = new.event_type,
event_id = new.event_id
);
Rules are versatile beasts in that they can be fired whenever (that is before or after) a row is Inserted, Deleted or Updated, and can be even fine grained to look for updates on specific columns of specific tables.In turn they can call Stored Procedures passing on the data intercepted:
create procedure db_insert_patientvisit
(
patient_id = integer not null,
event_type = char(3) not null ,
event_id = integer not null
)
AS
DECLARE data VARCHAR(200) not NULL;
BEGIN
data=varchar(patient_id)+' '+event_type+
' '+varchar(event_id);
RAISE DBEVENT evn_insert_patientvisit data;
END;
Stored Procedures can also raise Database Events with this data.The DB Event as in the Observer pattern, is the way that the database lets externals entities know that something just went on, passing on the relevant information.In this case, that entity is the Perl daemon sitting quietly in the shadows, listening for an interesting event (EVN_insert_PatientVisit).
The data passed on from the DB_insert_PatientVisit procedure on to the EVN_insert_PatientVisit event, is a concatenation of the PatientVisit table's PK ( data=varchar(patient_id)+' '+event_type+' '+varchar(event_id)) since Ingres doesn't offer any other ways to pass on the event based data than to treat it as a single string limited to a length of 256 characters.
This PK data is strategic, as I'm going to use it to retrieve the necessary information from a number of tables hosting the same composite PK or a superset of it:
create table patientassignor(
universal_id varchar(20) not null not default,
namespace_id varchar(199) not null not default,
universal_id_type varchar(6) not null not default,
PRIMARY KEY ( universal_id )
with STRUCTURE=BTREE)
;
create table patientregistry(
patient_id integer not null not default,
ssn integer not null not default,
family_name varchar(50) not null not default,
given_name varchar(50) not null not default,
dob date not null default ' ',
sex char(1) not null not default,
phone_number varchar(50) not null not default,
assignors_id varchar(20) not null not default,
PRIMARY KEY (patient_id),
UNIQUE (ssn),
FOREIGN KEY (assignors_id)
REFERENCES patientassignor (universal_id)
with STRUCTURE=BTREE)
;
create table patientevent(
patient_id integer not null not default,
event_type char(3) not null not default, /*A01*/
event_id integer not null not default,
event_date_time timestamp(6) without time zone default
CURRENT_TIMESTAMP,
sending_org char(5) not null not default,
PRIMARY KEY (patient_id,event_type, event_id),
FOREIGN KEY ( patient_id)
REFERENCES patientregistry (patient_id )
with STRUCTURE=BTREE)
;
create table patientvisit(
patient_id integer not null not default,
event_type char(3) not null not default,
event_id integer not null not default,
patient_class char(1) not null not default,
attending_doctor_id integer not null not default,
referring_doctor_id integer not null not default,
hospital_service char(1) not null not default,
admit_source smallint not null not default,
total_charges money not null default 0,
total_adjustments money not null default 0,
total_payments money not null default 0,
PRIMARY KEY (patient_id,event_type, event_id),
FOREIGN KEY (patient_id,event_type, event_id)
REFERENCES patientevent (patient_id ,event_type, event_id)
with STRUCTURE=BTREE)
;
create table patientdiagnosis(
patient_id integer not null not default,
event_type char(3) not null not default,
event_id integer not null not default,
diagnosis_id char(5) not null not default,
diagnosing_clinician_id integer not null not default,
PRIMARY KEY ( patient_id,event_type, event_id,diagnosis_id),
FOREIGN KEY (patient_id,event_type, event_id)
REFERENCES patientevent (patient_id ,event_type, event_id)
with STRUCTURE=BTREE)
;
Behind the scenes when at different points in time clerks save the data from within the GUI, the following Inserts take place inside the database:
insert into PatientAssignor
(universal_id,namespace_id,universal_id_type)
values
('2.16.840.1.113883.19.3','NationalPN','ISO');
insert into PatientRegistry
(patient_id,ssn,family_name,given_name,dob,sex,
phone_number,assignors_id)
values
(100660325,1234567890,'GREENING','WAYNE',
'30/10/1961','M','+44213445',
'2.16.840.1.113883.19.3');
insert into PatientEvent
(patient_id,event_type,event_id,event_date_time,sending_org)
values
(100660325,'A01',201,'2018-12-11 08:14:01','DD015');
insert into PatientDiagnosis
(patient_id,event_type,event_id,
diagnosis_id,diagnosing_clinician_id)
values
(100660325,'A01',201,'S42.1',120002);
insert into PatientDiagnosis
(patient_id,event_type,event_id,
diagnosis_id,diagnosing_clinician_id)
values
(100660325,'A01',201,'S42.2',120002);
But the ru_insert_patientvisit rule is activated only when an Insert into table PatientVisit happens:
insert into PatientVisit
(patient_id,event_type,event_id,patient_class,
attending_doctor_id,referring_doctor_id,
hospital_service,admit_source,
total_charges,total_adjustments,total_payments)
values
(100660325,'A01',201,'E',
120002,145601,'MED',2,
1200.44,200.44,1000);
The rule calls the procedure and the procedure raises then event which is yet again forwarding the PK's values but this time to the Perl daemon, breaking away from the database's tight confinements.
The daemon listens in the background and can be initialized as a Cron job:
01 7 * * * nohup perl patient_event_gateway.pl >
Logs/patient_event_gateway.log 2>&1 &
Anyhow, after it receives the PK data, it splits it, and from the type of the event (event_type) decides which Perl program to call next, forwarding the PK as a three part argument to it.
|