Kimai2: Database migration script

[jcgruenhage]

Is your feature request related to a problem? Please describe.
It's a bit frustrating right now to migrate between different database backends supported by kimai.

Describe the solution you'd like
A script for migrating from sqlite to mysql would help a lot with this.

Describe alternatives you've considered
The alternative is manually migrating the database, but that's not easy and a bit error prone.

Additional context
Other projects have scripts, take a look at this for example: https://github.com/matrix-org/synapse/blob/master/scripts/synapse_port_db

[adrianrudnik]

Sqlite should be compatible AFAIK, did you try it?

sqlite3 yourdb .dump > dump.sql
mysql -h yourhost -u youruser -pyourpassword yourdb < dump.sql

any specific error there?

[kevinpapst]

Dump adds the structure, doesn't it? That might be compatible, but is not what we want.
We need the real MySQL structure with indexes, foreign keys, collations ...

Here are some tipps how to get the raw data:
https://stackoverflow.com/questions/75675/how-do-i-dump-the-data-of-some-sqlite3-tables

[adrianrudnik]

If the source database is up with the migrations, a solid base database can be created with

bin/console doctrine:database:create
bin/console doctrine:schema:create
bin/console doctrine:migrations:version --all --add

and then maybe just remove the schema part on the dump's head prior importing it? Rest of the SQL should be almost be compatible, and much easier to handle.

The other way would be building a general import format that expects a very specific set of data. Just moving managed entities from one entity manager to another one while making them unmanaged will lead to unique ID problems at least, worst you need to work Doctrine to rebuild the relations which is much more effort then editing SQL statements.

[kevinpapst]

Hm, the migrations table ... interesting problem. If we strip the structure from the dump, then we don't get the migrations table (as it is not created by schema:create).
If we create it prior to the import with your last command, the import will fail with duplicate keys.
Not easy to automate.

Using Doctrine for the migration is 👎 for the reasons you mentioned. I would never ever do that.

I think the simplest solution is to write a proper documentation page with a handful of commands.

[kevinpapst]

If someone finds that topic, did a migration and wants to contribute a documentation, I would definitely include/merge it.

But for me, this is not in the scope of Kimai, closing.