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    /** Category pseudo-tag */
050    public static final String CATEGORY = "#CATEGORY:";
051
052    /**
053     * Tags that may condition execution of the statement.
054     */
055    public static class Tag {
056
057        /**
058         * Tag for a SELECT statement whose number of rows must be counted. Var "emptyResult" is set accordingly.
059         */
060        public static final String TAG_TEST = "#TEST:";
061
062        /**
063         * Tag to only execute statement if a var is true. Var may be preceded by ! inverse the test.
064         */
065        public static final String TAG_IF = "#IF:";
066
067        /**
068         * Tag to define a stored procedure / function / type / trigger. Followed by its name. Use by
069         * {@link Dialect#checkStoredProcedure}.
070         */
071        public static final String TAG_PROC = "#PROC:";
072
073        /**
074         * Tag to set a var to true if the result if the statement is empty.
075         */
076        public static final String TAG_SET_IF_EMPTY = "#SET_IF_EMPTY:";
077
078        /**
079         * Tag to set a var to true if the result if the statement is not empty.
080         */
081        public static final String TAG_SET_IF_NOT_EMPTY = "#SET_IF_NOT_EMPTY:";
082
083        public static final String VAR_EMPTY_RESULT = "emptyResult";
084
085        /** The tag key. */
086        public final String key;
087
088        /**
089         * The value behind a tag, used for {@link #TAG_IF}, {@link #TAG_PROC}, {@link #TAG_SET_IF_EMPTY},
090         * {@link #TAG_SET_IF_NOT_EMPTY}
091         */
092        public final String value;
093
094        public Tag(String key, String value) {
095            this.key = key;
096            this.value = value;
097        }
098    }
099
100    /**
101     * Collects a list of strings.
102     *
103     * @since 6.0-HF24, 7.10-HF01, 8.1
104     */
105    public static class ListCollector {
106
107        private final List<String> list = new ArrayList<>();
108
109        /** Collects one string. */
110        public void add(String string) {
111            list.add(string);
112        }
113
114        /** Collects several strings. */
115        public void addAll(List<String> strings) {
116            list.addAll(strings);
117        }
118
119        /** Gets the collected strings. */
120        public List<String> getStrings() {
121            return list;
122        }
123    }
124
125    /** SQL statement */
126    public final String sql;
127
128    /** Tags on the statement */
129    public final List<Tag> tags;
130
131    public SQLStatement(String sql, List<Tag> tags) {
132        this.sql = sql;
133        this.tags = tags == null ? Collections.<Tag> emptyList() : tags;
134    }
135
136    @Override
137    public String toString() {
138        StringBuilder sb = new StringBuilder();
139        sb.append("SQLStatement(");
140        for (Tag tag : tags) {
141            sb.append(tag.key);
142            String value = tag.value;
143            if (value != null) {
144                sb.append(' ');
145                sb.append(value);
146            }
147            sb.append(", ");
148        }
149        sb.append(sql);
150        sb.append(')');
151        return sb.toString();
152    }
153
154    /**
155     * Reads SQL statements from a text file.
156     * <p>
157     * Statements have a category, and optional tags (that may condition execution).
158     *
159     * <pre>
160     *   #CATEGORY: mycat
161     *   #TEST:
162     *   SELECT foo
163     *     from bar;
164     * </pre>
165     *
166     * <pre>
167     *   #CATEGORY: mycat
168     *   #IF: emptyResult
169     *   #IF: somethingEnabled
170     *   INSERT INTO ...;
171     * </pre>
172     *
173     * An empty line terminates a statement.
174     */
175    public static Map<String, List<SQLStatement>> read(String filename, Map<String, List<SQLStatement>> statements)
176            throws IOException {
177        return read(filename, statements, false);
178    }
179
180    public static Map<String, List<SQLStatement>> read(String filename, Map<String, List<SQLStatement>> statements,
181            boolean allDDL) throws IOException {
182        InputStream is = Activator.getResourceAsStream(filename);
183        if (is == null) {
184            throw new IOException("Cannot open: " + filename);
185        }
186        BufferedReader reader = new BufferedReader(new InputStreamReader(is, "UTF-8"));
187        String line;
188        String category = null;
189        List<Tag> tags = new LinkedList<>();
190        try {
191            while ((line = reader.readLine()) != null) {
192                int colonPos = line.indexOf(':');
193                String key = colonPos < 0 ? "" : line.substring(0, colonPos + 1);
194                String value = colonPos < 0 ? "" : line.substring(colonPos + 1).trim();
195                switch (key) {
196                case SQLStatement.CATEGORY:
197                    category = value;
198                    continue;
199                case Tag.TAG_TEST:
200                case Tag.TAG_IF:
201                case Tag.TAG_PROC:
202                case Tag.TAG_SET_IF_EMPTY:
203                case Tag.TAG_SET_IF_NOT_EMPTY:
204                    if (value.length() == 0) {
205                        value = null;
206                    }
207                    tags.add(new Tag(key, value));
208                    continue;
209                }
210                if (line.startsWith("#")) {
211                    continue;
212                }
213                StringBuilder sb = new StringBuilder();
214                boolean read = false;
215                while (true) {
216                    if (read) {
217                        line = reader.readLine();
218                    } else {
219                        read = true;
220                    }
221                    if (line == null || line.trim().equals("")) {
222                        if (sb.length() == 0) {
223                            break;
224                        }
225                        String sql = sb.toString().trim();
226                        SQLStatement statement = new SQLStatement(sql, tags);
227                        List<SQLStatement> catStatements = statements.get(category);
228                        if (catStatements == null) {
229                            statements.put(category, catStatements = new LinkedList<>());
230                        }
231                        catStatements.add(statement);
232                        break;
233                    } else if (line.startsWith("#")) {
234                        continue;
235                    } else {
236                        sb.append(line);
237                        sb.append('\n');
238                    }
239                }
240                tags = new LinkedList<>();
241                if (line == null) {
242                    break;
243                }
244            }
245        } finally {
246            reader.close();
247        }
248        return statements;
249    }
250
251    protected static String replaceVars(String sql, Map<String, Serializable> properties) {
252        if (properties != null) {
253            for (Entry<String, Serializable> en : properties.entrySet()) {
254                String key = "${" + en.getKey() + "}";
255                String value = String.valueOf(en.getValue());
256                sql = sql.replaceAll(Pattern.quote(key), Matcher.quoteReplacement(value));
257            }
258        }
259        return sql;
260    }
261
262    /**
263     * Executes a list of SQL statements, following the tags.
264     */
265    public static void execute(List<SQLStatement> statements, String ddlMode, Map<String, Serializable> properties,
266            Dialect dialect, Connection connection, JDBCLogger logger, ListCollector ddlCollector) throws SQLException {
267        try (Statement st = connection.createStatement()) {
268            STATEMENT: //
269            for (SQLStatement statement : statements) {
270                boolean test = false;
271                String proc = null;
272                Set<String> setIfEmpty = new HashSet<>();
273                Set<String> setIfNotEmpty = new HashSet<>();
274                for (Tag tag : statement.tags) {
275                    switch (tag.key) {
276                    case Tag.TAG_TEST:
277                        test = true;
278                        break;
279                    case Tag.TAG_PROC:
280                        proc = tag.value;
281                        break;
282                    case Tag.TAG_IF:
283                        String expr = tag.value;
284                        boolean res = false;
285                        for (String key : expr.split(" OR: ")) {
286                            boolean neg = key.startsWith("!");
287                            if (neg) {
288                                key = key.substring(1).trim();
289                            }
290                            Serializable value = properties.get(key);
291                            if (value == null) {
292                                logger.log("Defaulting to false: " + key);
293                                value = Boolean.FALSE;
294                            }
295                            if (!(value instanceof Boolean)) {
296                                logger.error("Not a boolean condition: " + key);
297                                continue STATEMENT;
298                            }
299                            if (((Boolean) value).booleanValue() != neg) {
300                                res = true;
301                                break;
302                            }
303                        }
304                        if (!res) {
305                            continue STATEMENT;
306                        }
307                        break;
308                    case Tag.TAG_SET_IF_EMPTY:
309                        setIfEmpty.add(tag.value);
310                        break;
311                    case Tag.TAG_SET_IF_NOT_EMPTY:
312                        setIfNotEmpty.add(tag.value);
313                        break;
314                    }
315                }
316                String sql = statement.sql;
317                sql = replaceVars(sql, properties);
318                if (sql.startsWith("LOG.DEBUG")) {
319                    String msg = sql.substring("LOG.DEBUG".length()).trim();
320                    logger.log(msg);
321                    continue;
322                } else if (sql.startsWith("LOG.INFO")) {
323                    String msg = sql.substring("LOG.INFO".length()).trim();
324                    logger.info(msg);
325                    continue;
326                } else if (sql.startsWith("LOG.ERROR")) {
327                    String msg = sql.substring("LOG.ERROR".length()).trim();
328                    logger.error(msg);
329                    continue;
330                } else if (sql.startsWith("LOG.FATAL")) {
331                    String msg = sql.substring("LOG.FATAL".length()).trim();
332                    logger.error(msg);
333                    throw new SQLException("Fatal error: " + msg);
334                }
335
336                try {
337                    if (test) {
338                        logger.log(sql.replace("\n", "\n    ")); // indented
339                        try (ResultSet rs = st.executeQuery(sql)) {
340                            boolean empty = !rs.next();
341                            properties.put(Tag.VAR_EMPTY_RESULT, Boolean.valueOf(empty));
342                            logger.log("  -> emptyResult = " + empty);
343                            if (empty) {
344                                for (String prop : setIfEmpty) {
345                                    properties.put(prop, Boolean.TRUE);
346                                    logger.log("  -> " + prop + " = true");
347                                }
348                            } else {
349                                for (String prop : setIfNotEmpty) {
350                                    properties.put(prop, Boolean.TRUE);
351                                    logger.log("  -> " + prop + " = true");
352                                }
353                            }
354                        }
355                    } else if (proc != null) {
356                        ddlCollector.addAll(
357                                dialect.checkStoredProcedure(proc, sql, ddlMode, connection, logger, properties));
358                    } else if (ddlCollector != null) {
359                        ddlCollector.add(sql);
360                    } else {
361                        // upgrade stuff, execute immediately
362                        logger.log(sql.replace("\n", "\n    ")); // indented
363                        st.execute(sql);
364                    }
365                } catch (SQLException e) {
366                    throw new SQLException("Error executing: " + sql + " : " + e.getMessage(), e);
367                }
368            }
369        }
370    }
371
372}