public void Can_generate_csv()
{
var data = new List <string[]>
{
new[] { "A number", "another number here", "#¤%i" },
new[] { "1", "2", "3" },
new[] { "2", "12", "14" },
new[] { "3", "12", "13" },
new[] { "4", "2", "3" }
};
var csvSettings = new CsvSettings(',')
{
HasHeader = true
};
var ctypes = new CsvColumnTypes(data, csvSettings);
var tableName = this.DataStorage.SaveData(new IntPtr(10), data, ctypes);
var result = this.DataStorage.ExecuteQueryToDataTable("SELECT * FROM " + tableName, new IntPtr(10));
Assert.AreEqual("Anumber", result.Columns[0].ColumnName);
string csv;
using (var memStream = new MemoryStream())
{
var lookup = this.DataStorage.GetUnsafeColumnMaps(new IntPtr(10));
csvSettings.GenerateToStream(result, memStream, lookup);
csv = Encoding.UTF8.GetString(memStream.GetBuffer());
}
Assert.IsTrue(csv.StartsWith("A number,another number here,#¤%i"));
}
private void Parse(CsvSettings csvSettings, DiagnosticTimer watch, string text, IntPtr bufferId)
{
var data = csvSettings.Parse(text);
watch.Checkpoint("Parse");
var columnTypes = new CsvColumnTypes(data, csvSettings);
try
{
Main.DataStorage.SaveData(bufferId, data, columnTypes);
}
catch (Exception ex)
{
this.ErrorMessage("Error when saving data to database:\n" + ex.Message);
return;
}
watch.Checkpoint("Saved to DB");
this.UiThread(() => txbQuery.Text = "SELECT * FROM THIS");
Execute(bufferId, watch);
var diagnostic = watch.LastCheckpoint("Resize");
Trace.TraceInformation(diagnostic);
if (Main.Settings.DebugMode)
{
this.Message(diagnostic);
}
}
public void CanReadFiles()
{
var data = CsvSettings.Semicolon.Parse(File.ReadAllText(@"TestFiles\Headers.csv")).ToList();
var result = new CsvColumnTypes(data, null);
Assert.AreEqual(true, result.HasHeader);
}
public void CanDetectColumnTypes()
{
var data = CsvSettings.Comma.Parse(File.ReadAllText(@"TestFiles\random100x10.csv")).ToList();
var result = new CsvColumnTypes(data, null);
// AaoboInteger,LebhxShortString,GftghLongString,VnsbqChar,NzgubDate,TuyceDateAndTime,6VrnpkDecimal,AnpcfDecimal,LbncsShortString,QofayDate
Assert.AreEqual(true, result.HasHeader);
Assert.IsTrue(result.Columns[0].DataType == ColumnType.Integer);
Assert.IsTrue(result.Columns[1].DataType == ColumnType.String);
Assert.IsTrue(result.Columns[6].DataType == ColumnType.Decimal);
}
public void DangerousCharsRemoved()
{
var data = new List <string[]>
{
new[] { "Col:3", "muu\\\"", "\r\n" },
new[] { "4", "2", "3", "meh" }
};
var columnTypes = new CsvColumnTypes(data, new CsvSettings {
HasHeader = true
});
Assert.IsTrue(columnTypes.Columns.Select(x => x.Name).SequenceEqual(new[] { "Col3", "muu", "Col32", "Col4" }));
}
public void NamesNotEmpty()
{
var data = new List <string[]>
{
new[] { "Col3", " \t", "" },
new[] { "4", "2", "3", "meh" }
};
var columnTypes = new CsvColumnTypes(data, new CsvSettings {
HasHeader = true
});
Assert.IsTrue(columnTypes.Columns.TrueForAll(x => !string.IsNullOrWhiteSpace(x.Name)));
}
public void CanNameColumnsUniqly()
{
var data = new List <string[]>
{
new[] { "Col3", "Col3", "Col3" },
new[] { "4", "2", "3" }
};
var columnTypes = new CsvColumnTypes(data, new CsvSettings {
HasHeader = true
});
Assert.IsTrue(columnTypes.Columns.Select(x => x.Name).SequenceEqual(new [] { "Col3", "Col32", "Col33" }));
}
public void CanDetectNoHeader()
{
var data = CsvSettings.Comma.Parse(File.ReadAllText(@"TestFiles\random100x10.csv")).ToList();
data.RemoveAt(0);
var result = new CsvColumnTypes(data, null);
Assert.AreEqual(false, result.HasHeader);
Assert.IsTrue(result.Columns[0].DataType == ColumnType.Integer);
Assert.IsTrue(result.Columns[1].DataType == ColumnType.String);
Assert.IsTrue(result.Columns[6].DataType == ColumnType.Decimal);
}
private static void PerformanceTest()
{
var timer = new DiagnosticTimer();
var data = new List <string[]>();
var row = new[] { "12,34", "string", "321.23" };
for (int i = 0; i < 2000000; i++)
{
data.Add(row);
}
timer.Checkpoint("data creation");
var result = new CsvColumnTypes(data, null);
Console.WriteLine(timer.LastCheckpoint("Anlyzed"));
Console.WriteLine(result);
Console.WriteLine("Column 1: " + result.Columns[0].DataType);
}
public void SaveToDbAndReadItBack()
{
var data = new List <string[]>
{
new[] { "hej", "du", "där" },
new[] { "1", "2", "3" },
new[] { "2", "12", "14" },
new[] { "3", "12", "13" },
new[] { "4", "2", "3" }
};
var ctypes = new CsvColumnTypes(data, new CsvSettings {
HasHeader = false
});
var tableName = this.DataStorage.SaveData(new IntPtr(10), data, ctypes);
var result = this.DataStorage.ExecuteQuery("SELECT * FROM " + tableName, false);
this.AssertDataEqual(data, result);
}
public void SaveDecimalsToDbAndReadItBack()
{
Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("en-US");
var data = new List <string[]>
{
new[] { "hej", "du", "där" },
new[] { "1", "2,44", "3" },
new[] { "2", "12,300", "14" },
new[] { "3", "12,000", "13" },
new[] { "4", "2,010", "3" }
};
var ctypes = new CsvColumnTypes(data, new CsvSettings {
HasHeader = true
});
var tableName = this.DataStorage.SaveData(new IntPtr(10), data, ctypes);
var result = this.DataStorage.ExecuteQuery("SELECT * FROM " + tableName, true);
this.AssertDataEqualAfterTypeConversion(data, result);
}
public void PerformanceTest()
{
var timer = new DiagnosticTimer();
var data = new List <string[]>();
var row = new[] { "12,34", "string" };
for (int i = 0; i < 1000000; i++)
{
data.Add(row);
}
timer.Checkpoint("data creation");
var result = new CsvColumnTypes(data, null);
Console.WriteLine(timer.LastCheckpoint("Anlyzed"));
Assert.AreEqual(false, result.HasHeader);
Assert.AreEqual(ColumnType.Decimal, result.Columns[0].DataType);
Assert.AreEqual(ColumnType.String, result.Columns[1].DataType);
}
public void CanHandleOneRowWithMoreData()
{
var data = new List <string[]>
{
new[] { "hej", "du", "där" },
new[] { "1", "2", "3" },
new[] { "2", "12", "14", "extra" },
new[] { "3", "12", "13" },
new[] { "4", "2", "3" }
};
var columnTypes = new CsvColumnTypes(data, null);
this.DataStorage.SaveData(new IntPtr(11), data, columnTypes);
this.DataStorage.SetActiveTab(new IntPtr(11));
var result = this.DataStorage.ExecuteQuery("SELECT * FROM this", true);
Assert.AreEqual(5, result.Count);
Assert.IsTrue(result.TrueForAll(x => x.Length == 4));
}
public void Generated_csv_get_safe_headers_when_setting_off()
{
var dataStorage = new SQLiteDataStorage();
Main.Settings = new Settings {
UseOriginalColumnHeadersOnGeneratedCsv = false
};
var data = new List <string[]>
{
new[] { "A number", "another number here", "#¤%i" },
new[] { "1", "2", "3" },
new[] { "2", "12", "14" },
new[] { "3", "12", "13" },
new[] { "4", "2", "3" }
};
var csvSettings = new CsvSettings(',')
{
HasHeader = true
};
var ctypes = new CsvColumnTypes(data, csvSettings);
var tableName = dataStorage.SaveData(new IntPtr(10), data, ctypes);
var result = dataStorage.ExecuteQueryToDataTable("SELECT * FROM " + tableName, new IntPtr(10));
Assert.AreEqual("Anumber", result.Columns[0].ColumnName);
string csv;
using (var memStream = new MemoryStream())
{
csvSettings.GenerateToStream(result, memStream);
csv = Encoding.UTF8.GetString(memStream.GetBuffer());
}
Assert.IsTrue(csv.StartsWith("Anumber,anothernumberhere,i"));
}
public void CanHandleNoHeaderDetectionWithDifferentRowLengths()
{
var data = new List <string[]>
{
new[] { "5", "7", "34" },
new[] { "1", "2", "3", "12.2" },
new[] { "2", "12", "14" },
new[] { "3", "12", "13" },
new[] { "4", "2", "3" }
};
var columnTypes = new CsvColumnTypes(data, null);
this.DataStorage.SaveData(new IntPtr(11), data, columnTypes);
this.DataStorage.SetActiveTab(new IntPtr(11));
var result = this.DataStorage.ExecuteQuery("SELECT * FROM this", true);
Assert.AreEqual(6, result.Count);
Assert.AreEqual(result[0][3], "Col4");
Assert.IsNull(result[1][3]);
Assert.IsNotNull(result[2][3]);
Assert.IsNull(result[3][3]);
}
protected void SaveUnsafeColumnNames(IntPtr bufferId, CsvColumnTypes columnTypes)
{
this.UnsafeColumnNames[bufferId] = columnTypes.ColumnUnsafeNames;
}
private void Parse(CsvSettings csvSettings, DiagnosticTimer watch, TextReader text, IntPtr bufferId)
{
IEnumerable <string[]> data;
try
{
data = csvSettings.Parse(text);
}
catch (Exception e)
{
this.ErrorMessage("Error when parsing text:\n" + e.Message);
return;
}
watch.Checkpoint("Parse");
var count = 0;
try
{
var first = true;
const int partitionSize = DataSettings.ChunkSize;
foreach (var chunk in data.Partition(partitionSize))
{
if (first)
{
var wholeChunk = chunk.ToList();
var columnTypes = new CsvColumnTypes(wholeChunk, csvSettings);
Main.DataStorage.SaveData(bufferId, wholeChunk, columnTypes);
first = false;
watch.Checkpoint("Saved first chunk to DB");
}
else
{
count += partitionSize;
var msg = $"Read lines: {count}";
this.UiThread(() => this.txbQuery.Text = msg);
Main.DataStorage.SaveMore(bufferId, chunk);
}
}
}
catch (Exception ex)
{
this.ErrorMessage("Error when saving data to database:\n" + ex.Message);
return;
}
watch.Checkpoint("Saved to DB");
var selectQuery = "SELECT * FROM THIS";
if (count > 10000)
{
selectQuery = Main.DataStorage.CreateLimitedSelect(10000);
}
this.UiThread(() => this.txbQuery.Text = selectQuery);
this.Execute(bufferId, watch);
var diagnostic = watch.LastCheckpoint("Resize");
Trace.TraceInformation(diagnostic);
if (Main.Settings.DebugMode)
{
this.Message(diagnostic);
}
}
public override string SaveData(IntPtr bufferId, List <string[]> data, CsvColumnTypes columnTypes)
{
var tableName = GetOrAllocateTableName(bufferId);
// Create SQL by string concat - look out for SQL injection! (although rather harmless since it's all your own data)
var createQuery = new StringBuilder("CREATE TABLE [" + tableName + "] (");
var first = true;
var columns = columnTypes.Columns.Count;
foreach (var column in columnTypes.Columns)
{
if (first)
{
first = false;
}
else
{
createQuery.Append(", ");
}
if (Main.Settings.DetectDbColumnTypes)
{
createQuery.Append('[').Append(column.Name).Append("] ");
createQuery.Append(ToLocalType(column));
createQuery.Append(column.Nullable ? " NULL" : " NOT NULL");
}
else
{
var stringSize = Math.Max(1, column.MaxSize);
createQuery.AppendFormat("[{0}] NVARCHAR({1})", column.Name, stringSize > 2000?"MAX":stringSize.ToString());
}
}
createQuery.Append(")");
ExecuteNonQuery(createQuery.ToString());
// Convert to datatable - bulk insert via datatable is fast
var table = new DataTable();
table.Columns.AddRange(columnTypes.Columns.Select(r => new DataColumn(r.Name)).ToArray());
var datalist = columnTypes.HasHeader ? data.Skip(1) : data;
foreach (var row in datalist)
{
var objects = new object[columns];
for (var j = 0; j < row.Length; j++)
{
objects[j] = columnTypes.Columns[j].Parse(row[j]);
}
table.Rows.Add(objects);
}
Trace.TraceInformation($"Converted {table.Rows.Count} rows to data table");
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
var transaction = connection.BeginTransaction();
using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
{
bulkCopy.BatchSize = 100;
bulkCopy.DestinationTableName = tableName;
try
{
bulkCopy.WriteToServer(table);
}
catch (Exception ex)
{
Trace.TraceError($"Error in MSSQL bulk copy: {ex.Message}");
transaction.Rollback();
connection.Close();
throw;
}
}
transaction.Commit();
}
SaveUnsafeColumnNames(bufferId, columnTypes);
return(tableName);
}
public abstract string SaveData(IntPtr bufferId, List <string[]> data, CsvColumnTypes columnTypes);
protected void SaveUnsafeColumnNames(IntPtr bufferId, CsvColumnTypes columnTypes)
{
UnsafeColumnNames[bufferId] = columnTypes.Columns.Where(c => c.CreationString != c.Name)
.ToDictionary(c => c.Name, c => c.CreationString);
}
/// <summary>
/// Saves parsed data into SQLite database.
/// This function currently does a little too much, it detects headers and column types (which it then pretty much ignore)
/// </summary>
/// <param name="bufferId"></param>
/// <param name="data"></param>
/// <param name="columnTypes"></param>
/// <returns></returns>
public override string SaveData(IntPtr bufferId, List <string[]> data, CsvColumnTypes columnTypes)
{
string tableName = GetOrAllocateTableName(bufferId);
// Create SQL by string concat - look out for SQL injection! (although rather harmless since it's all your own data)
var createQuery = new StringBuilder("CREATE TABLE [" + tableName + "] (");
bool first = true;
foreach (var column in columnTypes.Columns)
{
if (first)
{
first = false;
}
else
{
createQuery.Append(", ");
}
if (Main.Settings.DetectDbColumnTypes)
{
createQuery.Append('[').Append(column.Name).Append("] ");
switch (column.DataType)
{
case ColumnType.Empty:
createQuery.Append(" CHAR");
break;
case ColumnType.Integer:
createQuery.Append(" INT");
break;
case ColumnType.Decimal:
createQuery.Append(" FLOAT");
break;
case ColumnType.String:
createQuery.Append(" CHAR");
break;
}
createQuery.Append(column.Nullable ? " NULL" : " NOT NULL");
}
else
{
createQuery.AppendFormat("[{0}] CHAR", column.Name);
}
}
createQuery.Append(")");
ExecuteNonQuery("BEGIN EXCLUSIVE");
ExecuteNonQuery(createQuery.ToString());
ExecuteNonQuery("END");
var columns = columnTypes.Columns.Count;
var insertQuery = new StringBuilder("INSERT INTO ");
insertQuery.Append(tableName);
insertQuery.Append(" VALUES (?");
for (int i = 1; i < columns; i++)
{
insertQuery.Append(",?");
}
insertQuery.Append(")");
ExecuteNonQuery("BEGIN EXCLUSIVE");
var stmt = new SQLiteVdbe(Db, insertQuery.ToString());
first = true;
foreach (var stringse in data)
{
if (first)
{
first = false;
if (columnTypes.HasHeader)
{
continue;
}
}
stmt.Reset();
int index = 0;
foreach (var s in stringse)
{
stmt.BindText(++index, s);
}
while (index < columns)
{
stmt.BindText(++index, null);
}
stmt.ExecuteStep();
}
stmt.Close();
ExecuteNonQuery("END");
SaveUnsafeColumnNames(bufferId, columnTypes);
return(tableName);
}
