Method compute_table_schema

bmtools/tools/db_diag/utils/database.py:229–276

schema: {table_name: [field_name]} :param cursor: :return:

(self)

            return self.pgsql_cost_estimation(sql)

    def compute_table_schema(self):
        """
        schema: {table_name: [field_name]}
        :param cursor:
        :return:
        """

        if self.args.dbtype == 'postgresql':
            # cur_path = os.path.abspath('.')
            # tpath = cur_path + '/sampled_data/'+dbname+'/schema'
            sql = 'SELECT table_name FROM information_schema.tables WHERE table_schema = \'public\';'
            success, res = self.execute_sql(sql)
            #print("======== tables", res)
            if success == 1:                
                tables = res
                schema = {}
                for table_info in tables:
                    table_name = table_info[0]
                    sql = 'SELECT column_name, data_type FROM information_schema.columns WHERE table_name = \'' + table_name + '\';'
                    success, res = self.execute_sql(sql)
                    #print("======== table columns", res)
                    columns = res
                    schema[table_name] = []
                    for col in columns:

                        ''' compute the distinct value ratio of the column
                        
                        if transfer_field_type(col[1], self.args.dbtype) == DataType.VALUE.value:
                            sql = 'SELECT count({}) FROM {};'.format(col[0], table_name)
                            success, res = self.execute_sql(sql)
                            print("======== column rows", res)
                            num = res
                            if num[0][0] != 0:
                                schema[table_name].append(col[0])
                        '''

                        #schema[table_name].append("column {} is of {} type".format(col[0], col[1]))
                        schema[table_name].append("{}".format(col[0]))
                '''
                with open(tpath, 'w') as f:
                    f.write(str(schema))
                '''
                #print(schema)
                return schema

            else:
                logging.error('pgsql_cost_estimation Fails!')
                return 0

    def simulate_index(self, index):
        #table_name = index.table()