Question regarding contribution

[dorkyboi]

The guide states we should modify world.sql if we wish to add/change things. I would like to write a script to add more translations, as doing that manually for thousands of records is just not an option.

Question is if it would be okay to:

• import world.sql to database
• let the script modify the database
• generate new world.sql

The problem is with the last step - generated output will differ from what we have now, depending on the tool doing the generation. Would that still be okay? I would imagine it will make reviewing changes in PR literally impossible.

Perhaps you could tell me what tool should I use to produce output similar to original structure?

[dosubot[bot]]

Hey @dorkyboi! I'm here to help you with any questions or issues you have. Feel free to ask me anything!

You should use mysqldump to produce an output similar to the original structure of world.sql when generating a new world.sql after modifying the database with a script.

- name: Export MySQL SQL
  run: |
    mysqldump -uroot -proot --add-drop-table --disable-keys --set-charset --skip-add-locks world > sql/world.sql

Additionally, if you are working with a more complex setup or need to migrate data between different database systems, you might consider using a tool like nmig. nmig allows for detailed configuration of database connections and migration processes, supporting both MySQL and PostgreSQL databases.

Here is an example configuration for nmig:

{
    "source": {
        "host": "127.0.0.1",
        "port": 3306,
        "database": "world",
        "charset": "utf8mb4",
        "user": "root",
        "password": "root"
    },
    "target": {
        "host": "127.0.0.1",
        "port": 5432,
        "database": "world",
        "charset": "UTF8",
        "user": "postgres",
        "password": "postgres"
    },
    "max_each_db_connection_pool_size": 20,
    "number_of_simultaneously_running_loader_processes": "DEFAULT",
    "loader_max_old_space_size": "DEFAULT",
    "streams_high_water_mark": 16384,
    "encoding": "utf8",
    "schema": "public",
    "exclude_tables": [],
    "include_tables": [],
    "migrate_only_data": false,
    "delimiter": ",",
    "enable_extra_config": false
}

This configuration ensures that all necessary parameters are set for a smooth migration process ^[1][2].

To continue talking to Dosu, mention @dosu.

[dorkyboi]

I'll give that a try, let's see how it goes

[dorkyboi]

Nope, unfortunately the structure generated by mysqldump is different. For example right now there are CREATE TABLE statements first and then INSERTs, but in the generated file it's CREATE table, INSERT values, CREATE anothertable, INSERT valuesforanothertable, and so forth I can submit the file as it is but maybe there's another way?

[dosubot[bot]]

To ensure the structure of the generated world.sql file matches the original, you can use the --no-data and --no-create-info options with mysqldump to separate the schema and data dumps. This way, you can control the order of CREATE TABLE and INSERT statements.

Here’s a step-by-step approach:

1. Dump the schema (structure) only:

   mysqldump -uroot -proot --no-data --add-drop-table --disable-keys --set-charset --skip-add-locks world > sql/world_schema.sql

2. Dump the data only:

   mysqldump -uroot -proot --no-create-info --disable-keys --set-charset --skip-add-locks world > sql/world_data.sql

3. Combine the schema and data dumps:

   cat sql/world_schema.sql sql/world_data.sql > sql/world.sql

This method ensures that all CREATE TABLE statements are grouped together at the beginning, followed by all INSERT statements, maintaining the structure similar to the original world.sql file.

To continue talking to Dosu, mention @dosu.

[dr5hn]

@dorkyboi Thank you for showing interest.

That's completely fine! Go ahead and do your contribution. Rest i will handle.

[dorkyboi]

Great, thank you ❤️