def commit(self, db, immediate=False): self.apply() now = time.time() if self.debug: print >> sys.stderr, "PREFS commit, immediate %s, need_commit %s, when %s" % (immediate, self.need_commit, (now - (self.commit_time + self.commit_delay))) if (self.need_commit and (immediate or self.commit_time + self.commit_delay < now)): if self.preferences: data = dbobj.Binary(cPickle.dumps(self.preferences)) else: data = None try: curs = db.cursor() try: dbobj.execute(curs, 'UPDATE users SET preferences=%s' ' WHERE user_id = %s', (data, self.user_id)) finally: curs.close() except dbobj.DatabaseError: db.rollback() raise else: db.commit() self.commit_time = now self.need_commit = False
def commit(self, db, immediate=False): self.apply() now = time.time() if self.debug: print >> sys.stderr, "PREFS commit, immediate %s, need_commit %s, when %s" % ( immediate, self.need_commit, (now - (self.commit_time + self.commit_delay))) if (self.need_commit and (immediate or self.commit_time + self.commit_delay < now)): if self.preferences: data = dbobj.Binary(cPickle.dumps(self.preferences)) else: data = None try: curs = db.cursor() try: dbobj.execute( curs, 'UPDATE users SET preferences=%s' ' WHERE user_id = %s', (data, self.user_id)) finally: curs.close() except dbobj.DatabaseError: db.rollback() raise else: db.commit() self.commit_time = now self.need_commit = False
def agg(self, cmd, *args): curs = self.db.cursor() try: dbobj.execute(curs, cmd, args) row = curs.fetchone() assert row is not None and len(row) == 1 return row[0] finally: curs.close()
def delete(self, name): self.db.lock_table(self.table, 'EXCLUSIVE') curs = self.db.cursor() try: dbobj.execute(curs, 'DELETE FROM %s WHERE name=%%s' % self.table, (name,)) if curs.rowcount == 0: raise NoFormError('No form %r' % name) finally: curs.close()
def update(db, person_id, *names): curs = db.cursor() try: dbobj.execute(curs, 'DELETE FROM person_phonetics WHERE person_id=%s', (person_id,)) for mp in itertools.chain(*encode_phones(*names)): dbobj.execute(curs, 'INSERT INTO person_phonetics VALUES (%s, %s)', (person_id, mp)) finally: curs.close()
def update(db, person_id, *names): curs = db.cursor() try: dbobj.execute(curs, 'DELETE FROM person_phonetics WHERE person_id=%s', (person_id, )) for mp in itertools.chain(*encode_phones(*names)): dbobj.execute(curs, 'INSERT INTO person_phonetics VALUES (%s, %s)', (person_id, mp)) finally: curs.close()
def mark_checked(user): db = user.db() curs = db.cursor() now = DateTime.now() try: dbobj.execute(curs, 'UPDATE users' ' SET checked_timestamp = %s' ' WHERE user_id = %s', (now, user.user_id)) user.reset_initial('checked_timestamp', now) finally: curs.close()
def delete(self): this_row = self._fetch(for_update=True) curs = globals.db.cursor() try: dbobj.execute(curs, 'DELETE FROM case_contacts WHERE contact_type_id=%s', (this_row.contact_type_id,)) self.count = curs.rowcount / 2 finally: curs.close() this_row.db_delete()
def update(self): if self.share == 'False': privacy = '!' else: privacy = ','.join(self.share_details + ['fullname']) if self.privacy != privacy: curs = globals.db.cursor() dbobj.execute(curs, 'UPDATE users SET privacy=%s WHERE user_id=%s', (privacy, self.user_id)) globals.db.commit() self.privacy = privacy
def merge(self, credentials): # lock the relevent cases persondupe.dupe_lock(globals.db) query = globals.db.query('cases', for_update=True) query.where_in('person_id', (self.id_a, self.id_b)) case_ids = query.fetchcols('case_id') # lock the person records and make sure they haven't changed # and estimate how how many changes each record might require a_delta_count = b_delta_count = 0 person_a, person_b = self._fetch_persons(for_update=True) for mc in self.fields: try: a_changed, b_changed = mc.apply(person_a, person_b) except PersonHasChanged: person_a.db_revert() person_b.db_revert() self.init_fields(person_a, person_b) raise if a_changed: a_delta_count += 1 if b_changed: b_delta_count += 1 # Now decide which direction to merge if b_delta_count > a_delta_count: update_person, delete_person = person_a, person_b else: update_person, delete_person = person_b, person_a # Update the log case_ids.sort() update_desc = update_person.db_desc() delete_desc = delete_person.db_desc() if not update_desc: update_desc = 'no edits required' if not delete_desc: delete_desc = 'no edits required' desc = 'Merge person, System IDs %s, UPDATED %s, DELETED %s' %\ (utils.commalist(case_ids, 'and'), update_desc, delete_desc) for case_id in case_ids: credentials.user_log(globals.db, desc, case_id=case_id) # Now update the cases and persons curs = globals.db.cursor() try: dbobj.execute( curs, 'UPDATE cases SET person_id=%s' ' WHERE person_id=%s', (update_person.person_id, delete_person.person_id)) finally: curs.close() fuzzyperson.update(globals.db, update_person.person_id, update_person.surname, update_person.given_names) update_person.db_update(refetch=False) delete_person.db_delete() # globals.db.rollback() # when testing return update_person, case_ids
def reset_attempts(user): db = user.db() curs = db.cursor() try: # We use an explicit update, rather than letting the dbrow do it as we # may not (yet) want to apply other changes that are in the dbrow. dbobj.execute(curs, 'UPDATE users SET bad_attempts = 0' ' WHERE user_id = %s', (user.user_id,)) user.reset_initial('bad_attempts', 0) finally: curs.close()
def merge(self, credentials): # lock the relevent cases persondupe.dupe_lock(globals.db) query = globals.db.query('cases', for_update=True) query.where_in('person_id', (self.id_a, self.id_b)) case_ids = query.fetchcols('case_id') # lock the person records and make sure they haven't changed # and estimate how how many changes each record might require a_delta_count = b_delta_count = 0 person_a, person_b = self._fetch_persons(for_update=True) for mc in self.fields: try: a_changed, b_changed = mc.apply(person_a, person_b) except PersonHasChanged: person_a.db_revert() person_b.db_revert() self.init_fields(person_a, person_b) raise if a_changed: a_delta_count += 1 if b_changed: b_delta_count += 1 # Now decide which direction to merge if b_delta_count > a_delta_count: update_person, delete_person = person_a, person_b else: update_person, delete_person = person_b, person_a # Update the log case_ids.sort() update_desc = update_person.db_desc() delete_desc = delete_person.db_desc() if not update_desc: update_desc = 'no edits required' if not delete_desc: delete_desc = 'no edits required' desc = 'Merge person, System IDs %s, UPDATED %s, DELETED %s' %\ (utils.commalist(case_ids, 'and'), update_desc, delete_desc) for case_id in case_ids: credentials.user_log(globals.db, desc, case_id=case_id) # Now update the cases and persons curs = globals.db.cursor() try: dbobj.execute(curs, 'UPDATE cases SET person_id=%s' ' WHERE person_id=%s', (update_person.person_id, delete_person.person_id)) finally: curs.close() fuzzyperson.update(globals.db, update_person.person_id, update_person.surname, update_person.given_names) update_person.db_update(refetch=False) delete_person.db_delete() # globals.db.rollback() # when testing return update_person, case_ids
def mark_checked(user): db = user.db() curs = db.cursor() now = DateTime.now() try: dbobj.execute( curs, 'UPDATE users' ' SET checked_timestamp = %s' ' WHERE user_id = %s', (now, user.user_id)) user.reset_initial('checked_timestamp', now) finally: curs.close()
def reset_attempts(user): db = user.db() curs = db.cursor() try: # We use an explicit update, rather than letting the dbrow do it as we # may not (yet) want to apply other changes that are in the dbrow. dbobj.execute( curs, 'UPDATE users SET bad_attempts = 0' ' WHERE user_id = %s', (user.user_id, )) user.reset_initial('bad_attempts', 0) finally: curs.close()
def rename(self, oldname, newname): self.db.lock_table(self.table, 'EXCLUSIVE') if self.versions(newname): raise DuplicateFormError('form name %r is already used' % newname) curs = self.db.cursor() try: dbobj.execute(curs, 'UPDATE %s SET name=%%s WHERE name=%%s' % self.table, (newname, oldname)) if curs.rowcount == 0: raise NoFormError('No form %r' % oldname) finally: curs.close()
def if_empty_insert(db, table, data, *cols): """ If the named table has no rows, then insert the given list of row tuples into the named columns. """ if db.query(table, limit=1).fetchone() is None: cmd = 'INSERT INTO %s (%s) VALUES (%s)' %\ (table, ','.join(cols), ','.join(['%s'] * len(cols))) curs = db.cursor() try: for row in data: dbobj.execute(curs, cmd, row) finally: curs.close()
def has_col(self, tablecol): table, col = tablecol.split('.') if not self.has_relation(table): return False curs = self.db.cursor() try: try: dbobj.execute(curs, 'SELECT %s FROM %s LIMIT 0' % (col, table)) except dbobj.DatabaseError: self.db.rollback() return False else: return True finally: curs.close()
def merge(self): this_row = self._fetch(for_update=True) other_row = self._fetch(self.merge_to_id, for_update=True) if (this_row is None or other_row is None or this_row.contact_type_id == other_row.contact_type_id): raise globals.Error('Unable to merge') curs = globals.db.cursor() try: dbobj.execute(curs, 'UPDATE case_contacts SET contact_type_id=%s' ' WHERE contact_type_id=%s', (other_row.contact_type_id, this_row.contact_type_id)) self.count = curs.rowcount / 2 finally: curs.close() this_row.db_delete()
def delete_form(forms_row, name): try: globals.formlib.delete(name) except form_ui.NoFormError: pass for table_desc in globals.formlib.form_tables(globals.db, name): globals.db.drop_table(table_desc.name) # We haven't used an ON DELETE CASCADE here simply to make it harder for # the admin to have have a catastrophic accident SQL typo... curs = globals.db.cursor() try: dbobj.execute(curs, 'DELETE FROM case_form_summary ' 'WHERE form_label = %s', (name, )) finally: curs.close() forms_row.db_delete() globals.db.save_describer()
def delete_form(forms_row, name): try: globals.formlib.delete(name) except form_ui.NoFormError: pass for table_desc in globals.formlib.form_tables(globals.db, name): globals.db.drop_table(table_desc.name) # We haven't used an ON DELETE CASCADE here simply to make it harder for # the admin to have have a catastrophic accident SQL typo... curs = globals.db.cursor() try: dbobj.execute(curs, 'DELETE FROM case_form_summary ' 'WHERE form_label = %s', (name,)) finally: curs.close() forms_row.db_delete() globals.db.save_describer()
def formrollforward(db, name, target_vers, rollforward_map): """ This function makes no attempt to update summary text, as this would require loading all the form instance data! We also can't cope with the source data being spread across multiple tables (as is the case when the configuration option form_rollforward has been set to false) - we'd need a rollforward map for each previous version of the form, and the chances of a successful roll-forward would be greatly diminished. """ db.lock_table('case_form_summary', 'EXCLUSIVE') query = db.query('case_form_summary') query.where('form_label = %s', name) summ_id_by_version = {} for summary in query.fetchall(): try: ids = summ_id_by_version[summary.form_version] except KeyError: ids = summ_id_by_version[summary.form_version] = [] ids.append(summary.summary_id) if not summ_id_by_version: return # Nothing needing roll-forward if len(summ_id_by_version) > 1: raise dbobj.DatabaseError('Cannot roll-forward form data - system ' 'has been operated with form_rollforward ' 'set to False, and form data is spread over ' 'multiple tables.') from_vers = summ_id_by_version.keys()[0] src_cols, dst_cols = zip(*rollforward_map) sys_cols = ('summary_id', 'form_date') src_cols = ','.join(sys_cols + src_cols) dst_cols = ','.join(sys_cols + dst_cols) src_table = globals.formlib.tablename(name, from_vers) dst_table = globals.formlib.tablename(name, target_vers) curs = db.cursor() try: dbobj.execute( curs, 'UPDATE case_form_summary SET form_version=%s ' 'WHERE form_label = %s', (target_vers, name)) dbobj.execute(curs, 'INSERT INTO %s (%s) SELECT %s FROM %s' %\ (dst_table, dst_cols, src_cols, src_table)) finally: curs.close()
def update_constraint(self, ltable, ftable, updtype, deltype, *sql): # types: # a - no action (default) # c - cascade # n - null if self.has_relation(ltable) and self.has_relation(ftable): curs = self.db.cursor() try: dbobj.execute( curs, "SELECT conname" " FROM pg_constraint" " JOIN pg_class AS l ON (l.oid = conrelid)" " JOIN pg_class AS f ON (f.oid = confrelid)" " WHERE l.relname = %s" " AND f.relname = %s" " AND confupdtype = %s" " AND confdeltype = %s", (ltable, ftable, updtype, deltype)) rows = curs.fetchall() if len(rows) == 1: rel = rows[0][0] # print 'schema upgrade: constraint %r on %r' % (rel, ltable) dbobj.execute( curs, 'ALTER TABLE %s DROP CONSTRAINT "%s"' % (ltable, rel)) for cmd in sql: dbobj.execute(curs, cmd) finally: curs.close()
def update_constraint(self, ltable, ftable, updtype, deltype, *sql): # types: # a - no action (default) # c - cascade # n - null if self.has_relation(ltable) and self.has_relation(ftable): curs = self.db.cursor() try: dbobj.execute(curs, "SELECT conname" " FROM pg_constraint" " JOIN pg_class AS l ON (l.oid = conrelid)" " JOIN pg_class AS f ON (f.oid = confrelid)" " WHERE l.relname = %s" " AND f.relname = %s" " AND confupdtype = %s" " AND confdeltype = %s", (ltable, ftable, updtype, deltype)) rows = curs.fetchall() if len(rows) == 1: rel = rows[0][0] # print 'schema upgrade: constraint %r on %r' % (rel, ltable) dbobj.execute(curs, 'ALTER TABLE %s DROP CONSTRAINT "%s"' % (ltable, rel)) for cmd in sql: dbobj.execute(curs, cmd) finally: curs.close()
def __call__(self, cmd): curs = self.db.cursor() try: dbobj.execute(curs, cmd) finally: curs.close()
def merge(self, credentials): # lock the relevent cases case_a, case_b = self._fetch_cases(for_update=True) # Safety checks: not_same_person = (case_a.person_id != case_b.person_id) syndrome_mismatch = (case_a.syndrome_id != case_b.syndrome_id) if not_same_person or syndrome_mismatch: raise MergeError('Unable to merge - consistency check failed') for mc in self.fields: try: mc.apply(case_a, case_b) except CaseHasChanged: case_a.db_revert() case_b.db_revert() self.init_fields(case_a, case_b) raise # Which direction to merge? if self.keep == 'a': update_case, delete_case = case_a, case_b else: update_case, delete_case = case_b, case_a update_desc = update_case.db_desc() delete_desc = delete_case.db_desc() # XXX Describe tag changes - how? if not update_desc: update_desc = 'no edits required' if not delete_desc: delete_desc = 'no edits required' if update_case.deleted and not delete_case.deleted: update_case.deleted = False update_case.delete_reason = None update_case.delete_timestamp = None delete_case.deleted = True delete_case.delete_reason = 'Merged to %s' % update_case.case_id delete_case.delete_timestamp = datetime.now() curs = globals.db.cursor() try: # merge contacts dbobj.execute( curs, 'UPDATE case_contacts SET contact_id=%s' ' WHERE contact_id=%s' ' AND case_id != %s' ' AND case_id NOT IN' ' (SELECT case_id FROM case_contacts' ' WHERE contact_id=%s)', (update_case.case_id, delete_case.case_id, update_case.case_id, update_case.case_id)) dbobj.execute( curs, 'UPDATE case_contacts SET case_id=%s' ' WHERE case_id=%s' ' AND contact_id != %s' ' AND contact_id NOT IN' ' (SELECT contact_id FROM case_contacts' ' WHERE case_id=%s)', (update_case.case_id, delete_case.case_id, update_case.case_id, update_case.case_id)) dbobj.execute( curs, 'DELETE FROM case_contacts' ' WHERE case_id=%s OR contact_id=%s', (delete_case.case_id, delete_case.case_id)) # case_form_summary dbobj.execute( curs, 'UPDATE case_form_summary SET case_id=%s' ' WHERE case_id=%s', (update_case.case_id, delete_case.case_id)) # case_acl dbobj.execute(curs, 'UPDATE case_acl SET case_id=%s' ' WHERE case_id=%s', (update_case.case_id, delete_case.case_id)) # tasks dbobj.execute(curs, 'UPDATE tasks SET case_id=%s' ' WHERE case_id=%s', (update_case.case_id, delete_case.case_id)) dbobj.execute(curs, 'UPDATE tasks SET case_id=%s' ' WHERE case_id=%s', (update_case.case_id, delete_case.case_id)) dbobj.execute(curs, 'UPDATE user_log SET case_id=%s' ' WHERE case_id=%s', (update_case.case_id, delete_case.case_id)) dbobj.execute(curs, 'UPDATE user_log SET case_id=%s' ' WHERE case_id=%s', (update_case.case_id, delete_case.case_id)) finally: curs.close() update_case.db_update() tag_desc = casetags.set_case_tags(update_case.case_id, update_case.tags) delete_case.db_update() desc = 'Merge System ID %s into %s, UPDATED %s %s, DELETED %s' %\ (delete_case.case_id, update_case.case_id, update_desc, tag_desc, delete_desc) credentials.user_log(globals.db, desc, case_id=update_case.case_id) return update_case, delete_case
def execute(self, cmd, args): curs = self.db.cursor() try: dbobj.execute(curs, cmd % demogfields.DEMOG_TABLE, args) finally: curs.close()
def setUp(self): td = self.new_table('cases') td.column('case_id', dbobj.SerialColumn, primary_key=True) td.column('syndrome_id', dbobj.IntColumn) td.column('deleted', dbobj.BooleanColumn) td.create() td = self.new_table('case_acl') td.column('case_id', dbobj.ReferenceColumn, references='cases') td.column('unit_id', dbobj.IntColumn) td.create() td = self.new_table('workqueues') td.column('queue_id', dbobj.SerialColumn, primary_key=True) td.column('unit_id', dbobj.IntColumn) td.column('user_id', dbobj.IntColumn) td.create() td = self.new_table('workqueue_members') td.column('queue_id', dbobj.ReferenceColumn, references='workqueues') td.column('unit_id', dbobj.IntColumn) td.column('user_id', dbobj.IntColumn) td.create() td = self.new_table('tasks') td.column('task_id', dbobj.SerialColumn, primary_key=True) td.column('case_id', dbobj.ReferenceColumn, references='cases') td.column('queue_id', dbobj.ReferenceColumn, references='workqueues') td.create() curs = self.db.cursor() # Case 1, syndrome 1, unit 1 & 3 dbobj.execute(curs, 'INSERT INTO cases VALUES (1, 1, false)') dbobj.execute(curs, 'INSERT INTO case_acl VALUES (1, 1)') dbobj.execute(curs, 'INSERT INTO case_acl VALUES (1, 3)') self.db.commit() # Case 4, syndrome 1, no unit dbobj.execute(curs, 'INSERT INTO cases VALUES (4, 1, false)') # Case 5, syndrome 2, unit 1 dbobj.execute(curs, 'INSERT INTO cases VALUES (5, 2, false)') dbobj.execute(curs, 'INSERT INTO case_acl VALUES (5, 1)')
def merge(self, credentials): # lock the relevent cases case_a, case_b = self._fetch_cases(for_update=True) # Safety checks: not_same_person = (case_a.person_id != case_b.person_id) syndrome_mismatch = (case_a.syndrome_id != case_b.syndrome_id) if not_same_person or syndrome_mismatch: raise MergeError('Unable to merge - consistency check failed') for mc in self.fields: try: mc.apply(case_a, case_b) except CaseHasChanged: case_a.db_revert() case_b.db_revert() self.init_fields(case_a, case_b) raise # Which direction to merge? if self.keep == 'a': update_case, delete_case = case_a, case_b else: update_case, delete_case = case_b, case_a update_desc = update_case.db_desc() delete_desc = delete_case.db_desc() # XXX Describe tag changes - how? if not update_desc: update_desc = 'no edits required' if not delete_desc: delete_desc = 'no edits required' if update_case.deleted and not delete_case.deleted: update_case.deleted = False update_case.delete_reason = None update_case.delete_timestamp = None delete_case.deleted = True delete_case.delete_reason = 'Merged to %s' % update_case.case_id delete_case.delete_timestamp = datetime.now() curs = globals.db.cursor() try: # merge contacts dbobj.execute(curs, 'UPDATE case_contacts SET contact_id=%s' ' WHERE contact_id=%s' ' AND case_id != %s' ' AND case_id NOT IN' ' (SELECT case_id FROM case_contacts' ' WHERE contact_id=%s)', (update_case.case_id, delete_case.case_id, update_case.case_id, update_case.case_id)) dbobj.execute(curs, 'UPDATE case_contacts SET case_id=%s' ' WHERE case_id=%s' ' AND contact_id != %s' ' AND contact_id NOT IN' ' (SELECT contact_id FROM case_contacts' ' WHERE case_id=%s)', (update_case.case_id, delete_case.case_id, update_case.case_id, update_case.case_id)) dbobj.execute(curs, 'DELETE FROM case_contacts' ' WHERE case_id=%s OR contact_id=%s', (delete_case.case_id, delete_case.case_id)) # case_form_summary dbobj.execute(curs, 'UPDATE case_form_summary SET case_id=%s' ' WHERE case_id=%s', (update_case.case_id, delete_case.case_id)) # case_acl dbobj.execute(curs, 'UPDATE case_acl SET case_id=%s' ' WHERE case_id=%s', (update_case.case_id, delete_case.case_id)) # tasks dbobj.execute(curs, 'UPDATE tasks SET case_id=%s' ' WHERE case_id=%s', (update_case.case_id, delete_case.case_id)) dbobj.execute(curs, 'UPDATE tasks SET case_id=%s' ' WHERE case_id=%s', (update_case.case_id, delete_case.case_id)) dbobj.execute(curs, 'UPDATE user_log SET case_id=%s' ' WHERE case_id=%s', (update_case.case_id, delete_case.case_id)) dbobj.execute(curs, 'UPDATE user_log SET case_id=%s' ' WHERE case_id=%s', (update_case.case_id, delete_case.case_id)) finally: curs.close() update_case.db_update() tag_desc = casetags.set_case_tags(update_case.case_id, update_case.tags) delete_case.db_update() desc = 'Merge System ID %s into %s, UPDATED %s %s, DELETED %s' %\ (delete_case.case_id, update_case.case_id, update_desc, tag_desc, delete_desc) credentials.user_log(globals.db, desc, case_id=update_case.case_id) return update_case, delete_case