From 20cf62b417c5cb723ff2a2dfe64fb3421d11a858 Mon Sep 17 00:00:00 2001 From: Sean Dague Date: Mon, 14 Jan 2008 20:42:27 +0000 Subject: good bye ADO.NET to assets, notice faster startup time --- OpenSim/Framework/Data.SQLite/SQLiteAssetData.cs | 169 +++++++++-------------- 1 file changed, 66 insertions(+), 103 deletions(-) (limited to 'OpenSim') 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 /// /// Artificial constructor called upon plugin load /// + 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) -- cgit v1.1