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 */
019
020package org.nuxeo.ecm.core.storage.sql.db;
021
022import java.io.Serializable;
023import java.sql.Connection;
024import java.sql.DriverManager;
025import java.sql.PreparedStatement;
026import java.sql.ResultSet;
027import java.sql.SQLException;
028import java.util.ArrayList;
029import java.util.Collection;
030import java.util.Collections;
031import java.util.HashSet;
032import java.util.List;
033import java.util.Set;
034import java.util.regex.Pattern;
035
036/**
037 * Functions used as stored procedures for Derby and H2.
038 *
039 * @author Florent Guillaume
040 */
041public class EmbeddedFunctions {
042
043    // for debug
044    private static boolean isLogEnabled() {
045        return false;
046        // return log.isTraceEnabled();
047    }
048
049    // for debug
050    private static void logDebug(String message) {
051        // log.trace(message);
052    }
053
054    /**
055     * Checks if an id is a (strict) descendant of a given base id.
056     *
057     * @param id the id to check for
058     * @param baseId the base id
059     */
060    public static boolean isInTree(Serializable id, Serializable baseId) throws SQLException {
061        Connection conn = DriverManager.getConnection("jdbc:default:connection");
062        try {
063            return isInTree(conn, id, baseId);
064        } finally {
065            conn.close();
066        }
067    }
068
069    /**
070     * Checks if an id is a (strict) descendant of a given base id.
071     *
072     * @param conn the connection to the database
073     * @param id the id to check for
074     * @param baseId the base id
075     */
076    public static boolean isInTree(Connection conn, Serializable id, Serializable baseId) throws SQLException {
077        if (baseId == null || id == null || baseId.equals(id)) {
078            // containment check is strict
079            return false;
080        }
081        PreparedStatement ps = null;
082        try {
083            ps = conn.prepareStatement("SELECT PARENTID, ISPROPERTY FROM HIERARCHY WHERE ID = ?");
084            do {
085                ps.setObject(1, id);
086                ResultSet rs = ps.executeQuery();
087                if (!rs.next()) {
088                    // no such id
089                    return false;
090                }
091                if (id instanceof String) {
092                    id = rs.getString(1);
093                } else {
094                    id = Long.valueOf(rs.getLong(1));
095                }
096                if (rs.wasNull()) {
097                    id = null;
098                }
099                boolean isProperty = rs.getBoolean(2);
100                rs.close();
101                if (isProperty) {
102                    // a complex property is never in-tree
103                    return false;
104                }
105                if (baseId.equals(id)) {
106                    // found a match
107                    return true;
108                }
109            } while (id != null);
110            // got to the root
111            return false;
112        } finally {
113            if (ps != null) {
114                ps.close();
115            }
116        }
117    }
118
119    /**
120     * Checks if access to a document is allowed.
121     * <p>
122     * This implements in SQL the ACL-based security policy logic.
123     *
124     * @param id the id of the document
125     * @param principals the allowed identities
126     * @param permissions the allowed permissions
127     */
128    public static boolean isAccessAllowed(Serializable id, Set<String> principals, Set<String> permissions)
129            throws SQLException {
130        Connection conn = DriverManager.getConnection("jdbc:default:connection");
131        try {
132            return isAccessAllowed(conn, id, principals, permissions);
133        } finally {
134            conn.close();
135        }
136    }
137
138    /**
139     * Checks if access to a document is allowed.
140     * <p>
141     * This implements in SQL the ACL-based security policy logic.
142     *
143     * @param conn the database connection
144     * @param id the id of the document
145     * @param principals the allowed identities
146     * @param permissions the allowed permissions
147     */
148    public static boolean isAccessAllowed(Connection conn, Serializable id, Set<String> principals,
149            Set<String> permissions) throws SQLException {
150        if (isLogEnabled()) {
151            logDebug("isAccessAllowed " + id + " " + principals + " " + permissions);
152        }
153        PreparedStatement ps1 = null;
154        PreparedStatement ps2 = null;
155        PreparedStatement ps3 = null;
156        try {
157            ps1 = conn.prepareStatement( //
158            "SELECT \"GRANT\", \"PERMISSION\", \"USER\" FROM \"ACLS\" " + "WHERE ID = ? ORDER BY POS");
159            ps2 = conn.prepareStatement("SELECT PARENTID FROM HIERARCHY WHERE ID = ?");
160            boolean first = true;
161            do {
162                /*
163                 * Check permissions at this level.
164                 */
165                ps1.setObject(1, id);
166                ResultSet rs = ps1.executeQuery();
167                while (rs.next()) {
168                    boolean grant = rs.getShort(1) != 0;
169                    String permission = rs.getString(2);
170                    String user = rs.getString(3);
171                    if (isLogEnabled()) {
172                        logDebug(" -> " + user + " " + permission + " " + grant);
173                    }
174                    if (principals.contains(user) && permissions.contains(permission)) {
175                        if (isLogEnabled()) {
176                            logDebug(" => " + grant);
177                        }
178                        return grant;
179                    }
180                }
181                /*
182                 * Nothing conclusive found, repeat on the parent.
183                 */
184                ps2.setObject(1, id);
185                rs = ps2.executeQuery();
186                Serializable newId;
187                if (rs.next()) {
188                    newId = (Serializable) rs.getObject(1);
189                    if (rs.wasNull()) {
190                        newId = null;
191                    }
192                } else {
193                    // no such id
194                    newId = null;
195                }
196                if (first && newId == null) {
197                    // there is no parent for the first level
198                    // we may have a version on our hands, find the live doc
199                    ps3 = conn.prepareStatement("SELECT VERSIONABLEID FROM VERSIONS WHERE ID = ?");
200                    ps3.setObject(1, id);
201                    rs = ps3.executeQuery();
202                    if (rs.next()) {
203                        newId = (Serializable) rs.getObject(1);
204                        if (rs.wasNull()) {
205                            newId = null;
206                        }
207                    } else {
208                        // no such id
209                        newId = null;
210                    }
211                }
212                first = false;
213                id = newId;
214            } while (id != null);
215            /*
216             * We reached the root, deny access.
217             */
218            if (isLogEnabled()) {
219                logDebug(" => false (root)");
220            }
221            return false;
222        } finally {
223            if (ps1 != null) {
224                ps1.close();
225            }
226            if (ps2 != null) {
227                ps2.close();
228            }
229            if (ps3 != null) {
230                ps3.close();
231            }
232        }
233    }
234
235    /**
236     * Extracts the words from a string for simple fulltext indexing.
237     *
238     * @param string1 the first string
239     * @param string2 the second string
240     * @return a string with extracted words
241     */
242    public static String parseFullText(String string1, String string2) {
243        Set<String> set = new HashSet<String>();
244        set.addAll(parseFullText(string1));
245        set.addAll(parseFullText(string2));
246        List<String> words = new ArrayList<String>(set);
247        Collections.sort(words);
248        return join(words, ' ');
249    }
250
251    protected static Set<String> parseFullText(String string) {
252        if (string == null) {
253            return Collections.emptySet();
254        }
255        Set<String> set = new HashSet<String>();
256        for (String word : wordPattern.split(string)) {
257            String w = parseWord(word);
258            if (w != null) {
259                set.add(w);
260            }
261        }
262        return set;
263    }
264
265    /**
266     * Checks if the passed query expression matches the fulltext.
267     *
268     * @param fulltext the fulltext, space-separated words
269     * @param query a list of space-separated words
270     * @return {@code true} if all the words are in the fulltext
271     */
272    protected static boolean matchesFullText(String fulltext, String query) {
273        if (fulltext == null || query == null) {
274            return false;
275        }
276        Set<String> words = split(query.toLowerCase(), ' ');
277        Set<String> filtered = new HashSet<String>();
278        for (String word : words) {
279            if (!wordPattern.matcher(word).matches()) {
280                filtered.add(word);
281            }
282        }
283        words = filtered;
284        if (words.isEmpty()) {
285            return false;
286        }
287        Set<String> fulltextWords = split(fulltext.toLowerCase(), ' ');
288        for (String word : words) {
289            if (word.endsWith("*") || word.endsWith("%")) {
290                // prefix match
291                String prefix = word.substring(0, word.length() - 2);
292                boolean match = false;
293                for (String candidate : fulltextWords) {
294                    if (candidate.startsWith(prefix)) {
295                        match = true;
296                        break;
297                    }
298                }
299                if (!match) {
300                    return false;
301                }
302            } else {
303                if (!fulltextWords.contains(word)) {
304                    return false;
305                }
306            }
307        }
308        return true;
309    }
310
311    // ----- simple parsing, don't try to be exhaustive -----
312
313    private static final Pattern wordPattern = Pattern.compile("[\\s\\p{Punct}]+");
314
315    private static final String UNACCENTED = "aaaaaaaceeeeiiii\u00f0nooooo\u00f7ouuuuy\u00fey";
316
317    private static final String STOPWORDS = "a an are and as at be by for from how "
318            + "i in is it of on or that the this to was what when where who will with "
319            + "car donc est il ils je la le les mais ni nous or ou pour tu un une vous " + "www com net org";
320
321    private static final Set<String> stopWords = new HashSet<String>(split(STOPWORDS, ' '));
322
323    public static final String parseWord(String string) {
324        int len = string.length();
325        if (len < 3) {
326            return null;
327        }
328        StringBuilder buf = new StringBuilder(len);
329        for (int i = 0; i < len; i++) {
330            char c = Character.toLowerCase(string.charAt(i));
331            if (c == '\u00e6') {
332                buf.append("ae");
333            } else if (c >= '\u00e0' && c <= '\u00ff') {
334                buf.append(UNACCENTED.charAt((c) - 0xe0));
335            } else if (c == '\u0153') {
336                buf.append("oe");
337            } else {
338                buf.append(c);
339            }
340        }
341        // simple heuristic to remove plurals
342        int l = buf.length();
343        if (l > 3 && buf.charAt(l - 1) == 's') {
344            buf.setLength(l - 1);
345        }
346        String word = buf.toString();
347        if (stopWords.contains(word)) {
348            return null;
349        }
350        return word;
351    }
352
353    // ----- utility functions -----
354
355    public static Set<String> split(String string) {
356        return split(string, '|');
357    }
358
359    public static Set<String> split(String string, char sep) {
360        int len = string.length();
361        if (len == 0) {
362            return Collections.emptySet();
363        }
364        int end = string.indexOf(sep);
365        if (end == -1) {
366            return Collections.singleton(string);
367        }
368        Set<String> set = new HashSet<String>();
369        int start = 0;
370        do {
371            String segment = string.substring(start, end);
372            set.add(segment);
373            start = end + 1;
374            end = string.indexOf(sep, start);
375        } while (end != -1);
376        if (start < len) {
377            set.add(string.substring(start));
378        } else {
379            set.add("");
380        }
381        return set;
382    }
383
384    private static final String join(Collection<String> strings, char sep) {
385        if (strings == null || strings.isEmpty()) {
386            return "";
387        }
388        int size = 0;
389        for (String word : strings) {
390            size += word.length() + 1;
391        }
392        StringBuilder buf = new StringBuilder(size);
393        for (String word : strings) {
394            buf.append(word);
395            buf.append(sep);
396        }
397        buf.setLength(size - 1);
398        return buf.toString();
399    }
400
401}