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