| 1069 |
1069 |
def do_formdef_indexes(formdef, created, conn, cur, concurrently=False):
|
| 1070 |
1070 |
table_name = get_formdef_table_name(formdef)
|
| 1071 |
1071 |
evolutions_table_name = table_name + '_evolutions'
|
| 1072 |
|
existing_indexes = set()
|
| 1073 |
|
if not created:
|
| 1074 |
|
cur.execute(
|
| 1075 |
|
'''SELECT pci.relname, array_length(pi.indkey, 1)
|
| 1076 |
|
FROM pg_index pi
|
| 1077 |
|
JOIN pg_class pci on pci.oid = pi.indexrelid
|
| 1078 |
|
JOIN pg_class pct on pct.oid = pi.indrelid
|
| 1079 |
|
WHERE pci.relnamespace = 'public'::regnamespace
|
| 1080 |
|
AND pct.relname in (%s, %s)''',
|
| 1081 |
|
(table_name, evolutions_table_name),
|
| 1082 |
|
)
|
| 1083 |
|
existing_indexes = {x[0]: x[1] for x in cur.fetchall()}
|
| 1084 |
1072 |
|
| 1085 |
|
create_index = 'CREATE INDEX'
|
|
1073 |
create_index = 'CREATE INDEX IF NOT EXISTS'
|
| 1086 |
1074 |
if concurrently:
|
| 1087 |
|
create_index = 'CREATE INDEX CONCURRENTLY'
|
|
1075 |
create_index = 'CREATE INDEX CONCURRENTLY IF NOT EXISTS'
|
| 1088 |
1076 |
|
| 1089 |
|
fid_idx = evolutions_table_name + '_fid'
|
|
1077 |
cur.execute(
|
|
1078 |
'''%s %s_fid ON %s (formdata_id, id)''' % (create_index, evolutions_table_name, evolutions_table_name)
|
|
1079 |
)
|
| 1090 |
1080 |
|
| 1091 |
|
if fid_idx in existing_indexes and existing_indexes[fid_idx] != 2:
|
| 1092 |
|
# The index was previously (< #67757) on formdata_id only, so
|
| 1093 |
|
# search for it with only one key and recreate it if needed.
|
| 1094 |
|
if concurrently:
|
| 1095 |
|
cur.execute('DROP INDEX CONCURRENTLY %s' % fid_idx)
|
| 1096 |
|
else:
|
| 1097 |
|
cur.execute('DROP INDEX %s' % fid_idx)
|
| 1098 |
|
cur.execute(
|
| 1099 |
|
'''%s %s_fid ON %s (formdata_id, id)'''
|
| 1100 |
|
% (create_index, evolutions_table_name, evolutions_table_name)
|
| 1101 |
|
)
|
| 1102 |
|
elif fid_idx not in existing_indexes:
|
|
1081 |
for attr in ('receipt_time', 'anonymised', 'user_id', 'status'):
|
| 1103 |
1082 |
cur.execute(
|
| 1104 |
|
'''%s %s_fid ON %s (formdata_id, id)'''
|
| 1105 |
|
% (create_index, evolutions_table_name, evolutions_table_name)
|
|
1083 |
'%(create_index)s %(table_name)s_%(attr)s_idx ON %(table_name)s (%(attr)s)'
|
|
1084 |
% {'create_index': create_index, 'table_name': table_name, 'attr': attr}
|
| 1106 |
1085 |
)
|
| 1107 |
|
|
| 1108 |
|
for attr in ('receipt_time', 'anonymised', 'user_id', 'status'):
|
| 1109 |
|
if table_name + '_' + attr + '_idx' not in existing_indexes:
|
| 1110 |
|
cur.execute(
|
| 1111 |
|
'%(create_index)s %(table_name)s_%(attr)s_idx ON %(table_name)s (%(attr)s)'
|
| 1112 |
|
% {'create_index': create_index, 'table_name': table_name, 'attr': attr}
|
| 1113 |
|
)
|
| 1114 |
1086 |
for attr in ('concerned_roles_array', 'actions_roles_array'):
|
| 1115 |
1087 |
idx_name = 'idx_' + attr + '_' + table_name
|
| 1116 |
1088 |
cur.execute(
|
| 1117 |
|
'%(create_index)s IF NOT EXISTS %(idx_name)s ON %(table_name)s USING gin (%(attr)s)'
|
|
1089 |
'%(create_index)s %(idx_name)s ON %(table_name)s USING gin (%(attr)s)'
|
| 1118 |
1090 |
% {'create_index': create_index, 'table_name': table_name, 'idx_name': idx_name, 'attr': attr}
|
| 1119 |
1091 |
)
|
| 1120 |
1092 |
|
| ... | ... | |
| 1222 |
1194 |
for field in existing_fields - needed_fields:
|
| 1223 |
1195 |
cur.execute('''ALTER TABLE %s DROP COLUMN %s''' % (table_name, field))
|
| 1224 |
1196 |
|
| 1225 |
|
conn.commit()
|
| 1226 |
|
|
| 1227 |
|
try:
|
| 1228 |
|
cur.execute('CREATE INDEX users_name_idx ON users (name)')
|
| 1229 |
|
conn.commit()
|
| 1230 |
|
except psycopg2.ProgrammingError:
|
| 1231 |
|
conn.rollback()
|
| 1232 |
|
|
| 1233 |
|
try:
|
| 1234 |
|
cur.execute('CREATE INDEX users_name_identifiers_idx ON users USING gin(name_identifiers)')
|
| 1235 |
|
conn.commit()
|
| 1236 |
|
except psycopg2.ProgrammingError:
|
| 1237 |
|
conn.rollback()
|
|
1197 |
# create indexes
|
|
1198 |
cur.execute('CREATE INDEX IF NOT EXISTS users_name_idx ON users (name)')
|
|
1199 |
cur.execute('CREATE INDEX IF NOT EXISTS users_name_identifiers_idx ON users USING gin(name_identifiers)')
|
| 1238 |
1200 |
|
|
1201 |
conn.commit()
|
| 1239 |
1202 |
cur.close()
|
| 1240 |
1203 |
|
| 1241 |
1204 |
|
| ... | ... | |
| 1448 |
1411 |
|
| 1449 |
1412 |
# add indexes
|
| 1450 |
1413 |
cur.execute(
|
| 1451 |
|
'''SELECT indexname
|
| 1452 |
|
FROM pg_indexes
|
| 1453 |
|
WHERE schemaname = 'public'
|
| 1454 |
|
AND tablename = %s''',
|
| 1455 |
|
(table_name,),
|
|
1414 |
'''CREATE INDEX IF NOT EXISTS %s_formdef_type_id ON %s(formdef_type, formdef_id)'''
|
|
1415 |
% (table_name, table_name)
|
| 1456 |
1416 |
)
|
| 1457 |
|
existing_indexes = {x[0] for x in cur.fetchall()}
|
| 1458 |
|
if ('%s_formdef_type_id' % table_name) not in existing_indexes:
|
| 1459 |
|
cur.execute(
|
| 1460 |
|
'''CREATE INDEX %s_formdef_type_id ON %s(formdef_type, formdef_id)''' % (table_name, table_name)
|
| 1461 |
|
)
|
| 1462 |
1417 |
|
| 1463 |
1418 |
conn.commit()
|
| 1464 |
1419 |
cur.close()
|
| ... | ... | |
| 1476 |
1431 |
)
|
| 1477 |
1432 |
if cur.fetchone()[0] == 0:
|
| 1478 |
1433 |
cur.execute(
|
| 1479 |
|
'''CREATE TABLE %s (id SERIAL,
|
|
1434 |
'''CREATE TABLE %s (id SERIAL PRIMARY KEY,
|
| 1480 |
1435 |
object_type VARCHAR,
|
| 1481 |
1436 |
object_id VARCHAR,
|
| 1482 |
1437 |
timestamp TIMESTAMP WITH TIME ZONE,
|
| ... | ... | |
| 1506 |
1461 |
for field in existing_fields - needed_fields:
|
| 1507 |
1462 |
cur.execute('''ALTER TABLE %s DROP COLUMN %s''' % (table_name, field))
|
| 1508 |
1463 |
|
| 1509 |
|
# add indexes
|
|
1464 |
# add index
|
| 1510 |
1465 |
cur.execute(
|
| 1511 |
|
'''SELECT indexname
|
| 1512 |
|
FROM pg_indexes
|
| 1513 |
|
WHERE schemaname = 'public'
|
| 1514 |
|
AND tablename = %s''',
|
| 1515 |
|
(table_name,),
|
|
1466 |
'''CREATE INDEX IF NOT EXISTS %s_object_by_date ON %s(object_type, object_id, timestamp DESC)'''
|
|
1467 |
% (table_name, table_name)
|
| 1516 |
1468 |
)
|
| 1517 |
|
existing_indexes = {x[0] for x in cur.fetchall()}
|
| 1518 |
|
if ('%s_pkey' % table_name) not in existing_indexes:
|
| 1519 |
|
cur.execute('''ALTER TABLE %s ADD PRIMARY KEY (id)''' % table_name)
|
| 1520 |
|
if ('%s_object_by_date' % table_name) not in existing_indexes:
|
| 1521 |
|
cur.execute(
|
| 1522 |
|
'''CREATE INDEX %s_object_by_date ON %s(object_type, object_id, timestamp DESC)'''
|
| 1523 |
|
% (table_name, table_name)
|
| 1524 |
|
)
|
| 1525 |
1469 |
conn.commit()
|
| 1526 |
1470 |
cur.close()
|
| 1527 |
1471 |
|
| ... | ... | |
| 1577 |
1521 |
for field in existing_fields - needed_fields:
|
| 1578 |
1522 |
cur.execute('''ALTER TABLE %s DROP COLUMN %s''' % (table_name, field))
|
| 1579 |
1523 |
|
| 1580 |
|
create_index = 'CREATE INDEX'
|
|
1524 |
create_index = 'CREATE INDEX IF NOT EXISTS'
|
| 1581 |
1525 |
if concurrently:
|
| 1582 |
|
create_index = 'CREATE INDEX CONCURRENTLY'
|
|
1526 |
create_index = 'CREATE INDEX CONCURRENTLY IF NOT EXISTS'
|
| 1583 |
1527 |
|
| 1584 |
1528 |
# build indexes
|
| 1585 |
|
existing_indexes = set()
|
| 1586 |
|
cur.execute(
|
| 1587 |
|
'''SELECT indexname
|
| 1588 |
|
FROM pg_indexes
|
| 1589 |
|
WHERE schemaname = 'public'
|
| 1590 |
|
AND tablename = %s''',
|
| 1591 |
|
(table_name,),
|
| 1592 |
|
)
|
| 1593 |
|
existing_indexes = {x[0] for x in cur.fetchall()}
|
| 1594 |
|
|
| 1595 |
1529 |
for attr in ('formdef_id', 'workflow_id'):
|
| 1596 |
|
if table_name + '_' + attr + '_idx' not in existing_indexes:
|
| 1597 |
|
cur.execute(
|
| 1598 |
|
'%(create_index)s %(table_name)s_%(attr)s_idx ON %(table_name)s (%(attr)s)'
|
| 1599 |
|
% {'create_index': create_index, 'table_name': table_name, 'attr': attr}
|
| 1600 |
|
)
|
|
1530 |
cur.execute(
|
|
1531 |
'%(create_index)s %(table_name)s_%(attr)s_idx ON %(table_name)s (%(attr)s)'
|
|
1532 |
% {'create_index': create_index, 'table_name': table_name, 'attr': attr}
|
|
1533 |
)
|
| 1601 |
1534 |
|
| 1602 |
1535 |
conn.commit()
|
| 1603 |
1536 |
cur.close()
|
| 1604 |
|
-
|