Primary key name is lost when converting SIARD2 to MySQL

[chalkos]

Reproduced using dbptk-app-2.0.0-beta4.0.jar

1. I did an export from Postgre to SIARD-2 metadataPostgre.xml.txt
2. I imported this file to MySQL
3. and then I exported MySQL to SIARD-2 metadataMySQL.xml.txt
4. I compared the files metadataPostgre.xml and metadataMySQL.xml

For example in line 173 Postgre has pk_admin_unit but MySQL has PRIMARY

Originally reported by @Laurira

[chalkos]

This seems to be a case where some data loss is to be expected when restoring the SIARD file, because not all DBMS support all features in the same way. Sometimes there is no direct way to restore some parts of the database, eg: a view in MSSQL may contain an SQL statement incompatible with PostgreSQL, therefore that view will be present in SIARD mostly as documentation and will not be present when converting the SIARD to PostgreSQL, because PostgreSQL does not support the SQL statement used in the view.

In the future DBPTK will generate a report that will list all the data losses in the conversion. The case you describe may be one of those or there may be a simple way to solve it. I will have to look into this.

[Laurira]

As you described the issue so generally then maybe the following goes also under this bug. Seems like the application is losing the action of the foreignKey as well. See the picture (in the left hand there is SIARD generated from Postgre and the right side is generated from MySQL).

Also for mysql the application is not adding the nextval('auvik_keel_id_seq'::regclaas)

[chalkos]

Seems like the application is losing the action of the foreignKey as well

This is not critical information when converting the database (priorities are the data, then structure and only then an attempt is made to save behaviour such as the foreign key actions).

Also for mysql the application is not adding the nextval('auvik_keel_id_seq'::regclaas) On the second step, the import to MySQL, the default value may be lost (due to reasons I described in my previous comment, https://github.com/keeps/db-preservation-toolkit/issues/183#issuecomment-212370203)

The typeOriginal changing to int is because when converting the SIARD to MySQL, the MySQL data type created was INT, when creating a SIARD from MySQL, the INT type is perceived as the original type.