/// <summary>
/// Returns a list of regions within the specified ranges
/// </summary>
/// <param name="xmin">minimum X coordinate</param>
/// <param name="ymin">minimum Y coordinate</param>
/// <param name="xmax">maximum X coordinate</param>
/// <param name="ymax">maximum Y coordinate</param>
/// <returns>null</returns>
/// <remarks>always return null</remarks>
override public RegionProfileData[] GetProfilesInRange(uint xmin, uint ymin, uint xmax, uint ymax)
{
using (AutoClosingSqlCommand command = database.Query("SELECT * FROM regions WHERE locX >= @xmin AND locX <= @xmax AND locY >= @ymin AND locY <= @ymax"))
{
command.Parameters.Add(database.CreateParameter("xmin", xmin));
command.Parameters.Add(database.CreateParameter("ymin", ymin));
command.Parameters.Add(database.CreateParameter("xmax", xmax));
command.Parameters.Add(database.CreateParameter("ymax", ymax));
List <RegionProfileData> rows = new List <RegionProfileData>();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
rows.Add(ReadSimRow(reader));
}
}
if (rows.Count > 0)
{
return(rows.ToArray());
}
}
m_log.Info("[GRID DB] : Found no regions within range.");
return(null);
}
/// <summary>
/// Fetch Asset from m_database
/// </summary>
/// <param name="assetID">the asset UUID</param>
/// <returns></returns>
override protected AssetBase FetchStoredAsset(UUID assetID)
{
string sql = "SELECT * FROM assets WHERE id = @id";
using (AutoClosingSqlCommand command = m_database.Query(sql))
{
command.Parameters.Add(m_database.CreateParameter("id", assetID));
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
AssetBase asset = new AssetBase();
// Region Main
asset.FullID = new UUID((Guid)reader["id"]);
asset.Name = (string)reader["name"];
asset.Description = (string)reader["description"];
asset.Type = Convert.ToSByte(reader["assetType"]);
asset.Local = Convert.ToBoolean(reader["local"]);
asset.Temporary = Convert.ToBoolean(reader["temporary"]);
asset.Data = (byte[])reader["data"];
return(asset);
}
return(null); // throw new Exception("No rows to return");
}
}
}
/// <summary>
/// Returns a list of AssetMetadata objects. The list is a subset of
/// the entire data set offset by <paramref name="start" /> containing
/// <paramref name="count" /> elements.
/// </summary>
/// <param name="start">The number of results to discard from the total data set.</param>
/// <param name="count">The number of rows the returned list should contain.</param>
/// <returns>A list of AssetMetadata objects.</returns>
public override List <AssetMetadata> FetchAssetMetadataSet(int start, int count)
{
List <AssetMetadata> retList = new List <AssetMetadata>(count);
string sql = @"SELECT (name,description,assetType,temporary,id), Row = ROW_NUMBER()
OVER (ORDER BY (some column to order by))
WHERE Row >= @Start AND Row < @Start + @Count";
using (AutoClosingSqlCommand command = m_database.Query(sql))
{
command.Parameters.Add(m_database.CreateParameter("start", start));
command.Parameters.Add(m_database.CreateParameter("count", count));
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
AssetMetadata metadata = new AssetMetadata();
metadata.FullID = new UUID((Guid)reader["id"]);
metadata.Name = (string)reader["name"];
metadata.Description = (string)reader["description"];
metadata.Type = Convert.ToSByte(reader["assetType"]);
metadata.Temporary = Convert.ToBoolean(reader["temporary"]);
}
}
}
return(retList);
}
private void LoadBanList(EstateSettings es)
{
es.ClearBans();
string sql = "select bannedUUID from estateban where EstateID = @EstateID";
using (AutoClosingSqlCommand cmd = _Database.Query(sql))
{
SqlParameter idParameter = new SqlParameter("@EstateID", SqlDbType.Int);
idParameter.Value = es.EstateID;
cmd.Parameters.Add(idParameter);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
EstateBan eb = new EstateBan();
eb.BannedUserID = new UUID((Guid)reader["bannedUUID"]); //uuid;
eb.BannedHostAddress = "0.0.0.0";
eb.BannedHostIPMask = "0.0.0.0";
es.AddBan(eb);
}
}
}
}
/// <summary>
/// Gets the inventory folders.
/// </summary>
/// <param name="command">SQLcommand.</param>
/// <returns></returns>
private static List <InventoryFolderBase> getInventoryFolders(AutoClosingSqlCommand command)
{
using (IDataReader reader = command.ExecuteReader())
{
List <InventoryFolderBase> items = new List <InventoryFolderBase>();
while (reader.Read())
{
items.Add(readInventoryFolder(reader));
}
return(items);
}
}
/// <summary>
/// Returns a specified inventory folder
/// </summary>
/// <param name="folderID">The folder to return</param>
/// <returns>A folder class</returns>
public InventoryFolderBase getInventoryFolder(UUID folderID)
{
using (AutoClosingSqlCommand command = database.Query("SELECT * FROM inventoryfolders WHERE folderID = @folderID"))
{
command.Parameters.Add(database.CreateParameter("folderID", folderID));
using (IDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
return(readInventoryFolder(reader));
}
}
}
m_log.InfoFormat("[INVENTORY DB] : Found no inventory folder with ID : {0}", folderID);
return(null);
}
/// <summary>
/// Returns a sim profile from its UUID
/// </summary>
/// <param name="uuid">The region UUID</param>
/// <returns>The sim profile</returns>
override public RegionProfileData GetProfileByUUID(UUID uuid)
{
using (AutoClosingSqlCommand command = database.Query("SELECT * FROM " + m_regionsTableName + " WHERE uuid = @uuid"))
{
command.Parameters.Add(database.CreateParameter("uuid", uuid));
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
return(ReadSimRow(reader));
}
}
}
m_log.InfoFormat("[GRID DB] : No region found with UUID : {0}", uuid);
return(null);
}
/// <summary>
/// Returns a sim profile from its location
/// </summary>
/// <param name="handle">Region location handle</param>
/// <returns>Sim profile</returns>
override public RegionProfileData GetProfileByHandle(ulong handle)
{
using (AutoClosingSqlCommand command = database.Query("SELECT * FROM " + m_regionsTableName + " WHERE regionHandle = @handle"))
{
command.Parameters.Add(database.CreateParameter("handle", handle));
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
return(ReadSimRow(reader));
}
}
}
m_log.InfoFormat("[GRID DB] : No region found with handle : {0}", handle);
return(null);
}
/// <summary>
/// Returns all activated gesture-items in the inventory of the specified avatar.
/// </summary>
/// <param name="avatarID">The <see cref="UUID"/> of the avatar</param>
/// <returns>
/// The list of gestures (<see cref="InventoryItemBase"/>s)
/// </returns>
public List <InventoryItemBase> fetchActiveGestures(UUID avatarID)
{
using (AutoClosingSqlCommand command = database.Query("SELECT * FROM inventoryitems WHERE avatarId = @uuid AND assetType = @assetType and flags = 1"))
{
command.Parameters.Add(database.CreateParameter("uuid", avatarID));
command.Parameters.Add(database.CreateParameter("assetType", (int)AssetType.Gesture));
using (IDataReader reader = command.ExecuteReader())
{
List <InventoryItemBase> gestureList = new List <InventoryItemBase>();
while (reader.Read())
{
gestureList.Add(readInventoryItem(reader));
}
return(gestureList);
}
}
}
/// <summary>
/// Returns a specified inventory item
/// </summary>
/// <param name="itemID">The item ID</param>
/// <returns>An inventory item</returns>
public InventoryItemBase getInventoryItem(UUID itemID)
{
using (AutoClosingSqlCommand result = database.Query("SELECT * FROM inventoryitems WHERE inventoryID = @inventoryID"))
{
result.Parameters.Add(database.CreateParameter("inventoryID", itemID));
using (IDataReader reader = result.ExecuteReader())
{
if (reader.Read())
{
return(readInventoryItem(reader));
}
}
}
m_log.InfoFormat("[INVENTORY DB]: Found no inventory item with ID : {0}", itemID);
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)
{
using (AutoClosingSqlCommand command = database.Query("SELECT * FROM inventoryitems WHERE parentFolderID = @parentFolderID"))
{
command.Parameters.Add(database.CreateParameter("parentFolderID", folderID));
List <InventoryItemBase> items = new List <InventoryItemBase>();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
items.Add(readInventoryItem(reader));
}
}
return(items);
}
}
/// <summary>
/// Returns up to maxNum profiles of regions that have a name starting with namePrefix
/// </summary>
/// <param name="namePrefix">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)
{
using (AutoClosingSqlCommand command = database.Query("SELECT * FROM regions WHERE regionName LIKE @name"))
{
command.Parameters.Add(database.CreateParameter("name", namePrefix + "%"));
List <RegionProfileData> rows = new List <RegionProfileData>();
using (SqlDataReader reader = command.ExecuteReader())
{
while (rows.Count < maxNum && reader.Read())
{
rows.Add(ReadSimRow(reader));
}
}
return(rows);
}
}
private UUID[] LoadUUIDList(uint estateID, string table)
{
List <UUID> uuids = new List <UUID>();
string sql = string.Format("select uuid from {0} where EstateID = @EstateID", table);
using (AutoClosingSqlCommand cmd = _Database.Query(sql))
{
cmd.Parameters.Add(_Database.CreateParameter("@EstateID", estateID));
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
uuids.Add(new UUID((Guid)reader["uuid"])); //uuid);
}
}
}
return(uuids.ToArray());
}
/// <summary>
/// Returns a sim profile from it's Region name string
/// </summary>
/// <param name="regionName">The region name search query</param>
/// <returns>The sim profile</returns>
override public RegionProfileData GetProfileByString(string regionName)
{
if (regionName.Length > 2)
{
using (AutoClosingSqlCommand command = database.Query("SELECT top 1 * FROM " + m_regionsTableName + " WHERE regionName like @regionName order by regionName"))
{
command.Parameters.Add(database.CreateParameter("regionName", regionName + "%"));
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
return(ReadSimRow(reader));
}
}
}
m_log.InfoFormat("[GRID DB] : No region found with regionName : {0}", regionName);
return(null);
}
m_log.Error("[GRID DB]: Searched for a Region Name shorter then 3 characters");
return(null);
}
/// <summary>
/// Gets the inventory folders.
/// </summary>
/// <param name="command">SQLcommand.</param>
/// <returns></returns>
private static List<InventoryFolderBase> getInventoryFolders(AutoClosingSqlCommand command)
{
using (IDataReader reader = command.ExecuteReader())
{
List<InventoryFolderBase> items = new List<InventoryFolderBase>();
while (reader.Read())
{
items.Add(readInventoryFolder(reader));
}
return items;
}
}
/// <summary>
/// Loads the estate settings.
/// </summary>
/// <param name="regionID">region ID.</param>
/// <returns></returns>
public EstateSettings LoadEstateSettings(UUID regionID)
{
EstateSettings es = new EstateSettings();
string sql = "select estate_settings." + String.Join(",estate_settings.", FieldList) + " from estate_map left join estate_settings on estate_map.EstateID = estate_settings.EstateID where estate_settings.EstateID is not null and RegionID = @RegionID";
bool insertEstate = false;
using (AutoClosingSqlCommand cmd = _Database.Query(sql))
{
cmd.Parameters.Add(_Database.CreateParameter("@RegionID", regionID));
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
foreach (string name in FieldList)
{
if (_FieldMap[name].GetValue(es) is bool)
{
int v = Convert.ToInt32(reader[name]);
if (v != 0)
{
_FieldMap[name].SetValue(es, true);
}
else
{
_FieldMap[name].SetValue(es, false);
}
}
else if (_FieldMap[name].GetValue(es) is UUID)
{
_FieldMap[name].SetValue(es, new UUID((Guid)reader[name])); // uuid);
}
else
{
es.EstateID = Convert.ToUInt32(reader["EstateID"].ToString());
}
}
}
else
{
insertEstate = true;
}
}
}
if (insertEstate)
{
List <string> names = new List <string>(FieldList);
names.Remove("EstateID");
sql = string.Format("insert into estate_settings ({0}) values ( @{1})", String.Join(",", names.ToArray()), String.Join(", @", names.ToArray()));
//_Log.Debug("[DB ESTATE]: SQL: " + sql);
using (SqlConnection connection = _Database.DatabaseConnection())
{
using (SqlCommand insertCommand = connection.CreateCommand())
{
insertCommand.CommandText = sql + " SET @ID = SCOPE_IDENTITY()";
foreach (string name in names)
{
insertCommand.Parameters.Add(_Database.CreateParameter("@" + name, _FieldMap[name].GetValue(es)));
}
SqlParameter idParameter = new SqlParameter("@ID", SqlDbType.Int);
idParameter.Direction = ParameterDirection.Output;
insertCommand.Parameters.Add(idParameter);
insertCommand.ExecuteNonQuery();
es.EstateID = Convert.ToUInt32(idParameter.Value);
}
}
using (AutoClosingSqlCommand cmd = _Database.Query("INSERT INTO [estate_map] ([RegionID] ,[EstateID]) VALUES (@RegionID, @EstateID)"))
{
cmd.Parameters.Add(_Database.CreateParameter("@RegionID", regionID));
cmd.Parameters.Add(_Database.CreateParameter("@EstateID", es.EstateID));
// This will throw on dupe key
try
{
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
_Log.DebugFormat("[ESTATE DB]: Error inserting regionID and EstateID in estate_map: {0}", e);
}
}
// Munge and transfer the ban list
sql = string.Format("insert into estateban select {0}, bannedUUID, bannedIp, bannedIpHostMask, '' from regionban where regionban.regionUUID = @UUID", es.EstateID);
using (AutoClosingSqlCommand cmd = _Database.Query(sql))
{
cmd.Parameters.Add(_Database.CreateParameter("@UUID", regionID));
try
{
cmd.ExecuteNonQuery();
}
catch (Exception)
{
_Log.Debug("[ESTATE DB]: Error setting up estateban from regionban");
}
}
//TODO check if this is needed??
es.Save();
}
LoadBanList(es);
es.EstateManagers = LoadUUIDList(es.EstateID, "estate_managers");
es.EstateAccess = LoadUUIDList(es.EstateID, "estate_users");
es.EstateGroups = LoadUUIDList(es.EstateID, "estate_groups");
//Set event
es.OnSave += StoreEstateSettings;
return(es);
}
