Scalatra / Slick and insert IF NOT EXISTS

Question

I am a newbie so hoping for some patience. :)

I am trying to populate two tables if a value does not exist. Basically I have:

TABLE b
(
    id VARCHAR(254) PRIMARY KEY NOT NULL
);


TABLE d
(
    id VARCHAR(254) PRIMARY KEY NOT NULL,
    relay INT NOT NULL,
    FOREIGN KEY ( relay ) REFERENCES b ( id )
);

so I am trying to write a function that populates the two tables with a new value, if it doesn't exist, or ignores it otherwise... of course wrapped in a transaction:

IF (NOT EXISTS(SELECT * FROM b where id='something'))
    insert into b values('something')
    insert into d values(1, 'something')
END

What is the most efficient way of achieving something like this? If it matters I'm using POstgreSQL 9.1 but I'd like to keep it fairly generic.

(EDIT) These are my current table defs (simplified for illustration purposes):

object d extends Table[(String, String, Int)]("d")
{
  def id=column[String]("id", O.PrimaryKey)

  def relay=column[Int]("relay")
  def relay_ref=foreignKey("d2b.fk", relay, b)(_.id)
  def * = id ~ relay
}
object b extends Table[(String)]("b")
{
  def id=column[String]("id", O.PrimaryKey)
  def * = id
}

score 10 · Accepted Answer · answered Sep 18 '13 at 12:29

10

In Slick 1.0.1

db.withTransaction{ implicit session : Session =>
  if( ! Query(b).filter(_.id==="something").exists.run ){
    b.insert( "something" )
    d.insert( (1,"something") )
  }
}

In Slick 2.0

val b = TableQuery[b]
db.withTransaction{ implicit session =>
  if( ! b.filter(_.id==="something").exists.run ){
    b += "something"
    d += (1,"something")
  }
}

answered Sep 18 '13 at 12:29

cvogt

11,260
30
46

Thanks. Is the proper way to determine if the transaction failed by handling exceptions, or is there something built-in? – Will I Am Sep 18 '13 at 15:59
1

BTW, this has helped point me to the right direction (Slick 1.0.1), but I'm running into an issue: "ambiguous implicit values: both value session of type slick.driver.PostgresDriver.simple.Session and method threadLocalSession in object Database of type => scala.slick.session.Session match expected type scala.slick.session.Session if (!Query(b).filter(_.id === myval).exists.run)" – Will I Am Sep 18 '13 at 18:29
1

Regarding the ambigious implicits, just remove the threadLocalSession import. threadLocalSession should only be used in rare cases. – cvogt Sep 18 '13 at 20:41
Failing a transaction is a decision that you make yourself or that happens implicitly when the code in a withTransaction block does not terminate normally (by throwing an Exception or simply by not terminating at all). You will usually want to catch and handle exceptions somewhere outside of a withTransaction block. – szeiger Sep 18 '13 at 20:46
2

I am not sure, this is the optimal way of handling duplicate inserts. I have written an article about it recently. You should read it. http://i.am.michiel.fr/article/2013/08/07/mysql-insert-performance.html – i.am.michiel Sep 19 '13 at 09:53

Scalatra / Slick and insert IF NOT EXISTS

1 Answers1

Linked