diff options
Diffstat (limited to 'OpenSim')
-rw-r--r-- | OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs | 378 | ||||
-rw-r--r-- | OpenSim/Data/PGSQL/Resources/UserProfiles.migrations | 56 |
2 files changed, 256 insertions, 178 deletions
diff --git a/OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs b/OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs index 46f57d8..5539e3a 100644 --- a/OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs +++ b/OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs | |||
@@ -40,6 +40,8 @@ namespace OpenSim.Data.PGSQL | |||
40 | public class UserProfilesData: IProfilesData | 40 | public class UserProfilesData: IProfilesData |
41 | { | 41 | { |
42 | static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); | 42 | static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); |
43 | |||
44 | protected PGSQLManager m_database; | ||
43 | 45 | ||
44 | #region Properites | 46 | #region Properites |
45 | string ConnectionString | 47 | string ConnectionString |
@@ -74,6 +76,7 @@ namespace OpenSim.Data.PGSQL | |||
74 | 76 | ||
75 | Migration m = new Migration(dbcon, Assembly, "UserProfiles"); | 77 | Migration m = new Migration(dbcon, Assembly, "UserProfiles"); |
76 | m.Update(); | 78 | m.Update(); |
79 | m_database = new PGSQLManager(ConnectionString); | ||
77 | } | 80 | } |
78 | } | 81 | } |
79 | #endregion Member Functions | 82 | #endregion Member Functions |
@@ -94,11 +97,11 @@ namespace OpenSim.Data.PGSQL | |||
94 | 97 | ||
95 | using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) | 98 | using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) |
96 | { | 99 | { |
97 | string query = @"SELECT ""classifieduuid"", ""name"" FROM classifieds WHERE ""creatoruuid"" = :Id"; | 100 | string query = @"SELECT classifieduuid, name FROM classifieds WHERE creatoruuid = :Id"; |
98 | dbcon.Open(); | 101 | dbcon.Open(); |
99 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) | 102 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) |
100 | { | 103 | { |
101 | cmd.Parameters.AddWithValue("Id", creatorId); | 104 | cmd.Parameters.Add(m_database.CreateParameter("Id", creatorId)); |
102 | using( NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default)) | 105 | using( NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default)) |
103 | { | 106 | { |
104 | if(reader.HasRows) | 107 | if(reader.HasRows) |
@@ -111,7 +114,7 @@ namespace OpenSim.Data.PGSQL | |||
111 | string Name = null; | 114 | string Name = null; |
112 | try | 115 | try |
113 | { | 116 | { |
114 | UUID.TryParse(Convert.ToString( reader["classifieduuid"]), out Id); | 117 | Id = DBGuid.FromDB(reader["classifieduuid"]); |
115 | Name = Convert.ToString(reader["name"]); | 118 | Name = Convert.ToString(reader["name"]); |
116 | } | 119 | } |
117 | catch (Exception e) | 120 | catch (Exception e) |
@@ -132,28 +135,25 @@ namespace OpenSim.Data.PGSQL | |||
132 | 135 | ||
133 | public bool UpdateClassifiedRecord(UserClassifiedAdd ad, ref string result) | 136 | public bool UpdateClassifiedRecord(UserClassifiedAdd ad, ref string result) |
134 | { | 137 | { |
135 | string query = @"INSERT INTO classifieds ( ""classifieduuid"",""creatoruuid"", ""creationdate"", ""expirationdate"", ""category"", | 138 | string query = string.Empty; |
136 | ""name"", ""description"", ""parceluuid"", ""parentestate"", ""snapshotuuid"", ""simname"", | 139 | |
137 | ""posglobal"", ""parcelname"", ""classifiedflags"", ""priceforlisting"") | 140 | query = @"WITH upsert AS ( |
138 | Select :ClassifiedId, :CreatorId, :CreatedDate, :ExpirationDate, :Category, | 141 | UPDATE classifieds SET |
139 | :Name, :Description, :ParcelId, :ParentEstate, :SnapshotId, :SimName | 142 | classifieduuid = :ClassifiedId, creatoruuid = :CreatorId, creationdate = :CreatedDate, |
140 | :GlobalPos, :ParcelName, :Flags, :ListingPrice | 143 | expirationdate = :ExpirationDate,category =:Category, name = :Name, description = :Description, |
141 | Where not exists( Select ""classifieduuid"" from classifieds where ""classifieduuid"" = :ClassifiedId ); | 144 | parceluuid = :ParcelId, parentestate = :ParentEstate, snapshotuuid = :SnapshotId, |
145 | simname = :SimName, posglobal = :GlobalPos, parcelname = :ParcelName, classifiedflags = :Flags, | ||
146 | priceforlisting = :ListingPrice | ||
147 | RETURNING * ) | ||
148 | INSERT INTO classifieds (classifieduuid,creatoruuid,creationdate,expirationdate,category,name, | ||
149 | description,parceluuid,parentestate,snapshotuuid,simname,posglobal,parcelname,classifiedflags, | ||
150 | priceforlisting) | ||
151 | SELECT | ||
152 | :ClassifiedId,:CreatorId,:CreatedDate,:ExpirationDate,:Category,:Name,:Description, | ||
153 | :ParcelId,:ParentEstate,:SnapshotId,:SimName,:GlobalPos,:ParcelName,:Flags,:ListingPrice | ||
154 | WHERE NOT EXISTS ( | ||
155 | SELECT * FROM upsert )"; | ||
142 | 156 | ||
143 | update classifieds | ||
144 | set category =:Category, | ||
145 | expirationdate = :ExpirationDate, | ||
146 | name = :Name, | ||
147 | description = :Description, | ||
148 | parentestate = :ParentEstate, | ||
149 | posglobal = :GlobalPos, | ||
150 | parcelname = :ParcelName, | ||
151 | classifiedflags = :Flags, | ||
152 | priceforlisting = :ListingPrice, | ||
153 | snapshotuuid = :SnapshotId | ||
154 | where classifieduuid = :ClassifiedId ; | ||
155 | "; | ||
156 | |||
157 | if(string.IsNullOrEmpty(ad.ParcelName)) | 157 | if(string.IsNullOrEmpty(ad.ParcelName)) |
158 | ad.ParcelName = "Unknown"; | 158 | ad.ParcelName = "Unknown"; |
159 | if(ad.ParcelId == null) | 159 | if(ad.ParcelId == null) |
@@ -190,21 +190,21 @@ namespace OpenSim.Data.PGSQL | |||
190 | dbcon.Open(); | 190 | dbcon.Open(); |
191 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) | 191 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) |
192 | { | 192 | { |
193 | cmd.Parameters.AddWithValue("ClassifiedId", ad.ClassifiedId.ToString()); | 193 | cmd.Parameters.Add(m_database.CreateParameter("ClassifiedId", ad.ClassifiedId)); |
194 | cmd.Parameters.AddWithValue("CreatorId", ad.CreatorId.ToString()); | 194 | cmd.Parameters.Add(m_database.CreateParameter("CreatorId", ad.CreatorId)); |
195 | cmd.Parameters.AddWithValue("CreatedDate", ad.CreationDate.ToString()); | 195 | cmd.Parameters.Add(m_database.CreateParameter("CreatedDate", (int)ad.CreationDate)); |
196 | cmd.Parameters.AddWithValue("ExpirationDate", ad.ExpirationDate.ToString()); | 196 | cmd.Parameters.Add(m_database.CreateParameter("ExpirationDate", (int)ad.ExpirationDate)); |
197 | cmd.Parameters.AddWithValue("Category", ad.Category.ToString()); | 197 | cmd.Parameters.Add(m_database.CreateParameter("Category", ad.Category.ToString())); |
198 | cmd.Parameters.AddWithValue("Name", ad.Name.ToString()); | 198 | cmd.Parameters.Add(m_database.CreateParameter("Name", ad.Name.ToString())); |
199 | cmd.Parameters.AddWithValue("Description", ad.Description.ToString()); | 199 | cmd.Parameters.Add(m_database.CreateParameter("Description", ad.Description.ToString())); |
200 | cmd.Parameters.AddWithValue("ParcelId", ad.ParcelId.ToString()); | 200 | cmd.Parameters.Add(m_database.CreateParameter("ParcelId", ad.ParcelId)); |
201 | cmd.Parameters.AddWithValue("ParentEstate", ad.ParentEstate.ToString()); | 201 | cmd.Parameters.Add(m_database.CreateParameter("ParentEstate", (int)ad.ParentEstate)); |
202 | cmd.Parameters.AddWithValue("SnapshotId", ad.SnapshotId.ToString ()); | 202 | cmd.Parameters.Add(m_database.CreateParameter("SnapshotId", ad.SnapshotId)); |
203 | cmd.Parameters.AddWithValue("SimName", ad.SimName.ToString()); | 203 | cmd.Parameters.Add(m_database.CreateParameter("SimName", ad.SimName.ToString())); |
204 | cmd.Parameters.AddWithValue("GlobalPos", ad.GlobalPos.ToString()); | 204 | cmd.Parameters.Add(m_database.CreateParameter("GlobalPos", ad.GlobalPos.ToString())); |
205 | cmd.Parameters.AddWithValue("ParcelName", ad.ParcelName.ToString()); | 205 | cmd.Parameters.Add(m_database.CreateParameter("ParcelName", ad.ParcelName.ToString())); |
206 | cmd.Parameters.AddWithValue("Flags", ad.Flags.ToString()); | 206 | cmd.Parameters.Add(m_database.CreateParameter("Flags", (int)Convert.ToInt32(ad.Flags))); |
207 | cmd.Parameters.AddWithValue("ListingPrice", ad.Price.ToString ()); | 207 | cmd.Parameters.Add(m_database.CreateParameter("ListingPrice", (int)Convert.ToInt32(ad.Price))); |
208 | 208 | ||
209 | cmd.ExecuteNonQuery(); | 209 | cmd.ExecuteNonQuery(); |
210 | } | 210 | } |
@@ -235,7 +235,7 @@ namespace OpenSim.Data.PGSQL | |||
235 | 235 | ||
236 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) | 236 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) |
237 | { | 237 | { |
238 | cmd.Parameters.AddWithValue("ClassifiedId", recordId.ToString()); | 238 | cmd.Parameters.Add(m_database.CreateParameter("ClassifiedId", recordId)); |
239 | 239 | ||
240 | lock(Lock) | 240 | lock(Lock) |
241 | { | 241 | { |
@@ -267,15 +267,18 @@ namespace OpenSim.Data.PGSQL | |||
267 | dbcon.Open(); | 267 | dbcon.Open(); |
268 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) | 268 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) |
269 | { | 269 | { |
270 | cmd.Parameters.AddWithValue("AdId", ad.ClassifiedId.ToString()); | 270 | cmd.Parameters.Add(m_database.CreateParameter("AdId", ad.ClassifiedId)); |
271 | 271 | ||
272 | using (NpgsqlDataReader reader = cmd.ExecuteReader()) | 272 | using (NpgsqlDataReader reader = cmd.ExecuteReader()) |
273 | { | 273 | { |
274 | if(reader.Read ()) | 274 | if(reader.Read ()) |
275 | { | 275 | { |
276 | ad.CreatorId = GetUUID(reader["creatoruuid"]); | 276 | // ad.CreatorId = GetUUID(reader["creatoruuid"]); |
277 | ad.ParcelId = GetUUID(reader["parceluuid"]); | 277 | ad.CreatorId = DBGuid.FromDB(reader["creatoruuid"]); |
278 | ad.SnapshotId = GetUUID(reader["snapshotuuid"]); | 278 | // ad.ParcelId = GetUUID(reader["parceluuid"]); |
279 | ad.ParcelId = DBGuid.FromDB(reader["parceluuid"]); | ||
280 | // ad.SnapshotId = GetUUID(reader["snapshotuuid"]); | ||
281 | ad.SnapshotId = DBGuid.FromDB(reader["snapshotuuid"]); | ||
279 | ad.CreationDate = Convert.ToInt32(reader["creationdate"]); | 282 | ad.CreationDate = Convert.ToInt32(reader["creationdate"]); |
280 | ad.ExpirationDate = Convert.ToInt32(reader["expirationdate"]); | 283 | ad.ExpirationDate = Convert.ToInt32(reader["expirationdate"]); |
281 | ad.ParentEstate = Convert.ToInt32(reader["parentestate"]); | 284 | ad.ParentEstate = Convert.ToInt32(reader["parentestate"]); |
@@ -297,7 +300,7 @@ namespace OpenSim.Data.PGSQL | |||
297 | catch (Exception e) | 300 | catch (Exception e) |
298 | { | 301 | { |
299 | m_log.DebugFormat("[PROFILES_DATA]" + | 302 | m_log.DebugFormat("[PROFILES_DATA]" + |
300 | ": GetPickInfo exception {0}", e.Message); | 303 | ": GetClassifiedInfo exception {0}", e.Message); |
301 | } | 304 | } |
302 | return true; | 305 | return true; |
303 | } | 306 | } |
@@ -330,7 +333,7 @@ namespace OpenSim.Data.PGSQL | |||
330 | dbcon.Open(); | 333 | dbcon.Open(); |
331 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) | 334 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) |
332 | { | 335 | { |
333 | cmd.Parameters.AddWithValue("Id", avatarId.ToString()); | 336 | cmd.Parameters.Add(m_database.CreateParameter("Id", avatarId)); |
334 | 337 | ||
335 | using (NpgsqlDataReader reader = cmd.ExecuteReader()) | 338 | using (NpgsqlDataReader reader = cmd.ExecuteReader()) |
336 | { | 339 | { |
@@ -340,7 +343,7 @@ namespace OpenSim.Data.PGSQL | |||
340 | { | 343 | { |
341 | OSDMap record = new OSDMap(); | 344 | OSDMap record = new OSDMap(); |
342 | 345 | ||
343 | record.Add("pickuuid",OSD.FromString((string)reader["pickuuid"])); | 346 | record.Add("pickuuid",OSD.FromUUID(DBGuid.FromDB(reader["pickuuid"]))); |
344 | record.Add("name",OSD.FromString((string)reader["name"])); | 347 | record.Add("name",OSD.FromString((string)reader["name"])); |
345 | data.Add(record); | 348 | data.Add(record); |
346 | } | 349 | } |
@@ -373,8 +376,8 @@ namespace OpenSim.Data.PGSQL | |||
373 | dbcon.Open(); | 376 | dbcon.Open(); |
374 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) | 377 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) |
375 | { | 378 | { |
376 | cmd.Parameters.AddWithValue("CreatorId", avatarId.ToString()); | 379 | cmd.Parameters.Add(m_database.CreateParameter("CreatorId", avatarId)); |
377 | cmd.Parameters.AddWithValue("PickId", pickId.ToString()); | 380 | cmd.Parameters.Add(m_database.CreateParameter("PickId", pickId)); |
378 | 381 | ||
379 | using (NpgsqlDataReader reader = cmd.ExecuteReader()) | 382 | using (NpgsqlDataReader reader = cmd.ExecuteReader()) |
380 | { | 383 | { |
@@ -387,18 +390,18 @@ namespace OpenSim.Data.PGSQL | |||
387 | if (string.IsNullOrEmpty(description)) | 390 | if (string.IsNullOrEmpty(description)) |
388 | description = "No description given."; | 391 | description = "No description given."; |
389 | 392 | ||
390 | UUID.TryParse((string)reader["pickuuid"], out pick.PickId); | 393 | pick.PickId = DBGuid.FromDB(reader["pickuuid"]); |
391 | UUID.TryParse((string)reader["creatoruuid"], out pick.CreatorId); | 394 | pick.CreatorId = DBGuid.FromDB(reader["creatoruuid"]); |
392 | UUID.TryParse((string)reader["parceluuid"], out pick.ParcelId); | 395 | pick.ParcelId = DBGuid.FromDB(reader["parceluuid"]); |
393 | UUID.TryParse((string)reader["snapshotuuid"], out pick.SnapshotId); | 396 | pick.SnapshotId = DBGuid.FromDB(reader["snapshotuuid"]); |
394 | pick.GlobalPos = (string)reader["posglobal"]; | 397 | pick.GlobalPos = (string)reader["posglobal"].ToString(); |
395 | bool.TryParse((string)reader["toppick"], out pick.TopPick); | 398 | pick.TopPick = Convert.ToBoolean(reader["toppick"]); |
396 | bool.TryParse((string)reader["enabled"], out pick.Enabled); | 399 | pick.Enabled = Convert.ToBoolean(reader["enabled"]); |
397 | pick.Name = (string)reader["name"]; | 400 | pick.Name = reader["name"].ToString (); |
398 | pick.Desc = description; | 401 | pick.Desc = reader["description"].ToString(); |
399 | pick.User = (string)reader["user"]; | 402 | pick.User = reader["user"].ToString(); |
400 | pick.OriginalName = (string)reader["originalname"]; | 403 | pick.OriginalName = reader["originalname"].ToString(); |
401 | pick.SimName = (string)reader["simname"]; | 404 | pick.SimName = reader["simname"].ToString(); |
402 | pick.SortOrder = (int)reader["sortorder"]; | 405 | pick.SortOrder = (int)reader["sortorder"]; |
403 | } | 406 | } |
404 | } | 407 | } |
@@ -418,19 +421,22 @@ namespace OpenSim.Data.PGSQL | |||
418 | { | 421 | { |
419 | string query = string.Empty; | 422 | string query = string.Empty; |
420 | 423 | ||
421 | query = @"INSERT INTO userpicks VALUES ( :PickId, :CreatorId, :TopPick, :ParcelId,:Name, :Desc, :SnapshotId,:User, | ||
422 | :Original, :SimName, :GlobalPos, :SortOrder, :Enabled) | ||
423 | where not exists ( select pickid from userpicks where pickid = :pickid); | ||
424 | 424 | ||
425 | Update userpicks | 425 | query = @"WITH upsert AS ( |
426 | set parceluuid = :ParcelId, | 426 | UPDATE userpicks SET |
427 | name = :Name, | 427 | pickuuid = :PickId, creatoruuid = :CreatorId, toppick = :TopPick, parceluuid = :ParcelId, |
428 | description = :Desc, | 428 | name = :Name, description = :Desc, snapshotuuid = :SnapshotId, ""user"" = :User, |
429 | snapshotuuid = :SnapshotId, | 429 | originalname = :Original, simname = :SimName, posglobal = :GlobalPos, |
430 | pickuuid = :PickId, | 430 | sortorder = :SortOrder, enabled = :Enabled |
431 | posglobal = :GlobalPos | 431 | RETURNING * ) |
432 | where pickid = :PickId; | 432 | INSERT INTO userpicks (pickuuid,creatoruuid,toppick,parceluuid,name,description, |
433 | "; | 433 | snapshotuuid,""user"",originalname,simname,posglobal,sortorder,enabled) |
434 | SELECT | ||
435 | :PickId,:CreatorId,:TopPick,:ParcelId,:Name,:Desc,:SnapshotId,:User, | ||
436 | :Original,:SimName,:GlobalPos,:SortOrder,:Enabled | ||
437 | WHERE NOT EXISTS ( | ||
438 | SELECT * FROM upsert )"; | ||
439 | |||
434 | 440 | ||
435 | try | 441 | try |
436 | { | 442 | { |
@@ -439,19 +445,19 @@ namespace OpenSim.Data.PGSQL | |||
439 | dbcon.Open(); | 445 | dbcon.Open(); |
440 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) | 446 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) |
441 | { | 447 | { |
442 | cmd.Parameters.AddWithValue("PickId", pick.PickId.ToString()); | 448 | cmd.Parameters.Add(m_database.CreateParameter("PickId", pick.PickId)); |
443 | cmd.Parameters.AddWithValue("CreatorId", pick.CreatorId.ToString()); | 449 | cmd.Parameters.Add(m_database.CreateParameter("CreatorId", pick.CreatorId)); |
444 | cmd.Parameters.AddWithValue("TopPick", pick.TopPick.ToString()); | 450 | cmd.Parameters.Add(m_database.CreateParameter("TopPick", pick.TopPick)); |
445 | cmd.Parameters.AddWithValue("ParcelId", pick.ParcelId.ToString()); | 451 | cmd.Parameters.Add(m_database.CreateParameter("ParcelId", pick.ParcelId)); |
446 | cmd.Parameters.AddWithValue("Name", pick.Name.ToString()); | 452 | cmd.Parameters.Add(m_database.CreateParameter("Name", pick.Name)); |
447 | cmd.Parameters.AddWithValue("Desc", pick.Desc.ToString()); | 453 | cmd.Parameters.Add(m_database.CreateParameter("Desc", pick.Desc)); |
448 | cmd.Parameters.AddWithValue("SnapshotId", pick.SnapshotId.ToString()); | 454 | cmd.Parameters.Add(m_database.CreateParameter("SnapshotId", pick.SnapshotId)); |
449 | cmd.Parameters.AddWithValue("User", pick.User.ToString()); | 455 | cmd.Parameters.Add(m_database.CreateParameter("User", pick.User)); |
450 | cmd.Parameters.AddWithValue("Original", pick.OriginalName.ToString()); | 456 | cmd.Parameters.Add(m_database.CreateParameter("Original", pick.OriginalName)); |
451 | cmd.Parameters.AddWithValue("SimName",pick.SimName.ToString()); | 457 | cmd.Parameters.Add(m_database.CreateParameter("SimName",pick.SimName)); |
452 | cmd.Parameters.AddWithValue("GlobalPos", pick.GlobalPos); | 458 | cmd.Parameters.Add(m_database.CreateParameter("GlobalPos", pick.GlobalPos)); |
453 | cmd.Parameters.AddWithValue("SortOrder", pick.SortOrder.ToString ()); | 459 | cmd.Parameters.Add(m_database.CreateParameter("SortOrder", pick.SortOrder)); |
454 | cmd.Parameters.AddWithValue("Enabled", pick.Enabled.ToString()); | 460 | cmd.Parameters.Add(m_database.CreateParameter("Enabled", pick.Enabled)); |
455 | 461 | ||
456 | cmd.ExecuteNonQuery(); | 462 | cmd.ExecuteNonQuery(); |
457 | } | 463 | } |
@@ -481,7 +487,7 @@ namespace OpenSim.Data.PGSQL | |||
481 | 487 | ||
482 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) | 488 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) |
483 | { | 489 | { |
484 | cmd.Parameters.AddWithValue("PickId", pickId.ToString()); | 490 | cmd.Parameters.Add(m_database.CreateParameter("PickId", pickId)); |
485 | 491 | ||
486 | cmd.ExecuteNonQuery(); | 492 | cmd.ExecuteNonQuery(); |
487 | } | 493 | } |
@@ -514,8 +520,8 @@ namespace OpenSim.Data.PGSQL | |||
514 | dbcon.Open(); | 520 | dbcon.Open(); |
515 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) | 521 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) |
516 | { | 522 | { |
517 | cmd.Parameters.AddWithValue("Id", notes.UserId.ToString()); | 523 | cmd.Parameters.Add(m_database.CreateParameter("Id", notes.UserId)); |
518 | cmd.Parameters.AddWithValue("TargetId", notes.TargetId.ToString()); | 524 | cmd.Parameters.Add(m_database.CreateParameter("TargetId", notes.TargetId)); |
519 | 525 | ||
520 | using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) | 526 | using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) |
521 | { | 527 | { |
@@ -551,14 +557,25 @@ namespace OpenSim.Data.PGSQL | |||
551 | else | 557 | else |
552 | { | 558 | { |
553 | remove = false; | 559 | remove = false; |
554 | query = @"INSERT INTO usernotes VALUES ( :UserId, :TargetId, :Notes ) | 560 | // query = @"INSERT INTO usernotes VALUES ( :UserId, :TargetId, :Notes ) |
555 | where not exists ( Select useruuid from usernotes where useruuid = :UserId and targetuuid = :TargetId ); | 561 | // where not exists ( Select useruuid from usernotes where useruuid = :UserId and targetuuid = :TargetId ); |
562 | // | ||
563 | // update usernotes | ||
564 | // set notes = :Notes | ||
565 | // where useruuid = :UserId | ||
566 | // and targetuuid = :TargetId; | ||
567 | // "; | ||
568 | |||
569 | |||
570 | query = @"WITH upsert AS ( | ||
571 | UPDATE usernotes SET notes = :Notes, useruuid = :UserId, targetuuid = :TargetId RETURNING * ) | ||
572 | INSERT INTO usernotes (notes,useruuid,targetuuid) | ||
573 | SELECT :Notes,:UserId,:TargetId | ||
574 | WHERE NOT EXISTS ( | ||
575 | SELECT * FROM upsert | ||
576 | )"; | ||
577 | |||
556 | 578 | ||
557 | update usernotes | ||
558 | set notes = :Notes | ||
559 | where useruuid = :UserId | ||
560 | and targetuuid = :TargetId; | ||
561 | "; | ||
562 | } | 579 | } |
563 | 580 | ||
564 | try | 581 | try |
@@ -569,9 +586,9 @@ namespace OpenSim.Data.PGSQL | |||
569 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) | 586 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) |
570 | { | 587 | { |
571 | if(!remove) | 588 | if(!remove) |
572 | cmd.Parameters.AddWithValue("Notes", note.Notes); | 589 | cmd.Parameters.Add(m_database.CreateParameter("Notes", note.Notes)); |
573 | cmd.Parameters.AddWithValue("TargetId", note.TargetId.ToString ()); | 590 | cmd.Parameters.Add(m_database.CreateParameter("TargetId", note.TargetId)); |
574 | cmd.Parameters.AddWithValue("UserId", note.UserId.ToString()); | 591 | cmd.Parameters.Add(m_database.CreateParameter("UserId", note.UserId)); |
575 | 592 | ||
576 | cmd.ExecuteNonQuery(); | 593 | cmd.ExecuteNonQuery(); |
577 | } | 594 | } |
@@ -603,7 +620,8 @@ namespace OpenSim.Data.PGSQL | |||
603 | dbcon.Open(); | 620 | dbcon.Open(); |
604 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) | 621 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) |
605 | { | 622 | { |
606 | cmd.Parameters.AddWithValue("Id", props.UserId.ToString()); | 623 | cmd.Parameters.Add(m_database.CreateParameter("Id", props.UserId)); |
624 | m_log.InfoFormat("Profile Data {0}", props.ToString()); | ||
607 | 625 | ||
608 | using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) | 626 | using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) |
609 | { | 627 | { |
@@ -612,12 +630,15 @@ namespace OpenSim.Data.PGSQL | |||
612 | m_log.DebugFormat("[PROFILES_DATA]" + | 630 | m_log.DebugFormat("[PROFILES_DATA]" + |
613 | ": Getting data for {0}.", props.UserId); | 631 | ": Getting data for {0}.", props.UserId); |
614 | reader.Read(); | 632 | reader.Read(); |
615 | props.WebUrl = (string)reader["profileURL"]; | 633 | props.WebUrl = (string)reader["profileURL"].ToString(); |
616 | UUID.TryParse((string)reader["profileImage"], out props.ImageId); | 634 | m_log.DebugFormat("[PROFILES_DATA]: WebURL {0} ", props.WebUrl); |
635 | // UUID.TryParse((string)reader["profileImage"], out props.ImageId); | ||
636 | props.ImageId = DBGuid.FromDB(reader["profileImage"]); | ||
637 | m_log.DebugFormat("[PROFILES_DATA]: profileImage {0} ", props.ImageId); | ||
617 | props.AboutText = (string)reader["profileAboutText"]; | 638 | props.AboutText = (string)reader["profileAboutText"]; |
618 | UUID.TryParse((string)reader["profileFirstImage"], out props.FirstLifeImageId); | 639 | props.FirstLifeImageId = DBGuid.FromDB(reader["profileFirstImage"]); |
619 | props.FirstLifeText = (string)reader["profileFirstText"]; | 640 | props.FirstLifeText = (string)reader["profileFirstText"]; |
620 | UUID.TryParse((string)reader["profilePartner"], out props.PartnerId); | 641 | props.PartnerId = DBGuid.FromDB(reader["profilePartner"]); |
621 | props.WantToMask = (int)reader["profileWantToMask"]; | 642 | props.WantToMask = (int)reader["profileWantToMask"]; |
622 | props.WantToText = (string)reader["profileWantToText"]; | 643 | props.WantToText = (string)reader["profileWantToText"]; |
623 | props.SkillsMask = (int)reader["profileSkillsMask"]; | 644 | props.SkillsMask = (int)reader["profileSkillsMask"]; |
@@ -645,19 +666,19 @@ namespace OpenSim.Data.PGSQL | |||
645 | 666 | ||
646 | query = "INSERT INTO userprofile ("; | 667 | query = "INSERT INTO userprofile ("; |
647 | query += "useruuid, "; | 668 | query += "useruuid, "; |
648 | query += "profilePartner, "; | 669 | query += "\"profilePartner\", "; |
649 | query += "profileAllowPublish, "; | 670 | query += "\"profileAllowPublish\", "; |
650 | query += "profileMaturePublish, "; | 671 | query += "\"profileMaturePublish\", "; |
651 | query += "profileURL, "; | 672 | query += "\"profileURL\", "; |
652 | query += "profileWantToMask, "; | 673 | query += "\"profileWantToMask\", "; |
653 | query += "profileWantToText, "; | 674 | query += "\"profileWantToText\", "; |
654 | query += "profileSkillsMask, "; | 675 | query += "\"profileSkillsMask\", "; |
655 | query += "profileSkillsText, "; | 676 | query += "\"profileSkillsText\", "; |
656 | query += "profileLanguages, "; | 677 | query += "\"profileLanguages\", "; |
657 | query += "profileImage, "; | 678 | query += "\"profileImage\", "; |
658 | query += "profileAboutText, "; | 679 | query += "\"profileAboutText\", "; |
659 | query += "profileFirstImage, "; | 680 | query += "\"profileFirstImage\", "; |
660 | query += "profileFirstText) VALUES ("; | 681 | query += "\"profileFirstText\") VALUES ("; |
661 | query += ":userId, "; | 682 | query += ":userId, "; |
662 | query += ":profilePartner, "; | 683 | query += ":profilePartner, "; |
663 | query += ":profileAllowPublish, "; | 684 | query += ":profileAllowPublish, "; |
@@ -678,20 +699,23 @@ namespace OpenSim.Data.PGSQL | |||
678 | 699 | ||
679 | using (NpgsqlCommand put = new NpgsqlCommand(query, dbcon)) | 700 | using (NpgsqlCommand put = new NpgsqlCommand(query, dbcon)) |
680 | { | 701 | { |
681 | put.Parameters.AddWithValue("userId", props.UserId.ToString()); | 702 | m_log.DebugFormat("[PROFILES_DATA]" + |
682 | put.Parameters.AddWithValue("profilePartner", props.PartnerId.ToString()); | 703 | ": Adding new data for {0}", props.UserId); |
683 | put.Parameters.AddWithValue("profileAllowPublish", props.PublishProfile); | 704 | |
684 | put.Parameters.AddWithValue("profileMaturePublish", props.PublishMature); | 705 | put.Parameters.Add(m_database.CreateParameter("userId", props.UserId)); |
685 | put.Parameters.AddWithValue("profileURL", props.WebUrl); | 706 | put.Parameters.Add(m_database.CreateParameter("profilePartner", props.PartnerId)); |
686 | put.Parameters.AddWithValue("profileWantToMask", props.WantToMask); | 707 | put.Parameters.Add(m_database.CreateParameter("profileAllowPublish", props.PublishProfile)); |
687 | put.Parameters.AddWithValue("profileWantToText", props.WantToText); | 708 | put.Parameters.Add(m_database.CreateParameter("profileMaturePublish", props.PublishMature)); |
688 | put.Parameters.AddWithValue("profileSkillsMask", props.SkillsMask); | 709 | put.Parameters.Add(m_database.CreateParameter("profileURL", props.WebUrl)); |
689 | put.Parameters.AddWithValue("profileSkillsText", props.SkillsText); | 710 | put.Parameters.Add(m_database.CreateParameter("profileWantToMask", props.WantToMask)); |
690 | put.Parameters.AddWithValue("profileLanguages", props.Language); | 711 | put.Parameters.Add(m_database.CreateParameter("profileWantToText", props.WantToText)); |
691 | put.Parameters.AddWithValue("profileImage", props.ImageId.ToString()); | 712 | put.Parameters.Add(m_database.CreateParameter("profileSkillsMask", props.SkillsMask)); |
692 | put.Parameters.AddWithValue("profileAboutText", props.AboutText); | 713 | put.Parameters.Add(m_database.CreateParameter("profileSkillsText", props.SkillsText)); |
693 | put.Parameters.AddWithValue("profileFirstImage", props.FirstLifeImageId.ToString()); | 714 | put.Parameters.Add(m_database.CreateParameter("profileLanguages", props.Language)); |
694 | put.Parameters.AddWithValue("profileFirstText", props.FirstLifeText); | 715 | put.Parameters.Add(m_database.CreateParameter("profileImage", props.ImageId)); |
716 | put.Parameters.Add(m_database.CreateParameter("profileAboutText", props.AboutText)); | ||
717 | put.Parameters.Add(m_database.CreateParameter("profileFirstImage", props.FirstLifeImageId)); | ||
718 | put.Parameters.Add(m_database.CreateParameter("profileFirstText", props.FirstLifeText)); | ||
695 | 719 | ||
696 | put.ExecuteNonQuery(); | 720 | put.ExecuteNonQuery(); |
697 | } | 721 | } |
@@ -703,7 +727,7 @@ namespace OpenSim.Data.PGSQL | |||
703 | catch (Exception e) | 727 | catch (Exception e) |
704 | { | 728 | { |
705 | m_log.DebugFormat("[PROFILES_DATA]" + | 729 | m_log.DebugFormat("[PROFILES_DATA]" + |
706 | ": Requst properties exception {0}", e.Message); | 730 | ": Requst properties exception {0} {1}", e.Message, e.StackTrace); |
707 | result = e.Message; | 731 | result = e.Message; |
708 | return false; | 732 | return false; |
709 | } | 733 | } |
@@ -715,12 +739,12 @@ namespace OpenSim.Data.PGSQL | |||
715 | string query = string.Empty; | 739 | string query = string.Empty; |
716 | 740 | ||
717 | query += "UPDATE userprofile SET "; | 741 | query += "UPDATE userprofile SET "; |
718 | query += "profileURL=:profileURL, "; | 742 | query += "\"profileURL\"=:profileURL, "; |
719 | query += "profileImage=:image, "; | 743 | query += "\"profileImage\"=:image, "; |
720 | query += "profileAboutText=:abouttext,"; | 744 | query += "\"profileAboutText\"=:abouttext,"; |
721 | query += "profileFirstImage=:firstlifeimage,"; | 745 | query += "\"profileFirstImage\"=:firstlifeimage,"; |
722 | query += "profileFirstText=:firstlifetext "; | 746 | query += "\"profileFirstText\"=:firstlifetext "; |
723 | query += "WHERE useruuid=:uuid"; | 747 | query += "WHERE \"useruuid\"=:uuid"; |
724 | 748 | ||
725 | try | 749 | try |
726 | { | 750 | { |
@@ -729,12 +753,12 @@ namespace OpenSim.Data.PGSQL | |||
729 | dbcon.Open(); | 753 | dbcon.Open(); |
730 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) | 754 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) |
731 | { | 755 | { |
732 | cmd.Parameters.AddWithValue("profileURL", props.WebUrl); | 756 | cmd.Parameters.Add(m_database.CreateParameter("profileURL", props.WebUrl)); |
733 | cmd.Parameters.AddWithValue("image", props.ImageId.ToString()); | 757 | cmd.Parameters.Add(m_database.CreateParameter("image", props.ImageId)); |
734 | cmd.Parameters.AddWithValue("abouttext", props.AboutText); | 758 | cmd.Parameters.Add(m_database.CreateParameter("abouttext", props.AboutText)); |
735 | cmd.Parameters.AddWithValue("firstlifeimage", props.FirstLifeImageId.ToString()); | 759 | cmd.Parameters.Add(m_database.CreateParameter("firstlifeimage", props.FirstLifeImageId)); |
736 | cmd.Parameters.AddWithValue("firstlifetext", props.FirstLifeText); | 760 | cmd.Parameters.Add(m_database.CreateParameter("firstlifetext", props.FirstLifeText)); |
737 | cmd.Parameters.AddWithValue("uuid", props.UserId.ToString()); | 761 | cmd.Parameters.Add(m_database.CreateParameter("uuid", props.UserId)); |
738 | 762 | ||
739 | cmd.ExecuteNonQuery(); | 763 | cmd.ExecuteNonQuery(); |
740 | } | 764 | } |
@@ -757,12 +781,12 @@ namespace OpenSim.Data.PGSQL | |||
757 | string query = string.Empty; | 781 | string query = string.Empty; |
758 | 782 | ||
759 | query += "UPDATE userprofile SET "; | 783 | query += "UPDATE userprofile SET "; |
760 | query += "profileWantToMask=:WantMask, "; | 784 | query += "\"profileWantToMask\"=:WantMask, "; |
761 | query += "profileWantToText=:WantText,"; | 785 | query += "\"profileWantToText\"=:WantText,"; |
762 | query += "profileSkillsMask=:SkillsMask,"; | 786 | query += "\"profileSkillsMask\"=:SkillsMask,"; |
763 | query += "profileSkillsText=:SkillsText, "; | 787 | query += "\"profileSkillsText\"=:SkillsText, "; |
764 | query += "profileLanguages=:Languages "; | 788 | query += "\"profileLanguages\"=:Languages "; |
765 | query += "WHERE useruuid=:uuid"; | 789 | query += "WHERE \"useruuid\"=:uuid"; |
766 | 790 | ||
767 | try | 791 | try |
768 | { | 792 | { |
@@ -771,12 +795,12 @@ namespace OpenSim.Data.PGSQL | |||
771 | dbcon.Open(); | 795 | dbcon.Open(); |
772 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) | 796 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) |
773 | { | 797 | { |
774 | cmd.Parameters.AddWithValue("WantMask", up.WantToMask); | 798 | cmd.Parameters.Add(m_database.CreateParameter("WantMask", up.WantToMask)); |
775 | cmd.Parameters.AddWithValue("WantText", up.WantToText); | 799 | cmd.Parameters.Add(m_database.CreateParameter("WantText", up.WantToText)); |
776 | cmd.Parameters.AddWithValue("SkillsMask", up.SkillsMask); | 800 | cmd.Parameters.Add(m_database.CreateParameter("SkillsMask", up.SkillsMask)); |
777 | cmd.Parameters.AddWithValue("SkillsText", up.SkillsText); | 801 | cmd.Parameters.Add(m_database.CreateParameter("SkillsText", up.SkillsText)); |
778 | cmd.Parameters.AddWithValue("Languages", up.Language); | 802 | cmd.Parameters.Add(m_database.CreateParameter("Languages", up.Language)); |
779 | cmd.Parameters.AddWithValue("uuid", up.UserId.ToString()); | 803 | cmd.Parameters.Add(m_database.CreateParameter("uuid", up.UserId)); |
780 | 804 | ||
781 | cmd.ExecuteNonQuery(); | 805 | cmd.ExecuteNonQuery(); |
782 | } | 806 | } |
@@ -809,7 +833,7 @@ namespace OpenSim.Data.PGSQL | |||
809 | 833 | ||
810 | using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format (query,"\"classifieds\""), dbcon)) | 834 | using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format (query,"\"classifieds\""), dbcon)) |
811 | { | 835 | { |
812 | cmd.Parameters.AddWithValue("Id", avatarId.ToString()); | 836 | cmd.Parameters.Add(m_database.CreateParameter("Id", avatarId)); |
813 | 837 | ||
814 | using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) | 838 | using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) |
815 | { | 839 | { |
@@ -817,7 +841,7 @@ namespace OpenSim.Data.PGSQL | |||
817 | { | 841 | { |
818 | while (reader.Read()) | 842 | while (reader.Read()) |
819 | { | 843 | { |
820 | data.Add(new OSDString((string)reader["snapshotuuid"].ToString ())); | 844 | data.Add(new OSDString((string)reader["snapshotuuid"])); |
821 | } | 845 | } |
822 | } | 846 | } |
823 | } | 847 | } |
@@ -828,7 +852,7 @@ namespace OpenSim.Data.PGSQL | |||
828 | 852 | ||
829 | using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format (query,"\"userpicks\""), dbcon)) | 853 | using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format (query,"\"userpicks\""), dbcon)) |
830 | { | 854 | { |
831 | cmd.Parameters.AddWithValue("Id", avatarId.ToString()); | 855 | cmd.Parameters.Add(m_database.CreateParameter("Id", avatarId)); |
832 | 856 | ||
833 | using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) | 857 | using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) |
834 | { | 858 | { |
@@ -836,7 +860,7 @@ namespace OpenSim.Data.PGSQL | |||
836 | { | 860 | { |
837 | while (reader.Read()) | 861 | while (reader.Read()) |
838 | { | 862 | { |
839 | data.Add(new OSDString((string)reader["snapshotuuid"].ToString ())); | 863 | data.Add(new OSDString((string)reader["snapshotuuid"])); |
840 | } | 864 | } |
841 | } | 865 | } |
842 | } | 866 | } |
@@ -849,7 +873,7 @@ namespace OpenSim.Data.PGSQL | |||
849 | 873 | ||
850 | using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format (query,"\"userpicks\""), dbcon)) | 874 | using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format (query,"\"userpicks\""), dbcon)) |
851 | { | 875 | { |
852 | cmd.Parameters.AddWithValue("Id", avatarId.ToString()); | 876 | cmd.Parameters.Add(m_database.CreateParameter("Id", avatarId)); |
853 | 877 | ||
854 | using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) | 878 | using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) |
855 | { | 879 | { |
@@ -857,8 +881,8 @@ namespace OpenSim.Data.PGSQL | |||
857 | { | 881 | { |
858 | while (reader.Read()) | 882 | while (reader.Read()) |
859 | { | 883 | { |
860 | data.Add(new OSDString((string)reader["profileImage"].ToString ())); | 884 | data.Add(new OSDString((string)reader["profileImage"])); |
861 | data.Add(new OSDString((string)reader["profileFirstImage"].ToString ())); | 885 | data.Add(new OSDString((string)reader["profileFirstImage"])); |
862 | } | 886 | } |
863 | } | 887 | } |
864 | } | 888 | } |
@@ -891,7 +915,7 @@ namespace OpenSim.Data.PGSQL | |||
891 | dbcon.Open(); | 915 | dbcon.Open(); |
892 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) | 916 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) |
893 | { | 917 | { |
894 | cmd.Parameters.AddWithValue("Id", pref.UserId.ToString()); | 918 | cmd.Parameters.Add(m_database.CreateParameter("Id", pref.UserId)); |
895 | 919 | ||
896 | using (NpgsqlDataReader reader = cmd.ExecuteReader()) | 920 | using (NpgsqlDataReader reader = cmd.ExecuteReader()) |
897 | { | 921 | { |
@@ -944,9 +968,9 @@ namespace OpenSim.Data.PGSQL | |||
944 | dbcon.Open(); | 968 | dbcon.Open(); |
945 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) | 969 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) |
946 | { | 970 | { |
947 | cmd.Parameters.AddWithValue("ImViaEmail", pref.IMViaEmail.ToString().ToLower ()); | 971 | cmd.Parameters.Add(m_database.CreateParameter("ImViaEmail", pref.IMViaEmail.ToString().ToLower ())); |
948 | cmd.Parameters.AddWithValue("Visible", pref.Visible.ToString().ToLower ()); | 972 | cmd.Parameters.Add(m_database.CreateParameter("Visible", pref.Visible.ToString().ToLower ())); |
949 | cmd.Parameters.AddWithValue("uuid", pref.UserId.ToString()); | 973 | cmd.Parameters.Add(m_database.CreateParameter("uuid", pref.UserId.ToString())); |
950 | 974 | ||
951 | lock(Lock) | 975 | lock(Lock) |
952 | { | 976 | { |
@@ -982,8 +1006,8 @@ namespace OpenSim.Data.PGSQL | |||
982 | dbcon.Open(); | 1006 | dbcon.Open(); |
983 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) | 1007 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) |
984 | { | 1008 | { |
985 | cmd.Parameters.AddWithValue("Id", props.UserId.ToString()); | 1009 | cmd.Parameters.Add(m_database.CreateParameter("Id", props.UserId)); |
986 | cmd.Parameters.AddWithValue (":TagId", props.TagId.ToString()); | 1010 | cmd.Parameters.Add(m_database.CreateParameter(":TagId", props.TagId)); |
987 | 1011 | ||
988 | using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) | 1012 | using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) |
989 | { | 1013 | { |
@@ -1003,10 +1027,10 @@ namespace OpenSim.Data.PGSQL | |||
1003 | 1027 | ||
1004 | using (NpgsqlCommand put = new NpgsqlCommand(query, dbcon)) | 1028 | using (NpgsqlCommand put = new NpgsqlCommand(query, dbcon)) |
1005 | { | 1029 | { |
1006 | put.Parameters.AddWithValue("Id", props.UserId.ToString()); | 1030 | put.Parameters.Add(m_database.CreateParameter("Id", props.UserId)); |
1007 | put.Parameters.AddWithValue("TagId", props.TagId.ToString()); | 1031 | put.Parameters.Add(m_database.CreateParameter("TagId", props.TagId)); |
1008 | put.Parameters.AddWithValue("DataKey", props.DataKey.ToString()); | 1032 | put.Parameters.Add(m_database.CreateParameter("DataKey", props.DataKey.ToString())); |
1009 | put.Parameters.AddWithValue("DataVal", props.DataVal.ToString()); | 1033 | put.Parameters.Add(m_database.CreateParameter("DataVal", props.DataVal.ToString())); |
1010 | 1034 | ||
1011 | lock(Lock) | 1035 | lock(Lock) |
1012 | { | 1036 | { |
@@ -1046,10 +1070,10 @@ namespace OpenSim.Data.PGSQL | |||
1046 | dbcon.Open(); | 1070 | dbcon.Open(); |
1047 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) | 1071 | using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) |
1048 | { | 1072 | { |
1049 | cmd.Parameters.AddWithValue("UserId", props.UserId.ToString()); | 1073 | cmd.Parameters.Add(m_database.CreateParameter("UserId", props.UserId.ToString())); |
1050 | cmd.Parameters.AddWithValue("TagId", props.TagId.ToString ()); | 1074 | cmd.Parameters.Add(m_database.CreateParameter("TagId", props.TagId.ToString ())); |
1051 | cmd.Parameters.AddWithValue("DataKey", props.DataKey.ToString ()); | 1075 | cmd.Parameters.Add(m_database.CreateParameter("DataKey", props.DataKey.ToString ())); |
1052 | cmd.Parameters.AddWithValue("DataVal", props.DataKey.ToString ()); | 1076 | cmd.Parameters.Add(m_database.CreateParameter("DataVal", props.DataKey.ToString ())); |
1053 | 1077 | ||
1054 | lock(Lock) | 1078 | lock(Lock) |
1055 | { | 1079 | { |
diff --git a/OpenSim/Data/PGSQL/Resources/UserProfiles.migrations b/OpenSim/Data/PGSQL/Resources/UserProfiles.migrations index 4fcaa8e..1544b48 100644 --- a/OpenSim/Data/PGSQL/Resources/UserProfiles.migrations +++ b/OpenSim/Data/PGSQL/Resources/UserProfiles.migrations | |||
@@ -89,4 +89,58 @@ CREATE TABLE usersettings ( | |||
89 | "visible" bytea NOT NULL, | 89 | "visible" bytea NOT NULL, |
90 | PRIMARY KEY ("useruuid") | 90 | PRIMARY KEY ("useruuid") |
91 | ); | 91 | ); |
92 | commit; \ No newline at end of file | 92 | commit; |
93 | |||
94 | :VERSION 4 | ||
95 | |||
96 | BEGIN; | ||
97 | |||
98 | -- Classifieds | ||
99 | ALTER TABLE classifieds DROP CONSTRAINT classifiedspk; | ||
100 | ALTER TABLE classifieds ALTER COLUMN classifieduuid SET DATA TYPE uuid using classifieduuid::uuid; | ||
101 | ALTER TABLE classifieds ALTER COLUMN creatoruuid SET DATA TYPE uuid using creatoruuid::uuid; | ||
102 | ALTER TABLE classifieds ALTER COLUMN parceluuid SET DATA TYPE uuid using parceluuid::uuid; | ||
103 | ALTER TABLE classifieds ALTER COLUMN snapshotuuid SET DATA TYPE uuid using snapshotuuid::uuid; | ||
104 | ALTER TABLE classifieds ADD CONSTRAINT classifiedspk PRIMARY KEY (classifieduuid); | ||
105 | |||
106 | -- Notes | ||
107 | ALTER TABLE usernotes DROP CONSTRAINT usernoteuk; | ||
108 | ALTER TABLE usernotes ALTER COLUMN useruuid SET DATA TYPE uuid USING useruuid::uuid; | ||
109 | ALTER TABLE usernotes ALTER COLUMN targetuuid SET DATA TYPE uuid USING targetuuid::uuid; | ||
110 | ALTER TABLE usernotes ADD CONSTRAINT usernoteuk UNIQUE (useruuid,targetuuid); | ||
111 | |||
112 | |||
113 | -- Userpicks | ||
114 | ALTER TABLE userpicks DROP CONSTRAINT userpicks_pkey; | ||
115 | ALTER TABLE userpicks ALTER COLUMN pickuuid SET DATA TYPE uuid USING pickuuid::uuid; | ||
116 | ALTER TABLE userpicks ALTER COLUMN creatoruuid SET DATA TYPE uuid USING creatoruuid::uuid; | ||
117 | ALTER TABLE userpicks ALTER COLUMN parceluuid SET DATA TYPE uuid USING parceluuid::uuid; | ||
118 | ALTER TABLE userpicks ALTER COLUMN parceluuid SET DATA TYPE uuid USING parceluuid::uuid; | ||
119 | ALTER TABLE userpicks ADD PRIMARY KEY (pickuuid); | ||
120 | |||
121 | -- Userprofile | ||
122 | ALTER TABLE userprofile DROP CONSTRAINT userprofile_pkey; | ||
123 | ALTER TABLE userprofile ALTER COLUMN useruuid SET DATA TYPE uuid USING useruuid::uuid; | ||
124 | ALTER TABLE userprofile ALTER COLUMN "profilePartner" SET DATA TYPE uuid USING "profilePartner"::uuid; | ||
125 | -- Force column conversions | ||
126 | ALTER TABLE userprofile ALTER COLUMN "profileAllowPublish" SET DATA TYPE boolean USING CASE WHEN false THEN false ELSE true END; | ||
127 | ALTER TABLE userprofile ALTER COLUMN "profileMaturePublish" SET DATA TYPE boolean USING CASE WHEN false THEN false ELSE true END; | ||
128 | ALTER TABLE userprofile ALTER COLUMN "profileImage" SET DATA TYPE uuid USING "profileImage"::uuid; | ||
129 | ALTER TABLE userprofile ALTER COLUMN "profileFirstImage" SET DATA TYPE uuid USING "profileFirstImage"::uuid; | ||
130 | ALTER TABLE userprofile ADD PRIMARY KEY (useruuid); | ||
131 | |||
132 | -- Userdata | ||
133 | ALTER TABLE userdata DROP CONSTRAINT userdata_pkey; | ||
134 | ALTER TABLE userdata ALTER COLUMN "UserId" SET DATA TYPE uuid USING "UserId"::uuid; | ||
135 | ALTER TABLE userdata ALTER COLUMN "UserId" SET DATA TYPE uuid USING "UserId"::uuid; | ||
136 | ALTER TABLE userdata ADD PRIMARY KEY ("UserId","TagId"); | ||
137 | |||
138 | |||
139 | -- Usersettings | ||
140 | ALTER TABLE usersettings DROP CONSTRAINT usersettings_pkey; | ||
141 | ALTER TABLE usersettings ALTER COLUMN useruuid SET DATA TYPE uuid USING useruuid::uuid; | ||
142 | ALTER TABLE usersettings ALTER COLUMN visible SET DATA TYPE boolean USING CASE WHEN false THEN false ELSE true END; | ||
143 | ALTER TABLE usersettings ADD COLUMN email varchar(254) NOT NULL; | ||
144 | ALTER TABLE usersettings ADD PRIMARY KEY (useruuid); | ||
145 | |||
146 | COMMIT; \ No newline at end of file | ||