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; } public InventoryItemBase BuildItem(DataRow row) { InventoryItemBase item = new InventoryItemBase(); item.inventoryID = new LLUUID((string)row["UUID"]); item.assetID = new LLUUID((string)row["assetID"]); item.assetType = Convert.ToInt32(row["assetType"]); item.invType = Convert.ToInt32(row["invType"]); item.parentFolderID = new LLUUID((string)row["parentFolderID"]); item.avatarID = new LLUUID((string)row["avatarID"]); item.creatorsID = new LLUUID((string)row["creatorsID"]); item.inventoryName =(string) row["inventoryName"]; item.inventoryDescription = (string) row["inventoryDescription"]; item.inventoryNextPermissions = Convert.ToUInt32(row["inventoryNextPermissions"]); item.inventoryCurrentPermissions = Convert.ToUInt32(row["inventoryCurrentPermissions"]); item.inventoryBasePermissions = Convert.ToUInt32(row["inventoryBasePermissions"]); item.inventoryEveryOnePermissions = Convert.ToUInt32(row["inventoryEveryOnePermissions"]); return item; } private void fillItemRow(DataRow row, InventoryItemBase item) { row["UUID"] = item.inventoryID; row["assetID"] = item.assetID; row["assetType"] = item.assetType; row["invType"] = item.invType; row["parentFolderID"] = item.parentFolderID; row["avatarID"] = item.avatarID; row["creatorsID"] = item.creatorsID; row["inventoryName"] = item.inventoryName; row["inventoryDescription"] = item.inventoryDescription; row["inventoryNextPermissions"] = item.inventoryNextPermissions; row["inventoryCurrentPermissions"] = item.inventoryCurrentPermissions; row["inventoryBasePermissions"] = item.inventoryBasePermissions; row["inventoryEveryOnePermissions"] = item.inventoryEveryOnePermissions; } 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"); } private void addItem(InventoryItemBase item) { DataTable inventoryItemTable = ds.Tables["inventoryitems"]; DataRow inventoryRow = inventoryItemTable.Rows.Find(item.inventoryID); if (inventoryRow == null) { inventoryRow = inventoryItemTable.NewRow(); fillItemRow(inventoryRow, item); inventoryItemTable.Rows.Add(inventoryRow); } else { fillItemRow(inventoryRow, item); } this.invItemsDa.Update(ds, "inventoryitems"); } 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) { List retval = new List(); DataTable inventoryItemTable = ds.Tables["inventoryitems"]; string selectExp = "parentFolderID = '" + folderID.ToString() + "'"; DataRow[] rows = inventoryItemTable.Select(selectExp); foreach (DataRow row in rows) { retval.Add(BuildItem(row)); } return retval; } /// /// 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) { this.addItem(item); } /// /// Updates an inventory item with item (updates based on ID) /// /// The updated item public void updateInventoryItem(InventoryItemBase item) { this.addItem(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); } } }