Sqlite_orm: Composite keys are ok but can you make a foreign key referencing a composite key?

[juandent]

Hi,

i have a table with a composite key and i need to "connect" another table to it via references() but it does not seem to allow more than one parameter. Is this impossible?

Regards,

Juan

[fnc12]

looks like it is impossible right now. I'll add this soon

[fnc12]

let's not close it until it's done

[juandent]

Any chance you'll get around to doing this soon? Its important I believe.

[fnc12]

I'll do it in a week I believe. Is it ok for you?

[juandent]

yes!!

[juandent]

Hi!
Have you gotten around to working on:

composite key and i need to "connect" another table to it via references()

?

[fnc12]

Holy cr*p, I got sick and didn't write any line of code. Sorry. I'll get to work ASAP

[juandent]

Don't worry!!
Get better!

[juandent]

I did some changes in the schema and deleted all tables from DB. Now when I run the program, at the call to storage.sync_schema() I get a SQL logic error when attempting to add the third table from the last one. I am completely lost. Could I share the declarations and the schema with you in case you can shed some light?

[juandent]

This is my schema:

        static auto storage = make_storage("statements.sqlite",

            make_table("PERSONS"s,
                make_column("id",
                    &Person::m_id,
                    primary_key()),
                make_column("first_name",
                    &Person::m_first_name),
                make_column("last_name",
                    &Person::m_last_name),
                make_column("company_name",
                    &Person::m_company_name)),

            make_table("RESPONSIBLES"s,
                make_column("id",
                    &Responsible::m_id,
                    primary_key()),
                make_column("person",
                    &Responsible::m_person_fid),
                make_column("percentage",
                    &Responsible::m_percentage),
                foreign_key(&Responsible::m_person_fid).references(&Person::m_id)
            ),

            make_table("CATEGORIES"s,
                make_column("name",
                    &Category::m_name_id,
                    primary_key()),
                make_column("is_real",
                    &Category::m_real_expense_or_income)),

            make_table("CONCEPTS"s,
                make_column("id",
                    &Concept::m_concept_id,
                    primary_key()),
                make_column("category",
                    &Concept::m_category_name_fid),
                make_column("account_payment",
                    &Concept::m_account_payment_fid),
                make_column("always",
                    &Concept::m_always),
                make_column("is_regex",
                    &Concept::m_is_regex),
                foreign_key(&Concept::m_category_name_fid).references(&Category::m_name_id),
                foreign_key(&Concept::m_account_payment_fid).references(&Account::m_number_id)
            ),
            make_table("STATEMENTS"s,
                make_column("file_name",
                    &Statement::m_fileName),
                make_column("file_path",
                    &Statement::m_filePath),
                make_column("statement_date",
                    &Statement::m_statementDate),
                make_column("account",
                    &Statement::m_account_fid),
                primary_key(&Statement::m_account_fid, &Statement::m_statementDate),
                foreign_key(&Statement::m_account_fid).references(&Account::m_number_id)
            ),

            make_table("STATEMENTLINES"s,
                make_column("id",
                    &StatementLine::m_id,
                    primary_key(),
                    autoincrement()),
                make_column("belongs_to_account",
                    &StatementLine::m_belongs_to_account_fid),
                make_column("statement_date",
                    &StatementLine::m_statement_date),
                make_column("line_date",
                    &StatementLine::m_lineDate),
                make_column("concept",
                    &StatementLine::m_concept_fid),
                make_column("colones"s,
                    &StatementLine::m_amountInLocal),
                make_column("dolares"s,
                    &StatementLine::m_amountInDollars),
                make_column("category"s,
                    &StatementLine::m_category_fid),
                make_column("enabled",
                    &StatementLine::m_enabled),
                make_column("details",
                    &StatementLine::m_details),
                make_column("refers_to_account",
                    &StatementLine::m_refers_to_account_fid),
                foreign_key(&StatementLine::m_belongs_to_account_fid).references(&Account::m_number_id),
                foreign_key(&StatementLine::m_category_fid).references(&Category::m_name_id),
                foreign_key(&StatementLine::m_concept_fid).references(&Concept::m_concept_id),
                foreign_key(&StatementLine::m_refers_to_account_fid).references(&Account::m_number_id)
                //foreign_key(&StatementLine::m_account_fid, &StatementLine::m_statement_date).references(&Statement::m_account_fid, &Statement::m_statement_date))
            ),

            make_table("LINE_RESPONSABILITIES"s,
                make_column("statement_id",
                    &LineResponsibility::m_statement_fid),
                make_column("responsible_id",
                    &LineResponsibility::m_responsible_fid),
                foreign_key(&LineResponsibility::m_statement_fid).references(&StatementLine::m_id),
                foreign_key(&LineResponsibility::m_responsible_fid).references(&Responsible::m_id)),

                make_table("ACCOUNTS"s,
                    make_column("number",
                        &Account::m_number_id,
                        primary_key()),
                    make_column("owner",
                        &Account::m_owner_fid),
                    make_column("currency",
                        &Account::m_currency),
                    make_column("description",
                        &Account::m_description),
                    make_column("type",
                        &Account::m_type),
                    make_column("cuenta_cliente",
                        &Account::m_cuenta_cliente),
                    foreign_key(&Account::m_owner_fid).references(&Person::m_id))
                );
        if (flag == 0)
        {
            flag = 1;
            storage.sync_schema();
        }
        return storage;

[juandent]

And my structures are:

    struct Category
    {
        std::string                 m_name_id;
        bool                        m_real_expense_or_income;
    };

    struct Person                   // Leslie, Juan Jr, Juan Sr, Other
    {
        int m_id;
        std::string m_first_name;
        std::string m_last_name;
        std::string m_company_name;
    };

    struct Account
    {
        std::string m_number_id;
        int m_owner_fid;
        std::string m_cuenta_cliente;
        Coin        m_currency;
        std::string m_description;
        AccountType m_type;
    };

    struct Concept
    {
        PK std::string m_concept_id;
        std::shared_ptr<std::string> m_category_name_fid;
        std::shared_ptr<std::string> m_account_payment_fid;     // TODO
        bool m_always;      // always apply this mapping rather than just suggesting
        bool m_is_regex;    // 
    };

    struct Responsible
    {
        PK int m_id;
        int m_person_fid;
        double m_percentage;    // degree of responsibility
    };

    // N:M
    struct LineResponsibility
    {
        unsigned long m_statement_fid;
        int m_responsible_fid;
    };

    struct Statement
    {
        std::string             m_fileName;
        std::string             m_filePath;
        date::sys_days          m_statementDate;
        std::string     m_account_fid;
    };

    struct StatementLine
    {
        unsigned long   m_id;
        std::string     m_belongs_to_account_fid;
        date::sys_days              m_lineDate;
        std::string     m_concept_fid;
        Colones                     m_amountInLocal;
        Dolares                     m_amountInDollars;

        std::shared_ptr<std::string>    m_category_fid;
        bool                        m_enabled;
        std::string                 m_details;
        std::shared_ptr<std::string>  m_refers_to_account_fid;
        date::sys_days              m_statement_date;
    };

What can be going on?
The fields whose type are date::sys_days were already present in my previous schema - don't have a problem with them.

[juandent]

I fixed it by removing the autoincrement() function from the make_column for the primary key of STATEMENTLINES --- why??

[juandent]

The previous problems have been solved but I have a new problem with this query:

auto vec = storage.get_all<StatementLine>(where(c(&StatementLine::m_category_fid) == &Category::m_name_id));

Here m_category_fid is a std::shared_ptr while Category::m_name_id is a std::string.
It's throwing an exception and I assume it's because the column is a std::shared_ptr.
It shouldn't but...
Any ideas?
Thanks!!

[juandent]

Problem solved.....
Thanks anyway

[fnc12]

all problems solved or something is left?

[fnc12]

looks like you had a problem with writing foreign key to a column which is not defined yet. I think I can add a static_assert for this. I need to think about it

[juandent]

yet, all I did to fix it was to remove auto_increment()!

[juandent]

and besides, that does not cause any problem elsewhere where a foreign key to a column not yet defined, like in the following:

foreign_key(&LineResponsibility::m_statement_fid).references(&StatementLine::m_id),
foreign_key(&LineResponsibility::m_responsible_fid).references(&Responsible::m_id)),

Responsible is a class not defined before LineResponsibity...

In any case, is there a "correct" order when writing schemas in sqlite_orm?

[juandent]

also, tables are created in the inverse order in which they are defined isn't that right?

[juandent]

other than this questions, I have no more issues at this time

[juandent]

well one more thing: is there a way to not have to include the schema in a header file that needs to be #included by every place we need to do some data access? This bothers me because any small change makes me recompile everything creating a very coupled solution - how can this scale??

[fnc12]

1) change the order of autoincrement and primary_key
2) order of defining schema is the same as in sqlite3
3) storage is a templated class to you need to include everything to use it. There is only one option to not to compile everything on every change - to use extern template feature but it is too difficult to support it

[juandent]

ok, got it!
I will close this now. Have you had time to work in referencing a composite primary key?
Thanks for everything!!

[fnc12]

Please keep it open until foreign composite key is done or I forget about it. I'll add it in a few days

[juandent]

superb!

[fnc12]

@juandent please check out PR https://github.com/fnc12/sqlite_orm/pull/223 in dev branch. I've added foreign key to composite primary key and updated composite_key.cpp example