public AssetMetadata Get(string id, out string hash)
{
hash = String.Empty;
AssetMetadata meta = null;
UUID uuid = new UUID(id);
string query = String.Format("select \"id\", \"type\", \"hash\", \"create_time\", \"access_time\", \"asset_flags\" from {0} where \"id\" = :id", m_Table);
using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString))
using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon))
{
dbcon.Open();
cmd.Parameters.Add(m_database.CreateParameter("id", uuid));
using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default))
{
if (reader.Read())
{
meta = new AssetMetadata();
hash = reader["hash"].ToString();
meta.ID = id;
meta.FullID = uuid;
meta.Name = String.Empty;
meta.Description = String.Empty;
meta.Type = (sbyte)Convert.ToInt32(reader["type"]);
meta.ContentType = SLUtil.SLAssetTypeToContentType(meta.Type);
meta.CreationDate = Util.ToDateTime(Convert.ToInt32(reader["create_time"]));
meta.Flags = (AssetFlags)Convert.ToInt32(reader["asset_flags"]);
int atime = Convert.ToInt32(reader["access_time"]);
UpdateAccessTime(atime, uuid);
}
}
}
return(meta);
}
public virtual T[] Get(string field, string key)
{
using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
if (m_FieldTypes.ContainsKey(field))
{
cmd.Parameters.Add(m_database.CreateParameter(field, key, m_FieldTypes[field]));
}
else
{
cmd.Parameters.Add(m_database.CreateParameter(field, key));
}
string query = String.Format("SELECT * FROM {0} WHERE \"{1}\" = :{1}", m_Realm, field, field);
cmd.Connection = conn;
cmd.CommandText = query;
conn.Open();
return(DoQuery(cmd));
}
}
public virtual T[] Get(string[] fields, string[] keys)
{
if (fields.Length != keys.Length)
{
return(new T[0]);
}
List <string> terms = new List <string>();
using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
for (int i = 0; i < fields.Length; i++)
{
if (m_FieldTypes.ContainsKey(fields[i]))
{
cmd.Parameters.Add(m_database.CreateParameter(fields[i], keys[i], m_FieldTypes[fields[i]]));
}
else
{
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 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 (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
{
cmd.Parameters.Add(_Database.CreateParameter("RegionID", regionID));
conn.Open();
using (NpgsqlDataReader 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, v);
}
else if (f.FieldType == typeof(UUID))
{
UUID estUUID = UUID.Zero;
UUID.TryParse(v.ToString(), out estUUID);
f.SetValue(es, estUUID);
}
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);
}
public List <RegionData> Get(string regionName, UUID scopeID)
{
string sql = "select * from " + m_Realm + " where lower(\"regionName\") like lower(:regionName) ";
if (scopeID != UUID.Zero)
{
sql += " and \"ScopeID\" = :scopeID";
}
sql += " order by lower(\"regionName\")";
using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
{
cmd.Parameters.Add(m_database.CreateParameter("regionName", regionName));
if (scopeID != UUID.Zero)
{
cmd.Parameters.Add(m_database.CreateParameter("scopeID", scopeID));
}
conn.Open();
return(RunCommand(cmd));
}
}
/// <summary>
/// Fetch Asset <paramref name="assetID"/> from database
/// </summary>
/// <param name="assetID">Asset UUID to fetch</param>
/// <returns>Return the asset</returns>
/// <remarks>On failure : throw an exception and attempt to reconnect to database</remarks>
public AssetBase GetAsset(UUID assetID)
{
// m_log.DebugFormat("[PGSQL XASSET DATA]: Looking for asset {0}", assetID);
AssetBase asset = null;
lock (m_dbLock)
{
using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString))
{
dbcon.Open();
using (NpgsqlCommand cmd = new NpgsqlCommand(
@"SELECT name, description, access_time, ""AssetType"", local, temporary, asset_flags, creatorid, data
FROM XAssetsMeta
JOIN XAssetsData ON XAssetsMeta.hash = XAssetsData.Hash WHERE id=:ID",
dbcon))
{
cmd.Parameters.Add(m_database.CreateParameter("ID", assetID));
try
{
using (NpgsqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
if (dbReader.Read())
{
asset = new AssetBase(
assetID,
(string)dbReader["name"],
Convert.ToSByte(dbReader["AssetType"]),
dbReader["creatorid"].ToString());
asset.Data = (byte[])dbReader["data"];
asset.Description = (string)dbReader["description"];
string local = dbReader["local"].ToString();
if (local.Equals("1") || local.Equals("true", StringComparison.InvariantCultureIgnoreCase))
{
asset.Local = true;
}
else
{
asset.Local = false;
}
asset.Temporary = Convert.ToBoolean(dbReader["temporary"]);
asset.Flags = (AssetFlags)Convert.ToInt32(dbReader["asset_flags"]);
if (m_enableCompression)
{
using (GZipStream decompressionStream = new GZipStream(new MemoryStream(asset.Data), CompressionMode.Decompress))
{
MemoryStream outputStream = new MemoryStream();
WebUtil.CopyStream(decompressionStream, outputStream, int.MaxValue);
// int compressedLength = asset.Data.Length;
asset.Data = outputStream.ToArray();
// m_log.DebugFormat(
// "[XASSET DB]: Decompressed {0} {1} to {2} bytes from {3}",
// asset.ID, asset.Name, asset.Data.Length, compressedLength);
}
}
UpdateAccessTime(asset.Metadata, (int)dbReader["access_time"]);
}
}
}
catch (Exception e)
{
m_log.Error(string.Format("[PGSQL XASSET DATA]: Failure fetching asset {0}", assetID), e);
}
}
}
}
return(asset);
}
/// <summary>
/// Gets the classified records.
/// </summary>
/// <returns>
/// Array of classified records
/// </returns>
/// <param name='creatorId'>
/// Creator identifier.
/// </param>
public OSDArray GetClassifiedRecords(UUID creatorId)
{
OSDArray data = new OSDArray();
using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString))
{
string query = @"SELECT classifieduuid, name FROM classifieds WHERE creatoruuid = :Id";
dbcon.Open();
using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon))
{
cmd.Parameters.Add(m_database.CreateParameter("Id", creatorId));
using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default))
{
if (reader.HasRows)
{
while (reader.Read())
{
OSDMap n = new OSDMap();
UUID Id = UUID.Zero;
string Name = null;
try
{
Id = DBGuid.FromDB(reader["classifieduuid"]);
Name = Convert.ToString(reader["name"]);
}
catch (Exception e)
{
m_log.Error("[PROFILES_DATA]: UserAccount exception ", e);
}
n.Add("classifieduuid", OSD.FromUUID(Id));
n.Add("name", OSD.FromString(Name));
data.Add(n);
}
}
}
}
}
return(data);
}
/// <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 (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
{
cmd.Parameters.Add(database.CreateParameter("folderID", folderID));
conn.Open();
using (NpgsqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
return(readInventoryFolder(reader));
}
}
}
m_log.InfoFormat("[INVENTORY DB] : Found no inventory folder with ID : {0}", folderID);
return(null);
}
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 (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
{
cmd.Parameters.Add(m_database.CreateParameter("principalID", principalID));
conn.Open();
using (NpgsqlDataReader 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" || s == "uuid")
{
continue;
}
ret.Data[s] = result[s].ToString();
}
return(ret);
}
}
}
return(null);
}
/// <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 (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
{
cmd.Parameters.Add(m_database.CreateParameter("id", assetID));
conn.Open();
using (NpgsqlDataReader 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");
}
}
}
