///<summary>Queries information_schema.COLUMNS and returns all column names of given table.</summary>
public static List <string> GetColumnNamesFromTableMySql(string tableName)
{
return(Db.GetListString(@"
SELECT COLUMN_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = '" + DataConnection.GetDatabaseName() + "' AND TABLE_NAME='" + tableName + "'"));
}
///<summary>Returns serialized DbInfo object as JSON string of database info from both the preference table and non preferernce table info.</summary>
private string GetDbInfoJSON(long patNum, string moduleName)
{
_info = new BugSubmission.SubmissionInfo();
try {
//This list is not in a separate method because we want to ensure that future development related to bug submissions don't try to make assumptions
//on which preferences are in an object at any given time.
//Ex. Let's say in version 17.4, the list doesn't contain the payplan version preference, but 17.5 does.
//If we called the method that retrieves the used preferences from WebServiceMainHQ which in this example is on version 17.5,
// it would think all bugsubmission rows contain the payplan version preference when that is not the case.
List <PrefName> listPrefs = new List <PrefName>()
{
PrefName.AtoZfolderUsed,
PrefName.ClaimSnapshotEnabled,
PrefName.ClaimSnapshotRunTime,
PrefName.ClaimSnapshotTriggerType,
PrefName.CorruptedDatabase,
PrefName.DataBaseVersion,
PrefName.EasyNoClinics,
PrefName.LanguageAndRegion,
PrefName.MySqlVersion,
PrefName.PayPlansVersion,
PrefName.ProcessSigsIntervalInSecs,
PrefName.ProgramVersionLastUpdated,
PrefName.ProgramVersion,
PrefName.RandomPrimaryKeys,
PrefName.RegistrationKey,
PrefName.RegistrationKeyIsDisabled,
PrefName.ReplicationFailureAtServer_id,
PrefName.ReportingServerCompName,
PrefName.ReportingServerDbName,
PrefName.ReportingServerMySqlUser,
PrefName.ReportingServerMySqlPassHash,
PrefName.ReportingServerURI,
PrefName.WebServiceServerName
};
foreach (PrefName pref in listPrefs)
{
_info.DictPrefValues[pref] = Prefs.GetOne(pref).ValueString;
}
_info.CountClinics = Clinics.GetCount();
_info.EnabledPlugins = Programs.GetWhere(x => x.Enabled && !string.IsNullOrWhiteSpace(x.PluginDllName)).Select(x => x.ProgName).ToList();
_info.ClinicNumCur = Clinics.ClinicNum;
_info.UserNumCur = Security.CurUser.UserNum;
_info.PatientNumCur = patNum;
_info.IsOfficeOnReplication = (ReplicationServers.GetCount() > 0 ? true : false);
_info.IsOfficeUsingMiddleTier = (RemotingClient.RemotingRole == RemotingRole.ClientWeb ? true : false);
_info.WindowsVersion = MiscData.GetOSVersionInfo();
_info.CompName = Environment.MachineName;
List <UpdateHistory> listHist = UpdateHistories.GetPreviouUpdateHistories(2); //Ordered by newer versions first.
_info.PreviousUpdateVersion = listHist.Count == 2 ? listHist[1].ProgramVersion : ""; //Show the previous version they updated from
_info.PreviousUpdateTime = listHist.Count > 0 ? listHist[0].DateTimeUpdated : DateTime.MinValue; //Show when they updated to the current version.
_info.ModuleNameCur = moduleName;
_info.DatabaseName = DataConnection.GetDatabaseName();
}
catch (Exception ex) {
ex.DoNothing();
}
return(JsonConvert.SerializeObject(_info));
}
///<summary>Gets the names of tables in InnoDB format, comma delimited (excluding the 'phone' table). Returns empty string if none.</summary>
public static string GetInnodbTableNames()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetString(MethodBase.GetCurrentMethod()));
}
//Using COUNT(*) with INFORMATION_SCHEMA is buggy. It can return "1" even if no results.
string command = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.tables "
+ "WHERE TABLE_SCHEMA='" + POut.String(DataConnection.GetDatabaseName()) + "' "
+ "AND TABLE_NAME!='phone' " //this table is used internally at OD HQ, and is always innodb.
+ "AND ENGINE NOT LIKE 'MyISAM'";
DataTable table = Db.GetTable(command);
string tableNames = "";
for (int i = 0; i < table.Rows.Count; i++)
{
if (tableNames != "")
{
tableNames += ",";
}
tableNames += PIn.String(table.Rows[i][0].ToString());
}
return(tableNames);
}
///<summary>Turns "pooling" off, then opens the current database connection, adds that connection to the dictionary of MySqlConnections, then returns the unique ServerThread.
///The returned ServerThread can then be used later in order to stop the query in the middle of executing.
///A non-pooled connection will NOT attempt to re-use connections to the DB that already exist but are idle,
///rather it will create a brand new connection that no other connection can use.
///This is so that user queries can be safely cancelled if needed.
///Required as a first step for user queries (and ONLY user queries).
///Not currently Oracle compatible.</summary>
public static int GetServerThread(bool isReportServer)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetInt(MethodBase.GetCurrentMethod(), isReportServer));
}
MySqlConnection con = new MySqlConnection();
if (isReportServer)
{
if (ODBuild.IsWeb() && PrefC.ReportingServer.Server != "" && PrefC.ReportingServer.Database != DataConnection.GetDatabaseName())
{
//Security safeguard to prevent Web users from connecting to another office's database.
throw new ODException("Report server database name must match current database.");
}
con = new MySqlConnection(
DataConnection.GetReportConnectionString(
PrefC.ReportingServer.Server
, PrefC.ReportingServer.Database
, PrefC.ReportingServer.MySqlUser
, PrefC.ReportingServer.MySqlPass)
+ ";pooling=false");
}
else
{
//Use the database user with lower permissions when in Middle Tier since this method is explicitly designed for the User Query window.
string connectStr = (RemotingClient.RemotingRole == RemotingRole.ServerWeb ? DataConnection.GetLowConnectionString()
: DataConnection.GetCurrentConnectionString());
//all connection details are the same, except pooling should be false.
con = new MySqlConnection(connectStr + ";pooling=false");
}
con.Open();
int serverThread = con.ServerThread;
//If the dictionary already contains the ServerThread key, then something went wrong. Just stop and throw.
if (_dictCons.ContainsKey(serverThread))
{
con.Close();
throw new ApplicationException("Critical error in GetServerThread: A duplicate connection was found via the server thread ID.");
}
lock (_lockObj) {
_dictCons[serverThread] = con;
}
return(serverThread);
}
///<summary>Returns true if the database has at least one table in InnoDB format. Default db is DataConnection.GetDatabaseName().</summary>
public static bool HasInnoDbTables(string dbName = "")
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetBool(MethodBase.GetCurrentMethod(), dbName));
}
//Using COUNT(*) with INFORMATION_SCHEMA is buggy. It can return "1" even if no results.
string command = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.tables "
+ "WHERE TABLE_SCHEMA='" + POut.String(string.IsNullOrEmpty(dbName) ? DataConnection.GetDatabaseName() : dbName) + "' "
+ "AND ENGINE NOT LIKE 'MyISAM'";
return(Db.GetTable(command).Rows.Count > 1);
}