Array & Struct in single cell draft proposal

[ingted]

I did a full project code go-through and finished a very simple array/struct(tuple) experiment and the following insert(directly in server side since the parser hasn't yet support it in client side) and query (protobuf -> json deserialisation works but not able to show in grid, I could do some works in mgmt UI and show it if you want)

var d = new KbInsensitiveDictionary<fstring?>();

d.Add("poorguy", fstring.NewT("ttc", fstring.NewA(new fstring[] { fstring.NewS("OGC") })));

_core.Documents.InsertDocument(
    transactionReference.Transaction,
    "mySch",
    (new KbDocument(d)).Content
    );

Which shows an array as the value of key "ttc" in a tuple.

Some critical parts are like

//in public static IEnumerable<T> MapTo<T>(this Payloads.KbQueryDocumentListResult result) where T : new()
//originally => property.SetValue(obj, Convert.ChangeType(value, Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType));
property.SetValue(obj, value.toType(Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType));

Mod for nested data structure:

if (_core.Settings.UseCompression)
{
    using var input = new MemoryStream(Library.Compression.Deflate.Decompress(File.ReadAllBytes(filePath)));
    //return ProtoBuf.Serializer.Deserialize<T>(input);
    return PB.deserializeF<T>(input);
}
else
{
    using var file = File.OpenRead(filePath);
    //return ProtoBuf.Serializer.Deserialize<T>(file);
    return PB.deserializeF<T>(file);
}

And fstring type is something like:

[<ProtoBuf.ProtoContract>]
type fstring =
| S of string
| D of double
| A of fstring []
| T of string * fstring
    static member compareArrays (arr1: fstring array) (arr2: fstring array): int =
        Seq.zip arr1 arr2
        |> Seq.tryPick (fun (x, y) ->
            let res = compare x y
            if res = 0 then None else Some res)
        |> Option.defaultValue 0

    static member compareLength (arr1: fstring array) (arr2: fstring array): int =
        let a1l = if arr1 = null then 0 else arr1.Length
        let a2l = if arr2 = null then 0 else arr2.Length
        compare a1l a2l

    static member Compare (x: fstring, y: fstring): int =
        match box x, box y with
        | null, null -> 0
        | _, null -> 
            if x = S null then 0 else 1
        | null, _ ->
            if y = S null then 0 else -1
        | _ ->
            match (x, y) with
            | (D d1, D d2) -> Decimal.Compare(decimal d1, decimal d2) // 直接使用 decimal 的比较功能
            | (S s1, S s2) -> String.Compare(s1, s2, StringComparison.OrdinalIgnoreCase)
            | (A arr1, A arr2) ->
                let lenComp = fstring.compareLength arr1 arr2
                if lenComp <> 0 then lenComp
                else fstring.compareArrays arr1 arr2
            | (T (tag1, f1), T (tag2, f2)) ->
                let tagComp = String.Compare(tag1, tag2, StringComparison.OrdinalIgnoreCase)
                if tagComp <> 0 then tagComp
                else fstring.Compare (f1, f2)
            | (D _, _) -> -1 // 所有 D 与其他类型比较时的默认顺序
            | (_, D _) -> 1  // 所有其他类型与 D 比较时的默认顺序
            | (S _, (A _ | T _)) -> -1
            | ((A _ | T _), S _) -> 1
            | (A _, T _) -> -1
            | (T _, A _) -> 1

    interface IComparer<fstring> with
        override this.Compare(x: fstring, y: fstring): int =
            fstring.Compare(x, y)
    member this.s =
        match this with
        | S s -> s
        | _ -> failwith "Not fstring.S."
    member this.d =
        match this with
        | D d -> d
        | _ -> failwith "Not fstring.D."
    member this.db =
        match this with
        | D d -> d
        | _ -> failwith "Not fstring.D."
    member this.ToLowerInvariant () =
        match this with
        | S "" -> fstring.SNull
        | S s -> s.ToLowerInvariant() |> S
        | _ -> failwith "Not fstring.S."

    static member val CompareFunc : Func<fstring, fstring, bool> = 
        FuncConvert.FromFunc(fun (x: fstring) (y: fstring) -> fstring.Compare(x, y) = 0)

    static member aFromStringArr (sArr) =
        sArr
        |> Array.map S
        |> A
    static member fromStringArr (sArr) =
        sArr
        |> Array.map S
        
    static member val SEmpty        = S ""                          with get
    static member val AEmpty        = A [||]                        with get
    static member val SNull         = S null                        with get
    static member val ANull         = A null                        with get
    static member val Unassigned    = Unchecked.defaultof<fstring>  with get
    
    
    static member SIsNullOrEmpty (o:fstring) = if box o = null || o = fstring.SEmpty || o = fstring.SNull then true else false
    static member AIsNullOrEmpty (o:fstring) = if box o = null || o = fstring.AEmpty || o = fstring.ANull then true else false
    static member IsNull (o:fstring) = 
        if box o = null || o = fstring.ANull || o = fstring.SNull then true else false

open System.Runtime.CompilerServices

[<Extension>]
module FS =
    let mapper = new Dictionary<Type, fstring -> obj>()

    let _ =
        mapper.Add(
            typeof<string>, (fun (S s) -> box s)
        )
    let _ =
        mapper.Add(
            typeof<double>, (fun (D d) -> box d)
        )

    let _ =
        mapper.Add(
            typeof<fstring []>, (fun (A a) -> box a)
        )

    let _ =
        mapper.Add(
            typeof<string * fstring>, (fun (T (k, v)) -> box (KeyValuePair.Create(k, v)))
        )

    [<Extension>]
    let toType (this: fstring, t:Type) = mapper[t] this



[<Extension>]
module ExtensionsString =
    [<Extension>]
    let toF(str : string) = S str
[<Extension>]
module ExtensionsDecimal =
    [<Extension>]
    let toF(d : decimal) = D (double d)
[<Extension>]
module ExtensionsDouble =
    [<Extension>]
    let toF(d : double) = D d

[<Extension>]
module ExtensionsInt =
    [<Extension>]
    let toF(d : int) = D (double d)

    
module PB =
    open ProtoBuf.Meta
    open ProtoBuf.FSharp
    let pbModel = //lazy (
        printfn "???????????????????????????????????????????????????????????????"
        RuntimeTypeModel.Create("???")
        |> Serialiser.registerUnionIntoModel<fstring> 
        //

    let serializeFBase m (ms, o) =
        printfn "[serializeF] type: %s, %A" (o.GetType().Name) o
        Serialiser.serialise m ms o

    let deserializeFBase<'T> m ms =
        printfn "[deserializeF] 'T: %s" typeof<'T>.Name
        Serialiser.deserialise<'T> m ms

    let serializeF (ms, o) = serializeFBase pbModel (ms, o)

    let deserializeF<'T> ms = deserializeFBase<'T> pbModel ms

Yes, it requires some mods to make ProtoBuf working for nested dynamic struct/array...

of course we could implement these stuff in c#, I used the f# code just try to do it quickly and be able to show you...

[ingted]

I could also record a video to show it if you are interested in it ^^ If not, let's just ignore it. I found that the attempt to achieve it requires huge effort...

PS. I created the branch for reference here

[NTDLS]

This might be simple to insert via the client.Document.Store API but would be a rather large effort to support in the parser. That said, the one place that I think would be nearly impossible would be the condition parser and indexing. Maybe those columns wouldn’t be supported in conditions?

Would it not suffice to store json in a column and add support for manipulating JSON in in if those columns?

[ingted]

Asked ChatGPT to help me summarize the difference:

1. Structured vs. Unstructured Data

JSON: JSON is an unstructured data format. Although it can represent nested objects and arrays, it's stored as text or binary and requires parsing and transformation during queries. This introduces extra overhead, especially when dealing with large datasets or complex nested queries.

ARRAY/STRUCTS: These are natively supported structured data types in BigQuery. They represent complex, nested data structures directly, without the need for parsing, allowing for more efficient querying and enabling BigQuery's optimizer to handle them more effectively.

Key Difference: ARRAY/STRUCTS are native, eliminating the need for parsing, while JSON requires parsing and transformation, impacting performance.

2. Query Performance

JSON: Because JSON is unstructured, BigQuery must dynamically parse and extract information during queries. This can be inefficient, particularly with large datasets or deeply nested structures.

ARRAY/STRUCTS: These native types allow BigQuery to directly access and query the data without the overhead of parsing, resulting in significantly better performance.

Key Difference: ARRAY/STRUCTS offer faster query performance due to their native support, while JSON adds overhead due to parsing.

3. Query Syntax Usability

JSON: Queries on JSON data require functions like JSON_EXTRACT or JSON_VALUE to extract specific fields. This can become cumbersome, especially with deeply nested data, and may lead to error-prone and hard-to-maintain queries.

ARRAY/STRUCTS: Native support for these types means you can access fields using a dot notation (e.g., struct_field.sub_field), which is much simpler and more intuitive to write and maintain.

Key Difference: ARRAY/STRUCTS have simpler, more intuitive query syntax, while JSON queries require more complex functions.

4. Type Safety

JSON: Being a dynamically typed format, JSON can have inconsistencies in field types, which requires extra care when querying. For example, a field might sometimes contain a string and other times a number, leading to potential type errors.

ARRAY/STRUCTS: These are strongly typed, meaning BigQuery enforces consistent types for each field, reducing the risk of type-related errors.
Key Difference: ARRAY/STRUCTS offer better type safety, while JSON may introduce type inconsistencies that can lead to errors.

5. Storage and Compression Efficiency

JSON: Since JSON is stored as text, it tends to be less efficient in terms of storage, especially for large datasets with many nested structures. Additionally, BigQuery can't apply its advanced compression techniques as effectively to JSON fields.

ARRAY/STRUCTS: Being native types, they are stored more efficiently and benefit from BigQuery’s storage optimizations and compression techniques, resulting in reduced storage costs.
Key Difference: ARRAY/STRUCTS are more storage-efficient and compress better than JSON, which consumes more space.

[NTDLS]

Also, shoot me an email. I have questions.

[ingted]

Sent it!!

[ingted]

Hi @NTDLS,

This time I did another experiment to make the document type abstract from EngineCore (and so on most 60% class) with generic parameter.

I think we could extract the abstraction of the document and later extend it with array/struct... etc. So I did some modification _# 1 that the unit tests are able to apply to both non-generic and generic versions.

That means we could primarily keep develop the non-generic version and spend some time (not that much) to make the generic version complete _# 2. Once we trust the generic version that it should have no defect, we could bump to it?

_# 1 This time it is a pure C# implementation. (The type passed into EngineCore is fstring in APIService.cs implemented IStringable)

_# 2
I think I could handle the feature sync at beginning to make the generic version could keep the tempo with non-generic version. Every you added new features or code change, I would try to merge it and generic-lize them and keep the tests complete. Once the tests coverage is enough for both version, we could then decide if we want to support array and struct at that time with generic?

experiment_generic_data_type

PS. The code change to make so many classes be generic is very ugly (I know that), so it just a concept demo, I would guess if we really want to do that, we maybe need DI stuff like Ninject... to make code clean...

[ingted]

Client mod

[ingted]

Wow, it took five days for me to follow up the early commit of Katzebase (merged all-in-one), esp. the extracted parser! The parser is ultra cool and I know more about the parsing flow and methodology...

Will try to catch up with the latest commit and keep developing the unit cases!!!

PS. The generic version experiment is pushed. Basicunit tests passed.

[NTDLS]

Hahaha, yea. It’s all good. Take your time.

I'm working completely rewriting the schema intersection methods. Now every intersection function is parallel. I introduced a new thread pool and my test queries have gone from the high 800 ms to 10s of milliseconds.

It’s also much easier to read the code and I was able to remove a little more than 50% of the functions from the schema intersecting classes.

That work is being done in a separate branch and should not affect the unit tests once merged.