static bool SqlQueryFinalParseTests(Config.ConfigSettings appConfig) { var db = OpenDatabase(appConfig); Console.WriteLine($"\r\nDatabase: {db.Name}"); var queryCollection = new string[] { //errors "", "SELECT", "SELECT *", "SELECT * FROM", "SELECT * FROM none", "SELECT * FROM routes WHERE ", "SELECT * FROM routes LIMIT ", "SELECT * FROM routes WHERE route_id ", "SELECT * FROM routes WHERE route_id > ", "SELECT * FROM routes WHERE > 5 LIMIT 2", "SELECT * FROM routes WHERE route_id > LIMIT 2", //this one is because cookoo_id is not a table "SELECT * FROM routes WHERE cookoo_id > 4 LIMIT 2", // "SELECT * FROM routes", "SELECT * FROM routes LIMIT 5", "SELECT * FROM routes WHERE route_id >= 5", "SELECT * FROM routes WHERE route_id >= 5 AND agency_id <> 'NJB'", "SELECT * FROM routes WHERE route_id >= 5 AND agency_id <> 'NJB' LIMIT 5", // "SELECT route_id, agency_id,route_type FROM routes", "SELECT route_id, agency_id,route_type FROM routes LIMIT 2", "SELECT route_id, agency_id,route_type FROM routes WHERE route_id >= 5", "SELECT route_id, agency_id,route_type FROM routes WHERE route_id >= 5 AND agency_id <> 'NJB'", "SELECT route_id, agency_id,route_type FROM routes WHERE route_id >= 5 AND agency_id <> 'NJB' LIMIT 2" }; foreach (var query in queryCollection) { try { Console.WriteLine($"\r\n{new string('-', Console.WindowHeight - 1)}"); Console.WriteLine($"\r\n>{query}"); var queryDb = DbQuery.Parse(query, new CsvDbDefaultValidator(db)); var outQuery = queryDb.ToString(); Console.WriteLine($"\r\n>{outQuery}"); } catch (Exception ex) { Console.WriteLine($"exception: {ex.Message}"); } } return(false); }
static void SqlQueryExecuteTests(Config.ConfigSettings appConfig) { //var sw = new System.Diagnostics.Stopwatch(); //sw.Start(); //var db = OpenDatabase(appConfig); //sw.Stop(); //Console.WriteLine($"\r\nDatabase: {db.Name}"); //Console.WriteLine(">created on {0} ms", sw.ElapsedMilliseconds); //var query = //"SELECT * FROM routes WHERE route_id >= (Int32)5 AND agency_id <> 'NJB' LIMIT 5" // //"SELECT route_id, agency_id,route_type FROM routes WHERE route_id >= 5 AND agency_id <> 'NJB' LIMIT 5" // //"SELECT route_id, agency_id,route_type FROM routes WHERE route_id = 180" // // 180,NJB,74,,3,, // "SELECT * FROM trips WHERE route_id = 204 AND service_id = 5 AND trip_id = 52325" // // 204,5,52325,"810 WOODBRIDGE CENTER-Exact Fare",1,810MX003,6369 // ; //Console.WriteLine($"\r\nIn query: {query}"); //sw.Restart(); //var dbQuery = DbQuery.Parse(query, new CsvDbDefaultValidator(db)); // old one: CsvDb.CsvDbQuery.Parse(db, query); //sw.Stop(); //Console.WriteLine(">parsed on {0} ms", sw.ElapsedMilliseconds); //var outQuery = dbQuery.ToString(); //Console.WriteLine($"\r\nOut query: {outQuery}\r\n"); ////to calculate times //sw.Restart(); //var reader = DbRecordReader.Create(db, dbQuery); //var rows = reader.Rows().ToList(); //reader.Dispose(); //sw.Stop(); //Console.WriteLine(">{rows.Count} row(s) retrieved on {0} ms", sw.ElapsedMilliseconds); ////header //sw.Restart(); //var header = String.Join("|", dbQuery.Select.Header); //Console.WriteLine($"\r\n{header}"); //Console.WriteLine($"{new string('-', header.Length)}"); //foreach (var record in rows) //{ // Console.WriteLine($"{String.Join(",", record)}"); //} //sw.Stop(); //Console.WriteLine("\r\n>row(s) displayed on {0} ms", sw.ElapsedMilliseconds); }
static void CompileDb(Config.ConfigSettings appConfig, string dbname) { var db = OpenDatabase(appConfig, dbName: dbname); var basePath = appConfig.Database.BasePath; var gen = new DbGenerator(db, removeAll: false); gen.CompilePagers(); gen.Compile(); }
static void ShowAllTablesAndColumns(Config.ConfigSettings appConfig) { var db = OpenDatabase(appConfig); Console.WriteLine("switch (tableName)\r\n{"); foreach (var table in db.Tables) { Console.WriteLine($"\tcase \"{table.Name}\":"); Console.WriteLine($"\t\treturn new KeyValuePair<string, string>[] {{ {String.Join(", ", table.Columns.Select(c => $"new KeyValuePair<string, string>(\"{c.Name}\", \"{c.Type}\")"))} }}"); Console.WriteLine($"\t\t\t.Contains(columnName);"); } Console.WriteLine("\tdefault:"); Console.WriteLine("\t\treturn false;"); Console.WriteLine("}"); }
static void GenerateInitialDbData( Config.ConfigSettings appConfig, string dbname, DbSchemaConfigType dbConfig, string zipfile ) { var basePath = appConfig.Database.BasePath; var jsonfilepath = $"{basePath}\\{dbname}\\bin\\__tables.init.json"; var db = CsvDb.CsvDb.CreateFromJson(jsonfilepath, dbConfig); var gen = new DbGenerator(db, $@"{basePath}{zipfile}", removeAll: false); gen.Generate(); }
static void CsvToBin(Config.ConfigSettings appConfig) { var db = OpenDatabase(appConfig, dbName: "data-full"); string tableName = null; Console.WriteLine("enter table name, empty to exit, prefix with dash - to show percentage "); do { Console.Write("\r\ntable: "); tableName = Console.ReadLine(); var sw = new System.Diagnostics.Stopwatch(); sw.Start(); var response = CsvToBinTable(db, tableName); sw.Stop(); if (response) { Console.WriteLine(" ellapsed {0} ms\r\n\t {1}", sw.ElapsedMilliseconds, sw.Elapsed); } } while (!String.IsNullOrWhiteSpace(tableName)); }
static CsvDb.CsvDb OpenDatabase(Config.ConfigSettings appConfig, string dbName = null, bool logTimes = true) { var basePath = appConfig.Database.BasePath; if (string.IsNullOrWhiteSpace(dbName)) { dbName = "data-bin\\"; } string rootPath = $"{basePath}{dbName}"; if (!rootPath.EndsWith('\\')) { rootPath += "\\"; } System.Diagnostics.Stopwatch sw = null; if (logTimes) { sw = new System.Diagnostics.Stopwatch(); sw.Start(); } CsvDb.CsvDb db = null; try { db = new CsvDb.CsvDb(rootPath); if (logTimes) { sw.Stop(); Console.WriteLine(" opened on {0} ms", sw.ElapsedMilliseconds); } } catch (Exception ex) { db = null; Console.WriteLine($"error: {ex.Message}"); } return(db); }