Add CRUD code generators

[TwentyFourMinutes]

Definition

Writing all of the generators will be a one if not the most time consuming and buggiest things in the beginning. However there are a lot more benefits than downsides, with the biggest ones being:

• Debuggable and inspectable materializer & inserters in a meaningful manner
• Optimizing by the compiler to the code, instead of purely relying on the JIT and the hand written IL
• Way more maintainable code as hand written IL is very error-prune and hard to maintain

However as mentioned there are downsides, which do hurt, but it is well worth the trade off, with the most notable being:

• No reading and writing of hidden fields and or accessory, this includes read-only properties, and all accessory other than public
• Not the most optimized materializer theoretically possible, however we need fewer code loaded to accomplish the same task.

Query

Design

As mentioned before the materializer itself will be no longer as optimized as theoretically possibly, this comes down to a simple problem. There is no consistent and easy way to predict which columns may be returned by an SQL query AOT, meaning before the query actually executed once.

This therefore mean that we have two parts which play together while reading a DbDataReader.

1. The actual detection which columns got returned.
   This will be done by a dictionary that gets compiled AOT containing the column names of each property as a key with the value being their absolute property index in the entity. On the first execution at runtime of this query these will be mapped to a new static array where the index will be the column index of the reader and the value being the property index.
2. The actual materialization which is getting reused by all queries with the same table, order of joins and other configuration options such as change tracking.

Proposal

Single entity:

public static Person PersonParser_1(DbDataReader reader, ushort[] cols)
{
    Person person = null;
    var columnCount = cols.Length;

    for (var index = 0; index < columnCount; index++)
    {
        switch (cols[index])
        {
            case 0:
                person = new Person();
                person.Id = reader.GetFieldValue<int>(index);
                break;
            case 1:
                person.Name = reader.GetFieldValue<string>(index);
                break;
            case 2:
                person.Content = reader.GetFieldValue<string>(index);
                break;
        }
    }

    return person;
}

Multiple one-to-one entities:

public Person PersonParser_2(DbDataReader reader, ushort[] cols) 
{
    Person person = null;
    int personId = default;
    Data lastData = null;

    var startColumnIndex = 0;
    var columnCount = cols.Length;

    while(await reader.ReadAsync())
    {
        for(var index = startColumnIndex; index < columnCount; index++)
        {
            switch(cols[index])
            {
                case 0:
                    person = new Person();
                    person.Id = personId = reader.GetFieldValue<int>(index);

                    startColumnIndex = 3;
                break;
                case 1:
                    person.Name = reader.GetFieldValue<string>(index);
                break;
                case 2:
                    person.Address = reader.GetFieldValue<string>(index);
                break;

                case 3:
                    lastData = new Data();
                    person.Data.Add(lastData);
                    lastData.Id = reader.GetFieldValue<int>(index);
                    lastData.PersonId = personId;
                break;
                case 4:
                    lastData.Content = reader.GetFieldValue<string>(index);
                break;
            }
        }
    }

    return person;
}

Insert

Design

The insert doesn't change much in its implementation other than reusing some parts of the inserter.

Proposal

Single entity:

public static void PersonInserter(NpgsqlCommand cmd, Person p) 
{
    var parameters = cmd.Parameters;

    parameters.Add(new NpgsqlParameter<string>("@p0", p.Name));
    parameters.Add(new NpgsqlParameter<string>("@p1", p.Content));

    cmd.CommandText = "INSERT INTO people (name, content) VALUES (@p0, @p1) RETURNING id";
}

public static async Task Inserter(Person p) 
{
    using var cmd = new NpgsqlCommand();

    PersonInserter(cmd, p);

    p.Id = (int)await cmd.ExecuteScalarAsync();
}

Multiple entities:

public static void PersonInserter(NpgsqlParameterCollection parameters, StringBuilder commandText, Person[] people) 
{
    commandText.Append("INSERT INTO people (name, content) VALUES ");

    var absoluteIndex = 0;

    for(int i = 0; i < people.Length; i++)
    { 
        var p = people[i];

        var name1 = "@p" + absoluteIndex++;
        var name2 = "@p" + absoluteIndex++;

        commandText.Append('(')
                     .Append(name1)
                     .Append(',').Append(' ')
                     .Append(name2)
                     .Append(')').Append(',').Append(' ');

        parameters.Add(new NpgsqlParameter<string>(name1, p.Name));
        parameters.Add(new NpgsqlParameter<string>(name2, p.Content));
    }

    commandText.Length -=2;
    commandText.Append(" RETURNING id");
}

public static async Task Inserter(Person[] p) 
{
    using var cmd = new NpgsqlCommand();
    var commandText = new StringBuilder();
    PersonInserter(cmd.Parameters, commandText, p);
    cmd.CommandText = commandText.ToString();

    await using var reader = await cmd.ExecuteReaderAsync(p.Length == 1 ? CommandBehavior.SingleRow : CommandBehavior.Default);

    var index = 0;

    while(await reader.ReadAsync())
    {
        p[index++].Id = reader.GetFieldValue<int>(0);
    }
}

Update

Design

We used to store an array of booleans with a fixed length of the total theoretically updatable columns, which was semi optimal as it required an array allocation and an iteration over the whole array even if only one column was updated. In Reflow this will completely change.

From now on changes will be stored in local numeric fields containing the changed columns. Additionally, the trackChanges field will be stored in there as well to reduce memory usage even further. Assuming there are four updateable columns there would be one byte field. The least significant bit would indicate if changes should be tracked, the leading four will be responsible for the other columns (from least significant to most significant). The remaining two will be unused. If there are more than 7 fields which are updateable we will change the field to the next smallest fitting numeric type e.g. ushort/uint/ulong. For now we will restrict the maximum amount of updatable columns to 63 (as one bit is required for the previous trackChanges field).

While building the SQL required to actually update the columns in the database we use an algorithm which actually only iterates over all set bits, which improves the performance even further. Additionally we no longer need to query the method which gets us the new value of the columns.

Proposal

public static void Update(Person p, StringBuilder builder, NpgsqlParameterCollection parameters, ref uint abolsuteIndex) 
{
    if(p is not PersonProxy proxy)
        return;

    proxy.GetSectionChanges(0, out var section);

    while(section != 0)
    {
        string name = "@p" + abolsuteIndex++;

        switch((byte)(section & (byte)(~section + 1)))
        {
            case 1 << 0:
                builder.Append("id = ");

                parameters.Add(new NpgsqlParameter<int>(name, proxy.Id));
                break;
            case 1 << 1: 

                builder.Append("name = ");

                parameters.Add(new NpgsqlParameter<string>(name, proxy.Name));
                break;

            default:
                throw new Exception();
        }

        builder.Append(name)
               .Append(',')
               .Append(' ');

        section &= (byte)(section - 1);   
    }
}

public class Person
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public string Content { get; set; }
}

public class PersonProxy : Person
{
    private byte _hasChanges_1_8;

    public PersonProxy(bool trackChanges = false)
    {
        if(trackChanges)
            _hasChanges_1_8 |= 1;
    }
    
    public override int Id
    { 
        get 
        { 
            return base.Id;
        } 
        set
        { 
            base.Id = value; 
            
            if((byte)(_hasChanges_1_8 & 1) != 0)
            {
                _hasChanges_1_8 |= 1 << 1;
            }
        }
   }
   
   public override string Name
   { 
        get 
        { 
            return base.Name;
        } 
        set
        { 
            base.Name = value; 
            
            if((byte)(_hasChanges_1_8 & 1) != 0)
            {
                _hasChanges_1_8 |= 1 << 2;
            }
        }
   }
    
   public void GetSectionChanges(byte sectionIndex, out byte section)
   {
       switch(sectionIndex)
       {
           case 0:
               section = (byte)(_hasChanges_1_8 >> 1);
               break;
           default:
               throw new Exception();
       }
   }
}

Delete

Definition

The deletion of entities doesn't change much in its implementation either, other than no longer accessing the primary key through a delegate.

Proposal

Single entity:

public static Task<int> Delete(Person p) 
{
    using var cmd = new NpgsqlCommand();

    cmd.Parameters.Add(new NpgsqlParameter<int>("@p0", p.Id));

    cmd.CommandText = "DELETE FROM people WHERE id = @p0";

    return cmd.ExecuteNonQueryAsync();
}

Multiple entities:

public static Task<int> Delete(Person[] p) 
{
    if(p.Length == 0)
        return Task.FromResult(0);

    using var cmd = new NpgsqlCommand();

    var parameters = cmd.Parameters;

    var commandText = new StringBuilder();

    commandText.Append("DELETE FROM people WHERE id IN (");

    for(int i = 0; i < p.Length; i++)
    {
        var name = "@p" + i;
        parameters.Add(new NpgsqlParameter<int>(name, p[i].Id));
        commandText.Append(name)
                   .Append(',').Append(' ');
    }

    commandText.Length -= 2;
    commandText.Append(')');

    cmd.CommandText = commandText.ToString();

    return cmd.ExecuteNonQueryAsync();
}