public void CreateMultiReferencingTable()
{
var db = new OrmTestSession();
db.CreateTable <MultiReferencedTable>();
db.CreateTable <MultiReferencingTable>();
TableMapping refingMap = db.GetMapping <MultiReferencingTable>();
var sql = refingMap.GetCreateSql();
var correct =
@"CREATE TABLE [MultiReferencingTable] (
[RefId] integer NOT NULL,
[Indexed] integer NOT NULL,
CONSTRAINT [FK_Foreign_Key]
FOREIGN KEY ([Indexed], [RefId])
REFERENCES [MultiReferencedTable] ([Id2], [Id])
);";
Assert.AreEqual(correct, sql);
Assert.AreEqual(1, refingMap.ForeignKeys.Count);
Assert.AreEqual("FK_Foreign_Key", refingMap.ForeignKeys.First().Name);
TableMapping.ForeignKey[] fk = refingMap.ForeignKeys.Where(f => f.ChildTable == "MultiReferencedTable").ToArray();
Assert.AreEqual(1, fk.Count());
Assert.AreEqual("RefId", fk[0].Keys.Skip(1).First().Key);
Assert.AreEqual("Id", fk[0].Keys.Skip(1).First().Value);
Assert.AreEqual("Indexed", fk[0].Keys.First().Key);
Assert.AreEqual("Id2", fk[0].Keys.First().Value);
}
public void CreateReferencingTable()
{
var db = new OrmTestSession();
db.CreateTable <ReferencedTable>();
db.CreateTable <ReferencingTable>();
TableMapping refingMap = db.GetMapping <ReferencingTable>();
var sql = refingMap.GetCreateSql();
var correct =
@"CREATE TABLE [ReferencingTable] (
[RefId] integer NOT NULL,
[RandomName] integer NOT NULL,
CONSTRAINT [FK_Foreign_Key]
FOREIGN KEY ([RefId])
REFERENCES [ReferencedTable] ([Id]),
FOREIGN KEY ([RandomName])
REFERENCES [ReferencedTable] ([Id2])
ON UPDATE CASCADE
);";
Assert.AreEqual(correct, sql);
Assert.AreEqual(2, refingMap.ForeignKeys.Count);
Assert.AreEqual(1, refingMap.ForeignKeys.Count(f => f.Name == "FK_Foreign_Key"));
TableMapping.ForeignKey[] fk = refingMap.ForeignKeys.Where(f => f.ChildTable == "ReferencedTable").ToArray();
Assert.AreEqual(2, fk.Count());
Assert.AreEqual("Id", fk.Where(f => f.Keys.First().Key == "RefId").First().Keys.First().Value);
Assert.AreEqual("Id2", fk.Where(f => f.Keys.First().Key == "RandomName").First().Keys.First().Value);
}
public void InheritanceWorks()
{
var db = new OrmTestSession();
var mapping = db.GetMapping<Derived>();
Assert.AreEqual(3, mapping.Columns.Count);
Assert.AreEqual("Id", mapping.PrimaryKey.Columns.First().Name);
}
public void InheritanceWorks()
{
var db = new OrmTestSession();
var mapping = db.GetMapping <Derived>();
Assert.AreEqual(3, mapping.Columns.Count);
Assert.AreEqual("Id", mapping.PrimaryKey.Columns.First().Name);
}
public void DataConverterCreateTest()
{
var db = new OrmTestSession();
db.CreateTable <TestConverter>();
var column = db.GetMapping <TestConverter>().EditableColumns.First();
var dbType = column.ColumnType;
var converter = column.DataConverter.GetType();
Assert.AreEqual(typeof(string), dbType);
Assert.AreEqual(typeof(ColorConverter), converter);
}
public CreateTest(OrmTestSession db)
{
db.CreateTable<OrderLine>();
var orderLine = db.GetMapping<OrderLine>();
Assert.AreEqual(4, orderLine.Columns.Count, "OrderLine has 4 columns");
var l = new OrderLine { Status = OrderLineStatus.Shipped };
db.Insert(l);
OrderLine lo = db.Table<OrderLine>().First(x => x.Status == OrderLineStatus.Shipped);
Assert.AreEqual(lo.Id, l.Id);
Id = lo.Id;
}
public MigrateTest(OrmTestSession db)
{
db.CreateTable<OrderDetails>();
var orderLine = db.GetMapping<OrderDetails>();
Assert.AreEqual(6, orderLine.Columns.Count, "OrderDetails (OrderLine) has 6 columns");
OrderDetails l = db.Table<OrderDetails>().First(x => x.ShippingStatus == OrderLineStatus.Shipped);
l.Tax = 12;
db.Update(l);
OrderDetails lo = db.Table<OrderDetails>().First(x => x.Tax == 12);
Assert.AreEqual(lo.Id, l.Id);
Id = lo.Id;
}
public MigrateTest(OrmTestSession db)
{
db.CreateTable <OrderDetails>();
var orderLine = db.GetMapping <OrderDetails>();
Assert.AreEqual(6, orderLine.Columns.Count, "OrderDetails (OrderLine) has 6 columns");
OrderDetails l = db.Table <OrderDetails>().First(x => x.ShippingStatus == OrderLineStatus.Shipped);
l.Tax = 12;
db.Update(l);
OrderDetails lo = db.Table <OrderDetails>().First(x => x.Tax == 12);
Assert.AreEqual(lo.Id, l.Id);
Id = lo.Id;
}
public void CreateIndexedColumnTable()
{
var db = new OrmTestSession();
db.CreateTable <IndexedColumnTable>();
var columnMap = db.GetMapping <IndexedColumnTable>();
var sql = columnMap.Indexes.Single().GetCreateSql("IndexedColumnTable");
var correct = @"CREATE INDEX [IX_SomeName] on [IndexedColumnTable] (
[Indexed] COLLATE RTrim
);";
Assert.AreEqual(correct, sql);
var colIdx = columnMap.Indexes.Where(i => i.IndexName == "IX_SomeName").ToArray();
Assert.AreEqual(1, colIdx.Count());
Assert.AreEqual(Collation.RTrim, colIdx.First().Columns.First().Collation);
}
public void CreateVeryAdvancedTable()
{
var db = new OrmTestSession();
db.CreateTable <VeryAdvancedTable>();
TableMapping mapping = db.GetMapping <VeryAdvancedTable>();
var sql = mapping.GetCreateSql();
var correct =
@"CREATE TABLE [VeryAdvancedTable] (
[Id] integer NOT NULL CHECK (Id <= 25),
[DiffName] varchar(255) COLLATE RTrim,
[IsWorking] integer NOT NULL DEFAULT(1),
[AnotherId] integer NOT NULL,
CONSTRAINT PK_MyPrimaryKey
PRIMARY KEY ([AnotherId], [Id] Desc),
CHECK (Id <= 10)
);";
Assert.AreEqual(correct, sql);
Assert.AreEqual("Id <= 10", mapping.Checks.First());
Assert.AreEqual(2, mapping.PrimaryKey.Columns.Length);
Assert.AreEqual("AnotherId", mapping.PrimaryKey.Columns[0].Name);
Assert.AreEqual("Id", mapping.PrimaryKey.Columns[1].Name);
TableMapping.Column idCol = mapping.Columns.First(c => c.Name == "Id");
Assert.AreEqual("Id <= 25", idCol.Checks.First());
Assert.AreEqual("PK_MyPrimaryKey", idCol.PrimaryKey.Name);
Assert.AreEqual(Direction.Desc, idCol.PrimaryKey.Direction);
TableMapping.Column difCol = mapping.Columns.First(c => c.Name == "DiffName");
Assert.AreEqual(255, difCol.MaxStringLength);
Assert.AreEqual(Collation.RTrim, difCol.Collation);
TableMapping.Column workinCol = mapping.Columns.First(c => c.Name == "IsWorking");
Assert.IsFalse(workinCol.IsNullable);
Assert.AreEqual("1", workinCol.DefaultValue);
Assert.IsFalse(mapping.Columns.Any(c => c.Name == "IgnoredColumn"));
}
public CreateTest(OrmTestSession db)
{
db.CreateTable <OrderLine>();
var orderLine = db.GetMapping <OrderLine>();
Assert.AreEqual(4, orderLine.Columns.Count, "OrderLine has 4 columns");
var l = new OrderLine {
Status = OrderLineStatus.Shipped
};
db.Insert(l);
OrderLine lo = db.Table <OrderLine>().First(x => x.Status == OrderLineStatus.Shipped);
Assert.AreEqual(lo.Id, l.Id);
Id = lo.Id;
}
public void CreateIndexedTable()
{
var db = new OrmTestSession();
db.CreateTable <IndexedTable>();
TableMapping tableMap = db.GetMapping <IndexedTable>();
var sql = tableMap.Indexes.Single().GetCreateSql("IndexedTable");
var correct =
@"CREATE UNIQUE INDEX [IX_TabelIndex] on [IndexedTable] (
[Indexed]
);";
Assert.AreEqual(correct, sql);
var tblIdx = tableMap.Indexes.Where(i => i.IndexName == "IX_TabelIndex").ToArray();
Assert.AreEqual(1, tblIdx.Count());
Assert.AreEqual(true, tblIdx.First().Unique);
}
public void CreateCustomTable()
{
var db = new OrmTestSession();
db.CreateTable <CustomTable>();
var mapping = db.GetMapping <CustomTable>();
var sql = mapping.GetCreateSql();
var correct = @"CREATE TABLE [DifferentName] (
[Id] integer NOT NULL,
[NewName] text ,
[IsWorking] integer NOT NULL
);";
Assert.AreEqual(correct, sql);
Assert.AreEqual("DifferentName", mapping.TableName);
Assert.AreEqual("NewName", mapping.Columns[1].Name);
}
public void CreateMultiIndexedTable()
{
var db = new OrmTestSession();
db.CreateTable <MultiIndexedTable>();
var multiMap = db.GetMapping <MultiIndexedTable>();
var sql = multiMap.Indexes.Single().GetCreateSql("MultiIndexedTable");
var correct = @"CREATE UNIQUE INDEX [IX_MultiIndexedTable] on [MultiIndexedTable] (
[Indexed] COLLATE RTrim,
[Second] Desc
);";
Assert.AreEqual(correct, sql);
var multiIdx = multiMap.Indexes.Where(i => i.IndexName == "IX_MultiIndexedTable").ToArray();
Assert.AreEqual(1, multiIdx.Count());
Assert.AreEqual(2, multiIdx.First().Columns.Count());
Assert.AreEqual(Collation.RTrim, multiIdx.First().Columns.First(c => c.ColumnName == "Indexed").Collation);
}
public void CreateAdvancedTable()
{
var db = new OrmTestSession();
db.CreateTable <AdvancedTable>();
var mapping = db.GetMapping <AdvancedTable>();
var sql = mapping.GetCreateSql();
var correct = @"CREATE TABLE [AdvancedTable] (
[Id] integer CONSTRAINT PK_MyPrimaryKey PRIMARY KEY Desc ON CONFLICT Fail NOT NULL,
[IsWorking] integer UNIQUE ON CONFLICT Rollback
);";
Assert.AreEqual(correct, sql);
Assert.AreEqual(2, mapping.Columns.Count);
Assert.IsNotNull(mapping.Columns[1].Unique);
Assert.AreEqual(true, mapping.Columns.First(c => c.Name == "IsWorking").IsNullable);
Assert.AreEqual(1, mapping.PrimaryKey.Columns.Length);
Assert.AreEqual(ConflictResolution.Fail, mapping.OnPrimaryKeyConflict);
}
public void CreateTableWithoutIndexesTest()
{
var db = new OrmTestSession();
// the table
db.CreateTable <IndexedTable>(false);
// the indexes
var map = db.GetMapping <IndexedTable>();
foreach (var index in map.Indexes)
{
db.Execute(index.GetCreateSql(map.TableName));
}
// the test
db.Insert(new IndexedTable {
Indexed = 1
});
ExceptionAssert.Throws <SqliteException>(
() => db.Insert(new IndexedTable {
Indexed = 1
}));
}
public void CreateTableWithoutIndexesTest()
{
var db = new OrmTestSession();
// the table
db.CreateTable<IndexedTable>(false);
// the indexes
var map = db.GetMapping<IndexedTable>();
foreach (var index in map.Indexes)
{
db.Execute(index.GetCreateSql(map.TableName));
}
// the test
db.Insert(new IndexedTable { Indexed = 1 });
ExceptionAssert.Throws<SqliteException>(
() => db.Insert(new IndexedTable { Indexed = 1 }));
}
public void CreateMultiReferencingTable()
{
var db = new OrmTestSession();
db.CreateTable<MultiReferencedTable>();
db.CreateTable<MultiReferencingTable>();
var refingMap = db.GetMapping<MultiReferencingTable>();
var sql = refingMap.GetCreateSql();
var correct = @"CREATE TABLE [MultiReferencingTable] (
[RefId] integer NOT NULL,
[Indexed] integer NOT NULL,
CONSTRAINT [FK_Foreign_Key]
FOREIGN KEY ([Indexed], [RefId])
REFERENCES [MultiReferencedTable] ([Id2], [Id])
);";
Assert.AreEqual(correct, sql);
Assert.AreEqual(1, refingMap.ForeignKeys.Count);
Assert.AreEqual("FK_Foreign_Key", refingMap.ForeignKeys.First().Name);
TableMapping.ForeignKey[] fk =
refingMap.ForeignKeys.Where(f => f.ChildTable == "MultiReferencedTable").ToArray();
Assert.AreEqual(1, fk.Count());
Assert.AreEqual("RefId", fk[0].Keys.Skip(1).First().Key);
Assert.AreEqual("Id", fk[0].Keys.Skip(1).First().Value);
Assert.AreEqual("Indexed", fk[0].Keys.First().Key);
Assert.AreEqual("Id2", fk[0].Keys.First().Value);
}
public void CreateReferencingTable()
{
var db = new OrmTestSession();
db.CreateTable<ReferencedTable>();
db.CreateTable<ReferencingTable>();
var refingMap = db.GetMapping<ReferencingTable>();
var sql = refingMap.GetCreateSql();
var correct = @"CREATE TABLE [ReferencingTable] (
[RefId] integer NOT NULL,
[RandomName] integer NOT NULL,
CONSTRAINT [FK_Foreign_Key]
FOREIGN KEY ([RefId])
REFERENCES [ReferencedTable] ([Id]),
FOREIGN KEY ([RandomName])
REFERENCES [ReferencedTable] ([Id2])
ON UPDATE CASCADE
);";
Assert.AreEqual(correct, sql);
Assert.AreEqual(2, refingMap.ForeignKeys.Count);
Assert.AreEqual(1, refingMap.ForeignKeys.Count(f => f.Name == "FK_Foreign_Key"));
TableMapping.ForeignKey[] fk = refingMap.ForeignKeys.Where(f => f.ChildTable == "ReferencedTable").ToArray();
Assert.AreEqual(2, fk.Count());
Assert.AreEqual("Id", fk.Where(f => f.Keys.First().Key == "RefId").First().Keys.First().Value);
Assert.AreEqual("Id2", fk.Where(f => f.Keys.First().Key == "RandomName").First().Keys.First().Value);
}
public void CreateVeryAdvancedTable()
{
var db = new OrmTestSession();
db.CreateTable<VeryAdvancedTable>();
var mapping = db.GetMapping<VeryAdvancedTable>();
var sql = mapping.GetCreateSql();
var correct = @"CREATE TABLE [VeryAdvancedTable] (
[Id] integer NOT NULL CHECK (Id <= 25),
[DiffName] varchar(255) COLLATE RTrim,
[IsWorking] integer NOT NULL DEFAULT(1),
[AnotherId] integer NOT NULL,
CONSTRAINT PK_MyPrimaryKey
PRIMARY KEY ([AnotherId], [Id] Desc),
CHECK (Id <= 10)
);";
Assert.AreEqual(correct, sql);
Assert.AreEqual("Id <= 10", mapping.Checks.First());
Assert.AreEqual(2, mapping.PrimaryKey.Columns.Length);
Assert.AreEqual("AnotherId", mapping.PrimaryKey.Columns[0].Name);
Assert.AreEqual("Id", mapping.PrimaryKey.Columns[1].Name);
TableMapping.Column idCol = mapping.Columns.First(c => c.Name == "Id");
Assert.AreEqual("Id <= 25", idCol.Checks.First());
Assert.AreEqual("PK_MyPrimaryKey", idCol.PrimaryKey.Name);
Assert.AreEqual(Direction.Desc, idCol.PrimaryKey.Direction);
TableMapping.Column difCol = mapping.Columns.First(c => c.Name == "DiffName");
Assert.AreEqual(255, difCol.MaxStringLength);
Assert.AreEqual(Collation.RTrim, difCol.Collation);
TableMapping.Column workinCol = mapping.Columns.First(c => c.Name == "IsWorking");
Assert.IsFalse(workinCol.IsNullable);
Assert.AreEqual("1", workinCol.DefaultValue);
Assert.IsFalse(mapping.Columns.Any(c => c.Name == "IgnoredColumn"));
}
public void CreateAdvancedTable()
{
var db = new OrmTestSession();
db.CreateTable<AdvancedTable>();
var mapping = db.GetMapping<AdvancedTable>();
var sql = mapping.GetCreateSql();
var correct = @"CREATE TABLE [AdvancedTable] (
[Id] integer CONSTRAINT PK_MyPrimaryKey PRIMARY KEY Desc ON CONFLICT Fail NOT NULL,
[IsWorking] integer UNIQUE ON CONFLICT Rollback
);";
Assert.AreEqual(correct, sql);
Assert.AreEqual(2, mapping.Columns.Count);
Assert.IsNotNull(mapping.Columns[1].Unique);
Assert.AreEqual(true, mapping.Columns.First(c => c.Name == "IsWorking").IsNullable);
Assert.AreEqual(1, mapping.PrimaryKey.Columns.Length);
Assert.AreEqual(ConflictResolution.Fail, mapping.OnPrimaryKeyConflict);
}
public void CreateCustomTable()
{
var db = new OrmTestSession();
db.CreateTable<CustomTable>();
var mapping = db.GetMapping<CustomTable>();
var sql = mapping.GetCreateSql();
var correct = @"CREATE TABLE [DifferentName] (
[Id] integer NOT NULL,
[NewName] text ,
[IsWorking] integer NOT NULL
);";
Assert.AreEqual(correct, sql);
Assert.AreEqual("DifferentName", mapping.TableName);
Assert.AreEqual("NewName", mapping.Columns[1].Name);
}
public void DataConverterCreateTest()
{
var db = new OrmTestSession();
db.CreateTable<TestConverter>();
var column = db.GetMapping<TestConverter>().EditableColumns.First();
var dbType = column.ColumnType;
var converter = column.DataConverter.GetType();
Assert.AreEqual(typeof(string), dbType);
Assert.AreEqual(typeof(ColorConverter), converter);
}
public void CreateIndexedColumnTable()
{
var db = new OrmTestSession();
db.CreateTable<IndexedColumnTable>();
var columnMap = db.GetMapping<IndexedColumnTable>();
var sql = columnMap.Indexes.Single().GetCreateSql("IndexedColumnTable");
var correct = @"CREATE INDEX [IX_SomeName] on [IndexedColumnTable] (
[Indexed] COLLATE RTrim
);";
Assert.AreEqual(correct, sql);
var colIdx = columnMap.Indexes.Where(i => i.IndexName == "IX_SomeName").ToArray();
Assert.AreEqual(1, colIdx.Count());
Assert.AreEqual(Collation.RTrim, colIdx.First().Columns.First().Collation);
}
public void CreateMultiIndexedTable()
{
var db = new OrmTestSession();
db.CreateTable<MultiIndexedTable>();
var multiMap = db.GetMapping<MultiIndexedTable>();
var sql = multiMap.Indexes.Single().GetCreateSql("MultiIndexedTable");
var correct = @"CREATE UNIQUE INDEX [IX_MultiIndexedTable] on [MultiIndexedTable] (
[Indexed] COLLATE RTrim,
[Second] Desc
);";
Assert.AreEqual(correct, sql);
var multiIdx = multiMap.Indexes.Where(i => i.IndexName == "IX_MultiIndexedTable").ToArray();
Assert.AreEqual(1, multiIdx.Count());
Assert.AreEqual(2, multiIdx.First().Columns.Count());
Assert.AreEqual(Collation.RTrim, multiIdx.First().Columns.First(c => c.ColumnName == "Indexed").Collation);
}
public void CreateIndexedTable()
{
var db = new OrmTestSession();
db.CreateTable<IndexedTable>();
TableMapping tableMap = db.GetMapping<IndexedTable>();
var sql = tableMap.Indexes.Single().GetCreateSql("IndexedTable");
var correct =
@"CREATE UNIQUE INDEX [IX_TabelIndex] on [IndexedTable] (
[Indexed]
);";
Assert.AreEqual(correct, sql);
var tblIdx = tableMap.Indexes.Where(i => i.IndexName == "IX_TabelIndex").ToArray();
Assert.AreEqual(1, tblIdx.Count());
Assert.AreEqual(true, tblIdx.First().Unique);
}
