Coverage for cc_modules/cc_db.py: 52%

1""" 

2camcops_server/cc_modules/cc_db.py 

3 

4=============================================================================== 

5 

6 Copyright (C) 2012, University of Cambridge, Department of Psychiatry. 

7 Created by Rudolf Cardinal (rnc1001@cam.ac.uk). 

8 

9 This file is part of CamCOPS. 

10 

11 CamCOPS is free software: you can redistribute it and/or modify 

12 it under the terms of the GNU General Public License as published by 

13 the Free Software Foundation, either version 3 of the License, or 

14 (at your option) any later version. 

15 

16 CamCOPS is distributed in the hope that it will be useful, 

17 but WITHOUT ANY WARRANTY; without even the implied warranty of 

18 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 

19 GNU General Public License for more details. 

20 

21 You should have received a copy of the GNU General Public License 

22 along with CamCOPS. If not, see <https://www.gnu.org/licenses/>. 

23 

24=============================================================================== 

25 

26**Common database code, e.g. mixins for tables that are uploaded from the 

27client.** 

28 

29""" 

30 

31from collections import OrderedDict 

32import datetime 

33import logging 

34from typing import ( 

35 Any, 

36 Callable, 

37 Dict, 

38 Generator, 

39 Iterable, 

40 List, 

41 NoReturn, 

42 Optional, 

43 Sequence, 

44 Set, 

45 Tuple, 

46 Type, 

47 TYPE_CHECKING, 

48 TypeVar, 

49 Union, 

50) 

51 

52from cardinal_pythonlib.logs import BraceStyleAdapter 

53from cardinal_pythonlib.sqlalchemy.orm_inspect import gen_columns 

54from pendulum import DateTime as Pendulum 

55from semantic_version import Version 

56from sqlalchemy.exc import IntegrityError 

57from sqlalchemy.ext.declarative import declared_attr 

58from sqlalchemy.orm import Mapped, mapped_column, relationship 

59from sqlalchemy.orm.relationships import RelationshipProperty 

60from sqlalchemy.orm import Session as SqlASession 

61from sqlalchemy.sql.functions import func 

62from sqlalchemy.sql.schema import Column, ForeignKey 

63from sqlalchemy.sql.sqltypes import Integer 

64 

65from camcops_server.cc_modules.cc_constants import ( 

66 CLIENT_DATE_FIELD, 

67 ERA_NOW, 

68 EXTRA_COMMENT_PREFIX, 

69 EXTRA_TASK_SERVER_PK_FIELD, 

70 EXTRA_TASK_TABLENAME_FIELD, 

71 MOVE_OFF_TABLET_FIELD, 

72 SPREADSHEET_PATIENT_FIELD_PREFIX, 

73 TABLET_ID_FIELD, 

74) 

75from camcops_server.cc_modules.cc_dataclasses import SummarySchemaInfo 

76from camcops_server.cc_modules.cc_sqla_coltypes import ( 

77 camcops_column, 

78 COLATTR_BLOB_RELATIONSHIP_ATTR_NAME, 

79 COLATTR_PERMITTED_VALUE_CHECKER, 

80 EraColType, 

81 gen_ancillary_relationships, 

82 gen_camcops_blob_columns, 

83 PendulumDateTimeAsIsoTextColType, 

84 PermittedValueChecker, 

85 RelationshipInfo, 

86 SemanticVersionColType, 

87 TableNameColType, 

88) 

89from camcops_server.cc_modules.cc_simpleobjects import TaskExportOptions 

90from camcops_server.cc_modules.cc_spreadsheet import SpreadsheetPage 

91from camcops_server.cc_modules.cc_version import CAMCOPS_SERVER_VERSION 

92from camcops_server.cc_modules.cc_xml import ( 

93 make_xml_branches_from_blobs, 

94 make_xml_branches_from_columns, 

95 make_xml_branches_from_summaries, 

96 XML_COMMENT_STORED, 

97 XML_COMMENT_CALCULATED, 

98 XmlElement, 

99) 

100 

101if TYPE_CHECKING: 

102 from camcops_server.cc_modules.cc_blob import Blob 

103 from camcops_server.cc_modules.cc_device import Device 

104 from camcops_server.cc_modules.cc_group import Group 

105 from camcops_server.cc_modules.cc_patient import Patient 

106 from camcops_server.cc_modules.cc_request import ( 

107 CamcopsRequest, 

108 ) 

109 from camcops_server.cc_modules.cc_summaryelement import ( 

110 SummaryElement, 

111 ) 

112 from camcops_server.cc_modules.cc_task import Task 

113 from camcops_server.cc_modules.cc_user import User 

114 

115log = BraceStyleAdapter(logging.getLogger(__name__)) 

116 

117 

118# ============================================================================= 

119# Hacks for specific database drivers 

120# ============================================================================= 

121 

122CRASH_ON_BAD_CONVERSIONS = False # for debugging only! 

123 

124if CRASH_ON_BAD_CONVERSIONS: 

125 log.error("DANGER: CRASH_ON_BAD_CONVERSIONS set in cc_db.py") 

126 

127try: 

128 import MySQLdb 

129 import MySQLdb.converters 

130except ImportError: 

131 MySQLdb = None 

132 

133try: 

134 import pymysql 

135 import pymysql.converters 

136except ImportError: 

137 pymysql = None 

138 

139_SQL_LITERAL_TYPE = Union[int, float, str] 

140 

141_MYSQL_CONVERSION_DICT_TYPE = Dict[Any, Callable] 

142_MYSQLDB_PYTHON_TO_DB_TYPE = Callable[ 

143 [Any, _MYSQL_CONVERSION_DICT_TYPE], _SQL_LITERAL_TYPE 

144] # f(o, d) -> s 

145_MYSQLDB_DB_TO_PYTHON_TYPE = Callable[[_SQL_LITERAL_TYPE], Any] # f(s) -> o 

146 

147_PYMYSQL_ENCODER_DICT_TYPE = Dict[Type, Callable] 

148_PYMYSQL_PYTHON_TO_DB_TYPE = Callable[ 

149 [Any, Optional[_PYMYSQL_ENCODER_DICT_TYPE]], _SQL_LITERAL_TYPE 

150] # f(o, mapping) -> s 

151_PYMYSQL_DB_TO_PYTHON_TYPE = Callable[[_SQL_LITERAL_TYPE], Any] 

152 

153 

154def mysqldb_crash_on_bad_conversion( 

155 o: Any, d: _MYSQL_CONVERSION_DICT_TYPE 

156) -> NoReturn: 

157 """ 

158 Reports a bad conversion and crashes. For debugging only (obviously)! 

159 

160 **Conversions by mysqlclient (MySQLdb)** 

161 

162 As per the help docstring for ``MySQLdb/converters.py``, 

163 

164 - the Python-to-database conversion function has the signature ``f(o, d)`` 

165 where ``o`` is the thing to be converted (such as a datetime.datetime) 

166 and ``d`` is the conversion dictionary; it returns an SQL literal value. 

167 

168 - The database-to-Python conversion function has the argument ``f(s)`` 

169 where ``s`` is a string; it returns a Python object. 

170 

171 Both types of functions are stored in ``MySQLdb.converters``, which is a 

172 ``dict``. The keys named ``FIELD_TYPE.*`` are the database-to-Python 

173 converters; the others are the Python-to-database converters. 

174 

175 **Conversions by pymysql** 

176 

177 Similar (for back compatibility), but not the same. 

178 

179 - ``pymysql.converters.conversions`` is ``pymysql.converters.decoders`` and 

180 contains database-to-Python converters. 

181 

182 - ``pymysql.converters.encoders`` contains Python-to-database converters. 

183 

184 Args: 

185 o: Python object 

186 d: MySQLdb conversion dictionary 

187 

188 Returns: 

189 SQL literal 

190 """ 

191 failmsg = ( 

192 f"mysqldb_crash_on_bad_conversion: attempting to convert bad Python " 

193 f"object to database: {o!r}. Conversion dict is {d!r}." 

194 ) 

195 log.critical(failmsg) 

196 raise RuntimeError(failmsg) 

197 

198 

199def pymysql_crash_on_bad_conversion( 

200 obj: Any, mapping: _PYMYSQL_ENCODER_DICT_TYPE 

201) -> NoReturn: 

202 """ 

203 See :func:`mysqldb_crash_on_bad_conversion`. 

204 """ 

205 failmsg = ( 

206 f"pymysql_crash_on_bad_conversion: attempting to convert bad Python " 

207 f"object to database: {obj!r}. Mapping dict is {mapping!r}." 

208 ) 

209 log.critical(failmsg) 

210 raise RuntimeError(failmsg) 

211 

212 

213# ----------------------------------------------------------------------------- 

214# Pendulum; see https://pypi.org/project/pendulum/ -- but note that it says 

215# "pymysql.converters.conversions" but should say 

216# "pymysql.converters.encoders". 

217# ----------------------------------------------------------------------------- 

218 

219if MySQLdb: 

220 log.debug("Hacking MySQLdb to support pendulum.DateTime") 

221 if CRASH_ON_BAD_CONVERSIONS: 

222 MySQLdb.converters.conversions[Pendulum] = ( 

223 mysqldb_crash_on_bad_conversion 

224 ) 

225 else: 

226 MySQLdb.converters.conversions[Pendulum] = ( 

227 MySQLdb.converters.DateTime2literal 

228 ) 

229 

230if pymysql: 

231 log.debug("Hacking pymysql to support pendulum.DateTime") 

232 if CRASH_ON_BAD_CONVERSIONS: 

233 pymysql.converters.encoders[Pendulum] = pymysql_crash_on_bad_conversion 

234 else: 

235 pymysql.converters.encoders[Pendulum] = ( 

236 pymysql.converters.escape_datetime 

237 ) 

238 # And also, as per the source code and 

239 # https://stackoverflow.com/questions/59871904/convert-pymysql-query-result-with-mysql-decimal-type-to-python-float # noqa 

240 pymysql.converters.conversions = pymysql.converters.encoders.copy() 

241 pymysql.converters.conversions.update(pymysql.converters.decoders) 

242 

243 

244# ============================================================================= 

245# Constants 

246# ============================================================================= 

247 

248T = TypeVar("T") 

249 

250# Database fieldname constants. Do not change. Used here and in client_api.py 

251FN_PK = "_pk" 

252FN_DEVICE_ID = "_device_id" 

253FN_ERA = "_era" 

254FN_CURRENT = "_current" 

255FN_WHEN_ADDED_EXACT = "_when_added_exact" 

256FN_WHEN_ADDED_BATCH_UTC = "_when_added_batch_utc" 

257FN_ADDING_USER_ID = "_adding_user_id" 

258FN_WHEN_REMOVED_EXACT = "_when_removed_exact" 

259FN_WHEN_REMOVED_BATCH_UTC = "_when_removed_batch_utc" 

260FN_REMOVING_USER_ID = "_removing_user_id" 

261FN_PRESERVING_USER_ID = "_preserving_user_id" 

262FN_FORCIBLY_PRESERVED = "_forcibly_preserved" 

263FN_PREDECESSOR_PK = "_predecessor_pk" 

264FN_SUCCESSOR_PK = "_successor_pk" 

265FN_MANUALLY_ERASED = "_manually_erased" 

266FN_MANUALLY_ERASED_AT = "_manually_erased_at" 

267FN_MANUALLY_ERASING_USER_ID = "_manually_erasing_user_id" 

268FN_CAMCOPS_VERSION = "_camcops_version" 

269FN_ADDITION_PENDING = "_addition_pending" 

270FN_REMOVAL_PENDING = "_removal_pending" 

271FN_GROUP_ID = "_group_id" 

272 

273# Common fieldnames used by all tasks. Do not change. 

274TFN_WHEN_CREATED = "when_created" 

275TFN_WHEN_FIRSTEXIT = "when_firstexit" 

276TFN_FIRSTEXIT_IS_FINISH = "firstexit_is_finish" 

277TFN_FIRSTEXIT_IS_ABORT = "firstexit_is_abort" 

278TFN_EDITING_TIME_S = "editing_time_s" 

279 

280# Fieldnames for the task patient mixin. Do not change. 

281TFN_PATIENT_ID = "patient_id" 

282 

283# Fieldnames for the task clinician mixin. Do not change. 

284TFN_CLINICIAN_SPECIALTY = "clinician_specialty" 

285TFN_CLINICIAN_NAME = "clinician_name" 

286TFN_CLINICIAN_PROFESSIONAL_REGISTRATION = "clinician_professional_registration" 

287TFN_CLINICIAN_POST = "clinician_post" 

288TFN_CLINICIAN_SERVICE = "clinician_service" 

289TFN_CLINICIAN_CONTACT_DETAILS = "clinician_contact_details" 

290 

291# Fieldnames for the task respondent mixin. Do not change. 

292TFN_RESPONDENT_NAME = "respondent_name" 

293TFN_RESPONDENT_RELATIONSHIP = "respondent_relationship" 

294 

295# Selected field/column names for patients. Do not change. 

296PFN_UUID = "uuid" 

297 

298# Column names for task summaries. 

299SFN_IS_COMPLETE = "is_complete" 

300SFN_SECONDS_CREATION_TO_FIRST_FINISH = "seconds_from_creation_to_first_finish" 

301SFN_CAMCOPS_SERVER_VERSION = "camcops_server_version" 

302 

303RESERVED_FIELDS = ( # fields that tablets can't upload 

304 FN_PK, 

305 FN_DEVICE_ID, 

306 FN_ERA, 

307 FN_CURRENT, 

308 FN_WHEN_ADDED_EXACT, 

309 FN_WHEN_ADDED_BATCH_UTC, 

310 FN_ADDING_USER_ID, 

311 FN_WHEN_REMOVED_EXACT, 

312 FN_WHEN_REMOVED_BATCH_UTC, 

313 FN_REMOVING_USER_ID, 

314 FN_PRESERVING_USER_ID, 

315 FN_FORCIBLY_PRESERVED, 

316 FN_PREDECESSOR_PK, 

317 FN_SUCCESSOR_PK, 

318 FN_MANUALLY_ERASED, 

319 FN_MANUALLY_ERASED_AT, 

320 FN_MANUALLY_ERASING_USER_ID, 

321 FN_CAMCOPS_VERSION, 

322 FN_ADDITION_PENDING, 

323 FN_REMOVAL_PENDING, 

324 FN_GROUP_ID, 

325) # but more generally: they start with "_"... 

326assert all(x.startswith("_") for x in RESERVED_FIELDS) 

327 

328TABLET_STANDARD_FIELDS = RESERVED_FIELDS + ( 

329 TABLET_ID_FIELD, 

330 CLIENT_DATE_FIELD, # when_last_modified 

331 MOVE_OFF_TABLET_FIELD, 

332) 

333TASK_STANDARD_FIELDS = TABLET_STANDARD_FIELDS + ( 

334 # All tasks: 

335 TFN_WHEN_CREATED, 

336 TFN_WHEN_FIRSTEXIT, 

337 TFN_FIRSTEXIT_IS_FINISH, 

338 TFN_FIRSTEXIT_IS_ABORT, 

339 TFN_EDITING_TIME_S, 

340) 

341TASK_FREQUENT_AND_FK_FIELDS = TASK_STANDARD_FIELDS + ( 

342 # Tasks with a patient: 

343 TFN_PATIENT_ID, 

344) 

345TASK_FREQUENT_FIELDS = TASK_FREQUENT_AND_FK_FIELDS + ( 

346 # Tasks with a clinician: 

347 TFN_CLINICIAN_SPECIALTY, 

348 TFN_CLINICIAN_NAME, 

349 TFN_CLINICIAN_PROFESSIONAL_REGISTRATION, 

350 TFN_CLINICIAN_POST, 

351 TFN_CLINICIAN_SERVICE, 

352 TFN_CLINICIAN_CONTACT_DETAILS, 

353 # Tasks with a respondent: 

354 TFN_RESPONDENT_NAME, 

355 TFN_RESPONDENT_RELATIONSHIP, 

356) 

357 

358REMOVE_COLUMNS_FOR_SIMPLIFIED_SPREADSHEETS = { 

359 # keep this: CLIENT_DATE_FIELD = when_last_modified 

360 # keep this: FN_PK = task PK 

361 # keep this: SFN_IS_COMPLETE = is the task complete 

362 # keep this: SPREADSHEET_PATIENT_FIELD_PREFIX + FN_PK = patient PK 

363 # keep this: TFN_WHEN_CREATED = main creation time 

364 FN_ADDING_USER_ID, 

365 FN_ADDITION_PENDING, 

366 FN_CAMCOPS_VERSION, # debatable; version that captured the original data 

367 FN_CURRENT, 

368 FN_DEVICE_ID, 

369 FN_ERA, 

370 FN_FORCIBLY_PRESERVED, 

371 FN_GROUP_ID, 

372 FN_MANUALLY_ERASED, 

373 FN_MANUALLY_ERASED_AT, 

374 FN_MANUALLY_ERASING_USER_ID, 

375 FN_PREDECESSOR_PK, 

376 FN_PRESERVING_USER_ID, 

377 FN_REMOVAL_PENDING, 

378 FN_REMOVING_USER_ID, 

379 FN_SUCCESSOR_PK, 

380 FN_WHEN_ADDED_BATCH_UTC, 

381 FN_WHEN_ADDED_EXACT, 

382 FN_WHEN_REMOVED_BATCH_UTC, 

383 FN_WHEN_REMOVED_EXACT, 

384 MOVE_OFF_TABLET_FIELD, 

385 SFN_CAMCOPS_SERVER_VERSION, # debatable; version that generated summary information # noqa 

386 SFN_SECONDS_CREATION_TO_FIRST_FINISH, 

387 SPREADSHEET_PATIENT_FIELD_PREFIX + CLIENT_DATE_FIELD, 

388 SPREADSHEET_PATIENT_FIELD_PREFIX + FN_ADDING_USER_ID, 

389 SPREADSHEET_PATIENT_FIELD_PREFIX + FN_ADDITION_PENDING, 

390 SPREADSHEET_PATIENT_FIELD_PREFIX + FN_CAMCOPS_VERSION, 

391 SPREADSHEET_PATIENT_FIELD_PREFIX + FN_CURRENT, 

392 SPREADSHEET_PATIENT_FIELD_PREFIX + FN_DEVICE_ID, 

393 SPREADSHEET_PATIENT_FIELD_PREFIX + FN_ERA, 

394 SPREADSHEET_PATIENT_FIELD_PREFIX + FN_FORCIBLY_PRESERVED, 

395 SPREADSHEET_PATIENT_FIELD_PREFIX + FN_GROUP_ID, 

396 SPREADSHEET_PATIENT_FIELD_PREFIX + FN_MANUALLY_ERASED, 

397 SPREADSHEET_PATIENT_FIELD_PREFIX + FN_MANUALLY_ERASED_AT, 

398 SPREADSHEET_PATIENT_FIELD_PREFIX + FN_MANUALLY_ERASING_USER_ID, 

399 SPREADSHEET_PATIENT_FIELD_PREFIX + FN_PREDECESSOR_PK, 

400 SPREADSHEET_PATIENT_FIELD_PREFIX + FN_PRESERVING_USER_ID, 

401 SPREADSHEET_PATIENT_FIELD_PREFIX + FN_REMOVAL_PENDING, 

402 SPREADSHEET_PATIENT_FIELD_PREFIX + FN_REMOVING_USER_ID, 

403 SPREADSHEET_PATIENT_FIELD_PREFIX + FN_SUCCESSOR_PK, 

404 SPREADSHEET_PATIENT_FIELD_PREFIX + FN_WHEN_ADDED_BATCH_UTC, 

405 SPREADSHEET_PATIENT_FIELD_PREFIX + FN_WHEN_ADDED_EXACT, 

406 SPREADSHEET_PATIENT_FIELD_PREFIX + FN_WHEN_REMOVED_BATCH_UTC, 

407 SPREADSHEET_PATIENT_FIELD_PREFIX + FN_WHEN_REMOVED_EXACT, 

408 SPREADSHEET_PATIENT_FIELD_PREFIX + MOVE_OFF_TABLET_FIELD, 

409 SPREADSHEET_PATIENT_FIELD_PREFIX + PFN_UUID, 

410 SPREADSHEET_PATIENT_FIELD_PREFIX + TABLET_ID_FIELD, 

411 TABLET_ID_FIELD, 

412 TFN_EDITING_TIME_S, 

413 TFN_FIRSTEXIT_IS_ABORT, 

414 TFN_FIRSTEXIT_IS_FINISH, 

415 TFN_PATIENT_ID, 

416 TFN_WHEN_FIRSTEXIT, 

417} 

418 

419 

420# ============================================================================= 

421# GenericTabletRecordMixin 

422# ============================================================================= 

423 

424 

425# noinspection PyAttributeOutsideInit 

426class GenericTabletRecordMixin(object): 

427 """ 

428 Mixin for all tables that are uploaded from the client, representing the 

429 fields that the server adds at the point of upload. 

430 

431 From the server's perspective, ``_pk`` is the unique primary key. 

432 

433 However, records are defined also in their tablet context, for which an 

434 individual tablet (defined by the combination of ``_device_id`` and 

435 ``_era``) sees its own PK, ``id``. 

436 """ 

437 

438 __tablename__ = None # type: str # sorts out some mixin type checking 

439 

440 # ------------------------------------------------------------------------- 

441 # On the server side: 

442 # ------------------------------------------------------------------------- 

443 

444 # Plain columns 

445 

446 # noinspection PyMethodParameters 

447 _pk: Mapped[int] = mapped_column( 

448 FN_PK, 

449 primary_key=True, 

450 autoincrement=True, 

451 index=True, 

452 comment="(SERVER) Primary key (on the server)", 

453 ) 

454 

455 # noinspection PyMethodParameters 

456 _device_id: Mapped[int] = mapped_column( 

457 FN_DEVICE_ID, 

458 ForeignKey("_security_devices.id", use_alter=True), 

459 index=True, 

460 comment="(SERVER) ID of the source tablet device", 

461 ) 

462 

463 # noinspection PyMethodParameters 

464 _era: Mapped[str] = mapped_column( 

465 FN_ERA, 

466 EraColType, 

467 index=True, 

468 comment="(SERVER) 'NOW', or when this row was preserved and " 

469 "removed from the source device (UTC ISO 8601)", 

470 ) 

471 # ... note that _era is textual so that plain comparison 

472 # with "=" always works, i.e. no NULLs -- for USER comparison too, not 

473 # just in CamCOPS code 

474 

475 # noinspection PyMethodParameters 

476 _current: Mapped[bool] = mapped_column( 

477 FN_CURRENT, 

478 index=True, 

479 comment="(SERVER) Is the row current (1) or not (0)?", 

480 ) 

481 

482 # noinspection PyMethodParameters 

483 _when_added_exact: Mapped[Optional[Pendulum]] = mapped_column( 

484 FN_WHEN_ADDED_EXACT, 

485 PendulumDateTimeAsIsoTextColType, 

486 comment="(SERVER) Date/time this row was added (ISO 8601)", 

487 ) 

488 

489 # noinspection PyMethodParameters 

490 _when_added_batch_utc: Mapped[Optional[datetime.datetime]] = mapped_column( 

491 FN_WHEN_ADDED_BATCH_UTC, 

492 comment="(SERVER) Date/time of the upload batch that added this " 

493 "row (DATETIME in UTC)", 

494 ) 

495 

496 # noinspection PyMethodParameters 

497 _adding_user_id: Mapped[Optional[int]] = mapped_column( 

498 FN_ADDING_USER_ID, 

499 ForeignKey("_security_users.id"), 

500 comment="(SERVER) ID of user that added this row", 

501 ) 

502 

503 # noinspection PyMethodParameters 

504 _when_removed_exact: Mapped[Optional[Pendulum]] = mapped_column( 

505 FN_WHEN_REMOVED_EXACT, 

506 PendulumDateTimeAsIsoTextColType, 

507 comment="(SERVER) Date/time this row was removed, i.e. made " 

508 "not current (ISO 8601)", 

509 ) 

510 

511 # noinspection PyMethodParameters 

512 _when_removed_batch_utc: Mapped[Optional[datetime.datetime]] = ( 

513 mapped_column( 

514 FN_WHEN_REMOVED_BATCH_UTC, 

515 comment="(SERVER) Date/time of the upload batch that removed " 

516 "this row (DATETIME in UTC)", 

517 ) 

518 ) 

519 

520 _removing_user_id: Mapped[Optional[int]] = mapped_column( 

521 FN_REMOVING_USER_ID, 

522 ForeignKey("_security_users.id"), 

523 comment="(SERVER) ID of user that removed this row", 

524 ) 

525 

526 # noinspection PyMethodParameters 

527 _preserving_user_id: Mapped[Optional[int]] = mapped_column( 

528 FN_PRESERVING_USER_ID, 

529 ForeignKey("_security_users.id"), 

530 comment="(SERVER) ID of user that preserved this row", 

531 ) 

532 

533 # noinspection PyMethodParameters 

534 _forcibly_preserved: Mapped[Optional[bool]] = mapped_column( 

535 FN_FORCIBLY_PRESERVED, 

536 default=False, 

537 comment="(SERVER) Forcibly preserved by superuser (rather than " 

538 "normally preserved by tablet)?", 

539 ) 

540 

541 # noinspection PyMethodParameters 

542 _predecessor_pk: Mapped[Optional[int]] = mapped_column( 

543 FN_PREDECESSOR_PK, 

544 comment="(SERVER) PK of predecessor record, prior to modification", 

545 ) 

546 

547 # noinspection PyMethodParameters 

548 _successor_pk: Mapped[Optional[int]] = mapped_column( 

549 FN_SUCCESSOR_PK, 

550 comment="(SERVER) PK of successor record (after modification) " 

551 "or NULL (whilst live, or after deletion)", 

552 ) 

553 

554 # noinspection PyMethodParameters 

555 _manually_erased: Mapped[Optional[bool]] = mapped_column( 

556 FN_MANUALLY_ERASED, 

557 default=False, 

558 comment="(SERVER) Record manually erased (content destroyed)?", 

559 ) 

560 

561 # noinspection PyMethodParameters 

562 _manually_erased_at: Mapped[Optional[Pendulum]] = mapped_column( 

563 FN_MANUALLY_ERASED_AT, 

564 PendulumDateTimeAsIsoTextColType, 

565 comment="(SERVER) Date/time of manual erasure (ISO 8601)", 

566 ) 

567 

568 # noinspection PyMethodParameters 

569 _manually_erasing_user_id: Mapped[Optional[int]] = mapped_column( 

570 FN_MANUALLY_ERASING_USER_ID, 

571 ForeignKey("_security_users.id"), 

572 comment="(SERVER) ID of user that erased this row manually", 

573 ) 

574 

575 # noinspection PyMethodParameters 

576 _camcops_version: Mapped[Optional[Version]] = mapped_column( 

577 FN_CAMCOPS_VERSION, 

578 SemanticVersionColType, 

579 default=CAMCOPS_SERVER_VERSION, 

580 comment="(SERVER) CamCOPS version number of the uploading device", 

581 ) 

582 

583 # noinspection PyMethodParameters 

584 _addition_pending: Mapped[bool] = mapped_column( 

585 FN_ADDITION_PENDING, 

586 default=False, 

587 comment="(SERVER) Addition pending?", 

588 ) 

589 

590 # noinspection PyMethodParameters 

591 _removal_pending: Mapped[Optional[bool]] = mapped_column( 

592 FN_REMOVAL_PENDING, 

593 default=False, 

594 comment="(SERVER) Removal pending?", 

595 ) 

596 

597 # noinspection PyMethodParameters 

598 _group_id: Mapped[int] = mapped_column( 

599 FN_GROUP_ID, 

600 ForeignKey("_security_groups.id"), 

601 index=True, 

602 comment="(SERVER) ID of group to which this record belongs", 

603 ) 

604 

605 # ------------------------------------------------------------------------- 

606 # Fields that *all* client tables have: 

607 # ------------------------------------------------------------------------- 

608 

609 # noinspection PyMethodParameters 

610 id: Mapped[int] = mapped_column( 

611 TABLET_ID_FIELD, 

612 index=True, 

613 comment="(TASK) Primary key (task ID) on the tablet device", 

614 ) 

615 

616 # noinspection PyMethodParameters 

617 when_last_modified: Mapped[Optional[Pendulum]] = mapped_column( 

618 CLIENT_DATE_FIELD, 

619 PendulumDateTimeAsIsoTextColType, 

620 index=True, # ... as used by database upload script 

621 comment="(STANDARD) Date/time this row was last modified on the " 

622 "source tablet device (ISO 8601)", 

623 ) 

624 

625 # noinspection PyMethodParameters 

626 _move_off_tablet: Mapped[Optional[bool]] = mapped_column( 

627 MOVE_OFF_TABLET_FIELD, 

628 default=False, 

629 comment="(SERVER/TABLET) Record-specific preservation pending?", 

630 ) 

631 

632 # ------------------------------------------------------------------------- 

633 # Relationships 

634 # ------------------------------------------------------------------------- 

635 

636 # noinspection PyMethodParameters 

637 @declared_attr 

638 def _device(cls) -> Mapped["Device"]: 

639 return relationship("Device") 

640 

641 # noinspection PyMethodParameters 

642 @declared_attr 

643 def _adding_user(cls) -> Mapped["User"]: 

644 return relationship("User", foreign_keys=[cls._adding_user_id]) # type: ignore[list-item] # noqa: E501 

645 

646 # noinspection PyMethodParameters 

647 @declared_attr 

648 def _removing_user(cls) -> Mapped["User"]: 

649 return relationship("User", foreign_keys=[cls._removing_user_id]) # type: ignore[list-item] # noqa: E501 

650 

651 # noinspection PyMethodParameters 

652 @declared_attr 

653 def _preserving_user(cls) -> Mapped["User"]: 

654 return relationship("User", foreign_keys=[cls._preserving_user_id]) # type: ignore[list-item] # noqa: E501 

655 

656 # noinspection PyMethodParameters 

657 @declared_attr 

658 def _manually_erasing_user(cls) -> Mapped["User"]: 

659 return relationship( 

660 "User", foreign_keys=[cls._manually_erasing_user_id] # type: ignore[list-item] # noqa: E501 

661 ) 

662 

663 # noinspection PyMethodParameters 

664 @declared_attr 

665 def _group(cls) -> Mapped["Group"]: 

666 return relationship("Group", foreign_keys=[cls._group_id]) # type: ignore[list-item] # noqa: E501 

667 

668 # ------------------------------------------------------------------------- 

669 # Fetching attributes 

670 # ------------------------------------------------------------------------- 

671 

672 @property 

673 def pk(self) -> Optional[int]: 

674 """ 

675 Returns the (server) primary key of this record. 

676 """ 

677 return self._pk 

678 

679 @property 

680 def era(self) -> Optional[str]: 

681 """ 

682 Returns the era of this record (a text representation of the date/time 

683 of the point of record finalization, or ``NOW`` if the record is still 

684 present on the client device). 

685 """ 

686 return self._era 

687 

688 @property 

689 def device_id(self) -> Optional[int]: 

690 """ 

691 Returns the client device ID of this record. 

692 """ 

693 return self._device_id 

694 

695 @property 

696 def group_id(self) -> Optional[int]: 

697 """ 

698 Returns the group ID of this record. 

699 """ 

700 return self._group_id 

701 

702 # ------------------------------------------------------------------------- 

703 # Other universal properties 

704 # ------------------------------------------------------------------------- 

705 

706 def is_live_on_tablet(self) -> bool: 

707 """ 

708 Is the record live on a tablet (not finalized)? 

709 """ 

710 return self._era == ERA_NOW 

711 

712 def is_finalized(self) -> bool: 

713 """ 

714 Is the record finalized (no longer available to be edited on the 

715 client device), and therefore (if required) editable on the server? 

716 """ 

717 return not self.is_live_on_tablet() 

718 

719 def created_on_server(self, req: "CamcopsRequest") -> bool: 

720 """ 

721 Was this record created on the server? 

722 """ 

723 from camcops_server.cc_modules.cc_device import ( 

724 Device, 

725 ) # delayed import 

726 

727 server_device = Device.get_server_device(req.dbsession) 

728 return self._era == ERA_NOW and self._device_id == server_device.id 

729 

730 # ------------------------------------------------------------------------- 

731 # Autoscanning objects and their relationships 

732 # ------------------------------------------------------------------------- 

733 

734 def _get_xml_root( 

735 self, req: "CamcopsRequest", options: TaskExportOptions 

736 ) -> XmlElement: 

737 """ 

738 Called to create an XML root object for records ancillary to Task 

739 objects. Tasks themselves use a more complex mechanism. 

740 

741 Args: 

742 req: a :class:`camcops_server.cc_modules.cc_request.CamcopsRequest` 

743 options: a :class:`camcops_server.cc_modules.cc_simpleobjects.TaskExportOptions` 

744 """ # noqa 

745 # "__tablename__" will make the type checker complain, as we're 

746 # defining a function for a mixin that assumes it's mixed in to a 

747 # SQLAlchemy Base-derived class 

748 # noinspection PyUnresolvedReferences 

749 return XmlElement( 

750 name=self.__tablename__, 

751 value=self._get_xml_branches(req=req, options=options), 

752 ) 

753 

754 def _get_xml_branches( 

755 self, req: "CamcopsRequest", options: TaskExportOptions 

756 ) -> List[XmlElement]: 

757 """ 

758 Gets the values of SQLAlchemy columns as XmlElement objects. 

759 Optionally, find any SQLAlchemy relationships that are relationships 

760 to Blob objects, and include them too. 

761 

762 Used by :func:`_get_xml_root` above, but also by Tasks themselves. 

763 

764 Args: 

765 req: a :class:`camcops_server.cc_modules.cc_request.CamcopsRequest` 

766 options: a :class:`camcops_server.cc_modules.cc_simpleobjects.TaskExportOptions` 

767 """ # noqa 

768 # log.debug("_get_xml_branches for {!r}", self) 

769 options = options or TaskExportOptions( 

770 xml_include_plain_columns=True, 

771 xml_include_calculated=True, 

772 xml_sort_by_name=True, 

773 ) 

774 branches = [] # type: List[XmlElement] 

775 if options.xml_with_header_comments: 

776 branches.append(XML_COMMENT_STORED) 

777 if options.xml_include_plain_columns: 

778 new_branches = make_xml_branches_from_columns( 

779 self, skip_fields=options.xml_skip_fields 

780 ) 

781 if options.xml_sort_by_name: 

782 new_branches.sort(key=lambda el: el.name) 

783 branches += new_branches 

784 if options.include_blobs: 

785 new_branches = make_xml_branches_from_blobs( 

786 req, self, skip_fields=options.xml_skip_fields 

787 ) 

788 if options.xml_sort_by_name: 

789 new_branches.sort(key=lambda el: el.name) 

790 branches += new_branches 

791 # Calculated 

792 if options.xml_include_calculated: 

793 if options.xml_with_header_comments: 

794 branches.append(XML_COMMENT_CALCULATED) 

795 branches.extend( 

796 make_xml_branches_from_summaries( 

797 self.get_summaries(req), 

798 skip_fields=options.xml_skip_fields, 

799 sort_by_name=options.xml_sort_by_name, 

800 ) 

801 ) 

802 # log.debug("... branches for {!r}: {!r}", self, branches) 

803 return branches 

804 

805 def _get_core_spreadsheet_page( 

806 self, req: "CamcopsRequest", heading_prefix: str = "" 

807 ) -> SpreadsheetPage: 

808 """ 

809 Returns a single-row 

810 :class:`camcops_server.cc_modules.cc_spreadsheet.SpreadsheetPage`, like 

811 an Excel "sheet", representing this record. (It may be combined with 

812 others later to produce a multi-row spreadsheet.) 

813 """ 

814 row = OrderedDict() 

815 for attrname, column in gen_columns(self): 

816 row[heading_prefix + attrname] = getattr(self, attrname) 

817 for s in self.get_summaries(req): 

818 row[heading_prefix + s.name] = s.value 

819 return SpreadsheetPage(name=self.__tablename__, rows=[row]) 

820 

821 def _get_core_spreadsheet_schema( 

822 self, table_name: str = "", column_name_prefix: str = "" 

823 ) -> Set[SummarySchemaInfo]: 

824 """ 

825 Returns schema information compatible with 

826 :func:`_get_core_spreadsheet_page`. 

827 """ 

828 return set( 

829 SummarySchemaInfo.from_column( 

830 column, 

831 table_name=table_name, 

832 column_name_prefix=column_name_prefix, 

833 ) 

834 for _, column in gen_columns(self) 

835 ) 

836 

837 # ------------------------------------------------------------------------- 

838 # Erasing (overwriting data, not deleting the database records) 

839 # ------------------------------------------------------------------------- 

840 

841 def manually_erase_with_dependants(self, req: "CamcopsRequest") -> None: 

842 """ 

843 Manually erases a standard record and marks it so erased. Iterates 

844 through any dependants and does likewise to them. 

845 

846 The object remains ``_current`` (if it was), as a placeholder, but its 

847 contents are wiped. 

848 

849 WRITES TO THE DATABASE. 

850 """ 

851 if self._manually_erased or self._pk is None or self._era == ERA_NOW: 

852 # ... _manually_erased: don't do it twice 

853 # ... _pk: basic sanity check 

854 # ... _era: don't erase things that are current on the tablet 

855 return 

856 # 1. "Erase my dependants" 

857 for ancillary in self.gen_ancillary_instances_even_noncurrent(): 

858 ancillary.manually_erase_with_dependants(req) 

859 for blob in self.gen_blobs_even_noncurrent(): 

860 blob.manually_erase_with_dependants(req) 

861 # 2. "Erase me" 

862 erasure_attrs = [] # type: List[str] 

863 for attrname, column in gen_columns(self): 

864 if attrname.startswith("_"): # system field 

865 continue 

866 if not column.nullable: # this should cover FKs 

867 continue 

868 if column.foreign_keys: # ... but to be sure... 

869 continue 

870 erasure_attrs.append(attrname) 

871 for attrname in erasure_attrs: 

872 setattr(self, attrname, None) 

873 self._current = False 

874 self._manually_erased = True 

875 self._manually_erased_at = req.now 

876 self._manually_erasing_user_id = req.user_id 

877 

878 def delete_with_dependants(self, req: "CamcopsRequest") -> None: 

879 """ 

880 Deletes (completely from the database) this record and any 

881 dependant records. 

882 """ 

883 if self._pk is None: 

884 return 

885 # 1. "Delete my dependants" 

886 for ancillary in self.gen_ancillary_instances_even_noncurrent(): 

887 ancillary.delete_with_dependants(req) 

888 for blob in self.gen_blobs_even_noncurrent(): 

889 blob.delete_with_dependants(req) 

890 # 2. "Delete me" 

891 dbsession = SqlASession.object_session(self) 

892 dbsession.delete(self) 

893 

894 def gen_attrname_ancillary_pairs( 

895 self, 

896 ) -> Generator[Tuple[str, "GenericTabletRecordMixin"], None, None]: 

897 """ 

898 Iterates through and yields all ``_current`` "ancillary" objects 

899 (typically: records of subtables). 

900 

901 Yields tuples of ``(attrname, related_record)``. 

902 """ 

903 for attrname, rel_prop, rel_cls in gen_ancillary_relationships(self): 

904 if rel_prop.uselist: 

905 ancillaries = getattr( 

906 self, attrname 

907 ) # type: List[GenericTabletRecordMixin] 

908 else: 

909 ancillaries = [ # type: ignore[no-redef] 

910 getattr(self, attrname) 

911 ] # type: List[GenericTabletRecordMixin] 

912 for ancillary in ancillaries: 

913 if ancillary is None: 

914 continue 

915 yield attrname, ancillary 

916 

917 def gen_ancillary_instances( 

918 self, 

919 ) -> Generator["GenericTabletRecordMixin", None, None]: 

920 """ 

921 Generates all ``_current`` ancillary objects of this object. 

922 """ 

923 for attrname, ancillary in self.gen_attrname_ancillary_pairs(): 

924 yield ancillary 

925 

926 def gen_ancillary_instances_even_noncurrent( 

927 self, 

928 ) -> Generator["GenericTabletRecordMixin", None, None]: 

929 """ 

930 Generates all ancillary objects of this object, even non-current 

931 ones. 

932 """ 

933 for lineage_member in self._gen_unique_lineage_objects( 

934 self.gen_ancillary_instances() 

935 ): 

936 yield lineage_member 

937 

938 def gen_blobs(self) -> Generator["Blob", None, None]: 

939 """ 

940 Generate all ``_current`` BLOBs owned by this object. 

941 """ 

942 for id_attrname, column in gen_camcops_blob_columns(self): 

943 relationship_attr = column.info.get( 

944 COLATTR_BLOB_RELATIONSHIP_ATTR_NAME 

945 ) 

946 blob = getattr(self, relationship_attr) 

947 if blob is None: 

948 continue 

949 yield blob 

950 

951 def gen_blobs_even_noncurrent(self) -> Generator["Blob", None, None]: 

952 """ 

953 Generates all BLOBs owned by this object, even non-current ones. 

954 """ 

955 for lineage_member in self._gen_unique_lineage_objects( # type: ignore[assignment] # noqa: E501 

956 self.gen_blobs() 

957 ): # type: "Blob" 

958 yield lineage_member 

959 

960 def get_lineage(self) -> List["GenericTabletRecordMixin"]: 

961 """ 

962 Returns all records that are part of the same "lineage", that is: 

963 

964 - of the same class; 

965 - matching on id/device_id/era; 

966 - including both current and any historical non-current versions. 

967 

968 Will include the "self" object. 

969 

970 """ 

971 dbsession = SqlASession.object_session(self) 

972 cls = self.__class__ 

973 q = ( 

974 dbsession.query(cls) 

975 .filter(cls.id == self.id) 

976 .filter(cls._device_id == self._device_id) 

977 .filter(cls._era == self._era) 

978 ) 

979 return list(q) 

980 

981 @staticmethod 

982 def _gen_unique_lineage_objects( 

983 collection: Iterable["GenericTabletRecordMixin"], 

984 ) -> Generator["GenericTabletRecordMixin", None, None]: 

985 """ 

986 Given an iterable of database records, generate all related lineage 

987 objects for each of them (via :meth:`get_lineage`) that are unique by 

988 PK. 

989 """ 

990 seen_pks = set() # type: Set[int] 

991 for item in collection: 

992 if item is None: 

993 continue 

994 for lineage_member in item.get_lineage():