From 0ab6aac05255078a9d190f6623b2d86d5253d955 Mon Sep 17 00:00:00 2001 From: Diva Canto Date: Sat, 20 Feb 2010 17:52:38 -0800 Subject: Added UserAccountData and auth to the SQLite connector. Compiles, runs, but access to these tables doesn't work. --- OpenSim/Data/SQLite/Resources/001_AuthStore.sql | 18 ++ OpenSim/Data/SQLite/Resources/001_UserAccount.sql | 17 ++ OpenSim/Data/SQLite/Resources/002_AuthStore.sql | 5 + OpenSim/Data/SQLite/Resources/002_UserAccount.sql | 5 + OpenSim/Data/SQLite/SQLiteAuthenticationData.cs | 214 ++++++++++++++++++++++ OpenSim/Data/SQLite/SQLiteFramework.cs | 11 +- OpenSim/Data/SQLite/SQLiteGenericTableHandler.cs | 15 +- OpenSim/Data/SQLite/SQLiteUserAccountData.cs | 81 ++++++++ 8 files changed, 359 insertions(+), 7 deletions(-) create mode 100644 OpenSim/Data/SQLite/Resources/001_AuthStore.sql create mode 100644 OpenSim/Data/SQLite/Resources/001_UserAccount.sql create mode 100644 OpenSim/Data/SQLite/Resources/002_AuthStore.sql create mode 100644 OpenSim/Data/SQLite/Resources/002_UserAccount.sql create mode 100644 OpenSim/Data/SQLite/SQLiteAuthenticationData.cs create mode 100644 OpenSim/Data/SQLite/SQLiteUserAccountData.cs (limited to 'OpenSim/Data/SQLite') diff --git a/OpenSim/Data/SQLite/Resources/001_AuthStore.sql b/OpenSim/Data/SQLite/Resources/001_AuthStore.sql new file mode 100644 index 0000000..468567d --- /dev/null +++ b/OpenSim/Data/SQLite/Resources/001_AuthStore.sql @@ -0,0 +1,18 @@ +BEGIN TRANSACTION; + +CREATE TABLE auth ( + UUID char(36) NOT NULL, + passwordHash char(32) NOT NULL default '', + passwordSalt char(32) NOT NULL default '', + webLoginKey varchar(255) NOT NULL default '', + accountType VARCHAR(32) NOT NULL DEFAULT 'UserAccount', + PRIMARY KEY (`UUID`) +); + +CREATE TABLE tokens ( + UUID char(36) NOT NULL, + token varchar(255) NOT NULL, + validity datetime NOT NULL +); + +COMMIT; diff --git a/OpenSim/Data/SQLite/Resources/001_UserAccount.sql b/OpenSim/Data/SQLite/Resources/001_UserAccount.sql new file mode 100644 index 0000000..f9bf24c --- /dev/null +++ b/OpenSim/Data/SQLite/Resources/001_UserAccount.sql @@ -0,0 +1,17 @@ +BEGIN TRANSACTION; + +-- useraccounts table +CREATE TABLE UserAccounts ( + PrincipalID CHAR(36) NOT NULL, + ScopeID CHAR(36) NOT NULL, + FirstName VARCHAR(64) NOT NULL, + LastName VARCHAR(64) NOT NULL, + Email VARCHAR(64), + ServiceURLs TEXT, + Created INT(11), + UserLevel integer NOT NULL DEFAULT 0, + UserFlags integer NOT NULL DEFAULT 0, + UserTitle varchar(64) NOT NULL DEFAULT '' +); + +COMMIT; \ No newline at end of file diff --git a/OpenSim/Data/SQLite/Resources/002_AuthStore.sql b/OpenSim/Data/SQLite/Resources/002_AuthStore.sql new file mode 100644 index 0000000..3237b68 --- /dev/null +++ b/OpenSim/Data/SQLite/Resources/002_AuthStore.sql @@ -0,0 +1,5 @@ +BEGIN TRANSACTION; + +INSERT INTO auth (UUID, passwordHash, passwordSalt, webLoginKey) SELECT `UUID` AS UUID, `passwordHash` AS passwordHash, `passwordSalt` AS passwordSalt, `webLoginKey` AS webLoginKey FROM users; + +COMMIT; diff --git a/OpenSim/Data/SQLite/Resources/002_UserAccount.sql b/OpenSim/Data/SQLite/Resources/002_UserAccount.sql new file mode 100644 index 0000000..c0b3d7b --- /dev/null +++ b/OpenSim/Data/SQLite/Resources/002_UserAccount.sql @@ -0,0 +1,5 @@ +BEGIN TRANSACTION; + +INSERT INTO UserAccounts (PrincipalID, 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/SQLite/SQLiteAuthenticationData.cs b/OpenSim/Data/SQLite/SQLiteAuthenticationData.cs new file mode 100644 index 0000000..271ed47 --- /dev/null +++ b/OpenSim/Data/SQLite/SQLiteAuthenticationData.cs @@ -0,0 +1,214 @@ +/* + * 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; +using System.Collections.Generic; +using System.Data; +using OpenMetaverse; +using OpenSim.Framework; +using Mono.Data.SqliteClient; + +namespace OpenSim.Data.SQLite +{ + public class SQLiteAuthenticationData : SQLiteFramework, IAuthenticationData + { + private string m_Realm; + private List m_ColumnNames; + private int m_LastExpire; + private string m_connectionString; + + private static bool m_initialized = false; + + public SQLiteAuthenticationData(string connectionString, string realm) + : base(connectionString) + { + m_Realm = realm; + m_connectionString = connectionString; + + if (!m_initialized) + { + using (SqliteConnection dbcon = new SqliteConnection(m_connectionString)) + { + dbcon.Open(); + Migration m = new Migration(dbcon, GetType().Assembly, "AuthStore"); + m.Update(); + } + m_initialized = true; + } + } + + public AuthenticationData Get(UUID principalID) + { + AuthenticationData ret = new AuthenticationData(); + ret.Data = new Dictionary(); + + using (SqliteConnection dbcon = new SqliteConnection(m_connectionString)) + { + dbcon.Open(); + SqliteCommand cmd = new SqliteCommand("select * from `" + m_Realm + "` where UUID = '" + principalID.ToString() + "'", dbcon); + + IDataReader result = cmd.ExecuteReader(); + + if (result.Read()) + { + ret.PrincipalID = principalID; + + if (m_ColumnNames == null) + { + m_ColumnNames = new List(); + + 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; + + ret.Data[s] = result[s].ToString(); + } + + return ret; + } + else + { + return null; + } + } + } + + public bool Store(AuthenticationData data) + { + if (data.Data.ContainsKey("UUID")) + data.Data.Remove("UUID"); + + string[] fields = new List(data.Data.Keys).ToArray(); + string[] values = new string[data.Data.Count]; + int i = 0; + foreach (object o in data.Data.Values) + values[i++] = o.ToString(); + + SqliteCommand cmd = new SqliteCommand(); + + string update = "update `"+m_Realm+"` set "; + bool first = true; + foreach (string field in fields) + { + if (!first) + update += ", "; + update += "`" + field + "` = " + data.Data[field]; + + first = false; + + } + + update += " where UUID = '" + data.PrincipalID.ToString() + "'"; + + cmd.CommandText = update; + + if (ExecuteNonQuery(cmd) < 1) + { + string insert = "insert into `" + m_Realm + "` (`UUID`, `" + + String.Join("`, `", fields) + + "`) values ('" + data.PrincipalID.ToString() + "', " + String.Join(", '", values) + "')"; + + cmd.CommandText = insert; + + if (ExecuteNonQuery(cmd) < 1) + { + cmd.Dispose(); + return false; + } + } + + cmd.Dispose(); + + return true; + } + + public bool SetDataItem(UUID principalID, string item, string value) + { + SqliteCommand cmd = new SqliteCommand("update `" + m_Realm + + "` set `" + item + "` = " + value + " where UUID = '" + principalID.ToString() + "'"); + + if (ExecuteNonQuery(cmd) > 0) + return true; + + return false; + } + + public bool SetToken(UUID principalID, string token, int lifetime) + { + if (System.Environment.TickCount - m_LastExpire > 30000) + DoExpire(); + + SqliteCommand cmd = new SqliteCommand("insert into tokens (UUID, token, validity) values ('" + principalID.ToString() + + "', '" + token + "', datetime('now, 'localtime', '+" + lifetime.ToString() + " minutes'))"); + + if (ExecuteNonQuery(cmd) > 0) + { + cmd.Dispose(); + return true; + } + + cmd.Dispose(); + return false; + } + + public bool CheckToken(UUID principalID, string token, int lifetime) + { + if (System.Environment.TickCount - m_LastExpire > 30000) + DoExpire(); + + SqliteCommand cmd = new SqliteCommand("update tokens set validity = datetime('now, 'localtime', '+" + lifetime.ToString() + + " minutes') where UUID = '" + principalID.ToString() + "' and token = '" + token + "' and validity > datetime('now', 'localtime')"); + + if (ExecuteNonQuery(cmd) > 0) + { + cmd.Dispose(); + return true; + } + + cmd.Dispose(); + + return false; + } + + private void DoExpire() + { + SqliteCommand cmd = new SqliteCommand("delete from tokens where validity < datetime('now, 'localtime')"); + ExecuteNonQuery(cmd); + + cmd.Dispose(); + + m_LastExpire = System.Environment.TickCount; + } + } +} diff --git a/OpenSim/Data/SQLite/SQLiteFramework.cs b/OpenSim/Data/SQLite/SQLiteFramework.cs index 12b2750..d745c92 100644 --- a/OpenSim/Data/SQLite/SQLiteFramework.cs +++ b/OpenSim/Data/SQLite/SQLiteFramework.cs @@ -40,12 +40,17 @@ namespace OpenSim.Data.SQLite /// public class SQLiteFramework { - protected SqliteConnection m_Connection; + protected static SqliteConnection m_Connection; + private bool m_initialized; protected SQLiteFramework(string connectionString) { - m_Connection = new SqliteConnection(connectionString); - m_Connection.Open(); + if (!m_initialized) + { + m_Connection = new SqliteConnection(connectionString); + m_Connection.Open(); + m_initialized = true; + } } ////////////////////////////////////////////////////////////// diff --git a/OpenSim/Data/SQLite/SQLiteGenericTableHandler.cs b/OpenSim/Data/SQLite/SQLiteGenericTableHandler.cs index 8e91693..d29efa0 100644 --- a/OpenSim/Data/SQLite/SQLiteGenericTableHandler.cs +++ b/OpenSim/Data/SQLite/SQLiteGenericTableHandler.cs @@ -48,16 +48,23 @@ namespace OpenSim.Data.SQLite protected string m_Realm; protected FieldInfo m_DataField = null; + private static bool m_initialized; + public SQLiteGenericTableHandler(string connectionString, string realm, string storeName) : base(connectionString) { m_Realm = realm; - if (storeName != String.Empty) + + if (!m_initialized) { - Assembly assem = GetType().Assembly; + if (storeName != String.Empty) + { + Assembly assem = GetType().Assembly; - Migration m = new Migration(m_Connection, assem, storeName); - m.Update(); + Migration m = new Migration(m_Connection, assem, storeName); + m.Update(); + } + m_initialized = true; } Type t = typeof(T); diff --git a/OpenSim/Data/SQLite/SQLiteUserAccountData.cs b/OpenSim/Data/SQLite/SQLiteUserAccountData.cs new file mode 100644 index 0000000..50e8c23 --- /dev/null +++ b/OpenSim/Data/SQLite/SQLiteUserAccountData.cs @@ -0,0 +1,81 @@ +/* + * 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; +using System.Collections.Generic; +using System.Data; +using OpenMetaverse; +using OpenSim.Framework; +using Mono.Data.SqliteClient; + +namespace OpenSim.Data.SQLite +{ + public class SQLiteUserAccountData : SQLiteGenericTableHandler, IUserAccountData + { + public SQLiteUserAccountData(string connectionString, string realm) + : base(connectionString, realm, "UserAccount") + { + } + + public UserAccountData[] GetUsers(UUID scopeID, string query) + { + string[] words = query.Split(new char[] {' '}); + + for (int i = 0 ; i < words.Length ; i++) + { + if (words[i].Length < 3) + { + if (i != words.Length - 1) + Array.Copy(words, i + 1, words, i, words.Length - i - 1); + Array.Resize(ref words, words.Length - 1); + } + } + + if (words.Length == 0) + return new UserAccountData[0]; + + if (words.Length > 2) + return new UserAccountData[0]; + + SqliteCommand cmd = new SqliteCommand(); + + if (words.Length == 1) + { + cmd.CommandText = String.Format("select * from {0} where ScopeID='{1}' or ScopeID='00000000-0000-0000-0000-000000000000') and (FirstName like '{2}%' or LastName like '{2}%')", + m_Realm, scopeID.ToString(), words[0]); + } + else + { + cmd.CommandText = String.Format("select * from {0} where (ScopeID='{1}' or ScopeID='00000000-0000-0000-0000-000000000000') and (FirstName like '{2}%' or LastName like '{3}%')", + m_Realm, scopeID.ToString(), words[0], words[1]); + } + + return DoQuery(cmd); + } + } +} -- cgit v1.1 From df59d098b319367394bdeb898d7fd7dacd7104ec Mon Sep 17 00:00:00 2001 From: Diva Canto Date: Sat, 20 Feb 2010 20:13:38 -0800 Subject: SQLite connector better, but access to tables still doesn't work. --- OpenSim/Data/SQLite/SQLiteAuthenticationData.cs | 61 ++++++++++++++++++------ OpenSim/Data/SQLite/SQLiteFramework.cs | 13 ++--- OpenSim/Data/SQLite/SQLiteGenericTableHandler.cs | 16 ++++--- 3 files changed, 62 insertions(+), 28 deletions(-) (limited to 'OpenSim/Data/SQLite') diff --git a/OpenSim/Data/SQLite/SQLiteAuthenticationData.cs b/OpenSim/Data/SQLite/SQLiteAuthenticationData.cs index 271ed47..7dab6bf 100644 --- a/OpenSim/Data/SQLite/SQLiteAuthenticationData.cs +++ b/OpenSim/Data/SQLite/SQLiteAuthenticationData.cs @@ -52,12 +52,16 @@ namespace OpenSim.Data.SQLite if (!m_initialized) { + m_Connection = new SqliteConnection(connectionString); + m_Connection.Open(); + using (SqliteConnection dbcon = new SqliteConnection(m_connectionString)) { dbcon.Open(); Migration m = new Migration(dbcon, GetType().Assembly, "AuthStore"); m.Update(); } + m_initialized = true; } } @@ -105,7 +109,7 @@ namespace OpenSim.Data.SQLite } public bool Store(AuthenticationData data) - { + { if (data.Data.ContainsKey("UUID")) data.Data.Remove("UUID"); @@ -117,31 +121,60 @@ namespace OpenSim.Data.SQLite SqliteCommand cmd = new SqliteCommand(); - string update = "update `"+m_Realm+"` set "; - bool first = true; - foreach (string field in fields) + if (Get(data.PrincipalID) != null) { - if (!first) - update += ", "; - update += "`" + field + "` = " + data.Data[field]; - first = false; - } + string update = "update `" + m_Realm + "` set "; + bool first = true; + foreach (string field in fields) + { + if (!first) + update += ", "; + update += "`" + field + "` = '" + data.Data[field] + "'"; - update += " where UUID = '" + data.PrincipalID.ToString() + "'"; + first = false; - cmd.CommandText = update; + } + + update += " where UUID = '" + data.PrincipalID.ToString() + "'"; + + cmd.CommandText = update; + Console.WriteLine("XXX " + cmd.CommandText); + try + { + if (ExecuteNonQuery(cmd) < 1) + { + cmd.Dispose(); + return false; + } + } + catch + { + cmd.Dispose(); + return false; + } + } - if (ExecuteNonQuery(cmd) < 1) + else { string insert = "insert into `" + m_Realm + "` (`UUID`, `" + String.Join("`, `", fields) + - "`) values ('" + data.PrincipalID.ToString() + "', " + String.Join(", '", values) + "')"; + "`) values ('" + data.PrincipalID.ToString() + "', '" + String.Join("', '", values) + "')"; cmd.CommandText = insert; - if (ExecuteNonQuery(cmd) < 1) + Console.WriteLine("XXX " + cmd.CommandText); + + try + { + if (ExecuteNonQuery(cmd) < 1) + { + cmd.Dispose(); + return false; + } + } + catch { cmd.Dispose(); return false; diff --git a/OpenSim/Data/SQLite/SQLiteFramework.cs b/OpenSim/Data/SQLite/SQLiteFramework.cs index d745c92..2a8a022 100644 --- a/OpenSim/Data/SQLite/SQLiteFramework.cs +++ b/OpenSim/Data/SQLite/SQLiteFramework.cs @@ -40,17 +40,12 @@ namespace OpenSim.Data.SQLite /// public class SQLiteFramework { - protected static SqliteConnection m_Connection; - private bool m_initialized; + protected SqliteConnection m_Connection; protected SQLiteFramework(string connectionString) { - if (!m_initialized) - { - m_Connection = new SqliteConnection(connectionString); - m_Connection.Open(); - m_initialized = true; - } + //m_Connection = new SqliteConnection(connectionString); + //m_Connection.Open(); } ////////////////////////////////////////////////////////////// @@ -63,6 +58,7 @@ namespace OpenSim.Data.SQLite lock (m_Connection) { cmd.Connection = m_Connection; + Console.WriteLine("XXX " + cmd.CommandText); return cmd.ExecuteNonQuery(); } @@ -75,6 +71,7 @@ namespace OpenSim.Data.SQLite newConnection.Open(); cmd.Connection = newConnection; + Console.WriteLine("XXX " + cmd.CommandText); return cmd.ExecuteReader(); } diff --git a/OpenSim/Data/SQLite/SQLiteGenericTableHandler.cs b/OpenSim/Data/SQLite/SQLiteGenericTableHandler.cs index d29efa0..98943a0 100644 --- a/OpenSim/Data/SQLite/SQLiteGenericTableHandler.cs +++ b/OpenSim/Data/SQLite/SQLiteGenericTableHandler.cs @@ -57,6 +57,9 @@ namespace OpenSim.Data.SQLite if (!m_initialized) { + m_Connection = new SqliteConnection(connectionString); + m_Connection.Open(); + if (storeName != String.Empty) { Assembly assem = GetType().Assembly; @@ -64,6 +67,7 @@ namespace OpenSim.Data.SQLite Migration m = new Migration(m_Connection, assem, storeName); m.Update(); } + m_initialized = true; } @@ -117,7 +121,7 @@ namespace OpenSim.Data.SQLite for (int i = 0 ; i < fields.Length ; i++) { cmd.Parameters.Add(new SqliteParameter(":" + fields[i], keys[i])); - terms.Add("`" + fields[i] + "` = :" + fields[i]); + terms.Add("`" + fields[i] + "`='" + keys[i] + "'"); } string where = String.Join(" and ", terms.ToArray()); @@ -215,8 +219,8 @@ namespace OpenSim.Data.SQLite foreach (FieldInfo fi in m_Fields.Values) { names.Add(fi.Name); - values.Add(":" + fi.Name); - cmd.Parameters.Add(new SqliteParameter(":" + fi.Name, fi.GetValue(row).ToString())); + values.Add(fi.GetValue(row).ToString()); + cmd.Parameters.Add(new SqliteParameter(fi.Name, fi.GetValue(row).ToString())); } if (m_DataField != null) @@ -227,12 +231,12 @@ namespace OpenSim.Data.SQLite foreach (KeyValuePair kvp in data) { names.Add(kvp.Key); - values.Add(":" + kvp.Key); - cmd.Parameters.Add(new SqliteParameter(":" + kvp.Key, kvp.Value)); + values.Add(kvp.Value); + cmd.Parameters.Add(new SqliteParameter(kvp.Key, kvp.Value)); } } - query = String.Format("replace into {0} (`", m_Realm) + String.Join("`,`", names.ToArray()) + "`) values (" + String.Join(",", values.ToArray()) + ")"; + query = String.Format("replace into {0} (`", m_Realm) + String.Join("`,`", names.ToArray()) + "`) values ('" + String.Join("', '", values.ToArray()) + "')"; cmd.CommandText = query; -- cgit v1.1 From 611eeb583c2e8c7a750201bc10c535410e74330f Mon Sep 17 00:00:00 2001 From: Diva Canto Date: Sat, 20 Feb 2010 20:59:04 -0800 Subject: Reverted SQLite/SQLiteGenericTableHandler to what it was + singleton. --- OpenSim/Data/SQLite/SQLiteGenericTableHandler.cs | 12 ++++++------ 1 file changed, 6 insertions(+), 6 deletions(-) (limited to 'OpenSim/Data/SQLite') diff --git a/OpenSim/Data/SQLite/SQLiteGenericTableHandler.cs b/OpenSim/Data/SQLite/SQLiteGenericTableHandler.cs index 98943a0..e7e158d 100644 --- a/OpenSim/Data/SQLite/SQLiteGenericTableHandler.cs +++ b/OpenSim/Data/SQLite/SQLiteGenericTableHandler.cs @@ -121,7 +121,7 @@ namespace OpenSim.Data.SQLite for (int i = 0 ; i < fields.Length ; i++) { cmd.Parameters.Add(new SqliteParameter(":" + fields[i], keys[i])); - terms.Add("`" + fields[i] + "`='" + keys[i] + "'"); + terms.Add("`" + fields[i] + "` = :" + fields[i]); } string where = String.Join(" and ", terms.ToArray()); @@ -219,8 +219,8 @@ namespace OpenSim.Data.SQLite foreach (FieldInfo fi in m_Fields.Values) { names.Add(fi.Name); - values.Add(fi.GetValue(row).ToString()); - cmd.Parameters.Add(new SqliteParameter(fi.Name, fi.GetValue(row).ToString())); + values.Add(":" + fi.Name); + cmd.Parameters.Add(new SqliteParameter(":" + fi.Name, fi.GetValue(row).ToString())); } if (m_DataField != null) @@ -231,12 +231,12 @@ namespace OpenSim.Data.SQLite foreach (KeyValuePair kvp in data) { names.Add(kvp.Key); - values.Add(kvp.Value); - cmd.Parameters.Add(new SqliteParameter(kvp.Key, kvp.Value)); + values.Add(":" + kvp.Key); + cmd.Parameters.Add(new SqliteParameter(":" + kvp.Key, kvp.Value)); } } - query = String.Format("replace into {0} (`", m_Realm) + String.Join("`,`", names.ToArray()) + "`) values ('" + String.Join("', '", values.ToArray()) + "')"; + query = String.Format("replace into {0} (`", m_Realm) + String.Join("`,`", names.ToArray()) + "`) values (" + String.Join(",", values.ToArray()) + ")"; cmd.CommandText = query; -- cgit v1.1 From d761d1624b28a9ab3b006b3a3a94f4b805550f8c Mon Sep 17 00:00:00 2001 From: Melanie Date: Sun, 21 Feb 2010 04:20:22 +0000 Subject: Fix SQLite locking and make it more fascist for now --- OpenSim/Data/SQLite/SQLiteFramework.cs | 36 +++++++++++++++++++++------------- 1 file changed, 22 insertions(+), 14 deletions(-) (limited to 'OpenSim/Data/SQLite') diff --git a/OpenSim/Data/SQLite/SQLiteFramework.cs b/OpenSim/Data/SQLite/SQLiteFramework.cs index 2a8a022..96764f3 100644 --- a/OpenSim/Data/SQLite/SQLiteFramework.cs +++ b/OpenSim/Data/SQLite/SQLiteFramework.cs @@ -40,12 +40,10 @@ namespace OpenSim.Data.SQLite /// public class SQLiteFramework { - protected SqliteConnection m_Connection; + protected Object m_lockObject = new Object(); protected SQLiteFramework(string connectionString) { - //m_Connection = new SqliteConnection(connectionString); - //m_Connection.Open(); } ////////////////////////////////////////////////////////////// @@ -55,9 +53,13 @@ namespace OpenSim.Data.SQLite // protected int ExecuteNonQuery(SqliteCommand cmd) { - lock (m_Connection) + lock (m_lockObject) { - cmd.Connection = m_Connection; + SqliteConnection newConnection = + (SqliteConnection)((ICloneable)m_Connection).Clone(); + newConnection.Open(); + + cmd.Connection = newConnection; Console.WriteLine("XXX " + cmd.CommandText); return cmd.ExecuteNonQuery(); @@ -66,20 +68,26 @@ namespace OpenSim.Data.SQLite protected IDataReader ExecuteReader(SqliteCommand cmd) { - SqliteConnection newConnection = - (SqliteConnection)((ICloneable)m_Connection).Clone(); - newConnection.Open(); + lock (m_lockObject) + { + SqliteConnection newConnection = + (SqliteConnection)((ICloneable)m_Connection).Clone(); + newConnection.Open(); - cmd.Connection = newConnection; - Console.WriteLine("XXX " + cmd.CommandText); - return cmd.ExecuteReader(); + cmd.Connection = newConnection; + Console.WriteLine("XXX " + cmd.CommandText); + return cmd.ExecuteReader(); + } } protected void CloseReaderCommand(SqliteCommand cmd) { - cmd.Connection.Close(); - cmd.Connection.Dispose(); - cmd.Dispose(); + lock (m_lockObject) + { + cmd.Connection.Close(); + cmd.Connection.Dispose(); + cmd.Dispose(); + } } } } -- cgit v1.1 From 56fb7821ad021879d005da5ba65901c29c10de7f Mon Sep 17 00:00:00 2001 From: Diva Canto Date: Sat, 20 Feb 2010 21:24:18 -0800 Subject: Restored mising m_Connection. --- OpenSim/Data/SQLite/SQLiteAuthenticationData.cs | 10 ++++++---- OpenSim/Data/SQLite/SQLiteFramework.cs | 1 + 2 files changed, 7 insertions(+), 4 deletions(-) (limited to 'OpenSim/Data/SQLite') diff --git a/OpenSim/Data/SQLite/SQLiteAuthenticationData.cs b/OpenSim/Data/SQLite/SQLiteAuthenticationData.cs index 7dab6bf..d71c7eb 100644 --- a/OpenSim/Data/SQLite/SQLiteAuthenticationData.cs +++ b/OpenSim/Data/SQLite/SQLiteAuthenticationData.cs @@ -57,8 +57,8 @@ namespace OpenSim.Data.SQLite using (SqliteConnection dbcon = new SqliteConnection(m_connectionString)) { - dbcon.Open(); - Migration m = new Migration(dbcon, GetType().Assembly, "AuthStore"); + //dbcon.Open(); + Migration m = new Migration(m_Connection, GetType().Assembly, "AuthStore"); m.Update(); } @@ -149,8 +149,9 @@ namespace OpenSim.Data.SQLite return false; } } - catch + catch (Exception e) { + Console.WriteLine(e.ToString()); cmd.Dispose(); return false; } @@ -174,8 +175,9 @@ namespace OpenSim.Data.SQLite return false; } } - catch + catch (Exception e) { + Console.WriteLine(e.ToString()); cmd.Dispose(); return false; } diff --git a/OpenSim/Data/SQLite/SQLiteFramework.cs b/OpenSim/Data/SQLite/SQLiteFramework.cs index 96764f3..54b104b 100644 --- a/OpenSim/Data/SQLite/SQLiteFramework.cs +++ b/OpenSim/Data/SQLite/SQLiteFramework.cs @@ -42,6 +42,7 @@ namespace OpenSim.Data.SQLite { protected Object m_lockObject = new Object(); + protected static SqliteConnection m_Connection; protected SQLiteFramework(string connectionString) { } -- cgit v1.1 From 8a4947f8c75a2dbcd8c13dbff9ad2eff52711f0e Mon Sep 17 00:00:00 2001 From: Diva Canto Date: Sun, 21 Feb 2010 08:47:24 -0800 Subject: SQLite connector for UserAccounts and Auth works. Yey! --- OpenSim/Data/SQLite/SQLiteAuthenticationData.cs | 69 ++++++++++++++---------- OpenSim/Data/SQLite/SQLiteFramework.cs | 31 +++++------ OpenSim/Data/SQLite/SQLiteGenericTableHandler.cs | 16 ++++-- OpenSim/Data/SQLite/SQLiteXInventoryData.cs | 6 +-- 4 files changed, 69 insertions(+), 53 deletions(-) (limited to 'OpenSim/Data/SQLite') diff --git a/OpenSim/Data/SQLite/SQLiteAuthenticationData.cs b/OpenSim/Data/SQLite/SQLiteAuthenticationData.cs index d71c7eb..84ce775 100644 --- a/OpenSim/Data/SQLite/SQLiteAuthenticationData.cs +++ b/OpenSim/Data/SQLite/SQLiteAuthenticationData.cs @@ -42,6 +42,7 @@ namespace OpenSim.Data.SQLite private int m_LastExpire; private string m_connectionString; + protected static SqliteConnection m_Connection; private static bool m_initialized = false; public SQLiteAuthenticationData(string connectionString, string realm) @@ -55,11 +56,12 @@ namespace OpenSim.Data.SQLite m_Connection = new SqliteConnection(connectionString); m_Connection.Open(); - using (SqliteConnection dbcon = new SqliteConnection(m_connectionString)) + using (SqliteConnection dbcon = (SqliteConnection)((ICloneable)m_Connection).Clone()) { - //dbcon.Open(); - Migration m = new Migration(m_Connection, GetType().Assembly, "AuthStore"); + dbcon.Open(); + Migration m = new Migration(dbcon, GetType().Assembly, "AuthStore"); m.Update(); + dbcon.Close(); } m_initialized = true; @@ -71,13 +73,13 @@ namespace OpenSim.Data.SQLite AuthenticationData ret = new AuthenticationData(); ret.Data = new Dictionary(); - using (SqliteConnection dbcon = new SqliteConnection(m_connectionString)) - { - dbcon.Open(); - SqliteCommand cmd = new SqliteCommand("select * from `" + m_Realm + "` where UUID = '" + principalID.ToString() + "'", dbcon); + SqliteCommand cmd = new SqliteCommand("select * from `" + m_Realm + "` where UUID = :PrincipalID"); + cmd.Parameters.Add(new SqliteParameter(":PrincipalID", principalID.ToString())); - IDataReader result = cmd.ExecuteReader(); + IDataReader result = ExecuteReader(cmd, m_Connection); + try + { if (result.Read()) { ret.PrincipalID = principalID; @@ -106,6 +108,15 @@ namespace OpenSim.Data.SQLite return null; } } + catch + { + } + finally + { + CloseCommand(cmd); + } + + return null; } public bool Store(AuthenticationData data) @@ -131,28 +142,28 @@ namespace OpenSim.Data.SQLite { if (!first) update += ", "; - update += "`" + field + "` = '" + data.Data[field] + "'"; + update += "`" + field + "` = :" + field; + cmd.Parameters.Add(new SqliteParameter(":" + field, data.Data[field])); first = false; - } - update += " where UUID = '" + data.PrincipalID.ToString() + "'"; + update += " where UUID = :UUID"; + cmd.Parameters.Add(new SqliteParameter(":UUID", data.PrincipalID.ToString())); cmd.CommandText = update; - Console.WriteLine("XXX " + cmd.CommandText); try { - if (ExecuteNonQuery(cmd) < 1) + if (ExecuteNonQuery(cmd, m_Connection) < 1) { - cmd.Dispose(); + CloseCommand(cmd); return false; } } catch (Exception e) { Console.WriteLine(e.ToString()); - cmd.Dispose(); + CloseCommand(cmd); return false; } } @@ -161,29 +172,31 @@ namespace OpenSim.Data.SQLite { string insert = "insert into `" + m_Realm + "` (`UUID`, `" + String.Join("`, `", fields) + - "`) values ('" + data.PrincipalID.ToString() + "', '" + String.Join("', '", values) + "')"; + "`) values (:UUID, :" + String.Join(", :", fields) + ")"; - cmd.CommandText = insert; + cmd.Parameters.Add(new SqliteParameter(":UUID", data.PrincipalID.ToString())); + foreach (string field in fields) + cmd.Parameters.Add(new SqliteParameter(":" + field, data.Data[field])); - Console.WriteLine("XXX " + cmd.CommandText); + cmd.CommandText = insert; try { - if (ExecuteNonQuery(cmd) < 1) + if (ExecuteNonQuery(cmd, m_Connection) < 1) { - cmd.Dispose(); + CloseCommand(cmd); return false; } } catch (Exception e) { Console.WriteLine(e.ToString()); - cmd.Dispose(); + CloseCommand(cmd); return false; } } - cmd.Dispose(); + CloseCommand(cmd); return true; } @@ -193,7 +206,7 @@ namespace OpenSim.Data.SQLite SqliteCommand cmd = new SqliteCommand("update `" + m_Realm + "` set `" + item + "` = " + value + " where UUID = '" + principalID.ToString() + "'"); - if (ExecuteNonQuery(cmd) > 0) + if (ExecuteNonQuery(cmd, m_Connection) > 0) return true; return false; @@ -205,9 +218,9 @@ namespace OpenSim.Data.SQLite DoExpire(); SqliteCommand cmd = new SqliteCommand("insert into tokens (UUID, token, validity) values ('" + principalID.ToString() + - "', '" + token + "', datetime('now, 'localtime', '+" + lifetime.ToString() + " minutes'))"); + "', '" + token + "', datetime('now', 'localtime', '+" + lifetime.ToString() + " minutes'))"); - if (ExecuteNonQuery(cmd) > 0) + if (ExecuteNonQuery(cmd, m_Connection) > 0) { cmd.Dispose(); return true; @@ -225,7 +238,7 @@ namespace OpenSim.Data.SQLite SqliteCommand cmd = new SqliteCommand("update tokens set validity = datetime('now, 'localtime', '+" + lifetime.ToString() + " minutes') where UUID = '" + principalID.ToString() + "' and token = '" + token + "' and validity > datetime('now', 'localtime')"); - if (ExecuteNonQuery(cmd) > 0) + if (ExecuteNonQuery(cmd, m_Connection) > 0) { cmd.Dispose(); return true; @@ -238,8 +251,8 @@ namespace OpenSim.Data.SQLite private void DoExpire() { - SqliteCommand cmd = new SqliteCommand("delete from tokens where validity < datetime('now, 'localtime')"); - ExecuteNonQuery(cmd); + SqliteCommand cmd = new SqliteCommand("delete from tokens where validity < datetime('now', 'localtime')"); + ExecuteNonQuery(cmd, m_Connection); cmd.Dispose(); diff --git a/OpenSim/Data/SQLite/SQLiteFramework.cs b/OpenSim/Data/SQLite/SQLiteFramework.cs index 54b104b..20b5085 100644 --- a/OpenSim/Data/SQLite/SQLiteFramework.cs +++ b/OpenSim/Data/SQLite/SQLiteFramework.cs @@ -42,7 +42,6 @@ namespace OpenSim.Data.SQLite { protected Object m_lockObject = new Object(); - protected static SqliteConnection m_Connection; protected SQLiteFramework(string connectionString) { } @@ -52,43 +51,41 @@ namespace OpenSim.Data.SQLite // All non queries are funneled through one connection // to increase performance a little // - protected int ExecuteNonQuery(SqliteCommand cmd) + protected int ExecuteNonQuery(SqliteCommand cmd, SqliteConnection connection) { - lock (m_lockObject) + lock (connection) { SqliteConnection newConnection = - (SqliteConnection)((ICloneable)m_Connection).Clone(); + (SqliteConnection)((ICloneable)connection).Clone(); newConnection.Open(); cmd.Connection = newConnection; - Console.WriteLine("XXX " + cmd.CommandText); + //Console.WriteLine("XXX " + cmd.CommandText); return cmd.ExecuteNonQuery(); } } - - protected IDataReader ExecuteReader(SqliteCommand cmd) + + protected IDataReader ExecuteReader(SqliteCommand cmd, SqliteConnection connection) { - lock (m_lockObject) + lock (connection) { SqliteConnection newConnection = - (SqliteConnection)((ICloneable)m_Connection).Clone(); + (SqliteConnection)((ICloneable)connection).Clone(); newConnection.Open(); cmd.Connection = newConnection; - Console.WriteLine("XXX " + cmd.CommandText); + //Console.WriteLine("XXX " + cmd.CommandText); + return cmd.ExecuteReader(); } } - protected void CloseReaderCommand(SqliteCommand cmd) + protected void CloseCommand(SqliteCommand cmd) { - lock (m_lockObject) - { - cmd.Connection.Close(); - cmd.Connection.Dispose(); - cmd.Dispose(); - } + cmd.Connection.Close(); + cmd.Connection.Dispose(); + cmd.Dispose(); } } } diff --git a/OpenSim/Data/SQLite/SQLiteGenericTableHandler.cs b/OpenSim/Data/SQLite/SQLiteGenericTableHandler.cs index e7e158d..b39bb19 100644 --- a/OpenSim/Data/SQLite/SQLiteGenericTableHandler.cs +++ b/OpenSim/Data/SQLite/SQLiteGenericTableHandler.cs @@ -48,6 +48,7 @@ namespace OpenSim.Data.SQLite protected string m_Realm; protected FieldInfo m_DataField = null; + protected static SqliteConnection m_Connection; private static bool m_initialized; public SQLiteGenericTableHandler(string connectionString, @@ -63,9 +64,14 @@ namespace OpenSim.Data.SQLite if (storeName != String.Empty) { Assembly assem = GetType().Assembly; + SqliteConnection newConnection = + (SqliteConnection)((ICloneable)m_Connection).Clone(); + newConnection.Open(); - Migration m = new Migration(m_Connection, assem, storeName); + Migration m = new Migration(newConnection, assem, storeName); m.Update(); + newConnection.Close(); + newConnection.Dispose(); } m_initialized = true; @@ -136,7 +142,7 @@ namespace OpenSim.Data.SQLite protected T[] DoQuery(SqliteCommand cmd) { - IDataReader reader = ExecuteReader(cmd); + IDataReader reader = ExecuteReader(cmd, m_Connection); if (reader == null) return new T[0]; @@ -191,7 +197,7 @@ namespace OpenSim.Data.SQLite result.Add(row); } - CloseReaderCommand(cmd); + CloseCommand(cmd); return result.ToArray(); } @@ -240,7 +246,7 @@ namespace OpenSim.Data.SQLite cmd.CommandText = query; - if (ExecuteNonQuery(cmd) > 0) + if (ExecuteNonQuery(cmd, m_Connection) > 0) return true; return false; @@ -253,7 +259,7 @@ namespace OpenSim.Data.SQLite cmd.CommandText = String.Format("delete from {0} where `{1}` = :{1}", m_Realm, field); cmd.Parameters.Add(new SqliteParameter(field, val)); - if (ExecuteNonQuery(cmd) > 0) + if (ExecuteNonQuery(cmd, m_Connection) > 0) return true; return false; diff --git a/OpenSim/Data/SQLite/SQLiteXInventoryData.cs b/OpenSim/Data/SQLite/SQLiteXInventoryData.cs index 5c93f88..a66e0c6 100644 --- a/OpenSim/Data/SQLite/SQLiteXInventoryData.cs +++ b/OpenSim/Data/SQLite/SQLiteXInventoryData.cs @@ -115,7 +115,7 @@ namespace OpenSim.Data.SQLite cmd.Parameters.Add(new SqliteParameter(":ParentFolderID", newParent)); cmd.Parameters.Add(new SqliteParameter(":InventoryID", id)); - return ExecuteNonQuery(cmd) == 0 ? false : true; + return ExecuteNonQuery(cmd, m_Connection) == 0 ? false : true; } public XInventoryItem[] GetActiveGestures(UUID principalID) @@ -137,7 +137,7 @@ namespace OpenSim.Data.SQLite cmd.Parameters.Add(new SqliteParameter(":PrincipalID", principalID.ToString())); cmd.Parameters.Add(new SqliteParameter(":AssetID", assetID.ToString())); - IDataReader reader = ExecuteReader(cmd); + IDataReader reader = ExecuteReader(cmd, m_Connection); int perms = 0; @@ -147,7 +147,7 @@ namespace OpenSim.Data.SQLite } reader.Close(); - CloseReaderCommand(cmd); + CloseCommand(cmd); return perms; } -- cgit v1.1 From 552e9e8c7832f41f5a53666d9c3ece62f57be4ba Mon Sep 17 00:00:00 2001 From: Diva Canto Date: Sun, 21 Feb 2010 09:09:35 -0800 Subject: * Added SQlite connector for AvatarData. Tested -- works. * Small bug fix in debug message * Set default standalone configs to use SQLite across the board --- OpenSim/Data/SQLite/Resources/001_Avatar.sql | 9 ++++ OpenSim/Data/SQLite/SQLiteAvatarData.cs | 74 ++++++++++++++++++++++++++++ 2 files changed, 83 insertions(+) create mode 100644 OpenSim/Data/SQLite/Resources/001_Avatar.sql create mode 100644 OpenSim/Data/SQLite/SQLiteAvatarData.cs (limited to 'OpenSim/Data/SQLite') diff --git a/OpenSim/Data/SQLite/Resources/001_Avatar.sql b/OpenSim/Data/SQLite/Resources/001_Avatar.sql new file mode 100644 index 0000000..7ec906b --- /dev/null +++ b/OpenSim/Data/SQLite/Resources/001_Avatar.sql @@ -0,0 +1,9 @@ +BEGIN TRANSACTION; + +CREATE TABLE Avatars ( + PrincipalID CHAR(36) NOT NULL, + Name VARCHAR(32) NOT NULL, + Value VARCHAR(255) NOT NULL DEFAULT '', + PRIMARY KEY(PrincipalID, Name)); + +COMMIT; diff --git a/OpenSim/Data/SQLite/SQLiteAvatarData.cs b/OpenSim/Data/SQLite/SQLiteAvatarData.cs new file mode 100644 index 0000000..d0b82de --- /dev/null +++ b/OpenSim/Data/SQLite/SQLiteAvatarData.cs @@ -0,0 +1,74 @@ +/* + * 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 System.Threading; +using log4net; +using OpenMetaverse; +using OpenSim.Framework; +using Mono.Data.SqliteClient; + +namespace OpenSim.Data.SQLite +{ + /// + /// A MySQL Interface for the Grid Server + /// + public class SQLiteAvatarData : SQLiteGenericTableHandler, + IAvatarData + { + private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); + + public SQLiteAvatarData(string connectionString, string realm) : + base(connectionString, realm, "Avatar") + { + } + + public bool Delete(UUID principalID, string name) + { + SqliteCommand cmd = new SqliteCommand(); + + cmd.CommandText = String.Format("delete from {0} where `PrincipalID` = :PrincipalID and `Name` = :Name", m_Realm); + cmd.Parameters.Add(":PrincipalID", principalID.ToString()); + cmd.Parameters.Add(":Name", name); + + try + { + if (ExecuteNonQuery(cmd, m_Connection) > 0) + return true; + + return false; + } + finally + { + CloseCommand(cmd); + } + } + } +} -- cgit v1.1 From bd5a4dab0c06bf7faf05df5be7a1c93c5e025724 Mon Sep 17 00:00:00 2001 From: Diva Canto Date: Sun, 21 Feb 2010 09:39:12 -0800 Subject: Bug fixes on field names in order to make data import work from old users table to new UserAccounts table. --- OpenSim/Data/SQLite/Resources/002_UserAccount.sql | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'OpenSim/Data/SQLite') diff --git a/OpenSim/Data/SQLite/Resources/002_UserAccount.sql b/OpenSim/Data/SQLite/Resources/002_UserAccount.sql index c0b3d7b..c7a6293 100644 --- a/OpenSim/Data/SQLite/Resources/002_UserAccount.sql +++ b/OpenSim/Data/SQLite/Resources/002_UserAccount.sql @@ -1,5 +1,5 @@ BEGIN TRANSACTION; -INSERT INTO UserAccounts (PrincipalID, 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; +INSERT INTO UserAccounts (PrincipalID, ScopeID, FirstName, LastName, Email, ServiceURLs, Created) SELECT `UUID` AS PrincipalID, '00000000-0000-0000-0000-000000000000' AS ScopeID, username AS FirstName, surname AS LastName, '' as Email, '' AS ServiceURLs, created as Created FROM users; COMMIT; -- cgit v1.1 From bb171717ceaef37b022a135209c2e0bf031d21f9 Mon Sep 17 00:00:00 2001 From: Diva Canto Date: Sun, 21 Feb 2010 15:38:52 -0800 Subject: Deleted obsolete files in the Data layer. Compiles. --- OpenSim/Data/SQLite/SQLiteGridData.cs | 286 ------ OpenSim/Data/SQLite/SQLiteManager.cs | 225 ----- OpenSim/Data/SQLite/SQLiteUserData.cs | 1262 --------------------------- OpenSim/Data/SQLite/Tests/SQLiteUserTest.cs | 64 -- 4 files changed, 1837 deletions(-) delete mode 100644 OpenSim/Data/SQLite/SQLiteGridData.cs delete mode 100644 OpenSim/Data/SQLite/SQLiteManager.cs delete mode 100644 OpenSim/Data/SQLite/SQLiteUserData.cs delete mode 100644 OpenSim/Data/SQLite/Tests/SQLiteUserTest.cs (limited to 'OpenSim/Data/SQLite') diff --git a/OpenSim/Data/SQLite/SQLiteGridData.cs b/OpenSim/Data/SQLite/SQLiteGridData.cs deleted file mode 100644 index 18abb88..0000000 --- a/OpenSim/Data/SQLite/SQLiteGridData.cs +++ /dev/null @@ -1,286 +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 OpenMetaverse; -using OpenSim.Framework; - -namespace OpenSim.Data.SQLite -{ - /// - /// A Grid Interface to the SQLite database - /// - public class SQLiteGridData : GridDataBase - { - private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); - - /// - /// SQLite database manager - /// - private SQLiteManager database; - - override public void Initialise() - { - m_log.Info("[SQLite]: " + Name + " cannot be default-initialized!"); - throw new PluginNotInitialisedException (Name); - } - - /// - /// - /// Initialises Inventory interface - /// Loads and initialises a new SQLite connection and maintains it. - /// use default URI if connect string is empty. - /// - /// - /// connect string - override public void Initialise(string connect) - { - database = new SQLiteManager(connect); - } - - /// - /// Shuts down the grid interface - /// - override public void Dispose() - { - database.Close(); - } - - /// - /// Returns the name of this grid interface - /// - /// A string containing the grid interface - override public string Name - { - get { return "SQLite OpenGridData"; } - } - - /// - /// Returns the version of this grid interface - /// - /// A string containing the version - override public string Version - { - get { return "0.1"; } - } - - /// - /// Returns a list of regions within the specified ranges - /// - /// minimum X coordinate - /// minimum Y coordinate - /// maximum X coordinate - /// maximum Y coordinate - /// An array of region profiles - /// NOT IMPLEMENTED ? always return null - override public RegionProfileData[] GetProfilesInRange(uint a, uint b, uint c, uint d) - { - return null; - } - - - /// - /// Returns up to maxNum profiles of regions that have a name starting with namePrefix - /// - /// The name to match against - /// Maximum number of profiles to return - /// A list of sim profiles - override public List GetRegionsByName (string namePrefix, uint maxNum) - { - return null; - } - - /// - /// Returns a sim profile from it's handle - /// - /// Region location handle - /// Sim profile - override public RegionProfileData GetProfileByHandle(ulong handle) - { - Dictionary param = new Dictionary(); - param["handle"] = handle.ToString(); - - IDbCommand result = database.Query("SELECT * FROM regions WHERE handle = @handle", param); - IDataReader reader = result.ExecuteReader(); - - RegionProfileData row = database.getRow(reader); - reader.Close(); - result.Dispose(); - - return row; - } - - /// - /// Returns a sim profile from it's Region name string - /// - /// The region name search query - /// The sim profile - override public RegionProfileData GetProfileByString(string regionName) - { - if (regionName.Length > 2) - { - Dictionary param = new Dictionary(); - // Add % because this is a like query. - param["?regionName"] = regionName + "%"; - // Only returns one record or no record. - IDbCommand result = database.Query("SELECT * FROM regions WHERE regionName like ?regionName LIMIT 1", param); - IDataReader reader = result.ExecuteReader(); - - RegionProfileData row = database.getRow(reader); - reader.Close(); - result.Dispose(); - - return row; - } - else - { - //m_log.Error("[DATABASE]: Searched for a Region Name shorter then 3 characters"); - return null; - } - } - - /// - /// Returns a sim profile from it's UUID - /// - /// The region UUID - /// The sim profile - override public RegionProfileData GetProfileByUUID(UUID uuid) - { - Dictionary param = new Dictionary(); - param["uuid"] = uuid.ToString(); - - IDbCommand result = database.Query("SELECT * FROM regions WHERE uuid = @uuid", param); - IDataReader reader = result.ExecuteReader(); - - RegionProfileData row = database.getRow(reader); - reader.Close(); - result.Dispose(); - - return row; - } - - /// - /// Returns a list of avatar and UUIDs that match the query - /// - /// do nothing yet - public List GeneratePickerResults(UUID queryID, string query) - { - //Do nothing yet - List returnlist = new List(); - return returnlist; - } - - /// - /// Adds a new specified region to the database - /// - /// The profile to add - /// A dataresponse enum indicating success - override public DataResponse StoreProfile(RegionProfileData profile) - { - if (database.insertRow(profile)) - { - return DataResponse.RESPONSE_OK; - } - else - { - return DataResponse.RESPONSE_ERROR; - } - } - - /// - /// Deletes a sim profile from the database - /// - /// the sim UUID - /// Successful? - override public DataResponse DeleteProfile(string uuid) - { - Dictionary param = new Dictionary(); - param["uuid"] = uuid; - - IDbCommand result = database.Query("DELETE FROM regions WHERE uuid = @uuid", param); - if (result.ExecuteNonQuery() > 0) - { - return DataResponse.RESPONSE_OK; - } - return DataResponse.RESPONSE_ERROR; - } - - /// - /// DEPRECATED. Attempts to authenticate a region by comparing a shared secret. - /// - /// The UUID of the challenger - /// The attempted regionHandle of the challenger - /// The secret - /// Whether the secret and regionhandle match the database entry for UUID - override public bool AuthenticateSim(UUID uuid, ulong handle, string authkey) - { - bool throwHissyFit = false; // Should be true by 1.0 - - if (throwHissyFit) - throw new Exception("CRYPTOWEAK AUTHENTICATE: Refusing to authenticate due to replay potential."); - - RegionProfileData data = GetProfileByUUID(uuid); - - return (handle == data.regionHandle && authkey == data.regionSecret); - } - - /// - /// NOT YET FUNCTIONAL. Provides a cryptographic authentication of a region - /// - /// This requires a security audit. - /// - /// - /// - /// - /// - public bool AuthenticateSim(UUID uuid, ulong handle, string authhash, string challenge) - { - // SHA512Managed HashProvider = new SHA512Managed(); - // Encoding TextProvider = new UTF8Encoding(); - - // byte[] stream = TextProvider.GetBytes(uuid.ToString() + ":" + handle.ToString() + ":" + challenge); - // byte[] hash = HashProvider.ComputeHash(stream); - - return false; - } - - /// - /// NOT IMPLEMENTED - /// - /// x coordinate - /// y coordinate - /// always return null - override public ReservationData GetReservationAtPoint(uint x, uint y) - { - return null; - } - } -} diff --git a/OpenSim/Data/SQLite/SQLiteManager.cs b/OpenSim/Data/SQLite/SQLiteManager.cs deleted file mode 100644 index b6d4a1c..0000000 --- a/OpenSim/Data/SQLite/SQLiteManager.cs +++ /dev/null @@ -1,225 +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.Data.SQLite; -using System.Reflection; -using log4net; -using OpenMetaverse; - -namespace OpenSim.Data.SQLite -{ - /// - /// SQLite Manager - /// - internal class SQLiteManager : SQLiteUtil - { - private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); - - private IDbConnection dbcon; - - /// - /// - /// Initialises and creates a new SQLite connection and maintains it. - /// use default URI if connect string is empty. - /// - /// - /// connect string - public SQLiteManager(string connect) - { - try - { - string connectionString = String.Empty; - if (connect != String.Empty) - { - connectionString = connect; - } - else - { - m_log.Warn("[SQLITE] grid db not specified, using default"); - connectionString = "URI=file:GridServerSqlite.db;"; - } - - dbcon = new SQLiteConnection(connectionString); - - dbcon.Open(); - } - catch (Exception e) - { - throw new Exception("Error initialising SQLite Database: " + e.ToString()); - } - } - - /// - /// Shuts down the database connection - /// - public void Close() - { - dbcon.Close(); - dbcon = null; - } - - /// - /// Runs a query with protection against SQL Injection by using parameterised input. - /// - /// The SQL string - replace any variables such as WHERE x = "y" with WHERE x = @y - /// The parameters - index so that @y is indexed as 'y' - /// A SQLite DB Command - public IDbCommand Query(string sql, Dictionary parameters) - { - SQLiteCommand dbcommand = (SQLiteCommand) dbcon.CreateCommand(); - dbcommand.CommandText = sql; - foreach (KeyValuePair param in parameters) - { - SQLiteParameter paramx = new SQLiteParameter(param.Key, param.Value); - dbcommand.Parameters.Add(paramx); - } - - return (IDbCommand) dbcommand; - } - - /// - /// Reads a region row from a database reader - /// - /// An active database reader - /// A region profile - public RegionProfileData getRow(IDataReader reader) - { - RegionProfileData retval = new RegionProfileData(); - - if (reader.Read()) - { - // Region Main - retval.regionHandle = (ulong) reader["regionHandle"]; - retval.regionName = (string) reader["regionName"]; - retval.UUID = new UUID((string) reader["uuid"]); - - // Secrets - retval.regionRecvKey = (string) reader["regionRecvKey"]; - retval.regionSecret = (string) reader["regionSecret"]; - retval.regionSendKey = (string) reader["regionSendKey"]; - - // Region Server - retval.regionDataURI = (string) reader["regionDataURI"]; - retval.regionOnline = false; // Needs to be pinged before this can be set. - retval.serverIP = (string) reader["serverIP"]; - retval.serverPort = (uint) reader["serverPort"]; - retval.serverURI = (string) reader["serverURI"]; - - // Location - retval.regionLocX = (uint) ((int) reader["locX"]); - retval.regionLocY = (uint) ((int) reader["locY"]); - retval.regionLocZ = (uint) ((int) reader["locZ"]); - - // Neighbours - 0 = No Override - retval.regionEastOverrideHandle = (ulong) reader["eastOverrideHandle"]; - retval.regionWestOverrideHandle = (ulong) reader["westOverrideHandle"]; - retval.regionSouthOverrideHandle = (ulong) reader["southOverrideHandle"]; - retval.regionNorthOverrideHandle = (ulong) reader["northOverrideHandle"]; - - // Assets - retval.regionAssetURI = (string) reader["regionAssetURI"]; - retval.regionAssetRecvKey = (string) reader["regionAssetRecvKey"]; - retval.regionAssetSendKey = (string) reader["regionAssetSendKey"]; - - // Userserver - retval.regionUserURI = (string) reader["regionUserURI"]; - retval.regionUserRecvKey = (string) reader["regionUserRecvKey"]; - retval.regionUserSendKey = (string) reader["regionUserSendKey"]; - } - else - { - throw new Exception("No rows to return"); - } - return retval; - } - - /// - /// Inserts a new region into the database - /// - /// The region to insert - /// Success? - public bool insertRow(RegionProfileData profile) - { - string sql = - "REPLACE INTO regions VALUES (regionHandle, regionName, uuid, regionRecvKey, regionSecret, regionSendKey, regionDataURI, "; - sql += - "serverIP, serverPort, serverURI, locX, locY, locZ, eastOverrideHandle, westOverrideHandle, southOverrideHandle, northOverrideHandle, regionAssetURI, regionAssetRecvKey, "; - sql += "regionAssetSendKey, regionUserURI, regionUserRecvKey, regionUserSendKey) VALUES "; - - sql += "(@regionHandle, @regionName, @uuid, @regionRecvKey, @regionSecret, @regionSendKey, @regionDataURI, "; - sql += - "@serverIP, @serverPort, @serverURI, @locX, @locY, @locZ, @eastOverrideHandle, @westOverrideHandle, @southOverrideHandle, @northOverrideHandle, @regionAssetURI, @regionAssetRecvKey, "; - sql += "@regionAssetSendKey, @regionUserURI, @regionUserRecvKey, @regionUserSendKey);"; - - Dictionary parameters = new Dictionary(); - - parameters["regionHandle"] = profile.regionHandle.ToString(); - parameters["regionName"] = profile.regionName; - parameters["uuid"] = profile.UUID.ToString(); - parameters["regionRecvKey"] = profile.regionRecvKey; - parameters["regionSendKey"] = profile.regionSendKey; - parameters["regionDataURI"] = profile.regionDataURI; - parameters["serverIP"] = profile.serverIP; - parameters["serverPort"] = profile.serverPort.ToString(); - parameters["serverURI"] = profile.serverURI; - parameters["locX"] = profile.regionLocX.ToString(); - parameters["locY"] = profile.regionLocY.ToString(); - parameters["locZ"] = profile.regionLocZ.ToString(); - parameters["eastOverrideHandle"] = profile.regionEastOverrideHandle.ToString(); - parameters["westOverrideHandle"] = profile.regionWestOverrideHandle.ToString(); - parameters["northOverrideHandle"] = profile.regionNorthOverrideHandle.ToString(); - parameters["southOverrideHandle"] = profile.regionSouthOverrideHandle.ToString(); - parameters["regionAssetURI"] = profile.regionAssetURI; - parameters["regionAssetRecvKey"] = profile.regionAssetRecvKey; - parameters["regionAssetSendKey"] = profile.regionAssetSendKey; - parameters["regionUserURI"] = profile.regionUserURI; - parameters["regionUserRecvKey"] = profile.regionUserRecvKey; - parameters["regionUserSendKey"] = profile.regionUserSendKey; - - bool returnval = false; - - try - { - IDbCommand result = Query(sql, parameters); - - if (result.ExecuteNonQuery() == 1) - returnval = true; - - result.Dispose(); - } - catch (Exception) - { - return false; - } - - return returnval; - } - } -} diff --git a/OpenSim/Data/SQLite/SQLiteUserData.cs b/OpenSim/Data/SQLite/SQLiteUserData.cs deleted file mode 100644 index caddcf8..0000000 --- a/OpenSim/Data/SQLite/SQLiteUserData.cs +++ /dev/null @@ -1,1262 +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 Mono.Data.SqliteClient; -using OpenMetaverse; -using OpenSim.Framework; - -namespace OpenSim.Data.SQLite -{ - /// - /// A User storage interface for the SQLite database system - /// - public class SQLiteUserData : UserDataBase - { - private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); - - /// - /// The database manager - /// - /// - /// Artificial constructor called upon plugin load - /// - private const string SelectUserByUUID = "select * from users where UUID=:UUID"; - private const string SelectUserByName = "select * from users where username=:username and surname=:surname"; - private const string SelectFriendsByUUID = "select a.friendID, a.friendPerms, b.friendPerms from userfriends as a, userfriends as b where a.ownerID=:ownerID and b.ownerID=a.friendID and b.friendID=a.ownerID"; - - private const string userSelect = "select * from users"; - private const string userFriendsSelect = "select a.ownerID as ownerID,a.friendID as friendID,a.friendPerms as friendPerms,b.friendPerms as ownerperms, b.ownerID as fownerID, b.friendID as ffriendID from userfriends as a, userfriends as b"; - private const string userAgentSelect = "select * from useragents"; - private const string AvatarAppearanceSelect = "select * from avatarappearance"; - - private const string AvatarPickerAndSQL = "select * from users where username like :username and surname like :surname"; - private const string AvatarPickerOrSQL = "select * from users where username like :username or surname like :surname"; - - private DataSet ds; - private SqliteDataAdapter da; - private SqliteDataAdapter daf; - private SqliteDataAdapter dua; - private SqliteDataAdapter daa; - SqliteConnection g_conn; - - public override void Initialise() - { - m_log.Info("[SQLiteUserData]: " + Name + " cannot be default-initialized!"); - throw new PluginNotInitialisedException (Name); - } - - /// - /// - /// Initialises User Interface - /// Loads and initialises a new SQLite connection and maintains it. - /// use default URI if connect string string is empty. - /// - /// - /// connect string - override public void Initialise(string connect) - { - // default to something sensible - if (connect == "") - connect = "URI=file:userprofiles.db,version=3"; - - SqliteConnection conn = new SqliteConnection(connect); - - // This sucks, but It doesn't seem to work with the dataset Syncing :P - g_conn = conn; - g_conn.Open(); - - Assembly assem = GetType().Assembly; - Migration m = new Migration(g_conn, assem, "UserStore"); - m.Update(); - - - ds = new DataSet(); - da = new SqliteDataAdapter(new SqliteCommand(userSelect, conn)); - dua = new SqliteDataAdapter(new SqliteCommand(userAgentSelect, conn)); - daf = new SqliteDataAdapter(new SqliteCommand(userFriendsSelect, conn)); - daa = new SqliteDataAdapter(new SqliteCommand(AvatarAppearanceSelect, conn)); - //if (daa == null) m_log.Info("[SQLiteUserData]: daa = null"); - - lock (ds) - { - ds.Tables.Add(createUsersTable()); - ds.Tables.Add(createUserAgentsTable()); - ds.Tables.Add(createUserFriendsTable()); - ds.Tables.Add(createAvatarAppearanceTable()); - - setupUserCommands(da, conn); - da.Fill(ds.Tables["users"]); - - setupAgentCommands(dua, conn); - dua.Fill(ds.Tables["useragents"]); - - setupUserFriendsCommands(daf, conn); - daf.Fill(ds.Tables["userfriends"]); - - setupAvatarAppearanceCommands(daa, conn); - daa.Fill(ds.Tables["avatarappearance"]); - } - - return; - } - - public override void Dispose () - { - if (g_conn != null) - { - g_conn.Close(); - g_conn = null; - } - if (ds != null) - { - ds.Dispose(); - ds = null; - } - if (da != null) - { - da.Dispose(); - da = null; - } - if (daf != null) - { - daf.Dispose(); - daf = null; - } - if (dua != null) - { - dua.Dispose(); - dua = null; - } - if (daa != null) - { - daa.Dispose(); - daa = null; - } - } - - /// - /// see IUserDataPlugin, - /// Get user data profile by UUID - /// - /// User UUID - /// user profile data - override public UserProfileData GetUserByUUID(UUID uuid) - { - lock (ds) - { - DataRow row = ds.Tables["users"].Rows.Find(uuid.ToString()); - if (row != null) - { - UserProfileData user = buildUserProfile(row); - return user; - } - else - { - return null; - } - } - } - - /// - /// see IUserDataPlugin, - /// Get user data profile by name - /// - /// first name - /// last name - /// user profile data - override public UserProfileData GetUserByName(string fname, string lname) - { - string select = "surname = '" + lname + "' and username = '" + fname + "'"; - lock (ds) - { - DataRow[] rows = ds.Tables["users"].Select(select); - if (rows.Length > 0) - { - UserProfileData user = buildUserProfile(rows[0]); - return user; - } - else - { - return null; - } - } - } - - #region User Friends List Data - - private bool ExistsFriend(UUID owner, UUID friend) - { - string FindFriends = "select * from userfriends where (ownerID=:ownerID and friendID=:friendID) or (ownerID=:friendID and friendID=:ownerID)"; - using (SqliteCommand cmd = new SqliteCommand(FindFriends, g_conn)) - { - cmd.Parameters.Add(new SqliteParameter(":ownerID", owner.ToString())); - cmd.Parameters.Add(new SqliteParameter(":friendID", friend.ToString())); - try - { - using (IDataReader reader = cmd.ExecuteReader()) - { - if (reader.Read()) - { - reader.Close(); - return true; - } - else - { - reader.Close(); - return false; - } - } - } - catch (Exception ex) - { - m_log.Error("[USER DB]: Exception getting friends list for user: " + ex.ToString()); - return false; - } - } - } - /// - /// Add a new friend in the friendlist - /// - /// UUID of the friendlist owner - /// UUID of the friend to add - /// permission flag - override public void AddNewUserFriend(UUID friendlistowner, UUID friend, uint perms) - { - if (ExistsFriend(friendlistowner, friend)) - return; - - string InsertFriends = "insert into userfriends(ownerID, friendID, friendPerms) values(:ownerID, :friendID, :perms)"; - using (SqliteCommand cmd = new SqliteCommand(InsertFriends, g_conn)) - { - cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.ToString())); - cmd.Parameters.Add(new SqliteParameter(":friendID", friend.ToString())); - cmd.Parameters.Add(new SqliteParameter(":perms", perms)); - cmd.ExecuteNonQuery(); - } - using (SqliteCommand cmd = new SqliteCommand(InsertFriends, g_conn)) - { - cmd.Parameters.Add(new SqliteParameter(":ownerID", friend.ToString())); - cmd.Parameters.Add(new SqliteParameter(":friendID", friendlistowner.ToString())); - cmd.Parameters.Add(new SqliteParameter(":perms", perms)); - cmd.ExecuteNonQuery(); - } - } - - /// - /// Remove a user from the friendlist - /// - /// UUID of the friendlist owner - /// UUID of the friend to remove - override public void RemoveUserFriend(UUID friendlistowner, UUID friend) - { - string DeletePerms = "delete from userfriends where (ownerID=:ownerID and friendID=:friendID) or (ownerID=:friendID and friendID=:ownerID)"; - using (SqliteCommand cmd = new SqliteCommand(DeletePerms, g_conn)) - { - cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.ToString())); - cmd.Parameters.Add(new SqliteParameter(":friendID", friend.ToString())); - cmd.ExecuteNonQuery(); - } - } - - /// - /// Update the friendlist permission - /// - /// UUID of the friendlist owner - /// UUID of the friend to modify - /// updated permission flag - override public void UpdateUserFriendPerms(UUID friendlistowner, UUID friend, uint perms) - { - string UpdatePerms = "update userfriends set friendPerms=:perms where ownerID=:ownerID and friendID=:friendID"; - using (SqliteCommand cmd = new SqliteCommand(UpdatePerms, g_conn)) - { - cmd.Parameters.Add(new SqliteParameter(":perms", perms)); - cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.ToString())); - cmd.Parameters.Add(new SqliteParameter(":friendID", friend.ToString())); - cmd.ExecuteNonQuery(); - } - } - - /// - /// Get (fetch?) the friendlist for a user - /// - /// UUID of the friendlist owner - /// The friendlist list - override public List GetUserFriendList(UUID friendlistowner) - { - List returnlist = new List(); - - using (SqliteCommand cmd = new SqliteCommand(SelectFriendsByUUID, g_conn)) - { - cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.ToString())); - - try - { - using (IDataReader reader = cmd.ExecuteReader()) - { - while (reader.Read()) - { - FriendListItem user = new FriendListItem(); - user.FriendListOwner = friendlistowner; - user.Friend = new UUID((string)reader[0]); - user.FriendPerms = Convert.ToUInt32(reader[1]); - user.FriendListOwnerPerms = Convert.ToUInt32(reader[2]); - returnlist.Add(user); - } - reader.Close(); - } - } - catch (Exception ex) - { - m_log.Error("[USER DB]: Exception getting friends list for user: " + ex.ToString()); - } - } - - return returnlist; - } - - override public Dictionary GetFriendRegionInfos (List uuids) - { - Dictionary infos = new Dictionary(); - - DataTable agents = ds.Tables["useragents"]; - foreach (UUID uuid in uuids) - { - lock (ds) - { - DataRow row = agents.Rows.Find(uuid.ToString()); - if (row == null) infos[uuid] = null; - else - { - FriendRegionInfo fri = new FriendRegionInfo(); - fri.isOnline = (bool)row["agentOnline"]; - fri.regionHandle = Convert.ToUInt64(row["currentHandle"]); - infos[uuid] = fri; - } - } - } - return infos; - } - - #endregion - - /// - /// - /// - /// - /// - /// - override public List GeneratePickerResults(UUID queryID, string query) - { - List returnlist = new List(); - string[] querysplit; - querysplit = query.Split(' '); - if (querysplit.Length == 2) - { - using (SqliteCommand cmd = new SqliteCommand(AvatarPickerAndSQL, g_conn)) - { - cmd.Parameters.Add(new SqliteParameter(":username", querysplit[0] + "%")); - cmd.Parameters.Add(new SqliteParameter(":surname", querysplit[1] + "%")); - - using (IDataReader reader = cmd.ExecuteReader()) - { - while (reader.Read()) - { - AvatarPickerAvatar user = new AvatarPickerAvatar(); - user.AvatarID = new UUID((string) reader["UUID"]); - user.firstName = (string) reader["username"]; - user.lastName = (string) reader["surname"]; - returnlist.Add(user); - } - reader.Close(); - } - } - } - else if (querysplit.Length == 1) - { - using (SqliteCommand cmd = new SqliteCommand(AvatarPickerOrSQL, g_conn)) - { - cmd.Parameters.Add(new SqliteParameter(":username", querysplit[0] + "%")); - cmd.Parameters.Add(new SqliteParameter(":surname", querysplit[0] + "%")); - - using (IDataReader reader = cmd.ExecuteReader()) - { - while (reader.Read()) - { - AvatarPickerAvatar user = new AvatarPickerAvatar(); - user.AvatarID = new UUID((string) reader["UUID"]); - user.firstName = (string) reader["username"]; - user.lastName = (string) reader["surname"]; - returnlist.Add(user); - } - reader.Close(); - } - } - } - return returnlist; - } - - /// - /// Returns a user by UUID direct - /// - /// The user's account ID - /// A matching user profile - override public UserAgentData GetAgentByUUID(UUID uuid) - { - lock (ds) - { - DataRow row = ds.Tables["useragents"].Rows.Find(uuid.ToString()); - if (row != null) - { - return buildUserAgent(row); - } - else - { - return null; - } - } - } - - /// - /// Returns a session by account name - /// - /// The account name - /// The user's session agent - override public UserAgentData GetAgentByName(string name) - { - return GetAgentByName(name.Split(' ')[0], name.Split(' ')[1]); - } - - /// - /// Returns a session by account name - /// - /// The first part of the user's account name - /// The second part of the user's account name - /// A user agent - override public UserAgentData GetAgentByName(string fname, string lname) - { - UserAgentData agent = null; - - UserProfileData profile = GetUserByName(fname, lname); - if (profile != null) - { - agent = GetAgentByUUID(profile.ID); - } - return agent; - } - - /// - /// DEPRECATED? Store the weblogin key - /// - /// UUID of the user - /// UUID of the weblogin - override public void StoreWebLoginKey(UUID AgentID, UUID WebLoginKey) - { - DataTable users = ds.Tables["users"]; - lock (ds) - { - DataRow row = users.Rows.Find(AgentID.ToString()); - if (row == null) - { - m_log.Warn("[USER DB]: Unable to store new web login key for non-existant user"); - } - else - { - UserProfileData user = GetUserByUUID(AgentID); - user.WebLoginKey = WebLoginKey; - fillUserRow(row, user); - da.Update(ds, "users"); - } - } - } - - private bool ExistsFirstLastName(String fname, String lname) - { - string FindUser = "select * from users where (username=:username and surname=:surname)"; - using (SqliteCommand cmd = new SqliteCommand(FindUser, g_conn)) - { - cmd.Parameters.Add(new SqliteParameter(":username", fname)); - cmd.Parameters.Add(new SqliteParameter(":surname", lname)); - try - { - using (IDataReader reader = cmd.ExecuteReader()) - { - if (reader.Read()) - { - reader.Close(); - return true; - } - else - { - reader.Close(); - return false; - } - } - } - catch (Exception ex) - { - m_log.Error("[USER DB]: Exception searching for user's first and last name: " + ex.ToString()); - return false; - } - } - } - - /// - /// Creates a new user profile - /// - /// The profile to add to the database - override public void AddNewUserProfile(UserProfileData user) - { - DataTable users = ds.Tables["users"]; - UUID zero = UUID.Zero; - if (ExistsFirstLastName(user.FirstName, user.SurName) || user.ID == zero) - return; - - lock (ds) - { - DataRow row = users.Rows.Find(user.ID.ToString()); - if (row == null) - { - row = users.NewRow(); - fillUserRow(row, user); - users.Rows.Add(row); - - m_log.Debug("[USER DB]: Syncing user database: " + ds.Tables["users"].Rows.Count + " users stored"); - - // save changes off to disk - da.Update(ds, "users"); - } - else - { - m_log.WarnFormat("[USER DB]: Ignoring add since user with id {0} already exists", user.ID); - } - } - } - - /// - /// Creates a new user profile - /// - /// The profile to add to the database - /// True on success, false on error - override public bool UpdateUserProfile(UserProfileData user) - { - DataTable users = ds.Tables["users"]; - lock (ds) - { - DataRow row = users.Rows.Find(user.ID.ToString()); - if (row == null) - { - return false; - } - else - { - fillUserRow(row, user); - da.Update(ds, "users"); - } - } - - //AddNewUserProfile(user); - return true; - } - - /// - /// Creates a new user agent - /// - /// The agent to add to the database - override public void AddNewUserAgent(UserAgentData agent) - { - UUID zero = UUID.Zero; - if (agent.SessionID == zero || agent.ProfileID == zero) - return; - - DataTable agents = ds.Tables["useragents"]; - lock (ds) - { - DataRow row = agents.Rows.Find(agent.ProfileID.ToString()); - if (row == null) - { - row = agents.NewRow(); - fillUserAgentRow(row, agent); - agents.Rows.Add(row); - } - else - { - fillUserAgentRow(row, agent); - - } - m_log.Info("[USER DB]: Syncing useragent database: " + ds.Tables["useragents"].Rows.Count + " agents stored"); - // save changes off to disk - dua.Update(ds, "useragents"); - } - } - - /// - /// Transfers money between two user accounts - /// - /// Starting account - /// End account - /// The amount to move - /// Success? - override public bool MoneyTransferRequest(UUID from, UUID to, uint amount) - { - return false; // for consistency with the MySQL impl - } - - /// - /// Transfers inventory between two accounts - /// - /// Move to inventory server - /// Senders account - /// Receivers account - /// Inventory item - /// Success? - override public bool InventoryTransferRequest(UUID from, UUID to, UUID item) - { - return false; //for consistency with the MySQL impl - } - - - /// - /// Appearance. - /// TODO: stubs for now to do in memory appearance. - /// - /// The user UUID - /// Avatar Appearence - override public AvatarAppearance GetUserAppearance(UUID user) - { - m_log.Info("[APPEARANCE] GetUserAppearance " + user.ToString()); - - AvatarAppearance aa = new AvatarAppearance(user); - //try { - aa.Owner = user; - - DataTable aap = ds.Tables["avatarappearance"]; - lock (ds) - { - DataRow row = aap.Rows.Find(Util.ToRawUuidString(user)); - if (row == null) - { - m_log.Info("[APPEARANCE] Could not find appearance for " + user.ToString()); - - //m_log.Debug("[USER DB]: Creating avatarappearance For: " + user.ToString()); - - //row = aap.NewRow(); - //fillAvatarAppearanceRow(row, user, appearance); - //aap.Rows.Add(row); - // m_log.Debug("[USER DB]: Syncing user database: " + ds.Tables["users"].Rows.Count + " users stored"); - // save changes off to disk - //daa.Update(ds, "avatarappearance"); - } - else - { - m_log.InfoFormat("[APPEARANCE] appearance found for {0}", user.ToString()); - - aa.BodyAsset = new UUID((String)row["BodyAsset"]); - aa.BodyItem = new UUID((String)row["BodyItem"]); - aa.SkinItem = new UUID((String)row["SkinItem"]); - aa.SkinAsset = new UUID((String)row["SkinAsset"]); - aa.HairItem = new UUID((String)row["HairItem"]); - aa.HairAsset = new UUID((String)row["HairAsset"]); - aa.EyesItem = new UUID((String)row["EyesItem"]); - aa.EyesAsset = new UUID((String)row["EyesAsset"]); - aa.ShirtItem = new UUID((String)row["ShirtItem"]); - aa.ShirtAsset = new UUID((String)row["ShirtAsset"]); - aa.PantsItem = new UUID((String)row["PantsItem"]); - aa.PantsAsset = new UUID((String)row["PantsAsset"]); - aa.ShoesItem = new UUID((String)row["ShoesItem"]); - aa.ShoesAsset = new UUID((String)row["ShoesAsset"]); - aa.SocksItem = new UUID((String)row["SocksItem"]); - aa.SocksAsset = new UUID((String)row["SocksAsset"]); - aa.JacketItem = new UUID((String)row["JacketItem"]); - aa.JacketAsset = new UUID((String)row["JacketAsset"]); - aa.GlovesItem = new UUID((String)row["GlovesItem"]); - aa.GlovesAsset = new UUID((String)row["GlovesAsset"]); - aa.UnderShirtItem = new UUID((String)row["UnderShirtItem"]); - aa.UnderShirtAsset = new UUID((String)row["UnderShirtAsset"]); - aa.UnderPantsItem = new UUID((String)row["UnderPantsItem"]); - aa.UnderPantsAsset = new UUID((String)row["UnderPantsAsset"]); - aa.SkirtItem = new UUID((String)row["SkirtItem"]); - aa.SkirtAsset = new UUID((String)row["SkirtAsset"]); - - // Ewe Loon - // Used Base64String because for some reason it wont accept using Byte[] (which works in Region date) - - String str = (String)row["Texture"]; - byte[] texture = Convert.FromBase64String(str); - aa.Texture = new Primitive.TextureEntry(texture, 0, texture.Length); - - str = (String)row["VisualParams"]; - byte[] VisualParams = Convert.FromBase64String(str); - aa.VisualParams = VisualParams; - - aa.Serial = Convert.ToInt32(row["Serial"]); - aa.AvatarHeight = Convert.ToSingle(row["AvatarHeight"]); - m_log.InfoFormat("[APPEARANCE] appearance set for {0}", user.ToString()); - } - } - - // m_log.Info("[APPEARANCE] Found appearance for " + user.ToString() + aa.ToString()); - // } catch (KeyNotFoundException) { - // m_log.InfoFormat("[APPEARANCE] No appearance found for {0}", user.ToString()); - // } - return aa; - } - - /// - /// Update a user appearence - /// - /// the user UUID - /// appearence - override public void UpdateUserAppearance(UUID user, AvatarAppearance appearance) - { - appearance.Owner = user; - DataTable aap = ds.Tables["avatarappearance"]; - lock (ds) - { - DataRow row = aap.Rows.Find(Util.ToRawUuidString(user)); - if (row == null) - { - m_log.Debug("[USER DB]: Creating UserAppearance For: " + user.ToString()); - - row = aap.NewRow(); - fillAvatarAppearanceRow(row, user, appearance); - aap.Rows.Add(row); - // m_log.Debug("[USER DB]: Syncing user database: " + ds.Tables["users"].Rows.Count + " users stored"); - // save changes off to disk - daa.Update(ds, "avatarappearance"); - } - else - { - m_log.Debug("[USER DB]: Updating UserAppearance For: " + user.ToString()); - fillAvatarAppearanceRow(row, user, appearance); - daa.Update(ds, "avatarappearance"); - } - } - } - - /// - /// Returns the name of the storage provider - /// - /// Storage provider name - override public string Name - { - get {return "Sqlite Userdata";} - } - - /// - /// Returns the version of the storage provider - /// - /// Storage provider version - override public string Version - { - get {return "0.1";} - } - - /*********************************************************************** - * - * DataTable creation - * - **********************************************************************/ - /*********************************************************************** - * - * Database Definition Functions - * - * This should be db agnostic as we define them in ADO.NET terms - * - **********************************************************************/ - - /// - /// Create the "users" table - /// - /// DataTable - private static DataTable createUsersTable() - { - DataTable users = new DataTable("users"); - - SQLiteUtil.createCol(users, "UUID", typeof (String)); - SQLiteUtil.createCol(users, "username", typeof (String)); - SQLiteUtil.createCol(users, "surname", typeof (String)); - SQLiteUtil.createCol(users, "email", typeof (String)); - SQLiteUtil.createCol(users, "passwordHash", typeof (String)); - SQLiteUtil.createCol(users, "passwordSalt", typeof (String)); - - SQLiteUtil.createCol(users, "homeRegionX", typeof (Int32)); - SQLiteUtil.createCol(users, "homeRegionY", typeof (Int32)); - SQLiteUtil.createCol(users, "homeRegionID", typeof (String)); - SQLiteUtil.createCol(users, "homeLocationX", typeof (Double)); - SQLiteUtil.createCol(users, "homeLocationY", typeof (Double)); - SQLiteUtil.createCol(users, "homeLocationZ", typeof (Double)); - SQLiteUtil.createCol(users, "homeLookAtX", typeof (Double)); - SQLiteUtil.createCol(users, "homeLookAtY", typeof (Double)); - SQLiteUtil.createCol(users, "homeLookAtZ", typeof (Double)); - SQLiteUtil.createCol(users, "created", typeof (Int32)); - SQLiteUtil.createCol(users, "lastLogin", typeof (Int32)); - - //TODO: Please delete this column. It's now a brick - SQLiteUtil.createCol(users, "rootInventoryFolderID", typeof (String)); - - SQLiteUtil.createCol(users, "userInventoryURI", typeof (String)); - SQLiteUtil.createCol(users, "userAssetURI", typeof (String)); - SQLiteUtil.createCol(users, "profileCanDoMask", typeof (Int32)); - SQLiteUtil.createCol(users, "profileWantDoMask", typeof (Int32)); - SQLiteUtil.createCol(users, "profileAboutText", typeof (String)); - SQLiteUtil.createCol(users, "profileFirstText", typeof (String)); - SQLiteUtil.createCol(users, "profileImage", typeof (String)); - SQLiteUtil.createCol(users, "profileFirstImage", typeof (String)); - SQLiteUtil.createCol(users, "webLoginKey", typeof(String)); - SQLiteUtil.createCol(users, "userFlags", typeof (Int32)); - SQLiteUtil.createCol(users, "godLevel", typeof (Int32)); - SQLiteUtil.createCol(users, "customType", typeof (String)); - SQLiteUtil.createCol(users, "partner", typeof (String)); - // Add in contraints - users.PrimaryKey = new DataColumn[] {users.Columns["UUID"]}; - return users; - } - - /// - /// Create the "useragents" table - /// - /// Data Table - private static DataTable createUserAgentsTable() - { - DataTable ua = new DataTable("useragents"); - // this is the UUID of the user - SQLiteUtil.createCol(ua, "UUID", typeof (String)); - SQLiteUtil.createCol(ua, "agentIP", typeof (String)); - SQLiteUtil.createCol(ua, "agentPort", typeof (Int32)); - SQLiteUtil.createCol(ua, "agentOnline", typeof (Boolean)); - SQLiteUtil.createCol(ua, "sessionID", typeof (String)); - SQLiteUtil.createCol(ua, "secureSessionID", typeof (String)); - SQLiteUtil.createCol(ua, "regionID", typeof (String)); - SQLiteUtil.createCol(ua, "loginTime", typeof (Int32)); - SQLiteUtil.createCol(ua, "logoutTime", typeof (Int32)); - SQLiteUtil.createCol(ua, "currentRegion", typeof (String)); - SQLiteUtil.createCol(ua, "currentHandle", typeof (String)); - // vectors - SQLiteUtil.createCol(ua, "currentPosX", typeof (Double)); - SQLiteUtil.createCol(ua, "currentPosY", typeof (Double)); - SQLiteUtil.createCol(ua, "currentPosZ", typeof (Double)); - // constraints - ua.PrimaryKey = new DataColumn[] {ua.Columns["UUID"]}; - - return ua; - } - - /// - /// Create the "userfriends" table - /// - /// Data Table - private static DataTable createUserFriendsTable() - { - DataTable ua = new DataTable("userfriends"); - // table contains user <----> user relationship with perms - SQLiteUtil.createCol(ua, "ownerID", typeof(String)); - SQLiteUtil.createCol(ua, "friendID", typeof(String)); - SQLiteUtil.createCol(ua, "friendPerms", typeof(Int32)); - SQLiteUtil.createCol(ua, "ownerPerms", typeof(Int32)); - SQLiteUtil.createCol(ua, "datetimestamp", typeof(Int32)); - - return ua; - } - - /// - /// Create the "avatarappearance" table - /// - /// Data Table - private static DataTable createAvatarAppearanceTable() - { - DataTable aa = new DataTable("avatarappearance"); - // table contains user appearance items - - SQLiteUtil.createCol(aa, "Owner", typeof(String)); - SQLiteUtil.createCol(aa, "BodyItem", typeof(String)); - SQLiteUtil.createCol(aa, "BodyAsset", typeof(String)); - SQLiteUtil.createCol(aa, "SkinItem", typeof(String)); - SQLiteUtil.createCol(aa, "SkinAsset", typeof(String)); - SQLiteUtil.createCol(aa, "HairItem", typeof(String)); - SQLiteUtil.createCol(aa, "HairAsset", typeof(String)); - SQLiteUtil.createCol(aa, "EyesItem", typeof(String)); - SQLiteUtil.createCol(aa, "EyesAsset", typeof(String)); - SQLiteUtil.createCol(aa, "ShirtItem", typeof(String)); - SQLiteUtil.createCol(aa, "ShirtAsset", typeof(String)); - SQLiteUtil.createCol(aa, "PantsItem", typeof(String)); - SQLiteUtil.createCol(aa, "PantsAsset", typeof(String)); - SQLiteUtil.createCol(aa, "ShoesItem", typeof(String)); - SQLiteUtil.createCol(aa, "ShoesAsset", typeof(String)); - SQLiteUtil.createCol(aa, "SocksItem", typeof(String)); - SQLiteUtil.createCol(aa, "SocksAsset", typeof(String)); - SQLiteUtil.createCol(aa, "JacketItem", typeof(String)); - SQLiteUtil.createCol(aa, "JacketAsset", typeof(String)); - SQLiteUtil.createCol(aa, "GlovesItem", typeof(String)); - SQLiteUtil.createCol(aa, "GlovesAsset", typeof(String)); - SQLiteUtil.createCol(aa, "UnderShirtItem", typeof(String)); - SQLiteUtil.createCol(aa, "UnderShirtAsset", typeof(String)); - SQLiteUtil.createCol(aa, "UnderPantsItem", typeof(String)); - SQLiteUtil.createCol(aa, "UnderPantsAsset", typeof(String)); - SQLiteUtil.createCol(aa, "SkirtItem", typeof(String)); - SQLiteUtil.createCol(aa, "SkirtAsset", typeof(String)); - - // Used Base64String because for some reason it wont accept using Byte[] (which works in Region date) - SQLiteUtil.createCol(aa, "Texture", typeof (String)); - SQLiteUtil.createCol(aa, "VisualParams", typeof (String)); - - SQLiteUtil.createCol(aa, "Serial", typeof(Int32)); - SQLiteUtil.createCol(aa, "AvatarHeight", typeof(Double)); - - aa.PrimaryKey = new DataColumn[] { aa.Columns["Owner"] }; - - return aa; - } - - /*********************************************************************** - * - * Convert between ADO.NET <=> OpenSim Objects - * - * These should be database independant - * - **********************************************************************/ - - /// - /// TODO: this doesn't work yet because something more - /// interesting has to be done to actually get these values - /// back out. Not enough time to figure it out yet. - /// - /// - /// - private static UserProfileData buildUserProfile(DataRow row) - { - UserProfileData user = new UserProfileData(); - UUID tmp; - UUID.TryParse((String)row["UUID"], out tmp); - user.ID = tmp; - user.FirstName = (String) row["username"]; - user.SurName = (String) row["surname"]; - user.Email = (row.IsNull("email")) ? "" : (String) row["email"]; - - user.PasswordHash = (String) row["passwordHash"]; - user.PasswordSalt = (String) row["passwordSalt"]; - - user.HomeRegionX = Convert.ToUInt32(row["homeRegionX"]); - user.HomeRegionY = Convert.ToUInt32(row["homeRegionY"]); - user.HomeLocation = new Vector3( - Convert.ToSingle(row["homeLocationX"]), - Convert.ToSingle(row["homeLocationY"]), - Convert.ToSingle(row["homeLocationZ"]) - ); - user.HomeLookAt = new Vector3( - Convert.ToSingle(row["homeLookAtX"]), - Convert.ToSingle(row["homeLookAtY"]), - Convert.ToSingle(row["homeLookAtZ"]) - ); - - UUID regionID = UUID.Zero; - UUID.TryParse(row["homeRegionID"].ToString(), out regionID); // it's ok if it doesn't work; just use UUID.Zero - user.HomeRegionID = regionID; - - user.Created = Convert.ToInt32(row["created"]); - user.LastLogin = Convert.ToInt32(row["lastLogin"]); - user.UserInventoryURI = (String) row["userInventoryURI"]; - user.UserAssetURI = (String) row["userAssetURI"]; - user.CanDoMask = Convert.ToUInt32(row["profileCanDoMask"]); - user.WantDoMask = Convert.ToUInt32(row["profileWantDoMask"]); - user.AboutText = (String) row["profileAboutText"]; - user.FirstLifeAboutText = (String) row["profileFirstText"]; - UUID.TryParse((String)row["profileImage"], out tmp); - user.Image = tmp; - UUID.TryParse((String)row["profileFirstImage"], out tmp); - user.FirstLifeImage = tmp; - user.WebLoginKey = new UUID((String) row["webLoginKey"]); - user.UserFlags = Convert.ToInt32(row["userFlags"]); - user.GodLevel = Convert.ToInt32(row["godLevel"]); - user.CustomType = row["customType"].ToString(); - user.Partner = new UUID((String) row["partner"]); - - return user; - } - - /// - /// Persist user profile data - /// - /// - /// - private void fillUserRow(DataRow row, UserProfileData user) - { - row["UUID"] = user.ID.ToString(); - row["username"] = user.FirstName; - row["surname"] = user.SurName; - row["email"] = user.Email; - row["passwordHash"] = user.PasswordHash; - row["passwordSalt"] = user.PasswordSalt; - - row["homeRegionX"] = user.HomeRegionX; - row["homeRegionY"] = user.HomeRegionY; - row["homeRegionID"] = user.HomeRegionID.ToString(); - row["homeLocationX"] = user.HomeLocation.X; - row["homeLocationY"] = user.HomeLocation.Y; - row["homeLocationZ"] = user.HomeLocation.Z; - row["homeLookAtX"] = user.HomeLookAt.X; - row["homeLookAtY"] = user.HomeLookAt.Y; - row["homeLookAtZ"] = user.HomeLookAt.Z; - - row["created"] = user.Created; - row["lastLogin"] = user.LastLogin; - //TODO: Get rid of rootInventoryFolderID in a safe way. - row["rootInventoryFolderID"] = UUID.Zero.ToString(); - row["userInventoryURI"] = user.UserInventoryURI; - row["userAssetURI"] = user.UserAssetURI; - row["profileCanDoMask"] = user.CanDoMask; - row["profileWantDoMask"] = user.WantDoMask; - row["profileAboutText"] = user.AboutText; - row["profileFirstText"] = user.FirstLifeAboutText; - row["profileImage"] = user.Image.ToString(); - row["profileFirstImage"] = user.FirstLifeImage.ToString(); - row["webLoginKey"] = user.WebLoginKey.ToString(); - row["userFlags"] = user.UserFlags; - row["godLevel"] = user.GodLevel; - row["customType"] = user.CustomType == null ? "" : user.CustomType; - row["partner"] = user.Partner.ToString(); - - // ADO.NET doesn't handle NULL very well - foreach (DataColumn col in ds.Tables["users"].Columns) - { - if (row[col] == null) - { - row[col] = String.Empty; - } - } - } - - /// - /// - /// - /// - /// - private void fillAvatarAppearanceRow(DataRow row, UUID user, AvatarAppearance appearance) - { - row["Owner"] = Util.ToRawUuidString(user); - row["BodyItem"] = appearance.BodyItem.ToString(); - row["BodyAsset"] = appearance.BodyAsset.ToString(); - row["SkinItem"] = appearance.SkinItem.ToString(); - row["SkinAsset"] = appearance.SkinAsset.ToString(); - row["HairItem"] = appearance.HairItem.ToString(); - row["HairAsset"] = appearance.HairAsset.ToString(); - row["EyesItem"] = appearance.EyesItem.ToString(); - row["EyesAsset"] = appearance.EyesAsset.ToString(); - row["ShirtItem"] = appearance.ShirtItem.ToString(); - row["ShirtAsset"] = appearance.ShirtAsset.ToString(); - row["PantsItem"] = appearance.PantsItem.ToString(); - row["PantsAsset"] = appearance.PantsAsset.ToString(); - row["ShoesItem"] = appearance.ShoesItem.ToString(); - row["ShoesAsset"] = appearance.ShoesAsset.ToString(); - row["SocksItem"] = appearance.SocksItem.ToString(); - row["SocksAsset"] = appearance.SocksAsset.ToString(); - row["JacketItem"] = appearance.JacketItem.ToString(); - row["JacketAsset"] = appearance.JacketAsset.ToString(); - row["GlovesItem"] = appearance.GlovesItem.ToString(); - row["GlovesAsset"] = appearance.GlovesAsset.ToString(); - row["UnderShirtItem"] = appearance.UnderShirtItem.ToString(); - row["UnderShirtAsset"] = appearance.UnderShirtAsset.ToString(); - row["UnderPantsItem"] = appearance.UnderPantsItem.ToString(); - row["UnderPantsAsset"] = appearance.UnderPantsAsset.ToString(); - row["SkirtItem"] = appearance.SkirtItem.ToString(); - row["SkirtAsset"] = appearance.SkirtAsset.ToString(); - - // Used Base64String because for some reason it wont accept using Byte[] (which works in Region date) - row["Texture"] = Convert.ToBase64String(appearance.Texture.GetBytes()); - row["VisualParams"] = Convert.ToBase64String(appearance.VisualParams); - - row["Serial"] = appearance.Serial; - row["AvatarHeight"] = appearance.AvatarHeight; - - // ADO.NET doesn't handle NULL very well - foreach (DataColumn col in ds.Tables["avatarappearance"].Columns) - { - if (row[col] == null) - { - row[col] = String.Empty; - } - } - } - - /// - /// - /// - /// - /// - private static UserAgentData buildUserAgent(DataRow row) - { - UserAgentData ua = new UserAgentData(); - - UUID tmp; - UUID.TryParse((String)row["UUID"], out tmp); - ua.ProfileID = tmp; - ua.AgentIP = (String)row["agentIP"]; - ua.AgentPort = Convert.ToUInt32(row["agentPort"]); - ua.AgentOnline = Convert.ToBoolean(row["agentOnline"]); - ua.SessionID = new UUID((String) row["sessionID"]); - ua.SecureSessionID = new UUID((String) row["secureSessionID"]); - ua.InitialRegion = new UUID((String) row["regionID"]); - ua.LoginTime = Convert.ToInt32(row["loginTime"]); - ua.LogoutTime = Convert.ToInt32(row["logoutTime"]); - ua.Region = new UUID((String) row["currentRegion"]); - ua.Handle = Convert.ToUInt64(row["currentHandle"]); - ua.Position = new Vector3( - Convert.ToSingle(row["currentPosX"]), - Convert.ToSingle(row["currentPosY"]), - Convert.ToSingle(row["currentPosZ"]) - ); - ua.LookAt = new Vector3( - Convert.ToSingle(row["currentLookAtX"]), - Convert.ToSingle(row["currentLookAtY"]), - Convert.ToSingle(row["currentLookAtZ"]) - ); - return ua; - } - - /// - /// - /// - /// - /// - private static void fillUserAgentRow(DataRow row, UserAgentData ua) - { - row["UUID"] = ua.ProfileID.ToString(); - row["agentIP"] = ua.AgentIP; - row["agentPort"] = ua.AgentPort; - row["agentOnline"] = ua.AgentOnline; - row["sessionID"] = ua.SessionID.ToString(); - row["secureSessionID"] = ua.SecureSessionID.ToString(); - row["regionID"] = ua.InitialRegion.ToString(); - row["loginTime"] = ua.LoginTime; - row["logoutTime"] = ua.LogoutTime; - row["currentRegion"] = ua.Region.ToString(); - row["currentHandle"] = ua.Handle.ToString(); - // vectors - row["currentPosX"] = ua.Position.X; - row["currentPosY"] = ua.Position.Y; - row["currentPosZ"] = ua.Position.Z; - row["currentLookAtX"] = ua.LookAt.X; - row["currentLookAtY"] = ua.LookAt.Y; - row["currentLookAtZ"] = ua.LookAt.Z; - } - - /*********************************************************************** - * - * Database Binding functions - * - * These will be db specific due to typing, and minor differences - * in databases. - * - **********************************************************************/ - - /// - /// - /// - /// - /// - private void setupUserCommands(SqliteDataAdapter da, SqliteConnection conn) - { - da.InsertCommand = SQLiteUtil.createInsertCommand("users", ds.Tables["users"]); - da.InsertCommand.Connection = conn; - - da.UpdateCommand = SQLiteUtil.createUpdateCommand("users", "UUID=:UUID", ds.Tables["users"]); - da.UpdateCommand.Connection = conn; - - SqliteCommand delete = new SqliteCommand("delete from users where UUID = :UUID"); - delete.Parameters.Add(SQLiteUtil.createSqliteParameter("UUID", typeof(String))); - delete.Connection = conn; - da.DeleteCommand = delete; - } - - private void setupAgentCommands(SqliteDataAdapter da, SqliteConnection conn) - { - da.InsertCommand = SQLiteUtil.createInsertCommand("useragents", ds.Tables["useragents"]); - da.InsertCommand.Connection = conn; - - da.UpdateCommand = SQLiteUtil.createUpdateCommand("useragents", "UUID=:UUID", ds.Tables["useragents"]); - da.UpdateCommand.Connection = conn; - - SqliteCommand delete = new SqliteCommand("delete from useragents where UUID = :ProfileID"); - delete.Parameters.Add(SQLiteUtil.createSqliteParameter("ProfileID", typeof(String))); - delete.Connection = conn; - da.DeleteCommand = delete; - } - - /// - /// - /// - /// - /// - private void setupUserFriendsCommands(SqliteDataAdapter daf, SqliteConnection conn) - { - daf.InsertCommand = SQLiteUtil.createInsertCommand("userfriends", ds.Tables["userfriends"]); - daf.InsertCommand.Connection = conn; - - daf.UpdateCommand = SQLiteUtil.createUpdateCommand("userfriends", "ownerID=:ownerID and friendID=:friendID", ds.Tables["userfriends"]); - daf.UpdateCommand.Connection = conn; - - SqliteCommand delete = new SqliteCommand("delete from userfriends where ownerID=:ownerID and friendID=:friendID"); - delete.Parameters.Add(SQLiteUtil.createSqliteParameter("ownerID", typeof(String))); - delete.Parameters.Add(SQLiteUtil.createSqliteParameter("friendID", typeof(String))); - delete.Connection = conn; - daf.DeleteCommand = delete; - - } - - /// - /// - /// - /// - /// - private void setupAvatarAppearanceCommands(SqliteDataAdapter daa, SqliteConnection conn) - { - daa.InsertCommand = SQLiteUtil.createInsertCommand("avatarappearance", ds.Tables["avatarappearance"]); - daa.InsertCommand.Connection = conn; - - daa.UpdateCommand = SQLiteUtil.createUpdateCommand("avatarappearance", "Owner=:Owner", ds.Tables["avatarappearance"]); - daa.UpdateCommand.Connection = conn; - - SqliteCommand delete = new SqliteCommand("delete from avatarappearance where Owner=:Owner"); - delete.Parameters.Add(SQLiteUtil.createSqliteParameter("Owner", typeof(String))); - delete.Connection = conn; - daa.DeleteCommand = delete; - } - - - override public void ResetAttachments(UUID userID) - { - } - - override public void LogoutUsers(UUID regionID) - { - } - } -} diff --git a/OpenSim/Data/SQLite/Tests/SQLiteUserTest.cs b/OpenSim/Data/SQLite/Tests/SQLiteUserTest.cs deleted file mode 100644 index c9953c5..0000000 --- a/OpenSim/Data/SQLite/Tests/SQLiteUserTest.cs +++ /dev/null @@ -1,64 +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.IO; -using NUnit.Framework; -using OpenSim.Data.Tests; -using OpenSim.Tests.Common; - -namespace OpenSim.Data.SQLite.Tests -{ - [TestFixture, DatabaseTest] - public class SQLiteUserTest : BasicUserTest - { - public string file; - public string connect; - - [TestFixtureSetUp] - public void Init() - { - // SQLite doesn't work on power or z linux - if (Directory.Exists("/proc/ppc64") || Directory.Exists("/proc/dasd")) - { - Assert.Ignore(); - } - - SuperInit(); - file = Path.GetTempFileName() + ".db"; - connect = "URI=file:" + file + ",version=3"; - db = new SQLiteUserData(); - db.Initialise(connect); - } - - [TestFixtureTearDown] - public void Cleanup() - { - db.Dispose(); - File.Delete(file); - } - } -} -- cgit v1.1