gui/backend/gui_plugin/core/Db.py
def convert_workbench_sql_file_to_sqlite(mysql_sql_file_path):
    """Converts a MySQL SQL file to Sqlite syntax.

    Args:
        mysql_sql_file_path (str): The MySQL SQL file

    Returns:
        Nothing
    """
    try:
        with open(mysql_sql_file_path, 'r') as mysql_sql_file:
            sql = mysql_sql_file.read()

        sql = convert_workbench_sql_to_sqlite(sql)

        # Break apart multi ADD statements for ALTER TABLE
        # NOTE: one must never remove something from a table, only add
        prev_version_sql = ''
        alter_tables = re.findall(
            r'(ALTER\s+TABLE\s*(`[\w\d]*`)\s+((.|\n)*?);)', sql,
            flags=re.MULTILINE)
        for g in alter_tables:
            old_alter_table_stmt = g[0]
            new_alter_table_stmt = g[0]
            alter_table_stmt = ''
            table_name = g[1]
            # Add a , so the last action can be matched till , as well
            actions = f'{g[2]},'

            # Deal with ADD COLUMN actions
            actions_m = re.findall(
                r'(ADD\s+COLUMN\s+([`\w_\d\s\(\)]*)),',
                # r'(ADD\s+COLUMN\s+(([`\w_\d]+)[`\w_\d\s\(\)]*)),',
                actions, flags=re.MULTILINE)
            if actions_m:
                for g in actions_m:
                    alter_table_stmt = f'{alter_table_stmt}' \
                        f'ALTER TABLE {table_name}\n' \
                        f'  ADD COLUMN {g[1]};\n\n'

                    new_alter_table_stmt = re.sub(
                        re.escape(g[0]) + r'[,]*', '',
                        new_alter_table_stmt, flags=re.MULTILINE)

            # Deal with ADD INDEX actions
            actions_m = re.findall(
                r'(ADD\s+INDEX\s+([`\w_\d]*)\s*([\(\)`\w_\d\s]+)+\s*),',
                actions, flags=re.MULTILINE)
            if actions_m:
                for g in actions_m:
                    alter_table_stmt = f'{alter_table_stmt}' \
                        f'CREATE INDEX {g[1]} ON {table_name} {g[2]};\n\n'

                    new_alter_table_stmt = re.sub(
                        re.escape(g[0]) + r'[,]*', '',
                        new_alter_table_stmt, flags=re.MULTILINE)

            # If there are ADD CONSTRAINT actions, keep the cleaned up
            # ALTER TABLE for them
            add_constraints = re.findall(
                r'(ADD\s+(CONSTRAINT\s+(.|\n)*?),)',
                actions, flags=re.MULTILINE)
            if add_constraints:
                # As soon as there is a single ADD CONSTRAINT that Sqlite cannot
                # handle, the existing table has to be replaced by the new one

                # Get the original CREATE TABLE statement
                if prev_version_sql == '':
                    # If the previous SQL has not been fetched already, fetch
                    # it now
                    # file_dir = path.dirname(mysql_sql_file_path)
                    file_name = path.basename(mysql_sql_file_path)
                    filename_match = re.match(
                        r'(.*?)(\d+\.\d+\.\d+)_to_(\d+\.\d+\.\d+)((.|\.)*)',
                        file_name)
                    if not filename_match:
                        # cspell:ignore myschema
                        raise Exception(f'The file contains an ALTER TABLE '
                                        f'statement but the filename {file_name} does '
                                        f'not include the previous and the next version '
                                        f'number, e.g. myschema_1.0.3_to_1.0.4.mysql.sql')

                    g = filename_match.groups()
                    previous_version_file_path = path.join(
                        path.dirname(mysql_sql_file_path),
                        g[0] + g[1] + g[3])
                    if not Path(previous_version_file_path).is_file():
                        raise Exception(f'The file contains an ALTER TABLE '
                                        f'statement but the sql file for the previous '
                                        f'version {previous_version_file_path} cannot '
                                        f'be found.')

                    with open(previous_version_file_path, 'r') as prev_sql_file:
                        prev_version_sql = prev_sql_file.read()

                original_create_table_m = re.findall(
                    r'(CREATE\s+TABLE.*' + re.escape(table_name) +
                    r'\s*\((.|\n)*?;)',
                    prev_version_sql, flags=re.MULTILINE)
                if not original_create_table_m:
                    raise Exception(f'CREATE TABLE {table_name} statement not '
                                    f'found in previous sql file '
                                    f'{previous_version_file_path}.')

                original_create_table = convert_workbench_sql_to_sqlite(
                    original_create_table_m[0][0])

                tn = table_name[1:-1]

                # rename table to {tablename}_tmp
                tmp_create_table = re.sub(re.escape(f'`{tn}`'),
                                          f'`{tn}__tmp__`', original_create_table,
                                          flags=re.MULTILINE)

                # Get all column names of previous table
                columns = re.findall(r'^\s*(`[\w_\d]*`)',
                                     original_create_table, flags=re.MULTILINE)
                column_list = ', '.join(columns)

                # add script for table upgrade, like outlined here:
                # https://www.sqlite.org/lang_altertable.html
                replacement_script = f'-- Add constraints to table {tn}\n' \
                    f'{tmp_create_table};\n' \
                    f'INSERT INTO `{tn}__tmp__`({column_list})' \
                    f' SELECT {column_list} FROM `{tn}`;\n' \
                    f'DROP TABLE `{tn}`;\n' \
                    f'ALTER TABLE `{tn}__tmp__` RENAME TO `{tn}`' \
                    f';\n\n'

                sql = re.sub(re.escape(old_alter_table_stmt),
                             replacement_script, sql, flags=re.MULTILINE)

            if alter_table_stmt != '':
                sql = re.sub(re.escape(old_alter_table_stmt), alter_table_stmt,
                             sql, flags=re.MULTILINE)

        # Break out CREATE INDEX statement from CREATE TABLE
        # CREATE TABLE IF NOT EXISTS `user_group_has_user` (
        #   ...
        #   INDEX `fk_user_group_has_user_user1` (`user_id` ASC),
        #   ...
        create_tables = re.findall(
            r'(CREATE\s+TABLE\s+IF\s+NOT\s+EXISTS\s*(`\w*`'
            r')\s*\(\s*((.|\n)*?);)',
            sql, flags=re.MULTILINE)
        for g in create_tables:
            old_create_table_stmt = g[0]
            new_create_table_stmt = g[0]
            alter_index_stmt = ''
            table_name = g[1]
            actions = g[2]

            # Deal with ADD INDEX actions
            # INDEX `fk_user_group_has_user_user1` (`user_id` ASC),
            actions_m = re.findall(
                r'((UNIQUE\s+)*INDEX\s+([`\w_\d]*)\s+\s*(\('
                r'[`\w_\d\s,]+\))\s*,\s*)',
                actions, flags=re.MULTILINE)
            if actions_m:
                for g in actions_m:
                    index_line = g[0]

                    # If the table ends with __tmp__, remove that ending
                    # since it was only added for the ALTER TABLE workaround
                    if table_name.endswith('__tmp__'):
                        table_name = table_name[0:-7]

                    alter_index_stmt = f'{alter_index_stmt}' \
                        f'CREATE {g[1]}INDEX {g[2]} ON {table_name} {g[3]};\n\n'

                    # remove
                    new_create_table_stmt = re.sub(re.escape(index_line), '',
                                                   new_create_table_stmt, flags=re.MULTILINE)

            if alter_index_stmt != '':
                sql = re.sub(re.escape(old_create_table_stmt),
                             f'{new_create_table_stmt}\n\n{alter_index_stmt}\n',
                             sql, flags=re.MULTILINE)

        sqlite_sql_file_path = re.sub(r'(\.mysql\.)', '.sqlite.',
                                      mysql_sql_file_path)

        with open(sqlite_sql_file_path, 'w') as sqlite_sql_file:
            sqlite_sql_file.write(sql)

    except Exception as e:  # pragma: no cover
        logger.error(f'Cannot convert file. {e}')
Converts a MySQL SQL file to Sqlite syntax.

    Args:
        mysql_sql_file_path (str): The MySQL SQL file

    Returns:
        Nothing
    
Converts a MySQL SQL file to Sqlite syntax.
Converts a MySQL SQL file to Sqlite syntax .
def convert_workbench_sql_file_to_sqlite(mysql_sql_file_path):
    try:
        with open(mysql_sql_file_path, 'r') as mysql_sql_file:
            sql = mysql_sql_file.read()
        sql = convert_workbench_sql_to_sqlite(sql)
        prev_version_sql = ''
        alter_tables = re.findall(
            r'(ALTER\s+TABLE\s*(`[\w\d]*`)\s+((.|\n)*?);)', sql,
            flags=re.MULTILINE)
        for g in alter_tables:
            old_alter_table_stmt = g[0]
            new_alter_table_stmt = g[0]
            alter_table_stmt = ''
            table_name = g[1]
            actions = f'{g[2]},'
            actions_m = re.findall(
                r'(ADD\s+COLUMN\s+([`\w_\d\s\(\)]*)),',
                actions, flags=re.MULTILINE)
            if actions_m:
                for g in actions_m:
                    alter_table_stmt = f'{alter_table_stmt}' \
                        f'ALTER TABLE {table_name}\n' \
                        f'  ADD COLUMN {g[1]};\n\n'
                    new_alter_table_stmt = re.sub(
                        re.escape(g[0]) + r'[,]*', '',
                        new_alter_table_stmt, flags=re.MULTILINE)
            actions_m = re.findall(
                r'(ADD\s+INDEX\s+([`\w_\d]*)\s*([\(\)`\w_\d\s]+)+\s*),',
                actions, flags=re.MULTILINE)
            if actions_m:
                for g in actions_m:
                    alter_table_stmt = f'{alter_table_stmt}' \
                        f'CREATE INDEX {g[1]} ON {table_name} {g[2]};\n\n'
                    new_alter_table_stmt = re.sub(
                        re.escape(g[0]) + r'[,]*', '',
                        new_alter_table_stmt, flags=re.MULTILINE)
            add_constraints = re.findall(
                r'(ADD\s+(CONSTRAINT\s+(.|\n)*?),)',
                actions, flags=re.MULTILINE)
            if add_constraints:
                if prev_version_sql == '':
                    file_name = path.basename(mysql_sql_file_path)
                    filename_match = re.match(
                        r'(.*?)(\d+\.\d+\.\d+)_to_(\d+\.\d+\.\d+)((.|\.)*)',
                        file_name)
                    if not filename_match:
                        raise Exception(f'The file contains an ALTER TABLE '
                                        f'statement but the filename {file_name} does '
                                        f'not include the previous and the next version '
                                        f'number, e.g. myschema_1.0.3_to_1.0.4.mysql.sql')
                    g = filename_match.groups()
                    previous_version_file_path = path.join(
                        path.dirname(mysql_sql_file_path),
                        g[0] + g[1] + g[3])
                    if not Path(previous_version_file_path).is_file():
                        raise Exception(f'The file contains an ALTER TABLE '
                                        f'statement but the sql file for the previous '
                                        f'version {previous_version_file_path} cannot '
                                        f'be found.')
                    with open(previous_version_file_path, 'r') as prev_sql_file:
                        prev_version_sql = prev_sql_file.read()
                original_create_table_m = re.findall(
                    r'(CREATE\s+TABLE.*' + re.escape(table_name) +
                    r'\s*\((.|\n)*?;)',
                    prev_version_sql, flags=re.MULTILINE)
                if not original_create_table_m:
                    raise Exception(f'CREATE TABLE {table_name} statement not '
                                    f'found in previous sql file '
                                    f'{previous_version_file_path}.')
                original_create_table = convert_workbench_sql_to_sqlite(
                    original_create_table_m[0][0])
                tn = table_name[1:-1]
                tmp_create_table = re.sub(re.escape(f'`{tn}`'),
                                          f'`{tn}__tmp__`', original_create_table,
                                          flags=re.MULTILINE)
                columns = re.findall(r'^\s*(`[\w_\d]*`)',
                                     original_create_table, flags=re.MULTILINE)
                column_list = ', '.join(columns)
                replacement_script = f'-- Add constraints to table {tn}\n' \
                    f'{tmp_create_table};\n' \
                    f'INSERT INTO `{tn}__tmp__`({column_list})' \
                    f' SELECT {column_list} FROM `{tn}`;\n' \
                    f'DROP TABLE `{tn}`;\n' \
                    f'ALTER TABLE `{tn}__tmp__` RENAME TO `{tn}`' \
                    f';\n\n'
                sql = re.sub(re.escape(old_alter_table_stmt),
                             replacement_script, sql, flags=re.MULTILINE)
            if alter_table_stmt != '':
                sql = re.sub(re.escape(old_alter_table_stmt), alter_table_stmt,
                             sql, flags=re.MULTILINE)
        create_tables = re.findall(
            r'(CREATE\s+TABLE\s+IF\s+NOT\s+EXISTS\s*(`\w*`'
            r')\s*\(\s*((.|\n)*?);)',
            sql, flags=re.MULTILINE)
        for g in create_tables:
            old_create_table_stmt = g[0]
            new_create_table_stmt = g[0]
            alter_index_stmt = ''
            table_name = g[1]
            actions = g[2]
            actions_m = re.findall(
                r'((UNIQUE\s+)*INDEX\s+([`\w_\d]*)\s+\s*(\('
                r'[`\w_\d\s,]+\))\s*,\s*)',
                actions, flags=re.MULTILINE)
            if actions_m:
                for g in actions_m:
                    index_line = g[0]
                    if table_name.endswith('__tmp__'):
                        table_name = table_name[0:-7]
                    alter_index_stmt = f'{alter_index_stmt}' \
                        f'CREATE {g[1]}INDEX {g[2]} ON {table_name} {g[3]};\n\n'
                    new_create_table_stmt = re.sub(re.escape(index_line), '',
                                                   new_create_table_stmt, flags=re.MULTILINE)
            if alter_index_stmt != '':
                sql = re.sub(re.escape(old_create_table_stmt),
                             f'{new_create_table_stmt}\n\n{alter_index_stmt}\n',
                             sql, flags=re.MULTILINE)
        sqlite_sql_file_path = re.sub(r'(\.mysql\.)', '.sqlite.',
                                      mysql_sql_file_path)
        with open(sqlite_sql_file_path, 'w') as sqlite_sql_file:
            sqlite_sql_file.write(sql)
    except Exception as e:  
        logger.error(f'Cannot convert file. {e}')
def convert_workbench_sql_file_to_sqlite ( mysql_sql_file_path ) : try : with open ( mysql_sql_file_path , 'r' ) as mysql_sql_file : sql = mysql_sql_file . read ( ) sql = convert_workbench_sql_to_sqlite ( sql ) prev_version_sql = '' alter_tables = re . findall ( r'(ALTER\s+TABLE\s*(`[\w\d]*`)\s+((.|\n)*?);)' , sql , flags = re . MULTILINE ) for g in alter_tables : old_alter_table_stmt = g [ 0 ] new_alter_table_stmt = g [ 0 ] alter_table_stmt = '' table_name = g [ 1 ] actions = f'{g[2]},' actions_m = re . findall ( r'(ADD\s+COLUMN\s+([`\w_\d\s\(\)]*)),' , actions , flags = re . MULTILINE ) if actions_m : for g in actions_m : alter_table_stmt = f'{alter_table_stmt}' f'ALTER TABLE {table_name}\n' f'  ADD COLUMN {g[1]};\n\n' new_alter_table_stmt = re . sub ( re . escape ( g [ 0 ] ) + r'[,]*' , '' , new_alter_table_stmt , flags = re . MULTILINE ) actions_m = re . findall ( r'(ADD\s+INDEX\s+([`\w_\d]*)\s*([\(\)`\w_\d\s]+)+\s*),' , actions , flags = re . MULTILINE ) if actions_m : for g in actions_m : alter_table_stmt = f'{alter_table_stmt}' f'CREATE INDEX {g[1]} ON {table_name} {g[2]};\n\n' new_alter_table_stmt = re . sub ( re . escape ( g [ 0 ] ) + r'[,]*' , '' , new_alter_table_stmt , flags = re . MULTILINE ) add_constraints = re . findall ( r'(ADD\s+(CONSTRAINT\s+(.|\n)*?),)' , actions , flags = re . MULTILINE ) if add_constraints : if prev_version_sql == '' : file_name = path . basename ( mysql_sql_file_path ) filename_match = re . match ( r'(.*?)(\d+\.\d+\.\d+)_to_(\d+\.\d+\.\d+)((.|\.)*)' , file_name ) if not filename_match : raise Exception ( f'The file contains an ALTER TABLE ' f'statement but the filename {file_name} does ' f'not include the previous and the next version ' f'number, e.g. myschema_1.0.3_to_1.0.4.mysql.sql' ) g = filename_match . groups ( ) previous_version_file_path = path . join ( path . dirname ( mysql_sql_file_path ) , g [ 0 ] + g [ 1 ] + g [ 3 ] ) if not Path ( previous_version_file_path ) . is_file ( ) : raise Exception ( f'The file contains an ALTER TABLE ' f'statement but the sql file for the previous ' f'version {previous_version_file_path} cannot ' f'be found.' ) with open ( previous_version_file_path , 'r' ) as prev_sql_file : prev_version_sql = prev_sql_file . read ( ) original_create_table_m = re . findall ( r'(CREATE\s+TABLE.*' + re . escape ( table_name ) + r'\s*\((.|\n)*?;)' , prev_version_sql , flags = re . MULTILINE ) if not original_create_table_m : raise Exception ( f'CREATE TABLE {table_name} statement not ' f'found in previous sql file ' f'{previous_version_file_path}.' ) original_create_table = convert_workbench_sql_to_sqlite ( original_create_table_m [ 0 ] [ 0 ] ) tn = table_name [ 1 : - 1 ] tmp_create_table = re . sub ( re . escape ( f'`{tn}`' ) , f'`{tn}__tmp__`' , original_create_table , flags = re . MULTILINE ) columns = re . findall ( r'^\s*(`[\w_\d]*`)' , original_create_table , flags = re . MULTILINE ) column_list = ', ' . join ( columns ) replacement_script = f'-- Add constraints to table {tn}\n' f'{tmp_create_table};\n' f'INSERT INTO `{tn}__tmp__`({column_list})' f' SELECT {column_list} FROM `{tn}`;\n' f'DROP TABLE `{tn}`;\n' f'ALTER TABLE `{tn}__tmp__` RENAME TO `{tn}`' f';\n\n' sql = re . sub ( re . escape ( old_alter_table_stmt ) , replacement_script , sql , flags = re . MULTILINE ) if alter_table_stmt != '' : sql = re . sub ( re . escape ( old_alter_table_stmt ) , alter_table_stmt , sql , flags = re . MULTILINE ) create_tables = re . findall ( r'(CREATE\s+TABLE\s+IF\s+NOT\s+EXISTS\s*(`\w*`' r')\s*\(\s*((.|\n)*?);)' , sql , flags = re . MULTILINE ) for g in create_tables : old_create_table_stmt = g [ 0 ] new_create_table_stmt = g [ 0 ] alter_index_stmt = '' table_name = g [ 1 ] actions = g [ 2 ] actions_m = re . findall ( r'((UNIQUE\s+)*INDEX\s+([`\w_\d]*)\s+\s*(\(' r'[`\w_\d\s,]+\))\s*,\s*)' , actions , flags = re . MULTILINE ) if actions_m : for g in actions_m : index_line = g [ 0 ] if table_name . endswith ( '__tmp__' ) : table_name = table_name [ 0 : - 7 ] alter_index_stmt = f'{alter_index_stmt}' f'CREATE {g[1]}INDEX {g[2]} ON {table_name} {g[3]};\n\n' new_create_table_stmt = re . sub ( re . escape ( index_line ) , '' , new_create_table_stmt , flags = re . MULTILINE ) if alter_index_stmt != '' : sql = re . sub ( re . escape ( old_create_table_stmt ) , f'{new_create_table_stmt}\n\n{alter_index_stmt}\n' , sql , flags = re . MULTILINE ) sqlite_sql_file_path = re . sub ( r'(\.mysql\.)' , '.sqlite.' , mysql_sql_file_path ) with open ( sqlite_sql_file_path , 'w' ) as sqlite_sql_file : sqlite_sql_file . write ( sql ) except Exception as e : logger . error ( f'Cannot convert file. {e}' )
Converts a MySQL SQL file to Sqlite syntax.
Converts a MySQL SQL file to Sqlite syntax .
"""Converts a MySQL SQL file to Sqlite syntax.

    Args:
        mysql_sql_file_path (str): The MySQL SQL file

    Returns:
        Nothing
    """ # Break apart multi ADD statements for ALTER TABLE # NOTE: one must never remove something from a table, only add # Add a , so the last action can be matched till , as well # Deal with ADD COLUMN actions # r'(ADD\s+COLUMN\s+(([`\w_\d]+)[`\w_\d\s\(\)]*)),', # Deal with ADD INDEX actions # If there are ADD CONSTRAINT actions, keep the cleaned up # ALTER TABLE for them # As soon as there is a single ADD CONSTRAINT that Sqlite cannot # handle, the existing table has to be replaced by the new one # Get the original CREATE TABLE statement # If the previous SQL has not been fetched already, fetch # it now # file_dir = path.dirname(mysql_sql_file_path) # cspell:ignore myschema # rename table to {tablename}_tmp # Get all column names of previous table # add script for table upgrade, like outlined here: # https://www.sqlite.org/lang_altertable.html # Break out CREATE INDEX statement from CREATE TABLE # CREATE TABLE IF NOT EXISTS `user_group_has_user` ( #   ... #   INDEX `fk_user_group_has_user_user1` (`user_id` ASC), #   ... # Deal with ADD INDEX actions # INDEX `fk_user_group_has_user_user1` (`user_id` ASC), # If the table ends with __tmp__, remove that ending # since it was only added for the ALTER TABLE workaround # remove # pragma: no cover
None
mysql_sql_file_path
The MySQL SQL file
The MySQL SQL file