Maintaining created_at and updated_at columns.

[rayfinkle]

What's the best way to maintain created_at and updated_at columns from inside slick 2? 

Thanks!

[rayfinkle]

So I've been banging my head on this one. 

I have a createdAt column which is an Option[DateTime] and has an O.default value of the current time. The whole table is mapped to a case class. If I use the case class with None for createdAt, slick attempts to insert Null rather then the default value. What is the standard way to handle this scenario?

Thanks

[Naftoli Gugenheim]

Are you using case classes or tuples? Do you want the time of database insertion, or the time the object was instantiated?

--

---
You received this message because you are subscribed to the Google Groups "Slick / ScalaQuery" group.
To unsubscribe from this group and stop receiving emails from it, send an email to scalaquery+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/scalaquery/f6489f01-e981-44a1-966e-aec93da36f18%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Fayimora Femi-Balogun]

I'm having pretty much the same problem. I'm using case classes and I'll like createdAt to have the Timestamp of when the object/record was created while updatedAt should have the Timestamp of when the record was last manipulated(last update operation).

Any idea how to go about this?

Cheers,

Fayi

[Naftoli Gugenheim]

My question is, do you want the time it was created or updated in the database store, or at the time your code created or updated the object in memory?

To view this discussion on the web visit https://groups.google.com/d/msgid/scalaquery/f2b07017-d665-444f-ac81-8d82fbda8e17%40googlegroups.com.

[Fayimora Femi-Balogun]

Hmm I'll say the former, so in the database. However, both would be nice to know.

Cheers

[Naftoli Gugenheim]

Doing it in the database is probably tricky, I guess you would need to somehow attach the logic to the <> expression, that is the function that converts from case class to tuple should return the right value in the tuple. The question is how you communicate to the tuple what to do. You could have a case class with a "new" field and a "dirty" field, that doesn't exist in the database, and define the copy method by hand, such that it always sets dirty = true. The "new" field should default to true. Then the <> functions have to be written by hand, not mapping those fields to a tuple element. The function that converts from a tuple to a case class (read from db) should always set "new" and "dirty" to false. The function that converts from case class to tuple (write to db) should check the "new" and "dirty" fields and if they are true, override the value from the case class for "created_at" and "updated_at" respectively.

Alternatively you could not put "created_at" in the mapped projection, just define it in the database with a default value. You would still probably want it in *, since I think ddl uses that.

The problem with this is that when you write q.update(obj), obj will not change, thus it will still have dirty = true. You could make it a var and have the <> function set it, but that creates all the var problems: can't use in hash set or map (solution: don't make it a parameter), multiple threads, etc.

If you understand that it should be clear how you could do it by in-memory.

To view this discussion on the web visit https://groups.google.com/d/msgid/scalaquery/ba20a464-01ca-4bb5-801f-b598c1fecf7f%40googlegroups.com.

[Fayimora Femi-Balogun]

Wow seems like an awful lot of work for a simple feature. I still need to read this some 3 more times to fully understand it. Things like these make me miss Rails. I thought Slick would have this out of the box.

Do you by any chance have any code examples? It will help understand this.

Cheers

[Marc Siegel]

> Wow seems like an awful lot of work for a simple feature. I still need to

> read this some 3 more times to fully understand it. Things like these

> make me miss Rails. I thought Slick would have this out of the box.

Hey! I hear where you're coming from. You have to understand that the Slick

community is a different community. For example, when you said:

>> My question is, do you want the time it was created or updated in the

>> database store, or at the time your code created or updated the object

>> in memory?

>

> Hmm I'll say the former, so in the database. However, both would be nice

> to know.

...then nafg answered that question, not the question "how do I get something

like what Rails does", which would actually be the letter -- Rails (actually,

ActiveRecord) gives you the created_at / updated_at time from the ruby code,

and does not ask the database for the time, at least the last time that I 

checked.

Since that's a simpler problem to solve, I imagine that the solution will be

simpler as well.

Does that help at all?

-Marc

[Fayimora Femi-Balogun]

Hi Marc, 

Yes I actually do understand it. Whether the timestamp is recorded at database level or application level doesn't really matter to me right now. I was just interested in how I could get what ActiveRecord provides. Maybe I should have said that from the start but I was worried I'll start getting "Slick and ActiveRecord are different" type of responses.

Anyways, right now I have Timestamp code littered around my code(every one of my table needs those columns) and it's annoying. I really don't want to have to manually update those columns. I was hoping there was a way to do something like:

def createdAt = column[Timestamp]("CREATED_AT", O.InitialVal(getCreatedAtTime()), O.NotNull)

def updatedAt = column[Timestamp]("UPDATED_AT", O.InitialVal(getCreatedAtTime()), O.Default(getUpdatedAtTime()), O.NotNull)

Code example might be a little silly but you get the idea. That way, I don't have to create a Timestamp object every time I want to update an attribute.

Fayi

[Marc Siegel]

Cool -- so yeah let's discuss the best way to get the rails-like functionality in a simple but composable way...

[Naftoli Gugenheim]

If you have column definitions you want to use in a lot of tables, just put them in a trait. (You have to make sure to remember to include them in the * projection.) You may want to mix a related trait into your case classes, that way you'll be forced to remember to include the fields there too.

So something like:

trait CreatedAt {

  def createdAt: Timestamp

}

trait UpdatedAt {

  def updatedAt: Timestamp

}

trait Timestamped extends CreatedAt with UpdatedAt

trait CreatedAtTable { this: Table[_] =>

  def createdAt = column[Timestamp]("CREATED_AT", O.InitialVal(getCreatedAtTime()), O.NotNull)

}

trait UpdatedAtTable { this: Table[_] =>

  def updatedAt = column[Timestamp]("UPDATED_AT", O.InitialVal(getCreatedAtTime()), O.Default(getUpdatedAtTime()), O.NotNull)

}

trait TimestampedTable extends CreatedAtTable with UpdatedAtTable { this: Table[_] => }

Sorry I don't have concrete code for my previous message, I would have to work it out in an actual project exactly how to write it. If you need specific clarification just ask.

--

---
You received this message because you are subscribed to the Google Groups "Slick / ScalaQuery" group.
To unsubscribe from this group and stop receiving emails from it, send an email to scalaquery+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/scalaquery/ad56f27a-be3c-4ca1-9c0c-36efaa6153ca%40googlegroups.com.

[Fayimora Femi-Balogun]

I did it like this:

trait TimestampedTable {this: Table[_] =>

  def createdAt = column[Timestamp]("CREATED_AT", O.NotNull)

  def updatedAt = column[Timestamp]("UPDATED_AT", O.NotNull)

}

class Contests(tag: Tag) extends Table[Contest](tag, "CONTESTS") with TimestampedTable {

// other attrs and projection here

}

Do you mind explaining this part?: this: Table[_] =>

I have no idea what is going on there.

Cheers.

[Yoel R GARCIA DIAZ]

@Fayimore

Reading the thread it isn't clear to me where did you find troubles. I almost always have `created_on` and `updated_on` columns in my tables and have never got any problems inserting NULL.

here an example of my evolution sql for a table

# --- !Ups

CREATE TABLE `Sellings`(
  `id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `status` VARCHAR(25) NOT NULL,
  `userid` BIGINT NOT NULL,
  `transactionid` BIGINT NOT NULL,
  `created_on` TIMESTAMP DEFAULT 0,
  `updated_on` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                       ON UPDATE CURRENT_TIMESTAMP,
  INDEX `indx_selling_user` (`userid`),
  CONSTRAINT `fk_selling_transaction`
  FOREIGN KEY (`transactionid`) REFERENCES `Transactions`(`id`)
    ON UPDATE CASCADE
    ON DELETE CASCADE
) ENGINE=InnoDB;

# --- !Downs

DROP TABLE `Sellings`;

there case class for the table

case class Selling(id: Option[Long] = None,
                        status: String,
                        userid: Long,
                        transactionid: Long,
                        created_on: Option[Timestamp] = None,
                        updated_on: Option[Timestamp] = None)


and the table

class Sellings(tag: Tag) extends Table[Selling](tag, "Sellings") {
  def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def status = column[String]("status", O.NotNull)
  def userid = column[Long]("userid", O.NotNull)
  def transactionid = column[Long]("transactionid", O.Nullable)
  def created_on = column[Timestamp]("created_on", O.Nullable)
  def updated_on = column[Timestamp]("updated_on", O.Nullable)
  def * = (id.?, status, userid, transactionid, created_on.?, updated_on.?) <> (Selling.tupled, Selling.unapply _)
  def transaction = foreignKey("fk_selling_transaction", transactionid, TableQuery[Transactions])(_.id)
}

and here is an extract of a script inserting a record on that table

Json.obj(
              "status" -> "pending",
              "userid" -> transaction.sellerid,
              "transactionid" -> transId
            ).validate[Selling].map { sellTrans =>
              Sellings.insert(sellTrans)
}

As you can see since we left it up to that database to manage these filed we can just "ignore" them when inserting and when updating and MySql will take care of maintaining these columns up-to-date...

Oh yes, I just realise that you might not be on MySql but if you are this definitely works :)

[Fayimora Femi-Balogun]

Thanks for the perfect example. I didn't know I could do this on the database level. Unfortunately, I'm using postgres and I cant seem to find any recent articles on how to achieve this easily there. Most reasonable thing I found was this(http://stackoverflow.com/a/1036010/751077). Seems I have to create a trigger for every table :(

By the way, why is your created_on having a default value 0 and not CURRENT_TIMESTAMP?

Fayi

[Yoel R GARCIA DIAZ]

That was a bad copy/paste... I usually have updated_on default value as 0 and created_on as CURRENT_TIMESTAMP... good catch :)

[Yoel R GARCIA DIAZ]

Actually revising my code this is not a bad copy/paste, it's a common pattern for MySQL

see this question/answer http://stackoverflow.com/questions/4897002/mysql-current-timestamp-on-create-and-on-update

[Fayimora Femi-Balogun]

So I changed my code but when I try to create an object, it requires values for the default arguments.

case class Contest(id: Option[Long] = None,

                   createdAt: Option[Timestamp] = None,

                   updatedAt: Option[Timestamp] = None,

                   title: String,

                   author: String)

new Contest("new title", "damainboss")

Any idea why? I thought giving the attributes default values meant i didn’t have to pass in values.

Fayi 

-- 

---------------------

Fayimora Femi-Balogun

Computer Science Student

Sent with Sparrow

You received this message because you are subscribed to a topic in the Google Groups "Slick / ScalaQuery" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/scalaquery/EbPA2Iv8DT4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to scalaquery+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/scalaquery/a7101f1b-576c-44b5-b468-78eb239f965d%40googlegroups.com.

[Fayimora Femi-Balogun]

Fixed this by moving the optional arguments to the end of the argument list. 

When i try to insert a Contest, I get a weird type mismatch error

val contests = TableQuery[Contests]

contests += Contest("new title", "damainboss")

[error]  found   : Int

[error]  required: List[models.Contest]

[error]       contests += Contest("new title", "damainboss")

[error]

Isn’t that the right way to insert data? The arrow was pointing at the “+=“. I’m not even sure where it got an Int from. :(

-- 

---------------------

Fayimora Femi-Balogun

Computer Science Student

Sent with Sparrow

[Yoel R GARCIA DIAZ]

oh I see... in my example I was using an abstraction to create my object... I don't create the object directly, instead I let other do the work

to follow that example

  implicit val sellingFormat = Json.format[Selling]
  val sellingForm = Form(
    mapping(
      "id" -> optional(longNumber),
      "status" -> nonEmptyText,
      "userid" -> longNumber,
      "transactionid" -> longNumber,
      "created_on" -> optional(of[Timestamp]),
      "updated_on" -> optional(of[Timestamp])
    )(Selling.apply)(Selling.unapply)

however I think that your later error might be because of your case class do not match the DB/evolution?

I usually insert like this contests.insert(contest)

[Fayimora Femi-Balogun]

I tried using insert but got the same error. In reality, I’ll be doing something similar to you, that is, creating the object from JSON but I’m just trying to insert into the DB to test that everything is fine.

Where is it finding that Int?? :(

-- 

---------------------

Fayimora Femi-Balogun

Computer Science Student

Sent with Sparrow

To view this discussion on the web visit https://groups.google.com/d/msgid/scalaquery/d27439b7-6b04-428e-9a4e-9e4312f8cf7b%40googlegroups.com.

[Yoel R GARCIA DIAZ]

do you have the evolution script I could have a look at? since you reordered the case class arguments, these might not matched the order of your evolution? is that possible? I think that the Int is your auto-increment column. That's the only Int right?

[Fayimora Femi-Balogun]

Here is the evolution script https://gist.github.com/322838eb588344d25bc2 The entire project is at http://github.com/fayimora The last commit are the changes I made.

Thanks

-- 

---------------------

Fayimora Femi-Balogun

Computer Science Student

Sent with Sparrow

To view this discussion on the web visit https://groups.google.com/d/msgid/scalaquery/8e406a05-0f4c-4a12-aec2-91429db6dc2b%40googlegroups.com.

[Yoel R GARCIA DIAZ]

I looked at your project... this is the evolution that describes the table to the BD https://github.com/fayimora/brutehack-backend/blob/master/conf/evolutions/default/1.sql

As you can see the ordering of the columns do not match that of the ordering of the case class arguments. I haven't tried to insert something unordered but there is where I think you have a problem. You can avoid things like that by keeping your case class in sync and abstracting using the tools provided by Play/Slick. Good Luck!

[Fayimora Femi-Balogun]

Are you referring to the create statement? If yes then I updated it and it didn’t make any difference. I still got the same error. :(

-- 

---------------------

Fayimora Femi-Balogun

Computer Science Student

Sent with Sparrow

To view this discussion on the web visit https://groups.google.com/d/msgid/scalaquery/abb8d849-b3c7-43c3-96dc-21e468c60c09%40googlegroups.com.

[Fayimora Femi-Balogun]

Never mind. I fixed the issue. The order of the column on the DB doesn’t matter. The function I was testing that insert statement with had a return type of List which I totally forgot about. Thanks for the help Yoel!

-- 

---------------------

Fayimora Femi-Balogun

Computer Science Student

Sent with Sparrow

On Tuesday, 8 July 2014 at 23:25, virtualeyes wrote:

Yoel, nice.

Question, why don't you stick the following in a helper trait?

def created_on = column[Timestamp]("created_on", O.Nullable)

def updated_on = column[Timestamp]("updated_on", O.Nullable)

Need to manually define these properties for the case class but for the table mapping the same requirement doesn't hold (i.e. additional mappings can go in a helper)
 

--

---
You received this message because you are subscribed to a topic in the Google Groups "Slick / ScalaQuery" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/scalaquery/EbPA2Iv8DT4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to scalaquery+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/scalaquery/360051f2-7572-4e9d-ae28-57c4f31710b7%40googlegroups.com.

[Naftoli Gugenheim]

On Tue, Jul 8, 2014 at 2:48 PM, Fayimora Femi-Balogun <fa...@fayimora.com> wrote:

Fixed this by moving the optional arguments to the end of the argument list. 

Alternatively you could use named arguments.

 

When i try to insert a Contest, I get a weird type mismatch error

val contests = TableQuery[Contests]

contests += Contest("new title", "damainboss")

[error]  found   : Int

[error]  required: List[models.Contest]

[error]       contests += Contest("new title", "damainboss")

[error]

Isn’t that the right way to insert data? The arrow was pointing at the “+=“. I’m not even sure where it got an Int from. :(

Probably the database return value.

 

 

To view this discussion on the web visit https://groups.google.com/d/msgid/scalaquery/9E4C62EA688C46399B28BD571EAFEB32%40fayimora.com.

[Naftoli Gugenheim]

On Mon, Jul 7, 2014 at 4:10 PM, Fayimora Femi-Balogun <fa...@fayimora.com> wrote:

I did it like this:

trait TimestampedTable { this: Table[_] =>

  def createdAt = column[Timestamp]("CREATED_AT", O.NotNull)

  def updatedAt = column[Timestamp]("UPDATED_AT", O.NotNull)

}

class Contests(tag: Tag) extends Table[Contest](tag, "CONTESTS") with TimestampedTable {

// other attrs and projection here

}

Do you mind explaining this part?: this: Table[_] =>

It's a self type. (http://iainmcgin.github.io/scala-ref-markdown/ScalaReference.html#templates)

It means that it must extends Table[something], but this trait doesn't know or care what the type parameter is. Also it doesn't actually extends Table; you still have to extend it when you extend this trait. Since you're not extending it here, you don't have to pass in the constructor parameter. The other option would be to do:

class TimestampedTable[A](tag: Tag, name: String) extends Table[A](tag, name) {

  def createdAt ...

}

The advantage would be that subclasses don't have to inherit Table explicitly:

class ExampleTable(tag: Tag) extends TimestampedTable[(Int, String, Timestamp, Timestamp)](tag, "EXAMPLE_TABLE") {

  def * = ...

}

But it has the major shortcoming that you lose multiple inheritance, since it's now a class not a trait.

 

To view this discussion on the web visit https://groups.google.com/d/msgid/scalaquery/bb0da187-1e57-4995-bdaa-5539e5284549%40googlegroups.com.

[Fayimora Femi-Balogun]

Thanks a lot for the explanation Naftoli. Scala just keeps surprising me. I wonder if I’ll ever fully understand the language.

-- 

---------------------

Fayimora Femi-Balogun

Computer Science Student

Sent with Sparrow

You received this message because you are subscribed to a topic in the Google Groups "Slick / ScalaQuery" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/scalaquery/EbPA2Iv8DT4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to scalaquery+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/scalaquery/CANpg8PCM23d3%3DTDnsYn0gg_UwJydZ0QnA5SNgU7TiZadiBeRKQ%40mail.gmail.com.

[Farid Zakaria]

With my group, we just created a trigger i POSTGRES to update the createdAt, updatedAt time