Project

General

Profile

1
# Database access
2

    
3
import copy
4
import operator
5
import re
6
import warnings
7

    
8
import exc
9
import dicts
10
import iters
11
import lists
12
from Proxy import Proxy
13
import rand
14
import sql_gen
15
import strings
16
import util
17

    
18
##### Exceptions
19

    
20
def get_cur_query(cur, input_query=None, input_params=None):
21
    raw_query = None
22
    if hasattr(cur, 'query'): raw_query = cur.query
23
    elif hasattr(cur, '_last_executed'): raw_query = cur._last_executed
24
    
25
    if raw_query != None: return raw_query
26
    else: return '[input] '+strings.ustr(input_query)+' % '+repr(input_params)
27

    
28
def _add_cursor_info(e, *args, **kw_args):
29
    '''For params, see get_cur_query()'''
30
    exc.add_msg(e, 'query: '+str(get_cur_query(*args, **kw_args)))
31

    
32
class DbException(exc.ExceptionWithCause):
33
    def __init__(self, msg, cause=None, cur=None):
34
        exc.ExceptionWithCause.__init__(self, msg, cause, cause_newline=True)
35
        if cur != None: _add_cursor_info(self, cur)
36

    
37
class ExceptionWithName(DbException):
38
    def __init__(self, name, cause=None):
39
        DbException.__init__(self, 'for name: '+str(name), cause)
40
        self.name = name
41

    
42
class ExceptionWithNameValue(DbException):
43
    def __init__(self, name, value, cause=None):
44
        DbException.__init__(self,
45
            'for name: '+str(name)+'; value: '+repr(value), cause)
46
        self.name = name
47
        self.value = value
48

    
49
class ConstraintException(DbException):
50
    def __init__(self, name, cols, cause=None):
51
        DbException.__init__(self, 'Violated '+name+ ' constraint on columns: '
52
            +(', '.join(cols)), cause)
53
        self.name = name
54
        self.cols = cols
55

    
56
class NameException(DbException): pass
57

    
58
class DuplicateKeyException(ConstraintException): pass
59

    
60
class NullValueException(ConstraintException): pass
61

    
62
class FunctionValueException(ExceptionWithNameValue): pass
63

    
64
class DuplicateTableException(ExceptionWithName): pass
65

    
66
class DuplicateFunctionException(ExceptionWithName): pass
67

    
68
class EmptyRowException(DbException): pass
69

    
70
##### Warnings
71

    
72
class DbWarning(UserWarning): pass
73

    
74
##### Result retrieval
75

    
76
def col_names(cur): return (col[0] for col in cur.description)
77

    
78
def rows(cur): return iter(lambda: cur.fetchone(), None)
79

    
80
def consume_rows(cur):
81
    '''Used to fetch all rows so result will be cached'''
82
    iters.consume_iter(rows(cur))
83

    
84
def next_row(cur): return rows(cur).next()
85

    
86
def row(cur):
87
    row_ = next_row(cur)
88
    consume_rows(cur)
89
    return row_
90

    
91
def next_value(cur): return next_row(cur)[0]
92

    
93
def value(cur): return row(cur)[0]
94

    
95
def values(cur): return iters.func_iter(lambda: next_value(cur))
96

    
97
def value_or_none(cur):
98
    try: return value(cur)
99
    except StopIteration: return None
100

    
101
##### Input validation
102

    
103
def check_name(name):
104
    if re.search(r'\W', name) != None: raise NameException('Name "'+name
105
        +'" may contain only alphanumeric characters and _')
106

    
107
def esc_name_by_module(module, name, ignore_case=False):
108
    if module == 'psycopg2':
109
        if ignore_case:
110
            # Don't enclose in quotes because this disables case-insensitivity
111
            check_name(name)
112
            return name
113
        else: quote = '"'
114
    elif module == 'MySQLdb': quote = '`'
115
    else: raise NotImplementedError("Can't escape name for "+module+' database')
116
    return quote + name.replace(quote, '') + quote
117

    
118
def esc_name_by_engine(engine, name, **kw_args):
119
    return esc_name_by_module(db_engines[engine][0], name, **kw_args)
120

    
121
def esc_name(db, name, **kw_args):
122
    return esc_name_by_module(util.root_module(db.db), name, **kw_args)
123

    
124
def qual_name(db, schema, table):
125
    def esc_name_(name): return esc_name(db, name)
126
    table = esc_name_(table)
127
    if schema != None: return esc_name_(schema)+'.'+table
128
    else: return table
129

    
130
##### Database connections
131

    
132
db_config_names = ['engine', 'host', 'user', 'password', 'database', 'schemas']
133

    
134
db_engines = {
135
    'MySQL': ('MySQLdb', {'password': 'passwd', 'database': 'db'}),
136
    'PostgreSQL': ('psycopg2', {}),
137
}
138

    
139
DatabaseErrors_set = set([DbException])
140
DatabaseErrors = tuple(DatabaseErrors_set)
141

    
142
def _add_module(module):
143
    DatabaseErrors_set.add(module.DatabaseError)
144
    global DatabaseErrors
145
    DatabaseErrors = tuple(DatabaseErrors_set)
146

    
147
def db_config_str(db_config):
148
    return db_config['engine']+' database '+db_config['database']
149

    
150
def _query_lookup(query, params): return (query, dicts.make_hashable(params))
151

    
152
log_debug_none = lambda msg: None
153

    
154
class DbConn:
155
    def __init__(self, db_config, serializable=True, autocommit=False,
156
        caching=True, log_debug=log_debug_none):
157
        self.db_config = db_config
158
        self.serializable = serializable
159
        self.autocommit = autocommit
160
        self.caching = caching
161
        self.log_debug = log_debug
162
        self.debug = log_debug != log_debug_none
163
        
164
        self.__db = None
165
        self.query_results = {}
166
        self._savepoint = 0
167
    
168
    def __getattr__(self, name):
169
        if name == '__dict__': raise Exception('getting __dict__')
170
        if name == 'db': return self._db()
171
        else: raise AttributeError()
172
    
173
    def __getstate__(self):
174
        state = copy.copy(self.__dict__) # shallow copy
175
        state['log_debug'] = None # don't pickle the debug callback
176
        state['_DbConn__db'] = None # don't pickle the connection
177
        return state
178
    
179
    def connected(self): return self.__db != None
180
    
181
    def _db(self):
182
        if self.__db == None:
183
            # Process db_config
184
            db_config = self.db_config.copy() # don't modify input!
185
            schemas = db_config.pop('schemas', None)
186
            module_name, mappings = db_engines[db_config.pop('engine')]
187
            module = __import__(module_name)
188
            _add_module(module)
189
            for orig, new in mappings.iteritems():
190
                try: util.rename_key(db_config, orig, new)
191
                except KeyError: pass
192
            
193
            # Connect
194
            self.__db = module.connect(**db_config)
195
            
196
            # Configure connection
197
            if self.serializable and not self.autocommit: run_raw_query(self,
198
                'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE')
199
            if schemas != None:
200
                schemas_ = ''.join((esc_name(self, s)+', '
201
                    for s in schemas.split(',')))
202
                run_raw_query(self, "SELECT set_config('search_path', \
203
%s || current_setting('search_path'), false)", [schemas_])
204
        
205
        return self.__db
206
    
207
    class DbCursor(Proxy):
208
        def __init__(self, outer):
209
            Proxy.__init__(self, outer.db.cursor())
210
            self.outer = outer
211
            self.query_results = outer.query_results
212
            self.query_lookup = None
213
            self.result = []
214
        
215
        def execute(self, query, params=None):
216
            self._is_insert = query.upper().find('INSERT') >= 0
217
            self.query_lookup = _query_lookup(query, params)
218
            try:
219
                try:
220
                    return_value = self.inner.execute(query, params)
221
                    self.outer.do_autocommit()
222
                finally: self.query = get_cur_query(self.inner)
223
            except Exception, e:
224
                _add_cursor_info(e, self, query, params)
225
                self.result = e # cache the exception as the result
226
                self._cache_result()
227
                raise
228
            # Fetch all rows so result will be cached
229
            if self.rowcount == 0 and not self._is_insert: consume_rows(self)
230
            return return_value
231
        
232
        def fetchone(self):
233
            row = self.inner.fetchone()
234
            if row != None: self.result.append(row)
235
            # otherwise, fetched all rows
236
            else: self._cache_result()
237
            return row
238
        
239
        def _cache_result(self):
240
            # For inserts, only cache exceptions since inserts are not
241
            # idempotent, but an invalid insert will always be invalid
242
            if self.query_results != None and (not self._is_insert
243
                or isinstance(self.result, Exception)):
244
                
245
                assert self.query_lookup != None
246
                self.query_results[self.query_lookup] = self.CacheCursor(
247
                    util.dict_subset(dicts.AttrsDictView(self),
248
                    ['query', 'result', 'rowcount', 'description']))
249
        
250
        class CacheCursor:
251
            def __init__(self, cached_result): self.__dict__ = cached_result
252
            
253
            def execute(self, *args, **kw_args):
254
                if isinstance(self.result, Exception): raise self.result
255
                # otherwise, result is a rows list
256
                self.iter = iter(self.result)
257
            
258
            def fetchone(self):
259
                try: return self.iter.next()
260
                except StopIteration: return None
261
    
262
    def esc_value(self, value):
263
        module = util.root_module(self.db)
264
        if module == 'psycopg2': return self.db.cursor().mogrify('%s', [value])
265
        elif module == 'MySQLdb':
266
            import _mysql
267
            return _mysql.escape_string(value)
268
        else: raise NotImplementedError("Can't escape value for "+module
269
            +' database')
270
    
271
    def esc_name(self, name): return esc_name(self, name) # calls global func
272
    
273
    def run_query(self, query, params=None, cacheable=False):
274
        '''Translates known DB errors to typed exceptions:
275
        See self.DbCursor.execute().'''
276
        assert query != None
277
        
278
        if not self.caching: cacheable = False
279
        used_cache = False
280
        try:
281
            # Get cursor
282
            if cacheable:
283
                query_lookup = _query_lookup(query, params)
284
                try:
285
                    cur = self.query_results[query_lookup]
286
                    used_cache = True
287
                except KeyError: cur = self.DbCursor(self)
288
            else: cur = self.db.cursor()
289
            
290
            # Run query
291
            cur.execute(query, params)
292
        finally:
293
            if self.debug: # only compute msg if needed
294
                if used_cache: cache_status = 'Cache hit'
295
                elif cacheable: cache_status = 'Cache miss'
296
                else: cache_status = 'Non-cacheable'
297
                self.log_debug(cache_status+': '
298
                    +strings.one_line(str(get_cur_query(cur, query, params))))
299
        
300
        return cur
301
    
302
    def is_cached(self, query, params=None):
303
        return _query_lookup(query, params) in self.query_results
304
    
305
    def with_savepoint(self, func):
306
        savepoint = 'level_'+str(self._savepoint)
307
        self.run_query('SAVEPOINT '+savepoint)
308
        self._savepoint += 1
309
        try: 
310
            try: return_val = func()
311
            finally:
312
                self._savepoint -= 1
313
                assert self._savepoint >= 0
314
        except:
315
            self.run_query('ROLLBACK TO SAVEPOINT '+savepoint)
316
            raise
317
        else:
318
            self.run_query('RELEASE SAVEPOINT '+savepoint)
319
            self.do_autocommit()
320
            return return_val
321
    
322
    def do_autocommit(self):
323
        '''Autocommits if outside savepoint'''
324
        assert self._savepoint >= 0
325
        if self.autocommit and self._savepoint == 0:
326
            self.log_debug('Autocommiting')
327
            self.db.commit()
328

    
329
connect = DbConn
330

    
331
##### Querying
332

    
333
def run_raw_query(db, *args, **kw_args):
334
    '''For params, see DbConn.run_query()'''
335
    return db.run_query(*args, **kw_args)
336

    
337
def mogrify(db, query, params):
338
    module = util.root_module(db.db)
339
    if module == 'psycopg2': return db.db.cursor().mogrify(query, params)
340
    else: raise NotImplementedError("Can't mogrify query for "+module+
341
        ' database')
342

    
343
##### Recoverable querying
344

    
345
def with_savepoint(db, func): return db.with_savepoint(func)
346

    
347
def run_query(db, query, params=None, recover=None, cacheable=False):
348
    if recover == None: recover = False
349
    
350
    try:
351
        def run(): return run_raw_query(db, query, params, cacheable)
352
        if recover and not db.is_cached(query, params):
353
            return with_savepoint(db, run)
354
        else: return run() # don't need savepoint if cached
355
    except Exception, e:
356
        if not recover: raise # need savepoint to run index_cols()
357
        msg = exc.str_(e)
358
        
359
        match = re.search(r'duplicate key value violates unique constraint '
360
            r'"((_?[^\W_]+)_[^"]+?)"', msg)
361
        if match:
362
            constraint, table = match.groups()
363
            try: cols = index_cols(db, table, constraint)
364
            except NotImplementedError: raise e
365
            else: raise DuplicateKeyException(constraint, cols, e)
366
        
367
        match = re.search(r'null value in column "(\w+?)" violates not-null '
368
            r'constraint', msg)
369
        if match: raise NullValueException('NOT NULL', [match.group(1)], e)
370
        
371
        match = re.search(r'invalid input (?:syntax|value)\b.*?: "(.+?)"\n'
372
            r'(?:(?s).*?)\bfunction "(\w+?)".*?\bat assignment', msg)
373
        if match:
374
            raise FunctionValueException(match.group(2),
375
                strings.to_unicode(match.group(1)), e)
376
        
377
        match = re.search(r'relation "(\w+?)" already exists', msg)
378
        if match: raise DuplicateTableException(match.group(1), e)
379
        
380
        match = re.search(r'function "(\w+?)" already exists', msg)
381
        if match: raise DuplicateFunctionException(match.group(1), e)
382
        
383
        raise # no specific exception raised
384

    
385
##### Basic queries
386

    
387
def next_version(name):
388
    '''Prepends the version # so it won't be removed if the name is truncated'''
389
    version = 1 # first existing name was version 0
390
    match = re.match(r'^v(\d+)_(.*)$', name)
391
    if match:
392
        version = int(match.group(1))+1
393
        name = match.group(2)
394
    return 'v'+str(version)+'_'+name
395

    
396
def run_query_into(db, query, params, into_ref=None, *args, **kw_args):
397
    '''Outputs a query to a temp table.
398
    For params, see run_query().
399
    '''
400
    if into_ref == None: return run_query(db, query, params, *args, **kw_args)
401
    else: # place rows in temp table
402
        kw_args['recover'] = True
403
        while True:
404
            try:
405
                create_query = 'CREATE'
406
                if not db.debug: create_query += ' TEMP'
407
                into = sql_gen.as_Table(into_ref[0]).to_str(db)
408
                create_query += ' TABLE '+into+' AS '+query
409
                
410
                return run_query(db, create_query, params, *args, **kw_args)
411
                    # CREATE TABLE AS sets rowcount to # rows in query
412
            except DuplicateTableException, e:
413
                into_ref[0] = next_version(into_ref[0])
414
                # try again with next version of name
415

    
416
order_by_pkey = object() # tells mk_select() to order by the pkey
417

    
418
distinct_on_all = object() # tells mk_select() to SELECT DISTINCT ON all columns
419

    
420
def mk_select(db, tables, fields=None, conds=None, distinct_on=[], limit=None,
421
    start=None, order_by=order_by_pkey, default_table=None):
422
    '''
423
    @param tables The single table to select from, or a list of tables to join
424
        together, with tables after the first being sql_gen.Join objects
425
    @param fields Use None to select all fields in the table
426
    @param conds WHERE conditions: dict(compare_left_side=compare_right_side):
427
        * compare_left_side: Code|str (for col name)
428
        * compare_right_side: ValueCond|literal value
429
    @param distinct_on The columns to SELECT DISTINCT ON, or distinct_on_all to
430
        use all columns
431
    @return tuple(query, params)
432
    '''
433
    # Parse tables param
434
    if not lists.is_seq(tables): tables = [tables]
435
    tables = list(tables) # don't modify input! (list() copies input)
436
    table0 = sql_gen.as_Table(tables.pop(0)) # first table is separate
437
    
438
    # Parse other params
439
    if conds == None: conds = {}
440
    assert limit == None or type(limit) == int
441
    assert start == None or type(start) == int
442
    if order_by is order_by_pkey:
443
        if distinct_on != []: order_by = None
444
        else: order_by = pkey(db, table0, recover=True)
445
    
446
    query = 'SELECT'
447
    
448
    def parse_col(col): return sql_gen.as_Col(col, default_table).to_str(db)
449
    
450
    # DISTINCT ON columns
451
    if distinct_on != []:
452
        query += ' DISTINCT'
453
        if distinct_on is not distinct_on_all:
454
            query += ' ON ('+(', '.join(map(parse_col, distinct_on)))+')'
455
    
456
    # Columns
457
    query += ' '
458
    if fields == None: query += '*'
459
    else: query += ', '.join(map(parse_col, fields))
460
    
461
    # Main table
462
    query += ' FROM '+table0.to_str(db)
463
    
464
    # Add joins
465
    left_table = table0
466
    for join_ in tables:
467
        table = join_.table
468
        
469
        # Parse special values
470
        if join_.type_ is sql_gen.filter_out: # filter no match
471
            conds[sql_gen.Col(table_not_null_col(db, table), table)] = None
472
        
473
        query += ' '+join_.to_str(db, left_table)
474
        
475
        left_table = table
476
    
477
    missing = True
478
    if conds != {}:
479
        query += ' WHERE '+(' AND '.join((sql_gen.as_ValueCond(r).to_str(db, l)
480
            for l, r in conds.iteritems())))
481
        missing = False
482
    if order_by != None:
483
        query += ' ORDER BY '+sql_gen.as_Col(order_by, table0).to_str(db)
484
    if limit != None: query += ' LIMIT '+str(limit); missing = False
485
    if start != None:
486
        if start != 0: query += ' OFFSET '+str(start)
487
        missing = False
488
    if missing: warnings.warn(DbWarning(
489
        'SELECT statement missing a WHERE, LIMIT, or OFFSET clause: '+query))
490
    
491
    return (query, [])
492

    
493
def select(db, *args, **kw_args):
494
    '''For params, see mk_select() and run_query()'''
495
    recover = kw_args.pop('recover', None)
496
    cacheable = kw_args.pop('cacheable', True)
497
    
498
    query, params = mk_select(db, *args, **kw_args)
499
    return run_query(db, query, params, recover, cacheable)
500

    
501
def mk_insert_select(db, table, cols=None, select_query=None, params=None,
502
    returning=None, embeddable=False):
503
    '''
504
    @param returning str|None An inserted column (such as pkey) to return
505
    @param embeddable Whether the query should be embeddable as a nested SELECT.
506
        Warning: If you set this and cacheable=True when the query is run, the
507
        query will be fully cached, not just if it raises an exception.
508
    '''
509
    table = sql_gen.as_Table(table)
510
    if cols == []: cols = None # no cols (all defaults) = unknown col names
511
    if cols != None: cols = [sql_gen.as_Col(v).to_str(db) for v in cols]
512
    if select_query == None: select_query = 'DEFAULT VALUES'
513
    if returning != None: returning = sql_gen.as_Col(returning, table)
514
    
515
    # Build query
516
    query = 'INSERT INTO '+table.to_str(db)
517
    if cols != None: query += ' ('+', '.join(cols)+')'
518
    query += ' '+select_query
519
    
520
    if returning != None:
521
        returning_name = copy.copy(returning)
522
        returning_name.table = None
523
        returning_name = returning_name.to_str(db)
524
        query += ' RETURNING '+returning_name
525
    
526
    if embeddable:
527
        assert returning != None
528
        
529
        # Create function
530
        function_name = '_'.join(['insert', table.name] + cols)
531
        return_type = 'SETOF '+returning.to_str(db)+'%TYPE'
532
        while True:
533
            try:
534
                func_schema = None
535
                if not db.debug: func_schema = 'pg_temp'
536
                function = sql_gen.Table(function_name, func_schema).to_str(db)
537
                
538
                function_query = '''\
539
CREATE FUNCTION '''+function+'''() RETURNS '''+return_type+'''
540
    LANGUAGE sql
541
    AS $$'''+mogrify(db, query, params)+''';$$;
542
'''
543
                run_query(db, function_query, recover=True, cacheable=True)
544
                break # this version was successful
545
            except DuplicateFunctionException, e:
546
                function_name = next_version(function_name)
547
                # try again with next version of name
548
        
549
        # Return query that uses function
550
        func_table = sql_gen.NamedTable('f', sql_gen.CustomCode(function+'()'),
551
            [returning_name]) # AS clause requires function alias
552
        return mk_select(db, func_table, start=0, order_by=None)
553
    
554
    return (query, params)
555

    
556
def insert_select(db, *args, **kw_args):
557
    '''For params, see mk_insert_select() and run_query_into()
558
    @param into_ref List with name of temp table to place RETURNING values in
559
    '''
560
    into_ref = kw_args.pop('into_ref', None)
561
    if into_ref != None: kw_args['embeddable'] = True
562
    recover = kw_args.pop('recover', None)
563
    cacheable = kw_args.pop('cacheable', True)
564
    
565
    query, params = mk_insert_select(db, *args, **kw_args)
566
    return run_query_into(db, query, params, into_ref, recover=recover,
567
        cacheable=cacheable)
568

    
569
default = object() # tells insert() to use the default value for a column
570

    
571
def insert(db, table, row, *args, **kw_args):
572
    '''For params, see insert_select()'''
573
    if lists.is_seq(row): cols = None
574
    else:
575
        cols = row.keys()
576
        row = row.values()
577
    row = list(row) # ensure that "!= []" works
578
    
579
    # Check for special values
580
    labels = []
581
    values = []
582
    for value in row:
583
        if value is default: labels.append('DEFAULT')
584
        else:
585
            labels.append('%s')
586
            values.append(value)
587
    
588
    # Build query
589
    if values != []: query = ' VALUES ('+(', '.join(labels))+')'
590
    else: query = None
591
    
592
    return insert_select(db, table, cols, query, values, *args, **kw_args)
593

    
594
def last_insert_id(db):
595
    module = util.root_module(db.db)
596
    if module == 'psycopg2': return value(run_query(db, 'SELECT lastval()'))
597
    elif module == 'MySQLdb': return db.insert_id()
598
    else: return None
599

    
600
def truncate(db, table, schema='public'):
601
    return run_query(db, 'TRUNCATE '+qual_name(db, schema, table)+' CASCADE')
602

    
603
##### Database structure queries
604

    
605
def table_cols(db, table, recover=None):
606
    return list(col_names(select(db, table, limit=0, order_by=None,
607
        recover=recover)))
608

    
609
def pkey(db, table, recover=None):
610
    '''Assumed to be first column in table'''
611
    return table_cols(db, table, recover)[0]
612

    
613
not_null_col = 'not_null'
614

    
615
def table_not_null_col(db, table, recover=None):
616
    '''Name assumed to be the value of not_null_col. If not found, uses pkey.'''
617
    if not_null_col in table_cols(db, table, recover): return not_null_col
618
    else: return pkey(db, table, recover)
619

    
620
def index_cols(db, table, index):
621
    '''Can also use this for UNIQUE constraints, because a UNIQUE index is
622
    automatically created. When you don't know whether something is a UNIQUE
623
    constraint or a UNIQUE index, use this function.'''
624
    module = util.root_module(db.db)
625
    if module == 'psycopg2':
626
        return list(values(run_query(db, '''\
627
SELECT attname
628
FROM
629
(
630
        SELECT attnum, attname
631
        FROM pg_index
632
        JOIN pg_class index ON index.oid = indexrelid
633
        JOIN pg_class table_ ON table_.oid = indrelid
634
        JOIN pg_attribute ON attrelid = indrelid AND attnum = ANY (indkey)
635
        WHERE
636
            table_.relname = %(table)s
637
            AND index.relname = %(index)s
638
    UNION
639
        SELECT attnum, attname
640
        FROM
641
        (
642
            SELECT
643
                indrelid
644
                , (regexp_matches(indexprs, E':varattno (\\\\d+)', 'g'))[1]::int
645
                    AS indkey
646
            FROM pg_index
647
            JOIN pg_class index ON index.oid = indexrelid
648
            JOIN pg_class table_ ON table_.oid = indrelid
649
            WHERE
650
                table_.relname = %(table)s
651
                AND index.relname = %(index)s
652
        ) s
653
        JOIN pg_attribute ON attrelid = indrelid AND attnum = indkey
654
) s
655
ORDER BY attnum
656
''',
657
            {'table': table, 'index': index}, cacheable=True)))
658
    else: raise NotImplementedError("Can't list index columns for "+module+
659
        ' database')
660

    
661
def constraint_cols(db, table, constraint):
662
    module = util.root_module(db.db)
663
    if module == 'psycopg2':
664
        return list(values(run_query(db, '''\
665
SELECT attname
666
FROM pg_constraint
667
JOIN pg_class ON pg_class.oid = conrelid
668
JOIN pg_attribute ON attrelid = conrelid AND attnum = ANY (conkey)
669
WHERE
670
    relname = %(table)s
671
    AND conname = %(constraint)s
672
ORDER BY attnum
673
''',
674
            {'table': table, 'constraint': constraint})))
675
    else: raise NotImplementedError("Can't list constraint columns for "+module+
676
        ' database')
677

    
678
row_num_col = '_row_num'
679

    
680
def add_row_num(db, table):
681
    '''Adds a row number column to a table. Its name is in row_num_col. It will
682
    be the primary key.'''
683
    table = sql_gen.as_Table(table).to_str(db)
684
    run_query(db, 'ALTER TABLE '+table+' ADD COLUMN '+row_num_col
685
        +' serial NOT NULL PRIMARY KEY')
686

    
687
def tables(db, schema='public', table_like='%'):
688
    module = util.root_module(db.db)
689
    params = {'schema': schema, 'table_like': table_like}
690
    if module == 'psycopg2':
691
        return values(run_query(db, '''\
692
SELECT tablename
693
FROM pg_tables
694
WHERE
695
    schemaname = %(schema)s
696
    AND tablename LIKE %(table_like)s
697
ORDER BY tablename
698
''',
699
            params, cacheable=True))
700
    elif module == 'MySQLdb':
701
        return values(run_query(db, 'SHOW TABLES LIKE %(table_like)s', params,
702
            cacheable=True))
703
    else: raise NotImplementedError("Can't list tables for "+module+' database')
704

    
705
##### Database management
706

    
707
def empty_db(db, schema='public', **kw_args):
708
    '''For kw_args, see tables()'''
709
    for table in tables(db, schema, **kw_args): truncate(db, table, schema)
710

    
711
##### Heuristic queries
712

    
713
def put(db, table, row, pkey_=None, row_ct_ref=None):
714
    '''Recovers from errors.
715
    Only works under PostgreSQL (uses INSERT RETURNING).
716
    '''
717
    if pkey_ == None: pkey_ = pkey(db, table, recover=True)
718
    
719
    try:
720
        cur = insert(db, table, row, pkey_, recover=True)
721
        if row_ct_ref != None and cur.rowcount >= 0:
722
            row_ct_ref[0] += cur.rowcount
723
        return value(cur)
724
    except DuplicateKeyException, e:
725
        return value(select(db, table, [pkey_],
726
            util.dict_subset_right_join(row, e.cols), recover=True))
727

    
728
def get(db, table, row, pkey, row_ct_ref=None, create=False):
729
    '''Recovers from errors'''
730
    try: return value(select(db, table, [pkey], row, limit=1, recover=True))
731
    except StopIteration:
732
        if not create: raise
733
        return put(db, table, row, pkey, row_ct_ref) # insert new row
734

    
735
def put_table(db, out_table, in_tables, mapping, limit=None, start=0,
736
    row_ct_ref=None):
737
    '''Recovers from errors.
738
    Only works under PostgreSQL (uses INSERT RETURNING).
739
    @param in_tables The main input table to select from, followed by a list of
740
        tables to join with it using the main input table's pkey
741
    @param mapping dict(out_table_col=in_table_col, ...)
742
        * out_table_col: sql_gen.Col|str
743
        * in_table_col: sql_gen.Col Wrap literal values in a sql_gen.NamedCol
744
    @return sql_gen.Col Where the output pkeys are made available
745
    '''
746
    out_table = sql_gen.as_Table(out_table)
747
    for in_table_col in mapping.itervalues():
748
        assert isinstance(in_table_col, sql_gen.Col)
749
    
750
    temp_prefix = out_table.name
751
    pkeys_ref = [temp_prefix+'_pkeys']
752
    
753
    # Join together input tables
754
    in_tables = in_tables[:] # don't modify input!
755
    in_tables0 = in_tables.pop(0) # first table is separate
756
    in_pkey = pkey(db, in_tables0, recover=True)
757
    in_pkey_col = sql_gen.as_Col(in_pkey, in_tables0)
758
    input_joins = [in_tables0]+[sql_gen.Join(v, {in_pkey: sql_gen.join_same})
759
        for v in in_tables]
760
    
761
    out_pkey = pkey(db, out_table, recover=True)
762
    out_pkey_col = sql_gen.as_Col(out_pkey, out_table)
763
    
764
    pkeys = [in_pkey, out_pkey]
765
    pkeys_cols = [in_pkey_col, out_pkey_col]
766
    
767
    pkeys_table_exists_ref = [False]
768
    def run_query_into_pkeys(query, params):
769
        if pkeys_table_exists_ref[0]:
770
            insert_select(db, pkeys_ref[0], pkeys, query, params)
771
        else:
772
            run_query_into(db, query, params, into_ref=pkeys_ref)
773
            pkeys_table_exists_ref[0] = True
774
    
775
    conds = {}
776
    distinct_on = []
777
    def mk_main_select(joins, cols):
778
        return mk_select(db, joins, cols, conds, distinct_on, limit=limit,
779
            start=start, default_table=in_tables0)
780
    
781
    def log_exc(e):
782
        db.log_debug('Caught exception: '+exc.str_(e, first_line_only=True))
783
    def ignore(in_col, value):
784
        db.log_debug('Ignoring rows with '+str(in_col)+' = '+repr(value))
785
        assert in_col not in conds # avoid infinite loops
786
        conds[in_col] = sql_gen.CompareCond(value, '!=')
787
    
788
    # Do inserts and selects
789
    join_cols = {}
790
    out_pkeys_ref = [temp_prefix+'_out_pkeys']
791
    in_pkeys_ref = [temp_prefix+'_in_pkeys']
792
    while True:
793
        has_joins = join_cols != {}
794
        
795
        # Prepare to insert new rows
796
        insert_joins = input_joins[:] # don't modify original!
797
        insert_args = dict(recover=True)
798
        if has_joins:
799
            distinct_on = [v.to_Col() for v in join_cols.values()]
800
            insert_joins.append(sql_gen.Join(out_table, join_cols,
801
                sql_gen.filter_out))
802
        else:
803
            insert_args.update(dict(returning=out_pkey, into_ref=out_pkeys_ref))
804
        
805
        db.log_debug('Inserting new rows')
806
        try:
807
            cur = insert_select(db, out_table, mapping.keys(),
808
                *mk_main_select(insert_joins, mapping.values()), **insert_args)
809
            break # insert successful
810
        except DuplicateKeyException, e:
811
            log_exc(e)
812
            
813
            old_join_cols = join_cols.copy()
814
            join_cols.update(util.dict_subset(mapping, e.cols))
815
            db.log_debug('Ignoring existing rows, comparing on '+str(join_cols))
816
            assert join_cols != old_join_cols # avoid infinite loops
817
        except NullValueException, e:
818
            log_exc(e)
819
            
820
            out_col, = e.cols
821
            try: in_col = mapping[out_col]
822
            except KeyError:
823
                db.log_debug('Missing mapping for NOT NULL '+out_col)
824
                limit = 0 # just create an empty pkeys table
825
            else: ignore(in_col, None)
826
        except FunctionValueException, e:
827
            log_exc(e)
828
            
829
            assert e.name == out_table.name
830
            out_col = 'value' # assume function param was named "value"
831
            ignore(mapping[out_col], e.value)
832
        # after exception handled, rerun loop with additional constraints
833
    
834
    if row_ct_ref != None and cur.rowcount >= 0:
835
        row_ct_ref[0] += cur.rowcount
836
    
837
    if has_joins:
838
        select_joins = input_joins+[sql_gen.Join(out_table, join_cols)]
839
        db.log_debug('Getting output pkeys of existing/inserted rows')
840
        run_query_into_pkeys(*mk_select(db, select_joins, pkeys_cols,
841
            start=0))
842
    else:
843
        add_row_num(db, out_pkeys_ref[0]) # for joining with input pkeys
844
        
845
        db.log_debug('Getting input pkeys for rows in insert')
846
        run_query_into(db, *mk_main_select(input_joins, [in_pkey]),
847
            into_ref=in_pkeys_ref)
848
        add_row_num(db, in_pkeys_ref[0]) # for joining with output pkeys
849
        
850
        db.log_debug('Joining together output and input pkeys')
851
        pkey_joins = [in_pkeys_ref[0], sql_gen.Join(out_pkeys_ref[0],
852
            {row_num_col: sql_gen.join_same_not_null})]
853
        run_query_into_pkeys(*mk_select(db, pkey_joins, pkeys, start=0))
854
    
855
    db.log_debug("Setting missing rows' pkeys to NULL")
856
    missing_rows_joins = input_joins+[sql_gen.Join(pkeys_ref[0],
857
        {in_pkey: sql_gen.join_same_not_null}, sql_gen.filter_out)]
858
        # must use join_same_not_null or query will take forever
859
    run_query_into_pkeys(*mk_select(db, missing_rows_joins,
860
        [in_pkey_col, sql_gen.NamedCol(out_pkey, None)], start=0))
861
    
862
    return sql_gen.Col(out_pkey, pkeys_ref[0])
863

    
864
##### Data cleanup
865

    
866
def cleanup_table(db, table, cols):
867
    def esc_name_(name): return esc_name(db, name)
868
    
869
    table = sql_gen.as_Table(table).to_str(db)
870
    cols = map(esc_name_, cols)
871
    
872
    run_query(db, 'UPDATE '+table+' SET\n'+(',\n'.join(('\n'+col
873
        +' = nullif(nullif(trim(both from '+col+"), %(null0)s), %(null1)s)"
874
            for col in cols))),
875
        dict(null0='', null1=r'\N'))
(23-23/35)