mcs/class/Mainsoft.Web/Mainsoft.Web.Security/DerbyDBSchema.cs

   1 //
   2 // Mainsoft.Web.Security.DerbyDBSchema
   3 //
   4 // Authors:
   5 //      Vladimir Krasnov (vladimirk@mainsoft.com)
   6 //
   7 // (C) 2006 Mainsoft
   8 //
   9 // Permission is hereby granted, free of charge, to any person obtaining
  10 // a copy of this software and associated documentation files (the
  11 // "Software"), to deal in the Software without restriction, including
  12 // without limitation the rights to use, copy, modify, merge, publish,
  13 // distribute, sublicense, and/or sell copies of the Software, and to
  14 // permit persons to whom the Software is furnished to do so, subject to
  15 // the following conditions:
  16 //
  17 // The above copyright notice and this permission notice shall be
  18 // included in all copies or substantial portions of the Software.
  19 //
  20 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  21 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  22 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  23 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  24 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  25 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  26 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  27 //
  28
  29
  30 using System;
  31 using System.Data;
  32 using System.Data.OleDb;
  33 using System.Collections.Generic;
  34 using System.Text;
  35 using System.Configuration.Provider;
  36 using System.Diagnostics;
  37 using Mainsoft.Web.Hosting;
  38
  39 namespace Mainsoft.Web.Security
  40 {
  41         internal class DerbyDBSchema
  42         {
  43                 const string _currentSchemaVersion = "1.0";
  44                 static readonly object _lock = new object ();
  45
  46                 #region schema string array
  47                 static string [] schemaElements = new string [] {
  48                         // Applications table
  49                         @"CREATE TABLE aspnet_Applications (
  50                                 ApplicationId                           char(36)            NOT NULL PRIMARY KEY,
  51                                 ApplicationName                         varchar(256)        NOT NULL UNIQUE,
  52                                 LoweredApplicationName                  varchar(256)        NOT NULL UNIQUE,
  53                                 Description                             varchar(256)
  54                         )",
  55                         @"CREATE INDEX aspnet_App_Idx ON aspnet_Applications(LoweredApplicationName)",
  56
  57                         // Users table
  58                         @"CREATE TABLE aspnet_Users (
  59                                 ApplicationId                           char(36)            NOT NULL,
  60                                 UserId                                  char(36)            NOT NULL PRIMARY KEY,
  61                                 UserName                                varchar(256)        NOT NULL,
  62                                 LoweredUserName                         varchar(256)        NOT NULL,
  63                                 MobileAlias                             varchar(16)         DEFAULT NULL,
  64                                 IsAnonymous                             int                 NOT NULL DEFAULT 0,
  65                                 LastActivityDate                        timestamp           NOT NULL,
  66
  67                                 CONSTRAINT Users_AppId_PK FOREIGN KEY (ApplicationId) REFERENCES aspnet_Applications(ApplicationId)
  68                         )",
  69                         @"CREATE UNIQUE INDEX aspnet_Usr_Idx ON aspnet_Users(ApplicationId, LoweredUserName)",
  70                         @"CREATE INDEX aspnet_Usr_Idx2 ON aspnet_Users(ApplicationId, LastActivityDate)",
  71
  72                         // Membership table
  73                         @"CREATE TABLE aspnet_Membership (
  74                                 ApplicationId                           char(36)            NOT NULL,
  75                                 UserId                                  char(36)            NOT NULL PRIMARY KEY,
  76                                 Password                                varchar(128)        NOT NULL,
  77                                 PasswordFormat                          int                 NOT NULL DEFAULT 0,
  78                                 PasswordSalt                            varchar(128)        NOT NULL,
  79                                 MobilePIN                               varchar(16),
  80                                 Email                                   varchar(256),
  81                                 LoweredEmail                            varchar(256),
  82                                 PasswordQuestion                        varchar(256),
  83                                 PasswordAnswer                          varchar(128),
  84                                 IsApproved                              int                 NOT NULL,
  85                                 IsLockedOut                             int                 NOT NULL,
  86                                 CreateDate                              timestamp           NOT NULL,
  87                                 LastLoginDate                           timestamp           NOT NULL,
  88                                 LastPasswordChangedDate                 timestamp           NOT NULL,
  89                                 LastLockoutDate                         timestamp           NOT NULL,
  90                                 FailedPasswordAttemptCount              int                 NOT NULL,
  91                                 FailedPwdAttemptWindowStart             timestamp           NOT NULL,
  92                                 FailedPwdAnswerAttemptCount             int                 NOT NULL,
  93                                 FailedPwdAnswerAttWindowStart           timestamp           NOT NULL,
  94                                 Comment                                 varchar(256),
  95
  96                                 CONSTRAINT Member_AppId_PK FOREIGN KEY (ApplicationId) REFERENCES aspnet_Applications(ApplicationId),
  97                                 CONSTRAINT UserId_PK FOREIGN KEY (UserId) REFERENCES aspnet_Users(UserId)
  98                         )",
  99                         @"CREATE INDEX aspnet_Mbr_idx ON aspnet_Membership(ApplicationId, LoweredEmail)",
 100
 101                         // Roles table
 102                         @"CREATE TABLE aspnet_Roles (
 103                                 ApplicationId                           char(36)            NOT NULL,
 104                                 RoleId                                  char(36)            NOT NULL PRIMARY KEY,
 105                                 RoleName                                varchar(256)        NOT NULL,
 106                                 LoweredRoleName                         varchar(256)        NOT NULL,
 107                                 Description                             varchar(256),
 108
 109                                 CONSTRAINT Roles_AppId_PK FOREIGN KEY (ApplicationId) REFERENCES aspnet_Applications(ApplicationId)
 110                         )",
 111                         @"CREATE UNIQUE INDEX aspnet_Rls_idx ON aspnet_Roles(ApplicationId, LoweredRoleName)",
 112
 113                         // UsersInRoles table
 114                         @"CREATE TABLE aspnet_UsersInRoles (
 115                                 UserId                                  char(36)            NOT NULL,
 116                                 RoleId                                  char(36)            NOT NULL,
 117
 118                                 CONSTRAINT RoleId_UserId_PK FOREIGN KEY (UserId) REFERENCES aspnet_Users (UserId),
 119                                 CONSTRAINT UserId_RoleId_PK FOREIGN KEY (RoleId) REFERENCES aspnet_Roles (RoleId)
 120                         )",
 121                         @"ALTER TABLE aspnet_UsersInRoles ADD PRIMARY KEY (UserId, RoleId)",
 122                         @"CREATE INDEX aspnet_UsrRls_idx ON aspnet_UsersInRoles(RoleId)",
 123
 124                         // Profile table
 125                         @"CREATE TABLE aspnet_Profile (
 126                                 UserId                                  char(36)            NOT NULL PRIMARY KEY,
 127                                 PropertyNames                           long varchar        NOT NULL,
 128                                 PropertyValuesString                    long varchar        NOT NULL,
 129                                 PropertyValuesBinary                    blob                NOT NULL,
 130                                 LastUpdatedDate                         timestamp           NOT NULL,
 131
 132                                 CONSTRAINT Profile_UserId_PK FOREIGN KEY (UserId) REFERENCES aspnet_Users (UserId)
 133                         )",
 134
 135                         // Pathes table
 136                         //@"CREATE TABLE aspnet_Paths (
 137                         //      ApplicationId                           char(36)            NOT NULL,
 138                         //      PathId                                  char(36)            NOT NULL PRIMARY KEY,
 139                         //      Path                                    varchar(256)        NOT NULL,
 140                         //      LoweredPath                             varchar(256)        NOT NULL,
 141                         //
 142                         //      CONSTRAINT Paths_AppId_FK FOREIGN KEY (ApplicationId) REFERENCES aspnet_Applications(ApplicationId)
 143                         //)",
 144                         //@"CREATE UNIQUE INDEX aspnet_Pth_idx ON aspnet_Paths(ApplicationId, LoweredPath)",
 145
 146                         // Personalization tables
 147                         //@"CREATE TABLE aspnet_PersonalizationAllUsers (
 148                         //      PathId                                  char(36)            NOT NULL PRIMARY KEY,
 149                         //      PageSettings                            blob                NOT NULL,
 150                         //      LastUpdatedDate                         timestamp           NOT NULL,
 151                         //
 152                         //      CONSTRAINT PrsUsr_PathId_PK FOREIGN KEY (PathId) REFERENCES aspnet_Paths (PathId)
 153                         //)",
 154                         //@"CREATE TABLE aspnet_PersonalizationPerUser (
 155                         //      Id                                      char(36)            NOT NULL PRIMARY KEY,
 156                         //      PathId                                  char(36)            NOT NULL,
 157                         //      UserId                                  char(36)            NOT NULL,
 158                         //      PageSettings                            blob                NOT NULL,
 159                         //      LastUpdatedDate                         timestamp           NOT NULL,
 160                         //
 161                         //      CONSTRAINT PrsPUser_PathId_FK FOREIGN KEY (PathId) REFERENCES aspnet_Paths (PathId),
 162                         //      CONSTRAINT PrsPUser_UserId_FK FOREIGN KEY (UserId) REFERENCES aspnet_Users (UserId)
 163                         //)",
 164                         //@"CREATE UNIQUE INDEX PrsPUser_idx1 ON aspnet_PersonalizationPerUser(PathId,UserId)",
 165                         //@"CREATE UNIQUE INDEX PrsPUser_idx2 ON aspnet_PersonalizationPerUser(UserId,PathId)"
 166
 167                         // Version table
 168                         @"CREATE TABLE aspnet_Version (
 169                                 SchemaVersion                           varchar(10)             NOT NULL
 170                         )",
 171                         @"CREATE INDEX aspnet_Version_Idx ON aspnet_Version(SchemaVersion)",
 172                         @"INSERT INTO aspnet_Version VALUES ('1.0')"
 173                 };
 174                 #endregion
 175
 176                 public static void CheckSchema (string connectionString) {
 177                         string schemaVersion = GetSchemaVersion (connectionString);
 178                         if (schemaVersion != null) {
 179                                 if (string.CompareOrdinal (schemaVersion, _currentSchemaVersion) == 0)
 180                                         return;
 181                         }
 182                         else {
 183                                 lock (_lock) {
 184                                         schemaVersion = GetSchemaVersion (connectionString);
 185                                         if (schemaVersion == null) {
 186                                                 InitializeSchema (connectionString);
 187                                                 return;
 188                                         }
 189                                 }
 190                         }
 191
 192                         throw new ProviderException (String.Format ("Incorrect aspnetdb schema version: found '{0}', expected '{1}'.", schemaVersion, _currentSchemaVersion));
 193                 }
 194
 195                 static string GetSchemaVersion (string connectionString)
 196                 {
 197                         OleDbConnection connection = new OleDbConnection (connectionString);
 198
 199                         connection.Open ();
 200
 201                         using (connection) {
 202                                 OleDbCommand cmd = new OleDbCommand ("SELECT SchemaVersion FROM aspnet_Version", connection);
 203
 204                                 try {
 205                                         using (OleDbDataReader reader = cmd.ExecuteReader ()) {
 206                                                 if (reader.Read ())
 207                                                         return reader.GetString (0);
 208                                         }
 209                                 }
 210                                 catch { }
 211
 212                                 return null;
 213                         }
 214                 }
 215
 216                 static void InitializeSchema (string connectionString)
 217                 {
 218                         OleDbConnection connection = new OleDbConnection ();
 219                         connection.ConnectionString = connectionString;
 220
 221                         connection.Open ();
 222
 223                         using (connection) {
 224                                 for (int i = 0; i < schemaElements.Length; i++) {
 225                                         OleDbCommand cmd = new OleDbCommand (schemaElements [i], connection);
 226                                         cmd.ExecuteNonQuery ();
 227                                 }
 228                         }
 229                 }
 230         }
 231
 232         internal class DerbyUnloadManager
 233         {
 234                 public enum DerbyShutDownPolicy
 235                 {
 236                         Default,
 237                         Never,
 238                         Database,
 239                         System
 240                 }
 241
 242                 readonly string _connectionString;
 243                 readonly DerbyShutDownPolicy _policy;
 244
 245                 DerbyUnloadManager (string connectionString, DerbyShutDownPolicy policy) {
 246                         _connectionString = connectionString;
 247                         _policy = policy;
 248                 }
 249
 250                 public static void RegisterUnloadHandler (string connectionString, DerbyShutDownPolicy policy) {
 251                         if (policy == DerbyShutDownPolicy.Never)
 252                                 return;
 253
 254                         if (connectionString.IndexOf("org.apache.derby.jdbc.EmbeddedDriver", StringComparison.Ordinal) < 0)
 255                                 return;
 256
 257                         DerbyUnloadManager derbyMan = new DerbyUnloadManager (connectionString, policy);
 258                         AppDomain.CurrentDomain.DomainUnload += new EventHandler (derbyMan.UnloadHandler);
 259                 }
 260
 261                 public void UnloadHandler (object sender, EventArgs e)
 262                 {
 263                         string shutUrl;
 264
 265                         switch (_policy) {
 266                         case DerbyShutDownPolicy.Never:
 267                                 return;
 268                         case DerbyShutDownPolicy.Database:
 269                                 shutUrl = GetConnectionProperty (_connectionString, "JdbcURL");
 270                                 break;
 271                         case DerbyShutDownPolicy.System:
 272                                 shutUrl = "JdbcURL=jdbc:derby:";
 273                                 break;
 274                         default:
 275                         case DerbyShutDownPolicy.Default:
 276                                 java.lang.ClassLoader contextLoader = (java.lang.ClassLoader) AppDomain.CurrentDomain.GetData (J2EEConsts.CLASS_LOADER);
 277                                 java.lang.Class klass = contextLoader.loadClass ("org.apache.derby.jdbc.EmbeddedDriver");
 278                                 if (klass == null)
 279                                         return;
 280
 281                                 shutUrl = (klass.getClassLoader () == contextLoader) ?
 282                                         "JdbcURL=jdbc:derby:" : GetConnectionProperty (_connectionString, "JdbcURL");
 283
 284                                 break;
 285                         }
 286
 287                         const string shuttingConnection = "JdbcDriverClassName=org.apache.derby.jdbc.EmbeddedDriver;{0};shutdown=true";
 288
 289                         if (!String.IsNullOrEmpty (shutUrl)) {
 290                                 try {
 291                                         new OleDbConnection (String.Format (shuttingConnection, shutUrl)).Open ();
 292                                 }
 293                                 catch (Exception ex) {
 294                                         Trace.Write (ex.ToString ());
 295                                 }
 296                         }
 297                 }
 298
 299                 static string GetConnectionProperty (string connectionString, string name) {
 300                         if (String.IsNullOrEmpty (connectionString))
 301                                 return null;
 302
 303                         string [] parts = connectionString.Split (';');
 304                         foreach (string part in parts)
 305                                 if (part.StartsWith (name, StringComparison.OrdinalIgnoreCase))
 306                                         return part;
 307
 308                         return null;
 309                 }
 310         }
 311 }
 312