/// <summary>
/// Create asset in m_database
/// </summary>
/// <param name="asset">the asset</param>
override public void CreateAsset(AssetBase asset)
{
if (ExistsAsset(asset.FullID))
{
return;
}
string sql = @"INSERT INTO assets
([id], [name], [description], [assetType], [local],
[temporary], [create_time], [access_time], [data])
VALUES
(@id, @name, @description, @assetType, @local,
@temporary, @create_time, @access_time, @data)";
using (AutoClosingSqlCommand command = m_database.Query(sql))
{
int now = (int)((System.DateTime.Now.Ticks - m_ticksToEpoch) / 10000000);
command.Parameters.Add(m_database.CreateParameter("id", asset.FullID));
command.Parameters.Add(m_database.CreateParameter("name", asset.Name));
command.Parameters.Add(m_database.CreateParameter("description", asset.Description));
command.Parameters.Add(m_database.CreateParameter("assetType", asset.Type));
command.Parameters.Add(m_database.CreateParameter("local", asset.Local));
command.Parameters.Add(m_database.CreateParameter("temporary", asset.Temporary));
command.Parameters.Add(m_database.CreateParameter("access_time", now));
command.Parameters.Add(m_database.CreateParameter("create_time", now));
command.Parameters.Add(m_database.CreateParameter("data", asset.Data));
command.ExecuteNonQuery();
}
}
/// <summary>
/// Update asset in m_database
/// </summary>
/// <param name="asset">the asset</param>
override public void UpdateAsset(AssetBase asset)
{
string sql = @"UPDATE assets set id = @id, name = @name, description = @description, assetType = @assetType,
local = @local, temporary = @temporary, data = @data
WHERE id = @keyId;";
using (AutoClosingSqlCommand command = m_database.Query(sql))
{
command.Parameters.Add(m_database.CreateParameter("id", asset.FullID));
command.Parameters.Add(m_database.CreateParameter("name", asset.Name));
command.Parameters.Add(m_database.CreateParameter("description", asset.Description));
command.Parameters.Add(m_database.CreateParameter("assetType", asset.Type));
command.Parameters.Add(m_database.CreateParameter("local", asset.Local));
command.Parameters.Add(m_database.CreateParameter("temporary", asset.Temporary));
command.Parameters.Add(m_database.CreateParameter("data", asset.Data));
command.Parameters.Add(m_database.CreateParameter("@keyId", asset.FullID));
try
{
command.ExecuteNonQuery();
}
catch (Exception e)
{
m_log.Error(e.ToString());
}
}
}
private void SaveUUIDList(uint estateID, string table, UUID[] data)
{
//Delete first
string sql = string.Format("delete from {0} where EstateID = @EstateID", table);
using (AutoClosingSqlCommand cmd = _Database.Query(sql))
{
cmd.Parameters.Add(_Database.CreateParameter("@EstateID", estateID));
cmd.ExecuteNonQuery();
}
sql = string.Format("insert into {0} (EstateID, uuid) values ( @EstateID, @uuid )", table);
using (AutoClosingSqlCommand cmd = _Database.Query(sql))
{
cmd.Parameters.Add(_Database.CreateParameter("@EstateID", estateID));
bool createParamOnce = true;
foreach (UUID uuid in data)
{
if (createParamOnce)
{
cmd.Parameters.Add(_Database.CreateParameter("@uuid", uuid));
createParamOnce = false;
}
else
{
cmd.Parameters["@uuid"].Value = uuid.Guid; //.ToString(); //TODO check if this works
}
cmd.ExecuteNonQuery();
}
}
}
/// <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);
}
}
}
/// <summary>
/// Stores the estate settings.
/// </summary>
/// <param name="es">estate settings</param>
public void StoreEstateSettings(EstateSettings es)
{
List <string> names = new List <string>(FieldList);
names.Remove("EstateID");
string sql = string.Format("UPDATE estate_settings SET ");
foreach (string name in names)
{
sql += name + " = @" + name + ", ";
}
sql = sql.Remove(sql.LastIndexOf(","));
sql += " WHERE EstateID = @EstateID";
using (AutoClosingSqlCommand cmd = _Database.Query(sql))
{
foreach (string name in names)
{
cmd.Parameters.Add(_Database.CreateParameter("@" + name, _FieldMap[name].GetValue(es)));
}
cmd.Parameters.Add(_Database.CreateParameter("@EstateID", es.EstateID));
cmd.ExecuteNonQuery();
}
SaveBanList(es);
SaveUUIDList(es.EstateID, "estate_managers", es.EstateManagers);
SaveUUIDList(es.EstateID, "estate_users", es.EstateAccess);
SaveUUIDList(es.EstateID, "estate_groups", es.EstateGroups);
}
/// <summary>
/// Updates an inventory folder
/// </summary>
/// <param name="folder">Folder to update</param>
public void updateInventoryFolder(InventoryFolderBase folder)
{
string sql = @"UPDATE inventoryfolders SET folderID = @folderID,
agentID = @agentID,
parentFolderID = @parentFolderID,
folderName = @folderName,
type = @type,
version = @version
WHERE folderID = @keyFolderID";
using (AutoClosingSqlCommand command = database.Query(sql))
{
command.Parameters.Add(database.CreateParameter("folderID", folder.ID));
command.Parameters.Add(database.CreateParameter("agentID", folder.Owner));
command.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID));
command.Parameters.Add(database.CreateParameter("folderName", folder.Name));
command.Parameters.Add(database.CreateParameter("type", folder.Type));
command.Parameters.Add(database.CreateParameter("version", folder.Version));
command.Parameters.Add(database.CreateParameter("@keyFolderID", folder.ID));
try
{
command.ExecuteNonQuery();
}
catch (Exception e)
{
m_log.ErrorFormat("[INVENTORY DB]: Error : {0}", e.Message);
}
}
}
/// <summary>
/// Creates a new inventory folder
/// </summary>
/// <param name="folder">Folder to create</param>
public void addInventoryFolder(InventoryFolderBase folder)
{
string sql = @"INSERT INTO inventoryfolders ([folderID], [agentID], [parentFolderID], [folderName], [type], [version])
VALUES (@folderID, @agentID, @parentFolderID, @folderName, @type, @version);";
using (AutoClosingSqlCommand command = database.Query(sql))
{
command.Parameters.Add(database.CreateParameter("folderID", folder.ID));
command.Parameters.Add(database.CreateParameter("agentID", folder.Owner));
command.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID));
command.Parameters.Add(database.CreateParameter("folderName", folder.Name));
command.Parameters.Add(database.CreateParameter("type", folder.Type));
command.Parameters.Add(database.CreateParameter("version", folder.Version));
try
{
//IDbCommand result = database.Query(sql, param);
command.ExecuteNonQuery();
}
catch (Exception e)
{
m_log.ErrorFormat("[INVENTORY DB]: Error : {0}", e.Message);
}
}
}
/// <summary>
/// Updates the specified inventory item
/// </summary>
/// <param name="item">Inventory item to update</param>
public void updateInventoryItem(InventoryItemBase item)
{
string sql = @"UPDATE inventoryitems SET inventoryID = @inventoryID,
assetID = @assetID,
assetType = @assetType,
parentFolderID = @parentFolderID,
avatarID = @avatarID,
inventoryName = @inventoryName,
inventoryDescription = @inventoryDescription,
inventoryNextPermissions = @inventoryNextPermissions,
inventoryCurrentPermissions = @inventoryCurrentPermissions,
invType = @invType,
creatorID = @creatorID,
inventoryBasePermissions = @inventoryBasePermissions,
inventoryEveryOnePermissions = @inventoryEveryOnePermissions,
salePrice = @salePrice,
saleType = @saleType,
creationDate = @creationDate,
groupID = @groupID,
groupOwned = @groupOwned,
flags = @flags
WHERE inventoryID = @keyInventoryID";
using (AutoClosingSqlCommand command = database.Query(sql))
{
command.Parameters.Add(database.CreateParameter("inventoryID", item.ID));
command.Parameters.Add(database.CreateParameter("assetID", item.AssetID));
command.Parameters.Add(database.CreateParameter("assetType", item.AssetType));
command.Parameters.Add(database.CreateParameter("parentFolderID", item.Folder));
command.Parameters.Add(database.CreateParameter("avatarID", item.Owner));
command.Parameters.Add(database.CreateParameter("inventoryName", item.Name));
command.Parameters.Add(database.CreateParameter("inventoryDescription", item.Description));
command.Parameters.Add(database.CreateParameter("inventoryNextPermissions", item.NextPermissions));
command.Parameters.Add(database.CreateParameter("inventoryCurrentPermissions", item.CurrentPermissions));
command.Parameters.Add(database.CreateParameter("invType", item.InvType));
command.Parameters.Add(database.CreateParameter("creatorID", item.CreatorIdAsUuid));
command.Parameters.Add(database.CreateParameter("inventoryBasePermissions", item.BasePermissions));
command.Parameters.Add(database.CreateParameter("inventoryEveryOnePermissions", item.EveryOnePermissions));
command.Parameters.Add(database.CreateParameter("salePrice", item.SalePrice));
command.Parameters.Add(database.CreateParameter("saleType", item.SaleType));
command.Parameters.Add(database.CreateParameter("creationDate", item.CreationDate));
command.Parameters.Add(database.CreateParameter("groupID", item.GroupID));
command.Parameters.Add(database.CreateParameter("groupOwned", item.GroupOwned));
command.Parameters.Add(database.CreateParameter("flags", item.Flags));
command.Parameters.Add(database.CreateParameter("@keyInventoryID", item.ID));
try
{
command.ExecuteNonQuery();
}
catch (Exception e)
{
m_log.Error("[INVENTORY DB]: Error updating item :" + e.Message);
}
}
}
// See IInventoryDataPlugin
/// <summary>
/// Delete an item in inventory database
/// </summary>
/// <param name="itemID">the item UUID</param>
public void deleteInventoryItem(UUID itemID)
{
using (AutoClosingSqlCommand command = database.Query("DELETE FROM inventoryitems WHERE inventoryID=@inventoryID"))
{
command.Parameters.Add(database.CreateParameter("inventoryID", itemID));
try
{
command.ExecuteNonQuery();
}
catch (Exception e)
{
m_log.Error("[INVENTORY DB]: Error deleting item :" + e.Message);
}
}
}
/// <summary>
/// Deletes a sim profile from the database
/// </summary>
/// <param name="uuid">the sim UUID</param>
/// <returns>Successful?</returns>
//public DataResponse DeleteProfile(RegionProfileData profile)
override public DataResponse DeleteProfile(string uuid)
{
using (AutoClosingSqlCommand command = database.Query("DELETE FROM regions WHERE uuid = @uuid;"))
{
command.Parameters.Add(database.CreateParameter("uuid", uuid));
try
{
command.ExecuteNonQuery();
return(DataResponse.RESPONSE_OK);
}
catch (Exception e)
{
m_log.DebugFormat("[GRID DB] : Error deleting region info, error is : {0}", e.Message);
return(DataResponse.RESPONSE_ERROR);
}
}
}
private void SaveBanList(EstateSettings es)
{
//Delete first
string sql = "delete from estateban where EstateID = @EstateID";
using (AutoClosingSqlCommand cmd = _Database.Query(sql))
{
cmd.Parameters.Add(_Database.CreateParameter("@EstateID", es.EstateID));
cmd.ExecuteNonQuery();
}
//Insert after
sql = "insert into estateban (EstateID, bannedUUID) values ( @EstateID, @bannedUUID )";
using (AutoClosingSqlCommand cmd = _Database.Query(sql))
{
foreach (EstateBan b in es.EstateBans)
{
cmd.Parameters.Add(_Database.CreateParameter("@EstateID", es.EstateID));
cmd.Parameters.Add(_Database.CreateParameter("@bannedUUID", b.BannedUserID));
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
}
/// <summary>
/// Creates a new region in the database
/// </summary>
/// <param name="profile">The region profile to insert</param>
/// <returns>Successful?</returns>
private bool InsertRegionRow(RegionProfileData profile)
{
bool returnval = false;
//Insert new region
string sql =
"INSERT INTO " + m_regionsTableName + @" ([regionHandle], [regionName], [uuid], [regionRecvKey], [regionSecret], [regionSendKey], [regionDataURI],
[serverIP], [serverPort], [serverURI], [locX], [locY], [locZ], [eastOverrideHandle], [westOverrideHandle],
[southOverrideHandle], [northOverrideHandle], [regionAssetURI], [regionAssetRecvKey], [regionAssetSendKey],
[regionUserURI], [regionUserRecvKey], [regionUserSendKey], [regionMapTexture], [serverHttpPort],
[serverRemotingPort], [owner_uuid], [originUUID])
VALUES (@regionHandle, @regionName, @uuid, @regionRecvKey, @regionSecret, @regionSendKey, @regionDataURI,
@serverIP, @serverPort, @serverURI, @locX, @locY, @locZ, @eastOverrideHandle, @westOverrideHandle,
@southOverrideHandle, @northOverrideHandle, @regionAssetURI, @regionAssetRecvKey, @regionAssetSendKey,
@regionUserURI, @regionUserRecvKey, @regionUserSendKey, @regionMapTexture, @serverHttpPort, @serverRemotingPort, @owner_uuid, @originUUID);";
using (AutoClosingSqlCommand command = database.Query(sql))
{
command.Parameters.Add(database.CreateParameter("regionHandle", profile.regionHandle));
command.Parameters.Add(database.CreateParameter("regionName", profile.regionName));
command.Parameters.Add(database.CreateParameter("uuid", profile.UUID));
command.Parameters.Add(database.CreateParameter("regionRecvKey", profile.regionRecvKey));
command.Parameters.Add(database.CreateParameter("regionSecret", profile.regionSecret));
command.Parameters.Add(database.CreateParameter("regionSendKey", profile.regionSendKey));
command.Parameters.Add(database.CreateParameter("regionDataURI", profile.regionDataURI));
command.Parameters.Add(database.CreateParameter("serverIP", profile.serverIP));
command.Parameters.Add(database.CreateParameter("serverPort", profile.serverPort));
command.Parameters.Add(database.CreateParameter("serverURI", profile.serverURI));
command.Parameters.Add(database.CreateParameter("locX", profile.regionLocX));
command.Parameters.Add(database.CreateParameter("locY", profile.regionLocY));
command.Parameters.Add(database.CreateParameter("locZ", profile.regionLocZ));
command.Parameters.Add(database.CreateParameter("eastOverrideHandle", profile.regionEastOverrideHandle));
command.Parameters.Add(database.CreateParameter("westOverrideHandle", profile.regionWestOverrideHandle));
command.Parameters.Add(database.CreateParameter("northOverrideHandle", profile.regionNorthOverrideHandle));
command.Parameters.Add(database.CreateParameter("southOverrideHandle", profile.regionSouthOverrideHandle));
command.Parameters.Add(database.CreateParameter("regionAssetURI", profile.regionAssetURI));
command.Parameters.Add(database.CreateParameter("regionAssetRecvKey", profile.regionAssetRecvKey));
command.Parameters.Add(database.CreateParameter("regionAssetSendKey", profile.regionAssetSendKey));
command.Parameters.Add(database.CreateParameter("regionUserURI", profile.regionUserURI));
command.Parameters.Add(database.CreateParameter("regionUserRecvKey", profile.regionUserRecvKey));
command.Parameters.Add(database.CreateParameter("regionUserSendKey", profile.regionUserSendKey));
command.Parameters.Add(database.CreateParameter("regionMapTexture", profile.regionMapTextureID));
command.Parameters.Add(database.CreateParameter("serverHttpPort", profile.httpPort));
command.Parameters.Add(database.CreateParameter("serverRemotingPort", profile.remotingPort));
command.Parameters.Add(database.CreateParameter("owner_uuid", profile.owner_uuid));
command.Parameters.Add(database.CreateParameter("originUUID", profile.originUUID));
try
{
command.ExecuteNonQuery();
returnval = true;
}
catch (Exception e)
{
m_log.Error("[GRID DB] : Error inserting region, error: " + e.Message);
}
}
return(returnval);
}
/// <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>
/// Adds a specified item to the database
/// </summary>
/// <param name="item">The inventory item</param>
public void addInventoryItem(InventoryItemBase item)
{
if (getInventoryItem(item.ID) != null)
{
updateInventoryItem(item);
return;
}
string sql = @"INSERT INTO inventoryitems
([inventoryID], [assetID], [assetType], [parentFolderID], [avatarID], [inventoryName],
[inventoryDescription], [inventoryNextPermissions], [inventoryCurrentPermissions],
[invType], [creatorID], [inventoryBasePermissions], [inventoryEveryOnePermissions], [inventoryGroupPermissions],
[salePrice], [saleType], [creationDate], [groupID], [groupOwned], [flags])
VALUES
(@inventoryID, @assetID, @assetType, @parentFolderID, @avatarID, @inventoryName, @inventoryDescription,
@inventoryNextPermissions, @inventoryCurrentPermissions, @invType, @creatorID,
@inventoryBasePermissions, @inventoryEveryOnePermissions, @inventoryGroupPermissions, @salePrice, @saleType,
@creationDate, @groupID, @groupOwned, @flags)";
using (AutoClosingSqlCommand command = database.Query(sql))
{
command.Parameters.Add(database.CreateParameter("inventoryID", item.ID));
command.Parameters.Add(database.CreateParameter("assetID", item.AssetID));
command.Parameters.Add(database.CreateParameter("assetType", item.AssetType));
command.Parameters.Add(database.CreateParameter("parentFolderID", item.Folder));
command.Parameters.Add(database.CreateParameter("avatarID", item.Owner));
command.Parameters.Add(database.CreateParameter("inventoryName", item.Name));
command.Parameters.Add(database.CreateParameter("inventoryDescription", item.Description));
command.Parameters.Add(database.CreateParameter("inventoryNextPermissions", item.NextPermissions));
command.Parameters.Add(database.CreateParameter("inventoryCurrentPermissions", item.CurrentPermissions));
command.Parameters.Add(database.CreateParameter("invType", item.InvType));
command.Parameters.Add(database.CreateParameter("creatorID", item.CreatorId));
command.Parameters.Add(database.CreateParameter("inventoryBasePermissions", item.BasePermissions));
command.Parameters.Add(database.CreateParameter("inventoryEveryOnePermissions", item.EveryOnePermissions));
command.Parameters.Add(database.CreateParameter("inventoryGroupPermissions", item.GroupPermissions));
command.Parameters.Add(database.CreateParameter("salePrice", item.SalePrice));
command.Parameters.Add(database.CreateParameter("saleType", item.SaleType));
command.Parameters.Add(database.CreateParameter("creationDate", item.CreationDate));
command.Parameters.Add(database.CreateParameter("groupID", item.GroupID));
command.Parameters.Add(database.CreateParameter("groupOwned", item.GroupOwned));
command.Parameters.Add(database.CreateParameter("flags", item.Flags));
try
{
command.ExecuteNonQuery();
}
catch (Exception e)
{
m_log.Error("[INVENTORY DB]: Error inserting item :" + e.Message);
}
}
sql = "UPDATE inventoryfolders SET version = version + 1 WHERE folderID = @folderID";
using (AutoClosingSqlCommand command = database.Query(sql))
{
command.Parameters.Add(database.CreateParameter("folderID", item.Folder.ToString()));
try
{
command.ExecuteNonQuery();
}
catch (Exception e)
{
m_log.Error("[INVENTORY DB] Error updating inventory folder for new item :" + e.Message);
}
}
}
