public void TestCreateIndex()
{
using (var db = new PlainDb())
{
db.CreateTable <User>();
db.CreateIndex <User>("UsersUnique", true, u => new { u.FirstName, u.LastName });
var indexColumns = db.Query("PRAGMA index_info([UsersUnique]);", reader => new
{
No = reader.Get <int>("seqno"),
CId = reader.Get <int>("cid"),
Name = reader.Get <string>("name")
})
.ToList();
Assert.Collection(
indexColumns,
c =>
{
Assert.Equal(0, c.No);
Assert.Equal(1, c.CId);
Assert.Equal("FirstName", c.Name);
},
c =>
{
Assert.Equal(1, c.No);
Assert.Equal(2, c.CId);
Assert.Equal("LastName", c.Name);
}
);
}
}
public void TestCreateView()
{
using (var db = new PlainDb())
{
db.CreateTable <User>();
db.CreateView("UserView", db.Users.Where(u => u.DateOfBirth != null));
Assert.True(db.SQLiteMaster.Any(o => o.Type == "view" && o.Name == "UserView"));
db.Users.Insert(new[]
{
new User {
UserGuid = Guid.NewGuid(), FirstName = "AB", LastName = "CD"
},
new User {
UserGuid = Guid.NewGuid(), FirstName = "EF", LastName = "GH"
},
new User {
UserGuid = Guid.NewGuid(), FirstName = "IJ", LastName = "KL", DateOfBirth = DateTime.Now
},
new User {
UserGuid = Guid.NewGuid(), FirstName = "MN", LastName = "OP"
},
new User {
UserGuid = Guid.NewGuid(), FirstName = "QR", LastName = "ST", DateOfBirth = DateTime.Now
},
new User {
UserGuid = Guid.NewGuid(), FirstName = "UV", LastName = "WX"
},
new User {
UserGuid = Guid.NewGuid(), FirstName = "YZ", LastName = "AZ", DateOfBirth = DateTime.Now
},
new User {
UserGuid = Guid.NewGuid(), FirstName = "BY", LastName = "CX"
}
});
Assert.Equal(
db.Users.Where(u => u.DateOfBirth != null).Select(u => u.UserGuid),
db.UserView.Select(u => u.UserGuid)
);
}
}
public void TestCreateTable()
{
using (var db = new PlainDb())
{
Assert.Equal(0, db.SQLiteMaster.Count(o => o.Type == "table"));
db.CreateTable <User>(t => new
{
UsersUnique = t.Unique(u => new { u.FirstName, u.LastName })
});
Assert.Equal(1, db.SQLiteMaster.Count(o => o.Type == "table"));
var result = db.Query("PRAGMA table_info([Users]);", reader => new
{
CId = reader.Get <int>("cid"),
Name = reader.Get <string>("name"),
Type = reader.Get <string>("type"),
NotNull = reader.Get <bool>("notnull"),
Default = reader.Get <object>("dflt_value"),
PrimaryKey = reader.Get <bool>("pk")
})
.ToList();
Assert.Collection(
result,
c =>
{
Assert.Equal(0, c.CId);
Assert.Equal("UserGuid", c.Name);
Assert.True(c.PrimaryKey);
},
c =>
{
Assert.Equal(1, c.CId);
Assert.Equal("FirstName", c.Name);
Assert.True(c.NotNull);
},
c =>
{
Assert.Equal(2, c.CId);
Assert.Equal("LastName", c.Name);
Assert.True(c.NotNull);
},
c =>
{
Assert.Equal(3, c.CId);
Assert.Equal("DateOfBirth", c.Name);
Assert.False(c.NotNull);
}
);
// var indexColumns = db.Query("PRAGMA index_info([UsersUnique]);", reader => new
// {
// No = reader.Get<int>("seqno"),
// CId = reader.Get<int>("cid"),
// Name = reader.Get<string>("name")
// })
// .ToList();
//
// Assert.Equal(2, indexColumns.Count);
// Assert.IsTrue(indexColumns.Any(c => c.No == 1 && c.CId == 1 && c.Name == "FirstName"));
// Assert.IsTrue(indexColumns.Any(c => c.No == 2 && c.CId == 2 && c.Name == "LastName"));
db.CreateTable("Test Table", c => new
{
Id = c.Column <int>(primaryKey: true, autoIncrement: true),
Name = c.Column <string>("name", nullable: false),
UnitPrice = c.Column <decimal>("unit_price", defaultValue: 0.0M),
ExpiryDate = c.Column <DateTime>("expiry_date"),
Duration = c.Column <TimeSpan>("duration"),
UserId = c.Column <Guid>()
},
t => new
{
FK_Users = t.ForeignKey(u => u.UserId, "Users", "UserGuid")
});
Assert.Equal(3, db.SQLiteMaster.Count(o => o.Type == "table"));
result = db.Query("PRAGMA table_info([Test Table]);", reader => new
{
CId = reader.Get <int>("cid"),
Name = reader.Get <string>("name"),
Type = reader.Get <string>("type"),
NotNull = reader.Get <bool>("notnull"),
Default = reader.Get <object>("dflt_value"),
PrimaryKey = reader.Get <bool>("pk")
})
.ToList();
Assert.Collection(
result,
c =>
{
Assert.Equal(0, c.CId);
Assert.Equal("Id", c.Name);
Assert.True(c.PrimaryKey);
},
c =>
{
Assert.Equal(1, c.CId);
Assert.Equal("name", c.Name);
Assert.True(c.NotNull);
},
c =>
{
Assert.Equal(2, c.CId);
Assert.Equal("unit_price", c.Name);
Assert.True(c.NotNull);
Assert.Equal(0.0M, Convert.ToDecimal(c.Default));
},
c =>
{
Assert.Equal(3, c.CId);
Assert.Equal("expiry_date", c.Name);
Assert.True(c.NotNull);
},
c =>
{
Assert.Equal(4, c.CId);
Assert.Equal("duration", c.Name);
Assert.True(c.NotNull);
},
c =>
{
Assert.Equal(5, c.CId);
Assert.Equal("UserId", c.Name);
Assert.True(c.NotNull);
}
);
}
}
