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