Source code

001/* A program to upgrade the SQL provenance database.
002
003Copyright (c) 2010 The Regents of the University of California.
004All rights reserved.
005Permission is hereby granted, without written agreement and without
006license or royalty fees, to use, copy, modify, and distribute this
007software and its documentation for any purpose, provided that the above
008copyright notice and the following two paragraphs appear in all copies
009of this software.
010
011IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY
012FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES
013ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF
014THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF
015SUCH DAMAGE.
016
017THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES,
018INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
019MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE
020PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF
021CALIFORNIA HAS NO OBLIGATION TO PROVIDE MAINTENANCE, SUPPORT, UPDATES,
022ENHANCEMENTS, OR MODIFICATIONS.
023
024*/
025package org.kepler.provenance.sql;
026
027import java.sql.SQLException;
028import java.sql.Statement;
029import java.util.List;
030import java.util.Map;
031
032import javax.swing.JOptionPane;
033
034import org.kepler.configuration.ConfigurationManager;
035import org.kepler.configuration.ConfigurationProperty;
036import org.kepler.configuration.ConfigurationUtilities;
037import org.kepler.objectmanager.lsid.KeplerLSID;
038import org.kepler.provenance.QueryException;
039import org.kepler.provenance.Queryable;
040import org.kepler.util.WorkflowRun;
041import org.kepler.util.sql.Column;
042import org.kepler.util.sql.DatabaseFactory;
043import org.kepler.util.sql.DatabaseType;
044import org.kepler.util.sql.Schema;
045import org.kepler.util.sql.Table;
046
047import ptolemy.util.MessageHandler;
048
049/* A program to upgrade the SQL provenance schema.
050 * 
051 * @author Daniel Crawl
052 * @version $Id: Upgrade.java 34596 2017-08-10 23:33:56Z crawl $
053 */
054public class Upgrade
055{
056
057    /** Upgrade the database. */
058    public static void main(String[] args)
059    {
060     
061        Schema schema = Schemas.createSchemaV8();
062                
063        ConfigurationProperty provenanceDefaultsProperty =
064            ConfigurationManager.getInstance().getProperty(
065            ConfigurationManager.getModule("provenance"), "provenance.defaultSettings");
066        Map<String,String> dbParams = ConfigurationUtilities.getPairsMap(provenanceDefaultsProperty);
067        
068        DatabaseType dbType = null;
069        
070        try
071        {
072            dbType = DatabaseFactory.getConnectedDatabaseType(dbParams, "provenance");
073        }
074        catch(SQLException e)
075        {
076            MessageHandler.error("Unable to connect to database.", e);
077            System.exit(-1);
078        }
079                
080        int status = -1;
081        
082        try 
083        {
084            // see if we need to do an upgrade
085            if(dbType.hasOlderSchema(schema))
086            {
087                if(askAndUpgrade(dbType, schema))
088                {
089                    MessageHandler.message("Upgrade successful.");
090                    status = 0;
091                }
092            }
093            else
094            {
095                MessageHandler.message("Provenance database is already up to date!");
096                status = 0;
097            }
098        }
099        catch (Exception e)
100        {
101            MessageHandler.error("Error upgrading database.", e);
102            status = -1;
103        }
104        
105        System.exit(status);
106    }
107    
108    /** Ask the user if she wants to upgrade the provenance database, and
109     *  if the response is yes, upgrade the database.
110     */
111    public static boolean askAndUpgrade(DatabaseType dbType, Schema schema) throws SQLException
112    {
113        String newVersionStr = schema.getVersionString();
114        if(JOptionPane.showConfirmDialog(null,
115                "The provenance database needs to be upgraded to version " +
116                newVersionStr + ".\n" +
117                "Do you want to perform the upgrade now?\n" +
118                "\n" +
119                " If you select No, provenance and reporting will be disabled.", "Upgrade Provenance Database?",
120                JOptionPane.YES_NO_OPTION) == JOptionPane.OK_OPTION)
121        {
122            try 
123            {
124                _upgradeDatabase(dbType, schema);
125                return true;
126            }
127            catch (QueryException e)
128            {
129                throw new SQLException(e);
130            }
131        }
132        return false;
133    }
134    
135    /** Upgrade the database to the latest version. */
136    private static void _upgradeDatabase(DatabaseType dbType, Schema schema)
137        throws SQLException, QueryException
138    {
139        
140        // turn off auto-commit since we want only want to commit after
141        // each upgrade.
142        dbType.setAutoCommit(false);
143        try {            
144            while(dbType.hasOlderSchema(schema))
145            {
146                String versionStr = dbType.getVersionString();
147                if(versionStr.equals("8.0"))
148                {
149                    try {
150                        _upgrade_from_8_0(dbType);
151                        dbType.commit();
152                    } catch(SQLException | QueryException e) {
153                        dbType.rollback();
154                        throw e;
155                    }
156                }
157                else if(versionStr.equals("8.1"))
158                {
159                    try {
160                        _upgrade_from_8_1(dbType);
161                        dbType.commit();
162                    } catch(SQLException | QueryException e) {
163                        dbType.rollback();
164                        throw e;
165                    }
166                }
167                else if(versionStr.equals("8.2"))
168                {
169                    try {
170                        _upgrade_from_8_2(dbType);
171                        dbType.commit();
172                    } catch(SQLException | QueryException e) {
173                        dbType.rollback();
174                        throw e;
175                    }
176                }
177                else if(versionStr.equals("8.3"))
178                {
179                    try {
180                        _upgrade_from_8_3(dbType);
181                        dbType.commit();
182                    } catch(SQLException | QueryException e) {
183                        dbType.rollback();
184                        throw e;
185                    }
186                }
187                else
188                {
189                    MessageHandler.error("Unknown database version " + versionStr);
190                }
191            }
192        } finally {
193            dbType.setAutoCommit(true);
194        }
195    }
196    
197    /** Upgrade the schema from 8.0 to 8.1. */
198    private static void _upgrade_from_8_0(DatabaseType dbType)
199        throws SQLException, QueryException
200    {
201        // the following columns were added in 8.1:
202        //
203        // workflow_exec.module_dependencies
204        // workflow_exec.type
205        
206        Schema schema = Schemas.createSchemaV8(1);
207                
208        Statement statement = dbType.getStatement();
209        try
210        {
211            Table workflowExecTable = schema.getTable("workflow_exec");
212            
213            dbType.createColumn(workflowExecTable.getColumn("module_dependencies"), statement);
214            dbType.createColumn(workflowExecTable.getColumn("type"), statement);
215            
216            // now convert all the unknown types in workflow_exec table
217            String workflowExecStr = dbType.getTableName("workflow_exec");
218            Queryable query = new SQLQueryV8(dbType);
219            
220            List<KeplerLSID> runs = query.getExecutionsForType(WorkflowRun.type.Unknown);
221            for(KeplerLSID runId : runs)
222            {
223                WorkflowRun.type newType;
224                
225                // see if it was an error
226                if(query.isErrorForExecution(runId))
227                {
228                    newType = WorkflowRun.type.Error;
229                }
230                // see if it appears to be an imported run
231                else if(query.isImportedExecution(runId))
232                {
233                    newType = WorkflowRun.type.Imported;
234                }
235                // set the type to be completed.
236                else
237                {
238                    newType = WorkflowRun.type.Complete;
239                }
240                
241                String updateStr = "UPDATE " + workflowExecStr + " SET type = '" +
242                    newType + "' WHERE lsid = '" + runId + "'";
243                
244                statement.execute(updateStr);
245            }
246    
247            // database schema is now at 8.1
248            dbType.updateMinorVersion(1);
249        }
250        finally
251        {
252            if(statement != null)
253            {
254                statement.close();
255            }
256        }
257    }
258    
259    /** Upgrade the schema from 8.1 to 8.2. */
260    private static void _upgrade_from_8_1(DatabaseType dbType)
261        throws SQLException, QueryException
262    {
263        
264        Schema schema81 = Schemas.createSchemaV8(1);
265        Schema schema82 = Schemas.createSchemaV8(2);
266        
267        // change from 8.1 to 8.2:
268        //
269        // workflow_exec.module_dependencies changed to text unlimited
270        // actor state table added
271        // new indexes in entity table
272        
273        // change workflow_exec.module_dependencies type
274        Column newColumn = schema82.getTable("workflow_exec").getColumn("module_dependencies");
275        Column oldColumn = schema81.getTable("workflow_exec").getColumn("module_dependencies");
276        dbType.changeColumnType(newColumn, oldColumn);
277        
278        // add the actor state table            
279        Table actorStateTable = schema82.getTable("actor_state");
280        Schema newSchema = new Schema(0);
281        newSchema.putTable("actor_state", actorStateTable);
282        dbType.createTables(newSchema, false);
283       
284        // add indexes to entity table
285        Table entityTable = schema82.getTable("entity");
286        for(String indexName : entityTable.indexes())
287        {
288            dbType.createIndex(indexName, "entity", entityTable.getIndexColumns(indexName));
289        }
290                        
291        // database schema is now at 8.2
292        dbType.updateMinorVersion(2);
293    }
294    
295    /** Upgrade the schema from 8.2 to 8.3. */
296    private static void _upgrade_from_8_2(DatabaseType dbType)
297        throws SQLException, QueryException
298    {
299        Schema schema83 = Schemas.createSchemaV8(3);
300        
301        String message = 
302            "Upgrading to version 8.3 can take several minutes\n" +
303            "depending on the size of your provenance database.";            
304        JOptionPane.showMessageDialog(null, message, "Warning",
305            JOptionPane.WARNING_MESSAGE);
306        
307        // add index for port_event.write_id
308        Table portEventTable = schema83.getTable("port_event");
309        dbType.createIndex("write_event_id_idx",
310            portEventTable.getName(),
311            "write_event_id");
312        
313        // change port_event.data_id to allow null values
314        dbType.setColumnNull(portEventTable.getColumn("data_id"), "port_event");
315
316        // change port_event.type to allow null values
317        dbType.setColumnNull(portEventTable.getColumn("type"), "port_event");
318
319        // add port_event.data
320        Column data = portEventTable.getColumn("data");
321        dbType.createColumn(data, data.isNullAllowed());
322        
323        
324        // database schema is now at 8.3
325        dbType.updateMinorVersion(3);
326    }
327    
328    /** Upgrade the schema from 8.3 to 8.4. */
329    private static void _upgrade_from_8_3(DatabaseType dbType)
330        throws SQLException, QueryException
331    {
332        Schema schema84 = Schemas.createSchemaV8(4);
333        
334        String message = 
335            "Upgrading to version 8.4 can take several minutes\n" +
336            "depending on the size of your provenance database.";            
337        JOptionPane.showMessageDialog(null, message, "Warning",
338            JOptionPane.WARNING_MESSAGE);
339               
340        // change port_event.data_id to allow null values
341        Table associatedDataTable = schema84.getTable("associated_data");
342        dbType.setColumnNull(associatedDataTable.getColumn("data_id"), "associated_data");
343
344        
345        // database schema is now at 8.4
346        dbType.updateMinorVersion(4);
347    }
348}