Function get_table_sql

backend/apps/db/db_sql.py:36–168

(ds: CoreDatasource, conf: DatasourceConf, db_version: str = '')

def get_table_sql(ds: CoreDatasource, conf: DatasourceConf, db_version: str = ''):
    if equals_ignore_case(ds.type, "mysql"):
        return """
                SELECT 
                    TABLE_NAME, 
                    TABLE_COMMENT
                FROM 
                    information_schema.TABLES
                WHERE 
                    TABLE_SCHEMA = :param
                """, conf.database
    elif equals_ignore_case(ds.type, "sqlServer"):
        return """
                SELECT 
                    TABLE_NAME AS [TABLE_NAME],
                    ISNULL(ep.value, '') AS [TABLE_COMMENT]
                FROM 
                    INFORMATION_SCHEMA.TABLES t
                LEFT JOIN 
                    sys.extended_properties ep 
                    ON ep.major_id = OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME)
                    AND ep.minor_id = 0 
                    AND ep.name = 'MS_Description' 
                WHERE 
                    t.TABLE_TYPE IN ('BASE TABLE', 'VIEW')
                    AND t.TABLE_SCHEMA = :param
                """, conf.dbSchema
    elif equals_ignore_case(ds.type, "pg", "excel"):
        return """
              SELECT c.relname                                       AS TABLE_NAME,
                     COALESCE(COALESCE(d.description, obj_description(c.oid)), '') AS TABLE_COMMENT
              FROM pg_class c
                       LEFT JOIN
                   pg_namespace n ON n.oid = c.relnamespace
                       LEFT JOIN
                   pg_description d ON d.objoid = c.oid AND d.objsubid = 0
              WHERE n.nspname = :param
                AND c.relkind IN ('r', 'v', 'p', 'm')
                AND c.relname NOT LIKE 'pg_%'
                AND c.relname NOT LIKE 'sql_%'
              ORDER BY c.relname \
              """, conf.dbSchema
    elif equals_ignore_case(ds.type, "oracle"):
        return """
                SELECT DISTINCT
                    t.TABLE_NAME AS "TABLE_NAME",
                    NVL(c.COMMENTS, '') AS "TABLE_COMMENT"
                FROM (
                    SELECT TABLE_NAME, 'TABLE' AS OBJECT_TYPE
                    FROM ALL_TABLES
                    WHERE OWNER = :param  
                    UNION ALL
                    SELECT VIEW_NAME AS TABLE_NAME, 'VIEW' AS OBJECT_TYPE
                    FROM ALL_VIEWS
                    WHERE OWNER = :param  
                    UNION ALL
                    SELECT MVIEW_NAME AS TABLE_NAME, 'MATERIALIZED VIEW' AS OBJECT_TYPE
                    FROM ALL_MVIEWS