PageRenderTime 53ms CodeModel.GetById 11ms RepoModel.GetById 0ms app.codeStats 0ms

/src/mpv5/db/common/DatabaseSearch.java

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