public virtual T[] Get(string[] fields, string[] keys) { if (fields.Length != keys.Length) { return(new T[0]); } List <string> terms = new List <string>(); using (SqlConnection conn = new SqlConnection(m_ConnectionString)) using (SqlCommand cmd = new SqlCommand()) { for (int i = 0; i < fields.Length; i++) { cmd.Parameters.Add(m_database.CreateParameter(fields[i], keys[i])); terms.Add("[" + fields[i] + "] = @" + fields[i]); } string where = String.Join(" AND ", terms.ToArray()); string query = String.Format("SELECT * FROM {0} WHERE {1}", m_Realm, where); cmd.Connection = conn; cmd.CommandText = query; conn.Open(); return(DoQuery(cmd)); } }
/// <summary> /// Loads the terrain map. /// </summary> /// <param name = "regionID">regionID.</param> /// <returns></returns> public short[] LoadTerrain(IScene scene, bool Revert, int RegionSizeX, int RegionSizeY) { const string sql = "select top 1 RegionUUID, Revision, Heightfield from terrain where RegionUUID = @RegionUUID and Revert = @Revert order by Revision desc"; using (SqlConnection conn = new SqlConnection(m_connectionString)) using (SqlCommand cmd = new SqlCommand(sql, conn)) { // MySqlParameter param = new MySqlParameter(); cmd.Parameters.Add(_Database.CreateParameter("@RegionUUID", scene.RegionInfo.RegionID)); cmd.Parameters.Add(_Database.CreateParameter("@Revert", Revert)); conn.Open(); using (SqlDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { return((short[])reader["Heightfield"]); } else { return(null); } //_Log.Info("[REGION DB]: Loaded terrain revision r" + rev); } } }
/// <summary> /// Saves a log item to the database /// </summary> /// <param name="serverDaemon">The daemon triggering the event</param> /// <param name="target">The target of the action (region / agent UUID, etc)</param> /// <param name="methodCall">The method call where the problem occured</param> /// <param name="arguments">The arguments passed to the method</param> /// <param name="priority">How critical is this?</param> /// <param name="logMessage">The message to log</param> public void saveLog(string serverDaemon, string target, string methodCall, string arguments, int priority, string logMessage) { string sql = "INSERT INTO logs ([target], [server], [method], [arguments], [priority], [message]) VALUES "; sql += "(@target, @server, @method, @arguments, @priority, @message);"; using (AutoClosingSqlCommand command = database.Query(sql)) { command.Parameters.Add(database.CreateParameter("server", serverDaemon)); command.Parameters.Add(database.CreateParameter("target", target)); command.Parameters.Add(database.CreateParameter("method", methodCall)); command.Parameters.Add(database.CreateParameter("arguments", arguments)); command.Parameters.Add(database.CreateParameter("priority", priority.ToString())); command.Parameters.Add(database.CreateParameter("message", logMessage)); try { command.ExecuteNonQuery(); } catch (Exception e) { //Are we not in a loop here m_log.Error("[LOG DB] Error logging : " + e.Message); } } }
public List <RegionData> Get(string regionName, UUID scopeID) { string sql = "select * from [" + m_Realm + "] where regionName like @regionName"; if (scopeID != UUID.Zero) { sql += " and ScopeID = @scopeID"; } using (SqlConnection conn = new SqlConnection(m_ConnectionString)) using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.Parameters.Add(m_database.CreateParameter("@regionName", regionName)); cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID)); conn.Open(); return(RunCommand(cmd)); } }
/// <summary> /// Loads the estate settings. /// </summary> /// <param name="regionID">region ID.</param> /// <returns></returns> public EstateSettings LoadEstateSettings(UUID regionID, bool create) { 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 (SqlConnection conn = new SqlConnection(m_connectionString)) using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.Parameters.Add(_Database.CreateParameter("@RegionID", regionID)); conn.Open(); using (SqlDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { foreach (string name in FieldList) { FieldInfo f = _FieldMap[name]; object v = reader[name]; if (f.FieldType == typeof(bool)) { f.SetValue(es, Convert.ToInt32(v) != 0); } else if (f.FieldType == typeof(UUID)) { f.SetValue(es, new UUID((Guid)v)); // uuid); } else if (f.FieldType == typeof(string)) { f.SetValue(es, v.ToString()); } else if (f.FieldType == typeof(UInt32)) { f.SetValue(es, Convert.ToUInt32(v)); } else if (f.FieldType == typeof(Single)) { f.SetValue(es, Convert.ToSingle(v)); } else { f.SetValue(es, v); } } } else { insertEstate = true; } } } if (insertEstate && create) { 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 conn = new SqlConnection(m_connectionString)) using (SqlCommand insertCommand = new SqlCommand(sql, conn)) { 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); conn.Open(); insertCommand.ExecuteNonQuery(); es.EstateID = Convert.ToUInt32(idParameter.Value); } sql = "INSERT INTO [estate_map] ([RegionID] ,[EstateID]) VALUES (@RegionID, @EstateID)"; using (SqlConnection conn = new SqlConnection(m_connectionString)) using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.Parameters.Add(_Database.CreateParameter("@RegionID", regionID)); cmd.Parameters.Add(_Database.CreateParameter("@EstateID", es.EstateID)); // This will throw on dupe key try { conn.Open(); cmd.ExecuteNonQuery(); } catch (Exception e) { _Log.DebugFormat("[ESTATE DB]: Error inserting regionID and EstateID in estate_map: {0}", e); } } //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); }
public AuthenticationData Get(UUID principalID) { AuthenticationData ret = new AuthenticationData(); ret.Data = new Dictionary <string, object>(); string sql = string.Format("select * from {0} where UUID = @principalID", m_Realm); using (SqlConnection conn = new SqlConnection(m_ConnectionString)) using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.Parameters.Add(m_database.CreateParameter("@principalID", principalID)); conn.Open(); using (SqlDataReader result = cmd.ExecuteReader()) { if (result.Read()) { ret.PrincipalID = principalID; if (m_ColumnNames == null) { m_ColumnNames = new List <string>(); DataTable schemaTable = result.GetSchemaTable(); foreach (DataRow row in schemaTable.Rows) { m_ColumnNames.Add(row["ColumnName"].ToString()); } } foreach (string s in m_ColumnNames) { if (s == "UUID") { continue; } ret.Data[s] = result[s].ToString(); } return(ret); } } } return(null); }
/// <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) { string sql = "SELECT * FROM inventoryfolders WHERE folderID = @folderID"; using (SqlConnection conn = new SqlConnection(m_connectionString)) using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.Parameters.Add(database.CreateParameter("folderID", folderID)); conn.Open(); using (SqlDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { return(readInventoryFolder(reader)); } } } m_log.InfoFormat("[INVENTORY DB] : Found no inventory folder with ID : {0}", folderID); return(null); }
/// <summary> /// Loads the estate settings. /// </summary> /// <param name="regionID">region ID.</param> /// <returns></returns> public EstateSettings LoadEstateSettings(UUID regionID, bool create) { 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 (SqlConnection conn = new SqlConnection(m_connectionString)) using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.Parameters.Add(_Database.CreateParameter("@RegionID", regionID)); conn.Open(); using (SqlDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { foreach (string name in FieldList) { FieldInfo f = _FieldMap[name]; object v = reader[name]; if (f.FieldType == typeof(bool)) { f.SetValue(es, Convert.ToInt32(v) != 0); } else if (f.FieldType == typeof(UUID)) { f.SetValue(es, new UUID((Guid)v)); // uuid); } else if (f.FieldType == typeof(string)) { f.SetValue(es, v.ToString()); } else if (f.FieldType == typeof(UInt32)) { f.SetValue(es, Convert.ToUInt32(v)); } else if (f.FieldType == typeof(Single)) { f.SetValue(es, Convert.ToSingle(v)); } else { f.SetValue(es, v); } } } else { insertEstate = true; } } } if (insertEstate && create) { DoCreate(es); LinkRegion(regionID, (int)es.EstateID); } 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); }
/// <summary> /// Fetch Asset from m_database /// </summary> /// <param name="assetID">the asset UUID</param> /// <returns></returns> override public AssetBase GetAsset(UUID assetID) { string sql = "SELECT * FROM assets WHERE id = @id"; using (SqlConnection conn = new SqlConnection(m_connectionString)) using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.Parameters.Add(m_database.CreateParameter("id", assetID)); conn.Open(); using (SqlDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { AssetBase asset = new AssetBase( DBGuid.FromDB(reader["id"]), (string)reader["name"], Convert.ToSByte(reader["assetType"]), reader["creatorid"].ToString() ); // Region Main asset.Description = (string)reader["description"]; asset.Local = Convert.ToBoolean(reader["local"]); asset.Temporary = Convert.ToBoolean(reader["temporary"]); asset.Flags = (AssetFlags)(Convert.ToInt32(reader["asset_flags"])); asset.Data = (byte[])reader["data"]; return(asset); } return(null); // throw new Exception("No rows to return"); } } }
/// <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> /// 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); }
/// <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); }