Drop all tables using Slick Plain Sql queries

Question

I'm using Slick Plain Sql queries to drop all the tables in a MariaDb database.

Here's the code I'm using:

import dbConfig.profile.api._
val databaseName : String

import slick.jdbc.SetParameter
implicit val SetString = SetParameter[Vector[String]](
  (s, pp) => pp.setString(s(pp.pos))
)

def dropTables = {
  val tablesToDrop : DBIO[Vector[String]] = 
    sql"SELECT concat(table_name) FROM information_schema.tables WHERE table_schema = '#$databaseName';".as[String]   

  val dbio : DBIO[Vector[String]] = for { 
    table <- tablesToDrop
    _ <- sqlu"DROP TABLE IF EXISTS '$table';"
  } yield table

  val future = dbConfig.db.run(dbio)
  val r = Await.result(future.andThen { case _ => dbConfig.db.close },
  Duration.Inf)
}

It successfully gets a list of tables to delete, but then the errorException in thread "main" java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0). I suspect this due to the Setparameter code.

Any ideas?

Here's a version using maps (i'm guessing they should be flatMaps?):

val tablesToDrop : DBIO[Vector[String]] = sql"SELECT concat(table_name) FROM information_schema.tables WHERE table_schema = '#$databaseName';".as[String]
def dropTable(tableName: String) : DBIO[Vector[String]] = sql"DROP TABLE IF EXISTS '$tableName';".as[String]
val dt = tablesToDrop.map(dbio => dbio.map(dropTable))

This runs without error but doesn't delete the tables. Here's the log:

DEBUG slick.basic.BasicBackend.action - #1: StreamingResultAction [SELECT concat(table_name) FROM information_schema.tables WHERE table_schema = 'altairdb';]
DEBUG slick.jdbc.JdbcBackend.statement - Preparing statement: SELECT concat(table_name) FROM information_schema.tables WHERE table_schema = 'altairdb';
DEBUG slick.jdbc.JdbcBackend.benchmark - Execution of prepared statement took 7ms
DEBUG slick.jdbc.StatementInvoker.result - /--------------------\
DEBUG slick.jdbc.StatementInvoker.result - | 1                  |
DEBUG slick.jdbc.StatementInvoker.result - | concat(table_name) |
DEBUG slick.jdbc.StatementInvoker.result - |--------------------|
DEBUG slick.jdbc.StatementInvoker.result - | Org                |
DEBUG slick.jdbc.StatementInvoker.result - | OrgUser            |
DEBUG slick.jdbc.StatementInvoker.result - | PermissionList     |
DEBUG slick.jdbc.StatementInvoker.result - | PermissionType     |
DEBUG slick.jdbc.StatementInvoker.result - | User               |
DEBUG slick.jdbc.StatementInvoker.result - \--------------------/
DEBUG slick.jdbc.StatementInvoker.result - 1 more rows read (6 total)
DEBUG slick.basic.BasicBackend.action - #2: success Vector(slick.jdbc.SQLActionBuilder$$anon$1@3e4a6e4b, slick.jdbc.SQLActionBuilder$$anon$1@267dd7e5, slick.jdbc.SQLActionBuilder$$anon$1@6a736f6d, slick.jdbc.SQLActionBuilder$$anon$1@68834f9f, slick.jdbc.SQLActionBuilder$$anon$1@5d64cf2, slick.jdbc.SQLActionBuilder$$anon$1@4d5c7152)

Update 1

Based on James's advice I wrote the following:

val tablesToDrop : DBIO[Vector[String]] = sql"SELECT concat(table_name) FROM information_schema.tables WHERE table_schema = '#$databaseName';".as[String]

def dropTable(tableNames: Vector[String]) : DBIO[Vector[String]] =
      sql"DROP TABLE IF EXISTS #${tableNames.mkString(", ")};".as[String]

val dropTablesDbio : DBIO[Vector[String]] = for {
  tables <- tablesToDrop
  _  <- sqlu"SET FOREIGN_KEY_CHECKS = 0;"
  _ <- dropTable(tables)
  _ <- sqlu"SET FOREIGN_KEY_CHECKS = 1;"
} yield tables

It works as long as the table list is non-empty. The empty list case results in a sql syntax error. Is there an elegant way to to check for an empty table?

I was thinking that if I could send

DBIO.seq(sqlu"DROP TABLE IF EXISTS Table0;",..., sqlu"DROP TABLE IF EXISTS TableN;")

as sql then it would cover the empty table list case more elegantly.

Update 2

This version works for no table:

def dropTables = {
    val tablesToDrop: DBIO[Vector[String]] = sql"SELECT table_name FROM information_schema.tables WHERE table_schema = '#$databaseName';".as[String]

    def dropTables(tableNames: Vector[String]): DBIO[Int] =
      sqlu"DROP TABLE IF EXISTS #${tableNames.mkString(", ")};"

    def dropTable(name: String): DBIO[Int] = sqlu"DROP TABLE IF EXISTS #$name;"

    val dropTablesDbio: DBIO[Vector[String]] = {
      tablesToDrop.flatMap(tables => {
        if (tables.isEmpty)
          DBIO.successful[Vector[String]](Vector.empty)
        else {
          for {
            _ <- sqlu"SET FOREIGN_KEY_CHECKS = 0;"
            _ <- dropTables(tables)
            _ <- sqlu"SET FOREIGN_KEY_CHECKS = 1;"
          } yield tables
        }
      })
    }
    val future = dbConfig.db.run(dropTablesDbio.withPinnedSession)

    val r = Await.result(future, Duration.Inf)
    r.foreach(println(_))
  }

Gist to this and other versions

Cool! Thanks for sharing the final version so that others also can benefit from it!! — James

James James · Accepted Answer · 2018-07-09T07:24:10

DROP TABLE IF EXISTS expects a list of tables like below:

DROP TABLE IF EXISTS A, B, C

I don't think this query accepts a bind variable(a variable to be set in a prepared statement). Instead, what you want to do is use the table names as String literals, instead of bind variables.

You don't need the SetParameter implicit val. Try replacing the DELETE query like below:

DROP TABLE IF EXISTS #${table.mkString(",")};

Ref : http://slick.lightbend.com/doc/3.2.3/sql.html#splicing-literal-values

Also worth noting that you should disable foreign key checks before you delete tables and then re-enable it after deleting them. Refer : https://stackoverflow.com/a/4922312/2110188

Drop all tables using Slick Plain Sql queries

Update 1

Update 2

1 Answers