private Ast.ImportCsvStmt ParseImportCsvStmt(TokenQueue q) // or null
{
var stmt = new Ast.ImportCsvStmt {
SourceToken = q.SourceToken
};
var start = q.GetLocation();
if (!q.TakeMaybe("import"))
{
q.Jump(start); return(null);
}
if (!q.TakeMaybe("csv"))
{
q.Jump(start); return(null);
}
stmt.FilenameExpr = ParseExpr(q);
q.Take("into");
stmt.ImportTable = Check(q, ParseImportTable(q));
if (q.TakeMaybe("options"))
{
stmt.OptionsList = Check(q, ParseOptionsList(q));
}
ConsumeSemicolon(q);
return(stmt);
}
private void ExecuteImportCsvStmt(Ast.ImportCsvStmt stmt, ScriptEnv env)
{
try {
ImportCsvStmtRunner.Run(_notebook, env, this, stmt);
} catch (Exception ex) {
Throw(env, -1, ex.Message, -1);
}
}
private ImportCsvStmtRunner(INotebook notebook, ScriptEnv env, ScriptRunner runner, Ast.ImportCsvStmt stmt)
{
_notebook = notebook;
_env = env;
_runner = runner;
_stmt = stmt;
foreach (var option in _stmt.OptionsList.GetOptionKeys())
{
switch (option)
{
case "SKIP_LINES":
_skipLines = _stmt.OptionsList.GetOptionLong(option, _runner, _env, 0, minValue: 0);
break;
case "TAKE_LINES":
_takeLines = _stmt.OptionsList.GetOptionLong(option, _runner, _env, -1, minValue: -1);
break;
case "HEADER_ROW":
_headerRow = _stmt.OptionsList.GetOptionBool(option, _runner, _env, true);
break;
case "TRUNCATE_EXISTING_TABLE":
_truncateExistingTable = _stmt.OptionsList.GetOptionBool(option, _runner, _env, false);
break;
case "TEMPORARY_TABLE":
_temporaryTable = _stmt.OptionsList.GetOptionBool(option, _runner, _env, false);
break;
case "FILE_ENCODING":
_fileEncoding = _stmt.OptionsList.GetOptionEncoding(option, _runner, _env);
break;
case "IF_CONVERSION_FAILS":
_ifConversionFails = (IfConversionFails)_stmt.OptionsList.GetOptionLong(
option, _runner, _env, 1, minValue: 1, maxValue: 3);
break;
default:
throw new Exception($"\"{option}\" is not a recognized option name.");
}
}
}
// must be run from the SQLite thread
public static void Run(INotebook notebook, ScriptEnv env, ScriptRunner runner, Ast.ImportCsvStmt stmt)
{
var importer = new ImportCsvStmtRunner(notebook, env, runner, stmt);
SqlUtil.WithTransaction(notebook, importer.Import);
}
public override bool Apply(Ast.SqlStmt input)
{
var tableFunctionCallNodes = input.Traverse()
.OfType <Ast.SqliteSyntaxProduction>()
.Where(x => x.Name == "table-or-subquery.table-function-call");
foreach (var tableFunctionCallNode in tableFunctionCallNodes)
{
var functionName = tableFunctionCallNode.Traverse()
.OfType <Ast.SqliteSyntaxProduction>()
.FirstOrDefault(x => x.Name == "table-or-subquery.table-function-name")
?.Text;
if (functionName.ToLowerInvariant() != "read_csv")
{
continue;
}
// read_csv(file-path, [has-header-row], [skip-rows], [file-encoding])
var args = tableFunctionCallNode.TraverseDottedChildren()
.Where(x => x.Name == "table-or-subquery.arg").ToList();
if (args.Count < 1 || args.Count > 4)
{
throw new MacroProcessorException($"READ_CSV: Between 1 and 4 arguments are required.");
}
var filePathExpr = args[0];
var hasHeaderRowExpr = args.Count >= 2 ? args[1] : null;
var skipRowsExpr = args.Count >= 3 ? args[2] : null;
var fileEncodingExpr = args.Count >= 4 ? args[3] : null;
var tempTableName = Guid.NewGuid().ToString();
var importStmt = new Ast.ImportCsvStmt {
SourceToken = tableFunctionCallNode.SourceToken,
FilenameExpr = NewExpr(filePathExpr.Text),
ImportTable = new Ast.ImportTable {
TableName = new Ast.IdentifierOrExpr {
Identifier = tempTableName
},
},
OptionsList = new Ast.OptionsList {
Options = new Dictionary <string, Ast.Expr> {
["temporary_table"] = NewExpr("1"),
["skip_lines"] = NewExpr(skipRowsExpr?.Text ?? "0"),
["header_row"] = NewExpr(hasHeaderRowExpr?.Text ?? "1"),
["file_encoding"] = NewExpr(fileEncodingExpr?.Text ?? "0")
}
}
};
foreach (var n in importStmt.Traverse())
{
n.SourceToken = tableFunctionCallNode.SourceToken;
}
input.RunBefore.Add(importStmt);
var dropTableStmt = NewSqlStmt($"DROP TABLE IF EXISTS {tempTableName.DoubleQuote()}");
input.RunAfter.Add(dropTableStmt);
// replace the READ_CSV(...) call with the table name
var originalTokens = Core.Notebook.Tokenize(input.Sql).Select(x => x.Text).ToList();
var tokenReplaceIndex = tableFunctionCallNode.StartToken - input.FirstTokenIndex;
for (var i = 0; i < tableFunctionCallNode.NumTokens; i++)
{
originalTokens.RemoveAt(tokenReplaceIndex);
}
originalTokens.Insert(tokenReplaceIndex, tempTableName.DoubleQuote());
var newSqlText = string.Join(" ", originalTokens);
input.Sql = newSqlText;
input.SqliteSyntax = ParseSqlStmt(newSqlText);
return(true);
}
return(false);
}
