def _get_tables(self, schema, datasource_id):
query = """
SELECT col.table_schema,
col.table_name,
col.column_name,
col.data_type
FROM `information_schema`.`columns` col
INNER JOIN
(SELECT table_schema,
TABLE_NAME
FROM information_schema.tables
WHERE table_type <> 'SYSTEM VIEW' AND table_schema NOT IN ('performance_schema', 'mysql')) tables ON tables.table_schema = col.table_schema
AND tables.TABLE_NAME = col.TABLE_NAME;
"""
results, error = self.run_query(query)
if error is not None:
raise Exception("Failed getting schema.")
results = json.loads(results)
for row in results['rows']:
if row['table_schema'] != self.configuration['db']:
table_name = '{}.{}'.format(row['table_schema'], row['table_name'])
else:
table_name = row['table_name']
if table_name not in schema:
schema[table_name] = {'name': table_name, 'columns': []}
# schema[table_name]['columns'].append(row['column_name'])
schema[table_name]['columns'].append((row['column_name'],row['data_type']))
for tablename, data in schema.iteritems():
table, created = DataSourceTable.get_or_create(
datasource=datasource_id,
name=tablename
)
for c in data['columns']:
try:
column, created = DataSourceColumn.get_or_create(
table=table.id,
name=c[0],
data_type=c[1]
)
except Exception as ex:
# Will get thrown when an existing column gets a new data_type, so just update data_type
column = DataSourceColumn.get(table=table.id, name=c[0])
if column.data_type != c[1]:
column.data_type = c[1]
column.save()
schema[table.name] = table.to_dict()
return schema.values()
def _get_tables(self, schema, datasource_id):
query = """
SELECT
user_tables.TABLESPACE_NAME,
all_tab_cols.TABLE_NAME,
all_tab_cols.COLUMN_NAME,
all_tab_cols.DATA_TYPE
FROM all_tab_cols
JOIN user_tables ON (all_tab_cols.TABLE_NAME = user_tables.TABLE_NAME)
"""
results, error = self.run_query(query)
if error is not None:
raise Exception("Failed getting schema.")
results = json.loads(results)
for row in results['rows']:
if row['TABLESPACE_NAME'] != None:
table_name = '{}.{}'.format(row['TABLESPACE_NAME'], row['TABLE_NAME'])
else:
table_name = row['TABLE_NAME']
if table_name not in schema:
schema[table_name] = {'name': table_name, 'columns': []}
schema[table_name]['columns'].append((row['COLUMN_NAME'], row['DATA_TYPE']))
for tablename, data in schema.iteritems():
table, created = DataSourceTable.get_or_create(
datasource=datasource_id,
name=tablename
)
for c in data['columns']:
try:
column, created = DataSourceColumn.get_or_create(
table=table.id,
name=c[0],
data_type=c[1]
)
except Exception as ex:
# Will get thrown when an existing column gets a new data_type, so just update data_type
column = DataSourceColumn.get(table=table.id, name=c[0])
if column.data_type != c[1]:
column.data_type = c[1]
column.save()
schema[table.name] = table.to_dict()
return schema.values()
def _get_tables(self, schema, datasource_id):
query = """
SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema NOT IN ('guest','INFORMATION_SCHEMA','sys','db_owner','db_accessadmin'
,'db_securityadmin','db_ddladmin','db_backupoperator','db_datareader'
,'db_datawriter','db_denydatareader','db_denydatawriter'
);
"""
results, error = self.run_query(query)
if error is not None:
raise Exception("Failed getting schema.")
results = json.loads(results)
for row in results['rows']:
if row['table_schema'] != self.configuration['db']:
table_name = '{}.{}'.format(row['table_schema'], row['table_name'])
else:
table_name = row['table_name']
if table_name not in schema:
schema[table_name] = {'name': table_name, 'columns': []}
schema[table_name]['columns'].append((row['column_name'], row['data_type']))
for tablename, data in schema.iteritems():
table, created = DataSourceTable.get_or_create(
datasource=datasource_id,
name=tablename
)
for c in data['columns']:
try:
column, created = DataSourceColumn.get_or_create(
table=table.id,
name=c[0],
data_type=c[1]
)
except Exception as ex:
# Will get thrown when an existing column gets a new data_type, so just update data_type
column = DataSourceColumn.get(table=table.id, name=c[0])
if column.data_type != c[1]:
column.data_type = c[1]
column.save()
schema[table.name] = table.to_dict()
return schema.values()
def _get_tables(self, schema, datasource_id):
query = """
SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_name, ordinal_position;
"""
results, error = self.run_query(query)
if error is not None:
raise Exception("Failed getting schema.")
results = json.loads(results)
for row in results['rows']:
if row['table_schema'] != 'public':
table_name = '{}.{}'.format(row['table_schema'], row['table_name'])
else:
table_name = row['table_name']
if table_name not in schema:
schema[table_name] = {'name': table_name, 'columns': []}
schema[table_name]['columns'].append((row['column_name'], row['data_type']))
for tablename, data in schema.iteritems():
table, created = DataSourceTable.get_or_create(
datasource=datasource_id,
name=tablename
)
for c in data['columns']:
try:
column, created = DataSourceColumn.get_or_create(
table=table.id,
name=c[0],
data_type=c[1]
)
except Exception as ex:
# Will get thrown when an existing column gets a new data_type, so just update data_type
column = DataSourceColumn.get(table=table.id, name=c[0])
if column.data_type != c[1]:
column.data_type = c[1]
column.save()
schema[table.name] = table.to_dict()
def _get_tables(self, schema, datasource_id):
query_table = "select tbl_name from sqlite_master where type='table'"
query_columns = "PRAGMA table_info(%s)"
results, error = self.run_query(query_table)
if error is not None:
raise Exception("Failed getting schema.")
results = json.loads(results)
for row in results['rows']:
table_name = row['tbl_name']
schema[table_name] = {'name': table_name, 'columns': []}
results_table, error = self.run_query(query_columns % (table_name,))
if error is not None:
raise Exception("Failed getting schema.")
results_table = json.loads(results_table)
for row_column in results_table['rows']:
schema[table_name]['columns'].append((row_column['name'],row_column['type']))
for tablename, data in schema.iteritems():
table, created = DataSourceTable.get_or_create(
datasource=datasource_id,
name=tablename
)
for c in data['columns']:
try:
column, created = DataSourceColumn.get_or_create(
table=table.id,
name=c[0],
data_type=c[1]
)
except Exception as ex:
# Will get thrown when an existing column gets a new data_type, so just update data_type
column = DataSourceColumn.get(table=table.id, name=c[0])
if column.data_type != c[1]:
column.data_type = c[1]
column.save()
schema[table.name] = table.to_dict()
from redash.models import db, DataSourceTable, DataSourceColumn, DataSourceJoin
if __name__ == '__main__':
with db.database.transaction():
if not DataSourceTable.table_exists():
DataSourceTable.create_table()
if not DataSourceColumn.table_exists():
DataSourceColumn.create_table()
if not DataSourceJoin.table_exists():
DataSourceJoin.create_table()
db.close_db(None)