aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/MySQL
diff options
context:
space:
mode:
Diffstat (limited to 'OpenSim/Data/MySQL')
-rw-r--r--OpenSim/Data/MySQL/MySQLAssetData.cs198
-rw-r--r--OpenSim/Data/MySQL/MySQLDataStore.cs1722
-rw-r--r--OpenSim/Data/MySQL/MySQLGridData.cs402
-rw-r--r--OpenSim/Data/MySQL/MySQLInventoryData.cs648
-rw-r--r--OpenSim/Data/MySQL/MySQLLogData.cs106
-rw-r--r--OpenSim/Data/MySQL/MySQLManager.cs909
-rw-r--r--OpenSim/Data/MySQL/MySQLUserData.cs643
-rw-r--r--OpenSim/Data/MySQL/Properties/AssemblyInfo.cs65
-rw-r--r--OpenSim/Data/MySQL/Resources/AvatarAppearance.sql42
-rw-r--r--OpenSim/Data/MySQL/Resources/CreateAgentsTable.sql24
-rw-r--r--OpenSim/Data/MySQL/Resources/CreateAssetsTable.sql11
-rw-r--r--OpenSim/Data/MySQL/Resources/CreateFoldersTable.sql11
-rw-r--r--OpenSim/Data/MySQL/Resources/CreateItemsTable.sql18
-rw-r--r--OpenSim/Data/MySQL/Resources/CreateLogsTable.sql10
-rw-r--r--OpenSim/Data/MySQL/Resources/CreateRegionsTable.sql32
-rw-r--r--OpenSim/Data/MySQL/Resources/CreateUserFriendsTable.sql11
-rw-r--r--OpenSim/Data/MySQL/Resources/CreateUsersTable.sql35
-rw-r--r--OpenSim/Data/MySQL/Resources/UpgradeFoldersTableToVersion2.sql4
-rw-r--r--OpenSim/Data/MySQL/Resources/UpgradeItemsTableToVersion2.sql9
-rw-r--r--OpenSim/Data/MySQL/Resources/UpgradeRegionsTableToVersion2.sql4
-rw-r--r--OpenSim/Data/MySQL/Resources/UpgradeRegionsTableToVersion3.sql18
-rw-r--r--OpenSim/Data/MySQL/Resources/UpgradeUsersTableToVersion2.sql3
22 files changed, 4925 insertions, 0 deletions
diff --git a/OpenSim/Data/MySQL/MySQLAssetData.cs b/OpenSim/Data/MySQL/MySQLAssetData.cs
new file mode 100644
index 0000000..79994ae
--- /dev/null
+++ b/OpenSim/Data/MySQL/MySQLAssetData.cs
@@ -0,0 +1,198 @@
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 libsecondlife;
32using MySql.Data.MySqlClient;
33using OpenSim.Framework.Console;
34
35namespace OpenSim.Framework.Data.MySQL
36{
37 internal class MySQLAssetData : AssetDataBase, IPlugin
38 {
39 private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
40
41 private MySQLManager _dbConnection;
42
43 #region IAssetProvider Members
44
45 private void UpgradeAssetsTable(string oldVersion)
46 {
47 // null as the version, indicates that the table didn't exist
48 if (oldVersion == null)
49 {
50 m_log.Info("[ASSETS]: Creating new database tables");
51 _dbConnection.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 _dbConnection.GetTableVersion(tableList);
65
66 UpgradeAssetsTable(tableList["assets"]);
67 }
68
69 override public AssetBase FetchAsset(LLUUID assetID)
70 {
71 AssetBase asset = null;
72 lock (_dbConnection)
73 {
74 MySqlCommand cmd =
75 new MySqlCommand(
76 "SELECT name, description, assetType, invType, local, temporary, data FROM assets WHERE id=?id",
77 _dbConnection.Connection);
78 MySqlParameter p = cmd.Parameters.Add("?id", MySqlDbType.Binary, 16);
79 p.Value = assetID.GetBytes();
80
81 try
82 {
83 using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
84 {
85 if (dbReader.Read())
86 {
87 asset = new AssetBase();
88 asset.Data = (byte[]) dbReader["data"];
89 asset.Description = (string) dbReader["description"];
90 asset.FullID = assetID;
91 asset.InvType = (sbyte) dbReader["invType"];
92 asset.Local = ((sbyte) dbReader["local"]) != 0 ? true : false;
93 asset.Name = (string) dbReader["name"];
94 asset.Type = (sbyte) dbReader["assetType"];
95 }
96 dbReader.Close();
97 cmd.Dispose();
98 }
99 }
100 catch (Exception e)
101 {
102 m_log.ErrorFormat(
103 "[ASSETS]: MySql failure fetching asset {0}" + Environment.NewLine + e.ToString()
104 + Environment.NewLine + "Attempting reconnection", assetID);
105 _dbConnection.Reconnect();
106 }
107 }
108 return asset;
109 }
110
111 override public void CreateAsset(AssetBase asset)
112 {
113 lock (_dbConnection)
114 {
115 MySqlCommand cmd =
116 new MySqlCommand(
117 "REPLACE INTO assets(id, name, description, assetType, invType, local, temporary, data)" +
118 "VALUES(?id, ?name, ?description, ?assetType, ?invType, ?local, ?temporary, ?data)",
119 _dbConnection.Connection);
120
121 // need to ensure we dispose
122 try
123 {
124 using (cmd)
125 {
126 MySqlParameter p = cmd.Parameters.Add("?id", MySqlDbType.Binary, 16);
127 p.Value = asset.FullID.GetBytes();
128 cmd.Parameters.AddWithValue("?name", asset.Name);
129 cmd.Parameters.AddWithValue("?description", asset.Description);
130 cmd.Parameters.AddWithValue("?assetType", asset.Type);
131 cmd.Parameters.AddWithValue("?invType", asset.InvType);
132 cmd.Parameters.AddWithValue("?local", asset.Local);
133 cmd.Parameters.AddWithValue("?temporary", asset.Temporary);
134 cmd.Parameters.AddWithValue("?data", asset.Data);
135 cmd.ExecuteNonQuery();
136 cmd.Dispose();
137 }
138 }
139 catch (Exception e)
140 {
141 m_log.ErrorFormat(
142 "[ASSETS]: " +
143 "MySql failure creating asset {0} with name {1}" + Environment.NewLine + e.ToString()
144 + Environment.NewLine + "Attempting reconnection", asset.FullID, asset.Name);
145 _dbConnection.Reconnect();
146 }
147 }
148 }
149
150 override public void UpdateAsset(AssetBase asset)
151 {
152 CreateAsset(asset);
153 }
154
155 override public bool ExistsAsset(LLUUID uuid)
156 {
157 throw new Exception("The method or operation is not implemented.");
158 }
159
160 /// <summary>
161 /// All writes are immediately commited to the database, so this is a no-op
162 /// </summary>
163 override public void CommitAssets()
164 {
165 }
166
167 #endregion
168
169 #region IPlugin Members
170
171 override public void Initialise()
172 {
173 IniFile GridDataMySqlFile = new IniFile("mysql_connection.ini");
174 string hostname = GridDataMySqlFile.ParseFileReadValue("hostname");
175 string database = GridDataMySqlFile.ParseFileReadValue("database");
176 string username = GridDataMySqlFile.ParseFileReadValue("username");
177 string password = GridDataMySqlFile.ParseFileReadValue("password");
178 string pooling = GridDataMySqlFile.ParseFileReadValue("pooling");
179 string port = GridDataMySqlFile.ParseFileReadValue("port");
180
181 _dbConnection = new MySQLManager(hostname, database, username, password, pooling, port);
182
183 TestTables();
184 }
185
186 override public string Version
187 {
188 get { return _dbConnection.getVersion(); }
189 }
190
191 override public string Name
192 {
193 get { return "MySQL Asset storage engine"; }
194 }
195
196 #endregion
197 }
198}
diff --git a/OpenSim/Data/MySQL/MySQLDataStore.cs b/OpenSim/Data/MySQL/MySQLDataStore.cs
new file mode 100644
index 0000000..eaa7f14
--- /dev/null
+++ b/OpenSim/Data/MySQL/MySQLDataStore.cs
@@ -0,0 +1,1722 @@
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.Diagnostics;
32using System.IO;
33using libsecondlife;
34using MySql.Data.MySqlClient;
35using OpenSim.Framework.Console;
36using OpenSim.Region.Environment.Interfaces;
37using OpenSim.Region.Environment.Scenes;
38
39namespace OpenSim.Framework.Data.MySQL
40{
41 public class MySQLDataStore : IRegionDataStore
42 {
43 private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
44
45 private const string m_primSelect = "select * from prims";
46 private const string m_shapeSelect = "select * from primshapes";
47 private const string m_itemsSelect = "select * from primitems";
48 private const string m_terrainSelect = "select * from terrain limit 1";
49 private const string m_landSelect = "select * from land";
50 private const string m_landAccessListSelect = "select * from landaccesslist";
51
52 private DataSet m_dataSet;
53 private MySqlDataAdapter m_primDataAdapter;
54 private MySqlDataAdapter m_shapeDataAdapter;
55 private MySqlDataAdapter m_itemsDataAdapter;
56 private MySqlConnection m_connection;
57 private MySqlDataAdapter m_terrainDataAdapter;
58 private MySqlDataAdapter m_landDataAdapter;
59 private MySqlDataAdapter m_landAccessListDataAdapter;
60
61 private DataTable m_primTable;
62 private DataTable m_shapeTable;
63 private DataTable m_itemsTable;
64 private DataTable m_terrainTable;
65 private DataTable m_landTable;
66 private DataTable m_landAccessListTable;
67
68 // Temporary attribute while this is experimental
69 private bool persistPrimInventories;
70
71 /***********************************************************************
72 *
73 * Public Interface Functions
74 *
75 **********************************************************************/
76
77 // see IRegionDataStore
78 public void Initialise(string connectionstring, bool persistPrimInventories)
79 {
80 m_dataSet = new DataSet();
81 this.persistPrimInventories = persistPrimInventories;
82
83 m_log.Info("[DATASTORE]: MySql - connecting: " + connectionstring);
84 m_connection = new MySqlConnection(connectionstring);
85
86 MySqlCommand primSelectCmd = new MySqlCommand(m_primSelect, m_connection);
87 m_primDataAdapter = new MySqlDataAdapter(primSelectCmd);
88
89 MySqlCommand shapeSelectCmd = new MySqlCommand(m_shapeSelect, m_connection);
90 m_shapeDataAdapter = new MySqlDataAdapter(shapeSelectCmd);
91
92 MySqlCommand itemsSelectCmd = new MySqlCommand(m_itemsSelect, m_connection);
93 m_itemsDataAdapter = new MySqlDataAdapter(itemsSelectCmd);
94
95 MySqlCommand terrainSelectCmd = new MySqlCommand(m_terrainSelect, m_connection);
96 m_terrainDataAdapter = new MySqlDataAdapter(terrainSelectCmd);
97
98 MySqlCommand landSelectCmd = new MySqlCommand(m_landSelect, m_connection);
99 m_landDataAdapter = new MySqlDataAdapter(landSelectCmd);
100
101 MySqlCommand landAccessListSelectCmd = new MySqlCommand(m_landAccessListSelect, m_connection);
102 m_landAccessListDataAdapter = new MySqlDataAdapter(landAccessListSelectCmd);
103
104 TestTables(m_connection);
105
106 lock (m_dataSet)
107 {
108 m_primTable = createPrimTable();
109 m_dataSet.Tables.Add(m_primTable);
110 SetupPrimCommands(m_primDataAdapter, m_connection);
111 m_primDataAdapter.Fill(m_primTable);
112
113 m_shapeTable = createShapeTable();
114 m_dataSet.Tables.Add(m_shapeTable);
115 SetupShapeCommands(m_shapeDataAdapter, m_connection);
116 m_shapeDataAdapter.Fill(m_shapeTable);
117
118 if (persistPrimInventories)
119 {
120 m_itemsTable = createItemsTable();
121 m_dataSet.Tables.Add(m_itemsTable);
122 SetupItemsCommands(m_itemsDataAdapter, m_connection);
123 m_itemsDataAdapter.Fill(m_itemsTable);
124 }
125
126 m_terrainTable = createTerrainTable();
127 m_dataSet.Tables.Add(m_terrainTable);
128 SetupTerrainCommands(m_terrainDataAdapter, m_connection);
129 m_terrainDataAdapter.Fill(m_terrainTable);
130
131 m_landTable = createLandTable();
132 m_dataSet.Tables.Add(m_landTable);
133 setupLandCommands(m_landDataAdapter, m_connection);
134 m_landDataAdapter.Fill(m_landTable);
135
136 m_landAccessListTable = createLandAccessListTable();
137 m_dataSet.Tables.Add(m_landAccessListTable);
138 setupLandAccessCommands(m_landAccessListDataAdapter, m_connection);
139 m_landAccessListDataAdapter.Fill(m_landAccessListTable);
140 }
141 }
142
143 public void StoreObject(SceneObjectGroup obj, LLUUID regionUUID)
144 {
145 lock (m_dataSet)
146 {
147 foreach (SceneObjectPart prim in obj.Children.Values)
148 {
149 if ((prim.ObjectFlags & (uint) LLObject.ObjectFlags.Physics) == 0)
150 {
151 m_log.Info("[DATASTORE]: Adding obj: " + obj.UUID + " to region: " + regionUUID);
152 addPrim(prim, obj.UUID, regionUUID);
153 }
154 else
155 {
156 // m_log.Info("[DATASTORE]: Ignoring Physical obj: " + obj.UUID + " in region: " + regionUUID);
157 }
158 }
159 Commit();
160 }
161 }
162
163 public void RemoveObject(LLUUID obj, LLUUID regionUUID)
164 {
165 m_log.InfoFormat("[DATASTORE]: Removing obj: {0} from region: {1}", obj.UUID, regionUUID);
166
167 DataTable prims = m_primTable;
168 DataTable shapes = m_shapeTable;
169
170 string selectExp = "SceneGroupID = '" + Util.ToRawUuidString(obj) + "'";
171 lock (m_dataSet)
172 {
173 DataRow[] primRows = prims.Select(selectExp);
174 foreach (DataRow row in primRows)
175 {
176 // Remove shapes row
177 LLUUID uuid = new LLUUID((string) row["UUID"]);
178 DataRow shapeRow = shapes.Rows.Find(Util.ToRawUuidString(uuid));
179 if (shapeRow != null)
180 {
181 shapeRow.Delete();
182 }
183
184 if (persistPrimInventories)
185 {
186 RemoveItems(uuid);
187 }
188
189 // Remove prim row
190 row.Delete();
191 }
192 Commit();
193 }
194 }
195
196 /// <summary>
197 /// Remove all persisted items of the given prim.
198 /// The caller must acquire the necessrary synchronization locks and commit or rollback changes.
199 /// </summary>
200 private void RemoveItems(LLUUID uuid)
201 {
202 String sql = String.Format("primID = '{0}'", uuid);
203 DataRow[] itemRows = m_itemsTable.Select(sql);
204
205 foreach (DataRow itemRow in itemRows)
206 {
207 itemRow.Delete();
208 }
209 }
210
211 /// <summary>
212 /// Load persisted objects from region storage.
213 /// </summary>
214 public List<SceneObjectGroup> LoadObjects(LLUUID regionUUID)
215 {
216 Dictionary<LLUUID, SceneObjectGroup> createdObjects = new Dictionary<LLUUID, SceneObjectGroup>();
217
218 List<SceneObjectGroup> retvals = new List<SceneObjectGroup>();
219
220 DataTable prims = m_primTable;
221 DataTable shapes = m_shapeTable;
222
223 string byRegion = "RegionUUID = '" + Util.ToRawUuidString(regionUUID) + "'";
224 string orderByParent = "ParentID ASC";
225
226 lock (m_dataSet)
227 {
228 DataRow[] primsForRegion = prims.Select(byRegion, orderByParent);
229 m_log.Info("[DATASTORE]: " +
230 "Loaded " + primsForRegion.Length + " prims for region: " + regionUUID);
231
232 foreach (DataRow primRow in primsForRegion)
233 {
234 try
235 {
236 string uuid = (string) primRow["UUID"];
237 string objID = (string) primRow["SceneGroupID"];
238
239 SceneObjectPart prim = buildPrim(primRow);
240
241 if (uuid == objID) //is new SceneObjectGroup ?
242 {
243 SceneObjectGroup group = new SceneObjectGroup();
244
245 DataRow shapeRow = shapes.Rows.Find(Util.ToRawUuidString(prim.UUID));
246 if (shapeRow != null)
247 {
248 prim.Shape = buildShape(shapeRow);
249 }
250 else
251 {
252 m_log.Info(
253 "No shape found for prim in storage, so setting default box shape");
254 prim.Shape = PrimitiveBaseShape.Default;
255 }
256 group.AddPart(prim);
257 group.RootPart = prim;
258
259 createdObjects.Add(group.UUID, group);
260 retvals.Add(group);
261 }
262 else
263 {
264 DataRow shapeRow = shapes.Rows.Find(Util.ToRawUuidString(prim.UUID));
265 if (shapeRow != null)
266 {
267 prim.Shape = buildShape(shapeRow);
268 }
269 else
270 {
271 m_log.Info(
272 "No shape found for prim in storage, so setting default box shape");
273 prim.Shape = PrimitiveBaseShape.Default;
274 }
275 createdObjects[new LLUUID(objID)].AddPart(prim);
276 }
277
278 if (persistPrimInventories)
279 {
280 LoadItems(prim);
281 }
282 }
283 catch (Exception e)
284 {
285 m_log.Error("[DATASTORE]: Failed create prim object, exception and data follows");
286 m_log.Info("[DATASTORE]: " + e.ToString());
287 foreach (DataColumn col in prims.Columns)
288 {
289 m_log.Info("[DATASTORE]: Col: " + col.ColumnName + " => " + primRow[col]);
290 }
291 }
292 }
293 }
294 return retvals;
295 }
296
297 /// <summary>
298 /// Load in a prim's persisted inventory.
299 /// </summary>
300 /// <param name="prim"></param>
301 private void LoadItems(SceneObjectPart prim)
302 {
303 //m_log.InfoFormat("[DATASTORE]: Loading inventory for {0}, {1}", prim.Name, prim.UUID);
304
305 DataTable dbItems = m_itemsTable;
306
307 String sql = String.Format("primID = '{0}'", prim.UUID.ToString());
308 DataRow[] dbItemRows = dbItems.Select(sql);
309
310 IList<TaskInventoryItem> inventory = new List<TaskInventoryItem>();
311
312 foreach (DataRow row in dbItemRows)
313 {
314 TaskInventoryItem item = buildItem(row);
315 inventory.Add(item);
316
317 //m_log.DebugFormat("[DATASTORE]: Restored item {0}, {1}", item.Name, item.ItemID);
318 }
319
320 prim.RestoreInventoryItems(inventory);
321
322 // XXX A nasty little hack to recover the folder id for the prim (which is currently stored in
323 // every item). This data should really be stored in the prim table itself.
324 if (dbItemRows.Length > 0)
325 {
326 prim.FolderID = inventory[0].ParentID;
327 }
328 }
329
330 public void StoreTerrain(double[,] ter, LLUUID regionID)
331 {
332 int revision = Util.UnixTimeSinceEpoch();
333 m_log.Info("[DATASTORE]: Storing terrain revision r" + revision.ToString());
334
335 DataTable terrain = m_dataSet.Tables["terrain"];
336 lock (m_dataSet)
337 {
338 MySqlCommand cmd = new MySqlCommand("insert into terrain(RegionUUID, Revision, Heightfield)" +
339 " values(?RegionUUID, ?Revision, ?Heightfield)", m_connection);
340 using (cmd)
341 {
342 cmd.Parameters.Add(new MySqlParameter("?RegionUUID", Util.ToRawUuidString(regionID)));
343 cmd.Parameters.Add(new MySqlParameter("?Revision", revision));
344 cmd.Parameters.Add(new MySqlParameter("?Heightfield", serializeTerrain(ter)));
345 cmd.ExecuteNonQuery();
346 }
347 }
348 }
349
350 public double[,] LoadTerrain(LLUUID regionID)
351 {
352 double[,] terret = new double[256,256];
353 terret.Initialize();
354
355 MySqlCommand cmd = new MySqlCommand(
356 @"select RegionUUID, Revision, Heightfield from terrain
357 where RegionUUID=?RegionUUID order by Revision desc limit 1"
358 , m_connection);
359
360 MySqlParameter param = new MySqlParameter();
361 cmd.Parameters.Add(new MySqlParameter("?RegionUUID", Util.ToRawUuidString(regionID)));
362
363 if (m_connection.State != ConnectionState.Open)
364 {
365 m_connection.Open();
366 }
367
368 lock (m_dataSet)
369 {
370 using (MySqlDataReader row = cmd.ExecuteReader())
371 {
372 int rev = 0;
373 if (row.Read())
374 {
375 MemoryStream str = new MemoryStream((byte[]) row["Heightfield"]);
376 BinaryReader br = new BinaryReader(str);
377 for (int x = 0; x < 256; x++)
378 {
379 for (int y = 0; y < 256; y++)
380 {
381 terret[x, y] = br.ReadDouble();
382 }
383 }
384 rev = (int) row["Revision"];
385 }
386 else
387 {
388 m_log.Info("[DATASTORE]: No terrain found for region");
389 return null;
390 }
391
392 m_log.Info("[DATASTORE]: Loaded terrain revision r" + rev.ToString());
393 }
394 }
395 return terret;
396 }
397
398 public void RemoveLandObject(LLUUID globalID)
399 {
400 lock (m_dataSet)
401 {
402 using (MySqlCommand cmd = new MySqlCommand("delete from land where UUID=?UUID", m_connection))
403 {
404 cmd.Parameters.Add(new MySqlParameter("?UUID", Util.ToRawUuidString(globalID)));
405 cmd.ExecuteNonQuery();
406 }
407
408 using (
409 MySqlCommand cmd = new MySqlCommand("delete from landaccesslist where LandUUID=?UUID", m_connection)
410 )
411 {
412 cmd.Parameters.Add(new MySqlParameter("?UUID", Util.ToRawUuidString(globalID)));
413 cmd.ExecuteNonQuery();
414 }
415 }
416 }
417
418 public void StoreLandObject(ILandObject parcel)
419 {
420 lock (m_dataSet)
421 {
422 DataTable land = m_landTable;
423 DataTable landaccesslist = m_landAccessListTable;
424
425 DataRow landRow = land.Rows.Find(Util.ToRawUuidString(parcel.landData.globalID));
426 if (landRow == null)
427 {
428 landRow = land.NewRow();
429 fillLandRow(landRow, parcel.landData, parcel.regionUUID);
430 land.Rows.Add(landRow);
431 }
432 else
433 {
434 fillLandRow(landRow, parcel.landData, parcel.regionUUID);
435 }
436
437 using (
438 MySqlCommand cmd =
439 new MySqlCommand("delete from landaccesslist where LandUUID=?LandUUID", m_connection))
440 {
441 cmd.Parameters.Add(new MySqlParameter("?LandUUID", Util.ToRawUuidString(parcel.landData.globalID)));
442 cmd.ExecuteNonQuery();
443 }
444
445 foreach (ParcelManager.ParcelAccessEntry entry in parcel.landData.parcelAccessList)
446 {
447 DataRow newAccessRow = landaccesslist.NewRow();
448 fillLandAccessRow(newAccessRow, entry, parcel.landData.globalID);
449 landaccesslist.Rows.Add(newAccessRow);
450 }
451
452 Commit();
453 }
454 }
455
456 public List<LandData> LoadLandObjects(LLUUID regionUUID)
457 {
458 List<LandData> landDataForRegion = new List<LandData>();
459 lock (m_dataSet)
460 {
461 DataTable land = m_landTable;
462 DataTable landaccesslist = m_landAccessListTable;
463 string searchExp = "RegionUUID = '" + Util.ToRawUuidString(regionUUID) + "'";
464 DataRow[] rawDataForRegion = land.Select(searchExp);
465 foreach (DataRow rawDataLand in rawDataForRegion)
466 {
467 LandData newLand = buildLandData(rawDataLand);
468 string accessListSearchExp = "LandUUID = '" + Util.ToRawUuidString(newLand.globalID) + "'";
469 DataRow[] rawDataForLandAccessList = landaccesslist.Select(accessListSearchExp);
470 foreach (DataRow rawDataLandAccess in rawDataForLandAccessList)
471 {
472 newLand.parcelAccessList.Add(buildLandAccessData(rawDataLandAccess));
473 }
474
475 landDataForRegion.Add(newLand);
476 }
477 }
478 return landDataForRegion;
479 }
480
481// TODO: unused
482// private void DisplayDataSet(DataSet ds, string title)
483// {
484// Debug.WriteLine(title);
485// //--- Loop through the DataTables
486// foreach (DataTable table in ds.Tables)
487// {
488// Debug.WriteLine("*** DataTable: " + table.TableName + "***");
489// //--- Loop through each DataTable's DataRows
490// foreach (DataRow row in table.Rows)
491// {
492// //--- Display the original values, if there are any.
493// if (row.HasVersion(DataRowVersion.Original))
494// {
495// Debug.Write("Original Row Values ===> ");
496// foreach (DataColumn column in table.Columns)
497// Debug.Write(column.ColumnName + " = " +
498// row[column, DataRowVersion.Original] + ", ");
499// Debug.WriteLine(String.Empty);
500// }
501// //--- Display the current values, if there are any.
502// if (row.HasVersion(DataRowVersion.Current))
503// {
504// Debug.Write("Current Row Values ====> ");
505// foreach (DataColumn column in table.Columns)
506// Debug.Write(column.ColumnName + " = " +
507// row[column, DataRowVersion.Current] + ", ");
508// Debug.WriteLine(String.Empty);
509// }
510// Debug.WriteLine(String.Empty);
511// }
512// }
513// }
514
515 public void Commit()
516 {
517 if (m_connection.State != ConnectionState.Open)
518 {
519 m_connection.Open();
520 }
521
522 lock (m_dataSet)
523 {
524 // DisplayDataSet(m_dataSet, "Region DataSet");
525
526 m_primDataAdapter.Update(m_primTable);
527 m_shapeDataAdapter.Update(m_shapeTable);
528
529 if (persistPrimInventories)
530 {
531 m_itemsDataAdapter.Update(m_itemsTable);
532 }
533
534 m_terrainDataAdapter.Update(m_terrainTable);
535 m_landDataAdapter.Update(m_landTable);
536 m_landAccessListDataAdapter.Update(m_landAccessListTable);
537
538 m_dataSet.AcceptChanges();
539 }
540 }
541
542
543 public void Shutdown()
544 {
545 Commit();
546 }
547
548 /***********************************************************************
549 *
550 * Database Definition Functions
551 *
552 * This should be db agnostic as we define them in ADO.NET terms
553 *
554 **********************************************************************/
555
556 private DataColumn createCol(DataTable dt, string name, Type type)
557 {
558 DataColumn col = new DataColumn(name, type);
559 dt.Columns.Add(col);
560 return col;
561 }
562
563 private DataTable createTerrainTable()
564 {
565 DataTable terrain = new DataTable("terrain");
566
567 createCol(terrain, "RegionUUID", typeof (String));
568 createCol(terrain, "Revision", typeof (Int32));
569 DataColumn heightField = createCol(terrain, "Heightfield", typeof (Byte[]));
570 return terrain;
571 }
572
573 private DataTable createPrimTable()
574 {
575 DataTable prims = new DataTable("prims");
576
577 createCol(prims, "UUID", typeof (String));
578 createCol(prims, "RegionUUID", typeof (String));
579 createCol(prims, "ParentID", typeof (Int32));
580 createCol(prims, "CreationDate", typeof (Int32));
581 createCol(prims, "Name", typeof (String));
582 createCol(prims, "SceneGroupID", typeof (String));
583 // various text fields
584 createCol(prims, "Text", typeof (String));
585 createCol(prims, "Description", typeof (String));
586 createCol(prims, "SitName", typeof (String));
587 createCol(prims, "TouchName", typeof (String));
588 // permissions
589 createCol(prims, "ObjectFlags", typeof (Int32));
590 createCol(prims, "CreatorID", typeof (String));
591 createCol(prims, "OwnerID", typeof (String));
592 createCol(prims, "GroupID", typeof (String));
593 createCol(prims, "LastOwnerID", typeof (String));
594 createCol(prims, "OwnerMask", typeof (Int32));
595 createCol(prims, "NextOwnerMask", typeof (Int32));
596 createCol(prims, "GroupMask", typeof (Int32));
597 createCol(prims, "EveryoneMask", typeof (Int32));
598 createCol(prims, "BaseMask", typeof (Int32));
599 // vectors
600 createCol(prims, "PositionX", typeof (Double));
601 createCol(prims, "PositionY", typeof (Double));
602 createCol(prims, "PositionZ", typeof (Double));
603 createCol(prims, "GroupPositionX", typeof (Double));
604 createCol(prims, "GroupPositionY", typeof (Double));
605 createCol(prims, "GroupPositionZ", typeof (Double));
606 createCol(prims, "VelocityX", typeof (Double));
607 createCol(prims, "VelocityY", typeof (Double));
608 createCol(prims, "VelocityZ", typeof (Double));
609 createCol(prims, "AngularVelocityX", typeof (Double));
610 createCol(prims, "AngularVelocityY", typeof (Double));
611 createCol(prims, "AngularVelocityZ", typeof (Double));
612 createCol(prims, "AccelerationX", typeof (Double));
613 createCol(prims, "AccelerationY", typeof (Double));
614 createCol(prims, "AccelerationZ", typeof (Double));
615 // quaternions
616 createCol(prims, "RotationX", typeof (Double));
617 createCol(prims, "RotationY", typeof (Double));
618 createCol(prims, "RotationZ", typeof (Double));
619 createCol(prims, "RotationW", typeof (Double));
620 // sit target
621 createCol(prims, "SitTargetOffsetX", typeof (Double));
622 createCol(prims, "SitTargetOffsetY", typeof (Double));
623 createCol(prims, "SitTargetOffsetZ", typeof (Double));
624
625 createCol(prims, "SitTargetOrientW", typeof (Double));
626 createCol(prims, "SitTargetOrientX", typeof (Double));
627 createCol(prims, "SitTargetOrientY", typeof (Double));
628 createCol(prims, "SitTargetOrientZ", typeof (Double));
629
630
631 // Add in contraints
632 prims.PrimaryKey = new DataColumn[] {prims.Columns["UUID"]};
633
634 return prims;
635 }
636
637 private DataTable createLandTable()
638 {
639 DataTable land = new DataTable("land");
640 createCol(land, "UUID", typeof (String));
641 createCol(land, "RegionUUID", typeof (String));
642 createCol(land, "LocalLandID", typeof (Int32));
643
644 // Bitmap is a byte[512]
645 createCol(land, "Bitmap", typeof (Byte[]));
646
647 createCol(land, "Name", typeof (String));
648 createCol(land, "Description", typeof (String));
649 createCol(land, "OwnerUUID", typeof (String));
650 createCol(land, "IsGroupOwned", typeof (Int32));
651 createCol(land, "Area", typeof (Int32));
652 createCol(land, "AuctionID", typeof (Int32)); //Unemplemented
653 createCol(land, "Category", typeof (Int32)); //Enum libsecondlife.Parcel.ParcelCategory
654 createCol(land, "ClaimDate", typeof (Int32));
655 createCol(land, "ClaimPrice", typeof (Int32));
656 createCol(land, "GroupUUID", typeof (String));
657 createCol(land, "SalePrice", typeof (Int32));
658 createCol(land, "LandStatus", typeof (Int32)); //Enum. libsecondlife.Parcel.ParcelStatus
659 createCol(land, "LandFlags", typeof (Int32));
660 createCol(land, "LandingType", typeof (Int32));
661 createCol(land, "MediaAutoScale", typeof (Int32));
662 createCol(land, "MediaTextureUUID", typeof (String));
663 createCol(land, "MediaURL", typeof (String));
664 createCol(land, "MusicURL", typeof (String));
665 createCol(land, "PassHours", typeof (Double));
666 createCol(land, "PassPrice", typeof (Int32));
667 createCol(land, "SnapshotUUID", typeof (String));
668 createCol(land, "UserLocationX", typeof (Double));
669 createCol(land, "UserLocationY", typeof (Double));
670 createCol(land, "UserLocationZ", typeof (Double));
671 createCol(land, "UserLookAtX", typeof (Double));
672 createCol(land, "UserLookAtY", typeof (Double));
673 createCol(land, "UserLookAtZ", typeof (Double));
674
675 land.PrimaryKey = new DataColumn[] {land.Columns["UUID"]};
676
677 return land;
678 }
679
680 private DataTable createLandAccessListTable()
681 {
682 DataTable landaccess = new DataTable("landaccesslist");
683 createCol(landaccess, "LandUUID", typeof (String));
684 createCol(landaccess, "AccessUUID", typeof (String));
685 createCol(landaccess, "Flags", typeof (Int32));
686
687 return landaccess;
688 }
689
690 private DataTable createShapeTable()
691 {
692 DataTable shapes = new DataTable("primshapes");
693 createCol(shapes, "UUID", typeof (String));
694 // shape is an enum
695 createCol(shapes, "Shape", typeof (Int32));
696 // vectors
697 createCol(shapes, "ScaleX", typeof (Double));
698 createCol(shapes, "ScaleY", typeof (Double));
699 createCol(shapes, "ScaleZ", typeof (Double));
700 // paths
701 createCol(shapes, "PCode", typeof (Int32));
702 createCol(shapes, "PathBegin", typeof (Int32));
703 createCol(shapes, "PathEnd", typeof (Int32));
704 createCol(shapes, "PathScaleX", typeof (Int32));
705 createCol(shapes, "PathScaleY", typeof (Int32));
706 createCol(shapes, "PathShearX", typeof (Int32));
707 createCol(shapes, "PathShearY", typeof (Int32));
708 createCol(shapes, "PathSkew", typeof (Int32));
709 createCol(shapes, "PathCurve", typeof (Int32));
710 createCol(shapes, "PathRadiusOffset", typeof (Int32));
711 createCol(shapes, "PathRevolutions", typeof (Int32));
712 createCol(shapes, "PathTaperX", typeof (Int32));
713 createCol(shapes, "PathTaperY", typeof (Int32));
714 createCol(shapes, "PathTwist", typeof (Int32));
715 createCol(shapes, "PathTwistBegin", typeof (Int32));
716 // profile
717 createCol(shapes, "ProfileBegin", typeof (Int32));
718 createCol(shapes, "ProfileEnd", typeof (Int32));
719 createCol(shapes, "ProfileCurve", typeof (Int32));
720 createCol(shapes, "ProfileHollow", typeof (Int32));
721 createCol(shapes, "State", typeof(Int32));
722 createCol(shapes, "Texture", typeof (Byte[]));
723 createCol(shapes, "ExtraParams", typeof (Byte[]));
724
725 shapes.PrimaryKey = new DataColumn[] {shapes.Columns["UUID"]};
726
727 return shapes;
728 }
729
730 private DataTable createItemsTable()
731 {
732 DataTable items = new DataTable("primitems");
733
734 createCol(items, "itemID", typeof (String));
735 createCol(items, "primID", typeof (String));
736 createCol(items, "assetID", typeof (String));
737 createCol(items, "parentFolderID", typeof (String));
738
739 createCol(items, "invType", typeof (Int32));
740 createCol(items, "assetType", typeof (Int32));
741
742 createCol(items, "name", typeof (String));
743 createCol(items, "description", typeof (String));
744
745 createCol(items, "creationDate", typeof (Int64));
746 createCol(items, "creatorID", typeof (String));
747 createCol(items, "ownerID", typeof (String));
748 createCol(items, "lastOwnerID", typeof (String));
749 createCol(items, "groupID", typeof (String));
750
751 createCol(items, "nextPermissions", typeof (Int32));
752 createCol(items, "currentPermissions", typeof (Int32));
753 createCol(items, "basePermissions", typeof (Int32));
754 createCol(items, "everyonePermissions", typeof (Int32));
755 createCol(items, "groupPermissions", typeof (Int32));
756
757 items.PrimaryKey = new DataColumn[] {items.Columns["itemID"]};
758
759 return items;
760 }
761
762 /***********************************************************************
763 *
764 * Convert between ADO.NET <=> OpenSim Objects
765 *
766 * These should be database independant
767 *
768 **********************************************************************/
769
770 private SceneObjectPart buildPrim(DataRow row)
771 {
772 SceneObjectPart prim = new SceneObjectPart();
773 prim.UUID = new LLUUID((String) row["UUID"]);
774 // explicit conversion of integers is required, which sort
775 // of sucks. No idea if there is a shortcut here or not.
776 prim.ParentID = Convert.ToUInt32(row["ParentID"]);
777 prim.CreationDate = Convert.ToInt32(row["CreationDate"]);
778 prim.Name = (String) row["Name"];
779 // various text fields
780 prim.Text = (String) row["Text"];
781 prim.Description = (String) row["Description"];
782 prim.SitName = (String) row["SitName"];
783 prim.TouchName = (String) row["TouchName"];
784 // permissions
785 prim.ObjectFlags = Convert.ToUInt32(row["ObjectFlags"]);
786 prim.CreatorID = new LLUUID((String) row["CreatorID"]);
787 prim.OwnerID = new LLUUID((String) row["OwnerID"]);
788 prim.GroupID = new LLUUID((String) row["GroupID"]);
789 prim.LastOwnerID = new LLUUID((String) row["LastOwnerID"]);
790 prim.OwnerMask = Convert.ToUInt32(row["OwnerMask"]);
791 prim.NextOwnerMask = Convert.ToUInt32(row["NextOwnerMask"]);
792 prim.GroupMask = Convert.ToUInt32(row["GroupMask"]);
793 prim.EveryoneMask = Convert.ToUInt32(row["EveryoneMask"]);
794 prim.BaseMask = Convert.ToUInt32(row["BaseMask"]);
795 // vectors
796 prim.OffsetPosition = new LLVector3(
797 Convert.ToSingle(row["PositionX"]),
798 Convert.ToSingle(row["PositionY"]),
799 Convert.ToSingle(row["PositionZ"])
800 );
801 prim.GroupPosition = new LLVector3(
802 Convert.ToSingle(row["GroupPositionX"]),
803 Convert.ToSingle(row["GroupPositionY"]),
804 Convert.ToSingle(row["GroupPositionZ"])
805 );
806 prim.Velocity = new LLVector3(
807 Convert.ToSingle(row["VelocityX"]),
808 Convert.ToSingle(row["VelocityY"]),
809 Convert.ToSingle(row["VelocityZ"])
810 );
811 prim.AngularVelocity = new LLVector3(
812 Convert.ToSingle(row["AngularVelocityX"]),
813 Convert.ToSingle(row["AngularVelocityY"]),
814 Convert.ToSingle(row["AngularVelocityZ"])
815 );
816 prim.Acceleration = new LLVector3(
817 Convert.ToSingle(row["AccelerationX"]),
818 Convert.ToSingle(row["AccelerationY"]),
819 Convert.ToSingle(row["AccelerationZ"])
820 );
821 // quaternions
822 prim.RotationOffset = new LLQuaternion(
823 Convert.ToSingle(row["RotationX"]),
824 Convert.ToSingle(row["RotationY"]),
825 Convert.ToSingle(row["RotationZ"]),
826 Convert.ToSingle(row["RotationW"])
827 );
828 try
829 {
830 prim.SetSitTargetLL(new LLVector3(
831 Convert.ToSingle(row["SitTargetOffsetX"]),
832 Convert.ToSingle(row["SitTargetOffsetY"]),
833 Convert.ToSingle(row["SitTargetOffsetZ"])), new LLQuaternion(
834 Convert.ToSingle(
835 row["SitTargetOrientX"]),
836 Convert.ToSingle(
837 row["SitTargetOrientY"]),
838 Convert.ToSingle(
839 row["SitTargetOrientZ"]),
840 Convert.ToSingle(
841 row["SitTargetOrientW"])));
842 }
843 catch (InvalidCastException)
844 {
845 // Database table was created before we got here and needs to be created! :P
846
847 using (
848 MySqlCommand cmd =
849 new MySqlCommand(
850 "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;",
851 m_connection))
852 {
853 cmd.ExecuteNonQuery();
854 }
855 }
856 return prim;
857 }
858
859
860 /// <summary>
861 /// Build a prim inventory item from the persisted data.
862 /// </summary>
863 /// <param name="row"></param>
864 /// <returns></returns>
865 private TaskInventoryItem buildItem(DataRow row)
866 {
867 TaskInventoryItem taskItem = new TaskInventoryItem();
868
869 taskItem.ItemID = new LLUUID((String)row["itemID"]);
870 taskItem.ParentPartID = new LLUUID((String)row["primID"]);
871 taskItem.AssetID = new LLUUID((String)row["assetID"]);
872 taskItem.ParentID = new LLUUID((String)row["parentFolderID"]);
873
874 taskItem.InvType = Convert.ToInt32(row["invType"]);
875 taskItem.Type = Convert.ToInt32(row["assetType"]);
876
877 taskItem.Name = (String)row["name"];
878 taskItem.Description = (String)row["description"];
879 taskItem.CreationDate = Convert.ToUInt32(row["creationDate"]);
880 taskItem.CreatorID = new LLUUID((String)row["creatorID"]);
881 taskItem.OwnerID = new LLUUID((String)row["ownerID"]);
882 taskItem.LastOwnerID = new LLUUID((String)row["lastOwnerID"]);
883 taskItem.GroupID = new LLUUID((String)row["groupID"]);
884
885 taskItem.NextOwnerMask = Convert.ToUInt32(row["nextPermissions"]);
886 taskItem.OwnerMask = Convert.ToUInt32(row["currentPermissions"]);
887 taskItem.BaseMask = Convert.ToUInt32(row["basePermissions"]);
888 taskItem.EveryoneMask = Convert.ToUInt32(row["everyonePermissions"]);
889 taskItem.GroupMask = Convert.ToUInt32(row["groupPermissions"]);
890
891 return taskItem;
892 }
893
894 private LandData buildLandData(DataRow row)
895 {
896 LandData newData = new LandData();
897
898 newData.globalID = new LLUUID((String) row["UUID"]);
899 newData.localID = Convert.ToInt32(row["LocalLandID"]);
900
901 // Bitmap is a byte[512]
902 newData.landBitmapByteArray = (Byte[]) row["Bitmap"];
903
904 newData.landName = (String) row["Name"];
905 newData.landDesc = (String) row["Description"];
906 newData.ownerID = (String) row["OwnerUUID"];
907 newData.isGroupOwned = Convert.ToBoolean(row["IsGroupOwned"]);
908 newData.area = Convert.ToInt32(row["Area"]);
909 newData.auctionID = Convert.ToUInt32(row["AuctionID"]); //Unemplemented
910 newData.category = (Parcel.ParcelCategory) Convert.ToInt32(row["Category"]);
911 //Enum libsecondlife.Parcel.ParcelCategory
912 newData.claimDate = Convert.ToInt32(row["ClaimDate"]);
913 newData.claimPrice = Convert.ToInt32(row["ClaimPrice"]);
914 newData.groupID = new LLUUID((String) row["GroupUUID"]);
915 newData.salePrice = Convert.ToInt32(row["SalePrice"]);
916 newData.landStatus = (Parcel.ParcelStatus) Convert.ToInt32(row["LandStatus"]);
917 //Enum. libsecondlife.Parcel.ParcelStatus
918 newData.landFlags = Convert.ToUInt32(row["LandFlags"]);
919 newData.landingType = Convert.ToByte(row["LandingType"]);
920 newData.mediaAutoScale = Convert.ToByte(row["MediaAutoScale"]);
921 newData.mediaID = new LLUUID((String) row["MediaTextureUUID"]);
922 newData.mediaURL = (String) row["MediaURL"];
923 newData.musicURL = (String) row["MusicURL"];
924 newData.passHours = Convert.ToSingle(row["PassHours"]);
925 newData.passPrice = Convert.ToInt32(row["PassPrice"]);
926 newData.snapshotID = (String) row["SnapshotUUID"];
927
928 newData.userLocation =
929 new LLVector3(Convert.ToSingle(row["UserLocationX"]), Convert.ToSingle(row["UserLocationY"]),
930 Convert.ToSingle(row["UserLocationZ"]));
931 newData.userLookAt =
932 new LLVector3(Convert.ToSingle(row["UserLookAtX"]), Convert.ToSingle(row["UserLookAtY"]),
933 Convert.ToSingle(row["UserLookAtZ"]));
934 newData.parcelAccessList = new List<ParcelManager.ParcelAccessEntry>();
935
936 return newData;
937 }
938
939 private ParcelManager.ParcelAccessEntry buildLandAccessData(DataRow row)
940 {
941 ParcelManager.ParcelAccessEntry entry = new ParcelManager.ParcelAccessEntry();
942 entry.AgentID = new LLUUID((string) row["AccessUUID"]);
943 entry.Flags = (ParcelManager.AccessList) Convert.ToInt32(row["Flags"]);
944 entry.Time = new DateTime();
945 return entry;
946 }
947
948 private Array serializeTerrain(double[,] val)
949 {
950 MemoryStream str = new MemoryStream(65536*sizeof (double));
951 BinaryWriter bw = new BinaryWriter(str);
952
953 // TODO: COMPATIBILITY - Add byte-order conversions
954 for (int x = 0; x < 256; x++)
955 for (int y = 0; y < 256; y++)
956 bw.Write(val[x, y]);
957
958 return str.ToArray();
959 }
960
961 private void fillPrimRow(DataRow row, SceneObjectPart prim, LLUUID sceneGroupID, LLUUID regionUUID)
962 {
963 row["UUID"] = Util.ToRawUuidString(prim.UUID);
964 row["RegionUUID"] = Util.ToRawUuidString(regionUUID);
965 row["ParentID"] = prim.ParentID;
966 row["CreationDate"] = prim.CreationDate;
967 row["Name"] = prim.Name;
968 row["SceneGroupID"] = Util.ToRawUuidString(sceneGroupID);
969 // the UUID of the root part for this SceneObjectGroup
970 // various text fields
971 row["Text"] = prim.Text;
972 row["Description"] = prim.Description;
973 row["SitName"] = prim.SitName;
974 row["TouchName"] = prim.TouchName;
975 // permissions
976 row["ObjectFlags"] = prim.ObjectFlags;
977 row["CreatorID"] = Util.ToRawUuidString(prim.CreatorID);
978 row["OwnerID"] = Util.ToRawUuidString(prim.OwnerID);
979 row["GroupID"] = Util.ToRawUuidString(prim.GroupID);
980 row["LastOwnerID"] = Util.ToRawUuidString(prim.LastOwnerID);
981 row["OwnerMask"] = prim.OwnerMask;
982 row["NextOwnerMask"] = prim.NextOwnerMask;
983 row["GroupMask"] = prim.GroupMask;
984 row["EveryoneMask"] = prim.EveryoneMask;
985 row["BaseMask"] = prim.BaseMask;
986 // vectors
987 row["PositionX"] = prim.OffsetPosition.X;
988 row["PositionY"] = prim.OffsetPosition.Y;
989 row["PositionZ"] = prim.OffsetPosition.Z;
990 row["GroupPositionX"] = prim.GroupPosition.X;
991 row["GroupPositionY"] = prim.GroupPosition.Y;
992 row["GroupPositionZ"] = prim.GroupPosition.Z;
993 row["VelocityX"] = prim.Velocity.X;
994 row["VelocityY"] = prim.Velocity.Y;
995 row["VelocityZ"] = prim.Velocity.Z;
996 row["AngularVelocityX"] = prim.AngularVelocity.X;
997 row["AngularVelocityY"] = prim.AngularVelocity.Y;
998 row["AngularVelocityZ"] = prim.AngularVelocity.Z;
999 row["AccelerationX"] = prim.Acceleration.X;
1000 row["AccelerationY"] = prim.Acceleration.Y;
1001 row["AccelerationZ"] = prim.Acceleration.Z;
1002 // quaternions
1003 row["RotationX"] = prim.RotationOffset.X;
1004 row["RotationY"] = prim.RotationOffset.Y;
1005 row["RotationZ"] = prim.RotationOffset.Z;
1006 row["RotationW"] = prim.RotationOffset.W;
1007
1008 try
1009 {
1010 // Sit target
1011 LLVector3 sitTargetPos = prim.GetSitTargetPositionLL();
1012 row["SitTargetOffsetX"] = sitTargetPos.X;
1013 row["SitTargetOffsetY"] = sitTargetPos.Y;
1014 row["SitTargetOffsetZ"] = sitTargetPos.Z;
1015
1016 LLQuaternion sitTargetOrient = prim.GetSitTargetOrientationLL();
1017 row["SitTargetOrientW"] = sitTargetOrient.W;
1018 row["SitTargetOrientX"] = sitTargetOrient.X;
1019 row["SitTargetOrientY"] = sitTargetOrient.Y;
1020 row["SitTargetOrientZ"] = sitTargetOrient.Z;
1021 }
1022 catch (MySqlException)
1023 {
1024 // Database table was created before we got here and needs to be created! :P
1025
1026 using (
1027 MySqlCommand cmd =
1028 new MySqlCommand(
1029 "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;",
1030 m_connection))
1031 {
1032 cmd.ExecuteNonQuery();
1033 }
1034 }
1035 }
1036
1037 private void fillItemRow(DataRow row, TaskInventoryItem taskItem)
1038 {
1039 row["itemID"] = taskItem.ItemID;
1040 row["primID"] = taskItem.ParentPartID;
1041 row["assetID"] = taskItem.AssetID;
1042 row["parentFolderID"] = taskItem.ParentID;
1043
1044 row["invType"] = taskItem.InvType;
1045 row["assetType"] = taskItem.Type;
1046
1047 row["name"] = taskItem.Name;
1048 row["description"] = taskItem.Description;
1049 row["creationDate"] = taskItem.CreationDate;
1050 row["creatorID"] = taskItem.CreatorID;
1051 row["ownerID"] = taskItem.OwnerID;
1052 row["lastOwnerID"] = taskItem.LastOwnerID;
1053 row["groupID"] = taskItem.GroupID;
1054 row["nextPermissions"] = taskItem.NextOwnerMask;
1055 row["currentPermissions"] = taskItem.OwnerMask;
1056 row["basePermissions"] = taskItem.BaseMask;
1057 row["everyonePermissions"] = taskItem.EveryoneMask;
1058 row["groupPermissions"] = taskItem.GroupMask;
1059 }
1060
1061 private void fillLandRow(DataRow row, LandData land, LLUUID regionUUID)
1062 {
1063 row["UUID"] = Util.ToRawUuidString(land.globalID);
1064 row["RegionUUID"] = Util.ToRawUuidString(regionUUID);
1065 row["LocalLandID"] = land.localID;
1066
1067 // Bitmap is a byte[512]
1068 row["Bitmap"] = land.landBitmapByteArray;
1069
1070 row["Name"] = land.landName;
1071 row["Description"] = land.landDesc;
1072 row["OwnerUUID"] = Util.ToRawUuidString(land.ownerID);
1073 row["IsGroupOwned"] = land.isGroupOwned;
1074 row["Area"] = land.area;
1075 row["AuctionID"] = land.auctionID; //Unemplemented
1076 row["Category"] = land.category; //Enum libsecondlife.Parcel.ParcelCategory
1077 row["ClaimDate"] = land.claimDate;
1078 row["ClaimPrice"] = land.claimPrice;
1079 row["GroupUUID"] = Util.ToRawUuidString(land.groupID);
1080 row["SalePrice"] = land.salePrice;
1081 row["LandStatus"] = land.landStatus; //Enum. libsecondlife.Parcel.ParcelStatus
1082 row["LandFlags"] = land.landFlags;
1083 row["LandingType"] = land.landingType;
1084 row["MediaAutoScale"] = land.mediaAutoScale;
1085 row["MediaTextureUUID"] = Util.ToRawUuidString(land.mediaID);
1086 row["MediaURL"] = land.mediaURL;
1087 row["MusicURL"] = land.musicURL;
1088 row["PassHours"] = land.passHours;
1089 row["PassPrice"] = land.passPrice;
1090 row["SnapshotUUID"] = Util.ToRawUuidString(land.snapshotID);
1091 row["UserLocationX"] = land.userLocation.X;
1092 row["UserLocationY"] = land.userLocation.Y;
1093 row["UserLocationZ"] = land.userLocation.Z;
1094 row["UserLookAtX"] = land.userLookAt.X;
1095 row["UserLookAtY"] = land.userLookAt.Y;
1096 row["UserLookAtZ"] = land.userLookAt.Z;
1097 }
1098
1099 private void fillLandAccessRow(DataRow row, ParcelManager.ParcelAccessEntry entry, LLUUID parcelID)
1100 {
1101 row["LandUUID"] = Util.ToRawUuidString(parcelID);
1102 row["AccessUUID"] = Util.ToRawUuidString(entry.AgentID);
1103 row["Flags"] = entry.Flags;
1104 }
1105
1106 private PrimitiveBaseShape buildShape(DataRow row)
1107 {
1108 PrimitiveBaseShape s = new PrimitiveBaseShape();
1109 s.Scale = new LLVector3(
1110 Convert.ToSingle(row["ScaleX"]),
1111 Convert.ToSingle(row["ScaleY"]),
1112 Convert.ToSingle(row["ScaleZ"])
1113 );
1114 // paths
1115 s.PCode = Convert.ToByte(row["PCode"]);
1116 s.PathBegin = Convert.ToUInt16(row["PathBegin"]);
1117 s.PathEnd = Convert.ToUInt16(row["PathEnd"]);
1118 s.PathScaleX = Convert.ToByte(row["PathScaleX"]);
1119 s.PathScaleY = Convert.ToByte(row["PathScaleY"]);
1120 s.PathShearX = Convert.ToByte(row["PathShearX"]);
1121 s.PathShearY = Convert.ToByte(row["PathShearY"]);
1122 s.PathSkew = Convert.ToSByte(row["PathSkew"]);
1123 s.PathCurve = Convert.ToByte(row["PathCurve"]);
1124 s.PathRadiusOffset = Convert.ToSByte(row["PathRadiusOffset"]);
1125 s.PathRevolutions = Convert.ToByte(row["PathRevolutions"]);
1126 s.PathTaperX = Convert.ToSByte(row["PathTaperX"]);
1127 s.PathTaperY = Convert.ToSByte(row["PathTaperY"]);
1128 s.PathTwist = Convert.ToSByte(row["PathTwist"]);
1129 s.PathTwistBegin = Convert.ToSByte(row["PathTwistBegin"]);
1130 // profile
1131 s.ProfileBegin = Convert.ToUInt16(row["ProfileBegin"]);
1132 s.ProfileEnd = Convert.ToUInt16(row["ProfileEnd"]);
1133 s.ProfileCurve = Convert.ToByte(row["ProfileCurve"]);
1134 s.ProfileHollow = Convert.ToUInt16(row["ProfileHollow"]);
1135
1136 byte[] textureEntry = (byte[]) row["Texture"];
1137 s.TextureEntry = textureEntry;
1138
1139 s.ExtraParams = (byte[]) row["ExtraParams"];
1140
1141 try
1142 {
1143 s.State = Convert.ToByte(row["State"]);
1144 }
1145 catch (InvalidCastException)
1146 {
1147 // Database table was created before we got here and needs to be created! :P
1148
1149 using (
1150 MySqlCommand cmd =
1151 new MySqlCommand(
1152 "ALTER TABLE `primshapes` ADD COLUMN `State` int NOT NULL default 0;",
1153 m_connection))
1154 {
1155 cmd.ExecuteNonQuery();
1156 }
1157 }
1158
1159 return s;
1160 }
1161
1162 private void fillShapeRow(DataRow row, SceneObjectPart prim)
1163 {
1164 PrimitiveBaseShape s = prim.Shape;
1165 row["UUID"] = Util.ToRawUuidString(prim.UUID);
1166 // shape is an enum
1167 row["Shape"] = 0;
1168 // vectors
1169 row["ScaleX"] = s.Scale.X;
1170 row["ScaleY"] = s.Scale.Y;
1171 row["ScaleZ"] = s.Scale.Z;
1172 // paths
1173 row["PCode"] = s.PCode;
1174 row["PathBegin"] = s.PathBegin;
1175 row["PathEnd"] = s.PathEnd;
1176 row["PathScaleX"] = s.PathScaleX;
1177 row["PathScaleY"] = s.PathScaleY;
1178 row["PathShearX"] = s.PathShearX;
1179 row["PathShearY"] = s.PathShearY;
1180 row["PathSkew"] = s.PathSkew;
1181 row["PathCurve"] = s.PathCurve;
1182 row["PathRadiusOffset"] = s.PathRadiusOffset;
1183 row["PathRevolutions"] = s.PathRevolutions;
1184 row["PathTaperX"] = s.PathTaperX;
1185 row["PathTaperY"] = s.PathTaperY;
1186 row["PathTwist"] = s.PathTwist;
1187 row["PathTwistBegin"] = s.PathTwistBegin;
1188 // profile
1189 row["ProfileBegin"] = s.ProfileBegin;
1190 row["ProfileEnd"] = s.ProfileEnd;
1191 row["ProfileCurve"] = s.ProfileCurve;
1192 row["ProfileHollow"] = s.ProfileHollow;
1193 row["Texture"] = s.TextureEntry;
1194 row["ExtraParams"] = s.ExtraParams;
1195 try
1196 {
1197 row["State"] = s.State;
1198 }
1199 catch (MySqlException)
1200 {
1201 // Database table was created before we got here and needs to be created! :P
1202 using (
1203 MySqlCommand cmd =
1204 new MySqlCommand(
1205 "ALTER TABLE `primshapes` ADD COLUMN `State` int NOT NULL default 0;",
1206 m_connection))
1207 {
1208 cmd.ExecuteNonQuery();
1209 }
1210 }
1211 }
1212
1213 private void addPrim(SceneObjectPart prim, LLUUID sceneGroupID, LLUUID regionUUID)
1214 {
1215 DataTable prims = m_dataSet.Tables["prims"];
1216 DataTable shapes = m_dataSet.Tables["primshapes"];
1217
1218 DataRow primRow = prims.Rows.Find(Util.ToRawUuidString(prim.UUID));
1219 if (primRow == null)
1220 {
1221 primRow = prims.NewRow();
1222 fillPrimRow(primRow, prim, sceneGroupID, regionUUID);
1223 prims.Rows.Add(primRow);
1224 }
1225 else
1226 {
1227 fillPrimRow(primRow, prim, sceneGroupID, regionUUID);
1228 }
1229
1230 DataRow shapeRow = shapes.Rows.Find(Util.ToRawUuidString(prim.UUID));
1231 if (shapeRow == null)
1232 {
1233 shapeRow = shapes.NewRow();
1234 fillShapeRow(shapeRow, prim);
1235 shapes.Rows.Add(shapeRow);
1236 }
1237 else
1238 {
1239 fillShapeRow(shapeRow, prim);
1240 }
1241 }
1242
1243 // see IRegionDatastore
1244 public void StorePrimInventory(LLUUID primID, ICollection<TaskInventoryItem> items)
1245 {
1246 if (!persistPrimInventories)
1247 return;
1248
1249 m_log.InfoFormat("[DATASTORE]: Persisting Prim Inventory with prim ID {0}", primID);
1250
1251 // For now, we're just going to crudely remove all the previous inventory items
1252 // no matter whether they have changed or not, and replace them with the current set.
1253 lock (m_dataSet)
1254 {
1255 RemoveItems(primID);
1256
1257 // repalce with current inventory details
1258 foreach (TaskInventoryItem newItem in items)
1259 {
1260// m_log.InfoFormat(
1261// "[DATASTORE]: " +
1262// "Adding item {0}, {1} to prim ID {2}",
1263// newItem.Name, newItem.ItemID, newItem.ParentPartID);
1264
1265 DataRow newItemRow = m_itemsTable.NewRow();
1266 fillItemRow(newItemRow, newItem);
1267 m_itemsTable.Rows.Add(newItemRow);
1268 }
1269 }
1270
1271 Commit();
1272 }
1273
1274 /***********************************************************************
1275 *
1276 * SQL Statement Creation Functions
1277 *
1278 * These functions create SQL statements for update, insert, and create.
1279 * They can probably be factored later to have a db independant
1280 * portion and a db specific portion
1281 *
1282 **********************************************************************/
1283
1284 private MySqlCommand createInsertCommand(string table, DataTable dt)
1285 {
1286 /**
1287 * This is subtle enough to deserve some commentary.
1288 * Instead of doing *lots* and *lots of hardcoded strings
1289 * for database definitions we'll use the fact that
1290 * realistically all insert statements look like "insert
1291 * into A(b, c) values(:b, :c) on the parameterized query
1292 * front. If we just have a list of b, c, etc... we can
1293 * generate these strings instead of typing them out.
1294 */
1295 string[] cols = new string[dt.Columns.Count];
1296 for (int i = 0; i < dt.Columns.Count; i++)
1297 {
1298 DataColumn col = dt.Columns[i];
1299 cols[i] = col.ColumnName;
1300 }
1301
1302 string sql = "insert into " + table + "(";
1303 sql += String.Join(", ", cols);
1304 // important, the first ':' needs to be here, the rest get added in the join
1305 sql += ") values (?";
1306 sql += String.Join(", ?", cols);
1307 sql += ")";
1308 MySqlCommand cmd = new MySqlCommand(sql);
1309
1310 // this provides the binding for all our parameters, so
1311 // much less code than it used to be
1312 foreach (DataColumn col in dt.Columns)
1313 {
1314 cmd.Parameters.Add(createMySqlParameter(col.ColumnName, col.DataType));
1315 }
1316 return cmd;
1317 }
1318
1319 private MySqlCommand createUpdateCommand(string table, string pk, DataTable dt)
1320 {
1321 string sql = "update " + table + " set ";
1322 string subsql = String.Empty;
1323 foreach (DataColumn col in dt.Columns)
1324 {
1325 if (subsql.Length > 0)
1326 {
1327 // a map function would rock so much here
1328 subsql += ", ";
1329 }
1330 subsql += col.ColumnName + "=?" + col.ColumnName;
1331 }
1332 sql += subsql;
1333 sql += " where " + pk;
1334 MySqlCommand cmd = new MySqlCommand(sql);
1335
1336 // this provides the binding for all our parameters, so
1337 // much less code than it used to be
1338
1339 foreach (DataColumn col in dt.Columns)
1340 {
1341 cmd.Parameters.Add(createMySqlParameter(col.ColumnName, col.DataType));
1342 }
1343 return cmd;
1344 }
1345
1346 private string defineTable(DataTable dt)
1347 {
1348 string sql = "create table " + dt.TableName + "(";
1349 string subsql = String.Empty;
1350 foreach (DataColumn col in dt.Columns)
1351 {
1352 if (subsql.Length > 0)
1353 {
1354 // a map function would rock so much here
1355 subsql += ",\n";
1356 }
1357 subsql += col.ColumnName + " " + MySqlType(col.DataType);
1358 if (dt.PrimaryKey.Length > 0 && col == dt.PrimaryKey[0])
1359 {
1360 subsql += " primary key";
1361 }
1362 }
1363 sql += subsql;
1364 sql += ")";
1365
1366 //m_log.InfoFormat("[DATASTORE]: defineTable() sql {0}", sql);
1367
1368 return sql;
1369 }
1370
1371 /***********************************************************************
1372 *
1373 * Database Binding functions
1374 *
1375 * These will be db specific due to typing, and minor differences
1376 * in databases.
1377 *
1378 **********************************************************************/
1379
1380 ///<summary>
1381 /// This is a convenience function that collapses 5 repetitive
1382 /// lines for defining MySqlParameters to 2 parameters:
1383 /// column name and database type.
1384 ///
1385 /// It assumes certain conventions like ?param as the param
1386 /// name to replace in parametrized queries, and that source
1387 /// version is always current version, both of which are fine
1388 /// for us.
1389 ///</summary>
1390 ///<returns>a built MySql parameter</returns>
1391 private MySqlParameter createMySqlParameter(string name, Type type)
1392 {
1393 MySqlParameter param = new MySqlParameter();
1394 param.ParameterName = "?" + name;
1395 param.DbType = dbtypeFromType(type);
1396 param.SourceColumn = name;
1397 param.SourceVersion = DataRowVersion.Current;
1398 return param;
1399 }
1400
1401// TODO: unused
1402// private MySqlParameter createParamWithValue(string name, Type type, Object o)
1403// {
1404// MySqlParameter param = createMySqlParameter(name, type);
1405// param.Value = o;
1406// return param;
1407// }
1408
1409 private void SetupPrimCommands(MySqlDataAdapter da, MySqlConnection conn)
1410 {
1411 MySqlCommand insertCommand = createInsertCommand("prims", m_primTable);
1412 insertCommand.Connection = conn;
1413 da.InsertCommand = insertCommand;
1414
1415 MySqlCommand updateCommand = createUpdateCommand("prims", "UUID=?UUID", m_primTable);
1416 updateCommand.Connection = conn;
1417 da.UpdateCommand = updateCommand;
1418
1419 MySqlCommand delete = new MySqlCommand("delete from prims where UUID=?UUID");
1420 delete.Parameters.Add(createMySqlParameter("UUID", typeof (String)));
1421 delete.Connection = conn;
1422 da.DeleteCommand = delete;
1423 }
1424
1425 private void SetupItemsCommands(MySqlDataAdapter da, MySqlConnection conn)
1426 {
1427 da.InsertCommand = createInsertCommand("primitems", m_itemsTable);
1428 da.InsertCommand.Connection = conn;
1429
1430 da.UpdateCommand = createUpdateCommand("primitems", "itemID = ?itemID", m_itemsTable);
1431 da.UpdateCommand.Connection = conn;
1432
1433 MySqlCommand delete = new MySqlCommand("delete from primitems where itemID = ?itemID");
1434 delete.Parameters.Add(createMySqlParameter("itemID", typeof (String)));
1435 delete.Connection = conn;
1436 da.DeleteCommand = delete;
1437 }
1438
1439 private void SetupTerrainCommands(MySqlDataAdapter da, MySqlConnection conn)
1440 {
1441 da.InsertCommand = createInsertCommand("terrain", m_dataSet.Tables["terrain"]);
1442 da.InsertCommand.Connection = conn;
1443 }
1444
1445 private void setupLandCommands(MySqlDataAdapter da, MySqlConnection conn)
1446 {
1447 da.InsertCommand = createInsertCommand("land", m_dataSet.Tables["land"]);
1448 da.InsertCommand.Connection = conn;
1449
1450 da.UpdateCommand = createUpdateCommand("land", "UUID=?UUID", m_dataSet.Tables["land"]);
1451 da.UpdateCommand.Connection = conn;
1452 }
1453
1454 private void setupLandAccessCommands(MySqlDataAdapter da, MySqlConnection conn)
1455 {
1456 da.InsertCommand = createInsertCommand("landaccesslist", m_dataSet.Tables["landaccesslist"]);
1457 da.InsertCommand.Connection = conn;
1458 }
1459
1460 private void SetupShapeCommands(MySqlDataAdapter da, MySqlConnection conn)
1461 {
1462 da.InsertCommand = createInsertCommand("primshapes", m_dataSet.Tables["primshapes"]);
1463 da.InsertCommand.Connection = conn;
1464
1465 da.UpdateCommand = createUpdateCommand("primshapes", "UUID=?UUID", m_dataSet.Tables["primshapes"]);
1466 da.UpdateCommand.Connection = conn;
1467
1468 MySqlCommand delete = new MySqlCommand("delete from primshapes where UUID = ?UUID");
1469 delete.Parameters.Add(createMySqlParameter("UUID", typeof (String)));
1470 delete.Connection = conn;
1471 da.DeleteCommand = delete;
1472 }
1473
1474 private void InitDB(MySqlConnection conn)
1475 {
1476 string createPrims = defineTable(createPrimTable());
1477 string createShapes = defineTable(createShapeTable());
1478 string createItems = defineTable(createItemsTable());
1479 string createTerrain = defineTable(createTerrainTable());
1480 string createLand = defineTable(createLandTable());
1481 string createLandAccessList = defineTable(createLandAccessListTable());
1482
1483 MySqlCommand pcmd = new MySqlCommand(createPrims, conn);
1484 MySqlCommand scmd = new MySqlCommand(createShapes, conn);
1485 MySqlCommand icmd = new MySqlCommand(createItems, conn);
1486 MySqlCommand tcmd = new MySqlCommand(createTerrain, conn);
1487 MySqlCommand lcmd = new MySqlCommand(createLand, conn);
1488 MySqlCommand lalcmd = new MySqlCommand(createLandAccessList, conn);
1489
1490 if (conn.State != ConnectionState.Open)
1491 {
1492 try
1493 {
1494 conn.Open();
1495 }
1496 catch (Exception ex)
1497 {
1498 m_log.Error("[MySql]: Error connecting to MySQL server: " + ex.Message);
1499 m_log.Error("[MySql]: Application is terminating!");
1500 System.Threading.Thread.CurrentThread.Abort();
1501 }
1502 }
1503
1504 try
1505 {
1506 pcmd.ExecuteNonQuery();
1507 }
1508 catch (MySqlException e)
1509 {
1510 m_log.WarnFormat("[MySql]: Primitives Table Already Exists: {0}", e);
1511 }
1512
1513 try
1514 {
1515 scmd.ExecuteNonQuery();
1516 }
1517 catch (MySqlException e)
1518 {
1519 m_log.WarnFormat("[MySql]: Shapes Table Already Exists: {0}", e);
1520 }
1521
1522 try
1523 {
1524 icmd.ExecuteNonQuery();
1525 }
1526 catch (MySqlException e)
1527 {
1528 m_log.WarnFormat("[MySql]: Items Table Already Exists: {0}", e);
1529 }
1530
1531 try
1532 {
1533 tcmd.ExecuteNonQuery();
1534 }
1535 catch (MySqlException e)
1536 {
1537 m_log.WarnFormat("[MySql]: Terrain Table Already Exists: {0}", e);
1538 }
1539
1540 try
1541 {
1542 lcmd.ExecuteNonQuery();
1543 }
1544 catch (MySqlException e)
1545 {
1546 m_log.WarnFormat("[MySql]: Land Table Already Exists: {0}", e);
1547 }
1548
1549 try
1550 {
1551 lalcmd.ExecuteNonQuery();
1552 }
1553 catch (MySqlException e)
1554 {
1555 m_log.WarnFormat("[MySql]: LandAccessList Table Already Exists: {0}", e);
1556 }
1557 conn.Close();
1558 }
1559
1560 private bool TestTables(MySqlConnection conn)
1561 {
1562 MySqlCommand primSelectCmd = new MySqlCommand(m_primSelect, conn);
1563 MySqlDataAdapter pDa = new MySqlDataAdapter(primSelectCmd);
1564 MySqlCommand shapeSelectCmd = new MySqlCommand(m_shapeSelect, conn);
1565 MySqlDataAdapter sDa = new MySqlDataAdapter(shapeSelectCmd);
1566 MySqlCommand itemsSelectCmd = new MySqlCommand(m_itemsSelect, conn);
1567 MySqlDataAdapter iDa = new MySqlDataAdapter(itemsSelectCmd);
1568 MySqlCommand terrainSelectCmd = new MySqlCommand(m_terrainSelect, conn);
1569 MySqlDataAdapter tDa = new MySqlDataAdapter(terrainSelectCmd);
1570 MySqlCommand landSelectCmd = new MySqlCommand(m_landSelect, conn);
1571 MySqlDataAdapter lDa = new MySqlDataAdapter(landSelectCmd);
1572 MySqlCommand landAccessListSelectCmd = new MySqlCommand(m_landAccessListSelect, conn);
1573 MySqlDataAdapter lalDa = new MySqlDataAdapter(landAccessListSelectCmd);
1574
1575 DataSet tmpDS = new DataSet();
1576 try
1577 {
1578 pDa.Fill(tmpDS, "prims");
1579 sDa.Fill(tmpDS, "primshapes");
1580
1581 if (persistPrimInventories)
1582 iDa.Fill(tmpDS, "primitems");
1583
1584 tDa.Fill(tmpDS, "terrain");
1585 lDa.Fill(tmpDS, "land");
1586 lalDa.Fill(tmpDS, "landaccesslist");
1587 }
1588 catch (MySqlException)
1589 {
1590 m_log.Info("[DATASTORE]: MySql Database doesn't exist... creating");
1591 InitDB(conn);
1592 }
1593
1594 pDa.Fill(tmpDS, "prims");
1595 sDa.Fill(tmpDS, "primshapes");
1596
1597 if (persistPrimInventories)
1598 iDa.Fill(tmpDS, "primitems");
1599
1600 tDa.Fill(tmpDS, "terrain");
1601 lDa.Fill(tmpDS, "land");
1602 lalDa.Fill(tmpDS, "landaccesslist");
1603
1604 foreach (DataColumn col in createPrimTable().Columns)
1605 {
1606 if (!tmpDS.Tables["prims"].Columns.Contains(col.ColumnName))
1607 {
1608 m_log.Info("[DATASTORE]: Missing required column:" + col.ColumnName);
1609 return false;
1610 }
1611 }
1612
1613 foreach (DataColumn col in createShapeTable().Columns)
1614 {
1615 if (!tmpDS.Tables["primshapes"].Columns.Contains(col.ColumnName))
1616 {
1617 m_log.Info("[DATASTORE]: Missing required column:" + col.ColumnName);
1618 return false;
1619 }
1620 }
1621
1622 // XXX primitems should probably go here eventually
1623
1624 foreach (DataColumn col in createTerrainTable().Columns)
1625 {
1626 if (!tmpDS.Tables["terrain"].Columns.Contains(col.ColumnName))
1627 {
1628 m_log.Info("[DATASTORE]: Missing require column:" + col.ColumnName);
1629 return false;
1630 }
1631 }
1632
1633 foreach (DataColumn col in createLandTable().Columns)
1634 {
1635 if (!tmpDS.Tables["land"].Columns.Contains(col.ColumnName))
1636 {
1637 m_log.Info("[DATASTORE]: Missing require column:" + col.ColumnName);
1638 return false;
1639 }
1640 }
1641
1642 foreach (DataColumn col in createLandAccessListTable().Columns)
1643 {
1644 if (!tmpDS.Tables["landaccesslist"].Columns.Contains(col.ColumnName))
1645 {
1646 m_log.Info("[DATASTORE]: Missing require column:" + col.ColumnName);
1647 return false;
1648 }
1649 }
1650
1651 return true;
1652 }
1653
1654 /***********************************************************************
1655 *
1656 * Type conversion functions
1657 *
1658 **********************************************************************/
1659
1660 private DbType dbtypeFromType(Type type)
1661 {
1662 if (type == typeof (String))
1663 {
1664 return DbType.String;
1665 }
1666 else if (type == typeof (Int32))
1667 {
1668 return DbType.Int32;
1669 }
1670 else if (type == typeof (Double))
1671 {
1672 return DbType.Double;
1673 }
1674 else if (type == typeof (Byte))
1675 {
1676 return DbType.Byte;
1677 }
1678 else if (type == typeof (Double))
1679 {
1680 return DbType.Double;
1681 }
1682 else if (type == typeof (Byte[]))
1683 {
1684 return DbType.Binary;
1685 }
1686 else
1687 {
1688 return DbType.String;
1689 }
1690 }
1691
1692 // this is something we'll need to implement for each db
1693 // slightly differently.
1694 private string MySqlType(Type type)
1695 {
1696 if (type == typeof (String))
1697 {
1698 return "varchar(255)";
1699 }
1700 else if (type == typeof (Int32))
1701 {
1702 return "integer";
1703 }
1704 else if (type == typeof (Int64))
1705 {
1706 return "bigint";
1707 }
1708 else if (type == typeof (Double))
1709 {
1710 return "float";
1711 }
1712 else if (type == typeof (Byte[]))
1713 {
1714 return "longblob";
1715 }
1716 else
1717 {
1718 return "string";
1719 }
1720 }
1721 }
1722}
diff --git a/OpenSim/Data/MySQL/MySQLGridData.cs b/OpenSim/Data/MySQL/MySQLGridData.cs
new file mode 100644
index 0000000..61ab067
--- /dev/null
+++ b/OpenSim/Data/MySQL/MySQLGridData.cs
@@ -0,0 +1,402 @@
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 System.Text.RegularExpressions;
34using libsecondlife;
35using OpenSim.Framework.Console;
36
37namespace OpenSim.Framework.Data.MySQL
38{
39 /// <summary>
40 /// A MySQL Interface for the Grid Server
41 /// </summary>
42 public class MySQLGridData : GridDataBase
43 {
44 private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
45
46 /// <summary>
47 /// MySQL Database Manager
48 /// </summary>
49 private MySQLManager database;
50
51 /// <summary>
52 /// Initialises the Grid Interface
53 /// </summary>
54 override public void Initialise()
55 {
56 IniFile GridDataMySqlFile = new IniFile("mysql_connection.ini");
57 string settingHostname = GridDataMySqlFile.ParseFileReadValue("hostname");
58 string settingDatabase = GridDataMySqlFile.ParseFileReadValue("database");
59 string settingUsername = GridDataMySqlFile.ParseFileReadValue("username");
60 string settingPassword = GridDataMySqlFile.ParseFileReadValue("password");
61 string settingPooling = GridDataMySqlFile.ParseFileReadValue("pooling");
62 string settingPort = GridDataMySqlFile.ParseFileReadValue("port");
63
64 database =
65 new MySQLManager(settingHostname, settingDatabase, settingUsername, settingPassword, settingPooling,
66 settingPort);
67
68 TestTables();
69 }
70
71 #region Test and initialization code
72
73 /// <summary>
74 /// Ensure that the user related tables exists and are at the latest version
75 /// </summary>
76 private void TestTables()
77 {
78 Dictionary<string, string> tableList = new Dictionary<string, string>();
79
80 tableList["regions"] = null;
81 database.GetTableVersion(tableList);
82
83 UpgradeRegionsTable(tableList["regions"]);
84 }
85
86 /// <summary>
87 /// Create or upgrade the table if necessary
88 /// </summary>
89 /// <param name="oldVersion">A null indicates that the table does not
90 /// currently exist</param>
91 private void UpgradeRegionsTable(string oldVersion)
92 {
93 // null as the version, indicates that the table didn't exist
94 if (oldVersion == null)
95 {
96 database.ExecuteResourceSql("CreateRegionsTable.sql");
97 return;
98 }
99 if (oldVersion.Contains("Rev. 1"))
100 {
101 database.ExecuteResourceSql("UpgradeRegionsTableToVersion2.sql");
102 return;
103 }
104 if (oldVersion.Contains("Rev. 2"))
105 {
106 database.ExecuteResourceSql("UpgradeRegionsTableToVersion3.sql");
107 return;
108 }
109 }
110
111 #endregion
112
113 /// <summary>
114 /// Shuts down the grid interface
115 /// </summary>
116 override public void Close()
117 {
118 database.Close();
119 }
120
121 /// <summary>
122 /// Returns the plugin name
123 /// </summary>
124 /// <returns>Plugin name</returns>
125 override public string getName()
126 {
127 return "MySql OpenGridData";
128 }
129
130 /// <summary>
131 /// Returns the plugin version
132 /// </summary>
133 /// <returns>Plugin version</returns>
134 override public string getVersion()
135 {
136 return "0.1";
137 }
138
139 /// <summary>
140 /// Returns all the specified region profiles within coordates -- coordinates are inclusive
141 /// </summary>
142 /// <param name="xmin">Minimum X coordinate</param>
143 /// <param name="ymin">Minimum Y coordinate</param>
144 /// <param name="xmax">Maximum X coordinate</param>
145 /// <param name="ymax">Maximum Y coordinate</param>
146 /// <returns></returns>
147 override public RegionProfileData[] GetProfilesInRange(uint xmin, uint ymin, uint xmax, uint ymax)
148 {
149 try
150 {
151 lock (database)
152 {
153 Dictionary<string, string> param = new Dictionary<string, string>();
154 param["?xmin"] = xmin.ToString();
155 param["?ymin"] = ymin.ToString();
156 param["?xmax"] = xmax.ToString();
157 param["?ymax"] = ymax.ToString();
158
159 IDbCommand result =
160 database.Query(
161 "SELECT * FROM regions WHERE locX >= ?xmin AND locX <= ?xmax AND locY >= ?ymin AND locY <= ?ymax",
162 param);
163 IDataReader reader = result.ExecuteReader();
164
165 RegionProfileData row;
166
167 List<RegionProfileData> rows = new List<RegionProfileData>();
168
169 while ((row = database.readSimRow(reader)) != null)
170 {
171 rows.Add(row);
172 }
173 reader.Close();
174 result.Dispose();
175
176 return rows.ToArray();
177 }
178 }
179 catch (Exception e)
180 {
181 database.Reconnect();
182 m_log.Error(e.ToString());
183 return null;
184 }
185 }
186
187 /// <summary>
188 /// Returns a sim profile from it's location
189 /// </summary>
190 /// <param name="handle">Region location handle</param>
191 /// <returns>Sim profile</returns>
192 override public RegionProfileData GetProfileByHandle(ulong handle)
193 {
194 try
195 {
196 lock (database)
197 {
198 Dictionary<string, string> param = new Dictionary<string, string>();
199 param["?handle"] = handle.ToString();
200
201 IDbCommand result = database.Query("SELECT * FROM regions WHERE regionHandle = ?handle", param);
202 IDataReader reader = result.ExecuteReader();
203
204 RegionProfileData row = database.readSimRow(reader);
205 reader.Close();
206 result.Dispose();
207
208 return row;
209 }
210 }
211 catch (Exception e)
212 {
213 database.Reconnect();
214 m_log.Error(e.ToString());
215 return null;
216 }
217 }
218
219 /// <summary>
220 /// Returns a sim profile from it's UUID
221 /// </summary>
222 /// <param name="uuid">The region UUID</param>
223 /// <returns>The sim profile</returns>
224 override public RegionProfileData GetProfileByLLUUID(LLUUID uuid)
225 {
226 try
227 {
228 lock (database)
229 {
230 Dictionary<string, string> param = new Dictionary<string, string>();
231 param["?uuid"] = uuid.ToString();
232
233 IDbCommand result = database.Query("SELECT * FROM regions WHERE uuid = ?uuid", param);
234 IDataReader reader = result.ExecuteReader();
235
236 RegionProfileData row = database.readSimRow(reader);
237 reader.Close();
238 result.Dispose();
239
240 return row;
241 }
242 }
243 catch (Exception e)
244 {
245 database.Reconnect();
246 m_log.Error(e.ToString());
247 return null;
248 }
249 }
250
251 /// <summary>
252 /// Returns a sim profile from it's Region name string
253 /// </summary>
254 /// <param name="uuid">The region name search query</param>
255 /// <returns>The sim profile</returns>
256 override public RegionProfileData GetProfileByString(string regionName)
257 {
258 if (regionName.Length > 2)
259 {
260 try
261 {
262 lock (database)
263 {
264 Dictionary<string, string> param = new Dictionary<string, string>();
265 // Add % because this is a like query.
266 param["?regionName"] = regionName + "%";
267 // Order by statement will return shorter matches first. Only returns one record or no record.
268 IDbCommand result = database.Query("SELECT * FROM regions WHERE regionName like ?regionName order by LENGTH(regionName) asc LIMIT 1", param);
269 IDataReader reader = result.ExecuteReader();
270
271 RegionProfileData row = database.readSimRow(reader);
272 reader.Close();
273 result.Dispose();
274
275 return row;
276 }
277 }
278 catch (Exception e)
279 {
280 database.Reconnect();
281 m_log.Error(e.ToString());
282 return null;
283 }
284 }
285 else
286 {
287 m_log.Error("[DATABASE]: Searched for a Region Name shorter then 3 characters");
288 return null;
289 }
290 }
291
292 /// <summary>
293 /// Adds a new profile to the database
294 /// </summary>
295 /// <param name="profile">The profile to add</param>
296 /// <returns>Successful?</returns>
297 override public DataResponse AddProfile(RegionProfileData profile)
298 {
299 lock (database)
300 {
301 if (database.insertRegion(profile))
302 {
303 return DataResponse.RESPONSE_OK;
304 }
305 else
306 {
307 return DataResponse.RESPONSE_ERROR;
308 }
309 }
310 }
311
312 /// <summary>
313 /// Deletes a profile from the database
314 /// </summary>
315 /// <param name="profile">The profile to delete</param>
316 /// <returns>Successful?</returns>
317 //public DataResponse DeleteProfile(RegionProfileData profile)
318 public DataResponse DeleteProfile(string uuid)
319 {
320 lock (database)
321 {
322 if (database.deleteRegion(uuid))
323 {
324 return DataResponse.RESPONSE_OK;
325 }
326 else
327 {
328 return DataResponse.RESPONSE_ERROR;
329 }
330 }
331 }
332
333 /// <summary>
334 /// DEPRECATED. Attempts to authenticate a region by comparing a shared secret.
335 /// </summary>
336 /// <param name="uuid">The UUID of the challenger</param>
337 /// <param name="handle">The attempted regionHandle of the challenger</param>
338 /// <param name="authkey">The secret</param>
339 /// <returns>Whether the secret and regionhandle match the database entry for UUID</returns>
340 override public bool AuthenticateSim(LLUUID uuid, ulong handle, string authkey)
341 {
342 bool throwHissyFit = false; // Should be true by 1.0
343
344 if (throwHissyFit)
345 throw new Exception("CRYPTOWEAK AUTHENTICATE: Refusing to authenticate due to replay potential.");
346
347 RegionProfileData data = GetProfileByLLUUID(uuid);
348
349 return (handle == data.regionHandle && authkey == data.regionSecret);
350 }
351
352 /// <summary>
353 /// NOT YET FUNCTIONAL. Provides a cryptographic authentication of a region
354 /// </summary>
355 /// <remarks>This requires a security audit.</remarks>
356 /// <param name="uuid"></param>
357 /// <param name="handle"></param>
358 /// <param name="authhash"></param>
359 /// <param name="challenge"></param>
360 /// <returns></returns>
361 public bool AuthenticateSim(LLUUID uuid, ulong handle, string authhash, string challenge)
362 {
363 SHA512Managed HashProvider = new SHA512Managed();
364 ASCIIEncoding TextProvider = new ASCIIEncoding();
365
366 byte[] stream = TextProvider.GetBytes(uuid.ToString() + ":" + handle.ToString() + ":" + challenge);
367 byte[] hash = HashProvider.ComputeHash(stream);
368
369 return false;
370 }
371
372 override public ReservationData GetReservationAtPoint(uint x, uint y)
373 {
374 try
375 {
376 lock (database)
377 {
378 Dictionary<string, string> param = new Dictionary<string, string>();
379 param["?x"] = x.ToString();
380 param["?y"] = y.ToString();
381 IDbCommand result =
382 database.Query(
383 "SELECT * FROM reservations WHERE resXMin <= ?x AND resXMax >= ?x AND resYMin <= ?y AND resYMax >= ?y",
384 param);
385 IDataReader reader = result.ExecuteReader();
386
387 ReservationData row = database.readReservationRow(reader);
388 reader.Close();
389 result.Dispose();
390
391 return row;
392 }
393 }
394 catch (Exception e)
395 {
396 database.Reconnect();
397 m_log.Error(e.ToString());
398 return null;
399 }
400 }
401 }
402}
diff --git a/OpenSim/Data/MySQL/MySQLInventoryData.cs b/OpenSim/Data/MySQL/MySQLInventoryData.cs
new file mode 100644
index 0000000..4165d8f
--- /dev/null
+++ b/OpenSim/Data/MySQL/MySQLInventoryData.cs
@@ -0,0 +1,648 @@
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 libsecondlife;
31using MySql.Data.MySqlClient;
32using OpenSim.Framework.Console;
33
34namespace OpenSim.Framework.Data.MySQL
35{
36 /// <summary>
37 /// A MySQL interface for the inventory server
38 /// </summary>
39 public class MySQLInventoryData : IInventoryData
40 {
41 private static readonly log4net.ILog m_log
42 = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
43
44 /// <summary>
45 /// The database manager
46 /// </summary>
47 private MySQLManager database;
48
49 /// <summary>
50 /// Loads and initialises this database plugin
51 /// </summary>
52 public void Initialise()
53 {
54 IniFile GridDataMySqlFile = new IniFile("mysql_connection.ini");
55 string settingHostname = GridDataMySqlFile.ParseFileReadValue("hostname");
56 string settingDatabase = GridDataMySqlFile.ParseFileReadValue("database");
57 string settingUsername = GridDataMySqlFile.ParseFileReadValue("username");
58 string settingPassword = GridDataMySqlFile.ParseFileReadValue("password");
59 string settingPooling = GridDataMySqlFile.ParseFileReadValue("pooling");
60 string settingPort = GridDataMySqlFile.ParseFileReadValue("port");
61
62 database =
63 new MySQLManager(settingHostname, settingDatabase, settingUsername, settingPassword, settingPooling,
64 settingPort);
65 TestTables(database.Connection);
66 }
67
68 #region Test and initialization code
69
70 private void UpgradeFoldersTable(string oldVersion)
71 {
72 // null as the version, indicates that the table didn't exist
73 if (oldVersion == null)
74 {
75 database.ExecuteResourceSql("CreateFoldersTable.sql");
76 return;
77 }
78
79 // if the table is already at the current version, then we can exit immediately
80// if (oldVersion == "Rev. 2")
81// return;
82
83// database.ExecuteResourceSql("UpgradeFoldersTableToVersion2.sql");
84 }
85
86 private void UpgradeItemsTable(string oldVersion)
87 {
88 // null as the version, indicates that the table didn't exist
89 if (oldVersion == null)
90 {
91 database.ExecuteResourceSql("CreateItemsTable.sql");
92 return;
93 }
94
95 // if the table is already at the current version, then we can exit immediately
96// if (oldVersion == "Rev. 2")
97// return;
98
99// database.ExecuteResourceSql("UpgradeItemsTableToVersion2.sql");
100 }
101
102 private void TestTables(MySqlConnection conn)
103 {
104 Dictionary<string, string> tableList = new Dictionary<string, string>();
105
106 tableList["inventoryfolders"] = null;
107 tableList["inventoryitems"] = null;
108
109 database.GetTableVersion(tableList);
110 m_log.Info("[MYSQL]: Inventory Folder Version: " + tableList["inventoryfolders"]);
111 m_log.Info("[MYSQL]: Inventory Items Version: " + tableList["inventoryitems"]);
112
113 UpgradeFoldersTable(tableList["inventoryfolders"]);
114 UpgradeItemsTable(tableList["inventoryitems"]);
115 }
116
117 #endregion
118
119 /// <summary>
120 /// The name of this DB provider
121 /// </summary>
122 /// <returns>Name of DB provider</returns>
123 public string getName()
124 {
125 return "MySQL Inventory Data Interface";
126 }
127
128 /// <summary>
129 /// Closes this DB provider
130 /// </summary>
131 public void Close()
132 {
133 // Do nothing.
134 }
135
136 /// <summary>
137 /// Returns the version of this DB provider
138 /// </summary>
139 /// <returns>A string containing the DB provider</returns>
140 public string getVersion()
141 {
142 return database.getVersion();
143 }
144
145 /// <summary>
146 /// Returns a list of items in a specified folder
147 /// </summary>
148 /// <param name="folderID">The folder to search</param>
149 /// <returns>A list containing inventory items</returns>
150 public List<InventoryItemBase> getInventoryInFolder(LLUUID folderID)
151 {
152 try
153 {
154 lock (database)
155 {
156 List<InventoryItemBase> items = new List<InventoryItemBase>();
157
158 MySqlCommand result =
159 new MySqlCommand("SELECT * FROM inventoryitems WHERE parentFolderID = ?uuid",
160 database.Connection);
161 result.Parameters.AddWithValue("?uuid", folderID.ToString());
162 MySqlDataReader reader = result.ExecuteReader();
163
164 while (reader.Read())
165 items.Add(readInventoryItem(reader));
166
167 reader.Close();
168 result.Dispose();
169
170 return items;
171 }
172 }
173 catch (Exception e)
174 {
175 database.Reconnect();
176 m_log.Error(e.ToString());
177 return null;
178 }
179 }
180
181 /// <summary>
182 /// Returns a list of the root folders within a users inventory
183 /// </summary>
184 /// <param name="user">The user whos inventory is to be searched</param>
185 /// <returns>A list of folder objects</returns>
186 public List<InventoryFolderBase> getUserRootFolders(LLUUID user)
187 {
188 try
189 {
190 lock (database)
191 {
192 MySqlCommand result =
193 new MySqlCommand(
194 "SELECT * FROM inventoryfolders WHERE parentFolderID = ?zero AND agentID = ?uuid",
195 database.Connection);
196 result.Parameters.AddWithValue("?uuid", user.ToString());
197 result.Parameters.AddWithValue("?zero", LLUUID.Zero.ToString());
198 MySqlDataReader reader = result.ExecuteReader();
199
200 List<InventoryFolderBase> items = new List<InventoryFolderBase>();
201 while (reader.Read())
202 items.Add(readInventoryFolder(reader));
203
204
205 reader.Close();
206 result.Dispose();
207
208 return items;
209 }
210 }
211 catch (Exception e)
212 {
213 database.Reconnect();
214 m_log.Error(e.ToString());
215 return null;
216 }
217 }
218
219 // see InventoryItemBase.getUserRootFolder
220 public InventoryFolderBase getUserRootFolder(LLUUID user)
221 {
222 try
223 {
224 lock (database)
225 {
226 MySqlCommand result =
227 new MySqlCommand(
228 "SELECT * FROM inventoryfolders WHERE parentFolderID = ?zero AND agentID = ?uuid",
229 database.Connection);
230 result.Parameters.AddWithValue("?uuid", user.ToString());
231 result.Parameters.AddWithValue("?zero", LLUUID.Zero.ToString());
232
233 MySqlDataReader reader = result.ExecuteReader();
234
235 List<InventoryFolderBase> items = new List<InventoryFolderBase>();
236 while (reader.Read())
237 items.Add(readInventoryFolder(reader));
238
239 InventoryFolderBase rootFolder = null;
240
241 // There should only ever be one root folder for a user. However, if there's more
242 // than one we'll simply use the first one rather than failing. It would be even
243 // nicer to print some message to this effect, but this feels like it's too low a
244 // to put such a message out, and it's too minor right now to spare the time to
245 // suitably refactor.
246 if (items.Count > 0)
247 {
248 rootFolder = items[0];
249 }
250
251 reader.Close();
252 result.Dispose();
253
254 return rootFolder;
255 }
256 }
257 catch (Exception e)
258 {
259 database.Reconnect();
260 m_log.Error(e.ToString());
261 return null;
262 }
263 }
264
265 /// <summary>
266 /// Return a list of folders in a users inventory contained within the specified folder.
267 /// This method is only used in tests - in normal operation the user always have one,
268 /// and only one, root folder.
269 /// </summary>
270 /// <param name="parentID">The folder to search</param>
271 /// <returns>A list of inventory folders</returns>
272 public List<InventoryFolderBase> getInventoryFolders(LLUUID parentID)
273 {
274 try
275 {
276 lock (database)
277 {
278 MySqlCommand result =
279 new MySqlCommand("SELECT * FROM inventoryfolders WHERE parentFolderID = ?uuid",
280 database.Connection);
281 result.Parameters.AddWithValue("?uuid", parentID.ToString());
282 MySqlDataReader reader = result.ExecuteReader();
283
284 List<InventoryFolderBase> items = new List<InventoryFolderBase>();
285
286 while (reader.Read())
287 items.Add(readInventoryFolder(reader));
288
289 reader.Close();
290 result.Dispose();
291
292 return items;
293 }
294 }
295 catch (Exception e)
296 {
297 database.Reconnect();
298 m_log.Error(e.ToString());
299 return null;
300 }
301 }
302
303 /// <summary>
304 /// Reads a one item from an SQL result
305 /// </summary>
306 /// <param name="reader">The SQL Result</param>
307 /// <returns>the item read</returns>
308 private InventoryItemBase readInventoryItem(MySqlDataReader reader)
309 {
310 try
311 {
312 InventoryItemBase item = new InventoryItemBase();
313
314 item.inventoryID = new LLUUID((string) reader["inventoryID"]);
315 item.assetID = new LLUUID((string) reader["assetID"]);
316 item.assetType = (int) reader["assetType"];
317 item.parentFolderID = new LLUUID((string) reader["parentFolderID"]);
318 item.avatarID = new LLUUID((string) reader["avatarID"]);
319 item.inventoryName = (string) reader["inventoryName"];
320 item.inventoryDescription = (string) reader["inventoryDescription"];
321 item.inventoryNextPermissions = (uint) reader["inventoryNextPermissions"];
322 item.inventoryCurrentPermissions = (uint) reader["inventoryCurrentPermissions"];
323 item.invType = (int) reader["invType"];
324 item.creatorsID = new LLUUID((string) reader["creatorID"]);
325 item.inventoryBasePermissions = (uint) reader["inventoryBasePermissions"];
326 item.inventoryEveryOnePermissions = (uint) reader["inventoryEveryOnePermissions"];
327 return item;
328 }
329 catch (MySqlException e)
330 {
331 m_log.Error(e.ToString());
332 }
333
334 return null;
335 }
336
337 /// <summary>
338 /// Returns a specified inventory item
339 /// </summary>
340 /// <param name="item">The item to return</param>
341 /// <returns>An inventory item</returns>
342 public InventoryItemBase getInventoryItem(LLUUID itemID)
343 {
344 try
345 {
346 lock (database)
347 {
348 Dictionary<string, string> param = new Dictionary<string, string>();
349
350 MySqlCommand result =
351 new MySqlCommand("SELECT * FROM inventoryitems WHERE inventoryID = ?uuid", database.Connection);
352 result.Parameters.AddWithValue("?uuid", itemID.ToString());
353 MySqlDataReader reader = result.ExecuteReader();
354
355 InventoryItemBase item = null;
356 if (reader.Read())
357 item = readInventoryItem(reader);
358
359 reader.Close();
360 result.Dispose();
361
362 return item;
363 }
364 }
365 catch (Exception e)
366 {
367 database.Reconnect();
368 m_log.Error(e.ToString());
369 }
370 return null;
371 }
372
373 /// <summary>
374 /// Reads a list of inventory folders returned by a query.
375 /// </summary>
376 /// <param name="reader">A MySQL Data Reader</param>
377 /// <returns>A List containing inventory folders</returns>
378 protected InventoryFolderBase readInventoryFolder(MySqlDataReader reader)
379 {
380 try
381 {
382 InventoryFolderBase folder = new InventoryFolderBase();
383 folder.agentID = new LLUUID((string) reader["agentID"]);
384 folder.parentID = new LLUUID((string) reader["parentFolderID"]);
385 folder.folderID = new LLUUID((string) reader["folderID"]);
386 folder.name = (string) reader["folderName"];
387 folder.type = (short) reader["type"];
388 folder.version = (ushort) ((int) reader["version"]);
389 return folder;
390 }
391 catch (Exception e)
392 {
393 m_log.Error(e.ToString());
394 }
395
396 return null;
397 }
398
399
400 /// <summary>
401 /// Returns a specified inventory folder
402 /// </summary>
403 /// <param name="folder">The folder to return</param>
404 /// <returns>A folder class</returns>
405 public InventoryFolderBase getInventoryFolder(LLUUID folderID)
406 {
407 try
408 {
409 lock (database)
410 {
411 MySqlCommand result =
412 new MySqlCommand("SELECT * FROM inventoryfolders WHERE folderID = ?uuid", database.Connection);
413 result.Parameters.AddWithValue("?uuid", folderID.ToString());
414 MySqlDataReader reader = result.ExecuteReader();
415
416 reader.Read();
417 InventoryFolderBase folder = readInventoryFolder(reader);
418 reader.Close();
419 result.Dispose();
420
421 return folder;
422 }
423 }
424 catch (Exception e)
425 {
426 database.Reconnect();
427 m_log.Error(e.ToString());
428 return null;
429 }
430 }
431
432 /// <summary>
433 /// Adds a specified item to the database
434 /// </summary>
435 /// <param name="item">The inventory item</param>
436 public void addInventoryItem(InventoryItemBase item)
437 {
438 string sql =
439 "REPLACE INTO inventoryitems (inventoryID, assetID, assetType, parentFolderID, avatarID, inventoryName, inventoryDescription, inventoryNextPermissions, inventoryCurrentPermissions, invType, creatorID, inventoryBasePermissions, inventoryEveryOnePermissions) VALUES ";
440 sql +=
441 "(?inventoryID, ?assetID, ?assetType, ?parentFolderID, ?avatarID, ?inventoryName, ?inventoryDescription, ?inventoryNextPermissions, ?inventoryCurrentPermissions, ?invType, ?creatorID, ?inventoryBasePermissions, ?inventoryEveryOnePermissions)";
442
443 try
444 {
445 MySqlCommand result = new MySqlCommand(sql, database.Connection);
446 result.Parameters.AddWithValue("?inventoryID", item.inventoryID.ToString());
447 result.Parameters.AddWithValue("?assetID", item.assetID.ToString());
448 result.Parameters.AddWithValue("?assetType", item.assetType.ToString());
449 result.Parameters.AddWithValue("?parentFolderID", item.parentFolderID.ToString());
450 result.Parameters.AddWithValue("?avatarID", item.avatarID.ToString());
451 result.Parameters.AddWithValue("?inventoryName", item.inventoryName);
452 result.Parameters.AddWithValue("?inventoryDescription", item.inventoryDescription);
453 result.Parameters.AddWithValue("?inventoryNextPermissions", item.inventoryNextPermissions.ToString());
454 result.Parameters.AddWithValue("?inventoryCurrentPermissions",
455 item.inventoryCurrentPermissions.ToString());
456 result.Parameters.AddWithValue("?invType", item.invType);
457 result.Parameters.AddWithValue("?creatorID", item.creatorsID.ToString());
458 result.Parameters.AddWithValue("?inventoryBasePermissions", item.inventoryBasePermissions);
459 result.Parameters.AddWithValue("?inventoryEveryOnePermissions", item.inventoryEveryOnePermissions);
460 result.ExecuteNonQuery();
461 result.Dispose();
462 }
463 catch (MySqlException e)
464 {
465 m_log.Error(e.ToString());
466 }
467 }
468
469 /// <summary>
470 /// Updates the specified inventory item
471 /// </summary>
472 /// <param name="item">Inventory item to update</param>
473 public void updateInventoryItem(InventoryItemBase item)
474 {
475 addInventoryItem(item);
476 }
477
478 /// <summary>
479 ///
480 /// </summary>
481 /// <param name="item"></param>
482 public void deleteInventoryItem(LLUUID itemID)
483 {
484 try
485 {
486 MySqlCommand cmd =
487 new MySqlCommand("DELETE FROM inventoryitems WHERE inventoryID=?uuid", database.Connection);
488 cmd.Parameters.AddWithValue("?uuid", itemID.ToString());
489 cmd.ExecuteNonQuery();
490 }
491 catch (MySqlException e)
492 {
493 database.Reconnect();
494 m_log.Error(e.ToString());
495 }
496 }
497
498 /// <summary>
499 /// Creates a new inventory folder
500 /// </summary>
501 /// <param name="folder">Folder to create</param>
502 public void addInventoryFolder(InventoryFolderBase folder)
503 {
504 string sql =
505 "REPLACE INTO inventoryfolders (folderID, agentID, parentFolderID, folderName, type, version) VALUES ";
506 sql += "(?folderID, ?agentID, ?parentFolderID, ?folderName, ?type, ?version)";
507
508 MySqlCommand cmd = new MySqlCommand(sql, database.Connection);
509 cmd.Parameters.AddWithValue("?folderID", folder.folderID.ToString());
510 cmd.Parameters.AddWithValue("?agentID", folder.agentID.ToString());
511 cmd.Parameters.AddWithValue("?parentFolderID", folder.parentID.ToString());
512 cmd.Parameters.AddWithValue("?folderName", folder.name);
513 cmd.Parameters.AddWithValue("?type", (short) folder.type);
514 cmd.Parameters.AddWithValue("?version", folder.version);
515
516 try
517 {
518 lock (database)
519 {
520 cmd.ExecuteNonQuery();
521 }
522 }
523 catch (Exception e)
524 {
525 m_log.Error(e.ToString());
526 }
527 }
528
529 /// <summary>
530 /// Updates an inventory folder
531 /// </summary>
532 /// <param name="folder">Folder to update</param>
533 public void updateInventoryFolder(InventoryFolderBase folder)
534 {
535 addInventoryFolder(folder);
536 }
537
538 /// Creates a new inventory folder
539 /// </summary>
540 /// <param name="folder">Folder to create</param>
541 public void moveInventoryFolder(InventoryFolderBase folder)
542 {
543 string sql =
544 "UPDATE inventoryfolders SET parentFolderID=?parentFolderID WHERE folderID=?folderID";
545
546 MySqlCommand cmd = new MySqlCommand(sql, database.Connection);
547 cmd.Parameters.AddWithValue("?folderID", folder.folderID.ToString());
548 cmd.Parameters.AddWithValue("?parentFolderID", folder.parentID.ToString());
549
550 try
551 {
552 lock (database)
553 {
554 cmd.ExecuteNonQuery();
555 }
556 }
557 catch (Exception e)
558 {
559 m_log.Error(e.ToString());
560 }
561 }
562
563 /// <summary>
564 /// Append a list of all the child folders of a parent folder
565 /// </summary>
566 /// <param name="folders">list where folders will be appended</param>
567 /// <param name="parentID">ID of parent</param>
568 protected void getInventoryFolders(ref List<InventoryFolderBase> folders, LLUUID parentID)
569 {
570 List<InventoryFolderBase> subfolderList = getInventoryFolders(parentID);
571
572 foreach (InventoryFolderBase f in subfolderList)
573 folders.Add(f);
574 }
575
576 // See IInventoryData
577 public List<InventoryFolderBase> getFolderHierarchy(LLUUID parentID)
578 {
579 List<InventoryFolderBase> folders = new List<InventoryFolderBase>();
580 getInventoryFolders(ref folders, parentID);
581
582 for (int i = 0; i < folders.Count; i++)
583 getInventoryFolders(ref folders, folders[i].folderID);
584
585 return folders;
586 }
587
588 protected void deleteOneFolder(LLUUID folderID)
589 {
590 try
591 {
592 MySqlCommand cmd =
593 new MySqlCommand("DELETE FROM inventoryfolders WHERE folderID=?uuid", database.Connection);
594 cmd.Parameters.AddWithValue("?uuid", folderID.ToString());
595
596 lock (database)
597 {
598 cmd.ExecuteNonQuery();
599 }
600 }
601 catch (MySqlException e)
602 {
603 database.Reconnect();
604 m_log.Error(e.ToString());
605 }
606 }
607
608 protected void deleteItemsInFolder(LLUUID folderID)
609 {
610 try
611 {
612 MySqlCommand cmd =
613 new MySqlCommand("DELETE FROM inventoryitems WHERE parentFolderID=?uuid", database.Connection);
614 cmd.Parameters.AddWithValue("?uuid", folderID.ToString());
615
616 lock (database)
617 {
618 cmd.ExecuteNonQuery();
619 }
620 }
621 catch (MySqlException e)
622 {
623 database.Reconnect();
624 m_log.Error(e.ToString());
625 }
626 }
627
628 /// <summary>
629 /// Delete an inventory folder
630 /// </summary>
631 /// <param name="folderId">Id of folder to delete</param>
632 public void deleteInventoryFolder(LLUUID folderID)
633 {
634 List<InventoryFolderBase> subFolders = getFolderHierarchy(folderID);
635
636 //Delete all sub-folders
637 foreach (InventoryFolderBase f in subFolders)
638 {
639 deleteOneFolder(f.folderID);
640 deleteItemsInFolder(f.folderID);
641 }
642
643 //Delete the actual row
644 deleteOneFolder(folderID);
645 deleteItemsInFolder(folderID);
646 }
647 }
648}
diff --git a/OpenSim/Data/MySQL/MySQLLogData.cs b/OpenSim/Data/MySQL/MySQLLogData.cs
new file mode 100644
index 0000000..480446f
--- /dev/null
+++ b/OpenSim/Data/MySQL/MySQLLogData.cs
@@ -0,0 +1,106 @@
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
28namespace OpenSim.Framework.Data.MySQL
29{
30 /// <summary>
31 /// An interface to the log database for MySQL
32 /// </summary>
33 internal class MySQLLogData : ILogData
34 {
35 /// <summary>
36 /// The database manager
37 /// </summary>
38 public MySQLManager database;
39
40 /// <summary>
41 /// Artificial constructor called when the plugin is loaded
42 /// </summary>
43 public void Initialise()
44 {
45 IniFile GridDataMySqlFile = new IniFile("mysql_connection.ini");
46 string settingHostname = GridDataMySqlFile.ParseFileReadValue("hostname");
47 string settingDatabase = GridDataMySqlFile.ParseFileReadValue("database");
48 string settingUsername = GridDataMySqlFile.ParseFileReadValue("username");
49 string settingPassword = GridDataMySqlFile.ParseFileReadValue("password");
50 string settingPooling = GridDataMySqlFile.ParseFileReadValue("pooling");
51 string settingPort = GridDataMySqlFile.ParseFileReadValue("port");
52
53 database =
54 new MySQLManager(settingHostname, settingDatabase, settingUsername, settingPassword, settingPooling,
55 settingPort);
56 }
57
58 /// <summary>
59 /// Saves a log item to the database
60 /// </summary>
61 /// <param name="serverDaemon">The daemon triggering the event</param>
62 /// <param name="target">The target of the action (region / agent UUID, etc)</param>
63 /// <param name="methodCall">The method call where the problem occured</param>
64 /// <param name="arguments">The arguments passed to the method</param>
65 /// <param name="priority">How critical is this?</param>
66 /// <param name="logMessage">The message to log</param>
67 public void saveLog(string serverDaemon, string target, string methodCall, string arguments, int priority,
68 string logMessage)
69 {
70 try
71 {
72 database.insertLogRow(serverDaemon, target, methodCall, arguments, priority, logMessage);
73 }
74 catch
75 {
76 database.Reconnect();
77 }
78 }
79
80 /// <summary>
81 /// Returns the name of this DB provider
82 /// </summary>
83 /// <returns>A string containing the DB provider name</returns>
84 public string getName()
85 {
86 return "MySQL Logdata Interface";
87 }
88
89 /// <summary>
90 /// Closes the database provider
91 /// </summary>
92 public void Close()
93 {
94 // Do nothing.
95 }
96
97 /// <summary>
98 /// Returns the version of this DB provider
99 /// </summary>
100 /// <returns>A string containing the provider version</returns>
101 public string getVersion()
102 {
103 return "0.1";
104 }
105 }
106}
diff --git a/OpenSim/Data/MySQL/MySQLManager.cs b/OpenSim/Data/MySQL/MySQLManager.cs
new file mode 100644
index 0000000..579667b
--- /dev/null
+++ b/OpenSim/Data/MySQL/MySQLManager.cs
@@ -0,0 +1,909 @@
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 MySql.Data.MySqlClient;
36using OpenSim.Framework.Console;
37
38namespace OpenSim.Framework.Data.MySQL
39{
40 /// <summary>
41 /// A MySQL Database manager
42 /// </summary>
43 internal class MySQLManager
44 {
45 private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
46
47 /// <summary>
48 /// The database connection object
49 /// </summary>
50 private MySqlConnection dbcon;
51
52 /// <summary>
53 /// Connection string for ADO.net
54 /// </summary>
55 private string connectionString;
56
57 /// <summary>
58 /// Initialises and creates a new MySQL connection and maintains it.
59 /// </summary>
60 /// <param name="hostname">The MySQL server being connected to</param>
61 /// <param name="database">The name of the MySQL database being used</param>
62 /// <param name="username">The username logging into the database</param>
63 /// <param name="password">The password for the user logging in</param>
64 /// <param name="cpooling">Whether to use connection pooling or not, can be one of the following: 'yes', 'true', 'no' or 'false', if unsure use 'false'.</param>
65 public MySQLManager(string hostname, string database, string username, string password, string cpooling,
66 string port)
67 {
68 try
69 {
70 connectionString = "Server=" + hostname + ";Port=" + port + ";Database=" + database + ";User ID=" +
71 username + ";Password=" + password + ";Pooling=" + cpooling + ";";
72 dbcon = new MySqlConnection(connectionString);
73
74 try
75 {
76 dbcon.Open();
77 }
78 catch(Exception e)
79 {
80 throw new Exception( "Connection error while using connection string ["+connectionString+"]", e );
81 }
82
83 m_log.Info("[MYSQL]: Connection established");
84 }
85 catch (Exception e)
86 {
87 throw new Exception("Error initialising MySql Database: " + e.ToString());
88 }
89 }
90
91 /// <summary>
92 /// Get the connection being used
93 /// </summary>
94 public MySqlConnection Connection
95 {
96 get { return dbcon; }
97 }
98
99 /// <summary>
100 /// Shuts down the database connection
101 /// </summary>
102 public void Close()
103 {
104 dbcon.Close();
105 dbcon = null;
106 }
107
108 /// <summary>
109 /// Reconnects to the database
110 /// </summary>
111 public void Reconnect()
112 {
113 lock (dbcon)
114 {
115 try
116 {
117 // Close the DB connection
118 dbcon.Close();
119 // Try reopen it
120 dbcon = new MySqlConnection(connectionString);
121 dbcon.Open();
122 }
123 catch (Exception e)
124 {
125 m_log.Error("Unable to reconnect to database " + e.ToString());
126 }
127 }
128 }
129
130 /// <summary>
131 /// Returns the version of this DB provider
132 /// </summary>
133 /// <returns>A string containing the DB provider</returns>
134 public string getVersion()
135 {
136 Module module = GetType().Module;
137 string dllName = module.Assembly.ManifestModule.Name;
138 Version dllVersion = module.Assembly.GetName().Version;
139
140 return
141 string.Format("{0}.{1}.{2}.{3}", dllVersion.Major, dllVersion.Minor, dllVersion.Build,
142 dllVersion.Revision);
143 }
144
145 /// <summary>
146 /// Extract a named string resource from the embedded resources
147 /// </summary>
148 /// <param name="name">name of embedded resource</param>
149 /// <returns>string contained within the embedded resource</returns>
150 private string getResourceString(string name)
151 {
152 Assembly assem = GetType().Assembly;
153 string[] names = assem.GetManifestResourceNames();
154
155 foreach (string s in names)
156 {
157 if (s.EndsWith(name))
158 {
159 using (Stream resource = assem.GetManifestResourceStream(s))
160 {
161 using (StreamReader resourceReader = new StreamReader(resource))
162 {
163 string resourceString = resourceReader.ReadToEnd();
164 return resourceString;
165 }
166 }
167 }
168 }
169 throw new Exception(string.Format("Resource '{0}' was not found", name));
170 }
171
172 /// <summary>
173 /// Execute a SQL statement stored in a resource, as a string
174 /// </summary>
175 /// <param name="name"></param>
176 public void ExecuteResourceSql(string name)
177 {
178 MySqlCommand cmd = new MySqlCommand(getResourceString(name), dbcon);
179 cmd.ExecuteNonQuery();
180 }
181
182 /// <summary>
183 /// Given a list of tables, return the version of the tables, as seen in the database
184 /// </summary>
185 /// <param name="tableList"></param>
186 public void GetTableVersion(Dictionary<string, string> tableList)
187 {
188 lock (dbcon)
189 {
190 MySqlCommand tablesCmd =
191 new MySqlCommand(
192 "SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=?dbname",
193 dbcon);
194 tablesCmd.Parameters.AddWithValue("?dbname", dbcon.Database);
195 using (MySqlDataReader tables = tablesCmd.ExecuteReader())
196 {
197 while (tables.Read())
198 {
199 try
200 {
201 string tableName = (string) tables["TABLE_NAME"];
202 string comment = (string) tables["TABLE_COMMENT"];
203 if (tableList.ContainsKey(tableName))
204 {
205 tableList[tableName] = comment;
206 }
207 }
208 catch (Exception e)
209 {
210 m_log.Error(e.ToString());
211 }
212 }
213 tables.Close();
214 }
215 }
216 }
217
218 // TODO: at some time this code should be cleaned up
219
220 /// <summary>
221 /// Runs a query with protection against SQL Injection by using parameterised input.
222 /// </summary>
223 /// <param name="sql">The SQL string - replace any variables such as WHERE x = "y" with WHERE x = @y</param>
224 /// <param name="parameters">The parameters - index so that @y is indexed as 'y'</param>
225 /// <returns>A MySQL DB Command</returns>
226 public IDbCommand Query(string sql, Dictionary<string, string> parameters)
227 {
228 try
229 {
230 MySqlCommand dbcommand = (MySqlCommand) dbcon.CreateCommand();
231 dbcommand.CommandText = sql;
232 foreach (KeyValuePair<string, string> param in parameters)
233 {
234 dbcommand.Parameters.AddWithValue(param.Key, param.Value);
235 }
236
237 return (IDbCommand) dbcommand;
238 }
239 catch
240 {
241 lock (dbcon)
242 {
243 // Close the DB connection
244 try
245 {
246 dbcon.Close();
247 }
248 catch
249 {
250 }
251
252 // Try to reopen it
253 try
254 {
255 dbcon = new MySqlConnection(connectionString);
256 dbcon.Open();
257 }
258 catch (Exception e)
259 {
260 m_log.Error("Unable to reconnect to database " + e.ToString());
261 }
262
263 // Run the query again
264 try
265 {
266 MySqlCommand dbcommand = (MySqlCommand) dbcon.CreateCommand();
267 dbcommand.CommandText = sql;
268 foreach (KeyValuePair<string, string> param in parameters)
269 {
270 dbcommand.Parameters.AddWithValue(param.Key, param.Value);
271 }
272
273 return (IDbCommand) dbcommand;
274 }
275 catch (Exception e)
276 {
277 // Return null if it fails.
278 m_log.Error("Failed during Query generation: " + e.ToString());
279 return null;
280 }
281 }
282 }
283 }
284
285 /// <summary>
286 /// Reads a region row from a database reader
287 /// </summary>
288 /// <param name="reader">An active database reader</param>
289 /// <returns>A region profile</returns>
290 public RegionProfileData readSimRow(IDataReader reader)
291 {
292 RegionProfileData retval = new RegionProfileData();
293
294 if (reader.Read())
295 {
296 // Region Main gotta-have-or-we-return-null parts
297 if (!UInt64.TryParse(reader["regionHandle"].ToString(), out retval.regionHandle))
298 return null;
299 if (!LLUUID.TryParse((string)reader["uuid"], out retval.UUID))
300 return null;
301
302 // non-critical parts
303 retval.regionName = (string)reader["regionName"];
304 retval.originUUID = new LLUUID((string) reader["originUUID"]);
305
306 // Secrets
307 retval.regionRecvKey = (string) reader["regionRecvKey"];
308 retval.regionSecret = (string) reader["regionSecret"];
309 retval.regionSendKey = (string) reader["regionSendKey"];
310
311 // Region Server
312 retval.regionDataURI = (string) reader["regionDataURI"];
313 retval.regionOnline = false; // Needs to be pinged before this can be set.
314 retval.serverIP = (string) reader["serverIP"];
315 retval.serverPort = (uint) reader["serverPort"];
316 retval.serverURI = (string) reader["serverURI"];
317 retval.httpPort = Convert.ToUInt32(reader["serverHttpPort"].ToString());
318 retval.remotingPort = Convert.ToUInt32(reader["serverRemotingPort"].ToString());
319
320 // Location
321 retval.regionLocX = Convert.ToUInt32(reader["locX"].ToString());
322 retval.regionLocY = Convert.ToUInt32(reader["locY"].ToString());
323 retval.regionLocZ = Convert.ToUInt32(reader["locZ"].ToString());
324
325 // Neighbours - 0 = No Override
326 retval.regionEastOverrideHandle = Convert.ToUInt64(reader["eastOverrideHandle"].ToString());
327 retval.regionWestOverrideHandle = Convert.ToUInt64(reader["westOverrideHandle"].ToString());
328 retval.regionSouthOverrideHandle = Convert.ToUInt64(reader["southOverrideHandle"].ToString());
329 retval.regionNorthOverrideHandle = Convert.ToUInt64(reader["northOverrideHandle"].ToString());
330
331 // Assets
332 retval.regionAssetURI = (string) reader["regionAssetURI"];
333 retval.regionAssetRecvKey = (string) reader["regionAssetRecvKey"];
334 retval.regionAssetSendKey = (string) reader["regionAssetSendKey"];
335
336 // Userserver
337 retval.regionUserURI = (string) reader["regionUserURI"];
338 retval.regionUserRecvKey = (string) reader["regionUserRecvKey"];
339 retval.regionUserSendKey = (string) reader["regionUserSendKey"];
340
341 // World Map Addition
342 LLUUID.TryParse((string)reader["regionMapTexture"], out retval.regionMapTextureID);
343 LLUUID.TryParse((string)reader["owner_uuid"], out retval.owner_uuid);
344 }
345 else
346 {
347 return null;
348 }
349 return retval;
350 }
351
352 /// <summary>
353 /// Reads a reservation row from a database reader
354 /// </summary>
355 /// <param name="reader">An active database reader</param>
356 /// <returns>A reservation data object</returns>
357 public ReservationData readReservationRow(IDataReader reader)
358 {
359 ReservationData retval = new ReservationData();
360 if (reader.Read())
361 {
362 retval.gridRecvKey = (string) reader["gridRecvKey"];
363 retval.gridSendKey = (string) reader["gridSendKey"];
364 retval.reservationCompany = (string) reader["resCompany"];
365 retval.reservationMaxX = Convert.ToInt32(reader["resXMax"].ToString());
366 retval.reservationMaxY = Convert.ToInt32(reader["resYMax"].ToString());
367 retval.reservationMinX = Convert.ToInt32(reader["resXMin"].ToString());
368 retval.reservationMinY = Convert.ToInt32(reader["resYMin"].ToString());
369 retval.reservationName = (string) reader["resName"];
370 retval.status = Convert.ToInt32(reader["status"].ToString()) == 1;
371 LLUUID.TryParse((string) reader["userUUID"], out retval.userUUID);
372 }
373 else
374 {
375 return null;
376 }
377 return retval;
378 }
379
380 /// <summary>
381 /// Reads an agent row from a database reader
382 /// </summary>
383 /// <param name="reader">An active database reader</param>
384 /// <returns>A user session agent</returns>
385 public UserAgentData readAgentRow(IDataReader reader)
386 {
387 UserAgentData retval = new UserAgentData();
388
389 if (reader.Read())
390 {
391 // Agent IDs
392 if (!LLUUID.TryParse((string)reader["UUID"], out retval.UUID))
393 return null;
394 LLUUID.TryParse((string) reader["sessionID"], out retval.sessionID);
395 LLUUID.TryParse((string)reader["secureSessionID"], out retval.secureSessionID);
396
397 // Agent Who?
398 retval.agentIP = (string) reader["agentIP"];
399 retval.agentPort = Convert.ToUInt32(reader["agentPort"].ToString());
400 retval.agentOnline = Convert.ToBoolean(Convert.ToInt16(reader["agentOnline"].ToString()));
401
402 // Login/Logout times (UNIX Epoch)
403 retval.loginTime = Convert.ToInt32(reader["loginTime"].ToString());
404 retval.logoutTime = Convert.ToInt32(reader["logoutTime"].ToString());
405
406 // Current position
407 retval.currentRegion = new LLUUID((string)reader["currentRegion"]);
408 retval.currentHandle = Convert.ToUInt64(reader["currentHandle"].ToString());
409 LLVector3.TryParse((string) reader["currentPos"], out retval.currentPos);
410 }
411 else
412 {
413 return null;
414 }
415 return retval;
416 }
417
418 /// <summary>
419 /// Reads a user profile from an active data reader
420 /// </summary>
421 /// <param name="reader">An active database reader</param>
422 /// <returns>A user profile</returns>
423 public UserProfileData readUserRow(IDataReader reader)
424 {
425 UserProfileData retval = new UserProfileData();
426
427 if (reader.Read())
428 {
429 if (!LLUUID.TryParse((string)reader["UUID"], out retval.UUID))
430 return null;
431 retval.username = (string) reader["username"];
432 retval.surname = (string) reader["lastname"];
433
434 retval.passwordHash = (string) reader["passwordHash"];
435 retval.passwordSalt = (string) reader["passwordSalt"];
436
437 retval.homeRegion = Convert.ToUInt64(reader["homeRegion"].ToString());
438 retval.homeLocation = new LLVector3(
439 Convert.ToSingle(reader["homeLocationX"].ToString()),
440 Convert.ToSingle(reader["homeLocationY"].ToString()),
441 Convert.ToSingle(reader["homeLocationZ"].ToString()));
442 retval.homeLookAt = new LLVector3(
443 Convert.ToSingle(reader["homeLookAtX"].ToString()),
444 Convert.ToSingle(reader["homeLookAtY"].ToString()),
445 Convert.ToSingle(reader["homeLookAtZ"].ToString()));
446
447 retval.created = Convert.ToInt32(reader["created"].ToString());
448 retval.lastLogin = Convert.ToInt32(reader["lastLogin"].ToString());
449
450 retval.userInventoryURI = (string) reader["userInventoryURI"];
451 retval.userAssetURI = (string) reader["userAssetURI"];
452
453 retval.profileCanDoMask = Convert.ToUInt32(reader["profileCanDoMask"].ToString());
454 retval.profileWantDoMask = Convert.ToUInt32(reader["profileWantDoMask"].ToString());
455
456 if (reader.IsDBNull(reader.GetOrdinal("profileAboutText")))
457 retval.profileAboutText = "";
458 else
459 retval.profileAboutText = (string) reader["profileAboutText"];
460
461 if (reader.IsDBNull(reader.GetOrdinal("profileFirstText")))
462 retval.profileFirstText = "";
463 else
464 retval.profileFirstText = (string)reader["profileFirstText"];
465
466 if (reader.IsDBNull(reader.GetOrdinal("profileImage")))
467 retval.profileImage = LLUUID.Zero;
468 else
469 LLUUID.TryParse((string)reader["profileImage"], out retval.profileImage);
470
471 if (reader.IsDBNull(reader.GetOrdinal("profileFirstImage")))
472 retval.profileFirstImage = LLUUID.Zero;
473 else
474 LLUUID.TryParse((string)reader["profileFirstImage"], out retval.profileFirstImage);
475
476 if(reader.IsDBNull(reader.GetOrdinal("webLoginKey")))
477 {
478 retval.webLoginKey = LLUUID.Zero;
479 }
480 else
481 {
482 LLUUID.TryParse((string)reader["webLoginKey"], out retval.webLoginKey);
483 }
484 }
485 else
486 {
487 return null;
488 }
489 return retval;
490 }
491
492 /// <summary>
493 /// Inserts a new row into the log database
494 /// </summary>
495 /// <param name="serverDaemon">The daemon which triggered this event</param>
496 /// <param name="target">Who were we operating on when this occured (region UUID, user UUID, etc)</param>
497 /// <param name="methodCall">The method call where the problem occured</param>
498 /// <param name="arguments">The arguments passed to the method</param>
499 /// <param name="priority">How critical is this?</param>
500 /// <param name="logMessage">Extra message info</param>
501 /// <returns>Saved successfully?</returns>
502 public bool insertLogRow(string serverDaemon, string target, string methodCall, string arguments, int priority,
503 string logMessage)
504 {
505 string sql = "INSERT INTO logs (`target`, `server`, `method`, `arguments`, `priority`, `message`) VALUES ";
506 sql += "(?target, ?server, ?method, ?arguments, ?priority, ?message)";
507
508 Dictionary<string, string> parameters = new Dictionary<string, string>();
509 parameters["?server"] = serverDaemon;
510 parameters["?target"] = target;
511 parameters["?method"] = methodCall;
512 parameters["?arguments"] = arguments;
513 parameters["?priority"] = priority.ToString();
514 parameters["?message"] = logMessage;
515
516 bool returnval = false;
517
518 try
519 {
520 IDbCommand result = Query(sql, parameters);
521
522 if (result.ExecuteNonQuery() == 1)
523 returnval = true;
524
525 result.Dispose();
526 }
527 catch (Exception e)
528 {
529 m_log.Error(e.ToString());
530 return false;
531 }
532
533 return returnval;
534 }
535
536 /// <summary>
537 /// Creates a new user and inserts it into the database
538 /// </summary>
539 /// <param name="uuid">User ID</param>
540 /// <param name="username">First part of the login</param>
541 /// <param name="lastname">Second part of the login</param>
542 /// <param name="passwordHash">A salted hash of the users password</param>
543 /// <param name="passwordSalt">The salt used for the password hash</param>
544 /// <param name="homeRegion">A regionHandle of the users home region</param>
545 /// <param name="homeLocX">Home region position vector</param>
546 /// <param name="homeLocY">Home region position vector</param>
547 /// <param name="homeLocZ">Home region position vector</param>
548 /// <param name="homeLookAtX">Home region 'look at' vector</param>
549 /// <param name="homeLookAtY">Home region 'look at' vector</param>
550 /// <param name="homeLookAtZ">Home region 'look at' vector</param>
551 /// <param name="created">Account created (unix timestamp)</param>
552 /// <param name="lastlogin">Last login (unix timestamp)</param>
553 /// <param name="inventoryURI">Users inventory URI</param>
554 /// <param name="assetURI">Users asset URI</param>
555 /// <param name="canDoMask">I can do mask</param>
556 /// <param name="wantDoMask">I want to do mask</param>
557 /// <param name="aboutText">Profile text</param>
558 /// <param name="firstText">Firstlife text</param>
559 /// <param name="profileImage">UUID for profile image</param>
560 /// <param name="firstImage">UUID for firstlife image</param>
561 /// <returns>Success?</returns>
562 public bool insertUserRow(LLUUID uuid, string username, string lastname, string passwordHash,
563 string passwordSalt, UInt64 homeRegion, float homeLocX, float homeLocY, float homeLocZ,
564 float homeLookAtX, float homeLookAtY, float homeLookAtZ, int created, int lastlogin,
565 string inventoryURI, string assetURI, uint canDoMask, uint wantDoMask,
566 string aboutText, string firstText,
567 LLUUID profileImage, LLUUID firstImage, LLUUID webLoginKey)
568 {
569 m_log.Debug("[MySQLManager]: Fetching profile for " + uuid.ToString());
570 string sql =
571 "INSERT INTO users (`UUID`, `username`, `lastname`, `passwordHash`, `passwordSalt`, `homeRegion`, ";
572 sql +=
573 "`homeLocationX`, `homeLocationY`, `homeLocationZ`, `homeLookAtX`, `homeLookAtY`, `homeLookAtZ`, `created`, ";
574 sql +=
575 "`lastLogin`, `userInventoryURI`, `userAssetURI`, `profileCanDoMask`, `profileWantDoMask`, `profileAboutText`, ";
576 sql += "`profileFirstText`, `profileImage`, `profileFirstImage`, `webLoginKey`) VALUES ";
577
578 sql += "(?UUID, ?username, ?lastname, ?passwordHash, ?passwordSalt, ?homeRegion, ";
579 sql +=
580 "?homeLocationX, ?homeLocationY, ?homeLocationZ, ?homeLookAtX, ?homeLookAtY, ?homeLookAtZ, ?created, ";
581 sql +=
582 "?lastLogin, ?userInventoryURI, ?userAssetURI, ?profileCanDoMask, ?profileWantDoMask, ?profileAboutText, ";
583 sql += "?profileFirstText, ?profileImage, ?profileFirstImage, ?webLoginKey)";
584
585 Dictionary<string, string> parameters = new Dictionary<string, string>();
586 parameters["?UUID"] = uuid.ToString();
587 parameters["?username"] = username.ToString();
588 parameters["?lastname"] = lastname.ToString();
589 parameters["?passwordHash"] = passwordHash.ToString();
590 parameters["?passwordSalt"] = passwordSalt.ToString();
591 parameters["?homeRegion"] = homeRegion.ToString();
592 parameters["?homeLocationX"] = homeLocX.ToString();
593 parameters["?homeLocationY"] = homeLocY.ToString();
594 parameters["?homeLocationZ"] = homeLocZ.ToString();
595 parameters["?homeLookAtX"] = homeLookAtX.ToString();
596 parameters["?homeLookAtY"] = homeLookAtY.ToString();
597 parameters["?homeLookAtZ"] = homeLookAtZ.ToString();
598 parameters["?created"] = created.ToString();
599 parameters["?lastLogin"] = lastlogin.ToString();
600 parameters["?userInventoryURI"] = String.Empty;
601 parameters["?userAssetURI"] = String.Empty;
602 parameters["?profileCanDoMask"] = "0";
603 parameters["?profileWantDoMask"] = "0";
604 parameters["?profileAboutText"] = aboutText;
605 parameters["?profileFirstText"] = firstText;
606 parameters["?profileImage"] = profileImage.ToString();
607 parameters["?profileFirstImage"] = firstImage.ToString();
608 parameters["?webLoginKey"] = string.Empty;
609
610 bool returnval = false;
611
612 try
613 {
614 IDbCommand result = Query(sql, parameters);
615
616 if (result.ExecuteNonQuery() == 1)
617 returnval = true;
618
619 result.Dispose();
620 }
621 catch (Exception e)
622 {
623 m_log.Error(e.ToString());
624 return false;
625 }
626
627 m_log.Debug("[MySQLManager]: Fetch user retval == " + returnval.ToString());
628 return returnval;
629 }
630
631 /// <summary>
632 /// Creates a new user and inserts it into the database
633 /// </summary>
634 /// <param name="uuid">User ID</param>
635 /// <param name="username">First part of the login</param>
636 /// <param name="lastname">Second part of the login</param>
637 /// <param name="passwordHash">A salted hash of the users password</param>
638 /// <param name="passwordSalt">The salt used for the password hash</param>
639 /// <param name="homeRegion">A regionHandle of the users home region</param>
640 /// <param name="homeLocX">Home region position vector</param>
641 /// <param name="homeLocY">Home region position vector</param>
642 /// <param name="homeLocZ">Home region position vector</param>
643 /// <param name="homeLookAtX">Home region 'look at' vector</param>
644 /// <param name="homeLookAtY">Home region 'look at' vector</param>
645 /// <param name="homeLookAtZ">Home region 'look at' vector</param>
646 /// <param name="created">Account created (unix timestamp)</param>
647 /// <param name="lastlogin">Last login (unix timestamp)</param>
648 /// <param name="inventoryURI">Users inventory URI</param>
649 /// <param name="assetURI">Users asset URI</param>
650 /// <param name="canDoMask">I can do mask</param>
651 /// <param name="wantDoMask">I want to do mask</param>
652 /// <param name="aboutText">Profile text</param>
653 /// <param name="firstText">Firstlife text</param>
654 /// <param name="profileImage">UUID for profile image</param>
655 /// <param name="firstImage">UUID for firstlife image</param>
656 /// <returns>Success?</returns>
657 public bool updateUserRow(LLUUID uuid, string username, string lastname, string passwordHash,
658 string passwordSalt, UInt64 homeRegion, float homeLocX, float homeLocY, float homeLocZ,
659 float homeLookAtX, float homeLookAtY, float homeLookAtZ, int created, int lastlogin,
660 string inventoryURI, string assetURI, uint canDoMask, uint wantDoMask,
661 string aboutText, string firstText,
662 LLUUID profileImage, LLUUID firstImage, LLUUID webLoginKey)
663 {
664 string sql = "UPDATE users SET `username` = ?username , `lastname` = ?lastname ";
665 sql += ", `passwordHash` = ?passwordHash , `passwordSalt` = ?passwordSalt , ";
666 sql += "`homeRegion` = ?homeRegion , `homeLocationX` = ?homeLocationX , ";
667 sql += "`homeLocationY` = ?homeLocationY , `homeLocationZ` = ?homeLocationZ , ";
668 sql += "`homeLookAtX` = ?homeLookAtX , `homeLookAtY` = ?homeLookAtY , ";
669 sql += "`homeLookAtZ` = ?homeLookAtZ , `created` = ?created , `lastLogin` = ?lastLogin , ";
670 sql += "`userInventoryURI` = ?userInventoryURI , `userAssetURI` = ?userAssetURI , ";
671 sql += "`profileCanDoMask` = ?profileCanDoMask , `profileWantDoMask` = ?profileWantDoMask , ";
672 sql += "`profileAboutText` = ?profileAboutText , `profileFirstText` = ?profileFirstText, ";
673 sql += "`profileImage` = ?profileImage , `profileFirstImage` = ?profileFirstImage , ";
674 sql += "`webLoginKey` = ?webLoginKey WHERE UUID = ?UUID";
675
676 Dictionary<string, string> parameters = new Dictionary<string, string>();
677 parameters["?UUID"] = uuid.ToString();
678 parameters["?username"] = username.ToString();
679 parameters["?lastname"] = lastname.ToString();
680 parameters["?passwordHash"] = passwordHash.ToString();
681 parameters["?passwordSalt"] = passwordSalt.ToString();
682 parameters["?homeRegion"] = homeRegion.ToString();
683 parameters["?homeLocationX"] = homeLocX.ToString();
684 parameters["?homeLocationY"] = homeLocY.ToString();
685 parameters["?homeLocationZ"] = homeLocZ.ToString();
686 parameters["?homeLookAtX"] = homeLookAtX.ToString();
687 parameters["?homeLookAtY"] = homeLookAtY.ToString();
688 parameters["?homeLookAtZ"] = homeLookAtZ.ToString();
689 parameters["?created"] = created.ToString();
690 parameters["?lastLogin"] = lastlogin.ToString();
691 parameters["?userInventoryURI"] = inventoryURI;
692 parameters["?userAssetURI"] = assetURI;
693 parameters["?profileCanDoMask"] = "0";
694 parameters["?profileWantDoMask"] = "0";
695 parameters["?profileAboutText"] = aboutText;
696 parameters["?profileFirstText"] = firstText;
697 parameters["?profileImage"] = profileImage.ToString();
698 parameters["?profileFirstImage"] = firstImage.ToString();
699 parameters["?webLoginKey"] = webLoginKey.ToString();
700
701 bool returnval = false;
702 try
703 {
704 IDbCommand result = Query(sql, parameters);
705
706 if (result.ExecuteNonQuery() == 1)
707 returnval = true;
708
709 result.Dispose();
710 }
711 catch (Exception e)
712 {
713 m_log.Error(e.ToString());
714 return false;
715 }
716
717 m_log.Debug("[MySQLManager]: update user retval == " + returnval.ToString());
718 return returnval;
719 }
720
721 /// <summary>
722 /// Inserts a new region into the database
723 /// </summary>
724 /// <param name="profile">The region to insert</param>
725 /// <returns>Success?</returns>
726 public bool insertRegion(RegionProfileData regiondata)
727 {
728 bool GRID_ONLY_UPDATE_NECESSARY_DATA = false;
729
730 string sql = String.Empty;
731 if (GRID_ONLY_UPDATE_NECESSARY_DATA)
732 {
733 sql += "INSERT INTO ";
734 }
735 else
736 {
737 sql += "REPLACE INTO ";
738 }
739
740 sql += "regions (regionHandle, regionName, uuid, regionRecvKey, regionSecret, regionSendKey, regionDataURI, ";
741 sql +=
742 "serverIP, serverPort, serverURI, locX, locY, locZ, eastOverrideHandle, westOverrideHandle, southOverrideHandle, northOverrideHandle, regionAssetURI, regionAssetRecvKey, ";
743
744 // part of an initial brutish effort to provide accurate information (as per the xml region spec)
745 // wrt the ownership of a given region
746 // the (very bad) assumption is that this value is being read and handled inconsistently or
747 // not at all. Current strategy is to put the code in place to support the validity of this information
748 // and to roll forward debugging any issues from that point
749 //
750 // this particular section of the mod attempts to implement the commit of a supplied value
751 // server for the UUID of the region's owner (master avatar). It consists of the addition of the column and value to the relevant sql,
752 // as well as the related parameterization
753 sql +=
754 "regionAssetSendKey, regionUserURI, regionUserRecvKey, regionUserSendKey, regionMapTexture, serverHttpPort, serverRemotingPort, owner_uuid, originUUID) VALUES ";
755
756 sql += "(?regionHandle, ?regionName, ?uuid, ?regionRecvKey, ?regionSecret, ?regionSendKey, ?regionDataURI, ";
757 sql +=
758 "?serverIP, ?serverPort, ?serverURI, ?locX, ?locY, ?locZ, ?eastOverrideHandle, ?westOverrideHandle, ?southOverrideHandle, ?northOverrideHandle, ?regionAssetURI, ?regionAssetRecvKey, ";
759 sql +=
760 "?regionAssetSendKey, ?regionUserURI, ?regionUserRecvKey, ?regionUserSendKey, ?regionMapTexture, ?serverHttpPort, ?serverRemotingPort, ?owner_uuid, ?originUUID)";
761
762 if (GRID_ONLY_UPDATE_NECESSARY_DATA)
763 {
764 sql += "ON DUPLICATE KEY UPDATE serverIP = ?serverIP, serverPort = ?serverPort, serverURI = ?serverURI, owner_uuid - ?owner_uuid;";
765 }
766 else
767 {
768 sql += ";";
769 }
770
771 Dictionary<string, string> parameters = new Dictionary<string, string>();
772
773 parameters["?regionHandle"] = regiondata.regionHandle.ToString();
774 parameters["?regionName"] = regiondata.regionName.ToString();
775 parameters["?uuid"] = regiondata.UUID.ToString();
776 parameters["?regionRecvKey"] = regiondata.regionRecvKey.ToString();
777 parameters["?regionSecret"] = regiondata.regionSecret.ToString();
778 parameters["?regionSendKey"] = regiondata.regionSendKey.ToString();
779 parameters["?regionDataURI"] = regiondata.regionDataURI.ToString();
780 parameters["?serverIP"] = regiondata.serverIP.ToString();
781 parameters["?serverPort"] = regiondata.serverPort.ToString();
782 parameters["?serverURI"] = regiondata.serverURI.ToString();
783 parameters["?locX"] = regiondata.regionLocX.ToString();
784 parameters["?locY"] = regiondata.regionLocY.ToString();
785 parameters["?locZ"] = regiondata.regionLocZ.ToString();
786 parameters["?eastOverrideHandle"] = regiondata.regionEastOverrideHandle.ToString();
787 parameters["?westOverrideHandle"] = regiondata.regionWestOverrideHandle.ToString();
788 parameters["?northOverrideHandle"] = regiondata.regionNorthOverrideHandle.ToString();
789 parameters["?southOverrideHandle"] = regiondata.regionSouthOverrideHandle.ToString();
790 parameters["?regionAssetURI"] = regiondata.regionAssetURI.ToString();
791 parameters["?regionAssetRecvKey"] = regiondata.regionAssetRecvKey.ToString();
792 parameters["?regionAssetSendKey"] = regiondata.regionAssetSendKey.ToString();
793 parameters["?regionUserURI"] = regiondata.regionUserURI.ToString();
794 parameters["?regionUserRecvKey"] = regiondata.regionUserRecvKey.ToString();
795 parameters["?regionUserSendKey"] = regiondata.regionUserSendKey.ToString();
796 parameters["?regionMapTexture"] = regiondata.regionMapTextureID.ToString();
797 parameters["?serverHttpPort"] = regiondata.httpPort.ToString();
798 parameters["?serverRemotingPort"] = regiondata.remotingPort.ToString();
799 parameters["?owner_uuid"] = regiondata.owner_uuid.ToString();
800 parameters["?originUUID"] = regiondata.originUUID.ToString();
801
802 bool returnval = false;
803
804 try
805 {
806 IDbCommand result = Query(sql, parameters);
807
808 //Console.WriteLine(result.CommandText);
809 int x;
810 if ((x = result.ExecuteNonQuery()) > 0)
811 {
812 returnval = true;
813 }
814 result.Dispose();
815 }
816 catch (Exception e)
817 {
818 m_log.Error(e.ToString());
819 return false;
820 }
821
822 return returnval;
823 }
824
825 /// <summary>
826 /// Delete a region from the database
827 /// </summary>
828 /// <param name="profile">The region to insert</param>
829 /// <returns>Success?</returns>
830 //public bool deleteRegion(RegionProfileData regiondata)
831 public bool deleteRegion(string uuid)
832 {
833 bool returnval = false;
834
835 string sql = "DELETE FROM regions WHERE uuid = ?uuid;";
836
837 Dictionary<string, string> parameters = new Dictionary<string, string>();
838
839 try
840 {
841 parameters["?uuid"] = uuid;
842
843 IDbCommand result = Query(sql, parameters);
844
845 int x;
846 if ((x = result.ExecuteNonQuery()) > 0)
847 {
848 returnval = true;
849 }
850 result.Dispose();
851 }
852 catch (Exception e)
853 {
854 m_log.Error(e.ToString());
855 return false;
856 }
857
858 return returnval;
859 }
860
861 /// <summary>
862 /// Creates a new agent and inserts it into the database
863 /// </summary>
864 /// <param name="agentdata">The agent data to be inserted</param>
865 /// <returns>Success?</returns>
866 public bool insertAgentRow(UserAgentData agentdata)
867 {
868 string sql = String.Empty;
869 sql += "REPLACE INTO ";
870 sql += "agents (UUID, sessionID, secureSessionID, agentIP, agentPort, agentOnline, loginTime, logoutTime, currentRegion, currentHandle, currentPos) VALUES ";
871 sql += "(?UUID, ?sessionID, ?secureSessionID, ?agentIP, ?agentPort, ?agentOnline, ?loginTime, ?logoutTime, ?currentRegion, ?currentHandle, ?currentPos);";
872 Dictionary<string, string> parameters = new Dictionary<string, string>();
873
874 parameters["?UUID"] = agentdata.UUID.ToString();
875 parameters["?sessionID"] = agentdata.sessionID.ToString();
876 parameters["?secureSessionID"] = agentdata.secureSessionID.ToString();
877 parameters["?agentIP"] = agentdata.agentIP.ToString();
878 parameters["?agentPort"] = agentdata.agentPort.ToString();
879 parameters["?agentOnline"] = (agentdata.agentOnline == true) ? "1" : "0";
880 parameters["?loginTime"] = agentdata.loginTime.ToString();
881 parameters["?logoutTime"] = agentdata.logoutTime.ToString();
882 parameters["?currentRegion"] = agentdata.currentRegion.ToString();
883 parameters["?currentHandle"] = agentdata.currentHandle.ToString();
884 parameters["?currentPos"] = "<" + ((int)agentdata.currentPos.X).ToString() + "," + ((int)agentdata.currentPos.Y).ToString() + "," + ((int)agentdata.currentPos.Z).ToString() + ">";
885
886 bool returnval = false;
887
888 try
889 {
890 IDbCommand result = Query(sql, parameters);
891
892 //Console.WriteLine(result.CommandText);
893 int x;
894 if ((x = result.ExecuteNonQuery()) > 0)
895 {
896 returnval = true;
897 }
898 result.Dispose();
899 }
900 catch (Exception e)
901 {
902 m_log.Error(e.ToString());
903 return false;
904 }
905
906 return returnval;
907 }
908 }
909}
diff --git a/OpenSim/Data/MySQL/MySQLUserData.cs b/OpenSim/Data/MySQL/MySQLUserData.cs
new file mode 100644
index 0000000..fd640ec
--- /dev/null
+++ b/OpenSim/Data/MySQL/MySQLUserData.cs
@@ -0,0 +1,643 @@
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.Text.RegularExpressions;
32using libsecondlife;
33using OpenSim.Framework.Console;
34
35namespace OpenSim.Framework.Data.MySQL
36{
37 /// <summary>
38 /// A database interface class to a user profile storage system
39 /// </summary>
40 internal class MySQLUserData : 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 MySQLManager 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? Yes, PLEASE!
60
61 IniFile iniFile = new IniFile("mysql_connection.ini");
62 string settingHostname = iniFile.ParseFileReadValue("hostname");
63 string settingDatabase = iniFile.ParseFileReadValue("database");
64 string settingUsername = iniFile.ParseFileReadValue("username");
65 string settingPassword = iniFile.ParseFileReadValue("password");
66 string settingPooling = iniFile.ParseFileReadValue("pooling");
67 string settingPort = iniFile.ParseFileReadValue("port");
68
69 m_usersTableName = iniFile.ParseFileReadValue("userstablename");
70 if( m_usersTableName == null )
71 {
72 m_usersTableName = "users";
73 }
74
75 m_userFriendsTableName = iniFile.ParseFileReadValue("userfriendstablename");
76 if (m_userFriendsTableName == null)
77 {
78 m_userFriendsTableName = "userfriends";
79 }
80
81 m_agentsTableName = iniFile.ParseFileReadValue("agentstablename");
82 if (m_agentsTableName == null)
83 {
84 m_agentsTableName = "agents";
85 }
86
87 database =
88 new MySQLManager(settingHostname, settingDatabase, settingUsername, settingPassword, settingPooling,
89 settingPort);
90
91 TestTables();
92 }
93
94 #region Test and initialization code
95
96 /// <summary>
97 /// Ensure that the user related tables exists and are at the latest version
98 /// </summary>
99 private void TestTables()
100 {
101 Dictionary<string, string> tableList = new Dictionary<string, string>();
102
103 tableList[m_agentsTableName] = null;
104 tableList[m_usersTableName] = null;
105 tableList[m_userFriendsTableName] = null;
106 database.GetTableVersion(tableList);
107
108 UpgradeAgentsTable(tableList[m_agentsTableName]);
109 UpgradeUsersTable(tableList[m_usersTableName]);
110 UpgradeFriendsTable(tableList[m_userFriendsTableName]);
111 }
112
113 /// <summary>
114 /// Create or upgrade the table if necessary
115 /// </summary>
116 /// <param name="oldVersion">A null indicates that the table does not
117 /// currently exist</param>
118 private void UpgradeAgentsTable(string oldVersion)
119 {
120 // null as the version, indicates that the table didn't exist
121 if (oldVersion == null)
122 {
123 database.ExecuteResourceSql("CreateAgentsTable.sql");
124 return;
125 }
126 }
127
128 /// <summary>
129 /// Create or upgrade the table if necessary
130 /// </summary>
131 /// <param name="oldVersion">A null indicates that the table does not
132 /// currently exist</param>
133 private void UpgradeUsersTable(string oldVersion)
134 {
135 // null as the version, indicates that the table didn't exist
136 if (oldVersion == null)
137 {
138 database.ExecuteResourceSql("CreateUsersTable.sql");
139 return;
140 }
141 else if (oldVersion.Contains("Rev. 1"))
142 {
143 database.ExecuteResourceSql("UpgradeUsersTableToVersion2.sql");
144 return;
145 }
146 //m_log.Info("[DB]: DBVers:" + oldVersion);
147 }
148
149 /// <summary>
150 /// Create or upgrade the table if necessary
151 /// </summary>
152 /// <param name="oldVersion">A null indicates that the table does not
153 /// currently exist</param>
154 private void UpgradeFriendsTable(string oldVersion)
155 {
156 // null as the version, indicates that the table didn't exist
157 if (oldVersion == null)
158 {
159 database.ExecuteResourceSql("CreateUserFriendsTable.sql");
160 return;
161 }
162 }
163
164 #endregion
165
166 // see IUserData
167 override public UserProfileData GetUserByName(string user, string last)
168 {
169 try
170 {
171 lock (database)
172 {
173 Dictionary<string, string> param = new Dictionary<string, string>();
174 param["?first"] = user;
175 param["?second"] = last;
176
177 IDbCommand result =
178 database.Query("SELECT * FROM " + m_usersTableName + " WHERE username = ?first AND lastname = ?second", param);
179 IDataReader reader = result.ExecuteReader();
180
181 UserProfileData row = database.readUserRow(reader);
182
183 reader.Close();
184 result.Dispose();
185 return row;
186 }
187 }
188 catch (Exception e)
189 {
190 database.Reconnect();
191 m_log.Error(e.ToString());
192 return null;
193 }
194 }
195
196 #region User Friends List Data
197
198 override public void AddNewUserFriend(LLUUID friendlistowner, LLUUID friend, uint perms)
199 {
200 int dtvalue = Util.UnixTimeSinceEpoch();
201
202 Dictionary<string, string> param = new Dictionary<string, string>();
203 param["?ownerID"] = friendlistowner.UUID.ToString();
204 param["?friendID"] = friend.UUID.ToString();
205 param["?friendPerms"] = perms.ToString();
206 param["?datetimestamp"] = dtvalue.ToString();
207
208 try
209 {
210 lock (database)
211 {
212 IDbCommand adder =
213 database.Query(
214 "INSERT INTO `" + m_userFriendsTableName + "` " +
215 "(`ownerID`,`friendID`,`friendPerms`,`datetimestamp`) " +
216 "VALUES " +
217 "(?ownerID,?friendID,?friendPerms,?datetimestamp)",
218 param);
219 adder.ExecuteNonQuery();
220
221 adder =
222 database.Query(
223 "INSERT INTO `" + m_userFriendsTableName + "` " +
224 "(`ownerID`,`friendID`,`friendPerms`,`datetimestamp`) " +
225 "VALUES " +
226 "(?friendID,?ownerID,?friendPerms,?datetimestamp)",
227 param);
228 adder.ExecuteNonQuery();
229 }
230 }
231 catch (Exception e)
232 {
233 database.Reconnect();
234 m_log.Error(e.ToString());
235 return;
236 }
237 }
238
239 override public void RemoveUserFriend(LLUUID friendlistowner, LLUUID friend)
240 {
241 Dictionary<string, string> param = new Dictionary<string, string>();
242 param["?ownerID"] = friendlistowner.UUID.ToString();
243 param["?friendID"] = friend.UUID.ToString();
244
245 try
246 {
247 lock (database)
248 {
249 IDbCommand updater =
250 database.Query(
251 "delete from " + m_userFriendsTableName + " where ownerID = ?ownerID and friendID = ?friendID",
252 param);
253 updater.ExecuteNonQuery();
254
255 updater =
256 database.Query(
257 "delete from " + m_userFriendsTableName + " where ownerID = ?friendID and friendID = ?ownerID",
258 param);
259 updater.ExecuteNonQuery();
260 }
261 }
262 catch (Exception e)
263 {
264 database.Reconnect();
265 m_log.Error(e.ToString());
266 return;
267 }
268 }
269
270 override public void UpdateUserFriendPerms(LLUUID friendlistowner, LLUUID friend, uint perms)
271 {
272 Dictionary<string, string> param = new Dictionary<string, string>();
273 param["?ownerID"] = friendlistowner.UUID.ToString();
274 param["?friendID"] = friend.UUID.ToString();
275 param["?friendPerms"] = perms.ToString();
276
277 try
278 {
279 lock (database)
280 {
281 IDbCommand updater =
282 database.Query(
283 "update " + m_userFriendsTableName +
284 " SET friendPerms = ?friendPerms " +
285 "where ownerID = ?ownerID and friendID = ?friendID",
286 param);
287 updater.ExecuteNonQuery();
288 }
289 }
290 catch (Exception e)
291 {
292 database.Reconnect();
293 m_log.Error(e.ToString());
294 return;
295 }
296 }
297
298 override public List<FriendListItem> GetUserFriendList(LLUUID friendlistowner)
299 {
300 List<FriendListItem> Lfli = new List<FriendListItem>();
301
302 Dictionary<string, string> param = new Dictionary<string, string>();
303 param["?ownerID"] = friendlistowner.UUID.ToString();
304
305 try
306 {
307 lock (database)
308 {
309 //Left Join userfriends to itself
310 IDbCommand result =
311 database.Query(
312 "select a.ownerID,a.friendID,a.friendPerms,b.friendPerms as ownerperms from " + m_userFriendsTableName + " as a, " + m_userFriendsTableName + " as b" +
313 " where a.ownerID = ?ownerID and b.ownerID = a.friendID and b.friendID = a.ownerID",
314 param);
315 IDataReader reader = result.ExecuteReader();
316
317 while (reader.Read())
318 {
319 FriendListItem fli = new FriendListItem();
320 fli.FriendListOwner = new LLUUID((string)reader["ownerID"]);
321 fli.Friend = new LLUUID((string)reader["friendID"]);
322 fli.FriendPerms = (uint)Convert.ToInt32(reader["friendPerms"]);
323
324 // This is not a real column in the database table, it's a joined column from the opposite record
325 fli.FriendListOwnerPerms = (uint)Convert.ToInt32(reader["ownerperms"]);
326
327 Lfli.Add(fli);
328 }
329 reader.Close();
330 result.Dispose();
331 }
332 }
333 catch (Exception e)
334 {
335 database.Reconnect();
336 m_log.Error(e.ToString());
337 return Lfli;
338 }
339
340 return Lfli;
341 }
342
343 #endregion
344
345 override public void UpdateUserCurrentRegion(LLUUID avatarid, LLUUID regionuuid)
346 {
347 m_log.Info("[USER]: Stub UpdateUserCUrrentRegion called");
348 }
349
350 override public List<Framework.AvatarPickerAvatar> GeneratePickerResults(LLUUID queryID, string query)
351 {
352 List<Framework.AvatarPickerAvatar> returnlist = new List<Framework.AvatarPickerAvatar>();
353
354 Regex objAlphaNumericPattern = new Regex("[^a-zA-Z0-9]");
355
356 string[] querysplit;
357 querysplit = query.Split(' ');
358 if (querysplit.Length == 2)
359 {
360 Dictionary<string, string> param = new Dictionary<string, string>();
361 param["?first"] = objAlphaNumericPattern.Replace(querysplit[0], String.Empty) + "%";
362 param["?second"] = objAlphaNumericPattern.Replace(querysplit[1], String.Empty) + "%";
363 try
364 {
365 lock (database)
366 {
367 IDbCommand result =
368 database.Query(
369 "SELECT UUID,username,lastname FROM " + m_usersTableName + " WHERE username like ?first AND lastname like ?second LIMIT 100",
370 param);
371 IDataReader reader = result.ExecuteReader();
372
373 while (reader.Read())
374 {
375 Framework.AvatarPickerAvatar user = new Framework.AvatarPickerAvatar();
376 user.AvatarID = new LLUUID((string) reader["UUID"]);
377 user.firstName = (string) reader["username"];
378 user.lastName = (string) reader["lastname"];
379 returnlist.Add(user);
380 }
381 reader.Close();
382 result.Dispose();
383 }
384 }
385 catch (Exception e)
386 {
387 database.Reconnect();
388 m_log.Error(e.ToString());
389 return returnlist;
390 }
391 }
392 else if (querysplit.Length == 1)
393 {
394 try
395 {
396 lock (database)
397 {
398 Dictionary<string, string> param = new Dictionary<string, string>();
399 param["?first"] = objAlphaNumericPattern.Replace(querysplit[0], String.Empty) + "%";
400
401 IDbCommand result =
402 database.Query(
403 "SELECT UUID,username,lastname FROM " + m_usersTableName + " WHERE username like ?first OR lastname like ?first LIMIT 100",
404 param);
405 IDataReader reader = result.ExecuteReader();
406
407 while (reader.Read())
408 {
409 Framework.AvatarPickerAvatar user = new Framework.AvatarPickerAvatar();
410 user.AvatarID = new LLUUID((string) reader["UUID"]);
411 user.firstName = (string) reader["username"];
412 user.lastName = (string) reader["lastname"];
413 returnlist.Add(user);
414 }
415 reader.Close();
416 result.Dispose();
417 }
418 }
419 catch (Exception e)
420 {
421 database.Reconnect();
422 m_log.Error(e.ToString());
423 return returnlist;
424 }
425 }
426 return returnlist;
427 }
428
429 // see IUserData
430 override public UserProfileData GetUserByUUID(LLUUID uuid)
431 {
432 try
433 {
434 lock (database)
435 {
436 Dictionary<string, string> param = new Dictionary<string, string>();
437 param["?uuid"] = uuid.ToString();
438
439 IDbCommand result = database.Query("SELECT * FROM " + m_usersTableName + " WHERE UUID = ?uuid", param);
440 IDataReader reader = result.ExecuteReader();
441
442 UserProfileData row = database.readUserRow(reader);
443
444 reader.Close();
445 result.Dispose();
446
447 return row;
448 }
449 }
450 catch (Exception e)
451 {
452 database.Reconnect();
453 m_log.Error(e.ToString());
454 return null;
455 }
456 }
457
458 /// <summary>
459 /// Returns a user session searching by name
460 /// </summary>
461 /// <param name="name">The account name</param>
462 /// <returns>The users session</returns>
463 override public UserAgentData GetAgentByName(string name)
464 {
465 return GetAgentByName(name.Split(' ')[0], name.Split(' ')[1]);
466 }
467
468 /// <summary>
469 /// Returns a user session by account name
470 /// </summary>
471 /// <param name="user">First part of the users account name</param>
472 /// <param name="last">Second part of the users account name</param>
473 /// <returns>The users session</returns>
474 override public UserAgentData GetAgentByName(string user, string last)
475 {
476 UserProfileData profile = GetUserByName(user, last);
477 return GetAgentByUUID(profile.UUID);
478 }
479
480 override public void StoreWebLoginKey(LLUUID AgentID, LLUUID WebLoginKey)
481 {
482 Dictionary<string, string> param = new Dictionary<string, string>();
483 param["?UUID"] = AgentID.UUID.ToString();
484 param["?webLoginKey"] = WebLoginKey.UUID.ToString();
485
486 try
487 {
488 lock (database)
489 {
490 IDbCommand updater =
491 database.Query(
492 "update " + m_usersTableName + " SET webLoginKey = ?webLoginKey " +
493 "where UUID = ?UUID",
494 param);
495 updater.ExecuteNonQuery();
496 }
497 }
498 catch (Exception e)
499 {
500 database.Reconnect();
501 m_log.Error(e.ToString());
502 return;
503 }
504 }
505
506 /// <summary>
507 /// Returns an agent session by account UUID
508 /// </summary>
509 /// <param name="uuid">The accounts UUID</param>
510 /// <returns>The users session</returns>
511 override public UserAgentData GetAgentByUUID(LLUUID uuid)
512 {
513 try
514 {
515 lock (database)
516 {
517 Dictionary<string, string> param = new Dictionary<string, string>();
518 param["?uuid"] = uuid.ToString();
519
520 IDbCommand result = database.Query("SELECT * FROM " + m_agentsTableName + " WHERE UUID = ?uuid", param);
521 IDataReader reader = result.ExecuteReader();
522
523 UserAgentData row = database.readAgentRow(reader);
524
525 reader.Close();
526 result.Dispose();
527
528 return row;
529 }
530 }
531 catch (Exception e)
532 {
533 database.Reconnect();
534 m_log.Error(e.ToString());
535 return null;
536 }
537 }
538
539 /// <summary>
540 /// Creates a new users profile
541 /// </summary>
542 /// <param name="user">The user profile to create</param>
543 override public void AddNewUserProfile(UserProfileData user)
544 {
545 try
546 {
547 lock (database)
548 {
549 database.insertUserRow(user.UUID, user.username, user.surname, user.passwordHash, user.passwordSalt,
550 user.homeRegion, user.homeLocation.X, user.homeLocation.Y,
551 user.homeLocation.Z,
552 user.homeLookAt.X, user.homeLookAt.Y, user.homeLookAt.Z, user.created,
553 user.lastLogin, user.userInventoryURI, user.userAssetURI,
554 user.profileCanDoMask, user.profileWantDoMask,
555 user.profileAboutText, user.profileFirstText, user.profileImage,
556 user.profileFirstImage, user.webLoginKey);
557 }
558 }
559 catch (Exception e)
560 {
561 database.Reconnect();
562 m_log.Error(e.ToString());
563 }
564 }
565
566 /// <summary>
567 /// Creates a new agent
568 /// </summary>
569 /// <param name="agent">The agent to create</param>
570 override public void AddNewUserAgent(UserAgentData agent)
571 {
572 try
573 {
574 lock (database)
575 {
576 database.insertAgentRow(agent);
577 }
578 }
579 catch (Exception e)
580 {
581 database.Reconnect();
582 m_log.Error(e.ToString());
583 }
584 }
585
586 /// <summary>
587 /// Updates a user profile stored in the DB
588 /// </summary>
589 /// <param name="user">The profile data to use to update the DB</param>
590 override public bool UpdateUserProfile(UserProfileData user)
591 {
592 database.updateUserRow(user.UUID, user.username, user.surname, user.passwordHash, user.passwordSalt,
593 user.homeRegion, user.homeLocation.X, user.homeLocation.Y, user.homeLocation.Z, user.homeLookAt.X,
594 user.homeLookAt.Y, user.homeLookAt.Z, user.created, user.lastLogin, user.userInventoryURI,
595 user.userAssetURI, user.profileCanDoMask, user.profileWantDoMask, user.profileAboutText,
596 user.profileFirstText, user.profileImage, user.profileFirstImage, user.webLoginKey);
597 return true;
598 }
599
600 /// <summary>
601 /// Performs a money transfer request between two accounts
602 /// </summary>
603 /// <param name="from">The senders account ID</param>
604 /// <param name="to">The receivers account ID</param>
605 /// <param name="amount">The amount to transfer</param>
606 /// <returns>Success?</returns>
607 override public bool MoneyTransferRequest(LLUUID from, LLUUID to, uint amount)
608 {
609 return false;
610 }
611
612 /// <summary>
613 /// Performs an inventory transfer request between two accounts
614 /// </summary>
615 /// <remarks>TODO: Move to inventory server</remarks>
616 /// <param name="from">The senders account ID</param>
617 /// <param name="to">The receivers account ID</param>
618 /// <param name="item">The item to transfer</param>
619 /// <returns>Success?</returns>
620 override public bool InventoryTransferRequest(LLUUID from, LLUUID to, LLUUID item)
621 {
622 return false;
623 }
624
625 /// <summary>
626 /// Database provider name
627 /// </summary>
628 /// <returns>Provider name</returns>
629 override public string getName()
630 {
631 return "MySQL Userdata Interface";
632 }
633
634 /// <summary>
635 /// Database provider version
636 /// </summary>
637 /// <returns>provider version</returns>
638 override public string GetVersion()
639 {
640 return "0.1";
641 }
642 }
643}
diff --git a/OpenSim/Data/MySQL/Properties/AssemblyInfo.cs b/OpenSim/Data/MySQL/Properties/AssemblyInfo.cs
new file mode 100644
index 0000000..060e26c
--- /dev/null
+++ b/OpenSim/Data/MySQL/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.MySQL")]
36[assembly : AssemblyDescription("")]
37[assembly : AssemblyConfiguration("")]
38[assembly : AssemblyCompany("")]
39[assembly : AssemblyProduct("OpenSim.Framework.Data.MySQL")]
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("e49826b2-dcef-41be-a5bd-596733fa3304")]
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/MySQL/Resources/AvatarAppearance.sql b/OpenSim/Data/MySQL/Resources/AvatarAppearance.sql
new file mode 100644
index 0000000..b638ee2
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/AvatarAppearance.sql
@@ -0,0 +1,42 @@
1--
2-- Create schema avatar_appearance
3--
4
5CREATE DATABASE IF NOT EXISTS avatar_appearance;
6USE avatar_appearance;
7
8DROP TABLE IF EXISTS `avatarappearance`;
9CREATE TABLE `avatarappearance` (
10 `UUID` char(36) NOT NULL,
11 `Serial` int(10) unsigned NOT NULL,
12 `WearableItem0` char(36) NOT NULL,
13 `WearableAsset0` char(36) NOT NULL,
14 `WearableItem1` char(36) NOT NULL,
15 `WearableAsset1` char(36) NOT NULL,
16 `WearableItem2` char(36) NOT NULL,
17 `WearableAsset2` char(36) NOT NULL,
18 `WearableItem3` char(36) NOT NULL,
19 `WearableAsset3` char(36) NOT NULL,
20 `WearableItem4` char(36) NOT NULL,
21 `WearableAsset4` char(36) NOT NULL,
22 `WearableItem5` char(36) NOT NULL,
23 `WearableAsset5` char(36) NOT NULL,
24 `WearableItem6` char(36) NOT NULL,
25 `WearableAsset6` char(36) NOT NULL,
26 `WearableItem7` char(36) NOT NULL,
27 `WearableAsset7` char(36) NOT NULL,
28 `WearableItem8` char(36) NOT NULL,
29 `WearableAsset8` char(36) NOT NULL,
30 `WearableItem9` char(36) NOT NULL,
31 `WearableAsset9` char(36) NOT NULL,
32 `WearableItem10` char(36) NOT NULL,
33 `WearableAsset10` char(36) NOT NULL,
34 `WearableItem11` char(36) NOT NULL,
35 `WearableAsset11` char(36) NOT NULL,
36 `WearableItem12` char(36) NOT NULL,
37 `WearableAsset12` char(36) NOT NULL,
38
39
40 PRIMARY KEY (`UUID`)
41) ENGINE=InnoDB DEFAULT CHARSET=utf8;
42
diff --git a/OpenSim/Data/MySQL/Resources/CreateAgentsTable.sql b/OpenSim/Data/MySQL/Resources/CreateAgentsTable.sql
new file mode 100644
index 0000000..3ef7bc9
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/CreateAgentsTable.sql
@@ -0,0 +1,24 @@
1SET FOREIGN_KEY_CHECKS=0;
2-- ----------------------------
3-- Table structure for agents
4-- ----------------------------
5CREATE TABLE `agents` (
6 `UUID` varchar(36) NOT NULL,
7 `sessionID` varchar(36) NOT NULL,
8 `secureSessionID` varchar(36) NOT NULL,
9 `agentIP` varchar(16) NOT NULL,
10 `agentPort` int(11) NOT NULL,
11 `agentOnline` tinyint(4) NOT NULL,
12 `loginTime` int(11) NOT NULL,
13 `logoutTime` int(11) NOT NULL,
14 `currentRegion` varchar(36) NOT NULL,
15 `currentHandle` bigint(20) unsigned NOT NULL,
16 `currentPos` varchar(64) NOT NULL,
17 PRIMARY KEY (`UUID`),
18 UNIQUE KEY `session` (`sessionID`),
19 UNIQUE KEY `ssession` (`secureSessionID`)
20) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 1';
21
22-- ----------------------------
23-- Records
24-- ----------------------------
diff --git a/OpenSim/Data/MySQL/Resources/CreateAssetsTable.sql b/OpenSim/Data/MySQL/Resources/CreateAssetsTable.sql
new file mode 100644
index 0000000..2c750fe
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/CreateAssetsTable.sql
@@ -0,0 +1,11 @@
1CREATE TABLE `assets` (
2 `id` binary(16) NOT NULL,
3 `name` varchar(64) NOT NULL,
4 `description` varchar(64) NOT NULL,
5 `assetType` tinyint(4) NOT NULL,
6 `invType` tinyint(4) NOT NULL,
7 `local` tinyint(1) NOT NULL,
8 `temporary` tinyint(1) NOT NULL,
9 `data` longblob NOT NULL,
10 PRIMARY KEY (`id`)
11) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 1'; \ No newline at end of file
diff --git a/OpenSim/Data/MySQL/Resources/CreateFoldersTable.sql b/OpenSim/Data/MySQL/Resources/CreateFoldersTable.sql
new file mode 100644
index 0000000..b5bddde
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/CreateFoldersTable.sql
@@ -0,0 +1,11 @@
1CREATE TABLE `inventoryfolders` (
2 `folderID` varchar(36) NOT NULL default '',
3 `agentID` varchar(36) default NULL,
4 `parentFolderID` varchar(36) default NULL,
5 `folderName` varchar(64) default NULL,
6 `type` smallint NOT NULL default 0,
7 `version` int NOT NULL default 0,
8 PRIMARY KEY (`folderID`),
9 KEY `owner` (`agentID`),
10 KEY `parent` (`parentFolderID`)
11) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 2';
diff --git a/OpenSim/Data/MySQL/Resources/CreateItemsTable.sql b/OpenSim/Data/MySQL/Resources/CreateItemsTable.sql
new file mode 100644
index 0000000..1723ee3
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/CreateItemsTable.sql
@@ -0,0 +1,18 @@
1CREATE TABLE `inventoryitems` (
2 `inventoryID` varchar(36) NOT NULL default '',
3 `assetID` varchar(36) default NULL,
4 `assetType` int(11) default NULL,
5 `parentFolderID` varchar(36) default NULL,
6 `avatarID` varchar(36) default NULL,
7 `inventoryName` varchar(64) default NULL,
8 `inventoryDescription` varchar(128) default NULL,
9 `inventoryNextPermissions` int(10) unsigned default NULL,
10 `inventoryCurrentPermissions` int(10) unsigned default NULL,
11 `invType` int(11) default NULL,
12 `creatorID` varchar(36) default NULL,
13 `inventoryBasePermissions` int(10) unsigned NOT NULL default 0,
14 `inventoryEveryOnePermissions` int(10) unsigned NOT NULL default 0,
15 PRIMARY KEY (`inventoryID`),
16 KEY `owner` (`avatarID`),
17 KEY `folder` (`parentFolderID`)
18) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 2';
diff --git a/OpenSim/Data/MySQL/Resources/CreateLogsTable.sql b/OpenSim/Data/MySQL/Resources/CreateLogsTable.sql
new file mode 100644
index 0000000..64b3a80
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/CreateLogsTable.sql
@@ -0,0 +1,10 @@
1CREATE TABLE `logs` (
2 `logID` int(10) unsigned NOT NULL auto_increment,
3 `target` varchar(36) default NULL,
4 `server` varchar(64) default NULL,
5 `method` varchar(64) default NULL,
6 `arguments` varchar(255) default NULL,
7 `priority` int(11) default NULL,
8 `message` text,
9 PRIMARY KEY (`logID`)
10) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 1';
diff --git a/OpenSim/Data/MySQL/Resources/CreateRegionsTable.sql b/OpenSim/Data/MySQL/Resources/CreateRegionsTable.sql
new file mode 100644
index 0000000..cb0f9bd
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/CreateRegionsTable.sql
@@ -0,0 +1,32 @@
1CREATE TABLE `regions` (
2 `uuid` varchar(36) NOT NULL,
3 `regionHandle` bigint(20) unsigned NOT NULL,
4 `regionName` varchar(32) default NULL,
5 `regionRecvKey` varchar(128) default NULL,
6 `regionSendKey` varchar(128) default NULL,
7 `regionSecret` varchar(128) default NULL,
8 `regionDataURI` varchar(255) default NULL,
9 `serverIP` varchar(64) default NULL,
10 `serverPort` int(10) unsigned default NULL,
11 `serverURI` varchar(255) default NULL,
12 `locX` int(10) unsigned default NULL,
13 `locY` int(10) unsigned default NULL,
14 `locZ` int(10) unsigned default NULL,
15 `eastOverrideHandle` bigint(20) unsigned default NULL,
16 `westOverrideHandle` bigint(20) unsigned default NULL,
17 `southOverrideHandle` bigint(20) unsigned default NULL,
18 `northOverrideHandle` bigint(20) unsigned default NULL,
19 `regionAssetURI` varchar(255) default NULL,
20 `regionAssetRecvKey` varchar(128) default NULL,
21 `regionAssetSendKey` varchar(128) default NULL,
22 `regionUserURI` varchar(255) default NULL,
23 `regionUserRecvKey` varchar(128) default NULL,
24 `regionUserSendKey` varchar(128) default NULL, `regionMapTexture` varchar(36) default NULL,
25 `serverHttpPort` int(10) default NULL, `serverRemotingPort` int(10) default NULL,
26 `owner_uuid` varchar(36) default '00000000-0000-0000-0000-000000000000' not null,
27 `originUUID` varchar(36),
28 PRIMARY KEY (`uuid`),
29 KEY `regionName` (`regionName`),
30 KEY `regionHandle` (`regionHandle`),
31 KEY `overrideHandles` (`eastOverrideHandle`,`westOverrideHandle`,`southOverrideHandle`,`northOverrideHandle`)
32) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Rev. 3';
diff --git a/OpenSim/Data/MySQL/Resources/CreateUserFriendsTable.sql b/OpenSim/Data/MySQL/Resources/CreateUserFriendsTable.sql
new file mode 100644
index 0000000..8480d48
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/CreateUserFriendsTable.sql
@@ -0,0 +1,11 @@
1SET FOREIGN_KEY_CHECKS=0;
2-- ----------------------------
3-- Table structure for users
4-- ----------------------------
5CREATE TABLE `userfriends` (
6 `ownerID` VARCHAR(37) NOT NULL,
7 `friendID` VARCHAR(37) NOT NULL,
8 `friendPerms` INT NOT NULL,
9 `datetimestamp` INT NOT NULL,
10 UNIQUE KEY (`ownerID`, `friendID`)
11) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev.1'; \ No newline at end of file
diff --git a/OpenSim/Data/MySQL/Resources/CreateUsersTable.sql b/OpenSim/Data/MySQL/Resources/CreateUsersTable.sql
new file mode 100644
index 0000000..d9e8ae2
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/CreateUsersTable.sql
@@ -0,0 +1,35 @@
1SET FOREIGN_KEY_CHECKS=0;
2-- ----------------------------
3-- Table structure for users
4-- ----------------------------
5CREATE TABLE `users` (
6 `UUID` varchar(36) NOT NULL default '',
7 `username` varchar(32) NOT NULL,
8 `lastname` varchar(32) NOT NULL,
9 `passwordHash` varchar(32) NOT NULL,
10 `passwordSalt` varchar(32) NOT NULL,
11 `homeRegion` bigint(20) unsigned default NULL,
12 `homeLocationX` float default NULL,
13 `homeLocationY` float default NULL,
14 `homeLocationZ` float default NULL,
15 `homeLookAtX` float default NULL,
16 `homeLookAtY` float default NULL,
17 `homeLookAtZ` float default NULL,
18 `created` int(11) NOT NULL,
19 `lastLogin` int(11) NOT NULL,
20 `userInventoryURI` varchar(255) default NULL,
21 `userAssetURI` varchar(255) default NULL,
22 `profileCanDoMask` int(10) unsigned default NULL,
23 `profileWantDoMask` int(10) unsigned default NULL,
24 `profileAboutText` text,
25 `profileFirstText` text,
26 `profileImage` varchar(36) default NULL,
27 `profileFirstImage` varchar(36) default NULL,
28 `webLoginKey` varchar(36) default NULL,
29 PRIMARY KEY (`UUID`),
30 UNIQUE KEY `usernames` (`username`,`lastname`)
31) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Rev. 2';
32
33-- ----------------------------
34-- Records
35-- ----------------------------
diff --git a/OpenSim/Data/MySQL/Resources/UpgradeFoldersTableToVersion2.sql b/OpenSim/Data/MySQL/Resources/UpgradeFoldersTableToVersion2.sql
new file mode 100644
index 0000000..b5a7964
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/UpgradeFoldersTableToVersion2.sql
@@ -0,0 +1,4 @@
1ALTER TABLE `inventoryfolders`
2 ADD COLUMN `type` smallint NOT NULL default 0,
3 ADD COLUMN `version` int NOT NULL default 0,
4COMMENT='Rev. 2';
diff --git a/OpenSim/Data/MySQL/Resources/UpgradeItemsTableToVersion2.sql b/OpenSim/Data/MySQL/Resources/UpgradeItemsTableToVersion2.sql
new file mode 100644
index 0000000..d1ef504
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/UpgradeItemsTableToVersion2.sql
@@ -0,0 +1,9 @@
1ALTER TABLE `inventoryitems`
2 CHANGE COLUMN `type` `assetType` int(11) default NULL,
3 ADD COLUMN `invType` int(11) default NULL,
4 ADD COLUMN `creatorID` varchar(36) default NULL,
5 ADD COLUMN `inventoryBasePermissions` int(10) unsigned NOT NULL default 0,
6 ADD COLUMN `inventoryEveryOnePermissions` int(10) unsigned NOT NULL default 0,
7COMMENT='Rev. 2';
8
9UPDATE `inventoryitems` SET invType=assetType;
diff --git a/OpenSim/Data/MySQL/Resources/UpgradeRegionsTableToVersion2.sql b/OpenSim/Data/MySQL/Resources/UpgradeRegionsTableToVersion2.sql
new file mode 100644
index 0000000..034b755
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/UpgradeRegionsTableToVersion2.sql
@@ -0,0 +1,4 @@
1ALTER TABLE `regions`
2 ADD COLUMN `originUUID` varchar(36),
3COMMENT='Rev. 2';
4UPDATE `regions` SET originUUID=uuid;
diff --git a/OpenSim/Data/MySQL/Resources/UpgradeRegionsTableToVersion3.sql b/OpenSim/Data/MySQL/Resources/UpgradeRegionsTableToVersion3.sql
new file mode 100644
index 0000000..b48afec
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/UpgradeRegionsTableToVersion3.sql
@@ -0,0 +1,18 @@
1DROP PROCEDURE IF EXISTS upgraderegions3;
2
3create procedure upgraderegions3()
4BEGIN
5DECLARE db_name varchar(64);
6select database() into db_name;
7IF ((select count(*) from information_schema.columns where table_name='regions' and column_name='owner_uuid' and table_schema=db_name) > 0)
8THEN
9 ALTER TABLE `regions`, COMMENT='Rev. 3';
10ELSE
11 ALTER TABLE `regions`
12 ADD COLUMN `owner_uuid` varchar(36) default '00000000-0000-0000-0000-000000000000' not null after serverRemotingPort, COMMENT='Rev. 3';
13END IF;
14END;
15
16call upgraderegions3();
17
18
diff --git a/OpenSim/Data/MySQL/Resources/UpgradeUsersTableToVersion2.sql b/OpenSim/Data/MySQL/Resources/UpgradeUsersTableToVersion2.sql
new file mode 100644
index 0000000..dd21a66
--- /dev/null
+++ b/OpenSim/Data/MySQL/Resources/UpgradeUsersTableToVersion2.sql
@@ -0,0 +1,3 @@
1ALTER TABLE `users`
2 ADD COLUMN `webLoginKey` varchar(36) default '00000000-0000-0000-0000-000000000000' NOT NULL,
3COMMENT='Rev. 2'; \ No newline at end of file
generated by cgit v1.1 at 2024-11-10 16:52:12 +0000