aboutsummaryrefslogtreecommitdiffstatshomepage
diff options
context:
space:
mode:
authorlbsa712008-02-13 20:08:46 +0000
committerlbsa712008-02-13 20:08:46 +0000
commit15c6788dc79e7f6d802f0217198e3af21dff4e57 (patch)
treeba6f22296ef68226725ecf224bf0e32dd4a1607b
parent*fixed varchar size typo in createuserfriendstable.sql (diff)
downloadopensim-SC_OLD-15c6788dc79e7f6d802f0217198e3af21dff4e57.zip
opensim-SC_OLD-15c6788dc79e7f6d802f0217198e3af21dff4e57.tar.gz
opensim-SC_OLD-15c6788dc79e7f6d802f0217198e3af21dff4e57.tar.bz2
opensim-SC_OLD-15c6788dc79e7f6d802f0217198e3af21dff4e57.tar.xz
* Moved insertRegionRow into MSSQLGridData
* Split table test and creation into table by table * Debugged table creation Sql * Added missing CreateUserFriendsTable.sql
-rw-r--r--OpenSim/Framework/Data.MSSQL/MSSQLGridData.cs77
-rw-r--r--OpenSim/Framework/Data.MSSQL/MSSQLManager.cs250
-rw-r--r--OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs31
-rw-r--r--OpenSim/Framework/Data.MSSQL/Resources/CreateUserFriendsTable.sql14
-rw-r--r--OpenSim/Framework/Data.MSSQL/Resources/Mssql-regions.sql9
5 files changed, 193 insertions, 188 deletions
diff --git a/OpenSim/Framework/Data.MSSQL/MSSQLGridData.cs b/OpenSim/Framework/Data.MSSQL/MSSQLGridData.cs
index 0df88b6..3648437 100644
--- a/OpenSim/Framework/Data.MSSQL/MSSQLGridData.cs
+++ b/OpenSim/Framework/Data.MSSQL/MSSQLGridData.cs
@@ -38,7 +38,7 @@ namespace OpenSim.Framework.Data.MSSQL
38 /// <summary> 38 /// <summary>
39 /// A grid data interface for Microsoft SQL Server 39 /// A grid data interface for Microsoft SQL Server
40 /// </summary> 40 /// </summary>
41 public class SqlGridData : IGridData 41 public class MSSQLGridData : IGridData
42 { 42 {
43 private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); 43 private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
44 44
@@ -77,7 +77,7 @@ namespace OpenSim.Framework.Data.MSSQL
77 77
78 private void TestTables() 78 private void TestTables()
79 { 79 {
80 IDbCommand cmd = database.Query("SELECT * FROM "+m_regionsTableName, new Dictionary<string, string>()); 80 IDbCommand cmd = database.Query("SELECT TOP 1 * FROM "+m_regionsTableName, new Dictionary<string, string>());
81 81
82 try 82 try
83 { 83 {
@@ -200,7 +200,7 @@ namespace OpenSim.Framework.Data.MSSQL
200 System.Console.WriteLine("No regions found. Create new one."); 200 System.Console.WriteLine("No regions found. Create new one.");
201 } 201 }
202 202
203 if (database.insertRegionRow(profile)) 203 if ( insertRegionRow(profile))
204 { 204 {
205 return DataResponse.RESPONSE_OK; 205 return DataResponse.RESPONSE_OK;
206 } 206 }
@@ -210,6 +210,77 @@ namespace OpenSim.Framework.Data.MSSQL
210 } 210 }
211 } 211 }
212 212
213
214 /// <summary>
215 /// Creates a new region in the database
216 /// </summary>
217 /// <param name="profile">The region profile to insert</param>
218 /// <returns>Successful?</returns>
219 public bool insertRegionRow(RegionProfileData profile)
220 {
221 //Insert new region
222 string sql =
223 "INSERT INTO " + m_regionsTableName + " ([regionHandle], [regionName], [uuid], [regionRecvKey], [regionSecret], [regionSendKey], [regionDataURI], ";
224 sql +=
225 "[serverIP], [serverPort], [serverURI], [locX], [locY], [locZ], [eastOverrideHandle], [westOverrideHandle], [southOverrideHandle], [northOverrideHandle], [regionAssetURI], [regionAssetRecvKey], ";
226 sql +=
227 "[regionAssetSendKey], [regionUserURI], [regionUserRecvKey], [regionUserSendKey], [regionMapTexture], [serverHttpPort], [serverRemotingPort]) VALUES ";
228
229 sql += "(@regionHandle, @regionName, @uuid, @regionRecvKey, @regionSecret, @regionSendKey, @regionDataURI, ";
230 sql +=
231 "@serverIP, @serverPort, @serverURI, @locX, @locY, @locZ, @eastOverrideHandle, @westOverrideHandle, @southOverrideHandle, @northOverrideHandle, @regionAssetURI, @regionAssetRecvKey, ";
232 sql +=
233 "@regionAssetSendKey, @regionUserURI, @regionUserRecvKey, @regionUserSendKey, @regionMapTexture, @serverHttpPort, @serverRemotingPort);";
234
235 Dictionary<string, string> parameters = new Dictionary<string, string>();
236
237 parameters["regionHandle"] = profile.regionHandle.ToString();
238 parameters["regionName"] = profile.regionName;
239 parameters["uuid"] = profile.UUID.ToString();
240 parameters["regionRecvKey"] = profile.regionRecvKey;
241 parameters["regionSecret"] = profile.regionSecret;
242 parameters["regionSendKey"] = profile.regionSendKey;
243 parameters["regionDataURI"] = profile.regionDataURI;
244 parameters["serverIP"] = profile.serverIP;
245 parameters["serverPort"] = profile.serverPort.ToString();
246 parameters["serverURI"] = profile.serverURI;
247 parameters["locX"] = profile.regionLocX.ToString();
248 parameters["locY"] = profile.regionLocY.ToString();
249 parameters["locZ"] = profile.regionLocZ.ToString();
250 parameters["eastOverrideHandle"] = profile.regionEastOverrideHandle.ToString();
251 parameters["westOverrideHandle"] = profile.regionWestOverrideHandle.ToString();
252 parameters["northOverrideHandle"] = profile.regionNorthOverrideHandle.ToString();
253 parameters["southOverrideHandle"] = profile.regionSouthOverrideHandle.ToString();
254 parameters["regionAssetURI"] = profile.regionAssetURI;
255 parameters["regionAssetRecvKey"] = profile.regionAssetRecvKey;
256 parameters["regionAssetSendKey"] = profile.regionAssetSendKey;
257 parameters["regionUserURI"] = profile.regionUserURI;
258 parameters["regionUserRecvKey"] = profile.regionUserRecvKey;
259 parameters["regionUserSendKey"] = profile.regionUserSendKey;
260 parameters["regionMapTexture"] = profile.regionMapTextureID.ToString();
261 parameters["serverHttpPort"] = profile.httpPort.ToString();
262 parameters["serverRemotingPort"] = profile.remotingPort.ToString();
263
264
265 bool returnval = false;
266
267 try
268 {
269 IDbCommand result = database.Query(sql, parameters);
270
271 if (result.ExecuteNonQuery() == 1)
272 returnval = true;
273
274 result.Dispose();
275 }
276 catch (Exception e)
277 {
278 m_log.Error("MSSQLManager : " + e.ToString());
279 }
280
281 return returnval;
282 }
283
213 /// <summary> 284 /// <summary>
214 /// DEPRECIATED. Attempts to authenticate a region by comparing a shared secret. 285 /// DEPRECIATED. Attempts to authenticate a region by comparing a shared secret.
215 /// </summary> 286 /// </summary>
diff --git a/OpenSim/Framework/Data.MSSQL/MSSQLManager.cs b/OpenSim/Framework/Data.MSSQL/MSSQLManager.cs
index 5c3e94f..26e8eb9 100644
--- a/OpenSim/Framework/Data.MSSQL/MSSQLManager.cs
+++ b/OpenSim/Framework/Data.MSSQL/MSSQLManager.cs
@@ -40,7 +40,7 @@ namespace OpenSim.Framework.Data.MSSQL
40 /// <summary> 40 /// <summary>
41 /// A management class for the MS SQL Storage Engine 41 /// A management class for the MS SQL Storage Engine
42 /// </summary> 42 /// </summary>
43 internal class MSSQLManager 43 public class MSSQLManager
44 { 44 {
45 private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); 45 private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
46 46
@@ -57,63 +57,56 @@ namespace OpenSim.Framework.Data.MSSQL
57 public MSSQLManager(string dataSource, string initialCatalog, string persistSecurityInfo, string userId, 57 public MSSQLManager(string dataSource, string initialCatalog, string persistSecurityInfo, string userId,
58 string password) 58 string password)
59 { 59 {
60 try 60 connectionString = "Data Source=" + dataSource + ";Initial Catalog=" + initialCatalog +
61 {
62 connectionString = "Data Source=" + dataSource + ";Initial Catalog=" + initialCatalog +
63 ";Persist Security Info=" + persistSecurityInfo + ";User ID=" + userId + ";Password=" + 61 ";Persist Security Info=" + persistSecurityInfo + ";User ID=" + userId + ";Password=" +
64 password + ";"; 62 password + ";";
65 dbcon = new SqlConnection(connectionString); 63 dbcon = new SqlConnection(connectionString);
66 dbcon.Open(); 64 dbcon.Open();
67 }
68 catch (Exception e)
69 {
70 throw new Exception("Error initialising Sql Database: " + e.ToString());
71 }
72 } 65 }
73 66
74 private DataTable createRegionsTable() 67 //private DataTable createRegionsTable()
75 { 68 //{
76 DataTable regions = new DataTable("regions"); 69 // DataTable regions = new DataTable("regions");
77 70
78 createCol(regions, "regionHandle", typeof (ulong)); 71 // createCol(regions, "regionHandle", typeof (ulong));
79 createCol(regions, "regionName", typeof (String)); 72 // createCol(regions, "regionName", typeof (String));
80 createCol(regions, "uuid", typeof (String)); 73 // createCol(regions, "uuid", typeof (String));
81 74
82 createCol(regions, "regionRecvKey", typeof (String)); 75 // createCol(regions, "regionRecvKey", typeof (String));
83 createCol(regions, "regionSecret", typeof (String)); 76 // createCol(regions, "regionSecret", typeof (String));
84 createCol(regions, "regionSendKey", typeof (String)); 77 // createCol(regions, "regionSendKey", typeof (String));
85 78
86 createCol(regions, "regionDataURI", typeof (String)); 79 // createCol(regions, "regionDataURI", typeof (String));
87 createCol(regions, "serverIP", typeof (String)); 80 // createCol(regions, "serverIP", typeof (String));
88 createCol(regions, "serverPort", typeof (String)); 81 // createCol(regions, "serverPort", typeof (String));
89 createCol(regions, "serverURI", typeof (String)); 82 // createCol(regions, "serverURI", typeof (String));
90 83
91 84
92 createCol(regions, "locX", typeof (uint)); 85 // createCol(regions, "locX", typeof (uint));
93 createCol(regions, "locY", typeof (uint)); 86 // createCol(regions, "locY", typeof (uint));
94 createCol(regions, "locZ", typeof (uint)); 87 // createCol(regions, "locZ", typeof (uint));
95 88
96 createCol(regions, "eastOverrideHandle", typeof (ulong)); 89 // createCol(regions, "eastOverrideHandle", typeof (ulong));
97 createCol(regions, "westOverrideHandle", typeof (ulong)); 90 // createCol(regions, "westOverrideHandle", typeof (ulong));
98 createCol(regions, "southOverrideHandle", typeof (ulong)); 91 // createCol(regions, "southOverrideHandle", typeof (ulong));
99 createCol(regions, "northOverrideHandle", typeof (ulong)); 92 // createCol(regions, "northOverrideHandle", typeof (ulong));
100 93
101 createCol(regions, "regionAssetURI", typeof (String)); 94 // createCol(regions, "regionAssetURI", typeof (String));
102 createCol(regions, "regionAssetRecvKey", typeof (String)); 95 // createCol(regions, "regionAssetRecvKey", typeof (String));
103 createCol(regions, "regionAssetSendKey", typeof (String)); 96 // createCol(regions, "regionAssetSendKey", typeof (String));
104 97
105 createCol(regions, "regionUserURI", typeof (String)); 98 // createCol(regions, "regionUserURI", typeof (String));
106 createCol(regions, "regionUserRecvKey", typeof (String)); 99 // createCol(regions, "regionUserRecvKey", typeof (String));
107 createCol(regions, "regionUserSendKey", typeof (String)); 100 // createCol(regions, "regionUserSendKey", typeof (String));
108 101
109 createCol(regions, "regionMapTexture", typeof (String)); 102 // createCol(regions, "regionMapTexture", typeof (String));
110 createCol(regions, "serverHttpPort", typeof (String)); 103 // createCol(regions, "serverHttpPort", typeof (String));
111 createCol(regions, "serverRemotingPort", typeof (uint)); 104 // createCol(regions, "serverRemotingPort", typeof (uint));
112 105
113 // Add in contraints 106 // // Add in contraints
114 regions.PrimaryKey = new DataColumn[] {regions.Columns["UUID"]}; 107 // regions.PrimaryKey = new DataColumn[] {regions.Columns["UUID"]};
115 return regions; 108 // return regions;
116 } 109 //}
117 110
118 protected static void createCol(DataTable dt, string name, Type type) 111 protected static void createCol(DataTable dt, string name, Type type)
119 { 112 {
@@ -149,19 +142,19 @@ namespace OpenSim.Framework.Data.MSSQL
149 // slightly differently. 142 // slightly differently.
150 private static string SqlType(Type type) 143 private static string SqlType(Type type)
151 { 144 {
152 if (type == typeof (String)) 145 if (type == typeof(String))
153 { 146 {
154 return "varchar(255)"; 147 return "varchar(255)";
155 } 148 }
156 else if (type == typeof (Int32)) 149 else if (type == typeof(Int32))
157 { 150 {
158 return "integer"; 151 return "integer";
159 } 152 }
160 else if (type == typeof (Double)) 153 else if (type == typeof(Double))
161 { 154 {
162 return "float"; 155 return "float";
163 } 156 }
164 else if (type == typeof (Byte[])) 157 else if (type == typeof(Byte[]))
165 { 158 {
166 return "image"; 159 return "image";
167 } 160 }
@@ -210,14 +203,14 @@ namespace OpenSim.Framework.Data.MSSQL
210 /// <returns>A Sql DB Command</returns> 203 /// <returns>A Sql DB Command</returns>
211 public IDbCommand Query(string sql, Dictionary<string, string> parameters) 204 public IDbCommand Query(string sql, Dictionary<string, string> parameters)
212 { 205 {
213 SqlCommand dbcommand = (SqlCommand) dbcon.CreateCommand(); 206 SqlCommand dbcommand = (SqlCommand)dbcon.CreateCommand();
214 dbcommand.CommandText = sql; 207 dbcommand.CommandText = sql;
215 foreach (KeyValuePair<string, string> param in parameters) 208 foreach (KeyValuePair<string, string> param in parameters)
216 { 209 {
217 dbcommand.Parameters.AddWithValue(param.Key, param.Value); 210 dbcommand.Parameters.AddWithValue(param.Key, param.Value);
218 } 211 }
219 212
220 return (IDbCommand) dbcommand; 213 return (IDbCommand)dbcommand;
221 } 214 }
222 215
223 /// <summary> 216 /// <summary>
@@ -233,20 +226,20 @@ namespace OpenSim.Framework.Data.MSSQL
233 { 226 {
234 // Region Main 227 // Region Main
235 regionprofile.regionHandle = Convert.ToUInt64(reader["regionHandle"]); 228 regionprofile.regionHandle = Convert.ToUInt64(reader["regionHandle"]);
236 regionprofile.regionName = (string) reader["regionName"]; 229 regionprofile.regionName = (string)reader["regionName"];
237 regionprofile.UUID = new LLUUID((string) reader["uuid"]); 230 regionprofile.UUID = new LLUUID((string)reader["uuid"]);
238 231
239 // Secrets 232 // Secrets
240 regionprofile.regionRecvKey = (string) reader["regionRecvKey"]; 233 regionprofile.regionRecvKey = (string)reader["regionRecvKey"];
241 regionprofile.regionSecret = (string) reader["regionSecret"]; 234 regionprofile.regionSecret = (string)reader["regionSecret"];
242 regionprofile.regionSendKey = (string) reader["regionSendKey"]; 235 regionprofile.regionSendKey = (string)reader["regionSendKey"];
243 236
244 // Region Server 237 // Region Server
245 regionprofile.regionDataURI = (string) reader["regionDataURI"]; 238 regionprofile.regionDataURI = (string)reader["regionDataURI"];
246 regionprofile.regionOnline = false; // Needs to be pinged before this can be set. 239 regionprofile.regionOnline = false; // Needs to be pinged before this can be set.
247 regionprofile.serverIP = (string) reader["serverIP"]; 240 regionprofile.serverIP = (string)reader["serverIP"];
248 regionprofile.serverPort = Convert.ToUInt32(reader["serverPort"]); 241 regionprofile.serverPort = Convert.ToUInt32(reader["serverPort"]);
249 regionprofile.serverURI = (string) reader["serverURI"]; 242 regionprofile.serverURI = (string)reader["serverURI"];
250 regionprofile.httpPort = Convert.ToUInt32(reader["serverHttpPort"]); 243 regionprofile.httpPort = Convert.ToUInt32(reader["serverHttpPort"]);
251 regionprofile.remotingPort = Convert.ToUInt32(reader["serverRemotingPort"]); 244 regionprofile.remotingPort = Convert.ToUInt32(reader["serverRemotingPort"]);
252 245
@@ -263,14 +256,14 @@ namespace OpenSim.Framework.Data.MSSQL
263 regionprofile.regionNorthOverrideHandle = Convert.ToUInt64(reader["northOverrideHandle"]); 256 regionprofile.regionNorthOverrideHandle = Convert.ToUInt64(reader["northOverrideHandle"]);
264 257
265 // Assets 258 // Assets
266 regionprofile.regionAssetURI = (string) reader["regionAssetURI"]; 259 regionprofile.regionAssetURI = (string)reader["regionAssetURI"];
267 regionprofile.regionAssetRecvKey = (string) reader["regionAssetRecvKey"]; 260 regionprofile.regionAssetRecvKey = (string)reader["regionAssetRecvKey"];
268 regionprofile.regionAssetSendKey = (string) reader["regionAssetSendKey"]; 261 regionprofile.regionAssetSendKey = (string)reader["regionAssetSendKey"];
269 262
270 // Userserver 263 // Userserver
271 regionprofile.regionUserURI = (string) reader["regionUserURI"]; 264 regionprofile.regionUserURI = (string)reader["regionUserURI"];
272 regionprofile.regionUserRecvKey = (string) reader["regionUserRecvKey"]; 265 regionprofile.regionUserRecvKey = (string)reader["regionUserRecvKey"];
273 regionprofile.regionUserSendKey = (string) reader["regionUserSendKey"]; 266 regionprofile.regionUserSendKey = (string)reader["regionUserSendKey"];
274 267
275 // World Map Addition 268 // World Map Addition
276 string tempRegionMap = reader["regionMapTexture"].ToString(); 269 string tempRegionMap = reader["regionMapTexture"].ToString();
@@ -302,12 +295,12 @@ namespace OpenSim.Framework.Data.MSSQL
302 295
303 if (reader.Read()) 296 if (reader.Read())
304 { 297 {
305 retval.UUID = new LLUUID((string) reader["UUID"]); 298 retval.UUID = new LLUUID((string)reader["UUID"]);
306 retval.username = (string) reader["username"]; 299 retval.username = (string)reader["username"];
307 retval.surname = (string) reader["lastname"]; 300 retval.surname = (string)reader["lastname"];
308 301
309 retval.passwordHash = (string) reader["passwordHash"]; 302 retval.passwordHash = (string)reader["passwordHash"];
310 retval.passwordSalt = (string) reader["passwordSalt"]; 303 retval.passwordSalt = (string)reader["passwordSalt"];
311 304
312 retval.homeRegion = Convert.ToUInt64(reader["homeRegion"].ToString()); 305 retval.homeRegion = Convert.ToUInt64(reader["homeRegion"].ToString());
313 retval.homeLocation = new LLVector3( 306 retval.homeLocation = new LLVector3(
@@ -322,17 +315,17 @@ namespace OpenSim.Framework.Data.MSSQL
322 retval.created = Convert.ToInt32(reader["created"].ToString()); 315 retval.created = Convert.ToInt32(reader["created"].ToString());
323 retval.lastLogin = Convert.ToInt32(reader["lastLogin"].ToString()); 316 retval.lastLogin = Convert.ToInt32(reader["lastLogin"].ToString());
324 317
325 retval.userInventoryURI = (string) reader["userInventoryURI"]; 318 retval.userInventoryURI = (string)reader["userInventoryURI"];
326 retval.userAssetURI = (string) reader["userAssetURI"]; 319 retval.userAssetURI = (string)reader["userAssetURI"];
327 320
328 retval.profileCanDoMask = Convert.ToUInt32(reader["profileCanDoMask"].ToString()); 321 retval.profileCanDoMask = Convert.ToUInt32(reader["profileCanDoMask"].ToString());
329 retval.profileWantDoMask = Convert.ToUInt32(reader["profileWantDoMask"].ToString()); 322 retval.profileWantDoMask = Convert.ToUInt32(reader["profileWantDoMask"].ToString());
330 323
331 retval.profileAboutText = (string) reader["profileAboutText"]; 324 retval.profileAboutText = (string)reader["profileAboutText"];
332 retval.profileFirstText = (string) reader["profileFirstText"]; 325 retval.profileFirstText = (string)reader["profileFirstText"];
333 326
334 retval.profileImage = new LLUUID((string) reader["profileImage"]); 327 retval.profileImage = new LLUUID((string)reader["profileImage"]);
335 retval.profileFirstImage = new LLUUID((string) reader["profileFirstImage"]); 328 retval.profileFirstImage = new LLUUID((string)reader["profileFirstImage"]);
336 retval.webLoginKey = new LLUUID((string)reader["webLoginKey"]); 329 retval.webLoginKey = new LLUUID((string)reader["webLoginKey"]);
337 } 330 }
338 else 331 else
@@ -354,12 +347,12 @@ namespace OpenSim.Framework.Data.MSSQL
354 if (reader.Read()) 347 if (reader.Read())
355 { 348 {
356 // Agent IDs 349 // Agent IDs
357 retval.UUID = new LLUUID((string) reader["UUID"]); 350 retval.UUID = new LLUUID((string)reader["UUID"]);
358 retval.sessionID = new LLUUID((string) reader["sessionID"]); 351 retval.sessionID = new LLUUID((string)reader["sessionID"]);
359 retval.secureSessionID = new LLUUID((string) reader["secureSessionID"]); 352 retval.secureSessionID = new LLUUID((string)reader["secureSessionID"]);
360 353
361 // Agent Who? 354 // Agent Who?
362 retval.agentIP = (string) reader["agentIP"]; 355 retval.agentIP = (string)reader["agentIP"];
363 retval.agentPort = Convert.ToUInt32(reader["agentPort"].ToString()); 356 retval.agentPort = Convert.ToUInt32(reader["agentPort"].ToString());
364 retval.agentOnline = Convert.ToBoolean(reader["agentOnline"].ToString()); 357 retval.agentOnline = Convert.ToBoolean(reader["agentOnline"].ToString());
365 358
@@ -368,9 +361,9 @@ namespace OpenSim.Framework.Data.MSSQL
368 retval.logoutTime = Convert.ToInt32(reader["logoutTime"].ToString()); 361 retval.logoutTime = Convert.ToInt32(reader["logoutTime"].ToString());
369 362
370 // Current position 363 // Current position
371 retval.currentRegion = (string) reader["currentRegion"]; 364 retval.currentRegion = (string)reader["currentRegion"];
372 retval.currentHandle = Convert.ToUInt64(reader["currentHandle"].ToString()); 365 retval.currentHandle = Convert.ToUInt64(reader["currentHandle"].ToString());
373 LLVector3.TryParse((string) reader["currentPos"], out retval.currentPos); 366 LLVector3.TryParse((string)reader["currentPos"], out retval.currentPos);
374 } 367 }
375 else 368 else
376 { 369 {
@@ -387,12 +380,12 @@ namespace OpenSim.Framework.Data.MSSQL
387 // Region Main 380 // Region Main
388 381
389 asset = new AssetBase(); 382 asset = new AssetBase();
390 asset.Data = (byte[]) reader["data"]; 383 asset.Data = (byte[])reader["data"];
391 asset.Description = (string) reader["description"]; 384 asset.Description = (string)reader["description"];
392 asset.FullID = new LLUUID((string) reader["id"]); 385 asset.FullID = new LLUUID((string)reader["id"]);
393 asset.InvType = Convert.ToSByte(reader["invType"]); 386 asset.InvType = Convert.ToSByte(reader["invType"]);
394 asset.Local = Convert.ToBoolean(reader["local"]); // ((sbyte)reader["local"]) != 0 ? true : false; 387 asset.Local = Convert.ToBoolean(reader["local"]); // ((sbyte)reader["local"]) != 0 ? true : false;
395 asset.Name = (string) reader["name"]; 388 asset.Name = (string)reader["name"];
396 asset.Type = Convert.ToSByte(reader["assetType"]); 389 asset.Type = Convert.ToSByte(reader["assetType"]);
397 } 390 }
398 else 391 else
@@ -402,76 +395,6 @@ namespace OpenSim.Framework.Data.MSSQL
402 return asset; 395 return asset;
403 } 396 }
404 397
405 /// <summary>
406 /// Creates a new region in the database
407 /// </summary>
408 /// <param name="profile">The region profile to insert</param>
409 /// <returns>Successful?</returns>
410 public bool insertRegionRow(RegionProfileData profile)
411 {
412 //Insert new region
413 string sql =
414 "INSERT INTO regions ([regionHandle], [regionName], [uuid], [regionRecvKey], [regionSecret], [regionSendKey], [regionDataURI], ";
415 sql +=
416 "[serverIP], [serverPort], [serverURI], [locX], [locY], [locZ], [eastOverrideHandle], [westOverrideHandle], [southOverrideHandle], [northOverrideHandle], [regionAssetURI], [regionAssetRecvKey], ";
417 sql +=
418 "[regionAssetSendKey], [regionUserURI], [regionUserRecvKey], [regionUserSendKey], [regionMapTexture], [serverHttpPort], [serverRemotingPort]) VALUES ";
419
420 sql += "(@regionHandle, @regionName, @uuid, @regionRecvKey, @regionSecret, @regionSendKey, @regionDataURI, ";
421 sql +=
422 "@serverIP, @serverPort, @serverURI, @locX, @locY, @locZ, @eastOverrideHandle, @westOverrideHandle, @southOverrideHandle, @northOverrideHandle, @regionAssetURI, @regionAssetRecvKey, ";
423 sql +=
424 "@regionAssetSendKey, @regionUserURI, @regionUserRecvKey, @regionUserSendKey, @regionMapTexture, @serverHttpPort, @serverRemotingPort);";
425
426 Dictionary<string, string> parameters = new Dictionary<string, string>();
427
428 parameters["regionHandle"] = profile.regionHandle.ToString();
429 parameters["regionName"] = profile.regionName;
430 parameters["uuid"] = profile.UUID.ToString();
431 parameters["regionRecvKey"] = profile.regionRecvKey;
432 parameters["regionSecret"] = profile.regionSecret;
433 parameters["regionSendKey"] = profile.regionSendKey;
434 parameters["regionDataURI"] = profile.regionDataURI;
435 parameters["serverIP"] = profile.serverIP;
436 parameters["serverPort"] = profile.serverPort.ToString();
437 parameters["serverURI"] = profile.serverURI;
438 parameters["locX"] = profile.regionLocX.ToString();
439 parameters["locY"] = profile.regionLocY.ToString();
440 parameters["locZ"] = profile.regionLocZ.ToString();
441 parameters["eastOverrideHandle"] = profile.regionEastOverrideHandle.ToString();
442 parameters["westOverrideHandle"] = profile.regionWestOverrideHandle.ToString();
443 parameters["northOverrideHandle"] = profile.regionNorthOverrideHandle.ToString();
444 parameters["southOverrideHandle"] = profile.regionSouthOverrideHandle.ToString();
445 parameters["regionAssetURI"] = profile.regionAssetURI;
446 parameters["regionAssetRecvKey"] = profile.regionAssetRecvKey;
447 parameters["regionAssetSendKey"] = profile.regionAssetSendKey;
448 parameters["regionUserURI"] = profile.regionUserURI;
449 parameters["regionUserRecvKey"] = profile.regionUserRecvKey;
450 parameters["regionUserSendKey"] = profile.regionUserSendKey;
451 parameters["regionMapTexture"] = profile.regionMapTextureID.ToString();
452 parameters["serverHttpPort"] = profile.httpPort.ToString();
453 parameters["serverRemotingPort"] = profile.remotingPort.ToString();
454
455
456 bool returnval = false;
457
458 try
459 {
460 IDbCommand result = Query(sql, parameters);
461
462 if (result.ExecuteNonQuery() == 1)
463 returnval = true;
464
465 result.Dispose();
466 }
467 catch (Exception e)
468 {
469 m_log.Error("MSSQLManager : " + e.ToString());
470 }
471
472 return returnval;
473 }
474
475 398
476 /// <summary> 399 /// <summary>
477 /// Inserts a new row into the log database 400 /// Inserts a new row into the log database
@@ -617,21 +540,14 @@ namespace OpenSim.Framework.Data.MSSQL
617 /// <param name="name"></param> 540 /// <param name="name"></param>
618 public void ExecuteResourceSql(string name) 541 public void ExecuteResourceSql(string name)
619 { 542 {
620 try 543 SqlCommand cmd = new SqlCommand(getResourceString(name), (SqlConnection)dbcon);
621 { 544 cmd.ExecuteNonQuery();
622 SqlCommand cmd = new SqlCommand(getResourceString(name), (SqlConnection) dbcon); 545 cmd.Dispose();
623 cmd.ExecuteNonQuery();
624 cmd.Dispose();
625 }
626 catch (Exception e)
627 {
628 m_log.Error("Unable to execute query " + e.ToString());
629 }
630 } 546 }
631 547
632 public SqlConnection getConnection() 548 public SqlConnection getConnection()
633 { 549 {
634 return (SqlConnection) dbcon; 550 return (SqlConnection)dbcon;
635 } 551 }
636 552
637 /// <summary> 553 /// <summary>
@@ -652,7 +568,7 @@ namespace OpenSim.Framework.Data.MSSQL
652 { 568 {
653 try 569 try
654 { 570 {
655 string tableName = (string) tables["TABLE_NAME"]; 571 string tableName = (string)tables["TABLE_NAME"];
656 if (tableList.ContainsKey(tableName)) 572 if (tableList.ContainsKey(tableName))
657 tableList[tableName] = tableName; 573 tableList[tableName] = tableName;
658 } 574 }
diff --git a/OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs b/OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs
index 95a0c08..56ad3fa 100644
--- a/OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs
+++ b/OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs
@@ -37,7 +37,7 @@ namespace OpenSim.Framework.Data.MSSQL
37 /// <summary> 37 /// <summary>
38 /// A database interface class to a user profile storage system 38 /// A database interface class to a user profile storage system
39 /// </summary> 39 /// </summary>
40 internal class MSSQLUserData : IUserData 40 public class MSSQLUserData : IUserData
41 { 41 {
42 private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); 42 private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
43 43
@@ -86,37 +86,41 @@ namespace OpenSim.Framework.Data.MSSQL
86 new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, 86 new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId,
87 settingPassword); 87 settingPassword);
88 88
89 if (!TestTables()) 89 TestTables();
90 {
91 database.ExecuteResourceSql("Mssql-agents.sql");
92 database.ExecuteResourceSql("Mssql-users.sql");
93 database.ExecuteResourceSql("Mssql-userfriends.sql");
94 }
95 } 90 }
96 91
97 private bool TestTables() 92 private bool TestTables()
98 { 93 {
99 IDbCommand cmd = database.Query("select top 1 webLoginKey from "+m_usersTableName, new Dictionary<string, string>()); 94 IDbCommand cmd;
95
96 cmd = database.Query("select top 1 * from " + m_usersTableName, new Dictionary<string, string>());
100 try 97 try
101 { 98 {
102 cmd.ExecuteNonQuery(); 99 cmd.ExecuteNonQuery();
103 cmd.Dispose();
104 } 100 }
105 catch 101 catch
106 { 102 {
107 database.Query("alter table "+m_usersTableName+" add column [webLoginKey] varchar(36) default NULL", new Dictionary<string, string>()); 103 database.ExecuteResourceSql("Mssql-users.sql");
104 }
105
106 cmd = database.Query("select top 1 * from " + m_agentsTableName, new Dictionary<string, string>());
107 try
108 {
108 cmd.ExecuteNonQuery(); 109 cmd.ExecuteNonQuery();
109 cmd.Dispose(); 110 }
111 catch
112 {
113 database.ExecuteResourceSql("Mssql-agents.sql");
110 } 114 }
111 115
112 cmd = database.Query("select top 1 * from "+m_usersTableName, new Dictionary<string, string>()); 116 cmd = database.Query("select top 1 * from " + m_userFriendsTableName, new Dictionary<string, string>());
113 try 117 try
114 { 118 {
115 cmd.ExecuteNonQuery(); 119 cmd.ExecuteNonQuery();
116 } 120 }
117 catch 121 catch
118 { 122 {
119 return false; 123 database.ExecuteResourceSql("CreateUserFriendsTable.sql");
120 } 124 }
121 125
122 return true; 126 return true;
@@ -180,6 +184,7 @@ namespace OpenSim.Framework.Data.MSSQL
180 "VALUES " + 184 "VALUES " +
181 "(@ownerID,@friendID,@friendPerms,@datetimestamp)", 185 "(@ownerID,@friendID,@friendPerms,@datetimestamp)",
182 param); 186 param);
187
183 adder.ExecuteNonQuery(); 188 adder.ExecuteNonQuery();
184 189
185 adder = 190 adder =
diff --git a/OpenSim/Framework/Data.MSSQL/Resources/CreateUserFriendsTable.sql b/OpenSim/Framework/Data.MSSQL/Resources/CreateUserFriendsTable.sql
new file mode 100644
index 0000000..9aebfa3
--- /dev/null
+++ b/OpenSim/Framework/Data.MSSQL/Resources/CreateUserFriendsTable.sql
@@ -0,0 +1,14 @@
1SET ANSI_NULLS ON
2
3SET QUOTED_IDENTIFIER ON
4
5SET ANSI_PADDING ON
6
7CREATE TABLE [dbo].[userfriends](
8[ownerID] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
9[friendID] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
10[friendPerms] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
11[datetimestamp] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
12) ON [PRIMARY]
13
14SET ANSI_PADDING OFF
diff --git a/OpenSim/Framework/Data.MSSQL/Resources/Mssql-regions.sql b/OpenSim/Framework/Data.MSSQL/Resources/Mssql-regions.sql
index b46da16..7875c50 100644
--- a/OpenSim/Framework/Data.MSSQL/Resources/Mssql-regions.sql
+++ b/OpenSim/Framework/Data.MSSQL/Resources/Mssql-regions.sql
@@ -1,10 +1,10 @@
1SET ANSI_NULLS ON 1SET ANSI_NULLS ON
2GO 2
3SET QUOTED_IDENTIFIER ON 3SET QUOTED_IDENTIFIER ON
4GO 4
5SET ANSI_PADDING ON 5SET ANSI_PADDING ON
6GO 6
7CREATE TABLE [db_owner].[regions]( 7CREATE TABLE [dbo].[regions](
8 [regionHandle] [varchar](255) COLLATE Latin1_General_CI_AS NULL, 8 [regionHandle] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
9 [regionName] [varchar](255) COLLATE Latin1_General_CI_AS NULL, 9 [regionName] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
10 [uuid] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL, 10 [uuid] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
@@ -37,5 +37,4 @@ PRIMARY KEY CLUSTERED
37)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] 37)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
38) ON [PRIMARY] 38) ON [PRIMARY]
39 39
40GO
41SET ANSI_PADDING OFF 40SET ANSI_PADDING OFF