Add Oracle support in the trigger service (#9286)

* Add Oracle support in the trigger service This PR migrates the ddl & queries and adds tests for this. It does not yet expose this to users. I’ll handle that in a separate PR. changelog_begin changelog_end * use getOrElse changelog_begin changelog_end
digital-asset · Mar 31, 2021 · f7b2f14 · f7b2f14
1 parent 2625953
commit f7b2f14
Show file tree

Hide file tree

Showing 26 changed files with 265 additions and 94 deletions.
diff --git a/BUILD b/BUILD
@@ -8,6 +8,7 @@ load(
     "@rules_haskell//haskell:c2hs.bzl",
     "c2hs_toolchain",
 )
+load("@bazel_skylib//rules:common_settings.bzl", "string_flag")
 load("//bazel_tools:haskell.bzl", "da_haskell_library", "da_haskell_repl")
 load("//bazel_tools:scala.bzl", "da_scala_library")
 load("@os_info//:os_info.bzl", "is_windows")
@@ -270,3 +271,22 @@ da_haskell_repl(
         "//language-support/ts/codegen:daml2js",
     ],
 )
+
+string_flag(
+    name = "oracle_testing",
+    build_setting_default = "no",
+)
+
+config_setting(
+    name = "oracle_available",
+    flag_values = {
+        ":oracle_testing": "yes",
+    },
+)
+
+config_setting(
+    name = "oracle_unavailable",
+    flag_values = {
+        ":oracle_testing": "no",
+    },
+)
diff --git a/ci/build.yml b/ci/build.yml
@@ -238,9 +238,10 @@ jobs:
           sleep 1
         done
         # Actually run some tests
-        bazel test --//ledger-service/http-json-oracle:oracle_testing=yes \
+        bazel test --//:oracle_testing=yes \
           --test_env ORACLE_USERNAME=system --test_env ORACLE_PORT --test_env ORACLE_PWD \
-          //ledger-service/http-json-oracle/...
+          //ledger-service/http-json-oracle/... \
+          //triggers/service:test-oracle
       env:
         DOCKER_LOGIN: $(DOCKER_LOGIN)
         DOCKER_PASSWORD: $(DOCKER_PASSWORD)

diff --git a/ledger-service/http-json-oracle/BUILD.bazel b/ledger-service/http-json-oracle/BUILD.bazel
@@ -8,32 +8,12 @@ load(
     "da_scala_test",
     "lf_scalacopts",
 )
-load("@bazel_skylib//rules:common_settings.bzl", "string_flag")
 load("@scala_version//:index.bzl", "scala_version_suffix")
 
 hj_scalacopts = lf_scalacopts + [
     "-P:wartremover:traverser:org.wartremover.warts.NonUnitStatements",
 ]
 
-string_flag(
-    name = "oracle_testing",
-    build_setting_default = "no",
-)
-
-config_setting(
-    name = "oracle_available",
-    flag_values = {
-        ":oracle_testing": "yes",
-    },
-)
-
-config_setting(
-    name = "oracle_unavailable",
-    flag_values = {
-        ":oracle_testing": "no",
-    },
-)
-
 da_scala_test(
     name = "integration-tests",
     size = "large",
@@ -58,7 +38,7 @@ da_scala_test(
     ],
     scalacopts = hj_scalacopts,
     target_compatible_with = select({
-        ":oracle_available": [],
+        "//:oracle_available": [],
         "//conditions:default": ["@platforms//:incompatible"],
     }),
     runtime_deps = [

diff --git a/ledger-service/http-json-oracle/src/it/scala/http/HttpServiceOracleInt.scala b/ledger-service/http-json-oracle/src/it/scala/http/HttpServiceOracleInt.scala
@@ -16,7 +16,7 @@ trait HttpServiceOracleInt extends AbstractHttpServiceIntegrationTestFuns with O
 
   protected[this] lazy val jdbcConfig_ = JdbcConfig(
     driver = "oracle.jdbc.OracleDriver",
-    url = s"jdbc:oracle:thin:@localhost:$oraclePort/ORCLPDB1",
+    url = oracleJdbcUrl,
     user = oracleUser,
     password = oraclePwd,
     createSchema = true,

diff --git a/libs-scala/oracle-testing/src/main/scala/testing/oracle/OracleAround.scala b/libs-scala/oracle-testing/src/main/scala/testing/oracle/OracleAround.scala
@@ -19,6 +19,8 @@ trait OracleAround {
 
   def oraclePort: Port = port
 
+  def oracleJdbcUrl: String = s"jdbc:oracle:thin:@localhost:$oraclePort/ORCLPDB1"
+
   protected def connectToOracle(): Unit = {
     systemUser = sys.env("ORACLE_USERNAME")
     systemPwd = sys.env("ORACLE_PWD")
@@ -29,7 +31,7 @@ trait OracleAround {
     // See https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements008.htm#i27570
     // for name restrictions.
     val u = "u" + Random.alphanumeric.take(29).mkString("")
-    createNewUser(u)
+    createNewUser(u.toUpperCase)
   }
 
   protected def createNewUser(name: String): User = {

diff --git a/triggers/service/BUILD.bazel b/triggers/service/BUILD.bazel
@@ -133,6 +133,7 @@ da_scala_library(
         "//ledger/sandbox-classic",
         "//ledger/sandbox-common",
         "//libs-scala/adjustable-clock",
+        "//libs-scala/oracle-testing",
         "//libs-scala/ports",
         "//libs-scala/postgresql-testing",
         "//libs-scala/resources",
@@ -147,40 +148,62 @@ da_scala_library(
     ],
 )
 
+test_deps = [
+    ":trigger-service",
+    ":trigger-service-tests",
+    "//daml-lf/archive:daml_lf_archive_reader",
+    "//daml-lf/archive:daml_lf_dev_archive_proto_java",
+    "//daml-lf/data",
+    "//language-support/scala/bindings-akka",
+    "//ledger-api/rs-grpc-bridge",
+    "//ledger-api/testing-utils",
+    "//ledger-service/cli-opts",
+    "//ledger-service/jwt",
+    "//ledger/ledger-api-auth",
+    "//ledger/ledger-api-common",
+    "//ledger/ledger-resources",
+    "//ledger/sandbox-classic",
+    "//ledger/sandbox-common",
+    "//libs-scala/adjustable-clock",
+    "//libs-scala/flyway-testing",
+    "//libs-scala/oracle-testing",
+    "//libs-scala/ports",
+    "//libs-scala/postgresql-testing",
+    "//libs-scala/resources",
+    "//triggers/service/auth:oauth2-test-server",
+    "@maven//:eu_rekawek_toxiproxy_toxiproxy_java_2_1_3",
+    "@maven//:org_flywaydb_flyway_core",
+]
+
+scala_test_deps = [
+    "@maven//:com_typesafe_akka_akka_http_core",
+    "@maven//:io_spray_spray_json",
+    "@maven//:org_scalatest_scalatest",
+    "@maven//:org_scalaz_scalaz_core",
+]
+
 da_scala_test_suite(
     name = "test",
-    srcs = glob(["src/test-suite/scala/**/*.scala"]),
-    scala_deps = [
-        "@maven//:com_typesafe_akka_akka_http_core",
-        "@maven//:io_spray_spray_json",
-        "@maven//:org_scalatest_scalatest",
-        "@maven//:org_scalaz_scalaz_core",
-    ],
-    deps = [
-        ":trigger-service",
-        ":trigger-service-tests",
-        "//daml-lf/archive:daml_lf_archive_reader",
-        "//daml-lf/archive:daml_lf_dev_archive_proto_java",
-        "//daml-lf/data",
-        "//language-support/scala/bindings-akka",
-        "//ledger-api/rs-grpc-bridge",
-        "//ledger-api/testing-utils",
-        "//ledger-service/cli-opts",
-        "//ledger-service/jwt",
-        "//ledger/ledger-api-auth",
-        "//ledger/ledger-api-common",
-        "//ledger/ledger-resources",
-        "//ledger/sandbox-classic",
-        "//ledger/sandbox-common",
-        "//libs-scala/adjustable-clock",
-        "//libs-scala/flyway-testing",
-        "//libs-scala/ports",
-        "//libs-scala/postgresql-testing",
-        "//libs-scala/resources",
-        "//triggers/service/auth:oauth2-test-server",
-        "@maven//:eu_rekawek_toxiproxy_toxiproxy_java_2_1_3",
-        "@maven//:org_flywaydb_flyway_core",
+    srcs = glob(
+        ["src/test-suite/scala/**/*.scala"],
+        exclude = ["**/*Oracle*"],
+    ),
+    scala_deps = scala_test_deps,
+    deps = test_deps,
+)
+
+da_scala_test_suite(
+    name = "test-oracle",
+    srcs = glob(["src/test-suite/scala/**/*Oracle*.scala"]),
+    scala_deps = scala_test_deps,
+    target_compatible_with = select({
+        "//:oracle_available": [],
+        "//conditions:default": ["@platforms//:incompatible"],
+    }),
+    runtime_deps = [
+        "@maven//:com_oracle_database_jdbc_ojdbc8",
     ],
+    deps = test_deps,
 )
 
 genrule(

diff --git a/...rvice/src/main/resources/com/daml/lf/engine/trigger/db/migration/oracle/NO_AUTO_COPYRIGHT b/...rvice/src/main/resources/com/daml/lf/engine/trigger/db/migration/oracle/NO_AUTO_COPYRIGHT
diff --git a/...rs/service/src/main/resources/com/daml/lf/engine/trigger/db/migration/oracle/V1__Init.sql b/...rs/service/src/main/resources/com/daml/lf/engine/trigger/db/migration/oracle/V1__Init.sql
@@ -0,0 +1,22 @@
+-- Copyright (c) 2020 Digital Asset (Switzerland) GmbH and/or its affiliates. All rights reserved.
+-- SPDX-License-Identifier: Apache-2.0
+
+-- Running trigger table.
+create table running_triggers(
+  -- a UUID generated by the service
+  trigger_instance nvarchar2(36) primary key,
+  -- the token corresponding to the party
+  trigger_party nvarchar2(255) not null,
+  -- the identifier for the trigger in its dalf,
+  --  of the form "packageId:moduleName:triggerName"
+  full_trigger_name nvarchar2(1000) not null
+);
+
+-- Dalf table with binary package data.
+create table dalfs(
+  package_id nvarchar2(64) primary key,
+  package blob not null
+);
+
+-- Index for efficiently listing running triggers for a particular party.
+create index triggers_by_party on running_triggers(trigger_party);
diff --git a/...ice/src/main/resources/com/daml/lf/engine/trigger/db/migration/oracle/V1__Init.sql.sha256 b/...ice/src/main/resources/com/daml/lf/engine/trigger/db/migration/oracle/V1__Init.sql.sha256
@@ -0,0 +1 @@
+6dc2d7d667ab3052662754853f3aeaaa8323ea6f14184312eef5c3ca56a90f1c
diff --git a/...rc/main/resources/com/daml/lf/engine/trigger/db/migration/oracle/V2__Add_access_token.sql b/...rc/main/resources/com/daml/lf/engine/trigger/db/migration/oracle/V2__Add_access_token.sql
@@ -0,0 +1,5 @@
+-- Copyright (c) 2020 Digital Asset (Switzerland) GmbH and/or its affiliates. All rights reserved.
+-- SPDX-License-Identifier: Apache-2.0
+
+-- Add access token to running trigger table
+alter table running_triggers add (access_token nvarchar2(2000));
diff --git a/.../resources/com/daml/lf/engine/trigger/db/migration/oracle/V2__Add_access_token.sql.sha256 b/.../resources/com/daml/lf/engine/trigger/db/migration/oracle/V2__Add_access_token.sql.sha256
@@ -0,0 +1 @@
+dd554429e2248ed59748bda95664e2e06edee52fdfec13a5c1c9427b78d6f897
diff --git a/.../main/resources/com/daml/lf/engine/trigger/db/migration/oracle/V3__Add_application_id.sql b/.../main/resources/com/daml/lf/engine/trigger/db/migration/oracle/V3__Add_application_id.sql
@@ -0,0 +1,7 @@
+-- Copyright (c) 2020 Digital Asset (Switzerland) GmbH and/or its affiliates. All rights reserved.
+-- SPDX-License-Identifier: Apache-2.0
+
+-- Add application_id to running trigger table defaulting to trigger_instance
+alter table running_triggers add (application_id nvarchar2(1000));
+update running_triggers set application_id = trigger_instance;
+alter table running_triggers modify (application_id not null);
diff --git a/...esources/com/daml/lf/engine/trigger/db/migration/oracle/V3__Add_application_id.sql.sha256 b/...esources/com/daml/lf/engine/trigger/db/migration/oracle/V3__Add_application_id.sql.sha256
@@ -0,0 +1 @@
+00569de5aaa9b0c6f6f47b97267e1385faa6c602102521ee7893ab23830a2112
diff --git a/...c/main/resources/com/daml/lf/engine/trigger/db/migration/oracle/V4__Add_refresh_token.sql b/...c/main/resources/com/daml/lf/engine/trigger/db/migration/oracle/V4__Add_refresh_token.sql
@@ -0,0 +1,5 @@
+-- Copyright (c) 2020 Digital Asset (Switzerland) GmbH and/or its affiliates. All rights reserved.
+-- SPDX-License-Identifier: Apache-2.0
+
+-- Add refresh token to running trigger table
+alter table running_triggers add (refresh_token nvarchar2(2000));
diff --git a/...resources/com/daml/lf/engine/trigger/db/migration/oracle/V4__Add_refresh_token.sql.sha256 b/...resources/com/daml/lf/engine/trigger/db/migration/oracle/V4__Add_refresh_token.sql.sha256
@@ -0,0 +1 @@
+f1199f74901fd2af98bc0a64ce39e754389a2c6364fc0bfb6e6ee3f0732c0e49
diff --git a/triggers/service/src/main/scala/com/digitalasset/daml/lf/engine/trigger/ServiceConfig.scala b/triggers/service/src/main/scala/com/digitalasset/daml/lf/engine/trigger/ServiceConfig.scala
@@ -41,6 +41,7 @@ private[trigger] final case class ServiceConfig(
 )
 
 final case class JdbcConfig(
+    driver: String,
     url: String,
     user: String,
     password: String,
@@ -56,6 +57,7 @@ object JdbcConfig {
       user <- requiredField(x)("user")
       password <- requiredField(x)("password")
     } yield JdbcConfig(
+      driver = "org.postgresql.Driver", // TODO make this configurable
       url = url,
       user = user,
       password = password,

diff --git a/...rvice/src/main/scala/com/digitalasset/daml/lf/engine/trigger/dao/DbFlywayMigrations.scala b/...rvice/src/main/scala/com/digitalasset/daml/lf/engine/trigger/dao/DbFlywayMigrations.scala
@@ -12,12 +12,15 @@ import org.flywaydb.core.api.configuration.FluentConfiguration
 
 import javax.sql.DataSource
 
-private[trigger] class DbFlywayMigrations(private val ds: DataSource) extends StrictLogging {
+private[trigger] class DbFlywayMigrations(private val ds: DataSource, migrationsDir: String)
+    extends StrictLogging {
   import DbFlywayMigrations._
 
-  def migrate(allowExistingSchema: Boolean = false): ConnectionIO[Unit] =
+  var flyway: Flyway = _
+
+  def migrate(allowExistingSchema: Boolean = false): ConnectionIO[Unit] = {
     doobie.free.connection.delay {
-      val flyway = configurationBase()
+      flyway = configurationBase(migrationsDir)
         .dataSource(ds)
         .baselineOnMigrate(allowExistingSchema)
         .baselineVersion(MigrationVersion.fromVersion("0"))
@@ -26,13 +29,21 @@ private[trigger] class DbFlywayMigrations(private val ds: DataSource) extends St
       val stepsTaken = flyway.migrate()
       logger.info(s"Flyway schema migration finished successfully, applying $stepsTaken steps.")
     }
+  }
+
+  def clean(): ConnectionIO[Unit] =
+    doobie.free.connection.delay {
+      logger.info("Running Flyway clean...")
+      val stepsTaken = flyway.clean()
+      logger.info(s"Flyway clean finished successfully, applying $stepsTaken steps.")
+    }
 }
 
 private[trigger] object DbFlywayMigrations {
-  def configurationBase(): FluentConfiguration =
+  def configurationBase(migrationsDir: String): FluentConfiguration =
     Flyway
       .configure()
       .locations(
-        "classpath:com/daml/lf/engine/trigger/db/migration/postgres"
+        s"classpath:com/daml/lf/engine/trigger/db/migration/$migrationsDir"
       )
 }
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1 @@
		6dc2d7d667ab3052662754853f3aeaaa8323ea6f14184312eef5c3ca56a90f1c
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1 @@
		dd554429e2248ed59748bda95664e2e06edee52fdfec13a5c1c9427b78d6f897
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1 @@
		00569de5aaa9b0c6f6f47b97267e1385faa6c602102521ee7893ab23830a2112
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1 @@
		f1199f74901fd2af98bc0a64ce39e754389a2c6364fc0bfb6e6ee3f0732c0e49