/// <inheritdoc /> protected override string[] GetTableNames() { var result = new List <string>(); var rows = SqlStorage.Query(database: "information_schema", table: "TABLES", cmd: "SELECT table_name,table_schema,table_type FROM information_schema.TABLES where table_type='BASE TABLE' AND table_schema LIKE " + SqlStorage.EscapeString(Name)); foreach (var row in rows) { result.Add((string)row[0]); } return(result.ToArray()); }
/// <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)); }