export and import rules

[OliverLemm]

for easier export and importing rules the standalone plsql function fc_export_qa_rules has to be rewritten and integrated into an export_import_rules_pkg.

the primary key and the audit columns must be excluded inside the export/import file.

a merge statement based on the columns qaru_rule_number and qaru_client_name has to be used for export and import as unique identifier the sql statement should be seperated from the statement for easier reading.

it should be evaluated whether the metadata for the rules and the import/export logik should be divided

[ThisGuenter]

Currently using schemacrawler lint for qa of our datamodels, would appreciate if you could deliver a predefined rule set to ease evaluation of your tool

[OliverLemm]

thanks for advice, we will take a look into the schemacrawler Is this the link from the tool? https://www.schemacrawler.com/lint.html

[ThisGuenter]

Yes - for schemacrawler.tools.linter.LinterCatalogSql/schemacrawler.tools.linter.LinterTableSql we use SQL with LISTAGG

LP-Internal Von: Oliver @.> Gesendet: Mittwoch, 28. September 2022 10:40 An: mt-ag/quasto @.> Cc: Guenter Leimbeck @.>; Comment @.> Betreff: Re: [mt-ag/quasto] export and import rules (Issue #10)

CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.

thanks for advice, we will take a look into the schemacrawler Is this the link from the tool? https://www.schemacrawler.com/lint.html

- Reply to this email directly, view it on GitHubhttps://github.com/mt-ag/quasto/issues/10#issuecomment-1260578300, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AEMXIBPJ3Y2SFHG2XCNIXLTWAP76HANCNFSM576CK7UA. You are receiving this because you commented.Message ID: @.**@.>>

[OliverLemm]

For next steps the schemacrawler is to be checked and the structure should be compared to JSON.

We need a possible JSON structure for the QA_RULES table.

[SabrinaPrang]

Comparing schemacrawler with Oracle integrated JSON functionality:

Schemacrawler:

• extra installations are neccessary: the newest version of Java and the Schemacrawler itself.
• It is written in Java and supports any database with a JDBc driver
• It is free and an oupen source API, available under a number of licenses
• It is very powerfull. Database metadata can be described for example as HTML5 or JSON. also diffs between Databses are possible
• It needs Jackson jars on the Classpath to serialize to JSON and YAML
• It is possible to manipulate data in the database

Usability for QUASTO: -> both directions are possible: DB-table to JSON and JSON to DB-table -> Java is needed -> an additional language must be used (Javascript or Ruby for example) -> extra Software is needed

Oracle Integrated JSON:

• native support for JSON
• JSON can be stored as VARCHAr2, CLOB or BLOb in the database
• two ways to access JsON Data: specialized functions and a dot-notation (This can only call data to 4k bytes)
• a JSON check constraint makes sure that the existing data is in an valid JSON format
• the default syntax for JSON in oracle is lax, It reflects the Javascript syntax for object fields
• with the SQL/JSON path language it is possible to select specific fields of the JSON

Usability for QUASTO:

• no extra software or language needed; it is integrated in Oracle
• both directions are possible: DB-table to JSON and JSON to DB-table
• checks and rules ensure that the JSON is valid

Questions:

• how to insert the JSON in the database?
• how to ensure that the JSON is in a valid structure similar to the database table?
• feedback for errors and success of import and export the data?
• how to verify that there is no duplicated data?

[OliverLemm]

regarding the points @SabrinaPrang has listed we will use JSON without the schemacrawler as in/output format

• we need no other tool
• we need no java
• we need no extra software
• we only need to support data to be imported and exported.

Thanks for your support @ThisGuenter

[SabrinaPrang]

The first draft of the JSON-format: QUASTO_JSON_structure_first_draft.txt

[SabrinaPrang]

For the uploaded JSON-files an upload-table is planned. Here is the first draft of the upload table QA_IMPORT_FILES with all needed skripts for sequence, trigger, etc. qa_import_files_cons.txt qa_import_files_ind.txt qaim_iu_trg.txt qaim_seq.txt qa_import_files.txt

[OliverLemm]

Next steps. Write Export & Import mechanic for json format with given table structure. Export should be a clob.

A formated export File written directly into a file will be defined in another task

[OliverLemm]

create new package export_import_rules_pkg with

• f_export_rules_table_to_clob(pi_client_name)
• p_clob_to_output(pi_clob)
• p_import_clob_to_rules_table(pi_clob)

Document how to spool out the rules inside the package and the documentation page

Keep an eye on the correct format when importing and exporting the SQL

Adjust Jenkins jobs

• Import Rules from JSON by using internal gitlab repo => adjust git-mt-qa-tool\mt-qa-tool\jenkins\pipeline\install_uninstall_quasto
• new Job Exportrules => push them to internal gitlab repo. Every file by client_name

[PhilippDahlem]

quasto_json_tempalte.txt New Version of the Export JSON that is generated by the Database. I wrote everything lowercase and added a missing column. Along with the removal of the "U" in "QUARU" to reduce confusion with the actual table columns. Also we couldnt implement the boolean Value Conversion form the is_active column easily so we went ahead and put the original values (0/1) in it. Now the Json depicts the table as far as possible.

[OliverLemm]

Package qa_export_import_rules_pkg created Functions and Procedures added for Export and Import Script for Writing exported CLOB to a file added /scripts/export_rules_to_file

The following issues still in

• when writing a json file a break is inside the file after the 32767 char
• when writing a json file the SQL Statement has \n inside the SQL Statement and is not well formated

[PhilippDahlem]

Es gilbt folgende Optionen um den Clob beim Export des JSONs richtig in den File auszugeben:

1. mit einem Substr von 32767 Zeichen Rückwarts nach den ersten Linebreak suchen, und an der Stelle dann das Offset setzen
2. per print clob den Exportierten Clob im SQL Plus abhandeln statt per dmbs_output.put_line
3. Jedes Zeile in ein JSON_ARRAY packen und dann per Array Eintrag eine Ausgabe machen und später wieder zusammen konkatenieren

Punkt 2 wird als erstes ausprobiert, da sauberer und simplerer Code-Logik.

[PhilippDahlem]

Json-Template: we came to the conclusion that removing the prefix "qaru" inside our json-template would be the more user friendly option. The Json will become more readable.

[OliverLemm]

we should create a qa_files table for import/exporting files columns

• id
• filename
• clob
• audit_columns

[OliverLemm]

Script example for importing a file with sqlcl https://gist.github.com/krisrice/8da5e6cbc07db5becde799742a3c72d2#file-blobs-helper-js-L25