001/*
002 * Copyright (c) 2006-2011 Nuxeo SA (http://nuxeo.com/) and others.
003 *
004 * All rights reserved. This program and the accompanying materials
005 * are made available under the terms of the Eclipse Public License v1.0
006 * which accompanies this distribution, and is available at
007 * http://www.eclipse.org/legal/epl-v10.html
008 *
009 * Contributors:
010 *     Florent Guillaume
011 */
012package org.nuxeo.ecm.core.storage.sql.jdbc.dialect;
013
014import java.io.BufferedReader;
015import java.io.IOException;
016import java.io.InputStream;
017import java.io.InputStreamReader;
018import java.io.Serializable;
019import java.sql.ResultSet;
020import java.sql.SQLException;
021import java.sql.Statement;
022import java.util.Collections;
023import java.util.LinkedList;
024import java.util.List;
025import java.util.Map;
026import java.util.Map.Entry;
027import java.util.regex.Matcher;
028import java.util.regex.Pattern;
029
030import org.nuxeo.ecm.core.storage.sql.Activator;
031import org.nuxeo.ecm.core.storage.sql.jdbc.JDBCConnection;
032
033/**
034 * A SQL statement and some optional tags that condition execution.
035 */
036public class SQLStatement {
037
038    // for derby...
039    public static final String DIALECT_WITH_NO_SEMICOLON = "noSemicolon";
040
041    /** Category pseudo-tag */
042    public static final String CATEGORY = "#CATEGORY:";
043
044    /**
045     * Tags that may condition execution of the statement.
046     */
047    public static class Tag {
048
049        /**
050         * Tag for a SELECT statement whose number of rows must be counted. Var "emptyResult" is set accordingly.
051         */
052        public static final String TAG_TEST = "#TEST:";
053
054        /**
055         * Tag to only execute statement if a var is true. Var may be preceded by ! inverse the test.
056         */
057        public static final String TAG_IF = "#IF:";
058
059        public static final String VAR_EMPTY_RESULT = "emptyResult";
060
061        /** Tag: TAG_TEST, TAG_IF */
062        public final String key;
063
064        /** The value behind a tag, used for TAG_IF */
065        public final String value;
066
067        public Tag(String key, String value) {
068            this.key = key;
069            this.value = value;
070        }
071    }
072
073    /** SQL statement */
074    public final String sql;
075
076    /** Tags on the statement */
077    public final List<Tag> tags;
078
079    public SQLStatement(String sql, List<Tag> tags) {
080        this.sql = sql;
081        this.tags = tags == null ? Collections.<Tag> emptyList() : tags;
082    }
083
084    @Override
085    public String toString() {
086        StringBuilder buf = new StringBuilder();
087        buf.append("SQLStatement(");
088        for (Tag tag : tags) {
089            buf.append(tag.key);
090            String value = tag.value;
091            if (value != null) {
092                buf.append(' ');
093                buf.append(value);
094            }
095            buf.append(", ");
096        }
097        buf.append(sql);
098        buf.append(')');
099        return buf.toString();
100    }
101
102    /**
103     * Reads SQL statements from a text file.
104     * <p>
105     * Statements have a category, and optional tags (that may condition execution).
106     *
107     * <pre>
108     *   #CATEGORY: mycat
109     *   #TEST:
110     *   SELECT foo
111     *     from bar;
112     * </pre>
113     *
114     * <pre>
115     *   #CATEGORY: mycat
116     *   #IF: emptyResult
117     *   #IF: somethingEnabled
118     *   INSERT INTO ...;
119     * </pre>
120     *
121     * An empty line terminates a statement.
122     */
123    public static Map<String, List<SQLStatement>> read(String filename, Map<String, List<SQLStatement>> statements)
124            throws IOException {
125        InputStream is = Activator.getResourceAsStream(filename);
126        if (is == null) {
127            throw new IOException("Cannot open: " + filename);
128        }
129        BufferedReader reader = new BufferedReader(new InputStreamReader(is, "UTF-8"));
130        String line;
131        String category = null;
132        List<Tag> tags = null;
133        try {
134            while ((line = reader.readLine()) != null) {
135                if (line.startsWith(SQLStatement.CATEGORY)) {
136                    category = line.substring(SQLStatement.CATEGORY.length()).trim();
137                    continue;
138                } else if (line.startsWith(Tag.TAG_TEST) || line.startsWith(Tag.TAG_IF)) {
139                    String key = line.substring(0, line.indexOf(':') + 1);
140                    String value = line.substring(key.length()).trim();
141                    if (value.length() == 0) {
142                        value = null;
143                    }
144                    if (tags == null) {
145                        tags = new LinkedList<Tag>();
146                    }
147                    tags.add(new Tag(key, value));
148                    continue;
149                } else if (line.startsWith("#")) {
150                    continue;
151                }
152                StringBuilder buf = new StringBuilder();
153                boolean read = false;
154                while (true) {
155                    if (read) {
156                        line = reader.readLine();
157                    } else {
158                        read = true;
159                    }
160                    if (line == null || line.trim().equals("")) {
161                        if (buf.length() == 0) {
162                            break;
163                        }
164                        String sql = buf.toString().trim();
165                        SQLStatement statement = new SQLStatement(sql, tags);
166                        List<SQLStatement> catStatements = statements.get(category);
167                        if (catStatements == null) {
168                            statements.put(category, catStatements = new LinkedList<SQLStatement>());
169                        }
170                        catStatements.add(statement);
171                        break;
172                    } else if (line.startsWith("#")) {
173                        continue;
174                    } else {
175                        buf.append(line);
176                        buf.append('\n');
177                    }
178                }
179                tags = null;
180                if (line == null) {
181                    break;
182                }
183            }
184        } finally {
185            reader.close();
186        }
187        return statements;
188    }
189
190    protected static String replaceVars(String sql, Map<String, Serializable> properties) {
191        if (properties != null) {
192            for (Entry<String, Serializable> en : properties.entrySet()) {
193                String key = "${" + en.getKey() + "}";
194                String value = String.valueOf(en.getValue());
195                sql = sql.replaceAll(Pattern.quote(key), Matcher.quoteReplacement(value));
196            }
197        }
198        return sql;
199    }
200
201    /**
202     * Executes a list of SQL statements, following the tags.
203     */
204    public static void execute(List<SQLStatement> statements, Map<String, Serializable> properties, JDBCConnection jdbc)
205            throws SQLException {
206        Statement st = jdbc.connection.createStatement();
207        try {
208            STATEMENT: //
209            for (SQLStatement statement : statements) {
210                boolean test = false;
211                for (Tag tag : statement.tags) {
212                    if (tag.key.equals(Tag.TAG_TEST)) {
213                        test = true;
214                    } else if (tag.key.equals(Tag.TAG_IF)) {
215                        String key = tag.value;
216                        boolean neg = key.startsWith("!");
217                        if (neg) {
218                            key = key.substring(1).trim();
219                        }
220                        Serializable value = properties.get(key);
221                        if (value == null) {
222                            jdbc.logger.error("Unknown condition: " + key);
223                            continue STATEMENT;
224                        }
225                        if (!(value instanceof Boolean)) {
226                            jdbc.logger.error("Not a boolean condition: " + key);
227                            continue STATEMENT;
228                        }
229                        if (((Boolean) value).booleanValue() == neg) {
230                            // condition failed
231                            continue STATEMENT;
232                        }
233                        // ok
234                    }
235                }
236                String sql = statement.sql;
237                sql = replaceVars(sql, properties);
238                if (sql.startsWith("LOG.DEBUG")) {
239                    String msg = sql.substring("LOG.DEBUG".length()).trim();
240                    jdbc.logger.log(msg);
241                    continue;
242                } else if (sql.startsWith("LOG.INFO")) {
243                    String msg = sql.substring("LOG.INFO".length()).trim();
244                    jdbc.logger.info(msg);
245                    continue;
246                } else if (sql.startsWith("LOG.ERROR")) {
247                    String msg = sql.substring("LOG.ERROR".length()).trim();
248                    jdbc.logger.error(msg);
249                    continue;
250                } else if (sql.startsWith("LOG.FATAL")) {
251                    String msg = sql.substring("LOG.FATAL".length()).trim();
252                    jdbc.logger.error(msg);
253                    throw new SQLException("Fatal error: " + msg);
254                }
255
256                jdbc.logger.log(sql.replace("\n", "\n    ")); // indented
257                if (sql.endsWith(";") && properties.containsKey(DIALECT_WITH_NO_SEMICOLON)) {
258                    // derby at least doesn't allow a terminating semicolon
259                    sql = sql.substring(0, sql.length() - 1);
260                }
261
262                try {
263                    if (test) {
264                        ResultSet rs = st.executeQuery(sql);
265                        Boolean emptyResult = Boolean.valueOf(!rs.next());
266                        properties.put(Tag.VAR_EMPTY_RESULT, emptyResult);
267                        jdbc.logger.log("  -> emptyResult = " + emptyResult);
268                        rs.close();
269                    } else {
270                        st.execute(sql);
271                    }
272                } catch (SQLException e) {
273                    throw new SQLException("Error executing: " + sql + " : " + e.getMessage(), e);
274                }
275            }
276        } finally {
277            st.close();
278        }
279    }
280
281}