def test_databaseview_edit(self, username='******'):
# validate that sending a password-masked uri does not over-write the decrypted
# uri
self.login(username=username)
database = get_main_database(db.session)
sqlalchemy_uri_decrypted = database.sqlalchemy_uri_decrypted
url = 'databaseview/edit/{}'.format(database.id)
data = {k: database.__getattribute__(k) for k in DatabaseView.add_columns}
data['sqlalchemy_uri'] = database.safe_sqlalchemy_uri()
self.client.post(url, data=data)
database = get_main_database(db.session)
self.assertEqual(sqlalchemy_uri_decrypted, database.sqlalchemy_uri_decrypted)
def test_run_async_query_with_lower_limit(self):
main_db = get_main_database(db.session)
db_id = main_db.id
self.drop_table_if_exists('tmp_async_2', main_db)
sql_where = "SELECT name FROM ab_role WHERE name='Alpha' LIMIT 1"
result = self.run_sql(db_id,
sql_where,
'5',
async_='true',
tmp_table='tmp_async_2',
cta='true')
assert result['query']['state'] in (QueryStatus.PENDING,
QueryStatus.RUNNING,
QueryStatus.SUCCESS)
time.sleep(CELERY_SLEEP_TIME)
query = self.get_query_by_id(result['query']['serverId'])
self.assertEqual(QueryStatus.SUCCESS, query.status)
self.assertTrue('FROM tmp_async_2' in query.select_sql)
self.assertEqual(
'CREATE TABLE tmp_async_2 AS \nSELECT name FROM ab_role '
"WHERE name='Alpha' LIMIT 1", query.executed_sql)
self.assertEqual(sql_where, query.sql)
self.assertEqual(0, query.rows)
self.assertEqual(1, query.limit)
self.assertEqual(True, query.select_as_cta)
self.assertEqual(True, query.select_as_cta_used)
def test_run_async_query_with_lower_limit(self):
main_db = get_main_database(db.session)
eng = main_db.get_sqla_engine()
sql_where = "SELECT name FROM ab_role WHERE name='Alpha' LIMIT 1"
result = self.run_sql(main_db.id,
sql_where,
'5',
async_='true',
tmp_table='tmp_async_2',
cta='true')
assert result['query']['state'] in (QueryStatus.PENDING,
QueryStatus.RUNNING,
QueryStatus.SUCCESS)
time.sleep(1)
query = self.get_query_by_id(result['query']['serverId'])
df = pd.read_sql_query(query.select_sql, con=eng)
self.assertEqual(QueryStatus.SUCCESS, query.status)
self.assertEqual([{'name': 'Alpha'}], df.to_dict(orient='records'))
self.assertEqual(QueryStatus.SUCCESS, query.status)
self.assertTrue('FROM tmp_async_2' in query.select_sql)
self.assertEqual(
'CREATE TABLE tmp_async_2 AS \nSELECT name FROM ab_role '
"WHERE name='Alpha' LIMIT 1", query.executed_sql)
self.assertEqual(sql_where, query.sql)
self.assertEqual(0, query.rows)
self.assertEqual(1, query.limit)
self.assertEqual(True, query.select_as_cta)
self.assertEqual(True, query.select_as_cta_used)
def test_testconn(self, username='******'):
self.login(username=username)
database = get_main_database(db.session)
# validate that the endpoint works with the password-masked sqlalchemy uri
data = json.dumps({
'uri': database.safe_sqlalchemy_uri(),
'name': 'main',
'impersonate_user': False,
})
response = self.client.post('/superset/testconn',
data=data,
content_type='application/json')
assert response.status_code == 200
assert response.headers['Content-Type'] == 'application/json'
# validate that the endpoint works with the decrypted sqlalchemy uri
data = json.dumps({
'uri': database.sqlalchemy_uri_decrypted,
'name': 'main',
'impersonate_user': False,
})
response = self.client.post('/superset/testconn',
data=data,
content_type='application/json')
assert response.status_code == 200
assert response.headers['Content-Type'] == 'application/json'
def test_sql_json_has_access(self):
main_db = get_main_database(db.session)
security_manager.add_permission_view_menu('database_access', main_db.perm)
db.session.commit()
main_db_permission_view = (
db.session.query(ab_models.PermissionView)
.join(ab_models.ViewMenu)
.join(ab_models.Permission)
.filter(ab_models.ViewMenu.name == '[main].(id:1)')
.filter(ab_models.Permission.name == 'database_access')
.first()
)
astronaut = security_manager.add_role('Astronaut')
security_manager.add_permission_role(astronaut, main_db_permission_view)
# Astronaut role is Gamma + sqllab + main db permissions
for perm in security_manager.find_role('Gamma').permissions:
security_manager.add_permission_role(astronaut, perm)
for perm in security_manager.find_role('sql_lab').permissions:
security_manager.add_permission_role(astronaut, perm)
gagarin = security_manager.find_user('gagarin')
if not gagarin:
security_manager.add_user(
'gagarin', 'Iurii', 'Gagarin', '*****@*****.**',
astronaut,
password='******')
data = self.run_sql('SELECT * FROM ab_user', '3', user_name='gagarin')
db.session.query(Query).delete()
db.session.commit()
self.assertLess(0, len(data['data']))
def create_table(self,
name,
schema='',
id=0,
cols_names=[],
metric_names=[]):
database_name = 'main'
name = '{0}{1}'.format(NAME_PREFIX, name)
params = {DBREF: id, 'database_name': database_name}
dict_rep = {
'database_id': get_main_database(db.session).id,
'table_name': name,
'schema': schema,
'id': id,
'params': json.dumps(params),
'columns': [{
'column_name': c
} for c in cols_names],
'metrics': [{
'metric_name': c
} for c in metric_names],
}
table = SqlaTable(
id=id,
schema=schema,
table_name=name,
params=json.dumps(params),
)
for col_name in cols_names:
table.columns.append(TableColumn(column_name=col_name))
for metric_name in metric_names:
table.metrics.append(SqlMetric(metric_name=metric_name))
return table, dict_rep
def test_single_statement(self):
main_db = get_main_database(db.session)
if main_db.backend == 'mysql':
df = main_db.get_df('SELECT 1', None)
self.assertEquals(df.iat[0, 0], 1)
df = main_db.get_df('SELECT 1;', None)
self.assertEquals(df.iat[0, 0], 1)
def test_multi_statement(self):
main_db = get_main_database(db.session)
if main_db.backend == 'mysql':
df = main_db.get_df('USE superset; SELECT 1', None)
self.assertEquals(df.iat[0, 0], 1)
df = main_db.get_df("USE superset; SELECT ';';", None)
self.assertEquals(df.iat[0, 0], ';')
def test_run_sync_query_cta_no_data(self):
main_db = get_main_database(db.session)
db_id = main_db.id
sql_empty_result = 'SELECT * FROM ab_user WHERE id=666'
result3 = self.run_sql(db_id, sql_empty_result, '3')
self.assertEqual(QueryStatus.SUCCESS, result3['query']['state'])
self.assertEqual([], result3['data'])
self.assertEqual([], result3['columns'])
query3 = self.get_query_by_id(result3['query']['serverId'])
self.assertEqual(QueryStatus.SUCCESS, query3.status)
def test_custom_password_store(self):
database = get_main_database(db.session)
conn_pre = sqla.engine.url.make_url(database.sqlalchemy_uri_decrypted)
def custom_password_store(uri):
return 'password_store_test'
models.custom_password_store = custom_password_store
conn = sqla.engine.url.make_url(database.sqlalchemy_uri_decrypted)
if conn_pre.password:
assert conn.password == 'password_store_test'
assert conn.password != conn_pre.password
# Disable for password store for later tests
models.custom_password_store = None
def run_sql(self, sql, client_id=None, user_name=None, raise_on_error=False):
if user_name:
self.logout()
self.login(username=(user_name if user_name else 'admin'))
dbid = get_main_database(db.session).id
resp = self.get_json_resp(
'/superset/sql_json/',
raise_on_error=False,
data=dict(database_id=dbid, sql=sql, select_as_create_as=False,
client_id=client_id),
)
if raise_on_error and 'error' in resp:
raise Exception('run_sql failed')
return resp
def test_table_metadata(self):
maindb = get_main_database(db.session)
backend = maindb.backend
data = self.get_json_resp('/superset/table/{}/ab_user/null/'.format(
maindb.id))
self.assertEqual(data['name'], 'ab_user')
assert len(data['columns']) > 5
assert data.get('selectStar').startswith('SELECT')
# Engine specific tests
if backend in ('mysql', 'postgresql'):
self.assertEqual(data.get('primaryKey').get('type'), 'pk')
self.assertEqual(
data.get('primaryKey').get('column_names')[0], 'id')
self.assertEqual(len(data.get('foreignKeys')), 2)
if backend == 'mysql':
self.assertEqual(len(data.get('indexes')), 7)
elif backend == 'postgresql':
self.assertEqual(len(data.get('indexes')), 5)
def test_run_sync_query_cta(self):
main_db = get_main_database(db.session)
db_id = main_db.id
eng = main_db.get_sqla_engine()
tmp_table_name = 'tmp_async_22'
self.drop_table_if_exists(tmp_table_name, main_db)
perm_name = 'can_sql_json'
sql_where = (
"SELECT name FROM ab_permission WHERE name='{}'".format(perm_name))
result2 = self.run_sql(
db_id, sql_where, '2', tmp_table=tmp_table_name, cta='true')
self.assertEqual(QueryStatus.SUCCESS, result2['query']['state'])
self.assertEqual([], result2['data'])
self.assertEqual([], result2['columns'])
query2 = self.get_query_by_id(result2['query']['serverId'])
# Check the data in the tmp table.
df2 = pd.read_sql_query(sql=query2.select_sql, con=eng)
data2 = df2.to_dict(orient='records')
self.assertEqual([{'name': perm_name}], data2)
def test_select_star(self):
main_db = get_main_database(db.session)
table_name = 'bart_lines'
sql = main_db.select_star(table_name,
show_cols=False,
latest_partition=False)
expected = textwrap.dedent("""\
SELECT *
FROM {table_name}
LIMIT 100""".format(**locals()))
assert sql.startswith(expected)
sql = main_db.select_star(table_name,
show_cols=True,
latest_partition=False)
expected = textwrap.dedent("""\
SELECT color,
name,
path_json,
polyline
FROM bart_lines
LIMIT 100""".format(**locals()))
assert sql.startswith(expected)
def test_run_sync_query_dont_exist(self):
main_db = get_main_database(db.session)
db_id = main_db.id
sql_dont_exist = 'SELECT name FROM table_dont_exist'
result1 = self.run_sql(db_id, sql_dont_exist, '1', cta='true')
self.assertTrue('error' in result1)
def test_template_kwarg(self):
maindb = get_main_database(db.session)
s = '{{ foo }}'
tp = jinja_context.get_template_processor(database=maindb)
rendered = tp.process_template(s, foo='bar')
self.assertEqual('bar', rendered)
def test_process_template(self):
maindb = get_main_database(db.session)
sql = "SELECT '{{ datetime(2017, 1, 1).isoformat() }}'"
tp = jinja_context.get_template_processor(database=maindb)
rendered = tp.process_template(sql)
self.assertEqual("SELECT '2017-01-01T00:00:00'", rendered)
def test_extra_table_metadata(self):
self.login('admin')
dbid = get_main_database(db.session).id
self.get_json_resp('/superset/extra_table_metadata/{dbid}/'
'ab_permission_view/panoramix/'.format(**locals()))
def load_test_users_run():
"""
Loads admin, alpha, and gamma user for testing purposes
Syncs permissions for those users/roles
"""
if config.get('TESTING'):
security_manager.sync_role_definitions()
gamma_sqllab_role = security_manager.add_role('gamma_sqllab')
for perm in security_manager.find_role('Gamma').permissions:
security_manager.add_permission_role(gamma_sqllab_role, perm)
utils.get_or_create_main_db()
db_perm = utils.get_main_database(security_manager.get_session).perm
security_manager.merge_perm('database_access', db_perm)
db_pvm = security_manager.find_permission_view_menu(
view_menu_name=db_perm, permission_name='database_access')
gamma_sqllab_role.permissions.append(db_pvm)
for perm in security_manager.find_role('sql_lab').permissions:
security_manager.add_permission_role(gamma_sqllab_role, perm)
admin = security_manager.find_user('admin')
if not admin:
security_manager.add_user('admin',
'admin',
' user',
'*****@*****.**',
security_manager.find_role('Admin'),
password='******')
gamma = security_manager.find_user('gamma')
if not gamma:
security_manager.add_user('gamma',
'gamma',
'user',
'*****@*****.**',
security_manager.find_role('Gamma'),
password='******')
gamma2 = security_manager.find_user('gamma2')
if not gamma2:
security_manager.add_user('gamma2',
'gamma2',
'user',
'*****@*****.**',
security_manager.find_role('Gamma'),
password='******')
gamma_sqllab_user = security_manager.find_user('gamma_sqllab')
if not gamma_sqllab_user:
security_manager.add_user('gamma_sqllab',
'gamma_sqllab',
'user',
'*****@*****.**',
gamma_sqllab_role,
password='******')
alpha = security_manager.find_user('alpha')
if not alpha:
security_manager.add_user('alpha',
'alpha',
'user',
'*****@*****.**',
security_manager.find_role('Alpha'),
password='******')
security_manager.get_session.commit()
