aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/SQLite/SQLiteUserData.cs
diff options
context:
space:
mode:
authorHomer Horwitz2009-04-25 20:55:55 +0000
committerHomer Horwitz2009-04-25 20:55:55 +0000
commit94ab683fe13e833def72c4c41a6b570608a6427a (patch)
treeb27b7a17cf2a3deac7545b11eecc1c742be8b29a /OpenSim/Data/SQLite/SQLiteUserData.cs
parentThank you kindly, RemedyTomm, for a patch that fixes: (diff)
downloadopensim-SC_OLD-94ab683fe13e833def72c4c41a6b570608a6427a.zip
opensim-SC_OLD-94ab683fe13e833def72c4c41a6b570608a6427a.tar.gz
opensim-SC_OLD-94ab683fe13e833def72c4c41a6b570608a6427a.tar.bz2
opensim-SC_OLD-94ab683fe13e833def72c4c41a6b570608a6427a.tar.xz
Thanks, Ewe Loon for a patch that
provides persistent AvatarAppearance for SQLite. Fixes Mantis #3296.
Diffstat (limited to '')
-rw-r--r--OpenSim/Data/SQLite/SQLiteUserData.cs256
1 files changed, 241 insertions, 15 deletions
diff --git a/OpenSim/Data/SQLite/SQLiteUserData.cs b/OpenSim/Data/SQLite/SQLiteUserData.cs
index 5f9f979..9485340 100644
--- a/OpenSim/Data/SQLite/SQLiteUserData.cs
+++ b/OpenSim/Data/SQLite/SQLiteUserData.cs
@@ -56,6 +56,7 @@ namespace OpenSim.Data.SQLite
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 private const string userAgentSelect = "select * from useragents";
59 private const string AvatarAppearanceSelect = "select * from avatarappearance";
59 60
60 private const string AvatarPickerAndSQL = "select * from users where username like :username and surname like :surname"; 61 private const string AvatarPickerAndSQL = "select * from users where username like :username and surname like :surname";
61 private const string AvatarPickerOrSQL = "select * from users where username like :username or surname like :surname"; 62 private const string AvatarPickerOrSQL = "select * from users where username like :username or surname like :surname";
@@ -65,6 +66,7 @@ namespace OpenSim.Data.SQLite
65 private SqliteDataAdapter da; 66 private SqliteDataAdapter da;
66 private SqliteDataAdapter daf; 67 private SqliteDataAdapter daf;
67 private SqliteDataAdapter dua; 68 private SqliteDataAdapter dua;
69 private SqliteDataAdapter daa;
68 SqliteConnection g_conn; 70 SqliteConnection g_conn;
69 71
70 public override void Initialise() 72 public override void Initialise()
@@ -102,12 +104,15 @@ namespace OpenSim.Data.SQLite
102 da = new SqliteDataAdapter(new SqliteCommand(userSelect, conn)); 104 da = new SqliteDataAdapter(new SqliteCommand(userSelect, conn));
103 dua = new SqliteDataAdapter(new SqliteCommand(userAgentSelect, conn)); 105 dua = new SqliteDataAdapter(new SqliteCommand(userAgentSelect, conn));
104 daf = new SqliteDataAdapter(new SqliteCommand(userFriendsSelect, conn)); 106 daf = new SqliteDataAdapter(new SqliteCommand(userFriendsSelect, conn));
107 daa = new SqliteDataAdapter(new SqliteCommand(AvatarAppearanceSelect, conn));
108 //if (daa == null) m_log.Info("[SQLiteUserData]: daa = null");
105 109
106 lock (ds) 110 lock (ds)
107 { 111 {
108 ds.Tables.Add(createUsersTable()); 112 ds.Tables.Add(createUsersTable());
109 ds.Tables.Add(createUserAgentsTable()); 113 ds.Tables.Add(createUserAgentsTable());
110 ds.Tables.Add(createUserFriendsTable()); 114 ds.Tables.Add(createUserFriendsTable());
115 ds.Tables.Add(createAvatarAppearanceTable());
111 116
112 setupUserCommands(da, conn); 117 setupUserCommands(da, conn);
113 da.Fill(ds.Tables["users"]); 118 da.Fill(ds.Tables["users"]);
@@ -117,6 +122,9 @@ namespace OpenSim.Data.SQLite
117 122
118 setupUserFriendsCommands(daf, conn); 123 setupUserFriendsCommands(daf, conn);
119 daf.Fill(ds.Tables["userfriends"]); 124 daf.Fill(ds.Tables["userfriends"]);
125
126 setupAvatarAppearanceCommands(daa, conn);
127 daa.Fill(ds.Tables["avatarappearance"]);
120 } 128 }
121 129
122 return; 130 return;
@@ -149,6 +157,11 @@ namespace OpenSim.Data.SQLite
149 dua.Dispose(); 157 dua.Dispose();
150 dua = null; 158 dua = null;
151 } 159 }
160 if (daa != null)
161 {
162 daa.Dispose();
163 daa = null;
164 }
152 aplist = null; 165 aplist = null;
153 } 166 }
154 167
@@ -199,9 +212,9 @@ namespace OpenSim.Data.SQLite
199 } 212 }
200 } 213 }
201 } 214 }
202 215
203 #region User Friends List Data 216 #region User Friends List Data
204 217
205 private bool ExistsFriend(UUID owner, UUID friend) 218 private bool ExistsFriend(UUID owner, UUID friend)
206 { 219 {
207 string FindFriends = "select * from userfriends where (ownerID=:ownerID and friendID=:friendID) or (ownerID=:friendID and friendID=:ownerID)"; 220 string FindFriends = "select * from userfriends where (ownerID=:ownerID and friendID=:friendID) or (ownerID=:friendID and friendID=:ownerID)";
@@ -242,7 +255,7 @@ namespace OpenSim.Data.SQLite
242 { 255 {
243 if (ExistsFriend(friendlistowner, friend)) 256 if (ExistsFriend(friendlistowner, friend))
244 return; 257 return;
245 258
246 string InsertFriends = "insert into userfriends(ownerID, friendID, friendPerms) values(:ownerID, :friendID, :perms)"; 259 string InsertFriends = "insert into userfriends(ownerID, friendID, friendPerms) values(:ownerID, :friendID, :perms)";
247 using (SqliteCommand cmd = new SqliteCommand(InsertFriends, g_conn)) 260 using (SqliteCommand cmd = new SqliteCommand(InsertFriends, g_conn))
248 { 261 {
@@ -528,7 +541,7 @@ namespace OpenSim.Data.SQLite
528 UUID zero = UUID.Zero; 541 UUID zero = UUID.Zero;
529 if (ExistsFirstLastName(user.FirstName, user.SurName) || user.ID == zero) 542 if (ExistsFirstLastName(user.FirstName, user.SurName) || user.ID == zero)
530 return; 543 return;
531 544
532 lock (ds) 545 lock (ds)
533 { 546 {
534 DataRow row = users.Rows.Find(user.ID.ToString()); 547 DataRow row = users.Rows.Find(user.ID.ToString());
@@ -537,9 +550,9 @@ namespace OpenSim.Data.SQLite
537 row = users.NewRow(); 550 row = users.NewRow();
538 fillUserRow(row, user); 551 fillUserRow(row, user);
539 users.Rows.Add(row); 552 users.Rows.Add(row);
540 553
541 m_log.Debug("[USER DB]: Syncing user database: " + ds.Tables["users"].Rows.Count + " users stored"); 554 m_log.Debug("[USER DB]: Syncing user database: " + ds.Tables["users"].Rows.Count + " users stored");
542 555
543 // save changes off to disk 556 // save changes off to disk
544 da.Update(ds, "users"); 557 da.Update(ds, "users");
545 } 558 }
@@ -571,7 +584,7 @@ namespace OpenSim.Data.SQLite
571 da.Update(ds, "users"); 584 da.Update(ds, "users");
572 } 585 }
573 } 586 }
574 587
575 //AddNewUserProfile(user); 588 //AddNewUserProfile(user);
576 return true; 589 return true;
577 } 590 }
@@ -641,13 +654,83 @@ namespace OpenSim.Data.SQLite
641 /// <returns>Avatar Appearence</returns> 654 /// <returns>Avatar Appearence</returns>
642 override public AvatarAppearance GetUserAppearance(UUID user) 655 override public AvatarAppearance GetUserAppearance(UUID user)
643 { 656 {
644 AvatarAppearance aa = null; 657 m_log.Info("[APPEARANCE] GetUserAppearance " + user.ToString());
645 try { 658
646 aa = aplist[user]; 659 AvatarAppearance aa = new AvatarAppearance(user);
647 m_log.Info("[APPEARANCE] Found appearance for " + user.ToString() + aa.ToString()); 660 //try {
648 } catch (KeyNotFoundException) { 661 aa.Owner = user;
649 m_log.InfoFormat("[APPEARANCE] No appearance found for {0}", user.ToString()); 662 //aplist[user] = appearance;
663
664 DataTable aap = ds.Tables["avatarappearance"];
665 lock (ds)
666 {
667 DataRow row = aap.Rows.Find(Util.ToRawUuidString(user));
668 if (row == null)
669 {
670 m_log.Info("[APPEARANCE] Could not find appearance for " + user.ToString());
671
672 //m_log.Debug("[USER DB]: Creating avatarappearance For: " + user.ToString());
673
674 //row = aap.NewRow();
675 //fillAvatarAppearanceRow(row, user, appearance);
676 //aap.Rows.Add(row);
677 // m_log.Debug("[USER DB]: Syncing user database: " + ds.Tables["users"].Rows.Count + " users stored");
678 // save changes off to disk
679 //daa.Update(ds, "avatarappearance");
680 }
681 else
682 {
683 m_log.InfoFormat("[APPEARANCE] appearance found for {0}", user.ToString());
684
685 aa.BodyAsset = new UUID((String)row["BodyAsset"]);
686 aa.BodyItem = new UUID((String)row["BodyItem"]);
687 aa.SkinItem = new UUID((String)row["SkinItem"]);
688 aa.SkinAsset = new UUID((String)row["SkinAsset"]);
689 aa.HairItem = new UUID((String)row["HairItem"]);
690 aa.HairAsset = new UUID((String)row["HairAsset"]);
691 aa.EyesItem = new UUID((String)row["EyesItem"]);
692 aa.EyesAsset = new UUID((String)row["EyesAsset"]);
693 aa.ShirtItem = new UUID((String)row["ShirtItem"]);
694 aa.ShirtAsset = new UUID((String)row["ShirtAsset"]);
695 aa.PantsItem = new UUID((String)row["PantsItem"]);
696 aa.PantsAsset = new UUID((String)row["PantsAsset"]);
697 aa.ShoesItem = new UUID((String)row["ShoesItem"]);
698 aa.ShoesAsset = new UUID((String)row["ShoesAsset"]);
699 aa.SocksItem = new UUID((String)row["SocksItem"]);
700 aa.SocksAsset = new UUID((String)row["SocksAsset"]);
701 aa.JacketItem = new UUID((String)row["JacketItem"]);
702 aa.JacketAsset = new UUID((String)row["JacketAsset"]);
703 aa.GlovesItem = new UUID((String)row["GlovesItem"]);
704 aa.GlovesAsset = new UUID((String)row["GlovesAsset"]);
705 aa.UnderShirtItem = new UUID((String)row["UnderShirtItem"]);
706 aa.UnderShirtAsset = new UUID((String)row["UnderShirtAsset"]);
707 aa.UnderPantsItem = new UUID((String)row["UnderPantsItem"]);
708 aa.UnderPantsAsset = new UUID((String)row["UnderPantsAsset"]);
709 aa.SkirtItem = new UUID((String)row["SkirtItem"]);
710 aa.SkirtAsset = new UUID((String)row["SkirtAsset"]);
711
712 // Ewe Loon
713 // Used Base64String because for some reason it wont accept using Byte[] (which works in Region date)
714
715 String str = (String)row["Texture"];
716 byte[] texture = Convert.FromBase64String(str);
717 aa.Texture = new Primitive.TextureEntry(texture, 0, texture.Length);
718
719 str = (String)row["VisualParams"];
720 byte[] VisualParams = Convert.FromBase64String(str);
721 aa.VisualParams = VisualParams;
722
723 aa.Serial = Convert.ToInt32(row["Serial"]);
724 aa.AvatarHeight = Convert.ToSingle(row["AvatarHeight"]);
725 m_log.InfoFormat("[APPEARANCE] appearance set for {0}", user.ToString());
726 }
650 } 727 }
728
729 // aa = aplist[user];
730 // m_log.Info("[APPEARANCE] Found appearance for " + user.ToString() + aa.ToString());
731 // } catch (KeyNotFoundException) {
732 // m_log.InfoFormat("[APPEARANCE] No appearance found for {0}", user.ToString());
733 // }
651 return aa; 734 return aa;
652 } 735 }
653 736
@@ -659,7 +742,29 @@ namespace OpenSim.Data.SQLite
659 override public void UpdateUserAppearance(UUID user, AvatarAppearance appearance) 742 override public void UpdateUserAppearance(UUID user, AvatarAppearance appearance)
660 { 743 {
661 appearance.Owner = user; 744 appearance.Owner = user;
662 aplist[user] = appearance; 745 //aplist[user] = appearance;
746 DataTable aap = ds.Tables["avatarappearance"];
747 lock (ds)
748 {
749 DataRow row = aap.Rows.Find(Util.ToRawUuidString(user));
750 if (row == null)
751 {
752 m_log.Debug("[USER DB]: Creating UserAppearance For: " + user.ToString());
753
754 row = aap.NewRow();
755 fillAvatarAppearanceRow(row, user, appearance);
756 aap.Rows.Add(row);
757 // m_log.Debug("[USER DB]: Syncing user database: " + ds.Tables["users"].Rows.Count + " users stored");
758 // save changes off to disk
759 daa.Update(ds, "avatarappearance");
760 }
761 else
762 {
763 m_log.Debug("[USER DB]: Updating UserAppearance For: " + user.ToString());
764 fillAvatarAppearanceRow(row, user, appearance);
765 daa.Update(ds, "avatarappearance");
766 }
767 }
663 } 768 }
664 769
665 /// <summary> 770 /// <summary>
@@ -784,6 +889,55 @@ namespace OpenSim.Data.SQLite
784 return ua; 889 return ua;
785 } 890 }
786 891
892 /// <summary>
893 /// Create the "avatarappearance" table
894 /// </summary>
895 /// <returns>Data Table</returns>
896 private static DataTable createAvatarAppearanceTable()
897 {
898 DataTable aa = new DataTable("avatarappearance");
899 // table contains user appearance items
900
901 SQLiteUtil.createCol(aa, "Owner", typeof(String));
902 SQLiteUtil.createCol(aa, "BodyItem", typeof(String));
903 SQLiteUtil.createCol(aa, "BodyAsset", typeof(String));
904 SQLiteUtil.createCol(aa, "SkinItem", typeof(String));
905 SQLiteUtil.createCol(aa, "SkinAsset", typeof(String));
906 SQLiteUtil.createCol(aa, "HairItem", typeof(String));
907 SQLiteUtil.createCol(aa, "HairAsset", typeof(String));
908 SQLiteUtil.createCol(aa, "EyesItem", typeof(String));
909 SQLiteUtil.createCol(aa, "EyesAsset", typeof(String));
910 SQLiteUtil.createCol(aa, "ShirtItem", typeof(String));
911 SQLiteUtil.createCol(aa, "ShirtAsset", typeof(String));
912 SQLiteUtil.createCol(aa, "PantsItem", typeof(String));
913 SQLiteUtil.createCol(aa, "PantsAsset", typeof(String));
914 SQLiteUtil.createCol(aa, "ShoesItem", typeof(String));
915 SQLiteUtil.createCol(aa, "ShoesAsset", typeof(String));
916 SQLiteUtil.createCol(aa, "SocksItem", typeof(String));
917 SQLiteUtil.createCol(aa, "SocksAsset", typeof(String));
918 SQLiteUtil.createCol(aa, "JacketItem", typeof(String));
919 SQLiteUtil.createCol(aa, "JacketAsset", typeof(String));
920 SQLiteUtil.createCol(aa, "GlovesItem", typeof(String));
921 SQLiteUtil.createCol(aa, "GlovesAsset", typeof(String));
922 SQLiteUtil.createCol(aa, "UnderShirtItem", typeof(String));
923 SQLiteUtil.createCol(aa, "UnderShirtAsset", typeof(String));
924 SQLiteUtil.createCol(aa, "UnderPantsItem", typeof(String));
925 SQLiteUtil.createCol(aa, "UnderPantsAsset", typeof(String));
926 SQLiteUtil.createCol(aa, "SkirtItem", typeof(String));
927 SQLiteUtil.createCol(aa, "SkirtAsset", typeof(String));
928
929 // Used Base64String because for some reason it wont accept using Byte[] (which works in Region date)
930 SQLiteUtil.createCol(aa, "Texture", typeof (String));
931 SQLiteUtil.createCol(aa, "VisualParams", typeof (String));
932
933 SQLiteUtil.createCol(aa, "Serial", typeof(Int32));
934 SQLiteUtil.createCol(aa, "AvatarHeight", typeof(Double));
935
936 aa.PrimaryKey = new DataColumn[] { aa.Columns["Owner"] };
937
938 return aa;
939 }
940
787 /*********************************************************************** 941 /***********************************************************************
788 * 942 *
789 * Convert between ADO.NET <=> OpenSim Objects 943 * Convert between ADO.NET <=> OpenSim Objects
@@ -906,6 +1060,58 @@ namespace OpenSim.Data.SQLite
906 /// 1060 ///
907 /// </summary> 1061 /// </summary>
908 /// <param name="row"></param> 1062 /// <param name="row"></param>
1063 /// <param name="user"></param>
1064 private void fillAvatarAppearanceRow(DataRow row, UUID user, AvatarAppearance appearance)
1065 {
1066 row["Owner"] = Util.ToRawUuidString(user);
1067 row["BodyItem"] = appearance.BodyItem.ToString();
1068 row["BodyAsset"] = appearance.BodyAsset.ToString();
1069 row["SkinItem"] = appearance.SkinItem.ToString();
1070 row["SkinAsset"] = appearance.SkinAsset.ToString();
1071 row["HairItem"] = appearance.HairItem.ToString();
1072 row["HairAsset"] = appearance.HairAsset.ToString();
1073 row["EyesItem"] = appearance.EyesItem.ToString();
1074 row["EyesAsset"] = appearance.EyesAsset.ToString();
1075 row["ShirtItem"] = appearance.ShirtItem.ToString();
1076 row["ShirtAsset"] = appearance.ShirtAsset.ToString();
1077 row["PantsItem"] = appearance.PantsItem.ToString();
1078 row["PantsAsset"] = appearance.PantsAsset.ToString();
1079 row["ShoesItem"] = appearance.ShoesItem.ToString();
1080 row["ShoesAsset"] = appearance.ShoesAsset.ToString();
1081 row["SocksItem"] = appearance.SocksItem.ToString();
1082 row["SocksAsset"] = appearance.SocksAsset.ToString();
1083 row["JacketItem"] = appearance.JacketItem.ToString();
1084 row["JacketAsset"] = appearance.JacketAsset.ToString();
1085 row["GlovesItem"] = appearance.GlovesItem.ToString();
1086 row["GlovesAsset"] = appearance.GlovesAsset.ToString();
1087 row["UnderShirtItem"] = appearance.UnderShirtItem.ToString();
1088 row["UnderShirtAsset"] = appearance.UnderShirtAsset.ToString();
1089 row["UnderPantsItem"] = appearance.UnderPantsItem.ToString();
1090 row["UnderPantsAsset"] = appearance.UnderPantsAsset.ToString();
1091 row["SkirtItem"] = appearance.SkirtItem.ToString();
1092 row["SkirtAsset"] = appearance.SkirtAsset.ToString();
1093
1094 // Used Base64String because for some reason it wont accept using Byte[] (which works in Region date)
1095 row["Texture"] = Convert.ToBase64String(appearance.Texture.GetBytes());
1096 row["VisualParams"] = Convert.ToBase64String(appearance.VisualParams);
1097
1098 row["Serial"] = appearance.Serial;
1099 row["AvatarHeight"] = appearance.AvatarHeight;
1100
1101 // ADO.NET doesn't handle NULL very well
1102 foreach (DataColumn col in ds.Tables["avatarappearance"].Columns)
1103 {
1104 if (row[col] == null)
1105 {
1106 row[col] = String.Empty;
1107 }
1108 }
1109 }
1110
1111 /// <summary>
1112 ///
1113 /// </summary>
1114 /// <param name="row"></param>
909 /// <returns></returns> 1115 /// <returns></returns>
910 private static UserAgentData buildUserAgent(DataRow row) 1116 private static UserAgentData buildUserAgent(DataRow row)
911 { 1117 {
@@ -996,7 +1202,7 @@ namespace OpenSim.Data.SQLite
996 { 1202 {
997 da.InsertCommand = SQLiteUtil.createInsertCommand( "useragents", ds.Tables["useragents"]); 1203 da.InsertCommand = SQLiteUtil.createInsertCommand( "useragents", ds.Tables["useragents"]);
998 da.InsertCommand.Connection = conn; 1204 da.InsertCommand.Connection = conn;
999 1205
1000 da.UpdateCommand = SQLiteUtil.createUpdateCommand( "useragents", "UUID=:UUID", ds.Tables["useragents"]); 1206 da.UpdateCommand = SQLiteUtil.createUpdateCommand( "useragents", "UUID=:UUID", ds.Tables["useragents"]);
1001 da.UpdateCommand.Connection = conn; 1207 da.UpdateCommand.Connection = conn;
1002 1208
@@ -1027,6 +1233,26 @@ namespace OpenSim.Data.SQLite
1027 1233
1028 } 1234 }
1029 1235
1236 /// <summary>
1237 ///
1238 /// </summary>
1239 /// <param name="daf"></param>
1240 /// <param name="conn"></param>
1241 private void setupAvatarAppearanceCommands(SqliteDataAdapter daa, SqliteConnection conn)
1242 {
1243 daa.InsertCommand = SQLiteUtil.createInsertCommand("avatarappearance", ds.Tables["avatarappearance"]);
1244 daa.InsertCommand.Connection = conn;
1245
1246 daa.UpdateCommand = SQLiteUtil.createUpdateCommand("avatarappearance", "Owner=:Owner", ds.Tables["avatarappearance"]);
1247 daa.UpdateCommand.Connection = conn;
1248
1249 SqliteCommand delete = new SqliteCommand("delete from avatarappearance where Owner=:Owner");
1250 delete.Parameters.Add(SQLiteUtil.createSqliteParameter("Owner", typeof(String)));
1251 delete.Connection = conn;
1252 daa.DeleteCommand = delete;
1253 }
1254
1255
1030 override public void ResetAttachments(UUID userID) 1256 override public void ResetAttachments(UUID userID)
1031 { 1257 {
1032 } 1258 }