From 8c5e9dae712227bef7cede73fac16bf3e48b19c6 Mon Sep 17 00:00:00 2001
From: vnugent <public@vaughnnugent.com>
Date: Thu, 13 Apr 2023 11:36:37 -0400
Subject: Database creation helpers w/ fluent api

---
 .../src/DatabaseBuilder/DbCreationException.cs     |  44 ++++
 .../src/DatabaseBuilder/Helpers/AbstractDb.cs      | 104 ++++++++
 .../src/DatabaseBuilder/Helpers/MsSqlDb.cs         | 170 +++++++++++++
 .../src/DatabaseBuilder/Helpers/MySqlDb.cs         | 162 ++++++++++++
 .../src/DatabaseBuilder/Helpers/SqlLiteDb.cs       | 178 +++++++++++++
 .../src/DatabaseBuilder/IDBCommandGenerator.cs     |  44 ++++
 .../src/DatabaseBuilder/IDbTable.cs                |  39 +++
 .../src/DbBuilder.cs                               | 221 ++++++++++++++++
 .../src/IDbColumnBuilder.cs                        |  57 +++++
 .../src/IDbContextBuilder.cs                       |  51 ++++
 .../src/IDbTableBuilder.cs                         |  51 ++++
 .../src/IDbTableDefinition.cs                      |  41 +++
 .../src/SqlDbConnectionLoader.cs                   | 281 ++++++++++++++++++++-
 .../VNLib.Plugins.Extensions.Loading.Sql.csproj    |   2 +-
 14 files changed, 1440 insertions(+), 5 deletions(-)
 create mode 100644 lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/DbCreationException.cs
 create mode 100644 lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/AbstractDb.cs
 create mode 100644 lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/MsSqlDb.cs
 create mode 100644 lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/MySqlDb.cs
 create mode 100644 lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/Helpers/SqlLiteDb.cs
 create mode 100644 lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/IDBCommandGenerator.cs
 create mode 100644 lib/VNLib.Plugins.Extensions.Loading.Sql/src/DatabaseBuilder/IDbTable.cs
 create mode 100644 lib/VNLib.Plugins.Extensions.Loading.Sql/src/DbBuilder.cs
 create mode 100644 lib/VNLib.Plugins.Extensions.Loading.Sql/src/IDbColumnBuilder.cs
 create mode 100644 lib/VNLib.Plugins.Extensions.Loading.Sql/src/IDbContextBuilder.cs
 create mode 100644 lib/VNLib.Plugins.Extensions.Loading.Sql/src/IDbTableBuilder.cs
 create mode 100644 lib/VNLib.Plugins.Extensions.Loading.Sql/src/IDbTableDefinition.cs

(limited to 'lib/VNLib.Plugins.Extensions.Loading.Sql')

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

