Database creation helpers w/ fluent api

7 files changed, 741 insertions, 0 deletions

diff --git a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/DbCreationException.cs b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/DbCreationException.cs
new file mode 100644
index 0000000..c7825b0
--- /dev/null
+++ b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/DbCreationException.cs
@@ -0,0 +1,44 @@
+/*
+* Copyright (c) 2023 Vaughn Nugent
+* 
+* Library: VNLib
+* Package: VNLib.Plugins.Extensions.Loading.Sql
+* File: DbCreationException.cs 
+*
+* DbCreationException.cs is part of VNLib.Plugins.Extensions.Loading.Sql which 
+* is part of the larger VNLib collection of libraries and utilities.
+*
+* VNLib.Plugins.Extensions.Loading.Sql is free software: you can redistribute it and/or modify 
+* it under the terms of the GNU Affero General Public License as 
+* published by the Free Software Foundation, either version 3 of the
+* License, or (at your option) any later version.
+*
+* VNLib.Plugins.Extensions.Loading.Sql is distributed in the hope that it will be useful,
+* but WITHOUT ANY WARRANTY; without even the implied warranty of
+* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+* GNU Affero General Public License for more details.
+*
+* You should have received a copy of the GNU Affero General Public License
+* along with this program.  If not, see https://www.gnu.org/licenses/.
+*/
+
+using System;
+using System.Data.Common;
+
+namespace VNLib.Plugins.Extensions.Loading.Sql.DatabaseBuilder
+{
+    class DbCreationException : DbException
+    {
+        public DbCreationException()
+        { }
+
+        public DbCreationException(string? message) : base(message)
+        { }
+
+        public DbCreationException(string? message, Exception? innerException) : base(message, innerException)
+        { }
+
+        public DbCreationException(string? message, int errorCode) : base(message, errorCode)
+        { }
+    }
+}
diff --git a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/AbstractDb.cs b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/AbstractDb.cs
new file mode 100644
index 0000000..e88820e
--- /dev/null
+++ b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/AbstractDb.cs
@@ -0,0 +1,104 @@
+/*
+* Copyright (c) 2023 Vaughn Nugent
+* 
+* Library: VNLib
+* Package: VNLib.Plugins.Extensions.Loading.Sql
+* File: AbstractDb.cs 
+*
+* AbstractDb.cs is part of VNLib.Plugins.Extensions.Loading.Sql which 
+* is part of the larger VNLib collection of libraries and utilities.
+*
+* VNLib.Plugins.Extensions.Loading.Sql is free software: you can redistribute it and/or modify 
+* it under the terms of the GNU Affero General Public License as 
+* published by the Free Software Foundation, either version 3 of the
+* License, or (at your option) any later version.
+*
+* VNLib.Plugins.Extensions.Loading.Sql is distributed in the hope that it will be useful,
+* but WITHOUT ANY WARRANTY; without even the implied warranty of
+* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+* GNU Affero General Public License for more details.
+*
+* You should have received a copy of the GNU Affero General Public License
+* along with this program.  If not, see https://www.gnu.org/licenses/.
+*/
+
+using System;
+using System.IO;
+using System.Data;
+using System.Text;
+using System.Collections.Generic;
+
+namespace VNLib.Plugins.Extensions.Loading.Sql.DatabaseBuilder.Helpers
+{
+    internal abstract class AbstractDb : IDBCommandGenerator
+    {
+        private static readonly Dictionary<Type, DbType> TypeMap = new()
+        {
+            [typeof(byte)] = DbType.Byte,
+            [typeof(sbyte)] = DbType.SByte,
+            [typeof(short)] = DbType.Int16,
+            [typeof(ushort)] = DbType.UInt16,
+            [typeof(int)] = DbType.Int32,
+            [typeof(uint)] = DbType.UInt32,
+            [typeof(long)] = DbType.Int64,
+            [typeof(ulong)] = DbType.UInt64,
+            [typeof(float)] = DbType.Single,
+            [typeof(double)] = DbType.Double,
+            [typeof(decimal)] = DbType.Decimal,
+            [typeof(bool)] = DbType.Boolean,
+            [typeof(string)] = DbType.String,
+            [typeof(char)] = DbType.StringFixedLength,
+            [typeof(Guid)] = DbType.Guid,
+            [typeof(DateTime)] = DbType.DateTime,
+            [typeof(DateTimeOffset)] = DbType.DateTimeOffset,
+
+            [typeof(byte[])] = DbType.Binary,
+            [typeof(byte?)] = DbType.Byte,
+            [typeof(sbyte?)] = DbType.SByte,
+            [typeof(short?)] = DbType.Int16,
+            [typeof(ushort?)] = DbType.UInt16,
+            [typeof(int?)] = DbType.Int32,
+            [typeof(uint?)] = DbType.UInt32,
+            [typeof(long?)] = DbType.Int64,
+            [typeof(ulong?)] = DbType.UInt64,
+            [typeof(float?)] = DbType.Single,
+            [typeof(double?)] = DbType.Double,
+            [typeof(decimal?)] = DbType.Decimal,
+            [typeof(bool?)] = DbType.Boolean,
+            [typeof(char?)] = DbType.StringFixedLength,
+            [typeof(Guid?)] = DbType.Guid,
+            [typeof(DateTime?)] = DbType.DateTime,
+            [typeof(DateTimeOffset?)] = DbType.DateTimeOffset,
+            [typeof(Stream)] = DbType.Binary
+        };
+
+        /// <summary>
+        /// Gets the database string type name from the given .NET runtime type 
+        /// information.
+        /// </summary>
+        /// <param name="type">The type to resolve</param>
+        /// <returns>The type string that is realtive to the given database backend</returns>
+        /// <exception cref="DbCreationException"></exception>
+        public string GetTypeStringFromType(Type type)
+        {
+            if(!TypeMap.TryGetValue(type, out DbType dbType))
+            {
+                throw new DbCreationException($"The type {type} is not a supporeted database type");
+            }
+
+            //Get the type string
+            return GetTypeStringFromDbType(dbType);
+        }
+       
+
+        /// <summary>
+        /// Gets a string property value from a discovered <see cref="DbType"/>
+        /// </summary>
+        /// <param name="type">The dbType discovered from the type according to the backing database</param>
+        /// <returns>The parameter type as a string with an optional size variable</returns>
+        protected abstract string GetTypeStringFromDbType(DbType type);
+
+        ///<inheritdoc/>
+        public abstract void BuildCreateStatment(StringBuilder builder, DataTable table);
+    }
+}
diff --git a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/MsSqlDb.cs b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/MsSqlDb.cs
new file mode 100644
index 0000000..4fb7f93
--- /dev/null
+++ b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/MsSqlDb.cs
@@ -0,0 +1,170 @@
+/*
+* Copyright (c) 2023 Vaughn Nugent
+* 
+* Library: VNLib
+* Package: VNLib.Plugins.Extensions.Loading.Sql
+* File: MsSqlDb.cs 
+*
+* MsSqlDb.cs is part of VNLib.Plugins.Extensions.Loading.Sql which is part
+* of the larger VNLib collection of libraries and utilities.
+*
+* VNLib.Plugins.Extensions.Loading.Sql is free software: you can redistribute it and/or modify 
+* it under the terms of the GNU Affero General Public License as 
+* published by the Free Software Foundation, either version 3 of the
+* License, or (at your option) any later version.
+*
+* VNLib.Plugins.Extensions.Loading.Sql is distributed in the hope that it will be useful,
+* but WITHOUT ANY WARRANTY; without even the implied warranty of
+* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+* GNU Affero General Public License for more details.
+*
+* You should have received a copy of the GNU Affero General Public License
+* along with this program.  If not, see https://www.gnu.org/licenses/.
+*/
+
+using System;
+using System.Data;
+using System.Text;
+
+namespace VNLib.Plugins.Extensions.Loading.Sql.DatabaseBuilder.Helpers
+{
+    internal class MsSqlDb : AbstractDb
+    {
+        const int MAX_VARIABLE_SIZE = 8000;
+
+        ///<inheritdoc/>
+        public override void BuildCreateStatment(StringBuilder builder, DataTable table)
+        {
+            builder.AppendLine("IF OBJECT_ID(N'[dbo].[@tableName]', N'U') IS NULL");
+            builder.AppendLine("CREATE TABLE [dbo].[@tableName] (");
+
+            //Add columns
+            foreach(DataColumn col in table.Columns)
+            {
+                //Get dbType string
+                string dbType;
+
+                //Timestamps/rowversion must be handled specially for msSql
+                if (col.IsTimeStamp())
+                {
+                    dbType = "ROWVERSION";
+                }
+                else
+                {
+                    dbType = GetTypeStringFromType(col.DataType);
+                }
+
+                builder.Append('[')
+                    .Append(col.ColumnName)
+                    .Append("] ")
+                    .Append(dbType);
+
+                //Set primary key contraint
+                if (col.IsPrimaryKey())
+                {
+                    builder.Append(" PRIMARY KEY");
+                }
+                //Set unique constraint (only if not pk)
+                else if (col.Unique)
+                {
+                    builder.Append(" UNIQUE");
+                }
+
+                //If the value is not null, we can specify the default value
+                if (!col.AllowDBNull)
+                {
+                    if (!string.IsNullOrWhiteSpace(col.DefaultValue?.ToString()))
+                    {
+                        builder.Append(" DEFAULT ");
+                        builder.Append(col.DefaultValue);
+                    }
+                    else
+                    {
+                        //Set not null 
+                        builder.Append(" NOT NULL");
+                    }
+                }
+
+                //Set auto increment
+                if (col.AutoIncrement)
+                {
+                    builder.Append(" IDENTITY(")
+                        .Append(col.AutoIncrementSeed)
+                        .Append(',')
+                        .Append(col.AutoIncrementStep)
+                        .Append(')');
+                }
+
+                //Trailing comma
+                builder.AppendLine(",");
+
+
+                //Set size if defined
+                if (col.MaxLength() > MAX_VARIABLE_SIZE)
+                {
+                    builder.Replace("@size", "MAX");
+                }
+                else if (col.MaxLength() > 0)
+                {
+                    builder.Replace("@size", col.MaxLength().ToString());
+                }
+                else
+                {
+                    builder.Replace("(@size)", "");
+                }
+            }
+
+            int index = builder.Length;
+            while (builder[--index] != ',')
+            { }
+
+            //Remove the trailing comma
+            builder.Remove(index, 1);
+
+            //Close the create table command
+            builder.AppendLine(")");
+
+            //Replaced the table name variables
+            builder.Replace("@tableName", table.TableName);
+        }
+
+        ///<inheritdoc/>
+        protected override string GetTypeStringFromDbType(DbType type)
+        {
+            return type switch
+            {
+                DbType.AnsiString => "VARCHAR(@size)",
+                DbType.Binary => "VARBINARY(@size)",
+                DbType.Byte => "TINYINT",
+                DbType.Boolean => "BOOL",
+                DbType.Currency => "MONEY",
+                DbType.Date => "DATE",
+                DbType.DateTime => "DATETIME",
+                DbType.Decimal => "DECIMAL",
+                DbType.Double => "DOUBLE",
+                DbType.Guid => "VARCHAR(@size)",
+                DbType.Int16 => "SMALLINT",
+                DbType.Int32 => "INT",
+                DbType.Int64 => "BIGINT",
+                DbType.Object => throw new NotSupportedException("A .NET object type is not a supported MySql data-type"),
+                DbType.SByte => "TINYINT",
+                DbType.Single => "FLOAT",
+                //unicode string support
+                DbType.String => "NVARCHAR(@size)",
+                DbType.Time => "TIME",
+                DbType.UInt16 => "SMALLINT",
+                DbType.UInt32 => "INT",
+                DbType.UInt64 => "BIGINT",
+                DbType.VarNumeric => throw new NotSupportedException("Variable numeric value is not a supported MySql data-type"),
+                DbType.AnsiStringFixedLength => "TEXT(@size)",
+                //unicode text support
+                DbType.StringFixedLength => "NTEXT(@size)",
+                //Define custom xml schema variable
+                DbType.Xml => "XML(@xml_schema_collection)",
+                DbType.DateTime2 => "DATETIME2",
+                DbType.DateTimeOffset => "DATETIMEOFFSET",
+                _ => throw new NotSupportedException("The desired property data-type is not a supported MySql data-type"),
+            };
+        }
+    }
+}
diff --git a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/MySqlDb.cs b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/MySqlDb.cs
new file mode 100644
index 0000000..6f5552b
--- /dev/null
+++ b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/MySqlDb.cs
@@ -0,0 +1,162 @@
+/*
+* Copyright (c) 2023 Vaughn Nugent
+* 
+* Library: VNLib
+* Package: VNLib.Plugins.Extensions.Loading.Sql
+* File: MySqlDb.cs 
+*
+* MySqlDb.cs is part of VNLib.Plugins.Extensions.Loading.Sql which is part of the larger 
+* VNLib collection of libraries and utilities.
+*
+* VNLib.Plugins.Extensions.Loading.Sql is free software: you can redistribute it and/or modify 
+* it under the terms of the GNU Affero General Public License as 
+* published by the Free Software Foundation, either version 3 of the
+* License, or (at your option) any later version.
+*
+* VNLib.Plugins.Extensions.Loading.Sql is distributed in the hope that it will be useful,
+* but WITHOUT ANY WARRANTY; without even the implied warranty of
+* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+* GNU Affero General Public License for more details.
+*
+* You should have received a copy of the GNU Affero General Public License
+* along with this program.  If not, see https://www.gnu.org/licenses/.
+*/
+
+using System;
+using System.Data;
+using System.Text;
+
+namespace VNLib.Plugins.Extensions.Loading.Sql.DatabaseBuilder.Helpers
+{
+    internal sealed class MySqlDb : AbstractDb
+    {
+        const int MAX_VARIABLE_SIZE = 8000;
+
+        ///<inheritdoc/>
+        public override void BuildCreateStatment(StringBuilder builder, DataTable table)
+        {
+            builder.AppendLine("CREATE TABLE IF NOT EXISTS `@tableName` (");
+
+            //Add columns
+            foreach (DataColumn col in table.Columns)
+            {
+                //Get dbType string
+                string dbType;
+
+                //Timestamps/rowversion must be handled specially for MySql optimistic concurrency
+                if (col.IsTimeStamp())
+                {
+                    dbType = "TIMESTAMP";
+                }
+                else
+                {
+                    dbType = GetTypeStringFromType(col.DataType);
+                }
+
+                builder.Append('`')
+                    .Append(col.ColumnName)
+                    .Append("` ")
+                    .Append(dbType);
+
+                //Set primary key contraint
+                if (col.IsPrimaryKey())
+                {
+                    builder.Append(" PRIMARY KEY");
+                }
+                //Set unique constraint (only if not pk)
+                else if (col.Unique)
+                {
+                    builder.Append(" UNIQUE");
+                }
+
+                //If the value is not null, we can specify the default value
+                if (!col.AllowDBNull)
+                {
+                    if (!string.IsNullOrWhiteSpace(col.DefaultValue?.ToString()))
+                    {
+                        builder.Append(" DEFAULT ");
+                        builder.Append(col.DefaultValue);
+                    }
+                    else
+                    {
+                        //Set not null 
+                        builder.Append(" NOT NULL");
+                    }
+                }
+
+                //Set auto increment
+                if (col.AutoIncrement)
+                {
+                    builder.Append(" AUTO_INCREMENT=")
+                        .Append(col.AutoIncrementSeed);
+                }
+
+                //Trailing comma
+                builder.AppendLine(",");
+
+                //Set size if defined, we need to bypass column max length
+                if (col.MaxLength() > MAX_VARIABLE_SIZE)
+                {
+                    builder.Replace("@size", "MAX");
+                }
+                else if(col.MaxLength() > 0)
+                {
+                    builder.Replace("@size", col.MaxLength().ToString());
+                }
+                else
+                {
+                    builder.Replace("(@size)", "");
+                }
+            }
+
+            int index = builder.Length;
+            while (builder[--index] != ',')
+            { }
+
+            //Remove the trailing comma
+            builder.Remove(index, 1);
+
+            //Close the create table command
+            builder.AppendLine(")");
+
+            //Replaced the table name variables
+            builder.Replace("@tableName", table.TableName);
+        }
+
+        ///<inheritdoc/>
+        protected override string GetTypeStringFromDbType(DbType type)
+        {
+            return type switch
+            {
+                DbType.AnsiString => "VARCHAR(@size)",
+                DbType.Binary => "VARBINARY(@size)",
+                DbType.Byte => "TINYINT",
+                DbType.Boolean => "BOOL",
+                DbType.Currency => "DECIMAL",
+                DbType.Date => "DATE",
+                DbType.DateTime => "DATETIME",
+                DbType.Decimal => "DECIMAL",
+                DbType.Double => "DOUBLE",
+                DbType.Guid => "VARCHAR(@size)",
+                DbType.Int16 => "SMALLINT",
+                DbType.Int32 => "INT",
+                DbType.Int64 => "BIGINT",
+                DbType.Object => throw new NotSupportedException("A .NET object type is not a supported MySql data-type"),
+                DbType.SByte => "TINYINT",
+                DbType.Single => "FLOAT",
+                DbType.String => "VARCHAR(@size)",
+                DbType.Time => "TIME",
+                DbType.UInt16 => "SMALLINT",
+                DbType.UInt32 => "INT",
+                DbType.UInt64 => "BIGINT",
+                DbType.VarNumeric => throw new NotSupportedException("Variable numeric value is not a supported MySql data-type"),
+                DbType.AnsiStringFixedLength => "TEXT(@size)",
+                DbType.StringFixedLength => "TEXT(@size)",
+                DbType.Xml => "VARCHAR(@size)",
+                DbType.DateTime2 => "DATETIME",
+                DbType.DateTimeOffset => throw new NotSupportedException("DateTimeOffset is not a supported MySql data-type"),
+                _ => throw new NotSupportedException("The desired property data-type is not a supported MySql data-type"),
+            };
+        }
+    }
+}
diff --git a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/SqlLiteDb.cs b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/SqlLiteDb.cs
new file mode 100644
index 0000000..88ddbcd
--- /dev/null
+++ b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/SqlLiteDb.cs
@@ -0,0 +1,178 @@
+/*
+* Copyright (c) 2023 Vaughn Nugent
+* 
+* Library: VNLib
+* Package: VNLib.Plugins.Extensions.Loading.Sql
+* File: SqlLiteDb.cs 
+*
+* SqlLiteDb.cs is part of VNLib.Plugins.Extensions.Loading.Sql which 
+* is part of the larger VNLib collection of libraries and utilities.
+*
+* VNLib.Plugins.Extensions.Loading.Sql is free software: you can redistribute it and/or modify 
+* it under the terms of the GNU Affero General Public License as 
+* published by the Free Software Foundation, either version 3 of the
+* License, or (at your option) any later version.
+*
+* VNLib.Plugins.Extensions.Loading.Sql is distributed in the hope that it will be useful,
+* but WITHOUT ANY WARRANTY; without even the implied warranty of
+* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+* GNU Affero General Public License for more details.
+*
+* You should have received a copy of the GNU Affero General Public License
+* along with this program.  If not, see https://www.gnu.org/licenses/.
+*/
+
+using System;
+using System.Data;
+using System.Linq;
+using System.Text;
+using System.Collections.Generic;
+
+namespace VNLib.Plugins.Extensions.Loading.Sql.DatabaseBuilder.Helpers
+{
+    internal class SqlLiteDb : AbstractDb
+    {
+        public override void BuildCreateStatment(StringBuilder builder, DataTable table)
+        {
+            builder.AppendLine("CREATE TABLE IF NOT EXISTS @tableName (");
+
+            List<DataColumn> uniqueCols = new();
+
+            //Add columns
+            foreach (DataColumn col in table.Columns)
+            {
+                //Get dbType string
+                string dbType;
+
+                //Timestamps/rowversion must be handled specially for MySql optimistic concurrency
+                if (col.IsTimeStamp())
+                {
+                    dbType = "BINARY(8)";
+                    //We may also set the AllowNull property
+                    col.AllowDBNull = true;
+                }
+                else
+                {
+                    dbType = GetTypeStringFromType(col.DataType);
+                }
+
+                builder.Append('[')
+                  .Append(col.ColumnName)
+                  .Append("] ")
+                  .Append(dbType);
+
+                //Set primary key contraint
+                if (col.IsPrimaryKey())
+                {
+                    builder.Append(" PRIMARY KEY");
+                }
+                //Set unique constraint (only if not pk)
+                else if (col.Unique)
+                {
+                    //Add the column to unique list for later
+                    uniqueCols.Add(col);
+                }
+
+                //If the value is not null, we can specify the default value
+                if (!col.AllowDBNull)
+                {
+                    if (!string.IsNullOrWhiteSpace(col.DefaultValue?.ToString()))
+                    {
+                        builder.Append(" DEFAULT ");
+                        builder.Append(col.DefaultValue);
+                    }
+                    else
+                    {
+                        //Set not null 
+                        builder.Append(" NOT NULL");
+                    }
+                }
+
+                //Set auto increment
+                if (col.AutoIncrement)
+                {
+                    builder.Append(" AUTOINCREMENT ")
+                        .Append(col.AutoIncrementSeed);
+                }
+
+                //Trailing comma
+                builder.AppendLine(",");
+
+                //No sizing for sqlite
+            }
+
+            //Add unique column contraints
+            if (uniqueCols.Any())
+            {
+                builder.Append("UNIQUE(");
+                for(int i = 0; i < uniqueCols.Count;)
+                {
+                    //Add column name
+                    builder.Append(uniqueCols[i].ColumnName);
+
+                    i++;
+
+                    //Add trailing commas
+                    if(i < uniqueCols.Count)
+                    {
+                        builder.Append(',');
+                    }
+                }
+
+                //Add trailing )
+                builder.AppendLine(")");
+            }
+            else
+            {
+                //remove trailing comma
+                int index = builder.Length;
+                while (builder[--index] != ',')
+                { }
+
+                //Remove the trailing comma
+                builder.Remove(index, 1);
+            }
+
+            //Close the create table command
+            builder.AppendLine(")");
+
+            //Replaced the table name variables
+            builder.Replace("@tableName", table.TableName);
+        }
+
+        protected override string GetTypeStringFromDbType(DbType type)
+        {
+            return type switch
+            {
+                DbType.AnsiString => "TEXT",
+                DbType.Binary => "BLOB",
+                DbType.Byte => "INTEGER",
+                DbType.Boolean => "INTEGER",
+                DbType.Currency => "NUMERIC",
+                DbType.Date => "NUMERIC",
+                DbType.DateTime => "NUMERIC",
+                DbType.Decimal => "NUMERIC",
+                DbType.Double => "NUMERIC",
+                DbType.Guid => "TEXT",
+                DbType.Int16 => "INTEGER",
+                DbType.Int32 => "INTEGER",
+                DbType.Int64 => "INTEGER",
+                DbType.Object => throw new NotSupportedException("A .NET object type is not a supported MySql data-type"),
+                DbType.SByte => "INTEGER",
+                DbType.Single => "NUMERIC",
+                DbType.String => "TEXT",
+                DbType.Time => "TEXT",
+                DbType.UInt16 => "INTEGER",
+                DbType.UInt32 => "INTEGER",
+                DbType.UInt64 => "INTEGER",
+                DbType.VarNumeric => "BLOB",
+                DbType.AnsiStringFixedLength => "TEXT",
+                DbType.StringFixedLength => "TEXT",
+                DbType.Xml => "TEXT",
+                DbType.DateTime2 => "NUMERIC",
+                DbType.DateTimeOffset => "NUMERIC",
+                _ => throw new NotSupportedException("The desired property data-type is not a supported MySql data-type"),
+            };
+        }
+    }
+}
diff --git a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/IDBCommandGenerator.cs b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/IDBCommandGenerator.cs
new file mode 100644
index 0000000..b362b5e
--- /dev/null
+++ b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/IDBCommandGenerator.cs
@@ -0,0 +1,44 @@
+/*
+* Copyright (c) 2023 Vaughn Nugent
+* 
+* Library: VNLib
+* Package: VNLib.Plugins.Extensions.Loading.Sql
+* File: IDBCommandGenerator.cs 
+*
+* IDBCommandGenerator.cs is part of VNLib.Plugins.Extensions.Loading.Sql which is part of the larger 
+* VNLib collection of libraries and utilities.
+*
+* VNLib.Plugins.Extensions.Loading.Sql is free software: you can redistribute it and/or modify 
+* it under the terms of the GNU Affero General Public License as 
+* published by the Free Software Foundation, either version 3 of the
+* License, or (at your option) any later version.
+*
+* VNLib.Plugins.Extensions.Loading.Sql is distributed in the hope that it will be useful,
+* but WITHOUT ANY WARRANTY; without even the implied warranty of
+* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+* GNU Affero General Public License for more details.
+*
+* You should have received a copy of the GNU Affero General Public License
+* along with this program.  If not, see https://www.gnu.org/licenses/.
+*/
+
+using System;
+using System.Data;
+using System.Text;
+
+namespace VNLib.Plugins.Extensions.Loading.Sql.DatabaseBuilder
+{
+    /// <summary>
+    /// Generates specialized statments used to modify a database 
+    /// </summary>
+    interface IDBCommandGenerator
+    {
+        /// <summary>
+        /// Compiles a valid database table creation statment from the <see cref="DataTable"/>
+        /// defining data columns
+        /// </summary>
+        /// <param name="builder">The string builder used to build the creation statment</param>
+        /// <param name="table">The <see cref="DataTable"/> that defines the columns within the table</param>
+        void BuildCreateStatment(StringBuilder builder, DataTable table);
+    }
+}
diff --git a/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/IDbTable.cs b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/IDbTable.cs
new file mode 100644
index 0000000..2ab1912
--- /dev/null
+++ b/lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/IDbTable.cs
@@ -0,0 +1,39 @@
+/*
+* Copyright (c) 2023 Vaughn Nugent
+* 
+* Library: VNLib
+* Package: VNLib.Plugins.Extensions.Loading.Sql
+* File: IDbTable.cs 
+*
+* IDbTable.cs is part of VNLib.Plugins.Extensions.Loading.Sql which is part 
+* of the larger VNLib collection of libraries and utilities.
+*
+* VNLib.Plugins.Extensions.Loading.Sql is free software: you can redistribute it and/or modify 
+* it under the terms of the GNU Affero General Public License as 
+* published by the Free Software Foundation, either version 3 of the
+* License, or (at your option) any later version.
+*
+* VNLib.Plugins.Extensions.Loading.Sql is distributed in the hope that it will be useful,
+* but WITHOUT ANY WARRANTY; without even the implied warranty of
+* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+* GNU Affero General Public License for more details.
+*
+* You should have received a copy of the GNU Affero General Public License
+* along with this program.  If not, see https://www.gnu.org/licenses/.
+*/
+
+using System.Text;
+
+namespace VNLib.Plugins.Extensions.Loading.Sql.DatabaseBuilder
+{
+    interface IDbTable
+    {
+        /// <summary>
+        /// Requests the table build the table creation statment using the <see cref="IDBCommandGenerator"/>
+        /// instance and write the statment to the string builder instance
+        /// </summary>
+        /// <param name="sb">The <see cref="StringBuilder"/> instance to write the statment to</param>
+        /// <param name="commandBuilder">The abstract command builder used to create the statment</param>
+        void WriteCommand(StringBuilder sb, IDBCommandGenerator commandBuilder);
+    }
+}