sqlite and default - now() value for datetime column

[kuba]

is there any way to achive it on database level ? I thought about
db.sqlexecute() but sqlite doesn't support alter column.

Thx for help

[mdipierro]

I do not understand the question

[kuba]

I will try to explain it better:

for example one of my tables looks like this

db.define_table("active_task",
SQLField('parameters', 'string'),
SQLField('add_date', 'datetime'),
SQLField('state', 'integer'))

What I'm trying to achieve is that every time someone inserts a row -
column add_date should point to actual time of insertion.

The easiest way for me is default value in database:

add_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

but i can't make such statement with SQLField class ?

i found there is something like
'notnull': 'NOT NULL DEFAULT %(default)s',

in sql.py
but I have no idea how to make use of it

[Hans Donner]

Have you tried the 'default' keyword for SQLField?

SQLFIELD('add_date', 'datetime', default=<insert your default here>)?

[kuba]

On 27 Cze, 22:09, Hans Donner <hans.don...@pobox.com> wrote:
> Have you tried the 'default'  keyword for SQLField?
>
> SQLFIELD('add_date', 'datetime', default=<insert your default here>)?
>

yes, I thought also about it.

The problem is that it accepts datetime type (or what ever it is) not
string

i can only pass request.now to it - and if use crud.create.create()
with such table it automaticly initialize the field - so if user waits
some time with button clicking the value is not exacly the time of
insertion.

[mdipierro]

You can do it with a trick but I cannot promise it will work with the
new DAL

from gluon.sql import SQLXorable
db.define_table(....
SQLFIELD('add_date', 'datetime', default=SQLXorable("any SQL code
here"))
)

Anyway, I think it works but I am not 100% sure.

Massimo

[Hans Donner]

Yes, can see the value of having some statement executed right before
the actual insert (or update).
Massimo, would it be possible wit the new DAL to provide some hooks
into the table that can do some pre insert, update etc stuff? (or
provide a way to specify a new table instance where we can do speciifc
stuff).

This way we can add some common meta data to a table like
inserted/modified stamps and/or logging to a differen table for
specials cases without having to rely on the backend or having to
build it into other logic.

[mdipierro]

Good idea.

Massimo

On Jun 28, 12:38 am, Hans Donner <hans.don...@pobox.com> wrote:
> Yes, can see the value of having some statement executed right before
> the actual insert (or update).
> Massimo, would it be possible wit the new DAL to provide some hooks
> into the table that can do some pre insert, update etc stuff? (or
> provide a way to specify a new table instance where we can do speciifc
> stuff).
>
> This way we can add some common meta data to a table like
> inserted/modified stamps and/or logging to a differen table for
> specials cases without having to rely on the backend or having to
> build it into other logic.
>