def check(self):
filename = self.input.sql_as_filename
if filename is not None:
stream = open(self.input.sql)
source = stream.read()
stream.close()
else:
source = self.input.sql
from htsql import HTSQL
from htsql.core.error import Error
from htsql.core.connect import connect
from htsql.core.split_sql import split_sql
try:
app = HTSQL(self.input.connect)
except Exception:
self.ui.literal(traceback.format_exc())
self.ctl.failed("exception occured while"
" initializing an HTSQL application")
return
with app:
try:
statements = list(split_sql(source))
except ValueError, exc:
self.ctl.failed("cannot parse SQL: %s" % exc)
return
try:
connection = connect(with_autocommit=self.input.autocommit)
cursor = connection.cursor()
except Error, exc:
self.ui.literal(str(exc))
self.ctl.failed("failed to connect to the database")
return
def __call__(self):
connection = connect()
cursor = connection.cursor()
catalog = make_catalog()
schema_by_id = {}
cursor.execute("""
SELECT schema_id, name
FROM sys.schemas
ORDER BY name
""")
for row in cursor.fetchnamed():
if any(
fnmatch.fnmatchcase(row.name, pattern)
for pattern in self.system_schema_names):
continue
schema = catalog.add_schema(row.name)
schema_by_id[row.schema_id] = schema
cursor.execute("""
SELECT default_schema_name
FROM sys.database_principals
WHERE principal_id = USER_ID()
""")
default_schema_name = cursor.fetchone()[0]
if default_schema_name in catalog:
catalog[default_schema_name].set_priority(1)
table_by_id = {}
cursor.execute("""
SELECT object_id, schema_id, name
FROM sys.objects
WHERE type in ('U', 'V')
ORDER BY schema_id, name
""")
for row in cursor.fetchnamed():
if row.schema_id not in schema_by_id:
continue
schema = schema_by_id[row.schema_id]
table = schema.add_table(row.name)
table_by_id[row.object_id] = table
column_by_id = {}
cursor.execute("""
SELECT c.object_id, c.column_id, c.name, c.max_length,
c.precision, c.scale, c.is_nullable, c.default_object_id,
t.name AS type_name, s.name AS type_schema_name
FROM sys.columns c
JOIN sys.types t ON (c.user_type_id = t.user_type_id)
JOIN sys.schemas s ON (t.schema_id = s.schema_id)
ORDER BY c.object_id, c.column_id
""")
for row in cursor.fetchnamed():
if row.object_id not in table_by_id:
continue
table = table_by_id[row.object_id]
name = row.name
type_schema_name = row.type_schema_name
type_name = row.type_name
length = row.max_length if row.max_length != -1 else None
precision = row.precision
scale = row.scale
domain = IntrospectMSSQLDomain.__invoke__(type_schema_name,
type_name, length,
precision, scale)
is_nullable = bool(row.is_nullable)
has_default = bool(row.default_object_id)
column = table.add_column(name, domain, is_nullable, has_default)
column_by_id[row.object_id, row.column_id] = column
cursor.execute("""
SELECT object_id, index_id, is_primary_key, is_unique_constraint
FROM sys.indexes
WHERE (is_primary_key = 1 OR is_unique_constraint = 1) AND
is_disabled = 0
ORDER BY object_id, index_id
""")
index_rows = cursor.fetchnamed()
cursor.execute("""
SELECT object_id, index_id, index_column_id, column_id
FROM sys.index_columns
ORDER BY object_id, index_id, index_column_id
""")
column_rows_by_id = \
dict((key, list(group))
for key, group in itertools.groupby(cursor.fetchnamed(),
lambda r: (r.object_id, r.index_id)))
for row in index_rows:
if row.object_id not in table_by_id:
continue
table = table_by_id[row.object_id]
key = (row.object_id, row.index_id)
if key not in column_rows_by_id:
continue
column_rows = column_rows_by_id[key]
if not all(
(column_row.object_id, column_row.column_id) in column_by_id
for column_row in column_rows):
continue
columns = [
column_by_id[column_row.object_id, column_row.column_id]
for column_row in column_rows
]
is_primary = bool(row.is_primary_key)
table.add_unique_key(columns, is_primary)
cursor.execute("""
SELECT object_id, parent_object_id, referenced_object_id
FROM sys.foreign_keys
WHERE is_disabled = 0
ORDER BY object_id
""")
key_rows = cursor.fetchnamed()
cursor.execute("""
SELECT constraint_object_id, constraint_column_id,
parent_object_id, parent_column_id,
referenced_object_id, referenced_column_id
FROM sys.foreign_key_columns
ORDER BY constraint_object_id, constraint_column_id
""")
key_column_rows_by_id = \
dict((key, list(group))
for key, group in itertools.groupby(cursor.fetchnamed(),
lambda r: r.constraint_object_id))
for row in key_rows:
if row.parent_object_id not in table_by_id:
continue
table = table_by_id[row.parent_object_id]
if row.referenced_object_id not in table_by_id:
continue
target_table = table_by_id[row.referenced_object_id]
if row.object_id not in key_column_rows_by_id:
continue
column_rows = key_column_rows_by_id[row.object_id]
column_ids = [(column_row.parent_object_id,
column_row.parent_column_id)
for column_row in column_rows]
target_column_ids = [(column_row.referenced_object_id,
column_row.referenced_column_id)
for column_row in column_rows]
if not all(column_id in column_by_id for column_id in column_ids):
continue
columns = [column_by_id[column_id] for column_id in column_ids]
if not all(column_id in column_by_id
for column_id in target_column_ids):
continue
target_columns = [
column_by_id[column_id] for column_id in target_column_ids
]
table.add_foreign_key(columns, target_table, target_columns)
connection.release()
return catalog
def __call__(self):
connection = connect()
cursor = connection.cursor()
catalog = make_catalog()
cursor.execute("""
SELECT n.oid, n.nspname
FROM pg_catalog.pg_namespace n
ORDER BY n.nspname
""")
schema_by_oid = {}
for row in cursor.fetchnamed():
name = row.nspname
if any(fnmatch.fnmatchcase(name, pattern)
for pattern in self.system_schema_names):
continue
schema = catalog.add_schema(name)
schema_by_oid[row.oid] = schema
cursor.execute("""
SELECT CURRENT_SCHEMAS(TRUE)
""")
search_path = cursor.fetchone()[0]
for idx, name in enumerate(search_path):
priority = len(search_path)-idx
if name in catalog:
catalog[name].set_priority(priority)
table_by_oid = {}
cursor.execute("""
SELECT c.oid, c.relnamespace, c.relname
FROM pg_catalog.pg_class c
WHERE c.relkind IN ('r', 'v') AND
HAS_TABLE_PRIVILEGE(c.oid, 'SELECT')
ORDER BY c.relnamespace, c.relname
""")
for row in cursor.fetchnamed():
if row.relnamespace not in schema_by_oid:
continue
name = row.relname
if any(fnmatch.fnmatchcase(name, pattern)
for pattern in self.system_table_names):
continue
schema = schema_by_oid[row.relnamespace]
table = schema.add_table(row.relname)
table_by_oid[row.oid] = table
cursor.execute("""
SELECT t.oid, n.nspname, t.typname, t.typtype,
t.typbasetype, t.typlen, t.typtypmod, t.typdefault
FROM pg_catalog.pg_type t
JOIN pg_catalog.pg_namespace n ON (t.typnamespace = n.oid)
ORDER BY n.oid, t.typname
""")
typrows_by_oid = dict((row.oid, row) for row in cursor.fetchnamed())
# FIXME: respect `enumsortorder` if available
cursor.execute("""
SELECT e.enumtypid, e.enumlabel
FROM pg_catalog.pg_enum e
ORDER BY e.enumtypid, e.oid
""")
enumrows_by_typid = dict((key, list(group))
for key, group
in itertools.groupby(cursor.fetchnamed(),
lambda r: r.enumtypid))
column_by_num = {}
cursor.execute("""
SELECT a.attrelid, a.attnum, a.attname, a.atttypid, a.atttypmod,
a.attnotnull, a.atthasdef, a.attisdropped
FROM pg_catalog.pg_attribute a
ORDER BY a.attrelid, a.attnum
""")
for row in cursor.fetchnamed():
if row.attisdropped:
continue
if any(fnmatch.fnmatchcase(row.attname, pattern)
for pattern in self.system_column_names):
continue
if row.attrelid not in table_by_oid:
continue
table = table_by_oid[row.attrelid]
name = row.attname
modifier = row.atttypmod
typrow = typrows_by_oid[row.atttypid]
length = typrow.typlen
if modifier == -1:
modifier = typrow.typtypmod
is_nullable = (not row.attnotnull)
has_default = (row.atthasdef or typrow.typdefault is not None)
domain = IntrospectPGSQLDomain.__invoke__(typrow.nspname,
typrow.typname,
length, modifier)
while isinstance(domain, OpaqueDomain) and typrow.typtype == 'd':
typrow = typrows_by_oid[typrow.typbasetype]
if modifier == -1:
modifier = typrow.typtypmod
domain = IntrospectPGSQLDomain.__invoke__(typrow.nspname,
typrow.typname,
length, modifier)
if (isinstance(domain, OpaqueDomain) and typrow.typtype == 'e'
and typrow.oid in enumrows_by_typid):
enumrows = enumrows_by_typid[typrow.oid]
labels = [enumrow.enumlabel
for enumrow in enumrows]
domain = EnumDomain(labels=labels)
column = table.add_column(name, domain, is_nullable, has_default)
column_by_num[row.attrelid, row.attnum] = column
cursor.execute("""
SELECT c.contype, c.confmatchtype,
c.conrelid, c.conkey, c.confrelid, c.confkey
FROM pg_catalog.pg_constraint c
WHERE c.contype IN ('p', 'u', 'f')
ORDER BY c.oid
""")
for row in cursor.fetchnamed():
if row.conrelid not in table_by_oid:
continue
table = table_by_oid[row.conrelid]
if not all((row.conrelid, num) in column_by_num
for num in row.conkey):
continue
columns = [column_by_num[row.conrelid, num]
for num in row.conkey]
if row.contype in ('p', 'u'):
is_primary = (row.contype == 'p')
table.add_unique_key(columns, is_primary)
elif row.contype == 'f':
if row.confrelid not in table_by_oid:
continue
target_table = table_by_oid[row.confrelid]
if not all((row.confrelid, num) in column_by_num
for num in row.confkey):
continue
target_columns = [column_by_num[row.confrelid, num]
for num in row.confkey]
is_partial = (len(target_columns) > 1 and
any(column.is_nullable
for column in target_columns) and
row.confmatchtype == 'u')
table.add_foreign_key(columns, target_table, target_columns,
is_partial)
connection.release()
return catalog
def __call__(self):
connection = connect()
cursor = connection.cursor()
catalog = make_catalog()
cursor.execute("""
SELECT s.schema_name
FROM information_schema.schemata s
ORDER BY 1
""")
for row in cursor.fetchnamed():
catalog.add_schema(row.schema_name)
cursor.execute("""
SELECT DATABASE()
""")
database_name = cursor.fetchone()[0]
if database_name in catalog:
catalog[database_name].set_priority(1)
cursor.execute("""
SELECT t.table_schema, t.table_name
FROM information_schema.tables t
WHERE t.table_type IN ('BASE TABLE', 'VIEW')
ORDER BY 1, 2
""")
for row in cursor.fetchnamed():
if row.table_schema not in catalog:
continue
schema = catalog[row.table_schema]
schema.add_table(row.table_name)
cursor.execute("""
SELECT c.table_schema, c.table_name, c.ordinal_position,
c.column_name, c.is_nullable, c.column_default,
c.data_type, c.column_type, c.character_maximum_length,
c.numeric_precision, c.numeric_scale
FROM information_schema.columns c
ORDER BY 1, 2, 3
""")
for row in cursor.fetchnamed():
if row.table_schema not in catalog:
continue
schema = catalog[row.table_schema]
if row.table_name not in schema:
continue
table = schema[row.table_name]
name = row.column_name
is_nullable = (row.is_nullable == 'YES')
has_default = (row.column_default is not None)
data_type = row.data_type
column_type = row.column_type
length = row.character_maximum_length
if isinstance(length, long):
length = int(length)
if isinstance(length, long): # LONGTEXT
length = None
precision = row.numeric_precision
if isinstance(precision, long):
precision = int(precision)
scale = row.numeric_scale
if isinstance(scale, long):
scale = int(scale)
domain = IntrospectMySQLDomain.__invoke__(data_type, column_type,
length, precision, scale)
table.add_column(name, domain, is_nullable, has_default)
cursor.execute("""
SELECT c.table_schema, c.table_name,
c.constraint_schema, c.constraint_name,
c.constraint_type
FROM information_schema.table_constraints c
WHERE c.constraint_type IN ('PRIMARY KEY', 'UNIQUE', 'FOREIGN KEY')
ORDER BY 1, 2, 3, 4
""")
constraint_rows = cursor.fetchnamed()
cursor.execute("""
SELECT u.table_schema, u.table_name,
u.constraint_schema, u.constraint_name,
u.ordinal_position,
u.column_name,
u.referenced_table_schema,
u.referenced_table_name,
u.referenced_column_name
FROM information_schema.key_column_usage u
ORDER BY 1, 2, 3, 4, 5
""")
usage_rows_by_constraint_key = \
dict((key, list(group))
for key, group in itertools.groupby(cursor.fetchnamed(),
lambda r: (r.table_schema, r.table_name,
r.constraint_schema, r.constraint_name)))
for constraint_row in constraint_rows:
key = (constraint_row.table_schema,
constraint_row.table_name,
constraint_row.constraint_schema,
constraint_row.constraint_name)
if key not in usage_rows_by_constraint_key:
continue
usage_rows = usage_rows_by_constraint_key[key]
if constraint_row.table_schema not in catalog:
continue
schema = catalog[constraint_row.table_schema]
if constraint_row.table_name not in schema:
continue
table = schema[constraint_row.table_name]
if not all(row.column_name in table.columns
for row in usage_rows):
continue
columns = [table.columns[row.column_name] for row in usage_rows]
if constraint_row.constraint_type in ('PRIMARY KEY', 'UNIQUE'):
is_primary = (constraint_row.constraint_type == 'PRIMARY KEY')
table.add_unique_key(columns, is_primary)
elif constraint_row.constraint_type == 'FOREIGN KEY':
row = usage_rows[0]
if row.referenced_table_schema not in catalog:
continue
target_schema = catalog[row.referenced_table_schema]
if row.referenced_table_name not in target_schema:
continue
target_table = target_schema[row.referenced_table_name]
if not all(row.referenced_column_name in target_table.columns
for row in usage_rows):
continue
target_columns = \
[target_table.columns[row.referenced_column_name]
for row in usage_rows]
table.add_foreign_key(columns, target_table, target_columns)
connection.release()
return catalog
def __call__(self):
connection = connect()
cursor = connection.cursor()
catalog = make_catalog()
schema = catalog.add_schema('')
cursor.execute("""
SELECT *
FROM sqlite_master
WHERE type = 'table' OR type = 'view'
ORDER BY name
""")
for row in cursor.fetchnamed():
schema.add_table(row.name)
for table in schema:
cursor.execute("""PRAGMA table_info(%s)"""
% self.escape_name(table.name))
primary_key_columns = []
for row in cursor.fetchnamed():
name = row.name
domain = IntrospectSQLiteDomain.__invoke__(row.type)
is_nullable = (not row.notnull)
has_default = (row.dflt_value is not None)
column = table.add_column(name, domain,
is_nullable, has_default)
if row.pk:
primary_key_columns.append(column)
if primary_key_columns:
# SQLite does not enforce NOT NULL on PRIMARY KEY columns.
if any(column.is_nullable for column in primary_key_columns):
table.add_unique_key(primary_key_columns)
else:
table.add_primary_key(primary_key_columns)
for table in schema:
cursor.execute("""PRAGMA index_list(%s)"""
% self.escape_name(table.name))
for index_row in cursor.fetchnamed():
if not index_row.unique:
continue
cursor.execute("""PRAGMA index_info(%s)"""
% self.escape_name(index_row.name))
columns = []
for row in cursor.fetchnamed():
columns.append(table[row.name])
table.add_unique_key(columns)
for table in schema:
ids = set()
columns_by_id = {}
target_by_id = {}
target_columns_by_id = {}
cursor.execute("""PRAGMA foreign_key_list(%s)"""
% self.escape_name(table.name))
for row in cursor.fetchnamed():
if row.id not in ids:
ids.add(row.id)
columns_by_id[row.id] = []
target_name = row.table
# Workaround against extra quoting in
# PRAGMA foreign_key_list; column `table`.
# See `http://www.sqlite.org/cvstrac/tktview?tn=3800`
# and `http://www.sqlite.org/src/ci/600482d161`.
# The bug is fixed in SQLite 3.6.14.
if (target_name.startswith('"') and
target_name.endswith('"')):
target_name = target_name[1:-1].replace('""', '"')
target_by_id[row.id] = schema[target_name]
target_columns_by_id[row.id] = []
target = target_by_id[row.id]
column = table[row.from_]
target_column = target.columns[row.to]
columns_by_id[row.id].append(column)
target_columns_by_id[row.id].append(target_column)
for id in sorted(ids):
columns = columns_by_id[id]
target = target_by_id[id]
target_columns = target_columns_by_id[id]
table.add_foreign_key(columns, target, target_columns)
connection.release()
return catalog
def __call__(self):
connection = connect()
cursor = connection.cursor()
catalog = make_catalog()
if self.system_owner_names:
ignored_owners = ("(%s)" %
", ".join("'%s'" % name
for name in self.system_owner_names))
else:
ignored_owners = "('$')"
cursor.execute("""
SELECT username
FROM all_users
WHERE username NOT IN %(ignored_owners)s
ORDER BY 1
""" % vars())
for row in cursor.fetchnamed():
if '$' in row.username:
continue
catalog.add_schema(row.username)
cursor.execute("""
SELECT USER FROM DUAL
""")
current_user = cursor.fetchone()[0]
if current_user in catalog:
catalog[current_user].set_priority(1)
cursor.execute("""
SELECT owner, table_name
FROM all_catalog
WHERE owner NOT IN %(ignored_owners)s AND
table_type IN ('TABLE', 'VIEW')
ORDER BY 1, 2
""" % vars())
for row in cursor.fetchnamed():
if '$' in row.table_name:
continue
if row.owner not in catalog:
continue
schema = catalog[row.owner]
schema.add_table(row.table_name)
cursor.execute("""
SELECT owner, constraint_name, table_name, search_condition
FROM all_constraints
WHERE owner NOT IN %(ignored_owners)s AND
constraint_type = 'C'
ORDER BY 1, 3, 2
""" % vars())
checkrows_by_table = \
dict((key, list(group))
for key, group in itertools.groupby(cursor.fetchnamed(),
lambda r: (r.owner, r.table_name)))
cursor.execute("""
SELECT owner, table_name, column_id, column_name,
data_type, data_length, data_precision, data_scale,
nullable, data_default
FROM all_tab_columns
WHERE owner NOT IN %(ignored_owners)s
ORDER BY 1, 2, 3
""" % vars())
for row in cursor.fetchnamed():
if '$' in row.column_name:
continue
if row.owner not in catalog:
continue
schema = catalog[row.owner]
if row.table_name not in schema:
continue
table = schema[row.table_name]
name = row.column_name
check = None
check_key = (row.owner, row.table_name)
if check_key in checkrows_by_table:
for checkrow in checkrows_by_table[check_key]:
condition = checkrow.search_condition
if (condition.lower().startswith(name.lower() + ' ') or
condition.lower().startswith('"' + name.lower() +
'" ')):
check = condition
break
domain = IntrospectOracleDomain.__invoke__(row.data_type,
row.data_length,
row.data_precision,
row.data_scale, check)
is_nullable = (row.nullable == 'Y')
has_default = (row.data_default is not None)
table.add_column(name, domain, is_nullable, has_default)
cursor.execute("""
SELECT owner, constraint_name, constraint_type,
table_name, r_owner, r_constraint_name
FROM all_constraints
WHERE owner NOT IN %(ignored_owners)s AND
constraint_type IN ('P', 'U', 'R') AND
status = 'ENABLED' AND validated = 'VALIDATED'
ORDER BY 1, 2
""" % vars())
constraint_rows = cursor.fetchnamed()
constraint_row_by_constraint = \
dict(((row.owner, row.constraint_name), row)
for row in constraint_rows)
cursor.execute("""
SELECT owner, constraint_name, position, column_name
FROM all_cons_columns
WHERE owner NOT IN %(ignored_owners)s
ORDER BY 1, 2, 3
""" % vars())
column_rows_by_constraint = \
dict((key, list(group))
for key, group in itertools.groupby(cursor.fetchnamed(),
lambda r: (r.owner, r.constraint_name)))
for row in constraint_rows:
key = (row.owner, row.constraint_name)
if key not in column_rows_by_constraint:
continue
column_rows = column_rows_by_constraint[key]
if row.owner not in catalog:
continue
schema = catalog[row.owner]
if row.table_name not in schema:
continue
table = schema[row.table_name]
if not all(column_row.column_name in table.columns
for column_row in column_rows):
continue
columns = [
table.columns[column_row.column_name]
for column_row in column_rows
]
if row.constraint_type in ('P', 'U'):
is_primary = (row.constraint_type == 'P')
table.add_unique_key(columns, is_primary)
elif row.constraint_type == 'R':
target_key = (row.r_owner, row.r_constraint_name)
if target_key not in constraint_row_by_constraint:
continue
if target_key not in column_rows_by_constraint:
continue
target_row = constraint_row_by_constraint[target_key]
target_column_rows = column_rows_by_constraint[target_key]
if target_row.owner not in catalog:
continue
target_schema = catalog[target_row.owner]
if target_row.table_name not in target_schema:
continue
target_table = target_schema[target_row.table_name]
if not all(column_row.column_name in target_table.columns
for column_row in target_column_rows):
continue
target_columns = [
target_table.columns[column_row.column_name]
for column_row in target_column_rows
]
table.add_foreign_key(columns, target_table, target_columns)
connection.release()
return catalog
table = table[table.find('.') + 1:]
arguments = ", ".join(columns)
parameters = ", ".join(["?"] * len(columns))
if with_pyparams:
parameters = ", ".join(["%s"] * len(columns))
if with_numparams:
parameters = ", ".join(":" + str(idx + 1)
for idx in range(len(columns)))
sql = "INSERT INTO %s (%s) VALUES (%s)" \
% (table, arguments, parameters)
cursor.executemany(sql, data)
with app:
connection = connect()
cursor = connection.cursor()
content = yaml.load(open(REGRESS_DATA))
assert isinstance(content, list)
for sql in prelude:
cursor.execute(sql)
for line in content:
insert_table_data(line, cursor)
generated_content = data_generator.generate(content)
for line in generated_content:
insert_table_data(line, cursor)