Couchbase-lite-2.0 IOS - Querying is not generic anymore.

query

#1

Hi,

Couchbase 1.4.x querying is very generic when compared to 2.x,

1.4.x query builder,

let predicate = NSPredicate(format: "createdby == 'user1' AND (ANY detail.jobcode IN {'Jobcode1', 'Jobcode2’}")
let results = try? CBLQueryBuilder(database: self.database, select:[], wherePredicate: predicate, orderBy: nil).runQuery(withContext: nil)

But, In 2.x it’s different,

let DETAILS = ArrayExpression.variable("DETAILS") 
let DETAILS_JOBCODES = ArrayExpression.variable("DETAILS.jobcode") 
let a = [Expression.string("Jobcode1"), Expression.string("Jobcode2")]
let query = QueryBuilder 
.select(SelectResult.all()) 
.from(DataSource.database(database!)) 
.where(ArrayExpression.any(DETAILS).in(Expression.property("details")).satisfies(Expression.not(DETAILS_JOBCODES.in(a))))

Because, we are using a generic code base for more than 100 queries. So, we are facing a hard time converting the above string’s to expression’s to work with the latest couchbase-lite-2.0 app.

In 1.4.x, I can change this string "createdby == 'user1' AND (ANY detail.jobcode IN {'Jobcode1', 'Jobcode2’}" dymanically from the document and use it in queries. But, we can’t do this in 2.x.


#2

Could you explain the goal a little more clearly? Sure you can change the string dynamically in 1.4.x but I still think that means you have a different query each time. What do you mean by “change the string dynamically” and how do you want to apply that to 2.x?


#3

It’s a more strongly-typed API, yes. This is to reduce the likelihood of creating invalid queries.

You can still construct queries dynamically; you’re just doing it by dynamically connecting together the objects in the builder API, instead of dynamically concatenating strings.


#4

@borrrden We have published only one app in AppStore. So, all the client’s will use the same app, but for each client we have different operation tools in it.

For client1, our queries will be from a Couchbase document like the below,

let selectedToolQueryKey =  "jobtool.jobcode1" --> This will come from what we are selecting.
guard let queryDocument  =  database.document(withID: "genericquerystorageforclient1") ,
               let queryDictionary  =  queryDocument.properties,
               let queryString  = queryDictionary[selectedToolQueryKey] as?  String, 
else { return }

let predicate = NSPredicate(format: queryString)
let results = try? CBLQueryBuilder(database: self.database, select:[], wherePredicate: predicate, orderBy: nil).runQuery(withContext: nil)

My query document,

 {
      "_id": "genericquerystorageforclient1",
      "syncchannel": "client1channel",
      "syncuser": "client1user",
      "queries": {
        "jobtool": {
          "jobcode1": "createdby == 'appuser' AND detail.jobcode = 'jobcode1'",
          "jobcode2": "createdby == 'appuser' AND detail.jobcode = 'jobcode2'",
          "jobcode3": "createdby == 'appuser' AND detail.jobcode = 'jobcode3'",
          "default": "createdby == 'appuser' AND detail.jobcode != ''",
          "hoursdashboardtool": {
            "dashboardblock1": "createdby == 'appuser' AND totalworkedhours >= '8.00'",
            "dashboardblock2": "createdby == 'appuser' AND totalworkedhours < '8.00'",
            "dashboardblock3": "createdby == 'appuser' AND detail.@sum.workedhours"
          },
          "prioritydashboardtool": {
            "priority1": "jobstatus == 'notcompleted' AND jobpriority = 'High'",
            "priority2": "jobstatus == 'notcompleted' AND jobpriority = 'Medium'",
            "priority3": "jobstatus == 'notcompleted' AND jobpriority = 'Low'"
          }
        }
      }
    }

Like the above we have different queries for each of the client. Also, I have added only one tool, but we have more than 30 tool like this in our apps. I can’t post everything here.

Even, I can make more complex queries,

NSPredicate(format: "createdby == 'user1' AND (ANY detail.jobcode IN {'Sick', 'Vacation’})")
NSPredicate(format: "createdby == 'user1' AND NOT (ANY detail.jobcode IN {'Sick', 'Vacation’})")

#5

@jens Yeah, We can construct the queries dynamically in couchbase2.x. But, it not that easy, Please take a look at the above reply and let me know How to construct that dynamically in 2.x?


#6

We do actually use an internal string-based (JSON) syntax for queries, but it’s not exposed in the API.

There’s also an unused piece of code that translates an NSPredicate to this syntax.

Paging our PM @priya.rajagopal — here’s a use case for exposing JSON queries.


#7

@jens Oh Okay, Can I know what is the status of this request?. Because we are planning to release our app with couchbase 2.0 SDK.


#8

There are no plans to expose the internal API at this time. It is a future consideration. Please use the approach you are specified earlier for constructing your query.


#9

Okay, Then can you tell me how to convert the above queries string to expressions in couchbase 2.0?

Please use the approach you are specified earlier for constructing your query.

The above approach is for only one query, but for all the queries what do I need to do. I can’t harcode my query expressions all over the project. Also, it will be changed dynamically based on the requirement.


#10

You will have to parse those strings and programmatically generate queries using the 2.0 API.


#11

Sorry, But can you be more specific about parsing query strings. Do, I need to split the string into components based on spaces or something else.
If you don’t mind can you tell me how to parse this query string, "createdby == 'user1' AND NOT (ANY detail.jobcode IN {'Sick', 'Vacation’})".


#12

You can use NSExpression and NSPredicate to parse those.


#13

@jens How to do that? Please give me an example or just a piece of code, Thanks.


#14

I’m sorry, but this isn’t a Cocoa support forum; we don’t have the bandwidth to help with this. There is plenty of Apple documentation about those classes, and information to be found on StackOverflow and other sites.


#15

@jens We have found a generic way to create expressions by using the predicate strings.


#16

Just my $.02, but this does seem like a harsh change to push onto people with no forewarning.

I see arguments for both. How hard would it be to really continue to accept query strings along with the strongly typed API – would be best of both worlds. That would allow this poor guy to not have to refactor a lot of code, and people that want to store and utilize their queries in a string format to continue to do so, and others to use the new strongly typed API.


#17

You are going to have to refactor a lot of code anyway for 2.0 considering it was a complete rewrite. We also had 23 preview releases of the 2.0 API so it’s hardly fair to say it was “without warning”.


#18

Fair point, but it seems if you’re already using internal string based queries, and the original API supported string queries, it wouldn’t be too much of a leap to open that back up alongside the typed query api?


#19

We are not using anything based on NSPredicate internally. It also needs to translate itself into our internal format, which is based on JSON.


#20

Understood – it may not be in the cards, but from a client facing perspective, there are a lot of reasons why supporting a string based query api is beneficial. One is we can load the queries dynamically easily. They are also easier to parse, write and understand as a human. The value in strongly typed query API is that it compiles, but that doesn’t mean the query works or won’t throw an exception, it just means it is valid syntax, but there are tools and ways one can validate the syntax of SQL/NIQL outside of the compiler. Just $.02 feedback FWIW.