private void Analyze(bool silent)
{
var watch = new DiagnosticTimer();
var bufferId = NotepadPPGateway.GetCurrentBufferId();
var textLength = PluginBase.CurrentScintillaGateway.GetTextLength();
var text = PluginBase.CurrentScintillaGateway.GetTextRange(0, Math.Min(100000, textLength));
watch.Checkpoint("GetText");
var csvSettings = CsvAnalyzer.Analyze(text);
if (csvSettings.Separator == '\0' && csvSettings.FieldWidths == null)
{
if (silent)
{
return;
}
var askUserDialog = new ParseSettings(csvSettings);
this.UiThread(() => askUserDialog.ShowDialog());
if (askUserDialog.DialogResult != DialogResult.OK)
{
return;
}
csvSettings = askUserDialog.Settings;
}
watch.Checkpoint("Analyze");
using (var sr = ScintillaStreams.StreamAllText())
{
this.Parse(csvSettings, watch, sr, bufferId);
}
}
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);
}
}
private static void PerfTestCsvParsers()
{
// Prepare huge csv
var mm = new MemoryStream();
var sw = new StreamWriter(mm);
GenerateCsv(20, 200000, sw);
mm.Position = 0;
var sr = new StreamReader(mm, Encoding.UTF8, false, 1024 * 16, true);
var count = 0;
CsvSettings csvSettings = new CsvSettings(',');
var timer = new DiagnosticTimer();
//foreach (var line in csvSettings.ParseVB(sr))
// count++;
//timer.Checkpoint($"VB ({count})");
mm.Position = 0;
count = 0;
timer.Checkpoint("NOP");
foreach (var line in csvSettings.ParseStandard(sr))
{
count++;
}
timer.Checkpoint($"Standard ({count})");
mm.Position = 0;
count = 0;
timer.Checkpoint("NOP");
foreach (var line in csvSettings.ParseRaw(sr))
{
count++;
}
timer.Checkpoint($"ParseRaw ({count})");
mm.Position = 0;
count = 0;
timer.Checkpoint("NOP");
foreach (var line in csvSettings.ParseRawBuffered(sr))
{
count++;
}
var result = timer.LastCheckpoint($"ParseRawBuffered ({count})");
Console.WriteLine(result);
}
/// <summary>
/// This tests the SQLite in-memory DB by creating some data and then selecting it
/// </summary>
public static void TestDatabase()
{
var watch = new DiagnosticTimer();
var db = new SQLiteDatabase(":memory:");
watch.Checkpoint("Create DB");
db.ExecuteNonQuery("CREATE TABLE Root (intIndex INTEGER PRIMARY KEY, strIndex TEXT, nr REAL)");
watch.Checkpoint("Create table 1");
db.ExecuteNonQuery("CREATE TABLE This (intIndex INTEGER PRIMARY KEY, strIndex TEXT, nr REAL)");
watch.Checkpoint("Create table 2");
db.ExecuteNonQuery("CREATE INDEX RootStrIndex ON Root (strIndex)");
const string insertCommand = "INSERT INTO Root VALUES (?,?,?)";
int i;
var stmt = new SQLiteVdbe(db, insertCommand);
long key = 1999;
for (i = 0; i < 10000; i++)
{
key = (3141592621L * key + 2718281829L) % 1000000007L;
stmt.Reset();
stmt.BindLong(1, key);
stmt.BindText(2, key.ToString());
stmt.BindDouble(3, 12.34);
stmt.ExecuteStep();
}
stmt.Close();
watch.Checkpoint("Insert 10000 rows");
i = 0;
var c1 = new SQLiteVdbe(db, "SELECT * FROM Root ORDER BY intIndex LIMIT 5000");
while (c1.ExecuteStep() != Sqlite3.SQLITE_DONE)
{
long intKey = c1.Result_Long(0);
key = intKey;
i += 1;
}
c1.Close();
var diagnostic = watch.LastCheckpoint("Select 5000 sorted rows");
MessageBox.Show(diagnostic);
}
private void OnMenuClickCreateNewCSV(object sender, EventArgs e)
{
if (!(this.dataGrid.DataSource is DataTable table))
{
MessageBox.Show("No results available to convert");
return;
}
var initialSettings = this._lastGenerateSettings ?? new CsvSettings(Main.Settings.DefaultSeparator.Unescape(),
Main.Settings.DefaultQuoteChar, '\0', true);
var settingsDialog = new ParseSettings(initialSettings)
{
btnReparse = { Text = "&Ok" },
MainLabel = { Text = "How should the CSV be generated?" },
hasHeaderCheckbox = { ThreeState = false, Text = "Create header row" },
txbCommentChar = { Visible = false },
CommentCharLabel = { Visible = false },
};
if (settingsDialog.ShowDialog() == DialogResult.Cancel)
{
return;
}
var settings = settingsDialog.Settings;
var watch = new DiagnosticTimer();
try
{
// Create new tab for results
Win32.SendMessage(PluginBase.nppData._nppHandle, (uint)NppMsg.NPPM_MENUCOMMAND, 0, NppMenuCmd.IDM_FILE_NEW);
watch.Checkpoint("New document created");
var headerLookup = Main.Settings.UseOriginalColumnHeadersOnGeneratedCsv &&
table.ExtendedProperties.ContainsKey("bufferId") &&
table.ExtendedProperties["bufferId"] is IntPtr bufferId
? Main.DataStorage.GetUnsafeColumnMaps(bufferId)
: null;
using (var stream = new BlockingStream(10))
{
var producer = stream.StartProducer(s => settings.GenerateToStream(this.dataGrid.DataSource as DataTable, s, headerLookup));
var consumer = stream.StartConsumer(s => PluginBase.CurrentScintillaGateway.AddText(s));
Task.WaitAll(producer, consumer);
}
}
catch (Exception ex)
{
Trace.TraceInformation("CSV gen: Exception: " + ex.GetType().Name + " - " + ex.Message);
}
this._lastGenerateSettings = settings;
Trace.TraceInformation(watch.LastCheckpoint("CSV Done"));
}
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 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);
}
private void Execute(IntPtr bufferId, DiagnosticTimer watch)
{
Main.DataStorage.SetActiveTab(bufferId);
watch.Checkpoint("Switch buffer");
var query = this.txbQuery.Text;
List <string[]> toshow;
try
{
toshow = Main.DataStorage.ExecuteQuery(query, true);
}
catch (Exception)
{
this.Message("Could not execute query", Resources.Title_CSV_Query_Error);
return;
}
watch.Checkpoint("Execute query");
if (toshow == null || toshow.Count == 0)
{
this.Message("Query returned no data", Resources.Title_CSV_Query_Error);
return;
}
var table = new DataTable();
table.ExtendedProperties.Add("query", query);
table.ExtendedProperties.Add("bufferId", bufferId);
// Create columns
foreach (var s in toshow[0])
{
// Column names in a DataGridView can't contain commas it seems
var safeColumnName = s.Replace(",", string.Empty);
if (table.Columns.Contains(safeColumnName))
{
var safePrefix = safeColumnName;
if (safePrefix[safePrefix.Length - 1] >= '0' && safePrefix[safePrefix.Length - 1] <= '9')
{
safePrefix = safePrefix + "_";
}
var c = 2;
do
{
safeColumnName = safePrefix + c++;
} while (table.Columns.Contains(safeColumnName));
}
table.Columns.Add(safeColumnName);
}
// Insert rows
foreach (var row in toshow.Skip(1))
{
table.Rows.Add(row);
}
watch.Checkpoint("Create DataTable");
this.UiThread(() =>
{
this.dataGrid.DataSource = table;
});
watch.Checkpoint("Display");
// Store query in history
if (!this.txbQuery.AutoCompleteCustomSource.Contains(query))
{
this.UiThread(() => this.txbQuery.AutoCompleteCustomSource.Add(query));
if (Main.Settings.SaveQueryCache)
{
using (var writer = File.AppendText(PluginStorage.QueryCachePath))
{
writer.WriteLine(query);
}
}
}
}
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);
}
}