FAQ
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

Search Discussions

  • Andrus Adamchik at Oct 8, 2011 at 9:37 am

    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
  • Durchholz, Joachim at Oct 10, 2011 at 9:32 am

    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
  • Aristedes Maniatis at Oct 11, 2011 at 2:11 am

    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
  • Michael Gentry at Oct 11, 2011 at 1:47 pm
    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 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
  • Daniel Scheibe at Oct 11, 2011 at 5:40 pm
    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.
  • John Huss at Oct 11, 2011 at 7:31 pm
    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
    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.
  • Marek Šabo at Oct 11, 2011 at 8:50 pm

    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
  • Durchholz, Joachim at Oct 12, 2011 at 8:14 am

    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
  • Mike Kienenberger at Oct 12, 2011 at 9:55 pm

    On Wed, Oct 12, 2011 at 4:13 AM, Durchholz, Joachim 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.
  • Durchholz, Joachim at Oct 13, 2011 at 11:15 am

    On Wed, Oct 12, 2011 at 4:13 AM, Durchholz, Joachim 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupuser @
categoriescayenne
postedOct 7, '11 at 9:17p
activeOct 13, '11 at 11:15a
posts11
users8
websitecayenne.apache.org

People

Translate

site design / logo © 2022 Grokbase