def create(self, grant=None, owner=None):
cmds = []
for extra in self.extras:
sql = extra.pre_create_sql()
if sql is not None:
cmds.append(sql)
if not self.db.db_has_relation(self.name):
col_sql = []
for col in self.columns:
col.create_sql(col_sql)
if self.primary_keys:
pkey_cols = [coldesc.name for coldesc in self.primary_keys]
col_sql.append('PRIMARY KEY (%s)' % ', '.join(pkey_cols))
table_sql = [
'CREATE TABLE %s (' % self.name,
',\n'.join([' %s' % c for c in col_sql]),
') WITH OIDS',
'',
]
cmds.append('\n'.join(table_sql))
for extra in self.extras:
sql = extra.post_create_sql()
if sql is not None:
cmds.append(sql)
curs = self.db.cursor()
try:
for cmd in cmds:
execute(curs, cmd)
if cmds and grant:
self.grant(curs, grant)
if cmds and owner:
self.owner(curs, owner)
finally:
curs.close()
def nextval(self, colname):
col_desc = self.get_column(colname)
curs = self.db.cursor()
try:
execute(curs, col_desc.nextval_sql())
return curs.fetchone()[0]
finally:
curs.close()
def rename(self, new_name):
misc.valid_identifier(new_name, 'table identifier', strict=False)
curs = self.db.cursor()
try:
execute(curs,
'ALTER TABLE %s RENAME TO %s' % (self.name, new_name))
self.name = new_name
finally:
curs.close()
def lock_table(self, table, mode, wait=True):
cmd = 'LOCK %s IN %s MODE' % (table, mode)
if not wait:
cmd += ' NOWAIT'
curs = self.cursor()
try:
execute(curs, cmd)
finally:
curs.close()
def lock_table(self, table, mode, wait=True):
cmd = 'LOCK %s IN %s MODE' % (table, mode)
if not wait:
cmd += ' NOWAIT'
curs = self.cursor()
try:
execute(curs, cmd)
finally:
curs.close()
def make_user(self, username):
curs = self.cursor()
try:
execute(curs, 'SELECT usename FROM pg_user'
' WHERE usename=%s', (username, ))
if not curs.fetchone():
execute(curs, 'CREATE USER "%s"' % username)
self.commit()
finally:
curs.close()
def make_user(self, username):
curs = self.cursor()
try:
execute(curs, 'SELECT usename FROM pg_user'
' WHERE usename=%s', (username,))
if not curs.fetchone():
execute(curs, 'CREATE USER "%s"' % username)
self.commit()
finally:
curs.close()
def _connect_db(self):
self.db = self.dsn.connect()
curs = self.db.cursor()
try:
execute(curs, "SET datestyle TO 'ISO,European'")
# May potentially open us up to an SQL injection attack:
# http://www.postgresql.org/docs/techdocs.50
# execute(curs, "SET client_encoding TO unicode")
execute(curs, "SET client_encoding TO SQL_ASCII")
finally:
curs.close()
def _connect_db(self):
self.db = self.dsn.connect()
curs = self.db.cursor()
try:
execute(curs, "SET datestyle TO 'ISO,European'")
# May potentially open us up to an SQL injection attack:
# http://www.postgresql.org/docs/techdocs.50
# execute(curs, "SET client_encoding TO unicode")
execute(curs, "SET client_encoding TO SQL_ASCII")
finally:
curs.close()
def delete(self):
query = ['DELETE FROM %s' % self.table_desc.name]
query_args = ()
if self.where_expr:
where_expr, query_args = self.where_expr.build_expr()
query.append('WHERE %s' % where_expr)
curs = self.table_desc.db.cursor()
try:
execute.execute(curs, ' '.join(query), query_args)
finally:
curs.close()
def update(self, set, *args):
query = ['UPDATE %s SET %s' % (self.table_desc.name, set)]
args = list(args)
if self.where_expr:
where_expr, query_args = self.where_expr.build_expr()
query.append('WHERE %s' % where_expr)
args.extend(query_args)
curs = self.table_desc.db.cursor()
try:
execute.execute(curs, ' '.join(query), args)
finally:
curs.close()
def aggregate(self, *op):
query_expr, query_args = self.build_expr(op)
curs = self.table_desc.db.cursor()
try:
execute.execute(curs, query_expr, query_args)
result = curs.fetchone()
if len(result) != len(op):
raise dbapi.ProgrammingError('aggregate function returned '
'%d results, expected %d' %
(len(result), len(op)))
if len(result) == 1:
return result[0]
else:
return result
finally:
curs.close()
def get_ref(self, col_name):
col_desc, col_value = self._colpair(col_name)
ref_col_desc = col_desc.target_column()
ref_table_desc = ref_col_desc.table_desc
row = ref_table_desc.get_row()
curs = self.db().cursor()
try:
value = col_value.raw_value()
if value is not None:
execute(curs, 'SELECT * FROM %s WHERE %s = %%s' % \
(ref_table_desc.name, ref_col_desc.name),
(col_desc.to_sql(value),))
result = curs.fetchone()
if result:
row.from_fetch(curs.description, result)
finally:
curs.close()
return row
def template_exec(dsn, cmd):
"""
Connect to the template1 database, retrying if necessary
"""
template_dsn = DSN(dsn, database='template1')
db = template_dsn.connect()
try:
db.autocommit = True
curs = db.cursor()
n = 0
while 1:
try:
execute(curs, cmd)
break
except dbapi.DatabaseError, e:
if n < 3 and 'is being accessed by other users' in str(e):
n += 1
time.sleep(1)
continue
else:
raise
finally:
db.close()
def template_exec(dsn, cmd):
"""
Connect to the template1 database, retrying if necessary
"""
template_dsn = DSN(dsn, database='template1')
db = template_dsn.connect()
try:
db.autocommit = True
curs = db.cursor()
n = 0
while 1:
try:
execute(curs, cmd)
break
except dbapi.DatabaseError, e:
if n < 3 and 'is being accessed by other users' in str(e):
n += 1
time.sleep(1)
continue
else:
raise
finally:
db.close()
def drop(self):
if self.db.db_has_relation(self.name):
curs = self.db.cursor()
try:
for extra in self.extras:
sql = extra.pre_drop_sql()
if sql is not None:
execute(curs, sql)
execute(curs, 'DROP TABLE %s' % self.name)
# PG drops objects related to the table
for extra in self.extras:
sql = extra.post_drop_sql()
if sql is not None:
execute(curs, sql)
finally:
curs.close()
def owner(self, curs, owner):
execute(curs, 'ALTER TABLE %s OWNER TO "%s"' % (self.name, owner))
for extra in self.extras:
extra.owner(curs, owner)
self.owned_by = owner
def reset_sequences(self, curs):
execute(curs, "SELECT setval('%s',(SELECT max(%s) FROM %s))" %\
(self.seq.name, self.name, self.table_desc.name))
curs.fetchone()
def owner(self, curs, owner):
execute(curs, 'ALTER TABLE %s OWNER TO "%s"' % (self.name, owner))
def owner(self, curs, owner):
execute(curs, 'ALTER TABLE %s OWNER TO "%s"' % (self.name, owner))
def reset_sequences(self, curs):
execute(curs, "SELECT setval('%s',(SELECT max(%s) FROM %s))" %\
(self.seq.name, self.name, self.table_desc.name))
curs.fetchone()
def grant(self, curs, user):
objs = [self.name] + [e.name for e in self.extras if e.needs_grant]
execute(
curs, 'GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES'
' ON %s TO "%s"' % (', '.join(objs), user))
def revoke(self, curs, user):
objs = [self.name] + [e.name for e in self.extras if e.needs_grant]
execute(
curs, 'REVOKE SELECT, UPDATE, INSERT, DELETE, REFERENCES '
' ON %s FROM "%s"' % (', '.join(objs), user))
def db_update(self, refetch=True):
changed_cols, new_values = [], []
no_change = True
for col_desc in self._table_desc.get_columns():
col_value = self._columns[col_desc.name]
value = col_value.raw_value()
changed = ((self._new and value is not None)
or col_value.has_changed())
if changed:
no_change = False
elif col_desc.auto_timestamp:
# Not "changed", but auto-updating with other updates
value = DateTime.now()
changed = True
if changed:
changed_cols.append(col_desc.name)
new_values.append(col_desc.to_sql(value))
if no_change:
return
table = self._table_desc.name
curs = self.db().cursor()
try:
if self._new:
cmd = _CmdBuilder()
cmd.append('INSERT INTO %s' % table)
cmd.append_list_of_names(changed_cols)
cmd.append('VALUES')
cmd.append_list_of_values(new_values)
cmd.execute(curs)
if not refetch:
return
execute(curs, 'SELECT * FROM %s WHERE oid=%%s' % table,
(curs.oidValue,))
else:
pkey_names, pkey_values = self._get_pkey()
if not pkey_names:
raise dbapi.ProgrammingError('No primary key defined for "%s"' % table)
cmd = _CmdBuilder()
cmd.append('UPDATE %s SET' % table)
cmd.append_name_value_expr(', ', changed_cols, new_values)
cmd.append('WHERE')
cmd.append_name_value_expr(' and ', pkey_names, pkey_values)
cmd.execute(curs)
if curs.rowcount != 1:
raise dbapi.RecordDeleted('Record has been deleted')
pkey_names, pkey_values = self._get_pkey(initial = False)
if not refetch:
return
cmd = _CmdBuilder()
cmd.append('SELECT * FROM %s WHERE' % table)
cmd.append_name_value_expr(' and ', pkey_names, pkey_values)
cmd.execute(curs)
for col_value in self._columns.values():
col_value.save()
self._saved_new = self._new
self.db().add_pending(self)
result = curs.fetchmany(2)
assert len(result) == 1
self.from_fetch(curs.description, result[0])
finally:
curs.close()
def execute(self, curs):
execute(curs, ' '.join(self.cmd), self.args)
def execute(self, curs, columns=None):
query_expr, query_args = self.build_expr(columns)
execute.execute(curs, query_expr, query_args)