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;
029
030import org.opencms.configuration.CmsParameterConfiguration;
031import org.opencms.setup.CmsSetupDBWrapper;
032import org.opencms.setup.CmsSetupDb;
033import org.opencms.setup.CmsUpdateBean;
034import org.opencms.util.CmsStringUtil;
035import org.opencms.util.CmsUUID;
036
037import java.sql.SQLException;
038import java.util.ArrayList;
039import java.util.HashMap;
040import java.util.Iterator;
041import java.util.List;
042import java.util.Map;
043
044/**
045 * This manager controls the update of the database from OpenCms 6 to OpenCms 7.<p>
046 *
047 * @since 7.0.0
048 */
049public class CmsUpdateDBManager {
050
051    /** The database name. */
052    private String m_dbName;
053
054    /** The pools connection data. */
055    private Map<String, Map<String, String>> m_dbPools = new HashMap<String, Map<String, String>>();
056
057    /** The detected mayor version, based on DB structure. */
058    private double m_detectedVersion;
059
060    /** List of xml update plugins. */
061    private List<I_CmsUpdateDBPart> m_plugins;
062
063    /**
064     * Default constructor.<p>
065     */
066    public CmsUpdateDBManager() {
067
068        // no-op
069    }
070
071    /**
072     * Returns the configured jdbc driver for the given pool.<p>
073     *
074     * @param pool the db pool to get the driver for
075     *
076     * @return the driver class name
077     */
078    public String getDbDriver(String pool) {
079
080        return m_dbPools.get(pool).get("driver");
081    }
082
083    /**
084     * Returns the database name.<p>
085     *
086     * @return the database name
087     */
088    public String getDbName() {
089
090        return m_dbName;
091    }
092
093    /**
094     * Returns the configured jdbc url parameters for the given pool.<p>
095     *
096     * @param pool the db pool to get the params for
097     *
098     * @return the jdbc url parameters
099     */
100    public String getDbParams(String pool) {
101
102        return m_dbPools.get(pool).get("params");
103    }
104
105    /**
106     * Returns the configured jdbc connection url for the given pool.<p>
107     *
108     * @param pool the db pool to get the url for
109     *
110     * @return the jdbc connection url
111     */
112    public String getDbUrl(String pool) {
113
114        return m_dbPools.get(pool).get("url");
115    }
116
117    /**
118     * Returns the configured database user for the given pool.<p>
119     *
120     * @param pool the db pool to get the user for
121     *
122     * @return the database user
123     */
124    public String getDbUser(String pool) {
125
126        return m_dbPools.get(pool).get("user");
127    }
128
129    /**
130     * Returns the detected mayor version, based on DB structure.<p>
131     *
132     * @return the detected mayor version
133     */
134    public double getDetectedVersion() {
135
136        if (m_detectedVersion == 0) {
137            needUpdate();
138        }
139        return m_detectedVersion;
140    }
141
142    /**
143     * Returns all configured database pools.<p>
144     *
145     * @return a list of {@link String} objects
146     */
147    public List<String> getPools() {
148
149        return new ArrayList<String>(m_dbPools.keySet());
150    }
151
152    /**
153     * Generates html code for the given db pool.<p>
154     *
155     * @param pool the db pool to generate html for
156     *
157     * @return html code
158     *
159     * @throws Exception if something goes wrong
160     */
161    public String htmlPool(String pool) throws Exception {
162
163        StringBuffer html = new StringBuffer(256);
164
165        html.append("<a href=\"javascript:switchview('").append(pool).append("');\">");
166        html.append(pool).append("</a><br>\n");
167        html.append("\t<div id='").append(pool).append("' style='display: none;'>\n");
168        html.append("\t\t<table border='0'>\n");
169        html.append("\t\t\t<tr><td>JDBC Driver:</td><td>" + getDbDriver(pool) + "</td></tr>\n");
170        html.append("\t\t\t<tr><td>JDBC Connection Url:</td><td>" + getDbUrl(pool) + "</td></tr>\n");
171        html.append("\t\t\t<tr><td>JDBC Connection Url Params:</td><td>" + getDbParams(pool) + "</td></tr>\n");
172        html.append("\t\t\t<tr><td>Database User:</td><td>" + getDbUser(pool) + "</td></tr>\n");
173        html.append("\t\t</table>\n");
174        html.append("\t</div>\n");
175
176        return html.toString();
177    }
178
179    public String htmlPool(String pool, boolean hiddenInfo) {
180
181        if (hiddenInfo) {
182            try {
183                return htmlPool(pool);
184            } catch (Exception e) {
185                //
186            }
187        }
188        StringBuffer html = new StringBuffer(256);
189
190        html.append("<p>");
191        html.append(pool).append("</p><br>\n");
192        html.append("\t<div id='").append(pool);
193        html.append("\t\t<table border='0'>\n");
194        html.append("\t\t\t<tr><td>JDBC Driver:</td><td>" + getDbDriver(pool) + "</td></tr>\n");
195        html.append("\t\t\t<tr><td>JDBC Connection Url:</td><td>" + getDbUrl(pool) + "</td></tr>\n");
196        html.append("\t\t\t<tr><td>JDBC Connection Url Params:</td><td>" + getDbParams(pool) + "</td></tr>\n");
197        html.append("\t\t\t<tr><td>Database User:</td><td>" + getDbUser(pool) + "</td></tr>\n");
198        html.append("\t\t</table>\n");
199        html.append("\t</div>\n");
200
201        return html.toString();
202    }
203
204    /**
205     * Initializes the Update Manager object with the updateBean to get the database connection.<p>
206     *
207     * @param updateBean the update bean with the database connection
208     *
209     * @throws Exception if the setup bean is not initialized
210     */
211    public void initialize(CmsUpdateBean updateBean) throws Exception {
212
213        if (updateBean.isInitialized()) {
214            CmsParameterConfiguration props = updateBean.getProperties();
215
216            // Initialize the CmsUUID generator.
217            CmsUUID.init(props.get("server.ethernet.address"));
218
219            m_dbName = props.get("db.name");
220
221            List<String> pools = CmsStringUtil.splitAsList(props.get("db.pools"), ',');
222            for (String pool : pools) {
223                Map<String, String> data = new HashMap<String, String>();
224                data.put("driver", props.get("db.pool." + pool + ".jdbcDriver"));
225                data.put("url", props.get("db.pool." + pool + ".jdbcUrl"));
226                data.put("params", props.get("db.pool." + pool + ".jdbcUrl.params"));
227                data.put("user", props.get("db.pool." + pool + ".user"));
228                data.put("pwd", props.get("db.pool." + pool + ".password"));
229                data.put("keepHistory", String.valueOf(updateBean.isKeepHistory()));
230                m_dbPools.put(pool, data);
231            }
232        } else {
233            throw new Exception("setup bean not initialized");
234        }
235    }
236
237    /**
238     * Checks if an update is needed.<p>
239     *
240     * @return if an update is needed
241     */
242    public boolean needUpdate() {
243
244        String pool = "default";
245
246        double currentVersion = 8.5;
247        m_detectedVersion = 8.5;
248
249        CmsSetupDb setupDb = new CmsSetupDb(null);
250
251        try {
252            setupDb.setConnection(
253                getDbDriver(pool),
254                getDbUrl(pool),
255                getDbParams(pool),
256                getDbUser(pool),
257                m_dbPools.get(pool).get("pwd"));
258
259            if (!setupDb.hasTableOrColumn("CMS_USERS", "USER_OU")) {
260                m_detectedVersion = 6;
261            } else if (!setupDb.hasTableOrColumn("CMS_ONLINE_URLNAME_MAPPINGS", null)) {
262                m_detectedVersion = 7;
263            } else if (!setupDb.hasTableOrColumn("CMS_USER_PUBLISH_LIST", null)) {
264                m_detectedVersion = 8;
265            }
266        } finally {
267            setupDb.closeConnection();
268        }
269
270        return currentVersion != m_detectedVersion;
271    }
272
273    /**
274     * Updates all database pools.<p>
275     */
276    public void run() {
277
278        try {
279            // add a list of plugins to execute
280            // be sure to use the right order
281            m_plugins = new ArrayList<I_CmsUpdateDBPart>();
282
283            if (getDetectedVersion() < 7) {
284                m_plugins.add(new org.opencms.setup.db.update6to7.CmsUpdateDBDropOldIndexes());
285                m_plugins.add(new org.opencms.setup.db.update6to7.CmsUpdateDBUpdateOU());
286                m_plugins.add(new org.opencms.setup.db.update6to7.CmsUpdateDBCmsUsers());
287                m_plugins.add(new org.opencms.setup.db.update6to7.CmsUpdateDBProjectId());
288                m_plugins.add(new org.opencms.setup.db.update6to7.CmsUpdateDBNewTables());
289                m_plugins.add(new org.opencms.setup.db.update6to7.CmsUpdateDBHistoryTables());
290                m_plugins.add(new org.opencms.setup.db.update6to7.CmsUpdateDBHistoryPrincipals());
291                m_plugins.add(new org.opencms.setup.db.update6to7.CmsUpdateDBDropUnusedTables());
292                m_plugins.add(new org.opencms.setup.db.update6to7.CmsUpdateDBContentTables());
293                m_plugins.add(new org.opencms.setup.db.update6to7.CmsUpdateDBAlterTables());
294                m_plugins.add(new org.opencms.setup.db.update6to7.CmsUpdateDBDropBackupTables());
295                m_plugins.add(new org.opencms.setup.db.update6to7.CmsUpdateDBCreateIndexes7());
296            } else {
297                m_plugins.add(new org.opencms.setup.db.update7to8.CmsUpdateDBNewTables());
298                m_plugins.add(new org.opencms.setup.db.update7to8.CmsUpdatePasswordColumn());
299            }
300        } catch (Throwable t) {
301            t.printStackTrace();
302            throw new RuntimeException(t);
303        }
304
305        Iterator<String> it = getPools().iterator();
306        while (it.hasNext()) {
307            String dbPool = it.next();
308            System.out.println("Starting DB Update for pool " + dbPool + "... ");
309
310            try {
311                updateDatabase(dbPool);
312            } catch (Throwable t) {
313                t.printStackTrace();
314            }
315
316            System.out.println("... DB Update finished for " + dbPool + ".");
317        }
318    }
319
320    /**
321     * Updates the database.<p>
322     *
323     * @param pool the database pool to update
324     */
325    public void updateDatabase(String pool) {
326
327        Map<String, String> dbPoolData = new HashMap<String, String>(m_dbPools.get(pool));
328
329        // display info
330        System.out.println("JDBC Driver:                " + getDbDriver(pool));
331        System.out.println("JDBC Connection Url:        " + getDbUrl(pool));
332        System.out.println("JDBC Connection Url Params: " + getDbParams(pool));
333        System.out.println("Database User:              " + getDbUser(pool));
334
335        // get the db implementation name
336        String dbName = getDbName();
337        String name = null;
338        if (dbName.indexOf("mysql") > -1) {
339            getMySqlEngine(dbPoolData);
340            name = "mysql";
341        } else if (dbName.indexOf("oracle") > -1) {
342            getOracleTablespaces(dbPoolData);
343            name = "oracle";
344        } else if (dbName.indexOf("postgresql") > -1) {
345            getPostgreSqlTablespaces(dbPoolData);
346            name = "postgresql";
347        } else {
348            System.out.println("db " + dbName + " not supported");
349            return;
350        }
351
352        // execute update
353        Iterator<I_CmsUpdateDBPart> it = m_plugins.iterator();
354        while (it.hasNext()) {
355            I_CmsUpdateDBPart updatePart = it.next();
356            I_CmsUpdateDBPart dbUpdater = getInstanceForDb(updatePart, name);
357            if (dbUpdater != null) {
358                dbUpdater.execute(dbPoolData);
359            }
360        }
361    }
362
363    /**
364     * Creates a new instance for the given database and setting the db pool data.<p>
365     *
366     * @param dbUpdater the generic updater part
367     * @param dbName the database to get a new instance for
368     *
369     * @return right instance instance for the given database
370     */
371    protected I_CmsUpdateDBPart getInstanceForDb(I_CmsUpdateDBPart dbUpdater, String dbName) {
372
373        String clazz = dbUpdater.getClass().getName();
374        int pos = clazz.lastIndexOf('.');
375        clazz = clazz.substring(0, pos) + "." + dbName + clazz.substring(pos);
376        try {
377            return (I_CmsUpdateDBPart)Class.forName(clazz).newInstance();
378        } catch (Exception e) {
379            e.printStackTrace();
380            return null;
381        }
382    }
383
384    /**
385     * Retrieves the mysql engine name.<p>
386     *
387     * @param dbPoolData the database pool data
388     */
389    protected void getMySqlEngine(Map<String, String> dbPoolData) {
390
391        String engine = "MYISAM";
392        CmsSetupDb setupDb = new CmsSetupDb(null);
393        CmsSetupDBWrapper db = null;
394        try {
395            setupDb.setConnection(
396                dbPoolData.get("driver"),
397                dbPoolData.get("url"),
398                dbPoolData.get("params"),
399                dbPoolData.get("user"),
400                dbPoolData.get("pwd"));
401
402            db = setupDb.executeSqlStatement("SHOW TABLE STATUS LIKE 'CMS_GROUPS';", null);
403            if (db.getResultSet().next()) {
404                engine = db.getResultSet().getString("Engine").toUpperCase();
405            }
406
407        } catch (SQLException e) {
408            e.printStackTrace();
409        } finally {
410            if (db != null) {
411                db.close();
412            }
413            setupDb.closeConnection();
414        }
415        dbPoolData.put("engine", engine);
416        System.out.println("Table engine:               " + engine);
417    }
418
419    /**
420     * Retrieves the oracle tablespace names.<p>
421     *
422     * @param dbPoolData the database pool data
423     */
424    protected void getOracleTablespaces(Map<String, String> dbPoolData) {
425
426        String dataTablespace = "users";
427        String indexTablespace = "users";
428        CmsSetupDb setupDb = new CmsSetupDb(null);
429
430        try {
431            setupDb.setConnection(
432                dbPoolData.get("driver"),
433                dbPoolData.get("url"),
434                dbPoolData.get("params"),
435                dbPoolData.get("user"),
436                dbPoolData.get("pwd"));
437
438            // read tablespace for data
439            CmsSetupDBWrapper db = null;
440            try {
441                db = setupDb.executeSqlStatement("SELECT DISTINCT tablespace_name FROM user_tables", null);
442                if (db.getResultSet().next()) {
443                    dataTablespace = db.getResultSet().getString(1).toLowerCase();
444                }
445            } finally {
446                if (db != null) {
447                    db.close();
448                }
449            }
450            // read tablespace for indexes
451            try {
452                db = setupDb.executeSqlStatement("SELECT DISTINCT tablespace_name FROM user_indexes", null);
453                if (db.getResultSet().next()) {
454                    indexTablespace = db.getResultSet().getString(1).toLowerCase();
455                }
456            } finally {
457                if (db != null) {
458                    db.close();
459                }
460            }
461        } catch (SQLException e) {
462            e.printStackTrace();
463        } finally {
464            setupDb.closeConnection();
465        }
466
467        dbPoolData.put("indexTablespace", indexTablespace);
468        System.out.println("Index Tablespace:           " + indexTablespace);
469
470        dbPoolData.put("dataTablespace", dataTablespace);
471        System.out.println("Data Tablespace:            " + dataTablespace);
472    }
473
474    /**
475     * Retrieves the postgresql tablespace names.<p>
476     *
477     * @param dbPoolData the database pool data
478     */
479    protected void getPostgreSqlTablespaces(Map<String, String> dbPoolData) {
480
481        String dataTablespace = "pg_default";
482        String indexTablespace = "pg_default";
483        CmsSetupDb setupDb = new CmsSetupDb(null);
484
485        try {
486            setupDb.setConnection(
487                dbPoolData.get("driver"),
488                dbPoolData.get("url"),
489                dbPoolData.get("params"),
490                dbPoolData.get("user"),
491                dbPoolData.get("pwd"));
492
493            // read tablespace for data
494            CmsSetupDBWrapper db = null;
495            try {
496                db = setupDb.executeSqlStatement(
497                    "SELECT DISTINCT pg_tablespace.spcname FROM pg_class, pg_tablespace WHERE pg_class.relname='cms_user' AND pg_class.reltablespace = pg_tablespace.oid",
498                    null);
499                if (db.getResultSet().next()) {
500                    dataTablespace = db.getResultSet().getString(1).toLowerCase();
501                }
502            } finally {
503                if (db != null) {
504                    db.close();
505                }
506            }
507            // read tablespace for indexes
508            try {
509                db = setupDb.executeSqlStatement(
510                    "SELECT DISTINCT pg_tablespace.spcname FROM pg_class, pg_tablespace WHERE pg_class.relname='cms_users_pkey' AND pg_class.reltablespace = pg_tablespace.oid",
511                    null);
512                if (db.getResultSet().next()) {
513                    indexTablespace = db.getResultSet().getString(1).toLowerCase();
514                }
515            } finally {
516                if (db != null) {
517                    db.close();
518                }
519            }
520        } catch (SQLException e) {
521            e.printStackTrace();
522        } finally {
523            setupDb.closeConnection();
524        }
525
526        dbPoolData.put("indexTablespace", indexTablespace);
527        System.out.println("Index Tablespace:           " + indexTablespace);
528
529        dbPoolData.put("dataTablespace", dataTablespace);
530        System.out.println("Data Tablespace:            " + dataTablespace);
531    }
532}