aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/MSSQL
diff options
context:
space:
mode:
authorAlexRa2010-05-23 11:26:53 +0300
committerAlexRa2010-05-23 11:48:19 +0300
commit9976cb93ce351f45dea77e3389e0159b866757ae (patch)
tree7fd2d151b128cb036b683cba86262e47f1968fa6 /OpenSim/Data/MSSQL
parentSplit migrations for RegionStore and EstateStore (see WARNING!) (diff)
downloadopensim-SC_OLD-9976cb93ce351f45dea77e3389e0159b866757ae.zip
opensim-SC_OLD-9976cb93ce351f45dea77e3389e0159b866757ae.tar.gz
opensim-SC_OLD-9976cb93ce351f45dea77e3389e0159b866757ae.tar.bz2
opensim-SC_OLD-9976cb93ce351f45dea77e3389e0159b866757ae.tar.xz
Further corrections to MS SQL stores (now passes all tests)
Besides, AssetData is slightly optimized to StoreAsset in one request ("IF EXISTS() UPDATE ... ELSE INSERT ...") The main change in the MS SQL Inventory implem. is that it now return empty list (or whatever) when called with UUID.Zero, which is consistent with how the code for other DBs work. I did no changes at all in XInventory, as there is no test set for them.
Diffstat (limited to 'OpenSim/Data/MSSQL')
-rw-r--r--OpenSim/Data/MSSQL/MSSQLAssetData.cs87
-rw-r--r--OpenSim/Data/MSSQL/MSSQLInventoryData.cs51
2 files changed, 49 insertions, 89 deletions
diff --git a/OpenSim/Data/MSSQL/MSSQLAssetData.cs b/OpenSim/Data/MSSQL/MSSQLAssetData.cs
index ec9d4f6..c7488d8 100644
--- a/OpenSim/Data/MSSQL/MSSQLAssetData.cs
+++ b/OpenSim/Data/MSSQL/MSSQLAssetData.cs
@@ -145,26 +145,19 @@ namespace OpenSim.Data.MSSQL
145 /// <param name="asset">the asset</param> 145 /// <param name="asset">the asset</param>
146 override public void StoreAsset(AssetBase asset) 146 override public void StoreAsset(AssetBase asset)
147 { 147 {
148 if (ExistsAsset(asset.FullID)) 148
149 UpdateAsset(asset); 149 string sql =
150 else 150 @"IF EXISTS(SELECT * FROM assets WHERE id=@id)
151 InsertAsset(asset); 151 UPDATE assets set name = @name, description = @description, assetType = @assetType,
152 } 152 local = @local, temporary = @temporary, creatorid = @creatorid, data = @data
153 153 WHERE id=@id
154 154 ELSE
155 private void InsertAsset(AssetBase asset) 155 INSERT INTO assets
156 { 156 ([id], [name], [description], [assetType], [local],
157 if (ExistsAsset(asset.FullID)) 157 [temporary], [create_time], [access_time], [creatorid], [asset_flags], [data])
158 { 158 VALUES
159 return; 159 (@id, @name, @description, @assetType, @local,
160 } 160 @temporary, @create_time, @access_time, @creatorid, @asset_flags, @data)";
161
162 string sql = @"INSERT INTO assets
163 ([id], [name], [description], [assetType], [local],
164 [temporary], [create_time], [access_time], [creatorid], [asset_flags], [data])
165 VALUES
166 (@id, @name, @description, @assetType, @local,
167 @temporary, @create_time, @access_time, @creatorid, @asset_flags, @data)";
168 161
169 string assetName = asset.Name; 162 string assetName = asset.Name;
170 if (asset.Name.Length > 64) 163 if (asset.Name.Length > 64)
@@ -202,58 +195,11 @@ namespace OpenSim.Data.MSSQL
202 } 195 }
203 catch(Exception e) 196 catch(Exception e)
204 { 197 {
205 m_log.Error("[ASSET DB]: Error inserting item :" + e.Message); 198 m_log.Error("[ASSET DB]: Error storing item :" + e.Message);
206 } 199 }
207 } 200 }
208 } 201 }
209 202
210 /// <summary>
211 /// Update asset in m_database
212 /// </summary>
213 /// <param name="asset">the asset</param>
214 private void UpdateAsset(AssetBase asset)
215 {
216 string sql = @"UPDATE assets set name = @name, description = @description, assetType = @assetType,
217 local = @local, temporary = @temporary, data = @data
218 , creatorid = @creatorid
219 WHERE id = @keyId;";
220
221 string assetName = asset.Name;
222 if (asset.Name.Length > 64)
223 {
224 assetName = asset.Name.Substring(0, 64);
225 m_log.Warn("[ASSET DB]: Name field truncated from " + asset.Name.Length + " to " + assetName.Length + " characters on update");
226 }
227
228 string assetDescription = asset.Description;
229 if (asset.Description.Length > 64)
230 {
231 assetDescription = asset.Description.Substring(0, 64);
232 m_log.Warn("[ASSET DB]: Description field truncated from " + asset.Description.Length + " to " + assetDescription.Length + " characters on update");
233 }
234
235 using (SqlConnection conn = new SqlConnection(m_connectionString))
236 using (SqlCommand command = new SqlCommand(sql, conn))
237 {
238 command.Parameters.Add(m_database.CreateParameter("keyId", asset.FullID));
239 command.Parameters.Add(m_database.CreateParameter("name", assetName));
240 command.Parameters.Add(m_database.CreateParameter("description", assetDescription));
241 command.Parameters.Add(m_database.CreateParameter("assetType", asset.Type));
242 command.Parameters.Add(m_database.CreateParameter("local", asset.Local));
243 command.Parameters.Add(m_database.CreateParameter("temporary", asset.Temporary));
244 command.Parameters.Add(m_database.CreateParameter("data", asset.Data));
245 command.Parameters.Add(m_database.CreateParameter("creatorid", asset.Metadata.CreatorID));
246 conn.Open();
247 try
248 {
249 command.ExecuteNonQuery();
250 }
251 catch (Exception e)
252 {
253 m_log.Error(e.ToString());
254 }
255 }
256 }
257 203
258// Commented out since currently unused - this probably should be called in GetAsset() 204// Commented out since currently unused - this probably should be called in GetAsset()
259// private void UpdateAccessTime(AssetBase asset) 205// private void UpdateAccessTime(AssetBase asset)
@@ -302,7 +248,7 @@ namespace OpenSim.Data.MSSQL
302 string sql = @"WITH OrderedAssets AS 248 string sql = @"WITH OrderedAssets AS
303 ( 249 (
304 SELECT id, name, description, assetType, temporary, creatorid, 250 SELECT id, name, description, assetType, temporary, creatorid,
305 Row = ROW_NUMBER() OVER (ORDER BY id) 251 RowNumber = ROW_NUMBER() OVER (ORDER BY id)
306 FROM assets 252 FROM assets
307 ) 253 )
308 SELECT * 254 SELECT *
@@ -320,12 +266,13 @@ namespace OpenSim.Data.MSSQL
320 while (reader.Read()) 266 while (reader.Read())
321 { 267 {
322 AssetMetadata metadata = new AssetMetadata(); 268 AssetMetadata metadata = new AssetMetadata();
323 metadata.FullID = new UUID((Guid)reader["id"]); 269 metadata.FullID = DBGuid.FromDB(reader["id"]);
324 metadata.Name = (string)reader["name"]; 270 metadata.Name = (string)reader["name"];
325 metadata.Description = (string)reader["description"]; 271 metadata.Description = (string)reader["description"];
326 metadata.Type = Convert.ToSByte(reader["assetType"]); 272 metadata.Type = Convert.ToSByte(reader["assetType"]);
327 metadata.Temporary = Convert.ToBoolean(reader["temporary"]); 273 metadata.Temporary = Convert.ToBoolean(reader["temporary"]);
328 metadata.CreatorID = (string)reader["creatorid"]; 274 metadata.CreatorID = (string)reader["creatorid"];
275 retList.Add(metadata);
329 } 276 }
330 } 277 }
331 } 278 }
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
111 /// <returns>A list of folder objects</returns> 111 /// <returns>A list of folder objects</returns>
112 public List<InventoryFolderBase> getUserRootFolders(UUID user) 112 public List<InventoryFolderBase> getUserRootFolders(UUID user)
113 { 113 {
114 if (user == UUID.Zero)
115 return new List<InventoryFolderBase>();
116
114 return getInventoryFolders(UUID.Zero, user); 117 return getInventoryFolders(UUID.Zero, user);
115 } 118 }
116 119
@@ -184,7 +187,19 @@ namespace OpenSim.Data.MSSQL
184 //Note maybe change this to use a Dataset that loading in all folders of a user and then go throw it that way. 187 //Note maybe change this to use a Dataset that loading in all folders of a user and then go throw it that way.
185 //Note this is changed so it opens only one connection to the database and not everytime it wants to get data. 188 //Note this is changed so it opens only one connection to the database and not everytime it wants to get data.
186 189
190 /* NOTE: the implementation below is very inefficient (makes a separate request to get subfolders for
191 * every found folder, recursively). Inventory code for other DBs has been already rewritten to get ALL
192 * inventory for a specific user at once.
193 *
194 * Meanwhile, one little thing is corrected: getFolderHierarchy(UUID.Zero) doesn't make sense and should never
195 * be used, so check for that and return an empty list.
196 */
197
187 List<InventoryFolderBase> folders = new List<InventoryFolderBase>(); 198 List<InventoryFolderBase> folders = new List<InventoryFolderBase>();
199
200 if (parentID == UUID.Zero)
201 return folders;
202
188 string sql = "SELECT * FROM inventoryfolders WHERE parentFolderID = @parentID"; 203 string sql = "SELECT * FROM inventoryfolders WHERE parentFolderID = @parentID";
189 using (SqlConnection conn = new SqlConnection(m_connectionString)) 204 using (SqlConnection conn = new SqlConnection(m_connectionString))
190 using (SqlCommand cmd = new SqlCommand(sql, conn)) 205 using (SqlCommand cmd = new SqlCommand(sql, conn))
@@ -249,13 +264,12 @@ namespace OpenSim.Data.MSSQL
249 /// <param name="folder">Folder to update</param> 264 /// <param name="folder">Folder to update</param>
250 public void updateInventoryFolder(InventoryFolderBase folder) 265 public void updateInventoryFolder(InventoryFolderBase folder)
251 { 266 {
252 string sql = @"UPDATE inventoryfolders SET folderID = @folderID, 267 string sql = @"UPDATE inventoryfolders SET agentID = @agentID,
253 agentID = @agentID,
254 parentFolderID = @parentFolderID, 268 parentFolderID = @parentFolderID,
255 folderName = @folderName, 269 folderName = @folderName,
256 type = @type, 270 type = @type,
257 version = @version 271 version = @version
258 WHERE folderID = @keyFolderID"; 272 WHERE folderID = @folderID";
259 273
260 string folderName = folder.Name; 274 string folderName = folder.Name;
261 if (folderName.Length > 64) 275 if (folderName.Length > 64)
@@ -272,7 +286,6 @@ namespace OpenSim.Data.MSSQL
272 cmd.Parameters.Add(database.CreateParameter("folderName", folderName)); 286 cmd.Parameters.Add(database.CreateParameter("folderName", folderName));
273 cmd.Parameters.Add(database.CreateParameter("type", folder.Type)); 287 cmd.Parameters.Add(database.CreateParameter("type", folder.Type));
274 cmd.Parameters.Add(database.CreateParameter("version", folder.Version)); 288 cmd.Parameters.Add(database.CreateParameter("version", folder.Version));
275 cmd.Parameters.Add(database.CreateParameter("@keyFolderID", folder.ID));
276 conn.Open(); 289 conn.Open();
277 try 290 try
278 { 291 {
@@ -296,7 +309,7 @@ namespace OpenSim.Data.MSSQL
296 using (SqlCommand cmd = new SqlCommand(sql, conn)) 309 using (SqlCommand cmd = new SqlCommand(sql, conn))
297 { 310 {
298 cmd.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID)); 311 cmd.Parameters.Add(database.CreateParameter("parentFolderID", folder.ParentID));
299 cmd.Parameters.Add(database.CreateParameter("@folderID", folder.ID)); 312 cmd.Parameters.Add(database.CreateParameter("folderID", folder.ID));
300 conn.Open(); 313 conn.Open();
301 try 314 try
302 { 315 {
@@ -489,8 +502,7 @@ namespace OpenSim.Data.MSSQL
489 /// <param name="item">Inventory item to update</param> 502 /// <param name="item">Inventory item to update</param>
490 public void updateInventoryItem(InventoryItemBase item) 503 public void updateInventoryItem(InventoryItemBase item)
491 { 504 {
492 string sql = @"UPDATE inventoryitems SET inventoryID = @inventoryID, 505 string sql = @"UPDATE inventoryitems SET assetID = @assetID,
493 assetID = @assetID,
494 assetType = @assetType, 506 assetType = @assetType,
495 parentFolderID = @parentFolderID, 507 parentFolderID = @parentFolderID,
496 avatarID = @avatarID, 508 avatarID = @avatarID,
@@ -502,13 +514,14 @@ namespace OpenSim.Data.MSSQL
502 creatorID = @creatorID, 514 creatorID = @creatorID,
503 inventoryBasePermissions = @inventoryBasePermissions, 515 inventoryBasePermissions = @inventoryBasePermissions,
504 inventoryEveryOnePermissions = @inventoryEveryOnePermissions, 516 inventoryEveryOnePermissions = @inventoryEveryOnePermissions,
517 inventoryGroupPermissions = @inventoryGroupPermissions,
505 salePrice = @salePrice, 518 salePrice = @salePrice,
506 saleType = @saleType, 519 saleType = @saleType,
507 creationDate = @creationDate, 520 creationDate = @creationDate,
508 groupID = @groupID, 521 groupID = @groupID,
509 groupOwned = @groupOwned, 522 groupOwned = @groupOwned,
510 flags = @flags 523 flags = @flags
511 WHERE inventoryID = @keyInventoryID"; 524 WHERE inventoryID = @inventoryID";
512 525
513 string itemName = item.Name; 526 string itemName = item.Name;
514 if (item.Name.Length > 64) 527 if (item.Name.Length > 64)
@@ -537,16 +550,16 @@ namespace OpenSim.Data.MSSQL
537 command.Parameters.Add(database.CreateParameter("inventoryNextPermissions", item.NextPermissions)); 550 command.Parameters.Add(database.CreateParameter("inventoryNextPermissions", item.NextPermissions));
538 command.Parameters.Add(database.CreateParameter("inventoryCurrentPermissions", item.CurrentPermissions)); 551 command.Parameters.Add(database.CreateParameter("inventoryCurrentPermissions", item.CurrentPermissions));
539 command.Parameters.Add(database.CreateParameter("invType", item.InvType)); 552 command.Parameters.Add(database.CreateParameter("invType", item.InvType));
540 command.Parameters.Add(database.CreateParameter("creatorID", item.CreatorIdAsUuid)); 553 command.Parameters.Add(database.CreateParameter("creatorID", item.CreatorId));
541 command.Parameters.Add(database.CreateParameter("inventoryBasePermissions", item.BasePermissions)); 554 command.Parameters.Add(database.CreateParameter("inventoryBasePermissions", item.BasePermissions));
542 command.Parameters.Add(database.CreateParameter("inventoryEveryOnePermissions", item.EveryOnePermissions)); 555 command.Parameters.Add(database.CreateParameter("inventoryEveryOnePermissions", item.EveryOnePermissions));
556 command.Parameters.Add(database.CreateParameter("inventoryGroupPermissions", item.GroupPermissions));
543 command.Parameters.Add(database.CreateParameter("salePrice", item.SalePrice)); 557 command.Parameters.Add(database.CreateParameter("salePrice", item.SalePrice));
544 command.Parameters.Add(database.CreateParameter("saleType", item.SaleType)); 558 command.Parameters.Add(database.CreateParameter("saleType", item.SaleType));
545 command.Parameters.Add(database.CreateParameter("creationDate", item.CreationDate)); 559 command.Parameters.Add(database.CreateParameter("creationDate", item.CreationDate));
546 command.Parameters.Add(database.CreateParameter("groupID", item.GroupID)); 560 command.Parameters.Add(database.CreateParameter("groupID", item.GroupID));
547 command.Parameters.Add(database.CreateParameter("groupOwned", item.GroupOwned)); 561 command.Parameters.Add(database.CreateParameter("groupOwned", item.GroupOwned));
548 command.Parameters.Add(database.CreateParameter("flags", item.Flags)); 562 command.Parameters.Add(database.CreateParameter("flags", item.Flags));
549 command.Parameters.Add(database.CreateParameter("keyInventoryID", item.ID));
550 conn.Open(); 563 conn.Open();
551 try 564 try
552 { 565 {
@@ -732,9 +745,9 @@ namespace OpenSim.Data.MSSQL
732 try 745 try
733 { 746 {
734 InventoryFolderBase folder = new InventoryFolderBase(); 747 InventoryFolderBase folder = new InventoryFolderBase();
735 folder.Owner = new UUID((Guid)reader["agentID"]); 748 folder.Owner = DBGuid.FromDB(reader["agentID"]);
736 folder.ParentID = new UUID((Guid)reader["parentFolderID"]); 749 folder.ParentID = DBGuid.FromDB(reader["parentFolderID"]);
737 folder.ID = new UUID((Guid)reader["folderID"]); 750 folder.ID = DBGuid.FromDB(reader["folderID"]);
738 folder.Name = (string)reader["folderName"]; 751 folder.Name = (string)reader["folderName"];
739 folder.Type = (short)reader["type"]; 752 folder.Type = (short)reader["type"];
740 folder.Version = Convert.ToUInt16(reader["version"]); 753 folder.Version = Convert.ToUInt16(reader["version"]);
@@ -760,24 +773,24 @@ namespace OpenSim.Data.MSSQL
760 { 773 {
761 InventoryItemBase item = new InventoryItemBase(); 774 InventoryItemBase item = new InventoryItemBase();
762 775
763 item.ID = new UUID((Guid)reader["inventoryID"]); 776 item.ID = DBGuid.FromDB(reader["inventoryID"]);
764 item.AssetID = new UUID((Guid)reader["assetID"]); 777 item.AssetID = DBGuid.FromDB(reader["assetID"]);
765 item.AssetType = Convert.ToInt32(reader["assetType"].ToString()); 778 item.AssetType = Convert.ToInt32(reader["assetType"].ToString());
766 item.Folder = new UUID((Guid)reader["parentFolderID"]); 779 item.Folder = DBGuid.FromDB(reader["parentFolderID"]);
767 item.Owner = new UUID((Guid)reader["avatarID"]); 780 item.Owner = DBGuid.FromDB(reader["avatarID"]);
768 item.Name = reader["inventoryName"].ToString(); 781 item.Name = reader["inventoryName"].ToString();
769 item.Description = reader["inventoryDescription"].ToString(); 782 item.Description = reader["inventoryDescription"].ToString();
770 item.NextPermissions = Convert.ToUInt32(reader["inventoryNextPermissions"]); 783 item.NextPermissions = Convert.ToUInt32(reader["inventoryNextPermissions"]);
771 item.CurrentPermissions = Convert.ToUInt32(reader["inventoryCurrentPermissions"]); 784 item.CurrentPermissions = Convert.ToUInt32(reader["inventoryCurrentPermissions"]);
772 item.InvType = Convert.ToInt32(reader["invType"].ToString()); 785 item.InvType = Convert.ToInt32(reader["invType"].ToString());
773 item.CreatorId = ((Guid)reader["creatorID"]).ToString(); 786 item.CreatorId = reader["creatorID"].ToString();
774 item.BasePermissions = Convert.ToUInt32(reader["inventoryBasePermissions"]); 787 item.BasePermissions = Convert.ToUInt32(reader["inventoryBasePermissions"]);
775 item.EveryOnePermissions = Convert.ToUInt32(reader["inventoryEveryOnePermissions"]); 788 item.EveryOnePermissions = Convert.ToUInt32(reader["inventoryEveryOnePermissions"]);
776 item.GroupPermissions = Convert.ToUInt32(reader["inventoryGroupPermissions"]); 789 item.GroupPermissions = Convert.ToUInt32(reader["inventoryGroupPermissions"]);
777 item.SalePrice = Convert.ToInt32(reader["salePrice"]); 790 item.SalePrice = Convert.ToInt32(reader["salePrice"]);
778 item.SaleType = Convert.ToByte(reader["saleType"]); 791 item.SaleType = Convert.ToByte(reader["saleType"]);
779 item.CreationDate = Convert.ToInt32(reader["creationDate"]); 792 item.CreationDate = Convert.ToInt32(reader["creationDate"]);
780 item.GroupID = new UUID((Guid)reader["groupID"]); 793 item.GroupID = DBGuid.FromDB(reader["groupID"]);
781 item.GroupOwned = Convert.ToBoolean(reader["groupOwned"]); 794 item.GroupOwned = Convert.ToBoolean(reader["groupOwned"]);
782 item.Flags = Convert.ToUInt32(reader["flags"]); 795 item.Flags = Convert.ToUInt32(reader["flags"]);
783 796