Way to force inlining of default column values?

[xcdc...@gmail.com]

Hello. I am currently facing two problems with Rose::DB::Object.

Problem #1.

I have a timestamp column in database with default value of "TIMEZONE('UTC', NOW())" which

is a Postgres-specific way of saying "current time in UTC timezone". The only way to make this sort

of default work in RDBO seems to be to enable $db->keyword_function_calls, which seems dangerous.

Is there any other way to make a timestamp column with default value like above in RDBO that does not

involve enabling keyword_function_calls?

Problem #2.

I have a uuid type column in a table in a postgres db. Seeing how there is no support for this specific

column type, I went to see what auto_init will come up with for it, and it produced as type => 'scalar'

column, so I went with it. But my column is defined in database with default "uuid_generate_v4()" and

there does not seem to be any way to make this work with RDBO at all.

A new column metadata option that could be used instead of 'default' could help solve both of these

inline_default => "TIMEZONE('UTC', NOW())"

and

inline_default => "uuid_generate_v4()"

But since such option is not available I would like to ask if there are any solutions to the above problems

(that do not involve keyword_function_calls).

Thank you in advance.

[Randall Sindlinger]

Hello,

I looked back at a project that I'd worked on before, perhaps these insights will help you.  In short, I don't think you need to enforce the defaults at the RDBO level; when I faced a similar issue I left the defaults in postgres.  Nowhere was db->keyword_function_calls enabled.

Specifically, for #1 - the timestamp column was defined (without a timezone) as
CREATE TABLE term_map (
    ...
    "timestamp" timestamp(3) without time zone DEFAULT now(),
    ...
)

For #2, the uuid field was defined using character varying, since RDBO doesn't support the postgres uuid column format.
CREATE TABLE term (
    identifier character varying DEFAULT uuid_generate_v1() NOT NULL,
    ...
)

The code was using RDBO from within Mojolicious, so new rows were an HTTPS post with a JSON payload.  In the posts, json hashes simply omitted the "timestamp" and the "identifier" (uuid) fields.  So, RDBO did the insert without any value specified for the column, and postgres just took care of generating the default values on its own.

Later on, for testing, I needed to know a priori what a record's UUID was.  For that, we used create_uuid_as_string from UUID::Tiny to generate and store a value, and then explicitly included the "identifier" field with the stored value, overriding the postgres default.

I hope that helps.

-Randall

--
Source: https://github.com/siracusa/rose
CPAN: http://search.cpan.org/dist/Rose-DB-Object
---
You received this message because you are subscribed to the Google Groups "Rose::DB::Object" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rose-db-object+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[xcdc...@gmail.com]

Thank you for the response, but unfortunately I can not use the proposed solution for uuids (due to some legacy code doing funky stuff), and as for timestamp: now() will us postgres' own timezone value, which is another thing to worry about / remember - I would prefer to have timezone specifically set in the db structure.

I still think that a way to specify default value in a form of inline sql (as described in the first email) would be a good feature - for the same exact reasons why "clauses" parameter exists in build_select (QueryBuilder.pm).