Archi Forum

Discussion Boards => General Archi Discussion => Topic started by: Hervé on April 15, 2016, 13:37:31 PM

Title: Database export/import plugin
Post by: Hervé on April 15, 2016, 13:37:31 PM
Dear all,

I'm currently developping a database export/import plugin.

The first release will only allow to store models in a central PostGreSQL repository.

But my roadmap includes the following functionalities :
   - enable other database brands (at least Oracle, MySQL, ...),
   - allow to search models (by name, by component, by owner, ...),
   - allow relations between models,
   - manage locks (kind of check-in / check-out),
   - manage versions (track who does what, show a previous version or even rollback to a previous version, ...),
   - provide statistics,
   - manage credentials (reports only, read/only, read/write, administrator tasks, ...),
   - map technical objects to CMDB CIs (as my company uses ServiceNow as CMDB provider, I will reuse my ServiceNow plugin),
   - etc ...


Jean-Baptiste, could you please create an archicontribs folder on gitHub for this plugin (org.archicontribs.database) ?

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: Phil Beauvoir on April 17, 2016, 21:38:07 PM
@JB I can set this up if you like. What user permissions do I need to set?
Title: Re: Database export/import plugin
Post by: Jean-Baptiste Sarrodie on April 18, 2016, 18:53:56 PM
Hi,

This seems really interresting, I'm looking forward testing it !

I've just created a database-plugin repo (https://github.com/archi-contribs/database-plugin). Now I need to know you GitHub username to give your R/W access.

Regards,

JB
Title: Re: Database export/import plugin
Post by: Hervé on April 20, 2016, 18:50:21 PM
Hi Jean-Baptiste,

My GitHub user is herve91 (the same as Snow plugin)  ;)

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: Jean-Baptiste Sarrodie on April 20, 2016, 18:54:36 PM
Hi,

Ah, yes! I forgot you also worked on this plugin too!

I've just added you as admin on this new repo.

Regards,

JB
Title: Re: Database export/import plugin
Post by: Hervé on April 25, 2016, 02:12:56 AM
No worries.

I was not in a rush, I was on vacation and just came back  ;)
Title: Re: Database export/import plugin
Post by: Hervé on May 02, 2016, 07:13:45 AM
Dear all,

A version 0.2 of the plugin is available on GitHup : https://github.com/archi-contribs/database-plugin (https://github.com/archi-contribs/database-plugin)

The current version is able to :
   - Export and import models to a database (PostGreSQL and MySQL drivers but MySQL has not been fully tested)
   - Version the models (keep an history on the model versions and allow to retreive a former version)
   - Show up how many Archi components are exported and imported (kind of statistics)

The current limitations are :
   - Not all the Archi components are managed (like sketches and images)
   - Text fields (model purpose, components documentation, ...) are limited to 65535 chars
   - Models are independent (it is not possible create relations between models)

My roadmap includes the following functionalities :
   - Of course, manage all Archi components (like sketches and images)
   - Enable other database brands (at least relational ones like Oracle but eventually nosql ones),
   - Allow to search models (by component name, description, property, by date, by owner, ...),
   - Allow relations between models,
   - Manage locks (kind of check-in / check-out),
   - Manage credentials (read/only, read/write, administrator tasks, ...),
   - Map  objects to CMDB CIs (as my company uses ServiceNow as CMDB provider, I will reuse my ServiceNow plugin),
   - etc ...

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: Jean-Baptiste Sarrodie on May 02, 2016, 07:34:10 AM
Hi,

Really interresting. I haven't had time to test yet but had a quick look on code. It seems that you save the whole model each time and don't do any merge (you first delete all related lines and then save it). If this is the case this means that in fact your plugin doesn't allow two people to work at the same time (if both import the model and change it, the version kept will be the latest saved, thus loosing changes done by the first user). Am I wrong?

Another comment or in fact a question: have you tried the Grafico plugin ? I'm asking because the way it works could be reused to manage all elements and view types (canvas, sketch...) : the logic is pretty simple as it's mainly a loop on all EObjects (whatever their exact class) followed by a serialization. Currently serialization is done to a file, but it would be easy to do it in a DB (one can imagine a generic table with columns: ID, Name, Class, XML_Serialization). The main advantage is that potential ECore changes almost never impact plugin code.

I would be happy to further discuss all this with you, as we might converge and end up with a feature full plugin.

Regards,

JB
Title: Re: Database export/import plugin
Post by: bdendulk on May 06, 2016, 18:45:52 PM
Hi,

I would like to test the database plugin but when I put it in the plugin dir of Archi, it doesn't show up. Is there a different dir where it should be placed?

Kind regards,

Bart den Dulk
Title: Re: Database export/import plugin
Post by: prgee on May 07, 2016, 23:34:34 PM
Hi,

You may have had a problem with how it got downloaded. The file you copied to the plugin directory should be 1.52 MB (1,604,198 bytes) in size. On Windows you may also have to right-click on it to open the property sheet and "unblock" it.

If not, you can click this link: https://github.com/archi-contribs/database-plugin/raw/master/org.archicontribs.database_0.2.jar (https://github.com/archi-contribs/database-plugin/raw/master/org.archicontribs.database_0.2.jar) then press the "Save File" button to save it in the plugin directory.

Good Luck!
Peter
Title: Re: Database export/import plugin
Post by: Hervé on May 08, 2016, 22:10:51 PM
Dear all,

The version 0.3 of the plugin is available on GitHub. I added a filter on the import dialog that allows to show up models (and versions) that match certain conditions :
     - element type or name
     - relation type, source or target
     - property name or value

I also prepared buttons to change a model's ID and delete a model from the database but they aren't working yet.

My next priority is to manage to import several models from the database in a single Archi model and allow relations between them and of course increase the components types imported and exported.

@prgee, you only have to download the .jar in the "plugins" directory of Archi. Please let me know if you continue to have issues.

In the coming days, I will also create a wiki on GitHup to provide more details on how this plugin works.

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: bdendulk on May 09, 2016, 11:53:48 AM
Hi,

The plugin now shows up in Archi. I do get an error that the connector is not found but I will read through the posts on Github (I remember seeing a question on this subject).

Kind regards, Bart
Title: Re: Database export/import plugin
Post by: Hervé on May 09, 2016, 12:07:35 PM
Hi,

The jar I released on GitHup was buggy (I forgot the MySQL library in the dependency and made a mistake in the driver initialization). I corrected and deployed a new jar on gitHub (with the same version 0.3).

Please let me know if you still experience issues.

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: bdendulk on May 09, 2016, 20:43:44 PM
Hi Herve,

Connecting is now possible. However, I experience problems in the tables when I try to export. It complains about tables not found after I ran the SQL script. After editing the script to give the tables capitalized names (only the first letter) most table errors disappear. One table remains problematic: when i capitalize the first letter, the error messages says a table is missing. The table name is then without capitals. When I change is, the error is reversed ... (see attached image).

One thing might be an issue, I'm running MariaDB (which is a fork of MySQL). It comes with my NAS (Synology) and provides a handy network based database server.

Kind regards,

Bart den Dulk
Title: Re: Database export/import plugin
Post by: Hervé on May 10, 2016, 02:20:11 AM
Hi Bart,

To be honnest, I'm developping on Windows and on PostGreSQL so case sensitivity was not an issue for me so I've written the table names with some capitals in order to ease my code reading.

MySQL on Linux systems is case sentitive by default. I don't know if it is easy to change the lower_case_table_names system variable on a Synology NAS so I will change my code to use only fully lower case table names. It will ease portability.

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: prgee on May 10, 2016, 17:52:30 PM
Hi Hervé,

I just downloaded your 0.3 version and have been doing some tests (I'm on Windows 7 using PostgreSQL) and it's looking very good. I have a couple of large Archi models so I'm interested in seeing how the export/import performance is.

Great piece of work!!

Thanks and Regards,
Peter
Title: Re: Database export/import plugin
Post by: Hervé on May 10, 2016, 18:36:08 PM
Hi Peter,

Please remember that the plugin is not able to handle all the Archi components at this stage (if some of them are not exported, it will tell you).

I'm interested to have your feedback about performances and please do not hesitate to drop me a line if you discover a bug (I'm sure thay are plenty of them) or require some functionality.

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: bdendulk on May 10, 2016, 20:41:53 PM
Hi Herve,

I've looked into my MariaDB server settings. It is using a charset which is case insensitive. I have no clue whether it is possible to make the NAS as a whole case insensitive. I will start using a local MySQL stack om my PC.

Kind regards,

Bart
Title: Re: Database export/import plugin
Post by: Hervé on May 10, 2016, 20:45:29 PM
Hi Bart,

Please don't ... You may instead download the plugin version 0.4 that I compiled for you : I updated my code to use lowercase tablenames everywhere ...

Please let me know if this doesn't solve your issue ...

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: bdendulk on May 10, 2016, 22:41:42 PM
Hi Herve,

No luck. I still get the error message after I get the screen where I can specify which model I want to export. I reckon it has something to do with MariaDB.

Kind regards,

Bart
Title: Re: Database export/import plugin
Post by: cadriano.goncalves on May 11, 2016, 18:41:38 PM
Hi all,

I'm testing archi, and in my point of view, one aspect that is missing is a plugin like this one.

I'm trying with ubuntu 14.04 and MySQL 5.5.47 and i had the same issue related with "Model" and "model"... it seems that is related with DBExporter.java:

...
// we remove the old components (if any) from the database
         for(String table: asList("Model", "archimatediagrammodel", "archimateelement", "diagrammodelarchimateconnection", "diagrammodelarchimateobject", "relationship", "point", "property"))
            DBPlugin.sql(db, "DELETE FROM "+table+" WHERE model = ? AND version = ?", modelSelected.get("id"), modelSelected.get("version"));
...

Regards,
Carlos Gonçalves
Title: Re: Database export/import plugin
Post by: Jean-Baptiste Sarrodie on May 11, 2016, 19:29:06 PM
Hi,

Quote from: cadriano.goncalves on May 11, 2016, 18:41:38 PM
I'm testing archi, and in my point of view, one aspect that is missing is a plugin like this one.

Please don't get me wrong, no criticisme intended, but I have to make some comments:

These are the reasons why I created the Grafico plugin: the way the model is "exploded" in a bunch of XML files is done using some Eclipse features that are not linked to way Archi work with the model and thus changes at model (Ecore) level have only few impacts on the plugin. And the goal being to use Git, this means that there is no need to manage locks or checkin/out features making it useable as a simple import/export plugin.

So my conclusion could be summarized:

And the conclusion of the conclusion is that it would be better if all people that want/are able to contribute on this topic work together on the same direction in a collaborative way. A first step would be to discuss what we want to do with such feature, what we really call "collaborative work"...

Regards,

JB
Title: Re: Database export/import plugin
Post by: Hervé on May 11, 2016, 19:57:13 PM
Dear Bart, dear Carlos,

I effectively had forgotten to lowercase the tablename in one location  :-[ I've uploaded a new JAR (still version 0.4) that has been corrected. I installed MariaDB on my Synology and succedded in exporting and importing a model  :D

Dear Jean-Baptiste,

Yes you are right. At the moment, the plugin is only able to store models into a database but it already solves few points (at least for my needs) :

The other points are in my roadmap.

I'm not naive, I know that my plugin is only an export/import plugin  ::). Besides, I'm not a Java developer (everybody who read my code knows that  ;D) and I'm learning Java at the same time I develop the plugin (Thanks Google by the way). My aim is only to extend Archi to my own needs and bypass the key limitation : it works with individual XML files.

I tried Grafico. I created a GitHub server on a virtual server and installed a gitHub client on my desktop. But each time a conflict arised on an XML file, Java style comments were automatically added into the XML file with the data from both XML files (the one from my desktop and the one from the repository). Of course, the XML structure was not kept and the whole model was corrupted. I spent quite a lot of time searching how to disable this behavior but I failed. I know it may be a competence issue coming from me, but as I'm not a developer I do not master developer tools. That's why I decided to create my own plugin.

Once again, my aim was to fulfill my own needs ... but I'm fully available to discuss about features ...

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: Jean-Baptiste Sarrodie on May 11, 2016, 20:56:22 PM
Hi Hervé,

Some comments:

"The models are centralized in a single location" : that can be achieved with a simple shared folder
"The lodels are versionned" : can be done with sharepoint, Git, SVN...
"It is possible to search models by element, relation or property" : that's a really nice feature!

"Besides, I'm not a Java developer (everybody who read my code knows that  ;D) and I'm learning Java at the same time I develop the plugin (Thanks Google by the way). My aim is only to extend Archi to my own needs and bypass the key limitation : it works with individual XML files."

I'm not myself a developer so I can really understand you. The point is that because we are not developers we have to minimize the effort to achieve our goal. IMHO using a DB instead of a simple XML file doesn't solve anything at all.

"I tried Grafico. [...] But each time a conflict arised on an XML file [...]"

If conflict arise, that means that two users tried to both edit the same model "at the same time". Storing the model in a DB doesn't address this point: at best you'll end up with two versions that aren't merged. In addition, I think we should discuss (maybe private email discussion in french) why you had conflicts and what tools you used, because first I almost never faced conflicts (separation of work in my team has always prevented such issues) and Git is able to merge conflicts in more than 50% of the cases.

"That's why I decided to create my own plugin"

I understand and really respect this, But (unfortunately there's a but) you're the 4th or 5th user that has worked on this topic in the past 5 years. All previous attempts started with the easy part (storing into a DB instead of a file) but failed short when trying to address the hard part (the merge of changes). That's why about 3 years ago we had some discussion with other users to understand how to manage it and the best solution we came up with was to explode the model and use a source code management system. The first attempt (done by Michael Tapp) exploded the model in a bunch of YAML files but did so in a way that was too tighly linked to the way Archi store informations and thus was limited to only a subset of Archi's features and broke when some changes occured. I myself tried 2 implementation before Grafico but it took me almost one year to find what I think is the best approach (taking current constraints into account): rely on Eclipse EMF serialization feature to create small XML files and let Git manage the hard work.

Of course Grafico is currently in beta and only manages the import/export feature, not the merge itself, but IMHO it would be easier to contribute some UI (like you did for your plugin) and add the Git layer through JGit (pure java git implementation) than to work on a DB based implementation with its own merge feature. I truely believe that merge feature is something hard and that we should not reinvent the wheel.

I think you've done a really great work and I'm sure you've spent several nights and week-end working on it. So If there is a chance that we (and other) can work together to achieve our goal I'd like us to at least try.

Regarding the goal itself, I think we should at first define the features and not the technical solution we can imagine. So IMHO a good collaborative solution for Archi would be a solution that allows me:

Of course, the technical solution should be as simple as possible and not (too) dependant of the way Archi store information (or even which informations Archi stores).

Regards,

JB

PS: On the other hand, that's the typical thing that Phil could develop if he was paid for...
Title: Re: Database export/import plugin
Post by: Hervé on May 11, 2016, 21:30:52 PM
Wow ...

When I started the plugin, I didn't expect to generate such a polemic ...

Hervé
Title: Re: Database export/import plugin
Post by: Phil Beauvoir on May 11, 2016, 21:54:10 PM
Well, it's great to see such a level of interest in this initiative. :-)
Title: Re: Database export/import plugin
Post by: Hervé on May 11, 2016, 22:42:09 PM
For information, the functionality I'm currently working on is the ability to import several models and allow relations between them. Technically, this is achieved by allowing to import models in separate folders of a unique Archi model and exporting a folder as a separate model.

That is the simple part. The tricky point I'm working on is that I would like that:
   --> People importing a model should be "aware" of other models update that have an important impact on their model
   --> People exporting a model should be "aware" of the important impact their new version will have on other models

but I begin to have some ideas on how I will manage this  ;D

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: Jean-Baptiste Sarrodie on May 12, 2016, 14:52:18 PM
Hi,

Quote from: Hervé on May 11, 2016, 21:30:52 PM
When I started the plugin, I didn't expect to generate such a polemic ...

It wasn't meant to be a polemic, sorry if it seems so.

Quote from: Hervé on May 11, 2016, 22:42:09 PM
For information, the functionality I'm currently working on is the ability to import several models and allow relations between them. Technically, this is achieved by allowing to import models in separate folders of a unique Archi model and exporting a folder as a separate model.

That is the simple part.

Well... This is for sure a very interresting idea. In some aspects this looks like an UML Package.

I'm not so sure it is the simple part though :-) The complexity I can see is that you first have to define what folder you are talking about as there are no links between folders that group model elements from different layers: you have no way to be sure that the model is well splitted at all layers and that relationships are coherent. If you consider diagram folder, then there can obviously be some common elements between views included in different folders. So it can be simple to split the model (using folders or other criteria) but merging them back can be an issue.

Another potential issue if "just" merging model snippets into another model is that you have to make sure that element ids are uniq (should be ok most of the time but could fail if a sub-model has been created through a CSV import that defines Ids).

Maybe you don't know, but this is in fact a feature included into EMF. An EMF model can reference elements through proxies, a proxy being defined by the "path" to another model serialization file and the Id of the referred element. Archi has not been created at the begining with such feature in mind (and thus can't use proxies asof now) but this could be done with some developments.

Quote from: Hervé on May 11, 2016, 22:42:09 PM
The tricky point I'm working on is that I would like that:
   --> People importing a model should be "aware" of other models update that have an important impact on their model
   --> People exporting a model should be "aware" of the important impact their new version will have on other models

I think I got you but could you provide some examples you have in mind?

It seems that it is time to create a new page on the wiki to capture all these really good ideas :-)

Regards

JB
Title: Re: Database export/import plugin
Post by: Hervé on May 12, 2016, 15:33:03 PM
Hi Jean-Baptiste,

In fact, I'm implementing two import modes :

I solved the ID conflict possibility by changing the ID : all the components imported from the database to Archi have got an ID compounded of 3 fields : its own ID and the ID of the model it belongs to (which is compound of its own ID and a version number).

I've seen how Grafico manages proxies and I got around by importing components in the correct order. I cannot guarantee that it will work forever, but at the moment I do not need proxies for the twenty-ish models I've created so far at work. Should I experience issues when importing components, then I will learn about those proxies.

All the other cases might not be well managed, or even not managed at all ... Nevertheless, the plugin will ease my life on a daily basis  8)

Best regards
Hervé

Title: Re: Database export/import plugin
Post by: Jean-Baptiste Sarrodie on May 12, 2016, 15:45:27 PM
Hi,

Quote from: Hervé on May 12, 2016, 15:33:03 PM
All the other cases might not be well managed, or even not managed at all ... Nevertheless, the plugin will ease my life on a daily basis  8)

That's the main point and goal :-)

Re proxy: in fact Grafico doesn't manage them, or should I say I added code to managed them because I didn't want to manage it the EMF way (and also because at first I used a YAML serializer that just doesn't support proxies). Import order work if you don't manage view references: in such simple case, importing elements, then relationships then views works, but this is no more enough for view references because you can cannot predict the right order (and in worse case you can have circular references). As Grafico manages all elements type (archimate but also sketches notes and images) I had no other solutions.

Do you think it is possible to use a SQLite JDBC driver instead or MySQL or PostgreSQL ? This could help testing your plugin without having the need for a real DB server...

JB
Title: Re: Database export/import plugin
Post by: Hervé on May 12, 2016, 16:09:05 PM
Hi,

As long as I do not have to rewrite all my SQL request, it should be easy to add the SQLite drive. Else, I will learn SQLite synax  ;D

I'll try and let you know.

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: bdendulk on May 12, 2016, 19:28:41 PM
Hi Herve,

I'm running into a new problem. It is related to the use of tags for custom Jasper reports. I use them quite extensively to make reports based on a single view instead of reporting the whole model (see the forum for discussions on this subject). Apparantly the export plugin stumbles on these.

My model is on GITHUB, hence you can have look and/or test the pluging with my model. The content is in dutch but that shouldn't be a problem since the content is not an issue here ::)

See attached screenshot for error message (the number at the end is the version of the export). '_Project' is the property name that is used to filter a certain view for reporting. I have more than 20 different properties in use with a value for each view that is used in different reports.

Kind regards,

Bart den Dulk
Title: Re: Database export/import plugin
Post by: Hervé on May 12, 2016, 20:48:57 PM
Hi Bart,

Thanks for your feedback. I'll  investigate on your error and correct it. Properties are supposed to work but as I do not use them a lot, I may have a bug ...

Could you please provide me with the download link of your model ?

Best regqrds
Hervé
Title: Re: Database export/import plugin
Post by: bdendulk on May 12, 2016, 21:19:37 PM
Hi Herve,

Sorry, I meant to include it in my last post but I apparantly got distracted.

https://github.com/bdendulk/NVWA_CA_BLIK

Kind regards,

Bart
Title: Re: Database export/import plugin
Post by: Hervé on May 12, 2016, 21:38:43 PM
Hi Bart,

Thanks for the link. I downloaded it and reproduced on my desktop. I shall be able to provide a working version of the plugin for your model within the next few days  :D

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: Hervé on May 17, 2016, 00:53:34 AM
Dear all,

The plugin version 0.5 has just been released on GitHub : https://github.com/archi-contribs/database-plugin (https://github.com/archi-contribs/database-plugin)

You've got some screenshots on the Wiki : https://github.com/archi-contribs/database-plugin/wiki (https://github.com/archi-contribs/database-plugin/wiki)

The key updates are:
The shared mode is not finished. Even if it allows to import several models into separate folders, it is not yet possible to include elements from a model to another one ... but it eventually will (because this is my main objective  ;D).

Of course, my code is not optimized at all (I prefer working on adding functionalities), so import and export takes quite a long time at the moment.

Please note that the datamodel has to be upgraded so either you scrach you database and create a new one using the scripts on GitHub, either you need to update the tables manually. I apologize but this is still an alpha release after all. I believe there will be other database upgrades in the coming weeks (at tleast to optimize).

Please feel free to drop me a line with your feedback  ;D


@bdendulk, I tested the plugin against your Grafico model and it seems to work. Could you please check it on your side ? You need to upgrade the datamodel I'm afraid.

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: bdendulk on May 17, 2016, 06:15:38 AM
YES!!!

(see sccreenshot for evidence ;-))
Title: Re: Database export/import plugin
Post by: Hervé on May 17, 2016, 06:59:16 AM
Hi Bart,

Thanks for your feedback ...

But please do not declare victory too soon, I haven't had enough time double check that nothing has been forgotten  ;) I need to compare your model imported through Grafico and through my plugin ...

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: prgee on May 21, 2016, 15:24:08 PM
Hello Hervé,

I just wanted to give you some results on my test of ver 0.5 of your plugin. I have a very large model that I have been working with that contains almost every possible Archi feature. It took about 1.5 minutes to export to an "out-of-the-box" PostgreSQL 1.16.1 installation on Windows 7. However, some things did not export as noted in the attached screen shot. I was not able to re-import the model due to a large number of missing connections. I do have a couple of suggestions:

- display some sort of progress message to indicate how far along the export/import is
- if a problem is encountered with the export/import then offer the user the option to "cancel" or "continue"

That's all for now.

Regards,
Peter
Title: Re: Database export/import plugin
Post by: Hervé on May 21, 2016, 15:59:54 PM
Hi Peter,

Thanks a lot for your feedback and your suggestions. I do not have such complex models and the duration you've got tells me to give more priority to optimization.

The missing connexions should disapear in a next release as I will use "proxies" as Jean-Baptiste suggested and showing a progress indicator is definitively a good idea.

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: Hervé on May 22, 2016, 21:48:24 PM
Dear all,

The version 0.6 of the plugin is available on GitHub : https://github.com/archi-contribs/database-plugin

You've got some screenshots on the Wiki : https://github.com/archi-contribs/database-plugin/wiki

The key updates are :

Beware, the datamodel changed from previous version.

In the next release, it will be possible to import several models at the same time, and eventually recursively import model dependencies (a slider is already in place in the import popup to limit the recurse depth). I will also replace the statistics popups by a new one that will provide more "real time" information about the import/export process.

I tested the plugin against my own models and against Bart's but I know that does not cover all Archi features. therefore I'm interested if some people can provide me more test models.

@Peter, As your model includes all Archi features and is quite big, I would be interested to have your model, if it does not contain confidential information of course. It would definitely help me  :)

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: prgee on May 23, 2016, 14:44:27 PM
Good Day Hervé,

I tried exporting the same "test" model that I did previously but got the attached error message. As for your request for a copy of the file, it does contain confidential data but I will create a version of it with masked values so you can use for testing. I'll try to get that for you in the next day or two.

Thanks and Regards,
Peter
Title: Re: Database export/import plugin
Post by: Hervé on May 23, 2016, 18:37:54 PM
Dear Peter,

I've got the same error message. I've been too much focused on performances issues yesterday that I messed up a bit. I just uploaded a new JAR on GitHub (version 0.6c) that should solve this issue.

At the moment, it takes me 2.3s to export Bart's model (which is the biggest model I've got so far) when it takes me more than 12s to import it back. This means that I've got an improvement ratio of 5:1. If you needed 1.5 minutes to export your model, then it means that it would take 10ish minutes to import should no error popup shows up ... Optimization is now my number one priority  :D

Using your huge model, it will be much easier to calculate where I'm spending time in my code and therefore, where to concentrate my effort. So thank you very much in advance for your effort in providing me an anonymized version of it ...

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: Hervé on May 24, 2016, 22:59:53 PM
Dear all,

I uploaded the version 0.7 of the plugin on GitHub (https://github.com/archi-contribs/database-plugin (https://github.com/archi-contribs/database-plugin)).

This version has no new functionality and works with the same datamodel as version 0.6 ... I only concentrated on Java code optimization.

The export method is still the same speed (arround 2.3s to export Bart's model) but the import method duration has been divided by 5 and is now the same speed than the export method (around 2.3s instead of 12s before).

I believe there still is a lot to do regarding optimization and look forward to receiving Peter's model to work on a huge model.

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: prgee on May 25, 2016, 15:06:17 PM
Hello Hervé,

I have a version of that large Archi model ready - we just need to figure out how to get it to you. I have placed it on my Google Drive so if you have a gmail or other Google account I will grant you access. I also have a GitHub account if that would be easier to use.

Regards,
Peter
Title: Re: Database export/import plugin
Post by: Hervé on May 25, 2016, 19:16:55 PM
Hi Peter,

I just sent you me gmail address in a private message.

Thank you very much for the model.

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: Hervé on May 26, 2016, 20:21:15 PM
Hi Peter,

I downloaded your file.

Thank you very much.

It takes me 9 minutes and 20 seconds to open your .archimate file in Archi on my desktop ... I'm impressed. It is really huge !

So my objective for the next version is to manage your model. My priorities are therefore :

This one may take a bit more time than the former ones ;)

And of course, the datamodel will change, once again but I hope this will be the last time ...

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: Hervé on June 05, 2016, 22:19:06 PM
Dear all,

Please find below some information about the plugin development ...

So, as you see, it will take a bit longer than I expected. But be patient, i'll do my best to release something next weekend  :D

Best regards
Hervé


ps: Thanks again Peter, your model is very helpful  ;) By the way, how did you manage to get a DELETED folder after the views ? It's not a standard Archi folder, is it ?
Title: Re: Database export/import plugin
Post by: prgee on June 10, 2016, 16:18:26 PM
Hello Hervé,

Glad to hear the model is useful for testing. It grew way to large to manage so I ended up breaking it into smaller models - one which contained only the current-state model tree and a few basic views (e.g. organization, capabilities, principles, etc.) and several others related to various project-related and future-state views. However, in doing so it became more important to be able to manage inter-dependencies from several related model files. It seems that you are addressing this requirement in your database repository approach.

As for the DELETED folder, that seems to be something that was created automatically in earlier versions of Archi but was later dropped. Perhaps it was unnecessary because Archi provides "undo" and "redo" functionality. Maybe Phil could verify this.

Thanks and Regards,
Peter
Title: Re: Database export/import plugin
Post by: Hervé on June 10, 2016, 18:21:15 PM
Hi Peter,

Yes, I confirm, allowing dependencies between models is one of my objectives. It is achieved by importing different models into separate Archi folders of the same model and therefore you will be able to move a component between models by moving them between folders, using components of one model in a view of another model, and even create relations between components that belongs to different models. Then, the export process split back the "big model" into separate models which is quite easy as each of them are inside separate folders.

I need this functionality at work so it has to work !

By the way, I optimised my code, and my plugin is now about 3 times quicker to import your model from a SQLite database comparing to open the archimate file (3 minutes 19 seconds versus 10ish minutes). Unfortunately, showing up a progress bar and refreshing it may slow it a bit but I hope the import will stay below the 4 minutes ...

I hope I shall be able to deliver a version at the end of the weekend.

By the way, I had a look to your Internet site and to the PDF that is generated using Archi. I also wish to be able to generate documents as you do. Do you know a good site that describes how to start using Jasper Reports (like Jasper Reports for dummies) ? I begin to have a look and it seems very complicated to me ...

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: cadriano.goncalves on June 13, 2016, 13:53:52 PM
Hi Hervé,

After applying the version 0.7 of the plugin, the following error appears:

column "archimateelementid" specified more than once

This error comes up with postgresql and with mysql (with a new and clean database).

Best Regards,
Carlos
Title: Re: Database export/import plugin
Post by: Hervé on June 13, 2016, 16:35:07 PM
Hi Carlos,

Thanks for your feedback. I do not remember having this issue but I will release the version 0.8 very soon (tonight in fact if everything goes well).

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: Hervé on June 14, 2016, 09:31:50 AM
Dear all,

My apologies but I still have got some issues to manage images. I found a way that works in my dev environment (Eclipse) but doesn't when the JAR is created and copied to the plugins folder.

I won't be able to release the version 0.8 before few days.

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: Hervé on June 16, 2016, 21:02:17 PM
https://github.com/archi-contribs/database-plugin (https://github.com/archi-contribs/database-plugin)).

Installation instructions and an embryonic wiki is available on GitHub.

What works :

There is one known bug (but as I did not have much time to test it thoroughly, I count on you to find more) :

Pre-requisites :

My next priority (except to correct bugs that will necessarily arise) is to allow to use elements from one model into another one, or create relations between elements of distinct models. Basically, this is nothing more that enabling to load and save part of a more global model. The point is that now that models can be imported as folders of a single model, it is already possible to do such things but the import and export procedures need to manage them correctly.[/li]
[/list]

After that, I will study the possibility for several people to work on the same model at the same time. I just discovered that it is possible to subscribe to events that are raised each time an Archi component is updated. Therefore, it will be possible to share those updates with the other people working on the same model (technically easy, but do not know the performance impact). Of course, the tricky part will be, as usual, to manage concurrency (when 2 people are updating the same component at the same time). But imaging having a view opened and see in real time the components added/modified/moved, removed by other people. I am thinking about the ability to set a lock on components when someone whishes to be the only one allowed to update a component (kind of check-in/check-out).

But it's a bit too early to have a clear picture of what I wish to achieve here. I would be interested to have your opinion on this subject ...

Please do not hesitate to drop me a line should you have any question or comment, found a bug or require a new functionality that is not already in my roadmap.

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: theunsfvo on June 20, 2016, 13:05:51 PM
Great work Harve... this is what makes open source projects like this so awesome and successful.

I will see if I can help testing and start using it from my side...

Excellent contribution...  ;D
Title: Re: Database export/import plugin
Post by: prgee on June 20, 2016, 13:28:44 PM
Good Day Hervé,

Thanks for the update - I'm starting to do some testing on your ver 0.8. So far, I have discovered one small issue. Phil has added a test for invalid characters to the CSV import code so it will no longer process ID's which contain ".". This interferes with your default version numbering scheme which uses a "0.01" type format. I've been able to work around that by entering my own version numbers when I export to the DB but it's probably simple to change your numbering format.

Thanks Again!!
Peter

Title: Re: Database export/import plugin
Post by: Phil Beauvoir on June 20, 2016, 13:56:39 PM
Peter, it turns out that "." characters are OK for identifiers. I've just committed a change to GitHub that allows them to be used in IDs.

Phil
Title: Re: Database export/import plugin
Post by: prgee on June 20, 2016, 16:01:21 PM
Thanks for the speedy response Phil. I'm smiling again ;D

Hervé, please consider this issue resolved.

Thanks and Regards,
Peter
Title: Re: Database export/import plugin
Post by: Hervé on June 20, 2016, 17:43:48 PM
I like these kind of issues that solve themselves  ;D

More seriously, I knew that I may one day be confronted to this kind of issue because I am not supposed to modify the ID of objects, especially as I only develop an export/import plugin that is supposed to be non-intrusive ...  but I did not find any other way.

Thanks Phil for your update.

And thanks theunsfvo for your encouragement :D
Title: Re: Database export/import plugin
Post by: Phil Beauvoir on June 20, 2016, 17:46:45 PM
I can change the code if it makes sense, and doesn't impact other things.

The only issue is when a new build comes out. I'm trying to solve that problem too with automated builds. So far I have a local Maven and and Ant build process. I'm trying to deploy that to a Jenkins CI process.
Title: Re: Database export/import plugin
Post by: Hervé on June 20, 2016, 21:57:33 PM
Wow, I'm very far from it, much more "manual". I definitely need to industrialise my process  :-[
Title: Re: Database export/import plugin
Post by: Hervé on September 22, 2016, 22:05:02 PM
Dear all,

After quite a long time I must admit, I'm happy to announce that the version 0.10 of my plugin is available on GitHub (https://github.com/archi-contribs/database-plugin (https://github.com/archi-contribs/database-plugin)).

Installation instructions and an embryonic wiki is available on GitHub.

What's new :

The plugin has been compiled and tested against the versions 3.3.1 and 3.3.2.

I will update the GitHub wiki in the coming days.

Please note that I updated a bit the datamodel. If you previously created the database, you may manually create the 4 new columns in the diagrammodelreference table. New people may safely use the database scripts provided on GitHub.

Please do not hesitate to drop me a line should you have any question or comment, found a bug or require a new functionality that is not already in my roadmap.

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: Phil Beauvoir on September 23, 2016, 10:26:01 AM
Congratulations!
Title: Re: Database export/import plugin
Post by: Hervé on September 28, 2016, 22:33:59 PM
Thanks Phil,

But I've been a bit too much enthusiastic and just corrected few things. The version 0.10b is available on GitHub (https://github.com/archi-contribs/database-plugin (https://github.com/archi-contribs/database-plugin)).

The key changes are :

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: pbelkin on October 06, 2016, 11:21:07 AM
Hi Herve,

Thank you for your work.
I tried plugin to export data in russian to my_sql db with UTF8 charset.
Russian letters appears incorrectly as '????????'

Maybe you can add option to choose charset (as it done in csv export in archi) in future release?
It would be very helpful

Best regards
Title: Re: Database export/import plugin
Post by: Hervé on October 06, 2016, 11:33:52 AM
Hi,

Thanks a lot for pointing me this bug.

I'll try to do some testing regarding UTF8 beacuse it should definitively work. At the same time I'll have a look to the CSV plugin to check how they've done.

Would you be able to provide me with a sample archimate file (only few elements are needed) that does not exports corretly to your database that I can check if my correction works ?

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: pbelkin on October 07, 2016, 14:13:54 PM
Hi,Hervé

I created simple model "Модель" with one application component with name "Пример текста на русском языке".

Best regards,
Pavel
Title: Re: Database export/import plugin
Post by: Hervé on October 07, 2016, 14:16:33 PM
Thank you very much Pavel.

I'll have a look this weekend.

Best regards
Hervé

Title: Re: Database export/import plugin
Post by: Hervé on October 09, 2016, 20:15:03 PM
Hi Pavel,

I exported your model and imported it back. I checked with PostGreSQL, MySQL (MariaDB) and SQLite databases.

In all cases, the data stored in the database was in Cyrillic and the imported model was still using Cyrillic characters.

Could you please check that your database is correctly configured to use UTF8 characters ?

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: pbelkin on October 11, 2016, 11:15:38 AM
Hi, Herve

Thank you for trying my model.
It was a problem with charset configuration on my db server.

Best regards,
Pavel
Title: Re: Database export/import plugin
Post by: Hervé on October 11, 2016, 15:20:01 PM
Hi Pavel,

Good news as I thought I had another bug in my plugin. Please do not hesitate should you have other questions ...

A new version will be released in the coming days, including few bugs correction and this new release will be available for Archi 3 and Archi 4 for those who downloaded the source code and compiled it  :D

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: Hervé on October 15, 2016, 07:54:28 AM
Dear all,

I'm happy to announce that the version 0.11 of my plugin is available on GitHub (https://github.com/archi-contribs/database-plugin).

It is now available for Archi 3 and Archi 4.

The Archi 3 plugin is not able to import Archi 4 models, but the Archi 4 plugin is able to import Archi 3 models with the following caveats :

What's new in this release:

If you are using the plugin for the first time, I provided tables creation scripts on GitHub.
If you are already using the plugin, it is safer to configure a new database and import your .archimate files but if you prefer to upgrade you existing database to the new datamodel, please check the FAQ on the GitHub wiki : https://github.com/archi-contribs/database-plugin/wiki (https://github.com/archi-contribs/database-plugin/wiki).

Of course, please do not hesitate to contact me should you have any question or comment.

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: Hervé on October 24, 2016, 21:01:28 PM
Dear all,

I'm happy to announce the version 0.12 of my database plugin which is available on GitHub (https://github.com/archi-contribs/database-plugin (https://github.com/archi-contribs/database-plugin)).

It is available for Archi 3 and Archi 4 for those who downloaded the sources of Archi and compiled it.

The key updates are :

I'm currently completing the wiki (https://github.com/archi-contribs/database-plugin/wiki (https://github.com/archi-contribs/database-plugin/wiki)) to provide some information about the plugin, please do not hesitate to read it and tell me if some information you require is missing.

As a reminder, to install the plugin, you just have to :
, launch Archi and . It is as simple as that  ;D.

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: Hervé on October 25, 2016, 09:31:34 AM
By the way, JB or Phil,

Would you add a line about my plugin in the "Archi Extensions and Plugins" of the http://www.archimatetool.com/resources (http://www.archimatetool.com/resources) page ?

Thanks
Hervé
Title: Re: Database export/import plugin
Post by: Phil Beauvoir on October 26, 2016, 07:47:55 AM
Quote from: Hervé on October 25, 2016, 09:31:34 AM
By the way, JB or Phil,

Would you add a line about my plugin in the "Archi Extensions and Plugins" of the http://www.archimatetool.com/resources (http://www.archimatetool.com/resources) page ?

Thanks
Hervé

Hi,

when I get back from The Open Group Paris meetings I will do this.

Phil
Title: Re: Database export/import plugin
Post by: Hervé on October 26, 2016, 10:13:12 AM
Thanks a lot Phil :)
Title: Re: Database export/import plugin
Post by: Phil Beauvoir on October 28, 2016, 10:07:15 AM
Quote from: Hervé on October 26, 2016, 10:13:12 AM
Thanks a lot Phil :)

Done. http://www.archimatetool.com/resources

BTW - The Archi website needs updating and a makeover. I will do this in the coming weeks, and I want to put a greater emphasis on the developer community, the Archi roadmap and other things.

Phil
Title: Re: Database export/import plugin
Post by: brain_work on November 16, 2016, 05:18:19 AM
I got this error when I use the Database export/import plugin with Archi 4 Beta 3 on Windows after putting the correct org.archicontribs.database_v0.12(archi4).jar in the plugins folder and starting Archi 4:

!ENTRY org.archicontribs.database 4 0 2016-11-16 06:12:38.531
!MESSAGE FrameworkEvent ERROR
!STACK 0
org.osgi.framework.BundleException: Could not resolve module: org.archicontribs.database [41]
  Unresolved requirement: Require-Bundle: org.eclipse.core.resources

   at org.eclipse.osgi.container.Module.start(Module.java:444)
   at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.dispatchEvent(ModuleContainer.java:1536)
   at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.dispatchEvent(ModuleContainer.java:1)
   at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:230)
   at org.eclipse.osgi.framework.eventmgr.EventManager$EventThread.run(EventManager.java:340)

The java -version info:

java version "1.8.0_101"
Java(TM) SE Runtime Environment (build 1.8.0_101-b13)
Java HotSpot(TM) Client VM (build 25.101-b13, mixed mode, sharing)
Title: Re: Database export/import plugin
Post by: Hervé on November 16, 2016, 08:20:52 AM
Hi,

Some people are effectively affected by this message even if I failed to reproduce it when I did a fresh install on one laptop at work. Nevertheless, I recompiled the JAR file using different parameters and it seemed that it solved the error.

Please try again with release 0.12b that I just deployed to GitHub ...

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: brain_work on November 16, 2016, 09:16:28 AM
Thanks Hervé,

I don't see any errors anymore with the new jar and I can see the database plugin dialogs in Archi  :)

Unfortunately I can not test the plugin because I don't have access to a database at this moment (only Microsoft Access 2010). So I will see if I can do something with SQLite...

Regards,
Peter
Title: Re: Database export/import plugin
Post by: brain_work on November 16, 2016, 11:45:49 AM
Hello Hervé,

The export of a simple model worked with Archi 4 Beta 3 to a SQLite db. Later today I will try the import.

Regards,
Peter
Title: Re: Database export/import plugin
Post by: Hervé on November 16, 2016, 12:00:08 PM
Thanks Peter.

At the moment, the datamodel creation is manual (running a SQL script) but I'm currently working to create the tables directly from the plugin (and of course update the datamodel from a plugin version to another).

Please do not hesitate to drop me a line should you have any other question or comment.

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: brain_work on November 22, 2016, 14:08:45 PM
Hi Hervé,

Would it be possible to add Microsoft SQL on Azure, see https://docs.microsoft.com/en-us/azure/sql-database/sql-database-get-started) as an option to the databas plugin?

With regards,
Peter Bakker
Title: Re: Database export/import plugin
Post by: Hervé on November 22, 2016, 15:31:40 PM
Hi Peter,

I'll have a look and should be able to provide you with something quite quickly.

I'll check the datamodel against SQL Server Express Edition then, connecting to an Azure database should be quite straight forward.

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: brain_work on November 22, 2016, 15:46:45 PM
Hi Hervé,

Thanks for the quick response :-)

Regards,
Peter
Title: Re: Database export/import plugin
Post by: Hervé on November 23, 2016, 23:12:14 PM
Hi Peter,

The version 0.13.0 has been released on GitHub : https://github.com/archi-contribs/database-plugin/tree/master/database%20plugin%20for%20Archi%204 (https://github.com/archi-contribs/database-plugin/tree/master/database%20plugin%20for%20Archi%204)

The following updates have been made since version 0.12b:

I also added the SQL script to create the required tables in the database : create tables (Microsoft SQL Server).sql

I tested against the Linux version of SQL Server (Microsoft calls it vNext) as it is the closest version of what you may have in Azure, but I use a local connection so I'm not sure about Azure parameters (do you need a cert to authenticate for instance ?) so I may need to adapt few things depending on your tests.

As well, I supposed that you do not used named instances (as it was easier for me), but if I guessed wrong, please tell me and I will add an "instance" field.

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: brain_work on November 24, 2016, 07:40:32 AM
Hi Hervé,

Thanks. Azure is new for me too :-)  I just created a SQL Database on Azure and because I'm also not a database/SQL expert it might take some time for me to test your new version. But I'll do my best :-)

Regards,
Peter
Title: Re: Database export/import plugin
Post by: brain_work on November 24, 2016, 09:47:00 AM
Hi Hervé,

The connectivity with the Azure SQL database works :) And I didn't need to do anything special in the Archi preferences or on my laptop itself. So for your plugin it works the same as a local connection.

I tested export and import with an Archi model with some 1000 objects. Export took about 2 minutes and import about 1 minute. But that will of course vary based on the quality of the internet connection.

Regards,
Peter
Title: Re: Database export/import plugin
Post by: Glem on November 24, 2016, 09:53:40 AM
Hi Hervé,
on the v0.13 db plugin with MySQL:
- delete version is ok
- in standalone mode,
            1/ export a model with directory is working fine
            2/ import a model with directory too.
- in shared mode
            3/ import model with directory working fine
            4/ export model with directory working !!!
            5/ import model: An error occured while importing the model from the database.
                The model imported into Archi might be incomplete, please check below :
                  Exception : importConnections() : cannot find relationship 220da1bf
             all views are lost.

You are very near !! Congratulation.

Feel free to get in touch with me for any detail.

Best regards,
Gilles

Title: Re: Database export/import plugin
Post by: Hervé on November 24, 2016, 10:44:49 AM
Thanks very much guys for your tests.

@ Gilles : I will try to reproduce the relationship issue and if I fail, I will contact you to help me investigate.

@ Peter : until now, I've written my SQL requests for local database, so I'm doing a lot of unitary selects or inserts and am counting on the cache to improve performance. I will rework my procedures to reduce them. But this will consume more memory and I need to pay attention to that (we still use 32bit operating systems at work).

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: Hervé on November 24, 2016, 22:15:24 PM
Hi Gilles,

I exported and imported several times in shared mode few models that I use for my tests, one of them having more that 83.000 elements and relationships (thanks again Peter (Prgee)  ;))

and I cannot reproduce the error message.

Is your model confidential or can you send it to me by email ?

Best regards
Hervé
Title: Re: Database export/import plugin
Post by: Hervé on December 02, 2016, 15:43:37 PM
This post is just to provide an update on the testing made with Gilles on the "shared" mode :

Some history : by design, Archi models are impervious : it is not possible to reference an element from a model into another one. But it would ease my life if I could create a model with standard and common components and reference them in other models.

Thant's the reason why I introduced the "shared" mode in the database plugin that allows to import several models into a common container model. To acheive that, it creates one sub-folder per imported model in every category folder (Strategy, Business, Application, ...) and then store the components into their model's subfolder.

Unfortunately, when a new element or a new relationship is created, it is by default created in the root of the category folders. I haven't (yet) found a way to automatically move the elements in the proper sub-folder.

This means, that prior to export back the models inside the database, the elements should be moved in the proper sub-folder.

Of couse, I'm working on finding a way to automate this move but until then, one should move manually the elements (thing that I was doing because I knew this limitation but that Gille was not doing because this is not documented ... my bad). Not doing this move results in "orphans" elements that failed to be imported afterwards  :(



Here is a small screen capture to help understand the sub-folders ...

Best regards
Hervé