aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Framework/Data.MySQL/MySQLDataStore.cs
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--OpenSim/Framework/Data.MySQL/MySQLDataStore.cs1035
1 files changed, 1035 insertions, 0 deletions
diff --git a/OpenSim/Framework/Data.MySQL/MySQLDataStore.cs b/OpenSim/Framework/Data.MySQL/MySQLDataStore.cs
new file mode 100644
index 0000000..c468414
--- /dev/null
+++ b/OpenSim/Framework/Data.MySQL/MySQLDataStore.cs
@@ -0,0 +1,1035 @@
1using System;
2using System.Collections.Generic;
3using System.Data;
4using System.Diagnostics;
5using System.IO;
6using System.Text;
7using libsecondlife;
8using MySql.Data.MySqlClient;
9using OpenSim.Framework.Console;
10using OpenSim.Region.Environment.Interfaces;
11using OpenSim.Region.Environment.LandManagement;
12using OpenSim.Region.Environment.Scenes;
13using System.Data.SqlClient;
14using System.Data.Common;
15
16namespace OpenSim.Framework.Data.MySQL
17{
18 public class MySQLDataStore : IRegionDataStore
19 {
20 private const string m_primSelect = "select * from prims";
21 private const string m_shapeSelect = "select * from primshapes";
22 private const string m_terrainSelect = "select * from terrain limit 1";
23
24 private DataSet m_dataSet;
25 private MySqlDataAdapter m_primDataAdapter;
26 private MySqlDataAdapter m_shapeDataAdapter;
27 private MySqlConnection m_connection;
28 private MySqlDataAdapter m_terrainDataAdapter;
29 private DataTable m_primTable;
30 private DataTable m_shapeTable;
31 private DataTable m_terrainTable;
32
33 /***********************************************************************
34 *
35 * Public Interface Functions
36 *
37 **********************************************************************/
38
39 public void Initialise(string connectionstring, string dbname)
40 {
41 m_dataSet = new DataSet();
42
43 MainLog.Instance.Verbose("DATASTORE", "MySql - connecting: " + connectionstring);
44 m_connection = new MySqlConnection(connectionstring);
45
46 MySqlCommand primSelectCmd = new MySqlCommand(m_primSelect, m_connection);
47 m_primDataAdapter = new MySqlDataAdapter(primSelectCmd);
48
49 MySqlCommand shapeSelectCmd = new MySqlCommand(m_shapeSelect, m_connection);
50 m_shapeDataAdapter = new MySqlDataAdapter(shapeSelectCmd);
51
52 MySqlCommand terrainSelectCmd = new MySqlCommand(m_terrainSelect, m_connection);
53 m_terrainDataAdapter = new MySqlDataAdapter(terrainSelectCmd);
54
55 TestTables(m_connection);
56
57 lock (m_dataSet)
58 {
59 m_primTable = createPrimTable();
60 m_dataSet.Tables.Add(m_primTable);
61 SetupPrimCommands(m_primDataAdapter, m_connection);
62 m_primDataAdapter.Fill(m_primTable);
63
64 m_shapeTable = createShapeTable();
65 m_dataSet.Tables.Add(m_shapeTable);
66 SetupShapeCommands(m_shapeDataAdapter, m_connection);
67 m_shapeDataAdapter.Fill(m_shapeTable);
68
69 m_terrainTable = createTerrainTable();
70 m_dataSet.Tables.Add(m_terrainTable);
71 SetupTerrainCommands(m_terrainDataAdapter, m_connection);
72 m_terrainDataAdapter.Fill(m_terrainTable);
73 }
74 }
75
76 public void StoreObject(SceneObjectGroup obj, LLUUID regionUUID)
77 {
78 lock (m_dataSet)
79 {
80 foreach (SceneObjectPart prim in obj.Children.Values)
81 {
82 if ((prim.ObjectFlags & (uint)LLObject.ObjectFlags.Physics) == 0)
83 {
84 MainLog.Instance.Verbose("DATASTORE", "Adding obj: " + obj.UUID + " to region: " + regionUUID);
85 addPrim(prim, obj.UUID, regionUUID);
86 }
87 else
88 {
89 // MainLog.Instance.Verbose("DATASTORE", "Ignoring Physical obj: " + obj.UUID + " in region: " + regionUUID);
90 }
91 }
92 }
93
94 Commit();
95 }
96
97 public void RemoveObject(LLUUID obj, LLUUID regionUUID)
98 {
99 DataTable prims = m_primTable;
100 DataTable shapes = m_shapeTable;
101
102 string selectExp = "SceneGroupID = '" + obj.ToString() + "'";
103 lock (m_dataSet)
104 {
105 DataRow[] primRows = prims.Select(selectExp);
106 foreach (DataRow row in primRows)
107 {
108 LLUUID uuid = new LLUUID((string)row["UUID"]);
109 DataRow shapeRow = shapes.Rows.Find(uuid);
110 if (shapeRow != null)
111 {
112 shapeRow.Delete();
113 }
114 row.Delete();
115 }
116 }
117
118 Commit();
119 }
120
121 public List<SceneObjectGroup> LoadObjects(LLUUID regionUUID)
122 {
123 Dictionary<LLUUID, SceneObjectGroup> createdObjects = new Dictionary<LLUUID, SceneObjectGroup>();
124
125 List<SceneObjectGroup> retvals = new List<SceneObjectGroup>();
126
127 DataTable prims = m_primTable;
128 DataTable shapes = m_shapeTable;
129
130 string byRegion = "RegionUUID = '" + regionUUID.ToString() + "'";
131 string orderByParent = "ParentID ASC";
132
133 lock (m_dataSet)
134 {
135 DataRow[] primsForRegion = prims.Select(byRegion, orderByParent);
136 MainLog.Instance.Verbose("DATASTORE",
137 "Loaded " + primsForRegion.Length + " prims for region: " + regionUUID);
138
139 foreach (DataRow primRow in primsForRegion)
140 {
141 try
142 {
143 string uuid = (string)primRow["UUID"];
144 string objID = (string)primRow["SceneGroupID"];
145 if (uuid == objID) //is new SceneObjectGroup ?
146 {
147 SceneObjectGroup group = new SceneObjectGroup();
148 SceneObjectPart prim = buildPrim(primRow);
149 DataRow shapeRow = shapes.Rows.Find(prim.UUID);
150 if (shapeRow != null)
151 {
152 prim.Shape = buildShape(shapeRow);
153 }
154 else
155 {
156 MainLog.Instance.Notice(
157 "No shape found for prim in storage, so setting default box shape");
158 prim.Shape = BoxShape.Default;
159 }
160 group.AddPart(prim);
161 group.RootPart = prim;
162
163 createdObjects.Add(group.UUID, group);
164 retvals.Add(group);
165 }
166 else
167 {
168 SceneObjectPart prim = buildPrim(primRow);
169 DataRow shapeRow = shapes.Rows.Find(prim.UUID);
170 if (shapeRow != null)
171 {
172 prim.Shape = buildShape(shapeRow);
173 }
174 else
175 {
176 MainLog.Instance.Notice(
177 "No shape found for prim in storage, so setting default box shape");
178 prim.Shape = BoxShape.Default;
179 }
180 createdObjects[new LLUUID(objID)].AddPart(prim);
181 }
182 }
183 catch (Exception e)
184 {
185 MainLog.Instance.Error("DATASTORE", "Failed create prim object, exception and data follows");
186 MainLog.Instance.Verbose(e.ToString());
187 foreach (DataColumn col in prims.Columns)
188 {
189 MainLog.Instance.Verbose("Col: " + col.ColumnName + " => " + primRow[col]);
190 }
191 }
192 }
193 }
194 return retvals;
195 }
196
197
198 public void StoreTerrain(double[,] ter, LLUUID regionID)
199 {
200 int revision = Util.UnixTimeSinceEpoch();
201 MainLog.Instance.Verbose("DATASTORE", "Storing terrain revision r" + revision.ToString());
202
203 DataTable terrain = m_dataSet.Tables["terrain"];
204 lock (m_dataSet)
205 {
206 MySqlCommand cmd = new MySqlCommand("insert into terrain(RegionUUID, Revision, Heightfield)" +
207 " values(?RegionUUID, ?Revision, ?Heightfield)", m_connection);
208 using (cmd)
209 {
210
211 cmd.Parameters.Add(new MySqlParameter("?RegionUUID", regionID.ToString()));
212 cmd.Parameters.Add(new MySqlParameter("?Revision", revision));
213 cmd.Parameters.Add(new MySqlParameter("?Heightfield", serializeTerrain(ter)));
214 cmd.ExecuteNonQuery();
215 }
216 }
217 }
218
219 public double[,] LoadTerrain(LLUUID regionID)
220 {
221 double[,] terret = new double[256, 256];
222 terret.Initialize();
223
224 MySqlCommand cmd = new MySqlCommand(
225 @"select RegionUUID, Revision, Heightfield from terrain
226 where RegionUUID=?RegionUUID order by Revision desc limit 1"
227 , m_connection);
228
229 MySqlParameter param = new MySqlParameter();
230 cmd.Parameters.Add(new MySqlParameter("?RegionUUID", regionID.ToString()));
231
232 if (m_connection.State != ConnectionState.Open)
233 {
234 m_connection.Open();
235 }
236
237 using (MySqlDataReader row = cmd.ExecuteReader())
238 {
239 int rev = 0;
240 if (row.Read())
241 {
242 byte[] heightmap = (byte[])row["Heightfield"];
243 for (int x = 0; x < 256; x++)
244 {
245 for (int y = 0; y < 256; y++)
246 {
247 terret[x, y] = BitConverter.ToDouble(heightmap, ((x * 256) + y) * 8);
248 }
249 }
250 rev = (int)row["Revision"];
251 }
252 else
253 {
254 MainLog.Instance.Verbose("DATASTORE", "No terrain found for region");
255 return null;
256 }
257
258 MainLog.Instance.Verbose("DATASTORE", "Loaded terrain revision r" + rev.ToString());
259 }
260
261 return terret;
262 }
263
264 public void RemoveLandObject(uint id)
265 {
266 }
267
268 public void StoreParcel(Land parcel)
269 {
270 }
271
272 public List<Land> LoadLandObjects()
273 {
274 return new List<Land>();
275 }
276
277 private void DisplayDataSet(DataSet ds, string title)
278 {
279 Debug.WriteLine(title);
280 //--- Loop through the DataTables
281 foreach (DataTable table in ds.Tables)
282 {
283 Debug.WriteLine("*** DataTable: " + table.TableName + "***");
284 //--- Loop through each DataTable's DataRows
285 foreach (DataRow row in table.Rows)
286 {
287 //--- Display the original values, if there are any.
288 if (row.HasVersion(System.Data.DataRowVersion.Original))
289 {
290 Debug.Write("Original Row Values ===> ");
291 foreach (DataColumn column in table.Columns)
292 Debug.Write(column.ColumnName + " = " +
293 row[column, DataRowVersion.Original] + ", ");
294 Debug.WriteLine("");
295 }
296 //--- Display the current values, if there are any.
297 if (row.HasVersion(System.Data.DataRowVersion.Current))
298 {
299 Debug.Write("Current Row Values ====> ");
300 foreach (DataColumn column in table.Columns)
301 Debug.Write(column.ColumnName + " = " +
302 row[column, DataRowVersion.Current] + ", ");
303 Debug.WriteLine("");
304 }
305 Debug.WriteLine("");
306 }
307 }
308 }
309
310 //private void DisplayTableMappings( MySqlDataAdapter adapter )
311 //{
312 // DataTableMappingCollection mappings = adapter.TableMappings;
313
314 // foreach( DataTableMapping mapping in mappings )
315 // {
316 // Debug.WriteLine( String.Format( "Source Table: ", mapping.SourceTable ));
317
318 // DataColumnMappingCollection columnMappings = mapping.ColumnMappings;
319
320 // foreach (DataColumnMapping columnMapping in columnMappings)
321 // {
322 // Debug.WriteLine( String.Format( "DataSet [{0}] <-> Source [{1}]", columnMapping.DataSetColumn, columnMapping.SourceColumn ));
323 // }
324 // }
325 //}
326
327 public void Commit()
328 {
329 if (m_connection.State != ConnectionState.Open)
330 {
331 m_connection.Open();
332 }
333
334 lock (m_dataSet)
335 {
336 DisplayDataSet(m_dataSet, "Region DataSet");
337 //DisplayTableMappings(m_primDataAdapter);
338
339 // m_primDataAdapter.MissingMappingAction = MissingMappingAction.Error;
340
341 m_primDataAdapter.Update(m_primTable);
342 m_shapeDataAdapter.Update(m_shapeTable);
343 m_terrainDataAdapter.Update(m_terrainTable);
344
345 m_dataSet.AcceptChanges();
346 }
347 }
348
349 public void Shutdown()
350 {
351 Commit();
352 }
353
354 /***********************************************************************
355 *
356 * Database Definition Functions
357 *
358 * This should be db agnostic as we define them in ADO.NET terms
359 *
360 **********************************************************************/
361
362 private DataColumn createCol(DataTable dt, string name, Type type)
363 {
364 DataColumn col = new DataColumn(name, type);
365 dt.Columns.Add(col);
366 return col;
367 }
368
369 private DataTable createTerrainTable()
370 {
371 DataTable terrain = new DataTable("terrain");
372
373 createCol(terrain, "RegionUUID", typeof(String));
374 createCol(terrain, "Revision", typeof(Int32));
375 DataColumn heightField = createCol(terrain, "Heightfield", typeof(Byte[]));
376 return terrain;
377 }
378
379 private DataTable createPrimTable()
380 {
381 DataTable prims = new DataTable("prims");
382
383 createCol(prims, "UUID", typeof(String));
384 createCol(prims, "RegionUUID", typeof(String));
385 createCol(prims, "ParentID", typeof(Int32));
386 createCol(prims, "CreationDate", typeof(Int32));
387 createCol(prims, "Name", typeof(String));
388 createCol(prims, "SceneGroupID", typeof(String));
389 // various text fields
390 createCol(prims, "Text", typeof(String));
391 createCol(prims, "Description", typeof(String));
392 createCol(prims, "SitName", typeof(String));
393 createCol(prims, "TouchName", typeof(String));
394 // permissions
395 createCol(prims, "ObjectFlags", typeof(Int32));
396 createCol(prims, "CreatorID", typeof(String));
397 createCol(prims, "OwnerID", typeof(String));
398 createCol(prims, "GroupID", typeof(String));
399 createCol(prims, "LastOwnerID", typeof(String));
400 createCol(prims, "OwnerMask", typeof(Int32));
401 createCol(prims, "NextOwnerMask", typeof(Int32));
402 createCol(prims, "GroupMask", typeof(Int32));
403 createCol(prims, "EveryoneMask", typeof(Int32));
404 createCol(prims, "BaseMask", typeof(Int32));
405 // vectors
406 createCol(prims, "PositionX", typeof(Double));
407 createCol(prims, "PositionY", typeof(Double));
408 createCol(prims, "PositionZ", typeof(Double));
409 createCol(prims, "GroupPositionX", typeof(Double));
410 createCol(prims, "GroupPositionY", typeof(Double));
411 createCol(prims, "GroupPositionZ", typeof(Double));
412 createCol(prims, "VelocityX", typeof(Double));
413 createCol(prims, "VelocityY", typeof(Double));
414 createCol(prims, "VelocityZ", typeof(Double));
415 createCol(prims, "AngularVelocityX", typeof(Double));
416 createCol(prims, "AngularVelocityY", typeof(Double));
417 createCol(prims, "AngularVelocityZ", typeof(Double));
418 createCol(prims, "AccelerationX", typeof(Double));
419 createCol(prims, "AccelerationY", typeof(Double));
420 createCol(prims, "AccelerationZ", typeof(Double));
421 // quaternions
422 createCol(prims, "RotationX", typeof(Double));
423 createCol(prims, "RotationY", typeof(Double));
424 createCol(prims, "RotationZ", typeof(Double));
425 createCol(prims, "RotationW", typeof(Double));
426
427 // Add in contraints
428 prims.PrimaryKey = new DataColumn[] { prims.Columns["UUID"] };
429
430 return prims;
431 }
432
433 private DataTable createShapeTable()
434 {
435 DataTable shapes = new DataTable("primshapes");
436 createCol(shapes, "UUID", typeof(String));
437 // shape is an enum
438 createCol(shapes, "Shape", typeof(Int32));
439 // vectors
440 createCol(shapes, "ScaleX", typeof(Double));
441 createCol(shapes, "ScaleY", typeof(Double));
442 createCol(shapes, "ScaleZ", typeof(Double));
443 // paths
444 createCol(shapes, "PCode", typeof(Int32));
445 createCol(shapes, "PathBegin", typeof(Int32));
446 createCol(shapes, "PathEnd", typeof(Int32));
447 createCol(shapes, "PathScaleX", typeof(Int32));
448 createCol(shapes, "PathScaleY", typeof(Int32));
449 createCol(shapes, "PathShearX", typeof(Int32));
450 createCol(shapes, "PathShearY", typeof(Int32));
451 createCol(shapes, "PathSkew", typeof(Int32));
452 createCol(shapes, "PathCurve", typeof(Int32));
453 createCol(shapes, "PathRadiusOffset", typeof(Int32));
454 createCol(shapes, "PathRevolutions", typeof(Int32));
455 createCol(shapes, "PathTaperX", typeof(Int32));
456 createCol(shapes, "PathTaperY", typeof(Int32));
457 createCol(shapes, "PathTwist", typeof(Int32));
458 createCol(shapes, "PathTwistBegin", typeof(Int32));
459 // profile
460 createCol(shapes, "ProfileBegin", typeof(Int32));
461 createCol(shapes, "ProfileEnd", typeof(Int32));
462 createCol(shapes, "ProfileCurve", typeof(Int32));
463 createCol(shapes, "ProfileHollow", typeof(Int32));
464 createCol(shapes, "Texture", typeof(Byte[]));
465 createCol(shapes, "ExtraParams", typeof(Byte[]));
466
467 shapes.PrimaryKey = new DataColumn[] { shapes.Columns["UUID"] };
468
469 return shapes;
470 }
471
472 /***********************************************************************
473 *
474 * Convert between ADO.NET <=> OpenSim Objects
475 *
476 * These should be database independant
477 *
478 **********************************************************************/
479
480 private SceneObjectPart buildPrim(DataRow row)
481 {
482 // TODO: this doesn't work yet because something more
483 // interesting has to be done to actually get these values
484 // back out. Not enough time to figure it out yet.
485 SceneObjectPart prim = new SceneObjectPart();
486 prim.UUID = new LLUUID((String)row["UUID"]);
487 // explicit conversion of integers is required, which sort
488 // of sucks. No idea if there is a shortcut here or not.
489 prim.ParentID = Convert.ToUInt32(row["ParentID"]);
490 prim.CreationDate = Convert.ToInt32(row["CreationDate"]);
491 prim.Name = (String)row["Name"];
492 // various text fields
493 prim.Text = (String)row["Text"];
494 prim.Description = (String)row["Description"];
495 prim.SitName = (String)row["SitName"];
496 prim.TouchName = (String)row["TouchName"];
497 // permissions
498 prim.ObjectFlags = Convert.ToUInt32(row["ObjectFlags"]);
499 prim.CreatorID = new LLUUID((String)row["CreatorID"]);
500 prim.OwnerID = new LLUUID((String)row["OwnerID"]);
501 prim.GroupID = new LLUUID((String)row["GroupID"]);
502 prim.LastOwnerID = new LLUUID((String)row["LastOwnerID"]);
503 prim.OwnerMask = Convert.ToUInt32(row["OwnerMask"]);
504 prim.NextOwnerMask = Convert.ToUInt32(row["NextOwnerMask"]);
505 prim.GroupMask = Convert.ToUInt32(row["GroupMask"]);
506 prim.EveryoneMask = Convert.ToUInt32(row["EveryoneMask"]);
507 prim.BaseMask = Convert.ToUInt32(row["BaseMask"]);
508 // vectors
509 prim.OffsetPosition = new LLVector3(
510 Convert.ToSingle(row["PositionX"]),
511 Convert.ToSingle(row["PositionY"]),
512 Convert.ToSingle(row["PositionZ"])
513 );
514 prim.GroupPosition = new LLVector3(
515 Convert.ToSingle(row["GroupPositionX"]),
516 Convert.ToSingle(row["GroupPositionY"]),
517 Convert.ToSingle(row["GroupPositionZ"])
518 );
519 prim.Velocity = new LLVector3(
520 Convert.ToSingle(row["VelocityX"]),
521 Convert.ToSingle(row["VelocityY"]),
522 Convert.ToSingle(row["VelocityZ"])
523 );
524 prim.AngularVelocity = new LLVector3(
525 Convert.ToSingle(row["AngularVelocityX"]),
526 Convert.ToSingle(row["AngularVelocityY"]),
527 Convert.ToSingle(row["AngularVelocityZ"])
528 );
529 prim.Acceleration = new LLVector3(
530 Convert.ToSingle(row["AccelerationX"]),
531 Convert.ToSingle(row["AccelerationY"]),
532 Convert.ToSingle(row["AccelerationZ"])
533 );
534 // quaternions
535 prim.RotationOffset = new LLQuaternion(
536 Convert.ToSingle(row["RotationX"]),
537 Convert.ToSingle(row["RotationY"]),
538 Convert.ToSingle(row["RotationZ"]),
539 Convert.ToSingle(row["RotationW"])
540 );
541
542 return prim;
543 }
544
545 private Array serializeTerrain(double[,] val)
546 {
547 MemoryStream str = new MemoryStream(65536 * sizeof(double));
548 BinaryWriter bw = new BinaryWriter(str);
549
550 // TODO: COMPATIBILITY - Add byte-order conversions
551 for (int x = 0; x < 256; x++)
552 for (int y = 0; y < 256; y++)
553 bw.Write(val[x, y]);
554
555 return str.ToArray();
556 }
557
558 private void fillPrimRow(DataRow row, SceneObjectPart prim, LLUUID sceneGroupID, LLUUID regionUUID)
559 {
560 row["UUID"] = prim.UUID;
561 row["RegionUUID"] = regionUUID;
562 row["ParentID"] = prim.ParentID;
563 row["CreationDate"] = prim.CreationDate;
564 row["Name"] = prim.Name;
565 row["SceneGroupID"] = sceneGroupID; // the UUID of the root part for this SceneObjectGroup
566 // various text fields
567 row["Text"] = prim.Text;
568 row["Description"] = prim.Description;
569 row["SitName"] = prim.SitName;
570 row["TouchName"] = prim.TouchName;
571 // permissions
572 row["ObjectFlags"] = prim.ObjectFlags;
573 row["CreatorID"] = prim.CreatorID;
574 row["OwnerID"] = prim.OwnerID;
575 row["GroupID"] = prim.GroupID;
576 row["LastOwnerID"] = prim.LastOwnerID;
577 row["OwnerMask"] = prim.OwnerMask;
578 row["NextOwnerMask"] = prim.NextOwnerMask;
579 row["GroupMask"] = prim.GroupMask;
580 row["EveryoneMask"] = prim.EveryoneMask;
581 row["BaseMask"] = prim.BaseMask;
582 // vectors
583 row["PositionX"] = prim.OffsetPosition.X;
584 row["PositionY"] = prim.OffsetPosition.Y;
585 row["PositionZ"] = prim.OffsetPosition.Z;
586 row["GroupPositionX"] = prim.GroupPosition.X;
587 row["GroupPositionY"] = prim.GroupPosition.Y;
588 row["GroupPositionZ"] = prim.GroupPosition.Z;
589 row["VelocityX"] = prim.Velocity.X;
590 row["VelocityY"] = prim.Velocity.Y;
591 row["VelocityZ"] = prim.Velocity.Z;
592 row["AngularVelocityX"] = prim.AngularVelocity.X;
593 row["AngularVelocityY"] = prim.AngularVelocity.Y;
594 row["AngularVelocityZ"] = prim.AngularVelocity.Z;
595 row["AccelerationX"] = prim.Acceleration.X;
596 row["AccelerationY"] = prim.Acceleration.Y;
597 row["AccelerationZ"] = prim.Acceleration.Z;
598 // quaternions
599 row["RotationX"] = prim.RotationOffset.X;
600 row["RotationY"] = prim.RotationOffset.Y;
601 row["RotationZ"] = prim.RotationOffset.Z;
602 row["RotationW"] = prim.RotationOffset.W;
603 }
604
605 private PrimitiveBaseShape buildShape(DataRow row)
606 {
607 PrimitiveBaseShape s = new PrimitiveBaseShape();
608 s.Scale = new LLVector3(
609 Convert.ToSingle(row["ScaleX"]),
610 Convert.ToSingle(row["ScaleY"]),
611 Convert.ToSingle(row["ScaleZ"])
612 );
613 // paths
614 s.PCode = Convert.ToByte(row["PCode"]);
615 s.PathBegin = Convert.ToUInt16(row["PathBegin"]);
616 s.PathEnd = Convert.ToUInt16(row["PathEnd"]);
617 s.PathScaleX = Convert.ToByte(row["PathScaleX"]);
618 s.PathScaleY = Convert.ToByte(row["PathScaleY"]);
619 s.PathShearX = Convert.ToByte(row["PathShearX"]);
620 s.PathShearY = Convert.ToByte(row["PathShearY"]);
621 s.PathSkew = Convert.ToSByte(row["PathSkew"]);
622 s.PathCurve = Convert.ToByte(row["PathCurve"]);
623 s.PathRadiusOffset = Convert.ToSByte(row["PathRadiusOffset"]);
624 s.PathRevolutions = Convert.ToByte(row["PathRevolutions"]);
625 s.PathTaperX = Convert.ToSByte(row["PathTaperX"]);
626 s.PathTaperY = Convert.ToSByte(row["PathTaperY"]);
627 s.PathTwist = Convert.ToSByte(row["PathTwist"]);
628 s.PathTwistBegin = Convert.ToSByte(row["PathTwistBegin"]);
629 // profile
630 s.ProfileBegin = Convert.ToUInt16(row["ProfileBegin"]);
631 s.ProfileEnd = Convert.ToUInt16(row["ProfileEnd"]);
632 s.ProfileCurve = Convert.ToByte(row["ProfileCurve"]);
633 s.ProfileHollow = Convert.ToUInt16(row["ProfileHollow"]);
634 s.TextureEntry = (byte[])row["Texture"];
635 s.ExtraParams = (byte[])row["ExtraParams"];
636
637 return s;
638 }
639
640 private void fillShapeRow(DataRow row, SceneObjectPart prim)
641 {
642 PrimitiveBaseShape s = prim.Shape;
643 row["UUID"] = prim.UUID;
644 // shape is an enum
645 row["Shape"] = 0;
646 // vectors
647 row["ScaleX"] = s.Scale.X;
648 row["ScaleY"] = s.Scale.Y;
649 row["ScaleZ"] = s.Scale.Z;
650 // paths
651 row["PCode"] = s.PCode;
652 row["PathBegin"] = s.PathBegin;
653 row["PathEnd"] = s.PathEnd;
654 row["PathScaleX"] = s.PathScaleX;
655 row["PathScaleY"] = s.PathScaleY;
656 row["PathShearX"] = s.PathShearX;
657 row["PathShearY"] = s.PathShearY;
658 row["PathSkew"] = s.PathSkew;
659 row["PathCurve"] = s.PathCurve;
660 row["PathRadiusOffset"] = s.PathRadiusOffset;
661 row["PathRevolutions"] = s.PathRevolutions;
662 row["PathTaperX"] = s.PathTaperX;
663 row["PathTaperY"] = s.PathTaperY;
664 row["PathTwist"] = s.PathTwist;
665 row["PathTwistBegin"] = s.PathTwistBegin;
666 // profile
667 row["ProfileBegin"] = s.ProfileBegin;
668 row["ProfileEnd"] = s.ProfileEnd;
669 row["ProfileCurve"] = s.ProfileCurve;
670 row["ProfileHollow"] = s.ProfileHollow;
671 row["Texture"] = s.TextureEntry;
672 row["ExtraParams"] = s.ExtraParams;
673 }
674
675 private void addPrim(SceneObjectPart prim, LLUUID sceneGroupID, LLUUID regionUUID)
676 {
677 DataTable prims = m_dataSet.Tables["prims"];
678 DataTable shapes = m_dataSet.Tables["primshapes"];
679
680 DataRow primRow = prims.Rows.Find(prim.UUID);
681 if (primRow == null)
682 {
683 primRow = prims.NewRow();
684 fillPrimRow(primRow, prim, sceneGroupID, regionUUID);
685 prims.Rows.Add(primRow);
686 }
687 else
688 {
689 fillPrimRow(primRow, prim, sceneGroupID, regionUUID);
690 }
691
692 DataRow shapeRow = shapes.Rows.Find(prim.UUID);
693 if (shapeRow == null)
694 {
695 shapeRow = shapes.NewRow();
696 fillShapeRow(shapeRow, prim);
697 shapes.Rows.Add(shapeRow);
698 }
699 else
700 {
701 fillShapeRow(shapeRow, prim);
702 }
703 }
704
705 /***********************************************************************
706 *
707 * SQL Statement Creation Functions
708 *
709 * These functions create SQL statements for update, insert, and create.
710 * They can probably be factored later to have a db independant
711 * portion and a db specific portion
712 *
713 **********************************************************************/
714
715 private MySqlCommand createInsertCommand(string table, DataTable dt)
716 {
717 /**
718 * This is subtle enough to deserve some commentary.
719 * Instead of doing *lots* and *lots of hardcoded strings
720 * for database definitions we'll use the fact that
721 * realistically all insert statements look like "insert
722 * into A(b, c) values(:b, :c) on the parameterized query
723 * front. If we just have a list of b, c, etc... we can
724 * generate these strings instead of typing them out.
725 */
726 string[] cols = new string[dt.Columns.Count];
727 for (int i = 0; i < dt.Columns.Count; i++)
728 {
729 DataColumn col = dt.Columns[i];
730 cols[i] = col.ColumnName;
731 }
732
733 string sql = "insert into " + table + "(";
734 sql += String.Join(", ", cols);
735 // important, the first ':' needs to be here, the rest get added in the join
736 sql += ") values (?";
737 sql += String.Join(", ?", cols);
738 sql += ")";
739 MySqlCommand cmd = new MySqlCommand(sql);
740
741 // this provides the binding for all our parameters, so
742 // much less code than it used to be
743 foreach (DataColumn col in dt.Columns)
744 {
745 cmd.Parameters.Add(createMySqlParameter(col.ColumnName, col.DataType));
746 }
747 return cmd;
748 }
749
750 private MySqlCommand createUpdateCommand(string table, string pk, DataTable dt)
751 {
752 string sql = "update " + table + " set ";
753 string subsql = "";
754 foreach (DataColumn col in dt.Columns)
755 {
756 if (subsql.Length > 0)
757 {
758 // a map function would rock so much here
759 subsql += ", ";
760 }
761 subsql += col.ColumnName + "=?" + col.ColumnName;
762 }
763 sql += subsql;
764 sql += " where " + pk;
765 MySqlCommand cmd = new MySqlCommand(sql);
766
767 // this provides the binding for all our parameters, so
768 // much less code than it used to be
769
770 foreach (DataColumn col in dt.Columns)
771 {
772 cmd.Parameters.Add(createMySqlParameter(col.ColumnName, col.DataType));
773 }
774 return cmd;
775 }
776
777
778 private string defineTable(DataTable dt)
779 {
780 string sql = "create table " + dt.TableName + "(";
781 string subsql = "";
782 foreach (DataColumn col in dt.Columns)
783 {
784 if (subsql.Length > 0)
785 {
786 // a map function would rock so much here
787 subsql += ",\n";
788 }
789 subsql += col.ColumnName + " " + MySqlType(col.DataType);
790 if (dt.PrimaryKey.Length > 0 && col == dt.PrimaryKey[0])
791 {
792 subsql += " primary key";
793 }
794 }
795 sql += subsql;
796 sql += ")";
797 return sql;
798 }
799
800 /***********************************************************************
801 *
802 * Database Binding functions
803 *
804 * These will be db specific due to typing, and minor differences
805 * in databases.
806 *
807 **********************************************************************/
808
809 ///<summary>
810 /// This is a convenience function that collapses 5 repetitive
811 /// lines for defining MySqlParameters to 2 parameters:
812 /// column name and database type.
813 ///
814 /// It assumes certain conventions like ?param as the param
815 /// name to replace in parametrized queries, and that source
816 /// version is always current version, both of which are fine
817 /// for us.
818 ///</summary>
819 ///<returns>a built MySql parameter</returns>
820 private MySqlParameter createMySqlParameter(string name, Type type)
821 {
822 MySqlParameter param = new MySqlParameter();
823 param.ParameterName = "?" + name;
824 param.DbType = dbtypeFromType(type);
825 param.SourceColumn = name;
826 param.SourceVersion = DataRowVersion.Current;
827 return param;
828 }
829
830 private MySqlParameter createParamWithValue(string name, Type type, Object o)
831 {
832 MySqlParameter param = createMySqlParameter(name, type);
833 param.Value = o;
834 return param;
835 }
836
837 private void SetupPrimCommands(MySqlDataAdapter da, MySqlConnection conn)
838 {
839 MySqlCommand insertCommand = createInsertCommand("prims", m_primTable);
840 insertCommand.Connection = conn;
841 da.InsertCommand = insertCommand;
842
843 MySqlCommand updateCommand = createUpdateCommand("prims", "UUID=?UUID", m_primTable);
844 updateCommand.Connection = conn;
845 da.UpdateCommand = updateCommand;
846
847 MySqlCommand delete = new MySqlCommand("delete from prims where UUID=?UUID");
848 delete.Parameters.Add(createMySqlParameter("UUID", typeof(String)));
849 delete.Connection = conn;
850 da.DeleteCommand = delete;
851 }
852
853 private void SetupTerrainCommands(MySqlDataAdapter da, MySqlConnection conn)
854 {
855 da.InsertCommand = createInsertCommand("terrain", m_dataSet.Tables["terrain"]);
856 da.InsertCommand.Connection = conn;
857 }
858
859 private void SetupShapeCommands(MySqlDataAdapter da, MySqlConnection conn)
860 {
861 da.InsertCommand = createInsertCommand("primshapes", m_dataSet.Tables["primshapes"]);
862 da.InsertCommand.Connection = conn;
863
864 da.UpdateCommand = createUpdateCommand("primshapes", "UUID=?UUID", m_dataSet.Tables["primshapes"]);
865 da.UpdateCommand.Connection = conn;
866
867 MySqlCommand delete = new MySqlCommand("delete from primshapes where UUID = ?UUID");
868 delete.Parameters.Add(createMySqlParameter("UUID", typeof(String)));
869 delete.Connection = conn;
870 da.DeleteCommand = delete;
871 }
872
873 private void InitDB(MySqlConnection conn)
874 {
875 string createPrims = defineTable(createPrimTable());
876 string createShapes = defineTable(createShapeTable());
877 string createTerrain = defineTable(createTerrainTable());
878
879 MySqlCommand pcmd = new MySqlCommand(createPrims, conn);
880 MySqlCommand scmd = new MySqlCommand(createShapes, conn);
881 MySqlCommand tcmd = new MySqlCommand(createTerrain, conn);
882
883 if (conn.State != ConnectionState.Open)
884 {
885 conn.Open();
886 }
887
888 try
889 {
890 pcmd.ExecuteNonQuery();
891 }
892 catch (MySqlException)
893 {
894 MainLog.Instance.Warn("MySql", "Primitives Table Already Exists");
895 }
896
897 try
898 {
899 scmd.ExecuteNonQuery();
900 }
901 catch (MySqlException)
902 {
903 MainLog.Instance.Warn("MySql", "Shapes Table Already Exists");
904 }
905
906 try
907 {
908 tcmd.ExecuteNonQuery();
909 }
910 catch (MySqlException)
911 {
912 MainLog.Instance.Warn("MySql", "Terrain Table Already Exists");
913 }
914
915 conn.Close();
916 }
917
918 private bool TestTables(MySqlConnection conn)
919 {
920 MySqlCommand primSelectCmd = new MySqlCommand(m_primSelect, conn);
921 MySqlDataAdapter pDa = new MySqlDataAdapter(primSelectCmd);
922 MySqlCommand shapeSelectCmd = new MySqlCommand(m_shapeSelect, conn);
923 MySqlDataAdapter sDa = new MySqlDataAdapter(shapeSelectCmd);
924 MySqlCommand terrainSelectCmd = new MySqlCommand(m_terrainSelect, conn);
925 MySqlDataAdapter tDa = new MySqlDataAdapter(terrainSelectCmd);
926
927 DataSet tmpDS = new DataSet();
928 try
929 {
930 pDa.Fill(tmpDS, "prims");
931 sDa.Fill(tmpDS, "primshapes");
932 tDa.Fill(tmpDS, "terrain");
933 }
934 catch (MySqlException)
935 {
936 MainLog.Instance.Verbose("DATASTORE", "MySql Database doesn't exist... creating");
937 InitDB(conn);
938 }
939
940 pDa.Fill(tmpDS, "prims");
941 sDa.Fill(tmpDS, "primshapes");
942 tDa.Fill(tmpDS, "terrain");
943
944 foreach (DataColumn col in createPrimTable().Columns)
945 {
946 if (!tmpDS.Tables["prims"].Columns.Contains(col.ColumnName))
947 {
948 MainLog.Instance.Verbose("DATASTORE", "Missing required column:" + col.ColumnName);
949 return false;
950 }
951 }
952 foreach (DataColumn col in createShapeTable().Columns)
953 {
954 if (!tmpDS.Tables["primshapes"].Columns.Contains(col.ColumnName))
955 {
956 MainLog.Instance.Verbose("DATASTORE", "Missing required column:" + col.ColumnName);
957 return false;
958 }
959 }
960 foreach (DataColumn col in createTerrainTable().Columns)
961 {
962 if (!tmpDS.Tables["terrain"].Columns.Contains(col.ColumnName))
963 {
964 MainLog.Instance.Verbose("DATASTORE", "Missing require column:" + col.ColumnName);
965 return false;
966 }
967 }
968 return true;
969 }
970
971 /***********************************************************************
972 *
973 * Type conversion functions
974 *
975 **********************************************************************/
976
977 private DbType dbtypeFromType(Type type)
978 {
979 if (type == typeof(String))
980 {
981 return DbType.String;
982 }
983 else if (type == typeof(Int32))
984 {
985 return DbType.Int32;
986 }
987 else if (type == typeof(Double))
988 {
989 return DbType.Double;
990 }
991 else if (type == typeof(Byte))
992 {
993 return DbType.Byte;
994 }
995 else if (type == typeof(Double))
996 {
997 return DbType.Double;
998 }
999 else if (type == typeof(Byte[]))
1000 {
1001 return DbType.Binary;
1002 }
1003 else
1004 {
1005 return DbType.String;
1006 }
1007 }
1008
1009 // this is something we'll need to implement for each db
1010 // slightly differently.
1011 private string MySqlType(Type type)
1012 {
1013 if (type == typeof(String))
1014 {
1015 return "varchar(255)";
1016 }
1017 else if (type == typeof(Int32))
1018 {
1019 return "integer";
1020 }
1021 else if (type == typeof(Double))
1022 {
1023 return "float";
1024 }
1025 else if (type == typeof(Byte[]))
1026 {
1027 return "longblob";
1028 }
1029 else
1030 {
1031 return "string";
1032 }
1033 }
1034 }
1035}
generated by cgit v1.1 at 2024-11-17 16:24:09 +0000