Пример #1
0
def cli(  # noqa: ignore=C0330  # pylint: disable=C0330,R0913
    sqlite_file,
    mysql_user,
    mysql_password,
    mysql_database,
    mysql_host,
    mysql_port,
    chunk,
    log_file,
    vacuum,
    use_buffered_cursors,
):
    """Transfer MySQL to SQLite using the provided CLI options."""
    try:
        converter = MySQLtoSQLite(
            sqlite_file=sqlite_file,
            mysql_user=mysql_user,
            mysql_password=mysql_password,
            mysql_database=mysql_database,
            mysql_host=mysql_host,
            mysql_port=mysql_port,
            chunk=chunk,
            vacuum=vacuum,
            buffered=use_buffered_cursors,
            log_file=log_file,
        )
        converter.transfer()
    except KeyboardInterrupt:
        print("\nProcess interrupted. Exiting...")
        sys.exit(1)
    except Exception as err:  # pylint: disable=W0703
        print(err)
        sys.exit(1)
    def test_create_table_unknown_mysql_connector_error(
            self, sqlite_database, mysql_database, mysql_credentials, mocker,
            caplog):
        proc = MySQLtoSQLite(
            sqlite_file=sqlite_database,
            mysql_user=mysql_credentials.user,
            mysql_password=mysql_credentials.password,
            mysql_database=mysql_credentials.database,
            mysql_host=mysql_credentials.host,
            mysql_port=mysql_credentials.port,
        )

        class FakeSQLiteCursor:
            def executescript(self, statement):
                raise mysql.connector.Error(
                    msg="Error Code: 2000. Unknown MySQL error",
                    errno=errorcode.CR_UNKNOWN_ERROR,
                )

        mysql_inspect = inspect(mysql_database.engine)
        mysql_tables = mysql_inspect.get_table_names()
        mocker.patch.object(proc, "_sqlite_cur", FakeSQLiteCursor())
        caplog.set_level(logging.DEBUG)
        with pytest.raises(mysql.connector.Error):
            proc._create_table(choice(mysql_tables))
    def test_create_table_server_lost_connection_error(self, sqlite_database,
                                                       mysql_database,
                                                       mysql_credentials,
                                                       mocker, caplog):
        proc = MySQLtoSQLite(
            sqlite_file=sqlite_database,
            mysql_user=mysql_credentials.user,
            mysql_password=mysql_credentials.password,
            mysql_database=mysql_credentials.database,
            mysql_host=mysql_credentials.host,
            mysql_port=mysql_credentials.port,
        )

        class FakeSQLiteCursor:
            def executescript(self, *args, **kwargs):
                raise mysql.connector.Error(
                    msg=
                    "Error Code: 2013. Lost connection to MySQL server during query",
                    errno=errorcode.CR_SERVER_LOST,
                )

        class FakeSQLiteConnector:
            def commit(self, *args, **kwargs):
                return True

        mysql_inspect = inspect(mysql_database.engine)
        mysql_tables = mysql_inspect.get_table_names()

        mocker.patch.object(proc, "_sqlite_cur", FakeSQLiteCursor())
        mocker.patch.object(proc._mysql, "reconnect", return_value=True)
        mocker.patch.object(proc, "_sqlite", FakeSQLiteConnector())
        caplog.set_level(logging.DEBUG)
        with pytest.raises(mysql.connector.Error):
            proc._create_table(choice(mysql_tables))
 def test_translate_type_from_mysql_to_sqlite_invalid_column_type(
         self, mocker):
     with pytest.raises(ValueError) as excinfo:
         mocker.patch.object(MySQLtoSQLite,
                             "_valid_column_type",
                             return_value=False)
         MySQLtoSQLite._translate_type_from_mysql_to_sqlite("text")
     assert "Invalid column_type!" in str(excinfo.value)
 def test_data_type_collation_sequence_is_not_applied_on_non_textual_data_types(
     self, ):
     for column_type in (
             "BIGINT",
             "BINARY",
             "BIT",
             "BLOB",
             "BOOLEAN",
             "DATE",
             "DATETIME",
             "DATETIME",
             "DECIMAL",
             "DOUBLE",
             "FLOAT",
             "INTEGER",
             "INTEGER",
             "LONGBLOB",
             "MEDIUMBLOB",
             "MEDIUMINT",
             "NUMERIC",
             "REAL",
             "SMALLINT",
             "TIME",
             "TINYBLOB",
             "TINYINT",
             "VARBINARY",
             "YEAR",
     ):
         for collation in (
                 CollatingSequences.BINARY,
                 CollatingSequences.NOCASE,
                 CollatingSequences.RTRIM,
         ):
             assert (MySQLtoSQLite._data_type_collation_sequence(
                 collation, column_type) == "")
 def test_log_to_file(
     self,
     sqlite_database,
     mysql_database,
     mysql_credentials,
     caplog,
     tmpdir,
     faker,
     quiet,
 ):
     log_file = tmpdir.join("db.log")
     caplog.set_level(logging.DEBUG)
     with pytest.raises(mysql.connector.Error):
         MySQLtoSQLite(
             sqlite_file=sqlite_database,
             mysql_user=faker.first_name().lower(),
             mysql_password=faker.password(length=16),
             mysql_database=mysql_credentials.database,
             mysql_host=mysql_credentials.host,
             mysql_port=mysql_credentials.port,
             log_file=str(log_file),
             quiet=quiet,
         )
     assert any("Access denied for user" in message for message in caplog.messages)
     with log_file.open("r") as log_fh:
         log = log_fh.read()
         if len(caplog.messages) > 1:
             assert caplog.messages[1] in log
         else:
             assert caplog.messages[0] in log
         assert (
             re.match(r"^\d{4,}-\d{2,}-\d{2,}\s+\d{2,}:\d{2,}:\d{2,}\s+\w+\s+", log)
             is not None
         )
    def test_transfer_table_data_exceptions(
        self,
        sqlite_database,
        mysql_database,
        mysql_credentials,
        mocker,
        caplog,
        exception,
        quiet,
    ):
        proc = MySQLtoSQLite(
            sqlite_file=sqlite_database,
            mysql_user=mysql_credentials.user,
            mysql_password=mysql_credentials.password,
            mysql_database=mysql_credentials.database,
            mysql_host=mysql_credentials.host,
            mysql_port=mysql_credentials.port,
            quiet=quiet,
        )

        class FakeMySQLCursor:
            def fetchall(self):
                raise exception

            def fetchmany(self, size=1):
                raise exception

        mysql_inspect = inspect(mysql_database.engine)
        mysql_tables = mysql_inspect.get_table_names()

        table_name = choice(mysql_tables)
        columns = [
            column["name"] for column in mysql_inspect.get_columns(table_name)
        ]

        sql = (
            'INSERT OR IGNORE INTO "{table}" ({fields}) VALUES ({placeholders})'
            .format(
                table=table_name,
                fields=('"{}", ' * len(columns)).rstrip(" ,").format(*columns),
                placeholders=("?, " * len(columns)).rstrip(" ,"),
            ))

        mocker.patch.object(proc, "_mysql_cur", FakeMySQLCursor())

        with pytest.raises((mysql.connector.Error, sqlite3.Error)):
            proc._transfer_table_data(table_name, sql)
 def test_data_type_collation_sequence_is_applied_on_textual_data_types(
     self,
     collation,
     resulting_column_collation,
     column_type,
 ):
     assert (MySQLtoSQLite._data_type_collation_sequence(
         collation, column_type) == resulting_column_collation)
    def test_non_existing_mysql_database_raises_exception(
        self,
        sqlite_database,
        mysql_database,
        mysql_credentials,
        faker,
        mocker,
        caplog,
        exception,
        quiet,
    ):
        class FakeMySQLConnection(MySQLConnection):
            @property
            def database(self):
                return self._database

            @database.setter
            def database(self, value):
                self._database = value
                # raise a fake exception
                raise exception

            def is_connected(self):
                return True

            def cursor(
                self,
                buffered=None,
                raw=None,
                prepared=None,
                cursor_class=None,
                dictionary=None,
                named_tuple=None,
            ):
                return True

        caplog.set_level(logging.DEBUG)
        mocker.patch.object(
            mysql.connector, "connect", return_value=FakeMySQLConnection()
        )
        with pytest.raises((mysql.connector.Error, Exception)) as excinfo:
            MySQLtoSQLite(
                sqlite_file=sqlite_database,
                mysql_user=mysql_credentials.user,
                mysql_password=mysql_credentials.password,
                mysql_database=mysql_credentials.database,
                mysql_host=mysql_credentials.host,
                mysql_port=mysql_credentials.port,
                quiet=quiet,
            )
            assert any(
                "MySQL Database does not exist!" in message
                for message in caplog.messages
            )
        assert "Unknown database" in str(excinfo.value)
    def test_create_table_sqlite3_error(self, sqlite_database, mysql_database,
                                        mysql_credentials, mocker, caplog):
        proc = MySQLtoSQLite(
            sqlite_file=sqlite_database,
            mysql_user=mysql_credentials.user,
            mysql_password=mysql_credentials.password,
            mysql_database=mysql_credentials.database,
            mysql_host=mysql_credentials.host,
            mysql_port=mysql_credentials.port,
        )

        class FakeSQLiteCursor:
            def executescript(self, *args, **kwargs):
                raise sqlite3.Error("Unknown SQLite error")

        mysql_inspect = inspect(mysql_database.engine)
        mysql_tables = mysql_inspect.get_table_names()
        mocker.patch.object(proc, "_sqlite_cur", FakeSQLiteCursor())
        caplog.set_level(logging.DEBUG)
        with pytest.raises(sqlite3.Error):
            proc._create_table(choice(mysql_tables))
 def test_invalid_mysql_credentials_raises_access_denied_exception(
         self, sqlite_database, mysql_database, mysql_credentials, faker):
     with pytest.raises(mysql.connector.Error) as excinfo:
         MySQLtoSQLite(
             sqlite_file=sqlite_database,
             mysql_user=faker.first_name().lower(),
             mysql_password=faker.password(length=16),
             mysql_database=mysql_credentials.database,
             mysql_host=mysql_credentials.host,
             mysql_port=mysql_credentials.port,
         )
     assert "Access denied for user" in str(excinfo.value)
 def test_bad_mysql_connection(self, sqlite_database, mysql_credentials, mocker):
     FakeConnector = namedtuple("FakeConnector", ["is_connected"])
     mocker.patch.object(
         mysql.connector,
         "connect",
         return_value=FakeConnector(is_connected=lambda: False),
     )
     with pytest.raises((ConnectionError, IOError)) as excinfo:
         MySQLtoSQLite(
             sqlite_file=sqlite_database,
             mysql_user=mysql_credentials.user,
             mysql_password=mysql_credentials.password,
             mysql_host=mysql_credentials.host,
             mysql_port=mysql_credentials.port,
             mysql_database=mysql_credentials.database,
             chunk=1000,
         )
     assert "Unable to connect to MySQL" in str(excinfo.value)
 def test_missing_mysql_database_raises_exception(self, faker, quiet):
     with pytest.raises(ValueError) as excinfo:
         MySQLtoSQLite(mysql_user=faker.first_name().lower(), quiet=quiet)
     assert "Please provide a MySQL database" in str(excinfo.value)
 def test_missing_mysql_user_raises_exception(self, mysql_credentials, quiet):
     with pytest.raises(ValueError) as excinfo:
         MySQLtoSQLite(mysql_database=mysql_credentials.database, quiet=quiet)
     assert "Please provide a MySQL user" in str(excinfo.value)
 def test_translate_type_from_mysql_to_sqlite_all_valid_columns(self):
     for column_type in mysql_column_types + (
             "BIGINT UNSIGNED",
             "INTEGER UNSIGNED",
             "INT",
             "INT UNSIGNED",
             "SMALLINT UNSIGNED",
             "TINYINT UNSIGNED",
             "MEDIUMINT UNSIGNED",
             "CHAR(2)",
             "NCHAR(7)",
             "NVARCHAR(17)",
             "VARCHAR(123)",
     ):
         if column_type in {"dialect", "insert", "Insert"}:
             continue
         elif column_type == "INT":
             assert (MySQLtoSQLite._translate_type_from_mysql_to_sqlite(
                 column_type) == "INTEGER")
         elif column_type == "DECIMAL":
             assert (MySQLtoSQLite._translate_type_from_mysql_to_sqlite(
                 column_type) == "DECIMAL")
         elif column_type == "YEAR":
             assert (MySQLtoSQLite._translate_type_from_mysql_to_sqlite(
                 column_type) == "YEAR")
         elif column_type == "TIME":
             assert (MySQLtoSQLite._translate_type_from_mysql_to_sqlite(
                 column_type) == "TIME")
         elif column_type == "TIMESTAMP":
             assert (MySQLtoSQLite._translate_type_from_mysql_to_sqlite(
                 column_type) == "DATETIME")
         elif column_type in {
                 "BINARY",
                 "BIT",
                 "LONGBLOB",
                 "MEDIUMBLOB",
                 "TINYBLOB",
                 "VARBINARY",
         }:
             assert (MySQLtoSQLite._translate_type_from_mysql_to_sqlite(
                 column_type) == "BLOB")
         elif column_type == "CHAR":
             assert (MySQLtoSQLite._translate_type_from_mysql_to_sqlite(
                 column_type) == "CHARACTER")
         elif column_type == "CHAR(2)":
             assert (MySQLtoSQLite._translate_type_from_mysql_to_sqlite(
                 column_type) == "CHARACTER(2)")
         elif column_type == "NCHAR(7)":
             assert (MySQLtoSQLite._translate_type_from_mysql_to_sqlite(
                 column_type) == "NCHAR(7)")
         elif column_type == "NVARCHAR(17)":
             assert (MySQLtoSQLite._translate_type_from_mysql_to_sqlite(
                 column_type) == "NVARCHAR(17)")
         elif column_type == "VARCHAR(123)":
             assert (MySQLtoSQLite._translate_type_from_mysql_to_sqlite(
                 column_type) == "VARCHAR(123)")
         elif column_type in {
                 "ENUM",
                 "LONGTEXT",
                 "MEDIUMTEXT",
                 "SET",
                 "TINYTEXT",
         }:
             assert (MySQLtoSQLite._translate_type_from_mysql_to_sqlite(
                 column_type) == "TEXT")
         elif column_type == "JSON":
             assert (MySQLtoSQLite._translate_type_from_mysql_to_sqlite(
                 column_type) == "TEXT")
             assert (MySQLtoSQLite._translate_type_from_mysql_to_sqlite(
                 column_type,
                 sqlite_json1_extension_enabled=True) == "JSON")
         elif column_type.endswith(" UNSIGNED"):
             if column_type.startswith("INT "):
                 assert (MySQLtoSQLite._translate_type_from_mysql_to_sqlite(
                     column_type) == "INTEGER")
             else:
                 assert MySQLtoSQLite._translate_type_from_mysql_to_sqlite(
                     column_type) == column_type.replace(" UNSIGNED", "")
         else:
             assert (MySQLtoSQLite._translate_type_from_mysql_to_sqlite(
                 column_type) == column_type)
    def test_transfer_transfers_all_tables_from_mysql_to_sqlite(
        self,
        sqlite_database,
        mysql_database,
        mysql_credentials,
        helpers,
        capsys,
        caplog,
        chunk,
        vacuum,
        buffered,
    ):
        proc = MySQLtoSQLite(
            sqlite_file=sqlite_database,
            mysql_user=mysql_credentials.user,
            mysql_password=mysql_credentials.password,
            mysql_database=mysql_credentials.database,
            mysql_host=mysql_credentials.host,
            mysql_port=mysql_credentials.port,
            chunk=chunk,
            vacuum=vacuum,
            buffered=buffered,
        )
        caplog.set_level(logging.DEBUG)
        proc.transfer()
        assert all(
            message in [record.message for record in caplog.records]
            for message in {
                "Transferring table article_authors",
                "Transferring table article_images",
                "Transferring table article_tags",
                "Transferring table articles",
                "Transferring table authors",
                "Transferring table images",
                "Transferring table tags",
                "Transferring table misc",
                "Done!",
            }
        )
        assert all(record.levelname == "INFO" for record in caplog.records)
        assert not any(record.levelname == "ERROR" for record in caplog.records)
        out, err = capsys.readouterr()
        assert "Done!" in out.splitlines()[-1]

        sqlite_engine = create_engine(
            "sqlite:///{database}".format(
                database=sqlite_database,
                json_serializer=json.dumps,
                json_deserializer=json.loads,
            )
        )
        sqlite_cnx = sqlite_engine.connect()
        sqlite_inspect = inspect(sqlite_engine)
        sqlite_tables = sqlite_inspect.get_table_names()
        mysql_engine = create_engine(
            "mysql+mysqldb://{user}:{password}@{host}:{port}/{database}".format(
                user=mysql_credentials.user,
                password=mysql_credentials.password,
                host=mysql_credentials.host,
                port=mysql_credentials.port,
                database=mysql_credentials.database,
            )
        )
        mysql_cnx = mysql_engine.connect()
        mysql_inspect = inspect(mysql_engine)
        mysql_tables = mysql_inspect.get_table_names()

        """ Test if both databases have the same table names """
        assert sqlite_tables == mysql_tables

        """ Test if all the tables have the same column names """
        for table_name in sqlite_tables:
            assert [
                column["name"] for column in sqlite_inspect.get_columns(table_name)
            ] == [column["name"] for column in mysql_inspect.get_columns(table_name)]

        """ Test if all the tables have the same indices """
        index_keys = ("name", "column_names", "unique")
        mysql_indices = tuple(
            {key: index[key] for key in index_keys}
            for index in (
                chain.from_iterable(
                    mysql_inspect.get_indexes(table_name) for table_name in mysql_tables
                )
            )
        )

        for table_name in sqlite_tables:
            for sqlite_index in sqlite_inspect.get_indexes(table_name):
                sqlite_index["unique"] = bool(sqlite_index["unique"])
                assert sqlite_index in mysql_indices

        """ Test if all the tables have the same foreign keys """
        for table_name in mysql_tables:
            mysql_fk_stmt = text(
                """
                SELECT k.COLUMN_NAME AS `from`,
                       k.REFERENCED_TABLE_NAME AS `table`,
                       k.REFERENCED_COLUMN_NAME AS `to`,
                       c.UPDATE_RULE AS `on_update`,
                       c.DELETE_RULE AS `on_delete`
                FROM information_schema.TABLE_CONSTRAINTS AS i
                LEFT JOIN information_schema.KEY_COLUMN_USAGE AS k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME
                LEFT JOIN information_schema.REFERENTIAL_CONSTRAINTS c ON c.CONSTRAINT_NAME = i.CONSTRAINT_NAME
                WHERE i.TABLE_SCHEMA = :table_schema
                AND i.TABLE_NAME = :table_name
                AND i.CONSTRAINT_TYPE = :constraint_type
            """
            ).bindparams(
                table_schema=mysql_credentials.database,
                table_name=table_name,
                constraint_type="FOREIGN KEY",
            )
            mysql_fk_result = mysql_cnx.execute(mysql_fk_stmt)
            mysql_foreign_keys = [dict(row) for row in mysql_fk_result]

            sqlite_fk_stmt = 'PRAGMA foreign_key_list("{table}")'.format(
                table=table_name
            )
            sqlite_fk_result = sqlite_cnx.execute(sqlite_fk_stmt)
            if sqlite_fk_result.returns_rows:
                for row in sqlite_fk_result:
                    fk = dict(row)
                    assert {
                        "table": fk["table"],
                        "from": fk["from"],
                        "to": fk["to"],
                        "on_update": fk["on_update"],
                        "on_delete": fk["on_delete"],
                    } in mysql_foreign_keys

        """ Check if all the data was transferred correctly """
        sqlite_results = []
        mysql_results = []

        meta = MetaData(bind=None)
        for table_name in sqlite_tables:
            sqlite_table = Table(
                table_name, meta, autoload=True, autoload_with=sqlite_engine
            )
            sqlite_stmt = select([sqlite_table])
            sqlite_result = sqlite_cnx.execute(sqlite_stmt).fetchall()
            sqlite_result.sort()
            sqlite_result = [
                [float(data) if isinstance(data, Decimal) else data for data in row]
                for row in sqlite_result
            ]
            sqlite_results.append(sqlite_result)

        for table_name in mysql_tables:
            mysql_table = Table(
                table_name, meta, autoload=True, autoload_with=mysql_engine
            )
            mysql_stmt = select([mysql_table])
            mysql_result = mysql_cnx.execute(mysql_stmt).fetchall()
            mysql_result.sort()
            mysql_result = [
                [float(data) if isinstance(data, Decimal) else data for data in row]
                for row in mysql_result
            ]
            mysql_results.append(mysql_result)

        assert sqlite_results == mysql_results
    def test_transfer_specific_tables_transfers_only_specified_tables_from_mysql_to_sqlite(
        self,
        sqlite_database,
        mysql_database,
        mysql_credentials,
        helpers,
        caplog,
        chunk,
        vacuum,
        buffered,
        prefix_indices,
    ):
        mysql_engine = create_engine(
            "mysql+mysqldb://{user}:{password}@{host}:{port}/{database}".format(
                user=mysql_credentials.user,
                password=mysql_credentials.password,
                host=mysql_credentials.host,
                port=mysql_credentials.port,
                database=mysql_credentials.database,
            )
        )
        mysql_cnx = mysql_engine.connect()
        mysql_inspect = inspect(mysql_engine)
        mysql_tables = mysql_inspect.get_table_names()

        if six.PY2:
            table_number = choice(xrange(1, len(mysql_tables)))
        else:
            table_number = choice(range(1, len(mysql_tables)))

        random_mysql_tables = sample(mysql_tables, table_number)
        random_mysql_tables.sort()

        proc = MySQLtoSQLite(
            sqlite_file=sqlite_database,
            mysql_user=mysql_credentials.user,
            mysql_password=mysql_credentials.password,
            mysql_database=mysql_credentials.database,
            mysql_tables=random_mysql_tables,
            mysql_host=mysql_credentials.host,
            mysql_port=mysql_credentials.port,
            prefix_indices=prefix_indices,
        )
        caplog.set_level(logging.DEBUG)
        proc.transfer()
        assert all(
            message in [record.message for record in caplog.records]
            for message in set(
                ["Transferring table {}".format(table) for table in random_mysql_tables]
                + ["Done!"]
            )
        )
        assert all(record.levelname == "INFO" for record in caplog.records)
        assert not any(record.levelname == "ERROR" for record in caplog.records)

        sqlite_engine = create_engine(
            "sqlite:///{database}".format(
                database=sqlite_database,
                json_serializer=json.dumps,
                json_deserializer=json.loads,
            )
        )
        sqlite_cnx = sqlite_engine.connect()
        sqlite_inspect = inspect(sqlite_engine)
        sqlite_tables = sqlite_inspect.get_table_names()

        """ Test if both databases have the same table names """
        assert sqlite_tables == random_mysql_tables

        """ Test if all the tables have the same column names """
        for table_name in sqlite_tables:
            assert [
                column["name"] for column in sqlite_inspect.get_columns(table_name)
            ] == [column["name"] for column in mysql_inspect.get_columns(table_name)]

        """ Test if all the tables have the same indices """
        index_keys = {"name", "column_names", "unique"}
        mysql_indices = []
        for table_name in random_mysql_tables:
            for index in mysql_inspect.get_indexes(table_name):
                mysql_index = {}
                for key in index_keys:
                    if key == "name" and prefix_indices:
                        mysql_index[key] = "{table}_{name}".format(
                            table=table_name, name=index[key]
                        )
                    else:
                        mysql_index[key] = index[key]
                mysql_indices.append(mysql_index)

        for table_name in sqlite_tables:
            for sqlite_index in sqlite_inspect.get_indexes(table_name):
                sqlite_index["unique"] = bool(sqlite_index["unique"])
                assert sqlite_index in mysql_indices

        """ Check if all the data was transferred correctly """
        sqlite_results = []
        mysql_results = []

        meta = MetaData(bind=None)
        for table_name in sqlite_tables:
            sqlite_table = Table(
                table_name, meta, autoload=True, autoload_with=sqlite_engine
            )
            sqlite_stmt = select([sqlite_table])
            sqlite_result = sqlite_cnx.execute(sqlite_stmt).fetchall()
            sqlite_result.sort()
            sqlite_result = [
                [float(data) if isinstance(data, Decimal) else data for data in row]
                for row in sqlite_result
            ]
            sqlite_results.append(sqlite_result)

        for table_name in random_mysql_tables:
            mysql_table = Table(
                table_name, meta, autoload=True, autoload_with=mysql_engine
            )
            mysql_stmt = select([mysql_table])
            mysql_result = mysql_cnx.execute(mysql_stmt).fetchall()
            mysql_result.sort()
            mysql_result = [
                [float(data) if isinstance(data, Decimal) else data for data in row]
                for row in mysql_result
            ]
            mysql_results.append(mysql_result)

        assert sqlite_results == mysql_results
 def test_translate_type_from_mysql_to_sqlite_all_valid_columns(self):
     for column_type in mysql_column_types + (
             "CHAR(2)",
             "NCHAR(7)",
             "NVARCHAR(17)",
             "VARCHAR(123)",
     ):
         if column_type in {"dialect", "insert", "Insert"}:
             continue
         elif column_type == "INT":
             assert (MySQLtoSQLite._translate_type_from_mysql_to_sqlite(
                 column_type) == "INTEGER")
         elif column_type == "DECIMAL":
             assert (MySQLtoSQLite._translate_type_from_mysql_to_sqlite(
                 column_type) == "DECIMAL")
         elif column_type == "YEAR":
             assert (MySQLtoSQLite._translate_type_from_mysql_to_sqlite(
                 column_type) == "YEAR")
         elif column_type == "TIME":
             assert (MySQLtoSQLite._translate_type_from_mysql_to_sqlite(
                 column_type) == "TIME")
         elif column_type == "TIMESTAMP":
             assert (MySQLtoSQLite._translate_type_from_mysql_to_sqlite(
                 column_type) == "DATETIME")
         elif column_type in {
                 "BINARY",
                 "BIT",
                 "LONGBLOB",
                 "MEDIUMBLOB",
                 "TINYBLOB",
                 "VARBINARY",
         }:
             assert (MySQLtoSQLite._translate_type_from_mysql_to_sqlite(
                 column_type) == "BLOB")
         elif column_type == "CHAR":
             assert (MySQLtoSQLite._translate_type_from_mysql_to_sqlite(
                 column_type) == "CHARACTER")
         elif column_type == "CHAR(2)":
             assert (MySQLtoSQLite._translate_type_from_mysql_to_sqlite(
                 column_type) == "CHARACTER(2)")
         elif column_type == "NCHAR(7)":
             assert (MySQLtoSQLite._translate_type_from_mysql_to_sqlite(
                 column_type) == "NCHAR(7)")
         elif column_type == "NVARCHAR(17)":
             assert (MySQLtoSQLite._translate_type_from_mysql_to_sqlite(
                 column_type) == "NVARCHAR(17)")
         elif column_type == "VARCHAR(123)":
             assert (MySQLtoSQLite._translate_type_from_mysql_to_sqlite(
                 column_type) == "VARCHAR(123)")
         elif column_type in {
                 "ENUM",
                 "JSON",
                 "LONGTEXT",
                 "MEDIUMTEXT",
                 "SET",
                 "TINYTEXT",
         }:
             assert (MySQLtoSQLite._translate_type_from_mysql_to_sqlite(
                 column_type) == "TEXT")
         else:
             assert (MySQLtoSQLite._translate_type_from_mysql_to_sqlite(
                 column_type) == column_type)
 def test_translate_default_numbers_from_mysql_to_sqlite(
         self, column_default, sqlite_default_translation, column_type):
     assert (MySQLtoSQLite._translate_default_from_mysql_to_sqlite(
         column_default, column_type) == sqlite_default_translation)
    def test_transfer_limited_rows_from_mysql_to_sqlite(
        self,
        sqlite_database,
        mysql_database,
        mysql_credentials,
        helpers,
        caplog,
        chunk,
        vacuum,
        buffered,
        prefix_indices,
    ):
        if six.PY2:
            limit_rows = choice(xrange(1, 10))
        else:
            limit_rows = choice(range(1, 10))

        proc = MySQLtoSQLite(
            sqlite_file=sqlite_database,
            mysql_user=mysql_credentials.user,
            mysql_password=mysql_credentials.password,
            mysql_database=mysql_credentials.database,
            limit_rows=limit_rows,
            mysql_host=mysql_credentials.host,
            mysql_port=mysql_credentials.port,
            prefix_indices=prefix_indices,
        )
        caplog.set_level(logging.DEBUG)
        proc.transfer()
        assert all(
            message in [record.message for record in caplog.records]
            for message in {
                "Transferring table article_authors",
                "Transferring table article_images",
                "Transferring table article_tags",
                "Transferring table articles",
                "Transferring table authors",
                "Transferring table images",
                "Transferring table tags",
                "Transferring table misc",
                "Done!",
            }
        )
        assert all(record.levelname == "INFO" for record in caplog.records)
        assert not any(record.levelname == "ERROR" for record in caplog.records)

        sqlite_engine = create_engine(
            "sqlite:///{database}".format(
                database=sqlite_database,
                json_serializer=json.dumps,
                json_deserializer=json.loads,
            )
        )
        sqlite_cnx = sqlite_engine.connect()
        sqlite_inspect = inspect(sqlite_engine)
        sqlite_tables = sqlite_inspect.get_table_names()
        mysql_engine = create_engine(
            "mysql+mysqldb://{user}:{password}@{host}:{port}/{database}".format(
                user=mysql_credentials.user,
                password=mysql_credentials.password,
                host=mysql_credentials.host,
                port=mysql_credentials.port,
                database=mysql_credentials.database,
            )
        )
        mysql_cnx = mysql_engine.connect()
        mysql_inspect = inspect(mysql_engine)
        mysql_tables = mysql_inspect.get_table_names()

        mysql_connector_connection = mysql.connector.connect(
            user=mysql_credentials.user,
            password=mysql_credentials.password,
            host=mysql_credentials.host,
            port=mysql_credentials.port,
            database=mysql_credentials.database,
        )
        server_version = mysql_connector_connection.get_server_version()

        """ Test if both databases have the same table names """
        assert sqlite_tables == mysql_tables

        """ Test if all the tables have the same column names """
        for table_name in sqlite_tables:
            assert [
                column["name"] for column in sqlite_inspect.get_columns(table_name)
            ] == [column["name"] for column in mysql_inspect.get_columns(table_name)]

        """ Test if all the tables have the same indices """
        index_keys = {"name", "column_names", "unique"}
        mysql_indices = []
        for table_name in mysql_tables:
            for index in mysql_inspect.get_indexes(table_name):
                mysql_index = {}
                for key in index_keys:
                    if key == "name" and prefix_indices:
                        mysql_index[key] = "{table}_{name}".format(
                            table=table_name, name=index[key]
                        )
                    else:
                        mysql_index[key] = index[key]
                mysql_indices.append(mysql_index)

        for table_name in sqlite_tables:
            for sqlite_index in sqlite_inspect.get_indexes(table_name):
                sqlite_index["unique"] = bool(sqlite_index["unique"])
                assert sqlite_index in mysql_indices

        """ Test if all the tables have the same foreign keys """
        for table_name in mysql_tables:
            mysql_fk_stmt = text(
                """
                SELECT k.COLUMN_NAME AS `from`,
                       k.REFERENCED_TABLE_NAME AS `table`,
                       k.REFERENCED_COLUMN_NAME AS `to`,
                       c.UPDATE_RULE AS `on_update`,
                       c.DELETE_RULE AS `on_delete`
                FROM information_schema.TABLE_CONSTRAINTS AS i
                {JOIN} information_schema.KEY_COLUMN_USAGE AS k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME
                {JOIN} information_schema.REFERENTIAL_CONSTRAINTS c ON c.CONSTRAINT_NAME = i.CONSTRAINT_NAME
                WHERE i.TABLE_SCHEMA = :table_schema
                AND i.TABLE_NAME = :table_name
                AND i.CONSTRAINT_TYPE = :constraint_type
            """.format(
                    # MySQL 8.0.19 still works with "LEFT JOIN" everything above requires "JOIN"
                    JOIN="JOIN"
                    if (server_version[0] == 8 and server_version[2] > 19)
                    else "LEFT JOIN"
                )
            ).bindparams(
                table_schema=mysql_credentials.database,
                table_name=table_name,
                constraint_type="FOREIGN KEY",
            )
            mysql_fk_result = mysql_cnx.execute(mysql_fk_stmt)
            mysql_foreign_keys = [dict(row) for row in mysql_fk_result]

            sqlite_fk_stmt = 'PRAGMA foreign_key_list("{table}")'.format(
                table=table_name
            )
            sqlite_fk_result = sqlite_cnx.execute(sqlite_fk_stmt)
            if sqlite_fk_result.returns_rows:
                for row in sqlite_fk_result:
                    fk = dict(row)
                    assert {
                        "table": fk["table"],
                        "from": fk["from"],
                        "to": fk["to"],
                        "on_update": fk["on_update"],
                        "on_delete": fk["on_delete"],
                    } in mysql_foreign_keys

        """ Check if all the data was transferred correctly """
        sqlite_results = []
        mysql_results = []

        meta = MetaData(bind=None)
        for table_name in sqlite_tables:
            sqlite_table = Table(
                table_name, meta, autoload=True, autoload_with=sqlite_engine
            )
            sqlite_stmt = select([sqlite_table])
            sqlite_result = sqlite_cnx.execute(sqlite_stmt).fetchall()
            sqlite_result.sort()
            sqlite_result = [
                [float(data) if isinstance(data, Decimal) else data for data in row]
                for row in sqlite_result
            ]
            sqlite_results.append(sqlite_result)

        for table_name in mysql_tables:
            mysql_table = Table(
                table_name, meta, autoload=True, autoload_with=mysql_engine
            )
            mysql_stmt = select([mysql_table]).limit(limit_rows)
            mysql_result = mysql_cnx.execute(mysql_stmt).fetchall()
            mysql_result.sort()
            mysql_result = [
                [float(data) if isinstance(data, Decimal) else data for data in row]
                for row in mysql_result
            ]
            mysql_results.append(mysql_result)

        assert sqlite_results == mysql_results
 def test_translate_default_booleans_from_mysql_to_sqlite(
         self, mocker, column_default, sqlite_default_translation,
         sqlite_version):
     mocker.patch.object(sqlite3, "sqlite_version", sqlite_version)
     assert (MySQLtoSQLite._translate_default_from_mysql_to_sqlite(
         column_default, "BOOLEAN") == sqlite_default_translation)