aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data
diff options
context:
space:
mode:
Diffstat (limited to 'OpenSim/Data')
-rw-r--r--OpenSim/Data/MSSQL/AutoClosingSqlCommand.cs219
-rw-r--r--OpenSim/Data/MSSQL/MSSQLAssetData.cs54
-rw-r--r--OpenSim/Data/MSSQL/MSSQLAuthenticationData.cs16
-rw-r--r--OpenSim/Data/MSSQL/MSSQLAvatarData.cs71
-rw-r--r--OpenSim/Data/MSSQL/MSSQLEstateData.cs99
-rw-r--r--OpenSim/Data/MSSQL/MSSQLFriendsData.cs83
-rw-r--r--OpenSim/Data/MSSQL/MSSQLGenericTableHandler.cs359
-rw-r--r--OpenSim/Data/MSSQL/MSSQLGridData.cs582
-rw-r--r--OpenSim/Data/MSSQL/MSSQLInventoryData.cs206
-rw-r--r--OpenSim/Data/MSSQL/MSSQLLegacyRegionData.cs162
-rw-r--r--OpenSim/Data/MSSQL/MSSQLManager.cs225
-rw-r--r--OpenSim/Data/MSSQL/MSSQLPresenceData.cs170
-rw-r--r--OpenSim/Data/MSSQL/MSSQLRegionData.cs34
-rw-r--r--OpenSim/Data/MSSQL/MSSQLUserAccountData.cs319
-rw-r--r--OpenSim/Data/MSSQL/MSSQLUserData.cs1238
-rw-r--r--OpenSim/Data/MSSQL/MSSQLXInventoryData.cs166
-rw-r--r--OpenSim/Data/MSSQL/Resources/001_AuthStore.sql17
-rw-r--r--OpenSim/Data/MSSQL/Resources/001_Avatar.sql15
-rw-r--r--OpenSim/Data/MSSQL/Resources/001_FriendsStore.sql11
-rw-r--r--OpenSim/Data/MSSQL/Resources/001_Presence.sql19
-rw-r--r--OpenSim/Data/MSSQL/Resources/002_AuthStore.sql6
-rw-r--r--OpenSim/Data/MSSQL/Resources/002_FriendsStore.sql6
-rw-r--r--OpenSim/Data/MSSQL/Resources/002_Presence.sql6
-rw-r--r--OpenSim/Data/MSSQL/Resources/002_UserAccount.sql12
-rw-r--r--OpenSim/Data/MSSQL/Resources/007_GridStore.sql9
25 files changed, 3262 insertions, 842 deletions
diff --git a/OpenSim/Data/MSSQL/AutoClosingSqlCommand.cs b/OpenSim/Data/MSSQL/AutoClosingSqlCommand.cs
deleted file mode 100644
index 93e48cd..0000000
--- a/OpenSim/Data/MSSQL/AutoClosingSqlCommand.cs
+++ /dev/null
@@ -1,219 +0,0 @@
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.Data;
29using System.Data.SqlClient;
30
31namespace OpenSim.Data.MSSQL
32{
33 /// <summary>
34 /// Encapsulates a SqlCommand object but ensures that when it is disposed, its connection is closed and disposed also.
35 /// </summary>
36 internal class AutoClosingSqlCommand : IDbCommand
37 {
38 private SqlCommand realCommand;
39
40 public AutoClosingSqlCommand(SqlCommand cmd)
41 {
42 realCommand = cmd;
43 }
44
45 #region IDbCommand Members
46
47 public void Cancel()
48 {
49 realCommand.Cancel();
50 }
51
52 public string CommandText
53 {
54 get
55 {
56 return realCommand.CommandText;
57 }
58 set
59 {
60 realCommand.CommandText = value;
61 }
62 }
63
64 public int CommandTimeout
65 {
66 get
67 {
68 return realCommand.CommandTimeout;
69 }
70 set
71 {
72 realCommand.CommandTimeout = value;
73 }
74 }
75
76 public CommandType CommandType
77 {
78 get
79 {
80 return realCommand.CommandType;
81 }
82 set
83 {
84 realCommand.CommandType = value;
85 }
86 }
87
88 IDbConnection IDbCommand.Connection
89 {
90 get
91 {
92 return realCommand.Connection;
93 }
94 set
95 {
96 realCommand.Connection = (SqlConnection) value;
97 }
98 }
99
100 public SqlConnection Connection
101 {
102 get
103 {
104 return realCommand.Connection;
105 }
106 }
107
108 IDbDataParameter IDbCommand.CreateParameter()
109 {
110 return realCommand.CreateParameter();
111 }
112
113 public SqlParameter CreateParameter()
114 {
115 return realCommand.CreateParameter();
116 }
117
118 public int ExecuteNonQuery()
119 {
120 return realCommand.ExecuteNonQuery();
121 }
122
123 IDataReader IDbCommand.ExecuteReader(CommandBehavior behavior)
124 {
125 return realCommand.ExecuteReader(behavior);
126 }
127
128 public SqlDataReader ExecuteReader(CommandBehavior behavior)
129 {
130 return realCommand.ExecuteReader(behavior);
131 }
132
133 IDataReader IDbCommand.ExecuteReader()
134 {
135 return realCommand.ExecuteReader();
136 }
137
138 public SqlDataReader ExecuteReader()
139 {
140 return realCommand.ExecuteReader();
141 }
142
143 public object ExecuteScalar()
144 {
145 return realCommand.ExecuteScalar();
146 }
147
148 IDataParameterCollection IDbCommand.Parameters
149 {
150 get { return realCommand.Parameters; }
151 }
152
153 public SqlParameterCollection Parameters
154 {
155 get { return realCommand.Parameters; }
156 }
157
158 public void Prepare()
159 {
160 realCommand.Prepare();
161 }
162
163// IDbTransaction IDbCommand.Transaction
164// {
165// get
166// {
167// return realCommand.Transaction;
168// }
169// set
170// {
171// realCommand.Transaction = (SqlTransaction) value;
172// }
173// }
174
175 public IDbTransaction Transaction
176 {
177 get { return realCommand.Transaction; }
178 set { realCommand.Transaction = (SqlTransaction)value; }
179 }
180
181 UpdateRowSource IDbCommand.UpdatedRowSource
182 {
183 get
184 {
185 return realCommand.UpdatedRowSource;
186 }
187 set
188 {
189 realCommand.UpdatedRowSource = value;
190 }
191 }
192
193 #endregion
194
195 #region IDisposable Members
196
197 public void Dispose()
198 {
199 SqlConnection conn = realCommand.Connection;
200 try
201 {
202 realCommand.Dispose();
203 }
204 finally
205 {
206 try
207 {
208 conn.Close();
209 }
210 finally
211 {
212 conn.Dispose();
213 }
214 }
215 }
216
217 #endregion
218 }
219}
diff --git a/OpenSim/Data/MSSQL/MSSQLAssetData.cs b/OpenSim/Data/MSSQL/MSSQLAssetData.cs
index 437c09c..b1faf0b 100644
--- a/OpenSim/Data/MSSQL/MSSQLAssetData.cs
+++ b/OpenSim/Data/MSSQL/MSSQLAssetData.cs
@@ -49,6 +49,7 @@ namespace OpenSim.Data.MSSQL
49 /// Database manager 49 /// Database manager
50 /// </summary> 50 /// </summary>
51 private MSSQLManager m_database; 51 private MSSQLManager m_database;
52 private string m_connectionString;
52 53
53 #region IPlugin Members 54 #region IPlugin Members
54 55
@@ -75,23 +76,8 @@ namespace OpenSim.Data.MSSQL
75 { 76 {
76 m_ticksToEpoch = new System.DateTime(1970, 1, 1).Ticks; 77 m_ticksToEpoch = new System.DateTime(1970, 1, 1).Ticks;
77 78
78 if (!string.IsNullOrEmpty(connectionString)) 79 m_database = new MSSQLManager(connectionString);
79 { 80 m_connectionString = connectionString;
80 m_database = new MSSQLManager(connectionString);
81 }
82 else
83 {
84 IniFile gridDataMSSqlFile = new IniFile("mssql_connection.ini");
85 string settingDataSource = gridDataMSSqlFile.ParseFileReadValue("data_source");
86 string settingInitialCatalog = gridDataMSSqlFile.ParseFileReadValue("initial_catalog");
87 string settingPersistSecurityInfo = gridDataMSSqlFile.ParseFileReadValue("persist_security_info");
88 string settingUserId = gridDataMSSqlFile.ParseFileReadValue("user_id");
89 string settingPassword = gridDataMSSqlFile.ParseFileReadValue("password");
90
91 m_database =
92 new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId,
93 settingPassword);
94 }
95 81
96 //New migration to check for DB changes 82 //New migration to check for DB changes
97 m_database.CheckMigration(_migrationStore); 83 m_database.CheckMigration(_migrationStore);
@@ -125,18 +111,19 @@ namespace OpenSim.Data.MSSQL
125 override public AssetBase GetAsset(UUID assetID) 111 override public AssetBase GetAsset(UUID assetID)
126 { 112 {
127 string sql = "SELECT * FROM assets WHERE id = @id"; 113 string sql = "SELECT * FROM assets WHERE id = @id";
128 using (AutoClosingSqlCommand command = m_database.Query(sql)) 114 using (SqlConnection conn = new SqlConnection(m_connectionString))
115 using (SqlCommand cmd = new SqlCommand(sql, conn))
129 { 116 {
130 command.Parameters.Add(m_database.CreateParameter("id", assetID)); 117 cmd.Parameters.Add(m_database.CreateParameter("id", assetID));
131 using (SqlDataReader reader = command.ExecuteReader()) 118 conn.Open();
119 using (SqlDataReader reader = cmd.ExecuteReader())
132 { 120 {
133 if (reader.Read()) 121 if (reader.Read())
134 { 122 {
135 AssetBase asset = new AssetBase( 123 AssetBase asset = new AssetBase(
136 new UUID((Guid)reader["id"]), 124 new UUID((Guid)reader["id"]),
137 (string)reader["name"], 125 (string)reader["name"],
138 Convert.ToSByte(reader["assetType"]), 126 Convert.ToSByte(reader["assetType"])
139 UUID.Zero.ToString()
140 ); 127 );
141 // Region Main 128 // Region Main
142 asset.Description = (string)reader["description"]; 129 asset.Description = (string)reader["description"];
@@ -191,7 +178,8 @@ namespace OpenSim.Data.MSSQL
191 m_log.Warn("[ASSET DB]: Description field truncated from " + asset.Description.Length + " to " + assetDescription.Length + " characters on add"); 178 m_log.Warn("[ASSET DB]: Description field truncated from " + asset.Description.Length + " to " + assetDescription.Length + " characters on add");
192 } 179 }
193 180
194 using (AutoClosingSqlCommand command = m_database.Query(sql)) 181 using (SqlConnection conn = new SqlConnection(m_connectionString))
182 using (SqlCommand command = new SqlCommand(sql, conn))
195 { 183 {
196 int now = (int)((System.DateTime.Now.Ticks - m_ticksToEpoch) / 10000000); 184 int now = (int)((System.DateTime.Now.Ticks - m_ticksToEpoch) / 10000000);
197 command.Parameters.Add(m_database.CreateParameter("id", asset.FullID)); 185 command.Parameters.Add(m_database.CreateParameter("id", asset.FullID));
@@ -203,7 +191,7 @@ namespace OpenSim.Data.MSSQL
203 command.Parameters.Add(m_database.CreateParameter("access_time", now)); 191 command.Parameters.Add(m_database.CreateParameter("access_time", now));
204 command.Parameters.Add(m_database.CreateParameter("create_time", now)); 192 command.Parameters.Add(m_database.CreateParameter("create_time", now));
205 command.Parameters.Add(m_database.CreateParameter("data", asset.Data)); 193 command.Parameters.Add(m_database.CreateParameter("data", asset.Data));
206 194 conn.Open();
207 try 195 try
208 { 196 {
209 command.ExecuteNonQuery(); 197 command.ExecuteNonQuery();
@@ -239,7 +227,8 @@ namespace OpenSim.Data.MSSQL
239 m_log.Warn("[ASSET DB]: Description field truncated from " + asset.Description.Length + " to " + assetDescription.Length + " characters on update"); 227 m_log.Warn("[ASSET DB]: Description field truncated from " + asset.Description.Length + " to " + assetDescription.Length + " characters on update");
240 } 228 }
241 229
242 using (AutoClosingSqlCommand command = m_database.Query(sql)) 230 using (SqlConnection conn = new SqlConnection(m_connectionString))
231 using (SqlCommand command = new SqlCommand(sql, conn))
243 { 232 {
244 command.Parameters.Add(m_database.CreateParameter("id", asset.FullID)); 233 command.Parameters.Add(m_database.CreateParameter("id", asset.FullID));
245 command.Parameters.Add(m_database.CreateParameter("name", assetName)); 234 command.Parameters.Add(m_database.CreateParameter("name", assetName));
@@ -249,7 +238,7 @@ namespace OpenSim.Data.MSSQL
249 command.Parameters.Add(m_database.CreateParameter("temporary", asset.Temporary)); 238 command.Parameters.Add(m_database.CreateParameter("temporary", asset.Temporary));
250 command.Parameters.Add(m_database.CreateParameter("data", asset.Data)); 239 command.Parameters.Add(m_database.CreateParameter("data", asset.Data));
251 command.Parameters.Add(m_database.CreateParameter("@keyId", asset.FullID)); 240 command.Parameters.Add(m_database.CreateParameter("@keyId", asset.FullID));
252 241 conn.Open();
253 try 242 try
254 { 243 {
255 command.ExecuteNonQuery(); 244 command.ExecuteNonQuery();
@@ -308,13 +297,14 @@ namespace OpenSim.Data.MSSQL
308 string sql = @"SELECT (name,description,assetType,temporary,id), Row = ROW_NUMBER() 297 string sql = @"SELECT (name,description,assetType,temporary,id), Row = ROW_NUMBER()
309 OVER (ORDER BY (some column to order by)) 298 OVER (ORDER BY (some column to order by))
310 WHERE Row >= @Start AND Row < @Start + @Count"; 299 WHERE Row >= @Start AND Row < @Start + @Count";
311
312 using (AutoClosingSqlCommand command = m_database.Query(sql))
313 {
314 command.Parameters.Add(m_database.CreateParameter("start", start));
315 command.Parameters.Add(m_database.CreateParameter("count", count));
316 300
317 using (SqlDataReader reader = command.ExecuteReader()) 301 using (SqlConnection conn = new SqlConnection(m_connectionString))
302 using (SqlCommand cmd = new SqlCommand(sql, conn))
303 {
304 cmd.Parameters.Add(m_database.CreateParameter("start", start));
305 cmd.Parameters.Add(m_database.CreateParameter("count", count));
306 conn.Open();
307 using (SqlDataReader reader = cmd.ExecuteReader())
318 { 308 {
319 while (reader.Read()) 309 while (reader.Read())
320 { 310 {
diff --git a/OpenSim/Data/MSSQL/MSSQLAuthenticationData.cs b/OpenSim/Data/MSSQL/MSSQLAuthenticationData.cs
index 801610a..1ae78c4 100644
--- a/OpenSim/Data/MSSQL/MSSQLAuthenticationData.cs
+++ b/OpenSim/Data/MSSQL/MSSQLAuthenticationData.cs
@@ -53,6 +53,7 @@ namespace OpenSim.Data.MSSQL
53 { 53 {
54 conn.Open(); 54 conn.Open();
55 Migration m = new Migration(conn, GetType().Assembly, "AuthStore"); 55 Migration m = new Migration(conn, GetType().Assembly, "AuthStore");
56 m_database = new MSSQLManager(m_ConnectionString);
56 m.Update(); 57 m.Update();
57 } 58 }
58 } 59 }
@@ -168,13 +169,14 @@ namespace OpenSim.Data.MSSQL
168 { 169 {
169 if (System.Environment.TickCount - m_LastExpire > 30000) 170 if (System.Environment.TickCount - m_LastExpire > 30000)
170 DoExpire(); 171 DoExpire();
171 string sql = "insert into tokens (UUID, token, validity) values (@principalID, @token, date_add(now(), interval @lifetime minute))"; 172
173 string sql = "insert into tokens (UUID, token, validity) values (@principalID, @token, @lifetime)";
172 using (SqlConnection conn = new SqlConnection(m_ConnectionString)) 174 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
173 using (SqlCommand cmd = new SqlCommand(sql, conn)) 175 using (SqlCommand cmd = new SqlCommand(sql, conn))
174 { 176 {
175 cmd.Parameters.Add(m_database.CreateParameter("@principalID", principalID)); 177 cmd.Parameters.Add(m_database.CreateParameter("@principalID", principalID));
176 cmd.Parameters.Add(m_database.CreateParameter("@token", token)); 178 cmd.Parameters.Add(m_database.CreateParameter("@token", token));
177 cmd.Parameters.Add(m_database.CreateParameter("@lifetime", lifetime)); 179 cmd.Parameters.Add(m_database.CreateParameter("@lifetime", DateTime.Now.AddMinutes(lifetime)));
178 conn.Open(); 180 conn.Open();
179 181
180 if (cmd.ExecuteNonQuery() > 0) 182 if (cmd.ExecuteNonQuery() > 0)
@@ -189,13 +191,15 @@ namespace OpenSim.Data.MSSQL
189 { 191 {
190 if (System.Environment.TickCount - m_LastExpire > 30000) 192 if (System.Environment.TickCount - m_LastExpire > 30000)
191 DoExpire(); 193 DoExpire();
192 string sql = "update tokens set validity = date_add(now(), interval @lifetime minute) where UUID = @principalID and token = @token and validity > now()"; 194
195 DateTime validDate = DateTime.Now.AddMinutes(lifetime);
196 string sql = "update tokens set validity = @validDate where UUID = @principalID and token = @token and validity > GetDate()";
193 using (SqlConnection conn = new SqlConnection(m_ConnectionString)) 197 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
194 using (SqlCommand cmd = new SqlCommand(sql, conn)) 198 using (SqlCommand cmd = new SqlCommand(sql, conn))
195 { 199 {
196 cmd.Parameters.Add(m_database.CreateParameter("@principalID", principalID)); 200 cmd.Parameters.Add(m_database.CreateParameter("@principalID", principalID));
197 cmd.Parameters.Add(m_database.CreateParameter("@token", token)); 201 cmd.Parameters.Add(m_database.CreateParameter("@token", token));
198 cmd.Parameters.Add(m_database.CreateParameter("@lifetime", lifetime)); 202 cmd.Parameters.Add(m_database.CreateParameter("@validDate", validDate));
199 conn.Open(); 203 conn.Open();
200 204
201 if (cmd.ExecuteNonQuery() > 0) 205 if (cmd.ExecuteNonQuery() > 0)
@@ -208,11 +212,13 @@ namespace OpenSim.Data.MSSQL
208 212
209 private void DoExpire() 213 private void DoExpire()
210 { 214 {
211 string sql = "delete from tokens where validity < now()"; 215 DateTime currentDateTime = DateTime.Now;
216 string sql = "delete from tokens where validity < @currentDateTime";
212 using (SqlConnection conn = new SqlConnection(m_ConnectionString)) 217 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
213 using (SqlCommand cmd = new SqlCommand(sql, conn)) 218 using (SqlCommand cmd = new SqlCommand(sql, conn))
214 { 219 {
215 conn.Open(); 220 conn.Open();
221 cmd.Parameters.Add(m_database.CreateParameter("@currentDateTime", currentDateTime));
216 cmd.ExecuteNonQuery(); 222 cmd.ExecuteNonQuery();
217 } 223 }
218 m_LastExpire = System.Environment.TickCount; 224 m_LastExpire = System.Environment.TickCount;
diff --git a/OpenSim/Data/MSSQL/MSSQLAvatarData.cs b/OpenSim/Data/MSSQL/MSSQLAvatarData.cs
new file mode 100644
index 0000000..4992183
--- /dev/null
+++ b/OpenSim/Data/MSSQL/MSSQLAvatarData.cs
@@ -0,0 +1,71 @@
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 System.Data.SqlClient;
37
38namespace OpenSim.Data.MSSQL
39{
40 /// <summary>
41 /// A MSSQL Interface for Avatar Storage
42 /// </summary>
43 public class MSSQLAvatarData : MSSQLGenericTableHandler<AvatarBaseData>,
44 IAvatarData
45 {
46 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
47
48 public MSSQLAvatarData(string connectionString, string realm) :
49 base(connectionString, realm, "Avatar")
50 {
51 }
52
53 public bool Delete(UUID principalID, string name)
54 {
55 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
56 using (SqlCommand cmd = new SqlCommand())
57 {
58
59 cmd.CommandText = String.Format("DELETE FROM {0} where [PrincipalID] = @PrincipalID and [Name] = @Name", m_Realm);
60 cmd.Parameters.Add(m_database.CreateParameter("@PrincipalID", principalID.ToString()));
61 cmd.Parameters.Add(m_database.CreateParameter("@Name", name));
62 cmd.Connection = conn;
63 conn.Open();
64 if (cmd.ExecuteNonQuery() > 0)
65 return true;
66
67 return false;
68 }
69 }
70 }
71}
diff --git a/OpenSim/Data/MSSQL/MSSQLEstateData.cs b/OpenSim/Data/MSSQL/MSSQLEstateData.cs
index c0c6349..6f6f076 100644
--- a/OpenSim/Data/MSSQL/MSSQLEstateData.cs
+++ b/OpenSim/Data/MSSQL/MSSQLEstateData.cs
@@ -44,7 +44,7 @@ namespace OpenSim.Data.MSSQL
44 private static readonly ILog _Log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); 44 private static readonly ILog _Log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
45 45
46 private MSSQLManager _Database; 46 private MSSQLManager _Database;
47 47 private string m_connectionString;
48 private FieldInfo[] _Fields; 48 private FieldInfo[] _Fields;
49 private Dictionary<string, FieldInfo> _FieldMap = new Dictionary<string, FieldInfo>(); 49 private Dictionary<string, FieldInfo> _FieldMap = new Dictionary<string, FieldInfo>();
50 50
@@ -58,22 +58,9 @@ namespace OpenSim.Data.MSSQL
58 { 58 {
59 if (!string.IsNullOrEmpty(connectionString)) 59 if (!string.IsNullOrEmpty(connectionString))
60 { 60 {
61 m_connectionString = connectionString;
61 _Database = new MSSQLManager(connectionString); 62 _Database = new MSSQLManager(connectionString);
62 } 63 }
63 else
64 {
65 //TODO when can this be deleted
66 IniFile iniFile = new IniFile("mssql_connection.ini");
67 string settingDataSource = iniFile.ParseFileReadValue("data_source");
68 string settingInitialCatalog = iniFile.ParseFileReadValue("initial_catalog");
69 string settingPersistSecurityInfo = iniFile.ParseFileReadValue("persist_security_info");
70 string settingUserId = iniFile.ParseFileReadValue("user_id");
71 string settingPassword = iniFile.ParseFileReadValue("password");
72
73 _Database =
74 new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId,
75 settingPassword);
76 }
77 64
78 //Migration settings 65 //Migration settings
79 _Database.CheckMigration(_migrationStore); 66 _Database.CheckMigration(_migrationStore);
@@ -103,11 +90,11 @@ namespace OpenSim.Data.MSSQL
103 string sql = "select estate_settings." + String.Join(",estate_settings.", FieldList) + " from estate_map left join estate_settings on estate_map.EstateID = estate_settings.EstateID where estate_settings.EstateID is not null and RegionID = @RegionID"; 90 string sql = "select estate_settings." + String.Join(",estate_settings.", FieldList) + " from estate_map left join estate_settings on estate_map.EstateID = estate_settings.EstateID where estate_settings.EstateID is not null and RegionID = @RegionID";
104 91
105 bool insertEstate = false; 92 bool insertEstate = false;
106 93 using (SqlConnection conn = new SqlConnection(m_connectionString))
107 using (AutoClosingSqlCommand cmd = _Database.Query(sql)) 94 using (SqlCommand cmd = new SqlCommand(sql, conn))
108 { 95 {
109 cmd.Parameters.Add(_Database.CreateParameter("@RegionID", regionID)); 96 cmd.Parameters.Add(_Database.CreateParameter("@RegionID", regionID));
110 97 conn.Open();
111 using (SqlDataReader reader = cmd.ExecuteReader()) 98 using (SqlDataReader reader = cmd.ExecuteReader())
112 { 99 {
113 if (reader.Read()) 100 if (reader.Read())
@@ -124,7 +111,7 @@ namespace OpenSim.Data.MSSQL
124 } 111 }
125 else if (_FieldMap[name].GetValue(es) is UUID) 112 else if (_FieldMap[name].GetValue(es) is UUID)
126 { 113 {
127 _FieldMap[name].SetValue(es, new UUID((Guid) reader[name])); // uuid); 114 _FieldMap[name].SetValue(es, new UUID((Guid)reader[name])); // uuid);
128 } 115 }
129 else 116 else
130 { 117 {
@@ -149,34 +136,36 @@ namespace OpenSim.Data.MSSQL
149 sql = string.Format("insert into estate_settings ({0}) values ( @{1})", String.Join(",", names.ToArray()), String.Join(", @", names.ToArray())); 136 sql = string.Format("insert into estate_settings ({0}) values ( @{1})", String.Join(",", names.ToArray()), String.Join(", @", names.ToArray()));
150 137
151 //_Log.Debug("[DB ESTATE]: SQL: " + sql); 138 //_Log.Debug("[DB ESTATE]: SQL: " + sql);
152 using (SqlConnection connection = _Database.DatabaseConnection()) 139 using (SqlConnection conn = new SqlConnection(m_connectionString))
140 using (SqlCommand insertCommand = new SqlCommand(sql, conn))
153 { 141 {
154 using (SqlCommand insertCommand = connection.CreateCommand()) 142 insertCommand.CommandText = sql + " SET @ID = SCOPE_IDENTITY()";
155 {
156 insertCommand.CommandText = sql + " SET @ID = SCOPE_IDENTITY()";
157
158 foreach (string name in names)
159 {
160 insertCommand.Parameters.Add(_Database.CreateParameter("@" + name, _FieldMap[name].GetValue(es)));
161 }
162 SqlParameter idParameter = new SqlParameter("@ID", SqlDbType.Int);
163 idParameter.Direction = ParameterDirection.Output;
164 insertCommand.Parameters.Add(idParameter);
165
166 insertCommand.ExecuteNonQuery();
167 143
168 es.EstateID = Convert.ToUInt32(idParameter.Value); 144 foreach (string name in names)
145 {
146 insertCommand.Parameters.Add(_Database.CreateParameter("@" + name, _FieldMap[name].GetValue(es)));
169 } 147 }
148 SqlParameter idParameter = new SqlParameter("@ID", SqlDbType.Int);
149 idParameter.Direction = ParameterDirection.Output;
150 insertCommand.Parameters.Add(idParameter);
151 conn.Open();
152 insertCommand.ExecuteNonQuery();
153
154 es.EstateID = Convert.ToUInt32(idParameter.Value);
170 } 155 }
171 156
172 using (AutoClosingSqlCommand cmd = _Database.Query("INSERT INTO [estate_map] ([RegionID] ,[EstateID]) VALUES (@RegionID, @EstateID)")) 157 sql = "INSERT INTO [estate_map] ([RegionID] ,[EstateID]) VALUES (@RegionID, @EstateID)";
158 using (SqlConnection conn = new SqlConnection(m_connectionString))
159 using (SqlCommand cmd = new SqlCommand(sql, conn))
173 { 160 {
161
174 cmd.Parameters.Add(_Database.CreateParameter("@RegionID", regionID)); 162 cmd.Parameters.Add(_Database.CreateParameter("@RegionID", regionID));
175 cmd.Parameters.Add(_Database.CreateParameter("@EstateID", es.EstateID)); 163 cmd.Parameters.Add(_Database.CreateParameter("@EstateID", es.EstateID));
176 // This will throw on dupe key 164 // This will throw on dupe key
177 try 165 try
178 { 166 {
179 cmd.ExecuteNonQuery(); 167 conn.Open();
168 cmd.ExecuteNonQuery();
180 } 169 }
181 catch (Exception e) 170 catch (Exception e)
182 { 171 {
@@ -187,12 +176,14 @@ namespace OpenSim.Data.MSSQL
187 // Munge and transfer the ban list 176 // Munge and transfer the ban list
188 177
189 sql = string.Format("insert into estateban select {0}, bannedUUID, bannedIp, bannedIpHostMask, '' from regionban where regionban.regionUUID = @UUID", es.EstateID); 178 sql = string.Format("insert into estateban select {0}, bannedUUID, bannedIp, bannedIpHostMask, '' from regionban where regionban.regionUUID = @UUID", es.EstateID);
190 using (AutoClosingSqlCommand cmd = _Database.Query(sql)) 179 using (SqlConnection conn = new SqlConnection(m_connectionString))
180 using (SqlCommand cmd = new SqlCommand(sql, conn))
191 { 181 {
182
192 cmd.Parameters.Add(_Database.CreateParameter("@UUID", regionID)); 183 cmd.Parameters.Add(_Database.CreateParameter("@UUID", regionID));
193 try 184 try
194 { 185 {
195 186 conn.Open();
196 cmd.ExecuteNonQuery(); 187 cmd.ExecuteNonQuery();
197 } 188 }
198 catch (Exception) 189 catch (Exception)
@@ -226,7 +217,7 @@ namespace OpenSim.Data.MSSQL
226 217
227 names.Remove("EstateID"); 218 names.Remove("EstateID");
228 219
229 string sql = string.Format("UPDATE estate_settings SET ") ; 220 string sql = string.Format("UPDATE estate_settings SET ");
230 foreach (string name in names) 221 foreach (string name in names)
231 { 222 {
232 sql += name + " = @" + name + ", "; 223 sql += name + " = @" + name + ", ";
@@ -234,7 +225,8 @@ namespace OpenSim.Data.MSSQL
234 sql = sql.Remove(sql.LastIndexOf(",")); 225 sql = sql.Remove(sql.LastIndexOf(","));
235 sql += " WHERE EstateID = @EstateID"; 226 sql += " WHERE EstateID = @EstateID";
236 227
237 using (AutoClosingSqlCommand cmd = _Database.Query(sql)) 228 using (SqlConnection conn = new SqlConnection(m_connectionString))
229 using (SqlCommand cmd = new SqlCommand(sql, conn))
238 { 230 {
239 foreach (string name in names) 231 foreach (string name in names)
240 { 232 {
@@ -242,6 +234,7 @@ namespace OpenSim.Data.MSSQL
242 } 234 }
243 235
244 cmd.Parameters.Add(_Database.CreateParameter("@EstateID", es.EstateID)); 236 cmd.Parameters.Add(_Database.CreateParameter("@EstateID", es.EstateID));
237 conn.Open();
245 cmd.ExecuteNonQuery(); 238 cmd.ExecuteNonQuery();
246 } 239 }
247 240
@@ -266,12 +259,13 @@ namespace OpenSim.Data.MSSQL
266 259
267 string sql = "select bannedUUID from estateban where EstateID = @EstateID"; 260 string sql = "select bannedUUID from estateban where EstateID = @EstateID";
268 261
269 using (AutoClosingSqlCommand cmd = _Database.Query(sql)) 262 using (SqlConnection conn = new SqlConnection(m_connectionString))
263 using (SqlCommand cmd = new SqlCommand(sql, conn))
270 { 264 {
271 SqlParameter idParameter = new SqlParameter("@EstateID", SqlDbType.Int); 265 SqlParameter idParameter = new SqlParameter("@EstateID", SqlDbType.Int);
272 idParameter.Value = es.EstateID; 266 idParameter.Value = es.EstateID;
273 cmd.Parameters.Add(idParameter); 267 cmd.Parameters.Add(idParameter);
274 268 conn.Open();
275 using (SqlDataReader reader = cmd.ExecuteReader()) 269 using (SqlDataReader reader = cmd.ExecuteReader())
276 { 270 {
277 while (reader.Read()) 271 while (reader.Read())
@@ -293,10 +287,11 @@ namespace OpenSim.Data.MSSQL
293 287
294 string sql = string.Format("select uuid from {0} where EstateID = @EstateID", table); 288 string sql = string.Format("select uuid from {0} where EstateID = @EstateID", table);
295 289
296 using (AutoClosingSqlCommand cmd = _Database.Query(sql)) 290 using (SqlConnection conn = new SqlConnection(m_connectionString))
291 using (SqlCommand cmd = new SqlCommand(sql, conn))
297 { 292 {
298 cmd.Parameters.Add(_Database.CreateParameter("@EstateID", estateID)); 293 cmd.Parameters.Add(_Database.CreateParameter("@EstateID", estateID));
299 294 conn.Open();
300 using (SqlDataReader reader = cmd.ExecuteReader()) 295 using (SqlDataReader reader = cmd.ExecuteReader())
301 { 296 {
302 while (reader.Read()) 297 while (reader.Read())
@@ -313,20 +308,24 @@ namespace OpenSim.Data.MSSQL
313 { 308 {
314 //Delete first 309 //Delete first
315 string sql = "delete from estateban where EstateID = @EstateID"; 310 string sql = "delete from estateban where EstateID = @EstateID";
316 using (AutoClosingSqlCommand cmd = _Database.Query(sql)) 311 using (SqlConnection conn = new SqlConnection(m_connectionString))
312 using (SqlCommand cmd = new SqlCommand(sql, conn))
317 { 313 {
318 cmd.Parameters.Add(_Database.CreateParameter("@EstateID", es.EstateID)); 314 cmd.Parameters.Add(_Database.CreateParameter("@EstateID", es.EstateID));
315 conn.Open();
319 cmd.ExecuteNonQuery(); 316 cmd.ExecuteNonQuery();
320 } 317 }
321 318
322 //Insert after 319 //Insert after
323 sql = "insert into estateban (EstateID, bannedUUID) values ( @EstateID, @bannedUUID )"; 320 sql = "insert into estateban (EstateID, bannedUUID) values ( @EstateID, @bannedUUID )";
324 using (AutoClosingSqlCommand cmd = _Database.Query(sql)) 321 using (SqlConnection conn = new SqlConnection(m_connectionString))
322 using (SqlCommand cmd = new SqlCommand(sql, conn))
325 { 323 {
326 foreach (EstateBan b in es.EstateBans) 324 foreach (EstateBan b in es.EstateBans)
327 { 325 {
328 cmd.Parameters.Add(_Database.CreateParameter("@EstateID", es.EstateID)); 326 cmd.Parameters.Add(_Database.CreateParameter("@EstateID", es.EstateID));
329 cmd.Parameters.Add(_Database.CreateParameter("@bannedUUID", b.BannedUserID)); 327 cmd.Parameters.Add(_Database.CreateParameter("@bannedUUID", b.BannedUserID));
328 conn.Open();
330 cmd.ExecuteNonQuery(); 329 cmd.ExecuteNonQuery();
331 cmd.Parameters.Clear(); 330 cmd.Parameters.Clear();
332 } 331 }
@@ -337,14 +336,16 @@ namespace OpenSim.Data.MSSQL
337 { 336 {
338 //Delete first 337 //Delete first
339 string sql = string.Format("delete from {0} where EstateID = @EstateID", table); 338 string sql = string.Format("delete from {0} where EstateID = @EstateID", table);
340 using (AutoClosingSqlCommand cmd = _Database.Query(sql)) 339 using (SqlConnection conn = new SqlConnection(m_connectionString))
340 using (SqlCommand cmd = new SqlCommand(sql, conn))
341 { 341 {
342 cmd.Parameters.Add(_Database.CreateParameter("@EstateID", estateID)); 342 cmd.Parameters.Add(_Database.CreateParameter("@EstateID", estateID));
343 cmd.ExecuteNonQuery(); 343 cmd.ExecuteNonQuery();
344 } 344 }
345 345
346 sql = string.Format("insert into {0} (EstateID, uuid) values ( @EstateID, @uuid )", table); 346 sql = string.Format("insert into {0} (EstateID, uuid) values ( @EstateID, @uuid )", table);
347 using (AutoClosingSqlCommand cmd = _Database.Query(sql)) 347 using (SqlConnection conn = new SqlConnection(m_connectionString))
348 using (SqlCommand cmd = new SqlCommand(sql, conn))
348 { 349 {
349 cmd.Parameters.Add(_Database.CreateParameter("@EstateID", estateID)); 350 cmd.Parameters.Add(_Database.CreateParameter("@EstateID", estateID));
350 351
@@ -359,7 +360,7 @@ namespace OpenSim.Data.MSSQL
359 } 360 }
360 else 361 else
361 cmd.Parameters["@uuid"].Value = uuid.Guid; //.ToString(); //TODO check if this works 362 cmd.Parameters["@uuid"].Value = uuid.Guid; //.ToString(); //TODO check if this works
362 363 conn.Open();
363 cmd.ExecuteNonQuery(); 364 cmd.ExecuteNonQuery();
364 } 365 }
365 } 366 }
diff --git a/OpenSim/Data/MSSQL/MSSQLFriendsData.cs b/OpenSim/Data/MSSQL/MSSQLFriendsData.cs
new file mode 100644
index 0000000..34da943
--- /dev/null
+++ b/OpenSim/Data/MSSQL/MSSQLFriendsData.cs
@@ -0,0 +1,83 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ''AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections;
30using System.Collections.Generic;
31using System.Data;
32using OpenMetaverse;
33using OpenSim.Framework;
34using System.Data.SqlClient;
35using System.Reflection;
36using System.Text;
37
38namespace OpenSim.Data.MSSQL
39{
40 public class MSSQLFriendsData : MSSQLGenericTableHandler<FriendsData>, IFriendsData
41 {
42 public MSSQLFriendsData(string connectionString, string realm)
43 : base(connectionString, realm, "FriendsStore")
44 {
45 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
46 {
47 conn.Open();
48 Migration m = new Migration(conn, GetType().Assembly, "FriendsStore");
49 m.Update();
50 }
51 }
52
53 public bool Delete(UUID principalID, string friend)
54 {
55 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
56 using (SqlCommand cmd = new SqlCommand())
57 {
58 cmd.CommandText = String.Format("delete from {0} where PrincipalID = @PrincipalID and Friend = @Friend", m_Realm);
59 cmd.Parameters.Add(m_database.CreateParameter("@PrincipalID", principalID.ToString()));
60 cmd.Parameters.Add(m_database.CreateParameter("@Friend", friend));
61 cmd.Connection = conn;
62 conn.Open();
63 cmd.ExecuteNonQuery();
64
65 return true;
66 }
67 }
68
69 public FriendsData[] GetFriends(UUID principalID)
70 {
71 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
72 using (SqlCommand cmd = new SqlCommand())
73 {
74
75 cmd.CommandText = String.Format("select a.*,b.Flags as TheirFlags from {0} as a left join {0} as b on a.PrincipalID = b.Friend and a.Friend = b.PrincipalID where a.PrincipalID = ?PrincipalID and b.Flags is not null", m_Realm);
76 cmd.Parameters.Add(m_database.CreateParameter("@PrincipalID", principalID.ToString()));
77 cmd.Connection = conn;
78 conn.Open();
79 return DoQuery(cmd);
80 }
81 }
82 }
83}
diff --git a/OpenSim/Data/MSSQL/MSSQLGenericTableHandler.cs b/OpenSim/Data/MSSQL/MSSQLGenericTableHandler.cs
new file mode 100644
index 0000000..506056d
--- /dev/null
+++ b/OpenSim/Data/MSSQL/MSSQLGenericTableHandler.cs
@@ -0,0 +1,359 @@
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;
33using System.Data.SqlClient;
34using OpenMetaverse;
35using OpenSim.Framework;
36using OpenSim.Region.Framework.Interfaces;
37using System.Text;
38
39namespace OpenSim.Data.MSSQL
40{
41 public class MSSQLGenericTableHandler<T> where T : class, new()
42 {
43 private static readonly ILog m_log =
44 LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
45
46 protected string m_ConnectionString;
47 protected MSSQLManager m_database; //used for parameter type translation
48 protected Dictionary<string, FieldInfo> m_Fields =
49 new Dictionary<string, FieldInfo>();
50
51 protected List<string> m_ColumnNames = null;
52 protected string m_Realm;
53 protected FieldInfo m_DataField = null;
54
55 public MSSQLGenericTableHandler(string connectionString,
56 string realm, string storeName)
57 {
58 m_Realm = realm;
59
60 if (storeName != String.Empty)
61 {
62 Assembly assem = GetType().Assembly;
63 m_ConnectionString = connectionString;
64 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
65 {
66 conn.Open();
67 Migration m = new Migration(conn, assem, storeName);
68 m.Update();
69 }
70
71 }
72 m_database = new MSSQLManager(m_ConnectionString);
73
74 Type t = typeof(T);
75 FieldInfo[] fields = t.GetFields(BindingFlags.Public |
76 BindingFlags.Instance |
77 BindingFlags.DeclaredOnly);
78
79 if (fields.Length == 0)
80 return;
81
82 foreach (FieldInfo f in fields)
83 {
84 if (f.Name != "Data")
85 m_Fields[f.Name] = f;
86 else
87 m_DataField = f;
88 }
89
90 }
91
92 private void CheckColumnNames(SqlDataReader reader)
93 {
94 if (m_ColumnNames != null)
95 return;
96
97 m_ColumnNames = new List<string>();
98
99 DataTable schemaTable = reader.GetSchemaTable();
100 foreach (DataRow row in schemaTable.Rows)
101 {
102 if (row["ColumnName"] != null &&
103 (!m_Fields.ContainsKey(row["ColumnName"].ToString())))
104 m_ColumnNames.Add(row["ColumnName"].ToString());
105
106 }
107 }
108
109 private List<string> GetConstraints()
110 {
111 List<string> constraints = new List<string>();
112 string query = string.Format(@"SELECT
113 COL_NAME(ic.object_id,ic.column_id) AS column_name
114 FROM sys.indexes AS i
115 INNER JOIN sys.index_columns AS ic
116 ON i.object_id = ic.object_id AND i.index_id = ic.index_id
117 WHERE i.is_primary_key = 1
118 AND i.object_id = OBJECT_ID('{0}');", m_Realm);
119 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
120 using (SqlCommand cmd = new SqlCommand(query, conn))
121 {
122 conn.Open();
123 using (SqlDataReader rdr = cmd.ExecuteReader())
124 {
125 while (rdr.Read())
126 {
127 // query produces 0 to many rows of single column, so always add the first item in each row
128 constraints.Add((string)rdr[0]);
129 }
130 }
131 return constraints;
132 }
133 }
134
135 public virtual T[] Get(string field, string key)
136 {
137 return Get(new string[] { field }, new string[] { key });
138 }
139
140 public virtual T[] Get(string[] fields, string[] keys)
141 {
142 if (fields.Length != keys.Length)
143 return new T[0];
144
145 List<string> terms = new List<string>();
146
147 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
148 using (SqlCommand cmd = new SqlCommand())
149 {
150
151 for (int i = 0; i < fields.Length; i++)
152 {
153 cmd.Parameters.Add(m_database.CreateParameter(fields[i], keys[i]));
154 terms.Add("[" + fields[i] + "] = @" + fields[i]);
155 }
156
157 string where = String.Join(" AND ", terms.ToArray());
158
159 string query = String.Format("SELECT * FROM {0} WHERE {1}",
160 m_Realm, where);
161
162 cmd.Connection = conn;
163 cmd.CommandText = query;
164 conn.Open();
165 return DoQuery(cmd);
166 }
167 }
168
169 protected T[] DoQuery(SqlCommand cmd)
170 {
171 using (SqlDataReader reader = cmd.ExecuteReader())
172 {
173 if (reader == null)
174 return new T[0];
175
176 CheckColumnNames(reader);
177
178 List<T> result = new List<T>();
179
180 while (reader.Read())
181 {
182 T row = new T();
183
184 foreach (string name in m_Fields.Keys)
185 {
186 if (m_Fields[name].GetValue(row) is bool)
187 {
188 int v = Convert.ToInt32(reader[name]);
189 m_Fields[name].SetValue(row, v != 0 ? true : false);
190 }
191 else if (m_Fields[name].GetValue(row) is UUID)
192 {
193 UUID uuid = UUID.Zero;
194
195 UUID.TryParse(reader[name].ToString(), out uuid);
196 m_Fields[name].SetValue(row, uuid);
197 }
198 else if (m_Fields[name].GetValue(row) is int)
199 {
200 int v = Convert.ToInt32(reader[name]);
201 m_Fields[name].SetValue(row, v);
202 }
203 else
204 {
205 m_Fields[name].SetValue(row, reader[name]);
206 }
207 }
208
209 if (m_DataField != null)
210 {
211 Dictionary<string, string> data =
212 new Dictionary<string, string>();
213
214 foreach (string col in m_ColumnNames)
215 {
216 data[col] = reader[col].ToString();
217 if (data[col] == null)
218 data[col] = String.Empty;
219 }
220
221 m_DataField.SetValue(row, data);
222 }
223
224 result.Add(row);
225 }
226 return result.ToArray();
227 }
228 }
229
230 public virtual T[] Get(string where)
231 {
232 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
233 using (SqlCommand cmd = new SqlCommand())
234 {
235
236 string query = String.Format("SELECT * FROM {0} WHERE {1}",
237 m_Realm, where);
238 cmd.Connection = conn;
239 cmd.CommandText = query;
240
241 //m_log.WarnFormat("[MSSQLGenericTable]: SELECT {0} WHERE {1}", m_Realm, where);
242
243 conn.Open();
244 return DoQuery(cmd);
245 }
246 }
247
248 public virtual bool Store(T row)
249 {
250 List<string> constraintFields = GetConstraints();
251 List<KeyValuePair<string, string>> constraints = new List<KeyValuePair<string, string>>();
252
253 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
254 using (SqlCommand cmd = new SqlCommand())
255 {
256
257 StringBuilder query = new StringBuilder();
258 List<String> names = new List<String>();
259 List<String> values = new List<String>();
260
261 foreach (FieldInfo fi in m_Fields.Values)
262 {
263 names.Add(fi.Name);
264 values.Add("@" + fi.Name);
265 if (constraintFields.Count > 0 && constraintFields.Contains(fi.Name))
266 {
267 constraints.Add(new KeyValuePair<string, string>(fi.Name, fi.GetValue(row).ToString()));
268 }
269 cmd.Parameters.Add(m_database.CreateParameter(fi.Name, fi.GetValue(row).ToString()));
270 }
271
272 if (m_DataField != null)
273 {
274 Dictionary<string, string> data =
275 (Dictionary<string, string>)m_DataField.GetValue(row);
276
277 foreach (KeyValuePair<string, string> kvp in data)
278 {
279 if (constraintFields.Count > 0 && constraintFields.Contains(kvp.Key))
280 {
281 constraints.Add(new KeyValuePair<string, string>(kvp.Key, kvp.Key));
282 }
283 names.Add(kvp.Key);
284 values.Add("@" + kvp.Key);
285 cmd.Parameters.Add(m_database.CreateParameter("@" + kvp.Key, kvp.Value));
286 }
287
288 }
289
290 query.AppendFormat("UPDATE {0} SET ", m_Realm);
291 int i = 0;
292 for (i = 0; i < names.Count - 1; i++)
293 {
294 query.AppendFormat("[{0}] = {1}, ", names[i], values[i]);
295 }
296 query.AppendFormat("[{0}] = {1} ", names[i], values[i]);
297 if (constraints.Count > 0)
298 {
299 List<string> terms = new List<string>();
300 for (int j = 0; j < constraints.Count; j++)
301 {
302 terms.Add(" [" + constraints[j].Key + "] = @" + constraints[j].Key);
303 }
304 string where = String.Join(" AND ", terms.ToArray());
305 query.AppendFormat(" WHERE {0} ", where);
306
307 }
308 cmd.Connection = conn;
309 cmd.CommandText = query.ToString();
310
311 conn.Open();
312 if (cmd.ExecuteNonQuery() > 0)
313 {
314 //m_log.WarnFormat("[MSSQLGenericTable]: Updating {0}", m_Realm);
315 return true;
316 }
317 else
318 {
319 // assume record has not yet been inserted
320
321 query = new StringBuilder();
322 query.AppendFormat("INSERT INTO {0} ([", m_Realm);
323 query.Append(String.Join("],[", names.ToArray()));
324 query.Append("]) values (" + String.Join(",", values.ToArray()) + ")");
325 cmd.Connection = conn;
326 cmd.CommandText = query.ToString();
327 //m_log.WarnFormat("[MSSQLGenericTable]: Inserting into {0}", m_Realm);
328 if (conn.State != ConnectionState.Open)
329 conn.Open();
330 if (cmd.ExecuteNonQuery() > 0)
331 return true;
332 }
333
334 return false;
335 }
336 }
337
338 public virtual bool Delete(string field, string val)
339 {
340 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
341 using (SqlCommand cmd = new SqlCommand())
342 {
343 string deleteCommand = String.Format("DELETE FROM {0} WHERE [{1}] = @{1}", m_Realm, field);
344 cmd.CommandText = deleteCommand;
345
346 cmd.Parameters.Add(m_database.CreateParameter(field, val));
347 cmd.Connection = conn;
348 conn.Open();
349
350 if (cmd.ExecuteNonQuery() > 0)
351 {
352 //m_log.Warn("[MSSQLGenericTable]: " + deleteCommand);
353 return true;
354 }
355 return false;
356 }
357 }
358 }
359}
diff --git a/OpenSim/Data/MSSQL/MSSQLGridData.cs b/OpenSim/Data/MSSQL/MSSQLGridData.cs
new file mode 100644
index 0000000..6adb5f3
--- /dev/null
+++ b/OpenSim/Data/MSSQL/MSSQLGridData.cs
@@ -0,0 +1,582 @@
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.Data.SqlClient;
32using System.Reflection;
33using log4net;
34using OpenMetaverse;
35using OpenSim.Framework;
36
37namespace OpenSim.Data.MSSQL
38{
39 /// <summary>
40 /// A grid data interface for MSSQL Server
41 /// </summary>
42 public class MSSQLGridData : GridDataBase
43 {
44 private const string _migrationStore = "GridStore";
45
46 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
47
48 /// <summary>
49 /// Database manager
50 /// </summary>
51 private MSSQLManager database;
52 private string m_connectionString;
53
54 private string m_regionsTableName = "regions";
55
56 #region IPlugin Members
57
58 // [Obsolete("Cannot be default-initialized!")]
59 override public void Initialise()
60 {
61 m_log.Info("[GRID DB]: " + Name + " cannot be default-initialized!");
62 throw new PluginNotInitialisedException(Name);
63 }
64
65 /// <summary>
66 /// Initialises the Grid Interface
67 /// </summary>
68 /// <param name="connectionString">connect string</param>
69 /// <remarks>use mssql_connection.ini</remarks>
70 override public void Initialise(string connectionString)
71 {
72 m_connectionString = connectionString;
73 database = new MSSQLManager(connectionString);
74
75 //New migrations check of store
76 database.CheckMigration(_migrationStore);
77 }
78
79 /// <summary>
80 /// Shuts down the grid interface
81 /// </summary>
82 override public void Dispose()
83 {
84 database = null;
85 }
86
87 /// <summary>
88 /// The name of this DB provider.
89 /// </summary>
90 /// <returns>A string containing the storage system name</returns>
91 override public string Name
92 {
93 get { return "MSSQL OpenGridData"; }
94 }
95
96 /// <summary>
97 /// Database provider version.
98 /// </summary>
99 /// <returns>A string containing the storage system version</returns>
100 override public string Version
101 {
102 get { return "0.1"; }
103 }
104
105 #endregion
106
107 #region Public override GridDataBase methods
108
109 /// <summary>
110 /// Returns a list of regions within the specified ranges
111 /// </summary>
112 /// <param name="xmin">minimum X coordinate</param>
113 /// <param name="ymin">minimum Y coordinate</param>
114 /// <param name="xmax">maximum X coordinate</param>
115 /// <param name="ymax">maximum Y coordinate</param>
116 /// <returns>null</returns>
117 /// <remarks>always return null</remarks>
118 override public RegionProfileData[] GetProfilesInRange(uint xmin, uint ymin, uint xmax, uint ymax)
119 {
120 string sql = "SELECT * FROM regions WHERE locX >= @xmin AND locX <= @xmax AND locY >= @ymin AND locY <= @ymax";
121 using (SqlConnection conn = new SqlConnection(m_connectionString))
122 using (SqlCommand cmd = new SqlCommand(sql, conn))
123 {
124 cmd.Parameters.Add(database.CreateParameter("xmin", xmin));
125 cmd.Parameters.Add(database.CreateParameter("ymin", ymin));
126 cmd.Parameters.Add(database.CreateParameter("xmax", xmax));
127 cmd.Parameters.Add(database.CreateParameter("ymax", ymax));
128
129 List<RegionProfileData> rows = new List<RegionProfileData>();
130 conn.Open();
131 using (SqlDataReader reader = cmd.ExecuteReader())
132 {
133 while (reader.Read())
134 {
135 rows.Add(ReadSimRow(reader));
136 }
137 }
138
139 if (rows.Count > 0)
140 {
141 return rows.ToArray();
142 }
143 }
144 m_log.Info("[GRID DB] : Found no regions within range.");
145 return null;
146 }
147
148
149 /// <summary>
150 /// Returns up to maxNum profiles of regions that have a name starting with namePrefix
151 /// </summary>
152 /// <param name="namePrefix">The name to match against</param>
153 /// <param name="maxNum">Maximum number of profiles to return</param>
154 /// <returns>A list of sim profiles</returns>
155 override public List<RegionProfileData> GetRegionsByName (string namePrefix, uint maxNum)
156 {
157 string sql = "SELECT * FROM regions WHERE regionName LIKE @name";
158 using (SqlConnection conn = new SqlConnection(m_connectionString))
159 using (SqlCommand cmd = new SqlCommand(sql, conn))
160 {
161 cmd.Parameters.Add(database.CreateParameter("name", namePrefix + "%"));
162
163 List<RegionProfileData> rows = new List<RegionProfileData>();
164 conn.Open();
165 using (SqlDataReader reader = cmd.ExecuteReader())
166 {
167 while (rows.Count < maxNum && reader.Read())
168 {
169 rows.Add(ReadSimRow(reader));
170 }
171 }
172
173 return rows;
174 }
175 }
176
177 /// <summary>
178 /// Returns a sim profile from its location
179 /// </summary>
180 /// <param name="handle">Region location handle</param>
181 /// <returns>Sim profile</returns>
182 override public RegionProfileData GetProfileByHandle(ulong handle)
183 {
184 string sql = "SELECT * FROM " + m_regionsTableName + " WHERE regionHandle = @handle";
185 using (SqlConnection conn = new SqlConnection(m_connectionString))
186 using (SqlCommand cmd = new SqlCommand(sql, conn))
187 {
188 cmd.Parameters.Add(database.CreateParameter("handle", handle));
189 conn.Open();
190 using (SqlDataReader reader = cmd.ExecuteReader())
191 {
192 if (reader.Read())
193 {
194 return ReadSimRow(reader);
195 }
196 }
197 }
198 m_log.InfoFormat("[GRID DB] : No region found with handle : {0}", handle);
199 return null;
200 }
201
202 /// <summary>
203 /// Returns a sim profile from its UUID
204 /// </summary>
205 /// <param name="uuid">The region UUID</param>
206 /// <returns>The sim profile</returns>
207 override public RegionProfileData GetProfileByUUID(UUID uuid)
208 {
209 string sql = "SELECT * FROM " + m_regionsTableName + " WHERE uuid = @uuid";
210 using (SqlConnection conn = new SqlConnection(m_connectionString))
211 using (SqlCommand cmd = new SqlCommand(sql, conn))
212 {
213 cmd.Parameters.Add(database.CreateParameter("uuid", uuid));
214 conn.Open();
215 using (SqlDataReader reader = cmd.ExecuteReader())
216 {
217 if (reader.Read())
218 {
219 return ReadSimRow(reader);
220 }
221 }
222 }
223 m_log.InfoFormat("[GRID DB] : No region found with UUID : {0}", uuid);
224 return null;
225 }
226
227 /// <summary>
228 /// Returns a sim profile from it's Region name string
229 /// </summary>
230 /// <param name="regionName">The region name search query</param>
231 /// <returns>The sim profile</returns>
232 override public RegionProfileData GetProfileByString(string regionName)
233 {
234 if (regionName.Length > 2)
235 {
236 string sql = "SELECT top 1 * FROM " + m_regionsTableName + " WHERE regionName like @regionName order by regionName";
237
238 using (SqlConnection conn = new SqlConnection(m_connectionString))
239 using (SqlCommand cmd = new SqlCommand(sql, conn))
240 {
241 cmd.Parameters.Add(database.CreateParameter("regionName", regionName + "%"));
242 conn.Open();
243 using (SqlDataReader reader = cmd.ExecuteReader())
244 {
245 if (reader.Read())
246 {
247 return ReadSimRow(reader);
248 }
249 }
250 }
251 m_log.InfoFormat("[GRID DB] : No region found with regionName : {0}", regionName);
252 return null;
253 }
254
255 m_log.Error("[GRID DB]: Searched for a Region Name shorter then 3 characters");
256 return null;
257 }
258
259 /// <summary>
260 /// Adds a new specified region to the database
261 /// </summary>
262 /// <param name="profile">The profile to add</param>
263 /// <returns>A dataresponse enum indicating success</returns>
264 override public DataResponse StoreProfile(RegionProfileData profile)
265 {
266 if (GetProfileByUUID(profile.UUID) == null)
267 {
268 if (InsertRegionRow(profile))
269 {
270 return DataResponse.RESPONSE_OK;
271 }
272 }
273 else
274 {
275 if (UpdateRegionRow(profile))
276 {
277 return DataResponse.RESPONSE_OK;
278 }
279 }
280
281 return DataResponse.RESPONSE_ERROR;
282 }
283
284 /// <summary>
285 /// Deletes a sim profile from the database
286 /// </summary>
287 /// <param name="uuid">the sim UUID</param>
288 /// <returns>Successful?</returns>
289 //public DataResponse DeleteProfile(RegionProfileData profile)
290 override public DataResponse DeleteProfile(string uuid)
291 {
292 string sql = "DELETE FROM regions WHERE uuid = @uuid;";
293
294 using (SqlConnection conn = new SqlConnection(m_connectionString))
295 using (SqlCommand cmd = new SqlCommand(sql, conn))
296 {
297 cmd.Parameters.Add(database.CreateParameter("uuid", uuid));
298 try
299 {
300 conn.Open();
301 cmd.ExecuteNonQuery();
302 return DataResponse.RESPONSE_OK;
303 }
304 catch (Exception e)
305 {
306 m_log.DebugFormat("[GRID DB] : Error deleting region info, error is : {0}", e.Message);
307 return DataResponse.RESPONSE_ERROR;
308 }
309 }
310 }
311
312 #endregion
313
314 #region Methods that are not used or deprecated (still needed because of base class)
315
316 /// <summary>
317 /// DEPRECATED. Attempts to authenticate a region by comparing a shared secret.
318 /// </summary>
319 /// <param name="uuid">The UUID of the challenger</param>
320 /// <param name="handle">The attempted regionHandle of the challenger</param>
321 /// <param name="authkey">The secret</param>
322 /// <returns>Whether the secret and regionhandle match the database entry for UUID</returns>
323 override public bool AuthenticateSim(UUID uuid, ulong handle, string authkey)
324 {
325 bool throwHissyFit = false; // Should be true by 1.0
326
327 if (throwHissyFit)
328 throw new Exception("CRYPTOWEAK AUTHENTICATE: Refusing to authenticate due to replay potential.");
329
330 RegionProfileData data = GetProfileByUUID(uuid);
331
332 return (handle == data.regionHandle && authkey == data.regionSecret);
333 }
334
335 /// <summary>
336 /// NOT YET FUNCTIONAL. Provides a cryptographic authentication of a region
337 /// </summary>
338 /// <remarks>This requires a security audit.</remarks>
339 /// <param name="uuid"></param>
340 /// <param name="handle"></param>
341 /// <param name="authhash"></param>
342 /// <param name="challenge"></param>
343 /// <returns></returns>
344 public bool AuthenticateSim(UUID uuid, ulong handle, string authhash, string challenge)
345 {
346 // SHA512Managed HashProvider = new SHA512Managed();
347 // Encoding TextProvider = new UTF8Encoding();
348
349 // byte[] stream = TextProvider.GetBytes(uuid.ToString() + ":" + handle.ToString() + ":" + challenge);
350 // byte[] hash = HashProvider.ComputeHash(stream);
351 return false;
352 }
353
354 /// <summary>
355 /// NOT IMPLEMENTED
356 /// WHEN IS THIS GONNA BE IMPLEMENTED.
357 /// </summary>
358 /// <param name="x"></param>
359 /// <param name="y"></param>
360 /// <returns>null</returns>
361 override public ReservationData GetReservationAtPoint(uint x, uint y)
362 {
363 return null;
364 }
365
366 #endregion
367
368 #region private methods
369
370 /// <summary>
371 /// Reads a region row from a database reader
372 /// </summary>
373 /// <param name="reader">An active database reader</param>
374 /// <returns>A region profile</returns>
375 private static RegionProfileData ReadSimRow(IDataRecord reader)
376 {
377 RegionProfileData retval = new RegionProfileData();
378
379 // Region Main gotta-have-or-we-return-null parts
380 UInt64 tmp64;
381 if (!UInt64.TryParse(reader["regionHandle"].ToString(), out tmp64))
382 {
383 return null;
384 }
385
386 retval.regionHandle = tmp64;
387
388// UUID tmp_uuid;
389// if (!UUID.TryParse((string)reader["uuid"], out tmp_uuid))
390// {
391// return null;
392// }
393
394 retval.UUID = new UUID((Guid)reader["uuid"]); // tmp_uuid;
395
396 // non-critical parts
397 retval.regionName = reader["regionName"].ToString();
398 retval.originUUID = new UUID((Guid)reader["originUUID"]);
399
400 // Secrets
401 retval.regionRecvKey = reader["regionRecvKey"].ToString();
402 retval.regionSecret = reader["regionSecret"].ToString();
403 retval.regionSendKey = reader["regionSendKey"].ToString();
404
405 // Region Server
406 retval.regionDataURI = reader["regionDataURI"].ToString();
407 retval.regionOnline = false; // Needs to be pinged before this can be set.
408 retval.serverIP = reader["serverIP"].ToString();
409 retval.serverPort = Convert.ToUInt32(reader["serverPort"]);
410 retval.serverURI = reader["serverURI"].ToString();
411 retval.httpPort = Convert.ToUInt32(reader["serverHttpPort"].ToString());
412 retval.remotingPort = Convert.ToUInt32(reader["serverRemotingPort"].ToString());
413
414 // Location
415 retval.regionLocX = Convert.ToUInt32(reader["locX"].ToString());
416 retval.regionLocY = Convert.ToUInt32(reader["locY"].ToString());
417 retval.regionLocZ = Convert.ToUInt32(reader["locZ"].ToString());
418
419 // Neighbours - 0 = No Override
420 retval.regionEastOverrideHandle = Convert.ToUInt64(reader["eastOverrideHandle"].ToString());
421 retval.regionWestOverrideHandle = Convert.ToUInt64(reader["westOverrideHandle"].ToString());
422 retval.regionSouthOverrideHandle = Convert.ToUInt64(reader["southOverrideHandle"].ToString());
423 retval.regionNorthOverrideHandle = Convert.ToUInt64(reader["northOverrideHandle"].ToString());
424
425 // Assets
426 retval.regionAssetURI = reader["regionAssetURI"].ToString();
427 retval.regionAssetRecvKey = reader["regionAssetRecvKey"].ToString();
428 retval.regionAssetSendKey = reader["regionAssetSendKey"].ToString();
429
430 // Userserver
431 retval.regionUserURI = reader["regionUserURI"].ToString();
432 retval.regionUserRecvKey = reader["regionUserRecvKey"].ToString();
433 retval.regionUserSendKey = reader["regionUserSendKey"].ToString();
434
435 // World Map Addition
436 retval.regionMapTextureID = new UUID((Guid)reader["regionMapTexture"]);
437 retval.owner_uuid = new UUID((Guid)reader["owner_uuid"]);
438 retval.maturity = Convert.ToUInt32(reader["access"]);
439 return retval;
440 }
441
442 /// <summary>
443 /// Update the specified region in the database
444 /// </summary>
445 /// <param name="profile">The profile to update</param>
446 /// <returns>success ?</returns>
447 private bool UpdateRegionRow(RegionProfileData profile)
448 {
449 bool returnval = false;
450
451 //Insert new region
452 string sql =
453 "UPDATE " + m_regionsTableName + @" SET
454 [regionHandle]=@regionHandle, [regionName]=@regionName,
455 [regionRecvKey]=@regionRecvKey, [regionSecret]=@regionSecret, [regionSendKey]=@regionSendKey,
456 [regionDataURI]=@regionDataURI, [serverIP]=@serverIP, [serverPort]=@serverPort, [serverURI]=@serverURI,
457 [locX]=@locX, [locY]=@locY, [locZ]=@locZ, [eastOverrideHandle]=@eastOverrideHandle,
458 [westOverrideHandle]=@westOverrideHandle, [southOverrideHandle]=@southOverrideHandle,
459 [northOverrideHandle]=@northOverrideHandle, [regionAssetURI]=@regionAssetURI,
460 [regionAssetRecvKey]=@regionAssetRecvKey, [regionAssetSendKey]=@regionAssetSendKey,
461 [regionUserURI]=@regionUserURI, [regionUserRecvKey]=@regionUserRecvKey, [regionUserSendKey]=@regionUserSendKey,
462 [regionMapTexture]=@regionMapTexture, [serverHttpPort]=@serverHttpPort,
463 [serverRemotingPort]=@serverRemotingPort, [owner_uuid]=@owner_uuid , [originUUID]=@originUUID
464 where [uuid]=@uuid";
465
466 using (SqlConnection conn = new SqlConnection(m_connectionString))
467 using (SqlCommand command = new SqlCommand(sql, conn))
468 {
469 command.Parameters.Add(database.CreateParameter("regionHandle", profile.regionHandle));
470 command.Parameters.Add(database.CreateParameter("regionName", profile.regionName));
471 command.Parameters.Add(database.CreateParameter("uuid", profile.UUID));
472 command.Parameters.Add(database.CreateParameter("regionRecvKey", profile.regionRecvKey));
473 command.Parameters.Add(database.CreateParameter("regionSecret", profile.regionSecret));
474 command.Parameters.Add(database.CreateParameter("regionSendKey", profile.regionSendKey));
475 command.Parameters.Add(database.CreateParameter("regionDataURI", profile.regionDataURI));
476 command.Parameters.Add(database.CreateParameter("serverIP", profile.serverIP));
477 command.Parameters.Add(database.CreateParameter("serverPort", profile.serverPort));
478 command.Parameters.Add(database.CreateParameter("serverURI", profile.serverURI));
479 command.Parameters.Add(database.CreateParameter("locX", profile.regionLocX));
480 command.Parameters.Add(database.CreateParameter("locY", profile.regionLocY));
481 command.Parameters.Add(database.CreateParameter("locZ", profile.regionLocZ));
482 command.Parameters.Add(database.CreateParameter("eastOverrideHandle", profile.regionEastOverrideHandle));
483 command.Parameters.Add(database.CreateParameter("westOverrideHandle", profile.regionWestOverrideHandle));
484 command.Parameters.Add(database.CreateParameter("northOverrideHandle", profile.regionNorthOverrideHandle));
485 command.Parameters.Add(database.CreateParameter("southOverrideHandle", profile.regionSouthOverrideHandle));
486 command.Parameters.Add(database.CreateParameter("regionAssetURI", profile.regionAssetURI));
487 command.Parameters.Add(database.CreateParameter("regionAssetRecvKey", profile.regionAssetRecvKey));
488 command.Parameters.Add(database.CreateParameter("regionAssetSendKey", profile.regionAssetSendKey));
489 command.Parameters.Add(database.CreateParameter("regionUserURI", profile.regionUserURI));
490 command.Parameters.Add(database.CreateParameter("regionUserRecvKey", profile.regionUserRecvKey));
491 command.Parameters.Add(database.CreateParameter("regionUserSendKey", profile.regionUserSendKey));
492 command.Parameters.Add(database.CreateParameter("regionMapTexture", profile.regionMapTextureID));
493 command.Parameters.Add(database.CreateParameter("serverHttpPort", profile.httpPort));
494 command.Parameters.Add(database.CreateParameter("serverRemotingPort", profile.remotingPort));
495 command.Parameters.Add(database.CreateParameter("owner_uuid", profile.owner_uuid));
496 command.Parameters.Add(database.CreateParameter("originUUID", profile.originUUID));
497 conn.Open();
498 try
499 {
500 command.ExecuteNonQuery();
501 returnval = true;
502 }
503 catch (Exception e)
504 {
505 m_log.Error("[GRID DB] : Error updating region, error: " + e.Message);
506 }
507 }
508
509 return returnval;
510 }
511
512 /// <summary>
513 /// Creates a new region in the database
514 /// </summary>
515 /// <param name="profile">The region profile to insert</param>
516 /// <returns>Successful?</returns>
517 private bool InsertRegionRow(RegionProfileData profile)
518 {
519 bool returnval = false;
520
521 //Insert new region
522 string sql =
523 "INSERT INTO " + m_regionsTableName + @" ([regionHandle], [regionName], [uuid], [regionRecvKey], [regionSecret], [regionSendKey], [regionDataURI],
524 [serverIP], [serverPort], [serverURI], [locX], [locY], [locZ], [eastOverrideHandle], [westOverrideHandle],
525 [southOverrideHandle], [northOverrideHandle], [regionAssetURI], [regionAssetRecvKey], [regionAssetSendKey],
526 [regionUserURI], [regionUserRecvKey], [regionUserSendKey], [regionMapTexture], [serverHttpPort],
527 [serverRemotingPort], [owner_uuid], [originUUID], [access])
528 VALUES (@regionHandle, @regionName, @uuid, @regionRecvKey, @regionSecret, @regionSendKey, @regionDataURI,
529 @serverIP, @serverPort, @serverURI, @locX, @locY, @locZ, @eastOverrideHandle, @westOverrideHandle,
530 @southOverrideHandle, @northOverrideHandle, @regionAssetURI, @regionAssetRecvKey, @regionAssetSendKey,
531 @regionUserURI, @regionUserRecvKey, @regionUserSendKey, @regionMapTexture, @serverHttpPort, @serverRemotingPort, @owner_uuid, @originUUID, @access);";
532
533 using (SqlConnection conn = new SqlConnection(m_connectionString))
534 using (SqlCommand command = new SqlCommand(sql, conn))
535 {
536 command.Parameters.Add(database.CreateParameter("regionHandle", profile.regionHandle));
537 command.Parameters.Add(database.CreateParameter("regionName", profile.regionName));
538 command.Parameters.Add(database.CreateParameter("uuid", profile.UUID));
539 command.Parameters.Add(database.CreateParameter("regionRecvKey", profile.regionRecvKey));
540 command.Parameters.Add(database.CreateParameter("regionSecret", profile.regionSecret));
541 command.Parameters.Add(database.CreateParameter("regionSendKey", profile.regionSendKey));
542 command.Parameters.Add(database.CreateParameter("regionDataURI", profile.regionDataURI));
543 command.Parameters.Add(database.CreateParameter("serverIP", profile.serverIP));
544 command.Parameters.Add(database.CreateParameter("serverPort", profile.serverPort));
545 command.Parameters.Add(database.CreateParameter("serverURI", profile.serverURI));
546 command.Parameters.Add(database.CreateParameter("locX", profile.regionLocX));
547 command.Parameters.Add(database.CreateParameter("locY", profile.regionLocY));
548 command.Parameters.Add(database.CreateParameter("locZ", profile.regionLocZ));
549 command.Parameters.Add(database.CreateParameter("eastOverrideHandle", profile.regionEastOverrideHandle));
550 command.Parameters.Add(database.CreateParameter("westOverrideHandle", profile.regionWestOverrideHandle));
551 command.Parameters.Add(database.CreateParameter("northOverrideHandle", profile.regionNorthOverrideHandle));
552 command.Parameters.Add(database.CreateParameter("southOverrideHandle", profile.regionSouthOverrideHandle));
553 command.Parameters.Add(database.CreateParameter("regionAssetURI", profile.regionAssetURI));
554 command.Parameters.Add(database.CreateParameter("regionAssetRecvKey", profile.regionAssetRecvKey));
555 command.Parameters.Add(database.CreateParameter("regionAssetSendKey", profile.regionAssetSendKey));
556 command.Parameters.Add(database.CreateParameter("regionUserURI", profile.regionUserURI));
557 command.Parameters.Add(database.CreateParameter("regionUserRecvKey", profile.regionUserRecvKey));
558 command.Parameters.Add(database.CreateParameter("regionUserSendKey", profile.regionUserSendKey));
559 command.Parameters.Add(database.CreateParameter("regionMapTexture", profile.regionMapTextureID));
560 command.Parameters.Add(database.CreateParameter("serverHttpPort", profile.httpPort));
561 command.Parameters.Add(database.CreateParameter("serverRemotingPort", profile.remotingPort));
562 command.Parameters.Add(database.CreateParameter("owner_uuid", profile.owner_uuid));
563 command.Parameters.Add(database.CreateParameter("originUUID", profile.originUUID));
564 command.Parameters.Add(database.CreateParameter("access", profile.maturity));
565 conn.Open();
566 try
567 {
568 command.ExecuteNonQuery();
569 returnval = true;
570 }
571 catch (Exception e)
572 {
573 m_log.Error("[GRID DB] : Error inserting region, error: " + e.Message);
574 }
575 }
576
577 return returnval;
578 }
579
580 #endregion
581 }
582}
diff --git a/OpenSim/Data/MSSQL/MSSQLInventoryData.cs b/OpenSim/Data/MSSQL/MSSQLInventoryData.cs
index 1482184..4815700 100644
--- a/OpenSim/Data/MSSQL/MSSQLInventoryData.cs
+++ b/OpenSim/Data/MSSQL/MSSQLInventoryData.cs
@@ -49,6 +49,7 @@ namespace OpenSim.Data.MSSQL
49 /// The database manager 49 /// The database manager
50 /// </summary> 50 /// </summary>
51 private MSSQLManager database; 51 private MSSQLManager database;
52 private string m_connectionString;
52 53
53 #region IPlugin members 54 #region IPlugin members
54 55
@@ -66,24 +67,9 @@ namespace OpenSim.Data.MSSQL
66 /// <remarks>use mssql_connection.ini</remarks> 67 /// <remarks>use mssql_connection.ini</remarks>
67 public void Initialise(string connectionString) 68 public void Initialise(string connectionString)
68 { 69 {
69 if (!string.IsNullOrEmpty(connectionString)) 70 m_connectionString = connectionString;
70 { 71 database = new MSSQLManager(connectionString);
71 database = new MSSQLManager(connectionString); 72
72 }
73 else
74 {
75 IniFile gridDataMSSqlFile = new IniFile("mssql_connection.ini");
76 string settingDataSource = gridDataMSSqlFile.ParseFileReadValue("data_source");
77 string settingInitialCatalog = gridDataMSSqlFile.ParseFileReadValue("initial_catalog");
78 string settingPersistSecurityInfo = gridDataMSSqlFile.ParseFileReadValue("persist_security_info");
79 string settingUserId = gridDataMSSqlFile.ParseFileReadValue("user_id");
80 string settingPassword = gridDataMSSqlFile.ParseFileReadValue("password");
81
82 database =
83 new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId,
84 settingPassword);
85 }
86
87 //New migrations check of store 73 //New migrations check of store
88 database.CheckMigration(_migrationStore); 74 database.CheckMigration(_migrationStore);
89 } 75 }
@@ -169,11 +155,13 @@ namespace OpenSim.Data.MSSQL
169 /// <returns>A folder class</returns> 155 /// <returns>A folder class</returns>
170 public InventoryFolderBase getInventoryFolder(UUID folderID) 156 public InventoryFolderBase getInventoryFolder(UUID folderID)
171 { 157 {
172 using (AutoClosingSqlCommand command = database.Query("SELECT * FROM inventoryfolders WHERE folderID = @folderID")) 158 string sql = "SELECT * FROM inventoryfolders WHERE folderID = @folderID";
159 using (SqlConnection conn = new SqlConnection(m_connectionString))
160 using (SqlCommand cmd = new SqlCommand(sql, conn))
173 { 161 {
174 command.Parameters.Add(database.CreateParameter("folderID", folderID)); 162 cmd.Parameters.Add(database.CreateParameter("folderID", folderID));
175 163 conn.Open();
176 using (IDataReader reader = command.ExecuteReader()) 164 using (SqlDataReader reader = cmd.ExecuteReader())
177 { 165 {
178 if (reader.Read()) 166 if (reader.Read())
179 { 167 {
@@ -197,18 +185,19 @@ namespace OpenSim.Data.MSSQL
197 //Note this is changed so it opens only one connection to the database and not everytime it wants to get data. 185 //Note this is changed so it opens only one connection to the database and not everytime it wants to get data.
198 186
199 List<InventoryFolderBase> folders = new List<InventoryFolderBase>(); 187 List<InventoryFolderBase> folders = new List<InventoryFolderBase>();
200 188 string sql = "SELECT * FROM inventoryfolders WHERE parentFolderID = @parentID";
201 using (AutoClosingSqlCommand command = database.Query("SELECT * FROM inventoryfolders WHERE parentFolderID = @parentID")) 189 using (SqlConnection conn = new SqlConnection(m_connectionString))
190 using (SqlCommand cmd = new SqlCommand(sql, conn))
202 { 191 {
203 command.Parameters.Add(database.CreateParameter("@parentID", parentID)); 192 cmd.Parameters.Add(database.CreateParameter("@parentID", parentID));
204 193 conn.Open();
205 folders.AddRange(getInventoryFolders(command)); 194 folders.AddRange(getInventoryFolders(cmd));
206 195
207 List<InventoryFolderBase> tempFolders = new List<InventoryFolderBase>(); 196 List<InventoryFolderBase> tempFolders = new List<InventoryFolderBase>();
208 197
209 foreach (InventoryFolderBase folderBase in folders) 198 foreach (InventoryFolderBase folderBase in folders)
210 { 199 {
211 tempFolders.AddRange(getFolderHierarchy(folderBase.ID, command)); 200 tempFolders.AddRange(getFolderHierarchy(folderBase.ID, cmd));
212 } 201 }
213 if (tempFolders.Count > 0) 202 if (tempFolders.Count > 0)
214 { 203 {
@@ -233,20 +222,19 @@ namespace OpenSim.Data.MSSQL
233 folderName = folderName.Substring(0, 64); 222 folderName = folderName.Substring(0, 64);
234 m_log.Warn("[INVENTORY DB]: Name field truncated from " + folder.Name.Length.ToString() + " to " + folderName.Length + " characters on add"); 223 m_log.Warn("[INVENTORY DB]: Name field truncated from " + folder.Name.Length.ToString() + " to " + folderName.Length + " characters on add");
235 } 224 }
236 225 using (SqlConnection conn = new SqlConnection(m_connectionString))
237 using (AutoClosingSqlCommand command = database.Query(sql)) 226 using (SqlCommand cmd = new SqlCommand(sql, conn))
238 { 227 {
239 command.Parameters.Add(database.CreateParameter("folderID", folder.ID)); 228 cmd.Parameters.Add(database.CreateParameter("folderID", folder.ID));
240 command.Parameters.Add(database.CreateParameter("agentID", folder.Owner)); 229 cmd.Parameters.Add(database.CreateParameter("agentID", folder.Owner));
241 command.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID)); 230 cmd.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID));
242 command.Parameters.Add(database.CreateParameter("folderName", folderName)); 231 cmd.Parameters.Add(database.CreateParameter("folderName", folderName));
243 command.Parameters.Add(database.CreateParameter("type", folder.Type)); 232 cmd.Parameters.Add(database.CreateParameter("type", folder.Type));
244 command.Parameters.Add(database.CreateParameter("version", folder.Version)); 233 cmd.Parameters.Add(database.CreateParameter("version", folder.Version));
245 234 conn.Open();
246 try 235 try
247 { 236 {
248 //IDbCommand result = database.Query(sql, param); 237 cmd.ExecuteNonQuery();
249 command.ExecuteNonQuery();
250 } 238 }
251 catch (Exception e) 239 catch (Exception e)
252 { 240 {
@@ -275,20 +263,20 @@ namespace OpenSim.Data.MSSQL
275 folderName = folderName.Substring(0, 64); 263 folderName = folderName.Substring(0, 64);
276 m_log.Warn("[INVENTORY DB]: Name field truncated from " + folder.Name.Length.ToString() + " to " + folderName.Length + " characters on update"); 264 m_log.Warn("[INVENTORY DB]: Name field truncated from " + folder.Name.Length.ToString() + " to " + folderName.Length + " characters on update");
277 } 265 }
278 266 using (SqlConnection conn = new SqlConnection(m_connectionString))
279 using (AutoClosingSqlCommand command = database.Query(sql)) 267 using (SqlCommand cmd = new SqlCommand(sql, conn))
280 { 268 {
281 command.Parameters.Add(database.CreateParameter("folderID", folder.ID)); 269 cmd.Parameters.Add(database.CreateParameter("folderID", folder.ID));
282 command.Parameters.Add(database.CreateParameter("agentID", folder.Owner)); 270 cmd.Parameters.Add(database.CreateParameter("agentID", folder.Owner));
283 command.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID)); 271 cmd.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID));
284 command.Parameters.Add(database.CreateParameter("folderName", folderName)); 272 cmd.Parameters.Add(database.CreateParameter("folderName", folderName));
285 command.Parameters.Add(database.CreateParameter("type", folder.Type)); 273 cmd.Parameters.Add(database.CreateParameter("type", folder.Type));
286 command.Parameters.Add(database.CreateParameter("version", folder.Version)); 274 cmd.Parameters.Add(database.CreateParameter("version", folder.Version));
287 command.Parameters.Add(database.CreateParameter("@keyFolderID", folder.ID)); 275 cmd.Parameters.Add(database.CreateParameter("@keyFolderID", folder.ID));
288 276 conn.Open();
289 try 277 try
290 { 278 {
291 command.ExecuteNonQuery(); 279 cmd.ExecuteNonQuery();
292 } 280 }
293 catch (Exception e) 281 catch (Exception e)
294 { 282 {
@@ -304,14 +292,15 @@ namespace OpenSim.Data.MSSQL
304 public void moveInventoryFolder(InventoryFolderBase folder) 292 public void moveInventoryFolder(InventoryFolderBase folder)
305 { 293 {
306 string sql = @"UPDATE inventoryfolders SET parentFolderID = @parentFolderID WHERE folderID = @folderID"; 294 string sql = @"UPDATE inventoryfolders SET parentFolderID = @parentFolderID WHERE folderID = @folderID";
307 using (IDbCommand command = database.Query(sql)) 295 using (SqlConnection conn = new SqlConnection(m_connectionString))
296 using (SqlCommand cmd = new SqlCommand(sql, conn))
308 { 297 {
309 command.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID)); 298 cmd.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID));
310 command.Parameters.Add(database.CreateParameter("@folderID", folder.ID)); 299 cmd.Parameters.Add(database.CreateParameter("@folderID", folder.ID));
311 300 conn.Open();
312 try 301 try
313 { 302 {
314 command.ExecuteNonQuery(); 303 cmd.ExecuteNonQuery();
315 } 304 }
316 catch (Exception e) 305 catch (Exception e)
317 { 306 {
@@ -326,30 +315,27 @@ namespace OpenSim.Data.MSSQL
326 /// <param name="folderID">Id of folder to delete</param> 315 /// <param name="folderID">Id of folder to delete</param>
327 public void deleteInventoryFolder(UUID folderID) 316 public void deleteInventoryFolder(UUID folderID)
328 { 317 {
329 using (SqlConnection connection = database.DatabaseConnection()) 318 string sql = "SELECT * FROM inventoryfolders WHERE parentFolderID = @parentID";
319
320 using (SqlConnection conn = new SqlConnection(m_connectionString))
321 using (SqlCommand cmd = new SqlCommand(sql, conn))
330 { 322 {
331 List<InventoryFolderBase> subFolders; 323 List<InventoryFolderBase> subFolders;
332 using (SqlCommand command = new SqlCommand("SELECT * FROM inventoryfolders WHERE parentFolderID = @parentID", connection)) 324 cmd.Parameters.Add(database.CreateParameter("@parentID", UUID.Zero));
333 { 325 conn.Open();
334 command.Parameters.Add(database.CreateParameter("@parentID", UUID.Zero)); 326 subFolders = getFolderHierarchy(folderID, cmd);
335 327
336 AutoClosingSqlCommand autoCommand = new AutoClosingSqlCommand(command);
337
338 subFolders = getFolderHierarchy(folderID, autoCommand);
339 }
340 328
341 //Delete all sub-folders 329 //Delete all sub-folders
342 foreach (InventoryFolderBase f in subFolders) 330 foreach (InventoryFolderBase f in subFolders)
343 { 331 {
344 DeleteOneFolder(f.ID, connection); 332 DeleteOneFolder(f.ID, conn);
345 DeleteItemsInFolder(f.ID, connection); 333 DeleteItemsInFolder(f.ID, conn);
346 } 334 }
347 335
348 //Delete the actual row 336 //Delete the actual row
349 DeleteOneFolder(folderID, connection); 337 DeleteOneFolder(folderID, conn);
350 DeleteItemsInFolder(folderID, connection); 338 DeleteItemsInFolder(folderID, conn);
351
352 connection.Close();
353 } 339 }
354 } 340 }
355 341
@@ -364,13 +350,15 @@ namespace OpenSim.Data.MSSQL
364 /// <returns>A list containing inventory items</returns> 350 /// <returns>A list containing inventory items</returns>
365 public List<InventoryItemBase> getInventoryInFolder(UUID folderID) 351 public List<InventoryItemBase> getInventoryInFolder(UUID folderID)
366 { 352 {
367 using (AutoClosingSqlCommand command = database.Query("SELECT * FROM inventoryitems WHERE parentFolderID = @parentFolderID")) 353 string sql = "SELECT * FROM inventoryitems WHERE parentFolderID = @parentFolderID";
354 using (SqlConnection conn = new SqlConnection(m_connectionString))
355 using (SqlCommand cmd = new SqlCommand(sql, conn))
368 { 356 {
369 command.Parameters.Add(database.CreateParameter("parentFolderID", folderID)); 357 cmd.Parameters.Add(database.CreateParameter("parentFolderID", folderID));
370 358 conn.Open();
371 List<InventoryItemBase> items = new List<InventoryItemBase>(); 359 List<InventoryItemBase> items = new List<InventoryItemBase>();
372 360
373 using (SqlDataReader reader = command.ExecuteReader()) 361 using (SqlDataReader reader = cmd.ExecuteReader())
374 { 362 {
375 while (reader.Read()) 363 while (reader.Read())
376 { 364 {
@@ -388,11 +376,13 @@ namespace OpenSim.Data.MSSQL
388 /// <returns>An inventory item</returns> 376 /// <returns>An inventory item</returns>
389 public InventoryItemBase getInventoryItem(UUID itemID) 377 public InventoryItemBase getInventoryItem(UUID itemID)
390 { 378 {
391 using (AutoClosingSqlCommand result = database.Query("SELECT * FROM inventoryitems WHERE inventoryID = @inventoryID")) 379 string sql = "SELECT * FROM inventoryitems WHERE inventoryID = @inventoryID";
380 using (SqlConnection conn = new SqlConnection(m_connectionString))
381 using (SqlCommand cmd = new SqlCommand(sql, conn))
392 { 382 {
393 result.Parameters.Add(database.CreateParameter("inventoryID", itemID)); 383 cmd.Parameters.Add(database.CreateParameter("inventoryID", itemID));
394 384 conn.Open();
395 using (IDataReader reader = result.ExecuteReader()) 385 using (SqlDataReader reader = cmd.ExecuteReader())
396 { 386 {
397 if (reader.Read()) 387 if (reader.Read())
398 { 388 {
@@ -441,8 +431,9 @@ namespace OpenSim.Data.MSSQL
441 itemDesc = item.Description.Substring(0, 128); 431 itemDesc = item.Description.Substring(0, 128);
442 m_log.Warn("[INVENTORY DB]: Description field truncated from " + item.Description.Length.ToString() + " to " + itemDesc.Length.ToString() + " characters"); 432 m_log.Warn("[INVENTORY DB]: Description field truncated from " + item.Description.Length.ToString() + " to " + itemDesc.Length.ToString() + " characters");
443 } 433 }
444 434
445 using (AutoClosingSqlCommand command = database.Query(sql)) 435 using (SqlConnection conn = new SqlConnection(m_connectionString))
436 using (SqlCommand command = new SqlCommand(sql, conn))
446 { 437 {
447 command.Parameters.Add(database.CreateParameter("inventoryID", item.ID)); 438 command.Parameters.Add(database.CreateParameter("inventoryID", item.ID));
448 command.Parameters.Add(database.CreateParameter("assetID", item.AssetID)); 439 command.Parameters.Add(database.CreateParameter("assetID", item.AssetID));
@@ -464,7 +455,7 @@ namespace OpenSim.Data.MSSQL
464 command.Parameters.Add(database.CreateParameter("groupID", item.GroupID)); 455 command.Parameters.Add(database.CreateParameter("groupID", item.GroupID));
465 command.Parameters.Add(database.CreateParameter("groupOwned", item.GroupOwned)); 456 command.Parameters.Add(database.CreateParameter("groupOwned", item.GroupOwned));
466 command.Parameters.Add(database.CreateParameter("flags", item.Flags)); 457 command.Parameters.Add(database.CreateParameter("flags", item.Flags));
467 458 conn.Open();
468 try 459 try
469 { 460 {
470 command.ExecuteNonQuery(); 461 command.ExecuteNonQuery();
@@ -476,9 +467,11 @@ namespace OpenSim.Data.MSSQL
476 } 467 }
477 468
478 sql = "UPDATE inventoryfolders SET version = version + 1 WHERE folderID = @folderID"; 469 sql = "UPDATE inventoryfolders SET version = version + 1 WHERE folderID = @folderID";
479 using (AutoClosingSqlCommand command = database.Query(sql)) 470 using (SqlConnection conn = new SqlConnection(m_connectionString))
471 using (SqlCommand command = new SqlCommand(sql, conn))
480 { 472 {
481 command.Parameters.Add(database.CreateParameter("folderID", item.Folder.ToString())); 473 command.Parameters.Add(database.CreateParameter("folderID", item.Folder.ToString()));
474 conn.Open();
482 try 475 try
483 { 476 {
484 command.ExecuteNonQuery(); 477 command.ExecuteNonQuery();
@@ -530,8 +523,9 @@ namespace OpenSim.Data.MSSQL
530 itemDesc = item.Description.Substring(0, 128); 523 itemDesc = item.Description.Substring(0, 128);
531 m_log.Warn("[INVENTORY DB]: Description field truncated from " + item.Description.Length.ToString() + " to " + itemDesc.Length.ToString() + " characters on update"); 524 m_log.Warn("[INVENTORY DB]: Description field truncated from " + item.Description.Length.ToString() + " to " + itemDesc.Length.ToString() + " characters on update");
532 } 525 }
533 526
534 using (AutoClosingSqlCommand command = database.Query(sql)) 527 using (SqlConnection conn = new SqlConnection(m_connectionString))
528 using (SqlCommand command = new SqlCommand(sql, conn))
535 { 529 {
536 command.Parameters.Add(database.CreateParameter("inventoryID", item.ID)); 530 command.Parameters.Add(database.CreateParameter("inventoryID", item.ID));
537 command.Parameters.Add(database.CreateParameter("assetID", item.AssetID)); 531 command.Parameters.Add(database.CreateParameter("assetID", item.AssetID));
@@ -552,8 +546,8 @@ namespace OpenSim.Data.MSSQL
552 command.Parameters.Add(database.CreateParameter("groupID", item.GroupID)); 546 command.Parameters.Add(database.CreateParameter("groupID", item.GroupID));
553 command.Parameters.Add(database.CreateParameter("groupOwned", item.GroupOwned)); 547 command.Parameters.Add(database.CreateParameter("groupOwned", item.GroupOwned));
554 command.Parameters.Add(database.CreateParameter("flags", item.Flags)); 548 command.Parameters.Add(database.CreateParameter("flags", item.Flags));
555 command.Parameters.Add(database.CreateParameter("@keyInventoryID", item.ID)); 549 command.Parameters.Add(database.CreateParameter("keyInventoryID", item.ID));
556 550 conn.Open();
557 try 551 try
558 { 552 {
559 command.ExecuteNonQuery(); 553 command.ExecuteNonQuery();
@@ -573,13 +567,15 @@ namespace OpenSim.Data.MSSQL
573 /// <param name="itemID">the item UUID</param> 567 /// <param name="itemID">the item UUID</param>
574 public void deleteInventoryItem(UUID itemID) 568 public void deleteInventoryItem(UUID itemID)
575 { 569 {
576 using (AutoClosingSqlCommand command = database.Query("DELETE FROM inventoryitems WHERE inventoryID=@inventoryID")) 570 string sql = "DELETE FROM inventoryitems WHERE inventoryID=@inventoryID";
571 using (SqlConnection conn = new SqlConnection(m_connectionString))
572 using (SqlCommand cmd = new SqlCommand(sql, conn))
577 { 573 {
578 command.Parameters.Add(database.CreateParameter("inventoryID", itemID)); 574 cmd.Parameters.Add(database.CreateParameter("inventoryID", itemID));
579 try 575 try
580 { 576 {
581 577 conn.Open();
582 command.ExecuteNonQuery(); 578 cmd.ExecuteNonQuery();
583 } 579 }
584 catch (Exception e) 580 catch (Exception e)
585 { 581 {
@@ -607,12 +603,14 @@ namespace OpenSim.Data.MSSQL
607 /// </returns> 603 /// </returns>
608 public List<InventoryItemBase> fetchActiveGestures(UUID avatarID) 604 public List<InventoryItemBase> fetchActiveGestures(UUID avatarID)
609 { 605 {
610 using (AutoClosingSqlCommand command = database.Query("SELECT * FROM inventoryitems WHERE avatarId = @uuid AND assetType = @assetType and flags = 1")) 606 string sql = "SELECT * FROM inventoryitems WHERE avatarId = @uuid AND assetType = @assetType and flags = 1";
611 { 607 using (SqlConnection conn = new SqlConnection(m_connectionString))
612 command.Parameters.Add(database.CreateParameter("uuid", avatarID)); 608 using (SqlCommand cmd = new SqlCommand(sql, conn))
613 command.Parameters.Add(database.CreateParameter("assetType", (int)AssetType.Gesture)); 609 {
614 610 cmd.Parameters.Add(database.CreateParameter("uuid", avatarID));
615 using (IDataReader reader = command.ExecuteReader()) 611 cmd.Parameters.Add(database.CreateParameter("assetType", (int)AssetType.Gesture));
612 conn.Open();
613 using (SqlDataReader reader = cmd.ExecuteReader())
616 { 614 {
617 List<InventoryItemBase> gestureList = new List<InventoryItemBase>(); 615 List<InventoryItemBase> gestureList = new List<InventoryItemBase>();
618 while (reader.Read()) 616 while (reader.Read())
@@ -656,7 +654,7 @@ namespace OpenSim.Data.MSSQL
656 /// <param name="parentID">parent ID.</param> 654 /// <param name="parentID">parent ID.</param>
657 /// <param name="command">SQL command/connection to database</param> 655 /// <param name="command">SQL command/connection to database</param>
658 /// <returns></returns> 656 /// <returns></returns>
659 private static List<InventoryFolderBase> getFolderHierarchy(UUID parentID, AutoClosingSqlCommand command) 657 private static List<InventoryFolderBase> getFolderHierarchy(UUID parentID, SqlCommand command)
660 { 658 {
661 command.Parameters["@parentID"].Value = parentID.Guid; //.ToString(); 659 command.Parameters["@parentID"].Value = parentID.Guid; //.ToString();
662 660
@@ -687,7 +685,9 @@ namespace OpenSim.Data.MSSQL
687 /// <returns></returns> 685 /// <returns></returns>
688 private List<InventoryFolderBase> getInventoryFolders(UUID parentID, UUID user) 686 private List<InventoryFolderBase> getInventoryFolders(UUID parentID, UUID user)
689 { 687 {
690 using (AutoClosingSqlCommand command = database.Query("SELECT * FROM inventoryfolders WHERE parentFolderID = @parentID AND agentID LIKE @uuid")) 688 string sql = "SELECT * FROM inventoryfolders WHERE parentFolderID = @parentID AND agentID LIKE @uuid";
689 using (SqlConnection conn = new SqlConnection(m_connectionString))
690 using (SqlCommand command = new SqlCommand(sql, conn))
691 { 691 {
692 if (user == UUID.Zero) 692 if (user == UUID.Zero)
693 { 693 {
@@ -698,7 +698,7 @@ namespace OpenSim.Data.MSSQL
698 command.Parameters.Add(database.CreateParameter("uuid", user)); 698 command.Parameters.Add(database.CreateParameter("uuid", user));
699 } 699 }
700 command.Parameters.Add(database.CreateParameter("parentID", parentID)); 700 command.Parameters.Add(database.CreateParameter("parentID", parentID));
701 701 conn.Open();
702 return getInventoryFolders(command); 702 return getInventoryFolders(command);
703 } 703 }
704 } 704 }
@@ -708,9 +708,9 @@ namespace OpenSim.Data.MSSQL
708 /// </summary> 708 /// </summary>
709 /// <param name="command">SQLcommand.</param> 709 /// <param name="command">SQLcommand.</param>
710 /// <returns></returns> 710 /// <returns></returns>
711 private static List<InventoryFolderBase> getInventoryFolders(AutoClosingSqlCommand command) 711 private static List<InventoryFolderBase> getInventoryFolders(SqlCommand command)
712 { 712 {
713 using (IDataReader reader = command.ExecuteReader()) 713 using (SqlDataReader reader = command.ExecuteReader())
714 { 714 {
715 715
716 List<InventoryFolderBase> items = new List<InventoryFolderBase>(); 716 List<InventoryFolderBase> items = new List<InventoryFolderBase>();
@@ -727,7 +727,7 @@ namespace OpenSim.Data.MSSQL
727 /// </summary> 727 /// </summary>
728 /// <param name="reader">A MSSQL Data Reader</param> 728 /// <param name="reader">A MSSQL Data Reader</param>
729 /// <returns>A List containing inventory folders</returns> 729 /// <returns>A List containing inventory folders</returns>
730 protected static InventoryFolderBase readInventoryFolder(IDataReader reader) 730 protected static InventoryFolderBase readInventoryFolder(SqlDataReader reader)
731 { 731 {
732 try 732 try
733 { 733 {
diff --git a/OpenSim/Data/MSSQL/MSSQLLegacyRegionData.cs b/OpenSim/Data/MSSQL/MSSQLLegacyRegionData.cs
index 6371307..c849f38 100644
--- a/OpenSim/Data/MSSQL/MSSQLLegacyRegionData.cs
+++ b/OpenSim/Data/MSSQL/MSSQLLegacyRegionData.cs
@@ -54,28 +54,16 @@ namespace OpenSim.Data.MSSQL
54 /// The database manager 54 /// The database manager
55 /// </summary> 55 /// </summary>
56 private MSSQLManager _Database; 56 private MSSQLManager _Database;
57 57 private string m_connectionString;
58 /// <summary> 58 /// <summary>
59 /// Initialises the region datastore 59 /// Initialises the region datastore
60 /// </summary> 60 /// </summary>
61 /// <param name="connectionString">The connection string.</param> 61 /// <param name="connectionString">The connection string.</param>
62 public void Initialise(string connectionString) 62 public void Initialise(string connectionString)
63 { 63 {
64 if (!string.IsNullOrEmpty(connectionString)) 64 m_connectionString = connectionString;
65 { 65 _Database = new MSSQLManager(connectionString);
66 _Database = new MSSQLManager(connectionString); 66
67 }
68 else
69 {
70 IniFile iniFile = new IniFile("mssql_connection.ini");
71 string settingDataSource = iniFile.ParseFileReadValue("data_source");
72 string settingInitialCatalog = iniFile.ParseFileReadValue("initial_catalog");
73 string settingPersistSecurityInfo = iniFile.ParseFileReadValue("persist_security_info");
74 string settingUserId = iniFile.ParseFileReadValue("user_id");
75 string settingPassword = iniFile.ParseFileReadValue("password");
76
77 _Database = new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, settingPassword);
78 }
79 67
80 //Migration settings 68 //Migration settings
81 _Database.CheckMigration(_migrationStore); 69 _Database.CheckMigration(_migrationStore);
@@ -102,17 +90,18 @@ namespace OpenSim.Data.MSSQL
102 SceneObjectGroup grp = null; 90 SceneObjectGroup grp = null;
103 91
104 92
105 string query = "SELECT *, " + 93 string sql = "SELECT *, " +
106 "sort = CASE WHEN prims.UUID = prims.SceneGroupID THEN 0 ELSE 1 END " + 94 "sort = CASE WHEN prims.UUID = prims.SceneGroupID THEN 0 ELSE 1 END " +
107 "FROM prims " + 95 "FROM prims " +
108 "LEFT JOIN primshapes ON prims.UUID = primshapes.UUID " + 96 "LEFT JOIN primshapes ON prims.UUID = primshapes.UUID " +
109 "WHERE RegionUUID = @RegionUUID " + 97 "WHERE RegionUUID = @RegionUUID " +
110 "ORDER BY SceneGroupID asc, sort asc, LinkNumber asc"; 98 "ORDER BY SceneGroupID asc, sort asc, LinkNumber asc";
111 99
112 using (AutoClosingSqlCommand command = _Database.Query(query)) 100 using (SqlConnection conn = new SqlConnection(m_connectionString))
101 using (SqlCommand command = new SqlCommand(sql, conn))
113 { 102 {
114 command.Parameters.Add(_Database.CreateParameter("@regionUUID", regionUUID)); 103 command.Parameters.Add(_Database.CreateParameter("@regionUUID", regionUUID));
115 104 conn.Open();
116 using (SqlDataReader reader = command.ExecuteReader()) 105 using (SqlDataReader reader = command.ExecuteReader())
117 { 106 {
118 while (reader.Read()) 107 while (reader.Read())
@@ -122,7 +111,7 @@ namespace OpenSim.Data.MSSQL
122 sceneObjectPart.Shape = PrimitiveBaseShape.Default; 111 sceneObjectPart.Shape = PrimitiveBaseShape.Default;
123 else 112 else
124 sceneObjectPart.Shape = BuildShape(reader); 113 sceneObjectPart.Shape = BuildShape(reader);
125 114
126 prims[sceneObjectPart.UUID] = sceneObjectPart; 115 prims[sceneObjectPart.UUID] = sceneObjectPart;
127 116
128 UUID groupID = new UUID((Guid)reader["SceneGroupID"]); 117 UUID groupID = new UUID((Guid)reader["SceneGroupID"]);
@@ -133,7 +122,7 @@ namespace OpenSim.Data.MSSQL
133 objects[grp.UUID] = grp; 122 objects[grp.UUID] = grp;
134 123
135 lastGroupID = groupID; 124 lastGroupID = groupID;
136 125
137 // There sometimes exist OpenSim bugs that 'orphan groups' so that none of the prims are 126 // There sometimes exist OpenSim bugs that 'orphan groups' so that none of the prims are
138 // recorded as the root prim (for which the UUID must equal the persisted group UUID). In 127 // recorded as the root prim (for which the UUID must equal the persisted group UUID). In
139 // this case, force the UUID to be the same as the group UUID so that at least these can be 128 // this case, force the UUID to be the same as the group UUID so that at least these can be
@@ -142,7 +131,7 @@ namespace OpenSim.Data.MSSQL
142 if (sceneObjectPart.UUID != groupID && groupID != UUID.Zero) 131 if (sceneObjectPart.UUID != groupID && groupID != UUID.Zero)
143 { 132 {
144 _Log.WarnFormat( 133 _Log.WarnFormat(
145 "[REGION DB]: Found root prim {0} {1} at {2} where group was actually {3}. Forcing UUID to group UUID", 134 "[REGION DB]: Found root prim {0} {1} at {2} where group was actually {3}. Forcing UUID to group UUID",
146 sceneObjectPart.Name, sceneObjectPart.UUID, sceneObjectPart.GroupPosition, groupID); 135 sceneObjectPart.Name, sceneObjectPart.UUID, sceneObjectPart.GroupPosition, groupID);
147 136
148 sceneObjectPart.UUID = groupID; 137 sceneObjectPart.UUID = groupID;
@@ -174,8 +163,10 @@ namespace OpenSim.Data.MSSQL
174 // LoadItems only on those 163 // LoadItems only on those
175 List<SceneObjectPart> primsWithInventory = new List<SceneObjectPart>(); 164 List<SceneObjectPart> primsWithInventory = new List<SceneObjectPart>();
176 string qry = "select distinct primID from primitems"; 165 string qry = "select distinct primID from primitems";
177 using (AutoClosingSqlCommand command = _Database.Query(qry)) 166 using (SqlConnection conn = new SqlConnection(m_connectionString))
167 using (SqlCommand command = new SqlCommand(qry, conn))
178 { 168 {
169 conn.Open();
179 using (SqlDataReader itemReader = command.ExecuteReader()) 170 using (SqlDataReader itemReader = command.ExecuteReader())
180 { 171 {
181 while (itemReader.Read()) 172 while (itemReader.Read())
@@ -205,14 +196,16 @@ namespace OpenSim.Data.MSSQL
205 /// <param name="allPrims">all prims with inventory on a region</param> 196 /// <param name="allPrims">all prims with inventory on a region</param>
206 private void LoadItems(List<SceneObjectPart> allPrimsWithInventory) 197 private void LoadItems(List<SceneObjectPart> allPrimsWithInventory)
207 { 198 {
208 199 string sql = "SELECT * FROM primitems WHERE PrimID = @PrimID";
209 using (AutoClosingSqlCommand command = _Database.Query("SELECT * FROM primitems WHERE PrimID = @PrimID")) 200 using (SqlConnection conn = new SqlConnection(m_connectionString))
201 using (SqlCommand command = new SqlCommand(sql, conn))
210 { 202 {
203 conn.Open();
211 foreach (SceneObjectPart objectPart in allPrimsWithInventory) 204 foreach (SceneObjectPart objectPart in allPrimsWithInventory)
212 { 205 {
213 command.Parameters.Clear(); 206 command.Parameters.Clear();
214 command.Parameters.Add(_Database.CreateParameter("@PrimID", objectPart.UUID)); 207 command.Parameters.Add(_Database.CreateParameter("@PrimID", objectPart.UUID));
215 208
216 List<TaskInventoryItem> inventory = new List<TaskInventoryItem>(); 209 List<TaskInventoryItem> inventory = new List<TaskInventoryItem>();
217 210
218 using (SqlDataReader reader = command.ExecuteReader()) 211 using (SqlDataReader reader = command.ExecuteReader())
@@ -241,8 +234,9 @@ namespace OpenSim.Data.MSSQL
241 { 234 {
242 _Log.InfoFormat("[MSSQL]: Adding/Changing SceneObjectGroup: {0} to region: {1}, object has {2} prims.", obj.UUID, regionUUID, obj.Children.Count); 235 _Log.InfoFormat("[MSSQL]: Adding/Changing SceneObjectGroup: {0} to region: {1}, object has {2} prims.", obj.UUID, regionUUID, obj.Children.Count);
243 236
244 using (SqlConnection conn = _Database.DatabaseConnection()) 237 using (SqlConnection conn = new SqlConnection(m_connectionString))
245 { 238 {
239 conn.Open();
246 SqlTransaction transaction = conn.BeginTransaction(); 240 SqlTransaction transaction = conn.BeginTransaction();
247 241
248 try 242 try
@@ -437,8 +431,12 @@ ELSE
437 lock (_Database) 431 lock (_Database)
438 { 432 {
439 //Using the non transaction mode. 433 //Using the non transaction mode.
440 using (AutoClosingSqlCommand cmd = _Database.Query(sqlPrimShapes)) 434 using (SqlConnection conn = new SqlConnection(m_connectionString))
435 using (SqlCommand cmd = new SqlCommand())
441 { 436 {
437 cmd.Connection = conn;
438 cmd.CommandText = sqlPrimShapes;
439 conn.Open();
442 cmd.Parameters.Add(_Database.CreateParameter("objectID", objectID)); 440 cmd.Parameters.Add(_Database.CreateParameter("objectID", objectID));
443 cmd.ExecuteNonQuery(); 441 cmd.ExecuteNonQuery();
444 442
@@ -466,24 +464,30 @@ ELSE
466 464
467 //Delete everything from PrimID 465 //Delete everything from PrimID
468 //TODO add index on PrimID in DB, if not already exist 466 //TODO add index on PrimID in DB, if not already exist
469 using (AutoClosingSqlCommand cmd = _Database.Query("DELETE PRIMITEMS WHERE primID = @primID")) 467
468 string sql = "DELETE PRIMITEMS WHERE primID = @primID";
469 using (SqlConnection conn = new SqlConnection(m_connectionString))
470 using (SqlCommand cmd = new SqlCommand(sql, conn))
470 { 471 {
471 cmd.Parameters.Add(_Database.CreateParameter("@primID", primID)); 472 cmd.Parameters.Add(_Database.CreateParameter("@primID", primID));
473 conn.Open();
472 cmd.ExecuteNonQuery(); 474 cmd.ExecuteNonQuery();
473 } 475 }
474 476
475 string sql = 477 sql =
476 @"INSERT INTO primitems ( 478 @"INSERT INTO primitems (
477 itemID,primID,assetID,parentFolderID,invType,assetType,name,description,creationDate,creatorID,ownerID,lastOwnerID,groupID, 479 itemID,primID,assetID,parentFolderID,invType,assetType,name,description,creationDate,creatorID,ownerID,lastOwnerID,groupID,
478 nextPermissions,currentPermissions,basePermissions,everyonePermissions,groupPermissions,flags) 480 nextPermissions,currentPermissions,basePermissions,everyonePermissions,groupPermissions,flags)
479 VALUES (@itemID,@primID,@assetID,@parentFolderID,@invType,@assetType,@name,@description,@creationDate,@creatorID,@ownerID, 481 VALUES (@itemID,@primID,@assetID,@parentFolderID,@invType,@assetType,@name,@description,@creationDate,@creatorID,@ownerID,
480 @lastOwnerID,@groupID,@nextPermissions,@currentPermissions,@basePermissions,@everyonePermissions,@groupPermissions,@flags)"; 482 @lastOwnerID,@groupID,@nextPermissions,@currentPermissions,@basePermissions,@everyonePermissions,@groupPermissions,@flags)";
481 483
482 using (AutoClosingSqlCommand cmd = _Database.Query(sql)) 484 using (SqlConnection conn = new SqlConnection(m_connectionString))
485 using (SqlCommand cmd = new SqlCommand(sql, conn))
483 { 486 {
484 foreach (TaskInventoryItem taskItem in items) 487 foreach (TaskInventoryItem taskItem in items)
485 { 488 {
486 cmd.Parameters.AddRange(CreatePrimInventoryParameters(taskItem)); 489 cmd.Parameters.AddRange(CreatePrimInventoryParameters(taskItem));
490 conn.Open();
487 cmd.ExecuteNonQuery(); 491 cmd.ExecuteNonQuery();
488 492
489 cmd.Parameters.Clear(); 493 cmd.Parameters.Clear();
@@ -505,11 +509,12 @@ ELSE
505 509
506 string sql = "select top 1 RegionUUID, Revision, Heightfield from terrain where RegionUUID = @RegionUUID order by Revision desc"; 510 string sql = "select top 1 RegionUUID, Revision, Heightfield from terrain where RegionUUID = @RegionUUID order by Revision desc";
507 511
508 using (AutoClosingSqlCommand cmd = _Database.Query(sql)) 512 using (SqlConnection conn = new SqlConnection(m_connectionString))
513 using (SqlCommand cmd = new SqlCommand(sql, conn))
509 { 514 {
510 // MySqlParameter param = new MySqlParameter(); 515 // MySqlParameter param = new MySqlParameter();
511 cmd.Parameters.Add(_Database.CreateParameter("@RegionUUID", regionID)); 516 cmd.Parameters.Add(_Database.CreateParameter("@RegionUUID", regionID));
512 517 conn.Open();
513 using (SqlDataReader reader = cmd.ExecuteReader()) 518 using (SqlDataReader reader = cmd.ExecuteReader())
514 { 519 {
515 int rev; 520 int rev;
@@ -549,19 +554,23 @@ ELSE
549 554
550 //Delete old terrain map 555 //Delete old terrain map
551 string sql = "delete from terrain where RegionUUID=@RegionUUID"; 556 string sql = "delete from terrain where RegionUUID=@RegionUUID";
552 using (AutoClosingSqlCommand cmd = _Database.Query(sql)) 557 using (SqlConnection conn = new SqlConnection(m_connectionString))
558 using (SqlCommand cmd = new SqlCommand(sql, conn))
553 { 559 {
554 cmd.Parameters.Add(_Database.CreateParameter("@RegionUUID", regionID)); 560 cmd.Parameters.Add(_Database.CreateParameter("@RegionUUID", regionID));
561 conn.Open();
555 cmd.ExecuteNonQuery(); 562 cmd.ExecuteNonQuery();
556 } 563 }
557 564
558 sql = "insert into terrain(RegionUUID, Revision, Heightfield) values(@RegionUUID, @Revision, @Heightfield)"; 565 sql = "insert into terrain(RegionUUID, Revision, Heightfield) values(@RegionUUID, @Revision, @Heightfield)";
559 566
560 using (AutoClosingSqlCommand cmd = _Database.Query(sql)) 567 using (SqlConnection conn = new SqlConnection(m_connectionString))
568 using (SqlCommand cmd = new SqlCommand(sql, conn))
561 { 569 {
562 cmd.Parameters.Add(_Database.CreateParameter("@RegionUUID", regionID)); 570 cmd.Parameters.Add(_Database.CreateParameter("@RegionUUID", regionID));
563 cmd.Parameters.Add(_Database.CreateParameter("@Revision", revision)); 571 cmd.Parameters.Add(_Database.CreateParameter("@Revision", revision));
564 cmd.Parameters.Add(_Database.CreateParameter("@Heightfield", serializeTerrain(terrain))); 572 cmd.Parameters.Add(_Database.CreateParameter("@Heightfield", serializeTerrain(terrain)));
573 conn.Open();
565 cmd.ExecuteNonQuery(); 574 cmd.ExecuteNonQuery();
566 } 575 }
567 576
@@ -580,11 +589,12 @@ ELSE
580 string sql = "select * from land where RegionUUID = @RegionUUID"; 589 string sql = "select * from land where RegionUUID = @RegionUUID";
581 590
582 //Retrieve all land data from region 591 //Retrieve all land data from region
583 using (AutoClosingSqlCommand cmdLandData = _Database.Query(sql)) 592 using (SqlConnection conn = new SqlConnection(m_connectionString))
593 using (SqlCommand cmd = new SqlCommand(sql, conn))
584 { 594 {
585 cmdLandData.Parameters.Add(_Database.CreateParameter("@RegionUUID", regionUUID)); 595 cmd.Parameters.Add(_Database.CreateParameter("@RegionUUID", regionUUID));
586 596 conn.Open();
587 using (SqlDataReader readerLandData = cmdLandData.ExecuteReader()) 597 using (SqlDataReader readerLandData = cmd.ExecuteReader())
588 { 598 {
589 while (readerLandData.Read()) 599 while (readerLandData.Read())
590 { 600 {
@@ -597,10 +607,12 @@ ELSE
597 foreach (LandData LandData in LandDataForRegion) 607 foreach (LandData LandData in LandDataForRegion)
598 { 608 {
599 sql = "select * from landaccesslist where LandUUID = @LandUUID"; 609 sql = "select * from landaccesslist where LandUUID = @LandUUID";
600 using (AutoClosingSqlCommand cmdAccessList = _Database.Query(sql)) 610 using (SqlConnection conn = new SqlConnection(m_connectionString))
611 using (SqlCommand cmd = new SqlCommand(sql, conn))
601 { 612 {
602 cmdAccessList.Parameters.Add(_Database.CreateParameter("@LandUUID", LandData.GlobalID)); 613 cmd.Parameters.Add(_Database.CreateParameter("@LandUUID", LandData.GlobalID));
603 using (SqlDataReader readerAccessList = cmdAccessList.ExecuteReader()) 614 conn.Open();
615 using (SqlDataReader readerAccessList = cmd.ExecuteReader())
604 { 616 {
605 while (readerAccessList.Read()) 617 while (readerAccessList.Read())
606 { 618 {
@@ -632,17 +644,20 @@ ELSE
632VALUES 644VALUES
633(@UUID,@RegionUUID,@LocalLandID,@Bitmap,@Name,@Description,@OwnerUUID,@IsGroupOwned,@Area,@AuctionID,@Category,@ClaimDate,@ClaimPrice,@GroupUUID,@SalePrice,@LandStatus,@LandFlags,@LandingType,@MediaAutoScale,@MediaTextureUUID,@MediaURL,@MusicURL,@PassHours,@PassPrice,@SnapshotUUID,@UserLocationX,@UserLocationY,@UserLocationZ,@UserLookAtX,@UserLookAtY,@UserLookAtZ,@AuthbuyerID,@OtherCleanTime,@Dwell)"; 645(@UUID,@RegionUUID,@LocalLandID,@Bitmap,@Name,@Description,@OwnerUUID,@IsGroupOwned,@Area,@AuctionID,@Category,@ClaimDate,@ClaimPrice,@GroupUUID,@SalePrice,@LandStatus,@LandFlags,@LandingType,@MediaAutoScale,@MediaTextureUUID,@MediaURL,@MusicURL,@PassHours,@PassPrice,@SnapshotUUID,@UserLocationX,@UserLocationY,@UserLocationZ,@UserLookAtX,@UserLookAtY,@UserLookAtZ,@AuthbuyerID,@OtherCleanTime,@Dwell)";
634 646
635 using (AutoClosingSqlCommand cmd = _Database.Query(sql)) 647 using (SqlConnection conn = new SqlConnection(m_connectionString))
648 using (SqlCommand cmd = new SqlCommand(sql, conn))
636 { 649 {
637 cmd.Parameters.AddRange(CreateLandParameters(parcel.LandData, parcel.RegionUUID)); 650 cmd.Parameters.AddRange(CreateLandParameters(parcel.LandData, parcel.RegionUUID));
638 651 conn.Open();
639 cmd.ExecuteNonQuery(); 652 cmd.ExecuteNonQuery();
640 } 653 }
641 654
642 sql = "INSERT INTO [landaccesslist] ([LandUUID],[AccessUUID],[Flags]) VALUES (@LandUUID,@AccessUUID,@Flags)"; 655 sql = "INSERT INTO [landaccesslist] ([LandUUID],[AccessUUID],[Flags]) VALUES (@LandUUID,@AccessUUID,@Flags)";
643 656
644 using (AutoClosingSqlCommand cmd = _Database.Query(sql)) 657 using (SqlConnection conn = new SqlConnection(m_connectionString))
658 using (SqlCommand cmd = new SqlCommand(sql, conn))
645 { 659 {
660 conn.Open();
646 foreach (ParcelManager.ParcelAccessEntry parcelAccessEntry in parcel.LandData.ParcelAccessList) 661 foreach (ParcelManager.ParcelAccessEntry parcelAccessEntry in parcel.LandData.ParcelAccessList)
647 { 662 {
648 cmd.Parameters.AddRange(CreateLandAccessParameters(parcelAccessEntry, parcel.RegionUUID)); 663 cmd.Parameters.AddRange(CreateLandAccessParameters(parcelAccessEntry, parcel.RegionUUID));
@@ -659,15 +674,20 @@ VALUES
659 /// <param name="globalID">UUID of landobject</param> 674 /// <param name="globalID">UUID of landobject</param>
660 public void RemoveLandObject(UUID globalID) 675 public void RemoveLandObject(UUID globalID)
661 { 676 {
662 using (AutoClosingSqlCommand cmd = _Database.Query("delete from land where UUID=@UUID")) 677 string sql = "delete from land where UUID=@UUID";
678 using (SqlConnection conn = new SqlConnection(m_connectionString))
679 using (SqlCommand cmd = new SqlCommand(sql, conn))
663 { 680 {
664 cmd.Parameters.Add(_Database.CreateParameter("@UUID", globalID)); 681 cmd.Parameters.Add(_Database.CreateParameter("@UUID", globalID));
682 conn.Open();
665 cmd.ExecuteNonQuery(); 683 cmd.ExecuteNonQuery();
666 } 684 }
667 685 sql = "delete from landaccesslist where LandUUID=@UUID";
668 using (AutoClosingSqlCommand cmd = _Database.Query("delete from landaccesslist where LandUUID=@UUID")) 686 using (SqlConnection conn = new SqlConnection(m_connectionString))
687 using (SqlCommand cmd = new SqlCommand(sql, conn))
669 { 688 {
670 cmd.Parameters.Add(_Database.CreateParameter("@UUID", globalID)); 689 cmd.Parameters.Add(_Database.CreateParameter("@UUID", globalID));
690 conn.Open();
671 cmd.ExecuteNonQuery(); 691 cmd.ExecuteNonQuery();
672 } 692 }
673 } 693 }
@@ -681,9 +701,11 @@ VALUES
681 { 701 {
682 string sql = "select * from regionsettings where regionUUID = @regionUUID"; 702 string sql = "select * from regionsettings where regionUUID = @regionUUID";
683 RegionSettings regionSettings; 703 RegionSettings regionSettings;
684 using (AutoClosingSqlCommand cmd = _Database.Query(sql)) 704 using (SqlConnection conn = new SqlConnection(m_connectionString))
705 using (SqlCommand cmd = new SqlCommand(sql, conn))
685 { 706 {
686 cmd.Parameters.Add(_Database.CreateParameter("@regionUUID", regionUUID)); 707 cmd.Parameters.Add(_Database.CreateParameter("@regionUUID", regionUUID));
708 conn.Open();
687 using (SqlDataReader reader = cmd.ExecuteReader()) 709 using (SqlDataReader reader = cmd.ExecuteReader())
688 { 710 {
689 if (reader.Read()) 711 if (reader.Read())
@@ -715,9 +737,12 @@ VALUES
715 { 737 {
716 //Little check if regionUUID already exist in DB 738 //Little check if regionUUID already exist in DB
717 string regionUUID; 739 string regionUUID;
718 using (AutoClosingSqlCommand cmd = _Database.Query("SELECT regionUUID FROM regionsettings WHERE regionUUID = @regionUUID")) 740 string sql = "SELECT regionUUID FROM regionsettings WHERE regionUUID = @regionUUID";
741 using (SqlConnection conn = new SqlConnection(m_connectionString))
742 using (SqlCommand cmd = new SqlCommand(sql, conn))
719 { 743 {
720 cmd.Parameters.Add(_Database.CreateParameter("@regionUUID", regionSettings.RegionUUID)); 744 cmd.Parameters.Add(_Database.CreateParameter("@regionUUID", regionSettings.RegionUUID));
745 conn.Open();
721 regionUUID = cmd.ExecuteScalar().ToString(); 746 regionUUID = cmd.ExecuteScalar().ToString();
722 } 747 }
723 748
@@ -728,8 +753,8 @@ VALUES
728 else 753 else
729 { 754 {
730 //This method only updates region settings!!! First call LoadRegionSettings to create new region settings in DB 755 //This method only updates region settings!!! First call LoadRegionSettings to create new region settings in DB
731 string sql = 756 sql =
732 @"UPDATE [regionsettings] SET [block_terraform] = @block_terraform ,[block_fly] = @block_fly ,[allow_damage] = @allow_damage 757 @"UPDATE [regionsettings] SET [block_terraform] = @block_terraform ,[block_fly] = @block_fly ,[allow_damage] = @allow_damage
733,[restrict_pushing] = @restrict_pushing ,[allow_land_resell] = @allow_land_resell ,[allow_land_join_divide] = @allow_land_join_divide 758,[restrict_pushing] = @restrict_pushing ,[allow_land_resell] = @allow_land_resell ,[allow_land_join_divide] = @allow_land_join_divide
734,[block_show_in_search] = @block_show_in_search ,[agent_limit] = @agent_limit ,[object_bonus] = @object_bonus ,[maturity] = @maturity 759,[block_show_in_search] = @block_show_in_search ,[agent_limit] = @agent_limit ,[object_bonus] = @object_bonus ,[maturity] = @maturity
735,[disable_scripts] = @disable_scripts ,[disable_collisions] = @disable_collisions ,[disable_physics] = @disable_physics 760,[disable_scripts] = @disable_scripts ,[disable_collisions] = @disable_collisions ,[disable_physics] = @disable_physics
@@ -741,10 +766,11 @@ VALUES
741,[covenant] = @covenant , [sunvectorx] = @sunvectorx, [sunvectory] = @sunvectory, [sunvectorz] = @sunvectorz, [Sandbox] = @Sandbox, [loaded_creation_datetime] = @loaded_creation_datetime, [loaded_creation_id] = @loaded_creation_id 766,[covenant] = @covenant , [sunvectorx] = @sunvectorx, [sunvectory] = @sunvectory, [sunvectorz] = @sunvectorz, [Sandbox] = @Sandbox, [loaded_creation_datetime] = @loaded_creation_datetime, [loaded_creation_id] = @loaded_creation_id
742 WHERE [regionUUID] = @regionUUID"; 767 WHERE [regionUUID] = @regionUUID";
743 768
744 using (AutoClosingSqlCommand cmd = _Database.Query(sql)) 769 using (SqlConnection conn = new SqlConnection(m_connectionString))
770 using (SqlCommand cmd = new SqlCommand(sql, conn))
745 { 771 {
746 cmd.Parameters.AddRange(CreateRegionSettingParameters(regionSettings)); 772 cmd.Parameters.AddRange(CreateRegionSettingParameters(regionSettings));
747 773 conn.Open();
748 cmd.ExecuteNonQuery(); 774 cmd.ExecuteNonQuery();
749 } 775 }
750 } 776 }
@@ -801,9 +827,11 @@ VALUES
801 @elevation_2_ne,@elevation_1_se,@elevation_2_se,@elevation_1_sw,@elevation_2_sw,@water_height,@terrain_raise_limit, 827 @elevation_2_ne,@elevation_1_se,@elevation_2_se,@elevation_1_sw,@elevation_2_sw,@water_height,@terrain_raise_limit,
802 @terrain_lower_limit,@use_estate_sun,@fixed_sun,@sun_position,@covenant,@sunvectorx,@sunvectory, @sunvectorz, @Sandbox, @loaded_creation_datetime, @loaded_creation_id)"; 828 @terrain_lower_limit,@use_estate_sun,@fixed_sun,@sun_position,@covenant,@sunvectorx,@sunvectory, @sunvectorz, @Sandbox, @loaded_creation_datetime, @loaded_creation_id)";
803 829
804 using (AutoClosingSqlCommand cmd = _Database.Query(sql)) 830 using (SqlConnection conn = new SqlConnection(m_connectionString))
831 using (SqlCommand cmd = new SqlCommand(sql, conn))
805 { 832 {
806 cmd.Parameters.AddRange(CreateRegionSettingParameters(regionSettings)); 833 cmd.Parameters.AddRange(CreateRegionSettingParameters(regionSettings));
834 conn.Open();
807 cmd.ExecuteNonQuery(); 835 cmd.ExecuteNonQuery();
808 } 836 }
809 } 837 }
@@ -907,15 +935,15 @@ VALUES
907 newData.PassHours = Convert.ToSingle(row["PassHours"]); 935 newData.PassHours = Convert.ToSingle(row["PassHours"]);
908 newData.PassPrice = Convert.ToInt32(row["PassPrice"]); 936 newData.PassPrice = Convert.ToInt32(row["PassPrice"]);
909 937
910// UUID authedbuyer; 938 // UUID authedbuyer;
911// UUID snapshotID; 939 // UUID snapshotID;
912// 940 //
913// if (UUID.TryParse((string)row["AuthBuyerID"], out authedbuyer)) 941 // if (UUID.TryParse((string)row["AuthBuyerID"], out authedbuyer))
914// newData.AuthBuyerID = authedbuyer; 942 // newData.AuthBuyerID = authedbuyer;
915// 943 //
916// if (UUID.TryParse((string)row["SnapshotUUID"], out snapshotID)) 944 // if (UUID.TryParse((string)row["SnapshotUUID"], out snapshotID))
917// newData.SnapshotID = snapshotID; 945 // newData.SnapshotID = snapshotID;
918 newData.AuthBuyerID = new UUID((Guid) row["AuthBuyerID"]); 946 newData.AuthBuyerID = new UUID((Guid)row["AuthBuyerID"]);
919 newData.SnapshotID = new UUID((Guid)row["SnapshotUUID"]); 947 newData.SnapshotID = new UUID((Guid)row["SnapshotUUID"]);
920 948
921 newData.OtherCleanTime = Convert.ToInt32(row["OtherCleanTime"]); 949 newData.OtherCleanTime = Convert.ToInt32(row["OtherCleanTime"]);
@@ -1184,7 +1212,7 @@ VALUES
1184 #endregion 1212 #endregion
1185 1213
1186 #region Create parameters methods 1214 #region Create parameters methods
1187 1215
1188 /// <summary> 1216 /// <summary>
1189 /// Creates the prim inventory parameters. 1217 /// Creates the prim inventory parameters.
1190 /// </summary> 1218 /// </summary>
@@ -1468,7 +1496,7 @@ VALUES
1468 1496
1469 parameters.Add(_Database.CreateParameter("CollisionSound", prim.CollisionSound)); 1497 parameters.Add(_Database.CreateParameter("CollisionSound", prim.CollisionSound));
1470 parameters.Add(_Database.CreateParameter("CollisionSoundVolume", prim.CollisionSoundVolume)); 1498 parameters.Add(_Database.CreateParameter("CollisionSoundVolume", prim.CollisionSoundVolume));
1471 if (prim.PassTouches) 1499 if (prim.PassTouches)
1472 parameters.Add(_Database.CreateParameter("PassTouches", 1)); 1500 parameters.Add(_Database.CreateParameter("PassTouches", 1));
1473 else 1501 else
1474 parameters.Add(_Database.CreateParameter("PassTouches", 0)); 1502 parameters.Add(_Database.CreateParameter("PassTouches", 0));
@@ -1523,7 +1551,7 @@ VALUES
1523 1551
1524 return parameters.ToArray(); 1552 return parameters.ToArray();
1525 } 1553 }
1526 1554
1527 #endregion 1555 #endregion
1528 1556
1529 #endregion 1557 #endregion
diff --git a/OpenSim/Data/MSSQL/MSSQLManager.cs b/OpenSim/Data/MSSQL/MSSQLManager.cs
index 3d7a768..4309b42 100644
--- a/OpenSim/Data/MSSQL/MSSQLManager.cs
+++ b/OpenSim/Data/MSSQL/MSSQLManager.cs
@@ -46,22 +46,7 @@ namespace OpenSim.Data.MSSQL
46 /// <summary> 46 /// <summary>
47 /// Connection string for ADO.net 47 /// Connection string for ADO.net
48 /// </summary> 48 /// </summary>
49 private readonly string connectionString; 49 private readonly string connectionString;
50
51 public MSSQLManager(string dataSource, string initialCatalog, string persistSecurityInfo, string userId,
52 string password)
53 {
54 SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
55
56 builder.DataSource = dataSource;
57 builder.InitialCatalog = initialCatalog;
58 builder.PersistSecurityInfo = Convert.ToBoolean(persistSecurityInfo);
59 builder.UserID = userId;
60 builder.Password = password;
61 builder.ApplicationName = Assembly.GetEntryAssembly().Location;
62
63 connectionString = builder.ToString();
64 }
65 50
66 /// <summary> 51 /// <summary>
67 /// Initialize the manager and set the connectionstring 52 /// Initialize the manager and set the connectionstring
@@ -72,94 +57,6 @@ namespace OpenSim.Data.MSSQL
72 connectionString = connection; 57 connectionString = connection;
73 } 58 }
74 59
75 public SqlConnection DatabaseConnection()
76 {
77 SqlConnection conn = new SqlConnection(connectionString);
78
79 //TODO is this good??? Opening connection here
80 conn.Open();
81
82 return conn;
83 }
84
85 #region Obsolete functions, can be removed!
86
87 /// <summary>
88 ///
89 /// </summary>
90 /// <param name="dt"></param>
91 /// <param name="name"></param>
92 /// <param name="type"></param>
93 [Obsolete("Do not use!")]
94 protected static void createCol(DataTable dt, string name, Type type)
95 {
96 DataColumn col = new DataColumn(name, type);
97 dt.Columns.Add(col);
98 }
99
100 /// <summary>
101 /// Define Table function
102 /// </summary>
103 /// <param name="dt"></param>
104 /// <returns></returns>
105/*
106 [Obsolete("Do not use!")]
107 protected static string defineTable(DataTable dt)
108 {
109 string sql = "create table " + dt.TableName + "(";
110 string subsql = String.Empty;
111 foreach (DataColumn col in dt.Columns)
112 {
113 if (subsql.Length > 0)
114 {
115 // a map function would rock so much here
116 subsql += ",\n";
117 }
118
119 subsql += col.ColumnName + " " + SqlType(col.DataType);
120 if (col == dt.PrimaryKey[0])
121 {
122 subsql += " primary key";
123 }
124 }
125 sql += subsql;
126 sql += ")";
127 return sql;
128 }
129*/
130
131 #endregion
132
133 /// <summary>
134 /// Type conversion function
135 /// </summary>
136 /// <param name="type">a type</param>
137 /// <returns>a sqltype</returns>
138 /// <remarks>this is something we'll need to implement for each db slightly differently.</remarks>
139/*
140 [Obsolete("Used by a obsolete methods")]
141 public static string SqlType(Type type)
142 {
143 if (type == typeof(String))
144 {
145 return "varchar(255)";
146 }
147 if (type == typeof(Int32))
148 {
149 return "integer";
150 }
151 if (type == typeof(Double))
152 {
153 return "float";
154 }
155 if (type == typeof(Byte[]))
156 {
157 return "image";
158 }
159 return "varchar(255)";
160 }
161*/
162
163 /// <summary> 60 /// <summary>
164 /// Type conversion to a SQLDbType functions 61 /// Type conversion to a SQLDbType functions
165 /// </summary> 62 /// </summary>
@@ -286,134 +183,20 @@ namespace OpenSim.Data.MSSQL
286 private static readonly Dictionary<string, string> emptyDictionary = new Dictionary<string, string>(); 183 private static readonly Dictionary<string, string> emptyDictionary = new Dictionary<string, string>();
287 184
288 /// <summary> 185 /// <summary>
289 /// Run a query and return a sql db command
290 /// </summary>
291 /// <param name="sql">The SQL query.</param>
292 /// <returns></returns>
293 internal AutoClosingSqlCommand Query(string sql)
294 {
295 return Query(sql, emptyDictionary);
296 }
297
298 /// <summary>
299 /// Runs a query with protection against SQL Injection by using parameterised input.
300 /// </summary>
301 /// <param name="sql">The SQL string - replace any variables such as WHERE x = "y" with WHERE x = @y</param>
302 /// <param name="parameters">The parameters - index so that @y is indexed as 'y'</param>
303 /// <returns>A Sql DB Command</returns>
304 internal AutoClosingSqlCommand Query(string sql, Dictionary<string, string> parameters)
305 {
306 SqlCommand dbcommand = DatabaseConnection().CreateCommand();
307 dbcommand.CommandText = sql;
308 foreach (KeyValuePair<string, string> param in parameters)
309 {
310 dbcommand.Parameters.AddWithValue(param.Key, param.Value);
311 }
312
313 return new AutoClosingSqlCommand(dbcommand);
314 }
315
316 /// <summary>
317 /// Runs a query with protection against SQL Injection by using parameterised input.
318 /// </summary>
319 /// <param name="sql">The SQL string - replace any variables such as WHERE x = "y" with WHERE x = @y</param>
320 /// <param name="sqlParameter">A parameter - use createparameter to create parameter</param>
321 /// <returns></returns>
322 internal AutoClosingSqlCommand Query(string sql, SqlParameter sqlParameter)
323 {
324 SqlCommand dbcommand = DatabaseConnection().CreateCommand();
325 dbcommand.CommandText = sql;
326 dbcommand.Parameters.Add(sqlParameter);
327
328 return new AutoClosingSqlCommand(dbcommand);
329 }
330
331 /// <summary>
332 /// Checks if we need to do some migrations to the database 186 /// Checks if we need to do some migrations to the database
333 /// </summary> 187 /// </summary>
334 /// <param name="migrationStore">migrationStore.</param> 188 /// <param name="migrationStore">migrationStore.</param>
335 public void CheckMigration(string migrationStore) 189 public void CheckMigration(string migrationStore)
336 { 190 {
337 using (SqlConnection connection = DatabaseConnection()) 191 using (SqlConnection connection = new SqlConnection(connectionString))
338 { 192 {
193 connection.Open();
339 Assembly assem = GetType().Assembly; 194 Assembly assem = GetType().Assembly;
340 MSSQLMigration migration = new MSSQLMigration(connection, assem, migrationStore); 195 MSSQLMigration migration = new MSSQLMigration(connection, assem, migrationStore);
341 196
342 migration.Update(); 197 migration.Update();
343
344 connection.Close();
345 } 198 }
346 } 199 }
347
348 #region Old Testtable functions
349
350 /// <summary>
351 /// Execute a SQL statement stored in a resource, as a string
352 /// </summary>
353 /// <param name="name">the ressource string</param>
354 public void ExecuteResourceSql(string name)
355 {
356 using (IDbCommand cmd = Query(getResourceString(name), new Dictionary<string, string>()))
357 {
358 cmd.ExecuteNonQuery();
359 }
360 }
361
362 /// <summary>
363 /// Given a list of tables, return the version of the tables, as seen in the database
364 /// </summary>
365 /// <param name="tableList"></param>
366 public void GetTableVersion(Dictionary<string, string> tableList)
367 {
368 Dictionary<string, string> param = new Dictionary<string, string>();
369 param["dbname"] = new SqlConnectionStringBuilder(connectionString).InitialCatalog;
370
371 using (IDbCommand tablesCmd =
372 Query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG=@dbname", param))
373 using (IDataReader tables = tablesCmd.ExecuteReader())
374 {
375 while (tables.Read())
376 {
377 try
378 {
379 string tableName = (string)tables["TABLE_NAME"];
380 if (tableList.ContainsKey(tableName))
381 tableList[tableName] = tableName;
382 }
383 catch (Exception e)
384 {
385 m_log.Error(e.ToString());
386 }
387 }
388 tables.Close();
389 }
390
391 }
392
393 /// <summary>
394 ///
395 /// </summary>
396 /// <param name="name"></param>
397 /// <returns></returns>
398 private string getResourceString(string name)
399 {
400 Assembly assem = GetType().Assembly;
401 string[] names = assem.GetManifestResourceNames();
402
403 foreach (string s in names)
404 if (s.EndsWith(name))
405 using (Stream resource = assem.GetManifestResourceStream(s))
406 {
407 using (StreamReader resourceReader = new StreamReader(resource))
408 {
409 string resourceString = resourceReader.ReadToEnd();
410 return resourceString;
411 }
412 }
413 throw new Exception(string.Format("Resource '{0}' was not found", name));
414 }
415
416 #endregion
417 200
418 /// <summary> 201 /// <summary>
419 /// Returns the version of this DB provider 202 /// Returns the version of this DB provider
diff --git a/OpenSim/Data/MSSQL/MSSQLPresenceData.cs b/OpenSim/Data/MSSQL/MSSQLPresenceData.cs
new file mode 100644
index 0000000..5a4ad3a
--- /dev/null
+++ b/OpenSim/Data/MSSQL/MSSQLPresenceData.cs
@@ -0,0 +1,170 @@
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 System.Data.SqlClient;
37
38namespace OpenSim.Data.MSSQL
39{
40 /// <summary>
41 /// A MySQL Interface for the Presence Server
42 /// </summary>
43 public class MSSQLPresenceData : MSSQLGenericTableHandler<PresenceData>,
44 IPresenceData
45 {
46 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
47
48 public MSSQLPresenceData(string connectionString, string realm) :
49 base(connectionString, realm, "Presence")
50 {
51 }
52
53 public PresenceData Get(UUID sessionID)
54 {
55 PresenceData[] ret = Get("SessionID",
56 sessionID.ToString());
57
58 if (ret.Length == 0)
59 return null;
60
61 return ret[0];
62 }
63
64 public void LogoutRegionAgents(UUID regionID)
65 {
66 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
67 using (SqlCommand cmd = new SqlCommand())
68 {
69
70 cmd.CommandText = String.Format("UPDATE {0} SET Online='false' WHERE [RegionID]=@RegionID", m_Realm);
71
72 cmd.Parameters.Add(m_database.CreateParameter("@RegionID", regionID.ToString()));
73 cmd.Connection = conn;
74 conn.Open();
75 cmd.ExecuteNonQuery();
76 }
77 }
78
79 public bool ReportAgent(UUID sessionID, UUID regionID, string position,
80 string lookAt)
81 {
82 PresenceData[] pd = Get("SessionID", sessionID.ToString());
83 if (pd.Length == 0)
84 return false;
85
86 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
87 using (SqlCommand cmd = new SqlCommand())
88 {
89
90 cmd.CommandText = String.Format(@"UPDATE {0} SET
91 [RegionID] = @RegionID,
92 [Position] = @Position,
93 [LookAt] = @LookAt,
94 [Online] = 'true'
95 WHERE [SessionID] = @SessionID", m_Realm);
96
97 cmd.Parameters.Add(m_database.CreateParameter("@SessionID", sessionID.ToString()));
98 cmd.Parameters.Add(m_database.CreateParameter("@RegionID", regionID.ToString()));
99 cmd.Parameters.Add(m_database.CreateParameter("@Position", position.ToString()));
100 cmd.Parameters.Add(m_database.CreateParameter("@LookAt", lookAt.ToString()));
101 cmd.Connection = conn;
102 conn.Open();
103 if (cmd.ExecuteNonQuery() == 0)
104 return false;
105 }
106 return true;
107 }
108
109 public bool SetHomeLocation(string userID, UUID regionID, Vector3 position, Vector3 lookAt)
110 {
111 PresenceData[] pd = Get("UserID", userID);
112 if (pd.Length == 0)
113 return false;
114
115 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
116 using (SqlCommand cmd = new SqlCommand())
117 {
118
119 cmd.CommandText = String.Format(@"UPDATE {0} SET
120 [HomeRegionID] = @HomeRegionID,
121 [HomePosition] = @HomePosition,
122 [HomeLookAt] = @HomeLookAt
123 WHERE [UserID] = @UserID", m_Realm);
124
125 cmd.Parameters.Add(m_database.CreateParameter("@UserID", userID));
126 cmd.Parameters.Add(m_database.CreateParameter("@HomeRegionID", regionID.ToString()));
127 cmd.Parameters.Add(m_database.CreateParameter("@HomePosition", position));
128 cmd.Parameters.Add(m_database.CreateParameter("@HomeLookAt", lookAt));
129 cmd.Connection = conn;
130 conn.Open();
131 if (cmd.ExecuteNonQuery() == 0)
132 return false;
133 }
134 return true;
135 }
136
137 public void Prune(string userID)
138 {
139 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
140 using (SqlCommand cmd = new SqlCommand())
141 {
142 cmd.CommandText = String.Format("SELECT * from {0} WHERE [UserID] = @UserID", m_Realm);
143
144 cmd.Parameters.Add(m_database.CreateParameter("@UserID", userID));
145 cmd.Connection = conn;
146 conn.Open();
147
148 using (SqlDataReader reader = cmd.ExecuteReader())
149 {
150 List<UUID> deleteSessions = new List<UUID>();
151 int online = 0;
152
153 while (reader.Read())
154 {
155 if (bool.Parse(reader["Online"].ToString()))
156 online++;
157 else
158 deleteSessions.Add(new UUID(reader["SessionID"].ToString()));
159 }
160
161 if (online == 0 && deleteSessions.Count > 0)
162 deleteSessions.RemoveAt(0);
163
164 foreach (UUID s in deleteSessions)
165 Delete("SessionID", s.ToString());
166 }
167 }
168 }
169 }
170}
diff --git a/OpenSim/Data/MSSQL/MSSQLRegionData.cs b/OpenSim/Data/MSSQL/MSSQLRegionData.cs
index fbfb78e..66c3f81 100644
--- a/OpenSim/Data/MSSQL/MSSQLRegionData.cs
+++ b/OpenSim/Data/MSSQL/MSSQLRegionData.cs
@@ -129,10 +129,10 @@ namespace OpenSim.Data.MSSQL
129 using (SqlConnection conn = new SqlConnection(m_ConnectionString)) 129 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
130 using (SqlCommand cmd = new SqlCommand(sql, conn)) 130 using (SqlCommand cmd = new SqlCommand(sql, conn))
131 { 131 {
132 cmd.Parameters.Add(m_database.CreateParameter("@startX", startX.ToString())); 132 cmd.Parameters.Add(m_database.CreateParameter("@startX", startX));
133 cmd.Parameters.Add(m_database.CreateParameter("@startY", startY.ToString())); 133 cmd.Parameters.Add(m_database.CreateParameter("@startY", startY));
134 cmd.Parameters.Add(m_database.CreateParameter("@endX", endX.ToString())); 134 cmd.Parameters.Add(m_database.CreateParameter("@endX", endX));
135 cmd.Parameters.Add(m_database.CreateParameter("@endY", endY.ToString())); 135 cmd.Parameters.Add(m_database.CreateParameter("@endY", endY));
136 cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID)); 136 cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID));
137 conn.Open(); 137 conn.Open();
138 return RunCommand(cmd); 138 return RunCommand(cmd);
@@ -310,12 +310,34 @@ namespace OpenSim.Data.MSSQL
310 310
311 public List<RegionData> GetDefaultRegions(UUID scopeID) 311 public List<RegionData> GetDefaultRegions(UUID scopeID)
312 { 312 {
313 return null; 313 string sql = "SELECT * FROM [" + m_Realm + "] WHERE (flags & 1) <> 0";
314 if (scopeID != UUID.Zero)
315 sql += " AND ScopeID = @scopeID";
316
317 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
318 using (SqlCommand cmd = new SqlCommand(sql, conn))
319 {
320 cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID));
321 conn.Open();
322 return RunCommand(cmd);
323 }
324
314 } 325 }
315 326
316 public List<RegionData> GetFallbackRegions(UUID scopeID, int x, int y) 327 public List<RegionData> GetFallbackRegions(UUID scopeID, int x, int y)
317 { 328 {
318 return null; 329 string sql = "SELECT * FROM [" + m_Realm + "] WHERE (flags & 2) <> 0";
330 if (scopeID != UUID.Zero)
331 sql += " AND ScopeID = @scopeID";
332
333 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
334 using (SqlCommand cmd = new SqlCommand(sql, conn))
335 {
336 cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID));
337 conn.Open();
338 // TODO: distance-sort results
339 return RunCommand(cmd);
340 }
319 } 341 }
320 } 342 }
321} 343}
diff --git a/OpenSim/Data/MSSQL/MSSQLUserAccountData.cs b/OpenSim/Data/MSSQL/MSSQLUserAccountData.cs
index 01c64dc..9f18e5e 100644
--- a/OpenSim/Data/MSSQL/MSSQLUserAccountData.cs
+++ b/OpenSim/Data/MSSQL/MSSQLUserAccountData.cs
@@ -36,168 +36,207 @@ using System.Text;
36 36
37namespace OpenSim.Data.MSSQL 37namespace OpenSim.Data.MSSQL
38{ 38{
39 public class MSSQLUserAccountData : IUserAccountData 39 public class MSSQLUserAccountData : MSSQLGenericTableHandler<UserAccountData>,IUserAccountData
40 { 40 {
41 private string m_Realm; 41 public MSSQLUserAccountData(string connectionString, string realm) :
42 private List<string> m_ColumnNames = null; 42 base(connectionString, realm, "UserAccount")
43 private string m_ConnectionString;
44 private MSSQLManager m_database;
45
46 public MSSQLUserAccountData(string connectionString, string realm)
47 {
48 m_Realm = realm;
49 m_ConnectionString = connectionString;
50 m_database = new MSSQLManager(connectionString);
51
52 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
53 {
54 conn.Open();
55 Migration m = new Migration(conn, GetType().Assembly, "UserStore");
56 m.Update();
57 }
58 }
59
60 public List<UserAccountData> Query(UUID principalID, UUID scopeID, string query)
61 { 43 {
62 return null;
63 } 44 }
45 //private string m_Realm;
46 //private List<string> m_ColumnNames = null;
47 //private MSSQLManager m_database;
48
49 //public MSSQLUserAccountData(string connectionString, string realm)
50 //{
51 // m_Realm = realm;
52 // m_ConnectionString = connectionString;
53 // m_database = new MSSQLManager(connectionString);
54
55 // using (SqlConnection conn = new SqlConnection(m_ConnectionString))
56 // {
57 // conn.Open();
58 // Migration m = new Migration(conn, GetType().Assembly, "UserStore");
59 // m.Update();
60 // }
61 //}
62
63 //public List<UserAccountData> Query(UUID principalID, UUID scopeID, string query)
64 //{
65 // return null;
66 //}
67
68 //public UserAccountData Get(UUID principalID, UUID scopeID)
69 //{
70 // UserAccountData ret = new UserAccountData();
71 // ret.Data = new Dictionary<string, string>();
72
73 // string sql = string.Format("select * from {0} where UUID = @principalID", m_Realm);
74 // if (scopeID != UUID.Zero)
75 // sql += " and ScopeID = @scopeID";
76
77 // using (SqlConnection conn = new SqlConnection(m_ConnectionString))
78 // using (SqlCommand cmd = new SqlCommand(sql, conn))
79 // {
80 // cmd.Parameters.Add(m_database.CreateParameter("@principalID", principalID));
81 // cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID));
82
83 // conn.Open();
84 // using (SqlDataReader result = cmd.ExecuteReader())
85 // {
86 // if (result.Read())
87 // {
88 // ret.PrincipalID = principalID;
89 // UUID scope;
90 // UUID.TryParse(result["ScopeID"].ToString(), out scope);
91 // ret.ScopeID = scope;
92
93 // if (m_ColumnNames == null)
94 // {
95 // m_ColumnNames = new List<string>();
96
97 // DataTable schemaTable = result.GetSchemaTable();
98 // foreach (DataRow row in schemaTable.Rows)
99 // m_ColumnNames.Add(row["ColumnName"].ToString());
100 // }
101
102 // foreach (string s in m_ColumnNames)
103 // {
104 // if (s == "UUID")
105 // continue;
106 // if (s == "ScopeID")
107 // continue;
108
109 // ret.Data[s] = result[s].ToString();
110 // }
111 // return ret;
112 // }
113 // }
114 // }
115 // return null;
116 //}
117
118 //public bool Store(UserAccountData data)
119 //{
120 // if (data.Data.ContainsKey("UUID"))
121 // data.Data.Remove("UUID");
122 // if (data.Data.ContainsKey("ScopeID"))
123 // data.Data.Remove("ScopeID");
124
125 // string[] fields = new List<string>(data.Data.Keys).ToArray();
126
127 // using (SqlConnection conn = new SqlConnection(m_ConnectionString))
128 // using (SqlCommand cmd = new SqlCommand())
129 // {
130 // StringBuilder updateBuilder = new StringBuilder();
131 // updateBuilder.AppendFormat("update {0} set ", m_Realm);
132 // bool first = true;
133 // foreach (string field in fields)
134 // {
135 // if (!first)
136 // updateBuilder.Append(", ");
137 // updateBuilder.AppendFormat("{0} = @{0}", field);
138
139 // first = false;
140 // cmd.Parameters.Add(m_database.CreateParameter("@" + field, data.Data[field]));
141 // }
142
143 // updateBuilder.Append(" where UUID = @principalID");
144
145 // if (data.ScopeID != UUID.Zero)
146 // updateBuilder.Append(" and ScopeID = @scopeID");
147
148 // cmd.CommandText = updateBuilder.ToString();
149 // cmd.Connection = conn;
150 // cmd.Parameters.Add(m_database.CreateParameter("@principalID", data.PrincipalID));
151 // cmd.Parameters.Add(m_database.CreateParameter("@scopeID", data.ScopeID));
152 // conn.Open();
153
154 // if (cmd.ExecuteNonQuery() < 1)
155 // {
156 // StringBuilder insertBuilder = new StringBuilder();
157 // insertBuilder.AppendFormat("insert into {0} (UUID, ScopeID, ", m_Realm);
158 // insertBuilder.Append(String.Join(", ", fields));
159 // insertBuilder.Append(") values (@principalID, @scopeID, @");
160 // insertBuilder.Append(String.Join(", @", fields));
161 // insertBuilder.Append(")");
162
163 // cmd.CommandText = insertBuilder.ToString();
164
165 // if (cmd.ExecuteNonQuery() < 1)
166 // {
167 // return false;
168 // }
169 // }
170 // }
171 // return true;
172 //}
173
174 //public bool Store(UserAccountData data, UUID principalID, string token)
175 //{
176 // return false;
177 //}
178
179 //public bool SetDataItem(UUID principalID, string item, string value)
180 //{
181 // string sql = string.Format("update {0} set {1} = @{1} where UUID = @UUID", m_Realm, item);
182 // using (SqlConnection conn = new SqlConnection(m_ConnectionString))
183 // using (SqlCommand cmd = new SqlCommand(sql, conn))
184 // {
185 // cmd.Parameters.Add(m_database.CreateParameter("@" + item, value));
186 // cmd.Parameters.Add(m_database.CreateParameter("@UUID", principalID));
187
188 // conn.Open();
189
190 // if (cmd.ExecuteNonQuery() > 0)
191 // return true;
192 // }
193 // return false;
194 //}
195
196 //public UserAccountData[] Get(string[] keys, string[] vals)
197 //{
198 // return null;
199 //}
64 200
65 public UserAccountData Get(UUID principalID, UUID scopeID) 201 public UserAccountData[] GetUsers(UUID scopeID, string query)
66 { 202 {
67 UserAccountData ret = new UserAccountData(); 203 string[] words = query.Split(new char[] { ' ' });
68 ret.Data = new Dictionary<string, string>();
69 204
70 string sql = string.Format("select * from {0} where UUID = @principalID", m_Realm); 205 for (int i = 0; i < words.Length; i++)
71 if (scopeID != UUID.Zero)
72 sql += " and ScopeID = @scopeID";
73
74 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
75 using (SqlCommand cmd = new SqlCommand(sql, conn))
76 { 206 {
77 cmd.Parameters.Add(m_database.CreateParameter("@principalID", principalID)); 207 if (words[i].Length < 3)
78 cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID));
79
80 conn.Open();
81 using (SqlDataReader result = cmd.ExecuteReader())
82 { 208 {
83 if (result.Read()) 209 if (i != words.Length - 1)
84 { 210 Array.Copy(words, i + 1, words, i, words.Length - i - 1);
85 ret.PrincipalID = principalID; 211 Array.Resize(ref words, words.Length - 1);
86 UUID scope;
87 UUID.TryParse(result["ScopeID"].ToString(), out scope);
88 ret.ScopeID = scope;
89
90 if (m_ColumnNames == null)
91 {
92 m_ColumnNames = new List<string>();
93
94 DataTable schemaTable = result.GetSchemaTable();
95 foreach (DataRow row in schemaTable.Rows)
96 m_ColumnNames.Add(row["ColumnName"].ToString());
97 }
98
99 foreach (string s in m_ColumnNames)
100 {
101 if (s == "UUID")
102 continue;
103 if (s == "ScopeID")
104 continue;
105
106 ret.Data[s] = result[s].ToString();
107 }
108 return ret;
109 }
110 } 212 }
111 } 213 }
112 return null;
113 }
114 214
115 public bool Store(UserAccountData data) 215 if (words.Length == 0)
116 { 216 return new UserAccountData[0];
117 if (data.Data.ContainsKey("UUID"))
118 data.Data.Remove("UUID");
119 if (data.Data.ContainsKey("ScopeID"))
120 data.Data.Remove("ScopeID");
121 217
122 string[] fields = new List<string>(data.Data.Keys).ToArray(); 218 if (words.Length > 2)
219 return new UserAccountData[0];
123 220
124 using (SqlConnection conn = new SqlConnection(m_ConnectionString)) 221 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
125 using (SqlCommand cmd = new SqlCommand()) 222 using (SqlCommand cmd = new SqlCommand())
126 { 223 {
127 StringBuilder updateBuilder = new StringBuilder(); 224 if (words.Length == 1)
128 updateBuilder.AppendFormat("update {0} set ", m_Realm);
129 bool first = true;
130 foreach (string field in fields)
131 { 225 {
132 if (!first) 226 cmd.CommandText = String.Format("select * from {0} where ([ScopeID]=@ScopeID or [ScopeID]='00000000-0000-0000-0000-000000000000') and ([FirstName] like @search or [LastName] like @search)", m_Realm);
133 updateBuilder.Append(", "); 227 cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID));
134 updateBuilder.AppendFormat("{0} = @{0}", field); 228 cmd.Parameters.Add(m_database.CreateParameter("@search", "%" + words[0] + "%"));
135
136 first = false;
137 cmd.Parameters.Add(m_database.CreateParameter("@" + field, data.Data[field]));
138 } 229 }
139 230 else
140 updateBuilder.Append(" where UUID = @principalID");
141
142 if (data.ScopeID != UUID.Zero)
143 updateBuilder.Append(" and ScopeID = @scopeID");
144
145 cmd.CommandText = updateBuilder.ToString();
146 cmd.Connection = conn;
147 cmd.Parameters.Add(m_database.CreateParameter("@principalID", data.PrincipalID));
148 cmd.Parameters.Add(m_database.CreateParameter("@scopeID", data.ScopeID));
149 conn.Open();
150
151 if (cmd.ExecuteNonQuery() < 1)
152 { 231 {
153 StringBuilder insertBuilder = new StringBuilder(); 232 cmd.CommandText = String.Format("select * from {0} where ([ScopeID]=@ScopeID or [ScopeID]='00000000-0000-0000-0000-000000000000') and ([FirstName] like @searchFirst or [LastName] like @searchLast)", m_Realm);
154 insertBuilder.AppendFormat("insert into {0} (UUID, ScopeID, ", m_Realm); 233 cmd.Parameters.Add(m_database.CreateParameter("@searchFirst", "%" + words[0] + "%"));
155 insertBuilder.Append(String.Join(", ", fields)); 234 cmd.Parameters.Add(m_database.CreateParameter("@searchLast", "%" + words[1] + "%"));
156 insertBuilder.Append(") values (@principalID, @scopeID, @"); 235 cmd.Parameters.Add(m_database.CreateParameter("@ScopeID", scopeID.ToString()));
157 insertBuilder.Append(String.Join(", @", fields));
158 insertBuilder.Append(")");
159
160 cmd.CommandText = insertBuilder.ToString();
161
162 if (cmd.ExecuteNonQuery() < 1)
163 {
164 return false;
165 }
166 } 236 }
167 }
168 return true;
169 }
170
171 public bool Store(UserAccountData data, UUID principalID, string token)
172 {
173 return false;
174 }
175
176 public bool SetDataItem(UUID principalID, string item, string value)
177 {
178 string sql = string.Format("update {0} set {1} = @{1} where UUID = @UUID", m_Realm, item);
179 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
180 using (SqlCommand cmd = new SqlCommand(sql, conn))
181 {
182 cmd.Parameters.Add(m_database.CreateParameter("@" + item, value));
183 cmd.Parameters.Add(m_database.CreateParameter("@UUID", principalID));
184
185 conn.Open();
186 237
187 if (cmd.ExecuteNonQuery() > 0) 238 return DoQuery(cmd);
188 return true;
189 } 239 }
190 return false;
191 }
192
193 public UserAccountData[] Get(string[] keys, string[] vals)
194 {
195 return null;
196 }
197
198 public UserAccountData[] GetUsers(UUID scopeID, string query)
199 {
200 return null;
201 } 240 }
202 } 241 }
203} 242}
diff --git a/OpenSim/Data/MSSQL/MSSQLUserData.cs b/OpenSim/Data/MSSQL/MSSQLUserData.cs
new file mode 100644
index 0000000..6bdb559
--- /dev/null
+++ b/OpenSim/Data/MSSQL/MSSQLUserData.cs
@@ -0,0 +1,1238 @@
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 System.Data.SqlClient;
33using System.Reflection;
34using log4net;
35using OpenMetaverse;
36using OpenSim.Framework;
37
38namespace OpenSim.Data.MSSQL
39{
40 /// <summary>
41 /// A database interface class to a user profile storage system
42 /// </summary>
43 public class MSSQLUserData : UserDataBase
44 {
45 private const string _migrationStore = "UserStore";
46
47 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
48
49 /// <summary>
50 /// Database manager for MSSQL
51 /// </summary>
52 public MSSQLManager database;
53 private string m_connectionString;
54
55 private const string m_agentsTableName = "agents";
56 private const string m_usersTableName = "users";
57 private const string m_userFriendsTableName = "userfriends";
58
59 // [Obsolete("Cannot be default-initialized!")]
60 override public void Initialise()
61 {
62 m_log.Info("[MSSQLUserData]: " + Name + " cannot be default-initialized!");
63 throw new PluginNotInitialisedException(Name);
64 }
65
66 /// <summary>
67 /// Loads and initialises the MSSQL storage plugin
68 /// </summary>
69 /// <param name="connect">connectionstring</param>
70 /// <remarks>use mssql_connection.ini</remarks>
71 override public void Initialise(string connect)
72 {
73 m_connectionString = connect;
74 database = new MSSQLManager(connect);
75
76
77 //Check migration on DB
78 database.CheckMigration(_migrationStore);
79 }
80
81 /// <summary>
82 /// Releases unmanaged and - optionally - managed resources
83 /// </summary>
84 override public void Dispose() { }
85
86 #region User table methods
87
88 /// <summary>
89 /// Searches the database for a specified user profile by name components
90 /// </summary>
91 /// <param name="user">The first part of the account name</param>
92 /// <param name="last">The second part of the account name</param>
93 /// <returns>A user profile</returns>
94 override public UserProfileData GetUserByName(string user, string last)
95 {
96 string sql = string.Format(@"SELECT * FROM {0}
97 WHERE username = @first AND lastname = @second", m_usersTableName);
98 using (SqlConnection conn = new SqlConnection(m_connectionString))
99 using (SqlCommand cmd = new SqlCommand(sql, conn))
100 {
101 cmd.Parameters.Add(database.CreateParameter("first", user));
102 cmd.Parameters.Add(database.CreateParameter("second", last));
103 try
104 {
105 conn.Open();
106 using (SqlDataReader reader = cmd.ExecuteReader())
107 {
108 return ReadUserRow(reader);
109 }
110 }
111 catch (Exception e)
112 {
113 m_log.ErrorFormat("[USER DB] Error getting user profile for {0} {1}: {2}", user, last, e.Message);
114 return null;
115 }
116 }
117 }
118
119 /// <summary>
120 /// See IUserDataPlugin
121 /// </summary>
122 /// <param name="uuid"></param>
123 /// <returns></returns>
124 override public UserProfileData GetUserByUUID(UUID uuid)
125 {
126 string sql = string.Format("SELECT * FROM {0} WHERE UUID = @uuid", m_usersTableName);
127 using (SqlConnection conn = new SqlConnection(m_connectionString))
128 using (SqlCommand cmd = new SqlCommand(sql, conn))
129 {
130 cmd.Parameters.Add(database.CreateParameter("uuid", uuid));
131 conn.Open();
132 try
133 {
134 using (SqlDataReader reader = cmd.ExecuteReader())
135 {
136 return ReadUserRow(reader);
137 }
138 }
139 catch (Exception e)
140 {
141 m_log.ErrorFormat("[USER DB] Error getting user profile by UUID {0}, error: {1}", uuid, e.Message);
142 return null;
143 }
144 }
145 }
146
147
148 /// <summary>
149 /// Creates a new users profile
150 /// </summary>
151 /// <param name="user">The user profile to create</param>
152 override public void AddNewUserProfile(UserProfileData user)
153 {
154 try
155 {
156 InsertUserRow(user.ID, user.FirstName, user.SurName, user.Email, user.PasswordHash, user.PasswordSalt,
157 user.HomeRegion, user.HomeLocation.X, user.HomeLocation.Y,
158 user.HomeLocation.Z,
159 user.HomeLookAt.X, user.HomeLookAt.Y, user.HomeLookAt.Z, user.Created,
160 user.LastLogin, user.UserInventoryURI, user.UserAssetURI,
161 user.CanDoMask, user.WantDoMask,
162 user.AboutText, user.FirstLifeAboutText, user.Image,
163 user.FirstLifeImage, user.WebLoginKey, user.HomeRegionID,
164 user.GodLevel, user.UserFlags, user.CustomType, user.Partner);
165 }
166 catch (Exception e)
167 {
168 m_log.ErrorFormat("[USER DB] Error adding new profile, error: {0}", e.Message);
169 }
170 }
171
172 /// <summary>
173 /// update a user profile
174 /// </summary>
175 /// <param name="user">the profile to update</param>
176 /// <returns></returns>
177 override public bool UpdateUserProfile(UserProfileData user)
178 {
179 string sql = string.Format(@"UPDATE {0}
180 SET UUID = @uuid,
181 username = @username,
182 lastname = @lastname,
183 email = @email,
184 passwordHash = @passwordHash,
185 passwordSalt = @passwordSalt,
186 homeRegion = @homeRegion,
187 homeLocationX = @homeLocationX,
188 homeLocationY = @homeLocationY,
189 homeLocationZ = @homeLocationZ,
190 homeLookAtX = @homeLookAtX,
191 homeLookAtY = @homeLookAtY,
192 homeLookAtZ = @homeLookAtZ,
193 created = @created,
194 lastLogin = @lastLogin,
195 userInventoryURI = @userInventoryURI,
196 userAssetURI = @userAssetURI,
197 profileCanDoMask = @profileCanDoMask,
198 profileWantDoMask = @profileWantDoMask,
199 profileAboutText = @profileAboutText,
200 profileFirstText = @profileFirstText,
201 profileImage = @profileImage,
202 profileFirstImage = @profileFirstImage,
203 webLoginKey = @webLoginKey,
204 homeRegionID = @homeRegionID,
205 userFlags = @userFlags,
206 godLevel = @godLevel,
207 customType = @customType,
208 partner = @partner WHERE UUID = @keyUUUID;", m_usersTableName);
209 using (SqlConnection conn = new SqlConnection(m_connectionString))
210 using (SqlCommand command = new SqlCommand(sql, conn))
211 {
212 command.Parameters.Add(database.CreateParameter("uuid", user.ID));
213 command.Parameters.Add(database.CreateParameter("username", user.FirstName));
214 command.Parameters.Add(database.CreateParameter("lastname", user.SurName));
215 command.Parameters.Add(database.CreateParameter("email", user.Email));
216 command.Parameters.Add(database.CreateParameter("passwordHash", user.PasswordHash));
217 command.Parameters.Add(database.CreateParameter("passwordSalt", user.PasswordSalt));
218 command.Parameters.Add(database.CreateParameter("homeRegion", user.HomeRegion));
219 command.Parameters.Add(database.CreateParameter("homeLocationX", user.HomeLocation.X));
220 command.Parameters.Add(database.CreateParameter("homeLocationY", user.HomeLocation.Y));
221 command.Parameters.Add(database.CreateParameter("homeLocationZ", user.HomeLocation.Z));
222 command.Parameters.Add(database.CreateParameter("homeLookAtX", user.HomeLookAt.X));
223 command.Parameters.Add(database.CreateParameter("homeLookAtY", user.HomeLookAt.Y));
224 command.Parameters.Add(database.CreateParameter("homeLookAtZ", user.HomeLookAt.Z));
225 command.Parameters.Add(database.CreateParameter("created", user.Created));
226 command.Parameters.Add(database.CreateParameter("lastLogin", user.LastLogin));
227 command.Parameters.Add(database.CreateParameter("userInventoryURI", user.UserInventoryURI));
228 command.Parameters.Add(database.CreateParameter("userAssetURI", user.UserAssetURI));
229 command.Parameters.Add(database.CreateParameter("profileCanDoMask", user.CanDoMask));
230 command.Parameters.Add(database.CreateParameter("profileWantDoMask", user.WantDoMask));
231 command.Parameters.Add(database.CreateParameter("profileAboutText", user.AboutText));
232 command.Parameters.Add(database.CreateParameter("profileFirstText", user.FirstLifeAboutText));
233 command.Parameters.Add(database.CreateParameter("profileImage", user.Image));
234 command.Parameters.Add(database.CreateParameter("profileFirstImage", user.FirstLifeImage));
235 command.Parameters.Add(database.CreateParameter("webLoginKey", user.WebLoginKey));
236 command.Parameters.Add(database.CreateParameter("homeRegionID", user.HomeRegionID));
237 command.Parameters.Add(database.CreateParameter("userFlags", user.UserFlags));
238 command.Parameters.Add(database.CreateParameter("godLevel", user.GodLevel));
239 command.Parameters.Add(database.CreateParameter("customType", user.CustomType));
240 command.Parameters.Add(database.CreateParameter("partner", user.Partner));
241 command.Parameters.Add(database.CreateParameter("keyUUUID", user.ID));
242 conn.Open();
243 try
244 {
245 int affected = command.ExecuteNonQuery();
246 return (affected != 0);
247 }
248 catch (Exception e)
249 {
250 m_log.ErrorFormat("[USER DB] Error updating profile, error: {0}", e.Message);
251 }
252 }
253 return false;
254 }
255
256 #endregion
257
258 #region Agent table methods
259
260 /// <summary>
261 /// Returns a user session searching by name
262 /// </summary>
263 /// <param name="name">The account name</param>
264 /// <returns>The users session</returns>
265 override public UserAgentData GetAgentByName(string name)
266 {
267 return GetAgentByName(name.Split(' ')[0], name.Split(' ')[1]);
268 }
269
270 /// <summary>
271 /// Returns a user session by account name
272 /// </summary>
273 /// <param name="user">First part of the users account name</param>
274 /// <param name="last">Second part of the users account name</param>
275 /// <returns>The users session</returns>
276 override public UserAgentData GetAgentByName(string user, string last)
277 {
278 UserProfileData profile = GetUserByName(user, last);
279 return GetAgentByUUID(profile.ID);
280 }
281
282 /// <summary>
283 /// Returns an agent session by account UUID
284 /// </summary>
285 /// <param name="uuid">The accounts UUID</param>
286 /// <returns>The users session</returns>
287 override public UserAgentData GetAgentByUUID(UUID uuid)
288 {
289 string sql = string.Format("SELECT * FROM {0} WHERE UUID = @uuid", m_agentsTableName);
290 using (SqlConnection conn = new SqlConnection(m_connectionString))
291 using (SqlCommand cmd = new SqlCommand(sql, conn))
292 {
293 cmd.Parameters.Add(database.CreateParameter("uuid", uuid));
294 conn.Open();
295 try
296 {
297 using (SqlDataReader reader = cmd.ExecuteReader())
298 {
299 return readAgentRow(reader);
300 }
301 }
302 catch (Exception e)
303 {
304 m_log.ErrorFormat("[USER DB] Error updating agentdata by UUID, error: {0}", e.Message);
305 return null;
306 }
307 }
308 }
309
310 /// <summary>
311 /// Creates a new agent
312 /// </summary>
313 /// <param name="agent">The agent to create</param>
314 override public void AddNewUserAgent(UserAgentData agent)
315 {
316 try
317 {
318 InsertUpdateAgentRow(agent);
319 }
320 catch (Exception e)
321 {
322 m_log.ErrorFormat("[USER DB] Error adding new agentdata, error: {0}", e.Message);
323 }
324 }
325
326 #endregion
327
328 #region User Friends List Data
329
330 /// <summary>
331 /// Add a new friend in the friendlist
332 /// </summary>
333 /// <param name="friendlistowner">UUID of the friendlist owner</param>
334 /// <param name="friend">Friend's UUID</param>
335 /// <param name="perms">Permission flag</param>
336 override public void AddNewUserFriend(UUID friendlistowner, UUID friend, uint perms)
337 {
338 int dtvalue = Util.UnixTimeSinceEpoch();
339 string sql = string.Format(@"INSERT INTO {0}
340 (ownerID,friendID,friendPerms,datetimestamp)
341 VALUES
342 (@ownerID,@friendID,@friendPerms,@datetimestamp)", m_userFriendsTableName);
343 using (SqlConnection conn = new SqlConnection(m_connectionString))
344 using (SqlCommand cmd = new SqlCommand(sql, conn))
345 {
346 cmd.Parameters.Add(database.CreateParameter("ownerID", friendlistowner));
347 cmd.Parameters.Add(database.CreateParameter("friendID", friend));
348 cmd.Parameters.Add(database.CreateParameter("friendPerms", perms));
349 cmd.Parameters.Add(database.CreateParameter("datetimestamp", dtvalue));
350 conn.Open();
351 cmd.ExecuteNonQuery();
352
353 try
354 {
355 sql = string.Format(@"INSERT INTO {0}
356 (ownerID,friendID,friendPerms,datetimestamp)
357 VALUES
358 (@friendID,@ownerID,@friendPerms,@datetimestamp)", m_userFriendsTableName);
359 cmd.CommandText = sql;
360 cmd.ExecuteNonQuery();
361 }
362 catch (Exception e)
363 {
364 m_log.ErrorFormat("[USER DB] Error adding new userfriend, error: {0}", e.Message);
365 return;
366 }
367 }
368 }
369
370 /// <summary>
371 /// Remove an friend from the friendlist
372 /// </summary>
373 /// <param name="friendlistowner">UUID of the friendlist owner</param>
374 /// <param name="friend">UUID of the not-so-friendly user to remove from the list</param>
375 override public void RemoveUserFriend(UUID friendlistowner, UUID friend)
376 {
377 string sql = string.Format(@"DELETE from {0}
378 WHERE ownerID = @ownerID
379 AND friendID = @friendID", m_userFriendsTableName);
380 using (SqlConnection conn = new SqlConnection(m_connectionString))
381 using (SqlCommand cmd = new SqlCommand(sql, conn))
382 {
383 cmd.Parameters.Add(database.CreateParameter("@ownerID", friendlistowner));
384 cmd.Parameters.Add(database.CreateParameter("@friendID", friend));
385 cmd.ExecuteNonQuery();
386 sql = string.Format(@"DELETE from {0}
387 WHERE ownerID = @friendID
388 AND friendID = @ownerID", m_userFriendsTableName);
389 cmd.CommandText = sql;
390 conn.Open();
391 try
392 {
393 cmd.ExecuteNonQuery();
394 }
395 catch (Exception e)
396 {
397 m_log.ErrorFormat("[USER DB] Error removing userfriend, error: {0}", e.Message);
398 }
399 }
400 }
401
402 /// <summary>
403 /// Update friendlist permission flag for a friend
404 /// </summary>
405 /// <param name="friendlistowner">UUID of the friendlist owner</param>
406 /// <param name="friend">UUID of the friend</param>
407 /// <param name="perms">new permission flag</param>
408 override public void UpdateUserFriendPerms(UUID friendlistowner, UUID friend, uint perms)
409 {
410 string sql = string.Format(@"UPDATE {0} SET friendPerms = @friendPerms
411 WHERE ownerID = @ownerID
412 AND friendID = @friendID", m_userFriendsTableName);
413 using (SqlConnection conn = new SqlConnection(m_connectionString))
414 using (SqlCommand cmd = new SqlCommand(sql, conn))
415 {
416 cmd.Parameters.Add(database.CreateParameter("@ownerID", friendlistowner));
417 cmd.Parameters.Add(database.CreateParameter("@friendID", friend));
418 cmd.Parameters.Add(database.CreateParameter("@friendPerms", perms));
419 conn.Open();
420 try
421 {
422 cmd.ExecuteNonQuery();
423 }
424 catch (Exception e)
425 {
426 m_log.ErrorFormat("[USER DB] Error updating userfriend, error: {0}", e.Message);
427 }
428 }
429 }
430
431 /// <summary>
432 /// Get (fetch?) the user's friendlist
433 /// </summary>
434 /// <param name="friendlistowner">UUID of the friendlist owner</param>
435 /// <returns>Friendlist list</returns>
436 override public List<FriendListItem> GetUserFriendList(UUID friendlistowner)
437 {
438 List<FriendListItem> friendList = new List<FriendListItem>();
439
440 //Left Join userfriends to itself
441 string sql = string.Format(@"SELECT a.ownerID, a.friendID, a.friendPerms, b.friendPerms AS ownerperms
442 FROM {0} as a, {0} as b
443 WHERE a.ownerID = @ownerID
444 AND b.ownerID = a.friendID
445 AND b.friendID = a.ownerID", m_userFriendsTableName);
446 using (SqlConnection conn = new SqlConnection(m_connectionString))
447 using (SqlCommand cmd = new SqlCommand(sql, conn))
448 {
449 cmd.Parameters.Add(database.CreateParameter("@ownerID", friendlistowner));
450 conn.Open();
451 try
452 {
453 using (SqlDataReader reader = cmd.ExecuteReader())
454 {
455 while (reader.Read())
456 {
457 FriendListItem fli = new FriendListItem();
458 fli.FriendListOwner = new UUID((Guid)reader["ownerID"]);
459 fli.Friend = new UUID((Guid)reader["friendID"]);
460 fli.FriendPerms = (uint)Convert.ToInt32(reader["friendPerms"]);
461
462 // This is not a real column in the database table, it's a joined column from the opposite record
463 fli.FriendListOwnerPerms = (uint)Convert.ToInt32(reader["ownerperms"]);
464 friendList.Add(fli);
465 }
466 }
467 }
468 catch (Exception e)
469 {
470 m_log.ErrorFormat("[USER DB] Error updating userfriend, error: {0}", e.Message);
471 }
472 }
473 return friendList;
474 }
475
476 override public Dictionary<UUID, FriendRegionInfo> GetFriendRegionInfos(List<UUID> uuids)
477 {
478 Dictionary<UUID, FriendRegionInfo> infos = new Dictionary<UUID, FriendRegionInfo>();
479 try
480 {
481 foreach (UUID uuid in uuids)
482 {
483 string sql = string.Format(@"SELECT agentOnline,currentHandle
484 FROM {0} WHERE UUID = @uuid", m_agentsTableName);
485 using (SqlConnection conn = new SqlConnection(m_connectionString))
486 using (SqlCommand cmd = new SqlCommand(sql, conn))
487 {
488
489 cmd.Parameters.Add(database.CreateParameter("@uuid", uuid));
490 conn.Open();
491 using (SqlDataReader reader = cmd.ExecuteReader())
492 {
493 while (reader.Read())
494 {
495 FriendRegionInfo fri = new FriendRegionInfo();
496 fri.isOnline = (byte)reader["agentOnline"] != 0;
497 fri.regionHandle = Convert.ToUInt64(reader["currentHandle"].ToString());
498
499 infos[uuid] = fri;
500 }
501 }
502 }
503 }
504 }
505 catch (Exception e)
506 {
507 m_log.Warn("[MSSQL]: Got exception on trying to find friends regions:", e);
508 }
509
510 return infos;
511 }
512 #endregion
513
514 #region Money functions (not used)
515
516 /// <summary>
517 /// Performs a money transfer request between two accounts
518 /// </summary>
519 /// <param name="from">The senders account ID</param>
520 /// <param name="to">The receivers account ID</param>
521 /// <param name="amount">The amount to transfer</param>
522 /// <returns>false</returns>
523 override public bool MoneyTransferRequest(UUID from, UUID to, uint amount)
524 {
525 return false;
526 }
527
528 /// <summary>
529 /// Performs an inventory transfer request between two accounts
530 /// </summary>
531 /// <remarks>TODO: Move to inventory server</remarks>
532 /// <param name="from">The senders account ID</param>
533 /// <param name="to">The receivers account ID</param>
534 /// <param name="item">The item to transfer</param>
535 /// <returns>false</returns>
536 override public bool InventoryTransferRequest(UUID from, UUID to, UUID item)
537 {
538 return false;
539 }
540
541 #endregion
542
543 #region Appearance methods
544
545 /// <summary>
546 /// Gets the user appearance.
547 /// </summary>
548 /// <param name="user">The user.</param>
549 /// <returns></returns>
550 override public AvatarAppearance GetUserAppearance(UUID user)
551 {
552 try
553 {
554 AvatarAppearance appearance = new AvatarAppearance();
555 string sql = "SELECT * FROM avatarappearance WHERE owner = @UUID";
556 using (SqlConnection conn = new SqlConnection(m_connectionString))
557 using (SqlCommand cmd = new SqlCommand(sql, conn))
558 {
559
560 cmd.Parameters.Add(database.CreateParameter("@UUID", user));
561 conn.Open();
562 using (SqlDataReader reader = cmd.ExecuteReader())
563 {
564 if (reader.Read())
565 appearance = readUserAppearance(reader);
566 else
567 {
568 m_log.WarnFormat("[USER DB] No appearance found for user {0}", user.ToString());
569 return null;
570 }
571
572 }
573 }
574
575 appearance.SetAttachments(GetUserAttachments(user));
576
577 return appearance;
578 }
579 catch (Exception e)
580 {
581 m_log.ErrorFormat("[USER DB] Error updating userfriend, error: {0}", e.Message);
582 }
583 return null;
584 }
585
586 /// <summary>
587 /// Update a user appearence into database
588 /// </summary>
589 /// <param name="user">the used UUID</param>
590 /// <param name="appearance">the appearence</param>
591 override public void UpdateUserAppearance(UUID user, AvatarAppearance appearance)
592 {
593 string sql = @"DELETE FROM avatarappearance WHERE owner=@owner;
594 INSERT INTO avatarappearance
595 (owner, serial, visual_params, texture, avatar_height,
596 body_item, body_asset, skin_item, skin_asset, hair_item,
597 hair_asset, eyes_item, eyes_asset, shirt_item, shirt_asset,
598 pants_item, pants_asset, shoes_item, shoes_asset, socks_item,
599 socks_asset, jacket_item, jacket_asset, gloves_item, gloves_asset,
600 undershirt_item, undershirt_asset, underpants_item, underpants_asset,
601 skirt_item, skirt_asset)
602 VALUES
603 (@owner, @serial, @visual_params, @texture, @avatar_height,
604 @body_item, @body_asset, @skin_item, @skin_asset, @hair_item,
605 @hair_asset, @eyes_item, @eyes_asset, @shirt_item, @shirt_asset,
606 @pants_item, @pants_asset, @shoes_item, @shoes_asset, @socks_item,
607 @socks_asset, @jacket_item, @jacket_asset, @gloves_item, @gloves_asset,
608 @undershirt_item, @undershirt_asset, @underpants_item, @underpants_asset,
609 @skirt_item, @skirt_asset)";
610
611 using (SqlConnection conn = new SqlConnection(m_connectionString))
612 using (SqlCommand cmd = new SqlCommand(sql, conn))
613 {
614 cmd.Parameters.Add(database.CreateParameter("@owner", appearance.Owner));
615 cmd.Parameters.Add(database.CreateParameter("@serial", appearance.Serial));
616 cmd.Parameters.Add(database.CreateParameter("@visual_params", appearance.VisualParams));
617 cmd.Parameters.Add(database.CreateParameter("@texture", appearance.Texture.GetBytes()));
618 cmd.Parameters.Add(database.CreateParameter("@avatar_height", appearance.AvatarHeight));
619 cmd.Parameters.Add(database.CreateParameter("@body_item", appearance.BodyItem));
620 cmd.Parameters.Add(database.CreateParameter("@body_asset", appearance.BodyAsset));
621 cmd.Parameters.Add(database.CreateParameter("@skin_item", appearance.SkinItem));
622 cmd.Parameters.Add(database.CreateParameter("@skin_asset", appearance.SkinAsset));
623 cmd.Parameters.Add(database.CreateParameter("@hair_item", appearance.HairItem));
624 cmd.Parameters.Add(database.CreateParameter("@hair_asset", appearance.HairAsset));
625 cmd.Parameters.Add(database.CreateParameter("@eyes_item", appearance.EyesItem));
626 cmd.Parameters.Add(database.CreateParameter("@eyes_asset", appearance.EyesAsset));
627 cmd.Parameters.Add(database.CreateParameter("@shirt_item", appearance.ShirtItem));
628 cmd.Parameters.Add(database.CreateParameter("@shirt_asset", appearance.ShirtAsset));
629 cmd.Parameters.Add(database.CreateParameter("@pants_item", appearance.PantsItem));
630 cmd.Parameters.Add(database.CreateParameter("@pants_asset", appearance.PantsAsset));
631 cmd.Parameters.Add(database.CreateParameter("@shoes_item", appearance.ShoesItem));
632 cmd.Parameters.Add(database.CreateParameter("@shoes_asset", appearance.ShoesAsset));
633 cmd.Parameters.Add(database.CreateParameter("@socks_item", appearance.SocksItem));
634 cmd.Parameters.Add(database.CreateParameter("@socks_asset", appearance.SocksAsset));
635 cmd.Parameters.Add(database.CreateParameter("@jacket_item", appearance.JacketItem));
636 cmd.Parameters.Add(database.CreateParameter("@jacket_asset", appearance.JacketAsset));
637 cmd.Parameters.Add(database.CreateParameter("@gloves_item", appearance.GlovesItem));
638 cmd.Parameters.Add(database.CreateParameter("@gloves_asset", appearance.GlovesAsset));
639 cmd.Parameters.Add(database.CreateParameter("@undershirt_item", appearance.UnderShirtItem));
640 cmd.Parameters.Add(database.CreateParameter("@undershirt_asset", appearance.UnderShirtAsset));
641 cmd.Parameters.Add(database.CreateParameter("@underpants_item", appearance.UnderPantsItem));
642 cmd.Parameters.Add(database.CreateParameter("@underpants_asset", appearance.UnderPantsAsset));
643 cmd.Parameters.Add(database.CreateParameter("@skirt_item", appearance.SkirtItem));
644 cmd.Parameters.Add(database.CreateParameter("@skirt_asset", appearance.SkirtAsset));
645 conn.Open();
646 try
647 {
648 cmd.ExecuteNonQuery();
649 }
650 catch (Exception e)
651 {
652 m_log.ErrorFormat("[USER DB] Error updating user appearance, error: {0}", e.Message);
653 }
654 }
655 UpdateUserAttachments(user, appearance.GetAttachments());
656 }
657
658 #endregion
659
660 #region Attachment methods
661
662 /// <summary>
663 /// Gets all attachment of a agent.
664 /// </summary>
665 /// <param name="agentID">agent ID.</param>
666 /// <returns></returns>
667 public Hashtable GetUserAttachments(UUID agentID)
668 {
669 Hashtable returnTable = new Hashtable();
670 string sql = "select attachpoint, item, asset from avatarattachments where UUID = @uuid";
671 using (SqlConnection conn = new SqlConnection(m_connectionString))
672 using (SqlCommand cmd = new SqlCommand(sql, conn))
673 {
674 cmd.Parameters.Add(database.CreateParameter("@uuid", agentID));
675 conn.Open();
676 using (SqlDataReader reader = cmd.ExecuteReader())
677 {
678 while (reader.Read())
679 {
680 int attachpoint = Convert.ToInt32(reader["attachpoint"]);
681 if (returnTable.ContainsKey(attachpoint))
682 continue;
683 Hashtable item = new Hashtable();
684 item.Add("item", reader["item"].ToString());
685 item.Add("asset", reader["asset"].ToString());
686
687 returnTable.Add(attachpoint, item);
688 }
689 }
690 }
691 return returnTable;
692 }
693
694 /// <summary>
695 /// Updates all attachments of the agent.
696 /// </summary>
697 /// <param name="agentID">agentID.</param>
698 /// <param name="data">data with all items on attachmentpoints</param>
699 public void UpdateUserAttachments(UUID agentID, Hashtable data)
700 {
701 string sql = "DELETE FROM avatarattachments WHERE UUID = @uuid";
702
703 using (SqlConnection conn = new SqlConnection(m_connectionString))
704 using (SqlCommand cmd = new SqlCommand(sql, conn))
705 {
706 cmd.Parameters.Add(database.CreateParameter("uuid", agentID));
707 conn.Open();
708 cmd.ExecuteNonQuery();
709 }
710 if (data == null)
711 return;
712
713 sql = @"INSERT INTO avatarattachments (UUID, attachpoint, item, asset)
714 VALUES (@uuid, @attachpoint, @item, @asset)";
715
716 using (SqlConnection conn = new SqlConnection(m_connectionString))
717 using (SqlCommand cmd = new SqlCommand(sql, conn))
718 {
719 bool firstTime = true;
720 foreach (DictionaryEntry e in data)
721 {
722 int attachpoint = Convert.ToInt32(e.Key);
723
724 Hashtable item = (Hashtable)e.Value;
725
726 if (firstTime)
727 {
728 cmd.Parameters.Add(database.CreateParameter("@uuid", agentID));
729 cmd.Parameters.Add(database.CreateParameter("@attachpoint", attachpoint));
730 cmd.Parameters.Add(database.CreateParameter("@item", new UUID(item["item"].ToString())));
731 cmd.Parameters.Add(database.CreateParameter("@asset", new UUID(item["asset"].ToString())));
732 firstTime = false;
733 }
734 cmd.Parameters["@uuid"].Value = agentID.Guid; //.ToString();
735 cmd.Parameters["@attachpoint"].Value = attachpoint;
736 cmd.Parameters["@item"].Value = new Guid(item["item"].ToString());
737 cmd.Parameters["@asset"].Value = new Guid(item["asset"].ToString());
738
739 try
740 {
741 conn.Open();
742 cmd.ExecuteNonQuery();
743 }
744 catch (Exception ex)
745 {
746 m_log.DebugFormat("[USER DB] : Error adding user attachment. {0}", ex.Message);
747 }
748 }
749 }
750 }
751
752 /// <summary>
753 /// Resets all attachments of a agent in the database.
754 /// </summary>
755 /// <param name="agentID">agentID.</param>
756 override public void ResetAttachments(UUID agentID)
757 {
758 string sql = "UPDATE avatarattachments SET asset = '00000000-0000-0000-0000-000000000000' WHERE UUID = @uuid";
759 using (SqlConnection conn = new SqlConnection(m_connectionString))
760 using (SqlCommand cmd = new SqlCommand(sql, conn))
761 {
762 cmd.Parameters.Add(database.CreateParameter("uuid", agentID));
763 conn.Open();
764 cmd.ExecuteNonQuery();
765 }
766 }
767
768 override public void LogoutUsers(UUID regionID)
769 {
770 }
771
772 #endregion
773
774 #region Other public methods
775
776 /// <summary>
777 ///
778 /// </summary>
779 /// <param name="queryID"></param>
780 /// <param name="query"></param>
781 /// <returns></returns>
782 override public List<AvatarPickerAvatar> GeneratePickerResults(UUID queryID, string query)
783 {
784 List<AvatarPickerAvatar> returnlist = new List<AvatarPickerAvatar>();
785 string[] querysplit = query.Split(' ');
786 if (querysplit.Length == 2)
787 {
788 try
789 {
790 string sql = string.Format(@"SELECT UUID,username,lastname FROM {0}
791 WHERE username LIKE @first AND lastname LIKE @second", m_usersTableName);
792 using (SqlConnection conn = new SqlConnection(m_connectionString))
793 using (SqlCommand cmd = new SqlCommand(sql, conn))
794 {
795 //Add wildcard to the search
796 cmd.Parameters.Add(database.CreateParameter("first", querysplit[0] + "%"));
797 cmd.Parameters.Add(database.CreateParameter("second", querysplit[1] + "%"));
798 conn.Open();
799 using (SqlDataReader reader = cmd.ExecuteReader())
800 {
801 while (reader.Read())
802 {
803 AvatarPickerAvatar user = new AvatarPickerAvatar();
804 user.AvatarID = new UUID((Guid)reader["UUID"]);
805 user.firstName = (string)reader["username"];
806 user.lastName = (string)reader["lastname"];
807 returnlist.Add(user);
808 }
809 }
810 }
811 }
812 catch (Exception e)
813 {
814 m_log.Error(e.ToString());
815 }
816 }
817 else if (querysplit.Length == 1)
818 {
819 try
820 {
821 string sql = string.Format(@"SELECT UUID,username,lastname FROM {0}
822 WHERE username LIKE @first OR lastname LIKE @first", m_usersTableName);
823 using (SqlConnection conn = new SqlConnection(m_connectionString))
824 using (SqlCommand cmd = new SqlCommand(sql, conn))
825 {
826 cmd.Parameters.Add(database.CreateParameter("first", querysplit[0] + "%"));
827 conn.Open();
828 using (SqlDataReader reader = cmd.ExecuteReader())
829 {
830 while (reader.Read())
831 {
832 AvatarPickerAvatar user = new AvatarPickerAvatar();
833 user.AvatarID = new UUID((Guid)reader["UUID"]);
834 user.firstName = (string)reader["username"];
835 user.lastName = (string)reader["lastname"];
836 returnlist.Add(user);
837 }
838 }
839 }
840 }
841 catch (Exception e)
842 {
843 m_log.Error(e.ToString());
844 }
845 }
846 return returnlist;
847 }
848
849 /// <summary>
850 /// Store a weblogin key
851 /// </summary>
852 /// <param name="AgentID">The agent UUID</param>
853 /// <param name="WebLoginKey">the WebLogin Key</param>
854 /// <remarks>unused ?</remarks>
855 override public void StoreWebLoginKey(UUID AgentID, UUID WebLoginKey)
856 {
857 UserProfileData user = GetUserByUUID(AgentID);
858 user.WebLoginKey = WebLoginKey;
859 UpdateUserProfile(user);
860 }
861
862 /// <summary>
863 /// Database provider name
864 /// </summary>
865 /// <returns>Provider name</returns>
866 override public string Name
867 {
868 get { return "MSSQL Userdata Interface"; }
869 }
870
871 /// <summary>
872 /// Database provider version
873 /// </summary>
874 /// <returns>provider version</returns>
875 override public string Version
876 {
877 get { return database.getVersion(); }
878 }
879
880 #endregion
881
882 #region Private functions
883
884 /// <summary>
885 /// Reads a one item from an SQL result
886 /// </summary>
887 /// <param name="reader">The SQL Result</param>
888 /// <returns>the item read</returns>
889 private static AvatarAppearance readUserAppearance(SqlDataReader reader)
890 {
891 try
892 {
893 AvatarAppearance appearance = new AvatarAppearance();
894
895 appearance.Owner = new UUID((Guid)reader["owner"]);
896 appearance.Serial = Convert.ToInt32(reader["serial"]);
897 appearance.VisualParams = (byte[])reader["visual_params"];
898 appearance.Texture = new Primitive.TextureEntry((byte[])reader["texture"], 0, ((byte[])reader["texture"]).Length);
899 appearance.AvatarHeight = (float)Convert.ToDouble(reader["avatar_height"]);
900 appearance.BodyItem = new UUID((Guid)reader["body_item"]);
901 appearance.BodyAsset = new UUID((Guid)reader["body_asset"]);
902 appearance.SkinItem = new UUID((Guid)reader["skin_item"]);
903 appearance.SkinAsset = new UUID((Guid)reader["skin_asset"]);
904 appearance.HairItem = new UUID((Guid)reader["hair_item"]);
905 appearance.HairAsset = new UUID((Guid)reader["hair_asset"]);
906 appearance.EyesItem = new UUID((Guid)reader["eyes_item"]);
907 appearance.EyesAsset = new UUID((Guid)reader["eyes_asset"]);
908 appearance.ShirtItem = new UUID((Guid)reader["shirt_item"]);
909 appearance.ShirtAsset = new UUID((Guid)reader["shirt_asset"]);
910 appearance.PantsItem = new UUID((Guid)reader["pants_item"]);
911 appearance.PantsAsset = new UUID((Guid)reader["pants_asset"]);
912 appearance.ShoesItem = new UUID((Guid)reader["shoes_item"]);
913 appearance.ShoesAsset = new UUID((Guid)reader["shoes_asset"]);
914 appearance.SocksItem = new UUID((Guid)reader["socks_item"]);
915 appearance.SocksAsset = new UUID((Guid)reader["socks_asset"]);
916 appearance.JacketItem = new UUID((Guid)reader["jacket_item"]);
917 appearance.JacketAsset = new UUID((Guid)reader["jacket_asset"]);
918 appearance.GlovesItem = new UUID((Guid)reader["gloves_item"]);
919 appearance.GlovesAsset = new UUID((Guid)reader["gloves_asset"]);
920 appearance.UnderShirtItem = new UUID((Guid)reader["undershirt_item"]);
921 appearance.UnderShirtAsset = new UUID((Guid)reader["undershirt_asset"]);
922 appearance.UnderPantsItem = new UUID((Guid)reader["underpants_item"]);
923 appearance.UnderPantsAsset = new UUID((Guid)reader["underpants_asset"]);
924 appearance.SkirtItem = new UUID((Guid)reader["skirt_item"]);
925 appearance.SkirtAsset = new UUID((Guid)reader["skirt_asset"]);
926
927 return appearance;
928 }
929 catch (SqlException e)
930 {
931 m_log.Error(e.ToString());
932 }
933
934 return null;
935 }
936
937 /// <summary>
938 /// Insert/Update a agent row in the DB.
939 /// </summary>
940 /// <param name="agentdata">agentdata.</param>
941 private void InsertUpdateAgentRow(UserAgentData agentdata)
942 {
943 string sql = @"
944
945IF EXISTS (SELECT * FROM agents WHERE UUID = @UUID)
946 BEGIN
947 UPDATE agents SET UUID = @UUID, sessionID = @sessionID, secureSessionID = @secureSessionID, agentIP = @agentIP, agentPort = @agentPort, agentOnline = @agentOnline, loginTime = @loginTime, logoutTime = @logoutTime, currentRegion = @currentRegion, currentHandle = @currentHandle, currentPos = @currentPos
948 WHERE UUID = @UUID
949 END
950ELSE
951 BEGIN
952 INSERT INTO
953 agents (UUID, sessionID, secureSessionID, agentIP, agentPort, agentOnline, loginTime, logoutTime, currentRegion, currentHandle, currentPos) VALUES
954 (@UUID, @sessionID, @secureSessionID, @agentIP, @agentPort, @agentOnline, @loginTime, @logoutTime, @currentRegion, @currentHandle, @currentPos)
955 END
956";
957
958 using (SqlConnection conn = new SqlConnection(m_connectionString))
959 using (SqlCommand command = new SqlCommand(sql, conn))
960 {
961 command.Parameters.Add(database.CreateParameter("@UUID", agentdata.ProfileID));
962 command.Parameters.Add(database.CreateParameter("@sessionID", agentdata.SessionID));
963 command.Parameters.Add(database.CreateParameter("@secureSessionID", agentdata.SecureSessionID));
964 command.Parameters.Add(database.CreateParameter("@agentIP", agentdata.AgentIP));
965 command.Parameters.Add(database.CreateParameter("@agentPort", agentdata.AgentPort));
966 command.Parameters.Add(database.CreateParameter("@agentOnline", agentdata.AgentOnline));
967 command.Parameters.Add(database.CreateParameter("@loginTime", agentdata.LoginTime));
968 command.Parameters.Add(database.CreateParameter("@logoutTime", agentdata.LogoutTime));
969 command.Parameters.Add(database.CreateParameter("@currentRegion", agentdata.Region));
970 command.Parameters.Add(database.CreateParameter("@currentHandle", agentdata.Handle));
971 command.Parameters.Add(database.CreateParameter("@currentPos", "<" + ((int)agentdata.Position.X) + "," + ((int)agentdata.Position.Y) + "," + ((int)agentdata.Position.Z) + ">"));
972 conn.Open();
973
974 command.Transaction = command.Connection.BeginTransaction(IsolationLevel.Serializable);
975 try
976 {
977 if (command.ExecuteNonQuery() > 0)
978 {
979 command.Transaction.Commit();
980 return;
981 }
982
983 command.Transaction.Rollback();
984 return;
985 }
986 catch (Exception e)
987 {
988 command.Transaction.Rollback();
989 m_log.Error(e.ToString());
990 return;
991 }
992 }
993
994 }
995
996 /// <summary>
997 /// Reads an agent row from a database reader
998 /// </summary>
999 /// <param name="reader">An active database reader</param>
1000 /// <returns>A user session agent</returns>
1001 private UserAgentData readAgentRow(SqlDataReader reader)
1002 {
1003 UserAgentData retval = new UserAgentData();
1004
1005 if (reader.Read())
1006 {
1007 // Agent IDs
1008 retval.ProfileID = new UUID((Guid)reader["UUID"]);
1009 retval.SessionID = new UUID((Guid)reader["sessionID"]);
1010 retval.SecureSessionID = new UUID((Guid)reader["secureSessionID"]);
1011
1012 // Agent Who?
1013 retval.AgentIP = (string)reader["agentIP"];
1014 retval.AgentPort = Convert.ToUInt32(reader["agentPort"].ToString());
1015 retval.AgentOnline = Convert.ToInt32(reader["agentOnline"].ToString()) != 0;
1016
1017 // Login/Logout times (UNIX Epoch)
1018 retval.LoginTime = Convert.ToInt32(reader["loginTime"].ToString());
1019 retval.LogoutTime = Convert.ToInt32(reader["logoutTime"].ToString());
1020
1021 // Current position
1022 retval.Region = new UUID((Guid)reader["currentRegion"]);
1023 retval.Handle = Convert.ToUInt64(reader["currentHandle"].ToString());
1024 Vector3 tmp_v;
1025 Vector3.TryParse((string)reader["currentPos"], out tmp_v);
1026 retval.Position = tmp_v;
1027
1028 }
1029 else
1030 {
1031 return null;
1032 }
1033 return retval;
1034 }
1035
1036 /// <summary>
1037 /// Creates a new user and inserts it into the database
1038 /// </summary>
1039 /// <param name="uuid">User ID</param>
1040 /// <param name="username">First part of the login</param>
1041 /// <param name="lastname">Second part of the login</param>
1042 /// <param name="email">Email of person</param>
1043 /// <param name="passwordHash">A salted hash of the users password</param>
1044 /// <param name="passwordSalt">The salt used for the password hash</param>
1045 /// <param name="homeRegion">A regionHandle of the users home region</param>
1046 /// <param name="homeLocX">Home region position vector</param>
1047 /// <param name="homeLocY">Home region position vector</param>
1048 /// <param name="homeLocZ">Home region position vector</param>
1049 /// <param name="homeLookAtX">Home region 'look at' vector</param>
1050 /// <param name="homeLookAtY">Home region 'look at' vector</param>
1051 /// <param name="homeLookAtZ">Home region 'look at' vector</param>
1052 /// <param name="created">Account created (unix timestamp)</param>
1053 /// <param name="lastlogin">Last login (unix timestamp)</param>
1054 /// <param name="inventoryURI">Users inventory URI</param>
1055 /// <param name="assetURI">Users asset URI</param>
1056 /// <param name="canDoMask">I can do mask</param>
1057 /// <param name="wantDoMask">I want to do mask</param>
1058 /// <param name="aboutText">Profile text</param>
1059 /// <param name="firstText">Firstlife text</param>
1060 /// <param name="profileImage">UUID for profile image</param>
1061 /// <param name="firstImage">UUID for firstlife image</param>
1062 /// <param name="webLoginKey">web login key</param>
1063 /// <param name="homeRegionID">homeregion UUID</param>
1064 /// <param name="godLevel">has the user godlevel</param>
1065 /// <param name="userFlags">unknown</param>
1066 /// <param name="customType">unknown</param>
1067 /// <param name="partnerID">UUID of partner</param>
1068 /// <returns>Success?</returns>
1069 private void InsertUserRow(UUID uuid, string username, string lastname, string email, string passwordHash,
1070 string passwordSalt, UInt64 homeRegion, float homeLocX, float homeLocY, float homeLocZ,
1071 float homeLookAtX, float homeLookAtY, float homeLookAtZ, int created, int lastlogin,
1072 string inventoryURI, string assetURI, uint canDoMask, uint wantDoMask,
1073 string aboutText, string firstText,
1074 UUID profileImage, UUID firstImage, UUID webLoginKey, UUID homeRegionID,
1075 int godLevel, int userFlags, string customType, UUID partnerID)
1076 {
1077 string sql = string.Format(@"INSERT INTO {0}
1078 ([UUID], [username], [lastname], [email], [passwordHash], [passwordSalt],
1079 [homeRegion], [homeLocationX], [homeLocationY], [homeLocationZ], [homeLookAtX],
1080 [homeLookAtY], [homeLookAtZ], [created], [lastLogin], [userInventoryURI],
1081 [userAssetURI], [profileCanDoMask], [profileWantDoMask], [profileAboutText],
1082 [profileFirstText], [profileImage], [profileFirstImage], [webLoginKey],
1083 [homeRegionID], [userFlags], [godLevel], [customType], [partner])
1084 VALUES
1085 (@UUID, @username, @lastname, @email, @passwordHash, @passwordSalt,
1086 @homeRegion, @homeLocationX, @homeLocationY, @homeLocationZ, @homeLookAtX,
1087 @homeLookAtY, @homeLookAtZ, @created, @lastLogin, @userInventoryURI,
1088 @userAssetURI, @profileCanDoMask, @profileWantDoMask, @profileAboutText,
1089 @profileFirstText, @profileImage, @profileFirstImage, @webLoginKey,
1090 @homeRegionID, @userFlags, @godLevel, @customType, @partner)", m_usersTableName);
1091
1092 try
1093 {
1094 using (SqlConnection conn = new SqlConnection(m_connectionString))
1095 using (SqlCommand command = new SqlCommand(sql, conn))
1096 {
1097 command.Parameters.Add(database.CreateParameter("UUID", uuid));
1098 command.Parameters.Add(database.CreateParameter("username", username));
1099 command.Parameters.Add(database.CreateParameter("lastname", lastname));
1100 command.Parameters.Add(database.CreateParameter("email", email));
1101 command.Parameters.Add(database.CreateParameter("passwordHash", passwordHash));
1102 command.Parameters.Add(database.CreateParameter("passwordSalt", passwordSalt));
1103 command.Parameters.Add(database.CreateParameter("homeRegion", homeRegion));
1104 command.Parameters.Add(database.CreateParameter("homeLocationX", homeLocX));
1105 command.Parameters.Add(database.CreateParameter("homeLocationY", homeLocY));
1106 command.Parameters.Add(database.CreateParameter("homeLocationZ", homeLocZ));
1107 command.Parameters.Add(database.CreateParameter("homeLookAtX", homeLookAtX));
1108 command.Parameters.Add(database.CreateParameter("homeLookAtY", homeLookAtY));
1109 command.Parameters.Add(database.CreateParameter("homeLookAtZ", homeLookAtZ));
1110 command.Parameters.Add(database.CreateParameter("created", created));
1111 command.Parameters.Add(database.CreateParameter("lastLogin", lastlogin));
1112 command.Parameters.Add(database.CreateParameter("userInventoryURI", inventoryURI));
1113 command.Parameters.Add(database.CreateParameter("userAssetURI", assetURI));
1114 command.Parameters.Add(database.CreateParameter("profileCanDoMask", canDoMask));
1115 command.Parameters.Add(database.CreateParameter("profileWantDoMask", wantDoMask));
1116 command.Parameters.Add(database.CreateParameter("profileAboutText", aboutText));
1117 command.Parameters.Add(database.CreateParameter("profileFirstText", firstText));
1118 command.Parameters.Add(database.CreateParameter("profileImage", profileImage));
1119 command.Parameters.Add(database.CreateParameter("profileFirstImage", firstImage));
1120 command.Parameters.Add(database.CreateParameter("webLoginKey", webLoginKey));
1121 command.Parameters.Add(database.CreateParameter("homeRegionID", homeRegionID));
1122 command.Parameters.Add(database.CreateParameter("userFlags", userFlags));
1123 command.Parameters.Add(database.CreateParameter("godLevel", godLevel));
1124 command.Parameters.Add(database.CreateParameter("customType", customType));
1125 command.Parameters.Add(database.CreateParameter("partner", partnerID));
1126 conn.Open();
1127 command.ExecuteNonQuery();
1128 return;
1129 }
1130 }
1131 catch (Exception e)
1132 {
1133 m_log.Error(e.ToString());
1134 return;
1135 }
1136 }
1137
1138 /// <summary>
1139 /// Reads a user profile from an active data reader
1140 /// </summary>
1141 /// <param name="reader">An active database reader</param>
1142 /// <returns>A user profile</returns>
1143 private static UserProfileData ReadUserRow(SqlDataReader reader)
1144 {
1145 UserProfileData retval = new UserProfileData();
1146
1147 if (reader.Read())
1148 {
1149 retval.ID = new UUID((Guid)reader["UUID"]);
1150 retval.FirstName = (string)reader["username"];
1151 retval.SurName = (string)reader["lastname"];
1152 if (reader.IsDBNull(reader.GetOrdinal("email")))
1153 retval.Email = "";
1154 else
1155 retval.Email = (string)reader["email"];
1156
1157 retval.PasswordHash = (string)reader["passwordHash"];
1158 retval.PasswordSalt = (string)reader["passwordSalt"];
1159
1160 retval.HomeRegion = Convert.ToUInt64(reader["homeRegion"].ToString());
1161 retval.HomeLocation = new Vector3(
1162 Convert.ToSingle(reader["homeLocationX"].ToString()),
1163 Convert.ToSingle(reader["homeLocationY"].ToString()),
1164 Convert.ToSingle(reader["homeLocationZ"].ToString()));
1165 retval.HomeLookAt = new Vector3(
1166 Convert.ToSingle(reader["homeLookAtX"].ToString()),
1167 Convert.ToSingle(reader["homeLookAtY"].ToString()),
1168 Convert.ToSingle(reader["homeLookAtZ"].ToString()));
1169
1170 if (reader.IsDBNull(reader.GetOrdinal("homeRegionID")))
1171 retval.HomeRegionID = UUID.Zero;
1172 else
1173 retval.HomeRegionID = new UUID((Guid)reader["homeRegionID"]);
1174
1175 retval.Created = Convert.ToInt32(reader["created"].ToString());
1176 retval.LastLogin = Convert.ToInt32(reader["lastLogin"].ToString());
1177
1178 if (reader.IsDBNull(reader.GetOrdinal("userInventoryURI")))
1179 retval.UserInventoryURI = "";
1180 else
1181 retval.UserInventoryURI = (string)reader["userInventoryURI"];
1182
1183 if (reader.IsDBNull(reader.GetOrdinal("userAssetURI")))
1184 retval.UserAssetURI = "";
1185 else
1186 retval.UserAssetURI = (string)reader["userAssetURI"];
1187
1188 retval.CanDoMask = Convert.ToUInt32(reader["profileCanDoMask"].ToString());
1189 retval.WantDoMask = Convert.ToUInt32(reader["profileWantDoMask"].ToString());
1190
1191
1192 if (reader.IsDBNull(reader.GetOrdinal("profileAboutText")))
1193 retval.AboutText = "";
1194 else
1195 retval.AboutText = (string)reader["profileAboutText"];
1196
1197 if (reader.IsDBNull(reader.GetOrdinal("profileFirstText")))
1198 retval.FirstLifeAboutText = "";
1199 else
1200 retval.FirstLifeAboutText = (string)reader["profileFirstText"];
1201
1202 if (reader.IsDBNull(reader.GetOrdinal("profileImage")))
1203 retval.Image = UUID.Zero;
1204 else
1205 retval.Image = new UUID((Guid)reader["profileImage"]);
1206
1207 if (reader.IsDBNull(reader.GetOrdinal("profileFirstImage")))
1208 retval.Image = UUID.Zero;
1209 else
1210 retval.FirstLifeImage = new UUID((Guid)reader["profileFirstImage"]);
1211
1212 if (reader.IsDBNull(reader.GetOrdinal("webLoginKey")))
1213 retval.WebLoginKey = UUID.Zero;
1214 else
1215 retval.WebLoginKey = new UUID((Guid)reader["webLoginKey"]);
1216
1217 retval.UserFlags = Convert.ToInt32(reader["userFlags"].ToString());
1218 retval.GodLevel = Convert.ToInt32(reader["godLevel"].ToString());
1219 if (reader.IsDBNull(reader.GetOrdinal("customType")))
1220 retval.CustomType = "";
1221 else
1222 retval.CustomType = reader["customType"].ToString();
1223
1224 if (reader.IsDBNull(reader.GetOrdinal("partner")))
1225 retval.Partner = UUID.Zero;
1226 else
1227 retval.Partner = new UUID((Guid)reader["partner"]);
1228 }
1229 else
1230 {
1231 return null;
1232 }
1233 return retval;
1234 }
1235 #endregion
1236 }
1237
1238}
diff --git a/OpenSim/Data/MSSQL/MSSQLXInventoryData.cs b/OpenSim/Data/MSSQL/MSSQLXInventoryData.cs
new file mode 100644
index 0000000..739eb55
--- /dev/null
+++ b/OpenSim/Data/MSSQL/MSSQLXInventoryData.cs
@@ -0,0 +1,166 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ''AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections;
30using System.Collections.Generic;
31using System.Data;
32using OpenMetaverse;
33using OpenSim.Framework;
34using System.Data.SqlClient;
35using System.Reflection;
36using System.Text;
37using log4net;
38
39namespace OpenSim.Data.MSSQL
40{
41 public class MSSQLXInventoryData : IXInventoryData
42 {
43 private static readonly ILog m_log = LogManager.GetLogger(
44 MethodBase.GetCurrentMethod().DeclaringType);
45
46 private MSSQLGenericTableHandler<XInventoryFolder> m_Folders;
47 private MSSQLItemHandler m_Items;
48
49 public MSSQLXInventoryData(string conn, string realm)
50 {
51 m_Folders = new MSSQLGenericTableHandler<XInventoryFolder>(
52 conn, "inventoryfolders", "InventoryStore");
53 m_Items = new MSSQLItemHandler(
54 conn, "inventoryitems", String.Empty);
55 }
56
57 public XInventoryFolder[] GetFolders(string[] fields, string[] vals)
58 {
59 return m_Folders.Get(fields, vals);
60 }
61
62 public XInventoryItem[] GetItems(string[] fields, string[] vals)
63 {
64 return m_Items.Get(fields, vals);
65 }
66
67 public bool StoreFolder(XInventoryFolder folder)
68 {
69 return m_Folders.Store(folder);
70 }
71
72 public bool StoreItem(XInventoryItem item)
73 {
74 return m_Items.Store(item);
75 }
76
77 public bool DeleteFolders(string field, string val)
78 {
79 return m_Folders.Delete(field, val);
80 }
81
82 public bool DeleteItems(string field, string val)
83 {
84 return m_Items.Delete(field, val);
85 }
86
87 public bool MoveItem(string id, string newParent)
88 {
89 return m_Items.MoveItem(id, newParent);
90 }
91
92 public XInventoryItem[] GetActiveGestures(UUID principalID)
93 {
94 return m_Items.GetActiveGestures(principalID);
95 }
96
97 public int GetAssetPermissions(UUID principalID, UUID assetID)
98 {
99 return m_Items.GetAssetPermissions(principalID, assetID);
100 }
101 }
102
103 public class MSSQLItemHandler : MSSQLGenericTableHandler<XInventoryItem>
104 {
105 public MSSQLItemHandler(string c, string t, string m) :
106 base(c, t, m)
107 {
108 }
109
110 public bool MoveItem(string id, string newParent)
111 {
112 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
113 using (SqlCommand cmd = new SqlCommand())
114 {
115
116 cmd.CommandText = String.Format("update {0} set parentFolderID = @ParentFolderID where inventoryID = @InventoryID", m_Realm);
117 cmd.Parameters.Add(m_database.CreateParameter("@ParentFolderID", newParent));
118 cmd.Parameters.Add(m_database.CreateParameter("@InventoryID", id));
119 cmd.Connection = conn;
120 conn.Open();
121 return cmd.ExecuteNonQuery() == 0 ? false : true;
122 }
123 }
124
125 public XInventoryItem[] GetActiveGestures(UUID principalID)
126 {
127 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
128 using (SqlCommand cmd = new SqlCommand())
129 {
130 cmd.CommandText = String.Format("select * from inventoryitems where avatarId = @uuid and assetType = @type and flags = 1", m_Realm);
131
132 cmd.Parameters.Add(m_database.CreateParameter("@uuid", principalID.ToString()));
133 cmd.Parameters.Add(m_database.CreateParameter("@type", (int)AssetType.Gesture));
134 cmd.Connection = conn;
135 conn.Open();
136 return DoQuery(cmd);
137 }
138 }
139
140 public int GetAssetPermissions(UUID principalID, UUID assetID)
141 {
142 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
143 using (SqlCommand cmd = new SqlCommand())
144 {
145 cmd.CommandText = String.Format("select bit_or(inventoryCurrentPermissions) as inventoryCurrentPermissions from inventoryitems where avatarID = @PrincipalID and assetID = @AssetID group by assetID", m_Realm);
146 cmd.Parameters.Add(m_database.CreateParameter("@PrincipalID", principalID.ToString()));
147 cmd.Parameters.Add(m_database.CreateParameter("@AssetID", assetID.ToString()));
148 cmd.Connection = conn;
149 conn.Open();
150 using (SqlDataReader reader = cmd.ExecuteReader())
151 {
152
153 int perms = 0;
154
155 if (reader.Read())
156 {
157 perms = Convert.ToInt32(reader["inventoryCurrentPermissions"]);
158 }
159
160 return perms;
161 }
162
163 }
164 }
165 }
166}
diff --git a/OpenSim/Data/MSSQL/Resources/001_AuthStore.sql b/OpenSim/Data/MSSQL/Resources/001_AuthStore.sql
new file mode 100644
index 0000000..c70a193
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/001_AuthStore.sql
@@ -0,0 +1,17 @@
1BEGIN TRANSACTION
2
3CREATE TABLE [auth] (
4 [uuid] [uniqueidentifier] NOT NULL default '00000000-0000-0000-0000-000000000000',
5 [passwordHash] [varchar](32) NOT NULL,
6 [passwordSalt] [varchar](32) NOT NULL,
7 [webLoginKey] [varchar](255) NOT NULL,
8 [accountType] VARCHAR(32) NOT NULL DEFAULT 'UserAccount',
9) ON [PRIMARY]
10
11CREATE TABLE [tokens] (
12 [uuid] [uniqueidentifier] NOT NULL default '00000000-0000-0000-0000-000000000000',
13 [token] [varchar](255) NOT NULL,
14 [validity] [datetime] NOT NULL )
15 ON [PRIMARY]
16
17COMMIT \ No newline at end of file
diff --git a/OpenSim/Data/MSSQL/Resources/001_Avatar.sql b/OpenSim/Data/MSSQL/Resources/001_Avatar.sql
new file mode 100644
index 0000000..48f4c00
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/001_Avatar.sql
@@ -0,0 +1,15 @@
1BEGIN TRANSACTION
2
3CREATE TABLE [Avatars] (
4[PrincipalID] uniqueidentifier NOT NULL,
5[Name] varchar(32) NOT NULL,
6[Value] varchar(255) NOT NULL DEFAULT '',
7PRIMARY KEY CLUSTERED
8(
9 [PrincipalID] ASC, [Name] ASC
10)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
11) ON [PRIMARY]
12
13
14
15COMMIT \ No newline at end of file
diff --git a/OpenSim/Data/MSSQL/Resources/001_FriendsStore.sql b/OpenSim/Data/MSSQL/Resources/001_FriendsStore.sql
new file mode 100644
index 0000000..f6480f7
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/001_FriendsStore.sql
@@ -0,0 +1,11 @@
1BEGIN TRANSACTION
2
3CREATE TABLE [Friends] (
4[PrincipalID] uniqueidentifier NOT NULL,
5[FriendID] varchar(255) NOT NULL,
6[Flags] char(16) NOT NULL DEFAULT '0',
7[Offered] varchar(32) NOT NULL DEFAULT 0)
8 ON [PRIMARY]
9
10
11COMMIT \ No newline at end of file
diff --git a/OpenSim/Data/MSSQL/Resources/001_Presence.sql b/OpenSim/Data/MSSQL/Resources/001_Presence.sql
new file mode 100644
index 0000000..877881c
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/001_Presence.sql
@@ -0,0 +1,19 @@
1BEGIN TRANSACTION
2
3CREATE TABLE [Presence] (
4[UserID] varchar(255) NOT NULL,
5[RegionID] uniqueidentifier NOT NULL,
6[SessionID] uniqueidentifier NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
7[SecureSessionID] uniqueidentifier NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
8[Online] char(5) NOT NULL DEFAULT 'false',
9[Login] char(16) NOT NULL DEFAULT '0',
10[Logout] char(16) NOT NULL DEFAULT '0',
11[Position] char(64) NOT NULL DEFAULT '<0,0,0>',
12[LookAt] char(64) NOT NULL DEFAULT '<0,0,0>',
13[HomeRegionID] uniqueidentifier NOT NULL,
14[HomePosition] CHAR(64) NOT NULL DEFAULT '<0,0,0>',
15[HomeLookAt] CHAR(64) NOT NULL DEFAULT '<0,0,0>',
16)
17 ON [PRIMARY]
18
19COMMIT \ No newline at end of file
diff --git a/OpenSim/Data/MSSQL/Resources/002_AuthStore.sql b/OpenSim/Data/MSSQL/Resources/002_AuthStore.sql
new file mode 100644
index 0000000..daed955
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/002_AuthStore.sql
@@ -0,0 +1,6 @@
1BEGIN TRANSACTION
2
3INSERT INTO auth (UUID, passwordHash, passwordSalt, webLoginKey, accountType) SELECT [UUID] AS UUID, [passwordHash] AS passwordHash, [passwordSalt] AS passwordSalt, [webLoginKey] AS webLoginKey, 'UserAccount' as [accountType] FROM users;
4
5
6COMMIT \ No newline at end of file
diff --git a/OpenSim/Data/MSSQL/Resources/002_FriendsStore.sql b/OpenSim/Data/MSSQL/Resources/002_FriendsStore.sql
new file mode 100644
index 0000000..7762a26
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/002_FriendsStore.sql
@@ -0,0 +1,6 @@
1BEGIN TRANSACTION
2
3INSERT INTO Friends (PrincipalID, FriendID, Flags, Offered) SELECT [ownerID], [friendID], [friendPerms], 0 FROM userfriends;
4
5
6COMMIT \ No newline at end of file
diff --git a/OpenSim/Data/MSSQL/Resources/002_Presence.sql b/OpenSim/Data/MSSQL/Resources/002_Presence.sql
new file mode 100644
index 0000000..a67671d
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/002_Presence.sql
@@ -0,0 +1,6 @@
1BEGIN TRANSACTION
2
3CREATE UNIQUE INDEX SessionID ON Presence(SessionID);
4CREATE INDEX UserID ON Presence(UserID);
5
6COMMIT \ No newline at end of file
diff --git a/OpenSim/Data/MSSQL/Resources/002_UserAccount.sql b/OpenSim/Data/MSSQL/Resources/002_UserAccount.sql
new file mode 100644
index 0000000..89d1f34
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/002_UserAccount.sql
@@ -0,0 +1,12 @@
1BEGIN TRANSACTION
2
3INSERT INTO UserAccounts (PrincipalID, ScopeID, FirstName, LastName, Email, ServiceURLs, Created) SELECT [UUID] AS PrincipalID, '00000000-0000-0000-0000-000000000000' AS ScopeID,
4username AS FirstName,
5lastname AS LastName,
6email as Email, (
7'AssetServerURI=' +
8userAssetURI + ' InventoryServerURI=' + userInventoryURI + ' GatewayURI= HomeURI=') AS ServiceURLs,
9created as Created FROM users;
10
11
12COMMIT \ No newline at end of file
diff --git a/OpenSim/Data/MSSQL/Resources/007_GridStore.sql b/OpenSim/Data/MSSQL/Resources/007_GridStore.sql
new file mode 100644
index 0000000..0b66d40
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/007_GridStore.sql
@@ -0,0 +1,9 @@
1BEGIN TRANSACTION
2
3ALTER TABLE regions ADD [flags] integer NOT NULL DEFAULT 0;
4CREATE INDEX [flags] ON regions(flags);
5ALTER TABLE [regions] ADD [last_seen] integer NOT NULL DEFAULT 0;
6ALTER TABLE [regions] ADD [PrincipalID] uniqueidentifier NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000';
7ALTER TABLE [regions] ADD [Token] varchar(255) NOT NULL DEFAULT 0;
8
9COMMIT
generated by cgit v1.1 at 2024-07-05 14:12:49 +0000