From a228b5984e6523456871f2f8e51aa086050acbf2 Mon Sep 17 00:00:00 2001 From: MW Date: Tue, 14 Aug 2007 13:54:46 +0000 Subject: Start of Inventory service, currently only (partially) functional in standalone mode and using sqlite). In standalone mode, if you have account authenticate turned on (setting in opensim.ini) then when you create a new account, a set of inventory is created for that account and stored in database (currently only a set of empty folders). Then during login the database is search for that set and sent to the client in the login response. More functions will be added soon, like creating new folders (and a bit later items) from the client inventory window. --- .../Framework/Data.SQLite/SQLiteInventoryStore.cs | 401 +++++++++++++++++++++ 1 file changed, 401 insertions(+) create mode 100644 OpenSim/Framework/Data.SQLite/SQLiteInventoryStore.cs (limited to 'OpenSim/Framework/Data.SQLite/SQLiteInventoryStore.cs') diff --git a/OpenSim/Framework/Data.SQLite/SQLiteInventoryStore.cs b/OpenSim/Framework/Data.SQLite/SQLiteInventoryStore.cs new file mode 100644 index 0000000..811a355 --- /dev/null +++ b/OpenSim/Framework/Data.SQLite/SQLiteInventoryStore.cs @@ -0,0 +1,401 @@ +using System; +using System.Collections.Generic; +using System.Text; + +using OpenSim.Framework.Console; +using OpenSim.Framework.Types; +using OpenSim.Framework.Utilities; +using libsecondlife; + +using System.Data; +using System.Data.SqlTypes; + +using Mono.Data.SqliteClient; + +namespace OpenSim.Framework.Data.SQLite +{ + + public class SQLiteInventoryStore : IInventoryData + { + private const string invItemsSelect = "select * from inventoryitems"; + private const string invFoldersSelect = "select * from inventoryfolders"; + + private DataSet ds; + private SqliteDataAdapter invItemsDa; + private SqliteDataAdapter invFoldersDa; + + /// + /// Initialises the interface + /// + public void Initialise() + { + Initialise("inventoryStore.db", "inventoryDatabase"); + } + + public void Initialise(string dbfile, string dbname) + { + string connectionString = "URI=file:" + dbfile + ",version=3"; + + MainLog.Instance.Verbose("Inventory", "Sqlite - connecting: " + dbfile); + SqliteConnection conn = new SqliteConnection(connectionString); + + SqliteCommand itemsSelectCmd = new SqliteCommand(invItemsSelect, conn); + invItemsDa = new SqliteDataAdapter(itemsSelectCmd); + // SqliteCommandBuilder primCb = new SqliteCommandBuilder(primDa); + + SqliteCommand foldersSelectCmd = new SqliteCommand(invFoldersSelect, conn); + invFoldersDa = new SqliteDataAdapter(foldersSelectCmd); + + ds = new DataSet(); + + invItemsDa.Fill(ds, "inventoryitems"); + invFoldersDa.Fill(ds, "inventoryfolders"); + ds.AcceptChanges(); + + DataTable itemsTable = ds.Tables["inventoryitems"]; + itemsTable.PrimaryKey = new DataColumn[] { itemsTable.Columns["UUID"] }; + setupItemsCommands(invItemsDa, conn); + + // shapeDa.FillSchema(ds, SchemaType.Source, "ShapeSchema"); + DataTable folderTable = ds.Tables["inventoryfolders"]; + folderTable.PrimaryKey = new DataColumn[] { folderTable.Columns["UUID"] }; + setupFoldersCommands(invFoldersDa, conn); + return; + } + + private SqliteParameter createSqliteParameter(string name, DbType type) + { + SqliteParameter param = new SqliteParameter(); + param.ParameterName = ":" + name; + param.DbType = type; + param.SourceColumn = name; + param.SourceVersion = DataRowVersion.Current; + return param; + } + + private Dictionary createInventoryItemsDataDefs() + { + Dictionary data = new Dictionary(); + data.Add("UUID", DbType.String); //inventoryID + data.Add("assetID", DbType.String); + data.Add("assetType", DbType.Int32); + data.Add("invType", DbType.Int32); + data.Add("parentFolderID", DbType.String); + data.Add("avatarID", DbType.String); + data.Add("creatorsID", DbType.String); + + data.Add("inventoryName", DbType.String); + data.Add("inventoryDescription", DbType.String); + // permissions + data.Add("inventoryNextPermissions", DbType.Int32); + data.Add("inventoryCurrentPermissions", DbType.Int32); + data.Add("inventoryBasePermissions", DbType.Int32); + data.Add("inventoryEveryOnePermissions", DbType.Int32); + + return data; + } + + private Dictionary createShapeDataDefs() + { + Dictionary data = new Dictionary(); + data.Add("UUID", DbType.String); //folderID + // shape is an enum + data.Add("name", DbType.String); + // vectors + data.Add("agentID", DbType.String); + data.Add("parentID", DbType.String); + data.Add("type", DbType.Int32); + data.Add("version", DbType.Int32); + return data; + } + + private SqliteCommand createInsertCommand(string table, Dictionary defs) + { + /** + * This is subtle enough to deserve some commentary. + * Instead of doing *lots* and *lots of hardcoded strings + * for database definitions we'll use the fact that + * realistically all insert statements look like "insert + * into A(b, c) values(:b, :c) on the parameterized query + * front. If we just have a list of b, c, etc... we can + * generate these strings instead of typing them out. + */ + string[] cols = new string[defs.Keys.Count]; + defs.Keys.CopyTo(cols, 0); + + string sql = "insert into " + table + "("; + sql += String.Join(", ", cols); + // important, the first ':' needs to be here, the rest get added in the join + sql += ") values (:"; + sql += String.Join(", :", cols); + sql += ")"; + SqliteCommand cmd = new SqliteCommand(sql); + + // this provides the binding for all our parameters, so + // much less code than it used to be + foreach (KeyValuePair kvp in defs) + { + cmd.Parameters.Add(createSqliteParameter(kvp.Key, kvp.Value)); + } + return cmd; + } + + private SqliteCommand createUpdateCommand(string table, string pk, Dictionary defs) + { + string sql = "update " + table + " set "; + string subsql = ""; + foreach (string key in defs.Keys) + { + if (subsql.Length > 0) + { // a map function would rock so much here + subsql += ", "; + } + subsql += key + "= :" + key; + } + sql += subsql; + sql += " where " + pk; + SqliteCommand cmd = new SqliteCommand(sql); + + // this provides the binding for all our parameters, so + // much less code than it used to be + foreach (KeyValuePair kvp in defs) + { + cmd.Parameters.Add(createSqliteParameter(kvp.Key, kvp.Value)); + } + return cmd; + } + + private void setupItemsCommands(SqliteDataAdapter da, SqliteConnection conn) + { + Dictionary invDataDefs = createInventoryItemsDataDefs(); + + da.InsertCommand = createInsertCommand("inventoryitems", invDataDefs); + da.InsertCommand.Connection = conn; + + da.UpdateCommand = createUpdateCommand("inventoryitems", "UUID=:UUID", invDataDefs); + da.UpdateCommand.Connection = conn; + + SqliteCommand delete = new SqliteCommand("delete from inventoryitems where UUID = :UUID"); + delete.Parameters.Add(createSqliteParameter("UUID", DbType.String)); + delete.Connection = conn; + da.DeleteCommand = delete; + } + + private void setupFoldersCommands(SqliteDataAdapter da, SqliteConnection conn) + { + Dictionary shapeDataDefs = createShapeDataDefs(); + + da.InsertCommand = createInsertCommand("inventoryfolders", shapeDataDefs); + da.InsertCommand.Connection = conn; + + da.UpdateCommand = createUpdateCommand("inventoryfolders", "UUID=:UUID", shapeDataDefs); + da.UpdateCommand.Connection = conn; + + SqliteCommand delete = new SqliteCommand("delete from inventoryfolders where UUID = :UUID"); + delete.Parameters.Add(createSqliteParameter("UUID", DbType.String)); + delete.Connection = conn; + da.DeleteCommand = delete; + } + + private InventoryFolderBase buildFolder(DataRow row) + { + InventoryFolderBase folder = new InventoryFolderBase(); + folder.folderID = new LLUUID((string)row["UUID"]); + folder.name = (string)row["name"]; + folder.agentID = new LLUUID((string)row["agentID"]); + folder.parentID = new LLUUID((string)row["parentID"]); + folder.type = Convert.ToInt16(row["type"]); + folder.version = Convert.ToUInt16(row["version"]); + return folder; + } + + private void fillFolderRow(DataRow row, InventoryFolderBase folder) + { + row["UUID"] = folder.folderID; + row["name"] = folder.name; + row["agentID"] = folder.agentID; + row["parentID"] = folder.parentID; + row["type"] = folder.type; + row["version"] = folder.version; + } + + private void addFolder(InventoryFolderBase folder) + { + DataTable inventoryFolderTable = ds.Tables["inventoryfolders"]; + + DataRow inventoryRow = inventoryFolderTable.Rows.Find(folder.folderID); + if (inventoryRow == null) + { + inventoryRow = inventoryFolderTable.NewRow(); + fillFolderRow(inventoryRow, folder); + inventoryFolderTable.Rows.Add(inventoryRow); + } + else + { + fillFolderRow(inventoryRow, folder); + } + + this.invFoldersDa.Update(ds, "inventoryfolders"); + } + + public void Shutdown() + { + // TODO: DataSet commit + } + + /// + /// Closes the interface + /// + public void Close() + { + } + + /// + /// The plugin being loaded + /// + /// A string containing the plugin name + public string getName() + { + return "SQLite Inventory Data Interface"; + } + + /// + /// The plugins version + /// + /// A string containing the plugin version + public string getVersion() + { + return "0.1"; + } + + /// + /// Returns a list of inventory items contained within the specified folder + /// + /// The UUID of the target folder + /// A List of InventoryItemBase items + public List getInventoryInFolder(LLUUID folderID) + { + return null; + } + + /// + /// Returns a list of the root folders within a users inventory + /// + /// The user whos inventory is to be searched + /// A list of folder objects + public List getUserRootFolders(LLUUID user) + { + return null; + } + + /// + /// Returns the users inventory root folder. + /// + /// The UUID of the user who is having inventory being returned + /// Root inventory folder + public InventoryFolderBase getUserRootFolder(LLUUID user) + { + List folders = new List(); + DataTable inventoryFolderTable = ds.Tables["inventoryfolders"]; + string selectExp = "agentID = '"+ user.ToString()+"' AND parentID = '"+ LLUUID.Zero.ToString()+"'"; + DataRow[] rows = inventoryFolderTable.Select(selectExp); + foreach (DataRow row in rows) + { + folders.Add(this.buildFolder(row)); + } + + if (folders.Count == 1) + { + //we found the root + //System.Console.WriteLine("found root inventory folder"); + return folders[0]; + } + else if (folders.Count > 1) + { + //err shouldn't be more than one root + //System.Console.WriteLine("found more than one root inventory folder"); + } + else if (folders.Count == 0) + { + // no root? + //System.Console.WriteLine("couldn't find root inventory folder"); + } + + return null; + } + + /// + /// Returns a list of inventory folders contained in the folder 'parentID' + /// + /// The folder to get subfolders for + /// A list of inventory folders + public List getInventoryFolders(LLUUID parentID) + { + List folders = new List(); + DataTable inventoryFolderTable = ds.Tables["inventoryfolders"]; + string selectExp = "parentID = '" + parentID.ToString() + "'"; + DataRow[] rows = inventoryFolderTable.Select(selectExp); + foreach (DataRow row in rows) + { + folders.Add(this.buildFolder(row)); + } + // System.Console.WriteLine("found " + folders.Count + " inventory folders"); + return folders; + } + + /// + /// Returns an inventory item by its UUID + /// + /// The UUID of the item to be returned + /// A class containing item information + public InventoryItemBase getInventoryItem(LLUUID item) + { + return null; + } + + /// + /// Returns a specified inventory folder by its UUID + /// + /// The UUID of the folder to be returned + /// A class containing folder information + public InventoryFolderBase getInventoryFolder(LLUUID folder) + { + return null; + } + + /// + /// Creates a new inventory item based on item + /// + /// The item to be created + public void addInventoryItem(InventoryItemBase item) + { + } + + /// + /// Updates an inventory item with item (updates based on ID) + /// + /// The updated item + public void updateInventoryItem(InventoryItemBase item) + { + } + + /// + /// Adds a new folder specified by folder + /// + /// The inventory folder + public void addInventoryFolder(InventoryFolderBase folder) + { + this.addFolder(folder); + } + + /// + /// Updates a folder based on its ID with folder + /// + /// The inventory folder + public void updateInventoryFolder(InventoryFolderBase folder) + { + this.addFolder(folder); + } + } +} + -- cgit v1.1