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