diff options
author | Fernando Oliveira | 2013-10-12 16:33:45 -0500 |
---|---|---|
committer | fernando | 2013-10-12 16:33:45 -0500 |
commit | ff8a76825841533bdc5d534b6f58b2ab964ea6c6 (patch) | |
tree | 4ddde40916db04c1506486c9e7ba88b37f53e23e /OpenSim/Data/PGSQL/PGSQLAuthenticationData.cs | |
parent | * pushing test (diff) | |
download | opensim-SC_OLD-ff8a76825841533bdc5d534b6f58b2ab964ea6c6.zip opensim-SC_OLD-ff8a76825841533bdc5d534b6f58b2ab964ea6c6.tar.gz opensim-SC_OLD-ff8a76825841533bdc5d534b6f58b2ab964ea6c6.tar.bz2 opensim-SC_OLD-ff8a76825841533bdc5d534b6f58b2ab964ea6c6.tar.xz |
Fernando Oliveira's Postgress SQL Server Data Connector as a single commit.
* Added PostGreSQL support
* Added MySQL/MySQLXGroupData.cs
* PostgreSQL data access implementation
* PostgreSQL dll binarie and RegionStore.migrations
* Migrations Scripts from MSSQL to POSTGRES
* Postgres SQL Type fixes
* Postgres SQL Connection string
* Data type issues
* more fixes
* tests and +tests
* UUID x string - FIGHT!
* Fixed PG types to internal csharp types
* More data type fix (PostgreSQL fields are case sensitive) :(
* more field case sensitive fixes
* changed the migration files to be case sensitive for fields.
* fixed fields case
* finished converting, now search for hidden bugs.
* some more fixes
* bool type fixed
* more case fixes;
* creatorID case fixed
* case fields fixed
* fixed default now() for TMStamp fields with don't allow nulls.
* fix case sensitve for Region name and Estate name
* fixed case for names for search
* fix class name Error
* Bug fixed on select and migrations
* Un-Reverting my change due to Postgres issue with the ILIKE function
* Fixed some issued for Diva Distro
* Fixes for integration with Diva Distro
* Added System.Core to prebuild.xml for PG project
* Configured to make DIff for Push to OpenSim Project
* Diffs only to PostgreSQL mods.
Diffstat (limited to '')
-rw-r--r-- | OpenSim/Data/PGSQL/PGSQLAuthenticationData.cs | 254 |
1 files changed, 254 insertions, 0 deletions
diff --git a/OpenSim/Data/PGSQL/PGSQLAuthenticationData.cs b/OpenSim/Data/PGSQL/PGSQLAuthenticationData.cs new file mode 100644 index 0000000..d174112 --- /dev/null +++ b/OpenSim/Data/PGSQL/PGSQLAuthenticationData.cs | |||
@@ -0,0 +1,254 @@ | |||
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 OpenMetaverse; | ||
32 | using OpenSim.Framework; | ||
33 | using System.Reflection; | ||
34 | using System.Text; | ||
35 | using System.Data; | ||
36 | using Npgsql; | ||
37 | using NpgsqlTypes; | ||
38 | |||
39 | namespace OpenSim.Data.PGSQL | ||
40 | { | ||
41 | public class PGSQLAuthenticationData : IAuthenticationData | ||
42 | { | ||
43 | private string m_Realm; | ||
44 | private List<string> m_ColumnNames = null; | ||
45 | private int m_LastExpire = 0; | ||
46 | private string m_ConnectionString; | ||
47 | private PGSQLManager m_database; | ||
48 | |||
49 | protected virtual Assembly Assembly | ||
50 | { | ||
51 | get { return GetType().Assembly; } | ||
52 | } | ||
53 | |||
54 | public PGSQLAuthenticationData(string connectionString, string realm) | ||
55 | { | ||
56 | m_Realm = realm; | ||
57 | m_ConnectionString = connectionString; | ||
58 | using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) | ||
59 | { | ||
60 | conn.Open(); | ||
61 | Migration m = new Migration(conn, GetType().Assembly, "AuthStore"); | ||
62 | m_database = new PGSQLManager(m_ConnectionString); | ||
63 | m.Update(); | ||
64 | } | ||
65 | } | ||
66 | |||
67 | public AuthenticationData Get(UUID principalID) | ||
68 | { | ||
69 | AuthenticationData ret = new AuthenticationData(); | ||
70 | ret.Data = new Dictionary<string, object>(); | ||
71 | |||
72 | string sql = string.Format("select * from {0} where uuid = :principalID", m_Realm); | ||
73 | |||
74 | using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) | ||
75 | using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) | ||
76 | { | ||
77 | cmd.Parameters.Add(m_database.CreateParameter("principalID", principalID)); | ||
78 | conn.Open(); | ||
79 | using (NpgsqlDataReader result = cmd.ExecuteReader()) | ||
80 | { | ||
81 | if (result.Read()) | ||
82 | { | ||
83 | ret.PrincipalID = principalID; | ||
84 | |||
85 | if (m_ColumnNames == null) | ||
86 | { | ||
87 | m_ColumnNames = new List<string>(); | ||
88 | |||
89 | DataTable schemaTable = result.GetSchemaTable(); | ||
90 | foreach (DataRow row in schemaTable.Rows) | ||
91 | m_ColumnNames.Add(row["ColumnName"].ToString()); | ||
92 | } | ||
93 | |||
94 | foreach (string s in m_ColumnNames) | ||
95 | { | ||
96 | if (s == "UUID"||s == "uuid") | ||
97 | continue; | ||
98 | |||
99 | ret.Data[s] = result[s].ToString(); | ||
100 | } | ||
101 | return ret; | ||
102 | } | ||
103 | } | ||
104 | } | ||
105 | return null; | ||
106 | } | ||
107 | |||
108 | public bool Store(AuthenticationData data) | ||
109 | { | ||
110 | if (data.Data.ContainsKey("UUID")) | ||
111 | data.Data.Remove("UUID"); | ||
112 | if (data.Data.ContainsKey("uuid")) | ||
113 | data.Data.Remove("uuid"); | ||
114 | |||
115 | /* | ||
116 | Dictionary<string, object> oAuth = new Dictionary<string, object>(); | ||
117 | |||
118 | foreach (KeyValuePair<string, object> oDado in data.Data) | ||
119 | { | ||
120 | if (oDado.Key != oDado.Key.ToLower()) | ||
121 | { | ||
122 | oAuth.Add(oDado.Key.ToLower(), oDado.Value); | ||
123 | } | ||
124 | } | ||
125 | foreach (KeyValuePair<string, object> oDado in data.Data) | ||
126 | { | ||
127 | if (!oAuth.ContainsKey(oDado.Key.ToLower())) { | ||
128 | oAuth.Add(oDado.Key.ToLower(), oDado.Value); | ||
129 | } | ||
130 | } | ||
131 | */ | ||
132 | string[] fields = new List<string>(data.Data.Keys).ToArray(); | ||
133 | StringBuilder updateBuilder = new StringBuilder(); | ||
134 | |||
135 | using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) | ||
136 | using (NpgsqlCommand cmd = new NpgsqlCommand()) | ||
137 | { | ||
138 | updateBuilder.AppendFormat("update {0} set ", m_Realm); | ||
139 | |||
140 | bool first = true; | ||
141 | foreach (string field in fields) | ||
142 | { | ||
143 | if (!first) | ||
144 | updateBuilder.Append(", "); | ||
145 | updateBuilder.AppendFormat("\"{0}\" = :{0}",field); | ||
146 | |||
147 | first = false; | ||
148 | |||
149 | cmd.Parameters.Add(m_database.CreateParameter("" + field, data.Data[field])); | ||
150 | } | ||
151 | |||
152 | updateBuilder.Append(" where uuid = :principalID"); | ||
153 | |||
154 | cmd.CommandText = updateBuilder.ToString(); | ||
155 | cmd.Connection = conn; | ||
156 | cmd.Parameters.Add(m_database.CreateParameter("principalID", data.PrincipalID)); | ||
157 | |||
158 | conn.Open(); | ||
159 | if (cmd.ExecuteNonQuery() < 1) | ||
160 | { | ||
161 | StringBuilder insertBuilder = new StringBuilder(); | ||
162 | |||
163 | insertBuilder.AppendFormat("insert into {0} (uuid, \"", m_Realm); | ||
164 | insertBuilder.Append(String.Join("\", \"", fields)); | ||
165 | insertBuilder.Append("\") values (:principalID, :"); | ||
166 | insertBuilder.Append(String.Join(", :", fields)); | ||
167 | insertBuilder.Append(")"); | ||
168 | |||
169 | cmd.CommandText = insertBuilder.ToString(); | ||
170 | |||
171 | if (cmd.ExecuteNonQuery() < 1) | ||
172 | { | ||
173 | return false; | ||
174 | } | ||
175 | } | ||
176 | } | ||
177 | return true; | ||
178 | } | ||
179 | |||
180 | public bool SetDataItem(UUID principalID, string item, string value) | ||
181 | { | ||
182 | string sql = string.Format("update {0} set {1} = :{1} where uuid = :UUID", m_Realm, item); | ||
183 | using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) | ||
184 | using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) | ||
185 | { | ||
186 | cmd.Parameters.Add(m_database.CreateParameter("" + item, value)); | ||
187 | conn.Open(); | ||
188 | if (cmd.ExecuteNonQuery() > 0) | ||
189 | return true; | ||
190 | } | ||
191 | return false; | ||
192 | } | ||
193 | |||
194 | public bool SetToken(UUID principalID, string token, int lifetime) | ||
195 | { | ||
196 | if (System.Environment.TickCount - m_LastExpire > 30000) | ||
197 | DoExpire(); | ||
198 | |||
199 | string sql = "insert into tokens (uuid, token, validity) values (:principalID, :token, :lifetime)"; | ||
200 | using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) | ||
201 | using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) | ||
202 | { | ||
203 | cmd.Parameters.Add(m_database.CreateParameter("principalID", principalID)); | ||
204 | cmd.Parameters.Add(m_database.CreateParameter("token", token)); | ||
205 | cmd.Parameters.Add(m_database.CreateParameter("lifetime", DateTime.Now.AddMinutes(lifetime))); | ||
206 | conn.Open(); | ||
207 | |||
208 | if (cmd.ExecuteNonQuery() > 0) | ||
209 | { | ||
210 | return true; | ||
211 | } | ||
212 | } | ||
213 | return false; | ||
214 | } | ||
215 | |||
216 | public bool CheckToken(UUID principalID, string token, int lifetime) | ||
217 | { | ||
218 | if (System.Environment.TickCount - m_LastExpire > 30000) | ||
219 | DoExpire(); | ||
220 | |||
221 | DateTime validDate = DateTime.Now.AddMinutes(lifetime); | ||
222 | string sql = "update tokens set validity = :validDate where uuid = :principalID and token = :token and validity > (CURRENT_DATE + CURRENT_TIME)"; | ||
223 | |||
224 | using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) | ||
225 | using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) | ||
226 | { | ||
227 | cmd.Parameters.Add(m_database.CreateParameter("principalID", principalID)); | ||
228 | cmd.Parameters.Add(m_database.CreateParameter("token", token)); | ||
229 | cmd.Parameters.Add(m_database.CreateParameter("validDate", validDate)); | ||
230 | conn.Open(); | ||
231 | |||
232 | if (cmd.ExecuteNonQuery() > 0) | ||
233 | { | ||
234 | return true; | ||
235 | } | ||
236 | } | ||
237 | return false; | ||
238 | } | ||
239 | |||
240 | private void DoExpire() | ||
241 | { | ||
242 | DateTime currentDateTime = DateTime.Now; | ||
243 | string sql = "delete from tokens where validity < :currentDateTime"; | ||
244 | using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString)) | ||
245 | using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) | ||
246 | { | ||
247 | conn.Open(); | ||
248 | cmd.Parameters.Add(m_database.CreateParameter("currentDateTime", currentDateTime)); | ||
249 | cmd.ExecuteNonQuery(); | ||
250 | } | ||
251 | m_LastExpire = System.Environment.TickCount; | ||
252 | } | ||
253 | } | ||
254 | } | ||