Re: [Squirrel-sql-develop] Suggestions for improving Squirrel

[Colin B. <col...@bi...>]

Hi Glenn,

Now I know whats happening when you go quiet - you're thinking :-)

> - Making Connecting to DB easier:
> This is a big topic, and my proposals for this would involve major=20
> changes to Squirrel.  The explanation of what I would like to do=20
> for=20
> this is likely to be lengthy, and possibly controversial, so I'll=20
> send a=20
> separate mail devoted to this topic.

Waiting for the email. We haven't had any controvery in Squirrel yet,
its been boring.

> - Print the contents of a table:
> In my previous tool I had code that would let you print the entire=20
> tablecontents.  It would split large tables appropriately and=20
> label the
> output pages so you could reassemble them to see the table data on=20
> paper.  My guess is that this would work well as a plug-in.

Printing support is definitely needed.

> - Edit the data in the table:
> The archives mentioned that there were problems with doing this. =20
> I saw
> only two problems mentioned, and I have proposed solutions to both...
> =09- How to activate/de-activate the feature?  My suggestion would=20
> be to use
> a checkbox in the Session's SQL Preferences.  Alternatively, we could
> use two radio buttons labeled "Edit in table" and "Read-only".
> =09- Some DBs do not support updateable ResultSets.  My solution is=20
> to not
> use updateable ResultSets at all!  In my previous tool, my method of
> updating the DB based on changes to the table was to generate an SQL
> statement of the form:
> =09UPDATE <table> set <updatedColumn> =3D <newValue> WHERE
> =09<column1>=3D<column1Value> AND <column2>=3D<column2Value> AND ...
> This should work correctly except in the case where the DB=20
> contains 2
> identical rows.  To minimize that problem, we may be able to=20
> include the
> rowID for DBs that support that feature (I vaguely remember that some
> DBs do not have that capability).  There may also be a problem=20
> with=20
> editing BLOBs, CLOBs, and the other binary data types.
> Are these solutions acceptable?  Are there other problems that I have
> not recognized?

I like the idea of generating the update statement rather than using
updateable result sets. For the duplicate row issue it might be worth
issuing a select count(*) statement prior to the update (with the same
where clause including rowids if the db supports them) and if the returne=
d
count !=3D 1 then give an error msg.

Editing data in tables is probably the number 1 requested feature for
Squirrel and I'd definitely like to see it included in the next version (=
1.2)
if possible. I know that a couple of other people have expressed interest
in doing this but I don't think they've had the time to work on it. If an=
ybody
is looking at this at the moment now is the time to speak up.

> - Let Help frame (and others) move external to main frame:
> From the way the code is written, I infer that there is some=20
> reason for
> restricting all of the child windows to fit inside the space of=20
> the main
> window.  However, I find this annoying in a number of cases.  For
> example, when I get the Help frame, I would like to be able to expand
> that and move it in front of or behind the squirrel main frame.  Is
> there a "human factors" reason for forcing all child windows to be
> within the main frame, or can we change this?

I think this is one of those religious issues. I happen to like the MDI
style approach to windows handling, I hate the SDI approach. Other people=
 are
the opposite. The old version of Netbeans used to drive me crazy with its
multiple toplevel windows.

For the Help window its probably a good idea that it is external to the
main window, that way a quick alt/tab (or the equiv for your OS) will fli=
ck
you back and forwards betwen the help and the window you are working
on. This should be a quick and easy mod and I'll put it in the next
release.=20

As an FYI I'm intending to put out the first release candidate for 1.1 th=
is
weekend. Depending on how many issues are found in it this will either be
followed by another release candidate or the stable version of 1.1 and th=
en
we're back into development mode, so this email of yours is timed
perfectly.

> - Add a new row to table:
> This could be implemented in a couple of ways.  We could always=20
> includean empty row at the bottom of the table (as is done in the=20
> Postgresstool named pgaccess).  We could have a button that, when=20
> the user clicks
> on it, creates an empty row in the table, or brings up a separate=20
> window=20
> with a blank row in it.  There may also be other ways.
>       The simplest approach from the user's point of view is to=20
> always=20
> include an empty row, but that might cause problems in other=20
> places in=20
> the code, so we'll need to discuss it.

I don't know that always including an empty row would cause any problems
in the current code. Of course the only way to know for sure is to try
it. This logically belongs with your previous point of editing data in a
table.

>=20
> - Delete a row from the table:
> The user should be able to select a row, then click (somewhere) on=20
> "delete" and have that row be deleted from the DB (and the table).
> Where should the "delete" button be?  One place would be in the menu
> that pops up on a right-mouse-button click.  I suspect that it should
> also be on some more permanent menu, or a tab, or something.

I'll add a bit more to this from an existing feature request 577391:

<snip>
 A very nice feature which I picked up from DbVisualizer
is the concepts of shortcuts. (I am not sure that
"shortcuts" is what they call it.)

What basically happens is that when one clicks on the
content tab of a table or view (and see the data in the
table) one can right click and get options like Select
all, Select count, Select, Delete, Insert, Update,

Say I chose the first three columns of a row in a table
and select Delete. It will then take me to the SQL
section, where a delete from table_name, where ...
clause will have been pasted with the thee columns
names and pre-populated values.=20
</snip>

> - Page forward/backward through large data sets
> This was mentioned in the archives, so I'm just agreeing that I=20
> think it
> is necessary.

I remember having some problems with how to implement this but
I don't remember what they were. I think its a good idea.

> - Help for plugins:
> The user should be able to get help on every plugin that is installed.
> This could be done in three different ways.  One is to include the=20
> helpinfo for all possible plugins in the help file=20
> (quickstart.html), but
> that would be awkward and not easy to maintain.  A second approach=20
> wouldbe to have the main help file somehow automatically reference=20
> help pages=20
> in each plugin.  The third approach would be for each plugin to=20
> add a
> button to the Help menu that links to their own help page.

This is already implemented. A plugin can specify a text or HTML file
that is its Help file and this wll be placed in the Help menu.

However the existing Help system is just a quick hack. Theres no search
facility, theres no integration with the UI (E.G. pressing help in the
alias definition window should bring up help specific to that window) etc=
=2E

Squirrel needs a good Help system. It also needs somebody who knows how t=
o
write good help files, I certainly can't. I don't want to use JavaHelp th=
ere
have been too many reports of instability, bugs etc. I'm not sure what to
do abut the Help system.

> - Plugin version numbers should be visible in the Help menu:
> This might be part of the previous feature, or it could be separate.

Its easy to do but why? You can see the version numbers for the plugins i=
n
the Plugins/Summary dialog.

> - Maximum space for table contents:
> For some (usually large) tables, I would like to be able to view=20
> as much
> of the content as possible at one time.  This means:
> =09- removing the horizontal space between the main menu ("File",=20
> etc.) and
> the session window (Note: even if I de-select all toolbars and those
> tool bars are removed, the space for them is left in place.)

I'm not sure what you mean by "even if I de-select all toolbars and those
tool bars are removed, the space for them is left in place". If you turn
off the toolbar in the session settings then the space the toolbar took u=
p
is taken up by the tabs. Which version of the JDK are you using?

> =09- possibly removing the title bar on the DB session window,=20
> though I'm
> not sure about this.  For now I think I would keep that one.
> =09- removing the horizontal bar holding the session commands ("Display
> Session Properties", etc.)
> =09- removing the tabs for "Objects" and "SQL")
> =09- removing all of the tabs in the data display frame("Info",=20
> "Contents","Imported Keys", etc.)
> =09- removing the frame containing the tree of tables to view in the DB=
=2E
> The user should be able to switch into this mode of operation and back
> out of it.
> To provide access to the various controls ("Object"/"SQL",
> "Info"/"Imported Keys"/etc., and the list of tables in the DB) we=20
> wouldneed to provide access to these controls in another place.  I=20
> wouldsuggest adding menu items to the main window for "Edit" and=20
> "View", and
> adding the various controls under those menus.

This is a good idea, one I hadn't thought of. However it would require
some big changes to the existing code. I'd like to cleanup the code befor=
e
you attempt this change.

>=20
> - Change SQL error message background color:
> Yes, the red background makes it clear that this is an error message.
> Unfortunately, the bright red makes it hard to read the text of the
> error.  I don't have an immediate suggestion on how to make this=20
> better.     Maybe leave the background white and change the text to:
> =09<red>ERROR:</red> the error message

Victor Ott (who originally made this change) is actually going to make
a similar change. The idea is that only the text of the error msg will be
red.=20

> - Improve performance?:
> I don't have specific data to support this, but squirrel seems=20
> slow to
> me as compared to my old tool (which also used Swing).  Since I=20
> haven'tseen any comments in the user or developer archives on=20
> Squirrel'sperformance, it may just be my imagination.  If I get=20
> some time, I may
> do a performance comparison between the two to see if there is some
> improvement we can make to Squirrel.

I find the peformance of Squirrel "adequate", but not good. It needs to h=
ave
a profiler run over it.

> - Include the rowID in the tables for DBs that have it:
> I have a hazy recollection that some DBs do not provide the=20
> concept of a
> rowID.  However, for those DBs that do have rowIDs, we should=20
> allow the
> user to choose to view those rowIDs in the table data.  If=20
> selected, the
> rowID could be displayed in a special column in the table data.

Not sure how to do this. For instance Oracle has the ROWID column, e.g.

select rowid from table

but I don't know how other DBMSs do it and more importantly I don't
know how to find out through JDBC.

As a side note I've just discovered that Squirrel doesn't handle the
returned data type for ROWID in Oracle. Damn, another bug.

> - Use files for binary and large data types:
> For BLOBs, CLOBs, and the other binary data types, we could give the
> user the option of viewing and editing that data in a temporary=20
> file.=20
> This could be useful if the user wants to edit the data or replace=20
> it.=20
> For example, if a BLOB is being used to hold a jpeg, then the=20
> followingsequence could be used to "edit" that BLOB:
> =09- when the row in the table is read, the BLOB is output to a=20
> temporary file
> =09- the table then displays the name of that file
> =09- the user may edit that file, or replace the contents of the=20
> file with a
> new jpeg.
> =09- Alternatively, we could let the user replace the name of the=20
> temp file
> in the table with the name of the file containing the new jpeg.
> =09- updating the row in the DB (using the "edit data in table" feature=
)
> then updates the BLOB in the database.
> One problem with this scenario is that reading 500 rows from the table
> would cause 500 files to be created, which is both extremely slow and
> could use a large amount of disk space.  An alternative might be=20
> to keep
> the BLOB/CLOB/etc. in memory and just create the temp file when=20
> the user
> indicates an interest in editing the data by clicking on that=20
> entry in
> the table.
> This feature could be enabled/disabled in the "Session Properties"
> window.  We could change the section labeled "Show String Data.." to
> something like "Show Data of these types as:" and then for each of the
> data types display two radio buttons labeled "String" and "in File".

Very good idea. I don't know of any other ISQL tool that does this.

> - Catch un-caught exceptions in Main:
> In the (hopefully unusual) case where we have a bug that throws an
> exception somewhere deep in the code, it might be good to catch that
> exception and:
> =09- try to log it into the log file
> =09- print a message to the user saying there was in internal=20
> problem and
> they should email to the development group the log and and explanation
> of what they were doing when the exception occured.
> The advantages of catching all errors at the top level are that the
> program looks better to the user (because it doesn't just dump the
> exception stack) and it gives us a chance to capture the exception=20
> in a
> known place so we can work on it.

I assume that you are talking about startup issues here? As once the
main window is opened the exceptions don't get propogated back to main().
One problem that does cause stack traces in the command window is my not
handling errors in Action classes. I'm slowly fixing these. I might actua=
lly
try to hit them all this weekend prior to putting out the release.=20

>=20
> - Column width sizing:
> The user could set a preference that tables will be initially=20
> displayedwith the column widths set to one of the following:
> =09- maximum size of data in the column (this guarantees that all data
> (except BLOBs/CLOBs) will be visible in their columns, but with many
> tables this will push a lot of columns off-screen to the left, thus
> forcing the user to do a lot of sideways scrolling)
> =09- maximum size used (for columns that have a lot of room that is=20
> seldomused, this makes better use of the screen real estate)
> =09- a default size, e.g. 10 characters (this may allow more columns=20
> of the
> table to be seen

Thers nothing questionable about this. I'm getting really annoyed about
having to continually drag column headers so that I can see the data.

> - Let user add and delete columns:
> For a completely integrated user experience we should provide for=20
> simpleadding and removing of columns in the table view.  Adding=20
> and deleting
> columns would require "Add Column" and "Delete Column" controls
> somewhere, possibly under an "Edit" main menu item. =20
> Unfortunately, I
> believe that this operation may be specific to each DB.  I saw=20
> notes in
> one of DBs (sorry, forget which one) that said "We don't provide the
> ALTER TABLE operation, and here is a multi-step sequence of operations
> that gives you the same result".  We might be able to handle this with
> plug-ins.  We might also consider providing a default mechanism that
> does this in the usual way, but which can be overridden by a plug-
> in for
> a specific DB to do it in a different way.
>
> - Create tables:
> For creating tables, would it be useful to provide a wizard to=20
> help the
> user create the table?
> My guess is that this would be a low priority feature because=20
> creating a
> table requires a lot of details from the user that can be entered=20
> almostas easily in the SQL window, though something to help avoid=20
> the need to
> remember the exact command syntax might be good.

Personally I can never remember the correct syntax. I'd like to see
a table maintenance wizard plugin for Squirrel. Creating/modifying tables
etc. Big job, as you pointed out each DBMS has its own synatx for many
of these operations.

> - Get the list of all Schemas from the DB:
> In a former life I worked in an environment where we had multiple=20
> DBs on
> the same server that was running Oracle.  There was a way to log=20
> in to
> Oracle as a kind of super-user (e.g. as "system" or some such)=20
> that let=20
> you list all of the tables in all of the schemas being managed on=20
> that=20
> host.  Based on that, you could see what the names of the DBs
> (actually the DB Schemas) were.  That was very handy because we didn't
> always remember which schemas were on which machine and what their=20
> exactnames were.  It would be really nice to be able to point=20
> Squirrel at a
> machine and say "What Schemas are on that machine?"  (Note: this=20
> should=20
> not be a big security issue because you still need to know the=20
> user/password to get into and manipulate the DB.)  My guess is=20
> that we=20
> cannot do this as a general feature, though we might be able to do=20
> it=20
> for specific DB engines.

Do you mean schemas or SIDs? I.E the connect string for Oracle (thin
driver) is

jdbc:oracle:thin:@<server>:1521:SID

You log onto a particular SID and the object tree contains a list of the
schemas for that SID. You can have multiple SIDs on the server. Currently
in Squirrel you have to know the SID in order to connect to it. I don't
know of any way to retrieve the SIDs via JDBC (maybe the OCI driver? I
haven't tried that one).

Via JDBC you can do something like this for catalogs. I.E. for MySQL and
MS SQL Server there is a dropdown in the session toolbar that allows
you to select the current catalog.

> - Define a common interface for DB-specific operations:
> This idea is kind of "out there".  The thought is that there may be
> operations that you would like to do on all DBs, but which are
> accomplished differently for each one.  To support this, we could=20
> definean interface that the main code calls to execute that=20
> operation, and the
> DB-specific plugins would then do the real work.  I think that=20
> this is a
> little different than the current plugin interface, since that=20
> interfacelets you define new operations in Squirrel.  The=20
> interface that I am
> thinking of here would define given DB operations (e.g. "add a=20
> column to
> a table") that would then be supported or not supported by the
> DB-specific plugins.

I've been thinking about something similar to this. My idea was
to have XML files that specified how to do certain actions. E.G. to
retrieve the source for a stored procedure for Oracle might be defined
like this in an oracle.xml file:

<RETRIEVE_PROC_SOURCE>
  <STMT>
    SELECT    SRC_COLUMN FROM ALL_SOURCE
    WHERE     OBJECT_TYPE=3D'PROC'
=09AND       OBJ_NAME=3D%0
    ORDER BY  LINE_NUMBER
  </STMT>
</RETRIEVE_PROC_SOURCE>

Ignore the SQL statement, its wrong - I'm too lazy to look up the correct=
 one.

I haven't done any work on this but I think it could be quite useful.

> - Manage Users and Passwords:
> Should we provide a way to manage Users and Passwords through=20
> Squirrel?    My guess is that this is too DB specific, though we=20
> might provide a
> general interface that gets specialized by DB-specific plugins. =20
> I'm a
> bit doubtful that this would be worth the effort, but since it is
> probably not done often, it might be nice to include in Squirrel=20
> (thingsthat are seldom done are the hardest to remember, and often=20
> the most
> ugent when they ARE needed, so making those operations easier to=20
> do can
> be reeeeeealy appreciated.)

Do you mean a UI to create/change db users and passwords? If so it
could be done quite easily as a plugin.=20

> - Treat foreign keys as links:
> We could set up the table entries for foreign keys as links such that
> double-clicking on them would take the user to the table=20
> containing that
> key and displaying the row(s) refered to by that key.  We could=20
> also=20
> include a "back" feature that would return you to the previous=20
> table=20
> view.  I'm not sure how useful this would be.

Interesting idea. I think this could be quite useful. Quite often I'm
tracking links through tables. This would save me continually keying
SQL.

> - Graphical representation of tables:
> I think I remember a mail in the archives talking about creating a
> graphical representation of the tables and their relationships. =20
> Is that
> work still being persued?  It sounds like a really neat idea to me!

Greg Mackness is working on this at the moment and I believe he's
pretty close. I've just added some methods to the database metadata
class that he needed.

> - Help with bulk conversions:
> When loading a lot of data into a database from another database=20
> or from
> external files, it would be nice to have a wizard or a graphical=20
> tool of
> some kind that would help define the mapping of the data from the=20
> sourceto the DB tables and columns.  It would also be nice to have=20
> somethingto help with DB upgrades when moving from one version of=20
> a DB to
> another.  However, this whole topic is so messy that it may not be=20
> worththe effort to create the necessary tools.

Import/export is another area that Squirrel doesn't do. Johan
Compagners SQL Scripts plugin supplies some export functionality
by generating INSERT statements but more is needed.

Guido Cervone and Like Gao of GMU wrote a Data Import plugin that would
import comma separated files into tables. It has a GUI that maps the data
to columns. It needs some work (the old different DBMS's use different
syntax problem) and I've just been sitting on it for what must be 9 month=
s
now. If somebody would like to finish it off I'd be very grateful.

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

Some other things that would be useful.

- A shortcuts plugin. Saving favourite queries in a similar way to IE or
  Mozilla saving internet shortcuts. Partially taken care of by Johans
  SQL Scripts plugin but having them in a menu would be useful.

- "Code completion", ie having Squirrel popup column names, table names
  etc in the SQL window. Christian Sell,  Russell Thackston and=20
  Patrick Connors are currently working on this.

- Web start enabled

- An automated way of installing plugins. Rather than downloading
  and unzipping them I'd like to see something like the plugin manager
  in jEdit. You just take an Update plugins option and it comes back
  and tells you which of your plugins need to be updated and allows you
  to download and install them. Possibly using WebStart in some way?

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

There are also a couple of major changes to the sturcture of Squirrel
that I am intending to make for the next release. Firstly I want to
split out the Swing/AWT code from the rest of the application. Andrea
Mazzolini has a very nice ISQL tool JFaceDbc
(http://jfacedbc.sourceforge.net/ that uses the Squirrel SQL code
but with a JFace/SWT GUI. I'd lke Andrea to be able to use a lot more
of the Squirrel code than he can at the moment. As well separating out
the GUI code is going to make the code cleaner and easier to maintain.

I'm also going to be looking at replacing the current plugin
architecture with one based on OSCAR
(http://oscar-osgi.sourceforge.net/). This will give some advantages
such as dynamic class reloading which would be useful. I don't
envisage too many changes to the existing plugins other than they
would have to cleanup after themselves (I.E. remove menu items that
they have created etc.). The current APIs (such as IObjectTreeAPI)
would be unaffected.

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

What would I like to see done next for Squirrel? I'd really like to
see some form of data maintenace added. A plugin that allowed people
to change and insert data thru some form of GUI would be a big win.
Several people have expressed an interest so what I suggest is those who =
are
interested should get together and talk about how to do it.

--
Colin Bell
http://squirrel-sql.sf.net