/// <inheritdoc />
public override ITable CreateTable(RowLayout layout, TableFlags flags)
{
if (layout == null)
{
throw new ArgumentNullException(nameof(layout));
}
LogCreateTable(layout);
if ((flags & TableFlags.InMemory) != 0)
{
throw new NotSupportedException($"Table '{layout.Name}' does not support TableFlags.{TableFlags.InMemory}");
}
if (layout.Name.HasInvalidChars(ASCII.Strings.SafeName))
{
throw new ArgumentException($"Table name {layout.Name} contains invalid chars!");
}
var queryText = new StringBuilder();
queryText.Append($"CREATE TABLE {SqlStorage.FQTN(Name, layout.Name)} (");
for (var i = 0; i < layout.FieldCount; i++)
{
var fieldProperties = layout[i];
if (i > 0)
{
queryText.Append(",");
}
queryText.Append(fieldProperties.NameAtDatabase + " ");
switch (fieldProperties.DataType)
{
case DataType.Binary:
queryText.Append("VARBINARY(MAX)");
break;
case DataType.Bool:
queryText.Append("BIT");
break;
case DataType.DateTime:
switch (fieldProperties.DateTimeType)
{
case DateTimeType.Undefined:
case DateTimeType.Native:
queryText.Append("DATETIME");
break;
case DateTimeType.DoubleSeconds:
case DateTimeType.DoubleEpoch:
queryText.Append("FLOAT(53)");
break;
case DateTimeType.DecimalSeconds:
queryText.Append("NUMERIC(28,8)");
break;
case DateTimeType.BigIntHumanReadable:
case DateTimeType.BigIntTicks:
queryText.Append("BIGINT");
break;
default: throw new NotImplementedException();
}
break;
case DataType.TimeSpan:
switch (fieldProperties.DateTimeType)
{
case DateTimeType.Undefined:
case DateTimeType.Native:
queryText.Append("TIMESPAN");
break;
case DateTimeType.DoubleEpoch:
case DateTimeType.DoubleSeconds:
queryText.Append("FLOAT(53)");
break;
case DateTimeType.DecimalSeconds:
queryText.Append("NUMERIC(28,8)");
break;
case DateTimeType.BigIntHumanReadable:
case DateTimeType.BigIntTicks:
queryText.Append("BIGINT");
break;
default: throw new NotImplementedException();
}
break;
case DataType.Int8:
queryText.Append("SMALLINT");
break;
case DataType.Int16:
queryText.Append("SMALLINT");
break;
case DataType.Int32:
queryText.Append("INTEGER");
break;
case DataType.Int64:
queryText.Append("BIGINT");
break;
case DataType.Single:
queryText.Append("REAL");
break;
case DataType.Double:
queryText.Append("FLOAT(53)");
break;
case DataType.Enum:
queryText.Append("BIGINT");
break;
case DataType.User:
case DataType.String:
switch (fieldProperties.StringEncoding)
{
case StringEncoding.ASCII:
if ((fieldProperties.MaximumLength > 0) && (fieldProperties.MaximumLength <= 255))
{
queryText.AppendFormat("VARCHAR({0})", fieldProperties.MaximumLength);
}
else
{
queryText.Append("VARCHAR(MAX)");
}
break;
case StringEncoding.UTF16:
case StringEncoding.UTF8:
if ((fieldProperties.MaximumLength > 0) && (fieldProperties.MaximumLength <= 255))
{
queryText.AppendFormat("NVARCHAR({0})", fieldProperties.MaximumLength);
}
else
{
queryText.Append("NVARCHAR(MAX)");
}
break;
default: throw new NotSupportedException($"MSSQL Server does not support {fieldProperties.StringEncoding}!");
}
break;
case DataType.Decimal:
if (fieldProperties.MaximumLength > 0)
{
var l_PreDecimal = (int)fieldProperties.MaximumLength;
var l_Temp = (fieldProperties.MaximumLength - l_PreDecimal) * 100;
var l_Decimal = (int)l_Temp;
if ((l_Decimal >= l_PreDecimal) || (l_Decimal != l_Temp))
{
throw new ArgumentOutOfRangeException(
$"Field {fieldProperties.Name} has an invalid MaximumLength of {l_PreDecimal},{l_Decimal}. Correct values range from s,p = 1,0 to 28,27 with 0 < s < p!");
}
queryText.AppendFormat("NUMERIC({0},{1})", l_PreDecimal, l_Decimal);
}
else
{
queryText.Append("NUMERIC(28,8)");
}
break;
default: throw new NotImplementedException($"Unknown DataType {fieldProperties.DataType}!");
}
if ((fieldProperties.Flags & FieldFlags.ID) != 0)
{
queryText.Append(" PRIMARY KEY");
}
if ((fieldProperties.Flags & FieldFlags.AutoIncrement) != 0)
{
queryText.Append(" IDENTITY");
}
if ((fieldProperties.Flags & FieldFlags.Unique) != 0)
{
queryText.Append(" UNIQUE");
switch (fieldProperties.TypeAtDatabase)
{
case DataType.Bool:
case DataType.Char:
case DataType.DateTime:
case DataType.Decimal:
case DataType.Double:
case DataType.Enum:
case DataType.Int8:
case DataType.Int16:
case DataType.Int32:
case DataType.Int64:
case DataType.UInt8:
case DataType.UInt16:
case DataType.UInt32:
case DataType.UInt64:
case DataType.Single:
case DataType.TimeSpan:
break;
case DataType.String:
if (fieldProperties.MaximumLength <= 0)
{
throw new NotSupportedException(
$"Unique string fields without length are not supported! Please define Field.MaxLength at table {layout.Name} field {fieldProperties.Name}");
}
break;
default: throw new NotSupportedException($"Uniqueness for table {layout.Name} field {fieldProperties.Name} is not supported!");
}
}
if (fieldProperties.Description != null)
{
if (fieldProperties.Description.HasInvalidChars(ASCII.Strings.Printable))
{
throw new ArgumentException("Description of field '{0}' contains invalid chars!", fieldProperties.Name);
}
queryText.Append(" COMMENT '" + fieldProperties.Description.Substring(0, 60) + "'");
}
}
queryText.Append(")");
SqlStorage.Execute(database: Name, table: layout.Name, cmd: queryText.ToString());
for (var i = 0; i < layout.FieldCount; i++)
{
var fieldProperties = layout[i];
if ((fieldProperties.Flags & FieldFlags.ID) != 0)
{
continue;
}
if ((fieldProperties.Flags & FieldFlags.Index) != 0)
{
var command =
$"CREATE INDEX idx_{SqlStorage.EscapeString(fieldProperties.NameAtDatabase)} ON {SqlStorage.FQTN(Name, layout.Name)} ({SqlStorage.EscapeFieldName(fieldProperties)})";
SqlStorage.Execute(database: Name, table: layout.Name, cmd: command);
}
}
return(GetTable(layout));
}
/// <inheritdoc />
public override ITable CreateTable(RowLayout layout, TableFlags flags)
{
if (layout == null)
{
throw new ArgumentNullException(nameof(layout));
}
if (layout.Name.HasInvalidChars(ASCII.Strings.SafeName))
{
throw new ArgumentException($"Table name {layout.Name} contains invalid chars!");
}
var utf8Charset = ((MySqlStorage)Storage).CharacterSet;
LogCreateTable(layout);
var queryText = new StringBuilder();
queryText.Append($"CREATE TABLE {SqlStorage.FQTN(Name, layout.Name)} (");
for (var i = 0; i < layout.FieldCount; i++)
{
var fieldProperties = layout[i];
if (i > 0)
{
queryText.Append(',');
}
queryText.Append(SqlStorage.EscapeFieldName(fieldProperties));
queryText.Append(' ');
switch (fieldProperties.TypeAtDatabase)
{
case DataType.Binary:
if (fieldProperties.MaximumLength <= 0)
{
queryText.Append("LONGBLOB");
}
else if (fieldProperties.MaximumLength <= 255)
{
queryText.Append("TINYBLOB");
}
else if (fieldProperties.MaximumLength <= 65535)
{
queryText.Append("BLOB");
}
else if (fieldProperties.MaximumLength <= 16777215)
{
queryText.Append("MEDIUMBLOB");
}
else
{
queryText.Append("LONGBLOB");
}
break;
case DataType.Bool:
queryText.Append("TINYINT(1)");
break;
case DataType.DateTime:
switch (fieldProperties.DateTimeType)
{
case DateTimeType.Undefined:
case DateTimeType.Native:
queryText.Append("DATETIME");
break;
case DateTimeType.DoubleEpoch:
case DateTimeType.DoubleSeconds:
queryText.Append("DOUBLE");
break;
case DateTimeType.DecimalSeconds:
queryText.Append("DECIMAL(65,30)");
break;
case DateTimeType.BigIntHumanReadable:
case DateTimeType.BigIntTicks:
queryText.Append("BIGINT");
break;
default: throw new NotImplementedException();
}
break;
case DataType.TimeSpan:
switch (fieldProperties.DateTimeType)
{
case DateTimeType.Undefined:
case DateTimeType.Native:
queryText.Append("TIME");
break;
case DateTimeType.DoubleEpoch:
case DateTimeType.DoubleSeconds:
queryText.Append("DOUBLE");
break;
case DateTimeType.DecimalSeconds:
queryText.Append("DECIMAL(65,30)");
break;
case DateTimeType.BigIntHumanReadable:
case DateTimeType.BigIntTicks:
queryText.Append("BIGINT");
break;
default: throw new NotImplementedException();
}
break;
case DataType.Int8:
queryText.Append("TINYINT");
break;
case DataType.Int16:
queryText.Append("SMALLINT");
break;
case DataType.Int32:
queryText.Append("INTEGER");
break;
case DataType.Int64:
queryText.Append("BIGINT");
break;
case DataType.Single:
queryText.Append("FLOAT");
break;
case DataType.Double:
queryText.Append("DOUBLE");
break;
case DataType.Enum:
queryText.Append("BIGINT");
break;
case DataType.UInt8:
queryText.Append("TINYINT UNSIGNED");
break;
case DataType.UInt16:
queryText.Append("SMALLINT UNSIGNED");
break;
case DataType.UInt32:
queryText.Append("INTEGER UNSIGNED");
break;
case DataType.UInt64:
queryText.Append("BIGINT UNSIGNED");
break;
case DataType.User:
case DataType.String:
if (fieldProperties.MaximumLength <= 0)
{
queryText.Append("LONGTEXT");
}
else if (fieldProperties.MaximumLength <= 255)
{
queryText.Append($"VARCHAR({fieldProperties.MaximumLength})");
}
else if (fieldProperties.MaximumLength <= 65535)
{
queryText.Append("TEXT");
}
else if (fieldProperties.MaximumLength <= 16777215)
{
queryText.Append("MEDIUMTEXT");
}
else
{
queryText.Append("LONGTEXT");
}
switch (fieldProperties.StringEncoding)
{
case StringEncoding.Undefined:
case StringEncoding.ASCII:
queryText.Append(" CHARACTER SET latin1 COLLATE latin1_general_ci");
break;
case StringEncoding.UTF8:
queryText.Append($" CHARACTER SET utf8mb4 COLLATE {utf8Charset}_general_ci");
break;
case StringEncoding.UTF16:
queryText.Append(" CHARACTER SET ucs2 COLLATE ucs2_general_ci");
break;
case StringEncoding.UTF32:
queryText.Append(" CHARACTER SET utf32 COLLATE utf32_general_ci");
break;
default: throw new NotSupportedException($"MYSQL Server does not support {fieldProperties.StringEncoding}!");
}
break;
case DataType.Decimal:
if (fieldProperties.MaximumLength > 0)
{
var precision = (int)fieldProperties.MaximumLength;
var scale = (int)((fieldProperties.MaximumLength - precision) * 100);
if (scale >= precision)
{
throw new ArgumentOutOfRangeException(
$"Field {fieldProperties.Name} has an invalid MaximumLength of {precision},{scale}. Correct values range from s,p = 1,0 to 65,30(default value) with 0 < s < p!");
}
queryText.Append($"DECIMAL({precision},{scale})");
}
else
{
queryText.Append("DECIMAL(65,30)");
}
break;
default: throw new NotImplementedException($"Unknown DataType {fieldProperties.DataType}!");
}
if ((fieldProperties.Flags & FieldFlags.AutoIncrement) != 0)
{
queryText.Append(" AUTO_INCREMENT");
}
if ((fieldProperties.Flags & FieldFlags.Unique) != 0)
{
queryText.Append(" UNIQUE");
switch (fieldProperties.TypeAtDatabase)
{
case DataType.Bool:
case DataType.Char:
case DataType.DateTime:
case DataType.Decimal:
case DataType.Double:
case DataType.Enum:
case DataType.Int8:
case DataType.Int16:
case DataType.Int32:
case DataType.Int64:
case DataType.UInt8:
case DataType.UInt16:
case DataType.UInt32:
case DataType.UInt64:
case DataType.Single:
case DataType.TimeSpan:
break;
case DataType.String:
if (fieldProperties.MaximumLength <= 0)
{
throw new NotSupportedException(
$"Unique string fields without length are not supported! Please define Field.MaxLength at tableName {layout.Name} field {fieldProperties.Name}");
}
break;
default: throw new NotSupportedException($"Uniqueness for tableName {layout.Name} field {fieldProperties.Name} is not supported!");
}
}
if (fieldProperties.Description != null)
{
if (fieldProperties.Description.HasInvalidChars(ASCII.Strings.Printable))
{
throw new ArgumentException("Description of field '{0}' contains invalid chars!", fieldProperties.Name);
}
queryText.Append(" COMMENT '" + fieldProperties.Description.Substring(0, 60) + "'");
}
}
if (layout.Identifier.Any())
{
queryText.Append(",PRIMARY KEY(");
var count = 0;
foreach (var field in layout.Identifier)
{
if (count++ > 0)
{
queryText.Append(',');
}
queryText.Append(SqlStorage.EscapeFieldName(field));
}
queryText.Append(')');
}
queryText.Append(')');
if ((flags & TableFlags.InMemory) != 0)
{
queryText.Append(" ENGINE = MEMORY");
}
SqlStorage.Execute(database: Name, table: layout.Name, cmd: queryText.ToString());
try
{
for (var i = 0; i < layout.FieldCount; i++)
{
var field = layout[i];
if ((field.Flags & FieldFlags.ID) != 0)
{
continue;
}
if ((field.Flags & FieldFlags.Index) != 0)
{
string command;
switch (field.DataType)
{
case DataType.Binary:
case DataType.String:
case DataType.User:
var size = (int)field.MaximumLength;
if (size < 1)
{
size = 32;
}
command =
$"CREATE INDEX `idx_{layout.Name}_{field.Name}` ON {SqlStorage.FQTN(Name, layout.Name)} ({SqlStorage.EscapeFieldName(field)} ({size}))";
break;
case DataType.Bool:
case DataType.Char:
case DataType.DateTime:
case DataType.Decimal:
case DataType.Double:
case DataType.Enum:
case DataType.Int16:
case DataType.Int32:
case DataType.Int64:
case DataType.Int8:
case DataType.Single:
case DataType.TimeSpan:
case DataType.UInt16:
case DataType.UInt32:
case DataType.UInt64:
case DataType.UInt8:
command =
$"CREATE INDEX `idx_{layout.Name}_{field.Name}` ON {SqlStorage.FQTN(Name, layout.Name)} ({SqlStorage.EscapeFieldName(field)})";
break;
default: throw new NotSupportedException($"INDEX for datatype of field {field} is not supported!");
}
SqlStorage.Execute(database: Name, table: layout.Name, cmd: command);
}
}
}
catch
{
DeleteTable(layout.Name);
throw;
}
return(GetTable(layout));
}
/// <summary>Adds a new tableName with the specified name.</summary>
/// <param name="layout">Layout of the tableName.</param>
/// <param name="flags">The flags for tableName creation.</param>
/// <returns>Returns an <see cref="ITable" /> instance for the specified tableName.</returns>
public override ITable CreateTable(RowLayout layout, TableFlags flags = default)
{
if (layout == null)
{
throw new ArgumentNullException(nameof(layout));
}
Trace.TraceInformation($"Creating tableName {layout}");
if (layout.Name.HasInvalidChars(ASCII.Strings.SafeName))
{
throw new ArgumentException($"Table name {layout.Name} contains invalid chars!");
}
var queryText = new StringBuilder();
queryText.Append("CREATE ");
if ((flags & TableFlags.InMemory) != 0)
{
queryText.Append("UNLOGGED ");
}
queryText.Append($"TABLE {SqlStorage.FQTN(Name, layout.Name)} (");
for (var i = 0; i < layout.FieldCount; i++)
{
var fieldProperties = layout[i];
if (i > 0)
{
queryText.Append(',');
}
var fieldName = SqlStorage.EscapeFieldName(fieldProperties);
queryText.Append(fieldName);
queryText.Append(' ');
switch (fieldProperties.TypeAtDatabase)
{
case DataType.Binary:
if ((fieldProperties.Flags & FieldFlags.AutoIncrement) != 0)
{
throw new NotSupportedException($"AutoIncrement is not supported on data type {fieldProperties.TypeAtDatabase}");
}
queryText.Append("BYTEA");
break;
case DataType.Bool:
if ((fieldProperties.Flags & FieldFlags.AutoIncrement) != 0)
{
throw new NotSupportedException($"AutoIncrement is not supported on data type {fieldProperties.TypeAtDatabase}");
}
queryText.Append("BOOL");
break;
case DataType.DateTime:
if ((fieldProperties.Flags & FieldFlags.AutoIncrement) != 0)
{
throw new NotSupportedException($"AutoIncrement is not supported on data type {fieldProperties.TypeAtDatabase}");
}
queryText.Append("TIMESTAMP WITH TIME ZONE");
break;
case DataType.TimeSpan:
if ((fieldProperties.Flags & FieldFlags.AutoIncrement) != 0)
{
throw new NotSupportedException($"AutoIncrement is not supported on data type {fieldProperties.TypeAtDatabase}");
}
queryText.Append("FLOAT8");
break;
case DataType.Int8:
if ((fieldProperties.Flags & FieldFlags.AutoIncrement) != 0)
{
throw new NotSupportedException($"AutoIncrement is not supported on data type {fieldProperties.TypeAtDatabase}");
}
queryText.Append("SMALLINT");
break;
case DataType.Int16:
if ((fieldProperties.Flags & FieldFlags.AutoIncrement) != 0)
{
queryText.Append("SMALLSERIAL");
}
else
{
queryText.Append("SMALLINT");
}
break;
case DataType.Int32:
if ((fieldProperties.Flags & FieldFlags.AutoIncrement) != 0)
{
queryText.Append("SERIAL");
}
else
{
queryText.Append("INTEGER");
}
break;
case DataType.Int64:
if ((fieldProperties.Flags & FieldFlags.AutoIncrement) != 0)
{
queryText.Append("BIGSERIAL");
}
else
{
queryText.Append("BIGINT");
}
break;
case DataType.Single:
if ((fieldProperties.Flags & FieldFlags.AutoIncrement) != 0)
{
throw new NotSupportedException($"AutoIncrement is not supported on data type {fieldProperties.TypeAtDatabase}");
}
queryText.Append("FLOAT4");
break;
case DataType.Double:
if ((fieldProperties.Flags & FieldFlags.AutoIncrement) != 0)
{
throw new NotSupportedException($"AutoIncrement is not supported on data type {fieldProperties.TypeAtDatabase}");
}
queryText.Append("FLOAT8");
break;
case DataType.Enum:
if ((fieldProperties.Flags & FieldFlags.AutoIncrement) != 0)
{
throw new NotSupportedException($"AutoIncrement is not supported on data type {fieldProperties.TypeAtDatabase}");
}
queryText.Append("BIGINT");
break;
case DataType.UInt8:
if ((fieldProperties.Flags & FieldFlags.AutoIncrement) != 0)
{
throw new NotSupportedException($"AutoIncrement is not supported on data type {fieldProperties.TypeAtDatabase}");
}
queryText.Append($"SMALLINT CHECK ({fieldName} >= 0 AND {fieldName} <= {byte.MaxValue})");
break;
case DataType.UInt16:
if ((fieldProperties.Flags & FieldFlags.AutoIncrement) != 0)
{
throw new NotSupportedException($"AutoIncrement is not supported on data type {fieldProperties.TypeAtDatabase}");
}
queryText.Append($"INT CHECK ({fieldName} >= 0 AND {fieldName} <= {ushort.MaxValue})");
break;
case DataType.UInt32:
if ((fieldProperties.Flags & FieldFlags.AutoIncrement) != 0)
{
throw new NotSupportedException($"AutoIncrement is not supported on data type {fieldProperties.TypeAtDatabase}");
}
queryText.Append($"BIGINT CHECK ({fieldName} >= 0 AND {fieldName} <= {uint.MaxValue})");
break;
case DataType.UInt64:
if ((fieldProperties.Flags & FieldFlags.AutoIncrement) != 0)
{
throw new NotSupportedException($"AutoIncrement is not supported on data type {fieldProperties.TypeAtDatabase}");
}
queryText.Append($"NUMERIC(20,0) CHECK ({fieldName} >= 0 AND {fieldName} <= {ulong.MaxValue})");
break;
case DataType.User:
case DataType.String:
if ((fieldProperties.Flags & FieldFlags.AutoIncrement) != 0)
{
throw new NotSupportedException($"AutoIncrement is not supported on data type {fieldProperties.TypeAtDatabase}");
}
if (fieldProperties.MaximumLength <= 0)
{
queryText.Append("TEXT");
}
else
{
queryText.Append($"VARCHAR({fieldProperties.MaximumLength})");
}
break;
case DataType.Decimal:
if ((fieldProperties.Flags & FieldFlags.AutoIncrement) != 0)
{
throw new NotSupportedException($"AutoIncrement is not supported on data type {fieldProperties.TypeAtDatabase}");
}
if (fieldProperties.MaximumLength > 0)
{
var precision = (int)fieldProperties.MaximumLength;
var scale = (int)((fieldProperties.MaximumLength - precision) * 100);
if (scale >= precision)
{
throw new ArgumentOutOfRangeException(
$"Field {fieldProperties.Name} has an invalid MaximumLength of {precision},{scale}. Correct values range from s,p = 1,0 to 65,30(default value) with 0 < s < p!");
}
queryText.Append($"DECIMAL({precision},{scale})");
}
else
{
queryText.Append("DECIMAL(65,30)");
}
break;
default: throw new NotImplementedException($"Unknown DataType {fieldProperties.DataType}!");
}
if ((fieldProperties.Flags & FieldFlags.ID) != 0)
{
queryText.Append(" PRIMARY KEY");
}
if ((fieldProperties.Flags & FieldFlags.Unique) != 0)
{
queryText.Append(" UNIQUE");
switch (fieldProperties.TypeAtDatabase)
{
case DataType.Bool:
case DataType.Char:
case DataType.DateTime:
case DataType.Decimal:
case DataType.Double:
case DataType.Enum:
case DataType.Int8:
case DataType.Int16:
case DataType.Int32:
case DataType.Int64:
case DataType.UInt8:
case DataType.UInt16:
case DataType.UInt32:
case DataType.UInt64:
case DataType.Single:
case DataType.TimeSpan:
break;
case DataType.String:
if (fieldProperties.MaximumLength <= 0)
{
throw new NotSupportedException(
$"Unique string fields without length are not supported! Please define Field.MaxLength at tableName {layout.Name} field {fieldProperties.Name}");
}
break;
default: throw new NotSupportedException($"Uniqueness for tableName {layout.Name} field {fieldProperties.Name} is not supported!");
}
}
if (fieldProperties.Description != null)
{
if (fieldProperties.Description.HasInvalidChars(ASCII.Strings.Printable))
{
throw new ArgumentException("Description of field '{0}' contains invalid chars!", fieldProperties.Name);
}
queryText.Append(" COMMENT '" + fieldProperties.Description.Substring(0, 60) + "'");
}
}
queryText.Append(')');
SqlStorage.Execute(database: Name, table: layout.Name, cmd: queryText.ToString());
for (var i = 0; i < layout.FieldCount; i++)
{
var fieldProperties = layout[i];
if ((fieldProperties.Flags & FieldFlags.ID) != 0)
{
continue;
}
if ((fieldProperties.Flags & FieldFlags.Index) != 0)
{
var name = PgSqlStorage.GetObjectName($"idx_{layout.Name}_{fieldProperties.Name}");
var cmd = $"CREATE INDEX {name} ON {SqlStorage.FQTN(Name, layout.Name)} ({SqlStorage.EscapeFieldName(fieldProperties)})";
SqlStorage.Execute(database: Name, table: layout.Name, cmd: cmd);
}
}
return(GetTable(layout));
}
