From 2a8f66b221e93fb2d693c1c273c2dee85439f835 Mon Sep 17 00:00:00 2001 From: Melanie Date: Sun, 6 Sep 2009 04:28:42 +0100 Subject: Revising the user account data interfaces. No user functionality yet --- OpenSim/Data/IUserAccountData.cs | 55 ++++++++ OpenSim/Data/MySQL/MySQLUserAccountData.cs | 180 +++++++++++++++++++++++++ OpenSim/Data/MySQL/Resources/008_UserStore.sql | 5 + 3 files changed, 240 insertions(+) create mode 100644 OpenSim/Data/IUserAccountData.cs create mode 100644 OpenSim/Data/MySQL/MySQLUserAccountData.cs create mode 100644 OpenSim/Data/MySQL/Resources/008_UserStore.sql (limited to 'OpenSim/Data') diff --git a/OpenSim/Data/IUserAccountData.cs b/OpenSim/Data/IUserAccountData.cs new file mode 100644 index 0000000..6bec188 --- /dev/null +++ b/OpenSim/Data/IUserAccountData.cs @@ -0,0 +1,55 @@ +/* + * 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 OpenMetaverse; +using OpenSim.Framework; + +namespace OpenSim.Data +{ + public class UserAccountData + { + public UUID PrincipalID; + public UUID ScopeID; + public Dictionary Data; + } + + /// + /// An interface for connecting to the authentication datastore + /// + public interface IUserAccountData + { + UserAccountData Get(UUID principalID, UUID ScopeID); + + List Query(UUID principalID, UUID ScopeID, string query); + + bool Store(UserAccountData data); + + bool SetDataItem(UUID principalID, string item, string value); + } +} diff --git a/OpenSim/Data/MySQL/MySQLUserAccountData.cs b/OpenSim/Data/MySQL/MySQLUserAccountData.cs new file mode 100644 index 0000000..39d60ca --- /dev/null +++ b/OpenSim/Data/MySQL/MySQLUserAccountData.cs @@ -0,0 +1,180 @@ +/* + * 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 MySql.Data.MySqlClient; + +namespace OpenSim.Data.MySQL +{ + public class MySqlUserAccountData : MySqlFramework, IUserAccountData + { + private string m_Realm; + private List m_ColumnNames = null; + private int m_LastExpire = 0; + + public MySqlUserAccountData(string connectionString, string realm) + : base(connectionString) + { + m_Realm = realm; + + Migration m = new Migration(m_Connection, GetType().Assembly, "UserStore"); + m.Update(); + } + + public List Query(UUID principalID, UUID scopeID, string query) + { + return null; + } + + public UserAccountData Get(UUID principalID, UUID scopeID) + { + UserAccountData ret = new UserAccountData(); + ret.Data = new Dictionary(); + + string command = "select * from `"+m_Realm+"` where UUID = ?principalID"; + if (scopeID != UUID.Zero) + command += " and ScopeID = ?scopeID"; + + MySqlCommand cmd = new MySqlCommand(command); + + cmd.Parameters.AddWithValue("?principalID", principalID.ToString()); + cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString()); + + IDataReader result = ExecuteReader(cmd); + + if (result.Read()) + { + ret.PrincipalID = principalID; + UUID scope; + UUID.TryParse(result["ScopeID"].ToString(), out scope); + ret.ScopeID = scope; + + if (m_ColumnNames == null) + { + m_ColumnNames = new List(); + + DataTable schemaTable = result.GetSchemaTable(); + foreach (DataRow row in schemaTable.Rows) + m_ColumnNames.Add(row["ColumnName"].ToString()); + } + + foreach (string s in m_ColumnNames) + { + if (s == "UUID") + continue; + if (s == "ScopeID") + continue; + + ret.Data[s] = result[s].ToString(); + } + + result.Close(); + CloseReaderCommand(cmd); + + return ret; + } + + result.Close(); + CloseReaderCommand(cmd); + + return null; + } + + public bool Store(UserAccountData data) + { + if (data.Data.ContainsKey("UUID")) + data.Data.Remove("UUID"); + if (data.Data.ContainsKey("ScopeID")) + data.Data.Remove("ScopeID"); + + string[] fields = new List(data.Data.Keys).ToArray(); + + MySqlCommand cmd = new MySqlCommand(); + + string update = "update `"+m_Realm+"` set "; + bool first = true; + foreach (string field in fields) + { + if (!first) + update += ", "; + update += "`" + field + "` = ?"+field; + + first = false; + + cmd.Parameters.AddWithValue("?"+field, data.Data[field]); + } + + update += " where UUID = ?principalID"; + + if (data.ScopeID != UUID.Zero) + update += " and ScopeID = ?scopeID"; + + cmd.CommandText = update; + cmd.Parameters.AddWithValue("?principalID", data.PrincipalID.ToString()); + cmd.Parameters.AddWithValue("?scopeID", data.ScopeID.ToString()); + + if (ExecuteNonQuery(cmd) < 1) + { + string insert = "insert into `" + m_Realm + "` (`UUID`, `ScopeID`, `" + + String.Join("`, `", fields) + + "`) values ( ?principalID, ?scopeID, ?" + String.Join(", ?", fields) + ")"; + + cmd.CommandText = insert; + + if (ExecuteNonQuery(cmd) < 1) + { + cmd.Dispose(); + return false; + } + } + + cmd.Dispose(); + + return true; + } + + public bool SetDataItem(UUID principalID, string item, string value) + { + MySqlCommand cmd = new MySqlCommand("update `" + m_Realm + + "` set `" + item + "` = ?" + item + " where UUID = ?UUID"); + + + cmd.Parameters.AddWithValue("?"+item, value); + cmd.Parameters.AddWithValue("?UUID", principalID.ToString()); + + if (ExecuteNonQuery(cmd) > 0) + return true; + + return false; + } + } +} diff --git a/OpenSim/Data/MySQL/Resources/008_UserStore.sql b/OpenSim/Data/MySQL/Resources/008_UserStore.sql new file mode 100644 index 0000000..4500bd5 --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/008_UserStore.sql @@ -0,0 +1,5 @@ +BEGIN; + +ALTER TABLE users add scopeID char(36) not null default '00000000-0000-0000-0000-000000000000'; + +COMMIT; -- cgit v1.1 From a6e2e94345ae3d153f945dd79f3221630015d826 Mon Sep 17 00:00:00 2001 From: Melanie Date: Sun, 6 Sep 2009 23:05:06 +0100 Subject: Thank you, StrawberryFride, for a hit-and-run patch to add authentication and user services to MSSQL --- OpenSim/Data/MSSQL/MSSQLAuthenticationData.cs | 222 +++++++++++++++++++++++++ OpenSim/Data/MSSQL/MSSQLUserAccountData.cs | 186 +++++++++++++++++++++ OpenSim/Data/MSSQL/Resources/011_UserStore.sql | 5 + 3 files changed, 413 insertions(+) create mode 100644 OpenSim/Data/MSSQL/MSSQLAuthenticationData.cs create mode 100644 OpenSim/Data/MSSQL/MSSQLUserAccountData.cs create mode 100644 OpenSim/Data/MSSQL/Resources/011_UserStore.sql (limited to 'OpenSim/Data') diff --git a/OpenSim/Data/MSSQL/MSSQLAuthenticationData.cs b/OpenSim/Data/MSSQL/MSSQLAuthenticationData.cs new file mode 100644 index 0000000..78fc22c --- /dev/null +++ b/OpenSim/Data/MSSQL/MSSQLAuthenticationData.cs @@ -0,0 +1,222 @@ +/* + * 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 System.Data.SqlClient; +using System.Reflection; +using System.Text; + +namespace OpenSim.Data.MSSQL +{ + public class MSSQLAuthenticationData : IAuthenticationData + { + private string m_Realm; + private List m_ColumnNames = null; + private int m_LastExpire = 0; + private string m_ConnectionString; + + public MSSQLAuthenticationData(string connectionString, string realm) + { + m_Realm = realm; + m_ConnectionString = connectionString; + using (SqlConnection conn = new SqlConnection(m_ConnectionString)) + { + conn.Open(); + Migration m = new Migration(conn, GetType().Assembly, "AuthStore"); + m.Update(); + } + } + + public AuthenticationData Get(UUID principalID) + { + AuthenticationData ret = new AuthenticationData(); + ret.Data = new Dictionary(); + + string sql = string.Format("select * from '{0}' where UUID = @principalID", m_Realm); + + using (SqlConnection conn = new SqlConnection(m_ConnectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) + { + cmd.Parameters.AddWithValue("@principalID", principalID.ToString()); + conn.Open(); + using (SqlDataReader 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; + } + } + } + return null; + } + + public bool Store(AuthenticationData data) + { + if (data.Data.ContainsKey("UUID")) + data.Data.Remove("UUID"); + + string[] fields = new List(data.Data.Keys).ToArray(); + StringBuilder updateBuilder = new StringBuilder(); + + using (SqlConnection conn = new SqlConnection(m_ConnectionString)) + using (SqlCommand cmd = new SqlCommand()) + { + updateBuilder.AppendFormat("update '{0}' set ", m_Realm); + + bool first = true; + foreach (string field in fields) + { + if (!first) + updateBuilder.Append(", "); + updateBuilder.AppendFormat("'{0}' = @{0}",field); + + first = false; + + cmd.Parameters.AddWithValue("@" + field, data.Data[field]); + } + + updateBuilder.Append(" where UUID = @principalID"); + + cmd.CommandText = updateBuilder.ToString(); + cmd.Connection = conn; + + cmd.Parameters.AddWithValue("@principalID", data.PrincipalID.ToString()); + conn.Open(); + if (cmd.ExecuteNonQuery() < 1) + { + StringBuilder insertBuilder = new StringBuilder(); + + insertBuilder.AppendFormat("insert into '{0}' ('UUID', '", m_Realm); + insertBuilder.Append(String.Join("', '", fields)); + insertBuilder.Append("') values ( @principalID, @"); + insertBuilder.Append(String.Join(", @", fields)); + insertBuilder.Append(")"); + + cmd.CommandText = insertBuilder.ToString(); + + if (cmd.ExecuteNonQuery() < 1) + { + return false; + } + } + } + return true; + } + + public bool SetDataItem(UUID principalID, string item, string value) + { + string sql = string.Format("update '{0}' set '{1}' = @{1} where UUID = @UUID", m_Realm, item); + using (SqlConnection conn = new SqlConnection(m_ConnectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) + { + cmd.Parameters.AddWithValue("@" + item, value); + cmd.Parameters.AddWithValue("@UUID", principalID.ToString()); + conn.Open(); + if (cmd.ExecuteNonQuery() > 0) + return true; + } + return false; + } + + public bool SetToken(UUID principalID, string token, int lifetime) + { + if (System.Environment.TickCount - m_LastExpire > 30000) + DoExpire(); + string sql = "insert into tokens (UUID, token, validity) values (@principalID, @token, date_add(now(), interval @lifetime minute))"; + using (SqlConnection conn = new SqlConnection(m_ConnectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) + { + cmd.Parameters.AddWithValue("@principalID", principalID.ToString()); + cmd.Parameters.AddWithValue("@token", token); + cmd.Parameters.AddWithValue("@lifetime", lifetime.ToString()); + conn.Open(); + + if (cmd.ExecuteNonQuery() > 0) + { + return true; + } + } + return false; + } + + public bool CheckToken(UUID principalID, string token, int lifetime) + { + if (System.Environment.TickCount - m_LastExpire > 30000) + DoExpire(); + string sql = "update tokens set validity = date_add(now(), interval @lifetime minute) where UUID = @principalID and token = @token and validity > now()"; + using (SqlConnection conn = new SqlConnection(m_ConnectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) + { + cmd.Parameters.AddWithValue("@principalID", principalID.ToString()); + cmd.Parameters.AddWithValue("@token", token); + cmd.Parameters.AddWithValue("@lifetime", lifetime.ToString()); + conn.Open(); + + if (cmd.ExecuteNonQuery() > 0) + { + return true; + } + } + return false; + } + + private void DoExpire() + { + string sql = "delete from tokens where validity < now()"; + using (SqlConnection conn = new SqlConnection(m_ConnectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) + { + conn.Open(); + cmd.ExecuteNonQuery(); + } + m_LastExpire = System.Environment.TickCount; + } + } +} diff --git a/OpenSim/Data/MSSQL/MSSQLUserAccountData.cs b/OpenSim/Data/MSSQL/MSSQLUserAccountData.cs new file mode 100644 index 0000000..1520888 --- /dev/null +++ b/OpenSim/Data/MSSQL/MSSQLUserAccountData.cs @@ -0,0 +1,186 @@ +/* + * 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 System.Data.SqlClient; +using System.Text; + +namespace OpenSim.Data.MSSQL +{ + public class MSSQLUserAccountData : IUserAccountData + { + private string m_Realm; + private List m_ColumnNames = null; + private int m_LastExpire = 0; + private string m_ConnectionString; + + public MSSQLUserAccountData(string connectionString, string realm) + { + m_Realm = realm; + m_ConnectionString = connectionString; + using (SqlConnection conn = new SqlConnection(m_ConnectionString)) + { + conn.Open(); + Migration m = new Migration(conn, GetType().Assembly, "UserStore"); + m.Update(); + } + } + + public List Query(UUID principalID, UUID scopeID, string query) + { + return null; + } + + public UserAccountData Get(UUID principalID, UUID scopeID) + { + UserAccountData ret = new UserAccountData(); + ret.Data = new Dictionary(); + + string sql = string.Format("select * from '{0}' where UUID = @principalID", m_Realm); + if (scopeID != UUID.Zero) + sql += " and ScopeID = @scopeID"; + + using (SqlConnection conn = new SqlConnection(m_ConnectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) + { + + cmd.Parameters.AddWithValue("@principalID", principalID); + cmd.Parameters.AddWithValue("@scopeID", scopeID); + conn.Open(); + using (SqlDataReader result = cmd.ExecuteReader()) + { + if (result.Read()) + { + ret.PrincipalID = principalID; + UUID scope; + UUID.TryParse(result["ScopeID"].ToString(), out scope); + ret.ScopeID = scope; + + if (m_ColumnNames == null) + { + m_ColumnNames = new List(); + + DataTable schemaTable = result.GetSchemaTable(); + foreach (DataRow row in schemaTable.Rows) + m_ColumnNames.Add(row["ColumnName"].ToString()); + } + + foreach (string s in m_ColumnNames) + { + if (s == "UUID") + continue; + if (s == "ScopeID") + continue; + + ret.Data[s] = result[s].ToString(); + } + return ret; + } + } + } + return null; + } + + public bool Store(UserAccountData data) + { + if (data.Data.ContainsKey("UUID")) + data.Data.Remove("UUID"); + if (data.Data.ContainsKey("ScopeID")) + data.Data.Remove("ScopeID"); + + string[] fields = new List(data.Data.Keys).ToArray(); + + using (SqlConnection conn = new SqlConnection(m_ConnectionString)) + using (SqlCommand cmd = new SqlCommand()) + { + StringBuilder updateBuilder = new StringBuilder(); + updateBuilder.AppendFormat("update '{0}' set ", m_Realm); + bool first = true; + foreach (string field in fields) + { + if (!first) + updateBuilder.Append(", "); + updateBuilder.AppendFormat("'{0}' = @{0}", field); + + first = false; + + cmd.Parameters.AddWithValue("@" + field, data.Data[field]); + } + + updateBuilder.Append(" where UUID = @principalID"); + + if (data.ScopeID != UUID.Zero) + updateBuilder.Append(" and ScopeID = @scopeID"); + + cmd.CommandText = updateBuilder.ToString(); + cmd.Connection = conn; + cmd.Parameters.AddWithValue("@principalID", data.PrincipalID); + cmd.Parameters.AddWithValue("@scopeID", data.ScopeID); + conn.Open(); + + if (cmd.ExecuteNonQuery() < 1) + { + StringBuilder insertBuilder = new StringBuilder(); + insertBuilder.AppendFormat("insert into '{0}' ('UUID', 'ScopeID', '", m_Realm); + insertBuilder.Append(String.Join("', '", fields)); + insertBuilder.Append("') values ( @principalID, @scopeID, @"); + insertBuilder.Append(String.Join(", @", fields)); + insertBuilder.Append(")"); + + cmd.CommandText = insertBuilder.ToString(); + + if (cmd.ExecuteNonQuery() < 1) + { + return false; + } + } + } + return true; + } + + public bool SetDataItem(UUID principalID, string item, string value) + { + string sql = string.Format("update '{0}' set '{1}' = @{1} where UUID = @UUID", m_Realm, item); + using (SqlConnection conn = new SqlConnection(m_ConnectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) + { + cmd.Parameters.AddWithValue("@" + item, value); + cmd.Parameters.AddWithValue("@UUID", principalID); + conn.Open(); + + if (cmd.ExecuteNonQuery() > 0) + return true; + } + return false; + } + } +} diff --git a/OpenSim/Data/MSSQL/Resources/011_UserStore.sql b/OpenSim/Data/MSSQL/Resources/011_UserStore.sql new file mode 100644 index 0000000..5aa064f --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/011_UserStore.sql @@ -0,0 +1,5 @@ +BEGIN TRANSACTION + +ALTER TABLE users ADD scopeID uniqueidentifier not null default '00000000-0000-0000-0000-000000000000' + +COMMIT -- cgit v1.1