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

Question

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]) {
        database withSession {
            // How could I know table Users is alrady in the DB?
            if ( ??? )  {
                Users.ddl.create
            }
        }
    }
}

score 8 · Accepted Answer · answered Aug 29 '11 at 03:31

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))".

Thanks for this post! This is a great way to make an asynchronous bootstrap on the play! framework (http://www.playframework.org/documentation/1.2.4/jobs). Cheers, — egbutter, Jan 29 '12 at 16:36

hezamu · Answer 2 · 2013-04-08T14:50:25.077

7

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
}

edited Apr 08 '13 at 14:50

answered Apr 07 '13 at 11:39

hezamu

1,534
1
11
15

Thanks, this is great. Works with Slick 2.1.x – Gavin Sep 10 '14 at 18:46

Rasputin Jones · Answer 3 · 2012-07-12T22:27:31.960

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
    }
}

score 1 · Answer 4 · answered Mar 26 '11 at 11:52

1

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

answered Mar 26 '11 at 11:52

user unknown

35,537
11
75
121

So I guess there is no ScalaQuery way could do this? – Brian Hsu Mar 27 '11 at 03:30
I don't find anything, and from what I found (`User extends Table [(tuple of types)]`), I guess, the goal is to use early typing. I can't see how you could smoothly integrate dynamic defined tables yourself, but I didn't use reflection often, so maybe there is a way, and I just can't see it. The documentation is a little bit lean. There are some `meta`-classes, but I didn't find examples how to use them in the wiki or the blog. :) – user unknown Mar 27 '11 at 11:24
I don't find anything either. So I guess this is the answer. And just a note, Session#metaData could get the java.sq.DatabaseMetaData of current database session. – Brian Hsu Mar 28 '11 at 02:07

score 0 · Answer 5 · edited May 23 '17 at 11:44

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)
}

score 0 · Answer 6 · edited May 23 '17 at 11:51

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,

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

6 Answers6

Linked