(session: SessionDep, current_user: CurrentUser, id: int, data: TableObj)
| 294 | |
| 295 | |
| 296 | def preview(session: SessionDep, current_user: CurrentUser, id: int, data: TableObj): |
| 297 | ds = session.query(CoreDatasource).filter(CoreDatasource.id == id).first() |
| 298 | # check_status(session, ds, True) |
| 299 | |
| 300 | # ignore data's fields param, query fields from database |
| 301 | if not data.table.id: |
| 302 | return {"fields": [], "data": [], "sql": ''} |
| 303 | |
| 304 | fields = session.query(CoreField).filter(CoreField.table_id == data.table.id).order_by( |
| 305 | CoreField.field_index.asc()).all() |
| 306 | |
| 307 | if fields is None or len(fields) == 0: |
| 308 | return {"fields": [], "data": [], "sql": ''} |
| 309 | |
| 310 | where = '' |
| 311 | f_list = [f for f in fields if f.checked] |
| 312 | if is_normal_user(current_user): |
| 313 | # column is checked, and, column permission for data.fields |
| 314 | contain_rules = session.query(DsRules).all() |
| 315 | f_list = get_column_permission_fields(session=session, current_user=current_user, table=data.table, |
| 316 | fields=f_list, contain_rules=contain_rules) |
| 317 | |
| 318 | # row permission tree |
| 319 | where_str = '' |
| 320 | filter_mapping = get_row_permission_filters(session=session, current_user=current_user, ds=ds, tables=None, |
| 321 | single_table=data.table) |
| 322 | if filter_mapping: |
| 323 | mapping_dict = filter_mapping[0] |
| 324 | where_str = mapping_dict.get('filter') |
| 325 | where = (' where ' + where_str) if where_str is not None and where_str != '' else '' |
| 326 | |
| 327 | fields = [f.field_name for f in f_list] |
| 328 | if fields is None or len(fields) == 0: |
| 329 | return {"fields": [], "data": [], "sql": ''} |
| 330 | |
| 331 | table = session.query(CoreTable).filter(CoreTable.id == data.table.id).first() |
| 332 | conf = DatasourceConf(**json.loads(aes_decrypt(ds.configuration))) if ds.type != "excel" else get_engine_config() |
| 333 | sql: str = "" |
| 334 | if ds.type == "mysql" or ds.type == "doris" or ds.type == "starrocks" or ds.type == "hive": |
| 335 | sql = f"""SELECT `{"`, `".join(fields)}` FROM `{table.table_name}` |
| 336 | {where} |
| 337 | LIMIT 100""" |
| 338 | elif ds.type == "sqlServer": |
| 339 | sql = f"""SELECT TOP 100 [{"], [".join(fields)}] FROM [{conf.dbSchema}].[{table.table_name}] |
| 340 | {where} |
| 341 | """ |
| 342 | elif ds.type == "pg" or ds.type == "excel" or ds.type == "redshift" or ds.type == "kingbase": |
| 343 | sql = f"""SELECT "{'", "'.join(fields)}" FROM "{conf.dbSchema}"."{table.table_name}" |
| 344 | {where} |
| 345 | LIMIT 100""" |
| 346 | elif ds.type == "oracle": |
| 347 | # sql = f"""SELECT "{'", "'.join(fields)}" FROM "{conf.dbSchema}"."{data.table.table_name}" |
| 348 | # {where} |
| 349 | # ORDER BY "{fields[0]}" |
| 350 | # OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY""" |
| 351 | sql = f"""SELECT * FROM |
| 352 | (SELECT "{'", "'.join(fields)}" FROM "{conf.dbSchema}"."{table.table_name}" |
| 353 | {where} |
no test coverage detected