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: 97735
Status: open
Priority: 0/
Queue: Dancer-Plugin-Database
People
Owner: Nobody in particular
Requestors: phoemix [...] harmless.hu
Cc:
AdminCc:
Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: (no value)

History Show all quoted text
  Mon Aug 04 05:47:36 2014 phoemix [...] harmless.hu - Ticket created
Subject: Prepared queries at connect time
Date: Mon, 04 Aug 2014 09:47:23 +0000
To: bug-dancer-plugin-database [...] rt.cpan.org
From: Czuczy Gergely <phoemix [...] harmless.hu>
Hello, I'm trying to set up a bunch of prepared queries (using dancer1 atm) which are being used quite frequently for my application at connection time, and just keep on executing them during runtime. However, the database_connected hook passes the DBI handler before being reblessed to the handle_class, this makes this slightly messy, because the returned $sth handlers have to be stored somewhere (DBI has the private_foo stuff for private data, which is a workaround). I was wonder, wouldn't it be possible to add a facility for this kind of usage by supporting prepared query definitions in the YAML config and just referencing them runtime? The database plugin could be preparing all these during connection time automatically, and such statement's handler could be fetched by something like database->pquery($foo). This sounds conventient to me. I haven't checked other applications, however I expect preparing queries at connection time to be a common practice, as it is a good one, then just executing them during runtime, therefore supporting this kind of usage seems logical to me. So, what are your thoughts regarding this? Regards, Gergey
  Mon Aug 04 06:03:14 2014 davidp [...] preshweb.co.uk - Correspondence added
On 2014-08-04 10:47:36, phoemix@harmless.hu wrote: Show quoted text
> I was wonder, wouldn't it be possible to add a facility for this > kind of usage by supporting prepared query definitions in the YAML > config and just referencing them runtime? The database plugin could be > preparing all these during connection time automatically, and such > statement's handler could be fetched by something like > database->pquery($foo).
Hmm, interesting idea - that could be useful in some cases. I guess it would need to automatically add mysql_server_prepare=1 to the DSN if it was connecting to a MySQL database (as, otherwise, server-side prepared queries don't actually do anything, DBI will emulate it instead). I don't think I'll have any time to implement this for some time, but if you wish to contribute it, a pull request would be very welcome indeed! I guess they'd be defined in the config with a name, and that name used later when calling execute_prepared_query() or similarly named method... e.g.: # in config: prepared_queries => { update_count => "UPDATE people SET name = ? WHERE id = ?", }, and then at runtime: database()->execute_prepared_query('update_count', 'Bob', 42);
  Mon Aug 04 06:03:14 2014 The RT System itself - Status changed from 'new' to 'open'
  Mon Aug 04 13:53:26 2014 gergely.czuczy [...] harmless.hu - Correspondence added
Subject: Re: [rt.cpan.org #97735] Prepared queries at connect time
Date: Mon, 04 Aug 2014 19:53:14 +0200
To: bug-Dancer-Plugin-Database [...] rt.cpan.org
From: Gergely Czuczy <gergely.czuczy [...] harmless.hu>
On 2014.08.04. 12:03, David Precious via RT wrote: Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=97735 > > > On 2014-08-04 10:47:36, phoemix@harmless.hu wrote:
>> I was wonder, wouldn't it be possible to add a facility for this >> kind of usage by supporting prepared query definitions in the YAML >> config and just referencing them runtime? The database plugin could be >> preparing all these during connection time automatically, and such >> statement's handler could be fetched by something like >> database->pquery($foo).
> > Hmm, interesting idea - that could be useful in some cases. > > I guess it would need to automatically add mysql_server_prepare=1 to the DSN if it was connecting to a MySQL database (as, otherwise, server-side prepared queries don't actually do anything, DBI will emulate it instead). > > I don't think I'll have any time to implement this for some time, but if you wish to contribute it, a pull request would be very welcome indeed! > > I guess they'd be defined in the config with a name, and that name used later when calling execute_prepared_query() or similarly named method... e.g.: > > # in config: > prepared_queries => { > update_count => "UPDATE people SET name = ? WHERE id = ?", > }, > > and then at runtime: > > database()->execute_prepared_query('update_count', 'Bob', 42); > >
Something like that. Would be also nice to have them server-side prepared in as many cases as possible. For example PostgreSQL (what I'm using) runs the GEQO at prepare-time, saving effort at runtime, so it actually has benefit, especially for increased load. Regarding the execute() stuff, you could just model DBI's functionality. Actually returning the $sth from the DBI would be quite convenient. Also, there's the named parameter convention, where at prepare-time you name the params, and pass a hash to $sth's execute(), this ensures proper placement. Regarding time, I also don't have much. Doing my project, work, and also have to care for "overrated real-life things", and i'm left with little to no spare effort. But I can wait and later migrate over to a more mature solution, even to Dancer2. Regards, -czg