The answer to your questions is: Sure! Squirrel could do that! Sounds
neat. So when are you going to build it? :-)
What you are talking about is the representation and editing of data
within a cell. All of that functionality is encapsulated in DataTypeXXX
classes, where XXX represents a Java data type that can be gotten from
SQL, e.g. String, Integer, Date, etc. One way to handle your requests
would be to have the all of the DataTypes (where it makes sense) check
whether the field is a foreign key, then do an extra lookup to get the
appropriate row and column from that table. The column name to display
could be entered by the user in the "Data Types" section of the General
This could be a significant performance hit during the inital display of
the table, since each row could have several foreign keys and each
foreign key would require another JDBC access, but there are probably
ways around that.
Your feature # 2 would use a similar approach, but in the cell editor
section of the DataType objects. It would also need to handle the
reverse lookup (e.g. string-to-code) when the editing is completed. The
current code all assumes that the user is typeing text into a JTextField
object, so I don't know off hand what other issues might arrise from
using some other Swing component.
I don't see any problem with your feature # 3 except that I don't know
what happens if we try to display two different tables in the same
session. Probably could be done.
Dick Repasky wrote:
> I've been looking at database browsers/editors, and I know what I want. I
> haven't found it yet, and my question is whether the following is
> reasonable to think about doing with SquirrelSQL.
> What I'm looking for when browsing/editing tables is
> transparent translation of 1-to-many relations. There are three features
> that I have in mind.
> 1) When browsing a table, I would like to have values in columns that
> are foreign keys be replaced with labels taken from the tables that
> are referenced by the foreign key columns. For example, if a table has
> a column named system_id that is a foreign key to a table that contains the
> names of systems, I'd like to have the system name displayed instead
> of the value of the foreign key. Instead of a number like 32, I want
> to see "Scramble Linux Cluster".
> This feature appears to need only one piece of information that
> isn't in the usual schema data - for each table that has a primary key
> that is referenced by foreign keys, a column that is the name to be
> used to represent them in tables that have foreign keys.
> If the the labels and schema are known, the SQL to do this isn't
> SELECT ... system_id ... FROM accounts
> SELECT ... refa.system_name ... FROM accounts, systems AS refa WHERE ...
> The AS allows for multiple foreign keys pointing to the same table and
> self-referential foreign keys.
> 2) When editing a table. If I choose to edit a cell that is a foreign key
> (and represented as described above), I want to choose a new value
> from a list of labels taken from the label column of the table that is
> referenced by the foreign key.
> I have thought less about how to implement this one, but I assume that
> it can be done with a shell game of data classes and a
> ForeignKeyCellEditor class.
> 3) When browsing a table that has a primary key that is referenced by
> foreign keys in other tables, I'd like to be able to select a row
> and popup a menu that allows me to select one or more of those
> other tables and have them queried for all rows that have foreign key
> values that match the row that I have selected. (In relatively
> unconnected schemas, it might be nice to have an "all tables" menu item
> that queries all tables that reference the table for the value of
> interest and pops up a table window for each.
> These features strike me as very useful and general. The question is:
> are they practical with SquirrelSQL? And, I suppose, does anyone know
> of a browser/editor that does these things.
> Dick Repasky
> SF.Net email is sponsored by Shop4tech.com-Lowest price on Blank Media
> 100pk Sonic DVD-R 4x for only $29 -100pk Sonic DVD+R for only $33
> Save 50% off Retail on Ink & Toner - Free Shipping and Free Gift.
> Squirrel-sql-develop mailing list