001/*
002 * (C) Copyright 2006-2011 Nuxeo SA (http://nuxeo.com/) and others.
003 *
004 * Licensed under the Apache License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 *
008 *     http://www.apache.org/licenses/LICENSE-2.0
009 *
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 *
016 * Contributors:
017 *     Florent Guillaume
018 */
019package org.nuxeo.ecm.core.storage.sql.jdbc.dialect;
020
021import java.io.BufferedReader;
022import java.io.IOException;
023import java.io.InputStream;
024import java.io.InputStreamReader;
025import java.io.Serializable;
026import java.sql.Connection;
027import java.sql.ResultSet;
028import java.sql.SQLException;
029import java.sql.Statement;
030import java.util.ArrayList;
031import java.util.Collections;
032import java.util.HashSet;
033import java.util.LinkedList;
034import java.util.List;
035import java.util.Map;
036import java.util.Map.Entry;
037import java.util.Set;
038import java.util.regex.Matcher;
039import java.util.regex.Pattern;
040
041import org.nuxeo.ecm.core.storage.sql.Activator;
042import org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger;
043
044/**
045 * A SQL statement and some optional tags that condition execution.
046 */
047public class SQLStatement {
048
049    // for derby...
050    public static final String DIALECT_WITH_NO_SEMICOLON = "noSemicolon";
051
052    /** Category pseudo-tag */
053    public static final String CATEGORY = "#CATEGORY:";
054
055    /**
056     * Tags that may condition execution of the statement.
057     */
058    public static class Tag {
059
060        /**
061         * Tag for a SELECT statement whose number of rows must be counted. Var "emptyResult" is set accordingly.
062         */
063        public static final String TAG_TEST = "#TEST:";
064
065        /**
066         * Tag to only execute statement if a var is true. Var may be preceded by ! inverse the test.
067         */
068        public static final String TAG_IF = "#IF:";
069
070        /**
071         * Tag to define a stored procedure / function / type / trigger. Followed by its name. Use by
072         * {@link Dialect#checkStoredProcedure}.
073         */
074        public static final String TAG_PROC = "#PROC:";
075
076        /**
077         * Tag to set a var to true if the result if the statement is empty.
078         */
079        public static final String TAG_SET_IF_EMPTY = "#SET_IF_EMPTY:";
080
081        /**
082         * Tag to set a var to true if the result if the statement is not empty.
083         */
084        public static final String TAG_SET_IF_NOT_EMPTY = "#SET_IF_NOT_EMPTY:";
085
086        public static final String VAR_EMPTY_RESULT = "emptyResult";
087
088        /** The tag key. */
089        public final String key;
090
091        /**
092         * The value behind a tag, used for {@link #TAG_IF}, {@link #TAG_PROC}, {@link #TAG_SET_IF_EMPTY},
093         * {@link #TAG_SET_IF_NOT_EMPTY}
094         */
095        public final String value;
096
097        public Tag(String key, String value) {
098            this.key = key;
099            this.value = value;
100        }
101    }
102
103    /**
104     * Collects a list of strings.
105     *
106     * @since 6.0-HF24, 7.10-HF01, 8.1
107     */
108    public static class ListCollector {
109
110        private final List<String> list = new ArrayList<>();
111
112        /** Collects one string. */
113        public void add(String string) {
114            list.add(string);
115        }
116
117        /** Collects several strings. */
118        public void addAll(List<String> strings) {
119            list.addAll(strings);
120        }
121
122        /** Gets the collected strings. */
123        public List<String> getStrings() {
124            return list;
125        }
126    }
127
128    /** SQL statement */
129    public final String sql;
130
131    /** Tags on the statement */
132    public final List<Tag> tags;
133
134    public SQLStatement(String sql, List<Tag> tags) {
135        this.sql = sql;
136        this.tags = tags == null ? Collections.<Tag> emptyList() : tags;
137    }
138
139    @Override
140    public String toString() {
141        StringBuilder buf = new StringBuilder();
142        buf.append("SQLStatement(");
143        for (Tag tag : tags) {
144            buf.append(tag.key);
145            String value = tag.value;
146            if (value != null) {
147                buf.append(' ');
148                buf.append(value);
149            }
150            buf.append(", ");
151        }
152        buf.append(sql);
153        buf.append(')');
154        return buf.toString();
155    }
156
157    /**
158     * Reads SQL statements from a text file.
159     * <p>
160     * Statements have a category, and optional tags (that may condition execution).
161     *
162     * <pre>
163     *   #CATEGORY: mycat
164     *   #TEST:
165     *   SELECT foo
166     *     from bar;
167     * </pre>
168     *
169     * <pre>
170     *   #CATEGORY: mycat
171     *   #IF: emptyResult
172     *   #IF: somethingEnabled
173     *   INSERT INTO ...;
174     * </pre>
175     *
176     * An empty line terminates a statement.
177     */
178    public static Map<String, List<SQLStatement>> read(String filename, Map<String, List<SQLStatement>> statements)
179            throws IOException {
180        return read(filename, statements, false);
181    }
182
183    public static Map<String, List<SQLStatement>> read(String filename, Map<String, List<SQLStatement>> statements,
184            boolean allDDL) throws IOException {
185        InputStream is = Activator.getResourceAsStream(filename);
186        if (is == null) {
187            throw new IOException("Cannot open: " + filename);
188        }
189        BufferedReader reader = new BufferedReader(new InputStreamReader(is, "UTF-8"));
190        String line;
191        String category = null;
192        List<Tag> tags = new LinkedList<>();
193        try {
194            while ((line = reader.readLine()) != null) {
195                int colonPos = line.indexOf(':');
196                String key = colonPos < 0 ? "" : line.substring(0, colonPos + 1);
197                String value = colonPos < 0 ? "" : line.substring(colonPos + 1).trim();
198                switch (key) {
199                case SQLStatement.CATEGORY:
200                    category = value;
201                    continue;
202                case Tag.TAG_TEST:
203                case Tag.TAG_IF:
204                case Tag.TAG_PROC:
205                case Tag.TAG_SET_IF_EMPTY:
206                case Tag.TAG_SET_IF_NOT_EMPTY:
207                    if (value.length() == 0) {
208                        value = null;
209                    }
210                    tags.add(new Tag(key, value));
211                    continue;
212                }
213                if (line.startsWith("#")) {
214                    continue;
215                }
216                StringBuilder buf = new StringBuilder();
217                boolean read = false;
218                while (true) {
219                    if (read) {
220                        line = reader.readLine();
221                    } else {
222                        read = true;
223                    }
224                    if (line == null || line.trim().equals("")) {
225                        if (buf.length() == 0) {
226                            break;
227                        }
228                        String sql = buf.toString().trim();
229                        SQLStatement statement = new SQLStatement(sql, tags);
230                        List<SQLStatement> catStatements = statements.get(category);
231                        if (catStatements == null) {
232                            statements.put(category, catStatements = new LinkedList<SQLStatement>());
233                        }
234                        catStatements.add(statement);
235                        break;
236                    } else if (line.startsWith("#")) {
237                        continue;
238                    } else {
239                        buf.append(line);
240                        buf.append('\n');
241                    }
242                }
243                tags = new LinkedList<>();
244                if (line == null) {
245                    break;
246                }
247            }
248        } finally {
249            reader.close();
250        }
251        return statements;
252    }
253
254    protected static String replaceVars(String sql, Map<String, Serializable> properties) {
255        if (properties != null) {
256            for (Entry<String, Serializable> en : properties.entrySet()) {
257                String key = "${" + en.getKey() + "}";
258                String value = String.valueOf(en.getValue());
259                sql = sql.replaceAll(Pattern.quote(key), Matcher.quoteReplacement(value));
260            }
261        }
262        return sql;
263    }
264
265    /**
266     * Executes a list of SQL statements, following the tags.
267     */
268    public static void execute(List<SQLStatement> statements, String ddlMode, Map<String, Serializable> properties,
269            Dialect dialect, Connection connection, JDBCLogger logger, ListCollector ddlCollector) throws SQLException {
270        try (Statement st = connection.createStatement()) {
271            STATEMENT: //
272            for (SQLStatement statement : statements) {
273                boolean test = false;
274                String proc = null;
275                Set<String> setIfEmpty = new HashSet<>();
276                Set<String> setIfNotEmpty = new HashSet<>();
277                for (Tag tag : statement.tags) {
278                    switch (tag.key) {
279                    case Tag.TAG_TEST:
280                        test = true;
281                        break;
282                    case Tag.TAG_PROC:
283                        proc = tag.value;
284                        break;
285                    case Tag.TAG_IF:
286                        String expr = tag.value;
287                        boolean res = false;
288                        for (String key : expr.split(" OR: ")) {
289                            boolean neg = key.startsWith("!");
290                            if (neg) {
291                                key = key.substring(1).trim();
292                            }
293                            Serializable value = properties.get(key);
294                            if (value == null) {
295                                logger.log("Defaulting to false: " + key);
296                                value = Boolean.FALSE;
297                            }
298                            if (!(value instanceof Boolean)) {
299                                logger.error("Not a boolean condition: " + key);
300                                continue STATEMENT;
301                            }
302                            if (((Boolean) value).booleanValue() != neg) {
303                                res = true;
304                                break;
305                            }
306                        }
307                        if (!res) {
308                            continue STATEMENT;
309                        }
310                        break;
311                    case Tag.TAG_SET_IF_EMPTY:
312                        setIfEmpty.add(tag.value);
313                        break;
314                    case Tag.TAG_SET_IF_NOT_EMPTY:
315                        setIfNotEmpty.add(tag.value);
316                        break;
317                    }
318                }
319                String sql = statement.sql;
320                sql = replaceVars(sql, properties);
321                if (sql.startsWith("LOG.DEBUG")) {
322                    String msg = sql.substring("LOG.DEBUG".length()).trim();
323                    logger.log(msg);
324                    continue;
325                } else if (sql.startsWith("LOG.INFO")) {
326                    String msg = sql.substring("LOG.INFO".length()).trim();
327                    logger.info(msg);
328                    continue;
329                } else if (sql.startsWith("LOG.ERROR")) {
330                    String msg = sql.substring("LOG.ERROR".length()).trim();
331                    logger.error(msg);
332                    continue;
333                } else if (sql.startsWith("LOG.FATAL")) {
334                    String msg = sql.substring("LOG.FATAL".length()).trim();
335                    logger.error(msg);
336                    throw new SQLException("Fatal error: " + msg);
337                }
338
339                if (sql.endsWith(";") && properties.containsKey(DIALECT_WITH_NO_SEMICOLON)) {
340                    // derby at least doesn't allow a terminating semicolon
341                    sql = sql.substring(0, sql.length() - 1);
342                }
343
344                try {
345                    if (test) {
346                        logger.log(sql.replace("\n", "\n    ")); // indented
347                        try (ResultSet rs = st.executeQuery(sql)) {
348                            boolean empty = !rs.next();
349                            properties.put(Tag.VAR_EMPTY_RESULT, Boolean.valueOf(empty));
350                            logger.log("  -> emptyResult = " + empty);
351                            if (empty) {
352                                for (String prop : setIfEmpty) {
353                                    properties.put(prop, Boolean.TRUE);
354                                    logger.log("  -> " + prop + " = true");
355                                }
356                            } else {
357                                for (String prop : setIfNotEmpty) {
358                                    properties.put(prop, Boolean.TRUE);
359                                    logger.log("  -> " + prop + " = true");
360                                }
361                            }
362                        }
363                    } else if (proc != null) {
364                        ddlCollector.addAll(
365                                dialect.checkStoredProcedure(proc, sql, ddlMode, connection, logger, properties));
366                    } else if (ddlCollector != null) {
367                        ddlCollector.add(sql);
368                    } else {
369                        // upgrade stuff, execute immediately
370                        logger.log(sql.replace("\n", "\n    ")); // indented
371                        st.execute(sql);
372                    }
373                } catch (SQLException e) {
374                    throw new SQLException("Error executing: " + sql + " : " + e.getMessage(), e);
375                }
376            }
377        }
378    }
379
380}