17 August 2019
Flyway is a database migration (versioning) tool that supports a wide variety of database systems. Recently, they accepted my pull request to add support for Firebird in Flyway 6.0. Although my pull request supported Firebird 2.1 and higher, the Flyway team has decided to only support Firebird 3 and higher. I assume this was decided as Firebird 2.5 and earlier are all end-of-life.
| At this time, the release of Flyway 6.0 has not been announced yet, but it is already available from Maven central. |
Flyway provides a number of ways to migrate database:
Personally, I prefer to use the Java API to migrate the database from within an application. However, that solution requires the application to have access to credentials with sufficient rights in the database, which isn’t alway an option. Depending on your deployment model, it might be better to provide a separate application or use one of the other three options to migrate a database.
I will show a small example of use of Flyway with Firebird 3 below. Be sure to read Flyway’s Get Started and Flyway’s documentation for more information and an overview of all features. This example doesn’t cover all options available.
|
In its current form, the example does what is better done using the command-line or the Maven or Gradle plugins. Consider it a stepping stone for a migration feature as part of a bigger application. |
This example will use Gradle as its build and dependency tool, and for running the example.
The full code of the example is available from https://github.com/mrotteveel/firebird-flyway-example.
plugins {
id 'java'
id 'application'
}
group 'nl.lawinegevaar'
version '1.0-SNAPSHOT'
sourceCompatibility = 1.8
repositories {
mavenCentral()
}
dependencies {
implementation 'org.firebirdsql.jdbc:jaybird-jdk18:3.0.6'
implementation 'org.flywaydb:flyway-core:6.0.0'
}
application {
mainClassName = 'nl.lawinegevaar.firebird.flyway.example.Main'
}To execute the migrations, we’ll use the following simple main class.
package nl.lawinegevaar.firebird.flyway.example;
import org.flywaydb.core.Flyway;
import java.io.IOException;
public class Main {
public static void main(String[] args) throws IOException {
DatabaseProperties databaseProperties = DatabaseProperties.loadDefault();
migrateDatabase(databaseProperties);
}
private static void migrateDatabase(DatabaseProperties databaseProperties) {
Flyway flyway = Flyway.configure()
.dataSource(
databaseProperties.getUrl(),
databaseProperties.getUser(),
databaseProperties.getPassword())
.load();
flyway.migrate();
}
}This example assumes an existing empty database is used.
The database connection information is configured in a database.properties file in src/main/resources.
This properties file is accessed through DatabaseProperties.
This simple application can be built and run using Gradle:
./gradlew run
When run without existing migrations, the following logging will be produced: [1]
Aug 17, 2019 10:14:15 AM org.flywaydb.core.internal.license.VersionPrinter printVersionOnly INFO: Flyway Community Edition 6.0.0 by Boxfuse Aug 17, 2019 10:14:16 AM org.flywaydb.core.internal.database.DatabaseFactory createDatabase INFO: Database: jdbc:firebirdsql://localhost/flyway-example.fdb (Firebird 3.0 3.0) Aug 17, 2019 10:14:16 AM org.flywaydb.core.internal.command.DbValidate validate INFO: Successfully validated 0 migrations (execution time 00:00.064s) Aug 17, 2019 10:14:16 AM org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory create INFO: Creating Schema History table "flyway_schema_history" ... Aug 17, 2019 10:14:16 AM org.flywaydb.core.internal.command.DbMigrate migrateGroup INFO: Current version of schema "default": << Empty Schema >> Aug 17, 2019 10:14:16 AM org.flywaydb.core.internal.command.DbMigrate logSummary INFO: Schema "default" is up to date. No migration necessary.
Flyway has now created a flyway_schema_history table which is used to track the executed migrations.
Now, lets add a migration to add a simple person table. Flyway uses a naming format with scripts prefixed by a version, followed by a descriptive name. See SQL-based migrations for details.
The file we create is src/main/resources/db/migrations/V1__Create_person_table.sql
create table PERSON (
ID int generated by default as identity constraint PK_PERSON primary key,
NAME varchar(100) not null
);Executing our program, this now logs:
... Aug 17, 2019 10:20:24 AM org.flywaydb.core.internal.command.DbValidate validate INFO: Successfully validated 1 migration (execution time 00:00.083s) Aug 17, 2019 10:20:24 AM org.flywaydb.core.internal.command.DbMigrate migrateGroup INFO: Current version of schema "default": << Empty Schema >> Aug 17, 2019 10:20:24 AM org.flywaydb.core.internal.command.DbMigrate doMigrateGroup INFO: Migrating schema "default" to version 1 - Create person table Aug 17, 2019 10:20:24 AM org.flywaydb.core.internal.command.DbMigrate logSummary INFO: Successfully applied 1 migration to schema "default" (execution time 00:00.091s)
Now, we want to add some data to the person table:
insert into PERSON (NAME) values ('Mark');
insert into PERSON (NAME) values ('William');
insert into PERSON (NAME) values ('Andrew');Running this will produce:
... Aug 17, 2019 10:26:48 AM org.flywaydb.core.internal.command.DbValidate validate INFO: Successfully validated 2 migrations (execution time 00:00.110s) Aug 17, 2019 10:26:48 AM org.flywaydb.core.internal.command.DbMigrate migrateGroup INFO: Current version of schema "default": 1 Aug 17, 2019 10:26:48 AM org.flywaydb.core.internal.command.DbMigrate doMigrateGroup INFO: Migrating schema "default" to version 2 - Add people Aug 17, 2019 10:26:48 AM org.flywaydb.core.internal.command.DbMigrate logSummary INFO: Successfully applied 1 migration to schema "default" (execution time 00:00.047s)
A migration script is a collection of statement separated by a statement terminator, by default this is a semi-colon (;).
To be able to execute procedural SQL statements (eg create procedure or execute block) in a migration, the statement terminator must be changed.
To change the statement terminator, the Firebird support in Flyway supports the SET TERM statement that is also used by ISQL and other Firebird tools.[2]
As an example of creating a stored procedure:
set term #;
create procedure factorial(max_value integer)
returns (factorial bigint)
as
declare variable counter integer;
begin
factorial = 1;
counter = 0;
while (counter <= max_value) do
begin
if (counter > 0) then
factorial = factorial * counter;
counter = counter + 1;
end
end#
set term ;#Although Firebird has transactional DDL, it is not possible to use objects created in a transaction in DML in that same transaction.
Phrased differently, DML in Firebird can only use objects (tables, columns, etc) as they existed at the start of the transaction.
As a result, it is not possible to create a table and in the same migration populate it with data.[3]
You can logically group related DDL and DML together by using minor version numbers in your migration, for example V4_1__Create_department_table.sql and V4_2__Add_departments.sql.
The following statements are not supported as they will invalidate the transaction handle used by Jaybird:
SET TRANSACTION
COMMIT [WORK]
ROLLBACK [WORK]
Flyway uses quoted identifiers for its version table and assumes single quote string literals when parsing scripts. This means that Flyway will only work on dialect 3 databases.
Flyway is a powerful tool to manage changes to your database, and I think it is good news that Firebird is now supported. This example only shows the most basic usage of Flyway, so make sure to read the full documentation so you don’t miss out on other useful features.
If you need help, or more information, ask a question on the Firebird-Java mailinglist. You can subscribe by sending an email to firebird-java-subscribe@yahoogroups.com
SET TERM is not part of the Firebird server statement syntax
COMMIT RETAIN; statement to your script to commit in the middle of a script. This is not recommend as this will break the transactionality of your script, which can result in maintenance and recovery headaches if a script partially succeeds and partially fails
02 June 2019
I have just released org.firebirdsql:firebird-testcontainers-java:1.0.0, based on Testcontainers.
This library allows you to start a Firebird docker container for a JUnit 4 or 5 test or manually from your code. This can be useful for JUnit-based integration tests.
For a very simple example, add to your pom.xml:
<dependency>
<groupId>org.firebirdsql</groupId>
<artifactId>firebird-testcontainers-java</artifactId>
<version>1.0.0</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.firebirdsql.jdbc</groupId>
<artifactId>jaybird-jdk18</artifactId>
<version>3.0.6</version>
<scope>test</scope>
</dependency>And create a test:
/**
* Simple test demonstrating use of {@code @Rule}.
*/
public class ContainerExampleTest {
@Rule
public final FirebirdContainer container = new FirebirdContainer();
@Test
public void canConnectToContainer() throws Exception {
try (Connection connection = DriverManager
.getConnection(container.getJdbcUrl(), container.getUsername(),
container.getPassword());
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("select CURRENT_USER from RDB$DATABASE")) {
assertTrue("has row", rs.next());
assertEquals("user name",
container.getUsername().toUpperCase(), rs.getString(1));
}
}
}See https://github.com/FirebirdSQL/firebird-testcontainers-java and https://www.testcontainers.org/ for details.
25 May 2019
We are happy to announce the release of Jaybird 3.0.6.
This release is largely a bugfix release, but contains additional support for Firebird 4.
Jaybird 3.0.6 contains the following fixes and changes:
Fixed: Exceptions during fetch of cached result sets (holdable over commit, scrollable and metadata) prevented prepared statement reuse/re-execute with error "Statement state CURSOR_OPEN only allows next states [CLOSING, PREPARED, ERROR], received EXECUTING" (JDBC-531)
Improvement: Added FBManager.setDefaultCharacterSet to set default database character set during database creation (JDBC-541)
New feature: Support for Firebird 3 case sensitive user names (JDBC-549)
Fixed: Savepoints did not work in connection dialect 1 as savepoint names were always quoted (JDBC-556)
Changed: The DatabaseMetaData statement cache introduced in Jaybird 3 was unlimited, it is now limited to 12 prepared statements; the least recently used statement will be closed and removed when a new statement is added (JDBC-557)
Fixed: UPDATE OR INSERT with existing RETURNING clause handled incorrectly for generated keys (JDBC-566)
Fixed: Exceptions during initialization of result sets would not properly close the database cursor leading to error "Current statement state (CURSOR_OPEN) does not allow call to prepare" on reuse of the statement (or errors similar to described for JDBC-531 above). (JDBC-571)
New feature: boolean connection property ignoreProcedureType to disable usage of metadata for stored procedure types in CallableStatement. When set to true, call escapes and EXECUTE PROCEDURE will default to use EXECUTE PROCEDURE and not switch to SELECT for selectable stored procedures. (JDBC-576)
New feature: connection properties timeZoneBind and sessionTimeZone for limited support for Firebird 4 TIME(STAMP) WITH TIME ZONE types, and decfloatBind for limited support for Firebird 4 DECFLOAT types. (JDBC-538)
Fixed: Connection property defaultIsolation/isolation did not work through DriverManager, but only on DataSource implementations. (JDBC-584)
Jaybird 3.0 supports Firebird 2.0 and higher, on Java 7, 8 and 9 and higher. Basic Java 9 and higher compatibility is provided through the Java 8 version of the driver.
See also:
27 April 2019
We are happy to announce the first beta for Jaybird 4.
We’d really appreciate it if you take the time to test this version of Jaybird with your applications. Bug reports about undocumented changes in behavior are appreciated. Feedback can be sent to the Firebird-java mailing list or reported on the issue tracker http://tracker.firebirdsql.org/browse/JDBC.
Jaybird 4 is - compared to Jaybird 3 - an incremental release that builds on the foundations of Jaybird 3. The focus of this release has been on further improving JDBC support and adding support for the new data types and features of Firebird 4.
The main new features are:
Wire encryption support (backported to Jaybird 3.0.4)
Database encryption support (backported to Jaybird 3.0.4)
Authentication plugin improvements
Firebird 4 DECFLOAT support
Firebird 4 extended numeric precision support
Firebird 4 time zone support
JDBC RowId support
JDBC DatabaseMetaData.getPseudoColumns implemented
JDBC DatabaseMetaData.getVersionColumns implemented
Improved JDBC function escape support
New JDBC protocol prefix jdbc:firebird:
Generated keys support improvements
Jaybird 4.0.0-beta-1 is for testing purposes only, and not intended for production use.
See also:
06 April 2019
We are happy to announce the release of Jaybird 2.2.15.
Jaybird 2.2.15 is the last version of Jaybird 2.2.x, and we will not plan new changes or fixes for 2.2.x. This effectively means that Jaybird 2.2.x is End-Of-Life.
If you run into problems, we will recommend that you switch to Jaybird 3 (or 4 once it is released). Contact us on Firebird-Java for questions.
Jaybird 2.2.15 contains the following fixes and changes:
Fixed: Exceptions during fetch of cached result sets (holdable over commit, scrollable and metadata) prevented prepared statement reuse/re-execute with error "Attempt to reopen an open cursor" (JDBC-531)
Fixed: Jaybird cannot parse Firebird version numbers with revisions (JDBC-534)
Fixed: Incorrect parsing of Firebird version numbers (JDBC-535)
Jaybird 2.2 supports Firebird 1.0 and higher, on Java 6, 7 and 8. For Java 11 or higher, use Jaybird 3.
See also:
Older posts are available in the archive.