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}