Some questions regarding writing DataSource files

[harsha.galla]

Hi,

I have some questions on writing the datasource files(ds.xml files).

1) Can default clauses like $defaultWhereClause etc be used in any part of the operation binding. i.e can $defaultWhereClause be used inside the <tableClause>
of the operating binding.
We need this because we have inner queries.

2) We have lot of recurring logic in most of the clauses in different OperationBinding tags in the ds.xml file. Can this be declared somewhere outside of the OperationBinding and use it as a variable.
This would greatly reduce the recurring logic in ds.xml file for various OperationBindings.
The logic is quite hefty because the columns selected etc. are highly dynamic and seperating them into multiple OperationBindings will bloat it up.

3) In the query constructed I see lot of 'IS NULL' and 'IS NOT NULL' checks on some columns. What is the basis for this. I mean I see it on some columns but not on all. We don't need this. How to get rid of this in the default where clause costruction. Also I see lot of '1'='1' statements. Is there a way to get rid of these.

4) Can we have multiple clauses for where clause. I have a scenario where the query uses UNION. Though this can be split in a manner that part of the query goes into the table clause, it has two different where clauses one for each part. So it possible to generate two different $defaultWhereClauses in this case. I wanted to make use of smartgwt feature of generating query from Criteria objects just like the $defaultWhereClause.


Apart from this, we have highly dynamic queries which are very complex ( with inner queries, grouping at various levels etc, with unions etc.) and this doesn't fall in line with the standard approach of one tableclause, selectclause, etc. I would like to know any tricks in approach. Currently we put the whole of inner query etc in the tableclause itself.

Thanks.

[Isomorphic]

1. Yes

2. Yes, use a Custom DataSource to centralize logic that appears in multiple DMIs (see QuickStart Guide). Also, use dsRequest.addToTemplateContext() to make variables and or generated SQL expressions available to SQL templates.

3. The IS_NULL checks exist to avoid SQL's bizarre and counter-intuitive treatment of null values. The 1=1 checks are there because you submitted criteria on a field that didn't exist or submitted invalid criteria, and so 1=1 was injected as a replacement that would not affect the rest of the logical expression. There is no reason to get rid of either.

4. Yes, use SQLDataSource.getClause() to generate a where clause from a DSRequest containing the second set of criteria, then use dsRequest.addToTemplateContext() to make this second where clause available as a variable you can use in Velocity.

[harsha.galla]

Isomorphic,

Thanks for the response.

2) I have logic used in multiple operation bindings in ds.xml file not in the DMI. So something like ability to use VelociMacros would solve this.
Also we wanted only to leave the where clause construction to outside ds.xml through criteria and retain all other quey parts within the ds.xml file.

I have tried using VelociMacros and I also tried using variables. Both cases gave me invalid character exception.

Is there any other approach for this other than moving it to DMI?

3) I'm ok with '1'='1' but how do I knock of the IS_NULL check. We already have checks in place to ensure that tables have no anomolies. So we dont need this check. Our tables are very data intensive and we try and avoid anything that can cause extra operation in the queries we write.

4) So It can be done using DMI but not on client code. I see those classes only on server docs.

Also I checked the Quick Start Guide. I was surprised to see lot of new stuff in there. Kudos to the team.

Thanks.

[Isomorphic]

2) Again a custom DataSource is a good way to centralize logic that applies to multiple operationBindings

3) It's currently not possible to remove these, but they should have no measurable impact on query performance

4) Yes, SQL is never generated client side (and never should be).

[harsha.galla]

Thanks Isomorphic for the response.

From 2) I need to construct the recurring logic in JAVA using server api and use the variables in the ds.xml file.

3) We can live with it for the time being. It would be good you can make this optional in the future versions.

Also, I had another question regarding DataSources.
We use intermediate cache like JCS in our webapp. This is because our application setup in on servers which have lot of firewalls between application, database, iplanet..etc.etc. So there is a network lag of about 3-4 seconds in some cases hence we use cache wherever we feel necessary(this is generally done for UI population which change based on user interaction and which need to be populated dynamically on the fly). First time we get the data from database and store it in cache. For further interaction we use cache data. I have implemented DMI for the above functionality and it works fine. I would like to know if I can provide a dataset directly and all the Criteria etc. work as it should against a database. What I mean here is, in my implementation I manually search through the dataset and create the appropriate output to the client. Any servers API's which would take the dataset and simulate the search etc behavior sent based on criteria if available would be highly useful.

I have gone through the custom datasource example in the showcase, which mentions sorting etc is not supported by the example which is because the code doesn't seem to have the logic to handle that. So any server api's available which would provide this functionality without me having to write all this logic.

PS:I'm trying to reach the least possible level of writing code, :)

Thanks.

[Isomorphic]

2) yes once again, that's what the CustomDataSource strategy lets you do. Please read the referenced section of the QuickStart Guide and the further docs and samples it leads you to.

About your other question: this has potentially catastrophic performance. When you store a cached dataset "in memory" by, for example, storing it in an HttpSession, what this really means for a clustered deployment is that the entire dataset is being saved and restored to a database on every request, or at least "heartbeated" between in-memory session stores. "Sticky sessions" may prevent this, but only at the expense of losing sessions when a server fails or is taken out of the live cluster.

You also potentially have one cached copy of each cached dataset *per user* unless you pursue a shared cache architecture, which has it's own added complexity.

Having said all this - the API Evaluator.evaluateCriterion() would let you use our server-side libraries to filter (but not sort) in-memory datasets. However, because it's not intended for a usage like you plan, we would not accept performance bugs against this API (just functional bugs).

[curiousgally]

Isomorphic,

We don't have a cluster environment. We have an existing j2ee application(no gwt or smartgwt) which uses the cache. Also we don't store in-session but only in cache which is mostly common for all the users(i.e same across sessions).

The class Evaluator only evalutes if the dataset pertains to a given Criterion. It cannot aid in searching out things etc. (in a dataset identify records which match the criterion and return those records.) Am I right in this.

Thanks.

[Isomorphic]

To search a set of data, combine evaluateCriterion() with iteration.

[curiousgally]

Also, based on your previous suggestions to use the dsRequest.addToTemplateContext() to incorporate other where clauses as variables in ds.xml file. I was trying to construct the advancedCriteria object purely on the server. But I don't find it easy enough like it is supported on the client side class.

In client side I could construct a criteria by passing a array/list for a field value and it would automatically construct the OR field between the items in the array/list. I don't find that in server side criteria. Though SimpleCriterion takes Object as parameter it doesn't do the OR logic as with the client side code.

The only way of achieving this is using the OrCriterion which means we have to manually push the array/list values in the previous case as individual criterion to construct a OR which can then be used along with other criteria in the advanced criteria.

I feel the server side capabilities of AdvancedCriteria are far more limited compared to the client-side one.

Am I missing anything here or is it that way.
Also the previous scenario of SimpleCriterion not taking a list/array, can this be a bug ?



Thanks.

[Isomorphic]

Uhm, so far you've found the lack of one single convenience API that takes ~6 lines of code to replicate. We don't see a basis for a claim that the server-side APIs are "far more limited". Let us know if you discover something substantial.

[curiousgally]

Hm,,

My criteria is simple enough that I can evaluate it myself.
I just wanted to automate it if there is anything in the api to do that. If I have to write the iteration code I would rather prefer evaluating too.:)

Thanks for the info though.

[Isomorphic]

Feel free to write your own code. But for anyone reading this thread - Evaluator.evaluateCriterion() does recursive evaluation of arbitrarily complex criteria, supporting ~30 different operators. It would take thousands of lines of code to replicate, let alone fully test, where Isomorphic maintains automated tests that ensure the results match the client-side filtering system exactly.

[harsha.galla]

Isomorphic,

I'm not trying to say if they are limited etc and I have no thoughts of comparing smartgwt with anything else. We are quite happy with what we have got with smartgwt and we are only trying to learn things. I have given a use case where I have found server side Advancedcriteria to be limited compared to the AdvancedCriteria we have on the client side code. I have provided the use case and requested you to confirm if that is the limitation or if my understanding is wrong. If you think I'm trying to prove you wrong, you are mistaken. I have stated my observation, while trying to construct a AdvancedCriteria on server side. Here too I'm not bothered if there are other API's which are better than smartgwt, I'm comparing it against Smartgwt client-side class. My only concern is what if my user enters 50 airports and I need a OR between them on the server-side. Client side I can just take the list of the airports and pass it to advancedCriteria calling addCriteria method which is not the case with server-side class. I tried passing a array/list to SimpleCriterion but it doesn't work.


Thanks.

[Isomorphic]

Not sure why you wrote all that about other technologies - doesn't seem relevant here and not something we were trying to bring up.

Once again this is the absence of a convenience API that takes very little effort to replicate. So, you asked whether these APIs are "far more limited" as compared to the client-side API, and the answer is no, they are not - this minor convenience API is missing, but this is not an indication of a general problem.