|
Replies:
9
-
Last Post:
Dec 20, 2007 6:59 PM
by: woongiap
|
|
|
|
|
|
|
Can JPA cope with a dynamic schema?
Posted:
May 31, 2007 1:18 AM
|
|
|
I just need a quick sanity check here please.
I suspect that if I had a database schema where the number of columns in a table changed at runtime (i.e. the table definition is controlled by the application user), there is no way to effectively annotate a collection (representing the columns) dynamically at runtime.
I realize I could avoid the dynamic column definition by moving what would have been the columns into rows, and giving each row a "column id", but I'm reluctant to do this as I'll end up with a massive number of rows.
Further to this I'd like for the actual table creation and deletion to be under dynamic control of the application user.
Can anybody please confirm that this kind of dynamic schema use problem is appropriate or inappropriate to a Java Persistence Architecture solution? And if it is possible point me in the right direction.
Thanks, in advance,
Philip Wilkinson
|
|
|
|
|
|
|
Re: Can JPA cope with a dynamic schema?
Posted:
May 31, 2007 8:05 AM
in response to: wilkinp
|
|
|
I dont believe you can. And you need to seriously consider if a relational database is right for what you're doing. Every alter command to the database at least for Oracle is an automatic commit.
|
|
|
|
|
|
|
|
|
Re: Can JPA cope with a dynamic schema?
Posted:
May 31, 2007 8:06 AM
in response to: fdlcruz
|
|
|
And by the way, a massive amount of rows is fine as long as you're indexed correctly.
|
|
|
|
|
|
|
|
|
Re: Can JPA cope with a dynamic schema?
Posted:
May 31, 2007 11:07 AM
in response to: wilkinp
|
|
|
Relational Databases are optimized for dealing with massive numbers of rows, not columns. If you create the right indexes, there should be no performance problem (with rows).
If you describe your need in more detail, people will be able to help you more.
|
|
|
|
|
|
|
|
|
Re: Can JPA cope with a dynamic schema?
Posted:
May 31, 2007 6:22 PM
in response to: wilkinp
|
 |
Helpful |
|
|
You should take a look the Entity Attribute Value Schema ( link below) . Also, keep in mind that it can complicate the queries you might need to write if you need to do aggregation or other types of queries that might be better suited to a traditional table schema. However, it appears if you do a little googling you could find some success stories. This is one way.
OR
Also, why not just write out a new annotated class and compile it, add it to your database classes jar file when the user defines a new table. Then load the class dynamically.
This post below is slightly related if you read far enough down. It might be a possible solution
http://forums.java.net/jive/message.jspa?messageID=204835
|
|
|
|
|
|
|
|
|
Re: Can JPA cope with a dynamic schema?
Posted:
Jun 1, 2007 5:37 AM
in response to: wilkinp
|
|
|
Thanks for your input. It looks like the consensus answer so far is No. JPA cant cope with a dynamic schema.
Let me explain a little bit more about the specifics of the application I'm writing.
Its going to be a web based issue\task tracking system.
I want to allow the users to define the attributes of the issue\task that they want to track by defining a issue\task type. After these types are initially defined (by an admin user, within the web app) they will change fairly in-frequently (again from within the web-app by an admin user). Because I want the application to be able to track millions of issue\tasks of any particular user defined type, I want each issue\task type to have its own table. For simplicity, scalability and performance, I want each issue\task to be contained as one row. (when the alternative static - fixed column - schema solution is having it spread across several tables and rows, an extra row per custom attribute, with value type id, a table for each value type text, number, date, etc (http://en.wikipedia.org/wiki/Entity-Attribute-Value_model))
This is easy to achieve with standard sql thru jdbc with its alter and create table sql, I was just wondering if it was possible to access this kind of functionality from JPA. I wanted to use JPA so I my application could be database agnostic and because it would make storing my application data much simpler. Currently I'm storing things like the issue\task type definitions as serialised classes into a blob. Horrible I know and not a long term solution.
jdaggs, The workaround of creating a java class dynamically, adding the annotations sounds like a great idea, and I'll bear that in mind as a possibility. The Entity Attribute Value Schema (now i know its name!) idea is pretty much what Id have to do if I could accept a static schema solution.
Thank you all again for your input.
Cheers,
-Phil Wilkinson
|
|
|
|
|
|
|
|
|
Re: Can JPA cope with a dynamic schema?
Posted:
Jun 1, 2007 7:06 AM
in response to: wilkinp
|
|
|
I think that for a performance and scalability standpoint that way will cause issues.
#You cant be transacted across multiple threads. See my previous post. I mentioned oracle but I think MySql will to the same thing. My point is that schema alters are a terrible thing to do to a production database.
#Since you're changing the schema on the fly, you're going to have to change your jdbc insert/update/alter on the fly. You're going to have to find a way to synchronize your jdbc calls if you're inserting/updating and altering.
#If you're having a user append a column, and you have 10M records guess what, you're allocating space for 10M records for with null or worse, updating all 10M records with redundant values. First case being a waste of disk space, second case being a bigger waste of disk space and will take forever to execute.
My suggestion is to break out into a couple of tables.
Issues table.
Issues attributes type table
A join table in between with
Index properly and if needed, use database hints. Even better, have the database do the work for you by creating database functions or stored procedures. Tweak your JDBC driver for performance. Profile the heck out of your query and if performance is still an issue, dont use JPA. EBAY from what I understand, still uses JDBC in parts of its application
|
|
|
|
|
|
|
|
|
Re: Can JPA cope with a dynamic schema?
Posted:
Jun 1, 2007 8:18 AM
in response to: wilkinp
|
|
|
Hi Phil,
Not sure why you would want to dynamically create tables etc - any DBA would cringe at the thought. But dynamically adding classes will wreak havoc on your JVM scallablility - potentially each row could require its own class to be loaded as well as the data from the database. That, and accessing anything different on these 'dynamic' classes will require your entire application to use reflective calls to get any of the non-standard attributes..
The wiki you outline isn't advocating that solution to this problem - it seems to be advocating a single class with static attributes. The class would hold all the attributes common to all issue/task types as well as a map containing the dynamic ones. This version of JPA would require the map to reference a separate entity class as a 1-M relation since it currently doesn't support simple collection types.
Best of luck
Chris
|
|
|
|
|
|
|
|
|
Re: Can JPA cope with a dynamic schema?
Posted:
Jun 1, 2007 1:44 PM
in response to: wilkinp
|
|
|
The way I see it I have two choices.
I can choose the static schema option and use JPA.
or I can choose a dynamic schema option and use JDBC.
If I choose the dynamic option I'll have schema something like this..
IssueTable
IssueId
IssueTitle
IssueTypeId
IssueTypeFeatureTable
IssueId
Description
Sponser
Accepted
....[ more columns representing custom fields ]
IssueTypeBugTable
IssueId
StepsToReporoduce
Severity
....[ more columns representing custom fields ]
So to get at issue 100 I would do..
select * from IssueTable where issueId = 100
to get the issue type and so its table then
select * from IssueTypeBugTable where issueId = 100
then I would manually stitch the result set together into my issue object (yuk!!, thats why I want to use JPA)
For a static schema it would become
IssueTable
IssueId
IssueTitle
IssueType
IssueCustomFieldTable
IssueId
CustomFieldId
CustomFieldTypeStringTable
IssueId
CustomFieldId
StringValue
CustomFieldTypeDateTable
IssueId
CustomFieldId
DateValue
....[ more tables for each supported custom field type ]
each table would be have its own entity class in JPA, so I imagine em.find(Issue.class, 100) would cause (approx) the following sql queries
select * from IssueTable where IssueId = 100
select * from IssueCustomFieldTable where IssueId = 100 // to get the issues custom fields, and so the identities of the custom field data type tables
so, for each custom field thats a string type
select * from CustomFieldTypeString where IssueId = 100 and CustomFieldId = ?
and or each custom field thats a date type
select * from CustomFieldTypeDate where IssueId = 100 and CustomFieldId = ?
What gives cause for pause about the static schema option is how slow all those queries might be compared to the dynamic option. Plus the fact that if I have a million issues, and each issue has 10 custom fields in use, then thats 10 million rows. Thats a lot. Thats 10 times more than I need from the dynamic solution.
What gives cause for pause about the dynamic option is that the consensus of opnion is that a rdbms schema is static, dont mess with it, thats not what its designed for.
The two best aguements for not having dynamic columns is that
a) its wastes resources when you add a new column to 1 million rows. and
b) if you alter a table then nobody can access that table for the duration of the change.
The thing is though, I always thought that adding a column full of nulls had little real resource overhead. I figured any database only used resources to store actual data. Am I being naive, please correct me if I'm wrong on this.
The other point about locking others from the table while its changing is something I think I'm comfortable about. I'm in control of the sole application accessing the database. The tables being altered will only take place very infrequently, at the start of the issue types life (when there will be no or few actual issues in its table) and very infrequently later on, and by an administrator user.
Thank you again for every bodies opinion. I'm still in two minds about the solution. because JPA makes it so easy to write the database layer, its probably not going to be too hard to try the static schema option out and see how it performs. I wonder if my hardware is up to one million issue database.
Cheers,
Phil Wilkinson.
|
|
|
|
|
|
|
|
|
Re: Can JPA cope with a dynamic schema?
Posted:
Dec 20, 2007 6:59 PM
in response to: wilkinp
|
|
|
Hi Phil, I'm having the same problem with you, have you got any reasonably good solution yet?
|
|
|
|
|
java.net RSS
