Bug #36740 for Gtk2-Ex-Datasheet-DBI: multiple columns for primary key

Fri Jun 13 20:39:51 2008 user42 [...] zip.com.au - Ticket created

Subject:	multiple columns for primary key
Date:	Sat, 14 Jun 2008 10:38:18 +1000
To:	bug-gtk2-ex-datasheet-dbi [...] rt.cpan.org
From:	Kevin Ryde <user42 [...] zip.com.au>

I tried editing a row in a datasheet where the primary key had multiple columns, and the edit was applied to multiple rows of the table. It'd be good if multi-column primary keys could be supported. (I'm using sqlite and the "primary_key_info" already in use has all the columns.) Or if it's a bit hard for the moment I guess going read-only on multi-column primary key would prevent strange updates etc.

Sat Jun 14 06:56:34 2008 dan [...] entropy.homelinux.org - Correspondence added

Subject:	Re: [rt.cpan.org #36740] multiple columns for primary key
Date:	Sat, 14 Jun 2008 20:36:57 +1000
To:	bug-Gtk2-Ex-Datasheet-DBI [...] rt.cpan.org, user42 [...] zip.com.au
From:	Daniel Kasak <dan [...] entropy.homelinux.org>

On Fri, 2008-06-13 at 20:39 -0400, Kevin Ryde via RT wrote: Show quoted text

> Fri Jun 13 20:39:51 2008: Request 36740 was acted upon. > Transaction: Ticket created by user42@zip.com.au > Queue: Gtk2-Ex-Datasheet-DBI > Subject: multiple columns for primary key > Broken in: (no value) > Severity: (no value) > Owner: Nobody > Requestors: user42@zip.com.au > Status: new > Ticket <URL: http://rt.cpan.org/Ticket/Display.html?id=36740 > > > > I tried editing a row in a datasheet where the primary key had multiple > columns, and the edit was applied to multiple rows of the table. > > It'd be good if multi-column primary keys could be supported. (I'm > using sqlite and the "primary_key_info" already in use has all the > columns.)

Hi. Thanks for using my software :) The only setup we support is an auto-incrementing primary key. I should probably make this clear in the documentation. If you have a read of the insert, update and last_insert_id methods of Gtk2::Ex::DBI & Gtk2::Ex::Datasheet::DBI, you'll see why. You can keep your existing multi-column KEY, but you should add an auto_incrementing primary key to all tables you want to edit. This requirement is in no way exclusive to Gtk2::Ex::DBI & Gtk2::Ex::Datasheet::DBI. The only app that I know of that allows anything else is MS Access, and it uses timestamps instead. Having said that, if you can add support for multi-column primary keys without breaking things, I'll happily add it. It's never really crossed my mind to do it. I *did* think about supporting non-incrementing primary keys, but that lead to a number of problems that made me change my mind. Show quoted text

> Or if it's a bit hard for the moment I guess going read-only > on multi-column primary key would prevent strange updates etc.

Yes that makes more sense. I already do this when I find a table with *no* primary key. I'll have to make myself such a table and test to see how to detect for it in various DB servers. I'll be working on a new release over the next couple of days. I'll try to add this to the list. Unfortunately I lost my entire website in a server meltdown ( again ). But I'll release via cpan soon. Thanks again for trying out my software. Of course, I think you should also check out Gtk2::Ex::DBI, which you use with Glade to create forms. Gtk2::Ex::Datasheet::DBI is based on Gtk2::Ex::DBI. Dan

Sat Jun 14 06:56:37 2008 The RT System itself - Status changed from 'new' to 'open'

Sun Jun 15 20:18:19 2008 user42 [...] zip.com.au - Correspondence added

Subject:	Re: [rt.cpan.org #36740] multiple columns for primary key
Date:	Mon, 16 Jun 2008 10:17:29 +1000
To:	bug-Gtk2-Ex-Datasheet-DBI [...] rt.cpan.org
From:	Kevin Ryde <user42 [...] zip.com.au>

Daniel Kasak <dan@entropy.homelinux.org> writes: Show quoted text

> > The only setup we support is an auto-incrementing primary key.

I didn't realize that. Show quoted text

> If you have a read of the insert, update and last_insert_id methods of > Gtk2::Ex::DBI & Gtk2::Ex::Datasheet::DBI, you'll see why.

Ah. I only got as far as the "update" in apply() of CHANGED, which looked like it might take anything for the "$primary_key=?" bit. Show quoted text

> You can keep your existing multi-column KEY, but you should add an > auto_incrementing primary key to all tables you want to edit.

That might be possible, I've got a couple of tables with my own id numbers to uniquify rows anyway. Though I've also got bits like CREATE TABLE split ( symbol TEXT NOT NULL, date DATE NOT NULL, new TEXT NOT NULL, -- number old TEXT NOT NULL, -- number note TEXT DEFAULT NULL, PRIMARY KEY (symbol, date)) or even a primary key "(symbol, date, type)" for multiple flavours of row with the same date. I made my own custom TreeModel for a big data block, but for the rest I was thinking "why can't someone else do it?" :-)

Mon May 31 20:07:38 2010 user42 [...] zip.com.au - Correspondence added

Subject:	Re: [rt.cpan.org #36740] multiple columns for primary key
Date:	Tue, 01 Jun 2010 10:07:19 +1000
To:	bug-Gtk2-Ex-Datasheet-DBI [...] rt.cpan.org
From:	Kevin Ryde <user42 [...] zip.com.au>

Daniel Kasak <dan@entropy.homelinux.org> writes: Show quoted text

> > The only setup we support is an auto-incrementing primary key. I should > probably make this clear in the documentation.

Is it possible to ask the datasheet to use the sqlite ROWID if I don't otherwise have a suitable primary key? I suppose the datasheet code would have to look at $dbh->last_insert_id() when inserting.