Attach External Databases


(Ivan Martinez) #1

HI People,

it’s possible to attach an external SQL Database of an existing Financial program, and make relation with database created on CUBA (in SQL) and the attached database?

It’s my first project in Cuba, i like this tool. In the past, we are working with VS Lightswitch, and i have an application that communicates our financial tool, with the application. We are searching, for replace this tool with Cuba, but i cannot see any option to attach one or more databases and make relations with them.

One needed relation, it’s give customers from financial database on an entity of WorkItems (for tecnhicial tool) related.

Thanks for your help :wink:


(Konstantin Krivopustov) #2

Hi Ivan,

Let me quote myself from another similar topic:

Currently, CUBA application can map entities only to a single database, which is connected through the datasource located in JNDI by “jdbc/CubaDS” name (by default).

You can have additional datasources to connect to other databases, but you have to work with them through JDBC, or MyBatis, or create your own persistence unit for EclipseLink using standard JPA facilities (the last option is not yet tested). In order to display these data in standard CUBA UI, you can map the data to non-persistent entities of your standard data model."

Could you explain in more detail, what do you mean by relation with an additional database? How it worked on Lightswitch?
And what DBMS do you use?


(Ivan Martinez) #3

Hi Konstantin, at this moment in Lightswitch we have an application database, and then we have 3 external databases attached,

I attach a word document with some captures:

Cuba, it’s a very good tool, but i mean that need only to connect visible for work and create views, or screen to other databases for work directly to these databases.

yellow.docx (227.3K)


(Konstantin Krivopustov) #4

I’m afraid you cannot do the same in CUBA application. You have to write quite a lot of custom code to map data from additional databases to UI. We will try to address this issue in the future.
Could you provide a simplest use case for this feature? I mean some explanation what entity is in the main database, what is in the additional, how they are linked, how you display them in UI and whether you store changes back in the databases.


(Ivan Martinez) #5

Hi Konstantin, would you like too see in production, our VS LS application ?

If you prefer a teamviewer meeting to see our project there are’nt any problem.

For us, it’s bigger change, but, only this feature freezes our decision to migrate from Lightswitch to Cuba.


(Konstantin Krivopustov) #6

That would be great. I’ll send you my contact details to arrange a meeting.


(Jose Alberto Sanchez Nieto) #7

+1 We also have the need to read from external databases and mix data with the database of the application Cuba.
Greetings.


(Konstantin Krivopustov) #8

Hi All,

Let me summarize the requirements:

  1. Ability to define connections to additional databases.
  2. Ability to create persistent entities mapped to tables from additional databases.
  3. Ability to work with these entities in UI and on middleware in the same way as with entities from the main database.
  4. Entities from different databases can be linked, i.e. an entity from DB1 can have a reference to an entity from DB2.
  5. When Studio reverse-engineers tables from databases, the structure of tables should remain untouched. It means that simplest CUBA entities should not require the creation of additional database columns.

We are going to implement these requirements in CUBA v.6.3 and Studio v.2.3 which is scheduled for September 2016.

We appreciate your feedback and comments!


(Ivan Martinez) #9

It’s great to hear this! I look forward to this new functionality!

:slight_smile:


(Paul Wills) #10

Here are my comments on ability to connect to multiple databases.
Top priority for our use cases are items 1, 2, 3, 5. Preferably using JPA connections.
We would not need item 4 other than an audit log in DB1 would need to point to an entity in DB2. However, to have more strict reference (similar to a foreign key) would not be required in our case.

Thanks for the great work your doing


(Ranes Kumar) #11

Looking forward for the 6.3/2.3 release.
Does #5 also mean that existing primary key of the table will continue to be treated as primary key?

Thanks & Regards
Ranes


(Konstantin Krivopustov) #12

This is the case in the current version (6.2/2.2) too. Exceptions are:

  • Composite keys are not supported and replaced with a UUID key if there are no references to them.
  • MS SQL and MySQL identity keys are replaced with Long keys populated from sequences.
    Our goal is to remove these exceptions, but honestly, the whole #5 it is the most difficult part of the issue.

(Ivan Martinez) #13

Hi, for us we need to CUBA platform support external databases without changes. “As it”.

Normally, has certain Primary Key or Primary Keys in certains tables, and we need to make relation that this has trasnferred exactly as original database.

Remnember that Original Attached Databases, has the possibility to have changes , new fields, new tables (from original application of these database), and we need an option to update schema on Cuba to retrieve de new changes.

And we need to make relations on the original databases, and these relations has been saved on Cuba Platform.

Thanks!


(Konstantin Krivopustov) #14

Implemented in 6.3.