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

  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}