public List <string> GetColumnsFromCsv()
{
var firstLine = "";
using (StreamReader reader = new StreamReader(FileName))
{
firstLine = reader.ReadLine();
}
var columns = StaticHelperClass.SplitString(Configuration.Delimiter, firstLine, 1);
System.Diagnostics.Debug.WriteLine(columns?.Count);
return(columns);
}
public void CreateSingleTable(IColumn[] columns, int ChunkSize)
{
FileStream stream = new FileStream(FileName, FileMode.Open, FileAccess.Read, FileShare.Read, 32768, true);
using (StreamReader reader = new StreamReader(stream))
{
reader.ReadLine();
var table = MakeSingleTable(columns);
int counter = 1;
string line;
Task worker = null;
string[] _columns = columns.Select(c => c.ColumnName).ToArray();
string[] _values = new string[columns.Length];
List <string> queries = new List <string>(ChunkSize);
string[] cachedQueries;
using (var database = new Database(Configuration.DatabasePath))
{
if (!database.HasTables)
{
database.CreateTables(new List <ITable>()
{
table
});
}
List <IData> row = new List <IData>(columns.Length);
while ((line = reader.ReadLine()) != null)
{
var l = StaticHelperClass.SplitString(Configuration.Delimiter, line, 0);
for (var y = 0; y < l.Count; y++)
{
if (row.Count != columns.Length)
{
row.Add(new Data()
{
ColumnIndex = y, Value = l[y]
});
}
else
{
row[y] = new Data()
{
ColumnIndex = y, Value = l[y]
}
};
}
queries.Add(database.InsertDataIntoMainTable(row, table, columns, _columns, _values));
if (queries.Count == ChunkSize)
{
if (worker != null && !worker.IsCompleted)
{
worker.Wait();
}
PushNotification($"Saving {ChunkSize} {counter++}");
cachedQueries = new string[queries.Count];
for (int i = 0; i < queries.Count; i++)
{
cachedQueries[i] = queries[i];
}
worker = Task.Factory.StartNew(() =>
{
database.ExecuteMultipleNonQueriesInATranscaction(cachedQueries);
});
queries = new List <string>(ChunkSize);
}
}
if (worker != null && !worker.IsCompleted)
{
worker.Wait();
}
if (queries.Count > 0)
{
database.ExecuteMultipleNonQueriesInATranscaction(queries);
}
}
}
stream.Dispose();
}
public void ParseFile()
{
IColumn[] columns;
try
{
if (Configuration.Columns == null)
{
var cols = GetColumnsFromCsv();
if (cols != null)
{
var types = GetDataTypeFromCsvColumns();
if (types != null)
{
columns = new Column[cols.Count];
for (var i = 0; i < cols.Count; i++)
{
columns[i] = new Column()
{
ColumnName = cols[i],
ColumnType = types[i],
}
}
;
}
else
{
Observer.OnError(new Exception("No values found in csv file!"));
return;
}
}
else
{
Observer.OnError(new Exception("No columns found in csv file! Please put columns on the first line of file!"));
return;
}
}
else
{
columns = Configuration.Columns.ToArray();
}
LineCount = StaticHelperClass.GetCsvLineCount(FileName);
if (LineCount >= 131072)
{
int multiplier = LineCount / 131072;
if (multiplier > 1)
{
ChunkSize = 65536 * multiplier;
}
else
{
ChunkSize = 65536;
}
}
else if (LineCount < 131072 && LineCount >= 50000)
{
ChunkSize = 16384;
}
else if (LineCount < 50000 && LineCount >= 10000)
{
ChunkSize = LineCount / 4;
}
else
{
ChunkSize = LineCount;
}
PushNotification($"Validating and writing {LineCount} entries to database!\nChunk size is {ChunkSize}.");
if (Configuration.Type == RelationshipType.NoRelationship)
{
SingleTableCreator s = new SingleTableCreator(FileName, Configuration);
s.Subscribe(this);
s.CreateSingleTable(columns, ChunkSize);
}
else if (Configuration.Type == RelationshipType.AutomaticRelationship)
{
MultiTableCreator m = new MultiTableCreator(FileName, Configuration);
m.Subscribe(this);
m.CreateMultiTableDatabase(columns, ChunkSize);
}
PushNotification("Done", true);
}
catch (Exception ex)
{
Observer.OnError(ex);
return;
}
}
private ColumnTypeSQLLITE[] GetDataTypeFromCsvColumns()
{
var result = new List <ColumnTypeSQLLITE>();
var flagDictionary = new Dictionary <int, List <ColumnTypeSQLLITE> >();
using (StreamReader reader = new StreamReader(FileName))
{
reader.ReadLine();
string line;
bool isFirstLine = true;
int accuracy = 0;
if (Configuration.ColumnTypeAccuracy == 0)
{
Configuration.ColumnTypeAccuracy = 1;
}
while ((line = reader.ReadLine()) != null)
{
if (Configuration.ColumnTypeAccuracy != 11)
{
if (Configuration.ColumnTypeAccuracy == accuracy)
{
break;
}
else
{
accuracy++;
}
}
var nonDateIndexes = new List <int>();
var cultureInfo = new CultureInfo("en-US");
var dummyValueDateTime = new DateTime();
var values = StaticHelperClass.SplitString(Configuration.Delimiter, line, 2);
if (values != null)
{
for (int y = 0; y < values.Count; y++)
{
try
{
if (!isFirstLine)
{
if (IsDateTime(values[y], cultureInfo, dummyValueDateTime) && !nonDateIndexes.Contains(y))
{
if (result[y] != ColumnTypeSQLLITE.Numeric)
{
result[y] = ColumnTypeSQLLITE.Numeric;
if (flagDictionary.ContainsKey(y))
{
if (!flagDictionary[y].Contains(ColumnTypeSQLLITE.Numeric))
{
flagDictionary[y].Add(ColumnTypeSQLLITE.Numeric);
}
}
else
{
flagDictionary.Add(y, new List <ColumnTypeSQLLITE>()
{
ColumnTypeSQLLITE.Numeric
});
}
}
}
else if (int.TryParse(values[y], out int res))
{
if (result[y] != ColumnTypeSQLLITE.Integer)
{
result[y] = ColumnTypeSQLLITE.Integer;
if (flagDictionary.ContainsKey(y))
{
if (!flagDictionary[y].Contains(ColumnTypeSQLLITE.Integer))
{
flagDictionary[y].Add(ColumnTypeSQLLITE.Integer);
}
}
else
{
flagDictionary.Add(y, new List <ColumnTypeSQLLITE>()
{
ColumnTypeSQLLITE.Integer
});
}
}
}
else if (double.TryParse(values[y], out double res1))
{
if (result[y] != ColumnTypeSQLLITE.Real)
{
result[y] = ColumnTypeSQLLITE.Real;
if (flagDictionary.ContainsKey(y))
{
if (!flagDictionary[y].Contains(ColumnTypeSQLLITE.Real))
{
flagDictionary[y].Add(ColumnTypeSQLLITE.Real);
}
}
else
{
flagDictionary.Add(y, new List <ColumnTypeSQLLITE>()
{
ColumnTypeSQLLITE.Real
});
}
}
}
else
{
if (result[y] != ColumnTypeSQLLITE.Text)
{
result[y] = ColumnTypeSQLLITE.Text;
if (flagDictionary.ContainsKey(y))
{
if (!flagDictionary[y].Contains(ColumnTypeSQLLITE.Text))
{
flagDictionary[y].Add(ColumnTypeSQLLITE.Text);
}
}
else
{
flagDictionary.Add(y, new List <ColumnTypeSQLLITE>()
{
ColumnTypeSQLLITE.Text
});
}
}
}
}
else
{
if (IsDateTime(values[y], cultureInfo, dummyValueDateTime))
{
result.Add(ColumnTypeSQLLITE.Numeric);
}
if (int.TryParse(values[y], out int res))
{
nonDateIndexes.Add(y);
result.Add(ColumnTypeSQLLITE.Integer);
}
else if (double.TryParse(values[y], out double res1))
{
nonDateIndexes.Add(y);
result.Add(ColumnTypeSQLLITE.Real);
}
else
{
nonDateIndexes.Add(y);
result.Add(ColumnTypeSQLLITE.Text);
}
}
}
catch (ArgumentOutOfRangeException)
{
if (IsDateTime(values[y], cultureInfo, dummyValueDateTime))
{
result.Add(ColumnTypeSQLLITE.Numeric);
}
if (int.TryParse(values[y], out int res))
{
nonDateIndexes.Add(y);
result.Add(ColumnTypeSQLLITE.Integer);
}
else if (double.TryParse(values[y], out double res1))
{
nonDateIndexes.Add(y);
result.Add(ColumnTypeSQLLITE.Real);
}
else
{
nonDateIndexes.Add(y);
result.Add(ColumnTypeSQLLITE.Text);
}
}
}
}
isFirstLine = true;
}
}
// var lines = File.ReadAllLines(csvFilePath);
//flags changes in types
if (result.Count > 0)
{
return(FinesseTypes(result, flagDictionary).ToArray());
}
else
{
return(null);
}
}
//public void CreateMultiTableDatabase(IColumn[] columns, int chunkSize)
//{
// using (var stream = new FileStream(FileName, FileMode.Open, FileAccess.Read, FileShare.Read, 32768, true))
// {
// using (var reader = new StreamReader(stream))
// {
// reader.ReadLine();
// string line;
// Task worker = null;
// var data = new List<IData>(chunkSize);
// bool isRelationshipDone = false;
// var relationshipBuilder = new AutoRelationshipBuilder();
// ICollection<ITable> tables = null;
// Dictionary<IColumn, ITable> relationshipsResult = null;
// var idDictionary = new ConcurrentDictionary<RelationshipKey, int>();
// int id = 0;
// var queries = new List<string>();
// using (var database = new Database(Configuration.DatabasePath))
// {
// while (true)
// {
// if (data.Count < chunkSize)
// {
// line = reader.ReadLine();
// if (line == null)
// break;
// var d = StaticHelperClass.SplitString(Configuration.Delimiter, line, 0);
// for (var i = 0; i < d.Count; i++)
// data.Add(new Data() { ColumnIndex = i, Value = d[i] });
// }
// else if (!isRelationshipDone)
// {
// relationshipsResult = relationshipBuilder
// .MakeRelationships(columns, data, out tables);
// if (tables != null)
// database.CreateTables(tables);
// isRelationshipDone = true;
// if (relationshipsResult != null)
// queries = MakeRelationshipTableQueries(data, relationshipsResult, idDictionary, ref id, columns);
// database.ExecuteMultipleNonQueriesInATranscaction(queries);
// var transferList = new List<IData>(data.Count);
// transferList.AddRange(data);
// worker = Task.Factory.StartNew(() =>
// {
// MakeSQlTable_MultipleTables(transferList, tables.Last(), idDictionary, database, columns);
// });
// data = new List<IData>(chunkSize);
// queries = new List<string>();
// }
// else
// {
// if (worker != null && !worker.IsCompleted)
// worker.Wait();
// worker = Task.Factory.StartNew(() =>
// {
// if (relationshipsResult != null)
// queries = MakeRelationshipTableQueries(data, relationshipsResult, idDictionary, ref id, columns);
// database.ExecuteMultipleNonQueriesInATranscaction(queries);
// queries = new List<string>();
// var transferList = new List<IData>(data.Count);
// transferList.AddRange(data);
// MakeSQlTable_MultipleTables(transferList, tables.Last(), idDictionary, database, columns);
// });
// data = new List<IData>(chunkSize);
// }
// }
// if (data.Count > 0)
// {
// if (relationshipsResult != null)
// queries = MakeRelationshipTableQueries(data, relationshipsResult, idDictionary, ref id, columns);
// database.ExecuteMultipleNonQueriesInATranscaction(queries);
// queries = new List<string>();
// MakeSQlTable_MultipleTables(data, tables.Last(), idDictionary, database, columns);
// }
// }
// }
// }
//}
public void CreateMultiTableDatabase(IColumn[] columns, int chunkSize)
{
using (var stream = new FileStream(FileName, FileMode.Open, FileAccess.Read, FileShare.Read, 32768, true))
{
using (var reader = new StreamReader(stream))
{
reader.ReadLine();
string line;
var calcedChunkSize = (int)Math.Floor((double)chunkSize / columns.Length);
Task worker = null;
var data = new List <IData>(chunkSize);
List <IData> transferData;
bool RelationshipDone = false;
var relationshipBuilder = new AutoRelationshipBuilder();
ICollection <ITable> tables = null;
Dictionary <IColumn, ITable> relationshipsResult = null;
var idSaver = new ConcurrentDictionary <RelationshipKey, int>();
int id = 0;
var queries = new List <string>();
// copy of queries for the task to work with
List <string> q;
using (var database = new Database(Configuration.DatabasePath))
{
while (true)
{
if (data.Count < chunkSize)
{
line = reader.ReadLine();
if (line == null)
{
break;
}
var d = StaticHelperClass.SplitString(Configuration.Delimiter, line, 0);
for (var i = 0; i < d.Count; i++)
{
data.Add(new Data()
{
ColumnIndex = i, Value = d[i]
});
}
}
else
{
transferData = new List <IData>(chunkSize);
if (!RelationshipDone)
{
relationshipsResult = relationshipBuilder
.MakeRelationships(columns, data, out tables);
if (tables != null)
{
database.CreateTables(tables);
}
RelationshipDone = true;
}
if (relationshipsResult != null)
{
var cacheTask = Task.Factory.StartNew(() =>
{
transferData.AddRange(data);
});
queries = MakeRelationshipTableQueries(data, relationshipsResult, idSaver, ref id, columns);
if (!cacheTask.IsCompleted)
{
cacheTask.Wait();
}
}
q = new List <string>(queries.Count);
q.AddRange(queries);
if (worker != null)
{
worker.Wait();
}
worker = Task.Factory.StartNew(() =>
{
database.ExecuteMultipleNonQueriesInATranscaction(q);
MakeSQlTable_MultipleTables(transferData, tables.Last(), idSaver, database, columns);
});
data = new List <IData>(chunkSize);
queries = new List <string>();
}
}
if (data.Count > 0)
{
if (relationshipsResult != null)
{
var relationshipWorkers = new List <Task <List <string> > >();
queries = MakeRelationshipTableQueries(data, relationshipsResult, idSaver, ref id, columns);
}
if (worker != null && !worker.IsCompleted)
{
worker.Wait();
}
database.ExecuteMultipleNonQueriesInATranscaction(queries);
MakeSQlTable_MultipleTables(data, tables.Last(), idSaver, database, columns);
}
else if (worker != null && !worker.IsCompleted)
{
worker.Wait();
}
}
//PushNotification("Cycle");
}
}
}
