diff options
Diffstat (limited to 'lib/VNLib.Plugins.Extensions.Loading.Sql/src')
14 files changed, 1440 insertions, 5 deletions
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<Type, DbType> 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 + }; + + /// <summary> + /// Gets the database string type name from the given .NET runtime type + /// information. + /// </summary> + /// <param name="type">The type to resolve</param> + /// <returns>The type string that is realtive to the given database backend</returns> + /// <exception cref="DbCreationException"></exception> + 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); + } + + + /// <summary> + /// Gets a string property value from a discovered <see cref="DbType"/> + /// </summary> + /// <param name="type">The dbType discovered from the type according to the backing database</param> + /// <returns>The parameter type as a string with an optional size variable</returns> + protected abstract string GetTypeStringFromDbType(DbType type); + + ///<inheritdoc/> + 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; + + ///<inheritdoc/> + 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); + } + + ///<inheritdoc/> + 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; + + ///<inheritdoc/> + 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); + } + + ///<inheritdoc/> + 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<DataColumn> 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 +{ + /// <summary> + /// Generates specialized statments used to modify a database + /// </summary> + interface IDBCommandGenerator + { + /// <summary> + /// Compiles a valid database table creation statment from the <see cref="DataTable"/> + /// defining data columns + /// </summary> + /// <param name="builder">The string builder used to build the creation statment</param> + /// <param name="table">The <see cref="DataTable"/> that defines the columns within the table</param> + 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 + { + /// <summary> + /// Requests the table build the table creation statment using the <see cref="IDBCommandGenerator"/> + /// instance and write the statment to the string builder instance + /// </summary> + /// <param name="sb">The <see cref="StringBuilder"/> instance to write the statment to</param> + /// <param name="commandBuilder">The abstract command builder used to create the statment</param> + 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<IDbTable> _tables = new(); + ///<inheritdoc/> + public IDbTableBuilder<T> DefineTable<T>() + { + //Use the table attribute to specify the table name + TableAttribute? tnA = typeof(T).GetCustomAttribute<TableAttribute>(); + + return DefineTable<T>(tnA?.Name); + } + + ///<inheritdoc/> + public IDbTableBuilder<T> DefineTable<T>(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<T> builder = new(table, rtType); + + //Store the new table builder + _tables.AddLast(builder); + + return builder; + } + + internal string[] BuildCreateCommand(IDBCommandGenerator cmdBuilder) + { + List<string> 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<T>(DataTable Table, Type RuntimeType) : IDbTable, IDbTableBuilder<T> + { + ///<inheritdoc/> + public IDbColumnBuilder<T> WithColumn<TCol>(Expression<Func<T, TCol>> selector) + { + KeyValuePair<string, Type> 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); + } + + ///<inheritdoc/> + 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<MaxLengthAttribute>(); + + 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<ColumnAttribute>(); + + 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<KeyAttribute>(); + + 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<TimestampAttribute>(); + + return ts == null ? null : true; + } + + private record class ColumnBuilder(DataColumn Column, IDbTableBuilder<T> Table) : IDbColumnBuilder<T> + { + public IDbTableBuilder<T> Next() => Table; + + public IDbColumnBuilder<T> ConfigureColumn(Action<DataColumn> columnSetter) + { + columnSetter(Column); + return this; + } + + public IDbColumnBuilder<T> 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 +{ + /// <summary> + /// A tool used to configure your new columns within the database + /// </summary> + /// <typeparam name="T"></typeparam> + public interface IDbColumnBuilder<T> + { + /// <summary> + /// Gets the original <see cref="IDbTableBuilder{T}"/> to move to the next column + /// (allows method chaining) + /// </summary> + /// <returns>The original <see cref="IDbTableBuilder{T}"/> to define the next column</returns> + IDbTableBuilder<T> Next(); + + /// <summary> + /// Allows you to configure your new column. You may call this method as many times as necessary + /// to configure your new column. + /// <para> + /// <code> + /// .ConfigureColumn(c => c.ColumnName = "ColumnName") + /// .ConfigureColumn(c => c.MaxLength = 1000) + /// </code> + /// </para> + /// </summary> + /// <param name="columnSetter">Your callback action that alters the column state</param> + /// <returns>The chainable <see cref="IDbColumnBuilder{T}"/></returns> + IDbColumnBuilder<T> ConfigureColumn(Action<DataColumn> 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 +{ + /// <summary> + /// Passed to a <see cref="IDbTableDefinition"/> during a database creation event. + /// </summary> + public interface IDbContextBuilder + { + /// <summary> + /// Defines the existance of a table within the database by its type name + /// <para> + /// If your entity defines a <see cref="TableAttribute"/>, this name value is used + /// </para> + /// </summary> + /// <typeparam name="T">The entity type to build</typeparam> + /// <returns>A new <see cref="IDbTableBuilder{T}"/> used to build the table for this entity</returns> + IDbTableBuilder<T> DefineTable<T>(); + + /// <summary> + /// Defines the existance of a table within the database by the supplied table name + /// </summary> + /// <typeparam name="T">The entity type to build</typeparam> + /// <returns>A new <see cref="IDbTableBuilder{T}"/> used to build the table for this entity</returns> + IDbTableBuilder<T> DefineTable<T>(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 +{ + /// <summary> + /// A builder type that allows you to define columns within a database table + /// </summary> + /// <typeparam name="T">The entity type</typeparam> + public interface IDbTableBuilder<T> + { + /// <summary> + /// 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. + /// </summary> + /// <typeparam name="TColumn">The column type</typeparam> + /// <param name="propSelector">The entity property selector</param> + /// <returns>The new column builder for the entity</returns> + /// <remarks> + /// You may alter the column name by specifying the <see cref="ColumnAttribute"/> on a given property + /// or by overriding the column name: + /// <code> .ConfigureColumn(c => c.ColumnName = "MyColumnName")</code> + /// </remarks> + IDbColumnBuilder<T> WithColumn<TColumn>(Expression<Func<T, TColumn>> 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 +{ + /// <summary> + /// When implemented by a <see cref="Microsoft.EntityFrameworkCore.DbContext"/> allows + /// for the custom creation of database tables for any given entity + /// </summary> + public interface IDbTableDefinition + { + /// <summary> + /// Invoked when the model is being evaluated and the database tables are being created. You will define + /// your database tables on your entities. + /// </summary> + /// <param name="builder">The <see cref="IDbTableDefinition"/> used to define the tables and columns in your database</param> + /// <param name="userState">An optional user-supplied state instace passed from the creation method</param> + 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 { + /// <summary> /// Provides common basic SQL loading extensions for plugins /// </summary> @@ -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"; /// <summary> /// 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; } + + /// <summary> + /// Ensures the tables that back your desired DbContext exist within the configured database, + /// or creates them if needed. + /// </summary> + /// <typeparam name="T"></typeparam> + /// <param name="pbase"></param> + /// <param name="state">The state object to pass to the <see cref="IDbTableDefinition.OnDatabaseCreating(IDbContextBuilder, object?)"/></param> + /// <returns>A task that resolves when the tables have been created</returns> + public static Task EnsureDbCreatedAsync<T>(this PluginBase pbase, object? state) where T : IDbTableDefinition, new() + { + T creator = new (); + return EnsureDbCreatedAsync(pbase, creator, state); + } + + /// <summary> + /// Ensures the tables that back your desired DbContext exist within the configured database, + /// or creates them if needed. + /// </summary> + /// <typeparam name="T"></typeparam> + /// <param name="plugin"></param> + /// <param name="dbCreator">The instance of the <see cref="IDbTableDefinition"/> to build the database from</param> + /// <param name="state">The state object to pass to the <see cref="IDbTableDefinition.OnDatabaseCreating(IDbContextBuilder, object?)"/></param> + /// <returns>A task that resolves when the tables have been created</returns> + public static async Task EnsureDbCreatedAsync<T>(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 + + /// <summary> + /// Sets the column as a PrimaryKey in the table. You may also set the + /// <see cref="KeyAttribute"/> on the property. + /// </summary> + /// <typeparam name="T">The entity type</typeparam> + /// <param name="builder"></param> + /// <returns>The chainable <see cref="IDbColumnBuilder{T}"/></returns> + public static IDbColumnBuilder<T> SetIsKey<T>(this IDbColumnBuilder<T> builder) + { + //Add ourself to the primary keys list + builder.ConfigureColumn(static col => col.AddToPrimaryKeys()); + return builder; + } + + /// <summary> + /// Sets the column ordinal index, or column position, within the table. + /// </summary> + /// <typeparam name="T">The entity type</typeparam> + /// <param name="builder"></param> + /// <param name="columOridinalIndex">The column's ordinal postion with the database</param> + /// <returns>The chainable <see cref="IDbColumnBuilder{T}"/></returns> + public static IDbColumnBuilder<T> SetPosition<T>(this IDbColumnBuilder<T> builder, int columOridinalIndex) + { + //Add ourself to the primary keys list + builder.ConfigureColumn(col => col.SetOrdinal(columOridinalIndex)); + return builder; + } + + /// <summary> + /// Sets the auto-increment property on the column, this is just a short-cut to + /// setting the properties yourself on the column. + /// </summary> + /// <param name="seed">The starting (seed) of the increment parameter</param> + /// <param name="increment">The increment/step parameter</param> + /// <param name="builder"></param> + /// <returns>The chainable <see cref="IDbColumnBuilder{T}"/></returns> + public static IDbColumnBuilder<T> AutoIncrement<T>(this IDbColumnBuilder<T> 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; + } + + /// <summary> + /// Sets the <see cref="DataColumn.MaxLength"/> property to the desired value. This value is set + /// via a <see cref="MaxLengthAttribute"/> if defined on the property, this method will override + /// that value. + /// </summary> + /// <param name="maxLength">Override the maxium length property on the column</param> + /// <param name="builder"></param> + /// <returns>The chainable <see cref="IDbColumnBuilder{T}"/></returns> + public static IDbColumnBuilder<T> MaxLength<T>(this IDbColumnBuilder<T> builder, int maxLength) + { + //Set the max-length + builder.ConfigureColumn(col => col.MaxLength(maxLength)); + return builder; + } + + /// <summary> + /// Override the <see cref="DataColumn.AllowDBNull"/> + /// </summary> + /// <typeparam name="T"></typeparam> + /// <param name="builder"></param> + /// <param name="value">A value that indicate if you allow null in the column</param> + /// <returns>The chainable <see cref="IDbColumnBuilder{T}"/></returns> + public static IDbColumnBuilder<T> AllowNull<T>(this IDbColumnBuilder<T> builder, bool value) + { + builder.ConfigureColumn(col => col.AllowDBNull = value); + return builder; + } + + /// <summary> + /// Sets the <see cref="DataColumn.Unique"/> property to true + /// </summary> + /// <typeparam name="T">The entity type</typeparam> + /// <param name="builder"></param> + /// <returns>The chainable <see cref="IDbColumnBuilder{T}"/></returns> + public static IDbColumnBuilder<T> Unique<T>(this IDbColumnBuilder<T> builder) + { + builder.ConfigureColumn(static col => col.Unique = true); + return builder; + } + + /// <summary> + /// Sets the default value for the column + /// </summary> + /// <typeparam name="T">The entity type</typeparam> + /// <param name="builder"></param> + /// <param name="defaultValue">The column default value</param> + /// <returns>The chainable <see cref="IDbColumnBuilder{T}"/></returns> + public static IDbColumnBuilder<T> WithDefault<T>(this IDbColumnBuilder<T> builder, object defaultValue) + { + builder.ConfigureColumn(col => col.DefaultValue = defaultValue); + return builder; + } + + /// <summary> + /// Specifies this column is a RowVersion/TimeStamp for optimistic concurrency for some + /// databases. + /// <para> + /// This vaule is set by default if the entity property specifies a <see cref="TimestampAttribute"/> + /// </para> + /// </summary> + /// <typeparam name="T">The entity type</typeparam> + /// <param name="builder"></param> + /// <returns>The chainable <see cref="IDbColumnBuilder{T}"/></returns> + public static IDbColumnBuilder<T> TimeStamp<T>(this IDbColumnBuilder<T> 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<DataColumn> 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 @@ </PropertyGroup> <ItemGroup> - <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite.Core" Version="6.0.15" /> + <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="6.0.15" /> <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="6.0.15" /> <PackageReference Include="MySqlConnector" Version="2.2.5" /> <PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="6.0.2" /> |