Пример #1
0
    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()
Пример #2
0
    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()
Пример #3
0
    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()
Пример #4
0
Файл: pg.py Проект: hudl/redash
    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()
Пример #5
0
    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()