Thank you MrMonkE for a patch that seems to bring the MSSQL data layer up to speed with 0.7.x.

11 files changed, 199 insertions, 32 deletions

diff --git a/OpenSim/Data/MSSQL/MSSQLGenericTableHandler.cs b/OpenSim/Data/MSSQL/MSSQLGenericTableHandler.cs
index 6a5d6eb..f5492b3 100644
--- a/OpenSim/Data/MSSQL/MSSQLGenericTableHandler.cs
+++ b/OpenSim/Data/MSSQL/MSSQLGenericTableHandler.cs
@@ -56,15 +56,15 @@ namespace OpenSim.Data.MSSQL
                 string realm, string storeName)
         {
             m_Realm = realm;
+            
+            m_ConnectionString = connectionString;
 
             if (storeName != String.Empty)
             {
-                Assembly assem = GetType().Assembly;
-                m_ConnectionString = connectionString;
                 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
                 {
                     conn.Open();
-                    Migration m = new Migration(conn, assem, storeName);
+                    Migration m = new Migration(conn, GetType().Assembly, storeName);
                     m.Update();
                 }
 
diff --git a/OpenSim/Data/MSSQL/MSSQLMigration.cs b/OpenSim/Data/MSSQL/MSSQLMigration.cs
index aea31c9..cd395b8 100644
--- a/OpenSim/Data/MSSQL/MSSQLMigration.cs
+++ b/OpenSim/Data/MSSQL/MSSQLMigration.cs
@@ -61,7 +61,8 @@ namespace OpenSim.Data.MSSQL
                 }
                 catch
                 {
-                    // Something went wrong, so we're version 0
+                    // Return -1 to indicate table does not exist
+                    return -1;
                 }
             }
             return version;
diff --git a/OpenSim/Data/MSSQL/MSSQLSimulationData.cs b/OpenSim/Data/MSSQL/MSSQLSimulationData.cs
index 80ec65e..5155e56 100644
--- a/OpenSim/Data/MSSQL/MSSQLSimulationData.cs
+++ b/OpenSim/Data/MSSQL/MSSQLSimulationData.cs
@@ -492,12 +492,11 @@ ELSE
             using (SqlConnection conn = new SqlConnection(m_connectionString))
             using (SqlCommand cmd = new SqlCommand(sql, conn))
             {
+                conn.Open();
                 foreach (TaskInventoryItem taskItem in items)
                 {
                     cmd.Parameters.AddRange(CreatePrimInventoryParameters(taskItem));
-                    conn.Open();
                     cmd.ExecuteNonQuery();
-
                     cmd.Parameters.Clear();
                 }
             }
@@ -1154,9 +1153,9 @@ VALUES
             PrimitiveBaseShape baseShape = new PrimitiveBaseShape();
 
             baseShape.Scale = new Vector3(
-                        Convert.ToSingle(shapeRow["ScaleX"]),
-                        Convert.ToSingle(shapeRow["ScaleY"]),
-                        Convert.ToSingle(shapeRow["ScaleZ"]));
+                        (float)Convert.ToDouble(shapeRow["ScaleX"]),
+                        (float)Convert.ToDouble(shapeRow["ScaleY"]),
+                        (float)Convert.ToDouble(shapeRow["ScaleZ"]));
 
             // paths
             baseShape.PCode = Convert.ToByte(shapeRow["PCode"]);
@@ -1193,8 +1192,11 @@ VALUES
             {
             }
 
-            if (!(shapeRow["Media"] is System.DBNull))
+            if (!(shapeRow["Media"] is System.DBNull) )
+            {
                 baseShape.Media = PrimitiveBaseShape.MediaList.FromXml((string)shapeRow["Media"]);
+            }
+                
 
             return baseShape;
         }
@@ -1573,7 +1575,16 @@ VALUES
             parameters.Add(_Database.CreateParameter("Texture", s.TextureEntry));
             parameters.Add(_Database.CreateParameter("ExtraParams", s.ExtraParams));
             parameters.Add(_Database.CreateParameter("State", s.State));
-            parameters.Add(_Database.CreateParameter("Media", null == s.Media ? null : s.Media.ToXml()));
+
+            if(null == s.Media )
+            {
+                parameters.Add(_Database.CreateParameter("Media", DBNull.Value));    
+            }
+            else
+            {
+                parameters.Add(_Database.CreateParameter("Media", s.Media.ToXml()));    
+            }
+            
 
             return parameters.ToArray();
         }
diff --git a/OpenSim/Data/MSSQL/Resources/AuthStore.migrations b/OpenSim/Data/MSSQL/Resources/AuthStore.migrations
index 5b90ca3..eb91296 100644
--- a/OpenSim/Data/MSSQL/Resources/AuthStore.migrations
+++ b/OpenSim/Data/MSSQL/Resources/AuthStore.migrations
@@ -22,7 +22,11 @@ COMMIT
 
 BEGIN TRANSACTION
 
-INSERT INTO auth (UUID, passwordHash, passwordSalt, webLoginKey, accountType) SELECT [UUID] AS UUID, [passwordHash] AS passwordHash, [passwordSalt] AS passwordSalt, [webLoginKey] AS webLoginKey, 'UserAccount' as [accountType] FROM users;
+IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[users]') AND type in (N'U'))
+        INSERT INTO auth (UUID, passwordHash, passwordSalt, webLoginKey, accountType) SELECT [UUID] AS UUID, [passwordHash] AS passwordHash, [passwordSalt] AS passwordSalt, [webLoginKey] AS webLoginKey, 'UserAccount' as [accountType] FROM users;
+
+COMMIT
+
+
 
 
-COMMIT
\ No newline at end of file
diff --git a/OpenSim/Data/MSSQL/Resources/Avatar.migrations b/OpenSim/Data/MSSQL/Resources/Avatar.migrations
index 759e939..5364153 100644
--- a/OpenSim/Data/MSSQL/Resources/Avatar.migrations
+++ b/OpenSim/Data/MSSQL/Resources/Avatar.migrations
@@ -13,5 +13,28 @@ PRIMARY KEY CLUSTERED
 ) ON [PRIMARY]
 
 
+COMMIT
+
+:VERSION 2
+
+BEGIN TRANSACTION
+
+CREATE TABLE dbo.Tmp_Avatars
+        (
+        PrincipalID uniqueidentifier NOT NULL,
+        [Name] varchar(32) NOT NULL, 
+        Value text NOT NULL DEFAULT '',
+        )  ON [PRIMARY]
+         TEXTIMAGE_ON [PRIMARY]
+
+IF EXISTS(SELECT * FROM dbo.Avatars)
+         EXEC('INSERT INTO dbo.Tmp_Avatars (PrincipalID, Name, Value)
+                SELECT PrincipalID, CONVERT(text, Name), Value FROM dbo.Avatars WITH (HOLDLOCK TABLOCKX)')
+
+DROP TABLE dbo.Avatars
+
+EXECUTE sp_rename N'dbo.Tmp_Avatars', N'Avatars', 'OBJECT' 
+
+COMMIT
+
 
-COMMIT
\ No newline at end of file
diff --git a/OpenSim/Data/MSSQL/Resources/FriendsStore.migrations b/OpenSim/Data/MSSQL/Resources/FriendsStore.migrations
index f981a91..4d8ab0f 100644
--- a/OpenSim/Data/MSSQL/Resources/FriendsStore.migrations
+++ b/OpenSim/Data/MSSQL/Resources/FriendsStore.migrations
@@ -15,6 +15,8 @@ COMMIT
 
 BEGIN TRANSACTION
 
-INSERT INTO Friends (PrincipalID, Friend, Flags, Offered) SELECT [ownerID], [friendID], [friendPerms], 0 FROM userfriends;
+IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[userfriends]') AND type in (N'U'))
+INSERT INTO Friends (PrincipalID, Friend, Flags, Offered) 
+SELECT [ownerID], [friendID], [friendPerms], 0 FROM userfriends;
 
 COMMIT
\ No newline at end of file
diff --git a/OpenSim/Data/MSSQL/Resources/GridStore.migrations b/OpenSim/Data/MSSQL/Resources/GridStore.migrations
index d2ca27a..c6342fc 100644
--- a/OpenSim/Data/MSSQL/Resources/GridStore.migrations
+++ b/OpenSim/Data/MSSQL/Resources/GridStore.migrations
@@ -222,4 +222,17 @@ ALTER TABLE [regions] ADD [Token] varchar(255) NOT NULL DEFAULT 0;
 
 COMMIT
 
+:VERSION 8
 
+BEGIN TRANSACTION
+ALTER TABLE regions ALTER COLUMN regionName VarChar(128) 
+
+DROP INDEX IX_regions_name ON dbo.regions
+ALTER TABLE regions ALTER COLUMN regionName VarChar(128) null
+
+CREATE NONCLUSTERED INDEX IX_regions_name ON dbo.regions
+        (
+        regionName
+        ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
+
+COMMIT
\ No newline at end of file
diff --git a/OpenSim/Data/MSSQL/Resources/InventoryStore.migrations b/OpenSim/Data/MSSQL/Resources/InventoryStore.migrations
index e2a8d57..4a3cb27 100644
--- a/OpenSim/Data/MSSQL/Resources/InventoryStore.migrations
+++ b/OpenSim/Data/MSSQL/Resources/InventoryStore.migrations
@@ -238,7 +238,10 @@ alter table inventoryitems
 
 COMMIT  
    
- 
-
-
+:VERSION 8
 
+ALTER TABLE inventoryitems
+ADD CONSTRAINT DF_inventoryitems_creatorID 
+DEFAULT '00000000-0000-0000-0000-000000000000' FOR creatorID
+   
+:GO
\ No newline at end of file
diff --git a/OpenSim/Data/MSSQL/Resources/Presence.migrations b/OpenSim/Data/MSSQL/Resources/Presence.migrations
index 35f78e1..bcb6328 100644
--- a/OpenSim/Data/MSSQL/Resources/Presence.migrations
+++ b/OpenSim/Data/MSSQL/Resources/Presence.migrations
@@ -7,14 +7,7 @@ CREATE TABLE [Presence] (
 [RegionID] uniqueidentifier NOT NULL, 
 [SessionID] uniqueidentifier NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
 [SecureSessionID] uniqueidentifier NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
-[Online] char(5) NOT NULL DEFAULT 'false',
-[Login] char(16) NOT NULL DEFAULT '0',
-[Logout] char(16) NOT NULL DEFAULT '0',
-[Position] char(64) NOT NULL DEFAULT '<0,0,0>',
-[LookAt] char(64) NOT NULL DEFAULT '<0,0,0>',
-[HomeRegionID] uniqueidentifier NOT NULL,
-[HomePosition] CHAR(64) NOT NULL DEFAULT '<0,0,0>',
-[HomeLookAt] CHAR(64) NOT NULL DEFAULT '<0,0,0>',
+
 )
  ON [PRIMARY]
 
@@ -27,4 +20,12 @@ BEGIN TRANSACTION
 CREATE UNIQUE INDEX SessionID ON Presence(SessionID);
 CREATE INDEX UserID ON Presence(UserID);
 
+COMMIT
+
+:VERSION 2
+
+BEGIN TRANSACTION
+
+ALTER TABLE Presence ADD LastSeen DateTime
+
 COMMIT
\ No newline at end of file
diff --git a/OpenSim/Data/MSSQL/Resources/RegionStore.migrations b/OpenSim/Data/MSSQL/Resources/RegionStore.migrations
index e2e8cbb..340b63d 100644
--- a/OpenSim/Data/MSSQL/Resources/RegionStore.migrations
+++ b/OpenSim/Data/MSSQL/Resources/RegionStore.migrations
@@ -1,4 +1,3 @@
-
 :VERSION 1
 
 CREATE TABLE [dbo].[prims](
@@ -926,11 +925,121 @@ ALTER TABLE regionsettings ADD loaded_creation_datetime int NOT NULL default 0
 COMMIT
 
 :VERSION 24
--- Added post 0.7
 
 BEGIN TRANSACTION
 
-ALTER TABLE prims ADD COLUMN MediaURL varchar(255)
-ALTER TABLE primshapes ADD COLUMN Media TEXT
+ALTER TABLE prims ADD  MediaURL varchar(255)
+ALTER TABLE primshapes ADD Media TEXT NULL
+
+COMMIT
+
+:VERSION 25
+
+BEGIN TRANSACTION
+CREATE TABLE "regionwindlight" (
+  "region_id" varchar(36) NOT NULL DEFAULT '000000-0000-0000-0000-000000000000',
+  "water_color_r" [float] NOT NULL DEFAULT '4.000000',
+  "water_color_g" [float] NOT NULL DEFAULT '38.000000',
+  "water_color_b" [float] NOT NULL DEFAULT '64.000000',
+  "water_fog_density_exponent" [float] NOT NULL DEFAULT '4.0',
+  "underwater_fog_modifier" [float] NOT NULL DEFAULT '0.25',
+  "reflection_wavelet_scale_1" [float] NOT NULL DEFAULT '2.0',
+  "reflection_wavelet_scale_2" [float] NOT NULL DEFAULT '2.0',
+  "reflection_wavelet_scale_3" [float] NOT NULL DEFAULT '2.0',
+  "fresnel_scale" [float] NOT NULL DEFAULT '0.40',
+  "fresnel_offset" [float] NOT NULL DEFAULT '0.50',
+  "refract_scale_above" [float] NOT NULL DEFAULT '0.03',
+  "refract_scale_below" [float] NOT NULL DEFAULT '0.20',
+  "blur_multiplier" [float] NOT NULL DEFAULT '0.040',
+  "big_wave_direction_x" [float] NOT NULL DEFAULT '1.05',
+  "big_wave_direction_y" [float] NOT NULL DEFAULT '-0.42',
+  "little_wave_direction_x" [float] NOT NULL DEFAULT '1.11',
+  "little_wave_direction_y" [float] NOT NULL DEFAULT '-1.16',
+  "normal_map_texture" varchar(36) NOT NULL DEFAULT '822ded49-9a6c-f61c-cb89-6df54f42cdf4',
+  "horizon_r" [float] NOT NULL DEFAULT '0.25',
+  "horizon_g" [float] NOT NULL DEFAULT '0.25',
+  "horizon_b" [float] NOT NULL DEFAULT '0.32',
+  "horizon_i" [float] NOT NULL DEFAULT '0.32',
+  "haze_horizon" [float] NOT NULL DEFAULT '0.19',
+  "blue_density_r" [float] NOT NULL DEFAULT '0.12',
+  "blue_density_g" [float] NOT NULL DEFAULT '0.22',
+  "blue_density_b" [float] NOT NULL DEFAULT '0.38',
+  "blue_density_i" [float] NOT NULL DEFAULT '0.38',
+  "haze_density" [float] NOT NULL DEFAULT '0.70',
+  "density_multiplier" [float] NOT NULL DEFAULT '0.18',
+  "distance_multiplier" [float] NOT NULL DEFAULT '0.8',
+  "max_altitude" int NOT NULL DEFAULT '1605',
+  "sun_moon_color_r" [float] NOT NULL DEFAULT '0.24',
+  "sun_moon_color_g" [float] NOT NULL DEFAULT '0.26',
+  "sun_moon_color_b" [float] NOT NULL DEFAULT '0.30',
+  "sun_moon_color_i" [float] NOT NULL DEFAULT '0.30',
+  "sun_moon_position" [float] NOT NULL DEFAULT '0.317',
+  "ambient_r" [float] NOT NULL DEFAULT '0.35',
+  "ambient_g" [float] NOT NULL DEFAULT '0.35',
+  "ambient_b" [float] NOT NULL DEFAULT '0.35',
+  "ambient_i" [float] NOT NULL DEFAULT '0.35',
+  "east_angle" [float] NOT NULL DEFAULT '0.00',
+  "sun_glow_focus" [float] NOT NULL DEFAULT '0.10',
+  "sun_glow_size" [float] NOT NULL DEFAULT '1.75',
+  "scene_gamma" [float] NOT NULL DEFAULT '1.00',
+  "star_brightness" [float] NOT NULL DEFAULT '0.00',
+  "cloud_color_r" [float] NOT NULL DEFAULT '0.41',
+  "cloud_color_g" [float] NOT NULL DEFAULT '0.41',
+  "cloud_color_b" [float] NOT NULL DEFAULT '0.41',
+  "cloud_color_i" [float] NOT NULL DEFAULT '0.41',
+  "cloud_x" [float] NOT NULL DEFAULT '1.00',
+  "cloud_y" [float] NOT NULL DEFAULT '0.53',
+  "cloud_density" [float] NOT NULL DEFAULT '1.00',
+  "cloud_coverage" [float] NOT NULL DEFAULT '0.27',
+  "cloud_scale" [float] NOT NULL DEFAULT '0.42',
+  "cloud_detail_x" [float] NOT NULL DEFAULT '1.00',
+  "cloud_detail_y" [float] NOT NULL DEFAULT '0.53',
+  "cloud_detail_density" [float] NOT NULL DEFAULT '0.12',
+  "cloud_scroll_x" [float] NOT NULL DEFAULT '0.20',
+  "cloud_scroll_x_lock" tinyint NOT NULL DEFAULT '0',
+  "cloud_scroll_y" [float] NOT NULL DEFAULT '0.01',
+  "cloud_scroll_y_lock" tinyint NOT NULL DEFAULT '0',
+  "draw_classic_clouds" tinyint NOT NULL DEFAULT '1',
+  PRIMARY KEY ("region_id")
+)
+
+COMMIT TRANSACTION
+
+:VERSION 26
+
+BEGIN TRANSACTION
+
+ALTER TABLE regionsettings ADD map_tile_ID CHAR(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'
+
+COMMIT
+
+:VERSION 27         #---------------------
 
-COMMIT
\ No newline at end of file
+BEGIN TRANSACTION
+ALTER TABLE land ADD MediaType VARCHAR(32) NOT NULL DEFAULT 'none/none' 
+ALTER TABLE land ADD MediaDescription VARCHAR(255) NOT NULL DEFAULT ''
+ALTER TABLE land ADD MediaSize VARCHAR(16) NOT NULL DEFAULT '0,0'
+ALTER TABLE land ADD MediaLoop bit NOT NULL DEFAULT 0
+ALTER TABLE land ADD ObscureMusic bit NOT NULL DEFAULT 0
+ALTER TABLE land ADD ObscureMedia bit NOT NULL DEFAULT 0
+COMMIT
+
+:VERSION 28         #---------------------
+
+BEGIN TRANSACTION
+
+ALTER TABLE prims 
+ADD CONSTRAINT DF_prims_CreatorID 
+DEFAULT '00000000-0000-0000-0000-000000000000' 
+FOR CreatorID
+
+ALTER TABLE prims  ALTER COLUMN CreatorID uniqueidentifier NOT NULL
+
+ALTER TABLE primitems
+ADD CONSTRAINT DF_primitems_CreatorID 
+DEFAULT '00000000-0000-0000-0000-000000000000' 
+FOR CreatorID
+
+ALTER TABLE primitems ALTER COLUMN CreatorID uniqueidentifier NOT NULL
+
+COMMIT
diff --git a/OpenSim/Data/MSSQL/Resources/UserAccount.migrations b/OpenSim/Data/MSSQL/Resources/UserAccount.migrations
index 8534e23..a81704d 100644
--- a/OpenSim/Data/MSSQL/Resources/UserAccount.migrations
+++ b/OpenSim/Data/MSSQL/Resources/UserAccount.migrations
@@ -19,7 +19,7 @@ CREATE TABLE [UserAccounts] (
 :VERSION 2
 
 BEGIN TRANSACTION
-
+IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[users]') AND type in (N'U'))
 INSERT INTO UserAccounts (PrincipalID, ScopeID, FirstName, LastName, Email, ServiceURLs, Created) SELECT [UUID] AS PrincipalID, '00000000-0000-0000-0000-000000000000' AS ScopeID, 
 username AS FirstName, 
 lastname AS LastName,