14 files changed, 141 insertions, 94 deletions

diff --git a/OpenSim/Data/MSSQL/MSSQLAssetData.cs b/OpenSim/Data/MSSQL/MSSQLAssetData.cs
index 8475b22..c7488d8 100644
--- a/OpenSim/Data/MSSQL/MSSQLAssetData.cs
+++ b/OpenSim/Data/MSSQL/MSSQLAssetData.cs
@@ -121,15 +121,16 @@ namespace OpenSim.Data.MSSQL
                     if (reader.Read())
                     {
                         AssetBase asset = new AssetBase(
-                            new UUID((Guid)reader["id"]),
+                            DBGuid.FromDB(reader["id"]),
                             (string)reader["name"],
                             Convert.ToSByte(reader["assetType"]),
-                            String.Empty
+                            reader["creatorid"].ToString()
                         );
                         // Region Main
                         asset.Description = (string)reader["description"];
                         asset.Local = Convert.ToBoolean(reader["local"]);
                         asset.Temporary = Convert.ToBoolean(reader["temporary"]);
+                        asset.Flags = (AssetFlags)(Convert.ToInt32(reader["asset_flags"]));
                         asset.Data = (byte[])reader["data"];
                         return asset;
                     }
@@ -144,26 +145,19 @@ namespace OpenSim.Data.MSSQL
         /// <param name="asset">the asset</param>
         override public void StoreAsset(AssetBase asset)
         {
-            if (ExistsAsset(asset.FullID))
-                UpdateAsset(asset);
-            else
-                InsertAsset(asset);
-        }
-
-
-        private void InsertAsset(AssetBase asset)
-        {
-            if (ExistsAsset(asset.FullID))
-            {
-                return;
-            }
-            
-            string sql = @"INSERT INTO assets
-                            ([id], [name], [description], [assetType], [local], 
-                             [temporary], [create_time], [access_time], [data])
-                           VALUES
-                            (@id, @name, @description, @assetType, @local, 
-                             @temporary, @create_time, @access_time, @data)";
+           
+            string sql =
+                @"IF EXISTS(SELECT * FROM assets WHERE id=@id) 
+                    UPDATE assets set name = @name, description = @description, assetType = @assetType,
+                            local = @local, temporary = @temporary, creatorid = @creatorid, data = @data
+                           WHERE id=@id
+                   ELSE
+                    INSERT INTO assets
+                    ([id], [name], [description], [assetType], [local], 
+                     [temporary], [create_time], [access_time], [creatorid], [asset_flags], [data])
+                    VALUES
+                    (@id, @name, @description, @assetType, @local, 
+                     @temporary, @create_time, @access_time, @creatorid, @asset_flags, @data)";
             
             string assetName = asset.Name;
             if (asset.Name.Length > 64)
@@ -191,6 +185,8 @@ namespace OpenSim.Data.MSSQL
                 command.Parameters.Add(m_database.CreateParameter("temporary", asset.Temporary));
                 command.Parameters.Add(m_database.CreateParameter("access_time", now));
                 command.Parameters.Add(m_database.CreateParameter("create_time", now));
+                command.Parameters.Add(m_database.CreateParameter("asset_flags", (int)asset.Flags));
+                command.Parameters.Add(m_database.CreateParameter("creatorid", asset.Metadata.CreatorID));
                 command.Parameters.Add(m_database.CreateParameter("data", asset.Data));
                 conn.Open();
                 try
@@ -199,57 +195,11 @@ namespace OpenSim.Data.MSSQL
                 }
                 catch(Exception e)
                 {
-                    m_log.Error("[ASSET DB]: Error inserting item :" + e.Message);
+                    m_log.Error("[ASSET DB]: Error storing item :" + e.Message);
                 }
             }
         }
 
-        /// <summary>
-        /// Update asset in m_database
-        /// </summary>
-        /// <param name="asset">the asset</param>
-        private void UpdateAsset(AssetBase asset)
-        {
-            string sql = @"UPDATE assets set id = @id, name = @name, description = @description, assetType = @assetType,
-                            local = @local, temporary = @temporary, data = @data
-                           WHERE id = @keyId;";
-
-            string assetName = asset.Name;
-            if (asset.Name.Length > 64)
-            {
-                assetName = asset.Name.Substring(0, 64);
-                m_log.Warn("[ASSET DB]: Name field truncated from " + asset.Name.Length + " to " + assetName.Length + " characters on update");
-            }
-            
-            string assetDescription = asset.Description;
-            if (asset.Description.Length > 64)
-            {
-                assetDescription = asset.Description.Substring(0, 64);
-                m_log.Warn("[ASSET DB]: Description field truncated from " + asset.Description.Length + " to " + assetDescription.Length + " characters on update");
-            }
-            
-            using (SqlConnection conn = new SqlConnection(m_connectionString))
-            using (SqlCommand command = new SqlCommand(sql, conn))
-            {
-                command.Parameters.Add(m_database.CreateParameter("id", asset.FullID));
-                command.Parameters.Add(m_database.CreateParameter("name", assetName));
-                command.Parameters.Add(m_database.CreateParameter("description", assetDescription));
-                command.Parameters.Add(m_database.CreateParameter("assetType", asset.Type));
-                command.Parameters.Add(m_database.CreateParameter("local", asset.Local));
-                command.Parameters.Add(m_database.CreateParameter("temporary", asset.Temporary));
-                command.Parameters.Add(m_database.CreateParameter("data", asset.Data));
-                command.Parameters.Add(m_database.CreateParameter("@keyId", asset.FullID));
-                conn.Open();
-                try
-                {
-                    command.ExecuteNonQuery();
-                }
-                catch (Exception e)
-                {
-                    m_log.Error(e.ToString());
-                }
-            }
-        }
 
 // Commented out since currently unused - this probably should be called in GetAsset()
 //        private void UpdateAccessTime(AssetBase asset)
@@ -295,26 +245,34 @@ namespace OpenSim.Data.MSSQL
         public override List<AssetMetadata> FetchAssetMetadataSet(int start, int count)
         {
             List<AssetMetadata> retList = new List<AssetMetadata>(count);
-            string sql = @"SELECT (name,description,assetType,temporary,id), Row = ROW_NUMBER() 
-                            OVER (ORDER BY (some column to order by)) 
-                            WHERE Row >= @Start AND Row < @Start + @Count";
+            string sql = @"WITH OrderedAssets AS
+                (
+                    SELECT id, name, description, assetType, temporary, creatorid,
+                    RowNumber = ROW_NUMBER() OVER (ORDER BY id)
+                    FROM assets 
+                ) 
+                SELECT * 
+                FROM OrderedAssets
+                WHERE RowNumber BETWEEN @start AND @stop;";
 
             using (SqlConnection conn = new SqlConnection(m_connectionString))
             using (SqlCommand cmd = new SqlCommand(sql, conn))
             {
                 cmd.Parameters.Add(m_database.CreateParameter("start", start));
-                cmd.Parameters.Add(m_database.CreateParameter("count", count));
+                cmd.Parameters.Add(m_database.CreateParameter("stop", start + count - 1));
                 conn.Open();
                 using (SqlDataReader reader = cmd.ExecuteReader())
                 {
                     while (reader.Read())
                     {
                         AssetMetadata metadata = new AssetMetadata();
-                        metadata.FullID = new UUID((Guid)reader["id"]);
+                        metadata.FullID = DBGuid.FromDB(reader["id"]);
                         metadata.Name = (string)reader["name"];
                         metadata.Description = (string)reader["description"];
                         metadata.Type = Convert.ToSByte(reader["assetType"]);
                         metadata.Temporary = Convert.ToBoolean(reader["temporary"]);
+                        metadata.CreatorID = (string)reader["creatorid"];
+                        retList.Add(metadata);
                     }
                 }
             }
diff --git a/OpenSim/Data/MSSQL/MSSQLEstateData.cs b/OpenSim/Data/MSSQL/MSSQLEstateData.cs
index 66931e2..80bf106 100644
--- a/OpenSim/Data/MSSQL/MSSQLEstateData.cs
+++ b/OpenSim/Data/MSSQL/MSSQLEstateData.cs
@@ -37,7 +37,7 @@ using OpenSim.Region.Framework.Interfaces;
 
 namespace OpenSim.Data.MSSQL
 {
-    public class MSSQLEstateData : IEstateDataStore
+    public class MSSQLEstateStore : IEstateDataStore
     {
         private const string _migrationStore = "EstateStore";
 
diff --git a/OpenSim/Data/MSSQL/MSSQLInventoryData.cs b/OpenSim/Data/MSSQL/MSSQLInventoryData.cs
index 4815700..4d06377 100644
--- a/OpenSim/Data/MSSQL/MSSQLInventoryData.cs
+++ b/OpenSim/Data/MSSQL/MSSQLInventoryData.cs
@@ -111,6 +111,9 @@ namespace OpenSim.Data.MSSQL
         /// <returns>A list of folder objects</returns>
         public List<InventoryFolderBase> getUserRootFolders(UUID user)
         {
+            if (user == UUID.Zero)
+                return new List<InventoryFolderBase>();
+
             return getInventoryFolders(UUID.Zero, user);
         }
 
@@ -184,7 +187,19 @@ namespace OpenSim.Data.MSSQL
             //Note maybe change this to use a Dataset that loading in all folders of a user and then go throw it that way.
             //Note this is changed so it opens only one connection to the database and not everytime it wants to get data.
 
+            /*  NOTE: the implementation below is very inefficient (makes a separate request to get subfolders for
+             * every found folder, recursively).  Inventory code for other DBs has been already rewritten to get ALL
+             * inventory for a specific user at once.
+             * 
+             * Meanwhile, one little thing is corrected:  getFolderHierarchy(UUID.Zero) doesn't make sense and should never 
+             * be used, so check for that and return an empty list.
+             */
+
             List<InventoryFolderBase> folders = new List<InventoryFolderBase>();
+
+            if (parentID == UUID.Zero)
+                return folders;
+
             string sql = "SELECT * FROM inventoryfolders WHERE parentFolderID = @parentID";
             using (SqlConnection conn = new SqlConnection(m_connectionString))
             using (SqlCommand cmd = new SqlCommand(sql, conn))
@@ -249,13 +264,12 @@ namespace OpenSim.Data.MSSQL
         /// <param name="folder">Folder to update</param>
         public void updateInventoryFolder(InventoryFolderBase folder)
         {
-            string sql = @"UPDATE inventoryfolders SET folderID = @folderID, 
-                                                       agentID = @agentID, 
+            string sql = @"UPDATE inventoryfolders SET agentID = @agentID, 
                                                        parentFolderID = @parentFolderID,
                                                        folderName = @folderName,
                                                        type = @type,
                                                        version = @version 
-                           WHERE folderID = @keyFolderID";
+                           WHERE folderID = @folderID";
 
             string folderName = folder.Name;
             if (folderName.Length > 64)
@@ -272,7 +286,6 @@ namespace OpenSim.Data.MSSQL
                 cmd.Parameters.Add(database.CreateParameter("folderName", folderName));
                 cmd.Parameters.Add(database.CreateParameter("type", folder.Type));
                 cmd.Parameters.Add(database.CreateParameter("version", folder.Version));
-                cmd.Parameters.Add(database.CreateParameter("@keyFolderID", folder.ID));
                 conn.Open();
                 try
                 {
@@ -296,7 +309,7 @@ namespace OpenSim.Data.MSSQL
             using (SqlCommand cmd = new SqlCommand(sql, conn))
             {
                 cmd.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID));
-                cmd.Parameters.Add(database.CreateParameter("@folderID", folder.ID));
+                cmd.Parameters.Add(database.CreateParameter("folderID", folder.ID));
                 conn.Open();
                 try
                 {
@@ -489,8 +502,7 @@ namespace OpenSim.Data.MSSQL
         /// <param name="item">Inventory item to update</param>
         public void updateInventoryItem(InventoryItemBase item)
         {
-            string sql = @"UPDATE inventoryitems SET inventoryID = @inventoryID, 
-                                                assetID = @assetID, 
+            string sql = @"UPDATE inventoryitems SET assetID = @assetID, 
                                                 assetType = @assetType,
                                                 parentFolderID = @parentFolderID,
                                                 avatarID = @avatarID,
@@ -502,13 +514,14 @@ namespace OpenSim.Data.MSSQL
                                                 creatorID = @creatorID, 
                                                 inventoryBasePermissions = @inventoryBasePermissions, 
                                                 inventoryEveryOnePermissions = @inventoryEveryOnePermissions, 
+                                                inventoryGroupPermissions = @inventoryGroupPermissions, 
                                                 salePrice = @salePrice, 
                                                 saleType = @saleType, 
                                                 creationDate = @creationDate, 
                                                 groupID = @groupID, 
                                                 groupOwned = @groupOwned, 
                                                 flags = @flags 
-                                        WHERE inventoryID = @keyInventoryID";
+                                        WHERE inventoryID = @inventoryID";
 
             string itemName = item.Name;
             if (item.Name.Length > 64)
@@ -537,16 +550,16 @@ namespace OpenSim.Data.MSSQL
                 command.Parameters.Add(database.CreateParameter("inventoryNextPermissions", item.NextPermissions));
                 command.Parameters.Add(database.CreateParameter("inventoryCurrentPermissions", item.CurrentPermissions));
                 command.Parameters.Add(database.CreateParameter("invType", item.InvType));
-                command.Parameters.Add(database.CreateParameter("creatorID", item.CreatorIdAsUuid));
+                command.Parameters.Add(database.CreateParameter("creatorID", item.CreatorId));
                 command.Parameters.Add(database.CreateParameter("inventoryBasePermissions", item.BasePermissions));
                 command.Parameters.Add(database.CreateParameter("inventoryEveryOnePermissions", item.EveryOnePermissions));
+                command.Parameters.Add(database.CreateParameter("inventoryGroupPermissions", item.GroupPermissions));
                 command.Parameters.Add(database.CreateParameter("salePrice", item.SalePrice));
                 command.Parameters.Add(database.CreateParameter("saleType", item.SaleType));
                 command.Parameters.Add(database.CreateParameter("creationDate", item.CreationDate));
                 command.Parameters.Add(database.CreateParameter("groupID", item.GroupID));
                 command.Parameters.Add(database.CreateParameter("groupOwned", item.GroupOwned));
                 command.Parameters.Add(database.CreateParameter("flags", item.Flags));
-                command.Parameters.Add(database.CreateParameter("keyInventoryID", item.ID));
                 conn.Open();
                 try
                 {
@@ -732,9 +745,9 @@ namespace OpenSim.Data.MSSQL
             try
             {
                 InventoryFolderBase folder = new InventoryFolderBase();
-                folder.Owner = new UUID((Guid)reader["agentID"]);
-                folder.ParentID = new UUID((Guid)reader["parentFolderID"]);
-                folder.ID = new UUID((Guid)reader["folderID"]);
+                folder.Owner = DBGuid.FromDB(reader["agentID"]);
+                folder.ParentID = DBGuid.FromDB(reader["parentFolderID"]);
+                folder.ID = DBGuid.FromDB(reader["folderID"]);
                 folder.Name = (string)reader["folderName"];
                 folder.Type = (short)reader["type"];
                 folder.Version = Convert.ToUInt16(reader["version"]);
@@ -760,24 +773,24 @@ namespace OpenSim.Data.MSSQL
             {
                 InventoryItemBase item = new InventoryItemBase();
 
-                item.ID = new UUID((Guid)reader["inventoryID"]);
-                item.AssetID = new UUID((Guid)reader["assetID"]);
+                item.ID = DBGuid.FromDB(reader["inventoryID"]);
+                item.AssetID = DBGuid.FromDB(reader["assetID"]);
                 item.AssetType = Convert.ToInt32(reader["assetType"].ToString());
-                item.Folder = new UUID((Guid)reader["parentFolderID"]);
-                item.Owner = new UUID((Guid)reader["avatarID"]);
+                item.Folder = DBGuid.FromDB(reader["parentFolderID"]);
+                item.Owner = DBGuid.FromDB(reader["avatarID"]);
                 item.Name = reader["inventoryName"].ToString();
                 item.Description = reader["inventoryDescription"].ToString();
                 item.NextPermissions = Convert.ToUInt32(reader["inventoryNextPermissions"]);
                 item.CurrentPermissions = Convert.ToUInt32(reader["inventoryCurrentPermissions"]);
                 item.InvType = Convert.ToInt32(reader["invType"].ToString());
-                item.CreatorId = ((Guid)reader["creatorID"]).ToString();
+                item.CreatorId = reader["creatorID"].ToString();
                 item.BasePermissions = Convert.ToUInt32(reader["inventoryBasePermissions"]);
                 item.EveryOnePermissions = Convert.ToUInt32(reader["inventoryEveryOnePermissions"]);
                 item.GroupPermissions = Convert.ToUInt32(reader["inventoryGroupPermissions"]);
                 item.SalePrice = Convert.ToInt32(reader["salePrice"]);
                 item.SaleType = Convert.ToByte(reader["saleType"]);
                 item.CreationDate = Convert.ToInt32(reader["creationDate"]);
-                item.GroupID = new UUID((Guid)reader["groupID"]);
+                item.GroupID = DBGuid.FromDB(reader["groupID"]);
                 item.GroupOwned = Convert.ToBoolean(reader["groupOwned"]);
                 item.Flags = Convert.ToUInt32(reader["flags"]);
 
diff --git a/OpenSim/Data/MSSQL/Resources/AssetStore.migrations b/OpenSim/Data/MSSQL/Resources/AssetStore.migrations
index beb82b9..8664ce9 100644
--- a/OpenSim/Data/MSSQL/Resources/AssetStore.migrations
+++ b/OpenSim/Data/MSSQL/Resources/AssetStore.migrations
@@ -97,4 +97,10 @@ COMMIT
 
 DELETE FROM assets WHERE id = 'dc4b9f0b-d008-45c6-96a4-01dd947ac621';
 
+:VERSION 6
 
+ALTER TABLE assets ADD asset_flags INTEGER NOT NULL DEFAULT 0;
+
+:VERSION 7
+
+alter table assets add creatorid varchar(36) not null default '';
diff --git a/OpenSim/Data/MSSQL/Resources/AvatarAppearance.sql b/OpenSim/Data/MSSQL/Resources/AvatarAppearance.sql
deleted file mode 100644
index e69de29..0000000
--- a/OpenSim/Data/MSSQL/Resources/AvatarAppearance.sql
+++ /dev/null
diff --git a/OpenSim/Data/MSSQL/Resources/CreateAssetsTable.sql b/OpenSim/Data/MSSQL/Resources/CreateAssetsTable.sql
deleted file mode 100644
index e69de29..0000000
--- a/OpenSim/Data/MSSQL/Resources/CreateAssetsTable.sql
+++ /dev/null
diff --git a/OpenSim/Data/MSSQL/Resources/CreateFoldersTable.sql b/OpenSim/Data/MSSQL/Resources/CreateFoldersTable.sql
deleted file mode 100644
index e69de29..0000000
--- a/OpenSim/Data/MSSQL/Resources/CreateFoldersTable.sql
+++ /dev/null
diff --git a/OpenSim/Data/MSSQL/Resources/CreateItemsTable.sql b/OpenSim/Data/MSSQL/Resources/CreateItemsTable.sql
deleted file mode 100644
index e69de29..0000000
--- a/OpenSim/Data/MSSQL/Resources/CreateItemsTable.sql
+++ /dev/null
diff --git a/OpenSim/Data/MSSQL/Resources/CreateUserFriendsTable.sql b/OpenSim/Data/MSSQL/Resources/CreateUserFriendsTable.sql
deleted file mode 100644
index e69de29..0000000
--- a/OpenSim/Data/MSSQL/Resources/CreateUserFriendsTable.sql
+++ /dev/null
diff --git a/OpenSim/Data/MSSQL/Resources/InventoryStore.migrations b/OpenSim/Data/MSSQL/Resources/InventoryStore.migrations
index cd5dfdc..e2a8d57 100644
--- a/OpenSim/Data/MSSQL/Resources/InventoryStore.migrations
+++ b/OpenSim/Data/MSSQL/Resources/InventoryStore.migrations
@@ -171,4 +171,74 @@ CREATE NONCLUSTERED INDEX folder ON dbo.inventoryitems
 
 COMMIT
 
+:VERSION 5
+
+# It would be totally crazy to have to recreate the whole table just to change one column type,
+# just because MS SQL treats each DEFAULT as a constraint object that must be dropped
+# before anything can be done to the column.  Since all defaults here are unnamed, there is
+# no easy way to drop them!  The hairy piece of code below removes all DEFAULT constraints
+# from InventoryItems.
+
+# SO: anything that's NULLable is by default NULL, so please don't declare DEFAULT(NULL), 
+# they do nothing but prevent changes to the columns. If you really
+# need to have DEFAULTs or other constraints, give them names so they can be dropped when needed!
+
+BEGIN TRANSACTION
+DECLARE @nm varchar(80);
+DECLARE x CURSOR LOCAL FORWARD_ONLY READ_ONLY
+  FOR SELECT name FROM sys.default_constraints where parent_object_id = OBJECT_ID('inventoryitems');  
+OPEN x;
+FETCH NEXT FROM x INTO @nm;
+WHILE @@FETCH_STATUS = 0
+BEGIN
+  EXEC('alter table inventoryitems drop ' + @nm);
+  FETCH NEXT FROM x INTO @nm;
+END
+CLOSE x
+DEALLOCATE x 
+COMMIT     
+
+# all DEFAULTs dropped!
+
+:GO
+
+BEGIN TRANSACTION
+
+# Restoring defaults:   
+# NOTE: [inventoryID] does NOT need one: it's NOT NULL PK and a unique Guid must be provided every time anyway!
+ 
+alter table inventoryitems
+   add constraint def_baseperm default 0 for inventoryBasePermissions    
+alter table inventoryitems
+   add constraint def_allperm default 0 for inventoryEveryOnePermissions    
+alter table inventoryitems
+   add constraint def_grpperm default 0 for inventoryGroupPermissions    
+   
+COMMIT     
+   
+:VERSION 7
+
+BEGIN TRANSACTION
+
+# CreatorID goes back to VARCHAR(36) (???)
+
+exec sp_rename 'inventoryitems.CreatorID', 'cr_old', 'COLUMN'
+
+:GO
+
+alter table inventoryitems
+   add creatorID varchar(36) NULL
+   
+:GO   
+   
+update inventoryitems set creatorID = CONVERT(VARCHAR(36), cr_old)
+
+alter table inventoryitems
+   drop column cr_old
+
+COMMIT  
+   
+ 
+
+
 
diff --git a/OpenSim/Data/MSSQL/Resources/Mssql-agents.sql b/OpenSim/Data/MSSQL/Resources/Mssql-agents.sql
deleted file mode 100644
index e69de29..0000000
--- a/OpenSim/Data/MSSQL/Resources/Mssql-agents.sql
+++ /dev/null
diff --git a/OpenSim/Data/MSSQL/Resources/Mssql-logs.sql b/OpenSim/Data/MSSQL/Resources/Mssql-logs.sql
deleted file mode 100644
index e69de29..0000000
--- a/OpenSim/Data/MSSQL/Resources/Mssql-logs.sql
+++ /dev/null
diff --git a/OpenSim/Data/MSSQL/Resources/Mssql-regions.sql b/OpenSim/Data/MSSQL/Resources/Mssql-regions.sql
deleted file mode 100644
index e69de29..0000000
--- a/OpenSim/Data/MSSQL/Resources/Mssql-regions.sql
+++ /dev/null
diff --git a/OpenSim/Data/MSSQL/Resources/Mssql-users.sql b/OpenSim/Data/MSSQL/Resources/Mssql-users.sql
deleted file mode 100644
index e69de29..0000000
--- a/OpenSim/Data/MSSQL/Resources/Mssql-users.sql
+++ /dev/null