aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim
diff options
context:
space:
mode:
authorBlueWall2014-10-12 10:50:32 -0400
committerBlueWall2014-10-12 10:50:32 -0400
commit300e78bfd57b9ca48daa5e11f4bbe4a5abfd1e9c (patch)
treeee19b157d2089302e41e44302611956c35e92c73 /OpenSim
parentFix some errors in PgSQL XAssets by changing some data types. Also make sql q... (diff)
downloadopensim-SC_OLD-300e78bfd57b9ca48daa5e11f4bbe4a5abfd1e9c.zip
opensim-SC_OLD-300e78bfd57b9ca48daa5e11f4bbe4a5abfd1e9c.tar.gz
opensim-SC_OLD-300e78bfd57b9ca48daa5e11f4bbe4a5abfd1e9c.tar.bz2
opensim-SC_OLD-300e78bfd57b9ca48daa5e11f4bbe4a5abfd1e9c.tar.xz
Make column names lower-case, change hash column type to bytea and remove old commented lines of code
Diffstat (limited to 'OpenSim')
-rw-r--r--OpenSim/Data/PGSQL/PGSQLXAssetData.cs87
-rw-r--r--OpenSim/Data/PGSQL/Resources/XAssetStore.migrations45
2 files changed, 80 insertions, 52 deletions
diff --git a/OpenSim/Data/PGSQL/PGSQLXAssetData.cs b/OpenSim/Data/PGSQL/PGSQLXAssetData.cs
index a8a9e38..967aeba 100644
--- a/OpenSim/Data/PGSQL/PGSQLXAssetData.cs
+++ b/OpenSim/Data/PGSQL/PGSQLXAssetData.cs
@@ -140,9 +140,9 @@ namespace OpenSim.Data.PGSQL
140 dbcon.Open(); 140 dbcon.Open();
141 141
142 using (NpgsqlCommand cmd = new NpgsqlCommand( 142 using (NpgsqlCommand cmd = new NpgsqlCommand(
143 @"SELECT ""Name"", ""Description"", ""AccessTime"", ""AssetType"", ""Local"", ""Temporary"", ""AssetFlags"", ""CreatorID"", ""Data"" 143 @"SELECT name, description, access_time, ""AssetType"", local, temporary, asset_flags, creatorid, data
144 FROM XAssetsMeta 144 FROM XAssetsMeta
145 JOIN XAssetsData ON XAssetsMeta.Hash = XAssetsData.Hash WHERE ""ID""=:ID", 145 JOIN XAssetsData ON XAssetsMeta.hash = XAssetsData.Hash WHERE id=:ID",
146 dbcon)) 146 dbcon))
147 { 147 {
148 cmd.Parameters.AddWithValue("ID", assetID.ToString()); 148 cmd.Parameters.AddWithValue("ID", assetID.ToString());
@@ -155,21 +155,21 @@ namespace OpenSim.Data.PGSQL
155 { 155 {
156 asset = new AssetBase( 156 asset = new AssetBase(
157 assetID, 157 assetID,
158 (string)dbReader["Name"], 158 (string)dbReader["name"],
159 Convert.ToSByte(dbReader["AssetType"]), 159 Convert.ToSByte(dbReader["AssetType"]),
160 dbReader["CreatorID"].ToString()); 160 dbReader["creatorid"].ToString());
161 161
162 asset.Data = (byte[])dbReader["Data"]; 162 asset.Data = (byte[])dbReader["data"];
163 asset.Description = (string)dbReader["Description"]; 163 asset.Description = (string)dbReader["description"];
164 164
165 string local = dbReader["Local"].ToString(); 165 string local = dbReader["local"].ToString();
166 if (local.Equals("1") || local.Equals("true", StringComparison.InvariantCultureIgnoreCase)) 166 if (local.Equals("1") || local.Equals("true", StringComparison.InvariantCultureIgnoreCase))
167 asset.Local = true; 167 asset.Local = true;
168 else 168 else
169 asset.Local = false; 169 asset.Local = false;
170 170
171 asset.Temporary = Convert.ToBoolean(dbReader["Temporary"]); 171 asset.Temporary = Convert.ToBoolean(dbReader["temporary"]);
172 asset.Flags = (AssetFlags)Convert.ToInt32(dbReader["AssetFlags"]); 172 asset.Flags = (AssetFlags)Convert.ToInt32(dbReader["asset_flags"]);
173 173
174 if (m_enableCompression) 174 if (m_enableCompression)
175 { 175 {
@@ -186,7 +186,7 @@ namespace OpenSim.Data.PGSQL
186 } 186 }
187 } 187 }
188 188
189 UpdateAccessTime(asset.Metadata, (int)dbReader["AccessTime"]); 189 UpdateAccessTime(asset.Metadata, (int)dbReader["access_time"]);
190 } 190 }
191 } 191 }
192 } 192 }
@@ -256,48 +256,35 @@ namespace OpenSim.Data.PGSQL
256// "[XASSET DB]: Compressed data size for {0} {1}, hash {2} is {3}", 256// "[XASSET DB]: Compressed data size for {0} {1}, hash {2} is {3}",
257// asset.ID, asset.Name, hash, compressedData.Length); 257// asset.ID, asset.Name, hash, compressedData.Length);
258 258
259 m_log.DebugFormat("[XASSET DB]: Hash Info {0} {1}", hash, hash.Length);
260
261 try 259 try
262 { 260 {
263 using (NpgsqlCommand cmd = 261 using (NpgsqlCommand cmd =
264 new NpgsqlCommand( 262 new NpgsqlCommand(
265 @"insert INTO XAssetsMeta(""ID"", ""Hash"", ""Name"", ""Description"", " + " \"AssetType\" " + @", ""Local"", ""Temporary"", ""CreateTime"", ""AccessTime"", ""AssetFlags"", ""CreatorID"") 263 @"insert INTO XAssetsMeta(id, hash, name, description, " + " \"AssetType\" " + @", local, temporary, create_time, access_time, asset_flags, creatorid)
266 Select :ID, :Hash, :Name, :Description, :AssetType, :Local, :Temporary, :CreateTime, :AccessTime, :AssetFlags, :CreatorID 264 Select :ID, :Hash, :Name, :Description, :AssetType, :Local, :Temporary, :CreateTime, :AccessTime, :AssetFlags, :CreatorID
267 where not exists( Select ""ID"" from XAssetsMeta where ""ID"" = :ID); 265 where not exists( Select id from XAssetsMeta where id = :ID);
268 266
269 update XAssetsMeta 267 update XAssetsMeta
270 set ""ID"" = :ID, ""Hash"" = :Hash, ""Name"" = :Name, ""Description"" = :Description, " + 268 set id = :ID, hash = :Hash, name = :Name, description = :Description, " +
271 "\"AssetType\" " + @" = :AssetType, ""Local"" = :Local, ""Temporary"" = :Temporary, ""CreateTime"" = :CreateTime, 269 "\"AssetType\" " + @" = :AssetType, local = :Local, temporary = :Temporary, create_time = :CreateTime,
272 ""AccessTime"" = :AccessTime, ""AssetFlags"" = :AssetFlags, ""CreatorID"" = :CreatorID 270 access_time = :AccessTime, asset_flags = :AssetFlags, creatorid = :CreatorID
273 where ""ID"" = :ID; 271 where id = :ID;
274 ", 272 ",
275 dbcon)) 273 dbcon))
276 { 274 {
277 // create unix epoch time 275 // create unix epoch time
278 int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow); 276 int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow);
279 cmd.Parameters.Add(m_database.CreateParameter("ID", asset.ID)); 277 cmd.Parameters.Add(m_database.CreateParameter("ID", asset.ID));
280 // cmd.Parameters.AddWithValue("ID", asset.ID);
281 cmd.Parameters.Add(m_database.CreateParameter("Hash", hash)); 278 cmd.Parameters.Add(m_database.CreateParameter("Hash", hash));
282 // cmd.Parameters.AddWithValue("Hash", hash);
283 cmd.Parameters.Add(m_database.CreateParameter("Name", assetName)); 279 cmd.Parameters.Add(m_database.CreateParameter("Name", assetName));
284 // cmd.Parameters.AddWithValue("Name", assetName);
285 cmd.Parameters.Add(m_database.CreateParameter("Description", assetDescription)); 280 cmd.Parameters.Add(m_database.CreateParameter("Description", assetDescription));
286 // cmd.Parameters.AddWithValue("Description", assetDescription);
287 cmd.Parameters.Add(m_database.CreateParameter("AssetType", asset.Type)); 281 cmd.Parameters.Add(m_database.CreateParameter("AssetType", asset.Type));
288 // cmd.Parameters.AddWithValue("AssetType", asset.Type);
289 cmd.Parameters.Add(m_database.CreateParameter("Local", asset.Local)); 282 cmd.Parameters.Add(m_database.CreateParameter("Local", asset.Local));
290 // cmd.Parameters.AddWithValue("Local", asset.Local);
291 cmd.Parameters.Add(m_database.CreateParameter("Temporary", asset.Temporary)); 283 cmd.Parameters.Add(m_database.CreateParameter("Temporary", asset.Temporary));
292 // cmd.Parameters.AddWithValue("Temporary", asset.Temporary);
293 cmd.Parameters.Add(m_database.CreateParameter("CreateTime", now)); 284 cmd.Parameters.Add(m_database.CreateParameter("CreateTime", now));
294 // cmd.Parameters.AddWithValue("CreateTime", now);
295 cmd.Parameters.Add(m_database.CreateParameter("AccessTime", now)); 285 cmd.Parameters.Add(m_database.CreateParameter("AccessTime", now));
296 // cmd.Parameters.AddWithValue("AccessTime", now);
297 cmd.Parameters.Add(m_database.CreateParameter("CreatorID", asset.Metadata.CreatorID)); 286 cmd.Parameters.Add(m_database.CreateParameter("CreatorID", asset.Metadata.CreatorID));
298 // cmd.Parameters.AddWithValue("CreatorID", asset.Metadata.CreatorID);
299 cmd.Parameters.Add(m_database.CreateParameter("AssetFlags", (int)asset.Flags)); 287 cmd.Parameters.Add(m_database.CreateParameter("AssetFlags", (int)asset.Flags));
300 // cmd.Parameters.AddWithValue("AssetFlags", (int)asset.Flags);
301 288
302 cmd.ExecuteNonQuery(); 289 cmd.ExecuteNonQuery();
303 } 290 }
@@ -318,13 +305,11 @@ namespace OpenSim.Data.PGSQL
318 { 305 {
319 using (NpgsqlCommand cmd = 306 using (NpgsqlCommand cmd =
320 new NpgsqlCommand( 307 new NpgsqlCommand(
321 @"INSERT INTO XAssetsData(""Hash"", ""Data"") VALUES(:Hash, :Data)", 308 @"INSERT INTO XAssetsData(hash, data) VALUES(:Hash, :Data)",
322 dbcon)) 309 dbcon))
323 { 310 {
324 cmd.Parameters.Add(m_database.CreateParameter("Hash", hash)); 311 cmd.Parameters.Add(m_database.CreateParameter("Hash", hash));
325 // cmd.Parameters.AddWithValue("Hash", hash);
326 cmd.Parameters.Add(m_database.CreateParameter("Data", asset.Data)); 312 cmd.Parameters.Add(m_database.CreateParameter("Data", asset.Data));
327 // cmd.Parameters.AddWithValue("Data", asset.Data);
328 cmd.ExecuteNonQuery(); 313 cmd.ExecuteNonQuery();
329 } 314 }
330 } 315 }
@@ -367,17 +352,15 @@ namespace OpenSim.Data.PGSQL
367 { 352 {
368 dbcon.Open(); 353 dbcon.Open();
369 NpgsqlCommand cmd = 354 NpgsqlCommand cmd =
370 new NpgsqlCommand(@"update XAssetsMeta set ""AccessTime""=:AccessTime where ID=:ID", dbcon); 355 new NpgsqlCommand(@"update XAssetsMeta set access_time=:AccessTime where id=:ID", dbcon);
371 356
372 try 357 try
373 { 358 {
374 using (cmd) 359 using (cmd)
375 { 360 {
376 // create unix epoch time 361 // create unix epoch time
377 cmd.Parameters.Add(m_database.CreateParameter("ID", assetMetadata.ID)); 362 cmd.Parameters.Add(m_database.CreateParameter("id", assetMetadata.ID));
378 // cmd.Parameters.AddWithValue("ID", assetMetadata.ID); 363 cmd.Parameters.Add(m_database.CreateParameter("access_time", (int)Utils.DateTimeToUnixTime(now)));
379 cmd.Parameters.Add(m_database.CreateParameter("AccessTime", (int)Utils.DateTimeToUnixTime(now)));
380 // cmd.Parameters.AddWithValue("AccessTime", (int)Utils.DateTimeToUnixTime(now));
381 cmd.ExecuteNonQuery(); 364 cmd.ExecuteNonQuery();
382 } 365 }
383 } 366 }
@@ -405,9 +388,9 @@ namespace OpenSim.Data.PGSQL
405 388
406 bool exists = false; 389 bool exists = false;
407 390
408 using (NpgsqlCommand cmd = new NpgsqlCommand(@"SELECT ""Hash"" FROM XAssetsData WHERE ""Hash""=:Hash", dbcon)) 391 using (NpgsqlCommand cmd = new NpgsqlCommand(@"SELECT hash FROM XAssetsData WHERE hash=:Hash", dbcon))
409 { 392 {
410 cmd.Parameters.AddWithValue("Hash", hash); 393 cmd.Parameters.AddWithValue("hash", hash);
411 394
412 try 395 try
413 { 396 {
@@ -444,7 +427,7 @@ namespace OpenSim.Data.PGSQL
444 HashSet<UUID> exist = new HashSet<UUID>(); 427 HashSet<UUID> exist = new HashSet<UUID>();
445 428
446 string ids = "'" + string.Join("','", uuids) + "'"; 429 string ids = "'" + string.Join("','", uuids) + "'";
447 string sql = string.Format(@"SELECT ""ID"" FROM XAssetsMeta WHERE ""ID"" IN ({0})", ids); 430 string sql = string.Format(@"SELECT id FROM XAssetsMeta WHERE id IN ({0})", ids);
448 431
449 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) 432 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
450 { 433 {
@@ -484,9 +467,9 @@ namespace OpenSim.Data.PGSQL
484 using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString)) 467 using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString))
485 { 468 {
486 dbcon.Open(); 469 dbcon.Open();
487 using (NpgsqlCommand cmd = new NpgsqlCommand(@"SELECT ""ID"" FROM XAssetsMeta WHERE ""ID""=:ID", dbcon)) 470 using (NpgsqlCommand cmd = new NpgsqlCommand(@"SELECT id FROM XAssetsMeta WHERE id=:ID", dbcon))
488 { 471 {
489 cmd.Parameters.AddWithValue("ID", uuid.ToString()); 472 cmd.Parameters.AddWithValue("id", uuid.ToString());
490 473
491 try 474 try
492 { 475 {
@@ -528,7 +511,7 @@ namespace OpenSim.Data.PGSQL
528 using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString)) 511 using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString))
529 { 512 {
530 dbcon.Open(); 513 dbcon.Open();
531 NpgsqlCommand cmd = new NpgsqlCommand( @"SELECT ""Name"", ""Description"", ""AccessTime"", ""AssetType"", ""Temporary"", ""ID"", ""AssetFlags"", ""CreatorID"" 514 NpgsqlCommand cmd = new NpgsqlCommand( @"SELECT name, description, access_time, ""AssetType"", temporary, id, asset_flags, creatorid
532 FROM XAssetsMeta 515 FROM XAssetsMeta
533 LIMIT :start, :count", dbcon); 516 LIMIT :start, :count", dbcon);
534 cmd.Parameters.AddWithValue("start", start); 517 cmd.Parameters.AddWithValue("start", start);
@@ -541,18 +524,18 @@ namespace OpenSim.Data.PGSQL
541 while (dbReader.Read()) 524 while (dbReader.Read())
542 { 525 {
543 AssetMetadata metadata = new AssetMetadata(); 526 AssetMetadata metadata = new AssetMetadata();
544 metadata.Name = (string)dbReader["Name"]; 527 metadata.Name = (string)dbReader["name"];
545 metadata.Description = (string)dbReader["Description"]; 528 metadata.Description = (string)dbReader["description"];
546 metadata.Type = Convert.ToSByte(dbReader["AssetType"]); 529 metadata.Type = Convert.ToSByte(dbReader["AssetType"]);
547 metadata.Temporary = Convert.ToBoolean(dbReader["Temporary"]); // Not sure if this is correct. 530 metadata.Temporary = Convert.ToBoolean(dbReader["temporary"]);
548 metadata.Flags = (AssetFlags)Convert.ToInt32(dbReader["AssetFlags"]); 531 metadata.Flags = (AssetFlags)Convert.ToInt32(dbReader["asset_flags"]);
549 metadata.FullID = DBGuid.FromDB(dbReader["ID"]); 532 metadata.FullID = DBGuid.FromDB(dbReader["id"]);
550 metadata.CreatorID = dbReader["CreatorID"].ToString(); 533 metadata.CreatorID = dbReader["creatorid"].ToString();
551 534
552 // We'll ignore this for now - it appears unused! 535 // We'll ignore this for now - it appears unused!
553// metadata.SHA1 = dbReader["hash"]); 536// metadata.SHA1 = dbReader["hash"]);
554 537
555 UpdateAccessTime(metadata, (int)dbReader["AccessTime"]); 538 UpdateAccessTime(metadata, (int)dbReader["access_time"]);
556 539
557 retList.Add(metadata); 540 retList.Add(metadata);
558 } 541 }
@@ -578,9 +561,9 @@ namespace OpenSim.Data.PGSQL
578 { 561 {
579 dbcon.Open(); 562 dbcon.Open();
580 563
581 using (NpgsqlCommand cmd = new NpgsqlCommand(@"delete from XAssetsMeta where ""ID""=:ID", dbcon)) 564 using (NpgsqlCommand cmd = new NpgsqlCommand(@"delete from XAssetsMeta where id=:ID", dbcon))
582 { 565 {
583 cmd.Parameters.AddWithValue("ID", id); 566 cmd.Parameters.AddWithValue(id, id);
584 cmd.ExecuteNonQuery(); 567 cmd.ExecuteNonQuery();
585 } 568 }
586 569
diff --git a/OpenSim/Data/PGSQL/Resources/XAssetStore.migrations b/OpenSim/Data/PGSQL/Resources/XAssetStore.migrations
index 1513cc3..8fb3656 100644
--- a/OpenSim/Data/PGSQL/Resources/XAssetStore.migrations
+++ b/OpenSim/Data/PGSQL/Resources/XAssetStore.migrations
@@ -36,4 +36,49 @@ ALTER TABLE xassetsmeta ALTER COLUMN "Temporary" SET DATA TYPE boolean USING CAS
36ALTER TABLE xassetsmeta ALTER COLUMN "Hash" SET DATA TYPE char(66); 36ALTER TABLE xassetsmeta ALTER COLUMN "Hash" SET DATA TYPE char(66);
37ALTER TABLE xassetsdata ALTER COLUMN "Hash" SET DATA TYPE char(66); 37ALTER TABLE xassetsdata ALTER COLUMN "Hash" SET DATA TYPE char(66);
38 38
39COMMIT;
40
41:VERSION 3
42
43BEGIN;
44
45ALTER TABLE xassetsmeta RENAME COLUMN "ID" TO id;
46ALTER TABLE xassetsmeta RENAME COLUMN "Hash" TO hash;
47ALTER TABLE xassetsmeta RENAME COLUMN "Name" TO name;
48ALTER TABLE xassetsmeta RENAME COLUMN "Description" TO description;
49ALTER TABLE xassetsmeta RENAME COLUMN "Local" to local;
50ALTER TABLE xassetsmeta RENAME COLUMN "Temporary" TO temporary;
51ALTER TABLE xassetsmeta RENAME COLUMN "CreateTime" TO create_time;
52ALTER TABLE xassetsmeta RENAME COLUMN "AccessTime" TO access_time;
53ALTER TABLE xassetsmeta RENAME COLUMN "AssetFlags" TO asset_flags;
54ALTER TABLE xassetsmeta RENAME COLUMN "CreatorID" TO creatorid;
55ALTER TABLE xassetsmeta DROP CONSTRAINT xassetsmeta_pkey;
56ALTER TABLE xassetsmeta ADD PRIMARY KEY (id);
57
58
59ALTER TABLE xassetsdata RENAME COLUMN "Hash" TO hash;
60ALTER TABLE xassetsdata RENAME COLUMN "Data" TO data;
61ALTER TABLE xassetsdata DROP CONSTRAINT xassetsdata_pkey;
62ALTER TABLE xassetsdata ADD PRIMARY KEY (hash);
63
64COMMIT;
65
66
67:VERSION 4
68
69BEGIN;
70
71;; This is a harsh way of migrating these columns to
72;; a different data type, but it didn't work otherwise
73;; and we have a strict warning when using the module
74;; so here we go ...
75
76ALTER TABLE xassetsmeta DROP COLUMN hash;
77ALTER TABLE xassetsmeta ADD COLUMN hash bytea NOT NULL;
78
79ALTER TABLE xassetsdata DROP CONSTRAINT xassetsdata_pkey;
80ALTER TABLE xassetsdata DROP COLUMN hash;
81ALTER TABLE xassetsdata ADD COLUMN hash bytea NOT NULL;
82ALTER TABLE xassetsdata ADD PRIMARY KEY (hash);
83
39COMMIT; \ No newline at end of file 84COMMIT; \ No newline at end of file