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