public void CreateStoredProcedureIfNotExistsQueryShouldGiveCorrectSyntax()
{
var dbDriverMock = new Mock <IDataDriver>();
dbDriverMock.Setup(x => x.Dialect).Returns(Enums.DialectEnum.MSSQL);
dbDriverMock.Setup(x => x.GoTerminator()).Returns("\nGO");
var subquery = IGNQueriable.Begin("*****@*****.**", dbDriverMock.Object).
Select().
From("test").
IfExists().
WithCondition().
Where(IGNConditionWithParameter.FromConfig("name", Enums.IGNSqlCondition.Eq, 0)).
Go();
var query = IGNQueriable.Begin("*****@*****.**", dbDriverMock.Object).
Create().
StoredProcedure("sp_test", subquery, new List <IGNParameter>()
{
IGNParameter.FromConfig(0, typeof(string), 255)
}).
IfNotExists().
Go();
var expected = "CREATE PROCEDURE [sp_test] @p0 NVARCHAR(255)\nAS\nSELECT * FROM [test] WHERE [name] = @p0 \nGO \nGO";
Assert.AreEqual(expected, query.ToString());
}
public void ADeleteAllUsers()
{
var dataProvider = new MySqlDataDriver("*****@*****.**");
var query = IGNQueriable.Begin("*****@*****.**", dataProvider).
Delete().
From("ignusers").
Go();
dataProvider.Execute(query);
}
public void CDeleteTable()
{
var dataProvider = new MySqlDataDriver("*****@*****.**");
var query = IGNQueriable.Begin("*****@*****.**", dataProvider).
Drop().
Table("ignusers").
IfExists().
Go();
dataProvider.Execute(query);
}
public void UseQueryShouldGiveCorrectSyntax()
{
var dbDriverMock = new Mock <IDataDriver>();
dbDriverMock.Setup(x => x.Dialect).Returns(Enums.DialectEnum.MSSQL);
dbDriverMock.Setup(x => x.GoTerminator()).Returns("\nGO");
var query = IGNQueriable.Begin("*****@*****.**", dbDriverMock.Object).Use("testauth").Go();
var expected = "USE [testauth] \nGO";
Assert.AreEqual(expected, query.ToString());
}
public void CreateDatabaseQueryShouldGiveCorrectSyntax()
{
var dbDriverMock = new Mock <IDataDriver>();
dbDriverMock.Setup(x => x.Dialect).Returns(Enums.DialectEnum.MSSQL);
dbDriverMock.Setup(x => x.GoTerminator()).Returns("\nGO");
var query = IGNQueriable.Begin("*****@*****.**", dbDriverMock.Object).
Create().
Database("testdb").
IfNotExists().
Go();
var expected = "CREATE DATABASE [testdb] \nGO";
Assert.AreEqual(expected, query.ToString());
}
public void CreateViewIfNotExistsQueryShouldGiveCorrectSyntax()
{
var dbDriverMock = new Mock <IDataDriver>();
dbDriverMock.Setup(x => x.Dialect).Returns(Enums.DialectEnum.MSSQL);
dbDriverMock.Setup(x => x.GoTerminator()).Returns("\nGO");
var query = IGNQueriable.Begin("*****@*****.**", dbDriverMock.Object).
Create().
View("dm_test", IGNQueriable.FromQueryString("SELECT * FROM test WHERE name = 'test'", "*****@*****.**", dbDriverMock.Object)).
IfNotExists().
Go();
var expected = "CREATE VIEW [dm_test]\nAS \nSELECT * FROM test WHERE name = 'test' \nGO";
Assert.AreEqual(expected, query.ToString());
}
public void BAlterTableIfExists()
{
var dataProvider = new MySqlDataDriver("*****@*****.**");
var query = IGNQueriable.Begin("*****@*****.**", dataProvider).
Alter().
Table("ignusers").
IfExists().
Add().
Column(TableColumnConfiguration.FromConfig("test1", typeof(string), 25, false, false, false, string.Empty)).
IfNotExists().
Add().
Column(TableColumnConfiguration.FromConfig("test2", typeof(string), 25, false, false, false, string.Empty)).
IfNotExists().
Go();
dataProvider.Execute(query);
query = IGNQueriable.Begin("*****@*****.**", dataProvider).
Alter().
Table("ignusers").
IfExists().
Alter().
Column(TableColumnConfiguration.FromConfig("test1", typeof(string), 50, false, false, false, string.Empty)).
IfExists().
Go();
dataProvider.Execute(query);
query = IGNQueriable.Begin("*****@*****.**", dataProvider).
Alter().
Table("ignusers").
IfExists().
Drop("test1").
IfExists().
Drop("test2").
IfExists().
Go();
dataProvider.Execute(query);
var dataDriver = new MySqlDataDriver("*****@*****.**");
var column = TableColumnConfiguration.FromConfig("createdOn", typeof(DateTime), 0, false, true, false, string.Empty);
var altquery = IGNQueriable.Begin("*****@*****.**", dataDriver).
Alter().
Table("ignusers").
IfExists().
Add().
Column(column).
IfNotExists().
Go();
dataDriver.Execute(altquery);
}
public void CreateTableQueryShouldGiveCorrectSyntax()
{
var dbDriverMock = new Mock <IDataDriver>();
dbDriverMock.Setup(x => x.Dialect).Returns(Enums.DialectEnum.MSSQL);
dbDriverMock.Setup(x => x.GoTerminator()).Returns("\nGO");
dbDriverMock.Setup(x => x.GetDbAutoGenFor(It.IsAny <Type>(), It.IsAny <int>())).Returns(" IDENTITY(1,1)");
var query = IGNQueriable.Begin("*****@*****.**", dbDriverMock.Object).Create().Table("test", new List <TableColumnConfiguration>()
{
TableColumnConfiguration.FromConfig("id", typeof(long), 0, true, true, true, null),
TableColumnConfiguration.FromConfig("name", typeof(string), 255, false, false, false, null)
}).IfNotExists().Go();
var expected = "CREATE TABLE [test]([id] BIGINT NOT NULL IDENTITY(1,1),[name] NVARCHAR(255) NULL,CONSTRAINT PK_test PRIMARY KEY([id])) \nGO";
Assert.AreEqual(expected, query.ToString());
}
public void UpdateQueryShouldHaveCorrectSyntax()
{
var dbDriverMock = new Mock <IDataDriver>();
dbDriverMock.Setup(x => x.Dialect).Returns(Enums.DialectEnum.MSSQL);
dbDriverMock.Setup(x => x.GoTerminator()).Returns("\nGO");
var query = IGNQueriable.Begin("*****@*****.**", dbDriverMock.Object).
Update().
Table("users").
IfExists().
SetParametrizedWithCondition("loggedInDateTime", 0).
Where(IGNConditionWithParameter.FromConfig("Id", Enums.IGNSqlCondition.Eq, 1)).
Go();
var expected = "UPDATE [users] SET [loggedInDateTime] = @p0 WHERE [Id] = @p1 \nGO";
Assert.AreEqual(expected, query.ToString());
}
public void ACreateTableIfNotExists()
{
var dataProvider = new MySqlDataDriver("*****@*****.**");
var paramList = new List <TableColumnConfiguration>()
{
TableColumnConfiguration.FromConfig("id", typeof(long), 0, true, true, true, string.Empty),
TableColumnConfiguration.FromConfig("userId", typeof(long), 0, true, false, false, null),
TableColumnConfiguration.FromConfig("mail", typeof(string), 254, true, false, false, string.Empty),
TableColumnConfiguration.FromConfig("active", typeof(bool), 0, true, false, false, true)
};
var query = IGNQueriable.Begin("*****@*****.**", dataProvider).
Create().
Table("ignusers", paramList).
IfNotExists().
Go();
dataProvider.Execute(query);
}
public void AlterColumnsQueryShouldGiveCorrectSyntax()
{
var dbDriverMock = new Mock <IDataDriver>();
dbDriverMock.Setup(x => x.Dialect).Returns(Enums.DialectEnum.MSSQL);
dbDriverMock.Setup(x => x.GoTerminator()).Returns("\nGO");
var query = IGNQueriable.Begin("*****@*****.**", dbDriverMock.Object).
Alter().
Table("test").
IfExists().
Alter().
Column(TableColumnConfiguration.FromConfig("test1", typeof(string), 25, false, false, false, "")).
IfExists().
Go();
var expected = "ALTER TABLE [test] ALTER COLUMN [test1] NVARCHAR(25) NULL \nGO";
Assert.AreEqual(expected, query.ToString());
}
public void SelectDistinctQueryShouldHaveCorrectSyntax()
{
var dbDriverMock = new Mock <IDataDriver>();
dbDriverMock.Setup(x => x.Dialect).Returns(Enums.DialectEnum.MSSQL);
dbDriverMock.Setup(x => x.GoTerminator()).Returns("\nGO");
var query = IGNQueriable.Begin("*****@*****.**", dbDriverMock.Object).
Select(new List <string> {
"test.id", "test2.test", "test.test"
}, true).
From("test").
IfExists().
Join("test", "test2", "test2id", "id", true).
Go();
var expected = "SELECT DISTINCT [test].[id],[test2].[test],[test].[test] FROM [test] INNER JOIN [test2] ON [test].[test2id] = [test2].[id] \nGO";
Assert.AreEqual(expected, query.ToString());
}
public void AlterDropColumnsQueryShouldGiveCorrectSyntax()
{
var dbDriverMock = new Mock <IDataDriver>();
dbDriverMock.Setup(x => x.Dialect).Returns(Enums.DialectEnum.MSSQL);
dbDriverMock.Setup(x => x.GoTerminator()).Returns("\nGO");
var query = IGNQueriable.Begin("*****@*****.**", dbDriverMock.Object).
Alter().
Table("test").
IfExists().
Drop("test1").
IfExists().
Drop("test2").
IfExists().
Go();
var expected = "ALTER TABLE [test] DROP COLUMN [test1], [test2] \nGO";
Assert.AreEqual(expected, query.ToString());
}
public void BCreateUserIfNotExists()
{
var dataProvider = new MySqlDataDriver("*****@*****.**");
var query = IGNQueriable.Begin("*****@*****.**", dataProvider).
Insert().
Into("ignusers", new List <string>()
{
"mail", "userId"
}).
IfExists().
ValuesWithParams(new List <int> {
0, 1
}).
Go();
dataProvider.ExecuteWithParameters(query, new List <IGNParameterValue>
{
IGNParameterValue.FromConfig(0, "*****@*****.**"),
IGNParameterValue.FromConfig(1, -1)
});
}
public void BCreateStoredProcedureIfNotExists()
{
var dataProvider = new MySqlDataDriver("*****@*****.**");
var query = IGNQueriable.Begin("*****@*****.**", dataProvider).
Drop().
StoredProcedure("testProc").
IfExists().
Go();
dataProvider.Execute(query);
var spQuery = IGNQueriable.Begin("*****@*****.**", dataProvider).
Select().
From("ignusers").
IfExists().
Go();
query = IGNQueriable.Begin("*****@*****.**", dataProvider).
Create().
StoredProcedure("testProc", spQuery).
IfNotExists().
Go();
dataProvider.Execute(query);
}
