Author Topic: Database export/import plugin  (Read 25696 times)

Hervé

  • Hero Member
  • *****
  • Posts: 369
Database export/import plugin
« on: April 15, 2016, 14: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é

Phil Beauvoir

  • Archi Master
  • Hero Member
  • *****
  • Posts: 922
    • Archi
Re: Database export/import plugin
« Reply #1 on: April 17, 2016, 22:38:07 PM »
@JB I can set this up if you like. What user permissions do I need to set?
If you value and use Archi please consider making a donation! https://www.archimatetool.com/donate

Jean-Baptiste Sarrodie

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 414
  • Archi Evangelist and Contributor. Visionary.
    • About me...
Re: Database export/import plugin
« Reply #2 on: April 18, 2016, 19:53:56 PM »
Hi,

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

I've just created a database-plugin repo. Now I need to know you GitHub username to give your R/W access.

Regards,

JB
If you value and use Archi please consider making a donation! https://www.archimatetool.com/donate

Hervé

  • Hero Member
  • *****
  • Posts: 369
Re: Database export/import plugin
« Reply #3 on: April 20, 2016, 19:50:21 PM »
Hi Jean-Baptiste,

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

Best regards
Hervé

Jean-Baptiste Sarrodie

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 414
  • Archi Evangelist and Contributor. Visionary.
    • About me...
Re: Database export/import plugin
« Reply #4 on: April 20, 2016, 19: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
If you value and use Archi please consider making a donation! https://www.archimatetool.com/donate

Hervé

  • Hero Member
  • *****
  • Posts: 369
Re: Database export/import plugin
« Reply #5 on: April 25, 2016, 03:12:56 AM »
No worries.

I was not in a rush, I was on vacation and just came back  ;)

Hervé

  • Hero Member
  • *****
  • Posts: 369
Re: Database export/import plugin
« Reply #6 on: May 02, 2016, 08:13:45 AM »
Dear all,

A version 0.2 of the plugin is available on GitHup : 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é

Jean-Baptiste Sarrodie

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 414
  • Archi Evangelist and Contributor. Visionary.
    • About me...
Re: Database export/import plugin
« Reply #7 on: May 02, 2016, 08: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
If you value and use Archi please consider making a donation! https://www.archimatetool.com/donate

bdendulk

  • Active Member
  • ***
  • Posts: 34
    • Homepage company
Re: Database export/import plugin
« Reply #8 on: May 06, 2016, 19: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

prgee

  • Active Member
  • ***
  • Posts: 62
Re: Database export/import plugin
« Reply #9 on: May 08, 2016, 00:34:34 AM »
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 then press the "Save File" button to save it in the plugin directory.

Good Luck!
Peter

Hervé

  • Hero Member
  • *****
  • Posts: 369
Re: Database export/import plugin
« Reply #10 on: May 08, 2016, 23: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é
« Last Edit: May 08, 2016, 23:15:58 PM by Hervé »

bdendulk

  • Active Member
  • ***
  • Posts: 34
    • Homepage company
Re: Database export/import plugin
« Reply #11 on: May 09, 2016, 12:53:48 PM »
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

Hervé

  • Hero Member
  • *****
  • Posts: 369
Re: Database export/import plugin
« Reply #12 on: May 09, 2016, 13: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é

bdendulk

  • Active Member
  • ***
  • Posts: 34
    • Homepage company
Re: Database export/import plugin
« Reply #13 on: May 09, 2016, 21: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

Hervé

  • Hero Member
  • *****
  • Posts: 369
Re: Database export/import plugin
« Reply #14 on: May 10, 2016, 03: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é