Skip Menu |

Preferred bug tracker

Please visit the preferred bug tracker to report your issue.

This queue is for tickets about the Dancer-Plugin-Database CPAN distribution.

Report information
The Basics
Id: 83344
Status: open
Priority: 0/
Queue: Dancer-Plugin-Database
People
Owner: Nobody in particular
Requestors: dfaber [...] gmail.com
Cc:
AdminCc:
Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: (no value)

History Show all quoted text
  Fri Feb 15 08:43:12 2013 dfaber [...] gmail.com - Ticket created
Subject: Feature request: quick_insert returning value
Date: Fri, 15 Feb 2013 14:42:55 +0100
To: bug-dancer-plugin-database [...] rt.cpan.org
From: Dirk-Jan Faber <dfaber [...] gmail.com>
Hello, I love the simplicity of the convenience features of the Dancer::Plugin::Database, but is it possible to get quick_insert returning it's id? For example, the following works on postgres: insert into item(description) values('blaat') returning item_id; I'd like to do something like: my $item_id = database->quick_insert('item', { 'description' => 'blaat' }); Instead of my current usage of: database->quick_insert('item', { 'description' => 'blaat' }); my $item_id = database->quick_lookup('item', { 'description' => 'blaat' }, 'item_id'); I think it would be a handy and cool feature, though I do realize that not all of the database are aware of the 'RETURNING' syntax. SQLite doesn't know it and Oracle implements it slightly different. Regards, Dirk-Jan
  Fri Feb 15 08:57:12 2013 davidp [...] preshweb.co.uk - Correspondence added
That would indeed be useful. Working out the best way to do it for each DB type would be the most difficult aspect, I think. For MySQL, the plugin could automatically SELECT LAST_INSERT_ID() and return that. For databases like Postgres, it would need to know which column it should ask the DB to return the value of, wouldn't it? If it doesn't know how to return the ID, it should probably return 0E0, so it's still true if used in a boolean context (which I think is how the majority of people will use it) but distinguishable from returning an actual ID. DBI offers a `last_insert_id` method - but its behaviour and usage is driver-specific and unpredictable. I'll try to look in to this further when time permits; suggestions / pull requests welcomed.
  Fri Feb 15 08:57:14 2013 The RT System itself - Status changed from 'new' to 'open'
  Fri Feb 15 09:25:50 2013 dfaber [...] gmail.com - Correspondence added
Subject: Re: [rt.cpan.org #83344] Feature request: quick_insert returning value
Date: Fri, 15 Feb 2013 15:25:24 +0100
To: bug-Dancer-Plugin-Database [...] rt.cpan.org
From: Dirk-Jan Faber <dfaber [...] gmail.com>
On Feb 15, 2013, at 2:57 PM, David Precious via RT wrote: Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=83344 > > > That would indeed be useful. > > Working out the best way to do it for each DB type would be the most > difficult aspect, I think.
I think that is indeed the biggest problem. By searching the internet I could not determine wether this is actually an ISO/ANSI SQL standard. Show quoted text
> For MySQL, the plugin could automatically SELECT LAST_INSERT_ID() and > return that. > > For databases like Postgres, it would need to know which column it > should ask the DB to return the value of, wouldn't it?
From the documentation of Postgres, it features several options. From http://www.postgresql.org/docs/current/static/sql-insert.html: [ WITH [ RECURSIVE ] with_query [, ...] ] INSERT INTO table_name [ ( column_name [, ...] ) ] { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query } [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ] Personally I'd only use it for getting the id back, but others might want to use expressions and the output_names too. Which will make implementation even harder. Show quoted text
> If it doesn't know how to return the ID, it should probably return 0E0, > so it's still true if used in a boolean context (which I think is how > the majority of people will use it) but distinguishable from returning > an actual ID.
True. Show quoted text
> DBI offers a `last_insert_id` method - but its behaviour and usage is > driver-specific and unpredictable. > > I'll try to look in to this further when time permits; suggestions / > pull requests welcomed.
I could live with the ability to use something like: my $id = database->quick_insert('item', { 'description => 'blurp' }, 'returning item_id' }; But I am the first to admit that this will not win any prices for it's beauty. Regards, Dirk-Jan