private static string CreateCoprySql(SdbTableMapping.TableMapping tableMapping) { var sb = new StringBuilder(); var tableName = Program.Config.UseNamesInTables ? tableMapping.Name : $"H_{tableMapping.Id:X}"; sb.Append($"COPY {Program.Config.SqlNamespace}.\"{tableName}\" ("); for (var i = 0; i < tableMapping.Colums.Count; i++) { var col = tableMapping.Colums[i]; var name = Program.Config.UseNamesInTables ? col.Name : $"H_{col.Id:X}"; sb.Append($"\"{name}\""); if (tableMapping.Colums.Count - 1 != i) { sb.Append(", "); } } sb.Append(") FROM STDIN (FORMAT BINARY)"); return(sb.ToString()); }
public static string CreateTableSql(SdbTableMapping.TableMapping tableMapping) { var sb = new StringBuilder(); var tableName = Program.Config.UseNamesInTables ? tableMapping.Name : $"H_{tableMapping.Id:X}"; sb.AppendLine($"CREATE TABLE IF NOT EXISTS {Program.Config.SqlNamespace}.\"{tableName}\""); sb.AppendLine("("); for (var i = 0; i < tableMapping.Colums.Count; i++) { var col = tableMapping.Colums[i]; var name = Program.Config.UseNamesInTables ? col.Name : $"H_{col.Id:X}"; sb.Append($"\"{name}\"".PadLeft(4)); sb.Append(" "); sb.Append(GetSqlTypeName(col.SqlType)); if (col.IsPrimaryKey) { sb.Append(" PRIMARY KEY"); } else if (!col.IsNullable) { sb.Append(" NOT NULL"); } if (tableMapping.Colums.Count - 1 != i) { sb.Append(","); } sb.AppendLine(); } sb.AppendLine(");"); return(sb.ToString()); }
public static void ImportDataToTable(SdbTableMapping.TableMapping tableMapping, StaticDB.Table table, NpgsqlConnection conn = null) { if (conn == null) { conn = Conn; } var copySql = CreateCoprySql(tableMapping); using (var writer = conn.BeginBinaryImport(copySql)) { for (var i = 0; i < table.Rows.Count; i++) { var row = table.Rows[i]; writer.StartRow(); for (var index = 0; index < row.Fields.Count; index++) { var field = row.Fields[index]; var fieldinfo = tableMapping.Colums[index]; if (field == null) { writer.WriteNull(); } else if (IsBasicType(fieldinfo.SdbType)) { if (fieldinfo.SdbType == StaticDB.DBType.UShort) { writer.Write(Convert.ToInt32(field)); } else if (fieldinfo.SdbType == StaticDB.DBType.UInt) { writer.Write(Convert.ToInt64(field)); } else if (fieldinfo.SdbType == StaticDB.DBType.ULong) { var val = (long)(ulong)field; writer.Write(val); } else if (fieldinfo.SdbType == StaticDB.DBType.Half) { writer.Write(Convert.ToSingle(field)); } else if (field is string fieldStr) // clean nulls from the end of strings { var val = fieldStr.Replace("\0", ""); writer.Write(val); } else if (field is char fieldChar) { if (fieldChar == '\0') // feels abit eh, TODO: check incase of char trouble { Console.WriteLine($"Got an invalid char {fieldChar} in table {tableMapping.Name} ({tableMapping.Idx}) on colum {fieldinfo.Name} row {i}"); writer.Write(' '); } else { writer.Write(fieldChar); } } else { writer.Write(field); } } else if (IsCustomType(fieldinfo.SdbType)) { writer.Write(field); } else if (IsArrayType(fieldinfo.SdbType)) { if (field is List <ushort> shortsList) { var val = shortsList.Select(x => Convert.ToInt32(x)).ToList(); writer.Write(val); } else if (field is List <uint> intsList) { var val = intsList.Select(x => Convert.ToInt64(x)).ToList(); writer.Write(val); } else { writer.Write(field); } } else { Console.WriteLine("Unhandled type"); writer.WriteNull(); } } } writer.Complete(); } Console.WriteLine($"Imported data to table {tableMapping.Name} ({tableMapping.Idx})"); }