Hi all,
In our project, we need to implement materialized views. Creating them using SQL with Liquibase is no problem, but it was failing when recreating the schema.
All materialized views are seen as TABLEs, so when Liquibase runs the “dropAll” job, the following SQL is called, and fails:
DROP TABLE MY_MV;
it should be:
DROP MATERIALIZED VIEW MY_MV;
Here’s what I had to do to solve the issue:
-
extends OracleDatabase to override the “dropDatabaseObjects” method.
-
create DropMaterializedView Satement, Generator and Change.
-
add my package in the MANIFEST.MF of my plugin JAR so the OracleDatabase would be chosen by the factory.
To make it more simple, maybe we could include my code in one of the releases?
In a nutshell, here it is:
- private static final Logger logger = LogFactory.getLogger();
- public OracleDatabase() {
- super();
- SqlGeneratorFactory.getInstance().register(new DropMaterializedViewGenerator());
- }
- @Override
- public int getPriority() {
- return PRIORITY_DATABASE;
- }
- @Override
- public void dropDatabaseObjects(String schema) throws DatabaseException {
- logger.info("## custom Oracle DataBase drop support ##");
- List mviews = this.retrieveMViewNames();
- List changes = new ArrayList(mviews.size());
- for (String name : mviews) {
- DropMaterializedView ch = new DropMaterializedView();
- ch.setViewName(name);
- ch.setSchemaName(schema);
- changes.add(ch);
- }
- for (Change change : changes) {
- for (SqlStatement statement : change.generateStatements(this)) {
- ExecutorService.getInstance().getExecutor(this).execute(statement);
- }
- }
- super.dropDatabaseObjects(schema);
- }
- private List retrieveMViewNames() {
- List mviews = new ArrayList();
- Statement st = null;
- ResultSet rs = null;
- try {
- st = ((JdbcConnection) this.getConnection()).getUnderlyingConnection().createStatement();
- rs = st.executeQuery("select MVIEW_NAME from all_mviews");
- while (rs.next()) {
- String mview = rs.getString(1);
- logger.debug("found MVIEW: " + mview);
- mviews.add(mview);
- }
- }
- catch (SQLException e) {
- e.printStackTrace();
- } finally {
- try {
- if (rs != null) {
- rs.close();
- }
- if (st != null) {
- st.close();
- }
- }
- catch (SQLException e) {
- e.printStackTrace();
- }
- }
- return mviews;
- }
For the statement and generators, the same as the DropView can be implemented, with the generator returning "DROP MATERIALIZED VIEW " + args.
Maybe there is a better way of retrieving the MViews than querying a table. I’ll look into it if my projects leaves me time for that.
Thanks for the great tool,
Erwan