/src/mpv5/db/common/DatabaseSearch.java
Java | 429 lines | 231 code | 31 blank | 167 comment | 47 complexity | 7279c6a49180eec0e4c2715006288fe4 MD5 | raw file
1package mpv5.db.common; 2 3import java.util.ArrayList; 4import java.util.Arrays; 5import java.util.Collections; 6import java.util.Date; 7import java.util.List; 8import java.util.Set; 9import java.util.TreeSet; 10import java.util.logging.Level; 11import java.util.logging.Logger; 12import mpv5.db.objects.Group; 13import mpv5.db.objects.User; 14import mpv5.logging.Log; 15import mpv5.usermanagement.MPSecurityManager; 16import mpv5.utils.arrays.ArrayUtilities; 17 18/** 19 * 20 * Convenience class for database searches. <br/>Does <b>NOT</b> automatically 21 * take care of multi-client capability needs. 22 */ 23public class DatabaseSearch { 24 25 public static String ALL_COLUMNS = "*"; 26 private final Context context; 27 private int ROWLIMIT = 0; 28 29 /** 30 * Initiates a new search 31 * 32 * @param context 33 */ 34 public DatabaseSearch(Context context) { 35 this.context = context; 36 } 37 38 /** 39 * Initiates a new search 40 * 41 * @param obj The DBOBJ the search will rely on 42 */ 43 public DatabaseSearch(DatabaseObject obj) { 44 this.context = obj.getContext(); 45 context.setDbIdentity(obj.getDbIdentity()); 46 context.setSubID(Context.DEFAULT_SUBID); 47 } 48 49 /** 50 * Initiates a new search and allows to limit the resulting rows 51 * 52 * @param context 53 * @param rowlimit 54 */ 55 public DatabaseSearch(Context context, int rowlimit) { 56 this.context = context; 57 this.ROWLIMIT = rowlimit; 58 } 59// /** 60// * Initiates a new search and allows to limit the resulting rows 61// * @param rowlimit 62// */ 63// public DatabaseSearch(int rowlimit) { 64// this.ROWLIMIT = rowlimit; 65// } 66 67 /** 68 * Get multiple values from a search 69 * 70 * @return select("*", null); 71 */ 72 public Object[][] getValuesFor() { 73 return QueryHandler.instanceOf().clone(context, ROWLIMIT).select("*", (String[]) null); 74 } 75 76 /** 77 * Do a fulltextsearch 78 * 79 * @param val 80 * @return The result 81 * @deprecated SLOW 82 */ 83 @Deprecated 84 @SuppressWarnings("unchecked") 85 public Object[][] getFulltextSearchvaluesFor(String val) { 86 Log.Debug(this, "Fulltextlookup for " + val); 87 ArrayList<Context> contexts = Context.getSearchableContexts(); 88 Object[][] data = new Object[][]{}; 89 for (int i = 0; i < contexts.size(); i++) { 90 try { 91 Context contx = contexts.get(i); 92 ReturnValue rdata = QueryHandler.instanceOf().freeQuery(QueryHandler.instanceOf().clone(contx, ROWLIMIT).buildQuery(val, DatabaseObject.getObject(contx).getStringVars().toArray(new String[]{})), MPSecurityManager.VIEW, null); 93 Object[][] ndata = rdata.getData(); 94// Log.PrintArray(ndata); 95 Object[] idcol = new Object[ndata.length]; 96 for (int j = 0; j < idcol.length; j++) { 97 idcol[j] = new DatabaseObject.Entity(contx, Integer.valueOf(ndata[j][0].toString())); 98 } 99 ndata = ArrayUtilities.replaceColumn(ndata, 0, idcol); 100 data = ArrayUtilities.merge(data, ndata); 101 } catch (Exception e) { 102 Log.Debug(e); 103 } 104 } 105 return data; 106 } 107 108 /** 109 * Do a fulltextsearch 110 * 111 * @param val 112 * @return The result 113 */ 114 @SuppressWarnings("unchecked") 115 public List<Integer> searchObjectIdsFor(String val) { 116 Log.Debug(this, "searchObjectIdsFor for " + val); 117 List<Integer> l = new ArrayList<Integer>(); 118 try { 119 Context contx = context; 120 ReturnValue rdata = QueryHandler.instanceOf().freeQuery(QueryHandler.instanceOf().clone(contx, ROWLIMIT).buildIdQuery(val, DatabaseObject.getObject(contx).getStringVars().toArray(new String[]{})), MPSecurityManager.VIEW, null); 121 Object[] ndata = rdata.getFirstColumn(); 122 123 if (ndata != null) { 124 for (int j = 0; j < ndata.length; j++) { 125 l.add(Integer.valueOf(ndata[j].toString())); 126 } 127 } 128 129 } catch (Exception e) { 130 Log.Debug(e); 131 } 132 133 return l; 134 } 135 136 /** 137 * 138 * @param val 139 * @return 140 * @throws NodataFoundException 141 */ 142 public List<DatabaseObject> searchObjectsFor(String val) throws NodataFoundException { 143 List<Integer> data = searchObjectIdsFor(val); 144 return DatabaseObject.getObjects(context, data); 145 } 146 147 /** 148 * Get multiple values from a search 149 * 150 * @param resultingFieldNames What do you like to get (columns)? 151 * @return 152 */ 153 public Object[][] getValuesFor(String resultingFieldNames) { 154 return QueryHandler.instanceOf().clone(context, ROWLIMIT).select(resultingFieldNames, new String[]{null, "", "'"}); 155 } 156 157 /** 158 * Get multiple values from a search, where the search column is a number 159 * column 160 * 161 * @param resultingFieldNames What do you like to get (columns)? 162 * @param what Which column do you like to take for the condition? 163 * @param value 164 * @return 165 */ 166 public Object[][] getValuesFor(String resultingFieldNames, String what, Number value) { 167 return QueryHandler.instanceOf().clone(context, ROWLIMIT).select(resultingFieldNames, new String[]{what, value.toString(), ""}); 168 } 169 170 /** 171 * Get multiple values from a search, where the search column is a String 172 * column 173 * 174 * @param resultingFieldNames What do you like to get (columns)? 175 * @param what Which column do you like to take for the condition? 176 * @param value 177 * @return 178 */ 179 public Object[][] getValuesFor(String resultingFieldNames, String what, String value) { 180 return getValuesFor2(resultingFieldNames, value, null, false, false, new String[]{what}); 181 } 182 183 /** 184 * Get multiple values from a search 185 * 186 * @param resultingFieldNames What do you like to get (columns)? 187 * @param possibleColumns Which columns do you like to take for the 188 * condition? 189 * @param where And what value should the column value have? 190 * @return 191 */ 192 public Object[][] getValuesFor(String resultingFieldNames, String[] possibleColumns, Number where) { 193 try { 194 ArrayList<Object[]> list = new ArrayList<Object[]>(); 195 QueryCriteria2 c = new QueryCriteria2(); 196 ArrayList<QueryParameter> l = new ArrayList<QueryParameter>(); 197 for (int i = 0; i < possibleColumns.length; i++) { 198 String string = possibleColumns[i]; 199 l.add(new QueryParameter(context, string, where, QueryParameter.EQUALS)); 200 } 201 c.or(l); 202 list.addAll(Arrays.asList(QueryHandler.instanceOf().clone(context, ROWLIMIT).select(resultingFieldNames, c).getData())); 203 204 return list.toArray(new Object[][]{}); 205 } catch (NodataFoundException ex) { 206 return new Object[0][0]; 207 } 208 } 209 210 /** 211 * Get multiple values from a search. Will split word by whitespace 212 * 213 * @param resultingFieldNames What do you like to get (columns)? 214 * @param possibleColumns Which columns do you like to take for the 215 * condition? 216 * @param where And what value should the column value have? 217 * @param searchForLike Shall we search with "like" condition? 218 * @return 219 */ 220 public Object[][] getValuesFor(String resultingFieldNames, String[] possibleColumns, String where, boolean searchForLike) { 221 return getValuesFor2(resultingFieldNames, where, null, true, searchForLike, possibleColumns); 222 } 223 224 /** 225 * Get multiple values from a search, ignores reference tables and is 226 * herewith faster 227 * 228 * @param resultingFieldNames What do you like to get (columns)? 229 * @param possibleColumns Which columns do you like to take for the 230 * condition? 231 * @param groups 232 * @param splitByWhitespace 233 * @param search And what value should the column value have? 234 * @param searchForLike Shall we search with "like" condition? 235 * @return 236 */ 237 public Object[][] getValuesFor2(String resultingFieldNames, String search, List<Group> groups, boolean splitByWhitespace, boolean searchForLike, String... possibleColumns) { 238 QueryCriteria2 qc = new QueryCriteria2(); 239 if (possibleColumns == null || possibleColumns.length == 0) { 240 possibleColumns = DatabaseObject.getObject(context).getStringVars().toArray(new String[0]); 241 } 242 243 if (search != null) { 244 List<List<QueryParameter>> ps = new ArrayList<List<QueryParameter>>(); 245 List<QueryParameter> not = new ArrayList<QueryParameter>(); 246 String[] strings = null; 247 if (splitByWhitespace) { 248 strings = search.split("\\s+"); 249 } else { 250 strings = new String[]{search}; 251 } 252 for (int i = 0; i < strings.length; i++) { 253 ArrayList<QueryParameter> psx = new ArrayList<QueryParameter>(); 254 ps.add(psx); 255 String string = strings[i]; 256 for (String en : possibleColumns) { 257 if (!string.startsWith("-")) { 258 psx.add(new QueryParameter(context, en, string, searchForLike ? QueryParameter.LIKE : QueryParameter.EQUALS)); 259 } else { 260 not.add(new QueryParameter(context, en, string.substring(1), searchForLike ? QueryParameter.LIKE : QueryParameter.EQUALS)); 261 } 262 } 263 } 264 for (int i = 0; i < ps.size(); i++) { 265 qc.or(ps.get(i)); 266 } 267 qc.and(not); 268 } 269 List<QueryParameter> ps = new ArrayList<QueryParameter>(); 270 if (User.getCurrentUser().isGroupRestricted()) { 271 ps.add(new QueryParameter(context, "groupsids", User.getCurrentUser().__getGroupsids(), QueryParameter.EQUALS)); 272 } else if (groups != null && !groups.isEmpty()) { 273 for (int i = 0; i < groups.size(); i++) { 274 Group group = groups.get(i); 275 ps.add(new QueryParameter(context, "groupsids", group.__getIDS(), QueryParameter.EQUALS)); 276 } 277 } 278 qc.or(ps); 279 try { 280 return QueryHandler.instanceOf().clone(context).select(resultingFieldNames, qc).getData(); 281 } catch (NodataFoundException ex) { 282 Log.Debug(search, ex.getMessage()); 283 return new Object[0][0]; 284 } 285 } 286 287 /** 288 * Get multiple values from a search 289 * 290 * @param resultingFieldNames What do you like to get (columns, comma 291 * separated)? 292 * @param what Which column do you like to take for the condition? 293 * @param where And what value should the column value have? 294 * @param searchForLike Shall we search with "like" condition? 295 * @return 296 */ 297 public Object[][] getValuesFor(String resultingFieldNames, String what, String where, boolean searchForLike) { 298 QueryCriteria2 c = new QueryCriteria2(); 299 c.and(new QueryParameter(context, what, where, searchForLike ? QueryParameter.LIKE : QueryParameter.EQUALS)); 300 try { 301 return QueryHandler.instanceOf().clone(context, ROWLIMIT).select(resultingFieldNames, c).getData(); 302 } catch (NodataFoundException ex) { 303 Log.Debug(this, ex.getMessage()); 304 return new Object[0][0]; 305 } 306 } 307 308// 309// /** 310// * Get a single dimension list from a search after values from the column 311// * where the value is LIKE the given needle 312// * @param what Which column do you like to get and search through? 313// * @param needle 314// * @return 315// * @throws NodataFoundException If no data was found matching your search 316// */ 317// public Object[] searchFor(String what, String needle) throws NodataFoundException { 318// return searchFor(null, what, needle); 319// } 320 /** 321 * Get a single dimension list from a search after values from the column 322 * where the value is LIKE the given needle 323 * 324 * @param columns Which columns to get? 325 * @param what Which column do you like to search through? 326 * @param needle 327 * @param exactMatch 328 * @return 329 * @throws NodataFoundException If no data was found matching your search 330 */ 331 public Object[] searchFor(String[] columns, String what, String needle, boolean exactMatch) throws NodataFoundException { 332 Object[] data = QueryHandler.instanceOf().clone(context, ROWLIMIT).getValuesFor(columns, what, needle, exactMatch); 333 if (data == null || data.length == 0) { 334 throw new NodataFoundException(); 335 } else { 336 return data; 337 } 338 } 339 340 /** 341 * Get a single dimension list from a search after values from the column 342 * where the value is LIKE the given needle 343 * 344 * @param columns Which columns to get? 345 * @param what Which column do you like to search through? 346 * @param needle 347 * @return 348 * @throws NodataFoundException If no data was found matching your search 349 */ 350 public Object[] searchFor(String[] columns, String what, String needle) throws NodataFoundException { 351 Object[] data = QueryHandler.instanceOf().clone(context, ROWLIMIT).getValuesFor(columns, what, needle, false); 352 if (data == null || data.length == 0) { 353 throw new NodataFoundException(); 354 } else { 355 return data; 356 } 357 } 358 359 /** 360 * 361 * @param ext 362 * @param self 363 * @param value 364 * @return 365 */ 366 public List<Integer> searchObjectIdsFor(Context[] ext, Context[] self, String value) { 367 Log.Debug(this, "Search parameter: " + value); 368 Set<Integer> data = new TreeSet<Integer>(); 369 370 for (Integer s : new DatabaseSearch(context, 50).searchObjectIdsFor(value)) { 371 data.add(s); 372 } 373 374 for (Context exct : ext) { 375 376 String subitemids = "0"; 377 for (Integer s : new DatabaseSearch(exct, 50).searchObjectIdsFor(value)) { 378 subitemids = s + "," + subitemids; 379 } 380 381 final String x = Context.getAliasFor(context, exct); 382 Object[] sdata = QueryHandler.instanceOf().clone(context).freeQuery("select " + x + "ids from " + exct.getDbIdentity() + " where ids in(" + subitemids + ")", MPSecurityManager.VIEW, null).getFirstColumn(); 383 if (sdata != null) { 384 for (int i = 0; i < sdata.length; i++) { 385 try { 386 data.add(Integer.valueOf(sdata[i].toString())); 387 } catch (NumberFormatException numberFormatException) { 388 Log.Debug(numberFormatException); 389 } 390 } 391 } 392 } 393 for (Context selfc : self) { 394 String contactsids = "0"; 395 for (Integer s : new DatabaseSearch(selfc, 50).searchObjectIdsFor(value)) { 396 contactsids = s + "," + contactsids; 397 } 398 Object[] sdata = QueryHandler.instanceOf().clone(context).freeQuery("select ids from items where " + selfc.getDbIdentity() + "ids in(" + contactsids + ")", MPSecurityManager.VIEW, null).getFirstColumn(); 399 if (sdata != null) { 400 for (int i = 0; i < sdata.length; i++) { 401 try { 402 data.add(Integer.valueOf(sdata[i].toString())); 403 } catch (NumberFormatException numberFormatException) { 404 Log.Debug(numberFormatException); 405 } 406 } 407 } 408 } 409 410 return new ArrayList<Integer>(data); 411 } 412 413 /** 414 * 415 * @param sf 416 * @param ext 417 * @param self 418 * @param value 419 * @return 420 */ 421 public Object[][] searchDataFor(String sf, Context[] ext, Context[] self, String value) { 422 List<Integer> data = searchObjectIdsFor(ext, self, value); 423 String dboids = "0"; 424 for (Integer id : data) { 425 dboids = id + "," + dboids; 426 } 427 return QueryHandler.instanceOf().clone(context).freeQuery("select " + sf + " from %%tablename%% " + context.getConditions(false) + " AND ids in (" + dboids + ") AND " + context.getNoTrashSQLString() , MPSecurityManager.VIEW, null).getData(); 428 } 429}