diff options
Diffstat (limited to 'lib')
14 files changed, 955 insertions, 1043 deletions
diff --git a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DBCommandHelpers.cs b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DBCommandHelpers.cs new file mode 100644 index 0000000..b0dd548 --- /dev/null +++ b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DBCommandHelpers.cs @@ -0,0 +1,282 @@ +/* +* Copyright (c) 2024 Vaughn Nugent +* +* Library: VNLib +* Package: VNLib.Plugins.Extensions.Loading.Sql +* File: DBCommandHelpers.cs +* +* DBCommandHelpers.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.Linq; +using System.Collections.Generic; +using System.ComponentModel.DataAnnotations; + +namespace VNLib.Plugins.Extensions.Loading.Sql +{ + /// <summary> + /// Contains helper methods for loading and configuring SQL database connections + /// </summary> + public static class DBCommandHelpers + { + private const string MAX_LEN_BYPASS_KEY = "MaxLen"; + private const string TIMESTAMP_BYPASS = "TimeStamp"; + + 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 <see cref="DbType"/> from the given .NET runtime type information. + /// </summary> + /// <param name="col"></param> + /// <returns>The columns <see cref="DbType"/> or an exception if the type is not supported</returns> + /// <exception cref="NotSupportedException"></exception> + /// <exception cref="ArgumentNullException"></exception> + public static DbType GetDbType(this DataColumn col) + { + ArgumentNullException.ThrowIfNull(col); + ArgumentNullException.ThrowIfNull(col.DataType, nameof(col.DataType)); + + if (!TypeMap.TryGetValue(col.DataType, out DbType dbType)) + { + throw new NotSupportedException($"The type {col.DataType} is not a supporeted database type"); + } + + return dbType; + } + + /// <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) + { + 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; + } + + + /// <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> + /// Gets a value that determines if the current column is a primary key + /// </summary> + /// <param name="col"></param> + /// <returns>True if the collumn is part of the primary keys</returns> + public static bool IsPrimaryKey(this DataColumn col) + { + ArgumentNullException.ThrowIfNull(col); + ArgumentNullException.ThrowIfNull(col.Table, nameof(col.Table)); + return 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; + } + + /// <summary> + /// Gets the max length of the column + /// </summary> + /// <param name="column"></param> + /// <returns></returns> + public 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; + } + + /// <summary> + /// Gets a value that indicates if the column is a timestamp + /// </summary> + /// <param name="column"></param> + /// <returns>True if the column is a timestamp column</returns> + public 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/DatabaseBuilder/Helpers/AbstractDb.cs b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/AbstractDb.cs deleted file mode 100644 index e88820e..0000000 --- a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/AbstractDb.cs +++ /dev/null @@ -1,104 +0,0 @@ -/* -* 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 deleted file mode 100644 index 4fb7f93..0000000 --- a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/MsSqlDb.cs +++ /dev/null @@ -1,170 +0,0 @@ -/* -* 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 deleted file mode 100644 index 6f5552b..0000000 --- a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/MySqlDb.cs +++ /dev/null @@ -1,162 +0,0 @@ -/* -* 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 deleted file mode 100644 index 88ddbcd..0000000 --- a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/SqlLiteDb.cs +++ /dev/null @@ -1,178 +0,0 @@ -/* -* 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 index b362b5e..b063ccd 100644 --- a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/IDBCommandGenerator.cs +++ b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/IDBCommandGenerator.cs @@ -1,5 +1,5 @@ /* -* Copyright (c) 2023 Vaughn Nugent +* Copyright (c) 2024 Vaughn Nugent * * Library: VNLib * Package: VNLib.Plugins.Extensions.Loading.Sql @@ -31,7 +31,7 @@ namespace VNLib.Plugins.Extensions.Loading.Sql.DatabaseBuilder /// <summary> /// Generates specialized statments used to modify a database /// </summary> - interface IDBCommandGenerator + public interface IDBCommandGenerator { /// <summary> /// Compiles a valid database table creation statment from the <see cref="DataTable"/> diff --git a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/IRuntimeDbProvider.cs b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/IRuntimeDbProvider.cs new file mode 100644 index 0000000..f6f8a24 --- /dev/null +++ b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/IRuntimeDbProvider.cs @@ -0,0 +1,59 @@ +/* +* Copyright (c) 2024 Vaughn Nugent +* +* Library: VNLib +* Package: VNLib.Plugins.Extensions.Loading.Sql +* File: IRuntimeDbProvider.cs +* +* IRuntimeDbProvider.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; +using System.Threading.Tasks; + +using Microsoft.EntityFrameworkCore; + +using VNLib.Plugins.Extensions.Loading.Sql.DatabaseBuilder; + +namespace VNLib.Plugins.Extensions.Loading.Sql +{ + /// <summary> + /// Provides a dynamic database provider for the current plugin + /// </summary> + public interface IRuntimeDbProvider + { + /// <summary> + /// Asynchronously gets the <see cref="DbConnection"/> factory for the current plugin + /// </summary> + /// <returns>A task that resolves a new DB factory function</returns> + Task<Func<DbConnection>> GetDbConnectionAsync(); + + /// <summary> + /// Asynchronously gets the <see cref="DbContextOptions"/> instance for + /// the provider's database + /// </summary> + /// <returns>A task that resolves the <see cref="DbContextOptions"/> instance</returns> + Task<DbContextOptions> GetDbOptionsAsync(); + + /// <summary> + /// Gets the command generator for the specific database provider + /// </summary> + /// <returns>A command generator instance build DB specific commands</returns> + IDBCommandGenerator GetCommandGenerator(); + } +} diff --git a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/SqlDbConnectionLoader.cs b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/SqlDbConnectionLoader.cs index 4897b59..58ca934 100644 --- a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/SqlDbConnectionLoader.cs +++ b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/SqlDbConnectionLoader.cs @@ -23,21 +23,16 @@ */ using System; -using System.Linq; using System.Data; using System.Text; using System.Data.Common; using System.Threading.Tasks; using System.Collections.Generic; -using System.ComponentModel.DataAnnotations; using Microsoft.EntityFrameworkCore; using VNLib.Utils.Logging; -using VNLib.Utils.Resources; -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 { @@ -49,10 +44,7 @@ namespace VNLib.Plugins.Extensions.Loading.Sql { public const string SQL_CONFIG_KEY = "sql"; public const string DB_PASSWORD_KEY = "db_password"; - public const string SQL_PROVIDER_DLL_KEY = "provider"; - - private const string MAX_LEN_BYPASS_KEY = "MaxLen"; - private const string TIMESTAMP_BYPASS = "TimeStamp"; + public const string SQL_PROVIDER_DLL_KEY = "provider"; /// <summary> @@ -82,15 +74,17 @@ namespace VNLib.Plugins.Extensions.Loading.Sql /// <exception cref="ObjectDisposedException"></exception> public static IAsyncLazy<Func<DbConnection>> GetConnectionFactoryAsync(this PluginBase plugin) { - plugin.ThrowIfUnloaded(); - - //Get the provider singleton - DbProvider provider = LoadingExtensions.GetOrCreateSingleton(plugin, GetDbPovider); + IRuntimeDbProvider provider = plugin.GetDbProvider(); + return provider.GetDbConnectionAsync().AsLazy(); + } - return provider.ConnectionFactory.Value.AsLazy(); + private static IRuntimeDbProvider GetDbProvider(this PluginBase plugin) + { + plugin.ThrowIfUnloaded(); + return LoadingExtensions.GetOrCreateSingleton(plugin, LoadDbProvider); } - private static DbProvider GetDbPovider(PluginBase plugin) + private static IRuntimeDbProvider LoadDbProvider(PluginBase plugin) { //Get the sql configuration scope IConfigScope sqlConf = plugin.GetConfig(SQL_CONFIG_KEY); @@ -103,9 +97,7 @@ namespace VNLib.Plugins.Extensions.Loading.Sql * insead of forcing a shared interface. This allows the external library to be * more flexible and slimmer. */ - object instance = plugin.CreateServiceExternal<object>(dllPath); - - return new(instance, sqlConf); + return plugin.CreateServiceExternal<IRuntimeDbProvider>(dllPath); } /// <summary> @@ -137,12 +129,8 @@ namespace VNLib.Plugins.Extensions.Loading.Sql /// <remarks>If plugin is in debug mode, writes log data to the default log</remarks> public static IAsyncLazy<DbContextOptions> GetContextOptionsAsync(this PluginBase plugin) { - plugin.ThrowIfUnloaded(); - - //Get the provider singleton - DbProvider provider = LoadingExtensions.GetOrCreateSingleton(plugin, GetDbPovider); - - return provider.OptionsFactory.Value.AsLazy(); + IRuntimeDbProvider provider = plugin.GetDbProvider(); + return provider.GetDbOptionsAsync().AsLazy(); } /// <summary> @@ -179,10 +167,11 @@ namespace VNLib.Plugins.Extensions.Loading.Sql dbCreator.OnDatabaseCreating(builder, state); //Get the abstract database from the connection type - IDBCommandGenerator cb = GetCmdGenerator(plugin); + IRuntimeDbProvider dbp = plugin.GetDbProvider(); + IDBCommandGenerator cb = dbp.GetCommandGenerator(); //Wait for the connection factory to load - Func<DbConnection> dbConFactory = await GetConnectionFactoryAsync(plugin); + Func<DbConnection> dbConFactory = await dbp.GetDbConnectionAsync(); //Create a new db connection await using DbConnection connection = dbConFactory(); @@ -221,299 +210,5 @@ namespace VNLib.Plugins.Extensions.Loading.Sql //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 GetCmdGenerator(PluginBase plugin) - { - //Get the provider singleton - DbProvider provider = LoadingExtensions.GetOrCreateSingleton(plugin, GetDbPovider); - - //See if the provider has a command builder function, otherwise try to use known defaults - if (provider.HasCommandBuilder) - { - return provider.CommandGenerator; - } - else if (string.Equals(provider.ProviderName, "sqlserver", StringComparison.OrdinalIgnoreCase)) - { - return new MsSqlDb(); - } - else if (string.Equals(provider.ProviderName, "mysql", StringComparison.OrdinalIgnoreCase)) - { - return new MySqlDb(); - } - else if (string.Equals(provider.ProviderName, "sqlite", StringComparison.OrdinalIgnoreCase)) - { - return new SqlLiteDb(); - } - 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(); - } - - internal sealed class DbProvider(object instance, IConfigScope sqlConfig) - { - public delegate Task<Func<DbConnection>> AsynConBuilderDelegate(IConfigScope sqlConf); - public delegate Func<DbConnection> SyncConBuilderDelegate(IConfigScope sqlConf); - public delegate DbContextOptions SyncOptBuilderDelegate(IConfigScope sqlConf); - public delegate Task<DbContextOptions> AsynOptBuilderDelegate(IConfigScope sqlConf); - public delegate void BuildTableStringDelegate(StringBuilder builder, DataTable table); - public delegate string ProviderNameDelegate(); - - - public object Provider { get; } = instance; - - public IConfigScope SqlConfig { get; } = sqlConfig; - - /// <summary> - /// A lazy async connection factory. When called, may cause invocation in the external library, - /// but only once. - /// </summary> - public readonly Lazy<Task<Func<DbConnection>>> ConnectionFactory = new(() => GetConnections(instance, sqlConfig)); - - /// <summary> - /// A lazy async options factory. When called, may cause invocation in the external library, - /// but only once. - /// </summary> - public readonly Lazy<Task<DbContextOptions>> OptionsFactory = new(() => GetOptions(instance, sqlConfig)); - - /// <summary> - /// Gets the extern command generator for the external library - /// </summary> - public readonly IDBCommandGenerator CommandGenerator = new ExternCommandGenerator(instance); - - /// <summary> - /// Gets the provider name from the external library - /// </summary> - public readonly ProviderNameDelegate ProviderNameFunc = ManagedLibrary.GetMethod<ProviderNameDelegate>(instance, "GetProviderName"); - - /// <summary> - /// Gets a value indicating if the external library has a command builder - /// </summary> - public bool HasCommandBuilder => (CommandGenerator as ExternCommandGenerator)!.BuildTableString is not null; - - /// <summary> - /// Gets the provider name from the external library - /// </summary> - public string ProviderName => ProviderNameFunc.Invoke(); - - /* - * Methods below are designed to be called within a lazy/defered context and possible awaited - * by mutliple threads. This causes data to be only loaded once, and then cached for future calls. - */ - - private static Task<Func<DbConnection>> GetConnections(object instance, IConfigScope sqlConfig) - { - //Connection builder functions - SyncConBuilderDelegate? SyncBuilder = ManagedLibrary.TryGetMethod<SyncConBuilderDelegate>(instance, "GetDbConnection"); - - //try sync first - if (SyncBuilder is not null) - { - return Task.FromResult(SyncBuilder.Invoke(sqlConfig)); - } - - //If no sync function force call async, but try to schedule it on a new thread - AsynConBuilderDelegate? AsynConnectionBuilder = ManagedLibrary.GetMethod<AsynConBuilderDelegate>(instance, "GetDbConnectionAsync"); - return Task.Run(() => AsynConnectionBuilder.Invoke(sqlConfig)); - } - - private static Task<DbContextOptions> GetOptions(object instance, IConfigScope sqlConfig) - { - //Options builder functions - SyncOptBuilderDelegate? SyncBuilder = ManagedLibrary.TryGetMethod<SyncOptBuilderDelegate>(instance, "GetDbOptions"); - - //try sync first - if (SyncBuilder is not null) - { - return Task.FromResult(SyncBuilder.Invoke(sqlConfig)); - } - - //If no sync function force call async, but try to schedule it on a new thread - AsynOptBuilderDelegate? AsynOptionsBuilder = ManagedLibrary.GetMethod<AsynOptBuilderDelegate>(instance, "GetDbOptionsAsync"); - return Task.Run(() => AsynOptionsBuilder.Invoke(sqlConfig)); - } - - private sealed class ExternCommandGenerator(object instance) : IDBCommandGenerator - { - public BuildTableStringDelegate? BuildTableString = ManagedLibrary.TryGetMethod<BuildTableStringDelegate>(instance, "BuildCreateStatment"); - - - public void BuildCreateStatment(StringBuilder builder, DataTable table) - { - if(BuildTableString is not null) - { - BuildTableString.Invoke(builder, table); - } - else - { - throw new NotSupportedException("The external library does not support table creation"); - } - } - } - } } } diff --git a/lib/sql-providers/mysql/VNLib.Plugins.Extensions.Loading.Sql.MySql/src/MYSqlExport.cs b/lib/sql-providers/mysql/VNLib.Plugins.Extensions.Loading.Sql.MySql/src/MYSqlExport.cs index 26fd3a3..efe5410 100644 --- a/lib/sql-providers/mysql/VNLib.Plugins.Extensions.Loading.Sql.MySql/src/MYSqlExport.cs +++ b/lib/sql-providers/mysql/VNLib.Plugins.Extensions.Loading.Sql.MySql/src/MYSqlExport.cs @@ -23,29 +23,45 @@ */ using System; +using System.Data; +using System.Linq; +using System.Text; using System.Text.Json; using System.Data.Common; using System.Threading.Tasks; using Microsoft.EntityFrameworkCore; -using MySql.Data.MySqlClient; +using MySqlConnector; using VNLib.Utils.Logging; using VNLib.Plugins.Extensions.Loading; +using VNLib.Plugins.Extensions.Loading.Sql; +using VNLib.Plugins.Extensions.Loading.Sql.DatabaseBuilder; namespace VNLib.Plugins.Extensions.Sql { [ServiceExport] [ConfigurationName("sql", Required = true)] - public sealed class MySqlExport(PluginBase plugin, IConfigScope config) + public sealed class MySQLExport(PluginBase plugin, IConfigScope config) : IRuntimeDbProvider { private async Task<string> BuildConnStringAsync() { + MySqlConnectionStringBuilder sb; + //See if the user suggested a raw connection string if (config.TryGetProperty("connection_string", ps => ps.GetString(), out string? conString)) { + sb = new(conString); + + //If the user did not provide a password, try to get it from secret storage + if (string.IsNullOrWhiteSpace(sb.Password)) + { + using ISecretResult? password = await plugin.TryGetSecretAsync("db_password"); + sb.Password = password?.Result.ToString(); + } + return conString!; } else if (config.TryGetValue("json", out JsonElement value)) @@ -57,21 +73,18 @@ namespace VNLib.Plugins.Extensions.Sql DictionaryKeyPolicy = JsonNamingPolicy.SnakeCaseLower, }; - MySqlConnectionStringBuilder b = value.Deserialize<MySqlConnectionStringBuilder>(opt)!; + sb = value.Deserialize<MySqlConnectionStringBuilder>(opt)!; - //Get the password from the secret manager + //Get the db password from the secret manager using ISecretResult? secret = await plugin.TryGetSecretAsync("db_password"); - - b.Password = secret?.Result.ToString(); - return b.ConnectionString; + sb.Password = secret?.Result.ToString(); } else { //Get the password from the secret manager using ISecretResult? secret = await plugin.TryGetSecretAsync("db_password"); - // Build connection strin - return new MySqlConnectionStringBuilder() + sb = new() { Server = config["hostname"].GetString(), Database = config["catalog"].GetString(), @@ -79,36 +92,56 @@ namespace VNLib.Plugins.Extensions.Sql Pooling = true, MinimumPoolSize = config.GetValueOrDefault("min_pool_size", p => p.GetUInt32(), 10u), MaximumPoolSize = config.GetValueOrDefault("max_pool_size", p => p.GetUInt32(), 50u), - AllowBatch = config.GetValueOrDefault("allow_batch", p => p.GetBoolean(), true), - ConnectionLifeTime = config.GetValueOrDefault("connection_lifetime", p => p.GetUInt32(), 0u), - ConnectionTimeout = config.GetValueOrDefault("connection_timeout", p => p.GetUInt32(), 15u), - Port = config.GetValueOrDefault("port", p => p.GetUInt32(), 3306u), - PipeName = config.GetValueOrDefault("pipe_name", p => p.GetString(), null), - AllowLoadLocalInfile = config.GetValueOrDefault("allow_load_local_infile", p => p.GetBoolean(), false), - AllowLoadLocalInfileInPath = config.GetValueOrDefault("allow_load_local_infile_in_path", p => p.GetString(), null), - Password = secret?.Result.ToString(), + }; + + if (config.TryGetProperty("port", p => p.GetUInt16(), out ushort port)) + { + sb.Port = port; + } + + if (config.TryGetProperty("ssl_mode", p => p.GetString(), out string? sslMode) + && Enum.TryParse(sslMode, true, out MySqlSslMode mode)) + { + sb.SslMode = mode; + } + + if (config.TryGetProperty("connection_lifetime", value => value.GetUInt32(), out uint connLife)) + { + sb.ConnectionLifeTime = connLife; + } + + if (config.TryGetProperty("connection_timeout", value => value.GetUInt32(), out uint connTimeout)) + { + sb.ConnectionTimeout = connTimeout; + } + + if (config.TryGetProperty("pipe_name", value => value.GetString(), out string? pipeName)) + { + sb.PipeName = pipeName; + } + + if (config.TryGetProperty("allow_load_local_infile", value => value.GetBoolean(), out bool allowLoadLocalInfile)) + { + sb.AllowLoadLocalInfile = allowLoadLocalInfile; + } + + if (config.TryGetProperty("default_command_timeout", value => value.GetUInt32(), out uint defaultCommandTimeout)) + { + sb.DefaultCommandTimeout = defaultCommandTimeout; + } + + if (config.TryGetProperty("interactive_session", value => value.GetBoolean(), out bool interactiveSession)) + { + sb.InteractiveSession = interactiveSession; } - .ConnectionString; } + + return sb.ConnectionString; } - /* - * NOTICE: - * Function names must be public and must match the SqlConnectionLoader delegate names. - * - * GetDbConnection - A sync or async function that takes a configuration scope and - * returns a DbConnection factory - * - * GetDbOptions - A sync or async function that takes a configuration scope and - * returns a DbConnectionOptions instance - * - * GetProviderName - Returns a string that is the provider name for the connection - */ - - public string GetProviderName() => "mysql"; - - public async Task<Func<DbConnection>> GetDbConnectionAsync(IConfigScope sqlConfig) + ///<inheritdoc/> + public async Task<Func<DbConnection>> GetDbConnectionAsync() { //Store local copy of the connection string, probably not the best idea because of the password, but best for now string connString = await BuildConnStringAsync(); @@ -116,17 +149,18 @@ namespace VNLib.Plugins.Extensions.Sql return () => new MySqlConnection(connString); } - public async Task<DbContextOptions> GetDbOptionsAsync(IConfigScope sqlConfig) + ///<inheritdoc/> + public async Task<DbContextOptions> GetDbOptionsAsync() { //Get the connection string from the configuration string connString = await BuildConnStringAsync(); //Build the options using the mysql extension method - DbContextOptionsBuilder b = new(); - b.UseMySQL(connString); + DbContextOptionsBuilder b = new();; + b.UseMySql(connString, ServerVersion.AutoDetect(connString)); //Write debug loggin to the debug log if the user has it enabled or the plugin is in debug mode - if (sqlConfig.GetValueOrDefault("debug", p => p.GetBoolean(), false) || plugin.IsDebug()) + if (config.GetValueOrDefault("debug", p => p.GetBoolean(), false) || plugin.IsDebug()) { //Write the SQL to the debug log b.LogTo((v) => plugin.Log.Debug("MySql: {v}", v)); @@ -134,5 +168,165 @@ namespace VNLib.Plugins.Extensions.Sql return b.Options; } + + ///<inheritdoc/> + public IDBCommandGenerator GetCommandGenerator() => new MsSqlDb(); + + internal class MsSqlDb : IDBCommandGenerator + { + const int MAX_VARIABLE_SIZE = 8000; + + ///<inheritdoc/> + public 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 = GetTypeStringFromDbType(col); + } + + builder.Append('`') + .Append(col.ColumnName) + .Append("` ") + .Append(dbType); + + //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); + + AddConstraints(builder, table); + + //Close the create table command + builder.AppendLine(")"); + + //Replaced the table name variables + builder.Replace("@tableName", table.TableName); + } + + private static void AddConstraints(StringBuilder builder, DataTable table) + { + DataColumn[] primaryKeys = table.Columns.OfType<DataColumn>() + .Where(static c => c.IsPrimaryKey()) + .ToArray(); + + if (primaryKeys.Length > 0) + { + builder.AppendLine(",") + .Append("CONSTRAINT ") + .Append(table.TableName) + .Append("_pk PRIMARY KEY (") + .AppendJoin(", ", primaryKeys.Select(static c => c.ColumnName)) + .Append(')'); + } + + //Repeat for unique constraints + DataColumn[] uniqueKeys = table.Columns.OfType<DataColumn>() + .Where(static c => c.Unique && !c.IsPrimaryKey()) + .ToArray(); + + if (uniqueKeys.Length > 0) + { + builder.AppendLine(",") + .Append("CONSTRAINT ") + .Append(table.TableName) + .Append("_unique UNIQUE (") + .AppendJoin(", ", uniqueKeys.Select(static c => c.ColumnName)) + .Append(')'); + } + + builder.AppendLine(); + } + + ///<inheritdoc/> + private static string GetTypeStringFromDbType(DataColumn col) + { + return col.GetDbType() 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/sql-providers/mysql/VNLib.Plugins.Extensions.Loading.Sql.MySql/src/VNLib.Plugins.Extensions.Loading.Sql.MYSql.csproj b/lib/sql-providers/mysql/VNLib.Plugins.Extensions.Loading.Sql.MySql/src/VNLib.Plugins.Extensions.Loading.Sql.MYSql.csproj index dcb4a5c..f9d1ed0 100644 --- a/lib/sql-providers/mysql/VNLib.Plugins.Extensions.Loading.Sql.MySql/src/VNLib.Plugins.Extensions.Loading.Sql.MYSql.csproj +++ b/lib/sql-providers/mysql/VNLib.Plugins.Extensions.Loading.Sql.MySql/src/VNLib.Plugins.Extensions.Loading.Sql.MYSql.csproj @@ -2,7 +2,7 @@ <PropertyGroup> <TargetFramework>net8.0</TargetFramework> - <AssemblyName>VNLib.Plugins.Extensions.Sql.MySql</AssemblyName> + <AssemblyName>VNLib.Plugins.Extensions.Sql.MySQL</AssemblyName> <RootNamespace>VNLib.Plugins.Extensions.Sql</RootNamespace> <Nullable>enable</Nullable> <ImplicitUsings>disable</ImplicitUsings> @@ -12,14 +12,14 @@ </PropertyGroup> <PropertyGroup> - <PackageId>VNLib.Plugins.Extensions.Sql.MySql</PackageId> + <PackageId>VNLib.Plugins.Extensions.Sql.MySQL</PackageId> <Authors>Vaughn Nugent</Authors> <Company>Vaughn Nugent</Company> - <Product>VNLib.Plugins.Extensions.Sql.MySql</Product> + <Product>VNLib.Plugins.Extensions.Sql.MySQL</Product> <Copyright>Copyright © 2024 Vaughn Nugent</Copyright> <PackageProjectUrl>https://www.vaughnnugent.com/resources/software/modules/VNLib.Plugins.Extensions</PackageProjectUrl> - <RepositoryUrl>https://github.com/VnUgE/VNLib.Plugins.Extensions/tree/master/lib/sql-providers/VNLib.Plugins.Extensions.Sql.MySql</RepositoryUrl> - <Description>A runtime asset library that provides MySql interfaces for ADO and EFCore SQL server clients</Description> + <RepositoryUrl>https://github.com/VnUgE/VNLib.Plugins.Extensions/tree/master/lib/sql-providers/VNLib.Plugins.Extensions.Sql.MySQL</RepositoryUrl> + <Description>A runtime asset library that provides MySQL interfaces for ADO and EFCore SQL server clients</Description> <PackageReadmeFile>README.md</PackageReadmeFile> <PackageLicenseFile>LICENSE</PackageLicenseFile> </PropertyGroup> @@ -45,11 +45,11 @@ <PrivateAssets>all</PrivateAssets> <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets> </PackageReference> - <PackageReference Include="MySql.EntityFrameworkCore" Version="8.0.0" /> + <PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="8.0.0" /> </ItemGroup> <ItemGroup> - <ProjectReference Include="..\..\..\..\VNLib.Plugins.Extensions.Loading\src\VNLib.Plugins.Extensions.Loading.csproj" /> + <ProjectReference Include="..\..\..\..\VNLib.Plugins.Extensions.Loading.Sql\src\VNLib.Plugins.Extensions.Loading.Sql.csproj" /> </ItemGroup> <Target Condition="'$(BuildingInsideVisualStudio)' == true" Name="PostBuild" AfterTargets="PostBuildEvent"> diff --git a/lib/sql-providers/sqlite/VNLib.Plugins.Extensions.Loading.Sql.SQLite/src/SQLiteExport.cs b/lib/sql-providers/sqlite/VNLib.Plugins.Extensions.Loading.Sql.SQLite/src/SQLiteExport.cs index 6f9455a..ff74051 100644 --- a/lib/sql-providers/sqlite/VNLib.Plugins.Extensions.Loading.Sql.SQLite/src/SQLiteExport.cs +++ b/lib/sql-providers/sqlite/VNLib.Plugins.Extensions.Loading.Sql.SQLite/src/SQLiteExport.cs @@ -23,9 +23,12 @@ */ using System; +using System.Data; +using System.Text; using System.Text.Json; using System.Data.Common; using System.Threading.Tasks; +using System.Collections.Generic; using Microsoft.EntityFrameworkCore; @@ -33,21 +36,32 @@ using Microsoft.Data.Sqlite; using VNLib.Utils.Logging; using VNLib.Plugins.Extensions.Loading; +using VNLib.Plugins.Extensions.Loading.Sql; +using VNLib.Plugins.Extensions.Loading.Sql.DatabaseBuilder; namespace VNLib.Plugins.Extensions.Sql { [ServiceExport] [ConfigurationName("sql", Required = true)] - public sealed class SQLiteExport(PluginBase plugin, IConfigScope config) + public sealed class SQLiteExport(PluginBase plugin, IConfigScope config) : IRuntimeDbProvider { private async Task<string> BuildConnStringAsync() { + SqliteConnectionStringBuilder sb; + //See if the user suggested a raw connection string if (config.TryGetProperty("connection_string", ps => ps.GetString(), out string? conString)) { - return conString!; + sb = new(conString); + + //If the user did not provide a password, try to get it from secret storage + if (string.IsNullOrWhiteSpace(sb.Password)) + { + using ISecretResult? password = await plugin.TryGetSecretAsync("db_password"); + sb.Password = password?.Result.ToString(); + } } else if (config.TryGetValue("json", out JsonElement value)) { @@ -58,13 +72,11 @@ namespace VNLib.Plugins.Extensions.Sql DictionaryKeyPolicy = JsonNamingPolicy.SnakeCaseLower, }; - SqliteConnectionStringBuilder b = value.Deserialize<SqliteConnectionStringBuilder>(opt)!; + sb = value.Deserialize<SqliteConnectionStringBuilder>(opt)!; //Get the password from the secret manager using ISecretResult? secret = await plugin.TryGetSecretAsync("db_password"); - - b.Password = secret?.Result.ToString(); - return b.ConnectionString; + sb.Password = secret?.Result.ToString(); } else { @@ -72,7 +84,7 @@ namespace VNLib.Plugins.Extensions.Sql using ISecretResult? secret = await plugin.TryGetSecretAsync("db_password"); // Build connection strin - return new SqliteConnectionStringBuilder() + sb = new() { DataSource = config["source"].GetString(), Pooling = true, @@ -82,35 +94,23 @@ namespace VNLib.Plugins.Extensions.Sql Mode = config.GetValueOrDefault("mode", p => (SqliteOpenMode)p.GetInt32(), SqliteOpenMode.ReadWriteCreate), Password = secret?.Result.ToString(), - } - .ConnectionString; + }; } + + return sb.ConnectionString; } - - /* - * NOTICE: - * Function names must be public and must match the SqlConnectionLoader delegate names. - * - * GetDbConnection - A sync or async function that takes a configuration scope and - * returns a DbConnection factory - * - * GetDbOptions - A sync or async function that takes a configuration scope and - * returns a DbConnectionOptions instance - * - * GetProviderName - Returns a string that is the provider name for the connection - */ - - public string GetProviderName() => "sqlite"; //Use default handler for sqlite db creation - - public async Task<Func<DbConnection>> GetDbConnectionAsync(IConfigScope sqlConfig) - { + + /// <inheritdoc/> + public async Task<Func<DbConnection>> GetDbConnectionAsync() + { //Store local copy of the connection string, probably not the best idea because of the password, but best for now string connString = await BuildConnStringAsync(); return () => new SqliteConnection(connString); } - public async Task<DbContextOptions> GetDbOptionsAsync(IConfigScope sqlConfig) + /// <inheritdoc/> + public async Task<DbContextOptions> GetDbOptionsAsync() { //Get the connection string from the configuration string connString = await BuildConnStringAsync(); @@ -119,7 +119,7 @@ namespace VNLib.Plugins.Extensions.Sql b.UseSqlite(connString); //Write debug loggin to the debug log if the user has it enabled or the plugin is in debug mode - if (sqlConfig.GetValueOrDefault("debug", p => p.GetBoolean(), false) || plugin.IsDebug()) + if (config.GetValueOrDefault("debug", p => p.GetBoolean(), false) || plugin.IsDebug()) { //Write the SQL to the debug log b.LogTo((v) => plugin.Log.Debug("SQLite: {v}", v)); @@ -127,5 +127,157 @@ namespace VNLib.Plugins.Extensions.Sql return b.Options; } + + /// <inheritdoc/> + public IDBCommandGenerator GetCommandGenerator() => new SqlLiteDb(); + + /// <inheritdoc/> + public override string ToString() => $"SQLite DB runtime provider : {GetHashCode()}"; + + internal sealed class SqlLiteDb : IDBCommandGenerator + { + public 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 = GetTypeStringFromDbType(col); + } + + 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.Count != 0) + { + 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); + } + + private static string GetTypeStringFromDbType(DataColumn col) + { + return col.GetDbType() 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/sql-providers/sqlite/VNLib.Plugins.Extensions.Loading.Sql.SQLite/src/VNLib.Plugins.Extensions.Loading.Sql.SQLite.csproj b/lib/sql-providers/sqlite/VNLib.Plugins.Extensions.Loading.Sql.SQLite/src/VNLib.Plugins.Extensions.Loading.Sql.SQLite.csproj index b61f523..d9a38fa 100644 --- a/lib/sql-providers/sqlite/VNLib.Plugins.Extensions.Loading.Sql.SQLite/src/VNLib.Plugins.Extensions.Loading.Sql.SQLite.csproj +++ b/lib/sql-providers/sqlite/VNLib.Plugins.Extensions.Loading.Sql.SQLite/src/VNLib.Plugins.Extensions.Loading.Sql.SQLite.csproj @@ -48,8 +48,8 @@ <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="8.0.1" /> </ItemGroup> - <ItemGroup> - <ProjectReference Include="..\..\..\..\VNLib.Plugins.Extensions.Loading\src\VNLib.Plugins.Extensions.Loading.csproj" /> + <ItemGroup> + <ProjectReference Include="..\..\..\..\VNLib.Plugins.Extensions.Loading.Sql\src\VNLib.Plugins.Extensions.Loading.Sql.csproj" /> </ItemGroup> <Target Condition="'$(BuildingInsideVisualStudio)' == true" Name="PostBuild" AfterTargets="PostBuildEvent"> diff --git a/lib/sql-providers/sqlserver/VNLib.Plugins.Extensions.Loading.Sql.SQLServer/src/SqlServerExport.cs b/lib/sql-providers/sqlserver/VNLib.Plugins.Extensions.Loading.Sql.SQLServer/src/SqlServerExport.cs index 71f16bf..9f503ed 100644 --- a/lib/sql-providers/sqlserver/VNLib.Plugins.Extensions.Loading.Sql.SQLServer/src/SqlServerExport.cs +++ b/lib/sql-providers/sqlserver/VNLib.Plugins.Extensions.Loading.Sql.SQLServer/src/SqlServerExport.cs @@ -23,6 +23,8 @@ */ using System; +using System.Data; +using System.Text; using System.Text.Json; using System.Data.Common; using System.Threading.Tasks; @@ -33,20 +35,31 @@ using Microsoft.EntityFrameworkCore; using VNLib.Utils.Logging; using VNLib.Plugins.Extensions.Loading; +using VNLib.Plugins.Extensions.Loading.Sql; +using VNLib.Plugins.Extensions.Loading.Sql.DatabaseBuilder; namespace VNLib.Plugins.Extensions.Sql { [ServiceExport] [ConfigurationName("sql", Required = true)] - public sealed class SqlServerExport(PluginBase plugin, IConfigScope config) + public sealed class SqlServerExport(PluginBase plugin, IConfigScope config) : IRuntimeDbProvider { private async Task<string> BuildConnStringAsync() { + SqlConnectionStringBuilder sb; + //See if the user suggested a raw connection string if (config.TryGetProperty("connection_string", ps => ps.GetString(), out string? conString)) { - return conString!; + sb = new(conString); + + //If the user did not provide a password, try to get it from secret storage + if (string.IsNullOrWhiteSpace(sb.Password)) + { + using ISecretResult? password = await plugin.TryGetSecretAsync("db_password"); + sb.Password = password?.Result.ToString(); + } } else if (config.TryGetValue("json", out JsonElement value)) { @@ -57,13 +70,12 @@ namespace VNLib.Plugins.Extensions.Sql DictionaryKeyPolicy = JsonNamingPolicy.SnakeCaseLower, }; - SqlConnectionStringBuilder b = value.Deserialize<SqlConnectionStringBuilder>(opt)!; + //try to get the connection string from the json serialzied object directly + sb = value.Deserialize<SqlConnectionStringBuilder>(opt)!; //Get the password from the secret manager using ISecretResult? secret = await plugin.TryGetSecretAsync("db_password"); - - b.Password = secret?.Result.ToString(); - return b.ConnectionString; + sb.Password = secret?.Result.ToString(); } else { @@ -71,7 +83,7 @@ namespace VNLib.Plugins.Extensions.Sql using ISecretResult? secret = await plugin.TryGetSecretAsync("db_password"); // Build connection string - return new SqlConnectionStringBuilder() + sb = new() { DataSource = config["hostname"].GetString(), InitialCatalog = config["catalog"].GetString(), @@ -99,35 +111,22 @@ namespace VNLib.Plugins.Extensions.Sql UserInstance = config.GetValueOrDefault("user_instance", p => p.GetBoolean(), false), Password = secret?.Result.ToString(), - } - .ConnectionString; + }; } - } - /* - * NOTICE: - * Function names must be public and must match the SqlConnectionLoader delegate names. - * - * GetDbConnection - A sync or async function that takes a configuration scope and - * returns a DbConnection factory - * - * GetDbOptions - A sync or async function that takes a configuration scope and - * returns a DbConnectionOptions instance - * - * GetProviderName - Returns a string that is the provider name for the connection - */ - - public string GetProviderName() => "sqlserver"; - - public async Task<Func<DbConnection>> GetDbConnectionAsync(IConfigScope sqlConfig) + return sb.ConnectionString; + } + + ///<inheritdoc/> + public async Task<Func<DbConnection>> GetDbConnectionAsync() { //Store local copy of the connection string, probably not the best idea because of the password, but best for now string connString = await BuildConnStringAsync(); - return () => new SqlConnection(connString); } - public async Task<DbContextOptions> GetDbOptionsAsync(IConfigScope sqlConfig) + ///<inheritdoc/> + public async Task<DbContextOptions> GetDbOptionsAsync() { //Get the connection string from the configuration string connString = await BuildConnStringAsync(); @@ -137,7 +136,7 @@ namespace VNLib.Plugins.Extensions.Sql b.UseSqlServer(connString); //Write debug loggin to the debug log if the user has it enabled or the plugin is in debug mode - if (sqlConfig.GetValueOrDefault("debug", p => p.GetBoolean(), false) || plugin.IsDebug()) + if (config.GetValueOrDefault("debug", p => p.GetBoolean(), false) || plugin.IsDebug()) { //Write the SQL to the debug log b.LogTo((v) => plugin.Log.Debug("SqlServer: {v}", v)); @@ -145,5 +144,149 @@ namespace VNLib.Plugins.Extensions.Sql return b.Options; } + + ///<inheritdoc/> + public IDBCommandGenerator GetCommandGenerator() => new MsSqlDb(); + + + internal class MsSqlDb : IDBCommandGenerator + { + const int MAX_VARIABLE_SIZE = 8000; + + ///<inheritdoc/> + public 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 = GetTypeStringFromDbType(col); + } + + 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/> + private static string GetTypeStringFromDbType(DataColumn col) + { + return col.GetDbType() 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/sql-providers/sqlserver/VNLib.Plugins.Extensions.Loading.Sql.SQLServer/src/VNLib.Plugins.Extensions.Loading.Sql.SQLServer.csproj b/lib/sql-providers/sqlserver/VNLib.Plugins.Extensions.Loading.Sql.SQLServer/src/VNLib.Plugins.Extensions.Loading.Sql.SQLServer.csproj index fea8eee..5b611ca 100644 --- a/lib/sql-providers/sqlserver/VNLib.Plugins.Extensions.Loading.Sql.SQLServer/src/VNLib.Plugins.Extensions.Loading.Sql.SQLServer.csproj +++ b/lib/sql-providers/sqlserver/VNLib.Plugins.Extensions.Loading.Sql.SQLServer/src/VNLib.Plugins.Extensions.Loading.Sql.SQLServer.csproj @@ -46,10 +46,11 @@ <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets> </PackageReference> <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="8.0.1" /> + </ItemGroup> - <ItemGroup> - <ProjectReference Include="..\..\..\..\VNLib.Plugins.Extensions.Loading\src\VNLib.Plugins.Extensions.Loading.csproj" /> + <ItemGroup> + <ProjectReference Include="..\..\..\..\VNLib.Plugins.Extensions.Loading.Sql\src\VNLib.Plugins.Extensions.Loading.Sql.csproj" /> </ItemGroup> <Target Condition="'$(BuildingInsideVisualStudio)' == true" Name="PostBuild" AfterTargets="PostBuildEvent"> |