Using excel and ansible to create custom schema's (automating schemacreation)

[patrick.d]

Currently i'm doing an install of CMDBuild, but to say the least, the interface of creating classes, attributes, domains and such, is a bit cumbersome. So i devised a method to speed development up, especially since i'm currently in the process of revising my database schema quite a lot :)

 

So i'm working on the following:

Create an excelsheet with 3 tabs: classes, domains, lookups

From this excelsheet i will create a YAML file which i plan to using in ansible to create all classes. (since i'm using ansible this is the most logical solution for me, however every yaml template engine could take the yaml + template and create the SQL )

 

So far i have concluded the following workflow to create a useable database from the cmdbuild_dd_sql.log file

 

- create lookuptype: INSERT INTO "LookUp" ("Status", "Type", "Number", "IsDefault", "User") VALUES ('A','$lookup','1','false','system');

- create lookupvalue using INSERT INTO "LookUp" ("Notes", "Status", "Description", "Type", "Number", "IsDefault", "Code", "User") VALUES ('$notes','A','$Description','$Lookup','$incrementalvalue','false','$code','system');

- create class: SELECT * FROM cm_create_class('$classname', '$parent', 'DESCR: $classname|MODE: write|STATUS: active|SUPERCLASS: $superclass?|TYPE: class|USERSTOPPABLE: false');

- create domain: SELECT * FROM cm_create_domain('$domainname', 'LABEL: $label|DESCRDIR: $description|DESCRINV: $inverse|MODE: write|STATUS: active|TYPE: domain|CLASS1: $classleft|CLASS2: $classright|CARDIN: $cardinality|MASTERDETAIL: $masterdetail|MDLABEL: $detaildescr');

- create attributes:  SELECT * FROM cm_create_attribute('"$class"'::regclass,'$attrname','$attrtype',null,false,false,'STATUS: active|BASEDSP: true|CLASSORDER: 0|DESCR: $description|GROUP: |INDEX: $incremental|MODE: write|FIELDMODE: write');

 

and so on.

However, is there any documentation out there which describe these functions? It's pretty hard work digging through the logfile searching for all types of queries and which values go where :) Especially the last text variable (comment) in the function.

Also, am i on the right track?

 

Final result will be an SQL file to create the database schema without dumping existing database schema's. Not to mention when you create a database and notice a small error in the classname, for example administators instead of administrators. Currently the only solution is to delete the class and re-create the class. I know all of you probably did this dozens of times ;)

 

I hope this is a good step into the right directory for schema import

s

 

(oh and finally where has the default attribute value gone to? in older versions there was an option to insert a default value for an attribute, but it's gone in the current version)

[patrick.d]

First step into import export of LookUp types is done!

 

Background: i want a flexible method to import/export data. As i've seen that this is a much requested feature, i would like to consider this my first contribution. As i'm creating my models i want to import and export data between my D-database to my T-database. Ideally i would like to develop seperate schema's, such as Assetmanagement, Nagios, Cacti. I want to import those without hindering other already present schema's

 

So when importing lookup's i have to take into account the Id key of the table and i must also update the Sequence accordingly. Luckily CMDBuild does a fine job for us, just as long as we omit the Id column in the SQL insert statements. PSQL also has a nice feature called COPY {table}

 

Step 0: Define all tables, lookups and domains with a unique prefix. I used ALMA_ (actually i got this nice hint from looking at togaf extension)

 

Step 1: copy all records into a CSV (yes i look the liberty of ordering my data ;) )

copy (select "IdClass", "User", "BeginDate", "Code", "Description", "Status", "Notes", "Type", "ParentType", "ParentId", "Number", "IsDefault" from "LookUp" where "Type" LIKE 'ALMA_%' ORDER BY "Type" ASC, "Number" ASC) TO '/tmp/lookupdump.csv' WITH CSV HEADER;

 

Step 2: import the data from the CSV

copy "LookUp" ("IdClass", "User", "BeginDate", "Code", "Description", "Status", "Notes", "Type", "ParentType", "ParentId", "Number", "IsDefault") FROM '/home/administrator/lookupdump.csv' WITH CSV HEADER;

 

Voila, a nicely (ordered) copied lookup table.

 

Next: classes  (actually that's just 

pg_dump --format=plain --no-owner --schema-only --table='public.*"ALMA_"*' cmdbuild > classdump.sql )

Next: a nicer way of defining classes  :)

As a note, i would still like to know whether or not i'm on the right path for classcreation with cm_create_class() and how the content of the last text field should be created......

[tecnoteca]

You are doing great work ! But it can also be dangerous, because if anything is missing (triggers, constraints, etc) then the resulting database will be corrupted.
We use our SQL functions cm_create_class (), cm_create_domain (), etc. only when we have to bring structural changes from the test database to the production database. In that case, however, the list of the SQL functions to be performed can be retrieved in the dd_log file (you can find the configuration at the end of the log4j.conf file in the folder WEB-INF\conf), ready to be executed.
To create a new database we prefer draw the E-R model with an E-R editor, check it, modify it several times, etc, and then when it is the final version configure it with the Administration Module GUI (it is a more tedious work but safer.…)
CMDBuild Team

[patrick.d]

Missing triggers and constraints, that’s exactly the reason why I’m searching for the correct queries To be honest it’s a pitty that we need to be working through that interface. It’s rather slow and not RSI friendly

.

 

As for an E-R editor, any good (FOSS) editors that you could suggest? Right now i'm using Visio for some basic modelling, but that's just pure ....

[tecnoteca]

The administration interface may not be very friendly, but it certainly is not slow.
Maybe your server is misconfigured or your client does not have enough RAM.
As editor you can use MySQL Workbench (similar to ERwin), or yEd (similar to Visio).
CMDBuild Team

[patrick.d]

Previously Tecnoteca wrote:

The administration interface may not be very friendly, but it certainly is not slow.
Maybe your server is misconfigured or your client does not have enough RAM.
As editor you can use MySQL Workbench (similar to ERwin), or yEd (similar to Visio).
CMDBuild Team

 

Just to be clear. Offcourse it's fast, but when you create 20-30 classes and 15 or so domains and lookups you are spending quite some time in the interface, reordering attributes and what not. You can easily spend hours inside the administration module.

 

True, i'll probably spend more time automating it, but that's just part of the fun :D

[jame.agada]

Are you successful. it really is a tedious exercise.

Previously Patrick wrote:

Previously Tecnoteca wrote:

The administration interface may not be very friendly, but it certainly is not slow.
Maybe your server is misconfigured or your client does not have enough RAM.
As editor you can use MySQL Workbench (similar to ERwin), or yEd (similar to Visio).
CMDBuild Team

 

Just to be clear. Offcourse it's fast, but when you create 20-30 classes and 15 or so domains and lookups you are spending quite some time in the interface, reordering attributes and what not. You can easily spend hours inside the administration module.

 

True, i'll probably spend more time automating it, but that's just part of the fun :D