aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/SQLite/SQLiteUserData.cs
diff options
context:
space:
mode:
Diffstat (limited to 'OpenSim/Data/SQLite/SQLiteUserData.cs')
-rw-r--r--OpenSim/Data/SQLite/SQLiteUserData.cs821
1 files changed, 821 insertions, 0 deletions
diff --git a/OpenSim/Data/SQLite/SQLiteUserData.cs b/OpenSim/Data/SQLite/SQLiteUserData.cs
new file mode 100644
index 0000000..2efd4aa
--- /dev/null
+++ b/OpenSim/Data/SQLite/SQLiteUserData.cs
@@ -0,0 +1,821 @@
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 libsecondlife;
32using Mono.Data.SqliteClient;
33using OpenSim.Framework.Console;
34
35namespace OpenSim.Framework.Data.SQLite
36{
37 /// <summary>
38 /// A User storage interface for the SQLite database system
39 /// </summary>
40 public class SQLiteUserData : UserDataBase
41 {
42 private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
43
44 /// <summary>
45 /// The database manager
46 /// </summary>
47 /// <summary>
48 /// Artificial constructor called upon plugin load
49 /// </summary>
50 private const string SelectUserByUUID = "select * from users where UUID=:UUID";
51 private const string SelectUserByName = "select * from users where username=:username and surname=:surname";
52 private const string SelectFriendsByUUID = "select a.friendID, a.friendPerms, b.friendPerms from userfriends as a, userfriends as b where a.ownerID=:ownerID and b.ownerID=a.friendID and b.friendID=a.ownerID";
53
54 private const string userSelect = "select * from users";
55 private const string userFriendsSelect = "select a.ownerID as ownerID,a.friendID as friendID,a.friendPerms as friendPerms,b.friendPerms as ownerperms, b.ownerID as fownerID, b.friendID as ffriendID from userfriends as a, userfriends as b";
56
57 private const string AvatarPickerAndSQL = "select * from users where username like :username and surname like :surname";
58 private const string AvatarPickerOrSQL = "select * from users where username like :username or surname like :surname";
59
60 private DataSet ds;
61 private SqliteDataAdapter da;
62 private SqliteDataAdapter daf;
63 SqliteConnection g_conn;
64
65 override public void Initialise()
66 {
67 SqliteConnection conn = new SqliteConnection("URI=file:userprofiles.db,version=3");
68 TestTables(conn);
69
70 // This sucks, but It doesn't seem to work with the dataset Syncing :P
71 g_conn = conn;
72 g_conn.Open();
73
74 ds = new DataSet();
75 da = new SqliteDataAdapter(new SqliteCommand(userSelect, conn));
76 daf = new SqliteDataAdapter(new SqliteCommand(userFriendsSelect, conn));
77
78 lock (ds)
79 {
80 ds.Tables.Add(createUsersTable());
81 ds.Tables.Add(createUserAgentsTable());
82 ds.Tables.Add(createUserFriendsTable());
83
84 setupUserCommands(da, conn);
85 da.Fill(ds.Tables["users"]);
86
87 setupUserFriendsCommands(daf, conn);
88 try
89 {
90 daf.Fill(ds.Tables["userfriends"]);
91 }
92 catch (SqliteSyntaxException)
93 {
94 m_log.Info("[SQLITE]: userfriends table not found, creating.... ");
95 InitDB(conn);
96 daf.Fill(ds.Tables["userfriends"]);
97 }
98
99 }
100
101 return;
102 }
103
104 // see IUserData
105 override public UserProfileData GetUserByUUID(LLUUID uuid)
106 {
107 lock (ds)
108 {
109 DataRow row = ds.Tables["users"].Rows.Find(Util.ToRawUuidString(uuid));
110 if (row != null)
111 {
112 UserProfileData user = buildUserProfile(row);
113 row = ds.Tables["useragents"].Rows.Find(Util.ToRawUuidString(uuid));
114 if (row != null)
115 {
116 user.currentAgent = buildUserAgent(row);
117 }
118 return user;
119 }
120 else
121 {
122 return null;
123 }
124 }
125 }
126
127 // see IUserData
128 override public UserProfileData GetUserByName(string fname, string lname)
129 {
130 string select = "surname = '" + lname + "' and username = '" + fname + "'";
131 lock (ds)
132 {
133 DataRow[] rows = ds.Tables["users"].Select(select);
134 if (rows.Length > 0)
135 {
136 UserProfileData user = buildUserProfile(rows[0]);
137 DataRow row = ds.Tables["useragents"].Rows.Find(Util.ToRawUuidString(user.UUID));
138 if (row != null)
139 {
140 user.currentAgent = buildUserAgent(row);
141 }
142 return user;
143 }
144 else
145 {
146 return null;
147 }
148 }
149 }
150
151 #region User Friends List Data
152
153 override public void AddNewUserFriend(LLUUID friendlistowner, LLUUID friend, uint perms)
154 {
155 string InsertFriends = "insert into userfriends(ownerID, friendID, friendPerms) values(:ownerID, :friendID, :perms)";
156
157 using (SqliteCommand cmd = new SqliteCommand(InsertFriends, g_conn))
158 {
159 cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.UUID.ToString()));
160 cmd.Parameters.Add(new SqliteParameter(":friendID", friend.UUID.ToString()));
161 cmd.Parameters.Add(new SqliteParameter(":perms", perms));
162 cmd.ExecuteNonQuery();
163 }
164 using (SqliteCommand cmd = new SqliteCommand(InsertFriends, g_conn))
165 {
166 cmd.Parameters.Add(new SqliteParameter(":ownerID", friend.UUID.ToString()));
167 cmd.Parameters.Add(new SqliteParameter(":friendID", friendlistowner.UUID.ToString()));
168 cmd.Parameters.Add(new SqliteParameter(":perms", perms));
169 cmd.ExecuteNonQuery();
170 }
171 }
172
173 override public void RemoveUserFriend(LLUUID friendlistowner, LLUUID friend)
174 {
175 string DeletePerms = "delete from friendlist where (ownerID=:ownerID and friendID=:friendID) or (ownerID=:friendID and friendID=:ownerID)";
176 using (SqliteCommand cmd = new SqliteCommand(DeletePerms, g_conn))
177 {
178 cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.UUID.ToString()));
179 cmd.Parameters.Add(new SqliteParameter(":friendID", friend.UUID.ToString()));
180 cmd.ExecuteNonQuery();
181 }
182 }
183
184 override public void UpdateUserFriendPerms(LLUUID friendlistowner, LLUUID friend, uint perms)
185 {
186 string UpdatePerms = "update friendlist set perms=:perms where ownerID=:ownerID and friendID=:friendID";
187 using (SqliteCommand cmd = new SqliteCommand(UpdatePerms, g_conn))
188 {
189 cmd.Parameters.Add(new SqliteParameter(":perms", perms));
190 cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.UUID.ToString()));
191 cmd.Parameters.Add(new SqliteParameter(":friendID", friend.UUID.ToString()));
192 cmd.ExecuteNonQuery();
193 }
194 }
195
196 override public List<FriendListItem> GetUserFriendList(LLUUID friendlistowner)
197 {
198 List<FriendListItem> returnlist = new List<FriendListItem>();
199
200 using (SqliteCommand cmd = new SqliteCommand(SelectFriendsByUUID, g_conn))
201 {
202 cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.UUID.ToString()));
203
204 try
205 {
206 using (IDataReader reader = cmd.ExecuteReader())
207 {
208 while (reader.Read())
209 {
210 FriendListItem user = new FriendListItem();
211 user.FriendListOwner = friendlistowner;
212 user.Friend = new LLUUID((string)reader[0]);
213 user.FriendPerms = Convert.ToUInt32(reader[1]);
214 user.FriendListOwnerPerms = Convert.ToUInt32(reader[2]);
215 returnlist.Add(user);
216 }
217 reader.Close();
218 }
219 }
220 catch (Exception ex)
221 {
222 m_log.Error("[USER]: Exception getting friends list for user: " + ex.ToString());
223 }
224 }
225
226 return returnlist;
227 }
228
229
230
231
232 #endregion
233
234 override public void UpdateUserCurrentRegion(LLUUID avatarid, LLUUID regionuuid)
235 {
236 m_log.Info("[USER]: Stub UpdateUserCUrrentRegion called");
237 }
238
239
240 override public List<Framework.AvatarPickerAvatar> GeneratePickerResults(LLUUID queryID, string query)
241 {
242 List<Framework.AvatarPickerAvatar> returnlist = new List<Framework.AvatarPickerAvatar>();
243 string[] querysplit;
244 querysplit = query.Split(' ');
245 if (querysplit.Length == 2)
246 {
247 using (SqliteCommand cmd = new SqliteCommand(AvatarPickerAndSQL, g_conn))
248 {
249 cmd.Parameters.Add(new SqliteParameter(":username", querysplit[0] + "%"));
250 cmd.Parameters.Add(new SqliteParameter(":surname", querysplit[1] + "%"));
251
252 using (IDataReader reader = cmd.ExecuteReader())
253 {
254 while (reader.Read())
255 {
256 Framework.AvatarPickerAvatar user = new Framework.AvatarPickerAvatar();
257 user.AvatarID = new LLUUID((string) reader["UUID"]);
258 user.firstName = (string) reader["username"];
259 user.lastName = (string) reader["surname"];
260 returnlist.Add(user);
261 }
262 reader.Close();
263 }
264 }
265 }
266 else if (querysplit.Length == 1)
267 {
268 using (SqliteCommand cmd = new SqliteCommand(AvatarPickerOrSQL, g_conn))
269 {
270 cmd.Parameters.Add(new SqliteParameter(":username", querysplit[0] + "%"));
271 cmd.Parameters.Add(new SqliteParameter(":surname", querysplit[0] + "%"));
272
273 using (IDataReader reader = cmd.ExecuteReader())
274 {
275 while (reader.Read())
276 {
277 Framework.AvatarPickerAvatar user = new Framework.AvatarPickerAvatar();
278 user.AvatarID = new LLUUID((string) reader["UUID"]);
279 user.firstName = (string) reader["username"];
280 user.lastName = (string) reader["surname"];
281 returnlist.Add(user);
282 }
283 reader.Close();
284 }
285 }
286 }
287 return returnlist;
288 }
289
290 /// <summary>
291 /// Returns a user by UUID direct
292 /// </summary>
293 /// <param name="uuid">The user's account ID</param>
294 /// <returns>A matching user profile</returns>
295 override public UserAgentData GetAgentByUUID(LLUUID uuid)
296 {
297 try
298 {
299 return GetUserByUUID(uuid).currentAgent;
300 }
301 catch (Exception)
302 {
303 return null;
304 }
305 }
306
307 /// <summary>
308 /// Returns a session by account name
309 /// </summary>
310 /// <param name="name">The account name</param>
311 /// <returns>The user's session agent</returns>
312 override public UserAgentData GetAgentByName(string name)
313 {
314 return GetAgentByName(name.Split(' ')[0], name.Split(' ')[1]);
315 }
316
317 /// <summary>
318 /// Returns a session by account name
319 /// </summary>
320 /// <param name="fname">The first part of the user's account name</param>
321 /// <param name="lname">The second part of the user's account name</param>
322 /// <returns>A user agent</returns>
323 override public UserAgentData GetAgentByName(string fname, string lname)
324 {
325 try
326 {
327 return GetUserByName(fname, lname).currentAgent;
328 }
329 catch (Exception)
330 {
331 return null;
332 }
333 }
334
335
336 override public void StoreWebLoginKey(LLUUID AgentID, LLUUID WebLoginKey)
337 {
338 DataTable users = ds.Tables["users"];
339 lock (ds)
340 {
341 DataRow row = users.Rows.Find(Util.ToRawUuidString(AgentID));
342 if (row == null)
343 {
344 m_log.Warn("[WEBLOGIN]: Unable to store new web login key for non-existant user");
345 }
346 else
347 {
348 UserProfileData user = GetUserByUUID(AgentID);
349 user.webLoginKey = WebLoginKey;
350 fillUserRow(row, user);
351 da.Update(ds, "users");
352
353 }
354 }
355
356 }
357
358 /// <summary>
359 /// Creates a new user profile
360 /// </summary>
361 /// <param name="user">The profile to add to the database</param>
362 override public void AddNewUserProfile(UserProfileData user)
363 {
364 DataTable users = ds.Tables["users"];
365 lock (ds)
366 {
367 DataRow row = users.Rows.Find(Util.ToRawUuidString(user.UUID));
368 if (row == null)
369 {
370 row = users.NewRow();
371 fillUserRow(row, user);
372 users.Rows.Add(row);
373 }
374 else
375 {
376 fillUserRow(row, user);
377
378 }
379 // This is why we're getting the 'logins never log-off'.. because It isn't clearing the
380 // useragents table once the useragent is null
381 //
382 // A database guy should look at this and figure out the best way to clear the useragents table.
383 if (user.currentAgent != null)
384 {
385 DataTable ua = ds.Tables["useragents"];
386 row = ua.Rows.Find(Util.ToRawUuidString(user.UUID));
387 if (row == null)
388 {
389 row = ua.NewRow();
390 fillUserAgentRow(row, user.currentAgent);
391 ua.Rows.Add(row);
392 }
393 else
394 {
395 fillUserAgentRow(row, user.currentAgent);
396 }
397 }
398 else
399 {
400 // I just added this to help the standalone login situation.
401 //It still needs to be looked at by a Database guy
402 DataTable ua = ds.Tables["useragents"];
403 row = ua.Rows.Find(Util.ToRawUuidString(user.UUID));
404
405 if (row == null)
406 {
407 // do nothing
408 }
409 else
410 {
411 row.Delete();
412 ua.AcceptChanges();
413 }
414 }
415
416 m_log.Info("[SQLITE]: " +
417 "Syncing user database: " + ds.Tables["users"].Rows.Count + " users stored");
418 // save changes off to disk
419 da.Update(ds, "users");
420 }
421 }
422
423 /// <summary>
424 /// Creates a new user profile
425 /// </summary>
426 /// <param name="user">The profile to add to the database</param>
427 /// <returns>True on success, false on error</returns>
428 override public bool UpdateUserProfile(UserProfileData user)
429 {
430 try
431 {
432 AddNewUserProfile(user);
433 return true;
434 }
435 catch (Exception)
436 {
437 return false;
438 }
439 }
440
441 /// <summary>
442 /// Creates a new user agent
443 /// </summary>
444 /// <param name="agent">The agent to add to the database</param>
445 override public void AddNewUserAgent(UserAgentData agent)
446 {
447 // Do nothing. yet.
448 }
449
450 /// <summary>
451 /// Transfers money between two user accounts
452 /// </summary>
453 /// <param name="from">Starting account</param>
454 /// <param name="to">End account</param>
455 /// <param name="amount">The amount to move</param>
456 /// <returns>Success?</returns>
457 override public bool MoneyTransferRequest(LLUUID from, LLUUID to, uint amount)
458 {
459 return true;
460 }
461
462 /// <summary>
463 /// Transfers inventory between two accounts
464 /// </summary>
465 /// <remarks>Move to inventory server</remarks>
466 /// <param name="from">Senders account</param>
467 /// <param name="to">Receivers account</param>
468 /// <param name="item">Inventory item</param>
469 /// <returns>Success?</returns>
470 override public bool InventoryTransferRequest(LLUUID from, LLUUID to, LLUUID item)
471 {
472 return true;
473 }
474
475 /// <summary>
476 /// Returns the name of the storage provider
477 /// </summary>
478 /// <returns>Storage provider name</returns>
479 override public string getName()
480 {
481 return "Sqlite Userdata";
482 }
483
484 /// <summary>
485 /// Returns the version of the storage provider
486 /// </summary>
487 /// <returns>Storage provider version</returns>
488 override public string GetVersion()
489 {
490 return "0.1";
491 }
492
493 /***********************************************************************
494 *
495 * DataTable creation
496 *
497 **********************************************************************/
498 /***********************************************************************
499 *
500 * Database Definition Functions
501 *
502 * This should be db agnostic as we define them in ADO.NET terms
503 *
504 **********************************************************************/
505
506 private static DataTable createUsersTable()
507 {
508 DataTable users = new DataTable("users");
509
510 SQLiteUtil.createCol(users, "UUID", typeof (String));
511 SQLiteUtil.createCol(users, "username", typeof (String));
512 SQLiteUtil.createCol(users, "surname", typeof (String));
513 SQLiteUtil.createCol(users, "passwordHash", typeof (String));
514 SQLiteUtil.createCol(users, "passwordSalt", typeof (String));
515
516 SQLiteUtil.createCol(users, "homeRegionX", typeof (Int32));
517 SQLiteUtil.createCol(users, "homeRegionY", typeof (Int32));
518 SQLiteUtil.createCol(users, "homeLocationX", typeof (Double));
519 SQLiteUtil.createCol(users, "homeLocationY", typeof (Double));
520 SQLiteUtil.createCol(users, "homeLocationZ", typeof (Double));
521 SQLiteUtil.createCol(users, "homeLookAtX", typeof (Double));
522 SQLiteUtil.createCol(users, "homeLookAtY", typeof (Double));
523 SQLiteUtil.createCol(users, "homeLookAtZ", typeof (Double));
524 SQLiteUtil.createCol(users, "created", typeof (Int32));
525 SQLiteUtil.createCol(users, "lastLogin", typeof (Int32));
526 SQLiteUtil.createCol(users, "rootInventoryFolderID", typeof (String));
527 SQLiteUtil.createCol(users, "userInventoryURI", typeof (String));
528 SQLiteUtil.createCol(users, "userAssetURI", typeof (String));
529 SQLiteUtil.createCol(users, "profileCanDoMask", typeof (Int32));
530 SQLiteUtil.createCol(users, "profileWantDoMask", typeof (Int32));
531 SQLiteUtil.createCol(users, "profileAboutText", typeof (String));
532 SQLiteUtil.createCol(users, "profileFirstText", typeof (String));
533 SQLiteUtil.createCol(users, "profileImage", typeof (String));
534 SQLiteUtil.createCol(users, "profileFirstImage", typeof (String));
535 SQLiteUtil.createCol(users, "webLoginKey", typeof(String));
536 // Add in contraints
537 users.PrimaryKey = new DataColumn[] {users.Columns["UUID"]};
538 return users;
539 }
540
541 private static DataTable createUserAgentsTable()
542 {
543 DataTable ua = new DataTable("useragents");
544 // this is the UUID of the user
545 SQLiteUtil.createCol(ua, "UUID", typeof (String));
546 SQLiteUtil.createCol(ua, "agentIP", typeof (String));
547 SQLiteUtil.createCol(ua, "agentPort", typeof (Int32));
548 SQLiteUtil.createCol(ua, "agentOnline", typeof (Boolean));
549 SQLiteUtil.createCol(ua, "sessionID", typeof (String));
550 SQLiteUtil.createCol(ua, "secureSessionID", typeof (String));
551 SQLiteUtil.createCol(ua, "regionID", typeof (String));
552 SQLiteUtil.createCol(ua, "loginTime", typeof (Int32));
553 SQLiteUtil.createCol(ua, "logoutTime", typeof (Int32));
554 SQLiteUtil.createCol(ua, "currentRegion", typeof (String));
555 SQLiteUtil.createCol(ua, "currentHandle", typeof (String));
556 // vectors
557 SQLiteUtil.createCol(ua, "currentPosX", typeof (Double));
558 SQLiteUtil.createCol(ua, "currentPosY", typeof (Double));
559 SQLiteUtil.createCol(ua, "currentPosZ", typeof (Double));
560 // constraints
561 ua.PrimaryKey = new DataColumn[] {ua.Columns["UUID"]};
562
563 return ua;
564 }
565
566 private static DataTable createUserFriendsTable()
567 {
568 DataTable ua = new DataTable("userfriends");
569 // table contains user <----> user relationship with perms
570 SQLiteUtil.createCol(ua, "ownerID", typeof(String));
571 SQLiteUtil.createCol(ua, "friendID", typeof(String));
572 SQLiteUtil.createCol(ua, "friendPerms", typeof(Int32));
573 SQLiteUtil.createCol(ua, "ownerPerms", typeof(Int32));
574 SQLiteUtil.createCol(ua, "datetimestamp", typeof(Int32));
575
576 return ua;
577 }
578
579 /***********************************************************************
580 *
581 * Convert between ADO.NET <=> OpenSim Objects
582 *
583 * These should be database independant
584 *
585 **********************************************************************/
586
587 private static UserProfileData buildUserProfile(DataRow row)
588 {
589 // TODO: this doesn't work yet because something more
590 // interesting has to be done to actually get these values
591 // back out. Not enough time to figure it out yet.
592 UserProfileData user = new UserProfileData();
593 LLUUID.TryParse((String)row["UUID"], out user.UUID);
594 user.username = (String) row["username"];
595 user.surname = (String) row["surname"];
596 user.passwordHash = (String) row["passwordHash"];
597 user.passwordSalt = (String) row["passwordSalt"];
598
599 user.homeRegionX = Convert.ToUInt32(row["homeRegionX"]);
600 user.homeRegionY = Convert.ToUInt32(row["homeRegionY"]);
601 user.homeLocation = new LLVector3(
602 Convert.ToSingle(row["homeLocationX"]),
603 Convert.ToSingle(row["homeLocationY"]),
604 Convert.ToSingle(row["homeLocationZ"])
605 );
606 user.homeLookAt = new LLVector3(
607 Convert.ToSingle(row["homeLookAtX"]),
608 Convert.ToSingle(row["homeLookAtY"]),
609 Convert.ToSingle(row["homeLookAtZ"])
610 );
611 user.created = Convert.ToInt32(row["created"]);
612 user.lastLogin = Convert.ToInt32(row["lastLogin"]);
613 user.rootInventoryFolderID = new LLUUID((String) row["rootInventoryFolderID"]);
614 user.userInventoryURI = (String) row["userInventoryURI"];
615 user.userAssetURI = (String) row["userAssetURI"];
616 user.profileCanDoMask = Convert.ToUInt32(row["profileCanDoMask"]);
617 user.profileWantDoMask = Convert.ToUInt32(row["profileWantDoMask"]);
618 user.profileAboutText = (String) row["profileAboutText"];
619 user.profileFirstText = (String) row["profileFirstText"];
620 LLUUID.TryParse((String)row["profileImage"], out user.profileImage);
621 LLUUID.TryParse((String)row["profileFirstImage"], out user.profileFirstImage);
622 user.webLoginKey = new LLUUID((String) row["webLoginKey"]);
623
624 return user;
625 }
626
627 private void fillUserRow(DataRow row, UserProfileData user)
628 {
629 row["UUID"] = Util.ToRawUuidString(user.UUID);
630 row["username"] = user.username;
631 row["surname"] = user.surname;
632 row["passwordHash"] = user.passwordHash;
633 row["passwordSalt"] = user.passwordSalt;
634
635
636 row["homeRegionX"] = user.homeRegionX;
637 row["homeRegionY"] = user.homeRegionY;
638 row["homeLocationX"] = user.homeLocation.X;
639 row["homeLocationY"] = user.homeLocation.Y;
640 row["homeLocationZ"] = user.homeLocation.Z;
641 row["homeLookAtX"] = user.homeLookAt.X;
642 row["homeLookAtY"] = user.homeLookAt.Y;
643 row["homeLookAtZ"] = user.homeLookAt.Z;
644
645 row["created"] = user.created;
646 row["lastLogin"] = user.lastLogin;
647 row["rootInventoryFolderID"] = user.rootInventoryFolderID;
648 row["userInventoryURI"] = user.userInventoryURI;
649 row["userAssetURI"] = user.userAssetURI;
650 row["profileCanDoMask"] = user.profileCanDoMask;
651 row["profileWantDoMask"] = user.profileWantDoMask;
652 row["profileAboutText"] = user.profileAboutText;
653 row["profileFirstText"] = user.profileFirstText;
654 row["profileImage"] = user.profileImage;
655 row["profileFirstImage"] = user.profileFirstImage;
656 row["webLoginKey"] = user.webLoginKey;
657
658 // ADO.NET doesn't handle NULL very well
659 foreach (DataColumn col in ds.Tables["users"].Columns)
660 {
661 if (row[col] == null)
662 {
663 row[col] = String.Empty;
664 }
665 }
666 }
667
668 private static UserAgentData buildUserAgent(DataRow row)
669 {
670 UserAgentData ua = new UserAgentData();
671
672 ua.UUID = new LLUUID((String) row["UUID"]);
673 ua.agentIP = (String) row["agentIP"];
674 ua.agentPort = Convert.ToUInt32(row["agentPort"]);
675 ua.agentOnline = Convert.ToBoolean(row["agentOnline"]);
676 ua.sessionID = new LLUUID((String) row["sessionID"]);
677 ua.secureSessionID = new LLUUID((String) row["secureSessionID"]);
678 ua.regionID = new LLUUID((String) row["regionID"]);
679 ua.loginTime = Convert.ToInt32(row["loginTime"]);
680 ua.logoutTime = Convert.ToInt32(row["logoutTime"]);
681 ua.currentRegion = new LLUUID((String) row["currentRegion"]);
682 ua.currentHandle = Convert.ToUInt64(row["currentHandle"]);
683 ua.currentPos = new LLVector3(
684 Convert.ToSingle(row["currentPosX"]),
685 Convert.ToSingle(row["currentPosY"]),
686 Convert.ToSingle(row["currentPosZ"])
687 );
688 return ua;
689 }
690
691 private static void fillUserAgentRow(DataRow row, UserAgentData ua)
692 {
693 row["UUID"] = ua.UUID;
694 row["agentIP"] = ua.agentIP;
695 row["agentPort"] = ua.agentPort;
696 row["agentOnline"] = ua.agentOnline;
697 row["sessionID"] = ua.sessionID;
698 row["secureSessionID"] = ua.secureSessionID;
699 row["regionID"] = ua.regionID;
700 row["loginTime"] = ua.loginTime;
701 row["logoutTime"] = ua.logoutTime;
702 row["currentRegion"] = ua.currentRegion;
703 row["currentHandle"] = ua.currentHandle.ToString();
704 // vectors
705 row["currentPosX"] = ua.currentPos.X;
706 row["currentPosY"] = ua.currentPos.Y;
707 row["currentPosZ"] = ua.currentPos.Z;
708 }
709
710 /***********************************************************************
711 *
712 * Database Binding functions
713 *
714 * These will be db specific due to typing, and minor differences
715 * in databases.
716 *
717 **********************************************************************/
718
719 private void setupUserCommands(SqliteDataAdapter da, SqliteConnection conn)
720 {
721 da.InsertCommand = SQLiteUtil.createInsertCommand("users", ds.Tables["users"]);
722 da.InsertCommand.Connection = conn;
723
724 da.UpdateCommand = SQLiteUtil.createUpdateCommand("users", "UUID=:UUID", ds.Tables["users"]);
725 da.UpdateCommand.Connection = conn;
726
727 SqliteCommand delete = new SqliteCommand("delete from users where UUID = :UUID");
728 delete.Parameters.Add(SQLiteUtil.createSqliteParameter("UUID", typeof(String)));
729 delete.Connection = conn;
730 da.DeleteCommand = delete;
731 }
732
733 private void setupUserFriendsCommands(SqliteDataAdapter daf, SqliteConnection conn)
734 {
735 daf.InsertCommand = SQLiteUtil.createInsertCommand("userfriends", ds.Tables["userfriends"]);
736 daf.InsertCommand.Connection = conn;
737
738 daf.UpdateCommand = SQLiteUtil.createUpdateCommand("userfriends", "ownerID=:ownerID and friendID=:friendID", ds.Tables["userfriends"]);
739 daf.UpdateCommand.Connection = conn;
740
741 SqliteCommand delete = new SqliteCommand("delete from userfriends where ownerID=:ownerID and friendID=:friendID");
742 delete.Parameters.Add(SQLiteUtil.createSqliteParameter("ownerID", typeof(String)));
743 delete.Parameters.Add(SQLiteUtil.createSqliteParameter("friendID", typeof(String)));
744 delete.Connection = conn;
745 daf.DeleteCommand = delete;
746
747 }
748
749 private void InitDB(SqliteConnection conn)
750 {
751 string createUsers = SQLiteUtil.defineTable(createUsersTable());
752 string createFriends = SQLiteUtil.defineTable(createUserFriendsTable());
753
754 SqliteCommand pcmd = new SqliteCommand(createUsers, conn);
755 SqliteCommand fcmd = new SqliteCommand(createFriends, conn);
756
757 conn.Open();
758
759 try
760 {
761
762 pcmd.ExecuteNonQuery();
763 }
764 catch (System.Exception)
765 {
766 m_log.Info("[USERS]: users table already exists");
767 }
768
769 try
770 {
771 fcmd.ExecuteNonQuery();
772 }
773 catch (System.Exception)
774 {
775 m_log.Info("[USERS]: userfriends table already exists");
776 }
777
778 conn.Close();
779 }
780
781 private bool TestTables(SqliteConnection conn)
782 {
783 SqliteCommand cmd = new SqliteCommand(userSelect, conn);
784 SqliteCommand fcmd = new SqliteCommand(userFriendsSelect, conn);
785 SqliteDataAdapter pDa = new SqliteDataAdapter(cmd);
786 SqliteDataAdapter fDa = new SqliteDataAdapter(cmd);
787
788 DataSet tmpDS = new DataSet();
789 DataSet tmpDS2 = new DataSet();
790
791 try
792 {
793 pDa.Fill(tmpDS, "users");
794 fDa.Fill(tmpDS2, "userfriends");
795 }
796 catch (SqliteSyntaxException)
797 {
798 m_log.Info("[DATASTORE]: SQLite Database doesn't exist... creating");
799 InitDB(conn);
800 }
801 conn.Open();
802 try
803 {
804 cmd = new SqliteCommand("select webLoginKey from users limit 1;", conn);
805 cmd.ExecuteNonQuery();
806 }
807 catch (SqliteSyntaxException)
808 {
809 cmd = new SqliteCommand("alter table users add column webLoginKey text default '00000000-0000-0000-0000-000000000000';", conn);
810 cmd.ExecuteNonQuery();
811 pDa.Fill(tmpDS, "users");
812 }
813 finally
814 {
815 conn.Close();
816 }
817
818 return true;
819 }
820 }
821}
generated by cgit v1.1 at 2024-11-10 16:31:29 +0000