Implement Geopoly Interface

[nikitadol]

Is your feature request related to a problem? Please describe.
It would be great to be able to have a geopoly module which is described here -> https://www.sqlite.org/geopoly.html

Describe the solution you'd like
There are 2 options:

• Add only this module
• Allow defining custom modules (as a more universal solution)

[simolus3]

I agree that this is something worth having, but it's not a priority for me at the moment if there's no strong demand for it. Are you interested in contributing parts for this if I give you some pointers and help out a bit?

Allow defining custom modules (as a more universal solution)

You can add support for custom functions in the query builder by writing your own APIs on top of things like FunctionCallExpression - that works for geopoly as well.
To make use of these functions in drift files, you can register additional functions when configuring the builder.

But it's harder to generalize support for custom CREATE VIRTUAL TABLE modules, since they have no general syntax and we need to be able to figure out the actual schema statically.

[nikitadol]

Hi
I'm sorry for the delay. It's not something urgent. I decided to try to do it myself, but ran into some problems:

1. Such expressions are not parsed correctly:

blob_in_string: select typeof(X'0100000300000000000000000000803F000000000000003F0000803F');

Result:

  Selectable<String> blob_in_string() {
    return customSelect(
        'SELECT typeof(\'\'\'0100000300000000000000000000803F000000000000003F0000803F\') AS _c0',
        variables: [],
        readsFrom: {}).map((QueryRow row) => row.read<String>('_c0'));
  }

2. This code does not completely create the Table class and therefore in FunctionHandler I cannot get information that the table is virtual
   

   drift/drift_dev/lib/src/analysis/resolver/drift/sqlparser/mapping.dart

   Lines 52 to 57 in 37f120d

   	return Table(
   	name: table.schemaName,
   	isStrict: table.strict,
   	withoutRowId: table.withoutRowId,
   	resolvedColumns: columns,
   	);

3. About FunctionHandler: I need to implement inferReturnType so that the correct code is generated - this is ok, but there is inferArgumentType in which there is no element index (workaround) and it is also unclear what the result of this function affects

4. There is a difficult situation with the _shape field (as with other functions of this module too) - it accepts text and blob as input, but only returns blob. If I specify a text type for this column, then the code generator will expect a String from the user during insert operations, but at the same time, it will expect a String during a select operation, which is already an error, since the internal code will just call toString on Uint8List - is there any way to solve this without rewriting a lot of code?

[simolus3]

Thanks for starting to work on this!

Such expressions are not parsed correctly:

Ouch, fixed in 43b7f72.

This code does not completely create the Table class and therefore in FunctionHandler I cannot get information that the table is virtual

I wanted to avoid including the CREATE TABLE AST in drift's model, as it needs to be serialized and deserialized for efficient builds (granted, we can probably just format it to text and parse it again, but still). There's a metadata field on tables (virtualTableData) which I'd prefer to use for that, you can check the fts5-related lints and handlers as an example.

About FunctionHandler: I need to implement inferReturnType so that the correct code is generated - this is ok, but there is inferArgumentType in which there is no element index (workaround) and it is also unclear what the result of this function affects

Maybe we should move that workaround logic into the calling function and supply the index directly. It doesn't really make sense to infer arguments for foo(*) invocations either way.

There is a difficult situation with the _shape field (as with other functions of this module too) - it accepts text and blob as input, but only returns blob

Using DriftAny can be used as an escape hatch for the type system. Nothing substantially better comes to mind at the moment.