14.7.11. crate_anon.preprocess.rio_view_func

crate_anon/preprocess/rio_view_func.py

Copyright (C) 2015, University of Cambridge, Department of Psychiatry. Created by Rudolf Cardinal (rnc1001@cam.ac.uk).

This file is part of CRATE.

CRATE is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.

CRATE is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with CRATE. If not, see <https://www.gnu.org/licenses/>.

Assistance functions for RiO view creation.

class crate_anon.preprocess.rio_view_func.RioViewConfigOptions(rio: bool, rcep: bool, cpft: bool, print_sql_only: bool, drop_not_create: bool, master_patient_table: str, full_prognotes_table: str, prognotes_current_only: bool = True, clindocs_current_only: bool = True, allergies_current_only: bool = True, audit_info: bool = False, postcodedb: str = '', geogcols: List[str] | None = None)[source]

Simple class to store some command-line options for RiO view creation

__init__(rio: bool, rcep: bool, cpft: bool, print_sql_only: bool, drop_not_create: bool, master_patient_table: str, full_prognotes_table: str, prognotes_current_only: bool = True, clindocs_current_only: bool = True, allergies_current_only: bool = True, audit_info: bool = False, postcodedb: str = '', geogcols: List[str] | None = None) → None[source]
Parameters:

  • rio – Treat the source database as a direct copy of RiO?

  • rcep – Treat the source database as the product of Servelec’s RiO CRIS Extract Program v2 (instead of raw RiO)?

  • cpft – Apply hacks for Cambridgeshire & Peterborough NHS Foundation Trust (CPFT) RCEP database?

  • print_sql_only – print SQL rather than executing it?

  • drop_not_create – REMOVES new columns/indexes, rather than creating them. (Not really very dangerous, but might take some time to recreate.)

  • prognotes_current_only – restrict Progress Note records to current versions only?

  • clindocs_current_only – restrict Clinical Document records to current versions only?

  • allergies_current_only – restrict Allergy records to current versions only?

  • audit_info – include audit trail information in the research database?

  • postcodedb – Specify database (schema) name for ONS Postcode Database (as imported by CRATE) to link in. With SQL Server, you will have to specify the schema as well as the database; e.g. ONS_PD.dbo".

  • geogcols – List of geographical information columns to link in from ONS Postcode Database. BEWARE that you do not specify anything too identifying.

crate_anon.preprocess.rio_view_func.add_index_only(viewmaker: ViewMaker, table: str, column_or_columns: str | Iterable[str]) → None[source]

Adds an index request to a viewmaker.

Parameters:

  • viewmaker – crate_anon.common.sql.ViewMaker; will be modified

  • table – table name

  • column_or_columns – column name (string or list of strings) to index within that table

crate_anon.preprocess.rio_view_func.lookup_from_fragment(lookup_table: str, aliased_lookup_table: str, lookup_pk: str, basetable: str, basecolumn: str) → str[source]

Returns LEFT JOIN SQL to implement a lookup from a system lookup table.

For when lookup_pk is really a PK.

Parameters:

  • lookup_table – name of the lookup table

  • aliased_lookup_table – alias to use (in SQL) for the lookup table

  • lookup_pk – PK in the lookup table

  • basetable – name of the base table from which to look up information

  • basecolumn – name of the column in the base table that maps to the PK in the lookup table

Returns:

an SQL string like LEFT JOIN lookuptable lkalias ON lkalias.pk = basetable.somecol

crate_anon.preprocess.rio_view_func.lookup_from_fragment_first_row(lookup_table: str, aliased_lookup_table: str, lookup_key: str, lookup_unique_field: str, basetable: str, basecolumn: str) → str[source]

Returns LEFT JOIN SQL to look up values from a lookup table that might give us multiple values and we only want the first. See below.

Parameters:

  • lookup_table – name of the lookup table

  • aliased_lookup_table – alias to use (in SQL) for the lookup table

  • lookup_key – a field we’ll match to basetable.basecolumn

  • lookup_unique_field – the field from which we want the first value

  • basetable – name of the base table from which to look up information

  • basecolumn – name of the column in the base table that maps to the PK in the lookup table

Returns:

an SQL string

Modified 2017-01-23, because sometimes the lookup column is not unique, e.g. lookup from “Code” to “CodeDescription” in NNNStatus (see also rio_views.py). The LEFT JOIN was giving us duplicate rows. We want only the first match. See https://www.periscopedata.com/blog/4-ways-to-join-only-the-first-row-in-sql.html

We were doing the FROM component as:

LEFT JOIN {lookup_table} {aliased_lookup_table}
    ON {aliased_lookup_table}.{lookup_pk} = {basetable}.{basecolumn}

and we’ll replace that with

LEFT JOIN {lookup_table} {aliased_lookup_table}
    ON {aliased_lookup_table}.{lookup_pk} = (
        SELECT {lookup_pk} FROM {lookup_table}
        WHERE {lookup_table}.{lookup_pk} = {basetable}.{basecolumn}
        ORDER BY {lookup_table}.{lookup_pk}
        LIMIT 1
    )

… compare to the example of

SELECT * FROM users
JOIN widgets ON widgets.id = (
    SELECT id FROM widgets
    WHERE widgets.user_id = users.id
    ORDER BY created_at DESC
    LIMIT 1
)

Note that SQL Server uses “SELECT TOP 1 …” not “SELECT … LIMIT 1”.

crate_anon.preprocess.rio_view_func.lookup_from_fragment_first_row_outer_apply(lookup_fields: Iterable[str], lookup_table: str, aliased_lookup_table: str, lookup_key: str, basetable: str, basecolumn: str) → str[source]

As for lookup_from_fragment_first_row() (q.v.), but works without a unique field in the lookup table.

Parameters:

  • lookup_fields – field(s) to look up the first values from

  • lookup_table – name of the lookup table

  • aliased_lookup_table – alias to use (in SQL) for the lookup table

  • lookup_key – a field we’ll match to basetable.basecolumn

  • basetable – name of the base table from which to look up information

  • basecolumn – name of the column in the base table that maps to the PK in the lookup table

Returns:

an SQL string

See

crate_anon.preprocess.rio_view_func.rio_add_audit_info(viewmaker: ViewMaker) → None[source]

Modifies a RiO view to add audit information.

Parameters:

viewmaker – crate_anon.common.sql.ViewMaker; will be modified

  • In RCEP: lots of tables have Created_Date, Updated_Date with no source column; likely from the audit table.

  • Here, we call them: Audit_Created_Date, Audit_Updated_Date

crate_anon.preprocess.rio_view_func.rio_add_bay_lookup(viewmaker: ViewMaker, basecolumn_ward: str, basecolumn_bay: str, column_prefix: str, internal_alias_prefix: str) → None[source]

Adds a ward bed-bay lookup.

Parameters:

  • viewmaker – crate_anon.common.sql.ViewMaker; will be modified

  • basecolumn_ward – ward ID column from the base table

  • basecolumn_bay – bay ID column from the base table

  • column_prefix – column prefix describing the kind of GP this is

  • internal_alias_prefix – prefix to add to the lookup table’s name to make it unique, in case the SELECT statement uses the same lookup table more than once

crate_anon.preprocess.rio_view_func.rio_add_carespell_lookup(viewmaker: ViewMaker, basecolumn: str, column_prefix: str | None = None, internal_alias_prefix: str | None = None) → None[source]

Adds a care spell lookup.

Parameters:

  • viewmaker – crate_anon.common.sql.ViewMaker; will be modified

  • basecolumn – ID column from the base table

  • column_prefix – column prefix describing the kind of care spell this is

  • internal_alias_prefix – prefix to add to the lookup table’s name to make it unique, in case the SELECT statement uses the same lookup table more than once

crate_anon.preprocess.rio_view_func.rio_add_consultant_lookup(viewmaker: ViewMaker, basecolumn: str, column_prefix: str | None = None, internal_alias_prefix: str | None = None) → None[source]

Adds a user lookup where that lookup is a hospital consultant. Compare rio_add_user_lookup().

Parameters:

  • viewmaker – crate_anon.common.sql.ViewMaker; will be modified

  • basecolumn – user ID column from the base table

  • column_prefix – column prefix describing the kind of user

  • internal_alias_prefix – prefix to add to the lookup table’s name to make it unique, in case the SELECT statement uses the same lookup table more than once

crate_anon.preprocess.rio_view_func.rio_add_diagnosis_lookup(viewmaker: ViewMaker, basecolumn_scheme: str, basecolumn_code: str, alias_scheme: str, alias_code: str, alias_description: str, internal_alias_prefix: str | None = None) → None[source]

Adds a diagnosis lookup.

Parameters:

  • viewmaker – crate_anon.common.sql.ViewMaker; will be modified

  • basecolumn_scheme – base table column giving the diagnostic scheme (e.g. a column containing “ICD-10”)

  • basecolumn_code – base table column containing the diagnostic code

  • alias_scheme – alias to be used for “diagnostic scheme”

  • alias_code – alias to be used for “diagnostic code”

  • alias_description – alias to be used for “description of diagnosis”

  • internal_alias_prefix – prefix to add to the lookup table’s name to make it unique, in case the SELECT statement uses the same lookup table more than once

crate_anon.preprocess.rio_view_func.rio_add_gp_lookup(viewmaker: ViewMaker, basecolumn: str, column_prefix: str, internal_alias_prefix: str) → None[source]

Adds a general practitioner (GP) lookup.

Parameters:

  • viewmaker – crate_anon.common.sql.ViewMaker; will be modified

  • basecolumn – ID column from the base table

  • column_prefix – column prefix describing the kind of GP this is

  • internal_alias_prefix – prefix to add to the lookup table’s name to make it unique, in case the SELECT statement uses the same lookup table more than once

crate_anon.preprocess.rio_view_func.rio_add_gp_lookup_with_practice(viewmaker: ViewMaker, basecolumn: str, column_prefix: str, internal_alias_prefix: str) → None[source]

Adds a GP-with-their-practice lookup.

Parameters:

  • viewmaker – crate_anon.common.sql.ViewMaker; will be modified

  • basecolumn – ID column from the base table

  • column_prefix – column prefix describing the kind of GP this is

  • internal_alias_prefix – prefix to add to the lookup table’s name to make it unique, in case the SELECT statement uses the same lookup table more than once

crate_anon.preprocess.rio_view_func.rio_add_gp_practice_lookup(viewmaker: ViewMaker, basecolumn: str, column_prefix: str, internal_alias_prefix: str) → None[source]

Adds a GP practice lookup.

Parameters:

  • viewmaker – crate_anon.common.sql.ViewMaker; will be modified

  • basecolumn – ID column from the base table

  • column_prefix – column prefix describing the kind of GP practice this is

  • internal_alias_prefix – prefix to add to the lookup table’s name to make it unique, in case the SELECT statement uses the same lookup table more than once

crate_anon.preprocess.rio_view_func.rio_add_ims_event_lookup(viewmaker: ViewMaker, basecolumn_event_num: str, column_prefix: str, internal_alias_prefix: str) → None[source]

Adds an IMS event lookup. (?)

Parameters:

  • viewmaker – crate_anon.common.sql.ViewMaker; will be modified

  • basecolumn_event_num – ID column from the base table

  • column_prefix – column prefix describing the kind of care spell this is

  • internal_alias_prefix – prefix to add to the lookup table’s name to make it unique, in case the SELECT statement uses the same lookup table more than once

crate_anon.preprocess.rio_view_func.rio_add_location_lookup(viewmaker: ViewMaker, basecolumn: str, column_prefix: str, internal_alias_prefix: str) → None[source]

Adds a location (institutional address) lookup.

Parameters:

  • viewmaker – crate_anon.common.sql.ViewMaker; will be modified

  • basecolumn – ID column from the base table

  • column_prefix – column prefix describing the kind of location this is

  • internal_alias_prefix – prefix to add to the lookup table’s name to make it unique, in case the SELECT statement uses the same lookup table more than once

crate_anon.preprocess.rio_view_func.rio_add_org_contact_lookup(viewmaker: ViewMaker, basecolumn: str, column_prefix: str, internal_alias_prefix: str) → None[source]

Adds an organisation lookup.

Parameters:

  • viewmaker – crate_anon.common.sql.ViewMaker; will be modified

  • basecolumn – ID column from the base table

  • column_prefix – column prefix describing the kind of organisation this is

  • internal_alias_prefix – prefix to add to the lookup table’s name to make it unique, in case the SELECT statement uses the same lookup table more than once

crate_anon.preprocess.rio_view_func.rio_add_team_lookup(viewmaker: ViewMaker, basecolumn: str, column_prefix: str | None = None, internal_alias_prefix: str | None = None) → None[source]

Adds a team lookup (from team ID to team details).

Parameters:

  • viewmaker – crate_anon.common.sql.ViewMaker; will be modified

  • basecolumn – team ID column from the base table

  • column_prefix – column prefix describing the kind of team

  • internal_alias_prefix – prefix to add to the lookup table’s name to make it unique, in case the SELECT statement uses the same lookup table more than once

crate_anon.preprocess.rio_view_func.rio_add_user_lookup(viewmaker: ViewMaker, basecolumn: str, column_prefix: str | None = None, internal_alias_prefix: str | None = None) → None[source]

Adds a user lookup. For example, RiO tables tend to have columns like “modified_by_user” with a cryptic ID; this function adds views so we can see who that was.

Parameters:

  • viewmaker – crate_anon.common.sql.ViewMaker; will be modified

  • basecolumn – user ID column from the base table

  • column_prefix – column prefix describing the kind of user

  • internal_alias_prefix – prefix to add to the lookup table’s name to make it unique, in case the SELECT statement uses the same lookup table more than once

NOT VERIFIED IN FULL - insufficient data with just top 1000 rows for each table (2016-07-12).

crate_anon.preprocess.rio_view_func.rio_amend_standard_noncore(viewmaker: ViewMaker) → None[source]

Modifies a standard RiO “non-core” table (a table defined by the using institution – e.g. CPFT “Core Assessment” tables…).

  • Adds a user lookup on type12_UpdatedBy.

  • Omits deleted records based on type12_DeletedDate.

Parameters:

viewmaker – crate_anon.common.sql.ViewMaker; will be modified

crate_anon.preprocess.rio_view_func.rio_noncore_yn(viewmaker: ViewMaker, basecolumn: str, result_alias: str) → None[source]

Modifies a standard RiO “non-core” table to map a field using “1 = yes, 2 = no” encoding to a more conventional Boolean (1 = yes, 0 = no).

Parameters:
crate_anon.preprocess.rio_view_func.simple_lookup_join(viewmaker: ViewMaker, basecolumn: str, lookup_table: str, lookup_pk: str, lookup_fields_aliases: Dict[str, str], internal_alias_prefix: str) → None[source]

Modifies the ViewMaker to add a simple lookup join.

Parameters:

  • viewmaker – crate_anon.common.sql.ViewMaker; will be modified

  • basecolumn – name of the column in the base table to look up from

  • lookup_table – name of the lookup table

  • lookup_pk – PK of the lookup table

  • lookup_fields_aliases – dictionary mapping lookup field names (in the lookup table) to aliases in the SELECT part of the SQL statement

  • internal_alias_prefix – prefix to add to the lookup table’s name to make it unique, in case the SELECT statement uses the same lookup table more than once

crate_anon.preprocess.rio_view_func.simple_view_expr(viewmaker: ViewMaker, expr: str, alias: str) → None[source]

Adds a simple SQL expression to a viewmaker.

Parameters:
crate_anon.preprocess.rio_view_func.simple_view_where(viewmaker: ViewMaker, where_clause: str, index_cols: Iterable[str] | None = None) → None[source]

Applies a simple WHERE clause to a viewmaker.

Parameters:

  • viewmaker – crate_anon.common.sql.ViewMaker; will be modified

  • where_clause – WHERE clause, e.g. somecol = 1

  • index_cols – names of columns that are part of the WHERE condition and would benefit from being indexed, for speed

crate_anon.preprocess.rio_view_func.standard_rio_code_lookup(viewmaker: ViewMaker, basecolumn: str, lookup_table: str, column_prefix: str, internal_alias_prefix: str) → None[source]

Implements a standard RiO lookup using a lookup table with Code / CodeDescription fields.

Parameters:

  • viewmaker – crate_anon.common.sql.ViewMaker; will be modified

  • basecolumn – name of the column in the base table to look up from

  • lookup_table – name of the lookup table

  • column_prefix – prefix for the view alias that describes the thing being looked up

  • internal_alias_prefix – prefix to add to the lookup table’s name to make it unique, in case the SELECT statement uses the same lookup table more than once

crate_anon.preprocess.rio_view_func.standard_rio_code_lookup_with_national_code(viewmaker: ViewMaker, basecolumn: str, lookup_table: str, column_prefix: str, internal_alias_prefix: str) → None[source]

Implements a standard RiO lookup using a lookup table with Code, CodeDescription, and NationalCode fields.

Parameters:

  • viewmaker – crate_anon.common.sql.ViewMaker; will be modified

  • basecolumn – name of the column in the base table to look up from

  • lookup_table – name of the lookup table

  • column_prefix – prefix for the view alias that describes the thing being looked up

  • internal_alias_prefix – prefix to add to the lookup table’s name to make it unique, in case the SELECT statement uses the same lookup table more than once

crate_anon.preprocess.rio_view_func.view_formatting_dict(viewmaker: ViewMaker) → Dict[str, str][source]

Produces a dictionary that can be used for automatically formatting templatized SQL.

Parameters:

viewmaker – crate_anon.common.sql.ViewMaker

Returns:

dictionary with the following keys:

Key

Meaning

basetable

Base table of the view

Return type:

dict

crate_anon.preprocess.rio_view_func.where_allergies_current(viewmaker: ViewMaker) → None[source]

Apply a WHERE clause restricting an allergies table to current versions of records only.

Parameters:

viewmaker – crate_anon.common.sql.ViewMaker; will be modified

crate_anon.preprocess.rio_view_func.where_clindocs_current(viewmaker: ViewMaker) → None[source]

Apply a WHERE clause restricting a clinical documents table to current versions of documents only.

Parameters:

viewmaker – crate_anon.common.sql.ViewMaker; will be modified

crate_anon.preprocess.rio_view_func.where_not_deleted_flag(viewmaker: ViewMaker, basecolumn: str) → None[source]

Apply a WHERE clause restricting a table to “non-deleted” records only.

Parameters:
crate_anon.preprocess.rio_view_func.where_prognotes_current(viewmaker: ViewMaker) → None[source]

Apply a WHERE clause restricting a progress notes table to current versions of progress notes only.

Parameters:

viewmaker – crate_anon.common.sql.ViewMaker; will be modified