diff options
Diffstat (limited to 'OpenSim/Data')
-rw-r--r-- | OpenSim/Data/PGSQL/PGSQLXAssetData.cs | 87 | ||||
-rw-r--r-- | OpenSim/Data/PGSQL/Resources/XAssetStore.migrations | 45 |
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 | |||
36 | ALTER TABLE xassetsmeta ALTER COLUMN "Hash" SET DATA TYPE char(66); | 36 | ALTER TABLE xassetsmeta ALTER COLUMN "Hash" SET DATA TYPE char(66); |
37 | ALTER TABLE xassetsdata ALTER COLUMN "Hash" SET DATA TYPE char(66); | 37 | ALTER TABLE xassetsdata ALTER COLUMN "Hash" SET DATA TYPE char(66); |
38 | 38 | ||
39 | COMMIT; | ||
40 | |||
41 | :VERSION 3 | ||
42 | |||
43 | BEGIN; | ||
44 | |||
45 | ALTER TABLE xassetsmeta RENAME COLUMN "ID" TO id; | ||
46 | ALTER TABLE xassetsmeta RENAME COLUMN "Hash" TO hash; | ||
47 | ALTER TABLE xassetsmeta RENAME COLUMN "Name" TO name; | ||
48 | ALTER TABLE xassetsmeta RENAME COLUMN "Description" TO description; | ||
49 | ALTER TABLE xassetsmeta RENAME COLUMN "Local" to local; | ||
50 | ALTER TABLE xassetsmeta RENAME COLUMN "Temporary" TO temporary; | ||
51 | ALTER TABLE xassetsmeta RENAME COLUMN "CreateTime" TO create_time; | ||
52 | ALTER TABLE xassetsmeta RENAME COLUMN "AccessTime" TO access_time; | ||
53 | ALTER TABLE xassetsmeta RENAME COLUMN "AssetFlags" TO asset_flags; | ||
54 | ALTER TABLE xassetsmeta RENAME COLUMN "CreatorID" TO creatorid; | ||
55 | ALTER TABLE xassetsmeta DROP CONSTRAINT xassetsmeta_pkey; | ||
56 | ALTER TABLE xassetsmeta ADD PRIMARY KEY (id); | ||
57 | |||
58 | |||
59 | ALTER TABLE xassetsdata RENAME COLUMN "Hash" TO hash; | ||
60 | ALTER TABLE xassetsdata RENAME COLUMN "Data" TO data; | ||
61 | ALTER TABLE xassetsdata DROP CONSTRAINT xassetsdata_pkey; | ||
62 | ALTER TABLE xassetsdata ADD PRIMARY KEY (hash); | ||
63 | |||
64 | COMMIT; | ||
65 | |||
66 | |||
67 | :VERSION 4 | ||
68 | |||
69 | BEGIN; | ||
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 | |||
76 | ALTER TABLE xassetsmeta DROP COLUMN hash; | ||
77 | ALTER TABLE xassetsmeta ADD COLUMN hash bytea NOT NULL; | ||
78 | |||
79 | ALTER TABLE xassetsdata DROP CONSTRAINT xassetsdata_pkey; | ||
80 | ALTER TABLE xassetsdata DROP COLUMN hash; | ||
81 | ALTER TABLE xassetsdata ADD COLUMN hash bytea NOT NULL; | ||
82 | ALTER TABLE xassetsdata ADD PRIMARY KEY (hash); | ||
83 | |||
39 | COMMIT; \ No newline at end of file | 84 | COMMIT; \ No newline at end of file |