mapping classes

[Hielke Hoeve]

Hi there,

I have begun migrating from Hibernate to Ebean (whooptie!) and have a
few questions to things I can't find a replacement for.

Indices on fields
Cache regions
pure old sql queries that return a java.sql.ResultSet object.

Luckily converting most queries is a piece of cake thanks chaining
when adding the where clauses but I am missing the option to chain the
order by statements, now I have to append them myself and set the long
string. It would be nice to be able to chaing most properties of a
query. This safes time looking up the right syntax...

[Rob Bygrave]

>> Indices on fields

Does this mean DDL generation of an DB Index for a field/property/column?  Maybe you can give an example.


>> Cache regions

Can you explain more about what you are missing/wanting here.

>> pure old sql queries that return a java.sql.ResultSet object.

I'm not sure exactly what you are looking for but you can use SqlQuery or get the java.sql.Connection from a Transaction object and use your own jdbc code. I wonder if you mean something like SqlQuery but instead returns a java.sql.ResultSet rather than a List of SqlRow objects? 

Another approach that I have been thinking about is to provide a set of callback/visitor objects so that you (the developer) supply the sql/dml and use the PreparedStatement and ResultSet directly to set parameters and read the results ... but the management of the Connection and creating/closing of the PreparedStatement/ResultSet etc is handled for you.

>> missing the option to chain the order by statements

Something like:

query.addOrderBy("someProp");
query.addOrderBy("otherProp, OrderBy.DESCENDING);


They seem like pretty good idea's to me.


Cheers, Rob.

[Hielke Hoeve]

On Oct 15, 2:21 am, Rob Bygrave <robin.bygr...@gmail.com> wrote:
>*>> Indices on fields
>*

> Does this mean DDL generation of an DB Index for a field/property/column?
> Maybe you can give an example.

Yes in order to generate indices. For example:

public class Person
{
@ManyToOne(fetch = FetchType.LAZY)
@Index(name = "idx_person_address")
@JoinColumn(name = "incident")
private Address address;

....
}

>*>> Cache regions
>*

> Can you explain more about what you are missing/wanting here.

@Entity
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE, region =
"Company")
public class Person

@Entity
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE, region = "Global")
public class Country

Then in a config file we specify:

<cache
name="hibernate-.Company"
maxElementsInMemory="10000"
eternal="false"
timeToIdleSeconds="3600"
timeToLiveSeconds="3600"
overflowToDisk="false"
diskPersistent="false"
diskExpiryThreadIntervalSeconds="120"
/>

<cache
name="hibernate-.Global"
maxElementsInMemory="5000"
eternal="false"
timeToIdleSeconds="1800"
timeToLiveSeconds="1800"
overflowToDisk="false"
diskPersistent="false"
diskExpiryThreadIntervalSeconds="120"
/>

For example, we have an application to which several companies can
login, this data is company related. We also have information that is
used by all of them, we call this global info. We currently have this
divided into 2 cache regions. "company" and "global". I think you
could see this as dividing the bean cache into parts. I'm not sure if
you would benefit from using a config file as we mostly use the
default settings, but in the case of Hibernate you need to specify the
region names in the config. My personal flavor would be to scan for
regions in the class files but then again you'd have to guess for the
region size.

> Another approach that I have been thinking about is to provide a set of
> callback/visitor objects so that you (the developer) supply the sql/dml and
> use the PreparedStatement and ResultSet directly to set parameters and read
> the results ... but the management of the Connection and creating/closing of
> the PreparedStatement/ResultSet etc is handled for you.

Ah I missed that part :) I think getting the connection is good
enough. Your suggestion sounds nice. Maybe let the user provide a
callback class which implements an interface with a function that has
a connection argument. This would allow some space for Ebean to manage
the connection / transaction

>*>> missing the option to chain the order by statements
>*

I meant like the .where() and .having().

Ebean.find(Person.class).where().eq("name", "Pete");
Ebean.find(Person.class).order().asc("name");
Ebean.find(Person.class).group().asc("name");


Is there also a way to intercept all queries and saves? We have some
audit information we automatically update every time an entity is
saved or updated using an interceptor. This way we can log who edited
which entity etc. Also we add the company id to almost every query to
limit the load on the database and prevent companies from seeing each
others data.

Cheers for the quick feedback!

[Rob Bygrave]

>>
   @ManyToOne(fetch = FetchType.LAZY)
   @Index(name = "idx_person_address")
   @JoinColumn(name = "incident")
   private Address address;
<<

Ok, note that the DDL Generation from Ebean does by default generate indexes for the foreign key constraints but you can't define you're own arbitrary indexes (and right now you can't control the naming convention for the indexes constraints etc). So I'm thinking...

- I don't see an @Index in the JPA spec ... is this annotation defined somewhere?
- You want to define your own indexes on non-foreign key columns (I think so but just checking as the example looks like a FKey column to me)?
- How does it work for a single index on multiple columns?
- Do you want to control the naming convention Ebean uses for the indexes/constraints it creates?

Sorry if seems like 1000 questions :) ... but just helps me fully understand.

>> Cache Regions

Ok.  So I take it that means you don't want the "customer" caching to take over (grow massively) and kick out entries from the "global" data cache ... as that would negatively impact other customers. Yes, I see that.

>>
Ebean.find(Person.class).

where().eq("name", "Pete");
Ebean.find(Person.class).order().asc("name");
Ebean.find(Person.class).group().asc("name");

<<

Ok I get that.  query.order().asc("name") ...   fyi: I don't get the group().asc("name") bit but that maybe a typo.


>>
Is there also a way to intercept all queries ... ?
<<

Right now no.  ... but I see exactly what you are trying to do here... and I actually see it's on my todo list so I'll log it as an enhancement request now.

There is a com.avaje.ebean.event.BeanFinder ... but it is not really designed for this problem. It is more for replacing the find functionality for a specific bean type... aka instead of a jdbc/rdbms query... return data from some other source - csv file, excel file ... or in memory objects as in the case of the "meta" entity beans (MetaQueryStatistic etc).

What we really want is something more like:

abstract class BeanQueryIntercept
public abstract boolean isRegisterFor(Class<?> cls);
public void preQuery(BeanQueryRequest<?> request);

So you control what its registered for (a lot of your entity types I'm thinking) ... say all beans with a given interface (marker interface?) ... and all query requests for that type go through the preQuery() method... where you can just add your ... company predicate.

Does that sound right?

>>
We have some audit information we automatically update every time an entity is
saved or updated using an interceptor.
<<

Yes, a common requirement (lastUpdatedBy, createdBy, lastUpdatedTimestamp, createdTimestamp).

Here you can use a BeanPersistAdapter with the preInsert, preUpdate, preDelete methods.

That is the approach you have to take at the moment.

Note that I have been wondering about another approach to this.  Ebean could provide a "CurrentUser interface" that can be implemented that returns the current application specific user (typically from an application specific thread local).  The benefit of this approach is that we can also do things like put the userid in each statement/event that goes into the transaction logs... aka you can then view the transaction log events (statements/bind values etc) for a specific user.

With this "CurrentUser interface" approach I have been thinking of using 2 annotations ... maybe  @UpdatedByUser @CreatedByUser ... and you provide an implementation of the interface and then Ebean does the rest.

... but that doesn't exist at the moment so you have to use the BeanPersistAdapter right now.

NOTE: For the lastUpdatedTimestamp, createdTimestamp ... you could use @CreatedTimestamp and (@UpdatedTimestamp or @Version).

NOTE: There is also a BeanListener... which is NOT useful for this purpose.  If you are not familiar that is different in that it only gets notified of events that successfully commited.  Typically you'd use this to generate derived content (regenerate some html content) when data was successfully changed.


Cheers, Rob.

[Hielke Hoeve]

On Oct 16, 10:57 pm, Rob Bygrave <robin.bygr...@gmail.com> wrote:
> - I don't see an @Index in the JPA spec ... is this annotation defined
> somewhere?
> - You want to define your own indexes on non-foreign key columns (I think so
> but just checking as the example looks like a FKey column to me)?
> - How does it work for a single index on multiple columns?
> - Do you want to control the naming convention Ebean uses for the
> indexes/constraints it creates?

In this case yes we do want to make Indices on FK constraint as our
good friend Hibernate does not generate them. If Ebean does so then
i'm happy.
However I'm sure other people are/will be trying to get custom indices
working for example an entity with start and end date?

> Ok.  So I take it that means you don't want the "customer" caching to take
> over (grow massively) and kick out entries from the "global" data cache ...
> as that would negatively impact other customers. Yes, I see that.

Exactly.

> Ok I get that.  query.order().asc("name") ...   fyi: I don't get the
> group().asc("name") bit but that maybe a typo.

The group() is the GROUP BY statement. We do have statements that
group data and perform counts on columns. I have not been able to find
this in Ebean.

> abstract class BeanQueryIntercept
> public abstract boolean isRegisterFor(Class<?> cls);
> public void preQuery(BeanQueryRequest<?> request);
>

> Does that sound right?

Sounds about right.

> Here you can use a BeanPersistAdapter with the preInsert, preUpdate,
> preDelete methods.

Ah, my bad. That's exactly what i need.

> Note that I have been wondering about another approach to this.  Ebean could
> provide a "CurrentUser interface" that can be implemented that returns the
> current application specific user (typically from an application specific
> thread local).  The benefit of this approach is that we can also do things
> like put the userid in each statement/event that goes into the transaction
> logs... aka you can then view the transaction log events (statements/bind
> values etc) for a specific user.

The more the better, what if there isn't a user. Which is the case
with a news site.

> NOTE: For the lastUpdatedTimestamp, createdTimestamp ... you could use
> @CreatedTimestamp and (@UpdatedTimestamp or @Version).

Nice!

[edge]

> > - I don't see an @Index in the JPA spec ... is this annotation defined

@Column(length = 50, unique = true)

should produce a unique index

Hibernate uses for non unique indices
@Index(name="story1index")
http://docs.jboss.org/hibernate/stable/annotations/reference/en/html_single/#d0e2305

You can use @UniqueConstraint for compound uniques indices e.g.

http://www.oracle.com/technology/products/ias/toplink/jpa/resources/toplink-jpa-annotations.html#UniqueConstraint

We probably need to add some more support for index creation but I
think its not a critical feature at the moment as you can easily run a
script to add the indices after the schema has been created.