db.py

   1 from PySide2 import QtSql as qts, QtWidgets
   2 from PySide2 import QtWidgets as qtw
   3 from PySide2 import QtCore as qtc
   4
   5 import sys
   6 import os
   7 from os import path
   8
   9
  10 # noinspection PyTypeChecker
  11 from utils import resource_path
  12
  13
  14 class Database(qtc.QObject):
  15     __instance = None
  16
  17     @staticmethod
  18     def get_instance(mainWindow):
  19         if Database.__instance is None:
  20             Database(mainWindow)
  21         return Database.__instance
  22
  23     def get_db(self):
  24         return self.db
  25
  26     def create_db(self):
  27         if not self.db.open():
  28             self.show_error(
  29                 self.tr('Error'),
  30                 'open db: ' + self.db.lastError().text()
  31             )
  32             sys.exit(1)
  33         all_sqls = open(resource_path('db/database.sql'), encoding='utf8').read().split(';')
  34         for sql in all_sqls:
  35             query = qts.QSqlQuery(self.db)
  36             query.prepare(sql)
  37             if not query.exec_():
  38                 self.show_error(
  39                     self.tr('Error'),
  40                     'creating db: ' + self.db.lastError().text()
  41                 )
  42                 sys.exit(1)
  43
  44     def __init__(self, mainWindow):
  45         super().__init__()
  46         if Database.__instance is not None:
  47             raise Exception("This class is a singleton!")
  48         self.mainWindow = mainWindow
  49         self.db = qts.QSqlDatabase.addDatabase('QSQLITE')
  50         db_path = mainWindow.settings.value('db_path', None, type=str)
  51         if db_path is None or not path.exists(db_path):
  52             db_path = QtWidgets.QFileDialog.getExistingDirectory(mainWindow, 'Select a folder to store your data', '.', QtWidgets.QFileDialog.ShowDirsOnly)
  53             if db_path is None or len(db_path.strip()) == 0:
  54                 exit(1)
  55             mainWindow.settings.setValue('db_path', db_path)
  56         if not path.exists(db_path):
  57             os.mkdir(db_path)
  58         self.db.setDatabaseName('{}/minimizer.db'.format(db_path))
  59         if not os.path.exists('{}/minimizer.db'.format(db_path)):
  60             self.create_db()
  61         else:
  62             if not self.db.open():
  63                 self.show_error(
  64                     self.tr('DB Connection Error'),
  65                     self.tr('Could not open database file {}').format(self.db.lastError().text())
  66                 )
  67                 sys.exit(1)
  68         required_tables = {'trials', 'discarded_identifiers', 'treatments', 'factors', 'levels', 'subjects',
  69                            'subject_levels', 'preloads'}
  70         missing_tables = required_tables - set(self.db.tables())
  71         if missing_tables:
  72             self.show_error(
  73                 self.tr('DB Integrity Error'),
  74                 self.tr('Missing tables, please repair DB {}').format(missing_tables)
  75             )
  76             sys.exit(1)
  77         if not self.checkForeignKeys():
  78             sys.exit(1)
  79         Database.__instance = self
  80
  81     def checkForeignKeys(self):
  82         query = qts.QSqlQuery(self.db)
  83         query.prepare('PRAGMA foreign_keys;')
  84         if not query.exec_():
  85             self.show_query_error(query)
  86         res = query.next()
  87         if not res:
  88             self.show_query_error(query)
  89         if query.value(0):
  90             return True
  91         query.prepare('PRAGMA foreign_keys = ON;')
  92         if not query.exec_():
  93             self.show_query_error(query)
  94         query.prepare('PRAGMA foreign_keys;')
  95         query.exec_()
  96         if query.next():
  97             return query.value(0) > 0
  98         else:
  99             return False
 100
 101     def get_treatment_title(self, treatment_id):
 102         query = qts.QSqlQuery(self.db)
 103         query.prepare('SELECT title FROM treatments WHERE id = :treatment_id')
 104         query.bindValue(':treatment_id', treatment_id)
 105         if not query.exec_():
 106             self.show_query_error(query)
 107         query.next()
 108         return query.value('title')
 109
 110     def update_subject_treatment_id(self, subject_id, treatment_id):
 111         query = qts.QSqlQuery(self.db)
 112         query.prepare('''UPDATE subjects set treatment_id = :treatment_id,
 113         modified = CURRENT_TIMESTAMP WHERE id = :subject_id''')
 114         query.bindValue(':treatment_id', treatment_id)
 115         query.bindValue(':subject_id', subject_id)
 116         if not query.exec_():
 117             self.show_query_error(query)
 118
 119     def get_subject_treatment_id(self, subject_id):
 120         query = qts.QSqlQuery(self.db)
 121         query.prepare("SELECT treatment_id FROM subjects WHERE id = :subject_id")
 122         query.bindValue(':subject_id', subject_id)
 123         if not query.exec_():
 124             self.show_query_error(query)
 125         if not query.next():
 126             return None
 127         return query.value('treatment_id')
 128
 129     def get_subject(self, subject_id):
 130         query = qts.QSqlQuery(self.db)
 131         query.prepare("SELECT * FROM subjects WHERE id = :subject_id")
 132         query.bindValue(':subject_id', subject_id)
 133         if not query.exec_():
 134             self.show_query_error(query)
 135         if not query.next():
 136             return None
 137         return query
 138
 139     def get_subjects(self, trial_id):
 140         query = qts.QSqlQuery(self.db)
 141         query.prepare('''SELECT id, treatment_id, identifier_value, enrolled,
 142         modified FROM subjects WHERE trial_id = :trial_id''')
 143         query.bindValue(':trial_id', trial_id)
 144         if not query.exec_():
 145             self.show_query_error(query)
 146         subjects = []
 147         while query.next():
 148             subject = [
 149                 query.value('id'),
 150                 query.value('treatment_id'),
 151                 query.value('identifier_value'),
 152                 query.value('enrolled'),
 153                 query.value('modified')
 154             ]
 155             subjects.append(subject)
 156         return subjects
 157
 158     def get_subject_level_titles(self, subject_levels):
 159         query = qts.QSqlQuery(self.db)
 160         level_title = []
 161         for factor_id, level_id in subject_levels:
 162             query.prepare('SELECT title FROM levels WHERE id = :level_id')
 163             query.bindValue(':level_id', level_id)
 164             if not query.exec_():
 165                 self.show_query_error(query)
 166             if not query.next():
 167                 return False
 168             level_title.append(query.value('title'))
 169         return level_title
 170
 171     def get_subject_levels(self, subject_id):
 172         query = qts.QSqlQuery(self.db)
 173         query.prepare("SELECT factor_id, level_id FROM subject_levels WHERE subject_id = :subject_id ORDER BY id")
 174         query.bindValue(':subject_id', subject_id)
 175         if not query.exec_():
 176             self.show_query_error(query)
 177         subject_levels = []
 178         while query.next():
 179             subject_level = [query.value('factor_id'), query.value('level_id')]
 180             subject_levels.append(subject_level)
 181         return subject_levels
 182
 183     def get_freq(self, trial_id):
 184         subjects = self.get_subjects(trial_id)
 185         freq = self.get_empty_freq(trial_id)
 186         for subject in subjects:
 187             subject_levels = self.get_subject_levels(subject[0])
 188             for subject_level in subject_levels:
 189                 key = (subject[1], subject_level[0], subject_level[1])
 190                 freq[key] += 1
 191         return freq
 192
 193     def get_empty_freq(self, trial_id):
 194         freq = {}
 195         treatments = self.read_treatments(trial_id)
 196         factors = self.read_factors(trial_id)
 197         if not treatments or not factors:
 198             return
 199         for treatment in treatments:
 200             for factor in factors:
 201                 levels = self.factor_levels(factor[0])
 202                 for level in levels:
 203                     key = (treatment[0], factor[0], level[0])
 204                     if key in freq:
 205                         continue
 206                     freq[key] = 0
 207         return freq
 208
 209     def get_preload(self, trial_id):
 210         query = qts.QSqlQuery(self.db)
 211         preload = self.get_empty_freq(trial_id)
 212         query.prepare("SELECT treatment_id, factor_id, level_id, count FROM preloads WHERE trial_id = :trial_id")
 213         query.bindValue(':trial_id', trial_id)
 214         if not query.exec_():
 215             self.show_query_error(query)
 216         rows = []
 217         while query.next():
 218             row = [query.value('treatment_id'), query.value('factor_id'), query.value('level_id'), query.value('count')]
 219             rows.append(row)
 220         for row in rows:
 221             key = (row[0], row[1], row[2])
 222             preload[key] = row[3]
 223         return preload
 224
 225     def get_preload_with_freq(self, trial_id):
 226         freq = self.get_freq(trial_id)
 227         preload = self.get_preload(trial_id)
 228         if freq is None or preload is None:
 229             return None
 230         preload_with_freq = {}
 231         for key in freq:
 232             preload_with_freq[key] = freq[key] + preload[key]
 233         return preload_with_freq
 234
 235     def insert_level(self, trial_id, factor_id, title):
 236         query = qts.QSqlQuery(self.db)
 237         query.prepare('INSERT INTO levels (trial_id, factor_id, title)  VALUES (:trial_id, :factor_id, :title)')
 238         query.bindValue(':trial_id', trial_id)
 239         query.bindValue(':factor_id', factor_id)
 240         query.bindValue(':title', title)
 241         if not query.exec_():
 242             self.show_query_error(query)
 243         return query.lastInsertId()
 244
 245     def insert_factor(self, trial_id, title):
 246         query = qts.QSqlQuery(self.db)
 247         query.prepare('INSERT INTO factors (trial_id, title)  VALUES (:trial_id, :title)')
 248         query.bindValue(':title', title)
 249         query.bindValue(':trial_id', trial_id)
 250         if not query.exec_():
 251             self.show_query_error(query)
 252         return query.lastInsertId()
 253
 254     def insert_treatment(self, trial_id, title):
 255         query = qts.QSqlQuery(self.db)
 256         query.prepare('INSERT INTO treatments (trial_id, title)  VALUES (:trial_id, :title)')
 257         query.bindValue(':title', title)
 258         query.bindValue(':trial_id', trial_id)
 259         if not query.exec_():
 260             self.show_query_error(query)
 261         return query.lastInsertId()
 262
 263     def insert_trial(self, title):
 264         query = qts.QSqlQuery(self.db)
 265         query.prepare('INSERT INTO trials (title)  VALUES (:title)')
 266         query.bindValue(':title', title)
 267         if not query.exec_():
 268             self.show_query_error(query)
 269         return query.lastInsertId()
 270
 271     def delete_level(self, level_id):
 272         query = qts.QSqlQuery(self.db)
 273         query.prepare('DELETE FROM levels WHERE id = :id')
 274         query.bindValue(':id', level_id)
 275         if not query.exec_():
 276             self.show_query_error(query)
 277
 278     def delete_treatment(self, treatment_id):
 279         query = qts.QSqlQuery(self.db)
 280         query.prepare('DELETE FROM treatments WHERE id = :id')
 281         query.bindValue(':id', treatment_id)
 282         if not query.exec_():
 283             self.show_query_error(query)
 284
 285     def delete_factor(self, factor_id):
 286         query = qts.QSqlQuery(self.db)
 287         query.prepare('DELETE FROM factors WHERE id = :id')
 288         query.bindValue(':id', factor_id)
 289         if not query.exec_():
 290             self.show_query_error(query)
 291
 292     def delete_trial(self, trial_id):
 293         query = qts.QSqlQuery(self.db)
 294         query.prepare('DELETE FROM trials WHERE id = :id')
 295         query.bindValue(':id', trial_id)
 296         if not query.exec_():
 297             self.show_query_error(query)
 298
 299     def get_title(self, row_id, table):
 300         query = qts.QSqlQuery(self.db)
 301         query.prepare('SELECT title FROM {} WHERE id = :row_id'.format(table))
 302         query.bindValue(':row_id', row_id)
 303         if not query.exec_():
 304             self.show_query_error(query)
 305         query.next()
 306         return query.value('title')
 307
 308     def update_level(self, level_id, title):
 309         query = qts.QSqlQuery(self.db)
 310         query.prepare('UPDATE levels set title = :title WHERE id = :id')
 311         query.bindValue(':title', title)
 312         query.bindValue(':id', level_id)
 313         if not query.exec_():
 314             if self.show_query_error(query):
 315                 return self.get_title(level_id, 'levels')
 316
 317     def update_factor(self, factor_id, column, value):
 318         query = qts.QSqlQuery(self.db)
 319         query.prepare('UPDATE factors set {} = :value WHERE id = :id'.format(column))
 320         query.bindValue(':value', value)
 321         query.bindValue(':id', factor_id)
 322         if not query.exec_():
 323             if self.show_query_error(query):
 324                 return self.get_title(factor_id, 'factors')
 325
 326     def update_treatment(self, treatment_id, column, value):
 327         query = qts.QSqlQuery(self.db)
 328         query.prepare('UPDATE treatments set {} = :value WHERE id = :id'.format(column))
 329         query.bindValue(':value', value)
 330         query.bindValue(':id', treatment_id)
 331         if not query.exec_():
 332             if self.show_query_error(query):
 333                 return self.get_title(treatment_id, 'treatments')
 334         else:
 335             print(query.result())
 336
 337
 338     def update_trial(self, trial_id, column, value):
 339         query = qts.QSqlQuery(self.db)
 340         query.prepare('UPDATE trials SET {} = :value, modified = CURRENT_TIMESTAMP WHERE id = :id'.format(column))
 341         query.bindValue(':value', value)
 342         query.bindValue(':id', trial_id)
 343         if not query.exec_():
 344             if self.show_query_error(query):
 345                 return self.get_title(trial_id, 'trials')
 346
 347     def load_levels(self, factor_id):
 348         query = qts.QSqlQuery(self.db)
 349         query.prepare('SELECT * FROM levels WHERE factor_id = :factor_id ORDER BY id')
 350         query.bindValue(':factor_id', factor_id)
 351         if not query.exec_():
 352             self.show_query_error(query)
 353         return query
 354
 355     def load_treatments(self, trial_id):
 356         query = qts.QSqlQuery(self.db)
 357         query.prepare('SELECT * FROM treatments WHERE trial_id = :trial_id ORDER BY id')
 358         query.bindValue(':trial_id', trial_id)
 359         if not query.exec_():
 360             self.show_query_error(query)
 361         return query
 362
 363     def load_factors(self, trial_id):
 364         query = qts.QSqlQuery(self.db)
 365         query.prepare('SELECT * FROM factors WHERE trial_id = :trial_id ORDER BY id')
 366         query.bindValue(':trial_id', trial_id)
 367         if not query.exec_():
 368             self.show_query_error(query)
 369         return query
 370
 371     def get_levels(self, factor_id):
 372         query = qts.QSqlQuery(self.db)
 373         query.prepare('SELECT title FROM levels WHERE factor_id = :factor_id ORDER BY id')
 374         query.bindValue(':factor_id', factor_id)
 375         if not query.exec_():
 376             qtw.QMessageBox.critical(
 377                 self.parent(),
 378                 self.tr('DB read Error'),
 379                 query.lastError().text()
 380             )
 381             return
 382         levels = []
 383         while query.next():
 384             levels.append(query.value('title'))
 385         return ', '.join(levels)
 386
 387     def get_used_identifiers(self, trial_id):
 388         query = qts.QSqlQuery(self.db)
 389         query.prepare('SELECT identifier_value FROM subjects WHERE trial_id = :trial_id')
 390         query.bindValue(':trial_id', trial_id)
 391         if not query.exec_():
 392             self.show_query_error(query)
 393         ret = []
 394         while query.next():
 395             ret.append(query.value('identifier_value'))
 396         return ret
 397
 398     def get_discarded_identifiers(self, trial_id):
 399         query = qts.QSqlQuery(self.db)
 400         query.prepare('SELECT identifier FROM discarded_identifiers WHERE trial_id = :trial_id')
 401         query.bindValue(':trial_id', trial_id)
 402         if not query.exec_():
 403             self.show_query_error(query)
 404         ret = []
 405         while query.next():
 406             ret.append(query.value('identifier'))
 407         return ret
 408
 409     def get_count_discarded_identifiers(self, trial_id):
 410         query = qts.QSqlQuery(self.db)
 411         query.prepare('SELECT COUNT(*) FROM discarded_identifiers WHERE trial_id = :trial_id')
 412         query.bindValue(':trial_id', trial_id)
 413         if not query.exec_():
 414             self.show_query_error(query)
 415         query.next()
 416         return query.value(0)
 417
 418     def get_num_treatments(self, trial_id):
 419         query = qts.QSqlQuery(self.db)
 420         query.prepare('SELECT COUNT(*) FROM treatments WHERE trial_id = :trial_id')
 421         query.bindValue(':trial_id', trial_id)
 422         if not query.exec_():
 423             self.show_query_error(query)
 424         query.next()
 425         return query.value(0)
 426
 427     def get_factor_level_indices(self, factor_id):
 428         query = qts.QSqlQuery(self.db)
 429         query.prepare('SELECT COUNT(*) FROM levels WHERE factor_id = :factor_id')
 430         query.bindValue(':factor_id', factor_id)
 431         if not query.exec_():
 432             self.show_query_error(query)
 433         query.next()
 434         return list(range(query.value(0)))
 435
 436     def get_factor_weights(self, trial_id):
 437         factors = self.read_factors(trial_id)
 438         if not factors:
 439             return
 440         return [factor[2] for factor in factors]
 441
 442     def get_factors_level_indices(self, trial_id):
 443         factors = self.read_factors(trial_id)
 444         if not factors:
 445             return
 446         fli = []
 447         for factor in factors:
 448             fli.append(self.get_factor_level_indices(factor[0]))
 449         return fli
 450
 451     def get_allocation_ratios(self, trial_id):
 452         treatments = self.read_treatments(trial_id)
 453         if not treatments:
 454             return False
 455         return [treatment[2] for treatment in treatments]
 456
 457     def get_treatments_id_dict(self, trial_id):
 458         query = qts.QSqlQuery(self.db)
 459         query.prepare('SELECT id from treatments WHERE trial_id = :trial_id ORDER BY id')
 460         query.bindValue(':trial_id', trial_id)
 461         if not query.exec_():
 462             self.show_query_error(query)
 463         rows = []
 464         while query.next():
 465             rows.append(query.value('id'))
 466         return {row: i for i, row in enumerate(rows)}
 467
 468     def get_allocations(self, trial_id):
 469         treatment_dict = self.get_treatments_id_dict(trial_id)
 470         allocations = []
 471         subjects = self.get_subjects(trial_id)
 472         for subject in subjects:
 473             allocation = {'allocation': treatment_dict[subject[1]], 'levels': [], 'UI': subject[2]}
 474             levels = self.get_subject_levels(subject[0])
 475             for level in levels:
 476                 level_dict = self.get_factor_level_dict(level[0])
 477                 allocation['levels'].append(level_dict[level[1]])
 478             allocations.append(allocation)
 479         return allocations
 480
 481     def get_min_allocation_ratio_group_index(self, trial_id):
 482         query = qts.QSqlQuery(self.db)
 483         query.prepare('SELECT id FROM treatments WHERE trial_id = :trial_id AND ratio = (SELECT min(ratio) FROM treatments);')
 484         query.bindValue(':trial_id', trial_id)
 485         if not query.exec_():
 486             self.show_query_error(query)
 487         query.next()
 488         result = query.value('id')
 489         treatment_dict = self.get_treatments_id_dict(trial_id)
 490         return treatment_dict[result]
 491
 492     def get_factor_level_dict(self, factor_id):
 493         query = qts.QSqlQuery(self.db)
 494         query.prepare('SELECT id FROM levels WHERE factor_id = :factor_id ORDER BY id')
 495         query.bindValue(':factor_id', factor_id)
 496         if not query.exec_():
 497             self.show_query_error(query)
 498         rows = []
 499         while query.next():
 500             rows.append(query.value('id'))
 501         return {row: i for i, row in enumerate(rows)}
 502
 503     def update_trial_setting(self, trial_id, ui):
 504         title = ui.trialTitleLineEdit.text()
 505         if len(title.strip()) == 0:
 506             return
 507         code = ui.trialCodeLineEdit.text()
 508         prob_method = ui.trialProbMethodComboBox.currentIndex()
 509         base_prob = ui.trialBaseProbabilitySlider.value() / 100.0
 510         dist_method = ui.trialDistanceMethodComboBox.currentIndex()
 511         identifier_type = ui.trialIdentifierTypeComboBox.currentIndex()
 512         identifier_order = ui.trialIentifierOrderComboBox.currentIndex()
 513         identifier_length = ui.trialIdentifierLengthSpinBox.value()
 514         recycle_ids = 1 if ui.trialRecycleIdsCheckBox.isChecked() else 0
 515         new_subject_random = 1 if ui.trialNewSubjectRandomCheckBox.isChecked() else 0
 516         arms_weight = ui.trialArmsWeightDoubleSlider.value()
 517         query = qts.QSqlQuery(self.db)
 518         query.prepare('UPDATE trials set '
 519                       'title = :title, '
 520                       'code = :code, '
 521                       'modified = CURRENT_TIMESTAMP, '
 522                       'prob_method = :prob_method, '
 523                       'base_prob = :base_prob, '
 524                       'dist_method = :dist_method, '
 525                       'identifier_type = :identifier_type, '
 526                       'identifier_order = :identifier_order, '
 527                       'identifier_length = :identifier_length, '
 528                       'recycle_ids = :recycle_ids, '
 529                       'new_subject_random = :new_subject_random, '
 530                       'arms_weight = :arms_weight '
 531                       'WHERE id = :id'
 532                       )
 533         query.bindValue(':title', title)
 534         query.bindValue(':code', code)
 535         query.bindValue(':prob_method', prob_method)
 536         query.bindValue(':base_prob', base_prob)
 537         query.bindValue(':dist_method', dist_method)
 538         query.bindValue(':identifier_type', identifier_type)
 539         query.bindValue(':identifier_order', identifier_order)
 540         query.bindValue(':identifier_length', identifier_length)
 541         query.bindValue(':recycle_ids', recycle_ids)
 542         query.bindValue(':new_subject_random', new_subject_random)
 543         query.bindValue(':arms_weight', arms_weight)
 544         query.bindValue(':id', trial_id)
 545         if not query.exec_():
 546             self.show_query_error(query)
 547
 548     def get_trial_setting(self, trial_id):
 549         query = qts.QSqlQuery(self.db)
 550         query.prepare('SELECT * FROM trials WHERE id = :id')
 551         query.bindValue(':id', trial_id)
 552         if not query.exec_():
 553             self.show_query_error(query)
 554         if not query.next():
 555             return False
 556         return query
 557
 558     def clear_trial_setting(self, ui):
 559         ui.trialCreatedValueLabel.setText('')
 560         ui.trialModifiedValueLabel.setText('')
 561         ui.trialTitleLineEdit.setText('')
 562         ui.trialCodeLineEdit.setText('')
 563         ui.trialProbMethodComboBox.setCurrentIndex(0)
 564         ui.trialBaseProbabilitySlider.setValue(0.7 * 100)
 565         ui.trialDistanceMethodComboBox.setCurrentIndex(0)
 566         ui.trialIdentifierTypeComboBox.setCurrentIndex(0)
 567         ui.trialIentifierOrderComboBox.setCurrentIndex(0)
 568         ui.trialIdentifierLengthSpinBox.setValue(3)
 569         ui.trialRecycleIdsCheckBox.setChecked(False)
 570         ui.trialNewSubjectRandomCheckBox.setChecked(False)
 571         ui.trialArmsWeightDoubleSlider.setValue(1.0)
 572
 573     def map_trial_setting(self, query, ui):
 574         ui.trialCreatedValueLabel.setText(query.value('created'))
 575         ui.trialModifiedValueLabel.setText(query.value('modified'))
 576         ui.trialTitleLineEdit.setText(query.value('title'))
 577         ui.trialCodeLineEdit.setText(query.value('code'))
 578         ui.trialProbMethodComboBox.setCurrentIndex(query.value('prob_method'))
 579         ui.trialBaseProbabilitySlider.setValue(query.value('base_prob') * 100)
 580         ui.trialDistanceMethodComboBox.setCurrentIndex(query.value('dist_method'))
 581         ui.trialIdentifierTypeComboBox.setCurrentIndex(query.value('identifier_type'))
 582         ui.trialIentifierOrderComboBox.setCurrentIndex(query.value('identifier_order'))
 583         ui.trialIdentifierLengthSpinBox.setValue(query.value('identifier_length'))
 584         ui.trialRecycleIdsCheckBox.setChecked(query.value('recycle_ids') != 0)
 585         ui.trialNewSubjectRandomCheckBox.setChecked(query.value('new_subject_random') != 0)
 586         ui.trialArmsWeightDoubleSlider.setValue(query.value('arms_weight'))
 587
 588     def load_trial_setting(self, trial_id, ui):
 589         self.clear_trial_setting(ui)
 590         query = self.get_trial_setting(trial_id)
 591         if not query:
 592             return
 593         self.map_trial_setting(query, ui)
 594
 595     def read_factors(self, trial_id):
 596         query = qts.QSqlQuery(self.db)
 597         query.prepare('SELECT id, title, weight FROM factors WHERE trial_id = :trial_id ORDER BY id')
 598         query.bindValue(':trial_id', trial_id)
 599         if not query.exec_():
 600             self.show_query_error(query)
 601         factors = []
 602         while query.next():
 603             factor = [query.value('id'), query.value('title'), query.value('weight')]
 604             factors.append(factor)
 605         return factors
 606
 607     def read_treatments(self, trial_id):
 608         query = qts.QSqlQuery(self.db)
 609         query.prepare('SELECT id, title, ratio FROM treatments WHERE trial_id = :trial_id ORDER BY id')
 610         query.bindValue(':trial_id', trial_id)
 611         if not query.exec_():
 612             self.show_query_error(query)
 613         treatments = []
 614         while query.next():
 615             treatment = [query.value('id'), query.value('title'), query.value('ratio')]
 616             treatments.append(treatment)
 617         return treatments
 618
 619     def get_factor_level(self, trial_id):
 620         factors = self.read_factors(trial_id)
 621         if not factors:
 622             return
 623         fl = []
 624         for factor in factors:
 625             query = qts.QSqlQuery(self.db)
 626             query.prepare('SELECT id, title FROM levels WHERE factor_id = :factor_id ORDER BY id')
 627             query.bindValue(':factor_id', factor[0])
 628             query.exec_()
 629             levels = []
 630             while query.next():
 631                 levels.append([query.value('id'), query.value('title')])
 632             fl.append(levels)
 633         return fl
 634
 635     def factor_levels(self, factor_id):
 636         query = qts.QSqlQuery(self.db)
 637         query.prepare("SELECT id, title FROM levels WHERE factor_id = :factor_id ORDER BY id")
 638         query.bindValue(':factor_id', factor_id)
 639         query.exec_()
 640         levels = []
 641         while query.next():
 642             levels.append([query.value('id'), query.value('title')])
 643         return levels
 644
 645     def insert_subject(self, identifier_value, treatment_id, trial_id):
 646         query = qts.QSqlQuery(self.db)
 647         query.prepare('''INSERT INTO subjects (trial_id, identifier_value, treatment_id)
 648         VALUES (:trial_id, :identifier_value, :treatment_id)''')
 649         query.bindValue(':trial_id', trial_id)
 650         query.bindValue(':identifier_value', identifier_value)
 651         query.bindValue(':treatment_id', treatment_id)
 652         if not query.exec_():
 653             self.show_query_error(query)
 654         return query.lastInsertId()
 655
 656     def insert_subject_levels(self, trial_id, subject_id, subject_levels):
 657         query = qts.QSqlQuery(self.db)
 658         for factor_id, level_id in subject_levels:
 659             query.prepare('''INSERT INTO subject_levels (trial_id, subject_id, factor_id, level_id)
 660         VALUES (:trial_id, :subject_id, :factor_id, :level_id);''')
 661             query.bindValue(':trial_id', trial_id)
 662             query.bindValue(':subject_id', subject_id)
 663             query.bindValue(':factor_id', factor_id)
 664             query.bindValue(':level_id', level_id)
 665             if not query.exec_():
 666                 self.show_query_error(query)
 667         return self.get_subject_level_titles(subject_levels)
 668
 669     def clear_preload(self, trial_id):
 670         query = qts.QSqlQuery(self.db)
 671         query.prepare('DELETE FROM preloads WHERE trial_id = :trial_id')
 672         query.bindValue(':trial_id', trial_id)
 673         query.exec_()
 674
 675     def delete_subject_levels(self, subject_id):
 676         query = qts.QSqlQuery(self.db)
 677         query.prepare('DELETE FROM subject_levels WHERE subject_id = :subject_id')
 678         query.bindValue(':subject_id', subject_id)
 679         if not query.exec_():
 680             self.show_query_error(query)
 681
 682     def delete_subject(self, subject_id, id_value=None, trial_id=None):
 683         query = qts.QSqlQuery(self.db)
 684         query.prepare('DELETE FROM subjects WHERE id = :subject_id;')
 685         query.bindValue(':subject_id', subject_id)
 686         if not query.exec_():
 687             self.show_query_error(query)
 688         if id_value is None:
 689             return
 690         query.prepare('''INSERT INTO discarded_identifiers (trial_id, identifier)
 691         VALUES (:trial_id, :identifier)''')
 692         query.bindValue(':trial_id', trial_id)
 693         query.bindValue(':identifier', id_value)
 694         if not query.exec_():
 695             self.show_query_error(query)
 696
 697     def delete_subjects(self, trial_id):
 698         query = qts.QSqlQuery(self.db)
 699         query.prepare('DELETE FROM subjects WHERE trial_id = :trial_id;')
 700         query.bindValue(':trial_id', trial_id)
 701         if not query.exec_():
 702             self.show_query_error(query)
 703         query.prepare('DELETE FROM discarded_identifiers WHERE trial_id = :trial_id')
 704         query.bindValue(':trial_id', trial_id)
 705         if not query.exec_():
 706             self.show_query_error(query)
 707
 708     def save_preload(self, trial_id, preload):
 709         query = qts.QSqlQuery(self.db)
 710         for key in preload:
 711             t, f, lv = key
 712             c = preload[key]
 713             query.prepare('''INSERT INTO preloads (trial_id, treatment_id, factor_id, level_id, count)
 714                         VALUES (:trial_id, :treatment_id, :factor_id, :level_id, :count)''')
 715             query.bindValue(':trial_id', trial_id)
 716             query.bindValue(':treatment_id', t)
 717             query.bindValue(':factor_id', f)
 718             query.bindValue(':level_id', lv)
 719             query.bindValue(':count', c)
 720             query.exec_()
 721
 722     def show_error(self, title, text):
 723         qtw.QMessageBox.critical(self.mainWindow, title, text)
 724
 725     def show_query_error(self, query):
 726         qtw.QMessageBox.critical(
 727             self.mainWindow,
 728             self.tr('DB Error'),
 729             query.lastError().text()
 730         )
 731         fatal = 'UNIQUE constraint failed' not in query.lastError().text()
 732         if fatal:
 733             sys.exit(1)
 734             return False
 735         else:
 736             return True
 737
 738     def has_preload(self, trial_id):
 739         query = qts.QSqlQuery(self.db)
 740         query.prepare('SELECT COUNT(*) FROM preloads WHERE trial_id = :trial_id')
 741         query.bindValue(':trial_id', trial_id)
 742         if not query.exec_():
 743             self.show_query_error(query)
 744         if not query.next():
 745             return False
 746         return query.value(0) > 0
 747
 748     def get_subject_count(self, trial_id):
 749         query = qts.QSqlQuery(self.db)
 750         query.prepare('SELECT COUNT(*) FROM subjects WHERE trial_id = :trial_id')
 751         query.bindValue(':trial_id', trial_id)
 752         if not query.exec_():
 753             self.show_query_error(query)
 754         if not query.next():
 755             return False
 756         return query.value(0)
 757
 758     def has_subject(self, trial_id):
 759         subject_count = self.get_subject_count(trial_id)
 760         if not subject_count:
 761             return False
 762         return subject_count > 0
 763
 764     def get_first_trial_setting(self):
 765         query = qts.QSqlQuery(self.db)
 766         query.prepare('SELECT min(id) FROM trials')
 767         if not query.exec_():
 768             self.show_query_error(query)
 769         if not query.next():
 770             return False
 771         trial_id = query.value(0)
 772         return self.get_trial_setting(trial_id)
 773
 774     def trial_title_exists(self, title):
 775         query = qts.QSqlQuery(self.db)
 776         query.prepare('SELECT COUNT(*) FROM trials WHERE title = :title')
 777         query.bindValue(':title', title)
 778         if not query.exec_():
 779             self.show_query_error(query)
 780         query.next()
 781         return query.value(0) > 0
 782
 783     def treatment_title_exists(self, title, trial_id):
 784         query = qts.QSqlQuery(self.db)
 785         query.prepare('SELECT COUNT(*) FROM treatments WHERE title = :title and trial_id = :trial_id')
 786         query.bindValue(':title', title)
 787         query.bindValue(':trial_id', trial_id)
 788         if not query.exec_():
 789             self.show_query_error(query)
 790         query.next()
 791         return query.value(0) > 0
 792
 793     def factor_title_exists(self, title, trial_id):
 794         query = qts.QSqlQuery(self.db)
 795         query.prepare('SELECT COUNT(*) FROM factors WHERE title = :title and trial_id = :trial_id')
 796         query.bindValue(':title', title)
 797         query.bindValue(':trial_id', trial_id)
 798         if not query.exec_():
 799             self.show_query_error(query)
 800         query.next()
 801         return query.value(0) > 0
 802
 803     def level_title_exists(self, title, factor_id):
 804         query = qts.QSqlQuery(self.db)
 805         query.prepare('SELECT COUNT(*) FROM levels WHERE title = :title and factor_id = :factor_id')
 806         query.bindValue(':title', title)
 807         query.bindValue(':factor_id', factor_id)
 808         if not query.exec_():
 809             self.show_query_error(query)
 810         query.next()
 811         return query.value(0) > 0