From 1613ca58be0cc3a4c53e72b63899b7fa4492ed7b Mon Sep 17 00:00:00 2001 From: onefang Date: Wed, 7 Aug 2019 07:44:25 +1000 Subject: Swap out MySQLGenericHandler and swap in MySQLRaw. Smaller, simpler, works better. --- OpenSim/Data/MySQL/MySQLGenericHandler.cs | 353 --------------------- OpenSim/Data/MySQL/MySQLRaw.cs | 142 +++++++++ .../Server/Handlers/Web/WebServerInConnector.cs | 19 +- 3 files changed, 152 insertions(+), 362 deletions(-) delete mode 100644 OpenSim/Data/MySQL/MySQLGenericHandler.cs create mode 100644 OpenSim/Data/MySQL/MySQLRaw.cs diff --git a/OpenSim/Data/MySQL/MySQLGenericHandler.cs b/OpenSim/Data/MySQL/MySQLGenericHandler.cs deleted file mode 100644 index 4f84641..0000000 --- a/OpenSim/Data/MySQL/MySQLGenericHandler.cs +++ /dev/null @@ -1,353 +0,0 @@ -/* - * Copyright (c) Contributors, http://opensimulator.org/ - * See CONTRIBUTORS.TXT for a full list of copyright holders. - * - * Redistribution and use in source and binary forms, with or without - * modification, are permitted provided that the following conditions are met: - * * Redistributions of source code must retain the above copyright - * notice, this list of conditions and the following disclaimer. - * * Redistributions in binary form must reproduce the above copyright - * notice, this list of conditions and the following disclaimer in the - * documentation and/or other materials provided with the distribution. - * * Neither the name of the OpenSimulator Project nor the - * names of its contributors may be used to endorse or promote products - * derived from this software without specific prior written permission. - * - * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY - * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED - * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE - * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY - * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES - * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; - * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND - * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT - * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS - * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. - */ - -using System; -using System.Collections.Generic; -using System.Data; -using System.Reflection; -using log4net; -using MySql.Data.MySqlClient; -using OpenMetaverse; -using OpenSim.Framework; -using OpenSim.Region.Framework.Interfaces; - -namespace OpenSim.Data.MySQL -{ -// public class MySQLGenericHandler : MySqlFramework where T: class, new() - public class MySQLGenericHandler : MySqlFramework - { -// private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); - - protected virtual Assembly Assembly - { - get { return GetType().Assembly; } - } - - public MySQLGenericHandler(MySqlTransaction trans) : base(trans) - { - CommonConstruct(); - } - - public MySQLGenericHandler(string connectionString) : base(connectionString) - { - CommonConstruct(); - } - - protected void CommonConstruct() - { - // We always use a new connection for any Migrations - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) - { - dbcon.Open(); - } - } -/* - private void CheckColumnNames(IDataReader reader) - { - if (m_ColumnNames != null) - return; - - List columnNames = new List(); - - DataTable schemaTable = reader.GetSchemaTable(); - foreach (DataRow row in schemaTable.Rows) - { - if (row["ColumnName"] != null) - columnNames.Add(row["ColumnName"].ToString()); - } - - m_ColumnNames = columnNames; - } -*/ -/* - public virtual T[] Get(string table, string field, string key) - { - return Get(table, new string[] { field }, new string[] { key }); - } - - public virtual T[] Get(string table, string[] fields, string[] keys) - { - return Get(table, fields, keys, String.Empty); - } - - public virtual T[] Get(string table, string[] fields, string[] keys, string options) - { - if (fields.Length != keys.Length) - return new T[0]; - - List terms = new List(); - - using (MySqlCommand cmd = new MySqlCommand()) - { - for (int i = 0 ; i < fields.Length ; i++) - { - cmd.Parameters.AddWithValue(fields[i], keys[i]); - terms.Add("`" + fields[i] + "` = ?" + fields[i]); - } - - string where = String.Join(" and ", terms.ToArray()); - - string query = String.Format("select * from {0} where {1} {2}", - table, where, options); - - cmd.CommandText = query; - - return DoQuery(cmd); - } - } - - protected T[] DoQuery(MySqlCommand cmd) - { - if (m_trans == null) - { - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) - { - dbcon.Open(); - T[] ret = DoQueryWithConnection(cmd, dbcon); - dbcon.Close(); - return ret; - } - } - else - { - return DoQueryWithTransaction(cmd, m_trans); - } - } - - protected T[] DoQueryWithTransaction(MySqlCommand cmd, MySqlTransaction trans) - { - cmd.Transaction = trans; - - return DoQueryWithConnection(cmd, trans.Connection); - } - - protected T[] DoQueryWithConnection(MySqlCommand cmd, MySqlConnection dbcon) - { - List result = new List(); - - cmd.Connection = dbcon; - - using (IDataReader reader = cmd.ExecuteReader()) - { - if (reader == null) - return new T[0]; -/* - - CheckColumnNames(reader); - - while (reader.Read()) - { - T row = new T(); - - foreach (string name in m_Fields.Keys) - { - if (reader[name] is DBNull) - { - continue; - } - if (m_Fields[name].FieldType == typeof(bool)) - { - int v = Convert.ToInt32(reader[name]); - m_Fields[name].SetValue(row, v != 0 ? true : false); - } - else if (m_Fields[name].FieldType == typeof(UUID)) - { - m_Fields[name].SetValue(row, DBGuid.FromDB(reader[name])); - } - else if (m_Fields[name].FieldType == typeof(int)) - { - int v = Convert.ToInt32(reader[name]); - m_Fields[name].SetValue(row, v); - } - else if (m_Fields[name].FieldType == typeof(uint)) - { - uint v = Convert.ToUInt32(reader[name]); - m_Fields[name].SetValue(row, v); - } - else - { - m_Fields[name].SetValue(row, reader[name]); - } - } - - if (m_DataField != null) - { - Dictionary data = - new Dictionary(); - - foreach (string col in m_ColumnNames) - { - data[col] = reader[col].ToString(); - if (data[col] == null) - data[col] = String.Empty; - } - - m_DataField.SetValue(row, data); - } - result.Add(row); - } -*/ -/* - } - cmd.Connection = null; - return result.ToArray(); - } - - public virtual T[] Get(string table, string where) - { - using (MySqlCommand cmd = new MySqlCommand()) - { - string query = String.Format("select * from {0} where {1}", - table, where); - - cmd.CommandText = query; - - return DoQuery(cmd); - } - } -*/ - public virtual bool Delete(string table, string field, string key) - { - return Delete(table, new string[] { field }, new string[] { key }); - } - - public virtual bool Delete(string table, string[] fields, string[] keys) - { -// m_log.DebugFormat( -// "[MYSQL GENERIC TABLE HANDLER]: Delete(string[] fields, string[] keys) invoked with {0}:{1}", -// string.Join(",", fields), string.Join(",", keys)); - - if (fields.Length != keys.Length) - return false; - - List terms = new List(); - - using (MySqlCommand cmd = new MySqlCommand()) - { - for (int i = 0 ; i < fields.Length ; i++) - { - cmd.Parameters.AddWithValue(fields[i], keys[i]); - terms.Add("`" + fields[i] + "` = ?" + fields[i]); - } - - string where = String.Join(" and ", terms.ToArray()); - - string query = String.Format("delete from {0} where {1}", table, where); - - cmd.CommandText = query; - - return ExecuteNonQuery(cmd) > 0; - } - } - - public long GetCount(string table, string field, string key) - { - return GetCount(table, new string[] { field }, new string[] { key }); - } - - public long GetCount(string table, string[] fields, string[] keys) - { - if (fields.Length != keys.Length) - return 0; - - List terms = new List(); - - using (MySqlCommand cmd = new MySqlCommand()) - { - for (int i = 0; i < fields.Length; i++) - { - cmd.Parameters.AddWithValue(fields[i], keys[i]); - terms.Add("`" + fields[i] + "` = ?" + fields[i]); - } - - string where = String.Join(" and ", terms.ToArray()); - - string query = String.Format("select count(*) from {0} where {1}", - table, where); - - cmd.CommandText = query; - - Object result = DoQueryScalar(cmd); - - return Convert.ToInt64(result); - } - } - - public long GetCount(string table, string where) - { - using (MySqlCommand cmd = new MySqlCommand()) - { - string query = String.Format("select count(*) from {0} where {1}", - table, where); - - cmd.CommandText = query; - - object result = DoQueryScalar(cmd); - - return Convert.ToInt64(result); - } - } - - public long GetCount(string table) - { - using (MySqlCommand cmd = new MySqlCommand()) - { - string query = String.Format("select count(*) from {0}", table); - - cmd.CommandText = query; - - object result = DoQueryScalar(cmd); - - return Convert.ToInt64(result); - } - } - - public object DoQueryScalar(MySqlCommand cmd) - { - if (m_trans == null) - { - using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) - { - dbcon.Open(); - cmd.Connection = dbcon; - - Object ret = cmd.ExecuteScalar(); - cmd.Connection = null; - dbcon.Close(); - return ret; - } - } - else - { - cmd.Connection = m_trans.Connection; - cmd.Transaction = m_trans; - - return cmd.ExecuteScalar(); - } - } - } -} diff --git a/OpenSim/Data/MySQL/MySQLRaw.cs b/OpenSim/Data/MySQL/MySQLRaw.cs new file mode 100644 index 0000000..4be6d90 --- /dev/null +++ b/OpenSim/Data/MySQL/MySQLRaw.cs @@ -0,0 +1,142 @@ +// https://dev.mysql.com/doc/connector-net/en/ + + +using System; +using System.Collections; +using System.Collections.Generic; +using System.Data; +using System.Reflection; +using System.Text; +using log4net; +using MySql.Data.MySqlClient; + +namespace OpenSim.Data.MySQL +{ + public class MySQLRaw + { + private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); + private string m_connectString; + + public MySQLRaw(string connect) + { + m_connectString = connect; + } + + public int Count(string table) + { + return Count(table, ""); + } + public int Count(string table, string wher) + { + string query = "SELECT Count(*) FROM " + table; + if ("" != wher) + query = query + " WHERE " + wher; + int result = -1; + + object r = doScalarQuery(query); + if (r != null) + result = Convert.ToInt32(r); + + return result; + } + + public List< Hashtable > Select(string table, string wher) + { + string query = "SELECT * FROM " + table; + if ("" != wher) + query = query + " WHERE " + wher; + + using (MySqlConnection dbcon = new MySqlConnection(m_connectString)) + { + dbcon.Open(); + MySqlCommand cmd = new MySqlCommand(query, dbcon); + MySqlDataReader rdr = cmd.ExecuteReader(); + List names = new List(); + DataTable schema = rdr.GetSchemaTable(); + List< Hashtable > list = new List< Hashtable >(); + + foreach (DataRow row in schema.Rows) + { + string tbl = ""; + string nm = ""; + string tp = ""; + foreach (DataColumn col in schema.Columns) + { + if ("BaseTableName" == col.ColumnName) tbl = row[col].ToString(); + if ("ColumnName" == col.ColumnName) nm = row[col].ToString(); + if ("DataType" == col.ColumnName) tp = row[col].ToString(); + } + names.Add(nm); + } + + while (rdr.Read()) + { + Hashtable r = new Hashtable(); + foreach (string name in names) + { + r[name] = rdr[name]; + } + list.Add(r); + } + + rdr.Close(); + dbcon.Close(); + return list; + } + } + + private object doScalarQuery(string query) + { + try + { + using (MySqlConnection dbcon = new MySqlConnection(m_connectString)) + { + dbcon.Open(); + MySqlCommand cmd = new MySqlCommand(query, dbcon); + Object ret = cmd.ExecuteScalar(); + dbcon.Close(); + return ret; + } + } + catch (MySqlException e) + { + m_log.ErrorFormat("[MYSQL RAW]: Problem connecting to the database {0}", e.Message); + return null; + } + } + + private void doNonQuery(string query) + { + using (MySqlConnection dbcon = new MySqlConnection(m_connectString)) + { + dbcon.Open(); + MySqlCommand cmd = new MySqlCommand(query, dbcon); + cmd.ExecuteNonQuery(); + dbcon.Close(); + } + } + + public void Insert(string table) + { + string query = "INSERT INTO " + table + " (name, age) VALUES('John Smith', '33')"; + doNonQuery(query); + } + + public void Update(string table, string wher) + { + string query = "UPDATE " + table + " SET name='Joe', age='22'"; + if ("" != wher) + query = query + " WHERE " + wher; + doNonQuery(query); + } + + public void Delete(string table, string wher) + { + string query = "DELETE FROM " + table; + if ("" != wher) + query = query + " WHERE " + wher; + doNonQuery(query); + } + + } +} diff --git a/OpenSim/Server/Handlers/Web/WebServerInConnector.cs b/OpenSim/Server/Handlers/Web/WebServerInConnector.cs index 8c14fde..bc34bae 100644 --- a/OpenSim/Server/Handlers/Web/WebServerInConnector.cs +++ b/OpenSim/Server/Handlers/Web/WebServerInConnector.cs @@ -49,7 +49,7 @@ namespace OpenSim.Server.Handlers.Web // This is all slow and clunky, it's not a real web server, just something to use if you don't want a real one. private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); private IConfigSource m_Config; - protected MySQLGenericHandler m_Database = null; + protected MySQLRaw m_database = null; private Hashtable mime = new Hashtable(); private Hashtable ssi = new Hashtable(); @@ -78,8 +78,9 @@ namespace OpenSim.Server.Handlers.Web //// TODO - Should do the plugin thing to pick between database backends. //// Or not, we are all using MariaDB anyway. // m_Database = LoadPlugin(dllName, new Object[] { connString }); - m_Database = new MySQLGenericHandler(connString); - if (m_Database == null) + + m_database = new MySQLRaw(connString); + if (m_database == null) throw new Exception("Could not find a storage interface in the given module " + dllName); mime.Add(".gz", "application/gzip"); @@ -118,16 +119,16 @@ namespace OpenSim.Server.Handlers.Web private Hashtable WebRequestHandler(Hashtable request) { - long locIn = m_Database.GetCount("Presence", "RegionID != '00000000-0000-0000-0000-000000000000'"); // Locals online but not HGing, and HGers in world. - long HGin = m_Database.GetCount("Presence", "UserID NOT IN (SELECT PrincipalID FROM UserAccounts)"); // HGers in world. - long locOut = m_Database.GetCount("hg_traveling_data", "GridExternalName != '" + ssi["uri"] + "'"); // Locals that are HGing. + long locIn = m_database.Count("Presence", "RegionID != '00000000-0000-0000-0000-000000000000'"); // Locals online but not HGing, and HGers in world. + long HGin = m_database.Count("Presence", "UserID NOT IN (SELECT PrincipalID FROM UserAccounts)"); // HGers in world. + long locOut = m_database.Count("hg_traveling_data", "GridExternalName != '" + ssi["uri"] + "'"); // Locals that are HGing. Hashtable reply = new Hashtable(); - ssi["members"] = m_Database.GetCount("UserAccounts").ToString(); - ssi["sims"] = m_Database.GetCount("regions").ToString(); + ssi["members"] = m_database.Count("UserAccounts").ToString(); + ssi["sims"] = m_database.Count("regions").ToString(); ssi["inworld"] = (locIn - HGin).ToString(); ssi["outworld"] = locOut.ToString(); ssi["hgers"] = HGin.ToString(); - ssi["month"] = m_Database.GetCount("GridUser", "Login > UNIX_TIMESTAMP(FROM_UNIXTIME(UNIX_TIMESTAMP(now()) - 2419200))").ToString(); + ssi["month"] = m_database.Count("GridUser", "Login > UNIX_TIMESTAMP(FROM_UNIXTIME(UNIX_TIMESTAMP(now()) - 2419200))").ToString(); string reqpath = (string) request["uri"]; string[] query = (string[]) request["querystringkeys"]; -- cgit v1.1