aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data
diff options
context:
space:
mode:
authorBlueWall2014-10-15 09:08:25 -0400
committerBlueWall2014-10-15 09:08:25 -0400
commit1812cecdb7f4efb2b0527770a486ff0b2a51e7b4 (patch)
treebb6ced7c844ffbd318090cf850490536c798a1e4 /OpenSim/Data
parentGet V2 Groups working under PgSQL. Needed to re-create tables to satisy the g... (diff)
downloadopensim-SC_OLD-1812cecdb7f4efb2b0527770a486ff0b2a51e7b4.zip
opensim-SC_OLD-1812cecdb7f4efb2b0527770a486ff0b2a51e7b4.tar.gz
opensim-SC_OLD-1812cecdb7f4efb2b0527770a486ff0b2a51e7b4.tar.bz2
opensim-SC_OLD-1812cecdb7f4efb2b0527770a486ff0b2a51e7b4.tar.xz
Fix PgSQL adapter for UserProfiles
Diffstat (limited to 'OpenSim/Data')
-rw-r--r--OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs378
-rw-r--r--OpenSim/Data/PGSQL/Resources/UserProfiles.migrations56
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);
92commit; \ No newline at end of file 92commit;
93
94:VERSION 4
95
96BEGIN;
97
98-- Classifieds
99ALTER TABLE classifieds DROP CONSTRAINT classifiedspk;
100ALTER TABLE classifieds ALTER COLUMN classifieduuid SET DATA TYPE uuid using classifieduuid::uuid;
101ALTER TABLE classifieds ALTER COLUMN creatoruuid SET DATA TYPE uuid using creatoruuid::uuid;
102ALTER TABLE classifieds ALTER COLUMN parceluuid SET DATA TYPE uuid using parceluuid::uuid;
103ALTER TABLE classifieds ALTER COLUMN snapshotuuid SET DATA TYPE uuid using snapshotuuid::uuid;
104ALTER TABLE classifieds ADD CONSTRAINT classifiedspk PRIMARY KEY (classifieduuid);
105
106-- Notes
107ALTER TABLE usernotes DROP CONSTRAINT usernoteuk;
108ALTER TABLE usernotes ALTER COLUMN useruuid SET DATA TYPE uuid USING useruuid::uuid;
109ALTER TABLE usernotes ALTER COLUMN targetuuid SET DATA TYPE uuid USING targetuuid::uuid;
110ALTER TABLE usernotes ADD CONSTRAINT usernoteuk UNIQUE (useruuid,targetuuid);
111
112
113-- Userpicks
114ALTER TABLE userpicks DROP CONSTRAINT userpicks_pkey;
115ALTER TABLE userpicks ALTER COLUMN pickuuid SET DATA TYPE uuid USING pickuuid::uuid;
116ALTER TABLE userpicks ALTER COLUMN creatoruuid SET DATA TYPE uuid USING creatoruuid::uuid;
117ALTER TABLE userpicks ALTER COLUMN parceluuid SET DATA TYPE uuid USING parceluuid::uuid;
118ALTER TABLE userpicks ALTER COLUMN parceluuid SET DATA TYPE uuid USING parceluuid::uuid;
119ALTER TABLE userpicks ADD PRIMARY KEY (pickuuid);
120
121-- Userprofile
122ALTER TABLE userprofile DROP CONSTRAINT userprofile_pkey;
123ALTER TABLE userprofile ALTER COLUMN useruuid SET DATA TYPE uuid USING useruuid::uuid;
124ALTER TABLE userprofile ALTER COLUMN "profilePartner" SET DATA TYPE uuid USING "profilePartner"::uuid;
125-- Force column conversions
126ALTER TABLE userprofile ALTER COLUMN "profileAllowPublish" SET DATA TYPE boolean USING CASE WHEN false THEN false ELSE true END;
127ALTER TABLE userprofile ALTER COLUMN "profileMaturePublish" SET DATA TYPE boolean USING CASE WHEN false THEN false ELSE true END;
128ALTER TABLE userprofile ALTER COLUMN "profileImage" SET DATA TYPE uuid USING "profileImage"::uuid;
129ALTER TABLE userprofile ALTER COLUMN "profileFirstImage" SET DATA TYPE uuid USING "profileFirstImage"::uuid;
130ALTER TABLE userprofile ADD PRIMARY KEY (useruuid);
131
132-- Userdata
133ALTER TABLE userdata DROP CONSTRAINT userdata_pkey;
134ALTER TABLE userdata ALTER COLUMN "UserId" SET DATA TYPE uuid USING "UserId"::uuid;
135ALTER TABLE userdata ALTER COLUMN "UserId" SET DATA TYPE uuid USING "UserId"::uuid;
136ALTER TABLE userdata ADD PRIMARY KEY ("UserId","TagId");
137
138
139-- Usersettings
140ALTER TABLE usersettings DROP CONSTRAINT usersettings_pkey;
141ALTER TABLE usersettings ALTER COLUMN useruuid SET DATA TYPE uuid USING useruuid::uuid;
142ALTER TABLE usersettings ALTER COLUMN visible SET DATA TYPE boolean USING CASE WHEN false THEN false ELSE true END;
143ALTER TABLE usersettings ADD COLUMN email varchar(254) NOT NULL;
144ALTER TABLE usersettings ADD PRIMARY KEY (useruuid);
145
146COMMIT; \ No newline at end of file
generated by cgit v1.1 at 2024-07-16 10:41:16 +0000