search

aergoio / litetree

SQLite with Branches
MIT License
1.62k stars 35 forks source link

Builtin multiindex primary key support #7

Closed KOLANICH closed 6 years ago

KOLANICH commented 6 years ago

Hi. I guess it may be not a right place to post a bug report, but I gonna post it here since sqlite project has some issues with its bug tracker and maillist.

The problem is that I need FAST lookup by 2 indexex and ranges. Currently I solve this problem by packing 2 numbers into one rowid by concatenating, I mean major_index << n_bits | minor_index and lookups like where oid == (major_index << n_bits | minor_index) and where oid >= major_index << n_bits and oid <= major_index << n_bits | minor_index_mask. This solves the problem pretty well, but ... this ruins schema since parts of rowid cannot be a part of schema, I cannot store this relation in DB and make DB engine enforce consistency. I mean that major_index is a foreign key referring a primary key in another table.

I wonder if it makes sense have this feature built into the db.

kroggen commented 6 years ago

Hi!

Maybe you can use a primary key on multiple columns.

But unfortunately this is not the right place for general SQLite questions. You can join the sqlite-users mailing list and send an e-mail to it.

If you have any problem specific to LiteTree, feel comfortable to report here.