SQLDataSource, ListGrid advanced usages

[eyal]

Hello,
My organization considers buying SmartGWT Enterprise Edition, so I've downloaded the demo version and started writing a Proof of Concept to prove we really need it.

I've run into some issues which I hope you can solve for me, and speed the purchase process:
1. I'm using a ListGrid with an SQLDataSource to fetch and present data from the DB. I would like to add an extra Where clause string to the DSRequest the listGrid sends. So far I only managed to add it through a custom DataSource that inherits the SQLDataSource and adds the where clause, but it's patchy and I want the client to send it, not the server. so in short - How can i add the where clause string?
2. My DB table contains RAW and BLOB types. these two have to be mapped to 'binary' fieldType. the problem is that it forces me to create 3 additional fields per field (x_filename, x_size, x_date_created) that will be of no use for me. Also, i'd like the short RAW field to be presented as Hex-string, and to be converted during fetch and store.
So this questions is composed of a few:
a. do you have a workaround for that?
b. can i extend your code to support this feature?
c. can i raise this as a feature request? how long does it usually take?
3. Generating ForeignKeys: Using the DataSource Wizard doesn't discover my foreign keys, is it supposed to?
4. Using fetchRelated between 2 ListGrids and DataSources selects everything from the related DS.
5. I want to dynamically join 2 data sources in the same list grid, how is that possible?
6. Do you have testing methodology papers, design best practices? We planned on using GWT's MVP design pattern, but the way your object work interferes with it

Thank you for your help!

[Isomorphic]

1. use SQL Templating. Note, you don't want to send actual literal SQL from the client (which would be a security hole). Here's one sample, see also the others in this folder.

2. For the RAW field you want rendered as hex, you could use either SQL Templating or a DMI (see this sample) to format the value on it's way out of the database and modify it on the way in.

Note there's a feature about to land in mainline (perhaps next week) that makes this easier: dataSourceField.sqlSelectExpression, sqlUpdateExpression, sqlInsertExpression for controlling SQL on a per-field basis.

For the BLOB field, what handling do you want? The default handling is basically about uploading and downloading files to SQL - those extra fields are metadata needed to do this (eg, show a label for the field).

3. Not yet. This is planned but there's no ETA. Consider Feature Sponsorship to get this done right away and be sure to name what database(s) you need it for.

4. Something's wrong with the DataSource declarations or fetchRelatedData() call. The most common error is to pass the DataSource the grid already has, instead of the related DataSource.

5. SQL Templating or custom logic again - see first response.

6. Use Selenium or SOASTA for function testing. If you want to do mocking, someone has recommended jmockit but we haven't used it.

Bear in mind, it's not exactly that we "interfere" with MVP. MVP presupposes you are going to write reams and reams and reams of code for connecting components to data models, when they are already connected to data models in SmartGWT, with very sophisticated built-in behaviors. See for example ResultSet.

[eyal]

Thank you for the quick reply.

SQL Templating doesn't suit our needs, as its static and we need to be more dynamic.
Our users need to view data in many combinations that we cannot expect beforehand.
So what we need is to join in the listGrid based on whatever foreign-key they choose, and run custom SQL Where (nothing fancy, usually just "modification date > systdate -3" etc.).
We're aware of the security hole, but their queries are being sanitized, and they lack permissions to cause accidental damage.

About BLOBS - i'm just not interested in that data and don't want unneccesary field in my DB, I try to keep it minimal.

[Isomorphic]

It doesn't sound like you've really absorbed the capabilities of the SQL system itself and of SQL templating - a few things you may have missed:

1. the SQL subsystem is already capable of generating arbitrarily complex where clauses via the AdvancedCriteria system, including cross-table criteria. See also the FilterBuilder widget.

2. You can modify the dsRequest in Java before it reaches the SQL subsystem. You can add additional data and objects accessible to the SQL template (addToTemplateContext())

3. the SQL templates are Velocity templates. They can call methods on objects available via the template context, and can do conditional logic and iteration in-line

4. if tables are being dynamically generated and modified at runtime, DataSource.fromXML() lets you dynamically define a DataSource that can query a new table, and the APIs of the Batch DataSource Generator allow you to dynamically derive a DataSource definition from a table (on the fly).

So, basically nothing in the entire SQLDataSource / SQL Templating system is static.

About BLOBs, the question is really what you want to do with them - the default additional fields that the SQLDataSource uses are required for the upload/download/preview behaviors you see in ListGrid / DynamicForm / etc. If you don't need those behaviors, don't declare the BLOB in your DataSource at all, and access it directly to do whatever you want.

[lior]

Regarding blobs - we do need to have this behavior (download..) but we believe that those fields are useless. can we make anything to make smart ignore them or believe they exist without really having them?

[lior]

Regarding FK / Joined grids.

So we still having some issues unsolved:

a. if we define two DS that one points to another by FK - and we want both to enable free "where clause" querying on them and/or join them from one grid to another (by D&D or something like this) but we don`t want to manually define all binding operations by ourselves. How is that possible?

b. fetchRelatedData - still not working. but we saw a different example that takes the JSObjects convert them to a Record and than makes a criteria out of it. we wrote this generic code by ourself. isn't suppose to be that method who takes care for it?
(http://www.smartclient.com/smartgwtee/showcase/#master_detail_batch this gave us the idea..)

c. whereCluase - the way we make it work is by having a custom DS that takes the whereCluase from the request injects it instead of the $defaultWhereClause and removes it after execution. this is ugly and we prefer to have it in a better way (both for its ugliness and that it makes us deal with a server code logic..)

[Isomorphic]

On blobs - doesn't really seem like you've thought through the requirements for fulfilling the download behaviors - for example, to efficiently stream data, you need to know the total size of the data to set the HTTP Content-Length header. And you need to know a filename that the user will see in a "Save As.." dialog. But you can provide this information yourself via writing a DMI.

a) each DataSource would currently need a single operationBinding for "fetch", no other bindings are required (see samples). However if you find even this too burdensome, consider sponsoring a feature to have automatic joins across FKs be something you can enable declaratively.

b) try posting sample code. We can't correct your code if you don't post it.

c) not sure what this refers to. What were you working around?

[lior]

BLOBS - we don`t care about both, filename and transfer rates are not a concern to us.

a - right now we only show records, but in the near future - we need to edit it too.
if we do - how long will it take?

b - will do tomorrow

c - question 1 from original post

[Isomorphic]

BLOBS: once again, the built-in behaviors require a length and a filename. If you don't want the built-in behaviors, you don't have to use them, but if you do want the built-in behaviors, then you need a length and a filename. If you think there's some kind of third possibility here, please articulate it in detail.

a) no work is required

c) question 1 was already answered:

1. use SQL Templating. Note, you don't want to send actual literal SQL from the client (which would be a security hole). Here's one sample, see also the others in this folder.

.. just send the data you need in the where clause from the client, then add a SQL template that uses the data.

[lior]

Thanks for the quick replies!

BLOBS - we wish to use it in our way (we don`t want to write this code). I asked here to check if you have a third way..


a - i meant - once we ask for this feature how long will it take until it released.

c - we can`t use SQL templateing, and all we want is to enable our users to write the where clause (on one DS or more..)


If we could use the built in features or what you've already suggest - we had.
But it doesn't serves our product the best

[Isomorphic]

BLOBS: to spell it out, you would like SQL support for automatically storing BLOBS where:
- Content-Length is omitted in the HTTP response OR the entire file is first streamed into memory in Java, so it's length can be measured

- On download, the file is always given some placeholder name like "file.txt"

a. there doesn't seem to be a feature enhancement here. canEdit:true will cause adds and updates against the primary table being viewed. Did you have something else in mind like insert/update against multiple SQL tables from editing one DataSource?

c. to really spell it out, if you're going to generate your own SQL where clause, use addToTemplateContext() to add it to the SQL template as an available variable (eg "myWhereClause"), and your template can be just <whereClause>$myWhereClause</where>

[u4ibm]

Blobs:
Just to wrap the subject, as this point is not really critical-
As we said, the filename is not really important in storing or fetching.
When fetching the blob, filesize can be queried from the database, there is no need to read the entire binary for that.
The create_date really doesn't seem useful, as we don't see it (maybe when downloading the file?).
* What is your position about CLOB fields? are they handled like binaries?

DataSource inheritance:
I'll try to explain what we're looking for by describing our data model and our current database-editor, and hopefully you can say how to use SmartGWT to do it.
We have an hierarchical data model, where we have a base type, and several derived types (for example - File, and Image,Music,Video derived types with specific fields for them).
This bases on a Star-Scheme. For example, an Image record: The File related fields of a record are in the File table. The Image fields are in the Image Table, and the PK is a FK to the rest of the record in the File table.

Our current editor presents the data based on this model, and can represent the entire Image record in one row. When editing a field in the record, it can be mapped to know which table to update. inserts and deletes are the same, based on the order of relations.

We thought the DataSource's "inheritsFrom" was on the way there because it presented the record completely, until:
1. It couldn't update fields at all.
2. If the base type had a binary field, the supporting field (filesize etc) were searched at the derived type's table.

Thank you very much for your replies. You are helping us prove to our Team Leaders that smartGWT will save development time.