public void CreateTest()
{
var factory = new MySQLCommandFactory();
Assert.IsAssignableFrom <ISelectBuilder>(factory.CreateSelectBuilder());
Assert.IsAssignableFrom <IInsertBuilder>(factory.CreateInsertBuilder());
Assert.IsAssignableFrom <IUpdateBuilder>(factory.CreateUpdateBuilder());
Assert.IsAssignableFrom <IDeleteBuilder>(factory.CreateDeleteBuilder());
}
public void SelectStarFromTest()
{
var factory = new MySQLCommandFactory();
var table = Utils.RandomName();
var builder = factory.CreateSelectBuilder()
.From(factory.GetDbObjectFactory().CreateTable(table));
Assert.Equal($"SELECT * FROM `{ table }`", builder.Build());
}
public void DeleteTableTest()
{
var factory = new MySQLCommandFactory();
var table = Utils.RandomName();
var builder = factory.CreateDeleteBuilder()
.From(factory.GetDbObjectFactory().CreateTable(table));
Assert.Equal($"DELETE `{ table }`", builder.Build());
}
public void UpdateTableTest()
{
var factory = new MySQLCommandFactory();
var table = Utils.RandomName();
var builder = factory.CreateUpdateBuilder()
.Table(factory.GetDbObjectFactory().CreateTable(table));
Assert.Throws(typeof(MySQLNoneSetException), builder.Build);
}
public void UpdateTableSetOneColumnTest()
{
var factory = new MySQLCommandFactory();
var table = Utils.RandomName();
var column = Utils.RandomName();
var builder = factory.CreateUpdateBuilder()
.Table(factory.GetDbObjectFactory().CreateTable(table))
.Set(factory.GetDbObjectFactory().CreateColumn(column), factory.GetDbObjectFactory().CreateParameter(column));
Assert.Equal($"UPDATE `{ table }` SET `{ column }` = @{ column }", builder.Build());
}
public void DeleteTableWhereTest()
{
var factory = new MySQLCommandFactory();
var table = Utils.RandomName();
var column_1 = Utils.RandomName();
var condition_1 = factory.GetConditionFactory().EqualTo(factory.GetDbObjectFactory().CreateColumn(column_1), factory.GetDbObjectFactory().CreateParameter(column_1));
var builder = factory.CreateDeleteBuilder()
.From(factory.GetDbObjectFactory().CreateTable(table))
.Where(condition_1);
Assert.Equal($"DELETE `{ table }` WHERE `{ column_1 }` = @{ column_1 }", builder.Build());
}
public void InsertTableOneColumnTest()
{
var factory = new MySQLCommandFactory();
var table = Utils.RandomName();
var column = Utils.RandomName();
var builder = factory.CreateInsertBuilder()
.Into(factory.GetDbObjectFactory().CreateTable(table))
.Column(factory.GetDbObjectFactory().CreateColumn(column))
.Value(factory.GetDbObjectFactory().CreateParameter(column));
Assert.Equal($"INSERT INTO `{ table }` ( `{ column }` ) VALUES ( @{ column } )", builder.Build());
}
public void SelectStarFromWhereNotNullTest()
{
var factory = new MySQLCommandFactory();
var table = Utils.RandomName();
var column = Utils.RandomName();
var builder = factory.CreateSelectBuilder()
.From(factory.GetDbObjectFactory().CreateTable(table))
.Where(factory.GetConditionFactory().NotIsNull(
factory.GetDbObjectFactory().CreateColumn(column)));
Assert.Equal($"SELECT * FROM `{ table }` WHERE `{ column }` IS NOT NULL", builder.Build());
}
public void SelectStarFromOrderByThenByTest()
{
var factory = new MySQLCommandFactory();
var table = Utils.RandomName();
var column_1 = Utils.RandomName();
var column_2 = Utils.RandomName();
var builder = factory.CreateSelectBuilder()
.From(factory.GetDbObjectFactory().CreateTable(table))
.OrderByDescending(factory.GetDbObjectFactory().CreateColumn(column_1))
.ThenBy(factory.GetDbObjectFactory().CreateColumn(column_2));
Assert.Equal($"SELECT * FROM `{ table }` ORDER BY `{ column_1 }` DESC, `{ column_2 }`", builder.Build());
}
public void SelectStarFromWhereGTETest()
{
var factory = new MySQLCommandFactory();
var table = Utils.RandomName();
var column = Utils.RandomName();
var builder = factory.CreateSelectBuilder()
.From(factory.GetDbObjectFactory().CreateTable(table))
.Where(factory.GetConditionFactory().GreaterThanOrEqualTo(
factory.GetDbObjectFactory().CreateColumn(column),
factory.GetDbObjectFactory().CreateParameter(column)));
Assert.Equal($"SELECT * FROM `{ table }` WHERE `{ column }` >= @{ column }", builder.Build());
}
public void SelectColumn2FromTest()
{
var factory = new MySQLCommandFactory();
var table = Utils.RandomName();
var column_1 = Utils.RandomName();
var column_2 = Utils.RandomName();
var builder = factory.CreateSelectBuilder()
.Column(factory.GetDbObjectFactory().CreateColumn(column_1))
.Column(factory.GetDbObjectFactory().CreateColumn(column_2))
.From(factory.GetDbObjectFactory().CreateTable(table));
Assert.Equal($"SELECT `{ column_1 }`, `{ column_2 }` FROM `{ table }`", builder.Build());
}
public void SelectStarFromWhereConditionAndTest()
{
var factory = new MySQLCommandFactory();
var table = Utils.RandomName();
var column_1 = Utils.RandomName();
var column_2 = Utils.RandomName();
var condition_1 = factory.GetConditionFactory().EqualTo(factory.GetDbObjectFactory().CreateColumn(column_1), factory.GetDbObjectFactory().CreateParameter(column_1));
var condition_2 = factory.GetConditionFactory().EqualTo(factory.GetDbObjectFactory().CreateColumn(column_2), factory.GetDbObjectFactory().CreateParameter(column_2));
var builder = factory.CreateSelectBuilder()
.From(factory.GetDbObjectFactory().CreateTable(table))
.Where(condition_1.And(condition_2));
Assert.Equal($"SELECT * FROM `{ table }` WHERE (`{ column_1 }` = @{ column_1 }) AND (`{ column_2 }` = @{ column_2 })", builder.Build());
}
public void InsertTableColumnListTest()
{
var factory = new MySQLCommandFactory();
var table = Utils.RandomName();
var column_1 = Utils.RandomName();
var column_2 = Utils.RandomName();
var column_3 = Utils.RandomName();
var columns = new[] { column_1, column_2, column_3 };
var builder = factory.CreateInsertBuilder()
.Into(factory.GetDbObjectFactory().CreateTable(table))
.Columns(columns.Select(column => factory.GetDbObjectFactory().CreateColumn(column)))
.Values(columns.Select(column => factory.GetDbObjectFactory().CreateParameter(column)));
Assert.Equal($"INSERT INTO `{ table }` ( `{ column_1 }`, `{ column_2 }`, `{ column_3 }` ) VALUES ( @{ column_1 }, @{ column_2 }, @{ column_3 } )", builder.Build());
}
public void SelectColumnFromLimitTest()
{
var factory = new MySQLCommandFactory();
var table = Utils.RandomName();
var alias_1 = Utils.RandomName();
var column_1 = Utils.RandomName();
var column_2 = Utils.RandomName();
var count = new Random().Next(50, 200);
var builder = factory.CreateSelectBuilder()
.Column(factory.GetDbObjectFactory().CreateColumn(column_1))
.Column(factory.GetDbObjectFactory().CreateColumn(column_2))
.From(factory.GetDbObjectFactory().CreateTable(table, alias_1))
.Limit(count);
Assert.Equal($"SELECT `{ column_1 }`, `{ column_2 }` FROM `{ table }` AS `{ alias_1 }` LIMIT { count }", builder.Build());
}
public void UpdateTableSetWhereTest()
{
var factory = new MySQLCommandFactory();
var table = Utils.RandomName();
var column_1 = Utils.RandomName();
var column_2 = Utils.RandomName();
var column_3 = Utils.RandomName();
var condition_3 = factory.GetConditionFactory().EqualTo(factory.GetDbObjectFactory().CreateColumn(column_3), factory.GetDbObjectFactory().CreateParameter(column_3));
var builder = factory.CreateUpdateBuilder()
.Table(factory.GetDbObjectFactory().CreateTable(table))
.Set(factory.GetDbObjectFactory().CreateColumn(column_1), factory.GetDbObjectFactory().CreateParameter(column_1))
.Set(factory.GetDbObjectFactory().CreateColumn(column_2), factory.GetDbObjectFactory().CreateParameter(column_2))
.Where(condition_3);
Assert.Equal($"UPDATE `{ table }` SET `{ column_1 }` = @{ column_1 }, `{ column_2 }` = @{ column_2 } WHERE `{ column_3 }` = @{ column_3 }", builder.Build());
}
public void SelectStarFromOrderByThenByLimitTest()
{
var factory = new MySQLCommandFactory();
var table = Utils.RandomName();
var column_1 = Utils.RandomName();
var column_2 = Utils.RandomName();
var column_3 = Utils.RandomName();
var offset = new Random().Next(1000, 2000);
var rows = new Random().Next(10, 20);
var builder = factory.CreateSelectBuilder()
.From(factory.GetDbObjectFactory().CreateTable(table))
.OrderByDescending(factory.GetDbObjectFactory().CreateColumn(column_1))
.ThenBy(factory.GetDbObjectFactory().CreateColumn(column_2))
.ThenByDescending(factory.GetDbObjectFactory().CreateColumn(column_3))
.Limit(offset, rows);
Assert.Equal($"SELECT * FROM `{ table }` ORDER BY `{ column_1 }` DESC, `{ column_2 }`, `{ column_3 }` DESC LIMIT { offset }, { rows }", builder.Build());
}
public void SelectColumnFromLimitTest()
{
var factory = new MySQLCommandFactory();
var table = Utils.RandomName();
var column_1 = Utils.RandomName();
var column_2 = Utils.RandomName();
var column_3 = Utils.RandomName();
var offset = new Random().Next(1000, 2000);
var rows = new Random().Next(10, 20);
var condition_1 = factory.GetConditionFactory().EqualTo(factory.GetDbObjectFactory().CreateColumn(column_1), factory.GetDbObjectFactory().CreateParameter(column_1));
var condition_2 = factory.GetConditionFactory().EqualTo(factory.GetDbObjectFactory().CreateColumn(column_2), factory.GetDbObjectFactory().CreateParameter(column_2));
var condition_3 = factory.GetConditionFactory().EqualTo(factory.GetDbObjectFactory().CreateColumn(column_3), factory.GetDbObjectFactory().CreateParameter(column_3));
var builder = factory.CreateSelectBuilder()
.From(factory.GetDbObjectFactory().CreateTable(table))
.Where(condition_1)
.And(condition_2.And(condition_3))
.Limit(offset, rows);
Assert.Equal($"SELECT * FROM `{ table }` WHERE `{ column_1 }` = @{ column_1 } AND (`{ column_2 }` = @{ column_2 }) AND (`{ column_3 }` = @{ column_3 }) LIMIT { offset }, { rows }", builder.Build());
}
