Oracle Materialized views DROP

Liquibase Development
1

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:


  1. extends OracleDatabase to override the “dropDatabaseObjects” method.

  2. create DropMaterializedView Satement, Generator and Change.

  3. 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:

  2.     private static final Logger logger = LogFactory.getLogger();


  5.     public OracleDatabase() {
  6.         super();
  7.         SqlGeneratorFactory.getInstance().register(new DropMaterializedViewGenerator());
  8.     }

  10.     @Override
  11.     public int getPriority() {
  12.         return PRIORITY_DATABASE;
  13.     }

  15.     @Override
  16.     public void dropDatabaseObjects(String schema) throws DatabaseException {
  17.         logger.info("## custom Oracle DataBase drop support ##");

  19.         List mviews = this.retrieveMViewNames();
  20.         List changes = new ArrayList(mviews.size());
  21.         for (String name : mviews) {
  22.             DropMaterializedView ch = new DropMaterializedView();
  23.             ch.setViewName(name);
  24.             ch.setSchemaName(schema);
  25.             changes.add(ch);
  26.         }
  27.         for (Change change : changes) {
  28.             for (SqlStatement statement : change.generateStatements(this)) {
  29.                 ExecutorService.getInstance().getExecutor(this).execute(statement);
  30.             }
  31.         }

  33.         super.dropDatabaseObjects(schema);
  34.     }

  36.     private List retrieveMViewNames() {
  37.         List mviews = new ArrayList();
  38.         Statement st = null;
  39.         ResultSet rs = null;
  40.         try {
  41.             st = ((JdbcConnection) this.getConnection()).getUnderlyingConnection().createStatement();
  42.             rs = st.executeQuery("select MVIEW_NAME from all_mviews");

  44.             while (rs.next()) {
  45.                 String mview = rs.getString(1);
  46.                 logger.debug("found MVIEW: " + mview);
  47.                 mviews.add(mview);
  48.             }
  49.         }
  50.         catch (SQLException e) {
  51.             e.printStackTrace();
  52.         } finally {
  53.             try {
  54.                 if (rs != null) {
  55.                     rs.close();
  56.                 }
  57.                 if (st != null) {
  58.                     st.close();
  59.                 }
  60.             }
  61.             catch (SQLException e) {
  62.                 e.printStackTrace();
  63.             }
  64.         }
  65.         return mviews;
  66.     }

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

2

Hi,

It's a pitty this can't be achieved through liquibase itself so I worked around it using sql-maven-plugin to drop all materialized views before liquibase kicks in and only when liquibase.dropFirst is set to true. This avoids custom code and messing with classpath.

Used maven profiles to set a property to be the inverse boolean of another one since maven doesn't support it out of the box. I guess you could put the whole plugin execution in the profile too. It would probably be neater to also externalise the sql in the pom to an sql file, but for purposes of simple answer it's all below.

 My pom.xml …

  1.    
     …
     <db.skip.oracle.dropFirst>true</db.skip.oracle.dropFirst>
       
        …
  2.    
           
               
                    org.codehaus.mojo
                    sql-maven-plugin
                    1.5
                   
                       
                            oracle
                            ojdbc6
                            ${oracle-jdbc.version}
                       
                   
                   
                        ${db.driver}
                        ${db.url}
                        ${db.user}
                        ${db.password}
                        ${db.skip.oracle.dropFirst}
                   
                   
                       
                            drop-oracle-specific-schema
                            pre-integration-test
                           
                                execute
                           
                           
                                /
                                row
                               
                                    declare
                                      type mv_names_t is table of all_mviews.mview_name%type index by pls_integer;
                                      mview_names mv_names_t;
                                    begin
                                      select mview_name
                                      bulk collect into mview_names
                                      from all_mviews;
  3.                                   for indx in 1…mview_names.count loop
                                        execute immediate 'drop materialized view ’ || mview_names(indx);
                                      end loop;
                                    end;
                                    /
                               
                           
                       
                   
               
                …
           
  4.     …
       
           
                drop-oracle-schema
               
                   
                        liquibase.dropFirst
                        true
                   
               
               
                    <db.skip.oracle.dropFirst>false</db.skip.oracle.dropFirst>
               
           
       
          By the way the editor posting on this site is quite buggy - I had to use my windows VM to reply in IE and even then the rendering was all off. Editor didn’t even appear in chrome and ff on ubuntu.

        3

        I created https://liquibase.jira.com/browse/CORE-1416 to track the feature. It will probably go into the oracle extension since it is fairly oracle specific at this point.


        Nathan