Implementing Flyway for Tibero and Open Source Contribution

Introduction

In this article, I’d like to share the process of applying Flyway for DDL version management of the Tibero database used at our company, and the subsequent open source contribution experience.

Existing Problems

When I joined the project, there were the following issues related to database management:

1. Excessive DDL Legacy Code
   • Frequent schema modifications due to changing service requirements
   • Frequent database initialization in development and local environments
   • Increased risk of human error from manual DDL modification and execution
2. Unorganized DDL Management
   • Massive accumulation of DDL files
   • Uncontrolled DDL additions/modifications/deletions based on team requirements
   • Schema change work concentrated on specific individuals
3. Decreased Development Productivity
   • Difficulty in DB schema changes across environments (development-staging-production)
   • Increased burden on DB separation
   • Complexity in development environment setup

Eventually, I thought these problems would seriously lead to decreased development productivity as shown below.

Solution

To solve these problems, I considered introducing Flyway, a DB version control library that I had used before, which meant no learning curve and quick application.

However, there was a new problem that Flyway doesn’t officially support Tibero…

Not only our team but other developers with the same needs on GitHub confirmed this issue, and looking at the Flyway maintainer’s response, there seemed to be no plans for future support. Considering the syntactic similarities between Oracle and Tibero, which is already supported,

I thought I needed to implement it myself to prevent DB Migration issues that could easily occur in the current situation.

Project Goal Setting

Our team members agreed, and we started the project with the following goals:

Implementation Process

Our goal was to implement 6 of Flyway’s basic commands (baseline, migrate, clean, info, validate, repair), excluding ‘undo’ which is a Pro version feature.

Since there’s a lot of implementation content, rather than describing everything in the blog, I’ll focus on the parts that differed from Oracle and the difficulties encountered with their solutions.

First, the most time-consuming and difficult part of the implementation was the clean command.

Since Flyway’s clean function removes all schema objects, it needs to check the existence of each schema object and delete them.

In this process, the differences between Tibero and Oracle were prominent (schema object names, query methods, unsupported objects, etc.) were different between the two databases,

so I had to carefully review Tibero’s official documentation and write appropriate query statements for each object.

1. Differences between Oracle and Tibero in Flyway Clean

For example, Oracle uses ALL_SDO_GEOM_METADATA to query specific metadata, but in Tibero, we had to use the corresponding ALL_GEOMETRY_COLUMNS.

As shown below, looking at Oracle’s flyway clean method implementation, there’s a task to delete locatorMetadata first before the clean operation:

 private boolean locatorMetadataExists() throws SQLException {
  return database.queryReturnsRows("SELECT * FROM ALL_SDO_GEOM_METADATA WHERE OWNER = ?", name);
}

private void cleanLocatorMetadata() throws SQLException {
        if (!locatorMetadataExists()) {
            return;
        }

        if (!isDefaultSchemaForUser()) {
            LOG.warn("Unable to clean Oracle Locator metadata for schema " + database.quote(name) +
                             " by user \"" + database.doGetCurrentUser() + "\": unsupported operation");
            return;
        }

        jdbcTemplate.getConnection().commit();
        jdbcTemplate.execute("DELETE FROM USER_SDO_GEOM_METADATA");
        jdbcTemplate.getConnection().commit();
    }

For Tibero, the part corresponding to ALL_SDO_GEOM_METADATA is ALL_GEOMETRY_COLUMNS, so we needed to add the work to delete that part.

private void cleanLocatorMetadata() throws SQLException {
		if (!locatorMetadataExists()) {
			return;
		}

		if (!isDefaultSchemaForUser()) {
			return;
		}

		jdbcTemplate.getConnection().commit();
		jdbcTemplate.execute("DELETE FROM USER_GEOMETRY_COLUMNS");
		jdbcTemplate.getConnection().commit();
	}

	private boolean locatorMetadataExists() throws SQLException {
		return database.queryReturnsRows("SELECT * FROM ALL_GEOMETRY_COLUMNS WHERE F_TABLE_SCHEMA = ?",
			name);
	}

Also, for queueTable, there are considerations when emptying in Tibero: oracle, tibero - queue table

1. For queue_table, in Tibero it’s also queried in all_tables, so we need to add a query to exclude it if it’s also queried in all_queue_tables
2. For queue_table, a lob index is also created when creating, and the associated lob index is automatically deleted when dropping the queue table
3. Therefore, when querying index objects, lob indexes should be excluded to prevent errors

// All indexes, except for domain indexes and lob indexes, should be dropped after tables (if any left).
        INDEX("INDEX") {
          @Override
          public List<String> getObjectNames (JdbcTemplate jdbcTemplate, TiberoDatabase database,
            TiberoSchema schema) throws SQLException {
            return jdbcTemplate.queryForStringList(
              "SELECT INDEX_NAME FROM ALL_INDEXES WHERE OWNER = ?" +
                " AND INDEX_NAME NOT LIKE 'SYS_C%'" +
                " AND INDEX_TYPE NOT LIKE '%DOMAIN%'" +
                " AND INDEX_TYPE NOT LIKE '%LOB%'",
              schema.getName()
            );
          }
        }

Besides these, I referred to Tibero’s official documentation and tested syntactically different parts myself
to implement Flyway’s features.

See detailed implementation here

2. Flyway Tibero Test with Testcontainers

Ultimately, for team members and everyone using this implemented code to trust and use it, writing test code was essential.

The problem was that Tibero doesn’t have an official docker image, and testcontainers also doesn’t support Tibero as a module, so both needed implementation…

In this article, I’ll just leave links to successfully implemented and publicly released resources
tibero-docker - github
testcontainers-tibero - github

This part will continue in the next article…

Open Source Contribution

As the implemented Flyway for Tibero was used within the team, it worked without problems as the team originally intended, and other teams could easily apply it based on the documentation.

Therefore, we decided to make an open source contribution with agreement, to help developers who use Tibero and have the same needs as us.

Conclusion

Open source contribution was my second open source contribution after MDN Korean localization,
and I think the parts we implemented (tibero-docker, tibero flyway) helped the team and also helped fill the Tibero knowledge gap I had while developing.