aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Framework/Data.MSSQL
diff options
context:
space:
mode:
authorTeravus Ovares2007-12-05 15:53:58 +0000
committerTeravus Ovares2007-12-05 15:53:58 +0000
commit71fd737a66c58faa6854ad1c1d8c58f64491fdb0 (patch)
treec9c876c361afbf76d416b7f2c413ff56a1f9beef /OpenSim/Framework/Data.MSSQL
parentmade one or two more methods in Scene virtual to allow overriding in sub clas... (diff)
downloadopensim-SC-71fd737a66c58faa6854ad1c1d8c58f64491fdb0.zip
opensim-SC-71fd737a66c58faa6854ad1c1d8c58f64491fdb0.tar.gz
opensim-SC-71fd737a66c58faa6854ad1c1d8c58f64491fdb0.tar.bz2
opensim-SC-71fd737a66c58faa6854ad1c1d8c58f64491fdb0.tar.xz
* Applied MSSQL Patch from akokko, Thanks! akokko
* This hasn't been tested in MSSQL mode, however it's been checked to make sure it doesn't cause any issues with mySQL/SQLlite
Diffstat (limited to 'OpenSim/Framework/Data.MSSQL')
-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
12 files changed, 2369 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]
generated by cgit v1.1 at 2024-12-13 22:48:34 +0000