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'
]
