Re: [Squirrel-sql-develop] Updating data in the tables

[gwg <gw...@co...>]

Thackston,

Thackston Russell wrote:

> Colin,
> 
> 
>>One other advantage with the "every possible column in the where
>>clause" approach is reducing the risk of losing changes to the
>>database. If a Squirrel user has a row in "maintenance mode" and after
>>they have retrieved the row aother program changes one of the columns
>>then when the Squirrel user generates and executes the update
>>statement it will fail (0 rows updated) as the row will not be found.
>>Contrast this with just using the primary key where the first update
>>will be silently lost.
>>
> 
> For the sake of flexibility, I agree.
> 
> How are you going to handle tables with BLOBs?  If there is no primary key
> in a table and the table contains one or more BLOB fields (which cannot be
> used in a WHERE clause), there will be no reliable way to locate the
> appropriate record to update.  Do we just leave the BLOBs out of the query?
> In which case the user better pay attention to how many rows will be
> updated...
>


Since I've never used BLOBs, your info that they cannot be used in the 
WHERE clause is much appreciated.  My thought is that we would leave 
BLOBs (and CLOBs, if they have the same limitation) out of the WHERE 
clause.  If there are two rows in the DB that differ only in the BLOB 
field, then the user would get a dialog box saying "You are about to 
update N identical rows; Do you want to do this?" and they would have to 
say yes or no.  This should sure the user is aware of what is going on.

By the way, we would do the same thing in the check for creating 
duplicate rows, where any change in a field other than the BLOB might 
create the situation of two rows differing only in the BLOB.  The user 
would be warned about this before we make the change.

I'm curious.  If the DB contains two rows that differ only in a BLOB 
field, how would a user use standard SQL to change only one row?  I'm 
assuming that the DB does not use unique row identifiers (Oracle's rowID 
or Postgres' OID), because that would prevent this from happening 
(remember that I'm proposing to use any such unique pseudo field as part 
of the query/update statement).



> 
> 
>>For the more complex data types (such as image files) this sounds
>>good. I can see some issues coming up with file locking etc. but I'm
>>sure they can be worked around. However I think that defaulting to "in
>>place" editing would be simpler and would cater for most of what
>>people want. Normally if I want to edit a binary column I just want to
>>flip a couple of bits, a form of hex editing would be fine for me.
>>This would mean that for most data maintenance people would't have to
>>configure Squirrel to point to the various external programs. I've
>>found that if you have to configure a option then most people won't
>>use it, they want it to work "out of the box".
>>
> 
> I agree that if you want to flip a bit, then the hex editor would be best.
> However, I think you underestimate the needs and overestimate the skills of
> Squirrel users.  


I don't know about Colin and the rest of the developer community, but my 
target for Squirrel is developers, testers, and DBAs.  I hope that it is 
usable by people with a minimum of DB knowledge who just need to look at 
or touch a couple of items in the DB, then run a test and check the 
results.

That said, I agree that most such users will not have detailed knowledge 
of binary files and formats.  Anything more complicated than twiddleing 
a bit in a BINARY field will probably require an external editor 
designed for that format.  I just believe it is very difficult to do 
that automatically in Java because of the different environments that 
the user may be running in.

> The database I work with contains various graphic images
> (bmp, gif, etc.).  I wouldn't have the faintest idea how to flip a bit in a
> bitmap ;)  I need an editor.  
> 
> It would be annoying to have to open a hex editor, then click an export
> button, choose a location, switch to a file browser, locate the exported
> file, open the file, edit the file, save the file, return to Squirrel,
> import the binary, then commit the changes.  If I had to do all that I
> wouldn't use Squirrel.
> 
> Hmmm.  Maybe the default binary editor *should* be a hex editor.  Maybe it
> would be better to create an enhanced binary editor as a plug-in...
> 


We could certainly add a mechanism that would allow an external editor 
to be associated with a particular column in a table.  Because Squirrel 
is used on both Windows and Linux/Unix systems, I suspect that we would 
need to be pretty general in how we set it up.  Besides letting the user 
select which program to use as the editor for that field, we might also 
need to have the user tell us the command line arguments for that 
program.  Once that is given, we could certainly store the info and 
re-use it each time the user asks to edit that column.


Glenn