Exemple #1
0
        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);
                }
            }
        }
Exemple #2
0
        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()}");
            }
        }
Exemple #3
0
        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);
                }
            }
        }
Exemple #4
0
        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;
            }
        }