diff options
Diffstat (limited to 'lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder')
5 files changed, 2 insertions, 616 deletions
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"/> |