ODBC Export/Import cheatsheet using drincw

Remarks:

• this was not really tested with explicit Entity References(ER), but is an edjucated guess about how it should work
• it was tested by converting all ER paths of the old pathbuilder (PB) and their fields to plaintext. These were then resolved by the link formatter in the new system.

Steps:

1. Download the drincw executables or build them from source.

2. Export the Triplestore of the old system as .nq. For example using the GraphDB workbench

3. Export Pathbuilder of the old system as .xml:

   • In case you want to use the plaintext + WissKI Link Formatter approach change all ER paths to plaintext.

4. Create an SQLITE dump using n2j:

   • use ./n2j -sqlite dump.sqlite path_to_old_pb.xml triplestore_dump.nq
   • you can specify separators for fields with multiple values with the -sqlite-delimiter

5. Import the dump into phpMyAdmin:

   • Open the dump with an SQLITE viewer. E.g. https://sqlitebrowser.org/dl/
   • Export the desired tables as .csv files with the viewer
   • Import them using phpMyAdmin

6. Export the Pathbuilder of the new system:

   • make sure you temporarily convert fields that store URIs to a plaintext field e.g. Image
   • make sure all paths in the pathbuilder have a field assigned

7. Use makeodbc to generate a selector file for the new pathbuilder:

   ./makeodbc -dump-selectors path_to_new_pb.xml > selectors.json

8. Modify the selector file in a text editor of your choice:

   • generally the selector files entries look like this:

   "bundle_machine_name": {
       // name of the corresponding DB table
       "table": "table_name", 
       // define the import order
       // if not specified defaults to 0
       // tables with lower numbers are imported first
       "order": "0",
       // currenly does nothing
       "id": "uri", 
       "distinct": true, 
       // paths of the bundle
       "fields": { 
           // use `column` to reference a column in "table"
           "field_machine_name": "`column` column_name", 
           // use `join` to reference columns from other tables where myKey 
           // is the column name of the key of "table" and theirKey is 
           // the column name of the key of the "external_table"
           // usually these are from sub-bundles
           "other_field_machine_name": "`join` external_column_name `from` external_table on myKey theirKey",
       }
   }

   • create a mapping that maps the table columns to the correct bundles and field names of the new PB
   • (This is a guess, could be wrong!!) You may have to combine the field sections of Bundles in order to retain the correct Entity References. E.g.: Bundle1 references Bundle2 via explicit Entity Reference (not Sub-Bundle!). Then you would have to cut and paste the fields from Bundle2 to Bundle1 and treat them as a Sub-Bundle.

9. Use makeodbc to generate an ODBC import file:

     ./makeodbc -load-selectors selectors.json path_to_new_pb.xml > import.xml

10. You may need to adjust the import order depending on the structure of your data. This may be necessary when you have highly interlinked data, like e.g. classification trees. In this case it is advisable to import the data by starting from the root. This can be done by adding the following to the relevant table's <append> section in the import file:

    <append>... ORDER BY `table_name`.`column_name`</append>

11. Sometimes some fields should be combined into a single new field. This can be done by using the CONCAT and ISNULL functions:

    CONCAT(IFNULL(bundle__alte_maske_1.field__alte_maske_63,""),IFNULL(bundle__alte_maske_1.field__alte_maske_44, "")) as objektgeschichte

12. Import the data by using the created import.xml file.

• Enable the WissKI ODBC Import module by navigating to the Manage → Extend section of the WissKI.
• After enabling the module navigate to Configuration → WissKI ODBC Import
• Click File upload and select the import.xml file.
• Click Save at the bottom of the page and pray everything works