aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/MSSQL
diff options
context:
space:
mode:
authorSean Dague2008-04-02 15:24:31 +0000
committerSean Dague2008-04-02 15:24:31 +0000
commitc52c68f314c67c76c7181a6d0828f476290fbd66 (patch)
tree66ab347502892902a096fa985f31b25738eb1381 /OpenSim/Data/MSSQL
parentreorganizing namespaces to put all the Data stuff into it's own namespace (diff)
downloadopensim-SC-c52c68f314c67c76c7181a6d0828f476290fbd66.zip
opensim-SC-c52c68f314c67c76c7181a6d0828f476290fbd66.tar.gz
opensim-SC-c52c68f314c67c76c7181a6d0828f476290fbd66.tar.bz2
opensim-SC-c52c68f314c67c76c7181a6d0828f476290fbd66.tar.xz
whole lot more moving
Diffstat (limited to 'OpenSim/Data/MSSQL')
-rw-r--r--OpenSim/Data/MSSQL/MSSQLAssetData.cs221
-rw-r--r--OpenSim/Data/MSSQL/MSSQLDataStore.cs1622
-rw-r--r--OpenSim/Data/MSSQL/MSSQLGridData.cs366
-rw-r--r--OpenSim/Data/MSSQL/MSSQLInventoryData.cs728
-rw-r--r--OpenSim/Data/MSSQL/MSSQLLogData.cs120
-rw-r--r--OpenSim/Data/MSSQL/MSSQLManager.cs529
-rw-r--r--OpenSim/Data/MSSQL/MSSQLUserData.cs771
-rw-r--r--OpenSim/Data/MSSQL/Properties/AssemblyInfo.cs65
-rw-r--r--OpenSim/Data/MSSQL/Resources/AvatarAppearance.sql44
-rw-r--r--OpenSim/Data/MSSQL/Resources/CreateAssetsTable.sql19
-rw-r--r--OpenSim/Data/MSSQL/Resources/CreateFoldersTable.sql27
-rw-r--r--OpenSim/Data/MSSQL/Resources/CreateItemsTable.sql39
-rw-r--r--OpenSim/Data/MSSQL/Resources/CreateUserFriendsTable.sql14
-rw-r--r--OpenSim/Data/MSSQL/Resources/Mssql-agents.sql37
-rw-r--r--OpenSim/Data/MSSQL/Resources/Mssql-logs.sql20
-rw-r--r--OpenSim/Data/MSSQL/Resources/Mssql-regions.sql41
-rw-r--r--OpenSim/Data/MSSQL/Resources/Mssql-users.sql42
17 files changed, 4705 insertions, 0 deletions
diff --git a/OpenSim/Data/MSSQL/MSSQLAssetData.cs b/OpenSim/Data/MSSQL/MSSQLAssetData.cs
new file mode 100644
index 0000000..059bb5e
--- /dev/null
+++ b/OpenSim/Data/MSSQL/MSSQLAssetData.cs
@@ -0,0 +1,221 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSim Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections.Generic;
30using System.Data;
31using System.Data.SqlClient;
32using libsecondlife;
33using OpenSim.Framework.Console;
34
35namespace OpenSim.Framework.Data.MSSQL
36{
37 internal class MSSQLAssetData : AssetDataBase
38 {
39 private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
40
41 private MSSQLManager database;
42
43 #region IAssetProvider Members
44
45 private void UpgradeAssetsTable(string tableName)
46 {
47 // null as the version, indicates that the table didn't exist
48 if (tableName == null)
49 {
50 m_log.Info("[ASSETS]: Creating new database tables");
51 database.ExecuteResourceSql("CreateAssetsTable.sql");
52 return;
53 }
54 }
55
56 /// <summary>
57 /// Ensure that the assets related tables exists and are at the latest version
58 /// </summary>
59 private void TestTables()
60 {
61 Dictionary<string, string> tableList = new Dictionary<string, string>();
62
63 tableList["assets"] = null;
64 database.GetTableVersion(tableList);
65
66 UpgradeAssetsTable(tableList["assets"]);
67 }
68
69 override public AssetBase FetchAsset(LLUUID assetID)
70 {
71 AssetBase asset = null;
72
73 Dictionary<string, string> param = new Dictionary<string, string>();
74 param["id"] = assetID.ToString();
75
76 IDbCommand result = database.Query("SELECT * FROM assets WHERE id = @id", param);
77 IDataReader reader = result.ExecuteReader();
78
79 asset = database.getAssetRow(reader);
80 reader.Close();
81 result.Dispose();
82
83 return asset;
84 }
85
86 override public void CreateAsset(AssetBase asset)
87 {
88 if (ExistsAsset((LLUUID) asset.FullID))
89 {
90 return;
91 }
92
93
94 SqlCommand cmd =
95 new SqlCommand(
96 "INSERT INTO assets ([id], [name], [description], [assetType], [invType], [local], [temporary], [data])" +
97 " VALUES " +
98 "(@id, @name, @description, @assetType, @invType, @local, @temporary, @data)",
99 database.getConnection());
100
101 using (cmd)
102 {
103 //SqlParameter p = cmd.Parameters.Add("id", SqlDbType.NVarChar);
104 //p.Value = asset.FullID.ToString();
105 cmd.Parameters.AddWithValue("id", asset.FullID.ToString());
106 cmd.Parameters.AddWithValue("name", asset.Name);
107 cmd.Parameters.AddWithValue("description", asset.Description);
108 SqlParameter e = cmd.Parameters.Add("assetType", SqlDbType.TinyInt);
109 e.Value = asset.Type;
110 SqlParameter f = cmd.Parameters.Add("invType", SqlDbType.TinyInt);
111 f.Value = asset.InvType;
112 SqlParameter g = cmd.Parameters.Add("local", SqlDbType.TinyInt);
113 g.Value = asset.Local;
114 SqlParameter h = cmd.Parameters.Add("temporary", SqlDbType.TinyInt);
115 h.Value = asset.Temporary;
116 SqlParameter i = cmd.Parameters.Add("data", SqlDbType.Image);
117 i.Value = asset.Data;
118 try
119 {
120 cmd.ExecuteNonQuery();
121 }
122 catch (Exception)
123 {
124 throw;
125 }
126
127 cmd.Dispose();
128 }
129 }
130
131
132 override public void UpdateAsset(AssetBase asset)
133 {
134 SqlCommand command = new SqlCommand("UPDATE assets set id = @id, " +
135 "name = @name, " +
136 "description = @description," +
137 "assetType = @assetType," +
138 "invType = @invType," +
139 "local = @local," +
140 "temporary = @temporary," +
141 "data = @data where " +
142 "id = @keyId;", database.getConnection());
143 SqlParameter param1 = new SqlParameter("@id", asset.FullID.ToString());
144 SqlParameter param2 = new SqlParameter("@name", asset.Name);
145 SqlParameter param3 = new SqlParameter("@description", asset.Description);
146 SqlParameter param4 = new SqlParameter("@assetType", asset.Type);
147 SqlParameter param5 = new SqlParameter("@invType", asset.InvType);
148 SqlParameter param6 = new SqlParameter("@local", asset.Local);
149 SqlParameter param7 = new SqlParameter("@temporary", asset.Temporary);
150 SqlParameter param8 = new SqlParameter("@data", asset.Data);
151 SqlParameter param9 = new SqlParameter("@keyId", asset.FullID.ToString());
152 command.Parameters.Add(param1);
153 command.Parameters.Add(param2);
154 command.Parameters.Add(param3);
155 command.Parameters.Add(param4);
156 command.Parameters.Add(param5);
157 command.Parameters.Add(param6);
158 command.Parameters.Add(param7);
159 command.Parameters.Add(param8);
160 command.Parameters.Add(param9);
161
162 try
163 {
164 command.ExecuteNonQuery();
165 }
166 catch (Exception e)
167 {
168 m_log.Error(e.ToString());
169 }
170 }
171
172 override public bool ExistsAsset(LLUUID uuid)
173 {
174 if (FetchAsset(uuid) != null)
175 {
176 return true;
177 }
178 return false;
179 }
180
181 /// <summary>
182 /// All writes are immediately commited to the database, so this is a no-op
183 /// </summary>
184 override public void CommitAssets()
185 {
186 }
187
188 #endregion
189
190 #region IPlugin Members
191
192 override public void Initialise()
193 {
194 IniFile GridDataMySqlFile = new IniFile("mssql_connection.ini");
195 string settingDataSource = GridDataMySqlFile.ParseFileReadValue("data_source");
196 string settingInitialCatalog = GridDataMySqlFile.ParseFileReadValue("initial_catalog");
197 string settingPersistSecurityInfo = GridDataMySqlFile.ParseFileReadValue("persist_security_info");
198 string settingUserId = GridDataMySqlFile.ParseFileReadValue("user_id");
199 string settingPassword = GridDataMySqlFile.ParseFileReadValue("password");
200
201 database =
202 new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId,
203 settingPassword);
204
205 TestTables();
206 }
207
208 override public string Version
209 {
210// get { return database.getVersion(); }
211 get { return database.getVersion(); }
212 }
213
214 override public string Name
215 {
216 get { return "MSSQL Asset storage engine"; }
217 }
218
219 #endregion
220 }
221}
diff --git a/OpenSim/Data/MSSQL/MSSQLDataStore.cs b/OpenSim/Data/MSSQL/MSSQLDataStore.cs
new file mode 100644
index 0000000..d34abe3
--- /dev/null
+++ b/OpenSim/Data/MSSQL/MSSQLDataStore.cs
@@ -0,0 +1,1622 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSim Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections.Generic;
30using System.Data;
31using System.Data.SqlClient;
32using System.IO;
33using libsecondlife;
34using OpenSim.Framework;
35using OpenSim.Framework.Console;
36using OpenSim.Framework.Data;
37using OpenSim.Region.Environment.Interfaces;
38using OpenSim.Region.Environment.Scenes;
39using OpenSim.Framework.Data.MSSQL;
40
41namespace OpenSim.Framework.Data.MSSQL
42{
43 public class MSSQLDataStore : IRegionDataStore
44 {
45 // private static FileSystemDataStore Instance = new FileSystemDataStore();
46 private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
47
48 private const string m_primSelect = "select * from prims";
49 private const string m_shapeSelect = "select * from primshapes";
50 private const string m_itemsSelect = "select * from primitems";
51 private const string m_terrainSelect = "select top 1 * from terrain";
52 private const string m_landSelect = "select * from land";
53 private const string m_landAccessListSelect = "select * from landaccesslist";
54
55 private DataSet m_dataSet;
56 private SqlDataAdapter m_primDataAdapter;
57 private SqlDataAdapter m_shapeDataAdapter;
58 private SqlDataAdapter m_itemsDataAdapter;
59 private SqlConnection m_connection;
60 private SqlDataAdapter m_terrainDataAdapter;
61 private SqlDataAdapter m_landDataAdapter;
62 private SqlDataAdapter m_landAccessListDataAdapter;
63
64 private DataTable m_primTable;
65 private DataTable m_shapeTable;
66 private DataTable m_itemsTable;
67 private DataTable m_terrainTable;
68 private DataTable m_landTable;
69 private DataTable m_landAccessListTable;
70
71 // Temporary attribute while this is experimental
72 private bool persistPrimInventories;
73
74 /***********************************************************************
75 *
76 * Public Interface Functions
77 *
78 **********************************************************************/
79
80 // see IRegionDataStore
81 public void Initialise(string connectionString, bool persistPrimInventories)
82 {
83 // Instance.Initialise("", true);
84
85 m_dataSet = new DataSet();
86 this.persistPrimInventories = persistPrimInventories;
87
88 m_log.Info("[DATASTORE]: MSSql - connecting: " + connectionString);
89 m_connection = new SqlConnection(connectionString);
90
91 SqlCommand primSelectCmd = new SqlCommand(m_primSelect, m_connection);
92 m_primDataAdapter = new SqlDataAdapter(primSelectCmd);
93
94 SqlCommand shapeSelectCmd = new SqlCommand(m_shapeSelect, m_connection);
95 m_shapeDataAdapter = new SqlDataAdapter(shapeSelectCmd);
96
97 SqlCommand itemsSelectCmd = new SqlCommand(m_itemsSelect, m_connection);
98 m_itemsDataAdapter = new SqlDataAdapter(itemsSelectCmd);
99
100 SqlCommand terrainSelectCmd = new SqlCommand(m_terrainSelect, m_connection);
101 m_terrainDataAdapter = new SqlDataAdapter(terrainSelectCmd);
102
103 SqlCommand landSelectCmd = new SqlCommand(m_landSelect, m_connection);
104 m_landDataAdapter = new SqlDataAdapter(landSelectCmd);
105
106 SqlCommand landAccessListSelectCmd = new SqlCommand(m_landAccessListSelect, m_connection);
107 m_landAccessListDataAdapter = new SqlDataAdapter(landAccessListSelectCmd);
108
109 TestTables(m_connection);
110
111 lock (m_dataSet)
112 {
113 m_primTable = createPrimTable();
114 m_dataSet.Tables.Add(m_primTable);
115 setupPrimCommands(m_primDataAdapter, m_connection);
116 m_primDataAdapter.Fill(m_primTable);
117
118 m_shapeTable = createShapeTable();
119 m_dataSet.Tables.Add(m_shapeTable);
120 setupShapeCommands(m_shapeDataAdapter, m_connection);
121 m_shapeDataAdapter.Fill(m_shapeTable);
122
123 if (persistPrimInventories)
124 {
125 m_itemsTable = createItemsTable();
126 m_dataSet.Tables.Add(m_itemsTable);
127 SetupItemsCommands(m_itemsDataAdapter, m_connection);
128 m_itemsDataAdapter.Fill(m_itemsTable);
129 }
130
131 m_terrainTable = createTerrainTable();
132 m_dataSet.Tables.Add(m_terrainTable);
133 setupTerrainCommands(m_terrainDataAdapter, m_connection);
134 m_terrainDataAdapter.Fill(m_terrainTable);
135
136 m_landTable = createLandTable();
137 m_dataSet.Tables.Add(m_landTable);
138 setupLandCommands(m_landDataAdapter, m_connection);
139 m_landDataAdapter.Fill(m_landTable);
140
141 m_landAccessListTable = createLandAccessListTable();
142 m_dataSet.Tables.Add(m_landAccessListTable);
143 setupLandAccessCommands(m_landAccessListDataAdapter, m_connection);
144 m_landAccessListDataAdapter.Fill(m_landAccessListTable);
145 }
146 }
147
148 public void StoreObject(SceneObjectGroup obj, LLUUID regionUUID)
149 {
150 // Instance.StoreObject(obj, regionUUID);
151
152 lock (m_dataSet)
153 {
154 foreach (SceneObjectPart prim in obj.Children.Values)
155 {
156 if ((prim.ObjectFlags & (uint)LLObject.ObjectFlags.Physics) == 0)
157 {
158 m_log.Info("[DATASTORE]: Adding obj: " + obj.UUID + " to region: " + regionUUID);
159 addPrim(prim, obj.UUID, regionUUID);
160 }
161 else
162 {
163 // m_log.Info("[DATASTORE]: Ignoring Physical obj: " + obj.UUID + " in region: " + regionUUID);
164 }
165 }
166 }
167
168 Commit();
169 }
170
171 public void RemoveObject(LLUUID obj, LLUUID regionUUID)
172 {
173 // Instance.RemoveObject(obj, regionUUID);
174
175 m_log.InfoFormat("[DATASTORE]: Removing obj: {0} from region: {1}", obj.UUID, regionUUID);
176
177 DataTable prims = m_primTable;
178 DataTable shapes = m_shapeTable;
179
180 string selectExp = "SceneGroupID = '" + obj.ToString() + "'";
181 lock (m_dataSet)
182 {
183 foreach (DataRow row in prims.Select(selectExp))
184 {
185 // Remove shapes row
186 LLUUID uuid = new LLUUID((string)row["UUID"]);
187
188 DataRow shapeRow = shapes.Rows.Find(uuid.UUID);
189 if (shapeRow != null)
190 {
191 shapeRow.Delete();
192 }
193
194 if (persistPrimInventories)
195 {
196 RemoveItems(new LLUUID((string)row["UUID"]));
197 }
198
199 // Remove prim row
200 row.Delete();
201 }
202 }
203
204 Commit();
205 }
206
207 /// <summary>
208 /// Remove all persisted items of the given prim.
209 /// The caller must acquire the necessrary synchronization locks and commit or rollback changes.
210 /// </summary>
211 private void RemoveItems(LLUUID uuid)
212 {
213 String sql = String.Format("primID = '{0}'", uuid);
214 DataRow[] itemRows = m_itemsTable.Select(sql);
215
216 foreach (DataRow itemRow in itemRows)
217 {
218 itemRow.Delete();
219 }
220 }
221
222 /// <summary>
223 /// Load persisted objects from region storage.
224 /// </summary>
225 public List<SceneObjectGroup> LoadObjects(LLUUID regionUUID)
226 {
227 // return Instance.LoadObjects(regionUUID);
228
229 Dictionary<LLUUID, SceneObjectGroup> createdObjects = new Dictionary<LLUUID, SceneObjectGroup>();
230
231 List<SceneObjectGroup> retvals = new List<SceneObjectGroup>();
232
233 DataTable prims = m_primTable;
234 DataTable shapes = m_shapeTable;
235
236 string byRegion = "RegionUUID = '" + regionUUID.ToString() + "'";
237 string orderByParent = "ParentID ASC";
238
239 lock (m_dataSet)
240 {
241 DataRow[] primsForRegion = prims.Select(byRegion, orderByParent);
242 m_log.Info("[DATASTORE]: " +
243 "Loaded " + primsForRegion.Length + " prims for region: " + regionUUID);
244
245 foreach (DataRow primRow in primsForRegion)
246 {
247 try
248 {
249 string uuid = (string)primRow["UUID"];
250 string objID = (string)primRow["SceneGroupID"];
251
252 SceneObjectPart prim = buildPrim(primRow);
253
254 if (uuid == objID) //is new SceneObjectGroup ?
255 {
256 SceneObjectGroup group = new SceneObjectGroup();
257
258 DataRow shapeRow = shapes.Rows.Find(prim.UUID);
259 if (shapeRow != null)
260 {
261 prim.Shape = buildShape(shapeRow);
262 }
263 else
264 {
265 m_log.Info(
266 "No shape found for prim in storage, so setting default box shape");
267 prim.Shape = PrimitiveBaseShape.Default;
268 }
269 group.AddPart(prim);
270 group.RootPart = prim;
271
272 createdObjects.Add(group.UUID, group);
273 retvals.Add(group);
274 }
275 else
276 {
277 DataRow shapeRow = shapes.Rows.Find(prim.UUID);
278 if (shapeRow != null)
279 {
280 prim.Shape = buildShape(shapeRow);
281 }
282 else
283 {
284 m_log.Info(
285 "No shape found for prim in storage, so setting default box shape");
286 prim.Shape = PrimitiveBaseShape.Default;
287 }
288 createdObjects[new LLUUID(objID)].AddPart(prim);
289 }
290
291 if (persistPrimInventories)
292 {
293 LoadItems(prim);
294 }
295 }
296 catch (Exception e)
297 {
298 m_log.Error("[DATASTORE]: Failed create prim object, exception and data follows");
299 m_log.Info("[DATASTORE]: " + e.ToString());
300 foreach (DataColumn col in prims.Columns)
301 {
302 m_log.Info("[DATASTORE]: Col: " + col.ColumnName + " => " + primRow[col]);
303 }
304 }
305 }
306 }
307 return retvals;
308 }
309
310 /// <summary>
311 /// Load in a prim's persisted inventory.
312 /// </summary>
313 /// <param name="prim"></param>
314 private void LoadItems(SceneObjectPart prim)
315 {
316 //m_log.InfoFormat("[DATASTORE]: Loading inventory for {0}, {1}", prim.Name, prim.UUID);
317
318 DataTable dbItems = m_itemsTable;
319
320 String sql = String.Format("primID = '{0}'", prim.UUID.ToString());
321 DataRow[] dbItemRows = dbItems.Select(sql);
322
323 IList<TaskInventoryItem> inventory = new List<TaskInventoryItem>();
324
325 foreach (DataRow row in dbItemRows)
326 {
327 TaskInventoryItem item = buildItem(row);
328 inventory.Add(item);
329
330 //m_log.DebugFormat("[DATASTORE]: Restored item {0}, {1}", item.Name, item.ItemID);
331 }
332
333 prim.RestoreInventoryItems(inventory);
334
335 // XXX A nasty little hack to recover the folder id for the prim (which is currently stored in
336 // every item). This data should really be stored in the prim table itself.
337 if (dbItemRows.Length > 0)
338 {
339 prim.FolderID = inventory[0].ParentID;
340 }
341 }
342
343 public void StoreTerrain(double[,] ter, LLUUID regionID)
344 {
345 int revision = Util.UnixTimeSinceEpoch();
346 m_log.Info("[DATASTORE]: Storing terrain revision r" + revision.ToString());
347
348 DataTable terrain = m_dataSet.Tables["terrain"];
349 lock (m_dataSet)
350 {
351 SqlCommand cmd = new SqlCommand("insert into terrain(RegionUUID, Revision, Heightfield)" +
352 " values(@RegionUUID, @Revision, @Heightfield)", m_connection);
353 using (cmd)
354 {
355 cmd.Parameters.Add(new SqlParameter("@RegionUUID", regionID.UUID));
356 cmd.Parameters.Add(new SqlParameter("@Revision", revision));
357 cmd.Parameters.Add(new SqlParameter("@Heightfield", serializeTerrain(ter)));
358 cmd.ExecuteNonQuery();
359 }
360 }
361 }
362
363 public double[,] LoadTerrain(LLUUID regionID)
364 {
365 double[,] terret = new double[256, 256];
366 terret.Initialize();
367
368 SqlCommand cmd = new SqlCommand(
369 @"select top 1 RegionUUID, Revision, Heightfield from terrain
370 where RegionUUID=@RegionUUID order by Revision desc"
371 , m_connection);
372
373 SqlParameter param = new SqlParameter();
374 cmd.Parameters.Add(new SqlParameter("@RegionUUID", regionID.UUID));
375
376 if (m_connection.State != ConnectionState.Open)
377 {
378 m_connection.Open();
379 }
380
381 using (SqlDataReader row = cmd.ExecuteReader())
382 {
383 int rev = 0;
384 if (row.Read())
385 {
386 MemoryStream str = new MemoryStream((byte[])row["Heightfield"]);
387 BinaryReader br = new BinaryReader(str);
388 for (int x = 0; x < 256; x++)
389 {
390 for (int y = 0; y < 256; y++)
391 {
392 terret[x, y] = br.ReadDouble();
393 }
394 }
395 rev = (int)row["Revision"];
396 }
397 else
398 {
399 m_log.Info("[DATASTORE]: No terrain found for region");
400 return null;
401 }
402
403 m_log.Info("[DATASTORE]: Loaded terrain revision r" + rev.ToString());
404 }
405
406 return terret;
407 }
408
409 public void RemoveLandObject(LLUUID globalID)
410 {
411 // Instance.RemoveLandObject(globalID);
412
413 lock (m_dataSet)
414 {
415 using (SqlCommand cmd = new SqlCommand("delete from land where UUID=@UUID", m_connection))
416 {
417 cmd.Parameters.Add(new SqlParameter("@UUID", globalID.UUID));
418 cmd.ExecuteNonQuery();
419 }
420
421 using (
422 SqlCommand cmd = new SqlCommand("delete from landaccesslist where LandUUID=@UUID", m_connection)
423 )
424 {
425 cmd.Parameters.Add(new SqlParameter("@UUID", globalID.UUID));
426 cmd.ExecuteNonQuery();
427 }
428 }
429 }
430
431 public void StoreLandObject(ILandObject parcel)
432 {
433 // Instance.StoreLandObject(parcel, regionUUID);
434
435 // Does the new locking fix it?
436 // m_log.Info("[DATASTORE]: Tedds temp fix: Waiting 3 seconds to avoid others writing to table while we hold a dataset of it. (Someone please fix! :))");
437 // System.Threading.Thread.Sleep(2500 + rnd.Next(0, 1000));
438
439 lock (m_dataSet)
440 {
441 DataTable land = m_landTable;
442 DataTable landaccesslist = m_landAccessListTable;
443
444 DataRow landRow = land.Rows.Find(parcel.landData.globalID.UUID);
445 if (landRow == null)
446 {
447 landRow = land.NewRow();
448 fillLandRow(landRow, parcel.landData, parcel.regionUUID);
449 land.Rows.Add(landRow);
450 }
451 else
452 {
453 fillLandRow(landRow, parcel.landData, parcel.regionUUID);
454 }
455
456 using (
457 SqlCommand cmd =
458 new SqlCommand("delete from landaccesslist where LandUUID=@LandUUID", m_connection))
459 {
460 cmd.Parameters.Add(new SqlParameter("@LandUUID", parcel.landData.globalID.UUID));
461 cmd.ExecuteNonQuery();
462 }
463
464 foreach (ParcelManager.ParcelAccessEntry entry in parcel.landData.parcelAccessList)
465 {
466 DataRow newAccessRow = landaccesslist.NewRow();
467 fillLandAccessRow(newAccessRow, entry, parcel.landData.globalID);
468 landaccesslist.Rows.Add(newAccessRow);
469 }
470
471 }
472 Commit();
473 }
474
475 public List<LandData> LoadLandObjects(LLUUID regionUUID)
476 {
477 List<LandData> landDataForRegion = new List<LandData>();
478 lock (m_dataSet)
479 {
480 DataTable land = m_landTable;
481 DataTable landaccesslist = m_landAccessListTable;
482 string searchExp = "RegionUUID = '" + regionUUID.UUID + "'";
483 DataRow[] rawDataForRegion = land.Select(searchExp);
484 foreach (DataRow rawDataLand in rawDataForRegion)
485 {
486 LandData newLand = buildLandData(rawDataLand);
487 string accessListSearchExp = "LandUUID = '" + newLand.globalID.UUID + "'";
488 DataRow[] rawDataForLandAccessList = landaccesslist.Select(accessListSearchExp);
489 foreach (DataRow rawDataLandAccess in rawDataForLandAccessList)
490 {
491 newLand.parcelAccessList.Add(buildLandAccessData(rawDataLandAccess));
492 }
493
494 landDataForRegion.Add(newLand);
495 }
496 }
497 return landDataForRegion;
498 }
499
500 public void Commit()
501 {
502 if (m_connection.State != ConnectionState.Open)
503 {
504 m_connection.Open();
505 }
506
507 lock (m_dataSet)
508 {
509 // DisplayDataSet(m_dataSet, "Region DataSet");
510
511 m_primDataAdapter.Update(m_primTable);
512 m_shapeDataAdapter.Update(m_shapeTable);
513
514 if (persistPrimInventories)
515 {
516 m_itemsDataAdapter.Update(m_itemsTable);
517 }
518
519 m_terrainDataAdapter.Update(m_terrainTable);
520 m_landDataAdapter.Update(m_landTable);
521 m_landAccessListDataAdapter.Update(m_landAccessListTable);
522
523 m_dataSet.AcceptChanges();
524 }
525 }
526
527 public void Shutdown()
528 {
529 Commit();
530 }
531
532 /***********************************************************************
533 *
534 * Database Definition Functions
535 *
536 * This should be db agnostic as we define them in ADO.NET terms
537 *
538 **********************************************************************/
539
540 private DataColumn createCol(DataTable dt, string name, Type type)
541 {
542 DataColumn col = new DataColumn(name, type);
543 dt.Columns.Add(col);
544 return col;
545 }
546
547 private DataTable createTerrainTable()
548 {
549 DataTable terrain = new DataTable("terrain");
550
551 createCol(terrain, "RegionUUID", typeof(String));
552 createCol(terrain, "Revision", typeof(Int32));
553 createCol(terrain, "Heightfield", typeof(Byte[]));
554
555 return terrain;
556 }
557
558 private DataTable createPrimTable()
559 {
560 DataTable prims = new DataTable("prims");
561
562 createCol(prims, "UUID", typeof(String));
563 createCol(prims, "RegionUUID", typeof(String));
564 createCol(prims, "ParentID", typeof(Int32));
565 createCol(prims, "CreationDate", typeof(Int32));
566 createCol(prims, "Name", typeof(String));
567 createCol(prims, "SceneGroupID", typeof(String));
568 // various text fields
569 createCol(prims, "Text", typeof(String));
570 createCol(prims, "Description", typeof(String));
571 createCol(prims, "SitName", typeof(String));
572 createCol(prims, "TouchName", typeof(String));
573 // permissions
574 createCol(prims, "ObjectFlags", typeof(Int32));
575 createCol(prims, "CreatorID", typeof(String));
576 createCol(prims, "OwnerID", typeof(String));
577 createCol(prims, "GroupID", typeof(String));
578 createCol(prims, "LastOwnerID", typeof(String));
579 createCol(prims, "OwnerMask", typeof(Int32));
580 createCol(prims, "NextOwnerMask", typeof(Int32));
581 createCol(prims, "GroupMask", typeof(Int32));
582 createCol(prims, "EveryoneMask", typeof(Int32));
583 createCol(prims, "BaseMask", typeof(Int32));
584 // vectors
585 createCol(prims, "PositionX", typeof(Double));
586 createCol(prims, "PositionY", typeof(Double));
587 createCol(prims, "PositionZ", typeof(Double));
588 createCol(prims, "GroupPositionX", typeof(Double));
589 createCol(prims, "GroupPositionY", typeof(Double));
590 createCol(prims, "GroupPositionZ", typeof(Double));
591 createCol(prims, "VelocityX", typeof(Double));
592 createCol(prims, "VelocityY", typeof(Double));
593 createCol(prims, "VelocityZ", typeof(Double));
594 createCol(prims, "AngularVelocityX", typeof(Double));
595 createCol(prims, "AngularVelocityY", typeof(Double));
596 createCol(prims, "AngularVelocityZ", typeof(Double));
597 createCol(prims, "AccelerationX", typeof(Double));
598 createCol(prims, "AccelerationY", typeof(Double));
599 createCol(prims, "AccelerationZ", typeof(Double));
600 // quaternions
601 createCol(prims, "RotationX", typeof(Double));
602 createCol(prims, "RotationY", typeof(Double));
603 createCol(prims, "RotationZ", typeof(Double));
604 createCol(prims, "RotationW", typeof(Double));
605
606 // sit target
607 createCol(prims, "SitTargetOffsetX", typeof(Double));
608 createCol(prims, "SitTargetOffsetY", typeof(Double));
609 createCol(prims, "SitTargetOffsetZ", typeof(Double));
610
611 createCol(prims, "SitTargetOrientW", typeof(Double));
612 createCol(prims, "SitTargetOrientX", typeof(Double));
613 createCol(prims, "SitTargetOrientY", typeof(Double));
614 createCol(prims, "SitTargetOrientZ", typeof(Double));
615
616 // Add in contraints
617 prims.PrimaryKey = new DataColumn[] { prims.Columns["UUID"] };
618
619 return prims;
620 }
621
622 private DataTable createLandTable()
623 {
624 DataTable land = new DataTable("land");
625 createCol(land, "UUID", typeof(String));
626 createCol(land, "RegionUUID", typeof(String));
627 createCol(land, "LocalLandID", typeof(Int32));
628
629 // Bitmap is a byte[512]
630 createCol(land, "Bitmap", typeof(Byte[]));
631
632 createCol(land, "Name", typeof(String));
633 createCol(land, "Description", typeof(String));
634 createCol(land, "OwnerUUID", typeof(String));
635 createCol(land, "IsGroupOwned", typeof(Int32));
636 createCol(land, "Area", typeof(Int32));
637 createCol(land, "AuctionID", typeof(Int32)); //Unemplemented
638 createCol(land, "Category", typeof(Int32)); //Enum libsecondlife.Parcel.ParcelCategory
639 createCol(land, "ClaimDate", typeof(Int32));
640 createCol(land, "ClaimPrice", typeof(Int32));
641 createCol(land, "GroupUUID", typeof(String));
642 createCol(land, "SalePrice", typeof(Int32));
643 createCol(land, "LandStatus", typeof(Int32)); //Enum. libsecondlife.Parcel.ParcelStatus
644 createCol(land, "LandFlags", typeof(Int32));
645 createCol(land, "LandingType", typeof(Int32));
646 createCol(land, "MediaAutoScale", typeof(Int32));
647 createCol(land, "MediaTextureUUID", typeof(String));
648 createCol(land, "MediaURL", typeof(String));
649 createCol(land, "MusicURL", typeof(String));
650 createCol(land, "PassHours", typeof(Double));
651 createCol(land, "PassPrice", typeof(Int32));
652 createCol(land, "SnapshotUUID", typeof(String));
653 createCol(land, "UserLocationX", typeof(Double));
654 createCol(land, "UserLocationY", typeof(Double));
655 createCol(land, "UserLocationZ", typeof(Double));
656 createCol(land, "UserLookAtX", typeof(Double));
657 createCol(land, "UserLookAtY", typeof(Double));
658 createCol(land, "UserLookAtZ", typeof(Double));
659
660 land.PrimaryKey = new DataColumn[] { land.Columns["UUID"] };
661
662 return land;
663 }
664
665 private DataTable createLandAccessListTable()
666 {
667 DataTable landaccess = new DataTable("landaccesslist");
668 createCol(landaccess, "LandUUID", typeof(String));
669 createCol(landaccess, "AccessUUID", typeof(String));
670 createCol(landaccess, "Flags", typeof(Int32));
671
672 return landaccess;
673 }
674
675 private DataTable createShapeTable()
676 {
677 DataTable shapes = new DataTable("primshapes");
678 createCol(shapes, "UUID", typeof(String));
679 // shape is an enum
680 createCol(shapes, "Shape", typeof(Int32));
681 // vectors
682 createCol(shapes, "ScaleX", typeof(Double));
683 createCol(shapes, "ScaleY", typeof(Double));
684 createCol(shapes, "ScaleZ", typeof(Double));
685 // paths
686 createCol(shapes, "PCode", typeof(Int32));
687 createCol(shapes, "PathBegin", typeof(Int32));
688 createCol(shapes, "PathEnd", typeof(Int32));
689 createCol(shapes, "PathScaleX", typeof(Int32));
690 createCol(shapes, "PathScaleY", typeof(Int32));
691 createCol(shapes, "PathShearX", typeof(Int32));
692 createCol(shapes, "PathShearY", typeof(Int32));
693 createCol(shapes, "PathSkew", typeof(Int32));
694 createCol(shapes, "PathCurve", typeof(Int32));
695 createCol(shapes, "PathRadiusOffset", typeof(Int32));
696 createCol(shapes, "PathRevolutions", typeof(Int32));
697 createCol(shapes, "PathTaperX", typeof(Int32));
698 createCol(shapes, "PathTaperY", typeof(Int32));
699 createCol(shapes, "PathTwist", typeof(Int32));
700 createCol(shapes, "PathTwistBegin", typeof(Int32));
701 // profile
702 createCol(shapes, "ProfileBegin", typeof(Int32));
703 createCol(shapes, "ProfileEnd", typeof(Int32));
704 createCol(shapes, "ProfileCurve", typeof(Int32));
705 createCol(shapes, "ProfileHollow", typeof(Int32));
706 createCol(shapes, "State", typeof(Int32));
707 // text TODO: this isn't right, but I'm not sure the right
708 // way to specify this as a blob atm
709 createCol(shapes, "Texture", typeof(Byte[]));
710 createCol(shapes, "ExtraParams", typeof(Byte[]));
711
712 shapes.PrimaryKey = new DataColumn[] { shapes.Columns["UUID"] };
713
714 return shapes;
715 }
716
717 private DataTable createItemsTable()
718 {
719 DataTable items = new DataTable("primitems");
720
721 createCol(items, "itemID", typeof(String));
722 createCol(items, "primID", typeof(String));
723 createCol(items, "assetID", typeof(String));
724 createCol(items, "parentFolderID", typeof(String));
725
726 createCol(items, "invType", typeof(Int32));
727 createCol(items, "assetType", typeof(Int32));
728
729 createCol(items, "name", typeof(String));
730 createCol(items, "description", typeof(String));
731
732 createCol(items, "creationDate", typeof(Int64));
733 createCol(items, "creatorID", typeof(String));
734 createCol(items, "ownerID", typeof(String));
735 createCol(items, "lastOwnerID", typeof(String));
736 createCol(items, "groupID", typeof(String));
737
738 createCol(items, "nextPermissions", typeof(Int32));
739 createCol(items, "currentPermissions", typeof(Int32));
740 createCol(items, "basePermissions", typeof(Int32));
741 createCol(items, "everyonePermissions", typeof(Int32));
742 createCol(items, "groupPermissions", typeof(Int32));
743
744 items.PrimaryKey = new DataColumn[] { items.Columns["itemID"] };
745
746 return items;
747 }
748
749 /***********************************************************************
750 *
751 * Convert between ADO.NET <=> OpenSim Objects
752 *
753 * These should be database independant
754 *
755 **********************************************************************/
756
757 private SceneObjectPart buildPrim(DataRow row)
758 {
759 SceneObjectPart prim = new SceneObjectPart();
760 prim.UUID = new LLUUID((String)row["UUID"]);
761 // explicit conversion of integers is required, which sort
762 // of sucks. No idea if there is a shortcut here or not.
763 prim.ParentID = Convert.ToUInt32(row["ParentID"]);
764 prim.CreationDate = Convert.ToInt32(row["CreationDate"]);
765 prim.Name = (String)row["Name"];
766 // various text fields
767 prim.Text = (String)row["Text"];
768 prim.Description = (String)row["Description"];
769 prim.SitName = (String)row["SitName"];
770 prim.TouchName = (String)row["TouchName"];
771 // permissions
772 prim.ObjectFlags = Convert.ToUInt32(row["ObjectFlags"]);
773 prim.CreatorID = new LLUUID((String)row["CreatorID"]);
774 prim.OwnerID = new LLUUID((String)row["OwnerID"]);
775 prim.GroupID = new LLUUID((String)row["GroupID"]);
776 prim.LastOwnerID = new LLUUID((String)row["LastOwnerID"]);
777 prim.OwnerMask = Convert.ToUInt32(row["OwnerMask"]);
778 prim.NextOwnerMask = Convert.ToUInt32(row["NextOwnerMask"]);
779 prim.GroupMask = Convert.ToUInt32(row["GroupMask"]);
780 prim.EveryoneMask = Convert.ToUInt32(row["EveryoneMask"]);
781 prim.BaseMask = Convert.ToUInt32(row["BaseMask"]);
782 // vectors
783 prim.OffsetPosition = new LLVector3(
784 Convert.ToSingle(row["PositionX"]),
785 Convert.ToSingle(row["PositionY"]),
786 Convert.ToSingle(row["PositionZ"])
787 );
788 prim.GroupPosition = new LLVector3(
789 Convert.ToSingle(row["GroupPositionX"]),
790 Convert.ToSingle(row["GroupPositionY"]),
791 Convert.ToSingle(row["GroupPositionZ"])
792 );
793 prim.Velocity = new LLVector3(
794 Convert.ToSingle(row["VelocityX"]),
795 Convert.ToSingle(row["VelocityY"]),
796 Convert.ToSingle(row["VelocityZ"])
797 );
798 prim.AngularVelocity = new LLVector3(
799 Convert.ToSingle(row["AngularVelocityX"]),
800 Convert.ToSingle(row["AngularVelocityY"]),
801 Convert.ToSingle(row["AngularVelocityZ"])
802 );
803 prim.Acceleration = new LLVector3(
804 Convert.ToSingle(row["AccelerationX"]),
805 Convert.ToSingle(row["AccelerationY"]),
806 Convert.ToSingle(row["AccelerationZ"])
807 );
808 // quaternions
809 prim.RotationOffset = new LLQuaternion(
810 Convert.ToSingle(row["RotationX"]),
811 Convert.ToSingle(row["RotationY"]),
812 Convert.ToSingle(row["RotationZ"]),
813 Convert.ToSingle(row["RotationW"])
814 );
815 try
816 {
817 prim.SetSitTargetLL(new LLVector3(
818 Convert.ToSingle(row["SitTargetOffsetX"]),
819 Convert.ToSingle(row["SitTargetOffsetY"]),
820 Convert.ToSingle(row["SitTargetOffsetZ"])), new LLQuaternion(
821 Convert.ToSingle(
822 row["SitTargetOrientX"]),
823 Convert.ToSingle(
824 row["SitTargetOrientY"]),
825 Convert.ToSingle(
826 row["SitTargetOrientZ"]),
827 Convert.ToSingle(
828 row["SitTargetOrientW"])));
829 }
830 catch (InvalidCastException)
831 {
832 // Database table was created before we got here and now has null values :P
833
834 using (
835 SqlCommand cmd =
836 new SqlCommand(
837 "ALTER TABLE [prims] ADD COLUMN [SitTargetOffsetX] float NOT NULL default 0, ADD COLUMN [SitTargetOffsetY] float NOT NULL default 0, ADD COLUMN [SitTargetOffsetZ] float NOT NULL default 0, ADD COLUMN [SitTargetOrientW] float NOT NULL default 0, ADD COLUMN [SitTargetOrientX] float NOT NULL default 0, ADD COLUMN [SitTargetOrientY] float NOT NULL default 0, ADD COLUMN [SitTargetOrientZ] float NOT NULL default 0;",
838 m_connection))
839 {
840 cmd.ExecuteNonQuery();
841 }
842 }
843
844 return prim;
845 }
846
847 /// <summary>
848 /// Build a prim inventory item from the persisted data.
849 /// </summary>
850 /// <param name="row"></param>
851 /// <returns></returns>
852 private TaskInventoryItem buildItem(DataRow row)
853 {
854 TaskInventoryItem taskItem = new TaskInventoryItem();
855
856 taskItem.ItemID = new LLUUID((String)row["itemID"]);
857 taskItem.ParentPartID = new LLUUID((String)row["primID"]);
858 taskItem.AssetID = new LLUUID((String)row["assetID"]);
859 taskItem.ParentID = new LLUUID((String)row["parentFolderID"]);
860
861 taskItem.InvType = Convert.ToInt32(row["invType"]);
862 taskItem.Type = Convert.ToInt32(row["assetType"]);
863
864 taskItem.Name = (String)row["name"];
865 taskItem.Description = (String)row["description"];
866 taskItem.CreationDate = Convert.ToUInt32(row["creationDate"]);
867 taskItem.CreatorID = new LLUUID((String)row["creatorID"]);
868 taskItem.OwnerID = new LLUUID((String)row["ownerID"]);
869 taskItem.LastOwnerID = new LLUUID((String)row["lastOwnerID"]);
870 taskItem.GroupID = new LLUUID((String)row["groupID"]);
871
872 taskItem.NextOwnerMask = Convert.ToUInt32(row["nextPermissions"]);
873 taskItem.OwnerMask = Convert.ToUInt32(row["currentPermissions"]);
874 taskItem.BaseMask = Convert.ToUInt32(row["basePermissions"]);
875 taskItem.EveryoneMask = Convert.ToUInt32(row["everyonePermissions"]);
876 taskItem.GroupMask = Convert.ToUInt32(row["groupPermissions"]);
877
878 return taskItem;
879 }
880
881 private LandData buildLandData(DataRow row)
882 {
883 LandData newData = new LandData();
884
885 newData.globalID = new LLUUID((String)row["UUID"]);
886 newData.localID = Convert.ToInt32(row["LocalLandID"]);
887
888 // Bitmap is a byte[512]
889 newData.landBitmapByteArray = (Byte[])row["Bitmap"];
890
891 newData.landName = (String)row["Name"];
892 newData.landDesc = (String)row["Description"];
893 newData.ownerID = (String)row["OwnerUUID"];
894 newData.isGroupOwned = Convert.ToBoolean(row["IsGroupOwned"]);
895 newData.area = Convert.ToInt32(row["Area"]);
896 newData.auctionID = Convert.ToUInt32(row["AuctionID"]); //Unemplemented
897 newData.category = (Parcel.ParcelCategory)Convert.ToInt32(row["Category"]);
898 //Enum libsecondlife.Parcel.ParcelCategory
899 newData.claimDate = Convert.ToInt32(row["ClaimDate"]);
900 newData.claimPrice = Convert.ToInt32(row["ClaimPrice"]);
901 newData.groupID = new LLUUID((String)row["GroupUUID"]);
902 newData.salePrice = Convert.ToInt32(row["SalePrice"]);
903 newData.landStatus = (Parcel.ParcelStatus)Convert.ToInt32(row["LandStatus"]);
904 //Enum. libsecondlife.Parcel.ParcelStatus
905 newData.landFlags = Convert.ToUInt32(row["LandFlags"]);
906 newData.landingType = Convert.ToByte(row["LandingType"]);
907 newData.mediaAutoScale = Convert.ToByte(row["MediaAutoScale"]);
908 newData.mediaID = new LLUUID((String)row["MediaTextureUUID"]);
909 newData.mediaURL = (String)row["MediaURL"];
910 newData.musicURL = (String)row["MusicURL"];
911 newData.passHours = Convert.ToSingle(row["PassHours"]);
912 newData.passPrice = Convert.ToInt32(row["PassPrice"]);
913 newData.snapshotID = (String)row["SnapshotUUID"];
914
915 newData.userLocation =
916 new LLVector3(Convert.ToSingle(row["UserLocationX"]), Convert.ToSingle(row["UserLocationY"]),
917 Convert.ToSingle(row["UserLocationZ"]));
918 newData.userLookAt =
919 new LLVector3(Convert.ToSingle(row["UserLookAtX"]), Convert.ToSingle(row["UserLookAtY"]),
920 Convert.ToSingle(row["UserLookAtZ"]));
921 newData.parcelAccessList = new List<ParcelManager.ParcelAccessEntry>();
922
923 return newData;
924 }
925
926 private ParcelManager.ParcelAccessEntry buildLandAccessData(DataRow row)
927 {
928 ParcelManager.ParcelAccessEntry entry = new ParcelManager.ParcelAccessEntry();
929 entry.AgentID = new LLUUID((string)row["AccessUUID"]);
930 entry.Flags = (ParcelManager.AccessList)Convert.ToInt32(row["Flags"]);
931 entry.Time = new DateTime();
932 return entry;
933 }
934
935 private Array serializeTerrain(double[,] val)
936 {
937 MemoryStream str = new MemoryStream(65536 * sizeof(double));
938 BinaryWriter bw = new BinaryWriter(str);
939
940 // TODO: COMPATIBILITY - Add byte-order conversions
941 for (int x = 0; x < 256; x++)
942 for (int y = 0; y < 256; y++)
943 bw.Write(val[x, y]);
944
945 return str.ToArray();
946 }
947
948 private void fillPrimRow(DataRow row, SceneObjectPart prim, LLUUID sceneGroupID, LLUUID regionUUID)
949 {
950 row["UUID"] = prim.UUID;
951 row["RegionUUID"] = regionUUID;
952 row["ParentID"] = prim.ParentID;
953 row["CreationDate"] = prim.CreationDate;
954 row["Name"] = prim.Name;
955 row["SceneGroupID"] = sceneGroupID;
956 // the UUID of the root part for this SceneObjectGroup
957 // various text fields
958 row["Text"] = prim.Text;
959 row["Description"] = prim.Description;
960 row["SitName"] = prim.SitName;
961 row["TouchName"] = prim.TouchName;
962 // permissions
963 row["ObjectFlags"] = prim.ObjectFlags;
964 row["CreatorID"] = prim.CreatorID;
965 row["OwnerID"] = prim.OwnerID;
966 row["GroupID"] = prim.GroupID;
967 row["LastOwnerID"] = prim.LastOwnerID;
968 row["OwnerMask"] = prim.OwnerMask;
969 row["NextOwnerMask"] = prim.NextOwnerMask;
970 row["GroupMask"] = prim.GroupMask;
971 row["EveryoneMask"] = prim.EveryoneMask;
972 row["BaseMask"] = prim.BaseMask;
973 // vectors
974 row["PositionX"] = prim.OffsetPosition.X;
975 row["PositionY"] = prim.OffsetPosition.Y;
976 row["PositionZ"] = prim.OffsetPosition.Z;
977 row["GroupPositionX"] = prim.GroupPosition.X;
978 row["GroupPositionY"] = prim.GroupPosition.Y;
979 row["GroupPositionZ"] = prim.GroupPosition.Z;
980 row["VelocityX"] = prim.Velocity.X;
981 row["VelocityY"] = prim.Velocity.Y;
982 row["VelocityZ"] = prim.Velocity.Z;
983 row["AngularVelocityX"] = prim.AngularVelocity.X;
984 row["AngularVelocityY"] = prim.AngularVelocity.Y;
985 row["AngularVelocityZ"] = prim.AngularVelocity.Z;
986 row["AccelerationX"] = prim.Acceleration.X;
987 row["AccelerationY"] = prim.Acceleration.Y;
988 row["AccelerationZ"] = prim.Acceleration.Z;
989 // quaternions
990 row["RotationX"] = prim.RotationOffset.X;
991 row["RotationY"] = prim.RotationOffset.Y;
992 row["RotationZ"] = prim.RotationOffset.Z;
993 row["RotationW"] = prim.RotationOffset.W;
994
995 try
996 {
997 // Sit target
998 LLVector3 sitTargetPos = prim.GetSitTargetPositionLL();
999 row["SitTargetOffsetX"] = sitTargetPos.X;
1000 row["SitTargetOffsetY"] = sitTargetPos.Y;
1001 row["SitTargetOffsetZ"] = sitTargetPos.Z;
1002
1003 LLQuaternion sitTargetOrient = prim.GetSitTargetOrientationLL();
1004 row["SitTargetOrientW"] = sitTargetOrient.W;
1005 row["SitTargetOrientX"] = sitTargetOrient.X;
1006 row["SitTargetOrientY"] = sitTargetOrient.Y;
1007 row["SitTargetOrientZ"] = sitTargetOrient.Z;
1008 }
1009 catch (Exception)
1010 {
1011 // Database table was created before we got here and needs to be created! :P
1012
1013 using (
1014 SqlCommand cmd =
1015 new SqlCommand(
1016 "ALTER TABLE [prims] ADD COLUMN [SitTargetOffsetX] float NOT NULL default 0, ADD COLUMN [SitTargetOffsetY] float NOT NULL default 0, ADD COLUMN [SitTargetOffsetZ] float NOT NULL default 0, ADD COLUMN [SitTargetOrientW] float NOT NULL default 0, ADD COLUMN [SitTargetOrientX] float NOT NULL default 0, ADD COLUMN [SitTargetOrientY] float NOT NULL default 0, ADD COLUMN [SitTargetOrientZ] float NOT NULL default 0;",
1017 m_connection))
1018 {
1019 cmd.ExecuteNonQuery();
1020 }
1021 }
1022 }
1023
1024 private void fillItemRow(DataRow row, TaskInventoryItem taskItem)
1025 {
1026 row["itemID"] = taskItem.ItemID;
1027 row["primID"] = taskItem.ParentPartID;
1028 row["assetID"] = taskItem.AssetID;
1029 row["parentFolderID"] = taskItem.ParentID;
1030
1031 row["invType"] = taskItem.InvType;
1032 row["assetType"] = taskItem.Type;
1033
1034 row["name"] = taskItem.Name;
1035 row["description"] = taskItem.Description;
1036 row["creationDate"] = taskItem.CreationDate;
1037 row["creatorID"] = taskItem.CreatorID;
1038 row["ownerID"] = taskItem.OwnerID;
1039 row["lastOwnerID"] = taskItem.LastOwnerID;
1040 row["groupID"] = taskItem.GroupID;
1041 row["nextPermissions"] = taskItem.NextOwnerMask;
1042 row["currentPermissions"] = taskItem.OwnerMask;
1043 row["basePermissions"] = taskItem.BaseMask;
1044 row["everyonePermissions"] = taskItem.EveryoneMask;
1045 row["groupPermissions"] = taskItem.GroupMask;
1046 }
1047
1048 private void fillLandRow(DataRow row, LandData land, LLUUID regionUUID)
1049 {
1050 row["UUID"] = land.globalID.UUID;
1051 row["RegionUUID"] = regionUUID.UUID;
1052 row["LocalLandID"] = land.localID;
1053
1054 // Bitmap is a byte[512]
1055 row["Bitmap"] = land.landBitmapByteArray;
1056
1057 row["Name"] = land.landName;
1058 row["Description"] = land.landDesc;
1059 row["OwnerUUID"] = land.ownerID.UUID;
1060 row["IsGroupOwned"] = land.isGroupOwned;
1061 row["Area"] = land.area;
1062 row["AuctionID"] = land.auctionID; //Unemplemented
1063 row["Category"] = land.category; //Enum libsecondlife.Parcel.ParcelCategory
1064 row["ClaimDate"] = land.claimDate;
1065 row["ClaimPrice"] = land.claimPrice;
1066 row["GroupUUID"] = land.groupID.UUID;
1067 row["SalePrice"] = land.salePrice;
1068 row["LandStatus"] = land.landStatus; //Enum. libsecondlife.Parcel.ParcelStatus
1069 row["LandFlags"] = land.landFlags;
1070 row["LandingType"] = land.landingType;
1071 row["MediaAutoScale"] = land.mediaAutoScale;
1072 row["MediaTextureUUID"] = land.mediaID.UUID;
1073 row["MediaURL"] = land.mediaURL;
1074 row["MusicURL"] = land.musicURL;
1075 row["PassHours"] = land.passHours;
1076 row["PassPrice"] = land.passPrice;
1077 row["SnapshotUUID"] = land.snapshotID.UUID;
1078 row["UserLocationX"] = land.userLocation.X;
1079 row["UserLocationY"] = land.userLocation.Y;
1080 row["UserLocationZ"] = land.userLocation.Z;
1081 row["UserLookAtX"] = land.userLookAt.X;
1082 row["UserLookAtY"] = land.userLookAt.Y;
1083 row["UserLookAtZ"] = land.userLookAt.Z;
1084 }
1085
1086 private void fillLandAccessRow(DataRow row, ParcelManager.ParcelAccessEntry entry, LLUUID parcelID)
1087 {
1088 row["LandUUID"] = parcelID.UUID;
1089 row["AccessUUID"] = entry.AgentID.UUID;
1090 row["Flags"] = entry.Flags;
1091 }
1092
1093 private PrimitiveBaseShape buildShape(DataRow row)
1094 {
1095 PrimitiveBaseShape s = new PrimitiveBaseShape();
1096 s.Scale = new LLVector3(
1097 Convert.ToSingle(row["ScaleX"]),
1098 Convert.ToSingle(row["ScaleY"]),
1099 Convert.ToSingle(row["ScaleZ"])
1100 );
1101 // paths
1102 s.PCode = Convert.ToByte(row["PCode"]);
1103 s.PathBegin = Convert.ToUInt16(row["PathBegin"]);
1104 s.PathEnd = Convert.ToUInt16(row["PathEnd"]);
1105 s.PathScaleX = Convert.ToByte(row["PathScaleX"]);
1106 s.PathScaleY = Convert.ToByte(row["PathScaleY"]);
1107 s.PathShearX = Convert.ToByte(row["PathShearX"]);
1108 s.PathShearY = Convert.ToByte(row["PathShearY"]);
1109 s.PathSkew = Convert.ToSByte(row["PathSkew"]);
1110 s.PathCurve = Convert.ToByte(row["PathCurve"]);
1111 s.PathRadiusOffset = Convert.ToSByte(row["PathRadiusOffset"]);
1112 s.PathRevolutions = Convert.ToByte(row["PathRevolutions"]);
1113 s.PathTaperX = Convert.ToSByte(row["PathTaperX"]);
1114 s.PathTaperY = Convert.ToSByte(row["PathTaperY"]);
1115 s.PathTwist = Convert.ToSByte(row["PathTwist"]);
1116 s.PathTwistBegin = Convert.ToSByte(row["PathTwistBegin"]);
1117 // profile
1118 s.ProfileBegin = Convert.ToUInt16(row["ProfileBegin"]);
1119 s.ProfileEnd = Convert.ToUInt16(row["ProfileEnd"]);
1120 s.ProfileCurve = Convert.ToByte(row["ProfileCurve"]);
1121 s.ProfileHollow = Convert.ToUInt16(row["ProfileHollow"]);
1122 s.State = Convert.ToByte(row["State"]);
1123
1124 byte[] textureEntry = (byte[])row["Texture"];
1125 s.TextureEntry = textureEntry;
1126
1127 s.ExtraParams = (byte[])row["ExtraParams"];
1128
1129 return s;
1130 }
1131
1132 private void fillShapeRow(DataRow row, SceneObjectPart prim)
1133 {
1134 PrimitiveBaseShape s = prim.Shape;
1135 row["UUID"] = prim.UUID;
1136 // shape is an enum
1137 row["Shape"] = 0;
1138 // vectors
1139 row["ScaleX"] = s.Scale.X;
1140 row["ScaleY"] = s.Scale.Y;
1141 row["ScaleZ"] = s.Scale.Z;
1142 // paths
1143 row["PCode"] = s.PCode;
1144 row["PathBegin"] = s.PathBegin;
1145 row["PathEnd"] = s.PathEnd;
1146 row["PathScaleX"] = s.PathScaleX;
1147 row["PathScaleY"] = s.PathScaleY;
1148 row["PathShearX"] = s.PathShearX;
1149 row["PathShearY"] = s.PathShearY;
1150 row["PathSkew"] = s.PathSkew;
1151 row["PathCurve"] = s.PathCurve;
1152 row["PathRadiusOffset"] = s.PathRadiusOffset;
1153 row["PathRevolutions"] = s.PathRevolutions;
1154 row["PathTaperX"] = s.PathTaperX;
1155 row["PathTaperY"] = s.PathTaperY;
1156 row["PathTwist"] = s.PathTwist;
1157 row["PathTwistBegin"] = s.PathTwistBegin;
1158 // profile
1159 row["ProfileBegin"] = s.ProfileBegin;
1160 row["ProfileEnd"] = s.ProfileEnd;
1161 row["ProfileCurve"] = s.ProfileCurve;
1162 row["ProfileHollow"] = s.ProfileHollow;
1163 row["State"] = s.State;
1164 row["Texture"] = s.TextureEntry;
1165 row["ExtraParams"] = s.ExtraParams;
1166 }
1167
1168 private void addPrim(SceneObjectPart prim, LLUUID sceneGroupID, LLUUID regionUUID)
1169 {
1170 DataTable prims = m_dataSet.Tables["prims"];
1171 DataTable shapes = m_dataSet.Tables["primshapes"];
1172
1173 DataRow primRow = prims.Rows.Find(prim.UUID);
1174 if (primRow == null)
1175 {
1176 primRow = prims.NewRow();
1177 fillPrimRow(primRow, prim, sceneGroupID, regionUUID);
1178 prims.Rows.Add(primRow);
1179 }
1180 else
1181 {
1182 fillPrimRow(primRow, prim, sceneGroupID, regionUUID);
1183 }
1184
1185 DataRow shapeRow = shapes.Rows.Find(prim.UUID);
1186 if (shapeRow == null)
1187 {
1188 shapeRow = shapes.NewRow();
1189 fillShapeRow(shapeRow, prim);
1190 shapes.Rows.Add(shapeRow);
1191 }
1192 else
1193 {
1194 fillShapeRow(shapeRow, prim);
1195 }
1196 }
1197
1198 // see IRegionDatastore
1199 public void StorePrimInventory(LLUUID primID, ICollection<TaskInventoryItem> items)
1200 {
1201 if (!persistPrimInventories)
1202 return;
1203
1204 m_log.InfoFormat("[DATASTORE]: Persisting Prim Inventory with prim ID {0}", primID);
1205
1206 // For now, we're just going to crudely remove all the previous inventory items
1207 // no matter whether they have changed or not, and replace them with the current set.
1208 lock (m_dataSet)
1209 {
1210 RemoveItems(primID);
1211
1212 // repalce with current inventory details
1213 foreach (TaskInventoryItem newItem in items)
1214 {
1215 // m_log.InfoFormat(
1216 // "[DATASTORE]: " +
1217 // "Adding item {0}, {1} to prim ID {2}",
1218 // newItem.Name, newItem.ItemID, newItem.ParentPartID);
1219
1220 DataRow newItemRow = m_itemsTable.NewRow();
1221 fillItemRow(newItemRow, newItem);
1222 m_itemsTable.Rows.Add(newItemRow);
1223 }
1224 }
1225
1226 Commit();
1227 }
1228
1229 /***********************************************************************
1230 *
1231 * SQL Statement Creation Functions
1232 *
1233 * These functions create SQL statements for update, insert, and create.
1234 * They can probably be factored later to have a db independant
1235 * portion and a db specific portion
1236 *
1237 **********************************************************************/
1238
1239 private SqlCommand createInsertCommand(string table, DataTable dt)
1240 {
1241 /**
1242 * This is subtle enough to deserve some commentary.
1243 * Instead of doing *lots* and *lots of hardcoded strings
1244 * for database definitions we'll use the fact that
1245 * realistically all insert statements look like "insert
1246 * into A(b, c) values(:b, :c) on the parameterized query
1247 * front. If we just have a list of b, c, etc... we can
1248 * generate these strings instead of typing them out.
1249 */
1250 string[] cols = new string[dt.Columns.Count];
1251 for (int i = 0; i < dt.Columns.Count; i++)
1252 {
1253 DataColumn col = dt.Columns[i];
1254 cols[i] = col.ColumnName;
1255 }
1256
1257 string sql = "insert into " + table + "(";
1258 sql += String.Join(", ", cols);
1259 // important, the first ':' needs to be here, the rest get added in the join
1260 sql += ") values (@";
1261 sql += String.Join(", @", cols);
1262 sql += ")";
1263 SqlCommand cmd = new SqlCommand(sql);
1264
1265 // this provides the binding for all our parameters, so
1266 // much less code than it used to be
1267 foreach (DataColumn col in dt.Columns)
1268 {
1269 cmd.Parameters.Add(createSqlParameter(col.ColumnName, col.DataType));
1270 }
1271 return cmd;
1272 }
1273
1274 private SqlCommand createUpdateCommand(string table, string pk, DataTable dt)
1275 {
1276 string sql = "update " + table + " set ";
1277 string subsql = String.Empty;
1278 foreach (DataColumn col in dt.Columns)
1279 {
1280 if (subsql.Length > 0)
1281 {
1282 // a map function would rock so much here
1283 subsql += ", ";
1284 }
1285 subsql += col.ColumnName + "= @" + col.ColumnName;
1286 }
1287 sql += subsql;
1288 sql += " where " + pk;
1289 SqlCommand cmd = new SqlCommand(sql);
1290
1291 // this provides the binding for all our parameters, so
1292 // much less code than it used to be
1293
1294 foreach (DataColumn col in dt.Columns)
1295 {
1296 cmd.Parameters.Add(createSqlParameter(col.ColumnName, col.DataType));
1297 }
1298 return cmd;
1299 }
1300
1301 private string defineTable(DataTable dt)
1302 {
1303 string sql = "create table " + dt.TableName + "(";
1304 string subsql = String.Empty;
1305 foreach (DataColumn col in dt.Columns)
1306 {
1307 if (subsql.Length > 0)
1308 {
1309 // a map function would rock so much here
1310 subsql += ",\n";
1311 }
1312 subsql += col.ColumnName + " " + MSSQLManager.SqlType(col.DataType);
1313 if (dt.PrimaryKey.Length > 0 && col == dt.PrimaryKey[0])
1314 {
1315 subsql += " primary key";
1316 }
1317 }
1318 sql += subsql;
1319 sql += ")";
1320
1321 return sql;
1322 }
1323
1324 /***********************************************************************
1325 *
1326 * Database Binding functions
1327 *
1328 * These will be db specific due to typing, and minor differences
1329 * in databases.
1330 *
1331 **********************************************************************/
1332
1333 ///<summary>
1334 /// This is a convenience function that collapses 5 repetitive
1335 /// lines for defining SqlParameters to 2 parameters:
1336 /// column name and database type.
1337 ///
1338 /// It assumes certain conventions like :param as the param
1339 /// name to replace in parametrized queries, and that source
1340 /// version is always current version, both of which are fine
1341 /// for us.
1342 ///</summary>
1343 ///<returns>a built Sql parameter</returns>
1344 private SqlParameter createSqlParameter(string name, Type type)
1345 {
1346 SqlParameter param = new SqlParameter();
1347 param.ParameterName = "@" + name;
1348 param.DbType = dbtypeFromType(type);
1349 param.SourceColumn = name;
1350 param.SourceVersion = DataRowVersion.Current;
1351 return param;
1352 }
1353
1354// TODO: unused
1355// private SqlParameter createParamWithValue(string name, Type type, Object o)
1356// {
1357// SqlParameter param = createSqlParameter(name, type);
1358// param.Value = o;
1359// return param;
1360// }
1361
1362 private void setupPrimCommands(SqlDataAdapter da, SqlConnection conn)
1363 {
1364 da.InsertCommand = createInsertCommand("prims", m_dataSet.Tables["prims"]);
1365 da.InsertCommand.Connection = conn;
1366
1367 da.UpdateCommand = createUpdateCommand("prims", "UUID=@UUID", m_dataSet.Tables["prims"]);
1368 da.UpdateCommand.Connection = conn;
1369
1370 SqlCommand delete = new SqlCommand("delete from prims where UUID = @UUID");
1371 delete.Parameters.Add(createSqlParameter("UUID", typeof(String)));
1372 delete.Connection = conn;
1373 da.DeleteCommand = delete;
1374 }
1375
1376 private void SetupItemsCommands(SqlDataAdapter da, SqlConnection conn)
1377 {
1378 da.InsertCommand = createInsertCommand("primitems", m_itemsTable);
1379 da.InsertCommand.Connection = conn;
1380
1381 da.UpdateCommand = createUpdateCommand("primitems", "itemID = @itemID", m_itemsTable);
1382 da.UpdateCommand.Connection = conn;
1383
1384 SqlCommand delete = new SqlCommand("delete from primitems where itemID = @itemID");
1385 delete.Parameters.Add(createSqlParameter("itemID", typeof(String)));
1386 delete.Connection = conn;
1387 da.DeleteCommand = delete;
1388 }
1389
1390 private void setupTerrainCommands(SqlDataAdapter da, SqlConnection conn)
1391 {
1392 da.InsertCommand = createInsertCommand("terrain", m_dataSet.Tables["terrain"]);
1393 da.InsertCommand.Connection = conn;
1394 }
1395
1396 private void setupLandCommands(SqlDataAdapter da, SqlConnection conn)
1397 {
1398 da.InsertCommand = createInsertCommand("land", m_dataSet.Tables["land"]);
1399 da.InsertCommand.Connection = conn;
1400
1401 da.UpdateCommand = createUpdateCommand("land", "UUID=@UUID", m_dataSet.Tables["land"]);
1402 da.UpdateCommand.Connection = conn;
1403 }
1404
1405 private void setupLandAccessCommands(SqlDataAdapter da, SqlConnection conn)
1406 {
1407 da.InsertCommand = createInsertCommand("landaccesslist", m_dataSet.Tables["landaccesslist"]);
1408 da.InsertCommand.Connection = conn;
1409 }
1410
1411 private void setupShapeCommands(SqlDataAdapter da, SqlConnection conn)
1412 {
1413 da.InsertCommand = createInsertCommand("primshapes", m_dataSet.Tables["primshapes"]);
1414 da.InsertCommand.Connection = conn;
1415
1416 da.UpdateCommand = createUpdateCommand("primshapes", "UUID=@UUID", m_dataSet.Tables["primshapes"]);
1417 da.UpdateCommand.Connection = conn;
1418
1419 SqlCommand delete = new SqlCommand("delete from primshapes where UUID = @UUID");
1420 delete.Parameters.Add(createSqlParameter("UUID", typeof(String)));
1421 delete.Connection = conn;
1422 da.DeleteCommand = delete;
1423 }
1424
1425 private void InitDB(SqlConnection conn)
1426 {
1427 string createPrims = defineTable(createPrimTable());
1428 string createShapes = defineTable(createShapeTable());
1429 string createItems = defineTable(createItemsTable());
1430 string createTerrain = defineTable(createTerrainTable());
1431 string createLand = defineTable(createLandTable());
1432 string createLandAccessList = defineTable(createLandAccessListTable());
1433
1434 SqlCommand pcmd = new SqlCommand(createPrims, conn);
1435 SqlCommand scmd = new SqlCommand(createShapes, conn);
1436 SqlCommand icmd = new SqlCommand(createItems, conn);
1437 SqlCommand tcmd = new SqlCommand(createTerrain, conn);
1438 SqlCommand lcmd = new SqlCommand(createLand, conn);
1439 SqlCommand lalcmd = new SqlCommand(createLandAccessList, conn);
1440
1441 conn.Open();
1442 try
1443 {
1444 pcmd.ExecuteNonQuery();
1445 }
1446 catch (SqlException e)
1447 {
1448 m_log.WarnFormat("[MSSql]: Primitives Table Already Exists: {0}", e);
1449 }
1450
1451 try
1452 {
1453 scmd.ExecuteNonQuery();
1454 }
1455 catch (SqlException e)
1456 {
1457 m_log.WarnFormat("[MSSql]: Shapes Table Already Exists: {0}", e);
1458 }
1459
1460 try
1461 {
1462 icmd.ExecuteNonQuery();
1463 }
1464 catch (SqlException e)
1465 {
1466 m_log.WarnFormat("[MSSql]: Items Table Already Exists: {0}", e);
1467 }
1468
1469 try
1470 {
1471 tcmd.ExecuteNonQuery();
1472 }
1473 catch (SqlException e)
1474 {
1475 m_log.WarnFormat("[MSSql]: Terrain Table Already Exists: {0}", e);
1476 }
1477
1478 try
1479 {
1480 lcmd.ExecuteNonQuery();
1481 }
1482 catch (SqlException e)
1483 {
1484 m_log.WarnFormat("[MSSql]: Land Table Already Exists: {0}", e);
1485 }
1486
1487 try
1488 {
1489 lalcmd.ExecuteNonQuery();
1490 }
1491 catch (SqlException e)
1492 {
1493 m_log.WarnFormat("[MSSql]: LandAccessList Table Already Exists: {0}", e);
1494 }
1495 conn.Close();
1496 }
1497
1498 private bool TestTables(SqlConnection conn)
1499 {
1500 SqlCommand primSelectCmd = new SqlCommand(m_primSelect, conn);
1501 SqlDataAdapter pDa = new SqlDataAdapter(primSelectCmd);
1502 SqlCommand shapeSelectCmd = new SqlCommand(m_shapeSelect, conn);
1503 SqlDataAdapter sDa = new SqlDataAdapter(shapeSelectCmd);
1504 SqlCommand itemsSelectCmd = new SqlCommand(m_itemsSelect, conn);
1505 SqlDataAdapter iDa = new SqlDataAdapter(itemsSelectCmd);
1506 SqlCommand terrainSelectCmd = new SqlCommand(m_terrainSelect, conn);
1507 SqlDataAdapter tDa = new SqlDataAdapter(terrainSelectCmd);
1508 SqlCommand landSelectCmd = new SqlCommand(m_landSelect, conn);
1509 SqlDataAdapter lDa = new SqlDataAdapter(landSelectCmd);
1510 SqlCommand landAccessListSelectCmd = new SqlCommand(m_landAccessListSelect, conn);
1511 SqlDataAdapter lalDa = new SqlDataAdapter(landAccessListSelectCmd);
1512
1513 DataSet tmpDS = new DataSet();
1514 try
1515 {
1516 pDa.Fill(tmpDS, "prims");
1517 sDa.Fill(tmpDS, "primshapes");
1518
1519 if (persistPrimInventories)
1520 iDa.Fill(tmpDS, "primitems");
1521
1522 tDa.Fill(tmpDS, "terrain");
1523 lDa.Fill(tmpDS, "land");
1524 lalDa.Fill(tmpDS, "landaccesslist");
1525 }
1526 catch (SqlException)
1527 {
1528 m_log.Info("[DATASTORE]: MySql Database doesn't exist... creating");
1529 InitDB(conn);
1530 }
1531
1532 pDa.Fill(tmpDS, "prims");
1533 sDa.Fill(tmpDS, "primshapes");
1534
1535 if (persistPrimInventories)
1536 iDa.Fill(tmpDS, "primitems");
1537
1538 tDa.Fill(tmpDS, "terrain");
1539 lDa.Fill(tmpDS, "land");
1540 lalDa.Fill(tmpDS, "landaccesslist");
1541
1542 foreach (DataColumn col in createPrimTable().Columns)
1543 {
1544 if (!tmpDS.Tables["prims"].Columns.Contains(col.ColumnName))
1545 {
1546 m_log.Info("[DATASTORE]: Missing required column:" + col.ColumnName);
1547 return false;
1548 }
1549 }
1550
1551 foreach (DataColumn col in createShapeTable().Columns)
1552 {
1553 if (!tmpDS.Tables["primshapes"].Columns.Contains(col.ColumnName))
1554 {
1555 m_log.Info("[DATASTORE]: Missing required column:" + col.ColumnName);
1556 return false;
1557 }
1558 }
1559
1560 // XXX primitems should probably go here eventually
1561
1562 foreach (DataColumn col in createTerrainTable().Columns)
1563 {
1564 if (!tmpDS.Tables["terrain"].Columns.Contains(col.ColumnName))
1565 {
1566 m_log.Info("[DATASTORE]: Missing require column:" + col.ColumnName);
1567 return false;
1568 }
1569 }
1570
1571 foreach (DataColumn col in createLandTable().Columns)
1572 {
1573 if (!tmpDS.Tables["land"].Columns.Contains(col.ColumnName))
1574 {
1575 m_log.Info("[DATASTORE]: Missing require column:" + col.ColumnName);
1576 return false;
1577 }
1578 }
1579
1580 foreach (DataColumn col in createLandAccessListTable().Columns)
1581 {
1582 if (!tmpDS.Tables["landaccesslist"].Columns.Contains(col.ColumnName))
1583 {
1584 m_log.Info("[DATASTORE]: Missing require column:" + col.ColumnName);
1585 return false;
1586 }
1587 }
1588
1589 return true;
1590 }
1591
1592 /***********************************************************************
1593 *
1594 * Type conversion functions
1595 *
1596 **********************************************************************/
1597
1598 private DbType dbtypeFromType(Type type)
1599 {
1600 if (type == typeof(String))
1601 {
1602 return DbType.String;
1603 }
1604 else if (type == typeof(Int32))
1605 {
1606 return DbType.Int32;
1607 }
1608 else if (type == typeof(Double))
1609 {
1610 return DbType.Double;
1611 }
1612 else if (type == typeof(Byte[]))
1613 {
1614 return DbType.Binary;
1615 }
1616 else
1617 {
1618 return DbType.String;
1619 }
1620 }
1621 }
1622}
diff --git a/OpenSim/Data/MSSQL/MSSQLGridData.cs b/OpenSim/Data/MSSQL/MSSQLGridData.cs
new file mode 100644
index 0000000..9bd8acc
--- /dev/null
+++ b/OpenSim/Data/MSSQL/MSSQLGridData.cs
@@ -0,0 +1,366 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSim Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections.Generic;
30using System.Data;
31using System.Security.Cryptography;
32using System.Text;
33using libsecondlife;
34using OpenSim.Framework.Console;
35
36namespace OpenSim.Framework.Data.MSSQL
37{
38 /// <summary>
39 /// A grid data interface for Microsoft SQL Server
40 /// </summary>
41 public class MSSQLGridData : GridDataBase
42 {
43 private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
44
45 /// <summary>
46 /// Database manager
47 /// </summary>
48 private MSSQLManager database;
49
50 private string m_regionsTableName;
51
52 /// <summary>
53 /// Initialises the Grid Interface
54 /// </summary>
55 override public void Initialise()
56 {
57 IniFile iniFile = new IniFile("mssql_connection.ini");
58
59 string settingDataSource = iniFile.ParseFileReadValue("data_source");
60 string settingInitialCatalog = iniFile.ParseFileReadValue("initial_catalog");
61 string settingPersistSecurityInfo = iniFile.ParseFileReadValue("persist_security_info");
62 string settingUserId = iniFile.ParseFileReadValue("user_id");
63 string settingPassword = iniFile.ParseFileReadValue("password");
64
65 m_regionsTableName = iniFile.ParseFileReadValue("regionstablename");
66 if (m_regionsTableName == null)
67 {
68 m_regionsTableName = "regions";
69 }
70
71 database =
72 new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId,
73 settingPassword);
74
75 TestTables();
76 }
77
78 private void TestTables()
79 {
80 IDbCommand cmd = database.Query("SELECT TOP 1 * FROM "+m_regionsTableName, new Dictionary<string, string>());
81
82 try
83 {
84 cmd.ExecuteNonQuery();
85 cmd.Dispose();
86 }
87 catch (Exception)
88 {
89 m_log.Info("[DATASTORE]: MSSQL Database doesn't exist... creating");
90 database.ExecuteResourceSql("Mssql-regions.sql");
91 }
92 }
93
94 /// <summary>
95 /// Shuts down the grid interface
96 /// </summary>
97 override public void Close()
98 {
99 database.Close();
100 }
101
102 /// <summary>
103 /// Returns the storage system name
104 /// </summary>
105 /// <returns>A string containing the storage system name</returns>
106 override public string getName()
107 {
108 return "Sql OpenGridData";
109 }
110
111 /// <summary>
112 /// Returns the storage system version
113 /// </summary>
114 /// <returns>A string containing the storage system version</returns>
115 override public string getVersion()
116 {
117 return "0.1";
118 }
119
120 /// <summary>
121 /// Returns a list of regions within the specified ranges
122 /// </summary>
123 /// <param name="a">minimum X coordinate</param>
124 /// <param name="b">minimum Y coordinate</param>
125 /// <param name="c">maximum X coordinate</param>
126 /// <param name="d">maximum Y coordinate</param>
127 /// <returns>An array of region profiles</returns>
128 override public RegionProfileData[] GetProfilesInRange(uint a, uint b, uint c, uint d)
129 {
130 return null;
131 }
132
133 /// <summary>
134 /// Returns a sim profile from its location
135 /// </summary>
136 /// <param name="handle">Region location handle</param>
137 /// <returns>Sim profile</returns>
138 override public RegionProfileData GetProfileByHandle(ulong handle)
139 {
140 IDataReader reader = null;
141 try
142 {
143 Dictionary<string, string> param = new Dictionary<string, string>();
144 param["handle"] = handle.ToString();
145 IDbCommand result = database.Query("SELECT * FROM " + m_regionsTableName + " WHERE regionHandle = @handle", param);
146 reader = result.ExecuteReader();
147
148 RegionProfileData row = database.getRegionRow(reader);
149 reader.Close();
150 result.Dispose();
151
152 return row;
153 }
154 catch (Exception)
155 {
156 if (reader != null)
157 {
158 reader.Close();
159 }
160 }
161 return null;
162 }
163
164 /// <summary>
165 /// Returns a sim profile from its UUID
166 /// </summary>
167 /// <param name="uuid">The region UUID</param>
168 /// <returns>The sim profile</returns>
169 override public RegionProfileData GetProfileByLLUUID(LLUUID uuid)
170 {
171 Dictionary<string, string> param = new Dictionary<string, string>();
172 param["uuid"] = uuid.ToString();
173 IDbCommand result = database.Query("SELECT * FROM " + m_regionsTableName + " WHERE uuid = @uuid", param);
174 IDataReader reader = result.ExecuteReader();
175
176 RegionProfileData row = database.getRegionRow(reader);
177 reader.Close();
178 result.Dispose();
179
180 return row;
181 }
182
183 /// <summary>
184 /// Returns a sim profile from it's Region name string
185 /// </summary>
186 /// <param name="uuid">The region name search query</param>
187 /// <returns>The sim profile</returns>
188 override public RegionProfileData GetProfileByString(string regionName)
189 {
190 if (regionName.Length > 2)
191 {
192 try
193 {
194 lock (database)
195 {
196 Dictionary<string, string> param = new Dictionary<string, string>();
197 // Add % because this is a like query.
198 param["?regionName"] = regionName + "%";
199 // Order by statement will return shorter matches first. Only returns one record or no record.
200 IDbCommand result = database.Query("SELECT top 1 * FROM " + m_regionsTableName + " WHERE regionName like ?regionName order by regionName", param);
201 IDataReader reader = result.ExecuteReader();
202
203 RegionProfileData row = database.getRegionRow(reader);
204 reader.Close();
205 result.Dispose();
206
207 return row;
208 }
209 }
210 catch (Exception e)
211 {
212 database.Reconnect();
213 m_log.Error(e.ToString());
214 return null;
215 }
216 }
217 else
218 {
219 m_log.Error("[DATABASE]: Searched for a Region Name shorter then 3 characters");
220 return null;
221 }
222 }
223
224 /// <summary>
225 /// Adds a new specified region to the database
226 /// </summary>
227 /// <param name="profile">The profile to add</param>
228 /// <returns>A dataresponse enum indicating success</returns>
229 override public DataResponse AddProfile(RegionProfileData profile)
230 {
231 try
232 {
233 if (GetProfileByLLUUID(profile.UUID) != null)
234 {
235 return DataResponse.RESPONSE_OK;
236 }
237 }
238 catch (Exception)
239 {
240 System.Console.WriteLine("No regions found. Create new one.");
241 }
242
243 if (insertRegionRow(profile))
244 {
245 return DataResponse.RESPONSE_OK;
246 }
247 else
248 {
249 return DataResponse.RESPONSE_ERROR;
250 }
251 }
252
253 /// <summary>
254 /// Creates a new region in the database
255 /// </summary>
256 /// <param name="profile">The region profile to insert</param>
257 /// <returns>Successful?</returns>
258 public bool insertRegionRow(RegionProfileData profile)
259 {
260 //Insert new region
261 string sql =
262 "INSERT INTO " + m_regionsTableName + " ([regionHandle], [regionName], [uuid], [regionRecvKey], [regionSecret], [regionSendKey], [regionDataURI], ";
263 sql +=
264 "[serverIP], [serverPort], [serverURI], [locX], [locY], [locZ], [eastOverrideHandle], [westOverrideHandle], [southOverrideHandle], [northOverrideHandle], [regionAssetURI], [regionAssetRecvKey], ";
265 sql +=
266 "[regionAssetSendKey], [regionUserURI], [regionUserRecvKey], [regionUserSendKey], [regionMapTexture], [serverHttpPort], [serverRemotingPort], [owner_uuid]) VALUES ";
267
268 sql += "(@regionHandle, @regionName, @uuid, @regionRecvKey, @regionSecret, @regionSendKey, @regionDataURI, ";
269 sql +=
270 "@serverIP, @serverPort, @serverURI, @locX, @locY, @locZ, @eastOverrideHandle, @westOverrideHandle, @southOverrideHandle, @northOverrideHandle, @regionAssetURI, @regionAssetRecvKey, ";
271 sql +=
272 "@regionAssetSendKey, @regionUserURI, @regionUserRecvKey, @regionUserSendKey, @regionMapTexture, @serverHttpPort, @serverRemotingPort, @owner_uuid);";
273
274 Dictionary<string, string> parameters = new Dictionary<string, string>();
275
276 parameters["regionHandle"] = profile.regionHandle.ToString();
277 parameters["regionName"] = profile.regionName;
278 parameters["uuid"] = profile.UUID.ToString();
279 parameters["regionRecvKey"] = profile.regionRecvKey;
280 parameters["regionSecret"] = profile.regionSecret;
281 parameters["regionSendKey"] = profile.regionSendKey;
282 parameters["regionDataURI"] = profile.regionDataURI;
283 parameters["serverIP"] = profile.serverIP;
284 parameters["serverPort"] = profile.serverPort.ToString();
285 parameters["serverURI"] = profile.serverURI;
286 parameters["locX"] = profile.regionLocX.ToString();
287 parameters["locY"] = profile.regionLocY.ToString();
288 parameters["locZ"] = profile.regionLocZ.ToString();
289 parameters["eastOverrideHandle"] = profile.regionEastOverrideHandle.ToString();
290 parameters["westOverrideHandle"] = profile.regionWestOverrideHandle.ToString();
291 parameters["northOverrideHandle"] = profile.regionNorthOverrideHandle.ToString();
292 parameters["southOverrideHandle"] = profile.regionSouthOverrideHandle.ToString();
293 parameters["regionAssetURI"] = profile.regionAssetURI;
294 parameters["regionAssetRecvKey"] = profile.regionAssetRecvKey;
295 parameters["regionAssetSendKey"] = profile.regionAssetSendKey;
296 parameters["regionUserURI"] = profile.regionUserURI;
297 parameters["regionUserRecvKey"] = profile.regionUserRecvKey;
298 parameters["regionUserSendKey"] = profile.regionUserSendKey;
299 parameters["regionMapTexture"] = profile.regionMapTextureID.ToString();
300 parameters["serverHttpPort"] = profile.httpPort.ToString();
301 parameters["serverRemotingPort"] = profile.remotingPort.ToString();
302 parameters["owner_uuid"] = profile.owner_uuid.ToString();
303
304 bool returnval = false;
305
306 try
307 {
308 IDbCommand result = database.Query(sql, parameters);
309
310 if (result.ExecuteNonQuery() == 1)
311 returnval = true;
312
313 result.Dispose();
314 }
315 catch (Exception e)
316 {
317 m_log.Error("MSSQLManager : " + e.ToString());
318 }
319
320 return returnval;
321 }
322
323 /// <summary>
324 /// DEPRECATED. Attempts to authenticate a region by comparing a shared secret.
325 /// </summary>
326 /// <param name="uuid">The UUID of the challenger</param>
327 /// <param name="handle">The attempted regionHandle of the challenger</param>
328 /// <param name="authkey">The secret</param>
329 /// <returns>Whether the secret and regionhandle match the database entry for UUID</returns>
330 override public bool AuthenticateSim(LLUUID uuid, ulong handle, string authkey)
331 {
332 bool throwHissyFit = false; // Should be true by 1.0
333
334 if (throwHissyFit)
335 throw new Exception("CRYPTOWEAK AUTHENTICATE: Refusing to authenticate due to replay potential.");
336
337 RegionProfileData data = GetProfileByLLUUID(uuid);
338
339 return (handle == data.regionHandle && authkey == data.regionSecret);
340 }
341
342 /// <summary>
343 /// NOT YET FUNCTIONAL. Provides a cryptographic authentication of a region
344 /// </summary>
345 /// <remarks>This requires a security audit.</remarks>
346 /// <param name="uuid"></param>
347 /// <param name="handle"></param>
348 /// <param name="authhash"></param>
349 /// <param name="challenge"></param>
350 /// <returns></returns>
351 public bool AuthenticateSim(LLUUID uuid, ulong handle, string authhash, string challenge)
352 {
353 SHA512Managed HashProvider = new SHA512Managed();
354 ASCIIEncoding TextProvider = new ASCIIEncoding();
355
356 byte[] stream = TextProvider.GetBytes(uuid.ToString() + ":" + handle.ToString() + ":" + challenge);
357 byte[] hash = HashProvider.ComputeHash(stream);
358 return false;
359 }
360
361 override public ReservationData GetReservationAtPoint(uint x, uint y)
362 {
363 return null;
364 }
365 }
366}
diff --git a/OpenSim/Data/MSSQL/MSSQLInventoryData.cs b/OpenSim/Data/MSSQL/MSSQLInventoryData.cs
new file mode 100644
index 0000000..1e99e51
--- /dev/null
+++ b/OpenSim/Data/MSSQL/MSSQLInventoryData.cs
@@ -0,0 +1,728 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSim Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections.Generic;
30using System.Data;
31using System.Data.SqlClient;
32using libsecondlife;
33using OpenSim.Framework.Console;
34
35namespace OpenSim.Framework.Data.MSSQL
36{
37 /// <summary>
38 /// A MySQL interface for the inventory server
39 /// </summary>
40 public class MSSQLInventoryData : IInventoryData
41 {
42 private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
43
44 /// <summary>
45 /// The database manager
46 /// </summary>
47 private MSSQLManager database;
48
49 /// <summary>
50 /// Loads and initialises this database plugin
51 /// </summary>
52 public void Initialise()
53 {
54 IniFile GridDataMySqlFile = new IniFile("mssql_connection.ini");
55 string settingDataSource = GridDataMySqlFile.ParseFileReadValue("data_source");
56 string settingInitialCatalog = GridDataMySqlFile.ParseFileReadValue("initial_catalog");
57 string settingPersistSecurityInfo = GridDataMySqlFile.ParseFileReadValue("persist_security_info");
58 string settingUserId = GridDataMySqlFile.ParseFileReadValue("user_id");
59 string settingPassword = GridDataMySqlFile.ParseFileReadValue("password");
60
61 database =
62 new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId,
63 settingPassword);
64 TestTables();
65 }
66
67 #region Test and initialization code
68
69 private void UpgradeFoldersTable(string tableName)
70 {
71 // null as the version, indicates that the table didn't exist
72 if (tableName == null)
73 {
74 database.ExecuteResourceSql("CreateFoldersTable.sql");
75 //database.ExecuteResourceSql("UpgradeFoldersTableToVersion2.sql");
76 return;
77 }
78 }
79
80 private void UpgradeItemsTable(string tableName)
81 {
82 // null as the version, indicates that the table didn't exist
83 if (tableName == null)
84 {
85 database.ExecuteResourceSql("CreateItemsTable.sql");
86 //database.ExecuteResourceSql("UpgradeItemsTableToVersion2.sql");
87 return;
88 }
89 }
90
91 private void TestTables()
92 {
93 Dictionary<string, string> tableList = new Dictionary<string, string>();
94
95 tableList["inventoryfolders"] = null;
96 tableList["inventoryitems"] = null;
97
98 database.GetTableVersion(tableList);
99
100 UpgradeFoldersTable(tableList["inventoryfolders"]);
101 UpgradeItemsTable(tableList["inventoryitems"]);
102 }
103
104 #endregion
105
106 /// <summary>
107 /// The name of this DB provider
108 /// </summary>
109 /// <returns>Name of DB provider</returns>
110 public string getName()
111 {
112 return "MSSQL Inventory Data Interface";
113 }
114
115 /// <summary>
116 /// Closes this DB provider
117 /// </summary>
118 public void Close()
119 {
120 // Do nothing.
121 }
122
123 /// <summary>
124 /// Returns the version of this DB provider
125 /// </summary>
126 /// <returns>A string containing the DB provider</returns>
127 public string getVersion()
128 {
129 return database.getVersion();
130 }
131
132 /// <summary>
133 /// Returns a list of items in a specified folder
134 /// </summary>
135 /// <param name="folderID">The folder to search</param>
136 /// <returns>A list containing inventory items</returns>
137 public List<InventoryItemBase> getInventoryInFolder(LLUUID folderID)
138 {
139 try
140 {
141 lock (database)
142 {
143 List<InventoryItemBase> items = new List<InventoryItemBase>();
144
145 Dictionary<string, string> param = new Dictionary<string, string>();
146 param["parentFolderID"] = folderID.ToString();
147
148 IDbCommand result =
149 database.Query("SELECT * FROM inventoryitems WHERE parentFolderID = @parentFolderID", param);
150 IDataReader reader = result.ExecuteReader();
151
152 while (reader.Read())
153 items.Add(readInventoryItem(reader));
154
155 reader.Close();
156 result.Dispose();
157
158 return items;
159 }
160 }
161 catch (Exception e)
162 {
163 database.Reconnect();
164 m_log.Error(e.ToString());
165 return null;
166 }
167 }
168
169 /// <summary>
170 /// Returns a list of the root folders within a users inventory
171 /// </summary>
172 /// <param name="user">The user whos inventory is to be searched</param>
173 /// <returns>A list of folder objects</returns>
174 public List<InventoryFolderBase> getUserRootFolders(LLUUID user)
175 {
176 try
177 {
178 lock (database)
179 {
180 Dictionary<string, string> param = new Dictionary<string, string>();
181 param["uuid"] = user.ToString();
182 param["zero"] = LLUUID.Zero.ToString();
183
184 IDbCommand result =
185 database.Query(
186 "SELECT * FROM inventoryfolders WHERE parentFolderID = @zero AND agentID = @uuid", param);
187 IDataReader reader = result.ExecuteReader();
188
189 List<InventoryFolderBase> items = new List<InventoryFolderBase>();
190 while (reader.Read())
191 items.Add(readInventoryFolder(reader));
192
193
194 reader.Close();
195 result.Dispose();
196
197 return items;
198 }
199 }
200 catch (Exception e)
201 {
202 database.Reconnect();
203 m_log.Error(e.ToString());
204 return null;
205 }
206 }
207
208 // see InventoryItemBase.getUserRootFolder
209 public InventoryFolderBase getUserRootFolder(LLUUID user)
210 {
211 try
212 {
213 lock (database)
214 {
215 Dictionary<string, string> param = new Dictionary<string, string>();
216 param["uuid"] = user.ToString();
217 param["zero"] = LLUUID.Zero.ToString();
218
219 IDbCommand result =
220 database.Query(
221 "SELECT * FROM inventoryfolders WHERE parentFolderID = @zero AND agentID = @uuid", param);
222 IDataReader reader = result.ExecuteReader();
223
224 List<InventoryFolderBase> items = new List<InventoryFolderBase>();
225 while (reader.Read())
226 items.Add(readInventoryFolder(reader));
227
228 InventoryFolderBase rootFolder = null;
229
230 // There should only ever be one root folder for a user. However, if there's more
231 // than one we'll simply use the first one rather than failing. It would be even
232 // nicer to print some message to this effect, but this feels like it's too low a
233 // to put such a message out, and it's too minor right now to spare the time to
234 // suitably refactor.
235 if (items.Count > 0)
236 {
237 rootFolder = items[0];
238 }
239
240 reader.Close();
241 result.Dispose();
242
243 return rootFolder;
244 }
245 }
246 catch (Exception e)
247 {
248 database.Reconnect();
249 m_log.Error(e.ToString());
250 return null;
251 }
252 }
253
254 /// <summary>
255 /// Returns a list of folders in a users inventory contained within the specified folder
256 /// </summary>
257 /// <param name="parentID">The folder to search</param>
258 /// <returns>A list of inventory folders</returns>
259 public List<InventoryFolderBase> getInventoryFolders(LLUUID parentID)
260 {
261 try
262 {
263 lock (database)
264 {
265 Dictionary<string, string> param = new Dictionary<string, string>();
266 param["parentFolderID"] = parentID.ToString();
267
268
269 IDbCommand result =
270 database.Query("SELECT * FROM inventoryfolders WHERE parentFolderID = @parentFolderID", param);
271 IDataReader reader = result.ExecuteReader();
272
273 List<InventoryFolderBase> items = new List<InventoryFolderBase>();
274
275 while (reader.Read())
276 items.Add(readInventoryFolder(reader));
277
278 reader.Close();
279 result.Dispose();
280
281 return items;
282 }
283 }
284 catch (Exception e)
285 {
286 database.Reconnect();
287 m_log.Error(e.ToString());
288 return null;
289 }
290 }
291
292 /// <summary>
293 /// Reads a one item from an SQL result
294 /// </summary>
295 /// <param name="reader">The SQL Result</param>
296 /// <returns>the item read</returns>
297 private InventoryItemBase readInventoryItem(IDataReader reader)
298 {
299 try
300 {
301 InventoryItemBase item = new InventoryItemBase();
302
303 item.inventoryID = new LLUUID((string) reader["inventoryID"]);
304 item.assetID = new LLUUID((string) reader["assetID"]);
305 item.assetType = (int) reader["assetType"];
306 item.parentFolderID = new LLUUID((string) reader["parentFolderID"]);
307 item.avatarID = new LLUUID((string) reader["avatarID"]);
308 item.inventoryName = (string) reader["inventoryName"];
309 item.inventoryDescription = (string) reader["inventoryDescription"];
310 item.inventoryNextPermissions = Convert.ToUInt32(reader["inventoryNextPermissions"]);
311 item.inventoryCurrentPermissions = Convert.ToUInt32(reader["inventoryCurrentPermissions"]);
312 item.invType = (int) reader["invType"];
313 item.creatorsID = new LLUUID((string) reader["creatorID"]);
314 item.inventoryBasePermissions = Convert.ToUInt32(reader["inventoryBasePermissions"]);
315 item.inventoryEveryOnePermissions = Convert.ToUInt32(reader["inventoryEveryOnePermissions"]);
316 return item;
317 }
318 catch (SqlException e)
319 {
320 m_log.Error(e.ToString());
321 }
322
323 return null;
324 }
325
326 /// <summary>
327 /// Returns a specified inventory item
328 /// </summary>
329 /// <param name="item">The item to return</param>
330 /// <returns>An inventory item</returns>
331 public InventoryItemBase getInventoryItem(LLUUID itemID)
332 {
333 try
334 {
335 lock (database)
336 {
337 Dictionary<string, string> param = new Dictionary<string, string>();
338 param["inventoryID"] = itemID.ToString();
339
340 IDbCommand result =
341 database.Query("SELECT * FROM inventoryitems WHERE inventoryID = @inventoryID", param);
342 IDataReader reader = result.ExecuteReader();
343
344 InventoryItemBase item = null;
345 if (reader.Read())
346 item = readInventoryItem(reader);
347
348 reader.Close();
349 result.Dispose();
350
351 return item;
352 }
353 }
354 catch (Exception e)
355 {
356 database.Reconnect();
357 m_log.Error(e.ToString());
358 }
359 return null;
360 }
361
362 /// <summary>
363 /// Reads a list of inventory folders returned by a query.
364 /// </summary>
365 /// <param name="reader">A MySQL Data Reader</param>
366 /// <returns>A List containing inventory folders</returns>
367 protected InventoryFolderBase readInventoryFolder(IDataReader reader)
368 {
369 try
370 {
371 InventoryFolderBase folder = new InventoryFolderBase();
372 folder.agentID = new LLUUID((string) reader["agentID"]);
373 folder.parentID = new LLUUID((string) reader["parentFolderID"]);
374 folder.folderID = new LLUUID((string) reader["folderID"]);
375 folder.name = (string) reader["folderName"];
376 folder.type = (short) reader["type"];
377 folder.version = (ushort) ((int) reader["version"]);
378 return folder;
379 }
380 catch (Exception e)
381 {
382 m_log.Error(e.ToString());
383 }
384
385 return null;
386 }
387
388 /// <summary>
389 /// Returns a specified inventory folder
390 /// </summary>
391 /// <param name="folder">The folder to return</param>
392 /// <returns>A folder class</returns>
393 public InventoryFolderBase getInventoryFolder(LLUUID folderID)
394 {
395 try
396 {
397 lock (database)
398 {
399 Dictionary<string, string> param = new Dictionary<string, string>();
400 param["uuid"] = folderID.ToString();
401
402 IDbCommand result = database.Query("SELECT * FROM inventoryfolders WHERE folderID = @uuid", param);
403 IDataReader reader = result.ExecuteReader();
404
405 reader.Read();
406 InventoryFolderBase folder = readInventoryFolder(reader);
407 reader.Close();
408 result.Dispose();
409
410 return folder;
411 }
412 }
413 catch (Exception e)
414 {
415 database.Reconnect();
416 m_log.Error(e.ToString());
417 return null;
418 }
419 }
420
421 /// <summary>
422 /// Adds a specified item to the database
423 /// </summary>
424 /// <param name="item">The inventory item</param>
425 public void addInventoryItem(InventoryItemBase item)
426 {
427 if (getInventoryItem(item.inventoryID) != null)
428 {
429 updateInventoryItem(item);
430 return;
431 }
432
433 string sql = "INSERT INTO inventoryitems";
434 sql +=
435 "([inventoryID], [assetID], [assetType], [parentFolderID], [avatarID], [inventoryName], [inventoryDescription], [inventoryNextPermissions], [inventoryCurrentPermissions], [invType], [creatorID], [inventoryBasePermissions], [inventoryEveryOnePermissions]) VALUES ";
436 sql +=
437 "(@inventoryID, @assetID, @assetType, @parentFolderID, @avatarID, @inventoryName, @inventoryDescription, @inventoryNextPermissions, @inventoryCurrentPermissions, @invType, @creatorID, @inventoryBasePermissions, @inventoryEveryOnePermissions);";
438
439 try
440 {
441 Dictionary<string, string> param = new Dictionary<string, string>();
442 param["inventoryID"] = item.inventoryID.ToString();
443 param["assetID"] = item.assetID.ToString();
444 param["assetType"] = item.assetType.ToString();
445 param["parentFolderID"] = item.parentFolderID.ToString();
446 param["avatarID"] = item.avatarID.ToString();
447 param["inventoryName"] = item.inventoryName;
448 param["inventoryDescription"] = item.inventoryDescription;
449 param["inventoryNextPermissions"] = item.inventoryNextPermissions.ToString();
450 param["inventoryCurrentPermissions"] = item.inventoryCurrentPermissions.ToString();
451 param["invType"] = Convert.ToString(item.invType);
452 param["creatorID"] = item.creatorsID.ToString();
453 param["inventoryBasePermissions"] = Convert.ToString(item.inventoryBasePermissions);
454 param["inventoryEveryOnePermissions"] = Convert.ToString(item.inventoryEveryOnePermissions);
455
456 IDbCommand result = database.Query(sql, param);
457 result.ExecuteNonQuery();
458 result.Dispose();
459 }
460 catch (SqlException e)
461 {
462 m_log.Error(e.ToString());
463 }
464 }
465
466 /// <summary>
467 /// Updates the specified inventory item
468 /// </summary>
469 /// <param name="item">Inventory item to update</param>
470 public void updateInventoryItem(InventoryItemBase item)
471 {
472 SqlCommand command = new SqlCommand("UPDATE inventoryitems set inventoryID = @inventoryID, " +
473 "assetID = @assetID, " +
474 "assetType = @assetType" +
475 "parentFolderID = @parentFolderID" +
476 "avatarID = @avatarID" +
477 "inventoryName = @inventoryName" +
478 "inventoryDescription = @inventoryDescription" +
479 "inventoryNextPermissions = @inventoryNextPermissions" +
480 "inventoryCurrentPermissions = @inventoryCurrentPermissions" +
481 "invType = @invType" +
482 "creatorID = @creatorID" +
483 "inventoryBasePermissions = @inventoryBasePermissions" +
484 "inventoryEveryOnePermissions = @inventoryEveryOnePermissions) where " +
485 "inventoryID = @keyInventoryID;", database.getConnection());
486 SqlParameter param1 = new SqlParameter("@inventoryID", item.inventoryID.ToString());
487 SqlParameter param2 = new SqlParameter("@assetID", item.assetID);
488 SqlParameter param3 = new SqlParameter("@assetType", item.assetType);
489 SqlParameter param4 = new SqlParameter("@parentFolderID", item.parentFolderID);
490 SqlParameter param5 = new SqlParameter("@avatarID", item.avatarID);
491 SqlParameter param6 = new SqlParameter("@inventoryName", item.inventoryName);
492 SqlParameter param7 = new SqlParameter("@inventoryDescription", item.inventoryDescription);
493 SqlParameter param8 = new SqlParameter("@inventoryNextPermissions", item.inventoryNextPermissions);
494 SqlParameter param9 = new SqlParameter("@inventoryCurrentPermissions", item.inventoryCurrentPermissions);
495 SqlParameter param10 = new SqlParameter("@invType", item.invType);
496 SqlParameter param11 = new SqlParameter("@creatorID", item.creatorsID);
497 SqlParameter param12 = new SqlParameter("@inventoryBasePermissions", item.inventoryBasePermissions);
498 SqlParameter param13 = new SqlParameter("@inventoryEveryOnePermissions", item.inventoryEveryOnePermissions);
499 SqlParameter param14 = new SqlParameter("@keyInventoryID", item.inventoryID.ToString());
500 command.Parameters.Add(param1);
501 command.Parameters.Add(param2);
502 command.Parameters.Add(param3);
503 command.Parameters.Add(param4);
504 command.Parameters.Add(param5);
505 command.Parameters.Add(param6);
506 command.Parameters.Add(param7);
507 command.Parameters.Add(param8);
508 command.Parameters.Add(param9);
509 command.Parameters.Add(param10);
510 command.Parameters.Add(param11);
511 command.Parameters.Add(param12);
512 command.Parameters.Add(param13);
513 command.Parameters.Add(param14);
514
515 try
516 {
517 command.ExecuteNonQuery();
518 }
519 catch (Exception e)
520 {
521 m_log.Error(e.ToString());
522 }
523 }
524
525 /// <summary>
526 ///
527 /// </summary>
528 /// <param name="item"></param>
529 public void deleteInventoryItem(LLUUID itemID)
530 {
531 try
532 {
533 Dictionary<string, string> param = new Dictionary<string, string>();
534 param["uuid"] = itemID.ToString();
535
536 IDbCommand cmd = database.Query("DELETE FROM inventoryitems WHERE inventoryID=@uuid", param);
537 cmd.ExecuteNonQuery();
538 cmd.Dispose();
539 }
540 catch (SqlException e)
541 {
542 database.Reconnect();
543 m_log.Error(e.ToString());
544 }
545 }
546
547 /// <summary>
548 /// Creates a new inventory folder
549 /// </summary>
550 /// <param name="folder">Folder to create</param>
551 public void addInventoryFolder(InventoryFolderBase folder)
552 {
553 string sql =
554 "INSERT INTO inventoryfolders ([folderID], [agentID], [parentFolderID], [folderName], [type], [version]) VALUES ";
555 sql += "(@folderID, @agentID, @parentFolderID, @folderName, @type, @version);";
556
557
558 Dictionary<string, string> param = new Dictionary<string, string>();
559 param["folderID"] = folder.folderID.ToString();
560 param["agentID"] = folder.agentID.ToString();
561 param["parentFolderID"] = folder.parentID.ToString();
562 param["folderName"] = folder.name;
563 param["type"] = Convert.ToString(folder.type);
564 param["version"] = Convert.ToString(folder.version);
565
566 try
567 {
568 IDbCommand result = database.Query(sql, param);
569 result.ExecuteNonQuery();
570 result.Dispose();
571 }
572 catch (Exception e)
573 {
574 m_log.Error(e.ToString());
575 }
576 }
577
578 /// <summary>
579 /// Updates an inventory folder
580 /// </summary>
581 /// <param name="folder">Folder to update</param>
582 public void updateInventoryFolder(InventoryFolderBase folder)
583 {
584 SqlCommand command = new SqlCommand("UPDATE inventoryfolders set folderID = @folderID, " +
585 "agentID = @agentID, " +
586 "parentFolderID = @parentFolderID," +
587 "folderName = @folderName," +
588 "type = @type," +
589 "version = @version where " +
590 "folderID = @keyFolderID;", database.getConnection());
591 SqlParameter param1 = new SqlParameter("@folderID", folder.folderID.ToString());
592 SqlParameter param2 = new SqlParameter("@agentID", folder.agentID.ToString());
593 SqlParameter param3 = new SqlParameter("@parentFolderID", folder.parentID.ToString());
594 SqlParameter param4 = new SqlParameter("@folderName", folder.name);
595 SqlParameter param5 = new SqlParameter("@type", folder.type);
596 SqlParameter param6 = new SqlParameter("@version", folder.version);
597 SqlParameter param7 = new SqlParameter("@keyFolderID", folder.folderID.ToString());
598 command.Parameters.Add(param1);
599 command.Parameters.Add(param2);
600 command.Parameters.Add(param3);
601 command.Parameters.Add(param4);
602 command.Parameters.Add(param5);
603 command.Parameters.Add(param6);
604 command.Parameters.Add(param7);
605
606 try
607 {
608 command.ExecuteNonQuery();
609 }
610 catch (Exception e)
611 {
612 m_log.Error(e.ToString());
613 }
614 }
615
616 /// <summary>
617 /// Updates an inventory folder
618 /// </summary>
619 /// <param name="folder">Folder to update</param>
620 public void moveInventoryFolder(InventoryFolderBase folder)
621 {
622 SqlCommand command = new SqlCommand("UPDATE inventoryfolders set folderID = @folderID, " +
623 "parentFolderID = @parentFolderID," +
624 "folderID = @keyFolderID;", database.getConnection());
625 SqlParameter param1 = new SqlParameter("@folderID", folder.folderID.ToString());
626 SqlParameter param2 = new SqlParameter("@parentFolderID", folder.parentID.ToString());
627 SqlParameter param3 = new SqlParameter("@keyFolderID", folder.folderID.ToString());
628 command.Parameters.Add(param1);
629 command.Parameters.Add(param2);
630 command.Parameters.Add(param3);
631
632 try
633 {
634 command.ExecuteNonQuery();
635 }
636 catch (Exception e)
637 {
638 m_log.Error(e.ToString());
639 }
640 }
641
642 /// <summary>
643 /// Append a list of all the child folders of a parent folder
644 /// </summary>
645 /// <param name="folders">list where folders will be appended</param>
646 /// <param name="parentID">ID of parent</param>
647 protected void getInventoryFolders(ref List<InventoryFolderBase> folders, LLUUID parentID)
648 {
649 List<InventoryFolderBase> subfolderList = getInventoryFolders(parentID);
650
651 foreach (InventoryFolderBase f in subfolderList)
652 folders.Add(f);
653 }
654
655 // See IInventoryData
656 public List<InventoryFolderBase> getFolderHierarchy(LLUUID parentID)
657 {
658 List<InventoryFolderBase> folders = new List<InventoryFolderBase>();
659 getInventoryFolders(ref folders, parentID);
660
661 for (int i = 0; i < folders.Count; i++)
662 getInventoryFolders(ref folders, folders[i].folderID);
663
664 return folders;
665 }
666
667 protected void deleteOneFolder(LLUUID folderID)
668 {
669 try
670 {
671 Dictionary<string, string> param = new Dictionary<string, string>();
672 param["folderID"] = folderID.ToString();
673
674 IDbCommand cmd = database.Query("DELETE FROM inventoryfolders WHERE folderID=@folderID", param);
675 cmd.ExecuteNonQuery();
676 cmd.Dispose();
677 }
678 catch (SqlException e)
679 {
680 database.Reconnect();
681 m_log.Error(e.ToString());
682 }
683 }
684
685 protected void deleteItemsInFolder(LLUUID folderID)
686 {
687 try
688 {
689 Dictionary<string, string> param = new Dictionary<string, string>();
690 param["parentFolderID"] = folderID.ToString();
691
692
693 IDbCommand cmd =
694 database.Query("DELETE FROM inventoryitems WHERE parentFolderID=@parentFolderID", param);
695 cmd.ExecuteNonQuery();
696 cmd.Dispose();
697 }
698 catch (SqlException e)
699 {
700 database.Reconnect();
701 m_log.Error(e.ToString());
702 }
703 }
704
705 /// <summary>
706 /// Delete an inventory folder
707 /// </summary>
708 /// <param name="folderId">Id of folder to delete</param>
709 public void deleteInventoryFolder(LLUUID folderID)
710 {
711 lock (database)
712 {
713 List<InventoryFolderBase> subFolders = getFolderHierarchy(folderID);
714
715 //Delete all sub-folders
716 foreach (InventoryFolderBase f in subFolders)
717 {
718 deleteOneFolder(f.folderID);
719 deleteItemsInFolder(f.folderID);
720 }
721
722 //Delete the actual row
723 deleteOneFolder(folderID);
724 deleteItemsInFolder(folderID);
725 }
726 }
727 }
728}
diff --git a/OpenSim/Data/MSSQL/MSSQLLogData.cs b/OpenSim/Data/MSSQL/MSSQLLogData.cs
new file mode 100644
index 0000000..c76af53
--- /dev/null
+++ b/OpenSim/Data/MSSQL/MSSQLLogData.cs
@@ -0,0 +1,120 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSim Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System.Collections.Generic;
29using System.Data;
30
31namespace OpenSim.Framework.Data.MSSQL
32{
33 /// <summary>
34 /// An interface to the log database for MySQL
35 /// </summary>
36 internal class MSSQLLogData : ILogData
37 {
38 /// <summary>
39 /// The database manager
40 /// </summary>
41 public MSSQLManager database;
42
43 /// <summary>
44 /// Artificial constructor called when the plugin is loaded
45 /// </summary>
46 public void Initialise()
47 {
48 IniFile GridDataMySqlFile = new IniFile("mssql_connection.ini");
49 string settingDataSource = GridDataMySqlFile.ParseFileReadValue("data_source");
50 string settingInitialCatalog = GridDataMySqlFile.ParseFileReadValue("initial_catalog");
51 string settingPersistSecurityInfo = GridDataMySqlFile.ParseFileReadValue("persist_security_info");
52 string settingUserId = GridDataMySqlFile.ParseFileReadValue("user_id");
53 string settingPassword = GridDataMySqlFile.ParseFileReadValue("password");
54
55 database =
56 new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId,
57 settingPassword);
58
59 IDbCommand cmd = database.Query("select top 1 * from logs", new Dictionary<string, string>());
60 try
61 {
62 cmd.ExecuteNonQuery();
63 cmd.Dispose();
64 }
65 catch
66 {
67 database.ExecuteResourceSql("Mssql-logs.sql");
68 }
69
70 }
71
72 /// <summary>
73 /// Saves a log item to the database
74 /// </summary>
75 /// <param name="serverDaemon">The daemon triggering the event</param>
76 /// <param name="target">The target of the action (region / agent UUID, etc)</param>
77 /// <param name="methodCall">The method call where the problem occured</param>
78 /// <param name="arguments">The arguments passed to the method</param>
79 /// <param name="priority">How critical is this?</param>
80 /// <param name="logMessage">The message to log</param>
81 public void saveLog(string serverDaemon, string target, string methodCall, string arguments, int priority,
82 string logMessage)
83 {
84 try
85 {
86 database.insertLogRow(serverDaemon, target, methodCall, arguments, priority, logMessage);
87 }
88 catch
89 {
90 database.Reconnect();
91 }
92 }
93
94 /// <summary>
95 /// Returns the name of this DB provider
96 /// </summary>
97 /// <returns>A string containing the DB provider name</returns>
98 public string getName()
99 {
100 return "MSSQL Logdata Interface";
101 }
102
103 /// <summary>
104 /// Closes the database provider
105 /// </summary>
106 public void Close()
107 {
108 // Do nothing.
109 }
110
111 /// <summary>
112 /// Returns the version of this DB provider
113 /// </summary>
114 /// <returns>A string containing the provider version</returns>
115 public string getVersion()
116 {
117 return "0.1";
118 }
119 }
120}
diff --git a/OpenSim/Data/MSSQL/MSSQLManager.cs b/OpenSim/Data/MSSQL/MSSQLManager.cs
new file mode 100644
index 0000000..efe62be
--- /dev/null
+++ b/OpenSim/Data/MSSQL/MSSQLManager.cs
@@ -0,0 +1,529 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSim Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections.Generic;
30using System.Data;
31using System.Data.SqlClient;
32using System.IO;
33using System.Reflection;
34using libsecondlife;
35using OpenSim.Framework.Console;
36
37namespace OpenSim.Framework.Data.MSSQL
38{
39 /// <summary>
40 /// A management class for the MS SQL Storage Engine
41 /// </summary>
42 public class MSSQLManager
43 {
44 private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
45
46 /// <summary>
47 /// The database connection object
48 /// </summary>
49 private IDbConnection dbcon;
50
51 /// <summary>
52 /// Connection string for ADO.net
53 /// </summary>
54 private readonly string connectionString;
55
56 public MSSQLManager(string dataSource, string initialCatalog, string persistSecurityInfo, string userId,
57 string password)
58 {
59 connectionString = "Data Source=" + dataSource + ";Initial Catalog=" + initialCatalog +
60 ";Persist Security Info=" + persistSecurityInfo + ";User ID=" + userId + ";Password=" +
61 password + ";";
62 dbcon = new SqlConnection(connectionString);
63 dbcon.Open();
64 }
65
66 //private DataTable createRegionsTable()
67 //{
68 // DataTable regions = new DataTable("regions");
69
70 // createCol(regions, "regionHandle", typeof (ulong));
71 // createCol(regions, "regionName", typeof (String));
72 // createCol(regions, "uuid", typeof (String));
73
74 // createCol(regions, "regionRecvKey", typeof (String));
75 // createCol(regions, "regionSecret", typeof (String));
76 // createCol(regions, "regionSendKey", typeof (String));
77
78 // createCol(regions, "regionDataURI", typeof (String));
79 // createCol(regions, "serverIP", typeof (String));
80 // createCol(regions, "serverPort", typeof (String));
81 // createCol(regions, "serverURI", typeof (String));
82
83
84 // createCol(regions, "locX", typeof (uint));
85 // createCol(regions, "locY", typeof (uint));
86 // createCol(regions, "locZ", typeof (uint));
87
88 // createCol(regions, "eastOverrideHandle", typeof (ulong));
89 // createCol(regions, "westOverrideHandle", typeof (ulong));
90 // createCol(regions, "southOverrideHandle", typeof (ulong));
91 // createCol(regions, "northOverrideHandle", typeof (ulong));
92
93 // createCol(regions, "regionAssetURI", typeof (String));
94 // createCol(regions, "regionAssetRecvKey", typeof (String));
95 // createCol(regions, "regionAssetSendKey", typeof (String));
96
97 // createCol(regions, "regionUserURI", typeof (String));
98 // createCol(regions, "regionUserRecvKey", typeof (String));
99 // createCol(regions, "regionUserSendKey", typeof (String));
100
101 // createCol(regions, "regionMapTexture", typeof (String));
102 // createCol(regions, "serverHttpPort", typeof (String));
103 // createCol(regions, "serverRemotingPort", typeof (uint));
104
105 // // Add in contraints
106 // regions.PrimaryKey = new DataColumn[] {regions.Columns["UUID"]};
107 // return regions;
108 //}
109
110 protected static void createCol(DataTable dt, string name, Type type)
111 {
112 DataColumn col = new DataColumn(name, type);
113 dt.Columns.Add(col);
114 }
115
116 protected static string defineTable(DataTable dt)
117 {
118 string sql = "create table " + dt.TableName + "(";
119 string subsql = String.Empty;
120 foreach (DataColumn col in dt.Columns)
121 {
122 if (subsql.Length > 0)
123 {
124 // a map function would rock so much here
125 subsql += ",\n";
126 }
127
128 subsql += col.ColumnName + " " + SqlType(col.DataType);
129 if (col == dt.PrimaryKey[0])
130 {
131 subsql += " primary key";
132 }
133 }
134 sql += subsql;
135 sql += ")";
136 return sql;
137 }
138
139
140 // this is something we'll need to implement for each db
141 // slightly differently.
142 public static string SqlType(Type type)
143 {
144 if (type == typeof(String))
145 {
146 return "varchar(255)";
147 }
148 else if (type == typeof(Int32))
149 {
150 return "integer";
151 }
152 else if (type == typeof(Double))
153 {
154 return "float";
155 }
156 else if (type == typeof(Byte[]))
157 {
158 return "image";
159 }
160 else
161 {
162 return "varchar(255)";
163 }
164 }
165
166 /// <summary>
167 /// Shuts down the database connection
168 /// </summary>
169 public void Close()
170 {
171 dbcon.Close();
172 dbcon = null;
173 }
174
175 /// <summary>
176 /// Reconnects to the database
177 /// </summary>
178 public void Reconnect()
179 {
180 lock (dbcon)
181 {
182 try
183 {
184 // Close the DB connection
185 dbcon.Close();
186 // Try reopen it
187 dbcon = new SqlConnection(connectionString);
188 dbcon.Open();
189 }
190 catch (Exception e)
191 {
192 m_log.Error("Unable to reconnect to database " + e.ToString());
193 }
194 }
195 }
196
197 /// <summary>
198 /// Runs a query with protection against SQL Injection by using parameterised input.
199 /// </summary>
200 /// <param name="sql">The SQL string - replace any variables such as WHERE x = "y" with WHERE x = @y</param>
201 /// <param name="parameters">The parameters - index so that @y is indexed as 'y'</param>
202 /// <returns>A Sql DB Command</returns>
203 public IDbCommand Query(string sql, Dictionary<string, string> parameters)
204 {
205 SqlCommand dbcommand = (SqlCommand)dbcon.CreateCommand();
206 dbcommand.CommandText = sql;
207 foreach (KeyValuePair<string, string> param in parameters)
208 {
209 dbcommand.Parameters.AddWithValue(param.Key, param.Value);
210 }
211
212 return (IDbCommand)dbcommand;
213 }
214
215 /// <summary>
216 /// Runs a database reader object and returns a region row
217 /// </summary>
218 /// <param name="reader">An active database reader</param>
219 /// <returns>A region row</returns>
220 public RegionProfileData getRegionRow(IDataReader reader)
221 {
222 RegionProfileData regionprofile = new RegionProfileData();
223
224 if (reader.Read())
225 {
226 // Region Main
227 regionprofile.regionHandle = Convert.ToUInt64(reader["regionHandle"]);
228 regionprofile.regionName = (string)reader["regionName"];
229 regionprofile.UUID = new LLUUID((string)reader["uuid"]);
230
231 // Secrets
232 regionprofile.regionRecvKey = (string)reader["regionRecvKey"];
233 regionprofile.regionSecret = (string)reader["regionSecret"];
234 regionprofile.regionSendKey = (string)reader["regionSendKey"];
235
236 // Region Server
237 regionprofile.regionDataURI = (string)reader["regionDataURI"];
238 regionprofile.regionOnline = false; // Needs to be pinged before this can be set.
239 regionprofile.serverIP = (string)reader["serverIP"];
240 regionprofile.serverPort = Convert.ToUInt32(reader["serverPort"]);
241 regionprofile.serverURI = (string)reader["serverURI"];
242 regionprofile.httpPort = Convert.ToUInt32(reader["serverHttpPort"]);
243 regionprofile.remotingPort = Convert.ToUInt32(reader["serverRemotingPort"]);
244
245
246 // Location
247 regionprofile.regionLocX = Convert.ToUInt32(reader["locX"]);
248 regionprofile.regionLocY = Convert.ToUInt32(reader["locY"]);
249 regionprofile.regionLocZ = Convert.ToUInt32(reader["locZ"]);
250
251 // Neighbours - 0 = No Override
252 regionprofile.regionEastOverrideHandle = Convert.ToUInt64(reader["eastOverrideHandle"]);
253 regionprofile.regionWestOverrideHandle = Convert.ToUInt64(reader["westOverrideHandle"]);
254 regionprofile.regionSouthOverrideHandle = Convert.ToUInt64(reader["southOverrideHandle"]);
255 regionprofile.regionNorthOverrideHandle = Convert.ToUInt64(reader["northOverrideHandle"]);
256
257 // Assets
258 regionprofile.regionAssetURI = (string)reader["regionAssetURI"];
259 regionprofile.regionAssetRecvKey = (string)reader["regionAssetRecvKey"];
260 regionprofile.regionAssetSendKey = (string)reader["regionAssetSendKey"];
261
262 // Userserver
263 regionprofile.regionUserURI = (string)reader["regionUserURI"];
264 regionprofile.regionUserRecvKey = (string)reader["regionUserRecvKey"];
265 regionprofile.regionUserSendKey = (string)reader["regionUserSendKey"];
266 try
267 {
268 regionprofile.owner_uuid = new LLUUID((string)reader["owner_uuid"]);
269 }
270 catch(Exception)
271 {}
272 // World Map Addition
273 string tempRegionMap = reader["regionMapTexture"].ToString();
274 if (tempRegionMap != String.Empty)
275 {
276 regionprofile.regionMapTextureID = new LLUUID(tempRegionMap);
277 }
278 else
279 {
280 regionprofile.regionMapTextureID = new LLUUID();
281 }
282 }
283 else
284 {
285 reader.Close();
286 throw new Exception("No rows to return");
287 }
288 return regionprofile;
289 }
290
291 /// <summary>
292 /// Reads a user profile from an active data reader
293 /// </summary>
294 /// <param name="reader">An active database reader</param>
295 /// <returns>A user profile</returns>
296 public UserProfileData readUserRow(IDataReader reader)
297 {
298 UserProfileData retval = new UserProfileData();
299
300 if (reader.Read())
301 {
302 retval.UUID = new LLUUID((string)reader["UUID"]);
303 retval.username = (string)reader["username"];
304 retval.surname = (string)reader["lastname"];
305
306 retval.passwordHash = (string)reader["passwordHash"];
307 retval.passwordSalt = (string)reader["passwordSalt"];
308
309 retval.homeRegion = Convert.ToUInt64(reader["homeRegion"].ToString());
310 retval.homeLocation = new LLVector3(
311 Convert.ToSingle(reader["homeLocationX"].ToString()),
312 Convert.ToSingle(reader["homeLocationY"].ToString()),
313 Convert.ToSingle(reader["homeLocationZ"].ToString()));
314 retval.homeLookAt = new LLVector3(
315 Convert.ToSingle(reader["homeLookAtX"].ToString()),
316 Convert.ToSingle(reader["homeLookAtY"].ToString()),
317 Convert.ToSingle(reader["homeLookAtZ"].ToString()));
318
319 retval.created = Convert.ToInt32(reader["created"].ToString());
320 retval.lastLogin = Convert.ToInt32(reader["lastLogin"].ToString());
321
322 retval.userInventoryURI = (string)reader["userInventoryURI"];
323 retval.userAssetURI = (string)reader["userAssetURI"];
324
325 retval.profileCanDoMask = Convert.ToUInt32(reader["profileCanDoMask"].ToString());
326 retval.profileWantDoMask = Convert.ToUInt32(reader["profileWantDoMask"].ToString());
327
328 retval.profileAboutText = (string)reader["profileAboutText"];
329 retval.profileFirstText = (string)reader["profileFirstText"];
330
331 retval.profileImage = new LLUUID((string)reader["profileImage"]);
332 retval.profileFirstImage = new LLUUID((string)reader["profileFirstImage"]);
333 retval.webLoginKey = new LLUUID((string)reader["webLoginKey"]);
334 }
335 else
336 {
337 return null;
338 }
339 return retval;
340 }
341
342 /// <summary>
343 /// Reads an agent row from a database reader
344 /// </summary>
345 /// <param name="reader">An active database reader</param>
346 /// <returns>A user session agent</returns>
347 public UserAgentData readAgentRow(IDataReader reader)
348 {
349 UserAgentData retval = new UserAgentData();
350
351 if (reader.Read())
352 {
353 // Agent IDs
354 retval.UUID = new LLUUID((string)reader["UUID"]);
355 retval.sessionID = new LLUUID((string)reader["sessionID"]);
356 retval.secureSessionID = new LLUUID((string)reader["secureSessionID"]);
357
358 // Agent Who?
359 retval.agentIP = (string)reader["agentIP"];
360 retval.agentPort = Convert.ToUInt32(reader["agentPort"].ToString());
361 retval.agentOnline = Convert.ToBoolean(reader["agentOnline"].ToString());
362
363 // Login/Logout times (UNIX Epoch)
364 retval.loginTime = Convert.ToInt32(reader["loginTime"].ToString());
365 retval.logoutTime = Convert.ToInt32(reader["logoutTime"].ToString());
366
367 // Current position
368 retval.currentRegion = (string)reader["currentRegion"];
369 retval.currentHandle = Convert.ToUInt64(reader["currentHandle"].ToString());
370 LLVector3.TryParse((string)reader["currentPos"], out retval.currentPos);
371 }
372 else
373 {
374 return null;
375 }
376 return retval;
377 }
378
379 public AssetBase getAssetRow(IDataReader reader)
380 {
381 AssetBase asset = new AssetBase();
382 if (reader.Read())
383 {
384 // Region Main
385
386 asset = new AssetBase();
387 asset.Data = (byte[])reader["data"];
388 asset.Description = (string)reader["description"];
389 asset.FullID = new LLUUID((string)reader["id"]);
390 asset.InvType = Convert.ToSByte(reader["invType"]);
391 asset.Local = Convert.ToBoolean(reader["local"]); // ((sbyte)reader["local"]) != 0 ? true : false;
392 asset.Name = (string)reader["name"];
393 asset.Type = Convert.ToSByte(reader["assetType"]);
394 }
395 else
396 {
397 return null; // throw new Exception("No rows to return");
398 }
399 return asset;
400 }
401
402
403 /// <summary>
404 /// Inserts a new row into the log database
405 /// </summary>
406 /// <param name="serverDaemon">The daemon which triggered this event</param>
407 /// <param name="target">Who were we operating on when this occured (region UUID, user UUID, etc)</param>
408 /// <param name="methodCall">The method call where the problem occured</param>
409 /// <param name="arguments">The arguments passed to the method</param>
410 /// <param name="priority">How critical is this?</param>
411 /// <param name="logMessage">Extra message info</param>
412 /// <returns>Saved successfully?</returns>
413 public bool insertLogRow(string serverDaemon, string target, string methodCall, string arguments, int priority,
414 string logMessage)
415 {
416 string sql = "INSERT INTO logs ([target], [server], [method], [arguments], [priority], [message]) VALUES ";
417 sql += "(@target, @server, @method, @arguments, @priority, @message);";
418
419 Dictionary<string, string> parameters = new Dictionary<string, string>();
420 parameters["server"] = serverDaemon;
421 parameters["target"] = target;
422 parameters["method"] = methodCall;
423 parameters["arguments"] = arguments;
424 parameters["priority"] = priority.ToString();
425 parameters["message"] = logMessage;
426
427 bool returnval = false;
428
429 try
430 {
431 IDbCommand result = Query(sql, parameters);
432
433 if (result.ExecuteNonQuery() == 1)
434 returnval = true;
435
436 result.Dispose();
437 }
438 catch (Exception e)
439 {
440 m_log.Error(e.ToString());
441 return false;
442 }
443
444 return returnval;
445 }
446
447 /// <summary>
448 /// Execute a SQL statement stored in a resource, as a string
449 /// </summary>
450 /// <param name="name"></param>
451 public void ExecuteResourceSql(string name)
452 {
453 SqlCommand cmd = new SqlCommand(getResourceString(name), (SqlConnection)dbcon);
454 cmd.ExecuteNonQuery();
455 cmd.Dispose();
456 }
457
458 public SqlConnection getConnection()
459 {
460 return (SqlConnection)dbcon;
461 }
462
463 /// <summary>
464 /// Given a list of tables, return the version of the tables, as seen in the database
465 /// </summary>
466 /// <param name="tableList"></param>
467 public void GetTableVersion(Dictionary<string, string> tableList)
468 {
469 lock (dbcon)
470 {
471 Dictionary<string, string> param = new Dictionary<string, string>();
472 param["dbname"] = dbcon.Database;
473 IDbCommand tablesCmd =
474 Query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG=@dbname", param);
475 using (IDataReader tables = tablesCmd.ExecuteReader())
476 {
477 while (tables.Read())
478 {
479 try
480 {
481 string tableName = (string)tables["TABLE_NAME"];
482 if (tableList.ContainsKey(tableName))
483 tableList[tableName] = tableName;
484 }
485 catch (Exception e)
486 {
487 m_log.Error(e.ToString());
488 }
489 }
490 tables.Close();
491 }
492 }
493 }
494
495 private string getResourceString(string name)
496 {
497 Assembly assem = GetType().Assembly;
498 string[] names = assem.GetManifestResourceNames();
499
500 foreach (string s in names)
501 if (s.EndsWith(name))
502 using (Stream resource = assem.GetManifestResourceStream(s))
503 {
504 using (StreamReader resourceReader = new StreamReader(resource))
505 {
506 string resourceString = resourceReader.ReadToEnd();
507 return resourceString;
508 }
509 }
510 throw new Exception(string.Format("Resource '{0}' was not found", name));
511 }
512
513 /// <summary>
514 /// Returns the version of this DB provider
515 /// </summary>
516 /// <returns>A string containing the DB provider</returns>
517 public string getVersion()
518 {
519 Module module = GetType().Module;
520 string dllName = module.Assembly.ManifestModule.Name;
521 Version dllVersion = module.Assembly.GetName().Version;
522
523
524 return
525 string.Format("{0}.{1}.{2}.{3}", dllVersion.Major, dllVersion.Minor, dllVersion.Build,
526 dllVersion.Revision);
527 }
528 }
529}
diff --git a/OpenSim/Data/MSSQL/MSSQLUserData.cs b/OpenSim/Data/MSSQL/MSSQLUserData.cs
new file mode 100644
index 0000000..be0417d
--- /dev/null
+++ b/OpenSim/Data/MSSQL/MSSQLUserData.cs
@@ -0,0 +1,771 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSim Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections.Generic;
30using System.Data;
31using System.Data.SqlClient;
32using libsecondlife;
33using OpenSim.Framework.Console;
34
35namespace OpenSim.Framework.Data.MSSQL
36{
37 /// <summary>
38 /// A database interface class to a user profile storage system
39 /// </summary>
40 public class MSSQLUserData : UserDataBase
41 {
42 private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
43
44 /// <summary>
45 /// Database manager for MySQL
46 /// </summary>
47 public MSSQLManager database;
48
49 private string m_agentsTableName;
50 private string m_usersTableName;
51 private string m_userFriendsTableName;
52
53 /// <summary>
54 /// Loads and initialises the MySQL storage plugin
55 /// </summary>
56 override public void Initialise()
57 {
58 // Load from an INI file connection details
59 // TODO: move this to XML?
60 IniFile iniFile = new IniFile("mssql_connection.ini");
61 string settingDataSource = iniFile.ParseFileReadValue("data_source");
62 string settingInitialCatalog = iniFile.ParseFileReadValue("initial_catalog");
63 string settingPersistSecurityInfo = iniFile.ParseFileReadValue("persist_security_info");
64 string settingUserId = iniFile.ParseFileReadValue("user_id");
65 string settingPassword = iniFile.ParseFileReadValue("password");
66
67 m_usersTableName = iniFile.ParseFileReadValue("userstablename");
68 if (m_usersTableName == null)
69 {
70 m_usersTableName = "users";
71 }
72
73 m_userFriendsTableName = iniFile.ParseFileReadValue("userfriendstablename");
74 if (m_userFriendsTableName == null)
75 {
76 m_userFriendsTableName = "userfriends";
77 }
78
79 m_agentsTableName = iniFile.ParseFileReadValue("agentstablename");
80 if (m_agentsTableName == null)
81 {
82 m_agentsTableName = "agents";
83 }
84
85 database =
86 new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId,
87 settingPassword);
88
89 TestTables();
90 }
91
92 private bool TestTables()
93 {
94 IDbCommand cmd;
95
96 cmd = database.Query("select top 1 * from " + m_usersTableName, new Dictionary<string, string>());
97 try
98 {
99 cmd.ExecuteNonQuery();
100 }
101 catch
102 {
103 database.ExecuteResourceSql("Mssql-users.sql");
104 }
105
106 cmd = database.Query("select top 1 * from " + m_agentsTableName, new Dictionary<string, string>());
107 try
108 {
109 cmd.ExecuteNonQuery();
110 }
111 catch
112 {
113 database.ExecuteResourceSql("Mssql-agents.sql");
114 }
115
116 cmd = database.Query("select top 1 * from " + m_userFriendsTableName, new Dictionary<string, string>());
117 try
118 {
119 cmd.ExecuteNonQuery();
120 }
121 catch
122 {
123 database.ExecuteResourceSql("CreateUserFriendsTable.sql");
124 }
125
126 return true;
127 }
128 /// <summary>
129 /// Searches the database for a specified user profile by name components
130 /// </summary>
131 /// <param name="user">The first part of the account name</param>
132 /// <param name="last">The second part of the account name</param>
133 /// <returns>A user profile</returns>
134 override public UserProfileData GetUserByName(string user, string last)
135 {
136 try
137 {
138 lock (database)
139 {
140 Dictionary<string, string> param = new Dictionary<string, string>();
141 param["first"] = user;
142 param["second"] = last;
143
144 IDbCommand result =
145 database.Query("SELECT * FROM " + m_usersTableName + " WHERE username = @first AND lastname = @second", param);
146 IDataReader reader = result.ExecuteReader();
147
148 UserProfileData row = database.readUserRow(reader);
149
150 reader.Close();
151 result.Dispose();
152
153 return row;
154 }
155 }
156 catch (Exception e)
157 {
158 database.Reconnect();
159 m_log.Error(e.ToString());
160 return null;
161 }
162 }
163
164 #region User Friends List Data
165
166 override public void AddNewUserFriend(LLUUID friendlistowner, LLUUID friend, uint perms)
167 {
168 int dtvalue = Util.UnixTimeSinceEpoch();
169
170 Dictionary<string, string> param = new Dictionary<string, string>();
171 param["@ownerID"] = friendlistowner.UUID.ToString();
172 param["@friendID"] = friend.UUID.ToString();
173 param["@friendPerms"] = perms.ToString();
174 param["@datetimestamp"] = dtvalue.ToString();
175
176 try
177 {
178 lock (database)
179 {
180 IDbCommand adder =
181 database.Query(
182 "INSERT INTO " + m_userFriendsTableName + " " +
183 "(ownerID,friendID,friendPerms,datetimestamp) " +
184 "VALUES " +
185 "(@ownerID,@friendID,@friendPerms,@datetimestamp)",
186 param);
187
188 adder.ExecuteNonQuery();
189
190 adder =
191 database.Query(
192 "INSERT INTO " + m_userFriendsTableName + " " +
193 "(ownerID,friendID,friendPerms,datetimestamp) " +
194 "VALUES " +
195 "(@friendID,@ownerID,@friendPerms,@datetimestamp)",
196 param);
197 adder.ExecuteNonQuery();
198
199 }
200 }
201 catch (Exception e)
202 {
203 database.Reconnect();
204 m_log.Error(e.ToString());
205 return;
206 }
207 }
208
209 override public void RemoveUserFriend(LLUUID friendlistowner, LLUUID friend)
210 {
211 Dictionary<string, string> param = new Dictionary<string, string>();
212 param["@ownerID"] = friendlistowner.UUID.ToString();
213 param["@friendID"] = friend.UUID.ToString();
214
215
216 try
217 {
218 lock (database)
219 {
220 IDbCommand updater =
221 database.Query(
222 "delete from " + m_userFriendsTableName + " where ownerID = @ownerID and friendID = @friendID",
223 param);
224 updater.ExecuteNonQuery();
225
226 updater =
227 database.Query(
228 "delete from " + m_userFriendsTableName + " where ownerID = @friendID and friendID = @ownerID",
229 param);
230 updater.ExecuteNonQuery();
231
232 }
233 }
234 catch (Exception e)
235 {
236 database.Reconnect();
237 m_log.Error(e.ToString());
238 return;
239 }
240 }
241
242 override public void UpdateUserFriendPerms(LLUUID friendlistowner, LLUUID friend, uint perms)
243 {
244 Dictionary<string, string> param = new Dictionary<string, string>();
245 param["@ownerID"] = friendlistowner.UUID.ToString();
246 param["@friendID"] = friend.UUID.ToString();
247 param["@friendPerms"] = perms.ToString();
248
249
250 try
251 {
252 lock (database)
253 {
254 IDbCommand updater =
255 database.Query(
256 "update " + m_userFriendsTableName +
257 " SET friendPerms = @friendPerms " +
258 "where ownerID = @ownerID and friendID = @friendID",
259 param);
260
261 updater.ExecuteNonQuery();
262 }
263 }
264 catch (Exception e)
265 {
266 database.Reconnect();
267 m_log.Error(e.ToString());
268 return;
269 }
270 }
271
272
273 override public List<FriendListItem> GetUserFriendList(LLUUID friendlistowner)
274 {
275 List<FriendListItem> Lfli = new List<FriendListItem>();
276
277 Dictionary<string, string> param = new Dictionary<string, string>();
278 param["@ownerID"] = friendlistowner.UUID.ToString();
279
280 try
281 {
282 lock (database)
283 {
284 //Left Join userfriends to itself
285 IDbCommand result =
286 database.Query(
287 "select a.ownerID,a.friendID,a.friendPerms,b.friendPerms as ownerperms from " + m_userFriendsTableName + " as a, " + m_userFriendsTableName + " as b" +
288 " where a.ownerID = @ownerID and b.ownerID = a.friendID and b.friendID = a.ownerID",
289 param);
290 IDataReader reader = result.ExecuteReader();
291
292
293 while (reader.Read())
294 {
295 FriendListItem fli = new FriendListItem();
296 fli.FriendListOwner = new LLUUID((string)reader["ownerID"]);
297 fli.Friend = new LLUUID((string)reader["friendID"]);
298 fli.FriendPerms = (uint)Convert.ToInt32(reader["friendPerms"]);
299
300 // This is not a real column in the database table, it's a joined column from the opposite record
301 fli.FriendListOwnerPerms = (uint)Convert.ToInt32(reader["ownerperms"]);
302
303 Lfli.Add(fli);
304 }
305 reader.Close();
306 result.Dispose();
307 }
308 }
309 catch (Exception e)
310 {
311 database.Reconnect();
312 m_log.Error(e.ToString());
313 return Lfli;
314 }
315
316 return Lfli;
317 }
318
319 #endregion
320
321 override public void UpdateUserCurrentRegion(LLUUID avatarid, LLUUID regionuuid)
322 {
323 m_log.Info("[USER]: Stub UpdateUserCUrrentRegion called");
324 }
325
326
327
328 override public List<Framework.AvatarPickerAvatar> GeneratePickerResults(LLUUID queryID, string query)
329 {
330 List<Framework.AvatarPickerAvatar> returnlist = new List<Framework.AvatarPickerAvatar>();
331 string[] querysplit;
332 querysplit = query.Split(' ');
333 if (querysplit.Length == 2)
334 {
335 try
336 {
337 lock (database)
338 {
339 Dictionary<string, string> param = new Dictionary<string, string>();
340 param["first"] = querysplit[0];
341 param["second"] = querysplit[1];
342
343 IDbCommand result =
344 database.Query(
345 "SELECT UUID,username,lastname FROM " + m_usersTableName + " WHERE username = @first AND lastname = @second",
346 param);
347 IDataReader reader = result.ExecuteReader();
348
349
350 while (reader.Read())
351 {
352 Framework.AvatarPickerAvatar user = new Framework.AvatarPickerAvatar();
353 user.AvatarID = new LLUUID((string)reader["UUID"]);
354 user.firstName = (string)reader["username"];
355 user.lastName = (string)reader["lastname"];
356 returnlist.Add(user);
357 }
358 reader.Close();
359 result.Dispose();
360 }
361 }
362 catch (Exception e)
363 {
364 database.Reconnect();
365 m_log.Error(e.ToString());
366 return returnlist;
367 }
368 }
369 else if (querysplit.Length == 1)
370 {
371 try
372 {
373 lock (database)
374 {
375 Dictionary<string, string> param = new Dictionary<string, string>();
376 param["first"] = querysplit[0];
377
378 IDbCommand result =
379 database.Query(
380 "SELECT UUID,username,lastname FROM " + m_usersTableName + " WHERE username = @first OR lastname = @first",
381 param);
382 IDataReader reader = result.ExecuteReader();
383
384
385 while (reader.Read())
386 {
387 Framework.AvatarPickerAvatar user = new Framework.AvatarPickerAvatar();
388 user.AvatarID = new LLUUID((string)reader["UUID"]);
389 user.firstName = (string)reader["username"];
390 user.lastName = (string)reader["lastname"];
391 returnlist.Add(user);
392 }
393 reader.Close();
394 result.Dispose();
395 }
396 }
397 catch (Exception e)
398 {
399 database.Reconnect();
400 m_log.Error(e.ToString());
401 return returnlist;
402 }
403 }
404 return returnlist;
405 }
406
407 // See IUserData
408 override public UserProfileData GetUserByUUID(LLUUID uuid)
409 {
410 try
411 {
412 lock (database)
413 {
414 Dictionary<string, string> param = new Dictionary<string, string>();
415 param["uuid"] = uuid.ToString();
416
417 IDbCommand result = database.Query("SELECT * FROM " + m_usersTableName + " WHERE UUID = @uuid", param);
418 IDataReader reader = result.ExecuteReader();
419
420 UserProfileData row = database.readUserRow(reader);
421
422 reader.Close();
423 result.Dispose();
424
425 return row;
426 }
427 }
428 catch (Exception e)
429 {
430 database.Reconnect();
431 m_log.Error(e.ToString());
432 return null;
433 }
434 }
435
436 /// <summary>
437 /// Returns a user session searching by name
438 /// </summary>
439 /// <param name="name">The account name</param>
440 /// <returns>The users session</returns>
441 override public UserAgentData GetAgentByName(string name)
442 {
443 return GetAgentByName(name.Split(' ')[0], name.Split(' ')[1]);
444 }
445
446 /// <summary>
447 /// Returns a user session by account name
448 /// </summary>
449 /// <param name="user">First part of the users account name</param>
450 /// <param name="last">Second part of the users account name</param>
451 /// <returns>The users session</returns>
452 override public UserAgentData GetAgentByName(string user, string last)
453 {
454 UserProfileData profile = GetUserByName(user, last);
455 return GetAgentByUUID(profile.UUID);
456 }
457
458 /// <summary>
459 /// Returns an agent session by account UUID
460 /// </summary>
461 /// <param name="uuid">The accounts UUID</param>
462 /// <returns>The users session</returns>
463 override public UserAgentData GetAgentByUUID(LLUUID uuid)
464 {
465 try
466 {
467 lock (database)
468 {
469 Dictionary<string, string> param = new Dictionary<string, string>();
470 param["uuid"] = uuid.ToString();
471
472 IDbCommand result = database.Query("SELECT * FROM " + m_agentsTableName + " WHERE UUID = @uuid", param);
473 IDataReader reader = result.ExecuteReader();
474
475 UserAgentData row = database.readAgentRow(reader);
476
477 reader.Close();
478 result.Dispose();
479
480 return row;
481 }
482 }
483 catch (Exception e)
484 {
485 database.Reconnect();
486 m_log.Error(e.ToString());
487 return null;
488 }
489 }
490 override public void StoreWebLoginKey(LLUUID AgentID, LLUUID WebLoginKey)
491 {
492 UserProfileData user = GetUserByUUID(AgentID);
493 user.webLoginKey = WebLoginKey;
494 UpdateUserProfile(user);
495
496 }
497 /// <summary>
498 /// Creates a new users profile
499 /// </summary>
500 /// <param name="user">The user profile to create</param>
501 override public void AddNewUserProfile(UserProfileData user)
502 {
503 try
504 {
505 lock (database)
506 {
507 InsertUserRow(user.UUID, user.username, user.surname, user.passwordHash, user.passwordSalt,
508 user.homeRegion, user.homeLocation.X, user.homeLocation.Y,
509 user.homeLocation.Z,
510 user.homeLookAt.X, user.homeLookAt.Y, user.homeLookAt.Z, user.created,
511 user.lastLogin, user.userInventoryURI, user.userAssetURI,
512 user.profileCanDoMask, user.profileWantDoMask,
513 user.profileAboutText, user.profileFirstText, user.profileImage,
514 user.profileFirstImage, user.webLoginKey);
515 }
516 }
517 catch (Exception e)
518 {
519 database.Reconnect();
520 m_log.Error(e.ToString());
521 }
522 }
523
524 /// <summary>
525 /// Creates a new user and inserts it into the database
526 /// </summary>
527 /// <param name="uuid">User ID</param>
528 /// <param name="username">First part of the login</param>
529 /// <param name="lastname">Second part of the login</param>
530 /// <param name="passwordHash">A salted hash of the users password</param>
531 /// <param name="passwordSalt">The salt used for the password hash</param>
532 /// <param name="homeRegion">A regionHandle of the users home region</param>
533 /// <param name="homeLocX">Home region position vector</param>
534 /// <param name="homeLocY">Home region position vector</param>
535 /// <param name="homeLocZ">Home region position vector</param>
536 /// <param name="homeLookAtX">Home region 'look at' vector</param>
537 /// <param name="homeLookAtY">Home region 'look at' vector</param>
538 /// <param name="homeLookAtZ">Home region 'look at' vector</param>
539 /// <param name="created">Account created (unix timestamp)</param>
540 /// <param name="lastlogin">Last login (unix timestamp)</param>
541 /// <param name="inventoryURI">Users inventory URI</param>
542 /// <param name="assetURI">Users asset URI</param>
543 /// <param name="canDoMask">I can do mask</param>
544 /// <param name="wantDoMask">I want to do mask</param>
545 /// <param name="aboutText">Profile text</param>
546 /// <param name="firstText">Firstlife text</param>
547 /// <param name="profileImage">UUID for profile image</param>
548 /// <param name="firstImage">UUID for firstlife image</param>
549 /// <returns>Success?</returns>
550 private bool InsertUserRow(LLUUID uuid, string username, string lastname, string passwordHash,
551 string passwordSalt, UInt64 homeRegion, float homeLocX, float homeLocY, float homeLocZ,
552 float homeLookAtX, float homeLookAtY, float homeLookAtZ, int created, int lastlogin,
553 string inventoryURI, string assetURI, uint canDoMask, uint wantDoMask,
554 string aboutText, string firstText,
555 LLUUID profileImage, LLUUID firstImage, LLUUID webLoginKey)
556 {
557 string sql = "INSERT INTO "+m_usersTableName;
558 sql += " ([UUID], [username], [lastname], [passwordHash], [passwordSalt], [homeRegion], ";
559 sql +=
560 "[homeLocationX], [homeLocationY], [homeLocationZ], [homeLookAtX], [homeLookAtY], [homeLookAtZ], [created], ";
561 sql +=
562 "[lastLogin], [userInventoryURI], [userAssetURI], [profileCanDoMask], [profileWantDoMask], [profileAboutText], ";
563 sql += "[profileFirstText], [profileImage], [profileFirstImage], [webLoginKey]) VALUES ";
564
565 sql += "(@UUID, @username, @lastname, @passwordHash, @passwordSalt, @homeRegion, ";
566 sql +=
567 "@homeLocationX, @homeLocationY, @homeLocationZ, @homeLookAtX, @homeLookAtY, @homeLookAtZ, @created, ";
568 sql +=
569 "@lastLogin, @userInventoryURI, @userAssetURI, @profileCanDoMask, @profileWantDoMask, @profileAboutText, ";
570 sql += "@profileFirstText, @profileImage, @profileFirstImage, @webLoginKey);";
571
572 Dictionary<string, string> parameters = new Dictionary<string, string>();
573 parameters["UUID"] = uuid.ToString();
574 parameters["username"] = username.ToString();
575 parameters["lastname"] = lastname.ToString();
576 parameters["passwordHash"] = passwordHash.ToString();
577 parameters["passwordSalt"] = passwordSalt.ToString();
578 parameters["homeRegion"] = homeRegion.ToString();
579 parameters["homeLocationX"] = homeLocX.ToString();
580 parameters["homeLocationY"] = homeLocY.ToString();
581 parameters["homeLocationZ"] = homeLocZ.ToString();
582 parameters["homeLookAtX"] = homeLookAtX.ToString();
583 parameters["homeLookAtY"] = homeLookAtY.ToString();
584 parameters["homeLookAtZ"] = homeLookAtZ.ToString();
585 parameters["created"] = created.ToString();
586 parameters["lastLogin"] = lastlogin.ToString();
587 parameters["userInventoryURI"] = String.Empty;
588 parameters["userAssetURI"] = String.Empty;
589 parameters["profileCanDoMask"] = "0";
590 parameters["profileWantDoMask"] = "0";
591 parameters["profileAboutText"] = aboutText;
592 parameters["profileFirstText"] = firstText;
593 parameters["profileImage"] = profileImage.ToString();
594 parameters["profileFirstImage"] = firstImage.ToString();
595 parameters["webLoginKey"] = LLUUID.Random().ToString();
596
597 bool returnval = false;
598
599 try
600 {
601 IDbCommand result = database.Query(sql, parameters);
602
603 if (result.ExecuteNonQuery() == 1)
604 returnval = true;
605
606 result.Dispose();
607 }
608 catch (Exception e)
609 {
610 m_log.Error(e.ToString());
611 return false;
612 }
613
614 return returnval;
615 }
616
617 /// <summary>
618 /// Creates a new agent
619 /// </summary>
620 /// <param name="agent">The agent to create</param>
621 override public void AddNewUserAgent(UserAgentData agent)
622 {
623 // Do nothing.
624 }
625
626
627 override public bool UpdateUserProfile(UserProfileData user)
628 {
629 SqlCommand command = new SqlCommand("UPDATE " + m_usersTableName + " set UUID = @uuid, " +
630 "username = @username, " +
631 "lastname = @lastname," +
632 "passwordHash = @passwordHash," +
633 "passwordSalt = @passwordSalt," +
634 "homeRegion = @homeRegion," +
635 "homeLocationX = @homeLocationX," +
636 "homeLocationY = @homeLocationY," +
637 "homeLocationZ = @homeLocationZ," +
638 "homeLookAtX = @homeLookAtX," +
639 "homeLookAtY = @homeLookAtY," +
640 "homeLookAtZ = @homeLookAtZ," +
641 "created = @created," +
642 "lastLogin = @lastLogin," +
643 "userInventoryURI = @userInventoryURI," +
644 "userAssetURI = @userAssetURI," +
645 "profileCanDoMask = @profileCanDoMask," +
646 "profileWantDoMask = @profileWantDoMask," +
647 "profileAboutText = @profileAboutText," +
648 "profileFirstText = @profileFirstText," +
649 "profileImage = @profileImage," +
650 "profileFirstImage = @profileFirstImage, " +
651 "webLoginKey = @webLoginKey where " +
652 "UUID = @keyUUUID;", database.getConnection());
653 SqlParameter param1 = new SqlParameter("@uuid", user.UUID.ToString());
654 SqlParameter param2 = new SqlParameter("@username", user.username);
655 SqlParameter param3 = new SqlParameter("@lastname", user.surname);
656 SqlParameter param4 = new SqlParameter("@passwordHash", user.passwordHash);
657 SqlParameter param5 = new SqlParameter("@passwordSalt", user.passwordSalt);
658 SqlParameter param6 = new SqlParameter("@homeRegion", Convert.ToInt64(user.homeRegion));
659 SqlParameter param7 = new SqlParameter("@homeLocationX", user.homeLocation.X);
660 SqlParameter param8 = new SqlParameter("@homeLocationY", user.homeLocation.Y);
661 SqlParameter param9 = new SqlParameter("@homeLocationZ", user.homeLocation.Y);
662 SqlParameter param10 = new SqlParameter("@homeLookAtX", user.homeLookAt.X);
663 SqlParameter param11 = new SqlParameter("@homeLookAtY", user.homeLookAt.Y);
664 SqlParameter param12 = new SqlParameter("@homeLookAtZ", user.homeLookAt.Z);
665 SqlParameter param13 = new SqlParameter("@created", Convert.ToInt32(user.created));
666 SqlParameter param14 = new SqlParameter("@lastLogin", Convert.ToInt32(user.lastLogin));
667 SqlParameter param15 = new SqlParameter("@userInventoryURI", user.userInventoryURI);
668 SqlParameter param16 = new SqlParameter("@userAssetURI", user.userAssetURI);
669 SqlParameter param17 = new SqlParameter("@profileCanDoMask", Convert.ToInt32(user.profileCanDoMask));
670 SqlParameter param18 = new SqlParameter("@profileWantDoMask", Convert.ToInt32(user.profileWantDoMask));
671 SqlParameter param19 = new SqlParameter("@profileAboutText", user.profileAboutText);
672 SqlParameter param20 = new SqlParameter("@profileFirstText", user.profileFirstText);
673 SqlParameter param21 = new SqlParameter("@profileImage", user.profileImage.ToString());
674 SqlParameter param22 = new SqlParameter("@profileFirstImage", user.profileFirstImage.ToString());
675 SqlParameter param23 = new SqlParameter("@keyUUUID", user.UUID.ToString());
676 SqlParameter param24 = new SqlParameter("@webLoginKey", user.webLoginKey.UUID.ToString());
677 command.Parameters.Add(param1);
678 command.Parameters.Add(param2);
679 command.Parameters.Add(param3);
680 command.Parameters.Add(param4);
681 command.Parameters.Add(param5);
682 command.Parameters.Add(param6);
683 command.Parameters.Add(param7);
684 command.Parameters.Add(param8);
685 command.Parameters.Add(param9);
686 command.Parameters.Add(param10);
687 command.Parameters.Add(param11);
688 command.Parameters.Add(param12);
689 command.Parameters.Add(param13);
690 command.Parameters.Add(param14);
691 command.Parameters.Add(param15);
692 command.Parameters.Add(param16);
693 command.Parameters.Add(param17);
694 command.Parameters.Add(param18);
695 command.Parameters.Add(param19);
696 command.Parameters.Add(param20);
697 command.Parameters.Add(param21);
698 command.Parameters.Add(param22);
699 command.Parameters.Add(param23);
700 command.Parameters.Add(param24);
701 try
702 {
703 int affected = command.ExecuteNonQuery();
704 if (affected != 0)
705 {
706 return true;
707 }
708 else
709 {
710 return false;
711 }
712 }
713 catch (Exception e)
714 {
715 m_log.Error(e.ToString());
716 }
717 return false;
718 }
719
720 /// <summary>
721 /// Performs a money transfer request between two accounts
722 /// </summary>
723 /// <param name="from">The senders account ID</param>
724 /// <param name="to">The receivers account ID</param>
725 /// <param name="amount">The amount to transfer</param>
726 /// <returns>Success?</returns>
727 override public bool MoneyTransferRequest(LLUUID from, LLUUID to, uint amount)
728 {
729 return false;
730 }
731
732 /// <summary>
733 /// Performs an inventory transfer request between two accounts
734 /// </summary>
735 /// <remarks>TODO: Move to inventory server</remarks>
736 /// <param name="from">The senders account ID</param>
737 /// <param name="to">The receivers account ID</param>
738 /// <param name="item">The item to transfer</param>
739 /// <returns>Success?</returns>
740 override public bool InventoryTransferRequest(LLUUID from, LLUUID to, LLUUID item)
741 {
742 return false;
743 }
744
745 /// <summary>
746 /// Database provider name
747 /// </summary>
748 /// <returns>Provider name</returns>
749 override public string getName()
750 {
751 return "MSSQL Userdata Interface";
752 }
753
754 /// <summary>
755 /// Database provider version
756 /// </summary>
757 /// <returns>provider version</returns>
758 override public string GetVersion()
759 {
760 return database.getVersion();
761 }
762
763 /// <summary>
764 /// Not implemented
765 /// </summary>
766 /// <param name="query"></param>
767 public void runQuery(string query)
768 {
769 }
770 }
771}
diff --git a/OpenSim/Data/MSSQL/Properties/AssemblyInfo.cs b/OpenSim/Data/MSSQL/Properties/AssemblyInfo.cs
new file mode 100644
index 0000000..f6ac328
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Properties/AssemblyInfo.cs
@@ -0,0 +1,65 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSim Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System.Reflection;
29using System.Runtime.InteropServices;
30
31// General Information about an assembly is controlled through the following
32// set of attributes. Change these attribute values to modify the information
33// associated with an assembly.
34
35[assembly : AssemblyTitle("OpenSim.Framework.Data.MSSQL")]
36[assembly : AssemblyDescription("")]
37[assembly : AssemblyConfiguration("")]
38[assembly : AssemblyCompany("")]
39[assembly : AssemblyProduct("OpenSim.Framework.Data.MSSQL")]
40[assembly : AssemblyCopyright("Copyright (c) OpenSimulator.org Developers 2007-2008")]
41[assembly : AssemblyTrademark("")]
42[assembly : AssemblyCulture("")]
43
44// Setting ComVisible to false makes the types in this assembly not visible
45// to COM components. If you need to access a type in this assembly from
46// COM, set the ComVisible attribute to true on that type.
47
48[assembly : ComVisible(false)]
49
50// The following GUID is for the ID of the typelib if this project is exposed to COM
51
52[assembly : Guid("0e1c1ca4-2cf2-4315-b0e7-432c02feea8a")]
53
54// Version information for an assembly consists of the following four values:
55//
56// Major Version
57// Minor Version
58// Build Number
59// Revision
60//
61// You can specify all the values or you can default the Revision and Build Numbers
62// by using the '*' as shown below:
63
64[assembly : AssemblyVersion("1.0.0.0")]
65[assembly : AssemblyFileVersion("1.0.0.0")]
diff --git a/OpenSim/Data/MSSQL/Resources/AvatarAppearance.sql b/OpenSim/Data/MSSQL/Resources/AvatarAppearance.sql
new file mode 100644
index 0000000..ccefba2
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/AvatarAppearance.sql
@@ -0,0 +1,44 @@
1--
2-- Create schema avatar_appearance
3--
4
5SET ANSI_NULLS ON
6SET QUOTED_IDENTIFIER ON
7SET ANSI_PADDING ON
8
9CREATE TABLE [avatarappearance] (
10 [UUID] uniqueidentifier NOT NULL,
11 [Serial] int NOT NULL,
12 [WearableItem0] uniqueidentifier NOT NULL,
13 [WearableAsset0] uniqueidentifier NOT NULL,
14 [WearableItem1] uniqueidentifier NOT NULL,
15 [WearableAsset1] uniqueidentifier NOT NULL,
16 [WearableItem2] uniqueidentifier NOT NULL,
17 [WearableAsset2] uniqueidentifier NOT NULL,
18 [WearableItem3] uniqueidentifier NOT NULL,
19 [WearableAsset3] uniqueidentifier NOT NULL,
20 [WearableItem4] uniqueidentifier NOT NULL,
21 [WearableAsset4] uniqueidentifier NOT NULL,
22 [WearableItem5] uniqueidentifier NOT NULL,
23 [WearableAsset5] uniqueidentifier NOT NULL,
24 [WearableItem6] uniqueidentifier NOT NULL,
25 [WearableAsset6] uniqueidentifier NOT NULL,
26 [WearableItem7] uniqueidentifier NOT NULL,
27 [WearableAsset7] uniqueidentifier NOT NULL,
28 [WearableItem8] uniqueidentifier NOT NULL,
29 [WearableAsset8] uniqueidentifier NOT NULL,
30 [WearableItem9] uniqueidentifier NOT NULL,
31 [WearableAsset9] uniqueidentifier NOT NULL,
32 [WearableItem10] uniqueidentifier NOT NULL,
33 [WearableAsset10] uniqueidentifier NOT NULL,
34 [WearableItem11] uniqueidentifier NOT NULL,
35 [WearableAsset11] uniqueidentifier NOT NULL,
36 [WearableItem12] uniqueidentifier NOT NULL,
37 [WearableAsset12] uniqueidentifier NOT NULL
38
39 PRIMARY KEY CLUSTERED (
40 [UUID]
41 ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
42) ON [PRIMARY]
43
44SET ANSI_PADDING OFF
diff --git a/OpenSim/Data/MSSQL/Resources/CreateAssetsTable.sql b/OpenSim/Data/MSSQL/Resources/CreateAssetsTable.sql
new file mode 100644
index 0000000..c7cb21a
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/CreateAssetsTable.sql
@@ -0,0 +1,19 @@
1SET ANSI_NULLS ON
2SET QUOTED_IDENTIFIER ON
3SET ANSI_PADDING ON
4CREATE TABLE [assets] (
5 [id] [varchar](36) NOT NULL,
6 [name] [varchar](64) NOT NULL,
7 [description] [varchar](64) NOT NULL,
8 [assetType] [tinyint] NOT NULL,
9 [invType] [tinyint] NOT NULL,
10 [local] [tinyint] NOT NULL,
11 [temporary] [tinyint] NOT NULL,
12 [data] [image] NOT NULL,
13PRIMARY KEY CLUSTERED
14(
15 [id] ASC
16)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
17) ON [PRIMARY]
18
19SET ANSI_PADDING OFF
diff --git a/OpenSim/Data/MSSQL/Resources/CreateFoldersTable.sql b/OpenSim/Data/MSSQL/Resources/CreateFoldersTable.sql
new file mode 100644
index 0000000..95d183a
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/CreateFoldersTable.sql
@@ -0,0 +1,27 @@
1SET ANSI_NULLS ON
2SET QUOTED_IDENTIFIER ON
3SET ANSI_PADDING ON
4CREATE TABLE [inventoryfolders] (
5 [folderID] [varchar](36) NOT NULL default '',
6 [agentID] [varchar](36) default NULL,
7 [parentFolderID] [varchar](36) default NULL,
8 [folderName] [varchar](64) default NULL,
9 [type] [smallint] NOT NULL default 0,
10 [version] [int] NOT NULL default 0,
11 PRIMARY KEY CLUSTERED
12(
13 [folderID] ASC
14)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
15) ON [PRIMARY]
16
17CREATE NONCLUSTERED INDEX [owner] ON [inventoryfolders]
18(
19 [agentID] ASC
20)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]
21
22CREATE NONCLUSTERED INDEX [parent] ON [inventoryfolders]
23(
24 [parentFolderID] ASC
25)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]
26
27SET ANSI_PADDING OFF
diff --git a/OpenSim/Data/MSSQL/Resources/CreateItemsTable.sql b/OpenSim/Data/MSSQL/Resources/CreateItemsTable.sql
new file mode 100644
index 0000000..5bb27ba
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/CreateItemsTable.sql
@@ -0,0 +1,39 @@
1SET ANSI_NULLS ON
2
3SET QUOTED_IDENTIFIER ON
4
5SET ANSI_PADDING ON
6
7CREATE TABLE [inventoryitems] (
8 [inventoryID] [varchar](36) NOT NULL default '',
9 [assetID] [varchar](36) default NULL,
10 [assetType] [int] default NULL,
11 [parentFolderID] [varchar](36) default NULL,
12 [avatarID] [varchar](36) default NULL,
13 [inventoryName] [varchar](64) default NULL,
14 [inventoryDescription] [varchar](128) default NULL,
15 [inventoryNextPermissions] [int] default NULL,
16 [inventoryCurrentPermissions] [int] default NULL,
17 [invType] [int] default NULL,
18 [creatorID] [varchar](36) default NULL,
19 [inventoryBasePermissions] [int] NOT NULL default 0,
20 [inventoryEveryOnePermissions] [int] NOT NULL default 0,
21 PRIMARY KEY CLUSTERED
22(
23 [inventoryID] ASC
24)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
25) ON [PRIMARY]
26
27
28CREATE NONCLUSTERED INDEX [owner] ON [inventoryitems]
29(
30 [avatarID] ASC
31)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]
32
33CREATE NONCLUSTERED INDEX [folder] ON [inventoryitems]
34(
35 [parentFolderID] 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
38SET ANSI_PADDING OFF
39
diff --git a/OpenSim/Data/MSSQL/Resources/CreateUserFriendsTable.sql b/OpenSim/Data/MSSQL/Resources/CreateUserFriendsTable.sql
new file mode 100644
index 0000000..6f5885e
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/CreateUserFriendsTable.sql
@@ -0,0 +1,14 @@
1SET ANSI_NULLS ON
2
3SET QUOTED_IDENTIFIER ON
4
5SET ANSI_PADDING ON
6
7CREATE TABLE [dbo].[userfriends](
8[ownerID] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
9[friendID] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
10[friendPerms] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
11[datetimestamp] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
12) ON [PRIMARY]
13
14SET ANSI_PADDING OFF
diff --git a/OpenSim/Data/MSSQL/Resources/Mssql-agents.sql b/OpenSim/Data/MSSQL/Resources/Mssql-agents.sql
new file mode 100644
index 0000000..ad53173
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/Mssql-agents.sql
@@ -0,0 +1,37 @@
1SET ANSI_NULLS ON
2
3SET QUOTED_IDENTIFIER ON
4
5SET ANSI_PADDING ON
6
7CREATE TABLE [agents] (
8 [UUID] [varchar](36) NOT NULL,
9 [sessionID] [varchar](36) NOT NULL,
10 [secureSessionID] [varchar](36) NOT NULL,
11 [agentIP] [varchar](16) NOT NULL,
12 [agentPort] [int] NOT NULL,
13 [agentOnline] [tinyint] NOT NULL,
14 [loginTime] [int] NOT NULL,
15 [logoutTime] [int] NOT NULL,
16 [currentRegion] [varchar](36) NOT NULL,
17 [currentHandle] [bigint] NOT NULL,
18 [currentPos] [varchar](64) NOT NULL,
19 PRIMARY KEY CLUSTERED
20(
21 [UUID] ASC
22)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
23) ON [PRIMARY]
24
25
26CREATE NONCLUSTERED INDEX [session] ON [agents]
27(
28 [sessionID] ASC
29)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]
30
31CREATE NONCLUSTERED INDEX [ssession] ON [agents]
32(
33 [secureSessionID] ASC
34)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]
35
36SET ANSI_PADDING OFF
37
diff --git a/OpenSim/Data/MSSQL/Resources/Mssql-logs.sql b/OpenSim/Data/MSSQL/Resources/Mssql-logs.sql
new file mode 100644
index 0000000..3b747d8
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/Mssql-logs.sql
@@ -0,0 +1,20 @@
1SET ANSI_NULLS ON
2
3SET QUOTED_IDENTIFIER ON
4
5SET ANSI_PADDING ON
6
7CREATE TABLE [logs] (
8 [logID] [int] NOT NULL,
9 [target] [varchar](36) default NULL,
10 [server] [varchar](64) default NULL,
11 [method] [varchar](64) default NULL,
12 [arguments] [varchar](255) default NULL,
13 [priority] [int] default NULL,
14 [message] [ntext],
15 PRIMARY KEY CLUSTERED
16(
17 [logID] ASC
18)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
19) ON [PRIMARY]
20
diff --git a/OpenSim/Data/MSSQL/Resources/Mssql-regions.sql b/OpenSim/Data/MSSQL/Resources/Mssql-regions.sql
new file mode 100644
index 0000000..b29a2ab
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/Mssql-regions.sql
@@ -0,0 +1,41 @@
1SET ANSI_NULLS ON
2
3SET QUOTED_IDENTIFIER ON
4
5SET ANSI_PADDING ON
6
7CREATE TABLE [dbo].[regions](
8 [regionHandle] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
9 [regionName] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
10 [uuid] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
11 [regionRecvKey] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
12 [regionSecret] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
13 [regionSendKey] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
14 [regionDataURI] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
15 [serverIP] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
16 [serverPort] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
17 [serverURI] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
18 [locX] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
19 [locY] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
20 [locZ] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
21 [eastOverrideHandle] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
22 [westOverrideHandle] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
23 [southOverrideHandle] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
24 [northOverrideHandle] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
25 [regionAssetURI] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
26 [regionAssetRecvKey] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
27 [regionAssetSendKey] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
28 [regionUserURI] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
29 [regionUserRecvKey] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
30 [regionUserSendKey] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
31 [regionMapTexture] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
32 [serverHttpPort] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
33 [serverRemotingPort] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
34 [owner_uuid] [varchar](36) COLLATE Latin1_General_CI_AS NULL,
35PRIMARY KEY CLUSTERED
36(
37 [uuid] ASC
38)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
39) ON [PRIMARY]
40
41SET ANSI_PADDING OFF
diff --git a/OpenSim/Data/MSSQL/Resources/Mssql-users.sql b/OpenSim/Data/MSSQL/Resources/Mssql-users.sql
new file mode 100644
index 0000000..abcc091
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/Mssql-users.sql
@@ -0,0 +1,42 @@
1SET ANSI_NULLS ON
2
3SET QUOTED_IDENTIFIER ON
4
5SET ANSI_PADDING ON
6
7CREATE TABLE [users] (
8 [UUID] [varchar](36) NOT NULL default '',
9 [username] [varchar](32) NOT NULL,
10 [lastname] [varchar](32) NOT NULL,
11 [passwordHash] [varchar](32) NOT NULL,
12 [passwordSalt] [varchar](32) NOT NULL,
13 [homeRegion] [bigint] default NULL,
14 [homeLocationX] [float] default NULL,
15 [homeLocationY] [float] default NULL,
16 [homeLocationZ] [float] default NULL,
17 [homeLookAtX] [float] default NULL,
18 [homeLookAtY] [float] default NULL,
19 [homeLookAtZ] [float] default NULL,
20 [created] [int] NOT NULL,
21 [lastLogin] [int] NOT NULL,
22 [userInventoryURI] [varchar](255) default NULL,
23 [userAssetURI] [varchar](255) default NULL,
24 [profileCanDoMask] [int] default NULL,
25 [profileWantDoMask] [int] default NULL,
26 [profileAboutText] [ntext],
27 [profileFirstText] [ntext],
28 [profileImage] [varchar](36) default NULL,
29 [profileFirstImage] [varchar](36) default NULL,
30 [webLoginKey] [varchar](36) default NULL,
31 PRIMARY KEY CLUSTERED
32(
33 [UUID] ASC
34)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
35) ON [PRIMARY]
36
37
38CREATE NONCLUSTERED INDEX [usernames] ON [users]
39(
40 [username] ASC,
41 [lastname] ASC
42)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]
generated by cgit v1.1 at 2024-12-15 20:39:40 +0000