CODEWITHSUNDEEP
mysqlscalaslick-3.0
LuGo
LuGo

Reputation: 5045

Conditional drop of tables in slick

Any idea how to do a conditional drop in Slick 3.0, to prevent An exception or error caused a run to abort: Unknown table 'MY_TABLE' if for some reason it doesn't exist?

def clear = {
    val operations = DBIO.seq(
      myTable.schema.drop,
      // other table definitions
      ...
    )
    db.run(operations)
  }

Upvotes: 0

Views: 1982

Answers (4)

sam
sam

Reputation: 143

I am currently using the Slick framework in its 3.2.0 version. The solution I am giving may apply to earlier version of the framework however but I did not verify this point.

If the only problem is to drop the table if it exists without throwing exception you can use the combinators of the Actions for this.

I have a series of test for which I run the create/populate/drop statement for each test on H2 in memory database. I suppose you have two tables Canal and SubCanal (SubCanal has a foreign key on Canal so that you would like to drop it first if it exists) for wich you already have declared TableQuery variables such as:

lazy val canals = TableQuery[CanalTable]
lazy val subcanals = TableQuery[SubCanalTable]

// we don't put SubCanals to check if no exeption is produced and then 
// add it for further testing.
lazy val ddl = canals.schema // ++ subcanals.schema

...I provided helper methods as follows:

def create: DBIO[Unit] = ddl.create
def drop: DBIO[Unit] = ddl.drop

def popCanal = canals ++= Seq(
    Canal("Chat"),
    Canal("Web"),
    Canal("Mail"))

The above is just creating the action but what is cool is that Slick will attempt to drop the SubCanal table and the Canal table but will encapsulate the exception in the Try[...]. So this will run smoothly:

val db = Database.forConfig("yourBaseConfig")
val res = db.run(drop)

And this will run also:

val db = Database.forConfig("yourBaseConfig")
val res1 = db.run(
  create >>
    popCanal >>
    canals.result        
)

.... some interesting computation ...

val res2 = db.run(drop)

Note: The SubCanal scheme is still commented so has never been performed for the moment and the drop is however applied and fail to this table but does not raise the exeption.

More on combining actions (combinator):

Upvotes: 0

user404345
user404345

Reputation:

I did this:

val personQuery = TableQuery[PersonTable]
val addressQuery = TableQuery[AddressTable]
...
val setupAction = DBIO.seq(
  sqlu"SET FOREIGN_KEY_CHECKS = 0",
  sqlu"DROP TABLE IF EXISTS #${personQuery.baseTableRow.tableName}",
  sqlu"DROP TABLE IF EXISTS #${addressQuery.baseTableRow.tableName}",
  sqlu"SET FOREIGN_KEY_CHECKS = 1",
)
val setupFuture = db.run(setupAction)

Note how you need to use #${} not ${} otherwise slick will fire off something like:

DROP TABLE IF EXISTS 'PERSON'

Which won't work

Upvotes: 0

Ed Staub
Ed Staub

Reputation: 15690

I went down the MTable route, but at least in Postgres it's a big hassle.

Try 

def qDropSchema = sqlu"""drop table if exists your-table-name;""";

Watch out for case-sensitivity issues with the table name. I ran into odd problems with the postgres there - don't know about mysql.

Upvotes: 2

Bla...
Bla...

Reputation: 7288

Let me try to answer your question, I think you can first check the availability of the table using MTable then drop it if it exists. More or less like below:

import scala.slick.jdbc.meta._
if (MTable.getTables("table_name").list().isEmpty) {
    //do something here..
}

Upvotes: 0

Related Questions