/* A class to create the provenance schemas.

Copyright (c) 2009-2010 The Regents of the University of California.
All rights reserved.
Permission is hereby granted, without written agreement and without
license or royalty fees, to use, copy, modify, and distribute this
software and its documentation for any purpose, provided that the above
copyright notice and the following two paragraphs appear in all copies
of this software.

IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY
FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES
ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF
THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF
SUCH DAMAGE.

THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES,
INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE
PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF
CALIFORNIA HAS NO OBLIGATION TO PROVIDE MAINTENANCE, SUPPORT, UPDATES,
ENHANCEMENTS, OR MODIFICATIONS.

*/

package org.kepler.provenance.sql;

import java.sql.SQLException;

import org.kepler.util.sql.Column;
import org.kepler.util.sql.DatabaseType;
import org.kepler.util.sql.Schema;
import org.kepler.util.sql.Table;


/** A class to create the provenance schemas.
 *
 * @author Daniel Crawl
 * @version $Id: Schemas.java 34596 2017-08-10 23:33:56Z crawl $
 *
 */
    
public class Schemas
{
    /** This class cannot be instantiated. */
    private Schemas() { }

    /** Get the v6 provenance schema. */
    public static Schema createSchemaV6()
    {
        Schema retval = new Schema(6);

        // the action table
        Table actionTable = retval.createTable("action");
        actionTable.putColumn("id", Column.AUTOINCID);
        actionTable.putColumn("parent_id", Column.INTEGER);
        actionTable.putColumn("time", Column.TIMESTAMP);
        actionTable.putColumn("user", Column.TEXT);

        // the actor table
        Table actorTable = retval.createTable("actor");
        actorTable.putColumn("id", Column.PK_INTEGER, "entity", "id");
        actorTable.putColumn("class", Column.TEXT);

        // the actor_fire table
        Table actorFireTable = retval.createTable("actor_fire");
        actorFireTable.putColumn("actor_id", Column.INTEGER, "actor", "id");
        actorFireTable.putColumn("end_time", Column.TIMESTAMP);
        actorFireTable.putColumn("id", Column.AUTOINCID);
        actorFireTable.putColumn("start_time", Column.TIMESTAMP);
        actorFireTable.putColumn("type", Column.TEXT);
        actorFireTable.putColumn("wf_exec_id", Column.INTEGER, "workflow_exec",
            "id");

        // the director table
        Table directorTable = retval.createTable("director");
        directorTable.putColumn("class", Column.TEXT);
        directorTable.putColumn("id", Column.PK_INTEGER, "entity", "id");

        // the entity table
        Table entityTable = retval.createTable("entity");
        entityTable.putColumn("container_id", Column.INTEGER);
        entityTable.putColumn("id", Column.AUTOINCID);
        entityTable.putColumn("name", Column.TEXT);
        entityTable.putColumn("type", Column.TEXT);
        entityTable.putColumn("workflow_id", Column.INTEGER, "workflow", "id");

        // the link table
        Table linkTable = retval.createTable("link");
        linkTable.putColumn("end_point_1", Column.INTEGER);
        linkTable.putColumn("end_point_2", Column.INTEGER);
        linkTable.putColumn("id", Column.PK_INTEGER, "entity", "id");
        
        // the parameter table
        Table parameterTable = retval.createTable("parameter");
        parameterTable.putColumn("id", Column.PK_INTEGER, "entity", "id");
        parameterTable.putColumn("type", Column.TEXT);
        // value is allowed to be null since oracle treats "" as null
        parameterTable.putColumn("value", Column.NULLABLE_TEXT);

        // the port table
        Table portTable = retval.createTable("port");
        portTable.putColumn("direction", Column.INTEGER);
        portTable.putColumn("id", Column.PK_INTEGER, "entity", "id");
        portTable.putColumn("multiport", Column.BOOLEAN);
        portTable.putColumn("type", Column.TEXT);

        // the relation table
        Table relationTable = retval.createTable("relation");
        relationTable.putColumn("id", Column.PK_INTEGER, "entity", "id");
        relationTable.putColumn("width", Column.INTEGER);

        // the token_flow table
        Table tokenFlowTable = retval.createTable("token_flow");
        tokenFlowTable.putColumn("channel", Column.INTEGER);
        tokenFlowTable.putColumn("data", Column.TEXT);
        tokenFlowTable.putColumn("data_description", Column.INTEGER);
        tokenFlowTable.putColumn("fire_id", Column.INTEGER, "actor_fire", "id");
        tokenFlowTable.putColumn("id", Column.AUTOINCID);
        tokenFlowTable.putColumn("is_read", Column.BOOLEAN);
        tokenFlowTable.putColumn("port_id", Column.INTEGER, "port", "id");
        tokenFlowTable.putColumn("rw_fire_id", Column.INTEGER, "actor_fire",
            "id");
        tokenFlowTable.putColumn("time", Column.TIMESTAMP);

        // the workflow table
        Table workflowTable = retval.createTable("workflow");
        workflowTable.putColumn("action_id", Column.INTEGER, "action", "id");
        workflowTable.putColumn("id", Column.AUTOINCID);
        workflowTable.putColumn("name", Column.TEXT);
        workflowTable.putColumn("version", Column.TEXT);

        // the workflow exec table
        Table workflowExecTable = retval.createTable("workflow_exec");
        workflowExecTable.putColumn("id", Column.AUTOINCID);
        workflowExecTable.putColumn("end_time", Column.TIMESTAMP);
        workflowExecTable.putColumn("start_time", Column.TIMESTAMP);
        workflowExecTable.putColumn("user", Column.TEXT);
        workflowExecTable.putColumn("workflow_id", Column.INTEGER, "workflow",
            "id");

        return retval;
    }
    
    /** Get the v7 provenance schema. */
    public static Schema createSchemaV7()
    {
        Schema retval = createSchemaV6();
        retval.setMajorVersion(7);

        // remove the action table
        retval.removeTable("action");

        // the entity table
        Table entityTable = retval.getTable("entity");
        entityTable.putColumn("deleted", new Column(Column.Type.Boolean, "FALSE"));
        entityTable.putColumn("prev_id", Column.INTEGER);
        entityTable.putColumn("wf_change_id", Column.INTEGER,
            "workflow_change", "id");
        entityTable.removeColumn("workflow_id");
        entityTable.putColumn("wf_id", Column.INTEGER, "workflow", "id");

        // update the workflow table
        Table workflowTable = retval.getTable("workflow");
        workflowTable.removeColumn("action_id");
        workflowTable.removeColumn("version");

        // add the workflow change table
        Table workflowChangeTable = retval.createTable("workflow_change");
        workflowChangeTable.putColumn("id", Column.AUTOINCID);
        workflowChangeTable.putColumn("time", Column.TIMESTAMP);
        workflowChangeTable.putColumn("user", Column.TEXT);
        workflowChangeTable.putColumn("wf_id", Column.INTEGER, "workflow",
            "id");

        // update the workflow exec table
        Table workflowExecTable = retval.getTable("workflow_exec");
        workflowExecTable.removeColumn("workflow_id");
        workflowExecTable.putColumn("wf_id", Column.INTEGER, "workflow", "id");

        return retval;
    }
            
    /** Get the newest v8 provenance schema. */
    public static Schema createSchemaV8()
    {
        return createSchemaV8(-1);
    }
    
    /** Get the V8 provenance schema at a specific minor version.
     *  @param minorVersion the minor version of V8 to return. If -1,
     *  return the latest V8.
     */
    public static Schema createSchemaV8(int minorVersion)
    {
        Schema retval = createSchemaV7();
        retval.setMajorVersion(8);

        // remove type from actor_fire
        Table actorFireTable = retval.getTable("actor_fire");
        actorFireTable.removeColumn("type");

        // add associated file table
        Table assocDataTable = retval.createTable("associated_data");
        assocDataTable.putColumn("data_id", Column.MD5_TEXT, "data", "md5");
        assocDataTable.putColumn("id", Column.AUTOINCID);
        assocDataTable.putColumn("name", Column.TEXT);
        assocDataTable.putColumn("val", Column.TEXT);
        assocDataTable.putColumn("wf_exec_id", Column.INTEGER, "workflow_exec", "id");

        // add the data table
        Table dataTable = retval.createTable("data");
        dataTable.putColumn("contents", Column.BLOB);
        dataTable.putColumn("md5", Column.PK_MD5_TEXT);
        dataTable.putColumn("truncated", Column.BOOLEAN);

        // add the error table
        Table errorTable = retval.createTable("error");
        // we don't always know the source, so this can be null
        errorTable.putColumn("entity_id", Column.NULLABLE_INTEGER);
        errorTable.putColumn("id", Column.AUTOINCID);
        errorTable.putColumn("exec_id", Column.INTEGER, "workflow_exec", "id");
        // text can be null since there may be no message in the 
        // exception.
        errorTable.putColumn("message", Column.NULLABLE_TEXT);

        Table entityTable = retval.getTable("entity");
        // NOTE: these columns can be empty so must be nullable
        // for oracle.
        entityTable.putColumn("display", Column.NULLABLE_TEXT);
        entityTable.putColumn("name", Column.NULLABLE_TEXT);
        entityTable.removeColumn("container_id");
        
        // remove unused link table
        retval.removeTable("link");

        // the parameter exec table
        Table parameterExecTable = retval.createTable("parameter_exec");
        parameterExecTable.putColumn("parameter_id", Column.INTEGER, "parameter", "id");
        parameterExecTable.putColumn("wf_exec_id", Column.INTEGER, "workflow_exec", "id");
        
        // the port table
        Table portTable = retval.getTable("port");
        // remove type since port_event.type records token type.
        portTable.removeColumn("type");

        // the port event table
        Table portEventTable = retval.createTable("port_event");
        portEventTable.putColumn("channel", Column.INTEGER);
        portEventTable.putColumn("data_id", Column.MD5_TEXT, "data", "md5");
        portEventTable.putColumn("fire_id", Column.INTEGER, "actor_fire", "id");
        portEventTable.putColumn("file_id", Column.NULLABLE_MD5_TEXT);
        portEventTable.putColumn("id", Column.AUTOINCID);
        portEventTable.putColumn("port_id", Column.INTEGER, "port", "id");
        portEventTable.putColumn("time", Column.TIMESTAMP);
        portEventTable.putColumn("type", Column.TEXT);
        portEventTable.putColumn("write_event_id", Column.INTEGER);

        // remove unused relation table
        retval.removeTable("relation");

        // add table for tags
        Table tagTable = retval.createTable("tag");
        tagTable.putColumn("id", Column.AUTOINCID);
        tagTable.putColumn("searchstring", Column.TEXT);
        tagTable.putColumn("type", Column.TEXT);
        tagTable.putColumn("urn", Column.TEXT);
        tagTable.putColumn("wf_exec_id", Column.INTEGER, "workflow_exec", "id");
        
        // remove the token_flow table
        retval.removeTable("token_flow");

        // update the workflow table
        Table workflowTable = retval.getTable("workflow");
        // workflow id is now lsid
        workflowTable.putColumn("lsid", Column.TEXT);
        // NOTE: the name column can be empty so must be nullable
        // for oracle.
        workflowTable.putColumn("name", Column.NULLABLE_TEXT);

        // update the workflow change table
        Table workflowChangeTable = retval.getTable("workflow_change");
        workflowChangeTable.putColumn("host_id", Column.TEXT);

        // update the workflow_exec table
        Table workflowExecTable = retval.getTable("workflow_exec");
        workflowExecTable.putColumn("annotation", Column.NULLABLE_TEXT); 
        workflowExecTable.putColumn("host_id", Column.TEXT);
        workflowExecTable.putColumn("lsid", Column.TEXT);
        workflowExecTable.putColumn("wf_contents_id", Column.MD5_TEXT, "data",
            "md5");
        workflowExecTable.putColumn("wf_full_lsid", Column.TEXT);
        workflowExecTable.putColumn("derived_from", Column.NULLABLE_TEXT);

        if(minorVersion == 0)
        {
            return retval;
        }

        // below this line is 8.1
        retval.setMinorVersion(1);

        // NOTE: the default value for module_dependencies is "core" since
        // that was the value used in the kepler 2.0 release.
        
        workflowExecTable.putColumn("module_dependencies", new Column(Column.Type.Varchar, 2000), "core");
        workflowExecTable.putColumn("type", Column.TEXT, "unknown");

        if(minorVersion == 1)
        {
            return retval;
        }

        // below this line is 8.2
        retval.setMinorVersion(2);
        
        // module_dependencies can be large so change the type to unlimited length
        // NOTE: cannot set default value for TEXT_UNLIMITED
        workflowExecTable.putColumn("module_dependencies", Column.TEXT_UNLIMITED);

        // the actor_state table
        Table actorStateTable = retval.createTable("actor_state");
        actorStateTable.putColumn("fire_id", Column.PK_INTEGER, "actor_fire", "id");
        actorStateTable.putColumn("state", Column.BLOB);
        
        // add indexes to entity table
        entityTable.putIndex("entity_query_idx", "name, type, wf_change_id");
        
        if(minorVersion == 2)
        {
            return retval;
        }

        // below this line is 8.3
        retval.setMinorVersion(3);
        
        // add an index to portEvent.write_event_id to speed up queries
        // used by RecordPlayer
        portEventTable.putIndex("write_event_id_idx", "write_event_id");
        
        // allow port_event.data_id to have null values
        portEventTable.putColumn("data_id", Column.NULLABLE_MD5_TEXT, "data", "md5");
        
        // allow port_event.type to have null values
        portEventTable.putColumn("type", Column.NULLABLE_TEXT);
        
        // create port_event.data
        portEventTable.putColumn("data",
            new Column(Column.Type.Varchar, SQLRecordingV8.MAX_PORT_EVENT_DATA_LENGTH, true));

        
        if(minorVersion == 3) {
            return retval;
        }

        // below this line is 8.4
        retval.setMinorVersion(4);
        
        // allow associated_data.data_id to have null values.
        assocDataTable.putColumn("data_id", Column.NULLABLE_MD5_TEXT, "data", "md5");
        
        if(minorVersion == 4) {
            return retval;
        }

        return retval;
    }
    
    /** Verify the schema version in the database matches the version
     *  of the schema parameter. If the version table is not found,
     *  attempt to guess the version based on the schema structure.
     *  If the database appears empty, create the tables in the schema.
     *  If the database schema version is older than the schema
     *  parameter's version, asks if an upgrade is performed. If the
     *  database schema is newer, throws an exception.
     */
    public synchronized static void checkVersion(DatabaseType dbType, Schema schema) throws SQLException
    {
        String dbVersionStr;

        // attempt to get the version in the database
        Integer dbMajorVersion = dbType.getMajorVersion();
        Integer dbMinorVersion = dbType.getMinorVersion();

        // see if version table did not exist
        if(dbMajorVersion == null)
        {
            // guess the version

            // version 6 had the action table
            if(dbType.tableExists(dbType.getTableName("action")))
            {
                dbType.createVersionTable(6, 0);
                dbVersionStr = "6.0";
            }
            // XXX we assume v8 instead of checking for v7.
            else if(dbType.tableExists(dbType.getTableName("data")))
            {
                dbType.createVersionTable(8, 0);
                dbVersionStr = "8.0";
            }
            else
            {
                // create tables
                dbType.createTables(schema);
                dbVersionStr = schema.getVersionString();
            }
        }
        // see if the minor version did not exist.
        else if(dbMinorVersion == null)
        {
            // create the minor version information
            dbType.addMinorVersion(0);
            dbVersionStr = dbMajorVersion + ".0";
        }
        else
        {
            dbVersionStr = dbMajorVersion + "." + dbMinorVersion;
        }

        // see if the database is older than the schema
        if(dbType.hasOlderSchema(schema))
        {
            if(!Upgrade.askAndUpgrade(dbType, schema))
            {
                throw new SQLException("User chose not to upgrade schema.");
            }
        }
        // see if the database if newer than the schema
        else if(dbType.hasNewerSchema(schema))
        {
            throw new SQLException("Database schema (version " +
                dbVersionStr + ") is too new. You must use either an older or empty database.");
        }
    }
}