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