Swap out MySQLGenericHandler and swap in MySQLRaw.

Smaller, simpler, works better.

3 files changed, 152 insertions, 362 deletions

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<T> : 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<string> columnNames = new List<string>();
-
-            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<string> terms = new List<string>();
-
-            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<T> result = new List<T>();
-
-            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<string, string> data =
-                            new Dictionary<string, string>();
-
-                        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<string> terms = new List<string>();
-
-            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<string> terms = new List<string>();
-
-            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<string> names = new List<string>();
+                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<SQLGenericHandler>(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"];