def get_indexes(self, table, schema=None): schema = schema or 'main' query = f'SELECT name, sql FROM "{schema}".sqlite_master WHERE tbl_name = ? AND type = ? ORDER BY name' cursor = self.execute_sql(query, (table, 'index')) index_to_sql = dict(cursor.fetchall()) # Determine which indexes have a unique constraint. unique_indexes = set() cursor = self.execute_sql(f'PRAGMA "{schema}".index_list("{table}")') for row in cursor.fetchall(): name = row[1] is_unique = int(row[2]) == 1 if is_unique: unique_indexes.add(name) # Retrieve the indexed columns. index_columns = {} for index_name in sorted(index_to_sql): cursor = self.execute_sql( f'PRAGMA "{schema}".index_info("{index_name}")') index_columns[index_name] = [row[2] for row in cursor.fetchall()] return [ IndexMetadata(name, index_to_sql[name], index_columns[name], name in unique_indexes, table) for name in sorted(index_to_sql) ]
def get_all_indexes(self): cursor = self.query( 'SELECT name, sql FROM sqlite_master ' 'WHERE type = ? ORDER BY name', ('index',)) return [IndexMetadata(row[0], row[1], None, None, None) for row in cursor.fetchall()]
async def get_indexes(self, table, schema=None): cursor = await self.execute_sql('SHOW INDEX FROM `%s`' % table) unique = set() indexes = {} for row in cursor.fetchall(): if not row[1]: unique.add(row[2]) indexes.setdefault(row[2], []) indexes[row[2]].append(row[4]) return [ IndexMetadata(name, None, indexes[name], name in unique, table) for name in indexes ]
def get_all_indexes(self): cursor = self.query( 'SELECT name, sql FROM sqlite_master ' 'WHERE type = ? ORDER BY name', ('index', )) ''' return [ IndexMetadata('TABLE_name', 'sql_sentence',none,none,none), IndexMetadata('TABLE_name', 'sql_sentence',none,none,none), ... IndexMetadata('TABLE_name', 'sql_sentence',none,none,none) ] ''' return [ IndexMetadata(row[0], row[1], None, None, None) for row in cursor.fetchall() ]
def get_indexes(self, table, schema='public'): query = """ SELECT i.relname, idxs.indexdef, idx.indisunique, array_to_string(array_agg(cols.attname), ',') FROM pg_catalog.pg_class AS t INNER JOIN pg_catalog.pg_index AS idx ON t.oid = idx.indrelid INNER JOIN pg_catalog.pg_class AS i ON idx.indexrelid = i.oid INNER JOIN pg_catalog.pg_indexes AS idxs ON (idxs.tablename = t.relname AND idxs.indexname = i.relname) LEFT OUTER JOIN pg_catalog.pg_attribute AS cols ON (cols.attrelid = t.oid AND cols.attnum = ANY(idx.indkey)) WHERE t.relname = %s AND t.relkind = %s AND idxs.schemaname = %s GROUP BY i.relname, idxs.indexdef, idx.indisunique ORDER BY idx.indisunique DESC, i.relname;""" cursor = yield self.execute_sql(query, (table, 'r', schema)) raise gen.Return([IndexMetadata(row[0], row[1], row[3].split(','), row[2], table) for row in cursor.fetchall()])
def get_indexes(self, table): command = '''USE DATABASE ''' + self.database + ''' ;''' command = command + ''' DESC ''' command = command + table command = command + ''' ;''' code = exe(command)[0] code.pop() indexes = list([]) for line in code: line = line.strip('\n') values = line.split(' ') if (values[len(values) - 2] == 'Not'): name = values[0].strip(':') idxname = table + "_" + name unique = False if (values[len(values) - 3] == 'Indexed,'): idx = True else: idx = False else: name = values[0].strip(':') idxname = table + "_" + name unique = True if (values[len(values) - 2] == 'Indexed,'): idx = True else: idx = False if idx: sql = '''CREATE INDEX ''' + table + ''' ( ''' + name + ''' ) ''' index = IndexMetadata(name=idxname, sql=sql, columns=[name], unique=unique, table=table) indexes.append(index) return indexes
def get_indexes(self, table, schema='dbo'): return [ IndexMetadata(name, "", name, True, table) for name, *_, con in self.md_cache(table, schema) if con == 'UNIQUE' or con == 'PRIMARY KEY' ]