is there a way to use jspresso with an existing db schema, or a way to instruct jspresso not to generate the ID and VERSION columns ?
thanks for your help
Henrik
using jspresso with existing DB schema
Sun, 08/15/2010 - 22:30
#1
using jspresso with existing DB schema
Mon, 08/16/2010 - 01:24
#2
using jspresso with existing DB schema
Henrik,
is there a way to use jspresso with an existing db schema
See:
http://www.jspresso.org/forum/entity-annotations#comment-498
http://www.jspresso.org/forum/existing-domain-model
not to generate the ID and VERSION columns
no. You will find explanations in the above threads.
Regards
Pierre
Mon, 08/16/2010 - 20:01
#3
a DBA perspective ...
I see, thanks.
As long time DBA I think this will seriously hinder a broader
addaptation of jspresso, which I think is a shame. Of all the
GUI frameworks I have seen, jspresso is truly unique.
So maybe we can teach jspresso to work with legacy tables
and vice versa.
Assuming I have a legacy table T1_DBA with primary key PK1,
it must be possible to trick jspresso by using the DB.
Goal:
Create view T1 that the jspresso GUI can fully use.
Proposal:
1) - create table T1_jspresso with the ID,VERSION and PK
columns of legacy table T1_DBA
2) - create view T1 that joins legacy table and ID table
and that jspresso use for GUI etc.
3) - do initial insert on T1_jspresso
4) - use DB triggers to keep (1) in sync with legacy
table T1_DBA whenever the GUI changes and commit
data.
under the prerequisite that the DB supports the triggers, are there any reasons why this wouldnt work ?
Obviously the locking between any access to the legacy tables needs to be coordinated with the new jspresso tables.
Has anyone tried this ?
Henrik
Tue, 08/24/2010 - 09:01
#4
a DBA perspective ...
Hi Henrik,
I've never tried to "split" Jspresso tables as you suggested. As you pointed it out, this approach should require some validation on the TX side, but its main drawback is, for me, the lot of extra maintenance it puts on the db side; this is certainly due to the fact tat I'm not a DBA ;-)
However, things have evolved on the Jspresso side and we now have a (informal) process to upgrade existing schemas to work with Jspresso. It is not as unintrusive as your proposition since it still requires ID and VERSION columns on every table but it could be automated, at least on the DB side.
Here are the key points :
- Jspresso, starting from 3.5, allows to refine the name of every single created DB artifact independently of their Java equivalent. This is achieved using sqlName SJS keyword on entities, properties, and so on... This includes :
- table names
- column names
- index names
- We have succesfully achieved an existing schema migration using the following detailed steps :
- Describe your domain model using Jspresso and refine the names to match the existing DB schema, as described above.
- Manage all existing PKs as "normal" properties and include them in Jspresso "unicity scopes" to reflect their actual unicity.
- Add an ID and VERSION column to all tables.
- Initialize existing records ID column to a concatenation of their legacy PK column(s) using a separator, and VERSION to "0".
- Create a foreign key ID column next to every legacy FK and initialize these new foreign key IDs using the same concatenation algorithm you used at step 4.
- Transform the legacy PK constraints to unique indices (as declared in step 2) and create to PK constraints on the new ID columns.
- Create the FK constraints on the new FK IDs columns created at step 5. At that point you should have a Jspresso compatible schema that use your existing data.
- The only thing left to achieve is to keep legacy FK columns filled with legacy PK columns when 2 objects are associated. Jspresso (through Hibernate) will manage ID and ID FKs automatically and using Jspresso, we can easily achievethe the legacy PK/FK synchronization using property post processors.
The above process (I hope I didn't forget any step) has been succesfully used to migrate the admin part of an existing portal to Jspresso while keeping the portal itself unchanged (i.e. it still only uses the legacy schema). As a side note, and in order to validate the migration, we use the Jspresso generated schema to compare it with the DBA migrated one. This schema comparison (using TOAD for instance) should only raise compatible differences (like optimisation indices, compatible datatypes differences, ...).
However, I don't want to discourage any other option, so feel free to try another road and report your findings !
HTH,
Vincent
Tue, 08/24/2010 - 19:56
#5
Your outlined approach will
Your outlined approach will not work in my organisation. And I doubt in any larger enterprise setting. And from a DBA perspective changing the PK is a non-starter.
That said, I think we can use the hibernate @secondaryTable annotation. According to the specs this will achieve the behaviour I am looking for.
I dont think jspresso should support the full semantics of the @secondaryTable. I will suggest to add two properties to the Entity definition: legacyTable:String and legacyPrimarykey:String[], and then addapt the hibernate xdoclet to check for these variables.
I posted another post in the forum regarding developing jspresso.
If someone either add the properties or give a hint on how to extend the groovy/sjs to take the extra properties into account, then I will look at the xdoclet stuff.
thanks, Henrik
Wed, 08/25/2010 - 08:32
#6
a DBA perspective ...
Hi Henrik,
I understand your point. I'll give you the hints to extend te entity descriptors and experiment your solution in the other post.
Best,
Vincent
