Project

General

Profile

0001-sql-add-indexes-on-formdata-id-in-evolution-tables-2.patch

Frédéric Péters, 03 March 2018 03:25 PM

Download (5.34 KB)

View differences:

Subject: [PATCH] sql: add indexes on formdata id in evolution tables (#22234)

 tests/test_sql.py | 51 +++++++++++++++++++++++++++++++++++++++++++++++++++
 wcs/sql.py        | 28 +++++++++++++++++++++++++++-
 2 files changed, 78 insertions(+), 1 deletion(-)
tests/test_sql.py
666 666
    conn, cur = sql.get_connection_and_cursor()
667 667
    assert table_exists(cur, test_formdef.table_name)
668 668

  
669
@postgresql
670
def test_sql_indexes():
671
    test_formdef = FormDef()
672
    test_formdef.name = 'tests indexes'
673
    test_formdef.fields = []
674
    test_formdef.store()
675
    data_class = test_formdef.data_class(mode='sql')
676
    assert data_class.count() == 0
677
    conn, cur = sql.get_connection_and_cursor()
678
    assert index_exists(cur, test_formdef.table_name + '_evolutions_fid')
679
    conn.commit()
680
    cur.close()
681

  
682
    data_class.wipe(drop=True)
683
    conn, cur = sql.get_connection_and_cursor()
684
    assert not index_exists(cur, test_formdef.table_name + '_evolutions_fid')
685
    conn.commit()
686
    cur.close()
687

  
669 688
@postgresql
670 689
def test_sql_table_select():
671 690
    test_formdef = FormDef()
......
924 943
                      AND column_name = %s''', (table_name, column_name))
925 944
    return bool(cur.fetchone()[0] == 1)
926 945

  
946
def index_exists(cur, index_name):
947
    cur.execute('''SELECT COUNT(*) FROM pg_indexes
948
                    WHERE schemaname = 'public'
949
                      AND indexname = %s''', (index_name,))
950
    return bool(cur.fetchone()[0] == 1)
951

  
927 952
@postgresql
928 953
def test_sql_level():
929 954
    conn, cur = sql.get_connection_and_cursor()
......
1104 1129
    conn.commit()
1105 1130
    cur.close()
1106 1131

  
1132
@postgresql
1133
def test_migration_24_evolution_index():
1134
    formdef = FormDef()
1135
    formdef.name = 'tests migration 24'
1136
    formdef.fields = []
1137
    formdef.store()
1138

  
1139
    conn, cur = sql.get_connection_and_cursor()
1140
    cur.execute('DROP INDEX %s_evolutions_fid' % formdef.table_name)
1141
    cur.execute('UPDATE wcs_meta SET value = 23 WHERE key = %s', ('sql_level',))
1142
    conn.commit()
1143
    cur.close()
1144

  
1145
    conn, cur = sql.get_connection_and_cursor()
1146
    assert not index_exists(cur, formdef.table_name + '_evolutions_fid')
1147
    conn.commit()
1148
    cur.close()
1149

  
1150
    sql.migrate()
1151

  
1152
    conn, cur = sql.get_connection_and_cursor()
1153
    assert index_exists(cur, formdef.table_name + '_evolutions_fid')
1154

  
1155
    conn.commit()
1156
    cur.close()
1157

  
1107 1158
def drop_formdef_tables():
1108 1159
    conn, cur = sql.get_connection_and_cursor()
1109 1160
    cur.execute('''SELECT table_name FROM information_schema.tables''')
wcs/sql.py
375 375
                                    parts bytea,
376 376
                                    formdata_id integer REFERENCES %s (id) ON DELETE CASCADE)''' % (
377 377
                                    table_name, table_name))
378
        do_formdef_indexes(formdef, created=True, conn=conn, cur=cur)
379

  
378 380
    cur.execute('''SELECT column_name FROM information_schema.columns
379 381
                    WHERE table_schema = 'public'
380 382
                      AND table_name = %s''', (table_name,))
......
482 484

  
483 485
    return actions
484 486

  
487
def do_formdef_indexes(formdef, created, conn, cur, concurrently=False):
488
    table_name = get_formdef_table_name(formdef)
489
    evolutions_table_name = table_name + '_evolutions'
490
    existing_indexes = set()
491
    if not created:
492
        cur.execute('''SELECT indexname
493
                         FROM pg_indexes
494
                        WHERE schemaname = 'public'
495
                          AND tablename IN (%s, %s)''', (table_name, evolutions_table_name))
496
        existing_indexes = set([x[0] for x in cur.fetchall()])
497

  
498
    create_index = 'CREATE INDEX'
499
    if concurrently:
500
        create_index = 'CREATE INDEX CONCURRENTLY'
501

  
502
    if not evolutions_table_name + '_fid' in existing_indexes:
503
        cur.execute('''%s %s_fid ON %s (formdata_id)''' % (
504
            create_index, evolutions_table_name, evolutions_table_name))
505

  
485 506
@guard_postgres
486 507
def do_user_table():
487 508
    conn, cur = get_connection_and_cursor()
......
1955 1976
    return result
1956 1977

  
1957 1978

  
1958
SQL_LEVEL = 23
1979
SQL_LEVEL = 24
1959 1980

  
1960 1981
def migrate_global_views(conn, cur):
1961 1982
    cur.execute('''SELECT COUNT(*) FROM information_schema.tables
......
2034 2055
        for formdef in FormDef.select():
2035 2056
            for formdata in formdef.data_class().select():
2036 2057
                formdata.store()
2058
    if sql_level < 24:
2059
        from wcs.formdef import FormDef
2060
        # 24: add index on evolution(formdata_id)
2061
        for formdef in FormDef.select():
2062
            do_formdef_indexes(formdef, created=False, conn=conn, cur=cur)
2037 2063

  
2038 2064
    cur.execute('''UPDATE wcs_meta SET value = %s WHERE key = %s''', (
2039 2065
        str(SQL_LEVEL), 'sql_level'))
2040
-