mcs/class/Mainsoft.Web/Mainsoft.Web.Profile/DerbyProfileHelper.cs

   1 //\r
   2 // Mainsoft.Web.Profile.DerbyProfileHelper\r
   3 //\r
   4 // Authors:\r
   5 //      Vladimir Krasnov (vladimirk@mainsoft.com)\r
   6 //\r
   7 // (C) 2006 Mainsoft\r
   8 //\r
   9 // Permission is hereby granted, free of charge, to any person obtaining\r
  10 // a copy of this software and associated documentation files (the\r
  11 // "Software"), to deal in the Software without restriction, including\r
  12 // without limitation the rights to use, copy, modify, merge, publish,\r
  13 // distribute, sublicense, and/or sell copies of the Software, and to\r
  14 // permit persons to whom the Software is furnished to do so, subject to\r
  15 // the following conditions:\r
  16 // \r
  17 // The above copyright notice and this permission notice shall be\r
  18 // included in all copies or substantial portions of the Software.\r
  19 // \r
  20 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,\r
  21 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF\r
  22 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND\r
  23 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE\r
  24 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION\r
  25 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION\r
  26 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.\r
  27 //\r
  28 \r
  29 #if NET_2_0\r
  30 \r
  31 using System;\r
  32 using System.Web.Security;\r
  33 using System.Web.Configuration;\r
  34 using System.Data;\r
  35 using System.Data.OleDb;\r
  36 using System.Data.Common;\r
  37 using System.Collections.Generic;\r
  38 using System.Text;\r
  39 \r
  40 using Mainsoft.Web.Security;\r
  41 \r
  42 namespace Mainsoft.Web.Profile\r
  43 {\r
  44         class DerbyProfileHelper\r
  45         {\r
  46                 private static OleDbParameter AddParameter (OleDbCommand command, string paramName, object paramValue)\r
  47                 {\r
  48                         OleDbParameter prm = new OleDbParameter (paramName, paramValue);\r
  49                         command.Parameters.Add (prm);\r
  50                         return prm;\r
  51                 }\r
  52 \r
  53                 public static int Profile_DeleteInactiveProfiles(DbConnection connection, string applicationName, int profileAuthOptions, DateTime inactiveSinceDate)\r
  54                 {\r
  55                         string appId = DerbyApplicationsHelper.GetApplicationId (connection, applicationName);\r
  56                         if (appId == null)\r
  57                                 return 0;\r
  58 \r
  59                         string queryDelete = @"DELETE FROM aspnet_Profile WHERE UserId IN ( " +\r
  60                                 "SELECT UserId FROM aspnet_Users WHERE ApplicationId = ? AND LastActivityDate <= ? " +
  61                                 GetProfileAuthOptions (profileAuthOptions) + ")";\r
  62                         OleDbCommand cmdDelete = new OleDbCommand (queryDelete, (OleDbConnection) connection);\r
  63                         AddParameter (cmdDelete, "ApplicationId", appId);\r
  64                         AddParameter (cmdDelete, "LastActivityDate", inactiveSinceDate);\r
  65 \r
  66                         return cmdDelete.ExecuteNonQuery ();\r
  67                 }\r
  68 \r
  69                 public static int Profile_DeleteProfiles(DbConnection connection, string applicationName, string [] userNames)\r
  70                 {\r
  71                         int deletedUsers = 0;\r
  72                         string appId = DerbyApplicationsHelper.GetApplicationId (connection, applicationName);\r
  73                         if (appId == null)\r
  74                                 return 0;\r
  75 \r
  76                         OleDbTransaction trans = (OleDbTransaction) connection.BeginTransaction ();\r
  77                         try {\r
  78                                 foreach (string username in userNames) {\r
  79                                         string userId = GetUserId (connection, trans, appId, username);\r
  80                                         if (userId == null)\r
  81                                                 continue;\r
  82 \r
  83                                         string queryDelete = "DELETE FROM aspnet_Profile WHERE UserId = ?";\r
  84                                         OleDbCommand cmdDelete = new OleDbCommand (queryDelete, (OleDbConnection) connection);\r
  85                                         cmdDelete.Transaction = trans;\r
  86                                         AddParameter (cmdDelete, "UserId", userId);\r
  87                                         cmdDelete.Transaction = trans;\r
  88                                         deletedUsers += cmdDelete.ExecuteNonQuery ();\r
  89                                 }\r
  90                                 trans.Commit ();\r
  91                                 return deletedUsers;\r
  92                         }\r
  93                         catch (Exception e) {\r
  94                                 trans.Rollback ();\r
  95                                 throw e;\r
  96                         }\r
  97                 }\r
  98 \r
  99                 public static int Profile_GetNumberOfInactiveProfiles (DbConnection connection, string applicationName, int profileAuthOptions, DateTime inactiveSinceDate)\r
 100                 {\r
 101                         string appId = DerbyApplicationsHelper.GetApplicationId (connection, applicationName);\r
 102                         if (appId == null)\r
 103                                 return 0;\r
 104 \r
 105                         string querySelect = @"SELECT COUNT(*) FROM aspnet_Users usr, aspnet_Profile prf WHERE ApplicationId = ? " +
 106                                 "AND usr.UserId = prf.UserId AND LastActivityDate <= ? " + GetProfileAuthOptions(profileAuthOptions);\r
 107                         OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);\r
 108                         AddParameter (cmdSelect, "ApplicationId", appId);\r
 109                         AddParameter (cmdSelect, "LastActivityDate", inactiveSinceDate);\r
 110 \r
 111                         using (OleDbDataReader reader = cmdSelect.ExecuteReader ()) {\r
 112                                 if (reader.Read ())\r
 113                                         return reader.GetInt32 (0);\r
 114                         }\r
 115                         return 0;\r
 116                 }\r
 117 \r
 118                 public static int Profile_GetInactiveProfiles (DbConnection connection, string applicationName, int profileAuthOptions, int pageIndex, int pageSize, string userNameToMatch, DateTime inactiveSinceDate, out DbDataReader reader)\r
 119                 {\r
 120                         reader = null;\r
 121                         string appId = DerbyApplicationsHelper.GetApplicationId (connection, applicationName);\r
 122                         if (appId == null)\r
 123                                 return -1;\r
 124 \r
 125                         string querySelect = @"SELECT usr.UserName, usr.IsAnonymous, usr.LastActivityDate, prf.LastUpdatedDate, " +\r
 126                                 "LENGTH(prf.PropertyNames) + LENGTH(prf.PropertyValuesString) + LENGTH(prf.PropertyValuesBinary)" +\r
 127                                 "FROM aspnet_Users usr, aspnet_Profile prf WHERE usr.ApplicationId = ? AND usr.UserId = prf.UserId " +\r
 128                                 "AND usr.LastActivityDate <= ? " + GetProfileAuthOptions (profileAuthOptions) +\r
 129                                 (string.IsNullOrEmpty(userNameToMatch) ? "" : " AND usr.LoweredUserName LIKE ?");\r
 130                         OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);\r
 131                         AddParameter (cmdSelect, "ApplicationId", appId);\r
 132                         AddParameter (cmdSelect, "LastActivityDate", inactiveSinceDate);\r
 133                         if (!string.IsNullOrEmpty (userNameToMatch))\r
 134                                 AddParameter (cmdSelect, "LoweredUserName", userNameToMatch.ToLowerInvariant());\r
 135                         reader = cmdSelect.ExecuteReader ();\r
 136                         return 0;\r
 137                 }\r
 138 \r
 139                 public static int Profile_GetProfiles (DbConnection connection, string applicationName, int profileAuthOptions, int pageIndex, int pageSize, string userNameToMatch, out DbDataReader reader)\r
 140                 {\r
 141                         reader = null;\r
 142                         string appId = DerbyApplicationsHelper.GetApplicationId (connection, applicationName);\r
 143                         if (appId == null)\r
 144                                 return -1;\r
 145 \r
 146                         string querySelect = @"SELECT usr.UserName, usr.IsAnonymous, usr.LastActivityDate, prf.LastUpdatedDate, " +\r
 147                                 "LENGTH(prf.PropertyNames) + LENGTH(prf.PropertyValuesString) + LENGTH(prf.PropertyValuesBinary)" +\r
 148                                 "FROM aspnet_Users usr, aspnet_Profile prf WHERE ApplicationId = ? AND usr.UserId = prf.UserId " +\r
 149                                 GetProfileAuthOptions (profileAuthOptions) +\r
 150                                 (string.IsNullOrEmpty (userNameToMatch) ? "" : " AND usr.LoweredUserName LIKE ?");\r
 151                         OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);\r
 152                         AddParameter (cmdSelect, "ApplicationId", appId);\r
 153                         if (!string.IsNullOrEmpty (userNameToMatch))\r
 154                                 AddParameter (cmdSelect, "LoweredUserName", userNameToMatch.ToLowerInvariant ());\r
 155                         reader = cmdSelect.ExecuteReader ();\r
 156                         return 0;\r
 157                 }\r
 158 \r
 159                 public static int Profile_GetProperties (DbConnection connection, string applicationName, string username, DateTime currentTimeUtc, out DbDataReader reader)\r
 160                 {\r
 161                         reader = null;\r
 162                         string appId = DerbyApplicationsHelper.GetApplicationId (connection, applicationName);\r
 163                         if (appId == null)\r
 164                                 return -1;\r
 165 \r
 166                         string userId = GetUserId (connection, null, appId, username);\r
 167                         if (userId == null)\r
 168                                 return -1;\r
 169 \r
 170                         string queryUpdUser = @"UPDATE aspnet_Users SET LastActivityDate = ? WHERE UserId = ?";\r
 171                         OleDbCommand cmdUpdUser = new OleDbCommand (queryUpdUser, (OleDbConnection) connection);\r
 172                         AddParameter (cmdUpdUser, "LastActivityDate", currentTimeUtc);\r
 173                         AddParameter (cmdUpdUser, "UserId", userId);\r
 174                         cmdUpdUser.ExecuteNonQuery ();\r
 175 \r
 176                         string querySelect = @"SELECT PropertyNames, PropertyValuesString, PropertyValuesBinary FROM aspnet_Profile WHERE UserId = ?";\r
 177                         OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);\r
 178                         AddParameter (cmdSelect, "UserId", userId);\r
 179                         reader = cmdSelect.ExecuteReader ();\r
 180                         return 0;\r
 181                 }\r
 182 \r
 183                 public static int Profile_SetProperties (DbConnection connection, string applicationName, string propertyNames, string propertyValuesString, byte [] propertyValuesBinary, string username, bool isUserAnonymous, DateTime currentTimeUtc)\r
 184                 {\r
 185                         string appId = DerbyApplicationsHelper.GetApplicationId (connection, applicationName);\r
 186                         if (appId == null) {\r
 187                                 object newAppId = DerbyApplicationsHelper.Applications_CreateApplication (connection, applicationName);\r
 188                                 appId = newAppId as string;\r
 189                                 if (appId == null)\r
 190                                         return -1;\r
 191                         }\r
 192 \r
 193                         OleDbTransaction trans = (OleDbTransaction) connection.BeginTransaction ();\r
 194                         try {\r
 195                                 string userId = GetUserId (connection, trans, appId, username);\r
 196                                 if (userId == null) {\r
 197                                         object newUserId = null;\r
 198                                         DerbyMembershipHelper.Users_CreateUser (connection, trans, appId, username, true, currentTimeUtc, ref newUserId);\r
 199                                         userId = newUserId as string;\r
 200                                         if (userId == null) {\r
 201                                                 trans.Rollback ();\r
 202                                                 return -1;\r
 203                                         }\r
 204                                 }\r
 205 \r
 206                                 string queryUpdUser = @"UPDATE aspnet_Users SET LastActivityDate=? WHERE UserId = ?";\r
 207                                 OleDbCommand cmdUpdUser = new OleDbCommand (queryUpdUser, (OleDbConnection) connection);\r
 208                                 cmdUpdUser.Transaction = trans;\r
 209                                 AddParameter (cmdUpdUser, "LastActivityDate", currentTimeUtc);\r
 210                                 AddParameter (cmdUpdUser, "UserId", userId);\r
 211                                 cmdUpdUser.ExecuteNonQuery ();\r
 212 \r
 213                                 string querySelect = @"SELECT * FROM aspnet_Profile WHERE UserId = ?";\r
 214                                 OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);\r
 215                                 cmdSelect.Transaction = trans;\r
 216                                 AddParameter (cmdSelect, "UserId", userId);\r
 217                                 bool userHasRecords = false;\r
 218                                 using (OleDbDataReader reader = cmdSelect.ExecuteReader ()) {\r
 219                                         userHasRecords = reader.HasRows;\r
 220                                 }\r
 221 \r
 222                                 if (userHasRecords) {\r
 223                                         string queryUpdate = @"UPDATE aspnet_Profile SET PropertyNames = ?, PropertyValuesString = ?, " +\r
 224                                                 "PropertyValuesBinary = ?, LastUpdatedDate = ? WHERE  UserId = ?";\r
 225                                         OleDbCommand cmdUpdate = new OleDbCommand (queryUpdate, (OleDbConnection) connection);\r
 226                                         cmdUpdate.Transaction = trans;\r
 227                                         AddParameter (cmdUpdate, "PropertyNames", propertyNames);\r
 228                                         AddParameter (cmdUpdate, "PropertyValuesString", propertyValuesString);\r
 229                                         AddParameter (cmdUpdate, "PropertyValuesBinary", propertyValuesBinary);\r
 230                                         AddParameter (cmdUpdate, "LastUpdatedDate", currentTimeUtc);\r
 231                                         AddParameter (cmdUpdate, "UserId", userId);\r
 232                                         cmdUpdate.ExecuteNonQuery ();\r
 233                                 }\r
 234                                 else {\r
 235                                         string queryInsert = @"INSERT INTO aspnet_Profile(UserId, PropertyNames, PropertyValuesString, " +\r
 236                                                 "PropertyValuesBinary, LastUpdatedDate) VALUES (?, ?, ?, ?, ?)";\r
 237                                         OleDbCommand cmdInsert = new OleDbCommand (queryInsert, (OleDbConnection) connection);\r
 238                                         cmdInsert.Transaction = trans;\r
 239                                         AddParameter (cmdInsert, "UserId", userId);\r
 240                                         AddParameter (cmdInsert, "PropertyNames", propertyNames);\r
 241                                         AddParameter (cmdInsert, "PropertyValuesString", propertyValuesString);\r
 242                                         AddParameter (cmdInsert, "PropertyValuesBinary", propertyValuesBinary);\r
 243                                         AddParameter (cmdInsert, "LastUpdatedDate", currentTimeUtc);\r
 244                                         cmdInsert.ExecuteNonQuery ();\r
 245                                 }\r
 246                                 trans.Commit ();\r
 247                         }\r
 248                         catch (Exception e) {\r
 249                                 trans.Rollback ();\r
 250                                 throw e;\r
 251                         }\r
 252                         return 0;\r
 253                 }\r
 254 \r
 255                 private static string GetUserId (DbConnection connection, DbTransaction trans, string applicationId, string username)\r
 256                 {\r
 257                         if (username == null)\r
 258                                 return null;\r
 259 \r
 260                         string selectQuery = "SELECT UserId FROM aspnet_Users WHERE LoweredUserName = ? AND ApplicationId = ?";\r
 261 \r
 262                         OleDbCommand selectCmd = new OleDbCommand (selectQuery, (OleDbConnection) connection);\r
 263                         if (trans != null)\r
 264                                 selectCmd.Transaction = (OleDbTransaction) trans;\r
 265 \r
 266                         AddParameter (selectCmd, "LoweredUserName", username.ToLowerInvariant ());\r
 267                         AddParameter (selectCmd, "ApplicationId", applicationId);\r
 268                         using (OleDbDataReader reader = selectCmd.ExecuteReader ()) {\r
 269                                 if (reader.Read ())\r
 270                                         return reader.GetString (0);\r
 271                         }\r
 272 \r
 273                         return null;\r
 274                 }\r
 275                 \r
 276                 private static string GetProfileAuthOptions (int profileAuthOptions)\r
 277                 {\r
 278                         switch (profileAuthOptions) {\r
 279                                 case 1:\r
 280                                         return "AND IsAnonymous = 0";\r
 281 \r
 282                                 case 2:\r
 283                                         return "AND IsAnonymous = 1";\r
 284                         }\r
 285                         return string.Empty;\r
 286                 }\r
 287         }\r
 288 }\r
 289 \r
 290 #endif