Feature request: serialization handlers for custom types/json

[nicktucci]

I have a complex object I'd like to use with CreateTable, Get, Update, etc SQLiteConnection methods. With certain fields like a list of int or small complex objects, I'd like to be able to mark them with an attribute so that they're serialized/deserialized into/out of flat JSON documents (text) within a column of the row.

I've tried a variety of approaches to meet this requirement without modifying this project's source but have been so far unsuccessful.

How difficult/feasible/within this project's scope would this functionality be?

[gilzoide]

Hi @nicktucci. You should be able to deserialize the JSON in your string property's setter method. Imagine this flow: SQLite-net uses your setter method when setting the string value, and in the setter you initialize the list of ints/small complex objects which live in another field or property (note, I haven't actually tested this):

[Table("MyTable")]
public class MyTable
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    public string SerializedData
    {
        get => _serializedData;
        set
        {
            _serializedData = value;
            _deserializedData = JsonUtility.FromJson<SmallComplexObject>(value);
        }
    }
    public SmallComplexObject DeserializedData => _deserializedData;

    private string _serializedData;
    private SmallComplexObject _deserializedData;
}       

You couild probably also lazy load the value to avoid deserialization if it's not needed, something like this (also haven't tested it):

[Table("MyTable")]
public class MyTable
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    public string SerializedData { get; set; }
    public SmallComplexObject DeserializedData => _deserializedData ??= JsonUtility.FromJson<SmallComplexObject>(SerializedData);

    private SmallComplexObject _deserializedData;
}

How difficult/feasible/within this project's scope would this functionality be?

To be honest, I don't really know. I'm not familiar with SQLite-net's internals.

But in general, I'd say you should avoid JSON data in SQL databases, unless you already have some existing JSON data that you want to store the way you got it (maybe from a HTTP request, idk). The "list of int" example you gave is ok to store serialized as string/blob, but "small complex objects" should likely be broken down in their properties, each one being a column in the table. You can then construct your object based on the data, for example in a readonly property.