Mechanism for differentiating between "by me" and "liked by me"

dogsheep / dogsheep-beta

Build a search index across content from multiple SQLite database tables and run faceted searches against it using Datasette

https://dogsheep.github.io/

188 stars 6 forks source link

Mechanism for differentiating between "by me" and "liked by me" #7

Closed simonw closed 4 years ago

simonw commented 4 years ago

Some of the content I'm indexing is by me - photos I've taken, tweets I wrote, commits, comments I posted.

Some of it is stuff that I've "liked" or "bookmarked" in some way - favourited tweets, Pocket articles, starred GitHub repos.

It woud be useful to be able to differentiate between the two.

simonw commented 4 years ago

Some opet questions about this:

Should I restrict to two exclusive categories here, or should I have a generic category mechanism that can be expanded to more than two?
Should an item be able to exist in more than one category? Do I want to be able to mark an indexed item as both by-me and liked-by-me for example? This question is more interesting if the number of categories is greater than two.
How should this be modeled? Single column, multiple boolean columns, JSON array, m2m against separate table?
What's the best way to make this performant

simonw commented 4 years ago

Relevant: https://charlesleifer.com/blog/a-tour-of-tagging-schemas-many-to-many-bitmaps-and-more/

SQLite supports bitwise operators Binary AND (&) and Binary OR (|) - I could try those. Not sure how they interact with indexes though.

simonw commented 4 years ago

Relevant post: https://sqlite.org/forum/forumpost/9f06fedaa5 - drh says:

Indexes are one-to-one. There is one entry in the index for each row in the table.

You are asking for an index that is many-to-one - multiple index entries for each table row.

A Full-Text Index is basically a many-to-one index. So if all of your array entries really are words, you could probably get this to work using a Full-Text Index.

simonw commented 4 years ago

I'm going to implement the first version of this as an indexed integer category column which has 1 for "about me" and 2 for "liked by me" - and space for other category numerals in the future, albeit a row can only belong to one category.

I'll think about a full tagging system separately.

simonw commented 4 years ago

I'll make category a foreign key to a categories table so Datasette can automatically show the name column.

simonw commented 4 years ago

Default categories:

1 = created
2 = saved