Its really tough to migrate a DB from one type to another but there is a simple way which is 'flyway' which takes care of this migration if you do it in proper way using sql scripts . You can put all your DDL changes as well as DML changes into scripts . Once Application gets started , flyway will be triggered and it will take care of creating all the tables and insert or update the rows if any DML is provided.
So we can achieve this in very simple way using below approach-
Java-based migrations
Java-based migrations are a great fit for all changes that can not easily be expressed using SQL.
These would typically be things like
- BLOB & CLOB changes
- Advanced bulk data changes (Recalculations, advanced format changes, …)
Naming
In order to be picked up by Flyway, Java-based Migrations must implement the
JavaMigration interface. Most users however should inherit from the convenience class BaseJavaMigration instead as it encourages Flyway’s default naming convention, enabling Flyway to automatically extract the version and the description from the class name. To be able to do so, the class name must comply with the following naming pattern:Versioned Migrations
Prefix Separator
V2__Add_new_table
Version Description
Undo Migrations
Prefix Separator
U2__Add_new_table
Version Description
Repeatable Migrations
Prefix Separator
R__Add_new_table
Description
The file name consists of the following parts:
- Prefix:
Vfor versioned migrations,Ufor undo migrations,Rfor repeatable migrations - Version: Underscores (automatically replaced by dots at runtime) separate as many parts as you like (Not for repeatable migrations)
- Separator:
__(two underscores) - Description: Underscores (automatically replaced by spaces at runtime) separate the words
If you need more control over the class name, you can override the default convention by implementing the
JavaMigration interface directly.
This will allow you to name your class as you wish. Version, description and migration category are provided by implementing the respective methods.
Discovery
Flyway discovers Java-based migrations on the Java classpath in the packages referenced by the
locations property. my-project
src
main
java
db
migration classpath:db/migration
R__My_view
U1_1__Fix_indexes
V1__Initial_version
V1_1__Fix_indexes
pom.xml
New java migrations are discovered automatically through classpath scanning at runtime. The scanning is recursive. Java migrations in subpackages of the specified ones are also picked up.
Checksums and Validation
Unlike SQL migrations, Java migrations by default do not have a checksum and therefore do not participate in the change detection of Flyway’s validation. This can be remedied by implementing the
getChecksum() method, which you can then use to provide your own checksum, which will then be stored and validated for changes.Sample Class
package db.migration;
import org.flywaydb.core.api.migration.BaseJavaMigration;
import org.flywaydb.core.api.migration.Context;
import java.sql.PreparedStatement;
/**
* Example of a Java-based migration.
*/
public class V1_2__Another_user extends BaseJavaMigration {
public void migrate(Context context) throws Exception {
try (PreparedStatement statement =
context
.getConnection()
.prepareStatement("INSERT INTO test_user (name) VALUES ('Obelix')")) {
statement.execute();
}
}
}
Spring
If your application already uses Spring and you do not want to use JDBC directly you can easily use Spring JDBC’s
JdbcTemplateinstead:package db.migration;
import org.flywaydb.core.api.migration.BaseJavaMigration;
import org.flywaydb.core.api.migration.Context;
import java.sql.PreparedStatement;
/**
* Example of a Java-based migration using Spring JDBC.
*/
public class V1_2__Another_user extends BaseJavaMigration {
public void migrate(Context context) {
new JdbcTemplate(new SingleConnectionDataSource(context.getConnection(), true))
.execute("INSERT INTO test_user (name) VALUES ('Obelix')");
}
}
Transactions
By default, Flyway always wraps the execution of an entire migration within a single transaction.
Alternatively you can also configure Flyway to wrap the entire execution of all migrations of a single migration run within a single transaction by setting the
group property to true.
If Flyway detects that a specific statement cannot be run within a transaction due to technical limitations of your database, it won’t run that migration within a transaction. Instead it will be marked as non-transactional.
By default transactional and non-transactional statements cannot be mixed within a migration run. You can however allow this by setting the
mixed property to true. Note that this is only applicable for PostgreSQL, Aurora PostgreSQL, SQL Server and SQLite which all have statements that do not run at all within a transaction. This is not to be confused with implicit transaction, as they occur in MySQL or Oracle, where even though a DDL statement was run within within a transaction, the database will issue an implicit commit before and after its execution.Important Note
If your database cleanly supports DDL statements within a transaction, failed migrations will always be rolled back (unless they were marked as non-transactional).
If on the other hand your database does NOT cleanly supports DDL statements within a transaction (by for example issuing an implicit commit before and after every DDL statement), Flyway won’t be able to perform a clean rollback in case of failure and will instead mark the migration as failed, indicating that some manual cleanup may be required.
Query Results
Migrations are primarily meant to be executed as part of release and deployment automation processes and there is rarely the need to visually inspect the result of SQL queries.
There are however some scenarios where such manual inspection makes sense, and therefore Flyway Pro and Enterprise Edition also display query results in the usual tabular form when a
SELECT statement (or any other statement that returns results) is executed.Schema History Table
To keep track of which migrations have already been applied when and by whom, Flyway adds a special schema history table to your schema. You can think of this table as a complete audit trail of all changes performed against the schema. It also tracks migration checksums and whether or not the migrations were successful.
Read more about this in our getting started guide on how Flyway works.
Migration States
Migrations are either resolved or applied. Resolved migrations have been detected by Flyway’s filesystem and classpath scanner. Initially they are pending. Once they are executed against the database, they become applied.
When the migration succeeds it is marked as success in Flyway’s schema history table.
When the migration fails and the database supports DDL transactions, the migration is rolled back and nothing is recorded in the schema history table.
When the migration fails and the database doesn’t supports DDL transactions, the migration is marked as failed in the schema history table, indicating manual database cleanup may be required.
Versioned migrations whose effects have been undone by an undo migration are marked as undone.
Repeatable migrations whose checksum has changed since they are last applied are marked as outdated until they are executed again.
When Flyway discovers an applied versioned migration with a version that is higher than the highest known version (this happens typically when a newer version of the software has migrated that schema), that migration is marked as future.
I hope you got the enough knowledge to migrate your RDBMS DB , please feel free to contact me in comment box for any help if you stuck somewhere configuring this.
No comments :
Post a Comment