Support for 'true' relational schema

[Jogai]

This seems like a great project. The readme states "Fliox store entities in JSON columns". I was wondering if it would be easy to change the project to (optionally) support storing relational data in the traditional relational way. I have a prototype which I want to move to a more decent architecture, but for me a real relational storage would work best. If it's doable and you're interested I can have a go at it myself if you gave some pointers where it would fit in this project?

[friflo]

@Jogai Basically this is possible. I would not declare this task as trivial.

Available right now is: Each SQL Provider create generated columns based on the schema.
They can be used in SELECT queries in the traditional way. Their limitation is - they are read-only.

I guess you expect also writing to columns.
Doing this requires to extend the EntityContainer implementations in the SQL Providers. E.g. MySQLContainer
They are located at:
json/Fliox.Hub.MySQL
json/Fliox.Hub.PostgreSQL
json/Fliox.Hub.SQLServer
json/Fliox.Hub.SQLite

Each provider implements a set of methods declared at:

Friflo.Json.Fliox/Json/Fliox.Hub/Host/Base/EntityContainer.cs

Lines 81 to 93 in fb1c156

	#region - abstract container methods
	/// <summary>Create the entities specified in the given <paramref name="command"/></summary>
	public abstract Task<CreateEntitiesResult> CreateEntitiesAsync (CreateEntities command, SyncContext syncContext);
	/// <summary>Upsert the entities specified in the given <paramref name="command"/></summary>
	public abstract Task<UpsertEntitiesResult> UpsertEntitiesAsync (UpsertEntities command, SyncContext syncContext);
	/// <summary>Read entities by id with the ids passed in the given <paramref name="command"/></summary>
	public abstract Task<ReadEntitiesResult> ReadEntitiesAsync (ReadEntities command, SyncContext syncContext);
	/// <summary>Delete entities by id with the ids passed in the given <paramref name="command"/></summary>
	public abstract Task<DeleteEntitiesResult> DeleteEntitiesAsync (DeleteEntities command, SyncContext syncContext);
	/// <summary>Query entities using the filter in the given <paramref name="command"/></summary>
	public abstract Task<QueryEntitiesResult> QueryEntitiesAsync (QueryEntities command, SyncContext syncContext);
	/// <summary>Performs an aggregation specified in the given <paramref name="command"/></summary>
	public abstract Task<AggregateEntitiesResult> AggregateEntitiesAsync(AggregateEntities command, SyncContext syncContext);

All these methods deal with raw JSON.
For INSERT & UPDATE a Utf8JsonParser need to be used to convert JSON properties to column values.
For SELECT a Utf8JsonWriter need to be used to create a single JSON object from multiple columns values.
If supporting entity models with non-scalar members this conversion get more complicated. E.g.

{
    "id": 1,
    "list": [1,2,3],
    "object": { "key": "value" }
}

An option would be to disallow entity models with non scalar members.

Both conversions add CPU/memory cost and need to be well optimized.
That is basically the reason using JSON columns at the moment. Reading rows is very efficient.
Only a single value - the JSON column json_data - need to be read.

Also the query filter conversion - Operation to WHERE - for each provider need to be adjusted in all Traverse() methods.
This method is in the *Extension class of each provider.
I guess only the case FIELD: need to be adjusted.

In summary:

• The good part: Doing this require to change only SQL Provider code without touching anything else.
• I guess I will give a one shot to implement this. But you can also.

[friflo]

Start implementation beginning with commit
SQL Provider - column per member: add ObjectInfo

Subsequent commits are prefixed with: SQL Provider - column per member:

[friflo]

Finished implementation for 'member columns' (traditional relational) for MySQL & MariaDB: ad9ae30

not publish as nuget package right now.
Implementation was more difficult than expected.

[Jogai]

Oh wow, that's awesome

[Jogai]

Btw, why don't you utilize pull requests/branching? It seems it's something you would like seeing as you're prefixing your commits with the name of a feature

[friflo]

Having a relational table schema was a feature request I expected. Even by myself 😀
To enable this:

   new MySQLDatabase(db, connection, schema) { TableType = TableType.Relational };

"Btw, why don't you utilize pull requests/branching? It seems it's something you would like seeing as you're prefixing your commits with the name of a feature"

I'm a Git noob. Branching would be okay I guess. Pull requests are *ss 😠
I like linear logs - easy to understand. Maybe I try to use a branch in future.

[Jogai]

I like linear logs too. Rebase & merge handles that beautifully.

[friflo]

Published a new set of nuget packages providing the feature for all SQL database providers described in the initial post.
More info at: notes for version: v0.47.0