search

sfa-siard / SiardGui

SIARD Suite - SiardGui
Other
17 stars 3 forks source link

SIARD 2.1 --> Microsoft SQL --> SIARD 2.1: foreign-key action elements change (<deleteAction>, <updateAction> #58

Open ibbenz opened 1 year ago

ibbenz commented 1 year ago

Issue: The value of the and elements change during the process of importing date from a SIARD 2.1 in a DB and exporting the data in a SIARD 2.1-file. All values are changed to RESTRICT.

Example:

Values before import:

metadata.xml in original SIARD 2.1-file for foreign key ProductsOrderDetails1:

RESTRICT CASCADE

metadata.xml in original SIARD 2.1-file for foreign key OrdersOrderDetails:

CASCADE CASCADE

Values after export:

metadata.xml in exported SIARD 2.1-file for foreign key ProductsOrderDetails1: RESTRICT

RESTRICT

metadata.xml in exported SIARD 2.1-file for foreign key OrdersOrderDetails: RESTRICT

RESTRICT

Process: Upload data from 0_1-3_Northwind_simple_220913_B5_2d1.siard (original) into a Microsoft SQL database Download the value with Siard Suite 2.1 to a SIARD 2.1 file (exported).

SIARD file which was uploaded: 0_1-3_Northwind_simple_220913_B5_2d1.zip

SIARD file which was downloaded: 1_4b_Northwind_simple_220913_B_2d1.zip

mburri commented 1 year ago

Thanks for your issue.

We'll investigate this issue.

We will batch issues related to MS SQL in a future bugfix release.

sdbuehlmann commented 9 months ago

It seems that this is not only a problem when working with MS SQL but also when working with any other database. I've tested it with POSTGRES and wrote a script that initialized some simple sample data on a DB. When archiving this DB with siard-suite, the foreign-key-actions are archived as expected. But if I upload the generated archive again, the "ON DELETE" and the "ON UPDATE" statements are missing in the SQL expression for generating the foreign-keys (see addForeignKeys method in class PrimaryDataToDb).

Edit: You can find my reproduction code here