##### Database structure introspection

#### Tables

def tables(db, schema_like='public', table_like='%', exact=False):

    if exact: compare = '='

    else: compare = 'LIKE'

    module = util.root_module(db.db)

    if module == 'psycopg2':

        conds = [('schemaname', sql_gen.CompareCond(schema_like, compare)),

            ('tablename', sql_gen.CompareCond(table_like, compare))]

        return values(select(db, 'pg_tables', ['tablename'], conds,

            order_by='tablename', log_level=4))

    elif module == 'MySQLdb':

        return values(run_query(db, 'SHOW TABLES LIKE '+db.esc_value(table_like)

            , cacheable=True, log_level=4))

    else: raise NotImplementedError("Can't list tables for "+module+' database')

def table_exists(db, table):

    table = sql_gen.as_Table(table)

    return list(tables(db, table.schema, table.name, exact=True)) != []

#### Functions

def function_exists(db, function):

    function = sql_gen.as_Function(function)

    info_table = sql_gen.Table('routines', 'information_schema')

    conds = [('routine_name', function.name)]

    schema = function.schema

    if schema != None: conds.append(('routine_schema', schema))

    # Exclude trigger functions, since they cannot be called directly

    conds.append(('data_type', sql_gen.CompareCond('trigger', '!=')))

    return list(values(select(db, info_table, ['routine_name'], conds,

        order_by='routine_schema', limit=1, log_level=4))) != []

        # TODO: order_by search_path schema order

##### Structural changes

#### Columns

def add_col(db, table, col, comment=None, **kw_args):

    '''

    @param col TypedCol Name may be versioned, so be sure to propagate any

        renaming back to any source column for the TypedCol.

    @param comment None|str SQL comment used to distinguish columns of the same

        name from each other when they contain different data, to allow the

        ADD COLUMN query to be cached. If not set, query will not be cached.

    '''

    assert isinstance(col, sql_gen.TypedCol)

    while True:

        str_ = 'ALTER TABLE '+table.to_str(db)+' ADD COLUMN '+col.to_str(db)

        if comment != None: str_ += ' '+sql_gen.esc_comment(comment)

        try:

            run_query(db, str_, recover=True, cacheable=True, **kw_args)

            break

        except DuplicateException:

            col.name = next_version(col.name)

            # try again with next version of name

def add_not_null(db, col):

    table = col.table

    col = sql_gen.to_name_only_col(col)

    run_query(db, 'ALTER TABLE '+table.to_str(db)+' ALTER COLUMN '

        +col.to_str(db)+' SET NOT NULL', cacheable=True, log_level=3)

row_num_typed_col = sql_gen.TypedCol(row_num_col, 'serial', nullable=False,

    constraints='PRIMARY KEY')

def add_row_num(db, table):

    '''Adds a row number column to a table. Its name is in row_num_col. It will

    be the primary key.'''

    add_col(db, table, row_num_typed_col, log_level=3)

#### Indexes

def add_pkey(db, table, cols=None, recover=None):

    '''Adds a primary key.

    @param cols [sql_gen.Col,...] The columns in the primary key.

        Defaults to the first column in the table.

    @pre The table must not already have a primary key.

    '''

    table = sql_gen.as_Table(table)

    if cols == None: cols = [pkey(db, table, recover)]

    col_strs = [sql_gen.to_name_only_col(v).to_str(db) for v in cols]

    run_query(db, 'ALTER TABLE '+table.to_str(db)+' ADD PRIMARY KEY ('

        +(', '.join(col_strs))+')', recover=True, cacheable=True, log_level=3,

        log_ignore_excs=(DuplicateException,))

#### Tables

### Maintenance

def analyze(db, table):

    table = sql_gen.as_Table(table)

    run_query(db, 'ANALYZE '+table.to_str(db), log_level=3)

def autoanalyze(db, table):

    if db.autoanalyze: analyze(db, table)

def vacuum(db, table):

    table = sql_gen.as_Table(table)

    db.with_autocommit(lambda: run_query(db, 'VACUUM ANALYZE '+table.to_str(db),

        log_level=3))

### Lifecycle

already_indexed = object() # tells add_indexes() the pkey has already been added

def add_indexes(db, table, has_pkey=True):

    '''Adds an index on all columns in a table.

    @param has_pkey bool|already_indexed Whether a pkey instead of a regular

        index should be added on the first column.

        * If already_indexed, the pkey is assumed to have already been added

    '''

    cols = table_cols(db, table)

    if has_pkey:

        if has_pkey is not already_indexed: add_pkey(db, table)

        cols = cols[1:]

    for col in cols: add_index(db, col, table)

### Data

##### Database management