public void PrintSummaryResults(string tableName, Globals.ResultWriterDestination dest)
{
string errorLevel = (dest == Globals.ResultWriterDestination.stdOut) ? "Error" : "Warning";
SqliteStatus = db.ExecuteQuery("SELECT ErrorType, ErrorColumn, count(*) AS TotalCount FROM " + tableName + "_Errors WHERE ErrorLevel = '" + errorLevel + "' GROUP BY ErrorType, ErrorColumn;",
Validate, "SUMMARY RESULTS (" + errorLevel + ")", dest);
}
public void PrintDetailResults(string tableName, Globals.ResultWriterDestination dest)
{
string errorLevel = (dest == Globals.ResultWriterDestination.stdOut) ? "Error" : "Warning";
SqliteStatus = db.ExecuteQuery("SELECT * FROM " + tableName + "_Errors WHERE ErrorLevel = '" + errorLevel + "' ORDER BY ErrorType, ErrorColumn",
Validate, "DETAIL RESULTS (" + errorLevel + ")", dest);
}
public IResultWriter Write(string line, Globals.ResultWriterDestination dest)
{
switch (dest)
{
case Globals.ResultWriterDestination.stdOut:
WriteStd(line);
break;
case Globals.ResultWriterDestination.Error:
WriteError(line);
break;
case Globals.ResultWriterDestination.Verbose:
WriteVerbose(line);
break;
case Globals.ResultWriterDestination.Warning:
WriteWarning(line);
break;
default:
break;
}
return(this);
}
public IResultWriter EndContext(Globals.ResultWriterDestination dest = Globals.ResultWriterDestination.stdOut)
{
if (ContextStack.Count > 0)
{
CurrentContext = ContextStack.Pop();
}
return(this);
}
public IResultWriter EndContext(Globals.ResultWriterDestination dest = Globals.ResultWriterDestination.stdOut)
{
if (ContextStack.Count > 0)
{
CurrentContext = ContextStack.Pop();
}
colNames = new List <string>();
return(this);
}
public IResultWriter WriteResult(IReadOnlyList <IResultSetValue> result, Globals.ResultWriterDestination dest)
{
if (CurrentContext.ResultsWritten++ > 0)
{
Write("," + Environment.NewLine, dest);
}
Write(CurrentContext.ResultsWritten.ToString().DoubleQuote() + ":" + GetResultWithKeyAsJsonObject(result), dest);
return(this);
}
public IResultWriter EndContext(Globals.ResultWriterDestination dest = Globals.ResultWriterDestination.stdOut)
{
stdOut.Flush();
VerboseOut.Flush();
if (ContextStack.Count > 0)
{
CurrentContext = ContextStack.Pop();
}
return(this.DecreaseIndent());
}
public IResultWriter EndContext(Globals.ResultWriterDestination dest = Globals.ResultWriterDestination.stdOut)
{
Write("}", dest);
stdOut.Flush();
VerboseOut.Flush();
if (ContextStack.Count > 0)
{
CurrentContext = ContextStack.Pop();
}
return(this);
}
public IResultWriter BeginContext(string context, Globals.ResultWriterDestination dest)
{
//if (CurrentContext != null && CurrentContext.ContextsWritten++ > 0)
//IncreaseIndent();
if (CurrentContext != null)
{
ContextStack.Push(CurrentContext);
}
CurrentContext = new ResultContext(context);
Write(string.Concat(Enumerable.Repeat(Delimiter, IndentationLevel)) + context, dest);
return(this.IncreaseIndent());
}
public IResultWriter BeginContext(string context, Globals.ResultWriterDestination dest)
{
//if (CurrentContext != null && CurrentContext.ContextsWritten++ > 0)
//IncreaseIndent();
if (CurrentContext != null)
{
ContextStack.Push(CurrentContext);
}
CurrentContext = new ResultContext(context);
Write(context, dest);
return(this);
}
public IResultWriter BeginContext(string context, Globals.ResultWriterDestination dest)
{
if (CurrentContext != null)
{
ContextStack.Push(CurrentContext);
}
CurrentContextName = context.Replace(".", "_");
CurrentContextName += String.Join("_", ContextStack.Select(x => x.Name));
CurrentContext = new ResultContext(context);
TableIsCreated = false;
ContextColumnIsQuoted = new Dictionary <string, bool>();
//stdOut.WriteLine(string.Concat(Enumerable.Repeat(Delimiter, IndentationLevel)) + context);
return(this);
}
public IResultWriter BeginContext(string context, Globals.ResultWriterDestination dest)
{
if (CurrentContext != null)
{
if (CurrentContext.ContextsWritten++ > 0)
{
stdOut.Write("," + Environment.NewLine);
}
ContextStack.Push(CurrentContext);
CurrentContext = new ResultContext(context);
}
else
{
CurrentContext = new ResultContext(context, 1);
}
Write(context.DoubleQuote() + ": {", dest);
VerboseOut.WriteLine("Beginning context " + CurrentContext.Name);
return(this);
}
public IResultWriter WriteResult(IReadOnlyList <IResultSetValue> result, Globals.ResultWriterDestination dest)
{
if (!TableIsCreated)
{
string sql = "CREATE TABLE [" + CurrentContextName + "] ( {0} );";
List <string> cols = new List <string>();
foreach (var r in result)
{
var colname = r.ColumnInfo.Name;
bool isQuoted = false;
string mssqlDatatype = "";
if (r.ColumnInfo.TableName != null)
{
var query = "SELECT ColumnType FROM TableMetadata WHERE Tablename = " +
r.ColumnInfo.TableName.SingleQuote() + " AND ColumnName = " + colname.SingleQuote() + ";";
var enumerator = SqliteDb.GetEnumeratorForQuery(query);
if (enumerator.MoveNext())
{
var sqliteDatatype = enumerator.Current[0].ToString();
var tuple = SqliteToMsSqlLookup
.Where(t => (t.Item1.ToString().ToLower() == sqliteDatatype.ToLower()))
.Select(t => t).ElementAt(0);
var etlDatatype = tuple.Item1;
mssqlDatatype = (tuple.Item2 != null) ? tuple.Item2 : "varchar(max)";
isQuoted = (tuple.Item3.ToLower() == "quoted");
if (etlDatatype == DataType.@decimal || etlDatatype == DataType.@double || etlDatatype == DataType.@float)
{
string precscale = "18";
var precisionSql = "with cte as (" +
"select distinct length(" + colname + ")-1 as precision, length(" + colname + ") - instr(" + colname + ", '.') as scale, " +
"length(" + colname + ")-1 - (length(" + colname + ") - instr(" + colname + ", '.')) as beforeDec " +
"from " + r.ColumnInfo.TableName +
") " +
"select case when max(precision)+max(beforeDec)-1 - min(scale) >= 38 then 38 " +
"else max(precision)+max(beforeDec)-1 " +
"end as precision, max(scale) as scale " +
"from cte;";
enumerator = SqliteDb.GetEnumeratorForQuery(precisionSql);
if (enumerator.MoveNext())
{
int precnum, scalenum = 0;
int.TryParse(enumerator.Current[0].ToString(), out precnum);
int.TryParse(enumerator.Current[1].ToString(), out scalenum);
var prec = (string.IsNullOrWhiteSpace(enumerator.Current[0].ToString()) || precnum <= 0) ? "18," : enumerator.Current[0].ToString() + ",";
var scale = (string.IsNullOrWhiteSpace(enumerator.Current[1].ToString()) || scalenum <= 0) ? "2" : enumerator.Current[1].ToString();
precscale = prec + scale;
}
mssqlDatatype = string.Format(mssqlDatatype, precscale);
}
else if (etlDatatype == DataType.@sbyte || etlDatatype == DataType.@uint || etlDatatype == DataType.@ulong || etlDatatype == DataType.@ushort)
{
mssqlDatatype = string.Format(mssqlDatatype, colname);
}
}
else
{
mssqlDatatype = string.Format("varchar(max)", colname);
isQuoted = true;
}
}
else
{
// NPS_TODO try to ascertain datatype
mssqlDatatype = "nvarchar(max)";
ContextColumnIsQuoted.Add("null_" + colname, true);
}
cols.Add(colname + " " + mssqlDatatype);
ContextColumnIsQuoted.Add(r.ColumnInfo.TableName + "_" + colname, isQuoted);
}
sql = string.Format(sql, string.Join(",", cols));
Console.WriteLine(sql);
Write(sql, dest);
TableIsCreated = true;
}
Write(GetResultAsSqlInsertString(result), dest);
return(this);
}
public IResultWriter WriteHeaders(List <string> headers, Globals.ResultWriterDestination dest)
{
Write(String.Join(Delimiter.ToString(), headers.ToArray()), dest);
return(this);
}
public IResultWriter WriteHeaders(List <string> headers, Globals.ResultWriterDestination dest)
{
Write(string.Concat(Enumerable.Repeat(Delimiter, IndentationLevel)) + String.Join(Delimiter.ToString(), headers.ToArray()), dest);
return(this);
}
public int ExecuteQuery(string sql, IResultWriter sw, string context = "", Globals.ResultWriterDestination dest = Globals.ResultWriterDestination.stdOut)
{
Action <Int64> temp = (i => i = 0);
return(ExecuteQuery(sql, sw, temp, context, dest));
}
public int ExecuteQuery(string sql, IResultWriter sw, Action <Int64> setResults, string context = "", Globals.ResultWriterDestination dest = Globals.ResultWriterDestination.stdOut)
{
Int64 numResults = 0;
if (sql.Contains("ROW_NUMBER("))
{
SQLitePCL.Functions.core.CoreFn.RowNumDictionary = new Dictionary <string, int>();
}
if (sql.Contains("JSON("))
{
var match = Regex.Match(sql, @"JSON\((?<key>.*?)\)");
sql = Regex.Replace(sql, @"JSON\(\w*\)", "", RegexOptions.IgnoreCase);
sw.Key = match.Groups["key"].Value;
}
int resp = (int)ErrorCode.Ok;
try
{
var stmts = DbConnection.PrepareAll(CleanSql(sql));
foreach (var stmt in stmts)
{
if (stmt.SQL != null && stmt.MoveNext())
{
var headers = stmt.Columns.Select(col => col.Name).ToList <string>();
sw
.BeginContext(context, dest)
.WriteHeaders(headers, dest)
.WriteResult(stmt.Current, dest);
// Write the rest...
while (stmt.MoveNext())
{
numResults++;
sw.WriteResult(stmt.Current, dest);
}
sw.EndContext(dest);
}
}
}
catch (SQLiteException e)
{
resp = (int)e.ErrorCode;
LastError = e.Message + " -- " + raw.sqlite3_errmsg(RawDbConnection) + " -- on query: " + sql;
}
finally
{
sw.Flush();
Int64 changes = DbConnection.Changes;
// if we didn't make any db changes, then we performed a query, so let's return the number of results in that query
if (changes == 0 && numResults != 0)
{
changes = numResults;
}
setResults(changes);
}
return(resp);
}
public void PrintGeneralIssues(Globals.ResultWriterDestination dest)
{
string errorLevel = (dest == Globals.ResultWriterDestination.stdOut) ? "Error" : "Warning";
SqliteStatus = db.ExecuteQuery("SELECT * FROM GeneralErrors WHERE ErrorLevel = '" + errorLevel + "' ORDER BY ErrorType, ErrorTable, ErrorColumn;", Validate, "General Errors (" + errorLevel + ")");
}
public IResultWriter WriteResult(IReadOnlyList <IResultSetValue> result, Globals.ResultWriterDestination dest)
{
Write(GetResultAsDelimitedString(result), dest);
return(this);
}
public IResultWriter WriteHeaders(List <string> headers, Globals.ResultWriterDestination dest)
{
colNames = headers;
return(this);
}
public IResultWriter WriteResult(IReadOnlyList <IResultSetValue> result, Globals.ResultWriterDestination dest)
{
Write(string.Concat(Enumerable.Repeat(Delimiter, IndentationLevel)) + GetResultAsDelimitedString(result), dest);
return(this);
}
