aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/PGSQL
diff options
context:
space:
mode:
Diffstat (limited to 'OpenSim/Data/PGSQL')
-rw-r--r--OpenSim/Data/PGSQL/PGSQLAgentPreferencesData.cs64
-rw-r--r--OpenSim/Data/PGSQL/PGSQLAssetData.cs43
-rw-r--r--OpenSim/Data/PGSQL/PGSQLEstateData.cs6
-rw-r--r--OpenSim/Data/PGSQL/PGSQLFriendsData.cs2
-rw-r--r--OpenSim/Data/PGSQL/PGSQLGenericTableHandler.cs18
-rwxr-xr-x[-rw-r--r--]OpenSim/Data/PGSQL/PGSQLGroupsData.cs32
-rw-r--r--OpenSim/Data/PGSQL/PGSQLManager.cs4
-rwxr-xr-x[-rw-r--r--]OpenSim/Data/PGSQL/PGSQLPresenceData.cs9
-rw-r--r--OpenSim/Data/PGSQL/PGSQLSimulationData.cs72
-rw-r--r--OpenSim/Data/PGSQL/PGSQLUserAccountData.cs12
-rw-r--r--OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs765
-rw-r--r--OpenSim/Data/PGSQL/PGSQLXAssetData.cs152
-rw-r--r--OpenSim/Data/PGSQL/Properties/AssemblyInfo.cs2
-rw-r--r--OpenSim/Data/PGSQL/Resources/AssetStore.migrations5
-rw-r--r--OpenSim/Data/PGSQL/Resources/IM_Store.migrations19
-rwxr-xr-x[-rw-r--r--]OpenSim/Data/PGSQL/Resources/Presence.migrations18
-rw-r--r--OpenSim/Data/PGSQL/Resources/RegionStore.migrations8
-rw-r--r--OpenSim/Data/PGSQL/Resources/UserProfiles.migrations75
-rw-r--r--OpenSim/Data/PGSQL/Resources/XAssetStore.migrations53
-rw-r--r--OpenSim/Data/PGSQL/Resources/os_groups_Store.migrations117
20 files changed, 950 insertions, 526 deletions
diff --git a/OpenSim/Data/PGSQL/PGSQLAgentPreferencesData.cs b/OpenSim/Data/PGSQL/PGSQLAgentPreferencesData.cs
new file mode 100644
index 0000000..20612fe
--- /dev/null
+++ b/OpenSim/Data/PGSQL/PGSQLAgentPreferencesData.cs
@@ -0,0 +1,64 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections;
30using System.Collections.Generic;
31using System.Data;
32using OpenMetaverse;
33using OpenSim.Framework;
34using Npgsql;
35
36namespace OpenSim.Data.PGSQL
37{
38 public class PGSQLAgentPreferencesData : PGSQLGenericTableHandler<AgentPreferencesData>, IAgentPreferencesData
39 {
40 public PGSQLAgentPreferencesData(string connectionString, string realm)
41 : base(connectionString, realm, "AgentPrefs")
42 {
43 }
44
45 public AgentPreferencesData GetPrefs(UUID agentID)
46 {
47 // Until someone sends in a table that works
48 return null;
49 //AgentPreferencesData[] ret = Get("PrincipalID", agentID.ToString());
50
51 //if (ret.Length == 0)
52 // return null;
53
54 //return ret[0];
55 }
56
57 public override bool Store(AgentPreferencesData row)
58 {
59 // Until someone sends in a table that works
60 return false;
61 }
62
63 }
64}
diff --git a/OpenSim/Data/PGSQL/PGSQLAssetData.cs b/OpenSim/Data/PGSQL/PGSQLAssetData.cs
index 7c5c01d..81adb03 100644
--- a/OpenSim/Data/PGSQL/PGSQLAssetData.cs
+++ b/OpenSim/Data/PGSQL/PGSQLAssetData.cs
@@ -166,18 +166,18 @@ namespace OpenSim.Data.PGSQL
166 "; 166 ";
167 167
168 string assetName = asset.Name; 168 string assetName = asset.Name;
169 if (asset.Name.Length > 64) 169 if (asset.Name.Length > AssetBase.MAX_ASSET_NAME)
170 { 170 {
171 assetName = asset.Name.Substring(0, 64); 171 assetName = asset.Name.Substring(0, AssetBase.MAX_ASSET_NAME);
172 m_log.WarnFormat( 172 m_log.WarnFormat(
173 "[ASSET DB]: Name '{0}' for asset {1} truncated from {2} to {3} characters on add", 173 "[ASSET DB]: Name '{0}' for asset {1} truncated from {2} to {3} characters on add",
174 asset.Name, asset.ID, asset.Name.Length, assetName.Length); 174 asset.Name, asset.ID, asset.Name.Length, assetName.Length);
175 } 175 }
176 176
177 string assetDescription = asset.Description; 177 string assetDescription = asset.Description;
178 if (asset.Description.Length > 64) 178 if (asset.Description.Length > AssetBase.MAX_ASSET_DESC)
179 { 179 {
180 assetDescription = asset.Description.Substring(0, 64); 180 assetDescription = asset.Description.Substring(0, AssetBase.MAX_ASSET_DESC);
181 m_log.WarnFormat( 181 m_log.WarnFormat(
182 "[ASSET DB]: Description '{0}' for asset {1} truncated from {2} to {3} characters on add", 182 "[ASSET DB]: Description '{0}' for asset {1} truncated from {2} to {3} characters on add",
183 asset.Description, asset.ID, asset.Description.Length, assetDescription.Length); 183 asset.Description, asset.ID, asset.Description.Length, assetDescription.Length);
@@ -232,17 +232,38 @@ namespace OpenSim.Data.PGSQL
232// } 232// }
233 233
234 /// <summary> 234 /// <summary>
235 /// Check if asset exist in m_database 235 /// Check if the assets exist in the database.
236 /// </summary> 236 /// </summary>
237 /// <param name="uuid"></param> 237 /// <param name="uuids">The assets' IDs</param>
238 /// <returns>true if exist.</returns> 238 /// <returns>For each asset: true if it exists, false otherwise</returns>
239 override public bool ExistsAsset(UUID uuid) 239 public override bool[] AssetsExist(UUID[] uuids)
240 { 240 {
241 if (GetAsset(uuid) != null) 241 if (uuids.Length == 0)
242 return new bool[0];
243
244 HashSet<UUID> exist = new HashSet<UUID>();
245
246 string ids = "'" + string.Join("','", uuids) + "'";
247 string sql = string.Format("SELECT id FROM assets WHERE id IN ({0})", ids);
248
249 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
250 using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
242 { 251 {
243 return true; 252 conn.Open();
253 using (NpgsqlDataReader reader = cmd.ExecuteReader())
254 {
255 while (reader.Read())
256 {
257 UUID id = DBGuid.FromDB(reader["id"]);
258 exist.Add(id);
259 }
260 }
244 } 261 }
245 return false; 262
263 bool[] results = new bool[uuids.Length];
264 for (int i = 0; i < uuids.Length; i++)
265 results[i] = exist.Contains(uuids[i]);
266 return results;
246 } 267 }
247 268
248 /// <summary> 269 /// <summary>
diff --git a/OpenSim/Data/PGSQL/PGSQLEstateData.cs b/OpenSim/Data/PGSQL/PGSQLEstateData.cs
index 5ad0eaa..b5ca235 100644
--- a/OpenSim/Data/PGSQL/PGSQLEstateData.cs
+++ b/OpenSim/Data/PGSQL/PGSQLEstateData.cs
@@ -107,8 +107,8 @@ namespace OpenSim.Data.PGSQL
107 { 107 {
108 EstateSettings es = new EstateSettings(); 108 EstateSettings es = new EstateSettings();
109 109
110 string sql = "select estate_settings.\"" + String.Join("\",estate_settings.\"", FieldList) + 110 string sql = "select estate_settings.\"" + String.Join("\",estate_settings.\"", FieldList) +
111 "\" from estate_map left join estate_settings on estate_map.\"EstateID\" = estate_settings.\"EstateID\" " + 111 "\" from estate_map left join estate_settings on estate_map.\"EstateID\" = estate_settings.\"EstateID\" " +
112 " where estate_settings.\"EstateID\" is not null and \"RegionID\" = :RegionID"; 112 " where estate_settings.\"EstateID\" is not null and \"RegionID\" = :RegionID";
113 113
114 bool insertEstate = false; 114 bool insertEstate = false;
@@ -500,7 +500,7 @@ namespace OpenSim.Data.PGSQL
500 public List<int> GetEstatesByOwner(UUID ownerID) 500 public List<int> GetEstatesByOwner(UUID ownerID)
501 { 501 {
502 List<int> result = new List<int>(); 502 List<int> result = new List<int>();
503 string sql = "select \"estateID\" from estate_settings where \"EstateOwner\" = :EstateOwner"; 503 string sql = "select \"EstateID\" from estate_settings where \"EstateOwner\" = :EstateOwner";
504 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) 504 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
505 { 505 {
506 conn.Open(); 506 conn.Open();
diff --git a/OpenSim/Data/PGSQL/PGSQLFriendsData.cs b/OpenSim/Data/PGSQL/PGSQLFriendsData.cs
index 4c1ee02..a841353 100644
--- a/OpenSim/Data/PGSQL/PGSQLFriendsData.cs
+++ b/OpenSim/Data/PGSQL/PGSQLFriendsData.cs
@@ -51,7 +51,7 @@ namespace OpenSim.Data.PGSQL
51 } 51 }
52 52
53 53
54 public bool Delete(string principalID, string friend) 54 public override bool Delete(string principalID, string friend)
55 { 55 {
56 UUID princUUID = UUID.Zero; 56 UUID princUUID = UUID.Zero;
57 57
diff --git a/OpenSim/Data/PGSQL/PGSQLGenericTableHandler.cs b/OpenSim/Data/PGSQL/PGSQLGenericTableHandler.cs
index 2151568..826c6fc 100644
--- a/OpenSim/Data/PGSQL/PGSQLGenericTableHandler.cs
+++ b/OpenSim/Data/PGSQL/PGSQLGenericTableHandler.cs
@@ -300,9 +300,27 @@ namespace OpenSim.Data.PGSQL
300 m_Realm, where); 300 m_Realm, where);
301 cmd.Connection = conn; 301 cmd.Connection = conn;
302 cmd.CommandText = query; 302 cmd.CommandText = query;
303 //m_log.WarnFormat("[PGSQLGenericTable]: SELECT {0} WHERE {1}", m_Realm, where);
304
305 conn.Open();
306 return DoQuery(cmd);
307 }
308 }
303 309
310 public virtual T[] Get(string where, NpgsqlParameter parameter)
311 {
312 using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
313 using (NpgsqlCommand cmd = new NpgsqlCommand())
314 {
315
316 string query = String.Format("SELECT * FROM {0} WHERE {1}",
317 m_Realm, where);
318 cmd.Connection = conn;
319 cmd.CommandText = query;
304 //m_log.WarnFormat("[PGSQLGenericTable]: SELECT {0} WHERE {1}", m_Realm, where); 320 //m_log.WarnFormat("[PGSQLGenericTable]: SELECT {0} WHERE {1}", m_Realm, where);
305 321
322 cmd.Parameters.Add(parameter);
323
306 conn.Open(); 324 conn.Open();
307 return DoQuery(cmd); 325 return DoQuery(cmd);
308 } 326 }
diff --git a/OpenSim/Data/PGSQL/PGSQLGroupsData.cs b/OpenSim/Data/PGSQL/PGSQLGroupsData.cs
index ed75b63..e257e7c 100644..100755
--- a/OpenSim/Data/PGSQL/PGSQLGroupsData.cs
+++ b/OpenSim/Data/PGSQL/PGSQLGroupsData.cs
@@ -31,12 +31,13 @@ using System.Collections.Generic;
31using System.Reflection; 31using System.Reflection;
32using OpenSim.Framework; 32using OpenSim.Framework;
33using OpenMetaverse; 33using OpenMetaverse;
34using log4net;
34using Npgsql; 35using Npgsql;
35 36
36namespace OpenSim.Data.PGSQL 37namespace OpenSim.Data.PGSQL
37{ 38{
38 public class PGSQLGroupsData : IGroupsData 39 public class PGSQLGroupsData : IGroupsData
39 { 40 {
40 private PGSqlGroupsGroupsHandler m_Groups; 41 private PGSqlGroupsGroupsHandler m_Groups;
41 private PGSqlGroupsMembershipHandler m_Membership; 42 private PGSqlGroupsMembershipHandler m_Membership;
42 private PGSqlGroupsRolesHandler m_Roles; 43 private PGSqlGroupsRolesHandler m_Roles;
@@ -82,12 +83,19 @@ namespace OpenSim.Data.PGSQL
82 83
83 public GroupData[] RetrieveGroups(string pattern) 84 public GroupData[] RetrieveGroups(string pattern)
84 { 85 {
86
85 if (string.IsNullOrEmpty(pattern)) // True for where clause 87 if (string.IsNullOrEmpty(pattern)) // True for where clause
86 pattern = " true ORDER BY lower(\"Name\") LIMIT 100"; 88 {
87 else 89 pattern = " 1 ORDER BY lower(\"Name\") LIMIT 100";
88 pattern = string.Format(" lower(\"Name\") LIKE lower('%{0}%') ORDER BY lower(\"Name\") LIMIT 100", pattern); 90
89 91 return m_Groups.Get(pattern);
90 return m_Groups.Get(pattern); 92 }
93 else
94 {
95 pattern = " \"ShowInList\" = 1 AND lower(\"Name\") LIKE lower('%" + pattern + "%') ORDER BY lower(\"Name\") LIMIT 100";
96
97 return m_Groups.Get(pattern, new NpgsqlParameter("pattern", pattern));
98 }
91 } 99 }
92 100
93 public bool DeleteGroup(UUID groupID) 101 public bool DeleteGroup(UUID groupID)
@@ -424,13 +432,11 @@ namespace OpenSim.Data.PGSQL
424 432
425 public void DeleteOld() 433 public void DeleteOld()
426 { 434 {
427 uint now = (uint)Util.UnixTimeSinceEpoch();
428 435
429 using (NpgsqlCommand cmd = new NpgsqlCommand()) 436 using (NpgsqlCommand cmd = new NpgsqlCommand())
430 { 437 {
431 cmd.CommandText = String.Format("delete from {0} where \"TMStamp\" < :tstamp", m_Realm); 438 cmd.CommandText = String.Format("delete from {0} where \"TMStamp\" < CURRENT_DATE - INTERVAL '2 week'", m_Realm);
432 cmd.Parameters.AddWithValue("tstamp", now - 14 * 24 * 60 * 60); // > 2 weeks old 439
433
434 ExecuteNonQuery(cmd); 440 ExecuteNonQuery(cmd);
435 } 441 }
436 442
@@ -452,13 +458,11 @@ namespace OpenSim.Data.PGSQL
452 458
453 public void DeleteOld() 459 public void DeleteOld()
454 { 460 {
455 uint now = (uint)Util.UnixTimeSinceEpoch();
456 461
457 using (NpgsqlCommand cmd = new NpgsqlCommand()) 462 using (NpgsqlCommand cmd = new NpgsqlCommand())
458 { 463 {
459 cmd.CommandText = String.Format("delete from {0} where \"TMStamp\" < :tstamp", m_Realm); 464 cmd.CommandText = String.Format("delete from {0} where \"TMStamp\" < CURRENT_DATE - INTERVAL '2 week'", m_Realm);
460 cmd.Parameters.AddWithValue("tstamp", now - 14 * 24 * 60 * 60); // > 2 weeks old 465
461
462 ExecuteNonQuery(cmd); 466 ExecuteNonQuery(cmd);
463 } 467 }
464 468
diff --git a/OpenSim/Data/PGSQL/PGSQLManager.cs b/OpenSim/Data/PGSQL/PGSQLManager.cs
index 97f40b2..46f835a 100644
--- a/OpenSim/Data/PGSQL/PGSQLManager.cs
+++ b/OpenSim/Data/PGSQL/PGSQLManager.cs
@@ -249,6 +249,10 @@ namespace OpenSim.Data.PGSQL
249 { 249 {
250 return (DateTime)value; 250 return (DateTime)value;
251 } 251 }
252 if (PGFieldType == "double precision")
253 {
254 return (Double)value;
255 }
252 return CreateParameterValue(value); 256 return CreateParameterValue(value);
253 } 257 }
254 258
diff --git a/OpenSim/Data/PGSQL/PGSQLPresenceData.cs b/OpenSim/Data/PGSQL/PGSQLPresenceData.cs
index 666de07..0376585 100644..100755
--- a/OpenSim/Data/PGSQL/PGSQLPresenceData.cs
+++ b/OpenSim/Data/PGSQL/PGSQLPresenceData.cs
@@ -66,7 +66,7 @@ namespace OpenSim.Data.PGSQL
66 using (NpgsqlCommand cmd = new NpgsqlCommand()) 66 using (NpgsqlCommand cmd = new NpgsqlCommand())
67 { 67 {
68 68
69 cmd.CommandText = String.Format(@"DELETE FROM {0} WHERE ""RegionID""=:RegionID", m_Realm); 69 cmd.CommandText = String.Format("DELETE FROM {0} WHERE \"RegionID\" = :regionID", m_Realm);
70 70
71 cmd.Parameters.Add(m_database.CreateParameter("RegionID", regionID)); 71 cmd.Parameters.Add(m_database.CreateParameter("RegionID", regionID));
72 cmd.Connection = conn; 72 cmd.Connection = conn;
@@ -80,14 +80,15 @@ namespace OpenSim.Data.PGSQL
80 PresenceData[] pd = Get("SessionID", sessionID.ToString()); 80 PresenceData[] pd = Get("SessionID", sessionID.ToString());
81 if (pd.Length == 0) 81 if (pd.Length == 0)
82 return false; 82 return false;
83
84 if (regionID == UUID.Zero)
85 return false;
83 86
84 using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) 87 using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
85 using (NpgsqlCommand cmd = new NpgsqlCommand()) 88 using (NpgsqlCommand cmd = new NpgsqlCommand())
86 { 89 {
87 90
88 cmd.CommandText = String.Format(@"UPDATE {0} SET 91 cmd.CommandText = String.Format("UPDATE {0} SET \"RegionID\" = :regionID, \"LastSeen\" = now() WHERE \"SessionID\" = :sessionID", m_Realm);
89 ""RegionID"" = :RegionID
90 WHERE ""SessionID"" = :SessionID", m_Realm);
91 92
92 cmd.Parameters.Add(m_database.CreateParameter("SessionID", sessionID)); 93 cmd.Parameters.Add(m_database.CreateParameter("SessionID", sessionID));
93 cmd.Parameters.Add(m_database.CreateParameter("RegionID", regionID)); 94 cmd.Parameters.Add(m_database.CreateParameter("RegionID", regionID));
diff --git a/OpenSim/Data/PGSQL/PGSQLSimulationData.cs b/OpenSim/Data/PGSQL/PGSQLSimulationData.cs
index cd02e05..77d87d4 100644
--- a/OpenSim/Data/PGSQL/PGSQLSimulationData.cs
+++ b/OpenSim/Data/PGSQL/PGSQLSimulationData.cs
@@ -540,26 +540,28 @@ namespace OpenSim.Data.PGSQL
540 where ""RegionUUID"" = :RegionUUID order by ""Revision"" desc limit 1; "; 540 where ""RegionUUID"" = :RegionUUID order by ""Revision"" desc limit 1; ";
541 541
542 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString)) 542 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
543 using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
544 { 543 {
545 // PGSqlParameter param = new PGSqlParameter(); 544 using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
546 cmd.Parameters.Add(_Database.CreateParameter("RegionUUID", regionID));
547 conn.Open();
548 using (NpgsqlDataReader reader = cmd.ExecuteReader())
549 { 545 {
550 int rev; 546 // PGSqlParameter param = new PGSqlParameter();
551 if (reader.Read()) 547 cmd.Parameters.Add(_Database.CreateParameter("RegionUUID", regionID));
552 { 548 conn.Open();
553 rev = Convert.ToInt32(reader["Revision"]); 549 using (NpgsqlDataReader reader = cmd.ExecuteReader())
554 byte[] blob = (byte[])reader["Heightfield"];
555 terrData = TerrainData.CreateFromDatabaseBlobFactory(pSizeX, pSizeY, pSizeZ, rev, blob);
556 }
557 else
558 { 550 {
559 _Log.Info("[REGION DB]: No terrain found for region"); 551 int rev;
560 return null; 552 if (reader.Read())
553 {
554 rev = Convert.ToInt32(reader["Revision"]);
555 byte[] blob = (byte[])reader["Heightfield"];
556 terrData = TerrainData.CreateFromDatabaseBlobFactory(pSizeX, pSizeY, pSizeZ, rev, blob);
557 }
558 else
559 {
560 _Log.Info("[REGION DB]: No terrain found for region");
561 return null;
562 }
563 _Log.Info("[REGION DB]: Loaded terrain revision r" + rev);
561 } 564 }
562 _Log.Info("[REGION DB]: Loaded terrain revision r" + rev);
563 } 565 }
564 } 566 }
565 567
@@ -588,8 +590,11 @@ namespace OpenSim.Data.PGSQL
588 cmd.Parameters.Add(_Database.CreateParameter("RegionUUID", regionID)); 590 cmd.Parameters.Add(_Database.CreateParameter("RegionUUID", regionID));
589 conn.Open(); 591 conn.Open();
590 cmd.ExecuteNonQuery(); 592 cmd.ExecuteNonQuery();
593
594 _Log.InfoFormat("{0} Deleted terrain revision id = {1}", LogHeader, regionID);
591 } 595 }
592 } 596 }
597
593 int terrainDBRevision; 598 int terrainDBRevision;
594 Array terrainDBblob; 599 Array terrainDBblob;
595 terrData.GetDatabaseBlob(out terrainDBRevision, out terrainDBblob); 600 terrData.GetDatabaseBlob(out terrainDBRevision, out terrainDBblob);
@@ -605,10 +610,12 @@ namespace OpenSim.Data.PGSQL
605 cmd.Parameters.Add(_Database.CreateParameter("Heightfield", terrainDBblob)); 610 cmd.Parameters.Add(_Database.CreateParameter("Heightfield", terrainDBblob));
606 conn.Open(); 611 conn.Open();
607 cmd.ExecuteNonQuery(); 612 cmd.ExecuteNonQuery();
613
614 _Log.InfoFormat("{0} Stored terrain id = {1}, terrainSize = <{2},{3}>",
615 LogHeader, regionID, terrData.SizeX, terrData.SizeY);
608 } 616 }
609 } 617 }
610 618
611 _Log.Info("[REGION DB]: Stored terrain revision r " + terrainDBRevision);
612 } 619 }
613 620
614 /// <summary> 621 /// <summary>
@@ -1361,32 +1368,6 @@ namespace OpenSim.Data.PGSQL
1361 1368
1362 #region Private Methods 1369 #region Private Methods
1363 1370
1364 /*
1365 /// <summary>
1366 /// Serializes the terrain data for storage in DB.
1367 /// </summary>
1368 /// <param name="val">terrain data</param>
1369 /// <returns></returns>
1370 private static Array serializeTerrain(double[,] val)
1371 {
1372 MemoryStream str = new MemoryStream(((int)Constants.RegionSize * (int)Constants.RegionSize) * sizeof(double));
1373 BinaryWriter bw = new BinaryWriter(str);
1374
1375 // TODO: COMPATIBILITY - Add byte-order conversions
1376 for (int x = 0; x < (int)Constants.RegionSize; x++)
1377 for (int y = 0; y < (int)Constants.RegionSize; y++)
1378 {
1379 double height = val[x, y];
1380 if (height == 0.0)
1381 height = double.Epsilon;
1382
1383 bw.Write(height);
1384 }
1385
1386 return str.ToArray();
1387 }
1388 */
1389
1390 /// <summary> 1371 /// <summary>
1391 /// Stores new regionsettings. 1372 /// Stores new regionsettings.
1392 /// </summary> 1373 /// </summary>
@@ -2246,11 +2227,6 @@ namespace OpenSim.Data.PGSQL
2246 } 2227 }
2247 } 2228 }
2248 2229
2249 public UUID[] GetObjectIDs(UUID regionID)
2250 {
2251 return new UUID[0];
2252 }
2253
2254 public void SaveExtra(UUID regionID, string name, string value) 2230 public void SaveExtra(UUID regionID, string name, string value)
2255 { 2231 {
2256 } 2232 }
diff --git a/OpenSim/Data/PGSQL/PGSQLUserAccountData.cs b/OpenSim/Data/PGSQL/PGSQLUserAccountData.cs
index a5868fe..a2b5a2a 100644
--- a/OpenSim/Data/PGSQL/PGSQLUserAccountData.cs
+++ b/OpenSim/Data/PGSQL/PGSQLUserAccountData.cs
@@ -298,22 +298,26 @@ namespace OpenSim.Data.PGSQL
298 return new UserAccountData[0]; 298 return new UserAccountData[0];
299 299
300 string sql = ""; 300 string sql = "";
301 UUID scope_id;
302 UUID.TryParse(scopeID.ToString(), out scope_id);
301 303
302 using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) 304 using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
303 using (NpgsqlCommand cmd = new NpgsqlCommand()) 305 using (NpgsqlCommand cmd = new NpgsqlCommand())
304 { 306 {
305 if (words.Length == 1) 307 if (words.Length == 1)
306 { 308 {
307 sql = String.Format(@"select * from {0} where (""ScopeID""=:ScopeID or ""ScopeID""='00000000-0000-0000-0000-000000000000') and (""FirstName"" ilike :search or ""LastName"" ilike :search)", m_Realm); 309 sql = String.Format(@"select * from {0} where (""ScopeID""=:ScopeID or ""ScopeID""=:UUIDZero) and (""FirstName"" ilike :search or ""LastName"" ilike :search)", m_Realm);
308 cmd.Parameters.Add(m_database.CreateParameter("scopeID", scopeID)); 310 cmd.Parameters.Add(m_database.CreateParameter("scopeID", (UUID)scope_id));
311 cmd.Parameters.Add (m_database.CreateParameter("UUIDZero", (UUID)UUID.Zero));
309 cmd.Parameters.Add(m_database.CreateParameter("search", "%" + words[0] + "%")); 312 cmd.Parameters.Add(m_database.CreateParameter("search", "%" + words[0] + "%"));
310 } 313 }
311 else 314 else
312 { 315 {
313 sql = String.Format(@"select * from {0} where (""ScopeID""=:ScopeID or ""ScopeID""='00000000-0000-0000-0000-000000000000') and (""FirstName"" ilike :searchFirst or ""LastName"" ilike :searchLast)", m_Realm); 316 sql = String.Format(@"select * from {0} where (""ScopeID""=:ScopeID or ""ScopeID""=:UUIDZero) and (""FirstName"" ilike :searchFirst or ""LastName"" ilike :searchLast)", m_Realm);
314 cmd.Parameters.Add(m_database.CreateParameter("searchFirst", "%" + words[0] + "%")); 317 cmd.Parameters.Add(m_database.CreateParameter("searchFirst", "%" + words[0] + "%"));
315 cmd.Parameters.Add(m_database.CreateParameter("searchLast", "%" + words[1] + "%")); 318 cmd.Parameters.Add(m_database.CreateParameter("searchLast", "%" + words[1] + "%"));
316 cmd.Parameters.Add(m_database.CreateParameter("ScopeID", scopeID.ToString())); 319 cmd.Parameters.Add (m_database.CreateParameter("UUIDZero", (UUID)UUID.Zero));
320 cmd.Parameters.Add(m_database.CreateParameter("ScopeID", (UUID)scope_id));
317 } 321 }
318 cmd.Connection = conn; 322 cmd.Connection = conn;
319 cmd.CommandText = sql; 323 cmd.CommandText = sql;
diff --git a/OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs b/OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs
index f4e41b4..f166976 100644
--- a/OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs
+++ b/OpenSim/Data/PGSQL/PGSQLUserProfilesData.cs
@@ -37,47 +37,46 @@ using Npgsql;
37 37
38namespace OpenSim.Data.PGSQL 38namespace OpenSim.Data.PGSQL
39{ 39{
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 43
44 protected PGSQLManager m_database;
45
44 #region Properites 46 #region Properites
45 string ConnectionString 47 string ConnectionString
46 { 48 {
47 get; set; 49 get;
50 set;
48 } 51 }
49 52
50 protected object Lock
51 {
52 get; set;
53 }
54
55 protected virtual Assembly Assembly 53 protected virtual Assembly Assembly
56 { 54 {
57 get { return GetType().Assembly; } 55 get { return GetType().Assembly; }
58 } 56 }
59 57
60 #endregion Properties 58 #endregion Properties
61 59
62 #region class Member Functions 60 #region class Member Functions
63 public UserProfilesData(string connectionString) 61 public UserProfilesData(string connectionString)
64 { 62 {
65 ConnectionString = connectionString; 63 ConnectionString = connectionString;
66 Init(); 64 Init();
67 } 65 }
68 66
69 void Init() 67 void Init()
70 { 68 {
71 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) 69 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString))
72 { 70 {
73 dbcon.Open(); 71 dbcon.Open();
74 72
75 Migration m = new Migration(dbcon, Assembly, "UserProfiles"); 73 Migration m = new Migration(dbcon, Assembly, "UserProfiles");
76 m.Update(); 74 m.Update();
75 m_database = new PGSQLManager(ConnectionString);
77 } 76 }
78 } 77 }
79 #endregion Member Functions 78 #endregion Member Functions
80 79
81 #region Classifieds Queries 80 #region Classifieds Queries
82 /// <summary> 81 /// <summary>
83 /// Gets the classified records. 82 /// Gets the classified records.
@@ -91,34 +90,34 @@ namespace OpenSim.Data.PGSQL
91 public OSDArray GetClassifiedRecords(UUID creatorId) 90 public OSDArray GetClassifiedRecords(UUID creatorId)
92 { 91 {
93 OSDArray data = new OSDArray(); 92 OSDArray data = new OSDArray();
94 93
95 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) 94 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString))
96 { 95 {
97 string query = @"SELECT ""classifieduuid"", ""name"" FROM classifieds WHERE ""creatoruuid"" = :Id"; 96 string query = @"SELECT classifieduuid, name FROM classifieds WHERE creatoruuid = :Id";
98 dbcon.Open(); 97 dbcon.Open();
99 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) 98 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon))
100 { 99 {
101 cmd.Parameters.AddWithValue("Id", creatorId); 100 cmd.Parameters.Add(m_database.CreateParameter("Id", creatorId));
102 using( NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default)) 101 using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default))
103 { 102 {
104 if(reader.HasRows) 103 if (reader.HasRows)
105 { 104 {
106 while (reader.Read()) 105 while (reader.Read())
107 { 106 {
108 OSDMap n = new OSDMap(); 107 OSDMap n = new OSDMap();
109 UUID Id = UUID.Zero; 108 UUID Id = UUID.Zero;
110 109
111 string Name = null; 110 string Name = null;
112 try 111 try
113 { 112 {
114 UUID.TryParse(Convert.ToString( reader["classifieduuid"]), out Id); 113 Id = DBGuid.FromDB(reader["classifieduuid"]);
115 Name = Convert.ToString(reader["name"]); 114 Name = Convert.ToString(reader["name"]);
116 } 115 }
117 catch (Exception e) 116 catch (Exception e)
118 { 117 {
119 m_log.DebugFormat("[PROFILES_DATA]" + 118 m_log.Error("[PROFILES_DATA]: UserAccount exception ", e);
120 ": UserAccount exception {0}", e.Message);
121 } 119 }
120
122 n.Add("classifieduuid", OSD.FromUUID(Id)); 121 n.Add("classifieduuid", OSD.FromUUID(Id));
123 n.Add("name", OSD.FromString(Name)); 122 n.Add("name", OSD.FromString(Name));
124 data.Add(n); 123 data.Add(n);
@@ -129,60 +128,57 @@ namespace OpenSim.Data.PGSQL
129 } 128 }
130 return data; 129 return data;
131 } 130 }
132 131
133 public bool UpdateClassifiedRecord(UserClassifiedAdd ad, ref string result) 132 public bool UpdateClassifiedRecord(UserClassifiedAdd ad, ref string result)
134 { 133 {
135 string query = @"INSERT INTO classifieds ( ""classifieduuid"",""creatoruuid"", ""creationdate"", ""expirationdate"", ""category"", 134 string query = string.Empty;
136 ""name"", ""description"", ""parceluuid"", ""parentestate"", ""snapshotuuid"", ""simname"", 135
137 ""posglobal"", ""parcelname"", ""classifiedflags"", ""priceforlisting"") 136 query = @"WITH upsert AS (
138 Select :ClassifiedId, :CreatorId, :CreatedDate, :ExpirationDate, :Category, 137 UPDATE classifieds SET
139 :Name, :Description, :ParcelId, :ParentEstate, :SnapshotId, :SimName 138 classifieduuid = :ClassifiedId, creatoruuid = :CreatorId, creationdate = :CreatedDate,
140 :GlobalPos, :ParcelName, :Flags, :ListingPrice 139 expirationdate = :ExpirationDate,category =:Category, name = :Name, description = :Description,
141 Where not exists( Select ""classifieduuid"" from classifieds where ""classifieduuid"" = :ClassifiedId ); 140 parceluuid = :ParcelId, parentestate = :ParentEstate, snapshotuuid = :SnapshotId,
142 141 simname = :SimName, posglobal = :GlobalPos, parcelname = :ParcelName, classifiedflags = :Flags,
143 update classifieds 142 priceforlisting = :ListingPrice
144 set category =:Category, 143 RETURNING * )
145 expirationdate = :ExpirationDate, 144 INSERT INTO classifieds (classifieduuid,creatoruuid,creationdate,expirationdate,category,name,
146 name = :Name, 145 description,parceluuid,parentestate,snapshotuuid,simname,posglobal,parcelname,classifiedflags,
147 description = :Description, 146 priceforlisting)
148 parentestate = :ParentEstate, 147 SELECT
149 posglobal = :GlobalPos, 148 :ClassifiedId,:CreatorId,:CreatedDate,:ExpirationDate,:Category,:Name,:Description,
150 parcelname = :ParcelName, 149 :ParcelId,:ParentEstate,:SnapshotId,:SimName,:GlobalPos,:ParcelName,:Flags,:ListingPrice
151 classifiedflags = :Flags, 150 WHERE NOT EXISTS (
152 priceforlisting = :ListingPrice, 151 SELECT * FROM upsert )";
153 snapshotuuid = :SnapshotId 152
154 where classifieduuid = :ClassifiedId ; 153 if (string.IsNullOrEmpty(ad.ParcelName))
155 ";
156
157 if(string.IsNullOrEmpty(ad.ParcelName))
158 ad.ParcelName = "Unknown"; 154 ad.ParcelName = "Unknown";
159 if(ad.ParcelId == null) 155 if (ad.ParcelId == null)
160 ad.ParcelId = UUID.Zero; 156 ad.ParcelId = UUID.Zero;
161 if(string.IsNullOrEmpty(ad.Description)) 157 if (string.IsNullOrEmpty(ad.Description))
162 ad.Description = "No Description"; 158 ad.Description = "No Description";
163 159
164 DateTime epoch = new DateTime(1970, 1, 1); 160 DateTime epoch = new DateTime(1970, 1, 1);
165 DateTime now = DateTime.Now; 161 DateTime now = DateTime.Now;
166 TimeSpan epochnow = now - epoch; 162 TimeSpan epochnow = now - epoch;
167 TimeSpan duration; 163 TimeSpan duration;
168 DateTime expiration; 164 DateTime expiration;
169 TimeSpan epochexp; 165 TimeSpan epochexp;
170 166
171 if(ad.Flags == 2) 167 if (ad.Flags == 2)
172 { 168 {
173 duration = new TimeSpan(7,0,0,0); 169 duration = new TimeSpan(7, 0, 0, 0);
174 expiration = now.Add(duration); 170 expiration = now.Add(duration);
175 epochexp = expiration - epoch; 171 epochexp = expiration - epoch;
176 } 172 }
177 else 173 else
178 { 174 {
179 duration = new TimeSpan(365,0,0,0); 175 duration = new TimeSpan(365, 0, 0, 0);
180 expiration = now.Add(duration); 176 expiration = now.Add(duration);
181 epochexp = expiration - epoch; 177 epochexp = expiration - epoch;
182 } 178 }
183 ad.CreationDate = (int)epochnow.TotalSeconds; 179 ad.CreationDate = (int)epochnow.TotalSeconds;
184 ad.ExpirationDate = (int)epochexp.TotalSeconds; 180 ad.ExpirationDate = (int)epochexp.TotalSeconds;
185 181
186 try 182 try
187 { 183 {
188 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) 184 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString))
@@ -190,76 +186,71 @@ namespace OpenSim.Data.PGSQL
190 dbcon.Open(); 186 dbcon.Open();
191 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) 187 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon))
192 { 188 {
193 cmd.Parameters.AddWithValue("ClassifiedId", ad.ClassifiedId.ToString()); 189 cmd.Parameters.Add(m_database.CreateParameter("ClassifiedId", ad.ClassifiedId));
194 cmd.Parameters.AddWithValue("CreatorId", ad.CreatorId.ToString()); 190 cmd.Parameters.Add(m_database.CreateParameter("CreatorId", ad.CreatorId));
195 cmd.Parameters.AddWithValue("CreatedDate", ad.CreationDate.ToString()); 191 cmd.Parameters.Add(m_database.CreateParameter("CreatedDate", (int)ad.CreationDate));
196 cmd.Parameters.AddWithValue("ExpirationDate", ad.ExpirationDate.ToString()); 192 cmd.Parameters.Add(m_database.CreateParameter("ExpirationDate", (int)ad.ExpirationDate));
197 cmd.Parameters.AddWithValue("Category", ad.Category.ToString()); 193 cmd.Parameters.Add(m_database.CreateParameter("Category", ad.Category.ToString()));
198 cmd.Parameters.AddWithValue("Name", ad.Name.ToString()); 194 cmd.Parameters.Add(m_database.CreateParameter("Name", ad.Name.ToString()));
199 cmd.Parameters.AddWithValue("Description", ad.Description.ToString()); 195 cmd.Parameters.Add(m_database.CreateParameter("Description", ad.Description.ToString()));
200 cmd.Parameters.AddWithValue("ParcelId", ad.ParcelId.ToString()); 196 cmd.Parameters.Add(m_database.CreateParameter("ParcelId", ad.ParcelId));
201 cmd.Parameters.AddWithValue("ParentEstate", ad.ParentEstate.ToString()); 197 cmd.Parameters.Add(m_database.CreateParameter("ParentEstate", (int)ad.ParentEstate));
202 cmd.Parameters.AddWithValue("SnapshotId", ad.SnapshotId.ToString ()); 198 cmd.Parameters.Add(m_database.CreateParameter("SnapshotId", ad.SnapshotId));
203 cmd.Parameters.AddWithValue("SimName", ad.SimName.ToString()); 199 cmd.Parameters.Add(m_database.CreateParameter("SimName", ad.SimName.ToString()));
204 cmd.Parameters.AddWithValue("GlobalPos", ad.GlobalPos.ToString()); 200 cmd.Parameters.Add(m_database.CreateParameter("GlobalPos", ad.GlobalPos.ToString()));
205 cmd.Parameters.AddWithValue("ParcelName", ad.ParcelName.ToString()); 201 cmd.Parameters.Add(m_database.CreateParameter("ParcelName", ad.ParcelName.ToString()));
206 cmd.Parameters.AddWithValue("Flags", ad.Flags.ToString()); 202 cmd.Parameters.Add(m_database.CreateParameter("Flags", (int)Convert.ToInt32(ad.Flags)));
207 cmd.Parameters.AddWithValue("ListingPrice", ad.Price.ToString ()); 203 cmd.Parameters.Add(m_database.CreateParameter("ListingPrice", (int)Convert.ToInt32(ad.Price)));
208 204
209 cmd.ExecuteNonQuery(); 205 cmd.ExecuteNonQuery();
210 } 206 }
211 } 207 }
212 } 208 }
213 catch (Exception e) 209 catch (Exception e)
214 { 210 {
215 m_log.DebugFormat("[PROFILES_DATA]" + 211 m_log.Error("[PROFILES_DATA]: ClassifiedsUpdate exception ", e);
216 ": ClassifiedesUpdate exception {0}", e.Message);
217 result = e.Message; 212 result = e.Message;
218 return false; 213 return false;
219 } 214 }
215
220 return true; 216 return true;
221 } 217 }
222 218
223
224 public bool DeleteClassifiedRecord(UUID recordId) 219 public bool DeleteClassifiedRecord(UUID recordId)
225 { 220 {
226 string query = string.Empty; 221 string query = string.Empty;
227 222
228 query = @"DELETE FROM classifieds WHERE classifieduuid = :ClasifiedId ;"; 223 query = @"DELETE FROM classifieds WHERE classifieduuid = :ClassifiedId ;";
229 224
230 try 225 try
231 { 226 {
232 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) 227 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString))
233 { 228 {
234 dbcon.Open(); 229 dbcon.Open();
235 230
236 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) 231 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon))
237 { 232 {
238 cmd.Parameters.AddWithValue("ClassifiedId", recordId.ToString()); 233 cmd.Parameters.Add(m_database.CreateParameter("ClassifiedId", recordId));
239 234 cmd.ExecuteNonQuery();
240 lock(Lock)
241 {
242 cmd.ExecuteNonQuery();
243 }
244 } 235 }
245 } 236 }
246 } 237 }
247 catch (Exception e) 238 catch (Exception e)
248 { 239 {
249 m_log.DebugFormat("[PROFILES_DATA]" + 240 m_log.Error("[PROFILES_DATA]: DeleteClassifiedRecord exception ", e);
250 ": DeleteClassifiedRecord exception {0}", e.Message);
251 return false; 241 return false;
252 } 242 }
243
253 return true; 244 return true;
254 } 245 }
255 246
256 public bool GetClassifiedInfo(ref UserClassifiedAdd ad, ref string result) 247 public bool GetClassifiedInfo(ref UserClassifiedAdd ad, ref string result)
257 { 248 {
258 string query = string.Empty; 249 string query = string.Empty;
259 250
260 query += "SELECT * FROM classifieds WHERE "; 251 query += "SELECT * FROM classifieds WHERE ";
261 query += "classifieduuid = :AdId"; 252 query += "classifieduuid = :AdId";
262 253
263 try 254 try
264 { 255 {
265 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) 256 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString))
@@ -267,15 +258,15 @@ namespace OpenSim.Data.PGSQL
267 dbcon.Open(); 258 dbcon.Open();
268 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) 259 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon))
269 { 260 {
270 cmd.Parameters.AddWithValue("AdId", ad.ClassifiedId.ToString()); 261 cmd.Parameters.Add(m_database.CreateParameter("AdId", ad.ClassifiedId));
271 262
272 using (NpgsqlDataReader reader = cmd.ExecuteReader()) 263 using (NpgsqlDataReader reader = cmd.ExecuteReader())
273 { 264 {
274 if(reader.Read ()) 265 if (reader.Read())
275 { 266 {
276 ad.CreatorId = GetUUID(reader["creatoruuid"]); 267 ad.CreatorId = DBGuid.FromDB(reader["creatoruuid"]);
277 ad.ParcelId = GetUUID(reader["parceluuid"]); 268 ad.ParcelId = DBGuid.FromDB(reader["parceluuid"]);
278 ad.SnapshotId = GetUUID(reader["snapshotuuid"]); 269 ad.SnapshotId = DBGuid.FromDB(reader["snapshotuuid"]);
279 ad.CreationDate = Convert.ToInt32(reader["creationdate"]); 270 ad.CreationDate = Convert.ToInt32(reader["creationdate"]);
280 ad.ExpirationDate = Convert.ToInt32(reader["expirationdate"]); 271 ad.ExpirationDate = Convert.ToInt32(reader["expirationdate"]);
281 ad.ParentEstate = Convert.ToInt32(reader["parentestate"]); 272 ad.ParentEstate = Convert.ToInt32(reader["parentestate"]);
@@ -287,7 +278,6 @@ namespace OpenSim.Data.PGSQL
287 ad.SimName = reader["simname"].ToString(); 278 ad.SimName = reader["simname"].ToString();
288 ad.GlobalPos = reader["posglobal"].ToString(); 279 ad.GlobalPos = reader["posglobal"].ToString();
289 ad.ParcelName = reader["parcelname"].ToString(); 280 ad.ParcelName = reader["parcelname"].ToString();
290
291 } 281 }
292 } 282 }
293 } 283 }
@@ -296,13 +286,14 @@ namespace OpenSim.Data.PGSQL
296 } 286 }
297 catch (Exception e) 287 catch (Exception e)
298 { 288 {
299 m_log.DebugFormat("[PROFILES_DATA]" + 289 m_log.Error("[PROFILES_DATA]: GetClassifiedInfo exception ", e);
300 ": GetPickInfo exception {0}", e.Message);
301 } 290 }
291
302 return true; 292 return true;
303 } 293 }
304 294
305 public static UUID GetUUID( object uuidValue ) { 295 public static UUID GetUUID(object uuidValue)
296 {
306 297
307 UUID ret = UUID.Zero; 298 UUID ret = UUID.Zero;
308 299
@@ -311,18 +302,17 @@ namespace OpenSim.Data.PGSQL
311 return ret; 302 return ret;
312 } 303 }
313 304
314
315 #endregion Classifieds Queries 305 #endregion Classifieds Queries
316 306
317 #region Picks Queries 307 #region Picks Queries
318 public OSDArray GetAvatarPicks(UUID avatarId) 308 public OSDArray GetAvatarPicks(UUID avatarId)
319 { 309 {
320 string query = string.Empty; 310 string query = string.Empty;
321 311
322 query += "SELECT \"pickuuid\",\"name\" FROM userpicks WHERE "; 312 query += "SELECT pickuuid, name FROM userpicks WHERE ";
323 query += "creatoruuid = :Id"; 313 query += "creatoruuid = :Id";
324 OSDArray data = new OSDArray(); 314 OSDArray data = new OSDArray();
325 315
326 try 316 try
327 { 317 {
328 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) 318 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString))
@@ -330,18 +320,18 @@ namespace OpenSim.Data.PGSQL
330 dbcon.Open(); 320 dbcon.Open();
331 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) 321 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon))
332 { 322 {
333 cmd.Parameters.AddWithValue("Id", avatarId.ToString()); 323 cmd.Parameters.Add(m_database.CreateParameter("Id", avatarId));
334 324
335 using (NpgsqlDataReader reader = cmd.ExecuteReader()) 325 using (NpgsqlDataReader reader = cmd.ExecuteReader())
336 { 326 {
337 if(reader.HasRows) 327 if (reader.HasRows)
338 { 328 {
339 while (reader.Read()) 329 while (reader.Read())
340 { 330 {
341 OSDMap record = new OSDMap(); 331 OSDMap record = new OSDMap();
342 332
343 record.Add("pickuuid",OSD.FromString((string)reader["pickuuid"])); 333 record.Add("pickuuid", OSD.FromUUID(DBGuid.FromDB(reader["pickuuid"])));
344 record.Add("name",OSD.FromString((string)reader["name"])); 334 record.Add("name", OSD.FromString((string)reader["name"]));
345 data.Add(record); 335 data.Add(record);
346 } 336 }
347 } 337 }
@@ -351,21 +341,21 @@ namespace OpenSim.Data.PGSQL
351 } 341 }
352 catch (Exception e) 342 catch (Exception e)
353 { 343 {
354 m_log.DebugFormat("[PROFILES_DATA]" + 344 m_log.Error("[PROFILES_DATA]: GetAvatarPicks exception ", e);
355 ": GetAvatarPicks exception {0}", e.Message);
356 } 345 }
346
357 return data; 347 return data;
358 } 348 }
359 349
360 public UserProfilePick GetPickInfo(UUID avatarId, UUID pickId) 350 public UserProfilePick GetPickInfo(UUID avatarId, UUID pickId)
361 { 351 {
362 string query = string.Empty; 352 string query = string.Empty;
363 UserProfilePick pick = new UserProfilePick(); 353 UserProfilePick pick = new UserProfilePick();
364 354
365 query += "SELECT * FROM userpicks WHERE "; 355 query += "SELECT * FROM userpicks WHERE ";
366 query += "creatoruuid = :CreatorId AND "; 356 query += "creatoruuid = :CreatorId AND ";
367 query += "pickuuid = :PickId"; 357 query += "pickuuid = :PickId";
368 358
369 try 359 try
370 { 360 {
371 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) 361 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString))
@@ -373,32 +363,32 @@ namespace OpenSim.Data.PGSQL
373 dbcon.Open(); 363 dbcon.Open();
374 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) 364 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon))
375 { 365 {
376 cmd.Parameters.AddWithValue("CreatorId", avatarId.ToString()); 366 cmd.Parameters.Add(m_database.CreateParameter("CreatorId", avatarId));
377 cmd.Parameters.AddWithValue("PickId", pickId.ToString()); 367 cmd.Parameters.Add(m_database.CreateParameter("PickId", pickId));
378 368
379 using (NpgsqlDataReader reader = cmd.ExecuteReader()) 369 using (NpgsqlDataReader reader = cmd.ExecuteReader())
380 { 370 {
381 if(reader.HasRows) 371 if (reader.HasRows)
382 { 372 {
383 reader.Read(); 373 reader.Read();
384 374
385 string description = (string)reader["description"]; 375 string description = (string)reader["description"];
386 376
387 if (string.IsNullOrEmpty(description)) 377 if (string.IsNullOrEmpty(description))
388 description = "No description given."; 378 description = "No description given.";
389 379
390 UUID.TryParse((string)reader["pickuuid"], out pick.PickId); 380 pick.PickId = DBGuid.FromDB(reader["pickuuid"]);
391 UUID.TryParse((string)reader["creatoruuid"], out pick.CreatorId); 381 pick.CreatorId = DBGuid.FromDB(reader["creatoruuid"]);
392 UUID.TryParse((string)reader["parceluuid"], out pick.ParcelId); 382 pick.ParcelId = DBGuid.FromDB(reader["parceluuid"]);
393 UUID.TryParse((string)reader["snapshotuuid"], out pick.SnapshotId); 383 pick.SnapshotId = DBGuid.FromDB(reader["snapshotuuid"]);
394 pick.GlobalPos = (string)reader["posglobal"]; 384 pick.GlobalPos = (string)reader["posglobal"].ToString();
395 bool.TryParse((string)reader["toppick"], out pick.TopPick); 385 pick.TopPick = Convert.ToBoolean(reader["toppick"]);
396 bool.TryParse((string)reader["enabled"], out pick.Enabled); 386 pick.Enabled = Convert.ToBoolean(reader["enabled"]);
397 pick.Name = (string)reader["name"]; 387 pick.Name = reader["name"].ToString();
398 pick.Desc = description; 388 pick.Desc = reader["description"].ToString();
399 pick.User = (string)reader["user"]; 389 pick.ParcelName = reader["user"].ToString();
400 pick.OriginalName = (string)reader["originalname"]; 390 pick.OriginalName = reader["originalname"].ToString();
401 pick.SimName = (string)reader["simname"]; 391 pick.SimName = reader["simname"].ToString();
402 pick.SortOrder = (int)reader["sortorder"]; 392 pick.SortOrder = (int)reader["sortorder"];
403 } 393 }
404 } 394 }
@@ -408,30 +398,32 @@ namespace OpenSim.Data.PGSQL
408 } 398 }
409 catch (Exception e) 399 catch (Exception e)
410 { 400 {
411 m_log.DebugFormat("[PROFILES_DATA]" + 401 m_log.Error("[PROFILES_DATA]: GetPickInfo exception ", e);
412 ": GetPickInfo exception {0}", e.Message);
413 } 402 }
403
414 return pick; 404 return pick;
415 } 405 }
416 406
417 public bool UpdatePicksRecord(UserProfilePick pick) 407 public bool UpdatePicksRecord(UserProfilePick pick)
418 { 408 {
419 string query = string.Empty; 409 string query = string.Empty;
420 410
421 query = @"INSERT INTO userpicks VALUES ( :PickId, :CreatorId, :TopPick, :ParcelId,:Name, :Desc, :SnapshotId,:User, 411
422 :Original, :SimName, :GlobalPos, :SortOrder, :Enabled) 412 query = @"WITH upsert AS (
423 where not exists ( select pickid from userpicks where pickid = :pickid); 413 UPDATE userpicks SET
424 414 pickuuid = :PickId, creatoruuid = :CreatorId, toppick = :TopPick, parceluuid = :ParcelId,
425 Update userpicks 415 name = :Name, description = :Desc, snapshotuuid = :SnapshotId, ""user"" = :User,
426 set parceluuid = :ParcelId, 416 originalname = :Original, simname = :SimName, posglobal = :GlobalPos,
427 name = :Name, 417 sortorder = :SortOrder, enabled = :Enabled
428 description = :Desc, 418 RETURNING * )
429 snapshotuuid = :SnapshotId, 419 INSERT INTO userpicks (pickuuid,creatoruuid,toppick,parceluuid,name,description,
430 pickuuid = :PickId, 420 snapshotuuid,""user"",originalname,simname,posglobal,sortorder,enabled)
431 posglobal = :GlobalPos 421 SELECT
432 where pickid = :PickId; 422 :PickId,:CreatorId,:TopPick,:ParcelId,:Name,:Desc,:SnapshotId,:User,
433 "; 423 :Original,:SimName,:GlobalPos,:SortOrder,:Enabled
434 424 WHERE NOT EXISTS (
425 SELECT * FROM upsert )";
426
435 try 427 try
436 { 428 {
437 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) 429 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString))
@@ -439,74 +431,76 @@ namespace OpenSim.Data.PGSQL
439 dbcon.Open(); 431 dbcon.Open();
440 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) 432 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon))
441 { 433 {
442 cmd.Parameters.AddWithValue("PickId", pick.PickId.ToString()); 434 cmd.Parameters.Add(m_database.CreateParameter("PickId", pick.PickId));
443 cmd.Parameters.AddWithValue("CreatorId", pick.CreatorId.ToString()); 435 cmd.Parameters.Add(m_database.CreateParameter("CreatorId", pick.CreatorId));
444 cmd.Parameters.AddWithValue("TopPick", pick.TopPick.ToString()); 436 cmd.Parameters.Add(m_database.CreateParameter("TopPick", pick.TopPick));
445 cmd.Parameters.AddWithValue("ParcelId", pick.ParcelId.ToString()); 437 cmd.Parameters.Add(m_database.CreateParameter("ParcelId", pick.ParcelId));
446 cmd.Parameters.AddWithValue("Name", pick.Name.ToString()); 438 cmd.Parameters.Add(m_database.CreateParameter("Name", pick.Name));
447 cmd.Parameters.AddWithValue("Desc", pick.Desc.ToString()); 439 cmd.Parameters.Add(m_database.CreateParameter("Desc", pick.Desc));
448 cmd.Parameters.AddWithValue("SnapshotId", pick.SnapshotId.ToString()); 440 cmd.Parameters.Add(m_database.CreateParameter("SnapshotId", pick.SnapshotId));
449 cmd.Parameters.AddWithValue("User", pick.User.ToString()); 441 cmd.Parameters.Add(m_database.CreateParameter("User", pick.ParcelName));
450 cmd.Parameters.AddWithValue("Original", pick.OriginalName.ToString()); 442 cmd.Parameters.Add(m_database.CreateParameter("Original", pick.OriginalName));
451 cmd.Parameters.AddWithValue("SimName",pick.SimName.ToString()); 443 cmd.Parameters.Add(m_database.CreateParameter("SimName", pick.SimName));
452 cmd.Parameters.AddWithValue("GlobalPos", pick.GlobalPos); 444 cmd.Parameters.Add(m_database.CreateParameter("GlobalPos", pick.GlobalPos));
453 cmd.Parameters.AddWithValue("SortOrder", pick.SortOrder.ToString ()); 445 cmd.Parameters.Add(m_database.CreateParameter("SortOrder", pick.SortOrder));
454 cmd.Parameters.AddWithValue("Enabled", pick.Enabled.ToString()); 446 cmd.Parameters.Add(m_database.CreateParameter("Enabled", pick.Enabled));
455 447
456 cmd.ExecuteNonQuery(); 448 cmd.ExecuteNonQuery();
457 } 449 }
458 } 450 }
459 } 451 }
460 catch (Exception e) 452 catch (Exception e)
461 { 453 {
462 m_log.DebugFormat("[PROFILES_DATA]" + 454 m_log.Error("[PROFILES_DATA]: UpdateAvatarNotes exception ", e);
463 ": UpdateAvatarNotes exception {0}", e.Message);
464 return false; 455 return false;
465 } 456 }
457
466 return true; 458 return true;
467 } 459 }
468 460
469 public bool DeletePicksRecord(UUID pickId) 461 public bool DeletePicksRecord(UUID pickId)
470 { 462 {
471 string query = string.Empty; 463 string query = string.Empty;
472 464
473 query += "DELETE FROM userpicks WHERE "; 465 query += "DELETE FROM userpicks WHERE ";
474 query += "pickuuid = :PickId"; 466 query += "pickuuid = :PickId";
475 467
476 try 468 try
477 { 469 {
478 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) 470 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString))
479 { 471 {
480 dbcon.Open(); 472 dbcon.Open();
481 473
482 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) 474 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon))
483 { 475 {
484 cmd.Parameters.AddWithValue("PickId", pickId.ToString()); 476 cmd.Parameters.Add(m_database.CreateParameter("PickId", pickId));
485 477
486 cmd.ExecuteNonQuery(); 478 cmd.ExecuteNonQuery();
487 } 479 }
488 } 480 }
489 } 481 }
490 catch (Exception e) 482 catch (Exception e)
491 { 483 {
492 m_log.DebugFormat("[PROFILES_DATA]" + 484 m_log.Error("[PROFILES_DATA]: DeleteUserPickRecord exception ", e);
493 ": DeleteUserPickRecord exception {0}", e.Message);
494 return false; 485 return false;
495 } 486 }
487
496 return true; 488 return true;
497 } 489 }
490
498 #endregion Picks Queries 491 #endregion Picks Queries
499 492
500 #region Avatar Notes Queries 493 #region Avatar Notes Queries
494
501 public bool GetAvatarNotes(ref UserProfileNotes notes) 495 public bool GetAvatarNotes(ref UserProfileNotes notes)
502 { // WIP 496 { // WIP
503 string query = string.Empty; 497 string query = string.Empty;
504 498
505 query += "SELECT \"notes\" FROM usernotes WHERE "; 499 query += "SELECT notes FROM usernotes WHERE ";
506 query += "useruuid = :Id AND "; 500 query += "useruuid = :Id AND ";
507 query += "targetuuid = :TargetId"; 501 query += "targetuuid = :TargetId";
508 OSDArray data = new OSDArray(); 502 OSDArray data = new OSDArray();
509 503
510 try 504 try
511 { 505 {
512 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) 506 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString))
@@ -514,12 +508,12 @@ namespace OpenSim.Data.PGSQL
514 dbcon.Open(); 508 dbcon.Open();
515 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) 509 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon))
516 { 510 {
517 cmd.Parameters.AddWithValue("Id", notes.UserId.ToString()); 511 cmd.Parameters.Add(m_database.CreateParameter("Id", notes.UserId));
518 cmd.Parameters.AddWithValue("TargetId", notes.TargetId.ToString()); 512 cmd.Parameters.Add(m_database.CreateParameter("TargetId", notes.TargetId));
519 513
520 using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) 514 using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
521 { 515 {
522 if(reader.HasRows) 516 if (reader.HasRows)
523 { 517 {
524 reader.Read(); 518 reader.Read();
525 notes.Notes = OSD.FromString((string)reader["notes"]); 519 notes.Notes = OSD.FromString((string)reader["notes"]);
@@ -530,18 +524,18 @@ namespace OpenSim.Data.PGSQL
530 } 524 }
531 catch (Exception e) 525 catch (Exception e)
532 { 526 {
533 m_log.DebugFormat("[PROFILES_DATA]" + 527 m_log.Error("[PROFILES_DATA]: GetAvatarNotes exception ", e);
534 ": GetAvatarNotes exception {0}", e.Message);
535 } 528 }
529
536 return true; 530 return true;
537 } 531 }
538 532
539 public bool UpdateAvatarNotes(ref UserProfileNotes note, ref string result) 533 public bool UpdateAvatarNotes(ref UserProfileNotes note, ref string result)
540 { 534 {
541 string query = string.Empty; 535 string query = string.Empty;
542 bool remove; 536 bool remove;
543 537
544 if(string.IsNullOrEmpty(note.Notes)) 538 if (string.IsNullOrEmpty(note.Notes))
545 { 539 {
546 remove = true; 540 remove = true;
547 query += "DELETE FROM usernotes WHERE "; 541 query += "DELETE FROM usernotes WHERE ";
@@ -551,16 +545,16 @@ namespace OpenSim.Data.PGSQL
551 else 545 else
552 { 546 {
553 remove = false; 547 remove = false;
554 query = @"INSERT INTO usernotes VALUES ( :UserId, :TargetId, :Notes ) 548
555 where not exists ( Select useruuid from usernotes where useruuid = :UserId and targetuuid = :TargetId ); 549 query = @"WITH upsert AS (
556 550 UPDATE usernotes SET notes = :Notes, useruuid = :UserId, targetuuid = :TargetId RETURNING * )
557 update usernotes 551 INSERT INTO usernotes (notes,useruuid,targetuuid)
558 set notes = :Notes 552 SELECT :Notes,:UserId,:TargetId
559 where useruuid = :UserId 553 WHERE NOT EXISTS (
560 and targetuuid = :TargetId; 554 SELECT * FROM upsert
561 "; 555 )";
562 } 556 }
563 557
564 try 558 try
565 { 559 {
566 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) 560 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString))
@@ -568,34 +562,36 @@ namespace OpenSim.Data.PGSQL
568 dbcon.Open(); 562 dbcon.Open();
569 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) 563 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon))
570 { 564 {
571 if(!remove) 565 if (!remove)
572 cmd.Parameters.AddWithValue("Notes", note.Notes); 566 cmd.Parameters.Add(m_database.CreateParameter("Notes", note.Notes));
573 cmd.Parameters.AddWithValue("TargetId", note.TargetId.ToString ()); 567
574 cmd.Parameters.AddWithValue("UserId", note.UserId.ToString()); 568 cmd.Parameters.Add(m_database.CreateParameter("TargetId", note.TargetId));
575 569 cmd.Parameters.Add(m_database.CreateParameter("UserId", note.UserId));
570
576 cmd.ExecuteNonQuery(); 571 cmd.ExecuteNonQuery();
577 } 572 }
578 } 573 }
579 } 574 }
580 catch (Exception e) 575 catch (Exception e)
581 { 576 {
582 m_log.DebugFormat("[PROFILES_DATA]" + 577 m_log.Error("[PROFILES_DATA]: UpdateAvatarNotes exception ", e);
583 ": UpdateAvatarNotes exception {0}", e.Message);
584 return false; 578 return false;
585 } 579 }
580
586 return true; 581 return true;
587
588 } 582 }
583
589 #endregion Avatar Notes Queries 584 #endregion Avatar Notes Queries
590 585
591 #region Avatar Properties 586 #region Avatar Properties
587
592 public bool GetAvatarProperties(ref UserProfileProperties props, ref string result) 588 public bool GetAvatarProperties(ref UserProfileProperties props, ref string result)
593 { 589 {
594 string query = string.Empty; 590 string query = string.Empty;
595 591
596 query += "SELECT * FROM userprofile WHERE "; 592 query += "SELECT * FROM userprofile WHERE ";
597 query += "useruuid = :Id"; 593 query += "useruuid = :Id";
598 594
599 try 595 try
600 { 596 {
601 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) 597 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString))
@@ -603,21 +599,21 @@ namespace OpenSim.Data.PGSQL
603 dbcon.Open(); 599 dbcon.Open();
604 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) 600 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon))
605 { 601 {
606 cmd.Parameters.AddWithValue("Id", props.UserId.ToString()); 602 cmd.Parameters.Add(m_database.CreateParameter("Id", props.UserId));
607 603
608 using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) 604 using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
609 { 605 {
610 if(reader.HasRows) 606 if (reader.HasRows)
611 { 607 {
612 m_log.DebugFormat("[PROFILES_DATA]" + 608 // m_log.DebugFormat("[PROFILES_DATA]" +
613 ": Getting data for {0}.", props.UserId); 609 // ": Getting data for {0}.", props.UserId);
614 reader.Read(); 610 reader.Read();
615 props.WebUrl = (string)reader["profileURL"]; 611 props.WebUrl = (string)reader["profileURL"].ToString();
616 UUID.TryParse((string)reader["profileImage"], out props.ImageId); 612 props.ImageId = DBGuid.FromDB(reader["profileImage"]);
617 props.AboutText = (string)reader["profileAboutText"]; 613 props.AboutText = (string)reader["profileAboutText"];
618 UUID.TryParse((string)reader["profileFirstImage"], out props.FirstLifeImageId); 614 props.FirstLifeImageId = DBGuid.FromDB(reader["profileFirstImage"]);
619 props.FirstLifeText = (string)reader["profileFirstText"]; 615 props.FirstLifeText = (string)reader["profileFirstText"];
620 UUID.TryParse((string)reader["profilePartner"], out props.PartnerId); 616 props.PartnerId = DBGuid.FromDB(reader["profilePartner"]);
621 props.WantToMask = (int)reader["profileWantToMask"]; 617 props.WantToMask = (int)reader["profileWantToMask"];
622 props.WantToText = (string)reader["profileWantToText"]; 618 props.WantToText = (string)reader["profileWantToText"];
623 props.SkillsMask = (int)reader["profileSkillsMask"]; 619 props.SkillsMask = (int)reader["profileSkillsMask"];
@@ -626,9 +622,9 @@ namespace OpenSim.Data.PGSQL
626 } 622 }
627 else 623 else
628 { 624 {
629 m_log.DebugFormat("[PROFILES_DATA]" + 625 //m_log.DebugFormat("[PROFILES_DATA]" +
630 ": No data for {0}", props.UserId); 626 // ": No data for {0}", props.UserId);
631 627
632 props.WebUrl = string.Empty; 628 props.WebUrl = string.Empty;
633 props.ImageId = UUID.Zero; 629 props.ImageId = UUID.Zero;
634 props.AboutText = string.Empty; 630 props.AboutText = string.Empty;
@@ -645,19 +641,19 @@ namespace OpenSim.Data.PGSQL
645 641
646 query = "INSERT INTO userprofile ("; 642 query = "INSERT INTO userprofile (";
647 query += "useruuid, "; 643 query += "useruuid, ";
648 query += "profilePartner, "; 644 query += "\"profilePartner\", ";
649 query += "profileAllowPublish, "; 645 query += "\"profileAllowPublish\", ";
650 query += "profileMaturePublish, "; 646 query += "\"profileMaturePublish\", ";
651 query += "profileURL, "; 647 query += "\"profileURL\", ";
652 query += "profileWantToMask, "; 648 query += "\"profileWantToMask\", ";
653 query += "profileWantToText, "; 649 query += "\"profileWantToText\", ";
654 query += "profileSkillsMask, "; 650 query += "\"profileSkillsMask\", ";
655 query += "profileSkillsText, "; 651 query += "\"profileSkillsText\", ";
656 query += "profileLanguages, "; 652 query += "\"profileLanguages\", ";
657 query += "profileImage, "; 653 query += "\"profileImage\", ";
658 query += "profileAboutText, "; 654 query += "\"profileAboutText\", ";
659 query += "profileFirstImage, "; 655 query += "\"profileFirstImage\", ";
660 query += "profileFirstText) VALUES ("; 656 query += "\"profileFirstText\") VALUES (";
661 query += ":userId, "; 657 query += ":userId, ";
662 query += ":profilePartner, "; 658 query += ":profilePartner, ";
663 query += ":profileAllowPublish, "; 659 query += ":profileAllowPublish, ";
@@ -678,20 +674,23 @@ namespace OpenSim.Data.PGSQL
678 674
679 using (NpgsqlCommand put = new NpgsqlCommand(query, dbcon)) 675 using (NpgsqlCommand put = new NpgsqlCommand(query, dbcon))
680 { 676 {
681 put.Parameters.AddWithValue("userId", props.UserId.ToString()); 677 //m_log.DebugFormat("[PROFILES_DATA]" +
682 put.Parameters.AddWithValue("profilePartner", props.PartnerId.ToString()); 678 // ": Adding new data for {0}", props.UserId);
683 put.Parameters.AddWithValue("profileAllowPublish", props.PublishProfile); 679
684 put.Parameters.AddWithValue("profileMaturePublish", props.PublishMature); 680 put.Parameters.Add(m_database.CreateParameter("userId", props.UserId));
685 put.Parameters.AddWithValue("profileURL", props.WebUrl); 681 put.Parameters.Add(m_database.CreateParameter("profilePartner", props.PartnerId));
686 put.Parameters.AddWithValue("profileWantToMask", props.WantToMask); 682 put.Parameters.Add(m_database.CreateParameter("profileAllowPublish", props.PublishProfile));
687 put.Parameters.AddWithValue("profileWantToText", props.WantToText); 683 put.Parameters.Add(m_database.CreateParameter("profileMaturePublish", props.PublishMature));
688 put.Parameters.AddWithValue("profileSkillsMask", props.SkillsMask); 684 put.Parameters.Add(m_database.CreateParameter("profileURL", props.WebUrl));
689 put.Parameters.AddWithValue("profileSkillsText", props.SkillsText); 685 put.Parameters.Add(m_database.CreateParameter("profileWantToMask", props.WantToMask));
690 put.Parameters.AddWithValue("profileLanguages", props.Language); 686 put.Parameters.Add(m_database.CreateParameter("profileWantToText", props.WantToText));
691 put.Parameters.AddWithValue("profileImage", props.ImageId.ToString()); 687 put.Parameters.Add(m_database.CreateParameter("profileSkillsMask", props.SkillsMask));
692 put.Parameters.AddWithValue("profileAboutText", props.AboutText); 688 put.Parameters.Add(m_database.CreateParameter("profileSkillsText", props.SkillsText));
693 put.Parameters.AddWithValue("profileFirstImage", props.FirstLifeImageId.ToString()); 689 put.Parameters.Add(m_database.CreateParameter("profileLanguages", props.Language));
694 put.Parameters.AddWithValue("profileFirstText", props.FirstLifeText); 690 put.Parameters.Add(m_database.CreateParameter("profileImage", props.ImageId));
691 put.Parameters.Add(m_database.CreateParameter("profileAboutText", props.AboutText));
692 put.Parameters.Add(m_database.CreateParameter("profileFirstImage", props.FirstLifeImageId));
693 put.Parameters.Add(m_database.CreateParameter("profileFirstText", props.FirstLifeText));
695 694
696 put.ExecuteNonQuery(); 695 put.ExecuteNonQuery();
697 } 696 }
@@ -702,26 +701,26 @@ namespace OpenSim.Data.PGSQL
702 } 701 }
703 catch (Exception e) 702 catch (Exception e)
704 { 703 {
705 m_log.DebugFormat("[PROFILES_DATA]" + 704 m_log.Error("[PROFILES_DATA]: GetAvatarProperties exception ", e);
706 ": Requst properties exception {0}", e.Message);
707 result = e.Message; 705 result = e.Message;
708 return false; 706 return false;
709 } 707 }
708
710 return true; 709 return true;
711 } 710 }
712 711
713 public bool UpdateAvatarProperties(ref UserProfileProperties props, ref string result) 712 public bool UpdateAvatarProperties(ref UserProfileProperties props, ref string result)
714 { 713 {
715 string query = string.Empty; 714 string query = string.Empty;
716 715
717 query += "UPDATE userprofile SET "; 716 query += "UPDATE userprofile SET ";
718 query += "profileURL=:profileURL, "; 717 query += "\"profileURL\"=:profileURL, ";
719 query += "profileImage=:image, "; 718 query += "\"profileImage\"=:image, ";
720 query += "profileAboutText=:abouttext,"; 719 query += "\"profileAboutText\"=:abouttext,";
721 query += "profileFirstImage=:firstlifeimage,"; 720 query += "\"profileFirstImage\"=:firstlifeimage,";
722 query += "profileFirstText=:firstlifetext "; 721 query += "\"profileFirstText\"=:firstlifetext ";
723 query += "WHERE useruuid=:uuid"; 722 query += "WHERE \"useruuid\"=:uuid";
724 723
725 try 724 try
726 { 725 {
727 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) 726 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString))
@@ -729,41 +728,42 @@ namespace OpenSim.Data.PGSQL
729 dbcon.Open(); 728 dbcon.Open();
730 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) 729 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon))
731 { 730 {
732 cmd.Parameters.AddWithValue("profileURL", props.WebUrl); 731 cmd.Parameters.Add(m_database.CreateParameter("profileURL", props.WebUrl));
733 cmd.Parameters.AddWithValue("image", props.ImageId.ToString()); 732 cmd.Parameters.Add(m_database.CreateParameter("image", props.ImageId));
734 cmd.Parameters.AddWithValue("abouttext", props.AboutText); 733 cmd.Parameters.Add(m_database.CreateParameter("abouttext", props.AboutText));
735 cmd.Parameters.AddWithValue("firstlifeimage", props.FirstLifeImageId.ToString()); 734 cmd.Parameters.Add(m_database.CreateParameter("firstlifeimage", props.FirstLifeImageId));
736 cmd.Parameters.AddWithValue("firstlifetext", props.FirstLifeText); 735 cmd.Parameters.Add(m_database.CreateParameter("firstlifetext", props.FirstLifeText));
737 cmd.Parameters.AddWithValue("uuid", props.UserId.ToString()); 736 cmd.Parameters.Add(m_database.CreateParameter("uuid", props.UserId));
738 737
739 cmd.ExecuteNonQuery(); 738 cmd.ExecuteNonQuery();
740 } 739 }
741 } 740 }
742 } 741 }
743 catch (Exception e) 742 catch (Exception e)
744 { 743 {
745 m_log.DebugFormat("[PROFILES_DATA]" + 744 m_log.Error("[PROFILES_DATA]: AgentPropertiesUpdate exception ", e);
746 ": AgentPropertiesUpdate exception {0}", e.Message);
747
748 return false; 745 return false;
749 } 746 }
747
750 return true; 748 return true;
751 } 749 }
750
752 #endregion Avatar Properties 751 #endregion Avatar Properties
753 752
754 #region Avatar Interests 753 #region Avatar Interests
754
755 public bool UpdateAvatarInterests(UserProfileProperties up, ref string result) 755 public bool UpdateAvatarInterests(UserProfileProperties up, ref string result)
756 { 756 {
757 string query = string.Empty; 757 string query = string.Empty;
758 758
759 query += "UPDATE userprofile SET "; 759 query += "UPDATE userprofile SET ";
760 query += "profileWantToMask=:WantMask, "; 760 query += "\"profileWantToMask\"=:WantMask, ";
761 query += "profileWantToText=:WantText,"; 761 query += "\"profileWantToText\"=:WantText,";
762 query += "profileSkillsMask=:SkillsMask,"; 762 query += "\"profileSkillsMask\"=:SkillsMask,";
763 query += "profileSkillsText=:SkillsText, "; 763 query += "\"profileSkillsText\"=:SkillsText, ";
764 query += "profileLanguages=:Languages "; 764 query += "\"profileLanguages\"=:Languages ";
765 query += "WHERE useruuid=:uuid"; 765 query += "WHERE \"useruuid\"=:uuid";
766 766
767 try 767 try
768 { 768 {
769 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) 769 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString))
@@ -771,26 +771,27 @@ namespace OpenSim.Data.PGSQL
771 dbcon.Open(); 771 dbcon.Open();
772 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) 772 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon))
773 { 773 {
774 cmd.Parameters.AddWithValue("WantMask", up.WantToMask); 774 cmd.Parameters.Add(m_database.CreateParameter("WantMask", up.WantToMask));
775 cmd.Parameters.AddWithValue("WantText", up.WantToText); 775 cmd.Parameters.Add(m_database.CreateParameter("WantText", up.WantToText));
776 cmd.Parameters.AddWithValue("SkillsMask", up.SkillsMask); 776 cmd.Parameters.Add(m_database.CreateParameter("SkillsMask", up.SkillsMask));
777 cmd.Parameters.AddWithValue("SkillsText", up.SkillsText); 777 cmd.Parameters.Add(m_database.CreateParameter("SkillsText", up.SkillsText));
778 cmd.Parameters.AddWithValue("Languages", up.Language); 778 cmd.Parameters.Add(m_database.CreateParameter("Languages", up.Language));
779 cmd.Parameters.AddWithValue("uuid", up.UserId.ToString()); 779 cmd.Parameters.Add(m_database.CreateParameter("uuid", up.UserId));
780 780
781 cmd.ExecuteNonQuery(); 781 cmd.ExecuteNonQuery();
782 } 782 }
783 } 783 }
784 } 784 }
785 catch (Exception e) 785 catch (Exception e)
786 { 786 {
787 m_log.DebugFormat("[PROFILES_DATA]" + 787 m_log.Error("[PROFILES_DATA]: UpdateAvatarInterests exception ", e);
788 ": AgentInterestsUpdate exception {0}", e.Message);
789 result = e.Message; 788 result = e.Message;
790 return false; 789 return false;
791 } 790 }
791
792 return true; 792 return true;
793 } 793 }
794
794 #endregion Avatar Interests 795 #endregion Avatar Interests
795 796
796 public OSDArray GetUserImageAssets(UUID avatarId) 797 public OSDArray GetUserImageAssets(UUID avatarId)
@@ -798,26 +799,23 @@ namespace OpenSim.Data.PGSQL
798 OSDArray data = new OSDArray(); 799 OSDArray data = new OSDArray();
799 string query = "SELECT \"snapshotuuid\" FROM {0} WHERE \"creatoruuid\" = :Id"; 800 string query = "SELECT \"snapshotuuid\" FROM {0} WHERE \"creatoruuid\" = :Id";
800 801
801 // Get classified image assets
802
803
804 try 802 try
805 { 803 {
806 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) 804 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString))
807 { 805 {
808 dbcon.Open(); 806 dbcon.Open();
809 807
810 using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format (query,"\"classifieds\""), dbcon)) 808 using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format(query, "\"classifieds\""), dbcon))
811 { 809 {
812 cmd.Parameters.AddWithValue("Id", avatarId.ToString()); 810 cmd.Parameters.Add(m_database.CreateParameter("Id", avatarId));
813 811
814 using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) 812 using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
815 { 813 {
816 if(reader.HasRows) 814 if (reader.HasRows)
817 { 815 {
818 while (reader.Read()) 816 while (reader.Read())
819 { 817 {
820 data.Add(new OSDString((string)reader["snapshotuuid"].ToString ())); 818 data.Add(new OSDString(reader["snapshotuuid"].ToString()));
821 } 819 }
822 } 820 }
823 } 821 }
@@ -826,39 +824,39 @@ namespace OpenSim.Data.PGSQL
826 dbcon.Close(); 824 dbcon.Close();
827 dbcon.Open(); 825 dbcon.Open();
828 826
829 using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format (query,"\"userpicks\""), dbcon)) 827 using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format(query, "\"userpicks\""), dbcon))
830 { 828 {
831 cmd.Parameters.AddWithValue("Id", avatarId.ToString()); 829 cmd.Parameters.Add(m_database.CreateParameter("Id", avatarId));
832 830
833 using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) 831 using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
834 { 832 {
835 if(reader.HasRows) 833 if (reader.HasRows)
836 { 834 {
837 while (reader.Read()) 835 while (reader.Read())
838 { 836 {
839 data.Add(new OSDString((string)reader["snapshotuuid"].ToString ())); 837 data.Add(new OSDString(reader["snapshotuuid"].ToString()));
840 } 838 }
841 } 839 }
842 } 840 }
843 } 841 }
844 842
845 dbcon.Close(); 843 dbcon.Close();
846 dbcon.Open(); 844 dbcon.Open();
847 845
848 query = "SELECT \"profileImage\", \"profileFirstImage\" FROM \"userprofile\" WHERE \"useruuid\" = :Id"; 846 query = "SELECT \"profileImage\", \"profileFirstImage\" FROM \"userprofile\" WHERE \"useruuid\" = :Id";
849 847
850 using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format (query,"\"userpicks\""), dbcon)) 848 using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format(query, "\"userpicks\""), dbcon))
851 { 849 {
852 cmd.Parameters.AddWithValue("Id", avatarId.ToString()); 850 cmd.Parameters.Add(m_database.CreateParameter("Id", avatarId));
853 851
854 using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) 852 using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
855 { 853 {
856 if(reader.HasRows) 854 if (reader.HasRows)
857 { 855 {
858 while (reader.Read()) 856 while (reader.Read())
859 { 857 {
860 data.Add(new OSDString((string)reader["profileImage"].ToString ())); 858 data.Add(new OSDString(reader["profileImage"].ToString()));
861 data.Add(new OSDString((string)reader["profileFirstImage"].ToString ())); 859 data.Add(new OSDString(reader["profileFirstImage"].ToString()));
862 } 860 }
863 } 861 }
864 } 862 }
@@ -867,23 +865,24 @@ namespace OpenSim.Data.PGSQL
867 } 865 }
868 catch (Exception e) 866 catch (Exception e)
869 { 867 {
870 m_log.DebugFormat("[PROFILES_DATA]" + 868 m_log.Error("[PROFILES_DATA]: GetUserImageAssets exception ", e);
871 ": GetAvatarNotes exception {0}", e.Message);
872 } 869 }
870
873 return data; 871 return data;
874 } 872 }
875 873
876 #region User Preferences 874 #region User Preferences
877 public OSDArray GetUserPreferences(UUID avatarId) 875
876 public bool GetUserPreferences(ref UserPreferences pref, ref string result)
878 { 877 {
879 string query = string.Empty; 878 string query = string.Empty;
880 879
881 query += "SELECT imviaemail,visible,email FROM "; 880 query += "SELECT imviaemail::VARCHAR,visible::VARCHAR,email FROM ";
882 query += "usersettings WHERE "; 881 query += "usersettings WHERE ";
883 query += "useruuid = :Id"; 882 query += "useruuid = :Id";
884 883
885 OSDArray data = new OSDArray(); 884 OSDArray data = new OSDArray();
886 885
887 try 886 try
888 { 887 {
889 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) 888 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString))
@@ -891,31 +890,26 @@ namespace OpenSim.Data.PGSQL
891 dbcon.Open(); 890 dbcon.Open();
892 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) 891 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon))
893 { 892 {
894 cmd.Parameters.AddWithValue("Id", avatarId.ToString()); 893 cmd.Parameters.Add(m_database.CreateParameter("Id", pref.UserId));
895 894
896 using (NpgsqlDataReader reader = cmd.ExecuteReader()) 895 using (NpgsqlDataReader reader = cmd.ExecuteReader())
897 { 896 {
898 if(reader.HasRows) 897 if (reader.HasRows)
899 { 898 {
900 reader.Read(); 899 reader.Read();
901 OSDMap record = new OSDMap(); 900 bool.TryParse((string)reader["imviaemail"], out pref.IMViaEmail);
902 901 bool.TryParse((string)reader["visible"], out pref.Visible);
903 record.Add("imviaemail",OSD.FromString((string)reader["imviaemail"])); 902 pref.EMail = (string)reader["email"];
904 record.Add("visible",OSD.FromString((string)reader["visible"]));
905 record.Add("email",OSD.FromString((string)reader["email"]));
906 data.Add(record);
907 } 903 }
908 else 904 else
909 { 905 {
910 using (NpgsqlCommand put = new NpgsqlCommand(query, dbcon)) 906 using (NpgsqlCommand put = new NpgsqlCommand(query, dbcon))
911 { 907 {
908 put.Parameters.Add(m_database.CreateParameter("Id", pref.UserId));
912 query = "INSERT INTO usersettings VALUES "; 909 query = "INSERT INTO usersettings VALUES ";
913 query += "(:Id,'false','false', '')"; 910 query += "(:Id,'false','false', '')";
914 911
915 lock(Lock) 912 put.ExecuteNonQuery();
916 {
917 put.ExecuteNonQuery();
918 }
919 } 913 }
920 } 914 }
921 } 915 }
@@ -924,21 +918,23 @@ namespace OpenSim.Data.PGSQL
924 } 918 }
925 catch (Exception e) 919 catch (Exception e)
926 { 920 {
927 m_log.DebugFormat("[PROFILES_DATA]" + 921 m_log.Error("[PROFILES_DATA]: GetUserPreferences exception ", e);
928 ": Get preferences exception {0}", e.Message); 922 result = e.Message;
929 } 923 }
930 return data; 924
925 return true;
931 } 926 }
932 927
933 public bool UpdateUserPreferences(bool emailIm, bool visible, UUID avatarId ) 928 public bool UpdateUserPreferences(ref UserPreferences pref, ref string result)
934 { 929 {
935 string query = string.Empty; 930 string query = string.Empty;
936 931
937 query += "UPDATE userpsettings SET "; 932 query += "UPDATE usersettings SET ";
938 query += "imviaemail=:ImViaEmail, "; 933 query += "imviaemail=:ImViaEmail, ";
939 query += "visible=:Visible,"; 934 query += "visible=:Visible, ";
935 query += "email=:Email ";
940 query += "WHERE useruuid=:uuid"; 936 query += "WHERE useruuid=:uuid";
941 937
942 try 938 try
943 { 939 {
944 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) 940 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString))
@@ -946,36 +942,37 @@ namespace OpenSim.Data.PGSQL
946 dbcon.Open(); 942 dbcon.Open();
947 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) 943 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon))
948 { 944 {
949 cmd.Parameters.AddWithValue("ImViaEmail", emailIm.ToString().ToLower ()); 945 cmd.Parameters.Add(m_database.CreateParameter("ImViaEmail", pref.IMViaEmail));
950 cmd.Parameters.AddWithValue("WantText", visible.ToString().ToLower ()); 946 cmd.Parameters.Add(m_database.CreateParameter("Visible", pref.Visible));
951 cmd.Parameters.AddWithValue("uuid", avatarId.ToString()); 947 cmd.Parameters.Add(m_database.CreateParameter("EMail", pref.EMail.ToString().ToLower()));
952 948 cmd.Parameters.Add(m_database.CreateParameter("uuid", pref.UserId));
953 lock(Lock) 949
954 { 950 cmd.ExecuteNonQuery();
955 cmd.ExecuteNonQuery();
956 }
957 } 951 }
958 } 952 }
959 } 953 }
960 catch (Exception e) 954 catch (Exception e)
961 { 955 {
962 m_log.DebugFormat("[PROFILES_DATA]" + 956 m_log.Error("[PROFILES_DATA]: UpdateUserPreferences exception ", e);
963 ": AgentInterestsUpdate exception {0}", e.Message); 957 result = e.Message;
964 return false; 958 return false;
965 } 959 }
960
966 return true; 961 return true;
967 } 962 }
963
968 #endregion User Preferences 964 #endregion User Preferences
969 965
970 #region Integration 966 #region Integration
967
971 public bool GetUserAppData(ref UserAppData props, ref string result) 968 public bool GetUserAppData(ref UserAppData props, ref string result)
972 { 969 {
973 string query = string.Empty; 970 string query = string.Empty;
974 971
975 query += "SELECT * FROM userdata WHERE "; 972 query += "SELECT * FROM userdata WHERE ";
976 query += "\"UserId\" = :Id AND "; 973 query += "\"UserId\" = :Id AND ";
977 query += "\"TagId\" = :TagId"; 974 query += "\"TagId\" = :TagId";
978 975
979 try 976 try
980 { 977 {
981 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) 978 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString))
@@ -983,12 +980,12 @@ namespace OpenSim.Data.PGSQL
983 dbcon.Open(); 980 dbcon.Open();
984 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) 981 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon))
985 { 982 {
986 cmd.Parameters.AddWithValue("Id", props.UserId.ToString()); 983 cmd.Parameters.Add(m_database.CreateParameter("Id", props.UserId));
987 cmd.Parameters.AddWithValue (":TagId", props.TagId.ToString()); 984 cmd.Parameters.Add(m_database.CreateParameter("TagId", props.TagId));
988 985
989 using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) 986 using (NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
990 { 987 {
991 if(reader.HasRows) 988 if (reader.HasRows)
992 { 989 {
993 reader.Read(); 990 reader.Read();
994 props.DataKey = (string)reader["DataKey"]; 991 props.DataKey = (string)reader["DataKey"];
@@ -1000,19 +997,16 @@ namespace OpenSim.Data.PGSQL
1000 query += ":UserId,"; 997 query += ":UserId,";
1001 query += ":TagId,"; 998 query += ":TagId,";
1002 query += ":DataKey,"; 999 query += ":DataKey,";
1003 query += ":DataVal) "; 1000 query += ":DataVal) ";
1004 1001
1005 using (NpgsqlCommand put = new NpgsqlCommand(query, dbcon)) 1002 using (NpgsqlCommand put = new NpgsqlCommand(query, dbcon))
1006 { 1003 {
1007 put.Parameters.AddWithValue("Id", props.UserId.ToString()); 1004 put.Parameters.Add(m_database.CreateParameter("UserId", props.UserId));
1008 put.Parameters.AddWithValue("TagId", props.TagId.ToString()); 1005 put.Parameters.Add(m_database.CreateParameter("TagId", props.TagId));
1009 put.Parameters.AddWithValue("DataKey", props.DataKey.ToString()); 1006 put.Parameters.Add(m_database.CreateParameter("DataKey", props.DataKey.ToString()));
1010 put.Parameters.AddWithValue("DataVal", props.DataVal.ToString()); 1007 put.Parameters.Add(m_database.CreateParameter("DataVal", props.DataVal.ToString()));
1011 1008
1012 lock(Lock) 1009 put.ExecuteNonQuery();
1013 {
1014 put.ExecuteNonQuery();
1015 }
1016 } 1010 }
1017 } 1011 }
1018 } 1012 }
@@ -1021,25 +1015,25 @@ namespace OpenSim.Data.PGSQL
1021 } 1015 }
1022 catch (Exception e) 1016 catch (Exception e)
1023 { 1017 {
1024 m_log.DebugFormat("[PROFILES_DATA]" + 1018 m_log.Error("[PROFILES_DATA]: GetUserAppData exception ", e);
1025 ": Requst application data exception {0}", e.Message);
1026 result = e.Message; 1019 result = e.Message;
1027 return false; 1020 return false;
1028 } 1021 }
1022
1029 return true; 1023 return true;
1030 } 1024 }
1031 1025
1032 public bool SetUserAppData(UserAppData props, ref string result) 1026 public bool SetUserAppData(UserAppData props, ref string result)
1033 { 1027 {
1034 string query = string.Empty; 1028 string query = string.Empty;
1035 1029
1036 query += "UPDATE userdata SET "; 1030 query += "UPDATE userdata SET ";
1037 query += "\"TagId\" = :TagId, "; 1031 query += "\"TagId\" = :TagId, ";
1038 query += "\"DataKey\" = :DataKey, "; 1032 query += "\"DataKey\" = :DataKey, ";
1039 query += "\"DataVal\" = :DataVal WHERE "; 1033 query += "\"DataVal\" = :DataVal WHERE ";
1040 query += "\"UserId\" = :UserId AND "; 1034 query += "\"UserId\" = :UserId AND ";
1041 query += "\"TagId\" = :TagId"; 1035 query += "\"TagId\" = :TagId";
1042 1036
1043 try 1037 try
1044 { 1038 {
1045 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString)) 1039 using (NpgsqlConnection dbcon = new NpgsqlConnection(ConnectionString))
@@ -1047,27 +1041,24 @@ namespace OpenSim.Data.PGSQL
1047 dbcon.Open(); 1041 dbcon.Open();
1048 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon)) 1042 using (NpgsqlCommand cmd = new NpgsqlCommand(query, dbcon))
1049 { 1043 {
1050 cmd.Parameters.AddWithValue("UserId", props.UserId.ToString()); 1044 cmd.Parameters.Add(m_database.CreateParameter("UserId", props.UserId.ToString()));
1051 cmd.Parameters.AddWithValue("TagId", props.TagId.ToString ()); 1045 cmd.Parameters.Add(m_database.CreateParameter("TagId", props.TagId.ToString()));
1052 cmd.Parameters.AddWithValue("DataKey", props.DataKey.ToString ()); 1046 cmd.Parameters.Add(m_database.CreateParameter("DataKey", props.DataKey.ToString()));
1053 cmd.Parameters.AddWithValue("DataVal", props.DataKey.ToString ()); 1047 cmd.Parameters.Add(m_database.CreateParameter("DataVal", props.DataKey.ToString()));
1054 1048
1055 lock(Lock) 1049 cmd.ExecuteNonQuery();
1056 {
1057 cmd.ExecuteNonQuery();
1058 }
1059 } 1050 }
1060 } 1051 }
1061 } 1052 }
1062 catch (Exception e) 1053 catch (Exception e)
1063 { 1054 {
1064 m_log.DebugFormat("[PROFILES_DATA]" + 1055 m_log.Error("[PROFILES_DATA]: SetUserData exception ", e);
1065 ": SetUserData exception {0}", e.Message);
1066 return false; 1056 return false;
1067 } 1057 }
1058
1068 return true; 1059 return true;
1069 } 1060 }
1061
1070 #endregion Integration 1062 #endregion Integration
1071 } 1063 }
1072} 1064} \ No newline at end of file
1073
diff --git a/OpenSim/Data/PGSQL/PGSQLXAssetData.cs b/OpenSim/Data/PGSQL/PGSQLXAssetData.cs
index e959619..4f682f0 100644
--- a/OpenSim/Data/PGSQL/PGSQLXAssetData.cs
+++ b/OpenSim/Data/PGSQL/PGSQLXAssetData.cs
@@ -56,6 +56,7 @@ namespace OpenSim.Data.PGSQL
56 private const int DaysBetweenAccessTimeUpdates = 30; 56 private const int DaysBetweenAccessTimeUpdates = 30;
57 57
58 private bool m_enableCompression = false; 58 private bool m_enableCompression = false;
59 private PGSQLManager m_database;
59 private string m_connectionString; 60 private string m_connectionString;
60 private object m_dbLock = new object(); 61 private object m_dbLock = new object();
61 62
@@ -92,6 +93,7 @@ namespace OpenSim.Data.PGSQL
92 m_log.ErrorFormat("[PGSQL XASSETDATA]: ***********************************************************"); 93 m_log.ErrorFormat("[PGSQL XASSETDATA]: ***********************************************************");
93 94
94 m_connectionString = connect; 95 m_connectionString = connect;
96 m_database = new PGSQLManager(m_connectionString);
95 97
96 using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString)) 98 using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString))
97 { 99 {
@@ -138,12 +140,12 @@ namespace OpenSim.Data.PGSQL
138 dbcon.Open(); 140 dbcon.Open();
139 141
140 using (NpgsqlCommand cmd = new NpgsqlCommand( 142 using (NpgsqlCommand cmd = new NpgsqlCommand(
141 @"SELECT ""Name"", ""Description"", ""AccessTime"", ""AssetType"", ""Local"", ""Temporary"", ""AssetFlags"", ""CreatorID"", ""Data"" 143 @"SELECT name, description, access_time, ""AssetType"", local, temporary, asset_flags, creatorid, data
142 FROM XAssetsMeta 144 FROM XAssetsMeta
143 JOIN XAssetsData ON XAssetsMeta.Hash = XAssetsData.Hash WHERE ""ID""=:ID", 145 JOIN XAssetsData ON XAssetsMeta.hash = XAssetsData.Hash WHERE id=:ID",
144 dbcon)) 146 dbcon))
145 { 147 {
146 cmd.Parameters.AddWithValue("ID", assetID.ToString()); 148 cmd.Parameters.Add(m_database.CreateParameter("ID", assetID));
147 149
148 try 150 try
149 { 151 {
@@ -151,18 +153,23 @@ namespace OpenSim.Data.PGSQL
151 { 153 {
152 if (dbReader.Read()) 154 if (dbReader.Read())
153 { 155 {
154 asset = new AssetBase(assetID, (string)dbReader["Name"], (sbyte)dbReader["AssetType"], dbReader["CreatorID"].ToString()); 156 asset = new AssetBase(
155 asset.Data = (byte[])dbReader["Data"]; 157 assetID,
156 asset.Description = (string)dbReader["Description"]; 158 (string)dbReader["name"],
159 Convert.ToSByte(dbReader["AssetType"]),
160 dbReader["creatorid"].ToString());
157 161
158 string local = dbReader["Local"].ToString(); 162 asset.Data = (byte[])dbReader["data"];
163 asset.Description = (string)dbReader["description"];
164
165 string local = dbReader["local"].ToString();
159 if (local.Equals("1") || local.Equals("true", StringComparison.InvariantCultureIgnoreCase)) 166 if (local.Equals("1") || local.Equals("true", StringComparison.InvariantCultureIgnoreCase))
160 asset.Local = true; 167 asset.Local = true;
161 else 168 else
162 asset.Local = false; 169 asset.Local = false;
163 170
164 asset.Temporary = Convert.ToBoolean(dbReader["Temporary"]); 171 asset.Temporary = Convert.ToBoolean(dbReader["temporary"]);
165 asset.Flags = (AssetFlags)Convert.ToInt32(dbReader["AssetFlags"]); 172 asset.Flags = (AssetFlags)Convert.ToInt32(dbReader["asset_flags"]);
166 173
167 if (m_enableCompression) 174 if (m_enableCompression)
168 { 175 {
@@ -179,7 +186,7 @@ namespace OpenSim.Data.PGSQL
179 } 186 }
180 } 187 }
181 188
182 UpdateAccessTime(asset.Metadata, (int)dbReader["AccessTime"]); 189 UpdateAccessTime(asset.Metadata, (int)dbReader["access_time"]);
183 } 190 }
184 } 191 }
185 } 192 }
@@ -245,6 +252,9 @@ namespace OpenSim.Data.PGSQL
245 252
246 byte[] hash = hasher.ComputeHash(asset.Data); 253 byte[] hash = hasher.ComputeHash(asset.Data);
247 254
255 UUID asset_id;
256 UUID.TryParse(asset.ID, out asset_id);
257
248// m_log.DebugFormat( 258// m_log.DebugFormat(
249// "[XASSET DB]: Compressed data size for {0} {1}, hash {2} is {3}", 259// "[XASSET DB]: Compressed data size for {0} {1}, hash {2} is {3}",
250// asset.ID, asset.Name, hash, compressedData.Length); 260// asset.ID, asset.Name, hash, compressedData.Length);
@@ -253,31 +263,33 @@ namespace OpenSim.Data.PGSQL
253 { 263 {
254 using (NpgsqlCommand cmd = 264 using (NpgsqlCommand cmd =
255 new NpgsqlCommand( 265 new NpgsqlCommand(
256 @"insert INTO XAssetsMeta(""ID"", ""Hash"", ""Name"", ""Description"", ""AssetType"", ""Local"", ""Temporary"", ""CreateTime"", ""AccessTime"", ""AssetFlags"", ""CreatorID"") 266 @"insert INTO XAssetsMeta(id, hash, name, description, ""AssetType"", local, temporary, create_time, access_time, asset_flags, creatorid)
257 Select :ID, :Hash, :Name, :Description, :AssetType, :Local, :Temporary, :CreateTime, :AccessTime, :AssetFlags, :CreatorID 267 Select :ID, :Hash, :Name, :Description, :AssetType, :Local, :Temporary, :CreateTime, :AccessTime, :AssetFlags, :CreatorID
258 where not exists( Select ""ID"" from XAssetsMeta where ""ID"" = :ID ; 268 where not exists( Select id from XAssetsMeta where id = :ID);
259 269
260 update XAssetsMeta 270 update XAssetsMeta
261 set ""ID"" = :ID, ""Hash"" = :Hash, ""Name"" = :Name, ""Description"" = :Description, 271 set id = :ID, hash = :Hash, name = :Name, description = :Description,
262 ""AssetType"" = :AssetType, ""Local"" = :Local, ""Temporary"" = :Temporary, ""CreateTime"" = :CreateTime, 272 ""AssetType"" = :AssetType, local = :Local, temporary = :Temporary, create_time = :CreateTime,
263 ""AccessTime"" = :AccessTime, ""AssetFlags"" = :AssetFlags, ""CreatorID"" = :CreatorID 273 access_time = :AccessTime, asset_flags = :AssetFlags, creatorid = :CreatorID
264 where ""ID"" = :ID; 274 where id = :ID;
265 ", 275 ",
266 dbcon)) 276 dbcon))
267 { 277 {
278
268 // create unix epoch time 279 // create unix epoch time
269 int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow); 280 int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow);
270 cmd.Parameters.AddWithValue("ID", asset.ID); 281 cmd.Parameters.Add(m_database.CreateParameter("ID", asset_id));
271 cmd.Parameters.AddWithValue("Hash", hash); 282 cmd.Parameters.Add(m_database.CreateParameter("Hash", hash));
272 cmd.Parameters.AddWithValue("Name", assetName); 283 cmd.Parameters.Add(m_database.CreateParameter("Name", assetName));
273 cmd.Parameters.AddWithValue("Description", assetDescription); 284 cmd.Parameters.Add(m_database.CreateParameter("Description", assetDescription));
274 cmd.Parameters.AddWithValue("AssetType", asset.Type); 285 cmd.Parameters.Add(m_database.CreateParameter("AssetType", asset.Type));
275 cmd.Parameters.AddWithValue("Local", asset.Local); 286 cmd.Parameters.Add(m_database.CreateParameter("Local", asset.Local));
276 cmd.Parameters.AddWithValue("Temporary", asset.Temporary); 287 cmd.Parameters.Add(m_database.CreateParameter("Temporary", asset.Temporary));
277 cmd.Parameters.AddWithValue("CreateTime", now); 288 cmd.Parameters.Add(m_database.CreateParameter("CreateTime", now));
278 cmd.Parameters.AddWithValue("AccessTime", now); 289 cmd.Parameters.Add(m_database.CreateParameter("AccessTime", now));
279 cmd.Parameters.AddWithValue("CreatorID", asset.Metadata.CreatorID); 290 cmd.Parameters.Add(m_database.CreateParameter("CreatorID", asset.Metadata.CreatorID));
280 cmd.Parameters.AddWithValue("AssetFlags", (int)asset.Flags); 291 cmd.Parameters.Add(m_database.CreateParameter("AssetFlags", (int)asset.Flags));
292
281 cmd.ExecuteNonQuery(); 293 cmd.ExecuteNonQuery();
282 } 294 }
283 } 295 }
@@ -297,11 +309,11 @@ namespace OpenSim.Data.PGSQL
297 { 309 {
298 using (NpgsqlCommand cmd = 310 using (NpgsqlCommand cmd =
299 new NpgsqlCommand( 311 new NpgsqlCommand(
300 @"INSERT INTO XAssetsData(""Hash"", ""Data"") VALUES(:Hash, :Data)", 312 @"INSERT INTO XAssetsData(hash, data) VALUES(:Hash, :Data)",
301 dbcon)) 313 dbcon))
302 { 314 {
303 cmd.Parameters.AddWithValue("Hash", hash); 315 cmd.Parameters.Add(m_database.CreateParameter("Hash", hash));
304 cmd.Parameters.AddWithValue("Data", asset.Data); 316 cmd.Parameters.Add(m_database.CreateParameter("Data", asset.Data));
305 cmd.ExecuteNonQuery(); 317 cmd.ExecuteNonQuery();
306 } 318 }
307 } 319 }
@@ -344,15 +356,18 @@ namespace OpenSim.Data.PGSQL
344 { 356 {
345 dbcon.Open(); 357 dbcon.Open();
346 NpgsqlCommand cmd = 358 NpgsqlCommand cmd =
347 new NpgsqlCommand(@"update XAssetsMeta set ""AccessTime""=:AccessTime where ID=:ID", dbcon); 359 new NpgsqlCommand(@"update XAssetsMeta set access_time=:AccessTime where id=:ID", dbcon);
348 360
349 try 361 try
350 { 362 {
363 UUID asset_id;
364 UUID.TryParse(assetMetadata.ID, out asset_id);
365
351 using (cmd) 366 using (cmd)
352 { 367 {
353 // create unix epoch time 368 // create unix epoch time
354 cmd.Parameters.AddWithValue("ID", assetMetadata.ID); 369 cmd.Parameters.Add(m_database.CreateParameter("id", asset_id));
355 cmd.Parameters.AddWithValue("AccessTime", (int)Utils.DateTimeToUnixTime(now)); 370 cmd.Parameters.Add(m_database.CreateParameter("access_time", (int)Utils.DateTimeToUnixTime(now)));
356 cmd.ExecuteNonQuery(); 371 cmd.ExecuteNonQuery();
357 } 372 }
358 } 373 }
@@ -380,9 +395,9 @@ namespace OpenSim.Data.PGSQL
380 395
381 bool exists = false; 396 bool exists = false;
382 397
383 using (NpgsqlCommand cmd = new NpgsqlCommand(@"SELECT ""Hash"" FROM XAssetsData WHERE ""Hash""=:Hash", dbcon)) 398 using (NpgsqlCommand cmd = new NpgsqlCommand(@"SELECT hash FROM XAssetsData WHERE hash=:Hash", dbcon))
384 { 399 {
385 cmd.Parameters.AddWithValue("Hash", hash); 400 cmd.Parameters.Add(m_database.CreateParameter("Hash", hash));
386 401
387 try 402 try
388 { 403 {
@@ -407,6 +422,43 @@ namespace OpenSim.Data.PGSQL
407 } 422 }
408 423
409 /// <summary> 424 /// <summary>
425 /// Check if the assets exist in the database.
426 /// </summary>
427 /// <param name="uuids">The assets' IDs</param>
428 /// <returns>For each asset: true if it exists, false otherwise</returns>
429 public bool[] AssetsExist(UUID[] uuids)
430 {
431 if (uuids.Length == 0)
432 return new bool[0];
433
434 HashSet<UUID> exist = new HashSet<UUID>();
435
436 string ids = "'" + string.Join("','", uuids) + "'";
437 string sql = string.Format(@"SELECT id FROM XAssetsMeta WHERE id IN ({0})", ids);
438
439 using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
440 {
441 conn.Open();
442 using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
443 {
444 using (NpgsqlDataReader reader = cmd.ExecuteReader())
445 {
446 while (reader.Read())
447 {
448 UUID id = DBGuid.FromDB(reader["id"]);
449 exist.Add(id);
450 }
451 }
452 }
453 }
454
455 bool[] results = new bool[uuids.Length];
456 for (int i = 0; i < uuids.Length; i++)
457 results[i] = exist.Contains(uuids[i]);
458 return results;
459 }
460
461 /// <summary>
410 /// Check if the asset exists in the database 462 /// Check if the asset exists in the database
411 /// </summary> 463 /// </summary>
412 /// <param name="uuid">The asset UUID</param> 464 /// <param name="uuid">The asset UUID</param>
@@ -422,9 +474,9 @@ namespace OpenSim.Data.PGSQL
422 using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString)) 474 using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString))
423 { 475 {
424 dbcon.Open(); 476 dbcon.Open();
425 using (NpgsqlCommand cmd = new NpgsqlCommand(@"SELECT ""ID"" FROM XAssetsMeta WHERE ""ID""=:ID", dbcon)) 477 using (NpgsqlCommand cmd = new NpgsqlCommand(@"SELECT id FROM XAssetsMeta WHERE id=:ID", dbcon))
426 { 478 {
427 cmd.Parameters.AddWithValue("ID", uuid.ToString()); 479 cmd.Parameters.Add(m_database.CreateParameter("id", uuid));
428 480
429 try 481 try
430 { 482 {
@@ -466,11 +518,11 @@ namespace OpenSim.Data.PGSQL
466 using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString)) 518 using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString))
467 { 519 {
468 dbcon.Open(); 520 dbcon.Open();
469 NpgsqlCommand cmd = new NpgsqlCommand( @"SELECT ""Name"", ""Description"", ""AccessTime"", ""AssetType"", ""Temporary"", ""ID"", ""AssetFlags"", ""CreatorID"" 521 NpgsqlCommand cmd = new NpgsqlCommand( @"SELECT name, description, access_time, ""AssetType"", temporary, id, asset_flags, creatorid
470 FROM XAssetsMeta 522 FROM XAssetsMeta
471 LIMIT :start, :count", dbcon); 523 LIMIT :start, :count", dbcon);
472 cmd.Parameters.AddWithValue("start", start); 524 cmd.Parameters.Add(m_database.CreateParameter("start", start));
473 cmd.Parameters.AddWithValue("count", count); 525 cmd.Parameters.Add(m_database.CreateParameter("count", count));
474 526
475 try 527 try
476 { 528 {
@@ -479,18 +531,18 @@ namespace OpenSim.Data.PGSQL
479 while (dbReader.Read()) 531 while (dbReader.Read())
480 { 532 {
481 AssetMetadata metadata = new AssetMetadata(); 533 AssetMetadata metadata = new AssetMetadata();
482 metadata.Name = (string)dbReader["Name"]; 534 metadata.Name = (string)dbReader["name"];
483 metadata.Description = (string)dbReader["Description"]; 535 metadata.Description = (string)dbReader["description"];
484 metadata.Type = (sbyte)dbReader["AssetType"]; 536 metadata.Type = Convert.ToSByte(dbReader["AssetType"]);
485 metadata.Temporary = Convert.ToBoolean(dbReader["Temporary"]); // Not sure if this is correct. 537 metadata.Temporary = Convert.ToBoolean(dbReader["temporary"]);
486 metadata.Flags = (AssetFlags)Convert.ToInt32(dbReader["AssetFlags"]); 538 metadata.Flags = (AssetFlags)Convert.ToInt32(dbReader["asset_flags"]);
487 metadata.FullID = DBGuid.FromDB(dbReader["ID"]); 539 metadata.FullID = DBGuid.FromDB(dbReader["id"]);
488 metadata.CreatorID = dbReader["CreatorID"].ToString(); 540 metadata.CreatorID = dbReader["creatorid"].ToString();
489 541
490 // We'll ignore this for now - it appears unused! 542 // We'll ignore this for now - it appears unused!
491// metadata.SHA1 = dbReader["hash"]); 543// metadata.SHA1 = dbReader["hash"]);
492 544
493 UpdateAccessTime(metadata, (int)dbReader["AccessTime"]); 545 UpdateAccessTime(metadata, (int)dbReader["access_time"]);
494 546
495 retList.Add(metadata); 547 retList.Add(metadata);
496 } 548 }
@@ -516,9 +568,9 @@ namespace OpenSim.Data.PGSQL
516 { 568 {
517 dbcon.Open(); 569 dbcon.Open();
518 570
519 using (NpgsqlCommand cmd = new NpgsqlCommand(@"delete from XAssetsMeta where ""ID""=:ID", dbcon)) 571 using (NpgsqlCommand cmd = new NpgsqlCommand(@"delete from XAssetsMeta where id=:ID", dbcon))
520 { 572 {
521 cmd.Parameters.AddWithValue("ID", id); 573 cmd.Parameters.Add(m_database.CreateParameter(id, id));
522 cmd.ExecuteNonQuery(); 574 cmd.ExecuteNonQuery();
523 } 575 }
524 576
diff --git a/OpenSim/Data/PGSQL/Properties/AssemblyInfo.cs b/OpenSim/Data/PGSQL/Properties/AssemblyInfo.cs
index 27d0679..1e88b2c 100644
--- a/OpenSim/Data/PGSQL/Properties/AssemblyInfo.cs
+++ b/OpenSim/Data/PGSQL/Properties/AssemblyInfo.cs
@@ -61,5 +61,5 @@ using System.Runtime.InteropServices;
61// You can specify all the values or you can default the Revision and Build Numbers 61// You can specify all the values or you can default the Revision and Build Numbers
62// by using the '*' as shown below: 62// by using the '*' as shown below:
63 63
64[assembly : AssemblyVersion("0.7.6.*")] 64[assembly : AssemblyVersion("0.8.2.*")]
65 65
diff --git a/OpenSim/Data/PGSQL/Resources/AssetStore.migrations b/OpenSim/Data/PGSQL/Resources/AssetStore.migrations
index b6db585..7a858b4 100644
--- a/OpenSim/Data/PGSQL/Resources/AssetStore.migrations
+++ b/OpenSim/Data/PGSQL/Resources/AssetStore.migrations
@@ -92,3 +92,8 @@ ALTER TABLE assets ADD "asset_flags" INTEGER NOT NULL DEFAULT 0;
92:VERSION 7 92:VERSION 7
93 93
94alter table assets add "creatorid" varchar(36) not null default ''; 94alter table assets add "creatorid" varchar(36) not null default '';
95
96:VERSION 8
97
98BEGIN TRANSACTION;
99COMMIT;
diff --git a/OpenSim/Data/PGSQL/Resources/IM_Store.migrations b/OpenSim/Data/PGSQL/Resources/IM_Store.migrations
index 70dc011..eb97824 100644
--- a/OpenSim/Data/PGSQL/Resources/IM_Store.migrations
+++ b/OpenSim/Data/PGSQL/Resources/IM_Store.migrations
@@ -24,3 +24,22 @@ DELETE FROM `migrations` WHERE name='diva_im_Store';
24*/ 24*/
25 25
26COMMIT; 26COMMIT;
27
28:VERSION 3 # --------------------------
29
30BEGIN;
31
32-- dropping the table here as there most likely is only one record in the table at the time of migration
33
34DROP TABLE IF EXISTS "public"."im_offline";
35CREATE TABLE "public"."im_offline" (
36 "ID" serial,
37 "PrincipalID" uuid NOT NULL,
38 "Message" text NOT NULL COLLATE "default",
39 "TMStamp" timestamp(6) NOT NULL DEFAULT clock_timestamp(),
40 "FromID" uuid NOT NULL
41)
42WITH (OIDS=FALSE);
43ALTER TABLE "public"."im_offline" ADD PRIMARY KEY ("ID","PrincipalID","FromID") NOT DEFERRABLE INITIALLY IMMEDIATE;
44
45COMMIT; \ No newline at end of file
diff --git a/OpenSim/Data/PGSQL/Resources/Presence.migrations b/OpenSim/Data/PGSQL/Resources/Presence.migrations
index 684faa2..5184034 100644..100755
--- a/OpenSim/Data/PGSQL/Resources/Presence.migrations
+++ b/OpenSim/Data/PGSQL/Resources/Presence.migrations
@@ -19,12 +19,24 @@ BEGIN TRANSACTION;
19CREATE UNIQUE INDEX SessionID ON Presence("SessionID"); 19CREATE UNIQUE INDEX SessionID ON Presence("SessionID");
20CREATE INDEX UserID ON Presence("UserID"); 20CREATE INDEX UserID ON Presence("UserID");
21 21
22ALTER TABLE Presence ADD "LastSeen" Timestamp;
23
22COMMIT; 24COMMIT;
23 25
24:VERSION 2 26:VERSION 3 # --------------------------
25 27
26BEGIN TRANSACTION; 28BEGIN;
27 29
28ALTER TABLE Presence ADD "LastSeen" Timestamp; 30CREATE INDEX RegionID ON Presence("RegionID");
29 31
30COMMIT; 32COMMIT;
33
34:VERSION 4 # Making sure LastSeen is actually defined in the table as it most likely erred in the double version 2 migration above
35
36BEGIN;
37
38ALTER TABLE Presence
39DROP COLUMN IF EXISTS "LastSeen",
40ADD COLUMN "LastSeen" Timestamp;
41
42COMMIT; \ No newline at end of file
diff --git a/OpenSim/Data/PGSQL/Resources/RegionStore.migrations b/OpenSim/Data/PGSQL/Resources/RegionStore.migrations
index 1e33027..1284ce0 100644
--- a/OpenSim/Data/PGSQL/Resources/RegionStore.migrations
+++ b/OpenSim/Data/PGSQL/Resources/RegionStore.migrations
@@ -1152,3 +1152,11 @@ ALTER TABLE regionwindlight ALTER draw_classic_clouds TYPE bool USING CASE WHEN
1152ALTER TABLE regionwindlight ALTER COLUMN draw_classic_clouds SET DEFAULT FALSE; 1152ALTER TABLE regionwindlight ALTER COLUMN draw_classic_clouds SET DEFAULT FALSE;
1153 1153
1154COMMIT; 1154COMMIT;
1155
1156VERSION 41 #-- Change Landlags to bigint
1157
1158BEGIN TRANSACTION;
1159
1160ALTER TABLE land ALTER "LandFlags" TYPE bigint;
1161
1162COMMIT;
diff --git a/OpenSim/Data/PGSQL/Resources/UserProfiles.migrations b/OpenSim/Data/PGSQL/Resources/UserProfiles.migrations
index f23c870..a67107a 100644
--- a/OpenSim/Data/PGSQL/Resources/UserProfiles.migrations
+++ b/OpenSim/Data/PGSQL/Resources/UserProfiles.migrations
@@ -81,3 +81,78 @@ CREATE TABLE userdata (
81 81
82commit; 82commit;
83 83
84<<<<<<< HEAD
85:VERSION 3 # -------------------------------
86begin;
87CREATE TABLE usersettings (
88 "useruuid" char(36) NOT NULL,
89 "imviaemail" bytea NOT NULL,
90 "visible" bytea NOT NULL,
91 PRIMARY KEY ("useruuid")
92);
93commit;
94
95:VERSION 4
96
97BEGIN;
98
99-- Classifieds
100ALTER TABLE classifieds DROP CONSTRAINT classifiedspk;
101ALTER TABLE classifieds ALTER COLUMN classifieduuid SET DATA TYPE uuid using classifieduuid::uuid;
102ALTER TABLE classifieds ALTER COLUMN creatoruuid SET DATA TYPE uuid using creatoruuid::uuid;
103ALTER TABLE classifieds ALTER COLUMN parceluuid SET DATA TYPE uuid using parceluuid::uuid;
104ALTER TABLE classifieds ALTER COLUMN snapshotuuid SET DATA TYPE uuid using snapshotuuid::uuid;
105ALTER TABLE classifieds ADD CONSTRAINT classifiedspk PRIMARY KEY (classifieduuid);
106
107-- Notes
108ALTER TABLE usernotes DROP CONSTRAINT usernoteuk;
109ALTER TABLE usernotes ALTER COLUMN useruuid SET DATA TYPE uuid USING useruuid::uuid;
110ALTER TABLE usernotes ALTER COLUMN targetuuid SET DATA TYPE uuid USING targetuuid::uuid;
111ALTER TABLE usernotes ADD CONSTRAINT usernoteuk UNIQUE (useruuid,targetuuid);
112
113
114-- Userpicks
115ALTER TABLE userpicks DROP CONSTRAINT userpicks_pkey;
116ALTER TABLE userpicks ALTER COLUMN pickuuid SET DATA TYPE uuid USING pickuuid::uuid;
117ALTER TABLE userpicks ALTER COLUMN creatoruuid SET DATA TYPE uuid USING creatoruuid::uuid;
118ALTER TABLE userpicks ALTER COLUMN parceluuid SET DATA TYPE uuid USING parceluuid::uuid;
119ALTER TABLE userpicks ALTER COLUMN parceluuid SET DATA TYPE uuid USING parceluuid::uuid;
120ALTER TABLE userpicks ADD PRIMARY KEY (pickuuid);
121
122-- Userprofile
123ALTER TABLE userprofile DROP CONSTRAINT userprofile_pkey;
124ALTER TABLE userprofile ALTER COLUMN useruuid SET DATA TYPE uuid USING useruuid::uuid;
125ALTER TABLE userprofile ALTER COLUMN "profilePartner" SET DATA TYPE uuid USING "profilePartner"::uuid;
126-- Force column conversions
127ALTER TABLE userprofile ALTER COLUMN "profileAllowPublish" SET DATA TYPE boolean USING CASE WHEN false THEN false ELSE true END;
128ALTER TABLE userprofile ALTER COLUMN "profileMaturePublish" SET DATA TYPE boolean USING CASE WHEN false THEN false ELSE true END;
129ALTER TABLE userprofile ALTER COLUMN "profileImage" SET DATA TYPE uuid USING "profileImage"::uuid;
130ALTER TABLE userprofile ALTER COLUMN "profileFirstImage" SET DATA TYPE uuid USING "profileFirstImage"::uuid;
131ALTER TABLE userprofile ADD PRIMARY KEY (useruuid);
132
133-- Userdata
134ALTER TABLE userdata DROP CONSTRAINT userdata_pkey;
135ALTER TABLE userdata ALTER COLUMN "UserId" SET DATA TYPE uuid USING "UserId"::uuid;
136ALTER TABLE userdata ALTER COLUMN "UserId" SET DATA TYPE uuid USING "UserId"::uuid;
137ALTER TABLE userdata ADD PRIMARY KEY ("UserId","TagId");
138
139
140-- Usersettings
141ALTER TABLE usersettings DROP CONSTRAINT usersettings_pkey;
142ALTER TABLE usersettings ALTER COLUMN useruuid SET DATA TYPE uuid USING useruuid::uuid;
143ALTER TABLE usersettings ALTER COLUMN visible SET DATA TYPE boolean USING CASE WHEN false THEN false ELSE true END;
144ALTER TABLE usersettings ADD COLUMN email varchar(254) NOT NULL;
145ALTER TABLE usersettings ADD PRIMARY KEY (useruuid);
146
147COMMIT;
148
149
150:VERSION 5 # -------------------------------
151
152BEGIN;
153
154ALTER TABLE usersettings ALTER COLUMN imviaemail SET DATA TYPE boolean USING CASE WHEN false THEN false ELSE true END;
155
156COMMIT;
157=======
158>>>>>>> avn/ubitvar
diff --git a/OpenSim/Data/PGSQL/Resources/XAssetStore.migrations b/OpenSim/Data/PGSQL/Resources/XAssetStore.migrations
index 325ed0d..df9d821 100644
--- a/OpenSim/Data/PGSQL/Resources/XAssetStore.migrations
+++ b/OpenSim/Data/PGSQL/Resources/XAssetStore.migrations
@@ -25,3 +25,56 @@ CREATE TABLE XAssetsData (
25); 25);
26 26
27COMMIT; 27COMMIT;
28
29
30:VERSION 2
31
32BEGIN;
33
34ALTER TABLE xassetsmeta ALTER COLUMN "Local" SET DATA TYPE boolean USING CASE WHEN '0' THEN FALSE ELSE TRUE END;
35ALTER TABLE xassetsmeta ALTER COLUMN "Temporary" SET DATA TYPE boolean USING CASE WHEN '0' THEN FALSE ELSE TRUE END;
36ALTER TABLE xassetsmeta ALTER COLUMN "Hash" SET DATA TYPE char(66);
37ALTER TABLE xassetsdata ALTER COLUMN "Hash" SET DATA TYPE char(66);
38
39COMMIT;
40
41:VERSION 3
42
43BEGIN;
44
45ALTER TABLE xassetsmeta RENAME COLUMN "ID" TO id;
46ALTER TABLE xassetsmeta RENAME COLUMN "Hash" TO hash;
47ALTER TABLE xassetsmeta RENAME COLUMN "Name" TO name;
48ALTER TABLE xassetsmeta RENAME COLUMN "Description" TO description;
49ALTER TABLE xassetsmeta RENAME COLUMN "Local" to local;
50ALTER TABLE xassetsmeta RENAME COLUMN "Temporary" TO temporary;
51ALTER TABLE xassetsmeta RENAME COLUMN "CreateTime" TO create_time;
52ALTER TABLE xassetsmeta RENAME COLUMN "AccessTime" TO access_time;
53ALTER TABLE xassetsmeta RENAME COLUMN "AssetFlags" TO asset_flags;
54ALTER TABLE xassetsmeta RENAME COLUMN "CreatorID" TO creatorid;
55ALTER TABLE xassetsmeta DROP CONSTRAINT xassetsmeta_pkey;
56ALTER TABLE xassetsmeta ADD PRIMARY KEY (id);
57
58
59ALTER TABLE xassetsdata RENAME COLUMN "Hash" TO hash;
60ALTER TABLE xassetsdata RENAME COLUMN "Data" TO data;
61ALTER TABLE xassetsdata DROP CONSTRAINT xassetsdata_pkey;
62ALTER TABLE xassetsdata ADD PRIMARY KEY (hash);
63
64COMMIT;
65
66
67:VERSION 4
68
69BEGIN;
70
71ALTER TABLE xassetsmeta ALTER COLUMN id SET DATA TYPE uuid USING id::uuid;
72ALTER TABLE xassetsmeta ALTER COLUMN hash SET DATA TYPE bytea USING hash::bytea;
73ALTER TABLE xassetsdata ALTER COLUMN hash SET DATA TYPE bytea USING hash::bytea;
74
75COMMIT;
76
77:VERSION 5
78
79BEGIN;
80COMMIT;
diff --git a/OpenSim/Data/PGSQL/Resources/os_groups_Store.migrations b/OpenSim/Data/PGSQL/Resources/os_groups_Store.migrations
index 4573f71..74b07c3 100644
--- a/OpenSim/Data/PGSQL/Resources/os_groups_Store.migrations
+++ b/OpenSim/Data/PGSQL/Resources/os_groups_Store.migrations
@@ -92,3 +92,120 @@ BEGIN;
92 92
93 93
94COMMIT; 94COMMIT;
95
96
97
98:VERSION 3
99
100BEGIN;
101
102-- Not a pretty way to do this, but it did not work as-is
103-- and nothing was found about converting between existing data
104-- and the new type.
105-- Since there should be nothing to preserve ...
106
107DROP TABLE IF EXISTS os_groups_groups CASCADE;
108
109CREATE TABLE os_groups_groups (
110 "GroupID" uuid PRIMARY KEY NOT NULL,
111 "Location" varchar(255) NOT NULL DEFAULT '',
112 "Name" varchar(255) NOT NULL DEFAULT '',
113 "Charter" text NOT NULL,
114 "InsigniaID" uuid NOT NULL,
115 "FounderID" uuid NOT NULL,
116 "MembershipFee" integer NOT NULL DEFAULT '0',
117 "OpenEnrollment" varchar(255) NOT NULL DEFAULT '',
118 "ShowInList" integer NOT NULL DEFAULT '0',
119 "AllowPublish" integer NOT NULL DEFAULT '0',
120 "MaturePublish" integer NOT NULL DEFAULT '0',
121 "OwnerRoleID" uuid NOT NULL
122);
123
124
125DROP TABLE IF EXISTS os_groups_membership;
126
127CREATE TABLE os_groups_membership (
128 "GroupID"uuid NOT NULL,
129 "PrincipalID" VARCHAR(255) NOT NULL DEFAULT '',
130 "SelectedRoleID" uuid NOT NULL,
131 "Contribution" integer NOT NULL DEFAULT '0',
132 "ListInProfile" integer NOT NULL DEFAULT '1',
133 "AcceptNotices" integer NOT NULL DEFAULT '1',
134 "AccessToken" uuid NOT NULL,
135 constraint os_groupmemberpk PRIMARY KEY ("GroupID", "PrincipalID")
136);
137
138
139
140DROP TABLE IF EXISTS os_groups_roles;
141
142CREATE TABLE os_groups_roles (
143 "GroupID" uuid NOT NULL,
144 "RoleID" uuid NOT NULL,
145 "Name" varchar(255) NOT NULL DEFAULT '',
146 "Description" varchar(255) NOT NULL DEFAULT '',
147 "Title" varchar(255) NOT NULL DEFAULT '',
148 "Powers" varchar(36) NOT NULL DEFAULT '',
149 constraint os_grouprolepk PRIMARY KEY ("GroupID","RoleID")
150);
151
152
153DROP TABLE IF EXISTS os_groups_rolemembership;
154
155CREATE TABLE os_groups_rolemembership (
156 "GroupID" uuid NOT NULL,
157 "RoleID" uuid NOT NULL,
158 "PrincipalID" VARCHAR(255) NOT NULL DEFAULT '',
159 constraint os_grouprolememberpk PRIMARY KEY ("GroupID","RoleID","PrincipalID")
160);
161
162
163DROP TABLE IF EXISTS os_groups_invites;
164
165CREATE TABLE os_groups_invites (
166 "InviteID" uuid NOT NULL,
167 "GroupID" uuid NOT NULL,
168 "RoleID" uuid NOT NULL,
169 "PrincipalID" VARCHAR(255) NOT NULL DEFAULT '',
170 "TMStamp" timestamp NOT NULL DEFAULT now(),
171 constraint os_groupinvitespk PRIMARY KEY ("InviteID")
172);
173
174
175DROP TABLE IF EXISTS os_groups_notices;
176
177CREATE TABLE os_groups_notices (
178 "GroupID" uuid NOT NULL,
179 "NoticeID" uuid NOT NULL,
180 "TMStamp" integer NOT NULL DEFAULT '0',
181 "FromName" varchar(255) NOT NULL DEFAULT '',
182 "Subject" varchar(255) NOT NULL DEFAULT '',
183 "Message" text NOT NULL,
184 "HasAttachment" integer NOT NULL DEFAULT '0',
185 "AttachmentType" integer NOT NULL DEFAULT '0',
186 "AttachmentName" varchar(128) NOT NULL DEFAULT '',
187 "AttachmentItemID" uuid NOT NULL,
188 "AttachmentOwnerID" varchar(255) NOT NULL DEFAULT '',
189 constraint os_groupsnoticespk PRIMARY KEY ("NoticeID")
190);
191
192
193DROP TABLE IF EXISTS os_groups_principals;
194
195CREATE TABLE os_groups_principals (
196 "PrincipalID" VARCHAR(255) NOT NULL DEFAULT '',
197 "ActiveGroupID" uuid NOT NULL,
198 constraint os_groupprincpk PRIMARY KEY ("PrincipalID")
199);
200
201COMMIT;
202
203:VERSION 4
204
205BEGIN;
206
207ALTER TABLE IF EXISTS os_groups_notices
208 ALTER COLUMN "AttachmentItemID" SET DEFAULT '00000000-0000-0000-0000-000000000000'
209;
210
211COMMIT;
generated by cgit v1.1 at 2024-12-22 01:22:04 +0000