DDL generation and Oracle 9

[claudio]

Hi Rob, I'm using EBean in three small projects and very satisfied
with it.

Now, in a slightly bigger project, I tried DDL generation from
entities, but found some problems:

1. Seems there is an error dropping the tables:

2-nov-2009 14.20.48 com.avaje.ebean.server.util.ClassPathSearch
findClasses
GRAVE: Error: expected classPath entry [C:\documenti\lavoro\workspace
\s2zk\WebContent\WEB-INF\lib\ebean-2.1.0-src.zip] to be a directory or
a .jar file but it is not either of those?
2-nov-2009 14.20.48 com.avaje.ebean.server.core.BootupClassPathSearch
search
INFO: Classpath search hits in jars[ebean-2.1.0.jar] pkgs
[pianosociale.model, com.avaje.ebean.meta,
com.avaje.ebean.server.bean] searchTime[156]
2-nov-2009 14.20.48 com.avaje.ebean.server.lib.sql.DataSourcePool
initialise
INFO: DataSourcePool [s2] autoCommit[false] transIsolation
[READ_COMMITTED] min[2] max[20]
2-nov-2009 14.20.48 com.avaje.ebean.server.lib.sql.DataSourcePool
createConnection
INFO: DataSourcePool [s2] grow pool; busy[0] size[0] max[20]
2-nov-2009 14.20.48 com.avaje.ebean.server.core.DefaultServerFactory
setDatabasePlatform
INFO: DatabasePlatform s2 oracle9
2-nov-2009 14.20.48
com.avaje.ebean.server.resource.ResourceManagerFactory
createFileSource
INFO: ResourceManager initialised: type[file] [C:\documenti\lavoro
\workspace\s2zk\WebContent\WEB-INF]
2-nov-2009 14.20.48 com.avaje.ebean.server.deploy.DeployOrmXml
findAllOrmXml
INFO: No deployment xml (orm.xml etc) was loaded.
2-nov-2009 14.20.49
com.avaje.ebean.server.deploy.BeanDescriptorManager
readDeployAssociations
AVVERTENZA: Bean [pianosociale.model.Indicatore] has no unique id set
2-nov-2009 14.20.49
com.avaje.ebean.server.deploy.BeanDescriptorManager logStatus
INFO: Entities enhanced[17] subclassed[0]
2-nov-2009 14.20.49 com.avaje.ebean.config.PropertyExpression
evaluateExpression
AVVERTENZA: Unable to evaluate expression [catalina.base]
2-nov-2009 14.20.49
com.avaje.ebean.server.transaction.TransactionLogManager <init>
INFO: Transaction logs in: catalina.base\logs\trans
2-nov-2009 14.20.49
com.avaje.ebean.server.autofetch.AutoFetchManagerFactory
deserializeAutoFetch
INFO: AutoFetch deserialized from file [C:\documenti\lavoro\workspace
\s2zk\WebContent\WEB-INF\.ebean.s2.autofetch]
2-nov-2009 14.20.49 com.avaje.ebean.config.PropertyExpression
evaluateExpression
AVVERTENZA: Unable to evaluate expression [catalina.base]
2-nov-2009 14.20.49
com.avaje.ebean.server.autofetch.DefaultAutoFetchManagerLogging
logToJavaLogger
INFO: AutoFetch queryTuning[true] profiling[true] mode
[DEFAULT_ONIFEMPTY] profiling rate[0.05] min[1] base[10]
runScript
executing 1 of 38 drop table PSZ_AMBITO_TERRITORIALE cascade
constraints purge
... ignoring error executing drop table PSZ_AMBITO_TERRITORIALE
cascade constraints purge error: ORA-00933: SQL command not properly
ended

executing 2 of 38 drop table PSZ_AREA cascade constraints purge
... ignoring error executing drop table PSZ_AREA cascade constraints
purge error: ORA-00933: SQL command not properly ended
...


2. I'm using Oracle 9, so there is a limit of 30 chars in identifiers
length: the columns' name can be specified in @column(name="...")
annotations, for the join tables in many to many relationships I use
@JoinTable(name="...").
How can I specify the name of foreign key contraints when the defaults
one are too long?

...
Caused by: java.lang.RuntimeException: Error executing alter table
PSZ_ATTORE_SOCIALE add constraint fk_PSZ_ATTORE_SOCIALE_categori_3
foreign key (categoria_id) references PSZ_CATEGORIA_ATT_SOC (id)
at com.avaje.ebean.server.ddl.DdlGenerator.runStatement
(DdlGenerator.java:271)
at com.avaje.ebean.server.ddl.DdlGenerator.runStatements
(DdlGenerator.java:238)
at com.avaje.ebean.server.ddl.DdlGenerator.runScript
(DdlGenerator.java:216)
... 7 more
Caused by: java.sql.SQLException: ORA-00972: identifier is too long


Many many thanks

Claudio

[Rob Bygrave]

2) is a known bug #163 - fixed in HEAD.  http://www.avaje.org/bugdetail-163.html

1) I believe I fixed an issue around the drop when I fixed Bug 163, so I think that is also fixed.


Also...

Note: that I am now running ALL the tests against Oracle, MySql, Postgres and H2.  I generally use H2 during development as it is super fast to run the junit tests.  This means the junit tests have changed a little as some of them previously didn't run on all DB's (Identity/Autoincrement in MySql/H2 .... and DB Sequences on Oracle, Postgres and H2).

.... however, from now on I'm committed to running all the tests against H2, Oracle, MySql and Postgres prior to releasing a build. I should have done that before really - my bad !! 



Cheers, Rob.

[claudio]

Hi, seems there is another problem with DDL generation:

with Oracle 9 Ebean 2.1.0 generates the following statement:

alter table PSZ_PROGETTO_TO_FINALITA add constraint
fk_PSZ_PROGETTO_TO_FINALITA_P_01 foreign key (PSZ_PROGETTO_id)
references PSZ_PROGETTO (id) on delete restrict on update restrict;

both delete restrict and update restrict are not supported.
I think the delete and update clauses can safely be removed because
the default in Oracle is to disallow update and delete in the
referenced table when there are related records.


Claudio

[Rob Bygrave]

Quite right and thanks for pointing that out.

Note that this was also fixed as part of Bug 163... so already fixed in HEAD. I didn't explicitly state that on the bug details though ... so just added that note.

So FYI: I'm now running all tests against Oracle10, Postgres8, MySql5 and H2 ... so this includes DDL generation, DDL run, run all tests etc ... and so builds from now on have been tested against those DB's. 

If there is DB you want to add to that list let me know (SQLServer 2008 might be a candidate to add to that list).

Cheers, Rob.

[claudio]

Thanks Rob, I mainly use Oracle 9 at work and Postgres 8 and H2 at
home, so the supported platforms are ok for me.

In Italy many organizations use closed source DB, and the most used
are Oracle and SQL Server, so i think that supporting them both is
very convenient.

One last question: I have problems with Oracle 9 and sequence
support:

@Entity @Table(name="TEST") @SequenceGenerator(name = "GENERATOR",
sequenceName = "s_far_attestato")
public class Test{
@Id @GeneratedValue(strategy=GenerationType.SEQUENCE,
generator="GENERATOR")
private Integer id;
private String descrizione;

... getters + setters
}


Initialization code:

DataSourceConfig dataSourceConfig = new DataSourceConfig();
dataSourceConfig.set...

Oracle9Platform platform = new Oracle9Platform();
platform.getDbIdentity().setIdType(IdType.GENERATOR);

ServerConfig configuration = new ServerConfig();
configuration.set...
configuration.setDatabasePlatform(platform);

EbeanServerFactory.create(configuration);


when I save an object EBean writes the record correctly on DB, but
does not retrieve the id value generated using the sequence. There are
some hints in EBean documentation and forums about using an ID
Generator because Oracle 9 lacks generated keys support, but I cannot
make it works.
What am I missing?

Thanks

Claudio

[Rob Bygrave]

Ok, I'll have to have a look at this ... but there is an interesting story here ...

Note that with the next version of Ebean the strategy for using Sequences in Oracle (and other Db's with Sequences - Postgres/H2) has changed.

The reason for this change is that we want to transparently use JDBC batching  PreparedStatement.addBatch() PreparedStatement.executeBatch() etc.

... now, Oracle10 supports getGeneratedKeys but NOT when used with JDBC batching... and the Oracle10 JDBC drivers also don't support the standard JDBC api for batch statement execution. (If you are building Ebean 2.2 onwards from source we now have a dependency on the Oracle jdbc drivers ... as we need to cast to OraclePreparedStatement to use the Oracle specific methods to get back the row counts for the batched statements - we need the row counts for optimistic concurrency checking).

... SO... from Ebean 2.2 onwards the strategy with all Oracle versions (Oracle9, Oracle10, Oracle11) is to get the sequence value early and bind the value into the insert statement) ... rather than the current Oracle10 approach which includes sequence_name.nextval in the actual insert statement and uses getGeneratedKeys to get the sequence value back.

... so the upside is that from Ebean 2.2 onwards we can use JDBC statement batching transparently with Oracle9 and Oracle10 and Postgres - this can be a major performance improvement as it reduces the number of network messages.

Now when we get the sequence value early (as a separate sql statement ... select my_seq.nextval from dual) ...we would incur the cost of a query per row inserted.  We definately DON'T want the cost of an extra query per row inserted... so instead Ebean will batch load 20 sequences at a time ... and also include a mechanism to fetch more sequence ids via a background thread... aka when we use up half of the 20 sequence values Ebean will trigger off a background fetch for the next 20.  Yes, 20 is just the default and you can configure the actual batch size.

For Postgres... this approach is also used from Ebean 2.2 onwards.

Hopefully this makes sense - quite a bit of info there. I got stuck on following the getGeneratedKeys path (waiting/looking for its proper support in Postgres and Oracle... and now confident we should not be using getGeneratedKeys for those DB's).


Cheers, Rob.

[Rob Bygrave]

I have uploaded a release candidate for Ebean v2.2.0.

People can download it from here: http://groups.google.com/group/ebean/files

If you could try this out against Oracle9 that would be great (Oracle9
is now treated the same as Oracle10).

Note that there was some confusing code WRT IdType.GENERATOR and
IdType.SEQUENCE - mostly due to the previous use of getGeneratedKeys
with Oracle10. I have refactored that code so I believe it should be
easier to understand. All DB's have a default of IdType.SEQUENCE
(Oracle, Postgres, H2) or IdType.IDENTITY (mysql, sql server) as the
default strategy.

IdType.GENERATOR is typically for UUID types. Note that if the @Id
type is a UUID then Ebean will automatically assign a UUID specific
generator for that bean type.

Note: there is a Gotcha using JDBC statement batching
(PreparedStatement.addBatch() PreparedStatement.executeBatch()) with
Oracle ... specifically we need to cast to OraclePreparedStatement to
get the row counts (for optimistic concurrency checking). This is
automatically handled if you are using Ebeans DataSource
implementation but *NOT* if you are using another connection pool.

Anyway, I will be busy documenting this over the next week or so prior
to releasing Ebean 2.2.0.


Cheers, Rob.

On Nov 6, 9:34 am, Rob Bygrave <robin.bygr...@gmail.com> wrote:
> Ok, I'll have to have a look at this ... but there is an interesting story
> here ...
>
> Note that with the next version of Ebean the strategy for using Sequences in
> Oracle (and other Db's with Sequences - Postgres/H2) has changed.
>
> The reason for this change is that we want to transparently use JDBC
> batching  PreparedStatement.addBatch() PreparedStatement.executeBatch() etc.
>

> ... now, Oracle10 supports getGeneratedKeys but *NOT* when used with JDBC

[claudio]

Hi Rob, I tried 2.2.0 against Oracle 9 with the id field annotated
with

@SequenceGenerator(name = "GENERATOR", sequenceName =
"s_far_attestato")

@GeneratedValue(strategy=GenerationType.SEQUENCE,
generator="GENERATOR")

and it works.

thanks

Claudio

[Rob Bygrave]

Great.

Reminds me to point out (I'll put this in the docs) ...

... With Ebean you really only need to use these @SequenceGenerator @GeneratedValue annotations when you are using DB Sequences and the sequence names don't match the NamingConvention. (i.e. A typical naming convention for sequences could be to append "_seq" to the matching table name).

The 3 main cases are
- UUID
- DB Identity/AutoIncrement
- DB Sequences


So, if you are using a UUID type as the id property ...

@Id
UUID id;

... Ebean will automatically assign a UUID Generator for you.

If you are using a DB that uses Identity/AutoIncrement (such as MySql) then Ebean will use that strategy... else if the DB uses Sequences (Oracle, Postgres) then Ebean will by default use a sequence based on the NamingConvention - and you can use @SequenceGenerator @GeneratedValue if the sequence name doesn't match the NamingConvention.

There are a couple of DB's that support both sequences and identity ... H2 and DB2. However, H2 doesn't support getGeneratedKeys with JDBC batching - so imo it's really best to use Sequences with H2. DB2 ... well, can't say I have much experiance with that actually.

Anyway, perhaps useful background for those thinking those JPA annotations are ugly !!!!!

Also IMO we should be wary of other Id generators (other than UUID, Identity and sequence). Firstly DB's implement this well (Identity and Sequences) for highly concurrent use. Secondly... Ebean ORM is not always going to be the *ONLY* thing inserting into your DB - over time you could have other apps (non-java apps, stored procedures, data imports etc) wanting to load/insert into your DB and that is harder if you have a Java/ORM specific id generation mechanism in place. Obviously just my opinion here... but maybe something to keep in mind.



Cheers, Rob.