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