public void ShowItemPage <T>(DbColumn column, int offset)
where T : IComparable <T>
{
var indexItem = column.IndexItems <T>();
var page = indexItem[offset];
if (page == null)
{
Console.WriteLine($"Page offset: {offset} is invalid");
return;
}
var handler = DbTableDataReader.Create(Database, column.Table.Name);
int rows = 0;
foreach (var item in page.Items)
{
Console.WriteLine($"[{item.Key}] ({item.Value.Count}) offset value(s)");
//foreach (var ofs in item.Value)
//{
// var row = handler.ReadDbRecord(ofs);
// Console.WriteLine($" {string.Join(", ", row)}");
//}
rows++;
}
Console.WriteLine($" displayed ({rows}) key(s)");
}
public void ShowTableColumnRows <T>(DbColumn column, int count)
where T : IComparable <T>
{
var handler = DbTableDataReader.Create(Database, column.Table.Name);
var offset = 0;
//while (count-- > 0)
{
var row = handler.ReadDbRecord(offset);
Console.WriteLine(String.Join(", ", row));
}
}
static bool SqlQueryExecute()
{
var availableDatabases = new string[]
{
"data-full",
//"data",
//"data-light",
//"data-extra-light",
"data-bin"
};
var sw = new System.Diagnostics.Stopwatch();
CsvDb.CsvDb db = null;
bool IsObjectNull(object obj, string msg, bool testFor = true)
{
if ((obj == null) == testFor)
{
con.WriteLine(msg);
return(true);
}
return(false);
}
object CreateClass(Type type, object[] parameters)
{
if (type == null)
{
return(null);
}
try
{
object obj = Activator.CreateInstance(type, parameters ?? new object[] { });
return(obj);
}
catch (Exception ex)
{
con.WriteLine($"error: {ex.Message}");
return(null);
}
}
//Action displayHelp = () =>
void displayHelp()
{
con.WriteLine(" ┌────────────────────────────────┬────────────────────────────────┬──────────────────────────────────┐");
con.WriteLine(" │ Help h help │ Clear c clear │ Quit q quit │");
con.WriteLine(" ├────────────────────────────────┴────────┬───────────────────────┴──────────────────────────────────┤");
con.WriteLine(" │ Mount database m|use|mount 'db name' │ Display database(s) display │");
con.WriteLine(" │ Kill/close database k kill │ Display Tables Info display /tables │");
con.WriteLine(" │ Search Database search │ Display Index Tree Structure │");
con.WriteLine(" │ Eexecute Queries execute │ display 'table.column' /i │");
con.WriteLine(" │ Xtreme class x │ Display Index Tree Node Structure │");
con.WriteLine(" │ │ display 'table.column' /n │");
con.WriteLine(" │ │ Non-indexed search │");
con.WriteLine(" │ │ display 'table.column' /oper:> constant │");
con.WriteLine(" │ │ Page display 'table.column' /p /offset:int │");
con.WriteLine(" │ │ Visualize recs display 'table.column' /r count │");
con.WriteLine(" ├─────────────────────────────────────────┴──────────────────────────────────────────────────────────┤");
con.WriteLine(" │ SELECT [*] | [t0.col0, t0.col1,..] | [COUNT|AVG|SUM](col) │");
con.WriteLine(" │ FROM table [t0] │");
con.WriteLine(" │ WHERE │");
con.WriteLine(" │ [INNER|CROSS|(LEFT|RIGHT|FULL) OUTER] JOIN table0 t0 ON expr:<left> oper <right> │");
con.WriteLine(" └────────────────────────────────────────────────────────────────────────────────────────────────────┘");
// ORDER BY
// SELECT * FROM [table] t
// WHERE t.Name == ""
//
// *\t[table]\t[[column],==,[value]>]
//SELECT route_id, rout_short_name FROM routes r
// WHERE r.agency_id == "NJT" AND
// r.route_type == 2
// route_id,route_short_name\t[routes]\t[agency_id],==,"NJT"\tAND\t[route_type],==,2
// SELECT * | column0,column1,...
// | a.agency_id, b.serice_id,...
//
// FROM table [descriptor]
//
// WHERE [descriptor].column = constant AND|OR ...
// SKIP number
// LIMIT number
//
}
System.Reflection.Assembly assembly = null;
var nl = Environment.NewLine;
bool end = false;
//this's the matched rule
CommandArgRulesAction matchedRule = null;
//con.TreatControlCAsInput = true;
var ruleCollection = new CommandArgRules(
new CommandArgRulesAction[]
{
new CommandArgRulesAction(
CommandArgRule.Command("q", "quit"),
() =>
{
end = true;
if (db != null)
{
db.Dispose();
}
}
),
new CommandArgRulesAction(CommandArgRule.Command("h", "help"), () => displayHelp()),
new CommandArgRulesAction(CommandArgRule.Command("c", "clear"), () => con.Clear()),
new CommandArgRulesAction(
CommandArgRule.Command("k", "kill"),
() =>
{
if (!IsObjectNull(db, $" no database to close"))
{
con.WriteLine($" closing database [{db.Name}]");
db.Dispose();
db = null;
}
}
),
new CommandArgRulesAction(
CommandArgRule.Command("m", "mount", "use"),
() =>
{
//m "data-bin"
if (!IsObjectNull(db, $"\r\nplease first unmount current database", testFor: false))
{
if ((db = OpenDatabase(
dbName: matchedRule[1].Arg.GetKey(),
logTimes: true)) != null)
{
con.WriteLine($"\r\nUsing database: {db.Name}{db.IsBinary.IfTrue(" [Binary]")}{db.IsCsv.IfTrue(" [Csv]")}");
}
}
}
).Add(CommandArgRule.KeyTypeAs(CommandArgItemType.Identifier | CommandArgItemType.String)),
new CommandArgRulesAction(
CommandArgRule.Command("s", "search"),
() =>
{
if (!IsObjectNull(db, " there's no database in use"))
{
con.Write(" query >");
var query = con.In.ReadLine();
con.WriteLine();
sw.Restart();
var dbQuery = DbQuery.Parse(query, new CsvDbDefaultValidator(db));
sw.Stop();
con.WriteLine(" query parsed on {0} ms", sw.ElapsedMilliseconds);
con.WriteLine($" {dbQuery}");
var visualizer = new DbVisualizer(
db,
dbQuery,
DbVisualize.Paged |
DbVisualize.UnderlineHeader |
DbVisualize.Framed |
DbVisualize.LineNumbers);
visualizer.Display();
visualizer.Dispose();
}
}
),
new CommandArgRulesAction(
CommandArgRule.Command("execute"),
() =>
{
if (!IsObjectNull(db, " there's no database in use"))
{
con.WriteLine("Execute database queries:\r\n -empty query ends");
string query = null;
bool finish = false;
do
{
con.Write("\r\n query: ");
query = con.ReadLine();
if (!(finish = String.IsNullOrWhiteSpace(query)))
{
try
{
sw.Restart();
var dbQuery = DbQuery.Parse(query, new CsvDbDefaultValidator(db));
sw.Stop();
con.WriteLine(" query parsed on {0} ms", sw.ElapsedMilliseconds);
con.WriteLine($" {dbQuery}");
}
catch (Exception ex)
{
con.WriteLine($" error: {ex.Message}");
}
}
} while (!finish);
}
}
),
new CommandArgRulesAction(
CommandArgRule.Command("x"),
() =>
{
if (!IsObjectNull(db, " there's no database in use"))
{
con.Write("\r\n database table as class: ");
var tbleName = con.ReadLine();
DbTable table = db.Table(tbleName);
//
if (table == null)
{
con.WriteLine($"cannot find table [{tbleName}]");
}
else
{
if (assembly == null)
{
assembly = Utils.CreateDbClasses(db);
if (assembly == null)
{
con.WriteLine("Cannot generate database table classes");
}
else
{
con.WriteLine("database table classes generated succesfully!");
}
}
if (assembly != null)
{
//I can compile code once and load assembly at start
// just recompile when database table changes
//var an = System.Reflection.AssemblyName.GetAssemblyName(filePath);
//System.Reflection.Assembly.Load(an);
//AppDomain.CurrentDomain.Load(assembly.GetName());
//get it OK!
//Type type = assembly.GetType($"CsvDb.Dynamic.{tbleName}");
//object obj = Activator.CreateInstance(type);
//this was a test, constructor must be parameterless so CsvHelper can create it
Type dynTbleClass = assembly.GetType($"CsvDb.Dynamic.{tbleName}");
object obj = CreateClass(
dynTbleClass,
new object[] {
//table
}
);
var mthd = dynTbleClass.GetMethod("Link");
mthd.Invoke(obj, new object[]
{
table
});
//now I can use CsvHelper to parse CSV rows using this classes if needed
//don't get it
var classType = Type.GetType($"CsvDb.Dynamic.{tbleName}");
con.WriteLine("ok");
}
}
}
}
),
new CommandArgRulesAction(
CommandArgRule.Command("display"),
() =>
{
//compare
//display "routes.route_id" /oper:>= 250
if (!IsObjectNull(db, " there's no database in use"))
{
var dbTblCol = matchedRule[1].Arg.GetKey();
var operArg = matchedRule[2].Arg as CommandArgKeypair;
var constArg = matchedRule[3].Arg;
DbColumn column = null;
if ((column = db.Index(dbTblCol)) != null &&
operArg.Value.TryParseToken(out TokenType token))
{
var reader = DbTableDataReader.Create(db, column.Table);
object value = column.TypeEnum.ToObject(constArg.Key);
var collection = (IEnumerable <int>)Utils.CallGeneric(
reader, nameof(DbTableDataReader.Compare), column.TypeEnum,
new object[] { column, token, value },
System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance
);
var lista = collection.ToList();
con.WriteLine($" ({lista.Count}) match(es)");
}
}
}
