Give the new user tables the once-over. Strip the current set of methods

from IUserAccountService, we need to define what goes in there. Change
the handler to the generic handler. Adjust migrations, add index

5 files changed, 20 insertions, 145 deletions

diff --git a/OpenSim/Data/IUserAccountData.cs b/OpenSim/Data/IUserAccountData.cs
index d1d6c66..0004a5f 100644
--- a/OpenSim/Data/IUserAccountData.cs
+++ b/OpenSim/Data/IUserAccountData.cs
@@ -36,6 +36,8 @@ namespace OpenSim.Data
     {
         public UUID PrincipalID;
         public UUID ScopeID;
+        public string FirstName;
+        public string LastName;
         public Dictionary<string, object> Data;
     }
 
@@ -44,12 +46,6 @@ namespace OpenSim.Data
     /// </summary>
     public interface IUserAccountData
     {
-        UserAccountData Get(UUID principalID, UUID ScopeID);
-
-        List<UserAccountData> Query(UUID principalID, UUID ScopeID, string query);
-
         bool Store(UserAccountData data);
-
-        bool SetDataItem(UUID principalID, string item, string value);
     }
 }
diff --git a/OpenSim/Data/MySQL/MySQLUserAccountData.cs b/OpenSim/Data/MySQL/MySQLUserAccountData.cs
index d48144d..9624d79 100644
--- a/OpenSim/Data/MySQL/MySQLUserAccountData.cs
+++ b/OpenSim/Data/MySQL/MySQLUserAccountData.cs
@@ -35,146 +35,11 @@ using MySql.Data.MySqlClient;
 
 namespace OpenSim.Data.MySQL
 {
-    public class MySqlUserAccountData : MySqlFramework, IUserAccountData
+    public class MySqlUserAccountData : MySQLGenericTableHandler<UserAccountData>, IUserAccountData
     {
-        private string m_Realm;
-        private List<string> m_ColumnNames = null;
-//        private int m_LastExpire = 0;
-
         public MySqlUserAccountData(string connectionString, string realm)
-                : base(connectionString)
-        {
-            m_Realm = realm;
-
-            Migration m = new Migration(m_Connection, GetType().Assembly, "UserStore");
-            m.Update();
-        }
-
-        public List<UserAccountData> Query(UUID principalID, UUID scopeID, string query)
-        {
-            return null;
-        }
-
-        public UserAccountData Get(UUID principalID, UUID scopeID)
-        {
-            UserAccountData ret = new UserAccountData();
-            ret.Data = new Dictionary<string, object>();
-
-            string command = "select * from `"+m_Realm+"` where UUID = ?principalID";
-            if (scopeID != UUID.Zero)
-                command += " and ScopeID = ?scopeID";
-
-            MySqlCommand cmd = new MySqlCommand(command);
-
-            cmd.Parameters.AddWithValue("?principalID", principalID.ToString());
-            cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString());
-
-            IDataReader result = ExecuteReader(cmd);
-
-            if (result.Read())
-            {
-                ret.PrincipalID = principalID;
-                UUID scope;
-                UUID.TryParse(result["ScopeID"].ToString(), out scope);
-                ret.ScopeID = scope;
-
-                if (m_ColumnNames == null)
-                {
-                    m_ColumnNames = new List<string>();
-
-                    DataTable schemaTable = result.GetSchemaTable();
-                    foreach (DataRow row in schemaTable.Rows)
-                        m_ColumnNames.Add(row["ColumnName"].ToString());
-                }
-
-                foreach (string s in m_ColumnNames)
-                {
-                    if (s == "UUID")
-                        continue;
-                    if (s == "ScopeID")
-                        continue;
-
-                    ret.Data[s] = result[s].ToString();
-                }
-
-                result.Close();
-                CloseReaderCommand(cmd);
-
-                return ret;
-            }
-
-            result.Close();
-            CloseReaderCommand(cmd);
-
-            return null;
-        }
-
-        public bool Store(UserAccountData data)
-        {
-            if (data.Data.ContainsKey("UUID"))
-                data.Data.Remove("UUID");
-            if (data.Data.ContainsKey("ScopeID"))
-                data.Data.Remove("ScopeID");
-
-            string[] fields = new List<string>(data.Data.Keys).ToArray();
-
-            MySqlCommand cmd = new MySqlCommand();
-
-            string update = "update `"+m_Realm+"` set ";
-            bool first = true;
-            foreach (string field in fields)
-            {
-                if (!first)
-                    update += ", ";
-                update += "`" + field + "` = ?"+field;
-
-                first = false;
-
-                cmd.Parameters.AddWithValue("?"+field, data.Data[field]);
-            }
-
-            update += " where UUID = ?principalID";
-
-            if (data.ScopeID != UUID.Zero)
-                update += " and ScopeID = ?scopeID";
-
-            cmd.CommandText = update;
-            cmd.Parameters.AddWithValue("?principalID", data.PrincipalID.ToString());
-            cmd.Parameters.AddWithValue("?scopeID", data.ScopeID.ToString());
-
-            if (ExecuteNonQuery(cmd) < 1)
-            {
-                string insert = "insert into `" + m_Realm + "` (`UUID`, `ScopeID`, `" +
-                        String.Join("`, `", fields) +
-                        "`) values (?principalID, ?scopeID, ?" + String.Join(", ?", fields) + ")";
-
-                cmd.CommandText = insert;
-
-                if (ExecuteNonQuery(cmd) < 1)
-                {
-                    cmd.Dispose();
-                    return false;
-                }
-            }
-
-            cmd.Dispose();
-
-            return true;
-        }
-
-        public bool SetDataItem(UUID principalID, string item, string value)
+                : base(connectionString, realm, "UserAccount")
         {
-            MySqlCommand cmd = new MySqlCommand("update `" + m_Realm +
-                    "` set `" + item + "` = ?" + item + " where UUID = ?UUID");
-
-
-            cmd.Parameters.AddWithValue("?"+item, value);
-            cmd.Parameters.AddWithValue("?UUID", principalID.ToString());
-
-            if (ExecuteNonQuery(cmd) > 0)
-                return true;
-
-            return false;
         }
     }
 }
diff --git a/OpenSim/Data/MySQL/Resources/001_UserAccount.sql b/OpenSim/Data/MySQL/Resources/001_UserAccount.sql
index f946430..7d63816 100644
--- a/OpenSim/Data/MySQL/Resources/001_UserAccount.sql
+++ b/OpenSim/Data/MySQL/Resources/001_UserAccount.sql
@@ -1,7 +1,7 @@
 BEGIN;
 
-CREATE TABLE `useraccount` (
-    `UserID` CHAR(36) NOT NULL,
+CREATE TABLE `UserAccounts` (
+    `PrincipalID` CHAR(36) NOT NULL,
     `ScopeID` CHAR(36) NOT NULL,
     `FirstName` VARCHAR(64) NOT NULL,
     `LastName` VARCHAR(64) NOT NULL,
diff --git a/OpenSim/Data/MySQL/Resources/002_UserAccount.sql b/OpenSim/Data/MySQL/Resources/002_UserAccount.sql
new file mode 100644
index 0000000..08a0f87
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/002_UserAccount.sql
@@ -0,0 +1,5 @@
+BEGIN;
+
+INSERT INTO UserAccounts (UserID, ScopeID, FirstName, LastName, Email, ServiceURLs, Created) SELECT `UUID` AS PrincipalID, '00000000-0000-0000-0000-000000000000' AS ScopeID, username AS FirstName, lastname AS LastName, email as Email, CONCAT('AssetServerURI=', userAssetURI, ' InventoryServerURI=', userInventoryURI, ' GatewayURI= HomeURI=') AS ServiceURLs, created as Created FROM users;
+
+COMMIT;
diff --git a/OpenSim/Data/MySQL/Resources/003_UserAccount.sql b/OpenSim/Data/MySQL/Resources/003_UserAccount.sql
new file mode 100644
index 0000000..e42d93b
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/003_UserAccount.sql
@@ -0,0 +1,9 @@
+BEGIN;
+
+CREATE UNIQUE INDEX PrincipalID ON UserAccounts(PrincipalID);
+CREATE INDEX Email ON UserAccounts(Email);
+CREATE INDEX FirstName ON UserAccounts(FirstName);
+CREATE INDEX LastName ON UserAccounts(LastName);
+CREATE INDEX Name ON UserAccounts(FirstName,LastName);
+
+COMMIT;