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 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(); }