diff options
Diffstat (limited to '')
-rw-r--r-- | OpenSim/Data/MSSQL/MSSQLAuthenticationData.cs | 222 | ||||
-rw-r--r-- | OpenSim/Data/MSSQL/MSSQLUserAccountData.cs | 186 | ||||
-rw-r--r-- | OpenSim/Data/MSSQL/Resources/011_UserStore.sql | 5 |
3 files changed, 413 insertions, 0 deletions
diff --git a/OpenSim/Data/MSSQL/MSSQLAuthenticationData.cs b/OpenSim/Data/MSSQL/MSSQLAuthenticationData.cs new file mode 100644 index 0000000..78fc22c --- /dev/null +++ b/OpenSim/Data/MSSQL/MSSQLAuthenticationData.cs | |||
@@ -0,0 +1,222 @@ | |||
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 OpenSimulator 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 | |||
28 | using System; | ||
29 | using System.Collections; | ||
30 | using System.Collections.Generic; | ||
31 | using System.Data; | ||
32 | using OpenMetaverse; | ||
33 | using OpenSim.Framework; | ||
34 | using System.Data.SqlClient; | ||
35 | using System.Reflection; | ||
36 | using System.Text; | ||
37 | |||
38 | namespace OpenSim.Data.MSSQL | ||
39 | { | ||
40 | public class MSSQLAuthenticationData : IAuthenticationData | ||
41 | { | ||
42 | private string m_Realm; | ||
43 | private List<string> m_ColumnNames = null; | ||
44 | private int m_LastExpire = 0; | ||
45 | private string m_ConnectionString; | ||
46 | |||
47 | public MSSQLAuthenticationData(string connectionString, string realm) | ||
48 | { | ||
49 | m_Realm = realm; | ||
50 | m_ConnectionString = connectionString; | ||
51 | using (SqlConnection conn = new SqlConnection(m_ConnectionString)) | ||
52 | { | ||
53 | conn.Open(); | ||
54 | Migration m = new Migration(conn, GetType().Assembly, "AuthStore"); | ||
55 | m.Update(); | ||
56 | } | ||
57 | } | ||
58 | |||
59 | public AuthenticationData Get(UUID principalID) | ||
60 | { | ||
61 | AuthenticationData ret = new AuthenticationData(); | ||
62 | ret.Data = new Dictionary<string, object>(); | ||
63 | |||
64 | string sql = string.Format("select * from '{0}' where UUID = @principalID", m_Realm); | ||
65 | |||
66 | using (SqlConnection conn = new SqlConnection(m_ConnectionString)) | ||
67 | using (SqlCommand cmd = new SqlCommand(sql, conn)) | ||
68 | { | ||
69 | cmd.Parameters.AddWithValue("@principalID", principalID.ToString()); | ||
70 | conn.Open(); | ||
71 | using (SqlDataReader result = cmd.ExecuteReader()) | ||
72 | { | ||
73 | if (result.Read()) | ||
74 | { | ||
75 | ret.PrincipalID = principalID; | ||
76 | |||
77 | if (m_ColumnNames == null) | ||
78 | { | ||
79 | m_ColumnNames = new List<string>(); | ||
80 | |||
81 | DataTable schemaTable = result.GetSchemaTable(); | ||
82 | foreach (DataRow row in schemaTable.Rows) | ||
83 | m_ColumnNames.Add(row["ColumnName"].ToString()); | ||
84 | } | ||
85 | |||
86 | foreach (string s in m_ColumnNames) | ||
87 | { | ||
88 | if (s == "UUID") | ||
89 | continue; | ||
90 | |||
91 | ret.Data[s] = result[s].ToString(); | ||
92 | } | ||
93 | return ret; | ||
94 | } | ||
95 | } | ||
96 | } | ||
97 | return null; | ||
98 | } | ||
99 | |||
100 | public bool Store(AuthenticationData data) | ||
101 | { | ||
102 | if (data.Data.ContainsKey("UUID")) | ||
103 | data.Data.Remove("UUID"); | ||
104 | |||
105 | string[] fields = new List<string>(data.Data.Keys).ToArray(); | ||
106 | StringBuilder updateBuilder = new StringBuilder(); | ||
107 | |||
108 | using (SqlConnection conn = new SqlConnection(m_ConnectionString)) | ||
109 | using (SqlCommand cmd = new SqlCommand()) | ||
110 | { | ||
111 | updateBuilder.AppendFormat("update '{0}' set ", m_Realm); | ||
112 | |||
113 | bool first = true; | ||
114 | foreach (string field in fields) | ||
115 | { | ||
116 | if (!first) | ||
117 | updateBuilder.Append(", "); | ||
118 | updateBuilder.AppendFormat("'{0}' = @{0}",field); | ||
119 | |||
120 | first = false; | ||
121 | |||
122 | cmd.Parameters.AddWithValue("@" + field, data.Data[field]); | ||
123 | } | ||
124 | |||
125 | updateBuilder.Append(" where UUID = @principalID"); | ||
126 | |||
127 | cmd.CommandText = updateBuilder.ToString(); | ||
128 | cmd.Connection = conn; | ||
129 | |||
130 | cmd.Parameters.AddWithValue("@principalID", data.PrincipalID.ToString()); | ||
131 | conn.Open(); | ||
132 | if (cmd.ExecuteNonQuery() < 1) | ||
133 | { | ||
134 | StringBuilder insertBuilder = new StringBuilder(); | ||
135 | |||
136 | insertBuilder.AppendFormat("insert into '{0}' ('UUID', '", m_Realm); | ||
137 | insertBuilder.Append(String.Join("', '", fields)); | ||
138 | insertBuilder.Append("') values ( @principalID, @"); | ||
139 | insertBuilder.Append(String.Join(", @", fields)); | ||
140 | insertBuilder.Append(")"); | ||
141 | |||
142 | cmd.CommandText = insertBuilder.ToString(); | ||
143 | |||
144 | if (cmd.ExecuteNonQuery() < 1) | ||
145 | { | ||
146 | return false; | ||
147 | } | ||
148 | } | ||
149 | } | ||
150 | return true; | ||
151 | } | ||
152 | |||
153 | public bool SetDataItem(UUID principalID, string item, string value) | ||
154 | { | ||
155 | string sql = string.Format("update '{0}' set '{1}' = @{1} where UUID = @UUID", m_Realm, item); | ||
156 | using (SqlConnection conn = new SqlConnection(m_ConnectionString)) | ||
157 | using (SqlCommand cmd = new SqlCommand(sql, conn)) | ||
158 | { | ||
159 | cmd.Parameters.AddWithValue("@" + item, value); | ||
160 | cmd.Parameters.AddWithValue("@UUID", principalID.ToString()); | ||
161 | conn.Open(); | ||
162 | if (cmd.ExecuteNonQuery() > 0) | ||
163 | return true; | ||
164 | } | ||
165 | return false; | ||
166 | } | ||
167 | |||
168 | public bool SetToken(UUID principalID, string token, int lifetime) | ||
169 | { | ||
170 | if (System.Environment.TickCount - m_LastExpire > 30000) | ||
171 | DoExpire(); | ||
172 | string sql = "insert into tokens (UUID, token, validity) values (@principalID, @token, date_add(now(), interval @lifetime minute))"; | ||
173 | using (SqlConnection conn = new SqlConnection(m_ConnectionString)) | ||
174 | using (SqlCommand cmd = new SqlCommand(sql, conn)) | ||
175 | { | ||
176 | cmd.Parameters.AddWithValue("@principalID", principalID.ToString()); | ||
177 | cmd.Parameters.AddWithValue("@token", token); | ||
178 | cmd.Parameters.AddWithValue("@lifetime", lifetime.ToString()); | ||
179 | conn.Open(); | ||
180 | |||
181 | if (cmd.ExecuteNonQuery() > 0) | ||
182 | { | ||
183 | return true; | ||
184 | } | ||
185 | } | ||
186 | return false; | ||
187 | } | ||
188 | |||
189 | public bool CheckToken(UUID principalID, string token, int lifetime) | ||
190 | { | ||
191 | if (System.Environment.TickCount - m_LastExpire > 30000) | ||
192 | DoExpire(); | ||
193 | string sql = "update tokens set validity = date_add(now(), interval @lifetime minute) where UUID = @principalID and token = @token and validity > now()"; | ||
194 | using (SqlConnection conn = new SqlConnection(m_ConnectionString)) | ||
195 | using (SqlCommand cmd = new SqlCommand(sql, conn)) | ||
196 | { | ||
197 | cmd.Parameters.AddWithValue("@principalID", principalID.ToString()); | ||
198 | cmd.Parameters.AddWithValue("@token", token); | ||
199 | cmd.Parameters.AddWithValue("@lifetime", lifetime.ToString()); | ||
200 | conn.Open(); | ||
201 | |||
202 | if (cmd.ExecuteNonQuery() > 0) | ||
203 | { | ||
204 | return true; | ||
205 | } | ||
206 | } | ||
207 | return false; | ||
208 | } | ||
209 | |||
210 | private void DoExpire() | ||
211 | { | ||
212 | string sql = "delete from tokens where validity < now()"; | ||
213 | using (SqlConnection conn = new SqlConnection(m_ConnectionString)) | ||
214 | using (SqlCommand cmd = new SqlCommand(sql, conn)) | ||
215 | { | ||
216 | conn.Open(); | ||
217 | cmd.ExecuteNonQuery(); | ||
218 | } | ||
219 | m_LastExpire = System.Environment.TickCount; | ||
220 | } | ||
221 | } | ||
222 | } | ||
diff --git a/OpenSim/Data/MSSQL/MSSQLUserAccountData.cs b/OpenSim/Data/MSSQL/MSSQLUserAccountData.cs new file mode 100644 index 0000000..1520888 --- /dev/null +++ b/OpenSim/Data/MSSQL/MSSQLUserAccountData.cs | |||
@@ -0,0 +1,186 @@ | |||
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 OpenSimulator 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 | |||
28 | using System; | ||
29 | using System.Collections; | ||
30 | using System.Collections.Generic; | ||
31 | using System.Data; | ||
32 | using OpenMetaverse; | ||
33 | using OpenSim.Framework; | ||
34 | using System.Data.SqlClient; | ||
35 | using System.Text; | ||
36 | |||
37 | namespace OpenSim.Data.MSSQL | ||
38 | { | ||
39 | public class MSSQLUserAccountData : IUserAccountData | ||
40 | { | ||
41 | private string m_Realm; | ||
42 | private List<string> m_ColumnNames = null; | ||
43 | private int m_LastExpire = 0; | ||
44 | private string m_ConnectionString; | ||
45 | |||
46 | public MSSQLUserAccountData(string connectionString, string realm) | ||
47 | { | ||
48 | m_Realm = realm; | ||
49 | m_ConnectionString = connectionString; | ||
50 | using (SqlConnection conn = new SqlConnection(m_ConnectionString)) | ||
51 | { | ||
52 | conn.Open(); | ||
53 | Migration m = new Migration(conn, GetType().Assembly, "UserStore"); | ||
54 | m.Update(); | ||
55 | } | ||
56 | } | ||
57 | |||
58 | public List<UserAccountData> Query(UUID principalID, UUID scopeID, string query) | ||
59 | { | ||
60 | return null; | ||
61 | } | ||
62 | |||
63 | public UserAccountData Get(UUID principalID, UUID scopeID) | ||
64 | { | ||
65 | UserAccountData ret = new UserAccountData(); | ||
66 | ret.Data = new Dictionary<string, object>(); | ||
67 | |||
68 | string sql = string.Format("select * from '{0}' where UUID = @principalID", m_Realm); | ||
69 | if (scopeID != UUID.Zero) | ||
70 | sql += " and ScopeID = @scopeID"; | ||
71 | |||
72 | using (SqlConnection conn = new SqlConnection(m_ConnectionString)) | ||
73 | using (SqlCommand cmd = new SqlCommand(sql, conn)) | ||
74 | { | ||
75 | |||
76 | cmd.Parameters.AddWithValue("@principalID", principalID); | ||
77 | cmd.Parameters.AddWithValue("@scopeID", scopeID); | ||
78 | conn.Open(); | ||
79 | using (SqlDataReader result = cmd.ExecuteReader()) | ||
80 | { | ||
81 | if (result.Read()) | ||
82 | { | ||
83 | ret.PrincipalID = principalID; | ||
84 | UUID scope; | ||
85 | UUID.TryParse(result["ScopeID"].ToString(), out scope); | ||
86 | ret.ScopeID = scope; | ||
87 | |||
88 | if (m_ColumnNames == null) | ||
89 | { | ||
90 | m_ColumnNames = new List<string>(); | ||
91 | |||
92 | DataTable schemaTable = result.GetSchemaTable(); | ||
93 | foreach (DataRow row in schemaTable.Rows) | ||
94 | m_ColumnNames.Add(row["ColumnName"].ToString()); | ||
95 | } | ||
96 | |||
97 | foreach (string s in m_ColumnNames) | ||
98 | { | ||
99 | if (s == "UUID") | ||
100 | continue; | ||
101 | if (s == "ScopeID") | ||
102 | continue; | ||
103 | |||
104 | ret.Data[s] = result[s].ToString(); | ||
105 | } | ||
106 | return ret; | ||
107 | } | ||
108 | } | ||
109 | } | ||
110 | return null; | ||
111 | } | ||
112 | |||
113 | public bool Store(UserAccountData data) | ||
114 | { | ||
115 | if (data.Data.ContainsKey("UUID")) | ||
116 | data.Data.Remove("UUID"); | ||
117 | if (data.Data.ContainsKey("ScopeID")) | ||
118 | data.Data.Remove("ScopeID"); | ||
119 | |||
120 | string[] fields = new List<string>(data.Data.Keys).ToArray(); | ||
121 | |||
122 | using (SqlConnection conn = new SqlConnection(m_ConnectionString)) | ||
123 | using (SqlCommand cmd = new SqlCommand()) | ||
124 | { | ||
125 | StringBuilder updateBuilder = new StringBuilder(); | ||
126 | updateBuilder.AppendFormat("update '{0}' set ", m_Realm); | ||
127 | bool first = true; | ||
128 | foreach (string field in fields) | ||
129 | { | ||
130 | if (!first) | ||
131 | updateBuilder.Append(", "); | ||
132 | updateBuilder.AppendFormat("'{0}' = @{0}", field); | ||
133 | |||
134 | first = false; | ||
135 | |||
136 | cmd.Parameters.AddWithValue("@" + field, data.Data[field]); | ||
137 | } | ||
138 | |||
139 | updateBuilder.Append(" where UUID = @principalID"); | ||
140 | |||
141 | if (data.ScopeID != UUID.Zero) | ||
142 | updateBuilder.Append(" and ScopeID = @scopeID"); | ||
143 | |||
144 | cmd.CommandText = updateBuilder.ToString(); | ||
145 | cmd.Connection = conn; | ||
146 | cmd.Parameters.AddWithValue("@principalID", data.PrincipalID); | ||
147 | cmd.Parameters.AddWithValue("@scopeID", data.ScopeID); | ||
148 | conn.Open(); | ||
149 | |||
150 | if (cmd.ExecuteNonQuery() < 1) | ||
151 | { | ||
152 | StringBuilder insertBuilder = new StringBuilder(); | ||
153 | insertBuilder.AppendFormat("insert into '{0}' ('UUID', 'ScopeID', '", m_Realm); | ||
154 | insertBuilder.Append(String.Join("', '", fields)); | ||
155 | insertBuilder.Append("') values ( @principalID, @scopeID, @"); | ||
156 | insertBuilder.Append(String.Join(", @", fields)); | ||
157 | insertBuilder.Append(")"); | ||
158 | |||
159 | cmd.CommandText = insertBuilder.ToString(); | ||
160 | |||
161 | if (cmd.ExecuteNonQuery() < 1) | ||
162 | { | ||
163 | return false; | ||
164 | } | ||
165 | } | ||
166 | } | ||
167 | return true; | ||
168 | } | ||
169 | |||
170 | public bool SetDataItem(UUID principalID, string item, string value) | ||
171 | { | ||
172 | string sql = string.Format("update '{0}' set '{1}' = @{1} where UUID = @UUID", m_Realm, item); | ||
173 | using (SqlConnection conn = new SqlConnection(m_ConnectionString)) | ||
174 | using (SqlCommand cmd = new SqlCommand(sql, conn)) | ||
175 | { | ||
176 | cmd.Parameters.AddWithValue("@" + item, value); | ||
177 | cmd.Parameters.AddWithValue("@UUID", principalID); | ||
178 | conn.Open(); | ||
179 | |||
180 | if (cmd.ExecuteNonQuery() > 0) | ||
181 | return true; | ||
182 | } | ||
183 | return false; | ||
184 | } | ||
185 | } | ||
186 | } | ||
diff --git a/OpenSim/Data/MSSQL/Resources/011_UserStore.sql b/OpenSim/Data/MSSQL/Resources/011_UserStore.sql new file mode 100644 index 0000000..5aa064f --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/011_UserStore.sql | |||
@@ -0,0 +1,5 @@ | |||
1 | BEGIN TRANSACTION | ||
2 | |||
3 | ALTER TABLE users ADD scopeID uniqueidentifier not null default '00000000-0000-0000-0000-000000000000' | ||
4 | |||
5 | COMMIT | ||