aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/MSSQL
diff options
context:
space:
mode:
Diffstat (limited to 'OpenSim/Data/MSSQL')
-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.cs587
-rw-r--r--OpenSim/Data/MSSQL/MSSQLGridUserData.cs68
-rw-r--r--OpenSim/Data/MSSQL/MSSQLInventoryData.cs206
-rw-r--r--OpenSim/Data/MSSQL/MSSQLLegacyRegionData.cs162
-rw-r--r--OpenSim/Data/MSSQL/MSSQLLogData.cs146
-rw-r--r--OpenSim/Data/MSSQL/MSSQLManager.cs225
-rw-r--r--OpenSim/Data/MSSQL/MSSQLPresenceData.cs170
-rw-r--r--OpenSim/Data/MSSQL/MSSQLRegionData.cs40
-rw-r--r--OpenSim/Data/MSSQL/MSSQLUserAccountData.cs306
-rw-r--r--OpenSim/Data/MSSQL/MSSQLUserData.cs1214
-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/001_UserAccount.sql14
-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/003_UserAccount.sql9
-rw-r--r--OpenSim/Data/MSSQL/Resources/004_UserAccount.sql7
-rw-r--r--OpenSim/Data/MSSQL/Resources/007_GridStore.sql9
30 files changed, 1550 insertions, 2772 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 1ce4abf..d6ea262 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,17 +111,20 @@ 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"]),
127 String.Empty
139 ); 128 );
140 // Region Main 129 // Region Main
141 asset.Description = (string)reader["description"]; 130 asset.Description = (string)reader["description"];
@@ -190,7 +179,8 @@ namespace OpenSim.Data.MSSQL
190 m_log.Warn("[ASSET DB]: Description field truncated from " + asset.Description.Length + " to " + assetDescription.Length + " characters on add"); 179 m_log.Warn("[ASSET DB]: Description field truncated from " + asset.Description.Length + " to " + assetDescription.Length + " characters on add");
191 } 180 }
192 181
193 using (AutoClosingSqlCommand command = m_database.Query(sql)) 182 using (SqlConnection conn = new SqlConnection(m_connectionString))
183 using (SqlCommand command = new SqlCommand(sql, conn))
194 { 184 {
195 int now = (int)((System.DateTime.Now.Ticks - m_ticksToEpoch) / 10000000); 185 int now = (int)((System.DateTime.Now.Ticks - m_ticksToEpoch) / 10000000);
196 command.Parameters.Add(m_database.CreateParameter("id", asset.FullID)); 186 command.Parameters.Add(m_database.CreateParameter("id", asset.FullID));
@@ -202,7 +192,7 @@ namespace OpenSim.Data.MSSQL
202 command.Parameters.Add(m_database.CreateParameter("access_time", now)); 192 command.Parameters.Add(m_database.CreateParameter("access_time", now));
203 command.Parameters.Add(m_database.CreateParameter("create_time", now)); 193 command.Parameters.Add(m_database.CreateParameter("create_time", now));
204 command.Parameters.Add(m_database.CreateParameter("data", asset.Data)); 194 command.Parameters.Add(m_database.CreateParameter("data", asset.Data));
205 195 conn.Open();
206 try 196 try
207 { 197 {
208 command.ExecuteNonQuery(); 198 command.ExecuteNonQuery();
@@ -238,7 +228,8 @@ namespace OpenSim.Data.MSSQL
238 m_log.Warn("[ASSET DB]: Description field truncated from " + asset.Description.Length + " to " + assetDescription.Length + " characters on update"); 228 m_log.Warn("[ASSET DB]: Description field truncated from " + asset.Description.Length + " to " + assetDescription.Length + " characters on update");
239 } 229 }
240 230
241 using (AutoClosingSqlCommand command = m_database.Query(sql)) 231 using (SqlConnection conn = new SqlConnection(m_connectionString))
232 using (SqlCommand command = new SqlCommand(sql, conn))
242 { 233 {
243 command.Parameters.Add(m_database.CreateParameter("id", asset.FullID)); 234 command.Parameters.Add(m_database.CreateParameter("id", asset.FullID));
244 command.Parameters.Add(m_database.CreateParameter("name", assetName)); 235 command.Parameters.Add(m_database.CreateParameter("name", assetName));
@@ -248,7 +239,7 @@ namespace OpenSim.Data.MSSQL
248 command.Parameters.Add(m_database.CreateParameter("temporary", asset.Temporary)); 239 command.Parameters.Add(m_database.CreateParameter("temporary", asset.Temporary));
249 command.Parameters.Add(m_database.CreateParameter("data", asset.Data)); 240 command.Parameters.Add(m_database.CreateParameter("data", asset.Data));
250 command.Parameters.Add(m_database.CreateParameter("@keyId", asset.FullID)); 241 command.Parameters.Add(m_database.CreateParameter("@keyId", asset.FullID));
251 242 conn.Open();
252 try 243 try
253 { 244 {
254 command.ExecuteNonQuery(); 245 command.ExecuteNonQuery();
@@ -307,13 +298,14 @@ namespace OpenSim.Data.MSSQL
307 string sql = @"SELECT (name,description,assetType,temporary,id), Row = ROW_NUMBER() 298 string sql = @"SELECT (name,description,assetType,temporary,id), Row = ROW_NUMBER()
308 OVER (ORDER BY (some column to order by)) 299 OVER (ORDER BY (some column to order by))
309 WHERE Row >= @Start AND Row < @Start + @Count"; 300 WHERE Row >= @Start AND Row < @Start + @Count";
310
311 using (AutoClosingSqlCommand command = m_database.Query(sql))
312 {
313 command.Parameters.Add(m_database.CreateParameter("start", start));
314 command.Parameters.Add(m_database.CreateParameter("count", count));
315 301
316 using (SqlDataReader reader = command.ExecuteReader()) 302 using (SqlConnection conn = new SqlConnection(m_connectionString))
303 using (SqlCommand cmd = new SqlCommand(sql, conn))
304 {
305 cmd.Parameters.Add(m_database.CreateParameter("start", start));
306 cmd.Parameters.Add(m_database.CreateParameter("count", count));
307 conn.Open();
308 using (SqlDataReader reader = cmd.ExecuteReader())
317 { 309 {
318 while (reader.Read()) 310 while (reader.Read())
319 { 311 {
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..af4fd9b
--- /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.*,case when b.Flags is null then -1 else b.Flags end 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", 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
deleted file mode 100644
index 8a3d332..0000000
--- a/OpenSim/Data/MSSQL/MSSQLGridData.cs
+++ /dev/null
@@ -1,587 +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;
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
53 private string m_regionsTableName = "regions";
54
55 #region IPlugin Members
56
57 // [Obsolete("Cannot be default-initialized!")]
58 override public void Initialise()
59 {
60 m_log.Info("[GRID DB]: " + Name + " cannot be default-initialized!");
61 throw new PluginNotInitialisedException(Name);
62 }
63
64 /// <summary>
65 /// Initialises the Grid Interface
66 /// </summary>
67 /// <param name="connectionString">connect string</param>
68 /// <remarks>use mssql_connection.ini</remarks>
69 override public void Initialise(string connectionString)
70 {
71 if (!string.IsNullOrEmpty(connectionString))
72 {
73 database = new MSSQLManager(connectionString);
74 }
75 else
76 {
77 // TODO: make the connect string actually do something
78 IniFile iniFile = new IniFile("mssql_connection.ini");
79
80 string settingDataSource = iniFile.ParseFileReadValue("data_source");
81 string settingInitialCatalog = iniFile.ParseFileReadValue("initial_catalog");
82 string settingPersistSecurityInfo = iniFile.ParseFileReadValue("persist_security_info");
83 string settingUserId = iniFile.ParseFileReadValue("user_id");
84 string settingPassword = iniFile.ParseFileReadValue("password");
85
86 m_regionsTableName = iniFile.ParseFileReadValue("regionstablename");
87 if (m_regionsTableName == null)
88 {
89 m_regionsTableName = "regions";
90 }
91
92 database =
93 new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId,
94 settingPassword);
95 }
96
97 //New migrations check of store
98 database.CheckMigration(_migrationStore);
99 }
100
101 /// <summary>
102 /// Shuts down the grid interface
103 /// </summary>
104 override public void Dispose()
105 {
106 database = null;
107 }
108
109 /// <summary>
110 /// The name of this DB provider.
111 /// </summary>
112 /// <returns>A string containing the storage system name</returns>
113 override public string Name
114 {
115 get { return "MSSQL OpenGridData"; }
116 }
117
118 /// <summary>
119 /// Database provider version.
120 /// </summary>
121 /// <returns>A string containing the storage system version</returns>
122 override public string Version
123 {
124 get { return "0.1"; }
125 }
126
127 #endregion
128
129 #region Public override GridDataBase methods
130
131 /// <summary>
132 /// Returns a list of regions within the specified ranges
133 /// </summary>
134 /// <param name="xmin">minimum X coordinate</param>
135 /// <param name="ymin">minimum Y coordinate</param>
136 /// <param name="xmax">maximum X coordinate</param>
137 /// <param name="ymax">maximum Y coordinate</param>
138 /// <returns>null</returns>
139 /// <remarks>always return null</remarks>
140 override public RegionProfileData[] GetProfilesInRange(uint xmin, uint ymin, uint xmax, uint ymax)
141 {
142 using (AutoClosingSqlCommand command = database.Query("SELECT * FROM regions WHERE locX >= @xmin AND locX <= @xmax AND locY >= @ymin AND locY <= @ymax"))
143 {
144 command.Parameters.Add(database.CreateParameter("xmin", xmin));
145 command.Parameters.Add(database.CreateParameter("ymin", ymin));
146 command.Parameters.Add(database.CreateParameter("xmax", xmax));
147 command.Parameters.Add(database.CreateParameter("ymax", ymax));
148
149 List<RegionProfileData> rows = new List<RegionProfileData>();
150
151 using (SqlDataReader reader = command.ExecuteReader())
152 {
153 while (reader.Read())
154 {
155 rows.Add(ReadSimRow(reader));
156 }
157 }
158
159 if (rows.Count > 0)
160 {
161 return rows.ToArray();
162 }
163 }
164 m_log.Info("[GRID DB] : Found no regions within range.");
165 return null;
166 }
167
168
169 /// <summary>
170 /// Returns up to maxNum profiles of regions that have a name starting with namePrefix
171 /// </summary>
172 /// <param name="namePrefix">The name to match against</param>
173 /// <param name="maxNum">Maximum number of profiles to return</param>
174 /// <returns>A list of sim profiles</returns>
175 override public List<RegionProfileData> GetRegionsByName (string namePrefix, uint maxNum)
176 {
177 using (AutoClosingSqlCommand command = database.Query("SELECT * FROM regions WHERE regionName LIKE @name"))
178 {
179 command.Parameters.Add(database.CreateParameter("name", namePrefix + "%"));
180
181 List<RegionProfileData> rows = new List<RegionProfileData>();
182
183 using (SqlDataReader reader = command.ExecuteReader())
184 {
185 while (rows.Count < maxNum && reader.Read())
186 {
187 rows.Add(ReadSimRow(reader));
188 }
189 }
190
191 return rows;
192 }
193 }
194
195 /// <summary>
196 /// Returns a sim profile from its location
197 /// </summary>
198 /// <param name="handle">Region location handle</param>
199 /// <returns>Sim profile</returns>
200 override public RegionProfileData GetProfileByHandle(ulong handle)
201 {
202 using (AutoClosingSqlCommand command = database.Query("SELECT * FROM " + m_regionsTableName + " WHERE regionHandle = @handle"))
203 {
204 command.Parameters.Add(database.CreateParameter("handle", handle));
205
206 using (SqlDataReader reader = command.ExecuteReader())
207 {
208 if (reader.Read())
209 {
210 return ReadSimRow(reader);
211 }
212 }
213 }
214 m_log.InfoFormat("[GRID DB] : No region found with handle : {0}", handle);
215 return null;
216 }
217
218 /// <summary>
219 /// Returns a sim profile from its UUID
220 /// </summary>
221 /// <param name="uuid">The region UUID</param>
222 /// <returns>The sim profile</returns>
223 override public RegionProfileData GetProfileByUUID(UUID uuid)
224 {
225 using (AutoClosingSqlCommand command = database.Query("SELECT * FROM " + m_regionsTableName + " WHERE uuid = @uuid"))
226 {
227 command.Parameters.Add(database.CreateParameter("uuid", uuid));
228
229 using (SqlDataReader reader = command.ExecuteReader())
230 {
231 if (reader.Read())
232 {
233 return ReadSimRow(reader);
234 }
235 }
236 }
237 m_log.InfoFormat("[GRID DB] : No region found with UUID : {0}", uuid);
238 return null;
239 }
240
241 /// <summary>
242 /// Returns a sim profile from it's Region name string
243 /// </summary>
244 /// <param name="regionName">The region name search query</param>
245 /// <returns>The sim profile</returns>
246 override public RegionProfileData GetProfileByString(string regionName)
247 {
248 if (regionName.Length > 2)
249 {
250 using (AutoClosingSqlCommand command = database.Query("SELECT top 1 * FROM " + m_regionsTableName + " WHERE regionName like @regionName order by regionName"))
251 {
252 command.Parameters.Add(database.CreateParameter("regionName", regionName + "%"));
253
254 using (SqlDataReader reader = command.ExecuteReader())
255 {
256 if (reader.Read())
257 {
258 return ReadSimRow(reader);
259 }
260 }
261 }
262 m_log.InfoFormat("[GRID DB] : No region found with regionName : {0}", regionName);
263 return null;
264 }
265
266 m_log.Error("[GRID DB]: Searched for a Region Name shorter then 3 characters");
267 return null;
268 }
269
270 /// <summary>
271 /// Adds a new specified region to the database
272 /// </summary>
273 /// <param name="profile">The profile to add</param>
274 /// <returns>A dataresponse enum indicating success</returns>
275 override public DataResponse StoreProfile(RegionProfileData profile)
276 {
277 if (GetProfileByUUID(profile.UUID) == null)
278 {
279 if (InsertRegionRow(profile))
280 {
281 return DataResponse.RESPONSE_OK;
282 }
283 }
284 else
285 {
286 if (UpdateRegionRow(profile))
287 {
288 return DataResponse.RESPONSE_OK;
289 }
290 }
291
292 return DataResponse.RESPONSE_ERROR;
293 }
294
295 /// <summary>
296 /// Deletes a sim profile from the database
297 /// </summary>
298 /// <param name="uuid">the sim UUID</param>
299 /// <returns>Successful?</returns>
300 //public DataResponse DeleteProfile(RegionProfileData profile)
301 override public DataResponse DeleteProfile(string uuid)
302 {
303 using (AutoClosingSqlCommand command = database.Query("DELETE FROM regions WHERE uuid = @uuid;"))
304 {
305 command.Parameters.Add(database.CreateParameter("uuid", uuid));
306 try
307 {
308 command.ExecuteNonQuery();
309 return DataResponse.RESPONSE_OK;
310 }
311 catch (Exception e)
312 {
313 m_log.DebugFormat("[GRID DB] : Error deleting region info, error is : {0}", e.Message);
314 return DataResponse.RESPONSE_ERROR;
315 }
316 }
317 }
318
319 #endregion
320
321 #region Methods that are not used or deprecated (still needed because of base class)
322
323 /// <summary>
324 /// DEPRECATED. Attempts to authenticate a region by comparing a shared secret.
325 /// </summary>
326 /// <param name="uuid">The UUID of the challenger</param>
327 /// <param name="handle">The attempted regionHandle of the challenger</param>
328 /// <param name="authkey">The secret</param>
329 /// <returns>Whether the secret and regionhandle match the database entry for UUID</returns>
330 override public bool AuthenticateSim(UUID uuid, ulong handle, string authkey)
331 {
332 bool throwHissyFit = false; // Should be true by 1.0
333
334 if (throwHissyFit)
335 throw new Exception("CRYPTOWEAK AUTHENTICATE: Refusing to authenticate due to replay potential.");
336
337 RegionProfileData data = GetProfileByUUID(uuid);
338
339 return (handle == data.regionHandle && authkey == data.regionSecret);
340 }
341
342 /// <summary>
343 /// NOT YET FUNCTIONAL. Provides a cryptographic authentication of a region
344 /// </summary>
345 /// <remarks>This requires a security audit.</remarks>
346 /// <param name="uuid"></param>
347 /// <param name="handle"></param>
348 /// <param name="authhash"></param>
349 /// <param name="challenge"></param>
350 /// <returns></returns>
351 public bool AuthenticateSim(UUID uuid, ulong handle, string authhash, string challenge)
352 {
353 // SHA512Managed HashProvider = new SHA512Managed();
354 // Encoding TextProvider = new UTF8Encoding();
355
356 // byte[] stream = TextProvider.GetBytes(uuid.ToString() + ":" + handle.ToString() + ":" + challenge);
357 // byte[] hash = HashProvider.ComputeHash(stream);
358 return false;
359 }
360
361 /// <summary>
362 /// NOT IMPLEMENTED
363 /// WHEN IS THIS GONNA BE IMPLEMENTED.
364 /// </summary>
365 /// <param name="x"></param>
366 /// <param name="y"></param>
367 /// <returns>null</returns>
368 override public ReservationData GetReservationAtPoint(uint x, uint y)
369 {
370 return null;
371 }
372
373 #endregion
374
375 #region private methods
376
377 /// <summary>
378 /// Reads a region row from a database reader
379 /// </summary>
380 /// <param name="reader">An active database reader</param>
381 /// <returns>A region profile</returns>
382 private static RegionProfileData ReadSimRow(IDataRecord reader)
383 {
384 RegionProfileData retval = new RegionProfileData();
385
386 // Region Main gotta-have-or-we-return-null parts
387 UInt64 tmp64;
388 if (!UInt64.TryParse(reader["regionHandle"].ToString(), out tmp64))
389 {
390 return null;
391 }
392
393 retval.regionHandle = tmp64;
394
395// UUID tmp_uuid;
396// if (!UUID.TryParse((string)reader["uuid"], out tmp_uuid))
397// {
398// return null;
399// }
400
401 retval.UUID = new UUID((Guid)reader["uuid"]); // tmp_uuid;
402
403 // non-critical parts
404 retval.regionName = reader["regionName"].ToString();
405 retval.originUUID = new UUID((Guid)reader["originUUID"]);
406
407 // Secrets
408 retval.regionRecvKey = reader["regionRecvKey"].ToString();
409 retval.regionSecret = reader["regionSecret"].ToString();
410 retval.regionSendKey = reader["regionSendKey"].ToString();
411
412 // Region Server
413 retval.regionDataURI = reader["regionDataURI"].ToString();
414 retval.regionOnline = false; // Needs to be pinged before this can be set.
415 retval.serverIP = reader["serverIP"].ToString();
416 retval.serverPort = Convert.ToUInt32(reader["serverPort"]);
417 retval.serverURI = reader["serverURI"].ToString();
418 retval.httpPort = Convert.ToUInt32(reader["serverHttpPort"].ToString());
419 retval.remotingPort = Convert.ToUInt32(reader["serverRemotingPort"].ToString());
420
421 // Location
422 retval.regionLocX = Convert.ToUInt32(reader["locX"].ToString());
423 retval.regionLocY = Convert.ToUInt32(reader["locY"].ToString());
424 retval.regionLocZ = Convert.ToUInt32(reader["locZ"].ToString());
425
426 // Neighbours - 0 = No Override
427 retval.regionEastOverrideHandle = Convert.ToUInt64(reader["eastOverrideHandle"].ToString());
428 retval.regionWestOverrideHandle = Convert.ToUInt64(reader["westOverrideHandle"].ToString());
429 retval.regionSouthOverrideHandle = Convert.ToUInt64(reader["southOverrideHandle"].ToString());
430 retval.regionNorthOverrideHandle = Convert.ToUInt64(reader["northOverrideHandle"].ToString());
431
432 // Assets
433 retval.regionAssetURI = reader["regionAssetURI"].ToString();
434 retval.regionAssetRecvKey = reader["regionAssetRecvKey"].ToString();
435 retval.regionAssetSendKey = reader["regionAssetSendKey"].ToString();
436
437 // Userserver
438 retval.regionUserURI = reader["regionUserURI"].ToString();
439 retval.regionUserRecvKey = reader["regionUserRecvKey"].ToString();
440 retval.regionUserSendKey = reader["regionUserSendKey"].ToString();
441
442 // World Map Addition
443 retval.regionMapTextureID = new UUID((Guid)reader["regionMapTexture"]);
444 retval.owner_uuid = new UUID((Guid)reader["owner_uuid"]);
445 retval.maturity = Convert.ToUInt32(reader["access"]);
446 return retval;
447 }
448
449 /// <summary>
450 /// Update the specified region in the database
451 /// </summary>
452 /// <param name="profile">The profile to update</param>
453 /// <returns>success ?</returns>
454 private bool UpdateRegionRow(RegionProfileData profile)
455 {
456 bool returnval = false;
457
458 //Insert new region
459 string sql =
460 "UPDATE " + m_regionsTableName + @" SET
461 [regionHandle]=@regionHandle, [regionName]=@regionName,
462 [regionRecvKey]=@regionRecvKey, [regionSecret]=@regionSecret, [regionSendKey]=@regionSendKey,
463 [regionDataURI]=@regionDataURI, [serverIP]=@serverIP, [serverPort]=@serverPort, [serverURI]=@serverURI,
464 [locX]=@locX, [locY]=@locY, [locZ]=@locZ, [eastOverrideHandle]=@eastOverrideHandle,
465 [westOverrideHandle]=@westOverrideHandle, [southOverrideHandle]=@southOverrideHandle,
466 [northOverrideHandle]=@northOverrideHandle, [regionAssetURI]=@regionAssetURI,
467 [regionAssetRecvKey]=@regionAssetRecvKey, [regionAssetSendKey]=@regionAssetSendKey,
468 [regionUserURI]=@regionUserURI, [regionUserRecvKey]=@regionUserRecvKey, [regionUserSendKey]=@regionUserSendKey,
469 [regionMapTexture]=@regionMapTexture, [serverHttpPort]=@serverHttpPort,
470 [serverRemotingPort]=@serverRemotingPort, [owner_uuid]=@owner_uuid , [originUUID]=@originUUID
471 where [uuid]=@uuid";
472
473 using (AutoClosingSqlCommand command = database.Query(sql))
474 {
475 command.Parameters.Add(database.CreateParameter("regionHandle", profile.regionHandle));
476 command.Parameters.Add(database.CreateParameter("regionName", profile.regionName));
477 command.Parameters.Add(database.CreateParameter("uuid", profile.UUID));
478 command.Parameters.Add(database.CreateParameter("regionRecvKey", profile.regionRecvKey));
479 command.Parameters.Add(database.CreateParameter("regionSecret", profile.regionSecret));
480 command.Parameters.Add(database.CreateParameter("regionSendKey", profile.regionSendKey));
481 command.Parameters.Add(database.CreateParameter("regionDataURI", profile.regionDataURI));
482 command.Parameters.Add(database.CreateParameter("serverIP", profile.serverIP));
483 command.Parameters.Add(database.CreateParameter("serverPort", profile.serverPort));
484 command.Parameters.Add(database.CreateParameter("serverURI", profile.serverURI));
485 command.Parameters.Add(database.CreateParameter("locX", profile.regionLocX));
486 command.Parameters.Add(database.CreateParameter("locY", profile.regionLocY));
487 command.Parameters.Add(database.CreateParameter("locZ", profile.regionLocZ));
488 command.Parameters.Add(database.CreateParameter("eastOverrideHandle", profile.regionEastOverrideHandle));
489 command.Parameters.Add(database.CreateParameter("westOverrideHandle", profile.regionWestOverrideHandle));
490 command.Parameters.Add(database.CreateParameter("northOverrideHandle", profile.regionNorthOverrideHandle));
491 command.Parameters.Add(database.CreateParameter("southOverrideHandle", profile.regionSouthOverrideHandle));
492 command.Parameters.Add(database.CreateParameter("regionAssetURI", profile.regionAssetURI));
493 command.Parameters.Add(database.CreateParameter("regionAssetRecvKey", profile.regionAssetRecvKey));
494 command.Parameters.Add(database.CreateParameter("regionAssetSendKey", profile.regionAssetSendKey));
495 command.Parameters.Add(database.CreateParameter("regionUserURI", profile.regionUserURI));
496 command.Parameters.Add(database.CreateParameter("regionUserRecvKey", profile.regionUserRecvKey));
497 command.Parameters.Add(database.CreateParameter("regionUserSendKey", profile.regionUserSendKey));
498 command.Parameters.Add(database.CreateParameter("regionMapTexture", profile.regionMapTextureID));
499 command.Parameters.Add(database.CreateParameter("serverHttpPort", profile.httpPort));
500 command.Parameters.Add(database.CreateParameter("serverRemotingPort", profile.remotingPort));
501 command.Parameters.Add(database.CreateParameter("owner_uuid", profile.owner_uuid));
502 command.Parameters.Add(database.CreateParameter("originUUID", profile.originUUID));
503
504 try
505 {
506 command.ExecuteNonQuery();
507 returnval = true;
508 }
509 catch (Exception e)
510 {
511 m_log.Error("[GRID DB] : Error updating region, error: " + e.Message);
512 }
513 }
514
515 return returnval;
516 }
517
518 /// <summary>
519 /// Creates a new region in the database
520 /// </summary>
521 /// <param name="profile">The region profile to insert</param>
522 /// <returns>Successful?</returns>
523 private bool InsertRegionRow(RegionProfileData profile)
524 {
525 bool returnval = false;
526
527 //Insert new region
528 string sql =
529 "INSERT INTO " + m_regionsTableName + @" ([regionHandle], [regionName], [uuid], [regionRecvKey], [regionSecret], [regionSendKey], [regionDataURI],
530 [serverIP], [serverPort], [serverURI], [locX], [locY], [locZ], [eastOverrideHandle], [westOverrideHandle],
531 [southOverrideHandle], [northOverrideHandle], [regionAssetURI], [regionAssetRecvKey], [regionAssetSendKey],
532 [regionUserURI], [regionUserRecvKey], [regionUserSendKey], [regionMapTexture], [serverHttpPort],
533 [serverRemotingPort], [owner_uuid], [originUUID], [access])
534 VALUES (@regionHandle, @regionName, @uuid, @regionRecvKey, @regionSecret, @regionSendKey, @regionDataURI,
535 @serverIP, @serverPort, @serverURI, @locX, @locY, @locZ, @eastOverrideHandle, @westOverrideHandle,
536 @southOverrideHandle, @northOverrideHandle, @regionAssetURI, @regionAssetRecvKey, @regionAssetSendKey,
537 @regionUserURI, @regionUserRecvKey, @regionUserSendKey, @regionMapTexture, @serverHttpPort, @serverRemotingPort, @owner_uuid, @originUUID, @access);";
538
539 using (AutoClosingSqlCommand command = database.Query(sql))
540 {
541 command.Parameters.Add(database.CreateParameter("regionHandle", profile.regionHandle));
542 command.Parameters.Add(database.CreateParameter("regionName", profile.regionName));
543 command.Parameters.Add(database.CreateParameter("uuid", profile.UUID));
544 command.Parameters.Add(database.CreateParameter("regionRecvKey", profile.regionRecvKey));
545 command.Parameters.Add(database.CreateParameter("regionSecret", profile.regionSecret));
546 command.Parameters.Add(database.CreateParameter("regionSendKey", profile.regionSendKey));
547 command.Parameters.Add(database.CreateParameter("regionDataURI", profile.regionDataURI));
548 command.Parameters.Add(database.CreateParameter("serverIP", profile.serverIP));
549 command.Parameters.Add(database.CreateParameter("serverPort", profile.serverPort));
550 command.Parameters.Add(database.CreateParameter("serverURI", profile.serverURI));
551 command.Parameters.Add(database.CreateParameter("locX", profile.regionLocX));
552 command.Parameters.Add(database.CreateParameter("locY", profile.regionLocY));
553 command.Parameters.Add(database.CreateParameter("locZ", profile.regionLocZ));
554 command.Parameters.Add(database.CreateParameter("eastOverrideHandle", profile.regionEastOverrideHandle));
555 command.Parameters.Add(database.CreateParameter("westOverrideHandle", profile.regionWestOverrideHandle));
556 command.Parameters.Add(database.CreateParameter("northOverrideHandle", profile.regionNorthOverrideHandle));
557 command.Parameters.Add(database.CreateParameter("southOverrideHandle", profile.regionSouthOverrideHandle));
558 command.Parameters.Add(database.CreateParameter("regionAssetURI", profile.regionAssetURI));
559 command.Parameters.Add(database.CreateParameter("regionAssetRecvKey", profile.regionAssetRecvKey));
560 command.Parameters.Add(database.CreateParameter("regionAssetSendKey", profile.regionAssetSendKey));
561 command.Parameters.Add(database.CreateParameter("regionUserURI", profile.regionUserURI));
562 command.Parameters.Add(database.CreateParameter("regionUserRecvKey", profile.regionUserRecvKey));
563 command.Parameters.Add(database.CreateParameter("regionUserSendKey", profile.regionUserSendKey));
564 command.Parameters.Add(database.CreateParameter("regionMapTexture", profile.regionMapTextureID));
565 command.Parameters.Add(database.CreateParameter("serverHttpPort", profile.httpPort));
566 command.Parameters.Add(database.CreateParameter("serverRemotingPort", profile.remotingPort));
567 command.Parameters.Add(database.CreateParameter("owner_uuid", profile.owner_uuid));
568 command.Parameters.Add(database.CreateParameter("originUUID", profile.originUUID));
569 command.Parameters.Add(database.CreateParameter("access", profile.maturity));
570
571 try
572 {
573 command.ExecuteNonQuery();
574 returnval = true;
575 }
576 catch (Exception e)
577 {
578 m_log.Error("[GRID DB] : Error inserting region, error: " + e.Message);
579 }
580 }
581
582 return returnval;
583 }
584
585 #endregion
586 }
587}
diff --git a/OpenSim/Data/MSSQL/MSSQLGridUserData.cs b/OpenSim/Data/MSSQL/MSSQLGridUserData.cs
new file mode 100644
index 0000000..b4a945c
--- /dev/null
+++ b/OpenSim/Data/MSSQL/MSSQLGridUserData.cs
@@ -0,0 +1,68 @@
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 MSSQLGridUserData : MSSQLGenericTableHandler<GridUserData>,
44 IGridUserData
45 {
46 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
47
48 public MSSQLGridUserData(string connectionString, string realm) :
49 base(connectionString, realm, "UserGrid")
50 {
51 }
52
53 public GridUserData GetGridUserData(string userID)
54 {
55 GridUserData[] ret = Get("UserID", userID);
56
57 if (ret.Length == 0)
58 return null;
59
60 return ret[0];
61 }
62
63 public bool StoreGridUserData(GridUserData data)
64 {
65 return Store(data);
66 }
67 }
68}
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 0b430c7..b1339b6 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 }
@@ -690,9 +710,11 @@ VALUES
690 { 710 {
691 string sql = "select * from regionsettings where regionUUID = @regionUUID"; 711 string sql = "select * from regionsettings where regionUUID = @regionUUID";
692 RegionSettings regionSettings; 712 RegionSettings regionSettings;
693 using (AutoClosingSqlCommand cmd = _Database.Query(sql)) 713 using (SqlConnection conn = new SqlConnection(m_connectionString))
714 using (SqlCommand cmd = new SqlCommand(sql, conn))
694 { 715 {
695 cmd.Parameters.Add(_Database.CreateParameter("@regionUUID", regionUUID)); 716 cmd.Parameters.Add(_Database.CreateParameter("@regionUUID", regionUUID));
717 conn.Open();
696 using (SqlDataReader reader = cmd.ExecuteReader()) 718 using (SqlDataReader reader = cmd.ExecuteReader())
697 { 719 {
698 if (reader.Read()) 720 if (reader.Read())
@@ -724,9 +746,12 @@ VALUES
724 { 746 {
725 //Little check if regionUUID already exist in DB 747 //Little check if regionUUID already exist in DB
726 string regionUUID; 748 string regionUUID;
727 using (AutoClosingSqlCommand cmd = _Database.Query("SELECT regionUUID FROM regionsettings WHERE regionUUID = @regionUUID")) 749 string sql = "SELECT regionUUID FROM regionsettings WHERE regionUUID = @regionUUID";
750 using (SqlConnection conn = new SqlConnection(m_connectionString))
751 using (SqlCommand cmd = new SqlCommand(sql, conn))
728 { 752 {
729 cmd.Parameters.Add(_Database.CreateParameter("@regionUUID", regionSettings.RegionUUID)); 753 cmd.Parameters.Add(_Database.CreateParameter("@regionUUID", regionSettings.RegionUUID));
754 conn.Open();
730 regionUUID = cmd.ExecuteScalar().ToString(); 755 regionUUID = cmd.ExecuteScalar().ToString();
731 } 756 }
732 757
@@ -737,8 +762,8 @@ VALUES
737 else 762 else
738 { 763 {
739 //This method only updates region settings!!! First call LoadRegionSettings to create new region settings in DB 764 //This method only updates region settings!!! First call LoadRegionSettings to create new region settings in DB
740 string sql = 765 sql =
741 @"UPDATE [regionsettings] SET [block_terraform] = @block_terraform ,[block_fly] = @block_fly ,[allow_damage] = @allow_damage 766 @"UPDATE [regionsettings] SET [block_terraform] = @block_terraform ,[block_fly] = @block_fly ,[allow_damage] = @allow_damage
742,[restrict_pushing] = @restrict_pushing ,[allow_land_resell] = @allow_land_resell ,[allow_land_join_divide] = @allow_land_join_divide 767,[restrict_pushing] = @restrict_pushing ,[allow_land_resell] = @allow_land_resell ,[allow_land_join_divide] = @allow_land_join_divide
743,[block_show_in_search] = @block_show_in_search ,[agent_limit] = @agent_limit ,[object_bonus] = @object_bonus ,[maturity] = @maturity 768,[block_show_in_search] = @block_show_in_search ,[agent_limit] = @agent_limit ,[object_bonus] = @object_bonus ,[maturity] = @maturity
744,[disable_scripts] = @disable_scripts ,[disable_collisions] = @disable_collisions ,[disable_physics] = @disable_physics 769,[disable_scripts] = @disable_scripts ,[disable_collisions] = @disable_collisions ,[disable_physics] = @disable_physics
@@ -750,10 +775,11 @@ VALUES
750,[covenant] = @covenant , [sunvectorx] = @sunvectorx, [sunvectory] = @sunvectory, [sunvectorz] = @sunvectorz, [Sandbox] = @Sandbox, [loaded_creation_datetime] = @loaded_creation_datetime, [loaded_creation_id] = @loaded_creation_id 775,[covenant] = @covenant , [sunvectorx] = @sunvectorx, [sunvectory] = @sunvectory, [sunvectorz] = @sunvectorz, [Sandbox] = @Sandbox, [loaded_creation_datetime] = @loaded_creation_datetime, [loaded_creation_id] = @loaded_creation_id
751 WHERE [regionUUID] = @regionUUID"; 776 WHERE [regionUUID] = @regionUUID";
752 777
753 using (AutoClosingSqlCommand cmd = _Database.Query(sql)) 778 using (SqlConnection conn = new SqlConnection(m_connectionString))
779 using (SqlCommand cmd = new SqlCommand(sql, conn))
754 { 780 {
755 cmd.Parameters.AddRange(CreateRegionSettingParameters(regionSettings)); 781 cmd.Parameters.AddRange(CreateRegionSettingParameters(regionSettings));
756 782 conn.Open();
757 cmd.ExecuteNonQuery(); 783 cmd.ExecuteNonQuery();
758 } 784 }
759 } 785 }
@@ -810,9 +836,11 @@ VALUES
810 @elevation_2_ne,@elevation_1_se,@elevation_2_se,@elevation_1_sw,@elevation_2_sw,@water_height,@terrain_raise_limit, 836 @elevation_2_ne,@elevation_1_se,@elevation_2_se,@elevation_1_sw,@elevation_2_sw,@water_height,@terrain_raise_limit,
811 @terrain_lower_limit,@use_estate_sun,@fixed_sun,@sun_position,@covenant,@sunvectorx,@sunvectory, @sunvectorz, @Sandbox, @loaded_creation_datetime, @loaded_creation_id)"; 837 @terrain_lower_limit,@use_estate_sun,@fixed_sun,@sun_position,@covenant,@sunvectorx,@sunvectory, @sunvectorz, @Sandbox, @loaded_creation_datetime, @loaded_creation_id)";
812 838
813 using (AutoClosingSqlCommand cmd = _Database.Query(sql)) 839 using (SqlConnection conn = new SqlConnection(m_connectionString))
840 using (SqlCommand cmd = new SqlCommand(sql, conn))
814 { 841 {
815 cmd.Parameters.AddRange(CreateRegionSettingParameters(regionSettings)); 842 cmd.Parameters.AddRange(CreateRegionSettingParameters(regionSettings));
843 conn.Open();
816 cmd.ExecuteNonQuery(); 844 cmd.ExecuteNonQuery();
817 } 845 }
818 } 846 }
@@ -916,15 +944,15 @@ VALUES
916 newData.PassHours = Convert.ToSingle(row["PassHours"]); 944 newData.PassHours = Convert.ToSingle(row["PassHours"]);
917 newData.PassPrice = Convert.ToInt32(row["PassPrice"]); 945 newData.PassPrice = Convert.ToInt32(row["PassPrice"]);
918 946
919// UUID authedbuyer; 947 // UUID authedbuyer;
920// UUID snapshotID; 948 // UUID snapshotID;
921// 949 //
922// if (UUID.TryParse((string)row["AuthBuyerID"], out authedbuyer)) 950 // if (UUID.TryParse((string)row["AuthBuyerID"], out authedbuyer))
923// newData.AuthBuyerID = authedbuyer; 951 // newData.AuthBuyerID = authedbuyer;
924// 952 //
925// if (UUID.TryParse((string)row["SnapshotUUID"], out snapshotID)) 953 // if (UUID.TryParse((string)row["SnapshotUUID"], out snapshotID))
926// newData.SnapshotID = snapshotID; 954 // newData.SnapshotID = snapshotID;
927 newData.AuthBuyerID = new UUID((Guid) row["AuthBuyerID"]); 955 newData.AuthBuyerID = new UUID((Guid)row["AuthBuyerID"]);
928 newData.SnapshotID = new UUID((Guid)row["SnapshotUUID"]); 956 newData.SnapshotID = new UUID((Guid)row["SnapshotUUID"]);
929 957
930 newData.OtherCleanTime = Convert.ToInt32(row["OtherCleanTime"]); 958 newData.OtherCleanTime = Convert.ToInt32(row["OtherCleanTime"]);
@@ -1193,7 +1221,7 @@ VALUES
1193 #endregion 1221 #endregion
1194 1222
1195 #region Create parameters methods 1223 #region Create parameters methods
1196 1224
1197 /// <summary> 1225 /// <summary>
1198 /// Creates the prim inventory parameters. 1226 /// Creates the prim inventory parameters.
1199 /// </summary> 1227 /// </summary>
@@ -1477,7 +1505,7 @@ VALUES
1477 1505
1478 parameters.Add(_Database.CreateParameter("CollisionSound", prim.CollisionSound)); 1506 parameters.Add(_Database.CreateParameter("CollisionSound", prim.CollisionSound));
1479 parameters.Add(_Database.CreateParameter("CollisionSoundVolume", prim.CollisionSoundVolume)); 1507 parameters.Add(_Database.CreateParameter("CollisionSoundVolume", prim.CollisionSoundVolume));
1480 if (prim.PassTouches) 1508 if (prim.PassTouches)
1481 parameters.Add(_Database.CreateParameter("PassTouches", 1)); 1509 parameters.Add(_Database.CreateParameter("PassTouches", 1));
1482 else 1510 else
1483 parameters.Add(_Database.CreateParameter("PassTouches", 0)); 1511 parameters.Add(_Database.CreateParameter("PassTouches", 0));
@@ -1532,7 +1560,7 @@ VALUES
1532 1560
1533 return parameters.ToArray(); 1561 return parameters.ToArray();
1534 } 1562 }
1535 1563
1536 #endregion 1564 #endregion
1537 1565
1538 #endregion 1566 #endregion
diff --git a/OpenSim/Data/MSSQL/MSSQLLogData.cs b/OpenSim/Data/MSSQL/MSSQLLogData.cs
deleted file mode 100644
index 72c50e6..0000000
--- a/OpenSim/Data/MSSQL/MSSQLLogData.cs
+++ /dev/null
@@ -1,146 +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;
29using System.Reflection;
30using log4net;
31using OpenSim.Framework;
32
33namespace OpenSim.Data.MSSQL
34{
35 /// <summary>
36 /// An interface to the log database for MSSQL
37 /// </summary>
38 internal class MSSQLLogData : ILogDataPlugin
39 {
40 private const string _migrationStore = "LogStore";
41
42 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
43
44 /// <summary>
45 /// The database manager
46 /// </summary>
47 public MSSQLManager database;
48
49 [Obsolete("Cannot be default-initialized!")]
50 public void Initialise()
51 {
52 m_log.Info("[LOG DB]: " + Name + " cannot be default-initialized!");
53 throw new PluginNotInitialisedException (Name);
54 }
55
56 /// <summary>
57 /// Artificial constructor called when the plugin is loaded
58 /// </summary>
59 public void Initialise(string connect)
60 {
61 if (!string.IsNullOrEmpty(connect))
62 {
63 database = new MSSQLManager(connect);
64 }
65 else
66 {
67 // TODO: do something with the connect string
68 IniFile gridDataMSSqlFile = new IniFile("mssql_connection.ini");
69 string settingDataSource = gridDataMSSqlFile.ParseFileReadValue("data_source");
70 string settingInitialCatalog = gridDataMSSqlFile.ParseFileReadValue("initial_catalog");
71 string settingPersistSecurityInfo = gridDataMSSqlFile.ParseFileReadValue("persist_security_info");
72 string settingUserId = gridDataMSSqlFile.ParseFileReadValue("user_id");
73 string settingPassword = gridDataMSSqlFile.ParseFileReadValue("password");
74
75 database =
76 new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId,
77 settingPassword);
78 }
79
80 //Updating mechanisme
81 database.CheckMigration(_migrationStore);
82 }
83
84 /// <summary>
85 /// Saves a log item to the database
86 /// </summary>
87 /// <param name="serverDaemon">The daemon triggering the event</param>
88 /// <param name="target">The target of the action (region / agent UUID, etc)</param>
89 /// <param name="methodCall">The method call where the problem occured</param>
90 /// <param name="arguments">The arguments passed to the method</param>
91 /// <param name="priority">How critical is this?</param>
92 /// <param name="logMessage">The message to log</param>
93 public void saveLog(string serverDaemon, string target, string methodCall, string arguments, int priority,
94 string logMessage)
95 {
96 string sql = "INSERT INTO logs ([target], [server], [method], [arguments], [priority], [message]) VALUES ";
97 sql += "(@target, @server, @method, @arguments, @priority, @message);";
98
99 using (AutoClosingSqlCommand command = database.Query(sql))
100 {
101 command.Parameters.Add(database.CreateParameter("server", serverDaemon));
102 command.Parameters.Add(database.CreateParameter("target",target));
103 command.Parameters.Add(database.CreateParameter("method", methodCall));
104 command.Parameters.Add(database.CreateParameter("arguments", arguments));
105 command.Parameters.Add(database.CreateParameter("priority", priority.ToString()));
106 command.Parameters.Add(database.CreateParameter("message", logMessage));
107
108 try
109 {
110 command.ExecuteNonQuery();
111 }
112 catch (Exception e)
113 {
114 //Are we not in a loop here
115 m_log.Error("[LOG DB] Error logging : " + e.Message);
116 }
117 }
118 }
119
120 /// <summary>
121 /// Returns the name of this DB provider
122 /// </summary>
123 /// <returns>A string containing the DB provider name</returns>
124 public string Name
125 {
126 get { return "MSSQL Logdata Interface"; }
127 }
128
129 /// <summary>
130 /// Closes the database provider
131 /// </summary>
132 public void Dispose()
133 {
134 database = null;
135 }
136
137 /// <summary>
138 /// Returns the version of this DB provider
139 /// </summary>
140 /// <returns>A string containing the provider version</returns>
141 public string Version
142 {
143 get { return "0.1"; }
144 }
145 }
146}
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 a898aab..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);
@@ -307,5 +307,37 @@ namespace OpenSim.Data.MSSQL
307 } 307 }
308 return false; 308 return false;
309 } 309 }
310
311 public List<RegionData> GetDefaultRegions(UUID scopeID)
312 {
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
325 }
326
327 public List<RegionData> GetFallbackRegions(UUID scopeID, int x, int y)
328 {
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 }
341 }
310 } 342 }
311} 343}
diff --git a/OpenSim/Data/MSSQL/MSSQLUserAccountData.cs b/OpenSim/Data/MSSQL/MSSQLUserAccountData.cs
index 2d92cb1..9f18e5e 100644
--- a/OpenSim/Data/MSSQL/MSSQLUserAccountData.cs
+++ b/OpenSim/Data/MSSQL/MSSQLUserAccountData.cs
@@ -36,153 +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 }
64 45 //private string m_Realm;
65 public UserAccountData Get(UUID principalID, UUID scopeID) 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 //}
200
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, object>();
69
70 string sql = string.Format("select * from {0} where UUID = @principalID", m_Realm);
71 if (scopeID != UUID.Zero)
72 sql += " and ScopeID = @scopeID";
73 204
74 using (SqlConnection conn = new SqlConnection(m_ConnectionString)) 205 for (int i = 0; i < words.Length; i++)
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 SetDataItem(UUID principalID, string item, string value)
172 {
173 string sql = string.Format("update {0} set {1} = @{1} where UUID = @UUID", m_Realm, item);
174 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
175 using (SqlCommand cmd = new SqlCommand(sql, conn))
176 {
177 cmd.Parameters.Add(m_database.CreateParameter("@" + item, value));
178 cmd.Parameters.Add(m_database.CreateParameter("@UUID", principalID));
179
180 conn.Open();
181 237
182 if (cmd.ExecuteNonQuery() > 0) 238 return DoQuery(cmd);
183 return true;
184 } 239 }
185 return false;
186 } 240 }
187 } 241 }
188} 242}
diff --git a/OpenSim/Data/MSSQL/MSSQLUserData.cs b/OpenSim/Data/MSSQL/MSSQLUserData.cs
deleted file mode 100644
index 3ef1053..0000000
--- a/OpenSim/Data/MSSQL/MSSQLUserData.cs
+++ /dev/null
@@ -1,1214 +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;
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
54 private const string m_agentsTableName = "agents";
55 private const string m_usersTableName = "users";
56 private const string m_userFriendsTableName = "userfriends";
57
58 // [Obsolete("Cannot be default-initialized!")]
59 override public void Initialise()
60 {
61 m_log.Info("[MSSQLUserData]: " + Name + " cannot be default-initialized!");
62 throw new PluginNotInitialisedException(Name);
63 }
64
65 /// <summary>
66 /// Loads and initialises the MSSQL storage plugin
67 /// </summary>
68 /// <param name="connect">connectionstring</param>
69 /// <remarks>use mssql_connection.ini</remarks>
70 override public void Initialise(string connect)
71 {
72 if (!string.IsNullOrEmpty(connect))
73 {
74 database = new MSSQLManager(connect);
75 }
76 else
77 {
78 IniFile iniFile = new IniFile("mssql_connection.ini");
79
80 string settingDataSource = iniFile.ParseFileReadValue("data_source");
81 string settingInitialCatalog = iniFile.ParseFileReadValue("initial_catalog");
82 string settingPersistSecurityInfo = iniFile.ParseFileReadValue("persist_security_info");
83 string settingUserId = iniFile.ParseFileReadValue("user_id");
84 string settingPassword = iniFile.ParseFileReadValue("password");
85
86 database = new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, settingPassword);
87 }
88
89 //Check migration on DB
90 database.CheckMigration(_migrationStore);
91 }
92
93 /// <summary>
94 /// Releases unmanaged and - optionally - managed resources
95 /// </summary>
96 override public void Dispose() { }
97
98 #region User table methods
99
100 /// <summary>
101 /// Searches the database for a specified user profile by name components
102 /// </summary>
103 /// <param name="user">The first part of the account name</param>
104 /// <param name="last">The second part of the account name</param>
105 /// <returns>A user profile</returns>
106 override public UserProfileData GetUserByName(string user, string last)
107 {
108 string sql = string.Format(@"SELECT * FROM {0}
109 WHERE username = @first AND lastname = @second", m_usersTableName);
110 using (AutoClosingSqlCommand command = database.Query(sql))
111 {
112 command.Parameters.Add(database.CreateParameter("first", user));
113 command.Parameters.Add(database.CreateParameter("second", last));
114 try
115 {
116 using (SqlDataReader reader = command.ExecuteReader())
117 {
118 return ReadUserRow(reader);
119 }
120 }
121 catch (Exception e)
122 {
123 m_log.ErrorFormat("[USER DB] Error getting user profile for {0} {1}: {2}", user, last, e.Message);
124 return null;
125 }
126 }
127 }
128
129 /// <summary>
130 /// See IUserDataPlugin
131 /// </summary>
132 /// <param name="uuid"></param>
133 /// <returns></returns>
134 override public UserProfileData GetUserByUUID(UUID uuid)
135 {
136 string sql = string.Format("SELECT * FROM {0} WHERE UUID = @uuid", m_usersTableName);
137 using (AutoClosingSqlCommand command = database.Query(sql))
138 {
139 command.Parameters.Add(database.CreateParameter("uuid", uuid));
140 try
141 {
142 using (SqlDataReader reader = command.ExecuteReader())
143 {
144 return ReadUserRow(reader);
145 }
146 }
147 catch (Exception e)
148 {
149 m_log.ErrorFormat("[USER DB] Error getting user profile by UUID {0}, error: {1}", uuid, e.Message);
150 return null;
151 }
152 }
153 }
154
155
156 /// <summary>
157 /// Creates a new users profile
158 /// </summary>
159 /// <param name="user">The user profile to create</param>
160 override public void AddNewUserProfile(UserProfileData user)
161 {
162 try
163 {
164 InsertUserRow(user.ID, user.FirstName, user.SurName, user.Email, user.PasswordHash, user.PasswordSalt,
165 user.HomeRegion, user.HomeLocation.X, user.HomeLocation.Y,
166 user.HomeLocation.Z,
167 user.HomeLookAt.X, user.HomeLookAt.Y, user.HomeLookAt.Z, user.Created,
168 user.LastLogin, user.UserInventoryURI, user.UserAssetURI,
169 user.CanDoMask, user.WantDoMask,
170 user.AboutText, user.FirstLifeAboutText, user.Image,
171 user.FirstLifeImage, user.WebLoginKey, user.HomeRegionID,
172 user.GodLevel, user.UserFlags, user.CustomType, user.Partner);
173 }
174 catch (Exception e)
175 {
176 m_log.ErrorFormat("[USER DB] Error adding new profile, error: {0}", e.Message);
177 }
178 }
179
180 /// <summary>
181 /// update a user profile
182 /// </summary>
183 /// <param name="user">the profile to update</param>
184 /// <returns></returns>
185 override public bool UpdateUserProfile(UserProfileData user)
186 {
187 string sql = string.Format(@"UPDATE {0}
188 SET UUID = @uuid,
189 username = @username,
190 lastname = @lastname,
191 email = @email,
192 passwordHash = @passwordHash,
193 passwordSalt = @passwordSalt,
194 homeRegion = @homeRegion,
195 homeLocationX = @homeLocationX,
196 homeLocationY = @homeLocationY,
197 homeLocationZ = @homeLocationZ,
198 homeLookAtX = @homeLookAtX,
199 homeLookAtY = @homeLookAtY,
200 homeLookAtZ = @homeLookAtZ,
201 created = @created,
202 lastLogin = @lastLogin,
203 userInventoryURI = @userInventoryURI,
204 userAssetURI = @userAssetURI,
205 profileCanDoMask = @profileCanDoMask,
206 profileWantDoMask = @profileWantDoMask,
207 profileAboutText = @profileAboutText,
208 profileFirstText = @profileFirstText,
209 profileImage = @profileImage,
210 profileFirstImage = @profileFirstImage,
211 webLoginKey = @webLoginKey,
212 homeRegionID = @homeRegionID,
213 userFlags = @userFlags,
214 godLevel = @godLevel,
215 customType = @customType,
216 partner = @partner WHERE UUID = @keyUUUID;",m_usersTableName);
217 using (AutoClosingSqlCommand command = database.Query(sql))
218 {
219 command.Parameters.Add(database.CreateParameter("uuid", user.ID));
220 command.Parameters.Add(database.CreateParameter("username", user.FirstName));
221 command.Parameters.Add(database.CreateParameter("lastname", user.SurName));
222 command.Parameters.Add(database.CreateParameter("email", user.Email));
223 command.Parameters.Add(database.CreateParameter("passwordHash", user.PasswordHash));
224 command.Parameters.Add(database.CreateParameter("passwordSalt", user.PasswordSalt));
225 command.Parameters.Add(database.CreateParameter("homeRegion", user.HomeRegion));
226 command.Parameters.Add(database.CreateParameter("homeLocationX", user.HomeLocation.X));
227 command.Parameters.Add(database.CreateParameter("homeLocationY", user.HomeLocation.Y));
228 command.Parameters.Add(database.CreateParameter("homeLocationZ", user.HomeLocation.Z));
229 command.Parameters.Add(database.CreateParameter("homeLookAtX", user.HomeLookAt.X));
230 command.Parameters.Add(database.CreateParameter("homeLookAtY", user.HomeLookAt.Y));
231 command.Parameters.Add(database.CreateParameter("homeLookAtZ", user.HomeLookAt.Z));
232 command.Parameters.Add(database.CreateParameter("created", user.Created));
233 command.Parameters.Add(database.CreateParameter("lastLogin", user.LastLogin));
234 command.Parameters.Add(database.CreateParameter("userInventoryURI", user.UserInventoryURI));
235 command.Parameters.Add(database.CreateParameter("userAssetURI", user.UserAssetURI));
236 command.Parameters.Add(database.CreateParameter("profileCanDoMask", user.CanDoMask));
237 command.Parameters.Add(database.CreateParameter("profileWantDoMask", user.WantDoMask));
238 command.Parameters.Add(database.CreateParameter("profileAboutText", user.AboutText));
239 command.Parameters.Add(database.CreateParameter("profileFirstText", user.FirstLifeAboutText));
240 command.Parameters.Add(database.CreateParameter("profileImage", user.Image));
241 command.Parameters.Add(database.CreateParameter("profileFirstImage", user.FirstLifeImage));
242 command.Parameters.Add(database.CreateParameter("webLoginKey", user.WebLoginKey));
243 command.Parameters.Add(database.CreateParameter("homeRegionID", user.HomeRegionID));
244 command.Parameters.Add(database.CreateParameter("userFlags", user.UserFlags));
245 command.Parameters.Add(database.CreateParameter("godLevel", user.GodLevel));
246 command.Parameters.Add(database.CreateParameter("customType", user.CustomType));
247 command.Parameters.Add(database.CreateParameter("partner", user.Partner));
248 command.Parameters.Add(database.CreateParameter("keyUUUID", user.ID));
249
250 try
251 {
252 int affected = command.ExecuteNonQuery();
253 return (affected != 0);
254 }
255 catch (Exception e)
256 {
257 m_log.ErrorFormat("[USER DB] Error updating profile, error: {0}", e.Message);
258 }
259 }
260 return false;
261 }
262
263 #endregion
264
265 #region Agent table methods
266
267 /// <summary>
268 /// Returns a user session searching by name
269 /// </summary>
270 /// <param name="name">The account name</param>
271 /// <returns>The users session</returns>
272 override public UserAgentData GetAgentByName(string name)
273 {
274 return GetAgentByName(name.Split(' ')[0], name.Split(' ')[1]);
275 }
276
277 /// <summary>
278 /// Returns a user session by account name
279 /// </summary>
280 /// <param name="user">First part of the users account name</param>
281 /// <param name="last">Second part of the users account name</param>
282 /// <returns>The users session</returns>
283 override public UserAgentData GetAgentByName(string user, string last)
284 {
285 UserProfileData profile = GetUserByName(user, last);
286 return GetAgentByUUID(profile.ID);
287 }
288
289 /// <summary>
290 /// Returns an agent session by account UUID
291 /// </summary>
292 /// <param name="uuid">The accounts UUID</param>
293 /// <returns>The users session</returns>
294 override public UserAgentData GetAgentByUUID(UUID uuid)
295 {
296 string sql = string.Format("SELECT * FROM {0} WHERE UUID = @uuid", m_agentsTableName);
297 using (AutoClosingSqlCommand command = database.Query(sql))
298 {
299 command.Parameters.Add(database.CreateParameter("uuid", uuid));
300 try
301 {
302 using (SqlDataReader reader = command.ExecuteReader())
303 {
304 return readAgentRow(reader);
305 }
306 }
307 catch (Exception e)
308 {
309 m_log.ErrorFormat("[USER DB] Error updating agentdata by UUID, error: {0}", e.Message);
310 return null;
311 }
312 }
313 }
314
315 /// <summary>
316 /// Creates a new agent
317 /// </summary>
318 /// <param name="agent">The agent to create</param>
319 override public void AddNewUserAgent(UserAgentData agent)
320 {
321 try
322 {
323 InsertUpdateAgentRow(agent);
324 }
325 catch (Exception e)
326 {
327 m_log.ErrorFormat("[USER DB] Error adding new agentdata, error: {0}", e.Message);
328 }
329 }
330
331 #endregion
332
333 #region User Friends List Data
334
335 /// <summary>
336 /// Add a new friend in the friendlist
337 /// </summary>
338 /// <param name="friendlistowner">UUID of the friendlist owner</param>
339 /// <param name="friend">Friend's UUID</param>
340 /// <param name="perms">Permission flag</param>
341 override public void AddNewUserFriend(UUID friendlistowner, UUID friend, uint perms)
342 {
343 int dtvalue = Util.UnixTimeSinceEpoch();
344 string sql = string.Format(@"INSERT INTO {0}
345 (ownerID,friendID,friendPerms,datetimestamp)
346 VALUES
347 (@ownerID,@friendID,@friendPerms,@datetimestamp)", m_userFriendsTableName);
348 using (AutoClosingSqlCommand command = database.Query(sql))
349 {
350 command.Parameters.Add(database.CreateParameter("ownerID", friendlistowner));
351 command.Parameters.Add(database.CreateParameter("friendID", friend));
352 command.Parameters.Add(database.CreateParameter("friendPerms", perms));
353 command.Parameters.Add(database.CreateParameter("datetimestamp", dtvalue));
354 command.ExecuteNonQuery();
355
356 try
357 {
358 sql = string.Format(@"INSERT INTO {0}
359 (ownerID,friendID,friendPerms,datetimestamp)
360 VALUES
361 (@friendID,@ownerID,@friendPerms,@datetimestamp)", m_userFriendsTableName);
362 command.CommandText = sql;
363 command.ExecuteNonQuery();
364 }
365 catch (Exception e)
366 {
367 m_log.ErrorFormat("[USER DB] Error adding new userfriend, error: {0}", e.Message);
368 return;
369 }
370 }
371 }
372
373 /// <summary>
374 /// Remove an friend from the friendlist
375 /// </summary>
376 /// <param name="friendlistowner">UUID of the friendlist owner</param>
377 /// <param name="friend">UUID of the not-so-friendly user to remove from the list</param>
378 override public void RemoveUserFriend(UUID friendlistowner, UUID friend)
379 {
380 string sql = string.Format(@"DELETE from {0}
381 WHERE ownerID = @ownerID
382 AND friendID = @friendID", m_userFriendsTableName);
383 using (AutoClosingSqlCommand command = database.Query(sql))
384 {
385 command.Parameters.Add(database.CreateParameter("@ownerID", friendlistowner));
386 command.Parameters.Add(database.CreateParameter("@friendID", friend));
387 command.ExecuteNonQuery();
388 sql = string.Format(@"DELETE from {0}
389 WHERE ownerID = @friendID
390 AND friendID = @ownerID", m_userFriendsTableName);
391 command.CommandText = sql;
392 try
393 {
394 command.ExecuteNonQuery();
395 }
396 catch (Exception e)
397 {
398 m_log.ErrorFormat("[USER DB] Error removing userfriend, error: {0}", e.Message);
399 }
400 }
401 }
402
403 /// <summary>
404 /// Update friendlist permission flag for a friend
405 /// </summary>
406 /// <param name="friendlistowner">UUID of the friendlist owner</param>
407 /// <param name="friend">UUID of the friend</param>
408 /// <param name="perms">new permission flag</param>
409 override public void UpdateUserFriendPerms(UUID friendlistowner, UUID friend, uint perms)
410 {
411 string sql = string.Format(@"UPDATE {0} SET friendPerms = @friendPerms
412 WHERE ownerID = @ownerID
413 AND friendID = @friendID", m_userFriendsTableName);
414 using (AutoClosingSqlCommand command = database.Query(sql))
415 {
416 command.Parameters.Add(database.CreateParameter("@ownerID", friendlistowner));
417 command.Parameters.Add(database.CreateParameter("@friendID", friend));
418 command.Parameters.Add(database.CreateParameter("@friendPerms", perms));
419
420 try
421 {
422 command.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 (AutoClosingSqlCommand command = database.Query(sql))
447 {
448 command.Parameters.Add(database.CreateParameter("@ownerID", friendlistowner));
449 try
450 {
451 using (SqlDataReader reader = command.ExecuteReader())
452 {
453 while (reader.Read())
454 {
455 FriendListItem fli = new FriendListItem();
456 fli.FriendListOwner = new UUID((Guid)reader["ownerID"]);
457 fli.Friend = new UUID((Guid)reader["friendID"]);
458 fli.FriendPerms = (uint)Convert.ToInt32(reader["friendPerms"]);
459
460 // This is not a real column in the database table, it's a joined column from the opposite record
461 fli.FriendListOwnerPerms = (uint)Convert.ToInt32(reader["ownerperms"]);
462 friendList.Add(fli);
463 }
464 }
465 }
466 catch (Exception e)
467 {
468 m_log.ErrorFormat("[USER DB] Error updating userfriend, error: {0}", e.Message);
469 }
470 }
471 return friendList;
472 }
473
474 override public Dictionary<UUID, FriendRegionInfo> GetFriendRegionInfos (List<UUID> uuids)
475 {
476 Dictionary<UUID, FriendRegionInfo> infos = new Dictionary<UUID,FriendRegionInfo>();
477 try
478 {
479 foreach (UUID uuid in uuids)
480 {
481 string sql = string.Format(@"SELECT agentOnline,currentHandle
482 FROM {0} WHERE UUID = @uuid", m_agentsTableName);
483 using (AutoClosingSqlCommand command = database.Query(sql))
484 {
485 command.Parameters.Add(database.CreateParameter("@uuid", uuid));
486 using (SqlDataReader reader = command.ExecuteReader())
487 {
488 while (reader.Read())
489 {
490 FriendRegionInfo fri = new FriendRegionInfo();
491 fri.isOnline = (byte)reader["agentOnline"] != 0;
492 fri.regionHandle = Convert.ToUInt64(reader["currentHandle"].ToString());
493
494 infos[uuid] = fri;
495 }
496 }
497 }
498 }
499 }
500 catch (Exception e)
501 {
502 m_log.Warn("[MSSQL]: Got exception on trying to find friends regions:", e);
503 }
504
505 return infos;
506 }
507 #endregion
508
509 #region Money functions (not used)
510
511 /// <summary>
512 /// Performs a money transfer request between two accounts
513 /// </summary>
514 /// <param name="from">The senders account ID</param>
515 /// <param name="to">The receivers account ID</param>
516 /// <param name="amount">The amount to transfer</param>
517 /// <returns>false</returns>
518 override public bool MoneyTransferRequest(UUID from, UUID to, uint amount)
519 {
520 return false;
521 }
522
523 /// <summary>
524 /// Performs an inventory transfer request between two accounts
525 /// </summary>
526 /// <remarks>TODO: Move to inventory server</remarks>
527 /// <param name="from">The senders account ID</param>
528 /// <param name="to">The receivers account ID</param>
529 /// <param name="item">The item to transfer</param>
530 /// <returns>false</returns>
531 override public bool InventoryTransferRequest(UUID from, UUID to, UUID item)
532 {
533 return false;
534 }
535
536 #endregion
537
538 #region Appearance methods
539
540 /// <summary>
541 /// Gets the user appearance.
542 /// </summary>
543 /// <param name="user">The user.</param>
544 /// <returns></returns>
545 override public AvatarAppearance GetUserAppearance(UUID user)
546 {
547 try
548 {
549 AvatarAppearance appearance = new AvatarAppearance();
550 string sql = "SELECT * FROM avatarappearance WHERE owner = @UUID";
551 using (AutoClosingSqlCommand command = database.Query(sql))
552 {
553 command.Parameters.Add(database.CreateParameter("@UUID", user));
554 using (SqlDataReader reader = command.ExecuteReader())
555 {
556 if (reader.Read())
557 appearance = readUserAppearance(reader);
558 else
559 {
560 m_log.WarnFormat("[USER DB] No appearance found for user {0}", user.ToString());
561 return null;
562 }
563
564 }
565 }
566
567 appearance.SetAttachments(GetUserAttachments(user));
568
569 return appearance;
570 }
571 catch (Exception e)
572 {
573 m_log.ErrorFormat("[USER DB] Error updating userfriend, error: {0}", e.Message);
574 }
575 return null;
576 }
577
578 /// <summary>
579 /// Update a user appearence into database
580 /// </summary>
581 /// <param name="user">the used UUID</param>
582 /// <param name="appearance">the appearence</param>
583 override public void UpdateUserAppearance(UUID user, AvatarAppearance appearance)
584 {
585 string sql = @"DELETE FROM avatarappearance WHERE owner=@owner;
586 INSERT INTO avatarappearance
587 (owner, serial, visual_params, texture, avatar_height,
588 body_item, body_asset, skin_item, skin_asset, hair_item,
589 hair_asset, eyes_item, eyes_asset, shirt_item, shirt_asset,
590 pants_item, pants_asset, shoes_item, shoes_asset, socks_item,
591 socks_asset, jacket_item, jacket_asset, gloves_item, gloves_asset,
592 undershirt_item, undershirt_asset, underpants_item, underpants_asset,
593 skirt_item, skirt_asset)
594 VALUES
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
603 using (AutoClosingSqlCommand cmd = database.Query(sql))
604 {
605 cmd.Parameters.Add(database.CreateParameter("@owner", appearance.Owner));
606 cmd.Parameters.Add(database.CreateParameter("@serial", appearance.Serial));
607 cmd.Parameters.Add(database.CreateParameter("@visual_params", appearance.VisualParams));
608 cmd.Parameters.Add(database.CreateParameter("@texture", appearance.Texture.GetBytes()));
609 cmd.Parameters.Add(database.CreateParameter("@avatar_height", appearance.AvatarHeight));
610 cmd.Parameters.Add(database.CreateParameter("@body_item", appearance.BodyItem));
611 cmd.Parameters.Add(database.CreateParameter("@body_asset", appearance.BodyAsset));
612 cmd.Parameters.Add(database.CreateParameter("@skin_item", appearance.SkinItem));
613 cmd.Parameters.Add(database.CreateParameter("@skin_asset", appearance.SkinAsset));
614 cmd.Parameters.Add(database.CreateParameter("@hair_item", appearance.HairItem));
615 cmd.Parameters.Add(database.CreateParameter("@hair_asset", appearance.HairAsset));
616 cmd.Parameters.Add(database.CreateParameter("@eyes_item", appearance.EyesItem));
617 cmd.Parameters.Add(database.CreateParameter("@eyes_asset", appearance.EyesAsset));
618 cmd.Parameters.Add(database.CreateParameter("@shirt_item", appearance.ShirtItem));
619 cmd.Parameters.Add(database.CreateParameter("@shirt_asset", appearance.ShirtAsset));
620 cmd.Parameters.Add(database.CreateParameter("@pants_item", appearance.PantsItem));
621 cmd.Parameters.Add(database.CreateParameter("@pants_asset", appearance.PantsAsset));
622 cmd.Parameters.Add(database.CreateParameter("@shoes_item", appearance.ShoesItem));
623 cmd.Parameters.Add(database.CreateParameter("@shoes_asset", appearance.ShoesAsset));
624 cmd.Parameters.Add(database.CreateParameter("@socks_item", appearance.SocksItem));
625 cmd.Parameters.Add(database.CreateParameter("@socks_asset", appearance.SocksAsset));
626 cmd.Parameters.Add(database.CreateParameter("@jacket_item", appearance.JacketItem));
627 cmd.Parameters.Add(database.CreateParameter("@jacket_asset", appearance.JacketAsset));
628 cmd.Parameters.Add(database.CreateParameter("@gloves_item", appearance.GlovesItem));
629 cmd.Parameters.Add(database.CreateParameter("@gloves_asset", appearance.GlovesAsset));
630 cmd.Parameters.Add(database.CreateParameter("@undershirt_item", appearance.UnderShirtItem));
631 cmd.Parameters.Add(database.CreateParameter("@undershirt_asset", appearance.UnderShirtAsset));
632 cmd.Parameters.Add(database.CreateParameter("@underpants_item", appearance.UnderPantsItem));
633 cmd.Parameters.Add(database.CreateParameter("@underpants_asset", appearance.UnderPantsAsset));
634 cmd.Parameters.Add(database.CreateParameter("@skirt_item", appearance.SkirtItem));
635 cmd.Parameters.Add(database.CreateParameter("@skirt_asset", appearance.SkirtAsset));
636
637 try
638 {
639 cmd.ExecuteNonQuery();
640 }
641 catch (Exception e)
642 {
643 m_log.ErrorFormat("[USER DB] Error updating user appearance, error: {0}", e.Message);
644 }
645 }
646 UpdateUserAttachments(user, appearance.GetAttachments());
647 }
648
649 #endregion
650
651 #region Attachment methods
652
653 /// <summary>
654 /// Gets all attachment of a agent.
655 /// </summary>
656 /// <param name="agentID">agent ID.</param>
657 /// <returns></returns>
658 public Hashtable GetUserAttachments(UUID agentID)
659 {
660 Hashtable returnTable = new Hashtable();
661 string sql = "select attachpoint, item, asset from avatarattachments where UUID = @uuid";
662 using (AutoClosingSqlCommand command = database.Query(sql, database.CreateParameter("@uuid", agentID)))
663 {
664 using (SqlDataReader reader = command.ExecuteReader())
665 {
666 while (reader.Read())
667 {
668 int attachpoint = Convert.ToInt32(reader["attachpoint"]);
669 if (returnTable.ContainsKey(attachpoint))
670 continue;
671 Hashtable item = new Hashtable();
672 item.Add("item", reader["item"].ToString());
673 item.Add("asset", reader["asset"].ToString());
674
675 returnTable.Add(attachpoint, item);
676 }
677 }
678 }
679 return returnTable;
680 }
681
682 /// <summary>
683 /// Updates all attachments of the agent.
684 /// </summary>
685 /// <param name="agentID">agentID.</param>
686 /// <param name="data">data with all items on attachmentpoints</param>
687 public void UpdateUserAttachments(UUID agentID, Hashtable data)
688 {
689 string sql = "DELETE FROM avatarattachments WHERE UUID = @uuid";
690
691 using (AutoClosingSqlCommand command = database.Query(sql))
692 {
693 command.Parameters.Add(database.CreateParameter("uuid", agentID));
694 command.ExecuteNonQuery();
695 }
696 if (data == null)
697 return;
698
699 sql = @"INSERT INTO avatarattachments (UUID, attachpoint, item, asset)
700 VALUES (@uuid, @attachpoint, @item, @asset)";
701
702 using (AutoClosingSqlCommand command = database.Query(sql))
703 {
704 bool firstTime = true;
705 foreach (DictionaryEntry e in data)
706 {
707 int attachpoint = Convert.ToInt32(e.Key);
708
709 Hashtable item = (Hashtable)e.Value;
710
711 if (firstTime)
712 {
713 command.Parameters.Add(database.CreateParameter("@uuid", agentID));
714 command.Parameters.Add(database.CreateParameter("@attachpoint", attachpoint));
715 command.Parameters.Add(database.CreateParameter("@item", new UUID(item["item"].ToString())));
716 command.Parameters.Add(database.CreateParameter("@asset", new UUID(item["asset"].ToString())));
717 firstTime = false;
718 }
719 command.Parameters["@uuid"].Value = agentID.Guid; //.ToString();
720 command.Parameters["@attachpoint"].Value = attachpoint;
721 command.Parameters["@item"].Value = new Guid(item["item"].ToString());
722 command.Parameters["@asset"].Value = new Guid(item["asset"].ToString());
723
724 try
725 {
726 command.ExecuteNonQuery();
727 }
728 catch (Exception ex)
729 {
730 m_log.DebugFormat("[USER DB] : Error adding user attachment. {0}", ex.Message);
731 }
732 }
733 }
734 }
735
736 /// <summary>
737 /// Resets all attachments of a agent in the database.
738 /// </summary>
739 /// <param name="agentID">agentID.</param>
740 override public void ResetAttachments(UUID agentID)
741 {
742 string sql = "UPDATE avatarattachments SET asset = '00000000-0000-0000-0000-000000000000' WHERE UUID = @uuid";
743 using (AutoClosingSqlCommand command = database.Query(sql))
744 {
745 command.Parameters.Add(database.CreateParameter("uuid", agentID));
746 command.ExecuteNonQuery();
747 }
748 }
749
750 override public void LogoutUsers(UUID regionID)
751 {
752 }
753
754 #endregion
755
756 #region Other public methods
757
758 /// <summary>
759 ///
760 /// </summary>
761 /// <param name="queryID"></param>
762 /// <param name="query"></param>
763 /// <returns></returns>
764 override public List<AvatarPickerAvatar> GeneratePickerResults(UUID queryID, string query)
765 {
766 List<AvatarPickerAvatar> returnlist = new List<AvatarPickerAvatar>();
767 string[] querysplit = query.Split(' ');
768 if (querysplit.Length == 2)
769 {
770 try
771 {
772 string sql = string.Format(@"SELECT UUID,username,lastname FROM {0}
773 WHERE username LIKE @first AND lastname LIKE @second", m_usersTableName);
774 using (AutoClosingSqlCommand command = database.Query(sql))
775 {
776 //Add wildcard to the search
777 command.Parameters.Add(database.CreateParameter("first", querysplit[0] + "%"));
778 command.Parameters.Add(database.CreateParameter("second", querysplit[1] + "%"));
779 using (SqlDataReader reader = command.ExecuteReader())
780 {
781 while (reader.Read())
782 {
783 AvatarPickerAvatar user = new AvatarPickerAvatar();
784 user.AvatarID = new UUID((Guid)reader["UUID"]);
785 user.firstName = (string)reader["username"];
786 user.lastName = (string)reader["lastname"];
787 returnlist.Add(user);
788 }
789 }
790 }
791 }
792 catch (Exception e)
793 {
794 m_log.Error(e.ToString());
795 }
796 }
797 else if (querysplit.Length == 1)
798 {
799 try
800 {
801 string sql = string.Format(@"SELECT UUID,username,lastname FROM {0}
802 WHERE username LIKE @first OR lastname LIKE @first", m_usersTableName);
803 using (AutoClosingSqlCommand command = database.Query(sql))
804 {
805 command.Parameters.Add(database.CreateParameter("first", querysplit[0] + "%"));
806
807 using (SqlDataReader reader = command.ExecuteReader())
808 {
809 while (reader.Read())
810 {
811 AvatarPickerAvatar user = new AvatarPickerAvatar();
812 user.AvatarID = new UUID((Guid)reader["UUID"]);
813 user.firstName = (string)reader["username"];
814 user.lastName = (string)reader["lastname"];
815 returnlist.Add(user);
816 }
817 }
818 }
819 }
820 catch (Exception e)
821 {
822 m_log.Error(e.ToString());
823 }
824 }
825 return returnlist;
826 }
827
828 /// <summary>
829 /// Store a weblogin key
830 /// </summary>
831 /// <param name="AgentID">The agent UUID</param>
832 /// <param name="WebLoginKey">the WebLogin Key</param>
833 /// <remarks>unused ?</remarks>
834 override public void StoreWebLoginKey(UUID AgentID, UUID WebLoginKey)
835 {
836 UserProfileData user = GetUserByUUID(AgentID);
837 user.WebLoginKey = WebLoginKey;
838 UpdateUserProfile(user);
839 }
840
841 /// <summary>
842 /// Database provider name
843 /// </summary>
844 /// <returns>Provider name</returns>
845 override public string Name
846 {
847 get { return "MSSQL Userdata Interface"; }
848 }
849
850 /// <summary>
851 /// Database provider version
852 /// </summary>
853 /// <returns>provider version</returns>
854 override public string Version
855 {
856 get { return database.getVersion(); }
857 }
858
859 #endregion
860
861 #region Private functions
862
863 /// <summary>
864 /// Reads a one item from an SQL result
865 /// </summary>
866 /// <param name="reader">The SQL Result</param>
867 /// <returns>the item read</returns>
868 private static AvatarAppearance readUserAppearance(SqlDataReader reader)
869 {
870 try
871 {
872 AvatarAppearance appearance = new AvatarAppearance();
873
874 appearance.Owner = new UUID((Guid)reader["owner"]);
875 appearance.Serial = Convert.ToInt32(reader["serial"]);
876 appearance.VisualParams = (byte[])reader["visual_params"];
877 appearance.Texture = new Primitive.TextureEntry((byte[])reader["texture"], 0, ((byte[])reader["texture"]).Length);
878 appearance.AvatarHeight = (float)Convert.ToDouble(reader["avatar_height"]);
879 appearance.BodyItem = new UUID((Guid)reader["body_item"]);
880 appearance.BodyAsset = new UUID((Guid)reader["body_asset"]);
881 appearance.SkinItem = new UUID((Guid)reader["skin_item"]);
882 appearance.SkinAsset = new UUID((Guid)reader["skin_asset"]);
883 appearance.HairItem = new UUID((Guid)reader["hair_item"]);
884 appearance.HairAsset = new UUID((Guid)reader["hair_asset"]);
885 appearance.EyesItem = new UUID((Guid)reader["eyes_item"]);
886 appearance.EyesAsset = new UUID((Guid)reader["eyes_asset"]);
887 appearance.ShirtItem = new UUID((Guid)reader["shirt_item"]);
888 appearance.ShirtAsset = new UUID((Guid)reader["shirt_asset"]);
889 appearance.PantsItem = new UUID((Guid)reader["pants_item"]);
890 appearance.PantsAsset = new UUID((Guid)reader["pants_asset"]);
891 appearance.ShoesItem = new UUID((Guid)reader["shoes_item"]);
892 appearance.ShoesAsset = new UUID((Guid)reader["shoes_asset"]);
893 appearance.SocksItem = new UUID((Guid)reader["socks_item"]);
894 appearance.SocksAsset = new UUID((Guid)reader["socks_asset"]);
895 appearance.JacketItem = new UUID((Guid)reader["jacket_item"]);
896 appearance.JacketAsset = new UUID((Guid)reader["jacket_asset"]);
897 appearance.GlovesItem = new UUID((Guid)reader["gloves_item"]);
898 appearance.GlovesAsset = new UUID((Guid)reader["gloves_asset"]);
899 appearance.UnderShirtItem = new UUID((Guid)reader["undershirt_item"]);
900 appearance.UnderShirtAsset = new UUID((Guid)reader["undershirt_asset"]);
901 appearance.UnderPantsItem = new UUID((Guid)reader["underpants_item"]);
902 appearance.UnderPantsAsset = new UUID((Guid)reader["underpants_asset"]);
903 appearance.SkirtItem = new UUID((Guid)reader["skirt_item"]);
904 appearance.SkirtAsset = new UUID((Guid)reader["skirt_asset"]);
905
906 return appearance;
907 }
908 catch (SqlException e)
909 {
910 m_log.Error(e.ToString());
911 }
912
913 return null;
914 }
915
916 /// <summary>
917 /// Insert/Update a agent row in the DB.
918 /// </summary>
919 /// <param name="agentdata">agentdata.</param>
920 private void InsertUpdateAgentRow(UserAgentData agentdata)
921 {
922 string sql = @"
923
924IF EXISTS (SELECT * FROM agents WHERE UUID = @UUID)
925 BEGIN
926 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
927 WHERE UUID = @UUID
928 END
929ELSE
930 BEGIN
931 INSERT INTO
932 agents (UUID, sessionID, secureSessionID, agentIP, agentPort, agentOnline, loginTime, logoutTime, currentRegion, currentHandle, currentPos) VALUES
933 (@UUID, @sessionID, @secureSessionID, @agentIP, @agentPort, @agentOnline, @loginTime, @logoutTime, @currentRegion, @currentHandle, @currentPos)
934 END
935";
936
937 using (AutoClosingSqlCommand command = database.Query(sql))
938 {
939 command.Parameters.Add(database.CreateParameter("@UUID", agentdata.ProfileID));
940 command.Parameters.Add(database.CreateParameter("@sessionID", agentdata.SessionID));
941 command.Parameters.Add(database.CreateParameter("@secureSessionID", agentdata.SecureSessionID));
942 command.Parameters.Add(database.CreateParameter("@agentIP", agentdata.AgentIP));
943 command.Parameters.Add(database.CreateParameter("@agentPort", agentdata.AgentPort));
944 command.Parameters.Add(database.CreateParameter("@agentOnline", agentdata.AgentOnline));
945 command.Parameters.Add(database.CreateParameter("@loginTime", agentdata.LoginTime));
946 command.Parameters.Add(database.CreateParameter("@logoutTime", agentdata.LogoutTime));
947 command.Parameters.Add(database.CreateParameter("@currentRegion", agentdata.Region));
948 command.Parameters.Add(database.CreateParameter("@currentHandle", agentdata.Handle));
949 command.Parameters.Add(database.CreateParameter("@currentPos", "<" + ((int)agentdata.Position.X) + "," + ((int)agentdata.Position.Y) + "," + ((int)agentdata.Position.Z) + ">"));
950
951 command.Transaction = command.Connection.BeginTransaction(IsolationLevel.Serializable);
952 try
953 {
954 if (command.ExecuteNonQuery() > 0)
955 {
956 command.Transaction.Commit();
957 return;
958 }
959
960 command.Transaction.Rollback();
961 return;
962 }
963 catch (Exception e)
964 {
965 command.Transaction.Rollback();
966 m_log.Error(e.ToString());
967 return;
968 }
969 }
970
971 }
972
973 /// <summary>
974 /// Reads an agent row from a database reader
975 /// </summary>
976 /// <param name="reader">An active database reader</param>
977 /// <returns>A user session agent</returns>
978 private UserAgentData readAgentRow(SqlDataReader reader)
979 {
980 UserAgentData retval = new UserAgentData();
981
982 if (reader.Read())
983 {
984 // Agent IDs
985 retval.ProfileID = new UUID((Guid)reader["UUID"]);
986 retval.SessionID = new UUID((Guid)reader["sessionID"]);
987 retval.SecureSessionID = new UUID((Guid)reader["secureSessionID"]);
988
989 // Agent Who?
990 retval.AgentIP = (string)reader["agentIP"];
991 retval.AgentPort = Convert.ToUInt32(reader["agentPort"].ToString());
992 retval.AgentOnline = Convert.ToInt32(reader["agentOnline"].ToString()) != 0;
993
994 // Login/Logout times (UNIX Epoch)
995 retval.LoginTime = Convert.ToInt32(reader["loginTime"].ToString());
996 retval.LogoutTime = Convert.ToInt32(reader["logoutTime"].ToString());
997
998 // Current position
999 retval.Region = new UUID((Guid)reader["currentRegion"]);
1000 retval.Handle = Convert.ToUInt64(reader["currentHandle"].ToString());
1001 Vector3 tmp_v;
1002 Vector3.TryParse((string)reader["currentPos"], out tmp_v);
1003 retval.Position = tmp_v;
1004
1005 }
1006 else
1007 {
1008 return null;
1009 }
1010 return retval;
1011 }
1012
1013 /// <summary>
1014 /// Creates a new user and inserts it into the database
1015 /// </summary>
1016 /// <param name="uuid">User ID</param>
1017 /// <param name="username">First part of the login</param>
1018 /// <param name="lastname">Second part of the login</param>
1019 /// <param name="email">Email of person</param>
1020 /// <param name="passwordHash">A salted hash of the users password</param>
1021 /// <param name="passwordSalt">The salt used for the password hash</param>
1022 /// <param name="homeRegion">A regionHandle of the users home region</param>
1023 /// <param name="homeLocX">Home region position vector</param>
1024 /// <param name="homeLocY">Home region position vector</param>
1025 /// <param name="homeLocZ">Home region position vector</param>
1026 /// <param name="homeLookAtX">Home region 'look at' vector</param>
1027 /// <param name="homeLookAtY">Home region 'look at' vector</param>
1028 /// <param name="homeLookAtZ">Home region 'look at' vector</param>
1029 /// <param name="created">Account created (unix timestamp)</param>
1030 /// <param name="lastlogin">Last login (unix timestamp)</param>
1031 /// <param name="inventoryURI">Users inventory URI</param>
1032 /// <param name="assetURI">Users asset URI</param>
1033 /// <param name="canDoMask">I can do mask</param>
1034 /// <param name="wantDoMask">I want to do mask</param>
1035 /// <param name="aboutText">Profile text</param>
1036 /// <param name="firstText">Firstlife text</param>
1037 /// <param name="profileImage">UUID for profile image</param>
1038 /// <param name="firstImage">UUID for firstlife image</param>
1039 /// <param name="webLoginKey">web login key</param>
1040 /// <param name="homeRegionID">homeregion UUID</param>
1041 /// <param name="godLevel">has the user godlevel</param>
1042 /// <param name="userFlags">unknown</param>
1043 /// <param name="customType">unknown</param>
1044 /// <param name="partnerID">UUID of partner</param>
1045 /// <returns>Success?</returns>
1046 private void InsertUserRow(UUID uuid, string username, string lastname, string email, string passwordHash,
1047 string passwordSalt, UInt64 homeRegion, float homeLocX, float homeLocY, float homeLocZ,
1048 float homeLookAtX, float homeLookAtY, float homeLookAtZ, int created, int lastlogin,
1049 string inventoryURI, string assetURI, uint canDoMask, uint wantDoMask,
1050 string aboutText, string firstText,
1051 UUID profileImage, UUID firstImage, UUID webLoginKey, UUID homeRegionID,
1052 int godLevel, int userFlags, string customType, UUID partnerID)
1053 {
1054 string sql = string.Format(@"INSERT INTO {0}
1055 ([UUID], [username], [lastname], [email], [passwordHash], [passwordSalt],
1056 [homeRegion], [homeLocationX], [homeLocationY], [homeLocationZ], [homeLookAtX],
1057 [homeLookAtY], [homeLookAtZ], [created], [lastLogin], [userInventoryURI],
1058 [userAssetURI], [profileCanDoMask], [profileWantDoMask], [profileAboutText],
1059 [profileFirstText], [profileImage], [profileFirstImage], [webLoginKey],
1060 [homeRegionID], [userFlags], [godLevel], [customType], [partner])
1061 VALUES
1062 (@UUID, @username, @lastname, @email, @passwordHash, @passwordSalt,
1063 @homeRegion, @homeLocationX, @homeLocationY, @homeLocationZ, @homeLookAtX,
1064 @homeLookAtY, @homeLookAtZ, @created, @lastLogin, @userInventoryURI,
1065 @userAssetURI, @profileCanDoMask, @profileWantDoMask, @profileAboutText,
1066 @profileFirstText, @profileImage, @profileFirstImage, @webLoginKey,
1067 @homeRegionID, @userFlags, @godLevel, @customType, @partner)", m_usersTableName);
1068
1069 try
1070 {
1071 using (AutoClosingSqlCommand command = database.Query(sql))
1072 {
1073 command.Parameters.Add(database.CreateParameter("UUID", uuid));
1074 command.Parameters.Add(database.CreateParameter("username", username));
1075 command.Parameters.Add(database.CreateParameter("lastname", lastname));
1076 command.Parameters.Add(database.CreateParameter("email", email));
1077 command.Parameters.Add(database.CreateParameter("passwordHash", passwordHash));
1078 command.Parameters.Add(database.CreateParameter("passwordSalt", passwordSalt));
1079 command.Parameters.Add(database.CreateParameter("homeRegion", homeRegion));
1080 command.Parameters.Add(database.CreateParameter("homeLocationX", homeLocX));
1081 command.Parameters.Add(database.CreateParameter("homeLocationY", homeLocY));
1082 command.Parameters.Add(database.CreateParameter("homeLocationZ", homeLocZ));
1083 command.Parameters.Add(database.CreateParameter("homeLookAtX", homeLookAtX));
1084 command.Parameters.Add(database.CreateParameter("homeLookAtY", homeLookAtY));
1085 command.Parameters.Add(database.CreateParameter("homeLookAtZ", homeLookAtZ));
1086 command.Parameters.Add(database.CreateParameter("created", created));
1087 command.Parameters.Add(database.CreateParameter("lastLogin", lastlogin));
1088 command.Parameters.Add(database.CreateParameter("userInventoryURI", inventoryURI));
1089 command.Parameters.Add(database.CreateParameter("userAssetURI", assetURI));
1090 command.Parameters.Add(database.CreateParameter("profileCanDoMask", canDoMask));
1091 command.Parameters.Add(database.CreateParameter("profileWantDoMask", wantDoMask));
1092 command.Parameters.Add(database.CreateParameter("profileAboutText", aboutText));
1093 command.Parameters.Add(database.CreateParameter("profileFirstText", firstText));
1094 command.Parameters.Add(database.CreateParameter("profileImage", profileImage));
1095 command.Parameters.Add(database.CreateParameter("profileFirstImage", firstImage));
1096 command.Parameters.Add(database.CreateParameter("webLoginKey", webLoginKey));
1097 command.Parameters.Add(database.CreateParameter("homeRegionID", homeRegionID));
1098 command.Parameters.Add(database.CreateParameter("userFlags", userFlags));
1099 command.Parameters.Add(database.CreateParameter("godLevel", godLevel));
1100 command.Parameters.Add(database.CreateParameter("customType", customType));
1101 command.Parameters.Add(database.CreateParameter("partner", partnerID));
1102
1103 command.ExecuteNonQuery();
1104 return;
1105 }
1106 }
1107 catch (Exception e)
1108 {
1109 m_log.Error(e.ToString());
1110 return;
1111 }
1112 }
1113
1114 /// <summary>
1115 /// Reads a user profile from an active data reader
1116 /// </summary>
1117 /// <param name="reader">An active database reader</param>
1118 /// <returns>A user profile</returns>
1119 private static UserProfileData ReadUserRow(SqlDataReader reader)
1120 {
1121 UserProfileData retval = new UserProfileData();
1122
1123 if (reader.Read())
1124 {
1125 retval.ID = new UUID((Guid)reader["UUID"]);
1126 retval.FirstName = (string)reader["username"];
1127 retval.SurName = (string)reader["lastname"];
1128 if (reader.IsDBNull(reader.GetOrdinal("email")))
1129 retval.Email = "";
1130 else
1131 retval.Email = (string)reader["email"];
1132
1133 retval.PasswordHash = (string)reader["passwordHash"];
1134 retval.PasswordSalt = (string)reader["passwordSalt"];
1135
1136 retval.HomeRegion = Convert.ToUInt64(reader["homeRegion"].ToString());
1137 retval.HomeLocation = new Vector3(
1138 Convert.ToSingle(reader["homeLocationX"].ToString()),
1139 Convert.ToSingle(reader["homeLocationY"].ToString()),
1140 Convert.ToSingle(reader["homeLocationZ"].ToString()));
1141 retval.HomeLookAt = new Vector3(
1142 Convert.ToSingle(reader["homeLookAtX"].ToString()),
1143 Convert.ToSingle(reader["homeLookAtY"].ToString()),
1144 Convert.ToSingle(reader["homeLookAtZ"].ToString()));
1145
1146 if (reader.IsDBNull(reader.GetOrdinal("homeRegionID")))
1147 retval.HomeRegionID = UUID.Zero;
1148 else
1149 retval.HomeRegionID = new UUID((Guid)reader["homeRegionID"]);
1150
1151 retval.Created = Convert.ToInt32(reader["created"].ToString());
1152 retval.LastLogin = Convert.ToInt32(reader["lastLogin"].ToString());
1153
1154 if (reader.IsDBNull(reader.GetOrdinal("userInventoryURI")))
1155 retval.UserInventoryURI = "";
1156 else
1157 retval.UserInventoryURI = (string)reader["userInventoryURI"];
1158
1159 if (reader.IsDBNull(reader.GetOrdinal("userAssetURI")))
1160 retval.UserAssetURI = "";
1161 else
1162 retval.UserAssetURI = (string)reader["userAssetURI"];
1163
1164 retval.CanDoMask = Convert.ToUInt32(reader["profileCanDoMask"].ToString());
1165 retval.WantDoMask = Convert.ToUInt32(reader["profileWantDoMask"].ToString());
1166
1167
1168 if (reader.IsDBNull(reader.GetOrdinal("profileAboutText")))
1169 retval.AboutText = "";
1170 else
1171 retval.AboutText = (string)reader["profileAboutText"];
1172
1173 if (reader.IsDBNull(reader.GetOrdinal("profileFirstText")))
1174 retval.FirstLifeAboutText = "";
1175 else
1176 retval.FirstLifeAboutText = (string)reader["profileFirstText"];
1177
1178 if (reader.IsDBNull(reader.GetOrdinal("profileImage")))
1179 retval.Image = UUID.Zero;
1180 else
1181 retval.Image = new UUID((Guid)reader["profileImage"]);
1182
1183 if (reader.IsDBNull(reader.GetOrdinal("profileFirstImage")))
1184 retval.Image = UUID.Zero;
1185 else
1186 retval.FirstLifeImage = new UUID((Guid)reader["profileFirstImage"]);
1187
1188 if (reader.IsDBNull(reader.GetOrdinal("webLoginKey")))
1189 retval.WebLoginKey = UUID.Zero;
1190 else
1191 retval.WebLoginKey = new UUID((Guid)reader["webLoginKey"]);
1192
1193 retval.UserFlags = Convert.ToInt32(reader["userFlags"].ToString());
1194 retval.GodLevel = Convert.ToInt32(reader["godLevel"].ToString());
1195 if (reader.IsDBNull(reader.GetOrdinal("customType")))
1196 retval.CustomType = "";
1197 else
1198 retval.CustomType = reader["customType"].ToString();
1199
1200 if (reader.IsDBNull(reader.GetOrdinal("partner")))
1201 retval.Partner = UUID.Zero;
1202 else
1203 retval.Partner = new UUID((Guid)reader["partner"]);
1204 }
1205 else
1206 {
1207 return null;
1208 }
1209 return retval;
1210 }
1211 #endregion
1212 }
1213
1214}
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..94d240b
--- /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[Friend] 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/001_UserAccount.sql b/OpenSim/Data/MSSQL/Resources/001_UserAccount.sql
new file mode 100644
index 0000000..3dbf8a4
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/001_UserAccount.sql
@@ -0,0 +1,14 @@
1CREATE TABLE [UserAccounts] (
2 [PrincipalID] uniqueidentifier NOT NULL,
3 [ScopeID] uniqueidentifier NOT NULL,
4 [FirstName] [varchar](64) NOT NULL,
5 [LastName] [varchar](64) NOT NULL,
6 [Email] [varchar](64) NULL,
7 [ServiceURLs] [text] NULL,
8 [Created] [int] default NULL,
9
10 PRIMARY KEY CLUSTERED
11(
12 [PrincipalID] ASC
13)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
14) ON [PRIMARY]
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..e67d20e
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/002_FriendsStore.sql
@@ -0,0 +1,6 @@
1BEGIN TRANSACTION
2
3INSERT INTO Friends (PrincipalID, Friend, 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/003_UserAccount.sql b/OpenSim/Data/MSSQL/Resources/003_UserAccount.sql
new file mode 100644
index 0000000..da0395b
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/003_UserAccount.sql
@@ -0,0 +1,9 @@
1BEGIN TRANSACTION
2
3CREATE UNIQUE INDEX PrincipalID ON UserAccounts(PrincipalID);
4CREATE INDEX Email ON UserAccounts(Email);
5CREATE INDEX FirstName ON UserAccounts(FirstName);
6CREATE INDEX LastName ON UserAccounts(LastName);
7CREATE INDEX Name ON UserAccounts(FirstName,LastName);
8
9COMMIT \ No newline at end of file
diff --git a/OpenSim/Data/MSSQL/Resources/004_UserAccount.sql b/OpenSim/Data/MSSQL/Resources/004_UserAccount.sql
new file mode 100644
index 0000000..a9a9021
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/004_UserAccount.sql
@@ -0,0 +1,7 @@
1BEGIN TRANSACTION
2
3ALTER TABLE UserAccounts ADD UserLevel integer NOT NULL DEFAULT 0;
4ALTER TABLE UserAccounts ADD UserFlags integer NOT NULL DEFAULT 0;
5ALTER TABLE UserAccounts ADD UserTitle varchar(64) NOT NULL DEFAULT '';
6
7COMMIT \ 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-12-24 15:00:16 +0000