1 // 2 // $Id: PgProfileProvider.cs 36 2007-11-24 09:44:42Z dna $ 3 // 4 // Permission is hereby granted, free of charge, to any person obtaining 5 // a copy of this software and associated documentation files (the 6 // "Software"), to deal in the Software without restriction, including 7 // without limitation the rights to use, copy, modify, merge, publish, 8 // distribute, sublicense, and/or sell copies of the Software, and to 9 // permit persons to whom the Software is furnished to do so, subject to 10 // the following conditions: 11 // 12 // The above copyright notice and this permission notice shall be 13 // included in all copies or substantial portions of the Software. 14 // 15 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, 16 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF 17 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND 18 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE 19 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION 20 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION 21 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. 22 // 23 // Copyright � 2006, 2007 Nauck IT KG http://www.nauck-it.de 24 // 25 // Author: 26 // Daniel Nauck <d.nauck(at)nauck-it.de> 27 // 28 // Adapted to Sqlite by Marek Habersack <mhabersack@novell.com> 29 // 30 31 using System; 32 using System.Data; 33 using System.Data.Common; 34 using System.Configuration; 35 using System.Configuration.Provider; 36 using System.Collections.Generic; 37 using System.Collections.Specialized; 38 using System.Diagnostics; 39 using System.Text; 40 using System.Web.Hosting; 41 using System.Web.Util; 42 43 using Mono.Data.Sqlite; 44 45 namespace System.Web.Profile 46 { 47 internal class SqliteProfileProvider : ProfileProvider 48 { 49 const string m_ProfilesTableName = "Profiles"; 50 const string m_ProfileDataTableName = "ProfileData"; 51 string m_ConnectionString = string.Empty; 52 53 SerializationHelper m_serializationHelper = new SerializationHelper(); 54 AddParameter(DbCommand command, string parameterName)55 DbParameter AddParameter (DbCommand command, string parameterName) 56 { 57 return AddParameter (command, parameterName, null); 58 } 59 AddParameter(DbCommand command, string parameterName, object parameterValue)60 DbParameter AddParameter (DbCommand command, string parameterName, object parameterValue) 61 { 62 return AddParameter (command, parameterName, ParameterDirection.Input, parameterValue); 63 } 64 AddParameter(DbCommand command, string parameterName, ParameterDirection direction, object parameterValue)65 DbParameter AddParameter (DbCommand command, string parameterName, ParameterDirection direction, object parameterValue) 66 { 67 DbParameter dbp = command.CreateParameter (); 68 dbp.ParameterName = parameterName; 69 dbp.Value = parameterValue; 70 dbp.Direction = direction; 71 command.Parameters.Add (dbp); 72 return dbp; 73 } 74 75 /// <summary> 76 /// System.Configuration.Provider.ProviderBase.Initialize Method 77 /// </summary> Initialize(string name, NameValueCollection config)78 public override void Initialize(string name, NameValueCollection config) 79 { 80 // Initialize values from web.config. 81 if (config == null) 82 throw new ArgumentNullException("Config", Properties.Resources.ErrArgumentNull); 83 84 if (string.IsNullOrEmpty(name)) 85 name = Properties.Resources.ProfileProviderDefaultName; 86 87 if (string.IsNullOrEmpty(config["description"])) 88 { 89 config.Remove("description"); 90 config.Add("description", Properties.Resources.ProfileProviderDefaultDescription); 91 } 92 93 // Initialize the abstract base class. 94 base.Initialize(name, config); 95 96 m_ApplicationName = GetConfigValue(config["applicationName"], HostingEnvironment.ApplicationVirtualPath); 97 98 // Get connection string. 99 string connStrName = config["connectionStringName"]; 100 101 if (string.IsNullOrEmpty(connStrName)) 102 { 103 throw new ArgumentOutOfRangeException("ConnectionStringName", Properties.Resources.ErrArgumentNullOrEmpty); 104 } 105 else 106 { 107 ConnectionStringSettings ConnectionStringSettings = ConfigurationManager.ConnectionStrings[connStrName]; 108 109 if (ConnectionStringSettings == null || string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim())) 110 { 111 throw new ProviderException(Properties.Resources.ErrConnectionStringNullOrEmpty); 112 } 113 114 m_ConnectionString = ConnectionStringSettings.ConnectionString; 115 } 116 } 117 118 /// <summary> 119 /// System.Web.Profile.ProfileProvider properties. 120 /// </summary> 121 #region System.Web.Security.ProfileProvider properties 122 string m_ApplicationName = string.Empty; 123 124 public override string ApplicationName 125 { 126 get { return m_ApplicationName; } 127 set { m_ApplicationName = value; } 128 } 129 #endregion 130 131 /// <summary> 132 /// System.Web.Profile.ProfileProvider methods. 133 /// </summary> 134 #region System.Web.Security.ProfileProvider methods 135 136 /// <summary> 137 /// ProfileProvider.DeleteInactiveProfiles 138 /// </summary> DeleteInactiveProfiles(ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate)139 public override int DeleteInactiveProfiles(ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate) 140 { 141 throw new Exception("DeleteInactiveProfiles: The method or operation is not implemented."); 142 } 143 DeleteProfiles(string[] usernames)144 public override int DeleteProfiles(string[] usernames) 145 { 146 throw new Exception("DeleteProfiles1: The method or operation is not implemented."); 147 } 148 DeleteProfiles(ProfileInfoCollection profiles)149 public override int DeleteProfiles(ProfileInfoCollection profiles) 150 { 151 throw new Exception("DeleteProfiles2: The method or operation is not implemented."); 152 } 153 FindInactiveProfilesByUserName(ProfileAuthenticationOption authenticationOption, string usernameToMatch, DateTime userInactiveSinceDate, int pageIndex, int pageSize, out int totalRecords)154 public override ProfileInfoCollection FindInactiveProfilesByUserName(ProfileAuthenticationOption authenticationOption, string usernameToMatch, DateTime userInactiveSinceDate, int pageIndex, int pageSize, out int totalRecords) 155 { 156 throw new Exception("FindInactiveProfilesByUserName: The method or operation is not implemented."); 157 } 158 FindProfilesByUserName(ProfileAuthenticationOption authenticationOption, string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)159 public override ProfileInfoCollection FindProfilesByUserName(ProfileAuthenticationOption authenticationOption, string usernameToMatch, int pageIndex, int pageSize, out int totalRecords) 160 { 161 throw new Exception("FindProfilesByUserName: The method or operation is not implemented."); 162 } 163 GetAllInactiveProfiles(ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate, int pageIndex, int pageSize, out int totalRecords)164 public override ProfileInfoCollection GetAllInactiveProfiles(ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate, int pageIndex, int pageSize, out int totalRecords) 165 { 166 throw new Exception("GetAllInactiveProfiles: The method or operation is not implemented."); 167 } 168 GetAllProfiles(ProfileAuthenticationOption authenticationOption, int pageIndex, int pageSize, out int totalRecords)169 public override ProfileInfoCollection GetAllProfiles(ProfileAuthenticationOption authenticationOption, int pageIndex, int pageSize, out int totalRecords) 170 { 171 throw new Exception("GetAllProfiles: The method or operation is not implemented."); 172 } 173 GetNumberOfInactiveProfiles(ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate)174 public override int GetNumberOfInactiveProfiles(ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate) 175 { 176 throw new Exception("GetNumberOfInactiveProfiles: The method or operation is not implemented."); 177 } 178 #endregion 179 180 /// <summary> 181 /// System.Configuration.SettingsProvider methods. 182 /// </summary> 183 #region System.Web.Security.SettingsProvider methods 184 185 /// <summary> 186 /// 187 /// </summary> GetPropertyValues(SettingsContext context, SettingsPropertyCollection collection)188 public override SettingsPropertyValueCollection GetPropertyValues(SettingsContext context, SettingsPropertyCollection collection) 189 { 190 SettingsPropertyValueCollection result = new SettingsPropertyValueCollection(); 191 string username = (string)context["UserName"]; 192 bool isAuthenticated = (bool)context["IsAuthenticated"]; 193 Dictionary<string, object> databaseResult = new Dictionary<string, object>(); 194 195 using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString)) 196 { 197 using (SqliteCommand dbCommand = dbConn.CreateCommand()) 198 { 199 dbCommand.CommandText = string.Format("SELECT \"Name\", \"ValueString\", \"ValueBinary\" FROM \"{0}\" WHERE \"Profile\" = (SELECT \"pId\" FROM \"{1}\" WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName AND \"IsAnonymous\" = @IsAuthenticated)", m_ProfileDataTableName, m_ProfilesTableName); 200 201 AddParameter (dbCommand, "@Username", username); 202 AddParameter (dbCommand, "@ApplicationName", m_ApplicationName); 203 AddParameter (dbCommand, "@IsAuthenticated", !isAuthenticated); 204 205 try 206 { 207 dbConn.Open(); 208 dbCommand.Prepare(); 209 210 using (SqliteDataReader reader = dbCommand.ExecuteReader()) 211 { 212 while (reader.Read()) 213 { 214 object resultData = null; 215 if(!reader.IsDBNull(1)) 216 resultData = reader.GetValue(1); 217 else if(!reader.IsDBNull(2)) 218 resultData = reader.GetValue(2); 219 220 databaseResult.Add(reader.GetString(0), resultData); 221 } 222 } 223 } 224 catch (SqliteException e) 225 { 226 Trace.WriteLine(e.ToString()); 227 throw new ProviderException(Properties.Resources.ErrOperationAborted); 228 } 229 finally 230 { 231 if (dbConn != null) 232 dbConn.Close(); 233 } 234 } 235 } 236 237 foreach (SettingsProperty item in collection) 238 { 239 if (item.SerializeAs == SettingsSerializeAs.ProviderSpecific) 240 { 241 if (item.PropertyType.IsPrimitive || item.PropertyType.Equals(typeof(string))) 242 item.SerializeAs = SettingsSerializeAs.String; 243 else 244 item.SerializeAs = SettingsSerializeAs.Xml; 245 } 246 247 SettingsPropertyValue itemValue = new SettingsPropertyValue(item); 248 249 if ((databaseResult.ContainsKey(item.Name)) && (databaseResult[item.Name] != null)) 250 { 251 if(item.SerializeAs == SettingsSerializeAs.String) 252 itemValue.PropertyValue = m_serializationHelper.DeserializeFromBase64((string)databaseResult[item.Name]); 253 254 else if (item.SerializeAs == SettingsSerializeAs.Xml) 255 itemValue.PropertyValue = m_serializationHelper.DeserializeFromXml((string)databaseResult[item.Name]); 256 257 else if (item.SerializeAs == SettingsSerializeAs.Binary) 258 itemValue.PropertyValue = m_serializationHelper.DeserializeFromBinary((byte[])databaseResult[item.Name]); 259 } 260 itemValue.IsDirty = false; 261 result.Add(itemValue); 262 } 263 264 UpdateActivityDates(username, isAuthenticated, true); 265 266 return result; 267 } 268 SetPropertyValues(SettingsContext context, SettingsPropertyValueCollection collection)269 public override void SetPropertyValues(SettingsContext context, SettingsPropertyValueCollection collection) 270 { 271 string username = (string)context["UserName"]; 272 bool isAuthenticated = (bool)context["IsAuthenticated"]; 273 274 if (collection.Count < 1) 275 return; 276 277 if (!ProfileExists(username)) 278 CreateProfileForUser(username, isAuthenticated); 279 280 using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString)) 281 { 282 using (SqliteCommand deleteCommand = dbConn.CreateCommand(), 283 insertCommand = dbConn.CreateCommand()) 284 { 285 deleteCommand.CommandText = string.Format("DELETE FROM \"{0}\" WHERE \"Name\" = @Name AND \"Profile\" = (SELECT \"pId\" FROM \"{1}\" WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName AND \"IsAnonymous\" = @IsAuthenticated)", m_ProfileDataTableName, m_ProfilesTableName); 286 287 AddParameter (deleteCommand, "@Name"); 288 AddParameter (deleteCommand, "@Username", username); 289 AddParameter (deleteCommand, "@ApplicationName", m_ApplicationName); 290 AddParameter (deleteCommand, "@IsAuthenticated", !isAuthenticated); 291 292 293 insertCommand.CommandText = string.Format("INSERT INTO \"{0}\" (\"pId\", \"Profile\", \"Name\", \"ValueString\", \"ValueBinary\") VALUES (@pId, (SELECT \"pId\" FROM \"{1}\" WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName AND \"IsAnonymous\" = @IsAuthenticated), @Name, @ValueString, @ValueBinary)", m_ProfileDataTableName, m_ProfilesTableName); 294 295 AddParameter (insertCommand, "@pId"); 296 AddParameter (insertCommand, "@Name"); 297 AddParameter (insertCommand, "@ValueString"); 298 insertCommand.Parameters["@ValueString"].IsNullable = true; 299 AddParameter (insertCommand, "@ValueBinary"); 300 insertCommand.Parameters["@ValueBinary"].IsNullable = true; 301 AddParameter (insertCommand, "@Username", username); 302 AddParameter (insertCommand, "@ApplicationName", m_ApplicationName); 303 AddParameter (insertCommand, "@IsAuthenticated", !isAuthenticated); 304 305 SqliteTransaction dbTrans = null; 306 307 try 308 { 309 dbConn.Open(); 310 deleteCommand.Prepare(); 311 insertCommand.Prepare(); 312 313 using (dbTrans = dbConn.BeginTransaction()) 314 { 315 316 foreach (SettingsPropertyValue item in collection) 317 { 318 if (!item.IsDirty) 319 continue; 320 321 deleteCommand.Parameters["@Name"].Value = item.Name; 322 323 insertCommand.Parameters["@pId"].Value = Guid.NewGuid().ToString(); 324 insertCommand.Parameters["@Name"].Value = item.Name; 325 326 if (item.Property.SerializeAs == SettingsSerializeAs.String) 327 { 328 insertCommand.Parameters["@ValueString"].Value = m_serializationHelper.SerializeToBase64(item.PropertyValue); 329 insertCommand.Parameters["@ValueBinary"].Value = DBNull.Value; //new byte[0];//DBNull.Value; 330 } 331 else if (item.Property.SerializeAs == SettingsSerializeAs.Xml) 332 { 333 item.SerializedValue = m_serializationHelper.SerializeToXml(item.PropertyValue); 334 insertCommand.Parameters["@ValueString"].Value = item.SerializedValue; 335 insertCommand.Parameters["@ValueBinary"].Value = DBNull.Value; //new byte[0];//DBNull.Value; 336 } 337 else if (item.Property.SerializeAs == SettingsSerializeAs.Binary) 338 { 339 item.SerializedValue = m_serializationHelper.SerializeToBinary(item.PropertyValue); 340 insertCommand.Parameters["@ValueString"].Value = DBNull.Value; //string.Empty;//DBNull.Value; 341 insertCommand.Parameters["@ValueBinary"].Value = item.SerializedValue; 342 } 343 344 deleteCommand.ExecuteNonQuery(); 345 insertCommand.ExecuteNonQuery(); 346 } 347 348 UpdateActivityDates(username, isAuthenticated, false); 349 350 // Attempt to commit the transaction 351 dbTrans.Commit(); 352 } 353 } 354 catch (SqliteException e) 355 { 356 Trace.WriteLine(e.ToString()); 357 358 try 359 { 360 // Attempt to roll back the transaction 361 Trace.WriteLine(Properties.Resources.LogRollbackAttempt); 362 dbTrans.Rollback(); 363 } 364 catch (SqliteException re) 365 { 366 // Rollback failed 367 Trace.WriteLine(Properties.Resources.ErrRollbackFailed); 368 Trace.WriteLine(re.ToString()); 369 } 370 371 throw new ProviderException(Properties.Resources.ErrOperationAborted); 372 } 373 finally 374 { 375 if (dbConn != null) 376 dbConn.Close(); 377 } 378 } 379 } 380 } 381 #endregion 382 383 #region private methods 384 /// <summary> 385 /// Create a empty user profile 386 /// </summary> 387 /// <param name="username"></param> 388 /// <param name="isAuthenticated"></param> CreateProfileForUser(string username, bool isAuthenticated)389 void CreateProfileForUser(string username, bool isAuthenticated) 390 { 391 if (ProfileExists(username)) 392 { 393 throw new ProviderException(string.Format(Properties.Resources.ErrProfileAlreadyExist, username)); 394 } 395 396 using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString)) 397 { 398 using (SqliteCommand dbCommand = dbConn.CreateCommand()) 399 { 400 dbCommand.CommandText = string.Format("INSERT INTO \"{0}\" (\"pId\", \"Username\", \"ApplicationName\", \"IsAnonymous\", \"LastActivityDate\", \"LastUpdatedDate\") Values (@pId, @Username, @ApplicationName, @IsAuthenticated, @LastActivityDate, @LastUpdatedDate)", m_ProfilesTableName); 401 402 AddParameter (dbCommand, "@pId", Guid.NewGuid().ToString()); 403 AddParameter (dbCommand, "@Username", username); 404 AddParameter (dbCommand, "@ApplicationName", m_ApplicationName); 405 AddParameter (dbCommand, "@IsAuthenticated", !isAuthenticated); 406 AddParameter (dbCommand, "@LastActivityDate", DateTime.Now); 407 AddParameter (dbCommand, "@LastUpdatedDate", DateTime.Now); 408 409 try 410 { 411 dbConn.Open(); 412 dbCommand.Prepare(); 413 414 dbCommand.ExecuteNonQuery(); 415 } 416 catch (SqliteException e) 417 { 418 Trace.WriteLine(e.ToString()); 419 throw new ProviderException(Properties.Resources.ErrOperationAborted); 420 } 421 finally 422 { 423 if (dbConn != null) 424 dbConn.Close(); 425 } 426 } 427 } 428 } 429 430 ProfileExists(string username)431 bool ProfileExists(string username) 432 { 433 using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString)) 434 { 435 using (SqliteCommand dbCommand = dbConn.CreateCommand()) 436 { 437 dbCommand.CommandText = string.Format("SELECT COUNT(*) FROM \"{0}\" WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_ProfilesTableName); 438 439 AddParameter (dbCommand, "@Username", username); 440 AddParameter (dbCommand, "@ApplicationName", m_ApplicationName); 441 442 try 443 { 444 dbConn.Open(); 445 dbCommand.Prepare(); 446 447 int numRecs = 0; 448 Int32.TryParse(dbCommand.ExecuteScalar().ToString(), out numRecs); 449 450 if (numRecs > 0) 451 return true; 452 } 453 catch (SqliteException e) 454 { 455 Trace.WriteLine(e.ToString()); 456 throw new ProviderException(Properties.Resources.ErrOperationAborted); 457 } 458 finally 459 { 460 if (dbConn != null) 461 dbConn.Close(); 462 } 463 } 464 } 465 466 return false; 467 } 468 469 /// <summary> 470 /// Updates the LastActivityDate and LastUpdatedDate values when profile properties are accessed by the 471 /// GetPropertyValues and SetPropertyValues methods. 472 /// Passing true as the activityOnly parameter will update only the LastActivityDate. 473 /// </summary> 474 /// <param name="username"></param> 475 /// <param name="isAuthenticated"></param> 476 /// <param name="activityOnly"></param> UpdateActivityDates(string username, bool isAuthenticated, bool activityOnly)477 void UpdateActivityDates(string username, bool isAuthenticated, bool activityOnly) 478 { 479 using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString)) 480 { 481 using (SqliteCommand dbCommand = dbConn.CreateCommand()) 482 { 483 if (activityOnly) 484 { 485 dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"LastActivityDate\" = @LastActivityDate WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName AND \"IsAnonymous\" = @IsAuthenticated", m_ProfilesTableName); 486 487 AddParameter (dbCommand, "@LastActivityDate", DateTime.Now); 488 } 489 else 490 { 491 dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"LastActivityDate\" = @LastActivityDate, \"LastUpdatedDate\" = @LastUpdatedDate WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName AND \"IsAnonymous\" = @IsAuthenticated", m_ProfilesTableName); 492 493 AddParameter (dbCommand, "@LastActivityDate", DateTime.Now); 494 AddParameter (dbCommand, "@LastUpdatedDate", DateTime.Now); 495 } 496 497 AddParameter (dbCommand, "@Username", username); 498 AddParameter (dbCommand, "@ApplicationName", m_ApplicationName); 499 AddParameter (dbCommand, "@IsAuthenticated", !isAuthenticated); 500 501 try 502 { 503 dbConn.Open(); 504 dbCommand.Prepare(); 505 506 dbCommand.ExecuteNonQuery(); 507 } 508 catch (SqliteException e) 509 { 510 Trace.WriteLine(e.ToString()); 511 throw new ProviderException(Properties.Resources.ErrOperationAborted); 512 } 513 finally 514 { 515 if (dbConn != null) 516 dbConn.Close(); 517 } 518 } 519 } 520 } 521 522 /// <summary> 523 /// A helper function to retrieve config values from the configuration file. 524 /// </summary> 525 /// <param name="configValue"></param> 526 /// <param name="defaultValue"></param> 527 /// <returns></returns> GetConfigValue(string configValue, string defaultValue)528 string GetConfigValue(string configValue, string defaultValue) 529 { 530 if (string.IsNullOrEmpty(configValue)) 531 return defaultValue; 532 533 return configValue; 534 } 535 #endregion 536 } 537 } 538