From 8c5e9dae712227bef7cede73fac16bf3e48b19c6 Mon Sep 17 00:00:00 2001 From: vnugent Date: Thu, 13 Apr 2023 11:36:37 -0400 Subject: Database creation helpers w/ fluent api --- .../src/DatabaseBuilder/DbCreationException.cs | 44 ++++ .../src/DatabaseBuilder/Helpers/AbstractDb.cs | 104 ++++++++ .../src/DatabaseBuilder/Helpers/MsSqlDb.cs | 170 +++++++++++++ .../src/DatabaseBuilder/Helpers/MySqlDb.cs | 162 ++++++++++++ .../src/DatabaseBuilder/Helpers/SqlLiteDb.cs | 178 +++++++++++++ .../src/DatabaseBuilder/IDBCommandGenerator.cs | 44 ++++ .../src/DatabaseBuilder/IDbTable.cs | 39 +++ .../src/DbBuilder.cs | 221 ++++++++++++++++ .../src/IDbColumnBuilder.cs | 57 +++++ .../src/IDbContextBuilder.cs | 51 ++++ .../src/IDbTableBuilder.cs | 51 ++++ .../src/IDbTableDefinition.cs | 41 +++ .../src/SqlDbConnectionLoader.cs | 281 ++++++++++++++++++++- .../VNLib.Plugins.Extensions.Loading.Sql.csproj | 2 +- .../src/UserManager.cs | 10 +- .../src/VNLib.Plugins.Extensions.Loading.csproj | 14 +- .../src/VNLib.Plugins.Extensions.Validation.csproj | 2 +- 17 files changed, 1447 insertions(+), 24 deletions(-) create mode 100644 lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/DbCreationException.cs create mode 100644 lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/AbstractDb.cs create mode 100644 lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/MsSqlDb.cs create mode 100644 lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/MySqlDb.cs create mode 100644 lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/SqlLiteDb.cs create mode 100644 lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/IDBCommandGenerator.cs create mode 100644 lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/IDbTable.cs create mode 100644 lib/VNLib.Plugins.Extensions.Loading.Sql/src/DbBuilder.cs create mode 100644 lib/VNLib.Plugins.Extensions.Loading.Sql/src/IDbColumnBuilder.cs create mode 100644 lib/VNLib.Plugins.Extensions.Loading.Sql/src/IDbContextBuilder.cs create mode 100644 lib/VNLib.Plugins.Extensions.Loading.Sql/src/IDbTableBuilder.cs create mode 100644 lib/VNLib.Plugins.Extensions.Loading.Sql/src/IDbTableDefinition.cs diff --git a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/DbCreationException.cs b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/DbCreationException.cs new file mode 100644 index 0000000..c7825b0 --- /dev/null +++ b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/DbCreationException.cs @@ -0,0 +1,44 @@ +/* +* Copyright (c) 2023 Vaughn Nugent +* +* Library: VNLib +* Package: VNLib.Plugins.Extensions.Loading.Sql +* File: DbCreationException.cs +* +* DbCreationException.cs is part of VNLib.Plugins.Extensions.Loading.Sql which +* is part of the larger VNLib collection of libraries and utilities. +* +* VNLib.Plugins.Extensions.Loading.Sql is free software: you can redistribute it and/or modify +* it under the terms of the GNU Affero General Public License as +* published by the Free Software Foundation, either version 3 of the +* License, or (at your option) any later version. +* +* VNLib.Plugins.Extensions.Loading.Sql is distributed in the hope that it will be useful, +* but WITHOUT ANY WARRANTY; without even the implied warranty of +* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +* GNU Affero General Public License for more details. +* +* You should have received a copy of the GNU Affero General Public License +* along with this program. If not, see https://www.gnu.org/licenses/. +*/ + +using System; +using System.Data.Common; + +namespace VNLib.Plugins.Extensions.Loading.Sql.DatabaseBuilder +{ + class DbCreationException : DbException + { + public DbCreationException() + { } + + public DbCreationException(string? message) : base(message) + { } + + public DbCreationException(string? message, Exception? innerException) : base(message, innerException) + { } + + public DbCreationException(string? message, int errorCode) : base(message, errorCode) + { } + } +} diff --git a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/AbstractDb.cs b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/AbstractDb.cs new file mode 100644 index 0000000..e88820e --- /dev/null +++ b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/AbstractDb.cs @@ -0,0 +1,104 @@ +/* +* Copyright (c) 2023 Vaughn Nugent +* +* Library: VNLib +* Package: VNLib.Plugins.Extensions.Loading.Sql +* File: AbstractDb.cs +* +* AbstractDb.cs is part of VNLib.Plugins.Extensions.Loading.Sql which +* is part of the larger VNLib collection of libraries and utilities. +* +* VNLib.Plugins.Extensions.Loading.Sql is free software: you can redistribute it and/or modify +* it under the terms of the GNU Affero General Public License as +* published by the Free Software Foundation, either version 3 of the +* License, or (at your option) any later version. +* +* VNLib.Plugins.Extensions.Loading.Sql is distributed in the hope that it will be useful, +* but WITHOUT ANY WARRANTY; without even the implied warranty of +* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +* GNU Affero General Public License for more details. +* +* You should have received a copy of the GNU Affero General Public License +* along with this program. If not, see https://www.gnu.org/licenses/. +*/ + +using System; +using System.IO; +using System.Data; +using System.Text; +using System.Collections.Generic; + +namespace VNLib.Plugins.Extensions.Loading.Sql.DatabaseBuilder.Helpers +{ + internal abstract class AbstractDb : IDBCommandGenerator + { + private static readonly Dictionary TypeMap = new() + { + [typeof(byte)] = DbType.Byte, + [typeof(sbyte)] = DbType.SByte, + [typeof(short)] = DbType.Int16, + [typeof(ushort)] = DbType.UInt16, + [typeof(int)] = DbType.Int32, + [typeof(uint)] = DbType.UInt32, + [typeof(long)] = DbType.Int64, + [typeof(ulong)] = DbType.UInt64, + [typeof(float)] = DbType.Single, + [typeof(double)] = DbType.Double, + [typeof(decimal)] = DbType.Decimal, + [typeof(bool)] = DbType.Boolean, + [typeof(string)] = DbType.String, + [typeof(char)] = DbType.StringFixedLength, + [typeof(Guid)] = DbType.Guid, + [typeof(DateTime)] = DbType.DateTime, + [typeof(DateTimeOffset)] = DbType.DateTimeOffset, + + [typeof(byte[])] = DbType.Binary, + [typeof(byte?)] = DbType.Byte, + [typeof(sbyte?)] = DbType.SByte, + [typeof(short?)] = DbType.Int16, + [typeof(ushort?)] = DbType.UInt16, + [typeof(int?)] = DbType.Int32, + [typeof(uint?)] = DbType.UInt32, + [typeof(long?)] = DbType.Int64, + [typeof(ulong?)] = DbType.UInt64, + [typeof(float?)] = DbType.Single, + [typeof(double?)] = DbType.Double, + [typeof(decimal?)] = DbType.Decimal, + [typeof(bool?)] = DbType.Boolean, + [typeof(char?)] = DbType.StringFixedLength, + [typeof(Guid?)] = DbType.Guid, + [typeof(DateTime?)] = DbType.DateTime, + [typeof(DateTimeOffset?)] = DbType.DateTimeOffset, + [typeof(Stream)] = DbType.Binary + }; + + /// + /// Gets the database string type name from the given .NET runtime type + /// information. + /// + /// The type to resolve + /// The type string that is realtive to the given database backend + /// + public string GetTypeStringFromType(Type type) + { + if(!TypeMap.TryGetValue(type, out DbType dbType)) + { + throw new DbCreationException($"The type {type} is not a supporeted database type"); + } + + //Get the type string + return GetTypeStringFromDbType(dbType); + } + + + /// + /// Gets a string property value from a discovered + /// + /// The dbType discovered from the type according to the backing database + /// The parameter type as a string with an optional size variable + protected abstract string GetTypeStringFromDbType(DbType type); + + /// + public abstract void BuildCreateStatment(StringBuilder builder, DataTable table); + } +} diff --git a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/MsSqlDb.cs b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/MsSqlDb.cs new file mode 100644 index 0000000..4fb7f93 --- /dev/null +++ b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/MsSqlDb.cs @@ -0,0 +1,170 @@ +/* +* Copyright (c) 2023 Vaughn Nugent +* +* Library: VNLib +* Package: VNLib.Plugins.Extensions.Loading.Sql +* File: MsSqlDb.cs +* +* MsSqlDb.cs is part of VNLib.Plugins.Extensions.Loading.Sql which is part +* of the larger VNLib collection of libraries and utilities. +* +* VNLib.Plugins.Extensions.Loading.Sql is free software: you can redistribute it and/or modify +* it under the terms of the GNU Affero General Public License as +* published by the Free Software Foundation, either version 3 of the +* License, or (at your option) any later version. +* +* VNLib.Plugins.Extensions.Loading.Sql is distributed in the hope that it will be useful, +* but WITHOUT ANY WARRANTY; without even the implied warranty of +* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +* GNU Affero General Public License for more details. +* +* You should have received a copy of the GNU Affero General Public License +* along with this program. If not, see https://www.gnu.org/licenses/. +*/ + +using System; +using System.Data; +using System.Text; + +namespace VNLib.Plugins.Extensions.Loading.Sql.DatabaseBuilder.Helpers +{ + internal class MsSqlDb : AbstractDb + { + const int MAX_VARIABLE_SIZE = 8000; + + /// + public override void BuildCreateStatment(StringBuilder builder, DataTable table) + { + builder.AppendLine("IF OBJECT_ID(N'[dbo].[@tableName]', N'U') IS NULL"); + builder.AppendLine("CREATE TABLE [dbo].[@tableName] ("); + + //Add columns + foreach(DataColumn col in table.Columns) + { + //Get dbType string + string dbType; + + //Timestamps/rowversion must be handled specially for msSql + if (col.IsTimeStamp()) + { + dbType = "ROWVERSION"; + } + else + { + dbType = GetTypeStringFromType(col.DataType); + } + + builder.Append('[') + .Append(col.ColumnName) + .Append("] ") + .Append(dbType); + + //Set primary key contraint + if (col.IsPrimaryKey()) + { + builder.Append(" PRIMARY KEY"); + } + //Set unique constraint (only if not pk) + else if (col.Unique) + { + builder.Append(" UNIQUE"); + } + + //If the value is not null, we can specify the default value + if (!col.AllowDBNull) + { + if (!string.IsNullOrWhiteSpace(col.DefaultValue?.ToString())) + { + builder.Append(" DEFAULT "); + builder.Append(col.DefaultValue); + } + else + { + //Set not null + builder.Append(" NOT NULL"); + } + } + + //Set auto increment + if (col.AutoIncrement) + { + builder.Append(" IDENTITY(") + .Append(col.AutoIncrementSeed) + .Append(',') + .Append(col.AutoIncrementStep) + .Append(')'); + } + + //Trailing comma + builder.AppendLine(","); + + + //Set size if defined + if (col.MaxLength() > MAX_VARIABLE_SIZE) + { + builder.Replace("@size", "MAX"); + } + else if (col.MaxLength() > 0) + { + builder.Replace("@size", col.MaxLength().ToString()); + } + else + { + builder.Replace("(@size)", ""); + } + } + + int index = builder.Length; + while (builder[--index] != ',') + { } + + //Remove the trailing comma + builder.Remove(index, 1); + + //Close the create table command + builder.AppendLine(")"); + + //Replaced the table name variables + builder.Replace("@tableName", table.TableName); + } + + /// + protected override string GetTypeStringFromDbType(DbType type) + { + return type switch + { + DbType.AnsiString => "VARCHAR(@size)", + DbType.Binary => "VARBINARY(@size)", + DbType.Byte => "TINYINT", + DbType.Boolean => "BOOL", + DbType.Currency => "MONEY", + DbType.Date => "DATE", + DbType.DateTime => "DATETIME", + DbType.Decimal => "DECIMAL", + DbType.Double => "DOUBLE", + DbType.Guid => "VARCHAR(@size)", + DbType.Int16 => "SMALLINT", + DbType.Int32 => "INT", + DbType.Int64 => "BIGINT", + DbType.Object => throw new NotSupportedException("A .NET object type is not a supported MySql data-type"), + DbType.SByte => "TINYINT", + DbType.Single => "FLOAT", + //unicode string support + DbType.String => "NVARCHAR(@size)", + DbType.Time => "TIME", + DbType.UInt16 => "SMALLINT", + DbType.UInt32 => "INT", + DbType.UInt64 => "BIGINT", + DbType.VarNumeric => throw new NotSupportedException("Variable numeric value is not a supported MySql data-type"), + DbType.AnsiStringFixedLength => "TEXT(@size)", + //unicode text support + DbType.StringFixedLength => "NTEXT(@size)", + //Define custom xml schema variable + DbType.Xml => "XML(@xml_schema_collection)", + DbType.DateTime2 => "DATETIME2", + DbType.DateTimeOffset => "DATETIMEOFFSET", + _ => throw new NotSupportedException("The desired property data-type is not a supported MySql data-type"), + }; + } + } +} diff --git a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/MySqlDb.cs b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/MySqlDb.cs new file mode 100644 index 0000000..6f5552b --- /dev/null +++ b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/MySqlDb.cs @@ -0,0 +1,162 @@ +/* +* Copyright (c) 2023 Vaughn Nugent +* +* Library: VNLib +* Package: VNLib.Plugins.Extensions.Loading.Sql +* File: MySqlDb.cs +* +* MySqlDb.cs is part of VNLib.Plugins.Extensions.Loading.Sql which is part of the larger +* VNLib collection of libraries and utilities. +* +* VNLib.Plugins.Extensions.Loading.Sql is free software: you can redistribute it and/or modify +* it under the terms of the GNU Affero General Public License as +* published by the Free Software Foundation, either version 3 of the +* License, or (at your option) any later version. +* +* VNLib.Plugins.Extensions.Loading.Sql is distributed in the hope that it will be useful, +* but WITHOUT ANY WARRANTY; without even the implied warranty of +* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +* GNU Affero General Public License for more details. +* +* You should have received a copy of the GNU Affero General Public License +* along with this program. If not, see https://www.gnu.org/licenses/. +*/ + +using System; +using System.Data; +using System.Text; + +namespace VNLib.Plugins.Extensions.Loading.Sql.DatabaseBuilder.Helpers +{ + internal sealed class MySqlDb : AbstractDb + { + const int MAX_VARIABLE_SIZE = 8000; + + /// + public override void BuildCreateStatment(StringBuilder builder, DataTable table) + { + builder.AppendLine("CREATE TABLE IF NOT EXISTS `@tableName` ("); + + //Add columns + foreach (DataColumn col in table.Columns) + { + //Get dbType string + string dbType; + + //Timestamps/rowversion must be handled specially for MySql optimistic concurrency + if (col.IsTimeStamp()) + { + dbType = "TIMESTAMP"; + } + else + { + dbType = GetTypeStringFromType(col.DataType); + } + + builder.Append('`') + .Append(col.ColumnName) + .Append("` ") + .Append(dbType); + + //Set primary key contraint + if (col.IsPrimaryKey()) + { + builder.Append(" PRIMARY KEY"); + } + //Set unique constraint (only if not pk) + else if (col.Unique) + { + builder.Append(" UNIQUE"); + } + + //If the value is not null, we can specify the default value + if (!col.AllowDBNull) + { + if (!string.IsNullOrWhiteSpace(col.DefaultValue?.ToString())) + { + builder.Append(" DEFAULT "); + builder.Append(col.DefaultValue); + } + else + { + //Set not null + builder.Append(" NOT NULL"); + } + } + + //Set auto increment + if (col.AutoIncrement) + { + builder.Append(" AUTO_INCREMENT=") + .Append(col.AutoIncrementSeed); + } + + //Trailing comma + builder.AppendLine(","); + + //Set size if defined, we need to bypass column max length + if (col.MaxLength() > MAX_VARIABLE_SIZE) + { + builder.Replace("@size", "MAX"); + } + else if(col.MaxLength() > 0) + { + builder.Replace("@size", col.MaxLength().ToString()); + } + else + { + builder.Replace("(@size)", ""); + } + } + + int index = builder.Length; + while (builder[--index] != ',') + { } + + //Remove the trailing comma + builder.Remove(index, 1); + + //Close the create table command + builder.AppendLine(")"); + + //Replaced the table name variables + builder.Replace("@tableName", table.TableName); + } + + /// + protected override string GetTypeStringFromDbType(DbType type) + { + return type switch + { + DbType.AnsiString => "VARCHAR(@size)", + DbType.Binary => "VARBINARY(@size)", + DbType.Byte => "TINYINT", + DbType.Boolean => "BOOL", + DbType.Currency => "DECIMAL", + DbType.Date => "DATE", + DbType.DateTime => "DATETIME", + DbType.Decimal => "DECIMAL", + DbType.Double => "DOUBLE", + DbType.Guid => "VARCHAR(@size)", + DbType.Int16 => "SMALLINT", + DbType.Int32 => "INT", + DbType.Int64 => "BIGINT", + DbType.Object => throw new NotSupportedException("A .NET object type is not a supported MySql data-type"), + DbType.SByte => "TINYINT", + DbType.Single => "FLOAT", + DbType.String => "VARCHAR(@size)", + DbType.Time => "TIME", + DbType.UInt16 => "SMALLINT", + DbType.UInt32 => "INT", + DbType.UInt64 => "BIGINT", + DbType.VarNumeric => throw new NotSupportedException("Variable numeric value is not a supported MySql data-type"), + DbType.AnsiStringFixedLength => "TEXT(@size)", + DbType.StringFixedLength => "TEXT(@size)", + DbType.Xml => "VARCHAR(@size)", + DbType.DateTime2 => "DATETIME", + DbType.DateTimeOffset => throw new NotSupportedException("DateTimeOffset is not a supported MySql data-type"), + _ => throw new NotSupportedException("The desired property data-type is not a supported MySql data-type"), + }; + } + } +} diff --git a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/SqlLiteDb.cs b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/SqlLiteDb.cs new file mode 100644 index 0000000..88ddbcd --- /dev/null +++ b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/SqlLiteDb.cs @@ -0,0 +1,178 @@ +/* +* Copyright (c) 2023 Vaughn Nugent +* +* Library: VNLib +* Package: VNLib.Plugins.Extensions.Loading.Sql +* File: SqlLiteDb.cs +* +* SqlLiteDb.cs is part of VNLib.Plugins.Extensions.Loading.Sql which +* is part of the larger VNLib collection of libraries and utilities. +* +* VNLib.Plugins.Extensions.Loading.Sql is free software: you can redistribute it and/or modify +* it under the terms of the GNU Affero General Public License as +* published by the Free Software Foundation, either version 3 of the +* License, or (at your option) any later version. +* +* VNLib.Plugins.Extensions.Loading.Sql is distributed in the hope that it will be useful, +* but WITHOUT ANY WARRANTY; without even the implied warranty of +* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +* GNU Affero General Public License for more details. +* +* You should have received a copy of the GNU Affero General Public License +* along with this program. If not, see https://www.gnu.org/licenses/. +*/ + +using System; +using System.Data; +using System.Linq; +using System.Text; +using System.Collections.Generic; + +namespace VNLib.Plugins.Extensions.Loading.Sql.DatabaseBuilder.Helpers +{ + internal class SqlLiteDb : AbstractDb + { + public override void BuildCreateStatment(StringBuilder builder, DataTable table) + { + builder.AppendLine("CREATE TABLE IF NOT EXISTS @tableName ("); + + List uniqueCols = new(); + + //Add columns + foreach (DataColumn col in table.Columns) + { + //Get dbType string + string dbType; + + //Timestamps/rowversion must be handled specially for MySql optimistic concurrency + if (col.IsTimeStamp()) + { + dbType = "BINARY(8)"; + //We may also set the AllowNull property + col.AllowDBNull = true; + } + else + { + dbType = GetTypeStringFromType(col.DataType); + } + + builder.Append('[') + .Append(col.ColumnName) + .Append("] ") + .Append(dbType); + + //Set primary key contraint + if (col.IsPrimaryKey()) + { + builder.Append(" PRIMARY KEY"); + } + //Set unique constraint (only if not pk) + else if (col.Unique) + { + //Add the column to unique list for later + uniqueCols.Add(col); + } + + //If the value is not null, we can specify the default value + if (!col.AllowDBNull) + { + if (!string.IsNullOrWhiteSpace(col.DefaultValue?.ToString())) + { + builder.Append(" DEFAULT "); + builder.Append(col.DefaultValue); + } + else + { + //Set not null + builder.Append(" NOT NULL"); + } + } + + //Set auto increment + if (col.AutoIncrement) + { + builder.Append(" AUTOINCREMENT ") + .Append(col.AutoIncrementSeed); + } + + //Trailing comma + builder.AppendLine(","); + + //No sizing for sqlite + } + + //Add unique column contraints + if (uniqueCols.Any()) + { + builder.Append("UNIQUE("); + for(int i = 0; i < uniqueCols.Count;) + { + //Add column name + builder.Append(uniqueCols[i].ColumnName); + + i++; + + //Add trailing commas + if(i < uniqueCols.Count) + { + builder.Append(','); + } + } + + //Add trailing ) + builder.AppendLine(")"); + } + else + { + //remove trailing comma + int index = builder.Length; + while (builder[--index] != ',') + { } + + //Remove the trailing comma + builder.Remove(index, 1); + } + + //Close the create table command + builder.AppendLine(")"); + + //Replaced the table name variables + builder.Replace("@tableName", table.TableName); + } + + protected override string GetTypeStringFromDbType(DbType type) + { + return type switch + { + DbType.AnsiString => "TEXT", + DbType.Binary => "BLOB", + DbType.Byte => "INTEGER", + DbType.Boolean => "INTEGER", + DbType.Currency => "NUMERIC", + DbType.Date => "NUMERIC", + DbType.DateTime => "NUMERIC", + DbType.Decimal => "NUMERIC", + DbType.Double => "NUMERIC", + DbType.Guid => "TEXT", + DbType.Int16 => "INTEGER", + DbType.Int32 => "INTEGER", + DbType.Int64 => "INTEGER", + DbType.Object => throw new NotSupportedException("A .NET object type is not a supported MySql data-type"), + DbType.SByte => "INTEGER", + DbType.Single => "NUMERIC", + DbType.String => "TEXT", + DbType.Time => "TEXT", + DbType.UInt16 => "INTEGER", + DbType.UInt32 => "INTEGER", + DbType.UInt64 => "INTEGER", + DbType.VarNumeric => "BLOB", + DbType.AnsiStringFixedLength => "TEXT", + DbType.StringFixedLength => "TEXT", + DbType.Xml => "TEXT", + DbType.DateTime2 => "NUMERIC", + DbType.DateTimeOffset => "NUMERIC", + _ => throw new NotSupportedException("The desired property data-type is not a supported MySql data-type"), + }; + } + } +} diff --git a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/IDBCommandGenerator.cs b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/IDBCommandGenerator.cs new file mode 100644 index 0000000..b362b5e --- /dev/null +++ b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/IDBCommandGenerator.cs @@ -0,0 +1,44 @@ +/* +* Copyright (c) 2023 Vaughn Nugent +* +* Library: VNLib +* Package: VNLib.Plugins.Extensions.Loading.Sql +* File: IDBCommandGenerator.cs +* +* IDBCommandGenerator.cs is part of VNLib.Plugins.Extensions.Loading.Sql which is part of the larger +* VNLib collection of libraries and utilities. +* +* VNLib.Plugins.Extensions.Loading.Sql is free software: you can redistribute it and/or modify +* it under the terms of the GNU Affero General Public License as +* published by the Free Software Foundation, either version 3 of the +* License, or (at your option) any later version. +* +* VNLib.Plugins.Extensions.Loading.Sql is distributed in the hope that it will be useful, +* but WITHOUT ANY WARRANTY; without even the implied warranty of +* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +* GNU Affero General Public License for more details. +* +* You should have received a copy of the GNU Affero General Public License +* along with this program. If not, see https://www.gnu.org/licenses/. +*/ + +using System; +using System.Data; +using System.Text; + +namespace VNLib.Plugins.Extensions.Loading.Sql.DatabaseBuilder +{ + /// + /// Generates specialized statments used to modify a database + /// + interface IDBCommandGenerator + { + /// + /// Compiles a valid database table creation statment from the + /// defining data columns + /// + /// The string builder used to build the creation statment + /// The that defines the columns within the table + void BuildCreateStatment(StringBuilder builder, DataTable table); + } +} diff --git a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/IDbTable.cs b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/IDbTable.cs new file mode 100644 index 0000000..2ab1912 --- /dev/null +++ b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/IDbTable.cs @@ -0,0 +1,39 @@ +/* +* Copyright (c) 2023 Vaughn Nugent +* +* Library: VNLib +* Package: VNLib.Plugins.Extensions.Loading.Sql +* File: IDbTable.cs +* +* IDbTable.cs is part of VNLib.Plugins.Extensions.Loading.Sql which is part +* of the larger VNLib collection of libraries and utilities. +* +* VNLib.Plugins.Extensions.Loading.Sql is free software: you can redistribute it and/or modify +* it under the terms of the GNU Affero General Public License as +* published by the Free Software Foundation, either version 3 of the +* License, or (at your option) any later version. +* +* VNLib.Plugins.Extensions.Loading.Sql is distributed in the hope that it will be useful, +* but WITHOUT ANY WARRANTY; without even the implied warranty of +* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +* GNU Affero General Public License for more details. +* +* You should have received a copy of the GNU Affero General Public License +* along with this program. If not, see https://www.gnu.org/licenses/. +*/ + +using System.Text; + +namespace VNLib.Plugins.Extensions.Loading.Sql.DatabaseBuilder +{ + interface IDbTable + { + /// + /// Requests the table build the table creation statment using the + /// instance and write the statment to the string builder instance + /// + /// The instance to write the statment to + /// The abstract command builder used to create the statment + void WriteCommand(StringBuilder sb, IDBCommandGenerator commandBuilder); + } +} diff --git a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DbBuilder.cs b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DbBuilder.cs new file mode 100644 index 0000000..7eb3e66 --- /dev/null +++ b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DbBuilder.cs @@ -0,0 +1,221 @@ +/* +* Copyright (c) 2023 Vaughn Nugent +* +* Library: VNLib +* Package: VNLib.Plugins.Extensions.Loading.Sql +* File: DbBuilder.cs +* +* DbBuilder.cs is part of VNLib.Plugins.Extensions.Loading.Sql which +* is part of the larger VNLib collection of libraries and utilities. +* +* VNLib.Plugins.Extensions.Loading.Sql is free software: you can redistribute it and/or modify +* it under the terms of the GNU Affero General Public License as +* published by the Free Software Foundation, either version 3 of the +* License, or (at your option) any later version. +* +* VNLib.Plugins.Extensions.Loading.Sql is distributed in the hope that it will be useful, +* but WITHOUT ANY WARRANTY; without even the implied warranty of +* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +* GNU Affero General Public License for more details. +* +* You should have received a copy of the GNU Affero General Public License +* along with this program. If not, see https://www.gnu.org/licenses/. +*/ + +using System; +using System.Data; +using System.Linq; +using System.Text; +using System.Reflection; +using System.Linq.Expressions; +using System.Collections.Generic; +using System.ComponentModel.DataAnnotations; +using System.ComponentModel.DataAnnotations.Schema; + +using VNLib.Plugins.Extensions.Loading.Sql.DatabaseBuilder; + +namespace VNLib.Plugins.Extensions.Loading.Sql +{ + internal sealed class DbBuilder : IDbContextBuilder + { + private readonly LinkedList _tables = new(); + /// + public IDbTableBuilder DefineTable() + { + //Use the table attribute to specify the table name + TableAttribute? tnA = typeof(T).GetCustomAttribute(); + + return DefineTable(tnA?.Name); + } + + /// + public IDbTableBuilder DefineTable(string? tableName) + { + Type rtType = typeof(T); + + //Table name is the defined name, or the type name + DataTable table = new(tableName ?? rtType.Name); + + //Create table with name + TableBuilder builder = new(table, rtType); + + //Store the new table builder + _tables.AddLast(builder); + + return builder; + } + + internal string[] BuildCreateCommand(IDBCommandGenerator cmdBuilder) + { + List tableCommands = new(); + + foreach (IDbTable table in _tables) + { + //Setup a new string builder for this table command + StringBuilder sb = new(); + + table.WriteCommand(sb, cmdBuilder); + + //build the command string and add to the list + string cmd = sb.ToString(); + tableCommands.Add(cmd); + } + + return tableCommands.ToArray(); + } + + private record class TableBuilder(DataTable Table, Type RuntimeType) : IDbTable, IDbTableBuilder + { + /// + public IDbColumnBuilder WithColumn(Expression> selector) + { + KeyValuePair selectorData; + + //recover the expression information to determine the selected property + if (selector.Body is MemberExpression me) + { + selectorData = new(me.Member.Name, (me.Member as PropertyInfo)!.PropertyType); + } + else if(selector.Body is UnaryExpression ue) + { + //We need to get the property name from the operand + string name = ((MemberExpression)ue.Operand).Member.Name; + + //We want to get the operand type if the user wants to cast the type, we want to capture the casted type + selectorData = new(name, ue.Type); + } + else + { + throw new ArgumentException("The selector expression type is not supported", nameof(selector)); + } + + //try to see if an altername column name is defined on the type + string? colNameAttr = GetPropertyColumnName(selectorData.Key); + + /* + * Create the new column with the name of the column attribute, or fallback to the propearty name + * + * NOTE: I am recovering the column type from the expression type, not the model type. This allows + * the user to alter the type without having to alter the entity to 'fool' database type conversion + */ + DataColumn col = new(colNameAttr ?? selectorData.Key, selectorData.Value); + + //Check for maxLen property + int? maxLen = GetPropertyMaxLen(col.ColumnName); + + if (maxLen.HasValue) + { + col.MaxLength = maxLen.Value; + } + + //Store the column + Table.Columns.Add(col); + + //See if key is found, then add the colum to the primary key table + bool? isKey = GetPropertyIsKey(selectorData.Key); + if (isKey.HasValue && isKey.Value) + { + col.AddToPrimaryKeys(); + } + + //Set the colum as timestamp + bool? isRowVersion = GetPropertyIsRowVersion(selectorData.Key); + if (isRowVersion.HasValue && isRowVersion.Value) + { + col.SetTimeStamp(); + } + + //Init new column builder + return new ColumnBuilder(col, this); + } + + /// + public void WriteCommand(StringBuilder sb, IDBCommandGenerator commandBuilder) => commandBuilder.BuildCreateStatment(sb, Table); + + + private int? GetPropertyMaxLen(string propertyName) + { + PropertyInfo? property = RuntimeType.GetProperties() + .Where(p => propertyName.Equals(p.Name, StringComparison.OrdinalIgnoreCase)) + .FirstOrDefault(); + + //Get the max-length attribute + MaxLengthAttribute? mla = property?.GetCustomAttribute(); + + return mla?.Length; + } + private string? GetPropertyColumnName(string propertyName) + { + PropertyInfo? property = RuntimeType.GetProperties() + .Where(p => propertyName.Equals(p.Name, StringComparison.OrdinalIgnoreCase)) + .FirstOrDefault(); + + ColumnAttribute? mla = property?.GetCustomAttribute(); + + return mla?.Name; + } + private bool? GetPropertyIsKey(string propertyName) + { + PropertyInfo? property = RuntimeType.GetProperties() + .Where(p => propertyName.Equals(p.Name, StringComparison.OrdinalIgnoreCase)) + .FirstOrDefault(); + + //Get the propertie's key attribute + KeyAttribute? ka = property?.GetCustomAttribute(); + + return ka == null ? null : true; + } + + private bool? GetPropertyIsRowVersion(string propertyName) + { + PropertyInfo? property = RuntimeType.GetProperties() + .Where(p => propertyName.Equals(p.Name, StringComparison.OrdinalIgnoreCase)) + .FirstOrDefault(); + + //Get the properties' timestamp attribute + TimestampAttribute? ts = property?.GetCustomAttribute(); + + return ts == null ? null : true; + } + + private record class ColumnBuilder(DataColumn Column, IDbTableBuilder Table) : IDbColumnBuilder + { + public IDbTableBuilder Next() => Table; + + public IDbColumnBuilder ConfigureColumn(Action columnSetter) + { + columnSetter(Column); + return this; + } + + public IDbColumnBuilder AutoIncrement(int seed = 1, int step = 1) + { + Column.AutoIncrement = true; + Column.AutoIncrementSeed = seed; + Column.AutoIncrementStep = step; + return this; + } + } + } + } +} diff --git a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/IDbColumnBuilder.cs b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/IDbColumnBuilder.cs new file mode 100644 index 0000000..3bd066c --- /dev/null +++ b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/IDbColumnBuilder.cs @@ -0,0 +1,57 @@ +/* +* Copyright (c) 2023 Vaughn Nugent +* +* Library: VNLib +* Package: VNLib.Plugins.Extensions.Loading.Sql +* File: IDbColumnBuilder.cs +* +* IDbColumnBuilder.cs is part of VNLib.Plugins.Extensions.Loading.Sql which +* is part of the larger VNLib collection of libraries and utilities. +* +* VNLib.Plugins.Extensions.Loading.Sql is free software: you can redistribute it and/or modify +* it under the terms of the GNU Affero General Public License as +* published by the Free Software Foundation, either version 3 of the +* License, or (at your option) any later version. +* +* VNLib.Plugins.Extensions.Loading.Sql is distributed in the hope that it will be useful, +* but WITHOUT ANY WARRANTY; without even the implied warranty of +* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +* GNU Affero General Public License for more details. +* +* You should have received a copy of the GNU Affero General Public License +* along with this program. If not, see https://www.gnu.org/licenses/. +*/ + +using System; +using System.Data; + +namespace VNLib.Plugins.Extensions.Loading.Sql +{ + /// + /// A tool used to configure your new columns within the database + /// + /// + public interface IDbColumnBuilder + { + /// + /// Gets the original to move to the next column + /// (allows method chaining) + /// + /// The original to define the next column + IDbTableBuilder Next(); + + /// + /// Allows you to configure your new column. You may call this method as many times as necessary + /// to configure your new column. + /// + /// + /// .ConfigureColumn(c => c.ColumnName = "ColumnName") + /// .ConfigureColumn(c => c.MaxLength = 1000) + /// + /// + /// + /// Your callback action that alters the column state + /// The chainable + IDbColumnBuilder ConfigureColumn(Action columnSetter); + } +} diff --git a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/IDbContextBuilder.cs b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/IDbContextBuilder.cs new file mode 100644 index 0000000..01e9b2f --- /dev/null +++ b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/IDbContextBuilder.cs @@ -0,0 +1,51 @@ +/* +* Copyright (c) 2023 Vaughn Nugent +* +* Library: VNLib +* Package: VNLib.Plugins.Extensions.Loading.Sql +* File: IDbContextBuilder.cs +* +* IDbContextBuilder.cs is part of VNLib.Plugins.Extensions.Loading.Sql which is part of the larger +* VNLib collection of libraries and utilities. +* +* VNLib.Plugins.Extensions.Loading.Sql is free software: you can redistribute it and/or modify +* it under the terms of the GNU Affero General Public License as +* published by the Free Software Foundation, either version 3 of the +* License, or (at your option) any later version. +* +* VNLib.Plugins.Extensions.Loading.Sql is distributed in the hope that it will be useful, +* but WITHOUT ANY WARRANTY; without even the implied warranty of +* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +* GNU Affero General Public License for more details. +* +* You should have received a copy of the GNU Affero General Public License +* along with this program. If not, see https://www.gnu.org/licenses/. +*/ + +using System.ComponentModel.DataAnnotations.Schema; + +namespace VNLib.Plugins.Extensions.Loading.Sql +{ + /// + /// Passed to a during a database creation event. + /// + public interface IDbContextBuilder + { + /// + /// Defines the existance of a table within the database by its type name + /// + /// If your entity defines a , this name value is used + /// + /// + /// The entity type to build + /// A new used to build the table for this entity + IDbTableBuilder DefineTable(); + + /// + /// Defines the existance of a table within the database by the supplied table name + /// + /// The entity type to build + /// A new used to build the table for this entity + IDbTableBuilder DefineTable(string tableName); + } +} diff --git a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/IDbTableBuilder.cs b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/IDbTableBuilder.cs new file mode 100644 index 0000000..dbe3c83 --- /dev/null +++ b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/IDbTableBuilder.cs @@ -0,0 +1,51 @@ +/* +* Copyright (c) 2023 Vaughn Nugent +* +* Library: VNLib +* Package: VNLib.Plugins.Extensions.Loading.Sql +* File: IDbTableBuilder.cs +* +* IDbTableBuilder.cs is part of VNLib.Plugins.Extensions.Loading.Sql which +* is part of the larger VNLib collection of libraries and utilities. +* +* VNLib.Plugins.Extensions.Loading.Sql is free software: you can redistribute it and/or modify +* it under the terms of the GNU Affero General Public License as +* published by the Free Software Foundation, either version 3 of the +* License, or (at your option) any later version. +* +* VNLib.Plugins.Extensions.Loading.Sql is distributed in the hope that it will be useful, +* but WITHOUT ANY WARRANTY; without even the implied warranty of +* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +* GNU Affero General Public License for more details. +* +* You should have received a copy of the GNU Affero General Public License +* along with this program. If not, see https://www.gnu.org/licenses/. +*/ + +using System; +using System.Linq.Expressions; +using System.ComponentModel.DataAnnotations.Schema; + +namespace VNLib.Plugins.Extensions.Loading.Sql +{ + /// + /// A builder type that allows you to define columns within a database table + /// + /// The entity type + public interface IDbTableBuilder + { + /// + /// Define a column from your entity type in the new table. Column ordinal positions are defined + /// by the order this method is called on a table. + /// + /// The column type + /// The entity property selector + /// The new column builder for the entity + /// + /// You may alter the column name by specifying the on a given property + /// or by overriding the column name: + /// .ConfigureColumn(c => c.ColumnName = "MyColumnName") + /// + IDbColumnBuilder WithColumn(Expression> propSelector); + } +} diff --git a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/IDbTableDefinition.cs b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/IDbTableDefinition.cs new file mode 100644 index 0000000..7730afe --- /dev/null +++ b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/IDbTableDefinition.cs @@ -0,0 +1,41 @@ +/* +* Copyright (c) 2023 Vaughn Nugent +* +* Library: VNLib +* Package: VNLib.Plugins.Extensions.Loading.Sql +* File: IDbTableDefinition.cs +* +* IDbTableDefinition.cs is part of VNLib.Plugins.Extensions.Loading.Sql which is +* part of the larger VNLib collection of libraries and utilities. +* +* VNLib.Plugins.Extensions.Loading.Sql is free software: you can redistribute it and/or modify +* it under the terms of the GNU Affero General Public License as +* published by the Free Software Foundation, either version 3 of the +* License, or (at your option) any later version. +* +* VNLib.Plugins.Extensions.Loading.Sql is distributed in the hope that it will be useful, +* but WITHOUT ANY WARRANTY; without even the implied warranty of +* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +* GNU Affero General Public License for more details. +* +* You should have received a copy of the GNU Affero General Public License +* along with this program. If not, see https://www.gnu.org/licenses/. +*/ + +namespace VNLib.Plugins.Extensions.Loading.Sql +{ + /// + /// When implemented by a allows + /// for the custom creation of database tables for any given entity + /// + public interface IDbTableDefinition + { + /// + /// Invoked when the model is being evaluated and the database tables are being created. You will define + /// your database tables on your entities. + /// + /// The used to define the tables and columns in your database + /// An optional user-supplied state instace passed from the creation method + void OnDatabaseCreating(IDbContextBuilder builder, object? userState); + } +} diff --git a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/SqlDbConnectionLoader.cs b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/SqlDbConnectionLoader.cs index c2d7726..f6985f8 100644 --- a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/SqlDbConnectionLoader.cs +++ b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/SqlDbConnectionLoader.cs @@ -23,8 +23,12 @@ */ using System; +using System.Linq; +using System.Data; using System.Data.Common; +using System.Threading.Tasks; using System.Collections.Generic; +using System.ComponentModel.DataAnnotations; using MySqlConnector; @@ -34,9 +38,12 @@ using Microsoft.EntityFrameworkCore; using VNLib.Utils.Logging; using VNLib.Utils.Extensions; +using VNLib.Plugins.Extensions.Loading.Sql.DatabaseBuilder; +using VNLib.Plugins.Extensions.Loading.Sql.DatabaseBuilder.Helpers; namespace VNLib.Plugins.Extensions.Loading.Sql { + /// /// Provides common basic SQL loading extensions for plugins /// @@ -44,7 +51,9 @@ namespace VNLib.Plugins.Extensions.Loading.Sql { public const string SQL_CONFIG_KEY = "sql"; public const string DB_PASSWORD_KEY = "db_password"; - + + private const string MAX_LEN_BYPASS_KEY = "MaxLen"; + private const string TIMESTAMP_BYPASS = "TimeStamp"; /// /// Gets (or loads) the ambient sql connection factory for the current plugin @@ -69,18 +78,24 @@ namespace VNLib.Plugins.Extensions.Loading.Sql if ("sqlite".Equals(type, StringComparison.OrdinalIgnoreCase)) { + using SecretResult? password = plugin.TryGetSecretAsync(DB_PASSWORD_KEY).GetAwaiter().GetResult(); + //Use connection builder DbConnectionStringBuilder sqlBuilder = new SqliteConnectionStringBuilder() { DataSource = sqlConf["source"].GetString(), + Password = password?.Result.ToString(), + Pooling = true, + Mode = SqliteOpenMode.ReadWriteCreate }; + string connectionString = sqlBuilder.ToString(); DbConnection DbFactory() => new SqliteConnection(connectionString); return DbFactory; } else if("mysql".Equals(type, StringComparison.OrdinalIgnoreCase)) { - using SecretResult? password = plugin.TryGetSecretAsync(DB_PASSWORD_KEY).Result; + using SecretResult? password = plugin.TryGetSecretAsync(DB_PASSWORD_KEY).GetAwaiter().GetResult(); DbConnectionStringBuilder sqlBuilder = new MySqlConnectionStringBuilder() { @@ -90,7 +105,7 @@ namespace VNLib.Plugins.Extensions.Loading.Sql Password = password?.Result.ToString(), Pooling = true, LoadBalance = MySqlLoadBalance.LeastConnections, - MinimumPoolSize = sqlConf["min_pool_size"].GetUInt32() + MinimumPoolSize = sqlConf["min_pool_size"].GetUInt32(), }; string connectionString = sqlBuilder.ToString(); @@ -100,7 +115,7 @@ namespace VNLib.Plugins.Extensions.Loading.Sql //Default to mssql else { - using SecretResult? password = plugin.TryGetSecretAsync(DB_PASSWORD_KEY).Result; + using SecretResult? password = plugin.TryGetSecretAsync(DB_PASSWORD_KEY).GetAwaiter().GetResult(); //Use connection builder DbConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder() @@ -114,6 +129,7 @@ namespace VNLib.Plugins.Extensions.Loading.Sql MinPoolSize = sqlConf["min_pool_size"].GetInt32(), Replication = true }; + string connectionString = sqlBuilder.ToString(); DbConnection DbFactory() => new SqlConnection(connectionString); return DbFactory; @@ -170,5 +186,262 @@ namespace VNLib.Plugins.Extensions.Loading.Sql options.Freeze(); return options; } + + /// + /// Ensures the tables that back your desired DbContext exist within the configured database, + /// or creates them if needed. + /// + /// + /// + /// The state object to pass to the + /// A task that resolves when the tables have been created + public static Task EnsureDbCreatedAsync(this PluginBase pbase, object? state) where T : IDbTableDefinition, new() + { + T creator = new (); + return EnsureDbCreatedAsync(pbase, creator, state); + } + + /// + /// Ensures the tables that back your desired DbContext exist within the configured database, + /// or creates them if needed. + /// + /// + /// + /// The instance of the to build the database from + /// The state object to pass to the + /// A task that resolves when the tables have been created + public static async Task EnsureDbCreatedAsync(this PluginBase plugin, T dbCreator, object? state) where T : IDbTableDefinition + { + DbBuilder builder = new(); + + //Invoke ontbCreating to setup the dbBuilder + dbCreator.OnDatabaseCreating(builder, state); + + //Create a new db connection + await using DbConnection connection = plugin.GetConnectionFactory()(); + + //Get the abstract database from the connection type + IDBCommandGenerator cb = connection.GetCmGenerator(); + + //Compile the db command as a text Sql command + string[] createComamnds = builder.BuildCreateCommand(cb); + + //begin connection + await connection.OpenAsync(plugin.UnloadToken); + + //Transaction + await using DbTransaction transaction = await connection.BeginTransactionAsync(IsolationLevel.Serializable, plugin.UnloadToken); + + //Init new text command + await using DbCommand command = connection.CreateCommand(); + command.Transaction = transaction; + command.CommandType = CommandType.Text; + + foreach (string createCmd in createComamnds) + { + if (plugin.IsDebug()) + { + plugin.Log.Debug("Creating new table for {type} with command\n{cmd}", typeof(T).Name, createCmd); + } + + //Set the command, were not using parameters, so we dont need to clear anyting + command.CommandText = createCmd; + + //Excute the command, it may return 0 if the table's already exist + _ = await command.ExecuteNonQueryAsync(plugin.UnloadToken); + } + + //Commit transaction now were complete + await transaction.CommitAsync(plugin.UnloadToken); + + //All done! + plugin.Log.Debug("Successfully created tables for {type}", typeof(T).Name); + } + + #region ColumnExtensions + + /// + /// Sets the column as a PrimaryKey in the table. You may also set the + /// on the property. + /// + /// The entity type + /// + /// The chainable + public static IDbColumnBuilder SetIsKey(this IDbColumnBuilder builder) + { + //Add ourself to the primary keys list + builder.ConfigureColumn(static col => col.AddToPrimaryKeys()); + return builder; + } + + /// + /// Sets the column ordinal index, or column position, within the table. + /// + /// The entity type + /// + /// The column's ordinal postion with the database + /// The chainable + public static IDbColumnBuilder SetPosition(this IDbColumnBuilder builder, int columOridinalIndex) + { + //Add ourself to the primary keys list + builder.ConfigureColumn(col => col.SetOrdinal(columOridinalIndex)); + return builder; + } + + /// + /// Sets the auto-increment property on the column, this is just a short-cut to + /// setting the properties yourself on the column. + /// + /// The starting (seed) of the increment parameter + /// The increment/step parameter + /// + /// The chainable + public static IDbColumnBuilder AutoIncrement(this IDbColumnBuilder builder, int seed = 1, int increment = 1) + { + //Set the auto-increment features + builder.ConfigureColumn(col => + { + col.AutoIncrement = true; + col.AutoIncrementSeed = seed; + col.AutoIncrementStep = increment; + }); + return builder; + } + + /// + /// Sets the property to the desired value. This value is set + /// via a if defined on the property, this method will override + /// that value. + /// + /// Override the maxium length property on the column + /// + /// The chainable + public static IDbColumnBuilder MaxLength(this IDbColumnBuilder builder, int maxLength) + { + //Set the max-length + builder.ConfigureColumn(col => col.MaxLength(maxLength)); + return builder; + } + + /// + /// Override the + /// + /// + /// + /// A value that indicate if you allow null in the column + /// The chainable + public static IDbColumnBuilder AllowNull(this IDbColumnBuilder builder, bool value) + { + builder.ConfigureColumn(col => col.AllowDBNull = value); + return builder; + } + + /// + /// Sets the property to true + /// + /// The entity type + /// + /// The chainable + public static IDbColumnBuilder Unique(this IDbColumnBuilder builder) + { + builder.ConfigureColumn(static col => col.Unique = true); + return builder; + } + + /// + /// Sets the default value for the column + /// + /// The entity type + /// + /// The column default value + /// The chainable + public static IDbColumnBuilder WithDefault(this IDbColumnBuilder builder, object defaultValue) + { + builder.ConfigureColumn(col => col.DefaultValue = defaultValue); + return builder; + } + + /// + /// Specifies this column is a RowVersion/TimeStamp for optimistic concurrency for some + /// databases. + /// + /// This vaule is set by default if the entity property specifies a + /// + /// + /// The entity type + /// + /// The chainable + public static IDbColumnBuilder TimeStamp(this IDbColumnBuilder builder) + { + builder.ConfigureColumn(static col => col.SetTimeStamp()); + return builder; + } + + #endregion + + private static IDBCommandGenerator GetCmGenerator(this IDbConnection connection) + { + //Determine connection type + if (connection is SqlConnection) + { + //Return the abstract db from the db command type + return new MsSqlDb(); + } + else if (connection is SqliteConnection) + { + return new SqlLiteDb(); + } + else if (connection is MySqlConnection) + { + return new MySqlDb(); + } + else + { + throw new NotSupportedException("This library does not support the abstract databse backend"); + } + } + + internal static bool IsPrimaryKey(this DataColumn col) => col.Table!.PrimaryKey.Contains(col); + + /* + * I am bypassing the DataColumn.MaxLength property because it does more validation + * than we need against the type and can cause unecessary issues, so im just bypassing it + * for now + */ + + internal static void MaxLength(this DataColumn column, int length) + { + column.ExtendedProperties[MAX_LEN_BYPASS_KEY] = length; + } + + internal static int MaxLength(this DataColumn column) + { + return column.ExtendedProperties.ContainsKey(MAX_LEN_BYPASS_KEY) + ? (int)column.ExtendedProperties[MAX_LEN_BYPASS_KEY] + : column.MaxLength; + } + + internal static void SetTimeStamp(this DataColumn column) + { + //We just need to set the key + column.ExtendedProperties[TIMESTAMP_BYPASS] = null; + } + + internal static bool IsTimeStamp(this DataColumn column) + { + return column.ExtendedProperties.ContainsKey(TIMESTAMP_BYPASS); + } + + internal static void AddToPrimaryKeys(this DataColumn col) + { + //Add the column to the table's primary key array + List cols = new(col.Table!.PrimaryKey) + { + col + }; + + //Update the table primary keys now that this col has been added + col.Table.PrimaryKey = cols.Distinct().ToArray(); + } } } diff --git a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/VNLib.Plugins.Extensions.Loading.Sql.csproj b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/VNLib.Plugins.Extensions.Loading.Sql.csproj index 5e7e2a5..c8720ef 100644 --- a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/VNLib.Plugins.Extensions.Loading.Sql.csproj +++ b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/VNLib.Plugins.Extensions.Loading.Sql.csproj @@ -33,7 +33,7 @@ - + diff --git a/lib/VNLib.Plugins.Extensions.Loading/src/UserManager.cs b/lib/VNLib.Plugins.Extensions.Loading/src/UserManager.cs index a3d667d..33f6df3 100644 --- a/lib/VNLib.Plugins.Extensions.Loading/src/UserManager.cs +++ b/lib/VNLib.Plugins.Extensions.Loading/src/UserManager.cs @@ -25,7 +25,6 @@ using System; using System.Threading; using System.Threading.Tasks; -using System.Runtime.CompilerServices; using VNLib.Utils; using VNLib.Utils.Memory; @@ -49,6 +48,7 @@ namespace VNLib.Plugins.Extensions.Loading.Users public UserManager(PluginBase plugin) { + //Load the default user assembly _dynamicLoader = LoadUserAssembly(plugin, DEFAULT_USER_ASM); } @@ -88,48 +88,42 @@ namespace VNLib.Plugins.Extensions.Loading.Users } /// - [MethodImpl(MethodImplOptions.AggressiveInlining)] public Task CreateUserAsync(string userid, string emailAddress, ulong privilages, PrivateString passHash, CancellationToken cancellation = default) { return _dynamicLoader.CreateUserAsync(userid, emailAddress, privilages, passHash, cancellation); } /// - [MethodImpl(MethodImplOptions.AggressiveInlining)] public Task GetUserAndPassFromEmailAsync(string emailAddress, CancellationToken cancellationToken = default) { return _dynamicLoader.GetUserAndPassFromEmailAsync(emailAddress, cancellationToken); } /// - [MethodImpl(MethodImplOptions.AggressiveInlining)] public Task GetUserAndPassFromIDAsync(string userid, CancellationToken cancellation = default) { return _dynamicLoader.GetUserAndPassFromIDAsync(userid, cancellation); } /// - [MethodImpl(MethodImplOptions.AggressiveInlining)] public Task GetUserCountAsync(CancellationToken cancellation = default) { return _dynamicLoader.GetUserCountAsync(cancellation); } /// - [MethodImpl(MethodImplOptions.AggressiveInlining)] public Task GetUserFromEmailAsync(string emailAddress, CancellationToken cancellationToken = default) { return _dynamicLoader.GetUserFromEmailAsync(emailAddress, cancellationToken); } /// - [MethodImpl(MethodImplOptions.AggressiveInlining)] public Task GetUserFromIDAsync(string userId, CancellationToken cancellationToken = default) { return _dynamicLoader.GetUserFromIDAsync(userId, cancellationToken); } + /// - [MethodImpl(MethodImplOptions.AggressiveInlining)] public Task UpdatePassAsync(IUser user, PrivateString newPass, CancellationToken cancellation = default) { return _dynamicLoader.UpdatePassAsync(user, newPass, cancellation); diff --git a/lib/VNLib.Plugins.Extensions.Loading/src/VNLib.Plugins.Extensions.Loading.csproj b/lib/VNLib.Plugins.Extensions.Loading/src/VNLib.Plugins.Extensions.Loading.csproj index ca4113e..3b08812 100644 --- a/lib/VNLib.Plugins.Extensions.Loading/src/VNLib.Plugins.Extensions.Loading.csproj +++ b/lib/VNLib.Plugins.Extensions.Loading/src/VNLib.Plugins.Extensions.Loading.csproj @@ -8,14 +8,7 @@ enable latest-all - - - https://www.vaughnnugent.com/resources/software - Copyright © 2023 Vaughn Nugent - Vaughn Nugent - - - + Vaughn Nugent Vaughn Nugent @@ -39,13 +32,14 @@ all runtime; build; native; contentfiles; analyzers; buildtransitive - + + + - diff --git a/lib/VNLib.Plugins.Extensions.Validation/src/VNLib.Plugins.Extensions.Validation.csproj b/lib/VNLib.Plugins.Extensions.Validation/src/VNLib.Plugins.Extensions.Validation.csproj index 826fe47..aced03a 100644 --- a/lib/VNLib.Plugins.Extensions.Validation/src/VNLib.Plugins.Extensions.Validation.csproj +++ b/lib/VNLib.Plugins.Extensions.Validation/src/VNLib.Plugins.Extensions.Validation.csproj @@ -34,7 +34,7 @@ all runtime; build; native; contentfiles; analyzers; buildtransitive - + -- cgit