public override QvxDataTable ExtractQuery(string query, List <QvxTable> tables)
{
try
{
var script = ScriptCode.Parse(query);
var username = "";
var password = "";
var workdir = "";
var hostName = "";
this.MParameters.TryGetValue("userid", out username);
this.MParameters.TryGetValue("password", out password);
this.MParameters.TryGetValue("workdir", out workdir);
this.MParameters.TryGetValue("host", out hostName);
username = (username ?? "").Trim();
password = (password ?? "").Trim();
workdir = (workdir ?? "").Trim();
hostName = (hostName ?? "").Trim();
var qvxTable = GetData(script, username, password, workdir, hostName);
var result = new QvxDataTable(qvxTable);
result.Select(qvxTable.Fields);
return(result);
}
catch (Exception ex)
{
logger.Error(ex, "The query could not be executed.");
return(new QvxDataTable(new QvxTable()
{
TableName = "Error"
}));
}
}
public override QvxDataTable ExtractQuery(string query, List <QvxTable> tables)
{
try
{
AppInstance.LoadMemory();
logger.Debug($"Parse query {query}");
var script = ScriptCode.Parse(query);
if (script == null)
{
throw new Exception("The sql script is not valid.");
}
var parameter = ConnectorParameter.Create(MParameters);
var qvxTable = GetData(script, parameter);
var result = new QvxDataTable(qvxTable);
result.Select(qvxTable.Fields);
AppInstance.SaveMemory();
AppInstance.Dispose();
logger.Debug($"Send result table {qvxTable.TableName}");
return(result);
}
catch (Exception ex)
{
logger.Error(ex, "The query could not be executed.");
LogManager.Flush();
return(new QvxDataTable(new QvxTable()
{
TableName = "Error"
}));
}
}
public override QvxDataTable ExtractQuery(string query, List <QvxTable> tables)
{
try
{
var script = ScriptCode.Parse(query);
var username = "";
var password = "";
var workdir = "";
this.MParameters.TryGetValue("userid", out username);
this.MParameters.TryGetValue("password", out password);
this.MParameters.TryGetValue("workdir", out workdir);
username = (username ?? "").Trim();
password = (password ?? "").Trim();
workdir = workdir ?? Environment.GetFolderPath(Environment.SpecialFolder.UserProfile);
internalData = GetData(script, username, password, workdir);
var fieldsl = new List <QvxField>();
foreach (DataColumn column in internalData.Columns)
{
fieldsl.Add(new QvxField(column.ColumnName, QvxFieldType.QVX_TEXT, QvxNullRepresentation.QVX_NULL_FLAG_SUPPRESS_DATA, FieldAttrType.ASCII));
}
fields = fieldsl.ToArray();
var table = new QvxTable()
{
TableName = script.TableName,
Fields = fields,
GetRows = GetPowerShellResult
};
var result = new QvxDataTable(table);
result.Select(fields);
return(result);
}
catch (Exception ex)
{
logger.Error(ex, "The query could not be executed.");
return(new QvxDataTable(new QvxTable()
{
TableName = "Error"
}));
}
}
public override QvxDataTable ExtractQuery(string line, List <QvxTable> qvxTables)
{
string tableName = Parsers.GetTableName(line, MTables);
IDictionary <string, string> fields = null;
if (tableName != "listbuckets")
{
fields = Parsers.GetWhereFields(line, tableName);
}
QvxDataTable returnTable = null;
switch (tableName)
{
case "listbuckets":
QvxDataTable a = StorageOperations.ListBuckets(FindTable("ListBuckets", MTables), jsonCredentials);
returnTable = a;
break;
case "bucketobjects":
QvxDataTable a1 = StorageOperations.ListBucketObjects(FindTable("BucketObjects", MTables), fields, jsonCredentials);
returnTable = a1;
break;
case "downloadobject":
QvxDataTable downloadObj = StorageOperations.DownloadObject(FindTable("DownloadObject", MTables), fields, jsonCredentials);
returnTable = downloadObj;
break;
case "uploadobject":
// TBA
throw new QvxPleaseSendReplyException(QvxResult.QVX_UNKNOWN_COMMAND, "Not implemented yet :(");
//break;
case "deletelocalobject":
// TBA
throw new QvxPleaseSendReplyException(QvxResult.QVX_UNKNOWN_COMMAND, "Not implemented yet :(");
//break;
default:
throw new QvxPleaseSendReplyException(QvxResult.QVX_UNKNOWN_COMMAND, "Please provide WHERE clause");
}
return(returnTable);
}
public override QvxDataTable ExtractQuery(string query, List <QvxTable> qvxTables)
{
QvxDataTable returnTable = null;
IList <ParseError> errors = null;
var parser = new TSql100Parser(true);
TSqlScript script;
using (TextReader reader = new StringReader(query))
{
script = parser.Parse(reader, out errors) as TSqlScript;
}
IList <TSqlParserToken> tokens = script.ScriptTokenStream;
// get record folder
TSqlParserToken fromToken = tokens.First(t => t.TokenType == TSqlTokenType.From);
int indexOfFromToken = tokens.IndexOf(fromToken);
IEnumerable <TSqlParserToken> tableTokens = tokens.Skip(indexOfFromToken);
TSqlParserToken identifier = tableTokens.First(t => t.TokenType == TSqlTokenType.Identifier || t.TokenType == TSqlTokenType.AsciiStringOrQuotedIdentifier);
string folderName = identifier.Text;
if (identifier.TokenType == TSqlTokenType.AsciiStringOrQuotedIdentifier)
{
folderName = folderName.Substring(1, folderName.Length - 2);
}
// get report name
tableTokens = tokens.Skip(tokens.IndexOf(identifier));
TSqlParserToken reportSeparator = tableTokens.First(t => t.TokenType == TSqlTokenType.Dot);
tableTokens = tokens.Skip(tokens.IndexOf(reportSeparator));
TSqlParserToken reportNameToken = tableTokens.First(t => t.TokenType == TSqlTokenType.Identifier || t.TokenType == TSqlTokenType.AsciiStringOrQuotedIdentifier);
string reportName = reportNameToken.Text;
if (reportNameToken.TokenType == TSqlTokenType.AsciiStringOrQuotedIdentifier)
{
reportName = reportName.Substring(1, reportName.Length - 2);
}
if (this.MParameters.ContainsKey("folder_name"))
{
if (folderName == this.MParameters["folder_name"] && this.MTables == null)
{
this.Init();
}
else if (folderName != this.MParameters["folder_name"])
{
this.MParameters["folder_name"] = folderName;
this.Init();
}
}
else
{
this.MParameters.Add("folder_name", folderName);
this.Init();
}
var newTable = this.FindTable(reportName, this.MTables);
returnTable = new QvxDataTable(newTable);
return(returnTable);
}
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);
}
