public void Can_Query_For_Data()
{
using (var dataProvider = Sqlite3DataProvider.InMemory())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw(
"CREATE TABLE TestTable (Value TEXT)"
));
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw(
"INSERT INTO TestTable VALUES ('Hello World')"
));
var table = QueryExpression.Table("TestTable");
using (var queryResult = dataProvider.ExecuteReader(QueryExpression.Select(
projection: new[] { QueryExpression.Column("Value", table) },
from: table
)))
{
Assert.IsTrue(queryResult.HasRows);
Assert.IsTrue(queryResult.Read());
Assert.AreEqual("Hello World", queryResult.GetString(0));
}
}
}
public void Can_Select_Bound_Complex_Type_With_Transform()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Has_Complex_Type>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Has_Complex_Type> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Complex_A] INTEGER,
[Complex_B] INTEGER
)" ));
var a = 2;
var b = 4;
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName),
new[] { QueryExpression.Column("Complex_A"), QueryExpression.Column("Complex_B") },
new[] { QueryExpression.Value(a), QueryExpression.Value(b) }
));
var binding = new TypeBindingBuilder <Has_Complex_Type, Custom_View>()
.Bind(q => q.Sum, q => q.Complex, o => o.A + o.B)
.Bind(q => q.Product, q => q.Complex, o => o.A * o.B)
.BuildBinding();
var dataSet = new SqlDataSet <Has_Complex_Type>(sqlDataModel, dataProvider);
var result = dataSet.Select <Custom_View>(binding).ToSingle();
Assert.AreEqual(a + b, result.Sum);
Assert.AreEqual(a * b, result.Product);
}
}
public void Select_View_And_Expression_Returns_Zipped_DataSet()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Flat_Entity>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Flat_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] INTEGER
)" ));
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName),
new[] { QueryExpression.Column("Value") },
new[] { QueryExpression.Value(2) },
new[] { QueryExpression.Value(4) }
));
var dataSet = new SqlDataSet <Flat_Entity>(sqlDataModel, dataProvider);
var valueSet = dataSet
.Select(q => q.Id)
.Select <Flat_Entity_Value_View>()
.ToList();
Assert.IsTrue(
valueSet.Select(q => q.Item1).SequenceEqual(new[] { 1, 2 })
);
Assert.IsTrue(
valueSet.Select(q => q.Item2.Value).SequenceEqual(new[] { 2, 4 })
);
}
}
public void Insert_Flat_View_With_Binding_And_Transform()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Flat_Entity>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Flat_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Value] INTEGER
)" ));
var dataSet = new SqlDataSet <Flat_Entity>(sqlDataModel, dataProvider);
var value = 5;
var view = new { Custom = value.ToString() };
var transformWasCalled = false;
dataSet.Insert(view, binding =>
binding.Bind(t => t.Value, s => s.Custom, value =>
{
transformWasCalled = true;
return(int.Parse(value));
})).Execute();
Assert.IsTrue(transformWasCalled);
using (var result = dataProvider.ExecuteReader(QueryExpression.Select(new[] { QueryExpression.All() }, from: QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName))))
{
Assert.IsTrue(result.HasRows);
Assert.IsTrue(result.Read());
Assert.AreEqual(value, result.GetInt32(0));
}
}
}
public void Batch_Sql_Operates()
{
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE Entities
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] INTEGER
)" ));
var context = DataContext.Create <TestDataContext>(dataProvider);
var newEntityOne = new EntityType {
Value = 2
};
var newEntityTwo = new EntityType {
Value = 3
};
var retrievedEntity = new EntityType();
Batch.Create()
.Insert(context.Entities.Insert(newEntityOne))
.Insert(context.Entities.Insert(newEntityTwo))
.Update(context.Entities.Update().Set(q => q.Value, q => q.Value + 1))
.Inject(context.Entities.Select().AndWhere(q => q.Id == 2), retrievedEntity)
.Delete(context.Entities.Delete().AndWhere(q => q.Id == 1))
.List(context.Entities.Select(q => q.Value), out var fullValueSetResult)
.Single(context.Entities.Select(q => q.Id), out var singleIdResult)
.Execute();
Assert.AreEqual(2, retrievedEntity.Id);
Assert.AreEqual(4, retrievedEntity.Value);
Assert.IsTrue(new[] { 4 }.SequenceEqual(fullValueSetResult.Result));
Assert.AreEqual(2, singleIdResult.Result);
}
}
public void Can_Update_Field_To_Null_With_Entity()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Flat_Entity>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Flat_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] TEXT
)" ));
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName),
new[] { QueryExpression.Column("Value") },
new[] { QueryExpression.Value("Hello") }
));
var dataSet = new SqlDataSet <Flat_Entity>(sqlDataModel, dataProvider);
var entity = new Flat_Entity {
Id = 1, Value = null
};
dataSet.Update(entity).Execute();
using (var result = dataProvider.ExecuteReader(QueryExpression.Select(new[] { QueryExpression.All() }, from: QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName))))
{
Assert.IsTrue(result.HasRows);
Assert.IsTrue(result.Read());
Assert.IsTrue(result.IsDBNull(1));
}
}
}
public void Can_Rollback_By_Disposing_Transaction()
{
using (var dataProvider = Sqlite3DataProvider.InMemory())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw(
"CREATE TABLE TestTable (Value TEXT)"
));
using (var transaction = dataProvider.CreateTransaction())
{
transaction.ExecuteNonQuery(Sqlite3QueryExpression.Raw(
"INSERT INTO TestTable VALUES ('Hello World')"
));
}
using (var queryResult = dataProvider.ExecuteReader(QueryExpression.Select(
projection: new[] { QueryExpression.All() },
from: QueryExpression.Table("TestTable")
)))
{
Assert.IsFalse(queryResult.HasRows);
}
}
}
public void Insert_Complex_Entity_With_Relationship()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Flat_Entity>()
.AddSqlEntity <Complex_Entity>()
.Build();
var flatDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Flat_Entity> >().First();
var complexDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Complex_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {flatDataModel.StorageModel.DefaultTableName}
(
[Value] INTEGER,
[Id] INTEGER PRIMARY KEY AUTOINCREMENT
)" ));
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {complexDataModel.StorageModel.DefaultTableName}
(
[Value] INTEGER,
[Flat_Id] INTEGER
)" ));
var flatDataSet = new SqlDataSet <Flat_Entity>(flatDataModel, dataProvider);
var complexDataSet = new SqlDataSet <Complex_Entity>(complexDataModel, dataProvider);
// the ID here isn't written to the INSERT query because the ORM see's it as server generated (which it should be)
var flatEntity = new Flat_Entity {
Id = 1, Value = 5
};
var complexEntity = new Complex_Entity {
Value = 6, Flat = flatEntity
};
flatDataSet.Insert(flatEntity).Execute();
complexDataSet.Insert(complexEntity).Execute();
using (var result = dataProvider.ExecuteReader(QueryExpression.Select(
new[] { QueryExpression.All() },
from: QueryExpression.Table(complexDataModel.StorageModel.DefaultTableName),
joins: new[]
{
QueryExpression.Join(
QueryExpression.Table(flatDataModel.StorageModel.DefaultTableName),
QueryExpression.AreEqual(
QueryExpression.Column("Flat_Id", QueryExpression.Table(complexDataModel.StorageModel.DefaultTableName)),
QueryExpression.Column("Id", QueryExpression.Table(flatDataModel.StorageModel.DefaultTableName))
), JoinDirection.Left
)
})))
{
Assert.IsTrue(result.HasRows);
Assert.IsTrue(result.Read());
Assert.AreEqual(complexEntity.Value, result.GetInt32(0));
Assert.AreEqual(complexEntity.Flat.Id, result.GetInt32(1));
Assert.AreEqual(flatEntity.Value, result.GetInt32(2));
Assert.AreEqual(flatEntity.Id, result.GetInt32(3));
}
}
}
public void Select_Entity_With_Complex_Join_Returns_Entity()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Really_Complex_Entity>()
.AddSqlEntity <Complex_Entity>()
.AddSqlEntity <Flat_Entity>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Really_Complex_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE Flat_Entity
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] INTEGER
)" ));
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE Complex_Entity
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] INTEGER,
[Flat_Id] INTEGER
)" ));
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] INTEGER,
[Complex_Id] INTEGER,
[Flat_Id] INTEGER
)" ));
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table("Flat_Entity"),
new[] { QueryExpression.Column("Value") },
new[] { QueryExpression.Value(7) },
new[] { QueryExpression.Value(8) }
));
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table("Complex_Entity"),
new[] { QueryExpression.Column("Value"), QueryExpression.Column("Flat_Id") },
new[] { QueryExpression.Value(6), QueryExpression.Value(1) }
));
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName),
new[] { QueryExpression.Column("Value"), QueryExpression.Column("Flat_Id"), QueryExpression.Column("Complex_Id") },
new[] { QueryExpression.Value(5), QueryExpression.Value(2), QueryExpression.Value(1) }
));
var dataSet = new SqlDataSet <Really_Complex_Entity>(sqlDataModel, dataProvider);
var entitySet = dataSet.Select().ToList();
var entity = entitySet[0];
Assert.AreEqual(1, entity.Id);
Assert.AreEqual(5, entity.Value);
Assert.AreEqual(2, entity.Flat.Id);
Assert.AreEqual(8, entity.Flat.Value);
Assert.AreEqual(1, entity.Complex.Id);
Assert.AreEqual(6, entity.Complex.Value);
Assert.AreEqual(1, entity.Complex.Flat.Id);
Assert.AreEqual(7, entity.Complex.Flat.Value);
}
}
