Private/duckdb/DuckDBFastAppender.cs
|
// Compiled at module import by Initialize-DuckDBFastAppender (Add-Type).
// // Keep this at C# 5 language level: Windows PowerShell 5.1 compiles via CodeDom/csc.exe of // .NET Framework, which knows no string interpolation, ?., pattern matching, out var etc. // // A type cannot be redefined in a running PowerShell session. When you change this file, // bump the version suffix of the class name AND $typeName in Initialize-DuckDBFastAppender.ps1. using System; using System.Collections; using System.Collections.Generic; using System.Globalization; using System.Management.Automation; using System.Numerics; using DuckDB.NET.Data; namespace SqlPipeline { public static class DuckDBFastAppenderV1 { private enum Kind { Passthrough, Varchar, Boolean, TinyInt, SmallInt, Integer, BigInt, HugeInt, UTinyInt, USmallInt, UInteger, UBigInt, Float, Double, Decimal } private static readonly CultureInfo Inv = CultureInfo.InvariantCulture; /// <summary> /// Appends all rows of <paramref name="data"/> to <paramref name="tableName"/>. /// Columns are taken from the table schema (DESCRIBE), in table order, and looked up /// by name on each row (PSObject properties or IDictionary keys; case-insensitive for /// PSObjects). Missing properties become NULL, extra properties are ignored. /// Each value is converted to the exact .NET type of its column before calling the /// typed AppendValue overload, because DuckDB.NET reinterprets bytes instead of /// converting on a type mismatch (e.g. Int64 into DOUBLE gives 7.4e-323). /// Lists, dictionaries and PSCustomObjects are serialized with <paramref name="jsonConverter"/>. /// </summary> /// <returns>Number of rows appended.</returns> public static long Write(DuckDBConnection connection, string tableName, IEnumerable data, Func<object, string> jsonConverter) { if (connection == null) throw new ArgumentNullException("connection"); if (string.IsNullOrEmpty(tableName)) throw new ArgumentNullException("tableName"); if (data == null) return 0; // Read the schema: column names (in table order) and their DuckDB types List<string> names = new List<string>(); List<string> types = new List<string>(); using (var cmd = connection.CreateCommand()) { cmd.CommandText = "DESCRIBE \"" + tableName.Replace("\"", "\"\"") + "\""; using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { names.Add(reader.GetString(0)); types.Add(reader.GetString(1)); } } } int colCount = names.Count; Kind[] kinds = new Kind[colCount]; for (int c = 0; c < colCount; c++) kinds[c] = GetKind(types[c]); long rowNumber = 0; var appender = connection.CreateAppender(tableName); bool failed = true; try { foreach (object item in data) { if (item == null) continue; rowNumber++; PSObject pso = item as PSObject; object baseObj = pso != null ? pso.BaseObject : item; IDictionary dict = baseObj as IDictionary; if (dict == null && pso == null) pso = PSObject.AsPSObject(item); var row = appender.CreateRow(); for (int c = 0; c < colCount; c++) { // Lookup by name per column. (Measured: enumerating pso.Properties once per row and // mapping by name is ~2.5x slower on a 118-column PSCustomObject than the indexer.) object val; if (dict != null) { val = dict.Contains(names[c]) ? dict[names[c]] : null; } else { PSPropertyInfo prop = pso.Properties[names[c]]; val = prop == null ? null : prop.Value; } try { AppendCell(row, kinds[c], val, jsonConverter); } catch (Exception ex) { object shown = val is PSObject ? ((PSObject)val).BaseObject : val; throw new InvalidOperationException(string.Format( "[{0}] Row {1}, column '{2}' ({3}): cannot append value of type {4}: {5}", tableName, rowNumber, names[c], types[c], shown == null ? "null" : shown.GetType().FullName, ex.Message), ex); } } row.EndRow(); } // Close() flushes the remaining rows; errors (e.g. constraint violations) surface here appender.Close(); failed = false; } finally { if (failed) { // DuckDB.NET 1.4.4 crashes the process (access violation) when an appender is disposed // after a failed AppendValue without Close() first. Close() may flush the half-filled row, // which is acceptable: callers write into a staging table that is dropped on failure. // Errors here must not hide the original exception. try { appender.Close(); } catch { } try { appender.Dispose(); } catch { } } else { appender.Dispose(); } } return rowNumber; } private static Kind GetKind(string duckType) { string t = (duckType ?? string.Empty).Trim().ToUpperInvariant(); if (t.StartsWith("DECIMAL") || t.StartsWith("NUMERIC")) return Kind.Decimal; if (t.StartsWith("VARCHAR")) return Kind.Varchar; switch (t) { case "JSON": case "TEXT": case "STRING": case "CHAR": case "BPCHAR": return Kind.Varchar; case "BOOLEAN": case "BOOL": case "LOGICAL": return Kind.Boolean; case "TINYINT": case "INT1": return Kind.TinyInt; case "SMALLINT": case "INT2": case "SHORT": return Kind.SmallInt; case "INTEGER": case "INT4": case "INT": case "SIGNED": return Kind.Integer; case "BIGINT": case "INT8": case "LONG": return Kind.BigInt; case "HUGEINT": case "INT128": return Kind.HugeInt; case "UTINYINT": return Kind.UTinyInt; case "USMALLINT": return Kind.USmallInt; case "UINTEGER": return Kind.UInteger; case "UBIGINT": return Kind.UBigInt; case "FLOAT": case "FLOAT4": case "REAL": return Kind.Float; case "DOUBLE": case "FLOAT8": return Kind.Double; default: return Kind.Passthrough; // TIMESTAMP, DATE, UUID, BLOB, INTERVAL, ... -> by runtime type } } private static bool IsComplex(object v) { return v is PSCustomObject || v is IDictionary || (v is IList && !(v is byte[])); } private static void AppendCell(IDuckDBAppenderRow row, Kind kind, object val, Func<object, string> jsonConverter) { if (val == null) { row.AppendNullValue(); return; } PSObject wrapped = val as PSObject; object v = wrapped != null ? wrapped.BaseObject : val; if (v == null || v is DBNull) { row.AppendNullValue(); return; } if (IsComplex(v)) { // Pass the wrapped object: a PSCustomObject's properties live on the PSObject, not on its BaseObject string json = jsonConverter != null ? jsonConverter(val) : v.ToString(); if (json == null) row.AppendNullValue(); else row.AppendValue(json); return; } // Booleans count as 1/0 in numeric columns (same as the former PowerShell appender) object n = v is bool ? (object)((bool)v ? 1 : 0) : v; switch (kind) { case Kind.Varchar: row.AppendValue(ToInvariantString(v)); return; case Kind.Boolean: row.AppendValue((bool?)Convert.ToBoolean(v, Inv)); return; case Kind.TinyInt: row.AppendValue((sbyte?)Convert.ToSByte(n, Inv)); return; case Kind.SmallInt: row.AppendValue((short?)Convert.ToInt16(n, Inv)); return; case Kind.Integer: row.AppendValue((int?)Convert.ToInt32(n, Inv)); return; case Kind.BigInt: row.AppendValue((long?)Convert.ToInt64(n, Inv)); return; case Kind.UTinyInt: row.AppendValue((byte?)Convert.ToByte(n, Inv)); return; case Kind.USmallInt: row.AppendValue((ushort?)Convert.ToUInt16(n, Inv)); return; case Kind.UInteger: row.AppendValue((uint?)Convert.ToUInt32(n, Inv)); return; case Kind.UBigInt: row.AppendValue((ulong?)Convert.ToUInt64(n, Inv)); return; case Kind.Float: row.AppendValue((float?)Convert.ToSingle(n, Inv)); return; case Kind.Double: row.AppendValue((double?)Convert.ToDouble(n, Inv)); return; case Kind.Decimal: row.AppendValue((decimal?)Convert.ToDecimal(n, Inv)); return; case Kind.HugeInt: if (n is BigInteger) row.AppendValue((BigInteger?)(BigInteger)n); else if (n is double || n is float) row.AppendValue((BigInteger?)new BigInteger(Convert.ToDouble(n, Inv))); else row.AppendValue((BigInteger?)new BigInteger(Convert.ToDecimal(n, Inv))); return; default: AppendByRuntimeType(row, v); return; } } private static void AppendByRuntimeType(IDuckDBAppenderRow row, object v) { if (v is string) { row.AppendValue((string)v); return; } if (v is bool) { row.AppendValue((bool?)(bool)v); return; } if (v is DateTime) { row.AppendValue((DateTime?)(DateTime)v); return; } if (v is DateTimeOffset) { row.AppendValue((DateTimeOffset?)(DateTimeOffset)v); return; } if (v is TimeSpan) { row.AppendValue((TimeSpan?)(TimeSpan)v); return; } if (v is Guid) { row.AppendValue((Guid?)(Guid)v); return; } if (v is long) { row.AppendValue((long?)(long)v); return; } if (v is int) { row.AppendValue((int?)(int)v); return; } if (v is short) { row.AppendValue((short?)(short)v); return; } if (v is sbyte) { row.AppendValue((sbyte?)(sbyte)v); return; } if (v is ulong) { row.AppendValue((ulong?)(ulong)v); return; } if (v is uint) { row.AppendValue((uint?)(uint)v); return; } if (v is ushort) { row.AppendValue((ushort?)(ushort)v); return; } if (v is byte) { row.AppendValue((byte?)(byte)v); return; } if (v is double) { row.AppendValue((double?)(double)v); return; } if (v is float) { row.AppendValue((float?)(float)v); return; } if (v is decimal) { row.AppendValue((decimal?)(decimal)v); return; } if (v is BigInteger) { row.AppendValue((BigInteger?)(BigInteger)v); return; } if (v is byte[]) { row.AppendValue((byte[])v); return; } row.AppendValue(ToInvariantString(v)); } private static string ToInvariantString(object v) { string s = v as string; if (s != null) return s; if (v is DateTime) return ((DateTime)v).ToString("yyyy-MM-dd HH:mm:ss.FFFFFFF", Inv); if (v is DateTimeOffset) return ((DateTimeOffset)v).ToString("yyyy-MM-dd HH:mm:ss.FFFFFFFzzz", Inv); IFormattable f = v as IFormattable; if (f != null) return f.ToString(null, Inv); return v.ToString(); } } } |