Thread: RE: [Squirrel-sql-develop] New plugin planned

> -----Original Message-----
> From: Glenn [mailto:gw...@co...]
> Sent: Friday, 6 February 2004 10:02
> To: squ...@li...
> Subject: 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).
[...]


One possibility would be to only allow editing in the SQL tab if
there is only a single table in the SQL statement and the column list
includes all primary key columns for the table and the table has at least
one primary key column. The downside of this of course is that any
table you want want to do maintenance on _must_ have a primary key
defined for it.

When Glenn first started writing the editing mechanism for the contents
tab I pushed strongly to allow maintenance of tables that didn't have
a primary key defined because I've seen so many tables created
without primary keys. Yes its wrong but unfortunately developers/DBAs
do it and then people like me end up supporing these databases.

However in the SQL tab I don't see any way this would work without
the primary key being involved.

Updateable result sets aren't necessarily a way round this problem
either. For example MySQL doesn't have row identifiers so to create
an updateable result set over a MySQL SQL statement you must have
the complete primary key in the column list.

I went looking in the JDBC specification to see what it says
about updateable result sets:

"Due to differences in database implementations, the JDBC API does not
specify an ex-act
set of SQL queries which must yield an updatable result set for JDBC drivers
that
support updatability. Developers can, however, generally expect queries
which meet
the following criteria to produce an updatable result set:
1. The query references only a single table in the database.
2. The query does not contain any join operations.
3. The query selects the primary key of the table it references."


Col

> -----Original Message-----
> From: gwg [mailto:gw...@co...]
> Sent: Friday, 6 February 2004 15:55
> To: squ...@li...
> Subject: Re: [Squirrel-sql-develop] New plugin planned
> 

> 
> 
> Well, we could be a bit less restrictive by saying:
> - any table with a primary key OR
> - any query of the form "SELECT * FROM <table> [WHERE...]"
> That would give us enough to do the same update we do now in 
> ContentsTab.  Of course, that eliminates the really useful 
> part of the 
> SQL tab - the ability to do joins.  It could also be rather 
> annoying to 
> have the results be sometimes editable and sometimes not.

Of course if I'd thought about it properly I would have realized this. If we
use the same restriction (I.E. if the update will only update 1 rows then
its _safe_) then we can allow updates on a greater number of queries. As
well as only one table in the column list there should be no join clause.

It doesn't elimate joins in the SQL tab, it just means that if you use joins
(either in the FROM/WHERE clauses or in the JOIN clause) then you can't
update the data. I don't see a problem with this.

Col

> -----Original Message-----
> From: gwg [mailto:gw...@co...]
> Sent: Friday, 6 February 2004 15:55
> To: squ...@li...
> Subject: Re: [Squirrel-sql-develop] New plugin planned
> 
> 
> Help me out here.  If someone is looking at just one table, 
> why are they 
> not using the ContentsTab?  Since one of our fellow contributors 
> recently added the ability to do a WHERE clause filter on the 
> ContentsTab, it seems to me the user gets the same capability 
> with less 
> typing than in the SQL tab.  Given the severe limitations on 
> what could 
> be updateable in the SQL tab and the inconsistancy of 
> sometimes-editable 
> sometimes-not, what would be the benefit of adding editing 
> there?  Maybe 
> instead of adding that feature, we just point people to the 
> ContentsTab?
> 
> Of course, if you could edit/update the results of a join, 
> that would be 
> a different story, but I haven't heard any suggestions on how 
> to do that.
> 

Sorry, missed this paragraph when I first read the email, I definitel;y need
some sleep. Good question. I tend to use the filter a lot, drilling down to
the data I actually want to view. Most of the 1.2 enhancements aren't yet in
the help text (the only stuff thats there really is what you have done, I
haven't got around to it yet). Once its properly documented I suspect that
most people will use it. If the SQL starts getting more complicated and they
move to the SQL tab the query probably won't be updateable anyway. But
remember that queries like

select * from tab1 where col1 in (select col1 friom tab2 where ...)

should be updateable but cannot easily be done (unless I've missed somethign
obvious) in the filter dialog.

Also there will always be people who prefer keying up the SQL rather than
using a point and click interface so I think we should allow maintenacne
from the SQL tab. But your point emphasises that allowing it from the SQL
tab is secondary, I think it should be be working fully in the Contents tab
before its applied to the SQL tab.

My 2 cents worth

Col

If we want to give the use the best chance of being able to edit a cell 
in the SQL tab, we could use the following algorithm.

STARTING CONDITIONS:
- user has edited a cell and just exited it
- we have the original SQL statement
- we have the result set

ALGORITHM:
- extract the FROM clause, starting from the end of 'FROM' and going to 
the end of the statement, a 'WHERE', or an 'ORDER BY'.  (Do we need to 
look for other things?)
- get the list of tablesAndAlias names (separate the FROM clause at the 
commas, if any)
- for each name, separate the table name from the alias, if any, and 
remember both for later use
- For each table, get all of that table's column info
- For each column in the result set, identify which tables it belongs 
to.  A column may belong to multiple tables.
- For each table that the edited element belongs to:
    - create a WHERE clause using all of the columns in the
       result set that belong to that table
    - check for # of rows matching existing data.
        Warn user about multiple rows being updated.
        If user decides not to proceed, abort the update.
    - check for # of rows matching data after the proposed update.
        Warn user about multiple rows matching after update.
        If use decides not to proceed, abort the update.
- Start a transaction
- For each table that the edited element belongs to:
    - update the table
- End transaction

<END ALGORITHM>

For queries involving a single table, this degenerates into something 
similar to the ContentsTab, though with the possibility that the user 
did not include all of the fields from the table.  This will certainly 
work as well as ContentsTab for "SELECT * FROM <table> [WHERE...]" 
queries, and will also allow for updates in some joins.

The question comes down to, how often will the query contain enough 
fields from the table(s) to uniquely identify a single row in the table 
being updated?

Glenn

Colin Bell wrote:

>>-----Original Message-----
>>From: gwg [mailto:gw...@co...]
>>Sent: Friday, 6 February 2004 15:55
>>To: squ...@li...
>>Subject: Re: [Squirrel-sql-develop] New plugin planned
>>
>>
>>Help me out here.  If someone is looking at just one table, 
>>why are they 
>>not using the ContentsTab?  Since one of our fellow contributors 
>>recently added the ability to do a WHERE clause filter on the 
>>ContentsTab, it seems to me the user gets the same capability 
>>with less 
>>typing than in the SQL tab.  Given the severe limitations on 
>>what could 
>>be updateable in the SQL tab and the inconsistancy of 
>>sometimes-editable 
>>sometimes-not, what would be the benefit of adding editing 
>>there?  Maybe 
>>instead of adding that feature, we just point people to the 
>>ContentsTab?
>>
>>Of course, if you could edit/update the results of a join, 
>>that would be 
>>a different story, but I haven't heard any suggestions on how 
>>to do that.
>>
>>
> 
> Sorry, missed this paragraph when I first read the email, I definitel;y need
> some sleep. Good question. I tend to use the filter a lot, drilling down to
> the data I actually want to view. Most of the 1.2 enhancements aren't yet in
> the help text (the only stuff thats there really is what you have done, I
> haven't got around to it yet). Once its properly documented I suspect that
> most people will use it. If the SQL starts getting more complicated and they
> move to the SQL tab the query probably won't be updateable anyway. But
> remember that queries like
> 
> select * from tab1 where col1 in (select col1 friom tab2 where ...)
> 
> should be updateable but cannot easily be done (unless I've missed somethign
> obvious) in the filter dialog.
> 
> Also there will always be people who prefer keying up the SQL rather than
> using a point and click interface so I think we should allow maintenacne
> from the SQL tab. But your point emphasises that allowing it from the SQL
> tab is secondary, I think it should be be working fully in the Contents tab
> before its applied to the SQL tab.
> 
> My 2 cents worth
> 
> Col
> 
> 
> 
> -------------------------------------------------------
> The SF.Net email is sponsored by EclipseCon 2004
> Premiere Conference on Open Tools Development and Integration
> See the breadth of Eclipse activity. February 3-5 in Anaheim, CA.
> http://www.eclipsecon.org/osdn
> _______________________________________________
> Squirrel-sql-develop mailing list
> Squ...@li...
> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop
> 
> 

Colin Bell wrote:

>>-----Original Message-----
>>From: Glenn [mailto:gw...@co...]
>>Sent: Friday, 6 February 2004 10:02
>>To: squ...@li...
>>Subject: 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).
>>
> [...]
> 
> 
> One possibility would be to only allow editing in the SQL tab if
> there is only a single table in the SQL statement and the column list
> includes all primary key columns for the table and the table has at least
> one primary key column. The downside of this of course is that any
> table you want want to do maintenance on _must_ have a primary key
> defined for it.


Well, we could be a bit less restrictive by saying:
- any table with a primary key OR
- any query of the form "SELECT * FROM <table> [WHERE...]"
That would give us enough to do the same update we do now in 
ContentsTab.  Of course, that eliminates the really useful part of the 
SQL tab - the ability to do joins.  It could also be rather annoying to 
have the results be sometimes editable and sometimes not.


[snip]

> 
> "Due to differences in database implementations, the JDBC API does not
> specify an ex-act
> set of SQL queries which must yield an updatable result set for JDBC drivers
> that
> support updatability. Developers can, however, generally expect queries
> which meet
> the following criteria to produce an updatable result set:
> 1. The query references only a single table in the database.
> 2. The query does not contain any join operations.
> 3. The query selects the primary key of the table it references."


Help me out here.  If someone is looking at just one table, why are they 
not using the ContentsTab?  Since one of our fellow contributors 
recently added the ability to do a WHERE clause filter on the 
ContentsTab, it seems to me the user gets the same capability with less 
typing than in the SQL tab.  Given the severe limitations on what could 
be updateable in the SQL tab and the inconsistancy of sometimes-editable 
sometimes-not, what would be the benefit of adding editing there?  Maybe 
instead of adding that feature, we just point people to the ContentsTab?

Of course, if you could edit/update the results of a join, that would be 
a different story, but I haven't heard any suggestions on how to do that.

Glenn


> 
> 
> Col
> 
> 
> -------------------------------------------------------
> The SF.Net email is sponsored by EclipseCon 2004
> Premiere Conference on Open Tools Development and Integration
> See the breadth of Eclipse activity. February 3-5 in Anaheim, CA.
> http://www.eclipsecon.org/osdn
> _______________________________________________
> Squirrel-sql-develop mailing list
> Squ...@li...
> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop
> 
> 

I'm convinced.  The edit feature from ContentsTab will do exactly what I 
was looking at.  So, I guess "the new plugin is un-planned".  :-)  Our 
library doesn't offer more than you seem to have worked out already.  At 
least, that is what I see now.  I'll see more when I look at the edit 
feature.

When is the feature planned for release?

By the way, do you know if there is a way to do cvs checkout from behind 
the firewall without having to open the port on the firewall?  Our admin 
is rather particular about it.  Like over http?  Sorry, if it's a stupid 
question.  We've been using cvs only internallly, so far.

gwg wrote:

>
>
> Colin Bell wrote:
>
>>> -----Original Message-----
>>> From: Glenn [mailto:gw...@co...]
>>> Sent: Friday, 6 February 2004 10:02
>>> To: squ...@li...
>>> Subject: 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).
>>>
>> [...]
>>
>>
>> One possibility would be to only allow editing in the SQL tab if
>> there is only a single table in the SQL statement and the column list
>> includes all primary key columns for the table and the table has at 
>> least
>> one primary key column. The downside of this of course is that any
>> table you want want to do maintenance on _must_ have a primary key
>> defined for it.
>
>
>
> Well, we could be a bit less restrictive by saying:
> - any table with a primary key OR
> - any query of the form "SELECT * FROM <table> [WHERE...]"
> That would give us enough to do the same update we do now in 
> ContentsTab.  Of course, that eliminates the really useful part of the 
> SQL tab - the ability to do joins.  It could also be rather annoying 
> to have the results be sometimes editable and sometimes not.
>
>
> [snip]
>
>>
>> "Due to differences in database implementations, the JDBC API does not
>> specify an ex-act
>> set of SQL queries which must yield an updatable result set for JDBC 
>> drivers
>> that
>> support updatability. Developers can, however, generally expect queries
>> which meet
>> the following criteria to produce an updatable result set:
>> 1. The query references only a single table in the database.
>> 2. The query does not contain any join operations.
>> 3. The query selects the primary key of the table it references."
>
>
>
> Help me out here.  If someone is looking at just one table, why are 
> they not using the ContentsTab?  Since one of our fellow contributors 
> recently added the ability to do a WHERE clause filter on the 
> ContentsTab, it seems to me the user gets the same capability with 
> less typing than in the SQL tab.  Given the severe limitations on what 
> could be updateable in the SQL tab and the inconsistancy of 
> sometimes-editable sometimes-not, what would be the benefit of adding 
> editing there?  Maybe instead of adding that feature, we just point 
> people to the ContentsTab?
>
> Of course, if you could edit/update the results of a join, that would 
> be a different story, but I haven't heard any suggestions on how to do 
> that.
>
> Glenn
>
>
>>
>>
>> Col
>>
>>
>> -------------------------------------------------------
>> The SF.Net email is sponsored by EclipseCon 2004
>> Premiere Conference on Open Tools Development and Integration
>> See the breadth of Eclipse activity. February 3-5 in Anaheim, CA.
>> http://www.eclipsecon.org/osdn
>> _______________________________________________
>> Squirrel-sql-develop mailing list
>> Squ...@li...
>> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop
>>
>>
>
>
>
>
> -------------------------------------------------------
> The SF.Net email is sponsored by EclipseCon 2004
> Premiere Conference on Open Tools Development and Integration
> See the breadth of Eclipse activity. February 3-5 in Anaheim, CA.
> http://www.eclipsecon.org/osdn
> _______________________________________________
> Squirrel-sql-develop mailing list
> Squ...@li...
> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop
>