aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/SQLite
diff options
context:
space:
mode:
Diffstat (limited to 'OpenSim/Data/SQLite')
-rw-r--r--OpenSim/Data/SQLite/Resources/006_UserStore.sql20
-rw-r--r--OpenSim/Data/SQLite/SQLiteUserData.cs131
2 files changed, 84 insertions, 67 deletions
diff --git a/OpenSim/Data/SQLite/Resources/006_UserStore.sql b/OpenSim/Data/SQLite/Resources/006_UserStore.sql
new file mode 100644
index 0000000..f9454c5
--- /dev/null
+++ b/OpenSim/Data/SQLite/Resources/006_UserStore.sql
@@ -0,0 +1,20 @@
1BEGIN TRANSACTION;
2
3-- usersagents table
4CREATE TABLE IF NOT EXISTS useragents(
5 UUID varchar(255) primary key,
6 agentIP varchar(255),
7 agentPort integer,
8 agentOnline boolean,
9 sessionID varchar(255),
10 secureSessionID varchar(255),
11 regionID varchar(255),
12 loginTime integer,
13 logoutTime integer,
14 currentRegion varchar(255),
15 currentHandle varchar(255),
16 currentPosX float,
17 currentPosY float,
18 currentPosZ float);
19
20COMMIT;
diff --git a/OpenSim/Data/SQLite/SQLiteUserData.cs b/OpenSim/Data/SQLite/SQLiteUserData.cs
index 7f1fd62..ce3cf82 100644
--- a/OpenSim/Data/SQLite/SQLiteUserData.cs
+++ b/OpenSim/Data/SQLite/SQLiteUserData.cs
@@ -55,6 +55,7 @@ namespace OpenSim.Data.SQLite
55 55
56 private const string userSelect = "select * from users"; 56 private const string userSelect = "select * from users";
57 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"; 57 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";
58 private const string userAgentSelect = "select * from useragents";
58 59
59 private const string AvatarPickerAndSQL = "select * from users where username like :username and surname like :surname"; 60 private const string AvatarPickerAndSQL = "select * from users where username like :username and surname like :surname";
60 private const string AvatarPickerOrSQL = "select * from users where username like :username or surname like :surname"; 61 private const string AvatarPickerOrSQL = "select * from users where username like :username or surname like :surname";
@@ -63,6 +64,7 @@ namespace OpenSim.Data.SQLite
63 private DataSet ds; 64 private DataSet ds;
64 private SqliteDataAdapter da; 65 private SqliteDataAdapter da;
65 private SqliteDataAdapter daf; 66 private SqliteDataAdapter daf;
67 private SqliteDataAdapter dua;
66 SqliteConnection g_conn; 68 SqliteConnection g_conn;
67 69
68 public override void Initialise() 70 public override void Initialise()
@@ -98,6 +100,7 @@ namespace OpenSim.Data.SQLite
98 100
99 ds = new DataSet(); 101 ds = new DataSet();
100 da = new SqliteDataAdapter(new SqliteCommand(userSelect, conn)); 102 da = new SqliteDataAdapter(new SqliteCommand(userSelect, conn));
103 dua = new SqliteDataAdapter(new SqliteCommand(userAgentSelect, conn));
101 daf = new SqliteDataAdapter(new SqliteCommand(userFriendsSelect, conn)); 104 daf = new SqliteDataAdapter(new SqliteCommand(userFriendsSelect, conn));
102 105
103 lock (ds) 106 lock (ds)
@@ -109,6 +112,9 @@ namespace OpenSim.Data.SQLite
109 setupUserCommands(da, conn); 112 setupUserCommands(da, conn);
110 da.Fill(ds.Tables["users"]); 113 da.Fill(ds.Tables["users"]);
111 114
115 setupAgentCommands(dua, conn);
116 dua.Fill(ds.Tables["useragents"]);
117
112 setupUserFriendsCommands(daf, conn); 118 setupUserFriendsCommands(daf, conn);
113 daf.Fill(ds.Tables["userfriends"]); 119 daf.Fill(ds.Tables["userfriends"]);
114 } 120 }
@@ -132,11 +138,6 @@ namespace OpenSim.Data.SQLite
132 if (row != null) 138 if (row != null)
133 { 139 {
134 UserProfileData user = buildUserProfile(row); 140 UserProfileData user = buildUserProfile(row);
135 row = ds.Tables["useragents"].Rows.Find(Util.ToRawUuidString(uuid));
136 if (row != null)
137 {
138 user.CurrentAgent = buildUserAgent(row);
139 }
140 return user; 141 return user;
141 } 142 }
142 else 143 else
@@ -162,11 +163,6 @@ namespace OpenSim.Data.SQLite
162 if (rows.Length > 0) 163 if (rows.Length > 0)
163 { 164 {
164 UserProfileData user = buildUserProfile(rows[0]); 165 UserProfileData user = buildUserProfile(rows[0]);
165 DataRow row = ds.Tables["useragents"].Rows.Find(Util.ToRawUuidString(user.ID));
166 if (row != null)
167 {
168 user.CurrentAgent = buildUserAgent(row);
169 }
170 return user; 166 return user;
171 } 167 }
172 else 168 else
@@ -356,13 +352,17 @@ namespace OpenSim.Data.SQLite
356 /// <returns>A matching user profile</returns> 352 /// <returns>A matching user profile</returns>
357 override public UserAgentData GetAgentByUUID(UUID uuid) 353 override public UserAgentData GetAgentByUUID(UUID uuid)
358 { 354 {
359 try 355 lock (ds)
360 {
361 return GetUserByUUID(uuid).CurrentAgent;
362 }
363 catch (Exception)
364 { 356 {
365 return null; 357 DataRow row = ds.Tables["useragents"].Rows.Find(Util.ToRawUuidString(uuid));
358 if (row != null)
359 {
360 return buildUserAgent(row);
361 }
362 else
363 {
364 return null;
365 }
366 } 366 }
367 } 367 }
368 368
@@ -384,14 +384,14 @@ namespace OpenSim.Data.SQLite
384 /// <returns>A user agent</returns> 384 /// <returns>A user agent</returns>
385 override public UserAgentData GetAgentByName(string fname, string lname) 385 override public UserAgentData GetAgentByName(string fname, string lname)
386 { 386 {
387 try 387 UserAgentData agent = null;
388 { 388
389 return GetUserByName(fname, lname).CurrentAgent; 389 UserProfileData profile = GetUserByName(fname, lname);
390 } 390 if (profile != null)
391 catch (Exception)
392 { 391 {
393 return null; 392 agent = GetAgentByUUID(profile.ID);
394 } 393 }
394 return agent;
395 } 395 }
396 396
397 /// <summary> 397 /// <summary>
@@ -442,45 +442,7 @@ namespace OpenSim.Data.SQLite
442 fillUserRow(row, user); 442 fillUserRow(row, user);
443 443
444 } 444 }
445 // This is why we're getting the 'logins never log-off'.. because It isn't clearing the 445 m_log.Info("[USER DB]: Syncing user database: " + ds.Tables["users"].Rows.Count + " users stored");
446 // useragents table once the useragent is null
447 //
448 // A database guy should look at this and figure out the best way to clear the useragents table.
449 if (user.CurrentAgent != null)
450 {
451 DataTable ua = ds.Tables["useragents"];
452 row = ua.Rows.Find(Util.ToRawUuidString(user.ID));
453 if (row == null)
454 {
455 row = ua.NewRow();
456 fillUserAgentRow(row, user.CurrentAgent);
457 ua.Rows.Add(row);
458 }
459 else
460 {
461 fillUserAgentRow(row, user.CurrentAgent);
462 }
463 }
464 else
465 {
466 // I just added this to help the standalone login situation.
467 //It still needs to be looked at by a Database guy
468 DataTable ua = ds.Tables["useragents"];
469 row = ua.Rows.Find(Util.ToRawUuidString(user.ID));
470
471 if (row == null)
472 {
473 // do nothing
474 }
475 else
476 {
477 row.Delete();
478 ua.AcceptChanges();
479 }
480 }
481
482 m_log.Info("[USER DB]: " +
483 "Syncing user database: " + ds.Tables["users"].Rows.Count + " users stored");
484 // save changes off to disk 446 // save changes off to disk
485 da.Update(ds, "users"); 447 da.Update(ds, "users");
486 } 448 }
@@ -510,7 +472,26 @@ namespace OpenSim.Data.SQLite
510 /// <param name="agent">The agent to add to the database</param> 472 /// <param name="agent">The agent to add to the database</param>
511 override public void AddNewUserAgent(UserAgentData agent) 473 override public void AddNewUserAgent(UserAgentData agent)
512 { 474 {
513 // Do nothing. yet. 475 DataTable agents = ds.Tables["useragents"];
476 lock (ds)
477 {
478 DataRow row = agents.Rows.Find(Util.ToRawUuidString(agent.ProfileID));
479 if (row == null)
480 {
481 row = agents.NewRow();
482 fillUserAgentRow(row, agent);
483 agents.Rows.Add(row);
484 }
485 else
486 {
487 fillUserAgentRow(row, agent);
488
489 }
490 m_log.Info("[USER DB]: Syncing useragent database: " + ds.Tables["useragents"].Rows.Count + " agents stored");
491 // save changes off to disk
492 dua.Update(ds, "useragents");
493 }
494
514 } 495 }
515 496
516 /// <summary> 497 /// <summary>
@@ -522,7 +503,7 @@ namespace OpenSim.Data.SQLite
522 /// <returns>Success?</returns> 503 /// <returns>Success?</returns>
523 override public bool MoneyTransferRequest(UUID from, UUID to, uint amount) 504 override public bool MoneyTransferRequest(UUID from, UUID to, uint amount)
524 { 505 {
525 return true; 506 return false; // for consistency with the MySQL impl
526 } 507 }
527 508
528 /// <summary> 509 /// <summary>
@@ -535,7 +516,7 @@ namespace OpenSim.Data.SQLite
535 /// <returns>Success?</returns> 516 /// <returns>Success?</returns>
536 override public bool InventoryTransferRequest(UUID from, UUID to, UUID item) 517 override public bool InventoryTransferRequest(UUID from, UUID to, UUID item)
537 { 518 {
538 return true; 519 return false; //for consistency with the MySQL impl
539 } 520 }
540 521
541 522
@@ -814,8 +795,10 @@ namespace OpenSim.Data.SQLite
814 { 795 {
815 UserAgentData ua = new UserAgentData(); 796 UserAgentData ua = new UserAgentData();
816 797
817 ua.ProfileID = new UUID((String) row["UUID"]); 798 UUID tmp;
818 ua.AgentIP = (String) row["agentIP"]; 799 UUID.TryParse((String)row["UUID"], out tmp);
800 ua.ProfileID = tmp;
801 ua.AgentIP = (String)row["agentIP"];
819 ua.AgentPort = Convert.ToUInt32(row["agentPort"]); 802 ua.AgentPort = Convert.ToUInt32(row["agentPort"]);
820 ua.AgentOnline = Convert.ToBoolean(row["agentOnline"]); 803 ua.AgentOnline = Convert.ToBoolean(row["agentOnline"]);
821 ua.SessionID = new UUID((String) row["sessionID"]); 804 ua.SessionID = new UUID((String) row["sessionID"]);
@@ -840,7 +823,7 @@ namespace OpenSim.Data.SQLite
840 /// <param name="ua"></param> 823 /// <param name="ua"></param>
841 private static void fillUserAgentRow(DataRow row, UserAgentData ua) 824 private static void fillUserAgentRow(DataRow row, UserAgentData ua)
842 { 825 {
843 row["UUID"] = ua.ProfileID; 826 row["UUID"] = Util.ToRawUuidString(ua.ProfileID);
844 row["agentIP"] = ua.AgentIP; 827 row["agentIP"] = ua.AgentIP;
845 row["agentPort"] = ua.AgentPort; 828 row["agentPort"] = ua.AgentPort;
846 row["agentOnline"] = ua.AgentOnline; 829 row["agentOnline"] = ua.AgentOnline;
@@ -885,6 +868,20 @@ namespace OpenSim.Data.SQLite
885 da.DeleteCommand = delete; 868 da.DeleteCommand = delete;
886 } 869 }
887 870
871 private void setupAgentCommands(SqliteDataAdapter da, SqliteConnection conn)
872 {
873 da.InsertCommand = SQLiteUtil.createInsertCommand( "useragents", ds.Tables["useragents"]);
874 da.InsertCommand.Connection = conn;
875
876 da.UpdateCommand = SQLiteUtil.createUpdateCommand( "useragents", "UUID=:UUID", ds.Tables["useragents"]);
877 da.UpdateCommand.Connection = conn;
878
879 SqliteCommand delete = new SqliteCommand( "delete from useragents where UUID = :ProfileID");
880 delete.Parameters.Add( SQLiteUtil.createSqliteParameter( "ProfileID", typeof(String)));
881 delete.Connection = conn;
882 da.DeleteCommand = delete;
883 }
884
888 /// <summary> 885 /// <summary>
889 /// 886 ///
890 /// </summary> 887 /// </summary>