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