This is an automated email from the ASF dual-hosted git repository.
yuqi4733 pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/gravitino.git
The following commit(s) were added to refs/heads/main by this push:
new 20b9344c8 [#4022] improvement(core): Version manange for H2 database
(#4054)
20b9344c8 is described below
commit 20b9344c8b1e007cae5b3c9a7447bb72157c2e0f
Author: lwyang <[email protected]>
AuthorDate: Wed Jul 17 16:20:38 2024 +0800
[#4022] improvement(core): Version manange for H2 database (#4054)
### What changes were proposed in this pull request?
Version manange for H2 database like MySQL
### Why are the changes needed?
(Please clarify why the changes are needed. For instance,
Fix: #4022
### Does this PR introduce _any_ user-facing change?
No
### How was this patch tested?
ut
---
.../storage/relational/database/H2Database.java | 2 +-
.../storage/relational/TestJDBCBackend.java | 2 +-
.../src/test/resources}/h2/schema-h2.sql | 5 +-
docs/how-to-upgrade.md | 111 +++++++++++++++++++++
docs/index.md | 1 +
scripts/h2/{schema-h2.sql => schema-0.6.0-h2.sql} | 7 +-
6 files changed, 121 insertions(+), 7 deletions(-)
diff --git
a/core/src/main/java/org/apache/gravitino/storage/relational/database/H2Database.java
b/core/src/main/java/org/apache/gravitino/storage/relational/database/H2Database.java
index bd3c04436..4214a9bae 100644
---
a/core/src/main/java/org/apache/gravitino/storage/relational/database/H2Database.java
+++
b/core/src/main/java/org/apache/gravitino/storage/relational/database/H2Database.java
@@ -58,7 +58,7 @@ public class H2Database implements JDBCDatabase {
Statement statement = connection.createStatement()) {
String sqlContent =
FileUtils.readFileToString(
- new File(gravitinoHome + "/scripts/h2/schema-h2.sql"),
StandardCharsets.UTF_8);
+ new File(gravitinoHome + "/scripts/h2/schema-0.6.0-h2.sql"),
StandardCharsets.UTF_8);
statement.execute(sqlContent);
} catch (Exception e) {
diff --git
a/core/src/test/java/org/apache/gravitino/storage/relational/TestJDBCBackend.java
b/core/src/test/java/org/apache/gravitino/storage/relational/TestJDBCBackend.java
index 205b56ba3..da3246255 100644
---
a/core/src/test/java/org/apache/gravitino/storage/relational/TestJDBCBackend.java
+++
b/core/src/test/java/org/apache/gravitino/storage/relational/TestJDBCBackend.java
@@ -148,7 +148,7 @@ public class TestJDBCBackend {
private static void prepareJdbcTable() {
// Read the ddl sql to create table
- String scriptPath = "h2/schema-h2.sql";
+ String scriptPath = "h2/schema-0.6.0-h2.sql";
try (SqlSession sqlSession =
SqlSessionFactoryHelper.getInstance().getSqlSessionFactory().openSession(true);
Connection connection = sqlSession.getConnection();
diff --git a/scripts/h2/schema-h2.sql b/core/src/test/resources/h2/schema-h2.sql
similarity index 98%
copy from scripts/h2/schema-h2.sql
copy to core/src/test/resources/h2/schema-h2.sql
index 0ebf91fa3..3ffa848a8 100644
--- a/scripts/h2/schema-h2.sql
+++ b/core/src/test/resources/h2/schema-h2.sql
@@ -168,7 +168,7 @@ CREATE TABLE IF NOT EXISTS `role_meta` (
CREATE TABLE IF NOT EXISTS `role_meta_securable_object` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'auto increment
id',
`role_id` BIGINT(20) UNSIGNED NOT NULL COMMENT 'role id',
- `entity_id` BIGINT(20) NOT NULL COMMENT 'securable object entity id',
+ `entity_id` BIGINT(20) UNSIGNED NOT NULL COMMENT 'securable object entity
id',
`type` VARCHAR(128) NOT NULL COMMENT 'securable object type',
`privilege_names` VARCHAR(256) NOT NULL COMMENT 'securable object
privilege names',
`privilege_conditions` VARCHAR(256) NOT NULL COMMENT 'securable object
privilege conditions',
@@ -176,7 +176,8 @@ CREATE TABLE IF NOT EXISTS `role_meta_securable_object` (
`last_version` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'securable object
last version',
`deleted_at` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'securable
object deleted at',
PRIMARY KEY (`id`),
- KEY `idx_obj_rid` (`role_id`)
+ KEY `idx_obj_rid` (`role_id`),
+ KEY `idx_obj_eid` (`entity_id`)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `user_role_rel` (
diff --git a/docs/how-to-upgrade.md b/docs/how-to-upgrade.md
new file mode 100644
index 000000000..87401f328
--- /dev/null
+++ b/docs/how-to-upgrade.md
@@ -0,0 +1,111 @@
+---
+title: How to upgrade Apache Gravitino
+slug: /how-to-upgrade
+license: "This software is licensed under the Apache License version 2."
+---
+
+## Introduction
+This document describes how to upgrade the schema of backed
+Gravitino instance from one release version of Gravitino to another
+release version of Gravitino. For example, by following the steps listed
+below it is possible to upgrade a Gravitino 0.6.0 schema to a
+Gravitino 0.7.0 schema. Before attempting this project we
+strongly recommend that you read through all of the steps in this
+document and familiarize yourself with the required tools.
+
+## Upgrade Steps
+
+### Step 1: Shutdown your Gravitino instance
+Shutdown your Gravitino instance and restrict access to the
+Apache Gravitino's database. It is very important that no one else
+accesses or modifies the contents of database while you are
+performing the schema upgrade.
+
+### Step 2: Backup your Gravitino instance
+Create a backup of your database. This will allow
+you to revert any changes made during the upgrade process if
+something goes wrong.
+
+#### MySQL
+
+For MySQL, you can use the following command to backup your database:
+
+```shell
+mysqldump --opt <db_name> > backup.sql
+```
+Note that you may also need to specify a hostname and username
+using the `--host` and `--user` command line switches.
+
+#### H2
+The easiest way of accomplishing this task is
+by creating a copy of the directory containing your database.
+
+### Step 3: Dump your Gravitino database
+Dump your Gravitino database schema to a file
+
+#### MySQL
+You can use the mysqldump utility to dump the database schema to a file:
+
+```shell
+mysqldump --skip-add-drop-table --no-data <db_name> > schema-x.y.z-mysql.sql
+```
+
+#### H2
+For H2, you can use the `Script` tool to dump the database schema to a file:
+
+```shell
+wget https://repo1.maven.org/maven2/com/h2database/h2/1.4.200/h2-1.4.200.jar
+java -cp h2-1.4.200.jar org.h2.tools.Script -url
"jdbc:h2:file:<db_file>;DB_CLOSE_DELAY=-1;MODE=MYSQL" -user <user> -password
<password> -script backup.sql
+```
+Note that you may need to specify your h2 file path, username and password
+
+
+### Step 4: Determine differences between your schema and the official schema
+The schema upgrade scripts assume that the schema you are upgrading
+closely matches the official schema for your particular version of
+Gravitino. The files in this directory with names like
+`schema-x.y.z-<type>.sql` contain dumps of the official schemas
+corresponding to each of the released versions of Gravitino. You can
+determine differences between your schema and the official schema
+by comparing the contents of the official dump with the schema dump
+you created in the previous step. Some differences are acceptable and will not
interfere
+with the upgrade process, but others need to be resolved manually
+or the upgrade scripts will fail to complete.
+
+### Step 5: Apply the upgrade scripts
+You are now ready to run the schema upgrade scripts. If you are
+upgrading from Gravitino 0.6.0 to Gravitino 0.7.0 you need to run the
+`upgrade-0.6.0-to-0.7.0-<type>.sql` script, but if you are upgrading
+from 0.6.0 to 0.8.0 you will need to run the 0.6.0 to 0.7.0 upgrade
+script followed by the 0.7.0 to 0.8.0 upgrade script.
+
+#### MySQL
+Assuming you are upgrading the version of Gravitino server from 0.6.0 to 0.8.0
+
+```shell
+mysql --verbose
+mysql> use <db_name>;
+Database changed
+mysql> source upgrade-0.6.0-to-0.7.0-mysql.sql
+mysql> source upgrade-0.7.0-to-0.8.0-mysql.sql
+```
+#### H2
+For H2, you can use the `RunScript` tool to apply the upgrade script:
+
+```shell
+java -cp h2-1.4.200.jar org.h2.tools.RunScript -url
"jdbc:h2:file:<db_file>;DB_CLOSE_DELAY=-1;MODE=MYSQL" -user <user> -password
<password> -script upgrade-0.6.0-to-0.7.0-h2.sql
+java -cp h2-1.4.200.jar org.h2.tools.RunScript -url
"jdbc:h2:file:<db_file>;DB_CLOSE_DELAY=-1;MODE=MYSQL" -user <user> -password
<password> -script upgrade-0.7.0-to-0.8.0-h2.sql
+```
+
+These scripts should run to completion without any errors. If you
+do encounter errors you need to analyze the cause and attempt to
+trace it back to one of the preceding steps.
+
+### Step 6: Verify the upgrade
+The final step of the upgrade process is validating your freshly
+upgraded schema against the official schema for your particular
+version of Gravitino. This is accomplished by repeating steps (3) and
+(4), but this time comparing against the official version of the
+upgraded schema, e.g. if you upgraded the schema to Gravitino 0.8.0 then
+you will want to compare your schema dump against the contents of
+`schema-0.8.0-<type>.sql`
\ No newline at end of file
diff --git a/docs/index.md b/docs/index.md
index 71a48ab3a..d6c9d9260 100644
--- a/docs/index.md
+++ b/docs/index.md
@@ -149,3 +149,4 @@ Gravitino provides several ways to configure and manage the
Gravitino server. Se
a Gravitino release.
* [Publish Docker images](./publish-docker-images.md): a guide to publishing
Gravitino Docker images;
also lists the change logs of Gravitino CI Docker images and release images.
+* [How to upgrade Gravitino](./how-to-upgrade.md): a guide to upgrading the
schema of Gravitino storage backend from one release version to another.
diff --git a/scripts/h2/schema-h2.sql b/scripts/h2/schema-0.6.0-h2.sql
similarity index 98%
rename from scripts/h2/schema-h2.sql
rename to scripts/h2/schema-0.6.0-h2.sql
index 0ebf91fa3..b2aad0282 100644
--- a/scripts/h2/schema-h2.sql
+++ b/scripts/h2/schema-0.6.0-h2.sql
@@ -168,7 +168,7 @@ CREATE TABLE IF NOT EXISTS `role_meta` (
CREATE TABLE IF NOT EXISTS `role_meta_securable_object` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'auto increment
id',
`role_id` BIGINT(20) UNSIGNED NOT NULL COMMENT 'role id',
- `entity_id` BIGINT(20) NOT NULL COMMENT 'securable object entity id',
+ `entity_id` BIGINT(20) UNSIGNED NOT NULL COMMENT 'securable object entity
id',
`type` VARCHAR(128) NOT NULL COMMENT 'securable object type',
`privilege_names` VARCHAR(256) NOT NULL COMMENT 'securable object
privilege names',
`privilege_conditions` VARCHAR(256) NOT NULL COMMENT 'securable object
privilege conditions',
@@ -176,7 +176,8 @@ CREATE TABLE IF NOT EXISTS `role_meta_securable_object` (
`last_version` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'securable object
last version',
`deleted_at` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'securable
object deleted at',
PRIMARY KEY (`id`),
- KEY `idx_obj_rid` (`role_id`)
+ KEY `idx_obj_rid` (`role_id`),
+ KEY `idx_obj_eid` (`entity_id`)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `user_role_rel` (
@@ -241,7 +242,7 @@ CREATE TABLE IF NOT EXISTS `tag_relation_meta` (
`last_version` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'tag relation last
version',
`deleted_at` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'tag relation
deleted at',
PRIMARY KEY (`id`),
- UNIQUE KEY `uk_ti_mi_mo_del` (`tag_id`, `metadata_object_id`,
`metadata_object_type`, `deleted_at`),
+ UNIQUE KEY `uk_ti_mi_del` (`tag_id`, `metadata_object_id`, `deleted_at`),
KEY `idx_tid` (`tag_id`),
KEY `idx_mid` (`metadata_object_id`)
) ENGINE=InnoDB;
