SQL DDL

[isedwards]

Is it possible to generate the SQL DDL using Enterprise Architect and store the latest version of the SQL in the repository alongside the model (I don't have an EA licence to do this myself)?

[joergklausen]

Hi @isedwards I don't know how to do this, I am afraid. My first attempt, following https://sparxsystems.com/enterprise_architect_user_guide/15.2/model_domains/generateddl.html didn't bring up any packages I could work on. Any experience, anyone?

BTW, you can get a fully functional copy of EA for a 30 day trial, so please feel free to build the model (cf. documentation) and let us know if you succeeded.

[isedwards]

Thank you Jörg - I'll download EA and work through the WMDR documentation. I'll aim to report back on progress in general, and also whether I manage to generate SQL DDL from the model, by end of April 2021.

If you're happy for this issue to stay open then feel free to assign it to me.

[isedwards]

I've managed to create SQL DDL from the UML Class Model using the following steps (Professional Licence):

1. In browser, select the Model root node then, from the ribbon, choose Design > Add Package > test_ddl (a new empty package to contain the platform specific DDL)
2. From the ribbon: Start > Preferences > Preferences > Source Code Engineering > Code Editors > Default Database > PostgreSQL (set the default database type)
3. Select a UML Class diagram from Browser > Model > WMDR (e.g. HighLevelOverview)
4. From the ribbon, Design > Transform > Apply Transformation. Select classes to transform and choose DDL. In the pop up window, select the new test_ddl package as the Target Package and click "Do Transform"
5. In browser, select the DDL class diagram in the test_ddl package
6. Finally, from the ribbon: Develop > Generate > Single file: test_ddl.sql - and click the Generate button

[efucile]

thanks @isedwards this is very useful. I guess that this will produce a database of metadata as we don't have actual data in the model. I think we need to decide how to proceed now as the ddl is not enough because there are controlled vocabularies in http://codes.wmo.int/wmdr which should populate some of the tables for the database to be fully wmdr compliant. I think we should investigate if there is a way to define constraints in the model to linke some of the elements to controlled vocabularies and hope that this would be enough to have them automatically in the sql. Otherwise we need to generate extra sql to load the controlled vocabularies. It could be a simple python script.

[isedwards]

Yes, the process has generated the SQL schema but there is no data or metadata (e.g. no INSERT statements that would add contents to any of the data or metadata tables).

I suspect it would be best to continue to use the wmo-im/wmds repository as the single source for maintaining vocabularies.

However, would it makes sense to add additional classes (like ClimateZoneType) to the model?

4-07.csv - ClimateZoneType	notation	name	description
equatorialRainforestFullyHumid	Af	Equatorial rainforest - fully humid
equatorialMonsoon	Am	Equatorial - Monsoon
...	...	...

Currently we do not have ClimateZoneType and ClimateZone therefore does not have a foreignkey/relationship to ClimateZoneType

CREATE TABLE "Climatezone"
(
    "Climatezone" varchar(50) NULL, -- 4-07 type of climate zone at the facility. From the ClimateZoneType codelist.
    "Validperiod" varchar(50) NULL, -- The time period for which the specified climateZone is known to be valid. Normally, this will be specified as a "from" date, implying that the validity extends but does not include the next climateZone on record. If only one climateZone is specified for an observing facility, the time stamp is optional.
    "ClimatezoneID" integer NOT NULL
)
;

[efucile]

great @isedwards you are on the job! The model doesn't have everything and the right connections and tables need to be made. codes.wmo.int will always be the autoritative source for the controlled vocabularies, but we need to have corresponding tables with foreign keys and populated automatically from the codes.wmo.int.

[joergklausen]

The WMDR1.0 EA model contains classes for most of the code lists, maybe not all of them. For example, the ClimateZoneType is included (https://schemas.wmo.int/wmdr/1.0/html/index.html?goto=1:1:3:211). It is true that there is no link to the codes.wmo.int/wmdr to pull these in automatically. Please open a new issue at https://github.com/wmo-im/wmdr2 if this is needed.