aboutsummaryrefslogtreecommitdiffstatshomepage
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--OpenSim/Framework/Data.MSSQL/MSSQLAssetData.cs227
-rw-r--r--OpenSim/Framework/Data.MSSQL/MSSQLGridData.cs164
-rw-r--r--OpenSim/Framework/Data.MSSQL/MSSQLInventoryData.cs697
-rw-r--r--OpenSim/Framework/Data.MSSQL/MSSQLLogData.cs104
-rw-r--r--OpenSim/Framework/Data.MSSQL/MSSQLManager.cs616
-rw-r--r--OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs452
-rw-r--r--OpenSim/Framework/Data.MSSQL/Resources/CreateAssetsTable.sql19
-rw-r--r--OpenSim/Framework/Data.MSSQL/Resources/CreateFoldersTable.sql27
-rw-r--r--OpenSim/Framework/Data.MSSQL/Resources/CreateItemsTable.sql39
-rw-r--r--OpenSim/Framework/Data.MSSQL/Resources/Mssql-agents.sql37
-rw-r--r--OpenSim/Framework/Data.MSSQL/Resources/Mssql-logs.sql20
-rw-r--r--OpenSim/Framework/Data.MSSQL/Resources/Mssql-users.sql41
-rw-r--r--OpenSim/Region/Application/OpenSimMain.cs7
-rw-r--r--OpenSim/Region/Storage/OpenSim.DataStore.MSSQL/MSSQLDataStore.cs1032
-rw-r--r--bin/mssql_connection.ini6
-rw-r--r--prebuild.xml33
-rw-r--r--share/sql/mssql-CreateAssetsTable.sql19
-rw-r--r--share/sql/mssql-CreateFoldersTable.sql27
-rw-r--r--share/sql/mssql-CreateItemsTable.sql39
-rw-r--r--share/sql/mssql-agents.sql37
-rw-r--r--share/sql/mssql-logs.sql20
-rw-r--r--share/sql/mssql-users.sql41
22 files changed, 3630 insertions, 74 deletions
diff --git a/OpenSim/Framework/Data.MSSQL/MSSQLAssetData.cs b/OpenSim/Framework/Data.MSSQL/MSSQLAssetData.cs
new file mode 100644
index 0000000..e933a5b
--- /dev/null
+++ b/OpenSim/Framework/Data.MSSQL/MSSQLAssetData.cs
@@ -0,0 +1,227 @@
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.Data;
31using System.Collections.Generic;
32using System.Data.SqlClient;
33
34using libsecondlife;
35using OpenSim.Framework.Console;
36
37namespace OpenSim.Framework.Data.MSSQL
38{
39 class MSSQLAssetData : IAssetProvider
40 {
41 MSSQLManager database;
42 #region IAssetProvider Members
43
44 private void UpgradeAssetsTable(string tableName)
45 {
46 // null as the version, indicates that the table didn't exist
47 if (tableName == null)
48 {
49 MainLog.Instance.Notice("ASSETS", "Creating new database tables");
50 database.ExecuteResourceSql("CreateAssetsTable.sql");
51 return;
52 }
53 }
54
55 /// <summary>
56 /// Ensure that the assets related tables exists and are at the latest version
57 /// </summary>
58 private void TestTables()
59 {
60
61 Dictionary<string, string> tableList = new Dictionary<string, string>();
62
63 tableList["assets"] = null;
64 database.GetTableVersion(tableList);
65
66 UpgradeAssetsTable(tableList["assets"]);
67
68 }
69
70 public AssetBase FetchAsset(LLUUID assetID)
71 {
72 AssetBase asset = null;
73
74 Dictionary<string, string> param = new Dictionary<string, string>();
75 param["id"] = assetID.ToStringHyphenated();
76
77 IDbCommand result = database.Query("SELECT * FROM assets WHERE id = @id", param);
78 IDataReader reader = result.ExecuteReader();
79
80 asset = database.getAssetRow(reader);
81 reader.Close();
82 result.Dispose();
83
84 return asset;
85 }
86
87 public void CreateAsset(AssetBase asset)
88 {
89
90 if (ExistsAsset((LLUUID)asset.FullID))
91 {
92 return;
93 }
94
95
96
97 SqlCommand cmd =
98 new SqlCommand(
99 "INSERT INTO assets ([id], [name], [description], [assetType], [invType], [local], [temporary], [data])"+
100 " VALUES "+
101 "(@id, @name, @description, @assetType, @invType, @local, @temporary, @data)",
102 database.getConnection());
103
104 using (cmd)
105 {
106
107 //SqlParameter p = cmd.Parameters.Add("id", SqlDbType.NVarChar);
108 //p.Value = asset.FullID.ToStringHyphenated();
109 cmd.Parameters.AddWithValue("id", asset.FullID.ToStringHyphenated());
110 cmd.Parameters.AddWithValue("name", asset.Name);
111 cmd.Parameters.AddWithValue("description", asset.Description);
112 SqlParameter e = cmd.Parameters.Add("assetType", SqlDbType.TinyInt);
113 e.Value = asset.Type;
114 SqlParameter f = cmd.Parameters.Add("invType", SqlDbType.TinyInt);
115 f.Value = asset.InvType;
116 SqlParameter g = cmd.Parameters.Add("local", SqlDbType.TinyInt);
117 g.Value = asset.Local;
118 SqlParameter h = cmd.Parameters.Add("temporary", SqlDbType.TinyInt);
119 h.Value = asset.Temporary;
120 SqlParameter i = cmd.Parameters.Add("data", SqlDbType.Image);
121 i.Value = asset.Data;
122 try
123 {
124 cmd.ExecuteNonQuery();
125 }
126 catch (Exception)
127 {
128 throw;
129 }
130
131 cmd.Dispose();
132 }
133
134 }
135
136
137 public void UpdateAsset(AssetBase asset)
138 {
139 SqlCommand command = new SqlCommand("UPDATE assets set id = @id, " +
140 "name = @name, " +
141 "description = @description," +
142 "assetType = @assetType," +
143 "invType = @invType," +
144 "local = @local,"+
145 "temporary = @temporary," +
146 "data = @data where " +
147 "id = @keyId;", database.getConnection());
148 SqlParameter param1 = new SqlParameter("@id", asset.FullID.ToStringHyphenated());
149 SqlParameter param2 = new SqlParameter("@name", asset.Name);
150 SqlParameter param3 = new SqlParameter("@description", asset.Description);
151 SqlParameter param4 = new SqlParameter("@assetType", asset.Type);
152 SqlParameter param5 = new SqlParameter("@invType", asset.InvType);
153 SqlParameter param6 = new SqlParameter("@local", asset.Local);
154 SqlParameter param7 = new SqlParameter("@temporary", asset.Temporary);
155 SqlParameter param8 = new SqlParameter("@data", asset.Data);
156 SqlParameter param9 = new SqlParameter("@keyId", asset.FullID.ToStringHyphenated());
157 command.Parameters.Add(param1);
158 command.Parameters.Add(param2);
159 command.Parameters.Add(param3);
160 command.Parameters.Add(param4);
161 command.Parameters.Add(param5);
162 command.Parameters.Add(param6);
163 command.Parameters.Add(param7);
164 command.Parameters.Add(param8);
165 command.Parameters.Add(param9);
166
167 try
168 {
169 command.ExecuteNonQuery();
170 }
171 catch (Exception e)
172 {
173 MainLog.Instance.Error(e.ToString());
174 }
175
176 }
177
178 public bool ExistsAsset(LLUUID uuid)
179 {
180 if (FetchAsset(uuid) != null) {
181 return true;
182 }
183 return false;
184 }
185
186 /// <summary>
187 /// All writes are immediately commited to the database, so this is a no-op
188 /// </summary>
189 public void CommitAssets()
190 {
191 }
192
193 #endregion
194
195 #region IPlugin Members
196
197
198
199 public void Initialise()
200 {
201
202 IniFile GridDataMySqlFile = new IniFile("mssql_connection.ini");
203 string settingDataSource = GridDataMySqlFile.ParseFileReadValue("data_source");
204 string settingInitialCatalog = GridDataMySqlFile.ParseFileReadValue("initial_catalog");
205 string settingPersistSecurityInfo = GridDataMySqlFile.ParseFileReadValue("persist_security_info");
206 string settingUserId = GridDataMySqlFile.ParseFileReadValue("user_id");
207 string settingPassword = GridDataMySqlFile.ParseFileReadValue("password");
208
209 this.database = new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, settingPassword);
210
211 TestTables();
212 }
213
214 public string Version
215 {
216// get { return database.getVersion(); }
217 get { return database.getVersion(); }
218 }
219
220 public string Name
221 {
222 get { return "MSSQL Asset storage engine"; }
223 }
224
225 #endregion
226 }
227} \ No newline at end of file
diff --git a/OpenSim/Framework/Data.MSSQL/MSSQLGridData.cs b/OpenSim/Framework/Data.MSSQL/MSSQLGridData.cs
index 1516fec..0d294e7 100644
--- a/OpenSim/Framework/Data.MSSQL/MSSQLGridData.cs
+++ b/OpenSim/Framework/Data.MSSQL/MSSQLGridData.cs
@@ -28,11 +28,10 @@
28using System; 28using System;
29using System.Collections.Generic; 29using System.Collections.Generic;
30using System.Data; 30using System.Data;
31using OpenSim.Framework;
32using System.Security.Cryptography; 31using System.Security.Cryptography;
33using System.Text; 32using System.Text;
34using libsecondlife; 33using libsecondlife;
35 34using OpenSim.Framework.Console;
36 35
37namespace OpenSim.Framework.Data.MSSQL 36namespace OpenSim.Framework.Data.MSSQL
38{ 37{
@@ -44,14 +43,22 @@ namespace OpenSim.Framework.Data.MSSQL
44 /// <summary> 43 /// <summary>
45 /// Database manager 44 /// Database manager
46 /// </summary> 45 /// </summary>
47 private MSSqlManager database; 46 private MSSQLManager database;
48 47
49 /// <summary> 48 /// <summary>
50 /// Initialises the Grid Interface 49 /// Initialises the Grid Interface
51 /// </summary> 50 /// </summary>
52 public void Initialise() 51 public void Initialise()
53 { 52 {
54 database = new MSSqlManager("localhost", "db", "user", "password", "false"); 53 IniFile GridDataMySqlFile = new IniFile("mssql_connection.ini");
54 string settingDataSource = GridDataMySqlFile.ParseFileReadValue("data_source");
55 string settingInitialCatalog = GridDataMySqlFile.ParseFileReadValue("initial_catalog");
56 string settingPersistSecurityInfo = GridDataMySqlFile.ParseFileReadValue("persist_security_info");
57 string settingUserId = GridDataMySqlFile.ParseFileReadValue("user_id");
58 string settingPassword = GridDataMySqlFile.ParseFileReadValue("password");
59
60 database = new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, settingPassword);
61
55 } 62 }
56 63
57 /// <summary> 64 /// <summary>
@@ -100,17 +107,115 @@ namespace OpenSim.Framework.Data.MSSQL
100 /// <returns>Sim profile</returns> 107 /// <returns>Sim profile</returns>
101 public RegionProfileData GetProfileByHandle(ulong handle) 108 public RegionProfileData GetProfileByHandle(ulong handle)
102 { 109 {
103 Dictionary<string, string> param = new Dictionary<string, string>(); 110 IDataReader reader = null;
104 param["handle"] = handle.ToString(); 111 try
112 {
113 Dictionary<string, string> param = new Dictionary<string, string>();
114 param["handle"] = handle.ToString();
115 IDbCommand result = database.Query("SELECT * FROM regions WHERE regionHandle = @handle", param);
116 reader = result.ExecuteReader();
105 117
106 IDbCommand result = database.Query("SELECT * FROM regions WHERE handle = @handle", param); 118 RegionProfileData row = database.getRegionRow(reader);
107 IDataReader reader = result.ExecuteReader(); 119 reader.Close();
120 result.Dispose();
108 121
109 RegionProfileData row = database.getRow(reader); 122 return row;
110 reader.Close();
111 result.Dispose();
112 123
113 return row; 124 }
125 catch (Exception)
126 {
127 if (reader != null) {
128 reader.Close();
129 }
130
131 }
132 return null;
133 }
134
135 /// <summary>
136 /// // Returns a list of avatar and UUIDs that match the query
137 /// </summary>
138
139 public List<AvatarPickerAvatar> GeneratePickerResults(LLUUID queryID, string query)
140 {
141 List<AvatarPickerAvatar> returnlist = new List<AvatarPickerAvatar>();
142 string[] querysplit;
143 querysplit = query.Split(' ');
144 if (querysplit.Length == 2)
145 {
146 try
147 {
148 lock (database)
149 {
150 Dictionary<string, string> param = new Dictionary<string, string>();
151 param["first"] = querysplit[0];
152 param["second"] = querysplit[1];
153
154 IDbCommand result =
155 database.Query("SELECT UUID,username,surname FROM users WHERE username = @first AND lastname = @second", param);
156 IDataReader reader = result.ExecuteReader();
157
158
159 while (reader.Read())
160 {
161 AvatarPickerAvatar user = new AvatarPickerAvatar();
162 user.AvatarID = new LLUUID((string)reader["UUID"]);
163 user.firstName = (string)reader["username"];
164 user.lastName = (string)reader["surname"];
165 returnlist.Add(user);
166
167 }
168 reader.Close();
169 result.Dispose();
170 }
171 }
172 catch (Exception e)
173 {
174 database.Reconnect();
175 MainLog.Instance.Error(e.ToString());
176 return returnlist;
177 }
178
179
180
181 }
182 else if (querysplit.Length == 1)
183 {
184
185 try
186 {
187 lock (database)
188 {
189 Dictionary<string, string> param = new Dictionary<string, string>();
190 param["first"] = querysplit[0];
191 param["second"] = querysplit[1];
192
193 IDbCommand result =
194 database.Query("SELECT UUID,username,surname FROM users WHERE username = @first OR lastname = @second", param);
195 IDataReader reader = result.ExecuteReader();
196
197
198 while (reader.Read())
199 {
200 AvatarPickerAvatar user = new AvatarPickerAvatar();
201 user.AvatarID = new LLUUID((string)reader["UUID"]);
202 user.firstName = (string)reader["username"];
203 user.lastName = (string)reader["surname"];
204 returnlist.Add(user);
205
206 }
207 reader.Close();
208 result.Dispose();
209 }
210 }
211 catch (Exception e)
212 {
213 database.Reconnect();
214 MainLog.Instance.Error(e.ToString());
215 return returnlist;
216 }
217 }
218 return returnlist;
114 } 219 }
115 220
116 /// <summary> 221 /// <summary>
@@ -120,28 +225,18 @@ namespace OpenSim.Framework.Data.MSSQL
120 /// <returns>The sim profile</returns> 225 /// <returns>The sim profile</returns>
121 public RegionProfileData GetProfileByLLUUID(LLUUID uuid) 226 public RegionProfileData GetProfileByLLUUID(LLUUID uuid)
122 { 227 {
123 Dictionary<string, string> param = new Dictionary<string, string>(); 228 Dictionary<string, string> param = new Dictionary<string, string>();
124 param["uuid"] = uuid.ToStringHyphenated(); 229 param["uuid"] = uuid.ToString();
125
126 IDbCommand result = database.Query("SELECT * FROM regions WHERE uuid = @uuid", param); 230 IDbCommand result = database.Query("SELECT * FROM regions WHERE uuid = @uuid", param);
127 IDataReader reader = result.ExecuteReader(); 231 IDataReader reader = result.ExecuteReader();
128 232
129 RegionProfileData row = database.getRow(reader); 233 RegionProfileData row = database.getRegionRow(reader);
130 reader.Close(); 234 reader.Close();
131 result.Dispose(); 235 result.Dispose();
132 236
133 return row; 237 return row;
134 } 238 }
135 /// <summary>
136 /// // Returns a list of avatar and UUIDs that match the query
137 /// </summary>
138 239
139 public List<OpenSim.Framework.Data.AvatarPickerAvatar> GeneratePickerResults(LLUUID queryID, string query)
140 {
141 //Do nothing yet
142 List<AvatarPickerAvatar> returnlist = new List<AvatarPickerAvatar>();
143 return returnlist;
144 }
145 /// <summary> 240 /// <summary>
146 /// Adds a new specified region to the database 241 /// Adds a new specified region to the database
147 /// </summary> 242 /// </summary>
@@ -149,7 +244,20 @@ namespace OpenSim.Framework.Data.MSSQL
149 /// <returns>A dataresponse enum indicating success</returns> 244 /// <returns>A dataresponse enum indicating success</returns>
150 public DataResponse AddProfile(RegionProfileData profile) 245 public DataResponse AddProfile(RegionProfileData profile)
151 { 246 {
152 if (database.insertRow(profile)) 247
248 try
249 {
250 if (GetProfileByLLUUID(profile.UUID) != null)
251 {
252 return DataResponse.RESPONSE_OK;
253 }
254 }
255 catch (Exception)
256 {
257 System.Console.WriteLine("No regions found. Create new one.");
258 }
259
260 if (database.insertRegionRow(profile))
153 { 261 {
154 return DataResponse.RESPONSE_OK; 262 return DataResponse.RESPONSE_OK;
155 } 263 }
@@ -201,7 +309,5 @@ namespace OpenSim.Framework.Data.MSSQL
201 { 309 {
202 return null; 310 return null;
203 } 311 }
204 // This is here because MSSQL GridData only seems to know about itself o.O
205
206 } 312 }
207} \ No newline at end of file 313}
diff --git a/OpenSim/Framework/Data.MSSQL/MSSQLInventoryData.cs b/OpenSim/Framework/Data.MSSQL/MSSQLInventoryData.cs
new file mode 100644
index 0000000..364e0f2
--- /dev/null
+++ b/OpenSim/Framework/Data.MSSQL/MSSQLInventoryData.cs
@@ -0,0 +1,697 @@
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*/
28using System;
29using System.IO;
30using System.Data;
31using System.Data.SqlClient;
32using System.Collections.Generic;
33using libsecondlife;
34using OpenSim.Framework.Console;
35
36namespace OpenSim.Framework.Data.MSSQL
37{
38 /// <summary>
39 /// A MySQL interface for the inventory server
40 /// </summary>
41 public class MSSQLInventoryData : IInventoryData
42 {
43 /// <summary>
44 /// The database manager
45 /// </summary>
46 private MSSQLManager database;
47
48 /// <summary>
49 /// Loads and initialises this database plugin
50 /// </summary>
51 public void Initialise()
52 {
53 IniFile GridDataMySqlFile = new IniFile("mssql_connection.ini");
54 string settingDataSource = GridDataMySqlFile.ParseFileReadValue("data_source");
55 string settingInitialCatalog = GridDataMySqlFile.ParseFileReadValue("initial_catalog");
56 string settingPersistSecurityInfo = GridDataMySqlFile.ParseFileReadValue("persist_security_info");
57 string settingUserId = GridDataMySqlFile.ParseFileReadValue("user_id");
58 string settingPassword = GridDataMySqlFile.ParseFileReadValue("password");
59
60 database = new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, settingPassword);
61 TestTables();
62 }
63
64 #region Test and initialization code
65
66 private void UpgradeFoldersTable(string tableName)
67 {
68 // null as the version, indicates that the table didn't exist
69 if (tableName == null)
70 {
71 database.ExecuteResourceSql("CreateFoldersTable.sql");
72 //database.ExecuteResourceSql("UpgradeFoldersTableToVersion2.sql");
73 return;
74 }
75
76 }
77
78 private void UpgradeItemsTable(string tableName)
79 {
80 // null as the version, indicates that the table didn't exist
81 if (tableName == null)
82 {
83 database.ExecuteResourceSql("CreateItemsTable.sql");
84 //database.ExecuteResourceSql("UpgradeItemsTableToVersion2.sql");
85 return;
86 }
87 }
88
89 private void TestTables()
90 {
91
92 Dictionary<string, string> tableList = new Dictionary<string, string>();
93
94 tableList["inventoryfolders"] = null;
95 tableList["inventoryitems"] = null;
96
97 database.GetTableVersion(tableList);
98
99 UpgradeFoldersTable(tableList["inventoryfolders"]);
100 UpgradeItemsTable(tableList["inventoryitems"]);
101 }
102 #endregion
103
104 /// <summary>
105 /// The name of this DB provider
106 /// </summary>
107 /// <returns>Name of DB provider</returns>
108 public string getName()
109 {
110 return "MSSQL Inventory Data Interface";
111 }
112
113 /// <summary>
114 /// Closes this DB provider
115 /// </summary>
116 public void Close()
117 {
118 // Do nothing.
119 }
120
121 /// <summary>
122 /// Returns the version of this DB provider
123 /// </summary>
124 /// <returns>A string containing the DB provider</returns>
125 public string getVersion()
126 {
127 return database.getVersion();
128 }
129
130 /// <summary>
131 /// Returns a list of items in a specified folder
132 /// </summary>
133 /// <param name="folderID">The folder to search</param>
134 /// <returns>A list containing inventory items</returns>
135 public List<InventoryItemBase> getInventoryInFolder(LLUUID folderID)
136 {
137 try
138 {
139 lock (database)
140 {
141 List<InventoryItemBase> items = new List<InventoryItemBase>();
142
143 Dictionary<string, string> param = new Dictionary<string, string>();
144 param["parentFolderID"] = folderID.ToStringHyphenated();
145
146 IDbCommand result = database.Query("SELECT * FROM inventoryitems WHERE parentFolderID = @parentFolderID", param);
147 IDataReader reader = result.ExecuteReader();
148
149 while(reader.Read())
150 items.Add(readInventoryItem(reader));
151
152 reader.Close();
153 result.Dispose();
154
155 return items;
156 }
157 }
158 catch (Exception e)
159 {
160 database.Reconnect();
161 MainLog.Instance.Error(e.ToString());
162 return null;
163 }
164 }
165
166 /// <summary>
167 /// Returns a list of the root folders within a users inventory
168 /// </summary>
169 /// <param name="user">The user whos inventory is to be searched</param>
170 /// <returns>A list of folder objects</returns>
171 public List<InventoryFolderBase> getUserRootFolders(LLUUID user)
172 {
173 try
174 {
175 lock (database)
176 {
177
178 Dictionary<string, string> param = new Dictionary<string, string>();
179 param["uuid"] = user.ToStringHyphenated();
180 param["zero"] = LLUUID.Zero.ToStringHyphenated();
181
182 IDbCommand result = database.Query("SELECT * FROM inventoryfolders WHERE parentFolderID = @zero AND agentID = @uuid", param);
183 IDataReader reader = result.ExecuteReader();
184
185 List<InventoryFolderBase> items = new List<InventoryFolderBase>();
186 while(reader.Read())
187 items.Add(readInventoryFolder(reader));
188
189
190 reader.Close();
191 result.Dispose();
192
193 return items;
194 }
195 }
196 catch (Exception e)
197 {
198 database.Reconnect();
199 MainLog.Instance.Error(e.ToString());
200 return null;
201 }
202 }
203
204 /// <summary>
205 /// Returns the users inventory root folder.
206 /// </summary>
207 /// <param name="user"></param>
208 /// <returns></returns>
209 public InventoryFolderBase getUserRootFolder(LLUUID user)
210 {
211 try
212 {
213 lock (database)
214 {
215 Dictionary<string, string> param = new Dictionary<string, string>();
216 param["uuid"] = user.ToStringHyphenated();
217 param["zero"] = LLUUID.Zero.ToStringHyphenated();
218
219 IDbCommand result = database.Query("SELECT * FROM inventoryfolders WHERE parentFolderID = @zero AND agentID = @uuid", param);
220 IDataReader reader = result.ExecuteReader();
221
222 List<InventoryFolderBase> items = new List<InventoryFolderBase>();
223 while(reader.Read())
224 items.Add(readInventoryFolder(reader));
225
226 InventoryFolderBase rootFolder = null;
227 if (items.Count > 0) {
228 rootFolder = items[0]; //should only be one folder with parent set to zero (the root one).
229 }
230
231 reader.Close();
232 result.Dispose();
233
234 return rootFolder;
235 }
236 }
237 catch (Exception e)
238 {
239 database.Reconnect();
240 MainLog.Instance.Error(e.ToString());
241 return null;
242 }
243 }
244
245 /// <summary>
246 /// Returns a list of folders in a users inventory contained within the specified folder
247 /// </summary>
248 /// <param name="parentID">The folder to search</param>
249 /// <returns>A list of inventory folders</returns>
250 public List<InventoryFolderBase> getInventoryFolders(LLUUID parentID)
251 {
252 try
253 {
254 lock (database)
255 {
256 Dictionary<string, string> param = new Dictionary<string, string>();
257 param["parentFolderID"] = parentID.ToStringHyphenated();
258
259
260 IDbCommand result = database.Query("SELECT * FROM inventoryfolders WHERE parentFolderID = @parentFolderID", param);
261 IDataReader reader = result.ExecuteReader();
262
263 List<InventoryFolderBase> items = new List<InventoryFolderBase>();
264
265 while(reader.Read())
266 items.Add(readInventoryFolder(reader));
267
268 reader.Close();
269 result.Dispose();
270
271 return items;
272 }
273 }
274 catch (Exception e)
275 {
276 database.Reconnect();
277 MainLog.Instance.Error(e.ToString());
278 return null;
279 }
280 }
281
282 /// <summary>
283 /// Reads a one item from an SQL result
284 /// </summary>
285 /// <param name="reader">The SQL Result</param>
286 /// <returns>the item read</returns>
287 public InventoryItemBase readInventoryItem(IDataReader reader)
288 {
289 try
290 {
291 InventoryItemBase item = new InventoryItemBase();
292
293 item.inventoryID = new LLUUID((string)reader["inventoryID"]);
294 item.assetID = new LLUUID((string)reader["assetID"]);
295 item.assetType = (int)reader["assetType"];
296 item.parentFolderID = new LLUUID((string)reader["parentFolderID"]);
297 item.avatarID = new LLUUID((string)reader["avatarID"]);
298 item.inventoryName = (string)reader["inventoryName"];
299 item.inventoryDescription = (string)reader["inventoryDescription"];
300 item.inventoryNextPermissions = Convert.ToUInt32(reader["inventoryNextPermissions"]);
301 item.inventoryCurrentPermissions = Convert.ToUInt32(reader["inventoryCurrentPermissions"]);
302 item.invType = (int)reader["invType"];
303 item.creatorsID = new LLUUID((string)reader["creatorID"]);
304 item.inventoryBasePermissions = Convert.ToUInt32(reader["inventoryBasePermissions"]);
305 item.inventoryEveryOnePermissions = Convert.ToUInt32(reader["inventoryEveryOnePermissions"]);
306 return item;
307 }
308 catch (SqlException e)
309 {
310 MainLog.Instance.Error(e.ToString());
311 }
312
313 return null;
314 }
315
316 /// <summary>
317 /// Returns a specified inventory item
318 /// </summary>
319 /// <param name="item">The item to return</param>
320 /// <returns>An inventory item</returns>
321 public InventoryItemBase getInventoryItem(LLUUID itemID)
322 {
323
324 try
325 {
326 lock (database)
327 {
328 Dictionary<string, string> param = new Dictionary<string, string>();
329 param["inventoryID"] = itemID.ToStringHyphenated();
330
331 IDbCommand result = database.Query("SELECT * FROM inventoryitems WHERE inventoryID = @inventoryID", param);
332 IDataReader reader = result.ExecuteReader();
333
334 InventoryItemBase item = null;
335 if(reader.Read())
336 item = readInventoryItem(reader);
337
338 reader.Close();
339 result.Dispose();
340
341 return item;
342 }
343 }
344 catch (Exception e)
345 {
346 database.Reconnect();
347 MainLog.Instance.Error(e.ToString());
348 }
349 return null;
350 }
351
352 /// <summary>
353 /// Reads a list of inventory folders returned by a query.
354 /// </summary>
355 /// <param name="reader">A MySQL Data Reader</param>
356 /// <returns>A List containing inventory folders</returns>
357 protected InventoryFolderBase readInventoryFolder(IDataReader reader)
358 {
359 try
360 {
361 InventoryFolderBase folder = new InventoryFolderBase();
362 folder.agentID = new LLUUID((string)reader["agentID"]);
363 folder.parentID = new LLUUID((string)reader["parentFolderID"]);
364 folder.folderID = new LLUUID((string)reader["folderID"]);
365 folder.name = (string)reader["folderName"];
366 folder.type = (short)reader["type"];
367 folder.version = (ushort)((int)reader["version"]);
368 return folder;
369 }
370 catch (Exception e)
371 {
372 MainLog.Instance.Error(e.ToString());
373 }
374
375 return null;
376 }
377
378
379 /// <summary>
380 /// Returns a specified inventory folder
381 /// </summary>
382 /// <param name="folder">The folder to return</param>
383 /// <returns>A folder class</returns>
384 public InventoryFolderBase getInventoryFolder(LLUUID folderID)
385 {
386 try
387 {
388 lock (database)
389 {
390 Dictionary<string, string> param = new Dictionary<string,string>();
391 param["uuid"] = folderID.ToStringHyphenated();
392
393 IDbCommand result = database.Query("SELECT * FROM inventoryfolders WHERE folderID = @uuid", param);
394 IDataReader reader = result.ExecuteReader();
395
396 reader.Read();
397 InventoryFolderBase folder = readInventoryFolder(reader);
398 reader.Close();
399 result.Dispose();
400
401 return folder;
402 }
403 }
404 catch (Exception e)
405 {
406 database.Reconnect();
407 MainLog.Instance.Error(e.ToString());
408 return null;
409 }
410 }
411
412 /// <summary>
413 /// Adds a specified item to the database
414 /// </summary>
415 /// <param name="item">The inventory item</param>
416 public void addInventoryItem(InventoryItemBase item)
417 {
418 string sql = "INSERT INTO inventoryitems";
419 sql += "([inventoryID], [assetID], [assetType], [parentFolderID], [avatarID], [inventoryName], [inventoryDescription], [inventoryNextPermissions], [inventoryCurrentPermissions], [invType], [creatorID], [inventoryBasePermissions], [inventoryEveryOnePermissions]) VALUES ";
420 sql += "(@inventoryID, @assetID, @assetType, @parentFolderID, @avatarID, @inventoryName, @inventoryDescription, @inventoryNextPermissions, @inventoryCurrentPermissions, @invType, @creatorID, @inventoryBasePermissions, @inventoryEveryOnePermissions);";
421
422 try
423 {
424 Dictionary<string, string> param = new Dictionary<string, string>();
425 param["inventoryID"] = item.inventoryID.ToStringHyphenated();
426 param["assetID"] = item.assetID.ToStringHyphenated();
427 param["assetType"] = item.assetType.ToString();
428 param["parentFolderID"] = item.parentFolderID.ToStringHyphenated();
429 param["avatarID"] = item.avatarID.ToStringHyphenated();
430 param["inventoryName"] = item.inventoryName;
431 param["inventoryDescription"] = item.inventoryDescription;
432 param["inventoryNextPermissions"] = item.inventoryNextPermissions.ToString();
433 param["inventoryCurrentPermissions"] = item.inventoryCurrentPermissions.ToString();
434 param["invType"] = Convert.ToString(item.invType);
435 param["creatorID"] = item.creatorsID.ToStringHyphenated();
436 param["inventoryBasePermissions"] = Convert.ToString(item.inventoryBasePermissions);
437 param["inventoryEveryOnePermissions"] = Convert.ToString(item.inventoryEveryOnePermissions);
438
439 IDbCommand result = database.Query(sql, param);
440 result.ExecuteNonQuery();
441 result.Dispose();
442
443 }
444 catch (SqlException e)
445 {
446 MainLog.Instance.Error(e.ToString());
447 }
448 }
449
450 /// <summary>
451 /// Updates the specified inventory item
452 /// </summary>
453 /// <param name="item">Inventory item to update</param>
454 public void updateInventoryItem(InventoryItemBase item)
455 {
456 SqlCommand command = new SqlCommand("UPDATE inventoryitems set inventoryID = @inventoryID, " +
457 "assetID = @assetID, " +
458 "assetType = @assetType" +
459 "parentFolderID = @parentFolderID" +
460 "avatarID = @avatarID" +
461 "inventoryName = @inventoryName"+
462 "inventoryDescription = @inventoryDescription" +
463 "inventoryNextPermissions = @inventoryNextPermissions" +
464 "inventoryCurrentPermissions = @inventoryCurrentPermissions" +
465 "invType = @invType" +
466 "creatorID = @creatorID" +
467 "inventoryBasePermissions = @inventoryBasePermissions" +
468 "inventoryEveryOnePermissions = @inventoryEveryOnePermissions) where " +
469 "invenoryID = @keyInventoryID;", database.getConnection());
470 SqlParameter param1 = new SqlParameter("@inventoryID", item.inventoryID.ToStringHyphenated());
471 SqlParameter param2 = new SqlParameter("@assetID", item.assetID);
472 SqlParameter param3 = new SqlParameter("@assetType", item.assetType);
473 SqlParameter param4 = new SqlParameter("@parentFolderID", item.parentFolderID);
474 SqlParameter param5 = new SqlParameter("@avatarID", item.avatarID);
475 SqlParameter param6 = new SqlParameter("@inventoryName", item.inventoryName);
476 SqlParameter param7 = new SqlParameter("@inventoryDescription", item.inventoryDescription);
477 SqlParameter param8 = new SqlParameter("@inventoryNextPermissions", item.inventoryNextPermissions);
478 SqlParameter param9 = new SqlParameter("@inventoryCurrentPermissions", item.inventoryCurrentPermissions);
479 SqlParameter param10 = new SqlParameter("@invType", item.invType);
480 SqlParameter param11 = new SqlParameter("@creatorID", item.creatorsID);
481 SqlParameter param12 = new SqlParameter("@inventoryBasePermissions", item.inventoryBasePermissions);
482 SqlParameter param13 = new SqlParameter("@inventoryEveryOnePermissions", item.inventoryEveryOnePermissions);
483 SqlParameter param14 = new SqlParameter("@keyInventoryID", item.inventoryID.ToStringHyphenated());
484 command.Parameters.Add(param1);
485 command.Parameters.Add(param2);
486 command.Parameters.Add(param3);
487 command.Parameters.Add(param4);
488 command.Parameters.Add(param5);
489 command.Parameters.Add(param6);
490 command.Parameters.Add(param7);
491 command.Parameters.Add(param8);
492 command.Parameters.Add(param9);
493 command.Parameters.Add(param10);
494 command.Parameters.Add(param11);
495 command.Parameters.Add(param12);
496 command.Parameters.Add(param13);
497 command.Parameters.Add(param14);
498
499 try
500 {
501 command.ExecuteNonQuery();
502 }
503 catch (Exception e)
504 {
505 MainLog.Instance.Error(e.ToString());
506 }
507
508 }
509
510 /// <summary>
511 ///
512 /// </summary>
513 /// <param name="item"></param>
514 public void deleteInventoryItem(LLUUID itemID)
515 {
516 try
517 {
518 Dictionary<string, string> param = new Dictionary<string, string>();
519 param["uuid"] = itemID.ToStringHyphenated();
520
521 IDbCommand cmd = database.Query("DELETE FROM inventoryitems WHERE inventoryID=@uuid", param);
522 cmd.ExecuteNonQuery();
523 cmd.Dispose();
524
525
526 }
527 catch (SqlException e)
528 {
529 database.Reconnect();
530 MainLog.Instance.Error(e.ToString());
531 }
532 }
533
534 /// <summary>
535 /// Creates a new inventory folder
536 /// </summary>
537 /// <param name="folder">Folder to create</param>
538 public void addInventoryFolder(InventoryFolderBase folder)
539 {
540 string sql = "INSERT INTO inventoryfolders ([folderID], [agentID], [parentFolderID], [folderName], [type], [version]) VALUES ";
541 sql += "(@folderID, @agentID, @parentFolderID, @folderName, @type, @version);";
542
543
544 Dictionary<string, string> param = new Dictionary<string, string>();
545 param["folderID"] = folder.folderID.ToStringHyphenated();
546 param["agentID"] = folder.agentID.ToStringHyphenated();
547 param["parentFolderID"] = folder.parentID.ToStringHyphenated();
548 param["folderName"] = folder.name;
549 param["type"] = Convert.ToString(folder.type);
550 param["version"] = Convert.ToString(folder.version);
551
552 try
553 {
554 IDbCommand result = database.Query(sql, param);
555 result.ExecuteNonQuery();
556 result.Dispose();
557 }
558 catch (Exception e)
559 {
560 MainLog.Instance.Error(e.ToString());
561 }
562 }
563
564 /// <summary>
565 /// Updates an inventory folder
566 /// </summary>
567 /// <param name="folder">Folder to update</param>
568
569 public void updateInventoryFolder(InventoryFolderBase folder)
570 {
571 SqlCommand command = new SqlCommand("UPDATE inventoryfolders set folderID = @folderID, " +
572 "agentID = @agentID, " +
573 "parentFolderID = @parentFolderID," +
574 "folderName = @folderName," +
575 "type = @type," +
576 "version = @version where " +
577 "folderID = @keyFolderID;", database.getConnection());
578 SqlParameter param1 = new SqlParameter("@folderID", folder.folderID.ToStringHyphenated());
579 SqlParameter param2 = new SqlParameter("@agentID", folder.agentID.ToStringHyphenated());
580 SqlParameter param3 = new SqlParameter("@parentFolderID", folder.parentID.ToStringHyphenated());
581 SqlParameter param4 = new SqlParameter("@folderName", folder.name);
582 SqlParameter param5 = new SqlParameter("@type", folder.type);
583 SqlParameter param6 = new SqlParameter("@version", folder.version);
584 SqlParameter param7 = new SqlParameter("@keyFolderID", folder.folderID.ToStringHyphenated());
585 command.Parameters.Add(param1);
586 command.Parameters.Add(param2);
587 command.Parameters.Add(param3);
588 command.Parameters.Add(param4);
589 command.Parameters.Add(param5);
590 command.Parameters.Add(param6);
591 command.Parameters.Add(param7);
592
593 try
594 {
595 command.ExecuteNonQuery();
596 }
597 catch (Exception e)
598 {
599 MainLog.Instance.Error(e.ToString());
600 }
601
602
603 }
604
605
606 /// <summary>
607 /// Append a list of all the child folders of a parent folder
608 /// </summary>
609 /// <param name="folders">list where folders will be appended</param>
610 /// <param name="parentID">ID of parent</param>
611 protected void getInventoryFolders(ref List<InventoryFolderBase> folders, LLUUID parentID)
612 {
613 List<InventoryFolderBase> subfolderList = getInventoryFolders(parentID);
614
615 foreach (InventoryFolderBase f in subfolderList)
616 folders.Add(f);
617 }
618
619 /// <summary>
620 /// Returns all child folders in the hierarchy from the parent folder and down
621 /// </summary>
622 /// <param name="parentID">The folder to get subfolders for</param>
623 /// <returns>A list of inventory folders</returns>
624 protected List<InventoryFolderBase> getFolderHierarchy(LLUUID parentID)
625 {
626 List<InventoryFolderBase> folders = new List<InventoryFolderBase>();
627 getInventoryFolders(ref folders, parentID);
628
629 for (int i = 0; i < folders.Count; i++)
630 getInventoryFolders(ref folders, folders[i].folderID);
631
632 return folders;
633 }
634
635 protected void deleteOneFolder(LLUUID folderID)
636 {
637 try
638 {
639 Dictionary<string, string> param = new Dictionary<string, string>();
640 param["folderID"] = folderID.ToStringHyphenated();
641
642 IDbCommand cmd = database.Query("DELETE FROM inventoryfolders WHERE folderID=@folderID", param);
643 cmd.ExecuteNonQuery();
644 cmd.Dispose();
645
646 }
647 catch (SqlException e)
648 {
649 database.Reconnect();
650 MainLog.Instance.Error(e.ToString());
651 }
652 }
653
654 protected void deleteItemsInFolder(LLUUID folderID)
655 {
656 try
657 {
658 Dictionary<string, string> param = new Dictionary<string, string>();
659 param["parentFolderID"] = folderID.ToStringHyphenated();
660
661
662 IDbCommand cmd = database.Query("DELETE FROM inventoryitems WHERE parentFolderID=@parentFolderID", param);
663 cmd.ExecuteNonQuery();
664 cmd.Dispose();
665 }
666 catch (SqlException e)
667 {
668 database.Reconnect();
669 MainLog.Instance.Error(e.ToString());
670 }
671 }
672
673
674 /// <summary>
675 /// Delete an inventory folder
676 /// </summary>
677 /// <param name="folderId">Id of folder to delete</param>
678 public void deleteInventoryFolder(LLUUID folderID)
679 {
680 lock (database)
681 {
682 List<InventoryFolderBase> subFolders = getFolderHierarchy(folderID);
683
684 //Delete all sub-folders
685 foreach (InventoryFolderBase f in subFolders)
686 {
687 deleteOneFolder(f.folderID);
688 deleteItemsInFolder(f.folderID);
689 }
690
691 //Delete the actual row
692 deleteOneFolder(folderID);
693 deleteItemsInFolder(folderID);
694 }
695 }
696 }
697} \ No newline at end of file
diff --git a/OpenSim/Framework/Data.MSSQL/MSSQLLogData.cs b/OpenSim/Framework/Data.MSSQL/MSSQLLogData.cs
new file mode 100644
index 0000000..8a59bca
--- /dev/null
+++ b/OpenSim/Framework/Data.MSSQL/MSSQLLogData.cs
@@ -0,0 +1,104 @@
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*/
28using System;
29
30namespace OpenSim.Framework.Data.MSSQL
31{
32 /// <summary>
33 /// An interface to the log database for MySQL
34 /// </summary>
35 class MSSQLLogData : ILogData
36 {
37 /// <summary>
38 /// The database manager
39 /// </summary>
40 public MSSQLManager database;
41
42 /// <summary>
43 /// Artificial constructor called when the plugin is loaded
44 /// </summary>
45 public void Initialise()
46 {
47 IniFile GridDataMySqlFile = new IniFile("mssql_connection.ini");
48 string settingDataSource = GridDataMySqlFile.ParseFileReadValue("data_source");
49 string settingInitialCatalog = GridDataMySqlFile.ParseFileReadValue("initial_catalog");
50 string settingPersistSecurityInfo = GridDataMySqlFile.ParseFileReadValue("persist_security_info");
51 string settingUserId = GridDataMySqlFile.ParseFileReadValue("user_id");
52 string settingPassword = GridDataMySqlFile.ParseFileReadValue("password");
53
54 database = new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, settingPassword);
55 }
56
57 /// <summary>
58 /// Saves a log item to the database
59 /// </summary>
60 /// <param name="serverDaemon">The daemon triggering the event</param>
61 /// <param name="target">The target of the action (region / agent UUID, etc)</param>
62 /// <param name="methodCall">The method call where the problem occured</param>
63 /// <param name="arguments">The arguments passed to the method</param>
64 /// <param name="priority">How critical is this?</param>
65 /// <param name="logMessage">The message to log</param>
66 public void saveLog(string serverDaemon, string target, string methodCall, string arguments, int priority, string logMessage)
67 {
68 try
69 {
70 database.insertLogRow(serverDaemon, target, methodCall, arguments, priority, logMessage);
71 }
72 catch
73 {
74 database.Reconnect();
75 }
76 }
77
78 /// <summary>
79 /// Returns the name of this DB provider
80 /// </summary>
81 /// <returns>A string containing the DB provider name</returns>
82 public string getName()
83 {
84 return "MSSQL Logdata Interface";
85 }
86
87 /// <summary>
88 /// Closes the database provider
89 /// </summary>
90 public void Close()
91 {
92 // Do nothing.
93 }
94
95 /// <summary>
96 /// Returns the version of this DB provider
97 /// </summary>
98 /// <returns>A string containing the provider version</returns>
99 public string getVersion()
100 {
101 return "0.1";
102 }
103 }
104} \ No newline at end of file
diff --git a/OpenSim/Framework/Data.MSSQL/MSSQLManager.cs b/OpenSim/Framework/Data.MSSQL/MSSQLManager.cs
index c0b2edf..4bca2e1 100644
--- a/OpenSim/Framework/Data.MSSQL/MSSQLManager.cs
+++ b/OpenSim/Framework/Data.MSSQL/MSSQLManager.cs
@@ -29,19 +29,28 @@ using System;
29using System.Collections.Generic; 29using System.Collections.Generic;
30using System.Data; 30using System.Data;
31using System.Data.SqlClient; 31using System.Data.SqlClient;
32using System.IO;
33using System.Reflection;
32using libsecondlife; 34using libsecondlife;
33 35
36using OpenSim.Framework.Console;
37
34namespace OpenSim.Framework.Data.MSSQL 38namespace OpenSim.Framework.Data.MSSQL
35{ 39{
36 /// <summary> 40 /// <summary>
37 /// A management class for the MS SQL Storage Engine 41 /// A management class for the MS SQL Storage Engine
38 /// </summary> 42 /// </summary>
39 internal class MSSqlManager 43 class MSSQLManager
40 { 44 {
41 /// <summary> 45 /// <summary>
42 /// The database connection object 46 /// The database connection object
43 /// </summary> 47 /// </summary>
44 private IDbConnection dbcon; 48 IDbConnection dbcon;
49
50 /// <summary>
51 /// Connection string for ADO.net
52 /// </summary>
53 private string connectionString;
45 54
46 /// <summary> 55 /// <summary>
47 /// Initialises and creates a new Sql connection and maintains it. 56 /// Initialises and creates a new Sql connection and maintains it.
@@ -51,14 +60,14 @@ namespace OpenSim.Framework.Data.MSSQL
51 /// <param name="username">The username logging into the database</param> 60 /// <param name="username">The username logging into the database</param>
52 /// <param name="password">The password for the user logging in</param> 61 /// <param name="password">The password for the user logging in</param>
53 /// <param name="cpooling">Whether to use connection pooling or not, can be one of the following: 'yes', 'true', 'no' or 'false', if unsure use 'false'.</param> 62 /// <param name="cpooling">Whether to use connection pooling or not, can be one of the following: 'yes', 'true', 'no' or 'false', if unsure use 'false'.</param>
54 public MSSqlManager(string hostname, string database, string username, string password, string cpooling) 63 public MSSQLManager(string dataSource, string initialCatalog, string persistSecurityInfo, string userId, string password)
55 { 64 {
56 try 65 try
57 { 66 {
58 string connectionString = "Server=" + hostname + ";Database=" + database + ";User ID=" + username + 67
59 ";Password=" + password + ";Pooling=" + cpooling + ";"; 68 connectionString = "Data Source=" + dataSource + ";Initial Catalog=" + initialCatalog + ";Persist Security Info=" + persistSecurityInfo + ";User ID=" + userId + ";Password=" + password+";";
60 dbcon = new SqlConnection(connectionString); 69 dbcon = new SqlConnection(connectionString);
61 70 TestTables(dbcon);
62 dbcon.Open(); 71 dbcon.Open();
63 } 72 }
64 catch (Exception e) 73 catch (Exception e)
@@ -67,6 +76,144 @@ namespace OpenSim.Framework.Data.MSSQL
67 } 76 }
68 } 77 }
69 78
79 private bool TestTables(IDbConnection conn)
80 {
81 IDbCommand cmd = this.Query("SELECT * FROM regions", new Dictionary<string, string>());
82 //SqlCommand cmd = (SqlCommand)dbcon.CreateCommand();
83 //cmd.CommandText = "SELECT * FROM regions";
84 try
85 {
86 conn.Open();
87 cmd.ExecuteNonQuery();
88 cmd.Dispose();
89 conn.Close();
90 }
91 catch (Exception)
92 {
93 MainLog.Instance.Verbose("DATASTORE", "MSSQL Database doesn't exist... creating");
94 InitDB(conn);
95 }
96 return true;
97 }
98
99 private void InitDB(IDbConnection conn)
100 {
101 string createRegions = defineTable(createRegionsTable());
102 Dictionary<string, string> param = new Dictionary<string, string>();
103 IDbCommand pcmd = this.Query(createRegions, param);
104 if (conn.State == ConnectionState.Closed) {
105 conn.Open();
106 }
107 pcmd.ExecuteNonQuery();
108 pcmd.Dispose();
109
110 this.ExecuteResourceSql("Mssql-users.sql");
111 this.ExecuteResourceSql("Mssql-agents.sql");
112 this.ExecuteResourceSql("Mssql-logs.sql");
113
114 conn.Close();
115
116 }
117
118 private DataTable createRegionsTable()
119 {
120 DataTable regions = new DataTable("regions");
121
122 createCol(regions, "regionHandle", typeof(ulong));
123 createCol(regions, "regionName", typeof(System.String));
124 createCol(regions, "uuid", typeof(System.String));
125
126 createCol(regions, "regionRecvKey", typeof(System.String));
127 createCol(regions, "regionSecret", typeof(System.String));
128 createCol(regions, "regionSendKey", typeof(System.String));
129
130 createCol(regions, "regionDataURI", typeof(System.String));
131 createCol(regions, "serverIP", typeof(System.String));
132 createCol(regions, "serverPort", typeof(System.String));
133 createCol(regions, "serverURI", typeof(System.String));
134
135
136 createCol(regions, "locX", typeof(uint));
137 createCol(regions, "locY", typeof(uint));
138 createCol(regions, "locZ", typeof(uint));
139
140 createCol(regions, "eastOverrideHandle", typeof(ulong));
141 createCol(regions, "westOverrideHandle", typeof(ulong));
142 createCol(regions, "southOverrideHandle", typeof(ulong));
143 createCol(regions, "northOverrideHandle", typeof(ulong));
144
145 createCol(regions, "regionAssetURI", typeof(System.String));
146 createCol(regions, "regionAssetRecvKey", typeof(System.String));
147 createCol(regions, "regionAssetSendKey", typeof(System.String));
148
149 createCol(regions, "regionUserURI", typeof(System.String));
150 createCol(regions, "regionUserRecvKey", typeof(System.String));
151 createCol(regions, "regionUserSendKey", typeof(System.String));
152
153 createCol(regions, "regionMapTexture", typeof(System.String));
154 createCol(regions, "serverHttpPort", typeof(System.String));
155 createCol(regions, "serverRemotingPort", typeof(uint));
156
157 // Add in contraints
158 regions.PrimaryKey = new DataColumn[] { regions.Columns["UUID"] };
159 return regions;
160 }
161
162 protected static void createCol(DataTable dt, string name, System.Type type)
163 {
164 DataColumn col = new DataColumn(name, type);
165 dt.Columns.Add(col);
166 }
167
168 protected static string defineTable(DataTable dt)
169 {
170 string sql = "create table " + dt.TableName + "(";
171 string subsql = "";
172 foreach (DataColumn col in dt.Columns)
173 {
174 if (subsql.Length > 0)
175 { // a map function would rock so much here
176 subsql += ",\n";
177 }
178
179 subsql += col.ColumnName + " " + SqlType(col.DataType);
180 if (col == dt.PrimaryKey[0])
181 {
182 subsql += " primary key";
183 }
184 }
185 sql += subsql;
186 sql += ")";
187 return sql;
188 }
189
190
191 // this is something we'll need to implement for each db
192 // slightly differently.
193 private static string SqlType(Type type)
194 {
195 if (type == typeof(System.String))
196 {
197 return "varchar(255)";
198 }
199 else if (type == typeof(System.Int32))
200 {
201 return "integer";
202 }
203 else if (type == typeof(System.Double))
204 {
205 return "float";
206 }
207 else if (type == typeof(System.Byte[]))
208 {
209 return "image";
210 }
211 else
212 {
213 return "varchar(255)";
214 }
215 }
216
70 /// <summary> 217 /// <summary>
71 /// Shuts down the database connection 218 /// Shuts down the database connection
72 /// </summary> 219 /// </summary>
@@ -77,6 +224,29 @@ namespace OpenSim.Framework.Data.MSSQL
77 } 224 }
78 225
79 /// <summary> 226 /// <summary>
227 /// Reconnects to the database
228 /// </summary>
229 public void Reconnect()
230 {
231 lock (dbcon)
232 {
233 try
234 {
235 //string connectionString = "Data Source=WRK-OU-738\\SQLEXPRESS;Initial Catalog=rex;Persist Security Info=True;User ID=sa;Password=rex";
236 // Close the DB connection
237 dbcon.Close();
238 // Try reopen it
239 dbcon = new SqlConnection(connectionString);
240 dbcon.Open();
241 }
242 catch (Exception e)
243 {
244 MainLog.Instance.Error("Unable to reconnect to database " + e.ToString());
245 }
246 }
247 }
248
249 /// <summary>
80 /// Runs a query with protection against SQL Injection by using parameterised input. 250 /// Runs a query with protection against SQL Injection by using parameterised input.
81 /// </summary> 251 /// </summary>
82 /// <param name="sql">The SQL string - replace any variables such as WHERE x = "y" with WHERE x = @y</param> 252 /// <param name="sql">The SQL string - replace any variables such as WHERE x = "y" with WHERE x = @y</param>
@@ -84,14 +254,14 @@ namespace OpenSim.Framework.Data.MSSQL
84 /// <returns>A Sql DB Command</returns> 254 /// <returns>A Sql DB Command</returns>
85 public IDbCommand Query(string sql, Dictionary<string, string> parameters) 255 public IDbCommand Query(string sql, Dictionary<string, string> parameters)
86 { 256 {
87 SqlCommand dbcommand = (SqlCommand) dbcon.CreateCommand(); 257 SqlCommand dbcommand = (SqlCommand)dbcon.CreateCommand();
88 dbcommand.CommandText = sql; 258 dbcommand.CommandText = sql;
89 foreach (KeyValuePair<string, string> param in parameters) 259 foreach (KeyValuePair<string, string> param in parameters)
90 { 260 {
91 dbcommand.Parameters.AddWithValue(param.Key, param.Value); 261 dbcommand.Parameters.AddWithValue(param.Key, param.Value);
92 } 262 }
93 263
94 return (IDbCommand) dbcommand; 264 return (IDbCommand)dbcommand;
95 } 265 }
96 266
97 /// <summary> 267 /// <summary>
@@ -99,74 +269,201 @@ namespace OpenSim.Framework.Data.MSSQL
99 /// </summary> 269 /// </summary>
100 /// <param name="reader">An active database reader</param> 270 /// <param name="reader">An active database reader</param>
101 /// <returns>A region row</returns> 271 /// <returns>A region row</returns>
102 public RegionProfileData getRow(IDataReader reader) 272 public RegionProfileData getRegionRow(IDataReader reader)
103 { 273 {
104 RegionProfileData regionprofile = new RegionProfileData(); 274 RegionProfileData regionprofile = new RegionProfileData();
105 275
106 if (reader.Read()) 276 if (reader.Read())
107 { 277 {
108 // Region Main 278 // Region Main
109 regionprofile.regionHandle = (ulong) reader["regionHandle"]; 279 regionprofile.regionHandle = Convert.ToUInt64(reader["regionHandle"]);
110 regionprofile.regionName = (string) reader["regionName"]; 280 regionprofile.regionName = (string)reader["regionName"];
111 regionprofile.UUID = new LLUUID((string) reader["uuid"]); 281 regionprofile.UUID = new LLUUID((string)reader["uuid"]);
112 282
113 // Secrets 283 // Secrets
114 regionprofile.regionRecvKey = (string) reader["regionRecvKey"]; 284 regionprofile.regionRecvKey = (string)reader["regionRecvKey"];
115 regionprofile.regionSecret = (string) reader["regionSecret"]; 285 regionprofile.regionSecret = (string)reader["regionSecret"];
116 regionprofile.regionSendKey = (string) reader["regionSendKey"]; 286 regionprofile.regionSendKey = (string)reader["regionSendKey"];
117 287
118 // Region Server 288 // Region Server
119 regionprofile.regionDataURI = (string) reader["regionDataURI"]; 289 regionprofile.regionDataURI = (string)reader["regionDataURI"];
120 regionprofile.regionOnline = false; // Needs to be pinged before this can be set. 290 regionprofile.regionOnline = false; // Needs to be pinged before this can be set.
121 regionprofile.serverIP = (string) reader["serverIP"]; 291 regionprofile.serverIP = (string)reader["serverIP"];
122 regionprofile.serverPort = (uint) reader["serverPort"]; 292 regionprofile.serverPort = Convert.ToUInt32(reader["serverPort"]);
123 regionprofile.serverURI = (string) reader["serverURI"]; 293 regionprofile.serverURI = (string)reader["serverURI"];
294 regionprofile.httpPort = Convert.ToUInt32(reader["serverHttpPort"]);
295 regionprofile.remotingPort = Convert.ToUInt32(reader["serverRemotingPort"]);
296
124 297
125 // Location 298 // Location
126 regionprofile.regionLocX = (uint) ((int) reader["locX"]); 299 regionprofile.regionLocX = Convert.ToUInt32(reader["locX"]);
127 regionprofile.regionLocY = (uint) ((int) reader["locY"]); 300 regionprofile.regionLocY = Convert.ToUInt32(reader["locY"]);
128 regionprofile.regionLocZ = (uint) ((int) reader["locZ"]); 301 regionprofile.regionLocZ = Convert.ToUInt32(reader["locZ"]);
129 302
130 // Neighbours - 0 = No Override 303 // Neighbours - 0 = No Override
131 regionprofile.regionEastOverrideHandle = (ulong) reader["eastOverrideHandle"]; 304 regionprofile.regionEastOverrideHandle = Convert.ToUInt64(reader["eastOverrideHandle"]);
132 regionprofile.regionWestOverrideHandle = (ulong) reader["westOverrideHandle"]; 305 regionprofile.regionWestOverrideHandle = Convert.ToUInt64(reader["westOverrideHandle"]);
133 regionprofile.regionSouthOverrideHandle = (ulong) reader["southOverrideHandle"]; 306 regionprofile.regionSouthOverrideHandle = Convert.ToUInt64(reader["southOverrideHandle"]);
134 regionprofile.regionNorthOverrideHandle = (ulong) reader["northOverrideHandle"]; 307 regionprofile.regionNorthOverrideHandle = Convert.ToUInt64(reader["northOverrideHandle"]);
135 308
136 // Assets 309 // Assets
137 regionprofile.regionAssetURI = (string) reader["regionAssetURI"]; 310 regionprofile.regionAssetURI = (string)reader["regionAssetURI"];
138 regionprofile.regionAssetRecvKey = (string) reader["regionAssetRecvKey"]; 311 regionprofile.regionAssetRecvKey = (string)reader["regionAssetRecvKey"];
139 regionprofile.regionAssetSendKey = (string) reader["regionAssetSendKey"]; 312 regionprofile.regionAssetSendKey = (string)reader["regionAssetSendKey"];
140 313
141 // Userserver 314 // Userserver
142 regionprofile.regionUserURI = (string) reader["regionUserURI"]; 315 regionprofile.regionUserURI = (string)reader["regionUserURI"];
143 regionprofile.regionUserRecvKey = (string) reader["regionUserRecvKey"]; 316 regionprofile.regionUserRecvKey = (string)reader["regionUserRecvKey"];
144 regionprofile.regionUserSendKey = (string) reader["regionUserSendKey"]; 317 regionprofile.regionUserSendKey = (string)reader["regionUserSendKey"];
318
319 // World Map Addition
320 string tempRegionMap = reader["regionMapTexture"].ToString();
321 if (tempRegionMap != "")
322 {
323 regionprofile.regionMapTextureID = new LLUUID(tempRegionMap);
324 }
325 else
326 {
327 regionprofile.regionMapTextureID = new LLUUID();
328 }
145 } 329 }
146 else 330 else
147 { 331 {
332 reader.Close();
148 throw new Exception("No rows to return"); 333 throw new Exception("No rows to return");
334
149 } 335 }
150 return regionprofile; 336 return regionprofile;
151 } 337 }
152 338
153 /// <summary> 339 /// <summary>
340 /// Reads a user profile from an active data reader
341 /// </summary>
342 /// <param name="reader">An active database reader</param>
343 /// <returns>A user profile</returns>
344 public UserProfileData readUserRow(IDataReader reader)
345 {
346 UserProfileData retval = new UserProfileData();
347
348 if (reader.Read())
349 {
350 retval.UUID = new LLUUID((string)reader["UUID"]);
351 retval.username = (string)reader["username"];
352 retval.surname = (string)reader["lastname"];
353
354 retval.passwordHash = (string)reader["passwordHash"];
355 retval.passwordSalt = (string)reader["passwordSalt"];
356
357 retval.homeRegion = Convert.ToUInt64(reader["homeRegion"].ToString());
358 retval.homeLocation = new LLVector3(
359 Convert.ToSingle(reader["homeLocationX"].ToString()),
360 Convert.ToSingle(reader["homeLocationY"].ToString()),
361 Convert.ToSingle(reader["homeLocationZ"].ToString()));
362 retval.homeLookAt = new LLVector3(
363 Convert.ToSingle(reader["homeLookAtX"].ToString()),
364 Convert.ToSingle(reader["homeLookAtY"].ToString()),
365 Convert.ToSingle(reader["homeLookAtZ"].ToString()));
366
367 retval.created = Convert.ToInt32(reader["created"].ToString());
368 retval.lastLogin = Convert.ToInt32(reader["lastLogin"].ToString());
369
370 retval.userInventoryURI = (string)reader["userInventoryURI"];
371 retval.userAssetURI = (string)reader["userAssetURI"];
372
373 retval.profileCanDoMask = Convert.ToUInt32(reader["profileCanDoMask"].ToString());
374 retval.profileWantDoMask = Convert.ToUInt32(reader["profileWantDoMask"].ToString());
375
376 retval.profileAboutText = (string)reader["profileAboutText"];
377 retval.profileFirstText = (string)reader["profileFirstText"];
378
379 retval.profileImage = new LLUUID((string)reader["profileImage"]);
380 retval.profileFirstImage = new LLUUID((string)reader["profileFirstImage"]);
381
382 }
383 else
384 {
385 return null;
386 }
387 return retval;
388 }
389
390 /// <summary>
391 /// Reads an agent row from a database reader
392 /// </summary>
393 /// <param name="reader">An active database reader</param>
394 /// <returns>A user session agent</returns>
395 public UserAgentData readAgentRow(IDataReader reader)
396 {
397 UserAgentData retval = new UserAgentData();
398
399 if (reader.Read())
400 {
401 // Agent IDs
402 retval.UUID = new LLUUID((string)reader["UUID"]);
403 retval.sessionID = new LLUUID((string)reader["sessionID"]);
404 retval.secureSessionID = new LLUUID((string)reader["secureSessionID"]);
405
406 // Agent Who?
407 retval.agentIP = (string)reader["agentIP"];
408 retval.agentPort = Convert.ToUInt32(reader["agentPort"].ToString());
409 retval.agentOnline = Convert.ToBoolean(reader["agentOnline"].ToString());
410
411 // Login/Logout times (UNIX Epoch)
412 retval.loginTime = Convert.ToInt32(reader["loginTime"].ToString());
413 retval.logoutTime = Convert.ToInt32(reader["logoutTime"].ToString());
414
415 // Current position
416 retval.currentRegion = (string)reader["currentRegion"];
417 retval.currentHandle = Convert.ToUInt64(reader["currentHandle"].ToString());
418 LLVector3.TryParse((string)reader["currentPos"], out retval.currentPos);
419 }
420 else
421 {
422 return null;
423 }
424 return retval;
425 }
426
427 public AssetBase getAssetRow(IDataReader reader)
428 {
429 AssetBase asset = new AssetBase();
430 if (reader.Read())
431 {
432 // Region Main
433
434 asset = new AssetBase();
435 asset.Data = (byte[])reader["data"];
436 asset.Description = (string)reader["description"];
437 asset.FullID = new LLUUID((string)reader["id"]);
438 asset.InvType = Convert.ToSByte(reader["invType"]);
439 asset.Local = Convert.ToBoolean(reader["local"]); // ((sbyte)reader["local"]) != 0 ? true : false;
440 asset.Name = (string)reader["name"];
441 asset.Type = Convert.ToSByte(reader["assetType"]);
442
443 }
444 else
445 {
446 return null; // throw new Exception("No rows to return");
447 }
448 return asset;
449 }
450
451 /// <summary>
154 /// Creates a new region in the database 452 /// Creates a new region in the database
155 /// </summary> 453 /// </summary>
156 /// <param name="profile">The region profile to insert</param> 454 /// <param name="profile">The region profile to insert</param>
157 /// <returns>Successful?</returns> 455 /// <returns>Successful?</returns>
158 public bool insertRow(RegionProfileData profile) 456 public bool insertRegionRow(RegionProfileData profile)
159 { 457 {
160 string sql = 458
161 "REPLACE INTO regions VALUES (regionHandle, regionName, uuid, regionRecvKey, regionSecret, regionSendKey, regionDataURI, "; 459 //Insert new region
162 sql += 460 string sql = "INSERT INTO regions ([regionHandle], [regionName], [uuid], [regionRecvKey], [regionSecret], [regionSendKey], [regionDataURI], ";
163 "serverIP, serverPort, serverURI, locX, locY, locZ, eastOverrideHandle, westOverrideHandle, southOverrideHandle, northOverrideHandle, regionAssetURI, regionAssetRecvKey, "; 461 sql += "[serverIP], [serverPort], [serverURI], [locX], [locY], [locZ], [eastOverrideHandle], [westOverrideHandle], [southOverrideHandle], [northOverrideHandle], [regionAssetURI], [regionAssetRecvKey], ";
164 sql += "regionAssetSendKey, regionUserURI, regionUserRecvKey, regionUserSendKey) VALUES "; 462 sql += "[regionAssetSendKey], [regionUserURI], [regionUserRecvKey], [regionUserSendKey], [regionMapTexture], [serverHttpPort], [serverRemotingPort]) VALUES ";
165 463
166 sql += "(@regionHandle, @regionName, @uuid, @regionRecvKey, @regionSecret, @regionSendKey, @regionDataURI, "; 464 sql += "(@regionHandle, @regionName, @uuid, @regionRecvKey, @regionSecret, @regionSendKey, @regionDataURI, ";
167 sql += 465 sql += "@serverIP, @serverPort, @serverURI, @locX, @locY, @locZ, @eastOverrideHandle, @westOverrideHandle, @southOverrideHandle, @northOverrideHandle, @regionAssetURI, @regionAssetRecvKey, ";
168 "@serverIP, @serverPort, @serverURI, @locX, @locY, @locZ, @eastOverrideHandle, @westOverrideHandle, @southOverrideHandle, @northOverrideHandle, @regionAssetURI, @regionAssetRecvKey, "; 466 sql += "@regionAssetSendKey, @regionUserURI, @regionUserRecvKey, @regionUserSendKey, @regionMapTexture, @serverHttpPort, @serverRemotingPort);";
169 sql += "@regionAssetSendKey, @regionUserURI, @regionUserRecvKey, @regionUserSendKey);";
170 467
171 Dictionary<string, string> parameters = new Dictionary<string, string>(); 468 Dictionary<string, string> parameters = new Dictionary<string, string>();
172 469
@@ -174,6 +471,7 @@ namespace OpenSim.Framework.Data.MSSQL
174 parameters["regionName"] = profile.regionName; 471 parameters["regionName"] = profile.regionName;
175 parameters["uuid"] = profile.UUID.ToString(); 472 parameters["uuid"] = profile.UUID.ToString();
176 parameters["regionRecvKey"] = profile.regionRecvKey; 473 parameters["regionRecvKey"] = profile.regionRecvKey;
474 parameters["regionSecret"] = profile.regionSecret;
177 parameters["regionSendKey"] = profile.regionSendKey; 475 parameters["regionSendKey"] = profile.regionSendKey;
178 parameters["regionDataURI"] = profile.regionDataURI; 476 parameters["regionDataURI"] = profile.regionDataURI;
179 parameters["serverIP"] = profile.serverIP; 477 parameters["serverIP"] = profile.serverIP;
@@ -192,6 +490,56 @@ namespace OpenSim.Framework.Data.MSSQL
192 parameters["regionUserURI"] = profile.regionUserURI; 490 parameters["regionUserURI"] = profile.regionUserURI;
193 parameters["regionUserRecvKey"] = profile.regionUserRecvKey; 491 parameters["regionUserRecvKey"] = profile.regionUserRecvKey;
194 parameters["regionUserSendKey"] = profile.regionUserSendKey; 492 parameters["regionUserSendKey"] = profile.regionUserSendKey;
493 parameters["regionMapTexture"] = profile.regionMapTextureID.ToStringHyphenated();
494 parameters["serverHttpPort"] = profile.httpPort.ToString();
495 parameters["serverRemotingPort"] = profile.remotingPort.ToString();
496
497
498 bool returnval = false;
499
500 try
501 {
502 IDbCommand result = Query(sql, parameters);
503
504 if (result.ExecuteNonQuery() == 1)
505 returnval = true;
506
507 result.Dispose();
508 }
509 catch (Exception e)
510 {
511 MainLog.Instance.Error("MSSQLManager : " + e.ToString());
512
513 }
514
515 return returnval;
516
517 }
518
519
520
521 /// <summary>
522 /// Inserts a new row into the log database
523 /// </summary>
524 /// <param name="serverDaemon">The daemon which triggered this event</param>
525 /// <param name="target">Who were we operating on when this occured (region UUID, user UUID, etc)</param>
526 /// <param name="methodCall">The method call where the problem occured</param>
527 /// <param name="arguments">The arguments passed to the method</param>
528 /// <param name="priority">How critical is this?</param>
529 /// <param name="logMessage">Extra message info</param>
530 /// <returns>Saved successfully?</returns>
531 public bool insertLogRow(string serverDaemon, string target, string methodCall, string arguments, int priority, string logMessage)
532 {
533 string sql = "INSERT INTO logs ([target], [server], [method], [arguments], [priority], [message]) VALUES ";
534 sql += "(@target, @server, @method, @arguments, @priority, @message);";
535
536 Dictionary<string, string> parameters = new Dictionary<string, string>();
537 parameters["server"] = serverDaemon;
538 parameters["target"] = target;
539 parameters["method"] = methodCall;
540 parameters["arguments"] = arguments;
541 parameters["priority"] = priority.ToString();
542 parameters["message"] = logMessage;
195 543
196 bool returnval = false; 544 bool returnval = false;
197 545
@@ -204,12 +552,190 @@ namespace OpenSim.Framework.Data.MSSQL
204 552
205 result.Dispose(); 553 result.Dispose();
206 } 554 }
207 catch (Exception) 555 catch (Exception e)
208 { 556 {
557 MainLog.Instance.Error(e.ToString());
209 return false; 558 return false;
210 } 559 }
211 560
212 return returnval; 561 return returnval;
213 } 562 }
563
564
565 /// <summary>
566 /// Creates a new user and inserts it into the database
567 /// </summary>
568 /// <param name="uuid">User ID</param>
569 /// <param name="username">First part of the login</param>
570 /// <param name="lastname">Second part of the login</param>
571 /// <param name="passwordHash">A salted hash of the users password</param>
572 /// <param name="passwordSalt">The salt used for the password hash</param>
573 /// <param name="homeRegion">A regionHandle of the users home region</param>
574 /// <param name="homeLocX">Home region position vector</param>
575 /// <param name="homeLocY">Home region position vector</param>
576 /// <param name="homeLocZ">Home region position vector</param>
577 /// <param name="homeLookAtX">Home region 'look at' vector</param>
578 /// <param name="homeLookAtY">Home region 'look at' vector</param>
579 /// <param name="homeLookAtZ">Home region 'look at' vector</param>
580 /// <param name="created">Account created (unix timestamp)</param>
581 /// <param name="lastlogin">Last login (unix timestamp)</param>
582 /// <param name="inventoryURI">Users inventory URI</param>
583 /// <param name="assetURI">Users asset URI</param>
584 /// <param name="canDoMask">I can do mask</param>
585 /// <param name="wantDoMask">I want to do mask</param>
586 /// <param name="aboutText">Profile text</param>
587 /// <param name="firstText">Firstlife text</param>
588 /// <param name="profileImage">UUID for profile image</param>
589 /// <param name="firstImage">UUID for firstlife image</param>
590 /// <returns>Success?</returns>
591 public bool insertUserRow(libsecondlife.LLUUID uuid, string username, string lastname, string passwordHash, string passwordSalt, UInt64 homeRegion, float homeLocX, float homeLocY, float homeLocZ,
592 float homeLookAtX, float homeLookAtY, float homeLookAtZ, int created, int lastlogin, string inventoryURI, string assetURI, uint canDoMask, uint wantDoMask, string aboutText, string firstText,
593 libsecondlife.LLUUID profileImage, libsecondlife.LLUUID firstImage)
594 {
595 string sql = "INSERT INTO users ";
596 sql += "([UUID], [username], [lastname], [passwordHash], [passwordSalt], [homeRegion], ";
597 sql += "[homeLocationX], [homeLocationY], [homeLocationZ], [homeLookAtX], [homeLookAtY], [homeLookAtZ], [created], ";
598 sql += "[lastLogin], [userInventoryURI], [userAssetURI], [profileCanDoMask], [profileWantDoMask], [profileAboutText], ";
599 sql += "[profileFirstText], [profileImage], [profileFirstImage]) VALUES ";
600
601 sql += "(@UUID, @username, @lastname, @passwordHash, @passwordSalt, @homeRegion, ";
602 sql += "@homeLocationX, @homeLocationY, @homeLocationZ, @homeLookAtX, @homeLookAtY, @homeLookAtZ, @created, ";
603 sql += "@lastLogin, @userInventoryURI, @userAssetURI, @profileCanDoMask, @profileWantDoMask, @profileAboutText, ";
604 sql += "@profileFirstText, @profileImage, @profileFirstImage);";
605
606 Dictionary<string, string> parameters = new Dictionary<string, string>();
607 parameters["UUID"] = uuid.ToStringHyphenated();
608 parameters["username"] = username.ToString();
609 parameters["lastname"] = lastname.ToString();
610 parameters["passwordHash"] = passwordHash.ToString();
611 parameters["passwordSalt"] = passwordSalt.ToString();
612 parameters["homeRegion"] = homeRegion.ToString();
613 parameters["homeLocationX"] = homeLocX.ToString();
614 parameters["homeLocationY"] = homeLocY.ToString();
615 parameters["homeLocationZ"] = homeLocZ.ToString();
616 parameters["homeLookAtX"] = homeLookAtX.ToString();
617 parameters["homeLookAtY"] = homeLookAtY.ToString();
618 parameters["homeLookAtZ"] = homeLookAtZ.ToString();
619 parameters["created"] = created.ToString();
620 parameters["lastLogin"] = lastlogin.ToString();
621 parameters["userInventoryURI"] = "";
622 parameters["userAssetURI"] = "";
623 parameters["profileCanDoMask"] = "0";
624 parameters["profileWantDoMask"] = "0";
625 parameters["profileAboutText"] = "";
626 parameters["profileFirstText"] = "";
627 parameters["profileImage"] = libsecondlife.LLUUID.Zero.ToStringHyphenated();
628 parameters["profileFirstImage"] = libsecondlife.LLUUID.Zero.ToStringHyphenated();
629
630 bool returnval = false;
631
632 try
633 {
634 IDbCommand result = Query(sql, parameters);
635
636 if (result.ExecuteNonQuery() == 1)
637 returnval = true;
638
639 result.Dispose();
640 }
641 catch (Exception e)
642 {
643 MainLog.Instance.Error(e.ToString());
644 return false;
645 }
646
647 return returnval;
648 }
649
650 /// <summary>
651 /// Execute a SQL statement stored in a resource, as a string
652 /// </summary>
653 /// <param name="name"></param>
654 public void ExecuteResourceSql(string name)
655 {
656 try
657 {
658
659 SqlCommand cmd = new SqlCommand(getResourceString(name), (SqlConnection)dbcon);
660 cmd.ExecuteNonQuery();
661 cmd.Dispose();
662 }
663 catch (Exception e)
664 {
665 MainLog.Instance.Error("Unable to execute query " + e.ToString());
666 }
667 }
668
669 public SqlConnection getConnection()
670 {
671 return (SqlConnection)dbcon;
672 }
673
674 /// <summary>
675 /// Given a list of tables, return the version of the tables, as seen in the database
676 /// </summary>
677 /// <param name="tableList"></param>
678 public void GetTableVersion(Dictionary<string, string> tableList)
679 {
680 lock (dbcon)
681 {
682 Dictionary<string, string> param = new Dictionary<string, string>();
683 param["dbname"] = dbcon.Database;
684 IDbCommand tablesCmd = this.Query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG=@dbname", param);
685 using (IDataReader tables = tablesCmd.ExecuteReader())
686 {
687 while (tables.Read())
688 {
689 try
690 {
691 string tableName = (string)tables["TABLE_NAME"];
692 if (tableList.ContainsKey(tableName))
693 tableList[tableName] = tableName;
694 }
695 catch (Exception e)
696 {
697 MainLog.Instance.Error(e.ToString());
698 }
699 }
700 tables.Close();
701 }
702 }
703 }
704
705 private string getResourceString(string name)
706 {
707 Assembly assem = this.GetType().Assembly;
708 string[] names = assem.GetManifestResourceNames();
709
710 foreach (string s in names)
711 if (s.EndsWith(name))
712 using (Stream resource = assem.GetManifestResourceStream(s))
713 {
714 using (StreamReader resourceReader = new StreamReader(resource))
715 {
716 string resourceString = resourceReader.ReadToEnd();
717 return resourceString;
718 }
719 }
720 throw new Exception(string.Format("Resource '{0}' was not found", name));
721 }
722
723 /// <summary>
724 /// Returns the version of this DB provider
725 /// </summary>
726 /// <returns>A string containing the DB provider</returns>
727 public string getVersion()
728 {
729 System.Reflection.Module module = this.GetType().Module;
730 string dllName = module.Assembly.ManifestModule.Name;
731 Version dllVersion = module.Assembly.GetName().Version;
732
733
734 return string.Format("{0}.{1}.{2}.{3}", dllVersion.Major, dllVersion.Minor, dllVersion.Build, dllVersion.Revision);
735 }
736
214 } 737 }
215} \ No newline at end of file 738
739
740
741}
diff --git a/OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs b/OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs
new file mode 100644
index 0000000..2c4dfdc
--- /dev/null
+++ b/OpenSim/Framework/Data.MSSQL/MSSQLUserData.cs
@@ -0,0 +1,452 @@
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*/
28using System;
29using System.Collections.Generic;
30using System.Data;
31using System.Data.SqlClient;
32using libsecondlife;
33using OpenSim.Framework.Console;
34
35namespace OpenSim.Framework.Data.MSSQL
36{
37 /// <summary>
38 /// A database interface class to a user profile storage system
39 /// </summary>
40 class MSSQLUserData : IUserData
41 {
42 /// <summary>
43 /// Database manager for MySQL
44 /// </summary>
45 public MSSQLManager database;
46
47 /// <summary>
48 /// Loads and initialises the MySQL storage plugin
49 /// </summary>
50 public void Initialise()
51 {
52 // Load from an INI file connection details
53 // TODO: move this to XML?
54 IniFile GridDataMySqlFile = new IniFile("mssql_connection.ini");
55 string settingDataSource = GridDataMySqlFile.ParseFileReadValue("data_source");
56 string settingInitialCatalog = GridDataMySqlFile.ParseFileReadValue("initial_catalog");
57 string settingPersistSecurityInfo = GridDataMySqlFile.ParseFileReadValue("persist_security_info");
58 string settingUserId = GridDataMySqlFile.ParseFileReadValue("user_id");
59 string settingPassword = GridDataMySqlFile.ParseFileReadValue("password");
60
61 database = new MSSQLManager(settingDataSource, settingInitialCatalog, settingPersistSecurityInfo, settingUserId, settingPassword);
62 }
63
64 /// <summary>
65 /// Searches the database for a specified user profile
66 /// </summary>
67 /// <param name="name">The account name of the user</param>
68 /// <returns>A user profile</returns>
69 public UserProfileData GetUserByName(string name)
70 {
71 return GetUserByName(name.Split(' ')[0], name.Split(' ')[1]);
72 }
73
74 /// <summary>
75 /// Searches the database for a specified user profile by name components
76 /// </summary>
77 /// <param name="user">The first part of the account name</param>
78 /// <param name="last">The second part of the account name</param>
79 /// <returns>A user profile</returns>
80 public UserProfileData GetUserByName(string user, string last)
81 {
82 try
83 {
84 lock (database)
85 {
86 Dictionary<string, string> param = new Dictionary<string, string>();
87 param["first"] = user;
88 param["second"] = last;
89
90 IDbCommand result = database.Query("SELECT * FROM users WHERE username = @first AND lastname = @second", param);
91 IDataReader reader = result.ExecuteReader();
92
93 UserProfileData row = database.readUserRow(reader);
94
95 reader.Close();
96 result.Dispose();
97
98 return row;
99 }
100 }
101 catch (Exception e)
102 {
103 database.Reconnect();
104 MainLog.Instance.Error(e.ToString());
105 return null;
106 }
107 }
108
109 public List<OpenSim.Framework.AvatarPickerAvatar> GeneratePickerResults(LLUUID queryID, string query)
110 {
111 List<OpenSim.Framework.AvatarPickerAvatar> returnlist = new List<OpenSim.Framework.AvatarPickerAvatar>();
112 string[] querysplit;
113 querysplit = query.Split(' ');
114 if (querysplit.Length == 2)
115 {
116 try
117 {
118 lock (database)
119 {
120 Dictionary<string, string> param = new Dictionary<string, string>();
121 param["first"] = querysplit[0];
122 param["second"] = querysplit[1];
123
124 IDbCommand result =
125 database.Query("SELECT UUID,username,surname FROM users WHERE username = @first AND lastname = @second", param);
126 IDataReader reader = result.ExecuteReader();
127
128
129 while (reader.Read())
130 {
131 OpenSim.Framework.AvatarPickerAvatar user = new OpenSim.Framework.AvatarPickerAvatar();
132 user.AvatarID = new LLUUID((string)reader["UUID"]);
133 user.firstName = (string)reader["username"];
134 user.lastName = (string)reader["surname"];
135 returnlist.Add(user);
136
137 }
138 reader.Close();
139 result.Dispose();
140 }
141 }
142 catch (Exception e)
143 {
144 database.Reconnect();
145 MainLog.Instance.Error(e.ToString());
146 return returnlist;
147 }
148
149
150
151 }
152 else if (querysplit.Length == 1)
153 {
154
155 try
156 {
157 lock (database)
158 {
159 Dictionary<string, string> param = new Dictionary<string, string>();
160 param["first"] = querysplit[0];
161 param["second"] = querysplit[1];
162
163 IDbCommand result =
164 database.Query("SELECT UUID,username,surname FROM users WHERE username = @first OR lastname = @second", param);
165 IDataReader reader = result.ExecuteReader();
166
167
168 while (reader.Read())
169 {
170 OpenSim.Framework.AvatarPickerAvatar user = new OpenSim.Framework.AvatarPickerAvatar();
171 user.AvatarID = new LLUUID((string)reader["UUID"]);
172 user.firstName = (string)reader["username"];
173 user.lastName = (string)reader["surname"];
174 returnlist.Add(user);
175
176 }
177 reader.Close();
178 result.Dispose();
179 }
180 }
181 catch (Exception e)
182 {
183 database.Reconnect();
184 MainLog.Instance.Error(e.ToString());
185 return returnlist;
186 }
187 }
188 return returnlist;
189 }
190
191 /// <summary>
192 /// Searches the database for a specified user profile by UUID
193 /// </summary>
194 /// <param name="uuid">The account ID</param>
195 /// <returns>The users profile</returns>
196 public UserProfileData GetUserByUUID(LLUUID uuid)
197 {
198 try
199 {
200 lock (database)
201 {
202 Dictionary<string, string> param = new Dictionary<string, string>();
203 param["uuid"] = uuid.ToStringHyphenated();
204
205 IDbCommand result = database.Query("SELECT * FROM users WHERE UUID = @uuid", param);
206 IDataReader reader = result.ExecuteReader();
207
208 UserProfileData row = database.readUserRow(reader);
209
210 reader.Close();
211 result.Dispose();
212
213 return row;
214 }
215 }
216 catch (Exception e)
217 {
218 database.Reconnect();
219 MainLog.Instance.Error(e.ToString());
220 return null;
221 }
222 }
223
224 /// <summary>
225 /// Returns a user session searching by name
226 /// </summary>
227 /// <param name="name">The account name</param>
228 /// <returns>The users session</returns>
229 public UserAgentData GetAgentByName(string name)
230 {
231 return GetAgentByName(name.Split(' ')[0], name.Split(' ')[1]);
232 }
233
234 /// <summary>
235 /// Returns a user session by account name
236 /// </summary>
237 /// <param name="user">First part of the users account name</param>
238 /// <param name="last">Second part of the users account name</param>
239 /// <returns>The users session</returns>
240 public UserAgentData GetAgentByName(string user, string last)
241 {
242 UserProfileData profile = GetUserByName(user, last);
243 return GetAgentByUUID(profile.UUID);
244 }
245
246 /// <summary>
247 /// Returns an agent session by account UUID
248 /// </summary>
249 /// <param name="uuid">The accounts UUID</param>
250 /// <returns>The users session</returns>
251 public UserAgentData GetAgentByUUID(LLUUID uuid)
252 {
253 try
254 {
255 lock (database)
256 {
257 Dictionary<string, string> param = new Dictionary<string, string>();
258 param["uuid"] = uuid.ToStringHyphenated();
259
260 IDbCommand result = database.Query("SELECT * FROM agents WHERE UUID = @uuid", param);
261 IDataReader reader = result.ExecuteReader();
262
263 UserAgentData row = database.readAgentRow(reader);
264
265 reader.Close();
266 result.Dispose();
267
268 return row;
269 }
270 }
271 catch (Exception e)
272 {
273 database.Reconnect();
274 MainLog.Instance.Error(e.ToString());
275 return null;
276 }
277 }
278
279 /// <summary>
280 /// Creates a new users profile
281 /// </summary>
282 /// <param name="user">The user profile to create</param>
283 public void AddNewUserProfile(UserProfileData user)
284 {
285 try
286 {
287 lock (database)
288 {
289 database.insertUserRow(user.UUID, user.username, user.surname, user.passwordHash, user.passwordSalt, user.homeRegion, user.homeLocation.X, user.homeLocation.Y, user.homeLocation.Z,
290 user.homeLookAt.X, user.homeLookAt.Y, user.homeLookAt.Z, user.created, user.lastLogin, user.userInventoryURI, user.userAssetURI, user.profileCanDoMask, user.profileWantDoMask,
291 user.profileAboutText, user.profileFirstText, user.profileImage, user.profileFirstImage);
292 }
293 }
294 catch (Exception e)
295 {
296 database.Reconnect();
297 MainLog.Instance.Error(e.ToString());
298 }
299
300 }
301
302 /// <summary>
303 /// Creates a new agent
304 /// </summary>
305 /// <param name="agent">The agent to create</param>
306 public void AddNewUserAgent(UserAgentData agent)
307 {
308 // Do nothing.
309 }
310
311
312 public bool UpdateUserProfile(UserProfileData user)
313 {
314
315 SqlCommand command = new SqlCommand("UPDATE users set UUID = @uuid, " +
316 "username = @username, " +
317 "lastname = @lastname," +
318 "passwordHash = @passwordHash," +
319 "passwordSalt = @passwordSalt," +
320 "homeRegion = @homeRegion," +
321 "homeLocationX = @homeLocationX," +
322 "homeLocationY = @homeLocationY," +
323 "homeLocationZ = @homeLocationZ," +
324 "homeLookAtX = @homeLookAtX," +
325 "homeLookAtY = @homeLookAtY," +
326 "homeLookAtZ = @homeLookAtZ," +
327 "created = @created," +
328 "lastLogin = @lastLogin," +
329 "userInventoryURI = @userInventoryURI," +
330 "userAssetURI = @userAssetURI," +
331 "profileCanDoMask = @profileCanDoMask," +
332 "profileWantDoMask = @profileWantDoMask," +
333 "profileAboutText = @profileAboutText," +
334 "profileFirstText = @profileFirstText," +
335 "profileImage = @profileImage," +
336 "profileFirstImage = @profileFirstImage where " +
337 "UUID = @keyUUUID;", database.getConnection());
338 SqlParameter param1 = new SqlParameter("@uuid", user.UUID.ToStringHyphenated());
339 SqlParameter param2 = new SqlParameter("@username", user.username);
340 SqlParameter param3 = new SqlParameter("@lastname", user.surname);
341 SqlParameter param4 = new SqlParameter("@passwordHash", user.passwordHash);
342 SqlParameter param5 = new SqlParameter("@passwordSalt", user.passwordSalt);
343 SqlParameter param6 = new SqlParameter("@homeRegion", Convert.ToInt64(user.homeRegion));
344 SqlParameter param7 = new SqlParameter("@homeLocationX", user.homeLocation.X);
345 SqlParameter param8 = new SqlParameter("@homeLocationY", user.homeLocation.Y);
346 SqlParameter param9 = new SqlParameter("@homeLocationZ", user.homeLocation.Y);
347 SqlParameter param10 = new SqlParameter("@homeLookAtX", user.homeLookAt.X);
348 SqlParameter param11 = new SqlParameter("@homeLookAtY", user.homeLookAt.Y);
349 SqlParameter param12 = new SqlParameter("@homeLookAtZ", user.homeLookAt.Z);
350 SqlParameter param13 = new SqlParameter("@created", Convert.ToInt32(user.created));
351 SqlParameter param14 = new SqlParameter("@lastLogin", Convert.ToInt32(user.lastLogin));
352 SqlParameter param15 = new SqlParameter("@userInventoryURI", user.userInventoryURI);
353 SqlParameter param16 = new SqlParameter("@userAssetURI", user.userAssetURI);
354 SqlParameter param17 = new SqlParameter("@profileCanDoMask", Convert.ToInt32(user.profileCanDoMask));
355 SqlParameter param18 = new SqlParameter("@profileWantDoMask", Convert.ToInt32(user.profileWantDoMask));
356 SqlParameter param19 = new SqlParameter("@profileAboutText", user.profileAboutText);
357 SqlParameter param20 = new SqlParameter("@profileFirstText", user.profileFirstText);
358 SqlParameter param21 = new SqlParameter("@profileImage", libsecondlife.LLUUID.Zero.ToStringHyphenated());
359 SqlParameter param22 = new SqlParameter("@profileFirstImage", libsecondlife.LLUUID.Zero.ToStringHyphenated());
360 SqlParameter param23 = new SqlParameter("@keyUUUID", user.UUID.ToStringHyphenated());
361 command.Parameters.Add(param1);
362 command.Parameters.Add(param2);
363 command.Parameters.Add(param3);
364 command.Parameters.Add(param4);
365 command.Parameters.Add(param5);
366 command.Parameters.Add(param6);
367 command.Parameters.Add(param7);
368 command.Parameters.Add(param8);
369 command.Parameters.Add(param9);
370 command.Parameters.Add(param10);
371 command.Parameters.Add(param11);
372 command.Parameters.Add(param12);
373 command.Parameters.Add(param13);
374 command.Parameters.Add(param14);
375 command.Parameters.Add(param15);
376 command.Parameters.Add(param16);
377 command.Parameters.Add(param17);
378 command.Parameters.Add(param18);
379 command.Parameters.Add(param19);
380 command.Parameters.Add(param20);
381 command.Parameters.Add(param21);
382 command.Parameters.Add(param22);
383 command.Parameters.Add(param23);
384 try
385 {
386 int affected = command.ExecuteNonQuery();
387 if (affected != 0) {
388 return true;
389 } else {
390 return false;
391 }
392 }
393 catch (Exception e)
394 {
395 MainLog.Instance.Error(e.ToString());
396 }
397 return false;
398 }
399
400 /// <summary>
401 /// Performs a money transfer request between two accounts
402 /// </summary>
403 /// <param name="from">The senders account ID</param>
404 /// <param name="to">The recievers account ID</param>
405 /// <param name="amount">The amount to transfer</param>
406 /// <returns>Success?</returns>
407 public bool MoneyTransferRequest(LLUUID from, LLUUID to, uint amount)
408 {
409 return false;
410 }
411
412 /// <summary>
413 /// Performs an inventory transfer request between two accounts
414 /// </summary>
415 /// <remarks>TODO: Move to inventory server</remarks>
416 /// <param name="from">The senders account ID</param>
417 /// <param name="to">The recievers account ID</param>
418 /// <param name="item">The item to transfer</param>
419 /// <returns>Success?</returns>
420 public bool InventoryTransferRequest(LLUUID from, LLUUID to, LLUUID item)
421 {
422 return false;
423 }
424
425 /// <summary>
426 /// Database provider name
427 /// </summary>
428 /// <returns>Provider name</returns>
429 public string getName()
430 {
431 return "MSSQL Userdata Interface";
432 }
433
434 /// <summary>
435 /// Database provider version
436 /// </summary>
437 /// <returns>provider version</returns>
438 public string GetVersion()
439 {
440 return database.getVersion();
441 }
442
443 /// <summary>
444 /// Not implemented
445 /// </summary>
446 /// <param name="query"></param>
447 public void runQuery(string query)
448 {
449 }
450
451 }
452} \ No newline at end of file
diff --git a/OpenSim/Framework/Data.MSSQL/Resources/CreateAssetsTable.sql b/OpenSim/Framework/Data.MSSQL/Resources/CreateAssetsTable.sql
new file mode 100644
index 0000000..c7cb21a
--- /dev/null
+++ b/OpenSim/Framework/Data.MSSQL/Resources/CreateAssetsTable.sql
@@ -0,0 +1,19 @@
1SET ANSI_NULLS ON
2SET QUOTED_IDENTIFIER ON
3SET ANSI_PADDING ON
4CREATE TABLE [assets] (
5 [id] [varchar](36) NOT NULL,
6 [name] [varchar](64) NOT NULL,
7 [description] [varchar](64) NOT NULL,
8 [assetType] [tinyint] NOT NULL,
9 [invType] [tinyint] NOT NULL,
10 [local] [tinyint] NOT NULL,
11 [temporary] [tinyint] NOT NULL,
12 [data] [image] NOT NULL,
13PRIMARY KEY CLUSTERED
14(
15 [id] ASC
16)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
17) ON [PRIMARY]
18
19SET ANSI_PADDING OFF
diff --git a/OpenSim/Framework/Data.MSSQL/Resources/CreateFoldersTable.sql b/OpenSim/Framework/Data.MSSQL/Resources/CreateFoldersTable.sql
new file mode 100644
index 0000000..95d183a
--- /dev/null
+++ b/OpenSim/Framework/Data.MSSQL/Resources/CreateFoldersTable.sql
@@ -0,0 +1,27 @@
1SET ANSI_NULLS ON
2SET QUOTED_IDENTIFIER ON
3SET ANSI_PADDING ON
4CREATE TABLE [inventoryfolders] (
5 [folderID] [varchar](36) NOT NULL default '',
6 [agentID] [varchar](36) default NULL,
7 [parentFolderID] [varchar](36) default NULL,
8 [folderName] [varchar](64) default NULL,
9 [type] [smallint] NOT NULL default 0,
10 [version] [int] NOT NULL default 0,
11 PRIMARY KEY CLUSTERED
12(
13 [folderID] ASC
14)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
15) ON [PRIMARY]
16
17CREATE NONCLUSTERED INDEX [owner] ON [inventoryfolders]
18(
19 [agentID] ASC
20)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
21
22CREATE NONCLUSTERED INDEX [parent] ON [inventoryfolders]
23(
24 [parentFolderID] ASC
25)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
26
27SET ANSI_PADDING OFF
diff --git a/OpenSim/Framework/Data.MSSQL/Resources/CreateItemsTable.sql b/OpenSim/Framework/Data.MSSQL/Resources/CreateItemsTable.sql
new file mode 100644
index 0000000..40c047e
--- /dev/null
+++ b/OpenSim/Framework/Data.MSSQL/Resources/CreateItemsTable.sql
@@ -0,0 +1,39 @@
1SET ANSI_NULLS ON
2
3SET QUOTED_IDENTIFIER ON
4
5SET ANSI_PADDING ON
6
7CREATE TABLE [inventoryitems] (
8 [inventoryID] [varchar](36) NOT NULL default '',
9 [assetID] [varchar](36) default NULL,
10 [assetType] [int] default NULL,
11 [parentFolderID] [varchar](36) default NULL,
12 [avatarID] [varchar](36) default NULL,
13 [inventoryName] [varchar](64) default NULL,
14 [inventoryDescription] [varchar](64) default NULL,
15 [inventoryNextPermissions] [int] default NULL,
16 [inventoryCurrentPermissions] [int] default NULL,
17 [invType] [int] default NULL,
18 [creatorID] [varchar](36) default NULL,
19 [inventoryBasePermissions] [int] NOT NULL default 0,
20 [inventoryEveryOnePermissions] [int] NOT NULL default 0,
21 PRIMARY KEY CLUSTERED
22(
23 [inventoryID] ASC
24)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
25) ON [PRIMARY]
26
27
28CREATE NONCLUSTERED INDEX [owner] ON [inventoryitems]
29(
30 [avatarID] ASC
31)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
32
33CREATE NONCLUSTERED INDEX [folder] ON [inventoryitems]
34(
35 [parentFolderID] ASC
36)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
37
38SET ANSI_PADDING OFF
39
diff --git a/OpenSim/Framework/Data.MSSQL/Resources/Mssql-agents.sql b/OpenSim/Framework/Data.MSSQL/Resources/Mssql-agents.sql
new file mode 100644
index 0000000..ad53173
--- /dev/null
+++ b/OpenSim/Framework/Data.MSSQL/Resources/Mssql-agents.sql
@@ -0,0 +1,37 @@
1SET ANSI_NULLS ON
2
3SET QUOTED_IDENTIFIER ON
4
5SET ANSI_PADDING ON
6
7CREATE TABLE [agents] (
8 [UUID] [varchar](36) NOT NULL,
9 [sessionID] [varchar](36) NOT NULL,
10 [secureSessionID] [varchar](36) NOT NULL,
11 [agentIP] [varchar](16) NOT NULL,
12 [agentPort] [int] NOT NULL,
13 [agentOnline] [tinyint] NOT NULL,
14 [loginTime] [int] NOT NULL,
15 [logoutTime] [int] NOT NULL,
16 [currentRegion] [varchar](36) NOT NULL,
17 [currentHandle] [bigint] NOT NULL,
18 [currentPos] [varchar](64) NOT NULL,
19 PRIMARY KEY CLUSTERED
20(
21 [UUID] ASC
22)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
23) ON [PRIMARY]
24
25
26CREATE NONCLUSTERED INDEX [session] ON [agents]
27(
28 [sessionID] ASC
29)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
30
31CREATE NONCLUSTERED INDEX [ssession] ON [agents]
32(
33 [secureSessionID] ASC
34)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
35
36SET ANSI_PADDING OFF
37
diff --git a/OpenSim/Framework/Data.MSSQL/Resources/Mssql-logs.sql b/OpenSim/Framework/Data.MSSQL/Resources/Mssql-logs.sql
new file mode 100644
index 0000000..3b747d8
--- /dev/null
+++ b/OpenSim/Framework/Data.MSSQL/Resources/Mssql-logs.sql
@@ -0,0 +1,20 @@
1SET ANSI_NULLS ON
2
3SET QUOTED_IDENTIFIER ON
4
5SET ANSI_PADDING ON
6
7CREATE TABLE [logs] (
8 [logID] [int] NOT NULL,
9 [target] [varchar](36) default NULL,
10 [server] [varchar](64) default NULL,
11 [method] [varchar](64) default NULL,
12 [arguments] [varchar](255) default NULL,
13 [priority] [int] default NULL,
14 [message] [ntext],
15 PRIMARY KEY CLUSTERED
16(
17 [logID] ASC
18)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
19) ON [PRIMARY]
20
diff --git a/OpenSim/Framework/Data.MSSQL/Resources/Mssql-users.sql b/OpenSim/Framework/Data.MSSQL/Resources/Mssql-users.sql
new file mode 100644
index 0000000..3f5f896
--- /dev/null
+++ b/OpenSim/Framework/Data.MSSQL/Resources/Mssql-users.sql
@@ -0,0 +1,41 @@
1SET ANSI_NULLS ON
2
3SET QUOTED_IDENTIFIER ON
4
5SET ANSI_PADDING ON
6
7CREATE TABLE [users] (
8 [UUID] [varchar](36) NOT NULL default '',
9 [username] [varchar](32) NOT NULL,
10 [lastname] [varchar](32) NOT NULL,
11 [passwordHash] [varchar](32) NOT NULL,
12 [passwordSalt] [varchar](32) NOT NULL,
13 [homeRegion] [bigint] default NULL,
14 [homeLocationX] [float] default NULL,
15 [homeLocationY] [float] default NULL,
16 [homeLocationZ] [float] default NULL,
17 [homeLookAtX] [float] default NULL,
18 [homeLookAtY] [float] default NULL,
19 [homeLookAtZ] [float] default NULL,
20 [created] [int] NOT NULL,
21 [lastLogin] [int] NOT NULL,
22 [userInventoryURI] [varchar](255) default NULL,
23 [userAssetURI] [varchar](255) default NULL,
24 [profileCanDoMask] [int] default NULL,
25 [profileWantDoMask] [int] default NULL,
26 [profileAboutText] [ntext],
27 [profileFirstText] [ntext],
28 [profileImage] [varchar](36) default NULL,
29 [profileFirstImage] [varchar](36) default NULL,
30 PRIMARY KEY CLUSTERED
31(
32 [UUID] ASC
33)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
34) ON [PRIMARY]
35
36
37CREATE NONCLUSTERED INDEX [usernames] ON [users]
38(
39 [username] ASC,
40 [lastname] ASC
41)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
diff --git a/OpenSim/Region/Application/OpenSimMain.cs b/OpenSim/Region/Application/OpenSimMain.cs
index 2e089b8..b941e2d 100644
--- a/OpenSim/Region/Application/OpenSimMain.cs
+++ b/OpenSim/Region/Application/OpenSimMain.cs
@@ -426,6 +426,13 @@ namespace OpenSim
426 { 426 {
427 assetServer = new GridAssetClient(m_networkServersInfo.AssetURL); 427 assetServer = new GridAssetClient(m_networkServersInfo.AssetURL);
428 } 428 }
429 else if (m_assetStorage == "mssql")
430 {
431 SQLAssetServer sqlAssetServer = new SQLAssetServer("OpenSim.Framework.Data.MSSQL.dll");
432 sqlAssetServer.LoadDefaultAssets();
433 assetServer = sqlAssetServer;
434 //assetServer = new GridAssetClient("");
435 }
429 else 436 else
430 { 437 {
431 SQLAssetServer sqlAssetServer = new SQLAssetServer(m_standaloneAssetPlugin); 438 SQLAssetServer sqlAssetServer = new SQLAssetServer(m_standaloneAssetPlugin);
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
diff --git a/bin/mssql_connection.ini b/bin/mssql_connection.ini
new file mode 100644
index 0000000..c63d008
--- /dev/null
+++ b/bin/mssql_connection.ini
@@ -0,0 +1,6 @@
1[mssqlconnection]
2data_source=\SQLEXPRESS
3initial_catalog=database
4persist_security_info=True
5user_id=username
6password=password
diff --git a/prebuild.xml b/prebuild.xml
index 4705fc7..6606751 100644
--- a/prebuild.xml
+++ b/prebuild.xml
@@ -672,6 +672,33 @@
672 </Files> 672 </Files>
673 </Project> 673 </Project>
674 674
675
676 <Project name="OpenSim.DataStore.MSSQL" path="OpenSim/Region/Storage/OpenSim.DataStore.MSSQL" type="Library">
677 <Configuration name="Debug">
678 <Options>
679 <OutputPath>../../../../bin/</OutputPath>
680 </Options>
681 </Configuration>
682 <Configuration name="Release">
683 <Options>
684 <OutputPath>../../../../bin/</OutputPath>
685 </Options>
686 </Configuration>
687
688 <ReferencePath>../../../../bin/</ReferencePath>
689 <Reference name="System" localCopy="false"/>
690 <Reference name="System.Xml"/>
691 <Reference name="System.Data"/>
692 <Reference name="libsecondlife.dll"/>
693 <Reference name="OpenSim.Framework"/>
694 <Reference name="OpenSim.Framework.Data"/>
695 <Reference name="OpenSim.Region.Environment"/>
696 <Reference name="OpenSim.Framework.Console"/>
697 <Files>
698 <Match pattern="*.cs" recurse="true"/>
699 </Files>
700 </Project>
701
675 <Project name="OpenSim.Region.ExtensionsScriptModule" path="OpenSim/Region/ExtensionsScriptModule" type="Library"> 702 <Project name="OpenSim.Region.ExtensionsScriptModule" path="OpenSim/Region/ExtensionsScriptModule" type="Library">
676 <Configuration name="Debug"> 703 <Configuration name="Debug">
677 <Options> 704 <Options>
@@ -883,10 +910,14 @@
883 <Reference name="System" localCopy="false"/> 910 <Reference name="System" localCopy="false"/>
884 <Reference name="System.Xml"/> 911 <Reference name="System.Xml"/>
885 <Reference name="System.Data"/> 912 <Reference name="System.Data"/>
913 <Reference name="OpenSim.Framework"/>
886 <Reference name="OpenSim.Framework.Data"/> 914 <Reference name="OpenSim.Framework.Data"/>
915 <Reference name="OpenSim.Framework.Console"/>
887 <Reference name="libsecondlife.dll"/> 916 <Reference name="libsecondlife.dll"/>
917
888 <Files> 918 <Files>
889 <Match pattern="*.cs" recurse="true"/> 919 <Match pattern="*.cs" recurse="true"/>
920 <Match path="Resources" pattern="*.sql" buildAction="EmbeddedResource"/>
890 </Files> 921 </Files>
891 </Project> 922 </Project>
892 923
@@ -1191,3 +1222,5 @@
1191 1222
1192 1223
1193 1224
1225
1226
diff --git a/share/sql/mssql-CreateAssetsTable.sql b/share/sql/mssql-CreateAssetsTable.sql
new file mode 100644
index 0000000..c7cb21a
--- /dev/null
+++ b/share/sql/mssql-CreateAssetsTable.sql
@@ -0,0 +1,19 @@
1SET ANSI_NULLS ON
2SET QUOTED_IDENTIFIER ON
3SET ANSI_PADDING ON
4CREATE TABLE [assets] (
5 [id] [varchar](36) NOT NULL,
6 [name] [varchar](64) NOT NULL,
7 [description] [varchar](64) NOT NULL,
8 [assetType] [tinyint] NOT NULL,
9 [invType] [tinyint] NOT NULL,
10 [local] [tinyint] NOT NULL,
11 [temporary] [tinyint] NOT NULL,
12 [data] [image] NOT NULL,
13PRIMARY KEY CLUSTERED
14(
15 [id] ASC
16)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
17) ON [PRIMARY]
18
19SET ANSI_PADDING OFF
diff --git a/share/sql/mssql-CreateFoldersTable.sql b/share/sql/mssql-CreateFoldersTable.sql
new file mode 100644
index 0000000..95d183a
--- /dev/null
+++ b/share/sql/mssql-CreateFoldersTable.sql
@@ -0,0 +1,27 @@
1SET ANSI_NULLS ON
2SET QUOTED_IDENTIFIER ON
3SET ANSI_PADDING ON
4CREATE TABLE [inventoryfolders] (
5 [folderID] [varchar](36) NOT NULL default '',
6 [agentID] [varchar](36) default NULL,
7 [parentFolderID] [varchar](36) default NULL,
8 [folderName] [varchar](64) default NULL,
9 [type] [smallint] NOT NULL default 0,
10 [version] [int] NOT NULL default 0,
11 PRIMARY KEY CLUSTERED
12(
13 [folderID] ASC
14)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
15) ON [PRIMARY]
16
17CREATE NONCLUSTERED INDEX [owner] ON [inventoryfolders]
18(
19 [agentID] ASC
20)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
21
22CREATE NONCLUSTERED INDEX [parent] ON [inventoryfolders]
23(
24 [parentFolderID] ASC
25)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
26
27SET ANSI_PADDING OFF
diff --git a/share/sql/mssql-CreateItemsTable.sql b/share/sql/mssql-CreateItemsTable.sql
new file mode 100644
index 0000000..40c047e
--- /dev/null
+++ b/share/sql/mssql-CreateItemsTable.sql
@@ -0,0 +1,39 @@
1SET ANSI_NULLS ON
2
3SET QUOTED_IDENTIFIER ON
4
5SET ANSI_PADDING ON
6
7CREATE TABLE [inventoryitems] (
8 [inventoryID] [varchar](36) NOT NULL default '',
9 [assetID] [varchar](36) default NULL,
10 [assetType] [int] default NULL,
11 [parentFolderID] [varchar](36) default NULL,
12 [avatarID] [varchar](36) default NULL,
13 [inventoryName] [varchar](64) default NULL,
14 [inventoryDescription] [varchar](64) default NULL,
15 [inventoryNextPermissions] [int] default NULL,
16 [inventoryCurrentPermissions] [int] default NULL,
17 [invType] [int] default NULL,
18 [creatorID] [varchar](36) default NULL,
19 [inventoryBasePermissions] [int] NOT NULL default 0,
20 [inventoryEveryOnePermissions] [int] NOT NULL default 0,
21 PRIMARY KEY CLUSTERED
22(
23 [inventoryID] ASC
24)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
25) ON [PRIMARY]
26
27
28CREATE NONCLUSTERED INDEX [owner] ON [inventoryitems]
29(
30 [avatarID] ASC
31)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
32
33CREATE NONCLUSTERED INDEX [folder] ON [inventoryitems]
34(
35 [parentFolderID] ASC
36)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
37
38SET ANSI_PADDING OFF
39
diff --git a/share/sql/mssql-agents.sql b/share/sql/mssql-agents.sql
new file mode 100644
index 0000000..ad53173
--- /dev/null
+++ b/share/sql/mssql-agents.sql
@@ -0,0 +1,37 @@
1SET ANSI_NULLS ON
2
3SET QUOTED_IDENTIFIER ON
4
5SET ANSI_PADDING ON
6
7CREATE TABLE [agents] (
8 [UUID] [varchar](36) NOT NULL,
9 [sessionID] [varchar](36) NOT NULL,
10 [secureSessionID] [varchar](36) NOT NULL,
11 [agentIP] [varchar](16) NOT NULL,
12 [agentPort] [int] NOT NULL,
13 [agentOnline] [tinyint] NOT NULL,
14 [loginTime] [int] NOT NULL,
15 [logoutTime] [int] NOT NULL,
16 [currentRegion] [varchar](36) NOT NULL,
17 [currentHandle] [bigint] NOT NULL,
18 [currentPos] [varchar](64) NOT NULL,
19 PRIMARY KEY CLUSTERED
20(
21 [UUID] ASC
22)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
23) ON [PRIMARY]
24
25
26CREATE NONCLUSTERED INDEX [session] ON [agents]
27(
28 [sessionID] ASC
29)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
30
31CREATE NONCLUSTERED INDEX [ssession] ON [agents]
32(
33 [secureSessionID] ASC
34)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
35
36SET ANSI_PADDING OFF
37
diff --git a/share/sql/mssql-logs.sql b/share/sql/mssql-logs.sql
new file mode 100644
index 0000000..3b747d8
--- /dev/null
+++ b/share/sql/mssql-logs.sql
@@ -0,0 +1,20 @@
1SET ANSI_NULLS ON
2
3SET QUOTED_IDENTIFIER ON
4
5SET ANSI_PADDING ON
6
7CREATE TABLE [logs] (
8 [logID] [int] NOT NULL,
9 [target] [varchar](36) default NULL,
10 [server] [varchar](64) default NULL,
11 [method] [varchar](64) default NULL,
12 [arguments] [varchar](255) default NULL,
13 [priority] [int] default NULL,
14 [message] [ntext],
15 PRIMARY KEY CLUSTERED
16(
17 [logID] ASC
18)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
19) ON [PRIMARY]
20
diff --git a/share/sql/mssql-users.sql b/share/sql/mssql-users.sql
new file mode 100644
index 0000000..3f5f896
--- /dev/null
+++ b/share/sql/mssql-users.sql
@@ -0,0 +1,41 @@
1SET ANSI_NULLS ON
2
3SET QUOTED_IDENTIFIER ON
4
5SET ANSI_PADDING ON
6
7CREATE TABLE [users] (
8 [UUID] [varchar](36) NOT NULL default '',
9 [username] [varchar](32) NOT NULL,
10 [lastname] [varchar](32) NOT NULL,
11 [passwordHash] [varchar](32) NOT NULL,
12 [passwordSalt] [varchar](32) NOT NULL,
13 [homeRegion] [bigint] default NULL,
14 [homeLocationX] [float] default NULL,
15 [homeLocationY] [float] default NULL,
16 [homeLocationZ] [float] default NULL,
17 [homeLookAtX] [float] default NULL,
18 [homeLookAtY] [float] default NULL,
19 [homeLookAtZ] [float] default NULL,
20 [created] [int] NOT NULL,
21 [lastLogin] [int] NOT NULL,
22 [userInventoryURI] [varchar](255) default NULL,
23 [userAssetURI] [varchar](255) default NULL,
24 [profileCanDoMask] [int] default NULL,
25 [profileWantDoMask] [int] default NULL,
26 [profileAboutText] [ntext],
27 [profileFirstText] [ntext],
28 [profileImage] [varchar](36) default NULL,
29 [profileFirstImage] [varchar](36) default NULL,
30 PRIMARY KEY CLUSTERED
31(
32 [UUID] ASC
33)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
34) ON [PRIMARY]
35
36
37CREATE NONCLUSTERED INDEX [usernames] ON [users]
38(
39 [username] ASC,
40 [lastname] ASC
41)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
generated by cgit v1.1 at 2024-11-14 20:56:51 +0000