001/*
002 * Syncany, www.syncany.org
003 * Copyright (C) 2011-2016 Philipp C. Heckel <philipp.heckel@gmail.com>
004 *
005 * This program is free software: you can redistribute it and/or modify
006 * it under the terms of the GNU General Public License as published by
007 * the Free Software Foundation, either version 3 of the License, or
008 * (at your option) any later version.
009 *
010 * This program is distributed in the hope that it will be useful,
011 * but WITHOUT ANY WARRANTY; without even the implied warranty of
012 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
013 * GNU General Public License for more details.
014 *
015 * You should have received a copy of the GNU General Public License
016 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
017 */
018package org.syncany.util;
019
020import java.io.BufferedReader;
021import java.io.IOException;
022import java.io.InputStream;
023import java.io.InputStreamReader;
024import java.sql.Connection;
025import java.sql.SQLException;
026import java.sql.Statement;
027import java.util.logging.Level;
028import java.util.logging.Logger;
029import java.util.regex.Matcher;
030import java.util.regex.Pattern;
031
032/**
033 * Helper class to execute SQL scripts on a given connection. The script honors SQL comments and
034 * separately executes commands one after another.
035 *
036 * @author Philipp C. Heckel (philipp.heckel@gmail.com)
037 */
038public class SqlRunner {
039        private static final Logger logger = Logger.getLogger(SqlRunner.class.getSimpleName());
040
041        private static final String DEFAULT_DELIMITER = ";";
042        private static final Pattern NEW_DELIMITER_PATTERN = Pattern.compile("(?:--|\\/\\/|\\#)?!DELIMITER=(.+)");
043        private static final Pattern COMMENT_PATTERN = Pattern.compile("^(?:--|\\/\\/|\\#).+");
044
045        public static void runScript(Connection connection, InputStream scriptInputStream) throws SQLException, IOException {
046                try (BufferedReader scriptReader = new BufferedReader(new InputStreamReader(scriptInputStream))) {
047                        StringBuffer command = null;
048                        String delimiter = DEFAULT_DELIMITER;
049                        String line = null;
050
051                        while ((line = scriptReader.readLine()) != null) {
052                                if (command == null) {
053                                        command = new StringBuffer();
054                                }
055
056                                String trimmedLine = line.trim();
057
058                                Matcher delimiterMatcher = NEW_DELIMITER_PATTERN.matcher(trimmedLine);
059                                Matcher commentMatcher = COMMENT_PATTERN.matcher(trimmedLine);
060
061                                // a) Delimiter change
062                                if (delimiterMatcher.find()) {
063                                        delimiter = delimiterMatcher.group(1);
064                                        logger.log(Level.INFO, "SQL (new delimiter): " + delimiter);
065                                }
066
067                                // b) Comment
068                                else if (commentMatcher.find()) {
069                                        logger.log(Level.INFO, "SQL (comment): " + trimmedLine);
070                                }
071
072                                // c) Statement
073                                else {
074                                        command.append(trimmedLine);
075                                        command.append(' ');
076
077                                        // End of statement
078                                        if (trimmedLine.endsWith(delimiter)) {
079                                                logger.log(Level.INFO, "SQL: " + command);
080
081                                                Statement statement = connection.createStatement();
082
083                                                statement.execute(command.toString());
084                                                statement.close();
085
086                                                command = null;
087                                        }
088                                }
089                        }
090                }
091        }
092}