Schema constraints

[Tony Giaccone <to...@giaccone.org>]

Andrus,

We have a cayenne data model that we use in our app. We use Postgres as the
application DB in production and H2 for testing.  To create the database in
production, we use DB Deploy. We've captured the SQL that the modeler
generated and used that as part of the process. Through the process of
doing this it's become clear there are somethings it would be nice to have
in Cayenne.

1) Capture generated SQL to a text file.  This would make the process
slightly more efficient. Easier then a copy paste edit to a text file.

2)  There are constraints that we use, that it would be nice to have in the
model. So that these constraints could be applied in H2 when the test
database is spun up.  I could see this as just a set of place holder SQL
statements that are executed after the tables and sequences are generated.
This could also include things like index creation.

We've had test cases pass in dev because the constraints we needed hadn't
been applied to the H2 database.

I realize that you're a volunteer organization, and that the best way to
get this done would be to do it myself. :-) Still I thought I'd make the
suggestion just to see what you think.


Tony

Re: Schema constraints

[Andrus Adamchik <an...@objectstyle.org>]

Hi Tony,

With our focus on DB-first flow, model-first enabling features like SQL generation didn't get much love lately. But there are underlying fundamental reasons for that too. Namely the fact that Cayenne model doesn't even start to capture all the richness of a DB schema (indexes, various constraints beyond FK and NOT NULL, views, etc.). Hence we assume that the SQL will be written by hand, run against the DB (hopefully assisted by something like Liquibase), and then the OR model is recreated automatically from that schema. 

Trying to understand your DB/ORM modeling flow... What is "DB Deploy"? Is this some sort of migration framework? If not, are you using a migrations framework?

Thanks,
Andrus

> On Jan 28, 2019, at 2:33 PM, Tony Giaccone <to...@giaccone.org> wrote:
> 
> Andrus,
> 
> We have a cayenne data model that we use in our app. We use Postgres as the
> application DB in production and H2 for testing.  To create the database in
> production, we use DB Deploy. We've captured the SQL that the modeler
> generated and used that as part of the process. Through the process of
> doing this it's become clear there are somethings it would be nice to have
> in Cayenne.
> 
> 1) Capture generated SQL to a text file.  This would make the process
> slightly more efficient. Easier then a copy paste edit to a text file.
> 
> 2)  There are constraints that we use, that it would be nice to have in the
> model. So that these constraints could be applied in H2 when the test
> database is spun up.  I could see this as just a set of place holder SQL
> statements that are executed after the tables and sequences are generated.
> This could also include things like index creation.
> 
> We've had test cases pass in dev because the constraints we needed hadn't
> been applied to the H2 database.
> 
> I realize that you're a volunteer organization, and that the best way to
> get this done would be to do it myself. :-) Still I thought I'd make the
> suggestion just to see what you think.
> 
> 
> Tony