Re: [RDBO] help specifying a default datetime with SQLite and RDBO::Loader
On Aug 7, 2007 4:12 PM, George Hartzell [EMAIL PROTECTED] wrote:
The following little fragment of sql does what I'd like it to do,
inserts the current timestamp into the created_date column.
create table mooses (
id integer primary key autoincrement,
name text,
created_date datetime not null default current_timestamp
);
insert into mooses(name) values ('B. Moose');
insert into mooses(name) values ('Big Tex');
select * from mooses;
If I use Rose::DB::Object::Loader on the database created with the
above, it complains with:
insert() - Invalid default datetime: 'current_timestamp' at ... Object.pm
This is fixed in SVN. Such columns will not get a default value of now
-John
-
SF.Net email is sponsored by:
Check out the new SourceForge.net Marketplace.
It's the best place to buy or sell services
for just about anything Open Source.
http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace
___
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] help specifying a default datetime with SQLite and RDBO::Loader
John Siracusa writes:
On 8/14/07, George Hartzell [EMAIL PROTECTED] wrote:
The default value of now does work when used in the RDBO perl
module. The problem is that since I'm using RDBO::Loader, the only
way to get that value in there is to use it in the SQL, where it
doesn't do what is intended.
Remember that the Loader also provides hooks to modify the metadata
pulled from the database, both before and after class initialization.
In this case, you could add a pre_init_hook that walks over all the
columns and replaces any default current_datetime values on datetime
columns with now.
http://search.cpan.org/dist/Rose-DB-Object/lib/Rose/DB/Object/Loader.pm#pre_init_hook
I'll hold that in reserve, it seems like it's worth getting the
Rose::DB code to work with what SQLite uses (kinky as it may be).
Can you give me a hint on setting up the inlining? It looks like I'll
need to change
Rose::DB::Object::Metadata::Column::Timestamp::should_inline_value so
that it checks (off the top of my head)
($_[1]-driver eq 'sqlite' $_[2] =~ /current_datetime/i)
or something close to that. I'll have to figure out how to work it
into the existing ternary test.
You shouldn't need to modify should_inline_value(). Instead, add
current_datetime as a valid keyword for SQLite date/time columns.
To do this, alter the validate_*_keyword() method(s) in
Rose::DB::SQLite. This fix will be in the next release, but I haven't
don't it yet. If you do it first, feel free to post a patch :)
With a stock installation, using a default value of current_timestamp
in my sql causes an insert from perl to fail with
Invalid default datetime: 'current_timestamp' at .
So, I made the following change to Rose/DB/SQLite.pm (apeing the style
in Pg.pm)
--- /usr/local/lib/perl5/site_perl/5.8.8/Rose/DB/SQLite.pm.orig 2007-08-15
13:11:11.0 -0700
+++ /usr/local/lib/perl5/site_perl/5.8.8/Rose/DB/SQLite.pm 2007-08-15
13:08:22.0 -0700
@@ -56,7 +56,8 @@
sub validate_datetime_keyword
{
no warnings;
- !ref $_[1] $_[1] =~ /^\w+\(.*\)$/ ;
+ !ref $_[1] $_[1] =~ /^(?:current_timestamp|\w+\(.*\))$/ ;
+ #!ref $_[1] $_[1] =~ /^\w+\(.*\)$/ ;
}
and now the insert succeeds, but the value that gets inserted is the
literal string current_timestamp (w/out the double-quotation-marks).
I commented on this ealier in the thread and you thought that the
value needed to be inlined, which is where I got started with that idea.
[I know that I'm being sloppy about timestamps and datetimes and such,
and need to remember what the portable sql thingy to use is. I
*think* that's ortagonal to the problem I'm seeing...]
Suggestions?
g.
-
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems? Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now http://get.splunk.com/
___
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] help specifying a default datetime with SQLite and RDBO::Loader
John Siracusa writes: On 8/7/07 5:12 PM, George Hartzell wrote: If I change Rose::DB::SQLite::validate_datetime_keyword so that it'll accept 'current_timestamp' Yeah, I should do that... then the value gets inserted literally into the table. it probably also needs to be inlined (i.e., unquoted) I'd like to be able to specify the default behaviour in the schema, it'll be more robust if/when it's used outside of rdbo. A default value of now should work in all supported databases, IIRC. The default value of now does work when used in the RDBO perl module. The problem is that since I'm using RDBO::Loader, the only way to get that value in there is to use it in the SQL, where it doesn't do what is intended. Eventually I may stop generating the perl classes on the fly, but in the meantime I'd like it to do the right thing. Can you give me a hint on setting up the inlining? It looks like I'll need to change Rose::DB::Object::Metadata::Column::Timestamp::should_inline_value so that it checks (off the top of my head) ($_[1]-driver eq 'sqlite' $_[2] =~ /current_datetime/i) or something close to that. I'll have to figure out how to work it into the existing ternary test. Thanks, g. - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] help specifying a default datetime with SQLite and RDBO::Loader
On 8/14/07, George Hartzell [EMAIL PROTECTED] wrote: The default value of now does work when used in the RDBO perl module. The problem is that since I'm using RDBO::Loader, the only way to get that value in there is to use it in the SQL, where it doesn't do what is intended. Remember that the Loader also provides hooks to modify the metadata pulled from the database, both before and after class initialization. In this case, you could add a pre_init_hook that walks over all the columns and replaces any default current_datetime values on datetime columns with now. http://search.cpan.org/dist/Rose-DB-Object/lib/Rose/DB/Object/Loader.pm#pre_init_hook Can you give me a hint on setting up the inlining? It looks like I'll need to change Rose::DB::Object::Metadata::Column::Timestamp::should_inline_value so that it checks (off the top of my head) ($_[1]-driver eq 'sqlite' $_[2] =~ /current_datetime/i) or something close to that. I'll have to figure out how to work it into the existing ternary test. You shouldn't need to modify should_inline_value(). Instead, add current_datetime as a valid keyword for SQLite date/time columns. To do this, alter the validate_*_keyword() method(s) in Rose::DB::SQLite. This fix will be in the next release, but I haven't don't it yet. If you do it first, feel free to post a patch :) -John - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] help specifying a default datetime with SQLite and RDBO::Loader
On 8/7/07 5:12 PM, George Hartzell wrote: If I change Rose::DB::SQLite::validate_datetime_keyword so that it'll accept 'current_timestamp' Yeah, I should do that... then the value gets inserted literally into the table. it probably also needs to be inlined (i.e., unquoted) I'd like to be able to specify the default behaviour in the schema, it'll be more robust if/when it's used outside of rdbo. A default value of now should work in all supported databases, IIRC. -John - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
[RDBO] help specifying a default datetime with SQLite and RDBO::Loader
The following little fragment of sql does what I'd like it to do,
inserts the current timestamp into the created_date column.
create table mooses (
id integer primary key autoincrement,
name text,
created_date datetime not null default current_timestamp
);
insert into mooses(name) values ('B. Moose');
insert into mooses(name) values ('Big Tex');
select * from mooses;
If I use Rose::DB::Object::Loader on the database created with the
above, it complains with:
insert() - Invalid default datetime: 'current_timestamp' at ... Object.pm
If I change Rose::DB::SQLite::validate_datetime_keyword so that it'll
accept 'current_timestamp' then the value gets inserted literally into
the table.
I'd like to be able to specify the default behaviour in the schema,
it'll be more robust if/when it's used outside of rdbo.
Is there a proper way to make this work?
g.
-
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems? Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now http://get.splunk.com/
___
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
