Disable automatic DDL script generation for specific entities and many-to-many relations

We currently have several entities and many-to-many relations that map to custom database views instead of database tables. With database views we solve following issues:

  • Realize deep relations: E.g. for dependency A -> B -> C we created a view that represents the many-to-many table between A and C so we can model an additional (read only) direct relation A -> C
  • Custom Entities that do some calculations (e.g. count the number of related entities)

We struggle in CUBA studio since CUBA studio will create table and index generation scripts. It would be a good feature to either disable the DDL script generation for sepcific entities and many-to-many tables or to define custom (View generation) scripts.

This would also allow to define entities that are at same time many-to-many relations (see https://www.cuba-platform.com/discuss/t/suggestion-in-studio-let-us-edit-the-join-table-for-many-to-many-relationship)

Now you can disable DDL generation for an entity if you specify a table (or view) with a name not starting with the <namespace_> prefix. That is Studio tracks only DB objects that conform to this naming convention.

But you are right, we should make this mechanism more explicit.

Thx a lot. Works perfectly.

And for situations where we want to define a dedicated additional entity besides the many-to-many relation we use now synonyme tables without the <namespace_> prefix. E.g. in SQL server

Create Synonym CUSTOMER_ORDER_LINK
for <namespace_>_CUSTOMER_ORDER_LINK

CUSTOMER_ORDER_LINK will not be tracked by CUBA studio.

I have similar situation. But tables are in other databases (in same instance of mssql server)

This works for relation in main datastore but CUBA Studio generate DDL scripts for this table.


@DesignSupport("{'imported':true,'unmappedColumns':['Position']}")
@AttributeOverrides({
        @AttributeOverride(name = "id", column = @Column(name = "ElementId"))
})
@NamePattern("%s|title")
@Table(name = "AdditionalDBase.SSCommon.STElements")
@Entity(name = "sales$STElements")
public class STElements extends BaseIdentityIdEntity {
...

Maybe CUBA Studio recognized this “static” table by annotation, like this:


@DesignSupport("{***'createDLL:false'***}")

Marcin

p.s. Sorry for my english

Hi Marcin,

Thank you for the feedback. This feature is scheduled for release 6.6, see STUDIO-3647.

:ticket: See the following issue in our bug tracker:

https://youtrack.cuba-platform.com/issue/STUDIO-3647

Hi Konstantin,

I have the opposite problem, we will leave with the new project of porting our ERP towards Cuba. We have decided to keep the current database schema, so we should necessarily generate the entities starting from the scheme we have. However, for the addition of new fields and new tables I would like to use the automatic generation of sql scripts, starting from the new entities created. However, we can not currently use this function because our internal table name creation rules are not compatible with the schema used by Cuba. Our tables are CamelCase and without any prefix and we would like to keep this style. I think the DDL generation should be independent of the table schema name. Is it possible to remove this constraint and simply keep the Generate DDL option checkbox ?

Could be interesting to have a setting on how to automatically generate the name of the tables and fields, for example follow options:

UPPER_CASE
PREFIXPROJ_UPPER_CASE
LOWER_CASE
prefixproj_lower_case
CamelCase
PrefixProjCamelCase

Hi
I’ll share my personal advice on this matter, based on my past experiences with other platforms/frameworks, and inherent B2B integration issues, and progressive migration from legacy systems to new ones.

First of all, this is a dangerous path… Unfortunately, I have yet to see a project succeed when taking this approach. I mean, a big project, where the only viable approach is progressive migration from the legacy system to a new platform. I’ll elaborate this a bit more in the end of this post.

But the most important point is: Never ever try to use the same database tables by giving read/write access to multiple applications at the same time.
You should always have a single point of truth for your business logic, where the business rules/constraints are enforced for every consumer involved. This can be in any form, like REST services, SOAP/XML services, a CUBA middleware or (please don’t do this) a bunch of Stored Procedures in your DB.

Additionally, you should try to avoid going against what your target platform offers you.
So, let CUBA generate the DDL for you, and use other means to achieve interoperability with existing data and consumers. Every mature RDBMS out there gives you a plenty of options in this regard, and MS Sql Server is among them.

This is definitely an interesting option to add to Studio, but for the time being you could manually edit both the table name and columns when creating them. I know this is less convenient, but it works, and must be done only once at entity creation. In the end, you’ll not spend much time in entity creation, compared to the other programming tasks.
Again, I suggest you to stick with the default naming convention, and create new schema from scratch.
What does really matter here is consistency, not the actual convention used. And you have plenty of options to use in your RDBMS if you need alternate names (Views, Stored procedures, Synonyms, …).

Now, some more words on the progressive approach I was referring to before.
By progressive I mean separating the legacy monolith into smaller units, develop them one at a time (or more if you have a big enough team), and put them live along with the legacy application, by ensuring proper interoperability.

Here is an exemplified process that outlines the approach (please note that is only a high-level view, the actual steps can be more articulated):

  1. Take the legacy system, and spend some (can be much) time analysing it with the goal of a better modularisation. The outcome should be at least a diagram that decompose the system into smaller cooperating modules, that can be developed and tested as discrete units.
  2. Take first a “module” that has as less dependants as possible, that is a module in the outer area of the above diagram (ideally it should have “zero” dependants, that is no “dependency arrows” pointing to it in the diagram)
  3. MSSQL specific: create a NEW schema in your target database for the new application (be aware of this), OR create a completely new DB for it (it depends on many factors, outside of the scope of this post)
  4. Create your application module in CUBA, but carefully choose the root package and namespace because changing it afterwards is really really hard…
  5. Model your entities from scratch, without much regard of the old system. You should pretend starting from scratch, and so spend time analysing and documenting your requirements first, then model the entities accordingly.
  6. Pay particular attention to the dependencies of this new module. If it depends on other modules not already developed, you must decide how to consume the legacy data in your new code. Using read-only views mapped to the legacy data is a viable option.

Now for the hardest part - the integration with existing consumers (the following process can run in parallel with the previous one):

  1. Document all the consumers (clients) of the legacy application: the application itself, external tools, reporting services, external parties (if you have some B2B online or batch data migrations)
  2. For each of the above consumers, spend some time documenting how they interact with the legacy database(s). The most important property to document is if they are readers, writers or both (r/w access)
  3. For the readers (for example report services) the solution is very straightforward: develop appropriate views or stored procedures that mimic the old schema, using the new tables as the underlying source. A less easy task, but more powerful in complex cases, is to develop either triggers and/or batches to migrate data from the new schema to the legacy one (this can be done selectively, only if a view or SP would be too complex for a particular object)
  4. For the writers… well you should exclude them, because maintaining a 2-way sync at the data level is really hard. Not impossible, but requires effort. If the legacy consumers are only GUI applications, it’s better to invest some time by making the data that now is produced by the new component read-only in them.

Obviously this approach (maintaining both the new and the legacy app in production) has several drawbacks for the users, first of all the need of using two different applications in day-to-day work.

Hope this helps you in your future choices :wink:

Bye
Paolo

1 Like

As described in my initial post we are using database views to realize deep relations: E.g. for dependency A -> B -> C we created a view that represents the many-to-many table between A and C so we can model an additional (read only) direct relation A -> C

Till version 6.9.0 it was possible to name the view without namespace prefix + Set it as join table for the many-to-many association -> There was no ddl created by CUBA Studio. In version 6.9.0 cuba studio creates database scripts for all many-to-many tables even the table has no namespace prefix.

Is there any workaround? Could it be possible the disable DDL generation for join tables in future (as it is possible for entities already)?