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.file.CmsProject;
031import org.opencms.security.CmsOrganizationalUnit;
032import org.opencms.setup.CmsSetupDBWrapper;
033import org.opencms.setup.CmsSetupDb;
034import org.opencms.setup.db.A_CmsUpdateDBPart;
035import org.opencms.util.CmsUUID;
036
037import java.io.IOException;
038import java.sql.Date;
039import java.sql.ResultSetMetaData;
040import java.sql.SQLException;
041import java.util.ArrayList;
042import java.util.Arrays;
043import java.util.Collections;
044import java.util.HashMap;
045import java.util.Iterator;
046import java.util.List;
047import java.util.Map;
048
049/**
050 * This class updates the project ids from integer values to CmsUUIDs in all existing database tables.<p>
051 *
052 * It creates new UUIDs for each existing project and stores it into a temporary table.<p>
053 *
054 * For each table using a project id a new column for the UUID is added and the according data is transferred.<p>
055 * After that the original indexes and the column for the project id index is dropped and the new column with the
056 * project uuid becomes the primary key.<p>
057 *
058 * @since 7.0.0
059 */
060public class CmsUpdateDBProjectId extends A_CmsUpdateDBPart {
061
062    /** Constant for the sql column PROJECT_ID.<p> */
063    protected static final String COLUMN_PROJECT_ID = "PROJECT_ID";
064
065    /** Constant for the sql query to use the column PROJECT_LASTMODIFIED.<p> */
066    protected static final String COLUMN_PROJECT_LASTMODIFIED = "PROJECT_LASTMODIFIED";
067
068    /** Constant for the sql column PROJECT_UUID.<p> */
069    protected static final String COLUMN_PROJECT_UUID = "PROJECT_UUID";
070
071    /** Constant for the sql column TEMP_PROJECT_UUID.<p> */
072    protected static final String COLUMN_TEMP_PROJECT_UUID = "TEMP_PROJECT_UUID";
073
074    /** Constant for the table name of the CMS_HISTORY_PROJECTS table.<p> */
075    protected static final String HISTORY_PROJECTS_TABLE = "CMS_HISTORY_PROJECTS";
076
077    /** Constant for the sql query to add a new primary key.<p> */
078    protected static final String QUERY_ADD_PRIMARY_KEY = "Q_ADD_PRIMARY_KEY";
079
080    /** Constant for the sql query to add a new column to the table.<p> */
081    protected static final String QUERY_ADD_TEMP_UUID_COLUMN = "Q_ADD_COLUMN";
082
083    /** Constant for the sql query to create the new CMS_HISTORY_PROJECTS table.<p> */
084    protected static final String QUERY_CREATE_HISTORY_PROJECTS_TABLE = "Q_CREATE_HISTORY_PROJECTS_TABLE";
085
086    /** Constant for the sql query to create the temporary table.<p> */
087    protected static final String QUERY_CREATE_TEMP_TABLE_UUIDS = "Q_CREATE_TEMPORARY_TABLE_UUIDS";
088
089    /** Constant for the sql query to describe the given table.<p> */
090    protected static final String QUERY_DESCRIBE_TABLE = "Q_DESCRIBE_TABLE";
091
092    /** Constant for the sql query to read max publish tag.<p> */
093    protected static final String QUERY_READ_MAX_PUBTAG = "Q_READ_MAX_PUBTAG";
094
095    /** Constant for the replacement in the SQL query for the columnname.<p> */
096    protected static final String REPLACEMENT_COLUMN = "${column}";
097
098    /** Constant for the replacement in the SQL query for the new columnname.<p> */
099    protected static final String REPLACEMENT_NEW_COLUMN = "${newcolumn}";
100
101    /** Constant for the replacement in the SQL query for old id to update.<p> */
102    protected static final String REPLACEMENT_OLDID = "${oldid}";
103
104    /** Constant for the replacement in the SQL query for the primary key.<p> */
105    protected static final String REPLACEMENT_PRIMARY_KEY = "${primarykeycolumn}";
106
107    /** Constant for the replacement in the SQL query for the tablename.<p> */
108    protected static final String REPLACEMENT_TABLENAME = "${tablename}";
109
110    /** Array of the online and offline resources tables.<p> */
111    protected static final String[] RESOURCE_TABLES = {"CMS_OFFLINE_RESOURCES", "CMS_ONLINE_RESOURCES"};
112
113    /** Arraylist for the online and offline resources tables that shall be updated.<p> */
114    protected static final List<String> RESOURCES_TABLES_LIST = Collections.unmodifiableList(
115        Arrays.asList(RESOURCE_TABLES));
116
117    /** Array of the tables that are to be updated.<p> */
118    protected static final String[] TABLES = {
119        "CMS_OFFLINE_RESOURCES",
120        "CMS_ONLINE_RESOURCES",
121        "CMS_PROJECTRESOURCES",
122        "CMS_PROJECTS"};
123
124    /** Arraylist for the tables that shall be updated.<p> */
125    protected static final List<String> TABLES_LIST = Collections.unmodifiableList(Arrays.asList(TABLES));
126
127    /** Constant for the temporary UUID column in the tables.<p> */
128    protected static final String TEMP_UUID_COLUMN = "TEMP_PROJECT_UUID";
129
130    /** Constant for the name of temporary table containing the project ids and uuids.<p> */
131    protected static final String TEMPORARY_TABLE_NAME = "TEMP_PROJECT_UUIDS";
132
133    /** Constant for the sql primary key of the CMS_PROJECTRESOURCES table.<p> */
134    private static final String COLUMN_PROJECT_ID_RESOURCE_PATH = "PROJECT_ID,RESOURCE_PATH(255)";
135
136    /** Constant for the sql query to drop a given column.<p> */
137    private static final String QUERY_DROP_COLUMN = "Q_DROP_COLUMN";
138
139    /** Constant for the sql query to get the project ids.<p> */
140    private static final String QUERY_GET_PROJECT_IDS = "Q_SELECT_PROJECT_IDS";
141
142    /** Constant for the sql query to get the uuids and project ids.<p> */
143    private static final String QUERY_GET_UUIDS = "Q_SELECT_UUIDS";
144
145    /** Constant for the sql query to insert the data into the CMS_HISTORY_PROJECTS table.<p> */
146    private static final String QUERY_INSERT_CMS_HISTORY_TABLE = "Q_INSERT_CMS_HISTORY_TABLE";
147
148    /** Constant for the sql query to insert a pair of values to the temp table.<p> */
149    private static final String QUERY_INSERT_UUIDS = "Q_INSERT_UUIDS_TEMP_TABLE";
150
151    /** Constant for the SQL query properties.<p> */
152    private static final String QUERY_PROPERTY_FILE = "cms_projectid_queries.properties";
153
154    /** Constant for the sql query to read the id of the administrators group.<p> */
155    private static final String QUERY_READ_ADMIN_GROUP = "Q_READ_ADMIN_GROUP";
156
157    /** Constant for the sql query to read the id of the admin user.<p> */
158    private static final String QUERY_READ_ADMIN_USER = "Q_READ_ADMIN_USER";
159
160    /** Constant for the sql query to add a rename a column in the table.<p> */
161    private static final String QUERY_RENAME_COLUMN = "Q_RENAME_COLUMN";
162
163    /** Constant for the sql query to count the hsitorical projects.<p> */
164    private static final String QUERY_SELECT_COUNT_HISTORY_TABLE = "Q_SELECT_COUNT_HISTORY_TABLE";
165
166    /** Constant for the sql query to select the data from the CMS_BACKUP_PROJECTS table.<p> */
167    private static final String QUERY_SELECT_DATA_FROM_BACKUP_PROJECTS = "Q_SELECT_DATA_FROM_BACKUP_PROJECTS";
168
169    /** Constant for the sql query to transfer the new uuids to the temporary column.<p> */
170    private static final String QUERY_TRANSFER_UUID = "Q_TRANSFER_UUID";
171
172    /** Constant for the sql query to repair lost project ids.<p> */
173    private static final String QUERY_UPDATE_NULL_PROJECTID = "Q_UPDATE_NULL_PROJECTID";
174
175    /**
176     * Constructor.<p>
177     *
178     * @throws IOException if the query properties cannot be read
179     */
180    public CmsUpdateDBProjectId()
181    throws IOException {
182
183        super();
184        loadQueryProperties(getPropertyFileLocation() + QUERY_PROPERTY_FILE);
185    }
186
187    /**
188     * Adds a new primary key to the given table.<p>
189     *
190     * @param dbCon the db connection interface
191     * @param tablename the table to add the primary key to
192     * @param primaryKey the new primary key
193     *
194     * @throws SQLException if something goes wrong
195     */
196    protected void addPrimaryKey(CmsSetupDb dbCon, String tablename, String primaryKey) throws SQLException {
197
198        System.out.println(new Exception().getStackTrace()[0].toString());
199        if (dbCon.hasTableOrColumn(tablename, null)) {
200            String query = readQuery(QUERY_ADD_PRIMARY_KEY);
201            Map<String, String> replacer = new HashMap<String, String>();
202            replacer.put(REPLACEMENT_TABLENAME, tablename);
203            replacer.put(REPLACEMENT_PRIMARY_KEY, primaryKey);
204            dbCon.updateSqlStatement(query, replacer, null);
205        } else {
206            System.out.println("table " + tablename + " does not exists");
207        }
208    }
209
210    /**
211     * Adds the new column for the uuids to a table.<p>
212     *
213     * @param dbCon the db connection interface
214     * @param tablename the table to add the column to
215     * @param column the new colum to add
216     *
217     * @throws SQLException if something goes wrong
218     */
219    protected void addUUIDColumnToTable(CmsSetupDb dbCon, String tablename, String column) throws SQLException {
220
221        System.out.println(new Exception().getStackTrace()[0].toString());
222        if (!dbCon.hasTableOrColumn(tablename, column)) {
223            String query = readQuery(QUERY_ADD_TEMP_UUID_COLUMN); // Get the query
224            // if the table is not one of the ONLINE or OFFLINE resources add the new column in the first position
225            if (!RESOURCES_TABLES_LIST.contains(tablename)) {
226                query += " FIRST";
227            }
228            Map<String, String> replacer = new HashMap<String, String>(); // Build the replacements
229            replacer.put(REPLACEMENT_TABLENAME, tablename);
230            replacer.put(REPLACEMENT_COLUMN, column);
231            dbCon.updateSqlStatement(query, replacer, null); // execute the query
232        } else {
233            System.out.println("column " + column + " in table " + tablename + " already exists");
234        }
235    }
236
237    /**
238     * Check if the column type of the project id is incorrect.<p>
239     *
240     * @param type the type of the column from the meta data
241     *
242     * @return true if the type is incorrect
243     */
244    protected boolean checkColumnTypeProjectId(int type) {
245
246        return type == java.sql.Types.INTEGER;
247    }
248
249    /**
250     * Creates the CMS_HISTORY_PROJECTS table if it does not exist yet.<p>
251     *
252     * @param dbCon the db connection interface
253     *
254     * @throws SQLException if soemthing goes wrong
255     */
256    protected void createHistProjectsTable(CmsSetupDb dbCon) throws SQLException {
257
258        System.out.println(new Exception().getStackTrace()[0].toString());
259        if (!dbCon.hasTableOrColumn(HISTORY_PROJECTS_TABLE, null)) {
260            String createStatement = readQuery(QUERY_CREATE_HISTORY_PROJECTS_TABLE);
261            dbCon.updateSqlStatement(createStatement, null, null);
262            transferDataToHistoryTable(dbCon);
263        } else {
264            System.out.println("table " + HISTORY_PROJECTS_TABLE + " already exists");
265        }
266    }
267
268    /**
269     * Creates the temp table for project ids if it does not exist yet.<p>
270     *
271     * @param dbCon the db connection interface
272     *
273     * @throws SQLException if soemthing goes wrong
274     */
275    protected void createTempTable(CmsSetupDb dbCon) throws SQLException {
276
277        System.out.println(new Exception().getStackTrace()[0].toString());
278        if (!dbCon.hasTableOrColumn(TEMPORARY_TABLE_NAME, null)) {
279            String createStatement = readQuery(QUERY_CREATE_TEMP_TABLE_UUIDS);
280            dbCon.updateSqlStatement(createStatement, null, null);
281        } else {
282            System.out.println("table " + TEMPORARY_TABLE_NAME + " already exists");
283        }
284    }
285
286    /**
287     * Returns the columns for the primary key of the project resources table.<p>
288     *
289     * @return the columns for the primary key of the project resources table
290     */
291    protected String getColumnProjectIdResourcePath() {
292
293        return COLUMN_PROJECT_ID_RESOURCE_PATH;
294    }
295
296    /**
297     * @see org.opencms.setup.db.A_CmsUpdateDBPart#internalExecute(org.opencms.setup.CmsSetupDb)
298     */
299    @Override
300    protected void internalExecute(CmsSetupDb dbCon) throws SQLException {
301
302        System.out.println(new Exception().getStackTrace()[0].toString());
303
304        generateUUIDs(dbCon);
305
306        createHistProjectsTable(dbCon);
307
308        Map<String, String> uuids = getUUIDs(dbCon); // Get the UUIDS
309
310        /*
311         * Add the temporary column for the new UUIDs and fill it with data
312         */
313        for (Iterator<String> it = TABLES_LIST.iterator(); it.hasNext();) {
314            String tablename = it.next();
315
316            if (needsUpdating(dbCon, tablename)) {
317                addUUIDColumnToTable(dbCon, tablename, TEMP_UUID_COLUMN);
318                boolean isInResourcesList = RESOURCES_TABLES_LIST.contains(tablename);
319                // Add the new uuids
320                Iterator<Map.Entry<String, String>> entries = uuids.entrySet().iterator();
321                while (entries.hasNext()) {
322                    Map.Entry<String, String> entry = entries.next();
323                    if ((entry.getKey() != null) && (entry.getValue() != null)) {
324                        if (isInResourcesList) {
325                            fillUUIDSColumn(
326                                dbCon,
327                                tablename,
328                                TEMP_UUID_COLUMN,
329                                entry.getValue(),
330                                COLUMN_PROJECT_LASTMODIFIED,
331                                entry.getKey());
332                        } else {
333                            fillUUIDSColumn(
334                                dbCon,
335                                tablename,
336                                TEMP_UUID_COLUMN,
337                                entry.getValue(),
338                                COLUMN_PROJECT_ID,
339                                entry.getKey());
340                        }
341                    }
342                }
343
344                /*
345                 * In this phase the primary keys or indexes are dropped and the old columns containing the
346                 * old project ids are dropped. After that the temporary columns are renamed and the new
347                 * indexes and primary keys are added.
348                 */
349                if (isInResourcesList) {
350                    // fix lost project ids
351                    Map<String, String> replacer = Collections.singletonMap("${tablename}", tablename);
352                    List<Object> params = Collections.<Object> singletonList(CmsUUID.getNullUUID().toString());
353                    String query = readQuery(QUERY_UPDATE_NULL_PROJECTID);
354                    dbCon.updateSqlStatement(query, replacer, params);
355
356                    // Drop the column PROJECT_LASTMODIFIED
357                    dropColumn(dbCon, tablename, COLUMN_PROJECT_LASTMODIFIED);
358                    // rename the column TEMP_PROJECT_UUID to PROJECT_LASTMODIFIED
359                    renameColumn(dbCon, tablename, COLUMN_TEMP_PROJECT_UUID, COLUMN_PROJECT_LASTMODIFIED);
360                } else {
361                    // drop the columns
362                    dropColumn(dbCon, tablename, COLUMN_PROJECT_ID);
363
364                    // rename the column TEMP_PROJECT_UUID to PROJECT_ID
365                    renameColumn(dbCon, tablename, COLUMN_TEMP_PROJECT_UUID, COLUMN_PROJECT_ID);
366
367                    // add the new primary key
368                    if (tablename.equals("CMS_PROJECTRESOURCES")) {
369                        addPrimaryKey(dbCon, tablename, getColumnProjectIdResourcePath());
370                    }
371                    if (tablename.equals("CMS_PROJECTS")) {
372                        addPrimaryKey(dbCon, tablename, COLUMN_PROJECT_ID);
373                    }
374                }
375            } else {
376                System.out.println("table " + tablename + " does not need to be updated");
377            }
378        }
379
380        CmsSetupDBWrapper db = null;
381        boolean update = false;
382        try {
383            db = dbCon.executeSqlStatement(readQuery(QUERY_SELECT_COUNT_HISTORY_TABLE), null);
384
385            if (db.getResultSet().next()) {
386                if (db.getResultSet().getInt("COUNT") <= 0) {
387                    update = true;
388                }
389            }
390        } finally {
391            if (db != null) {
392                db.close();
393            }
394        }
395        if (update) {
396            System.out.println("table " + HISTORY_PROJECTS_TABLE + " has no content, create a dummy entry");
397
398            CmsUUID userId = CmsUUID.getNullUUID();
399            try {
400                db = dbCon.executeSqlStatement(readQuery(QUERY_READ_ADMIN_USER), null);
401                if (db.getResultSet().next()) {
402                    userId = new CmsUUID(db.getResultSet().getString(1));
403                }
404            } finally {
405                if (db != null) {
406                    db.close();
407                }
408            }
409            CmsUUID groupId = CmsUUID.getNullUUID();
410            try {
411                db = dbCon.executeSqlStatement(readQuery(QUERY_READ_ADMIN_GROUP), null);
412                if (db.getResultSet().next()) {
413                    groupId = new CmsUUID(db.getResultSet().getString(1));
414                }
415            } finally {
416                if (db != null) {
417                    db.close();
418                }
419            }
420            // read publish tag
421            int pubTag = 1;
422            String query = readQuery(QUERY_READ_MAX_PUBTAG);
423            try {
424                db = dbCon.executeSqlStatement(query, null);
425                if (db.getResultSet().next()) {
426                    pubTag = db.getResultSet().getInt(1);
427                }
428            } finally {
429                if (db != null) {
430                    db.close();
431                }
432            }
433
434            List<Object> params = new ArrayList<Object>();
435            params.add(new CmsUUID().toString());
436            params.add("updateWizardDummyProject");
437            params.add("dummy project just for having an entry");
438            params.add(Integer.valueOf(1));
439            params.add(userId.toString());
440            params.add(groupId.toString());
441            params.add(groupId.toString());
442            params.add(Long.valueOf(System.currentTimeMillis()));
443            params.add(Integer.valueOf(pubTag));
444            params.add(Long.valueOf(System.currentTimeMillis()));
445            params.add(userId.toString());
446            params.add(CmsOrganizationalUnit.SEPARATOR);
447
448            query = readQuery(QUERY_INSERT_CMS_HISTORY_TABLE);
449            dbCon.updateSqlStatement(query, null, params);
450        } else {
451            System.out.println("table " + HISTORY_PROJECTS_TABLE + " has content");
452        }
453    }
454
455    /**
456     * Checks if the given table needs an update of the uuids.<p>
457     *
458     * @param dbCon the db connection interface
459     * @param tablename the table to check
460     *
461     * @return true if the project ids are not yet updated, false if nothing needs to be done
462     *
463     * @throws SQLException if something goes wrong
464     */
465    protected boolean needsUpdating(CmsSetupDb dbCon, String tablename) throws SQLException {
466
467        System.out.println(new Exception().getStackTrace()[0].toString());
468        boolean result = true;
469
470        String query = readQuery(QUERY_DESCRIBE_TABLE);
471        Map<String, String> replacer = new HashMap<String, String>();
472        replacer.put(REPLACEMENT_TABLENAME, tablename);
473        CmsSetupDBWrapper db = null;
474
475        try {
476            db = dbCon.executeSqlStatement(query, replacer);
477
478            while (db.getResultSet().next()) {
479                String fieldname = db.getResultSet().getString("Field");
480                if (fieldname.equals(COLUMN_PROJECT_ID) || fieldname.equals(COLUMN_PROJECT_LASTMODIFIED)) {
481                    try {
482                        String fieldtype = db.getResultSet().getString("Type");
483                        // If the type is varchar then no update needs to be done.
484                        if (fieldtype.indexOf("varchar") > 0) {
485                            return false;
486                        }
487                    } catch (SQLException e) {
488                        result = true;
489                    }
490                }
491            }
492        } finally {
493            if (db != null) {
494                db.close();
495            }
496        }
497        return result;
498    }
499
500    /**
501     * Transfers the data from the CMS_BACKUP_PROJECTS to the CMS_HISTORY_PROJECTS table.<p>
502     *
503     * The datetime type for the column PROJECT_PUBLISHDATE is converted to the new long value.<p>
504     *
505     * @param dbCon the db connection interface
506     *
507     * @throws SQLException if something goes wrong
508     */
509    protected void transferDataToHistoryTable(CmsSetupDb dbCon) throws SQLException {
510
511        if (!isKeepHistory()) {
512            return;
513        }
514        System.out.println(new Exception().getStackTrace()[0].toString());
515        // Get the data from the CMS_BACKUP table
516        String query = readQuery(QUERY_SELECT_DATA_FROM_BACKUP_PROJECTS);
517        CmsSetupDBWrapper db = null;
518        try {
519            db = dbCon.executeSqlStatement(query, null);
520
521            String insertQuery = readQuery(QUERY_INSERT_CMS_HISTORY_TABLE);
522            while (db.getResultSet().next()) {
523                // Add the values to be inserted into the CMS_HISTORY_PROJECTS table
524                List<Object> params = new ArrayList<Object>();
525                params.add(db.getResultSet().getString("PROJECT_UUID"));
526                params.add(db.getResultSet().getString("PROJECT_NAME"));
527                params.add(db.getResultSet().getString("PROJECT_DESCRIPTION"));
528                params.add(Integer.valueOf(db.getResultSet().getInt("PROJECT_TYPE")));
529                params.add(db.getResultSet().getString("USER_ID"));
530                params.add(db.getResultSet().getString("GROUP_ID"));
531                params.add(db.getResultSet().getString("MANAGERGROUP_ID"));
532                params.add(Long.valueOf(db.getResultSet().getLong("DATE_CREATED")));
533                params.add(Integer.valueOf(db.getResultSet().getInt("PUBLISH_TAG")));
534                Date date = db.getResultSet().getDate("PROJECT_PUBLISHDATE");
535                params.add(Long.valueOf(date.getTime()));
536                params.add(db.getResultSet().getString("PROJECT_PUBLISHED_BY"));
537                params.add(db.getResultSet().getString("PROJECT_OU"));
538
539                dbCon.updateSqlStatement(insertQuery, null, params);
540            }
541        } finally {
542            if (db != null) {
543                db.close();
544            }
545        }
546
547    }
548
549    /**
550     * Drops the column of the given table.<p>
551     *
552     * @param dbCon the db connection interface
553     * @param tablename the table in which the columns shall be dropped
554     * @param column the column to drop
555     *
556     * @throws SQLException if something goes wrong
557     */
558    private void dropColumn(CmsSetupDb dbCon, String tablename, String column) throws SQLException {
559
560        System.out.println(new Exception().getStackTrace()[0].toString());
561        if (dbCon.hasTableOrColumn(tablename, column)) {
562            String query = readQuery(QUERY_DROP_COLUMN);
563            Map<String, String> replacer = new HashMap<String, String>();
564            replacer.put(REPLACEMENT_TABLENAME, tablename);
565            replacer.put(REPLACEMENT_COLUMN, column);
566            dbCon.updateSqlStatement(query, replacer, null);
567        } else {
568            System.out.println("column " + column + " in table " + tablename + " does not exist");
569        }
570    }
571
572    /**
573     * Updates the given table with the new UUID value.<p>
574     *
575     * @param dbCon the db connection interface
576     * @param tablename the table to update
577     * @param column the column to update
578     * @param newvalue the new value to insert
579     * @param oldid the old id to compare the old value to
580     * @param tempValue the old value in the temporary table
581     *
582     * @throws SQLException if something goes wrong
583     */
584    private void fillUUIDSColumn(
585        CmsSetupDb dbCon,
586        String tablename,
587        String column,
588        String newvalue,
589        String oldid,
590        String tempValue) throws SQLException {
591
592        System.out.println(new Exception().getStackTrace()[0].toString());
593        if (dbCon.hasTableOrColumn(tablename, column)) {
594            String query = readQuery(QUERY_TRANSFER_UUID);
595            Map<String, String> replacer = new HashMap<String, String>();
596            replacer.put(REPLACEMENT_TABLENAME, tablename);
597            replacer.put(REPLACEMENT_COLUMN, column);
598            replacer.put(REPLACEMENT_OLDID, oldid);
599            List<Object> params = new ArrayList<Object>();
600            params.add(newvalue);
601            params.add(Integer.valueOf(tempValue)); // Change type to integer
602
603            dbCon.updateSqlStatement(query, replacer, params);
604        } else {
605            System.out.println("column " + column + " in table " + tablename + " does not exists");
606        }
607    }
608
609    /**
610     * Generates the new UUIDs for the project ids.<p>
611     * The new uuids are stored in the temporary table.<p>
612     *
613     * @param dbCon the db connection interface
614     *
615     * @throws SQLException if something goes wrong
616     */
617    private void generateUUIDs(CmsSetupDb dbCon) throws SQLException {
618
619        System.out.println(new Exception().getStackTrace()[0].toString());
620        String query = readQuery(QUERY_GET_PROJECT_IDS);
621
622        CmsSetupDBWrapper db = null;
623        try {
624            db = dbCon.executeSqlStatement(query, null);
625            ResultSetMetaData metaData = db.getResultSet().getMetaData();
626            // Check the type of the column if it is integer, then create the new uuids
627            int columnType = metaData.getColumnType(1);
628            if (checkColumnTypeProjectId(columnType)) {
629                if (!dbCon.hasTableOrColumn(TEMPORARY_TABLE_NAME, null)) {
630                    createTempTable(dbCon);
631
632                    String updateQuery = readQuery(QUERY_INSERT_UUIDS);
633                    List<Object> params = new ArrayList<Object>();
634                    // Get the project id and insert it with a new uuid into the temp table
635                    boolean hasNullId = false;
636                    while (db.getResultSet().next()) {
637                        int id = db.getResultSet().getInt("PROJECT_ID");
638                        params.add(Integer.valueOf(id)); // Add the number
639                        CmsUUID uuid = new CmsUUID();
640
641                        // Check for 0 project id
642                        if (id == 0) {
643                            hasNullId = true;
644                            uuid = CmsUUID.getNullUUID();
645                        }
646                        // Check for the online project
647                        if (id == 1) {
648                            uuid = CmsProject.ONLINE_PROJECT_ID;
649                        }
650                        params.add(uuid.toString()); // Add the uuid
651
652                        // Insert the values to the temp table
653                        dbCon.updateSqlStatement(updateQuery, null, params);
654
655                        params.clear();
656                    }
657
658                    // If no project id with value 0 was found
659                    if (!hasNullId) {
660                        params.add(Integer.valueOf(0));
661                        params.add(CmsUUID.getNullUUID().toString());
662                        dbCon.updateSqlStatement(updateQuery, null, params);
663                    }
664                } else {
665                    System.out.println("table " + TEMPORARY_TABLE_NAME + " already exists");
666                }
667            }
668        } finally {
669            if (db != null) {
670                db.close();
671            }
672        }
673    }
674
675    /**
676     * Gets the UUIDs from the temporary table TEMP_CMS_UUIDS.<p>
677     *
678     * @param dbCon the db connection interface
679     *
680     * @return a map with the old project ids and the new uuids
681     *
682     * @throws SQLException if something goes wrong
683     */
684    private Map<String, String> getUUIDs(CmsSetupDb dbCon) throws SQLException {
685
686        System.out.println(new Exception().getStackTrace()[0].toString());
687        Map<String, String> result = new HashMap<String, String>();
688
689        String query = readQuery(QUERY_GET_UUIDS);
690        CmsSetupDBWrapper db = null;
691        try {
692            db = dbCon.executeSqlStatement(query, null);
693            while (db.getResultSet().next()) {
694                String key = Integer.toString(db.getResultSet().getInt(COLUMN_PROJECT_ID));
695                String value = db.getResultSet().getString(COLUMN_PROJECT_UUID);
696
697                result.put(key, value);
698            }
699        } finally {
700            if (db != null) {
701                db.close();
702            }
703        }
704        return result;
705    }
706
707    /**
708     * Renames the column of the given table the new name.<p>
709     *
710     * @param dbCon the db connection interface
711     * @param tablename the table in which the column shall be renamed
712     * @param oldname the old name of the column
713     * @param newname the new name of the column
714     *
715     * @throws SQLException if something goes wrong
716     */
717    private void renameColumn(CmsSetupDb dbCon, String tablename, String oldname, String newname) throws SQLException {
718
719        System.out.println(new Exception().getStackTrace()[0].toString());
720        if (dbCon.hasTableOrColumn(tablename, oldname)) {
721            String query = readQuery(QUERY_RENAME_COLUMN);
722            Map<String, String> replacer = new HashMap<String, String>();
723            replacer.put(REPLACEMENT_TABLENAME, tablename);
724            replacer.put(REPLACEMENT_COLUMN, oldname);
725            replacer.put(REPLACEMENT_NEW_COLUMN, newname);
726
727            dbCon.updateSqlStatement(query, replacer, null);
728        } else {
729            System.out.println("column " + oldname + " in table " + tablename + " not found exists");
730        }
731    }
732}