I have curated an initial checklist of functionality I’m after. It looks like a lot of the functionality I need might not be available in SQL. Could you let me know if the following functionality is available in XTDBv2 in any shape - whether its SQL/XTQL/other.
I want to enforce some kind of strict type-schema on my dataset. Usually I would create a table with specific types - but there seems to be no CREATE. Is there a way to restrict changes to the schema after an initial INSERT with the datatypes I want. Does the concept of an empty ‘typed’ document exist?
How can I set additional primary keys?
How can I set foreign key and uniqueness constraints?
I have some non-changing ‘static data’. Is there a way to create a table without any temporal columns?
Is there a way to create VIEWs?
How can I create an auto incrementing column - e.g., suppose I wanted to auto-increment _id?
How can I set a DEFAULT for an unspecified value?
Do you support COALESCE?
Is there a way to say some columns cannot be NULL?
The pg execute result appears not to report the correct number of inserts, updates, deletes. This often serves a useful sanity/correctness check. Is there another way to get this information?
I’ll be interested in an official reply but here’s my take in the meantime:
XTDB has no concept of schema and allows arbitrary documents to be stored in any table; this is pretty typical of document-based databases.
There is only xt/id (or _id) which must be provided by the application.
There is no concept of foreign key and only xt/id needs to be unique.
If it doesn’t change, insert it just once into a table and query it as needed – essentially ignore the bitemporality aspects.
No views.
No auto-incrementing columns – the application must manage/provide xt/id.
No defaults – arbitrary documents may or may not have a specific field.
I just tried COALESCE in the XTDB online playground and it seems to support it.
No. See comments above about no schema an arbitrary documents.
Inserts, updates, and deletes are essentially asynchronous so the underlying submit-tx cannot tell you the result; I suspect you can get some information from the tx-log once a tx has completed but I don’t know for sure.
Thanks for chiming in Sean - your answers are essentially all correct as-of-now
We would like to add “gradual schema” features soon, like being able to enforce various constraints, and even support CREATE TABLE. (Temporal) foreign keys should be possible too. The general idea is that you should be able to introduce concrete and enforced notions of schema wherever/whenever it makes sense.
The pg execute result appears not to report the correct number of inserts, updates, deletes. This often serves a useful sanity/correctness check. Is there another way to get this information?
That’s right, it’s a limitation due to the asynchronous nature of transactions. It’s possible it could be partially supported for single-statement transactions. For now this sort of information can be calculated/retrieved by querying the xt.txs table and joining the _system_from column with the tables your UPDATEs may have affected. You can get the _id for the previously submitted transaction using SHOW LATEST SUBMITTED TRANSACTION (where it is shown as tx_id).
