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 DataTable ReadData(IGNQueriable query)
{
if (query.CanExecute)
{
return(ReadDataWithParameters(query, query.ParamValues));
}
return(null);
}
private void SetStoresProcedureExists(string name, IGNQueriable queriable, ExistsEnum existsFunc)
{
var checkQuery = $"SELECT * FROM [INFORMATION_SCHEMA].[ROUTINES] WHERE [ROUTINE_NAME] = '{name}'";
var query = IGNQueriable.FromQueryString(checkQuery, this.email, this);
var result = ReadDataWithParameters(query, new List <IGNParameterValue>());
IGNQueriable.SetExists(result.Rows.Count > 0, queriable);
IGNQueriable.SetCanExecute(existsFunc, queriable);
}
private void SetViewExists(string name, IGNQueriable queriable, ExistsEnum existsFunc)
{
var checkQuery = $"SELECT * FROM `INFORMATION_SCHEMA`.`VIEWS` WHERE `TABLE_SCHEMA` = database() AND `TABLE_NAME` = '{name}'";
var query = IGNQueriable.FromQueryString(checkQuery, this.email, this);
var result = ReadDataWithParameters(query, new List <IGNParameterValue>());
IGNQueriable.SetExists(result.Rows.Count > 0, queriable);
IGNQueriable.SetCanExecute(existsFunc, queriable);
}
private void SetIndexExists(string name, string table, IGNQueriable queriable, ExistsEnum existsFunc)
{
var checkQuery = $"SELECT * FROM sysindexes WHERE name='{name}'";
var query = IGNQueriable.FromQueryString(checkQuery, this.email, this);
var result = ReadDataWithParameters(query, new List <IGNParameterValue>());
IGNQueriable.SetExists(result.Rows.Count > 0, queriable);
IGNQueriable.SetCanExecute(existsFunc, queriable);
}
private void SetColumnExists(string name, string table, IGNQueriable queriable, ExistsEnum existsFunc)
{
var checkQuery = $"SELECT * FROM [INFORMATION_SCHEMA].[COLUMNS] WHERE [TABLE_CATALOG] = DB_NAME() AND [TABLE_NAME] = '{table}' AND [COLUMN_NAME] = '{name}'";
var query = IGNQueriable.FromQueryString(checkQuery, this.email, this);
var result = ReadDataWithParameters(query, new List <IGNParameterValue>());
IGNQueriable.SetExists(result.Rows.Count > 0, queriable);
IGNQueriable.SetCanExecute(existsFunc, queriable);
}
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 ExecuteWithParameters(IGNQueriable query, IEnumerable <IGNParameterValue> args)
{
if (query.CanExecute)
{
using (var connection = OpenConnection())
{
var dbc = PrepareDbCommand(query.ToString(), connection);
AddParameters(dbc, args);
dbc.ExecuteNonQuery();
}
}
}
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 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 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 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 DataTable ReadDataWithParameters(IGNQueriable query, IEnumerable <IGNParameterValue> args)
{
DataTable result = null;
if (query.CanExecute)
{
using (var connection = OpenConnection())
{
var dbc = PrepareDbCommand(query.ToString(), connection);
AddParameters(dbc, args);
using (var dbReader = dbc.ExecuteReader())
{
result = InitDataTable(dbReader);
}
}
}
return(result);
}
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 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 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);
}
public override void IfViewNotExists(string name, IGNQueriable queriable)
{
SetViewExists(name, queriable, ExistsEnum.NotExists);
}
public override void IfTableNotExists(string name, IGNQueriable queriable)
{
SetTableExists(name, queriable, ExistsEnum.NotExists);
}
public override void IfStoredProcedureNotExists(string name, IGNQueriable queriable)
{
SetStoresProcedureExists(name, queriable, ExistsEnum.NotExists);
}
public override void IfIndexNotExists(string name, string table, IGNQueriable queriable)
{
SetIndexExists(name, table, queriable, ExistsEnum.NotExists);
}
public override void IfDatabaseExists(string name, IGNQueriable queriable)
{
SetDatabaseExists(name, queriable, ExistsEnum.Exists);
}
public override void IfColumnNotExists(string name, string table, IGNQueriable queriable)
{
SetColumnExists(name, table, queriable, ExistsEnum.NotExists);
}
public void Execute(IGNQueriable query)
{
ExecuteWithParameters(query, query.ParamValues);
}
public virtual void IfIndexExists(string name, string table, IGNQueriable queriable)
{
throw new NotImplementedException(this.nonSpecDriverErr);
}
