Using default values when inserting records

[ShaharHD]

I've been trying to get a working example of inserting records with a field which has a default based on datetime("now", "localtime") or default_value("CURRENT_TIMESTAMP") (un-comment #define CURRENT_TIMESTAMP)

Here's my short example test code and would love some insight on what I'm doing wrong as when I inject the new record, the createdAt field is empty.

But when manualy running a SQL INSERT the default value is injected currectly, so somehow I'm missing right syntax on the SQLite ORM side

Tried also with std::optional<std::string>> but then createdAt is missing the NOT NULL designation.
(un-comment #define OPTIONAL)

#include <fmt/core.h>
#include <memory>
#include <optional>
#include <spdlog/spdlog.h>
#include <sqlite_orm/sqlite_orm.h>
#include <string>

// #define OPTIONAL

// #define CURRENT_TIMESTAMP

struct Personnel {
  public:
    std::int32_t  id;
    std::string   name;
    std::int32_t  age;
    std::string   address;
    std::string   designation;
    std::uint64_t bigNumber;

#ifdef OPTIONAL
    std::optional<std::string> createdAt;
#else
    std::string createdAt;
#endif
};

int main(int argc, char **argv)
{
    Personnel person{-1, "Test", 21, "Somewhere", "Mr.", 7903197353};

    const auto personnelTable = sqlite_orm::make_table(
        "personnel",                                                                              //
        sqlite_orm::make_column("id", &Personnel::id, sqlite_orm::primary_key().autoincrement()), //
        sqlite_orm::make_column("name", &Personnel::name),                                        //
        sqlite_orm::make_column("age", &Personnel::age),                                          //
        sqlite_orm::make_column("address", &Personnel::address),                                  //
        sqlite_orm::make_column("designation", &Personnel::designation),                          //
#ifdef CURRENT_TIMESTAMP
        sqlite_orm::make_column("createdAt", &Personnel::createdAt, sqlite_orm::default_value("CURRENT_TIMESTAMP")), //
#else
        sqlite_orm::make_column("createdAt", &Personnel::createdAt, sqlite_orm::default_value(sqlite_orm::datetime("now", "localtime"))), //
#endif
        sqlite_orm::make_column("bigNumber", &Personnel::bigNumber) //
    );

    const std::string dbFile{"db.sqlite"};

    try {
        auto storage = sqlite_orm::make_storage(dbFile, personnelTable);
        storage.sync_schema(true);

        spdlog::info("Database opened: {0}", dbFile);

        auto insertedId = storage.insert(person);
        spdlog::info("insert: {0}", insertedId);

        auto newPerson = storage.get<Personnel>(insertedId);

#ifdef OPTIONAL
        spdlog::info("read: \"{0}\", \"{1}\", \"{2}\"", newPerson.id, newPerson.name,
                     newPerson.createdAt.has_value() ? *newPerson.createdAt : "NULL");
#else
        spdlog::info("get: \"{0}\", \"{1}\", \"{2}\"", newPerson.id, newPerson.name, newPerson.createdAt);
#endif

        newPerson.name = fmt::format("{0}-{0}", person.name);
        storage.update(newPerson);

        auto personP = storage.get_pointer<Personnel>(insertedId);
        if (personP) {

#ifdef OPTIONAL
            spdlog::info("read: \"{0}\", \"{1}\", \"{2}\"", personP->id, personP->name,
                         personP->createdAt.has_value() ? *personP->createdAt : "NULL");
#else
            spdlog::info("get: \"{0}\", \"{1}\", \"{2}\"", personP->id, personP->name, personP->createdAt);
#endif
        }
    } catch (std::system_error error) {
        spdlog::error(error.what());
    }

    return 0;
}

$ rm db.sqlite
$ build/sqlite-test
[2024-06-12 11:15:13.697] [info] Database opened: db.sqlite
[2024-06-12 11:15:13.701] [info] insert: 1
[2024-06-12 11:15:13.702] [info] get: "1", "Test", ""
[2024-06-12 11:15:13.704] [info] get: "1", "Test-Test", ""

$ sqlite3 db.sqlite .schema
CREATE TABLE IF NOT EXISTS "personnel" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "name" TEXT NOT NULL , "age" INTEGER NOT NULL , "address" TEXT NOT NULL , "designation" TEXT NOT NULL , "createdAt" TEXT DEFAULT ((DATETIME('now', 'localtime'))) NOT NULL , "bigNumber" INTEGER NOT NULL );
CREATE TABLE sqlite_sequence(name,seq);

[fnc12]

1. You use CURRENT_TIMESTAMP incorrectly. Replace "CURRENT_TIMESTAMP" with current_timestamp().
2. storage.insert inserts all fields except primary key columns. It ignores DEFAULT constraints. If you want to skip non-primary key columns then you need to use storage.insert_into function - it allows specifying every column you want to insert just like you write it in raw SQL but without raw string queries

[ShaharHD]

1. Can't find sqlite_orm::current_timestamp(), looking at the sqlite_orm.h I can see the only function without a parameter is inside the sqlite_orm::internal namespace and inside storage_base struct

Do I need to include another h file?

2. Found the clean solution, not using insert(into<T> but rather with

   auto insertedId = storage.insert(person, sqlite_orm::columns(&Personnel::name, &Personnel::age, &Personnel::address,
                                                                   &Personnel::designation, &Personnel::bigNumber));

   So all good on 2 (and I think I can optimize it)

[fnc12]

1. Check sqlite_orm's version. This func is available in dev branch not master cause it is fresh https://github.com/fnc12/sqlite_orm/blob/dev/tests/constraints/default.cpp#L99
2. Good. I really forgot about this version of insert 😅

[ShaharHD]

1. Don't want to use dev, I'll stick with sqlite_orm::default_value(sqlite_orm::datetime("now", "localtime")))
   Any planned ETA for next release version?
2. The issue with the other insert version, is that it doesn't return sqlite3_last_insert_rowid which was critical for me.

FFR (if someone searches for this), this is the optimization I was able to use:

struct Personnel {
  public:
    std::int32_t               id;
    std::string                name;
    std::int32_t               age;
    std::string                address;
    std::string                designation;
    std::optional<std::string> createdAt;
    std::uint64_t              bigNumber;

    /* Can be an operator for shorter syntax if using C++23 */
    inline static const auto columns()
    {
        static const auto columns =
            sqlite_orm::columns(&Personnel::name, &Personnel::age, &Personnel::address, &Personnel::designation, &Personnel::bigNumber);

        return columns;
    }
};

And when inserting

auto insertedId = storage.insert(person, Personnel::columns());

@fnc12 Thank you very much for a quick response on this one.

[ShaharHD]

Complete solution FFR

#include <fmt/core.h>
#include <memory>
#include <optional>
#include <spdlog/spdlog.h>
#include <sqlite_orm/sqlite_orm.h>
#include <string>

#include <optional>
#include <sqlite_orm/sqlite_orm.h>
#include <string>

struct Personnel {
  public:
    std::int32_t               id;
    std::string                name;
    std::int32_t               age;
    std::string                address;
    std::string                designation;
    std::optional<std::string> createdAt;
    std::uint64_t              bigNumber;

    inline static const auto columns()
    {
        static const auto columns =
            sqlite_orm::columns(&Personnel::name, &Personnel::age, &Personnel::address, &Personnel::designation, &Personnel::bigNumber);

        return columns;
    }
};

int main(int argc, char **argv)
{
    Personnel person{-1, "Test", 21, "Somewhere", "Mr.", std::nullopt, 7903197353};

    const auto personnelTable = sqlite_orm::make_table(
        "personnel",                                                                                                                      //
        sqlite_orm::make_column("id", &Personnel::id, sqlite_orm::primary_key().autoincrement()),                                         //
        sqlite_orm::make_column("name", &Personnel::name),                                                                                //
        sqlite_orm::make_column("age", &Personnel::age),                                                                                  //
        sqlite_orm::make_column("address", &Personnel::address),                                                                          //
        sqlite_orm::make_column("designation", &Personnel::designation),                                                                  //
        sqlite_orm::make_column("createdAt", &Personnel::createdAt, sqlite_orm::default_value(sqlite_orm::datetime("now", "localtime"))), //
        sqlite_orm::make_column("bigNumber", &Personnel::bigNumber)                                                                       //
    );

    const std::string dbFile{"db.sqlite"};

    try {
        auto storage   = sqlite_orm::make_storage(dbFile, personnelTable);
        auto timestamp = storage.current_timestamp();
        storage.sync_schema(true);

        spdlog::info("Database opened: {0}", dbFile);

        auto insertedId = storage.insert(person, Personnel::columns());

        spdlog::info("insert: {0}", insertedId);

        auto newPerson = storage.get<Personnel>(insertedId);

        spdlog::info("read: \"{0}\", \"{1}\", \"{2}\"", newPerson.id, newPerson.name,
                     newPerson.createdAt.has_value() ? *newPerson.createdAt : "NULL");

        newPerson.name = fmt::format("{0}-{0}", person.name);
        storage.update(newPerson);

        if (auto personP = storage.get_pointer<Personnel>(insertedId); personP) {
            spdlog::info("read: \"{0}\", \"{1}\", \"{2}\"", personP->id, personP->name,
                         personP->createdAt.has_value() ? *personP->createdAt : "NULL");
        }
    } catch (std::system_error error) {
        spdlog::error(error.what());
    }

    return 0;
}

[fnc12]

@ShaharHD you can get last inserted row id from the storage like this:

storage.insert(into<...>, ...);
auto insertedId = storage.last_insert_rowid();

[ShaharHD]

Yes, but then I have to make sure no one else has another "write" operation before I ask for the last inserted row id
(which might be an issue in multi-threded environment and add complexitiy beside just lock the write)

I have one last quesion, how can I force createdAt to be marked as NOT NULL while still using std::optional or that's a hard limitation?

[fnc12]

Yes, but then I have to make sure no one else has another "write" operation before I ask for the last inserted row id
(which might be an issue in multi-threded environment and add complexitiy beside just lock the write)

Just use exclusive transaction for inserting and last insert rowid obtaining .

I have one last quesion, how can I force createdAt to be marked as NOT NULL while still using std::optional or that's a hard limitation?

Two solutions:

1. make your custom type which contains something nullable like std::optional and make it not nullable using type constraints type_is_nullable
2. skip sync_schema and change the schema by yourself to NOT NULL

[ShaharHD]

Yes, but then I have to make sure no one else has another "write" operation before I ask for the last inserted row id
(which might be an issue in multi-threded environment and add complexitiy beside just lock the write)

Just use exclusive transaction for inserting and last insert rowid obtaining .
Which is the complexity I want to avoid in some cases.

1. make your custom type which contains something nullable like std::optional and make it not nullable using type constraints type_is_nullable

I'm trying this and will report.

2. skip sync_schema and change the schema by yourself to NOT NULL

No 2 adds a lot of work, which is the intenation to use ORM.

[trueqbit]

sqlite_orm has explicit null() and not_null() column constraints (still in the dev branch)...