public void AlterTableAddField()
{
var provider = new SqliteContextProvider(ConnectionString);
using (var context = provider.Open())
{
context.Database.Table<Weapon>()
.Key(wpn => wpn.ID)
.Create();
context.Database.Table<Warrior>()
.Key(wrir => wrir.ID)
.ForeignKey<Weapon>(wrir => wrir.WeaponID, wpn => wpn.ID)
.Ignore(wrir => wrir.Race)
.Create();
context.Commit();
//TODO: Check table definition
context.Database.Table<Warrior>()
.Column(wrir => wrir.Race, FieldOperation.Add)
.Alter();
context.Commit();
//TODO: Check table definition
var tables = context.Select<Sqlite_Master>(m => m.Type == "table");
Assert.IsTrue(tables.Any(t => t.Name == typeof(Warrior).Name));
Assert.IsTrue(tables.Any(t => t.Name == typeof(Weapon).Name));
}
}
public void SelectWithJoinAndMap()
{
var provider = new SqliteContextProvider(ConnectionString);
using (var context = provider.Open())
{
var wrirWithWeapon = context.From<Warrior>()
.Map(wrir => wrir.Name, "WarriorName")
.Join<Weapon>((wpn, wrir) => wpn.ID == wrir.WeaponID)
.Map(wpn => wpn.Name, "WeaponName")
.Where<Warrior>(wrir => wrir.ID == 3)
.Select(() => new
{
WeaponName = "",
WarriorName = "",
Damage = 0
});
Assert.IsTrue(wrirWithWeapon.First().WarriorName == "Burt");
var parts = context.From<Warrior>()
.Join<Weapon>((wpn, wrir) => wpn.ID == wrir.WeaponID)
.Join<Armour, Warrior>((a, wrir) => a.WarriorID == wrir.ID)
.Join<ArmourPart>((ap, a) => ap.ID == a.ArmourPartID)
.Map(ap => ap.ID)
.Map(ap => ap.Name)
.Where<Warrior>(wrir => wrir.Name == "Harry")
.Select();
Assert.IsTrue(parts.Count() == 3);
}
}
public void Work()
{
var provider = new SqliteContextProvider(DatabaseManager.ConnectionString);
// add interceptor that traces the query string before it is executed
provider.Interceptor<WarriorWithArmour>().BeforeExecute(q => Trace.WriteLine(q.QueryString.Flatten()));
using (var context = provider.Open())
{
var item = context.From<Warrior>()
.Map(w => w.ID)
.Map(w => w.Name)
.Join<Armour>((a, w) => a.WarriorID == w.ID)
.Join<ArmourPart>((ap, a) => ap.ID == a.ArmourPartID)
.Join<Weapon, Warrior>((w, wa) => w.ID == wa.WeaponID)
.For<WarriorWithArmour>()
.Map<Weapon>(w => w.Name, wa => wa.WeaponName)
.Map<Armour>(a => a.Name, wa => wa.ArmourName)
.Select();
}
provider.Interceptor<Warrior>().BeforeExecute(q => Trace.WriteLine(q.QueryString.Flatten()));
using (var context = provider.Open())
{
var tmp = context.From<Warrior>().Select(w => new
{
w.ID,
w.Name,
w.Race
});
}
}
internal static void CreateDatabase(bool insertData = false)
{
var provider = new SqliteContextProvider(ConnectionString);
using (var context = provider.Open())
{
context.Database.Table<Weapon>().Key(wpn => wpn.ID).Create();
context.Database.Table<Warrior>()
.Key(wrir => wrir.ID)
.ForeignKey<Weapon>(wrir => wrir.WeaponID, wpn => wpn.ID)
.Create();
context.Database.Table<ArmourPart>()
.Key(ap => ap.ID)
.Create();
context.Database.Table<Armour>()
.ForeignKey<Weapon>(a => a.WarriorID, wpn => wpn.ID)
.ForeignKey<ArmourPart>(a => a.ArmourPartID, ap => ap.ID)
.Create();
context.Commit();
if (!insertData)
return;
context.Insert<Weapon>(() => new Weapon { ID = 1, Name = "Sword", Damage = 40 });
context.Insert<Weapon>(() => new Weapon { ID = 2, Name = "Dagger", Damage = 5 });
context.Insert<Weapon>(() => new Weapon { ID = 3, Name = "Ax", Damage = 50 });
context.Insert<Weapon>(() => new Weapon { ID = 4, Name = "Hammer", Damage = 70 });
context.Insert<Weapon>(() => new Weapon { ID = 5, Name = "Staff", Damage = 20 });
context.Insert<Warrior>(() => new Warrior { ID = 1, Name = "Ruben", Race = "Elf", SpecialSkill = "Magic claw", WeaponID = 5 });
context.Insert<Warrior>(() => new Warrior { ID = 2, Name = "Harry", Race = "Dwarf", SpecialSkill = "Fistblow", WeaponID = 3 });
context.Insert<Warrior>(() => new Warrior { ID = 3, Name = "Burt", Race = "Human", SpecialSkill = "Stomp", WeaponID = 3 });
context.Insert<ArmourPart>(() => new ArmourPart { ID = 1, Name = "Shoulderplate", Defense = 5 });
context.Insert<ArmourPart>(() => new ArmourPart { ID = 2, Name = "Glove", Defense = 5 });
context.Insert<ArmourPart>(() => new ArmourPart { ID = 3, Name = "Breastplate", Defense = 30 });
context.Insert<ArmourPart>(() => new ArmourPart { ID = 4, Name = "Shield", Defense = 50 });
context.Insert<ArmourPart>(() => new ArmourPart { ID = 5, Name = "Breastleather", Defense = 20 });
context.Insert<Armour>(() => new Armour { ArmourPartID = 1, WarriorID = 1 });
context.Insert<Armour>(() => new Armour { ArmourPartID = 5, WarriorID = 1 });
context.Insert<Armour>(() => new Armour { ArmourPartID = 4, WarriorID = 1 });
context.Insert<Armour>(() => new Armour { ArmourPartID = 1, WarriorID = 2 });
context.Insert<Armour>(() => new Armour { ArmourPartID = 2, WarriorID = 2 });
context.Insert<Armour>(() => new Armour { ArmourPartID = 3, WarriorID = 2 });
context.Insert<Armour>(() => new Armour { ArmourPartID = 1, WarriorID = 3 });
context.Insert<Armour>(() => new Armour { ArmourPartID = 2, WarriorID = 1 });
context.Insert<Armour>(() => new Armour { ArmourPartID = 5, WarriorID = 1 });
context.Insert<Armour>(() => new Armour { ArmourPartID = 4, WarriorID = 1 });
context.Commit();
}
}
public void AddFieldByStringFail()
{
var provider = new SqliteContextProvider(ConnectionString);
using (var context = provider.Open())
{
context.Database.Table<Warrior>().Ignore(wrir => wrir.Race).Create();
Assert.Throws<ArgumentNullException>(() => context.Database.Table<Warrior>().Column("Race", FieldOperation.Add).Alter());
}
}
public void AddFieldByString()
{
var provider = new SqliteContextProvider(ConnectionString);
var logger = new MessageStackLogger();
provider.Settings.AddLogger(logger);
using (var context = provider.Open())
{
context.Database.Table<Warrior>().Ignore(wrir => wrir.Race).Create();
context.Database.Table<Warrior>().Column("Race", FieldOperation.Add, typeof(string)).Alter();
context.Commit();
}
}
public void SimpleSelect()
{
var provider = new SqliteContextProvider(ConnectionString);
using (var context = provider.Open())
{
var warriors = context.Select<Warrior>();
Assert.IsTrue(warriors.Count() == 3);
warriors = context.Select<Warrior>(wrir => wrir.Race == "Elf");
Assert.IsTrue(warriors.Count() == 1);
Assert.IsTrue(warriors.First().Race == "Elf");
warriors = context.From<Warrior>(wrir => wrir.Race == "Dwarf").Select();
Assert.IsTrue(warriors.Count() == 1);
Assert.IsTrue(warriors.First().Race == "Dwarf");
}
}
public void Work()
{
// this method shows how the
_log = new List<string>();
int count = 100;
DatabaseManager.CreateDatabase();
var stopwatch = new Stopwatch();
stopwatch.Start();
var provider = new SqliteContextProvider(DatabaseManager.ConnectionString);
using (var context = provider.Open())
{
using (var uow = new UnitOfWork(context))
{
for (int i = 0; i < count; i++)
{
DoReadWork(uow, i);
}
}
}
stopwatch.Stop();
_log.Add(string.Format("Creating one context for {0} selects calls took {1} ms", count, stopwatch.ElapsedMilliseconds));
stopwatch.Reset();
stopwatch.Start();
for (int i = 0; i < count; i++)
{
using (var uow = new UnitOfWork(DatabaseManager.ConnectionString))
{
DoReadWork(uow, i);
}
}
stopwatch.Stop();
_log.Add(string.Format("Creating a context per call for {0} selects calls took {1} ms", count, stopwatch.ElapsedMilliseconds));
PrintLog();
}
public void RenameTable()
{
var provider = new SqliteContextProvider(ConnectionString);
using (var context = provider.Open())
{
// create a table to drop later in the test
context.Database.Table<Warrior>().Create();
context.Commit();
// drop the table
context.Database.Table<Warrior>().RenameTo<Solidier>();
context.Commit();
var tables = context.Select<Sqlite_Master>(m => m.Type == "table");
Assert.IsTrue(tables.Any(t => t.Name == typeof(Solidier).Name));
Assert.IsFalse(tables.Any(t => t.Name == typeof(Warrior).Name));
}
}
public void GetDatabaseFromConnectionStringTest()
{
var provider = new SqliteContextProvider(ConnectionString);
using (var context = provider.Open())
{
Assert.AreEqual(DatabaseName, context.ConnectionProvider.Database);
context.ConnectionProvider.Database = "Test.db";
Assert.AreEqual(context.ConnectionProvider.Database, "Test.db");
}
}
public void DropTable()
{
var provider = new SqliteContextProvider(ConnectionString);
using (var context = provider.Open())
{
// create a table to drop later in the test
context.Database.Table<Weapon>()
.Key(wpn => wpn.ID)
.Create();
context.Commit();
var tables = context.Select<Sqlite_Master>(m => m.Type == "table");
Assert.IsTrue(tables.Any(t => t.Name == typeof(Weapon).Name));
// drop the table
context.Database.Table<Weapon>()
.Drop();
context.Commit();
tables = context.Select<Sqlite_Master>(m => m.Type == "table");
Assert.IsFalse(tables.Any(t => t.Name == typeof(Weapon).Name));
}
}
public void CreateTableNotNullableColumn()
{
var provider = new SqliteContextProvider(ConnectionString);
var logger = new MessageStackLogger();
provider.Settings.AddLogger(logger);
using (var context = provider.Open())
{
// table with a foreign key
context.Database.Table<Warrior>()
.Column(wrir => wrir.ID)
.Column(wrir => wrir.Race, isNullable: false)
.Create();
Assert.IsFalse(File.Exists(DatabaseName));
//context.QueryCommandStore.First().Execute(context);
context.Commit();
Assert.AreEqual(logger.Logs.First().Message.Flatten(), "CREATE TABLE IF NOT EXISTS Warrior (ID int NOT NULL, Race varchar(1000) NOT NULL, Name varchar(1000), WeaponID int NOT NULL, SpecialSkill varchar(1000))");
}
}
public void CreateTableMultyKey()
{
var provider = new SqliteContextProvider(ConnectionString);
using (var context = provider.Open())
{
// table with multiple columns for key
context.Database.Table<Warrior>()
.Key(wrir => wrir.ID, wrir => wrir.WeaponID)
.Create();
Assert.IsFalse(File.Exists(DatabaseName));
context.Commit();
Assert.IsTrue(File.Exists(DatabaseName));
}
}
public void CreateTableForeignKey()
{
var provider = new SqliteContextProvider(ConnectionString);
using (var context = provider.Open())
{
context.Database.Table<Weapon>()
.Key(wpn => wpn.ID)
.Create();
// table with a foreign key
context.Database.Table<Warrior>()
.Key(wrir => wrir.ID)
.ForeignKey<Weapon>(wrir => wrir.WeaponID, wpn => wpn.ID)
.Create();
Assert.IsFalse(File.Exists(DatabaseName));
context.Commit();
Assert.IsTrue(File.Exists(DatabaseName));
var tables = context.Select<Sqlite_Master>(m => m.Type == "table");
Assert.IsTrue(tables.Any(t => t.Name == typeof(Warrior).Name));
Assert.IsTrue(tables.Any(t => t.Name == typeof(Weapon).Name));
}
}
public UnitOfWork(string connectionString)
{
var connection = new SqliteContextProvider(connectionString);
_context = connection.Open();
}
