/*
 * File   : $Source: /usr/local/cvs/opencms/src-setup/org/opencms/setup/update6to7/generic/CmsUpdateDBAlterTables.java,v $
 * Date   : $Date: 2008-02-27 12:05:34 $
 * Version: $Revision: 1.2 $
 *
 * This library is part of OpenCms -
 * the Open Source Content Management System
 *
 * Copyright (c) 2002 - 2008 Alkacon Software GmbH (http://www.alkacon.com)
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 * Lesser General Public License for more details.
 *
 * For further information about Alkacon Software GmbH, please see the
 * company website: http://www.alkacon.com
 *
 * For further information about OpenCms, please see the
 * project website: http://www.opencms.org
 * 
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 */

package org.opencms.setup.update6to7.generic;

import org.opencms.setup.CmsSetupDBWrapper;
import org.opencms.setup.CmsSetupDb;
import org.opencms.setup.update6to7.A_CmsUpdateDBPart;

import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

/**
 * This class makes the remaining changes to some tables in order to update them.<p>
 * 
 * The following tables will be altered
 * 
 * CMS_ONLINE/OFFLINE_PROPERTYDEF   Add the TYPE column
 * CMS_ONLINE/OFFLINE_RESOURCES     Add the columns DATE_CONTENT and RESOURCE_VERSION
 * CMS_ONLINE/OFFLINE_STRUCTURE     Add the column STRUCTURE_VERSION
 * CMS_PROJECTS                     Drop the column TASK_ID and change the size for the project name
 * 
 * @author Roland Metzler
 * 
 * @version $Revision: 1.2 $ 
 * 
 * @since 7.0.0
 */
public class CmsUpdateDBAlterTables extends A_CmsUpdateDBPart {

    /** Constant array with the queries for the CMS_ONLINE_CONTENTS table.<p> */
    protected static final String[] CMS_OFFLINE_CONTENTS_QUERIES = {"Q_OFFLINE_CONTENTS_DROP_COLUMN"};

    /** Constant ArrayList of the queries of the CMS_OFFLINE table.<p> */
    protected static final List CMS_OFFLINE_CONTENTS_QUERIES_LIST = Collections.unmodifiableList(Arrays.asList(CMS_OFFLINE_CONTENTS_QUERIES));

    /** Constant array with the ONLINE and OFFLINE PROPERTYDEF tables.<p> */
    protected static final String[] CMS_PROPERTYDEF = {"CMS_OFFLINE_PROPERTYDEF", "CMS_ONLINE_PROPERTYDEF"};

    /** Constant ArrayList of the two PROPERTYDEF tables.<p> */
    protected static final List CMS_PROPERTYDEF_LIST = Collections.unmodifiableList(Arrays.asList(CMS_PROPERTYDEF));

    /** Constant array with the ONLINE and OFFLINE RESOURCES tables.<p> */
    protected static final String[] CMS_RESOURCES = {"CMS_OFFLINE_RESOURCES", "CMS_ONLINE_RESOURCES"};

    /** Constant ArrayList of the two RESOURCES tables.<p> */
    protected static final List CMS_RESOURCES_LIST = Collections.unmodifiableList(Arrays.asList(CMS_RESOURCES));

    /** Constant array with the ONLINE and OFFLINE STRUCTURE tables.<p> */
    protected static final String[] CMS_STRUCTURE = {"CMS_OFFLINE_STRUCTURE", "CMS_ONLINE_STRUCTURE"};

    /** Constant ArrayList of the two PROPERTYDEF tables.<p> */
    protected static final List CMS_STRUCTURE_LIST = Collections.unmodifiableList(Arrays.asList(CMS_STRUCTURE));

    /** Constant for the column CONTENT_ID of the table CMS_OFFLINE_CONTENTS.<p> */
    protected static final String COLUMN_CMS_OFFLINE_CONTENTS_CONTENT_ID = "CONTENT_ID";

    /** Constant for the column PROPERTYDEF_TYPE of the PROPERTYDEF tables.<p> */
    protected static final String COLUMN_CMS_PROPERTYDEF_TYPE = "PROPERTYDEF_TYPE";

    /** Constant for the column STRUCTURE_VERSION in the STRUCTURE tables.<p> */
    protected static final String COLUMN_CMS_STRUCTURE_STRUCTURE_VERSION = "STRUCTURE_VERSION";

    /** Constant for the column PROJECT_NAME of the CMS_PROJECTS table.<p> */
    protected static final String COLUMN_PROJECTS_PROJECT_NAME = "PROJECT_NAME";

    /** Constant for the column TASK_ID of the CMS_PROJECTS table.<p> */
    protected static final String COLUMN_PROJECTS_TASK_ID = "TASK_ID";

    /** Constant for the new column DATE_CONTENT of the CMS_RESOURCES tables.<p> */
    protected static final String COLUMN_RESOURCES_DATE_CONTENT = "DATE_CONTENT";

    /** Constant for the new column RESOURCE_VERSION of the CMS_RESOURCES tables.<p> */
    protected static final String COLUMN_RESOURCES_RESOURCE_VERSION = "RESOURCE_VERSION";

    /** Constant for the sql replacement of the tablename.<p> */
    protected static final String REPLACEMENT_TABLENAME = "${tablename}";

    /** Constant for the table name CMS_OFFLINE_CONTENTS.<p> */
    protected static final String TABLE_CMS_OFFLINE_CONTENTS = "CMS_OFFLINE_CONTENTS";

    /** Constant for the table name CMS_PROJECTS.<p> */
    protected static final String TABLE_CMS_PROJECTS = "CMS_PROJECTS";

    /** Constant for the sql query to change the colum PROJECT_NAME.<p> */
    private static final String QUERY_CMS_PROJECTS_CHANGE_PROJECT_NAME = "Q_CMS_PROJECTS_CHANGE_PROJECT_NAME_SIZE";

    /** Constant for the sql query to drop the TASK_ID from the CMS_PROJECTS table.<p> */
    private static final String QUERY_CMS_PROJECTS_DROP_TASK_ID = "Q_CMS_PROJECTS_DROP_TASK_ID";

    /** Constant for the sql query to change the colum PROJECT_NAME.<p> */
    private static final String QUERY_CMS_PROJECTS_UPDATE_PROJECT_FLAGS = "Q_CMS_PROJECTS_UPDATE_PROJECT_FLAGS";

    /** Constant for the sql query to add the STRUCTURE_VERSION column to the STRUCTURE tables.<p> */
    private static final String QUERY_CMS_STRUCTURE_ADD_STRUCTURE_VERSION = "Q_CMS_STRUCTURE_ADD_STRUCTURE_VERSION";

    /** Constant for the SQL query properties.<p> */
    private static final String QUERY_PROPERTY_FILE = "generic/cms_alter_remaining_queries.properties";

    /** Constant for the sql query to add the PROPERTYDEF_TYPE to the PROPERTYDEF tables.<p> */
    private static final String QUERY_PROPERTYDEF_TYPE = "Q_CMS_PROPERTYDEF";

    /** Constant for the sql query to select the correct structure versions.<p> */
    private static final String QUERY_SELECT_CMS_STRUCTURE_VERSION = "Q_SELECT_CMS_STRUCTURE_VERSION";

    /** Constant for the sql query to add the DATE_CONTENT column to the CMS_RESOURCES tables.<p> */
    private static final String QUERY_UPDATE_RESOURCES_DATE_CONTENT = "Q_UPDATE_RESOURCES_DATE_CONTENT";

    /** Constant for the sql query to add the RESOURCE_VERSIOn to the CMS_RESOURCES tables.<p> */
    private static final String QUERY_UPDATE_RESOURCES_RESOURCE_VERSION = "Q_UPDATE_RESOURCES_RESOURCE_VERSION";

    /** Constant for the sql query to update the structure version in each row.<p> */
    private static final String QUERY_UPDATE_STRUCTURE_VERSION = "Q_UPDATE_STRUCTURE_VERSION";

    /**
     * Default constructor.<p>
     * 
     * @throws IOException if the default sql queries property file could not be read 
     */
    public CmsUpdateDBAlterTables()
    throws IOException {

        super();
        loadQueryProperties(QUERY_PROPERTIES_PREFIX + QUERY_PROPERTY_FILE);
    }

    /**
     * @see org.opencms.setup.update6to7.A_CmsUpdateDBPart#internalExecute(org.opencms.setup.CmsSetupDb)
     */
    public void internalExecute(CmsSetupDb dbCon) throws SQLException {

        System.out.println(new Exception().getStackTrace()[0].toString());
        // Update the CMS_OFFLINE_CONTENTS table
        // drop column content_id
        if (dbCon.hasTableOrColumn(TABLE_CMS_OFFLINE_CONTENTS, COLUMN_CMS_OFFLINE_CONTENTS_CONTENT_ID)) {
            for (Iterator it = CMS_OFFLINE_CONTENTS_QUERIES_LIST.iterator(); it.hasNext();) {
                String query = readQuery((String)it.next());
                dbCon.updateSqlStatement(query, null, null);
            }
        } else {
            System.out.println("no column "
                + COLUMN_CMS_OFFLINE_CONTENTS_CONTENT_ID
                + " in table "
                + TABLE_CMS_OFFLINE_CONTENTS);
        }

        // Update the CMS_ONLINE/OFFLINE_PROPERTYDEF tables
        // Add the column PROPERTYDEF_TYPE
        for (Iterator it = CMS_PROPERTYDEF_LIST.iterator(); it.hasNext();) {
            String table = (String)it.next();
            if (!dbCon.hasTableOrColumn(table, COLUMN_CMS_PROPERTYDEF_TYPE)) {
                String query = readQuery(QUERY_PROPERTYDEF_TYPE);
                HashMap replacer = new HashMap();
                replacer.put(REPLACEMENT_TABLENAME, table);
                dbCon.updateSqlStatement(query, replacer, null);
                replacer.clear();
            } else {
                System.out.println("column " + COLUMN_CMS_PROPERTYDEF_TYPE + " in table " + table + " already exists");
            }
        }

        // Update the ONLINE/OFFLINE_STRUCTURE 
        // Add the STRUCTURE_VERSION
        for (Iterator it = CMS_STRUCTURE_LIST.iterator(); it.hasNext();) {
            String table = (String)it.next();
            // Add the column if needed
            if (!dbCon.hasTableOrColumn(table, COLUMN_CMS_STRUCTURE_STRUCTURE_VERSION)) {
                String addColumn = readQuery(QUERY_CMS_STRUCTURE_ADD_STRUCTURE_VERSION);
                // Add the column
                HashMap replacer = new HashMap();
                replacer.put(REPLACEMENT_TABLENAME, table);
                dbCon.updateSqlStatement(addColumn, replacer, null);

                // Update the entries of the newly created column
                String structureVersion = readQuery(QUERY_SELECT_CMS_STRUCTURE_VERSION);
                CmsSetupDBWrapper db = null;
                try {
                    db = dbCon.executeSqlStatement(structureVersion, replacer);
                    // update each row
                    while (db.getResultSet().next()) {
                        String updateQuery = readQuery(QUERY_UPDATE_STRUCTURE_VERSION);
                        String structureId = db.getResultSet().getString("STRUCTURE_ID");
                        int version = db.getResultSet().getInt("STRUCTURE_VERSION");
                        List params = new ArrayList();
                        params.add(new Integer(version)); // add the version
                        params.add(structureId);
                        dbCon.updateSqlStatement(updateQuery, replacer, params);
                    }
                } finally {
                    if (db != null) {
                        db.close();
                    }
                }
            } else {
                System.out.println("column "
                    + COLUMN_CMS_STRUCTURE_STRUCTURE_VERSION
                    + " in table "
                    + table
                    + " already exists");
            }
        } // end update structure_version

        // Drop the TASK_ID column from CMS_PROJECTS
        if (dbCon.hasTableOrColumn(TABLE_CMS_PROJECTS, COLUMN_PROJECTS_TASK_ID)) {
            String dropTaskId = readQuery(QUERY_CMS_PROJECTS_DROP_TASK_ID);
            dbCon.updateSqlStatement(dropTaskId, null, null);
        } else {
            System.out.println("no column " + COLUMN_PROJECTS_TASK_ID + " in table " + TABLE_CMS_PROJECTS);
        }

        // Change the size of the project names
        if (dbCon.hasTableOrColumn(TABLE_CMS_PROJECTS, COLUMN_PROJECTS_PROJECT_NAME)) {
            String changeProjectName = readQuery(QUERY_CMS_PROJECTS_CHANGE_PROJECT_NAME);
            dbCon.updateSqlStatement(changeProjectName, null, null);
        } else {
            System.out.println("no column " + COLUMN_PROJECTS_PROJECT_NAME + " in table " + TABLE_CMS_PROJECTS);
        }

        // Update project flags for temporary projects
        if (dbCon.hasTableOrColumn(TABLE_CMS_PROJECTS, null)) {
            String updateProjectFlags = readQuery(QUERY_CMS_PROJECTS_UPDATE_PROJECT_FLAGS);
            dbCon.updateSqlStatement(updateProjectFlags, null, null);
        } else {
            System.out.println("table " + TABLE_CMS_PROJECTS + " does not exists");
        }

        // Update CMS_GROUPS and add the system roles

        // Update CMS_RESOURCES tables
        for (Iterator it = CMS_RESOURCES_LIST.iterator(); it.hasNext();) {
            String table = (String)it.next();
            HashMap replacer = new HashMap();
            replacer.put(REPLACEMENT_TABLENAME, table);
            if (!dbCon.hasTableOrColumn(table, COLUMN_RESOURCES_DATE_CONTENT)) {
                String addDateContent = readQuery(QUERY_UPDATE_RESOURCES_DATE_CONTENT);
                // add the DATE_CONTENT column
                dbCon.updateSqlStatement(addDateContent, replacer, null);
            } else {
                System.out.println("column " + COLUMN_RESOURCES_DATE_CONTENT + " in table " + table + " already exists");
            }

            if (!dbCon.hasTableOrColumn(table, COLUMN_RESOURCES_RESOURCE_VERSION)) {
                // add the RESOURCE_VERISION column
                String addResourceVersion = readQuery(QUERY_UPDATE_RESOURCES_RESOURCE_VERSION);
                dbCon.updateSqlStatement(addResourceVersion, replacer, null);
            } else {
                System.out.println("column "
                    + COLUMN_RESOURCES_RESOURCE_VERSION
                    + " in table "
                    + table
                    + " already exists");
            }
        }
    }
}