good bye ADO.NET to assets, notice faster startup time

1 files changed, 66 insertions, 103 deletions

diff --git a/OpenSim/Framework/Data.SQLite/SQLiteAssetData.cs b/OpenSim/Framework/Data.SQLite/SQLiteAssetData.cs
index 462c433..7b02e99 100644
--- a/OpenSim/Framework/Data.SQLite/SQLiteAssetData.cs
+++ b/OpenSim/Framework/Data.SQLite/SQLiteAssetData.cs
@@ -45,76 +45,86 @@ namespace OpenSim.Framework.Data.SQLite
         /// <summary>
         /// Artificial constructor called upon plugin load
         /// </summary>
+        private const string SelectAssetSQL = "select * from assets where UUID=:UUID";
+        private const string DeleteAssetSQL = "delete from assets where UUID=:UUID";
+        private const string InsertAssetSQL = "insert into assets(UUID, Name, Description, Type, InvType, Local, Temporary, Data) values(:UUID, :Name, :Description, :Type, :InvType, :Local, :Temporary, :Data)";
+        private const string UpdateAssetSQL = "update assets set Name=:Name, Description=:Description, Type=:Type, InvType=:InvType, Local=:Local, Temporary=:Temporary, Data=:Data where UUID=:UUID"; 
         private const string assetSelect = "select * from assets";
 
-        private DataSet ds;
-        private SqliteDataAdapter da;
+        private SqliteConnection m_conn;
 
         public void Initialise(string dbfile, string dbname)
         {
-            SqliteConnection conn = new SqliteConnection("URI=file:" + dbfile + ",version=3");
-            TestTables(conn);
+            m_conn = new SqliteConnection("URI=file:" + dbfile + ",version=3");
+            m_conn.Open();
 
-            ds = new DataSet();
-            da = new SqliteDataAdapter(new SqliteCommand(assetSelect, conn));
+            TestTables(m_conn);
+            return;
+        }
 
-            lock (ds)
+        public AssetBase FetchAsset(LLUUID uuid)
+        {
+            
+            using (SqliteCommand cmd = new SqliteCommand(SelectAssetSQL, m_conn))
             {
-                ds.Tables.Add(createAssetsTable());
-
-                setupAssetCommands(da, conn);
-                try
-                {
-                    da.Fill(ds.Tables["assets"]);
-                }
-                catch (Exception e)
+                cmd.Parameters.Add(new SqliteParameter(":UUID", uuid.UUID.ToString()));
+                using (IDataReader reader = cmd.ExecuteReader()) 
                 {
-                    MainLog.Instance.Verbose("SQLITE", e.ToString());
+                    reader.Read();
+                    if (reader != null) 
+                    {
+                        return buildAsset(reader);
+                    }
+                    else
+                    {
+                        return null;
+                    }
                 }
             }
-
-            return;
         }
 
-        public AssetBase FetchAsset(LLUUID uuid)
+        public void CreateAsset(AssetBase asset)
         {
-            AssetBase asset = new AssetBase();
-            DataRow row = ds.Tables["assets"].Rows.Find(Util.ToRawUuidString(uuid));
-            if (row != null)
+            if (ExistsAsset(asset.FullID))
             {
-                return buildAsset(row);
+                UpdateAsset(asset);
             }
-            else
+            else 
             {
-                return null;
+                using (SqliteCommand cmd = new SqliteCommand(InsertAssetSQL, m_conn))
+                {
+                    cmd.Parameters.Add(new SqliteParameter(":UUID", asset.FullID.UUID.ToString()));
+                    cmd.Parameters.Add(new SqliteParameter(":Name", asset.Name));
+                    cmd.Parameters.Add(new SqliteParameter(":Description", asset.Description));
+                    cmd.Parameters.Add(new SqliteParameter(":Type", asset.Type));
+                    cmd.Parameters.Add(new SqliteParameter(":InvType", asset.InvType));
+                    cmd.Parameters.Add(new SqliteParameter(":Local", asset.Local));
+                    cmd.Parameters.Add(new SqliteParameter(":Temporary", asset.Temporary));
+                    cmd.Parameters.Add(new SqliteParameter(":Data", asset.Data));
+                    
+                    cmd.ExecuteNonQuery();
+                }
             }
         }
 
-        public void CreateAsset(AssetBase asset)
-        {
-            // no difference for now
-            UpdateAsset(asset);
-        }
-
         public void UpdateAsset(AssetBase asset)
         {
             LogAssetLoad(asset);
-
-            DataTable assets = ds.Tables["assets"];
-            lock (ds)
+            
+            using (SqliteCommand cmd = new SqliteCommand(UpdateAssetSQL, m_conn))
             {
-                DataRow row = assets.Rows.Find(Util.ToRawUuidString(asset.FullID));
-                if (row == null)
-                {
-                    row = assets.NewRow();
-                    fillAssetRow(row, asset);
-                    assets.Rows.Add(row);
-                }
-                else
-                {
-                    fillAssetRow(row, asset);
-                }
+                cmd.Parameters.Add(new SqliteParameter(":UUID", asset.FullID.UUID.ToString()));
+                cmd.Parameters.Add(new SqliteParameter(":Name", asset.Name));
+                cmd.Parameters.Add(new SqliteParameter(":Description", asset.Description));
+                cmd.Parameters.Add(new SqliteParameter(":Type", asset.Type));
+                cmd.Parameters.Add(new SqliteParameter(":InvType", asset.InvType));
+                cmd.Parameters.Add(new SqliteParameter(":Local", asset.Local));
+                cmd.Parameters.Add(new SqliteParameter(":Temporary", asset.Temporary));
+                cmd.Parameters.Add(new SqliteParameter(":Data", asset.Data));
+                
+                cmd.ExecuteNonQuery();
             }
+
         }
 
         private void LogAssetLoad(AssetBase asset)
@@ -130,30 +140,27 @@ namespace OpenSim.Framework.Data.SQLite
 
         public bool ExistsAsset(LLUUID uuid)
         {
-            DataRow row = ds.Tables["assets"].Rows.Find(Util.ToRawUuidString(uuid));
-            return (row != null);
+            return (FetchAsset(uuid) != null);
         }
 
         public void DeleteAsset(LLUUID uuid)
         {
-            lock (ds)
+            using (SqliteCommand cmd = new SqliteCommand(DeleteAssetSQL, m_conn))
             {
-                DataRow row = ds.Tables["assets"].Rows.Find(Util.ToRawUuidString(uuid));
-                if (row != null)
-                {
-                    row.Delete();
-                }
+                cmd.Parameters.Add(new SqliteParameter(":UUID", uuid.UUID.ToString()));
+                
+                cmd.ExecuteNonQuery();
             }
         }
 
         public void CommitAssets() // force a sync to the database
         {
             MainLog.Instance.Verbose("SQLITE", "Attempting commit");
-            lock (ds)
-            {
-                da.Update(ds, "assets");
-                ds.AcceptChanges();
-            }
+            // lock (ds)
+            //             {
+            //                 da.Update(ds, "assets");
+            //                 ds.AcceptChanges();
+            // }
         }
 
         /***********************************************************************
@@ -189,7 +196,7 @@ namespace OpenSim.Framework.Data.SQLite
          *
          **********************************************************************/
 
-        private AssetBase buildAsset(DataRow row)
+        private AssetBase buildAsset(IDataReader row)
         {
             // TODO: this doesn't work yet because something more
             // interesting has to be done to actually get these values
@@ -208,34 +215,6 @@ namespace OpenSim.Framework.Data.SQLite
         }
 
 
-        private void fillAssetRow(DataRow row, AssetBase asset)
-        {
-            row["UUID"] = Util.ToRawUuidString(asset.FullID);
-            row["Name"] = asset.Name;
-            if (asset.Description != null)
-            {
-                row["Description"] = asset.Description;
-            }
-            else
-            {
-                row["Description"] = " ";
-            }
-            row["Type"] = asset.Type;
-            row["InvType"] = asset.InvType;
-            row["Local"] = asset.Local;
-            row["Temporary"] = asset.Temporary;
-            row["Data"] = asset.Data;
-
-            // ADO.NET doesn't handle NULL very well
-            foreach (DataColumn col in ds.Tables["assets"].Columns)
-            {
-                if (row[col] == null)
-                {
-                    row[col] = "";
-                }
-            }
-        }
-
         /***********************************************************************
          *
          *  Database Binding functions
@@ -245,27 +224,11 @@ namespace OpenSim.Framework.Data.SQLite
          *
          **********************************************************************/
 
-        private void setupAssetCommands(SqliteDataAdapter da, SqliteConnection conn)
-        {
-            da.InsertCommand = createInsertCommand("assets", ds.Tables["assets"]);
-            da.InsertCommand.Connection = conn;
-
-            da.UpdateCommand = createUpdateCommand("assets", "UUID=:UUID", ds.Tables["assets"]);
-            da.UpdateCommand.Connection = conn;
-
-            SqliteCommand delete = new SqliteCommand("delete from assets where UUID = :UUID");
-            delete.Parameters.Add(createSqliteParameter("UUID", typeof (String)));
-            delete.Connection = conn;
-            da.DeleteCommand = delete;
-        }
-
         private void InitDB(SqliteConnection conn)
         {
             string createAssets = defineTable(createAssetsTable());
             SqliteCommand pcmd = new SqliteCommand(createAssets, conn);
-            conn.Open();
             pcmd.ExecuteNonQuery();
-            conn.Close();
         }
 
         private bool TestTables(SqliteConnection conn)