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 repr(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: '+strings.ustr(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 constraint '+name+ ' 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 run_query(self, query, params=None, cacheable=False):
272
        '''Translates known DB errors to typed exceptions:
273
        See self.DbCursor.execute().'''
274
        assert query != None
275
        
276
        if not self.caching: cacheable = False
277
        used_cache = False
278
        try:
279
            # Get cursor
280
            if cacheable:
281
                query_lookup = _query_lookup(query, params)
282
                try:
283
                    cur = self.query_results[query_lookup]
284
                    used_cache = True
285
                except KeyError: cur = self.DbCursor(self)
286
            else: cur = self.db.cursor()
287
            
288
            # Run query
289
            cur.execute(query, params)
290
        finally:
291
            if self.debug: # only compute msg if needed
292
                if used_cache: cache_status = 'Cache hit'
293
                elif cacheable: cache_status = 'Cache miss'
294
                else: cache_status = 'Non-cacheable'
295
                self.log_debug(cache_status+': '
296
                    +strings.one_line(str(get_cur_query(cur, query, params))))
297
        
298
        return cur
299
    
300
    def is_cached(self, query, params=None):
301
        return _query_lookup(query, params) in self.query_results
302
    
303
    def with_savepoint(self, func):
304
        savepoint = 'level_'+str(self._savepoint)
305
        self.run_query('SAVEPOINT '+savepoint)
306
        self._savepoint += 1
307
        try: 
308
            try: return_val = func()
309
            finally:
310
                self._savepoint -= 1
311
                assert self._savepoint >= 0
312
        except:
313
            self.run_query('ROLLBACK TO SAVEPOINT '+savepoint)
314
            raise
315
        else:
316
            self.run_query('RELEASE SAVEPOINT '+savepoint)
317
            self.do_autocommit()
318
            return return_val
319
    
320
    def do_autocommit(self):
321
        '''Autocommits if outside savepoint'''
322
        assert self._savepoint >= 0
323
        if self.autocommit and self._savepoint == 0:
324
            self.log_debug('Autocommiting')
325
            self.db.commit()
326

    
327
connect = DbConn
328

    
329
##### Querying
330

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

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

    
341
##### Recoverable querying
342

    
343
def with_savepoint(db, func): return db.with_savepoint(func)
344

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

    
383
##### Basic queries
384

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

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

    
414
order_by_pkey = object() # tells mk_select() to order by the pkey
415

    
416
distinct_on_all = object() # tells mk_select() to SELECT DISTINCT ON all columns
417

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

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

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

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

    
565
default = object() # tells insert() to use the default value for a column
566

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

    
590
def last_insert_id(db):
591
    module = util.root_module(db.db)
592
    if module == 'psycopg2': return value(run_query(db, 'SELECT lastval()'))
593
    elif module == 'MySQLdb': return db.insert_id()
594
    else: return None
595

    
596
def truncate(db, table, schema='public'):
597
    return run_query(db, 'TRUNCATE '+qual_name(db, schema, table)+' CASCADE')
598

    
599
##### Database structure queries
600

    
601
def pkey(db, table, recover=None):
602
    '''Assumed to be first column in table'''
603
    return col_names(select(db, table, limit=0, order_by=None,
604
        recover=recover)).next()
605

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

    
647
def constraint_cols(db, table, constraint):
648
    module = util.root_module(db.db)
649
    if module == 'psycopg2':
650
        return list(values(run_query(db, '''\
651
SELECT attname
652
FROM pg_constraint
653
JOIN pg_class ON pg_class.oid = conrelid
654
JOIN pg_attribute ON attrelid = conrelid AND attnum = ANY (conkey)
655
WHERE
656
    relname = %(table)s
657
    AND conname = %(constraint)s
658
ORDER BY attnum
659
''',
660
            {'table': table, 'constraint': constraint})))
661
    else: raise NotImplementedError("Can't list constraint columns for "+module+
662
        ' database')
663

    
664
row_num_col = '_row_num'
665

    
666
def add_row_num(db, table):
667
    '''Adds a row number column to a table. Its name is in row_num_col. It will
668
    be the primary key.'''
669
    table = sql_gen.as_Table(table).to_str(db)
670
    run_query(db, 'ALTER TABLE '+table+' ADD COLUMN '+row_num_col
671
        +' serial NOT NULL PRIMARY KEY')
672

    
673
def tables(db, schema='public', table_like='%'):
674
    module = util.root_module(db.db)
675
    params = {'schema': schema, 'table_like': table_like}
676
    if module == 'psycopg2':
677
        return values(run_query(db, '''\
678
SELECT tablename
679
FROM pg_tables
680
WHERE
681
    schemaname = %(schema)s
682
    AND tablename LIKE %(table_like)s
683
ORDER BY tablename
684
''',
685
            params, cacheable=True))
686
    elif module == 'MySQLdb':
687
        return values(run_query(db, 'SHOW TABLES LIKE %(table_like)s', params,
688
            cacheable=True))
689
    else: raise NotImplementedError("Can't list tables for "+module+' database')
690

    
691
##### Database management
692

    
693
def empty_db(db, schema='public', **kw_args):
694
    '''For kw_args, see tables()'''
695
    for table in tables(db, schema, **kw_args): truncate(db, table, schema)
696

    
697
##### Heuristic queries
698

    
699
def put(db, table, row, pkey_=None, row_ct_ref=None):
700
    '''Recovers from errors.
701
    Only works under PostgreSQL (uses INSERT RETURNING).
702
    '''
703
    if pkey_ == None: pkey_ = pkey(db, table, recover=True)
704
    
705
    try:
706
        cur = insert(db, table, row, pkey_, recover=True)
707
        if row_ct_ref != None and cur.rowcount >= 0:
708
            row_ct_ref[0] += cur.rowcount
709
        return value(cur)
710
    except DuplicateKeyException, e:
711
        return value(select(db, table, [pkey_],
712
            util.dict_subset_right_join(row, e.cols), recover=True))
713

    
714
def get(db, table, row, pkey, row_ct_ref=None, create=False):
715
    '''Recovers from errors'''
716
    try: return value(select(db, table, [pkey], row, limit=1, recover=True))
717
    except StopIteration:
718
        if not create: raise
719
        return put(db, table, row, pkey, row_ct_ref) # insert new row
720

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

    
852
##### Data cleanup
853

    
854
def cleanup_table(db, table, cols):
855
    def esc_name_(name): return esc_name(db, name)
856
    
857
    table = sql_gen.as_Table(table).to_str(db)
858
    cols = map(esc_name_, cols)
859
    
860
    run_query(db, 'UPDATE '+table+' SET\n'+(',\n'.join(('\n'+col
861
        +' = nullif(nullif(trim(both from '+col+"), %(null0)s), %(null1)s)"
862
            for col in cols))),
863
        dict(null0='', null1=r'\N'))
(22-22/34)