public void AvatarNotesUpdate(IClientAPI remoteClient, UUID queryTargetID, string queryNotes)
{
UUID avatarID = remoteClient.AgentId;
// allow leading spaces for formatting, but TrimEnd will help us detect an empty field other than spaces
string notes = queryNotes.TrimEnd();
// filter out the old text that said there was no text. ;)
if (notes == LEGACY_EMPTY)
{
notes = String.Empty;
}
using (ISimpleDB db = _connFactory.GetConnection())
{
Dictionary <string, object> parms = new Dictionary <string, object>();
parms.Add("?avatarID", avatarID);
parms.Add("?targetID", queryTargetID);
parms.Add("?notes", notes);
string query;
if (String.IsNullOrEmpty(notes))
{
query = "DELETE FROM usernotes WHERE useruuid=?avatarID AND targetuuid=?targetID";
}
else
{
query = "INSERT INTO usernotes(useruuid, targetuuid, notes) VALUES(?avatarID,?targetID,?notes) ON DUPLICATE KEY UPDATE notes=?notes";
}
db.QueryNoResults(query, parms);
}
}
public InventoryFolderBase findUserFolderForType(UUID userId, int typeId)
{
string query = "SELECT * FROM inventoryfolders WHERE agentID = ?agentId AND type = ?type;";
Dictionary <string, object> parms = new Dictionary <string, object>();
parms.Add("?agentId", userId);
parms.Add("?type", typeId);
try
{
using (ISimpleDB conn = _connFactory.GetConnection())
{
using (IDataReader reader = conn.QueryAndUseReader(query, parms))
{
if (reader.Read())
{
// A null item (because something went wrong) breaks everything in the folder
return(readInventoryFolder(reader));
}
else
{
return(null);
}
}
}
}
catch (Exception e)
{
m_log.Error(e.ToString());
return(null);
}
}
private void CacheRdbHosts()
{
using (ISimpleDB db = _connFactory.GetConnection())
{
CacheRdbHosts(db);
}
}
/// <summary>
/// Returns a specified inventory folder
/// </summary>
/// <param name="folder">The folder to return</param>
/// <returns>A folder class</returns>
public InventoryFolderBase getInventoryFolder(UUID folderID)
{
try
{
using (ISimpleDB conn = _connFactory.GetConnection())
{
string query = "SELECT * FROM inventoryfolders WHERE folderID = ?uuid";
Dictionary <string, object> parms = new Dictionary <string, object>();
parms.Add("?uuid", folderID.ToString());
using (IDataReader reader = conn.QueryAndUseReader(query, parms))
{
if (reader.Read())
{
InventoryFolderBase folder = readInventoryFolder(reader);
return(folder);
}
else
{
return(null);
}
}
}
}
catch (Exception e)
{
m_log.Error(e.ToString());
return(null);
}
}
private void DBStoreMuteListEntry(UUID AgentID, UUID MuteID, MuteListEntry entry, bool isUpdate)
{
using (ISimpleDB db = _connectionFactory.GetConnection())
{
string query;
if (isUpdate)
{
query = "UPDATE mutelist " +
"SET " +
" MuteType = ?muteType" +
" , MuteName = ?muteName" +
" , MuteFlags= ?muteFlags" +
" WHERE " +
" AgentID = ?agentID AND MuteID = ?muteID";
}
else
{
query = "INSERT INTO mutelist " +
"(AgentID, MuteType, MuteID, MuteName, MuteFlags) " +
"VALUES(?AgentID, ?MuteType, ?MuteID, ?MuteName, ?MuteFlags)";
}
Dictionary <string, object> parms = new Dictionary <string, object>();
parms.Add("?agentID", AgentID);
parms.Add("?muteID", MuteID);
parms.Add("?muteType", entry.m_Type);
parms.Add("?muteName", entry.m_Name);
parms.Add("?muteFlags", entry.m_Flags);
db.QueryNoResults(query, parms);
}
}
/// <summary>
/// This doesn't return the mutelist (mutees) for a user, it returns all users who have this user/object muted (muters).
/// Implemented as a single DB call.
/// </summary>
/// <param name="id">The user or object that may be muted.</param>
/// <returns>UUIDs of those who have it muted.</returns>
public List <UUID> GetInverseMuteList(UUID muteID)
{
List <UUID> muters = new List <UUID>();
using (ISimpleDB db = _connectionFactory.GetConnection())
{
string query = " SELECT * FROM mutelist WHERE MuteID = ?muteID";
Dictionary <string, object> parms = new Dictionary <string, object>();
parms.Add("?muteID", muteID);
List <Dictionary <string, string> > results = db.QueryWithResults(query, parms);
if (results.Count < 1 || results[0]["AgentID"] == null)
{
return(muters); // empty list
}
foreach (Dictionary <string, string> result in results)
{
UUID MuteID = new UUID(result["AgentID"]);
muters.Add(MuteID);
}
}
return(muters);
}
/// <summary>
/// Returns a sim profile from it's Region name string
/// </summary>
/// <returns>The sim profile</returns>
override public RegionProfileData GetProfileByString(string regionName)
{
if (regionName.Length > 2)
{
try
{
Dictionary <string, object> parms = new Dictionary <string, object>();
// Add % because this is a like query.
parms["?regionName"] = regionName + "%";
using (ISimpleDB conn = _connFactory.GetConnection())
{
string query = "SELECT * FROM regions WHERE regionName LIKE ?regionName ORDER BY LENGTH(regionName) ASC LIMIT 1";
using (IDataReader reader = conn.QueryAndUseReader(query, parms))
{
RegionProfileData row = this.readSimRow(reader);
return(row);
}
}
}
catch (Exception e)
{
m_log.Error(e.ToString());
return(null);
}
}
m_log.Error("[GRID DB]: Searched for a Region Name shorter then 3 characters");
return(null);
}
public RDBConnectionQuery(ISimpleDB rdb, string query, uint queryFlags, Dictionary <string, object> parms)
{
m_rdb = rdb;
m_query = query;
m_parms = parms;
RefreshResults();
}
// Picks Handler
public void HandleAvatarPicksRequest(Object sender, List <String> args)
{
if (!(sender is IClientAPI))
{
return;
}
IClientAPI remoteClient = (IClientAPI)sender;
UUID avatarID = new UUID(args[0]);
using (ISimpleDB db = _connFactory.GetConnection())
{
Dictionary <UUID, string> picksRequest = new Dictionary <UUID, string>();
Dictionary <string, object> parms = new Dictionary <string, object>();
parms.Add("?avatarID", avatarID);
string query = "SELECT pickuuid, name from userpicks " +
"WHERE creatoruuid=?avatarID";
List <Dictionary <string, string> > results = db.QueryWithResults(query, parms);
foreach (Dictionary <string, string> row in results)
{
picksRequest[new UUID(row["pickuuid"].ToString())] = row["name"].ToString();
}
remoteClient.SendAvatarPicksReply(avatarID,
picksRequest);
}
}
/// <summary>
/// Return the current regions UP after possibly refreshing if stale.
/// </summary>
/// <returns>
/// null if could not fetch the list (error, unreliable list)
/// a list (possibly empty although it should always find this one) otherwise
/// </returns>
protected List <UUID> UpdateRegionsList(ISimpleDB coredb)
{
List <UUID> regions = new List <UUID>();
lock (mRegionsOnline)
{
// The viewer fires 3 parallel requests together.
// Protect against multiple initial parallel requests by using
// the lock to block the other threads during the refresh. They need it anyway.
// If we're up to date though, just return it.
if ((DateTime.Now - mRegionsOnlineStamp).TotalSeconds < REGIONS_CACHE_TIME)
{
return(mRegionsOnline);
}
List <Dictionary <string, string> > results = coredb.QueryWithResults("SELECT uuid FROM regions LIMIT 999999999999"); // no limit
foreach (var row in results)
{
regions.Add(new UUID(row["UUID"]));
}
// Now stamp it inside the lock so that if we reenter,
// second caller will not make another request.
mRegionsOnline = regions;
mRegionsOnlineStamp = DateTime.Now;
}
return(regions);
}
public void HandleAvatarNotesRequest(Object sender, string method, List <String> args)
{
if (!(sender is IClientAPI))
{
return;
}
IClientAPI remoteClient = (IClientAPI)sender;
UUID avatarID = remoteClient.AgentId;
UUID targetAvatarID = new UUID(args[0]);
using (ISimpleDB db = _connFactory.GetConnection())
{
Dictionary <string, object> parms = new Dictionary <string, object>();
parms.Add("?avatarID", avatarID);
parms.Add("?targetID", targetAvatarID);
string query = "SELECT notes from usernotes where useruuid=?avatarID AND targetuuid=?targetID";
List <Dictionary <string, string> > notesResult = db.QueryWithResults(query, parms);
string notes = String.Empty;
if (notesResult.Count > 0)
{
notes = notesResult[0]["notes"];
}
if (notes == LEGACY_EMPTY) // filter out the old text that said there was no text. ;)
{
notes = String.Empty;
}
remoteClient.SendAvatarNotesReply(targetAvatarID, notes);
}
}
void client_OnGrantUserRights(IClientAPI sender, OpenMetaverse.UUID grantor, OpenMetaverse.UUID grantee, int rights)
{
if (sender.AgentId != grantor)
{
return;
}
//set the user rights on the DB
using (ISimpleDB db = _connFactory.GetConnection())
{
Dictionary <string, object> parms = new Dictionary <string, object>();
parms.Add("rights", rights);
parms.Add("ownerID", grantor);
parms.Add("friendID", grantee);
db.QueryNoResults("UPDATE userfriends " +
"SET friendPerms = ?rights " +
"WHERE ownerID = ?ownerID AND friendID = ?friendID;",
parms);
}
m_scene.CommsManager.UserService.UpdateUserFriendPerms(grantor, grantee, (uint)rights);
sender.SendChangeUserRights(grantor, grantee, rights);
}
/// <summary>
/// Move an inventory folder
/// </summary>
/// <param name="folder">Folder to move</param>
/// <remarks>UPDATE inventoryfolders SET parentFolderID=?parentFolderID WHERE folderID=?folderID</remarks>
public void moveInventoryFolder(InventoryFolderBase folder, UUID parentId)
{
string query = "UPDATE inventoryfolders SET parentFolderID=?parentFolderID WHERE folderID=?folderID";
try
{
using (ISimpleDB conn = _connFactory.GetConnection())
{
Dictionary <string, object> parms = new Dictionary <string, object>();
parms.Add("?folderID", folder.ID.ToString());
parms.Add("?parentFolderID", parentId.ToString());
conn.QueryNoResults(query, parms);
folder.ParentID = parentId; // Only change if the above succeeded.
// Increment both the old and the new parents - checking for null.
this.IncrementSpecifiedFolderVersion(conn, parentId);
this.IncrementSpecifiedFolderVersion(conn, folder.ParentID);
}
}
catch (Exception e)
{
m_log.Error(e.ToString());
}
}
/// <summary>
/// Returns a specified inventory item
/// </summary>
/// <param name="item">The item to return</param>
/// <returns>An inventory item</returns>
public InventoryItemBase getInventoryItem(UUID itemID)
{
try
{
using (ISimpleDB conn = _connFactory.GetConnection())
{
string query = "SELECT * FROM inventoryitems WHERE inventoryID = ?uuid";
Dictionary <string, object> parms = new Dictionary <string, object>();
parms.Add("?uuid", itemID.ToString());
using (IDataReader reader = conn.QueryAndUseReader(query, parms))
{
InventoryItemBase item = null;
if (reader.Read())
{
item = readInventoryItem(reader);
}
return(item);
}
}
}
catch (Exception e)
{
m_log.Error(e.ToString());
}
return(null);
}
/// <summary>
/// Returns up to maxNum profiles of regions that have a name starting with namePrefix
/// </summary>
/// <param name="name">The name to match against</param>
/// <param name="maxNum">Maximum number of profiles to return</param>
/// <returns>A list of sim profiles</returns>
override public List <RegionProfileData> GetRegionsByName(string namePrefix, uint maxNum)
{
try
{
Dictionary <string, object> parms = new Dictionary <string, object>();
parms["?name"] = namePrefix + "%";
using (ISimpleDB conn = _connFactory.GetConnection())
{
using (IDataReader reader
= conn.QueryAndUseReader("SELECT * FROM regions WHERE regionName LIKE ?name", parms))
{
RegionProfileData row;
List <RegionProfileData> rows = new List <RegionProfileData>();
while (rows.Count < maxNum && (row = this.readSimRow(reader)) != null)
{
rows.Add(row);
}
return(rows);
}
}
}
catch (Exception e)
{
m_log.Error(e.ToString());
return(null);
}
}
/// <summary>
/// Return a list of folders in a users inventory contained within the specified folder.
/// This method is only used in tests - in normal operation the user always have one,
/// and only one, root folder.
/// </summary>
/// <param name="parentID">The folder to search</param>
/// <returns>A list of inventory folders</returns>
public List <InventoryFolderBase> getInventoryFolders(UUID parentID)
{
try
{
using (ISimpleDB conn = _connFactory.GetConnection())
{
string query = "SELECT * FROM inventoryfolders WHERE parentFolderID = ?uuid";
Dictionary <string, object> parms = new Dictionary <string, object>();
parms.Add("?uuid", parentID.ToString());
using (IDataReader reader = conn.QueryAndUseReader(query, parms))
{
List <InventoryFolderBase> items = new List <InventoryFolderBase>();
while (reader.Read())
{
InventoryFolderBase folder = readInventoryFolder(reader);
items.Add(folder);
}
return(items);
}
}
}
catch (Exception e)
{
m_log.Error(e.ToString());
return(null);
}
}
/// <summary>
/// Returns a list of items in a specified folder
/// </summary>
/// <param name="folderID">The folder to search</param>
/// <returns>A list containing inventory items</returns>
public List <InventoryItemBase> getInventoryInFolder(UUID folderID)
{
try
{
using (ISimpleDB conn = _connFactory.GetConnection())
{
string query = "SELECT * FROM inventoryitems WHERE parentFolderID = ?uuid";
Dictionary <string, object> parms = new Dictionary <string, object>();
parms.Add("?uuid", folderID.ToString());
List <InventoryItemBase> items = new List <InventoryItemBase>();
using (IDataReader reader = conn.QueryAndUseReader(query, parms))
{
while (reader.Read())
{
// A null item (because something went wrong) breaks everything in the folder
InventoryItemBase item = readInventoryItem(reader);
if (item != null)
{
items.Add(item);
}
}
}
return(items);
}
}
catch (Exception e)
{
m_log.Error(e.ToString());
return(null);
}
}
public List <InventoryItemBase> getAllItems(UUID avatarID)
{
try
{
using (ISimpleDB conn = _connFactory.GetConnection())
{
string query = "SELECT * FROM inventoryitems WHERE avatarId = ?uuid";
Dictionary <string, object> parms = new Dictionary <string, object>();
parms.Add("?uuid", avatarID.ToString());
using (IDataReader result = conn.QueryAndUseReader(query, parms))
{
List <InventoryItemBase> list = new List <InventoryItemBase>();
while (result.Read())
{
InventoryItemBase item = readInventoryItem(result);
if (item != null)
{
list.Add(item);
}
}
return(list);
}
}
}
catch (Exception e)
{
m_log.Error(e.ToString());
return(null);
}
}
/// <summary>
/// Returns the folder info for the given folder, or null if one could not be found.
/// </summary>
/// <param name="userId"></param>
/// <param name="type"></param>
/// <returns></returns>
public InventoryFolderBase findFolder(UUID owner, UUID folderID)
{
string query = "SELECT * FROM inventoryfolders WHERE agentID = ?agentId AND folderId = ?folderId;";
Dictionary <string, object> parms = new Dictionary <string, object>();
parms.Add("?agentId", owner);
parms.Add("?folderId", folderID);
try
{
using (ISimpleDB conn = _connFactory.GetConnection())
{
using (IDataReader reader = conn.QueryAndUseReader(query, parms))
{
if (reader.Read())
{
// A null item (because something went wrong) breaks everything in the folder
return(readInventoryFolder(reader));
}
else
{
m_log.WarnFormat("[Inventory]: findUserTopLevelFolderFor: No top-level folders.");
return(null);
}
}
}
}
catch (Exception e)
{
m_log.Error(e.ToString());
return(null);
}
}
// Picks Request
public void HandlePickInfoRequest(Object sender, string method, List <String> args)
{
if (!(sender is IClientAPI))
{
return;
}
IClientAPI remoteClient = (IClientAPI)sender;
UUID avatarID = new UUID(args[0]);
UUID pickID = new UUID(args[1]);
using (ISimpleDB db = _connFactory.GetConnection())
{
Dictionary <string, object> parms = new Dictionary <string, object>();
parms.Add("?avatarID", avatarID);
parms.Add("?pickID", pickID);
string query = "SELECT * from userpicks WHERE creatoruuid=?avatarID AND pickuuid=?pickID";
List <Dictionary <string, string> > results = db.QueryWithResults(query, parms);
bool topPick = new bool();
UUID parcelUUID = new UUID();
string name = String.Empty;
string description = String.Empty;
UUID snapshotID = new UUID();
string userName = String.Empty;
string originalName = String.Empty;
string simName = String.Empty;
Vector3 globalPos = new Vector3();
int sortOrder = new int();
bool enabled = new bool();
foreach (Dictionary <string, string> row in results)
{
topPick = Boolean.Parse(row["toppick"]);
parcelUUID = UUID.Parse(row["parceluuid"]);
name = row["name"];
description = row["description"];
snapshotID = UUID.Parse(row["snapshotuuid"]);
userName = row["user"];
//userName = row["simname"];
originalName = row["originalname"];
simName = row["simname"];
globalPos = Vector3.Parse(row["posglobal"]);
sortOrder = Convert.ToInt32(row["sortorder"]);
enabled = Boolean.Parse(row["enabled"]);
}
remoteClient.SendPickInfoReply(pickID, avatarID,
topPick, parcelUUID, name, description,
snapshotID, userName, originalName,
simName, globalPos, sortOrder,
enabled);
}
}
/// <summary>
/// Updates the specified inventory item
/// </summary>
/// <param name="item">Inventory item to update</param>
public void updateInventoryItem(InventoryItemBase item)
{
//addInventoryItem(item);
/* 12/9/2009 - Ele's Edit - Rather than simply adding a whole new item, which seems kind of pointless to me,
* let's actually try UPDATING the item as it should be. This is not fully functioning yet from the updating of items
* within Scene.Inventory.cs MoveInventoryItem yet. Not sure the effect it will have on the rest of the updates either, as they
* originally pointed back to addInventoryItem above.
*/
string query =
"UPDATE inventoryitems SET assetID=?assetID, assetType=?assetType, parentFolderID=?parentFolderID, "
+ "avatarID=?avatarID, inventoryName=?inventoryName, inventoryDescription=?inventoryDescription, inventoryNextPermissions=?inventoryNextPermissions, "
+ "inventoryCurrentPermissions=?inventoryCurrentPermissions, invType=?invType, creatorID=?creatorID, inventoryBasePermissions=?inventoryBasePermissions, "
+ "inventoryEveryOnePermissions=?inventoryEveryOnePermissions, inventoryGroupPermissions=?inventoryGroupPermissions, salePrice=?salePrice, "
+ "saleType=?saleType, creationDate=?creationDate, groupID=?groupID, groupOwned=?groupOwned, flags=?flags "
+ "WHERE inventoryID=?inventoryID";
try
{
using (ISimpleDB conn = _connFactory.GetConnection())
{
Dictionary <string, object> parms = new Dictionary <string, object>();
parms.Add("?inventoryID", item.ID.ToString());
parms.Add("?assetID", item.AssetID.ToString());
parms.Add("?assetType", item.AssetType.ToString());
parms.Add("?parentFolderID", item.Folder.ToString());
parms.Add("?avatarID", item.Owner.ToString());
parms.Add("?inventoryName", item.Name);
parms.Add("?inventoryDescription", item.Description);
parms.Add("?inventoryNextPermissions", item.NextPermissions.ToString());
parms.Add("?inventoryCurrentPermissions", item.CurrentPermissions.ToString());
parms.Add("?invType", item.InvType);
parms.Add("?creatorID", item.CreatorId);
parms.Add("?inventoryBasePermissions", item.BasePermissions);
parms.Add("?inventoryEveryOnePermissions", item.EveryOnePermissions);
parms.Add("?inventoryGroupPermissions", item.GroupPermissions);
parms.Add("?salePrice", item.SalePrice);
parms.Add("?saleType", item.SaleType);
parms.Add("?creationDate", item.CreationDate);
parms.Add("?groupID", item.GroupID);
parms.Add("?groupOwned", item.GroupOwned);
parms.Add("?flags", item.Flags);
conn.QueryNoResults(query, parms);
// Also increment the parent version number if not null.
this.IncrementSpecifiedFolderVersion(conn, item.Folder);
}
}
catch (Exception e)
{
m_log.Error(e.ToString());
}
}
private void DBRemoveMuteListEntry(UUID AgentID, UUID MuteID)
{
using (ISimpleDB db = _connectionFactory.GetConnection())
{
Dictionary <string, object> parms = new Dictionary <string, object>();
string query = "DELETE FROM mutelist WHERE AgentID = ?agentID AND MuteID = ?muteID";
parms.Add("?agentID", AgentID);
parms.Add("?muteID", MuteID);
db.QueryNoResults(query, parms);
}
}
/// <summary>
/// Delete a folder from database. Must be called from within a using{} block for the database connection.
/// </summary>
/// Passing in the connection allows for consolidation of the DB connections, important as this method is often called from an inner loop.
/// <param name="folderID">the folder UUID</param>
/// <param name="conn">the database connection</param>
private void deleteOneFolder(ISimpleDB conn, InventoryFolderBase folder)
{
string query = "DELETE FROM inventoryfolders WHERE folderID=?uuid";
Dictionary <string, object> parms = new Dictionary <string, object>();
parms.Add("?uuid", folder.ID.ToString());
conn.QueryNoResults(query, parms);
// As the callers of this function will increment the version, there's no need to do so here.
}
/// <summary>
/// Increments the version of the passed folder, making sure the folder isn't Zero. Must be called from within a using{} block!
/// </summary>
/// <param name="conn">Database connection.</param>
/// <param name="folderId">Folder UUID to increment</param>
private void IncrementSpecifiedFolderVersion(ISimpleDB conn, UUID folderId)
{
if (folderId != UUID.Zero)
{
string query = "update inventoryfolders set version=version+1 where folderID = ?folderID";
Dictionary <string, object> updParms = new Dictionary <string, object>();
updParms.Add("?folderID", folderId.ToString());
conn.QueryNoResults(query, updParms);
}
}
/// <summary>
/// <para>Initializes Grid interface</para>
/// <para>
/// <list type="bullet">
/// <item>Loads and initializes the MySQL storage plugin</item>
/// <item>Warns and uses the obsolete mysql_connection.ini if connect string is empty.</item>
/// <item>Check for migration</item>
/// </list>
/// </para>
/// </summary>
/// <param name="connect">connect string.</param>
override public void Initialize(string connect)
{
m_log.Info("[MySQLGridData.InWorldz]: Starting up");
_connFactory = new ConnectionFactory("MySQL", connect);
using (ISimpleDB db = _connFactory.GetConnection())
{
m_log.Info("[MySQLGridData.InWorldz]: Sucessfully made connection to database");
}
}
private void CacheRdbHosts(ISimpleDB db)
{
List <Dictionary <string, string> > hostNames = db.QueryWithResults("SELECT host_name FROM RdbHosts");
_rdbHostCache.Clear();
foreach (var hostNameDict in hostNames)
{
_rdbHostCache.Add(hostNameDict["host_name"]);
}
_rdbCacheTime = DateTime.Now;
}
private string[] CheckAndRetrieveRdbHostList(ISimpleDB coreDb)
{
lock (_rdbHostCache)
{
if (DateTime.Now - _rdbCacheTime >= RDB_CACHE_TIMEOUT)
{
//recache
CacheRdbHosts(coreDb);
}
return(_rdbHostCache.ToArray());
}
}
/// <summary>
/// Adds a specified item to the database
/// </summary>
/// <param name="item">The inventory item</param>
public void addInventoryItem(InventoryItemBase item)
{
string query =
"REPLACE INTO inventoryitems (inventoryID, assetID, assetType, parentFolderID, avatarID, inventoryName"
+ ", inventoryDescription, inventoryNextPermissions, inventoryCurrentPermissions, invType"
+ ", creatorID, inventoryBasePermissions, inventoryEveryOnePermissions, inventoryGroupPermissions, salePrice, saleType"
+ ", creationDate, groupID, groupOwned, flags) VALUES ";
query +=
"(?inventoryID, ?assetID, ?assetType, ?parentFolderID, ?avatarID, ?inventoryName, ?inventoryDescription"
+ ", ?inventoryNextPermissions, ?inventoryCurrentPermissions, ?invType, ?creatorID"
+ ", ?inventoryBasePermissions, ?inventoryEveryOnePermissions, ?inventoryGroupPermissions, ?salePrice, ?saleType, ?creationDate"
+ ", ?groupID, ?groupOwned, ?flags)";
try
{
using (ISimpleDB conn = _connFactory.GetConnection())
{
Dictionary <string, object> parms = new Dictionary <string, object>();
parms.Add("?inventoryID", item.ID.ToString());
parms.Add("?assetID", item.AssetID.ToString());
parms.Add("?assetType", item.AssetType.ToString());
parms.Add("?parentFolderID", item.Folder.ToString());
parms.Add("?avatarID", item.Owner.ToString());
parms.Add("?inventoryName", item.Name);
parms.Add("?inventoryDescription", item.Description);
parms.Add("?inventoryNextPermissions", item.NextPermissions.ToString());
parms.Add("?inventoryCurrentPermissions", item.CurrentPermissions.ToString());
parms.Add("?invType", item.InvType);
parms.Add("?creatorID", item.CreatorId);
parms.Add("?inventoryBasePermissions", item.BasePermissions);
parms.Add("?inventoryEveryOnePermissions", item.EveryOnePermissions);
parms.Add("?inventoryGroupPermissions", item.GroupPermissions);
parms.Add("?salePrice", item.SalePrice);
parms.Add("?saleType", item.SaleType);
parms.Add("?creationDate", item.CreationDate);
parms.Add("?groupID", item.GroupID);
parms.Add("?groupOwned", item.GroupOwned);
parms.Add("?flags", item.Flags);
conn.QueryNoResults(query, parms);
// Also increment the parent version number if not null.
this.IncrementSpecifiedFolderVersion(conn, item.Folder);
}
}
catch (Exception e)
{
m_log.Error(e.ToString());
}
}
// Picks Delete
public void PickDelete(IClientAPI remoteClient, UUID queryPickID)
{
UUID avatarID = remoteClient.AgentId;
using (ISimpleDB db = _connFactory.GetConnection())
{
Dictionary <string, object> parms = new Dictionary <string, object>();
parms.Add("?pickID", queryPickID);
parms.Add("?avatarID", avatarID);
string query = "delete from userpicks where pickuuid=?pickID AND creatoruuid=?avatarID";
db.QueryNoResults(query, parms);
}
}
/// <summary>
/// Don't use this for messages broadcast to more than one user
/// </summary>
/// <param name="sender"></param>
/// <param name="target"></param>
/// <returns>Returns true if target has sender muted.</returns>
public bool IsMuted(UUID sender, UUID target)
{
using (ISimpleDB db = _connectionFactory.GetConnection())
{
string query = "SELECT COUNT(*) as MuteCount FROM mutelist WHERE AgentID = ?agentID AND MuteID = ?muteID";
Dictionary <string, object> parms = new Dictionary <string, object>();
parms.Add("?agentID", target);
parms.Add("?muteID", sender);
List <Dictionary <string, string> > results = db.QueryWithResults(query, parms);
return(Convert.ToInt32(results[0]["MuteCount"]) != 0);
}
}
private bool AgentHasActiveGroup(ISimpleDB db, OpenMetaverse.UUID agentID)
{
string query
= "SELECT COUNT(*) AS agentCount " +
"FROM osagent " +
"WHERE AgentID = ?agentID";
Dictionary<string, object> parms = new Dictionary<string, object>();
parms.Add("?agentID", agentID);
List<Dictionary<string, string>> results = db.QueryWithResults(query, parms);
if (results.Count > 0 && Int32.Parse(results[0]["agentCount"]) > 0)
{
return true;
}
else
{
return false;
}
}
// WARNING: This function only supports queries that result in a TOTAL number of results of 100 or less.
// If too many results come back, the rest can be discarded/lost. Intended for searching for a specific parcel ID (1 result).
// See AvatarSearchModule.cs for the alternative more complex implementation that avoids this restriction.
private List<Dictionary<string, string>> DoLandQueryAndCombine(ISimpleDB coreDb, string query, Dictionary<string, object> parms)
{
string[] rdbHosts = this.CheckAndRetrieveRdbHostList(coreDb);
if (rdbHosts.Length == 0)
{
// RDB not configured. Fall back to core db.
return coreDb.QueryWithResults(query, parms);
}
List<Dictionary<string, string>> finalList = new List<Dictionary<string, string>>();
int whichDB = 0;
foreach (string host in rdbHosts)
{
ConnectionFactory rdbFactory;
if ((++whichDB == 1) || String.IsNullOrWhiteSpace(_rdbConnectionTemplateDebug))
rdbFactory = new ConnectionFactory("MySQL", String.Format(_rdbConnectionTemplate, host));
else // Special debugging support for multiple RDBs on one machine ("inworldz_rdb2", etc)
rdbFactory = new ConnectionFactory("MySQL", String.Format(_rdbConnectionTemplateDebug, host, whichDB));
using (ISimpleDB rdb = rdbFactory.GetConnection())
{
finalList.AddRange(rdb.QueryWithResults(query, parms));
}
}
return finalList;
}
/// <summary>
/// Delete all subfolders and items in a folder. Must be called from within a using{} block for the database connection.
/// </summary>
/// Passing in the connection allows for consolidation of the DB connections, important as this method is often called from an inner loop.
/// <param name="folderID">the folder UUID</param>
/// <param name="conn">the database connection</param>
private void deleteFolderContents(ISimpleDB conn, UUID folderID)
{
// Get a flattened list of all subfolders.
List<InventoryFolderBase> subFolders = getFolderHierarchy(folderID);
// Delete all sub-folders
foreach (InventoryFolderBase f in subFolders)
{
deleteFolderContents(conn, f.ID); // Recurse!
deleteOneFolder(conn, f);
}
// Delete the actual items in this folder.
string query = "DELETE FROM inventoryitems WHERE parentFolderID=?uuid";
Dictionary<string, object> parms = new Dictionary<string, object>();
parms.Add("?uuid", folderID.ToString());
conn.QueryNoResults(query, parms);
// As the callers of this function will increment the version, there's no need to do so here where this is most often ceing called from an inner loop!
}
/// <summary>
/// Increments the version of the passed folder, making sure the folder isn't Zero. Must be called from within a using{} block!
/// </summary>
/// <param name="conn">Database connection.</param>
/// <param name="folderId">Folder UUID to increment</param>
private void IncrementSpecifiedFolderVersion(ISimpleDB conn, UUID folderId)
{
if (folderId != UUID.Zero)
{
string query = "update inventoryfolders set version=version+1 where folderID = ?folderID";
Dictionary<string, object> updParms = new Dictionary<string, object>();
updParms.Add("?folderID", folderId.ToString());
conn.QueryNoResults(query, updParms);
}
}
private bool IsAgentMemberOfGroup(ISimpleDB db, OpenMetaverse.UUID agentID, OpenMetaverse.UUID groupID)
{
// If the caller intends to allow a group operation for a null group ID (e.g. ActivateGroup),
// then the caller is the one that needs to check that case and support it.
if (groupID == UUID.Zero)
return false; // don't bother making the DB call
string qIsAgentMember =
"SELECT count(AgentID) AS isMember " +
"FROM osgroupmembership " +
"WHERE AgentID = ?agentID AND GroupID = ?groupID";
Dictionary<string, object> parms = new Dictionary<string, object>();
parms.Add("?agentID", agentID);
parms.Add("?groupID", groupID);
List<Dictionary<string, string>> isAgentMemberResults = db.QueryWithResults(qIsAgentMember, parms);
if (isAgentMemberResults.Count == 0)
{
//this shouldnt happen
m_log.Error("[GROUPS]: IsAgentMemberOfGroup: Expected at least one record");
return false;
}
if (Convert.ToInt32(isAgentMemberResults[0]["isMember"]) > 0)
{
return true;
}
return false;
}
private bool IsAgentInRole(ISimpleDB db, OpenMetaverse.UUID AgentID, OpenMetaverse.UUID GroupID, OpenMetaverse.UUID RoleID)
{
string query
= "SELECT count(AgentID) as isMember " +
"FROM osgrouprolemembership " +
"WHERE AgentID = ?agentID AND RoleID = ?roleID AND GroupID = ?groupID";
Dictionary<string, object> parms = new Dictionary<string, object>();
parms.Add("?agentID", AgentID);
parms.Add("?groupID", GroupID);
parms.Add("?roleID", RoleID);
List<Dictionary<string, string>> agentInRoleResults = db.QueryWithResults(query, parms);
if (agentInRoleResults.Count == 0)
{
//this shouldnt happen
m_log.Error("[GROUPS]: IsAgentInRole: Expected at least one record");
return false;
}
else if (agentInRoleResults[0]["isMember"] == "0")
{
return false;
}
else
{
return true;
}
}
/// <summary>
/// Return the current regions UP after possibly refreshing if stale.
/// </summary>
/// <returns>
/// null if could not fetch the list (error, unreliable list)
/// a list (possibly empty although it should always find this one) otherwise
/// </returns>
protected List<UUID> UpdateRegionsList(ISimpleDB coredb)
{
List<UUID> regions = new List<UUID>();
lock (mRegionsOnline)
{
// The viewer fires 3 parallel requests together.
// Protect against multiple initial parallel requests by using
// the lock to block the other threads during the refresh. They need it anyway.
// If we're up to date though, just return it.
if ((DateTime.Now - mRegionsOnlineStamp).TotalSeconds < REGIONS_CACHE_TIME)
return mRegionsOnline;
List<Dictionary<string, string>> results = coredb.QueryWithResults("SELECT uuid FROM regions LIMIT 999999999999"); // no limit
foreach (var row in results)
{
regions.Add(new UUID(row["UUID"]));
}
// Now stamp it inside the lock so that if we reenter,
// second caller will not make another request.
mRegionsOnline = regions;
mRegionsOnlineStamp = DateTime.Now;
}
return regions;
}
public bool AgentHasOwnerRole(ISimpleDB db, GroupRequestID requestID, OpenMetaverse.UUID AgentID, OpenMetaverse.UUID GroupID)
{
GroupRecord groupInfo = GetGroupRecord(requestID, GroupID, null);
if (groupInfo == null) return false;
return IsAgentInRole(db, AgentID, GroupID, groupInfo.OwnerRoleID);
}
private string[] CheckAndRetrieveRdbHostList(ISimpleDB coreDb)
{
lock (_rdbHostCache)
{
if (DateTime.Now - _rdbCacheTime >= RDB_CACHE_TIMEOUT)
{
//recache
CacheRdbHosts(coreDb);
}
return _rdbHostCache.ToArray();
}
}
private List<Dictionary<string, string>> DoLandQueryAndCombine(IClientAPI remoteClient, ISimpleDB coreDb,
string query, Dictionary<string, object> parms,
uint queryFlags, int queryStart, int queryEnd,
List<UUID> regionsToInclude)
{
string[] rdbHosts = this.CheckAndRetrieveRdbHostList(coreDb);
if (rdbHosts.Length == 0)
{
// RDB not configured. Fall back to core db.
return coreDb.QueryWithResults(query, parms);
}
List<RDBConnectionQuery> rdbQueries = new List<RDBConnectionQuery>();
int whichDB = 0;
foreach (string host in rdbHosts)
{
// Initialize the RDB connection and initial results lists
ConnectionFactory rdbFactory;
if ((++whichDB == 1) || String.IsNullOrWhiteSpace(_rdbConnectionTemplateDebug))
rdbFactory = new ConnectionFactory("MySQL", String.Format(_rdbConnectionTemplate, host));
else // Special debugging support for multiple RDBs on one machine ("inworldz_rdb2", etc)
rdbFactory = new ConnectionFactory("MySQL", String.Format(_rdbConnectionTemplateDebug, host, whichDB));
RDBConnectionQuery rdb = new RDBConnectionQuery(rdbFactory.GetConnection(), query, queryFlags, parms);
rdbQueries.Add(rdb);
}
List<Dictionary<string, string>> finalList = new List<Dictionary<string, string>>();
int current = 0;
Dictionary<string, string> result = null;
while ((result = ConsumeNext(rdbQueries, queryFlags)) != null)
{
UUID regionUUID = new UUID(result["RegionUUID"]);
// When regionsToInclude.Count==0, it means do not filter by regions.
if ((regionsToInclude.Count == 0) || regionsToInclude.Contains(regionUUID))
{
// 0-based numbering
if ((current >= queryStart) && (current <= queryEnd))
finalList.Add(result);
current++;
}
}
return finalList;
}
public RDBConnectionQuery(ISimpleDB rdb, string query, uint queryFlags, Dictionary<string, object> parms)
{
m_rdb = rdb;
m_query = query;
m_parms = parms;
RefreshResults();
}
private void RefreshList(ISimpleDB rdb, string query, Dictionary<string, object> parms, List<Dictionary<string, string>> results)
{
results.AddRange(rdb.QueryWithResults(query, parms));
}
private void CacheRdbHosts(ISimpleDB db)
{
List<Dictionary<string, string>> hostNames = db.QueryWithResults("SELECT host_name FROM RdbHosts");
_rdbHostCache.Clear();
foreach (var hostNameDict in hostNames)
{
_rdbHostCache.Add(hostNameDict["host_name"]);
}
_rdbCacheTime = DateTime.Now;
}
private void UpdateRegistration(ISimpleDB db, UUID uuid, UUID regionUUID)
{
string sql = "INSERT INTO emailregistrations (`uuid`, `time`, `region`) VALUES (?uuid, ?time, ?region)";
sql += " ON DUPLICATE KEY UPDATE `time`=?time";
Dictionary<string, object> parameters = new Dictionary<string, object>();
parameters["?uuid"] = uuid.ToString();
parameters["?region"] = regionUUID.ToString();
parameters["?time"] = DateTime2UnixTime(DateTime.UtcNow); // All storage dates are UTC
try
{
db.QueryNoResults(sql, parameters);
}
catch (Exception e)
{
m_log.Error("[InboundEmail]: Exception during database call to store message: "+e.Message);
m_log.Error(e.StackTrace);
}
}
private uint QueryEmailCount(ISimpleDB db, UUID uuid)
{
try
{
string query
= "SELECT COUNT(*) FROM emailmessages WHERE uuid = ?uuid";
Dictionary<string, object> parms = new Dictionary<string, object>();
parms.Add("?uuid", uuid);
List<Dictionary<string, string>> results = db.QueryWithResults(query, parms);
if (results.Count != 1)
return 0;
return Convert.ToUInt32(results[0]["COUNT(*)"]);
}
catch (Exception e)
{
m_log.Error("[InboundEmail]: Exception during database call to count messages: " + e.Message);
m_log.Error(e.StackTrace);
return 0;
}
}
private void SetAgentGroupSelectedRole(ISimpleDB db, OpenMetaverse.UUID agentID, OpenMetaverse.UUID groupID, OpenMetaverse.UUID roleID)
{
string query
= "UPDATE osgroupmembership " +
"SET SelectedRoleID = ?roleID " +
"WHERE AgentID = ?agentID AND GroupID = ?groupID";
Dictionary<string, object> parms = new Dictionary<string, object>();
parms.Add("?roleID", roleID);
parms.Add("?agentID", agentID);
parms.Add("?groupID", groupID);
db.QueryNoResults(query, parms);
}
/// <summary>
/// Delete a folder from database. Must be called from within a using{} block for the database connection.
/// </summary>
/// Passing in the connection allows for consolidation of the DB connections, important as this method is often called from an inner loop.
/// <param name="folderID">the folder UUID</param>
/// <param name="conn">the database connection</param>
private void deleteOneFolder(ISimpleDB conn, InventoryFolderBase folder)
{
string query = "DELETE FROM inventoryfolders WHERE folderID=?uuid";
Dictionary<string, object> parms = new Dictionary<string, object>();
parms.Add("?uuid", folder.ID.ToString());
conn.QueryNoResults(query, parms);
// As the callers of this function will increment the version, there's no need to do so here.
}
private void DeleteMessage(ISimpleDB db, uint ID)
{
string sql = "DELETE FROM emailmessages WHERE `ID`=?id";
Dictionary<string, object> parameters = new Dictionary<string, object>();
parameters["?ID"] = ID.ToString();
try
{
db.QueryNoResults(sql, parameters);
}
catch (Exception e)
{
m_log.Error("[InboundEmail]: Exception during database call to delete delivered message: " + e.Message);
m_log.Error(e.StackTrace);
}
}
private static Dictionary<string, string> FindGroupPowersForAgent(ISimpleDB db, UUID groupID, UUID agentID)
{
string query = " SELECT BIT_OR(osrole.Powers) AS GroupPowers" +
" FROM osgrouprolemembership JOIN osrole ON (osgrouprolemembership.GroupID = osrole.GroupID AND osgrouprolemembership.RoleID = osrole.RoleID)" +
" WHERE osgrouprolemembership.GroupID = ?groupID AND osgrouprolemembership.AgentID = ?agentID";
Dictionary<string, object> parms = new Dictionary<string, object>();
parms.Add("?groupID", groupID);
parms.Add("?agentID", agentID);
List<Dictionary<string, string>> powersResults = db.QueryWithResults(query, parms);
if (powersResults.Count == 0)
{
m_log.Error("[GROUPS]: Could not find any matching powers for agent");
return null;
}
return powersResults[0];
}
private Email QueryNextEmail(ISimpleDB db, UUID uuid, string sender, string subject)
{
Email msg = new Email();
try
{
Dictionary<string, object> parms = new Dictionary<string, object>();
string query = "SELECT * FROM emailmessages WHERE uuid = ?uuid";
parms.Add("?uuid", uuid);
if (!String.IsNullOrEmpty(sender))
{
query += " AND from = ?from";
parms.Add("?from", sender);
}
if (!String.IsNullOrEmpty(subject))
{
query += " AND subject = ?subject";
parms.Add("?subject", subject);
}
query += " ORDER BY sent LIMIT 1";
List<Dictionary<string, string>> results = db.QueryWithResults(query, parms);
if (results.Count != 1)
return null;
uint ID = Convert.ToUInt32(results[0]["ID"]);
uint unixtime = Convert.ToUInt32(results[0]["sent"]);
msg.time = unixtime.ToString(); // Note: email event is documented as string form of *UTC* unix time
msg.sender = results[0]["from"];
msg.subject = results[0]["subject"];
msg.message = results[0]["body"];
// This one has been handled, remove it from those queued in the DB.
DeleteMessage(db, ID);
}
catch (Exception e)
{
m_log.Error("[InboundEmail]: Exception during database call to store message: " + e.Message);
m_log.Error(e.StackTrace);
return null;
}
msg.numLeft = (int)QueryEmailCount(db, uuid);
return msg;
}
private void DoInsertGroupMembership(ISimpleDB db, OpenMetaverse.UUID agentID, OpenMetaverse.UUID groupID, OpenMetaverse.UUID roleID)
{
string qInsMembership =
"INSERT INTO osgroupmembership (GroupID, AgentID, Contribution, ListInProfile, AcceptNotices, SelectedRoleID) " +
"VALUES (?groupID,?agentID, 0, 1, 1,?roleID)";
Dictionary<string, object> parms = new Dictionary<string, object>();
parms.Add("?agentID", agentID);
parms.Add("?groupID", groupID);
parms.Add("?roleID", roleID);
db.QueryNoResults(qInsMembership, parms);
}
