All Downloads are FREE. Search and download functionalities are using the official Maven repository.
  • Log In
  • Register

    • org.opencms.setup.db.update6to7.CmsUpdateDBAlterTables Maven / Gradle / Ivy

    Go to download

    OpenCms is an enterprise-ready, easy to use website content management system based on Java and XML technology. Offering a complete set of features, OpenCms helps content managers worldwide to create and maintain beautiful websites fast and efficiently.

    There is a newer version: 18.0
    Show newest version
    /*
 * This library is part of OpenCms -
 * the Open Source Content Management System
 *
 * Copyright (c) Alkacon Software GmbH & Co. KG (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 & Co. KG, 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.db.update6to7;

import org.opencms.setup.CmsSetupDBWrapper;
import org.opencms.setup.CmsSetupDb;
import org.opencms.setup.db.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.
    
 *
 * 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
 *
 * @since 7.0.0
 */
public class CmsUpdateDBAlterTables extends A_CmsUpdateDBPart {

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

    /** Constant ArrayList of the queries of the CMS_OFFLINE table.
     */
    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.
     */
    protected static final String[] CMS_PROPERTYDEF = {"CMS_OFFLINE_PROPERTYDEF", "CMS_ONLINE_PROPERTYDEF"};

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

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

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

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

    /** Constant ArrayList of the two PROPERTYDEF tables.
     */
    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.
     */
    protected static final String COLUMN_CMS_OFFLINE_CONTENTS_CONTENT_ID = "CONTENT_ID";

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

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

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

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

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

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

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

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

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

    /** Constant for the sql query to change the colum PROJECT_NAME.
     */
    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.
     */
    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.
     */
    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.
     */
    private static final String QUERY_CMS_STRUCTURE_ADD_STRUCTURE_VERSION = "Q_CMS_STRUCTURE_ADD_STRUCTURE_VERSION";

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

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

    /** Constant for the sql query to select the correct resource versions.
     */
    private static final String QUERY_SELECT_CMS_RESOURCE_VERSION = "Q_SELECT_CMS_RESOURCE_VERSION";

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

    /** Constant for the sql query to update the resource version in each row.
     */
    private static final String QUERY_UPDATE_RESOURCE_VERSION = "Q_UPDATE_RESOURCE_VERSION";

    /** Constant for the sql query to add the DATE_CONTENT column to the CMS_RESOURCES tables.
     */
    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.
     */
    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.
     */
    private static final String QUERY_UPDATE_STRUCTURE_VERSION = "Q_UPDATE_STRUCTURE_VERSION";

    /** Constant for the sql query to initialize the structure version in each row.
     */
    private static final String QUERY_SET_STRUCTURE_VERSION = "Q_SET_STRUCTURE_VERSION";

    /** Constant for the sql query to initialize the resource version in each row.
     */
    private static final String QUERY_SET_RESOURCES_VERSION = "Q_SET_RESOURCES_VERSION";

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

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

    /**
     * @see org.opencms.setup.db.A_CmsUpdateDBPart#internalExecute(org.opencms.setup.CmsSetupDb)
     */
    @Override
    protected 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(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 = 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 = 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);

                // initialize the STRUCTURE_VERSION column
                String initStructureVersion = readQuery(QUERY_SET_STRUCTURE_VERSION);
                dbCon.updateSqlStatement(initStructureVersion, 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_RESOURCES tables
        for (Iterator it = CMS_RESOURCES_LIST.iterator(); it.hasNext();) {
            String table = 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_VERSION column
                String addResourceVersion = readQuery(QUERY_UPDATE_RESOURCES_RESOURCE_VERSION);
                dbCon.updateSqlStatement(addResourceVersion, replacer, null);

                // initialize the RESOURCE_VERSION column
                String initResourceVersion = readQuery(QUERY_SET_RESOURCES_VERSION);
                dbCon.updateSqlStatement(initResourceVersion, replacer, null);

                // Update the entries of the newly created column
                String resourceVersion = readQuery(QUERY_SELECT_CMS_RESOURCE_VERSION);
                CmsSetupDBWrapper db = null;
                try {
                    db = dbCon.executeSqlStatement(resourceVersion, replacer);
                    // update each row
                    while (db.getResultSet().next()) {
                        String updateQuery = readQuery(QUERY_UPDATE_RESOURCE_VERSION);
                        String resourceId = db.getResultSet().getString("RESOURCE_ID");
                        int version = db.getResultSet().getInt("RESOURCE_VERSION");
                        List params = new ArrayList();
                        params.add(new Integer(version)); // add the version
                        params.add(resourceId);
                        dbCon.updateSqlStatement(updateQuery, replacer, params);
                    }
                } finally {
                    if (db != null) {
                        db.close();
                    }
                }
            } else {
                System.out.println(
                    "column " + COLUMN_RESOURCES_RESOURCE_VERSION + " in table " + table + " already exists");
            }
        }
    }
}    
    
    
    

    




    
    
    © 2015 - 2024 Weber Informatics LLC | Privacy Policy