Use CREATE VIRTUAL TABLE rather than select crsql_as_crr

[tantaman]

We currently upgrade tables to crrs by:

1. Creating a table as normal

CREATE TABLE my_table (
  id primary key integer,
  field1,
  field2,
  ...
);

2. running select crsql_as_crr

SELECT crsql_as_crr('my_table');

While working on the libsql integration (tursodatabase/libsql#147) @asg017 brought up a good point that we can use the create virtual table syntax.

Allowing us to do:

CREATE VIRTUAL TABLE foo USING CausalLengthSet(
  id PRIMARY KEY,
  views COUNTER,
  content PERITEXT,
  owner_id LWW INTEGER
);

The problems with this approach are:

1. A virtual table name foo will be created
2. We can't alter virtual table definitions
3. If we force the user to read and write from the virtual table, reads suffer a 4x performance hit (@ivertom even found this to be the case for vtabs that are no-ops and just forward reads to a real table)

We want to somehow achieve the best of both worlds.

One where the user can use the CREATE VIRTUAL TABLE mechanism but still read and write directly to/from the real table and where we can still support alter table commands.

Proposal

My idea for a "middle path" is this:

Users use the CREATE VIRTUAL but suffix their table name with _crr:

CREATE VIRTUAL TABLE foo_crr USING CausalLengthSet(
  ...
)

Under the hood this will:

1. Create table foo if it does not exist, with the desired schema
2. Upgrade foo to a crr if it does exist
3. Create a dummy virtual table foo_crr that isn't used for anything.
4. Create the clock tables as shadow tables of the vtab.

Unfortunately this does generate clutter (a random foo_crr table). The benefits though:

• Good syntax and maybe even ability to fold in fractional indexing
• Updating schema via CREATE rather than SELECT
• Ability to drop a crr (and all related tables) by dropping the virtual table
• Better protection of clock tables by making them shadow tables

Supporting Schema Modification

In addition to the vtab clutter, schema modification isn't supported against vtabs. @asg017 had another idea here: model schema modification as insert against the vtab.

Given our *_crr vtabs are doing nothing but being clutter, we can use them for schema modification.

Maybe the suffix should be _schema or _crr_schema?

To modify a schema with that via inserts would look like:

-- rename a column:
UPDATE foo_crr SET column_name = 'new_name' WHERE column_name = 'old_name';

-- add column(s)
INSERT INTO foo_crr (column_name, column_type, crdt_type) VALUES (
  'new_col',
  'INTEGER',
  'LWW'
);

-- drop a column
DELETE FROM foo_crr WHERE column_name = 'col_name';

[tantaman]

Indexing can be done out of band / against the "base tables"