|
From: Thackston R. <r.t...@le...> - 2002-10-04 15:25:36
|
Some comments...
Glenn said,
> Simply put, I won't need to locate the Primary Keys because I will
> always use every column of the row in the WHERE clause, so any columns
> that have been designated as Primary Keys will be included
> automatically. <snip>
Updating of rows should not be done this way (in my opinion). We should
check for primary keys and, if supported, allow the user to update the
record, matching on the primary key. If no primary keys are defined, then
the table is not editable. This would encourage good database design and
ensure reliable results. This seems to be the approach taken by most
commercial tools I have seen.
Glenn said,
> <snip> My suggestion is that when the user clicks
> on a binary field that we pop up a small window containing the value in
> a editable text field and a set of radio buttons that lets the user
> view/edit the data as:
> - bits ("10110011" - I would put spaces between each byte)
> - hex ("B3")
> - octal ("1 7 3" - I'm not sure of the best byte alignment here)
> - ascii (i.e. as the ascii chars for those nybbles)
I suggest when a user tries to edit a binary field, we pop up a window that
prompts the user for the type of binary... bitmap (*.bmp), Word document
(*.doc), Rich text file (*.rtf), etc. Think plug-ins, user-defined list,
etc. The user selects the data type; Squirrel then writes the file to a
temp file and opens the file using a "default editor."
For example...
Bitmap (*.bmp) = C:\Program Files\Accessories\Paint.exe
Word document (*.doc) = C:\Program Files\Office\Word.exe
Rich Text file (*.rtf) = C:\Program Files\OpenOffice\OpenOffice.exe
Raw binary (*.bin) = C:\Program Files\pfe\pfe.exe
...
Glenn said,
> I'd like to toss out another open issue here: Should we consider adding
> an "Undo"/"Redo" mechanism to Squirrel? If so, then editing of the data
> would be one thing that should be tied into that mechanism.
Let the user start and end sessions, maybe? They could, at any time, "undo"
their changes by rolling back an open session.
In fact, if we combine editing and sessions, it could work something like
this...
1. Open a table.
2. Start a session, which makes the table editable (checking for primary
keys).
3. Move around the table making changes, which are written to the DB, but
not committed.
4. Commit or rollback the session (table is no longer editable).
For a step-by-step undo feature, we could build a stack for SQL statements
that mirror each change made (in step 3). Call it anti-SQL :) Triggering
an undo would execute the top item on the stack.
Russell Thackston
|
|
From: Colin B. <col...@te...> - 2002-10-09 03:56:10
|
Hi Russell, > Glenn said, > > Simply put, I won't need to locate the Primary Keys because I > will > > always use every column of the row in the WHERE clause, so any > columns > > that have been designated as Primary Keys will be included > > automatically. <snip> > > Updating of rows should not be done this way (in my opinion). We > shouldcheck for primary keys and, if supported, allow the user to > update the > record, matching on the primary key. If no primary keys are > defined, then > the table is not editable. This would encourage good database > design and > ensure reliable results. This seems to be the approach taken by most > commercial tools I have seen. The problem with encouraging good database design is that the people who can be encouraged are already doing it. The rest, well... 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. Also remember that people may be using Squirrel over databases that they didn't create and have no authority to change the structure. I wouldn't like the idea that we are saying that Squirrel will only work if your database is perfect. However it gets done I would suggest doing a "select count(*)" prior to the actual update just to check that only a single row gets updated. I'd hate for somebody to be screaming about Squirrel updating every row in a table when he only meant to update 1 row. > I suggest when a user tries to edit a binary field, we pop up a > window that > prompts the user for the type of binary... bitmap (*.bmp), Word > document(*.doc), Rich text file (*.rtf), etc. Think plug-ins, > user-defined list, > etc. The user selects the data type; Squirrel then writes the > file to a > temp file and opens the file using a "default editor." > > For example... > Bitmap (*.bmp) = C:\Program Files\Accessories\Paint.exe > Word document (*.doc) = C:\Program Files\Office\Word.exe > Rich Text file (*.rtf) = C:\Program Files\OpenOffice\OpenOffice.exe > Raw binary (*.bin) = C:\Program Files\pfe\pfe.exe 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". > For a step-by-step undo feature, we could build a stack for SQL > statementsthat mirror each change made (in step 3). Call it anti- > SQL :) Triggering > an undo would execute the top item on the stack. I like the idea of a step by step undo but I'd suggest this as part of "phase 2". The standard commit/rollback could be used initially and the step by step undo added later. -- Colin Bell http://squirrel-sql.sf.net |
|
From: Thackston R. <r.t...@le...> - 2002-10-09 12:51:32
|
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... > 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. 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... Russell |
|
From: gwg <gw...@co...> - 2002-10-09 17:53:16
|
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 |
|
From: Colin B. <col...@te...> - 2002-10-10 01:05:08
|
> 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). AFAIK they couldn't. Another reason to have a primary key defined for every table. -- Colin Bell http://squirrel-sql.sf.net |
|
From: Thackston R. <r.t...@le...> - 2002-10-10 13:06:36
|
Glenn wrote, > 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? They wouldn't be able to. A key of *some* type is required, whether it's a rowID, OID, or a primary/alternate key. Russell |
|
From: gwg <gw...@co...> - 2002-10-09 02:22:32
|
Thackston Russell wrote:
> Some comments...
>
> Glenn said,
>
>>Simply put, I won't need to locate the Primary Keys because I will
>>always use every column of the row in the WHERE clause, so any
columns
>>that have been designated as Primary Keys will be included
>>automatically. <snip>
>>
>
> Updating of rows should not be done this way (in my opinion). We
should
> check for primary keys and, if supported, allow the user to
update the
> record, matching on the primary key. If no primary keys are
defined,
then
> the table is not editable. This would encourage good database
design and
> ensure reliable results. This seems to be the approach taken by
most
> commercial tools I have seen.
There are several reasons why I think we should let the user update
tables without this limitation:
- there may be tables that do not require primary keys
- as Colin has said elsewhere, many people do not identify primary keys
in their DBs
- the people using the tool may not be DBAs, and may not have the
authority/ability to add that info to the DB
I don't know about you, but tools that force me to follow a particular
picky policy drive me nuts. If we were to force this policy on our
users, I, for one, would not use Squirrel. I think we want to entice
users with the freedom to use our tool the way they want. Let it help
them, not get in their way.
Please let us know if there are circumstances in which the proposed
sequence of operations (including the checks for identical rows) will do
something unexpected to the database.
As for encouraging good DB design, you could always create a "Good
Database Design Hints" plugin, or even one that checks the DB against
some criteria and generates lists of suggested fixes.
>
>
> Glenn said,
>
>><snip> My suggestion is that when the user clicks
>>on a binary field that we pop up a small window containing the
value in
>>a editable text field and a set of radio buttons that lets the user
>>view/edit the data as:
>> - bits ("10110011" - I would put spaces between each byte)
>> - hex ("B3")
>> - octal ("1 7 3" - I'm not sure of the best byte alignment here)
>> - ascii (i.e. as the ascii chars for those nybbles)
>>
>
> I suggest when a user tries to edit a binary field, we pop up a
window that
> prompts the user for the type of binary... bitmap (*.bmp), Word
document
> (*.doc), Rich text file (*.rtf), etc. Think plug-ins, user-defined
list,
> etc. The user selects the data type; Squirrel then writes the file
to a
> temp file and opens the file using a "default editor."
>
> For example...
> Bitmap (*.bmp) = C:\Program Files\Accessories\Paint.exe
> Word document (*.doc) = C:\Program Files\Office\Word.exe
> Rich Text file (*.rtf) = C:\Program
Files\OpenOffice\OpenOffice.exe
> Raw binary (*.bin) = C:\Program Files\pfe\pfe.exe
> ...
I believe that simple things should be easy to do. This means that if I
have a bit field and I want to flip one bit, I should be able to do that
directly in the Squirrel window, just as I do for strings and numbers,
without using an outside editor. Likewise, if I have a binary file that
contains some text and I want to change one of the characters from lower
to upper case, it would be nice to do that without resorting to an
external editor. External editors present the following issues:
- Their look and feel will be different from Squirrel, making it harder
for the user to switch back and forth between them.
- There is no such thing as a "default" editor for any given type. None
of the examples you gave would work on my machine (Linux). Moreover,
different people use different editors for the same types, such as
Picture It vs. Photoshop for .gif/.jpeg/.bmp.
There are an awful lot of data types out there. Creating an maintaining
the list of data types, their association with the editors, and managing
the different ways those editors are called in the different
environments seems like a lot of work, and that effort does not seem to
be focused on the main task of the Squirrel tool. If the user community
thinks this is a useful feature, it could be
added later.
However, your comments do make me think that we should be more flexible
than I had previously suggested. My thought now is that binary fields
(including BLOBs) should all be edited using the same window, and that
this window would have text fields and buttons providing for "Export to
file" and "Import from file" as well as the simple data view/edit text
window and controls.
>
>
> Glenn said,
>
>>I'd like to toss out another open issue here: Should we consider
adding
>>an "Undo"/"Redo" mechanism to Squirrel? If so, then editing of the
data
>>would be one thing that should be tied into that mechanism.
>>
>
> Let the user start and end sessions, maybe? They could, at any
time,
"undo"
> their changes by rolling back an open session.
>
> In fact, if we combine editing and sessions, it could work something
like
> this...
> 1. Open a table.
> 2. Start a session, which makes the table editable (checking for
primary
> keys).
> 3. Move around the table making changes, which are written to the
DB, but
> not committed.
> 4. Commit or rollback the session (table is no longer editable).
>
> For a step-by-step undo feature, we could build a stack for SQL
statements
> that mirror each change made (in step 3). Call it anti-SQL :)
Triggering
> an undo would execute the top item on the stack.
Yes, I thought of that too. Unfortunately, there are people like me
that prefer to have their changes made to the DB immediately, and there
are others that want to be able to use the transaction scope in their
own way, which this might interfere with.
The "anti-SQL" suggestion sounds like a standard "undo" feature. I
think that is how we would need to implement such a thing.
Glenn
|
