/// <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); }