/// <summary>
/// Map to an existing instance
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="query"></param>
/// <param name="ignoreExtraRows"></param>
/// <param name="target"></param>
/// <param name="parameters"></param>
/// <returns></returns>
protected bool TryGetSingle <T>(
object query,
T target,
bool ignoreExtraRows,
params object[] parameters)
{
var wrapper = new SqlQueryParser <T>(QueryType.Select, query, Transaction, parameters);
wrapper.ExpectSelectQuery();
bool looped = false;
using (IDataReader reader = wrapper.RunQuery(StorageController))
{
DataReaderWrapper drWrap = new DataReaderWrapper(reader);
MapperWrapper <T> mapWrap = new MapperWrapper <T>(drWrap);
foreach (var item in mapWrap)
{
if (looped)
{
throw new IQException("More than one record was returned by the Single<T> query");
}
target = item;
looped = true;
if (ignoreExtraRows)
{
break;
}
}
}
return(looped);
}
static void Main(string[] args)
{
var services = new ServiceCollection();
services.AddSingleton <ITest1, TestClass1>();
services.AddSingleton <ITest, TestClass>();
var serviceProvider = services.BuildServiceProvider();
ActivatorUtilities.CreateInstance <TestClass>(serviceProvider);
var select = new SelectQueryBuilder()
.Select(Tables.U.ID, Tables.U.Username, Tables.P.Email)
.Where(Tables.U.Age > 30 | Tables.P.Email.Like("*****@*****.**"))
.Where(Tables.U.ID != null)
.From(Tables.Users.As("U").InnerJoin(Tables.UserProfile.As("P")).On(Tables.U.ID == Tables.P.ID))
.Build();
var insert = new InsertQueryBuilder()
.InsertInto(Tables.Users, Names.ID, Names.Username, Names.Email)
.WithValues(ValuesClause.Values((1, "Ivony", "*****@*****.**")))
.Build();
var parser = new SqlQueryParser();
ShowQuery(parser.ParseSelectQuery(select));
ShowQuery(parser.ParseInsertQuery(insert));
Console.ReadKey();
}
/// <summary>
/// Executa as várias consultas.
/// </summary>
/// <param name="session"></param>
/// <param name="queryInfo"></param>
/// <returns></returns>
protected virtual IQueryResult Execute(GDASession session, QueryInfo queryInfo)
{
if (session.State == GDASessionState.Closed)
{
throw new QueryException("Database connection is closed to execute query");
}
queryInfo.Require("queries").NotNull();
string providerName = ProviderLocator.GetProviderName(queryInfo);
var providerConfiguration = GDASettings.GetProviderConfiguration(providerName);
if (queryInfo.StoredProcedureName == null)
{
SqlQueryParser parser = CreateParser(queryInfo);
string queryString = parser.GetText();
try
{
return(ExecuteQuery(session, queryString, queryInfo));
}
catch (GDAException ex)
{
throw new QueryException(string.Format("{0}. Query: {1}", ex.Message, queryInfo.ToString()), ex.InnerException);
}
}
else
{
return(ExecuteStoredProcedure(session, queryInfo));
}
}
public RbacEngineWebResponse Post([FromBody] RbacEngineWebRequest request)
{
RbacEngineWebResponse response = new RbacEngineWebResponse();
try
{
response.UserName = request.UserName;
response.RoleName = request.RoleName;
using (Rbac ctx = new Rbac(request.UserName, request.RbacName, request.RoleName))
{
response.RbacName = request.RbacName;
SqlQueryParser parser = new SqlQueryParser(ctx, request.SkipParsing);
parser.Parse(request.Query);
using (RbacSqlQueryEngine eng = new RbacSqlQueryEngine(parser, request.DebugMode))
{
eng.SkipExecution = request.SkipExecution;
eng.Execute();
response.SetResult(eng);
}
}
}
catch (Exception ex)
{
response.SetResult(ex.Message);
}
return(response);
}
private static void ParseUsingMangedParser(SqlQuerySpec sqlQuerySpec)
{
if (!SqlQueryParser.TryParse(sqlQuerySpec.QueryText, out SqlQuery sqlQuery))
{
throw new InvalidOperationException("FAILED TO PARSE QUERY.");
}
}
public void Untouched(string sql)
{
_params.AddWithValue(":param", "foo");
SqlQueryParser.ParseRawQuery(sql, true, _params, _queries);
Assert.That(_queries.Single().SQL, Is.EqualTo(sql));
Assert.That(_queries.Single().InputParameters, Is.Empty);
}
public void ParamSimple()
{
_params.AddWithValue(":p1", "foo");
_params.AddWithValue(":p2", "bar");
SqlQueryParser.ParseRawQuery("SELECT :p1, :p2", true, _params, _queries);
Assert.That(_queries.Single().InputParameters, Is.EqualTo(_params));
}
public void NonConformantStrings()
{
var parser = new SqlQueryParser(false);
parser.ParseRawQuery(@"SELECT 'abc\':str''a:str'", _params, _queries);
Assert.That(_queries.Single().SQL, Is.EqualTo(@"SELECT 'abc\':str''a:str'"));
Assert.That(_queries.Single().InputParameters, Is.Empty);
}
public void MissingParamIsIgnored()
{
SqlQueryParser.ParseRawQuery("SELECT @p; SELECT 1", true, _params, _queries);
Assert.That(_queries[0].SQL, Is.EqualTo("SELECT @p"));
Assert.That(_queries[1].SQL, Is.EqualTo("SELECT 1"));
Assert.That(_queries[0].InputParameters, Is.Empty);
Assert.That(_queries[1].InputParameters, Is.Empty);
}
public void NoOutputParameters()
{
var p = new NpgsqlParameter("p", DbType.String)
{
Direction = ParameterDirection.Output
};
_params.Add(p);
Assert.That(() => SqlQueryParser.ParseRawQuery("SELECT @p", true, _params, _queries), Throws.Exception);
}
List <NpgsqlBatchCommand> ParseCommand(string sql, NpgsqlParameter[] parameters, bool standardConformingStrings)
{
var cmd = new NpgsqlCommand(sql);
cmd.Parameters.AddRange(parameters);
var parser = new SqlQueryParser();
parser.ParseRawQuery(cmd, standardConformingStrings);
return(cmd.InternalBatchCommands);
}
private void IsAllowedToInsertOrUpdateOrDelete(string query = null)
{
using (Rbac rbac = new Rbac("essie")) //<-- you should pass the logged in user name from the context
{
using (SqlQueryParser parser = new SqlQueryParser(rbac))
{
parser.Parse(query); //<-- this will throw error if not permitted and silent is false
}
}
}
private void IsAllowedToInsertOrUpdateOrDelete(string query = null)
{
using (Rbac rbac = new Rbac("essie")) //<-- you should pass the logged in user name from the context
{
using (SqlQueryParser parser = new SqlQueryParser(rbac))
{
parser.Parse(query); //<-- this will throw exception if not permitted
//<-- if you are here, you are goood. Just perform basic insert/update/delete
}
}
}
private void LoadAssemblies()
{
//this will load the assembly into memory, so that 2nd call is more efficient
try
{
Rbac rbac = new Rbac("Lashawn", "Books", "role_city_mgr");
SqlQueryParser parser = new SqlQueryParser(rbac);
parser.Parse("select * from Author");
}
catch { }
}
private void btnExecute_Click(object sender, EventArgs e)
{
SetStatusText("Parsing...");
txtErrors.Text = string.Empty;
txtParsedQuerys1.Text = string.Empty;
txtParsedQuery.Text = string.Empty;
txtErrors.Visible = false;
RbacEngineWebResponse response = new RbacEngineWebResponse();
this.Cursor = Cursors.WaitCursor;
try
{
_Request.RbacName = ((Rbac)cbInstances.SelectedItem).Name;
_Request.UserName = ((RbacUser)cbUsers.SelectedItem).UserName;
_Request.RoleName = ((RbacRole)cbRoles.SelectedItem).Name;
_Request.Query = txtQuery.Text;
engineInput.SelectedObject = _Request;
using (Rbac ctx = new Rbac(_Request.UserName, _Request.RbacName, _Request.RoleName))
{
SqlQueryParser parser = new SqlQueryParser(ctx, _Request.SkipParsing);
parser.Parse(_Request.Query);
response.SetResult(parser);
BindResult(response);
SetStatusText("Parsing...Done.", response);
if (parser.QueryType == RbacQueryTypes.Select)
{
SetStatusText("Parsing...Done. Executing...", response);
using (RbacSqlQueryEngine eng = new RbacSqlQueryEngine(parser, _Request.DebugMode))
{
eng.SkipExecution = _Request.SkipExecution;
eng.Execute();
response.SetResult(eng);
SetStatusText("Parsing...Done. Executing...Done.", response);
}
}
}
}
catch (RbacException ex)
{
txtErrors.Text = ex.Message;
txtErrors.Visible = true;
SetStatusText("Done.");
}
BindResult(response);
tabControl1.SelectedIndex = 0;
this.Cursor = Cursors.Default;
}
protected bool TryGetSingle <T>(
object query,
out T target,
bool ignoreExtraRows,
params object[] parameters)
{
var wrapper = new SqlQueryParser <T>(QueryType.Select, query, Transaction, parameters);
wrapper.ExpectSelectQuery();
//wrapper.Query.Top = 1;
return(TryGetSingle(wrapper, ignoreExtraRows, out target));
}
public void ReduceNumberOfStatements()
{
var parser = new SqlQueryParser();
var cmd = new NpgsqlCommand("SELECT 1; SELECT 2");
parser.ParseRawQuery(cmd);
Assert.That(cmd.InternalBatchCommands, Has.Count.EqualTo(2));
cmd.CommandText = "SELECT 1";
parser.ParseRawQuery(cmd);
Assert.That(cmd.InternalBatchCommands, Has.Count.EqualTo(1));
}
private void btnExecuteAll_Click(object sender, EventArgs e)
{
if (lvwQueries.Tag != null)
{
DataTable table = lvwQueries.Tag as DataTable;
if (table.Columns["ParsedQueryStage1"] == null)
{
table.Columns.Add("ParsedQueryStage1");
table.Columns.Add("ParsedQuery");
table.Columns.Add("Errors");
}
foreach (DataRow row in table.Rows)
{
try
{
Rbac rbac = new Rbac(row["User"].ToString(), "Books", row["Role"].ToString());
SqlQueryParser parser = new SqlQueryParser(rbac);
parser.Parse(row["Query"].ToString());
RbacSqlQueryEngine engine = new RbacSqlQueryEngine(parser, true);
engine.Execute();
row["ParsedQueryStage1"] = parser.ParsedQueryStage1;
row["ParsedQuery"] = parser.ParsedQuery;
row["Errors"] = parser.AllErrors + Environment.NewLine;
}
catch (Exception ex)
{
row["Errors"] = ex.Message;
}
}
string fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, table.TableName + "_out.csv");
try
{
table.ToCsv(fileName);
MessageBox.Show("Test results are saved on " + fileName + "!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message,
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
public void MultiqueryWithParams()
{
var p1 = new NpgsqlParameter("p1", DbType.String);
_params.Add(p1);
var p2 = new NpgsqlParameter("p2", DbType.String);
_params.Add(p2);
var p3 = new NpgsqlParameter("p3", DbType.String);
_params.Add(p3);
SqlQueryParser.ParseRawQuery("SELECT @p3, @p1; SELECT @p2, @p3", true, _params, _queries);
Assert.That(_queries, Has.Count.EqualTo(2));
Assert.That(_queries[0].InputParameters[0], Is.SameAs(p3));
Assert.That(_queries[0].InputParameters[1], Is.SameAs(p1));
Assert.That(_queries[1].InputParameters[0], Is.SameAs(p2));
Assert.That(_queries[1].InputParameters[1], Is.SameAs(p3));
}
// POST: api/Rbac
public string Post([FromBody] RbacWebRequest request)
{
RbacWebResponse response = new RbacWebResponse(request.RbacId);
if (request.RbacId == 0)
{
response.SetResult(string.Format("Rbac instance with rbac id '{0}' not found!", request.RbacId));
return(JsonConvert.SerializeObject(response));
}
RbacUser user = new Rbac(request.RbacId).GetUser(request.UserName);
if (user == null)
{
response.SetResult(string.Format("User '{0}' not found!", request.UserName));
return(JsonConvert.SerializeObject(response));
}
if (!string.IsNullOrEmpty(request.RoleName))
{
user.Role = new RbacRole(request.RoleName); //dangerous code, only to be used for role testing
if (user.Role == null)
{
response.SetResult(string.Format("Role '{0}' not found!", request.RoleName));
return(JsonConvert.SerializeObject(response));
}
}
SqlQueryParser parser = new SqlQueryParser(new RbacContext(user), request.SkipParsing);
parser.Parse(request.Query);
RbacSqlQueryEngine engine = new RbacSqlQueryEngine(parser, request.DebugMode);
engine.SkipExecution = request.SkipExecution;
engine.Execute();
response.SetResult(engine);
return(JsonConvert.SerializeObject(response));
}
public void SetUp()
{
_parser = new SqlQueryParser();
_queries = new List <NpgsqlStatement>();
_params = new NpgsqlParameterCollection();
}
public void TestBatch()
{
GenericParserAdapter genParser = new GenericParserAdapter(Path.Combine(_rootDir, "Books", "tests.csv"));
genParser.FirstRowHasHeader = true;
DataTable table = genParser.GetDataTable();
if (table.Columns["ParsedQueryStage1"] == null)
{
table.Columns.Add("ParsedQueryStage1");
table.Columns.Add("ParsedQuery");
table.Columns.Add("Records");
table.Columns.Add("Errors");
table.Columns.Add("TestResult");
}
bool cleaned = false;
foreach (DataRow row in table.Rows)
{
//if (row["Id"].ToString() == "11")
// Debugger.Break();
Rbac rbac = new Rbac(row["User"].ToString());
RbacRole role = Rbac.GetRole(row["Role"].ToString());
if (!cleaned)
{
CleanDataFromDb(rbac.ConnectionString);
cleaned = true;
}
SqlQueryParser parser = new SqlQueryParser(rbac);
try
{
parser.Parse(row["Query"].ToString());
}
catch (Exception ex)
{
row["Errors"] = ex.Message;
if (row["Expected"].ToString().Equals(row["Errors"].ToString()))
{
row["TestResult"] = "Passed";
}
else
{
row["TestResult"] = "Failed";
}
continue;
}
row["ParsedQueryStage1"] = parser.ParsedQueryStage1;
row["ParsedQuery"] = parser.ParsedQuery;
row["Errors"] += parser.AllErrors;
if (string.IsNullOrEmpty(parser.AllErrors))
{
RbacSqlQueryEngine engine = new RbacSqlQueryEngine(parser, true);
engine.Execute();
if (engine.IsErrored)
{
row["Records"] = "Errored";
}
else if ((parser.QueryType == RbacQueryTypes.Select) && (engine.Table == null))
{
row["Records"] = "Errored";
}
else if ((parser.QueryType == RbacQueryTypes.Select) && (engine.Table != null))
{
row["Records"] = engine.Table.Rows.Count + " record(s)";
}
if (!string.IsNullOrEmpty(parser.AllErrors))
{
row["Errors"] += parser.AllErrors + Environment.NewLine;
}
if (!string.IsNullOrEmpty(engine.AllErrors))
{
row["Errors"] += engine.AllErrors + Environment.NewLine;
}
}
if (row["Expected"].ToString().Equals(row["Errors"].ToString()))
{
row["TestResult"] = "Passed";
}
else
{
row["TestResult"] = "Failed";
}
CleanDataFromDb(rbac.ConnectionString);
}
string outFile = Path.Combine(_rootDir, "Books", "tests_result.csv");
table.ToCsv(outFile);
WriteColor(ConsoleColor.Green, outFile + " is generated!");
Console.WriteLine();
ToCsvMarkdownFormat(table, Path.Combine(_rootDir, "Books", "tests_result.md"));
}
/// <summary>
/// Executa as várias consultas.
/// </summary>
/// <param name="queries"></param>
/// <returns></returns>
public override IEnumerable <IQueryResult> Execute(QueryInfo[] queries)
{
queries.Require("queries").NotNull();
var result = new IQueryResult[queries.Length];
Dictionary <QueryExecutionGroup, IList <int> > providerDictionary = new Dictionary <QueryExecutionGroup, IList <int> >(QueryExecutionGroup.Comparer);
for (int i = 0; i < queries.Length; i++)
{
string providerName = ProviderLocator.GetProviderName(queries[i]);
var group = new QueryExecutionGroup(providerName, queries[i].IsolationLevel);
IList <int> indexes;
if (providerDictionary.TryGetValue(group, out indexes))
{
indexes.Add(i);
}
else
{
indexes = new List <int>();
indexes.Add(i);
providerDictionary.Add(group, indexes);
}
}
IStoredProcedureTransaction storedProcedureTransaction = null;
foreach (var provider in providerDictionary)
{
using (var session = CreateSession(provider.Key))
{
RegisterSession(session);
foreach (var index in provider.Value)
{
IQueryResult queryResult = null;
try
{
if (queries[index].StoredProcedureName == null)
{
SqlQueryParser parser = CreateParser(queries[index]);
string queryString = parser.GetText();
try
{
queryResult = ExecuteQuery(session, queryString, queries[index]);
}
catch (GDAException ex)
{
throw new GDAException(string.Format("{0}. Query: {1}", ex.Message, queries[index].ToString()), ex.InnerException);
}
}
else
{
if (storedProcedureTransaction == null)
{
storedProcedureTransaction = new GDAStoredProcedureTransaction(session, provider.Key.ProviderName);
if (!queries[index].IgnoreRegisterUserInfo)
{
RegisterUserInfo(storedProcedureTransaction);
}
}
queryResult = ExecuteStoredProcedure(session, queries[index]);
}
}
catch
{
if (session is GDATransaction)
{
((GDATransaction)session).Rollback();
}
throw;
}
yield return(queryResult);
}
if (session is GDATransaction)
{
((GDATransaction)session).Commit();
}
}
}
}
public int Delete <T>(object query, params object[] parameters)
{
var wrapper = new SqlQueryParser <T>(QueryType.Delete, query, Transaction, parameters);
return(wrapper.RunQueryScalar(StorageController));
}
public int QueryScalar(string query, params object[] parameters)
{
var wrapper = new SqlQueryParser <object>(0, query, Transaction, parameters);
return(wrapper.RunQueryScalar(StorageController));
}
public int Count <T>(object query, params object[] parameters)
{
var wrapper = new SqlQueryParser <T>(QueryType.Select, query, Transaction, parameters);
return(StorageController.Count(wrapper.Connection, wrapper.Query));
}
private void btnExecuteAll_Click(object sender, EventArgs e)
{
Cursor = Cursors.WaitCursor;
toolStripProgressBar1.Visible = true;
if (lvwQueries.Tag != null)
{
DataTable table = lvwQueries.Tag as DataTable;
toolStripProgressBar1.Maximum = table.Rows.Count;
if (table.Columns["ParsedQueryStage1"] == null)
{
table.Columns.Add("ParsedQueryStage1");
table.Columns.Add("ParsedQuery");
table.Columns.Add("Errors");
}
foreach (DataRow row in table.Rows)
{
try
{
Rbac rbac = new Rbac(row["User"].ToString(), "Books", row["Role"].ToString());
RbacEngineWebResponse response = new RbacEngineWebResponse();
this.Cursor = Cursors.WaitCursor;
_Request.RbacName = rbac.Name;
_Request.UserName = rbac.User.UserName;
_Request.RoleName = rbac.User.Role.Name;
_Request.Query = row["Query"].ToString();
SqlQueryParser parser = new SqlQueryParser(rbac);
parser.Parse(_Request.Query);
response.SetResult(parser);
SetStatusText("Parsing...Done.", response);
if (parser.QueryType == RbacQueryTypes.Select)
{
SetStatusText("Parsing...Done. Executing...", response);
RbacSqlQueryEngine engine = new RbacSqlQueryEngine(parser, true);
engine.Execute();
response.SetResult(engine);
SetStatusText("Parsing...Done. Executing...Done.", response);
}
row["ParsedQueryStage1"] = parser.ParsedQueryStage1;
row["ParsedQuery"] = parser.ParsedQuery;
row["Errors"] = parser.AllErrors + Environment.NewLine;
SetStatusText("Done.", response);
}
catch (Exception ex)
{
row["Errors"] = ex.Message;
}
toolStripProgressBar1.PerformStep();
Application.DoEvents();
}
toolStripProgressBar1.Visible = false;
string fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, table.TableName + "_out.csv");
try
{
table.ToCsv(fileName);
MessageBox.Show("Test results are saved on " + fileName + "!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
Cursor = Cursors.Default;
MessageBox.Show(ex.Message,
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
tabControl1.SelectedIndex = 0;
Cursor = Cursors.Default;
}
}
public IEnumerable <T> Select <T>(object query, params object[] parameters)
{
var wrapper = new SqlQueryParser <T>(QueryType.Select, query, Transaction, parameters);
return(MapAll <T>(wrapper.RunQuery(StorageController), wrapper.Buffered));
}
public string GetText(SqlQueryParser a, Colosoft.Query.TakeParameters b)
{
return(new GDA.Provider.Oracle.OracleProvider().SQLCommandLimit(null, a.Text, b.Skip, b.Take));
}
/// <summary>
/// Return true of anything matches the criteria
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="where"></param>
/// <param name="parameters"></param>
/// <returns></returns>
//public bool Any<T>(string where, params object[] parameters)
//{
// var wrapper = new SqlQueryParser<T>(QueryType.Where, where, Transaction, parameters);
// wrapper.ExpectSelectQuery();
// //wrapper.QueryFull.Top = 1;
// wrapper.QueryFull.Select = "1";
// int target;
// return TryGetSingle<int>(wrapper,false,out target);
//}
public IEnumerable <T> Where <T>(string where, params object[] parameters)
{
var wrapper = new SqlQueryParser <T>(QueryType.Where, where, Transaction, parameters);
return(MapAll <T>(wrapper.RunQuery(StorageController), wrapper.Buffered));
}
