Dumped on 2018-02-22

Index of database - gnumed_v20

Table: atc

holds ATC data

atc Structure
F-Key Name Type Description
pk_coding_system integer NOT NULL DEFAULT nextval('coding_system_root_pk_coding_system_seq'::regclass)
code text NOT NULL

holds the ATC code
term text NOT NULL

the name of the drug component
data_source.pk fk_data_source integer NOT NULL
comment text

a comment on this ATC
pk serial PRIMARY KEY
administration_route text

by what route this drug is to be given

Table atc Inherits coding_system_root,

idx_ref_atc_fk_data_src fk_data_source

Index - Schema ref

Table: atc_staging

used for importing ATC data

atc_staging Structure
F-Key Name Type Description
atc text
name text
ddd text
unit text
adro text
comment text

Index - Schema ref

Table: auto_hint

This table stores SQL queries and the associated hints. If the query returns TRUE the client should display the hint.

auto_hint Structure
F-Key Name Type Description
pk_audit integer NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass)
row_version integer NOT NULL
modified_when timestamp with time zone NOT NULL DEFAULT now()
modified_by name NOT NULL DEFAULT "current_user"()
pk serial PRIMARY KEY
query text UNIQUE

This query is run against the database.
title text UNIQUE

A short title to summarize and identify the hint.
hint text

When the query returns true this is the hint that should be displayed.
url text

An URL relevant to the hint.
is_active boolean NOT NULL DEFAULT true

Whether or not this query/hint is active.
source text

Who provided query and hint.
lang text

The language the hint is written in.

 

auto_hint Constraints
Name Constraint
ref_auto_hint_sane_hint CHECK ((gm.is_null_or_blank_string(hint) IS FALSE))
ref_auto_hint_sane_lang CHECK ((gm.is_null_or_blank_string(lang) IS FALSE))
ref_auto_hint_sane_query CHECK ((gm.is_null_or_blank_string(query) IS FALSE))
ref_auto_hint_sane_source CHECK ((gm.is_null_or_blank_string(source) IS FALSE))
ref_auto_hint_sane_title CHECK ((gm.is_null_or_blank_string(title) IS FALSE))
ref_auto_hint_sane_url CHECK (gm.is_null_or_non_empty_string(url))

Index - Schema ref

Table: billable

items that *can* be billed to patients

billable Structure
F-Key Name Type Description
pk_coding_system integer NOT NULL DEFAULT nextval('coding_system_root_pk_coding_system_seq'::regclass)
code text NOT NULL
term text NOT NULL
data_source.pk fk_data_source integer NOT NULL
comment text
pk serial PRIMARY KEY
amount numeric NOT NULL

How much to bill for this item.
currency text DEFAULT 'u20AC'::text

The currency .amount is in.
vat_multiplier numeric NOT NULL

Multiplier to apply to .amount to calculate VAT, eg 0.19 = 19%, 0 = no VAT
active boolean NOT NULL DEFAULT true

Whether this item is currently supposed to be used for billing patients.
discountable boolean NOT NULL DEFAULT false

Whether discounts can be applied to this item.

Table billable Inherits coding_system_root,

 

billable Constraints
Name Constraint
ref_billable_sane_amount CHECK ((amount >= (0)::numeric))
ref_billable_sane_currency CHECK ((gm.is_null_or_blank_string(currency) IS FALSE))
ref_billable_sane_vat_multiplier CHECK ((vat_multiplier >= (0)::numeric))
idx_ref_billable_fk_data_src fk_data_source

Index - Schema ref

Table: branded_drug

The medicine chest of this praxis. Stores brands of drugs patients have been taking regardless of whether that brand still exists or in fact ever existed as such (as in lifestyle thingies).

branded_drug Structure
F-Key Name Type Description
pk_audit integer NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass)
row_version integer NOT NULL
modified_when timestamp with time zone NOT NULL DEFAULT now()
modified_by name NOT NULL DEFAULT "current_user"()
pk serial PRIMARY KEY
description text NOT NULL

the name of this drug it is marketed under by the manufacturer
preparation text NOT NULL

the preparation the drug is delivered in, eg liquid, cream, tablet, etc.
atc_code text

the Anatomic Therapeutic Chemical code for this drug, used to compute possible substitutes
is_fake boolean NOT NULL DEFAULT false

Whether this truly is an actual brand of an actual drug rather than a fake brand created for documenting a, say, lifestyle nutrient or simply a component as opposed to a particular actual brand.
data_source.pk fk_data_source integer

the data source this entry came from
external_code text

an opaque code from an external data source, such as "PZN" in Germany
external_code_type text

an opaque code type from an external data source, such as "PZN" in Germany

 

branded_drug Constraints
Name Constraint
desc_not_empty CHECK ((gm.is_null_or_blank_string(description) IS FALSE))
drug_sane_external_code CHECK ((gm.is_null_or_non_empty_string(external_code) IS TRUE))
drug_sane_external_code_type CHECK ((((external_code IS NULL) AND (external_code_type IS NULL)) OR ((external_code IS NOT NULL) AND (external_code_type IS NOT NULL))))
prep_not_empty CHECK ((gm.is_null_or_blank_string(preparation) IS FALSE))
sane_atc CHECK ((gm.is_null_or_non_empty_string(atc_code) IS TRUE))

Tables referencing this one via Foreign Key Constraints:

idx_drug_data_source fk_data_source idx_drug_description description idx_drug_ext_code external_code

Index - Schema ref

Table: coda

Holds CodA/CodZ codes.

coda Structure
F-Key Name Type Description
pk_coding_system integer NOT NULL DEFAULT nextval('coding_system_root_pk_coding_system_seq'::regclass)
code text NOT NULL
term text NOT NULL

The Reason-For-Encounter like meaning of the code.
data_source.pk fk_data_source integer NOT NULL
comment text
pk serial PRIMARY KEY
icd10_text text

The text of the corresponding ICD-10 code.

Table coda Inherits coding_system_root,

 

coda Constraints
Name Constraint
chk_ref_coda_sane_icd10_text CHECK ((gm.is_null_or_blank_string(icd10_text) IS FALSE))
idx_ref_coda_fk_data_src fk_data_source

Index - Schema ref

Table: code_thesaurus_root

Synonyms for coded terms.

code_thesaurus_root Structure
F-Key Name Type Description
pk_thesaurus serial PRIMARY KEY
fk_code integer NOT NULL
synonym text

 

code_thesaurus_root Constraints
Name Constraint
ref_code_thes_root_sane_synonym CHECK ((gm.is_null_or_blank_string(synonym) IS FALSE))

Index - Schema ref

Table: coding_system_root

Base table for coding system tables providing common fields.

coding_system_root Structure
F-Key Name Type Description
pk_coding_system serial PRIMARY KEY
code text UNIQUE#1 NOT NULL
term text UNIQUE#2 NOT NULL
data_source.pk fk_data_source integer UNIQUE#1 UNIQUE#2 NOT NULL

links to the data source for the external reference data set
comment text

an arbitrary comment on the code and/or term, child tables will use this in different ways

Index - Schema ref

Table: consumable_substance

lists substances that are consumable by patients, whether or not linked to a branded drug

consumable_substance Structure
F-Key Name Type Description
pk_audit integer NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass)
row_version integer NOT NULL
modified_when timestamp with time zone NOT NULL DEFAULT now()
modified_by name NOT NULL DEFAULT "current_user"()
pk serial PRIMARY KEY
description text UNIQUE#1

The substance name.
atc_code text

(One) Anatomic Therapeutic Chemical code for this substance. Note that substances can have *several* ATC codes assigned to it by the WHO denoting different therapeutic uses and/or local application formulations. This code can *only* be used to *identify* the substance, not the use/application formulation thereof.
amount numeric UNIQUE#1 NOT NULL

The amount of substance.
unit text UNIQUE#1

The unit of the amount of substance.

 

consumable_substance Constraints
Name Constraint
ref_consumable_sane_amount CHECK ((amount >= (0)::numeric))
ref_consumable_sane_unit CHECK ((gm.is_null_or_blank_string(unit) IS FALSE))
ref_subst_sane_atc CHECK ((gm.is_null_or_non_empty_string(atc_code) IS TRUE))
ref_subst_sane_desc CHECK ((gm.is_null_or_blank_string(description) IS FALSE))

Tables referencing this one via Foreign Key Constraints:

Index - Schema ref

Table: data_source

lists the available coding systems, classifications, ontologies and term lists

data_source Structure
F-Key Name Type Description
pk_audit integer NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass)
row_version integer NOT NULL
modified_when timestamp with time zone NOT NULL DEFAULT now()
modified_by name NOT NULL DEFAULT "current_user"()
pk serial PRIMARY KEY
name_long text UNIQUE#1 NOT NULL

long, complete (, ?official) name for this reference entry
name_short text UNIQUE#2 NOT NULL

shorthand for referring to this reference entry
version text UNIQUE#1 UNIQUE#2 NOT NULL

the exact and non-ambigous version for this entry
description text

optional arbitrary description, should include external license
source text NOT NULL

non-ambigous description of source; with this info in hand it must be possible to locate a copy of the external data set
lang text

Tables referencing this one via Foreign Key Constraints:

idx_ref_data_source_lang lang

Index - Schema ref

Table: document_type

pre-installed document types, do not change these as they will be overwritten during database upgrades at the discretion of the GNUmed team

document_type Structure
F-Key Name Type Description
pk serial PRIMARY KEY
scope text

can be used to group document types according to applicability, say, per country
description text NOT NULL

Index - Schema ref

Table: form_types

types of forms which are available, generally by purpose (radiology, pathology, sick leave, Therapiebericht etc.)

form_types Structure
F-Key Name Type Description
name text UNIQUE NOT NULL
pk serial PRIMARY KEY

Tables referencing this one via Foreign Key Constraints:

Index - Schema ref

Table: icd10

Holds ICD-10 codes.

icd10 Structure
F-Key Name Type Description
pk_coding_system integer NOT NULL DEFAULT nextval('coding_system_root_pk_coding_system_seq'::regclass)
code text NOT NULL
term text NOT NULL
data_source.pk fk_data_source integer NOT NULL
comment text
pk serial PRIMARY KEY
star_code text

The star code which needs to be combined with the primary code to define the term.
aux_code text

The auxiliary code which needs to be combined with the primary code to define the term.

Table icd10 Inherits coding_system_root,

 

icd10 Constraints
Name Constraint
chk_ref_icd10_sane_aux_code CHECK ((gm.is_null_or_non_empty_string(aux_code) IS TRUE))
chk_ref_icd10_sane_star_code CHECK ((gm.is_null_or_non_empty_string(star_code) IS TRUE))
idx_ref_icd10_fk_data_src fk_data_source

Index - Schema ref

Table: icd9

Holds ICD-9 codes.

icd9 Structure
F-Key Name Type Description
pk_coding_system integer NOT NULL DEFAULT nextval('coding_system_root_pk_coding_system_seq'::regclass)
code text NOT NULL
term text NOT NULL
data_source.pk fk_data_source integer NOT NULL
comment text
pk serial PRIMARY KEY

Table icd9 Inherits coding_system_root,

idx_ref_icd9_fk_data_src fk_data_source

Index - Schema ref

Table: icpc

This table holds ICPC2 codes along with local extensions.

icpc Structure
F-Key Name Type Description
pk_coding_system integer NOT NULL DEFAULT nextval('coding_system_root_pk_coding_system_seq'::regclass)
code text UNIQUE#1 NOT NULL
term text NOT NULL
data_source.pk fk_data_source integer NOT NULL
comment text
pk serial PRIMARY KEY
code_extension text UNIQUE#1

An extension to the bare code as defined in, say, the Netherlands or Australia.
short_description text

A shorter term for this item
icd10 text[]

Array of corresponding ICD-10 codes.
criteria text

Criteria to guide in selection of the appropriate code.
inclusions text

Items included under this code.
exclusions text[]

Items NOT included under this code because there is another code for them.
see_also text[]

See also under these items.
icpc_component.component fk_component smallint NOT NULL
icpc_chapter.chapter fk_chapter character(1) NOT NULL

Table icpc Inherits coding_system_root,

 

icpc Constraints
Name Constraint
ref_icpc_sane_code_ext CHECK ((gm.is_null_or_non_empty_string(code_extension) IS TRUE))
ref_icpc_sane_criteria CHECK ((gm.is_null_or_non_empty_string(criteria) IS TRUE))
ref_icpc_sane_exclusions CHECK (((exclusions IS NULL) OR (array_length(exclusions, 1) > 0)))
ref_icpc_sane_icd10 CHECK (((icd10 IS NULL) OR (array_length(icd10, 1) > 0)))
ref_icpc_sane_inclusions CHECK ((gm.is_null_or_non_empty_string(inclusions) IS TRUE))
ref_icpc_sane_see_also CHECK (((see_also IS NULL) OR (array_length(see_also, 1) > 0)))
ref_icpc_sane_term CHECK ((gm.is_null_or_blank_string(term) IS FALSE))

Tables referencing this one via Foreign Key Constraints:

idx_ref_icpc_chapter fk_chapter idx_ref_icpc_code code idx_ref_icpc_component fk_component idx_ref_icpc_fk_data_src fk_data_source idx_ref_icpc_term term

Index - Schema ref

Table: icpc_chapter

The chapters of the ICPC.

icpc_chapter Structure
F-Key Name Type Description
pk serial PRIMARY KEY
chapter character(1) UNIQUE NOT NULL
description text UNIQUE

 

icpc_chapter Constraints
Name Constraint
ref_icpc_chapter_sane_desc CHECK ((gm.is_null_or_blank_string(description) IS FALSE))

Tables referencing this one via Foreign Key Constraints:

Index - Schema ref

Table: icpc_component

The Components of the ICPC chapters.

icpc_component Structure
F-Key Name Type Description
pk serial PRIMARY KEY
component smallint UNIQUE NOT NULL
description text UNIQUE
typical_soap_cat text[]

An array of SOAP categories which codes from this component are typically used for.

 

icpc_component Constraints
Name Constraint
ref_icpc_component_sane_desc CHECK ((gm.is_null_or_blank_string(description) IS FALSE))

Tables referencing this one via Foreign Key Constraints:

Index - Schema ref

Table: icpc_thesaurus

Synonyms for ICPC terms.

icpc_thesaurus Structure
F-Key Name Type Description
pk_thesaurus integer NOT NULL DEFAULT nextval('code_thesaurus_root_pk_thesaurus_seq'::regclass)
icpc.pk_coding_system fk_code integer NOT NULL
synonym text
pk serial PRIMARY KEY

Table icpc_thesaurus Inherits code_thesaurus_root,

 

icpc_thesaurus Constraints
Name Constraint
ref_code_thes_root_sane_synonym CHECK ((gm.is_null_or_blank_string(synonym) IS FALSE))
ref_icpc_thes_sane_synonym CHECK ((gm.is_null_or_blank_string(synonym) IS FALSE))

Index - Schema ref

Table: keyword_expansion

Arbitrary binary or textual snippets of data. Used as text macros or document "ribbons".

keyword_expansion Structure
F-Key Name Type Description
pk serial PRIMARY KEY
fk_staff integer UNIQUE#1

The provider this expansion applies to. If NULL: applies to all providers.
keyword text UNIQUE#1 NOT NULL

A keyword by which to uniquely identify this snippet. Can only exist once per provider.
textual_data text

This holds the text of non-binary snippets.
owner text NOT NULL DEFAULT "current_user"()

Who "owns" this text expansion.
encrypted boolean NOT NULL DEFAULT false

If true the snippet is encrypted with GnuPG.
binary_data bytea

This holds the binary data of non-textual snippets

 

keyword_expansion Constraints
Name Constraint
keyword_expansion_keyword_check CHECK ((btrim(keyword) <> ''::text))
ref_kwd_exp_binary_xor_textual CHECK ((((binary_data IS NULL) AND (textual_data IS NOT NULL)) OR ((binary_data IS NOT NULL) AND (textual_data IS NULL))))
ref_kwd_exp_sane_data CHECK (((binary_data IS NULL) OR (octet_length(binary_data) > 0)))
ref_kwd_exp_sane_keyword CHECK ((gm.is_null_or_blank_string(keyword) IS FALSE))
ref_kwd_exp_sane_text CHECK ((gm.is_null_or_non_empty_string(textual_data) IS TRUE))

Index - Schema ref

Table: lnk_substance2brand

This table links substances (INNs, mostly) as components into drugs.

lnk_substance2brand Structure
F-Key Name Type Description
pk_audit integer NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass)
row_version integer NOT NULL
modified_when timestamp with time zone NOT NULL DEFAULT now()
modified_by name NOT NULL DEFAULT "current_user"()
pk serial PRIMARY KEY
branded_drug.pk fk_brand integer UNIQUE#1 NOT NULL
consumable_substance.pk fk_substance integer UNIQUE#1 NOT NULL
idx_lnk_s2b_fk_brand fk_brand idx_lnk_s2b_fk_substance fk_substance

Index - Schema ref

Table: loinc

holds LOINC codes

loinc Structure
F-Key Name Type Description
pk_coding_system integer NOT NULL DEFAULT nextval('coding_system_root_pk_coding_system_seq'::regclass)
code text NOT NULL

holds LOINC_NUM
term text NOT NULL

holds either long_common_name or a ":".join of .component to .method_type
data_source.pk fk_data_source integer NOT NULL

points to a particular data source which in turn holds the version
comment text
pk serial PRIMARY KEY
component text
property text
time_aspect text
system text
scale_type text
method_type text
related_names_1_old text
grouping_class text
loinc_internal_source text
dt_last_change text
change_type text
answer_list text
code_status text
maps_to text
scope text
normal_range text
ipcc_units text
reference text
exact_component_synonym text
molar_mass text
grouping_class_type smallint
formula text
species text
example_answers text
acs_synonyms text
base_name text
final text
naa_ccr_id text
code_table text
is_set_root boolean
panel_elements text
survey_question_text text
survey_question_source text
units_required text
submitted_units text
related_names_2 text
short_name text
order_obs text
cdisc_common_tests text
hl7_field_subfield_id text
external_copyright_notice text
example_units text
inpc_percentage text
long_common_name text

Table loinc Inherits coding_system_root,

idx_ref_loinc_fk_data_src fk_data_source

Index - Schema ref

Table: ops

Holds OPS (German ICPM-CM) codes.

ops Structure
F-Key Name Type Description
pk_coding_system integer NOT NULL DEFAULT nextval('coding_system_root_pk_coding_system_seq'::regclass)
code text NOT NULL
term text NOT NULL
data_source.pk fk_data_source integer NOT NULL
comment text
pk serial PRIMARY KEY
second_code text
requires_laterality boolean

Table ops Inherits coding_system_root,

idx_ref_ops_fk_data_src fk_data_source

Index - Schema ref

Table: other_code

Holds codes from "other" coding systems for which no specific tables exist just yet.

other_code Structure
F-Key Name Type Description
pk_coding_system integer NOT NULL DEFAULT nextval('coding_system_root_pk_coding_system_seq'::regclass)
code text NOT NULL
term text NOT NULL
data_source.pk fk_data_source integer NOT NULL
comment text
pk serial PRIMARY KEY

Table other_code Inherits coding_system_root,

idx_ref_other_code_fk_data_src fk_data_source

Index - Schema ref

Table: papersizes

papersizes Structure
F-Key Name Type Description
pk serial PRIMARY KEY
name text UNIQUE NOT NULL
size point NOT NULL

(cm, cm)

Index - Schema ref

Table: paperwork_templates

form and letter template definitions

paperwork_templates Structure
F-Key Name Type Description
pk_audit integer NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass)
row_version integer NOT NULL
modified_when timestamp with time zone NOT NULL DEFAULT now()
modified_by name NOT NULL DEFAULT "current_user"()
pk serial PRIMARY KEY
form_types.pk fk_template_type integer NOT NULL
instance_type text

default document type to store documents generated from this form under, note that this may generate rows in blobs.doc_type if set to a non-existant document type
name_short text UNIQUE#2 NOT NULL

a short name for use in a GUI or some such
name_long text UNIQUE#1 UNIQUE#2 NOT NULL

a long name unambigously describing the form
external_version text UNIQUE#1 NOT NULL
gnumed_revision double precision UNIQUE#1

GnuMed internal form def version, may occur if we rolled out a faulty form def
engine text NOT NULL DEFAULT 'O'::text

the business layer forms engine used to process this form, currently: - T: plain text (generic postprocessing) - L: LaTeX - H: HTML - O: OpenOffice - I: image editor (visual progress notes) - G: gnuplot scripts (test results graphing) - P: PDF form (FDF based) - A: AbiWord - X: Xe(La)TeX - S: XSLT
in_use boolean NOT NULL DEFAULT true

whether this template is currently actively used in a given practice
filename text

the filename from when the template data was imported if applicable, used by some engines (such as OOo) to differentiate what to do with certain files, such as *.ott vs. *.ods, GNUmed uses it to derive a file extension when exporting the template data
data bytea

the template complete with placeholders in the format accepted by the engine defined in ref.paperwork_templates.engine
edit_after_substitution boolean NOT NULL DEFAULT true

Whether to offer last-minute, manual, generic editing inbetween placeholder substitution and final output generation.

 

paperwork_templates Constraints
Name Constraint
ref_templates_engine_range CHECK ((engine = ANY (ARRAY['T'::text, 'L'::text, 'H'::text, 'O'::text, 'I'::text, 'G'::text, 'P'::text, 'A'::text, 'X'::text, 'S'::text])))

Index - Schema ref

Table: tag_image

Text+image tags that can be applied to a person for characterization.

tag_image Structure
F-Key Name Type Description
pk_audit integer NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass)
row_version integer NOT NULL
modified_when timestamp with time zone NOT NULL DEFAULT now()
modified_by name NOT NULL DEFAULT "current_user"()
pk serial PRIMARY KEY
description text UNIQUE

A textual description of the meaning of the tag. Keep this reasonably short.
filename text

An example filename, mainly for preserving the file suffix. Set during import, suffix used during export.
image bytea NOT NULL

An image showing the meaning of the tag.

 

tag_image Constraints
Name Constraint
ref_tag_image_sane_desc CHECK ((gm.is_null_or_blank_string(description) IS FALSE))
ref_tag_image_sane_filename CHECK ((gm.is_null_or_non_empty_string(filename) IS TRUE))

Index - Schema ref

View: v_atc

v_atc Structure
F-Key Name Type Description
pk_atc integer
atc text
term text
administration_route text
comment text
is_group_code boolean
atc_level integer
name_long text
name_short text
version text
lang text
pk_coding_system integer
pk_data_source integer 
SELECT a.pk AS pk_atc
,
    a.code AS atc
,
    a.term
,
    a.administration_route
,
    a.comment
,
    
(octet_length
     (a.code) < 7
) AS is_group_code
,
    
(octet_length
     (a.code) - 
     (octet_length
           (a.code) / 3
     )
) AS atc_level
,
    rds.name_long
,
    rds.name_short
,
    rds.version
,
    rds.lang
,
    a.pk_coding_system
,
    a.fk_data_source AS pk_data_source
   
FROM (ref.atc a
     
  JOIN ref.data_source rds 
    ON (
           (rds.pk = a.fk_data_source)
     )
);

Index - Schema ref

View: v_auto_hints

v_auto_hints Structure
F-Key Name Type Description
pk_auto_hint integer
query text
title text
hint text
url text
is_active boolean
source text
lang text
rationale4suppression text
md5_sum text
xmin_auto_hint xid 
SELECT auto_hint.pk AS pk_auto_hint
,
    auto_hint.query
,
    auto_hint.title
,
    auto_hint.hint
,
    auto_hint.url
,
    auto_hint.is_active
,
    auto_hint.source
,
    auto_hint.lang
,
    NULL::text AS rationale4suppression
,
    md5
(
     (
           (
                 (COALESCE
                       (auto_hint.query
                             ,''::text
                       ) || COALESCE
                       (auto_hint.title
                             ,''::text
                       )
                 ) || COALESCE
                 (auto_hint.hint
                       ,''::text
                 )
           ) || COALESCE
           (auto_hint.url
                 ,''::text
           )
     )
) AS md5_sum
,
    auto_hint.xmin AS xmin_auto_hint
   
FROM ref.auto_hint;

Index - Schema ref

View: v_billables

v_billables Structure
F-Key Name Type Description
pk_billable integer
billable_code text
billable_description text
raw_amount numeric
amount_with_vat numeric
currency text
comment text
vat_multiplier numeric
active boolean
discountable boolean
catalog_long text
catalog_short text
catalog_version text
catalog_language text
pk_data_source integer
pk_coding_system_root integer
xmin_billable xid 
SELECT r_b.pk AS pk_billable
,
    r_b.code AS billable_code
,
    r_b.term AS billable_description
,
    r_b.amount AS raw_amount
,
    
(r_b.amount + 
     (r_b.amount * r_b.vat_multiplier)
) AS amount_with_vat
,
    r_b.currency
,
    r_b.comment
,
    r_b.vat_multiplier
,
    r_b.active
,
    r_b.discountable
,
    r_ds.name_long AS catalog_long
,
    r_ds.name_short AS catalog_short
,
    r_ds.version AS catalog_version
,
    r_ds.lang AS catalog_language
,
    r_b.fk_data_source AS pk_data_source
,
    r_b.pk_coding_system AS pk_coding_system_root
,
    r_b.xmin AS xmin_billable
   
FROM (ref.billable r_b
     
LEFT JOIN ref.data_source r_ds 
    ON (
           (r_b.fk_data_source = r_ds.pk)
     )
);

Index - Schema ref

View: v_branded_drugs

v_branded_drugs Structure
F-Key Name Type Description
pk_brand integer
brand text
preparation text
atc text
external_code text
external_code_type text
is_fake_brand boolean
components text[]
pk_components integer[]
pk_substances integer[]
pk_data_source integer
xmin_branded_drug xid 
SELECT r_bd.pk AS pk_brand
,
    r_bd.description AS brand
,
    r_bd.preparation
,
    r_bd.atc_code AS atc
,
    r_bd.external_code
,
    r_bd.external_code_type
,
    r_bd.is_fake AS is_fake_brand
,
    
(
SELECT array_agg
     (
           (
                 (
                       (
                             (
                                   (
                                         (r_cs.description || '::'::text) || r_cs.amount
                                   ) || '::'::text
                             ) || r_cs.unit
                       ) || '::'::text
                 ) || COALESCE
                 (r_cs.atc_code
                       ,''::text
                 )
           )
     ) AS array_agg
           
  FROM (ref.lnk_substance2brand r_ls2b
             
        JOIN ref.consumable_substance r_cs 
          ON (
                 (r_ls2b.fk_substance = r_cs.pk)
           )
     )
          
 WHERE (r_ls2b.fk_brand = r_bd.pk)
) AS components
,
    
(
SELECT array_agg
     (r_ls2b.pk) AS array_agg
           
  FROM ref.lnk_substance2brand r_ls2b
          
 WHERE (r_ls2b.fk_brand = r_bd.pk)
) AS pk_components
,
    
(
SELECT array_agg
     (r_ls2b.fk_substance) AS array_agg
           
  FROM ref.lnk_substance2brand r_ls2b
          
 WHERE (r_ls2b.fk_brand = r_bd.pk)
) AS pk_substances
,
    r_bd.fk_data_source AS pk_data_source
,
    r_bd.xmin AS xmin_branded_drug
   
FROM ref.branded_drug r_bd;

Index - Schema ref

View: v_coded_terms

This view aggregates all official (reference) terms, including "official" synonyms, for which a corresponding code is known to the system.

v_coded_terms Structure
F-Key Name Type Description
code text
term text
coding_system text
coding_system_long text
version text
lang text
pk_generic_code integer 
SELECT r_csr.code
,
    r_csr.term
,
    r_ds.name_short AS coding_system
,
    r_ds.name_long AS coding_system_long
,
    r_ds.version
,
    r_ds.lang
,
    r_csr.pk_coding_system AS pk_generic_code
   
FROM (ref.coding_system_root r_csr
     
  JOIN ref.data_source r_ds 
    ON (
           (r_csr.fk_data_source = r_ds.pk)
     )
)
UNION
 
SELECT ri.code
,
    r_it.synonym AS term
,
    r_ds.name_short AS coding_system
,
    r_ds.name_long AS coding_system_long
,
    r_ds.version
,
    r_ds.lang
,
    r_it.fk_code AS pk_generic_code
   
FROM (
     (ref.icpc_thesaurus r_it
     
   LEFT JOIN ref.icpc ri 
          ON (
                 (r_it.fk_code = ri.pk_coding_system)
           )
     )
     
LEFT JOIN ref.data_source r_ds 
    ON (
           (ri.fk_data_source = r_ds.pk)
     )
);

Index - Schema ref

View: v_drug_components

v_drug_components Structure
F-Key Name Type Description
pk_component integer
brand text
substance text
amount numeric
unit text
preparation text
atc_substance text
atc_brand text
external_code_brand text
external_code_type_brand text
is_fake_brand boolean
is_in_use boolean
pk_brand integer
pk_consumable_substance integer
pk_data_source integer
xmin_lnk_substance2brand xid 
SELECT r_ls2b.pk AS pk_component
,
    r_bd.description AS brand
,
    r_cs.description AS substance
,
    r_cs.amount
,
    r_cs.unit
,
    r_bd.preparation
,
    r_cs.atc_code AS atc_substance
,
    r_bd.atc_code AS atc_brand
,
    r_bd.external_code AS external_code_brand
,
    r_bd.external_code_type AS external_code_type_brand
,
    r_bd.is_fake AS is_fake_brand
,
    
(EXISTS 
     (
      SELECT 1
           
        FROM clin.substance_intake c_si
          
       WHERE (c_si.fk_drug_component = r_ls2b.pk)
         LIMIT 1
     )
) AS is_in_use
,
    r_ls2b.fk_brand AS pk_brand
,
    r_cs.pk AS pk_consumable_substance
,
    r_bd.fk_data_source AS pk_data_source
,
    r_ls2b.xmin AS xmin_lnk_substance2brand
   
FROM (
     (ref.consumable_substance r_cs
     
        JOIN ref.lnk_substance2brand r_ls2b 
          ON (
                 (r_cs.pk = r_ls2b.fk_substance)
           )
     )
     
LEFT JOIN ref.branded_drug r_bd 
    ON (
           (r_ls2b.fk_brand = r_bd.pk)
     )
);

Index - Schema ref

View: v_generic_codes

Denormalized generic codes.

v_generic_codes Structure
F-Key Name Type Description
pk_generic_code integer
code text
term text
name_long text
name_short text
version text
lang text
code_table regclass
pk_data_source integer 
SELECT r_csr.pk_coding_system AS pk_generic_code
,
    r_csr.code
,
    r_csr.term
,
    r_ds.name_long
,
    r_ds.name_short
,
    r_ds.version
,
    r_ds.lang
,
    
(r_csr.tableoid)::regclass AS code_table
,
    r_csr.fk_data_source AS pk_data_source
   
FROM (ref.coding_system_root r_csr
     
  JOIN ref.data_source r_ds 
    ON (
           (r_ds.pk = r_csr.fk_data_source)
     )
);

Index - Schema ref

View: v_icpc

View over denormalized ICPC2 data.

v_icpc Structure
F-Key Name Type Description
code text
code_extension text
extended_code text
term text
short_description text
code_chapter character(1)
chapter text
l10n_chapter text
code_component smallint
component text
l10n_component text
typical_soap_cat text[]
icd10 text[]
criteria text
inclusions text
exclusions text[]
see_also text[]
comment text
name_short text
name_long text
version text
lang text
pk_icpc integer
pk_data_source integer 
SELECT ri.code
,
    ri.code_extension
,
    
(ri.code || COALESCE
     (ri.code_extension
           ,''::text
     )
) AS extended_code
,
    ri.term
,
    ri.short_description
,
    ri.fk_chapter AS code_chapter
,
    rich.description AS chapter
,
    _
(rich.description) AS l10n_chapter
,
    ri.fk_component AS code_component
,
    rico.description AS component
,
    _
(rico.description) AS l10n_component
,
    rico.typical_soap_cat
,
    ri.icd10
,
    ri.criteria
,
    ri.inclusions
,
    ri.exclusions
,
    ri.see_also
,
    ri.comment
,
    rds.name_short
,
    rds.name_long
,
    rds.version
,
    rds.lang
,
    ri.pk AS pk_icpc
,
    ri.fk_data_source AS pk_data_source
   
FROM (
     (
           (ref.icpc ri
     
              JOIN ref.data_source rds 
                ON (
                       (ri.fk_data_source = rds.pk)
                 )
           )
     
        JOIN ref.icpc_chapter rich 
          ON (
                 (ri.fk_chapter = rich.chapter)
           )
     )
     
  JOIN ref.icpc_component rico 
    ON (
           (ri.fk_component = rico.component)
     )
);

Index - Schema ref

View: v_icpc_thesaurus

View over denormalized ICPC2 thesaurus.

v_icpc_thesaurus Structure
F-Key Name Type Description
pk_icpc_thesaurus integer
code text
term text
synonym text
pk_thesaurus integer
pk_coding_system integer
pk_icpc integer 
SELECT rit.pk AS pk_icpc_thesaurus
,
    ri.code
,
    ri.term
,
    rit.synonym
,
    rit.pk_thesaurus
,
    rit.fk_code AS pk_coding_system
,
    ri.pk AS pk_icpc
   
FROM (ref.icpc_thesaurus rit
     
  JOIN ref.icpc ri 
    ON (
           (rit.fk_code = ri.pk_coding_system)
     )
);

Index - Schema ref

View: v_keyword_expansions

Just a slightly more convenient view over expansions.

v_keyword_expansions Structure
F-Key Name Type Description
pk_expansion integer
pk_staff integer
keyword text
expansion text
is_encrypted boolean
is_textual boolean
data_size integer
public_expansion boolean
private_expansion boolean
owner text
xmin_expansion xid 
SELECT r_ke.pk AS pk_expansion
,
    r_ke.fk_staff AS pk_staff
,
    r_ke.keyword
,
    r_ke.textual_data AS expansion
,
    r_ke.encrypted AS is_encrypted
,
    
(r_ke.binary_data IS NULL) AS is_textual
,
    octet_length
(r_ke.binary_data) AS data_size
,
    
(r_ke.fk_staff IS NULL) AS public_expansion
,
    
(r_ke.fk_staff IS NOT NULL) AS private_expansion
,
    r_ke.owner
,
    r_ke.xmin AS xmin_expansion
   
FROM ref.keyword_expansion r_ke;

Index - Schema ref

View: v_paperwork_templates

v_paperwork_templates Structure
F-Key Name Type Description
pk_paperwork_template integer
name_short text
name_long text
external_version text
template_type text
l10n_template_type text
instance_type text
l10n_instance_type text
engine text
in_use boolean
edit_after_substitution boolean
filename text
has_template_data boolean
last_modified timestamp with time zone
modified_by text
pk_template_type integer
xmin_paperwork_template xid 
SELECT r_pt.pk AS pk_paperwork_template
,
    r_pt.name_short
,
    r_pt.name_long
,
    r_pt.external_version
,
    
(
SELECT form_types.name
           
  FROM ref.form_types
          
 WHERE (form_types.pk = r_pt.fk_template_type)
) AS template_type
,
    
(
SELECT _
     (form_types.name) AS _
           
  FROM ref.form_types
          
 WHERE (form_types.pk = r_pt.fk_template_type)
) AS l10n_template_type
,
    COALESCE
(r_pt.instance_type
     , (
      SELECT form_types.name
           
        FROM ref.form_types
          
       WHERE (form_types.pk = r_pt.fk_template_type)
     )
) AS instance_type
,
    COALESCE
(_
     (r_pt.instance_type)
     , (
      SELECT _
           (form_types.name) AS _
           
        FROM ref.form_types
          
       WHERE (form_types.pk = r_pt.fk_template_type)
     )
) AS l10n_instance_type
,
    r_pt.engine
,
    r_pt.in_use
,
    r_pt.edit_after_substitution
,
    r_pt.filename
,
        CASE
            WHEN 
(r_pt.data IS NOT NULL) THEN true
            ELSE false
        END AS has_template_data
,
    r_pt.modified_when AS last_modified
,
    COALESCE
(
     (
      SELECT staff.short_alias
           
        FROM dem.staff
          
       WHERE (staff.db_user = r_pt.modified_by)
     )
     , (
           ('<'::text || 
                 (r_pt.modified_by)::text
           ) || '>'::text
     )
) AS modified_by
,
    r_pt.fk_template_type AS pk_template_type
,
    r_pt.xmin AS xmin_paperwork_template
   
FROM ref.paperwork_templates r_pt;

Index - Schema ref

View: v_tag_images_no_data

v_tag_images_no_data Structure
F-Key Name Type Description
pk_tag_image integer
description text
l10n_description text
filename text
size integer
is_in_use boolean
xmin_tag_image xid 
SELECT rti.pk AS pk_tag_image
,
    rti.description
,
    _
(rti.description) AS l10n_description
,
    rti.filename
,
    octet_length
(COALESCE
     (rti.image
           ,'\x'::bytea
     )
) AS size
,
    
(EXISTS 
     (
      SELECT 1
           
        FROM dem.identity_tag dit
          
       WHERE (dit.fk_tag = rti.pk)
         LIMIT 1
     )
) AS is_in_use
,
    rti.xmin AS xmin_tag_image
   
FROM ref.tag_image rti;

Index - Schema ref

View: v_your_keyword_expansions

View over the text expansions relevant to the current user: a private expansion set up for the current user overrides a public expansion of the same keyword.

v_your_keyword_expansions Structure
F-Key Name Type Description
pk_expansion integer
pk_staff integer
keyword text
expansion text
is_encrypted boolean
is_textual boolean
data_size integer
public_expansion boolean
private_expansion boolean
owner text
xmin_expansion xid 
SELECT DISTINCT 
ON (union_result.keyword) union_result.pk_expansion
,
    union_result.pk_staff
,
    union_result.keyword
,
    union_result.expansion
,
    union_result.is_encrypted
,
    union_result.is_textual
,
    union_result.data_size
,
    union_result.public_expansion
,
    union_result.private_expansion
,
    union_result.owner
,
    union_result.xmin_expansion
   
FROM (
SELECT r_ke.pk AS pk_expansion
     ,
            r_ke.fk_staff AS pk_staff
     ,
            r_ke.keyword
     ,
            r_ke.textual_data AS expansion
     ,
            r_ke.encrypted AS is_encrypted
     ,
            
     (r_ke.binary_data IS NULL) AS is_textual
     ,
            octet_length
     (r_ke.binary_data) AS data_size
     ,
            false AS public_expansion
     ,
            true AS private_expansion
     ,
            r_ke.owner
     ,
            r_ke.xmin AS xmin_expansion
           
  FROM ref.keyword_expansion r_ke
          
 WHERE (r_ke.fk_staff = 
           (
            SELECT staff.pk
                   
              FROM dem.staff
                  
             WHERE (staff.db_user = "current_user"
                       ()
                 )
           )
     )
        
UNION ALL
         
SELECT r_ke.pk AS pk_expansion
     ,
            r_ke.fk_staff AS pk_staff
     ,
            r_ke.keyword
     ,
            r_ke.textual_data AS expansion
     ,
            r_ke.encrypted AS is_encrypted
     ,
            
     (r_ke.binary_data IS NULL) AS is_textual
     ,
            octet_length
     (r_ke.binary_data) AS data_size
     ,
            true AS public_expansion
     ,
            false AS private_expansion
     ,
            r_ke.owner
     ,
            r_ke.xmin AS xmin_expansion
           
  FROM ref.keyword_expansion r_ke
          
 WHERE (r_ke.fk_staff IS NULL)
  
ORDER BY 9 DESC
) union_result;

Index - Schema ref

Function: trf_announce_substance_in_brand_mod_no_pk()

Returns: trigger

Language: PLPGSQL

begin
	execute 'notify "substance_in_brand_mod_db:"';
	return NULL;
end;

Function: trf_consumable_subst_normalize_amount()

Returns: trigger

Language: PLPGSQL

On INSERT/UPDATE drop .000 all-zero fractions from amounts.

BEGIN
	NEW.amount := gm.strip_allzeros_fraction(NEW.amount);
	return NEW;
END;

Function: trf_del_ref_code_tbl_check_backlink()

Returns: trigger

Language: PLPGSQL

When deleting from any child of ref.coding_system_root check whether its row is being used in any clin.lnk_code2item_root child.

DECLARE
	_msg text;
BEGIN
	perform 1 from clin.lnk_code2item_root where fk_generic_code = NEW.pk_coding_system;
	if not FOUND then
		return OLD;
	end if;
	_msg := 'ref.trf_del_ref_code_tbl_check_backlink(): DELETE from '
		|| TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME || ': '
		|| 'pk_coding_system=(' || NEW.pk_coding_system || ') '
		|| 'in use in clin.lnk_code2item_root.fk_generic_code, '
		|| 'old pk_coding_system=(' || OLD.pk_coding_system || ')';
	raise foreign_key_violation using message = _msg;
	return OLD;
END;

Function: trf_do_not_update_component_if_taken_by_patient()

Returns: trigger

Language: PLPGSQL

If this drug component is taken by any patient do not modify it ( amount, unit, substance, brand).

BEGIN
	if OLD.fk_brand = NEW.fk_brand then
		if OLD.fk_substance = NEW.fk_substance then
			return NEW;
		end if;
	end if;
	perform 1 from clin.substance_intake c_si
	where c_si.fk_drug_component = OLD.pk
	limit 1;
	if NOT FOUND then
		return NEW;
	end if;
	raise exception '[ref.trf_do_not_update_component_if_taken_by_patient]: as long as drug component <%> is taken by a patient you cannot modify it', OLD.pk;
	return NEW;
END;

Function: trf_do_not_update_substance_if_taken_by_patient()

Returns: trigger

Language: PLPGSQL

If this substance is taken by any patient do not modify description, amount, or unit (case changes allowed).

DECLARE
	_msg text;
BEGIN
	-- allow for case insensitive non-changes
	if upper(OLD.description) = upper(NEW.description) then
		if OLD.amount = NEW.amount then
			if upper(OLD.unit) = upper(NEW.unit) then
				return NEW;
			end if;
		end if;
	end if;
	_msg := '[ref.trf_do_not_update_substance_if_taken_by_patient]: as long as substance <' || OLD.description || '> is taken by a patient you cannot modify it';
	perform 1 from clin.substance_intake c_si
	where c_si.fk_substance = OLD.pk
	limit 1;
	if FOUND then
		raise exception '%', _msg;
	end if;
	PERFORM 1
	FROM clin.substance_intake c_si
	WHERE c_si.fk_drug_component IN (
		-- get all PKs in component link table which
		-- represent the substance we want to modify
		SELECT
			r_ls2b.pk
		FROM
			ref.lnk_substance2brand r_ls2b
		WHERE
			r_ls2b.fk_substance = OLD.pk
	)
	LIMIT 1;
	if FOUND then
		raise exception '%', _msg;
	end if;
	return NEW;
END;

Function: trf_protect_template_data()

Returns: trigger

Language: PLPGSQL

Do not allow updates to the template data if any forms already use this template.

BEGIN
	if NEW.data != OLD.data then
		-- look for references in clin.form_instances
		-- if there are any we fail this update no matter what
		perform 1 from clin.form_instances where fk_form_def = NEW.pk;
		if FOUND then
			raise exception 'Updating ref.paperwork_templates.data not allowed because it is referenced from existing forms.';
		end if;
	end if;
	-- otherwise let it happen
	return NEW;
END;

Function: trf_true_brands_must_have_components()

Returns: trigger

Language: PLPGSQL

There must always be at least one component for any existing non-fake branded drug.

DECLARE
	_brand_is_deleted boolean;
	_is_fake_brand boolean;
	_has_other_components boolean;
BEGIN
	-- if an UPDATE does NOT move the component to another drug
	-- there WILL be at least one component left
	if TG_OP = 'UPDATE' then
		if NEW.fk_brand = OLD.fk_brand then
			return NEW;
		end if;
	end if;
	-- fake drugs may become devoid of components
	select
		is_fake into _is_fake_brand
	from
		ref.branded_drug
	where
		pk = OLD.fk_brand
	;
	if _is_fake_brand is TRUE then
		return OLD;
	end if;
	-- DELETEs may proceed if the drug has been deleted, too
	if TG_OP = 'DELETE' then
		select not exists (
			select 1 from ref.branded_drug
			where pk = OLD.fk_brand
		) into _brand_is_deleted;
		if _brand_is_deleted is TRUE then
			return OLD;
		end if;
	end if;
	-- if there are other components left after the
	-- UPDATE or DELETE everything is fine
	select exists (
		select 1 from ref.lnk_substance2brand
		where
			fk_brand = OLD.fk_brand
				and
			fk_substance != OLD.fk_substance
		limit 1
	) into _has_other_components;
	if _has_other_components is TRUE then
		return OLD;
	end if;
	raise exception '[ref.trf_true_brands_must_have_components::%] brand must have components (brand <%> component <%>)', TG_OP, OLD.fk_brand, OLD.fk_substance;
	return OLD;
END;

Function: trf_upd_ref_code_tbl_check_backlink()

Returns: trigger

Language: PLPGSQL

When updating any child of ref.coding_system_root check whether its row is being used in any clin.lnk_code2item_root child.

DECLARE
	_msg text;
BEGIN
	if NEW.pk_coding_system = OLD.pk_coding_system then
		return NEW;
	end if;
	perform 1 from clin.lnk_code2item_root where fk_generic_code = NEW.pk_coding_system;
	if not FOUND then
		return NEW;
	end if;
	_msg := 'ref.trf_upd_ref_code_tbl_check_backlink(): UPDATE of '
		|| TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME || ': '
		|| 'pk_coding_system=(' || NEW.pk_coding_system || ') '
		|| 'in use in clin.lnk_code2item_root.fk_generic_code, '
		|| 'old pk_coding_system=(' || OLD.pk_coding_system || ')';
	raise foreign_key_violation using message = _msg;
	return OLD;
END;

Generated by PostgreSQL Autodoc

W3C HTML 4.01 Strict