SQL interpolation should not force upper-case #166

Closed
Pyppe opened this Issue Jun 5, 2013 · 3 comments

Comments

Projects
None yet
2 participants
@Pyppe

Pyppe commented Jun 5, 2013

In H2 quoted names are case sensitive. However, it seems to SQL interpolation forces table and column names into upper-case. Given the following simple example:

package example

import java.sql.Timestamp

import scala.slick.session.Database
import scala.slick.driver.H2Driver.simple._
import scala.slick.jdbc.{GetResult, StaticQuery => Q}
import Q.interpolation

object SqlInterpolationExample extends App {

  object Foos extends Table[(String, Timestamp)]("foo") {
    def name = column[String]("name")
    def createTime = column[Timestamp]("create_time")

    override def * = name ~ createTime
  }

  Database.forURL("jdbc:h2:mem:test1", driver = "org.h2.Driver")
    .withSession { implicit s: Session =>
      Foos.ddl.create
      sql"select max(create_time) from foo".as[Option[Timestamp]].first
    }

}

an exception is thrown:

Exception in thread "main" org.h2.jdbc.JdbcSQLException: Table "FOO" not found; SQL statement:
select max(create_time) from foo [42102-171]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
    at org.h2.message.DbException.get(DbException.java:169)
    at org.h2.message.DbException.get(DbException.java:146)
    at org.h2.command.Parser.readTableOrView(Parser.java:4782)
    at org.h2.command.Parser.readTableFilter(Parser.java:1091)
    at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1697)
    at org.h2.command.Parser.parseSelectSimple(Parser.java:1804)
    at org.h2.command.Parser.parseSelectSub(Parser.java:1691)
    at org.h2.command.Parser.parseSelectUnion(Parser.java:1534)
    at org.h2.command.Parser.parseSelect(Parser.java:1522)
    at org.h2.command.Parser.parsePrepared(Parser.java:404)
    at org.h2.command.Parser.parse(Parser.java:278)
    at org.h2.command.Parser.parse(Parser.java:250)
    at org.h2.command.Parser.prepareCommand(Parser.java:217)
    at org.h2.engine.Session.prepareLocal(Session.java:417)
    at org.h2.engine.Session.prepareCommand(Session.java:366)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1116)
    at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:74)
    at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:629)
    at scala.slick.session.Session$class.prepareStatement(Session.scala:29)
    at scala.slick.session.BaseSession.prepareStatement(Session.scala:201)
    at scala.slick.jdbc.StatementInvoker.results(StatementInvoker.scala:29)
    at scala.slick.jdbc.StatementInvoker.elementsTo(StatementInvoker.scala:17)
    at scala.slick.jdbc.Invoker$class.foreach(Invoker.scala:90)
    at scala.slick.jdbc.StatementInvoker.foreach(StatementInvoker.scala:10)
    at scala.slick.jdbc.Invoker$class.firstOption(Invoker.scala:41)
    at scala.slick.jdbc.StatementInvoker.firstOption(StatementInvoker.scala:10)
    at scala.slick.jdbc.Invoker$class.first(Invoker.scala:50)
    at scala.slick.jdbc.StatementInvoker.first(StatementInvoker.scala:10)
    at scala.slick.jdbc.UnitInvoker$class.first(Invoker.scala:149)
    at scala.slick.jdbc.StaticQuery0.first(StaticQuery.scala:95)
    at example.SqlInterpolationExample$$anonfun$1.apply(SqlInterpolationExample.scala:22)
@szeiger

This comment has been minimized.

Show comment
Hide comment
@szeiger

szeiger Jun 5, 2013

Member

This is simply the kind of error message you get from most database systems. You are passing it a non-quoted table name foo (which is completely opaque to Slick - there is no rewriting going on) and the error message shows the equivalent quoted name "FOO" (because non-quoted names are treated as upper-case).

Member

szeiger commented Jun 5, 2013

This is simply the kind of error message you get from most database systems. You are passing it a non-quoted table name foo (which is completely opaque to Slick - there is no rewriting going on) and the error message shows the equivalent quoted name "FOO" (because non-quoted names are treated as upper-case).

@szeiger szeiger closed this Jun 5, 2013

@Pyppe

This comment has been minimized.

Show comment
Hide comment
@Pyppe

Pyppe Jun 5, 2013

Thanks for the quick response, and sorry for the falsy report.

FYI; you can configure H2 not to force upper-case http://www.h2database.com/javadoc/org/h2/constant/DbSettings.html#DATABASE_TO_UPPER:

Database.forURL("jdbc:h2:mem:test1;DATABASE_TO_UPPER=false", driver = "org.h2.Driver")

In addition, H2 would work out of the box, if Foos.ddl.create would not use quotes for table/column names... Would it make sense to make it configurable whether DDL-creation uses quotes?

Pyppe commented Jun 5, 2013

Thanks for the quick response, and sorry for the falsy report.

FYI; you can configure H2 not to force upper-case http://www.h2database.com/javadoc/org/h2/constant/DbSettings.html#DATABASE_TO_UPPER:

Database.forURL("jdbc:h2:mem:test1;DATABASE_TO_UPPER=false", driver = "org.h2.Driver")

In addition, H2 would work out of the box, if Foos.ddl.create would not use quotes for table/column names... Would it make sense to make it configurable whether DDL-creation uses quotes?

@szeiger

This comment has been minimized.

Show comment
Hide comment
@szeiger

szeiger Jun 5, 2013

Member

Generally, if you want your DB names to be upper-case, write them like that in Slick's table definitions. The only case where you might run into problems with this approach is interoperability between PostgreSQL (which does non-quoted names wrong, converting them to lowercase) and all other databases. You can always subclass the driver and override quoteIdentifier to get the desired behavior. Slick quotes all names to avoid collisions with SQL keywords and other syntax issues.

Member

szeiger commented Jun 5, 2013

Generally, if you want your DB names to be upper-case, write them like that in Slick's table definitions. The only case where you might run into problems with this approach is interoperability between PostgreSQL (which does non-quoted names wrong, converting them to lowercase) and all other databases. You can always subclass the driver and override quoteIdentifier to get the desired behavior. Slick quotes all names to avoid collisions with SQL keywords and other syntax issues.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment