public override QvxDataTable ExtractQuery(string query, List<QvxTable> qvxTables)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Notice, "Extracting Query");
//Debugger.Launch();
//NOTE: Where clause not yet supported
String fields = "";
String where = "";
Int16 limit;
query = query.Replace("\r\n", " ").Replace("\n", " ");
try
{
Match match;
match = Regex.Match(query, @"(?:select\s(?<fields>[^\/\r\n]*))\s(?:from\s(?<table>[^\/\r\n\s]+))\s*(?:where\s(?<where>[^\r\n\s]*))?(?:\s*)(?:limit\s(?<limit>[^\/\r\n\s]*))?(?:\s*)(?<cache>cache)?", RegexOptions.IgnoreCase);
if (!match.Success)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Error, string.Format("ExtractQueryAndTransmitTableHeader() - QvxPleaseSendReplyException({0}, \"Invalid query: {1}\")", QvxResult.QVX_SYNTAX_ERROR, query));
}
//Establish Table Name
fields = match.Groups["fields"].Value;
fields = fields.Trim();
if(match.Groups["where"]!=null){
where = match.Groups["where"].Value;
where = where.Trim();
}
if (match.Groups["limit"] != null)
{
try
{
limit = Int16.Parse(match.Groups["limit"].Value);
helper.pageInfo.LoadLimit = limit;
}
catch (Exception ex)
{
}
}
liveTable = match.Groups["table"].Value;
if (match.Groups["cache"].Value.ToLower() == "cache")
{
helper.addTableToCacheList(liveTable);
}
}
catch (Exception ex)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Error, ex.Message);
}
if (!String.IsNullOrEmpty(liveTable) && helper!=null)
{
helper.SetActiveTable(liveTable, where);
//Create QvxTable based on fields in Select statement
MTables = new List<QvxTable>();
QvxTable qT = new QvxTable { TableName = liveTable, Fields = helper.createFieldList(liveTable, fields), GetRows = GetData };
MTables.Add(qT);
return new QvxDataTable(qT);
}
return null;
//return table
}
private QvxDataRow MakeEntry(EventLogEntry evl, QvxTable table)
{
var row = new QvxDataRow();
row[table.Fields[0]] = evl.Category;
row[table.Fields[1]] = evl.EntryType.ToString();
row[table.Fields[2]] = evl.Message;
row[table.Fields[3]] = evl.CategoryNumber.ToString();
row[table.Fields[4]] = evl.Index.ToString();
row[table.Fields[5]] = evl.MachineName;
row[table.Fields[6]] = evl.Source;
row[table.Fields[7]] = evl.TimeGenerated.ToString();
return row;
}
/// <summary>
/// Create the QvxTable.
/// </summary>
private void CreateTable()
{
_table = new QvxTable();
_table.TableName = _tableName;
_table.GetRows = GetRows;
var statusesFields = new List<QvxField>();
foreach (var field in Enum.GetNames(typeof(EntertainmentFields)))
{
statusesFields.Add(new QvxField(field, QvxFieldType.QVX_TEXT, QvxNullRepresentation.QVX_NULL_FLAG_SUPPRESS_DATA, FieldAttrType.ASCII));
}
_table.Fields = statusesFields.ToArray();
}
public override QvxDataTable ExtractQuery(string query, List <QvxTable> qvxTables)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Notice, "Extracting Query");
//Debugger.Launch();
//NOTE: Where clause not yet supported
String fields = "";
String where = "";
Int16 limit;
query = query.Replace("\r\n", " ").Replace("\n", " ");
try
{
Match match;
match = Regex.Match(query, @"(?:select\s(?<fields>[^\/\r\n]*))\s(?:from\s(?<table>[^\/\r\n\s]+))\s*(?:where\s(?<where>[^\r\n\s]*))?(?:\s*)(?:limit\s(?<limit>[^\/\r\n\s]*))?(?:\s*)(?<cache>cache)?", RegexOptions.IgnoreCase);
if (!match.Success)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Error, string.Format("ExtractQueryAndTransmitTableHeader() - QvxPleaseSendReplyException({0}, \"Invalid query: {1}\")", QvxResult.QVX_SYNTAX_ERROR, query));
}
//Establish Table Name
fields = match.Groups["fields"].Value;
fields = fields.Trim();
if (match.Groups["where"] != null)
{
where = match.Groups["where"].Value;
where = where.Trim();
}
if (match.Groups["limit"] != null)
{
try
{
limit = Int16.Parse(match.Groups["limit"].Value);
helper.pageInfo.LoadLimit = limit;
}
catch (Exception ex)
{
}
}
liveTable = match.Groups["table"].Value;
if (match.Groups["cache"].Value.ToLower() == "cache")
{
helper.addTableToCacheList(liveTable);
}
}
catch (Exception ex)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Error, ex.Message);
}
if (!String.IsNullOrEmpty(liveTable) && helper != null)
{
helper.SetActiveTable(liveTable, where);
//Create QvxTable based on fields in Select statement
MTables = new List <QvxTable>();
QvxTable qT = new QvxTable {
TableName = liveTable, Fields = helper.createFieldList(liveTable, fields), GetRows = GetData
};
MTables.Add(qT);
return(new QvxDataTable(qT));
}
return(null);
//return table
}
private IEnumerable <QvxDataRow> GetData()
{
//Debugger.Launch();
dynamic data;
recordsLoaded = 0;
//Get a reference to the QvxTable from MTables
QvxTable qTable = FindTable(liveTable, MTables);
helper.Prep();
bool isFromCache = helper.cacheEndpointMap.ContainsKey(helper.ActiveTable.endpoint.ToString());
while (helper.IsMore)
{
if (isFromCache)
{
String cachedTable = helper.cacheEndpointMap[helper.ActiveTable.endpoint.ToString()];
if (cachedTable != liveTable)
{
data = helper.getCachedData(cachedTable, helper.pageInfo.CurrentPage);
}
else
{
data = helper.GetJSON();
}
}
else
{
data = helper.GetJSON();
}
if (data == null)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Notice, "End of data (" + liveTable + ")");
break;
}
if (data.GetType().Name == "JArray")
{
if (((Newtonsoft.Json.Linq.JArray)(data)).Count == 0)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Notice, "End of data (" + liveTable + ")");
break;
}
}
if (helper.tableCacheList.IndexOf(liveTable) != -1)
{
if (!helper.cacheEndpointMap.ContainsKey(helper.ActiveTable.endpoint.ToString()))
{
helper.cacheEndpointMap.Add(helper.ActiveTable.endpoint.ToString(), liveTable);
}
helper.cacheTable(liveTable, helper.pageInfo.CurrentPage, data);
}
//if we have a child link configured
if (helper.ActiveTable.has_link_to_child != null && helper.ActiveTable.has_link_to_child == true)
{
dynamic childData = data;
if (helper.ActiveTable.data_element_override == null || helper.ActiveTable.data_element_override.ToString() == "")
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Error, "Data Element Override has not been set for Table - " + helper.ActiveTable.qName);
}
else
{
String backupUrl = helper.ActiveUrl;
String childDataElementParam = helper.ActiveTable.data_element_override.ToString();
List <String> childDataElement = childDataElementParam.Split(new String[] { "." }, StringSplitOptions.RemoveEmptyEntries).ToList();
String childDataUrlElement = childDataElement.Last();
childDataElement.Remove(childDataElement.Last());
foreach (String elem in childDataElement)
{
childData = childData[elem];
}
foreach (dynamic child in childData)
{
helper.ActiveUrl = ((dynamic)child)[childDataUrlElement];
childData = helper.GetJSON();
if (helper.ActiveTable.child_data_element != null && helper.ActiveTable.child_data_element.ToString() != "")
{
String childDataElemParam = helper.ActiveTable.child_data_element.ToString();
List <String> childDataElem = childDataElemParam.Split(new String[] { "." }, StringSplitOptions.RemoveEmptyEntries).ToList();
foreach (String elem in childDataElem)
{
childData = childData[elem];
}
}
Boolean dataIsArray;
try
{
dynamic check = ((Newtonsoft.Json.Linq.JArray)(childData)).Type; //if this works then the data is an array
dataIsArray = true;
}
catch (Exception ex)
{
dataIsArray = false;
}
if (dataIsArray)
{
if (((Newtonsoft.Json.Linq.JArray)(childData)).Count == 0)
{
helper.IsMore = false;
break;
}
foreach (dynamic row in childData)
{
if (recordsLoaded < helper.pageInfo.LoadLimit)
{
yield return(InsertRow(row, qTable, child));
recordsLoaded++;
}
else
{
helper.IsMore = false;
break;
}
}
}
else
{
if (recordsLoaded < helper.pageInfo.LoadLimit)
{
yield return(InsertRow(childData, qTable, child));
}
else
{
helper.IsMore = false;
break;
}
}
}
helper.ActiveUrl = backupUrl;
}
}
else
{
if (!String.IsNullOrEmpty(helper.DataElement))
{
//Debugger.Launch();
List <String> dataElemPath = helper.DataElement.Split(new String[] { "." }, StringSplitOptions.RemoveEmptyEntries).ToList();
foreach (String elem in dataElemPath)
{
data = data[elem];
}
}
if (helper.ActiveTable.child_data_element != null && helper.ActiveTable.child_data_element.ToString() != "")
{
String childDataElemParam = helper.ActiveTable.child_data_element.ToString();
if (((Newtonsoft.Json.Linq.JArray)(data)).Count == 0)
{
helper.IsMore = false;
break;
}
foreach (dynamic row in data)
{
dynamic childData = row;
List <String> childDataElem = childDataElemParam.Split(new String[] { "." }, StringSplitOptions.RemoveEmptyEntries).ToList();
foreach (String elem in childDataElem)
{
if (elem == "*")
{
try
{
childData = ((Newtonsoft.Json.Linq.JProperty)(childData)).Value;
}
catch (Exception ex)
{
}
}
else
{
childData = childData[elem];
}
}
if (childData != null)
{
foreach (dynamic childRow in childData)
{
yield return(InsertRow(childRow, qTable, row));
}
}
if (recordsLoaded >= helper.pageInfo.LoadLimit)
{
helper.IsMore = false;
break;
}
recordsLoaded++;
}
}
else
{
//helper.pageInfo.CurrentPageSize = Convert.ToInt32(data.Count);
//helper.pageInfo.CurrentPage++;
if (data.GetType().Name == "JArray")
{
helper.pageInfo.CurrentPageSize = ((Newtonsoft.Json.Linq.JArray)(data)).Count;
if (((Newtonsoft.Json.Linq.JArray)(data)).Count == 0)
{
helper.IsMore = false;
break;
}
foreach (dynamic row in data)
{
if (recordsLoaded < helper.pageInfo.LoadLimit)
{
yield return(InsertRow(row, qTable, null));
recordsLoaded++;
}
else
{
helper.IsMore = false;
break;
}
}
}
else
{
helper.pageInfo.CurrentPageSize = Convert.ToInt32(data.Count);
if (recordsLoaded < helper.pageInfo.LoadLimit)
{
yield return(InsertRow(data, qTable, null));
recordsLoaded++;
}
else
{
helper.IsMore = false;
break;
}
}
}
}
helper.pageInfo.CurrentRecord = recordsLoaded;
if (isFromCache)
{
helper.pageInfo.CurrentPage++;
}
else
{
helper.Page();
}
}
}
/// <summary>
/// This method obtains the data from the GAMS file using the low level API
/// </summary>
/// <param name="_source"></param>
/// <param name="file"></param>
/// <param name="table"></param>
/// <param name="where"></param>
/// <returns></returns>
public static IEnumerable <QvxDataRow> GetGamsDataLowLevel(string _source, string file, QvxTable table, List <string> selectedFields, List <QueryExtractor.WhereCondition> where)
{
const int GMS_VAL_LEVEL = 0;
const int GMS_VAL_MARGINAL = 1;
const int GMS_VAL_LOWER = 2;
const int GMS_VAL_UPPER = 3;
const int GMS_VAL_SCALE = 4;
//const int GMS_VAL_MAX = 5;
string msg = string.Empty, VarName = string.Empty, sText = string.Empty, UelName = string.Empty;
int ErrNr = 0, SymNr = 0, SymTyp = 0, ADim = 0, ACount = 0, AUser = 0, NRec = 0, FDim = 0, j = 0, i = 0, IDum = 0;
using (gdxcs gdx = new gdxcs(ref msg))
{
gdx.gdxOpenRead(_source + "\\" + file, ref ErrNr);
gdx.gdxFindSymbol(table.TableName, ref SymNr);
gdx.gdxSymbolInfo(SymNr, ref VarName, ref ADim, ref SymTyp);
gdx.gdxSymbolInfoX(SymNr, ref ACount, ref AUser, ref sText);
gdx.gdxDataReadRawStart(SymNr, ref NRec);
Dictionary <int, string>[] dimensionCache = new Dictionary <int, string> [ADim];
if (where.Count == 0)
{
// No WHERE clause
double[] Vals = new double[gamsglobals.val_max];
int[] Keys = new int[gamsglobals.maxdim];
while (gdx.gdxDataReadRaw(ref Keys, ref Vals, ref FDim) != 0)
{
QvxDataRow row = mapRow(Keys, Vals);
if (row != null)
{
yield return(row);
}
}
}
else
{
// WHERE clause
string[] strFilter = Enumerable.Repeat("", table.Fields.Count()).ToArray();
var emptyFilters = new List <QvxField>();
foreach (var whereCondition in where)
{
int m = Array.FindIndex(table.Fields, element => element.FieldName == whereCondition.Field);
if (m >= 0)
{
if (m > (ADim - 1))
{
// Only dimensions can be filtered
throw new QvxPleaseSendReplyException(QvxResult.QVX_UNSUPPORTED_COMMAND, String.Format("Field \"{0}\" is not a dimension and can't be filtered", whereCondition.Field));
}
else if ("".Equals(whereCondition.Value))
{
// GAMS API doesn't allow empty filters, so we have to filter them ourselves
emptyFilters.Add(table.Fields[m]);
}
else
{
strFilter[m] = whereCondition.Value;
}
}
else
{
throw new QvxPleaseSendReplyException(QvxResult.QVX_FIELD_NOT_FOUND, String.Format("The field \"{0}\" is not valid", whereCondition.Field));
}
}
using (BlockingCollection <QvxDataRow> buffer = new BlockingCollection <QvxDataRow>())
{
// Start reading thread
Exception exception = null;
var readTask = Task.Run(() =>
{
try
{
gdx.gdxDataReadRawFastFilt(SymNr, strFilter, FilterProc);
}
catch (Exception e)
{
exception = e;
}
finally
{
// Signal the end of the data
buffer.CompleteAdding();
}
});
int FilterProc(IntPtr IndxPtr, IntPtr ValsPtr, IntPtr Uptr)
{
double[] managedArrayVals = new double[gamsglobals.val_max];
Marshal.Copy(ValsPtr, managedArrayVals, 0, gamsglobals.val_max);
int[] managedArrayIndx = new int[gamsglobals.maxdim];
Marshal.Copy(IndxPtr, managedArrayIndx, 0, gamsglobals.maxdim);
QvxDataRow row = mapRow(managedArrayIndx, managedArrayVals, emptyFilters);
if (row != null)
{
buffer.Add(row);
}
return(1);
}
// Writing process
foreach (QvxDataRow row in buffer.GetConsumingEnumerable())
{
yield return(row);
}
if (exception != null)
{
throw exception;
}
}
}
QvxDataRow mapRow(int[] Keys, double[] Vals, List <QvxField> emptyFilters = null)
{
i = 0;
var row = new QvxDataRow();
// Read the dimensions
for (j = 0; j < ADim; j++)
{
if (dimensionCache[j] == null)
{
dimensionCache[j] = new Dictionary <int, string>();
}
UelName = null;
if (dimensionCache[j].ContainsKey(Keys[j]))
{
UelName = dimensionCache[j][Keys[j]];
}
else
{
gdx.gdxUMUelGet(Keys[j], ref UelName, ref IDum);
dimensionCache[j][Keys[j]] = UelName;
}
if (UelName != null)
{
QvxField field = table.Fields[i++];
if (selectedFields.Contains(field.FieldName))
{
row[field] = UelName;
}
// we check the empty filters, as GAMS API doesn't do it
if (emptyFilters != null && emptyFilters.Contains(field) && !string.IsNullOrEmpty(UelName))
{
return(null);
}
}
}
switch (SymTyp)
{
// SET
case gamsglobals.dt_set:
if (gdx.gdxGetElemText((int)Vals[GMS_VAL_LEVEL], ref msg, ref IDum) != 0)
{
QvxField field2 = table.Fields[i++];
if (selectedFields.Contains(field2.FieldName))
{
row[field2] = msg;
}
}
else
{
QvxField field2 = table.Fields[i++];
if (selectedFields.Contains(field2.FieldName))
{
row[field2] = Y_STRING;
}
}
break;
// PARAMETER
case gamsglobals.dt_par:
// Value
readValueField(row, table.Fields[i++], table.Fields[i++], Vals[GMS_VAL_LEVEL]);
if (!string.IsNullOrEmpty(sText) && ADim == 0)
{
QvxField field = table.Fields[i++];
if (selectedFields.Contains(field.FieldName))
{
row[field] = sText;
}
}
break;
// EQUATION and VARIABLE
case gamsglobals.dt_equ:
case gamsglobals.dt_var:
int[] gms_values = { GMS_VAL_LEVEL, GMS_VAL_MARGINAL, GMS_VAL_LOWER, GMS_VAL_UPPER, GMS_VAL_SCALE };
foreach (int gms_value in gms_values)
{
// Value
readValueField(row, table.Fields[i++], table.Fields[i++], Vals[gms_value]);
}
break;
}
return(row);
}
/// <summary>This method reads a value separated in two fields, the first with the numeric value and the second with the special value description.</summary>
void readValueField(QvxDataRow pRow, QvxField numberField, QvxField specialField, double pVal)
{
Boolean isSpecialValue = false;
// Value
if (selectedFields.Contains(numberField.FieldName))
{
pRow[numberField] = val2str(pVal, msg, out isSpecialValue, true, false);
}
// Value (Special)
if (selectedFields.Contains(specialField.FieldName))
{
pRow[specialField] = val2str(pVal, msg, out isSpecialValue, false, true);
}
else if (isSpecialValue)
{
// If the value is special, but the "Special value" column is not selected, we throw an error
throw new QvxPleaseSendReplyException(QvxResult.QVX_FIELD_NOT_FOUND, String.Format("The field \"{0}\" contains special values, so the field \"{1}\" has to be selected", numberField.FieldName, specialField.FieldName));
}
}
/// <summary>This method generates the final value of a field, that can be a normal number, an acronym, infinite, ...</summary>
/// <param name="returnNumber">If true, the value of a number will be returned as a double</param>
/// <param name="returnSpecial">If true, the value of a special value (infinite, epsilon, acronym, ...) will be returned as a string</param>
dynamic val2str(double val, string s, out Boolean isSpecial, Boolean returnNumber = true, Boolean returnSpecial = true)
{
string[] gmsSVText = { "UNdef", "NA", "+Inf", "-Inf", "Eps", "0", "AcroN" };
int sv = 0;
if (gdx.gdxAcronymName(val, ref s) != 0)
{
isSpecial = true;
return(returnSpecial ? s : null);
}
else
{
gdx.gdxMapValue(val, ref sv);
if (gamsglobals.sv_normal != sv)
{
isSpecial = true;
return(returnSpecial ? gmsSVText[sv] : null);
}
else
{
isSpecial = false;
if (returnNumber)
{
return(val);
}
else
{
return(null);
}
}
}
}
}
}
/// <summary>
/// Obtains the preview data using the high level API
/// </summary>
/// <param name="tableName"></param>
/// <param name="NumberOfRows"></param>
/// <returns></returns>
public string[,] GetPreviewData(QvxTable table, int NumberOfRows = int.MaxValue)
{
string[,] returnTable;
GAMSSymbol symbol = Db.GetSymbol(table.TableName);
switch (symbol)
{
case GAMSSet gset:
GetPreviewColumnsData(out returnTable, gset, NumberOfRows);
break;
case GAMSParameter gparam:
GetPreviewColumnsData(out returnTable, gparam, NumberOfRows);
break;
case GAMSEquation geq:
GetPreviewColumnsData(out returnTable, geq, NumberOfRows);
break;
case GAMSVariable gvar:
GetPreviewColumnsData(out returnTable, gvar, NumberOfRows);
break;
default:
returnTable = new string[0, 0];
break;
}
int FinalNumberOfRows = returnTable.GetLength(0);
int NumberOfColumns = returnTable.GetLength(1);
//Using advanced api for retrieve acronyms if existing
string msg = "";
int ErrNr = 0, SymNr = 0;
using (gdxcs gdx = new gdxcs(ref msg))
{
gdx.gdxOpenRead(GDXFileLocation, ref ErrNr);
gdx.gdxFindSymbol(table.TableName, ref SymNr);
int dimensions = symbol.Dim;
for (int i = 0; i < FinalNumberOfRows; i++)
{
for (int j = dimensions; j < NumberOfColumns; j++)
{
if (double.TryParse(returnTable[i, j], out double doubleForParse))
{
string value = val2str(doubleForParse, msg, out Boolean isSpecial);
if (isSpecial)
{
// Values use two columns, and special values appear in the second one. So we increase the counter
returnTable[i, j++] = "";
}
returnTable[i, j] = value;
}
}
}
string val2str(double val, string s, out Boolean isSpecial)
{
string[] gmsSVText = { "UNdef", "NA", "+Inf", "-Inf", "Eps", "0", "AcroN" };
isSpecial = false;
int sv = 0;
if (gdx.gdxAcronymName(val, ref s) != 0)
{
isSpecial = true;
return(s);
}
else
{
gdx.gdxMapValue(val, ref sv);
if (gamsglobals.sv_normal != sv)
{
isSpecial = true;
return(gmsSVText[sv]);
}
else
{
return(val.ToString("N", new CultureInfo("en-US", false).NumberFormat));
}
}
}
}
return(returnTable);
}
public List <QvxField> getFields(Database database, string owner, QvxTable table, Dictionary <string, string> args)
{
return(table.Fields.ToList());
}
public List <QvxTable> getTables(Database database, Dictionary <string, string> args)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, "+ getTables()");
List <QvxTable> lt = new List <QvxTable>();
if (args["Location"] == "File")
{
if (
args.ContainsKey("Force Refresh") &&
(!Convert.ToBoolean(args["Force Refresh"])) &&
this.connectParams.ContainsKey("File") &&
this.connectParams["File"].ContainsKey(args["File Name"])
)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, "- getTables() (from cache)");
return(this.connectParams["File"][args["File Name"]]);
}
}
else
{
string param = args["Method"] == "POST" ? args["Params"] : "default";
if (
args.ContainsKey("Force Refresh") &&
(!Convert.ToBoolean(args["Force Refresh"])) &&
this.connectParams.ContainsKey(args["Method"]) &&
this.connectParams[args["Method"]].ContainsKey(param)
)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, "- getTables() (from cache)");
return(this.connectParams[args["Method"]][param]);
}
}
foreach (DataTable dt in this.getRawTables(database, args))
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Notice, "Found table " + dt.TableName + ": " + dt.Rows.Count.ToString());
List <QvxField> l = new List <QvxField>();
foreach (DataColumn dc in dt.Columns)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Notice, "Found Column " + dc.ColumnName);
l.Add(new QvxField(dc.ColumnName, QvxFieldType.QVX_TEXT, QvxNullRepresentation.QVX_NULL_FLAG_SUPPRESS_DATA, FieldAttrType.ASCII));
}
QvxTable mt = new QvxTable();
mt.TableName = dt.TableName;
mt.GetRows = delegate() { return(GetJSONRows(dt, mt)); };
mt.Fields = l.ToArray();
lt.Add(mt);
}
if (args["Location"] == "File")
{
if (!this.connectParams.ContainsKey("File"))
{
this.connectParams.Add("File", new Dictionary <string, List <QvxTable> >());
}
if (this.connectParams["File"].ContainsKey(args["File Name"]))
{
this.connectParams["File"][args["File Name"]] = lt;
}
else
{
this.connectParams["File"].Add(args["File Name"], lt);
}
}
else
{
string param = args["Method"] == "POST" ? args["Params"] : "default";
if (!this.connectParams.ContainsKey(args["Method"]))
{
this.connectParams.Add(args["Method"], new Dictionary <string, List <QvxTable> >());
}
if (this.connectParams[args["Method"]].ContainsKey(param))
{
this.connectParams[args["Method"]][param] = lt;
}
else
{
this.connectParams[args["Method"]].Add(param, lt);
}
}
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, "- getTables()");
return(lt);
}
public static QvxTable.GetRowsHandler ListBucketObjectsRows(IDictionary <string, string> fields, QvxTable table, string jsonCredentials)
{
return(() =>
{
List <QvxDataRow> rows = new List <QvxDataRow>();
GoogleCredential credential = null;
StorageClient storage = null;
Google.Api.Gax.PagedEnumerable <Objects, Google.Apis.Storage.v1.Data.Object> bucketObjects = null;
fields.TryGetValue("bucketname", out string bucketName);
if (String.IsNullOrEmpty(bucketName))
{
throw new QvxPleaseSendReplyException(QvxResult.QVX_UNKNOWN_COMMAND, String.Format("Missing required param: {0}", "BucketName"));
}
try
{
credential = GoogleCredential.FromJson(jsonCredentials);
storage = StorageClient.Create(credential);
bucketObjects = storage.ListObjects(bucketName, null, null);
foreach (Google.Apis.Storage.v1.Data.Object bucketObject in bucketObjects)
{
var row = new QvxDataRow();
row[table.Fields[0]] = bucketObject.Acl?.ToString() ?? "";
row[table.Fields[1]] = bucketObject.Bucket?.ToString() ?? "";
row[table.Fields[2]] = bucketObject.CacheControl?.ToString() ?? "";
row[table.Fields[3]] = bucketObject.ComponentCount ?? 0;
row[table.Fields[4]] = bucketObject.ContentDisposition?.ToString() ?? "";
row[table.Fields[5]] = bucketObject.ContentEncoding?.ToString() ?? "";
row[table.Fields[6]] = bucketObject.ContentLanguage?.ToString() ?? "";
row[table.Fields[7]] = bucketObject.Crc32c.ToString() ?? "";
row[table.Fields[8]] = bucketObject.ETag?.ToString() ?? "";
row[table.Fields[9]] = bucketObject.Generation?.ToString() ?? "";
row[table.Fields[10]] = bucketObject.Id?.ToString() ?? "";
row[table.Fields[11]] = bucketObject.Kind?.ToString() ?? "";
row[table.Fields[12]] = bucketObject.KmsKeyName?.ToString() ?? "";
row[table.Fields[13]] = bucketObject.Md5Hash?.ToString() ?? "";
row[table.Fields[14]] = bucketObject.MediaLink?.ToString() ?? "";
row[table.Fields[15]] = ""; // bucketObject.Metadata?.ToString() ?? "";
row[table.Fields[16]] = bucketObject.Metageneration ?? 0;
row[table.Fields[17]] = bucketObject.Name?.ToString() ?? "";
row[table.Fields[18]] = bucketObject.Owner?.ToString() ?? "";
row[table.Fields[19]] = bucketObject.SelfLink?.ToString() ?? "";
row[table.Fields[20]] = bucketObject.Size ?? 0;
row[table.Fields[21]] = bucketObject.StorageClass?.ToString() ?? "";
row[table.Fields[22]] = bucketObject.TimeCreatedRaw?.ToString() ?? "";
row[table.Fields[23]] = bucketObject.TimeDeletedRaw?.ToString() ?? "";
row[table.Fields[24]] = bucketObject.TimeStorageClassUpdatedRaw?.ToString() ?? "";
row[table.Fields[25]] = bucketObject.UpdatedRaw?.ToString() ?? "";
rows.Add(row);
}
}
catch (Exception ex)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Error, ex.Message);
throw new QvxPleaseSendReplyException(QvxResult.QVX_UNKNOWN_COMMAND, "Error getting the data from Google Storage");
}
return rows;
});
}
public static QvxDataTable ListBucketObjects(QvxTable bucketObjectsTable, IDictionary <string, string> fields, string jsonCredentials)
{
bucketObjectsTable.GetRows = ListBucketObjectsRows(fields, bucketObjectsTable, jsonCredentials);
return(new QvxDataTable(bucketObjectsTable));
}
public static QvxTable.GetRowsHandler ListBucketsRows(QvxTable table, string jsonCredentials)
{
string GCPProjectId = "";
try
{
var JSONObj = new JavaScriptSerializer().Deserialize <Dictionary <string, string> >(jsonCredentials);
GCPProjectId = JSONObj["project_id"];
} catch (Exception ex)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Error, ex.Message);
throw new QvxPleaseSendReplyException(QvxResult.QVX_UNKNOWN_COMMAND, "Error parsing the JSON credentials file.");
}
return(() =>
{
List <QvxDataRow> rows = new List <QvxDataRow>();
GoogleCredential credential = null;
StorageClient storage = null;
Google.Api.Gax.PagedEnumerable <Buckets, Bucket> buckets = null;
try
{
credential = GoogleCredential.FromJson(jsonCredentials);
storage = StorageClient.Create(credential);
buckets = storage.ListBuckets(GCPProjectId);
} catch (Exception ex)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Error, ex.Message);
throw new QvxPleaseSendReplyException(QvxResult.QVX_UNKNOWN_COMMAND, "Error getting the data from Google Storage");
}
int length = 0;
foreach (Bucket bucket in buckets)
{
length++;
}
if (length == 0)
{
throw new QvxPleaseSendReplyException(QvxResult.QVX_UNKNOWN_COMMAND, String.Format("No buckets found for project {0}", GCPProjectId));
}
else
{
foreach (Bucket bucket in buckets)
{
var row = new QvxDataRow();
row[table.Fields[0]] = bucket.Acl?.ToString() ?? "";
row[table.Fields[1]] = bucket.Billing?.ToString() ?? "";
row[table.Fields[2]] = bucket.Cors?.ToString() ?? "";
row[table.Fields[3]] = bucket.ETag?.ToString() ?? "";
row[table.Fields[4]] = bucket.Encryption?.ToString() ?? "";
row[table.Fields[5]] = bucket.Id?.ToString() ?? "";
row[table.Fields[6]] = bucket.Kind?.ToString() ?? "";
row[table.Fields[7]] = bucket.Labels?.ToString() ?? "";
row[table.Fields[8]] = bucket.Lifecycle?.ToString() ?? "";
row[table.Fields[9]] = bucket.Location?.ToString() ?? "";
row[table.Fields[10]] = bucket.Logging?.ToString() ?? "";
row[table.Fields[11]] = bucket.Name?.ToString() ?? "";
row[table.Fields[12]] = bucket.Owner?.ToString() ?? "";
rows.Add(row);
}
}
return rows;
});
}
public static QvxDataTable ListBuckets(QvxTable bucketsTable, string jsonCredentials)
{
bucketsTable.GetRows = ListBucketsRows(bucketsTable, jsonCredentials);
return(new QvxDataTable(bucketsTable));
}
private QvxDataRow InsertRow(dynamic sourceRow, QvxTable tableDef, dynamic parentData)
{
QvxDataRow destRow = new QvxDataRow();
foreach (QvxField fieldDef in tableDef.Fields)
{
dynamic originalDef = helper.ActiveFields[fieldDef.FieldName];
dynamic sourceField;
if(originalDef.path.ToString().IndexOf("{parent}")==-1){
sourceField = GetSourceValue(sourceRow, originalDef.path.ToString(), originalDef.type.ToString());
}
else{
String parentPath = originalDef.path.ToString();
parentPath = parentPath.Replace("{parent}.", "");
sourceField = GetSourceValue(parentData, parentPath, originalDef.type.ToString());
}
if (sourceField != null)
{
destRow[fieldDef] = sourceField.ToString();
}
else if (originalDef.path.ToString()=="")
{
sourceField = GetSourceValue(sourceRow, null, originalDef.type.ToString());
}
}
//recordsLoaded++;
return destRow;
}
public override QvxDataTable ExtractQuery(string query, List <QvxTable> tables)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, "+ ExtractQuery()");
Dictionary <string, string> myArgs = this.args.Select(kv => kv).ToDictionary(kv => kv.Key, kv => kv.Value);
QvxDataTable retVal = null;
try {
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, "ExtractQuery() : " + query + " " + ((tables != null) ? String.Join("|", tables.Select(t => t.TableName)) : ""));
IQlikConnector c = this.parent.Registered(myArgs["qDriver"]);
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, "ExtractQuery() : driver found!");
string pattern =
"^" +
"SELECT" +
"\\s*(?<select>" +
"((\\[(?<field>[^\\[\\]]+)\\]|(?<field>[^\\[\\],\\s]+))(\\s*,\\s*))*" +
"((\\[(?<field>[^\\[\\]]+)\\]|(?<field>[^\\[\\],\\s]+)))*" +
")\\s*" +
"FROM" +
"\\s*(?<from>" +
"(\\[(?<db>[^\\[\\]]*?)\\]|(?<db>[^\\[\\]\\.\\s]*?))" +
"\\s*\\.\\s*" +
"(\\[(?<table>[^\\[\\]]*?)\\]|(?<table>[^\\[\\]\\.\\s]*?))" +
")\\s*" +
"(WHERE" +
"\\s*(?<where>" +
".*" +
")\\s*" +
")?" +
"$"
;
Match m = Regex.Match(query.Trim(), pattern, RegexOptions.IgnoreCase | RegexOptions.Singleline
);
if (m.Success)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, "ExtractQuery() : query matched!");
string select = m.Groups["select"].Value.Trim();
string from = m.Groups["from"].Value.Trim();
string where = (m.Groups["where"].Success) ? m.Groups["where"].Value.Trim() : null;
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, String.Format("ExtractQuery() : {0}, {1}, {2}!", select, from, where));
if (m.Groups["where"].Success)
{
bool quoted = false, escaped = false, name = true;
Dictionary <string, string> currParam = new Dictionary <string, string>();
string currParamName = "";
string currParamValue = "";
#region //where clause parsing
int i = 0;
where.ToList().ForEach(chr =>
{
bool addchar = false;
if (!escaped && !quoted && chr == '\\')
{
throw new ArgumentOutOfRangeException(String.Format("unescaped \\ @{0} in where clause", i));
}
if (!escaped && quoted && chr == '\\')
{
escaped = true;
}
else if (!escaped && !quoted && chr == '=')
{
name = false;
escaped = false;
}
else if (!escaped && chr == '"')
{
if (quoted)
{
if (!name)
{
currParam.Add(currParamName, currParamValue);
currParamName = "";
currParamValue = "";
name = true;
}
else
{
throw new ArgumentOutOfRangeException(String.Format("quote in param name @{0} in where clause", i));
}
}
quoted = !quoted;
escaped = false;
}
else
{
addchar = true;
escaped = false;
}
if (addchar)
{
if (name)
{
currParamName += chr;
}
else
{
currParamValue += chr;
}
}
i++;
});
#endregion
currParam.ToList().ForEach(kv =>
{
Match mWhere = Regex.Match(kv.Key, "^(\\s*AND)?\\s*((?<param>[^\\s]+)|\\[(?<param>[^\\]]+)\\])\\s*$");
if (mWhere.Success)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, String.Format("ExtractQuery() : {0}, {1}!", mWhere.Groups["param"].Value, kv.Value));
myArgs[mWhere.Groups["param"].Value.Trim().Replace("[", "").Replace("]", "")] = kv.Value.Trim();
}
else
{
throw new ArgumentOutOfRangeException();
}
});
}
List <string> fields = m.Groups["field"].Captures.Cast <Capture>().Select(cap => cap.Value).ToList();
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, String.Format("ExtractQuery() : {0}!", String.Join("|", fields)));
string fromDb = m.Groups["db"].Value.Trim();
string fromTable = m.Groups["table"].Value.Trim();
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, String.Format("ExtractQuery() : {0}, {1}!", fromDb, fromTable));
Database db = c.getDatabases(myArgs).Where(dbItem => dbItem.qName == fromDb).FirstOrDefault();
if (db != null)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, "ExtractQuery() : found db!");
QvxTable table = c.getTables(db, myArgs).Where(tableItem => tableItem.TableName == fromTable).FirstOrDefault();
if (table != null)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, "ExtractQuery() : found table!");
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, String.Format("ExtractQuery() : {0}", table.GetRows().Count()));
QvxDataTable t = new QvxDataTable(table);
t.Select(table.Fields.Where(fld => fields.Contains(fld.FieldName)).ToArray());
return(t);
}
}
}
}
catch (Exception e)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Error, "ExtractQuery() : " + e.Message);
throw e;
}
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, "- ExtractQuery()");
return(retVal);
}
private QvxDataRow MakeEntry(XmlNode rowNode, QvxTable table)
{
var row = new QvxDataRow();
foreach (XmlNode field_node in rowNode.ChildNodes)
{
switch (field_node.Name.ToLower())
{
case "billtocode":
row[table.Fields[0]] = field_node.InnerText;
break;
case "billtodesc":
row[table.Fields[1]] = field_node.InnerText;
break;
case "billtoeffdt":
row[table.Fields[2]] = field_node.InnerText;
break;
case "billtoexpdt":
row[table.Fields[3]] = field_node.InnerText;
break;
}
}
//row[table.Fields[0]] = "CODE VALUE 1";
//row[table.Fields[1]] = "CODE DESCRIPTION 1";
//row[table.Fields[2]] = DateTime.Now.ToString();
//row[table.Fields[3]] = DateTime.Now.ToString();
//row[table.Fields[0]] = evl.Category;
//row[table.Fields[1]] = evl.EntryType.ToString();
//row[table.Fields[2]] = evl.Message;
//row[table.Fields[3]] = evl.CategoryNumber.ToString();
//row[table.Fields[4]] = evl.Index.ToString();
//row[table.Fields[5]] = evl.MachineName;
//row[table.Fields[6]] = evl.Source;
//row[table.Fields[7]] = evl.TimeGenerated.ToString();
return row;
}
private QvxDataRow MakeEntry(DataRow dr, QvxTable table)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Notice, "MakeEntry()");
var row = new QvxDataRow();
foreach (DataColumn dc in dr.Table.Columns)
{
row[table[dc.ColumnName]] = dr[dc].ToString();
}
return row;
}
/// <summary>
/// Creates a Qvx data entry from an Svn log entry.
/// </summary>
/// <param name="item">The Svn log entry to be used as a source</param>
/// <param name="table">The destination Qvx table</param>
/// <returns>The resulting row</returns>
private QvxDataRow MakeEntry(SvnLogEventArgs entry, QvxTable table)
{
QvxDataRow row = new QvxDataRow();
row[table.Fields[0]] = entry.Author;
row[table.Fields[1]] = entry.LogMessage;
row[table.Fields[2]] = entry.Revision;
row[table.Fields[3]] = entry.Time.ToOADate();
return row;
}
private async Task<QvxDataRow> MakeEntry(string id, EntityModel model, QvxTable table)
{
var row = new QvxDataRow();
var doc = await _bucket.GetAsync<string>(id);
Dictionary<string, object> raw = null;
try
{
raw = JsonConvert.DeserializeObject<Dictionary<string, object>>(doc.Value);
}
catch (Exception)
{
return null;
}
row[table.Fields[0]] = model.Name;
for (int i = 1; i < table.Fields.Length; i++ )
row[table.Fields[i]] = raw[table.Fields[i].FieldName].ToString();
return row;
}
