An updatable grid

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

An updatable grid

Milan Babuskov-2


-------- Original Message --------
Subject: Re: [Fbmanager-devel] An updatable grid
Date: Sat, 19 Nov 2005 12:21:56 +0100
From: Olivier Mascia <[hidden email]>
To: Milan Babuskov <[hidden email]>
CC: [hidden email]
References: <[hidden email]>
<[hidden email]> <[hidden email]>
<[hidden email]>

Hi Milan,

Le 19 nov. 05 à 09:06, Milan Babuskov a écrit :

> Olivier Mascia wrote:
>> You might SELECT RDB$DB_KEY along with your data fields when you  
>> select. Then you can UPDATE ... WHERE RDB$DB_KEY = ....
>> No matter your table has or doesn't have a PK. Each row has a  
>> unique DB_KEY for the duration of the transaction. And there is no  
>> faster single direct row access in Interbase/Firebird than through  
>> the DB_KEY. Going through the PK index is not even as straight as  
>> going through the DB_KEY for positionned updates.
>> Hint: if you ever wondered what the IBPP::DBKey can be used  
>> for... ;-)
>
> Is there any chance we could use DBKey without having it in SELECT  
> statement (I guess your Row class does it)?

I know of no way to get the RDB$DB_KEY without asking for it.

> Basically, we would like to allow user to do his select the way he  
> wants, and allow change of values afterwards.

I see only two ways of doing it :

a) changing the user select 'begind the scene' to ask for RDB$_KEY
(and maybe masking it out from the result set)

b) using the cursor API (which does the pretty much the same thing,
in such a hidden way), unfortunately that is not so flexible because
you can only UPDATE ... WHERE CURRENT OF <cursor> for the 'current'
row, the last one which was fetched (since bidrectional cursors are
not available in FB).

The (a) path seems the one viable. Alter behind the scene the exact
query to get the RDB$DB_KEY (if it wasn't asked by the user), get the
data, hiding the column which the user didn't asked for, and use it
for the UPDATES.

One of the Row goals was that : storing the columns of a row, and
being able to detect what columns values where changed by the user
after select, so that adequate sets of UPDATE statements, updating
only what is necessaryn could be issued.

We have some experimental development using the technique, but the
Row in IBPP 2.4.5 does _not_ implement that and is really something
which you must forget about. I'll drop it very soon. As many people
(including me) quickly recognized, the way the Row is built today
around the XSQLDA is dumb if it has to be used as a subcontainer in
some larger one, name a 'dataset'.

In any way, if you develop some grid editing in FR using a (possibly
hidden) RDB$DB_KEY for later UPDATEs, then you'll be just in line
with what IBPP would offer at some point in time. The concept and
programming is very simple. I see no reason not to do it in FR, even
though in a later stage, FR might choose to rehost that feature on
top of the equivalent code IBPP would introduce.

--
Olivier





--
Milan Babuskov
http://fbexport.sourceforge.net
http://www.flamerobin.org



-------------------------------------------------------
This SF.Net email is sponsored by the JBoss Inc.  Get Certified Today
Register for a JBoss Training Course.  Free Certification Exam
for All Training Attendees Through End of 2005. For more info visit:
<a href="http://ads.osdn.com/?ad_idv28&alloc_id845&op=click">http://ads.osdn.com/?ad_idv28&alloc_id845&op=click
_______________________________________________
Flamerobin-devel mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/flamerobin-devel
Reply | Threaded
Open this post in threaded view
|

Re: An updatable grid

Milan Babuskov-2
Olivier wrote:

> I know of no way to get the RDB$DB_KEY without asking for it.
>
> The (a) path seems the one viable. Alter behind the scene the exact
> query to get the RDB$DB_KEY (if it wasn't asked by the user), get the
> data, hiding the column which the user didn't asked for, and use it
> for the UPDATES.
>
> In any way, if you develop some grid editing in FR using a (possibly
> hidden) RDB$DB_KEY for later UPDATEs, then you'll be just in line
> with what IBPP would offer at some point in time. The concept and
> programming is very simple. I see no reason not to do it in FR, even
> though in a later stage, FR might choose to rehost that feature on
> top of the equivalent code IBPP would introduce.

I see few paths ahead:

1. mess with user's query visibly by adding DBKey as the first column
for each query

2. mess with user's query invisibly

3. have a separate "edit table" option that selects *, dbkey from table

4. forget the dbkey idea, and require user to have primary keys in
select statements


In case of 1 and 2 we also need to take care of grouping in queries with
GROUP BY clause, and if they use ordinal numbers... perhaps dbkey should
be a last column in the query after all.

Using 3 gives us less flexibility as only a single table can be edited
at a time.

I really can't decide...

--
Milan Babuskov
http://fbexport.sourceforge.net
http://www.flamerobin.org



-------------------------------------------------------
This SF.Net email is sponsored by the JBoss Inc.  Get Certified Today
Register for a JBoss Training Course.  Free Certification Exam
for All Training Attendees Through End of 2005. For more info visit:
http://ads.osdn.com/?ad_id=7628&alloc_id=16845&op=click
_______________________________________________
Flamerobin-devel mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/flamerobin-devel
Reply | Threaded
Open this post in threaded view
|

Re: An updatable grid

Michael Hieke
Milan Babuskov wrote:

> I see few paths ahead:
>
> 1. mess with user's query visibly by adding DBKey as the first column
> for each query
>
> 2. mess with user's query invisibly
>
> 3. have a separate "edit table" option that selects *, dbkey from table
>
> 4. forget the dbkey idea, and require user to have primary keys in
> select statements
>
>
> In case of 1 and 2 we also need to take care of grouping in queries
> with GROUP BY clause, and if they use ordinal numbers... perhaps
> dbkey should be a last column in the query after all.
>
> Using 3 gives us less flexibility as only a single table can be
> edited at a time.

That seems to me a severe limitation, and OTOH doesn't really make the
task easier.
A lot of things can be used in a query which make result sets hard to
edit, like aliases, joins and subselects.  Computed columns may not be
edited at all.  So in any case we have to decide for each column in the
result set whether it can be edited, to which column in which table/view
an update has to go, and whether we have the primary key (or DBKey, but
does it help us for joins?) value for this relation.
So it's maybe easiest to not require the DBKey, but to prepare the
SELECT statement, to determine the information outlined above, and then
to create the necessary grid cell editor instances.  Or create them
on-demand.  We will have to override wxGrid::GetCellEditor() anyway.

Thanks

--
Michael Hieke



-------------------------------------------------------
This SF.Net email is sponsored by the JBoss Inc.  Get Certified Today
Register for a JBoss Training Course.  Free Certification Exam
for All Training Attendees Through End of 2005. For more info visit:
http://ads.osdn.com/?ad_id=7628&alloc_id=16845&op=click
_______________________________________________
Flamerobin-devel mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/flamerobin-devel
Reply | Threaded
Open this post in threaded view
|

Re: An updatable grid

Milan Babuskov-5
Michael Hieke wrote:
> A lot of things can be used in a query which make result sets hard to
> edit, like aliases, joins and subselects

None of these is the issue. We have IBPP::Statement::ColumnTable which
should give up the name of table that is the column source. I believe it
returns the empty string for complex things like subselects, UDFs,
cases, etc. so we can make such columns read-only.

> So in any case we have to decide for each column in the
> result set whether it can be edited, to which column in which table/view
> an update has to go

IBPP::Statement::ColumnTable gives us all that info. If it does not, we
can ask Olivier to make it so, or even change IBPP ourselves.

>, and whether we have the primary key (or DBKey, but
> does it help us for joins?) value for this relation.

Not a problem. We just check affected tables - load their PKs, and see
if we have those PK columns in resultset.

As for DBKey, I'm not sure. I think we can update multiple tables using
the same DBKey value. This needs testing though... I'll test and report
here.

> So it's maybe easiest to not require the DBKey, but to prepare the
> SELECT statement, to determine the information outlined above, and then
> to create the necessary grid cell editor instances.  Or create them
> on-demand.  We will have to override wxGrid::GetCellEditor() anyway.

Think that on-demand is a good idea. Or even the idea you proposed at
the FBCon. We can keep the grid read-only, and provide the Edit option
which would open a new dialog with all the selected fields. That dialog
can even have few checkboxes like: "Display executed statements" which
would display the UPDATE,INSERT and DELETE statements that are going to
be run on database.

--
Milan Babuskov
http://www.flamerobin.org



-------------------------------------------------------
This SF.Net email is sponsored by the JBoss Inc.  Get Certified Today
Register for a JBoss Training Course.  Free Certification Exam
for All Training Attendees Through End of 2005. For more info visit:
http://ads.osdn.com/?ad_id=7628&alloc_id=16845&op=click
_______________________________________________
Flamerobin-devel mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/flamerobin-devel
Reply | Threaded
Open this post in threaded view
|

Re: An updatable grid

Milan Babuskov-5
In reply to this post by Michael Hieke
Michael Hieke wrote:
> So it's maybe easiest to not require the DBKey

I investigated a little bit more. In order to update multiple tables
(joins), we need to get DBKey for each table. So select would have to be
changed to include DBKeys for each table in statement:

select t1.value, t2.value, t1.rdb$db_key, t2.rdb$db_key
from table1 t1
join table2 t2 on t2.fk = t1.pk
...

Given all the possible problems (we would need to parse the statement
carefully), I suggest we either give up the DBKey idea for now, or only
give a single-table editing option: an "Edit data" (context) menu item.

--
Milan Babuskov
http://www.flamerobin.org



-------------------------------------------------------
This SF.Net email is sponsored by the JBoss Inc.  Get Certified Today
Register for a JBoss Training Course.  Free Certification Exam
for All Training Attendees Through End of 2005. For more info visit:
http://ads.osdn.com/?ad_id=7628&alloc_id=16845&op=click
_______________________________________________
Flamerobin-devel mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/flamerobin-devel
Reply | Threaded
Open this post in threaded view
|

Re: An updatable grid

Olivier Mascia
Le 19 nov. 05 à 19:08, Milan Babuskov a écrit :

> I investigated a little bit more. In order to update multiple  
> tables (joins), we need to get DBKey for each table. So select  
> would have to be changed to include DBKeys for each table in  
> statement:
>
> select t1.value, t2.value, t1.rdb$db_key, t2.rdb$db_key
> from table1 t1
> join table2 t2 on t2.fk = t1.pk
> ...
>
> Given all the possible problems (we would need to parse the  
> statement carefully), I suggest we either give up the DBKey idea  
> for now, or only give a single-table editing option: an "Edit  
> data" (context) menu item.

Hmm. Maybe you could Prepare() the original statement, and then use  
ColumnTable() on each column to find how many different tables are  
referenced in the statement. Updating it to add the required rdb
$db_key might then be much simpler (adding then before the first  
FROM) and call Prepare() again. Might not require a full parser. Just  
a 2c idea, though.

--
Olivier





-------------------------------------------------------
This SF.Net email is sponsored by the JBoss Inc.  Get Certified Today
Register for a JBoss Training Course.  Free Certification Exam
for All Training Attendees Through End of 2005. For more info visit:
<a href="http://ads.osdn.com/?ad_idv28&alloc_id845&op=click">http://ads.osdn.com/?ad_idv28&alloc_id845&op=click
_______________________________________________
Flamerobin-devel mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/flamerobin-devel