Unique Index Constraints in Cayenne Modeler

[Daniel Scheibe <ds...@googlemail.com>]

Hi,

i was just asking myself if i'm missing something or if its not possible 
to configure unique index constraints inside cayenne modeler?

Right now i'm creating such indices manually but it would be nice to 
have this available through the cayenne modeler as well.

Another thing that's bugging me is the way cayenne modeler trys to 
delete the records from the pk generation table on schema 
updates/modifications. I  wrote some code to manually update the pk 
generation table on startup of my application to set the available 
primary key identifier to the highest number used in the corresponding 
table but that doesn't feel like taking the right approach.

Thanks in advance!

Cheers,
Daniel
	

Generating tables: Manual SQL vs. Modeller

["Durchholz, Joachim" <Jo...@hennig-fahrzeugteile.de>]

On Wed, Oct 12, 2011 at 4:13 AM, Durchholz, Joachim <Jo...@hennig-fahrzeugteile.de> wrote:
>> Maintaining a set of scripts so that it remains consistent with the database would be just a waste of effort. It is unlikely that they'd ever need such scripts anyway: the ability to create an empty database would be worthless to the company, it needs the data.
> 
> While I have no opinion on the thread as a whole, I do want to state that the above statement isn't true for many of us.   When you are 
writing integration tests, the ability to create an empty database with an identical schema and populate it with your test data is extremely useful.

Heh, you're right. I should have known better, our own unit tests generate a test database (maybe they're running just too smoothly ;-) ).

However, we are generating the database not from SQLs but straight from the ORM model. Our use case includes these reasons:
1) ORM-generated tables are closer to what the Java side of things actually needs. In other words, a generated table may expose assumptions that are satisfied in the database, but not in the ORM model.
2) We assume that the ORM can check compatibility of its internal model with the database it is connected to. We further assume that this check is more reliable than manually comparing a set of creation SQL scripts with the actual production database. (This assumption need not be true for all circumstances, but it is definitely true for our team and Hibernate, which we're still tied to.)
3) For portability across databases, a set of manually maintained SQL scripts simply isn't going to work since table/view/whatever creation syntax varies wildly.

One use case that I don't have but others might is installable applications that are portable across databases.
For these, all aspects of the database schema (as far as they can be made portable) would best be placed inside modeller. This includes stuff like comments, views, triggers, sequences.
Not that I think Cayenne should change its scope on the spot, just that it might be something to consider in the future.

Just my 2 cents.

Regards,
Jo

Re: Unique Index Constraints in Cayenne Modeler

[Mike Kienenberger <mk...@gmail.com>]

On Wed, Oct 12, 2011 at 4:13 AM, Durchholz, Joachim
<Jo...@hennig-fahrzeugteile.de> wrote:
> Maintaining a set of scripts so that it remains consistent with the database would be just a waste of effort. It is unlikely that they'd ever need such scripts anyway: the ability to create an empty database would be worthless to the company, it needs the data.

While I have no opinion on the thread as a whole, I do want to state
that the above statement isn't true for many of us.   When you are
writing integration tests, the ability to create an empty database
with an identical schema and populate it with your test data is
extremely useful.

RE: Unique Index Constraints in Cayenne Modeler

["Durchholz, Joachim" <Jo...@hennig-fahrzeugteile.de>]

John Huss wrote:
> I'd say it not essential.

If a N:1 relationship is based on a UK that's not known to Cayenne, Cayenne will be forced to assume an N:M relationship.
This means that on the N side, the Java object will have a Set instead of a simple reference.

I'd consider stuff that affects the Java classes quite essential.

> In any project of decent size you're likely to have SOME
> additional sql to execute to set up your database, be it
> creating views or functions or triggers or indexes.

Setting up a new database actually is not relevant in the vast majority of projects.
See, there's a HUGE number of programmers who do in-house development. The typical scenario for them is "the" database, which is updated with triggers and views and indexes as the need arises.
Maintaining a set of scripts so that it remains consistent with the database would be just a waste of effort. It is unlikely that they'd ever need such scripts anyway: the ability to create an empty database would be worthless to the company, it needs the data.

Regards,
Jo

Re: Unique Index Constraints in Cayenne Modeler

[Marek Šabo <ms...@buk.cvut.cz>]

> But I'd say it not essential.  In any project of decent size you're likely
> to have SOME additional sql to execute to set up your database, be it
> creating views or functions or triggers or indexes.  It's definitely not the
> modeler's job to handle all that stuff.  So if you have to execute custom
> sql already, it's trivial to add the unique constraints at the same time.
>
> What I would really like to see in Modeler is the ability to define *default
> values* for attributes.  That is useful because since cayenne is generating
> the sql to define the column it's really nice to have it go all the way.
>   And the default values can be utilized in the object side in memory when
> new objects are created, so it's not just an SQL thing.
Not that I disagree, but we can say this about eg. comments as well, 
generating default javadoc for public properties in CDOs so the model 
would contain some description.

I think default values, unique keys and e.g. comments and etc. would all 
be very useful in cayenne modeler. I know i takes time to implement and 
there are more important issues at stake. However, sometimes DB modeler 
is overkill so maybe it would just help if cayenne modeler would allow 
to append arbitrary SQL and store it in XML for later (DDL generation). 
Although this would not directly automate tasks we mentioned, it would 
be imho cost effective feature for inserts, additional constraints, etc.

Regards,

Marek

Re: Unique Index Constraints in Cayenne Modeler

[John Huss <jo...@gmail.com>]

My 2 cents:  Coming from the WebObjects world, their modeler does let you
define indexes (unique or not) in the modeler, and it will generate the sql
for them.  It's nice.

But I'd say it not essential.  In any project of decent size you're likely
to have SOME additional sql to execute to set up your database, be it
creating views or functions or triggers or indexes.  It's definitely not the
modeler's job to handle all that stuff.  So if you have to execute custom
sql already, it's trivial to add the unique constraints at the same time.

What I would really like to see in Modeler is the ability to define *default
values* for attributes.  That is useful because since cayenne is generating
the sql to define the column it's really nice to have it go all the way.
 And the default values can be utilized in the object side in memory when
new objects are created, so it's not just an SQL thing.

John

On Tue, Oct 11, 2011 at 12:40 PM, Daniel Scheibe
<ds...@googlemail.com>wrote:

> I second this because as i see the DB Modeler is like an abstraction
> layer/tool of the database in terms of a model and i would love to skip any
> direct contact with the database (as i don't really care about the specific
> database backend itself as long as it adheres and fulfils Cayenne
> requirements).
>
> Maybe i can make my point a little bit more clear with an example:
>
> Let's say we have a simple Customer Table (composed of a integer PK id, and
> some varchar name column) and a Customer Entity (composed of the string name
> attribute). With only that given i can't identify a single customer as i
> obviously don't want to expose the concept of a primary key to the
> entity/object mapping and for various reasons where a Customer Number simply
> doesn't match the meaning of a PK. So what i would have to do is to
> introduce another column/attribute CustomerNumber to the table and the
> entity.
>
> Now this CustomerNumber is used to identify a single customer entity but it
> doesn't prohibit the use of duplicate Customer Numbers. That's the point
> where i have to go to the a DB Administration tool and put a unique
> constraint to the CostumerNumber column to ensure uniqueness of the entries
> since i can't do that in the DB Modeler. If i later on recreate the database
> schema the constraints are no longer contained so i have to maintain them in
> another "place".
>
> So i guess the main question remains whether or not a unique constraint
> should be part of the database model...
>
> Cheers,
> Daniel
>
>
>
>> I agree that unique indices are important to the model, just like
>> validating that a varchar is no more than (say) 12 characters.
>> Personally I'd like to see all indices in the Cayenne model if only
>> because it means we have one place to store the entire db schema
>> definition, but I do understand why this should be kept separate to the
>> Cayenne runtime.
>>
>>

Re: Unique Index Constraints in Cayenne Modeler

[Daniel Scheibe <ds...@googlemail.com>]

I second this because as i see the DB Modeler is like an abstraction 
layer/tool of the database in terms of a model and i would love to skip 
any direct contact with the database (as i don't really care about the 
specific database backend itself as long as it adheres and fulfils 
Cayenne requirements).

Maybe i can make my point a little bit more clear with an example:

Let's say we have a simple Customer Table (composed of a integer PK id, 
and some varchar name column) and a Customer Entity (composed of the 
string name attribute). With only that given i can't identify a single 
customer as i obviously don't want to expose the concept of a primary 
key to the entity/object mapping and for various reasons where a 
Customer Number simply doesn't match the meaning of a PK. So what i 
would have to do is to introduce another column/attribute CustomerNumber 
to the table and the entity.

Now this CustomerNumber is used to identify a single customer entity but 
it doesn't prohibit the use of duplicate Customer Numbers. That's the 
point where i have to go to the a DB Administration tool and put a 
unique constraint to the CostumerNumber column to ensure uniqueness of 
the entries since i can't do that in the DB Modeler. If i later on 
recreate the database schema the constraints are no longer contained so 
i have to maintain them in another "place".

So i guess the main question remains whether or not a unique constraint 
should be part of the database model...

Cheers,
Daniel

>
> I agree that unique indices are important to the model, just like
> validating that a varchar is no more than (say) 12 characters.
> Personally I'd like to see all indices in the Cayenne model if only
> because it means we have one place to store the entire db schema
> definition, but I do understand why this should be kept separate to the
> Cayenne runtime.
>

Re: Unique Index Constraints in Cayenne Modeler

[Michael Gentry <mg...@masslight.net>]

I think the biggest argument for keeping constraints in the model is
that it could be used in the future to help Cayenne determine better
orders of operation.

mrg


On Mon, Oct 10, 2011 at 10:11 PM, Aristedes Maniatis <ar...@maniatis.org> wrote:
> On Mon Oct 10 20:32:13 2011, Durchholz, Joachim wrote:
>>
>> Just giving feedback from my user perspective:
>>
>>> CayenneModeler is not a DB admin tool, so DB concepts not directly
>>> related to ORM are generally ignored.
>>
>> I find that reasonable.
>>
>>> UNIQUE indexes are somewhat on the border and I even believe we have a
>>> feature request in Jira somewhere...
>>
>> This I cannot agree with, I do think unique indexes are solidly on the ORM
>> side.
>> The issue is relationships defined using unique keys. These tend to come
>> from two sources:
>> - Legacy databases that I need to interface with.
>> - People with direct manual SQL access and a say on the data model. For
>> them, having a UK with well-chosen content can make the difference between
>> needing a join or not, so they do have a point.
>>
>> Regards,
>> Jo
>
> I agree that unique indices are important to the model, just like validating
> that a varchar is no more than (say) 12 characters. Personally I'd like to
> see all indices in the Cayenne model if only because it means we have one
> place to store the entire db schema definition, but I do understand why this
> should be kept separate to the Cayenne runtime.
>
> --
> -------------------------->
> Aristedes Maniatis
> GPG fingerprint CBFB 84B4 738D 4E87 5E5C  5EFA EF6A 7D2E 3E49 102A
>

Re: Unique Index Constraints in Cayenne Modeler

[Aristedes Maniatis <ar...@maniatis.org>]

On Mon Oct 10 20:32:13 2011, Durchholz, Joachim wrote:
> Just giving feedback from my user perspective:
>
>> CayenneModeler is not a DB admin tool, so DB concepts not directly related to ORM are generally ignored.
>
> I find that reasonable.
>
>> UNIQUE indexes are somewhat on the border and I even believe we have a feature request in Jira somewhere...
>
> This I cannot agree with, I do think unique indexes are solidly on the ORM side.
> The issue is relationships defined using unique keys. These tend to come from two sources:
> - Legacy databases that I need to interface with.
> - People with direct manual SQL access and a say on the data model. For them, having a UK with well-chosen content can make the difference between needing a join or not, so they do have a point.
>
> Regards,
> Jo

I agree that unique indices are important to the model, just like 
validating that a varchar is no more than (say) 12 characters. 
Personally I'd like to see all indices in the Cayenne model if only 
because it means we have one place to store the entire db schema 
definition, but I do understand why this should be kept separate to the 
Cayenne runtime.

-- 
-------------------------->
Aristedes Maniatis
GPG fingerprint CBFB 84B4 738D 4E87 5E5C  5EFA EF6A 7D2E 3E49 102A

RE: Unique Index Constraints in Cayenne Modeler

["Durchholz, Joachim" <Jo...@hennig-fahrzeugteile.de>]

Just giving feedback from my user perspective:

> CayenneModeler is not a DB admin tool, so DB concepts not directly related to ORM are generally ignored.

I find that reasonable.

> UNIQUE indexes are somewhat on the border and I even believe we have a feature request in Jira somewhere...

This I cannot agree with, I do think unique indexes are solidly on the ORM side.
The issue is relationships defined using unique keys. These tend to come from two sources:
- Legacy databases that I need to interface with.
- People with direct manual SQL access and a say on the data model. For them, having a UK with well-chosen content can make the difference between needing a join or not, so they do have a point.

Regards,
Jo

Re: Unique Index Constraints in Cayenne Modeler

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

On Oct 8, 2011, at 12:17 AM, Daniel Scheibe wrote:

> i was just asking myself if i'm missing something or if its not possible to configure unique index constraints inside cayenne modeler?

You are correct. CayenneModeler is not a DB admin tool, so DB concepts not directly related to ORM are generally ignored. UNIQUE indexes are somewhat on the border and I even believe we have a feature request in Jira somewhere...

> Another thing that's bugging me is the way cayenne modeler trys to delete the records from the pk generation table on schema updates/modifications. I  wrote some code to manually update the pk generation table on startup of my application to set the available primary key identifier to the highest number used in the corresponding table but that doesn't feel like taking the right approach.

Yeah good point. This can be improved. Maybe do something like "select max(ID) from table X" to determine initial value?

Andrus