diff options
Diffstat (limited to 'lib/sql-providers/sqlite/VNLib.Plugins.Extensions.Loading.Sql.SQLite/src/SQLiteExport.cs')
| -rw-r--r-- | lib/sql-providers/sqlite/VNLib.Plugins.Extensions.Loading.Sql.SQLite/src/SQLiteExport.cs | 210 |
1 files changed, 181 insertions, 29 deletions
diff --git a/lib/sql-providers/sqlite/VNLib.Plugins.Extensions.Loading.Sql.SQLite/src/SQLiteExport.cs b/lib/sql-providers/sqlite/VNLib.Plugins.Extensions.Loading.Sql.SQLite/src/SQLiteExport.cs index 6f9455a..ff74051 100644 --- a/lib/sql-providers/sqlite/VNLib.Plugins.Extensions.Loading.Sql.SQLite/src/SQLiteExport.cs +++ b/lib/sql-providers/sqlite/VNLib.Plugins.Extensions.Loading.Sql.SQLite/src/SQLiteExport.cs @@ -23,9 +23,12 @@ */ using System; +using System.Data; +using System.Text; using System.Text.Json; using System.Data.Common; using System.Threading.Tasks; +using System.Collections.Generic; using Microsoft.EntityFrameworkCore; @@ -33,21 +36,32 @@ using Microsoft.Data.Sqlite; using VNLib.Utils.Logging; using VNLib.Plugins.Extensions.Loading; +using VNLib.Plugins.Extensions.Loading.Sql; +using VNLib.Plugins.Extensions.Loading.Sql.DatabaseBuilder; namespace VNLib.Plugins.Extensions.Sql { [ServiceExport] [ConfigurationName("sql", Required = true)] - public sealed class SQLiteExport(PluginBase plugin, IConfigScope config) + public sealed class SQLiteExport(PluginBase plugin, IConfigScope config) : IRuntimeDbProvider { private async Task<string> BuildConnStringAsync() { + SqliteConnectionStringBuilder sb; + //See if the user suggested a raw connection string if (config.TryGetProperty("connection_string", ps => ps.GetString(), out string? conString)) { - return conString!; + sb = new(conString); + + //If the user did not provide a password, try to get it from secret storage + if (string.IsNullOrWhiteSpace(sb.Password)) + { + using ISecretResult? password = await plugin.TryGetSecretAsync("db_password"); + sb.Password = password?.Result.ToString(); + } } else if (config.TryGetValue("json", out JsonElement value)) { @@ -58,13 +72,11 @@ namespace VNLib.Plugins.Extensions.Sql DictionaryKeyPolicy = JsonNamingPolicy.SnakeCaseLower, }; - SqliteConnectionStringBuilder b = value.Deserialize<SqliteConnectionStringBuilder>(opt)!; + sb = value.Deserialize<SqliteConnectionStringBuilder>(opt)!; //Get the password from the secret manager using ISecretResult? secret = await plugin.TryGetSecretAsync("db_password"); - - b.Password = secret?.Result.ToString(); - return b.ConnectionString; + sb.Password = secret?.Result.ToString(); } else { @@ -72,7 +84,7 @@ namespace VNLib.Plugins.Extensions.Sql using ISecretResult? secret = await plugin.TryGetSecretAsync("db_password"); // Build connection strin - return new SqliteConnectionStringBuilder() + sb = new() { DataSource = config["source"].GetString(), Pooling = true, @@ -82,35 +94,23 @@ namespace VNLib.Plugins.Extensions.Sql Mode = config.GetValueOrDefault("mode", p => (SqliteOpenMode)p.GetInt32(), SqliteOpenMode.ReadWriteCreate), Password = secret?.Result.ToString(), - } - .ConnectionString; + }; } + + return sb.ConnectionString; } - - /* - * NOTICE: - * Function names must be public and must match the SqlConnectionLoader delegate names. - * - * GetDbConnection - A sync or async function that takes a configuration scope and - * returns a DbConnection factory - * - * GetDbOptions - A sync or async function that takes a configuration scope and - * returns a DbConnectionOptions instance - * - * GetProviderName - Returns a string that is the provider name for the connection - */ - - public string GetProviderName() => "sqlite"; //Use default handler for sqlite db creation - - public async Task<Func<DbConnection>> GetDbConnectionAsync(IConfigScope sqlConfig) - { + + /// <inheritdoc/> + public async Task<Func<DbConnection>> GetDbConnectionAsync() + { //Store local copy of the connection string, probably not the best idea because of the password, but best for now string connString = await BuildConnStringAsync(); return () => new SqliteConnection(connString); } - public async Task<DbContextOptions> GetDbOptionsAsync(IConfigScope sqlConfig) + /// <inheritdoc/> + public async Task<DbContextOptions> GetDbOptionsAsync() { //Get the connection string from the configuration string connString = await BuildConnStringAsync(); @@ -119,7 +119,7 @@ namespace VNLib.Plugins.Extensions.Sql b.UseSqlite(connString); //Write debug loggin to the debug log if the user has it enabled or the plugin is in debug mode - if (sqlConfig.GetValueOrDefault("debug", p => p.GetBoolean(), false) || plugin.IsDebug()) + if (config.GetValueOrDefault("debug", p => p.GetBoolean(), false) || plugin.IsDebug()) { //Write the SQL to the debug log b.LogTo((v) => plugin.Log.Debug("SQLite: {v}", v)); @@ -127,5 +127,157 @@ namespace VNLib.Plugins.Extensions.Sql return b.Options; } + + /// <inheritdoc/> + public IDBCommandGenerator GetCommandGenerator() => new SqlLiteDb(); + + /// <inheritdoc/> + public override string ToString() => $"SQLite DB runtime provider : {GetHashCode()}"; + + internal sealed class SqlLiteDb : IDBCommandGenerator + { + public void BuildCreateStatment(StringBuilder builder, DataTable table) + { + builder.AppendLine("CREATE TABLE IF NOT EXISTS @tableName ("); + + List<DataColumn> uniqueCols = new(); + + //Add columns + foreach (DataColumn col in table.Columns) + { + //Get dbType string + string dbType; + + //Timestamps/rowversion must be handled specially for MySql optimistic concurrency + if (col.IsTimeStamp()) + { + dbType = "BINARY(8)"; + //We may also set the AllowNull property + col.AllowDBNull = true; + } + else + { + dbType = GetTypeStringFromDbType(col); + } + + builder.Append('[') + .Append(col.ColumnName) + .Append("] ") + .Append(dbType); + + //Set primary key contraint + if (col.IsPrimaryKey()) + { + builder.Append(" PRIMARY KEY"); + } + //Set unique constraint (only if not pk) + else if (col.Unique) + { + //Add the column to unique list for later + uniqueCols.Add(col); + } + + //If the value is not null, we can specify the default value + if (!col.AllowDBNull) + { + if (!string.IsNullOrWhiteSpace(col.DefaultValue?.ToString())) + { + builder.Append(" DEFAULT "); + builder.Append(col.DefaultValue); + } + else + { + //Set not null + builder.Append(" NOT NULL"); + } + } + + //Set auto increment + if (col.AutoIncrement) + { + builder.Append(" AUTOINCREMENT ") + .Append(col.AutoIncrementSeed); + } + + //Trailing comma + builder.AppendLine(","); + + //No sizing for sqlite + } + + //Add unique column contraints + if (uniqueCols.Count != 0) + { + builder.Append("UNIQUE("); + for (int i = 0; i < uniqueCols.Count;) + { + //Add column name + builder.Append(uniqueCols[i].ColumnName); + + i++; + + //Add trailing commas + if (i < uniqueCols.Count) + { + builder.Append(','); + } + } + + //Add trailing ) + builder.AppendLine(")"); + } + else + { + //remove trailing comma + int index = builder.Length; + while (builder[--index] != ',') + { } + + //Remove the trailing comma + builder.Remove(index, 1); + } + + //Close the create table command + builder.AppendLine(")"); + + //Replaced the table name variables + builder.Replace("@tableName", table.TableName); + } + + private static string GetTypeStringFromDbType(DataColumn col) + { + return col.GetDbType() switch + { + DbType.AnsiString => "TEXT", + DbType.Binary => "BLOB", + DbType.Byte => "INTEGER", + DbType.Boolean => "INTEGER", + DbType.Currency => "NUMERIC", + DbType.Date => "NUMERIC", + DbType.DateTime => "NUMERIC", + DbType.Decimal => "NUMERIC", + DbType.Double => "NUMERIC", + DbType.Guid => "TEXT", + DbType.Int16 => "INTEGER", + DbType.Int32 => "INTEGER", + DbType.Int64 => "INTEGER", + DbType.Object => throw new NotSupportedException("A .NET object type is not a supported MySql data-type"), + DbType.SByte => "INTEGER", + DbType.Single => "NUMERIC", + DbType.String => "TEXT", + DbType.Time => "TEXT", + DbType.UInt16 => "INTEGER", + DbType.UInt32 => "INTEGER", + DbType.UInt64 => "INTEGER", + DbType.VarNumeric => "BLOB", + DbType.AnsiStringFixedLength => "TEXT", + DbType.StringFixedLength => "TEXT", + DbType.Xml => "TEXT", + DbType.DateTime2 => "NUMERIC", + DbType.DateTimeOffset => "NUMERIC", + _ => throw new NotSupportedException("The desired property data-type is not a supported MySql data-type"), + }; + } + } } } |