How to configure Play! Framework to work with ScalaQuery and H2?

Question

I already have some simple project with one migration script:

# --- !Ups

create table user (
  name        varchar(255) not null primary key,
  password    varchar(255) not null
);

insert into user values ('demo', 'demo');
insert into user values ('kuki', 'pass');


# --- !Downs

drop table if exists user;

Database which I'm using is H2 in memory:

db.default.driver=org.h2.Driver
db.default.url=jdbc:h2:mem:play

Then I obviously want to query some data. When I'm using anorm everything is working properly:

case class User(name: String, password: String)
object User {

  val simple = {
    get[String]("user.name") ~/
    get[String]("user.password") ^^ {
      case name~password => User(name, password)
    }
  }

  def findByName(name: String): Option[User] = {
    DB.withConnection { implicit connection =>
     SQL("select * from user where name = {name}").on(
        'name -> name
      ).as(User.simple ?)
    }
  }
}

unlucky when I try to do the same with ScalaQuery:

object User extends Table[(String, String)]("user") {

  lazy val database = Database.forDataSource(DB.getDataSource())

  def name = column[String]("name", O PrimaryKey, O NotNull)
  def password = column[String]("password", O NotNull)
  def * = name ~ password

  def findByName(name: String) = database withSession {
    implicit db: Session =>
      (for (u <- this if u.name === name) yield u.name ~ u.password).list
  }
}

I always get the same error:

[JdbcSQLException: Tablela "user" nie istnieje Table "user" not found; 
SQL statement: SELECT "t1"."name","t1"."password" FROM "user" "t1" WHERE ("t1"."name"='input_name') [42102-158]]

Is there anything which I'm doing wrong? I think I strictly follow guide from there: https://github.com/playframework/Play20/wiki/ScalaDatabase

--------------------- EDIT -----------------------

Looks like it's some kind of incompatibility between Play's evolutions and ScalaQuery. When I created table using:

database withSession {
  implicit db: Session =>
    User.ddl.create
    User.insert("demo", "demo")
}

everything seems to work fine. Maybe later I'll create some simple MySQL database and check what really happens inside.

--------------------- EDIT 2 -----------------------

So I more or less know what is going on (but I don't know why). When I'm creating db structure with evolutions then table name and column names are written down with all uppercase letters.
And since I'm on linux then it matters. If I would change table and columns names in the code to be uppercase also then everything works.

I'm only curious if it's a bug or if it's any way to enforce proper case on migrations?

Answer 1

Most likely, the problem is that the Play! Framework quotes the identifier names (table names, column names) in the query, so that you need to quote the table name in the 'create table' statement as well:

create table "user" (
  "name"        varchar(255) not null primary key,
  "password"    varchar(255) not null
);