public async Task VerifyHasTableWorks()
{
DropTable.For <Player>().TryExecute(Connection);
await Table.Ensure <Player>(Connection);
await InsertInto.Row(Create("Willem")).ExecuteAsync(Connection);
await InsertInto.Row(Create("Willemse")).ExecuteAsync(Connection);
await InsertInto.Row(Create("Bastiaan")).ExecuteAsync(Connection);
await InsertInto.Row(Create("Harry")).ExecuteAsync(Connection);
Console.WriteLine(Select <Player> .All().WhereContains(x => x.Name, "illem").Format(SqlFormat.MySql));
var result = Select <Player> .All().WhereContains(x => x.Name, "illem").ExecuteRead(Connection).ToList();
Assert.That(result, Has.Count.EqualTo(2));
result = Select <Player> .All().WhereStartsWith(x => x.Name, "bas").ExecuteRead(Connection).ToList();
Assert.That(result, Has.Count.EqualTo(1));
Assert.That(result[0].Name, Is.EqualTo("Bastiaan"));
result = Select <Player> .All().WhereEndsWith(x => x.Name, "ry").ExecuteRead(Connection).ToList();
Assert.That(result, Has.Count.EqualTo(1));
Assert.That(result[0].Name, Is.EqualTo("Harry"));
}
public void VerifyHasTableWorks()
{
DropTable.For <Player>().TryExecute(Connection);
Assert.That(HasTable.For <Player>().ExecuteCheck(Connection), Is.False);
CreateTable.For <Player>().Execute(Connection);
Assert.That(HasTable.For <Player>().ExecuteCheck(Connection), Is.True);
}
public void WhenRecordInsertedWithAutoIncrementIdentityGetsUpdated()
{
DropTable.For <TableWithIncrement>().TryExecute(Connection);
CreateTable.For <TableWithIncrement>().Execute(Connection);
var first = new TableWithIncrement
{
Value = "Foo"
};
var second = new TableWithIncrement
{
Value = "Bar"
};
var numberFirst = InsertInto.Row(first).GetLastInsertIdentity <ulong>().ExecuteRead(Connection).Single();
var numberSecond = InsertInto.Row(second).GetLastInsertIdentity <ulong>().ExecuteRead(Connection).Single();
Assert.That(numberFirst, Is.EqualTo(1));
Assert.That(numberSecond, Is.EqualTo(2));
var firstSelected = Select.From <TableWithIncrement>().WhereEqual(x => x.Identifier, 1UL)
.ExecuteRead(Connection)
.SingleOrDefault();
Assert.IsNotNull(firstSelected);
Assert.That(firstSelected.Identifier, Is.EqualTo(1UL));
Assert.That(firstSelected.Value, Is.EqualTo("Foo"));
}
public void WhenRecordsInsertedTheyCanGetUpdated()
{
DropTable.For <Player>().TryExecute(Connection);
CreateTable.For <Player>().Execute(Connection);
var player = new Player
{
Age = 10,
Birthday = DateTime.Now,
Identifier = Guid.NewGuid(),
Name = "Jim"
};
Assert.That(InsertInto.Row(player).TryExecute(Connection));
var records = Select.From <Player>().ExecuteRead(Connection).ToImmutableList();
Assert.That(records, Has.Count.EqualTo(1));
Assert.That(records[0].Name, Is.EqualTo("Jim"));
player.Name = "John";
Assert.That(InsertInto.Row(player).TryExecute(Connection), Is.False);
records = Select.From <Player>().ExecuteRead(Connection).ToImmutableList();
Assert.That(records, Has.Count.EqualTo(1));
Assert.That(records[0].Name, Is.EqualTo("Jim"));
Assert.That(InsertInto.Row(player).UpdateOnDuplicateKey.TryExecute(Connection));
records = Select.From <Player>().ExecuteRead(Connection).ToImmutableList();
Assert.That(records, Has.Count.EqualTo(1));
Assert.That(records[0].Name, Is.EqualTo("John"));
}
public void VerifyEnumWriteAndRead()
{
DropTable.For <EnumTable>().TryExecute(Connection);
CreateTable.For <EnumTable>().Execute(Connection);
var value = new EnumTable
{
Name = "Foo",
Something = Something.Second
};
InsertInto.Row(value).Execute(Connection);
var values = Select.From <EnumTable>().WhereEqual(x => x.Name, "Foo").ExecuteRead(Connection).ToList();
Assert.That(values, Has.Count.EqualTo(1));
Assert.That(values[0].Something, Is.EqualTo(Something.Second));
Assert.That(values[0].Name, Is.EqualTo("Foo"));
}
public void InsertAndSelectProtoBuffer()
{
DropTable.For <IdentifiedBlob>().TryExecute(Connection);
CreateTable.For <IdentifiedBlob>().Execute(Connection);
var value = new IdentifiedBlob
{
Identifier = Guid.NewGuid(),
Value = new Payload
{
ValueA = 42,
ValueB = -44
}
};
InsertInto.Row(value).Execute(Connection);
var result = Select.From <IdentifiedBlob>().ExecuteRead(Connection).ToImmutableList();
Assert.That(result, Has.Count.EqualTo(1));
Assert.That(result[0].Identifier, Is.EqualTo(value.Identifier));
Assert.That(result[0].Value.ValueA, Is.EqualTo(value.Value.ValueA));
Assert.That(result[0].Value.ValueB, Is.EqualTo(value.Value.ValueB));
}
public void ConnectWithSimpleObject()
{
DropTable.For <Player>().TryExecute(Connection);
CreateTable.For <Player>().Execute(Connection);
var peter = new Player
{
Identifier = Guid.NewGuid(),
Name = "Peter",
Age = 22,
Birthday = new DateTime(1983, 3, 21).ToUniversalTime()
};
var willem = new Player
{
Identifier = Guid.NewGuid(),
Name = "Willem",
Age = 50,
Birthday = new DateTime(1983, 3, 22).ToUniversalTime()
};
InsertInto.Row(peter).Execute(Connection);
InsertInto.Row(willem).Execute(Connection);
var selectResult = Select.From <Player>().ExecuteRead(Connection)
.OrderBy(x => x.Name)
.ToImmutableList();
Assert.That(selectResult, Has.Count.EqualTo(2));
Assert.That(selectResult[0].Name, Is.EqualTo(peter.Name));
Assert.That(selectResult[0].Identifier, Is.EqualTo(peter.Identifier));
Assert.That(selectResult[0].Age, Is.EqualTo(peter.Age));
Assert.That(selectResult[0].Birthday, Is.EqualTo(peter.Birthday));
Assert.That(selectResult[1].Name, Is.EqualTo(willem.Name));
Assert.That(selectResult[1].Identifier, Is.EqualTo(willem.Identifier));
Assert.That(selectResult[1].Age, Is.EqualTo(willem.Age));
Assert.That(selectResult[1].Birthday, Is.EqualTo(willem.Birthday));
selectResult = Select.From <Player>().Take(1).ExecuteRead(Connection)
.OrderBy(x => x.Name)
.ToImmutableList();
Assert.That(selectResult, Has.Count.EqualTo(1));
selectResult = Select.From <Player>()
.WhereEqual(x => x.Identifier, peter.Identifier)
.ExecuteRead(Connection)
.OrderBy(x => x.Name).ToImmutableList();
Assert.That(selectResult, Has.Count.EqualTo(1));
Assert.That(selectResult[0].Name, Is.EqualTo(peter.Name));
var maximumAge = Select.From <Player>().Maximum(x => x.Age).ExecuteRead(Connection).ToImmutableList();
Assert.That(maximumAge, Has.Count.EqualTo(1));
Assert.That(maximumAge[0], Is.EqualTo(50));
var minimumAge = Select.From <Player>().Minimum(x => x.Age).ExecuteRead(Connection).ToImmutableList();
Assert.That(minimumAge, Has.Count.EqualTo(1));
Assert.That(minimumAge[0], Is.EqualTo(22));
}
