static void parseParameters(JProperty jpParameters, Method method, IDictionary<string, ParameterType> parameterTypes, Func<string, string> tokenLookup)
{
if (jpParameters.Value.Type != JTokenType.Object)
{
method.Errors.Add("The `parameters` property is expected to be of type object");
return;
}
var joParameters = (JObject)jpParameters.Value;
// Keep track of unique SQL parameter names:
var sqlNames = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
// Parse parameter properties:
method.Parameters = new Dictionary<string, Parameter>(joParameters.Count, StringComparer.OrdinalIgnoreCase);
foreach (var jpParam in joParameters.Properties())
{
if (jpParam.Value.Type != JTokenType.Object)
{
method.Errors.Add("Parameter property '{0}' is expected to be of type object".F(jpParam.Name));
continue;
}
var joParam = (JObject)jpParam.Value;
var sqlName = getString(joParam.Property("sqlName")).Interpolate(tokenLookup);
var sqlType = getString(joParam.Property("sqlType")).Interpolate(tokenLookup);
var typeName = getString(joParam.Property("type")).Interpolate(tokenLookup);
var isOptional = getBool(joParam.Property("optional")) ?? false;
var desc = getString(joParam.Property("description"));
object defaultValue = DBNull.Value;
// Assign a default `sqlName` if null:
if (sqlName == null) sqlName = "@" + jpParam.Name;
// TODO: validate sqlName is valid SQL parameter identifier!
if (sqlNames.Contains(sqlName))
{
method.Errors.Add("Duplicate SQL parameter name (`sqlName`): '{0}'".F(sqlName));
continue;
}
var param = new Parameter()
{
Name = jpParam.Name,
SqlName = sqlName,
Description = desc,
IsOptional = isOptional
};
if (sqlType != null)
{
int? length;
int? scale;
var typeBase = parseSqlType(sqlType, out length, out scale);
var sqlDbType = getSqlType(typeBase);
if (!sqlDbType.HasValue)
{
method.Errors.Add("Unknown SQL type name '{0}' for parameter '{1}'".F(typeBase, jpParam.Name));
continue;
}
else
{
param.SqlType = new ParameterType()
{
TypeBase = typeBase,
SqlDbType = sqlDbType.Value,
Length = length,
Scale = scale
};
}
}
else
{
ParameterType paramType;
if (!parameterTypes.TryGetValue(typeName, out paramType))
{
method.Errors.Add("Could not find parameter type '{0}' for parameter '{1}'".F(typeName, jpParam.Name));
continue;
}
param.Type = paramType;
}
if (isOptional)
{
var jpDefault = joParam.Property("default");
if (jpDefault != null)
{
// Parse the default value into a SqlValue:
param.DefaultSQLValue = jsonToSqlValue(jpDefault.Value, param.SqlType ?? param.Type);
param.DefaultCLRValue = jsonToCLRValue(jpDefault.Value, param.SqlType ?? param.Type);
}
else
{
// Use null:
param.DefaultSQLValue = DBNull.Value;
param.DefaultCLRValue = null;
}
}
method.Parameters.Add(jpParam.Name, param);
}
}
static void parseQuery(JProperty jpQuery, Method method, Func<string, string> tokenLookup)
{
if (jpQuery.Value.Type != JTokenType.Object)
{
method.Errors.Add("The `query` property is expected to be an object");
return;
}
var joQuery = (JObject)jpQuery.Value;
method.Query = new Query();
// Parse the separated form of a query; this ensures that a SELECT query form is constructed.
// 'select' is required:
method.Query.Select = getString(joQuery.Property("select")).Interpolate(tokenLookup);
if (String.IsNullOrEmpty(method.Query.Select))
{
method.Errors.Add("A `select` clause is required");
}
// The rest are optional:
var jpFrom = joQuery.Property("from");
if (jpFrom != null && jpFrom.Value.Type == JTokenType.Array)
{
// If the "from" property is an array, treat it as a subquery with optional joins:
// [
// { subquery } | "object1Name", "alias1Name"
// (optional):
// ,"join", { subquery } | "object2Name", "alias2Name", "join condition"
// ,"left join", { subquery } | "object3Name", "alias3Name", "join condition"
// ,"outer join", { subquery } | "object4Name", "alias4Name", "join condition"
// ...
// ]
method.Query.From = parseQueryFrom((JArray)jpFrom.Value, method.Errors, String.Empty).Interpolate(tokenLookup);
}
else if (jpFrom != null && jpFrom.Value.Type == JTokenType.String)
{
// Otherwise, assume it's a string:
method.Query.From = getString(jpFrom).Interpolate(tokenLookup);
}
method.Query.Where = getString(joQuery.Property("where")).Interpolate(tokenLookup);
method.Query.GroupBy = getString(joQuery.Property("groupBy")).Interpolate(tokenLookup);
method.Query.Having = getString(joQuery.Property("having")).Interpolate(tokenLookup);
method.Query.OrderBy = getString(joQuery.Property("orderBy")).Interpolate(tokenLookup);
method.Query.WithCTEidentifier = getString(joQuery.Property("withCTEidentifier")).Interpolate(tokenLookup);
method.Query.WithCTEexpression = getString(joQuery.Property("withCTEexpression")).Interpolate(tokenLookup);
// Parse "xmlns" dictionary of "prefix": "http://uri.example.org/namespace" properties for WITH XMLNAMESPACES:
var xmlNamespaces = new Dictionary<string, string>(StringComparer.Ordinal);
var jpXmlns = joQuery.Property("xmlns");
if (jpXmlns != null)
{
var joXmlns = (JObject)jpXmlns.Value;
foreach (var jpNs in joXmlns.Properties())
{
var prefix = jpNs.Name;
var ns = getString(jpNs).Interpolate(tokenLookup);
xmlNamespaces.Add(prefix, ns);
}
if (xmlNamespaces.Count > 0) method.Query.XMLNamespaces = xmlNamespaces;
}
string withCTEidentifier, withCTEexpression, select, from;
string where, groupBy, having, orderBy;
try
{
// Strip out all SQL comments:
withCTEidentifier = stripSQLComments(method.Query.WithCTEidentifier);
withCTEexpression = stripSQLComments(method.Query.WithCTEexpression);
select = stripSQLComments(method.Query.Select);
from = stripSQLComments(method.Query.From);
where = stripSQLComments(method.Query.Where);
groupBy = stripSQLComments(method.Query.GroupBy);
having = stripSQLComments(method.Query.Having);
orderBy = stripSQLComments(method.Query.OrderBy);
}
catch (Exception ex)
{
method.Errors.Add(ex.Message);
return;
}
// Allocate a StringBuilder with enough space to construct the query:
StringBuilder qb = new StringBuilder(
(withCTEidentifier ?? String.Empty).Length + (withCTEexpression ?? String.Empty).Length + ";WITH AS ()\r\n".Length
+ (select ?? String.Empty).Length + "SELECT ".Length
+ (from ?? String.Empty).Length + "\r\nFROM ".Length
+ (where ?? String.Empty).Length + "\r\nWHERE ".Length
+ (groupBy ?? String.Empty).Length + "\r\nGROUP BY ".Length
+ (having ?? String.Empty).Length + "\r\nHAVING ".Length
+ (orderBy ?? String.Empty).Length + "\r\nORDER BY ".Length
);
try
{
// This is a very conservative approach and will lead to false-positives for things like EXISTS() and sub-queries:
if (containsSQLkeywords(select, "from", "into", "where", "group", "having", "order", "for"))
method.Errors.Add("SELECT clause cannot contain FROM, INTO, WHERE, GROUP BY, HAVING, ORDER BY, or FOR");
if (containsSQLkeywords(from, "where", "group", "having", "order", "for"))
method.Errors.Add("FROM clause cannot contain WHERE, GROUP BY, HAVING, ORDER BY, or FOR");
if (containsSQLkeywords(where, "group", "having", "order", "for"))
method.Errors.Add("WHERE clause cannot contain GROUP BY, HAVING, ORDER BY, or FOR");
if (containsSQLkeywords(groupBy, "having", "order", "for"))
method.Errors.Add("GROUP BY clause cannot contain HAVING, ORDER BY, or FOR");
if (containsSQLkeywords(having, "order", "for"))
method.Errors.Add("HAVING clause cannot contain ORDER BY or FOR");
if (containsSQLkeywords(orderBy, "for"))
method.Errors.Add("ORDER BY clause cannot contain FOR");
}
catch (Exception ex)
{
method.Errors.Add(ex.Message);
}
if (method.Errors.Count != 0)
return;
// Construct the query:
bool didSemi = false;
if (xmlNamespaces.Count > 0)
{
didSemi = true;
qb.AppendLine(";WITH XMLNAMESPACES (");
using (var en = xmlNamespaces.GetEnumerator())
for (int i = 0; en.MoveNext(); ++i)
{
var xmlns = en.Current;
qb.AppendFormat(" '{0}' AS {1}", xmlns.Value.Replace("\'", "\'\'"), xmlns.Key);
if (i < xmlNamespaces.Count - 1) qb.Append(",\r\n");
else qb.Append("\r\n");
}
qb.Append(")\r\n");
}
if (!String.IsNullOrEmpty(withCTEidentifier) && !String.IsNullOrEmpty(withCTEexpression))
{
if (!didSemi) qb.Append(';');
qb.AppendFormat("WITH {0} AS (\r\n{1}\r\n)\r\n", withCTEidentifier, withCTEexpression);
}
qb.AppendFormat("SELECT {0}", select);
if (!String.IsNullOrEmpty(from)) qb.AppendFormat("\r\nFROM {0}", from);
if (!String.IsNullOrEmpty(where)) qb.AppendFormat("\r\nWHERE {0}", where);
if (!String.IsNullOrEmpty(groupBy)) qb.AppendFormat("\r\nGROUP BY {0}", groupBy);
if (!String.IsNullOrEmpty(having)) qb.AppendFormat("\r\nHAVING {0}", having);
if (!String.IsNullOrEmpty(orderBy)) qb.AppendFormat("\r\nORDER BY {0}", orderBy);
// Assign the constructed query:
method.Query.SQL = qb.ToString();
}
IHttpResponseAction errorsMethod(SHA1Hashed<ServicesOffering> main, Service service, Method method)
{
return new JsonRootResponse(
links: new RestfulLink[]
{
},
meta: new
{
configHash = main.HashHexString,
serviceName = service.Name,
methodName = method.Name,
methodErrors = method.Errors
}
);
}
void parseMethods(JProperty jpMethods, Service svc, string connectionString, IDictionary<string, ParameterType> parameterTypes, IDictionary<string, Method> methods, Func<string, string> tokenLookup)
{
if (jpMethods.Value.Type != JTokenType.Object)
{
svc.Errors.Add("The `methods` property is expected to be of type object");
return;
}
var joMethods = (JObject)jpMethods.Value;
// Parse each method:
foreach (var jpMethod in joMethods.Properties())
{
// Is the method set to null?
if (jpMethod.Value.Type == JTokenType.Null)
{
// Remove it:
methods.Remove(jpMethod.Name);
continue;
}
if (jpMethod.Value.Type != JTokenType.Object)
{
svc.Errors.Add("The method property `{0}` is expected to be of type object".F(jpMethod.Name));
continue;
}
var joMethod = ((JObject)jpMethod.Value);
// Create a clone of the inherited descriptor or a new descriptor:
Method method;
if (methods.TryGetValue(jpMethod.Name, out method))
method = method.Clone();
else
{
method = new Method()
{
Name = jpMethod.Name,
ConnectionString = connectionString,
Errors = new List<string>(5)
};
}
methods[jpMethod.Name] = method;
method.Service = svc;
Debug.Assert(method.Errors != null);
// Parse the definition:
method.Description = getString(joMethod.Property("description")).Interpolate(tokenLookup);
method.DeprecatedMessage = getString(joMethod.Property("deprecated")).Interpolate(tokenLookup);
// Parse connection:
var jpConnection = joMethod.Property("connection");
if (jpConnection != null)
{
method.ConnectionString = parseConnection(jpConnection, method.Errors, (s) => s.Interpolate(tokenLookup));
}
// Parse the parameters:
var jpParameters = joMethod.Property("parameters");
if (jpParameters != null)
{
parseParameters(jpParameters, method, parameterTypes, tokenLookup);
}
// Parse query:
var jpQuery = joMethod.Property("query");
if (jpQuery != null)
{
parseQuery(jpQuery, method, tokenLookup);
}
if (method.Query == null)
{
method.Errors.Add("No query specified");
}
// Parse result mapping:
var jpMapping = joMethod.Property("result");
if (jpMapping != null)
{
var joMapping = (JObject)jpMapping.Value;
method.Mapping = parseMapping(joMapping, method.Errors);
}
} // foreach (var method)
}
async Task<IHttpResponseAction> dataMethod(SHA1Hashed<ServicesOffering> main, Method method, System.Collections.Specialized.NameValueCollection queryString)
{
// Check for descriptor errors:
if (method.Errors.Count > 0)
{
return new JsonRootResponse(
statusCode: 500,
statusDescription: "Bad method descriptor",
message: "Bad method descriptor",
meta: new
{
configHash = main.HashHexString,
serviceName = method.Service.Name,
methodName = method.Name,
errors = method.Errors.ToArray()
}
);
}
// Check required parameters:
if (method.Parameters != null)
{
// Create a hash set of the query-string parameter names:
var q = new HashSet<string>(queryString.AllKeys, StringComparer.OrdinalIgnoreCase);
// Create a list of missing required parameter names:
var missingParams = new List<string>(method.Parameters.Count(p => !p.Value.IsOptional));
missingParams.AddRange(
from p in method.Parameters
where !p.Value.IsOptional && !q.Contains(p.Key)
select p.Key
);
if (missingParams.Count > 0)
return new JsonRootResponse(
statusCode: 400,
statusDescription: "Missing required parameters",
message: "Missing required parameters",
meta: new
{
configHash = main.HashHexString,
serviceName = method.Service.Name,
methodName = method.Name
},
errors: new[]
{
new
{
missingParams = missingParams.ToDictionary(
p => p,
p => new ParameterSerialized(method.Parameters[p]),
StringComparer.OrdinalIgnoreCase
)
}
}
);
missingParams = null;
}
// Open a connection and execute the command:
using (var conn = new SqlConnection(method.ConnectionString))
using (var cmd = conn.CreateCommand())
{
var parameterValues = new Dictionary<string, ParameterValue>(method.Parameters == null ? 0 : method.Parameters.Count);
// Add parameters:
if (method.Parameters != null)
{
foreach (var param in method.Parameters)
{
bool isValid = true;
string message = null;
object sqlValue, clrValue;
var paramType = (param.Value.SqlType ?? param.Value.Type);
string rawValue = queryString[param.Key];
if (param.Value.IsOptional & (rawValue == null))
{
// Use the default value if the parameter is optional and is not specified on the query-string:
sqlValue = param.Value.DefaultSQLValue;
clrValue = param.Value.DefaultCLRValue;
}
else
{
try
{
sqlValue = getSqlValue(paramType.SqlDbType, rawValue);
if (sqlValue == null)
{
isValid = false;
message = "Unsupported SQL type '{0}'".F(paramType.SqlDbType);
}
}
catch (Exception ex)
{
isValid = false;
sqlValue = DBNull.Value;
message = ex.Message;
}
try
{
clrValue = getCLRValue(paramType.SqlDbType, rawValue);
}
catch { clrValue = null; }
}
parameterValues.Add(param.Key, isValid ? new ParameterValue(clrValue) : new ParameterValue(message, rawValue));
// Add the SQL parameter:
var sqlprm = cmd.Parameters.Add(param.Value.Name, paramType.SqlDbType);
sqlprm.IsNullable = param.Value.IsOptional;
if (paramType.Length != null) sqlprm.Precision = (byte)paramType.Length.Value;
if (paramType.Scale != null) sqlprm.Scale = (byte)paramType.Scale.Value;
sqlprm.SqlValue = sqlValue;
}
}
// Abort if we have invalid parameters:
var invalidParameters = parameterValues.Where(p => !p.Value.isValid);
if (invalidParameters.Any())
{
return new JsonRootResponse(
statusCode: 400,
statusDescription: "Invalid parameter value(s)",
message: "Invalid parameter value(s)",
meta: new
{
configHash = main.HashHexString,
serviceName = method.Service.Name,
methodName = method.Name
},
errors: invalidParameters.Select(p => (object)new { name = p.Key, attemptedValue = p.Value.attemptedValue, message = p.Value.message }).ToArray()
);
}
//cmd.CommandTimeout = 360; // seconds
cmd.CommandType = CommandType.Text;
// Set TRANSACTION ISOLATION LEVEL and optionally ROWCOUNT before the query:
const string setIsoLevel = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;\r\n";
var sbCmd = new StringBuilder(setIsoLevel, setIsoLevel.Length + method.Query.SQL.Length);
//if (rowLimit > 0)
// sbCmd.Append("SET ROWCOUNT {0};\r\n".F(rowLimit));
sbCmd.Append(method.Query.SQL);
cmd.CommandText = sbCmd.ToString();
// Stopwatches used for precise timing:
Stopwatch swOpenTime, swExecTime, swReadTime;
swOpenTime = Stopwatch.StartNew();
try
{
// Open the connection asynchronously:
await conn.OpenAsync();
swOpenTime.Stop();
}
catch (Exception ex)
{
swOpenTime.Stop();
return getErrorResponse(ex);
}
// Execute the query:
SqlDataReader dr;
swExecTime = Stopwatch.StartNew();
try
{
// Execute the query asynchronously:
dr = await cmd.ExecuteReaderAsync(CommandBehavior.SequentialAccess | CommandBehavior.CloseConnection);
swExecTime.Stop();
}
catch (ArgumentException aex)
{
swExecTime.Stop();
// SQL Parameter validation only gives `null` for `aex.ParamName`.
return new JsonRootResponse(400, aex.Message);
}
catch (Exception ex)
{
swExecTime.Stop();
return getErrorResponse(ex);
}
swReadTime = Stopwatch.StartNew();
try
{
var results = await ReadResult(method, dr, RowMapperUseMapping);
swReadTime.Stop();
var meta = new MetadataSerialized
{
configHash = main.HashHexString,
serviceName = method.Service.Name,
methodName = method.Name,
deprecated = method.DeprecatedMessage,
parameters = parameterValues,
// Timings are in msec:
timings = new MetadataTimingsSerialized
{
open = Math.Round(swOpenTime.ElapsedTicks * 1000m / (decimal)Stopwatch.Frequency, 2),
exec = Math.Round(swExecTime.ElapsedTicks * 1000m / (decimal)Stopwatch.Frequency, 2),
read = Math.Round(swReadTime.ElapsedTicks * 1000m / (decimal)Stopwatch.Frequency, 2),
total = Math.Round((swOpenTime.ElapsedTicks + swExecTime.ElapsedTicks + swReadTime.ElapsedTicks) * 1000m / (decimal)Stopwatch.Frequency, 2),
}
};
return new JsonRootResponse(
links: new RestfulLink[]
{
},
meta: meta,
results: results
);
}
catch (JsonResultException jex)
{
swReadTime.Stop();
return new JsonRootResponse(statusCode: jex.StatusCode, message: jex.Message);
}
catch (Exception ex)
{
swReadTime.Stop();
return getErrorResponse(ex);
}
}
}
IHttpResponseAction debugMethod(SHA1Hashed<ServicesOffering> main, Service service, Method method)
{
return new JsonRootResponse(
links: new RestfulLink[]
{
RestfulLink.Create("self", "/debug/{0}/{1}".F(service.Name, method.Name), "self"),
RestfulLink.Create("parent", "/debug/{0}".F(service.Name), "parent"),
RestfulLink.Create("meta", "/meta/{0}/{1}".F(service.Name, method.Name)),
RestfulLink.Create("errors", "/errors/{0}/{1}".F(service.Name, method.Name)),
RestfulLink.Create("data", "/data/{0}/{1}".F(service.Name, method.Name))
},
meta: new
{
configHash = main.HashHexString,
method = new MethodDebug(method)
}
);
}
/// <summary>
/// Reads the entire SqlDataReader asynchronously and returns the entire list of row objects when complete.
/// </summary>
/// <param name="method"></param>
/// <param name="dr"></param>
/// <param name="rowMapper"></param>
/// <returns></returns>
async Task<List<Dictionary<string, object>>> ReadResult(Method method, SqlDataReader dr, RowMapperDelegate rowMapper)
{
int fieldCount = dr.FieldCount;
var names = new string[fieldCount];
var columns = new object[fieldCount];
for (int i = 0; i < fieldCount; ++i)
{
names[i] = dr.GetName(i);
}
var nameLookup = (
from i in Enumerable.Range(0, fieldCount)
select new { i, name = dr.GetName(i) }
).ToLookup(p => p.name, p => p.i);
var list = new List<Dictionary<string, object>>();
// Enumerate rows asynchronously:
while (await dr.ReadAsync())
{
// Enumerate columns asynchronously:
for (int i = 0; i < fieldCount; ++i)
{
columns[i] = await dr.GetFieldValueAsync<object>(i);
}
// Map all the columns to a single object:
var result = rowMapper(method, names, nameLookup, columns);
list.Add(result);
}
return list;
}
/// <summary>
/// Maps columns from the result set using the mapping schema defined in the method descriptor.
/// </summary>
/// <param name="names"></param>
/// <param name="ordinals"></param>
/// <param name="method"></param>
/// <param name="values"></param>
/// <returns></returns>
Dictionary<string, object> RowMapperUseMapping(Method method, string[] names, ILookup<string, int> ordinals, object[] values)
{
// No custom mapping?
if (method.Mapping == null)
{
var result = new Dictionary<string, object>();
// Use a default mapping:
for (int i = 0; i < names.Length; ++i)
{
if (result.ContainsKey(names[i]))
{
// TODO: add a warning about duplicate column names.
continue;
}
result.Add(names[i], values[i]);
}
return result;
}
// We have a custom mapping:
return mapColumns(method.Mapping, ordinals, values);
}
/// <summary>
/// Parses column names for '{' and '}' which are used to indicate nested object mapping.
/// </summary>
/// <param name="names"></param>
/// <param name="ordinals"></param>
/// <param name="method"></param>
/// <param name="values"></param>
/// <returns></returns>
Dictionary<string, object> RowMapperCurlyInflate(Method method, string[] names, ILookup<string, int> ordinals, object[] values)
{
var objStack = new Stack<Dictionary<string, object>>(3);
var result = new Dictionary<string, object>();
var addTo = result;
// Enumerate columns asynchronously:
for (int i = 0; i < values.Length; ++i)
{
object col = values[i];
string name = names[i];
// Opening or closing a sub-object?
if (name.StartsWith("{") || name.StartsWith("}"))
{
int n = 0;
while (n < name.Length)
{
// Allow any number of leading close-curlies:
if (name[n] == '}')
{
addTo = objStack.Pop();
++n;
continue;
}
// Only one open-curly allowed at the end:
if (name[n] == '{')
{
var curr = addTo;
objStack.Push(addTo);
if (curr == null) break;
string objname = name.Substring(n + 1);
if (col == DBNull.Value)
addTo = null;
else
addTo = new Dictionary<string, object>();
if (curr.ContainsKey(objname))
throw new JsonResultException(500, "{0} key specified more than once".F(name));
curr.Add(objname, addTo);
}
break;
}
continue;
}
if (addTo == null) continue;
addTo.Add(name, col);
}
if (objStack.Count != 0)
throw new JsonResultException(500, "Too many open curlies in column list: {0}".F(objStack.Count));
return result;
}
internal MethodMetadata(Method desc)
{
this.desc = desc;
}
internal MethodDebug(Method desc)
{
this.desc = desc;
}
