aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/MSSQL
diff options
context:
space:
mode:
Diffstat (limited to 'OpenSim/Data/MSSQL')
-rw-r--r--OpenSim/Data/MSSQL/AutoClosingSqlCommand.cs27
-rw-r--r--OpenSim/Data/MSSQL/MSSQLAssetData.cs245
-rw-r--r--OpenSim/Data/MSSQL/MSSQLEstateData.cs12
-rw-r--r--OpenSim/Data/MSSQL/MSSQLGridData.cs579
-rw-r--r--OpenSim/Data/MSSQL/MSSQLInventoryData.cs818
-rw-r--r--OpenSim/Data/MSSQL/MSSQLLogData.cs106
-rw-r--r--OpenSim/Data/MSSQL/MSSQLManager.cs374
-rw-r--r--OpenSim/Data/MSSQL/MSSQLRegionData.cs206
-rw-r--r--OpenSim/Data/MSSQL/MSSQLUserData.cs1360
-rw-r--r--OpenSim/Data/MSSQL/Resources/001_AssetStore.sql13
-rw-r--r--OpenSim/Data/MSSQL/Resources/001_GridStore.sql37
-rw-r--r--OpenSim/Data/MSSQL/Resources/001_InventoryStore.sql64
-rw-r--r--OpenSim/Data/MSSQL/Resources/001_UserStore.sql224
-rw-r--r--OpenSim/Data/MSSQL/Resources/002_AssetStore.sql29
-rw-r--r--OpenSim/Data/MSSQL/Resources/002_UserStore.sql18
-rw-r--r--OpenSim/Data/MSSQL/Resources/003_GridStore.sql22
-rw-r--r--OpenSim/Data/MSSQL/Resources/003_UserStore.sql30
-rw-r--r--OpenSim/Data/MSSQL/Resources/004_RegionStore.sql12
-rw-r--r--OpenSim/Data/MSSQL/Resources/004_UserStore.sql58
-rw-r--r--OpenSim/Data/MSSQL/Resources/005_RegionStore.sql16
20 files changed, 2534 insertions, 1716 deletions
diff --git a/OpenSim/Data/MSSQL/AutoClosingSqlCommand.cs b/OpenSim/Data/MSSQL/AutoClosingSqlCommand.cs
index cae864b..9211747 100644
--- a/OpenSim/Data/MSSQL/AutoClosingSqlCommand.cs
+++ b/OpenSim/Data/MSSQL/AutoClosingSqlCommand.cs
@@ -25,9 +25,6 @@
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */ 26 */
27 27
28using System;
29using System.Collections.Generic;
30using System.Text;
31using System.Data.SqlClient; 28using System.Data.SqlClient;
32using System.Data; 29using System.Data;
33 30
@@ -163,16 +160,22 @@ namespace OpenSim.Data.MSSQL
163 realCommand.Prepare(); 160 realCommand.Prepare();
164 } 161 }
165 162
166 IDbTransaction IDbCommand.Transaction 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
167 { 176 {
168 get 177 get { return realCommand.Transaction; }
169 { 178 set { realCommand.Transaction = (SqlTransaction)value; }
170 return realCommand.Transaction;
171 }
172 set
173 {
174 realCommand.Transaction = (SqlTransaction) value;
175 }
176 } 179 }
177 180
178 UpdateRowSource IDbCommand.UpdatedRowSource 181 UpdateRowSource IDbCommand.UpdatedRowSource
diff --git a/OpenSim/Data/MSSQL/MSSQLAssetData.cs b/OpenSim/Data/MSSQL/MSSQLAssetData.cs
index ed1c91b..4d5ca1c 100644
--- a/OpenSim/Data/MSSQL/MSSQLAssetData.cs
+++ b/OpenSim/Data/MSSQL/MSSQLAssetData.cs
@@ -41,44 +41,83 @@ namespace OpenSim.Data.MSSQL
41 /// </summary> 41 /// </summary>
42 internal class MSSQLAssetData : AssetDataBase 42 internal class MSSQLAssetData : AssetDataBase
43 { 43 {
44 private const string _migrationStore = "AssetStore";
45
44 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); 46 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
45 47
48 /// <summary>
49 /// Database manager
50 /// </summary>
46 private MSSQLManager database; 51 private MSSQLManager database;
47 52
48 #region IAssetProviderPlugin Members 53 #region IPlugin Members
54
55 override public void Dispose() { }
49 56
50 /// <summary> 57 /// <summary>
51 /// Migration method 58 /// <para>Initialises asset interface</para>
52 /// <list type="bullet">
53 /// <item>Execute "CreateAssetsTable.sql" if tableName == null</item>
54 /// </list>
55 /// </summary> 59 /// </summary>
56 /// <param name="tableName">Name of table</param> 60 override public void Initialise()
57 private void UpgradeAssetsTable(string tableName)
58 { 61 {
59 // null as the version, indicates that the table didn't exist 62 m_log.Info("[MSSQLUserData]: " + Name + " cannot be default-initialized!");
60 if (tableName == null) 63 throw new PluginNotInitialisedException(Name);
64 }
65
66 /// <summary>
67 /// Initialises asset interface
68 /// </summary>
69 /// <para>
70 /// a string instead of file, if someone writes the support
71 /// </para>
72 /// <param name="connectionString">connect string</param>
73 override public void Initialise(string connectionString)
74 {
75 if (string.IsNullOrEmpty(connectionString))
61 { 76 {
62 m_log.Info("[ASSET DB]: Creating new database tables"); 77 database = new MSSQLManager(connectionString);
63 database.ExecuteResourceSql("CreateAssetsTable.sql"); 78 }
64 return; 79 else
80 {
81
82 IniFile gridDataMSSqlFile = new IniFile("mssql_connection.ini");
83 string settingDataSource = gridDataMSSqlFile.ParseFileReadValue("data_source");
84 string settingInitialCatalog = gridDataMSSqlFile.ParseFileReadValue("initial_catalog");
85 string settingPersistSecurityInfo = gridDataMSSqlFile.ParseFileReadValue("persist_security_info");
86 string settingUserId = gridDataMSSqlFile.ParseFileReadValue("user_id");
87 string settingPassword = gridDataMSSqlFile.ParseFileReadValue("password");
88
89 database =
90 new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId,
91 settingPassword);
65 } 92 }
66 93
94 //TODO can be removed at some time!!
95 TestTables();
96
97 //New migration to check for DB changes
98 database.CheckMigration(_migrationStore);
67 } 99 }
68 100
69 /// <summary> 101 /// <summary>
70 /// Ensure that the assets related tables exists and are at the latest version 102 /// Database provider version.
71 /// </summary> 103 /// </summary>
72 private void TestTables() 104 override public string Version
73 { 105 {
74 Dictionary<string, string> tableList = new Dictionary<string, string>(); 106 get { return database.getVersion(); }
75 107 }
76 tableList["assets"] = null;
77 database.GetTableVersion(tableList);
78 108
79 UpgradeAssetsTable(tableList["assets"]); 109 /// <summary>
110 /// The name of this DB provider.
111 /// </summary>
112 override public string Name
113 {
114 get { return "MSSQL Asset storage engine"; }
80 } 115 }
81 116
117 #endregion
118
119 #region IAssetProviderPlugin Members
120
82 /// <summary> 121 /// <summary>
83 /// Fetch Asset from database 122 /// Fetch Asset from database
84 /// </summary> 123 /// </summary>
@@ -86,13 +125,26 @@ namespace OpenSim.Data.MSSQL
86 /// <returns></returns> 125 /// <returns></returns>
87 override public AssetBase FetchAsset(UUID assetID) 126 override public AssetBase FetchAsset(UUID assetID)
88 { 127 {
89 Dictionary<string, string> param = new Dictionary<string, string>(); 128 using (AutoClosingSqlCommand command = database.Query("SELECT * FROM assets WHERE id = @id"))
90 param["id"] = assetID.ToString();
91
92 using (IDbCommand result = database.Query("SELECT * FROM assets WHERE id = @id", param))
93 using (IDataReader reader = result.ExecuteReader())
94 { 129 {
95 return database.getAssetRow(reader); 130 command.Parameters.Add(database.CreateParameter("id", assetID));
131 using (IDataReader reader = command.ExecuteReader())
132 {
133 if (reader.Read())
134 {
135 AssetBase asset = new AssetBase();
136 // Region Main
137 asset.FullID = new UUID((string)reader["id"]);
138 asset.Name = (string)reader["name"];
139 asset.Description = (string)reader["description"];
140 asset.Type = Convert.ToSByte(reader["assetType"]);
141 asset.Local = Convert.ToBoolean(reader["local"]);
142 asset.Temporary = Convert.ToBoolean(reader["temporary"]);
143 asset.Data = (byte[])reader["data"];
144 return asset;
145 }
146 return null; // throw new Exception("No rows to return");
147 }
96 } 148 }
97 } 149 }
98 150
@@ -102,34 +154,27 @@ namespace OpenSim.Data.MSSQL
102 /// <param name="asset">the asset</param> 154 /// <param name="asset">the asset</param>
103 override public void CreateAsset(AssetBase asset) 155 override public void CreateAsset(AssetBase asset)
104 { 156 {
105 if (ExistsAsset((UUID) asset.FullID)) 157 if (ExistsAsset(asset.FullID))
106 { 158 {
107 return; 159 return;
108 } 160 }
109 161
110 162 using (AutoClosingSqlCommand command = database.Query(
111 using (AutoClosingSqlCommand cmd =
112 database.Query(
113 "INSERT INTO assets ([id], [name], [description], [assetType], [local], [temporary], [data])" + 163 "INSERT INTO assets ([id], [name], [description], [assetType], [local], [temporary], [data])" +
114 " VALUES " + 164 " VALUES " +
115 "(@id, @name, @description, @assetType, @local, @temporary, @data)")) 165 "(@id, @name, @description, @assetType, @local, @temporary, @data)"))
116 { 166 {
117
118 //SqlParameter p = cmd.Parameters.Add("id", SqlDbType.NVarChar); 167 //SqlParameter p = cmd.Parameters.Add("id", SqlDbType.NVarChar);
119 //p.Value = asset.FullID.ToString(); 168 //p.Value = asset.FullID.ToString();
120 cmd.Parameters.AddWithValue("id", asset.FullID.ToString()); 169 command.Parameters.Add(database.CreateParameter("id", asset.FullID));
121 cmd.Parameters.AddWithValue("name", asset.Name); 170 command.Parameters.Add(database.CreateParameter("name", asset.Name));
122 cmd.Parameters.AddWithValue("description", asset.Description); 171 command.Parameters.Add(database.CreateParameter("description", asset.Description));
123 SqlParameter e = cmd.Parameters.Add("assetType", SqlDbType.TinyInt); 172 command.Parameters.Add(database.CreateParameter("assetType", asset.Type));
124 e.Value = asset.Type; 173 command.Parameters.Add(database.CreateParameter("local", asset.Local));
125 SqlParameter g = cmd.Parameters.Add("local", SqlDbType.TinyInt); 174 command.Parameters.Add(database.CreateParameter("temporary", asset.Temporary));
126 g.Value = asset.Local; 175 command.Parameters.Add(database.CreateParameter("data", asset.Data));
127 SqlParameter h = cmd.Parameters.Add("temporary", SqlDbType.TinyInt); 176
128 h.Value = asset.Temporary; 177 command.ExecuteNonQuery();
129 SqlParameter i = cmd.Parameters.Add("data", SqlDbType.Image);
130 i.Value = asset.Data;
131
132 cmd.ExecuteNonQuery();
133 } 178 }
134 } 179 }
135 180
@@ -139,7 +184,7 @@ namespace OpenSim.Data.MSSQL
139 /// <param name="asset">the asset</param> 184 /// <param name="asset">the asset</param>
140 override public void UpdateAsset(AssetBase asset) 185 override public void UpdateAsset(AssetBase asset)
141 { 186 {
142 using (IDbCommand command = database.Query("UPDATE assets set id = @id, " + 187 using (AutoClosingSqlCommand command = database.Query("UPDATE assets set id = @id, " +
143 "name = @name, " + 188 "name = @name, " +
144 "description = @description," + 189 "description = @description," +
145 "assetType = @assetType," + 190 "assetType = @assetType," +
@@ -148,22 +193,14 @@ namespace OpenSim.Data.MSSQL
148 "data = @data where " + 193 "data = @data where " +
149 "id = @keyId;")) 194 "id = @keyId;"))
150 { 195 {
151 SqlParameter param1 = new SqlParameter("@id", asset.FullID.ToString()); 196 command.Parameters.Add(database.CreateParameter("id", asset.FullID));
152 SqlParameter param2 = new SqlParameter("@name", asset.Name); 197 command.Parameters.Add(database.CreateParameter("name", asset.Name));
153 SqlParameter param3 = new SqlParameter("@description", asset.Description); 198 command.Parameters.Add(database.CreateParameter("description", asset.Description));
154 SqlParameter param4 = new SqlParameter("@assetType", asset.Type); 199 command.Parameters.Add(database.CreateParameter("assetType", asset.Type));
155 SqlParameter param6 = new SqlParameter("@local", asset.Local); 200 command.Parameters.Add(database.CreateParameter("local", asset.Local));
156 SqlParameter param7 = new SqlParameter("@temporary", asset.Temporary); 201 command.Parameters.Add(database.CreateParameter("temporary", asset.Temporary));
157 SqlParameter param8 = new SqlParameter("@data", asset.Data); 202 command.Parameters.Add(database.CreateParameter("data", asset.Data));
158 SqlParameter param9 = new SqlParameter("@keyId", asset.FullID.ToString()); 203 command.Parameters.Add(database.CreateParameter("@keyId", asset.FullID));
159 command.Parameters.Add(param1);
160 command.Parameters.Add(param2);
161 command.Parameters.Add(param3);
162 command.Parameters.Add(param4);
163 command.Parameters.Add(param6);
164 command.Parameters.Add(param7);
165 command.Parameters.Add(param8);
166 command.Parameters.Add(param9);
167 204
168 try 205 try
169 { 206 {
@@ -192,57 +229,69 @@ namespace OpenSim.Data.MSSQL
192 229
193 #endregion 230 #endregion
194 231
195 #region IPlugin Members 232 #region Private Methods
196
197 override public void Dispose() { }
198 233
199 /// <summary> 234 /// <summary>
200 /// <para>Initialises asset interface</para> 235 /// Migration method
201 /// <para> 236 /// <list type="bullet">
202 /// TODO: this would allow you to pass in connnect info as 237 /// <item>Execute "CreateAssetsTable.sql" if tableName == null</item>
203 /// a string instead of file, if someone writes the support 238 /// </list>
204 /// </para>
205 /// </summary> 239 /// </summary>
206 /// <param name="connect">connect string</param> 240 /// <param name="tableName">Name of table</param>
207 override public void Initialise(string connect) 241 private void UpgradeAssetsTable(string tableName)
208 { 242 {
209 Initialise(); 243 // null as the version, indicates that the table didn't exist
244 if (tableName == null)
245 {
246 m_log.Info("[ASSET DB]: Creating new database tables");
247 database.ExecuteResourceSql("CreateAssetsTable.sql");
248 return;
249 }
210 } 250 }
211 251
212 /// <summary> 252 /// <summary>
213 /// Initialises asset interface 253 /// Ensure that the assets related tables exists and are at the latest version
214 /// </summary> 254 /// </summary>
215 /// <remarks>it use mssql_connection.ini</remarks> 255 private void TestTables()
216 override public void Initialise()
217 { 256 {
218 IniFile gridDataMSSqlFile = new IniFile("mssql_connection.ini"); 257 Dictionary<string, string> tableList = new Dictionary<string, string>();
219 string settingDataSource = gridDataMSSqlFile.ParseFileReadValue("data_source");
220 string settingInitialCatalog = gridDataMSSqlFile.ParseFileReadValue("initial_catalog");
221 string settingPersistSecurityInfo = gridDataMSSqlFile.ParseFileReadValue("persist_security_info");
222 string settingUserId = gridDataMSSqlFile.ParseFileReadValue("user_id");
223 string settingPassword = gridDataMSSqlFile.ParseFileReadValue("password");
224 258
225 database = 259 tableList["assets"] = null;
226 new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, 260 database.GetTableVersion(tableList);
227 settingPassword);
228 261
229 TestTables(); 262 UpgradeAssetsTable(tableList["assets"]);
230 }
231 263
232 /// <summary> 264 //Special for Migrations
233 /// Database provider version. 265 using (AutoClosingSqlCommand cmd = database.Query("select * from migrations where name = '" + _migrationStore + "'"))
234 /// </summary> 266 {
235 override public string Version 267 try
236 { 268 {
237 get { return database.getVersion(); } 269 bool insert = true;
238 } 270 using (SqlDataReader reader = cmd.ExecuteReader())
271 {
272 if (reader.Read()) insert = false;
273 }
274 if (insert)
275 {
276 cmd.CommandText = "insert into migrations(name, version) values('" + _migrationStore + "', 1)";
277 cmd.ExecuteNonQuery();
278 }
279 }
280 catch
281 {
282 //No migrations table
283 //HACK create one and add data
284 cmd.CommandText = "create table migrations(name varchar(100), version int)";
285 cmd.ExecuteNonQuery();
286
287 cmd.CommandText = "insert into migrations(name, version) values('migrations', 1)";
288 cmd.ExecuteNonQuery();
289
290 cmd.CommandText = "insert into migrations(name, version) values('" + _migrationStore + "', 1)";
291 cmd.ExecuteNonQuery();
292 }
293 }
239 294
240 /// <summary>
241 /// The name of this DB provider.
242 /// </summary>
243 override public string Name
244 {
245 get { return "MSSQL Asset storage engine"; }
246 } 295 }
247 296
248 #endregion 297 #endregion
diff --git a/OpenSim/Data/MSSQL/MSSQLEstateData.cs b/OpenSim/Data/MSSQL/MSSQLEstateData.cs
index b42c67f..b3595d9 100644
--- a/OpenSim/Data/MSSQL/MSSQLEstateData.cs
+++ b/OpenSim/Data/MSSQL/MSSQLEstateData.cs
@@ -39,6 +39,8 @@ namespace OpenSim.Data.MSSQL
39{ 39{
40 public class MSSQLEstateData : IEstateDataStore 40 public class MSSQLEstateData : IEstateDataStore
41 { 41 {
42 private const string _migrationStore = "EstateStore";
43
42 private static readonly ILog _Log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); 44 private static readonly ILog _Log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
43 45
44 private MSSQLManager _Database; 46 private MSSQLManager _Database;
@@ -74,15 +76,7 @@ namespace OpenSim.Data.MSSQL
74 } 76 }
75 77
76 //Migration settings 78 //Migration settings
77 using (SqlConnection connection = _Database.DatabaseConnection()) 79 _Database.CheckMigration(_migrationStore);
78 {
79 Assembly assem = GetType().Assembly;
80 MSSQLMigration migration = new MSSQLMigration(connection, assem, "EstateStore");
81
82 migration.Update();
83
84 connection.Close();
85 }
86 80
87 //Interesting way to get parameters! Maybe implement that also with other types 81 //Interesting way to get parameters! Maybe implement that also with other types
88 Type t = typeof(EstateSettings); 82 Type t = typeof(EstateSettings);
diff --git a/OpenSim/Data/MSSQL/MSSQLGridData.cs b/OpenSim/Data/MSSQL/MSSQLGridData.cs
index 47edc4b..552dc04 100644
--- a/OpenSim/Data/MSSQL/MSSQLGridData.cs
+++ b/OpenSim/Data/MSSQL/MSSQLGridData.cs
@@ -28,9 +28,8 @@
28using System; 28using System;
29using System.Collections.Generic; 29using System.Collections.Generic;
30using System.Data; 30using System.Data;
31using System.Data.SqlClient;
31using System.Reflection; 32using System.Reflection;
32using System.Security.Cryptography;
33using System.Text;
34using OpenMetaverse; 33using OpenMetaverse;
35using log4net; 34using log4net;
36using OpenSim.Framework; 35using OpenSim.Framework;
@@ -42,6 +41,8 @@ namespace OpenSim.Data.MSSQL
42 /// </summary> 41 /// </summary>
43 public class MSSQLGridData : GridDataBase 42 public class MSSQLGridData : GridDataBase
44 { 43 {
44 private const string _migrationStore = "GridStore";
45
45 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); 46 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
46 47
47 /// <summary> 48 /// <summary>
@@ -49,49 +50,62 @@ namespace OpenSim.Data.MSSQL
49 /// </summary> 50 /// </summary>
50 private MSSQLManager database; 51 private MSSQLManager database;
51 52
52 private string m_regionsTableName; 53 private string m_regionsTableName = "regions";
54
55 #region IPlugin Members
53 56
54 override public void Initialise() 57 override public void Initialise()
55 { 58 {
56 m_log.Info("[MSSQLGridData]: " + Name + " cannot be default-initialized!"); 59 m_log.Info("[GRID DB]: " + Name + " cannot be default-initialized!");
57 throw new PluginNotInitialisedException (Name); 60 throw new PluginNotInitialisedException(Name);
58 } 61 }
59 62
60 /// <summary> 63 /// <summary>
61 /// Initialises the Grid Interface 64 /// Initialises the Grid Interface
62 /// </summary> 65 /// </summary>
63 /// <param name="connect">connect string</param> 66 /// <param name="connectionString">connect string</param>
64 /// <remarks>use mssql_connection.ini</remarks> 67 /// <remarks>use mssql_connection.ini</remarks>
65 override public void Initialise(string connect) 68 override public void Initialise(string connectionString)
66 { 69 {
67 // TODO: make the connect string actually do something 70 if (string.IsNullOrEmpty(connectionString))
68 IniFile iniFile = new IniFile("mssql_connection.ini");
69
70 string settingDataSource = iniFile.ParseFileReadValue("data_source");
71 string settingInitialCatalog = iniFile.ParseFileReadValue("initial_catalog");
72 string settingPersistSecurityInfo = iniFile.ParseFileReadValue("persist_security_info");
73 string settingUserId = iniFile.ParseFileReadValue("user_id");
74 string settingPassword = iniFile.ParseFileReadValue("password");
75
76 m_regionsTableName = iniFile.ParseFileReadValue("regionstablename");
77 if (m_regionsTableName == null)
78 { 71 {
79 m_regionsTableName = "regions"; 72 database = new MSSQLManager(connectionString);
80 } 73 }
74 else
75 {
76 // TODO: make the connect string actually do something
77 IniFile iniFile = new IniFile("mssql_connection.ini");
78
79 string settingDataSource = iniFile.ParseFileReadValue("data_source");
80 string settingInitialCatalog = iniFile.ParseFileReadValue("initial_catalog");
81 string settingPersistSecurityInfo = iniFile.ParseFileReadValue("persist_security_info");
82 string settingUserId = iniFile.ParseFileReadValue("user_id");
83 string settingPassword = iniFile.ParseFileReadValue("password");
81 84
82 database = 85 m_regionsTableName = iniFile.ParseFileReadValue("regionstablename");
83 new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, 86 if (m_regionsTableName == null)
84 settingPassword); 87 {
88 m_regionsTableName = "regions";
89 }
90
91 database =
92 new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId,
93 settingPassword);
94 }
85 95
96 //TODO this can be removed at a certain time
86 TestTables(); 97 TestTables();
98
99 //New migrations check of store
100 database.CheckMigration(_migrationStore);
87 } 101 }
88 102
89 /// <summary> 103 /// <summary>
90 /// 104 /// Test is region
91 /// </summary> 105 /// </summary>
92 private void TestTables() 106 private void TestTables()
93 { 107 {
94 using (IDbCommand cmd = database.Query("SELECT TOP 1 * FROM " + m_regionsTableName, new Dictionary<string, string>())) 108 using (AutoClosingSqlCommand cmd = database.Query("SELECT TOP 1 * FROM " + m_regionsTableName, new Dictionary<string, string>()))
95 { 109 {
96 try 110 try
97 { 111 {
@@ -103,6 +117,36 @@ namespace OpenSim.Data.MSSQL
103 database.ExecuteResourceSql("Mssql-regions.sql"); 117 database.ExecuteResourceSql("Mssql-regions.sql");
104 } 118 }
105 } 119 }
120 using (AutoClosingSqlCommand cmd = database.Query("select * from migrations where name = '" + _migrationStore + "'"))
121 {
122 //Special for Migrations to create backword compatible
123 try
124 {
125 bool insert = true;
126 using (SqlDataReader reader = cmd.ExecuteReader())
127 {
128 if (reader.Read()) insert = false;
129 }
130 if (insert)
131 {
132 cmd.CommandText = "insert into migrations(name, version) values('" + _migrationStore + "', 1)";
133 cmd.ExecuteNonQuery();
134 }
135 }
136 catch
137 {
138 //No migrations table
139 //HACK create one and add data
140 cmd.CommandText = "create table migrations(name varchar(100), version int)";
141 cmd.ExecuteNonQuery();
142
143 cmd.CommandText = "insert into migrations(name, version) values('migrations', 1)";
144 cmd.ExecuteNonQuery();
145
146 cmd.CommandText = "insert into migrations(name, version) values('" + _migrationStore + "', 1)";
147 cmd.ExecuteNonQuery();
148 }
149 }
106 } 150 }
107 151
108 /// <summary> 152 /// <summary>
@@ -110,7 +154,7 @@ namespace OpenSim.Data.MSSQL
110 /// </summary> 154 /// </summary>
111 override public void Dispose() 155 override public void Dispose()
112 { 156 {
113 // nothing to close 157 database = null;
114 } 158 }
115 159
116 /// <summary> 160 /// <summary>
@@ -131,18 +175,44 @@ namespace OpenSim.Data.MSSQL
131 get { return "0.1"; } 175 get { return "0.1"; }
132 } 176 }
133 177
178 #endregion
179
180 #region Public override GridDataBase methods
181
134 /// <summary> 182 /// <summary>
135 /// NOT IMPLEMENTED,
136 /// Returns a list of regions within the specified ranges 183 /// Returns a list of regions within the specified ranges
137 /// </summary> 184 /// </summary>
138 /// <param name="a">minimum X coordinate</param> 185 /// <param name="xmin">minimum X coordinate</param>
139 /// <param name="b">minimum Y coordinate</param> 186 /// <param name="ymin">minimum Y coordinate</param>
140 /// <param name="c">maximum X coordinate</param> 187 /// <param name="xmax">maximum X coordinate</param>
141 /// <param name="d">maximum Y coordinate</param> 188 /// <param name="ymax">maximum Y coordinate</param>
142 /// <returns>null</returns> 189 /// <returns>null</returns>
143 /// <remarks>always return null</remarks> 190 /// <remarks>always return null</remarks>
144 override public RegionProfileData[] GetProfilesInRange(uint a, uint b, uint c, uint d) 191 override public RegionProfileData[] GetProfilesInRange(uint xmin, uint ymin, uint xmax, uint ymax)
145 { 192 {
193 using (AutoClosingSqlCommand command = database.Query("SELECT * FROM regions WHERE locX >= @xmin AND locX <= @xmax AND locY >= @ymin AND locY <= @ymax"))
194 {
195 command.Parameters.Add(database.CreateParameter("xmin", xmin));
196 command.Parameters.Add(database.CreateParameter("ymin", ymin));
197 command.Parameters.Add(database.CreateParameter("xmax", xmax));
198 command.Parameters.Add(database.CreateParameter("ymax", ymax));
199
200 List<RegionProfileData> rows = new List<RegionProfileData>();
201
202 using (SqlDataReader reader = command.ExecuteReader())
203 {
204 while (reader.Read())
205 {
206 rows.Add(ReadSimRow(reader));
207 }
208 }
209
210 if (rows.Count > 0)
211 {
212 return rows.ToArray();
213 }
214 }
215 m_log.Info("[GRID DB] : Found no regions within range.");
146 return null; 216 return null;
147 } 217 }
148 218
@@ -153,22 +223,20 @@ namespace OpenSim.Data.MSSQL
153 /// <returns>Sim profile</returns> 223 /// <returns>Sim profile</returns>
154 override public RegionProfileData GetProfileByHandle(ulong handle) 224 override public RegionProfileData GetProfileByHandle(ulong handle)
155 { 225 {
156 226 using (AutoClosingSqlCommand command = database.Query("SELECT * FROM " + m_regionsTableName + " WHERE regionHandle = @handle"))
157 Dictionary<string, string> param = new Dictionary<string, string>();
158 param["handle"] = handle.ToString();
159
160 try
161 { 227 {
162 using (IDbCommand result = database.Query("SELECT * FROM " + m_regionsTableName + " WHERE regionHandle = @handle", param)) 228 command.Parameters.Add(database.CreateParameter("handle", handle));
163 using (IDataReader reader = result.ExecuteReader()) 229
230 using (SqlDataReader reader = command.ExecuteReader())
164 { 231 {
165 return database.getRegionRow(reader); 232 if (reader.Read())
233 {
234 return ReadSimRow(reader);
235 }
166 } 236 }
167 } 237 }
168 catch 238 m_log.InfoFormat("[GRID DB] : No region found with handle : {0}", handle);
169 { 239 return null;
170 return null;
171 }
172 } 240 }
173 241
174 /// <summary> 242 /// <summary>
@@ -178,50 +246,49 @@ namespace OpenSim.Data.MSSQL
178 /// <returns>The sim profile</returns> 246 /// <returns>The sim profile</returns>
179 override public RegionProfileData GetProfileByUUID(UUID uuid) 247 override public RegionProfileData GetProfileByUUID(UUID uuid)
180 { 248 {
181 Dictionary<string, string> param = new Dictionary<string, string>(); 249 using (AutoClosingSqlCommand command = database.Query("SELECT * FROM " + m_regionsTableName + " WHERE uuid = @uuid"))
182 param["uuid"] = uuid.ToString();
183
184 using (IDbCommand result = database.Query("SELECT * FROM " + m_regionsTableName + " WHERE uuid = @uuid", param))
185 using (IDataReader reader = result.ExecuteReader())
186 { 250 {
187 return database.getRegionRow(reader); 251 command.Parameters.Add(database.CreateParameter("uuid", uuid));
188 }
189 252
253 using (SqlDataReader reader = command.ExecuteReader())
254 {
255 if (reader.Read())
256 {
257 return ReadSimRow(reader);
258 }
259 }
260 }
261 m_log.InfoFormat("[GRID DB] : No region found with UUID : {0}", uuid);
262 return null;
190 } 263 }
191 264
192 /// <summary> 265 /// <summary>
193 /// Returns a sim profile from it's Region name string 266 /// Returns a sim profile from it's Region name string
194 /// </summary> 267 /// </summary>
195 /// <param name="uuid">The region name search query</param> 268 /// <param name="regionName">The region name search query</param>
196 /// <returns>The sim profile</returns> 269 /// <returns>The sim profile</returns>
197 override public RegionProfileData GetProfileByString(string regionName) 270 override public RegionProfileData GetProfileByString(string regionName)
198 { 271 {
199 if (regionName.Length > 2) 272 if (regionName.Length > 2)
200 { 273 {
201 try 274 using (AutoClosingSqlCommand command = database.Query("SELECT top 1 * FROM " + m_regionsTableName + " WHERE regionName like @regionName order by regionName"))
202 { 275 {
203 Dictionary<string, string> param = new Dictionary<string, string>(); 276 command.Parameters.Add(database.CreateParameter("regionName", regionName + "%"));
204 // Add % because this is a like query. 277
205 param["?regionName"] = regionName + "%"; 278 using (SqlDataReader reader = command.ExecuteReader())
206 // Order by statement will return shorter matches first. Only returns one record or no record.
207 using (IDbCommand result = database.Query("SELECT top 1 * FROM " + m_regionsTableName + " WHERE regionName like ?regionName order by regionName", param))
208 using (IDataReader reader = result.ExecuteReader())
209 { 279 {
210 return database.getRegionRow(reader); 280 if (reader.Read())
281 {
282 return ReadSimRow(reader);
283 }
211 } 284 }
212
213 } 285 }
214 catch (Exception e) 286 m_log.InfoFormat("[GRID DB] : No region found with regionName : {0}", regionName);
215 {
216 m_log.Error(e.ToString());
217 return null;
218 }
219 }
220 else
221 {
222 m_log.Error("[GRID DB]: Searched for a Region Name shorter then 3 characters");
223 return null; 287 return null;
224 } 288 }
289
290 m_log.Error("[GRID DB]: Searched for a Region Name shorter then 3 characters");
291 return null;
225 } 292 }
226 293
227 /// <summary> 294 /// <summary>
@@ -231,14 +298,11 @@ namespace OpenSim.Data.MSSQL
231 /// <returns>A dataresponse enum indicating success</returns> 298 /// <returns>A dataresponse enum indicating success</returns>
232 override public DataResponse AddProfile(RegionProfileData profile) 299 override public DataResponse AddProfile(RegionProfileData profile)
233 { 300 {
234 if (insertRegionRow(profile)) 301 if (InsertRegionRow(profile))
235 { 302 {
236 return DataResponse.RESPONSE_OK; 303 return DataResponse.RESPONSE_OK;
237 } 304 }
238 else 305 return DataResponse.RESPONSE_ERROR;
239 {
240 return DataResponse.RESPONSE_ERROR;
241 }
242 } 306 }
243 307
244 /// <summary> 308 /// <summary>
@@ -246,156 +310,43 @@ namespace OpenSim.Data.MSSQL
246 /// </summary> 310 /// </summary>
247 /// <param name="profile">The profile to update</param> 311 /// <param name="profile">The profile to update</param>
248 /// <returns>A dataresponse enum indicating success</returns> 312 /// <returns>A dataresponse enum indicating success</returns>
249 public override DataResponse UpdateProfile(RegionProfileData profile) 313 override public DataResponse UpdateProfile(RegionProfileData profile)
250 { 314 {
251 if (updateRegionRow(profile)) 315 if (UpdateRegionRow(profile))
252 { 316 {
253 return DataResponse.RESPONSE_OK; 317 return DataResponse.RESPONSE_OK;
254 } 318 }
255 else 319 return DataResponse.RESPONSE_ERROR;
256 {
257 return DataResponse.RESPONSE_ERROR;
258 }
259 } 320 }
260 321
261 /// <summary> 322 /// <summary>
262 /// Update the specified region in the database 323 /// Deletes a sim profile from the database
263 /// </summary> 324 /// </summary>
264 /// <param name="profile">The profile to update</param> 325 /// <param name="uuid">the sim UUID</param>
265 /// <returns>success ?</returns> 326 /// <returns>Successful?</returns>
266 public bool updateRegionRow(RegionProfileData profile) 327 //public DataResponse DeleteProfile(RegionProfileData profile)
328 override public DataResponse DeleteProfile(string uuid)
267 { 329 {
268 //Insert new region 330 using (AutoClosingSqlCommand command = database.Query("DELETE FROM regions WHERE uuid = @uuid;"))
269 string sql =
270 "UPDATE " + m_regionsTableName + @" SET
271 [regionHandle]=@regionHandle, [regionName]=@regionName,
272 [regionRecvKey]=@regionRecvKey, [regionSecret]=@regionSecret, [regionSendKey]=@regionSendKey,
273 [regionDataURI]=@regionDataURI, [serverIP]=@serverIP, [serverPort]=@serverPort, [serverURI]=@serverURI,
274 [locX]=@locX, [locY]=@locY, [locZ]=@locZ, [eastOverrideHandle]=@eastOverrideHandle,
275 [westOverrideHandle]=@westOverrideHandle, [southOverrideHandle]=@southOverrideHandle,
276 [northOverrideHandle]=@northOverrideHandle, [regionAssetURI]=@regionAssetURI,
277 [regionAssetRecvKey]=@regionAssetRecvKey, [regionAssetSendKey]=@regionAssetSendKey,
278 [regionUserURI]=@regionUserURI, [regionUserRecvKey]=@regionUserRecvKey, [regionUserSendKey]=@regionUserSendKey,
279 [regionMapTexture]=@regionMapTexture, [serverHttpPort]=@serverHttpPort,
280 [serverRemotingPort]=@serverRemotingPort, [owner_uuid]=@owner_uuid
281 where [uuid]=@uuid";
282
283 Dictionary<string, string> parameters = new Dictionary<string, string>();
284
285 parameters["regionHandle"] = profile.regionHandle.ToString();
286 parameters["regionName"] = profile.regionName;
287 parameters["uuid"] = profile.ToString();
288 parameters["regionRecvKey"] = profile.regionRecvKey;
289 parameters["regionSecret"] = profile.regionSecret;
290 parameters["regionSendKey"] = profile.regionSendKey;
291 parameters["regionDataURI"] = profile.regionDataURI;
292 parameters["serverIP"] = profile.serverIP;
293 parameters["serverPort"] = profile.serverPort.ToString();
294 parameters["serverURI"] = profile.serverURI;
295 parameters["locX"] = profile.regionLocX.ToString();
296 parameters["locY"] = profile.regionLocY.ToString();
297 parameters["locZ"] = profile.regionLocZ.ToString();
298 parameters["eastOverrideHandle"] = profile.regionEastOverrideHandle.ToString();
299 parameters["westOverrideHandle"] = profile.regionWestOverrideHandle.ToString();
300 parameters["northOverrideHandle"] = profile.regionNorthOverrideHandle.ToString();
301 parameters["southOverrideHandle"] = profile.regionSouthOverrideHandle.ToString();
302 parameters["regionAssetURI"] = profile.regionAssetURI;
303 parameters["regionAssetRecvKey"] = profile.regionAssetRecvKey;
304 parameters["regionAssetSendKey"] = profile.regionAssetSendKey;
305 parameters["regionUserURI"] = profile.regionUserURI;
306 parameters["regionUserRecvKey"] = profile.regionUserRecvKey;
307 parameters["regionUserSendKey"] = profile.regionUserSendKey;
308 parameters["regionMapTexture"] = profile.regionMapTextureID.ToString();
309 parameters["serverHttpPort"] = profile.httpPort.ToString();
310 parameters["serverRemotingPort"] = profile.remotingPort.ToString();
311 parameters["owner_uuid"] = profile.owner_uuid.ToString();
312
313 bool returnval = false;
314
315 try
316 { 331 {
317 using (IDbCommand result = database.Query(sql, parameters)) 332 command.Parameters.Add(database.CreateParameter("uuid", uuid));
333 try
318 { 334 {
319 335 command.ExecuteNonQuery();
320 if (result.ExecuteNonQuery() == 1) 336 return DataResponse.RESPONSE_OK;
321 returnval = true;
322
323 } 337 }
324 } 338 catch (Exception e)
325 catch (Exception e)
326 {
327 m_log.Error("MSSQLManager : " + e.ToString());
328 }
329
330 return returnval;
331 }
332 /// <summary>
333 /// Creates a new region in the database
334 /// </summary>
335 /// <param name="profile">The region profile to insert</param>
336 /// <returns>Successful?</returns>
337 public bool insertRegionRow(RegionProfileData profile)
338 {
339 //Insert new region
340 string sql =
341 "INSERT INTO " + m_regionsTableName + @" ([regionHandle], [regionName], [uuid], [regionRecvKey], [regionSecret], [regionSendKey], [regionDataURI],
342 [serverIP], [serverPort], [serverURI], [locX], [locY], [locZ], [eastOverrideHandle], [westOverrideHandle],
343 [southOverrideHandle], [northOverrideHandle], [regionAssetURI], [regionAssetRecvKey], [regionAssetSendKey],
344 [regionUserURI], [regionUserRecvKey], [regionUserSendKey], [regionMapTexture], [serverHttpPort],
345 [serverRemotingPort], [owner_uuid])
346 VALUES (@regionHandle, @regionName, @uuid, @regionRecvKey, @regionSecret, @regionSendKey, @regionDataURI,
347 @serverIP, @serverPort, @serverURI, @locX, @locY, @locZ, @eastOverrideHandle, @westOverrideHandle,
348 @southOverrideHandle, @northOverrideHandle, @regionAssetURI, @regionAssetRecvKey, @regionAssetSendKey,
349 @regionUserURI, @regionUserRecvKey, @regionUserSendKey, @regionMapTexture, @serverHttpPort, @serverRemotingPort, @owner_uuid);";
350
351 Dictionary<string, string> parameters = new Dictionary<string, string>();
352
353 parameters["regionHandle"] = profile.regionHandle.ToString();
354 parameters["regionName"] = profile.regionName;
355 parameters["uuid"] = profile.ToString();
356 parameters["regionRecvKey"] = profile.regionRecvKey;
357 parameters["regionSecret"] = profile.regionSecret;
358 parameters["regionSendKey"] = profile.regionSendKey;
359 parameters["regionDataURI"] = profile.regionDataURI;
360 parameters["serverIP"] = profile.serverIP;
361 parameters["serverPort"] = profile.serverPort.ToString();
362 parameters["serverURI"] = profile.serverURI;
363 parameters["locX"] = profile.regionLocX.ToString();
364 parameters["locY"] = profile.regionLocY.ToString();
365 parameters["locZ"] = profile.regionLocZ.ToString();
366 parameters["eastOverrideHandle"] = profile.regionEastOverrideHandle.ToString();
367 parameters["westOverrideHandle"] = profile.regionWestOverrideHandle.ToString();
368 parameters["northOverrideHandle"] = profile.regionNorthOverrideHandle.ToString();
369 parameters["southOverrideHandle"] = profile.regionSouthOverrideHandle.ToString();
370 parameters["regionAssetURI"] = profile.regionAssetURI;
371 parameters["regionAssetRecvKey"] = profile.regionAssetRecvKey;
372 parameters["regionAssetSendKey"] = profile.regionAssetSendKey;
373 parameters["regionUserURI"] = profile.regionUserURI;
374 parameters["regionUserRecvKey"] = profile.regionUserRecvKey;
375 parameters["regionUserSendKey"] = profile.regionUserSendKey;
376 parameters["regionMapTexture"] = profile.regionMapTextureID.ToString();
377 parameters["serverHttpPort"] = profile.httpPort.ToString();
378 parameters["serverRemotingPort"] = profile.remotingPort.ToString();
379 parameters["owner_uuid"] = profile.owner_uuid.ToString();
380
381 bool returnval = false;
382
383 try
384 {
385 using (IDbCommand result = database.Query(sql, parameters))
386 { 339 {
387 if (result.ExecuteNonQuery() == 1) 340 m_log.DebugFormat("[GRID DB] : Error deleting region info, error is : {0}", e.Message);
388 returnval = true; 341 return DataResponse.RESPONSE_ERROR;
389 } 342 }
390 } 343 }
391 catch (Exception e)
392 {
393 m_log.Error("[GRID DB]: " + e.ToString());
394 }
395
396 return returnval;
397 } 344 }
398 345
346 #endregion
347
348 #region Methods that are not used or deprecated (still needed because of base class)
349
399 /// <summary> 350 /// <summary>
400 /// DEPRECATED. Attempts to authenticate a region by comparing a shared secret. 351 /// DEPRECATED. Attempts to authenticate a region by comparing a shared secret.
401 /// </summary> 352 /// </summary>
@@ -436,6 +387,7 @@ namespace OpenSim.Data.MSSQL
436 387
437 /// <summary> 388 /// <summary>
438 /// NOT IMPLEMENTED 389 /// NOT IMPLEMENTED
390 /// WHEN IS THIS GONNA BE IMPLEMENTED.
439 /// </summary> 391 /// </summary>
440 /// <param name="x"></param> 392 /// <param name="x"></param>
441 /// <param name="y"></param> 393 /// <param name="y"></param>
@@ -444,5 +396,218 @@ namespace OpenSim.Data.MSSQL
444 { 396 {
445 return null; 397 return null;
446 } 398 }
399
400 #endregion
401
402 #region private methods
403
404 /// <summary>
405 /// Reads a region row from a database reader
406 /// </summary>
407 /// <param name="reader">An active database reader</param>
408 /// <returns>A region profile</returns>
409 private static RegionProfileData ReadSimRow(IDataRecord reader)
410 {
411 RegionProfileData retval = new RegionProfileData();
412
413 // Region Main gotta-have-or-we-return-null parts
414 UInt64 tmp64;
415 if (!UInt64.TryParse(reader["regionHandle"].ToString(), out tmp64))
416 {
417 return null;
418 }
419
420 retval.regionHandle = tmp64;
421
422 UUID tmp_uuid;
423 if (!UUID.TryParse((string)reader["uuid"], out tmp_uuid))
424 {
425 return null;
426 }
427
428 retval.UUID = tmp_uuid;
429
430 // non-critical parts
431 retval.regionName = reader["regionName"].ToString();
432 retval.originUUID = new UUID((string)reader["originUUID"]);
433
434 // Secrets
435 retval.regionRecvKey = reader["regionRecvKey"].ToString();
436 retval.regionSecret = reader["regionSecret"].ToString();
437 retval.regionSendKey = reader["regionSendKey"].ToString();
438
439 // Region Server
440 retval.regionDataURI = reader["regionDataURI"].ToString();
441 retval.regionOnline = false; // Needs to be pinged before this can be set.
442 retval.serverIP = reader["serverIP"].ToString();
443 retval.serverPort = Convert.ToUInt32(reader["serverPort"]);
444 retval.serverURI = reader["serverURI"].ToString();
445 retval.httpPort = Convert.ToUInt32(reader["serverHttpPort"].ToString());
446 retval.remotingPort = Convert.ToUInt32(reader["serverRemotingPort"].ToString());
447
448 // Location
449 retval.regionLocX = Convert.ToUInt32(reader["locX"].ToString());
450 retval.regionLocY = Convert.ToUInt32(reader["locY"].ToString());
451 retval.regionLocZ = Convert.ToUInt32(reader["locZ"].ToString());
452
453 // Neighbours - 0 = No Override
454 retval.regionEastOverrideHandle = Convert.ToUInt64(reader["eastOverrideHandle"].ToString());
455 retval.regionWestOverrideHandle = Convert.ToUInt64(reader["westOverrideHandle"].ToString());
456 retval.regionSouthOverrideHandle = Convert.ToUInt64(reader["southOverrideHandle"].ToString());
457 retval.regionNorthOverrideHandle = Convert.ToUInt64(reader["northOverrideHandle"].ToString());
458
459 // Assets
460 retval.regionAssetURI = reader["regionAssetURI"].ToString();
461 retval.regionAssetRecvKey = reader["regionAssetRecvKey"].ToString();
462 retval.regionAssetSendKey = reader["regionAssetSendKey"].ToString();
463
464 // Userserver
465 retval.regionUserURI = reader["regionUserURI"].ToString();
466 retval.regionUserRecvKey = reader["regionUserRecvKey"].ToString();
467 retval.regionUserSendKey = reader["regionUserSendKey"].ToString();
468
469 // World Map Addition
470 UUID.TryParse((string)reader["regionMapTexture"], out retval.regionMapTextureID);
471 UUID.TryParse((string)reader["owner_uuid"], out retval.owner_uuid);
472
473 return retval;
474 }
475
476 /// <summary>
477 /// Update the specified region in the database
478 /// </summary>
479 /// <param name="profile">The profile to update</param>
480 /// <returns>success ?</returns>
481 private bool UpdateRegionRow(RegionProfileData profile)
482 {
483 bool returnval = false;
484
485 //Insert new region
486 string sql =
487 "UPDATE " + m_regionsTableName + @" SET
488 [regionHandle]=@regionHandle, [regionName]=@regionName,
489 [regionRecvKey]=@regionRecvKey, [regionSecret]=@regionSecret, [regionSendKey]=@regionSendKey,
490 [regionDataURI]=@regionDataURI, [serverIP]=@serverIP, [serverPort]=@serverPort, [serverURI]=@serverURI,
491 [locX]=@locX, [locY]=@locY, [locZ]=@locZ, [eastOverrideHandle]=@eastOverrideHandle,
492 [westOverrideHandle]=@westOverrideHandle, [southOverrideHandle]=@southOverrideHandle,
493 [northOverrideHandle]=@northOverrideHandle, [regionAssetURI]=@regionAssetURI,
494 [regionAssetRecvKey]=@regionAssetRecvKey, [regionAssetSendKey]=@regionAssetSendKey,
495 [regionUserURI]=@regionUserURI, [regionUserRecvKey]=@regionUserRecvKey, [regionUserSendKey]=@regionUserSendKey,
496 [regionMapTexture]=@regionMapTexture, [serverHttpPort]=@serverHttpPort,
497 [serverRemotingPort]=@serverRemotingPort, [owner_uuid]=@owner_uuid , [originUUID]=@originUUID
498 where [uuid]=@uuid";
499
500 using (AutoClosingSqlCommand command = database.Query(sql))
501 {
502 command.Parameters.Add(database.CreateParameter("regionHandle", profile.regionHandle));
503 command.Parameters.Add(database.CreateParameter("regionName", profile.regionName));
504 command.Parameters.Add(database.CreateParameter("uuid", profile.UUID));
505 command.Parameters.Add(database.CreateParameter("regionRecvKey", profile.regionRecvKey));
506 command.Parameters.Add(database.CreateParameter("regionSecret", profile.regionSecret));
507 command.Parameters.Add(database.CreateParameter("regionSendKey", profile.regionSendKey));
508 command.Parameters.Add(database.CreateParameter("regionDataURI", profile.regionDataURI));
509 command.Parameters.Add(database.CreateParameter("serverIP", profile.serverIP));
510 command.Parameters.Add(database.CreateParameter("serverPort", profile.serverPort));
511 command.Parameters.Add(database.CreateParameter("serverURI", profile.serverURI));
512 command.Parameters.Add(database.CreateParameter("locX", profile.regionLocX));
513 command.Parameters.Add(database.CreateParameter("locY", profile.regionLocY));
514 command.Parameters.Add(database.CreateParameter("locZ", profile.regionLocZ));
515 command.Parameters.Add(database.CreateParameter("eastOverrideHandle", profile.regionEastOverrideHandle));
516 command.Parameters.Add(database.CreateParameter("westOverrideHandle", profile.regionWestOverrideHandle));
517 command.Parameters.Add(database.CreateParameter("northOverrideHandle", profile.regionNorthOverrideHandle));
518 command.Parameters.Add(database.CreateParameter("southOverrideHandle", profile.regionSouthOverrideHandle));
519 command.Parameters.Add(database.CreateParameter("regionAssetURI", profile.regionAssetURI));
520 command.Parameters.Add(database.CreateParameter("regionAssetRecvKey", profile.regionAssetRecvKey));
521 command.Parameters.Add(database.CreateParameter("regionAssetSendKey", profile.regionAssetSendKey));
522 command.Parameters.Add(database.CreateParameter("regionUserURI", profile.regionUserURI));
523 command.Parameters.Add(database.CreateParameter("regionUserRecvKey", profile.regionUserRecvKey));
524 command.Parameters.Add(database.CreateParameter("regionUserSendKey", profile.regionUserSendKey));
525 command.Parameters.Add(database.CreateParameter("regionMapTexture", profile.regionMapTextureID));
526 command.Parameters.Add(database.CreateParameter("serverHttpPort", profile.httpPort));
527 command.Parameters.Add(database.CreateParameter("serverRemotingPort", profile.remotingPort));
528 command.Parameters.Add(database.CreateParameter("owner_uuid", profile.owner_uuid));
529 command.Parameters.Add(database.CreateParameter("originUUID", profile.originUUID));
530
531 try
532 {
533 command.ExecuteNonQuery();
534 returnval = true;
535 }
536 catch (Exception e)
537 {
538 m_log.Error("[GRID DB] : Error updating region, error: " + e.Message);
539 }
540 }
541
542 return returnval;
543 }
544
545 /// <summary>
546 /// Creates a new region in the database
547 /// </summary>
548 /// <param name="profile">The region profile to insert</param>
549 /// <returns>Successful?</returns>
550 private bool InsertRegionRow(RegionProfileData profile)
551 {
552 bool returnval = false;
553
554 //Insert new region
555 string sql =
556 "INSERT INTO " + m_regionsTableName + @" ([regionHandle], [regionName], [uuid], [regionRecvKey], [regionSecret], [regionSendKey], [regionDataURI],
557 [serverIP], [serverPort], [serverURI], [locX], [locY], [locZ], [eastOverrideHandle], [westOverrideHandle],
558 [southOverrideHandle], [northOverrideHandle], [regionAssetURI], [regionAssetRecvKey], [regionAssetSendKey],
559 [regionUserURI], [regionUserRecvKey], [regionUserSendKey], [regionMapTexture], [serverHttpPort],
560 [serverRemotingPort], [owner_uuid], [originUUID])
561 VALUES (@regionHandle, @regionName, @uuid, @regionRecvKey, @regionSecret, @regionSendKey, @regionDataURI,
562 @serverIP, @serverPort, @serverURI, @locX, @locY, @locZ, @eastOverrideHandle, @westOverrideHandle,
563 @southOverrideHandle, @northOverrideHandle, @regionAssetURI, @regionAssetRecvKey, @regionAssetSendKey,
564 @regionUserURI, @regionUserRecvKey, @regionUserSendKey, @regionMapTexture, @serverHttpPort, @serverRemotingPort, @owner_uuid, @originUUID);";
565
566 using (AutoClosingSqlCommand command = database.Query(sql))
567 {
568 command.Parameters.Add(database.CreateParameter("regionHandle", profile.regionHandle));
569 command.Parameters.Add(database.CreateParameter("regionName", profile.regionName));
570 command.Parameters.Add(database.CreateParameter("uuid", profile.UUID));
571 command.Parameters.Add(database.CreateParameter("regionRecvKey", profile.regionRecvKey));
572 command.Parameters.Add(database.CreateParameter("regionSecret", profile.regionSecret));
573 command.Parameters.Add(database.CreateParameter("regionSendKey", profile.regionSendKey));
574 command.Parameters.Add(database.CreateParameter("regionDataURI", profile.regionDataURI));
575 command.Parameters.Add(database.CreateParameter("serverIP", profile.serverIP));
576 command.Parameters.Add(database.CreateParameter("serverPort", profile.serverPort));
577 command.Parameters.Add(database.CreateParameter("serverURI", profile.serverURI));
578 command.Parameters.Add(database.CreateParameter("locX", profile.regionLocX));
579 command.Parameters.Add(database.CreateParameter("locY", profile.regionLocY));
580 command.Parameters.Add(database.CreateParameter("locZ", profile.regionLocZ));
581 command.Parameters.Add(database.CreateParameter("eastOverrideHandle", profile.regionEastOverrideHandle));
582 command.Parameters.Add(database.CreateParameter("westOverrideHandle", profile.regionWestOverrideHandle));
583 command.Parameters.Add(database.CreateParameter("northOverrideHandle", profile.regionNorthOverrideHandle));
584 command.Parameters.Add(database.CreateParameter("southOverrideHandle", profile.regionSouthOverrideHandle));
585 command.Parameters.Add(database.CreateParameter("regionAssetURI", profile.regionAssetURI));
586 command.Parameters.Add(database.CreateParameter("regionAssetRecvKey", profile.regionAssetRecvKey));
587 command.Parameters.Add(database.CreateParameter("regionAssetSendKey", profile.regionAssetSendKey));
588 command.Parameters.Add(database.CreateParameter("regionUserURI", profile.regionUserURI));
589 command.Parameters.Add(database.CreateParameter("regionUserRecvKey", profile.regionUserRecvKey));
590 command.Parameters.Add(database.CreateParameter("regionUserSendKey", profile.regionUserSendKey));
591 command.Parameters.Add(database.CreateParameter("regionMapTexture", profile.regionMapTextureID));
592 command.Parameters.Add(database.CreateParameter("serverHttpPort", profile.httpPort));
593 command.Parameters.Add(database.CreateParameter("serverRemotingPort", profile.remotingPort));
594 command.Parameters.Add(database.CreateParameter("owner_uuid", profile.owner_uuid));
595 command.Parameters.Add(database.CreateParameter("originUUID", profile.originUUID));
596
597 try
598 {
599 command.ExecuteNonQuery();
600 returnval = true;
601 }
602 catch (Exception e)
603 {
604 m_log.Error("[GRID DB] : Error inserting region, error: " + e.Message);
605 }
606 }
607
608 return returnval;
609 }
610
611 #endregion
447 } 612 }
448} 613}
diff --git a/OpenSim/Data/MSSQL/MSSQLInventoryData.cs b/OpenSim/Data/MSSQL/MSSQLInventoryData.cs
index e7df7c1..03600e2 100644
--- a/OpenSim/Data/MSSQL/MSSQLInventoryData.cs
+++ b/OpenSim/Data/MSSQL/MSSQLInventoryData.cs
@@ -41,49 +41,53 @@ namespace OpenSim.Data.MSSQL
41 /// </summary> 41 /// </summary>
42 public class MSSQLInventoryData : IInventoryDataPlugin 42 public class MSSQLInventoryData : IInventoryDataPlugin
43 { 43 {
44 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); 44 private const string _migrationStore = "InventoryStore";
45 45
46 #region Helper converters to preserve unsigned bitfield-type data in DB roundtrips via signed int32s 46 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
47 private static int ConvertUint32BitFieldToInt32(uint bitField)
48 {
49 return BitConverter.ToInt32(BitConverter.GetBytes(bitField), 0);
50 }
51 private static uint ConvertInt32BitFieldToUint32(int bitField)
52 {
53 return BitConverter.ToUInt32(BitConverter.GetBytes(bitField), 0);
54 }
55 #endregion
56 47
57 /// <summary> 48 /// <summary>
58 /// The database manager 49 /// The database manager
59 /// </summary> 50 /// </summary>
60 private MSSQLManager database; 51 private MSSQLManager database;
61 52
62 public void Initialise() 53 #region IPlugin members
63 { 54
55 public void Initialise()
56 {
64 m_log.Info("[MSSQLInventoryData]: " + Name + " cannot be default-initialized!"); 57 m_log.Info("[MSSQLInventoryData]: " + Name + " cannot be default-initialized!");
65 throw new PluginNotInitialisedException (Name); 58 throw new PluginNotInitialisedException(Name);
66 } 59 }
67 60
68 /// <summary> 61 /// <summary>
69 /// Loads and initialises the MSSQL inventory storage interface 62 /// Loads and initialises the MSSQL inventory storage interface
70 /// </summary> 63 /// </summary>
71 /// <param name="connect">connect string</param> 64 /// <param name="connectionString">connect string</param>
72 /// <remarks>use mssql_connection.ini</remarks> 65 /// <remarks>use mssql_connection.ini</remarks>
73 public void Initialise(string connect) 66 public void Initialise(string connectionString)
74 { 67 {
75 // TODO: actually use the provided connect string 68 if (string.IsNullOrEmpty(connectionString))
76 IniFile gridDataMSSqlFile = new IniFile("mssql_connection.ini"); 69 {
77 string settingDataSource = gridDataMSSqlFile.ParseFileReadValue("data_source"); 70 database = new MSSQLManager(connectionString);
78 string settingInitialCatalog = gridDataMSSqlFile.ParseFileReadValue("initial_catalog"); 71 }
79 string settingPersistSecurityInfo = gridDataMSSqlFile.ParseFileReadValue("persist_security_info"); 72 else
80 string settingUserId = gridDataMSSqlFile.ParseFileReadValue("user_id"); 73 {
81 string settingPassword = gridDataMSSqlFile.ParseFileReadValue("password"); 74 IniFile gridDataMSSqlFile = new IniFile("mssql_connection.ini");
82 75 string settingDataSource = gridDataMSSqlFile.ParseFileReadValue("data_source");
83 database = 76 string settingInitialCatalog = gridDataMSSqlFile.ParseFileReadValue("initial_catalog");
84 new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, 77 string settingPersistSecurityInfo = gridDataMSSqlFile.ParseFileReadValue("persist_security_info");
85 settingPassword); 78 string settingUserId = gridDataMSSqlFile.ParseFileReadValue("user_id");
79 string settingPassword = gridDataMSSqlFile.ParseFileReadValue("password");
80
81 database =
82 new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId,
83 settingPassword);
84 }
85
86 //TODO remove this at one point
86 TestTables(); 87 TestTables();
88
89 //New migrations check of store
90 database.CheckMigration(_migrationStore);
87 } 91 }
88 92
89 #region Test and initialization code 93 #region Test and initialization code
@@ -132,6 +136,37 @@ namespace OpenSim.Data.MSSQL
132 136
133 UpgradeFoldersTable(tableList["inventoryfolders"]); 137 UpgradeFoldersTable(tableList["inventoryfolders"]);
134 UpgradeItemsTable(tableList["inventoryitems"]); 138 UpgradeItemsTable(tableList["inventoryitems"]);
139
140 using (AutoClosingSqlCommand cmd = database.Query("select * from migrations where name = '" + _migrationStore + "'"))
141 {
142 //Special for Migrations to create backword compatible
143 try
144 {
145 bool insert = true;
146 using (SqlDataReader reader = cmd.ExecuteReader())
147 {
148 if (reader.Read()) insert = false;
149 }
150 if (insert)
151 {
152 cmd.CommandText = "insert into migrations(name, version) values('" + _migrationStore + "', 1)";
153 cmd.ExecuteNonQuery();
154 }
155 }
156 catch
157 {
158 //No migrations table
159 //HACK create one and add data
160 cmd.CommandText = "create table migrations(name varchar(100), version int)";
161 cmd.ExecuteNonQuery();
162
163 cmd.CommandText = "insert into migrations(name, version) values('migrations', 1)";
164 cmd.ExecuteNonQuery();
165
166 cmd.CommandText = "insert into migrations(name, version) values('" + _migrationStore + "', 1)";
167 cmd.ExecuteNonQuery();
168 }
169 }
135 } 170 }
136 171
137 #endregion 172 #endregion
@@ -150,7 +185,7 @@ namespace OpenSim.Data.MSSQL
150 /// </summary> 185 /// </summary>
151 public void Dispose() 186 public void Dispose()
152 { 187 {
153 // Do nothing. 188 database = null;
154 } 189 }
155 190
156 /// <summary> 191 /// <summary>
@@ -162,39 +197,9 @@ namespace OpenSim.Data.MSSQL
162 get { return database.getVersion(); } 197 get { return database.getVersion(); }
163 } 198 }
164 199
165 /// <summary> 200 #endregion
166 /// Returns a list of items in a specified folder
167 /// </summary>
168 /// <param name="folderID">The folder to search</param>
169 /// <returns>A list containing inventory items</returns>
170 public List<InventoryItemBase> getInventoryInFolder(UUID folderID)
171 {
172 try
173 {
174 List<InventoryItemBase> items = new List<InventoryItemBase>();
175
176 Dictionary<string, string> param = new Dictionary<string, string>();
177 param["parentFolderID"] = folderID.ToString();
178
179 using (IDbCommand result =
180 database.Query("SELECT * FROM inventoryitems WHERE parentFolderID = @parentFolderID", param))
181 using (IDataReader reader = result.ExecuteReader())
182 {
183
184 while (reader.Read())
185 items.Add(readInventoryItem(reader));
186
187 reader.Close();
188 }
189 201
190 return items; 202 #region Folder methods
191 }
192 catch (Exception e)
193 {
194 m_log.Error(e.ToString());
195 return null;
196 }
197 }
198 203
199 /// <summary> 204 /// <summary>
200 /// Returns a list of the root folders within a users inventory 205 /// Returns a list of the root folders within a users inventory
@@ -203,31 +208,7 @@ namespace OpenSim.Data.MSSQL
203 /// <returns>A list of folder objects</returns> 208 /// <returns>A list of folder objects</returns>
204 public List<InventoryFolderBase> getUserRootFolders(UUID user) 209 public List<InventoryFolderBase> getUserRootFolders(UUID user)
205 { 210 {
206 try 211 return getInventoryFolders(UUID.Zero, user);
207 {
208 Dictionary<string, string> param = new Dictionary<string, string>();
209 param["uuid"] = user.ToString();
210 param["zero"] = UUID.Zero.ToString();
211
212 using (IDbCommand result =
213 database.Query(
214 "SELECT * FROM inventoryfolders WHERE parentFolderID = @zero AND agentID = @uuid", param))
215 using (IDataReader reader = result.ExecuteReader())
216 {
217
218 List<InventoryFolderBase> items = new List<InventoryFolderBase>();
219 while (reader.Read())
220 items.Add(readInventoryFolder(reader));
221
222 return items;
223 }
224
225 }
226 catch (Exception e)
227 {
228 m_log.Error(e.ToString());
229 return null;
230 }
231 } 212 }
232 213
233 /// <summary> 214 /// <summary>
@@ -237,43 +218,21 @@ namespace OpenSim.Data.MSSQL
237 /// <returns></returns> 218 /// <returns></returns>
238 public InventoryFolderBase getUserRootFolder(UUID user) 219 public InventoryFolderBase getUserRootFolder(UUID user)
239 { 220 {
240 try 221 List<InventoryFolderBase> items = getUserRootFolders(user);
241 {
242 Dictionary<string, string> param = new Dictionary<string, string>();
243 param["uuid"] = user.ToString();
244 param["zero"] = UUID.Zero.ToString();
245
246 using (IDbCommand result =
247 database.Query(
248 "SELECT * FROM inventoryfolders WHERE parentFolderID = @zero AND agentID = @uuid", param))
249 using (IDataReader reader = result.ExecuteReader())
250 {
251
252 List<InventoryFolderBase> items = new List<InventoryFolderBase>();
253 while (reader.Read())
254 items.Add(readInventoryFolder(reader));
255
256 InventoryFolderBase rootFolder = null;
257 222
258 // There should only ever be one root folder for a user. However, if there's more 223 InventoryFolderBase rootFolder = null;
259 // than one we'll simply use the first one rather than failing. It would be even
260 // nicer to print some message to this effect, but this feels like it's too low a
261 // to put such a message out, and it's too minor right now to spare the time to
262 // suitably refactor.
263 if (items.Count > 0)
264 {
265 rootFolder = items[0];
266 }
267
268 return rootFolder;
269 }
270 224
271 } 225 // There should only ever be one root folder for a user. However, if there's more
272 catch (Exception e) 226 // than one we'll simply use the first one rather than failing. It would be even
227 // nicer to print some message to this effect, but this feels like it's too low a
228 // to put such a message out, and it's too minor right now to spare the time to
229 // suitably refactor.
230 if (items.Count > 0)
273 { 231 {
274 m_log.Error(e.ToString()); 232 rootFolder = items[0];
275 return null;
276 } 233 }
234
235 return rootFolder;
277 } 236 }
278 237
279 /// <summary> 238 /// <summary>
@@ -283,156 +242,235 @@ namespace OpenSim.Data.MSSQL
283 /// <returns>A list of inventory folders</returns> 242 /// <returns>A list of inventory folders</returns>
284 public List<InventoryFolderBase> getInventoryFolders(UUID parentID) 243 public List<InventoryFolderBase> getInventoryFolders(UUID parentID)
285 { 244 {
286 try 245 return getInventoryFolders(parentID, UUID.Zero);
246 }
247
248 /// <summary>
249 /// Returns a specified inventory folder
250 /// </summary>
251 /// <param name="folderID">The folder to return</param>
252 /// <returns>A folder class</returns>
253 public InventoryFolderBase getInventoryFolder(UUID folderID)
254 {
255 using (AutoClosingSqlCommand command = database.Query("SELECT * FROM inventoryfolders WHERE folderID = @folderID"))
287 { 256 {
288 Dictionary<string, string> param = new Dictionary<string, string>(); 257 command.Parameters.Add(database.CreateParameter("folderID", folderID));
289 param["parentFolderID"] = parentID.ToString();
290 258
291 using (IDbCommand result = 259 using (IDataReader reader = command.ExecuteReader())
292 database.Query("SELECT * FROM inventoryfolders WHERE parentFolderID = @parentFolderID", param))
293 using (IDataReader reader = result.ExecuteReader())
294 { 260 {
295 List<InventoryFolderBase> items = new List<InventoryFolderBase>(); 261 if (reader.Read())
296 262 {
297 while (reader.Read()) 263 return readInventoryFolder(reader);
298 items.Add(readInventoryFolder(reader)); 264 }
299
300 return items;
301 } 265 }
302 } 266 }
303 catch (Exception e) 267 m_log.InfoFormat("[INVENTORY DB] : FOund no inventory folder with ID : {0}", folderID);
304 { 268 return null;
305 m_log.Error(e.ToString());
306 return null;
307 }
308 } 269 }
309 270
310 /// <summary> 271 /// <summary>
311 /// Reads a one item from an SQL result 272 /// Returns all child folders in the hierarchy from the parent folder and down.
273 /// Does not return the parent folder itself.
312 /// </summary> 274 /// </summary>
313 /// <param name="reader">The SQL Result</param> 275 /// <param name="parentID">The folder to get subfolders for</param>
314 /// <returns>the item read</returns> 276 /// <returns>A list of inventory folders</returns>
315 private static InventoryItemBase readInventoryItem(IDataReader reader) 277 public List<InventoryFolderBase> getFolderHierarchy(UUID parentID)
316 { 278 {
317 try 279 //Note maybe change this to use a Dataset that loading in all folders of a user and then go throw it that way.
318 { 280 //Note this is changed so it opens only one connection to the database and not everytime it wants to get data.
319 InventoryItemBase item = new InventoryItemBase();
320 281
321 item.ID = new UUID((string) reader["inventoryID"]); 282 List<InventoryFolderBase> folders = new List<InventoryFolderBase>();
322 item.AssetID = new UUID((string) reader["assetID"]);
323 item.AssetType = (int) reader["assetType"];
324 item.Folder = new UUID((string) reader["parentFolderID"]);
325 item.Owner = new UUID((string) reader["avatarID"]);
326 item.Name = (string) reader["inventoryName"];
327 item.Description = (string) reader["inventoryDescription"];
328 item.NextPermissions = ConvertInt32BitFieldToUint32((int)reader["inventoryNextPermissions"]);
329 item.CurrentPermissions = ConvertInt32BitFieldToUint32((int)reader["inventoryCurrentPermissions"]);
330 item.InvType = (int) reader["invType"];
331 item.Creator = new UUID((string) reader["creatorID"]);
332 item.BasePermissions = ConvertInt32BitFieldToUint32((int)reader["inventoryBasePermissions"]);
333 item.EveryOnePermissions = ConvertInt32BitFieldToUint32((int)reader["inventoryEveryOnePermissions"]);
334 item.SalePrice = (int) reader["salePrice"];
335 item.SaleType = Convert.ToByte(reader["saleType"]);
336 item.CreationDate = (int) reader["creationDate"];
337 item.GroupID = new UUID(reader["groupID"].ToString());
338 item.GroupOwned = Convert.ToBoolean(reader["groupOwned"]);
339 item.Flags = ConvertInt32BitFieldToUint32((int)reader["flags"]);
340 283
341 return item; 284 using (AutoClosingSqlCommand command = database.Query("SELECT * FROM inventoryfolders WHERE parentFolderID = @parentID"))
342 }
343 catch (SqlException e)
344 { 285 {
345 m_log.Error(e.ToString()); 286 command.Parameters.Add(database.CreateParameter("@parentID", parentID));
346 }
347 287
348 return null; 288 folders.AddRange(getInventoryFolders(command));
289
290 List<InventoryFolderBase> tempFolders = new List<InventoryFolderBase>();
291
292 foreach (InventoryFolderBase folderBase in folders)
293 {
294 tempFolders.AddRange(getFolderHierarchy(folderBase.ID, command));
295 }
296 if (tempFolders.Count > 0)
297 {
298 folders.AddRange(tempFolders);
299 }
300 }
301 return folders;
349 } 302 }
350 303
351 /// <summary> 304 /// <summary>
352 /// Returns a specified inventory item 305 /// Creates a new inventory folder
353 /// </summary> 306 /// </summary>
354 /// <param name="item">The item to return</param> 307 /// <param name="folder">Folder to create</param>
355 /// <returns>An inventory item</returns> 308 public void addInventoryFolder(InventoryFolderBase folder)
356 public InventoryItemBase getInventoryItem(UUID itemID)
357 { 309 {
358 try 310 string sql =
359 { 311 "INSERT INTO inventoryfolders ([folderID], [agentID], [parentFolderID], [folderName], [type], [version]) VALUES ";
360 Dictionary<string, string> param = new Dictionary<string, string>(); 312 sql += "(@folderID, @agentID, @parentFolderID, @folderName, @type, @version);";
361 param["inventoryID"] = itemID.ToString();
362 313
363 using (IDbCommand result =
364 database.Query("SELECT * FROM inventoryitems WHERE inventoryID = @inventoryID", param))
365 using (IDataReader reader = result.ExecuteReader())
366 {
367 314
368 InventoryItemBase item = null; 315 using (AutoClosingSqlCommand command = database.Query(sql))
369 if (reader.Read()) 316 {
370 item = readInventoryItem(reader); 317 command.Parameters.Add(database.CreateParameter("folderID", folder.ID));
318 command.Parameters.Add(database.CreateParameter("agentID", folder.Owner));
319 command.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID));
320 command.Parameters.Add(database.CreateParameter("folderName", folder.Name));
321 command.Parameters.Add(database.CreateParameter("type", folder.Type));
322 command.Parameters.Add(database.CreateParameter("version", folder.Version));
371 323
372 return item; 324 try
325 {
326 //IDbCommand result = database.Query(sql, param);
327 command.ExecuteNonQuery();
328 }
329 catch (Exception e)
330 {
331 m_log.ErrorFormat("[ASSET DB] Error : {0}", e.Message);
373 } 332 }
374 } 333 }
375 catch (Exception e)
376 {
377 m_log.Error(e.ToString());
378 }
379 return null;
380 } 334 }
381 335
382 /// <summary> 336 /// <summary>
383 /// Reads a list of inventory folders returned by a query. 337 /// Updates an inventory folder
384 /// </summary> 338 /// </summary>
385 /// <param name="reader">A MSSQL Data Reader</param> 339 /// <param name="folder">Folder to update</param>
386 /// <returns>A List containing inventory folders</returns> 340 public void updateInventoryFolder(InventoryFolderBase folder)
387 protected static InventoryFolderBase readInventoryFolder(IDataReader reader)
388 { 341 {
389 try 342 using (AutoClosingSqlCommand command = database.Query("UPDATE inventoryfolders set folderID = @folderID, " +
343 "agentID = @agentID, " +
344 "parentFolderID = @parentFolderID," +
345 "folderName = @folderName," +
346 "type = @type," +
347 "version = @version where " +
348 "folderID = @keyFolderID;"))
390 { 349 {
391 InventoryFolderBase folder = new InventoryFolderBase(); 350 command.Parameters.Add(database.CreateParameter("folderID", folder.ID));
392 folder.Owner = new UUID((string) reader["agentID"]); 351 command.Parameters.Add(database.CreateParameter("agentID", folder.Owner));
393 folder.ParentID = new UUID((string) reader["parentFolderID"]); 352 command.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID));
394 folder.ID = new UUID((string) reader["folderID"]); 353 command.Parameters.Add(database.CreateParameter("folderName", folder.Name));
395 folder.Name = (string) reader["folderName"]; 354 command.Parameters.Add(database.CreateParameter("type", folder.Type));
396 folder.Type = (short) reader["type"]; 355 command.Parameters.Add(database.CreateParameter("version", folder.Version));
397 folder.Version = Convert.ToUInt16(reader["version"]); 356 command.Parameters.Add(database.CreateParameter("@keyFolderID", folder.ID));
398 return folder; 357 try
358 {
359 command.ExecuteNonQuery();
360 }
361 catch (Exception e)
362 {
363 m_log.ErrorFormat("[ASSET DB] Error : {0}", e.Message);
364 }
399 } 365 }
400 catch (Exception e) 366 }
367
368 /// <summary>
369 /// Updates an inventory folder
370 /// </summary>
371 /// <param name="folder">Folder to update</param>
372 public void moveInventoryFolder(InventoryFolderBase folder)
373 {
374 using (IDbCommand command = database.Query("UPDATE inventoryfolders set " +
375 "parentFolderID = @parentFolderID where " +
376 "folderID = @folderID;"))
401 { 377 {
402 m_log.Error(e.ToString()); 378 command.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID));
403 } 379 command.Parameters.Add(database.CreateParameter("@folderID", folder.ID));
404 380
405 return null; 381 try
382 {
383 command.ExecuteNonQuery();
384 }
385 catch (Exception e)
386 {
387 m_log.ErrorFormat("[ASSET DB] Error : {0}", e.Message);
388 }
389 }
406 } 390 }
407 391
408 /// <summary> 392 /// <summary>
409 /// Returns a specified inventory folder 393 /// Delete an inventory folder
410 /// </summary> 394 /// </summary>
411 /// <param name="folder">The folder to return</param> 395 /// <param name="folderID">Id of folder to delete</param>
412 /// <returns>A folder class</returns> 396 public void deleteInventoryFolder(UUID folderID)
413 public InventoryFolderBase getInventoryFolder(UUID folderID)
414 { 397 {
415 try 398 using (SqlConnection connection = database.DatabaseConnection())
416 { 399 {
417 Dictionary<string, string> param = new Dictionary<string, string>(); 400 List<InventoryFolderBase> subFolders;
418 param["uuid"] = folderID.ToString(); 401 using (SqlCommand command = new SqlCommand("SELECT * FROM inventoryfolders WHERE parentFolderID = @parentID", connection))
402 {
403 command.Parameters.Add(database.CreateParameter("@parentID", string.Empty));
419 404
420 using (IDbCommand result = database.Query("SELECT * FROM inventoryfolders WHERE folderID = @uuid", param)) 405 AutoClosingSqlCommand autoCommand = new AutoClosingSqlCommand(command);
421 using (IDataReader reader = result.ExecuteReader()) 406
407 subFolders = getFolderHierarchy(folderID, autoCommand);
408 }
409
410 //Delete all sub-folders
411 foreach (InventoryFolderBase f in subFolders)
422 { 412 {
413 DeleteOneFolder(f.ID, connection);
414 DeleteItemsInFolder(f.ID, connection);
415 }
423 416
424 reader.Read(); 417 //Delete the actual row
418 DeleteOneFolder(folderID, connection);
419 DeleteItemsInFolder(folderID, connection);
420
421 connection.Close();
422 }
423 }
424
425 #endregion
425 426
426 InventoryFolderBase folder = readInventoryFolder(reader); 427 #region Item Methods
428
429 /// <summary>
430 /// Returns a list of items in a specified folder
431 /// </summary>
432 /// <param name="folderID">The folder to search</param>
433 /// <returns>A list containing inventory items</returns>
434 public List<InventoryItemBase> getInventoryInFolder(UUID folderID)
435 {
436 using (AutoClosingSqlCommand command = database.Query("SELECT * FROM inventoryitems WHERE parentFolderID = @parentFolderID"))
437 {
438 command.Parameters.Add(database.CreateParameter("parentFolderID", folderID));
439
440 List<InventoryItemBase> items = new List<InventoryItemBase>();
427 441
428 return folder; 442 using (SqlDataReader reader = command.ExecuteReader())
443 {
444 while (reader.Read())
445 {
446 items.Add(readInventoryItem(reader));
447 }
429 } 448 }
449 return items;
430 } 450 }
431 catch (Exception e) 451 }
452
453 /// <summary>
454 /// Returns a specified inventory item
455 /// </summary>
456 /// <param name="itemID">The item ID</param>
457 /// <returns>An inventory item</returns>
458 public InventoryItemBase getInventoryItem(UUID itemID)
459 {
460 using (AutoClosingSqlCommand result = database.Query("SELECT * FROM inventoryitems WHERE inventoryID = @inventoryID"))
432 { 461 {
433 m_log.Error(e.ToString()); 462 result.Parameters.Add(database.CreateParameter("inventoryID", itemID));
434 return null; 463
464 using (IDataReader reader = result.ExecuteReader())
465 {
466 if (reader.Read())
467 {
468 return readInventoryItem(reader);
469 }
470 }
435 } 471 }
472 m_log.InfoFormat("[INVENTORY DB] : Found no inventory item with ID : {0}", itemID);
473 return null;
436 } 474 }
437 475
438 /// <summary> 476 /// <summary>
@@ -448,46 +486,44 @@ namespace OpenSim.Data.MSSQL
448 } 486 }
449 487
450 string sql = "INSERT INTO inventoryitems"; 488 string sql = "INSERT INTO inventoryitems";
451 sql += 489 sql += "([inventoryID], [assetID], [assetType], [parentFolderID], [avatarID], [inventoryName]"
452 "([inventoryID], [assetID], [assetType], [parentFolderID], [avatarID], [inventoryName]"
453 + ", [inventoryDescription], [inventoryNextPermissions], [inventoryCurrentPermissions]" 490 + ", [inventoryDescription], [inventoryNextPermissions], [inventoryCurrentPermissions]"
454 + ", [invType], [creatorID], [inventoryBasePermissions], [inventoryEveryOnePermissions]" 491 + ", [invType], [creatorID], [inventoryBasePermissions], [inventoryEveryOnePermissions]"
455 + ", [salePrice], [saleType], [creationDate], [groupID], [groupOwned], [flags]) VALUES "; 492 + ", [salePrice], [saleType], [creationDate], [groupID], [groupOwned], [flags]) VALUES ";
456 sql += 493 sql += "(@inventoryID, @assetID, @assetType, @parentFolderID, @avatarID, @inventoryName, @inventoryDescription"
457 "(@inventoryID, @assetID, @assetType, @parentFolderID, @avatarID, @inventoryName, @inventoryDescription"
458 + ", @inventoryNextPermissions, @inventoryCurrentPermissions, @invType, @creatorID" 494 + ", @inventoryNextPermissions, @inventoryCurrentPermissions, @invType, @creatorID"
459 + ", @inventoryBasePermissions, @inventoryEveryOnePermissions, @salePrice, @saleType" 495 + ", @inventoryBasePermissions, @inventoryEveryOnePermissions, @salePrice, @saleType"
460 + ", @creationDate, @groupID, @groupOwned, @flags);"; 496 + ", @creationDate, @groupID, @groupOwned, @flags);";
461 497
462 using (AutoClosingSqlCommand command = database.Query(sql)) 498 using (AutoClosingSqlCommand command = database.Query(sql))
463 { 499 {
464 command.Parameters.AddWithValue("inventoryID", item.ID.ToString()); 500 command.Parameters.Add(database.CreateParameter("inventoryID", item.ID));
465 command.Parameters.AddWithValue("assetID", item.AssetID.ToString()); 501 command.Parameters.Add(database.CreateParameter("assetID", item.AssetID));
466 command.Parameters.AddWithValue("assetType", item.AssetType.ToString()); 502 command.Parameters.Add(database.CreateParameter("assetType", item.AssetType));
467 command.Parameters.AddWithValue("parentFolderID", item.Folder.ToString()); 503 command.Parameters.Add(database.CreateParameter("parentFolderID", item.Folder));
468 command.Parameters.AddWithValue("avatarID", item.Owner.ToString()); 504 command.Parameters.Add(database.CreateParameter("avatarID", item.Owner));
469 command.Parameters.AddWithValue("inventoryName", item.Name); 505 command.Parameters.Add(database.CreateParameter("inventoryName", item.Name));
470 command.Parameters.AddWithValue("inventoryDescription", item.Description); 506 command.Parameters.Add(database.CreateParameter("inventoryDescription", item.Description));
471 command.Parameters.AddWithValue("inventoryNextPermissions", ConvertUint32BitFieldToInt32(item.NextPermissions)); 507 command.Parameters.Add(database.CreateParameter("inventoryNextPermissions", item.NextPermissions));
472 command.Parameters.AddWithValue("inventoryCurrentPermissions", ConvertUint32BitFieldToInt32(item.CurrentPermissions)); 508 command.Parameters.Add(database.CreateParameter("inventoryCurrentPermissions", item.CurrentPermissions));
473 command.Parameters.AddWithValue("invType", item.InvType); 509 command.Parameters.Add(database.CreateParameter("invType", item.InvType));
474 command.Parameters.AddWithValue("creatorID", item.Creator.ToString()); 510 command.Parameters.Add(database.CreateParameter("creatorID", item.Creator));
475 command.Parameters.AddWithValue("inventoryBasePermissions", ConvertUint32BitFieldToInt32(item.BasePermissions)); 511 command.Parameters.Add(database.CreateParameter("inventoryBasePermissions", item.BasePermissions));
476 command.Parameters.AddWithValue("inventoryEveryOnePermissions", ConvertUint32BitFieldToInt32(item.EveryOnePermissions)); 512 command.Parameters.Add(database.CreateParameter("inventoryEveryOnePermissions", item.EveryOnePermissions));
477 command.Parameters.AddWithValue("salePrice", item.SalePrice); 513 command.Parameters.Add(database.CreateParameter("salePrice", item.SalePrice));
478 command.Parameters.AddWithValue("saleType", item.SaleType); 514 command.Parameters.Add(database.CreateParameter("saleType", item.SaleType));
479 command.Parameters.AddWithValue("creationDate", item.CreationDate); 515 command.Parameters.Add(database.CreateParameter("creationDate", item.CreationDate));
480 command.Parameters.AddWithValue("groupID", item.GroupID.ToString()); 516 command.Parameters.Add(database.CreateParameter("groupID", item.GroupID));
481 command.Parameters.AddWithValue("groupOwned", item.GroupOwned); 517 command.Parameters.Add(database.CreateParameter("groupOwned", item.GroupOwned));
482 command.Parameters.AddWithValue("flags", ConvertUint32BitFieldToInt32(item.Flags)); 518 command.Parameters.Add(database.CreateParameter("flags", item.Flags));
483 519
484 try 520 try
485 { 521 {
486 command.ExecuteNonQuery(); 522 command.ExecuteNonQuery();
487 } 523 }
488 catch (SqlException e) 524 catch (Exception e)
489 { 525 {
490 m_log.Error(e.ToString()); 526 m_log.Error("[INVENTORY DB] Error inserting item :" + e.Message);
491 } 527 }
492 } 528 }
493 529
@@ -520,26 +556,26 @@ namespace OpenSim.Data.MSSQL
520 "flags = @flags where " + 556 "flags = @flags where " +
521 "inventoryID = @keyInventoryID;")) 557 "inventoryID = @keyInventoryID;"))
522 { 558 {
523 command.Parameters.AddWithValue("inventoryID", item.ID.ToString()); 559 command.Parameters.Add(database.CreateParameter("inventoryID", item.ID));
524 command.Parameters.AddWithValue("assetID", item.AssetID.ToString()); 560 command.Parameters.Add(database.CreateParameter("assetID", item.AssetID));
525 command.Parameters.AddWithValue("assetType", item.AssetType.ToString()); 561 command.Parameters.Add(database.CreateParameter("assetType", item.AssetType));
526 command.Parameters.AddWithValue("parentFolderID", item.Folder.ToString()); 562 command.Parameters.Add(database.CreateParameter("parentFolderID", item.Folder));
527 command.Parameters.AddWithValue("avatarID", item.Owner.ToString()); 563 command.Parameters.Add(database.CreateParameter("avatarID", item.Owner));
528 command.Parameters.AddWithValue("inventoryName", item.Name); 564 command.Parameters.Add(database.CreateParameter("inventoryName", item.Name));
529 command.Parameters.AddWithValue("inventoryDescription", item.Description); 565 command.Parameters.Add(database.CreateParameter("inventoryDescription", item.Description));
530 command.Parameters.AddWithValue("inventoryNextPermissions", ConvertUint32BitFieldToInt32(item.NextPermissions)); 566 command.Parameters.Add(database.CreateParameter("inventoryNextPermissions", item.NextPermissions));
531 command.Parameters.AddWithValue("inventoryCurrentPermissions", ConvertUint32BitFieldToInt32(item.CurrentPermissions)); 567 command.Parameters.Add(database.CreateParameter("inventoryCurrentPermissions", item.CurrentPermissions));
532 command.Parameters.AddWithValue("invType", item.InvType); 568 command.Parameters.Add(database.CreateParameter("invType", item.InvType));
533 command.Parameters.AddWithValue("creatorID", item.Creator.ToString()); 569 command.Parameters.Add(database.CreateParameter("creatorID", item.Creator));
534 command.Parameters.AddWithValue("inventoryBasePermissions", ConvertUint32BitFieldToInt32(item.BasePermissions)); 570 command.Parameters.Add(database.CreateParameter("inventoryBasePermissions", item.BasePermissions));
535 command.Parameters.AddWithValue("inventoryEveryOnePermissions", ConvertUint32BitFieldToInt32(item.EveryOnePermissions)); 571 command.Parameters.Add(database.CreateParameter("inventoryEveryOnePermissions", item.EveryOnePermissions));
536 command.Parameters.AddWithValue("salePrice", item.SalePrice); 572 command.Parameters.Add(database.CreateParameter("salePrice", item.SalePrice));
537 command.Parameters.AddWithValue("saleType", item.SaleType); 573 command.Parameters.Add(database.CreateParameter("saleType", item.SaleType));
538 command.Parameters.AddWithValue("creationDate", item.CreationDate); 574 command.Parameters.Add(database.CreateParameter("creationDate", item.CreationDate));
539 command.Parameters.AddWithValue("groupID", item.GroupID.ToString()); 575 command.Parameters.Add(database.CreateParameter("groupID", item.GroupID));
540 command.Parameters.AddWithValue("groupOwned", item.GroupOwned); 576 command.Parameters.Add(database.CreateParameter("groupOwned", item.GroupOwned));
541 command.Parameters.AddWithValue("flags", ConvertUint32BitFieldToInt32(item.Flags)); 577 command.Parameters.Add(database.CreateParameter("flags", item.Flags));
542 command.Parameters.AddWithValue("@keyInventoryID", item.ID.ToString()); 578 command.Parameters.Add(database.CreateParameter("@keyInventoryID", item.ID));
543 579
544 try 580 try
545 { 581 {
@@ -547,225 +583,221 @@ namespace OpenSim.Data.MSSQL
547 } 583 }
548 catch (Exception e) 584 catch (Exception e)
549 { 585 {
550 m_log.Error(e.ToString()); 586 m_log.Error("[INVENTORY DB] Error updating item :" + e.Message);
551 } 587 }
552 } 588 }
553 } 589 }
554 590
591 // See IInventoryDataPlugin
592
555 /// <summary> 593 /// <summary>
556 /// Delete an item in inventory database 594 /// Delete an item in inventory database
557 /// </summary> 595 /// </summary>
558 /// <param name="item">the item UUID</param> 596 /// <param name="itemID">the item UUID</param>
559 public void deleteInventoryItem(UUID itemID) 597 public void deleteInventoryItem(UUID itemID)
560 { 598 {
561 try 599 using (AutoClosingSqlCommand command = database.Query("DELETE FROM inventoryitems WHERE inventoryID=@inventoryID"))
562 { 600 {
563 Dictionary<string, string> param = new Dictionary<string, string>(); 601 command.Parameters.Add(database.CreateParameter("inventoryID", itemID));
564 param["uuid"] = itemID.ToString(); 602 try
603 {
565 604
566 using (IDbCommand cmd = database.Query("DELETE FROM inventoryitems WHERE inventoryID=@uuid", param)) 605 command.ExecuteNonQuery();
606 }
607 catch (Exception e)
567 { 608 {
568 cmd.ExecuteNonQuery(); 609 m_log.Error("[INVENTORY DB] Error deleting item :" + e.Message);
569 } 610 }
570 } 611 }
571 catch (SqlException e)
572 {
573 m_log.Error(e.ToString());
574 }
575 } 612 }
576 613
614 #endregion
615
616 #region Private methods
617
577 /// <summary> 618 /// <summary>
578 /// Creates a new inventory folder 619 /// Delete an item in inventory database
579 /// </summary> 620 /// </summary>
580 /// <param name="folder">Folder to create</param> 621 /// <param name="folderID">the item ID</param>
581 public void addInventoryFolder(InventoryFolderBase folder) 622 /// <param name="connection">connection to the database</param>
623 private void DeleteItemsInFolder(UUID folderID, SqlConnection connection)
582 { 624 {
583 string sql = 625 using (SqlCommand command = new SqlCommand("DELETE FROM inventoryitems WHERE folderID=@folderID", connection))
584 "INSERT INTO inventoryfolders ([folderID], [agentID], [parentFolderID], [folderName], [type], [version]) VALUES ";
585 sql += "(@folderID, @agentID, @parentFolderID, @folderName, @type, @version);";
586
587
588 using (AutoClosingSqlCommand command = database.Query(sql))
589 { 626 {
590 command.Parameters.AddWithValue("folderID", folder.ID.ToString()); 627 command.Parameters.Add(database.CreateParameter("folderID", folderID));
591 command.Parameters.AddWithValue("agentID", folder.Owner.ToString());
592 command.Parameters.AddWithValue("parentFolderID", folder.ParentID.ToString());
593 command.Parameters.AddWithValue("folderName", folder.Name);
594 command.Parameters.AddWithValue("type", folder.Type);
595 command.Parameters.AddWithValue("version", Convert.ToInt32(folder.Version));
596 628
597 try 629 try
598 { 630 {
599 //IDbCommand result = database.Query(sql, param);
600 command.ExecuteNonQuery(); 631 command.ExecuteNonQuery();
601 } 632 }
602 catch (Exception e) 633 catch (Exception e)
603 { 634 {
604 m_log.Error(e.ToString()); 635 m_log.Error("[INVENTORY DB] Error deleting item :" + e.Message);
605 } 636 }
606 } 637 }
607 } 638 }
608 639
609 /// <summary> 640 /// <summary>
610 /// Updates an inventory folder 641 /// Gets the folder hierarchy in a loop.
611 /// </summary> 642 /// </summary>
612 /// <param name="folder">Folder to update</param> 643 /// <param name="parentID">parent ID.</param>
613 public void updateInventoryFolder(InventoryFolderBase folder) 644 /// <param name="command">SQL command/connection to database</param>
645 /// <returns></returns>
646 private static List<InventoryFolderBase> getFolderHierarchy(UUID parentID, AutoClosingSqlCommand command)
614 { 647 {
615 using (IDbCommand command = database.Query("UPDATE inventoryfolders set folderID = @folderID, " + 648 command.Parameters["@parentID"].Value = parentID.ToString();
616 "agentID = @agentID, " + 649
617 "parentFolderID = @parentFolderID," + 650 List<InventoryFolderBase> folders = getInventoryFolders(command);
618 "folderName = @folderName," + 651
619 "type = @type," + 652 if (folders.Count > 0)
620 "version = @version where " +
621 "folderID = @keyFolderID;"))
622 { 653 {
623 SqlParameter param1 = new SqlParameter("@folderID", folder.ID.ToString()); 654 List<InventoryFolderBase> tempFolders = new List<InventoryFolderBase>();
624 SqlParameter param2 = new SqlParameter("@agentID", folder.Owner.ToString());
625 SqlParameter param3 = new SqlParameter("@parentFolderID", folder.ParentID.ToString());
626 SqlParameter param4 = new SqlParameter("@folderName", folder.Name);
627 SqlParameter param5 = new SqlParameter("@type", folder.Type);
628 SqlParameter param6 = new SqlParameter("@version", Convert.ToInt32(folder.Version));
629 SqlParameter param7 = new SqlParameter("@keyFolderID", folder.ID.ToString());
630 command.Parameters.Add(param1);
631 command.Parameters.Add(param2);
632 command.Parameters.Add(param3);
633 command.Parameters.Add(param4);
634 command.Parameters.Add(param5);
635 command.Parameters.Add(param6);
636 command.Parameters.Add(param7);
637 655
638 try 656 foreach (InventoryFolderBase folderBase in folders)
639 { 657 {
640 command.ExecuteNonQuery(); 658 tempFolders.AddRange(getFolderHierarchy(folderBase.ID, command));
641 } 659 }
642 catch (Exception e) 660
661 if (tempFolders.Count > 0)
643 { 662 {
644 m_log.Error(e.ToString()); 663 folders.AddRange(tempFolders);
645 } 664 }
646 } 665 }
666 return folders;
647 } 667 }
648 668
649 /// <summary> 669 /// <summary>
650 /// Updates an inventory folder 670 /// Gets the inventory folders.
651 /// </summary> 671 /// </summary>
652 /// <param name="folder">Folder to update</param> 672 /// <param name="parentID">parentID, use UUID.Zero to get root</param>
653 public void moveInventoryFolder(InventoryFolderBase folder) 673 /// <param name="user">user id, use UUID.Zero, if you want all folders from a parentID.</param>
674 /// <returns></returns>
675 private List<InventoryFolderBase> getInventoryFolders(UUID parentID, UUID user)
654 { 676 {
655 using (IDbCommand command = database.Query("UPDATE inventoryfolders set folderID = @folderID, " + 677 using (AutoClosingSqlCommand command = database.Query("SELECT * FROM inventoryfolders WHERE parentFolderID = @parentID AND agentID LIKE @uuid"))
656 "parentFolderID = @parentFolderID," +
657 "folderID = @keyFolderID;"))
658 { 678 {
659 SqlParameter param1 = new SqlParameter("@folderID", folder.ID.ToString()); 679 if (user == UUID.Zero)
660 SqlParameter param2 = new SqlParameter("@parentFolderID", folder.ParentID.ToString());
661 SqlParameter param3 = new SqlParameter("@keyFolderID", folder.ID.ToString());
662 command.Parameters.Add(param1);
663 command.Parameters.Add(param2);
664 command.Parameters.Add(param3);
665
666 try
667 { 680 {
668 command.ExecuteNonQuery(); 681 command.Parameters.Add(database.CreateParameter("uuid", "%"));
669 } 682 }
670 catch (Exception e) 683 else
671 { 684 {
672 m_log.Error(e.ToString()); 685 command.Parameters.Add(database.CreateParameter("uuid", user));
673 } 686 }
687 command.Parameters.Add(database.CreateParameter("parentID", parentID));
688
689 return getInventoryFolders(command);
674 } 690 }
675 } 691 }
676 692
677 /// <summary> 693 /// <summary>
678 /// Append a list of all the child folders of a parent folder 694 /// Gets the inventory folders.
679 /// </summary> 695 /// </summary>
680 /// <param name="folders">list where folders will be appended</param> 696 /// <param name="command">SQLcommand.</param>
681 /// <param name="parentID">ID of parent</param> 697 /// <returns></returns>
682 protected void getInventoryFolders(ref List<InventoryFolderBase> folders, UUID parentID) 698 private static List<InventoryFolderBase> getInventoryFolders(AutoClosingSqlCommand command)
683 {
684 List<InventoryFolderBase> subfolderList = getInventoryFolders(parentID);
685
686 foreach (InventoryFolderBase f in subfolderList)
687 folders.Add(f);
688 }
689
690 // See IInventoryDataPlugin
691 public List<InventoryFolderBase> getFolderHierarchy(UUID parentID)
692 { 699 {
693 List<InventoryFolderBase> folders = new List<InventoryFolderBase>(); 700 using (IDataReader reader = command.ExecuteReader())
694 getInventoryFolders(ref folders, parentID); 701 {
695
696 for (int i = 0; i < folders.Count; i++)
697 getInventoryFolders(ref folders, folders[i].ID);
698 702
699 return folders; 703 List<InventoryFolderBase> items = new List<InventoryFolderBase>();
704 while (reader.Read())
705 {
706 items.Add(readInventoryFolder(reader));
707 }
708 return items;
709 }
700 } 710 }
701 711
702 /// <summary> 712 /// <summary>
703 /// Delete a folder in inventory databasae 713 /// Reads a list of inventory folders returned by a query.
704 /// </summary> 714 /// </summary>
705 /// <param name="folderID">the folder UUID</param> 715 /// <param name="reader">A MSSQL Data Reader</param>
706 protected void deleteOneFolder(UUID folderID) 716 /// <returns>A List containing inventory folders</returns>
717 protected static InventoryFolderBase readInventoryFolder(IDataReader reader)
707 { 718 {
708 try 719 try
709 { 720 {
710 Dictionary<string, string> param = new Dictionary<string, string>(); 721 InventoryFolderBase folder = new InventoryFolderBase();
711 param["folderID"] = folderID.ToString(); 722 folder.Owner = new UUID((string)reader["agentID"]);
723 folder.ParentID = new UUID((string)reader["parentFolderID"]);
724 folder.ID = new UUID((string)reader["folderID"]);
725 folder.Name = (string)reader["folderName"];
726 folder.Type = (short)reader["type"];
727 folder.Version = Convert.ToUInt16(reader["version"]);
712 728
713 using (IDbCommand cmd = database.Query("DELETE FROM inventoryfolders WHERE folderID=@folderID", param)) 729 return folder;
714 {
715 cmd.ExecuteNonQuery();
716 }
717 } 730 }
718 catch (SqlException e) 731 catch (Exception e)
719 { 732 {
720 m_log.Error(e.ToString()); 733 m_log.Error("[INVENTORY DB] Error reading inventory folder :" + e.Message);
721 } 734 }
735
736 return null;
722 } 737 }
723 738
724 /// <summary> 739 /// <summary>
725 /// Delete an item in inventory database 740 /// Reads a one item from an SQL result
726 /// </summary> 741 /// </summary>
727 /// <param name="folderID">the item ID</param> 742 /// <param name="reader">The SQL Result</param>
728 protected void deleteItemsInFolder(UUID folderID) 743 /// <returns>the item read</returns>
744 private static InventoryItemBase readInventoryItem(IDataRecord reader)
729 { 745 {
730 try 746 try
731 { 747 {
732 Dictionary<string, string> param = new Dictionary<string, string>(); 748 InventoryItemBase item = new InventoryItemBase();
733 param["parentFolderID"] = folderID.ToString();
734 749
750 item.ID = new UUID(reader["inventoryID"].ToString());
751 item.AssetID = new UUID(reader["assetID"].ToString());
752 item.AssetType = Convert.ToInt32(reader["assetType"].ToString());
753 item.Folder = new UUID(reader["parentFolderID"].ToString());
754 item.Owner = new UUID(reader["avatarID"].ToString());
755 item.Name = reader["inventoryName"].ToString();
756 item.Description = reader["inventoryDescription"].ToString();
757 item.NextPermissions = Convert.ToUInt32(reader["inventoryNextPermissions"]);
758 item.CurrentPermissions = Convert.ToUInt32(reader["inventoryCurrentPermissions"]);
759 item.InvType = Convert.ToInt32(reader["invType"].ToString());
760 item.Creator = new UUID(reader["creatorID"].ToString());
761 item.BasePermissions = Convert.ToUInt32(reader["inventoryBasePermissions"]);
762 item.EveryOnePermissions = Convert.ToUInt32(reader["inventoryEveryOnePermissions"]);
763 item.SalePrice = Convert.ToInt32(reader["salePrice"]);
764 item.SaleType = Convert.ToByte(reader["saleType"]);
765 item.CreationDate = Convert.ToInt32(reader["creationDate"]);
766 item.GroupID = new UUID(reader["groupID"].ToString());
767 item.GroupOwned = Convert.ToBoolean(reader["groupOwned"]);
768 item.Flags = Convert.ToUInt32(reader["flags"]);
735 769
736 using (IDbCommand cmd = 770 return item;
737 database.Query("DELETE FROM inventoryitems WHERE parentFolderID=@parentFolderID", param))
738 {
739 cmd.ExecuteNonQuery();
740 }
741 } 771 }
742 catch (SqlException e) 772 catch (SqlException e)
743 { 773 {
744 m_log.Error(e.ToString()); 774 m_log.Error("[INVENTORY DB] Error reading inventory item :" + e.Message);
745 } 775 }
776
777 return null;
746 } 778 }
747 779
748 /// <summary> 780 /// <summary>
749 /// Delete an inventory folder 781 /// Delete a folder in inventory databasae
750 /// </summary> 782 /// </summary>
751 /// <param name="folderId">Id of folder to delete</param> 783 /// <param name="folderID">the folder UUID</param>
752 public void deleteInventoryFolder(UUID folderID) 784 /// <param name="connection">connection to database</param>
785 private void DeleteOneFolder(UUID folderID, SqlConnection connection)
753 { 786 {
754 // lock (database) 787 try
755 { 788 {
756 List<InventoryFolderBase> subFolders = getFolderHierarchy(folderID); 789 using (SqlCommand command = new SqlCommand("DELETE FROM inventoryfolders WHERE folderID=@folderID", connection))
757
758 //Delete all sub-folders
759 foreach (InventoryFolderBase f in subFolders)
760 { 790 {
761 deleteOneFolder(f.ID); 791 command.Parameters.Add(database.CreateParameter("folderID", folderID));
762 deleteItemsInFolder(f.ID);
763 }
764 792
765 //Delete the actual row 793 command.ExecuteNonQuery();
766 deleteOneFolder(folderID); 794 }
767 deleteItemsInFolder(folderID); 795 }
796 catch (SqlException e)
797 {
798 m_log.Error("[INVENTORY DB] Error deleting folder :" + e.Message);
768 } 799 }
769 } 800 }
801 #endregion
770 } 802 }
771} 803}
diff --git a/OpenSim/Data/MSSQL/MSSQLLogData.cs b/OpenSim/Data/MSSQL/MSSQLLogData.cs
index b284c02..a617cea 100644
--- a/OpenSim/Data/MSSQL/MSSQLLogData.cs
+++ b/OpenSim/Data/MSSQL/MSSQLLogData.cs
@@ -26,6 +26,7 @@
26 */ 26 */
27 27
28using System; 28using System;
29using System.Data.SqlClient;
29using System.Reflection; 30using System.Reflection;
30using System.Collections.Generic; 31using System.Collections.Generic;
31using System.Data; 32using System.Data;
@@ -39,6 +40,8 @@ namespace OpenSim.Data.MSSQL
39 /// </summary> 40 /// </summary>
40 internal class MSSQLLogData : ILogDataPlugin 41 internal class MSSQLLogData : ILogDataPlugin
41 { 42 {
43 private const string _migrationStore = "LogStore";
44
42 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); 45 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
43 46
44 /// <summary> 47 /// <summary>
@@ -48,7 +51,7 @@ namespace OpenSim.Data.MSSQL
48 51
49 public void Initialise() 52 public void Initialise()
50 { 53 {
51 m_log.Info("[MSSQLLogData]: " + Name + " cannot be default-initialized!"); 54 m_log.Info("[LOG DB]: " + Name + " cannot be default-initialized!");
52 throw new PluginNotInitialisedException (Name); 55 throw new PluginNotInitialisedException (Name);
53 } 56 }
54 57
@@ -57,18 +60,37 @@ namespace OpenSim.Data.MSSQL
57 /// </summary> 60 /// </summary>
58 public void Initialise(string connect) 61 public void Initialise(string connect)
59 { 62 {
60 // TODO: do something with the connect string 63 if (string.IsNullOrEmpty(connect))
61 IniFile gridDataMSSqlFile = new IniFile("mssql_connection.ini"); 64 {
62 string settingDataSource = gridDataMSSqlFile.ParseFileReadValue("data_source"); 65 database = new MSSQLManager(connect);
63 string settingInitialCatalog = gridDataMSSqlFile.ParseFileReadValue("initial_catalog"); 66 }
64 string settingPersistSecurityInfo = gridDataMSSqlFile.ParseFileReadValue("persist_security_info"); 67 else
65 string settingUserId = gridDataMSSqlFile.ParseFileReadValue("user_id"); 68 {
66 string settingPassword = gridDataMSSqlFile.ParseFileReadValue("password"); 69 // TODO: do something with the connect string
67 70 IniFile gridDataMSSqlFile = new IniFile("mssql_connection.ini");
68 database = 71 string settingDataSource = gridDataMSSqlFile.ParseFileReadValue("data_source");
69 new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, 72 string settingInitialCatalog = gridDataMSSqlFile.ParseFileReadValue("initial_catalog");
70 settingPassword); 73 string settingPersistSecurityInfo = gridDataMSSqlFile.ParseFileReadValue("persist_security_info");
74 string settingUserId = gridDataMSSqlFile.ParseFileReadValue("user_id");
75 string settingPassword = gridDataMSSqlFile.ParseFileReadValue("password");
76
77 database =
78 new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId,
79 settingPassword);
80 }
81
82 //TODO when can this be removed
83 TestTable();
84
85 //Updating mechanisme
86 database.CheckMigration(_migrationStore);
87 }
71 88
89 /// <summary>
90 /// Can be removed someday!!!
91 /// </summary>
92 private void TestTable()
93 {
72 using (IDbCommand cmd = database.Query("select top 1 * from logs", new Dictionary<string, string>())) 94 using (IDbCommand cmd = database.Query("select top 1 * from logs", new Dictionary<string, string>()))
73 { 95 {
74 try 96 try
@@ -80,9 +102,38 @@ namespace OpenSim.Data.MSSQL
80 database.ExecuteResourceSql("Mssql-logs.sql"); 102 database.ExecuteResourceSql("Mssql-logs.sql");
81 } 103 }
82 } 104 }
105 using (AutoClosingSqlCommand cmd = database.Query("select * from migrations where name = '" + _migrationStore + "'"))
106 {
107 //Special for Migrations to create backword compatible
108 try
109 {
110 bool insert = true;
111 using (SqlDataReader reader = cmd.ExecuteReader())
112 {
113 if (reader.Read()) insert = false;
114 }
115 if (insert)
116 {
117 cmd.CommandText = "insert into migrations(name, version) values('" + _migrationStore + "', 1)";
118 cmd.ExecuteNonQuery();
119 }
120 }
121 catch
122 {
123 //No migrations table
124 //HACK create one and add data
125 cmd.CommandText = "create table migrations(name varchar(100), version int)";
126 cmd.ExecuteNonQuery();
83 127
84 } 128 cmd.CommandText = "insert into migrations(name, version) values('migrations', 1)";
129 cmd.ExecuteNonQuery();
85 130
131 cmd.CommandText = "insert into migrations(name, version) values('" + _migrationStore + "', 1)";
132 cmd.ExecuteNonQuery();
133 }
134 }
135 }
136
86 /// <summary> 137 /// <summary>
87 /// Saves a log item to the database 138 /// Saves a log item to the database
88 /// </summary> 139 /// </summary>
@@ -95,13 +146,28 @@ namespace OpenSim.Data.MSSQL
95 public void saveLog(string serverDaemon, string target, string methodCall, string arguments, int priority, 146 public void saveLog(string serverDaemon, string target, string methodCall, string arguments, int priority,
96 string logMessage) 147 string logMessage)
97 { 148 {
98 try 149 string sql = "INSERT INTO logs ([target], [server], [method], [arguments], [priority], [message]) VALUES ";
99 { 150 sql += "(@target, @server, @method, @arguments, @priority, @message);";
100 database.insertLogRow(serverDaemon, target, methodCall, arguments, priority, logMessage); 151
101 } 152 using (AutoClosingSqlCommand command = database.Query(sql))
102 catch
103 { 153 {
104 // it didn't log, don't worry about it 154 command.Parameters.Add(database.CreateParameter("server", serverDaemon));
155 command.Parameters.Add(database.CreateParameter("target",target));
156 command.Parameters.Add(database.CreateParameter("method", methodCall));
157 command.Parameters.Add(database.CreateParameter("arguments", arguments));
158 command.Parameters.Add(database.CreateParameter("priority", priority.ToString()));
159 command.Parameters.Add(database.CreateParameter("message", logMessage));
160
161 try
162 {
163 command.ExecuteNonQuery();
164 }
165 catch (Exception e)
166 {
167 //Are we not in a loop here
168 //m_log.Error(e.ToString());
169 Console.WriteLine("[LOG DB] Error logging : " + e.Message);
170 }
105 } 171 }
106 } 172 }
107 173
@@ -119,7 +185,7 @@ namespace OpenSim.Data.MSSQL
119 /// </summary> 185 /// </summary>
120 public void Dispose() 186 public void Dispose()
121 { 187 {
122 // Do nothing. 188 database = null;
123 } 189 }
124 190
125 /// <summary> 191 /// <summary>
diff --git a/OpenSim/Data/MSSQL/MSSQLManager.cs b/OpenSim/Data/MSSQL/MSSQLManager.cs
index abbcddf..b3e9b6e 100644
--- a/OpenSim/Data/MSSQL/MSSQLManager.cs
+++ b/OpenSim/Data/MSSQL/MSSQLManager.cs
@@ -33,7 +33,6 @@ using System.IO;
33using System.Reflection; 33using System.Reflection;
34using OpenMetaverse; 34using OpenMetaverse;
35using log4net; 35using log4net;
36using OpenSim.Framework;
37 36
38namespace OpenSim.Data.MSSQL 37namespace OpenSim.Data.MSSQL
39{ 38{
@@ -83,49 +82,7 @@ namespace OpenSim.Data.MSSQL
83 return conn; 82 return conn;
84 } 83 }
85 84
86 //private DataTable createRegionsTable() 85 #region Obsolete functions, can be removed!
87 //{
88 // DataTable regions = new DataTable("regions");
89
90 // createCol(regions, "regionHandle", typeof (ulong));
91 // createCol(regions, "regionName", typeof (String));
92 // createCol(regions, "uuid", typeof (String));
93
94 // createCol(regions, "regionRecvKey", typeof (String));
95 // createCol(regions, "regionSecret", typeof (String));
96 // createCol(regions, "regionSendKey", typeof (String));
97
98 // createCol(regions, "regionDataURI", typeof (String));
99 // createCol(regions, "serverIP", typeof (String));
100 // createCol(regions, "serverPort", typeof (String));
101 // createCol(regions, "serverURI", typeof (String));
102
103
104 // createCol(regions, "locX", typeof (uint));
105 // createCol(regions, "locY", typeof (uint));
106 // createCol(regions, "locZ", typeof (uint));
107
108 // createCol(regions, "eastOverrideHandle", typeof (ulong));
109 // createCol(regions, "westOverrideHandle", typeof (ulong));
110 // createCol(regions, "southOverrideHandle", typeof (ulong));
111 // createCol(regions, "northOverrideHandle", typeof (ulong));
112
113 // createCol(regions, "regionAssetURI", typeof (String));
114 // createCol(regions, "regionAssetRecvKey", typeof (String));
115 // createCol(regions, "regionAssetSendKey", typeof (String));
116
117 // createCol(regions, "regionUserURI", typeof (String));
118 // createCol(regions, "regionUserRecvKey", typeof (String));
119 // createCol(regions, "regionUserSendKey", typeof (String));
120
121 // createCol(regions, "regionMapTexture", typeof (String));
122 // createCol(regions, "serverHttpPort", typeof (String));
123 // createCol(regions, "serverRemotingPort", typeof (uint));
124
125 // // Add in contraints
126 // regions.PrimaryKey = new DataColumn[] {regions.Columns["UUID"]};
127 // return regions;
128 //}
129 86
130 /// <summary> 87 /// <summary>
131 /// 88 ///
@@ -133,6 +90,7 @@ namespace OpenSim.Data.MSSQL
133 /// <param name="dt"></param> 90 /// <param name="dt"></param>
134 /// <param name="name"></param> 91 /// <param name="name"></param>
135 /// <param name="type"></param> 92 /// <param name="type"></param>
93 [Obsolete("Do not use!")]
136 protected static void createCol(DataTable dt, string name, Type type) 94 protected static void createCol(DataTable dt, string name, Type type)
137 { 95 {
138 DataColumn col = new DataColumn(name, type); 96 DataColumn col = new DataColumn(name, type);
@@ -144,6 +102,7 @@ namespace OpenSim.Data.MSSQL
144 /// </summary> 102 /// </summary>
145 /// <param name="dt"></param> 103 /// <param name="dt"></param>
146 /// <returns></returns> 104 /// <returns></returns>
105 [Obsolete("Do not use!")]
147 protected static string defineTable(DataTable dt) 106 protected static string defineTable(DataTable dt)
148 { 107 {
149 string sql = "create table " + dt.TableName + "("; 108 string sql = "create table " + dt.TableName + "(";
@@ -167,34 +126,34 @@ namespace OpenSim.Data.MSSQL
167 return sql; 126 return sql;
168 } 127 }
169 128
129 #endregion
130
170 /// <summary> 131 /// <summary>
171 /// Type conversion function 132 /// Type conversion function
172 /// </summary> 133 /// </summary>
173 /// <param name="type">a type</param> 134 /// <param name="type">a type</param>
174 /// <returns>a sqltype</returns> 135 /// <returns>a sqltype</returns>
175 /// <remarks>this is something we'll need to implement for each db slightly differently.</remarks> 136 /// <remarks>this is something we'll need to implement for each db slightly differently.</remarks>
137 [Obsolete("Used by a obsolete methods")]
176 public static string SqlType(Type type) 138 public static string SqlType(Type type)
177 { 139 {
178 if (type == typeof(String)) 140 if (type == typeof(String))
179 { 141 {
180 return "varchar(255)"; 142 return "varchar(255)";
181 } 143 }
182 else if (type == typeof(Int32)) 144 if (type == typeof(Int32))
183 { 145 {
184 return "integer"; 146 return "integer";
185 } 147 }
186 else if (type == typeof(Double)) 148 if (type == typeof(Double))
187 { 149 {
188 return "float"; 150 return "float";
189 } 151 }
190 else if (type == typeof(Byte[])) 152 if (type == typeof(Byte[]))
191 { 153 {
192 return "image"; 154 return "image";
193 } 155 }
194 else 156 return "varchar(255)";
195 {
196 return "varchar(255)";
197 }
198 } 157 }
199 158
200 /// <summary> 159 /// <summary>
@@ -224,14 +183,22 @@ namespace OpenSim.Data.MSSQL
224 { 183 {
225 return SqlDbType.VarChar; 184 return SqlDbType.VarChar;
226 } 185 }
186 if (type == typeof(sbyte))
187 {
188 return SqlDbType.TinyInt;
189 }
227 if (type == typeof(Byte[])) 190 if (type == typeof(Byte[]))
228 { 191 {
229 return SqlDbType.Image; 192 return SqlDbType.Image;
230 } 193 }
231 if (type == typeof(uint)) 194 if (type == typeof(uint) || type == typeof(ushort))
232 { 195 {
233 return SqlDbType.Int; 196 return SqlDbType.Int;
234 } 197 }
198 if (type == typeof(ulong))
199 {
200 return SqlDbType.BigInt;
201 }
235 return SqlDbType.VarChar; 202 return SqlDbType.VarChar;
236 } 203 }
237 204
@@ -282,6 +249,9 @@ namespace OpenSim.Data.MSSQL
282 //Tweak so we dont always have to add @ sign 249 //Tweak so we dont always have to add @ sign
283 if (!parameterName.StartsWith("@")) parameterName = "@" + parameterName; 250 if (!parameterName.StartsWith("@")) parameterName = "@" + parameterName;
284 251
252 //HACK if object is null, it is turned into a string, there are no nullable type till now
253 if (parameterObject == null) parameterObject = "";
254
285 SqlParameter parameter = new SqlParameter(parameterName, DbtypeFromType(parameterObject.GetType())); 255 SqlParameter parameter = new SqlParameter(parameterName, DbtypeFromType(parameterObject.GetType()));
286 256
287 if (parameterOut) 257 if (parameterOut)
@@ -298,6 +268,7 @@ namespace OpenSim.Data.MSSQL
298 } 268 }
299 269
300 private static readonly Dictionary<string, string> emptyDictionary = new Dictionary<string, string>(); 270 private static readonly Dictionary<string, string> emptyDictionary = new Dictionary<string, string>();
271
301 internal AutoClosingSqlCommand Query(string sql) 272 internal AutoClosingSqlCommand Query(string sql)
302 { 273 {
303 return Query(sql, emptyDictionary); 274 return Query(sql, emptyDictionary);
@@ -322,238 +293,38 @@ namespace OpenSim.Data.MSSQL
322 } 293 }
323 294
324 /// <summary> 295 /// <summary>
325 /// Runs a database reader object and returns a region row 296 /// Runs a query with protection against SQL Injection by using parameterised input.
326 /// </summary>
327 /// <param name="reader">An active database reader</param>
328 /// <returns>A region row</returns>
329 public RegionProfileData getRegionRow(IDataReader reader)
330 {
331 RegionProfileData regionprofile = new RegionProfileData();
332
333 if (reader.Read())
334 {
335 // Region Main
336 regionprofile.regionHandle = Convert.ToUInt64(reader["regionHandle"]);
337 regionprofile.regionName = (string)reader["regionName"];
338 regionprofile.UUID = new UUID((string)reader["uuid"]);
339
340 // Secrets
341 regionprofile.regionRecvKey = (string)reader["regionRecvKey"];
342 regionprofile.regionSecret = (string)reader["regionSecret"];
343 regionprofile.regionSendKey = (string)reader["regionSendKey"];
344
345 // Region Server
346 regionprofile.regionDataURI = (string)reader["regionDataURI"];
347 regionprofile.regionOnline = false; // Needs to be pinged before this can be set.
348 regionprofile.serverIP = (string)reader["serverIP"];
349 regionprofile.serverPort = Convert.ToUInt32(reader["serverPort"]);
350 regionprofile.serverURI = (string)reader["serverURI"];
351 regionprofile.httpPort = Convert.ToUInt32(reader["serverHttpPort"]);
352 regionprofile.remotingPort = Convert.ToUInt32(reader["serverRemotingPort"]);
353
354
355 // Location
356 regionprofile.regionLocX = Convert.ToUInt32(reader["locX"]);
357 regionprofile.regionLocY = Convert.ToUInt32(reader["locY"]);
358 regionprofile.regionLocZ = Convert.ToUInt32(reader["locZ"]);
359
360 // Neighbours - 0 = No Override
361 regionprofile.regionEastOverrideHandle = Convert.ToUInt64(reader["eastOverrideHandle"]);
362 regionprofile.regionWestOverrideHandle = Convert.ToUInt64(reader["westOverrideHandle"]);
363 regionprofile.regionSouthOverrideHandle = Convert.ToUInt64(reader["southOverrideHandle"]);
364 regionprofile.regionNorthOverrideHandle = Convert.ToUInt64(reader["northOverrideHandle"]);
365
366 // Assets
367 regionprofile.regionAssetURI = (string)reader["regionAssetURI"];
368 regionprofile.regionAssetRecvKey = (string)reader["regionAssetRecvKey"];
369 regionprofile.regionAssetSendKey = (string)reader["regionAssetSendKey"];
370
371 // Userserver
372 regionprofile.regionUserURI = (string)reader["regionUserURI"];
373 regionprofile.regionUserRecvKey = (string)reader["regionUserRecvKey"];
374 regionprofile.regionUserSendKey = (string)reader["regionUserSendKey"];
375 regionprofile.owner_uuid = new UUID((string) reader["owner_uuid"]);
376 // World Map Addition
377 string tempRegionMap = reader["regionMapTexture"].ToString();
378 if (tempRegionMap != String.Empty)
379 {
380 regionprofile.regionMapTextureID = new UUID(tempRegionMap);
381 }
382 else
383 {
384 regionprofile.regionMapTextureID = new UUID();
385 }
386 }
387 else
388 {
389 reader.Close();
390 throw new Exception("No rows to return");
391 }
392 return regionprofile;
393 }
394
395 /// <summary>
396 /// Reads a user profile from an active data reader
397 /// </summary> 297 /// </summary>
398 /// <param name="reader">An active database reader</param> 298 /// <param name="sql">The SQL string - replace any variables such as WHERE x = "y" with WHERE x = @y</param>
399 /// <returns>A user profile</returns> 299 /// <param name="sqlParameter">A parameter - use createparameter to create parameter</param>
400 public UserProfileData readUserRow(IDataReader reader) 300 /// <returns></returns>
301 internal AutoClosingSqlCommand Query(string sql, SqlParameter sqlParameter)
401 { 302 {
402 UserProfileData retval = new UserProfileData(); 303 SqlCommand dbcommand = DatabaseConnection().CreateCommand();
304 dbcommand.CommandText = sql;
305 dbcommand.Parameters.Add(sqlParameter);
403 306
404 if (reader.Read()) 307 return new AutoClosingSqlCommand(dbcommand);
405 {
406 retval.ID = new UUID((string)reader["UUID"]);
407 retval.FirstName = (string)reader["username"];
408 retval.SurName = (string)reader["lastname"];
409
410 retval.PasswordHash = (string)reader["passwordHash"];
411 retval.PasswordSalt = (string)reader["passwordSalt"];
412
413 retval.HomeRegion = Convert.ToUInt64(reader["homeRegion"].ToString());
414 retval.HomeLocation = new Vector3(
415 Convert.ToSingle(reader["homeLocationX"].ToString()),
416 Convert.ToSingle(reader["homeLocationY"].ToString()),
417 Convert.ToSingle(reader["homeLocationZ"].ToString()));
418 retval.HomeLookAt = new Vector3(
419 Convert.ToSingle(reader["homeLookAtX"].ToString()),
420 Convert.ToSingle(reader["homeLookAtY"].ToString()),
421 Convert.ToSingle(reader["homeLookAtZ"].ToString()));
422
423 retval.Created = Convert.ToInt32(reader["created"].ToString());
424 retval.LastLogin = Convert.ToInt32(reader["lastLogin"].ToString());
425
426 retval.UserInventoryURI = (string)reader["userInventoryURI"];
427 retval.UserAssetURI = (string)reader["userAssetURI"];
428
429 retval.CanDoMask = Convert.ToUInt32(reader["profileCanDoMask"].ToString());
430 retval.WantDoMask = Convert.ToUInt32(reader["profileWantDoMask"].ToString());
431
432 retval.AboutText = (string)reader["profileAboutText"];
433 retval.FirstLifeAboutText = (string)reader["profileFirstText"];
434
435 retval.Image = new UUID((string)reader["profileImage"]);
436 retval.FirstLifeImage = new UUID((string)reader["profileFirstImage"]);
437 retval.WebLoginKey = new UUID((string)reader["webLoginKey"]);
438 }
439 else
440 {
441 return null;
442 }
443 return retval;
444 } 308 }
445 309
446 /// <summary> 310 /// <summary>
447 /// Reads an agent row from a database reader 311 /// Checks if we need to do some migrations to the database
448 /// </summary> 312 /// </summary>
449 /// <param name="reader">An active database reader</param> 313 /// <param name="migrationStore">migrationStore.</param>
450 /// <returns>A user session agent</returns> 314 public void CheckMigration(string migrationStore)
451 public UserAgentData readAgentRow(IDataReader reader)
452 { 315 {
453 UserAgentData retval = new UserAgentData(); 316 using (SqlConnection connection = DatabaseConnection())
454
455 if (reader.Read())
456 { 317 {
457 // Agent IDs 318 Assembly assem = GetType().Assembly;
458 retval.ProfileID = new UUID((string)reader["UUID"]); 319 MSSQLMigration migration = new MSSQLMigration(connection, assem, migrationStore);
459 retval.SessionID = new UUID((string)reader["sessionID"]);
460 retval.SecureSessionID = new UUID((string)reader["secureSessionID"]);
461
462 // Agent Who?
463 retval.AgentIP = (string)reader["agentIP"];
464 retval.AgentPort = Convert.ToUInt32(reader["agentPort"].ToString());
465 retval.AgentOnline = Convert.ToInt32(reader["agentOnline"].ToString()) != 0;
466
467 // Login/Logout times (UNIX Epoch)
468 retval.LoginTime = Convert.ToInt32(reader["loginTime"].ToString());
469 retval.LogoutTime = Convert.ToInt32(reader["logoutTime"].ToString());
470
471 // Current position
472 retval.Region = (string)reader["currentRegion"];
473 retval.Handle = Convert.ToUInt64(reader["currentHandle"].ToString());
474 Vector3 tmp_v;
475 Vector3.TryParse((string)reader["currentPos"], out tmp_v);
476 retval.Position = tmp_v;
477 320
478 } 321 migration.Update();
479 else
480 {
481 return null;
482 }
483 return retval;
484 }
485 322
486 /// <summary> 323 connection.Close();
487 ///
488 /// </summary>
489 /// <param name="reader"></param>
490 /// <returns></returns>
491 public AssetBase getAssetRow(IDataReader reader)
492 {
493 AssetBase asset = new AssetBase();
494 if (reader.Read())
495 {
496 // Region Main
497 asset = new AssetBase();
498 asset.Data = (byte[])reader["data"];
499 asset.Description = (string)reader["description"];
500 asset.FullID = new UUID((string)reader["id"]);
501 asset.Local = Convert.ToBoolean(reader["local"]); // ((sbyte)reader["local"]) != 0 ? true : false;
502 asset.Name = (string)reader["name"];
503 asset.Type = Convert.ToSByte(reader["assetType"]);
504 } 324 }
505 else
506 {
507 return null; // throw new Exception("No rows to return");
508 }
509 return asset;
510 } 325 }
511 326
512 327 #region Old Testtable functions
513 /// <summary>
514 /// Inserts a new row into the log database
515 /// </summary>
516 /// <param name="serverDaemon">The daemon which triggered this event</param>
517 /// <param name="target">Who were we operating on when this occured (region UUID, user UUID, etc)</param>
518 /// <param name="methodCall">The method call where the problem occured</param>
519 /// <param name="arguments">The arguments passed to the method</param>
520 /// <param name="priority">How critical is this?</param>
521 /// <param name="logMessage">Extra message info</param>
522 /// <returns>Saved successfully?</returns>
523 public bool insertLogRow(string serverDaemon, string target, string methodCall, string arguments, int priority,
524 string logMessage)
525 {
526 string sql = "INSERT INTO logs ([target], [server], [method], [arguments], [priority], [message]) VALUES ";
527 sql += "(@target, @server, @method, @arguments, @priority, @message);";
528
529 Dictionary<string, string> parameters = new Dictionary<string, string>();
530 parameters["server"] = serverDaemon;
531 parameters["target"] = target;
532 parameters["method"] = methodCall;
533 parameters["arguments"] = arguments;
534 parameters["priority"] = priority.ToString();
535 parameters["message"] = logMessage;
536
537 bool returnval = false;
538
539 using (IDbCommand result = Query(sql, parameters))
540 {
541 try
542 {
543
544 if (result.ExecuteNonQuery() == 1)
545 returnval = true;
546
547 }
548 catch (Exception e)
549 {
550 m_log.Error(e.ToString());
551 return false;
552 }
553 }
554
555 return returnval;
556 }
557 328
558 /// <summary> 329 /// <summary>
559 /// Execute a SQL statement stored in a resource, as a string 330 /// Execute a SQL statement stored in a resource, as a string
@@ -561,13 +332,12 @@ namespace OpenSim.Data.MSSQL
561 /// <param name="name">the ressource string</param> 332 /// <param name="name">the ressource string</param>
562 public void ExecuteResourceSql(string name) 333 public void ExecuteResourceSql(string name)
563 { 334 {
564 using (IDbCommand cmd = Query(getResourceString(name), new Dictionary<string,string>())) 335 using (IDbCommand cmd = Query(getResourceString(name), new Dictionary<string, string>()))
565 { 336 {
566 cmd.ExecuteNonQuery(); 337 cmd.ExecuteNonQuery();
567 } 338 }
568 } 339 }
569 340
570
571 /// <summary> 341 /// <summary>
572 /// Given a list of tables, return the version of the tables, as seen in the database 342 /// Given a list of tables, return the version of the tables, as seen in the database
573 /// </summary> 343 /// </summary>
@@ -622,6 +392,8 @@ namespace OpenSim.Data.MSSQL
622 throw new Exception(string.Format("Resource '{0}' was not found", name)); 392 throw new Exception(string.Format("Resource '{0}' was not found", name));
623 } 393 }
624 394
395 #endregion
396
625 /// <summary> 397 /// <summary>
626 /// Returns the version of this DB provider 398 /// Returns the version of this DB provider
627 /// </summary> 399 /// </summary>
@@ -636,63 +408,5 @@ namespace OpenSim.Data.MSSQL
636 string.Format("{0}.{1}.{2}.{3}", dllVersion.Major, dllVersion.Minor, dllVersion.Build, 408 string.Format("{0}.{1}.{2}.{3}", dllVersion.Major, dllVersion.Minor, dllVersion.Build,
637 dllVersion.Revision); 409 dllVersion.Revision);
638 } 410 }
639
640 public bool insertAgentRow(UserAgentData agentdata)
641 {
642 string sql = @"
643
644IF EXISTS (SELECT * FROM agents WHERE UUID = @UUID)
645 BEGIN
646 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
647 WHERE UUID = @UUID
648 END
649ELSE
650 BEGIN
651 INSERT INTO
652 agents (UUID, sessionID, secureSessionID, agentIP, agentPort, agentOnline, loginTime, logoutTime, currentRegion, currentHandle, currentPos) VALUES
653 (@UUID, @sessionID, @secureSessionID, @agentIP, @agentPort, @agentOnline, @loginTime, @logoutTime, @currentRegion, @currentHandle, @currentPos)
654 END
655";
656
657 Dictionary<string, string> parameters = new Dictionary<string, string>();
658
659 parameters["@UUID"] = agentdata.ProfileID.ToString();
660 parameters["@sessionID"] = agentdata.SessionID.ToString();
661 parameters["@secureSessionID"] = agentdata.SecureSessionID.ToString();
662 parameters["@agentIP"] = agentdata.AgentIP.ToString();
663 parameters["@agentPort"] = agentdata.AgentPort.ToString();
664 parameters["@agentOnline"] = (agentdata.AgentOnline == true) ? "1" : "0";
665 parameters["@loginTime"] = agentdata.LoginTime.ToString();
666 parameters["@logoutTime"] = agentdata.LogoutTime.ToString();
667 parameters["@currentRegion"] = agentdata.Region.ToString();
668 parameters["@currentHandle"] = agentdata.Handle.ToString();
669 parameters["@currentPos"] = "<" + ((int)agentdata.Position.X).ToString() + "," + ((int)agentdata.Position.Y).ToString() + "," + ((int)agentdata.Position.Z).ToString() + ">";
670
671
672 using (IDbCommand result = Query(sql, parameters))
673 {
674 result.Transaction = result.Connection.BeginTransaction(IsolationLevel.Serializable);
675 try
676 {
677 if (result.ExecuteNonQuery() > 0)
678 {
679 result.Transaction.Commit();
680 return true;
681 }
682 else
683 {
684 result.Transaction.Rollback();
685 return false;
686 }
687 }
688 catch (Exception e)
689 {
690 result.Transaction.Rollback();
691 m_log.Error(e.ToString());
692 return false;
693 }
694 }
695
696 }
697 } 411 }
698} 412}
diff --git a/OpenSim/Data/MSSQL/MSSQLRegionData.cs b/OpenSim/Data/MSSQL/MSSQLRegionData.cs
index b942f00..8b808d3 100644
--- a/OpenSim/Data/MSSQL/MSSQLRegionData.cs
+++ b/OpenSim/Data/MSSQL/MSSQLRegionData.cs
@@ -44,14 +44,19 @@ namespace OpenSim.Data.MSSQL
44 /// </summary> 44 /// </summary>
45 public class MSSQLRegionDataStore : IRegionDataStore 45 public class MSSQLRegionDataStore : IRegionDataStore
46 { 46 {
47 private const string _migrationStore = "RegionStore";
48
47 // private static FileSystemDataStore Instance = new FileSystemDataStore(); 49 // private static FileSystemDataStore Instance = new FileSystemDataStore();
48 private static readonly ILog _Log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); 50 private static readonly ILog _Log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
49 51
52 /// <summary>
53 /// The database manager
54 /// </summary>
50 private MSSQLManager _Database; 55 private MSSQLManager _Database;
51 56
52// private const string _PrimSelect = "SELECT * FROM PRIMS WHERE RegionUUID = @RegionUUID AND (SceneGroupID LIKE @SceneGroupID OR UUID = @UUID)"; 57 /// <summary>
53// private const string _ShapeSelect = "SELECT * FROM PRIMSHAPES WHERE UUID in (SELECT UUID FROM PRIMS WHERE RegionUUID = @RegionUUID AND (SceneGroupID LIKE @SceneGroupID OR UUID = @UUID))"; 58 /// Const for the prim store..
54// private const string _ItemsSelect = "SELECT * FROM PRIMITEMS WHERE primID in (SELECT UUID FROM PRIMS WHERE RegionUUID = @RegionUUID AND (SceneGroupID LIKE @SceneGroupID OR UUID = @UUID))"; 59 /// </summary>
55 private const string _PrimSelect = "SELECT * FROM PRIMS WHERE RegionUUID = @RegionUUID AND (SceneGroupID LIKE @SceneGroupID OR UUID IN (@UUID))"; 60 private const string _PrimSelect = "SELECT * FROM PRIMS WHERE RegionUUID = @RegionUUID AND (SceneGroupID LIKE @SceneGroupID OR UUID IN (@UUID))";
56 private const string _ShapeSelect = "SELECT * FROM PRIMSHAPES WHERE UUID in (SELECT UUID FROM PRIMS WHERE RegionUUID = @RegionUUID AND (SceneGroupID LIKE @SceneGroupID OR UUID IN (@UUID)))"; 61 private const string _ShapeSelect = "SELECT * FROM PRIMSHAPES WHERE UUID in (SELECT UUID FROM PRIMS WHERE RegionUUID = @RegionUUID AND (SceneGroupID LIKE @SceneGroupID OR UUID IN (@UUID)))";
57 private const string _ItemsSelect = "SELECT * FROM PRIMITEMS WHERE primID in (SELECT UUID FROM PRIMS WHERE RegionUUID = @RegionUUID AND (SceneGroupID LIKE @SceneGroupID OR UUID IN (@UUID)))"; 62 private const string _ItemsSelect = "SELECT * FROM PRIMITEMS WHERE primID in (SELECT UUID FROM PRIMS WHERE RegionUUID = @RegionUUID AND (SceneGroupID LIKE @SceneGroupID OR UUID IN (@UUID)))";
@@ -81,31 +86,14 @@ namespace OpenSim.Data.MSSQL
81 string settingUserId = iniFile.ParseFileReadValue("user_id"); 86 string settingUserId = iniFile.ParseFileReadValue("user_id");
82 string settingPassword = iniFile.ParseFileReadValue("password"); 87 string settingPassword = iniFile.ParseFileReadValue("password");
83 88
84 _Database = 89 _Database = new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, settingPassword);
85 new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId,
86 settingPassword);
87
88
89 SqlConnectionStringBuilder conBuilder = new SqlConnectionStringBuilder();
90 conBuilder.DataSource = settingDataSource;
91 conBuilder.InitialCatalog = settingInitialCatalog;
92 conBuilder.PersistSecurityInfo = Convert.ToBoolean(settingPersistSecurityInfo);
93 conBuilder.UserID = settingUserId;
94 conBuilder.Password = settingPassword;
95 conBuilder.ApplicationName = Assembly.GetEntryAssembly().Location;
96
97 connectionString = conBuilder.ToString();
98 } 90 }
99 91
100 //Migration settings 92 //Migration settings
101 Assembly assem = GetType().Assembly; 93 _Database.CheckMigration(_migrationStore);
102 94
103 using (SqlConnection connection = _Database.DatabaseConnection()) 95 using (SqlConnection connection = _Database.DatabaseConnection())
104 { 96 {
105 MSSQLMigration m = new MSSQLMigration(connection, assem, "RegionStore");
106
107 m.Update();
108
109 //Create Dataset. Not filled!!! 97 //Create Dataset. Not filled!!!
110 _PrimsDataSet = new DataSet("primsdata"); 98 _PrimsDataSet = new DataSet("primsdata");
111 99
@@ -243,7 +231,7 @@ namespace OpenSim.Data.MSSQL
243 catch (Exception e) 231 catch (Exception e)
244 { 232 {
245 _Log.Error("[REGION DB]: Failed create prim object, exception and data follows"); 233 _Log.Error("[REGION DB]: Failed create prim object, exception and data follows");
246 _Log.Info("[REGION DB]: " + e.ToString()); 234 _Log.Info("[REGION DB]: " + e.Message);
247 foreach (DataColumn col in prims.Columns) 235 foreach (DataColumn col in prims.Columns)
248 { 236 {
249 _Log.Info("[REGION DB]: Col: " + col.ColumnName + " => " + primRow[col]); 237 _Log.Info("[REGION DB]: Col: " + col.ColumnName + " => " + primRow[col]);
@@ -259,71 +247,76 @@ namespace OpenSim.Data.MSSQL
259 247
260 #region Experimental 248 #region Experimental
261 249
262// 250 //
263// //Get all prims 251 // //Get all prims
264// string sql = "select * from prims where RegionUUID = @RegionUUID"; 252 // string sql = "select * from prims where RegionUUID = @RegionUUID";
265// 253 //
266// using (AutoClosingSqlCommand cmdPrims = _Database.Query(sql)) 254 // using (AutoClosingSqlCommand cmdPrims = _Database.Query(sql))
267// { 255 // {
268// cmdPrims.Parameters.AddWithValue("@RegionUUID", regionUUID.ToString()); 256 // cmdPrims.Parameters.AddWithValue("@RegionUUID", regionUUID.ToString());
269// using (SqlDataReader readerPrims = cmdPrims.ExecuteReader()) 257 // using (SqlDataReader readerPrims = cmdPrims.ExecuteReader())
270// { 258 // {
271// while (readerPrims.Read()) 259 // while (readerPrims.Read())
272// { 260 // {
273// string uuid = (string)readerPrims["UUID"]; 261 // string uuid = (string)readerPrims["UUID"];
274// string objID = (string)readerPrims["SceneGroupID"]; 262 // string objID = (string)readerPrims["SceneGroupID"];
275// SceneObjectPart prim = buildPrim(readerPrims); 263 // SceneObjectPart prim = buildPrim(readerPrims);
276// 264 //
277// //Setting default shape, will change shape ltr 265 // //Setting default shape, will change shape ltr
278// prim.Shape = PrimitiveBaseShape.Default; 266 // prim.Shape = PrimitiveBaseShape.Default;
279// 267 //
280// //Load inventory items of prim 268 // //Load inventory items of prim
281// //LoadItems(prim); 269 // //LoadItems(prim);
282// 270 //
283// if (uuid == objID) 271 // if (uuid == objID)
284// { 272 // {
285// SceneObjectGroup group = new SceneObjectGroup(); 273 // SceneObjectGroup group = new SceneObjectGroup();
286// 274 //
287// group.AddPart(prim); 275 // group.AddPart(prim);
288// group.RootPart = prim; 276 // group.RootPart = prim;
289// 277 //
290// createdObjects.Add(group.UUID, group); 278 // createdObjects.Add(group.UUID, group);
291// retvals.Add(group); 279 // retvals.Add(group);
292// } 280 // }
293// else 281 // else
294// { 282 // {
295// createdObjects[new UUID(objID)].AddPart(prim); 283 // createdObjects[new UUID(objID)].AddPart(prim);
296// } 284 // }
297// } 285 // }
298// } 286 // }
299// } 287 // }
300// m_log.Info("[REGION DB]: Loaded " + retvals.Count + " prim objects for region: " + regionUUID); 288 // m_log.Info("[REGION DB]: Loaded " + retvals.Count + " prim objects for region: " + regionUUID);
301// 289 //
302// //Find all shapes related with prims 290 // //Find all shapes related with prims
303// sql = "select * from primshapes"; 291 // sql = "select * from primshapes";
304// using (AutoClosingSqlCommand cmdShapes = _Database.Query(sql)) 292 // using (AutoClosingSqlCommand cmdShapes = _Database.Query(sql))
305// { 293 // {
306// using (SqlDataReader readerShapes = cmdShapes.ExecuteReader()) 294 // using (SqlDataReader readerShapes = cmdShapes.ExecuteReader())
307// { 295 // {
308// while (readerShapes.Read()) 296 // while (readerShapes.Read())
309// { 297 // {
310// UUID UUID = new UUID((string) readerShapes["UUID"]); 298 // UUID UUID = new UUID((string) readerShapes["UUID"]);
311// 299 //
312// foreach (SceneObjectGroup objectGroup in createdObjects.Values) 300 // foreach (SceneObjectGroup objectGroup in createdObjects.Values)
313// { 301 // {
314// if (objectGroup.Children.ContainsKey(UUID)) 302 // if (objectGroup.Children.ContainsKey(UUID))
315// { 303 // {
316// objectGroup.Children[UUID].Shape = buildShape(readerShapes); 304 // objectGroup.Children[UUID].Shape = buildShape(readerShapes);
317// } 305 // }
318// } 306 // }
319// } 307 // }
320// } 308 // }
321// } 309 // }
322// return retvals; 310 // return retvals;
323 311
324 #endregion 312 #endregion
325 } 313 }
326 314
315 /// <summary>
316 /// Stores all object's details apart from inventory
317 /// </summary>
318 /// <param name="obj"></param>
319 /// <param name="regionUUID"></param>
327 public void StoreObject(SceneObjectGroup obj, UUID regionUUID) 320 public void StoreObject(SceneObjectGroup obj, UUID regionUUID)
328 { 321 {
329 //Retrieve all values of current region, and current scene/or prims 322 //Retrieve all values of current region, and current scene/or prims
@@ -375,10 +368,6 @@ namespace OpenSim.Data.MSSQL
375 } 368 }
376 } 369 }
377 } 370 }
378 else
379 {
380 // m_log.Info("[DATASTORE]: Ignoring Physical obj: " + obj.UUID + " in region: " + regionUUID);
381 }
382 } 371 }
383 372
384 //Save changes 373 //Save changes
@@ -430,7 +419,7 @@ namespace OpenSim.Data.MSSQL
430 //TODO add index on PrimID in DB, if not already exist 419 //TODO add index on PrimID in DB, if not already exist
431 using (AutoClosingSqlCommand cmd = _Database.Query("DELETE PRIMITEMS WHERE primID = @primID")) 420 using (AutoClosingSqlCommand cmd = _Database.Query("DELETE PRIMITEMS WHERE primID = @primID"))
432 { 421 {
433 cmd.Parameters.AddWithValue("@primID", primID.ToString()); 422 cmd.Parameters.Add(_Database.CreateParameter("@primID", primID));
434 cmd.ExecuteNonQuery(); 423 cmd.ExecuteNonQuery();
435 } 424 }
436 425
@@ -470,7 +459,7 @@ namespace OpenSim.Data.MSSQL
470 459
471 using (SqlDataReader reader = cmd.ExecuteReader()) 460 using (SqlDataReader reader = cmd.ExecuteReader())
472 { 461 {
473 int rev = 0; 462 int rev;
474 if (reader.Read()) 463 if (reader.Read())
475 { 464 {
476 MemoryStream str = new MemoryStream((byte[])reader["Heightfield"]); 465 MemoryStream str = new MemoryStream((byte[])reader["Heightfield"]);
@@ -509,22 +498,21 @@ namespace OpenSim.Data.MSSQL
509 string sql = "delete from terrain where RegionUUID=@RegionUUID"; 498 string sql = "delete from terrain where RegionUUID=@RegionUUID";
510 using (AutoClosingSqlCommand cmd = _Database.Query(sql)) 499 using (AutoClosingSqlCommand cmd = _Database.Query(sql))
511 { 500 {
512 cmd.Parameters.AddWithValue("@RegionUUID", regionID.ToString()); 501 cmd.Parameters.Add(_Database.CreateParameter("@RegionUUID", regionID));
513 cmd.ExecuteNonQuery(); 502 cmd.ExecuteNonQuery();
514 } 503 }
515 504
516 sql = "insert into terrain(RegionUUID, Revision, Heightfield)" + 505 sql = "insert into terrain(RegionUUID, Revision, Heightfield) values(@RegionUUID, @Revision, @Heightfield)";
517 " values(@RegionUUID, @Revision, @Heightfield)";
518 506
519 using (AutoClosingSqlCommand cmd = _Database.Query(sql)) 507 using (AutoClosingSqlCommand cmd = _Database.Query(sql))
520 { 508 {
521 cmd.Parameters.AddWithValue("@RegionUUID", regionID.ToString()); 509 cmd.Parameters.Add(_Database.CreateParameter("@RegionUUID", regionID));
522 cmd.Parameters.AddWithValue("@Revision", revision); 510 cmd.Parameters.Add(_Database.CreateParameter("@Revision", revision));
523 cmd.Parameters.AddWithValue("@Heightfield", serializeTerrain(terrain)); 511 cmd.Parameters.Add(_Database.CreateParameter("@Heightfield", serializeTerrain(terrain)));
524 cmd.ExecuteNonQuery(); 512 cmd.ExecuteNonQuery();
525 } 513 }
526 514
527 _Log.Info("[REGION DB]: Stored terrain revision r" + revision); 515 _Log.Info("[REGION DB]: Stored terrain revision r " + revision);
528 } 516 }
529 517
530 /// <summary> 518 /// <summary>
@@ -541,12 +529,13 @@ namespace OpenSim.Data.MSSQL
541 //Retrieve all land data from region 529 //Retrieve all land data from region
542 using (AutoClosingSqlCommand cmdLandData = _Database.Query(sql)) 530 using (AutoClosingSqlCommand cmdLandData = _Database.Query(sql))
543 { 531 {
544 cmdLandData.Parameters.AddWithValue("@RegionUUID", regionUUID.ToString()); 532 cmdLandData.Parameters.Add(_Database.CreateParameter("@RegionUUID", regionUUID));
545 533
546 using (SqlDataReader readerLandData = cmdLandData.ExecuteReader()) 534 using (SqlDataReader readerLandData = cmdLandData.ExecuteReader())
547 { 535 {
548 while (readerLandData.Read()) 536 while (readerLandData.Read())
549 { 537 {
538 // LandData data = buildLandData(readerLandData);
550 landDataForRegion.Add(buildLandData(readerLandData)); 539 landDataForRegion.Add(buildLandData(readerLandData));
551 } 540 }
552 } 541 }
@@ -558,7 +547,7 @@ namespace OpenSim.Data.MSSQL
558 sql = "select * from landaccesslist where LandUUID = @LandUUID"; 547 sql = "select * from landaccesslist where LandUUID = @LandUUID";
559 using (AutoClosingSqlCommand cmdAccessList = _Database.Query(sql)) 548 using (AutoClosingSqlCommand cmdAccessList = _Database.Query(sql))
560 { 549 {
561 cmdAccessList.Parameters.AddWithValue("@LandUUID", landData.GlobalID.ToString()); 550 cmdAccessList.Parameters.Add(_Database.CreateParameter("@LandUUID", landData.GlobalID));
562 using (SqlDataReader readerAccessList = cmdAccessList.ExecuteReader()) 551 using (SqlDataReader readerAccessList = cmdAccessList.ExecuteReader())
563 { 552 {
564 while (readerAccessList.Read()) 553 while (readerAccessList.Read())
@@ -643,7 +632,7 @@ VALUES
643 RegionSettings regionSettings; 632 RegionSettings regionSettings;
644 using (AutoClosingSqlCommand cmd = _Database.Query(sql)) 633 using (AutoClosingSqlCommand cmd = _Database.Query(sql))
645 { 634 {
646 cmd.Parameters.Add(_Database.CreateParameter("@regionUUID", regionUUID.ToString())); 635 cmd.Parameters.Add(_Database.CreateParameter("@regionUUID", regionUUID));
647 using (SqlDataReader reader = cmd.ExecuteReader()) 636 using (SqlDataReader reader = cmd.ExecuteReader())
648 { 637 {
649 if (reader.Read()) 638 if (reader.Read())
@@ -674,7 +663,7 @@ VALUES
674 public void StoreRegionSettings(RegionSettings regionSettings) 663 public void StoreRegionSettings(RegionSettings regionSettings)
675 { 664 {
676 //Little check if regionUUID already exist in DB 665 //Little check if regionUUID already exist in DB
677 string regionUUID = null; 666 string regionUUID;
678 using (AutoClosingSqlCommand cmd = _Database.Query("SELECT regionUUID FROM regionsettings WHERE regionUUID = @regionUUID")) 667 using (AutoClosingSqlCommand cmd = _Database.Query("SELECT regionUUID FROM regionsettings WHERE regionUUID = @regionUUID"))
679 { 668 {
680 cmd.Parameters.Add(_Database.CreateParameter("@regionUUID", regionSettings.RegionUUID)); 669 cmd.Parameters.Add(_Database.CreateParameter("@regionUUID", regionSettings.RegionUUID));
@@ -724,7 +713,7 @@ VALUES
724 { 713 {
725 DataTable dbItems = _PrimsDataSet.Tables["primitems"]; 714 DataTable dbItems = _PrimsDataSet.Tables["primitems"];
726 715
727 String sql = String.Format("primID = '{0}'", prim.UUID.ToString()); 716 String sql = String.Format("primID = '{0}'", prim.UUID);
728 DataRow[] dbItemRows = dbItems.Select(sql); 717 DataRow[] dbItemRows = dbItems.Select(sql);
729 718
730 IList<TaskInventoryItem> inventory = new List<TaskInventoryItem>(); 719 IList<TaskInventoryItem> inventory = new List<TaskInventoryItem>();
@@ -1446,6 +1435,12 @@ VALUES
1446 1435
1447 #endregion 1436 #endregion
1448 1437
1438 /// <summary>
1439 /// Retrieves the prims data for region.
1440 /// </summary>
1441 /// <param name="regionUUID">The region UUID.</param>
1442 /// <param name="sceneGroupID">The scene group ID.</param>
1443 /// <param name="primID">The prim ID.</param>
1449 private void RetrievePrimsDataForRegion(UUID regionUUID, UUID sceneGroupID, string primID) 1444 private void RetrievePrimsDataForRegion(UUID regionUUID, UUID sceneGroupID, string primID)
1450 { 1445 {
1451 using (SqlConnection connection = _Database.DatabaseConnection()) 1446 using (SqlConnection connection = _Database.DatabaseConnection())
@@ -1482,6 +1477,9 @@ VALUES
1482 } 1477 }
1483 } 1478 }
1484 1479
1480 /// <summary>
1481 /// Commits the dataset.
1482 /// </summary>
1485 private void CommitDataSet() 1483 private void CommitDataSet()
1486 { 1484 {
1487 lock (_PrimsDataSet) 1485 lock (_PrimsDataSet)
@@ -1513,6 +1511,10 @@ VALUES
1513 } 1511 }
1514 } 1512 }
1515 1513
1514 /// <summary>
1515 /// Create commands for a dataadapter.
1516 /// </summary>
1517 /// <param name="dataAdapter">The data adapter.</param>
1516 private static void SetupCommands(SqlDataAdapter dataAdapter) 1518 private static void SetupCommands(SqlDataAdapter dataAdapter)
1517 { 1519 {
1518 SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter); 1520 SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
diff --git a/OpenSim/Data/MSSQL/MSSQLUserData.cs b/OpenSim/Data/MSSQL/MSSQLUserData.cs
index 9118012..6352cce 100644
--- a/OpenSim/Data/MSSQL/MSSQLUserData.cs
+++ b/OpenSim/Data/MSSQL/MSSQLUserData.cs
@@ -26,6 +26,7 @@
26 */ 26 */
27 27
28using System; 28using System;
29using System.Collections;
29using System.Collections.Generic; 30using System.Collections.Generic;
30using System.Data; 31using System.Data;
31using System.Data.SqlClient; 32using System.Data.SqlClient;
@@ -41,6 +42,8 @@ namespace OpenSim.Data.MSSQL
41 /// </summary> 42 /// </summary>
42 public class MSSQLUserData : UserDataBase 43 public class MSSQLUserData : UserDataBase
43 { 44 {
45 private const string _migrationStore = "UserStore";
46
44 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); 47 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
45 48
46 /// <summary> 49 /// <summary>
@@ -52,28 +55,35 @@ namespace OpenSim.Data.MSSQL
52 private string m_usersTableName; 55 private string m_usersTableName;
53 private string m_userFriendsTableName; 56 private string m_userFriendsTableName;
54 57
55 public override void Initialise() 58 override public void Initialise()
56 { 59 {
57 m_log.Info("[MSSQLUserData]: " + Name + " cannot be default-initialized!"); 60 m_log.Info("[MSSQLUserData]: " + Name + " cannot be default-initialized!");
58 throw new PluginNotInitialisedException (Name); 61 throw new PluginNotInitialisedException(Name);
59 } 62 }
60 63
61 /// <summary> 64 /// <summary>
62 /// Loads and initialises the MSSQL storage plugin 65 /// Loads and initialises the MSSQL storage plugin
63 /// </summary> 66 /// </summary>
64 /// <param name="connect">TODO: do something with the connect string instead of ignoring it.</param> 67 /// <param name="connect">connectionstring</param>
65 /// <remarks>use mssql_connection.ini</remarks> 68 /// <remarks>use mssql_connection.ini</remarks>
66 override public void Initialise(string connect) 69 override public void Initialise(string connect)
67 { 70 {
68 // TODO: do something with the connect string instead of
69 // ignoring it.
70
71 IniFile iniFile = new IniFile("mssql_connection.ini"); 71 IniFile iniFile = new IniFile("mssql_connection.ini");
72 string settingDataSource = iniFile.ParseFileReadValue("data_source"); 72
73 string settingInitialCatalog = iniFile.ParseFileReadValue("initial_catalog"); 73 if (string.IsNullOrEmpty(connect))
74 string settingPersistSecurityInfo = iniFile.ParseFileReadValue("persist_security_info"); 74 {
75 string settingUserId = iniFile.ParseFileReadValue("user_id"); 75 database = new MSSQLManager(connect);
76 string settingPassword = iniFile.ParseFileReadValue("password"); 76 }
77 else
78 {
79 string settingDataSource = iniFile.ParseFileReadValue("data_source");
80 string settingInitialCatalog = iniFile.ParseFileReadValue("initial_catalog");
81 string settingPersistSecurityInfo = iniFile.ParseFileReadValue("persist_security_info");
82 string settingUserId = iniFile.ParseFileReadValue("user_id");
83 string settingPassword = iniFile.ParseFileReadValue("password");
84
85 database = new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, settingPassword);
86 }
77 87
78 m_usersTableName = iniFile.ParseFileReadValue("userstablename"); 88 m_usersTableName = iniFile.ParseFileReadValue("userstablename");
79 if (m_usersTableName == null) 89 if (m_usersTableName == null)
@@ -93,23 +103,22 @@ namespace OpenSim.Data.MSSQL
93 m_agentsTableName = "agents"; 103 m_agentsTableName = "agents";
94 } 104 }
95 105
96 database = 106 //TODO this can be removed at one time!!!!!
97 new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId,
98 settingPassword);
99
100 TestTables(); 107 TestTables();
108
109 //Check migration on DB
110 database.CheckMigration(_migrationStore);
101 } 111 }
102 112
103 public override void Dispose () {} 113 override public void Dispose() { }
104 114
105 /// <summary> 115 /// <summary>
106 /// 116 /// Can be deleted at one time!
107 /// </summary> 117 /// </summary>
108 /// <returns></returns> 118 /// <returns></returns>
109 private bool TestTables() 119 private void TestTables()
110 { 120 {
111 121 using (IDbCommand cmd = database.Query("select top 1 * from " + m_usersTableName))
112 using (IDbCommand cmd = database.Query("select top 1 * from " + m_usersTableName, new Dictionary<string, string>()))
113 { 122 {
114 try 123 try
115 { 124 {
@@ -145,9 +154,53 @@ namespace OpenSim.Data.MSSQL
145 } 154 }
146 } 155 }
147 156
148 return true; 157 using (IDbCommand cmd = database.Query("select top 1 * from avatarappearance", new Dictionary<string, string>()))
158 {
159 try
160 {
161 cmd.ExecuteNonQuery();
162 }
163 catch
164 {
165 database.ExecuteResourceSql("AvatarAppearance.sql");
166 }
167 }
168
169 //Special for Migrations
170 using (AutoClosingSqlCommand cmd = database.Query("select * from migrations where name = 'UserStore'"))
171 {
172 try
173 {
174 bool insert = true;
175 using (SqlDataReader reader = cmd.ExecuteReader())
176 {
177 if (reader.Read()) insert = false;
178 }
179 if (insert)
180 {
181 cmd.CommandText = "insert into migrations(name, version) values('UserStore', 1)";
182 cmd.ExecuteNonQuery();
183 }
184 }
185 catch
186 {
187 //No migrations table
188 //HACK create one and add data
189 cmd.CommandText = "create table migrations(name varchar(100), version int)";
190 cmd.ExecuteNonQuery();
191
192 cmd.CommandText = "insert into migrations(name, version) values('migrations', 1)";
193 cmd.ExecuteNonQuery();
194
195 cmd.CommandText = "insert into migrations(name, version) values('UserStore', 1)";
196 cmd.ExecuteNonQuery();
197 }
198 }
199 return;
149 } 200 }
150 201
202 #region User table methods
203
151 /// <summary> 204 /// <summary>
152 /// Searches the database for a specified user profile by name components 205 /// Searches the database for a specified user profile by name components
153 /// </summary> 206 /// </summary>
@@ -156,623 +209,713 @@ namespace OpenSim.Data.MSSQL
156 /// <returns>A user profile</returns> 209 /// <returns>A user profile</returns>
157 override public UserProfileData GetUserByName(string user, string last) 210 override public UserProfileData GetUserByName(string user, string last)
158 { 211 {
159 try 212 using (AutoClosingSqlCommand command = database.Query("SELECT * FROM " + m_usersTableName + " WHERE username = @first AND lastname = @second"))
160 { 213 {
161 Dictionary<string, string> param = new Dictionary<string, string>(); 214 command.Parameters.Add(database.CreateParameter("first", user));
162 param["first"] = user; 215 command.Parameters.Add(database.CreateParameter("second", last));
163 param["second"] = last;
164 216
165 using (IDbCommand result = database.Query("SELECT * FROM " + m_usersTableName + " WHERE username = @first AND lastname = @second", param)) 217 try
166 using (IDataReader reader = result.ExecuteReader())
167 { 218 {
168 return database.readUserRow(reader); 219 using (SqlDataReader reader = command.ExecuteReader())
220 {
221 return ReadUserRow(reader);
222 }
223 }
224 catch (Exception e)
225 {
226 m_log.Error("[USER DB] Error getting user profile, error: " + e.Message);
227 return null;
169 } 228 }
170 }
171 catch (Exception e)
172 {
173 m_log.Error(e.ToString());
174 return null;
175 } 229 }
176 } 230 }
177 231
178 #region User Friends List Data
179
180 /// <summary> 232 /// <summary>
181 /// Add a new friend in the friendlist 233 /// See IUserDataPlugin
182 /// </summary> 234 /// </summary>
183 /// <param name="friendlistowner">UUID of the friendlist owner</param> 235 /// <param name="uuid"></param>
184 /// <param name="friend">Friend's UUID</param> 236 /// <returns></returns>
185 /// <param name="perms">Permission flag</param> 237 override public UserProfileData GetUserByUUID(UUID uuid)
186 override public void AddNewUserFriend(UUID friendlistowner, UUID friend, uint perms)
187 { 238 {
188 int dtvalue = Util.UnixTimeSinceEpoch(); 239 using (AutoClosingSqlCommand command = database.Query("SELECT * FROM " + m_usersTableName + " WHERE UUID = @uuid"))
189
190 Dictionary<string, string> param = new Dictionary<string, string>();
191 param["@ownerID"] = friendlistowner.ToString();
192 param["@friendID"] = friend.ToString();
193 param["@friendPerms"] = perms.ToString();
194 param["@datetimestamp"] = dtvalue.ToString();
195
196 try
197 { 240 {
198 using (IDbCommand adder = 241 command.Parameters.Add(database.CreateParameter("uuid", uuid));
199 database.Query( 242
200 "INSERT INTO " + m_userFriendsTableName + " " + 243 try
201 "(ownerID,friendID,friendPerms,datetimestamp) " +
202 "VALUES " +
203 "(@ownerID,@friendID,@friendPerms,@datetimestamp)",
204 param))
205 { 244 {
206 adder.ExecuteNonQuery(); 245 using (IDataReader reader = command.ExecuteReader())
246 {
247 return ReadUserRow(reader);
248 }
207 } 249 }
208 250 catch (Exception e)
209 using (IDbCommand adder =
210 database.Query(
211 "INSERT INTO " + m_userFriendsTableName + " " +
212 "(ownerID,friendID,friendPerms,datetimestamp) " +
213 "VALUES " +
214 "(@friendID,@ownerID,@friendPerms,@datetimestamp)",
215 param))
216 { 251 {
217 adder.ExecuteNonQuery(); 252 m_log.Error("[USER DB] Error getting user profile by UUID, error: " + e.Message);
253 return null;
218 } 254 }
219 } 255 }
220 catch (Exception e)
221 {
222 m_log.Error(e.ToString());
223 return;
224 }
225 } 256 }
226 257
258
227 /// <summary> 259 /// <summary>
228 /// Remove an friend from the friendlist 260 /// Creates a new users profile
229 /// </summary> 261 /// </summary>
230 /// <param name="friendlistowner">UUID of the friendlist owner</param> 262 /// <param name="user">The user profile to create</param>
231 /// <param name="friend">UUID of the not-so-friendly user to remove from the list</param> 263 override public void AddNewUserProfile(UserProfileData user)
232 override public void RemoveUserFriend(UUID friendlistowner, UUID friend)
233 { 264 {
234 Dictionary<string, string> param = new Dictionary<string, string>();
235 param["@ownerID"] = friendlistowner.ToString();
236 param["@friendID"] = friend.ToString();
237
238
239 try 265 try
240 { 266 {
241 using (IDbCommand updater = 267 InsertUserRow(user.ID, user.FirstName, user.SurName, user.PasswordHash, user.PasswordSalt,
242 database.Query( 268 user.HomeRegion, user.HomeLocation.X, user.HomeLocation.Y,
243 "delete from " + m_userFriendsTableName + " where ownerID = @ownerID and friendID = @friendID", 269 user.HomeLocation.Z,
244 param)) 270 user.HomeLookAt.X, user.HomeLookAt.Y, user.HomeLookAt.Z, user.Created,
245 { 271 user.LastLogin, user.UserInventoryURI, user.UserAssetURI,
246 updater.ExecuteNonQuery(); 272 user.CanDoMask, user.WantDoMask,
247 } 273 user.AboutText, user.FirstLifeAboutText, user.Image,
248 274 user.FirstLifeImage, user.WebLoginKey, user.HomeRegionID,
249 using (IDbCommand updater = 275 user.GodLevel, user.UserFlags, user.CustomType, user.Partner);
250 database.Query(
251 "delete from " + m_userFriendsTableName + " where ownerID = @friendID and friendID = @ownerID",
252 param))
253 {
254 updater.ExecuteNonQuery();
255 }
256
257 } 276 }
258 catch (Exception e) 277 catch (Exception e)
259 { 278 {
260 m_log.Error(e.ToString()); 279 m_log.Error("[USER DB] Error adding new profile, error: " + e.Message);
261 } 280 }
262 } 281 }
263 282
264 /// <summary> 283 /// <summary>
265 /// Update friendlist permission flag for a friend 284 /// update a user profile
266 /// </summary> 285 /// </summary>
267 /// <param name="friendlistowner">UUID of the friendlist owner</param> 286 /// <param name="user">the profile to update</param>
268 /// <param name="friend">UUID of the friend</param> 287 /// <returns></returns>
269 /// <param name="perms">new permission flag</param> 288 override public bool UpdateUserProfile(UserProfileData user)
270 override public void UpdateUserFriendPerms(UUID friendlistowner, UUID friend, uint perms)
271 { 289 {
272 Dictionary<string, string> param = new Dictionary<string, string>(); 290 using (AutoClosingSqlCommand command = database.Query("UPDATE " + m_usersTableName + " set UUID = @uuid, " +
273 param["@ownerID"] = friendlistowner.ToString(); 291 "username = @username, " +
274 param["@friendID"] = friend.ToString(); 292 "lastname = @lastname," +
275 param["@friendPerms"] = perms.ToString(); 293 "passwordHash = @passwordHash," +
276 294 "passwordSalt = @passwordSalt," +
277 295 "homeRegion = @homeRegion," +
278 try 296 "homeLocationX = @homeLocationX," +
297 "homeLocationY = @homeLocationY," +
298 "homeLocationZ = @homeLocationZ," +
299 "homeLookAtX = @homeLookAtX," +
300 "homeLookAtY = @homeLookAtY," +
301 "homeLookAtZ = @homeLookAtZ," +
302 "created = @created," +
303 "lastLogin = @lastLogin," +
304 "userInventoryURI = @userInventoryURI," +
305 "userAssetURI = @userAssetURI," +
306 "profileCanDoMask = @profileCanDoMask," +
307 "profileWantDoMask = @profileWantDoMask," +
308 "profileAboutText = @profileAboutText," +
309 "profileFirstText = @profileFirstText," +
310 "profileImage = @profileImage," +
311 "profileFirstImage = @profileFirstImage, " +
312 "webLoginKey = @webLoginKey, " +
313 "homeRegionID = @homeRegionID, " +
314 "userFlags = @userFlags, " +
315 "godLevel = @godLevel, " +
316 "customType = @customType, " +
317 "partner = @partner where " +
318 "UUID = @keyUUUID;"))
279 { 319 {
280 using (IDbCommand updater = 320 command.Parameters.Add(database.CreateParameter("uuid", user.ID));
281 database.Query( 321 command.Parameters.Add(database.CreateParameter("username", user.FirstName));
282 "update " + m_userFriendsTableName + 322 command.Parameters.Add(database.CreateParameter("lastname", user.SurName));
283 " SET friendPerms = @friendPerms " + 323 command.Parameters.Add(database.CreateParameter("passwordHash", user.PasswordHash));
284 "where ownerID = @ownerID and friendID = @friendID", 324 command.Parameters.Add(database.CreateParameter("passwordSalt", user.PasswordSalt));
285 param)) 325 command.Parameters.Add(database.CreateParameter("homeRegion", user.HomeRegion));
326 command.Parameters.Add(database.CreateParameter("homeLocationX", user.HomeLocation.X));
327 command.Parameters.Add(database.CreateParameter("homeLocationY", user.HomeLocation.Y));
328 command.Parameters.Add(database.CreateParameter("homeLocationZ", user.HomeLocation.Z));
329 command.Parameters.Add(database.CreateParameter("homeLookAtX", user.HomeLookAt.X));
330 command.Parameters.Add(database.CreateParameter("homeLookAtY", user.HomeLookAt.Y));
331 command.Parameters.Add(database.CreateParameter("homeLookAtZ", user.HomeLookAt.Z));
332 command.Parameters.Add(database.CreateParameter("created", user.Created));
333 command.Parameters.Add(database.CreateParameter("lastLogin", user.LastLogin));
334 command.Parameters.Add(database.CreateParameter("userInventoryURI", user.UserInventoryURI));
335 command.Parameters.Add(database.CreateParameter("userAssetURI", user.UserAssetURI));
336 command.Parameters.Add(database.CreateParameter("profileCanDoMask", user.CanDoMask));
337 command.Parameters.Add(database.CreateParameter("profileWantDoMask", user.WantDoMask));
338 command.Parameters.Add(database.CreateParameter("profileAboutText", user.AboutText));
339 command.Parameters.Add(database.CreateParameter("profileFirstText", user.FirstLifeAboutText));
340 command.Parameters.Add(database.CreateParameter("profileImage", user.Image));
341 command.Parameters.Add(database.CreateParameter("profileFirstImage", user.FirstLifeImage));
342 command.Parameters.Add(database.CreateParameter("webLoginKey", user.WebLoginKey));
343 //
344 command.Parameters.Add(database.CreateParameter("homeRegionID", user.HomeRegionID));
345 command.Parameters.Add(database.CreateParameter("userFlags", user.UserFlags));
346 command.Parameters.Add(database.CreateParameter("godLevel", user.GodLevel));
347 command.Parameters.Add(database.CreateParameter("customType", user.CustomType));
348 command.Parameters.Add(database.CreateParameter("partner", user.Partner));
349 command.Parameters.Add(database.CreateParameter("keyUUUID", user.ID));
350
351 try
286 { 352 {
287 updater.ExecuteNonQuery(); 353 int affected = command.ExecuteNonQuery();
354 return (affected != 0);
355 }
356 catch (Exception e)
357 {
358 m_log.Error("[USER DB] Error updating profile, error: " + e.Message);
288 } 359 }
289 } 360 }
290 catch (Exception e) 361 return false;
291 {
292 m_log.Error(e.ToString());
293 }
294 } 362 }
295 363
364 #endregion
365
366 #region Agent table methods
367
296 /// <summary> 368 /// <summary>
297 /// Get (fetch?) the user's friendlist 369 /// Returns a user session searching by name
298 /// </summary> 370 /// </summary>
299 /// <param name="friendlistowner">UUID of the friendlist owner</param> 371 /// <param name="name">The account name</param>
300 /// <returns>Friendlist list</returns> 372 /// <returns>The users session</returns>
301 override public List<FriendListItem> GetUserFriendList(UUID friendlistowner) 373 override public UserAgentData GetAgentByName(string name)
302 { 374 {
303 List<FriendListItem> Lfli = new List<FriendListItem>(); 375 return GetAgentByName(name.Split(' ')[0], name.Split(' ')[1]);
376 }
304 377
305 Dictionary<string, string> param = new Dictionary<string, string>(); 378 /// <summary>
306 param["@ownerID"] = friendlistowner.ToString(); 379 /// Returns a user session by account name
380 /// </summary>
381 /// <param name="user">First part of the users account name</param>
382 /// <param name="last">Second part of the users account name</param>
383 /// <returns>The users session</returns>
384 override public UserAgentData GetAgentByName(string user, string last)
385 {
386 UserProfileData profile = GetUserByName(user, last);
387 return GetAgentByUUID(profile.ID);
388 }
307 389
308 try 390 /// <summary>
391 /// Returns an agent session by account UUID
392 /// </summary>
393 /// <param name="uuid">The accounts UUID</param>
394 /// <returns>The users session</returns>
395 override public UserAgentData GetAgentByUUID(UUID uuid)
396 {
397 using (AutoClosingSqlCommand command = database.Query("SELECT * FROM " + m_agentsTableName + " WHERE UUID = @uuid"))
309 { 398 {
310 //Left Join userfriends to itself 399 command.Parameters.Add(database.CreateParameter("uuid", uuid));
311 using (IDbCommand result = 400 try
312 database.Query(
313 "select a.ownerID,a.friendID,a.friendPerms,b.friendPerms as ownerperms from " + m_userFriendsTableName + " as a, " + m_userFriendsTableName + " as b" +
314 " where a.ownerID = @ownerID and b.ownerID = a.friendID and b.friendID = a.ownerID",
315 param))
316 using (IDataReader reader = result.ExecuteReader())
317 { 401 {
318 while (reader.Read()) 402 using (SqlDataReader reader = command.ExecuteReader())
319 { 403 {
320 FriendListItem fli = new FriendListItem(); 404 return readAgentRow(reader);
321 fli.FriendListOwner = new UUID((string)reader["ownerID"]);
322 fli.Friend = new UUID((string)reader["friendID"]);
323 fli.FriendPerms = (uint)Convert.ToInt32(reader["friendPerms"]);
324
325 // This is not a real column in the database table, it's a joined column from the opposite record
326 fli.FriendListOwnerPerms = (uint)Convert.ToInt32(reader["ownerperms"]);
327
328 Lfli.Add(fli);
329 } 405 }
330 } 406 }
407 catch (Exception e)
408 {
409 m_log.Error("[USER DB] Error updating agentdata by UUID, error: " + e.Message);
410 return null;
411 }
412 }
413 }
414
415 /// <summary>
416 /// Creates a new agent
417 /// </summary>
418 /// <param name="agent">The agent to create</param>
419 override public void AddNewUserAgent(UserAgentData agent)
420 {
421 try
422 {
423 InsertUpdateAgentRow(agent);
331 } 424 }
332 catch (Exception e) 425 catch (Exception e)
333 { 426 {
334 m_log.Error(e.ToString()); 427 m_log.Error("[USER DB] Error adding new agentdata, error: " + e.Message);
335 } 428 }
336
337 return Lfli;
338 } 429 }
339 430
340 #endregion 431 #endregion
341 432
433 #region User Friends List Data
434
342 /// <summary> 435 /// <summary>
343 /// STUB ! Update current region 436 /// Add a new friend in the friendlist
344 /// </summary> 437 /// </summary>
345 /// <param name="avatarid">avatar uuid</param> 438 /// <param name="friendlistowner">UUID of the friendlist owner</param>
346 /// <param name="regionuuid">region uuid</param> 439 /// <param name="friend">Friend's UUID</param>
347 /// <param name="regionhandle">region handle</param> 440 /// <param name="perms">Permission flag</param>
348 override public void UpdateUserCurrentRegion(UUID avatarid, UUID regionuuid, ulong regionhandle) 441 override public void AddNewUserFriend(UUID friendlistowner, UUID friend, uint perms)
349 { 442 {
350 //m_log.Info("[USER]: Stub UpdateUserCUrrentRegion called"); 443 int dtvalue = Util.UnixTimeSinceEpoch();
444
445 using (AutoClosingSqlCommand command = database.Query(
446 "INSERT INTO " + m_userFriendsTableName + " " +
447 "(ownerID,friendID,friendPerms,datetimestamp) " +
448 "VALUES " +
449 "(@ownerID,@friendID,@friendPerms,@datetimestamp)"))
450 {
451 command.Parameters.Add(database.CreateParameter("ownerID", friendlistowner));
452 command.Parameters.Add(database.CreateParameter("friendID", friend));
453 command.Parameters.Add(database.CreateParameter("friendPerms", perms));
454 command.Parameters.Add(database.CreateParameter("datetimestamp", dtvalue));
455 command.ExecuteNonQuery();
456
457 try
458 {
459 command.CommandText = string.Format("INSERT INTO {0} (ownerID,friendID,friendPerms,datetimestamp) VALUES (@friendID,@ownerID,@friendPerms,@datetimestamp)",
460 m_userFriendsTableName);
461
462 command.ExecuteNonQuery();
463 }
464 catch (Exception e)
465 {
466 m_log.Error("[USER DB] Error adding new userfriend, error: " + e.Message);
467 return;
468 }
469 }
351 } 470 }
352 471
353 /// <summary> 472 /// <summary>
354 /// 473 /// Remove an friend from the friendlist
355 /// </summary> 474 /// </summary>
356 /// <param name="queryID"></param> 475 /// <param name="friendlistowner">UUID of the friendlist owner</param>
357 /// <param name="query"></param> 476 /// <param name="friend">UUID of the not-so-friendly user to remove from the list</param>
358 /// <returns></returns> 477 override public void RemoveUserFriend(UUID friendlistowner, UUID friend)
359 override public List<AvatarPickerAvatar> GeneratePickerResults(UUID queryID, string query)
360 { 478 {
361 List<AvatarPickerAvatar> returnlist = new List<AvatarPickerAvatar>(); 479 using (AutoClosingSqlCommand command = database.Query("delete from " + m_userFriendsTableName + " where ownerID = @ownerID and friendID = @friendID"))
362 string[] querysplit;
363 querysplit = query.Split(' ');
364 if (querysplit.Length == 2)
365 { 480 {
481 command.Parameters.Add(database.CreateParameter("@ownerID", friendlistowner));
482 command.Parameters.Add(database.CreateParameter("@friendID", friend));
483 command.ExecuteNonQuery();
484
485 command.CommandText = "delete from " + m_userFriendsTableName +
486 " where ownerID = @friendID and friendID = @ownerID";
366 try 487 try
367 { 488 {
368 Dictionary<string, string> param = new Dictionary<string, string>(); 489 command.ExecuteNonQuery();
369 param["first"] = querysplit[0];
370 param["second"] = querysplit[1];
371
372 using (IDbCommand result = database.Query("SELECT UUID,username,lastname FROM " + m_usersTableName + " WHERE username = @first AND lastname = @second", param))
373 using (IDataReader reader = result.ExecuteReader())
374 {
375 while (reader.Read())
376 {
377 AvatarPickerAvatar user = new AvatarPickerAvatar();
378 user.AvatarID = new UUID((string)reader["UUID"]);
379 user.firstName = (string)reader["username"];
380 user.lastName = (string)reader["lastname"];
381 returnlist.Add(user);
382 }
383 }
384 } 490 }
385 catch (Exception e) 491 catch (Exception e)
386 { 492 {
387 m_log.Error(e.ToString()); 493 m_log.Error("[USER DB] Error removing userfriend, error: " + e.Message);
388 } 494 }
389 } 495 }
390 else if (querysplit.Length == 1) 496 }
497
498 /// <summary>
499 /// Update friendlist permission flag for a friend
500 /// </summary>
501 /// <param name="friendlistowner">UUID of the friendlist owner</param>
502 /// <param name="friend">UUID of the friend</param>
503 /// <param name="perms">new permission flag</param>
504 override public void UpdateUserFriendPerms(UUID friendlistowner, UUID friend, uint perms)
505 {
506 using (AutoClosingSqlCommand command = database.Query(
507 "update " + m_userFriendsTableName +
508 " SET friendPerms = @friendPerms " +
509 "where ownerID = @ownerID and friendID = @friendID"))
391 { 510 {
511 command.Parameters.Add(database.CreateParameter("@ownerID", friendlistowner));
512 command.Parameters.Add(database.CreateParameter("@friendID", friend));
513 command.Parameters.Add(database.CreateParameter("@friendPerms", perms));
514
392 try 515 try
393 { 516 {
394 Dictionary<string, string> param = new Dictionary<string, string>(); 517 command.ExecuteNonQuery();
395 param["first"] = querysplit[0];
396
397 using (IDbCommand result = database.Query("SELECT UUID,username,lastname FROM " + m_usersTableName + " WHERE username = @first OR lastname = @first", param))
398 using (IDataReader reader = result.ExecuteReader())
399 {
400 while (reader.Read())
401 {
402 AvatarPickerAvatar user = new AvatarPickerAvatar();
403 user.AvatarID = new UUID((string)reader["UUID"]);
404 user.firstName = (string)reader["username"];
405 user.lastName = (string)reader["lastname"];
406 returnlist.Add(user);
407 }
408 }
409 } 518 }
410 catch (Exception e) 519 catch (Exception e)
411 { 520 {
412 m_log.Error(e.ToString()); 521 m_log.Error("[USER DB] Error updating userfriend, error: " + e.Message);
413 } 522 }
414 } 523 }
415 return returnlist;
416 } 524 }
417 525
418 /// <summary> 526 /// <summary>
419 /// See IUserDataPlugin 527 /// Get (fetch?) the user's friendlist
420 /// </summary> 528 /// </summary>
421 /// <param name="uuid"></param> 529 /// <param name="friendlistowner">UUID of the friendlist owner</param>
422 /// <returns></returns> 530 /// <returns>Friendlist list</returns>
423 override public UserProfileData GetUserByUUID(UUID uuid) 531 override public List<FriendListItem> GetUserFriendList(UUID friendlistowner)
424 { 532 {
425 try 533 List<FriendListItem> friendList = new List<FriendListItem>();
534
535 //Left Join userfriends to itself
536 using (AutoClosingSqlCommand command = database.Query(
537 "select a.ownerID,a.friendID,a.friendPerms,b.friendPerms as ownerperms from " + m_userFriendsTableName + " as a, " + m_userFriendsTableName + " as b" +
538 " where a.ownerID = @ownerID and b.ownerID = a.friendID and b.friendID = a.ownerID"))
426 { 539 {
427 Dictionary<string, string> param = new Dictionary<string, string>(); 540 command.Parameters.Add(database.CreateParameter("@ownerID", friendlistowner));
428 param["uuid"] = uuid.ToString(); 541
542 try
543 {
544 using (IDataReader reader = command.ExecuteReader())
545 {
546 while (reader.Read())
547 {
548 FriendListItem fli = new FriendListItem();
549 fli.FriendListOwner = new UUID((string)reader["ownerID"]);
550 fli.Friend = new UUID((string)reader["friendID"]);
551 fli.FriendPerms = (uint)Convert.ToInt32(reader["friendPerms"]);
429 552
430 using (IDbCommand result = database.Query("SELECT * FROM " + m_usersTableName + " WHERE UUID = @uuid", param)) 553 // This is not a real column in the database table, it's a joined column from the opposite record
431 using (IDataReader reader = result.ExecuteReader()) 554 fli.FriendListOwnerPerms = (uint)Convert.ToInt32(reader["ownerperms"]);
555
556 friendList.Add(fli);
557 }
558 }
559 }
560 catch (Exception e)
432 { 561 {
433 return database.readUserRow(reader); 562 m_log.Error("[USER DB] Error updating userfriend, error: " + e.Message);
434 } 563 }
435 } 564 }
436 catch (Exception e) 565
437 { 566 return friendList;
438 m_log.Error(e.ToString());
439 return null;
440 }
441 } 567 }
442 568
569 #endregion
570
571 #region Money functions (not used)
572
443 /// <summary> 573 /// <summary>
444 /// Returns a user session searching by name 574 /// Performs a money transfer request between two accounts
445 /// </summary> 575 /// </summary>
446 /// <param name="name">The account name</param> 576 /// <param name="from">The senders account ID</param>
447 /// <returns>The users session</returns> 577 /// <param name="to">The receivers account ID</param>
448 override public UserAgentData GetAgentByName(string name) 578 /// <param name="amount">The amount to transfer</param>
579 /// <returns>false</returns>
580 override public bool MoneyTransferRequest(UUID from, UUID to, uint amount)
449 { 581 {
450 return GetAgentByName(name.Split(' ')[0], name.Split(' ')[1]); 582 return false;
451 } 583 }
452 584
453 /// <summary> 585 /// <summary>
454 /// Returns a user session by account name 586 /// Performs an inventory transfer request between two accounts
455 /// </summary> 587 /// </summary>
456 /// <param name="user">First part of the users account name</param> 588 /// <remarks>TODO: Move to inventory server</remarks>
457 /// <param name="last">Second part of the users account name</param> 589 /// <param name="from">The senders account ID</param>
458 /// <returns>The users session</returns> 590 /// <param name="to">The receivers account ID</param>
459 override public UserAgentData GetAgentByName(string user, string last) 591 /// <param name="item">The item to transfer</param>
592 /// <returns>false</returns>
593 override public bool InventoryTransferRequest(UUID from, UUID to, UUID item)
460 { 594 {
461 UserProfileData profile = GetUserByName(user, last); 595 return false;
462 return GetAgentByUUID(profile.ID);
463 } 596 }
464 597
598 #endregion
599
600 #region Appearance methods
601
465 /// <summary> 602 /// <summary>
466 /// Returns an agent session by account UUID 603 /// Gets the user appearance.
467 /// </summary> 604 /// </summary>
468 /// <param name="uuid">The accounts UUID</param> 605 /// <param name="user">The user.</param>
469 /// <returns>The users session</returns> 606 /// <returns></returns>
470 override public UserAgentData GetAgentByUUID(UUID uuid) 607 /// TODO: stubs for now to get us to a compiling state gently
608 override public AvatarAppearance GetUserAppearance(UUID user)
471 { 609 {
472 try 610 try
473 { 611 {
474 Dictionary<string, string> param = new Dictionary<string, string>(); 612 AvatarAppearance appearance = new AvatarAppearance();
475 param["uuid"] = uuid.ToString();
476 613
477 using (IDbCommand result = database.Query("SELECT * FROM " + m_agentsTableName + " WHERE UUID = @uuid", param)) 614 using (AutoClosingSqlCommand command = database.Query("SELECT * FROM avatarappearance WHERE owner = @UUID"))
478 using (IDataReader reader = result.ExecuteReader())
479 { 615 {
480 return database.readAgentRow(reader); 616 command.Parameters.Add(database.CreateParameter("@UUID", user));
617 using (IDataReader reader = command.ExecuteReader())
618 {
619 if (reader.Read())
620 appearance = readUserAppearance(reader);
621 }
481 } 622 }
623
624 appearance.SetAttachments(GetUserAttachments(user));
625
626 return appearance;
482 } 627 }
483 catch (Exception e) 628 catch (Exception e)
484 { 629 {
485 m_log.Error(e.ToString()); 630 m_log.Error("[USER DB] Error updating userfriend, error: " + e.Message);
486 return null;
487 } 631 }
632 return null;
488 } 633 }
489 634
635
490 /// <summary> 636 /// <summary>
491 /// Store a weblogin key 637 /// Update a user appearence into database
492 /// </summary> 638 /// </summary>
493 /// <param name="AgentID">The agent UUID</param> 639 /// <param name="user">the used UUID</param>
494 /// <param name="WebLoginKey">the WebLogin Key</param> 640 /// <param name="appearance">the appearence</param>
495 /// <remarks>unused ?</remarks> 641 override public void UpdateUserAppearance(UUID user, AvatarAppearance appearance)
496 override public void StoreWebLoginKey(UUID AgentID, UUID WebLoginKey)
497 { 642 {
498 UserProfileData user = GetUserByUUID(AgentID); 643 string sql = String.Empty;
499 user.WebLoginKey = WebLoginKey; 644 sql += "DELETE FROM avatarappearance WHERE owner=@owner ";
500 UpdateUserProfile(user); 645 sql += "INSERT INTO avatarappearance ";
646 sql += "(owner, serial, visual_params, texture, avatar_height, ";
647 sql += "body_item, body_asset, skin_item, skin_asset, hair_item, hair_asset, eyes_item, eyes_asset, ";
648 sql += "shirt_item, shirt_asset, pants_item, pants_asset, shoes_item, shoes_asset, socks_item, socks_asset, ";
649 sql += "jacket_item, jacket_asset, gloves_item, gloves_asset, undershirt_item, undershirt_asset, underpants_item, underpants_asset, ";
650 sql += "skirt_item, skirt_asset) values (";
651 sql += "@owner, @serial, @visual_params, @texture, @avatar_height, ";
652 sql += "@body_item, @body_asset, @skin_item, @skin_asset, @hair_item, @hair_asset, @eyes_item, @eyes_asset, ";
653 sql += "@shirt_item, @shirt_asset, @pants_item, @pants_asset, @shoes_item, @shoes_asset, @socks_item, @socks_asset, ";
654 sql += "@jacket_item, @jacket_asset, @gloves_item, @gloves_asset, @undershirt_item, @undershirt_asset, @underpants_item, @underpants_asset, ";
655 sql += "@skirt_item, @skirt_asset)";
656
657 using (AutoClosingSqlCommand cmd = database.Query(sql))
658 {
659 cmd.Parameters.Add(database.CreateParameter("@owner", appearance.Owner));
660 cmd.Parameters.Add(database.CreateParameter("@serial", appearance.Serial));
661 cmd.Parameters.Add(database.CreateParameter("@visual_params", appearance.VisualParams));
662 cmd.Parameters.Add(database.CreateParameter("@texture", appearance.Texture.ToBytes()));
663 cmd.Parameters.Add(database.CreateParameter("@avatar_height", appearance.AvatarHeight));
664 cmd.Parameters.Add(database.CreateParameter("@body_item", appearance.BodyItem));
665 cmd.Parameters.Add(database.CreateParameter("@body_asset", appearance.BodyAsset));
666 cmd.Parameters.Add(database.CreateParameter("@skin_item", appearance.SkinItem));
667 cmd.Parameters.Add(database.CreateParameter("@skin_asset", appearance.SkinAsset));
668 cmd.Parameters.Add(database.CreateParameter("@hair_item", appearance.HairItem));
669 cmd.Parameters.Add(database.CreateParameter("@hair_asset", appearance.HairAsset));
670 cmd.Parameters.Add(database.CreateParameter("@eyes_item", appearance.EyesItem));
671 cmd.Parameters.Add(database.CreateParameter("@eyes_asset", appearance.EyesAsset));
672 cmd.Parameters.Add(database.CreateParameter("@shirt_item", appearance.ShirtItem));
673 cmd.Parameters.Add(database.CreateParameter("@shirt_asset", appearance.ShirtAsset));
674 cmd.Parameters.Add(database.CreateParameter("@pants_item", appearance.PantsItem));
675 cmd.Parameters.Add(database.CreateParameter("@pants_asset", appearance.PantsAsset));
676 cmd.Parameters.Add(database.CreateParameter("@shoes_item", appearance.ShoesItem));
677 cmd.Parameters.Add(database.CreateParameter("@shoes_asset", appearance.ShoesAsset));
678 cmd.Parameters.Add(database.CreateParameter("@socks_item", appearance.SocksItem));
679 cmd.Parameters.Add(database.CreateParameter("@socks_asset", appearance.SocksAsset));
680 cmd.Parameters.Add(database.CreateParameter("@jacket_item", appearance.JacketItem));
681 cmd.Parameters.Add(database.CreateParameter("@jacket_asset", appearance.JacketAsset));
682 cmd.Parameters.Add(database.CreateParameter("@gloves_item", appearance.GlovesItem));
683 cmd.Parameters.Add(database.CreateParameter("@gloves_asset", appearance.GlovesAsset));
684 cmd.Parameters.Add(database.CreateParameter("@undershirt_item", appearance.UnderShirtItem));
685 cmd.Parameters.Add(database.CreateParameter("@undershirt_asset", appearance.UnderShirtAsset));
686 cmd.Parameters.Add(database.CreateParameter("@underpants_item", appearance.UnderPantsItem));
687 cmd.Parameters.Add(database.CreateParameter("@underpants_asset", appearance.UnderPantsAsset));
688 cmd.Parameters.Add(database.CreateParameter("@skirt_item", appearance.SkirtItem));
689 cmd.Parameters.Add(database.CreateParameter("@skirt_asset", appearance.SkirtAsset));
501 690
691 try
692 {
693 cmd.ExecuteNonQuery();
694 }
695 catch (Exception e)
696 {
697 m_log.Error("[USER DB] Error updating user appearance, error: " + e.Message);
698 }
699 }
700
701 UpdateUserAttachments(user, appearance.GetAttachments());
502 } 702 }
703
704 #endregion
705
706 #region Attachment methods
707
503 /// <summary> 708 /// <summary>
504 /// Creates a new users profile 709 /// Gets all attachment of a agent.
505 /// </summary> 710 /// </summary>
506 /// <param name="user">The user profile to create</param> 711 /// <param name="agentID">agent ID.</param>
507 override public void AddNewUserProfile(UserProfileData user) 712 /// <returns></returns>
713 public Hashtable GetUserAttachments(UUID agentID)
508 { 714 {
509 try 715 Hashtable returnTable = new Hashtable();
510 { 716 using (AutoClosingSqlCommand command = database.Query("select attachpoint, item, asset from avatarattachments where UUID = @uuid", database.CreateParameter("@uuid", agentID)))
511 InsertUserRow(user.ID, user.FirstName, user.SurName, user.PasswordHash, user.PasswordSalt,
512 user.HomeRegion, user.HomeLocation.X, user.HomeLocation.Y,
513 user.HomeLocation.Z,
514 user.HomeLookAt.X, user.HomeLookAt.Y, user.HomeLookAt.Z, user.Created,
515 user.LastLogin, user.UserInventoryURI, user.UserAssetURI,
516 user.CanDoMask, user.WantDoMask,
517 user.AboutText, user.FirstLifeAboutText, user.Image,
518 user.FirstLifeImage, user.WebLoginKey);
519 }
520 catch (Exception e)
521 { 717 {
522 m_log.Error(e.ToString()); 718 using (SqlDataReader reader = command.ExecuteReader())
719 {
720 while (reader.Read())
721 {
722 int attachpoint = Convert.ToInt32(reader["attachpoint"]);
723 if (returnTable.ContainsKey(attachpoint))
724 continue;
725 Hashtable item = new Hashtable();
726 item.Add("item", reader["item"].ToString());
727 item.Add("asset", reader["asset"].ToString());
728
729 returnTable.Add(attachpoint, item);
730 }
731 }
523 } 732 }
733 return returnTable;
524 } 734 }
525 735
526 /// <summary> 736 /// <summary>
527 /// Creates a new user and inserts it into the database 737 /// Updates all attachments of the agent.
528 /// </summary> 738 /// </summary>
529 /// <param name="uuid">User ID</param> 739 /// <param name="agentID">agentID.</param>
530 /// <param name="username">First part of the login</param> 740 /// <param name="data">data with all items on attachmentpoints</param>
531 /// <param name="lastname">Second part of the login</param> 741 public void UpdateUserAttachments(UUID agentID, Hashtable data)
532 /// <param name="passwordHash">A salted hash of the users password</param> 742 {
533 /// <param name="passwordSalt">The salt used for the password hash</param> 743 string sql = "delete from avatarattachments where UUID = @uuid";
534 /// <param name="homeRegion">A regionHandle of the users home region</param>
535 /// <param name="homeLocX">Home region position vector</param>
536 /// <param name="homeLocY">Home region position vector</param>
537 /// <param name="homeLocZ">Home region position vector</param>
538 /// <param name="homeLookAtX">Home region 'look at' vector</param>
539 /// <param name="homeLookAtY">Home region 'look at' vector</param>
540 /// <param name="homeLookAtZ">Home region 'look at' vector</param>
541 /// <param name="created">Account created (unix timestamp)</param>
542 /// <param name="lastlogin">Last login (unix timestamp)</param>
543 /// <param name="inventoryURI">Users inventory URI</param>
544 /// <param name="assetURI">Users asset URI</param>
545 /// <param name="canDoMask">I can do mask</param>
546 /// <param name="wantDoMask">I want to do mask</param>
547 /// <param name="aboutText">Profile text</param>
548 /// <param name="firstText">Firstlife text</param>
549 /// <param name="profileImage">UUID for profile image</param>
550 /// <param name="firstImage">UUID for firstlife image</param>
551 /// <returns>Success?</returns>
552 private bool InsertUserRow(UUID uuid, string username, string lastname, string passwordHash,
553 string passwordSalt, UInt64 homeRegion, float homeLocX, float homeLocY, float homeLocZ,
554 float homeLookAtX, float homeLookAtY, float homeLookAtZ, int created, int lastlogin,
555 string inventoryURI, string assetURI, uint canDoMask, uint wantDoMask,
556 string aboutText, string firstText,
557 UUID profileImage, UUID firstImage, UUID webLoginKey)
558 {
559 string sql = "INSERT INTO "+m_usersTableName;
560 sql += " ([UUID], [username], [lastname], [passwordHash], [passwordSalt], [homeRegion], ";
561 sql +=
562 "[homeLocationX], [homeLocationY], [homeLocationZ], [homeLookAtX], [homeLookAtY], [homeLookAtZ], [created], ";
563 sql +=
564 "[lastLogin], [userInventoryURI], [userAssetURI], [profileCanDoMask], [profileWantDoMask], [profileAboutText], ";
565 sql += "[profileFirstText], [profileImage], [profileFirstImage], [webLoginKey]) VALUES ";
566 744
567 sql += "(@UUID, @username, @lastname, @passwordHash, @passwordSalt, @homeRegion, "; 745 using (AutoClosingSqlCommand command = database.Query(sql))
568 sql += 746 {
569 "@homeLocationX, @homeLocationY, @homeLocationZ, @homeLookAtX, @homeLookAtY, @homeLookAtZ, @created, "; 747 command.Parameters.Add(database.CreateParameter("uuid", agentID));
570 sql += 748 command.ExecuteNonQuery();
571 "@lastLogin, @userInventoryURI, @userAssetURI, @profileCanDoMask, @profileWantDoMask, @profileAboutText, "; 749 }
572 sql += "@profileFirstText, @profileImage, @profileFirstImage, @webLoginKey);"; 750 if (data == null)
573 751 return;
574 Dictionary<string, string> parameters = new Dictionary<string, string>();
575 parameters["UUID"] = uuid.ToString();
576 parameters["username"] = username.ToString();
577 parameters["lastname"] = lastname.ToString();
578 parameters["passwordHash"] = passwordHash.ToString();
579 parameters["passwordSalt"] = passwordSalt.ToString();
580 parameters["homeRegion"] = homeRegion.ToString();
581 parameters["homeLocationX"] = homeLocX.ToString();
582 parameters["homeLocationY"] = homeLocY.ToString();
583 parameters["homeLocationZ"] = homeLocZ.ToString();
584 parameters["homeLookAtX"] = homeLookAtX.ToString();
585 parameters["homeLookAtY"] = homeLookAtY.ToString();
586 parameters["homeLookAtZ"] = homeLookAtZ.ToString();
587 parameters["created"] = created.ToString();
588 parameters["lastLogin"] = lastlogin.ToString();
589 parameters["userInventoryURI"] = String.Empty;
590 parameters["userAssetURI"] = String.Empty;
591 parameters["profileCanDoMask"] = "0";
592 parameters["profileWantDoMask"] = "0";
593 parameters["profileAboutText"] = aboutText;
594 parameters["profileFirstText"] = firstText;
595 parameters["profileImage"] = profileImage.ToString();
596 parameters["profileFirstImage"] = firstImage.ToString();
597 parameters["webLoginKey"] = UUID.Random().ToString();
598 752
753 sql = "insert into avatarattachments (UUID, attachpoint, item, asset) values (@uuid, @attachpoint, @item, @asset)";
599 754
600 try 755 using (AutoClosingSqlCommand command = database.Query(sql))
601 { 756 {
602 using (IDbCommand result = database.Query(sql, parameters)) 757 bool firstTime = true;
758 foreach (DictionaryEntry e in data)
603 { 759 {
604 return (result.ExecuteNonQuery() == 1); 760 int attachpoint = Convert.ToInt32(e.Key);
761
762 Hashtable item = (Hashtable)e.Value;
763
764 if (firstTime)
765 {
766 command.Parameters.Add(database.CreateParameter("@uuid", agentID));
767 command.Parameters.Add(database.CreateParameter("@attachpoint", attachpoint));
768 command.Parameters.Add(database.CreateParameter("@item", item["item"].ToString()));
769 command.Parameters.Add(database.CreateParameter("@asset", item["asset"].ToString()));
770 firstTime = false;
771 }
772 command.Parameters["@uuid"].Value = agentID.ToString();
773 command.Parameters["@attachpoint"].Value = attachpoint;
774 command.Parameters["@item"].Value = item["item"].ToString();
775 command.Parameters["@asset"].Value = item["asset"].ToString();
776
777 try
778 {
779 command.ExecuteNonQuery();
780 }
781 catch (Exception ex)
782 {
783 m_log.DebugFormat("[USER DB] : Error adding user attachment. {0}", ex.Message);
784 }
605 } 785 }
606 } 786 }
607 catch (Exception e)
608 {
609 m_log.Error(e.ToString());
610 return false;
611 }
612
613 } 787 }
614 788
615 /// <summary> 789 /// <summary>
616 /// Creates a new agent 790 /// Resets all attachments of a agent in the database.
617 /// </summary> 791 /// </summary>
618 /// <param name="agent">The agent to create</param> 792 /// <param name="agentID">agentID.</param>
619 override public void AddNewUserAgent(UserAgentData agent) 793 override public void ResetAttachments(UUID agentID)
620 { 794 {
621 try 795 using (AutoClosingSqlCommand command = database.Query("update avatarattachments set asset = '00000000-0000-0000-0000-000000000000' where UUID = @uuid"))
622 { 796 {
623 database.insertAgentRow(agent); 797 command.Parameters.Add(database.CreateParameter("uuid", agentID));
624 } 798 command.ExecuteNonQuery();
625 catch (Exception e)
626 {
627 m_log.Error(e.ToString());
628 } 799 }
629 } 800 }
630 801
802 #endregion
803
804 #region Other public methods
805
631 /// <summary> 806 /// <summary>
632 /// update a user profile 807 /// STUB ! Update current region
633 /// </summary> 808 /// </summary>
634 /// <param name="user">the profile to update</param> 809 /// <param name="avatarid">avatar uuid</param>
810 /// <param name="regionuuid">region uuid</param>
811 /// <param name="regionhandle">region handle</param>
812 override public void UpdateUserCurrentRegion(UUID avatarid, UUID regionuuid, ulong regionhandle)
813 {
814 //m_log.Info("[USER]: Stub UpdateUserCUrrentRegion called");
815 }
816
817 /// <summary>
818 ///
819 /// </summary>
820 /// <param name="queryID"></param>
821 /// <param name="query"></param>
635 /// <returns></returns> 822 /// <returns></returns>
636 override public bool UpdateUserProfile(UserProfileData user) 823 override public List<AvatarPickerAvatar> GeneratePickerResults(UUID queryID, string query)
637 { 824 {
638 using (IDbCommand command = database.Query("UPDATE " + m_usersTableName + " set UUID = @uuid, " + 825 List<AvatarPickerAvatar> returnlist = new List<AvatarPickerAvatar>();
639 "username = @username, " + 826 string[] querysplit = query.Split(' ');
640 "lastname = @lastname," + 827 if (querysplit.Length == 2)
641 "passwordHash = @passwordHash," + 828 {
642 "passwordSalt = @passwordSalt," +
643 "homeRegion = @homeRegion," +
644 "homeLocationX = @homeLocationX," +
645 "homeLocationY = @homeLocationY," +
646 "homeLocationZ = @homeLocationZ," +
647 "homeLookAtX = @homeLookAtX," +
648 "homeLookAtY = @homeLookAtY," +
649 "homeLookAtZ = @homeLookAtZ," +
650 "created = @created," +
651 "lastLogin = @lastLogin," +
652 "userInventoryURI = @userInventoryURI," +
653 "userAssetURI = @userAssetURI," +
654 "profileCanDoMask = @profileCanDoMask," +
655 "profileWantDoMask = @profileWantDoMask," +
656 "profileAboutText = @profileAboutText," +
657 "profileFirstText = @profileFirstText," +
658 "profileImage = @profileImage," +
659 "profileFirstImage = @profileFirstImage, " +
660 "webLoginKey = @webLoginKey where " +
661 "UUID = @keyUUUID;"))
662 {
663 SqlParameter param1 = new SqlParameter("@uuid", user.ID.ToString());
664 SqlParameter param2 = new SqlParameter("@username", user.FirstName);
665 SqlParameter param3 = new SqlParameter("@lastname", user.SurName);
666 SqlParameter param4 = new SqlParameter("@passwordHash", user.PasswordHash);
667 SqlParameter param5 = new SqlParameter("@passwordSalt", user.PasswordSalt);
668 SqlParameter param6 = new SqlParameter("@homeRegion", Convert.ToInt64(user.HomeRegion));
669 SqlParameter param7 = new SqlParameter("@homeLocationX", user.HomeLocation.X);
670 SqlParameter param8 = new SqlParameter("@homeLocationY", user.HomeLocation.Y);
671 SqlParameter param9 = new SqlParameter("@homeLocationZ", user.HomeLocation.Y);
672 SqlParameter param10 = new SqlParameter("@homeLookAtX", user.HomeLookAt.X);
673 SqlParameter param11 = new SqlParameter("@homeLookAtY", user.HomeLookAt.Y);
674 SqlParameter param12 = new SqlParameter("@homeLookAtZ", user.HomeLookAt.Z);
675 SqlParameter param13 = new SqlParameter("@created", Convert.ToInt32(user.Created));
676 SqlParameter param14 = new SqlParameter("@lastLogin", Convert.ToInt32(user.LastLogin));
677 SqlParameter param15 = new SqlParameter("@userInventoryURI", user.UserInventoryURI);
678 SqlParameter param16 = new SqlParameter("@userAssetURI", user.UserAssetURI);
679 SqlParameter param17 = new SqlParameter("@profileCanDoMask", Convert.ToInt32(user.CanDoMask));
680 SqlParameter param18 = new SqlParameter("@profileWantDoMask", Convert.ToInt32(user.WantDoMask));
681 SqlParameter param19 = new SqlParameter("@profileAboutText", user.AboutText);
682 SqlParameter param20 = new SqlParameter("@profileFirstText", user.FirstLifeAboutText);
683 SqlParameter param21 = new SqlParameter("@profileImage", user.Image.ToString());
684 SqlParameter param22 = new SqlParameter("@profileFirstImage", user.FirstLifeImage.ToString());
685 SqlParameter param23 = new SqlParameter("@keyUUUID", user.ID.ToString());
686 SqlParameter param24 = new SqlParameter("@webLoginKey", user.WebLoginKey.ToString());
687 command.Parameters.Add(param1);
688 command.Parameters.Add(param2);
689 command.Parameters.Add(param3);
690 command.Parameters.Add(param4);
691 command.Parameters.Add(param5);
692 command.Parameters.Add(param6);
693 command.Parameters.Add(param7);
694 command.Parameters.Add(param8);
695 command.Parameters.Add(param9);
696 command.Parameters.Add(param10);
697 command.Parameters.Add(param11);
698 command.Parameters.Add(param12);
699 command.Parameters.Add(param13);
700 command.Parameters.Add(param14);
701 command.Parameters.Add(param15);
702 command.Parameters.Add(param16);
703 command.Parameters.Add(param17);
704 command.Parameters.Add(param18);
705 command.Parameters.Add(param19);
706 command.Parameters.Add(param20);
707 command.Parameters.Add(param21);
708 command.Parameters.Add(param22);
709 command.Parameters.Add(param23);
710 command.Parameters.Add(param24);
711 try 829 try
712 { 830 {
713 int affected = command.ExecuteNonQuery(); 831 using (AutoClosingSqlCommand command = database.Query("SELECT UUID,username,lastname FROM " + m_usersTableName + " WHERE username LIKE @first AND lastname LIKE @second"))
714 return (affected != 0); 832 {
833 //Add wildcard to the search
834 command.Parameters.Add(database.CreateParameter("first", querysplit[0] + "%"));
835 command.Parameters.Add(database.CreateParameter("second", querysplit[1] + "%"));
836 using (IDataReader reader = command.ExecuteReader())
837 {
838 while (reader.Read())
839 {
840 AvatarPickerAvatar user = new AvatarPickerAvatar();
841 user.AvatarID = new UUID((string)reader["UUID"]);
842 user.firstName = (string)reader["username"];
843 user.lastName = (string)reader["lastname"];
844 returnlist.Add(user);
845 }
846 }
847 }
715 } 848 }
716 catch (Exception e) 849 catch (Exception e)
717 { 850 {
718 m_log.Error(e.ToString()); 851 m_log.Error(e.ToString());
719 } 852 }
720 } 853 }
721 return false; 854 else if (querysplit.Length == 1)
855 {
856 try
857 {
858 using (AutoClosingSqlCommand command = database.Query("SELECT UUID,username,lastname FROM " + m_usersTableName + " WHERE username LIKE @first OR lastname LIKE @first"))
859 {
860 command.Parameters.Add(database.CreateParameter("first", querysplit[0] + "%"));
861
862 using (IDataReader reader = command.ExecuteReader())
863 {
864 while (reader.Read())
865 {
866 AvatarPickerAvatar user = new AvatarPickerAvatar();
867 user.AvatarID = new UUID((string)reader["UUID"]);
868 user.firstName = (string)reader["username"];
869 user.lastName = (string)reader["lastname"];
870 returnlist.Add(user);
871 }
872 }
873 }
874 }
875 catch (Exception e)
876 {
877 m_log.Error(e.ToString());
878 }
879 }
880 return returnlist;
722 } 881 }
723 882
724 /// <summary> 883 /// <summary>
725 /// Performs a money transfer request between two accounts 884 /// Store a weblogin key
726 /// </summary> 885 /// </summary>
727 /// <param name="from">The senders account ID</param> 886 /// <param name="AgentID">The agent UUID</param>
728 /// <param name="to">The receivers account ID</param> 887 /// <param name="WebLoginKey">the WebLogin Key</param>
729 /// <param name="amount">The amount to transfer</param> 888 /// <remarks>unused ?</remarks>
730 /// <returns>false</returns> 889 override public void StoreWebLoginKey(UUID AgentID, UUID WebLoginKey)
731 override public bool MoneyTransferRequest(UUID from, UUID to, uint amount)
732 { 890 {
733 return false; 891 UserProfileData user = GetUserByUUID(AgentID);
892 user.WebLoginKey = WebLoginKey;
893 UpdateUserProfile(user);
894
734 } 895 }
735 896
736 /// <summary> 897 /// <summary>
737 /// Performs an inventory transfer request between two accounts 898 /// Database provider name
738 /// </summary> 899 /// </summary>
739 /// <remarks>TODO: Move to inventory server</remarks> 900 /// <returns>Provider name</returns>
740 /// <param name="from">The senders account ID</param> 901 override public string Name
741 /// <param name="to">The receivers account ID</param>
742 /// <param name="item">The item to transfer</param>
743 /// <returns>false</returns>
744 override public bool InventoryTransferRequest(UUID from, UUID to, UUID item)
745 { 902 {
746 return false; 903 get { return "MSSQL Userdata Interface"; }
747 } 904 }
748 905
749 /// Appearance 906 /// <summary>
750 /// TODO: stubs for now to get us to a compiling state gently 907 /// Database provider version
751 override public AvatarAppearance GetUserAppearance(UUID user) 908 /// </summary>
909 /// <returns>provider version</returns>
910 override public string Version
752 { 911 {
753// return new AvatarAppearance(); 912 get { return database.getVersion(); }
754 try
755 {
756 Dictionary<string, string> param = new Dictionary<string, string>();
757 param["@UUID"] = user.ToString();
758
759 using (IDbCommand result =
760 database.Query("SELECT * FROM avatarappearance WHERE owner = @UUID", param))
761 using (IDataReader reader = result.ExecuteReader())
762 {
763 AvatarAppearance item = null;
764 if (reader.Read())
765 item = readUserAppearance(reader);
766 return item;
767 }
768 }
769 catch (Exception e)
770 {
771 m_log.Error(e.ToString());
772 }
773 return null;
774 } 913 }
775 914
915 #endregion
916
917 #region Private functions
918
776 /// <summary> 919 /// <summary>
777 /// Reads a one item from an SQL result 920 /// Reads a one item from an SQL result
778 /// </summary> 921 /// </summary>
@@ -827,99 +970,254 @@ namespace OpenSim.Data.MSSQL
827 } 970 }
828 971
829 /// <summary> 972 /// <summary>
830 /// Update a user appearence into database 973 /// Insert/Update a agent row in the DB.
831 /// </summary> 974 /// </summary>
832 /// <param name="user">the used UUID</param> 975 /// <param name="agentdata">agentdata.</param>
833 /// <param name="appearance">the appearence</param> 976 private void InsertUpdateAgentRow(UserAgentData agentdata)
834 override public void UpdateUserAppearance(UUID user, AvatarAppearance appearance)
835 { 977 {
836 string sql = String.Empty; 978 string sql = @"
837 sql += "DELETE FROM avatarappearance WHERE owner=@owner "; 979
838 sql += "INSERT INTO avatarappearance "; 980IF EXISTS (SELECT * FROM agents WHERE UUID = @UUID)
839 sql += "(owner, serial, visual_params, texture, avatar_height, "; 981 BEGIN
840 sql += "body_item, body_asset, skin_item, skin_asset, hair_item, hair_asset, eyes_item, eyes_asset, "; 982 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
841 sql += "shirt_item, shirt_asset, pants_item, pants_asset, shoes_item, shoes_asset, socks_item, socks_asset, "; 983 WHERE UUID = @UUID
842 sql += "jacket_item, jacket_asset, gloves_item, gloves_asset, undershirt_item, undershirt_asset, underpants_item, underpants_asset, "; 984 END
843 sql += "skirt_item, skirt_asset) values ("; 985ELSE
844 sql += "@owner, @serial, @visual_params, @texture, @avatar_height, "; 986 BEGIN
845 sql += "@body_item, @body_asset, @skin_item, @skin_asset, @hair_item, @hair_asset, @eyes_item, @eyes_asset, "; 987 INSERT INTO
846 sql += "@shirt_item, @shirt_asset, @pants_item, @pants_asset, @shoes_item, @shoes_asset, @socks_item, @socks_asset, "; 988 agents (UUID, sessionID, secureSessionID, agentIP, agentPort, agentOnline, loginTime, logoutTime, currentRegion, currentHandle, currentPos) VALUES
847 sql += "@jacket_item, @jacket_asset, @gloves_item, @gloves_asset, @undershirt_item, @undershirt_asset, @underpants_item, @underpants_asset, "; 989 (@UUID, @sessionID, @secureSessionID, @agentIP, @agentPort, @agentOnline, @loginTime, @logoutTime, @currentRegion, @currentHandle, @currentPos)
848 sql += "@skirt_item, @skirt_asset)"; 990 END
849 991";
850 using (AutoClosingSqlCommand cmd = database.Query(sql)) 992
993 using (AutoClosingSqlCommand command = database.Query(sql))
851 { 994 {
852 cmd.Parameters.AddWithValue("@owner", appearance.Owner.ToString()); 995 command.Parameters.Add(database.CreateParameter("@UUID", agentdata.ProfileID));
853 cmd.Parameters.AddWithValue("@serial", appearance.Serial); 996 command.Parameters.Add(database.CreateParameter("@sessionID", agentdata.SessionID));
854 cmd.Parameters.AddWithValue("@visual_params", appearance.VisualParams); 997 command.Parameters.Add(database.CreateParameter("@secureSessionID", agentdata.SecureSessionID));
855 cmd.Parameters.AddWithValue("@texture", appearance.Texture.ToBytes()); 998 command.Parameters.Add(database.CreateParameter("@agentIP", agentdata.AgentIP));
856 cmd.Parameters.AddWithValue("@avatar_height", appearance.AvatarHeight); 999 command.Parameters.Add(database.CreateParameter("@agentPort", agentdata.AgentPort));
857 cmd.Parameters.AddWithValue("@body_item", appearance.BodyItem.ToString()); 1000 command.Parameters.Add(database.CreateParameter("@agentOnline", agentdata.AgentOnline));
858 cmd.Parameters.AddWithValue("@body_asset", appearance.BodyAsset.ToString()); 1001 command.Parameters.Add(database.CreateParameter("@loginTime", agentdata.LoginTime));
859 cmd.Parameters.AddWithValue("@skin_item", appearance.SkinItem.ToString()); 1002 command.Parameters.Add(database.CreateParameter("@logoutTime", agentdata.LogoutTime));
860 cmd.Parameters.AddWithValue("@skin_asset", appearance.SkinAsset.ToString()); 1003 command.Parameters.Add(database.CreateParameter("@currentRegion", agentdata.Region));
861 cmd.Parameters.AddWithValue("@hair_item", appearance.HairItem.ToString()); 1004 command.Parameters.Add(database.CreateParameter("@currentHandle", agentdata.Handle));
862 cmd.Parameters.AddWithValue("@hair_asset", appearance.HairAsset.ToString()); 1005 command.Parameters.Add(database.CreateParameter("@currentPos", "<" + ((int)agentdata.Position.X) + "," + ((int)agentdata.Position.Y) + "," + ((int)agentdata.Position.Z) + ">"));
863 cmd.Parameters.AddWithValue("@eyes_item", appearance.EyesItem.ToString()); 1006
864 cmd.Parameters.AddWithValue("@eyes_asset", appearance.EyesAsset.ToString()); 1007 command.Transaction = command.Connection.BeginTransaction(IsolationLevel.Serializable);
865 cmd.Parameters.AddWithValue("@shirt_item", appearance.ShirtItem.ToString());
866 cmd.Parameters.AddWithValue("@shirt_asset", appearance.ShirtAsset.ToString());
867 cmd.Parameters.AddWithValue("@pants_item", appearance.PantsItem.ToString());
868 cmd.Parameters.AddWithValue("@pants_asset", appearance.PantsAsset.ToString());
869 cmd.Parameters.AddWithValue("@shoes_item", appearance.ShoesItem.ToString());
870 cmd.Parameters.AddWithValue("@shoes_asset", appearance.ShoesAsset.ToString());
871 cmd.Parameters.AddWithValue("@socks_item", appearance.SocksItem.ToString());
872 cmd.Parameters.AddWithValue("@socks_asset", appearance.SocksAsset.ToString());
873 cmd.Parameters.AddWithValue("@jacket_item", appearance.JacketItem.ToString());
874 cmd.Parameters.AddWithValue("@jacket_asset", appearance.JacketAsset.ToString());
875 cmd.Parameters.AddWithValue("@gloves_item", appearance.GlovesItem.ToString());
876 cmd.Parameters.AddWithValue("@gloves_asset", appearance.GlovesAsset.ToString());
877 cmd.Parameters.AddWithValue("@undershirt_item", appearance.UnderShirtItem.ToString());
878 cmd.Parameters.AddWithValue("@undershirt_asset", appearance.UnderShirtAsset.ToString());
879 cmd.Parameters.AddWithValue("@underpants_item", appearance.UnderPantsItem.ToString());
880 cmd.Parameters.AddWithValue("@underpants_asset", appearance.UnderPantsAsset.ToString());
881 cmd.Parameters.AddWithValue("@skirt_item", appearance.SkirtItem.ToString());
882 cmd.Parameters.AddWithValue("@skirt_asset", appearance.SkirtAsset.ToString());
883
884 try 1008 try
885 { 1009 {
886 cmd.ExecuteNonQuery(); 1010 if (command.ExecuteNonQuery() > 0)
1011 {
1012 command.Transaction.Commit();
1013 return;
1014 }
1015
1016 command.Transaction.Rollback();
1017 return;
887 } 1018 }
888 catch (Exception e) 1019 catch (Exception e)
889 { 1020 {
1021 command.Transaction.Rollback();
890 m_log.Error(e.ToString()); 1022 m_log.Error(e.ToString());
1023 return;
891 } 1024 }
892 } 1025 }
1026
893 } 1027 }
894 1028
895 /// <summary> 1029 /// <summary>
896 /// Database provider name 1030 /// Reads an agent row from a database reader
897 /// </summary> 1031 /// </summary>
898 /// <returns>Provider name</returns> 1032 /// <param name="reader">An active database reader</param>
899 override public string Name 1033 /// <returns>A user session agent</returns>
1034 private UserAgentData readAgentRow(IDataReader reader)
900 { 1035 {
901 get {return "MSSQL Userdata Interface";} 1036 UserAgentData retval = new UserAgentData();
1037
1038 if (reader.Read())
1039 {
1040 // Agent IDs
1041 retval.ProfileID = new UUID((string)reader["UUID"]);
1042 retval.SessionID = new UUID((string)reader["sessionID"]);
1043 retval.SecureSessionID = new UUID((string)reader["secureSessionID"]);
1044
1045 // Agent Who?
1046 retval.AgentIP = (string)reader["agentIP"];
1047 retval.AgentPort = Convert.ToUInt32(reader["agentPort"].ToString());
1048 retval.AgentOnline = Convert.ToInt32(reader["agentOnline"].ToString()) != 0;
1049
1050 // Login/Logout times (UNIX Epoch)
1051 retval.LoginTime = Convert.ToInt32(reader["loginTime"].ToString());
1052 retval.LogoutTime = Convert.ToInt32(reader["logoutTime"].ToString());
1053
1054 // Current position
1055 retval.Region = (string)reader["currentRegion"];
1056 retval.Handle = Convert.ToUInt64(reader["currentHandle"].ToString());
1057 Vector3 tmp_v;
1058 Vector3.TryParse((string)reader["currentPos"], out tmp_v);
1059 retval.Position = tmp_v;
1060
1061 }
1062 else
1063 {
1064 return null;
1065 }
1066 return retval;
902 } 1067 }
903 1068
904 /// <summary> 1069 /// <summary>
905 /// Database provider version 1070 /// Creates a new user and inserts it into the database
906 /// </summary> 1071 /// </summary>
907 /// <returns>provider version</returns> 1072 /// <param name="uuid">User ID</param>
908 override public string Version 1073 /// <param name="username">First part of the login</param>
1074 /// <param name="lastname">Second part of the login</param>
1075 /// <param name="passwordHash">A salted hash of the users password</param>
1076 /// <param name="passwordSalt">The salt used for the password hash</param>
1077 /// <param name="homeRegion">A regionHandle of the users home region</param>
1078 /// <param name="homeLocX">Home region position vector</param>
1079 /// <param name="homeLocY">Home region position vector</param>
1080 /// <param name="homeLocZ">Home region position vector</param>
1081 /// <param name="homeLookAtX">Home region 'look at' vector</param>
1082 /// <param name="homeLookAtY">Home region 'look at' vector</param>
1083 /// <param name="homeLookAtZ">Home region 'look at' vector</param>
1084 /// <param name="created">Account created (unix timestamp)</param>
1085 /// <param name="lastlogin">Last login (unix timestamp)</param>
1086 /// <param name="inventoryURI">Users inventory URI</param>
1087 /// <param name="assetURI">Users asset URI</param>
1088 /// <param name="canDoMask">I can do mask</param>
1089 /// <param name="wantDoMask">I want to do mask</param>
1090 /// <param name="aboutText">Profile text</param>
1091 /// <param name="firstText">Firstlife text</param>
1092 /// <param name="profileImage">UUID for profile image</param>
1093 /// <param name="firstImage">UUID for firstlife image</param>
1094 /// <param name="webLoginKey">web login key</param>
1095 /// <param name="homeRegionID">homeregion UUID</param>
1096 /// <param name="godLevel">has the user godlevel</param>
1097 /// <param name="userFlags">unknown</param>
1098 /// <param name="customType">unknown</param>
1099 /// <param name="partnerID">UUID of partner</param>
1100 /// <returns>Success?</returns>
1101 private void InsertUserRow(UUID uuid, string username, string lastname, string passwordHash,
1102 string passwordSalt, UInt64 homeRegion, float homeLocX, float homeLocY, float homeLocZ,
1103 float homeLookAtX, float homeLookAtY, float homeLookAtZ, int created, int lastlogin,
1104 string inventoryURI, string assetURI, uint canDoMask, uint wantDoMask,
1105 string aboutText, string firstText,
1106 UUID profileImage, UUID firstImage, UUID webLoginKey, UUID homeRegionID,
1107 int godLevel, int userFlags, string customType, UUID partnerID)
909 { 1108 {
910 get {return database.getVersion();} 1109 string sql = "INSERT INTO " + m_usersTableName;
1110 sql += " ([UUID], [username], [lastname], [passwordHash], [passwordSalt], [homeRegion], ";
1111 sql += "[homeLocationX], [homeLocationY], [homeLocationZ], [homeLookAtX], [homeLookAtY], [homeLookAtZ], [created], ";
1112 sql += "[lastLogin], [userInventoryURI], [userAssetURI], [profileCanDoMask], [profileWantDoMask], [profileAboutText], ";
1113 sql += "[profileFirstText], [profileImage], [profileFirstImage], [webLoginKey], ";
1114 sql += "[homeRegionID], [userFlags], [godLevel], [customType], [partner]) VALUES ";
1115
1116 sql += "(@UUID, @username, @lastname, @passwordHash, @passwordSalt, @homeRegion, ";
1117 sql += "@homeLocationX, @homeLocationY, @homeLocationZ, @homeLookAtX, @homeLookAtY, @homeLookAtZ, @created, ";
1118 sql += "@lastLogin, @userInventoryURI, @userAssetURI, @profileCanDoMask, @profileWantDoMask, @profileAboutText, ";
1119 sql += "@profileFirstText, @profileImage, @profileFirstImage, @webLoginKey, ";
1120 sql += "@homeRegionID, @userFlags, @godLevel, @customType, @partner)";
1121
1122 try
1123 {
1124 using (AutoClosingSqlCommand command = database.Query(sql))
1125 {
1126 command.Parameters.Add(database.CreateParameter("UUID", uuid));
1127 command.Parameters.Add(database.CreateParameter("username", username));
1128 command.Parameters.Add(database.CreateParameter("lastname", lastname));
1129 command.Parameters.Add(database.CreateParameter("passwordHash", passwordHash));
1130 command.Parameters.Add(database.CreateParameter("passwordSalt", passwordSalt));
1131 command.Parameters.Add(database.CreateParameter("homeRegion", homeRegion));
1132 command.Parameters.Add(database.CreateParameter("homeLocationX", homeLocX));
1133 command.Parameters.Add(database.CreateParameter("homeLocationY", homeLocY));
1134 command.Parameters.Add(database.CreateParameter("homeLocationZ", homeLocZ));
1135 command.Parameters.Add(database.CreateParameter("homeLookAtX", homeLookAtX));
1136 command.Parameters.Add(database.CreateParameter("homeLookAtY", homeLookAtY));
1137 command.Parameters.Add(database.CreateParameter("homeLookAtZ", homeLookAtZ));
1138 command.Parameters.Add(database.CreateParameter("created", created));
1139 command.Parameters.Add(database.CreateParameter("lastLogin", lastlogin));
1140 command.Parameters.Add(database.CreateParameter("userInventoryURI", inventoryURI));
1141 command.Parameters.Add(database.CreateParameter("userAssetURI", assetURI));
1142 command.Parameters.Add(database.CreateParameter("profileCanDoMask", canDoMask));
1143 command.Parameters.Add(database.CreateParameter("profileWantDoMask", wantDoMask));
1144 command.Parameters.Add(database.CreateParameter("profileAboutText", aboutText));
1145 command.Parameters.Add(database.CreateParameter("profileFirstText", firstText));
1146 command.Parameters.Add(database.CreateParameter("profileImage", profileImage));
1147 command.Parameters.Add(database.CreateParameter("profileFirstImage", firstImage));
1148 command.Parameters.Add(database.CreateParameter("webLoginKey", webLoginKey));
1149 //
1150 command.Parameters.Add(database.CreateParameter("homeRegionID", homeRegionID));
1151 command.Parameters.Add(database.CreateParameter("userFlags", userFlags));
1152 command.Parameters.Add(database.CreateParameter("godLevel", godLevel));
1153 command.Parameters.Add(database.CreateParameter("customType", customType));
1154 command.Parameters.Add(database.CreateParameter("partner", partnerID));
1155
1156
1157 command.ExecuteNonQuery();
1158 return;
1159 }
1160 }
1161 catch (Exception e)
1162 {
1163 m_log.Error(e.ToString());
1164 return;
1165 }
1166
911 } 1167 }
912 1168
913 /// <summary> 1169 /// <summary>
914 /// Not implemented 1170 /// Reads a user profile from an active data reader
915 /// </summary> 1171 /// </summary>
916 /// <param name="query"></param> 1172 /// <param name="reader">An active database reader</param>
917 public void runQuery(string query) 1173 /// <returns>A user profile</returns>
1174 private static UserProfileData ReadUserRow(IDataReader reader)
918 { 1175 {
919 } 1176 UserProfileData retval = new UserProfileData();
920 1177
921 override public void ResetAttachments(UUID userID) 1178 if (reader.Read())
922 { 1179 {
1180 retval.ID = new UUID((string)reader["UUID"]);
1181 retval.FirstName = (string)reader["username"];
1182 retval.SurName = (string)reader["lastname"];
1183
1184 retval.PasswordHash = (string)reader["passwordHash"];
1185 retval.PasswordSalt = (string)reader["passwordSalt"];
1186
1187 retval.HomeRegion = Convert.ToUInt64(reader["homeRegion"].ToString());
1188 retval.HomeLocation = new Vector3(
1189 Convert.ToSingle(reader["homeLocationX"].ToString()),
1190 Convert.ToSingle(reader["homeLocationY"].ToString()),
1191 Convert.ToSingle(reader["homeLocationZ"].ToString()));
1192 retval.HomeLookAt = new Vector3(
1193 Convert.ToSingle(reader["homeLookAtX"].ToString()),
1194 Convert.ToSingle(reader["homeLookAtY"].ToString()),
1195 Convert.ToSingle(reader["homeLookAtZ"].ToString()));
1196
1197 retval.Created = Convert.ToInt32(reader["created"].ToString());
1198 retval.LastLogin = Convert.ToInt32(reader["lastLogin"].ToString());
1199
1200 retval.UserInventoryURI = (string)reader["userInventoryURI"];
1201 retval.UserAssetURI = (string)reader["userAssetURI"];
1202
1203 retval.CanDoMask = Convert.ToUInt32(reader["profileCanDoMask"].ToString());
1204 retval.WantDoMask = Convert.ToUInt32(reader["profileWantDoMask"].ToString());
1205
1206 retval.AboutText = (string)reader["profileAboutText"];
1207 retval.FirstLifeAboutText = (string)reader["profileFirstText"];
1208
1209 retval.Image = new UUID((string)reader["profileImage"]);
1210 retval.FirstLifeImage = new UUID((string)reader["profileFirstImage"]);
1211 retval.WebLoginKey = new UUID((string)reader["webLoginKey"]);
1212 }
1213 else
1214 {
1215 return null;
1216 }
1217 return retval;
923 } 1218 }
1219
1220 #endregion
924 } 1221 }
1222
925} 1223}
diff --git a/OpenSim/Data/MSSQL/Resources/001_AssetStore.sql b/OpenSim/Data/MSSQL/Resources/001_AssetStore.sql
new file mode 100644
index 0000000..2b293c7
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/001_AssetStore.sql
@@ -0,0 +1,13 @@
1CREATE TABLE [assets] (
2 [id] [varchar](36) NOT NULL,
3 [name] [varchar](64) NOT NULL,
4 [description] [varchar](64) NOT NULL,
5 [assetType] [tinyint] NOT NULL,
6 [local] [tinyint] NOT NULL,
7 [temporary] [tinyint] NOT NULL,
8 [data] [image] NOT NULL,
9PRIMARY KEY CLUSTERED
10(
11 [id] ASC
12)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
13) ON [PRIMARY]
diff --git a/OpenSim/Data/MSSQL/Resources/001_GridStore.sql b/OpenSim/Data/MSSQL/Resources/001_GridStore.sql
new file mode 100644
index 0000000..ff15f54
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/001_GridStore.sql
@@ -0,0 +1,37 @@
1BEGIN TRANSACTION
2
3CREATE TABLE [dbo].[regions](
4 [regionHandle] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
5 [regionName] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
6 [uuid] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
7 [regionRecvKey] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
8 [regionSecret] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
9 [regionSendKey] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
10 [regionDataURI] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
11 [serverIP] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
12 [serverPort] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
13 [serverURI] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
14 [locX] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
15 [locY] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
16 [locZ] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
17 [eastOverrideHandle] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
18 [westOverrideHandle] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
19 [southOverrideHandle] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
20 [northOverrideHandle] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
21 [regionAssetURI] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
22 [regionAssetRecvKey] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
23 [regionAssetSendKey] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
24 [regionUserURI] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
25 [regionUserRecvKey] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
26 [regionUserSendKey] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
27 [regionMapTexture] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
28 [serverHttpPort] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
29 [serverRemotingPort] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
30 [owner_uuid] [varchar](36) COLLATE Latin1_General_CI_AS NULL,
31PRIMARY KEY CLUSTERED
32(
33 [uuid] ASC
34)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
35) ON [PRIMARY]
36
37COMMIT
diff --git a/OpenSim/Data/MSSQL/Resources/001_InventoryStore.sql b/OpenSim/Data/MSSQL/Resources/001_InventoryStore.sql
new file mode 100644
index 0000000..836d2d1
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/001_InventoryStore.sql
@@ -0,0 +1,64 @@
1BEGIN TRANSACTION
2
3CREATE TABLE [inventoryfolders] (
4 [folderID] [varchar](36) NOT NULL default '',
5 [agentID] [varchar](36) default NULL,
6 [parentFolderID] [varchar](36) default NULL,
7 [folderName] [varchar](64) default NULL,
8 [type] [smallint] NOT NULL default 0,
9 [version] [int] NOT NULL default 0,
10 PRIMARY KEY CLUSTERED
11(
12 [folderID] 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]
15
16CREATE NONCLUSTERED INDEX [owner] ON [inventoryfolders]
17(
18 [agentID] ASC
19)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
20
21CREATE NONCLUSTERED INDEX [parent] ON [inventoryfolders]
22(
23 [parentFolderID] ASC
24)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
25
26
27CREATE TABLE [inventoryitems] (
28 [inventoryID] [varchar](36) NOT NULL default '',
29 [assetID] [varchar](36) default NULL,
30 [assetType] [int] default NULL,
31 [parentFolderID] [varchar](36) default NULL,
32 [avatarID] [varchar](36) default NULL,
33 [inventoryName] [varchar](64) default NULL,
34 [inventoryDescription] [varchar](128) default NULL,
35 [inventoryNextPermissions] [int] default NULL,
36 [inventoryCurrentPermissions] [int] default NULL,
37 [invType] [int] default NULL,
38 [creatorID] [varchar](36) default NULL,
39 [inventoryBasePermissions] [int] NOT NULL default 0,
40 [inventoryEveryOnePermissions] [int] NOT NULL default 0,
41 [salePrice] [int] default NULL,
42 [saleType] [tinyint] default NULL,
43 [creationDate] [int] default NULL,
44 [groupID] [varchar](36) default NULL,
45 [groupOwned] [bit] default NULL,
46 [flags] [int] default NULL,
47 PRIMARY KEY CLUSTERED
48(
49 [inventoryID] ASC
50)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
51) ON [PRIMARY]
52
53
54CREATE NONCLUSTERED INDEX [owner] ON [inventoryitems]
55(
56 [avatarID] ASC
57)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
58
59CREATE NONCLUSTERED INDEX [folder] ON [inventoryitems]
60(
61 [parentFolderID] ASC
62)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
63
64COMMIT
diff --git a/OpenSim/Data/MSSQL/Resources/001_UserStore.sql b/OpenSim/Data/MSSQL/Resources/001_UserStore.sql
new file mode 100644
index 0000000..abd6ff2
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/001_UserStore.sql
@@ -0,0 +1,224 @@
1CREATE TABLE [users] (
2 [UUID] [varchar](36) NOT NULL default '',
3 [username] [varchar](32) NOT NULL,
4 [lastname] [varchar](32) NOT NULL,
5 [passwordHash] [varchar](32) NOT NULL,
6 [passwordSalt] [varchar](32) NOT NULL,
7 [homeRegion] [bigint] default NULL,
8 [homeLocationX] [float] default NULL,
9 [homeLocationY] [float] default NULL,
10 [homeLocationZ] [float] default NULL,
11 [homeLookAtX] [float] default NULL,
12 [homeLookAtY] [float] default NULL,
13 [homeLookAtZ] [float] default NULL,
14 [created] [int] NOT NULL,
15 [lastLogin] [int] NOT NULL,
16 [userInventoryURI] [varchar](255) default NULL,
17 [userAssetURI] [varchar](255) default NULL,
18 [profileCanDoMask] [int] default NULL,
19 [profileWantDoMask] [int] default NULL,
20 [profileAboutText] [ntext],
21 [profileFirstText] [ntext],
22 [profileImage] [varchar](36) default NULL,
23 [profileFirstImage] [varchar](36) default NULL,
24 [webLoginKey] [varchar](36) default NULL,
25 PRIMARY KEY CLUSTERED
26(
27 [UUID] ASC
28)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
29) ON [PRIMARY]
30
31
32CREATE NONCLUSTERED INDEX [usernames] ON [users]
33(
34 [username] ASC,
35 [lastname] ASC
36)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
37
38
39CREATE TABLE [agents] (
40 [UUID] [varchar](36) NOT NULL,
41 [sessionID] [varchar](36) NOT NULL,
42 [secureSessionID] [varchar](36) NOT NULL,
43 [agentIP] [varchar](16) NOT NULL,
44 [agentPort] [int] NOT NULL,
45 [agentOnline] [tinyint] NOT NULL,
46 [loginTime] [int] NOT NULL,
47 [logoutTime] [int] NOT NULL,
48 [currentRegion] [varchar](36) NOT NULL,
49 [currentHandle] [bigint] NOT NULL,
50 [currentPos] [varchar](64) NOT NULL,
51 PRIMARY KEY CLUSTERED
52(
53 [UUID] ASC
54)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
55) ON [PRIMARY]
56
57
58CREATE NONCLUSTERED INDEX [session] ON [agents]
59(
60 [sessionID] ASC
61)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
62
63CREATE NONCLUSTERED INDEX [ssession] ON [agents]
64(
65 [secureSessionID] ASC
66)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
67
68
69CREATE TABLE [dbo].[userfriends](
70 [ownerID] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
71 [friendID] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
72 [friendPerms] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
73 [datetimestamp] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
74) ON [PRIMARY]
75
76CREATE TABLE [avatarappearance] (
77 [Owner] [varchar](36) NOT NULL,
78 [Serial] int NOT NULL,
79 [Visual_Params] [image] NOT NULL,
80 [Texture] [image] NOT NULL,
81 [Avatar_Height] float NOT NULL,
82 [Body_Item] [varchar](36) NOT NULL,
83 [Body_Asset] [varchar](36) NOT NULL,
84 [Skin_Item] [varchar](36) NOT NULL,
85 [Skin_Asset] [varchar](36) NOT NULL,
86 [Hair_Item] [varchar](36) NOT NULL,
87 [Hair_Asset] [varchar](36) NOT NULL,
88 [Eyes_Item] [varchar](36) NOT NULL,
89 [Eyes_Asset] [varchar](36) NOT NULL,
90 [Shirt_Item] [varchar](36) NOT NULL,
91 [Shirt_Asset] [varchar](36) NOT NULL,
92 [Pants_Item] [varchar](36) NOT NULL,
93 [Pants_Asset] [varchar](36) NOT NULL,
94 [Shoes_Item] [varchar](36) NOT NULL,
95 [Shoes_Asset] [varchar](36) NOT NULL,
96 [Socks_Item] [varchar](36) NOT NULL,
97 [Socks_Asset] [varchar](36) NOT NULL,
98 [Jacket_Item] [varchar](36) NOT NULL,
99 [Jacket_Asset] [varchar](36) NOT NULL,
100 [Gloves_Item] [varchar](36) NOT NULL,
101 [Gloves_Asset] [varchar](36) NOT NULL,
102 [Undershirt_Item] [varchar](36) NOT NULL,
103 [Undershirt_Asset] [varchar](36) NOT NULL,
104 [Underpants_Item] [varchar](36) NOT NULL,
105 [Underpants_Asset] [varchar](36) NOT NULL,
106 [Skirt_Item] [varchar](36) NOT NULL,
107 [Skirt_Asset] [varchar](36) NOT NULL,
108
109 PRIMARY KEY CLUSTERED (
110 [Owner]
111 ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
112) ON [PRIMARY]
113CREATE TABLE [users] (
114 [UUID] [varchar](36) NOT NULL default '',
115 [username] [varchar](32) NOT NULL,
116 [lastname] [varchar](32) NOT NULL,
117 [passwordHash] [varchar](32) NOT NULL,
118 [passwordSalt] [varchar](32) NOT NULL,
119 [homeRegion] [bigint] default NULL,
120 [homeLocationX] [float] default NULL,
121 [homeLocationY] [float] default NULL,
122 [homeLocationZ] [float] default NULL,
123 [homeLookAtX] [float] default NULL,
124 [homeLookAtY] [float] default NULL,
125 [homeLookAtZ] [float] default NULL,
126 [created] [int] NOT NULL,
127 [lastLogin] [int] NOT NULL,
128 [userInventoryURI] [varchar](255) default NULL,
129 [userAssetURI] [varchar](255) default NULL,
130 [profileCanDoMask] [int] default NULL,
131 [profileWantDoMask] [int] default NULL,
132 [profileAboutText] [ntext],
133 [profileFirstText] [ntext],
134 [profileImage] [varchar](36) default NULL,
135 [profileFirstImage] [varchar](36) default NULL,
136 [webLoginKey] [varchar](36) default NULL,
137 PRIMARY KEY CLUSTERED
138(
139 [UUID] ASC
140)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
141) ON [PRIMARY]
142
143
144CREATE NONCLUSTERED INDEX [usernames] ON [users]
145(
146 [username] ASC,
147 [lastname] ASC
148)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
149
150
151CREATE TABLE [agents] (
152 [UUID] [varchar](36) NOT NULL,
153 [sessionID] [varchar](36) NOT NULL,
154 [secureSessionID] [varchar](36) NOT NULL,
155 [agentIP] [varchar](16) NOT NULL,
156 [agentPort] [int] NOT NULL,
157 [agentOnline] [tinyint] NOT NULL,
158 [loginTime] [int] NOT NULL,
159 [logoutTime] [int] NOT NULL,
160 [currentRegion] [varchar](36) NOT NULL,
161 [currentHandle] [bigint] NOT NULL,
162 [currentPos] [varchar](64) NOT NULL,
163 PRIMARY KEY CLUSTERED
164(
165 [UUID] ASC
166)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
167) ON [PRIMARY]
168
169
170CREATE NONCLUSTERED INDEX [session] ON [agents]
171(
172 [sessionID] ASC
173)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
174
175CREATE NONCLUSTERED INDEX [ssession] ON [agents]
176(
177 [secureSessionID] ASC
178)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
179
180
181CREATE TABLE [dbo].[userfriends](
182 [ownerID] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
183 [friendID] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
184 [friendPerms] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
185 [datetimestamp] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
186) ON [PRIMARY]
187
188CREATE TABLE [avatarappearance] (
189 [Owner] [varchar](36) NOT NULL,
190 [Serial] int NOT NULL,
191 [Visual_Params] [image] NOT NULL,
192 [Texture] [image] NOT NULL,
193 [Avatar_Height] float NOT NULL,
194 [Body_Item] [varchar](36) NOT NULL,
195 [Body_Asset] [varchar](36) NOT NULL,
196 [Skin_Item] [varchar](36) NOT NULL,
197 [Skin_Asset] [varchar](36) NOT NULL,
198 [Hair_Item] [varchar](36) NOT NULL,
199 [Hair_Asset] [varchar](36) NOT NULL,
200 [Eyes_Item] [varchar](36) NOT NULL,
201 [Eyes_Asset] [varchar](36) NOT NULL,
202 [Shirt_Item] [varchar](36) NOT NULL,
203 [Shirt_Asset] [varchar](36) NOT NULL,
204 [Pants_Item] [varchar](36) NOT NULL,
205 [Pants_Asset] [varchar](36) NOT NULL,
206 [Shoes_Item] [varchar](36) NOT NULL,
207 [Shoes_Asset] [varchar](36) NOT NULL,
208 [Socks_Item] [varchar](36) NOT NULL,
209 [Socks_Asset] [varchar](36) NOT NULL,
210 [Jacket_Item] [varchar](36) NOT NULL,
211 [Jacket_Asset] [varchar](36) NOT NULL,
212 [Gloves_Item] [varchar](36) NOT NULL,
213 [Gloves_Asset] [varchar](36) NOT NULL,
214 [Undershirt_Item] [varchar](36) NOT NULL,
215 [Undershirt_Asset] [varchar](36) NOT NULL,
216 [Underpants_Item] [varchar](36) NOT NULL,
217 [Underpants_Asset] [varchar](36) NOT NULL,
218 [Skirt_Item] [varchar](36) NOT NULL,
219 [Skirt_Asset] [varchar](36) NOT NULL,
220
221 PRIMARY KEY CLUSTERED (
222 [Owner]
223 ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
224) ON [PRIMARY]
diff --git a/OpenSim/Data/MSSQL/Resources/002_AssetStore.sql b/OpenSim/Data/MSSQL/Resources/002_AssetStore.sql
new file mode 100644
index 0000000..3e24543
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/002_AssetStore.sql
@@ -0,0 +1,29 @@
1BEGIN TRANSACTION
2
3CREATE TABLE Tmp_assets
4 (
5 id varchar(36) NOT NULL,
6 name varchar(64) NOT NULL,
7 description varchar(64) NOT NULL,
8 assetType tinyint NOT NULL,
9 local bit NOT NULL,
10 temporary bit NOT NULL,
11 data image NOT NULL
12 ) ON [PRIMARY]
13 TEXTIMAGE_ON [PRIMARY]
14
15IF EXISTS(SELECT * FROM assets)
16 EXEC('INSERT INTO Tmp_assets (id, name, description, assetType, local, temporary, data)
17 SELECT id, name, description, assetType, CONVERT(bit, local), CONVERT(bit, temporary), data FROM assets WITH (HOLDLOCK TABLOCKX)')
18
19DROP TABLE assets
20
21EXECUTE sp_rename N'Tmp_assets', N'assets', 'OBJECT'
22
23ALTER TABLE dbo.assets ADD CONSTRAINT
24 PK__assets__id PRIMARY KEY CLUSTERED
25 (
26 id
27 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
28
29COMMIT
diff --git a/OpenSim/Data/MSSQL/Resources/002_UserStore.sql b/OpenSim/Data/MSSQL/Resources/002_UserStore.sql
new file mode 100644
index 0000000..a3b5868
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/002_UserStore.sql
@@ -0,0 +1,18 @@
1BEGIN TRANSACTION
2
3ALTER TABLE users ADD homeRegionID varchar(36) NOT NULL default '00000000-0000-0000-0000-000000000000';
4ALTER TABLE users ADD userFlags int NOT NULL default 0;
5ALTER TABLE users ADD godLevel int NOT NULL default 0;
6ALTER TABLE users ADD customType varchar(32) not null default '';
7ALTER TABLE users ADD partner varchar(36) not null default '00000000-0000-0000-0000-000000000000';
8
9COMMIT
10BEGIN TRANSACTION
11
12ALTER TABLE users ADD homeRegionID varchar(36) NOT NULL default '00000000-0000-0000-0000-000000000000';
13ALTER TABLE users ADD userFlags int NOT NULL default 0;
14ALTER TABLE users ADD godLevel int NOT NULL default 0;
15ALTER TABLE users ADD customType varchar(32) not null default '';
16ALTER TABLE users ADD partner varchar(36) not null default '00000000-0000-0000-0000-000000000000';
17
18COMMIT
diff --git a/OpenSim/Data/MSSQL/Resources/003_GridStore.sql b/OpenSim/Data/MSSQL/Resources/003_GridStore.sql
new file mode 100644
index 0000000..e080947
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/003_GridStore.sql
@@ -0,0 +1,22 @@
1BEGIN TRANSACTION
2
3CREATE NONCLUSTERED INDEX IX_regions_name ON dbo.regions
4 (
5 regionName
6 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
7
8CREATE NONCLUSTERED INDEX IX_regions_handle ON dbo.regions
9 (
10 regionHandle
11 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
12
13
14CREATE NONCLUSTERED INDEX IX_regions_override ON dbo.regions
15 (
16 eastOverrideHandle,
17 westOverrideHandle,
18 southOverrideHandle,
19 northOverrideHandle
20 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
21
22COMMIT
diff --git a/OpenSim/Data/MSSQL/Resources/003_UserStore.sql b/OpenSim/Data/MSSQL/Resources/003_UserStore.sql
new file mode 100644
index 0000000..c2821e6
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/003_UserStore.sql
@@ -0,0 +1,30 @@
1BEGIN TRANSACTION
2
3CREATE TABLE [avatarattachments] (
4 [UUID] varchar(36) NOT NULL
5 , [attachpoint] int NOT NULL
6 , [item] varchar(36) NOT NULL
7 , [asset] varchar(36) NOT NULL)
8
9CREATE NONCLUSTERED INDEX IX_avatarattachments ON dbo.avatarattachments
10 (
11 UUID
12 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
13
14
15COMMIT
16BEGIN TRANSACTION
17
18CREATE TABLE [avatarattachments] (
19 [UUID] varchar(36) NOT NULL
20 , [attachpoint] int NOT NULL
21 , [item] varchar(36) NOT NULL
22 , [asset] varchar(36) NOT NULL)
23
24CREATE NONCLUSTERED INDEX IX_avatarattachments ON dbo.avatarattachments
25 (
26 UUID
27 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
28
29
30COMMIT
diff --git a/OpenSim/Data/MSSQL/Resources/004_RegionStore.sql b/OpenSim/Data/MSSQL/Resources/004_RegionStore.sql
index 4e64901..15b39a7 100644
--- a/OpenSim/Data/MSSQL/Resources/004_RegionStore.sql
+++ b/OpenSim/Data/MSSQL/Resources/004_RegionStore.sql
@@ -1,6 +1,6 @@
1BEGIN TRANSACTION 1BEGIN TRANSACTION
2 2
3CREATE TABLE dbo.Tmp_primitems 3CREATE TABLE Tmp_primitems
4 ( 4 (
5 itemID varchar(36) NOT NULL, 5 itemID varchar(36) NOT NULL,
6 primID varchar(36) NULL, 6 primID varchar(36) NULL,
@@ -22,15 +22,15 @@ CREATE TABLE dbo.Tmp_primitems
22 groupPermissions int NULL 22 groupPermissions int NULL
23 ) ON [PRIMARY] 23 ) ON [PRIMARY]
24 24
25IF EXISTS(SELECT * FROM dbo.primitems) 25IF EXISTS(SELECT * FROM primitems)
26 EXEC('INSERT INTO dbo.Tmp_primitems (itemID, primID, assetID, parentFolderID, invType, assetType, name, description, creationDate, creatorID, ownerID, lastOwnerID, groupID, nextPermissions, currentPermissions, basePermissions, everyonePermissions, groupPermissions) 26 EXEC('INSERT INTO Tmp_primitems (itemID, primID, assetID, parentFolderID, invType, assetType, name, description, creationDate, creatorID, ownerID, lastOwnerID, groupID, nextPermissions, currentPermissions, basePermissions, everyonePermissions, groupPermissions)
27 SELECT CONVERT(varchar(36), itemID), CONVERT(varchar(36), primID), CONVERT(varchar(36), assetID), CONVERT(varchar(36), parentFolderID), invType, assetType, name, description, creationDate, CONVERT(varchar(36), creatorID), CONVERT(varchar(36), ownerID), CONVERT(varchar(36), lastOwnerID), CONVERT(varchar(36), groupID), nextPermissions, currentPermissions, basePermissions, everyonePermissions, groupPermissions') 27 SELECT CONVERT(varchar(36), itemID), CONVERT(varchar(36), primID), CONVERT(varchar(36), assetID), CONVERT(varchar(36), parentFolderID), invType, assetType, name, description, creationDate, CONVERT(varchar(36), creatorID), CONVERT(varchar(36), ownerID), CONVERT(varchar(36), lastOwnerID), CONVERT(varchar(36), groupID), nextPermissions, currentPermissions, basePermissions, everyonePermissions, groupPermissions')
28 28
29DROP TABLE dbo.primitems 29DROP TABLE primitems
30 30
31EXECUTE sp_rename N'dbo.Tmp_primitems', N'primitems', 'OBJECT' 31EXECUTE sp_rename N'Tmp_primitems', N'primitems', 'OBJECT'
32 32
33ALTER TABLE dbo.primitems ADD CONSTRAINT 33ALTER TABLE primitems ADD CONSTRAINT
34 PK__primitems__0A688BB1 PRIMARY KEY CLUSTERED 34 PK__primitems__0A688BB1 PRIMARY KEY CLUSTERED
35 ( 35 (
36 itemID 36 itemID
diff --git a/OpenSim/Data/MSSQL/Resources/004_UserStore.sql b/OpenSim/Data/MSSQL/Resources/004_UserStore.sql
new file mode 100644
index 0000000..f3e223e
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/004_UserStore.sql
@@ -0,0 +1,58 @@
1BEGIN TRANSACTION
2
3CREATE TABLE Tmp_userfriends
4 (
5 ownerID varchar(36) NOT NULL,
6 friendID varchar(36) NOT NULL,
7 friendPerms int NOT NULL,
8 datetimestamp int NOT NULL
9 ) ON [PRIMARY]
10
11IF EXISTS(SELECT * FROM userfriends)
12 EXEC('INSERT INTO dbo.Tmp_userfriends (ownerID, friendID, friendPerms, datetimestamp)
13 SELECT CONVERT(varchar(36), ownerID), CONVERT(varchar(36), friendID), CONVERT(int, friendPerms), CONVERT(int, datetimestamp) FROM dbo.userfriends WITH (HOLDLOCK TABLOCKX)')
14
15DROP TABLE dbo.userfriends
16
17EXECUTE sp_rename N'Tmp_userfriends', N'userfriends', 'OBJECT'
18
19CREATE NONCLUSTERED INDEX IX_userfriends_ownerID ON userfriends
20 (
21 ownerID
22 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
23
24CREATE NONCLUSTERED INDEX IX_userfriends_friendID ON userfriends
25 (
26 friendID
27 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
28
29COMMIT
30BEGIN TRANSACTION
31
32CREATE TABLE Tmp_userfriends
33 (
34 ownerID varchar(36) NOT NULL,
35 friendID varchar(36) NOT NULL,
36 friendPerms int NOT NULL,
37 datetimestamp int NOT NULL
38 ) ON [PRIMARY]
39
40IF EXISTS(SELECT * FROM dbo.userfriends)
41 EXEC('INSERT INTO dbo.Tmp_userfriends (ownerID, friendID, friendPerms, datetimestamp)
42 SELECT CONVERT(varchar(36), ownerID), CONVERT(varchar(36), friendID), CONVERT(int, friendPerms), CONVERT(int, datetimestamp) FROM dbo.userfriends WITH (HOLDLOCK TABLOCKX)')
43
44DROP TABLE dbo.userfriends
45
46EXECUTE sp_rename N'Tmp_userfriends', N'userfriends', 'OBJECT'
47
48CREATE NONCLUSTERED INDEX IX_userfriends_ownerID ON userfriends
49 (
50 ownerID
51 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
52
53CREATE NONCLUSTERED INDEX IX_userfriends_friendID ON userfriends
54 (
55 friendID
56 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
57
58COMMIT
diff --git a/OpenSim/Data/MSSQL/Resources/005_RegionStore.sql b/OpenSim/Data/MSSQL/Resources/005_RegionStore.sql
index 74e9d85..eb0862c 100644
--- a/OpenSim/Data/MSSQL/Resources/005_RegionStore.sql
+++ b/OpenSim/Data/MSSQL/Resources/005_RegionStore.sql
@@ -1,6 +1,6 @@
1BEGIN TRANSACTION 1BEGIN TRANSACTION
2 2
3CREATE TABLE dbo.Tmp_primshapes 3CREATE TABLE Tmp_primshapes
4 ( 4 (
5 UUID varchar(36) NOT NULL, 5 UUID varchar(36) NOT NULL,
6 Shape int NULL, 6 Shape int NULL,
@@ -32,18 +32,18 @@ CREATE TABLE dbo.Tmp_primshapes
32 ) ON [PRIMARY] 32 ) ON [PRIMARY]
33 TEXTIMAGE_ON [PRIMARY] 33 TEXTIMAGE_ON [PRIMARY]
34 34
35IF EXISTS(SELECT * FROM dbo.primshapes) 35IF EXISTS(SELECT * FROM primshapes)
36 EXEC('INSERT INTO dbo.Tmp_primshapes (UUID, Shape, ScaleX, ScaleY, ScaleZ, PCode, PathBegin, PathEnd, PathScaleX, PathScaleY, PathShearX, PathShearY, PathSkew, PathCurve, PathRadiusOffset, PathRevolutions, PathTaperX, PathTaperY, PathTwist, PathTwistBegin, ProfileBegin, ProfileEnd, ProfileCurve, ProfileHollow, State, Texture, ExtraParams) 36 EXEC('INSERT INTO Tmp_primshapes (UUID, Shape, ScaleX, ScaleY, ScaleZ, PCode, PathBegin, PathEnd, PathScaleX, PathScaleY, PathShearX, PathShearY, PathSkew, PathCurve, PathRadiusOffset, PathRevolutions, PathTaperX, PathTaperY, PathTwist, PathTwistBegin, ProfileBegin, ProfileEnd, ProfileCurve, ProfileHollow, State, Texture, ExtraParams)
37 SELECT CONVERT(varchar(36), UUID), Shape, ScaleX, ScaleY, ScaleZ, PCode, PathBegin, PathEnd, PathScaleX, PathScaleY, PathShearX, PathShearY, PathSkew, PathCurve, PathRadiusOffset, PathRevolutions, PathTaperX, PathTaperY, PathTwist, PathTwistBegin, ProfileBegin, ProfileEnd, ProfileCurve, ProfileHollow, State, Texture, ExtraParams FROM dbo.primshapes WITH (HOLDLOCK TABLOCKX)') 37 SELECT CONVERT(varchar(36), UUID), Shape, ScaleX, ScaleY, ScaleZ, PCode, PathBegin, PathEnd, PathScaleX, PathScaleY, PathShearX, PathShearY, PathSkew, PathCurve, PathRadiusOffset, PathRevolutions, PathTaperX, PathTaperY, PathTwist, PathTwistBegin, ProfileBegin, ProfileEnd, ProfileCurve, ProfileHollow, State, Texture, ExtraParams FROM primshapes WITH (HOLDLOCK TABLOCKX)')
38 38
39DROP TABLE dbo.primshapes 39DROP TABLE primshapes
40 40
41EXECUTE sp_rename N'dbo.Tmp_primshapes', N'primshapes', 'OBJECT' 41EXECUTE sp_rename N'Tmp_primshapes', N'primshapes', 'OBJECT'
42 42
43ALTER TABLE dbo.primshapes ADD CONSTRAINT 43ALTER TABLE primshapes ADD CONSTRAINT
44 PK__primshapes__0880433F PRIMARY KEY CLUSTERED 44 PK__primshapes__0880433F PRIMARY KEY CLUSTERED
45 ( 45 (
46 UUID 46 UUID
47 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 47 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
48 48
49COMMIT \ No newline at end of file 49COMMIT
generated by cgit v1.1 at 2024-11-06 00:22:01 +0000