diff options
Diffstat (limited to 'lib/sql-providers/mysql/VNLib.Plugins.Extensions.Loading.Sql.MySql/src/MYSqlExport.cs')
| -rw-r--r-- | lib/sql-providers/mysql/VNLib.Plugins.Extensions.Loading.Sql.MySql/src/MYSqlExport.cs | 270 |
1 files changed, 232 insertions, 38 deletions
diff --git a/lib/sql-providers/mysql/VNLib.Plugins.Extensions.Loading.Sql.MySql/src/MYSqlExport.cs b/lib/sql-providers/mysql/VNLib.Plugins.Extensions.Loading.Sql.MySql/src/MYSqlExport.cs index 26fd3a3..efe5410 100644 --- a/lib/sql-providers/mysql/VNLib.Plugins.Extensions.Loading.Sql.MySql/src/MYSqlExport.cs +++ b/lib/sql-providers/mysql/VNLib.Plugins.Extensions.Loading.Sql.MySql/src/MYSqlExport.cs @@ -23,29 +23,45 @@ */ using System; +using System.Data; +using System.Linq; +using System.Text; using System.Text.Json; using System.Data.Common; using System.Threading.Tasks; using Microsoft.EntityFrameworkCore; -using MySql.Data.MySqlClient; +using MySqlConnector; using VNLib.Utils.Logging; using VNLib.Plugins.Extensions.Loading; +using VNLib.Plugins.Extensions.Loading.Sql; +using VNLib.Plugins.Extensions.Loading.Sql.DatabaseBuilder; namespace VNLib.Plugins.Extensions.Sql { [ServiceExport] [ConfigurationName("sql", Required = true)] - public sealed class MySqlExport(PluginBase plugin, IConfigScope config) + public sealed class MySQLExport(PluginBase plugin, IConfigScope config) : IRuntimeDbProvider { private async Task<string> BuildConnStringAsync() { + MySqlConnectionStringBuilder sb; + //See if the user suggested a raw connection string if (config.TryGetProperty("connection_string", ps => ps.GetString(), out string? conString)) { + sb = new(conString); + + //If the user did not provide a password, try to get it from secret storage + if (string.IsNullOrWhiteSpace(sb.Password)) + { + using ISecretResult? password = await plugin.TryGetSecretAsync("db_password"); + sb.Password = password?.Result.ToString(); + } + return conString!; } else if (config.TryGetValue("json", out JsonElement value)) @@ -57,21 +73,18 @@ namespace VNLib.Plugins.Extensions.Sql DictionaryKeyPolicy = JsonNamingPolicy.SnakeCaseLower, }; - MySqlConnectionStringBuilder b = value.Deserialize<MySqlConnectionStringBuilder>(opt)!; + sb = value.Deserialize<MySqlConnectionStringBuilder>(opt)!; - //Get the password from the secret manager + //Get the db password from the secret manager using ISecretResult? secret = await plugin.TryGetSecretAsync("db_password"); - - b.Password = secret?.Result.ToString(); - return b.ConnectionString; + sb.Password = secret?.Result.ToString(); } else { //Get the password from the secret manager using ISecretResult? secret = await plugin.TryGetSecretAsync("db_password"); - // Build connection strin - return new MySqlConnectionStringBuilder() + sb = new() { Server = config["hostname"].GetString(), Database = config["catalog"].GetString(), @@ -79,36 +92,56 @@ namespace VNLib.Plugins.Extensions.Sql Pooling = true, MinimumPoolSize = config.GetValueOrDefault("min_pool_size", p => p.GetUInt32(), 10u), MaximumPoolSize = config.GetValueOrDefault("max_pool_size", p => p.GetUInt32(), 50u), - AllowBatch = config.GetValueOrDefault("allow_batch", p => p.GetBoolean(), true), - ConnectionLifeTime = config.GetValueOrDefault("connection_lifetime", p => p.GetUInt32(), 0u), - ConnectionTimeout = config.GetValueOrDefault("connection_timeout", p => p.GetUInt32(), 15u), - Port = config.GetValueOrDefault("port", p => p.GetUInt32(), 3306u), - PipeName = config.GetValueOrDefault("pipe_name", p => p.GetString(), null), - AllowLoadLocalInfile = config.GetValueOrDefault("allow_load_local_infile", p => p.GetBoolean(), false), - AllowLoadLocalInfileInPath = config.GetValueOrDefault("allow_load_local_infile_in_path", p => p.GetString(), null), - Password = secret?.Result.ToString(), + }; + + if (config.TryGetProperty("port", p => p.GetUInt16(), out ushort port)) + { + sb.Port = port; + } + + if (config.TryGetProperty("ssl_mode", p => p.GetString(), out string? sslMode) + && Enum.TryParse(sslMode, true, out MySqlSslMode mode)) + { + sb.SslMode = mode; + } + + if (config.TryGetProperty("connection_lifetime", value => value.GetUInt32(), out uint connLife)) + { + sb.ConnectionLifeTime = connLife; + } + + if (config.TryGetProperty("connection_timeout", value => value.GetUInt32(), out uint connTimeout)) + { + sb.ConnectionTimeout = connTimeout; + } + + if (config.TryGetProperty("pipe_name", value => value.GetString(), out string? pipeName)) + { + sb.PipeName = pipeName; + } + + if (config.TryGetProperty("allow_load_local_infile", value => value.GetBoolean(), out bool allowLoadLocalInfile)) + { + sb.AllowLoadLocalInfile = allowLoadLocalInfile; + } + + if (config.TryGetProperty("default_command_timeout", value => value.GetUInt32(), out uint defaultCommandTimeout)) + { + sb.DefaultCommandTimeout = defaultCommandTimeout; + } + + if (config.TryGetProperty("interactive_session", value => value.GetBoolean(), out bool interactiveSession)) + { + sb.InteractiveSession = interactiveSession; } - .ConnectionString; } + + return sb.ConnectionString; } - /* - * NOTICE: - * Function names must be public and must match the SqlConnectionLoader delegate names. - * - * GetDbConnection - A sync or async function that takes a configuration scope and - * returns a DbConnection factory - * - * GetDbOptions - A sync or async function that takes a configuration scope and - * returns a DbConnectionOptions instance - * - * GetProviderName - Returns a string that is the provider name for the connection - */ - - public string GetProviderName() => "mysql"; - - public async Task<Func<DbConnection>> GetDbConnectionAsync(IConfigScope sqlConfig) + ///<inheritdoc/> + public async Task<Func<DbConnection>> GetDbConnectionAsync() { //Store local copy of the connection string, probably not the best idea because of the password, but best for now string connString = await BuildConnStringAsync(); @@ -116,17 +149,18 @@ namespace VNLib.Plugins.Extensions.Sql return () => new MySqlConnection(connString); } - public async Task<DbContextOptions> GetDbOptionsAsync(IConfigScope sqlConfig) + ///<inheritdoc/> + public async Task<DbContextOptions> GetDbOptionsAsync() { //Get the connection string from the configuration string connString = await BuildConnStringAsync(); //Build the options using the mysql extension method - DbContextOptionsBuilder b = new(); - b.UseMySQL(connString); + DbContextOptionsBuilder b = new();; + b.UseMySql(connString, ServerVersion.AutoDetect(connString)); //Write debug loggin to the debug log if the user has it enabled or the plugin is in debug mode - if (sqlConfig.GetValueOrDefault("debug", p => p.GetBoolean(), false) || plugin.IsDebug()) + if (config.GetValueOrDefault("debug", p => p.GetBoolean(), false) || plugin.IsDebug()) { //Write the SQL to the debug log b.LogTo((v) => plugin.Log.Debug("MySql: {v}", v)); @@ -134,5 +168,165 @@ namespace VNLib.Plugins.Extensions.Sql return b.Options; } + + ///<inheritdoc/> + public IDBCommandGenerator GetCommandGenerator() => new MsSqlDb(); + + internal class MsSqlDb : IDBCommandGenerator + { + const int MAX_VARIABLE_SIZE = 8000; + + ///<inheritdoc/> + public void BuildCreateStatment(StringBuilder builder, DataTable table) + { + builder.AppendLine("CREATE TABLE IF NOT EXISTS `@tableName` ("); + + //Add columns + foreach (DataColumn col in table.Columns) + { + //Get dbType string + string dbType; + + //Timestamps/rowversion must be handled specially for MySql optimistic concurrency + if (col.IsTimeStamp()) + { + dbType = "TIMESTAMP"; + } + else + { + dbType = GetTypeStringFromDbType(col); + } + + builder.Append('`') + .Append(col.ColumnName) + .Append("` ") + .Append(dbType); + + //If the value is not null, we can specify the default value + if (!col.AllowDBNull) + { + if (!string.IsNullOrWhiteSpace(col.DefaultValue?.ToString())) + { + builder.Append(" DEFAULT "); + builder.Append(col.DefaultValue); + } + else + { + //Set not null + builder.Append(" NOT NULL"); + } + } + + //Set auto increment + if (col.AutoIncrement) + { + builder.Append(" AUTO_INCREMENT=") + .Append(col.AutoIncrementSeed); + } + + //Trailing comma + builder.AppendLine(","); + + //Set size if defined, we need to bypass column max length + if (col.MaxLength() > MAX_VARIABLE_SIZE) + { + builder.Replace("@size", "MAX"); + } + else if (col.MaxLength() > 0) + { + builder.Replace("@size", col.MaxLength().ToString()); + } + else + { + builder.Replace("(@size)", ""); + } + } + + int index = builder.Length; + while (builder[--index] != ',') + { } + + //Remove the trailing comma + builder.Remove(index, 1); + + AddConstraints(builder, table); + + //Close the create table command + builder.AppendLine(")"); + + //Replaced the table name variables + builder.Replace("@tableName", table.TableName); + } + + private static void AddConstraints(StringBuilder builder, DataTable table) + { + DataColumn[] primaryKeys = table.Columns.OfType<DataColumn>() + .Where(static c => c.IsPrimaryKey()) + .ToArray(); + + if (primaryKeys.Length > 0) + { + builder.AppendLine(",") + .Append("CONSTRAINT ") + .Append(table.TableName) + .Append("_pk PRIMARY KEY (") + .AppendJoin(", ", primaryKeys.Select(static c => c.ColumnName)) + .Append(')'); + } + + //Repeat for unique constraints + DataColumn[] uniqueKeys = table.Columns.OfType<DataColumn>() + .Where(static c => c.Unique && !c.IsPrimaryKey()) + .ToArray(); + + if (uniqueKeys.Length > 0) + { + builder.AppendLine(",") + .Append("CONSTRAINT ") + .Append(table.TableName) + .Append("_unique UNIQUE (") + .AppendJoin(", ", uniqueKeys.Select(static c => c.ColumnName)) + .Append(')'); + } + + builder.AppendLine(); + } + + ///<inheritdoc/> + private static string GetTypeStringFromDbType(DataColumn col) + { + return col.GetDbType() switch + { + DbType.AnsiString => "VARCHAR(@size)", + DbType.Binary => "VARBINARY(@size)", + DbType.Byte => "TINYINT", + DbType.Boolean => "BOOL", + DbType.Currency => "DECIMAL", + DbType.Date => "DATE", + DbType.DateTime => "DATETIME", + DbType.Decimal => "DECIMAL", + DbType.Double => "DOUBLE", + DbType.Guid => "VARCHAR(@size)", + DbType.Int16 => "SMALLINT", + DbType.Int32 => "INT", + DbType.Int64 => "BIGINT", + DbType.Object => throw new NotSupportedException("A .NET object type is not a supported MySql data-type"), + DbType.SByte => "TINYINT", + DbType.Single => "FLOAT", + DbType.String => "VARCHAR(@size)", + DbType.Time => "TIME", + DbType.UInt16 => "SMALLINT", + DbType.UInt32 => "INT", + DbType.UInt64 => "BIGINT", + DbType.VarNumeric => throw new NotSupportedException("Variable numeric value is not a supported MySql data-type"), + DbType.AnsiStringFixedLength => "TEXT(@size)", + DbType.StringFixedLength => "TEXT(@size)", + DbType.Xml => "VARCHAR(@size)", + DbType.DateTime2 => "DATETIME", + DbType.DateTimeOffset => throw new NotSupportedException("DateTimeOffset is not a supported MySql data-type"), + _ => throw new NotSupportedException("The desired property data-type is not a supported MySql data-type"), + }; + } + } } } |