static void AddToList(string path, string Name, string ShortName, EnginesMode Mode, string Hash)
{
AddToList(path, Name, ShortName, Mode);
_DB_Hash.Add(Hash, Name);
var Info = _DBInfo[Name];
Info.Hash = Hash;
_DBInfo[Name] = Info;
}
/// <summary>
/// Old Method. Kept in for backwards compatability.
/// </summary>
/// <param name="compatability"></param>
/// <returns></returns>
public static string DB(EnginesMode Mode, Primitive Data)
{
int StackIndex = Stack.Add($"Engines.Load.DB({Mode})");
Dictionary <string, string> _Data = new Dictionary <string, string>
{
["URI"] = Data
};
Stack.Exit(StackIndex);
return(DB(Mode, _Data));
}
public static string DB(EnginesMode Mode, Dictionary <string, string> Data) //Tasked with connecting to a Database and adding the DB Connection Name to a list.
{
int StackEntry = Stack.Add($"Engines.Load.DB({Mode})");
switch (Mode)
{
case EnginesMode.SQLITE:
Stack.Exit(StackEntry);
return(DB(Mode, Data, Path.GetFileNameWithoutExtension(Data["URI"])));
default:
throw new NotImplementedException();
}
}
public static List <string> Functions(EnginesMode Mode)
{
switch (Mode)
{
case EnginesMode.SQLITE:
List <string> Func = new List <string> {
"Avg", "Count", "Max", "Min", "Sum", "Total", "Hex", "Length", "Lower", "Round", "Trim", "Upper", "Sqrt", "Abs"
};
Func.Sort();
return(Func);
default:
throw new NotImplementedException();
}
}
public static void MemoryDB(EnginesMode Mode)
{
int StackPointer = Stack.Add($"Engines.Load.MemoryDB({Mode})");
switch (Mode)
{
case EnginesMode.SQLITE:
LDDataBase.Connection = "Data Source=:memory:;Version=3;New=True;";
AddToList("", LDDataBase.ConnectSQLite(""), "SQLITE MEM DB", EnginesMode.SQLITE, "URI=;");
LDDataBase.Connection = string.Empty;
break;
default:
throw new NotImplementedException();
}
Stack.Exit(StackPointer);
}
static void AddToList(string path, string Name, string ShortName, EnginesMode Engine)
{
DatabaseShortname = ShortName;
var Connections = new Connections
{
SQLITE = GetConnection(Name),
};
var Info = new DatabaseInfo
{
ID = Name,
Path = path,
ShortName = ShortName,
Engine = Engine,
Connections = Connections
};
_DBInfo.Add(Name, Info);
}
public static void GetColumnsofTable(string database, string table)
{
int StackPointer = Stack.Add($"Engines.GetSchemaofTable({database},{table})");
if (string.IsNullOrEmpty(database)) //Prevents calls to nonexistent Databases
{
throw new ArgumentException("The Database Paramater cannot be null or empty", database);
}
EnginesMode EngineMode = Engine_Type(database);
switch (EngineMode)
{
case EnginesMode.SQLITE:
_Schema.Clear();
QuerySettings QS = new QuerySettings
{
Database = database,
SQL = Sqlite.GetColumnsOfTable(table),
FetchRecords = true,
User = Language.Localization["App"],
Explanation = Language.Localization["SCHEMA PRIVATE"]
};
Primitive QSchema = Query(QS);
_Schema = Sqlite.GetColumnsOfTable(QSchema);
break;
default:
throw new NotImplementedException();
}
Schema = _Schema.ToPrimitiveArray();
OnGetColumnsofTable?.Invoke(null, EventArgs.Empty);
Stack.Exit(StackPointer);
}
public static string DB(EnginesMode Mode, Dictionary <string, string> Data, string ShortName)
{
//MAKE SURE The CurrentMode is always currently changed.
int StackPointer = Stack.Add($"Engines.Load.DB({Mode},{ShortName})");
string HashCode = Data.ToPrimitiveArray();
//If DB is already in the list...
if (_DB_Hash.ContainsKey(HashCode))
{
CurrentDatabase = _DB_Hash[HashCode];
DatabaseShortname = ShortName;
Stack.Exit(StackPointer);
return(CurrentDatabase);
}
//New Database creation code
switch (Mode)
{
case EnginesMode.MySQL:
CurrentDatabase = LDDataBase.ConnectMySQL(Data["Server"], Data["User"], Data["Password"], Data["Database"]);
_DB_Hash.Add(HashCode, CurrentDatabase);
Stack.Exit(StackPointer);
return(CurrentDatabase);
case EnginesMode.ODBC:
CurrentDatabase = LDDataBase.ConnectOdbc(Data["Driver"], Data["Server"], Data["Port"], Data["User"], Data["Password"], Data["Option"], Data["Database"]);
_DB_Hash.Add(HashCode, CurrentDatabase);
Stack.Exit(StackPointer);
return(CurrentDatabase);
case EnginesMode.OLEDB:
CurrentDatabase = LDDataBase.ConnectOleDb(Data["Provider"], Data["Server"], Data["Database"]);
_DB_Hash.Add(HashCode, CurrentDatabase);
Stack.Exit(StackPointer);
return(CurrentDatabase);
case EnginesMode.SQLITE:
//Add Custom Password handling code here
if (Data.ContainsKey("Password"))
{
LDDataBase.Connection = $"Data Source{Data["URI"]};Password={Data["Password"]}";
}
if (Directory.Exists(Path.GetDirectoryName(Data["URI"])))
{
string Database = LDDataBase.ConnectSQLite(Data["URI"]);
AddToList(Data["URI"], Database, ShortName, EnginesMode.SQLITE);
Settings.Save();
CurrentDatabase = Database;
_DB_Hash.Add(HashCode, CurrentDatabase);
DB_Info[Database].Connections.SQLITE.Bind();
Stack.Exit(StackPointer);
return(Database);
}
Stack.Exit(StackPointer);
return(null);
case EnginesMode.SQLSERVER:
CurrentDatabase = LDDataBase.ConnectSqlServer(Data["Server"], Data["Database"]);
_DB_Hash.Add(HashCode, CurrentDatabase);
Stack.Exit(StackPointer);
return(CurrentDatabase);
default:
Stack.Exit(StackPointer);
return("Incorrect Paramters");
}
}
public static void Emulator(EnginesMode Mode, string Database, string SQL, string Username, string Listview) //TODO Implement Emulator atleast for sqlite for DBM
{
//Attempts to emulate some if not all commands of a database engine by aliasing it to SQL
int StackPointer = Stack.Add($"Engines.Emulator({Mode},{Database},{SQL}");
StringBuilder Emulator_Sql = new StringBuilder();
string EmulatorTable = null;
StringComparison Comparison = StringComparison.InvariantCultureIgnoreCase;
switch (Mode)
{
case EnginesMode.SQLITE:
if (SQL.StartsWith(".help", Comparison))
{
//EmulatorTable = "DBM_SQLITE_Help";
List <string> Arguments = new List <string>()
{
".help", ".databases", ".tables", ".index", ".pragma", ".querytimes", ".filesystem $Path", ".stacktrace", ".localizations", ".functions"
};
Dictionary <string, string> Description = new Dictionary <string, string>
{
{ ".help", "Displays this table.Note: The following commands are being interpreted by the DBM CLI Emulator not the SQLITE CLI." },
{ ".databases", "Displays a list of connected databases." },
{ ".tables", "Lists all tables and views in the current database." },
{ ".index", "Lists all the indexes in the current database." },
{ ".pragma", "Lists most pragma settings for sqlite db." },
{ ".querytimes", "Lists all queries executed by the program and the time in miliseconds." },
{ ".filesystem $Path", "Lists all the folders and files in the argumented path. Defaults to MyDocuments if no argument is given." },
{ ".stacktrace", "Lists the functions and methods and the like and the order they were called in." },
{ ".localizations", "List of the key value pairs of the current languages localization" },
{ ".functions", "List of all custom functions." },
{ ".charts", "A List of all charts currentley implemented" }
};
Arguments.Sort();
Emulator_Sql.AppendFormat("CREATE TEMP TABLE {0} (Arguments TEXT,Description TEXT);", EmulatorTable);
for (int i = 0; i < Arguments.Count; i++)
{
Emulator_Sql.AppendFormat("INSERT INTO {0} VALUES ('{1}','{2}');", EmulatorTable, Arguments[i], Description[Arguments[i]]);
}
break;
}
else if (SQL.StartsWith(".databases", Comparison))
{
EmulatorTable = "DBM_SQLITE_Databases";
Emulator_Sql.AppendFormat("CREATE TEMP TABLE {0} (ID INTEGER PRIMARY KEY,NAME TEXT,\"DBM NAME\" TEXT,FILE TEXT);", EmulatorTable);
for (int i = 0; i < DB_Name.Count; i++)
{
Emulator_Sql.AppendFormat("INSERT INTO {0} (NAME,\"DBM NAME\",FILE) VALUES ('{1}','{2}','{3}');", EmulatorTable, DB_Name[i].Replace("'", "''"), DB_ShortName[i].Replace("'", "''"), DB_Path[i].Replace("'", "''"));
}
}
else if (SQL.StartsWith(".tables", Comparison) || SQL.StartsWith(".views", Comparison))
{
EmulatorTable = "DBM_SQLITE_Tables";
Emulator_Sql.AppendFormat("CREATE TEMP TABLE {0} (ID INTEGER PRIMARY KEY,NAME TEXT,Type TEXT);", EmulatorTable);
for (int i = 0; i < Tables.Count; i++)
{
Emulator_Sql.AppendFormat("INSERT INTO {0} (Name,Type) VALUES ('{1}','table');", EmulatorTable, Tables[i].Replace("'", "''"));
}
for (int i = 0; i < Views.Count; i++)
{
Emulator_Sql.AppendFormat("INSERT INTO {0} (Name,Type) VALUES ('{1}','view');", EmulatorTable, Views[i].Replace("'", "''"));
}
}
else if (SQL.StartsWith(".index", Comparison))
{
EmulatorTable = "DBM_SQLITE_Index";
Emulator_Sql.AppendFormat("CREATE TEMP TABLE {0} (ID INTEGER PRIMARY KEY,NAME TEXT,Type TEXT);", EmulatorTable);
for (int i = 0; i < Indexes.Count; i++)
{
Emulator_Sql.AppendFormat("INSERT INTO {0} (Name,Type) VALUES ('{1}','index');", EmulatorTable, Indexes[i].Replace("'", "''"));
}
}
else if (SQL.StartsWith(".pragma", Comparison))
{
EmulatorTable = "DBM_SQLITE_PRAGMA";
Emulator_Sql.AppendFormat("CREATE TEMP TABLE {0} (\"PRAGMA\" TEXT,VALUE TEXT);", EmulatorTable);
List <string> PRAGMA = new List <string>()
{
"Application_ID", "Auto_Vacuum", "Automatic_Index", "Busy_TimeOut", "Cache_Size", "Cache_Spill", "Case_Sensitive_Like", "Cell_Size_Check", "Checkpoint_fullfsync", "data_version", "defer_foreign_keys", "Encoding", "Foreign_Keys", "Freelist_count", "fullfsync", "ignore_check_constraints",
"incremental_vacuum", "integrity_check", "journal_mode", "journal_size_limit", "legacy_file_format", "locking_mode", "max_page_count", "mmap_size", "page_count", "page_size", "query_only", "read_uncommitted", "recursive_triggers", "reverse_unordered_selects", "secure_delete", "soft_heap_limit", "synchronous", "temp_store", "threads", "user_version",
"wal_autocheckpoint", "writable_schema"
};
PRAGMA.Sort();
/*The following were removed due to them displaying 1+ rows of data
* Collation_List
* Compile_Options
* database_list
* wal_checkpoint
* Foreign_Key_Check
*/
for (int i = 0; i < PRAGMA.Count; i++)
{
var QS = new QuerySettings
{
Database = CurrentDatabase,
SQL = $"PRAGMA {PRAGMA[i]};",
ListView = null,
FetchRecords = true,
User = Username,
Explanation = "Emulator"
};
Emulator_Sql.AppendFormat("INSERT INTO {0} VALUES ('{1}','{2}');",
EmulatorTable,
PRAGMA[i].Replace("_", " "),
Query(QS)[1][PRAGMA[i]]);
}
}
else if (SQL.StartsWith(".filesystem", Comparison))
{
try
{
string _Path = SQL.Substring(".filesystem".Length);
if (string.IsNullOrWhiteSpace(_Path))
{
_Path = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
}
List <string> DirectoryList = new List <string>(System.IO.Directory.EnumerateDirectories(_Path));
List <string> FileList = new List <string>(System.IO.Directory.EnumerateFiles(_Path));
EmulatorTable = "DBM_SQLITE_Filesystem";
Emulator_Sql.AppendFormat("CREATE TEMP TABLE {0} (Name TEXT,\"Date Modified\" TEXT,TYPE TEXT,SIZE INT);", EmulatorTable);
for (int i = 0; i < DirectoryList.Count; i++)
{
string Directory = DirectoryList[i];
DirectoryInfo DI = new DirectoryInfo(Directory);
Emulator_Sql.AppendFormat("INSERT INTO {0} VALUES ('{1}','{2}','File Folder',NULL);", EmulatorTable, Directory.Replace("'", "''"), DI.LastWriteTime.ToString("yyyy-MM-dd HH:mm:ss"));
}
for (int i = 0; i < FileList.Count; i++)
{
string File = FileList[i];
FileInfo FI = new FileInfo(File);
Emulator_Sql.AppendFormat("INSERT INTO {0} VALUES ('{1}','{2}','{3}','{4}');", EmulatorTable, File.Replace("'", "''"), FI.LastWriteTime.ToString("yyyy-MM-dd HH:mm:ss"), FI.Extension, FI.Length);
}
}
catch (Exception ex)
{
Events.LogMessage(ex.Message, Language.Localization["System"]);
}
}
else if (SQL.StartsWith(".querytimes", Comparison))
{
EmulatorTable = "DBM_SQLITE_QueryTimes";
Emulator_Sql.AppendFormat("CREATE TEMP TABLE {0} (ID INTEGER PRIMARY KEY,SQL TEXT,\"Execution Time (ms)\" INTEGER,Explanation TEXT,User TEXT,\"Start Time (UTC)\" TEXT,Cache TEXT);", EmulatorTable);
int ii = 0;
for (int i = 0; i < _Type_Referer.Count; i++)
{
if (_Type_Referer[i] == Type.Query)
{
Emulator_Sql.AppendFormat("INSERT INTO {0} VALUES('{1}','{2}','{3}','{4}','{5}','{6}','{7}');", EmulatorTable, ii, LastQuery[ii].Replace("'", "''"), _Timer[i], _Explanation[i], _User[i], _UTC_Start[i], _CacheStatus[ii]);
ii++;
}
#if DEBUG
else
{
Console.WriteLine("#{0} is a {1} with a time of {2}(ms)", i, _Type_Referer[i], _Timer[i]);
}
#endif
}
}
else if (SQL.StartsWith(".stacktrace", Comparison))
{
//"hh:mm:ss ffffff"
EmulatorTable = "DBM_SQLITE_StackTrace";
Emulator_Sql.AppendFormat("CREATE TEMP TABLE {0} (ID INTEGER PRIMARY KEY,Item TEXT,\"Start Time (UTC)\" TEXT,\"Exit Time (UTC)\" TEXT,\"Duration (ms)\" TEXT);", EmulatorTable);
for (int i = 0; i < Stack.StackEntries.Count; i++)
{
var SE = Stack.StackEntries[i];
Emulator_Sql.AppendFormat("INSERT INTO {0} VALUES('{1}','{2}','{3}','{4}','{5}');", EmulatorTable, i,
SE.Trace,
SE.StartTime.ToString("hh:mm:ss ffffff"),
SE.ExitTime.ToString("hh:mm:ss ffffff"),
SE.Duration.TotalMilliseconds
);
}
}
else if (SQL.StartsWith(".localization", Comparison))
{
EmulatorTable = "DBM_SQLITE_Localizations";
Emulator_Sql.AppendFormat("CREATE TEMP TABLE {0} (ID INTEGER PRIMARY KEY,KEY TEXT,VALUE TEXT);", EmulatorTable);
foreach (KeyValuePair <string, string> entry in Language.Localization)
{
Emulator_Sql.AppendFormat("INSERT INTO {0} (Key,Value) VALUES('{1}','{2}');", EmulatorTable, entry.Key, entry.Value);
}
}
else if (SQL.StartsWith(".function", Comparison))
{
EmulatorTable = "DBM_Sqlite_Functions";
Emulator_Sql.AppendFormat("CREATE TEMP TABLE {0} (ID INTEGER PRIMARY KEY,Function TEXT,Description TEXT);", EmulatorTable);
Dictionary <string, string> Functions = new Dictionary <string, string>()
{
{ "REGEXP(input,pattern)", "Pattern matching" },
{ "POWER(b,x)", "b^x" },
{ "Sqrt(x)", "x^(1/2)" },
{ "Sin(x)", "" },
{ "Sinh(x)", "" },
{ "Cos(x)", "" },
{ "Cosh(x)", "" },
{ "Tan(x)", "" },
{ "Tanh(x)", "" },
{ "e()", "" },
{ "pi()", "" },
{ "log(x)", "" },
{ "encrypt(text,password)", "Encrypts the text given the password using AES encryption." },
{ "decrypt(text,password)", "Decrypts the text given the password using AES encryption." },
{ "hash(text)", "Hashes an item using SHA512." },
};
foreach (KeyValuePair <string, string> entry in Functions)
{
Emulator_Sql.AppendFormat("INSERT INTO {0} (Function,Description) VALUES('{1}','{2}');", EmulatorTable, entry.Key, entry.Value);
}
}
break;
default:
throw new NotImplementedException();
}
if (EmulatorTable != null)
{
string _SQL = Emulator_Sql.ToString();
var CS = new Engines.CommandSettings()
{
Database = Database,
SQL = $"DROP TABLE IF EXISTS {EmulatorTable};" + _SQL,
User = GlobalStatic.UserName,
Explanation = Language.Localization["User Requested"] + ": CLI EMULATOR"
};
Command(CS);
QuerySettings QS = new QuerySettings
{
Database = Database,
SQL = $"SELECT * FROM {EmulatorTable};",
ListView = Listview,
FetchRecords = false,
User = Username,
Explanation = Language.Localization["User Requested"] + ": CLI EMULATOR"
};
Query(QS);
GetSchema(Database);
SetDefaultTable(EmulatorTable);
GetColumnsofTable(Database, EmulatorTable);
}
Stack.Exit(StackPointer);
}
public static void GetSchema(string Database)
{
int StackPointer = Stack.Add($"Engines.GetSchema({Database})");
if (string.IsNullOrEmpty(Database))//Prevents Prevents Application from querying a nonexistent db
{
Stack.Exit(StackPointer);
return;
}
EnginesMode EngineMode = Engine_Type(Database);
switch (EngineMode)
{
case EnginesMode.SQLITE:
_Tables.Clear();
_Views.Clear();
_Indexes.Clear();
QuerySettings QS = new QuerySettings
{
Database = Database,
SQL = Sqlite.GetSchema(),
FetchRecords = true,
User = Language.Localization["App"],
Explanation = "SCHEMA"
};
Primitive Master_Schema_List = Query(QS);
for (int i = 1; i <= Master_Schema_List.GetItemCount(); i++)
{
string Name = Master_Schema_List[i]["tbl_name"];
switch (Master_Schema_List[i]["type"].ToString())
{
case "table":
_Tables.Add(Name);
break;
case "view":
_Views.Add(Name);
break;
case "index":
_Indexes.Add(Name);
break;
}
}
break;
}
try
{
CurrentTable = _Tables.FirstOrDefault();
}
catch (Exception ex)
{
Events.LogMessage(ex.ToString(), "System");
}
_TrackingDefaultTable.Add(Database + "." + CurrentTable);
if (Database != null && CurrentTable != null)
{
GetColumnsofTable(Database, CurrentTable);
}
_Views.Sort();
OnSchemaChange?.Invoke(null, EventArgs.Empty);
Stack.Exit(StackPointer);
}
