Database default values

[Ian Phillips]

Hi,

What's the recommended way to support default values in the database? If I have a table defined as (in PostgreSQL 9.3):

create table bw_queries (

  id           bigserial primary key,

  project_name text not null,

  query_name   text not null,

  created_at   timestamp with time zone not null default current_timestamp,

  updated_at   timestamp with time zone not null default current_timestamp,

  constraint bw_queries_name_idx unique (project_name, query_name)

);

and an Ebean model class as:

@Entity
@Table(name = "bw_queries")
public class BWQuery extends Model {
    public static Model.Finder<Long, BWQuery> find = new Model.Finder<>(Long.class, BWQuery.class);
    

    // this seems to be needed or it Ebean uses the wrong sequence name
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "bw_queries_id_seq")
    @Id public Long id;

    @Constraints.Required public String projectName;
    @Constraints.Required public String queryName;
    public LocalDateTime createdAt;
    public LocalDateTime updatedAt;
}

Then creating a new model instance fails when I try to save it as it attempts to explicitly store nulls into the two timestamps.

I've tried marking it @Basic(optional=true) and also marking it as a generated value with type AUTO to no avail. Is the correct way to just set the default values in the constructor/field (I've tried this and it works)? I could live with this but it seems like needless duplication of code if they are already defined in the DB.

Cheers,

Ian.

[Ian Phillips]

Inevitable, 5 minutes after posting the question I find the answer!

@Column(insertable = false, updatable = false)

public LocalDateTime createdAt;

@Column(insertable = false)

public LocalDateTime updatedAt;

So, a different question: given that the Ebean @CreatedTimestamp and @UpdatedTimestamp annotations don't work with LocalDateTime objects (even if a type converter is defined) is there a way to add a "pre-commit hook" to the model so that I can always update the updatedAt time without needing to do it explicitly in code everywhere?

Cheers,

Ian.

[Ian Phillips]

@Column(insertable = false, updatable = false)

public LocalDateTime createdAt;

This doesn't work. Bean just throws OptimisticLockExceptions whenever an entity is changed. I'm back to duplicating in Java the defaults that already exist in my DB.

:-(

 

[Rob Bygrave]

>> What's the recommended way to support default values in the database? If I have a table defined as (in PostgreSQL 9.3):

So here we are talking about createdAt and updatedAt (and specifically not talking about "ID" columns which we populate via getGeneratedKeys).

The general problem with values that are created in the DB via default values (or DB triggers) is that typically the client (in our case EbeanORM) doesn't know what these values are unless we do extra work to fetch them (and typically we would need to fetch these values over a network). So this becomes do you want to perform some extra work (extra network traffic) after every insert and update to get the default values set by the database.  

With EbeanORM we are typically saying no, we don't want to do that extra work / extra traffic and instead use @CreatedTimestamp and @UpdatedTimestamp.

>> This doesn't work. Bean just throws OptimisticLockExceptions whenever an entity is changed.

It wouldn't if you were using a 4.x version of Ebean. Specifically you are not using a @Version property and in that case with 4.x the behavior changed to not perform optimistic concurrency checking.

Cheers, Rob.

--

---
You received this message because you are subscribed to the Google Groups "Ebean ORM" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ebean+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Ian Phillips]

>> This doesn't work. Bean just throws OptimisticLockExceptions whenever an entity is changed.

It wouldn't if you were using a 4.x version of Ebean. Specifically you are not using a @Version property and in that case with 4.x the behavior changed to not perform optimistic concurrency checking. 

Yeah, I'm just using the version bundled with Play, 3.3.4 I think. Do you know whether anybody has tried running 4.1.x under Play? Is it just a case of adding the 2 dependencies to your build file ?

Cheers,

Ian.

[Jae Gangemi]

   doesn't work :(

   there are plans to split ebean out from the 'core' of play so that updates, etc can happen independently and faster but that isn't slated until the 2.4 release and i'm not sure when that's due (i'm in an similar situation where i want to use play but my model has been built w/ bean 4.x).

   this is the link to the github ticket that discusses this:

    https://github.com/playframework/playframework/issues/2926