I'd like to work on the database directly

Started by MantaCH, January 17, 2019, 17:13:28 PM

Previous topic - Next topic

MantaCH

Hi,

I use Archi since several months and I recently discovered the database plugin. I so mounted a SQL DB dedicated to my model and exported the Archi model in it. Everything works perfectly and I thank you for that ;).

Now, I've got questions :


  • May it possible I write a script that creates occurrences in concerned elements, properties and relations tables directly (and then import the DB to the Archi model) instead of passing through the importing process of the three csv files to Archi and then export the Archi model to the DB ?
    I need it because it will be simpler for me to update data from information I get back from an external csv file containing new artifacts and existing ones (for updates) in my model.
  • If yes, how may I manage the unique identifier (in the DB) that Archi deals with normally? Does the ID engine be run in the DB instead of using Archi for that part?
  • About artifact versioning in the DB now: do you now how I should manage to uniquely deal with the last version of my artifacts in the DB as the plugin manages versioning about each artifact update made in the model when exported to the DB?

Many thanks in advance :).
Manta

Hervé

Hi Manta,

You're welcome.

Please find below the answers to your questions:

Quote1. May it possible I write a script that creates occurrences  ...

Yes you can. I designed the database for that purpose and I personally generate elements and relationships using Talend Open Studio.

The only points of attention are:
   - each and every component must have a unique ID
   - components are not meant to be updated, but a new version must be created with the new values
   - version must start from 1 (zero is reserved for the internal plugin usage)

Quote2. If yes, how may I manage the unique identifier ...

IDs are strings and nor Archi nor the plugin do any assumption about its format. When I extract data from my CMDB to create Archimate components in my database, I personally keep the CMDB internal ID, this way I no not need to maintain any conversion table.

Quote3. About artifact versioning in the DB now ...

There are several methods depending on what you manually did to your model components in the database:
   1- if you created some components then you create new versions of your views and model. The new model version will then be visible on the import windows.
   2. if you updated components but did not create new ones, then you end up with new versions of components that are already declared in your model. When you import a model, you can specify the specific version to import (in that case, you import the components as they were in this specific model version) os ask to import the latest version and in this case, the latest version of all the components is imported to Archi.



You can find the database structure here: https://github.com/archi-contribs/database-plugin/tree/master/v2/database%20structure
You can find more details about the plugin behaviour here : https://github.com/archi-contribs/database-plugin/wiki or in the plugin help pages that can be accessed using the interrogation mark on every window of my plugin)

Please do not hesitate if you've got other questions  :)
Best regards
Hervé

MantaCH

Hi Hervé,

First of all: Many thanks for your answer. That's great, really.

After reading your answers I'd like to have more information about the following points :

About point 1.

a)
Quote- each and every component must have a unique ID.

Ok, unique ID is crucial ;) but does something exist to let Archi dealing with this ID (as when I import a new element in Archi with the elements.csv file, if I let the field "ID" blank : "" then Archi will calculate a new unique ID for me. As I'd like to let this responsibility to Archi I'd like it continues to provide it but when I create a new record in the elements table. May I have to activate something in the DB or use something special to perform it ?
Of course if not I'll search for something else to take this responsibility... as you already precised in your first answer (point 2.): you personally keep the CMDB internal ID.

b)
Quote- components are not meant to be updated, but a new version must be created with the new values

So if I well understand, I can't modify existing components with my scripts, I have always to create a new one and deal with its version. Right ?

If yes, does something exist (a function in your plugin or your database I can use) to calculate the new version of this component or may I find the last version myself and add 1?
And what about the other fields that are present in the concerned tables? I'm speaking about the ones that aren't present in the csv files of Archi in fact... I really don't know what to write in them.

About point 3.
Quote
1- if you created some components then you create new versions of your views and model. The new model version will then be visible on the import windows.   
2- if you updated components but did not create new ones, then you end up with new versions of components that are already declared in your model. When you import a model, you can specify the specific version to import (in that case, you import the components as they were in this specific model version) or ask to import the latest version and in this case, the latest version of all the components is imported to Archi.

I'd just like to add components (artifacts with properties and relations) but no new views, and update existing components if needed. My inputs are coming from a csv file I will handle through scripts.
If I well understand I only deal with version of components, if new the version is 1.

And then when I import the DB (in last components version) I'll have all of them (new and updated ones) with all their properties and relations. I'll just have to create in Archi the views I need, but for example if I open the visualiser panel and click on a component (artifact) I'll have its schema with all of its relations and linked artifacts. Is it right ?

Sorry for these (perhaps) newbie-questions but even with the different documentations I already read I'm quite a little lost with all of these...

Again : Many thanks in advance for answers :)
Best Regards
Manta

Hervé

Hi Manta,

1 a) Unfortunately, you cannot leave the ID value as null or empty in the database as the ID is a primary key. But as said, Archi (nor my plugin) does not care about the ID format. So you need to generate your own ID. The actual versions of Archi generate 36 chars length string under the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, but Archi can deal with any string as ID, so it's not mandatory to follow the same rules. The database implement a varchar(50) to allow this ID to grow in the future.

I can provide you the algorithm used by Archi if it makes you feel more comfortable ;-)

b) In fact, I didn't mean you can't, but that I suggest not to do it. In fact, one of the plugin's objective is to keep an history of all the components updates and rollback if necessary. So if you update existing components using SQL requests without creating new versions, the plugin will not be able to know it :-) The version is a simple numerical value, so to add a new version of a component, you simply need to calculate for this component "MAX(version)+1".

3 ) New components must effectively have 1 as version (never zero). Elements and relationships that are in you model are visible in the visualizer, without distinction if they've been created using archi or using a SQL request directly in the database.

But, even if generating components using SQL requests is not complication, it still is quite complex.

The pre-requisite is of course to have a valid database. Tables creation can be automated using the check button in the plugin's preference page in Archi.

Model creation
You first need to create a model in your database. As you can see in the model tree (on the left handside of Archi), an empty model contains 9 folders and a default view. The default view is not mandatory and you do not need any view to let's discard it. The folders, in the opposite, are mandatory.

So you need to insert:
   - 1 line in the "models" table with a unique ID, and 1 as the version if it's a new model, or latest version +1 if it's a new version
   - 9 lines in the "folders" table, each with a unique ID, and 1 as the version if it's a new folder, or latest version +1 if it's a new version
           - as a general rule, you may consider that the folders version is the same as the model version
   - 9 lines in the "folders_in_model" tables, one per folder

Alternatively, you can create an empty model in Archi and export it to the database.

Component creation
You then need to create your Archimate components.

For each of them, you need to insert:
   - 1 line to the "elements" or "relationships" table with a unique ID, and 1 as the version if it's a new component, or latest version +1 if it's a new version

Adding the components in the model
At last, you need to add the components to your model. To achieve that, you need to link the element to a model but indicate as will in which folder the element should be stored. Please be careful as you must calculate the correct folder depending on the type of component : Strategy folder for ressources, Capability and course of Action; Business folder for business elements; and so on ...

To achieve that, please insert:
   - 1 line in the "elements_in_model" table or "relationships_in_model" with the component ID and version, the model ID and version and the folder ID.

There is no need to provide the folder version in this table as the plugin will get the folder version from the "folders_in_model" table.

Alternatively, you may import elements to any model in Archi by right click on your model, then select "import components from database". You can then select the types of components you wish to import.

Properties
You may add properties to any model, folder, view, element or relationship by inserting:
   - 1 line per property in the "properties" table, specifying the corresponding object ID and version.

Checksums
Every object in the database (models, folders, views, elements, relationships, ...) have got a checksum that allows the plugin to know if it has been updated between an import and an export. This way, only updated components are exported in the database and therefore have got a new version created.

If the checksum column has got dummy data, then when you ask the plugin to export back you model to the database, it will indicate that all your components have been updated and will generate a new version for them with the correct checksum.

If you do not plan to export back your model to the database, then you do not need to care about this checksum.
But if you plan to update your components in Archi and export back the updates to the database, then you've got 2 options:
   - option 1: you do not care if the plugin create a new version for all the components, so you do not need to care about this checksum.
   - option 2: you do care and in this case I can provide you with the algorithm used to generate this checksum



I know that my plugin's documentation is far from complete and especially about working with the database outside of Archi. Very few people asked me about it. So please do not hesitate and fell free to ask me all the questions you need. I'll be happy to answer.

Best regards
Hervé

MantaCH

Hi Hervé,

What an answer ! A great one, thanks a lot :)

It's just a little post to let you know I read your post with attention this morning and see that I'll have to take each point and adapt my functional procedures with all these information. Depending of my needs, I'll perhaps ask you the different algorithms you spoke about. Thanks for your proposal.

So I'll work on these things and will certainly come back to you with questions... thanks really for your availability ;)

....... to be continued :D

Hervé

You're welcome ... Please do not hesitate  ;)

MantaCH

#6
Hi Hervé,

I move forward in my specifications, I am about to insert new components in tables and so I think I'll need to deal with the checksum field.
As you proposed it, may I ask you to provide me the algorithm used to generate it?

I think I'll be interested too in having the algorithm used by Archi to generate the IDs. May I ask you to provide it to me too ?

Another thing :
i) Several tables have a checksum field you spoke about, so ok. But others (as relationships_in_model) have a "rank" field. What is it for ? I don't understand what to do with it...

ii) About the elements_in_model table :
As no elements_in_model.version exists I thought I just have to deal with an occurrence I just have to update the elements_in_model.element_version field to follow my changes in elements table and so point to the new version of the element (I just changed) : telling that now it's this version that is part of the model.
But when I executed this request (select * from [dbo].[elements_in_model] eltim where eltim.element_id = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx') : I found 3 lines with 2 different eim_id.
I'm a little lost here ;)


Many thanks in advance for you insights.
Manta

Hervé

Hi Manta,

Archi algorithme to generate UUID (Unique identifiers) is:
import java.util.UUID;

...

    do {
        id = UUID.randomUUID().toString();
    }
    while(fUsedIDs.contains(id));

...


My algorithm to calculate checksum is to concats the objects properties and then calculates a MD5 on that string. It is provided by the DBChecksum class: https://github.com/archi-contribs/database-plugin/blob/master/v2/sources/src/org/archicontribs/database/data/DBChecksum.java

The rank column is a simple integer value that allows to order the import process. For instance, when you import a folder from the database into a model, then this folder may be a subfolder of a parent folder. I therefore must ensure that the parent folder has been imported before the subfolder. I've got the same kind of requirement for relationships as a relationship must source or target another relationship so this source or target must already exist, ... and so on.

(Philippe and JB tried to explain to me that the Eclipse framework does have the notion of proxy that avoid to manage that order, but I failed to learn how it works and therefore had to manage that order manually).

As for the elements_in_model table, I do not understand as this table has got versions in it. Basically, this table lists all the elements included in the models ... or should I say it lists all the versions of the elements included in each version of the models.

If your request returns 3 lines, it means that your element is present in 3 different models (or 3 different versions of the same model).

If you wish to list all the elements in a specific version of a model, then your request would be:
select element_id, element_version from elements_in_model where model_id = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' and model_version = x

Then, for each of them, you can retrieve the corresponding elements from the elements table (sqlite syntax, you may adapt a bit for SQL Server):
   - if you want to get the elements as they were when that specific model version has been created -->
SELECT
*
FROM
elements
WHERE
(id, version)
IN (
SELECT
element_id,
element_version
FROM
elements_in_model
WHERE
model_id = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'
AND model_version = x
)


   - if you want to get the latest version of those elements (even if they've been updated after that speciific model version has been created) -->
SELECT
*
FROM
elements
WHERE
(id, version)
IN (
SELECT
element_id,
(SELECT MAX(version) FROM elements WHERE id = element_id) v
FROM
elements_in_model
WHERE
model_id = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'
AND model_version = x
)


Should you need, we can arrange for a Skype meeting where I can provide you with more effective guidance :-)

Best regards
Hervé

MantaCH

Hi Hervé,

Many thanks for all of that  ;) So I'm working on all your add on and will certainly come back to you again :)

I try now to complete my functional procedures document. And of course we can use Skype but I've some job before to disturb you again  ;D I let you know.

Best Regards
Manta

Hervé