Repository: slick/slick (scala)

Issue:
Support H2 v2.x
The current blocker is this:

https://github.com/slick/slick/blob/8a553aec20dabbf1fb9f852f19696891c4bd9351/slick-testkit/src/codegen/resources/dbs/h2.sql#L13 fails with H2 v2 because https://github.com/slick/slick/blob/8a553aec20dabbf1fb9f852f19696891c4bd9351/slick-testkit/src/codegen/resources/dbs/h2.sql#L12 creates only a unique index and not a unique contraint (the latter implies the former but not vice versa, apparently) which it requires for the 2-column foreign key constraint.

That could be fixed by changing it to create a unique constraint:

```sql
alter table "d" add unique ("f1","f2");
```

However the tests don't just expect this `.sql` file to run. They expect to use codegen to create Scala code for it and then call the generated `.schema` method: https://github.com/slick/slick/blob/7409c2c48e8a3fce61ca547a6bf66c37172a798b/slick-testkit/src/test/scala/slick/test/codegen/GeneratedCodeTest.scala#L19

But Slick has no way to represent that constraint, and therefore codegen doesn't generate something whose `schema` method will generate DDL for it.

There are two ways to solve this that I know of:

1. Implement #3027. This way the test stays the same and continues working.
2. Change the tests. Essentially we would be saying, either use Slick to generate your DDL SQL from your Scala code (good for either first time, or to use with Atlas), or use Codegen to generate your Scala code from your DDL SQL, but don't expect to be able to round-trip them (a nice property to have, but I'm not sure when you would want to do that in the real world).

Thoughts?

Pull request description:
This PR contains the following updates:

| Package | Update | Change |
|---|---|---|
| [com.h2database:h2](https://h2database.com) ([source](https://redirect.github.com/h2database/h2database)) | major | `1.4.200` -> `2.3.232` |

---

### Configuration

📅 **Schedule**: Branch creation - At any time (no schedule defined), Automerge - At any time (no schedule defined).

🚦 **Automerge**: Disabled by config. Please merge this manually once you are satisfied.

♻ **Rebasing**: Whenever PR becomes conflicted, or you tick the rebase/retry checkbox.

🔕 **Ignore**: Close this PR and you won't be reminded about this update again.

---

 - [ ] <!-- rebase-check -->If you want to rebase/retry this PR, check this box

---

This PR was generated by [Mend Renovate](https://mend.io/renovate/). View the [repository job log](https://developer.mend.io/github/slick/slick).
<!--renovate-debug:eyJjcmVhdGVkSW5WZXIiOiIzMi4xOTUuNSIsInVwZGF0ZWRJblZlciI6IjM4Ljk3LjAiLCJ0YXJnZXRCcmFuY2giOiJtYWluIn0=-->

Patch:
diff --git a/project/Dependencies.scala b/project/Dependencies.scala
index c570dd208d..d563c611af 100644
--- a/project/Dependencies.scala
+++ b/project/Dependencies.scala
@@ -33,7 +33,7 @@ object Dependencies {
   val logback = "ch.qos.logback" % "logback-classic" % "1.5.18"
   val hikariCP = "com.zaxxer" % "HikariCP" % "6.3.0"
 
-  val h2 = "com.h2database" % "h2" % "1.4.200"
+  val h2 = "com.h2database" % "h2" % "2.3.232"
   val sqlServer = {
     val javaVersion = System.getProperty("java.version")
     val jreVersionToUse = if (javaVersion.startsWith("11") || javaVersion.startsWith("12")) {
diff --git a/slick/src/main/scala/slick/jdbc/H2Profile.scala b/slick/src/main/scala/slick/jdbc/H2Profile.scala
index e9acbdd706..6f50194753 100644
--- a/slick/src/main/scala/slick/jdbc/H2Profile.scala
+++ b/slick/src/main/scala/slick/jdbc/H2Profile.scala
@@ -64,8 +64,7 @@ trait H2Profile extends JdbcProfile with JdbcActionComponent.MultipleRowsPerStat
     }
 
     class H2ColumnBuilder(tableBuilder: TableBuilder, meta: MColumn) extends ColumnBuilder(tableBuilder, meta) {
-      override def length =
-        super.length.filter(_ != Int.MaxValue) // H2 sometimes show this value, but doesn't accept it back in the DBType
+      override def length = super.length.filter(_ < 1000000000) // H2's max length is equivalent to no limit
       override def default =
         rawDefault
           .map((_, tpe))
@@ -95,6 +94,10 @@ trait H2Profile extends JdbcProfile with JdbcActionComponent.MultipleRowsPerStat
   override def createUpsertBuilder(node: Insert): InsertBuilder = new H2UpsertBuilder(node)
   override def createColumnDDLBuilder(column: FieldSymbol, table: Table[?]): ColumnDDLBuilder =
     new H2ColumnDDLBuilder(column)
+  override def createTableDDLBuilder(table: Table[?]): TableDDLBuilder =
+    new H2TableDDLBuilder(table)
+  override def createSequenceDDLBuilder(seq: Sequence[?]): SequenceDDLBuilder =
+    new H2SequenceDDLBuilder(seq)
   override def createInsertActionExtensionMethods[T](compiled: CompiledInsert): InsertActionExtensionMethods[T] =
     new H2CountingInsertActionComposerImpl[T](compiled)
 
@@ -117,13 +120,6 @@ trait H2Profile extends JdbcProfile with JdbcActionComponent.MultipleRowsPerStat
       case RowNumber(_) => b"rownum"
       case _ => super.expr(n)
     }
-
-    override protected def buildFetchOffsetClause(fetch: Option[Node], offset: Option[Node]) = (fetch, offset) match {
-      case (Some(t), Some(d)) => b"\nlimit $t offset $d"
-      case (Some(t), None   ) => b"\nlimit $t"
-      case (None, Some(d)   ) => b"\nlimit -1 offset $d"
-      case _ =>
-    }
   }
 
   class H2ColumnDDLBuilder(column: FieldSymbol) extends ColumnDDLBuilder(column) {
@@ -136,6 +132,13 @@ trait H2Profile extends JdbcProfile with JdbcActionComponent.MultipleRowsPerStat
     }
   }
 
+  class H2TableDDLBuilder(table: Table[?]) extends TableDDLBuilder(table) with TableDDLBuilder.UniqueIndexAsConstraint
+
+  class H2SequenceDDLBuilder(seq: Sequence[?])
+    extends SequenceDDLBuilder.BuiltInSupport(seq)
+      with SequenceDDLBuilder.BuiltInSupport.IncrementBy
+      with SequenceDDLBuilder.BuiltInSupport.StartWith
+
   class H2JdbcTypes extends JdbcTypes {
     override val uuidJdbcType = new UUIDJdbcType {
       override def sqlTypeName(sym: Option[FieldSymbol]) = "UUID"


Test patch:
diff --git a/slick-testkit/src/codegen/resources/dbs/h2.sql b/slick-testkit/src/codegen/resources/dbs/h2.sql
index 5e4e264f9a..4d00c48e81 100644
--- a/slick-testkit/src/codegen/resources/dbs/h2.sql
+++ b/slick-testkit/src/codegen/resources/dbs/h2.sql
@@ -9,7 +9,7 @@ alter table "a" add constraint "b_fk" foreign key("k1") references "b"("f1") on
 
 create table "c" ("k1" INTEGER NOT NULL,"k2" INTEGER NOT NULL,"s" VARCHAR NOT NULL);
 create table "d" ("f1" INTEGER NOT NULL,"f2" INTEGER NOT NULL,"s" VARCHAR NOT NULL);
-create unique index "d_idx1" on "d" ("f1","f2");
+alter table "d" add constraint "d_unique" unique ("f1","f2");
 alter table "c" add constraint "d_fk" foreign key("k1","k2") references "d"("f1","f2") on update NO ACTION on delete CASCADE;
 
 create table "E" (
diff --git a/slick-testkit/src/main/scala/com/typesafe/slick/testkit/tests/AggregateTest.scala b/slick-testkit/src/main/scala/com/typesafe/slick/testkit/tests/AggregateTest.scala
index 7f836fef3f..6bec2ded2b 100644
--- a/slick-testkit/src/main/scala/com/typesafe/slick/testkit/tests/AggregateTest.scala
+++ b/slick-testkit/src/main/scala/com/typesafe/slick/testkit/tests/AggregateTest.scala
@@ -27,15 +27,7 @@ class AggregateTest extends AsyncTest[RelationalTestDB] {
 
   private def roundAvg(avgResult: BigDecimal) =
     tdb.confName match {
-      case "db2" |
-           "derbydisk" |
-           "derbymem" |
-           "h2disk" |
-           "h2mem" |
-           "h2rownum" |
-           "hsqldbdisk" |
-           "hsqldbmem" |
-           "sqlserver-sqljdbc" =>
+      case "db2" | "derbydisk" | "derbymem" | "hsqldbdisk" | "hsqldbmem" | "sqlserver-sqljdbc" =>
         avgResult.setScale(0, BigDecimal.RoundingMode.DOWN).toDouble
       case "mysql" =>
         avgResult.setScale(4, BigDecimal.RoundingMode.HALF_UP).toDouble
diff --git a/slick-testkit/src/main/scala/com/typesafe/slick/testkit/tests/ModelBuilderTest.scala b/slick-testkit/src/main/scala/com/typesafe/slick/testkit/tests/ModelBuilderTest.scala
index d22d809471..73823687be 100644
--- a/slick-testkit/src/main/scala/com/typesafe/slick/testkit/tests/ModelBuilderTest.scala
+++ b/slick-testkit/src/main/scala/com/typesafe/slick/testkit/tests/ModelBuilderTest.scala
@@ -182,9 +182,10 @@ class ModelBuilderTest extends AsyncTest[JdbcTestDB] {
         assert(
           Seq(
             "VARCHAR",
-            "VARCHAR2" // oracle
+            "VARCHAR2", // oracle
+            "CHARACTER VARYING" // H2 v2.0 SQL standard name
           ) contains tpe("some_string").toUpperCase,
-          tpe("title")
+          tpe("some_string")
         )
         assertEquals(
           (99,false),