You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by No1UNo <je...@jerrycarter.org> on 2010/06/03 19:41:20 UTC
Re: JPA for portable database management (and wishes for JPA 3.0)
If you need predictable access to triggers and other stored procedures, you can't use JPA and be portable. That stated, there are a few tricks which may help...
[Triggers]
Let's divide triggers into three categories:
1. Triggers that only modify internal data (i.e. data is not exposed through any JPA entities). These triggers are safe but, because JPA flushes the SQL calls at will, you cannot rely on them being called at a specific time. For situations, such as recording who modified a record and when, this would seem to be a perfectly safe technique...but there is a real danger that a developer in future will extent the entity to cover the previously internal fields.
2. Triggers that modify entity data within a table. Here, the JPA cache will be out of sync with the database. This can be acceptable if you make the JPA calls, then call flush() to trigger the SQL statements, and then call merge() on any modified entities to refresh the cache. It is safe to say that this is NOT RECOMMENDED and can lead to problems if merge() is not called.
3. Triggers that modify entity data across multiple tables. DANGER. Because the sequencing of the SQL is not under developer control, you cannot rely on this safely working. Don't do it.
In other words, I do not believe that database triggers should be used with JPA. Thankfully, JPA 2.0 attempts to define a replacement for triggers. These are called the lifecycle callbacks (see section 3.5 of JPA 2.0). These appear to be useful for simple cases, but I've found them inappropriate for more complex operations. The specification places a significant limitation on what can be done in a lifecycle callback:
> In general, the lifecycle method of a portable application should not invoke EntityManager or Query operations, access other entity instances, or modify relationships within the same persistence context. A lifecycle callback method may modify the non-relationship state of the entity on which it is invoked.
More on this later.
[Stored Procedures / SQL functions / DB-specific functions]
Native Queries are your answer. An unfortunate reality of JPA 2.0 is that any query that needs access to a function not contained in JPQL MUST be turned into a native query. This is a significant weakness of JPA in my not so humble opinion. I would love to see a mechanism in JPA 3.0 which allows the author to define aliases within JPQL for stored procedures, SQL functions, or DB-specific functions. This would then reduce porting to updating those aliases rather than updating all the individual native queries.
I recommend defining a <mapping-file> within your persistence.xml in which as many of the native queries are defined as possible. This allows the code to access the queries by name and simplifies the task of finding and migrating all the native queries.
[My Wish and a Use Case]
I have an entity which I'll call MagazineArticle. The entity might look something like this (getters / setters omitted for brevity):
@Entity @Table(name="magazine_articles")
public class MagazineArticle {
@SequenceGenerator(name="ArticleIdGenerator", sequence="article_id_gen")
@Id @GeneratedValue(generator="ArticleIdGenerator", strategy=Generator.Type.SEQUENCE)
private long id;
private String title;
private String author;
private String body;
// NOTE: Getters / Setters omitted for brevity
@ElementCollection(fetch=FetchType.EAGER)
@CollectionTable(name="article_figures", joinColumns=@JoinColumn(name="article_ref"))
private Collection<Figure> figures;
}
Hidden the entity but contained within the 'magazine_articles" table is a DB-field which is used for text search operations. The contents of this field are generated from content within the article entity (e.g. title / author / body) and from data in other tables (e.g. article_figures). Optimally, the contents would be generated within the database whenever the entity is created or updated without action from JPA. However, because the sequence of database operations is hidden and MagazineArticle would probably be flushed before the Figures, I do not believe that a trigger on MagazineArticle can be safely used.
One might then consider a @PostUpdate and @PostPersist callback, but the JPA 2.0 specification recommends against EntityManager or Query operations. So this is out.
Instead, a combination of JPA and a NativeQuery appears to be necessary.
em.persist(article);
em.flush(); // to write out MagazineArticle and associated Figures.
Query query = em.createNativeQuery("SELECT generate_article_index(?1)");
query.setParameter(1, article.getId());
query.getSingleResult(); // generate the index
em.flush();
em.refresh(article); // not strictly necessary but recommended
Not quite as smooth as a trigger, but it works.
Search operations naturally benefit from having the index.
Query query = em.createNamedQuery("FindArticle);
query.setParameter(1, searchTerms);
List<Articles> results = query.ResultList();
Here the 'FindArticle' query is referenced from the persistence.xml file. But this is a simple search operation. Why shouldn't we be able to use JPQL instead? Why not call
Query query = em.createQuery("SELECT a,calculate_search_rank(a) AS score FROM MagazineArticle a WHERE article_matches_terms(:terms) ORDER BY score DESC");
query.setParameter("terms", searchTerms);
List<Articles> results = query.ResultList();
where the 'calculate_search_rank' and 'article_matches_terms' are aliases which get converted into SQL at the last minute? I would expect these aliases to be defined either within persistence.xml or within the <mapping-file>. With this simple change, many situations currently requiring native queries can migrate to JPQL queries.
On Jun 3, 2010, at 4:34 AM, Jean-Baptiste BRIAUD -- Novlog [via OpenJPA] wrote:
> Hi,
>
> Is there a way to use JPA abstraction from Java code to manipulate database instances, triggers, stored procedure ?
>
> Before going to the complex scenario, simple things first : How can I create a database instance in a Java way : "dev once, run everywhere" ?
>
> I had to write some SQL to create a MySQL database empty instance from Java code but that code is not portable now.
>
> Any thoughts ?
>
> Ps : more complex scenarios : need to manage triggers, how can I create, update and delete it in a portable manner ?
>
> View message @ http://openjpa.208410.n2.nabble.com/JPA-for-portable-database-management-tp5134215p5134215.html
> To start a new topic under OpenJPA Users, email ml-node+208411-1595610943-93721@n2.nabble.com
> To unsubscribe from OpenJPA Users, click here.
>
--
View this message in context: http://openjpa.208410.n2.nabble.com/JPA-for-portable-database-management-tp5134215p5136127.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: JPA for portable database management (and wishes for JPA 3.0)
Posted by No1UNo <je...@jerrycarter.org>.
On Jun 3, 2010, at 4:26 PM, Pinaki Poddar [via OpenJPA] wrote:
> The provision to allow function() expression in Criteria API was to accommodate database functions. Not sure why you qualified its location as "a rather unexpected place".
To quote from "Pro JPA 2: Mastering the Java Persistence API", p. 264:
> Unlike native SQL queries, which are clearly marked, function expressions are a small part of what otherwise looks like a normal portable JPA query that is actually tied to database-specific behavior.
I am not the only one to find this "a rather unexpected place". Unlike createNativeQuery which is easily identified by grep, searches for 'function' require a bit more care and are likely to be missed.
-=- Jerry
--
View this message in context: http://openjpa.208410.n2.nabble.com/JPA-for-portable-database-management-tp5134215p5136980.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: JPA for portable database management (and wishes for JPA 3.0)
Posted by No1UNo <je...@jerrycarter.org>.
Pinaki:
Thank you for your insights on JPA and on the Criteria API. This is helpful even if I do not agree on the details of the decisions that were made. I don't have the benefit of being privy to the discussions which shaped JPA 2.0 and appreciate hearing your views.
I have found that JPA works best when amount of application logic residing in the database in minimized. If you can survive without triggers / stored procedures / views / etc., then the abstraction provided by JPA is quite successful. As you say, "OpenJPA kernel does not even depend or assume that the underlying database is relational or JDBC-aware". This is notable and commendable.
The difficulty is in those situations where JPA must coexist with logic residing in the database. The text search example that I give is one such instance where functional considerations force coexistence. Another impetus might be organizational policies, such requirements that security and/or validation rules be imposed at the DB level. In each case, I would hope to benefit from JPA without resorting to a lower level syntax such as JDBC. You present diametric schools of thought: those who want to be entirely shielded from the database and those who want absolute control. I represent a different school of though: I just want something that works.
I will admit to having rarely used the Criteria API, though I have appreciated your article on the subject. I have believed JPQL to be simpler for simple things and native queries to be required for complex things. But I will take some time to reassess the capabilities of the Criteria API, particularly in the coexistence scenario which I believe to be the biggest weakness of JPA.
-=- Jerry
On Jun 3, 2010, at 4:26 PM, Pinaki Poddar [via OpenJPA] wrote:
> Hi Jerry,
> I hope I am not hijacking the thread's original topic....
>
> The provision to allow function() expression in Criteria API was to accommodate database functions. Not sure why you qualified its location as "a rather unexpected place". JPA Expert group roughly spent 30-40% of total deliberation time during JPA 2.0 on how to add a "type-safe" query mechanics. Criteria API was the crown jewel of JPA 2.0 and expanding string-based, fixed-grammar JPQL in whichever way was its raison d'être.
>
> Of course, by adding a new function() expression only covers a small portion of what the original poster wished for a JPA provider to accommodate in terms of datastore functionality. But in so doing, the original poster perhaps placed an expectation on JPA that is quite contrary to its "philosophy".
>
> JPA wants the developer (who loves his objects so much) to shield from the database/foreign key/schema/SQL/triggers... In fact, another similar specification that did influence JPA in a prominent way were not even committed to a relational database and postulated object persistence on *any* datastore. I am not arguing whether such effort to shield the database details from the application programmer is "good" or "bad" -- but it exists as a school of thought in technical community -- and JPA primarily addresses that segment/sentiment.
>
> However, the opposing school that states - "you can not get anything meaningful done about persistence, unless you directly control/manipulate the schema and data, or more precisely, set based data" -- is a significant voice and emboldened by the long reigning power of SQL. A realistic spec such as JPA can not see the light of the day without catering to that segment either. Hence this "design by committee" API where
> createNativeQuery(String sql)
> coexists with
> createQuery(CritriaQuery stronglyTypedQuery)
>
> Another recent poster in this forum asked : "Can the result of native SQL query be cached in L2 cache?"
> The users want good of both worlds -- a strongly-typed, object-oriented data as well as power of relational database/SQL -- but a spec that aspires to be "all things to all people" may end up satisfying none.
> In my view, it is better to ask: Is an object-oriented, strongly-typed view that hides access to underlying data model is right for my application's need?
> And if the answer is yes, then choose JPA.
>
> But coming back to original question of the poster, I will like to mention that OpenJPA architecturally makes a clean separation between object management and database interaction via its StoreManager interface -- a separation so distinct that OpenJPA kernel does not even depend or assume that the underlying database is relational or JDBC-aware. Such a decision by original OpenJPA architects is one of its core strength. In the context of the original question on this thread, Storemanager is the right place to explore how to leverage specific database functions within OpenJPA runtime.
>
>
>
>
>
>
>
>
>
>
> Pinaki
>
>
> View message @ http://openjpa.208410.n2.nabble.com/JPA-for-portable-database-management-tp5134215p5136808.html
> To start a new topic under OpenJPA Users, email ml-node+208411-1595610943-93721@n2.nabble.com
> To unsubscribe from OpenJPA Users, click here.
>
--
View this message in context: http://openjpa.208410.n2.nabble.com/JPA-for-portable-database-management-tp5134215p5136916.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: JPA for portable database management (and wishes for JPA 3.0)
Posted by Pinaki Poddar <pp...@apache.org>.
Hi Jerry,
I hope I am not hijacking the thread's original topic....
The provision to allow function() expression in Criteria API was to
accommodate database functions. Not sure why you qualified its location as
"a rather unexpected place". JPA Expert group roughly spent 30-40% of total
deliberation time during JPA 2.0 on how to add a "type-safe" query
mechanics. Criteria API was the crown jewel of JPA 2.0 and expanding
string-based, fixed-grammar JPQL in whichever way was its raison d'être.
Of course, by adding a new function() expression only covers a small portion
of what the original poster wished for a JPA provider to accommodate in
terms of datastore functionality. But in so doing, the original poster
perhaps placed an expectation on JPA that is quite contrary to its
"philosophy".
JPA wants the developer (who loves his objects so much) to shield from the
database/foreign key/schema/SQL/triggers... In fact, another similar
specification that did influence JPA in a prominent way were not even
committed to a relational database and postulated object persistence on
*any* datastore. I am not arguing whether such effort to shield the database
details from the application programmer is "good" or "bad" -- but it exists
as a school of thought in technical community -- and JPA primarily addresses
that segment/sentiment.
However, the opposing school that states - "you can not get anything
meaningful done about persistence, unless you directly control/manipulate
the schema and data, or more precisely, set based data" -- is a significant
voice and emboldened by the long reigning power of SQL. A realistic spec
such as JPA can not see the light of the day without catering to that
segment either. Hence this "design by committee" API where
createNativeQuery(String sql)
coexists with
createQuery(CritriaQuery stronglyTypedQuery)
Another recent poster in this forum asked : "Can the result of native SQL
query be cached in L2 cache?"
The users want good of both worlds -- a strongly-typed, object-oriented data
as well as power of relational database/SQL -- but a spec that aspires to be
"all things to all people" may end up satisfying none.
In my view, it is better to ask: Is an object-oriented, strongly-typed
view that hides access to underlying data model is right for my
application's need?
And if the answer is yes, then choose JPA.
But coming back to original question of the poster, I will like to mention
that OpenJPA architecturally makes a clean separation between object
management and database interaction via its StoreManager interface -- a
separation so distinct that OpenJPA kernel does not even depend or assume
that the underlying database is relational or JDBC-aware. Such a decision by
original OpenJPA architects is one of its core strength. In the context of
the original question on this thread, Storemanager is the right place to
explore how to leverage specific database functions within OpenJPA runtime.
-----
Pinaki
--
View this message in context: http://openjpa.208410.n2.nabble.com/JPA-for-portable-database-management-tp5134215p5136808.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: JPA for portable database management (and wishes for JPA 3.0)
Posted by No1UNo <je...@jerrycarter.org>.
Pinaki:
Thanks for the reference. I'd read your article earlier but I didn't understand the implications of that section. I need some additional time to ponder the full implications of this capability and to decide whether or not this provides sufficient power for extending JPQL. But I am immediately concerned that this hides invocation of native functionality in a rather unexpected place. I doubt that I am the first to raise this objection. What persuaded the committee to accept this syntax?
-=- Jerry
On Jun 3, 2010, at 2:37 PM, Pinaki Poddar [via OpenJPA] wrote:
> > This is a significant weakness of JPA in my not so humble opinion. I would love to see a mechanism in JPA > 3.0 which allows the author to define aliases within JPQL for stored procedures, SQL functions, or
> > DB-specific functions.
>
> Part of this limitation has been answered with JPA 2.0 itself. In JPA 2.0, queries can use database specific functions [1].
>
> [1] http://www.ibm.com/developerworks/java/library/j-typesafejpa/#N10B02
> See the subsection "Extensible datastore expressions"
>
> Pinaki
>
>
> View message @ http://openjpa.208410.n2.nabble.com/JPA-for-portable-database-management-tp5134215p5136374.html
> To start a new topic under OpenJPA Users, email ml-node+208411-1595610943-93721@n2.nabble.com
> To unsubscribe from OpenJPA Users, click here.
>
--
View this message in context: http://openjpa.208410.n2.nabble.com/JPA-for-portable-database-management-tp5134215p5136436.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: JPA for portable database management (and wishes for JPA 3.0)
Posted by Pinaki Poddar <pp...@apache.org>.
> This is a significant weakness of JPA in my not so humble opinion. I would
love to see a mechanism in JPA > 3.0 which allows the author to define
aliases within JPQL for stored procedures, SQL functions, or
> DB-specific functions.
Part of this limitation has been answered with JPA 2.0 itself. In JPA 2.0,
queries can use database specific functions [1].
[1] http://www.ibm.com/developerworks/java/library/j-typesafejpa/#N10B02
See the subsection "Extensible datastore expressions"
-----
Pinaki
--
View this message in context: http://openjpa.208410.n2.nabble.com/JPA-for-portable-database-management-tp5134215p5136374.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: JPA for portable database management (and wishes for JPA 3.0)
Posted by Jean-Baptiste BRIAUD -- Novlog <j-...@novlog.com>.
Thanks for the pointer. Unfortunately, the implementation doesn't rely on native database triggers.
There is only one things that could unlock the use of lifecycle for a legal audit system : the abilty to catch modification on entity done outside the JVM.
I don't think this is possible => native database triggers are the only way to do that audit in a legal context (any modification must be tracked, including direct SQL in the database without JPA)
On 5 juin 2010, at 16:27, Pinaki Poddar wrote:
>
> Hi,
> Few pointers...
>
>
> http://webspherepersistence.blogspot.com/2009/01/auditing-with-openjpa.html
>
>
> -----
> Pinaki
> --
> View this message in context: http://openjpa.208410.n2.nabble.com/JPA-for-portable-database-management-tp5134215p5142920.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: JPA for portable database management (and wishes for JPA 3.0)
Posted by Pinaki Poddar <pp...@apache.org>.
Hi,
Few pointers...
http://webspherepersistence.blogspot.com/2009/01/auditing-with-openjpa.html
-----
Pinaki
--
View this message in context: http://openjpa.208410.n2.nabble.com/JPA-for-portable-database-management-tp5134215p5142920.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
RE: JPA for portable database management (and wishes for JPA 3.0)
Posted by Pinaki Poddar <pp...@apache.org>.
Actually OpenJPA plug-in property takes care such that one can use mneomonics
rather than implementation class name. Also the plug-in can configure the
concrete implementation if it has bean style methods
So instead of
<property name='openjpa.AuditManagerImpl"
value="com.mypackage.MyAuditManager"/>
One can write
<property name="openjpa.AuditManager"
value="trigger(query=false,table=true)"/>
where "trigger" is a mnemonic for actual implementation class
com.mypackage.MyAuditManager.
And com.mypackage.MyAuditManager has bean style getter/setters
setQuery(boolean) and setTable(boolean)
OpenJPA's configuration/product derivation framework wires all these up
during bootstrap such that com.mypackage.MyAuditManager" can be packaged in
a separate jar (with possible dependency on some specific JDBC driver etc)
*without* core OpenJPA libraries being aware of it.
-----
Pinaki
--
View this message in context: http://openjpa.208410.n2.nabble.com/JPA-for-portable-database-management-tp5134215p5153706.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
RE: JPA for portable database management (and wishes for JPA 3.0)
Posted by C N Davies <cn...@cndavies.com>.
I like @Auditable because it's easy to switch on auditing of specific
entities or entity hierarchies. But the property would allow for
auditability globally to be turned on or off. Personally I would favour
also adding a property like :
<property name='openjpa.AuditManagerImpl"
value="com.mypackage.MyAuditManager"/>
Different countries and industries have different audit requirements so we
can implement/extend to our specific requirements.
2c more :)
Chris
-----Original Message-----
From: Pinaki Poddar [mailto:ppoddar@apache.org]
Sent: Tuesday, 8 June 2010 10:20 PM
To: users@openjpa.apache.org
Subject: Re: JPA for portable database management (and wishes for JPA 3.0)
> some technical annotation like @NativeTrigger or should we have higher
> level annotation like @Audit ?
I prefer @Audit to @NativeTrigger.
In fact I see two specifications
a) one on the persistent domain entity as an annotation
@Entity
@Auditable
public class Customer {....}
b) one on the run-time as a plug-in property
<property name="openjpa.AuditManager" value="trigger(query=false,
table=true)"/>
or
<property name='openjpa.AuditManager" value="none"/>
-----
Pinaki
--
View this message in context:
http://openjpa.208410.n2.nabble.com/JPA-for-portable-database-management-tp5
134215p5153394.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: JPA for portable database management (and wishes for JPA 3.0)
Posted by Pinaki Poddar <pp...@apache.org>.
> some technical annotation like @NativeTrigger or should we have higher
> level annotation like @Audit ?
I prefer @Audit to @NativeTrigger.
In fact I see two specifications
a) one on the persistent domain entity as an annotation
@Entity
@Auditable
public class Customer {....}
b) one on the run-time as a plug-in property
<property name="openjpa.AuditManager" value="trigger(query=false,
table=true)"/>
or
<property name='openjpa.AuditManager" value="none"/>
-----
Pinaki
--
View this message in context: http://openjpa.208410.n2.nabble.com/JPA-for-portable-database-management-tp5134215p5153394.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: JPA for portable database management (and wishes for JPA 3.0)
Posted by Jean-Baptiste BRIAUD -- Novlog <j-...@novlog.com>.
I didn't got answers from that important question :
let's imagine we'll have to do it. Let's be clear here : we're considering option to add that to OpenJPA and share it back as Open source of course.
How will we describe it in OpenJPA ?
Should we have some technical annotation like @NativeTrigger or should we have higher level annotation like @Audit ?
The later had been done for Hibernate here : http://www.jboss.org/envers
Unfortunately it use lifecycle listener to implement @Audit and I'm talking about database "native" trigger.
It could be an annotation attribute @Audit(native=true/false) or @Audit(DBTriggers=true/false).
About the database instance creation, I would think about some mappingtool option.
From both code we could use the current way OpenJPA code already use to detect database and have some branch in the code (command pattern rather than lots of if) so we could add more database over time.
What do you think ?
On 5 juin 2010, at 13:26, Jean-Baptiste BRIAUD -- Novlog wrote:
> OK, let's imagine we'll have to do it.
> How will we describe it in OpenJPA ?
>
> Should we have some technical annotation like @NativeTrigger or should we have higher level annotation like @Audit ?
> The later had been done for Hibernate here : http://www.jboss.org/envers
> Unfortunately it use lifecycle listener to implement @Audit and I'm talking about database "native" trigger.
> It could be an annotation attribute @Audit(native=true/false) or @Audit(DBTriggers=true/false).
>
> About the database instance creation, I would think about some mappingtool option.
>
> From both code we could use the current way OpenJPA code already use to detect database and have some branch in the code (command pattern rather than lots of if) so we could add more database over time.
>
> What do you think ?
>
> On 5 juin 2010, at 09:21, Jean-Baptiste BRIAUD -- Novlog wrote:
>
>> I know what do you mean but I really need to do that from Java like the =
>> DBA would do in your scenario.
>>
>> I would love to do that using annotation with OpenJPA mappingtool.
>> In fact native query are not portable by nature and also, trigger is =
>> more static than dynamic like database schema is.
>> That's why annotation would be more convenient to define triggers in a =
>> portable manner it should be couple with query to specify what to do =
>> when trigger is fired.
>>
>> Are you aware of a portable way to do that in raw Java before JPA =
>> integrate that cool idea to do it with mappingtool ?
>>
>> On 4 juin 2010, at 13:27, No1UNo wrote:
>>
>>>
>>> Security teams are, in my experience, much more comfortable with database triggers for auditing purposes. If you are writing records into a audit_log on each operation, this will not interfere with JPA. That you require it to be operational from any method of data access would seem to confirm implementation on the DB side.
>>>
>>> Generally, I would expect the triggers to be installed and locked down from a secure database account (i.e. not the one used by the JPA application). As such, I would expect the table creation and trigger installation to be performed by the DBA prior to executing the JPA application. In the unlikely event that it is acceptable to install the triggers from the JPA side, you could do this using native queries.
>>>
>>> -=- Jerry
>>>
>>
>
Re: JPA for portable database management (and wishes for JPA 3.0)
Posted by Jean-Baptiste BRIAUD -- Novlog <j-...@novlog.com>.
OK, let's imagine we'll have to do it.
How will we describe it in OpenJPA ?
Should we have some technical annotation like @NativeTrigger or should we have higher level annotation like @Audit ?
The later had been done for Hibernate here : http://www.jboss.org/envers
Unfortunately it use lifecycle listener to implement @Audit and I'm talking about database "native" trigger.
It could be an annotation attribute @Audit(native=true/false) or @Audit(DBTriggers=true/false).
About the database instance creation, I would think about some mappingtool option.
From both code we could use the current way OpenJPA code already use to detect database and have some branch in the code (command pattern rather than lots of if) so we could add more database over time.
What do you think ?
On 5 juin 2010, at 09:21, Jean-Baptiste BRIAUD -- Novlog wrote:
> I know what do you mean but I really need to do that from Java like the =
> DBA would do in your scenario.
>
> I would love to do that using annotation with OpenJPA mappingtool.
> In fact native query are not portable by nature and also, trigger is =
> more static than dynamic like database schema is.
> That's why annotation would be more convenient to define triggers in a =
> portable manner it should be couple with query to specify what to do =
> when trigger is fired.
>
> Are you aware of a portable way to do that in raw Java before JPA =
> integrate that cool idea to do it with mappingtool ?
>
> On 4 juin 2010, at 13:27, No1UNo wrote:
>
>>
>> Security teams are, in my experience, much more comfortable with database triggers for auditing purposes. If you are writing records into a audit_log on each operation, this will not interfere with JPA. That you require it to be operational from any method of data access would seem to confirm implementation on the DB side.
>>
>> Generally, I would expect the triggers to be installed and locked down from a secure database account (i.e. not the one used by the JPA application). As such, I would expect the table creation and trigger installation to be performed by the DBA prior to executing the JPA application. In the unlikely event that it is acceptable to install the triggers from the JPA side, you could do this using native queries.
>>
>> -=- Jerry
>>
>
Re: JPA for portable database management (and wishes for JPA 3.0)
Posted by Jean-Baptiste BRIAUD -- Novlog <j-...@novlog.com>.
I know what do you mean but I really need to do that from Java like the =
DBA would do in your scenario.
I would love to do that using annotation with OpenJPA mappingtool.
In fact native query are not portable by nature and also, trigger is =
more static than dynamic like database schema is.
That's why annotation would be more convenient to define triggers in a =
portable manner it should be couple with query to specify what to do =
when trigger is fired.
Are you aware of a portable way to do that in raw Java before JPA =
integrate that cool idea to do it with mappingtool ?
On 4 juin 2010, at 13:27, No1UNo wrote:
>
> Security teams are, in my experience, much more comfortable with database triggers for auditing purposes. If you are writing records into a audit_log on each operation, this will not interfere with JPA. That you require it to be operational from any method of data access would seem to confirm implementation on the DB side.
>
> Generally, I would expect the triggers to be installed and locked down from a secure database account (i.e. not the one used by the JPA application). As such, I would expect the table creation and trigger installation to be performed by the DBA prior to executing the JPA application. In the unlikely event that it is acceptable to install the triggers from the JPA side, you could do this using native queries.
>
> -=- Jerry
>
Re: JPA for portable database management (and wishes for JPA 3.0)
Posted by No1UNo <je...@jerrycarter.org>.
Security teams are, in my experience, much more comfortable with database triggers for auditing purposes. If you are writing records into a audit_log on each operation, this will not interfere with JPA. That you require it to be operational from any method of data access would seem to confirm implementation on the DB side.
Generally, I would expect the triggers to be installed and locked down from a secure database account (i.e. not the one used by the JPA application). As such, I would expect the table creation and trigger installation to be performed by the DBA prior to executing the JPA application. In the unlikely event that it is acceptable to install the triggers from the JPA side, you could do this using native queries.
-=- Jerry
On Jun 4, 2010, at 4:09 AM, Jean-Baptiste BRIAUD -- Novlog [via OpenJPA] wrote:
> Unfortunately, lifecycle in JPA will not answer to the direct need of "that" triggers.
> I need triggers because our customer asked for *secure* audit trail.
> That audit trail must be operational for any data access, from or not from the Java application server.
> That must be done very strictly for legal purpose, so it can't be any Java part at runtime.
>
> So, I was only thinking about a "installation time" (creating the triggers) written in Java so it will be portable.
> As we are not just doing project for customers, I would like to to that in a portable manner.
>
> If not OpenJPA, are you aware of any framework that would allow portable DDL statement in Java ?
>
> View message @ http://openjpa.208410.n2.nabble.com/JPA-for-portable-database-management-tp5134215p5138491.html
> To start a new topic under OpenJPA Users, email ml-node+208411-1595610943-93721@n2.nabble.com
> To unsubscribe from OpenJPA Users, click here.
>
--
View this message in context: http://openjpa.208410.n2.nabble.com/JPA-for-portable-database-management-tp5134215p5139037.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: JPA for portable database management (and wishes for JPA 3.0)
Posted by Jean-Baptiste BRIAUD -- Novlog <j-...@novlog.com>.
Unfortunately, lifecycle in JPA will not answer to the direct need of "that" triggers.
I need triggers because our customer asked for *secure* audit trail.
That audit trail must be operational for any data access, from or not from the Java application server.
That must be done very strictly for legal purpose, so it can't be any Java part at runtime.
So, I was only thinking about a "installation time" (creating the triggers) written in Java so it will be portable.
As we are not just doing project for customers, I would like to to that in a portable manner.
If not OpenJPA, are you aware of any framework that would allow portable DDL statement in Java ?