<commit_before>from __future__ import unicode_literals
import logging
import shlex
import subprocess
from collections import namedtuple

from .main import special_command, RAW_QUERY

TableInfo = namedtuple("TableInfo", [
    'checks', 'relkind', 'hasindex', 'hasrules', 'hastriggers', 'hasoids',
    'tablespace', 'reloptions', 'reloftype', 'relpersistence', 'relispartition'])

log = logging.getLogger(__name__)


@special_command('\\l', '\\l[+] [pattern]', 'List databases.', aliases=('\\list',))
def list_databases(cur, pattern, verbose):
    query = '''SELECT d.datname as "Name",
        pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
        pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
        d.datcollate as "Collate",
        d.datctype as "Ctype",
        pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"'''
    if verbose:
        query += ''',
            CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
                    THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
                    ELSE 'No Access'
            END as "Size",
            t.spcname as "Tablespace",
            pg_catalog.shobj_description(d.oid, 'pg_database') as "Description"'''
    query += '''
    FROM pg_catalog.pg_database d
    '''
    if verbose:
        query += '''
        JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid
        '''
    params = []
    if pattern:
        query += '''
        WHERE d.datname ~ %s
        '''
        _, schema = sql_name_pattern(pattern)
        params.append(schema)
    query = cur.mogrify(query + ' ORDER BY 1', params)
    cur.execute(query)
    if cur.description:
        headers = [x[0] for x in cur.description]
        return [(None, cur, headers, cur.statusmessage)]
    else:
        return [(None, None, None, cur.statusmessage)]


@special_command('\\du', '\\du[+] [pattern]', 'List roles.')
def list_roles(cur, pattern, verbose):
    """
    Returns (title, rows, headers, status)
    """

    if cur.connection.server_version > 90000:
        sql = '''
            SELECT r.rolname,
                r.rolsuper,
                r.rolinherit,
                r.rolcreaterole,
                r.rolcreatedb,
                r.rolcanlogin,
                r.rolconnlimit,
                r.rolvaliduntil,
                ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid) as memberof,
            '''
        if verbose:
            sql += '''
                pg_catalog.shobj_description(r.oid, 'pg_authid') AS description,
            '''
        sql += '''
            r.rolreplication
        FROM pg_catalog.pg_roles r
        '''
    else:
        sql = '''
            SELECT u.usename AS rolname,
                u.usesuper AS rolsuper,
                true AS rolinherit,
                false AS rolcreaterole,
                u.usecreatedb AS rolcreatedb,
                true AS rolcanlogin,
                -1 AS rolconnlimit,
                u.valuntil as rolvaliduntil,
                ARRAY(SELECT g.groname FROM pg_catalog.pg_group g WHERE u.usesysid = ANY(g.grolist)) as memberof
            FROM pg_catalog.pg_user u
            '''

    params = []
    if pattern:
        _, schema = sql_name_pattern(pattern)
        sql += 'WHERE r.rolname ~ %s'
        params.append(schema)
    sql = cur.mogrify(sql + " ORDER BY 1", params)

    log.debug(sql)
    cur.execute(sql)
    if cur.description:
        headers = [x[0] for x in cur.description]
        return [(None, cur, headers, cur.statusmessage)]


@special_command('\\db', '\\db[+] [pattern]', 'List tablespaces.')
def list_tablespaces(cur, pattern, **_):
    """
    Returns (title, rows, headers, status)
    """

    cur.execute("SELECT EXISTS(SELECT * FROM pg_proc WHERE proname = 'pg_tablespace_location')")
    (is_location,) = cur.fetchone()

    sql = '''SELECT n.spcname AS "Name",
    pg_catalog.pg_get_userbyid(n.spcowner) AS "Owner",'''

    sql += " pg_catalog.pg_tablespace_location(n.oid)" if is_location else " 'Not supported'"
    sql += ''' AS "Location"
    FROM pg_catalog.pg_tablespace n'''

    params = []
    if pattern:
        _, tbsp = sql_name_pattern(pattern)
        sql += " WHERE n.spcname ~ %s"
        params.append(tbsp)

    sql = cur.mogrify(sql + " ORDER BY 1", params)
    log.debug(sql)
    cur.execute(sql)

    headers = [x[0] for x in cur.description] if cur.description else None
    return [(None, cur, headers, cur.statusmessage)]


@special_command('\\dn', '\\dn[+] [pattern]', 'List schemas.')
def list_schemas(cur, pattern, verbose):
    """
    Returns (title, rows, headers, status)
    """

    sql = '''SELECT n.nspname AS "Name",
    pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"''' + (''',
    pg_catalog.array_to_string(n.nspacl, E'\\n') AS "Access privileges",
    pg_catalog.obj_description(n.oid, 'pg_namespace') AS "Description"''' if verbose else '') + """
    FROM pg_catalog.pg_namespace n WHERE n.nspname """

    params = []
    if pattern:
        _, schema = sql_name_pattern(pattern)
        sql += '~ %s'
        params.append(schema)
    else:
        sql += "!~ '^pg_' AND n.nspname <> 'information_schema'"
    sql = cur.mogrify(sql + " ORDER BY 1", params)

    log.debug(sql)
    cur.execute(sql)
    if cur.description:
        headers = [x[0] for x in cur.description]
        return [(None, cur, headers, cur.statusmessage)]


@special_command('\\dx', '\\dx[+] [pattern]', 'List extensions.')
def list_extensions(cur, pattern, verbose):

    # Note: psql \dx command seems to ignore schema patterns
    _, name_pattern = sql_name_pattern(pattern)

    if verbose:
        extensions = _find_extensions(cur, name_pattern)

        if not extensions:
            msg = 'Did not find any extension named "%s"' % pattern
            return [(None, cur, [], msg)]

        results = []
        for ext_name, oid in extensions:
            title = 'Objects in extension "%s"' % ext_name
            cur, headers, status = _describe_extension(cur, oid)
            results.append((title, cur, headers, status))

        return results

    sql = '''
      SELECT e.extname AS "Name",
             e.extversion AS "Version",
             n.nspname AS "Schema",
             c.description AS "Description"
      FROM pg_catalog.pg_extension e
           LEFT JOIN pg_catalog.pg_namespace n
             ON n.oid = e.extnamespace
           LEFT JOIN pg_catalog.pg_description c
             ON c.objoid = e.oid
                AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
      '''

    if name_pattern:
        sql = cur.mogrify(
            sql + ' WHERE e.extname ~ %s ORDER BY 1', [name_pattern])
    else:
        sql += ' ORDER BY 1'

    log.debug(sql)
    cur.execute(sql)
    headers = [x[0] for x in cur.description]
    return [(None, cur, headers, cur.statusmessage)]


def _find_extensions(cur, pattern):
    sql = 'SELECT e.extname, e.oid FROM pg_catalog.pg_extension e'

    if pattern:
        sql = cur.mogrify(sql + ' WHERE e.extname ~ %s', [pattern])

    sql += ' ORDER BY 1'

    log.debug(sql)
    cur.execute(sql)
    return cur.fetchall()


def _describe_extension(cur, oid):
    sql = '''
        SELECT  pg_catalog.pg_describe_object(classid, objid, 0)
                  AS "Object Description"
        FROM    pg_catalog.pg_depend
        WHERE   refclassid = 'pg_catalog.pg_extension'::pg_catalog.regclass
                AND refobjid = %s
                AND deptype = 'e'
        ORDER BY 1'''
    sql = cur.mogrify(sql, [oid])
    log.debug(sql)
    cur.execute(sql)

    headers = [x[0] for x in cur.description]
    return cur, headers, cur.statusmessage


def list_objects(cur, pattern, verbose, relkinds):
    """
        Returns (title, rows, header, status)

        This method is used by list_tables, list_views, list_materialized views
        and list_indexes

        relkinds is a list of strings to filter pg_class.relkind

    """
    schema_pattern, table_pattern = sql_name_pattern(pattern)

    if verbose:
        verbose_columns = '''
            ,pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
            pg_catalog.obj_description(c.oid, 'pg_class') as "Description" '''
    else:
        verbose_columns = ''

    sql = '''SELECT n.nspname as "Schema",
                    c.relname as "Name",
                    CASE c.relkind
                      WHEN 'r' THEN 'table' WHEN 'v' THEN 'view'
                      WHEN 'p' THEN 'partitioned table'
                      WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index'
                      WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special'
                      WHEN 'f' THEN 'foreign table' END
                    as "Type",
                    pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
          ''' + verbose_columns + '''
            FROM    pg_catalog.pg_class c
                    LEFT JOIN pg_catalog.pg_namespace n
                      ON n.oid = c.relnamespace
            WHERE   c.relkind = ANY(%s) '''

    params = [relkinds]

    if schema_pattern:
        sql += ' AND n.nspname ~ %s'
        params.append(schema_pattern)
    else:
        sql += '''
            AND n.nspname <> 'pg_catalog'
            AND n.nspname <> 'information_schema'
            AND n.nspname !~ '^pg_toast'
            AND pg_catalog.pg_table_is_visible(c.oid) '''

    if table_pattern:
        sql += ' AND c.relname ~ %s'
        params.append(table_pattern)

    sql = cur.mogrify(sql + ' ORDER BY 1, 2', params)

    log.debug(sql)
    cur.execute(sql)

    if cur.description:
        headers = [x[0] for x in cur.description]
        return [(None, cur, headers, cur.statusmessage)]


@special_command('\\dt', '\\dt[+] [pattern]', 'List tables.')
def list_tables(cur, pattern, verbose):
    return list_objects(cur, pattern, verbose, ['r', 'p', ''])


@special_command('\\dv', '\\dv[+] [pattern]', 'List views.')
def list_views(cur, pattern, verbose):
    return list_objects(cur, pattern, verbose, ['v', 's', ''])


@special_command('\\dm', '\\dm[+] [pattern]', 'List materialized views.')
def list_materialized_views(cur, pattern, verbose):
    return list_objects(cur, pattern, verbose, ['m', 's', ''])


@special_command('\\ds', '\\ds[+] [pattern]', 'List sequences.')
def list_sequences(cur, pattern, verbose):
    return list_objects(cur, pattern, verbose, ['S', 's', ''])


@special_command('\\di', '\\di[+] [pattern]', 'List indexes.')
def list_indexes(cur, pattern, verbose):
    return list_objects(cur, pattern, verbose, ['i', 's', ''])


@special_command('\\df', '\\df[+] [pattern]', 'List functions.')
def list_functions(cur, pattern, verbose):

    if verbose:
        verbose_columns = '''
            ,CASE
                 WHEN p.provolatile = 'i' THEN 'immutable'
                 WHEN p.provolatile = 's' THEN 'stable'
                 WHEN p.provolatile = 'v' THEN 'volatile'
            END as "Volatility",
            pg_catalog.pg_get_userbyid(p.proowner) as "Owner",
          l.lanname as "Language",
          p.prosrc as "Source code",
          pg_catalog.obj_description(p.oid, 'pg_proc') as "Description" '''

        verbose_table = ''' LEFT JOIN pg_catalog.pg_language l
                                ON l.oid = p.prolang'''
    else:
        verbose_columns = verbose_table = ''

    if cur.connection.server_version >= 110000:
        sql = '''
            SELECT  n.nspname as "Schema",
                    p.proname as "Name",
                    pg_catalog.pg_get_function_result(p.oid)
                      as "Result data type",
                    pg_catalog.pg_get_function_arguments(p.oid)
                      as "Argument data types",
                     CASE
                        WHEN p.prokind = 'a' THEN 'agg'
                        WHEN p.prokind = 'w' THEN 'window'
                        WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype
                            THEN 'trigger'
                        ELSE 'normal'
                    END as "Type" ''' + verbose_columns + '''
            FROM    pg_catalog.pg_proc p
                    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
                            ''' + verbose_table + '''
            WHERE  '''
    elif cur.connection.server_version > 90000:
        sql = '''
            SELECT  n.nspname as "Schema",
                    p.proname as "Name",
                    pg_catalog.pg_get_function_result(p.oid)
                      as "Result data type",
                    pg_catalog.pg_get_function_arguments(p.oid)
                      as "Argument data types",
                     CASE
                        WHEN p.proisagg THEN 'agg'
                        WHEN p.proiswindow THEN 'window'
                        WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype
                            THEN 'trigger'
                        ELSE 'normal'
                    END as "Type" ''' + verbose_columns + '''
            FROM    pg_catalog.pg_proc p
                    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
                            ''' + verbose_table + '''
            WHERE  '''
    else:
        sql = '''
            SELECT  n.nspname as "Schema",
                    p.proname as "Name",
                    pg_catalog.format_type(p.prorettype, NULL) as "Result data type",
                    pg_catalog.oidvectortypes(p.proargtypes) as "Argument data types",
                     CASE
                        WHEN p.proisagg THEN 'agg'
                        WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
                        ELSE 'normal'
                    END as "Type" ''' + verbose_columns + '''
            FROM    pg_catalog.pg_proc p
                    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
                            ''' + verbose_table + '''
            WHERE  '''


    schema_pattern, func_pattern = sql_name_pattern(pattern)
    params = []

    if schema_pattern:
        sql += ' n.nspname ~ %s '
        params.append(schema_pattern)
    else:
        sql += ' pg_catalog.pg_function_is_visible(p.oid) '

    if func_pattern:
        sql += ' AND p.proname ~ %s '
        params.append(func_pattern)

    if not (schema_pattern or func_pattern):
        sql += ''' AND n.nspname <> 'pg_catalog'
                   AND n.nspname <> 'information_schema' '''

    sql = cur.mogrify(sql + ' ORDER BY 1, 2, 4', params)

    log.debug(sql)
    cur.execute(sql)

    if cur.description:
        headers = [x[0] for x in cur.description]
        return [(None, cur, headers, cur.statusmessage)]


@special_command('\\dT', '\\dT[S+] [pattern]', 'List data types')
def list_datatypes(cur, pattern, verbose):
    sql = '''SELECT n.nspname as "Schema",
                    pg_catalog.format_type(t.oid, NULL) AS "Name", '''

    if verbose:
        sql += r''' t.typname AS "Internal name",
                    CASE
                        WHEN t.typrelid != 0
                            THEN CAST('tuple' AS pg_catalog.text)
                        WHEN t.typlen < 0
                            THEN CAST('var' AS pg_catalog.text)
                        ELSE CAST(t.typlen AS pg_catalog.text)
                    END AS "Size",
                    pg_catalog.array_to_string(
                        ARRAY(
                              SELECT e.enumlabel
                              FROM pg_catalog.pg_enum e
                              WHERE e.enumtypid = t.oid
                              ORDER BY e.enumsortorder
                          ), E'\n') AS "Elements",
                    pg_catalog.array_to_string(t.typacl, E'\n')
                        AS "Access privileges",
                    pg_catalog.obj_description(t.oid, 'pg_type')
                        AS "Description"'''
    else:
        sql += '''  pg_catalog.obj_description(t.oid, 'pg_type')
                        as "Description" '''

    if cur.connection.server_version > 90000:
        sql += '''  FROM    pg_catalog.pg_type t
                            LEFT JOIN pg_catalog.pg_namespace n
                              ON n.oid = t.typnamespace
                    WHERE   (t.typrelid = 0 OR
                              ( SELECT c.relkind = 'c'
                                FROM pg_catalog.pg_class c
                                WHERE c.oid = t.typrelid))
                            AND NOT EXISTS(
                                SELECT 1
                                FROM pg_catalog.pg_type el
                                WHERE el.oid = t.typelem
                                      AND el.typarray = t.oid) '''
    else:
        sql += '''  FROM    pg_catalog.pg_type t
                            LEFT JOIN pg_catalog.pg_namespace n
                              ON n.oid = t.typnamespace
                    WHERE   (t.typrelid = 0 OR
                              ( SELECT c.relkind = 'c'
                                FROM pg_catalog.pg_class c
                                WHERE c.oid = t.typrelid)) '''

    schema_pattern, type_pattern = sql_name_pattern(pattern)
    params = []

    if schema_pattern:
        sql += ' AND n.nspname ~ %s '
        params.append(schema_pattern)
    else:
        sql += ' AND pg_catalog.pg_type_is_visible(t.oid) '

    if type_pattern:
        sql += ''' AND (t.typname ~ %s
                        OR pg_catalog.format_type(t.oid, NULL) ~ %s) '''
        params.extend(2 * [type_pattern])

    if not (schema_pattern or type_pattern):
        sql += ''' AND n.nspname <> 'pg_catalog'
                   AND n.nspname <> 'information_schema' '''

    sql = cur.mogrify(sql + ' ORDER BY 1, 2', params)
    log.debug(sql)
    cur.execute(sql)
    if cur.description:
        headers = [x[0] for x in cur.description]
        return [(None, cur, headers, cur.statusmessage)]


@special_command('\\dD', '\\dD[+] [pattern]', 'List or describe domains.')
def list_domains(cur, pattern, verbose):
    if verbose:
        extra_cols = r''',
               pg_catalog.array_to_string(t.typacl, E'\n') AS "Access privileges",
               d.description as "Description"'''
        extra_joins = '''
           LEFT JOIN pg_catalog.pg_description d ON d.classoid = t.tableoid
                                                AND d.objoid = t.oid AND d.objsubid = 0'''
    else:
        extra_cols = extra_joins = ''

    sql = '''\
        SELECT n.nspname AS "Schema",
               t.typname AS "Name",
               pg_catalog.format_type(t.typbasetype, t.typtypmod) AS "Type",
               pg_catalog.ltrim((COALESCE((SELECT (' collate ' || c.collname)
                                           FROM pg_catalog.pg_collation AS c,
                                                pg_catalog.pg_type AS bt
                                           WHERE c.oid = t.typcollation
                                             AND bt.oid = t.typbasetype
                                             AND t.typcollation <> bt.typcollation) , '')
                                || CASE
                                     WHEN t.typnotnull
                                       THEN ' not null'
                                     ELSE ''
                                   END) || CASE
                                             WHEN t.typdefault IS NOT NULL
                                               THEN(' default ' || t.typdefault)
                                             ELSE ''
                                           END) AS "Modifier",
               pg_catalog.array_to_string(ARRAY(
                 SELECT pg_catalog.pg_get_constraintdef(r.oid, TRUE)
                 FROM pg_catalog.pg_constraint AS r
                 WHERE t.oid = r.contypid), ' ') AS "Check"%s
        FROM pg_catalog.pg_type AS t
           LEFT JOIN pg_catalog.pg_namespace AS n ON n.oid = t.typnamespace%s
        WHERE t.typtype = 'd' ''' % (extra_cols, extra_joins)

    schema_pattern, name_pattern = sql_name_pattern(pattern)
    params = []
    if schema_pattern or name_pattern:
        if schema_pattern:
            sql += ' AND n.nspname ~ %s'
            params.append(schema_pattern)
        if name_pattern:
            sql += ' AND t.typname ~ %s'
            params.append(name_pattern)
    else:
        sql += '''
          AND (n.nspname <> 'pg_catalog')
          AND (n.nspname <> 'information_schema')
          AND pg_catalog.pg_type_is_visible(t.oid)'''

    sql = cur.mogrify(sql + ' ORDER BY 1, 2', params)
    log.debug(sql)
    cur.execute(sql)
    if cur.description:
        headers = [x[0] for x in cur.description]
        return [(None, cur, headers, cur.statusmessage)]

@special_command('\\dF', '\\dF[+] [pattern]', 'List text search configurations.')
def list_text_search_configurations(cur, pattern, verbose):

    def _find_text_search_configs(cur, pattern):
        sql = '''
            SELECT c.oid,
                 c.cfgname,
                 n.nspname,
                 p.prsname,
                 np.nspname AS pnspname
            FROM pg_catalog.pg_ts_config c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.cfgnamespace,
                                                 pg_catalog.pg_ts_parser p
            LEFT JOIN pg_catalog.pg_namespace np ON np.oid = p.prsnamespace
            WHERE p.oid = c.cfgparser
        '''

        if pattern:
            sql = cur.mogrify(sql + ' WHERE e.extname ~ {}'.format(pattern))

        sql += ' ORDER BY 1'

        log.debug(sql)
        cur.execute(sql)
        return cur.fetchall()

    def _fetch_oid_details(cur, oid):
        sql = '''
            SELECT
              (SELECT t.alias
               FROM pg_catalog.ts_token_type(c.cfgparser) AS t
               WHERE t.tokid = m.maptokentype ) AS "Token",
                   pg_catalog.btrim( ARRAY
                                      (SELECT mm.mapdict::pg_catalog.regdictionary
                                       FROM pg_catalog.pg_ts_config_map AS mm
                                       WHERE mm.mapcfg = m.mapcfg
                                         AND mm.maptokentype = m.maptokentype
                                       ORDER BY mapcfg, maptokentype, mapseqno ) :: pg_catalog.text, '{}') AS "Dictionaries"
            FROM pg_catalog.pg_ts_config AS c,
                 pg_catalog.pg_ts_config_map AS m
            WHERE c.oid = %s
              AND m.mapcfg = c.oid
            GROUP BY m.mapcfg,
                     m.maptokentype,
                     c.cfgparser
            ORDER BY 1;
        '''

        sql = cur.mogrify(sql, [oid])
        log.debug(sql)
        cur.execute(sql)

        headers = [x[0] for x in cur.description]
        return cur, headers, cur.statusmessage

    if cur.connection.server_version < 80300:
        not_supported = "Server versions below 8.3 do not support full text search."
        cur, headers = [], []
        yield None, cur, None, not_supported<commit_msg>bugfix & result expansion<commit_after>from __future__ import unicode_literals
import logging
import shlex
import subprocess
from collections import namedtuple

from .main import special_command, RAW_QUERY

TableInfo = namedtuple("TableInfo", [
    'checks', 'relkind', 'hasindex', 'hasrules', 'hastriggers', 'hasoids',
    'tablespace', 'reloptions', 'reloftype', 'relpersistence', 'relispartition'])

log = logging.getLogger(__name__)


@special_command('\\l', '\\l[+] [pattern]', 'List databases.', aliases=('\\list',))
def list_databases(cur, pattern, verbose):
    query = '''SELECT d.datname as "Name",
        pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
        pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
        d.datcollate as "Collate",
        d.datctype as "Ctype",
        pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"'''
    if verbose:
        query += ''',
            CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
                    THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
                    ELSE 'No Access'
            END as "Size",
            t.spcname as "Tablespace",
            pg_catalog.shobj_description(d.oid, 'pg_database') as "Description"'''
    query += '''
    FROM pg_catalog.pg_database d
    '''
    if verbose:
        query += '''
        JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid
        '''
    params = []
    if pattern:
        query += '''
        WHERE d.datname ~ %s
        '''
        _, schema = sql_name_pattern(pattern)
        params.append(schema)
    query = cur.mogrify(query + ' ORDER BY 1', params)
    cur.execute(query)
    if cur.description:
        headers = [x[0] for x in cur.description]
        return [(None, cur, headers, cur.statusmessage)]
    else:
        return [(None, None, None, cur.statusmessage)]


@special_command('\\du', '\\du[+] [pattern]', 'List roles.')
def list_roles(cur, pattern, verbose):
    """
    Returns (title, rows, headers, status)
    """

    if cur.connection.server_version > 90000:
        sql = '''
            SELECT r.rolname,
                r.rolsuper,
                r.rolinherit,
                r.rolcreaterole,
                r.rolcreatedb,
                r.rolcanlogin,
                r.rolconnlimit,
                r.rolvaliduntil,
                ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid) as memberof,
            '''
        if verbose:
            sql += '''
                pg_catalog.shobj_description(r.oid, 'pg_authid') AS description,
            '''
        sql += '''
            r.rolreplication
        FROM pg_catalog.pg_roles r
        '''
    else:
        sql = '''
            SELECT u.usename AS rolname,
                u.usesuper AS rolsuper,
                true AS rolinherit,
                false AS rolcreaterole,
                u.usecreatedb AS rolcreatedb,
                true AS rolcanlogin,
                -1 AS rolconnlimit,
                u.valuntil as rolvaliduntil,
                ARRAY(SELECT g.groname FROM pg_catalog.pg_group g WHERE u.usesysid = ANY(g.grolist)) as memberof
            FROM pg_catalog.pg_user u
            '''

    params = []
    if pattern:
        _, schema = sql_name_pattern(pattern)
        sql += 'WHERE r.rolname ~ %s'
        params.append(schema)
    sql = cur.mogrify(sql + " ORDER BY 1", params)

    log.debug(sql)
    cur.execute(sql)
    if cur.description:
        headers = [x[0] for x in cur.description]
        return [(None, cur, headers, cur.statusmessage)]


@special_command('\\db', '\\db[+] [pattern]', 'List tablespaces.')
def list_tablespaces(cur, pattern, **_):
    """
    Returns (title, rows, headers, status)
    """

    cur.execute("SELECT EXISTS(SELECT * FROM pg_proc WHERE proname = 'pg_tablespace_location')")
    (is_location,) = cur.fetchone()

    sql = '''SELECT n.spcname AS "Name",
    pg_catalog.pg_get_userbyid(n.spcowner) AS "Owner",'''

    sql += " pg_catalog.pg_tablespace_location(n.oid)" if is_location else " 'Not supported'"
    sql += ''' AS "Location"
    FROM pg_catalog.pg_tablespace n'''

    params = []
    if pattern:
        _, tbsp = sql_name_pattern(pattern)
        sql += " WHERE n.spcname ~ %s"
        params.append(tbsp)

    sql = cur.mogrify(sql + " ORDER BY 1", params)
    log.debug(sql)
    cur.execute(sql)

    headers = [x[0] for x in cur.description] if cur.description else None
    return [(None, cur, headers, cur.statusmessage)]


@special_command('\\dn', '\\dn[+] [pattern]', 'List schemas.')
def list_schemas(cur, pattern, verbose):
    """
    Returns (title, rows, headers, status)
    """

    sql = '''SELECT n.nspname AS "Name",
    pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"''' + (''',
    pg_catalog.array_to_string(n.nspacl, E'\\n') AS "Access privileges",
    pg_catalog.obj_description(n.oid, 'pg_namespace') AS "Description"''' if verbose else '') + """
    FROM pg_catalog.pg_namespace n WHERE n.nspname """

    params = []
    if pattern:
        _, schema = sql_name_pattern(pattern)
        sql += '~ %s'
        params.append(schema)
    else:
        sql += "!~ '^pg_' AND n.nspname <> 'information_schema'"
    sql = cur.mogrify(sql + " ORDER BY 1", params)

    log.debug(sql)
    cur.execute(sql)
    if cur.description:
        headers = [x[0] for x in cur.description]
        return [(None, cur, headers, cur.statusmessage)]


@special_command('\\dx', '\\dx[+] [pattern]', 'List extensions.')
def list_extensions(cur, pattern, verbose):

    # Note: psql \dx command seems to ignore schema patterns
    _, name_pattern = sql_name_pattern(pattern)

    if verbose:
        extensions = _find_extensions(cur, name_pattern)

        if not extensions:
            msg = 'Did not find any extension named "%s"' % pattern
            return [(None, cur, [], msg)]

        results = []
        for ext_name, oid in extensions:
            title = 'Objects in extension "%s"' % ext_name
            cur, headers, status = _describe_extension(cur, oid)
            results.append((title, cur, headers, status))

        return results

    sql = '''
      SELECT e.extname AS "Name",
             e.extversion AS "Version",
             n.nspname AS "Schema",
             c.description AS "Description"
      FROM pg_catalog.pg_extension e
           LEFT JOIN pg_catalog.pg_namespace n
             ON n.oid = e.extnamespace
           LEFT JOIN pg_catalog.pg_description c
             ON c.objoid = e.oid
                AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
      '''

    if name_pattern:
        sql = cur.mogrify(
            sql + ' WHERE e.extname ~ %s ORDER BY 1', [name_pattern])
    else:
        sql += ' ORDER BY 1'

    log.debug(sql)
    cur.execute(sql)
    headers = [x[0] for x in cur.description]
    return [(None, cur, headers, cur.statusmessage)]


def _find_extensions(cur, pattern):
    sql = 'SELECT e.extname, e.oid FROM pg_catalog.pg_extension e'

    if pattern:
        sql = cur.mogrify(sql + ' WHERE e.extname ~ %s', [pattern])

    sql += ' ORDER BY 1'

    log.debug(sql)
    cur.execute(sql)
    return cur.fetchall()


def _describe_extension(cur, oid):
    sql = '''
        SELECT  pg_catalog.pg_describe_object(classid, objid, 0)
                  AS "Object Description"
        FROM    pg_catalog.pg_depend
        WHERE   refclassid = 'pg_catalog.pg_extension'::pg_catalog.regclass
                AND refobjid = %s
                AND deptype = 'e'
        ORDER BY 1'''
    sql = cur.mogrify(sql, [oid])
    log.debug(sql)
    cur.execute(sql)

    headers = [x[0] for x in cur.description]
    return cur, headers, cur.statusmessage


def list_objects(cur, pattern, verbose, relkinds):
    """
        Returns (title, rows, header, status)

        This method is used by list_tables, list_views, list_materialized views
        and list_indexes

        relkinds is a list of strings to filter pg_class.relkind

    """
    schema_pattern, table_pattern = sql_name_pattern(pattern)

    if verbose:
        verbose_columns = '''
            ,pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
            pg_catalog.obj_description(c.oid, 'pg_class') as "Description" '''
    else:
        verbose_columns = ''

    sql = '''SELECT n.nspname as "Schema",
                    c.relname as "Name",
                    CASE c.relkind
                      WHEN 'r' THEN 'table' WHEN 'v' THEN 'view'
                      WHEN 'p' THEN 'partitioned table'
                      WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index'
                      WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special'
                      WHEN 'f' THEN 'foreign table' END
                    as "Type",
                    pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
          ''' + verbose_columns + '''
            FROM    pg_catalog.pg_class c
                    LEFT JOIN pg_catalog.pg_namespace n
                      ON n.oid = c.relnamespace
            WHERE   c.relkind = ANY(%s) '''

    params = [relkinds]

    if schema_pattern:
        sql += ' AND n.nspname ~ %s'
        params.append(schema_pattern)
    else:
        sql += '''
            AND n.nspname <> 'pg_catalog'
            AND n.nspname <> 'information_schema'
            AND n.nspname !~ '^pg_toast'
            AND pg_catalog.pg_table_is_visible(c.oid) '''

    if table_pattern:
        sql += ' AND c.relname ~ %s'
        params.append(table_pattern)

    sql = cur.mogrify(sql + ' ORDER BY 1, 2', params)

    log.debug(sql)
    cur.execute(sql)

    if cur.description:
        headers = [x[0] for x in cur.description]
        return [(None, cur, headers, cur.statusmessage)]


@special_command('\\dt', '\\dt[+] [pattern]', 'List tables.')
def list_tables(cur, pattern, verbose):
    return list_objects(cur, pattern, verbose, ['r', 'p', ''])


@special_command('\\dv', '\\dv[+] [pattern]', 'List views.')
def list_views(cur, pattern, verbose):
    return list_objects(cur, pattern, verbose, ['v', 's', ''])


@special_command('\\dm', '\\dm[+] [pattern]', 'List materialized views.')
def list_materialized_views(cur, pattern, verbose):
    return list_objects(cur, pattern, verbose, ['m', 's', ''])


@special_command('\\ds', '\\ds[+] [pattern]', 'List sequences.')
def list_sequences(cur, pattern, verbose):
    return list_objects(cur, pattern, verbose, ['S', 's', ''])


@special_command('\\di', '\\di[+] [pattern]', 'List indexes.')
def list_indexes(cur, pattern, verbose):
    return list_objects(cur, pattern, verbose, ['i', 's', ''])


@special_command('\\df', '\\df[+] [pattern]', 'List functions.')
def list_functions(cur, pattern, verbose):

    if verbose:
        verbose_columns = '''
            ,CASE
                 WHEN p.provolatile = 'i' THEN 'immutable'
                 WHEN p.provolatile = 's' THEN 'stable'
                 WHEN p.provolatile = 'v' THEN 'volatile'
            END as "Volatility",
            pg_catalog.pg_get_userbyid(p.proowner) as "Owner",
          l.lanname as "Language",
          p.prosrc as "Source code",
          pg_catalog.obj_description(p.oid, 'pg_proc') as "Description" '''

        verbose_table = ''' LEFT JOIN pg_catalog.pg_language l
                                ON l.oid = p.prolang'''
    else:
        verbose_columns = verbose_table = ''

    if cur.connection.server_version >= 110000:
        sql = '''
            SELECT  n.nspname as "Schema",
                    p.proname as "Name",
                    pg_catalog.pg_get_function_result(p.oid)
                      as "Result data type",
                    pg_catalog.pg_get_function_arguments(p.oid)
                      as "Argument data types",
                     CASE
                        WHEN p.prokind = 'a' THEN 'agg'
                        WHEN p.prokind = 'w' THEN 'window'
                        WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype
                            THEN 'trigger'
                        ELSE 'normal'
                    END as "Type" ''' + verbose_columns + '''
            FROM    pg_catalog.pg_proc p
                    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
                            ''' + verbose_table + '''
            WHERE  '''
    elif cur.connection.server_version > 90000:
        sql = '''
            SELECT  n.nspname as "Schema",
                    p.proname as "Name",
                    pg_catalog.pg_get_function_result(p.oid)
                      as "Result data type",
                    pg_catalog.pg_get_function_arguments(p.oid)
                      as "Argument data types",
                     CASE
                        WHEN p.proisagg THEN 'agg'
                        WHEN p.proiswindow THEN 'window'
                        WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype
                            THEN 'trigger'
                        ELSE 'normal'
                    END as "Type" ''' + verbose_columns + '''
            FROM    pg_catalog.pg_proc p
                    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
                            ''' + verbose_table + '''
            WHERE  '''
    else:
        sql = '''
            SELECT  n.nspname as "Schema",
                    p.proname as "Name",
                    pg_catalog.format_type(p.prorettype, NULL) as "Result data type",
                    pg_catalog.oidvectortypes(p.proargtypes) as "Argument data types",
                     CASE
                        WHEN p.proisagg THEN 'agg'
                        WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
                        ELSE 'normal'
                    END as "Type" ''' + verbose_columns + '''
            FROM    pg_catalog.pg_proc p
                    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
                            ''' + verbose_table + '''
            WHERE  '''


    schema_pattern, func_pattern = sql_name_pattern(pattern)
    params = []

    if schema_pattern:
        sql += ' n.nspname ~ %s '
        params.append(schema_pattern)
    else:
        sql += ' pg_catalog.pg_function_is_visible(p.oid) '

    if func_pattern:
        sql += ' AND p.proname ~ %s '
        params.append(func_pattern)

    if not (schema_pattern or func_pattern):
        sql += ''' AND n.nspname <> 'pg_catalog'
                   AND n.nspname <> 'information_schema' '''

    sql = cur.mogrify(sql + ' ORDER BY 1, 2, 4', params)

    log.debug(sql)
    cur.execute(sql)

    if cur.description:
        headers = [x[0] for x in cur.description]
        return [(None, cur, headers, cur.statusmessage)]


@special_command('\\dT', '\\dT[S+] [pattern]', 'List data types')
def list_datatypes(cur, pattern, verbose):
    sql = '''SELECT n.nspname as "Schema",
                    pg_catalog.format_type(t.oid, NULL) AS "Name", '''

    if verbose:
        sql += r''' t.typname AS "Internal name",
                    CASE
                        WHEN t.typrelid != 0
                            THEN CAST('tuple' AS pg_catalog.text)
                        WHEN t.typlen < 0
                            THEN CAST('var' AS pg_catalog.text)
                        ELSE CAST(t.typlen AS pg_catalog.text)
                    END AS "Size",
                    pg_catalog.array_to_string(
                        ARRAY(
                              SELECT e.enumlabel
                              FROM pg_catalog.pg_enum e
                              WHERE e.enumtypid = t.oid
                              ORDER BY e.enumsortorder
                          ), E'\n') AS "Elements",
                    pg_catalog.array_to_string(t.typacl, E'\n')
                        AS "Access privileges",
                    pg_catalog.obj_description(t.oid, 'pg_type')
                        AS "Description"'''
    else:
        sql += '''  pg_catalog.obj_description(t.oid, 'pg_type')
                        as "Description" '''

    if cur.connection.server_version > 90000:
        sql += '''  FROM    pg_catalog.pg_type t
                            LEFT JOIN pg_catalog.pg_namespace n
                              ON n.oid = t.typnamespace
                    WHERE   (t.typrelid = 0 OR
                              ( SELECT c.relkind = 'c'
                                FROM pg_catalog.pg_class c
                                WHERE c.oid = t.typrelid))
                            AND NOT EXISTS(
                                SELECT 1
                                FROM pg_catalog.pg_type el
                                WHERE el.oid = t.typelem
                                      AND el.typarray = t.oid) '''
    else:
        sql += '''  FROM    pg_catalog.pg_type t
                            LEFT JOIN pg_catalog.pg_namespace n
                              ON n.oid = t.typnamespace
                    WHERE   (t.typrelid = 0 OR
                              ( SELECT c.relkind = 'c'
                                FROM pg_catalog.pg_class c
                                WHERE c.oid = t.typrelid)) '''

    schema_pattern, type_pattern = sql_name_pattern(pattern)
    params = []

    if schema_pattern:
        sql += ' AND n.nspname ~ %s '
        params.append(schema_pattern)
    else:
        sql += ' AND pg_catalog.pg_type_is_visible(t.oid) '

    if type_pattern:
        sql += ''' AND (t.typname ~ %s
                        OR pg_catalog.format_type(t.oid, NULL) ~ %s) '''
        params.extend(2 * [type_pattern])

    if not (schema_pattern or type_pattern):
        sql += ''' AND n.nspname <> 'pg_catalog'
                   AND n.nspname <> 'information_schema' '''

    sql = cur.mogrify(sql + ' ORDER BY 1, 2', params)
    log.debug(sql)
    cur.execute(sql)
    if cur.description:
        headers = [x[0] for x in cur.description]
        return [(None, cur, headers, cur.statusmessage)]


@special_command('\\dD', '\\dD[+] [pattern]', 'List or describe domains.')
def list_domains(cur, pattern, verbose):
    if verbose:
        extra_cols = r''',
               pg_catalog.array_to_string(t.typacl, E'\n') AS "Access privileges",
               d.description as "Description"'''
        extra_joins = '''
           LEFT JOIN pg_catalog.pg_description d ON d.classoid = t.tableoid
                                                AND d.objoid = t.oid AND d.objsubid = 0'''
    else:
        extra_cols = extra_joins = ''

    sql = '''\
        SELECT n.nspname AS "Schema",
               t.typname AS "Name",
               pg_catalog.format_type(t.typbasetype, t.typtypmod) AS "Type",
               pg_catalog.ltrim((COALESCE((SELECT (' collate ' || c.collname)
                                           FROM pg_catalog.pg_collation AS c,
                                                pg_catalog.pg_type AS bt
                                           WHERE c.oid = t.typcollation
                                             AND bt.oid = t.typbasetype
                                             AND t.typcollation <> bt.typcollation) , '')
                                || CASE
                                     WHEN t.typnotnull
                                       THEN ' not null'
                                     ELSE ''
                                   END) || CASE
                                             WHEN t.typdefault IS NOT NULL
                                               THEN(' default ' || t.typdefault)
                                             ELSE ''
                                           END) AS "Modifier",
               pg_catalog.array_to_string(ARRAY(
                 SELECT pg_catalog.pg_get_constraintdef(r.oid, TRUE)
                 FROM pg_catalog.pg_constraint AS r
                 WHERE t.oid = r.contypid), ' ') AS "Check"%s
        FROM pg_catalog.pg_type AS t
           LEFT JOIN pg_catalog.pg_namespace AS n ON n.oid = t.typnamespace%s
        WHERE t.typtype = 'd' ''' % (extra_cols, extra_joins)

    schema_pattern, name_pattern = sql_name_pattern(pattern)
    params = []
    if schema_pattern or name_pattern:
        if schema_pattern:
            sql += ' AND n.nspname ~ %s'
            params.append(schema_pattern)
        if name_pattern:
            sql += ' AND t.typname ~ %s'
            params.append(name_pattern)
    else:
        sql += '''
          AND (n.nspname <> 'pg_catalog')
          AND (n.nspname <> 'information_schema')
          AND pg_catalog.pg_type_is_visible(t.oid)'''

    sql = cur.mogrify(sql + ' ORDER BY 1, 2', params)
    log.debug(sql)
    cur.execute(sql)
    if cur.description:
        headers = [x[0] for x in cur.description]
        return [(None, cur, headers, cur.statusmessage)]

@special_command('\\dF', '\\dF[+] [pattern]', 'List text search configurations.')
def list_text_search_configurations(cur, pattern, verbose):

    def _find_text_search_configs(cur, pattern):
        sql = '''
            SELECT c.oid,
                 c.cfgname,
                 n.nspname,
                 p.prsname,
                 np.nspname AS pnspname
            FROM pg_catalog.pg_ts_config c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.cfgnamespace,
                                                 pg_catalog.pg_ts_parser p
            LEFT JOIN pg_catalog.pg_namespace np ON np.oid = p.prsnamespace
            WHERE p.oid = c.cfgparser
        '''

        if pattern:
            sql = cur.mogrify(sql + ' WHERE e.extname ~ {}'.format(pattern))

        sql += ' ORDER BY 1'

        log.debug(sql)
        cur.execute(sql)
        return cur.fetchall()

    def _fetch_oid_details(cur, oid):
        sql = '''
            SELECT
              (SELECT t.alias
               FROM pg_catalog.ts_token_type(c.cfgparser) AS t
               WHERE t.tokid = m.maptokentype ) AS "Token",
                   pg_catalog.btrim( ARRAY
                                      (SELECT mm.mapdict::pg_catalog.regdictionary
                                       FROM pg_catalog.pg_ts_config_map AS mm
                                       WHERE mm.mapcfg = m.mapcfg
                                         AND mm.maptokentype = m.maptokentype
                                       ORDER BY mapcfg, maptokentype, mapseqno ) :: pg_catalog.text, '{}') AS "Dictionaries"
            FROM pg_catalog.pg_ts_config AS c,
                 pg_catalog.pg_ts_config_map AS m
            WHERE c.oid = %s
              AND m.mapcfg = c.oid
            GROUP BY m.mapcfg,
                     m.maptokentype,
                     c.cfgparser
            ORDER BY 1;
        '''

        sql = cur.mogrify(sql, [oid])
        log.debug(sql)
        cur.execute(sql)

        headers = [x[0] for x in cur.description]
        return cur, headers, cur.statusmessage

    if cur.connection.server_version < 80300:
        not_supported = "Server versions below 8.3 do not support full text search."
        cur, headers = [], []
        yield None, cur, None, not_supported
        return
{'fraction_non_alphanumeric': 0.11203713699665335, 'fraction_numerical': 0.002893231134621613, 'mean_word_length': 1.6225015571032217, 'pattern_counts': {'":': 0, '<': 27, '<?xml version=': 0, '>': 31, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 84, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6924362', 'n_tokens_mistral': 14164, 'n_tokens_neox': 13295, 'n_words': 3300}