aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/PGSQL/PGSQLXAssetData.cs
diff options
context:
space:
mode:
Diffstat (limited to 'OpenSim/Data/PGSQL/PGSQLXAssetData.cs')
-rw-r--r--OpenSim/Data/PGSQL/PGSQLXAssetData.cs152
1 files changed, 102 insertions, 50 deletions
diff --git a/OpenSim/Data/PGSQL/PGSQLXAssetData.cs b/OpenSim/Data/PGSQL/PGSQLXAssetData.cs
index e959619..4f682f0 100644
--- a/OpenSim/Data/PGSQL/PGSQLXAssetData.cs
+++ b/OpenSim/Data/PGSQL/PGSQLXAssetData.cs
@@ -56,6 +56,7 @@ namespace OpenSim.Data.PGSQL
56 private const int DaysBetweenAccessTimeUpdates = 30; 56 private const int DaysBetweenAccessTimeUpdates = 30;
57 57
58 private bool m_enableCompression = false; 58 private bool m_enableCompression = false;
59 private PGSQLManager m_database;
59 private string m_connectionString; 60 private string m_connectionString;
60 private object m_dbLock = new object(); 61 private object m_dbLock = new object();
61 62
@@ -92,6 +93,7 @@ namespace OpenSim.Data.PGSQL
92 m_log.ErrorFormat("[PGSQL XASSETDATA]: ***********************************************************"); 93 m_log.ErrorFormat("[PGSQL XASSETDATA]: ***********************************************************");
93 94
94 m_connectionString = connect; 95 m_connectionString = connect;
96 m_database = new PGSQLManager(m_connectionString);
95 97
96 using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString)) 98 using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString))
97 { 99 {
@@ -138,12 +140,12 @@ namespace OpenSim.Data.PGSQL
138 dbcon.Open(); 140 dbcon.Open();
139 141
140 using (NpgsqlCommand cmd = new NpgsqlCommand( 142 using (NpgsqlCommand cmd = new NpgsqlCommand(
141 @"SELECT ""Name"", ""Description"", ""AccessTime"", ""AssetType"", ""Local"", ""Temporary"", ""AssetFlags"", ""CreatorID"", ""Data"" 143 @"SELECT name, description, access_time, ""AssetType"", local, temporary, asset_flags, creatorid, data
142 FROM XAssetsMeta 144 FROM XAssetsMeta
143 JOIN XAssetsData ON XAssetsMeta.Hash = XAssetsData.Hash WHERE ""ID""=:ID", 145 JOIN XAssetsData ON XAssetsMeta.hash = XAssetsData.Hash WHERE id=:ID",
144 dbcon)) 146 dbcon))
145 { 147 {
146 cmd.Parameters.AddWithValue("ID", assetID.ToString()); 148 cmd.Parameters.Add(m_database.CreateParameter("ID", assetID));
147 149
148 try 150 try
149 { 151 {
@@ -151,18 +153,23 @@ namespace OpenSim.Data.PGSQL
151 { 153 {
152 if (dbReader.Read()) 154 if (dbReader.Read())
153 { 155 {
154 asset = new AssetBase(assetID, (string)dbReader["Name"], (sbyte)dbReader["AssetType"], dbReader["CreatorID"].ToString()); 156 asset = new AssetBase(
155 asset.Data = (byte[])dbReader["Data"]; 157 assetID,
156 asset.Description = (string)dbReader["Description"]; 158 (string)dbReader["name"],
159 Convert.ToSByte(dbReader["AssetType"]),
160 dbReader["creatorid"].ToString());
157 161
158 string local = dbReader["Local"].ToString(); 162 asset.Data = (byte[])dbReader["data"];
163 asset.Description = (string)dbReader["description"];
164
165 string local = dbReader["local"].ToString();
159 if (local.Equals("1") || local.Equals("true", StringComparison.InvariantCultureIgnoreCase)) 166 if (local.Equals("1") || local.Equals("true", StringComparison.InvariantCultureIgnoreCase))
160 asset.Local = true; 167 asset.Local = true;
161 else 168 else
162 asset.Local = false; 169 asset.Local = false;
163 170
164 asset.Temporary = Convert.ToBoolean(dbReader["Temporary"]); 171 asset.Temporary = Convert.ToBoolean(dbReader["temporary"]);
165 asset.Flags = (AssetFlags)Convert.ToInt32(dbReader["AssetFlags"]); 172 asset.Flags = (AssetFlags)Convert.ToInt32(dbReader["asset_flags"]);
166 173
167 if (m_enableCompression) 174 if (m_enableCompression)
168 { 175 {
@@ -179,7 +186,7 @@ namespace OpenSim.Data.PGSQL
179 } 186 }
180 } 187 }
181 188
182 UpdateAccessTime(asset.Metadata, (int)dbReader["AccessTime"]); 189 UpdateAccessTime(asset.Metadata, (int)dbReader["access_time"]);
183 } 190 }
184 } 191 }
185 } 192 }
@@ -245,6 +252,9 @@ namespace OpenSim.Data.PGSQL
245 252
246 byte[] hash = hasher.ComputeHash(asset.Data); 253 byte[] hash = hasher.ComputeHash(asset.Data);
247 254
255 UUID asset_id;
256 UUID.TryParse(asset.ID, out asset_id);
257
248// m_log.DebugFormat( 258// m_log.DebugFormat(
249// "[XASSET DB]: Compressed data size for {0} {1}, hash {2} is {3}", 259// "[XASSET DB]: Compressed data size for {0} {1}, hash {2} is {3}",
250// asset.ID, asset.Name, hash, compressedData.Length); 260// asset.ID, asset.Name, hash, compressedData.Length);
@@ -253,31 +263,33 @@ namespace OpenSim.Data.PGSQL
253 { 263 {
254 using (NpgsqlCommand cmd = 264 using (NpgsqlCommand cmd =
255 new NpgsqlCommand( 265 new NpgsqlCommand(
256 @"insert INTO XAssetsMeta(""ID"", ""Hash"", ""Name"", ""Description"", ""AssetType"", ""Local"", ""Temporary"", ""CreateTime"", ""AccessTime"", ""AssetFlags"", ""CreatorID"") 266 @"insert INTO XAssetsMeta(id, hash, name, description, ""AssetType"", local, temporary, create_time, access_time, asset_flags, creatorid)
257 Select :ID, :Hash, :Name, :Description, :AssetType, :Local, :Temporary, :CreateTime, :AccessTime, :AssetFlags, :CreatorID 267 Select :ID, :Hash, :Name, :Description, :AssetType, :Local, :Temporary, :CreateTime, :AccessTime, :AssetFlags, :CreatorID
258 where not exists( Select ""ID"" from XAssetsMeta where ""ID"" = :ID ; 268 where not exists( Select id from XAssetsMeta where id = :ID);
259 269
260 update XAssetsMeta 270 update XAssetsMeta
261 set ""ID"" = :ID, ""Hash"" = :Hash, ""Name"" = :Name, ""Description"" = :Description, 271 set id = :ID, hash = :Hash, name = :Name, description = :Description,
262 ""AssetType"" = :AssetType, ""Local"" = :Local, ""Temporary"" = :Temporary, ""CreateTime"" = :CreateTime, 272 ""AssetType"" = :AssetType, local = :Local, temporary = :Temporary, create_time = :CreateTime,
263 ""AccessTime"" = :AccessTime, ""AssetFlags"" = :AssetFlags, ""CreatorID"" = :CreatorID 273 access_time = :AccessTime, asset_flags = :AssetFlags, creatorid = :CreatorID
264 where ""ID"" = :ID; 274 where id = :ID;
265 ", 275 ",
266 dbcon)) 276 dbcon))
267 { 277 {
278
268 // create unix epoch time 279 // create unix epoch time
269 int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow); 280 int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow);
270 cmd.Parameters.AddWithValue("ID", asset.ID); 281 cmd.Parameters.Add(m_database.CreateParameter("ID", asset_id));
271 cmd.Parameters.AddWithValue("Hash", hash); 282 cmd.Parameters.Add(m_database.CreateParameter("Hash", hash));
272 cmd.Parameters.AddWithValue("Name", assetName); 283 cmd.Parameters.Add(m_database.CreateParameter("Name", assetName));
273 cmd.Parameters.AddWithValue("Description", assetDescription); 284 cmd.Parameters.Add(m_database.CreateParameter("Description", assetDescription));
274 cmd.Parameters.AddWithValue("AssetType", asset.Type); 285 cmd.Parameters.Add(m_database.CreateParameter("AssetType", asset.Type));
275 cmd.Parameters.AddWithValue("Local", asset.Local); 286 cmd.Parameters.Add(m_database.CreateParameter("Local", asset.Local));
276 cmd.Parameters.AddWithValue("Temporary", asset.Temporary); 287 cmd.Parameters.Add(m_database.CreateParameter("Temporary", asset.Temporary));
277 cmd.Parameters.AddWithValue("CreateTime", now); 288 cmd.Parameters.Add(m_database.CreateParameter("CreateTime", now));
278 cmd.Parameters.AddWithValue("AccessTime", now); 289 cmd.Parameters.Add(m_database.CreateParameter("AccessTime", now));
279 cmd.Parameters.AddWithValue("CreatorID", asset.Metadata.CreatorID); 290 cmd.Parameters.Add(m_database.CreateParameter("CreatorID", asset.Metadata.CreatorID));
280 cmd.Parameters.AddWithValue("AssetFlags", (int)asset.Flags); 291 cmd.Parameters.Add(m_database.CreateParameter("AssetFlags", (int)asset.Flags));
292
281 cmd.ExecuteNonQuery(); 293 cmd.ExecuteNonQuery();
282 } 294 }
283 } 295 }
@@ -297,11 +309,11 @@ namespace OpenSim.Data.PGSQL
297 { 309 {
298 using (NpgsqlCommand cmd = 310 using (NpgsqlCommand cmd =
299 new NpgsqlCommand( 311 new NpgsqlCommand(
300 @"INSERT INTO XAssetsData(""Hash"", ""Data"") VALUES(:Hash, :Data)", 312 @"INSERT INTO XAssetsData(hash, data) VALUES(:Hash, :Data)",
301 dbcon)) 313 dbcon))
302 { 314 {
303 cmd.Parameters.AddWithValue("Hash", hash); 315 cmd.Parameters.Add(m_database.CreateParameter("Hash", hash));
304 cmd.Parameters.AddWithValue("Data", asset.Data); 316 cmd.Parameters.Add(m_database.CreateParameter("Data", asset.Data));
305 cmd.ExecuteNonQuery(); 317 cmd.ExecuteNonQuery();
306 } 318 }
307 } 319 }
@@ -344,15 +356,18 @@ namespace OpenSim.Data.PGSQL
344 { 356 {
345 dbcon.Open(); 357 dbcon.Open();
346 NpgsqlCommand cmd = 358 NpgsqlCommand cmd =
347 new NpgsqlCommand(@"update XAssetsMeta set ""AccessTime""=:AccessTime where ID=:ID", dbcon); 359 new NpgsqlCommand(@"update XAssetsMeta set access_time=:AccessTime where id=:ID", dbcon);
348 360
349 try 361 try
350 { 362 {
363 UUID asset_id;
364 UUID.TryParse(assetMetadata.ID, out asset_id);
365
351 using (cmd) 366 using (cmd)
352 { 367 {
353 // create unix epoch time 368 // create unix epoch time
354 cmd.Parameters.AddWithValue("ID", assetMetadata.ID); 369 cmd.Parameters.Add(m_database.CreateParameter("id", asset_id));
355 cmd.Parameters.AddWithValue("AccessTime", (int)Utils.DateTimeToUnixTime(now)); 370 cmd.Parameters.Add(m_database.CreateParameter("access_time", (int)Utils.DateTimeToUnixTime(now)));
356 cmd.ExecuteNonQuery(); 371 cmd.ExecuteNonQuery();
357 } 372 }
358 } 373 }
@@ -380,9 +395,9 @@ namespace OpenSim.Data.PGSQL
380 395
381 bool exists = false; 396 bool exists = false;
382 397
383 using (NpgsqlCommand cmd = new NpgsqlCommand(@"SELECT ""Hash"" FROM XAssetsData WHERE ""Hash""=:Hash", dbcon)) 398 using (NpgsqlCommand cmd = new NpgsqlCommand(@"SELECT hash FROM XAssetsData WHERE hash=:Hash", dbcon))
384 { 399 {
385 cmd.Parameters.AddWithValue("Hash", hash); 400 cmd.Parameters.Add(m_database.CreateParameter("Hash", hash));
386 401
387 try 402 try
388 { 403 {
@@ -407,6 +422,43 @@ namespace OpenSim.Data.PGSQL
407 } 422 }
408 423
409 /// <summary> 424 /// <summary>
425 /// Check if the assets exist in the database.
426 /// </summary>
427 /// <param name="uuids">The assets' IDs</param>
428 /// <returns>For each asset: true if it exists, false otherwise</returns>
429 public bool[] AssetsExist(UUID[] uuids)
430 {
431 if (uuids.Length == 0)
432 return new bool[0];
433
434 HashSet<UUID> exist = new HashSet<UUID>();
435
436 string ids = "'" + string.Join("','", uuids) + "'";
437 string sql = string.Format(@"SELECT id FROM XAssetsMeta WHERE id IN ({0})", ids);
438
439 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
440 {
441 conn.Open();
442 using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
443 {
444 using (NpgsqlDataReader reader = cmd.ExecuteReader())
445 {
446 while (reader.Read())
447 {
448 UUID id = DBGuid.FromDB(reader["id"]);
449 exist.Add(id);
450 }
451 }
452 }
453 }
454
455 bool[] results = new bool[uuids.Length];
456 for (int i = 0; i < uuids.Length; i++)
457 results[i] = exist.Contains(uuids[i]);
458 return results;
459 }
460
461 /// <summary>
410 /// Check if the asset exists in the database 462 /// Check if the asset exists in the database
411 /// </summary> 463 /// </summary>
412 /// <param name="uuid">The asset UUID</param> 464 /// <param name="uuid">The asset UUID</param>
@@ -422,9 +474,9 @@ namespace OpenSim.Data.PGSQL
422 using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString)) 474 using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString))
423 { 475 {
424 dbcon.Open(); 476 dbcon.Open();
425 using (NpgsqlCommand cmd = new NpgsqlCommand(@"SELECT ""ID"" FROM XAssetsMeta WHERE ""ID""=:ID", dbcon)) 477 using (NpgsqlCommand cmd = new NpgsqlCommand(@"SELECT id FROM XAssetsMeta WHERE id=:ID", dbcon))
426 { 478 {
427 cmd.Parameters.AddWithValue("ID", uuid.ToString()); 479 cmd.Parameters.Add(m_database.CreateParameter("id", uuid));
428 480
429 try 481 try
430 { 482 {
@@ -466,11 +518,11 @@ namespace OpenSim.Data.PGSQL
466 using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString)) 518 using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString))
467 { 519 {
468 dbcon.Open(); 520 dbcon.Open();
469 NpgsqlCommand cmd = new NpgsqlCommand( @"SELECT ""Name"", ""Description"", ""AccessTime"", ""AssetType"", ""Temporary"", ""ID"", ""AssetFlags"", ""CreatorID"" 521 NpgsqlCommand cmd = new NpgsqlCommand( @"SELECT name, description, access_time, ""AssetType"", temporary, id, asset_flags, creatorid
470 FROM XAssetsMeta 522 FROM XAssetsMeta
471 LIMIT :start, :count", dbcon); 523 LIMIT :start, :count", dbcon);
472 cmd.Parameters.AddWithValue("start", start); 524 cmd.Parameters.Add(m_database.CreateParameter("start", start));
473 cmd.Parameters.AddWithValue("count", count); 525 cmd.Parameters.Add(m_database.CreateParameter("count", count));
474 526
475 try 527 try
476 { 528 {
@@ -479,18 +531,18 @@ namespace OpenSim.Data.PGSQL
479 while (dbReader.Read()) 531 while (dbReader.Read())
480 { 532 {
481 AssetMetadata metadata = new AssetMetadata(); 533 AssetMetadata metadata = new AssetMetadata();
482 metadata.Name = (string)dbReader["Name"]; 534 metadata.Name = (string)dbReader["name"];
483 metadata.Description = (string)dbReader["Description"]; 535 metadata.Description = (string)dbReader["description"];
484 metadata.Type = (sbyte)dbReader["AssetType"]; 536 metadata.Type = Convert.ToSByte(dbReader["AssetType"]);
485 metadata.Temporary = Convert.ToBoolean(dbReader["Temporary"]); // Not sure if this is correct. 537 metadata.Temporary = Convert.ToBoolean(dbReader["temporary"]);
486 metadata.Flags = (AssetFlags)Convert.ToInt32(dbReader["AssetFlags"]); 538 metadata.Flags = (AssetFlags)Convert.ToInt32(dbReader["asset_flags"]);
487 metadata.FullID = DBGuid.FromDB(dbReader["ID"]); 539 metadata.FullID = DBGuid.FromDB(dbReader["id"]);
488 metadata.CreatorID = dbReader["CreatorID"].ToString(); 540 metadata.CreatorID = dbReader["creatorid"].ToString();
489 541
490 // We'll ignore this for now - it appears unused! 542 // We'll ignore this for now - it appears unused!
491// metadata.SHA1 = dbReader["hash"]); 543// metadata.SHA1 = dbReader["hash"]);
492 544
493 UpdateAccessTime(metadata, (int)dbReader["AccessTime"]); 545 UpdateAccessTime(metadata, (int)dbReader["access_time"]);
494 546
495 retList.Add(metadata); 547 retList.Add(metadata);
496 } 548 }
@@ -516,9 +568,9 @@ namespace OpenSim.Data.PGSQL
516 { 568 {
517 dbcon.Open(); 569 dbcon.Open();
518 570
519 using (NpgsqlCommand cmd = new NpgsqlCommand(@"delete from XAssetsMeta where ""ID""=:ID", dbcon)) 571 using (NpgsqlCommand cmd = new NpgsqlCommand(@"delete from XAssetsMeta where id=:ID", dbcon))
520 { 572 {
521 cmd.Parameters.AddWithValue("ID", id); 573 cmd.Parameters.Add(m_database.CreateParameter(id, id));
522 cmd.ExecuteNonQuery(); 574 cmd.ExecuteNonQuery();
523 } 575 }
524 576
generated by cgit v1.1 at 2025-01-09 09:20:29 +0000