Revision 2187
Added by Aaron Marcuse-Kubitza over 12 years ago
| lib/sql.py | ||
|---|---|---|
| 365 | 365 |
|
| 366 | 366 |
join_using = object() # tells mk_select() to join the column with USING |
| 367 | 367 |
|
| 368 |
join_not_equal = object() # tells mk_select() to join the column with !=
|
|
| 368 |
filter_out = object() # tells mk_select() to filter out rows that match the join
|
|
| 369 | 369 |
|
| 370 | 370 |
def mk_select(db, tables, fields=None, conds=None, limit=None, start=None, |
| 371 | 371 |
order_by=order_by_pkey, table_is_esc=False): |
| 372 | 372 |
''' |
| 373 | 373 |
@param tables The single table to select from, or a list of tables to join |
| 374 |
together: [table0, (table1, dict(right_col=left_col, ...)), ...] |
|
| 374 |
together: [table0, (table1, joins), ...] |
|
| 375 |
|
|
| 376 |
joins has the format: dict(right_col=left_col, ...) |
|
| 377 |
* if left_col is join_using, left_col is set to right_col |
|
| 378 |
* if left_col is filter_out, the tables are LEFT JOINed together and the |
|
| 379 |
query is filtered by `right_col IS NULL` (indicating no match) |
|
| 375 | 380 |
@param fields Use None to select all fields in the table |
| 376 | 381 |
@param table_is_esc Whether the table name has already been escaped |
| 377 | 382 |
@return tuple(query, params) |
| ... | ... | |
| 410 | 415 |
def cond(entry): |
| 411 | 416 |
'''Parses conditions''' |
| 412 | 417 |
col, value = entry |
| 413 |
cond_ = esc_name_(col)+' '
|
|
| 418 |
cond_ = parse_col(col)+' '
|
|
| 414 | 419 |
if value == None: cond_ += 'IS' |
| 415 | 420 |
else: cond_ += '=' |
| 416 | 421 |
cond_ += ' %s' |
| ... | ... | |
| 425 | 430 |
left_table = table0 |
| 426 | 431 |
for table, joins in tables: |
| 427 | 432 |
if not table_is_esc: table = esc_name_(table) |
| 428 |
query += ' JOIN '+table |
|
| 429 | 433 |
|
| 434 |
left_join_ref = [False] |
|
| 435 |
|
|
| 430 | 436 |
def join(entry): |
| 431 | 437 |
'''Parses non-USING joins''' |
| 432 | 438 |
right_col, left_col = entry |
| 433 | 439 |
|
| 434 |
negative = False |
|
| 435 |
|
|
| 436 | 440 |
# Parse special values |
| 437 | 441 |
if left_col == None: left_col = (left_col,) |
| 438 | 442 |
# for None values, tuple is optional |
| 439 | 443 |
elif left_col == join_using: left_col = right_col |
| 440 |
elif left_col == join_not_equal:
|
|
| 444 |
elif left_col == filter_out:
|
|
| 441 | 445 |
left_col = right_col |
| 442 |
negative = True |
|
| 446 |
left_join_ref[0] = True |
|
| 447 |
conds[(table, right_col)] = None # filter query by no match |
|
| 443 | 448 |
|
| 444 | 449 |
# Create SQL |
| 445 | 450 |
right_col = table+'.'+esc_name_(right_col) |
| ... | ... | |
| 456 | 461 |
sql_ += ('= '+left_col+' OR ('+right_col+' IS NULL AND '
|
| 457 | 462 |
+left_col+' IS NULL)') |
| 458 | 463 |
|
| 459 |
if negative: sql_ = 'NOT ('+sql_+')'
|
|
| 460 |
|
|
| 461 | 464 |
return sql_ |
| 462 | 465 |
|
| 466 |
# Create join condition and determine join type |
|
| 463 | 467 |
if reduce(operator.and_, (v == join_using for v in joins.itervalues())): |
| 464 | 468 |
# all cols w/ USING, so can use simpler USING syntax |
| 465 |
query += ' USING ('+(', '.join(joins.iterkeys()))+')'
|
|
| 466 |
else: query += ' ON '+(' AND '.join(map(join, joins.iteritems())))
|
|
| 469 |
join_cond = 'USING ('+(', '.join(joins.iterkeys()))+')'
|
|
| 470 |
else: join_cond = 'ON '+(' AND '.join(map(join, joins.iteritems())))
|
|
| 467 | 471 |
|
| 472 |
# Create join |
|
| 473 |
if left_join_ref[0]: query += ' LEFT' |
|
| 474 |
query += ' JOIN '+table+' '+join_cond |
|
| 475 |
|
|
| 468 | 476 |
left_table = table |
| 469 | 477 |
|
| 470 | 478 |
missing = True |
Also available in: Unified diff
sql.py: mk_select(): joins: Fixed bug where join_not_equal did not do what it was designed for, which is filtering out matches of the join condition (before the bug fix, it effectively did a cross join with matching rows excluded, causing duplication of rows). Renamed join_not_equal to filter_out to reflect its intended use. Support table-scoped column names in the WHERE conds list.