Direct schema update via API

[eugenefomenko]

First of all, thank you for such a powerful tool!

I am excited to use sqlynx to provide autocomplete features within our browser-based code editor.
However, I've encountered some limitations, particularly when it comes to efficiently handling databases with a large number of tables. Currently, the tool requires an external script containing SQL instructions (e.g., CREATE TABLE) for accurate hints.

The issue arises in terms of performance, especially with databases containing thousands of tables. Parsing this script for schema information impacts performance by freezing the page for several seconds.

It would be nice to have additional API methods that allow direct updates to the completion index with new identifiers. Ideally, these changes could look like:

interface Schema {
    tables: {
        tableName: string;
        columns: string[];
    }[];
}

getSchema(): Schema
addTable(tableName: string, columns: string[]): Schema
removeTable(tableName: string): Schema

With packing data into corresponding flatbuffers structure.

Here's a bit of context regarding our usage scenarios:

• During document initialization, we get analyzedScript.tableReferences and analyzedScript.columnReferences, searching for matches within our internal storage and updating the schema.
• Similarly, while users type text, we continuously check for new table and column identifiers and update the schema.
• When executing a DROP TABLE statement, we remove the table from the schema.

Implementing these changes would significantly enhance performance, especially in scenarios with massive database schemas.

Thank you for considering these changes!

[ankoh]

Fully agree, we need this!
And the proposal makes sense, although I'm favoring a variant where we'd rather pass multiple Schemas to the analyzer instead of maintaining a single one (it wouldn't be a single schema that we get viagetSchema and then modify).
Every single external database would be loaded as Schema descriptor into Wasm and would be considered, for example for name resolution.
We could then lazy load individual databases without rebuilding name indexes all them time.

I'll sketch this soon.

[ankoh]

Small update here: I added support for catalogs and the dynamic registration of schemas.

A first example of the JS api can be found here: https://github.com/ankoh/sqlynx/blob/b8e51e153b11a326a4de32df435b6b48fbb8cd70/packages/sqlynx-wasm/test/catalog.test.ts#L21-L71

Maintaining these descriptor pools is cheap and resolving names is fast against both, a large number of pools as well as pools with many schemas and tables.
I will add dedicated benchmarks for both soon as well as examples for lazy-loading Salesforce Data Cloud schemas.

[ankoh]

There were a variety of changes necessary to integrate this properly:

• SQLynxScript.analyze() no longer receives an external script as parameter. Instead, the catalog is specified when creating the script. This aligns better with the copy-on-write nature of the repeated script processing.
• I realized that returning ids of name dictionaries in the analyzer output leads to a highly confusing bookkeeping, especially if you're not consuming the AST directly and/or have to deal a multi-versioned catalog. The analyzer output now includes the raw strings, which means that the user only has to worry about the analyzed script version when resolving ast nodes or statements.
• I initially built this around a "traditional" schema search path, as you would find it in a database system. But this turned out to be quite a misfit. Synchronizing a search path between js and wasm is quite annoying and inconvenient. Catalog entries are organized using ranks. Schema precedences as with a path can be achieved by selecting ranks appropriately. A colliding name will be resolved in such a way that the smaller rank wins.

[ankoh]

Additional note:

Actions on a descriptor pool are currently only additive. In fact, the api just takes a Schema Descriptor FlatBuffer, copies it into wasm memory and updates all necessary indexes for the catalog. This makes lazy loading of schema entries very fast. For example we now use a suffix btree that will directly reference the string data in the FlatBuffer.

The downside is that I'm currently not supporting deletion. Dropping an entire descriptor pool is supported but deleting partial data of individual schema descriptors is currently not. We can add this later, should this really become relevant.

[eugenefomenko]

https://github.com/ankoh/sqlynx/blob/b8e51e153b11a326a4de32df435b6b48fbb8cd70/packages/sqlynx-wasm/test/catalog.test.ts#L23

@ankoh Could you please explain what is the purpose of the second rank parameter?

[ankoh]

It's a slightly different way to model the traditional schema search path.

Databases need a way to give certain schemas precedence over others for disambiguation of names.
In Hyper, this is done via a schema search path, which is just a sequence in which schemas are checked for an identifier.
This works, but is a bit annoying for a JS api to maintain since you'd have to reason about a schema sequence when interoperating with wasm.
So instead we're using a rank parameter here that dictates the order in which names are considered. (Smaller rank wins)

You can model a traditional search path by just inserting catalog entries in with incrementing/decrementing ranks.

[eugenefomenko]

Hello André,

I wanted to thank you for making those changes so quickly. They seem to really solve the problem I faced, just as I hoped. I apologize for the delayed response. I've just started testing the changes.

I'll provide feedback once I've completed the integration and testing.

[ankoh]

Hey Eugene, no worries, I also haven't invested any further time to package this up as standalone npm package. Only for prioritisation reasons though. If you run into troubles, want to discuss something or need this packaged up on npm let me know.

[ankoh]

Also, I'm very interested in scaling & performance discussions. If you encounter anything let me know and we can work on regression testing your cases.