def _fixed_lookup_fixture(self):
return [
(sqltypes.String(), sqltypes.VARCHAR()),
(sqltypes.String(1), sqltypes.VARCHAR(1)),
(sqltypes.String(3), sqltypes.VARCHAR(3)),
(sqltypes.Text(), sqltypes.TEXT()),
(sqltypes.Unicode(), sqltypes.VARCHAR()),
(sqltypes.Unicode(1), sqltypes.VARCHAR(1)),
(sqltypes.UnicodeText(), sqltypes.TEXT()),
(sqltypes.CHAR(3), sqltypes.CHAR(3)),
(sqltypes.NUMERIC, sqltypes.NUMERIC()),
(sqltypes.NUMERIC(10, 2), sqltypes.NUMERIC(10, 2)),
(sqltypes.Numeric, sqltypes.NUMERIC()),
(sqltypes.Numeric(10, 2), sqltypes.NUMERIC(10, 2)),
(sqltypes.DECIMAL, sqltypes.DECIMAL()),
(sqltypes.DECIMAL(10, 2), sqltypes.DECIMAL(10, 2)),
(sqltypes.INTEGER, sqltypes.INTEGER()),
(sqltypes.BIGINT, sqltypes.BIGINT()),
(sqltypes.Float, sqltypes.FLOAT()),
(sqltypes.TIMESTAMP, sqltypes.TIMESTAMP()),
(sqltypes.DATETIME, sqltypes.DATETIME()),
(sqltypes.DateTime, sqltypes.DATETIME()),
(sqltypes.DateTime(), sqltypes.DATETIME()),
(sqltypes.DATE, sqltypes.DATE()),
(sqltypes.Date, sqltypes.DATE()),
(sqltypes.TIME, sqltypes.TIME()),
(sqltypes.Time, sqltypes.TIME()),
(sqltypes.BOOLEAN, sqltypes.BOOLEAN()),
(sqltypes.Boolean, sqltypes.BOOLEAN()),
]
def _fixed_lookup_fixture(self):
return [
(sqltypes.String(), sqltypes.VARCHAR()),
(sqltypes.String(1), sqltypes.VARCHAR(1)),
(sqltypes.String(3), sqltypes.VARCHAR(3)),
(sqltypes.Text(), sqltypes.TEXT()),
(sqltypes.Unicode(), sqltypes.VARCHAR()),
(sqltypes.Unicode(1), sqltypes.VARCHAR(1)),
(sqltypes.UnicodeText(), sqltypes.TEXT()),
(sqltypes.CHAR(3), sqltypes.CHAR(3)),
(sqltypes.NUMERIC, sqltypes.NUMERIC()),
(sqltypes.NUMERIC(10, 2), sqltypes.NUMERIC(10, 2)),
(sqltypes.Numeric, sqltypes.NUMERIC()),
(sqltypes.Numeric(10, 2), sqltypes.NUMERIC(10, 2)),
(sqltypes.DECIMAL, sqltypes.DECIMAL()),
(sqltypes.DECIMAL(10, 2), sqltypes.DECIMAL(10, 2)),
(sqltypes.INTEGER, sqltypes.INTEGER()),
(sqltypes.BIGINT, sqltypes.BIGINT()),
(sqltypes.Float, sqltypes.FLOAT()),
(sqltypes.TIMESTAMP, sqltypes.TIMESTAMP()),
(sqltypes.DATETIME, sqltypes.DATETIME()),
(sqltypes.DateTime, sqltypes.DATETIME()),
(sqltypes.DateTime(), sqltypes.DATETIME()),
(sqltypes.DATE, sqltypes.DATE()),
(sqltypes.Date, sqltypes.DATE()),
(sqltypes.TIME, sqltypes.TIME()),
(sqltypes.Time, sqltypes.TIME()),
(sqltypes.BOOLEAN, sqltypes.BOOLEAN()),
(sqltypes.Boolean, sqltypes.BOOLEAN()),
(sqlite.DATE(storage_format="%(year)04d%(month)02d%(day)02d", ),
sqltypes.DATE()),
(sqlite.TIME(
storage_format="%(hour)02d%(minute)02d%(second)02d", ),
sqltypes.TIME()),
(sqlite.DATETIME(storage_format="%(year)04d%(month)02d%(day)02d"
"%(hour)02d%(minute)02d%(second)02d", ),
sqltypes.DATETIME()),
]
},
{
'name': 'date',
'type': types.DATE(),
'nullable': True,
'default': None
},
{
'name': 'datetime',
'type': types.DATETIME(),
'nullable': True,
'default': None
},
{
'name': 'time',
'type': types.TIME(),
'nullable': True,
'default': None
},
{
'name': 'bytes',
'type': types.BINARY(),
'nullable': True,
'default': None
},
{
'name': 'record.name',
'type': types.String(),
'nullable': True,
'default': None
},
class VerticaDialect(PyODBCConnector, PGDialect):
""" Vertica Dialect using a pyodbc connection and PGDialect """
ischema_names = {
'BINARY': sqltypes.BLOB,
'VARBINARY': sqltypes.BLOB,
'BYTEA': sqltypes.BLOB,
'RAW': sqltypes.BLOB,
'BOOLEAN': sqltypes.BOOLEAN,
'CHAR': sqltypes.CHAR,
'VARCHAR': sqltypes.VARCHAR,
'VARCHAR2': sqltypes.VARCHAR,
'DATE': sqltypes.DATE,
'DATETIME': sqltypes.DATETIME,
'SMALLDATETIME': sqltypes.DATETIME,
'TIME': sqltypes.TIME,
'TIME': sqltypes.TIME(timezone=True),
'TIMESTAMP': sqltypes.TIMESTAMP,
'TIMESTAMP WITH TIMEZONE': sqltypes.TIMESTAMP(timezone=True),
# Not supported yet
# INTERVAL
# All the same internal representation
'FLOAT': sqltypes.FLOAT,
'FLOAT8': sqltypes.FLOAT,
'DOUBLE': sqltypes.FLOAT,
'REAL': sqltypes.FLOAT,
'INT': sqltypes.INTEGER,
'INTEGER': sqltypes.INTEGER,
'INT8': sqltypes.INTEGER,
'BIGINT': sqltypes.INTEGER,
'SMALLINT': sqltypes.INTEGER,
'TINYINT': sqltypes.INTEGER,
'NUMERIC': sqltypes.NUMERIC,
'DECIMAL': sqltypes.NUMERIC,
'NUMBER': sqltypes.NUMERIC,
'MONEY': sqltypes.NUMERIC,
}
name = 'vertica'
pyodbc_driver_name = 'Vertica'
def has_schema(self, connection, schema):
query = ("SELECT EXISTS (SELECT schema_name FROM v_catalog.schemata "
"WHERE schema_name='%s')") % (schema)
rs = connection.execute(query)
return bool(rs.scalar())
def has_table(self, connection, table_name, schema=None):
if schema is None:
schema = self._get_default_schema_name(connection)
query = ("SELECT EXISTS ("
"SELECT table_name FROM v_catalog.all_tables "
"WHERE schema_name='%s' AND "
"table_name='%s'"
")") % (schema, table_name)
rs = connection.execute(query)
return bool(rs.scalar())
def has_sequence(self, connection, sequence_name, schema=None):
if schema is None:
schema = self._get_default_schema_name(connection)
query = ("SELECT EXISTS ("
"SELECT sequence_name FROM v_catalog.sequences "
"WHERE sequence_schema='%s' AND "
"sequence_name='%s'"
")") % (schema, sequence_name)
rs = connection.execute(query)
return bool(rs.scalar())
def has_type(self, connection, type_name, schema=None):
query = ("SELECT EXISTS ("
"SELECT type_name FROM v_catalog.types "
"WHERE type_name='%s'"
")") % (type_name)
rs = connection.execute(query)
return bool(rs.scalar())
def _get_server_version_info(self, connection):
v = connection.scalar("select version()")
m = re.match('.*Vertica Analytic Database '
'v(\d+)\.(\d+)\.(\d)+.*', v)
if not m:
raise AssertionError(
"Could not determine version from string '%s'" % v)
return tuple([int(x) for x in m.group(1, 2, 3) if x is not None])
def _get_default_schema_name(self, connection):
return connection.scalar("select current_schema()")
@reflection.cache
def get_schema_names(self, connection, **kw):
query = "SELECT schema_name FROM v_catalog.schemata"
rs = connection.execute(query)
return [row[0] for row in rs if not row[0].startswith('v_')]
@reflection.cache
def get_table_names(self, connection, schema=None, **kw):
s = ["SELECT table_name FROM v_catalog.tables"]
if schema is not None:
s.append("WHERE table_schema = '%s'" % (schema, ))
s.append("ORDER BY table_schema, table_name")
rs = connection.execute(' '.join(s))
return [row[0] for row in rs]
@reflection.cache
def get_view_names(self, connection, schema=None, **kw):
s = ["SELECT table_name FROM v_catalog.views"]
if schema is not None:
s.append("WHERE table_schema = '%s'" % (schema, ))
s.append("ORDER BY table_schema, table_name")
rs = connection.execute(' '.join(s))
return [row[0] for row in rs]
@reflection.cache
def get_columns(self, connection, table_name, schema=None, **kw):
# print('in get columns %s' % table_name)
s = ("SELECT * FROM v_catalog.columns "
"WHERE table_name = '%s' ") % (table_name, )
spk = ("SELECT column_name FROM v_catalog.primary_keys "
"WHERE table_name = '%s' "
"AND constraint_type = 'p'") % (table_name)
if schema is not None:
_pred = lambda p: ("%s AND table_schema = '%s'" % (p, schema))
s = _pred(s)
spk = _pred(spk)
pk_columns = [x[0] for x in connection.execute(spk)]
columns = []
for row in connection.execute(s):
name = row.column_name
dtype = row.data_type.upper()
if '(' in dtype:
dtype = dtype.split('(')[0]
coltype = self.ischema_names[dtype]
primary_key = name in pk_columns
default = row.column_default
nullable = row.is_nullable
columns.append({
'name': name,
'type': coltype,
'nullable': nullable,
'default': default,
'primary_key': primary_key
})
return columns
@reflection.cache
def get_unique_constraints(self,
connection,
table_name,
schema=None,
**kw):
query = "SELECT constraint_id, constraint_name, column_name \
FROM v_catalog.constraint_columns WHERE CONSTRAINT_TYPE IN ('p', 'u')\
AND table_name = '{}'".format(table_name)
if schema is not None:
query += " AND table_name = '{}'".format(schema)
rs = connection.execute(query)
uniques = defaultdict(lambda: defaultdict(list))
for row in rs:
uc = uniques[row.constraint_id]
uc["name"] = row.constraint_name
uc["cols"] += [row.column_name]
return [{
'name': uc['name'],
'column_names': uc["cols"]
} for uc in uniques.itervalues()]
# constraints are enforced on selects, but returning nothing for these
# methods allows table introspection to work
def get_pk_constraint(self, bind, table_name, schema, **kw):
return {'constrained_columns': [], 'name': 'undefined'}
def get_foreign_keys(self, connection, table_name, schema, **kw):
return []
def get_indexes(self, connection, table_name, schema, **kw):
return []
# Disable index creation since that's not a thing in Vertica.
def visit_create_index(self, create):
return None
),
(
"DATE",
types.DATE(),
),
(
"DATETIME",
types.DATETIME(),
),
(
"JSON",
types.JSON(),
),
(
"TIME",
types.TIME(timezone=False),
),
(
"TIMESTAMP_NTZ",
types.TIMESTAMP(timezone=False),
),
(
"TIMESTAMP_TZ",
types.TIMESTAMP(timezone=True),
),
(
"VARCHAR",
types.VARCHAR(),
),
)
class VerticaDialect(PGDialect):
""" Vertica Dialect using a vertica-python connection and PGDialect """
name = 'vertica'
driver = 'vertica_python'
ischema_names = {
'BINARY': sqltypes.BLOB,
'VARBINARY': sqltypes.BLOB,
'BYTEA': sqltypes.BLOB,
'RAW': sqltypes.BLOB,
'BOOLEAN': sqltypes.BOOLEAN,
'CHAR': sqltypes.CHAR,
'VARCHAR': sqltypes.VARCHAR,
'VARCHAR2': sqltypes.VARCHAR,
'DATE': sqltypes.DATE,
'DATETIME': sqltypes.DATETIME,
'SMALLDATETIME': sqltypes.DATETIME,
'TIME': sqltypes.TIME,
'TIME': sqltypes.TIME(timezone=True),
'TIMESTAMP': sqltypes.TIMESTAMP,
'TIMESTAMP WITH TIMEZONE': sqltypes.TIMESTAMP(timezone=True),
# Not supported yet
# INTERVAL
# All the same internal representation
'FLOAT': sqltypes.FLOAT,
'FLOAT8': sqltypes.FLOAT,
'DOUBLE': sqltypes.FLOAT,
'REAL': sqltypes.FLOAT,
'INT': sqltypes.INTEGER,
'INTEGER': sqltypes.INTEGER,
'INT8': sqltypes.INTEGER,
'BIGINT': sqltypes.INTEGER,
'SMALLINT': sqltypes.INTEGER,
'TINYINT': sqltypes.INTEGER,
'NUMERIC': sqltypes.NUMERIC,
'DECIMAL': sqltypes.NUMERIC,
'NUMBER': sqltypes.NUMERIC,
'MONEY': sqltypes.NUMERIC,
}
@classmethod
def dbapi(cls):
vp_module = __import__('vertica_python')
# sqlalchemy expects to find the base Error class here,
# so we need to alias it
vp_module.Error = vp_module.errors.Error
return vp_module
def create_connect_args(self, url):
opts = url.translate_connect_args(username='******')
opts.update(url.query)
return [[], opts]
def has_schema(self, connection, schema):
query = ("SELECT EXISTS (SELECT schema_name FROM v_catalog.schemata "
"WHERE schema_name='%s')") % (schema)
rs = connection.execute(query)
return bool(rs.scalar())
def has_table(self, connection, table_name, schema=None):
if schema is None:
schema = self._get_default_schema_name(connection)
query = ("SELECT EXISTS ("
"SELECT table_name FROM v_catalog.all_tables "
"WHERE schema_name='%s' AND "
"table_name='%s'"
")") % (schema, table_name)
rs = connection.execute(query)
return bool(rs.scalar())
def has_sequence(self, connection, sequence_name, schema=None):
if schema is None:
schema = self._get_default_schema_name(connection)
query = ("SELECT EXISTS ("
"SELECT sequence_name FROM v_catalog.sequences "
"WHERE sequence_schema='%s' AND "
"sequence_name='%s'"
")") % (schema, sequence_name)
rs = connection.execute(query)
return bool(rs.scalar())
def has_type(self, connection, type_name, schema=None):
query = ("SELECT EXISTS ("
"SELECT type_name FROM v_catalog.types "
"WHERE type_name='%s'"
")") % (type_name)
rs = connection.execute(query)
return bool(rs.scalar())
def _get_server_version_info(self, connection):
v = connection.scalar("select version()")
m = re.match(
'.*Vertica Analytic Database '
'v(\d+)\.(\d+)\.(\d)+.*',
v)
if not m:
raise AssertionError(
"Could not determine version from string '%s'" % v)
return tuple([int(x) for x in m.group(1, 2, 3) if x is not None])
def _get_default_schema_name(self, connection):
return connection.scalar("select current_schema()")
@reflection.cache
def get_schema_names(self, connection, **kw):
query = "SELECT schema_name FROM v_catalog.schemata"
rs = connection.execute(query)
return [row[0] for row in rs if not row[0].startswith('v_')]
@reflection.cache
def get_table_names(self, connection, schema=None, **kw):
s = ["SELECT table_name FROM v_catalog.tables"]
if schema is not None:
s.append("WHERE table_schema = '%s'" % (schema,))
s.append("ORDER BY table_schema, table_name")
rs = connection.execute(' '.join(s))
return [row[0] for row in rs]
@reflection.cache
def get_view_names(self, connection, schema=None, **kw):
s = ["SELECT table_name FROM v_catalog.views"]
if schema is not None:
s.append("WHERE table_schema = '%s'" % (schema,))
s.append("ORDER BY table_schema, table_name")
rs = connection.execute(' '.join(s))
return [row[0] for row in rs]
@reflection.cache
def get_columns(self, connection, table_name, schema=None, **kw):
schema_conditional = (
"" if schema is None else "AND table_schema = '{schema}'".format(schema=schema))
pk_column_select = """
SELECT column_name FROM v_catalog.primary_keys
WHERE table_name = '{table_name}'
AND constraint_type = 'p'
{schema_conditional}
""".format(table_name=table_name, schema_conditional=schema_conditional)
primary_key_columns = tuple(row[0] for row in connection.execute(pk_column_select))
column_select = """
SELECT
column_name,
data_type,
column_default,
is_nullable
FROM v_catalog.columns
where table_name = '{table_name}'
{schema_conditional}
UNION ALL
SELECT
column_name,
data_type,
'' as column_default,
true as is_nullable
FROM v_catalog.view_columns
where table_name = '{table_name}'
{schema_conditional}
""".format(table_name=table_name, schema_conditional=schema_conditional)
return [
{
'name': row.column_name,
'type': self.ischema_names[row.data_type.upper().split('(')[0]],
'nullable': row.is_nullable,
'default': row.column_default,
'primary_key': row.column_name in primary_key_columns
} for row in connection.execute(column_select)
]
@reflection.cache
def get_unique_constraints(self, connection, table_name, schema=None, **kw):
query = None
if schema is not None:
query = "select constraint_id, constraint_name, column_name from v_catalog.constraint_columns \n\
WHERE table_name = '" + table_name + "' AND table_schema = '" + schema + "'"
else:
query = "select constraint_id, constraint_name, column_name from v_catalog.constraint_columns \n\
WHERE table_name = '" + table_name + "'"
rs = connection.execute(query)
unique_names = {row[1] for row in rs}
result_dict = {unique: [] for unique in unique_names}
for row in rs:
result_dict[row[1]].append(row[2])
result = []
for key in result_dict.keys():
result.append(
{"name": key,
"column_names": result_dict[key]}
)
return result
@reflection.cache
def get_check_constraints(self, connection, table_name, schema=None, **kw):
query = """
SELECT
cons.constraint_name as name,
cons.predicate as src
FROM
v_catalog.table_constraints cons
WHERE
cons.table_id = (
SELECT
i.table_id
FROM
v_catalog.tables i]
WHERE
i.table_name='{table_name}'
AND
cons.constraint_type = 'c'
{schema_clause}
)
""".format(table_name=table_name, schema_clause=(
"" if schema is None else "AND i.table_schema ='{schema}'".format(schema)))
return [
{
'name': name,
'sqltext': src[1:-1]
} for name, src in connection.execute(query).fetchall()
]
# constraints are enforced on selects, but returning nothing for these
# methods allows table introspection to work
def get_pk_constraint(self, bind, table_name, schema, **kw):
return {'constrained_columns': [], 'name': 'undefined'}
def get_foreign_keys(self, connection, table_name, schema, **kw):
return []
def get_indexes(self, connection, table_name, schema, **kw):
return []
# Disable index creation since that's not a thing in Vertica.
def visit_create_index(self, create):
return None
datetime.date(2013, 10, 10),
datetime.datetime(2013, 10, 10, 11, 27, 16),
datetime.time(11, 27, 16),
"test_bytes",
]
SAMPLE_COLUMNS = [
{"name": "integer", "type": types.Integer(), "nullable": True, "default": None},
{"name": "timestamp", "type": types.TIMESTAMP(), "nullable": True, "default": None},
{"name": "string", "type": types.String(), "nullable": True, "default": None},
{"name": "float", "type": types.Float(), "nullable": True, "default": None},
{"name": "numeric", "type": types.Numeric(), "nullable": True, "default": None},
{"name": "boolean", "type": types.Boolean(), "nullable": True, "default": None},
{"name": "date", "type": types.DATE(), "nullable": True, "default": None},
{"name": "datetime", "type": types.DATETIME(), "nullable": True, "default": None},
{"name": "time", "type": types.TIME(), "nullable": True, "default": None},
{"name": "bytes", "type": types.BINARY(), "nullable": True, "default": None},
{
"name": "record",
"type": types.JSON(),
"nullable": True,
"default": None,
"comment": "In Standard SQL this data type is a STRUCT<name STRING, age INT64>.",
},
{"name": "record.name", "type": types.String(), "nullable": True, "default": None},
{"name": "record.age", "type": types.Integer(), "nullable": True, "default": None},
{"name": "nested_record", "type": types.JSON(), "nullable": True, "default": None},
{
"name": "nested_record.record",
"type": types.JSON(),
"nullable": True,
},
{
"name": "date",
"type": types.DATE(),
"nullable": True,
"default": None
},
{
"name": "datetime",
"type": types.DATETIME(),
"nullable": True,
"default": None
},
{
"name": "time",
"type": types.TIME(),
"nullable": True,
"default": None
},
{
"name": "bytes",
"type": types.BINARY(),
"nullable": True,
"default": None
},
{
"name":
"record",
"type":
sqlalchemy_bigquery.STRUCT(name=types.String, age=types.Integer),
"nullable":
class VerticaDialect(PGDialect):
""" Vertica Dialect using a vertica-python connection and PGDialect """
name = 'vertica'
driver = 'vertica_python'
# UPDATE functionality works with the following option set to False
supports_sane_rowcount = False
supports_unicode_statements = True
supports_unicode_binds = True
supports_native_decimal = True
ischema_names = {
'BINARY': sqltypes.BLOB,
'VARBINARY': sqltypes.BLOB,
'LONG VARBINARY': sqltypes.BLOB,
'BYTEA': sqltypes.BLOB,
'RAW': sqltypes.BLOB,
'BOOLEAN': sqltypes.BOOLEAN,
'CHAR': sqltypes.CHAR,
'VARCHAR': sqltypes.VARCHAR,
'LONG VARCHAR': sqltypes.VARCHAR,
'VARCHAR2': sqltypes.VARCHAR,
'TEXT': sqltypes.VARCHAR,
'UUID': sqltypes.VARCHAR,
'DATE': sqltypes.DATE(),
'DATETIME': sqltypes.DATETIME(),
'SMALLDATETIME': sqltypes.DATETIME(),
'TIME': sqltypes.TIME(),
'TIMETZ': sqltypes.TIME(timezone=True),
'TIME WITH TIMEZONE': sqltypes.TIME(timezone=True),
'TIMESTAMP': sqltypes.TIMESTAMP(),
'TIMESTAMPTZ': sqltypes.TIMESTAMP(timezone=True),
'TIMESTAMP WITH TIMEZONE': sqltypes.TIMESTAMP(timezone=True),
'INTERVAL': INTERVAL,
# All the same internal representation
'FLOAT': sqltypes.FLOAT,
'FLOAT8': sqltypes.FLOAT,
'DOUBLE': sqltypes.FLOAT,
'REAL': sqltypes.FLOAT,
'INT': sqltypes.INTEGER,
'INTEGER': sqltypes.INTEGER,
'INT8': sqltypes.INTEGER,
'BIGINT': sqltypes.INTEGER,
'SMALLINT': sqltypes.INTEGER,
'TINYINT': sqltypes.INTEGER,
'NUMERIC': sqltypes.NUMERIC,
'DECIMAL': sqltypes.NUMERIC,
'NUMBER': sqltypes.NUMERIC,
'MONEY': sqltypes.NUMERIC,
}
# skip all the version-specific stuff in PGDialect's initialize method (Vertica versions don't match feature-wise)
def initialize(self, connection):
super(PGDialect, self).initialize(connection)
self.implicit_returning = False
def is_disconnect(self, e, connection, cursor):
return (
isinstance(e, self.dbapi.Error) and
connection is not None and
connection.closed()
)
@classmethod
def dbapi(cls):
vp_module = __import__('vertica_python')
# sqlalchemy expects to find the base Error class here,
# so we need to alias it
vp_module.Error = vp_module.errors.Error
return vp_module
def create_connect_args(self, url):
opts = url.translate_connect_args(username='******')
opts.update(url.query)
return [[], opts]
def has_schema(self, connection, schema):
query = ("SELECT EXISTS (SELECT schema_name FROM v_catalog.schemata "
"WHERE schema_name='%s')") % (schema)
rs = connection.execute(query)
return bool(rs.scalar())
def has_table(self, connection, table_name, schema=None):
if schema is None:
schema = self._get_default_schema_name(connection)
query = ("SELECT EXISTS ("
"SELECT table_name FROM v_catalog.all_tables "
"WHERE schema_name='%s' AND "
"table_name='%s'"
")") % (schema, table_name)
rs = connection.execute(query)
return bool(rs.scalar())
def has_sequence(self, connection, sequence_name, schema=None):
if schema is None:
schema = self._get_default_schema_name(connection)
query = ("SELECT EXISTS ("
"SELECT sequence_name FROM v_catalog.sequences "
"WHERE sequence_schema='%s' AND "
"sequence_name='%s'"
")") % (schema, sequence_name)
rs = connection.execute(query)
return bool(rs.scalar())
def has_type(self, connection, type_name, schema=None):
query = ("SELECT EXISTS ("
"SELECT type_name FROM v_catalog.types "
"WHERE type_name='%s'"
")") % (type_name)
rs = connection.execute(query)
return bool(rs.scalar())
def _get_server_version_info(self, connection):
v = connection.scalar("select version()")
m = re.match(
'.*Vertica Analytic Database '
'v(\d+)\.(\d+)\.(\d)+.*',
v)
if not m:
raise AssertionError(
"Could not determine version from string '%s'" % v)
return tuple([int(x) for x in m.group(1, 2, 3) if x is not None])
def _get_default_schema_name(self, connection):
return connection.scalar("select current_schema()")
@reflection.cache
def get_schema_names(self, connection, **kw):
query = "SELECT schema_name FROM v_catalog.schemata ORDER BY schema_name"
rs = connection.execute(query)
return [row[0] for row in rs if not row[0].startswith('v_')]
@reflection.cache
def get_table_comment(self, connection, table_name, schema=None, **kw):
schema_conditional = (
"" if schema is None else "AND object_schema = '{schema}'".format(schema=schema))
query = """
SELECT comment FROM v_catalog.comments WHERE object_type = 'TABLE'
AND object_name = '{table_name}'
{schema_conditional}
""".format(table_name=table_name, schema_conditional=schema_conditional)
rs = connection.execute(query)
return {"text": rs.scalar()}
@reflection.cache
def get_table_names(self, connection, schema=None, **kw):
s = ["SELECT table_name FROM v_catalog.tables"]
if schema is not None:
s.append("WHERE table_schema = '%s'" % (schema,))
s.append("ORDER BY table_schema, table_name")
rs = connection.execute(' '.join(s))
return [row[0] for row in rs]
@reflection.cache
def get_view_names(self, connection, schema=None, **kw):
s = ["SELECT table_name FROM v_catalog.views"]
if schema is not None:
s.append("WHERE table_schema = '%s'" % (schema,))
s.append("ORDER BY table_schema, table_name")
rs = connection.execute(' '.join(s))
return [row[0] for row in rs]
@reflection.cache
def get_columns(self, connection, table_name, schema=None, **kw):
schema_conditional = (
"" if schema is None else "AND table_schema = '{schema}'".format(schema=schema))
pk_column_select = """
SELECT column_name FROM v_catalog.primary_keys
WHERE table_name = '{table_name}'
AND constraint_type = 'p'
{schema_conditional}
""".format(table_name=table_name, schema_conditional=schema_conditional)
primary_key_columns = tuple(row[0] for row in connection.execute(pk_column_select))
column_select = """
SELECT
column_name,
data_type,
column_default,
is_nullable,
is_identity,
ordinal_position
FROM v_catalog.columns
where table_name = '{table_name}'
{schema_conditional}
UNION
SELECT
column_name,
data_type,
'' as column_default,
true as is_nullable,
false as is_identity,
ordinal_position
FROM v_catalog.view_columns
where table_name = '{table_name}'
{schema_conditional}
ORDER BY ordinal_position ASC
""".format(table_name=table_name, schema_conditional=schema_conditional)
colobjs = []
column_select_results = list(connection.execute(column_select))
for row in list(connection.execute(column_select)):
sequence_info = connection.execute("""
SELECT
sequence_name as name,
minimum as start,
increment_by as increment
FROM v_catalog.sequences
WHERE identity_table_name = '{table_name}'
{schema_conditional}
""".format(
table_name=table_name,
schema_conditional=(
"" if schema is None
else "AND sequence_schema = '{schema}'".format(schema=schema)
)
)
).first() if row.is_identity else None
colobj = self._get_column_info(
row.column_name,
row.data_type,
row.is_nullable,
row.column_default,
row.is_identity,
(row.column_name in primary_key_columns),
sequence_info
)
if colobj:
colobjs.append(colobj)
return colobjs
def _get_column_info(self, name, data_type, is_nullable, default, is_identity, is_primary_key, sequence):
m = re.match(r'(\w[ \w]*\w)(?:\((\d+)(?:,(\d+))?\))?', data_type)
if not m:
raise ValueError("data type string not parseable for type name and optional parameters: %s" % data_type)
typename = m.group(1).upper()
typeobj = self.ischema_names[typename]
typeargs = []
typekwargs = {}
for arg_group in (2, 3):
try:
param = m.group(arg_group)
if param:
typeargs.append(int(param))
except (TypeError, ValueError):
pass
if any(tz_string in typename for tz_string in ('TIMEZONE', 'TIME ZONE', 'TIMESTAMPTZ')):
typekwargs['timezone'] = True
if callable(typeobj):
typeobj = typeobj(*typeargs, **typekwargs)
column_info = {
'name': name,
'type': typeobj,
'nullable': is_nullable,
'default': default,
'primary_key': (is_primary_key or is_identity)
}
if is_identity:
column_info['autoincrement'] = True
if sequence:
column_info['sequence'] = dict(sequence)
return column_info
@reflection.cache
def get_unique_constraints(self, connection, table_name, schema=None, **kw):
query = "SELECT constraint_id, constraint_name, column_name FROM v_catalog.constraint_columns \n\
WHERE table_name = '" + table_name + "'"
if schema is not None:
query += " AND table_schema = '" + schema + "'"
query += " AND constraint_type = 'u'"
rs = connection.execute(query)
unique_names = {row[1] for row in rs}
result_dict = {unique: [] for unique in unique_names}
for row in rs:
result_dict[row[1]].append(row[2])
result = []
for key in result_dict.keys():
result.append(
{"name": key,
"column_names": result_dict[key]}
)
return result
@reflection.cache
def get_check_constraints(self, connection, table_name, schema=None, **kw):
query = """
SELECT
cons.constraint_name as name,
cons.predicate as src
FROM
v_catalog.table_constraints cons
WHERE
cons.constraint_type = 'c'
AND
cons.table_id = (
SELECT
i.table_id
FROM
v_catalog.tables i
WHERE
i.table_name='{table_name}'
{schema_clause}
)
""".format(table_name=table_name, schema_clause=(
"" if schema is None else "AND i.table_schema ='{schema}'".format(schema=schema)))
return [
{
'name': name,
'sqltext': src[1:-1]
} for name, src in connection.execute(query).fetchall()
]
# constraints are enforced on selects, but returning nothing for these
# methods allows table introspection to work
@reflection.cache
def get_pk_constraint(self, connection, table_name, schema=None, **kw):
query = "SELECT constraint_id, constraint_name, column_name FROM v_catalog.constraint_columns \n\
WHERE constraint_type = 'p' AND table_name = '" + table_name + "'"
if schema is not None:
query += " AND table_schema = '" + schema + "' \n"
cols = set()
name = None
for row in connection.execute(query):
name = row[1] if name is None else name
cols.add(row[2])
return {"constrained_columns": list(cols), "name": name}
def get_foreign_keys(self, connection, table_name, schema, **kw):
return []
def get_indexes(self, connection, table_name, schema, **kw):
return []
# Disable index creation since that's not a thing in Vertica.
def visit_create_index(self, create):
return None
datetime.date(2013, 10, 10),
datetime.datetime(2013, 10, 10, 11, 27, 16),
datetime.time(11, 27, 16),
'test_bytes'
]
SAMPLE_COLUMNS = [
{'name': 'integer', 'type': types.Integer(), 'nullable': True, 'default': None},
{'name': 'timestamp', 'type': types.TIMESTAMP(), 'nullable': True, 'default': None},
{'name': 'string', 'type': types.String(), 'nullable': True, 'default': None},
{'name': 'float', 'type': types.Float(), 'nullable': True, 'default': None},
{'name': 'numeric', 'type': types.DECIMAL(), 'nullable': True, 'default': None},
{'name': 'boolean', 'type': types.Boolean(), 'nullable': True, 'default': None},
{'name': 'date', 'type': types.DATE(), 'nullable': True, 'default': None},
{'name': 'datetime', 'type': types.DATETIME(), 'nullable': True, 'default': None},
{'name': 'time', 'type': types.TIME(), 'nullable': True, 'default': None},
{'name': 'bytes', 'type': types.BINARY(), 'nullable': True, 'default': None},
{'name': 'record', 'type': types.JSON(), 'nullable': True, 'default': None},
{'name': 'record.name', 'type': types.String(), 'nullable': True, 'default': None},
{'name': 'record.age', 'type': types.Integer(), 'nullable': True, 'default': None},
{'name': 'nested_record', 'type': types.JSON(), 'nullable': True, 'default': None},
{'name': 'nested_record.record', 'type': types.JSON(), 'nullable': True, 'default': None},
{'name': 'nested_record.record.name', 'type': types.String(), 'nullable': True, 'default': None},
{'name': 'nested_record.record.age', 'type': types.Integer(), 'nullable': True, 'default': None},
{'name': 'array', 'type': types.ARRAY(types.Integer()), 'nullable': True, 'default': None},
]
@pytest.fixture(scope='session')
def engine():
engine = create_engine('bigquery://', echo=True)
),
(
"DATE",
types.DATE(),
),
(
"DATETIME",
types.DATETIME(),
),
(
"JSON",
types.JSON(),
),
(
"TIME",
types.TIME(timezone=False),
),
(
"TIME_TZ",
types.TIME(timezone=True),
),
(
"TIMESTAMP_NTZ",
types.TIMESTAMP(timezone=False),
),
(
"TIMESTAMP_TZ",
types.TIMESTAMP(timezone=True),
),
(
"TEXT",
