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/SQLiteAuthenticationData.cs | 214 ++++++++++++++++++++++++ 1 file changed, 214 insertions(+) create mode 100644 OpenSim/Data/SQLite/SQLiteAuthenticationData.cs (limited to 'OpenSim/Data/SQLite/SQLiteAuthenticationData.cs') 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; + } + } +} -- 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 +++++++++++++++++++------ 1 file changed, 47 insertions(+), 14 deletions(-) (limited to 'OpenSim/Data/SQLite/SQLiteAuthenticationData.cs') 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; -- 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 ++++++---- 1 file changed, 6 insertions(+), 4 deletions(-) (limited to 'OpenSim/Data/SQLite/SQLiteAuthenticationData.cs') 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; } -- 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 +++++++++++++++---------- 1 file changed, 41 insertions(+), 28 deletions(-) (limited to 'OpenSim/Data/SQLite/SQLiteAuthenticationData.cs') 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(); -- cgit v1.1