public void FindSqlFiles()
{
LoadMaps();
_logger.Info("Looking for SQLite databases...");
var dir = @"..\..\..\TestFiles";
var foundFiles = 0;
foreach (var fileSystemEntry in Directory.GetFiles(dir, "*", SearchOption.AllDirectories))
{
_logger.Trace($"Checking '{fileSystemEntry}'");
try
{
if (SQLiteFile.IsSQLiteFile(fileSystemEntry))
{
_logger.Info($"\t '{fileSystemEntry}' is a sqlite database");
var fName = Path.GetFileName(fileSystemEntry);
var maps = SQLMap.MapFiles.Values.Where(t =>
string.Equals(t.FileName, fName, StringComparison.InvariantCultureIgnoreCase)).ToList();
var outPath = @"C:\temp\sqlout";
if (Directory.Exists(outPath))
{
Directory.Delete(outPath, true);
}
var baseTime = DateTimeOffset.UtcNow;
Directory.CreateDirectory(outPath);
if (maps.Any())
{
_logger.Info($"Found at least one map for '{fName}'");
foreach (var map in maps)
{
_logger.Info(map);
var dbFactory = new OrmLiteConnectionFactory($"{fileSystemEntry}", SqliteDialect.Provider);
using (var db = dbFactory.Open())
{
_logger.Info($"Verifying database via '{map.IdentifyQuery}'");
var id = db.ExecuteScalar <string>(map.IdentifyQuery);
if (string.Equals(id, map.IdentifyValue, StringComparison.InvariantCultureIgnoreCase) == false)
{
_logger.Warn($"Got value '{id}' from IdentityQuery, but expected '{map.IdentifyValue}'. Queries will not be processed!");
continue;
}
_logger.Info($"Map queries found: {map.Queries.Count:N0}. Processing...");
foreach (var queryInfo in map.Queries)
{
try
{
_logger.Info($"Dumping information for '{queryInfo.Name}'. BaseFilename: {queryInfo.BaseFileName}");
var foo = db.Query <dynamic>(queryInfo.Query);
// var bar = (IDictionary<string, object>) foo.First();
// _logger.Info($"Headers: {string.Join(",",bar.Keys)}");
var outName =
$"{baseTime:yyyyMMddHHmmss}_{queryInfo.BaseFileName}.csv";
var fullOutName = Path.Combine(outPath, outName);
using (var writer = new StreamWriter(new FileStream(fullOutName, FileMode.CreateNew))) //var writer = new StringWriter()
{
using (var csv = new CsvWriter(writer, CultureInfo.InvariantCulture))
{
// csv.WriteDynamicHeader(foo.First());
// csv.NextRecord();
// foreach(IDictionary<string, object> row in foo) {
// Console.WriteLine("row:");
// foreach(var pair in row) {
// Console.WriteLine(" {0} = {1}", pair.Key, pair.Value);
// }
// }
csv.WriteRecords(foo);
//_logger.Info(writer.ToString());
csv.Flush();
writer.Flush();
}
}
}
catch (Exception e)
{
_logger.Error(e.Message);
}
}
}
}
}
foundFiles += 1;
}
}
catch (Exception e)
{
_logger.Error(e);
}
}
foundFiles.Should().Be(1);
}
private void ButtonConvert_Click(object sender, EventArgs e)
{
//Lege einen Buffer für später an
byte[] Buffer = new Byte[4096];
//Frag den Ort ab, an dem die GPX gespeichert werden sollen
FolderBrowserDialog TmpFolderBrowser = new FolderBrowserDialog();
TmpFolderBrowser.ShowDialog();
foreach (object TmpObject in ListBoxOpened.Items)
{
//ZipFile initalisieren zum Entpacken
ZipFile TmpFile = null;
try
{
//Öffne die Datei aus dem Kasten
TmpFile = new ZipFile(TmpObject.ToString());
//Gehe Alle Dateien durch
foreach (ZipEntry TmpEntry in TmpFile)
{
//Bis eine den namen ituser.poi hat
if (TmpEntry.IsFile && TmpEntry.Name == "ituser.poi\0")
{
//Wenn das so ist, dann hole dir einen Stream auf dieses File
Stream TmpFileStream = TmpFile.GetInputStream(TmpEntry);
//erstelle den neuen Dateinamen, indem du den vorhin ausgewählten Pfad nimmst
string TmpFullPath = Path.Combine(TmpFolderBrowser.SelectedPath, "ituser.poi");
//und erstelle die Datei
using (FileStream TmpOutput = File.Create(TmpFullPath))
{
//anschließend kopiere sie aus der zip dorthin und schließe sie
StreamUtils.Copy(TmpFileStream, TmpOutput, Buffer);
TmpOutput.Close();
//Jetzt erstelle ein GPX und SQL Objekt
SQLiteFile sqlitefile = new SQLiteFile(TmpFullPath);
GPX gpxfile = new GPX(Path.Combine(TmpFolderBrowser.SelectedPath,Path.ChangeExtension(Path.GetFileName(TmpObject.ToString()),".gpx")));
//Gpx File erstellen
sqlitefile.SQLiteToGPX(gpxfile);
//und schreiben
gpxfile.WriteGPX();
//TmpOutput löschen
File.Delete(TmpFullPath);
}
}
}
}
finally
{
if (TmpFile != null)
{
TmpFile.IsStreamOwner = true;
TmpFile.Close();
}
}
}
}
private static void ProcessFile(string fileName)
{
_logger.Debug($"Checking if '{fileName}' is a SQLite file");
if (SQLiteFile.IsSQLiteFile(fileName) == false)
{
if (_fluentCommandLineParser.Object.Hunt == false)
{
_logger.Error($"\t'{fileName}' is not a SQLite file! Skipping...");
}
else
{
_logger.Debug($"\t'{fileName}' is not a SQLite file! Skipping...");
}
return;
}
_logger.Debug($"'{fileName}' is a SQLite file!");
if (_fluentCommandLineParser.Object.Dedupe)
{
var sha = File.GetHash(fileName, HashType.SHA1);
if (SeenHashes.Contains(sha))
{
_logger.Error($"Skipping '{fileName}' as a file with SHA-1 '{sha}' has already been processed");
Console.WriteLine();
return;
}
_logger.Debug($"Adding '{fileName}' SHA-1 '{sha}' to seen hashes collection");
SeenHashes.Add(sha);
}
_logger.Warn($"Processing '{fileName}'...");
ProcessedFiles.Add(fileName);
var maps = new List <MapFile>();
if (_fluentCommandLineParser.Object.Hunt)
{
maps = SQLMap.MapFiles.Values.ToList();
}
else
{
//only find maps tht match the db filename
maps = SQLMap.MapFiles.Values.Where(t => string.Equals(t.FileName, Path.GetFileName(fileName),
StringComparison.InvariantCultureIgnoreCase)).ToList();
}
var foundMap = false;
//need to run thru each map and see if we get any IdentityQuery matches
//process each one that matches
foreach (var map in maps)
{
_logger.Debug($"Processing map '{map.Description}' with Id '{map.Id}'");
var dbFactory = new OrmLiteConnectionFactory($"{fileName}", SqliteDialect.Provider);
var baseTime = DateTimeOffset.UtcNow;
using (var db = dbFactory.Open())
{
_logger.Debug($"\tVerifying database via '{map.IdentifyQuery}'");
var id = db.ExecuteScalar <string>(map.IdentifyQuery);
if (string.Equals(id, map.IdentifyValue, StringComparison.InvariantCultureIgnoreCase) == false)
{
_logger.Error($"\tFor map w/ description '{map.Description}', got value '{id}' from IdentityQuery, but expected '{map.IdentifyValue}'. Queries will not be processed!");
continue;
}
//if we find any matches, its not unmatched anymore
foundMap = true;
_logger.Error($"\tMap queries found: {map.Queries.Count:N0}. Processing queries...");
foreach (var queryInfo in map.Queries)
{
_logger.Debug($"Processing query '{queryInfo.Name}'");
try
{
var results = db.Query <dynamic>(queryInfo.Query).ToList();
var outName =
$"{baseTime:yyyyMMddHHmmssffffff}_{map.CSVPrefix}_{queryInfo.BaseFileName}_{map.Id}.csv";
var fullOutName = Path.Combine(_fluentCommandLineParser.Object.CsvDirectory, outName);
if (results.Any() == false)
{
_logger.Warn($"\t '{queryInfo.Name}' did not return any results. CSV will not be saved.");
continue;
}
_logger.Info($"\tDumping '{queryInfo.Name}' to '{fullOutName}'");
using (var writer = new StreamWriter(new FileStream(fullOutName, FileMode.CreateNew)))
{
using (var csv = new CsvWriter(writer, CultureInfo.InvariantCulture))
{
// csv.WriteRecords(results);
foreach (var result in results)
{
result.SourceFile = fileName;
csv.WriteRecord(result);
csv.NextRecord();
}
// csv.WriteComment("");
// csv.NextRecord();
// csv.WriteComment($"SourceFile: {fileName}");
// csv.NextRecord();
csv.Flush();
writer.Flush();
}
}
}
catch (Exception e)
{
_logger.Fatal($"Error processing map '{map.Description}' with Id '{map.Id}' for query '{queryInfo.Name}':");
_logger.Error($"\t{e.Message.Replace("\r\n","\r\n\t")}");
}
}
}
}
if (foundMap == false)
{
_logger.Info($"\tNo maps found for '{fileName}'. Adding to unmatched database list");
UnmatchedDbs.Add(fileName);
}
Console.WriteLine();
}
