public Query UpdateQuery(IRepositoryLocator locator, Query updatedQuery)
{
var parameters = ExtractParameters(updatedQuery);
updatedQuery.AssociateParameters(parameters);
return locator.Update(updatedQuery);
}
public void TestRepositoryInMemoryDirectAccess()
{
var queryRepository = new RepositoryInMemory<Query>();
var newQuery = new Query()
{
DbType = EnumDatabase.Oracle,
Description = "Qurey to fetch all planning changes",
Name = "Planning Change",
Text = "SELECT Id FROM DUAL"
};
var newQuery2 = new Query()
{
DbType = EnumDatabase.Oracle,
Description = "Qurey to fetch all rail req changes",
Name = "Rail Reqs",
Text = "SELECT Id FROM DUAL"
};
var q1 = queryRepository.Save(newQuery);
var q2 = queryRepository.Save(newQuery2);
Assert.IsNotNull(q1);
Assert.AreNotEqual(0, q1.Id);
Console.WriteLine("Query Id (1) " + q1.Id);
Assert.IsNotNull(q2);
Assert.AreNotEqual(q1.Id, q2.Id);
Console.WriteLine("Query Id (2) " + q2.Id);
}
public void AddEntityTest()
{
var user = new User
{
Name = "Dennis Predovnik",
CreatedDateTime = DateTime.Now,
};
var q1 = new Query
{
CreatedDateTime = DateTime.Now,
DbType = EnumDatabase.Oracle,
Description = "Query - Simple from Test",
DisplayOnHomeScreen = true,
Name = "RPO - Simple Query",
Text = "SELECT * from Entity",
Owner = user,
ParameterCount = 0,
Published = true
};
ExecuteCommandWithoutResult(locator =>
{
locator.Save(user);
locator.Save(q1);
});
ExecuteCommandWithoutResult(locator => CountEntities(locator, 3));
}
private Query AddDefaultProperties(Query newQuery,User owner)
{
newQuery.CreatedDateTime = DateTime.Now;
newQuery.Owner = owner;
newQuery.Published = true;
newQuery.DbType = EnumDatabase.Oracle;
return newQuery;
}
public Query AddQuery(IRepositoryLocator locator,Query newQuery,string userName)
{
//Resolve windows user name to qt user
var chris = locator.GetById<User>(1);
var parameters = ExtractParameters(newQuery);
newQuery.AssociateParameters(parameters);
return locator.Save(AddDefaultProperties(newQuery,chris));
}
public ActionResult Create([DataSourceRequest] DataSourceRequest request)
{
var q = new Query();
if (TryUpdateModel(q, includeProperties: new string[] { "Name", "Text", "Description" }))
{
new QueryManagementHelper().CreateQuery(q);
}
return Json(new[] { q }.ToDataSourceResult(request));
}
public QueryResult Execute(Query q, QtDbConnection targetQtDb)
{
switch (q.DbType)
{
case EnumDatabase.Oracle:
{
return new OracleExecutor().ExecuteRead(targetQtDb,q);
}
case EnumDatabase.SqlServer:
{
return new MsSqlServerExecutor().ExecuteRead(targetQtDb,q);
}
default:
throw new NotImplementedException(string.Format("Database type {0} is not supported",q.DbType));
}
}
private static void CreateQueries(IRepositoryLocator locator)
{
var user = locator.FetchAll<User>().FirstOrDefault();
if (user == null) throw new InvalidDataException("User is needed before creating query");
var q1 = new Query
{
CreatedDateTime = DateTime.Now,
DbType = EnumDatabase.Oracle,
Description = "Test Query - User Permissions",
DisplayOnHomeScreen = true,
Name = "RPO - Permissions",
Text = "select p.description as User_Or_Group, r.description as RpoMS_Role from role r,principal p,role_principal rp where r.id = rp.role_id and p.id = rp.principal_id",
Owner = user,
ParameterCount = 0,
Published = true
};
var q2 = new Query
{
CreatedDateTime = DateTime.Now,
DbType = EnumDatabase.Oracle,
Description = "Test Query - Rail Requirement Manager",
Text = "Select * from Query",
DisplayOnHomeScreen = true,
Name = "RPO - Rail Req",
Owner = user,
ParameterCount = 1,
Published = true
};
var q3 = new Query
{
CreatedDateTime = DateTime.Now,
DbType = EnumDatabase.Oracle,
Description = "Test Query - PIM All sites",
Text = "Select * from site",
DisplayOnHomeScreen = true,
Name = "PIM - sites",
Owner = user,
ParameterCount = 0,
Published = true
};
locator.Save(q1);
locator.Save(q2);
locator.Save(q3);
}
public QueryResult ExecuteRead(QtDbConnection targetQtDb, Query query)
{
using (var cmd = GetCommand(targetQtDb,query))
{
try
{
cmd.Connection.Open();
List<Field> fields;
var rows = new List<Row>();
using (var reader = cmd.ExecuteReader())
{
fields = GetQueryFieldInfo(reader.GetSchemaTable());
var fieldCount = reader.FieldCount;
while (reader.Read())
{
var row = new Row();
for (int i = 0; i < fieldCount; i++)
{
var obj = reader[i];
row.AddValue(obj.ToString());
}
rows.Add(row);
}
}
return new QueryResult()
{
Rows = rows,
Fields = fields
};
}
catch (Exception ex)
{
throw;
}
finally
{
cmd.Connection.Close();
}
}
}
private List<QueryParameter> ExtractParameters(Query query)
{
//parameters are of the form [[param_name]]
var parameters = new List<QueryParameter>();
var pattern = new Regex(@"\[\[\w+\|?[\w\s]*\]\]"); //matches items like [[product_id]]
var matches = pattern.Matches(query.Text);
if (matches.Count == 0) return parameters;
int paraCount = 0;
foreach (var match in matches)
{
var paraArray = match.ToString().Trim(new char[]{'[',']'}).Split('|');
var param = new QueryParameter()
{
Name = paraArray[0],
Description = paraArray.Length > 1 ? paraArray[1] : string.Empty,
ParameterNumber = paraCount++,
};
parameters.Add(param);
}
return parameters;
}
private OracleCommand GetCommand(QtDbConnection targetQtDb, Query query)
{
return OracleCommandCreator.GetCommand(targetQtDb, query.Text);
}
public Query UpdateQuery(Query updatedQuery)
{
return ExecuteCommand(locator => _queryService.UpdateQuery(locator, updatedQuery));
}
public Query CreateQuery(Query newQuery)
{
var currentUser = SessionManager.CurrentWindowsUserName;
return ExecuteCommand(locator => _queryService.AddQuery(locator, newQuery, currentUser));
}
public QueryResult ExecuteRead(QtDbConnection targetQtDb, Query query)
{
throw new NotImplementedException();
}
public void AddQuery(Query query)
{
Queries.Add(query);
}