On 5/18/2012 8:26 AM, Steve Ebersole wrote:
>
> On Thu 17 May 2012 07:14:19 PM CDT, Linda DeMichiel wrote:
>>> Another related thing to consider is "auxiliary database objects";
>>> basically allowing create/drop definitions for
>>> database objects other than those explicitly named in mappings.
>>> Functions, procedures, view, etc, etc.
>>>
>>
>> Right. This would be doable via the scripts
>
> You mean the "init scripts"? If so, I'd caution against that plan. For these auxiliary objects you need both create and
> drop capability, whereas typically the init script will only contain "create" information (INSERTS, etc).
>
No, I had assumed that these would be in the DDL scripts.
>
>>>> SQL DDL scripts might be provided with the application (bundled as
>>>> part of the persistence unit) or the platform provider might invoke
>>>> the PersistenceProvider implementation to generate them from the
>>>> persistence unit metadata. If scripts are provided or generated, it
>>>> should be the platform provider's responsibility to run them against
>>>> the database. If the application has provided DDL scripts, the
>>>> application should also have the option to provide SQL scripts to do
>>>> a bulk insert of data. It would be the responsibility of the
>>>> platform provider to run these as well.
>>>
>>> Personally, I think the ability to provide SQL init scripts for
>>> initial data insertion should be available regardless of
>>> whether the application provided the DDL or the persistence provider
>>> generated the DDL.
>>>
>>
>> OK, but if the provider generates the DDL, we do need to be very
>> precise as
>> to exactly what would be generated to guarantee that this would work.
>> Even then, I'd expect developers to have the provider generate the DDL
>> and then
>> package that DDL together with the data insertion scripts.
>
> Sure, same as with native-sql queries...
>
> Maybe there is an option to supply either SQL or JPQL-like statements, since I am guessing your reservation is around
> column/table names matching up. That way the user can decide which format to write the init script in.
>
I'd like this to work like the raw JDBC/SQL case, where the platform provider would be running
the scripts. In that case, JPQL presumably wouldn't come into play.
>
>>>> (2) Foreign Key Constraints
>>>>
>>>> I see two possible approaches:
>>>>
>>>> (a) Add a string-valued foreignKeyDefinition element to JoinColumn,
>>>> JoinColumns, MapKeyJoinColumn, etc. to specify a SQL fragment
>>>> (intended along the lines of columnDefinition) for defining a foreign
>>>> key constraint and/or for overriding the persistence provider's
>>>> default foreign key definition. It might also be helpful to allow a
>>>> name for the foreign key constraint to be specified for the case where
>>>> the provider is using its default foreign key generation strategy.
>>>>
>>>> or
>>>>
>>>> (b) Add a ForeignKey annotation to specify the foreign key
>>>> constraint --
>>>> for example,
>>>>
>>>> public @interface ForeignKey {
>>>> String name() default "";
>>>> ForeignKeyAction updateAction() default NO_ACTION;
>>>> ForeignKeyAction deleteAction() default NO_ACTION;
>>>> boolean deferred() default false;
>>>> }
>>>>
>>>> public enum ForeignKeyAction {NO_ACTION, CASCADE, SET_NULL};
>>>>
>>>> I have some misgivings about approach (b), and, given a lack of
>>>> database portability here, we might wind up needing a
>>>> foreignKeyDefinition
>>>> fragment anyway.
>>>
>>> Can you give an example of what you are thinking with
>>> 'foreignKeyDefinition'? Personally I was leaning towards (b) until
>>> I read this last bit. Do you just mean different capabilities (i.e.,
>>> some dbs do not support deferred constraints, etc)?
>>>
>>
>> Analogous to the columnDefinition, it would allow capturing
>> database-specific syntax/semantics.
>> E.g., different variants of foreign key actions, what might be
>> deferrable / deferred, etc.
>>
>>> There is an implication in terms of how a persistence provider needs
>>> to handle the case of cascading foreign keys in
>>> terms of mapped cascading. Take the case of remove operation, with
>>> ForeignKey.deleteAction==CASCADE all of a sudden the
>>> database has now physically deleted corresponding rows "underneath"
>>> the provider which has ramifications on the
>>> persistence provider in terms of cleaning up cache entries and
>>> possibly in terms of additional cascading. Yes, we have
>>> this mismatch today as is, but today we also don't have the needed
>>> visibility into that situation because we do not know
>>> the foreign key has been defined that way. Here that changes. Just
>>> want to make sure we then account for that in the
>>> other "entity operation"-related sections.
>>>
>>
>> If the application bundles the DDL, that problem remains, doesn't it?
>> I didn't understand what you
>> meant by the last sentence above though.
>
> Correct. However, if the application is providing the DDL or if the schema already exists there is in essence a limited
> culpability because persistence providers could simply claim "well we did not know about these FK-level cascades".
> Really this is true even in the case of the 'foreignKeyDefinition' solution.
>
> However, with @ForeignKey.deleteAction the provider unequivocally knows about the FK-level cascades. Really,
> @ForeignKey.deleteAction is specifying runtime behavior in addition to FK-creation metadata. It implies REMOVE
> cascading. So I was just saying that it would be good to note this in the sections that deal with REMOVE cascading and
> the remove operation in general.
>
Yes -- I agree.