Feature idea for the squidb package; Filtering API

[dotWee]

I'm not very active in the SQL scene and also don't know much about its principles (maybe there already exists something like that?), but I find myself writing methods similar to the following:

    /**
     * This method returns a filtered cursor, using given properties and a string query.
     *
     * @param properties the query should equal / contain. If null, default cursor will be returned.
     *
     * @param query What to look for. If null, default cursor will be returned.
     *
     * @param exactly If false, the LIKE operator instead of EQUALS will be used and the global search using "%" will be enabled.
     *
     * @return A filtered cursor, or 'normal' cursor if query is null.
     */
    @Override
    public SquidCursor<T> filtered(@Nullable Property[] properties, @Nullable String query, boolean exactly) {
        SquidCursor<T> squidCursor;

        try {
            if (properties == null) {
                throw new IllegalArgumentException("Properties are null or empty!");
            }

            if (query == null) {
                throw new IllegalArgumentException("Query is null!");
            }

            if (!exactly) {
                query = "%" + query + "%";
            }

            Log.i(LOG_TAG, "string to look for: " + query);
            Criterion mergedCriterion = properties[0].like(query);

            if (properties.length >= 1) {
                for (int i = 1; i < properties.length; i++) {
                    mergedCriterion = mergedCriterion.or(
                            exactly ? properties[i].eq(query) : properties[i].like(query)
                    );
                }
            }

            squidCursor = providedSquidDatabase.query(modelClass, Query.select().where(mergedCriterion));
        } catch (IllegalArgumentException e) {
            e.printStackTrace();

            squidCursor = providedSquidDatabase.query(modelClass, Query.select());
        }

        if (squidCursor != null) {
            Log.i(LOG_TAG, "cursor size: " + squidCursor.getCount());
        }

        return squidCursor;
    }

[jdkoren]

To me this appears a bit too usage-specific for a general API. For all intents and purposes, this function really only builds a Criterion; I'm happy to leave the details of that to the developer, particularly for something like filtering, which seems straightforward but can actually be rather complicated.

[dotWee]

Alright, was just a thought. Issue closed.

[jdkoren]

@dotWee I may be reading a bit much into your example, but if you are trying to implement text search across multiple columns that may have lots of content (e.g. finding documents with a specific word in the title, body, etc.), maybe FTS with a virtual table is something to consider for your use case? Android SQLite binaries include the FTS4 extension and we support this in SquiDB using the virtualModule argument of the @TableModelSpec annotation -- just be aware this creates a virtual table, not a regular one, so its behavior is governed by the module and not the typical SQLite rules (for example, with FTS4 all columns are text columns, column constraints do not work, etc).

[dotWee]

Haven't heard about SQLite's virtual table modules until now. It seems a bit overkill since I'm only searching through a small number of columns with the average content-length of 100 chars.

Thanks for recommendation though!