private void Import()
{
using (var reader = NewReader(GetFilePath())) {
// skip the specified number of initial file lines
for (int i = 0; i < _skipLines && !reader.EndOfStream; i++)
{
reader.ReadLine();
}
CreateOrTruncateTable();
if (_textColumnName != null)
{
SqlUtil.VerifyColumnsExist(new[] { _lineNumberColumnName, _textColumnName }, _tableName, _notebook);
}
var insertSql = GetInsertSql();
var args = new object[2];
for (long i = 0; (!_takeLines.HasValue || i < _takeLines.Value) && !reader.EndOfStream; i++)
{
args[0] = i;
args[1] = reader.ReadLine();
_notebook.Execute(insertSql, args);
}
}
}
public static void CreateOrTruncateTable(IReadOnlyList <string> srcColNames, Ast.ImportColumn[] dstColNodes,
IReadOnlyList <string> dstColNames, string dstTableName, bool temporaryTable, bool truncateExistingTable,
INotebook notebook)
{
// create the table if it doesn't already exist.
var columnDefs = new List <string>();
if (dstColNodes.All(x => x == null))
{
// the user did not specify a column list, so all columns will be included
columnDefs.AddRange(srcColNames.Select(SqlUtil.DoubleQuote));
}
else
{
// the user specified which columns to include
for (int i = 0; i < dstColNodes.Length; i++)
{
if (dstColNodes[i] != null)
{
var name = dstColNames[i];
var type = dstColNodes[i].TypeConversion?.ToString() ?? "";
string sqlType = "";
if (dstColNodes[i].TypeConversion.HasValue)
{
switch (dstColNodes[i].TypeConversion.Value)
{
case Ast.TypeConversion.Integer: sqlType = "integer"; break;
case Ast.TypeConversion.Real: sqlType = "real"; break;
default: sqlType = "text"; break;
}
}
columnDefs.Add($"{name.DoubleQuote()} {sqlType}");
}
}
}
var columnDefsList = string.Join(", ", columnDefs);
var sql = $"CREATE {(temporaryTable ? "TEMPORARY" : "")} TABLE IF NOT EXISTS " +
$"{dstTableName.DoubleQuote()} ({columnDefsList})";
notebook.Execute(sql);
if (truncateExistingTable)
{
notebook.Execute($"DELETE FROM {dstTableName.DoubleQuote()}");
}
}
public static void InsertDataRows(IEnumerable <object[]> rows, string[] dstColNames,
Ast.ImportColumn[] dstColNodes, string dstTableName, IfConversionFails ifConversionFails, INotebook notebook)
{
var dstColCount = dstColNames.Where(x => x != null).Count();
var insertSql = GetInsertSql(dstTableName, dstColCount, numRows: 1);
var insertArgs = new List <object>();
foreach (var row in rows)
{
insertArgs.Clear();
bool skipRow = false;
for (int j = 0; j < Math.Min(row.Length, dstColNames.Length); j++)
{
if (dstColNames[j] == null)
{
continue; // column not chosen to be imported
}
var originalValue = row[j];
var typeConversion = dstColNodes[j].TypeConversion ?? Ast.TypeConversion.Text;
object converted;
bool error = !TryParseValue(originalValue, typeConversion, out converted);
if (!error)
{
insertArgs.Add(converted);
}
else if (ifConversionFails == IfConversionFails.ImportAsText)
{
insertArgs.Add(originalValue.ToString());
}
else if (ifConversionFails == IfConversionFails.SkipRow)
{
skipRow = true;
}
else if (ifConversionFails == IfConversionFails.Abort)
{
throw new Exception($"Failed to parse input value as type \"{typeConversion}\". Value: \"{originalValue}\".");
}
else
{
throw new Exception($"Internal error: unknown value for IF_CONVERSION_FAILS: \"{ifConversionFails}\".");
}
}
if (!skipRow)
{
while (insertArgs.Count < dstColCount)
{
insertArgs.Add(null);
}
notebook.Execute(insertSql, insertArgs);
}
}
}
private static void WithTransactionCore(INotebook notebook, Action action, bool rollback)
{
var didBeginTransaction = false;
if (!notebook.IsTransactionActive())
{
notebook.Execute("BEGIN");
didBeginTransaction = true;
}
try {
action();
if (didBeginTransaction)
{
notebook.Execute(rollback ? "ROLLBACK" : "COMMIT");
}
} catch {
if (didBeginTransaction)
{
notebook.Execute("ROLLBACK");
}
throw;
}
}
public static void WithTransaction(INotebook notebook, Action action)
{
bool didBeginTransaction = false;
if (!notebook.IsTransactionActive())
{
notebook.Execute("BEGIN");
didBeginTransaction = true;
}
try {
action();
if (didBeginTransaction)
{
notebook.Execute("COMMIT");
}
} catch {
if (didBeginTransaction)
{
notebook.Execute("ROLLBACK");
}
throw;
}
}