Revision 2277
Added by Aaron Marcuse-Kubitza over 12 years ago
| lib/sql.py | ||
|---|---|---|
| 413 | 413 |
|
| 414 | 414 |
order_by_pkey = object() # tells mk_select() to order by the pkey |
| 415 | 415 |
|
| 416 |
join_using = object() # tells mk_select() to join the column with USING |
|
| 417 |
|
|
| 418 | 416 |
distinct_on_all = object() # tells mk_select() to SELECT DISTINCT ON all columns |
| 419 | 417 |
|
| 420 | 418 |
def mk_select(db, tables, fields=None, conds=None, distinct_on=[], limit=None, |
| ... | ... | |
| 424 | 422 |
together: [table0, (table1, joins), ...] |
| 425 | 423 |
|
| 426 | 424 |
joins has the format: dict(right_col=left_col, ...) |
| 427 |
* if left_col is join_using, left_col is set to right_col |
|
| 425 |
* if left_col is sql_gen.join_using, left_col is set to right_col
|
|
| 428 | 426 |
@param fields Use None to select all fields in the table |
| 429 | 427 |
@param conds WHERE conditions: dict(compare_left_side=compare_right_side): |
| 430 | 428 |
* compare_left_side: Code|str (for col name) |
| ... | ... | |
| 478 | 476 |
tables = [sql_gen.join2sql_gen(v, table_is_esc) for v in tables] |
| 479 | 477 |
left_table = table0 |
| 480 | 478 |
for join_ in tables: |
| 479 |
# Parse special values |
|
| 481 | 480 |
table = join_.table |
| 482 |
joins = join_.mapping |
|
| 483 |
|
|
| 484 |
left_join = False |
|
| 485 |
|
|
| 486 |
# Parse special values |
|
| 487 | 481 |
if join_.type_ is sql_gen.filter_out: |
| 488 |
left_join = True |
|
| 489 |
table_pkey = pkey(db, table.to_str(db), table_is_esc=True) |
|
| 490 |
conds[sql_gen.Col(table_pkey, table)] = None # filter by no match |
|
| 482 |
conds[sql_gen.Col(pkey(db, table), table)] = None # filter no match |
|
| 491 | 483 |
|
| 492 |
def join(entry): |
|
| 493 |
'''Parses non-USING joins''' |
|
| 494 |
right_col, left_col = entry |
|
| 495 |
|
|
| 496 |
# Parse special values |
|
| 497 |
if left_col == None: left_col = (left_col,) |
|
| 498 |
# for None values, tuple is optional |
|
| 499 |
elif left_col is join_using: left_col = right_col |
|
| 500 |
|
|
| 501 |
return cond((sql_gen.col2sql_gen(right_col, table), |
|
| 502 |
sql_gen.cond2sql_gen(left_col, left_table, assume_col=True))) |
|
| 484 |
query += ' '+join_.to_str(db, left_table) |
|
| 503 | 485 |
|
| 504 |
# Create join condition and determine join type |
|
| 505 |
if reduce(operator.and_, (v is join_using for v in joins.itervalues())): |
|
| 506 |
# all cols w/ USING, so can use simpler USING syntax |
|
| 507 |
join_cond = 'USING ('+(', '.join(joins.iterkeys()))+')'
|
|
| 508 |
else: join_cond = 'ON '+(' AND '.join(map(join, joins.iteritems())))
|
|
| 509 |
|
|
| 510 |
# Create join |
|
| 511 |
if left_join: query += ' LEFT' |
|
| 512 |
query += ' JOIN '+table.to_str(db)+' '+join_cond |
|
| 513 |
|
|
| 514 | 486 |
left_table = table |
| 515 | 487 |
|
| 516 | 488 |
missing = True |
| ... | ... | |
| 778 | 750 |
in_tables0 = in_tables.pop(0) # first table is separate |
| 779 | 751 |
in_pkey = pkey(db, in_tables0, recover=True, table_is_esc=table_is_esc) |
| 780 | 752 |
in_pkey_col = sql_gen.col2sql_gen(in_pkey, in_tables0, table_is_esc) |
| 781 |
insert_joins = [in_tables0]+[(t, {in_pkey: join_using}) for t in in_tables]
|
|
| 753 |
insert_joins = [in_tables0]+[(t, {in_pkey: sql_gen.join_using})
|
|
| 754 |
for t in in_tables] |
|
| 782 | 755 |
|
| 783 | 756 |
out_pkey = pkey(db, out_table, recover=True, table_is_esc=table_is_esc) |
| 784 | 757 |
out_pkey_col = sql_gen.col2sql_gen(out_pkey, out_table, table_is_esc) |
| ... | ... | |
| 840 | 813 |
|
| 841 | 814 |
db.log_debug('Joining together output and input pkeys')
|
| 842 | 815 |
run_query_into_pkeys(*mk_select(db, [in_pkeys_ref[0], |
| 843 |
(out_pkeys_ref[0], {row_num_col: join_using})], pkeys, start=0))
|
|
| 816 |
(out_pkeys_ref[0], {row_num_col: sql_gen.join_using})], pkeys,
|
|
| 817 |
start=0)) |
|
| 844 | 818 |
|
| 845 | 819 |
break # insert successful |
| 846 | 820 |
except DuplicateKeyException, e: |
Also available in: Unified diff
sql.py: mk_select(): joins: Switched to using sql_gen.Join.to_str() to render joins to SQL