SQLite table schema

[linonetwo]

I see in #7915 @Jermolene , we have following long table

		-- Tiddlers also have unordered lists of fields, each of which has a name and associated value
		CREATE TABLE IF NOT EXISTS fields (
			tiddler_id INTEGER,
			field_name TEXT,
			field_value TEXT,
			FOREIGN KEY (tiddler_id) REFERENCES tiddlers(tiddler_id) ON UPDATE CASCADE ON DELETE CASCADE,
			UNIQUE (tiddler_id, field_name)
		)

Which is useful when we can compile filter expression to sql.

And I'm storing tiddlers like this https://github.com/tiddly-gittly/TidGi-Mobile/blob/a1154caddb498e7244b599826bc001b860d2b560/src/services/SQLiteService/orm/index.ts#L4

@Entity('tiddlers')
export class TiddlerSQLModel {
  @PrimaryColumn('text')
  title!: string;

  @Column('text')
  text?: string | null;

  @Column('text')
  fields?: string;
}

Which is fast for the initial loading of a wiki, and it was my primary goal to achieve on the mobile platform.

I'm thinking which one is better. Maybe we can use two tables, one for fast read, one for fast filter execution? And sync data between two tables?

[Jermolene]

Hi @linonetwo the work I did last year on SQLite in the browser via WASM uses a schema very similar to your example from TiddlyGit:

TiddlyWiki5/plugins/tiddlywiki/sqlite3store/sql-functions.js

Lines 54 to 60 in 6983564

	CREATE TABLE tiddlers (
	title TEXT NOT NULL ${COLLATION_CLAUSE},
	plugintitle TEXT NOT NULL, -- Empty string for tiddlers that are not part of a plugin
	meta TEXT NOT NULL,
	text TEXT NOT NULL,
	PRIMARY KEY(title,plugintitle)
	);

The main disadvantage of this approach is that, as you note, having the field values in a JSON blob is not conducive to performing filter operations on field values within filters. My goals include being able to compile filters into SQL queries that operate entirely within the database.

If you're not familiar with SQLite, you may find these short papers interesting:

• "35% Faster Than The Filesystem" - https://sqlite.org/fasterthanfs.html
• "SQLite As An Application File Format" - https://sqlite.org/appfileformat.html
• "Architecture of SQLite" - https://sqlite.org/arch.html

[linonetwo]

Are you going to use https://github.com/WiseLibs/better-sqlite3/blob/master/docs/api.md#tablename-definition---this ?

I can have a try in https://github.com/tiddly-gittly/TidGi-Mobile 's SQlite store, to see which is better

1. use two table, tiddlers table and fields table
2. use fields table, and a virtual tiddlers table

[Jermolene]

Hi @linonetwo I do not think that virtual tables will be required for this implementation. I was suggesting that they could be used by others to provide a simplified schema for apps that don't want to have to navigate how fields are stored. In practice, though, other apps bypassing the API and hitting the database directly would not be a recommended design pattern.

[Jermolene]

Another way of saying this is to acknowledge that the field-based schema is complex. But the solution to that complexity is to use the JS API or the HTTP API to interact with the database.

[linonetwo]

Sure, I will wrap sqlite behind standard $tw APIs. Here I'm mostly considering the speed, because my wiki has 30k+ tiddlers and many custom fields. So my discussion was mostly about which approach can give the best speed and adapt to every scenario.

[Jermolene]

Hi @linonetwo I don't think the idea of a hybrid schema is appealing. Working with denormalised, duplicated data stores is error prone and brittle. We need a well defined schema that we can reason about robustly. So we're stuck with the fields-based schema because of the need to be able to execute filter logic within SQL queries. I think we need more experimentation and evaluation before we try to prematurely optimise the schema.

[linonetwo]

We might also need a indexer table, to store result of link-indexer.

There is bug report tiddly-gittly/TidGi-Mobile#3 that says backlink[] not working when using lazy-loading on mobile. This kind of filter operator (backlink[]) can't directly run on the fields table. @Jermolene

[Jermolene]

Hi @linonetwo at this point in #7915 I am just focussing on making the SQLite-based wiki store functional enough to support the requirements on the server.

I think that the mechanism behind backlinks would always have to be JS-based, because it relies on parsing, which I am not planning to attempt in SQL.

[linonetwo]

Okay, but if I want this, hope I can implement it in a plugin way. We are not sure about the API of SQL filter framework, but I think it is possible.

[linonetwo]

@Jermolene Can there be a indexer table, to cache the result of each indexer? We can populate the table on server side, and client don't need to do this again. You seem to mention this before in #7302 (comment)

Do you have idea on the schema of this cache?

[Jermolene]

Hi @linonetwo I think where SQLite really benefits us in terms of caching is that the ability to easily query for a list of changes since a given point allows us to do much more fine grained cache invalidation. It certainly would make it attractive to store caches within the database, regardless of how the data is generated.