twentyfourminutes / venflow Goto Github PK
View Code? Open in Web Editor NEWA brand new, fast and lightweight ORM, build for PostgreSQL.
Home Page: https://twentyfourminutes.github.io/Venflow/
License: Apache License 2.0
A brand new, fast and lightweight ORM, build for PostgreSQL.
Home Page: https://twentyfourminutes.github.io/Venflow/
License: Apache License 2.0
Hi, I am interested to see your benchmark result as this is targeting the PostgreSQL.
Can you take a look at here? It seems it is querying all records the data from the DB and do the post filter in the client.
You can use the code below.
connection.QueryAll<T>(top: 1);
For all ToList()
method, can you use the AsList()
method instead? Part of RepoDb.Extensions.
Originally posted by TwentyFourMinutes September 4, 2021
Currently we have the following methods: QueryInterpolatedSingle
, QueryInterpolatedBatch
and ExecuteInterpolatedAsync
, their purpose is to avoid SQL injection and to allow for easier parameterized SQL. However it is quite easy to mistakenly call their non-interpolated counter parts.
Omit Interpolated
in all of the above mentioned method names and instead add Raw
to their counter part methods e.g. QueryRawSingle
, QueryRawBatch
and ExecuteRawAsync
.
I would be happy about any thoughts or suggestions as well as a vote for either ๐ or ๐!
The new fluent API should be as fast and allocation free as possible, while still providing the same level of configuration capabilities as the old one. The most notable changes are:
MethodInfo
which the pipeline gets from SQL statement which will from now on be required to be defined as a lambda.The finalized fluent API for querying will look something along the lines of:
await database.Table.Query(table => $"select {table:*} from {table} where {table.Id} = {0}").TrackChanges().SingleAsync();
For now the current goal is just to allow parameters and configurations. Anyhow, this restricts us to limit the amount of flexibility which is not too big of a deal as this was a very uncommon use case anyway. Some of these restrictions are:
var query = database.Table.Query(() => "select * from table");
if(trackChanges)
query.TrackChanges();
var result = await query.SingleAsync();
Func
and similar attempts would considered be illegal:
await database.Table.Query(() => "select Id " + (includeName ? ", Name" : "") + " from table").SingleAsync();
The fluent API itself will no longer be based of interfaces and instance methods, but rather by empty structs and extension methods. The SingleAsync
method will receive the data through an ambient ThreadStatic
variable which holds data such as the DbCommand
and query materializer which should be used.
public class Table<T> where T : class, new()
{
public QueryBuilder<T> Query()
{
return default;
}
}
[StructLayout(LayoutKind.Sequential, Pack = 0)]
public readonly ref struct QueryBuilder<T> where T : class, new()
{
}
public static class QueryBuilderExtensions
{
public static QueryBuilder<T> TrackChanges<T>(this QueryBuilder<T> builder) where T : class, new()
{
return default;
}
public static QueryBuilder<T> TrackChanges<T>(this QueryBuilder<T> builder, bool trackChanges) where T : class, new()
{
return default;
}
public static T SingleAsync<T>(this QueryBuilder<T> builder) where T : class, new()
{
return default;
}
}
Currently we are having a global cache of all DatabaseConfigurations
which point to delegates continuing runtime generated backing field accessors which allows for the following usage:
public class Db : Database
{
public Table<Person> People { get; }
}
However, with AOT generated code we can not access fields which are generated by the compiler at runtime. Therefore we will need to fallback for either internal or public set
accessor.
While the afore mentioned restriction is somewhat inconvenient, we can at least improve performance by declaring the base Database
class as a generic type which will allow for a generic based cache instead of a type based dictionary cache. This generic cache will be instantiated on the first call of the Database
constructor.
The instantiater itself, below represented by a static lambda, will be generated AOT.
public class Db : Database<PostgreDb>
{
public Table<Person> People { get; set; }
}
public class Database<T> where T : Database<T>
{
private static Action<Database<T>> _instantiater;
protected Database()
{
if(_instantiater is null)
{
_instantiater = static db => (db as PostgreDb).People = new Table<Person>();
}
_instantiater.Invoke(this);
}
}
Some types are either defined as Foo
or Venflow.Foo
where both should rather be global::Venflow.Foo
.
Database timestampz column gets the value of '0001-12-31 23:42:30.000000 +00:00 BC' when inserting a new row, if the timestamp is not specified in entity. (Even if the database column has a default value of 'now()').
public class Interception {
public Guid Id { get; set; }
public DateTime gdh_inter { get; set; }
}
Interception i = new Interception();
await db.Interceptions.InsertAsync(i);
The only way to get rid of that bug for now is to specify defaut datetime in C# class :
public class Interception {
public Guid Id { get; set; }
public DateTime gdh_inter { get; set; } = DateTime.UtcNow;
}
Add an option to the Query fluent API which allows bypassing of the sql cache.
As there is no database creation logic and the ORM is primarily Postgres it would make sense to support native naming convention, this would making building on top of existing databases easier as well.
I've got a working solution here:
T0shik@ddb91f7
If you want this change I can create a pull request, if you'd like this to be implemented a particular way feel free to tell me I'll change it.
Nada
When I insert a new object un DB I would like to get all default values that I didn't specified to be stored in my C# class.
Currently CLR Enums are not getting converted to their underlying numeric type.
var row = await database.Table.QueryInterpolatedSingle(ยง"SELECT * FROM table WHERE clr_enum = {CLREnum.Value}").QueryAsync();
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:
However as mentioned there are downsides, which do hurt, but it is well worth the trade off, with the most notable being:
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
.
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;
}
The insert doesn't change much in its implementation other than reusing some parts of the inserter.
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);
}
}
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.
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();
}
}
}
The deletion of entities doesn't change much in its implementation either, other than no longer accessing the primary key through a delegate.
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();
}
Hey,
The contributing file has a scope of improvement and can be updated to a concise way after error removal.
I would like to contribute so please can you assign this issue to me.
The purpose of this handler should be to replace the old usage of the FormattableString
, which worked and required way less code, however the memory allocation was quite immense.
With the new custom string interpolation we got with .NET 6 and C# 10 we can reduce the amount allocation of heap allocations to exactly 0. Not only are we avoiding the boxing of value type parameters, but also the allocations of the FormattableString
object itself and the Expression Trees, which were required to allow for maintainable SQL.
As we enforce that a query through the fluent API has to be inline and the content of the lambda has to be an interpolated string, we can use a ThreadStatic
variable. Which acts as shared data pool between the query method and the SqlInterpolationHandler
.
The data we provide to the string handler will contain the ParameterIndecies
array which will contain all the locations of the parameters (0 based). This will be required once we add the following syntax table => "select * from {table}"
, another array will be required which contains the actual string that will replace the fake data. However this syntax will most likely be implemented in v2.1.0
.
Additionally we will be able to give the exact length of the string that the CommandBuilder
will produce as long as it won't contain any collection parameters.
[InterpolatedStringHandler]
public struct SqlInterpolationHandler
{
internal class ScopeData
{
[ThreadStatic]
internal static ScopeData Current;
internal StringBuilder CommandBuilder;
internal NpgsqlParameterCollection Parameters;
internal short[] ParameterIndecies;
}
private int _interpolationIndex;
private short _parameterIndex;
private short _absolutParameterIndex;
private short _nextParameterIndex;
private readonly StringBuilder _commandBuilder;
private readonly short[] _parameterIndecies;
private readonly NpgsqlParameterCollection _parameters;
public SqlInterpolationHandler(int literalLength, int formattedCount)
{
_interpolationIndex = 0;
_parameterIndex = 0;
_absolutParameterIndex = 0;
var current = ScopeData.Current;
_commandBuilder = current.CommandBuilder;
_parameterIndecies = current.ParameterIndecies;
_parameters = current.Parameters;
_nextParameterIndex = _parameterIndecies.Length > 0 ? _parameterIndecies[0] : (short)-1;
}
public void AppendLiteral(string value)
{
_commandBuilder.Append(value);
}
public void AppendFormatted<T>(T value)
{
BaseAppendFormatted(value);
}
public void AppendFormatted<T>(T value, string format) => AppendFormatted<T>(value);
private void BaseAppendFormatted<T>(T value)
{
if (_interpolationIndex++ != _nextParameterIndex)
{
return;
}
if (++_parameterIndex < _parameterIndecies.Length)
_nextParameterIndex = _parameterIndecies[_parameterIndex];
var parameterName = "@p" + _absolutParameterIndex++;
_parameters.Add(new NpgsqlParameter<T>(parameterName, value));
_commandBuilder.Append(parameterName);
}
}
Func<SqlInterpolationHandler> lambda = () => $"select * from table where id = {0}";
var command = new NpgsqlCommand();
command.Connection = conn;
var commandBuilder = new StringBuilder(34);
var scope = new SqlInterpolationHandler.ScopeData
{
ParameterIndecies = new short[ ] { 0 },
Parameters = command.Parameters,
CommandBuilder = commandBuilder
};
SqlInterpolationHandler.ScopeData.Current = scope;
query.Invoke();
command.CommandText = commandBuilder.ToString();
Any column should be able to be marked as database generated.
As the configuration possibilities for a single column stack up by now, we should consider adding a new fluent API just for columns.
This could look something like this:
entityBuilder.Property(x => x.Column).WithName("column");
With the option to mark it as database generated:
entityBuilder.Property(x => x.Column).HasDefault();
// Or
entityBuilder.Property(x => x.Column).IsGenerated();
This would allow for way easier debugging of the generated sources. However, this feature should be opt-in as CSharpier takes time to do its magic and we do not necessarily want to format it for every build. An attribute on the assembly such as [FormatVenflowSource]
would be a way to accomplish that. Anyhow we will need to wait until there is a proper way to pass the sources to CSharpier.
On this code line, I would highly recommend to use the QueryAll<T>(top: X)
instead.
Like the on one in our documentation:
using (var connection = new SqlConnection(connectionString).EnsureOpen())
{
var people = connection.QueryAll<Person>(top: 100);
// Do the stuffs for 'people' here
}
Why? By using the where
on the Query<T>()
would force writing the WHERE (Field IS NOT NULL);
in which slow down the process (along the way) even though they're returning the same result.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.