Re: [Squirrel-sql-develop] New plugin planned

The ContentsTab editing mechanism cannot be used as-is in the SQL tab 
because:

- it assumes that only one table it being displayed, and
- it assumes that all columns in the table are being displayed, as well 
as any pseudo-column key fields (e.g. ROWID, OID, etc).

When a cell is edited, it uses a prepared statement of the form
   "UPDATE <table> SET <column>=<value> WHERE <list all fields with 
current values>"
There are some gotchas here, such as fields that cannot be used in WHERE 
clauses, but I'm confident that this will work 99% of the time, and 
those times when there is a problem will be caught (e.g. "This will 
update 3 rows.  Do you wish to proceed?").  This approach was used 
because people told us that the updateable result set feature was not 
implemented in all DBMSs.

While that mechanism is adaquate for the ContentsTab, it obviously won't 
work in the SQL tab because the user can do joins or limit the output to 
only a few columns in a table.  Given that kind of SQL statement, I 
might (or might not - I haven't thought about it) be able to identify 
which table to update, but I'm pretty sure that we would not be able to 
limit the update to the desired row(s).

If the library uses updateable result sets, then we might want to 
include it and find a way to disable editing when that feature is not 
available in the DBMS.  If it uses another mechanism, I'll be interested 
in learning how it works.

In any case, I think it would be nice if we could make the user 
interface work the same for editing the tables in the SQL tab as it is 
in ContentsTab.  The ContentsTab now handles data in a 
data-type-specific way, assists with editing, allows export/import and 
operating on data with an external program, etc.  We can re-use many of 
the classes to do the same thing in the SQL tab, but we will need a 
different mechanism for checking whether the update is safe and for 
actually doing the update.

So that's what's up with the ContentsTab editing and why it can't be 
immediately used for the SQL tab.  I've described these issues in a 
separate email to Stan, and since your note showed an interest I thought 
I'ld fill you in too.

Glenn

Maury Hammel wrote:

> Stanislaw:
> 
>> We have a little library here that we have built, that can be used to 
>> display the results of a select querry in JTable and allows to edit the
>> data, cell-by-cell.
>>
>> I plan to create a plugin out of it.  There is no data edit plugin so
>> far, is there?
> 
> 
> There isn't a plugin for that, but it is similar to what is currently 
> developed in the core of SQuirreL itself (editing from the Contents tab).
> 
> It sounds like your plugin will allow you to edit the results of a SQL 
> statement entered in the SQL tab.  Is that correct?  If that is so, and 
> the Contents tab editing capability can't/isn't planned to be added to 
> the SQL tab results, it would be useful.
> 
> 
> Maury...