Script to repair action ids in tryton db.

repair-after.py

"""
This script attempts to repair the tryton 1.8 database AFTER migrating to 2.6.
"""
import sys
import logging

from sqlalchemy import create_engine
from sqlalchemy.sql import text


log = logging.getLogger(__name__)


db_uri = 'postgresql://tfs@localhost:5432/stockmodules-tryton-2.6'


def fix_action_ids(conn):
    """ All sub actions should have the same id as the base action.

    This includes:
        act_window
        wizard
        report
        url
    """
    fix_act_window_action_ids(conn)
    fix_wizard_action_ids(conn)
    fix_report_action_ids(conn)
    fix_url_action_ids(conn)


def fix_act_window_action_ids(conn):
    #
    # act_window
    #
    log.info('fixing act_window action ids')
    conn.execute(
        'ALTER TABLE dashboard_action DROP CONSTRAINT dashboard_action_act_window_fkey')
    conn.execute(
        'ALTER TABLE ir_action_act_window_view DROP CONSTRAINT ir_action_act_window_view_act_window_fkey')
    action_rows = conn.execute('SELECT ir_action.id AS ir_action_id, '
        'ir_action_act_window.id AS ir_action_act_window_id '
        'FROM ir_action '
        'JOIN ir_action_act_window ON ir_action.id = ir_action_act_window.action '
        'WHERE ir_action.id != ir_action_act_window.id').fetchall()
    for row in action_rows:
        action_id, action_act_window_id = row
        count = conn.execute(
            text('SELECT COUNT(*) FROM ir_action_act_window WHERE id = :new_id'),
            new_id=action_id).fetchone()[0]
        if count > 0:
            raise Exception('Cannot set child id to parent id because there is'
                'another child with that id.')

        conn.execute(text('UPDATE ir_action_act_window SET write_date = create_date, id = :new_id WHERE id = :old_id'),
            new_id=action_id, old_id=action_act_window_id)
        conn.execute(text('UPDATE ir_action_act_window_view SET write_date = create_date, act_window = :new_id WHERE act_window = :old_id'),
            new_id=action_id, old_id=action_act_window_id)
        conn.execute(text('UPDATE dashboard_action SET write_date = create_date, act_window = :new_id WHERE act_window = :old_id'),
            new_id=action_id, old_id=action_act_window_id)
    conn.execute('ALTER TABLE dashboard_action ADD CONSTRAINT dashboard_action_act_window_fkey '
        'FOREIGN KEY (act_window) REFERENCES ir_action_act_window (id) ON DELETE RESTRICT')
    conn.execute('ALTER TABLE ir_action_act_window_view ADD CONSTRAINT ir_action_act_window_view_act_window_fkey '
        'FOREIGN KEY (act_window) REFERENCES ir_action_act_window (id) ON DELETE CASCADE')


def fix_wizard_action_ids(conn):
    #
    # wizard
    #
    log.info('fixing wizard action ids')
    action_rows = conn.execute('SELECT ir_action.id AS ir_action_id, '
        'ir_action_wizard.id AS ir_action_wizard_id '
        'FROM ir_action '
        'JOIN ir_action_wizard ON ir_action.id = ir_action_wizard.action '
        'WHERE ir_action.id != ir_action_wizard.id').fetchall()
    for row in action_rows:
        action_id, action_wizard_id = row
        count = conn.execute(text('SELECT COUNT(*) FROM ir_action_wizard WHERE id = :new_id'), new_id=action_id).fetchone()[0]
        if count > 0:
            raise Exception('Cannot set child id to parent id because there is'
                'another child with that id.')

        conn.execute(text('UPDATE ir_action_wizard SET id = :new_id WHERE id = :old_id'), new_id=action_id, old_id=action_wizard_id)


def fix_report_action_ids(conn):
    #
    # report
    #
    log.info('fixing report action ids')
    action_rows = conn.execute('SELECT ir_action.id AS ir_action_id, '
        'ir_action_report.id AS ir_action_report_id '
        'FROM ir_action '
        'JOIN ir_action_report ON ir_action.id = ir_action_report.action '
        'WHERE ir_action.id != ir_action_report.id').fetchall()
    for row in action_rows:
        action_id, action_report_id = row
        count = conn.execute(text('SELECT COUNT(*) FROM ir_action_report WHERE id = :new_id'),
            new_id=action_id).fetchone()[0]
        if count > 0:
            raise Exception('Cannot set child id to parent id because there is'
                'another child with that id.')
        conn.execute(text('UPDATE ir_action_report SET id = :new_id WHERE id = :old_id'),
            new_id=action_id, old_id=action_report_id)


def fix_url_action_ids(conn):
    #
    # url
    #
    log.info('fixing url action ids')
    action_rows = conn.execute('SELECT ir_action.id AS ir_action_id, '
        'ir_action_url.id AS ir_action_url_id '
        'FROM ir_action '
        'JOIN ir_action_url ON ir_action.id = ir_action_url.action '
        'WHERE ir_action.id != ir_action_url.id').fetchall()
    for row in action_rows:
        action_id, action_url_id = row
        count = conn.execute(text('SELECT COUNT(*) FROM ir_action_url WHERE id = :new_id'),
            new_id=action_id).fetchone()[0]
        if count > 0:
            raise Exception('Cannot set child id to parent id because there is'
                'another child with that id.')
        conn.execute(text('UPDATE ir_action_url SET id = :new_id WHERE id = :old_id'),
            new_id=action_id, old_id=action_url_id)


def main():
    engine = create_engine(db_uri,
        isolation_level='SERIALIZABLE', echo=True)

    with engine.begin() as connection:
        connection = connection.execution_options(
            autoflush=False, autocommit=False)

        fix_action_ids(connection)


if __name__ == '__main__':
    logging.basicConfig(level=logging.DEBUG, stream=sys.stdout)
    main()