[odb-users] Use SQLite functions to create timestamps by default

[Henri Schwarz]

Hi,

in chapter 14.4.7 of the documentation is an example how to set the default
value of a column to the current timestamp.

#pragma db options("DEFAULT CURRENT_TIMESTAMP()")

date timestamp_; // DEFAULT CURRENT_TIMESTAMP()

I tried this using SQLite but it doesn't seem to work right. At first I had
to remove the parentheses at the end (throws exception on schema creation).
I found out that *date* is a typedef for* long* which is mapped to an
INTEGER but the SQLite documentation states that CURRENT_TIMESTAMP returns
a timestring (TEXT).

If the default value of a column is CURRENT_TIME, CURRENT_DATE or
CURRENT_TIMESTAMP, then the value used in the new row is a text
representation of the current UTC date and/or time. /*...*/ The format for
CURRENT_TIMESTAMP is "YYYY-MM-DD HH:MM:SS".

In the means of SQLite this shouldn't be a problem due to the dynamic
typing.

So with a persistent class member declared as *date* or *long* I end up
with a timestamp in the database that seems to be the positive maximum for
the SQLite integer type (4777546605244151150) since it doesn't change when
I recreate the database and start all over.

Nevertheless when I change the timestamp_ member in the persistent class to
*std::string* or use the pragma *type("TEXT")* the column remains empty
when I create items in the table through ODB.

The CREATE TABLE statement with the *std:string* timestamp_ member looks as
follows.


When I insert items manually (using SQLite Browser) the timestring is put
in the row correctly.

So am I getting something completely wrong here or could this even be a
bug?

[Henri Schwarz]

Whoops,

forgot to insert the CREATE TABLE statement. Here you go.

CREATE TABLE `connection` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`type` INTEGER NOT NULL,
`timestamp` TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
`typeid` TEXT NOT NULL,
`name` TEXT NOT NULL,
`is_default` INTEGER NOT NULL,
`child` INTEGER,
FOREIGN KEY(`child`) REFERENCES `session`(`id`) DEFERRABLE INITIALLY
DEFERRED
);

Regards.

Post by Henri Schwarz
Hi,
in chapter 14.4.7 of the documentation is an example how to set the
default value of a column to the current timestamp.
#pragma db options("DEFAULT CURRENT_TIMESTAMP()")
date timestamp_; // DEFAULT CURRENT_TIMESTAMP()
I tried this using SQLite but it doesn't seem to work right. At first I
had to remove the parentheses at the end (throws exception on schema
creation). I found out that *date* is a typedef for* long* which is
mapped to an INTEGER but the SQLite documentation states that
CURRENT_TIMESTAMP returns a timestring (TEXT).
If the default value of a column is CURRENT_TIME, CURRENT_DATE or
CURRENT_TIMESTAMP, then the value used in the new row is a text
representation of the current UTC date and/or time. /*...*/ The format for
CURRENT_TIMESTAMP is "YYYY-MM-DD HH:MM:SS".
In the means of SQLite this shouldn't be a problem due to the dynamic
typing.
So with a persistent class member declared as *date* or *long* I end up
with a timestamp in the database that seems to be the positive maximum for
the SQLite integer type (4777546605244151150) since it doesn't change when
I recreate the database and start all over.
Nevertheless when I change the timestamp_ member in the persistent class
to *std::string* or use the pragma *type("TEXT")* the column remains
empty when I create items in the table through ODB.
The CREATE TABLE statement with the *std:string* timestamp_ member looks
as follows.
When I insert items manually (using SQLite Browser) the timestring is put
in the row correctly.
So am I getting something completely wrong here or could this even be a
bug?

[Boris Kolpackov]

Post by Henri Schwarz
#pragma db options("DEFAULT CURRENT_TIMESTAMP()")
date timestamp_; // DEFAULT CURRENT_TIMESTAMP()
I tried this using SQLite but it doesn't seem to work right. At first I had
to remove the parentheses at the end (throws exception on schema creation).
I found out that *date* is a typedef for* long* which is mapped to an
INTEGER but the SQLite documentation states that CURRENT_TIMESTAMP returns
a timestring (TEXT).

The example shows how to use the options pragma. The type used for
timestamp is "a date" not anything specific.

Post by Henri Schwarz
Nevertheless when I change the timestamp_ member in the persistent class to
*std::string* [...] the column remains empty when I create items in the
table through ODB.

For the column to be assigned default value it must be unspecified or
NULL on insertion. Try something like this:

#include <odb/nullable.hxx>

#pragma db options("DEFAULT CURRENT_TIMESTAMP()")
odb::nullable<std::string> timestamp_;

Boris

[Henri Schwarz]

Hi Boris,

thanks for your advice. I didn't use the nullable type so far and as
expected the class member is now "NULL" in the database but still the
default rule doesn't apply. I'll build a small example around this issue to
investigate what's happening.

Kind regards,

Henri

Post by Henri Schwarz

Post by Henri Schwarz
#pragma db options("DEFAULT CURRENT_TIMESTAMP()")
date timestamp_; // DEFAULT CURRENT_TIMESTAMP()
I tried this using SQLite but it doesn't seem to work right. At first I

had

Post by Henri Schwarz
to remove the parentheses at the end (throws exception on schema

creation).

Post by Henri Schwarz
I found out that *date* is a typedef for* long* which is mapped to an
INTEGER but the SQLite documentation states that CURRENT_TIMESTAMP

returns

Post by Henri Schwarz
a timestring (TEXT).

The example shows how to use the options pragma. The type used for
timestamp is "a date" not anything specific.

Post by Henri Schwarz
Nevertheless when I change the timestamp_ member in the persistent class

to

Post by Henri Schwarz
*std::string* [...] the column remains empty when I create items in the
table through ODB.

For the column to be assigned default value it must be unspecified or
#include <odb/nullable.hxx>
#pragma db options("DEFAULT CURRENT_TIMESTAMP()")
odb::nullable<std::string> timestamp_;
Boris