public QvDataContractResponse getTables(IQlikConnector driver, string database, Dictionary <string, string> userParameters)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, "+ getTables()");
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, String.Format("getTables() : {0}", String.Join(", ", userParameters.Select(kv => String.Format("{0} : {1}", kv.Key, kv.Value)))));
QvDataContractTableListResponse retVal;
try {
Database db = driver.getDatabases(userParameters).Find(x => x.qName == database);
retVal = new QvDataContractTableListResponse
{
qTables = driver
.getTables(db, userParameters)
.ToList()
};
}
catch (Exception e)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Error, "getTables() : " + e.Message);
throw e;
}
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, "- getTables()");
return(retVal);
}
public QvDataContractResponse getFields(IQlikConnector driver, string database, string table, Dictionary <string, string> userParameters)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, "+ getFields()");
QvDataContractResponse retVal;
try {
Database db = driver.getDatabases(userParameters).Find(x => x.qName == database);
QvxTable tb = driver.getTables(db, userParameters).Find(x => x.TableName == table);
retVal = new QvDataContractFieldListResponse
{
qFields = driver.getFields(db, tb, userParameters).ToArray()
};
}
catch (Exception e)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Error, "getFields() : " + e.Message);
throw e;
}
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, "- getFields()");
return(retVal);
}
public QvDataContractResponse getPreview(IQlikConnector driver, string database, string table, Dictionary <string, string> userParameters)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, "+ getPreview()");
QvDataContractPreviewResponse retVal;
try {
Database db = driver.getDatabases(userParameters).Find(x => x.qName == database);
QvxTable tb = driver.getTables(db, userParameters).Find(x => x.TableName == table);
retVal = new QvDataContractPreviewResponse()
{
qPreview = new List <MyQvxDataRow>()
{
new MyQvxDataRow()
{
qValues = driver.getFields(db, tb, userParameters).Select(a => a.FieldName).ToList()
}
}
};
retVal.qPreview.AddRange(
driver.getPreview(db, tb, userParameters).Select(
a => new MyQvxDataRow()
{
qValues = a
}
).ToList()
);
}
catch (Exception e)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Error, "getPreview() : " + e.Message);
throw e;
}
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, "- getPreview()");
return(retVal);
}
public QvDataContractResponse getDatabases(IQlikConnector driver, Dictionary <string, string> userParameters)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, "+ getDatabases()");
QvDataContractDatabaseListResponse retVal;
try {
retVal = new QvDataContractDatabaseListResponse
{
qDatabases = driver
.getDatabases(userParameters)
.ToArray()
};
}
catch (Exception e)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Error, "getDatabases() : " + e.Message);
throw e;
}
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, "- getDatabases()");
return(retVal);
}
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);
}
