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;
029
030import org.opencms.main.CmsException;
031import org.opencms.main.CmsLog;
032import org.opencms.util.CmsDataTypeUtil;
033import org.opencms.util.CmsStringUtil;
034
035import java.io.File;
036import java.io.FileNotFoundException;
037import java.io.FileReader;
038import java.io.IOException;
039import java.io.LineNumberReader;
040import java.io.Reader;
041import java.io.StringReader;
042import java.sql.Connection;
043import java.sql.DriverManager;
044import java.sql.PreparedStatement;
045import java.sql.ResultSet;
046import java.sql.SQLException;
047import java.sql.Statement;
048import java.util.ArrayList;
049import java.util.Iterator;
050import java.util.List;
051import java.util.Map;
052import java.util.StringTokenizer;
053
054import org.apache.commons.logging.Log;
055
056/**
057 * Helper class to call database setup scripts.<p>
058 *
059 * @since 6.0.0
060 */
061public class CmsSetupDb extends Object {
062
063    /** The folder where to read the setup data from. */
064    public static final String SETUP_DATA_FOLDER = "WEB-INF/setupdata/";
065
066    /** The folder where the setup wizard is located. */
067    public static final String SETUP_FOLDER = CmsSetupBean.FOLDER_SETUP;
068
069    /** The log object for this class. */
070    private static final Log LOG = CmsLog.getLog(CmsSetupDb.class);
071
072    /** The setup base path. */
073    private String m_basePath;
074
075    /** A SQL connection. */
076    private Connection m_con;
077
078    /** A flag signaling if error logging is enabled. */
079    private boolean m_errorLogging;
080
081    /** A list to store error messages. */
082    private List<String> m_errors;
083
084    /**
085     * Creates a new CmsSetupDb object.<p>
086     *
087     * @param basePath the location of the setup scripts
088     */
089    public CmsSetupDb(String basePath) {
090
091        m_errors = new ArrayList<String>();
092        m_basePath = basePath;
093        m_errorLogging = true;
094    }
095
096    /**
097     * Returns an optional warning message if needed, <code>null</code> if not.<p>
098     *
099     * @param db the selected database key
100     *
101     * @return html warning, or <code>null</code> if no warning
102     */
103    public String checkVariables(String db) {
104
105        StringBuffer html = new StringBuffer(512);
106        if (m_con == null) {
107            return null; // prior error, trying to get a connection
108        }
109        Exception exception = null;
110        if (db.equals("mysql")) {
111            String statement = "SELECT @@max_allowed_packet;";
112            Statement stmt = null;
113            ResultSet rs = null;
114            long maxAllowedPacket = 0;
115            try {
116                stmt = m_con.createStatement();
117                rs = stmt.executeQuery(statement);
118                if (rs.next()) {
119                    maxAllowedPacket = rs.getLong(1);
120                }
121            } catch (Exception e) {
122                exception = e;
123            } finally {
124                if (stmt != null) {
125                    try {
126                        stmt.close();
127                    } catch (SQLException e) {
128                        // ignore
129                    }
130                }
131            }
132            if (exception == null) {
133                int megabyte = 1024 * 1024;
134                if (maxAllowedPacket > 0) {
135                    html.append("<p>MySQL system variable <code>'max_allowed_packet'</code> is set to ");
136                    html.append(maxAllowedPacket);
137                    html.append(" Byte (");
138                    html.append((maxAllowedPacket / megabyte) + "MB).</p>\n");
139                }
140                html.append(
141                    "<p>Please note that it will not be possible for OpenCms to handle files bigger than this value in the VFS.</p>\n");
142                int requiredMaxAllowdPacket = 16;
143                if (maxAllowedPacket < (requiredMaxAllowdPacket * megabyte)) {
144                    m_errors.add(
145                        "<p><b>Your <code>'max_allowed_packet'</code> variable is set to less than "
146                            + (requiredMaxAllowdPacket * megabyte)
147                            + " Byte ("
148                            + requiredMaxAllowdPacket
149                            + "MB).</b></p>\n"
150                            + "<p>The required value for running OpenCms is at least "
151                            + requiredMaxAllowdPacket
152                            + "MB."
153                            + "Please change your MySQL configuration (in the <code>my.ini</code> or <code>my.cnf</code> file).</p>\n");
154                }
155            } else {
156                html.append(
157                    "<p><i>OpenCms was not able to detect the value of your <code>'max_allowed_packet'</code> variable.</i></p>\n");
158                html.append(
159                    "<p>Please note that it will not be possible for OpenCms to handle files bigger than this value.</p>\n");
160                html.append(
161                    "<p><b>The recommended value for running OpenCms is 16MB, please set it in your MySQL configuration (in your <code>my.ini</code> or <code>my.cnf</code> file).</b></p>\n");
162                html.append(CmsException.getStackTraceAsString(exception));
163            }
164        }
165        if (html.length() == 0) {
166            return null;
167        }
168        return html.toString();
169    }
170
171    /**
172     * Clears the error messages stored internally.<p>
173     */
174    public void clearErrors() {
175
176        m_errors.clear();
177    }
178
179    /**
180     * Closes the internal connection to the database.<p>
181     */
182    public void closeConnection() {
183
184        try {
185            if (m_con != null) {
186                m_con.close();
187            }
188        } catch (Exception e) {
189            // ignore
190        }
191        m_con = null;
192    }
193
194    /**
195     * Calls the create database script for the given database.<p>
196     *
197     * @param database the name of the database
198     * @param replacer the replacements to perform in the drop script
199     */
200    public void createDatabase(String database, Map<String, String> replacer) {
201
202        m_errorLogging = true;
203        executeSql(database, "create_db.sql", replacer, true);
204    }
205
206    /**
207     * Calls the create database script for the given database.<p>
208     *
209     * @param database the name of the database
210     * @param replacer the replacements to perform in the drop script
211     * @param abortOnError indicates if the script is aborted if an error occurs
212     */
213    public void createDatabase(String database, Map<String, String> replacer, boolean abortOnError) {
214
215        m_errorLogging = true;
216        executeSql(database, "create_db.sql", replacer, abortOnError);
217    }
218
219    /**
220     * Calls the create tables script for the given database.<p>
221     *
222     * @param database the name of the database
223     * @param replacer the replacements to perform in the drop script
224     */
225    public void createTables(String database, Map<String, String> replacer) {
226
227        m_errorLogging = true;
228        executeSql(database, "create_tables.sql", replacer, true);
229    }
230
231    /**
232     * Calls the create tables script for the given database.<p>
233     *
234     * @param database the name of the database
235     * @param replacer the replacements to perform in the drop script
236     * @param abortOnError indicates if the script is aborted if an error occurs
237     */
238    public void createTables(String database, Map<String, String> replacer, boolean abortOnError) {
239
240        m_errorLogging = true;
241        executeSql(database, "create_tables.sql", replacer, abortOnError);
242    }
243
244    /**
245     * Calls the drop script for the given database.
246     *
247     * @param database the name of the database
248     * @param replacer the replacements to perform in the drop script
249     */
250    public void dropDatabase(String database, Map<String, String> replacer) {
251
252        m_errorLogging = true;
253        executeSql(database, "drop_db.sql", replacer, false);
254    }
255
256    /**
257     * Calls the drop script for the given database.
258     *
259     * @param database the name of the database
260     * @param replacer the replacements to perform in the drop script
261     * @param abortOnError indicates if the script is aborted if an error occurs
262     */
263    public void dropDatabase(String database, Map<String, String> replacer, boolean abortOnError) {
264
265        m_errorLogging = true;
266        executeSql(database, "drop_db.sql", replacer, abortOnError);
267    }
268
269    /**
270     * Calls the drop tables script for the given database.<p>
271     *
272     * @param database the name of the database
273     */
274    public void dropTables(String database) {
275
276        m_errorLogging = true;
277        executeSql(database, "drop_tables.sql", null, false);
278    }
279
280    /**
281     * Calls the drop tables script for the given database.<p>
282     *
283     * @param database the name of the database
284     * @param replacer the replacements to perform in the drop script
285     */
286    public void dropTables(String database, Map<String, String> replacer) {
287
288        m_errorLogging = true;
289        executeSql(database, "drop_tables.sql", replacer, false);
290    }
291
292    /**
293     * Calls the drop tables script for the given database.<p>
294     *
295     * @param database the name of the database
296     * @param replacer the replacements to perform in the drop script
297     * @param abortOnError indicates if the script is aborted if an error occurs
298     */
299    public void dropTables(String database, Map<String, String> replacer, boolean abortOnError) {
300
301        m_errorLogging = true;
302        executeSql(database, "drop_tables.sql", replacer, abortOnError);
303    }
304
305    /**
306     * Creates and executes a database statement from a String returning the result set.<p>
307     *
308     * @param query the query to execute
309     * @param replacer the replacements to perform in the script
310     *
311     * @return the result set of the query
312     *
313     * @throws SQLException if something goes wrong
314     */
315    public CmsSetupDBWrapper executeSqlStatement(String query, Map<String, String> replacer) throws SQLException {
316
317        CmsSetupDBWrapper dbwrapper = new CmsSetupDBWrapper(m_con);
318        dbwrapper.createStatement();
319
320        String queryToExecute = query;
321
322        // Check if a map of replacements is given
323        if (replacer != null) {
324            queryToExecute = replaceTokens(query, replacer);
325        }
326        // do the query
327        dbwrapper.excecuteQuery(queryToExecute);
328
329        // return the result
330        return dbwrapper;
331
332    }
333
334    /** Creates and executes a database statement from a String returning the result set.<p>
335     *
336     * @param query the query to execute
337     * @param replacer the replacements to perform in the script
338     * @param params the list of parameters for the statement
339     *
340     * @return the result set of the query
341     *
342     * @throws SQLException if something goes wrong
343     */
344    public CmsSetupDBWrapper executeSqlStatement(String query, Map<String, String> replacer, List<Object> params)
345    throws SQLException {
346
347        CmsSetupDBWrapper dbwrapper = new CmsSetupDBWrapper(m_con);
348
349        String queryToExecute = query;
350
351        // Check if a map of replacements is given
352        if (replacer != null) {
353            queryToExecute = replaceTokens(query, replacer);
354        }
355
356        dbwrapper.createPreparedStatement(queryToExecute, params);
357
358        dbwrapper.excecutePreparedQuery();
359
360        return dbwrapper;
361    }
362
363    /**
364     * Returns the connection.<p>
365     *
366     * @return the connection
367     */
368    public Connection getConnection() {
369
370        return m_con;
371    }
372
373    /**
374     * Returns a Vector of Error messages.<p>
375     *
376     * @return all error messages collected internally
377     */
378    public List<String> getErrors() {
379
380        return m_errors;
381    }
382
383    /**
384     * Checks if the given table, column or combination of both is available in the database in case insensitive way.<P>
385     *
386     * @param table the sought table
387     * @param column the sought column
388     *
389     * @return true if the requested table/column is available, false if not
390     */
391    public boolean hasTableOrColumn(String table, String column) {
392
393        String tableName, columnName;
394        boolean result;
395
396        tableName = table == null ? null : table.toUpperCase();
397        columnName = column == null ? null : column.toUpperCase();
398        result = hasTableOrColumnCaseSensitive(tableName, columnName);
399
400        if (!result) {
401            tableName = table == null ? null : table.toLowerCase();
402            columnName = column == null ? null : column.toLowerCase();
403            result = result || hasTableOrColumnCaseSensitive(tableName, columnName);
404        }
405
406        return result;
407    }
408
409    /**
410     * Checks if the given table, column or combination of both is available in the database in a case sensitive way.<P>
411     *
412     * @param table the sought table
413     * @param column the sought column
414     *
415     * @return true if the requested table/column is available, false if not
416     */
417    public boolean hasTableOrColumnCaseSensitive(String table, String column) {
418
419        boolean result = false;
420        ResultSet set = null;
421
422        try {
423            if (CmsStringUtil.isNotEmptyOrWhitespaceOnly(column)) {
424                // Check if the column is given
425                set = m_con.getMetaData().getColumns(null, null, table, column);
426                if (set.next()) {
427                    String colname = set.getString("COLUMN_NAME");
428                    if (colname.equalsIgnoreCase(column)) {
429                        result = true; // The column is available
430                    }
431                }
432            } else if (CmsStringUtil.isNotEmptyOrWhitespaceOnly(table)) {
433                // Check the table
434                set = m_con.getMetaData().getTables(null, null, table, null);
435                if (set.next()) {
436                    String tablename = set.getString("TABLE_NAME");
437                    if (tablename.equalsIgnoreCase(table)) {
438                        result = true;
439                    }
440                }
441            }
442        } catch (SQLException e) {
443            e.printStackTrace();
444            result = false;
445        } finally {
446            try {
447                if (set != null) {
448                    set.close();
449                }
450            } catch (SQLException e) {
451                e.printStackTrace();
452            }
453        }
454        return result;
455    }
456
457    /**
458     * Checks if internal errors occurred.<p>
459     *
460     * @return true if internal errors occurred
461     */
462    public boolean noErrors() {
463
464        return m_errors.isEmpty();
465    }
466
467    /**
468     * Sets a new internal connection to the database.<p>
469     *
470     * @param conn the connection to use
471     */
472    public void setConnection(Connection conn) {
473
474        m_con = conn;
475    }
476
477    /**
478     * Creates a new internal connection to the database.<p>
479     *
480     * @param DbDriver JDBC driver class name
481     * @param DbConStr JDBC connect URL
482     * @param DbConStrParams JDBC connect URL params, or null
483     * @param DbUser JDBC database user
484     * @param DbPwd JDBC database password
485     */
486    public void setConnection(String DbDriver, String DbConStr, String DbConStrParams, String DbUser, String DbPwd) {
487
488        setConnection(DbDriver, DbConStr, DbConStrParams, DbUser, DbPwd, true);
489    }
490
491    /**
492     * Creates a new internal connection to the database.<p>
493     *
494     * @param DbDriver JDBC driver class name
495     * @param DbConStr JDBC connect URL
496     * @param DbConStrParams JDBC connect URL params, or null
497     * @param DbUser JDBC database user
498     * @param DbPwd JDBC database password
499     * @param logErrors if set to 'true', errors are written to the log file
500     */
501    public void setConnection(
502        String DbDriver,
503        String DbConStr,
504        String DbConStrParams,
505        String DbUser,
506        String DbPwd,
507        boolean logErrors) {
508
509        String jdbcUrl = DbConStr;
510        try {
511            if (DbConStrParams != null) {
512                jdbcUrl += DbConStrParams;
513            }
514            if (CmsStringUtil.isEmptyOrWhitespaceOnly(DbPwd)) {
515                DbPwd = null;
516            }
517            Class.forName(DbDriver).newInstance();
518            m_con = DriverManager.getConnection(jdbcUrl, DbUser, DbPwd);
519            LOG.info("OpenCms setup connection established: " + m_con);
520            LOG.info(" [autocommit: " + m_con.getAutoCommit() + "]");
521        } catch (ClassNotFoundException e) {
522            System.out.println("Class not found exception: " + e);
523            m_errors.add(Messages.get().getBundle().key(Messages.ERR_LOAD_JDBC_DRIVER_1, DbDriver));
524            m_errors.add(CmsException.getStackTraceAsString(e));
525        } catch (Exception e) {
526            if (logErrors) {
527                System.out.println("Exception: " + CmsException.getStackTraceAsString(e));
528            }
529            m_errors.add(Messages.get().getBundle().key(Messages.ERR_DB_CONNECT_1, DbConStr));
530            m_errors.add(CmsException.getStackTraceAsString(e));
531        }
532    }
533
534    /**
535     * Calls an update script.<p>
536     *
537     * @param updateScript the update script code
538     * @param replacers the replacers to use in the script code
539     */
540    public void updateDatabase(String updateScript, Map<String, String> replacers) {
541
542        StringReader reader = new StringReader(updateScript);
543        executeSql(reader, replacers, true);
544    }
545
546    /**
547     * Calls an update script.<p>
548     *
549     * @param updateScript the update script code
550     * @param replacers the replacers to use in the script code
551     * @param abortOnError indicates if the script is aborted if an error occurs
552     */
553    public void updateDatabase(String updateScript, Map<String, String> replacers, boolean abortOnError) {
554
555        StringReader reader = new StringReader(updateScript);
556        executeSql(reader, replacers, abortOnError);
557    }
558
559    /**
560     * Creates and executes a database statment from a String.<p>
561     *
562     * @param query the query to execute
563     * @param replacer the replacements to perform in the script
564     * @param params the list of parameters for the statement
565     *
566     * @return the result set of the query
567     *
568     * @throws SQLException if something goes wrong
569     */
570    public int updateSqlStatement(String query, Map<String, String> replacer, List<Object> params) throws SQLException {
571
572        String queryToExecute = query;
573        // Check if a map of replacements is given
574        if (replacer != null) {
575            queryToExecute = replaceTokens(query, replacer);
576        }
577
578        int result;
579        PreparedStatement stmt = null;
580        stmt = m_con.prepareStatement(queryToExecute);
581        try {
582            // Check the params
583            if (params != null) {
584                for (int i = 0; i < params.size(); i++) {
585                    Object item = params.get(i);
586
587                    // Check if the parameter is a string
588                    if (item instanceof String) {
589                        stmt.setString(i + 1, (String)item);
590                    }
591                    if (item instanceof Integer) {
592                        Integer number = (Integer)item;
593                        stmt.setInt(i + 1, number.intValue());
594                    }
595                    if (item instanceof Long) {
596                        Long longNumber = (Long)item;
597                        stmt.setLong(i + 1, longNumber.longValue());
598                    }
599
600                    // If item is none of types above set the statement to use the bytes
601                    if (!(item instanceof Integer) && !(item instanceof String) && !(item instanceof Long)) {
602                        try {
603                            stmt.setBytes(i + 1, CmsDataTypeUtil.dataSerialize(item));
604                        } catch (IOException e) {
605                            e.printStackTrace();
606                        }
607                    }
608                }
609            }
610
611            if (!queryToExecute.startsWith("UPDATE CMS_ONLINE_STRUCTURE SET STRUCTURE_VERSION")
612                && !queryToExecute.startsWith("UPDATE CMS_OFFLINE_STRUCTURE SET STRUCTURE_VERSION")) {
613                System.out.println("executing query: " + queryToExecute);
614                if ((params != null) && !params.isEmpty()) {
615                    System.out.println("params: " + params);
616                }
617            }
618            result = stmt.executeUpdate();
619        } finally {
620            stmt.close();
621        }
622
623        return result;
624    }
625
626    /**
627     * Internal method to parse and execute a setup script.<p>
628     *
629     * @param inputReader an input stream reader on the setup script
630     * @param replacers the replacements to perform in the script
631     * @param abortOnError if a error occurs this flag indicates if to continue or to abort
632     */
633    private void executeSql(Reader inputReader, Map<String, String> replacers, boolean abortOnError) {
634
635        String statement = "";
636        LineNumberReader reader = null;
637        String line = null;
638
639        // parse the setup script
640        try {
641            reader = new LineNumberReader(inputReader);
642
643            while (true) {
644                line = reader.readLine();
645                if (line == null) {
646                    break;
647                }
648                StringTokenizer st = new StringTokenizer(line);
649
650                while (st.hasMoreTokens()) {
651                    String currentToken = st.nextToken();
652
653                    // comment! Skip rest of the line
654                    if (currentToken.startsWith("#")) {
655                        break;
656                    }
657
658                    // not to be executed
659                    if (currentToken.startsWith("prompt")) {
660                        break;
661                    }
662
663                    // add token to query
664                    statement += " " + currentToken;
665
666                    // query complete (terminated by ';')
667                    if (currentToken.endsWith(";")) {
668                        // cut of ';' at the end
669                        statement = statement.substring(0, (statement.length() - 1));
670
671                        // normal statement, execute it
672                        try {
673                            if (replacers != null) {
674                                statement = replaceTokens(statement, replacers);
675                                executeStatement(statement);
676                            } else {
677                                executeStatement(statement);
678                            }
679                        } catch (SQLException e) {
680                            if (!abortOnError) {
681                                if (m_errorLogging) {
682                                    m_errors.add("Error executing SQL statement: " + statement);
683                                    m_errors.add(CmsException.getStackTraceAsString(e));
684                                }
685                            } else {
686                                throw e;
687                            }
688                        }
689
690                        // reset
691                        statement = "";
692                    }
693                }
694
695                statement += " \n";
696            }
697        } catch (SQLException e) {
698            if (m_errorLogging) {
699                m_errors.add("Error executing SQL statement: " + statement);
700                m_errors.add(CmsException.getStackTraceAsString(e));
701            }
702        } catch (Exception e) {
703            if (m_errorLogging) {
704                m_errors.add("Error parsing database setup SQL script in line: " + line);
705                m_errors.add(CmsException.getStackTraceAsString(e));
706            }
707        } finally {
708            try {
709                if (reader != null) {
710                    reader.close();
711                }
712            } catch (Exception e) {
713                // noop
714            }
715        }
716    }
717
718    /**
719     * Internal method to parse and execute a setup script.<p>
720     *
721     * @param databaseKey the database variant of the script
722     * @param sqlScript the name of the script
723     * @param replacers the replacements to perform in the script
724     * @param abortOnError if a error occurs this flag indicates if to continue or to abort
725     */
726    private void executeSql(String databaseKey, String sqlScript, Map<String, String> replacers, boolean abortOnError) {
727
728        String filename = null;
729        try {
730            filename = m_basePath
731                + CmsSetupBean.FOLDER_SETUP
732                + "database"
733                + File.separator
734                + databaseKey
735                + File.separator
736                + sqlScript;
737            executeSql(new FileReader(filename), replacers, abortOnError);
738        } catch (FileNotFoundException e) {
739            if (m_errorLogging) {
740                m_errors.add("Database setup SQL script not found: " + filename);
741                m_errors.add(CmsException.getStackTraceAsString(e));
742            }
743        }
744    }
745
746    /**
747     * Creates and executes a database statement from a String.<p>
748     *
749     * @param statement the database statement
750     *
751     * @throws SQLException if something goes wrong
752     */
753    private void executeStatement(String statement) throws SQLException {
754
755        Statement stmt = null;
756
757        try {
758            stmt = m_con.createStatement();
759            stmt.execute(statement);
760        } finally {
761            if (stmt != null) {
762                stmt.close();
763            }
764        }
765    }
766
767    /**
768     * Replaces tokens "${xxx}" in a specified SQL query.<p>
769     *
770     * @param sql a SQL query
771     * @param replacers a Map with values keyed by "${xxx}" tokens
772     * @return the SQl query with all "${xxx}" tokens replaced
773     */
774    private String replaceTokens(String sql, Map<String, String> replacers) {
775
776        Iterator<Map.Entry<String, String>> keys = replacers.entrySet().iterator();
777        while (keys.hasNext()) {
778            Map.Entry<String, String> entry = keys.next();
779
780            String key = entry.getKey();
781            String value = entry.getValue();
782
783            sql = CmsStringUtil.substitute(sql, key, value);
784        }
785
786        return sql;
787    }
788}