SQLite connector for UserAccounts and Auth works. Yey!

4 files changed, 69 insertions, 53 deletions

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<string, object>();
 
-            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;
         }