aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/SQLite
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--OpenSim/Data/SQLite/Properties/AssemblyInfo.cs4
-rw-r--r--OpenSim/Data/SQLite/Resources/AgentPrefs.migrations36
-rw-r--r--OpenSim/Data/SQLite/Resources/EstateStore.migrations9
-rw-r--r--OpenSim/Data/SQLite/Resources/HGTravelStore.migrations18
-rw-r--r--OpenSim/Data/SQLite/Resources/RegionStore.migrations37
-rw-r--r--OpenSim/Data/SQLite/Resources/UserProfiles.migrations102
-rw-r--r--OpenSim/Data/SQLite/SQLiteAgentPreferencesData.cs60
-rw-r--r--OpenSim/Data/SQLite/SQLiteAssetData.cs67
-rw-r--r--OpenSim/Data/SQLite/SQLiteFriendsData.cs2
-rw-r--r--OpenSim/Data/SQLite/SQLiteGridUserData.cs4
-rw-r--r--OpenSim/Data/SQLite/SQLiteHGTravelData.cs82
-rw-r--r--OpenSim/Data/SQLite/SQLiteInventoryStore.cs4
-rw-r--r--OpenSim/Data/SQLite/SQLiteSimulationData.cs183
-rw-r--r--OpenSim/Data/SQLite/SQLiteUserProfilesData.cs981
14 files changed, 1491 insertions, 98 deletions
diff --git a/OpenSim/Data/SQLite/Properties/AssemblyInfo.cs b/OpenSim/Data/SQLite/Properties/AssemblyInfo.cs
index c9a8553..d2e62d2 100644
--- a/OpenSim/Data/SQLite/Properties/AssemblyInfo.cs
+++ b/OpenSim/Data/SQLite/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.5.*")] 64[assembly : AssemblyVersion("0.8.2.*")]
65[assembly : AssemblyFileVersion("0.6.5.0")] 65
diff --git a/OpenSim/Data/SQLite/Resources/AgentPrefs.migrations b/OpenSim/Data/SQLite/Resources/AgentPrefs.migrations
new file mode 100644
index 0000000..7e0525d
--- /dev/null
+++ b/OpenSim/Data/SQLite/Resources/AgentPrefs.migrations
@@ -0,0 +1,36 @@
1:VERSION 1
2
3BEGIN TRANSACTION;
4
5CREATE TABLE `AgentPrefs` (
6 `PrincipalID` CHAR(36) NOT NULL,
7 `AccessPrefs` CHAR(2) NOT NULL DEFAULT 'M',
8 `HoverHeight` DOUBLE(30, 27) NOT NULL DEFAULT 0,
9 `Language` CHAR(5) NOT NULL DEFAULT 'en-us',
10 `LanguageIsPublic` BOOLEAN NOT NULL DEFAULT 1,
11 `PermEveryone` INT(6) NOT NULL DEFAULT 0,
12 `PermGroup` INT(6) NOT NULL DEFAULT 0,
13 `PermNextOwner` INT(6) NOT NULL DEFAULT 532480,
14 UNIQUE KEY `PrincipalID` (`PrincipalID`),
15 PRIMARY KEY(`PrincipalID`));
16
17COMMIT;
18
19:VERSION 2
20
21BEGIN;
22
23CREATE TABLE AgentPrefs(
24 PrincipalID CHAR(36) NOT NULL,
25 AccessPrefs CHAR(2) NOT NULL DEFAULT 'M',
26 HoverHeight DOUBLE(30, 27) NOT NULL DEFAULT 0,
27 Language CHAR(5) NOT NULL DEFAULT 'en-us',
28 LanguageIsPublic BOOLEAN NOT NULL DEFAULT 1,
29 PermEveryone INT(6) NOT NULL DEFAULT 0,
30 PermGroup INT(6) NOT NULL DEFAULT 0,
31 PermNextOwner INT(6) NOT NULL DEFAULT 532480,
32 UNIQUE(PrincipalID),
33 PRIMARY KEY(PrincipalID)
34);
35
36COMMIT;
diff --git a/OpenSim/Data/SQLite/Resources/EstateStore.migrations b/OpenSim/Data/SQLite/Resources/EstateStore.migrations
index 62f6464..0aec49b 100644
--- a/OpenSim/Data/SQLite/Resources/EstateStore.migrations
+++ b/OpenSim/Data/SQLite/Resources/EstateStore.migrations
@@ -86,3 +86,12 @@ begin;
86alter table estate_settings add column DenyMinors tinyint not null default 0; 86alter table estate_settings add column DenyMinors tinyint not null default 0;
87 87
88commit; 88commit;
89
90:VERSION 9
91
92begin;
93alter table estate_settings add column AllowLandmark tinyint not null default '1';
94alter table estate_settings add column AllowParcelChanges tinyint not null default '1';
95alter table estate_settings add column AllowSetHome tinyint not null default '1';
96commit;
97
diff --git a/OpenSim/Data/SQLite/Resources/HGTravelStore.migrations b/OpenSim/Data/SQLite/Resources/HGTravelStore.migrations
new file mode 100644
index 0000000..02612ce
--- /dev/null
+++ b/OpenSim/Data/SQLite/Resources/HGTravelStore.migrations
@@ -0,0 +1,18 @@
1:VERSION 2 # --------------------------
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 DEFAULT CURRENT_TIMESTAMP,
13 PRIMARY KEY(SessionID),
14 UNIQUE(UserID)
15);
16
17COMMIT;
18
diff --git a/OpenSim/Data/SQLite/Resources/RegionStore.migrations b/OpenSim/Data/SQLite/Resources/RegionStore.migrations
index e872977..901068f 100644
--- a/OpenSim/Data/SQLite/Resources/RegionStore.migrations
+++ b/OpenSim/Data/SQLite/Resources/RegionStore.migrations
@@ -575,3 +575,40 @@ CREATE TABLE `regionenvironment` (
575); 575);
576 576
577COMMIT; 577COMMIT;
578
579:VERSION 27
580BEGIN;
581ALTER TABLE prims ADD COLUMN DynAttrs TEXT;
582COMMIT;
583
584:VERSION 28
585
586BEGIN;
587
588ALTER TABLE prims ADD COLUMN `PhysicsShapeType` tinyint(4) NOT NULL default '0';
589ALTER TABLE prims ADD COLUMN `Density` double NOT NULL default '1000';
590ALTER TABLE prims ADD COLUMN `GravityModifier` double NOT NULL default '1';
591ALTER TABLE prims ADD COLUMN `Friction` double NOT NULL default '0.6';
592ALTER TABLE prims ADD COLUMN `Restitution` double NOT NULL default '0.5';
593
594COMMIT;
595
596:VERSION 29 #---------------- Keyframes
597
598BEGIN;
599
600ALTER TABLE prims ADD COLUMN `KeyframeMotion` blob;
601
602COMMIT;
603
604:VERSION 30 #---------------- Save Attachment info
605
606BEGIN;
607
608ALTER TABLE prims ADD COLUMN AttachedPosX double default '0';
609ALTER TABLE prims ADD COLUMN AttachedPosY double default '0';
610ALTER TABLE prims ADD COLUMN AttachedPosZ double default '0';
611ALTER TABLE primshapes ADD COLUMN LastAttachPoint int not null default '0';
612
613COMMIT;
614
diff --git a/OpenSim/Data/SQLite/Resources/UserProfiles.migrations b/OpenSim/Data/SQLite/Resources/UserProfiles.migrations
new file mode 100644
index 0000000..86434e8
--- /dev/null
+++ b/OpenSim/Data/SQLite/Resources/UserProfiles.migrations
@@ -0,0 +1,102 @@
1:VERSION 1 # -------------------------------
2
3begin;
4
5CREATE TABLE IF NOT EXISTS classifieds (
6 classifieduuid char(36) NOT NULL PRIMARY KEY,
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);
22
23commit;
24
25begin;
26
27CREATE TABLE IF NOT EXISTS usernotes (
28 useruuid varchar(36) NOT NULL,
29 targetuuid varchar(36) NOT NULL,
30 notes text NOT NULL,
31 UNIQUE (useruuid,targetuuid) ON CONFLICT REPLACE
32);
33
34commit;
35
36begin;
37
38CREATE TABLE IF NOT EXISTS userpicks (
39 pickuuid varchar(36) NOT NULL PRIMARY KEY,
40 creatoruuid varchar(36) NOT NULL,
41 toppick int NOT NULL,
42 parceluuid varchar(36) NOT NULL,
43 name varchar(255) NOT NULL,
44 description text NOT NULL,
45 snapshotuuid varchar(36) NOT NULL,
46 user varchar(255) NOT NULL,
47 originalname varchar(255) NOT NULL,
48 simname varchar(255) NOT NULL,
49 posglobal varchar(255) NOT NULL,
50 sortorder int(2) NOT NULL,
51 enabled int NOT NULL
52);
53
54commit;
55
56begin;
57
58CREATE TABLE IF NOT EXISTS userprofile (
59 useruuid varchar(36) NOT NULL PRIMARY KEY,
60 profilePartner varchar(36) NOT NULL,
61 profileAllowPublish binary(1) NOT NULL,
62 profileMaturePublish binary(1) NOT NULL,
63 profileURL varchar(255) NOT NULL,
64 profileWantToMask int(3) NOT NULL,
65 profileWantToText text NOT NULL,
66 profileSkillsMask int(3) NOT NULL,
67 profileSkillsText text NOT NULL,
68 profileLanguages text NOT NULL,
69 profileImage varchar(36) NOT NULL,
70 profileAboutText text NOT NULL,
71 profileFirstImage varchar(36) NOT NULL,
72 profileFirstText text NOT NULL
73);
74
75commit;
76
77:VERSION 2 # -------------------------------
78
79begin;
80
81CREATE TABLE IF NOT EXISTS userdata (
82 UserId char(36) NOT NULL,
83 TagId varchar(64) NOT NULL,
84 DataKey varchar(255),
85 DataVal varchar(255),
86 PRIMARY KEY (UserId,TagId)
87);
88
89commit;
90
91
92:VERSION 3 # -------------------------------
93
94begin;
95CREATE TABLE IF NOT EXISTS usersettings (
96 useruuid char(36) NOT NULL,
97 imviaemail binary(1) NOT NULL,
98 visible binary(1) NOT NULL,
99 email varchar(254) NOT NULL,
100 PRIMARY KEY (useruuid)
101)
102commit; \ No newline at end of file
diff --git a/OpenSim/Data/SQLite/SQLiteAgentPreferencesData.cs b/OpenSim/Data/SQLite/SQLiteAgentPreferencesData.cs
new file mode 100644
index 0000000..d22393d
--- /dev/null
+++ b/OpenSim/Data/SQLite/SQLiteAgentPreferencesData.cs
@@ -0,0 +1,60 @@
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;
34#if CSharpSqlite
35 using Community.CsharpSqlite.Sqlite;
36#else
37 using Mono.Data.Sqlite;
38#endif
39
40namespace OpenSim.Data.SQLite
41{
42 public class SQLiteAgentPreferencesData : SQLiteGenericTableHandler<AgentPreferencesData>, IAgentPreferencesData
43 {
44 public SQLiteAgentPreferencesData(string connectionString, string realm)
45 : base(connectionString, realm, "AgentPrefs")
46 {
47 }
48
49 public AgentPreferencesData GetPrefs(UUID agentID)
50 {
51 AgentPreferencesData[] ret = Get("PrincipalID", agentID.ToString());
52
53 if (ret.Length == 0)
54 return null;
55
56 return ret[0];
57 }
58
59 }
60}
diff --git a/OpenSim/Data/SQLite/SQLiteAssetData.cs b/OpenSim/Data/SQLite/SQLiteAssetData.cs
index 61e7aaf..f0dda64 100644
--- a/OpenSim/Data/SQLite/SQLiteAssetData.cs
+++ b/OpenSim/Data/SQLite/SQLiteAssetData.cs
@@ -46,7 +46,7 @@ namespace OpenSim.Data.SQLite
46 /// </summary> 46 /// </summary>
47 public class SQLiteAssetData : AssetDataBase 47 public class SQLiteAssetData : AssetDataBase
48 { 48 {
49// private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); 49 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
50 50
51 private const string SelectAssetSQL = "select * from assets where UUID=:UUID"; 51 private const string SelectAssetSQL = "select * from assets where UUID=:UUID";
52 private const string SelectAssetMetadataSQL = "select Name, Description, Type, Temporary, asset_flags, UUID, CreatorID from assets limit :start, :count"; 52 private const string SelectAssetMetadataSQL = "select Name, Description, Type, Temporary, asset_flags, UUID, CreatorID from assets limit :start, :count";
@@ -133,8 +133,26 @@ namespace OpenSim.Data.SQLite
133 /// <param name="asset">Asset Base</param> 133 /// <param name="asset">Asset Base</param>
134 override public void StoreAsset(AssetBase asset) 134 override public void StoreAsset(AssetBase asset)
135 { 135 {
136 string assetName = asset.Name;
137 if (asset.Name.Length > AssetBase.MAX_ASSET_NAME)
138 {
139 assetName = asset.Name.Substring(0, AssetBase.MAX_ASSET_NAME);
140 m_log.WarnFormat(
141 "[ASSET DB]: Name '{0}' for asset {1} truncated from {2} to {3} characters on add",
142 asset.Name, asset.ID, asset.Name.Length, assetName.Length);
143 }
144
145 string assetDescription = asset.Description;
146 if (asset.Description.Length > AssetBase.MAX_ASSET_DESC)
147 {
148 assetDescription = asset.Description.Substring(0, AssetBase.MAX_ASSET_DESC);
149 m_log.WarnFormat(
150 "[ASSET DB]: Description '{0}' for asset {1} truncated from {2} to {3} characters on add",
151 asset.Description, asset.ID, asset.Description.Length, assetDescription.Length);
152 }
153
136 //m_log.Info("[ASSET DB]: Creating Asset " + asset.FullID.ToString()); 154 //m_log.Info("[ASSET DB]: Creating Asset " + asset.FullID.ToString());
137 if (ExistsAsset(asset.FullID)) 155 if (AssetsExist(new[] { asset.FullID })[0])
138 { 156 {
139 //LogAssetLoad(asset); 157 //LogAssetLoad(asset);
140 158
@@ -143,8 +161,8 @@ namespace OpenSim.Data.SQLite
143 using (SqliteCommand cmd = new SqliteCommand(UpdateAssetSQL, m_conn)) 161 using (SqliteCommand cmd = new SqliteCommand(UpdateAssetSQL, m_conn))
144 { 162 {
145 cmd.Parameters.Add(new SqliteParameter(":UUID", asset.FullID.ToString())); 163 cmd.Parameters.Add(new SqliteParameter(":UUID", asset.FullID.ToString()));
146 cmd.Parameters.Add(new SqliteParameter(":Name", asset.Name)); 164 cmd.Parameters.Add(new SqliteParameter(":Name", assetName));
147 cmd.Parameters.Add(new SqliteParameter(":Description", asset.Description)); 165 cmd.Parameters.Add(new SqliteParameter(":Description", assetDescription));
148 cmd.Parameters.Add(new SqliteParameter(":Type", asset.Type)); 166 cmd.Parameters.Add(new SqliteParameter(":Type", asset.Type));
149 cmd.Parameters.Add(new SqliteParameter(":Local", asset.Local)); 167 cmd.Parameters.Add(new SqliteParameter(":Local", asset.Local));
150 cmd.Parameters.Add(new SqliteParameter(":Temporary", asset.Temporary)); 168 cmd.Parameters.Add(new SqliteParameter(":Temporary", asset.Temporary));
@@ -163,8 +181,8 @@ namespace OpenSim.Data.SQLite
163 using (SqliteCommand cmd = new SqliteCommand(InsertAssetSQL, m_conn)) 181 using (SqliteCommand cmd = new SqliteCommand(InsertAssetSQL, m_conn))
164 { 182 {
165 cmd.Parameters.Add(new SqliteParameter(":UUID", asset.FullID.ToString())); 183 cmd.Parameters.Add(new SqliteParameter(":UUID", asset.FullID.ToString()));
166 cmd.Parameters.Add(new SqliteParameter(":Name", asset.Name)); 184 cmd.Parameters.Add(new SqliteParameter(":Name", assetName));
167 cmd.Parameters.Add(new SqliteParameter(":Description", asset.Description)); 185 cmd.Parameters.Add(new SqliteParameter(":Description", assetDescription));
168 cmd.Parameters.Add(new SqliteParameter(":Type", asset.Type)); 186 cmd.Parameters.Add(new SqliteParameter(":Type", asset.Type));
169 cmd.Parameters.Add(new SqliteParameter(":Local", asset.Local)); 187 cmd.Parameters.Add(new SqliteParameter(":Local", asset.Local));
170 cmd.Parameters.Add(new SqliteParameter(":Temporary", asset.Temporary)); 188 cmd.Parameters.Add(new SqliteParameter(":Temporary", asset.Temporary));
@@ -196,32 +214,39 @@ namespace OpenSim.Data.SQLite
196// } 214// }
197 215
198 /// <summary> 216 /// <summary>
199 /// Check if an asset exist in database 217 /// Check if the assets exist in the database.
200 /// </summary> 218 /// </summary>
201 /// <param name="uuid">The asset UUID</param> 219 /// <param name="uuids">The assets' IDs</param>
202 /// <returns>True if exist, or false.</returns> 220 /// <returns>For each asset: true if it exists, false otherwise</returns>
203 override public bool ExistsAsset(UUID uuid) 221 public override bool[] AssetsExist(UUID[] uuids)
204 { 222 {
205 lock (this) 223 if (uuids.Length == 0)
224 return new bool[0];
225
226 HashSet<UUID> exist = new HashSet<UUID>();
227
228 string ids = "'" + string.Join("','", uuids) + "'";
229 string sql = string.Format("select UUID from assets where UUID in ({0})", ids);
230
231 lock (this)
206 { 232 {
207 using (SqliteCommand cmd = new SqliteCommand(SelectAssetSQL, m_conn)) 233 using (SqliteCommand cmd = new SqliteCommand(sql, m_conn))
208 { 234 {
209 cmd.Parameters.Add(new SqliteParameter(":UUID", uuid.ToString()));
210 using (IDataReader reader = cmd.ExecuteReader()) 235 using (IDataReader reader = cmd.ExecuteReader())
211 { 236 {
212 if (reader.Read()) 237 while (reader.Read())
213 {
214 reader.Close();
215 return true;
216 }
217 else
218 { 238 {
219 reader.Close(); 239 UUID id = new UUID((string)reader["UUID"]);
220 return false; 240 exist.Add(id);
221 } 241 }
222 } 242 }
223 } 243 }
224 } 244 }
245
246 bool[] results = new bool[uuids.Length];
247 for (int i = 0; i < uuids.Length; i++)
248 results[i] = exist.Contains(uuids[i]);
249 return results;
225 } 250 }
226 251
227 /// <summary> 252 /// <summary>
diff --git a/OpenSim/Data/SQLite/SQLiteFriendsData.cs b/OpenSim/Data/SQLite/SQLiteFriendsData.cs
index cab85eb..331f426 100644
--- a/OpenSim/Data/SQLite/SQLiteFriendsData.cs
+++ b/OpenSim/Data/SQLite/SQLiteFriendsData.cs
@@ -67,7 +67,7 @@ namespace OpenSim.Data.SQLite
67 return Delete(principalID.ToString(), friend); 67 return Delete(principalID.ToString(), friend);
68 } 68 }
69 69
70 public bool Delete(string principalID, string friend) 70 public override bool Delete(string principalID, string friend)
71 { 71 {
72 using (SqliteCommand cmd = new SqliteCommand()) 72 using (SqliteCommand cmd = new SqliteCommand())
73 { 73 {
diff --git a/OpenSim/Data/SQLite/SQLiteGridUserData.cs b/OpenSim/Data/SQLite/SQLiteGridUserData.cs
index 1bb5ed8..d8c52f8 100644
--- a/OpenSim/Data/SQLite/SQLiteGridUserData.cs
+++ b/OpenSim/Data/SQLite/SQLiteGridUserData.cs
@@ -56,6 +56,10 @@ namespace OpenSim.Data.SQLite
56 return ret[0]; 56 return ret[0];
57 } 57 }
58 58
59 public GridUserData[] GetAll(string userID)
60 {
61 return base.Get(String.Format("UserID LIKE '{0}%'", userID));
62 }
59 63
60 } 64 }
61} \ No newline at end of file 65} \ No newline at end of file
diff --git a/OpenSim/Data/SQLite/SQLiteHGTravelData.cs b/OpenSim/Data/SQLite/SQLiteHGTravelData.cs
new file mode 100644
index 0000000..db288b2
--- /dev/null
+++ b/OpenSim/Data/SQLite/SQLiteHGTravelData.cs
@@ -0,0 +1,82 @@
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 Mono.Data.Sqlite;
37
38namespace OpenSim.Data.SQLite
39{
40 /// <summary>
41 /// A SQL Interface for user grid data
42 /// </summary>
43 public class SQLiteHGTravelData : SQLiteGenericTableHandler<HGTravelingData>, IHGTravelingData
44 {
45// private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
46
47 public SQLiteHGTravelData(string connectionString, string realm)
48 : base(connectionString, realm, "HGTravelStore") {}
49
50 public HGTravelingData Get(UUID sessionID)
51 {
52 HGTravelingData[] ret = Get("SessionID", sessionID.ToString());
53
54 if (ret.Length == 0)
55 return null;
56
57 return ret[0];
58 }
59
60 public HGTravelingData[] GetSessions(UUID userID)
61 {
62 return base.Get("UserID", userID.ToString());
63 }
64
65 public bool Delete(UUID sessionID)
66 {
67 return Delete("SessionID", sessionID.ToString());
68 }
69
70 public void DeleteOld()
71 {
72 using (SqliteCommand cmd = new SqliteCommand())
73 {
74 cmd.CommandText = String.Format("delete from {0} where TMStamp < datetime('now', '-2 day') ", m_Realm);
75
76 DoQuery(cmd);
77 }
78
79 }
80
81 }
82} \ No newline at end of file
diff --git a/OpenSim/Data/SQLite/SQLiteInventoryStore.cs b/OpenSim/Data/SQLite/SQLiteInventoryStore.cs
index ccbd154..7d493ca 100644
--- a/OpenSim/Data/SQLite/SQLiteInventoryStore.cs
+++ b/OpenSim/Data/SQLite/SQLiteInventoryStore.cs
@@ -239,7 +239,7 @@ namespace OpenSim.Data.SQLite
239 if (inventoryRow == null) 239 if (inventoryRow == null)
240 { 240 {
241 if (! add) 241 if (! add)
242 m_log.ErrorFormat("Interface Misuse: Attempting to Update non-existant inventory folder: {0}", folder.ID); 242 m_log.ErrorFormat("Interface Misuse: Attempting to Update non-existent inventory folder: {0}", folder.ID);
243 243
244 inventoryRow = inventoryFolderTable.NewRow(); 244 inventoryRow = inventoryFolderTable.NewRow();
245 fillFolderRow(inventoryRow, folder); 245 fillFolderRow(inventoryRow, folder);
@@ -298,7 +298,7 @@ namespace OpenSim.Data.SQLite
298 if (inventoryRow == null) 298 if (inventoryRow == null)
299 { 299 {
300 if (!add) 300 if (!add)
301 m_log.ErrorFormat("[INVENTORY DB]: Interface Misuse: Attempting to Update non-existant inventory item: {0}", item.ID); 301 m_log.ErrorFormat("[INVENTORY DB]: Interface Misuse: Attempting to Update non-existent inventory item: {0}", item.ID);
302 302
303 inventoryRow = inventoryItemTable.NewRow(); 303 inventoryRow = inventoryItemTable.NewRow();
304 fillItemRow(inventoryRow, item); 304 fillItemRow(inventoryRow, item);
diff --git a/OpenSim/Data/SQLite/SQLiteSimulationData.cs b/OpenSim/Data/SQLite/SQLiteSimulationData.cs
index 29cac3c..6ed3d40 100644
--- a/OpenSim/Data/SQLite/SQLiteSimulationData.cs
+++ b/OpenSim/Data/SQLite/SQLiteSimulationData.cs
@@ -51,6 +51,7 @@ namespace OpenSim.Data.SQLite
51 public class SQLiteSimulationData : ISimulationDataStore 51 public class SQLiteSimulationData : ISimulationDataStore
52 { 52 {
53 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); 53 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
54 private static readonly string LogHeader = "[REGION DB SQLITE]";
54 55
55 private const string primSelect = "select * from prims"; 56 private const string primSelect = "select * from prims";
56 private const string shapeSelect = "select * from primshapes"; 57 private const string shapeSelect = "select * from primshapes";
@@ -732,9 +733,12 @@ namespace OpenSim.Data.SQLite
732 } 733 }
733 734
734 SceneObjectGroup group = new SceneObjectGroup(prim); 735 SceneObjectGroup group = new SceneObjectGroup(prim);
736
735 createdObjects.Add(group.UUID, group); 737 createdObjects.Add(group.UUID, group);
736 retvals.Add(group); 738 retvals.Add(group);
737 LoadItems(prim); 739 LoadItems(prim);
740
741
738 } 742 }
739 } 743 }
740 catch (Exception e) 744 catch (Exception e)
@@ -816,45 +820,44 @@ namespace OpenSim.Data.SQLite
816 prim.Inventory.RestoreInventoryItems(inventory); 820 prim.Inventory.RestoreInventoryItems(inventory);
817 } 821 }
818 822
823 // Legacy entry point for when terrain was always a 256x256 hieghtmap
824 public void StoreTerrain(double[,] ter, UUID regionID)
825 {
826 StoreTerrain(new HeightmapTerrainData(ter), regionID);
827 }
828
819 /// <summary> 829 /// <summary>
820 /// Store a terrain revision in region storage 830 /// Store a terrain revision in region storage
821 /// </summary> 831 /// </summary>
822 /// <param name="ter">terrain heightfield</param> 832 /// <param name="ter">terrain heightfield</param>
823 /// <param name="regionID">region UUID</param> 833 /// <param name="regionID">region UUID</param>
824 public void StoreTerrain(double[,] ter, UUID regionID) 834 public void StoreTerrain(TerrainData terrData, UUID regionID)
825 { 835 {
826 lock (ds) 836 lock (ds)
827 { 837 {
828 int revision = Util.UnixTimeSinceEpoch();
829
830 // This is added to get rid of the infinitely growing
831 // terrain databases which negatively impact on SQLite
832 // over time. Before reenabling this feature there
833 // needs to be a limitter put on the number of
834 // revisions in the database, as this old
835 // implementation is a DOS attack waiting to happen.
836
837 using ( 838 using (
838 SqliteCommand cmd = 839 SqliteCommand cmd = new SqliteCommand("delete from terrain where RegionUUID=:RegionUUID", m_conn))
839 new SqliteCommand("delete from terrain where RegionUUID=:RegionUUID and Revision <= :Revision",
840 m_conn))
841 { 840 {
842 cmd.Parameters.Add(new SqliteParameter(":RegionUUID", regionID.ToString())); 841 cmd.Parameters.Add(new SqliteParameter(":RegionUUID", regionID.ToString()));
843 cmd.Parameters.Add(new SqliteParameter(":Revision", revision));
844 cmd.ExecuteNonQuery(); 842 cmd.ExecuteNonQuery();
845 } 843 }
846 844
847 // the following is an work around for .NET. The perf 845 // the following is an work around for .NET. The perf
848 // issues associated with it aren't as bad as you think. 846 // issues associated with it aren't as bad as you think.
849 m_log.Debug("[SQLITE REGION DB]: Storing terrain revision r" + revision.ToString());
850 String sql = "insert into terrain(RegionUUID, Revision, Heightfield)" + 847 String sql = "insert into terrain(RegionUUID, Revision, Heightfield)" +
851 " values(:RegionUUID, :Revision, :Heightfield)"; 848 " values(:RegionUUID, :Revision, :Heightfield)";
852 849
850 int terrainDBRevision;
851 Array terrainDBblob;
852 terrData.GetDatabaseBlob(out terrainDBRevision, out terrainDBblob);
853
854 m_log.DebugFormat("{0} Storing terrain revision r {1}", LogHeader, terrainDBRevision);
855
853 using (SqliteCommand cmd = new SqliteCommand(sql, m_conn)) 856 using (SqliteCommand cmd = new SqliteCommand(sql, m_conn))
854 { 857 {
855 cmd.Parameters.Add(new SqliteParameter(":RegionUUID", regionID.ToString())); 858 cmd.Parameters.Add(new SqliteParameter(":RegionUUID", regionID.ToString()));
856 cmd.Parameters.Add(new SqliteParameter(":Revision", revision)); 859 cmd.Parameters.Add(new SqliteParameter(":Revision", terrainDBRevision));
857 cmd.Parameters.Add(new SqliteParameter(":Heightfield", serializeTerrain(ter))); 860 cmd.Parameters.Add(new SqliteParameter(":Heightfield", terrainDBblob));
858 cmd.ExecuteNonQuery(); 861 cmd.ExecuteNonQuery();
859 } 862 }
860 } 863 }
@@ -867,11 +870,20 @@ namespace OpenSim.Data.SQLite
867 /// <returns>Heightfield data</returns> 870 /// <returns>Heightfield data</returns>
868 public double[,] LoadTerrain(UUID regionID) 871 public double[,] LoadTerrain(UUID regionID)
869 { 872 {
873 double[,] ret = null;
874 TerrainData terrData = LoadTerrain(regionID, (int)Constants.RegionSize, (int)Constants.RegionSize, (int)Constants.RegionHeight);
875 if (terrData != null)
876 ret = terrData.GetDoubles();
877 return ret;
878 }
879
880 // Returns 'null' if region not found
881 public TerrainData LoadTerrain(UUID regionID, int pSizeX, int pSizeY, int pSizeZ)
882 {
883 TerrainData terrData = null;
884
870 lock (ds) 885 lock (ds)
871 { 886 {
872 double[,] terret = new double[(int)Constants.RegionSize, (int)Constants.RegionSize];
873 terret.Initialize();
874
875 String sql = "select RegionUUID, Revision, Heightfield from terrain" + 887 String sql = "select RegionUUID, Revision, Heightfield from terrain" +
876 " where RegionUUID=:RegionUUID order by Revision desc"; 888 " where RegionUUID=:RegionUUID order by Revision desc";
877 889
@@ -884,21 +896,9 @@ namespace OpenSim.Data.SQLite
884 int rev = 0; 896 int rev = 0;
885 if (row.Read()) 897 if (row.Read())
886 { 898 {
887 // TODO: put this into a function
888 using (MemoryStream str = new MemoryStream((byte[])row["Heightfield"]))
889 {
890 using (BinaryReader br = new BinaryReader(str))
891 {
892 for (int x = 0; x < (int)Constants.RegionSize; x++)
893 {
894 for (int y = 0; y < (int)Constants.RegionSize; y++)
895 {
896 terret[x, y] = br.ReadDouble();
897 }
898 }
899 }
900 }
901 rev = Convert.ToInt32(row["Revision"]); 899 rev = Convert.ToInt32(row["Revision"]);
900 byte[] blob = (byte[])row["Heightfield"];
901 terrData = TerrainData.CreateFromDatabaseBlobFactory(pSizeX, pSizeY, pSizeZ, rev, blob);
902 } 902 }
903 else 903 else
904 { 904 {
@@ -909,8 +909,8 @@ namespace OpenSim.Data.SQLite
909 m_log.Debug("[SQLITE REGION DB]: Loaded terrain revision r" + rev.ToString()); 909 m_log.Debug("[SQLITE REGION DB]: Loaded terrain revision r" + rev.ToString());
910 } 910 }
911 } 911 }
912 return terret;
913 } 912 }
913 return terrData;
914 } 914 }
915 915
916 public void RemoveLandObject(UUID globalID) 916 public void RemoveLandObject(UUID globalID)
@@ -1232,7 +1232,20 @@ namespace OpenSim.Data.SQLite
1232 createCol(prims, "VolumeDetect", typeof(Int16)); 1232 createCol(prims, "VolumeDetect", typeof(Int16));
1233 1233
1234 createCol(prims, "MediaURL", typeof(String)); 1234 createCol(prims, "MediaURL", typeof(String));
1235
1236 createCol(prims, "AttachedPosX", typeof(Double));
1237 createCol(prims, "AttachedPosY", typeof(Double));
1238 createCol(prims, "AttachedPosZ", typeof(Double));
1239
1240 createCol(prims, "DynAttrs", typeof(String));
1241
1242 createCol(prims, "PhysicsShapeType", typeof(Byte));
1243 createCol(prims, "Density", typeof(Double));
1244 createCol(prims, "GravityModifier", typeof(Double));
1245 createCol(prims, "Friction", typeof(Double));
1246 createCol(prims, "Restitution", typeof(Double));
1235 1247
1248 createCol(prims, "KeyframeMotion", typeof(Byte[]));
1236 // Add in contraints 1249 // Add in contraints
1237 prims.PrimaryKey = new DataColumn[] { prims.Columns["UUID"] }; 1250 prims.PrimaryKey = new DataColumn[] { prims.Columns["UUID"] };
1238 1251
@@ -1592,7 +1605,7 @@ namespace OpenSim.Data.SQLite
1592 prim.SitName = (String)row["SitName"]; 1605 prim.SitName = (String)row["SitName"];
1593 prim.TouchName = (String)row["TouchName"]; 1606 prim.TouchName = (String)row["TouchName"];
1594 // permissions 1607 // permissions
1595 prim.ObjectFlags = Convert.ToUInt32(row["ObjectFlags"]); 1608 prim.Flags = (PrimFlags)Convert.ToUInt32(row["ObjectFlags"]);
1596 prim.CreatorIdentification = (String)row["CreatorID"]; 1609 prim.CreatorIdentification = (String)row["CreatorID"];
1597 prim.OwnerID = new UUID((String)row["OwnerID"]); 1610 prim.OwnerID = new UUID((String)row["OwnerID"]);
1598 prim.GroupID = new UUID((String)row["GroupID"]); 1611 prim.GroupID = new UUID((String)row["GroupID"]);
@@ -1711,7 +1724,43 @@ namespace OpenSim.Data.SQLite
1711// m_log.DebugFormat("[SQLITE]: MediaUrl type [{0}]", row["MediaURL"].GetType()); 1724// m_log.DebugFormat("[SQLITE]: MediaUrl type [{0}]", row["MediaURL"].GetType());
1712 prim.MediaUrl = (string)row["MediaURL"]; 1725 prim.MediaUrl = (string)row["MediaURL"];
1713 } 1726 }
1727
1728 prim.AttachedPos = new Vector3(
1729 Convert.ToSingle(row["AttachedPosX"]),
1730 Convert.ToSingle(row["AttachedPosY"]),
1731 Convert.ToSingle(row["AttachedPosZ"])
1732 );
1733
1734 if (!(row["DynAttrs"] is System.DBNull))
1735 {
1736 //m_log.DebugFormat("[SQLITE]: DynAttrs type [{0}]", row["DynAttrs"].GetType());
1737 prim.DynAttrs = DAMap.FromXml((string)row["DynAttrs"]);
1738 }
1739 else
1740 {
1741 prim.DynAttrs = new DAMap();
1742 }
1743
1744 prim.PhysicsShapeType = Convert.ToByte(row["PhysicsShapeType"]);
1745 prim.Density = Convert.ToSingle(row["Density"]);
1746 prim.GravityModifier = Convert.ToSingle(row["GravityModifier"]);
1747 prim.Friction = Convert.ToSingle(row["Friction"]);
1748 prim.Restitution = Convert.ToSingle(row["Restitution"]);
1714 1749
1750
1751 if (!(row["KeyframeMotion"] is DBNull))
1752 {
1753 Byte[] data = (byte[])row["KeyframeMotion"];
1754 if (data.Length > 0)
1755 prim.KeyframeMotion = KeyframeMotion.FromData(null, data);
1756 else
1757 prim.KeyframeMotion = null;
1758 }
1759 else
1760 {
1761 prim.KeyframeMotion = null;
1762 }
1763
1715 return prim; 1764 return prim;
1716 } 1765 }
1717 1766
@@ -1967,40 +2016,6 @@ namespace OpenSim.Data.SQLite
1967 /// <summary> 2016 /// <summary>
1968 /// 2017 ///
1969 /// </summary> 2018 /// </summary>
1970 /// <param name="val"></param>
1971 /// <returns></returns>
1972 private static Array serializeTerrain(double[,] val)
1973 {
1974 MemoryStream str = new MemoryStream(((int)Constants.RegionSize * (int)Constants.RegionSize) * sizeof(double));
1975 BinaryWriter bw = new BinaryWriter(str);
1976
1977 // TODO: COMPATIBILITY - Add byte-order conversions
1978 for (int x = 0; x < (int)Constants.RegionSize; x++)
1979 for (int y = 0; y < (int)Constants.RegionSize; y++)
1980 bw.Write(val[x, y]);
1981
1982 return str.ToArray();
1983 }
1984
1985 // private void fillTerrainRow(DataRow row, UUID regionUUID, int rev, double[,] val)
1986 // {
1987 // row["RegionUUID"] = regionUUID;
1988 // row["Revision"] = rev;
1989
1990 // MemoryStream str = new MemoryStream(((int)Constants.RegionSize * (int)Constants.RegionSize)*sizeof (double));
1991 // BinaryWriter bw = new BinaryWriter(str);
1992
1993 // // TODO: COMPATIBILITY - Add byte-order conversions
1994 // for (int x = 0; x < (int)Constants.RegionSize; x++)
1995 // for (int y = 0; y < (int)Constants.RegionSize; y++)
1996 // bw.Write(val[x, y]);
1997
1998 // row["Heightfield"] = str.ToArray();
1999 // }
2000
2001 /// <summary>
2002 ///
2003 /// </summary>
2004 /// <param name="row"></param> 2019 /// <param name="row"></param>
2005 /// <param name="prim"></param> 2020 /// <param name="prim"></param>
2006 /// <param name="sceneGroupID"></param> 2021 /// <param name="sceneGroupID"></param>
@@ -2019,7 +2034,7 @@ namespace OpenSim.Data.SQLite
2019 row["SitName"] = prim.SitName; 2034 row["SitName"] = prim.SitName;
2020 row["TouchName"] = prim.TouchName; 2035 row["TouchName"] = prim.TouchName;
2021 // permissions 2036 // permissions
2022 row["ObjectFlags"] = prim.ObjectFlags; 2037 row["ObjectFlags"] = (uint)prim.Flags;
2023 row["CreatorID"] = prim.CreatorIdentification.ToString(); 2038 row["CreatorID"] = prim.CreatorIdentification.ToString();
2024 row["OwnerID"] = prim.OwnerID.ToString(); 2039 row["OwnerID"] = prim.OwnerID.ToString();
2025 row["GroupID"] = prim.GroupID.ToString(); 2040 row["GroupID"] = prim.GroupID.ToString();
@@ -2133,6 +2148,28 @@ namespace OpenSim.Data.SQLite
2133 row["VolumeDetect"] = 0; 2148 row["VolumeDetect"] = 0;
2134 2149
2135 row["MediaURL"] = prim.MediaUrl; 2150 row["MediaURL"] = prim.MediaUrl;
2151
2152 row["AttachedPosX"] = prim.AttachedPos.X;
2153 row["AttachedPosY"] = prim.AttachedPos.Y;
2154 row["AttachedPosZ"] = prim.AttachedPos.Z;
2155
2156 if (prim.DynAttrs.CountNamespaces > 0)
2157 row["DynAttrs"] = prim.DynAttrs.ToXml();
2158 else
2159 row["DynAttrs"] = null;
2160
2161 row["PhysicsShapeType"] = prim.PhysicsShapeType;
2162 row["Density"] = (double)prim.Density;
2163 row["GravityModifier"] = (double)prim.GravityModifier;
2164 row["Friction"] = (double)prim.Friction;
2165 row["Restitution"] = (double)prim.Restitution;
2166
2167 if (prim.KeyframeMotion != null)
2168 row["KeyframeMotion"] = prim.KeyframeMotion.Serialize();
2169 else
2170 row["KeyframeMotion"] = new Byte[0];
2171
2172
2136 } 2173 }
2137 2174
2138 /// <summary> 2175 /// <summary>
@@ -2384,6 +2421,7 @@ namespace OpenSim.Data.SQLite
2384 s.ProfileCurve = Convert.ToByte(row["ProfileCurve"]); 2421 s.ProfileCurve = Convert.ToByte(row["ProfileCurve"]);
2385 s.ProfileHollow = Convert.ToUInt16(row["ProfileHollow"]); 2422 s.ProfileHollow = Convert.ToUInt16(row["ProfileHollow"]);
2386 s.State = Convert.ToByte(row["State"]); 2423 s.State = Convert.ToByte(row["State"]);
2424 s.LastAttachPoint = Convert.ToByte(row["LastAttachPoint"]);
2387 2425
2388 byte[] textureEntry = (byte[])row["Texture"]; 2426 byte[] textureEntry = (byte[])row["Texture"];
2389 s.TextureEntry = textureEntry; 2427 s.TextureEntry = textureEntry;
@@ -2392,7 +2430,7 @@ namespace OpenSim.Data.SQLite
2392 2430
2393 if (!(row["Media"] is System.DBNull)) 2431 if (!(row["Media"] is System.DBNull))
2394 s.Media = PrimitiveBaseShape.MediaList.FromXml((string)row["Media"]); 2432 s.Media = PrimitiveBaseShape.MediaList.FromXml((string)row["Media"]);
2395 2433
2396 return s; 2434 return s;
2397 } 2435 }
2398 2436
@@ -2433,6 +2471,7 @@ namespace OpenSim.Data.SQLite
2433 row["ProfileCurve"] = s.ProfileCurve; 2471 row["ProfileCurve"] = s.ProfileCurve;
2434 row["ProfileHollow"] = s.ProfileHollow; 2472 row["ProfileHollow"] = s.ProfileHollow;
2435 row["State"] = s.State; 2473 row["State"] = s.State;
2474 row["LastAttachPoint"] = s.LastAttachPoint;
2436 2475
2437 row["Texture"] = s.TextureEntry; 2476 row["Texture"] = s.TextureEntry;
2438 row["ExtraParams"] = s.ExtraParams; 2477 row["ExtraParams"] = s.ExtraParams;
diff --git a/OpenSim/Data/SQLite/SQLiteUserProfilesData.cs b/OpenSim/Data/SQLite/SQLiteUserProfilesData.cs
new file mode 100644
index 0000000..cd3e8b6
--- /dev/null
+++ b/OpenSim/Data/SQLite/SQLiteUserProfilesData.cs
@@ -0,0 +1,981 @@
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 log4net;
33#if CSharpSqlite
34using Community.CsharpSqlite.Sqlite;
35#else
36using Mono.Data.Sqlite;
37#endif
38using OpenMetaverse;
39using OpenMetaverse.StructuredData;
40using OpenSim.Framework;
41using OpenSim.Region.Framework.Interfaces;
42
43namespace OpenSim.Data.SQLite
44{
45 public class SQLiteUserProfilesData: IProfilesData
46 {
47 private static readonly ILog m_log =
48 LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
49
50 private SqliteConnection m_connection;
51 private string m_connectionString;
52
53 private Dictionary<string, FieldInfo> m_FieldMap =
54 new Dictionary<string, FieldInfo>();
55
56 protected virtual Assembly Assembly
57 {
58 get { return GetType().Assembly; }
59 }
60
61 public SQLiteUserProfilesData()
62 {
63 }
64
65 public SQLiteUserProfilesData(string connectionString)
66 {
67 Initialise(connectionString);
68 }
69
70 public void Initialise(string connectionString)
71 {
72 if (Util.IsWindows())
73 Util.LoadArchSpecificWindowsDll("sqlite3.dll");
74
75 m_connectionString = connectionString;
76
77 m_log.Info("[PROFILES_DATA]: Sqlite - connecting: "+m_connectionString);
78
79 m_connection = new SqliteConnection(m_connectionString);
80 m_connection.Open();
81
82 Migration m = new Migration(m_connection, Assembly, "UserProfiles");
83 m.Update();
84 }
85
86 private string[] FieldList
87 {
88 get { return new List<string>(m_FieldMap.Keys).ToArray(); }
89 }
90
91 #region IProfilesData implementation
92 public OSDArray GetClassifiedRecords(UUID creatorId)
93 {
94 OSDArray data = new OSDArray();
95 string query = "SELECT classifieduuid, name FROM classifieds WHERE creatoruuid = :Id";
96 IDataReader reader = null;
97
98 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
99 {
100 cmd.CommandText = query;
101 cmd.Parameters.AddWithValue(":Id", creatorId);
102 reader = cmd.ExecuteReader();
103 }
104
105 while (reader.Read())
106 {
107 OSDMap n = new OSDMap();
108 UUID Id = UUID.Zero;
109 string Name = null;
110 try
111 {
112 UUID.TryParse(Convert.ToString( reader["classifieduuid"]), out Id);
113 Name = Convert.ToString(reader["name"]);
114 }
115 catch (Exception e)
116 {
117 m_log.ErrorFormat("[PROFILES_DATA]" +
118 ": UserAccount exception {0}", e.Message);
119 }
120 n.Add("classifieduuid", OSD.FromUUID(Id));
121 n.Add("name", OSD.FromString(Name));
122 data.Add(n);
123 }
124
125 reader.Close();
126
127 return data;
128 }
129 public bool UpdateClassifiedRecord(UserClassifiedAdd ad, ref string result)
130 {
131 string query = string.Empty;
132
133 query += "INSERT OR REPLACE INTO classifieds (";
134 query += "`classifieduuid`,";
135 query += "`creatoruuid`,";
136 query += "`creationdate`,";
137 query += "`expirationdate`,";
138 query += "`category`,";
139 query += "`name`,";
140 query += "`description`,";
141 query += "`parceluuid`,";
142 query += "`parentestate`,";
143 query += "`snapshotuuid`,";
144 query += "`simname`,";
145 query += "`posglobal`,";
146 query += "`parcelname`,";
147 query += "`classifiedflags`,";
148 query += "`priceforlisting`) ";
149 query += "VALUES (";
150 query += ":ClassifiedId,";
151 query += ":CreatorId,";
152 query += ":CreatedDate,";
153 query += ":ExpirationDate,";
154 query += ":Category,";
155 query += ":Name,";
156 query += ":Description,";
157 query += ":ParcelId,";
158 query += ":ParentEstate,";
159 query += ":SnapshotId,";
160 query += ":SimName,";
161 query += ":GlobalPos,";
162 query += ":ParcelName,";
163 query += ":Flags,";
164 query += ":ListingPrice ) ";
165
166 if(string.IsNullOrEmpty(ad.ParcelName))
167 ad.ParcelName = "Unknown";
168 if(ad.ParcelId == null)
169 ad.ParcelId = UUID.Zero;
170 if(string.IsNullOrEmpty(ad.Description))
171 ad.Description = "No Description";
172
173 DateTime epoch = new DateTime(1970, 1, 1);
174 DateTime now = DateTime.Now;
175 TimeSpan epochnow = now - epoch;
176 TimeSpan duration;
177 DateTime expiration;
178 TimeSpan epochexp;
179
180 if(ad.Flags == 2)
181 {
182 duration = new TimeSpan(7,0,0,0);
183 expiration = now.Add(duration);
184 epochexp = expiration - epoch;
185 }
186 else
187 {
188 duration = new TimeSpan(365,0,0,0);
189 expiration = now.Add(duration);
190 epochexp = expiration - epoch;
191 }
192 ad.CreationDate = (int)epochnow.TotalSeconds;
193 ad.ExpirationDate = (int)epochexp.TotalSeconds;
194
195 try {
196 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
197 {
198 cmd.CommandText = query;
199 cmd.Parameters.AddWithValue(":ClassifiedId", ad.ClassifiedId.ToString());
200 cmd.Parameters.AddWithValue(":CreatorId", ad.CreatorId.ToString());
201 cmd.Parameters.AddWithValue(":CreatedDate", ad.CreationDate.ToString());
202 cmd.Parameters.AddWithValue(":ExpirationDate", ad.ExpirationDate.ToString());
203 cmd.Parameters.AddWithValue(":Category", ad.Category.ToString());
204 cmd.Parameters.AddWithValue(":Name", ad.Name.ToString());
205 cmd.Parameters.AddWithValue(":Description", ad.Description.ToString());
206 cmd.Parameters.AddWithValue(":ParcelId", ad.ParcelId.ToString());
207 cmd.Parameters.AddWithValue(":ParentEstate", ad.ParentEstate.ToString());
208 cmd.Parameters.AddWithValue(":SnapshotId", ad.SnapshotId.ToString ());
209 cmd.Parameters.AddWithValue(":SimName", ad.SimName.ToString());
210 cmd.Parameters.AddWithValue(":GlobalPos", ad.GlobalPos.ToString());
211 cmd.Parameters.AddWithValue(":ParcelName", ad.ParcelName.ToString());
212 cmd.Parameters.AddWithValue(":Flags", ad.Flags.ToString());
213 cmd.Parameters.AddWithValue(":ListingPrice", ad.Price.ToString ());
214
215 cmd.ExecuteNonQuery();
216 }
217 }
218 catch (Exception e)
219 {
220 m_log.ErrorFormat("[PROFILES_DATA]" +
221 ": ClassifiedesUpdate exception {0}", e.Message);
222 result = e.Message;
223 return false;
224 }
225 return true;
226 }
227 public bool DeleteClassifiedRecord(UUID recordId)
228 {
229 string query = string.Empty;
230
231 query += "DELETE FROM classifieds WHERE ";
232 query += "classifieduuid = :ClasifiedId";
233
234 try
235 {
236 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
237 {
238 cmd.CommandText = query;
239 cmd.Parameters.AddWithValue(":ClassifiedId", recordId.ToString());
240
241 cmd.ExecuteNonQuery();
242 }
243 }
244 catch (Exception e)
245 {
246 m_log.ErrorFormat("[PROFILES_DATA]" +
247 ": DeleteClassifiedRecord exception {0}", e.Message);
248 return false;
249 }
250 return true;
251 }
252
253 public bool GetClassifiedInfo(ref UserClassifiedAdd ad, ref string result)
254 {
255 IDataReader reader = null;
256 string query = string.Empty;
257
258 query += "SELECT * FROM classifieds WHERE ";
259 query += "classifieduuid = :AdId";
260
261 try
262 {
263 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
264 {
265 cmd.CommandText = query;
266 cmd.Parameters.AddWithValue(":AdId", ad.ClassifiedId.ToString());
267
268 using (reader = cmd.ExecuteReader())
269 {
270 if(reader.Read ())
271 {
272 ad.CreatorId = new UUID(reader["creatoruuid"].ToString());
273 ad.ParcelId = new UUID(reader["parceluuid"].ToString ());
274 ad.SnapshotId = new UUID(reader["snapshotuuid"].ToString ());
275 ad.CreationDate = Convert.ToInt32(reader["creationdate"]);
276 ad.ExpirationDate = Convert.ToInt32(reader["expirationdate"]);
277 ad.ParentEstate = Convert.ToInt32(reader["parentestate"]);
278 ad.Flags = (byte) Convert.ToUInt32(reader["classifiedflags"]);
279 ad.Category = Convert.ToInt32(reader["category"]);
280 ad.Price = Convert.ToInt16(reader["priceforlisting"]);
281 ad.Name = reader["name"].ToString();
282 ad.Description = reader["description"].ToString();
283 ad.SimName = reader["simname"].ToString();
284 ad.GlobalPos = reader["posglobal"].ToString();
285 ad.ParcelName = reader["parcelname"].ToString();
286 }
287 }
288 }
289 }
290 catch (Exception e)
291 {
292 m_log.ErrorFormat("[PROFILES_DATA]" +
293 ": GetPickInfo exception {0}", e.Message);
294 }
295 return true;
296 }
297
298 public OSDArray GetAvatarPicks(UUID avatarId)
299 {
300 IDataReader reader = null;
301 string query = string.Empty;
302
303 query += "SELECT `pickuuid`,`name` FROM userpicks WHERE ";
304 query += "creatoruuid = :Id";
305 OSDArray data = new OSDArray();
306
307 try
308 {
309 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
310 {
311 cmd.CommandText = query;
312 cmd.Parameters.AddWithValue(":Id", avatarId.ToString());
313
314 using (reader = cmd.ExecuteReader())
315 {
316 while (reader.Read())
317 {
318 OSDMap record = new OSDMap();
319
320 record.Add("pickuuid",OSD.FromString((string)reader["pickuuid"]));
321 record.Add("name",OSD.FromString((string)reader["name"]));
322 data.Add(record);
323 }
324 }
325 }
326 }
327 catch (Exception e)
328 {
329 m_log.ErrorFormat("[PROFILES_DATA]" +
330 ": GetAvatarPicks exception {0}", e.Message);
331 }
332 return data;
333 }
334 public UserProfilePick GetPickInfo(UUID avatarId, UUID pickId)
335 {
336 IDataReader reader = null;
337 string query = string.Empty;
338 UserProfilePick pick = new UserProfilePick();
339
340 query += "SELECT * FROM userpicks WHERE ";
341 query += "creatoruuid = :CreatorId AND ";
342 query += "pickuuid = :PickId";
343
344 try
345 {
346 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
347 {
348 cmd.CommandText = query;
349 cmd.Parameters.AddWithValue(":CreatorId", avatarId.ToString());
350 cmd.Parameters.AddWithValue(":PickId", pickId.ToString());
351
352 using (reader = cmd.ExecuteReader())
353 {
354
355 while (reader.Read())
356 {
357 string description = (string)reader["description"];
358
359 if (string.IsNullOrEmpty(description))
360 description = "No description given.";
361
362 UUID.TryParse((string)reader["pickuuid"], out pick.PickId);
363 UUID.TryParse((string)reader["creatoruuid"], out pick.CreatorId);
364 UUID.TryParse((string)reader["parceluuid"], out pick.ParcelId);
365 UUID.TryParse((string)reader["snapshotuuid"], out pick.SnapshotId);
366 pick.GlobalPos = (string)reader["posglobal"];
367 bool.TryParse((string)reader["toppick"].ToString(), out pick.TopPick);
368 bool.TryParse((string)reader["enabled"].ToString(), out pick.Enabled);
369 pick.Name = (string)reader["name"];
370 pick.Desc = description;
371 pick.ParcelName = (string)reader["user"];
372 pick.OriginalName = (string)reader["originalname"];
373 pick.SimName = (string)reader["simname"];
374 pick.SortOrder = (int)reader["sortorder"];
375 }
376 }
377 }
378 }
379 catch (Exception e)
380 {
381 m_log.ErrorFormat("[PROFILES_DATA]" +
382 ": GetPickInfo exception {0}", e.Message);
383 }
384 return pick;
385 }
386
387 public bool UpdatePicksRecord(UserProfilePick pick)
388 {
389 string query = string.Empty;
390
391 query += "INSERT OR REPLACE INTO userpicks (";
392 query += "pickuuid, ";
393 query += "creatoruuid, ";
394 query += "toppick, ";
395 query += "parceluuid, ";
396 query += "name, ";
397 query += "description, ";
398 query += "snapshotuuid, ";
399 query += "user, ";
400 query += "originalname, ";
401 query += "simname, ";
402 query += "posglobal, ";
403 query += "sortorder, ";
404 query += "enabled ) ";
405 query += "VALUES (";
406 query += ":PickId,";
407 query += ":CreatorId,";
408 query += ":TopPick,";
409 query += ":ParcelId,";
410 query += ":Name,";
411 query += ":Desc,";
412 query += ":SnapshotId,";
413 query += ":User,";
414 query += ":Original,";
415 query += ":SimName,";
416 query += ":GlobalPos,";
417 query += ":SortOrder,";
418 query += ":Enabled) ";
419
420 try
421 {
422 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
423 {
424 int top_pick;
425 int.TryParse(pick.TopPick.ToString(), out top_pick);
426 int enabled;
427 int.TryParse(pick.Enabled.ToString(), out enabled);
428
429 cmd.CommandText = query;
430 cmd.Parameters.AddWithValue(":PickId", pick.PickId.ToString());
431 cmd.Parameters.AddWithValue(":CreatorId", pick.CreatorId.ToString());
432 cmd.Parameters.AddWithValue(":TopPick", top_pick);
433 cmd.Parameters.AddWithValue(":ParcelId", pick.ParcelId.ToString());
434 cmd.Parameters.AddWithValue(":Name", pick.Name.ToString());
435 cmd.Parameters.AddWithValue(":Desc", pick.Desc.ToString());
436 cmd.Parameters.AddWithValue(":SnapshotId", pick.SnapshotId.ToString());
437 cmd.Parameters.AddWithValue(":User", pick.ParcelName.ToString());
438 cmd.Parameters.AddWithValue(":Original", pick.OriginalName.ToString());
439 cmd.Parameters.AddWithValue(":SimName",pick.SimName.ToString());
440 cmd.Parameters.AddWithValue(":GlobalPos", pick.GlobalPos);
441 cmd.Parameters.AddWithValue(":SortOrder", pick.SortOrder.ToString ());
442 cmd.Parameters.AddWithValue(":Enabled", enabled);
443
444 cmd.ExecuteNonQuery();
445 }
446 }
447 catch (Exception e)
448 {
449 m_log.ErrorFormat("[PROFILES_DATA]" +
450 ": UpdateAvatarNotes exception {0}", e.Message);
451 return false;
452 }
453 return true;
454 }
455
456 public bool DeletePicksRecord(UUID pickId)
457 {
458 string query = string.Empty;
459
460 query += "DELETE FROM userpicks WHERE ";
461 query += "pickuuid = :PickId";
462
463 try
464 {
465 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
466 {
467 cmd.CommandText = query;
468 cmd.Parameters.AddWithValue(":PickId", pickId.ToString());
469 cmd.ExecuteNonQuery();
470 }
471 }
472 catch (Exception e)
473 {
474 m_log.ErrorFormat("[PROFILES_DATA]" +
475 ": DeleteUserPickRecord exception {0}", e.Message);
476 return false;
477 }
478 return true;
479 }
480
481 public bool GetAvatarNotes(ref UserProfileNotes notes)
482 {
483 IDataReader reader = null;
484 string query = string.Empty;
485
486 query += "SELECT `notes` FROM usernotes WHERE ";
487 query += "useruuid = :Id AND ";
488 query += "targetuuid = :TargetId";
489 OSDArray data = new OSDArray();
490
491 try
492 {
493 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
494 {
495 cmd.CommandText = query;
496 cmd.Parameters.AddWithValue(":Id", notes.UserId.ToString());
497 cmd.Parameters.AddWithValue(":TargetId", notes.TargetId.ToString());
498
499 using (reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
500 {
501 while (reader.Read())
502 {
503 notes.Notes = OSD.FromString((string)reader["notes"]);
504 }
505 }
506 }
507 }
508 catch (Exception e)
509 {
510 m_log.ErrorFormat("[PROFILES_DATA]" +
511 ": GetAvatarNotes exception {0}", e.Message);
512 }
513 return true;
514 }
515
516 public bool UpdateAvatarNotes(ref UserProfileNotes note, ref string result)
517 {
518 string query = string.Empty;
519 bool remove;
520
521 if(string.IsNullOrEmpty(note.Notes))
522 {
523 remove = true;
524 query += "DELETE FROM usernotes WHERE ";
525 query += "useruuid=:UserId AND ";
526 query += "targetuuid=:TargetId";
527 }
528 else
529 {
530 remove = false;
531 query += "INSERT OR REPLACE INTO usernotes VALUES ( ";
532 query += ":UserId,";
533 query += ":TargetId,";
534 query += ":Notes )";
535 }
536
537 try
538 {
539 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
540 {
541 cmd.CommandText = query;
542
543 if(!remove)
544 cmd.Parameters.AddWithValue(":Notes", note.Notes);
545 cmd.Parameters.AddWithValue(":TargetId", note.TargetId.ToString ());
546 cmd.Parameters.AddWithValue(":UserId", note.UserId.ToString());
547
548 cmd.ExecuteNonQuery();
549 }
550 }
551 catch (Exception e)
552 {
553 m_log.ErrorFormat("[PROFILES_DATA]" +
554 ": UpdateAvatarNotes exception {0}", e.Message);
555 return false;
556 }
557 return true;
558 }
559
560 public bool GetAvatarProperties(ref UserProfileProperties props, ref string result)
561 {
562 IDataReader reader = null;
563 string query = string.Empty;
564
565 query += "SELECT * FROM userprofile WHERE ";
566 query += "useruuid = :Id";
567
568 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
569 {
570 cmd.CommandText = query;
571 cmd.Parameters.AddWithValue(":Id", props.UserId.ToString());
572
573
574 try
575 {
576 reader = cmd.ExecuteReader();
577 }
578 catch(Exception e)
579 {
580 m_log.ErrorFormat("[PROFILES_DATA]" +
581 ": GetAvatarProperties exception {0}", e.Message);
582 result = e.Message;
583 return false;
584 }
585 if(reader != null && reader.Read())
586 {
587 props.WebUrl = (string)reader["profileURL"];
588 UUID.TryParse((string)reader["profileImage"], out props.ImageId);
589 props.AboutText = (string)reader["profileAboutText"];
590 UUID.TryParse((string)reader["profileFirstImage"], out props.FirstLifeImageId);
591 props.FirstLifeText = (string)reader["profileFirstText"];
592 UUID.TryParse((string)reader["profilePartner"], out props.PartnerId);
593 props.WantToMask = (int)reader["profileWantToMask"];
594 props.WantToText = (string)reader["profileWantToText"];
595 props.SkillsMask = (int)reader["profileSkillsMask"];
596 props.SkillsText = (string)reader["profileSkillsText"];
597 props.Language = (string)reader["profileLanguages"];
598 }
599 else
600 {
601 props.WebUrl = string.Empty;
602 props.ImageId = UUID.Zero;
603 props.AboutText = string.Empty;
604 props.FirstLifeImageId = UUID.Zero;
605 props.FirstLifeText = string.Empty;
606 props.PartnerId = UUID.Zero;
607 props.WantToMask = 0;
608 props.WantToText = string.Empty;
609 props.SkillsMask = 0;
610 props.SkillsText = string.Empty;
611 props.Language = string.Empty;
612 props.PublishProfile = false;
613 props.PublishMature = false;
614
615 query = "INSERT INTO userprofile (";
616 query += "useruuid, ";
617 query += "profilePartner, ";
618 query += "profileAllowPublish, ";
619 query += "profileMaturePublish, ";
620 query += "profileURL, ";
621 query += "profileWantToMask, ";
622 query += "profileWantToText, ";
623 query += "profileSkillsMask, ";
624 query += "profileSkillsText, ";
625 query += "profileLanguages, ";
626 query += "profileImage, ";
627 query += "profileAboutText, ";
628 query += "profileFirstImage, ";
629 query += "profileFirstText) VALUES (";
630 query += ":userId, ";
631 query += ":profilePartner, ";
632 query += ":profileAllowPublish, ";
633 query += ":profileMaturePublish, ";
634 query += ":profileURL, ";
635 query += ":profileWantToMask, ";
636 query += ":profileWantToText, ";
637 query += ":profileSkillsMask, ";
638 query += ":profileSkillsText, ";
639 query += ":profileLanguages, ";
640 query += ":profileImage, ";
641 query += ":profileAboutText, ";
642 query += ":profileFirstImage, ";
643 query += ":profileFirstText)";
644
645 using (SqliteCommand put = (SqliteCommand)m_connection.CreateCommand())
646 {
647 put.CommandText = query;
648 put.Parameters.AddWithValue(":userId", props.UserId.ToString());
649 put.Parameters.AddWithValue(":profilePartner", props.PartnerId.ToString());
650 put.Parameters.AddWithValue(":profileAllowPublish", props.PublishProfile);
651 put.Parameters.AddWithValue(":profileMaturePublish", props.PublishMature);
652 put.Parameters.AddWithValue(":profileURL", props.WebUrl);
653 put.Parameters.AddWithValue(":profileWantToMask", props.WantToMask);
654 put.Parameters.AddWithValue(":profileWantToText", props.WantToText);
655 put.Parameters.AddWithValue(":profileSkillsMask", props.SkillsMask);
656 put.Parameters.AddWithValue(":profileSkillsText", props.SkillsText);
657 put.Parameters.AddWithValue(":profileLanguages", props.Language);
658 put.Parameters.AddWithValue(":profileImage", props.ImageId.ToString());
659 put.Parameters.AddWithValue(":profileAboutText", props.AboutText);
660 put.Parameters.AddWithValue(":profileFirstImage", props.FirstLifeImageId.ToString());
661 put.Parameters.AddWithValue(":profileFirstText", props.FirstLifeText);
662
663 put.ExecuteNonQuery();
664 }
665 }
666 }
667 return true;
668 }
669
670 public bool UpdateAvatarProperties(ref UserProfileProperties props, ref string result)
671 {
672 string query = string.Empty;
673
674 query += "UPDATE userprofile SET ";
675 query += "profileURL=:profileURL, ";
676 query += "profileImage=:image, ";
677 query += "profileAboutText=:abouttext,";
678 query += "profileFirstImage=:firstlifeimage,";
679 query += "profileFirstText=:firstlifetext ";
680 query += "WHERE useruuid=:uuid";
681
682 try
683 {
684 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
685 {
686 cmd.CommandText = query;
687 cmd.Parameters.AddWithValue(":profileURL", props.WebUrl);
688 cmd.Parameters.AddWithValue(":image", props.ImageId.ToString());
689 cmd.Parameters.AddWithValue(":abouttext", props.AboutText);
690 cmd.Parameters.AddWithValue(":firstlifeimage", props.FirstLifeImageId.ToString());
691 cmd.Parameters.AddWithValue(":firstlifetext", props.FirstLifeText);
692 cmd.Parameters.AddWithValue(":uuid", props.UserId.ToString());
693
694 cmd.ExecuteNonQuery();
695 }
696 }
697 catch (Exception e)
698 {
699 m_log.ErrorFormat("[PROFILES_DATA]" +
700 ": AgentPropertiesUpdate exception {0}", e.Message);
701
702 return false;
703 }
704 return true;
705 }
706
707 public bool UpdateAvatarInterests(UserProfileProperties up, ref string result)
708 {
709 string query = string.Empty;
710
711 query += "UPDATE userprofile SET ";
712 query += "profileWantToMask=:WantMask, ";
713 query += "profileWantToText=:WantText,";
714 query += "profileSkillsMask=:SkillsMask,";
715 query += "profileSkillsText=:SkillsText, ";
716 query += "profileLanguages=:Languages ";
717 query += "WHERE useruuid=:uuid";
718
719 try
720 {
721 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
722 {
723 cmd.CommandText = query;
724 cmd.Parameters.AddWithValue(":WantMask", up.WantToMask);
725 cmd.Parameters.AddWithValue(":WantText", up.WantToText);
726 cmd.Parameters.AddWithValue(":SkillsMask", up.SkillsMask);
727 cmd.Parameters.AddWithValue(":SkillsText", up.SkillsText);
728 cmd.Parameters.AddWithValue(":Languages", up.Language);
729 cmd.Parameters.AddWithValue(":uuid", up.UserId.ToString());
730
731 cmd.ExecuteNonQuery();
732 }
733 }
734 catch (Exception e)
735 {
736 m_log.ErrorFormat("[PROFILES_DATA]" +
737 ": AgentInterestsUpdate exception {0}", e.Message);
738 result = e.Message;
739 return false;
740 }
741 return true;
742 }
743
744 public bool UpdateUserPreferences(ref UserPreferences pref, ref string result)
745 {
746 string query = string.Empty;
747
748 query += "UPDATE usersettings SET ";
749 query += "imviaemail=:ImViaEmail, ";
750 query += "visible=:Visible, ";
751 query += "email=:EMail ";
752 query += "WHERE useruuid=:uuid";
753
754 try
755 {
756 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
757 {
758 cmd.CommandText = query;
759 cmd.Parameters.AddWithValue(":ImViaEmail", pref.IMViaEmail);
760 cmd.Parameters.AddWithValue(":Visible", pref.Visible);
761 cmd.Parameters.AddWithValue(":EMail", pref.EMail);
762 cmd.Parameters.AddWithValue(":uuid", pref.UserId.ToString());
763
764 cmd.ExecuteNonQuery();
765 }
766 }
767 catch (Exception e)
768 {
769 m_log.ErrorFormat("[PROFILES_DATA]" +
770 ": AgentInterestsUpdate exception {0}", e.Message);
771 result = e.Message;
772 return false;
773 }
774 return true;
775 }
776
777 public bool GetUserPreferences(ref UserPreferences pref, ref string result)
778 {
779 IDataReader reader = null;
780 string query = string.Empty;
781
782 query += "SELECT imviaemail,visible,email FROM ";
783 query += "usersettings WHERE ";
784 query += "useruuid = :Id";
785
786 OSDArray data = new OSDArray();
787
788 try
789 {
790 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
791 {
792 cmd.CommandText = query;
793 cmd.Parameters.AddWithValue("?Id", pref.UserId.ToString());
794
795 using (reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
796 {
797 if(reader.Read())
798 {
799 bool.TryParse((string)reader["imviaemail"], out pref.IMViaEmail);
800 bool.TryParse((string)reader["visible"], out pref.Visible);
801 pref.EMail = (string)reader["email"];
802 }
803 else
804 {
805 query = "INSERT INTO usersettings VALUES ";
806 query += "(:Id,'false','false', :Email)";
807
808 using (SqliteCommand put = (SqliteCommand)m_connection.CreateCommand())
809 {
810 put.Parameters.AddWithValue(":Id", pref.UserId.ToString());
811 put.Parameters.AddWithValue(":Email", pref.EMail);
812 put.ExecuteNonQuery();
813
814 }
815 }
816 }
817 }
818 }
819 catch (Exception e)
820 {
821 m_log.ErrorFormat("[PROFILES_DATA]" +
822 ": Get preferences exception {0}", e.Message);
823 result = e.Message;
824 return false;
825 }
826 return true;
827 }
828
829 public bool GetUserAppData(ref UserAppData props, ref string result)
830 {
831 IDataReader reader = null;
832 string query = string.Empty;
833
834 query += "SELECT * FROM `userdata` WHERE ";
835 query += "UserId = :Id AND ";
836 query += "TagId = :TagId";
837
838 try
839 {
840 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
841 {
842 cmd.CommandText = query;
843 cmd.Parameters.AddWithValue(":Id", props.UserId.ToString());
844 cmd.Parameters.AddWithValue (":TagId", props.TagId.ToString());
845
846 using (reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
847 {
848 if(reader.Read())
849 {
850 props.DataKey = (string)reader["DataKey"];
851 props.DataVal = (string)reader["DataVal"];
852 }
853 else
854 {
855 query += "INSERT INTO userdata VALUES ( ";
856 query += ":UserId,";
857 query += ":TagId,";
858 query += ":DataKey,";
859 query += ":DataVal) ";
860
861 using (SqliteCommand put = (SqliteCommand)m_connection.CreateCommand())
862 {
863 put.Parameters.AddWithValue(":Id", props.UserId.ToString());
864 put.Parameters.AddWithValue(":TagId", props.TagId.ToString());
865 put.Parameters.AddWithValue(":DataKey", props.DataKey.ToString());
866 put.Parameters.AddWithValue(":DataVal", props.DataVal.ToString());
867
868 put.ExecuteNonQuery();
869 }
870 }
871 }
872 }
873 }
874 catch (Exception e)
875 {
876 m_log.ErrorFormat("[PROFILES_DATA]" +
877 ": Requst application data exception {0}", e.Message);
878 result = e.Message;
879 return false;
880 }
881 return true;
882 }
883 public bool SetUserAppData(UserAppData props, ref string result)
884 {
885 string query = string.Empty;
886
887 query += "UPDATE userdata SET ";
888 query += "TagId = :TagId, ";
889 query += "DataKey = :DataKey, ";
890 query += "DataVal = :DataVal WHERE ";
891 query += "UserId = :UserId AND ";
892 query += "TagId = :TagId";
893
894 try
895 {
896 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
897 {
898 cmd.CommandText = query;
899 cmd.Parameters.AddWithValue(":UserId", props.UserId.ToString());
900 cmd.Parameters.AddWithValue(":TagId", props.TagId.ToString ());
901 cmd.Parameters.AddWithValue(":DataKey", props.DataKey.ToString ());
902 cmd.Parameters.AddWithValue(":DataVal", props.DataKey.ToString ());
903
904 cmd.ExecuteNonQuery();
905 }
906 }
907 catch (Exception e)
908 {
909 m_log.ErrorFormat("[PROFILES_DATA]" +
910 ": SetUserData exception {0}", e.Message);
911 return false;
912 }
913 return true;
914 }
915 public OSDArray GetUserImageAssets(UUID avatarId)
916 {
917 IDataReader reader = null;
918 OSDArray data = new OSDArray();
919 string query = "SELECT `snapshotuuid` FROM {0} WHERE `creatoruuid` = :Id";
920
921 // Get classified image assets
922
923
924 try
925 {
926 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
927 {
928 cmd.CommandText = query;
929 cmd.Parameters.AddWithValue(":Id", avatarId.ToString());
930
931 using (reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
932 {
933 while(reader.Read())
934 {
935 data.Add(new OSDString((string)reader["snapshotuuid"].ToString()));
936 }
937 }
938 }
939
940 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
941 {
942 cmd.CommandText = query;
943 cmd.Parameters.AddWithValue(":Id", avatarId.ToString());
944
945 using (reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
946 {
947 if(reader.Read())
948 {
949 data.Add(new OSDString((string)reader["snapshotuuid"].ToString ()));
950 }
951 }
952 }
953
954 query = "SELECT `profileImage`, `profileFirstImage` FROM `userprofile` WHERE `useruuid` = :Id";
955
956 using (SqliteCommand cmd = (SqliteCommand)m_connection.CreateCommand())
957 {
958 cmd.CommandText = query;
959 cmd.Parameters.AddWithValue(":Id", avatarId.ToString());
960
961 using (reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
962 {
963 if(reader.Read())
964 {
965 data.Add(new OSDString((string)reader["profileImage"].ToString ()));
966 data.Add(new OSDString((string)reader["profileFirstImage"].ToString ()));
967 }
968 }
969 }
970 }
971 catch (Exception e)
972 {
973 m_log.ErrorFormat("[PROFILES_DATA]" +
974 ": GetAvatarNotes exception {0}", e.Message);
975 }
976 return data;
977 }
978 #endregion
979 }
980}
981
generated by cgit v1.1 at 2024-12-25 14:22:26 +0000