created_at in database changes on every update

[sveri]

Hello again,

i have a created_at column in my  database table, created with this sql syntax:
created_at TIMESTAMP DEFAULT NOW(),
It is the only timestamp column in this table, and it should insert the date on creation, but not on update. It does work if i do it with plain sql like insert and update, but it does not work using circumflex-orm.
I already found this thread: https://groups.google.com/forum/?fromgroups#!searchin/circumflex-scala/created_at/circumflex-scala/iWBdlrPQOFY/G6SKD4iVQ8IJ
and tried the two proposed ways there, but without success.
Everytime i update an entry with circumflex's .save() method it does update the "created_at" column too.

Right now my class' val looks like this:
val createdAt = "created_at".TIMESTAMP.DEFAULT("current_timestamp")

Which syntax do i have to use on application side to get my desired behaviour?

Thanks again,
Sven

[sveri]

As i have not got farer in this matter i started writing tests (obv. should have done this before, but, well :D).
However, this is my class so far:

class Task extends Record[Long, Task] with IdentityGenerator[Long, Task] { 
  def this(name: String, description: String, done: Boolean, dueTo: Date, id: Long) = {
    this()
    this.name := name.trim()
    this.description := description.trim()
    this.dueTo := dueTo
    this.done := done
    if(id == 0) this.id.setNull else this.id := id   
  }
   
  def PRIMARY_KEY = id
  def relation = Task
 
  val id = "id".BIGINT.NOT_NULL.AUTO_INCREMENT
  val name = "name".VARCHAR(255)
  val description = "description".TEXT

  val createdAt = "created_at".TIMESTAMP.DEFAULT("current_timestamp")

  val dueTo = "due_to".DATE
  val done = "done".BOOLEAN.NOT_NULL(false)

}

object Task extends Task with Table[Long, Task] {
  def apply(name: String, description: String, done: String, dueTo: Option[Date], id: Long) = {
      new Task(name, description, done == "on", dueTo getOrElse null, id)}
 
  // dummy unapply so that we can provide something for the form
  def unapply(t : Task) = Option(t.name(), t.description(), t.done().toString, null, t.id())
}

Now, if i do create a new task with:
val task = new Task("name", "description", true, null, 0)
save, refresh, change the name, save (which updates the record) and refresh again, the timestamp does not change.

But, if i use the apply method from the object in a similar way:
val taskApply = Task.apply("name", "description", "on", None, 0)
save, refresh and:
val taskApplyTwo = Task.apply("name", "description", "on", None, taskApply.id())
then save and refresh the second one too, i get two different timestamps.

And i just cannot see why this is?

I will add the two different tests for convenience:

"Task update" should {
    "not change the created_at timestamp" in {
      Context.executeInNew { ctx =>
        val task = new Task("name", "description", true, null, 0)
        task.save
        task.refresh
        createdTasks += task

        val timeStamp = task.createdAt()

        Thread.sleep(1500)

        task.description := "changed desc"
        task.save
        task.refresh

        task.createdAt() must equalTo(timeStamp)
      }
    }
  }


"Task apply, save and update" should {
    "keep the same created_at timestamp in database" in {
      Context.executeInNew { ctx =>

        val taskApply = Task.apply("name", "description", "on", None, 0)

        taskApply.save
        taskApply.refresh
        Thread.sleep(1500)

        val taskApplyTwo = Task.apply("name", "description", "on", None, taskApply.id())
       
        taskApplyTwo.save
        taskApplyTwo.refresh
       
       
        taskApply.createdAt() must equalTo(taskApplyTwo.createdAt())
      }
    }
  }

Best regards,
Sven

[sveri]

Sorry for the spam :D

I understand now why this happens.
The difference is the way i acces the task objects.

1. If i do fetch an existing task from database with Task.get(id), change that, and save it again, the timestamp remains the same.
2. If i create a new Task object, with an existing id, the database get's updated, but with a new timestamp.

Now, my question must be, is there a way to keep circumflex from updating this field, if an update got executed? No matter if it is null, or some Date?

Best regards,
Sven

[Boris Okunskiy]

Hi Sven,

Sorry for the late response, I've been into something big for a couple of days.

Regarding the issue number one ("cannot read timestamp after insert"), the columns with `NULL` are skipped by the default dialect, so that the database can initialize them with NULL if they are nullable, or with the DEFAULT clause, if it exists. My only suggestion is that the timestamp column should be NOT_NULL in this case, so that the database refer to the DEFAULT clause rather then just emit NULL in that column. Please let me know, if NOT_NULL resolves the issue.

Now, issue number two is pretty interesting: you see, Circumflex ORM tries to be "honest" enough to keep all fields in each UPDATE statement. The only way to "skip" the timestamp here is to list all other fields (sans `createAt`) as arguments to the UPDATE method, like this:

    task.UPDATE(task.id, task.name, task.description, task.dueDate, task.done)

And still, in all my projects, I don't experience this problem: I just don't write into that `createAt` with my application.

Please let me know, if something there helps.

Best regards,

Boris Okunskiy

[sveri]

Hi Boris,

Am Donnerstag, 3. Mai 2012 09:33:27 UTC+2 schrieb Boris Okunskiy:

Sorry for the late response, I've been into something big for a couple of days.

No problem, it's the same for me, work and family comes first :D

Regarding the issue number one ("cannot read timestamp after insert"), the columns with `NULL` are skipped by the default dialect, so that the database can initialize them with NULL if they are nullable, or with the DEFAULT clause, if it exists. My only suggestion is that the timestamp column should be NOT_NULL in this case, so that the database refer to the DEFAULT clause rather then just emit NULL in that column. Please let me know, if NOT_NULL resolves the issue.

To be honest, i dont know what exactly you refer to here?
The only thing i could imagine was that i had to do a refresh after the first save, to get the created_at timestamp. Out of curiosity i did make the column created_at NOT_NULL, and left out the refresh, but it didn't work.
 

Now, issue number two is pretty interesting: you see, Circumflex ORM tries to be "honest" enough to keep all fields in each UPDATE statement. The only way to "skip" the timestamp here is to list all other fields (sans `createAt`) as arguments to the UPDATE method, like this:

    task.UPDATE(task.id, task.name, task.description, task.dueDate, task.done)

This way it works. Thanks very much :-)
It's easy to provide a savePreserveCreatedAt Method for every DB-Object that calls Task.update, in the end i like it more than: fetch -> save.

And still, in all my projects, I don't experience this problem: I just don't write into that `createAt` with my application.

Hm, how do you do that exactly? I did not write into that createdAt column either. I never ever touched it, all i do is define it in the db object like i wrote before.

However, it's working better now regarding performance and code, that's always good, thank you for your time.

Best regards,
Sven

[Boris Okunskiy]

Greetings Sven,

To be honest, i dont know what exactly you refer to here?
The only thing i could imagine was that i had to do a refresh after the first save, to get the created_at timestamp. Out of curiosity i did make the column created_at NOT_NULL, and left out the refresh, but it didn't work.
 

It just came out of a simple assumption.

Let's say you have following table:

create table test(id bigint not null primary key, created_at timestamp default now());

Now since `created_at` actually allows NULLs,  following two statements can actually behave differently:

INSERT INTO test(id, created_at) VALUES(1, NULL);

INSERT INTO test(id) VALUES(1);

Now as far as I am concerned, our default dialects issue the second version, that is, they just skip uninitialized columns in DMLs. In most cases with DEFAULT clauses it should be fine.

So by far I still can't get it, how you got the two different timestamps in your example with `taskApply` and `taskApplyTwo`. Perhaps, we need to examine the exact SQLs issued to get the better understanding.

However, it's working better now regarding performance and code, that's always good, thank you for your time.

You are always welcome :)

Best regards,
Sven

[sveri]

Hi Boris,

i saw that you output Debug messages when running tests, so here they are:

This happens when i call Task.apply() -> Task.save() -> Task.refresh -> Task.apply(with the id from the first apply) -> Task.save and Task.refresh.
Just what i described above, and this is the case where created_at gets changed:

INSERT INTO task (name, description, done) VALUES (?, ?, ?)
UPDATE task AS root SET name = ?, description = ?, created_at = ?, due_to = ?, done = ? WHERE root.id = ?


Here we have the same, but instead of using Task.save the second time, i do what you proposed, i do a Task.update(...) without created_at.

INSERT INTO task (name, description, done) VALUES (?, ?, ?)
UPDATE task AS root SET name = ?, description = ?, done = ?, due_to = ? WHERE root.id = ?

I left out the selects, and i will add my table definition below (just for convenience again). If i can provide some other information i'd be happy to do so.

Best regards,
Sven

class Task extends Record[Long, Task] with IdentityGenerator[Long, Task] {
  def this(name: String, description: String, done: Boolean, dueTo: Date, id: Long) = {
    this()
    this.name := name.trim()
    this.description := description.trim()
    this.dueTo := dueTo
    this.done := done

    if (id == 0) this.id.setNull else this.id := id

  }

  def PRIMARY_KEY = id
  def relation = Task

  val id = "id".BIGINT.NOT_NULL.AUTO_INCREMENT
  val name = "name".VARCHAR(255)
  val description = "description".TEXT
  val createdAt = "created_at".TIMESTAMP.DEFAULT("current_timestamp")
  val dueTo = "due_to".DATE
  val done = "done".BOOLEAN.NOT_NULL(false)
 
}

[Boris Okunskiy]

Strange… how exactly these timestamps change? Maybe it only involves changing timezone? Did you compare their UNIX times (in milliseconds)?

Either way, ORM seems to do what it's supposed to: insert a record first time, then update it second. As long as the values of `created_at` remain the same in application, their values in backend should remain the same, too.

Anyway, glad that the update with explicit fields works for you :)

Best regards,

Boris Okunskiy

[sveri]

Hi Boris,

i think i fully understand this now. I always set created_at to null in the frontend, making it null in the backend too, before the update, this is then it get's reset.
Thanks for your efforts.
My mistake was, that i always assumed created_at will never get touched if it is the 'special' column in the database. It was a wrong mindset, so to say :-)

Thank you again, very much :-)
Sven