Revision 5989
Added by Aaron Marcuse-Kubitza about 12 years ago
| lib/sql.py | ||
|---|---|---|
| 1114 | 1114 |
def table_pkey_col(db, table, recover=None): |
| 1115 | 1115 |
table = sql_gen.as_Table(table) |
| 1116 | 1116 |
|
| 1117 |
join_cols = ['table_schema', 'table_name', 'constraint_schema', |
|
| 1118 |
'constraint_name'] |
|
| 1119 |
tables = [sql_gen.Table('key_column_usage', 'information_schema'),
|
|
| 1120 |
sql_gen.Join(sql_gen.Table('table_constraints', 'information_schema'),
|
|
| 1121 |
dict(((c, sql_gen.join_same_not_null) for c in join_cols)))] |
|
| 1122 |
cols = [sql_gen.Col('column_name')]
|
|
| 1123 |
|
|
| 1124 |
conds = [('constraint_type', 'PRIMARY KEY'), ('table_name', table.name)]
|
|
| 1125 |
schema = table.schema |
|
| 1126 |
if schema != None: conds.append(('table_schema', schema))
|
|
| 1127 |
order_by = 'position_in_unique_constraint' |
|
| 1128 |
|
|
| 1129 |
try: return sql_gen.Col(value(select(db, tables, cols, conds, |
|
| 1130 |
order_by=order_by, limit=1, log_level=4)), table) |
|
| 1131 |
except StopIteration: raise DoesNotExistException('primary key', '')
|
|
| 1117 |
module = util.root_module(db.db) |
|
| 1118 |
if module == 'psycopg2': |
|
| 1119 |
return sql_gen.Col(index_cols(db, table_pkey_index(db, table, |
|
| 1120 |
recover))[0], table) |
|
| 1121 |
else: |
|
| 1122 |
join_cols = ['table_schema', 'table_name', 'constraint_schema', |
|
| 1123 |
'constraint_name'] |
|
| 1124 |
tables = [sql_gen.Table('key_column_usage', 'information_schema'),
|
|
| 1125 |
sql_gen.Join( |
|
| 1126 |
sql_gen.Table('table_constraints', 'information_schema'),
|
|
| 1127 |
dict(((c, sql_gen.join_same_not_null) for c in join_cols)))] |
|
| 1128 |
cols = [sql_gen.Col('column_name')]
|
|
| 1129 |
|
|
| 1130 |
conds = [('constraint_type', 'PRIMARY KEY'), ('table_name', table.name)]
|
|
| 1131 |
schema = table.schema |
|
| 1132 |
if schema != None: conds.append(('table_schema', schema))
|
|
| 1133 |
order_by = 'position_in_unique_constraint' |
|
| 1134 |
|
|
| 1135 |
try: return sql_gen.Col(value(select(db, tables, cols, conds, |
|
| 1136 |
order_by=order_by, limit=1, log_level=4)), table) |
|
| 1137 |
except StopIteration: raise DoesNotExistException('primary key', '')
|
|
| 1132 | 1138 |
|
| 1133 | 1139 |
def pkey_name(db, table, recover=None): |
| 1134 | 1140 |
'''If no pkey, returns the first column in the table.''' |
Also available in: Unified diff
sql.py: table_pkey_col(): For PostgreSQL DBs, use pg_catalog via index_cols() and table_pkey_index(), in order to use the search_path to look up the table. This fixes a bug where the pkey would be selected from information_schema.table_constraints in random order, and this order sometimes returned the corresponding table in the public schema but sometimes in other schemas, such as VegBank. This became a problem now that VegBIEN has a place table, which conflicts with VegBank's place table. (Most other VegBank tables that are mapped to have been renamed in VegBIEN.)