aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/MSSQL/MSSQLDataStore.cs
diff options
context:
space:
mode:
authorSean Dague2008-04-02 15:24:31 +0000
committerSean Dague2008-04-02 15:24:31 +0000
commitc52c68f314c67c76c7181a6d0828f476290fbd66 (patch)
tree66ab347502892902a096fa985f31b25738eb1381 /OpenSim/Data/MSSQL/MSSQLDataStore.cs
parentreorganizing namespaces to put all the Data stuff into it's own namespace (diff)
downloadopensim-SC_OLD-c52c68f314c67c76c7181a6d0828f476290fbd66.zip
opensim-SC_OLD-c52c68f314c67c76c7181a6d0828f476290fbd66.tar.gz
opensim-SC_OLD-c52c68f314c67c76c7181a6d0828f476290fbd66.tar.bz2
opensim-SC_OLD-c52c68f314c67c76c7181a6d0828f476290fbd66.tar.xz
whole lot more moving
Diffstat (limited to 'OpenSim/Data/MSSQL/MSSQLDataStore.cs')
-rw-r--r--OpenSim/Data/MSSQL/MSSQLDataStore.cs1622
1 files changed, 1622 insertions, 0 deletions
diff --git a/OpenSim/Data/MSSQL/MSSQLDataStore.cs b/OpenSim/Data/MSSQL/MSSQLDataStore.cs
new file mode 100644
index 0000000..d34abe3
--- /dev/null
+++ b/OpenSim/Data/MSSQL/MSSQLDataStore.cs
@@ -0,0 +1,1622 @@
1/*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSim Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27
28using System;
29using System.Collections.Generic;
30using System.Data;
31using System.Data.SqlClient;
32using System.IO;
33using libsecondlife;
34using OpenSim.Framework;
35using OpenSim.Framework.Console;
36using OpenSim.Framework.Data;
37using OpenSim.Region.Environment.Interfaces;
38using OpenSim.Region.Environment.Scenes;
39using OpenSim.Framework.Data.MSSQL;
40
41namespace OpenSim.Framework.Data.MSSQL
42{
43 public class MSSQLDataStore : IRegionDataStore
44 {
45 // private static FileSystemDataStore Instance = new FileSystemDataStore();
46 private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
47
48 private const string m_primSelect = "select * from prims";
49 private const string m_shapeSelect = "select * from primshapes";
50 private const string m_itemsSelect = "select * from primitems";
51 private const string m_terrainSelect = "select top 1 * from terrain";
52 private const string m_landSelect = "select * from land";
53 private const string m_landAccessListSelect = "select * from landaccesslist";
54
55 private DataSet m_dataSet;
56 private SqlDataAdapter m_primDataAdapter;
57 private SqlDataAdapter m_shapeDataAdapter;
58 private SqlDataAdapter m_itemsDataAdapter;
59 private SqlConnection m_connection;
60 private SqlDataAdapter m_terrainDataAdapter;
61 private SqlDataAdapter m_landDataAdapter;
62 private SqlDataAdapter m_landAccessListDataAdapter;
63
64 private DataTable m_primTable;
65 private DataTable m_shapeTable;
66 private DataTable m_itemsTable;
67 private DataTable m_terrainTable;
68 private DataTable m_landTable;
69 private DataTable m_landAccessListTable;
70
71 // Temporary attribute while this is experimental
72 private bool persistPrimInventories;
73
74 /***********************************************************************
75 *
76 * Public Interface Functions
77 *
78 **********************************************************************/
79
80 // see IRegionDataStore
81 public void Initialise(string connectionString, bool persistPrimInventories)
82 {
83 // Instance.Initialise("", true);
84
85 m_dataSet = new DataSet();
86 this.persistPrimInventories = persistPrimInventories;
87
88 m_log.Info("[DATASTORE]: MSSql - connecting: " + connectionString);
89 m_connection = new SqlConnection(connectionString);
90
91 SqlCommand primSelectCmd = new SqlCommand(m_primSelect, m_connection);
92 m_primDataAdapter = new SqlDataAdapter(primSelectCmd);
93
94 SqlCommand shapeSelectCmd = new SqlCommand(m_shapeSelect, m_connection);
95 m_shapeDataAdapter = new SqlDataAdapter(shapeSelectCmd);
96
97 SqlCommand itemsSelectCmd = new SqlCommand(m_itemsSelect, m_connection);
98 m_itemsDataAdapter = new SqlDataAdapter(itemsSelectCmd);
99
100 SqlCommand terrainSelectCmd = new SqlCommand(m_terrainSelect, m_connection);
101 m_terrainDataAdapter = new SqlDataAdapter(terrainSelectCmd);
102
103 SqlCommand landSelectCmd = new SqlCommand(m_landSelect, m_connection);
104 m_landDataAdapter = new SqlDataAdapter(landSelectCmd);
105
106 SqlCommand landAccessListSelectCmd = new SqlCommand(m_landAccessListSelect, m_connection);
107 m_landAccessListDataAdapter = new SqlDataAdapter(landAccessListSelectCmd);
108
109 TestTables(m_connection);
110
111 lock (m_dataSet)
112 {
113 m_primTable = createPrimTable();
114 m_dataSet.Tables.Add(m_primTable);
115 setupPrimCommands(m_primDataAdapter, m_connection);
116 m_primDataAdapter.Fill(m_primTable);
117
118 m_shapeTable = createShapeTable();
119 m_dataSet.Tables.Add(m_shapeTable);
120 setupShapeCommands(m_shapeDataAdapter, m_connection);
121 m_shapeDataAdapter.Fill(m_shapeTable);
122
123 if (persistPrimInventories)
124 {
125 m_itemsTable = createItemsTable();
126 m_dataSet.Tables.Add(m_itemsTable);
127 SetupItemsCommands(m_itemsDataAdapter, m_connection);
128 m_itemsDataAdapter.Fill(m_itemsTable);
129 }
130
131 m_terrainTable = createTerrainTable();
132 m_dataSet.Tables.Add(m_terrainTable);
133 setupTerrainCommands(m_terrainDataAdapter, m_connection);
134 m_terrainDataAdapter.Fill(m_terrainTable);
135
136 m_landTable = createLandTable();
137 m_dataSet.Tables.Add(m_landTable);
138 setupLandCommands(m_landDataAdapter, m_connection);
139 m_landDataAdapter.Fill(m_landTable);
140
141 m_landAccessListTable = createLandAccessListTable();
142 m_dataSet.Tables.Add(m_landAccessListTable);
143 setupLandAccessCommands(m_landAccessListDataAdapter, m_connection);
144 m_landAccessListDataAdapter.Fill(m_landAccessListTable);
145 }
146 }
147
148 public void StoreObject(SceneObjectGroup obj, LLUUID regionUUID)
149 {
150 // Instance.StoreObject(obj, regionUUID);
151
152 lock (m_dataSet)
153 {
154 foreach (SceneObjectPart prim in obj.Children.Values)
155 {
156 if ((prim.ObjectFlags & (uint)LLObject.ObjectFlags.Physics) == 0)
157 {
158 m_log.Info("[DATASTORE]: Adding obj: " + obj.UUID + " to region: " + regionUUID);
159 addPrim(prim, obj.UUID, regionUUID);
160 }
161 else
162 {
163 // m_log.Info("[DATASTORE]: Ignoring Physical obj: " + obj.UUID + " in region: " + regionUUID);
164 }
165 }
166 }
167
168 Commit();
169 }
170
171 public void RemoveObject(LLUUID obj, LLUUID regionUUID)
172 {
173 // Instance.RemoveObject(obj, regionUUID);
174
175 m_log.InfoFormat("[DATASTORE]: Removing obj: {0} from region: {1}", obj.UUID, regionUUID);
176
177 DataTable prims = m_primTable;
178 DataTable shapes = m_shapeTable;
179
180 string selectExp = "SceneGroupID = '" + obj.ToString() + "'";
181 lock (m_dataSet)
182 {
183 foreach (DataRow row in prims.Select(selectExp))
184 {
185 // Remove shapes row
186 LLUUID uuid = new LLUUID((string)row["UUID"]);
187
188 DataRow shapeRow = shapes.Rows.Find(uuid.UUID);
189 if (shapeRow != null)
190 {
191 shapeRow.Delete();
192 }
193
194 if (persistPrimInventories)
195 {
196 RemoveItems(new LLUUID((string)row["UUID"]));
197 }
198
199 // Remove prim row
200 row.Delete();
201 }
202 }
203
204 Commit();
205 }
206
207 /// <summary>
208 /// Remove all persisted items of the given prim.
209 /// The caller must acquire the necessrary synchronization locks and commit or rollback changes.
210 /// </summary>
211 private void RemoveItems(LLUUID uuid)
212 {
213 String sql = String.Format("primID = '{0}'", uuid);
214 DataRow[] itemRows = m_itemsTable.Select(sql);
215
216 foreach (DataRow itemRow in itemRows)
217 {
218 itemRow.Delete();
219 }
220 }
221
222 /// <summary>
223 /// Load persisted objects from region storage.
224 /// </summary>
225 public List<SceneObjectGroup> LoadObjects(LLUUID regionUUID)
226 {
227 // return Instance.LoadObjects(regionUUID);
228
229 Dictionary<LLUUID, SceneObjectGroup> createdObjects = new Dictionary<LLUUID, SceneObjectGroup>();
230
231 List<SceneObjectGroup> retvals = new List<SceneObjectGroup>();
232
233 DataTable prims = m_primTable;
234 DataTable shapes = m_shapeTable;
235
236 string byRegion = "RegionUUID = '" + regionUUID.ToString() + "'";
237 string orderByParent = "ParentID ASC";
238
239 lock (m_dataSet)
240 {
241 DataRow[] primsForRegion = prims.Select(byRegion, orderByParent);
242 m_log.Info("[DATASTORE]: " +
243 "Loaded " + primsForRegion.Length + " prims for region: " + regionUUID);
244
245 foreach (DataRow primRow in primsForRegion)
246 {
247 try
248 {
249 string uuid = (string)primRow["UUID"];
250 string objID = (string)primRow["SceneGroupID"];
251
252 SceneObjectPart prim = buildPrim(primRow);
253
254 if (uuid == objID) //is new SceneObjectGroup ?
255 {
256 SceneObjectGroup group = new SceneObjectGroup();
257
258 DataRow shapeRow = shapes.Rows.Find(prim.UUID);
259 if (shapeRow != null)
260 {
261 prim.Shape = buildShape(shapeRow);
262 }
263 else
264 {
265 m_log.Info(
266 "No shape found for prim in storage, so setting default box shape");
267 prim.Shape = PrimitiveBaseShape.Default;
268 }
269 group.AddPart(prim);
270 group.RootPart = prim;
271
272 createdObjects.Add(group.UUID, group);
273 retvals.Add(group);
274 }
275 else
276 {
277 DataRow shapeRow = shapes.Rows.Find(prim.UUID);
278 if (shapeRow != null)
279 {
280 prim.Shape = buildShape(shapeRow);
281 }
282 else
283 {
284 m_log.Info(
285 "No shape found for prim in storage, so setting default box shape");
286 prim.Shape = PrimitiveBaseShape.Default;
287 }
288 createdObjects[new LLUUID(objID)].AddPart(prim);
289 }
290
291 if (persistPrimInventories)
292 {
293 LoadItems(prim);
294 }
295 }
296 catch (Exception e)
297 {
298 m_log.Error("[DATASTORE]: Failed create prim object, exception and data follows");
299 m_log.Info("[DATASTORE]: " + e.ToString());
300 foreach (DataColumn col in prims.Columns)
301 {
302 m_log.Info("[DATASTORE]: Col: " + col.ColumnName + " => " + primRow[col]);
303 }
304 }
305 }
306 }
307 return retvals;
308 }
309
310 /// <summary>
311 /// Load in a prim's persisted inventory.
312 /// </summary>
313 /// <param name="prim"></param>
314 private void LoadItems(SceneObjectPart prim)
315 {
316 //m_log.InfoFormat("[DATASTORE]: Loading inventory for {0}, {1}", prim.Name, prim.UUID);
317
318 DataTable dbItems = m_itemsTable;
319
320 String sql = String.Format("primID = '{0}'", prim.UUID.ToString());
321 DataRow[] dbItemRows = dbItems.Select(sql);
322
323 IList<TaskInventoryItem> inventory = new List<TaskInventoryItem>();
324
325 foreach (DataRow row in dbItemRows)
326 {
327 TaskInventoryItem item = buildItem(row);
328 inventory.Add(item);
329
330 //m_log.DebugFormat("[DATASTORE]: Restored item {0}, {1}", item.Name, item.ItemID);
331 }
332
333 prim.RestoreInventoryItems(inventory);
334
335 // XXX A nasty little hack to recover the folder id for the prim (which is currently stored in
336 // every item). This data should really be stored in the prim table itself.
337 if (dbItemRows.Length > 0)
338 {
339 prim.FolderID = inventory[0].ParentID;
340 }
341 }
342
343 public void StoreTerrain(double[,] ter, LLUUID regionID)
344 {
345 int revision = Util.UnixTimeSinceEpoch();
346 m_log.Info("[DATASTORE]: Storing terrain revision r" + revision.ToString());
347
348 DataTable terrain = m_dataSet.Tables["terrain"];
349 lock (m_dataSet)
350 {
351 SqlCommand cmd = new SqlCommand("insert into terrain(RegionUUID, Revision, Heightfield)" +
352 " values(@RegionUUID, @Revision, @Heightfield)", m_connection);
353 using (cmd)
354 {
355 cmd.Parameters.Add(new SqlParameter("@RegionUUID", regionID.UUID));
356 cmd.Parameters.Add(new SqlParameter("@Revision", revision));
357 cmd.Parameters.Add(new SqlParameter("@Heightfield", serializeTerrain(ter)));
358 cmd.ExecuteNonQuery();
359 }
360 }
361 }
362
363 public double[,] LoadTerrain(LLUUID regionID)
364 {
365 double[,] terret = new double[256, 256];
366 terret.Initialize();
367
368 SqlCommand cmd = new SqlCommand(
369 @"select top 1 RegionUUID, Revision, Heightfield from terrain
370 where RegionUUID=@RegionUUID order by Revision desc"
371 , m_connection);
372
373 SqlParameter param = new SqlParameter();
374 cmd.Parameters.Add(new SqlParameter("@RegionUUID", regionID.UUID));
375
376 if (m_connection.State != ConnectionState.Open)
377 {
378 m_connection.Open();
379 }
380
381 using (SqlDataReader row = cmd.ExecuteReader())
382 {
383 int rev = 0;
384 if (row.Read())
385 {
386 MemoryStream str = new MemoryStream((byte[])row["Heightfield"]);
387 BinaryReader br = new BinaryReader(str);
388 for (int x = 0; x < 256; x++)
389 {
390 for (int y = 0; y < 256; y++)
391 {
392 terret[x, y] = br.ReadDouble();
393 }
394 }
395 rev = (int)row["Revision"];
396 }
397 else
398 {
399 m_log.Info("[DATASTORE]: No terrain found for region");
400 return null;
401 }
402
403 m_log.Info("[DATASTORE]: Loaded terrain revision r" + rev.ToString());
404 }
405
406 return terret;
407 }
408
409 public void RemoveLandObject(LLUUID globalID)
410 {
411 // Instance.RemoveLandObject(globalID);
412
413 lock (m_dataSet)
414 {
415 using (SqlCommand cmd = new SqlCommand("delete from land where UUID=@UUID", m_connection))
416 {
417 cmd.Parameters.Add(new SqlParameter("@UUID", globalID.UUID));
418 cmd.ExecuteNonQuery();
419 }
420
421 using (
422 SqlCommand cmd = new SqlCommand("delete from landaccesslist where LandUUID=@UUID", m_connection)
423 )
424 {
425 cmd.Parameters.Add(new SqlParameter("@UUID", globalID.UUID));
426 cmd.ExecuteNonQuery();
427 }
428 }
429 }
430
431 public void StoreLandObject(ILandObject parcel)
432 {
433 // Instance.StoreLandObject(parcel, regionUUID);
434
435 // Does the new locking fix it?
436 // m_log.Info("[DATASTORE]: Tedds temp fix: Waiting 3 seconds to avoid others writing to table while we hold a dataset of it. (Someone please fix! :))");
437 // System.Threading.Thread.Sleep(2500 + rnd.Next(0, 1000));
438
439 lock (m_dataSet)
440 {
441 DataTable land = m_landTable;
442 DataTable landaccesslist = m_landAccessListTable;
443
444 DataRow landRow = land.Rows.Find(parcel.landData.globalID.UUID);
445 if (landRow == null)
446 {
447 landRow = land.NewRow();
448 fillLandRow(landRow, parcel.landData, parcel.regionUUID);
449 land.Rows.Add(landRow);
450 }
451 else
452 {
453 fillLandRow(landRow, parcel.landData, parcel.regionUUID);
454 }
455
456 using (
457 SqlCommand cmd =
458 new SqlCommand("delete from landaccesslist where LandUUID=@LandUUID", m_connection))
459 {
460 cmd.Parameters.Add(new SqlParameter("@LandUUID", parcel.landData.globalID.UUID));
461 cmd.ExecuteNonQuery();
462 }
463
464 foreach (ParcelManager.ParcelAccessEntry entry in parcel.landData.parcelAccessList)
465 {
466 DataRow newAccessRow = landaccesslist.NewRow();
467 fillLandAccessRow(newAccessRow, entry, parcel.landData.globalID);
468 landaccesslist.Rows.Add(newAccessRow);
469 }
470
471 }
472 Commit();
473 }
474
475 public List<LandData> LoadLandObjects(LLUUID regionUUID)
476 {
477 List<LandData> landDataForRegion = new List<LandData>();
478 lock (m_dataSet)
479 {
480 DataTable land = m_landTable;
481 DataTable landaccesslist = m_landAccessListTable;
482 string searchExp = "RegionUUID = '" + regionUUID.UUID + "'";
483 DataRow[] rawDataForRegion = land.Select(searchExp);
484 foreach (DataRow rawDataLand in rawDataForRegion)
485 {
486 LandData newLand = buildLandData(rawDataLand);
487 string accessListSearchExp = "LandUUID = '" + newLand.globalID.UUID + "'";
488 DataRow[] rawDataForLandAccessList = landaccesslist.Select(accessListSearchExp);
489 foreach (DataRow rawDataLandAccess in rawDataForLandAccessList)
490 {
491 newLand.parcelAccessList.Add(buildLandAccessData(rawDataLandAccess));
492 }
493
494 landDataForRegion.Add(newLand);
495 }
496 }
497 return landDataForRegion;
498 }
499
500 public void Commit()
501 {
502 if (m_connection.State != ConnectionState.Open)
503 {
504 m_connection.Open();
505 }
506
507 lock (m_dataSet)
508 {
509 // DisplayDataSet(m_dataSet, "Region DataSet");
510
511 m_primDataAdapter.Update(m_primTable);
512 m_shapeDataAdapter.Update(m_shapeTable);
513
514 if (persistPrimInventories)
515 {
516 m_itemsDataAdapter.Update(m_itemsTable);
517 }
518
519 m_terrainDataAdapter.Update(m_terrainTable);
520 m_landDataAdapter.Update(m_landTable);
521 m_landAccessListDataAdapter.Update(m_landAccessListTable);
522
523 m_dataSet.AcceptChanges();
524 }
525 }
526
527 public void Shutdown()
528 {
529 Commit();
530 }
531
532 /***********************************************************************
533 *
534 * Database Definition Functions
535 *
536 * This should be db agnostic as we define them in ADO.NET terms
537 *
538 **********************************************************************/
539
540 private DataColumn createCol(DataTable dt, string name, Type type)
541 {
542 DataColumn col = new DataColumn(name, type);
543 dt.Columns.Add(col);
544 return col;
545 }
546
547 private DataTable createTerrainTable()
548 {
549 DataTable terrain = new DataTable("terrain");
550
551 createCol(terrain, "RegionUUID", typeof(String));
552 createCol(terrain, "Revision", typeof(Int32));
553 createCol(terrain, "Heightfield", typeof(Byte[]));
554
555 return terrain;
556 }
557
558 private DataTable createPrimTable()
559 {
560 DataTable prims = new DataTable("prims");
561
562 createCol(prims, "UUID", typeof(String));
563 createCol(prims, "RegionUUID", typeof(String));
564 createCol(prims, "ParentID", typeof(Int32));
565 createCol(prims, "CreationDate", typeof(Int32));
566 createCol(prims, "Name", typeof(String));
567 createCol(prims, "SceneGroupID", typeof(String));
568 // various text fields
569 createCol(prims, "Text", typeof(String));
570 createCol(prims, "Description", typeof(String));
571 createCol(prims, "SitName", typeof(String));
572 createCol(prims, "TouchName", typeof(String));
573 // permissions
574 createCol(prims, "ObjectFlags", typeof(Int32));
575 createCol(prims, "CreatorID", typeof(String));
576 createCol(prims, "OwnerID", typeof(String));
577 createCol(prims, "GroupID", typeof(String));
578 createCol(prims, "LastOwnerID", typeof(String));
579 createCol(prims, "OwnerMask", typeof(Int32));
580 createCol(prims, "NextOwnerMask", typeof(Int32));
581 createCol(prims, "GroupMask", typeof(Int32));
582 createCol(prims, "EveryoneMask", typeof(Int32));
583 createCol(prims, "BaseMask", typeof(Int32));
584 // vectors
585 createCol(prims, "PositionX", typeof(Double));
586 createCol(prims, "PositionY", typeof(Double));
587 createCol(prims, "PositionZ", typeof(Double));
588 createCol(prims, "GroupPositionX", typeof(Double));
589 createCol(prims, "GroupPositionY", typeof(Double));
590 createCol(prims, "GroupPositionZ", typeof(Double));
591 createCol(prims, "VelocityX", typeof(Double));
592 createCol(prims, "VelocityY", typeof(Double));
593 createCol(prims, "VelocityZ", typeof(Double));
594 createCol(prims, "AngularVelocityX", typeof(Double));
595 createCol(prims, "AngularVelocityY", typeof(Double));
596 createCol(prims, "AngularVelocityZ", typeof(Double));
597 createCol(prims, "AccelerationX", typeof(Double));
598 createCol(prims, "AccelerationY", typeof(Double));
599 createCol(prims, "AccelerationZ", typeof(Double));
600 // quaternions
601 createCol(prims, "RotationX", typeof(Double));
602 createCol(prims, "RotationY", typeof(Double));
603 createCol(prims, "RotationZ", typeof(Double));
604 createCol(prims, "RotationW", typeof(Double));
605
606 // sit target
607 createCol(prims, "SitTargetOffsetX", typeof(Double));
608 createCol(prims, "SitTargetOffsetY", typeof(Double));
609 createCol(prims, "SitTargetOffsetZ", typeof(Double));
610
611 createCol(prims, "SitTargetOrientW", typeof(Double));
612 createCol(prims, "SitTargetOrientX", typeof(Double));
613 createCol(prims, "SitTargetOrientY", typeof(Double));
614 createCol(prims, "SitTargetOrientZ", typeof(Double));
615
616 // Add in contraints
617 prims.PrimaryKey = new DataColumn[] { prims.Columns["UUID"] };
618
619 return prims;
620 }
621
622 private DataTable createLandTable()
623 {
624 DataTable land = new DataTable("land");
625 createCol(land, "UUID", typeof(String));
626 createCol(land, "RegionUUID", typeof(String));
627 createCol(land, "LocalLandID", typeof(Int32));
628
629 // Bitmap is a byte[512]
630 createCol(land, "Bitmap", typeof(Byte[]));
631
632 createCol(land, "Name", typeof(String));
633 createCol(land, "Description", typeof(String));
634 createCol(land, "OwnerUUID", typeof(String));
635 createCol(land, "IsGroupOwned", typeof(Int32));
636 createCol(land, "Area", typeof(Int32));
637 createCol(land, "AuctionID", typeof(Int32)); //Unemplemented
638 createCol(land, "Category", typeof(Int32)); //Enum libsecondlife.Parcel.ParcelCategory
639 createCol(land, "ClaimDate", typeof(Int32));
640 createCol(land, "ClaimPrice", typeof(Int32));
641 createCol(land, "GroupUUID", typeof(String));
642 createCol(land, "SalePrice", typeof(Int32));
643 createCol(land, "LandStatus", typeof(Int32)); //Enum. libsecondlife.Parcel.ParcelStatus
644 createCol(land, "LandFlags", typeof(Int32));
645 createCol(land, "LandingType", typeof(Int32));
646 createCol(land, "MediaAutoScale", typeof(Int32));
647 createCol(land, "MediaTextureUUID", typeof(String));
648 createCol(land, "MediaURL", typeof(String));
649 createCol(land, "MusicURL", typeof(String));
650 createCol(land, "PassHours", typeof(Double));
651 createCol(land, "PassPrice", typeof(Int32));
652 createCol(land, "SnapshotUUID", typeof(String));
653 createCol(land, "UserLocationX", typeof(Double));
654 createCol(land, "UserLocationY", typeof(Double));
655 createCol(land, "UserLocationZ", typeof(Double));
656 createCol(land, "UserLookAtX", typeof(Double));
657 createCol(land, "UserLookAtY", typeof(Double));
658 createCol(land, "UserLookAtZ", typeof(Double));
659
660 land.PrimaryKey = new DataColumn[] { land.Columns["UUID"] };
661
662 return land;
663 }
664
665 private DataTable createLandAccessListTable()
666 {
667 DataTable landaccess = new DataTable("landaccesslist");
668 createCol(landaccess, "LandUUID", typeof(String));
669 createCol(landaccess, "AccessUUID", typeof(String));
670 createCol(landaccess, "Flags", typeof(Int32));
671
672 return landaccess;
673 }
674
675 private DataTable createShapeTable()
676 {
677 DataTable shapes = new DataTable("primshapes");
678 createCol(shapes, "UUID", typeof(String));
679 // shape is an enum
680 createCol(shapes, "Shape", typeof(Int32));
681 // vectors
682 createCol(shapes, "ScaleX", typeof(Double));
683 createCol(shapes, "ScaleY", typeof(Double));
684 createCol(shapes, "ScaleZ", typeof(Double));
685 // paths
686 createCol(shapes, "PCode", typeof(Int32));
687 createCol(shapes, "PathBegin", typeof(Int32));
688 createCol(shapes, "PathEnd", typeof(Int32));
689 createCol(shapes, "PathScaleX", typeof(Int32));
690 createCol(shapes, "PathScaleY", typeof(Int32));
691 createCol(shapes, "PathShearX", typeof(Int32));
692 createCol(shapes, "PathShearY", typeof(Int32));
693 createCol(shapes, "PathSkew", typeof(Int32));
694 createCol(shapes, "PathCurve", typeof(Int32));
695 createCol(shapes, "PathRadiusOffset", typeof(Int32));
696 createCol(shapes, "PathRevolutions", typeof(Int32));
697 createCol(shapes, "PathTaperX", typeof(Int32));
698 createCol(shapes, "PathTaperY", typeof(Int32));
699 createCol(shapes, "PathTwist", typeof(Int32));
700 createCol(shapes, "PathTwistBegin", typeof(Int32));
701 // profile
702 createCol(shapes, "ProfileBegin", typeof(Int32));
703 createCol(shapes, "ProfileEnd", typeof(Int32));
704 createCol(shapes, "ProfileCurve", typeof(Int32));
705 createCol(shapes, "ProfileHollow", typeof(Int32));
706 createCol(shapes, "State", typeof(Int32));
707 // text TODO: this isn't right, but I'm not sure the right
708 // way to specify this as a blob atm
709 createCol(shapes, "Texture", typeof(Byte[]));
710 createCol(shapes, "ExtraParams", typeof(Byte[]));
711
712 shapes.PrimaryKey = new DataColumn[] { shapes.Columns["UUID"] };
713
714 return shapes;
715 }
716
717 private DataTable createItemsTable()
718 {
719 DataTable items = new DataTable("primitems");
720
721 createCol(items, "itemID", typeof(String));
722 createCol(items, "primID", typeof(String));
723 createCol(items, "assetID", typeof(String));
724 createCol(items, "parentFolderID", typeof(String));
725
726 createCol(items, "invType", typeof(Int32));
727 createCol(items, "assetType", typeof(Int32));
728
729 createCol(items, "name", typeof(String));
730 createCol(items, "description", typeof(String));
731
732 createCol(items, "creationDate", typeof(Int64));
733 createCol(items, "creatorID", typeof(String));
734 createCol(items, "ownerID", typeof(String));
735 createCol(items, "lastOwnerID", typeof(String));
736 createCol(items, "groupID", typeof(String));
737
738 createCol(items, "nextPermissions", typeof(Int32));
739 createCol(items, "currentPermissions", typeof(Int32));
740 createCol(items, "basePermissions", typeof(Int32));
741 createCol(items, "everyonePermissions", typeof(Int32));
742 createCol(items, "groupPermissions", typeof(Int32));
743
744 items.PrimaryKey = new DataColumn[] { items.Columns["itemID"] };
745
746 return items;
747 }
748
749 /***********************************************************************
750 *
751 * Convert between ADO.NET <=> OpenSim Objects
752 *
753 * These should be database independant
754 *
755 **********************************************************************/
756
757 private SceneObjectPart buildPrim(DataRow row)
758 {
759 SceneObjectPart prim = new SceneObjectPart();
760 prim.UUID = new LLUUID((String)row["UUID"]);
761 // explicit conversion of integers is required, which sort
762 // of sucks. No idea if there is a shortcut here or not.
763 prim.ParentID = Convert.ToUInt32(row["ParentID"]);
764 prim.CreationDate = Convert.ToInt32(row["CreationDate"]);
765 prim.Name = (String)row["Name"];
766 // various text fields
767 prim.Text = (String)row["Text"];
768 prim.Description = (String)row["Description"];
769 prim.SitName = (String)row["SitName"];
770 prim.TouchName = (String)row["TouchName"];
771 // permissions
772 prim.ObjectFlags = Convert.ToUInt32(row["ObjectFlags"]);
773 prim.CreatorID = new LLUUID((String)row["CreatorID"]);
774 prim.OwnerID = new LLUUID((String)row["OwnerID"]);
775 prim.GroupID = new LLUUID((String)row["GroupID"]);
776 prim.LastOwnerID = new LLUUID((String)row["LastOwnerID"]);
777 prim.OwnerMask = Convert.ToUInt32(row["OwnerMask"]);
778 prim.NextOwnerMask = Convert.ToUInt32(row["NextOwnerMask"]);
779 prim.GroupMask = Convert.ToUInt32(row["GroupMask"]);
780 prim.EveryoneMask = Convert.ToUInt32(row["EveryoneMask"]);
781 prim.BaseMask = Convert.ToUInt32(row["BaseMask"]);
782 // vectors
783 prim.OffsetPosition = new LLVector3(
784 Convert.ToSingle(row["PositionX"]),
785 Convert.ToSingle(row["PositionY"]),
786 Convert.ToSingle(row["PositionZ"])
787 );
788 prim.GroupPosition = new LLVector3(
789 Convert.ToSingle(row["GroupPositionX"]),
790 Convert.ToSingle(row["GroupPositionY"]),
791 Convert.ToSingle(row["GroupPositionZ"])
792 );
793 prim.Velocity = new LLVector3(
794 Convert.ToSingle(row["VelocityX"]),
795 Convert.ToSingle(row["VelocityY"]),
796 Convert.ToSingle(row["VelocityZ"])
797 );
798 prim.AngularVelocity = new LLVector3(
799 Convert.ToSingle(row["AngularVelocityX"]),
800 Convert.ToSingle(row["AngularVelocityY"]),
801 Convert.ToSingle(row["AngularVelocityZ"])
802 );
803 prim.Acceleration = new LLVector3(
804 Convert.ToSingle(row["AccelerationX"]),
805 Convert.ToSingle(row["AccelerationY"]),
806 Convert.ToSingle(row["AccelerationZ"])
807 );
808 // quaternions
809 prim.RotationOffset = new LLQuaternion(
810 Convert.ToSingle(row["RotationX"]),
811 Convert.ToSingle(row["RotationY"]),
812 Convert.ToSingle(row["RotationZ"]),
813 Convert.ToSingle(row["RotationW"])
814 );
815 try
816 {
817 prim.SetSitTargetLL(new LLVector3(
818 Convert.ToSingle(row["SitTargetOffsetX"]),
819 Convert.ToSingle(row["SitTargetOffsetY"]),
820 Convert.ToSingle(row["SitTargetOffsetZ"])), new LLQuaternion(
821 Convert.ToSingle(
822 row["SitTargetOrientX"]),
823 Convert.ToSingle(
824 row["SitTargetOrientY"]),
825 Convert.ToSingle(
826 row["SitTargetOrientZ"]),
827 Convert.ToSingle(
828 row["SitTargetOrientW"])));
829 }
830 catch (InvalidCastException)
831 {
832 // Database table was created before we got here and now has null values :P
833
834 using (
835 SqlCommand cmd =
836 new SqlCommand(
837 "ALTER TABLE [prims] ADD COLUMN [SitTargetOffsetX] float NOT NULL default 0, ADD COLUMN [SitTargetOffsetY] float NOT NULL default 0, ADD COLUMN [SitTargetOffsetZ] float NOT NULL default 0, ADD COLUMN [SitTargetOrientW] float NOT NULL default 0, ADD COLUMN [SitTargetOrientX] float NOT NULL default 0, ADD COLUMN [SitTargetOrientY] float NOT NULL default 0, ADD COLUMN [SitTargetOrientZ] float NOT NULL default 0;",
838 m_connection))
839 {
840 cmd.ExecuteNonQuery();
841 }
842 }
843
844 return prim;
845 }
846
847 /// <summary>
848 /// Build a prim inventory item from the persisted data.
849 /// </summary>
850 /// <param name="row"></param>
851 /// <returns></returns>
852 private TaskInventoryItem buildItem(DataRow row)
853 {
854 TaskInventoryItem taskItem = new TaskInventoryItem();
855
856 taskItem.ItemID = new LLUUID((String)row["itemID"]);
857 taskItem.ParentPartID = new LLUUID((String)row["primID"]);
858 taskItem.AssetID = new LLUUID((String)row["assetID"]);
859 taskItem.ParentID = new LLUUID((String)row["parentFolderID"]);
860
861 taskItem.InvType = Convert.ToInt32(row["invType"]);
862 taskItem.Type = Convert.ToInt32(row["assetType"]);
863
864 taskItem.Name = (String)row["name"];
865 taskItem.Description = (String)row["description"];
866 taskItem.CreationDate = Convert.ToUInt32(row["creationDate"]);
867 taskItem.CreatorID = new LLUUID((String)row["creatorID"]);
868 taskItem.OwnerID = new LLUUID((String)row["ownerID"]);
869 taskItem.LastOwnerID = new LLUUID((String)row["lastOwnerID"]);
870 taskItem.GroupID = new LLUUID((String)row["groupID"]);
871
872 taskItem.NextOwnerMask = Convert.ToUInt32(row["nextPermissions"]);
873 taskItem.OwnerMask = Convert.ToUInt32(row["currentPermissions"]);
874 taskItem.BaseMask = Convert.ToUInt32(row["basePermissions"]);
875 taskItem.EveryoneMask = Convert.ToUInt32(row["everyonePermissions"]);
876 taskItem.GroupMask = Convert.ToUInt32(row["groupPermissions"]);
877
878 return taskItem;
879 }
880
881 private LandData buildLandData(DataRow row)
882 {
883 LandData newData = new LandData();
884
885 newData.globalID = new LLUUID((String)row["UUID"]);
886 newData.localID = Convert.ToInt32(row["LocalLandID"]);
887
888 // Bitmap is a byte[512]
889 newData.landBitmapByteArray = (Byte[])row["Bitmap"];
890
891 newData.landName = (String)row["Name"];
892 newData.landDesc = (String)row["Description"];
893 newData.ownerID = (String)row["OwnerUUID"];
894 newData.isGroupOwned = Convert.ToBoolean(row["IsGroupOwned"]);
895 newData.area = Convert.ToInt32(row["Area"]);
896 newData.auctionID = Convert.ToUInt32(row["AuctionID"]); //Unemplemented
897 newData.category = (Parcel.ParcelCategory)Convert.ToInt32(row["Category"]);
898 //Enum libsecondlife.Parcel.ParcelCategory
899 newData.claimDate = Convert.ToInt32(row["ClaimDate"]);
900 newData.claimPrice = Convert.ToInt32(row["ClaimPrice"]);
901 newData.groupID = new LLUUID((String)row["GroupUUID"]);
902 newData.salePrice = Convert.ToInt32(row["SalePrice"]);
903 newData.landStatus = (Parcel.ParcelStatus)Convert.ToInt32(row["LandStatus"]);
904 //Enum. libsecondlife.Parcel.ParcelStatus
905 newData.landFlags = Convert.ToUInt32(row["LandFlags"]);
906 newData.landingType = Convert.ToByte(row["LandingType"]);
907 newData.mediaAutoScale = Convert.ToByte(row["MediaAutoScale"]);
908 newData.mediaID = new LLUUID((String)row["MediaTextureUUID"]);
909 newData.mediaURL = (String)row["MediaURL"];
910 newData.musicURL = (String)row["MusicURL"];
911 newData.passHours = Convert.ToSingle(row["PassHours"]);
912 newData.passPrice = Convert.ToInt32(row["PassPrice"]);
913 newData.snapshotID = (String)row["SnapshotUUID"];
914
915 newData.userLocation =
916 new LLVector3(Convert.ToSingle(row["UserLocationX"]), Convert.ToSingle(row["UserLocationY"]),
917 Convert.ToSingle(row["UserLocationZ"]));
918 newData.userLookAt =
919 new LLVector3(Convert.ToSingle(row["UserLookAtX"]), Convert.ToSingle(row["UserLookAtY"]),
920 Convert.ToSingle(row["UserLookAtZ"]));
921 newData.parcelAccessList = new List<ParcelManager.ParcelAccessEntry>();
922
923 return newData;
924 }
925
926 private ParcelManager.ParcelAccessEntry buildLandAccessData(DataRow row)
927 {
928 ParcelManager.ParcelAccessEntry entry = new ParcelManager.ParcelAccessEntry();
929 entry.AgentID = new LLUUID((string)row["AccessUUID"]);
930 entry.Flags = (ParcelManager.AccessList)Convert.ToInt32(row["Flags"]);
931 entry.Time = new DateTime();
932 return entry;
933 }
934
935 private Array serializeTerrain(double[,] val)
936 {
937 MemoryStream str = new MemoryStream(65536 * sizeof(double));
938 BinaryWriter bw = new BinaryWriter(str);
939
940 // TODO: COMPATIBILITY - Add byte-order conversions
941 for (int x = 0; x < 256; x++)
942 for (int y = 0; y < 256; y++)
943 bw.Write(val[x, y]);
944
945 return str.ToArray();
946 }
947
948 private void fillPrimRow(DataRow row, SceneObjectPart prim, LLUUID sceneGroupID, LLUUID regionUUID)
949 {
950 row["UUID"] = prim.UUID;
951 row["RegionUUID"] = regionUUID;
952 row["ParentID"] = prim.ParentID;
953 row["CreationDate"] = prim.CreationDate;
954 row["Name"] = prim.Name;
955 row["SceneGroupID"] = sceneGroupID;
956 // the UUID of the root part for this SceneObjectGroup
957 // various text fields
958 row["Text"] = prim.Text;
959 row["Description"] = prim.Description;
960 row["SitName"] = prim.SitName;
961 row["TouchName"] = prim.TouchName;
962 // permissions
963 row["ObjectFlags"] = prim.ObjectFlags;
964 row["CreatorID"] = prim.CreatorID;
965 row["OwnerID"] = prim.OwnerID;
966 row["GroupID"] = prim.GroupID;
967 row["LastOwnerID"] = prim.LastOwnerID;
968 row["OwnerMask"] = prim.OwnerMask;
969 row["NextOwnerMask"] = prim.NextOwnerMask;
970 row["GroupMask"] = prim.GroupMask;
971 row["EveryoneMask"] = prim.EveryoneMask;
972 row["BaseMask"] = prim.BaseMask;
973 // vectors
974 row["PositionX"] = prim.OffsetPosition.X;
975 row["PositionY"] = prim.OffsetPosition.Y;
976 row["PositionZ"] = prim.OffsetPosition.Z;
977 row["GroupPositionX"] = prim.GroupPosition.X;
978 row["GroupPositionY"] = prim.GroupPosition.Y;
979 row["GroupPositionZ"] = prim.GroupPosition.Z;
980 row["VelocityX"] = prim.Velocity.X;
981 row["VelocityY"] = prim.Velocity.Y;
982 row["VelocityZ"] = prim.Velocity.Z;
983 row["AngularVelocityX"] = prim.AngularVelocity.X;
984 row["AngularVelocityY"] = prim.AngularVelocity.Y;
985 row["AngularVelocityZ"] = prim.AngularVelocity.Z;
986 row["AccelerationX"] = prim.Acceleration.X;
987 row["AccelerationY"] = prim.Acceleration.Y;
988 row["AccelerationZ"] = prim.Acceleration.Z;
989 // quaternions
990 row["RotationX"] = prim.RotationOffset.X;
991 row["RotationY"] = prim.RotationOffset.Y;
992 row["RotationZ"] = prim.RotationOffset.Z;
993 row["RotationW"] = prim.RotationOffset.W;
994
995 try
996 {
997 // Sit target
998 LLVector3 sitTargetPos = prim.GetSitTargetPositionLL();
999 row["SitTargetOffsetX"] = sitTargetPos.X;
1000 row["SitTargetOffsetY"] = sitTargetPos.Y;
1001 row["SitTargetOffsetZ"] = sitTargetPos.Z;
1002
1003 LLQuaternion sitTargetOrient = prim.GetSitTargetOrientationLL();
1004 row["SitTargetOrientW"] = sitTargetOrient.W;
1005 row["SitTargetOrientX"] = sitTargetOrient.X;
1006 row["SitTargetOrientY"] = sitTargetOrient.Y;
1007 row["SitTargetOrientZ"] = sitTargetOrient.Z;
1008 }
1009 catch (Exception)
1010 {
1011 // Database table was created before we got here and needs to be created! :P
1012
1013 using (
1014 SqlCommand cmd =
1015 new SqlCommand(
1016 "ALTER TABLE [prims] ADD COLUMN [SitTargetOffsetX] float NOT NULL default 0, ADD COLUMN [SitTargetOffsetY] float NOT NULL default 0, ADD COLUMN [SitTargetOffsetZ] float NOT NULL default 0, ADD COLUMN [SitTargetOrientW] float NOT NULL default 0, ADD COLUMN [SitTargetOrientX] float NOT NULL default 0, ADD COLUMN [SitTargetOrientY] float NOT NULL default 0, ADD COLUMN [SitTargetOrientZ] float NOT NULL default 0;",
1017 m_connection))
1018 {
1019 cmd.ExecuteNonQuery();
1020 }
1021 }
1022 }
1023
1024 private void fillItemRow(DataRow row, TaskInventoryItem taskItem)
1025 {
1026 row["itemID"] = taskItem.ItemID;
1027 row["primID"] = taskItem.ParentPartID;
1028 row["assetID"] = taskItem.AssetID;
1029 row["parentFolderID"] = taskItem.ParentID;
1030
1031 row["invType"] = taskItem.InvType;
1032 row["assetType"] = taskItem.Type;
1033
1034 row["name"] = taskItem.Name;
1035 row["description"] = taskItem.Description;
1036 row["creationDate"] = taskItem.CreationDate;
1037 row["creatorID"] = taskItem.CreatorID;
1038 row["ownerID"] = taskItem.OwnerID;
1039 row["lastOwnerID"] = taskItem.LastOwnerID;
1040 row["groupID"] = taskItem.GroupID;
1041 row["nextPermissions"] = taskItem.NextOwnerMask;
1042 row["currentPermissions"] = taskItem.OwnerMask;
1043 row["basePermissions"] = taskItem.BaseMask;
1044 row["everyonePermissions"] = taskItem.EveryoneMask;
1045 row["groupPermissions"] = taskItem.GroupMask;
1046 }
1047
1048 private void fillLandRow(DataRow row, LandData land, LLUUID regionUUID)
1049 {
1050 row["UUID"] = land.globalID.UUID;
1051 row["RegionUUID"] = regionUUID.UUID;
1052 row["LocalLandID"] = land.localID;
1053
1054 // Bitmap is a byte[512]
1055 row["Bitmap"] = land.landBitmapByteArray;
1056
1057 row["Name"] = land.landName;
1058 row["Description"] = land.landDesc;
1059 row["OwnerUUID"] = land.ownerID.UUID;
1060 row["IsGroupOwned"] = land.isGroupOwned;
1061 row["Area"] = land.area;
1062 row["AuctionID"] = land.auctionID; //Unemplemented
1063 row["Category"] = land.category; //Enum libsecondlife.Parcel.ParcelCategory
1064 row["ClaimDate"] = land.claimDate;
1065 row["ClaimPrice"] = land.claimPrice;
1066 row["GroupUUID"] = land.groupID.UUID;
1067 row["SalePrice"] = land.salePrice;
1068 row["LandStatus"] = land.landStatus; //Enum. libsecondlife.Parcel.ParcelStatus
1069 row["LandFlags"] = land.landFlags;
1070 row["LandingType"] = land.landingType;
1071 row["MediaAutoScale"] = land.mediaAutoScale;
1072 row["MediaTextureUUID"] = land.mediaID.UUID;
1073 row["MediaURL"] = land.mediaURL;
1074 row["MusicURL"] = land.musicURL;
1075 row["PassHours"] = land.passHours;
1076 row["PassPrice"] = land.passPrice;
1077 row["SnapshotUUID"] = land.snapshotID.UUID;
1078 row["UserLocationX"] = land.userLocation.X;
1079 row["UserLocationY"] = land.userLocation.Y;
1080 row["UserLocationZ"] = land.userLocation.Z;
1081 row["UserLookAtX"] = land.userLookAt.X;
1082 row["UserLookAtY"] = land.userLookAt.Y;
1083 row["UserLookAtZ"] = land.userLookAt.Z;
1084 }
1085
1086 private void fillLandAccessRow(DataRow row, ParcelManager.ParcelAccessEntry entry, LLUUID parcelID)
1087 {
1088 row["LandUUID"] = parcelID.UUID;
1089 row["AccessUUID"] = entry.AgentID.UUID;
1090 row["Flags"] = entry.Flags;
1091 }
1092
1093 private PrimitiveBaseShape buildShape(DataRow row)
1094 {
1095 PrimitiveBaseShape s = new PrimitiveBaseShape();
1096 s.Scale = new LLVector3(
1097 Convert.ToSingle(row["ScaleX"]),
1098 Convert.ToSingle(row["ScaleY"]),
1099 Convert.ToSingle(row["ScaleZ"])
1100 );
1101 // paths
1102 s.PCode = Convert.ToByte(row["PCode"]);
1103 s.PathBegin = Convert.ToUInt16(row["PathBegin"]);
1104 s.PathEnd = Convert.ToUInt16(row["PathEnd"]);
1105 s.PathScaleX = Convert.ToByte(row["PathScaleX"]);
1106 s.PathScaleY = Convert.ToByte(row["PathScaleY"]);
1107 s.PathShearX = Convert.ToByte(row["PathShearX"]);
1108 s.PathShearY = Convert.ToByte(row["PathShearY"]);
1109 s.PathSkew = Convert.ToSByte(row["PathSkew"]);
1110 s.PathCurve = Convert.ToByte(row["PathCurve"]);
1111 s.PathRadiusOffset = Convert.ToSByte(row["PathRadiusOffset"]);
1112 s.PathRevolutions = Convert.ToByte(row["PathRevolutions"]);
1113 s.PathTaperX = Convert.ToSByte(row["PathTaperX"]);
1114 s.PathTaperY = Convert.ToSByte(row["PathTaperY"]);
1115 s.PathTwist = Convert.ToSByte(row["PathTwist"]);
1116 s.PathTwistBegin = Convert.ToSByte(row["PathTwistBegin"]);
1117 // profile
1118 s.ProfileBegin = Convert.ToUInt16(row["ProfileBegin"]);
1119 s.ProfileEnd = Convert.ToUInt16(row["ProfileEnd"]);
1120 s.ProfileCurve = Convert.ToByte(row["ProfileCurve"]);
1121 s.ProfileHollow = Convert.ToUInt16(row["ProfileHollow"]);
1122 s.State = Convert.ToByte(row["State"]);
1123
1124 byte[] textureEntry = (byte[])row["Texture"];
1125 s.TextureEntry = textureEntry;
1126
1127 s.ExtraParams = (byte[])row["ExtraParams"];
1128
1129 return s;
1130 }
1131
1132 private void fillShapeRow(DataRow row, SceneObjectPart prim)
1133 {
1134 PrimitiveBaseShape s = prim.Shape;
1135 row["UUID"] = prim.UUID;
1136 // shape is an enum
1137 row["Shape"] = 0;
1138 // vectors
1139 row["ScaleX"] = s.Scale.X;
1140 row["ScaleY"] = s.Scale.Y;
1141 row["ScaleZ"] = s.Scale.Z;
1142 // paths
1143 row["PCode"] = s.PCode;
1144 row["PathBegin"] = s.PathBegin;
1145 row["PathEnd"] = s.PathEnd;
1146 row["PathScaleX"] = s.PathScaleX;
1147 row["PathScaleY"] = s.PathScaleY;
1148 row["PathShearX"] = s.PathShearX;
1149 row["PathShearY"] = s.PathShearY;
1150 row["PathSkew"] = s.PathSkew;
1151 row["PathCurve"] = s.PathCurve;
1152 row["PathRadiusOffset"] = s.PathRadiusOffset;
1153 row["PathRevolutions"] = s.PathRevolutions;
1154 row["PathTaperX"] = s.PathTaperX;
1155 row["PathTaperY"] = s.PathTaperY;
1156 row["PathTwist"] = s.PathTwist;
1157 row["PathTwistBegin"] = s.PathTwistBegin;
1158 // profile
1159 row["ProfileBegin"] = s.ProfileBegin;
1160 row["ProfileEnd"] = s.ProfileEnd;
1161 row["ProfileCurve"] = s.ProfileCurve;
1162 row["ProfileHollow"] = s.ProfileHollow;
1163 row["State"] = s.State;
1164 row["Texture"] = s.TextureEntry;
1165 row["ExtraParams"] = s.ExtraParams;
1166 }
1167
1168 private void addPrim(SceneObjectPart prim, LLUUID sceneGroupID, LLUUID regionUUID)
1169 {
1170 DataTable prims = m_dataSet.Tables["prims"];
1171 DataTable shapes = m_dataSet.Tables["primshapes"];
1172
1173 DataRow primRow = prims.Rows.Find(prim.UUID);
1174 if (primRow == null)
1175 {
1176 primRow = prims.NewRow();
1177 fillPrimRow(primRow, prim, sceneGroupID, regionUUID);
1178 prims.Rows.Add(primRow);
1179 }
1180 else
1181 {
1182 fillPrimRow(primRow, prim, sceneGroupID, regionUUID);
1183 }
1184
1185 DataRow shapeRow = shapes.Rows.Find(prim.UUID);
1186 if (shapeRow == null)
1187 {
1188 shapeRow = shapes.NewRow();
1189 fillShapeRow(shapeRow, prim);
1190 shapes.Rows.Add(shapeRow);
1191 }
1192 else
1193 {
1194 fillShapeRow(shapeRow, prim);
1195 }
1196 }
1197
1198 // see IRegionDatastore
1199 public void StorePrimInventory(LLUUID primID, ICollection<TaskInventoryItem> items)
1200 {
1201 if (!persistPrimInventories)
1202 return;
1203
1204 m_log.InfoFormat("[DATASTORE]: Persisting Prim Inventory with prim ID {0}", primID);
1205
1206 // For now, we're just going to crudely remove all the previous inventory items
1207 // no matter whether they have changed or not, and replace them with the current set.
1208 lock (m_dataSet)
1209 {
1210 RemoveItems(primID);
1211
1212 // repalce with current inventory details
1213 foreach (TaskInventoryItem newItem in items)
1214 {
1215 // m_log.InfoFormat(
1216 // "[DATASTORE]: " +
1217 // "Adding item {0}, {1} to prim ID {2}",
1218 // newItem.Name, newItem.ItemID, newItem.ParentPartID);
1219
1220 DataRow newItemRow = m_itemsTable.NewRow();
1221 fillItemRow(newItemRow, newItem);
1222 m_itemsTable.Rows.Add(newItemRow);
1223 }
1224 }
1225
1226 Commit();
1227 }
1228
1229 /***********************************************************************
1230 *
1231 * SQL Statement Creation Functions
1232 *
1233 * These functions create SQL statements for update, insert, and create.
1234 * They can probably be factored later to have a db independant
1235 * portion and a db specific portion
1236 *
1237 **********************************************************************/
1238
1239 private SqlCommand createInsertCommand(string table, DataTable dt)
1240 {
1241 /**
1242 * This is subtle enough to deserve some commentary.
1243 * Instead of doing *lots* and *lots of hardcoded strings
1244 * for database definitions we'll use the fact that
1245 * realistically all insert statements look like "insert
1246 * into A(b, c) values(:b, :c) on the parameterized query
1247 * front. If we just have a list of b, c, etc... we can
1248 * generate these strings instead of typing them out.
1249 */
1250 string[] cols = new string[dt.Columns.Count];
1251 for (int i = 0; i < dt.Columns.Count; i++)
1252 {
1253 DataColumn col = dt.Columns[i];
1254 cols[i] = col.ColumnName;
1255 }
1256
1257 string sql = "insert into " + table + "(";
1258 sql += String.Join(", ", cols);
1259 // important, the first ':' needs to be here, the rest get added in the join
1260 sql += ") values (@";
1261 sql += String.Join(", @", cols);
1262 sql += ")";
1263 SqlCommand cmd = new SqlCommand(sql);
1264
1265 // this provides the binding for all our parameters, so
1266 // much less code than it used to be
1267 foreach (DataColumn col in dt.Columns)
1268 {
1269 cmd.Parameters.Add(createSqlParameter(col.ColumnName, col.DataType));
1270 }
1271 return cmd;
1272 }
1273
1274 private SqlCommand createUpdateCommand(string table, string pk, DataTable dt)
1275 {
1276 string sql = "update " + table + " set ";
1277 string subsql = String.Empty;
1278 foreach (DataColumn col in dt.Columns)
1279 {
1280 if (subsql.Length > 0)
1281 {
1282 // a map function would rock so much here
1283 subsql += ", ";
1284 }
1285 subsql += col.ColumnName + "= @" + col.ColumnName;
1286 }
1287 sql += subsql;
1288 sql += " where " + pk;
1289 SqlCommand cmd = new SqlCommand(sql);
1290
1291 // this provides the binding for all our parameters, so
1292 // much less code than it used to be
1293
1294 foreach (DataColumn col in dt.Columns)
1295 {
1296 cmd.Parameters.Add(createSqlParameter(col.ColumnName, col.DataType));
1297 }
1298 return cmd;
1299 }
1300
1301 private string defineTable(DataTable dt)
1302 {
1303 string sql = "create table " + dt.TableName + "(";
1304 string subsql = String.Empty;
1305 foreach (DataColumn col in dt.Columns)
1306 {
1307 if (subsql.Length > 0)
1308 {
1309 // a map function would rock so much here
1310 subsql += ",\n";
1311 }
1312 subsql += col.ColumnName + " " + MSSQLManager.SqlType(col.DataType);
1313 if (dt.PrimaryKey.Length > 0 && col == dt.PrimaryKey[0])
1314 {
1315 subsql += " primary key";
1316 }
1317 }
1318 sql += subsql;
1319 sql += ")";
1320
1321 return sql;
1322 }
1323
1324 /***********************************************************************
1325 *
1326 * Database Binding functions
1327 *
1328 * These will be db specific due to typing, and minor differences
1329 * in databases.
1330 *
1331 **********************************************************************/
1332
1333 ///<summary>
1334 /// This is a convenience function that collapses 5 repetitive
1335 /// lines for defining SqlParameters to 2 parameters:
1336 /// column name and database type.
1337 ///
1338 /// It assumes certain conventions like :param as the param
1339 /// name to replace in parametrized queries, and that source
1340 /// version is always current version, both of which are fine
1341 /// for us.
1342 ///</summary>
1343 ///<returns>a built Sql parameter</returns>
1344 private SqlParameter createSqlParameter(string name, Type type)
1345 {
1346 SqlParameter param = new SqlParameter();
1347 param.ParameterName = "@" + name;
1348 param.DbType = dbtypeFromType(type);
1349 param.SourceColumn = name;
1350 param.SourceVersion = DataRowVersion.Current;
1351 return param;
1352 }
1353
1354// TODO: unused
1355// private SqlParameter createParamWithValue(string name, Type type, Object o)
1356// {
1357// SqlParameter param = createSqlParameter(name, type);
1358// param.Value = o;
1359// return param;
1360// }
1361
1362 private void setupPrimCommands(SqlDataAdapter da, SqlConnection conn)
1363 {
1364 da.InsertCommand = createInsertCommand("prims", m_dataSet.Tables["prims"]);
1365 da.InsertCommand.Connection = conn;
1366
1367 da.UpdateCommand = createUpdateCommand("prims", "UUID=@UUID", m_dataSet.Tables["prims"]);
1368 da.UpdateCommand.Connection = conn;
1369
1370 SqlCommand delete = new SqlCommand("delete from prims where UUID = @UUID");
1371 delete.Parameters.Add(createSqlParameter("UUID", typeof(String)));
1372 delete.Connection = conn;
1373 da.DeleteCommand = delete;
1374 }
1375
1376 private void SetupItemsCommands(SqlDataAdapter da, SqlConnection conn)
1377 {
1378 da.InsertCommand = createInsertCommand("primitems", m_itemsTable);
1379 da.InsertCommand.Connection = conn;
1380
1381 da.UpdateCommand = createUpdateCommand("primitems", "itemID = @itemID", m_itemsTable);
1382 da.UpdateCommand.Connection = conn;
1383
1384 SqlCommand delete = new SqlCommand("delete from primitems where itemID = @itemID");
1385 delete.Parameters.Add(createSqlParameter("itemID", typeof(String)));
1386 delete.Connection = conn;
1387 da.DeleteCommand = delete;
1388 }
1389
1390 private void setupTerrainCommands(SqlDataAdapter da, SqlConnection conn)
1391 {
1392 da.InsertCommand = createInsertCommand("terrain", m_dataSet.Tables["terrain"]);
1393 da.InsertCommand.Connection = conn;
1394 }
1395
1396 private void setupLandCommands(SqlDataAdapter da, SqlConnection conn)
1397 {
1398 da.InsertCommand = createInsertCommand("land", m_dataSet.Tables["land"]);
1399 da.InsertCommand.Connection = conn;
1400
1401 da.UpdateCommand = createUpdateCommand("land", "UUID=@UUID", m_dataSet.Tables["land"]);
1402 da.UpdateCommand.Connection = conn;
1403 }
1404
1405 private void setupLandAccessCommands(SqlDataAdapter da, SqlConnection conn)
1406 {
1407 da.InsertCommand = createInsertCommand("landaccesslist", m_dataSet.Tables["landaccesslist"]);
1408 da.InsertCommand.Connection = conn;
1409 }
1410
1411 private void setupShapeCommands(SqlDataAdapter da, SqlConnection conn)
1412 {
1413 da.InsertCommand = createInsertCommand("primshapes", m_dataSet.Tables["primshapes"]);
1414 da.InsertCommand.Connection = conn;
1415
1416 da.UpdateCommand = createUpdateCommand("primshapes", "UUID=@UUID", m_dataSet.Tables["primshapes"]);
1417 da.UpdateCommand.Connection = conn;
1418
1419 SqlCommand delete = new SqlCommand("delete from primshapes where UUID = @UUID");
1420 delete.Parameters.Add(createSqlParameter("UUID", typeof(String)));
1421 delete.Connection = conn;
1422 da.DeleteCommand = delete;
1423 }
1424
1425 private void InitDB(SqlConnection conn)
1426 {
1427 string createPrims = defineTable(createPrimTable());
1428 string createShapes = defineTable(createShapeTable());
1429 string createItems = defineTable(createItemsTable());
1430 string createTerrain = defineTable(createTerrainTable());
1431 string createLand = defineTable(createLandTable());
1432 string createLandAccessList = defineTable(createLandAccessListTable());
1433
1434 SqlCommand pcmd = new SqlCommand(createPrims, conn);
1435 SqlCommand scmd = new SqlCommand(createShapes, conn);
1436 SqlCommand icmd = new SqlCommand(createItems, conn);
1437 SqlCommand tcmd = new SqlCommand(createTerrain, conn);
1438 SqlCommand lcmd = new SqlCommand(createLand, conn);
1439 SqlCommand lalcmd = new SqlCommand(createLandAccessList, conn);
1440
1441 conn.Open();
1442 try
1443 {
1444 pcmd.ExecuteNonQuery();
1445 }
1446 catch (SqlException e)
1447 {
1448 m_log.WarnFormat("[MSSql]: Primitives Table Already Exists: {0}", e);
1449 }
1450
1451 try
1452 {
1453 scmd.ExecuteNonQuery();
1454 }
1455 catch (SqlException e)
1456 {
1457 m_log.WarnFormat("[MSSql]: Shapes Table Already Exists: {0}", e);
1458 }
1459
1460 try
1461 {
1462 icmd.ExecuteNonQuery();
1463 }
1464 catch (SqlException e)
1465 {
1466 m_log.WarnFormat("[MSSql]: Items Table Already Exists: {0}", e);
1467 }
1468
1469 try
1470 {
1471 tcmd.ExecuteNonQuery();
1472 }
1473 catch (SqlException e)
1474 {
1475 m_log.WarnFormat("[MSSql]: Terrain Table Already Exists: {0}", e);
1476 }
1477
1478 try
1479 {
1480 lcmd.ExecuteNonQuery();
1481 }
1482 catch (SqlException e)
1483 {
1484 m_log.WarnFormat("[MSSql]: Land Table Already Exists: {0}", e);
1485 }
1486
1487 try
1488 {
1489 lalcmd.ExecuteNonQuery();
1490 }
1491 catch (SqlException e)
1492 {
1493 m_log.WarnFormat("[MSSql]: LandAccessList Table Already Exists: {0}", e);
1494 }
1495 conn.Close();
1496 }
1497
1498 private bool TestTables(SqlConnection conn)
1499 {
1500 SqlCommand primSelectCmd = new SqlCommand(m_primSelect, conn);
1501 SqlDataAdapter pDa = new SqlDataAdapter(primSelectCmd);
1502 SqlCommand shapeSelectCmd = new SqlCommand(m_shapeSelect, conn);
1503 SqlDataAdapter sDa = new SqlDataAdapter(shapeSelectCmd);
1504 SqlCommand itemsSelectCmd = new SqlCommand(m_itemsSelect, conn);
1505 SqlDataAdapter iDa = new SqlDataAdapter(itemsSelectCmd);
1506 SqlCommand terrainSelectCmd = new SqlCommand(m_terrainSelect, conn);
1507 SqlDataAdapter tDa = new SqlDataAdapter(terrainSelectCmd);
1508 SqlCommand landSelectCmd = new SqlCommand(m_landSelect, conn);
1509 SqlDataAdapter lDa = new SqlDataAdapter(landSelectCmd);
1510 SqlCommand landAccessListSelectCmd = new SqlCommand(m_landAccessListSelect, conn);
1511 SqlDataAdapter lalDa = new SqlDataAdapter(landAccessListSelectCmd);
1512
1513 DataSet tmpDS = new DataSet();
1514 try
1515 {
1516 pDa.Fill(tmpDS, "prims");
1517 sDa.Fill(tmpDS, "primshapes");
1518
1519 if (persistPrimInventories)
1520 iDa.Fill(tmpDS, "primitems");
1521
1522 tDa.Fill(tmpDS, "terrain");
1523 lDa.Fill(tmpDS, "land");
1524 lalDa.Fill(tmpDS, "landaccesslist");
1525 }
1526 catch (SqlException)
1527 {
1528 m_log.Info("[DATASTORE]: MySql Database doesn't exist... creating");
1529 InitDB(conn);
1530 }
1531
1532 pDa.Fill(tmpDS, "prims");
1533 sDa.Fill(tmpDS, "primshapes");
1534
1535 if (persistPrimInventories)
1536 iDa.Fill(tmpDS, "primitems");
1537
1538 tDa.Fill(tmpDS, "terrain");
1539 lDa.Fill(tmpDS, "land");
1540 lalDa.Fill(tmpDS, "landaccesslist");
1541
1542 foreach (DataColumn col in createPrimTable().Columns)
1543 {
1544 if (!tmpDS.Tables["prims"].Columns.Contains(col.ColumnName))
1545 {
1546 m_log.Info("[DATASTORE]: Missing required column:" + col.ColumnName);
1547 return false;
1548 }
1549 }
1550
1551 foreach (DataColumn col in createShapeTable().Columns)
1552 {
1553 if (!tmpDS.Tables["primshapes"].Columns.Contains(col.ColumnName))
1554 {
1555 m_log.Info("[DATASTORE]: Missing required column:" + col.ColumnName);
1556 return false;
1557 }
1558 }
1559
1560 // XXX primitems should probably go here eventually
1561
1562 foreach (DataColumn col in createTerrainTable().Columns)
1563 {
1564 if (!tmpDS.Tables["terrain"].Columns.Contains(col.ColumnName))
1565 {
1566 m_log.Info("[DATASTORE]: Missing require column:" + col.ColumnName);
1567 return false;
1568 }
1569 }
1570
1571 foreach (DataColumn col in createLandTable().Columns)
1572 {
1573 if (!tmpDS.Tables["land"].Columns.Contains(col.ColumnName))
1574 {
1575 m_log.Info("[DATASTORE]: Missing require column:" + col.ColumnName);
1576 return false;
1577 }
1578 }
1579
1580 foreach (DataColumn col in createLandAccessListTable().Columns)
1581 {
1582 if (!tmpDS.Tables["landaccesslist"].Columns.Contains(col.ColumnName))
1583 {
1584 m_log.Info("[DATASTORE]: Missing require column:" + col.ColumnName);
1585 return false;
1586 }
1587 }
1588
1589 return true;
1590 }
1591
1592 /***********************************************************************
1593 *
1594 * Type conversion functions
1595 *
1596 **********************************************************************/
1597
1598 private DbType dbtypeFromType(Type type)
1599 {
1600 if (type == typeof(String))
1601 {
1602 return DbType.String;
1603 }
1604 else if (type == typeof(Int32))
1605 {
1606 return DbType.Int32;
1607 }
1608 else if (type == typeof(Double))
1609 {
1610 return DbType.Double;
1611 }
1612 else if (type == typeof(Byte[]))
1613 {
1614 return DbType.Binary;
1615 }
1616 else
1617 {
1618 return DbType.String;
1619 }
1620 }
1621 }
1622}