This is an automated email from the ASF dual-hosted git repository.
fanningpj pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/pekko-persistence-jdbc.git
The following commit(s) were added to refs/heads/main by this push:
new 8da2124 add migration scripts that users can run themselves (#391)
8da2124 is described below
commit 8da212459954443976a8d5248cde344ff22aa98d
Author: PJ Fanning <[email protected]>
AuthorDate: Tue Dec 16 14:03:46 2025 +0100
add migration scripts that users can run themselves (#391)
* add migration scripts that users can run themselves
* Update sqlserver-nvarchar-migration.sql
* Update sqlserver-nvarchar-migration.sql
* fix name and add checks
* Update sqlserver-nvarchar-migration.sql
* Create MigrationScriptSpec.scala
* Update MigrationScriptSpec.scala
* scalafmt
* Update MigrationScriptSpec.scala
* sqlserver: you can't change the column type if it is part of the primary
key
* refactor
* remove oracle test
* add warning
* Adding mariadb execution and MySql.
* update comment
* Update
core/src/main/resources/schema/sqlserver/sqlserver-nvarchar-migration.sql
Co-authored-by: Copilot <[email protected]>
* add comment
* move migration scripts
* Update MigrationScriptSpec.scala
* scalafmt
* v1.2.0
---------
Co-authored-by: Samuele <[email protected]>
Co-authored-by: Copilot <[email protected]>
---
.../mariadb-durable-state-migration.sql | 30 +++++
.../mysql-durable-state-migration.sql | 34 ++++++
.../resources/schema/mysql/mysql-create-schema.sql | 3 +-
.../oracle-number-boolean-migration.sql | 11 ++
...acle-number-boolean-schema-legacy-migration.sql | 11 ++
.../sqlserver-nvarchar-migration.sql | 92 ++++++++++++++++
.../schema/sqlserver/sqlserver-create-schema.sql | 2 +-
.../jdbc/integration/MigrationScriptSpec.scala | 122 +++++++++++++++++++++
8 files changed, 303 insertions(+), 2 deletions(-)
diff --git
a/core/src/main/resources/schema/mariadb/migration-1.2.0/mariadb-durable-state-migration.sql
b/core/src/main/resources/schema/mariadb/migration-1.2.0/mariadb-durable-state-migration.sql
new file mode 100644
index 0000000..b33cbca
--- /dev/null
+++
b/core/src/main/resources/schema/mariadb/migration-1.2.0/mariadb-durable-state-migration.sql
@@ -0,0 +1,30 @@
+-- SPDX-License-Identifier: Apache-2.0
+
+-- see https://github.com/apache/pekko-persistence-jdbc/pull/365
+
+-- Script is provided as an example only and has only been partially tested.
+-- Please review and test thoroughly before using in production and
+-- ideally, in a test environment first.
+-- Always back up your database before running migration scripts.
+
+CREATE TABLE IF NOT EXISTS durable_state
+(
+ global_offset SERIAL,
+ persistence_id VARCHAR(255) NOT NULL,
+ revision BIGINT NOT NULL,
+ state_payload BLOB NOT NULL,
+ state_serial_id INTEGER NOT NULL,
+ state_serial_manifest VARCHAR(255),
+ tag VARCHAR(255),
+ state_timestamp BIGINT NOT NULL,
+ PRIMARY KEY (persistence_id)
+);
+CREATE INDEX IF NOT EXISTS state_tag_idx on durable_state (tag);
+CREATE INDEX IF NOT EXISTS state_global_offset_idx on durable_state
(global_offset);
+
+CREATE SEQUENCE IF NOT EXISTS durable_state_global_offset_seq
+ INCREMENT BY 1
+ MINVALUE 1
+ NO MAXVALUE
+ START 1
+ CACHE 1;
diff --git
a/core/src/main/resources/schema/mysql/migration-1.2.0/mysql-durable-state-migration.sql
b/core/src/main/resources/schema/mysql/migration-1.2.0/mysql-durable-state-migration.sql
new file mode 100644
index 0000000..cf03f7c
--- /dev/null
+++
b/core/src/main/resources/schema/mysql/migration-1.2.0/mysql-durable-state-migration.sql
@@ -0,0 +1,34 @@
+-- SPDX-License-Identifier: Apache-2.0
+
+-- see https://github.com/apache/pekko-persistence-jdbc/pull/366
+
+-- Script is provided as an example only and has only been partially tested.
+-- Please review and test thoroughly before using in production and
+-- ideally, in a test environment first.
+-- Always back up your database before running migration scripts.
+
+CREATE TABLE IF NOT EXISTS durable_state
+(
+ global_offset SERIAL,
+ persistence_id VARCHAR(255) NOT NULL,
+ revision BIGINT NOT NULL,
+ state_payload BLOB NOT NULL,
+ state_serial_id INTEGER NOT NULL,
+ state_serial_manifest VARCHAR(255),
+ tag VARCHAR(255),
+ state_timestamp BIGINT NOT NULL,
+ PRIMARY KEY (persistence_id)
+);
+
+-- older MySQL versions do not support `IF NOT EXISTS` for indexes
+-- feel free to modify this part if your MySQL version supports it
+CREATE INDEX state_tag_idx on durable_state (tag);
+CREATE INDEX state_global_offset_idx on durable_state (global_offset);
+
+CREATE TABLE IF NOT EXISTS durable_state_global_offset
+(
+ singleton TINYINT NOT NULL,
+ current_offset BIGINT UNSIGNED NOT NULL UNIQUE,
+ PRIMARY KEY (singleton)
+);
+INSERT INTO durable_state_global_offset (singleton, current_offset) VALUES (0,
0);
diff --git a/core/src/main/resources/schema/mysql/mysql-create-schema.sql
b/core/src/main/resources/schema/mysql/mysql-create-schema.sql
index 3e4d778..22da5ed 100644
--- a/core/src/main/resources/schema/mysql/mysql-create-schema.sql
+++ b/core/src/main/resources/schema/mysql/mysql-create-schema.sql
@@ -10,7 +10,8 @@ CREATE TABLE IF NOT EXISTS event_journal(
event_ser_id INTEGER NOT NULL,
event_ser_manifest TEXT NOT NULL,
meta_payload BLOB,
- meta_ser_id INTEGER,meta_ser_manifest TEXT,
+ meta_ser_id INTEGER,
+ meta_ser_manifest TEXT,
PRIMARY KEY(persistence_id,sequence_number)
);
diff --git
a/core/src/main/resources/schema/oracle/migration-1.2.0/oracle-number-boolean-migration.sql
b/core/src/main/resources/schema/oracle/migration-1.2.0/oracle-number-boolean-migration.sql
new file mode 100644
index 0000000..97b1113
--- /dev/null
+++
b/core/src/main/resources/schema/oracle/migration-1.2.0/oracle-number-boolean-migration.sql
@@ -0,0 +1,11 @@
+-- SPDX-License-Identifier: Apache-2.0
+
+-- see https://github.com/apache/pekko-persistence-jdbc/pull/323
+
+-- Script is provided as an example only and has only been partially tested.
+-- Please review and test thoroughly before using in production and
+-- ideally, in a test environment first.
+-- Always back up your database before running migration scripts.
+
+ALTER TABLE EVENT_JOURNAL MODIFY DELETED NUMBER(1) DEFAULT 0 NOT NULL check
(DELETED in (0, 1))
+/
diff --git
a/core/src/main/resources/schema/oracle/migration-1.2.0/oracle-number-boolean-schema-legacy-migration.sql
b/core/src/main/resources/schema/oracle/migration-1.2.0/oracle-number-boolean-schema-legacy-migration.sql
new file mode 100644
index 0000000..6d0c78d
--- /dev/null
+++
b/core/src/main/resources/schema/oracle/migration-1.2.0/oracle-number-boolean-schema-legacy-migration.sql
@@ -0,0 +1,11 @@
+-- SPDX-License-Identifier: Apache-2.0
+
+-- see https://github.com/apache/pekko-persistence-jdbc/pull/323
+
+-- Script is provided as an example only and has only been partially tested.
+-- Please review and test thoroughly before using in production and
+-- ideally, in a test environment first.
+-- Always back up your database before running migration scripts.
+
+ALTER TABLE "journal" MODIFY "deleted" NUMBER(1) check ("deleted" in (0,1))
NOT NULL
+/
diff --git
a/core/src/main/resources/schema/sqlserver/migration-1.2.0/sqlserver-nvarchar-migration.sql
b/core/src/main/resources/schema/sqlserver/migration-1.2.0/sqlserver-nvarchar-migration.sql
new file mode 100644
index 0000000..d21d5f2
--- /dev/null
+++
b/core/src/main/resources/schema/sqlserver/migration-1.2.0/sqlserver-nvarchar-migration.sql
@@ -0,0 +1,92 @@
+-- SPDX-License-Identifier: Apache-2.0
+
+-- see https://github.com/apache/pekko-persistence-jdbc/pull/382
+
+-- Drop primary key constraint on event_journal to allow altering column types
+
+-- Script is provided as an example only and has only been partially tested.
+-- Please review and test thoroughly before using in production and
+-- ideally, in a test environment first.
+-- Always back up your database before running migration scripts.
+
+CREATE PROCEDURE DropPrimaryKey
+ @TableName NVARCHAR(255)
+AS
+BEGIN
+ SET NOCOUNT ON;
+ DECLARE @PKName NVARCHAR(1024);
+
+ SELECT @PKName = name
+ FROM sys.key_constraints
+ WHERE parent_object_id = OBJECT_ID(@TableName)
+ AND type = 'PK';
+
+ IF @PKName IS NOT NULL
+ EXEC('ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + @PKName);
+END;
+
+EXEC DropPrimaryKey 'event_journal';
+
+ALTER TABLE event_journal ALTER COLUMN
+ "persistence_id" NVARCHAR(255) NOT NULL;
+
+ALTER TABLE event_journal
+ ADD CONSTRAINT PK_event_journal PRIMARY KEY ("persistence_id",
"sequence_number");
+
+ALTER TABLE event_journal ALTER COLUMN
+ "writer" NVARCHAR(255) NOT NULL;
+
+ALTER TABLE event_journal ALTER COLUMN
+ "adapter_manifest" NVARCHAR(MAX) NOT NULL;
+
+ALTER TABLE event_journal ALTER COLUMN
+ "event_ser_manifest" NVARCHAR(MAX) NOT NULL;
+
+ALTER TABLE event_journal ALTER COLUMN
+ "meta_ser_manifest" NVARCHAR(MAX);
+
+-- Drop primary key constraint on event_tag to allow altering column types
+
+EXEC DropPrimaryKey 'event_tag';
+
+ALTER TABLE "event_tag" ALTER COLUMN
+ "tag" NVARCHAR(255) NOT NULL;
+
+ALTER TABLE "event_tag"
+ ADD CONSTRAINT PK_event_tag PRIMARY KEY ("event_id", "tag");
+
+-- Drop primary key constraint on snapshot to allow altering column types
+
+EXEC DropPrimaryKey 'snapshot';
+
+ALTER TABLE "snapshot" ALTER COLUMN
+ "persistence_id" NVARCHAR(255) NOT NULL;
+
+ALTER TABLE "snapshot"
+ ADD CONSTRAINT PK_snapshot PRIMARY KEY ("persistence_id", "sequence_number");
+
+ALTER TABLE "snapshot" ALTER COLUMN
+ "snapshot_ser_manifest" NVARCHAR(255) NOT NULL;
+
+ALTER TABLE "snapshot" ALTER COLUMN
+ "meta_ser_manifest" NVARCHAR(255);
+
+-- Drop primary key constraint on durable_state to allow altering column types
+
+EXEC DropPrimaryKey 'durable_state';
+
+ALTER TABLE durable_state ALTER COLUMN
+ "persistence_id" NVARCHAR(255) NOT NULL;
+
+ALTER TABLE durable_state
+ ADD CONSTRAINT PK_durable_state PRIMARY KEY ("persistence_id");
+
+ALTER TABLE durable_state ALTER COLUMN
+ "state_serial_manifest" NVARCHAR(MAX);
+
+ALTER TABLE durable_state ALTER COLUMN
+ "tag" NVARCHAR(255);
+
+-- Drop the procedure as it's no longer needed
+
+DROP PROCEDURE DropPrimaryKey;
diff --git
a/core/src/main/resources/schema/sqlserver/sqlserver-create-schema.sql
b/core/src/main/resources/schema/sqlserver/sqlserver-create-schema.sql
index 3794727..1116e03 100644
--- a/core/src/main/resources/schema/sqlserver/sqlserver-create-schema.sql
+++ b/core/src/main/resources/schema/sqlserver/sqlserver-create-schema.sql
@@ -50,7 +50,7 @@ CREATE TABLE durable_state
"global_offset" BIGINT
CONSTRAINT [df_global_offset] DEFAULT
(NEXT VALUE FOR global_offset),
- "persistence_id" NVARCHAR(255) NOT NULL,
+ "persistence_id" NVARCHAR(255) NOT NULL,
"revision" NUMERIC(10, 0) NOT NULL,
"state_payload" VARBINARY(MAX) NOT NULL,
"state_serial_id" INTEGER NOT NULL,
diff --git
a/integration-test/src/test/scala/org/apache/pekko/persistence/jdbc/integration/MigrationScriptSpec.scala
b/integration-test/src/test/scala/org/apache/pekko/persistence/jdbc/integration/MigrationScriptSpec.scala
new file mode 100644
index 0000000..2687d28
--- /dev/null
+++
b/integration-test/src/test/scala/org/apache/pekko/persistence/jdbc/integration/MigrationScriptSpec.scala
@@ -0,0 +1,122 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.pekko.persistence.jdbc.integration
+
+import java.sql.Statement
+import com.typesafe.config.{ Config, ConfigFactory }
+import org.apache.pekko
+import pekko.Done
+import pekko.actor.ActorSystem
+import pekko.persistence.jdbc.state.scaladsl.StateSpecBase
+import pekko.persistence.jdbc.testkit.internal.{ SchemaType, SqlServer }
+import slick.jdbc.JdbcBackend.Database
+
+import scala.util.Using
+
+abstract class MigrationScriptSpec(config: Config, schemaType: SchemaType)
extends StateSpecBase(config, schemaType) {
+
+ implicit lazy val system: ActorSystem = ActorSystem("migration-test", config)
+
+ protected def withStatement(database: Database)(f: Statement => Unit): Done
= {
+ val session = database.createSession()
+ try session.withStatement()(f)
+ finally session.close()
+ Done
+ }
+
+ protected def applyScriptWithSlick(script: String, database: Database): Done
= {
+ withStatement(database) { stmt =>
+ stmt.executeUpdate(script)
+ }
+ }
+
+ protected def applyScriptWithSlick(script: String, separator: String,
database: Database): Done = {
+ withStatement(database) { stmt =>
+ val lines = script.split(separator).map(_.trim)
+ for {
+ line <- lines if line.nonEmpty
+ } yield {
+ stmt.executeUpdate(line)
+ }
+ }
+ }
+}
+
+class SqlServerMigrationScriptSpec extends MigrationScriptSpec(
+ ConfigFactory.load("sqlserver-application.conf"),
+ SqlServer
+ ) {
+ "SQL Server nvarchar migration script" must {
+ "apply without errors" in {
+ val scriptPath =
+
getClass.getResource("/schema/sqlserver/migration-1.2.0/sqlserver-nvarchar-migration.sql").getPath
+ val sql = Using(scala.io.Source.fromFile(scriptPath))(_.mkString).get
+ val parts = sql.split("(?<=END;)")
+
+ parts.length should be > 1
+
+ applyScriptWithSlick(parts.head, db)
+ parts.tail.foreach(part => applyScriptWithSlick(part, db))
+ }
+ }
+}
+
+class MariaDBMigrationScriptSpec extends MigrationScriptSpec(
+ ConfigFactory.load("mariadb-application.conf"),
+ SqlServer
+ ) {
+ "MariaDB migration script" must {
+ "apply the schema and the migration without errors" in {
+ val schemaPath =
getClass.getResource("/schema/mariadb/mariadb-create-schema.sql").getPath
+ val schema = Using(scala.io.Source.fromFile(schemaPath))(_.mkString).get
+ applyScriptWithSlick(schema, db)
+
+ val migrationPath =
+
getClass.getResource("/schema/mariadb/migration-1.2.0/mariadb-durable-state-migration.sql").getPath
+ val migration =
Using(scala.io.Source.fromFile(migrationPath))(_.mkString).get
+ applyScriptWithSlick(migration, db)
+ }
+ }
+}
+
+class MySQLMigrationScriptSpec extends MigrationScriptSpec(
+ ConfigFactory.load("mysql-application.conf"),
+ SqlServer
+ ) {
+ "MySQL migration script" must {
+ "apply the schema and the migration without errors" in {
+ val schemaPath =
getClass.getResource("/schema/mysql/mysql-create-schema-legacy.sql").getPath
+ val schema = Using(scala.io.Source.fromFile(schemaPath))(_.mkString).get
+
+ // Each statement executed as standalone
+ schema.split(";")
+ .map(_.trim)
+ .filter(_.nonEmpty)
+ .foreach(statement => applyScriptWithSlick(statement, db))
+
+ val migrationPath =
+
getClass.getResource("/schema/mysql/migration-1.2.0/mysql-durable-state-migration.sql").getPath
+ val migration =
Using(scala.io.Source.fromFile(migrationPath))(_.mkString).get
+
+ migration.split(";")
+ .map(_.trim)
+ .filter(_.nonEmpty)
+ .foreach(statement => applyScriptWithSlick(statement, db))
+ }
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
