aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/MySQL
diff options
context:
space:
mode:
Diffstat (limited to 'OpenSim/Data/MySQL')
-rw-r--r--OpenSim/Data/MySQL/MySQLAssetData.cs22
-rw-r--r--OpenSim/Data/MySQL/MySQLGridUserData.cs7
-rw-r--r--OpenSim/Data/MySQL/MySQLGroupsData.cs2
-rw-r--r--OpenSim/Data/MySQL/MySQLHGTravelData.cs80
-rw-r--r--OpenSim/Data/MySQL/MySQLOfflineIMData.cs7
-rw-r--r--OpenSim/Data/MySQL/MySQLRegionData.cs5
-rw-r--r--OpenSim/Data/MySQL/MySQLSimulationData.cs31
-rw-r--r--OpenSim/Data/MySQL/MySQLUserProfilesData.cs1102
-rw-r--r--OpenSim/Data/MySQL/MySQLXAssetData.cs2
-rw-r--r--OpenSim/Data/MySQL/Properties/AssemblyInfo.cs2
-rw-r--r--OpenSim/Data/MySQL/Resources/EstateStore.migrations6
-rw-r--r--OpenSim/Data/MySQL/Resources/FriendsStore.migrations2
-rw-r--r--OpenSim/Data/MySQL/Resources/HGTravelStore.migrations18
-rw-r--r--OpenSim/Data/MySQL/Resources/IM_Store.migrations12
-rw-r--r--OpenSim/Data/MySQL/Resources/RegionStore.migrations17
-rw-r--r--OpenSim/Data/MySQL/Resources/UserProfiles.migrations83
16 files changed, 1373 insertions, 25 deletions
diff --git a/OpenSim/Data/MySQL/MySQLAssetData.cs b/OpenSim/Data/MySQL/MySQLAssetData.cs
index 21dd5aa..59cc22a 100644
--- a/OpenSim/Data/MySQL/MySQLAssetData.cs
+++ b/OpenSim/Data/MySQL/MySQLAssetData.cs
@@ -142,7 +142,8 @@ namespace OpenSim.Data.MySQL
142 } 142 }
143 catch (Exception e) 143 catch (Exception e)
144 { 144 {
145 m_log.Error("[ASSETS DB]: MySql failure fetching asset " + assetID + ": " + e.Message); 145 m_log.Error(
146 string.Format("[ASSETS DB]: MySql failure fetching asset {0}. Exception ", assetID), e);
146 } 147 }
147 } 148 }
148 } 149 }
@@ -243,10 +244,11 @@ namespace OpenSim.Data.MySQL
243 } 244 }
244 catch (Exception e) 245 catch (Exception e)
245 { 246 {
246 m_log.ErrorFormat( 247 m_log.Error(
247 "[ASSETS DB]: " + 248 string.Format(
248 "MySql failure updating access_time for asset {0} with name {1}" + Environment.NewLine + e.ToString() 249 "[ASSETS DB]: Failure updating access_time for asset {0} with name {1}. Exception ",
249 + Environment.NewLine + "Attempting reconnection", asset.FullID, asset.Name); 250 asset.FullID, asset.Name),
251 e);
250 } 252 }
251 } 253 }
252 } 254 }
@@ -286,8 +288,8 @@ namespace OpenSim.Data.MySQL
286 } 288 }
287 catch (Exception e) 289 catch (Exception e)
288 { 290 {
289 m_log.ErrorFormat( 291 m_log.Error(
290 "[ASSETS DB]: MySql failure fetching asset {0}" + Environment.NewLine + e.ToString(), uuid); 292 string.Format("[ASSETS DB]: MySql failure fetching asset {0}. Exception ", uuid), e);
291 } 293 }
292 } 294 }
293 } 295 }
@@ -346,7 +348,11 @@ namespace OpenSim.Data.MySQL
346 } 348 }
347 catch (Exception e) 349 catch (Exception e)
348 { 350 {
349 m_log.Error("[ASSETS DB]: MySql failure fetching asset set" + Environment.NewLine + e.ToString()); 351 m_log.Error(
352 string.Format(
353 "[ASSETS DB]: MySql failure fetching asset set from {0}, count {1}. Exception ",
354 start, count),
355 e);
350 } 356 }
351 } 357 }
352 } 358 }
diff --git a/OpenSim/Data/MySQL/MySQLGridUserData.cs b/OpenSim/Data/MySQL/MySQLGridUserData.cs
index a9ce94d..00560c1 100644
--- a/OpenSim/Data/MySQL/MySQLGridUserData.cs
+++ b/OpenSim/Data/MySQL/MySQLGridUserData.cs
@@ -46,7 +46,7 @@ namespace OpenSim.Data.MySQL
46 46
47 public MySQLGridUserData(string connectionString, string realm) : base(connectionString, realm, "GridUserStore") {} 47 public MySQLGridUserData(string connectionString, string realm) : base(connectionString, realm, "GridUserStore") {}
48 48
49 public GridUserData Get(string userID) 49 public new GridUserData Get(string userID)
50 { 50 {
51 GridUserData[] ret = Get("UserID", userID); 51 GridUserData[] ret = Get("UserID", userID);
52 52
@@ -56,6 +56,9 @@ namespace OpenSim.Data.MySQL
56 return ret[0]; 56 return ret[0];
57 } 57 }
58 58
59 59 public GridUserData[] GetAll(string userID)
60 {
61 return base.Get(String.Format("UserID LIKE '{0}%'", userID));
62 }
60 } 63 }
61} \ No newline at end of file 64} \ No newline at end of file
diff --git a/OpenSim/Data/MySQL/MySQLGroupsData.cs b/OpenSim/Data/MySQL/MySQLGroupsData.cs
index 2a1bd6c..0318284 100644
--- a/OpenSim/Data/MySQL/MySQLGroupsData.cs
+++ b/OpenSim/Data/MySQL/MySQLGroupsData.cs
@@ -88,7 +88,7 @@ namespace OpenSim.Data.MySQL
88 if (string.IsNullOrEmpty(pattern)) 88 if (string.IsNullOrEmpty(pattern))
89 pattern = "1 ORDER BY Name LIMIT 100"; 89 pattern = "1 ORDER BY Name LIMIT 100";
90 else 90 else
91 pattern = string.Format("Name LIKE %{0}% ORDER BY Name LIMIT 100", pattern); 91 pattern = string.Format("Name LIKE '%{0}%' ORDER BY Name LIMIT 100", pattern);
92 92
93 return m_Groups.Get(pattern); 93 return m_Groups.Get(pattern);
94 } 94 }
diff --git a/OpenSim/Data/MySQL/MySQLHGTravelData.cs b/OpenSim/Data/MySQL/MySQLHGTravelData.cs
new file mode 100644
index 0000000..e81b880
--- /dev/null
+++ b/OpenSim/Data/MySQL/MySQLHGTravelData.cs
@@ -0,0 +1,80 @@
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.Generic;
30using System.Data;
31using System.Reflection;
32using System.Threading;
33using log4net;
34using OpenMetaverse;
35using OpenSim.Framework;
36using MySql.Data.MySqlClient;
37
38namespace OpenSim.Data.MySQL
39{
40 /// <summary>
41 /// A MySQL Interface for user grid data
42 /// </summary>
43 public class MySQLHGTravelData : MySQLGenericTableHandler<HGTravelingData>, IHGTravelingData
44 {
45// private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
46
47 public MySQLHGTravelData(string connectionString, string realm) : base(connectionString, realm, "HGTravelStore") { }
48
49 public HGTravelingData Get(UUID sessionID)
50 {
51 HGTravelingData[] ret = Get("SessionID", sessionID.ToString());
52
53 if (ret.Length == 0)
54 return null;
55
56 return ret[0];
57 }
58
59 public HGTravelingData[] GetSessions(UUID userID)
60 {
61 return base.Get("UserID", userID.ToString());
62 }
63
64 public bool Delete(UUID sessionID)
65 {
66 return Delete("SessionID", sessionID.ToString());
67 }
68
69 public void DeleteOld()
70 {
71 using (MySqlCommand cmd = new MySqlCommand())
72 {
73 cmd.CommandText = String.Format("delete from {0} where TMStamp < NOW() - INTERVAL 2 DAY", m_Realm);
74
75 ExecuteNonQuery(cmd);
76 }
77
78 }
79 }
80} \ No newline at end of file
diff --git a/OpenSim/Data/MySQL/MySQLOfflineIMData.cs b/OpenSim/Data/MySQL/MySQLOfflineIMData.cs
index 252f358..bafd204 100644
--- a/OpenSim/Data/MySQL/MySQLOfflineIMData.cs
+++ b/OpenSim/Data/MySQL/MySQLOfflineIMData.cs
@@ -47,13 +47,10 @@ namespace OpenSim.Data.MySQL
47 47
48 public void DeleteOld() 48 public void DeleteOld()
49 { 49 {
50 uint now = (uint)Util.UnixTimeSinceEpoch();
51
52 using (MySqlCommand cmd = new MySqlCommand()) 50 using (MySqlCommand cmd = new MySqlCommand())
53 { 51 {
54 cmd.CommandText = String.Format("delete from {0} where TMStamp < ?tstamp", m_Realm); 52 cmd.CommandText = String.Format("delete from {0} where TMStamp < NOW() - INTERVAL 2 WEEK", m_Realm);
55 cmd.Parameters.AddWithValue("?tstamp", now - 14 * 24 * 60 * 60); // > 2 weeks old 53
56
57 ExecuteNonQuery(cmd); 54 ExecuteNonQuery(cmd);
58 } 55 }
59 56
diff --git a/OpenSim/Data/MySQL/MySQLRegionData.cs b/OpenSim/Data/MySQL/MySQLRegionData.cs
index a2d4ae4..2ad7590 100644
--- a/OpenSim/Data/MySQL/MySQLRegionData.cs
+++ b/OpenSim/Data/MySQL/MySQLRegionData.cs
@@ -310,6 +310,11 @@ namespace OpenSim.Data.MySQL
310 return Get((int)RegionFlags.DefaultRegion, scopeID); 310 return Get((int)RegionFlags.DefaultRegion, scopeID);
311 } 311 }
312 312
313 public List<RegionData> GetDefaultHypergridRegions(UUID scopeID)
314 {
315 return Get((int)RegionFlags.DefaultHGRegion, scopeID);
316 }
317
313 public List<RegionData> GetFallbackRegions(UUID scopeID, int x, int y) 318 public List<RegionData> GetFallbackRegions(UUID scopeID, int x, int y)
314 { 319 {
315 List<RegionData> regions = Get((int)RegionFlags.FallbackRegion, scopeID); 320 List<RegionData> regions = Get((int)RegionFlags.FallbackRegion, scopeID);
diff --git a/OpenSim/Data/MySQL/MySQLSimulationData.cs b/OpenSim/Data/MySQL/MySQLSimulationData.cs
index 537ec85..1b3e81e 100644
--- a/OpenSim/Data/MySQL/MySQLSimulationData.cs
+++ b/OpenSim/Data/MySQL/MySQLSimulationData.cs
@@ -174,7 +174,8 @@ namespace OpenSim.Data.MySQL
174 "CollisionSound, CollisionSoundVolume, " + 174 "CollisionSound, CollisionSoundVolume, " +
175 "PassTouches, " + 175 "PassTouches, " +
176 "PassCollisions, " + 176 "PassCollisions, " +
177 "LinkNumber, MediaURL, KeyframeMotion, " + 177 "LinkNumber, MediaURL, KeyframeMotion, AttachedPosX, " +
178 "AttachedPosY, AttachedPosZ, " +
178 "PhysicsShapeType, Density, GravityModifier, " + 179 "PhysicsShapeType, Density, GravityModifier, " +
179 "Friction, Restitution, Vehicle, DynAttrs " + 180 "Friction, Restitution, Vehicle, DynAttrs " +
180 ") values (" + "?UUID, " + 181 ") values (" + "?UUID, " +
@@ -209,7 +210,8 @@ namespace OpenSim.Data.MySQL
209 "?ColorB, ?ColorA, ?ParticleSystem, " + 210 "?ColorB, ?ColorA, ?ParticleSystem, " +
210 "?ClickAction, ?Material, ?CollisionSound, " + 211 "?ClickAction, ?Material, ?CollisionSound, " +
211 "?CollisionSoundVolume, ?PassTouches, ?PassCollisions, " + 212 "?CollisionSoundVolume, ?PassTouches, ?PassCollisions, " +
212 "?LinkNumber, ?MediaURL, ?KeyframeMotion, " + 213 "?LinkNumber, ?MediaURL, ?KeyframeMotion, ?AttachedPosX, " +
214 "?AttachedPosY, ?AttachedPosZ, " +
213 "?PhysicsShapeType, ?Density, ?GravityModifier, " + 215 "?PhysicsShapeType, ?Density, ?GravityModifier, " +
214 "?Friction, ?Restitution, ?Vehicle, ?DynAttrs)"; 216 "?Friction, ?Restitution, ?Vehicle, ?DynAttrs)";
215 217
@@ -228,7 +230,7 @@ namespace OpenSim.Data.MySQL
228 "PathTaperX, PathTaperY, PathTwist, " + 230 "PathTaperX, PathTaperY, PathTwist, " +
229 "PathTwistBegin, ProfileBegin, ProfileEnd, " + 231 "PathTwistBegin, ProfileBegin, ProfileEnd, " +
230 "ProfileCurve, ProfileHollow, Texture, " + 232 "ProfileCurve, ProfileHollow, Texture, " +
231 "ExtraParams, State, Media) " + 233 "ExtraParams, State, LastAttachPoint, Media) " +
232 "values (?UUID, " + 234 "values (?UUID, " +
233 "?Shape, ?ScaleX, ?ScaleY, ?ScaleZ, " + 235 "?Shape, ?ScaleX, ?ScaleY, ?ScaleZ, " +
234 "?PCode, ?PathBegin, ?PathEnd, " + 236 "?PCode, ?PathBegin, ?PathEnd, " +
@@ -240,7 +242,7 @@ namespace OpenSim.Data.MySQL
240 "?PathTwistBegin, ?ProfileBegin, " + 242 "?PathTwistBegin, ?ProfileBegin, " +
241 "?ProfileEnd, ?ProfileCurve, " + 243 "?ProfileEnd, ?ProfileCurve, " +
242 "?ProfileHollow, ?Texture, ?ExtraParams, " + 244 "?ProfileHollow, ?Texture, ?ExtraParams, " +
243 "?State, ?Media)"; 245 "?State, ?LastAttachPoint, ?Media)";
244 246
245 FillShapeCommand(cmd, prim); 247 FillShapeCommand(cmd, prim);
246 248
@@ -1320,7 +1322,16 @@ namespace OpenSim.Data.MySQL
1320 1322
1321 if (!(row["MediaURL"] is System.DBNull)) 1323 if (!(row["MediaURL"] is System.DBNull))
1322 prim.MediaUrl = (string)row["MediaURL"]; 1324 prim.MediaUrl = (string)row["MediaURL"];
1323 1325
1326 if (!(row["AttachedPosX"] is System.DBNull))
1327 {
1328 prim.AttachedPos = new Vector3(
1329 (float)(double)row["AttachedPosX"],
1330 (float)(double)row["AttachedPosY"],
1331 (float)(double)row["AttachedPosZ"]
1332 );
1333 }
1334
1324 if (!(row["DynAttrs"] is System.DBNull)) 1335 if (!(row["DynAttrs"] is System.DBNull))
1325 prim.DynAttrs = DAMap.FromXml((string)row["DynAttrs"]); 1336 prim.DynAttrs = DAMap.FromXml((string)row["DynAttrs"]);
1326 else 1337 else
@@ -1719,6 +1730,12 @@ namespace OpenSim.Data.MySQL
1719 1730
1720 cmd.Parameters.AddWithValue("LinkNumber", prim.LinkNum); 1731 cmd.Parameters.AddWithValue("LinkNumber", prim.LinkNum);
1721 cmd.Parameters.AddWithValue("MediaURL", prim.MediaUrl); 1732 cmd.Parameters.AddWithValue("MediaURL", prim.MediaUrl);
1733 if (prim.AttachedPos != null)
1734 {
1735 cmd.Parameters.AddWithValue("AttachedPosX", (double)prim.AttachedPos.X);
1736 cmd.Parameters.AddWithValue("AttachedPosY", (double)prim.AttachedPos.Y);
1737 cmd.Parameters.AddWithValue("AttachedPosZ", (double)prim.AttachedPos.Z);
1738 }
1722 1739
1723 if (prim.KeyframeMotion != null) 1740 if (prim.KeyframeMotion != null)
1724 cmd.Parameters.AddWithValue("KeyframeMotion", prim.KeyframeMotion.Serialize()); 1741 cmd.Parameters.AddWithValue("KeyframeMotion", prim.KeyframeMotion.Serialize());
@@ -1730,7 +1747,7 @@ namespace OpenSim.Data.MySQL
1730 else 1747 else
1731 cmd.Parameters.AddWithValue("Vehicle", String.Empty); 1748 cmd.Parameters.AddWithValue("Vehicle", String.Empty);
1732 1749
1733 if (prim.DynAttrs.Count > 0) 1750 if (prim.DynAttrs.CountNamespaces > 0)
1734 cmd.Parameters.AddWithValue("DynAttrs", prim.DynAttrs.ToXml()); 1751 cmd.Parameters.AddWithValue("DynAttrs", prim.DynAttrs.ToXml());
1735 else 1752 else
1736 cmd.Parameters.AddWithValue("DynAttrs", null); 1753 cmd.Parameters.AddWithValue("DynAttrs", null);
@@ -1932,6 +1949,7 @@ namespace OpenSim.Data.MySQL
1932 s.ExtraParams = (byte[])row["ExtraParams"]; 1949 s.ExtraParams = (byte[])row["ExtraParams"];
1933 1950
1934 s.State = (byte)(int)row["State"]; 1951 s.State = (byte)(int)row["State"];
1952 s.LastAttachPoint = (byte)(int)row["LastAttachPoint"];
1935 1953
1936 if (!(row["Media"] is System.DBNull)) 1954 if (!(row["Media"] is System.DBNull))
1937 s.Media = PrimitiveBaseShape.MediaList.FromXml((string)row["Media"]); 1955 s.Media = PrimitiveBaseShape.MediaList.FromXml((string)row["Media"]);
@@ -1978,6 +1996,7 @@ namespace OpenSim.Data.MySQL
1978 cmd.Parameters.AddWithValue("Texture", s.TextureEntry); 1996 cmd.Parameters.AddWithValue("Texture", s.TextureEntry);
1979 cmd.Parameters.AddWithValue("ExtraParams", s.ExtraParams); 1997 cmd.Parameters.AddWithValue("ExtraParams", s.ExtraParams);
1980 cmd.Parameters.AddWithValue("State", s.State); 1998 cmd.Parameters.AddWithValue("State", s.State);
1999 cmd.Parameters.AddWithValue("LastAttachPoint", s.LastAttachPoint);
1981 cmd.Parameters.AddWithValue("Media", null == s.Media ? null : s.Media.ToXml()); 2000 cmd.Parameters.AddWithValue("Media", null == s.Media ? null : s.Media.ToXml());
1982 } 2001 }
1983 2002
diff --git a/OpenSim/Data/MySQL/MySQLUserProfilesData.cs b/OpenSim/Data/MySQL/MySQLUserProfilesData.cs
new file mode 100644
index 0000000..8b50c54
--- /dev/null
+++ b/OpenSim/Data/MySQL/MySQLUserProfilesData.cs
@@ -0,0 +1,1102 @@
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.Data;
30using System.Reflection;
31using OpenSim.Data;
32using OpenSim.Framework;
33using MySql.Data.MySqlClient;
34using OpenMetaverse;
35using OpenMetaverse.StructuredData;
36using log4net;
37
38namespace OpenSim.Data.MySQL
39{
40 public class UserProfilesData: IProfilesData
41 {
42 static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
43
44 #region Properites
45 string ConnectionString
46 {
47 get; set;
48 }
49
50 protected object Lock
51 {
52 get; set;
53 }
54
55 protected virtual Assembly Assembly
56 {
57 get { return GetType().Assembly; }
58 }
59
60 #endregion Properties
61
62 #region class Member Functions
63 public UserProfilesData(string connectionString)
64 {
65 ConnectionString = connectionString;
66 Init();
67 }
68
69 void Init()
70 {
71 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
72 {
73 dbcon.Open();
74
75 Migration m = new Migration(dbcon, Assembly, "UserProfiles");
76 m.Update();
77 }
78 }
79 #endregion Member Functions
80
81 #region Classifieds Queries
82 /// <summary>
83 /// Gets the classified records.
84 /// </summary>
85 /// <returns>
86 /// Array of classified records
87 /// </returns>
88 /// <param name='creatorId'>
89 /// Creator identifier.
90 /// </param>
91 public OSDArray GetClassifiedRecords(UUID creatorId)
92 {
93 OSDArray data = new OSDArray();
94
95 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
96 {
97 string query = "SELECT classifieduuid, name FROM classifieds WHERE creatoruuid = ?Id";
98 dbcon.Open();
99 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
100 {
101 cmd.Parameters.AddWithValue("?Id", creatorId);
102 using( MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default))
103 {
104 if(reader.HasRows)
105 {
106 while (reader.Read())
107 {
108 OSDMap n = new OSDMap();
109 UUID Id = UUID.Zero;
110
111 string Name = null;
112 try
113 {
114 UUID.TryParse(Convert.ToString( reader["classifieduuid"]), out Id);
115 Name = Convert.ToString(reader["name"]);
116 }
117 catch (Exception e)
118 {
119 m_log.DebugFormat("[PROFILES_DATA]" +
120 ": UserAccount exception {0}", e.Message);
121 }
122 n.Add("classifieduuid", OSD.FromUUID(Id));
123 n.Add("name", OSD.FromString(Name));
124 data.Add(n);
125 }
126 }
127 }
128 }
129 }
130 return data;
131 }
132
133 public bool UpdateClassifiedRecord(UserClassifiedAdd ad, ref string result)
134 {
135 string query = string.Empty;
136
137
138 query += "INSERT INTO classifieds (";
139 query += "`classifieduuid`,";
140 query += "`creatoruuid`,";
141 query += "`creationdate`,";
142 query += "`expirationdate`,";
143 query += "`category`,";
144 query += "`name`,";
145 query += "`description`,";
146 query += "`parceluuid`,";
147 query += "`parentestate`,";
148 query += "`snapshotuuid`,";
149 query += "`simname`,";
150 query += "`posglobal`,";
151 query += "`parcelname`,";
152 query += "`classifiedflags`,";
153 query += "`priceforlisting`) ";
154 query += "VALUES (";
155 query += "?ClassifiedId,";
156 query += "?CreatorId,";
157 query += "?CreatedDate,";
158 query += "?ExpirationDate,";
159 query += "?Category,";
160 query += "?Name,";
161 query += "?Description,";
162 query += "?ParcelId,";
163 query += "?ParentEstate,";
164 query += "?SnapshotId,";
165 query += "?SimName,";
166 query += "?GlobalPos,";
167 query += "?ParcelName,";
168 query += "?Flags,";
169 query += "?ListingPrice ) ";
170 query += "ON DUPLICATE KEY UPDATE ";
171 query += "category=?Category, ";
172 query += "expirationdate=?ExpirationDate, ";
173 query += "name=?Name, ";
174 query += "description=?Description, ";
175 query += "parentestate=?ParentEstate, ";
176 query += "posglobal=?GlobalPos, ";
177 query += "parcelname=?ParcelName, ";
178 query += "classifiedflags=?Flags, ";
179 query += "priceforlisting=?ListingPrice, ";
180 query += "snapshotuuid=?SnapshotId";
181
182 if(string.IsNullOrEmpty(ad.ParcelName))
183 ad.ParcelName = "Unknown";
184 if(ad.ParcelId == null)
185 ad.ParcelId = UUID.Zero;
186 if(string.IsNullOrEmpty(ad.Description))
187 ad.Description = "No Description";
188
189 DateTime epoch = new DateTime(1970, 1, 1);
190 DateTime now = DateTime.Now;
191 TimeSpan epochnow = now - epoch;
192 TimeSpan duration;
193 DateTime expiration;
194 TimeSpan epochexp;
195
196 if(ad.Flags == 2)
197 {
198 duration = new TimeSpan(7,0,0,0);
199 expiration = now.Add(duration);
200 epochexp = expiration - epoch;
201 }
202 else
203 {
204 duration = new TimeSpan(365,0,0,0);
205 expiration = now.Add(duration);
206 epochexp = expiration - epoch;
207 }
208 ad.CreationDate = (int)epochnow.TotalSeconds;
209 ad.ExpirationDate = (int)epochexp.TotalSeconds;
210
211 try
212 {
213 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
214 {
215 dbcon.Open();
216 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
217 {
218 cmd.Parameters.AddWithValue("?ClassifiedId", ad.ClassifiedId.ToString());
219 cmd.Parameters.AddWithValue("?CreatorId", ad.CreatorId.ToString());
220 cmd.Parameters.AddWithValue("?CreatedDate", ad.CreationDate.ToString());
221 cmd.Parameters.AddWithValue("?ExpirationDate", ad.ExpirationDate.ToString());
222 cmd.Parameters.AddWithValue("?Category", ad.Category.ToString());
223 cmd.Parameters.AddWithValue("?Name", ad.Name.ToString());
224 cmd.Parameters.AddWithValue("?Description", ad.Description.ToString());
225 cmd.Parameters.AddWithValue("?ParcelId", ad.ParcelId.ToString());
226 cmd.Parameters.AddWithValue("?ParentEstate", ad.ParentEstate.ToString());
227 cmd.Parameters.AddWithValue("?SnapshotId", ad.SnapshotId.ToString ());
228 cmd.Parameters.AddWithValue("?SimName", ad.SimName.ToString());
229 cmd.Parameters.AddWithValue("?GlobalPos", ad.GlobalPos.ToString());
230 cmd.Parameters.AddWithValue("?ParcelName", ad.ParcelName.ToString());
231 cmd.Parameters.AddWithValue("?Flags", ad.Flags.ToString());
232 cmd.Parameters.AddWithValue("?ListingPrice", ad.Price.ToString ());
233
234 cmd.ExecuteNonQuery();
235 }
236 }
237 }
238 catch (Exception e)
239 {
240 m_log.DebugFormat("[PROFILES_DATA]" +
241 ": ClassifiedesUpdate exception {0}", e.Message);
242 result = e.Message;
243 return false;
244 }
245 return true;
246 }
247
248 public bool DeleteClassifiedRecord(UUID recordId)
249 {
250 string query = string.Empty;
251
252 query += "DELETE FROM classifieds WHERE ";
253 query += "classifieduuid = ?ClasifiedId";
254
255 try
256 {
257 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
258 {
259 dbcon.Open();
260
261 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
262 {
263 cmd.Parameters.AddWithValue("?ClassifiedId", recordId.ToString());
264
265 lock(Lock)
266 {
267 cmd.ExecuteNonQuery();
268 }
269 }
270 }
271 }
272 catch (Exception e)
273 {
274 m_log.DebugFormat("[PROFILES_DATA]" +
275 ": DeleteClassifiedRecord exception {0}", e.Message);
276 return false;
277 }
278 return true;
279 }
280
281 public bool GetClassifiedInfo(ref UserClassifiedAdd ad, ref string result)
282 {
283 string query = string.Empty;
284
285 query += "SELECT * FROM classifieds WHERE ";
286 query += "classifieduuid = ?AdId";
287
288 try
289 {
290 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
291 {
292 dbcon.Open();
293 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
294 {
295 cmd.Parameters.AddWithValue("?AdId", ad.ClassifiedId.ToString());
296
297 using (MySqlDataReader reader = cmd.ExecuteReader())
298 {
299 if(reader.Read ())
300 {
301 ad.CreatorId = new UUID(reader.GetGuid("creatoruuid"));
302 ad.ParcelId = new UUID(reader.GetGuid("parceluuid"));
303 ad.SnapshotId = new UUID(reader.GetGuid("snapshotuuid"));
304 ad.CreationDate = Convert.ToInt32(reader["creationdate"]);
305 ad.ExpirationDate = Convert.ToInt32(reader["expirationdate"]);
306 ad.ParentEstate = Convert.ToInt32(reader["parentestate"]);
307 ad.Flags = (byte)reader.GetUInt32("classifiedflags");
308 ad.Category = reader.GetInt32("category");
309 ad.Price = reader.GetInt16("priceforlisting");
310 ad.Name = reader.GetString("name");
311 ad.Description = reader.GetString("description");
312 ad.SimName = reader.GetString("simname");
313 ad.GlobalPos = reader.GetString("posglobal");
314 ad.ParcelName = reader.GetString("parcelname");
315
316 }
317 }
318 }
319 dbcon.Close();
320 }
321 }
322 catch (Exception e)
323 {
324 m_log.DebugFormat("[PROFILES_DATA]" +
325 ": GetPickInfo exception {0}", e.Message);
326 }
327 return true;
328 }
329 #endregion Classifieds Queries
330
331 #region Picks Queries
332 public OSDArray GetAvatarPicks(UUID avatarId)
333 {
334 string query = string.Empty;
335
336 query += "SELECT `pickuuid`,`name` FROM userpicks WHERE ";
337 query += "creatoruuid = ?Id";
338 OSDArray data = new OSDArray();
339
340 try
341 {
342 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
343 {
344 dbcon.Open();
345 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
346 {
347 cmd.Parameters.AddWithValue("?Id", avatarId.ToString());
348
349 using (MySqlDataReader reader = cmd.ExecuteReader())
350 {
351 if(reader.HasRows)
352 {
353 while (reader.Read())
354 {
355 OSDMap record = new OSDMap();
356
357 record.Add("pickuuid",OSD.FromString((string)reader["pickuuid"]));
358 record.Add("name",OSD.FromString((string)reader["name"]));
359 data.Add(record);
360 }
361 }
362 }
363 }
364 }
365 }
366 catch (Exception e)
367 {
368 m_log.DebugFormat("[PROFILES_DATA]" +
369 ": GetAvatarPicks exception {0}", e.Message);
370 }
371 return data;
372 }
373
374 public UserProfilePick GetPickInfo(UUID avatarId, UUID pickId)
375 {
376 string query = string.Empty;
377 UserProfilePick pick = new UserProfilePick();
378
379 query += "SELECT * FROM userpicks WHERE ";
380 query += "creatoruuid = ?CreatorId AND ";
381 query += "pickuuid = ?PickId";
382
383 try
384 {
385 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
386 {
387 dbcon.Open();
388 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
389 {
390 cmd.Parameters.AddWithValue("?CreatorId", avatarId.ToString());
391 cmd.Parameters.AddWithValue("?PickId", pickId.ToString());
392
393 using (MySqlDataReader reader = cmd.ExecuteReader())
394 {
395 if(reader.HasRows)
396 {
397 reader.Read();
398
399 string description = (string)reader["description"];
400
401 if (string.IsNullOrEmpty(description))
402 description = "No description given.";
403
404 UUID.TryParse((string)reader["pickuuid"], out pick.PickId);
405 UUID.TryParse((string)reader["creatoruuid"], out pick.CreatorId);
406 UUID.TryParse((string)reader["parceluuid"], out pick.ParcelId);
407 UUID.TryParse((string)reader["snapshotuuid"], out pick.SnapshotId);
408 pick.GlobalPos = (string)reader["posglobal"];
409 bool.TryParse((string)reader["toppick"], out pick.TopPick);
410 bool.TryParse((string)reader["enabled"], out pick.Enabled);
411 pick.Name = (string)reader["name"];
412 pick.Desc = description;
413 pick.User = (string)reader["user"];
414 pick.OriginalName = (string)reader["originalname"];
415 pick.SimName = (string)reader["simname"];
416 pick.SortOrder = (int)reader["sortorder"];
417 }
418 }
419 }
420 dbcon.Close();
421 }
422 }
423 catch (Exception e)
424 {
425 m_log.DebugFormat("[PROFILES_DATA]" +
426 ": GetPickInfo exception {0}", e.Message);
427 }
428 return pick;
429 }
430
431 public bool UpdatePicksRecord(UserProfilePick pick)
432 {
433 string query = string.Empty;
434
435 query += "INSERT INTO userpicks VALUES (";
436 query += "?PickId,";
437 query += "?CreatorId,";
438 query += "?TopPick,";
439 query += "?ParcelId,";
440 query += "?Name,";
441 query += "?Desc,";
442 query += "?SnapshotId,";
443 query += "?User,";
444 query += "?Original,";
445 query += "?SimName,";
446 query += "?GlobalPos,";
447 query += "?SortOrder,";
448 query += "?Enabled) ";
449 query += "ON DUPLICATE KEY UPDATE ";
450 query += "parceluuid=?ParcelId,";
451 query += "name=?Name,";
452 query += "description=?Desc,";
453 query += "snapshotuuid=?SnapshotId,";
454 query += "pickuuid=?PickId,";
455 query += "posglobal=?GlobalPos";
456
457 try
458 {
459 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
460 {
461 dbcon.Open();
462 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
463 {
464 cmd.Parameters.AddWithValue("?PickId", pick.PickId.ToString());
465 cmd.Parameters.AddWithValue("?CreatorId", pick.CreatorId.ToString());
466 cmd.Parameters.AddWithValue("?TopPick", pick.TopPick.ToString());
467 cmd.Parameters.AddWithValue("?ParcelId", pick.ParcelId.ToString());
468 cmd.Parameters.AddWithValue("?Name", pick.Name.ToString());
469 cmd.Parameters.AddWithValue("?Desc", pick.Desc.ToString());
470 cmd.Parameters.AddWithValue("?SnapshotId", pick.SnapshotId.ToString());
471 cmd.Parameters.AddWithValue("?User", pick.User.ToString());
472 cmd.Parameters.AddWithValue("?Original", pick.OriginalName.ToString());
473 cmd.Parameters.AddWithValue("?SimName",pick.SimName.ToString());
474 cmd.Parameters.AddWithValue("?GlobalPos", pick.GlobalPos);
475 cmd.Parameters.AddWithValue("?SortOrder", pick.SortOrder.ToString ());
476 cmd.Parameters.AddWithValue("?Enabled", pick.Enabled.ToString());
477
478 cmd.ExecuteNonQuery();
479 }
480 }
481 }
482 catch (Exception e)
483 {
484 m_log.DebugFormat("[PROFILES_DATA]" +
485 ": UpdateAvatarNotes exception {0}", e.Message);
486 return false;
487 }
488 return true;
489 }
490
491 public bool DeletePicksRecord(UUID pickId)
492 {
493 string query = string.Empty;
494
495 query += "DELETE FROM userpicks WHERE ";
496 query += "pickuuid = ?PickId";
497
498 try
499 {
500 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
501 {
502 dbcon.Open();
503
504 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
505 {
506 cmd.Parameters.AddWithValue("?PickId", pickId.ToString());
507
508 cmd.ExecuteNonQuery();
509 }
510 }
511 }
512 catch (Exception e)
513 {
514 m_log.DebugFormat("[PROFILES_DATA]" +
515 ": DeleteUserPickRecord exception {0}", e.Message);
516 return false;
517 }
518 return true;
519 }
520 #endregion Picks Queries
521
522 #region Avatar Notes Queries
523 public bool GetAvatarNotes(ref UserProfileNotes notes)
524 { // WIP
525 string query = string.Empty;
526
527 query += "SELECT `notes` FROM usernotes WHERE ";
528 query += "useruuid = ?Id AND ";
529 query += "targetuuid = ?TargetId";
530 OSDArray data = new OSDArray();
531
532 try
533 {
534 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
535 {
536 dbcon.Open();
537 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
538 {
539 cmd.Parameters.AddWithValue("?Id", notes.UserId.ToString());
540 cmd.Parameters.AddWithValue("?TargetId", notes.TargetId.ToString());
541
542 using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
543 {
544 if(reader.HasRows)
545 {
546 reader.Read();
547 notes.Notes = OSD.FromString((string)reader["notes"]);
548 }
549 else
550 {
551 notes.Notes = OSD.FromString("");
552 }
553 }
554 }
555 }
556 }
557 catch (Exception e)
558 {
559 m_log.DebugFormat("[PROFILES_DATA]" +
560 ": GetAvatarNotes exception {0}", e.Message);
561 }
562 return true;
563 }
564
565 public bool UpdateAvatarNotes(ref UserProfileNotes note, ref string result)
566 {
567 string query = string.Empty;
568 bool remove;
569
570 if(string.IsNullOrEmpty(note.Notes))
571 {
572 remove = true;
573 query += "DELETE FROM usernotes WHERE ";
574 query += "useruuid=?UserId AND ";
575 query += "targetuuid=?TargetId";
576 }
577 else
578 {
579 remove = false;
580 query += "INSERT INTO usernotes VALUES ( ";
581 query += "?UserId,";
582 query += "?TargetId,";
583 query += "?Notes )";
584 query += "ON DUPLICATE KEY ";
585 query += "UPDATE ";
586 query += "notes=?Notes";
587 }
588
589 try
590 {
591 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
592 {
593 dbcon.Open();
594 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
595 {
596 if(!remove)
597 cmd.Parameters.AddWithValue("?Notes", note.Notes);
598 cmd.Parameters.AddWithValue("?TargetId", note.TargetId.ToString ());
599 cmd.Parameters.AddWithValue("?UserId", note.UserId.ToString());
600
601 cmd.ExecuteNonQuery();
602 }
603 }
604 }
605 catch (Exception e)
606 {
607 m_log.DebugFormat("[PROFILES_DATA]" +
608 ": UpdateAvatarNotes exception {0}", e.Message);
609 return false;
610 }
611 return true;
612
613 }
614 #endregion Avatar Notes Queries
615
616 #region Avatar Properties
617 public bool GetAvatarProperties(ref UserProfileProperties props, ref string result)
618 {
619 string query = string.Empty;
620
621 query += "SELECT * FROM userprofile WHERE ";
622 query += "useruuid = ?Id";
623
624 try
625 {
626 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
627 {
628 dbcon.Open();
629 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
630 {
631 cmd.Parameters.AddWithValue("?Id", props.UserId.ToString());
632
633 using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
634 {
635 if(reader.HasRows)
636 {
637 m_log.DebugFormat("[PROFILES_DATA]" +
638 ": Getting data for {0}.", props.UserId);
639 reader.Read();
640 props.WebUrl = (string)reader["profileURL"];
641 UUID.TryParse((string)reader["profileImage"], out props.ImageId);
642 props.AboutText = (string)reader["profileAboutText"];
643 UUID.TryParse((string)reader["profileFirstImage"], out props.FirstLifeImageId);
644 props.FirstLifeText = (string)reader["profileFirstText"];
645 UUID.TryParse((string)reader["profilePartner"], out props.PartnerId);
646 props.WantToMask = (int)reader["profileWantToMask"];
647 props.WantToText = (string)reader["profileWantToText"];
648 props.SkillsMask = (int)reader["profileSkillsMask"];
649 props.SkillsText = (string)reader["profileSkillsText"];
650 props.Language = (string)reader["profileLanguages"];
651 }
652 else
653 {
654 m_log.DebugFormat("[PROFILES_DATA]" +
655 ": No data for {0}", props.UserId);
656
657 props.WebUrl = string.Empty;
658 props.ImageId = UUID.Zero;
659 props.AboutText = string.Empty;
660 props.FirstLifeImageId = UUID.Zero;
661 props.FirstLifeText = string.Empty;
662 props.PartnerId = UUID.Zero;
663 props.WantToMask = 0;
664 props.WantToText = string.Empty;
665 props.SkillsMask = 0;
666 props.SkillsText = string.Empty;
667 props.Language = string.Empty;
668 props.PublishProfile = false;
669 props.PublishMature = false;
670
671 query = "INSERT INTO userprofile (";
672 query += "useruuid, ";
673 query += "profilePartner, ";
674 query += "profileAllowPublish, ";
675 query += "profileMaturePublish, ";
676 query += "profileURL, ";
677 query += "profileWantToMask, ";
678 query += "profileWantToText, ";
679 query += "profileSkillsMask, ";
680 query += "profileSkillsText, ";
681 query += "profileLanguages, ";
682 query += "profileImage, ";
683 query += "profileAboutText, ";
684 query += "profileFirstImage, ";
685 query += "profileFirstText) VALUES (";
686 query += "?userId, ";
687 query += "?profilePartner, ";
688 query += "?profileAllowPublish, ";
689 query += "?profileMaturePublish, ";
690 query += "?profileURL, ";
691 query += "?profileWantToMask, ";
692 query += "?profileWantToText, ";
693 query += "?profileSkillsMask, ";
694 query += "?profileSkillsText, ";
695 query += "?profileLanguages, ";
696 query += "?profileImage, ";
697 query += "?profileAboutText, ";
698 query += "?profileFirstImage, ";
699 query += "?profileFirstText)";
700
701 dbcon.Close();
702 dbcon.Open();
703
704 using (MySqlCommand put = new MySqlCommand(query, dbcon))
705 {
706 put.Parameters.AddWithValue("?userId", props.UserId.ToString());
707 put.Parameters.AddWithValue("?profilePartner", props.PartnerId.ToString());
708 put.Parameters.AddWithValue("?profileAllowPublish", props.PublishProfile);
709 put.Parameters.AddWithValue("?profileMaturePublish", props.PublishMature);
710 put.Parameters.AddWithValue("?profileURL", props.WebUrl);
711 put.Parameters.AddWithValue("?profileWantToMask", props.WantToMask);
712 put.Parameters.AddWithValue("?profileWantToText", props.WantToText);
713 put.Parameters.AddWithValue("?profileSkillsMask", props.SkillsMask);
714 put.Parameters.AddWithValue("?profileSkillsText", props.SkillsText);
715 put.Parameters.AddWithValue("?profileLanguages", props.Language);
716 put.Parameters.AddWithValue("?profileImage", props.ImageId.ToString());
717 put.Parameters.AddWithValue("?profileAboutText", props.AboutText);
718 put.Parameters.AddWithValue("?profileFirstImage", props.FirstLifeImageId.ToString());
719 put.Parameters.AddWithValue("?profileFirstText", props.FirstLifeText);
720
721 put.ExecuteNonQuery();
722 }
723 }
724 }
725 }
726 }
727 }
728 catch (Exception e)
729 {
730 m_log.DebugFormat("[PROFILES_DATA]" +
731 ": Requst properties exception {0}", e.Message);
732 result = e.Message;
733 return false;
734 }
735 return true;
736 }
737
738 public bool UpdateAvatarProperties(ref UserProfileProperties props, ref string result)
739 {
740 string query = string.Empty;
741
742 query += "UPDATE userprofile SET ";
743 query += "profileURL=?profileURL, ";
744 query += "profileImage=?image, ";
745 query += "profileAboutText=?abouttext,";
746 query += "profileFirstImage=?firstlifeimage,";
747 query += "profileFirstText=?firstlifetext ";
748 query += "WHERE useruuid=?uuid";
749
750 try
751 {
752 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
753 {
754 dbcon.Open();
755 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
756 {
757 cmd.Parameters.AddWithValue("?profileURL", props.WebUrl);
758 cmd.Parameters.AddWithValue("?image", props.ImageId.ToString());
759 cmd.Parameters.AddWithValue("?abouttext", props.AboutText);
760 cmd.Parameters.AddWithValue("?firstlifeimage", props.FirstLifeImageId.ToString());
761 cmd.Parameters.AddWithValue("?firstlifetext", props.FirstLifeText);
762 cmd.Parameters.AddWithValue("?uuid", props.UserId.ToString());
763
764 cmd.ExecuteNonQuery();
765 }
766 }
767 }
768 catch (Exception e)
769 {
770 m_log.DebugFormat("[PROFILES_DATA]" +
771 ": AgentPropertiesUpdate exception {0}", e.Message);
772
773 return false;
774 }
775 return true;
776 }
777 #endregion Avatar Properties
778
779 #region Avatar Interests
780 public bool UpdateAvatarInterests(UserProfileProperties up, ref string result)
781 {
782 string query = string.Empty;
783
784 query += "UPDATE userprofile SET ";
785 query += "profileWantToMask=?WantMask, ";
786 query += "profileWantToText=?WantText,";
787 query += "profileSkillsMask=?SkillsMask,";
788 query += "profileSkillsText=?SkillsText, ";
789 query += "profileLanguages=?Languages ";
790 query += "WHERE useruuid=?uuid";
791
792 try
793 {
794 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
795 {
796 dbcon.Open();
797 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
798 {
799 cmd.Parameters.AddWithValue("?WantMask", up.WantToMask);
800 cmd.Parameters.AddWithValue("?WantText", up.WantToText);
801 cmd.Parameters.AddWithValue("?SkillsMask", up.SkillsMask);
802 cmd.Parameters.AddWithValue("?SkillsText", up.SkillsText);
803 cmd.Parameters.AddWithValue("?Languages", up.Language);
804 cmd.Parameters.AddWithValue("?uuid", up.UserId.ToString());
805
806 cmd.ExecuteNonQuery();
807 }
808 }
809 }
810 catch (Exception e)
811 {
812 m_log.DebugFormat("[PROFILES_DATA]" +
813 ": AgentInterestsUpdate exception {0}", e.Message);
814 result = e.Message;
815 return false;
816 }
817 return true;
818 }
819 #endregion Avatar Interests
820
821 public OSDArray GetUserImageAssets(UUID avatarId)
822 {
823 OSDArray data = new OSDArray();
824 string query = "SELECT `snapshotuuid` FROM {0} WHERE `creatoruuid` = ?Id";
825
826 // Get classified image assets
827
828
829 try
830 {
831 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
832 {
833 dbcon.Open();
834
835 using (MySqlCommand cmd = new MySqlCommand(string.Format (query,"`classifieds`"), dbcon))
836 {
837 cmd.Parameters.AddWithValue("?Id", avatarId.ToString());
838
839 using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
840 {
841 if(reader.HasRows)
842 {
843 while (reader.Read())
844 {
845 data.Add(new OSDString((string)reader["snapshotuuid"].ToString ()));
846 }
847 }
848 }
849 }
850
851 dbcon.Close();
852 dbcon.Open();
853
854 using (MySqlCommand cmd = new MySqlCommand(string.Format (query,"`userpicks`"), dbcon))
855 {
856 cmd.Parameters.AddWithValue("?Id", avatarId.ToString());
857
858 using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
859 {
860 if(reader.HasRows)
861 {
862 while (reader.Read())
863 {
864 data.Add(new OSDString((string)reader["snapshotuuid"].ToString ()));
865 }
866 }
867 }
868 }
869
870 dbcon.Close();
871 dbcon.Open();
872
873 query = "SELECT `profileImage`, `profileFirstImage` FROM `userprofile` WHERE `useruuid` = ?Id";
874
875 using (MySqlCommand cmd = new MySqlCommand(string.Format (query,"`userpicks`"), dbcon))
876 {
877 cmd.Parameters.AddWithValue("?Id", avatarId.ToString());
878
879 using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
880 {
881 if(reader.HasRows)
882 {
883 while (reader.Read())
884 {
885 data.Add(new OSDString((string)reader["profileImage"].ToString ()));
886 data.Add(new OSDString((string)reader["profileFirstImage"].ToString ()));
887 }
888 }
889 }
890 }
891 }
892 }
893 catch (Exception e)
894 {
895 m_log.DebugFormat("[PROFILES_DATA]" +
896 ": GetAvatarNotes exception {0}", e.Message);
897 }
898 return data;
899 }
900
901 #region User Preferences
902 public OSDArray GetUserPreferences(UUID avatarId)
903 {
904 string query = string.Empty;
905
906 query += "SELECT imviaemail,visible,email FROM ";
907 query += "usersettings WHERE ";
908 query += "useruuid = ?Id";
909
910 OSDArray data = new OSDArray();
911
912 try
913 {
914 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
915 {
916 dbcon.Open();
917 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
918 {
919 cmd.Parameters.AddWithValue("?Id", avatarId.ToString());
920
921 using (MySqlDataReader reader = cmd.ExecuteReader())
922 {
923 if(reader.HasRows)
924 {
925 reader.Read();
926 OSDMap record = new OSDMap();
927
928 record.Add("imviaemail",OSD.FromString((string)reader["imviaemail"]));
929 record.Add("visible",OSD.FromString((string)reader["visible"]));
930 record.Add("email",OSD.FromString((string)reader["email"]));
931 data.Add(record);
932 }
933 else
934 {
935 dbcon.Close();
936 dbcon.Open();
937
938 query = "INSERT INTO usersettings VALUES ";
939 query += "(?uuid,'false','false', ?Email)";
940
941 using (MySqlCommand put = new MySqlCommand(query, dbcon))
942 {
943
944// put.Parameters.AddWithValue("?Email", pref.EMail);
945// put.Parameters.AddWithValue("?uuid", pref.UserId.ToString());
946
947 put.ExecuteNonQuery();
948 }
949 }
950 }
951 }
952 }
953 }
954 catch (Exception e)
955 {
956 m_log.DebugFormat("[PROFILES_DATA]" +
957 ": Get preferences exception {0}", e.Message);
958 }
959 return data;
960 }
961
962 public bool UpdateUserPreferences(bool emailIm, bool visible, UUID avatarId )
963 {
964 string query = string.Empty;
965
966 query += "UPDATE userpsettings SET ";
967 query += "imviaemail=?ImViaEmail, ";
968 query += "visible=?Visible,";
969 query += "WHERE useruuid=?uuid";
970
971 try
972 {
973 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
974 {
975 dbcon.Open();
976 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
977 {
978 cmd.Parameters.AddWithValue("?ImViaEmail", emailIm.ToString().ToLower ());
979 cmd.Parameters.AddWithValue("?WantText", visible.ToString().ToLower ());
980 cmd.Parameters.AddWithValue("?uuid", avatarId.ToString());
981
982 lock(Lock)
983 {
984 cmd.ExecuteNonQuery();
985 }
986 }
987 }
988 }
989 catch (Exception e)
990 {
991 m_log.DebugFormat("[PROFILES_DATA]" +
992 ": AgentInterestsUpdate exception {0}", e.Message);
993 return false;
994 }
995 return true;
996 }
997 #endregion User Preferences
998
999 #region Integration
1000 public bool GetUserAppData(ref UserAppData props, ref string result)
1001 {
1002 string query = string.Empty;
1003
1004 query += "SELECT * FROM `userdata` WHERE ";
1005 query += "UserId = ?Id AND ";
1006 query += "TagId = ?TagId";
1007
1008 try
1009 {
1010 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
1011 {
1012 dbcon.Open();
1013 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
1014 {
1015 cmd.Parameters.AddWithValue("?Id", props.UserId.ToString());
1016 cmd.Parameters.AddWithValue ("?TagId", props.TagId.ToString());
1017
1018 using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
1019 {
1020 if(reader.HasRows)
1021 {
1022 reader.Read();
1023 props.DataKey = (string)reader["DataKey"];
1024 props.DataVal = (string)reader["DataVal"];
1025 }
1026 else
1027 {
1028 query += "INSERT INTO userdata VALUES ( ";
1029 query += "?UserId,";
1030 query += "?TagId,";
1031 query += "?DataKey,";
1032 query += "?DataVal) ";
1033
1034 using (MySqlCommand put = new MySqlCommand(query, dbcon))
1035 {
1036 put.Parameters.AddWithValue("?Id", props.UserId.ToString());
1037 put.Parameters.AddWithValue("?TagId", props.TagId.ToString());
1038 put.Parameters.AddWithValue("?DataKey", props.DataKey.ToString());
1039 put.Parameters.AddWithValue("?DataVal", props.DataVal.ToString());
1040
1041 lock(Lock)
1042 {
1043 put.ExecuteNonQuery();
1044 }
1045 }
1046 }
1047 }
1048 }
1049 }
1050 }
1051 catch (Exception e)
1052 {
1053 m_log.DebugFormat("[PROFILES_DATA]" +
1054 ": Requst application data exception {0}", e.Message);
1055 result = e.Message;
1056 return false;
1057 }
1058 return true;
1059 }
1060
1061 public bool SetUserAppData(UserAppData props, ref string result)
1062 {
1063 string query = string.Empty;
1064
1065 query += "UPDATE userdata SET ";
1066 query += "TagId = ?TagId, ";
1067 query += "DataKey = ?DataKey, ";
1068 query += "DataVal = ?DataVal WHERE ";
1069 query += "UserId = ?UserId AND ";
1070 query += "TagId = ?TagId";
1071
1072 try
1073 {
1074 using (MySqlConnection dbcon = new MySqlConnection(ConnectionString))
1075 {
1076 dbcon.Open();
1077 using (MySqlCommand cmd = new MySqlCommand(query, dbcon))
1078 {
1079 cmd.Parameters.AddWithValue("?UserId", props.UserId.ToString());
1080 cmd.Parameters.AddWithValue("?TagId", props.TagId.ToString ());
1081 cmd.Parameters.AddWithValue("?DataKey", props.DataKey.ToString ());
1082 cmd.Parameters.AddWithValue("?DataVal", props.DataKey.ToString ());
1083
1084 lock(Lock)
1085 {
1086 cmd.ExecuteNonQuery();
1087 }
1088 }
1089 }
1090 }
1091 catch (Exception e)
1092 {
1093 m_log.DebugFormat("[PROFILES_DATA]" +
1094 ": SetUserData exception {0}", e.Message);
1095 return false;
1096 }
1097 return true;
1098 }
1099 #endregion Integration
1100 }
1101}
1102
diff --git a/OpenSim/Data/MySQL/MySQLXAssetData.cs b/OpenSim/Data/MySQL/MySQLXAssetData.cs
index 15ac921..5f1d2ee 100644
--- a/OpenSim/Data/MySQL/MySQLXAssetData.cs
+++ b/OpenSim/Data/MySQL/MySQLXAssetData.cs
@@ -199,6 +199,8 @@ namespace OpenSim.Data.MySQL
199 /// <remarks>On failure : Throw an exception and attempt to reconnect to database</remarks> 199 /// <remarks>On failure : Throw an exception and attempt to reconnect to database</remarks>
200 public void StoreAsset(AssetBase asset) 200 public void StoreAsset(AssetBase asset)
201 { 201 {
202// m_log.DebugFormat("[XASSETS DB]: Storing asset {0} {1}", asset.Name, asset.ID);
203
202 lock (m_dbLock) 204 lock (m_dbLock)
203 { 205 {
204 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) 206 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
diff --git a/OpenSim/Data/MySQL/Properties/AssemblyInfo.cs b/OpenSim/Data/MySQL/Properties/AssemblyInfo.cs
index 1146d92..f562300 100644
--- a/OpenSim/Data/MySQL/Properties/AssemblyInfo.cs
+++ b/OpenSim/Data/MySQL/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.0.*")]
65 65
diff --git a/OpenSim/Data/MySQL/Resources/EstateStore.migrations b/OpenSim/Data/MySQL/Resources/EstateStore.migrations
index df82a2e..2d1c2b5 100644
--- a/OpenSim/Data/MySQL/Resources/EstateStore.migrations
+++ b/OpenSim/Data/MySQL/Resources/EstateStore.migrations
@@ -77,5 +77,11 @@ BEGIN;
77ALTER TABLE estate_settings AUTO_INCREMENT = 100; 77ALTER TABLE estate_settings AUTO_INCREMENT = 100;
78COMMIT; 78COMMIT;
79 79
80:VERSION 33 #---------------------
80 81
82BEGIN;
83ALTER TABLE estate_settings ADD COLUMN `AllowLandmark` tinyint(4) NOT NULL default '1';
84ALTER TABLE estate_settings ADD COLUMN `AllowParcelChanges` tinyint(4) NOT NULL default '1';
85ALTER TABLE estate_settings ADD COLUMN `AllowSetHome` tinyint(4) NOT NULL default '1';
86COMMIT;
81 87
diff --git a/OpenSim/Data/MySQL/Resources/FriendsStore.migrations b/OpenSim/Data/MySQL/Resources/FriendsStore.migrations
index 55d82ec..5faf956 100644
--- a/OpenSim/Data/MySQL/Resources/FriendsStore.migrations
+++ b/OpenSim/Data/MySQL/Resources/FriendsStore.migrations
@@ -9,7 +9,7 @@ CREATE TABLE `Friends` (
9 `Offered` VARCHAR(32) NOT NULL DEFAULT 0, 9 `Offered` VARCHAR(32) NOT NULL DEFAULT 0,
10 PRIMARY KEY(`PrincipalID`, `Friend`), 10 PRIMARY KEY(`PrincipalID`, `Friend`),
11 KEY(`PrincipalID`) 11 KEY(`PrincipalID`)
12); 12) ENGINE=InnoDB;
13 13
14COMMIT; 14COMMIT;
15 15
diff --git a/OpenSim/Data/MySQL/Resources/HGTravelStore.migrations b/OpenSim/Data/MySQL/Resources/HGTravelStore.migrations
new file mode 100644
index 0000000..b4e4422
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/HGTravelStore.migrations
@@ -0,0 +1,18 @@
1:VERSION 1 # --------------------------
2
3BEGIN;
4
5CREATE TABLE `hg_traveling_data` (
6 `SessionID` VARCHAR(36) NOT NULL,
7 `UserID` VARCHAR(36) NOT NULL,
8 `GridExternalName` VARCHAR(255) NOT NULL DEFAULT '',
9 `ServiceToken` VARCHAR(255) NOT NULL DEFAULT '',
10 `ClientIPAddress` VARCHAR(16) NOT NULL DEFAULT '',
11 `MyIPAddress` VARCHAR(16) NOT NULL DEFAULT '',
12 `TMStamp` timestamp NOT NULL,
13 PRIMARY KEY (`SessionID`),
14 KEY (`UserID`)
15) ENGINE=InnoDB;
16
17COMMIT;
18
diff --git a/OpenSim/Data/MySQL/Resources/IM_Store.migrations b/OpenSim/Data/MySQL/Resources/IM_Store.migrations
index 7cfcd43..f73475e 100644
--- a/OpenSim/Data/MySQL/Resources/IM_Store.migrations
+++ b/OpenSim/Data/MySQL/Resources/IM_Store.migrations
@@ -21,4 +21,14 @@ INSERT INTO `im_offline` SELECT * from `diva_im_offline`;
21DROP TABLE `diva_im_offline`; 21DROP TABLE `diva_im_offline`;
22DELETE FROM `migrations` WHERE name='diva_im_Store'; 22DELETE FROM `migrations` WHERE name='diva_im_Store';
23 23
24COMMIT; \ No newline at end of file 24COMMIT;
25
26:VERSION 3 # --------------------------
27
28BEGIN;
29
30ALTER TABLE `im_offline`
31 ADD `FromID` char(36) NOT NULL default '' AFTER `PrincipalID`,
32 ADD KEY `FromID` (`FromID`);
33
34COMMIT;
diff --git a/OpenSim/Data/MySQL/Resources/RegionStore.migrations b/OpenSim/Data/MySQL/Resources/RegionStore.migrations
index bda1b6a..c2e3afe 100644
--- a/OpenSim/Data/MySQL/Resources/RegionStore.migrations
+++ b/OpenSim/Data/MySQL/Resources/RegionStore.migrations
@@ -922,3 +922,20 @@ ALTER TABLE prims ADD COLUMN `Friction` double NOT NULL default '0.6';
922ALTER TABLE prims ADD COLUMN `Restitution` double NOT NULL default '0.5'; 922ALTER TABLE prims ADD COLUMN `Restitution` double NOT NULL default '0.5';
923 923
924COMMIT; 924COMMIT;
925
926:VERSION 48 #---------------- Keyframes
927
928BEGIN;
929
930ALTER TABLE prims ADD COLUMN `KeyframeMotion` blob;
931
932COMMIT;
933
934:VERSION 49 #--------------------- Save attachment info
935
936BEGIN;
937ALTER TABLE prims ADD COLUMN AttachedPosX double default 0;
938ALTER TABLE prims ADD COLUMN AttachedPosY double default 0;
939ALTER TABLE prims ADD COLUMN AttachedPosZ double default 0;
940ALTER TABLE primshapes ADD COLUMN LastAttachPoint int(4) not null default '0';
941COMMIT;
diff --git a/OpenSim/Data/MySQL/Resources/UserProfiles.migrations b/OpenSim/Data/MySQL/Resources/UserProfiles.migrations
new file mode 100644
index 0000000..c29f1ab
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/UserProfiles.migrations
@@ -0,0 +1,83 @@
1:VERSION 1 # -------------------------------
2
3begin;
4
5CREATE TABLE IF NOT EXISTS `classifieds` (
6 `classifieduuid` char(36) NOT NULL,
7 `creatoruuid` char(36) NOT NULL,
8 `creationdate` int(20) NOT NULL,
9 `expirationdate` int(20) NOT NULL,
10 `category` varchar(20) NOT NULL,
11 `name` varchar(255) NOT NULL,
12 `description` text NOT NULL,
13 `parceluuid` char(36) NOT NULL,
14 `parentestate` int(11) NOT NULL,
15 `snapshotuuid` char(36) NOT NULL,
16 `simname` varchar(255) NOT NULL,
17 `posglobal` varchar(255) NOT NULL,
18 `parcelname` varchar(255) NOT NULL,
19 `classifiedflags` int(8) NOT NULL,
20 `priceforlisting` int(5) NOT NULL,
21 PRIMARY KEY (`classifieduuid`)
22) ENGINE=InnoDB DEFAULT CHARSET=latin1;
23
24
25CREATE TABLE IF NOT EXISTS `usernotes` (
26 `useruuid` varchar(36) NOT NULL,
27 `targetuuid` varchar(36) NOT NULL,
28 `notes` text NOT NULL,
29 UNIQUE KEY `useruuid` (`useruuid`,`targetuuid`)
30) ENGINE=MyISAM DEFAULT CHARSET=latin1;
31
32
33CREATE TABLE IF NOT EXISTS `userpicks` (
34 `pickuuid` varchar(36) NOT NULL,
35 `creatoruuid` varchar(36) NOT NULL,
36 `toppick` enum('true','false') NOT NULL,
37 `parceluuid` varchar(36) NOT NULL,
38 `name` varchar(255) NOT NULL,
39 `description` text NOT NULL,
40 `snapshotuuid` varchar(36) NOT NULL,
41 `user` varchar(255) NOT NULL,
42 `originalname` varchar(255) NOT NULL,
43 `simname` varchar(255) NOT NULL,
44 `posglobal` varchar(255) NOT NULL,
45 `sortorder` int(2) NOT NULL,
46 `enabled` enum('true','false') NOT NULL,
47 PRIMARY KEY (`pickuuid`)
48) ENGINE=MyISAM DEFAULT CHARSET=latin1;
49
50
51CREATE TABLE IF NOT EXISTS `userprofile` (
52 `useruuid` varchar(36) NOT NULL,
53 `profilePartner` varchar(36) NOT NULL,
54 `profileAllowPublish` binary(1) NOT NULL,
55 `profileMaturePublish` binary(1) NOT NULL,
56 `profileURL` varchar(255) NOT NULL,
57 `profileWantToMask` int(3) NOT NULL,
58 `profileWantToText` text NOT NULL,
59 `profileSkillsMask` int(3) NOT NULL,
60 `profileSkillsText` text NOT NULL,
61 `profileLanguages` text NOT NULL,
62 `profileImage` varchar(36) NOT NULL,
63 `profileAboutText` text NOT NULL,
64 `profileFirstImage` varchar(36) NOT NULL,
65 `profileFirstText` text NOT NULL,
66 PRIMARY KEY (`useruuid`)
67) ENGINE=MyISAM DEFAULT CHARSET=latin1;
68
69commit;
70
71:VERSION 2 # -------------------------------
72
73begin;
74CREATE TABLE IF NOT EXISTS `userdata` (
75 `UserId` char(36) NOT NULL,
76 `TagId` varchar(64) NOT NULL,
77 `DataKey` varchar(255),
78 `DataVal` varchar(255),
79 PRIMARY KEY (`UserId`,`TagId`)
80) ENGINE=MyISAM DEFAULT CHARSET=latin1;
81
82commit;
83
generated by cgit v1.1 at 2024-08-24 02:21:51 +0000