See attached files.
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
-- =============================================
-- GNUmed - tracking of reviewed status of incoming data
-- =============================================
-- $Source: /cvsroot/gnumed/gnumed/gnumed/server/sql/gmSchemaRevision.sql,v $
-- $Id: gmSchemaRevision.sql,v 1.15 2005/09/19 16:22:12 ncq Exp $
-- license: GPL
-- author: [EMAIL PROTECTED]
-- =============================================
-- force terminate + exit(3) on errors if non-interactive
\set ON_ERROR_STOP 1
-- ---------------------------------------------
create table review_root (
pk serial primary key,
fk_reviewed_row integer
not null,
fk_reviewer integer
not null
references public.xlnk_identity(xfk_identity),
is_technically_abnormal boolean
not null,
clinically_relevant boolean
not null,
comment text
default null,
unique (fk_reviewed_row, fk_reviewer)
) inherits (audit_fields);
-- ---------------------------------------------
create table reviewed_test_results (
primary key (pk),
foreign key (fk_reviewed_row) references test_result(pk),
unique (fk_reviewed_row, fk_reviewer)
) inherits (review_root);
create table reviewed_doc_objs (
primary key (pk),
foreign key (fk_reviewed_row) references blobs.doc_obj(id),
unique (fk_reviewed_row, fk_reviewer)
) inherits (review_root);
-- =============================================
-- do simple schema revision tracking
select log_script_insertion('$RCSfile: gmWaitingList.sql,v $', '$Revision: 1.1
$');
-- =============================================
-- $Log: gmWaitingList.sql,v $
--
-- =============================================
-- GNUmed - tracking of reviewed status of incoming data
-- =============================================
-- $Source: /cvsroot/gnumed/gnumed/gnumed/server/sql/gmSchemaRevision.sql,v $
-- $Id: gmSchemaRevision.sql,v 1.15 2005/09/19 16:22:12 ncq Exp $
-- license: GPL
-- author: [EMAIL PROTECTED]
-- =============================================
-- force terminate + exit(3) on errors if non-interactive
\set ON_ERROR_STOP 1
-- ---------------------------------------------
-- review_root
comment on table review_root is
'this table tracks whether a particular clinical item
was reviewed by a clinician or not';
comment on column review_root.fk_reviewed_row is
'the row the review status is for: to be qualified
as a proper foreign key in child tables';
comment on column review_root.fk_reviewer is
'who has reviewed the item';
comment on column review_root.is_technically_abnormal is
'whether test provider flagged this result as abnormal,
*not* a clinical assessment but rather a technical one
LDT: exist(8422)';
comment on column review_root.clinically_relevant is
'whether this result is considered relevant clinically,
need not correspond to the value of "techically_abnormal"
since abnormal values may be irrelevant while normal
ones can be of significance';
-- rules !
-- ---------------------------------------------
-- review root child tables
comment on table reviewed_test_results is
'review table for test results';
comment on table reviewed_doc_objs is
'review table for documents - per page !';
-- ---------------------------------------------
\unset ON_ERROR_STOP
drop view v_reviewed_items cascade;
\set ON_ERROR_STOP 1
create view v_reviewed_items as
select
rr.pk as pk_review_root,
rr.fk_reviewed_row as pk_reviewed_row,
rr.fk_reviewer as pk_reviewer,
rr.is_technically_abnormal as is_technically_abnormal,
rr.clinically_relevant as clinically_relevant,
case when ((select 1 from v_staff where pk_identity = rr.fk_reviewer)
is null)
then '<' || rr.fk_reviewer || '>'
else (select sign from v_staff where pk_identity =
rr.fk_reviewer)
end as reviewer,
(select relname
from pg_class
where pg_class.oid = rr.tableoid
) as src_table
from
review_root rr
;
-- =============================================
grant SELECT, UPDATE, INSERT, DELETE on
review_root
, review_root_pk_seq
, reviewed_test_results
, reviewed_doc_objs
to group "gm-doctors";
grant select on
v_reviewed_items
to group "gm-doctors";
-- =============================================
-- do simple schema revision tracking
select log_script_insertion('$RCSfile: gmWaitingList.sql,v $', '$Revision: 1.1
$');
-- =============================================
-- $Log: gmWaitingList.sql,v $
--
_______________________________________________
Gnumed-devel mailing list
[email protected]
http://lists.gnu.org/mailman/listinfo/gnumed-devel
