Indexes and foreign keys

[clouddra]

Hi,

Thanks for creating this library! It is intuitive and easy to use.
Just want to find out if there are any plans to add support for foreign keys and indexing?

[fnc12]

@clouddra hi. Thanks for the issue. I'm very pleased this code is useful.
Yes, there are plans to add support for foreign keys and indexes. Could you please help me: tell me how do you expect foreign keys and indexes to use (I mean are there any expectations about lib interface for this features). And please describe your use cases where you use FOREIGN KEY and INDEX.

1. My plans about foreign key - instead of
   make_column("artist_id", &Song::artistId)
   we'll have to write
   make_column("artist_id", &Song::artistId, foreign_key(&Artist::id)).

2. Plans about indexes are more abstract - right now I'm trying to understand how to do better: specify indexes statically (insert into make_storage a function make_index and create index in sync_schema if it doesn't exist just like table) or allow devs add indexes dynamically. What's your point? Thanks

[clouddra]

Thanks for the quick response.

1. That looks good
2. My use case will be more of the first scenario where indexes are defined statically. Theoretically I think that also allows devs to add index dynamically if I am not wrong. (devs can close, reopen the db and sync_schema)

[fnc12]

@clouddra now I'm thinking about adding foreign key feature to the lib. Firstly if schema has at least one foreign key the storage will call PRAGMA foreign_keys = ON after every sqlite3_open. It's ok. Second is sync_schema. Assume we call sync_schema when database doesn't exist at all. sync_schema will create every table in the order specified in make_storage call. It means during table creation any table that has column or table references to must be already created. This means that tables must be provided in order with increasing dependencies. Otherwise error occurs.
Another case is tables with cross-references. For example: book with category_id and category with previous_inserted_book_id. If database doesn't exist than this kind of schema cannot be created without ALTER TABLE command.
So there are two ways of creating FOREIGN KEY support in sqlite_orm:

1. easy (for lib contributors). Tables order in make_storage matters - if it is incorrect std::runtime_error will be thrown. Cross-references are not supported at all.
2. hard. Order doesn't matter at all. Cross-references are fully supported. sync_schema creates a dependency graph before creating table (if any of them do not exist) and creates tables without references and then alters them with foreign key tables.

What do you think? Which way do you expect as a lib user? I think I'm gonna write first way firstly and test it. Later I'll improve it to the second one.

[clouddra]

my use case would be closer to 1. I am mainly using the indexes as to optimize a readonly sqlite db.

[fnc12]

@clouddra thanks. I'm starting to work on foreign keys

[fnc12]

@clouddra please check cda7f36 . I've added foreign key support. It is raw right now - it doesn't support two-columned keys and reverse foreign keys (when table A has fk to table B and reverse). And tables must be defined in make_storage in order of foreign keys usage - first is a parent table, next is a child table. You can see an example here.
Limitations described below will be erased in a future commits. Sorry for late response - I was busy with JOIN.

[fnc12]

Order importance is eliminated in 927a636

[fnc12]

Please check out indexes support in a0bb1e8.