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})");
}
