urQL/docs/roadmap.md

Roadmap

Features coming after the initial v0.4 alpha release, in no particular order, although what I consider some of the more important are clustered at the top.

Most of the commands and clauses in the list below already exist in the urQL grammar and are supported by the parser.

Prioritization depends on user feedback and bribery.

• A real Gall agent -- need to retrieve results generated by poke.

• Security -- (cf. Permissions document) the current permissions model proposal is incomplete, notably lacking per table/view permissions. Currently a foreign ship cannot alter the schema but it can discover the database schemas and modify data.

• Shrubbery and/or Clay implementation -- may be pretty trivial to do. Opens the possibility of multiple %obelisk "servers" per ship and probably makes cross server joins easy to implement.

• Views -- views are cached queries and can shadow user-defined tables.

• Additional system views -- likely breaking change on sys.tables view. You have been warned.

• DELETE -- Operates on user-defined tables, not on views; optional predicate. (If you skip the predicate you should be using TRUNCATE TABLE...maybe I'll make the predicate mandatory.)

• UPDATE -- Operates on user-defined tables, not on views; optional predicate.

• UPSERT -- INSERT that does not fail on duplicate key, but rather updates the row.

• ORDER BY ... -- order result set.

• Support cells and/or jammed nouns -- currently only aura-typed atoms supported.

• Set operators -- UNION, INTERSECT, EXCEPT, DIVIDED BY, DIVIDED BY WITH REMAINDER

• Foreign Keys -- enforce referential integrity and allow natural join even when names do not match.

• Scalar functions -- functions on one row of a table-set returning a noun. (A table-set is a user-defined table, view, result of a CTE, or join of any or all of the above.)

• GROUP BY...HAVING -- as in SQL.

• More JOINs -- JOIN ON <predicate>, CROSS JOIN, and 3 kinds of outer joins.

• Common Table Expressions (CTE) -- improved urQL composability and required by the urQL grammar for some predicate operations. (cf. SQL Server, I forget what this is called in the SQL specification)

• Aggregate functions -- functions on a column, depends on GROUP BY, e.g. COUNT(*).

• Stored procedures -- Parameterized queries, to be designed (TBD). Possibly urQL + inlined hoon...who knows.

• Triggers -- TBD. Kick off some other process inside or outside of %obelisk.

• Localization of date/time -- TBD. (cf. https://github.com/sigilante/l10n) Not sure this is really important, but I can be persuaded.

• Advanced aggregate features -- DISTINCT, like Grouping Sets, Rollup, Cube, GROUPING function. (cf. Feature T301 'Functional dependencies' from SQL 1999 specification). (Not to be confused with SELECT DISTINCT, which does not exist in %obelisk because every table-set is a proper set.)

• Pivoting -- similar to SQL.

• Windowing -- and windowing functions, similar to SQL.

• INSERT FROM...SELECT... -- INSERT data defined by a query.

• INSERT <columns> ... AS <name> <key columns> -- Skip CREATE TABLE and define new table in the INSERT command.

• MERGE -- a much maligned SQL command that could be better implemented in urQL/%obelisk. (cf. Merge document, which is incomplete)

• B+ trees -- a performance thing.

• Aura @uc -- Bitcoin address 0c1A1zP1eP5QGefi2DMPTfTL5SLmv7DivfNa

• INSERT aura validation -- probably as an option, +sane impact on performance unknown. Currently possible to mix-up auras.

• Support @ta and @tas auras -- would require +sane.

• %quiz -- property tests on database, namespace, table, insert, query, etc.

• remove SELECT TOP and BOTTOM -- TOP and BOTTOM are not idempotent, unless ORDER BY is complete ordering. Likely remove them from the grammar and the parser as determining complete ordering is probably not worthwhile.

• in-line table-set -- much infrastructure is in place for this, not sure it is a good idea (idea floated at Re-Assembly 2023)

• update urQL grammar -- SELECT should be after WHERE clause.

• alternate urQL grammar -- row tuple in square brackets and/or pith for from.

• SELECT <database>, SELECT <namespace>, SELECT <table> -- Return the noun of an entire database, namespace, table for export, backup, or any other purpose. It's a noun. Do with it what you will.

• bug: parse NOT predicate -- NOT in predicate documentation but does not parse correctly. Need to add %not unary-type.

• bug: IN type -- parse of IN (<list>) does not pass enough type info to do full type check at runtime