14.2.98. camcops_server.cc_modules.cc_sqla_coltypes

camcops_server/cc_modules/cc_sqla_coltypes.py


Copyright (C) 2012-2019 Rudolf Cardinal (rudolf@pobox.com).

This file is part of CamCOPS.

CamCOPS 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.

CamCOPS 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 CamCOPS. If not, see <http://www.gnu.org/licenses/>.


SQLAlchemy column types used by CamCOPS.

Note these built-in SQLAlchemy types (http://docs.sqlalchemy.org/en/latest/core/type_basics.html#generic-types):

SQLAlchemy type Comment
BigInteger MySQL: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (64-bit) (compare NHS number: up to 9,999,999,999)
Boolean  
Date  
DateTime  
Enum  
Float  
Integer MySQL: -2,147,483,648 to 2,147,483,647 (32-bit)
Interval For datetime.timedelta
LargeBinary Under MySQL, maps to BLOB
MatchType For the return type of the MATCH operator
Numeric For fixed-precision numbers like NUMERIC or DECIMAL
PickleType  
SchemaType  
SmallInteger  
String VARCHAR
Text Variably sized string type. (Under MySQL, renders as TEXT.)
Time  
Unicode Implies that the underlying column explicitly supports Unicode
UnicodeText Variably sized version of Unicode (Under MySQL, renders as TEXT too.)

Not supported across all platforms:

SQL type Comment
BIGINT UNSIGNED MySQL: 0 to 18,446,744,073,709,551,615 (64-bit). Use sqlalchemy.dialects.mysql.BIGINT(unsigned=True).
INT UNSIGNED MySQL: 0 to 4,294,967,295 (32-bit). Use sqlalchemy.dialects.mysql.INTEGER(unsigned=True).

Other MySQL sizes:

MySQL type Comment
TINYBLOB 2^8 bytes = 256 bytes
BLOB 2^16 bytes = 64 KiB
MEDIUMBLOB 2^24 bytes = 16 MiB
LONGBLOB 2^32 bytes = 4 GiB
TINYTEXT 255 (2^8 - 1) bytes
TEXT 65,535 bytes (2^16 - 1) = 64 KiB
MEDIUMTEXT 16,777,215 (2^24 - 1) bytes = 16 MiB
LONGTEXT 4,294,967,295 (2^32 - 1) bytes = 4 GiB

See https://stackoverflow.com/questions/13932750/tinytext-text-mediumtext-and-longtext-maximum-storage-sizes.

Also notes:

camcops_server.cc_modules.cc_sqla_coltypes.AUDIT_SOURCE_MAX_LEN = 20

our choice based on use in CamCOPS code

class camcops_server.cc_modules.cc_sqla_coltypes.BoolColumn(*args, **kwargs)[source]

A camcops_server.cc_modules.cc_sqla_coltypes.CamcopsColumn representing a boolean value.

camcops_server.cc_modules.cc_sqla_coltypes.CHARSET_MAX_LEN = 64

See https – //docs.python.org/3.7/library/codecs.html#standard-encodings. Probably ~18 so give it some headroom.

camcops_server.cc_modules.cc_sqla_coltypes.CURRENCY_MAX_LEN = 3

Can have Unicode symbols like € or text like “GBP”

class camcops_server.cc_modules.cc_sqla_coltypes.CamcopsColumn(*args, include_in_anon_staging_db: bool = False, exempt_from_anonymisation: bool = False, identifies_patient: bool = False, is_blob_id_field: bool = False, blob_relationship_attr_name: str = '', permitted_value_checker: camcops_server.cc_modules.cc_sqla_coltypes.PermittedValueChecker = None, **kwargs)[source]

A SQLAlchemy Column class that supports some CamCOPS-specific flags, such as:

  • whether a field is a BLOB reference;
  • how it should be treated for anonymisation;
  • which values are permitted in the field (in a soft sense: duff values cause errors to be reported, but they’re still stored).
Parameters:
  • *args – Arguments to the Column constructor.
  • include_in_anon_staging_db – Ensure this is marked for inclusion in data dictionaries for an anonymisation staging database.
  • exempt_from_anonymisation – If true: though this field might be text, it is guaranteed not to contain identifiers (e.g. it might contain only predefined disease severity descriptions) and does not require anonymisation.
  • identifies_patient – If true: contains a patient identifier (e.g. name).
  • is_blob_id_field – If true: this field contains a reference (client FK) to the BLOB table.
  • blob_relationship_attr_name – For BLOB ID fields: the name of the associated relationship attribute (which, when accessed, yields the BLOB itself) in the owning class/object.
  • permitted_value_checker – If specified, a PermittedValueChecker that allows soft constraints to be specified on the field’s contents. (That is, no constraints are specified at the database level, but we can moan if incorrect data are present.)
  • **kwargs – Arguments to the Column constructor.
set_permitted_value_checker(permitted_value_checker: camcops_server.cc_modules.cc_sqla_coltypes.PermittedValueChecker) → None[source]

Sets the PermittedValueChecker attribute.

camcops_server.cc_modules.cc_sqla_coltypes.DATABASE_TITLE_MAX_LEN = 255

our choice

camcops_server.cc_modules.cc_sqla_coltypes.DEVICE_NAME_MAX_LEN = 191

191 is the maximum for MySQL + InnoDB + VARCHAR + utf8mb4 + index; must be compatible with tablet

camcops_server.cc_modules.cc_sqla_coltypes.EMAIL_ADDRESS_MAX_LEN = 255

See https – //en.wikipedia.org/wiki/Email_address.

camcops_server.cc_modules.cc_sqla_coltypes.EXPORT_RECIPIENT_NAME_MAX_LEN = 191

191 is the maximum for MySQL + InnoDB + VARCHAR + utf8mb4 + index

camcops_server.cc_modules.cc_sqla_coltypes.FILESPEC_MAX_LEN = 255

Our choice

camcops_server.cc_modules.cc_sqla_coltypes.FILTER_TEXT_MAX_LEN = 255

Our choice

camcops_server.cc_modules.cc_sqla_coltypes.FULLNAME_MAX_LEN = 255

Our choice; used for user full names on the server

camcops_server.cc_modules.cc_sqla_coltypes.GROUP_DESCRIPTION_MAX_LEN = 255

Our choice

camcops_server.cc_modules.cc_sqla_coltypes.GROUP_NAME_MAX_LEN = 191

191 is the maximum for MySQL + InnoDB + VARCHAR + utf8mb4 + index

camcops_server.cc_modules.cc_sqla_coltypes.HASHED_PW_MAX_LEN = 60

We use bcrypt. Empirically, the length of its hashed output is –

   "$2a$" (4)
   cost parameter, e.g. "$09" for 9 rounds (3)
   b64-enc 128-bit salt (22)
   b64enc 184-bit hash (31)

... total 60

See https://stackoverflow.com/questions/5881169/what-column-type-length-should-i-use-for-storing-a-bcrypt-hashed-password-in-a-d

camcops_server.cc_modules.cc_sqla_coltypes.HL7_AA_MAX_LEN = 20
  • The AA appears in Table 4.6 “Extended composite ID”, p46-47 of hl7guide-1-4-2012-08.pdf
  • … but is defined in Table 4.9 “Entity Identifier”, p50, in which:
    • component 2 is the Assigning Authority (see component 1)
    • component 2 is also a Namespace ID with a length of 20
  • … and multiple other examples of an Assigning Authority being one example of a Namespace ID
  • … and examples are in Table 0363 (p229 of the PDF), which are all 3-char.
  • … and several other examples of “Namespace ID” being of length 1..20 meaning 1-20.
camcops_server.cc_modules.cc_sqla_coltypes.HL7_ID_TYPE_MAX_LEN = 5

Table 4.6 “Extended composite ID”, p46-47 of hl7guide-1-4-2012-08.pdf, and Table 0203 “Identifier type”, p204 of that PDF, in Appendix B.

camcops_server.cc_modules.cc_sqla_coltypes.HOSTNAME_MAX_LEN = 255

FQDN; see https://stackoverflow.com/questions/8724954/what-is-the-maximum-number-of-characters-for-a-host-name-in-unix

camcops_server.cc_modules.cc_sqla_coltypes.ICD10_CODE_MAX_LEN = 7

longest is e.g. “F00.000”; “F10.202”; thus, 7

camcops_server.cc_modules.cc_sqla_coltypes.ICD9_CODE_MAX_LEN = 6

Longest is “xxx.xx”; thus, 6; see https://www.cms.gov/Medicare/Quality-Initiatives-Patient-Assessment-Instruments/HospitalQualityInits/Downloads/HospitalAppendix_F.pdf

camcops_server.cc_modules.cc_sqla_coltypes.ID_DESCRIPTOR_MAX_LEN = 255

Our choice

camcops_server.cc_modules.cc_sqla_coltypes.ID_POLICY_MAX_LEN = 255

Our choice

camcops_server.cc_modules.cc_sqla_coltypes.IP_ADDRESS_MAX_LEN = 45

See http – //stackoverflow.com/questions/166132

camcops_server.cc_modules.cc_sqla_coltypes.ISO8601_DATETIME_STRING_MAX_LEN = 32

Max length e.g.

2013-07-24T20:04:07.123456+01:00
1234567890123456789012345678901234567890

(with punctuation, T, microseconds, colon in timezone).

camcops_server.cc_modules.cc_sqla_coltypes.ISO8601_DURATION_STRING_MAX_LEN = 29

See cardinal_pythonlib.datetimefunc.duration_to_iso()

class camcops_server.cc_modules.cc_sqla_coltypes.IdNumReferenceListColType(*args, **kwargs)[source]

Stores a list of IdNumReference objects. On the database side, uses a comma-separated list of integers.

Construct a TypeDecorator.

Arguments sent here are passed to the constructor of the class assigned to the impl class level attribute, assuming the impl is a callable, and the resulting object is assigned to the self.impl instance attribute (thus overriding the class attribute of the same name).

If the class level impl is not a callable (the unusual case), it will be assigned to the same instance attribute ‘as-is’, ignoring those arguments passed to the constructor.

Subclasses can override this to customize the generation of self.impl entirely.

process_bind_param(value: Union[typing.List[camcops_server.cc_modules.cc_simpleobjects.IdNumReference], NoneType], dialect: sqlalchemy.engine.interfaces.Dialect) → str[source]

Convert parameters on the way from Python to the database.

process_literal_param(value: Union[typing.List[camcops_server.cc_modules.cc_simpleobjects.IdNumReference], NoneType], dialect: sqlalchemy.engine.interfaces.Dialect) → str[source]

Convert literals on the way from Python to the database.

process_result_value(value: Union[str, NoneType], dialect: sqlalchemy.engine.interfaces.Dialect) → List[camcops_server.cc_modules.cc_simpleobjects.IdNumReference][source]

Convert things on the way from the database to Python.

python_type

The Python type of the object.

camcops_server.cc_modules.cc_sqla_coltypes.MIMETYPE_MAX_LEN = 255

See https – //stackoverflow.com/questions/643690

camcops_server.cc_modules.cc_sqla_coltypes.PATIENT_NAME_MAX_LEN = 255

For forename and surname, each; our choice but must match tablet

class camcops_server.cc_modules.cc_sqla_coltypes.PendulumDateTimeAsIsoTextColType(*args, **kwargs)[source]

Stores date/time values as ISO-8601, in a specific format. Uses Pendulum on the Python side.

Construct a TypeDecorator.

Arguments sent here are passed to the constructor of the class assigned to the impl class level attribute, assuming the impl is a callable, and the resulting object is assigned to the self.impl instance attribute (thus overriding the class attribute of the same name).

If the class level impl is not a callable (the unusual case), it will be assigned to the same instance attribute ‘as-is’, ignoring those arguments passed to the constructor.

Subclasses can override this to customize the generation of self.impl entirely.

class comparator_factory(expr)[source]

Process SQL for when we are comparing our column, in the database, to something else.

We make this dialect-independent by calling functions like

unknown_field_to_utcdatetime
isotzdatetime_to_utcdatetime

… which we then specialize for specific dialects.

This function itself does not appear to be able to access any information about the dialect.

operate(op, *other, **kwargs)[source]

Operate on an argument.

This is the lowest level of operation, raises NotImplementedError by default.

Overriding this on a subclass can allow common behavior to be applied to all operations. For example, overriding ColumnOperators to apply func.lower() to the left and right side:

class MyComparator(ColumnOperators):
    def operate(self, op, other):
        return op(func.lower(self), func.lower(other))
Parameters:
  • op – Operator callable.
  • *other – the ‘other’ side of the operation. Will be a single scalar for most operations.
  • **kwargs – modifiers. These may be passed by special operators such as ColumnOperators.contains().
reverse_operate(op, *other, **kwargs)[source]

Reverse operate on an argument.

Usage is the same as operate().

static isostring_to_pendulum(x: Union[str, NoneType]) → Union[pendulum.datetime.DateTime, NoneType][source]

From an ISO-formatted string to a Python Pendulum, with timezone.

static pendulum_to_isostring(x: Union[NoneType, datetime.date, str, arrow.arrow.Arrow]) → Union[str, NoneType][source]

From a Python datetime to an ISO-formatted string in our particular format.

process_bind_param(value: Union[pendulum.datetime.DateTime, NoneType], dialect: sqlalchemy.engine.interfaces.Dialect) → Union[str, NoneType][source]

Convert parameters on the way from Python to the database.

process_literal_param(value: Union[pendulum.datetime.DateTime, NoneType], dialect: sqlalchemy.engine.interfaces.Dialect) → Union[str, NoneType][source]

Convert literals on the way from Python to the database.

process_result_value(value: Union[str, NoneType], dialect: sqlalchemy.engine.interfaces.Dialect) → Union[pendulum.datetime.DateTime, NoneType][source]

Convert things on the way from the database to Python.

python_type

The Python type of the object.

class camcops_server.cc_modules.cc_sqla_coltypes.PendulumDurationAsIsoTextColType(*args, **kwargs)[source]

Stores time durations as ISO-8601, in a specific format. Uses pendulum.Duration on the Python side.

Construct a TypeDecorator.

Arguments sent here are passed to the constructor of the class assigned to the impl class level attribute, assuming the impl is a callable, and the resulting object is assigned to the self.impl instance attribute (thus overriding the class attribute of the same name).

If the class level impl is not a callable (the unusual case), it will be assigned to the same instance attribute ‘as-is’, ignoring those arguments passed to the constructor.

Subclasses can override this to customize the generation of self.impl entirely.

static isostring_to_pendulum_duration(x: Union[str, NoneType]) → Union[pendulum.duration.Duration, NoneType][source]

From an ISO-formatted string to a Python Pendulum, with timezone.

static pendulum_duration_to_isostring(x: Union[pendulum.duration.Duration, NoneType]) → Union[str, NoneType][source]

From a pendulum.Duration (or None) an ISO-formatted string in our particular format (or NULL).

process_bind_param(value: Union[pendulum.datetime.DateTime, NoneType], dialect: sqlalchemy.engine.interfaces.Dialect) → Union[str, NoneType][source]

Convert parameters on the way from Python to the database.

process_literal_param(value: Union[pendulum.datetime.DateTime, NoneType], dialect: sqlalchemy.engine.interfaces.Dialect) → Union[str, NoneType][source]

Convert literals on the way from Python to the database.

process_result_value(value: Union[str, NoneType], dialect: sqlalchemy.engine.interfaces.Dialect) → Union[pendulum.datetime.DateTime, NoneType][source]

Convert things on the way from the database to Python.

python_type

The Python type of the object.

class camcops_server.cc_modules.cc_sqla_coltypes.PermittedValueChecker(not_null: bool = False, minimum: Union[int, float] = None, maximum: Union[int, float] = None, permitted_values: List[Any] = None)[source]

Represents permitted values (in columns belonging to CamCOPS tasks), and checks a value against them.

Parameters:
  • not_null – must the value not be NULL?
  • minimum – if specified, a numeric minimum value
  • maximum – if specified, a numeric maximum value
  • permitted_values – if specified, a list of permitted values
failure_msg(value: Any) → str[source]

Why does the value not pass our tests?

is_ok(value: Any) → bool[source]

Does the value pass our tests?

permitted_values_csv() → str[source]

Returns a CSV representation of the permitted values.

Primarily used for CRIS data dictionaries.

camcops_server.cc_modules.cc_sqla_coltypes.RFC_2822_DATE_MAX_LEN = 31

e.g. Fri, 09 Nov 2001 01 -- 08 -- 47 -0000; 3.3 in https://tools.ietf.org/html/rfc2822, assuming extra white space not added

class camcops_server.cc_modules.cc_sqla_coltypes.RelationshipInfo[source]

Used as keys the info (user-defined) dictionary parameter to SQLAlchemy relationship calls; see https://docs.sqlalchemy.org/en/latest/orm/relationship_api.html#sqlalchemy.orm.relationship.

camcops_server.cc_modules.cc_sqla_coltypes.SENDING_FORMAT_MAX_LEN = 50

for export; our choice based on use in CamCOPS code

camcops_server.cc_modules.cc_sqla_coltypes.SESSION_TOKEN_MAX_BYTES = 64

our choice; 64 bytes => 512 bits, which is a lot in 2017

class camcops_server.cc_modules.cc_sqla_coltypes.SemanticVersionColType(*args, **kwargs)[source]

Stores semantic versions in the database. Uses semantic_version.Version on the Python side.

Construct a TypeDecorator.

Arguments sent here are passed to the constructor of the class assigned to the impl class level attribute, assuming the impl is a callable, and the resulting object is assigned to the self.impl instance attribute (thus overriding the class attribute of the same name).

If the class level impl is not a callable (the unusual case), it will be assigned to the same instance attribute ‘as-is’, ignoring those arguments passed to the constructor.

Subclasses can override this to customize the generation of self.impl entirely.

process_bind_param(value: Union[semantic_version.base.Version, NoneType], dialect: sqlalchemy.engine.interfaces.Dialect) → Union[str, NoneType][source]

Convert parameters on the way from Python to the database.

process_literal_param(value: Union[semantic_version.base.Version, NoneType], dialect: sqlalchemy.engine.interfaces.Dialect) → Union[str, NoneType][source]

Convert literals on the way from Python to the database.

process_result_value(value: Union[str, NoneType], dialect: sqlalchemy.engine.interfaces.Dialect) → Union[semantic_version.base.Version, NoneType][source]

Convert things on the way from the database to Python.

python_type

The Python type of the object.

class camcops_server.cc_modules.cc_sqla_coltypes.SqlaColtypesTest(methodName='runTest')[source]

Unit tests.

Create an instance of the class that will use the named test method when executed. Raises a ValueError if the instance does not have a method with the specified name.

camcops_server.cc_modules.cc_sqla_coltypes.TABLENAME_MAX_LEN = 128

For

camcops_server.cc_modules.cc_sqla_coltypes.TASK_SUMMARY_TEXT_FIELD_DEFAULT_MAX_LEN = 50

… our choice, contains short strings like “normal”, “abnormal”, “severe”. Easy to change, since it’s only used when exporting summaries, and not in the core database.

camcops_server.cc_modules.cc_sqla_coltypes.URL_MAX_LEN = 255

Our choice

camcops_server.cc_modules.cc_sqla_coltypes.USERNAME_CAMCOPS_MAX_LEN = 191

191 is the maximum for MySQL + InnoDB + VARCHAR + utf8mb4 + index

camcops_server.cc_modules.cc_sqla_coltypes.USERNAME_EXTERNAL_MAX_LEN = 255

Our choice

camcops_server.cc_modules.cc_sqla_coltypes.gen_ancillary_relationships(obj) → Generator[[Tuple[str, sqlalchemy.orm.relationships.RelationshipProperty, Type[_ForwardRef('GenericTabletRecordMixin')]], NoneType], NoneType][source]

For an SQLAlchemy ORM object, yields tuples of attrname, relationship_property, related_class for all relationships that are marked as a CamCOPS ancillary relationship.

camcops_server.cc_modules.cc_sqla_coltypes.gen_blob_relationships(obj) → Generator[[Tuple[str, sqlalchemy.orm.relationships.RelationshipProperty, Type[_ForwardRef('GenericTabletRecordMixin')]], NoneType], NoneType][source]

For an SQLAlchemy ORM object, yields tuples of attrname, relationship_property, related_class for all relationships that are marked as a CamCOPS BLOB relationship.

camcops_server.cc_modules.cc_sqla_coltypes.gen_camcops_blob_columns(obj) → Generator[[Tuple[str, camcops_server.cc_modules.cc_sqla_coltypes.CamcopsColumn], NoneType], NoneType][source]

Finds all columns of an object that are camcops_server.cc_modules.cc_sqla_coltypes.CamcopsColumn columns referencing the BLOB table.

Parameters:obj – SQLAlchemy ORM object to inspect
Yields:attrname, column tuples
camcops_server.cc_modules.cc_sqla_coltypes.gen_camcops_columns(obj) → Generator[[Tuple[str, camcops_server.cc_modules.cc_sqla_coltypes.CamcopsColumn], NoneType], NoneType][source]

Finds all columns of an object that are camcops_server.cc_modules.cc_sqla_coltypes.CamcopsColumn columns.

Parameters:obj – SQLAlchemy ORM object to inspect
Yields:attrname, column tuples
camcops_server.cc_modules.cc_sqla_coltypes.gen_columns_matching_attrnames(obj, attrnames: List[str]) → Generator[[Tuple[str, sqlalchemy.sql.schema.Column], NoneType], NoneType][source]

Find columns of an SQLAlchemy ORM object whose attribute names match a list.

Parameters:
  • obj – SQLAlchemy ORM object to inspect
  • attrnames – attribute names
Yields:

attrname, column tuples

camcops_server.cc_modules.cc_sqla_coltypes.get_camcops_blob_column_attr_names(obj) → List[str][source]

Get a list of camcops_server.cc_modules.cc_sqla_coltypes.CamcopsColumn BLOB column attribute names from an SQLAlchemy ORM object.

camcops_server.cc_modules.cc_sqla_coltypes.get_camcops_column_attr_names(obj) → List[str][source]

Get a list of camcops_server.cc_modules.cc_sqla_coltypes.CamcopsColumn column attribute names from an SQLAlchemy ORM object.

camcops_server.cc_modules.cc_sqla_coltypes.get_column_attr_names(obj) → List[str][source]

Get a list of column attribute names from an SQLAlchemy ORM object.

class camcops_server.cc_modules.cc_sqla_coltypes.isotzdatetime_to_utcdatetime(*clauses, **kwargs)[source]

Used as an SQL operation by PendulumDateTimeAsIsoTextColType.

Creates an SQL expression wrapping a field containing our ISO-8601 text, making a DATETIME out of it, in the UTC timezone.

Implemented for different SQL dialects.

Construct a FunctionElement.

camcops_server.cc_modules.cc_sqla_coltypes.isotzdatetime_to_utcdatetime_default(element: ClauseElement, compiler: SQLCompiler, **kw) → None[source]

Default implementation for isotzdatetime_to_utcdatetime: fail.

camcops_server.cc_modules.cc_sqla_coltypes.isotzdatetime_to_utcdatetime_mysql(element: ClauseElement, compiler: SQLCompiler, **kw) → str[source]

Implementation of isotzdatetime_to_utcdatetime for MySQL.

For format, see https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

Note the use of “%i” for minutes.

Things after func. get passed to the database engine as literal SQL functions; http://docs.sqlalchemy.org/en/latest/core/tutorial.html

camcops_server.cc_modules.cc_sqla_coltypes.isotzdatetime_to_utcdatetime_sqlite(element: ClauseElement, compiler: SQLCompiler, **kw) → str[source]

Implementation of isotzdatetime_to_utcdatetime for SQLite.

Get an SQL expression for the timezone adjustment in hours. Note that if a time is 12:00+01:00, that means e.g. midday BST, which is 11:00+00:00 or 11:00 UTC. So you SUBTRACT the displayed timezone from the time, which I’ve always thought is a bit odd.

Ha! Was busy implementing this, but SQLite is magic; if there’s a timezone at the end, STRFTIME() will convert it to UTC automatically! Moreover, the format is the OUTPUT format that a Python datetime will recognize, so no ‘T’.

The output format is like this: 2018-06-01 00:00:00.000. Note that SQLite provides millisecond precision only (in general and via the %f argument to STRFTIME).

See also SQLAlchemy’s DATETIME support for SQLite:

… but that doesn’t support timezones, so that doesn’t help us.

One further problem – see camcops_server.tasks.core10.Core10ReportDateRangeTests – is that comparisons are done by SQLite as text, so e.g.

SELECT '2018-06-01 00:00:00.000' >= '2018-06-01 00:00:00.000000';  -- 0, false
SELECT '2018-06-01 00:00:00.000' >= '2018-06-01 00:00:00.000';  -- 1, true

and therefore we need to ensure either that the SQLite side gets translated to 6dp, or the bind param gets translated to 3dp. I don’t think we can always have control over the bind parameter. So we append ‘000’ to the SQLite side.

camcops_server.cc_modules.cc_sqla_coltypes.isotzdatetime_to_utcdatetime_sqlserver(element: ClauseElement, compiler: SQLCompiler, **kw) → str[source]

Implementation of isotzdatetime_to_utcdatetime for SQL Server.

Converting strings to DATETIME values

  • CAST(): Part of ANSI SQL.
  • CONVERT(): Not part of ANSI SQL; has some extra formatting options.

Both methods work:

SELECT CAST('2001-01-31T21:30:49.123' AS DATETIME) AS via_cast,
       CONVERT(DATETIME, '2001-01-31T21:30:49.123') AS via_convert;

… fine on SQL Server 2005, with milliseconds in both cases. However, going beyond milliseconds doesn’t fail gracefully, it causes an error (e.g. “…21:30.49.123456”) both for CAST and CONVERT.

The DATETIME2 format accepts greater precision, but requires SQL Server 2008 or higher. Then this works:

SELECT CAST('2001-01-31T21:30:49.123456' AS DATETIME2) AS via_cast,
       CONVERT(DATETIME2, '2001-01-31T21:30:49.123456') AS via_convert;

So as not to be too optimistic: CAST(x AS DATETIME2) ignores (silently) any timezone information in the string. So does CONVERT(DATETIME2, x, {0 or 1}).

Converting between time zones

NO TIME ZONE SUPPORT in SQL Server 2005. e.g. https://stackoverflow.com/questions/3200827/how-to-convert-timezones-in-sql-server-2005.

TODATETIMEOFFSET(expression, time_zone):
      expression: something that evaluates to a DATETIME2 value
      time_zone: integer minutes, or string hours/minutes e.g. "+13.00"
  -> produces a DATETIMEOFFSET value

Available from SQL Server 2008 (https://docs.microsoft.com/en-us/sql/t-sql/functions/todatetimeoffset-transact-sql).

SWITCHOFFSET
  -> converts one DATETIMEOFFSET value to another, preserving its UTC
     time, but changing the displayed (local) time zone.

… however, is that unnecessary? We want a plain DATETIME2 in UTC, and .conversion to UTC is automatically achieved by CONVERT(DATETIME2, .some_datetimeoffset, 1)

https://stackoverflow.com/questions/4953903/how-can-i-convert-a-sql-server-2008-datetimeoffset-to-a-datetime

… but not by CAST(some_datetimeoffset AS DATETIME2), and not by CONVERT(DATETIME2, some_datetimeoffset, 0)

… and styles 0 and 1 are the only ones permissible from SQL Server 2012 and up (empirically, and documented for the reverse direction at https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017)

… this is not properly documented re UTC conversion, as far as I can see. Let’s use SWITCHOFFSET -> CAST to be explicit and clear.

AT TIME ZONE: From SQL Server 2016 only. https://docs.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql?view=sql-server-2017

Therefore

  • We need to require SQL Server 2008 or higher.
  • Therefore we can use the DATETIME2 type.
  • Note that LEN(), not LENGTH(), is ANSI SQL; SQL Server only supports LEN.

Example (tested on SQL Server 2014)

DECLARE @source AS VARCHAR(100) = '2001-01-31T21:30:49.123456+07:00';

SELECT CAST(
    SWITCHOFFSET(
        TODATETIMEOFFSET(
            CAST(LEFT(@source, LEN(@source) - 6) AS DATETIME2),
            RIGHT(@source, 6)
        ),
        '+00:00'
    )
    AS DATETIME2
)  -- 2001-01-31 14:30:49.1234560
camcops_server.cc_modules.cc_sqla_coltypes.permitted_value_failure_msgs(obj) → List[str][source]

Checks a SQLAlchemy ORM object instance against its permitted value checks (via its camcops_server.cc_modules.cc_sqla_coltypes.CamcopsColumn columns), if it has any.

Returns a list of failure messages (empty list means all OK).

If you just want to know whether it passes, a quicker way is via permitted_values_ok().

camcops_server.cc_modules.cc_sqla_coltypes.permitted_values_ok(obj) → bool[source]

Checks whether an instance passes its permitted value checks, if it has any.

If you want to know why it failed, see permitted_value_failure_msgs().

class camcops_server.cc_modules.cc_sqla_coltypes.unknown_field_to_utcdatetime(*clauses, **kwargs)[source]

Used as an SQL operation by PendulumDateTimeAsIsoTextColType.

Creates an SQL expression wrapping a field containing something unknown, which might be a DATETIME or an ISO-formatted field, and making a DATETIME out of it, in the UTC timezone.

Implemented for different SQL dialects.

Construct a FunctionElement.

camcops_server.cc_modules.cc_sqla_coltypes.unknown_field_to_utcdatetime_default(element: ClauseElement, compiler: SQLCompiler, **kw) → None[source]

Default implementation for unknown_field_to_utcdatetime: fail.

camcops_server.cc_modules.cc_sqla_coltypes.unknown_field_to_utcdatetime_mysql(element: ClauseElement, compiler: SQLCompiler, **kw) → str[source]

Implementation of unknown_field_to_utcdatetime for MySQL.

If it’s the length of a plain DATETIME e.g. 2013-05-30 00:00:00 (19), leave it as a DATETIME; otherwise convert ISO -> DATETIME.

camcops_server.cc_modules.cc_sqla_coltypes.unknown_field_to_utcdatetime_sqlite(element: ClauseElement, compiler: SQLCompiler, **kw) → str[source]

Implementation of unknown_field_to_utcdatetime for SQLite.

camcops_server.cc_modules.cc_sqla_coltypes.unknown_field_to_utcdatetime_sqlserver(element: ClauseElement, compiler: SQLCompiler, **kw) → str[source]

Implementation of unknown_field_to_utcdatetime for SQL Server.

We should cope also with the possibility of a DATETIME2 field, not just DATETIME. It seems consistent that LEN(DATETIME2) = 27, with precision tenth of a microsecond, e.g. 2001-01-31 21:30:49.1234567 (27).

So, if it looks like a DATETIME or a DATETIME2, then we leave it alone; otherwise we put it through our ISO-to-datetime function.

Importantly, note that neither _SQLSERVER_DATETIME_LEN nor _SQLSERVER_DATETIME2_LEN are the length of any of our ISO strings.