15.2.155. camcops_server.cc_modules.cc_sqla_coltypes¶
camcops_server/cc_modules/cc_sqla_coltypes.py
Copyright (C) 2012, University of Cambridge, Department of Psychiatry. Created by Rudolf Cardinal (rnc1001@cam.ac.uk).
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 <https://www.gnu.org/licenses/>.
SQLAlchemy column types used by CamCOPS.
Note these built-in SQLAlchemy types (https://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
operatorNumeric
For fixed-precision numbers like
NUMERIC
orDECIMAL
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
Also notes:
Columns may need their character set specified explicitly under MySQL: https://stackoverflow.com/questions/2108824/mysql-incorrect-string-value-error-when-save-unicode-string-in-django
- class camcops_server.cc_modules.cc_sqla_coltypes.BoolColumn(*args: Any, **kwargs: Any)[source]¶
A
camcops_server.cc_modules.cc_sqla_coltypes.CamcopsColumn
representing a boolean value.- __init__(*args: Any, **kwargs: Any) None [source]¶
- 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.
- 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: Optional[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).
- __init__(*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: Optional[camcops_server.cc_modules.cc_sqla_coltypes.PermittedValueChecker] = None, **kwargs) None [source]¶
- 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.
- 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.
- process_bind_param(value: Optional[List[camcops_server.cc_modules.cc_simpleobjects.IdNumReference]], dialect: sqlalchemy.engine.interfaces.Dialect) str [source]¶
Convert parameters on the way from Python to the database.
- process_literal_param(value: Optional[List[camcops_server.cc_modules.cc_simpleobjects.IdNumReference]], dialect: sqlalchemy.engine.interfaces.Dialect) str [source]¶
Convert literals on the way from Python to the database.
- process_result_value(value: Optional[str], dialect: sqlalchemy.engine.interfaces.Dialect) List[camcops_server.cc_modules.cc_simpleobjects.IdNumReference] [source]¶
Convert things on the way from the database to Python.
- property python_type: type¶
The Python type of the object.
- class camcops_server.cc_modules.cc_sqla_coltypes.JsonColType(*args, **kwargs)[source]¶
- impl¶
alias of
sqlalchemy.sql.sqltypes.UnicodeText
- process_bind_param(value: Any, dialect: sqlalchemy.engine.interfaces.Dialect) Optional[str] [source]¶
Convert parameters on the way from Python to the database.
- process_result_value(value: str, dialect: sqlalchemy.engine.interfaces.Dialect) Any [source]¶
Convert things on the way from the database to Python.
- property python_type: type¶
Return the Python type object expected to be returned by instances of this type, if known.
Basically, for those types which enforce a return type, or are known across the board to do such for all common DBAPIs (like
int
for example), will return that type.If a return type is not defined, raises
NotImplementedError
.Note that any type also accommodates NULL in SQL which means you can also get back
None
from any type in practice.
- 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.
- 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 applyfunc.lower()
to the left and right side:class MyComparator(ColumnOperators): def operate(self, op, other, **kwargs): return op(func.lower(self), func.lower(other), **kwargs)
- 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()
.
- static isostring_to_pendulum(x: Optional[str]) Optional[pendulum.datetime.DateTime] [source]¶
From an ISO-formatted string to a Python Pendulum, with timezone.
- static pendulum_to_isostring(x: Union[None, datetime.datetime, datetime.date, pendulum.datetime.DateTime, str]) Optional[str] [source]¶
From a Python datetime to an ISO-formatted string in our particular format.
- process_bind_param(value: Optional[pendulum.datetime.DateTime], dialect: sqlalchemy.engine.interfaces.Dialect) Optional[str] [source]¶
Convert parameters on the way from Python to the database.
- process_literal_param(value: Optional[pendulum.datetime.DateTime], dialect: sqlalchemy.engine.interfaces.Dialect) Optional[str] [source]¶
Convert literals on the way from Python to the database.
- process_result_value(value: Optional[str], dialect: sqlalchemy.engine.interfaces.Dialect) Optional[pendulum.datetime.DateTime] [source]¶
Convert things on the way from the database to Python.
- property python_type: 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.- static isostring_to_pendulum_duration(x: Optional[str]) Optional[pendulum.duration.Duration] [source]¶
From an ISO-formatted string to a Python Pendulum, with timezone.
- static pendulum_duration_to_isostring(x: Optional[pendulum.duration.Duration]) Optional[str] [source]¶
From a
pendulum.Duration
(orNone
) an ISO-formatted string in our particular format (orNULL
).
- process_bind_param(value: Optional[pendulum.datetime.DateTime], dialect: sqlalchemy.engine.interfaces.Dialect) Optional[str] [source]¶
Convert parameters on the way from Python to the database.
- process_literal_param(value: Optional[pendulum.datetime.DateTime], dialect: sqlalchemy.engine.interfaces.Dialect) Optional[str] [source]¶
Convert literals on the way from Python to the database.
- process_result_value(value: Optional[str], dialect: sqlalchemy.engine.interfaces.Dialect) Optional[pendulum.datetime.DateTime] [source]¶
Convert things on the way from the database to Python.
- property python_type: type¶
The Python type of the object.
- class camcops_server.cc_modules.cc_sqla_coltypes.PermittedValueChecker(not_null: bool = False, minimum: Optional[Union[float, int]] = None, maximum: Optional[Union[float, int]] = None, permitted_values: Optional[Sequence[Any]] = None)[source]¶
Represents permitted values (in columns belonging to CamCOPS tasks), and checks a value against them.
- __init__(not_null: bool = False, minimum: Optional[Union[float, int]] = None, maximum: Optional[Union[float, int]] = None, permitted_values: Optional[Sequence[Any]] = None) None [source]¶
- 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
- class camcops_server.cc_modules.cc_sqla_coltypes.PhoneNumberColType(*args, **kwargs)[source]¶
- process_bind_param(value: Any, dialect: sqlalchemy.engine.interfaces.Dialect) Optional[str] [source]¶
Convert parameters on the way from Python to the database.
- process_result_value(value: str, dialect: sqlalchemy.engine.interfaces.Dialect) Any [source]¶
Convert things on the way from the database to Python.
- property python_type: type¶
Return the Python type object expected to be returned by instances of this type, if known.
Basically, for those types which enforce a return type, or are known across the board to do such for all common DBAPIs (like
int
for example), will return that type.If a return type is not defined, raises
NotImplementedError
.Note that any type also accommodates NULL in SQL which means you can also get back
None
from any type in practice.
- class camcops_server.cc_modules.cc_sqla_coltypes.RelationshipInfo[source]¶
Used as keys the
info
(user-defined) dictionary parameter to SQLAlchemyrelationship
calls; see https://docs.sqlalchemy.org/en/latest/orm/relationship_api.html#sqlalchemy.orm.relationship.
- 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.- process_bind_param(value: Optional[semantic_version.base.Version], dialect: sqlalchemy.engine.interfaces.Dialect) Optional[str] [source]¶
Convert parameters on the way from Python to the database.
- process_literal_param(value: Optional[semantic_version.base.Version], dialect: sqlalchemy.engine.interfaces.Dialect) Optional[str] [source]¶
Convert literals on the way from Python to the database.
- process_result_value(value: Optional[str], dialect: sqlalchemy.engine.interfaces.Dialect) Optional[semantic_version.base.Version] [source]¶
Convert things on the way from the database to Python.
- property python_type: type¶
The Python type of the object.
- class camcops_server.cc_modules.cc_sqla_coltypes.UuidColType(*args, **kwargs)[source]¶
- process_bind_param(value: uuid.UUID, dialect: sqlalchemy.engine.interfaces.Dialect) Optional[str] [source]¶
Convert parameters on the way from Python to the database.
- process_result_value(value: Optional[str], dialect: sqlalchemy.engine.interfaces.Dialect) Optional[uuid.UUID] [source]¶
Convert things on the way from the database to Python.
- property python_type: type¶
Return the Python type object expected to be returned by instances of this type, if known.
Basically, for those types which enforce a return type, or are known across the board to do such for all common DBAPIs (like
int
for example), will return that type.If a return type is not defined, raises
NotImplementedError
.Note that any type also accommodates NULL in SQL which means you can also get back
None
from any type in practice.
- camcops_server.cc_modules.cc_sqla_coltypes.gen_ancillary_relationships(obj) Generator[Tuple[str, sqlalchemy.orm.relationships.RelationshipProperty, Type[GenericTabletRecordMixin]], None, None] [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[GenericTabletRecordMixin]], None, None] [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], None, None] [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], None, None] [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], None, None] [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.
- 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; https://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 toSTRFTIME
).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 doesCONVERT(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 byCONVERT(DATETIME2, .some_datetimeoffset, 1)
… but not by
CAST(some_datetimeoffset AS DATETIME2)
, and not byCONVERT(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-2017Therefore
We need to require SQL Server 2008 or higher.
Therefore we can use the
DATETIME2
type.Note that
LEN()
, notLENGTH()
, is ANSI SQL; SQL Server only supportsLEN
.
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 aDATETIME
out of it, in the UTC timezone.Implemented for different SQL dialects.
- 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 aDATETIME
; 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 justDATETIME
. It seems consistent thatLEN(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 aDATETIME2
, 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.