/// <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));
}
/// <summary>Initializes a new instance of the <see cref="PgSqlDatabase" /> class.</summary>
/// <param name="storage">the postgre sql storage engine.</param>
/// <param name="name">the name of the database.</param>
public PgSqlDatabase(PgSqlStorage storage, string name)
: base(storage, PgSqlStorage.GetObjectName(name))
{
}