How could I know if a database table is exists in ScalaQuery

I'm trying ScalaQuery, it is really amazing. I could defined the database table using Scala class, and query it easily.

But I would like to know, in the following code, how could I check if a table is exists, so I won't call 'Table.ddl.create' twice and get a exception when I run this program twice?

object Users extends Table[(Int, String, String)]("Users") {
    def id = column[Int]("id")
    def first = column[String]("first")
    def last = column[String]("last")
    def * = id ~ first ~ last
}

object Main
{
    val database = Database.forURL("jdbc:sqlite:sample.db", driver = "org.sqlite.JDBC")

    def main(args: Array[String]) {
        dat开发者_如何学运维abase withSession {
            // How could I know table Users is alrady in the DB?
            if ( ??? )  {
                Users.ddl.create
            }
        }
    }
}

ScalaQuery version 0.9.4 includes a number of helpful SQL metadata wrapper classes in the org.scalaquery.meta package, such as MTable:

http://scalaquery.org/doc/api/scalaquery-0.9.4/#org.scalaquery.meta.MTable

In the test code for ScalaQuery, we can see examples of these classes being used. In particular, see org.scalaquery.test.MetaTest.

I wrote this little function to give me a map of all the known tables, keyed by table name.

import org.scalaquery.meta.{MTable}
def makeTableMap(dbsess: Session) : Map[String, MTable] = {
    val tableList = MTable.getTables.list()(dbsess);
    val tableMap = tableList.map{t => (t.name.name, t)}.toMap;
    tableMap;
}

So now, before I create an SQL table, I can check "if (!tableMap.contains(tableName))".

This thread is a bit old, but maybe someone will find this useful. All my DAOs include this:

def create = db withSession {
    if (!MTable.getTables.list.exists(_.name.name == MyTable.tableName))
        MyTable.ddl.create
}

Here's a full solution that checks on application start using a PostGreSQL DB for PlayFramework

import globals.DBGlobal
import models.UsersTable
import org.scalaquery.meta.MTable
import org.scalaquery.session.Session

import play.api.GlobalSettings
import play.api.Application

object Global extends GlobalSettings {

    override def onStart(app: Application)  {

        DBGlobal.db.withSession { session : Session =>
            import org.scalaquery.session.Database.threadLocalSession
            import org.scalaquery.ql.extended.PostgresDriver.Implicit._
            if (!makeTableMap(session).contains("tableName")) {
                UsersTable.ddl.create(session)
            }
        }
    }

    def makeTableMap(dbsess: Session): Map[String, MTable] = {
        val tableList = MTable.getTables.list()(dbsess)
        val tableMap = tableList.map {
        t => (t.name.name, t)
    }.toMap
        tableMap
    }
}

With java.sql.DatabaseMetaData (Interface). Depending on your Database, more or less functions might be implemented.

See also the related discussion here.I personally prefer hezamu's suggestion and extend it as follows to keep it DRY:

def createIfNotExists(tables: TableQuery[_ <: Table[_]]*)(implicit session: Session) {
  tables foreach {table => if(MTable.getTables(table.baseTableRow.tableName).list.isEmpty) table.ddl.create}
}

Then you can just create your tables with the implicit session:

db withSession {
  implicit session =>
    createIfNotExists(table1, table2, ..., tablen)
}

You can define in your DAO impl the following method (taken from Slick MTable.getTables always fails with Unexpected exception[JdbcSQLException: Invalid value 7 for parameter columnIndex [90008-60]]) that gives you a true o false depending if there a defined table in your db:

    def checkTable() : Boolean = {
        val action = MTable.getTables
        val future = db.run(action)
        val retVal = future map {result =>
          result map {x => x}
        }

        val x = Await.result(retVal, Duration.Inf)

        if (x.length > 0) {
          true
        } else {
          false
        }
      }

Or, you can check if some "GIVENTABLENAME" or something exists with println method:

      def printTable() ={
          val q = db.run(MTable.getTables)
          println(Await.result(q, Duration.Inf).toList(0)) //prints first MTable element
          println(Await.result(q, Duration.Inf).toList(1))//prints second MTable element
          println(Await.result(q, Duration.Inf).toList.toString.contains("MTable(MQName(public.GIVENTABLENAME_pkey),INDEX,null,None,None,None)"))
      }

Don't forget to add

    import slick.jdbc.meta._

Then call the methods from anywhere with the usual @Inject(). Using play 2.4 and play-slick 1.0.0.

Cheers,