Source code

001/*
002 * This library is part of OpenCms -
003 * the Open Source Content Management System
004 *
005 * Copyright (c) Alkacon Software GmbH & Co. KG (http://www.alkacon.com)
006 *
007 * This library is free software; you can redistribute it and/or
008 * modify it under the terms of the GNU Lesser General Public
009 * License as published by the Free Software Foundation; either
010 * version 2.1 of the License, or (at your option) any later version.
011 *
012 * This library is distributed in the hope that it will be useful,
013 * but WITHOUT ANY WARRANTY; without even the implied warranty of
014 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
015 * Lesser General Public License for more details.
016 *
017 * For further information about Alkacon Software GmbH & Co. KG, please see the
018 * company website: http://www.alkacon.com
019 *
020 * For further information about OpenCms, please see the
021 * project website: http://www.opencms.org
022 *
023 * You should have received a copy of the GNU Lesser General Public
024 * License along with this library; if not, write to the Free Software
025 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
026 */
027
028package org.opencms.setup.db.update6to7;
029
030import org.opencms.setup.CmsSetupDBWrapper;
031import org.opencms.setup.CmsSetupDb;
032import org.opencms.setup.db.A_CmsUpdateDBPart;
033
034import java.io.IOException;
035import java.sql.SQLException;
036import java.util.ArrayList;
037import java.util.Arrays;
038import java.util.Collections;
039import java.util.HashMap;
040import java.util.Iterator;
041import java.util.List;
042
043/**
044 * This class makes the remaining changes to some tables in order to update them.<p>
045 *
046 * The following tables will be altered
047 *
048 * CMS_ONLINE/OFFLINE_PROPERTYDEF   Add the TYPE column
049 * CMS_ONLINE/OFFLINE_RESOURCES     Add the columns DATE_CONTENT and RESOURCE_VERSION
050 * CMS_ONLINE/OFFLINE_STRUCTURE     Add the column STRUCTURE_VERSION
051 * CMS_PROJECTS                     Drop the column TASK_ID and change the size for the project name
052 *
053 * @since 7.0.0
054 */
055public class CmsUpdateDBAlterTables extends A_CmsUpdateDBPart {
056
057    /** Constant array with the queries for the CMS_ONLINE_CONTENTS table.<p> */
058    protected static final String[] CMS_OFFLINE_CONTENTS_QUERIES = {"Q_OFFLINE_CONTENTS_DROP_COLUMN"};
059
060    /** Constant ArrayList of the queries of the CMS_OFFLINE table.<p> */
061    protected static final List<String> CMS_OFFLINE_CONTENTS_QUERIES_LIST = Collections.unmodifiableList(
062        Arrays.asList(CMS_OFFLINE_CONTENTS_QUERIES));
063
064    /** Constant array with the ONLINE and OFFLINE PROPERTYDEF tables.<p> */
065    protected static final String[] CMS_PROPERTYDEF = {"CMS_OFFLINE_PROPERTYDEF", "CMS_ONLINE_PROPERTYDEF"};
066
067    /** Constant ArrayList of the two PROPERTYDEF tables.<p> */
068    protected static final List<String> CMS_PROPERTYDEF_LIST = Collections.unmodifiableList(
069        Arrays.asList(CMS_PROPERTYDEF));
070
071    /** Constant array with the ONLINE and OFFLINE RESOURCES tables.<p> */
072    protected static final String[] CMS_RESOURCES = {"CMS_OFFLINE_RESOURCES", "CMS_ONLINE_RESOURCES"};
073
074    /** Constant ArrayList of the two RESOURCES tables.<p> */
075    protected static final List<String> CMS_RESOURCES_LIST = Collections.unmodifiableList(Arrays.asList(CMS_RESOURCES));
076
077    /** Constant array with the ONLINE and OFFLINE STRUCTURE tables.<p> */
078    protected static final String[] CMS_STRUCTURE = {"CMS_OFFLINE_STRUCTURE", "CMS_ONLINE_STRUCTURE"};
079
080    /** Constant ArrayList of the two PROPERTYDEF tables.<p> */
081    protected static final List<String> CMS_STRUCTURE_LIST = Collections.unmodifiableList(Arrays.asList(CMS_STRUCTURE));
082
083    /** Constant for the column CONTENT_ID of the table CMS_OFFLINE_CONTENTS.<p> */
084    protected static final String COLUMN_CMS_OFFLINE_CONTENTS_CONTENT_ID = "CONTENT_ID";
085
086    /** Constant for the column PROPERTYDEF_TYPE of the PROPERTYDEF tables.<p> */
087    protected static final String COLUMN_CMS_PROPERTYDEF_TYPE = "PROPERTYDEF_TYPE";
088
089    /** Constant for the column STRUCTURE_VERSION in the STRUCTURE tables.<p> */
090    protected static final String COLUMN_CMS_STRUCTURE_STRUCTURE_VERSION = "STRUCTURE_VERSION";
091
092    /** Constant for the column PROJECT_NAME of the CMS_PROJECTS table.<p> */
093    protected static final String COLUMN_PROJECTS_PROJECT_NAME = "PROJECT_NAME";
094
095    /** Constant for the column TASK_ID of the CMS_PROJECTS table.<p> */
096    protected static final String COLUMN_PROJECTS_TASK_ID = "TASK_ID";
097
098    /** Constant for the new column DATE_CONTENT of the CMS_RESOURCES tables.<p> */
099    protected static final String COLUMN_RESOURCES_DATE_CONTENT = "DATE_CONTENT";
100
101    /** Constant for the new column RESOURCE_VERSION of the CMS_RESOURCES tables.<p> */
102    protected static final String COLUMN_RESOURCES_RESOURCE_VERSION = "RESOURCE_VERSION";
103
104    /** Constant for the sql replacement of the tablename.<p> */
105    protected static final String REPLACEMENT_TABLENAME = "${tablename}";
106
107    /** Constant for the table name CMS_OFFLINE_CONTENTS.<p> */
108    protected static final String TABLE_CMS_OFFLINE_CONTENTS = "CMS_OFFLINE_CONTENTS";
109
110    /** Constant for the table name CMS_PROJECTS.<p> */
111    protected static final String TABLE_CMS_PROJECTS = "CMS_PROJECTS";
112
113    /** Constant for the sql query to change the colum PROJECT_NAME.<p> */
114    private static final String QUERY_CMS_PROJECTS_CHANGE_PROJECT_NAME = "Q_CMS_PROJECTS_CHANGE_PROJECT_NAME_SIZE";
115
116    /** Constant for the sql query to drop the TASK_ID from the CMS_PROJECTS table.<p> */
117    private static final String QUERY_CMS_PROJECTS_DROP_TASK_ID = "Q_CMS_PROJECTS_DROP_TASK_ID";
118
119    /** Constant for the sql query to change the colum PROJECT_NAME.<p> */
120    private static final String QUERY_CMS_PROJECTS_UPDATE_PROJECT_FLAGS = "Q_CMS_PROJECTS_UPDATE_PROJECT_FLAGS";
121
122    /** Constant for the sql query to add the STRUCTURE_VERSION column to the STRUCTURE tables.<p> */
123    private static final String QUERY_CMS_STRUCTURE_ADD_STRUCTURE_VERSION = "Q_CMS_STRUCTURE_ADD_STRUCTURE_VERSION";
124
125    /** Constant for the SQL query properties.<p> */
126    private static final String QUERY_PROPERTY_FILE = "cms_alter_remaining_queries.properties";
127
128    /** Constant for the sql query to add the PROPERTYDEF_TYPE to the PROPERTYDEF tables.<p> */
129    private static final String QUERY_PROPERTYDEF_TYPE = "Q_CMS_PROPERTYDEF";
130
131    /** Constant for the sql query to select the correct resource versions.<p> */
132    private static final String QUERY_SELECT_CMS_RESOURCE_VERSION = "Q_SELECT_CMS_RESOURCE_VERSION";
133
134    /** Constant for the sql query to select the correct structure versions.<p> */
135    private static final String QUERY_SELECT_CMS_STRUCTURE_VERSION = "Q_SELECT_CMS_STRUCTURE_VERSION";
136
137    /** Constant for the sql query to update the resource version in each row.<p> */
138    private static final String QUERY_UPDATE_RESOURCE_VERSION = "Q_UPDATE_RESOURCE_VERSION";
139
140    /** Constant for the sql query to add the DATE_CONTENT column to the CMS_RESOURCES tables.<p> */
141    private static final String QUERY_UPDATE_RESOURCES_DATE_CONTENT = "Q_UPDATE_RESOURCES_DATE_CONTENT";
142
143    /** Constant for the sql query to add the RESOURCE_VERSIOn to the CMS_RESOURCES tables.<p> */
144    private static final String QUERY_UPDATE_RESOURCES_RESOURCE_VERSION = "Q_UPDATE_RESOURCES_RESOURCE_VERSION";
145
146    /** Constant for the sql query to update the structure version in each row.<p> */
147    private static final String QUERY_UPDATE_STRUCTURE_VERSION = "Q_UPDATE_STRUCTURE_VERSION";
148
149    /** Constant for the sql query to initialize the structure version in each row.<p> */
150    private static final String QUERY_SET_STRUCTURE_VERSION = "Q_SET_STRUCTURE_VERSION";
151
152    /** Constant for the sql query to initialize the resource version in each row.<p> */
153    private static final String QUERY_SET_RESOURCES_VERSION = "Q_SET_RESOURCES_VERSION";
154
155    /**
156     * Default constructor.<p>
157     *
158     * @throws IOException if the default sql queries property file could not be read
159     */
160    public CmsUpdateDBAlterTables()
161    throws IOException {
162
163        super();
164        loadQueryProperties(getPropertyFileLocation() + QUERY_PROPERTY_FILE);
165    }
166
167    /**
168     * @see org.opencms.setup.db.A_CmsUpdateDBPart#internalExecute(org.opencms.setup.CmsSetupDb)
169     */
170    @Override
171    protected void internalExecute(CmsSetupDb dbCon) throws SQLException {
172
173        System.out.println(new Exception().getStackTrace()[0].toString());
174        // Update the CMS_OFFLINE_CONTENTS table
175        // drop column content_id
176        if (dbCon.hasTableOrColumn(TABLE_CMS_OFFLINE_CONTENTS, COLUMN_CMS_OFFLINE_CONTENTS_CONTENT_ID)) {
177            for (Iterator<String> it = CMS_OFFLINE_CONTENTS_QUERIES_LIST.iterator(); it.hasNext();) {
178                String query = readQuery(it.next());
179                dbCon.updateSqlStatement(query, null, null);
180            }
181        } else {
182            System.out.println(
183                "no column " + COLUMN_CMS_OFFLINE_CONTENTS_CONTENT_ID + " in table " + TABLE_CMS_OFFLINE_CONTENTS);
184        }
185
186        // Update the CMS_ONLINE/OFFLINE_PROPERTYDEF tables
187        // Add the column PROPERTYDEF_TYPE
188        for (Iterator<String> it = CMS_PROPERTYDEF_LIST.iterator(); it.hasNext();) {
189            String table = it.next();
190            if (!dbCon.hasTableOrColumn(table, COLUMN_CMS_PROPERTYDEF_TYPE)) {
191                String query = readQuery(QUERY_PROPERTYDEF_TYPE);
192                HashMap<String, String> replacer = new HashMap<String, String>();
193                replacer.put(REPLACEMENT_TABLENAME, table);
194                dbCon.updateSqlStatement(query, replacer, null);
195                replacer.clear();
196            } else {
197                System.out.println("column " + COLUMN_CMS_PROPERTYDEF_TYPE + " in table " + table + " already exists");
198            }
199        }
200
201        // Update the ONLINE/OFFLINE_STRUCTURE
202        // Add the STRUCTURE_VERSION
203        for (Iterator<String> it = CMS_STRUCTURE_LIST.iterator(); it.hasNext();) {
204            String table = it.next();
205            // Add the column if needed
206            if (!dbCon.hasTableOrColumn(table, COLUMN_CMS_STRUCTURE_STRUCTURE_VERSION)) {
207                String addColumn = readQuery(QUERY_CMS_STRUCTURE_ADD_STRUCTURE_VERSION);
208                // Add the column
209                HashMap<String, String> replacer = new HashMap<String, String>();
210                replacer.put(REPLACEMENT_TABLENAME, table);
211                dbCon.updateSqlStatement(addColumn, replacer, null);
212
213                // initialize the STRUCTURE_VERSION column
214                String initStructureVersion = readQuery(QUERY_SET_STRUCTURE_VERSION);
215                dbCon.updateSqlStatement(initStructureVersion, replacer, null);
216
217                // Update the entries of the newly created column
218                String structureVersion = readQuery(QUERY_SELECT_CMS_STRUCTURE_VERSION);
219                CmsSetupDBWrapper db = null;
220                try {
221                    db = dbCon.executeSqlStatement(structureVersion, replacer);
222                    // update each row
223                    while (db.getResultSet().next()) {
224                        String updateQuery = readQuery(QUERY_UPDATE_STRUCTURE_VERSION);
225                        String structureId = db.getResultSet().getString("STRUCTURE_ID");
226                        int version = db.getResultSet().getInt("STRUCTURE_VERSION");
227                        List<Object> params = new ArrayList<Object>();
228                        params.add(Integer.valueOf(version)); // add the version
229                        params.add(structureId);
230                        dbCon.updateSqlStatement(updateQuery, replacer, params);
231                    }
232                } finally {
233                    if (db != null) {
234                        db.close();
235                    }
236                }
237            } else {
238                System.out.println(
239                    "column " + COLUMN_CMS_STRUCTURE_STRUCTURE_VERSION + " in table " + table + " already exists");
240            }
241        } // end update structure_version
242
243        // Drop the TASK_ID column from CMS_PROJECTS
244        if (dbCon.hasTableOrColumn(TABLE_CMS_PROJECTS, COLUMN_PROJECTS_TASK_ID)) {
245            String dropTaskId = readQuery(QUERY_CMS_PROJECTS_DROP_TASK_ID);
246            dbCon.updateSqlStatement(dropTaskId, null, null);
247        } else {
248            System.out.println("no column " + COLUMN_PROJECTS_TASK_ID + " in table " + TABLE_CMS_PROJECTS);
249        }
250
251        // Change the size of the project names
252        if (dbCon.hasTableOrColumn(TABLE_CMS_PROJECTS, COLUMN_PROJECTS_PROJECT_NAME)) {
253            String changeProjectName = readQuery(QUERY_CMS_PROJECTS_CHANGE_PROJECT_NAME);
254            dbCon.updateSqlStatement(changeProjectName, null, null);
255        } else {
256            System.out.println("no column " + COLUMN_PROJECTS_PROJECT_NAME + " in table " + TABLE_CMS_PROJECTS);
257        }
258
259        // Update project flags for temporary projects
260        if (dbCon.hasTableOrColumn(TABLE_CMS_PROJECTS, null)) {
261            String updateProjectFlags = readQuery(QUERY_CMS_PROJECTS_UPDATE_PROJECT_FLAGS);
262            dbCon.updateSqlStatement(updateProjectFlags, null, null);
263        } else {
264            System.out.println("table " + TABLE_CMS_PROJECTS + " does not exists");
265        }
266
267        // Update CMS_RESOURCES tables
268        for (Iterator<String> it = CMS_RESOURCES_LIST.iterator(); it.hasNext();) {
269            String table = it.next();
270            HashMap<String, String> replacer = new HashMap<String, String>();
271            replacer.put(REPLACEMENT_TABLENAME, table);
272            if (!dbCon.hasTableOrColumn(table, COLUMN_RESOURCES_DATE_CONTENT)) {
273                String addDateContent = readQuery(QUERY_UPDATE_RESOURCES_DATE_CONTENT);
274                // add the DATE_CONTENT column
275                dbCon.updateSqlStatement(addDateContent, replacer, null);
276            } else {
277                System.out.println(
278                    "column " + COLUMN_RESOURCES_DATE_CONTENT + " in table " + table + " already exists");
279            }
280
281            if (!dbCon.hasTableOrColumn(table, COLUMN_RESOURCES_RESOURCE_VERSION)) {
282                // add the RESOURCE_VERSION column
283                String addResourceVersion = readQuery(QUERY_UPDATE_RESOURCES_RESOURCE_VERSION);
284                dbCon.updateSqlStatement(addResourceVersion, replacer, null);
285
286                // initialize the RESOURCE_VERSION column
287                String initResourceVersion = readQuery(QUERY_SET_RESOURCES_VERSION);
288                dbCon.updateSqlStatement(initResourceVersion, replacer, null);
289
290                // Update the entries of the newly created column
291                String resourceVersion = readQuery(QUERY_SELECT_CMS_RESOURCE_VERSION);
292                CmsSetupDBWrapper db = null;
293                try {
294                    db = dbCon.executeSqlStatement(resourceVersion, replacer);
295                    // update each row
296                    while (db.getResultSet().next()) {
297                        String updateQuery = readQuery(QUERY_UPDATE_RESOURCE_VERSION);
298                        String resourceId = db.getResultSet().getString("RESOURCE_ID");
299                        int version = db.getResultSet().getInt("RESOURCE_VERSION");
300                        List<Object> params = new ArrayList<Object>();
301                        params.add(Integer.valueOf(version)); // add the version
302                        params.add(resourceId);
303                        dbCon.updateSqlStatement(updateQuery, replacer, params);
304                    }
305                } finally {
306                    if (db != null) {
307                        db.close();
308                    }
309                }
310            } else {
311                System.out.println(
312                    "column " + COLUMN_RESOURCES_RESOURCE_VERSION + " in table " + table + " already exists");
313            }
314        }
315    }
316}