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

[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.


2017-06-15 9:57 GMT+02:00 Henri Schwarz <il1k3pepsi at gmail.com>:

> 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?
>
>
>
>
>
>
>
>
>
>
>