Cannot create BOOLEAN and UNSIGNED BIG INT

[pavel-cpp]

It is critically important for me that some of the fields in the database are of these types.

create table Statistics
(
ID INTEGER not null
primary key autoincrement
unique,
PacketsCount UNSIGNED BIG INT,
FilesCount UNSIGNED BIG INT,
ErrorsCount UNSIGNED BIG INT,
DBConnected BOOLEAN default 0 not null
);

For this script, I use the following model:

struct statistics {
    int id;
    std::optional<uint64_t> packets_count;
    std::optional<uint64_t> files_count;
    std::optional<uint64_t> errors_count;
    bool db_connected;
};

make_table("Statistics",
    make_column("ID", &statistics::id, primary_key().autoincrement()),
    make_column("PacketsCount", &statistics::packets_count),
    make_column("FilesCount", &statistics::files_count),
    make_column("ErrorsCount", &statistics::errors_count),
    make_column("DBConnected", &statistics::db_connected, default_value(false))
),

But as a result, when calling sync_schema I get the following table

create table Statistics
(
ID INTEGER not null
primary key autoincrement,
PacketsCount INTEGER,
FilesCount INTEGER,
ErrorsCount INTEGER,
DBConnected INTEGER default 0 not null
);

How can I solve this problem?

[fnc12]

1. If you already have database you can skip calling sync_schema
2. types are not important if your table is not strict so probably it is not as important as you think. E.g. you can insert blob value in integer column and it will not throw any error
3. boolean type is not built-in in SQLite. If you want sync_schema to create BOOLEAN type then you need to create your own type_printer, next create your own field type to replace bool and specialize field printer for this type as your new type_printer (check out enum_binding example in examples folder). But probably first or second option is more suitable for you cause they are easier. If you want to go this (third) way I can put more details on it

[pavel-cpp]

If you already have database you can skip calling sync_schema

Thank you, that's great solution

boolean type is not built-in in SQLite

That is interesting because this is output from official sqlite terminal

CREATE TABLE Statistics
(
    ID           INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
    PacketsCount UNSIGNED BIG INT,
    FilesCount   UNSIGNED BIG INT,
    ErrorsCount  UNSIGNED BIG INT,
    DBConnected  BOOLEAN                                  NOT NULL DEFAULT 0
);

But UNSIGNED BIG INT and BOOLEAN is aliases of INTEGER

You can reproduce this using the following commands:

sqlite> .open your_db.sqlite3
sqlite> .output test_dump.sql
sqlite> .dump test_dump
sqlite> .quit

What do you think about the possible addition of these aliases? After all, they are also part of the standard

[fnc12]

@pavel-cpp if 'official sqlite terminal' is sqlite3 CLI utility than it is expected cause you can write any type like this:

CREATE TABLE ototo (
    id INTEGER PRIMARY KEY NOT NULL,
    value ABRAKADABRA);

[pavel-cpp]

Hmm... This is interesting, but it is simply a matter of how the standard defines these types.

Additionally, I have just noticed that the BOOLEAN type is an alias for NUMERIC, but NUMERIC is not supported in SQLite_ORM. While this may be understandable, given that even the sqlite3 developers have stated that it behaves similarly to INTEGER.

[fnc12]

Looks like we got nothing to commit within this issue. I'll convert it to discussion if you don't mind