426 'tx_relation_actions_eid_to_idx': ('tx_relation_actions', 'eid_to'), |
426 'tx_relation_actions_eid_to_idx': ('tx_relation_actions', 'eid_to'), |
427 'tx_relation_actions_tx_uuid_idx': ('tx_relation_actions', 'tx_uuid'), |
427 'tx_relation_actions_tx_uuid_idx': ('tx_relation_actions', 'tx_uuid'), |
428 } |
428 } |
429 |
429 |
430 |
430 |
431 def check_indexes(cnx): |
431 def expected_indexes(cnx): |
432 """Check indexes of a system database: output missing expected indexes as well as unexpected ones. |
432 """Return a dictionary describing indexes expected by the schema {index name: (table, column)}. |
433 |
433 |
434 Return 0 if there is no differences, else 1. |
434 This doesn't include primary key indexes. |
435 """ |
435 """ |
436 source = cnx.repo.system_source |
436 source = cnx.repo.system_source |
437 dbh = source.dbhelper |
437 dbh = source.dbhelper |
438 schema = cnx.repo.schema |
438 schema = cnx.repo.schema |
439 schema_indices = SYSTEM_INDICES.copy() |
439 schema_indexes = SYSTEM_INDEXES.copy() |
440 if source.dbdriver == 'postgres': |
440 if source.dbdriver == 'postgres': |
441 schema_indices.update({'appears_words_idx': ('appears', 'words')}) |
441 schema_indexes.update({'appears_words_idx': ('appears', 'words')}) |
442 index_filter = lambda idx: not (idx.startswith('pg_') or idx.endswith('_pkey')) |
|
443 else: |
442 else: |
444 schema_indices.update({'appears_uid': ('appears', 'uid'), |
443 schema_indexes.update({'appears_uid': ('appears', 'uid'), |
445 'appears_word_id': ('appears', 'word_id')}) |
444 'appears_word_id': ('appears', 'word_id')}) |
446 index_filter = lambda idx: not idx.startswith('sqlite_') |
|
447 db_indices = set(idx for idx in dbh.list_indices(cnx.cnxset.cu) |
|
448 if index_filter(idx)) |
|
449 for rschema in schema.relations(): |
445 for rschema in schema.relations(): |
450 if rschema.rule or rschema in PURE_VIRTUAL_RTYPES: |
446 if rschema.rule or rschema in PURE_VIRTUAL_RTYPES: |
451 continue # computed relation |
447 continue # computed relation |
452 if rschema.final or rschema.inlined: |
448 if rschema.final or rschema.inlined: |
453 for rdef in rschema.rdefs.values(): |
449 for rdef in rschema.rdefs.values(): |
454 table = 'cw_{0}'.format(rdef.subject) |
450 table = 'cw_{0}'.format(rdef.subject) |
455 column = 'cw_{0}'.format(rdef.rtype) |
451 column = 'cw_{0}'.format(rdef.rtype) |
456 if any(isinstance(cstr, UniqueConstraint) for cstr in rdef.constraints): |
452 if any(isinstance(cstr, UniqueConstraint) for cstr in rdef.constraints): |
457 schema_indices[dbh._index_name(table, column, unique=True)] = ( |
453 schema_indexes[dbh._index_name(table, column, unique=True)] = ( |
458 table, [column]) |
454 table, [column]) |
459 if rschema.inlined or rdef.indexed: |
455 if rschema.inlined or rdef.indexed: |
460 schema_indices[dbh._index_name(table, column)] = (table, [column]) |
456 schema_indexes[dbh._index_name(table, column)] = (table, [column]) |
461 else: |
457 else: |
462 table = '{0}_relation'.format(rschema) |
458 table = '{0}_relation'.format(rschema) |
463 if source.dbdriver == 'postgres': |
459 if source.dbdriver == 'postgres': |
464 # index built after the primary key constraint |
460 # index built after the primary key constraint |
465 schema_indices[build_index_name(table, ['eid_from', 'eid_to'], 'key_')] = ( |
461 schema_indexes[build_index_name(table, ['eid_from', 'eid_to'], 'key_')] = ( |
466 table, ['eid_from', 'eid_to']) |
462 table, ['eid_from', 'eid_to']) |
467 schema_indices[build_index_name(table, ['eid_from'], 'idx_')] = ( |
463 schema_indexes[build_index_name(table, ['eid_from'], 'idx_')] = ( |
468 table, ['eid_from']) |
464 table, ['eid_from']) |
469 schema_indices[build_index_name(table, ['eid_to'], 'idx_')] = ( |
465 schema_indexes[build_index_name(table, ['eid_to'], 'idx_')] = ( |
470 table, ['eid_to']) |
466 table, ['eid_to']) |
471 for eschema in schema.entities(): |
467 for eschema in schema.entities(): |
472 if eschema.final: |
468 if eschema.final: |
473 continue |
469 continue |
474 table = 'cw_{0}'.format(eschema) |
470 table = 'cw_{0}'.format(eschema) |
475 for columns, index_name in iter_unique_index_names(eschema): |
471 for columns, index_name in iter_unique_index_names(eschema): |
476 schema_indices[index_name] = (table, columns) |
472 schema_indexes[index_name] = (table, columns) |
477 |
473 |
478 missing_indices = set(schema_indices) - db_indices |
474 return schema_indexes |
479 if missing_indices: |
475 |
480 print(underline_title('Missing indices')) |
476 |
|
477 def database_indexes(cnx): |
|
478 """Return a set of indexes found in the database, excluding primary key indexes.""" |
|
479 source = cnx.repo.system_source |
|
480 dbh = source.dbhelper |
|
481 if source.dbdriver == 'postgres': |
|
482 |
|
483 def index_filter(idx): |
|
484 return not (idx.startswith('pg_') or idx.endswith('_pkey')) |
|
485 else: |
|
486 |
|
487 def index_filter(idx): |
|
488 return not idx.startswith('sqlite_') |
|
489 |
|
490 return set(idx for idx in dbh.list_indices(cnx.cnxset.cu) |
|
491 if index_filter(idx)) |
|
492 |
|
493 |
|
494 def check_indexes(cnx): |
|
495 """Check indexes of a system database: output missing expected indexes as well as unexpected ones. |
|
496 |
|
497 Return 0 if there is no differences, else 1. |
|
498 """ |
|
499 schema_indexes = expected_indexes(cnx) |
|
500 db_indexes = database_indexes(cnx) |
|
501 |
|
502 missing_indexes = set(schema_indexes) - db_indexes |
|
503 if missing_indexes: |
|
504 print(underline_title('Missing indexes')) |
481 print('index expected by the schema but not found in the database:\n') |
505 print('index expected by the schema but not found in the database:\n') |
482 missing = ['{0} ON {1[0]} {1[1]}'.format(idx, schema_indices[idx]) |
506 missing = ['{0} ON {1[0]} {1[1]}'.format(idx, schema_indexes[idx]) |
483 for idx in missing_indices] |
507 for idx in missing_indexes] |
484 print('\n'.join(sorted(missing))) |
508 print('\n'.join(sorted(missing))) |
485 print() |
509 print() |
486 status = 1 |
510 status = 1 |
487 additional_indices = db_indices - set(schema_indices) |
511 |
488 if additional_indices: |
512 additional_indexes = db_indexes - set(schema_indexes) |
489 print(underline_title('Additional indices')) |
513 if additional_indexes: |
|
514 print(underline_title('Additional indexes')) |
490 print('index in the database but not expected by the schema:\n') |
515 print('index in the database but not expected by the schema:\n') |
491 print('\n'.join(sorted(additional_indices))) |
516 print('\n'.join(sorted(additional_indexes))) |
492 print() |
517 print() |
493 status = 1 |
518 status = 1 |
494 if not (missing_indices or additional_indices): |
519 |
|
520 if not (missing_indexes or additional_indexes): |
495 print('Everything is Ok') |
521 print('Everything is Ok') |
496 status = 0 |
522 status = 0 |
497 |
523 |
498 return status |
524 return status |