public void Drop_Table_Test()
{
var statement = SchemaWriter.CreateDeleteTableStatement(TableName);
statement.Result.ShouldEqual(Statement.ResultType.None);
statement.Text.ShouldEqual("IF EXISTS (SELECT * FROM [sys].[tables] WHERE [name] = 'some_table_in_the_db') DROP TABLE [some_table_in_the_db]");
}
public void Create_Non_Clustered_Index_Test()
{
var statement = SchemaWriter.CreateAddNonClusteredIndexStatement(TableName, new Index.Column("Created"), new Index.Column("Id"));
statement.Result.ShouldEqual(Statement.ResultType.None);
statement.Text.ShouldEqual("CREATE NONCLUSTERED INDEX [IX_some_table_in_the_db_Created_Id] ON [some_table_in_the_db] ([Created] ASC, [Id] ASC)");
}
public void Table_Exists_Test()
{
var statement = SchemaWriter.CreateTableExistsStatement(TableName);
statement.Result.ShouldEqual(Statement.ResultType.Scalar);
statement.Text.ShouldEqual("SELECT CAST(CASE WHEN EXISTS (SELECT * FROM [sys].[tables] WHERE [name] = 'some_table_in_the_db') THEN 1 ELSE 0 END AS bit)");
}
public IEnumerable <Index> GetIndexes(string tableName)
{
var indexes = new List <Index>();
using (var reader = Command.Create(SchemaWriter.CreateGetIndexesStatement(tableName), _profiler).ExecuteReader(_connectionManager))
{
Index index = null;
Index.ColumnSet columns = null;
while (reader.Read())
{
var name = (string)reader[TransactSql.System.Indexes.Name];
if (index == null || index.Name != name)
{
columns = new Index.ColumnSet();
index = new Index(name,
(byte)reader[TransactSql.System.Indexes.Type] == 1,
(bool)reader[TransactSql.System.Indexes.IsUnique],
(bool)reader[TransactSql.System.Indexes.IsPrimaryKey], columns);
indexes.Add(index);
}
columns.Add(
(string)reader[SqlWriter.Aliases.ColumnName],
(bool)reader[TransactSql.System.IndexColumns.IsDescendingKey]);
}
}
return(indexes);
}
public void Drop_Index_Test()
{
var statement = SchemaWriter.CreateRemoveNonClusteredIndexStatement(TableName, "Created_Index");
statement.Result.ShouldEqual(Statement.ResultType.None);
statement.Text.ShouldEqual("IF EXISTS (SELECT * FROM [sys].[indexes] WHERE [object_id] = OBJECT_ID(N'some_table_in_the_db') AND [name] = 'Created_Index') DROP INDEX [Created_Index] ON [some_table_in_the_db]");
}
public void should_generate_proper_create_table_columns_sql()
{
var select = new Select {
From = { Type = Data.DataType.Query,
Queries = new List <Select> {
new Select {
From = { Type = Data.DataType.Table,
Table = new Table {
Name = TableName1
} }
}
} }
};
var statement = SchemaWriter.CreateSharedColumnsStatement(select);
statement.Result.ShouldEqual(Statement.ResultType.Multiple);
statement.Text.ShouldEqual("SELECT [SC].[name], [SC].[system_type_id], CAST(CASE WHEN [SC].[system_type_id] IN (239, 231, 99) THEN [SC].[max_length] / 2 ELSE [SC].[max_length] END AS smallint) AS [max_length], [SC].[is_nullable], [SC].[is_identity], " +
"CAST((CASE OBJECT_DEFINITION([SC].[default_object_id]) WHEN '(GETDATE())' THEN 1 WHEN '(NEWID())' THEN 1 WHEN '(NEWSEQUENTIALID())' THEN 1 ELSE 0 END) AS bit) AS [is_auto_generated], " +
"CASE OBJECT_DEFINITION([SC].[default_object_id]) WHEN '(GETDATE())' THEN NULL WHEN '(NEWID())' THEN NULL WHEN '(NEWSEQUENTIALID())' THEN NULL ELSE REPLACE(REPLACE(OBJECT_DEFINITION([SC].[default_object_id]), '(', ''), ')', '') END AS [default_value], " +
"ISNULL([SI].[is_primary_key], 0) AS [is_primary_key], CAST((CASE [SI].[type] WHEN 1 THEN 1 ELSE 0 END) AS bit) AS [is_primary_key_clustered], [SC].[precision], [SC].[scale], [SCC].[definition] AS [computation], [SCC].[is_persisted] AS [persisted_computation], " +
"CAST(CASE WHEN [SC].[system_type_id] < (SELECT MAX([system_type_id]) FROM [sys].[columns] WHERE [name] = [__SubQuery__].[name] AND [system_type_id] IN (175, 167, 35, 239, 231, 99) AND [object_id] IN (OBJECT_ID(N'XLIST_1'))) THEN 1 ELSE 0 END AS bit) AS [is_narrowing] " +
"FROM ( ( ( [sys].[columns] [SC] LEFT JOIN [sys].[index_columns] [SIC] ON [SC].[column_id] = [SIC].[column_id] AND [SC].[object_id] = [SIC].[object_id] ) LEFT JOIN [sys].[indexes] [SI] ON [SI].[index_id] = [SIC].[index_id] AND " +
"[SI].[object_id] = [SIC].[object_id] ) LEFT JOIN [sys].[computed_columns] [SCC] ON [SC].[column_id] = [SCC].[column_id] AND [SC].[object_id] = [SCC].[object_id] ) JOIN (SELECT [name], " +
"CASE [system_type_id] WHEN 167 THEN 231 WHEN 175 THEN 239 WHEN 35 THEN 99 ELSE [system_type_id] END AS [system_type_id], CASE [user_type_id] WHEN 167 THEN 231 WHEN 175 THEN 239 WHEN 35 THEN 99 ELSE [user_type_id] END AS [user_type_id] " +
"FROM [sys].[columns] WHERE [object_id] = OBJECT_ID(N'XLIST_1')) [__SubQuery__] ON [__SubQuery__].[name] = [SC].[name] AND [SC].[object_id] = OBJECT_ID(N'XLIST_1')");
}
public void Drop_Column_Test()
{
var statement = SchemaWriter.CreateRemoveColumnStatement(TableName, "Created");
statement.Result.ShouldEqual(Statement.ResultType.None);
statement.Text.ShouldEqual("IF EXISTS (SELECT * FROM [sys].[columns] WHERE [object_id] = OBJECT_ID(N'some_table_in_the_db') AND [name] = 'Created') ALTER TABLE [some_table_in_the_db] DROP COLUMN [Created]");
}
public void SchemaWriterTest()
{
var strategy = A.Fake<IStrategy<Document>>();
var schemaWriter = new SchemaWriter(strategy);
Assert.IsNotNull(schemaWriter);
}
public void should_generate_proper_get_indexes_sql()
{
var statement = SchemaWriter.CreateGetIndexesStatement("XLIST_1");
statement.Result.ShouldEqual(Statement.ResultType.Multiple);
statement.Text.ShouldEqual("SELECT [SI].[name], [SI].[type], [SI].[is_unique], [SI].[is_primary_key], [SC].[name] AS [column_name], [SIC].[is_descending_key] FROM [sys].[indexes] [SI] JOIN [sys].[index_columns] [SIC] ON " +
"[SI].[object_id] = [SIC].[object_id] AND [SI].[index_id] = [SIC].[index_id] JOIN [sys].[columns] [SC] ON [SIC].[object_id] = [SC].[object_id] AND [SIC].[column_id] = [SC].[column_id] WHERE [SI].[object_id] = OBJECT_ID(N'XLIST_1') ORDER BY [SI].[name]");
}
public void Add_Sql_Type_Column_Test()
{
var statement = SchemaWriter.CreateAddColumnStatement(TableName,
new Column("Created", sqlType: SqlDbType.DateTime, isNullable: false, isAutoGenerated: true));
statement.Result.ShouldEqual(Statement.ResultType.None);
statement.Text.ShouldEqual("ALTER TABLE [some_table_in_the_db] ADD [Created] datetime NOT NULL DEFAULT GETDATE()");
}
private static void WriteSqlFile(FileInfo daoFile, FileInfo sqlFile, SqlDialect dialect)
{
Assembly daoAssembly = Assembly.LoadFrom(daoFile.FullName);
SchemaWriter schemaWriter = SchemaWriters[dialect]();
schemaWriter.WriteSchemaScript(daoAssembly);
schemaWriter.ToString().SafeWriteToFile(sqlFile.FullName);
}
public void Create_Table_Identity_Test()
{
var statement = SchemaWriter.CreateTableCreateStatement(TableName,
new Column("Id", typeof(int), isIdentity: true, key: Column.KeyType.PrimaryKey));
statement.Result.ShouldEqual(Statement.ResultType.None);
statement.Text.ShouldEqual("CREATE TABLE [some_table_in_the_db] ([Id] int IDENTITY(1,1) NOT NULL, CONSTRAINT [PK_some_table_in_the_db_Id] PRIMARY KEY ([Id] ASC))");
}
public void Create_Table_with_decimal_precision_and_scale_Test()
{
var statement = SchemaWriter.CreateTableCreateStatement(TableName,
new Column("Value", typeof(decimal), precision: 5, scale: 1));
statement.Result.ShouldEqual(Statement.ResultType.None);
statement.Text.ShouldEqual("CREATE TABLE [some_table_in_the_db] ([Value] decimal (5, 1) NOT NULL)");
}
public void Create_Table_with_persisted_computation_Test()
{
var statement = SchemaWriter.CreateTableCreateStatement(TableName,
new Column("Value", typeof(string), computation: "1 + 1", computationPersisted: true));
statement.Result.ShouldEqual(Statement.ResultType.None);
statement.Text.ShouldEqual("CREATE TABLE [some_table_in_the_db] ([Value] AS (1 + 1) PERSISTED NOT NULL)");
}
public void SetUp()
{
SchemaWriter.RemovePersistedConfiguration();
DatabaseDriver.Bootstrap(true);
container = DatabaseDriver.ContainerWithDatabase();
source = container.GetInstance <IConfigurationSource>();
}
public override async Task <SchemaResult> GetSchema(Protocol.FlightDescriptor request, ServerCallContext context)
{
var flightDescriptor = new FlightDescriptor(request);
var schema = await _flightServer.GetSchema(flightDescriptor, context).ConfigureAwait(false);
return(new SchemaResult()
{
Schema = SchemaWriter.SerializeSchema(schema)
});
}
public override void Initialize(DbContext context, IEntity entity)
{
var writer = new SchemaWriter();
var cmd = new Query(new QueryOptions(entity.Table)
{
Query = writer.Write(entity)
});
context.Database.Execute(cmd);
}
public static void Cleanup(HashSet <Database> testDatabases)
{
testDatabases.Each(db =>
{
SchemaWriter dropper = db.ServiceProvider.Get <SchemaWriter>();
dropper.EnableDrop = true;
dropper.DropAllTables <TestTable>();
db.ExecuteSql(dropper);
});
}
/// <summary>
/// Serialize a message. (See <see cref="IAsyncSerializer{T}.SerializeAsync(T, SerializationContext)" />.)
/// </summary>
public virtual async Task <byte[]> SerializeAsync(T data, SerializationContext context)
{
var serialize = await(_cache.GetOrAdd(SubjectNameBuilder(context), async subject =>
{
int id;
Action <T, Stream> @delegate;
try
{
var existing = await _resolve(subject).ConfigureAwait(false);
var schema = SchemaReader.Read(existing.SchemaString);
@delegate = SerializerBuilder.BuildDelegate <T>(schema);
id = existing.Id;
}
catch (Exception e) when(RegisterAutomatically && (
(e is SchemaRegistryException sre && sre.ErrorCode == 40401) ||
(e is AggregateException a && a.InnerExceptions.All(i =>
i is UnsupportedSchemaException ||
i is UnsupportedTypeException
))
))
{
var schema = SchemaBuilder.BuildSchema <T>();
var json = SchemaWriter.Write(schema);
@delegate = SerializerBuilder.BuildDelegate <T>(schema);
id = await _register(subject, json).ConfigureAwait(false);
}
var bytes = BitConverter.GetBytes(id);
if (BitConverter.IsLittleEndian)
{
Array.Reverse(bytes);
}
return(value =>
{
var stream = new MemoryStream();
using (stream)
{
stream.WriteByte(0x00);
stream.Write(bytes, 0, bytes.Length);
@delegate(value, stream);
}
return stream.ToArray();
});
})).ConfigureAwait(false);
return(serialize(data));
}
public void Create_Table__With_Sql_Types_Test()
{
var statement = SchemaWriter.CreateTableCreateStatement(TableName,
new Column("Id", sqlType: SqlDbType.UniqueIdentifier, key: Column.KeyType.PrimaryKey, isAutoGenerated: true),
new Column("Name", sqlType: SqlDbType.NVarChar, length: 500, isNullable: true),
new Column("Active", sqlType: SqlDbType.Bit, isNullable: false, defaultValue: true),
new Column("Created", sqlType: SqlDbType.DateTime, isNullable: false, isAutoGenerated: true));
statement.Result.ShouldEqual(Statement.ResultType.None);
statement.Text.ShouldEqual("CREATE TABLE [some_table_in_the_db] ([Id] uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(), [Name] nvarchar (500) NULL, [Active] bit NOT NULL DEFAULT 1, [Created] datetime NOT NULL DEFAULT GETDATE(), CONSTRAINT [PK_some_table_in_the_db_Id] PRIMARY KEY ([Id] ASC))");
}
public IEnumerable <Column> GetColumns(string tableName)
{
var statement = SchemaWriter.CreateTableColumnsStatement(tableName);
var columns = new List <Column>();
using (var reader = Command.Create(statement, _profiler).ExecuteReader(_connectionManager))
while (reader.Read())
{
columns.Add(ColumnFactory(reader));
}
return(columns);
}
public void should_generate_proper_get_columns_sql()
{
var statement = SchemaWriter.CreateTableColumnsStatement("XLIST_1");
statement.Result.ShouldEqual(Statement.ResultType.Multiple);
statement.Text.ShouldEqual("SELECT [SC].[name], [SC].[system_type_id], CAST(CASE WHEN [SC].[system_type_id] IN (239, 231, 99) THEN [SC].[max_length] / 2 ELSE [SC].[max_length] END AS smallint) AS [max_length], [SC].[is_nullable], [SC].[is_identity], " +
"CAST((CASE OBJECT_DEFINITION([SC].[default_object_id]) WHEN '(GETDATE())' THEN 1 WHEN '(NEWID())' THEN 1 WHEN '(NEWSEQUENTIALID())' THEN 1 ELSE 0 END) AS bit) AS [is_auto_generated], " +
"CASE OBJECT_DEFINITION([SC].[default_object_id]) WHEN '(GETDATE())' THEN NULL WHEN '(NEWID())' THEN NULL WHEN '(NEWSEQUENTIALID())' THEN NULL ELSE REPLACE(REPLACE(OBJECT_DEFINITION([SC].[default_object_id]), '(', ''), ')', '') END AS [default_value], " +
"ISNULL([SI].[is_primary_key], 0) AS [is_primary_key], CAST((CASE [SI].[type] WHEN 1 THEN 1 ELSE 0 END) AS bit) AS [is_primary_key_clustered], [SC].[precision], [SC].[scale], [SCC].[definition] AS [computation], [SCC].[is_persisted] AS [persisted_computation] " +
"FROM ( ( ( [sys].[columns] [SC] LEFT JOIN [sys].[index_columns] [SIC] ON [SC].[column_id] = [SIC].[column_id] AND [SC].[object_id] = [SIC].[object_id] ) LEFT JOIN [sys].[indexes] [SI] ON [SI].[index_id] = [SIC].[index_id] AND " +
"[SI].[object_id] = [SIC].[object_id] ) LEFT JOIN [sys].[computed_columns] [SCC] ON [SC].[column_id] = [SCC].[column_id] AND [SC].[object_id] = [SCC].[object_id] ) WHERE [SC].[object_id] = OBJECT_ID(N'XLIST_1')");
}
public void Columns_Test()
{
var query = MockQueryable <Entity> .Create(TableName1);
query.Union(MockQueryable <Entity> .Create(TableName2).Take(5).Union(MockQueryable <Entity> .Create(TableName3).Skip(4).OrderBy(x => x.Active)));
var statement = SchemaWriter.CreateUnionColumnsStatement(QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select);
statement.Parameters.Count().ShouldEqual(0);
statement.Text.ShouldEqual("SELECT [name], CASE [system_type_id] WHEN 167 THEN 231 WHEN 175 THEN 239 WHEN 35 THEN 99 ELSE [system_type_id] END AS [system_type_id], CASE [user_type_id] WHEN 167 THEN 231 WHEN 175 THEN 239 WHEN 35 THEN 99 ELSE [user_type_id] END AS [user_type_id] FROM [sys].[columns] WHERE [object_id] = OBJECT_ID(N'XLIST_1') INTERSECT " +
"SELECT [name], CASE [system_type_id] WHEN 167 THEN 231 WHEN 175 THEN 239 WHEN 35 THEN 99 ELSE [system_type_id] END AS [system_type_id], CASE [user_type_id] WHEN 167 THEN 231 WHEN 175 THEN 239 WHEN 35 THEN 99 ELSE [user_type_id] END AS [user_type_id] FROM [sys].[columns] WHERE [object_id] = OBJECT_ID(N'XLIST_2') INTERSECT " +
"SELECT [name], CASE [system_type_id] WHEN 167 THEN 231 WHEN 175 THEN 239 WHEN 35 THEN 99 ELSE [system_type_id] END AS [system_type_id], CASE [user_type_id] WHEN 167 THEN 231 WHEN 175 THEN 239 WHEN 35 THEN 99 ELSE [user_type_id] END AS [user_type_id] FROM [sys].[columns] WHERE [object_id] = OBJECT_ID(N'XLIST_3')");
}
public void WriteTest()
{
var strategy = A.Fake<IStrategy<Document>>();
var schemaWriter = new SchemaWriter(strategy);
var database = new Document();
schemaWriter.Write(database);
A.CallTo(() => strategy.Execute(A<Document>.Ignored)).MustHaveHappened();
}
public void SchemaWriter(string schemaname, ScriptMode mode, DbDialect dialect, string test)
{
var schema = new Schema {
Name = schemaname
};
var writer = new SchemaWriter(schema)
{
Mode = mode, Dialect = dialect, NoComment = true, NoDelimiter = true
};
Assert.AreEqual(test, writer.ToString().Trim());
}
private static void DropAllTables(Database reproduceIn)
{
SchemaWriter dropper = reproduceIn.ServiceProvider.GetNew <SchemaWriter>();
dropper.EnableDrop = true;
dropper.DropAllTables <Item>();
Exception ex = null;
if (!dropper.TryExecute(reproduceIn, out ex))
{
Out(ex.Message, ConsoleColor.Yellow);
}
}
public static void CrudSqlClient()
{
Dao.ProxyConnection(typeof(Item), "CRUD_SqlClient");
SqlClientRegistrar.Register <Item>();
Database db = Db.For <Item>();
SchemaWriter sw = db.ServiceProvider.GetNew <SchemaWriter>();
sw.EnableDrop = true;
sw.DropAllTables <Item>();
sw.TryExecute(db);
sw.Reset();
sw.WriteSchemaScript <Item>();
Exception ex;
sw.TryExecute(db, out ex);
Expect.IsNull(ex);
// Create
string name = "".RandomString(8);
Item created = new Item();
created.Name = name;
created.Commit();
// Retrieve
ItemCollection results = Item.Where(p => p.Name == name);
Expect.AreEqual(1, results.Count);
// Update
Item check = results[0];
check.Name = "".RandomString(8);
check.Commit();
results = Item.Where(p => p.Name == name);
Expect.AreEqual(0, results.Count);
results = Item.Where(p => p.Name == check.Name);
Expect.AreEqual(1, results.Count);
// Delete
check.Delete();
results = Item.Where(p => p.Name == name);
Expect.AreEqual(0, results.Count);
results = Item.Where(p => p.Name == check.Name);
Expect.AreEqual(0, results.Count);
}
public void SchemaWriterWithComments_COVER_PROPOSE()
{
var schema = new Schema { Name = "x"};
var writer = new SchemaWriter(schema) { Mode = ScriptMode.Create, Dialect = DbDialect.SqlServer, Comment = "Multi line\r\ncomment" };
var result = writer.ToString().Trim();
var dig = writer.GetDigest().Trim();
Console.WriteLine(result);
Assert.AreEqual(@"-- begin command SchemaWriter
-- Multi line
-- comment
if (SCHEMA_ID('x') is null) exec sp_executesql N'CREATE SCHEMA x';
GO", result);
Assert.AreEqual(@"Schema x (C,S,R, Multi line; comment)", dig);
}
public void CanWriteSchema(string fileNameStem, JsonSchema schema)
{
string expected = TestUtil.ReadTestDataFile(fileNameStem);
string actual = null;
using (var writer = new StringWriter())
{
SchemaWriter.WriteSchema(writer, schema, Formatting.Indented);
actual = writer.ToString();
}
actual.Should().Be(expected);
}
internal Protocol.FlightInfo ToProtocol()
{
var serializedSchema = SchemaWriter.SerializeSchema(Schema);
var response = new Protocol.FlightInfo()
{
Schema = serializedSchema,
FlightDescriptor = Descriptor.ToProtocol()
};
foreach (var endpoint in Endpoints)
{
response.Endpoint.Add(endpoint.ToProtocol());
}
return(response);
}
private TResult ExecuteQuery <TResult>(Select select)
{
IEnumerable <string> columns = null;
if (select.From.HasQueries)
{
var columnsStatement = SchemaWriter.CreateUnionColumnsStatement(select);
columns = Command.Create(columnsStatement, _profiler)
.ExecuteEnumerable <string>(_connectionManager);
}
var selectStatement = SelectWriter <TEntity> .CreateStatement(
select, _map, columns, _noLock);
return((TResult)(new Loader <TEntity>(Command.Create(selectStatement,
_profiler), _map).Execute(_connectionManager)));
}
public void Write()
{
var schema = Schema.Create();
var e = schema.Entity <Record>().Auto();
e.Key.Partition(x => new { x.Id, x.When });
e.Column(x => x.When).Quantum(1, Unit.Days)
.Sort(Order.Desc);
var sut = new SchemaWriter();
var query = sut.Write(e);
Assert.AreEqual(CreateRecord, query);
}
/// <summary>
/// Serialize a message. (See <see cref="IAsyncSerializer{T}.SerializeAsync(T, SerializationContext)" />.)
/// </summary>
public virtual async Task <byte[]> SerializeAsync(T data, SerializationContext context)
{
var subject = SubjectNameBuilder(context);
Task <Func <T, byte[]> > task;
lock (_cache)
{
if (!_cache.TryGetValue(subject, out task) || task.IsFaulted)
{
_cache[subject] = task = ((Func <string, Task <Func <T, byte[]> > >)(async subject =>
{
switch (RegisterAutomatically)
{
case AutomaticRegistrationBehavior.Always:
var schema = SchemaBuilder.BuildSchema <T>();
var id = await _register(subject, SchemaWriter.Write(schema)).ConfigureAwait(false);
return(Build(id, schema));
case AutomaticRegistrationBehavior.Never:
var existing = await _resolve(subject).ConfigureAwait(false);
return(Build(existing.Id, SchemaReader.Read(existing.SchemaString)));
default:
throw new ArgumentOutOfRangeException(nameof(RegisterAutomatically));
}
}))(subject);
}
}
var serialize = await task.ConfigureAwait(false);
if (data == null && TombstoneBehavior != TombstoneBehavior.None)
{
if (context.Component == MessageComponentType.Value || TombstoneBehavior != TombstoneBehavior.Strict)
{
return(null);
}
}
return(serialize(data));
}
public void SchemaWriter(string schemaname, ScriptMode mode, DbDialect dialect, string test){
var schema = new Schema{Name = schemaname};
var writer = new SchemaWriter(schema){Mode = mode, Dialect = dialect,NoComment=true,NoDelimiter=true};
Assert.AreEqual(test,writer.ToString().Trim());
}
