aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/MSSQL/MSSQLUserAccountData.cs
diff options
context:
space:
mode:
Diffstat (limited to 'OpenSim/Data/MSSQL/MSSQLUserAccountData.cs')
-rw-r--r--OpenSim/Data/MSSQL/MSSQLUserAccountData.cs306
1 files changed, 180 insertions, 126 deletions
diff --git a/OpenSim/Data/MSSQL/MSSQLUserAccountData.cs b/OpenSim/Data/MSSQL/MSSQLUserAccountData.cs
index 2d92cb1..9f18e5e 100644
--- a/OpenSim/Data/MSSQL/MSSQLUserAccountData.cs
+++ b/OpenSim/Data/MSSQL/MSSQLUserAccountData.cs
@@ -36,153 +36,207 @@ using System.Text;
36 36
37namespace OpenSim.Data.MSSQL 37namespace OpenSim.Data.MSSQL
38{ 38{
39 public class MSSQLUserAccountData : IUserAccountData 39 public class MSSQLUserAccountData : MSSQLGenericTableHandler<UserAccountData>,IUserAccountData
40 { 40 {
41 private string m_Realm; 41 public MSSQLUserAccountData(string connectionString, string realm) :
42 private List<string> m_ColumnNames = null; 42 base(connectionString, realm, "UserAccount")
43 private string m_ConnectionString;
44 private MSSQLManager m_database;
45
46 public MSSQLUserAccountData(string connectionString, string realm)
47 {
48 m_Realm = realm;
49 m_ConnectionString = connectionString;
50 m_database = new MSSQLManager(connectionString);
51
52 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
53 {
54 conn.Open();
55 Migration m = new Migration(conn, GetType().Assembly, "UserStore");
56 m.Update();
57 }
58 }
59
60 public List<UserAccountData> Query(UUID principalID, UUID scopeID, string query)
61 { 43 {
62 return null;
63 } 44 }
64 45 //private string m_Realm;
65 public UserAccountData Get(UUID principalID, UUID scopeID) 46 //private List<string> m_ColumnNames = null;
47 //private MSSQLManager m_database;
48
49 //public MSSQLUserAccountData(string connectionString, string realm)
50 //{
51 // m_Realm = realm;
52 // m_ConnectionString = connectionString;
53 // m_database = new MSSQLManager(connectionString);
54
55 // using (SqlConnection conn = new SqlConnection(m_ConnectionString))
56 // {
57 // conn.Open();
58 // Migration m = new Migration(conn, GetType().Assembly, "UserStore");
59 // m.Update();
60 // }
61 //}
62
63 //public List<UserAccountData> Query(UUID principalID, UUID scopeID, string query)
64 //{
65 // return null;
66 //}
67
68 //public UserAccountData Get(UUID principalID, UUID scopeID)
69 //{
70 // UserAccountData ret = new UserAccountData();
71 // ret.Data = new Dictionary<string, string>();
72
73 // string sql = string.Format("select * from {0} where UUID = @principalID", m_Realm);
74 // if (scopeID != UUID.Zero)
75 // sql += " and ScopeID = @scopeID";
76
77 // using (SqlConnection conn = new SqlConnection(m_ConnectionString))
78 // using (SqlCommand cmd = new SqlCommand(sql, conn))
79 // {
80 // cmd.Parameters.Add(m_database.CreateParameter("@principalID", principalID));
81 // cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID));
82
83 // conn.Open();
84 // using (SqlDataReader result = cmd.ExecuteReader())
85 // {
86 // if (result.Read())
87 // {
88 // ret.PrincipalID = principalID;
89 // UUID scope;
90 // UUID.TryParse(result["ScopeID"].ToString(), out scope);
91 // ret.ScopeID = scope;
92
93 // if (m_ColumnNames == null)
94 // {
95 // m_ColumnNames = new List<string>();
96
97 // DataTable schemaTable = result.GetSchemaTable();
98 // foreach (DataRow row in schemaTable.Rows)
99 // m_ColumnNames.Add(row["ColumnName"].ToString());
100 // }
101
102 // foreach (string s in m_ColumnNames)
103 // {
104 // if (s == "UUID")
105 // continue;
106 // if (s == "ScopeID")
107 // continue;
108
109 // ret.Data[s] = result[s].ToString();
110 // }
111 // return ret;
112 // }
113 // }
114 // }
115 // return null;
116 //}
117
118 //public bool Store(UserAccountData data)
119 //{
120 // if (data.Data.ContainsKey("UUID"))
121 // data.Data.Remove("UUID");
122 // if (data.Data.ContainsKey("ScopeID"))
123 // data.Data.Remove("ScopeID");
124
125 // string[] fields = new List<string>(data.Data.Keys).ToArray();
126
127 // using (SqlConnection conn = new SqlConnection(m_ConnectionString))
128 // using (SqlCommand cmd = new SqlCommand())
129 // {
130 // StringBuilder updateBuilder = new StringBuilder();
131 // updateBuilder.AppendFormat("update {0} set ", m_Realm);
132 // bool first = true;
133 // foreach (string field in fields)
134 // {
135 // if (!first)
136 // updateBuilder.Append(", ");
137 // updateBuilder.AppendFormat("{0} = @{0}", field);
138
139 // first = false;
140 // cmd.Parameters.Add(m_database.CreateParameter("@" + field, data.Data[field]));
141 // }
142
143 // updateBuilder.Append(" where UUID = @principalID");
144
145 // if (data.ScopeID != UUID.Zero)
146 // updateBuilder.Append(" and ScopeID = @scopeID");
147
148 // cmd.CommandText = updateBuilder.ToString();
149 // cmd.Connection = conn;
150 // cmd.Parameters.Add(m_database.CreateParameter("@principalID", data.PrincipalID));
151 // cmd.Parameters.Add(m_database.CreateParameter("@scopeID", data.ScopeID));
152 // conn.Open();
153
154 // if (cmd.ExecuteNonQuery() < 1)
155 // {
156 // StringBuilder insertBuilder = new StringBuilder();
157 // insertBuilder.AppendFormat("insert into {0} (UUID, ScopeID, ", m_Realm);
158 // insertBuilder.Append(String.Join(", ", fields));
159 // insertBuilder.Append(") values (@principalID, @scopeID, @");
160 // insertBuilder.Append(String.Join(", @", fields));
161 // insertBuilder.Append(")");
162
163 // cmd.CommandText = insertBuilder.ToString();
164
165 // if (cmd.ExecuteNonQuery() < 1)
166 // {
167 // return false;
168 // }
169 // }
170 // }
171 // return true;
172 //}
173
174 //public bool Store(UserAccountData data, UUID principalID, string token)
175 //{
176 // return false;
177 //}
178
179 //public bool SetDataItem(UUID principalID, string item, string value)
180 //{
181 // string sql = string.Format("update {0} set {1} = @{1} where UUID = @UUID", m_Realm, item);
182 // using (SqlConnection conn = new SqlConnection(m_ConnectionString))
183 // using (SqlCommand cmd = new SqlCommand(sql, conn))
184 // {
185 // cmd.Parameters.Add(m_database.CreateParameter("@" + item, value));
186 // cmd.Parameters.Add(m_database.CreateParameter("@UUID", principalID));
187
188 // conn.Open();
189
190 // if (cmd.ExecuteNonQuery() > 0)
191 // return true;
192 // }
193 // return false;
194 //}
195
196 //public UserAccountData[] Get(string[] keys, string[] vals)
197 //{
198 // return null;
199 //}
200
201 public UserAccountData[] GetUsers(UUID scopeID, string query)
66 { 202 {
67 UserAccountData ret = new UserAccountData(); 203 string[] words = query.Split(new char[] { ' ' });
68 ret.Data = new Dictionary<string, object>();
69
70 string sql = string.Format("select * from {0} where UUID = @principalID", m_Realm);
71 if (scopeID != UUID.Zero)
72 sql += " and ScopeID = @scopeID";
73 204
74 using (SqlConnection conn = new SqlConnection(m_ConnectionString)) 205 for (int i = 0; i < words.Length; i++)
75 using (SqlCommand cmd = new SqlCommand(sql, conn))
76 { 206 {
77 cmd.Parameters.Add(m_database.CreateParameter("@principalID", principalID)); 207 if (words[i].Length < 3)
78 cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID));
79
80 conn.Open();
81 using (SqlDataReader result = cmd.ExecuteReader())
82 { 208 {
83 if (result.Read()) 209 if (i != words.Length - 1)
84 { 210 Array.Copy(words, i + 1, words, i, words.Length - i - 1);
85 ret.PrincipalID = principalID; 211 Array.Resize(ref words, words.Length - 1);
86 UUID scope;
87 UUID.TryParse(result["ScopeID"].ToString(), out scope);
88 ret.ScopeID = scope;
89
90 if (m_ColumnNames == null)
91 {
92 m_ColumnNames = new List<string>();
93
94 DataTable schemaTable = result.GetSchemaTable();
95 foreach (DataRow row in schemaTable.Rows)
96 m_ColumnNames.Add(row["ColumnName"].ToString());
97 }
98
99 foreach (string s in m_ColumnNames)
100 {
101 if (s == "UUID")
102 continue;
103 if (s == "ScopeID")
104 continue;
105
106 ret.Data[s] = result[s].ToString();
107 }
108 return ret;
109 }
110 } 212 }
111 } 213 }
112 return null;
113 }
114 214
115 public bool Store(UserAccountData data) 215 if (words.Length == 0)
116 { 216 return new UserAccountData[0];
117 if (data.Data.ContainsKey("UUID"))
118 data.Data.Remove("UUID");
119 if (data.Data.ContainsKey("ScopeID"))
120 data.Data.Remove("ScopeID");
121 217
122 string[] fields = new List<string>(data.Data.Keys).ToArray(); 218 if (words.Length > 2)
219 return new UserAccountData[0];
123 220
124 using (SqlConnection conn = new SqlConnection(m_ConnectionString)) 221 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
125 using (SqlCommand cmd = new SqlCommand()) 222 using (SqlCommand cmd = new SqlCommand())
126 { 223 {
127 StringBuilder updateBuilder = new StringBuilder(); 224 if (words.Length == 1)
128 updateBuilder.AppendFormat("update {0} set ", m_Realm);
129 bool first = true;
130 foreach (string field in fields)
131 { 225 {
132 if (!first) 226 cmd.CommandText = String.Format("select * from {0} where ([ScopeID]=@ScopeID or [ScopeID]='00000000-0000-0000-0000-000000000000') and ([FirstName] like @search or [LastName] like @search)", m_Realm);
133 updateBuilder.Append(", "); 227 cmd.Parameters.Add(m_database.CreateParameter("@scopeID", scopeID));
134 updateBuilder.AppendFormat("{0} = @{0}", field); 228 cmd.Parameters.Add(m_database.CreateParameter("@search", "%" + words[0] + "%"));
135
136 first = false;
137 cmd.Parameters.Add(m_database.CreateParameter("@" + field, data.Data[field]));
138 } 229 }
139 230 else
140 updateBuilder.Append(" where UUID = @principalID");
141
142 if (data.ScopeID != UUID.Zero)
143 updateBuilder.Append(" and ScopeID = @scopeID");
144
145 cmd.CommandText = updateBuilder.ToString();
146 cmd.Connection = conn;
147 cmd.Parameters.Add(m_database.CreateParameter("@principalID", data.PrincipalID));
148 cmd.Parameters.Add(m_database.CreateParameter("@scopeID", data.ScopeID));
149 conn.Open();
150
151 if (cmd.ExecuteNonQuery() < 1)
152 { 231 {
153 StringBuilder insertBuilder = new StringBuilder(); 232 cmd.CommandText = String.Format("select * from {0} where ([ScopeID]=@ScopeID or [ScopeID]='00000000-0000-0000-0000-000000000000') and ([FirstName] like @searchFirst or [LastName] like @searchLast)", m_Realm);
154 insertBuilder.AppendFormat("insert into {0} (UUID, ScopeID, ", m_Realm); 233 cmd.Parameters.Add(m_database.CreateParameter("@searchFirst", "%" + words[0] + "%"));
155 insertBuilder.Append(String.Join(", ", fields)); 234 cmd.Parameters.Add(m_database.CreateParameter("@searchLast", "%" + words[1] + "%"));
156 insertBuilder.Append(") values (@principalID, @scopeID, @"); 235 cmd.Parameters.Add(m_database.CreateParameter("@ScopeID", scopeID.ToString()));
157 insertBuilder.Append(String.Join(", @", fields));
158 insertBuilder.Append(")");
159
160 cmd.CommandText = insertBuilder.ToString();
161
162 if (cmd.ExecuteNonQuery() < 1)
163 {
164 return false;
165 }
166 } 236 }
167 }
168 return true;
169 }
170
171 public bool SetDataItem(UUID principalID, string item, string value)
172 {
173 string sql = string.Format("update {0} set {1} = @{1} where UUID = @UUID", m_Realm, item);
174 using (SqlConnection conn = new SqlConnection(m_ConnectionString))
175 using (SqlCommand cmd = new SqlCommand(sql, conn))
176 {
177 cmd.Parameters.Add(m_database.CreateParameter("@" + item, value));
178 cmd.Parameters.Add(m_database.CreateParameter("@UUID", principalID));
179
180 conn.Open();
181 237
182 if (cmd.ExecuteNonQuery() > 0) 238 return DoQuery(cmd);
183 return true;
184 } 239 }
185 return false;
186 } 240 }
187 } 241 }
188} 242}