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()