aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenGridServices/OpenGrid.Framework.Data.MySQL/MySQLManager.cs
diff options
context:
space:
mode:
Diffstat (limited to 'OpenGridServices/OpenGrid.Framework.Data.MySQL/MySQLManager.cs')
-rw-r--r--OpenGridServices/OpenGrid.Framework.Data.MySQL/MySQLManager.cs270
1 files changed, 270 insertions, 0 deletions
diff --git a/OpenGridServices/OpenGrid.Framework.Data.MySQL/MySQLManager.cs b/OpenGridServices/OpenGrid.Framework.Data.MySQL/MySQLManager.cs
new file mode 100644
index 0000000..ea7e2ac
--- /dev/null
+++ b/OpenGridServices/OpenGrid.Framework.Data.MySQL/MySQLManager.cs
@@ -0,0 +1,270 @@
1using System;
2using System.Collections.Generic;
3using System.Text;
4using System.Data;
5
6// MySQL Native
7using MySql;
8using MySql.Data;
9using MySql.Data.Types;
10using MySql.Data.MySqlClient;
11
12using OpenGrid.Framework.Data;
13
14namespace OpenGrid.Framework.Data.MySQL
15{
16 class MySQLManager
17 {
18 IDbConnection dbcon;
19
20 /// <summary>
21 /// Initialises and creates a new MySQL connection and maintains it.
22 /// </summary>
23 /// <param name="hostname">The MySQL server being connected to</param>
24 /// <param name="database">The name of the MySQL database being used</param>
25 /// <param name="username">The username logging into the database</param>
26 /// <param name="password">The password for the user logging in</param>
27 /// <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>
28 public MySQLManager(string hostname, string database, string username, string password, string cpooling, string port)
29 {
30 try
31 {
32 string connectionString = "Server=" + hostname + ";Port=" + port + ";Database=" + database + ";User ID=" + username + ";Password=" + password + ";Pooling=" + cpooling + ";";
33 dbcon = new MySqlConnection(connectionString);
34
35 dbcon.Open();
36 }
37 catch (Exception e)
38 {
39 throw new Exception("Error initialising MySql Database: " + e.ToString());
40 }
41 }
42
43 /// <summary>
44 /// Shuts down the database connection
45 /// </summary>
46 public void Close()
47 {
48 dbcon.Close();
49 dbcon = null;
50 }
51
52 /// <summary>
53 /// Runs a query with protection against SQL Injection by using parameterised input.
54 /// </summary>
55 /// <param name="sql">The SQL string - replace any variables such as WHERE x = "y" with WHERE x = @y</param>
56 /// <param name="parameters">The parameters - index so that @y is indexed as 'y'</param>
57 /// <returns>A MySQL DB Command</returns>
58 public IDbCommand Query(string sql, Dictionary<string, string> parameters)
59 {
60 try
61 {
62 MySqlCommand dbcommand = (MySqlCommand)dbcon.CreateCommand();
63 dbcommand.CommandText = sql;
64 foreach (KeyValuePair<string, string> param in parameters)
65 {
66 dbcommand.Parameters.Add(param.Key, param.Value);
67 }
68
69 return (IDbCommand)dbcommand;
70 }
71 catch (Exception e)
72 {
73 Console.WriteLine("Failed during Query generation: " + e.ToString());
74 return null;
75 }
76 }
77
78 public SimProfileData getSimRow(IDataReader reader)
79 {
80 SimProfileData retval = new SimProfileData();
81
82 if (reader.Read())
83 {
84 // Region Main
85 retval.regionHandle = Convert.ToUInt64(reader["regionHandle"].ToString());
86 retval.regionName = (string)reader["regionName"];
87 retval.UUID = new libsecondlife.LLUUID((string)reader["uuid"]);
88
89 // Secrets
90 retval.regionRecvKey = (string)reader["regionRecvKey"];
91 retval.regionSecret = (string)reader["regionSecret"];
92 retval.regionSendKey = (string)reader["regionSendKey"];
93
94 // Region Server
95 retval.regionDataURI = (string)reader["regionDataURI"];
96 retval.regionOnline = false; // Needs to be pinged before this can be set.
97 retval.serverIP = (string)reader["serverIP"];
98 retval.serverPort = (uint)reader["serverPort"];
99 retval.serverURI = (string)reader["serverURI"];
100
101 // Location
102 retval.regionLocX = Convert.ToUInt32(reader["locX"].ToString());
103 retval.regionLocY = Convert.ToUInt32(reader["locY"].ToString());
104 retval.regionLocZ = Convert.ToUInt32(reader["locZ"].ToString());
105
106 // Neighbours - 0 = No Override
107 retval.regionEastOverrideHandle = Convert.ToUInt64(reader["eastOverrideHandle"].ToString());
108 retval.regionWestOverrideHandle = Convert.ToUInt64(reader["westOverrideHandle"].ToString());
109 retval.regionSouthOverrideHandle = Convert.ToUInt64(reader["southOverrideHandle"].ToString());
110 retval.regionNorthOverrideHandle = Convert.ToUInt64(reader["northOverrideHandle"].ToString());
111
112 // Assets
113 retval.regionAssetURI = (string)reader["regionAssetURI"];
114 retval.regionAssetRecvKey = (string)reader["regionAssetRecvKey"];
115 retval.regionAssetSendKey = (string)reader["regionAssetSendKey"];
116
117 // Userserver
118 retval.regionUserURI = (string)reader["regionUserURI"];
119 retval.regionUserRecvKey = (string)reader["regionUserRecvKey"];
120 retval.regionUserSendKey = (string)reader["regionUserSendKey"];
121
122 // World Map Addition
123 retval.regionMapTextureID = new libsecondlife.LLUUID((string)reader["regionMapTexture"]);
124 }
125 else
126 {
127 return null;
128 }
129 return retval;
130 }
131
132 public UserAgentData getAgentRow(IDataReader reader)
133 {
134 UserAgentData retval = new UserAgentData();
135
136 if (reader.Read())
137 {
138 // Agent IDs
139 retval.UUID = new libsecondlife.LLUUID((string)reader["UUID"]);
140 retval.sessionID = new libsecondlife.LLUUID((string)reader["sessionID"]);
141 retval.secureSessionID = new libsecondlife.LLUUID((string)reader["secureSessionID"]);
142
143 // Agent Who?
144 retval.agentIP = (string)reader["agentIP"];
145 retval.agentPort = Convert.ToUInt32(reader["agentPort"].ToString());
146 retval.agentOnline = Convert.ToBoolean(reader["agentOnline"].ToString());
147
148 // Login/Logout times (UNIX Epoch)
149 retval.loginTime = Convert.ToInt32(reader["loginTime"].ToString());
150 retval.logoutTime = Convert.ToInt32(reader["logoutTime"].ToString());
151
152 // Current position
153 retval.currentRegion = (string)reader["currentRegion"];
154 retval.currentHandle = Convert.ToUInt64(reader["currentHandle"].ToString());
155 libsecondlife.LLVector3.TryParse((string)reader["currentPos"], out retval.currentPos);
156 }
157 else
158 {
159 return null;
160 }
161 return retval;
162 }
163
164 public UserProfileData getUserRow(IDataReader reader)
165 {
166 UserProfileData retval = new UserProfileData();
167
168 if (reader.Read())
169 {
170 retval.UUID = new libsecondlife.LLUUID((string)reader["UUID"]);
171 retval.username = (string)reader["username"];
172 retval.surname = (string)reader["lastname"];
173
174 retval.passwordHash = (string)reader["passwordHash"];
175 retval.passwordSalt = (string)reader["passwordSalt"];
176
177 retval.homeRegion = Convert.ToUInt64(reader["homeRegion"].ToString());
178 retval.homeLocation = new libsecondlife.LLVector3(
179 Convert.ToSingle(reader["homeLocationX"].ToString()),
180 Convert.ToSingle(reader["homeLocationY"].ToString()),
181 Convert.ToSingle(reader["homeLocationZ"].ToString()));
182 retval.homeLookAt = new libsecondlife.LLVector3(
183 Convert.ToSingle(reader["homeLookAtX"].ToString()),
184 Convert.ToSingle(reader["homeLookAtY"].ToString()),
185 Convert.ToSingle(reader["homeLookAtZ"].ToString()));
186
187 retval.created = Convert.ToInt32(reader["created"].ToString());
188 retval.lastLogin = Convert.ToInt32(reader["lastLogin"].ToString());
189
190 retval.userInventoryURI = (string)reader["userInventoryURI"];
191 retval.userAssetURI = (string)reader["userAssetURI"];
192
193 retval.profileCanDoMask = Convert.ToUInt32(reader["profileCanDoMask"].ToString());
194 retval.profileWantDoMask = Convert.ToUInt32(reader["profileWantDoMask"].ToString());
195
196 retval.profileAboutText = (string)reader["profileAboutText"];
197 retval.profileFirstText = (string)reader["profileFirstText"];
198
199 retval.profileImage = new libsecondlife.LLUUID((string)reader["profileImage"]);
200 retval.profileFirstImage = new libsecondlife.LLUUID((string)reader["profileFirstImage"]);
201
202 }
203 else
204 {
205 return null;
206 }
207 return retval;
208 }
209
210 public bool insertRow(SimProfileData profile)
211 {
212 string sql = "REPLACE INTO regions (regionHandle, regionName, uuid, regionRecvKey, regionSecret, regionSendKey, regionDataURI, ";
213 sql += "serverIP, serverPort, serverURI, locX, locY, locZ, eastOverrideHandle, westOverrideHandle, southOverrideHandle, northOverrideHandle, regionAssetURI, regionAssetRecvKey, ";
214 sql += "regionAssetSendKey, regionUserURI, regionUserRecvKey, regionUserSendKey, regionMapTexture) VALUES ";
215
216 sql += "(?regionHandle, ?regionName, ?uuid, ?regionRecvKey, ?regionSecret, ?regionSendKey, ?regionDataURI, ";
217 sql += "?serverIP, ?serverPort, ?serverURI, ?locX, ?locY, ?locZ, ?eastOverrideHandle, ?westOverrideHandle, ?southOverrideHandle, ?northOverrideHandle, ?regionAssetURI, ?regionAssetRecvKey, ";
218 sql += "?regionAssetSendKey, ?regionUserURI, ?regionUserRecvKey, ?regionUserSendKey, ?regionMapTexture);";
219
220 Dictionary<string, string> parameters = new Dictionary<string, string>();
221
222 parameters["?regionHandle"] = profile.regionHandle.ToString();
223 parameters["?regionName"] = profile.regionName.ToString();
224 parameters["?uuid"] = profile.UUID.ToStringHyphenated();
225 parameters["?regionRecvKey"] = profile.regionRecvKey.ToString();
226 parameters["?regionSecret"] = profile.regionSecret.ToString();
227 parameters["?regionSendKey"] = profile.regionSendKey.ToString();
228 parameters["?regionDataURI"] = profile.regionDataURI.ToString();
229 parameters["?serverIP"] = profile.serverIP.ToString();
230 parameters["?serverPort"] = profile.serverPort.ToString();
231 parameters["?serverURI"] = profile.serverURI.ToString();
232 parameters["?locX"] = profile.regionLocX.ToString();
233 parameters["?locY"] = profile.regionLocY.ToString();
234 parameters["?locZ"] = profile.regionLocZ.ToString();
235 parameters["?eastOverrideHandle"] = profile.regionEastOverrideHandle.ToString();
236 parameters["?westOverrideHandle"] = profile.regionWestOverrideHandle.ToString();
237 parameters["?northOverrideHandle"] = profile.regionNorthOverrideHandle.ToString();
238 parameters["?southOverrideHandle"] = profile.regionSouthOverrideHandle.ToString();
239 parameters["?regionAssetURI"] = profile.regionAssetURI.ToString();
240 parameters["?regionAssetRecvKey"] = profile.regionAssetRecvKey.ToString();
241 parameters["?regionAssetSendKey"] = profile.regionAssetSendKey.ToString();
242 parameters["?regionUserURI"] = profile.regionUserURI.ToString();
243 parameters["?regionUserRecvKey"] = profile.regionUserRecvKey.ToString();
244 parameters["?regionUserSendKey"] = profile.regionUserSendKey.ToString();
245 parameters["?regionMapTexture"] = profile.regionMapTextureID.ToStringHyphenated();
246
247 bool returnval = false;
248
249 try
250 {
251
252 IDbCommand result = Query(sql, parameters);
253
254 //Console.WriteLine(result.CommandText);
255
256 if (result.ExecuteNonQuery() == 1)
257 returnval = true;
258
259 result.Dispose();
260 }
261 catch (Exception e)
262 {
263 Console.WriteLine(e.ToString());
264 return false;
265 }
266
267 return returnval;
268 }
269 }
270}
generated by cgit v1.1 at 2024-11-05 01:04:10 +0000