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 #if NET_2_0
  30
  31 using System;
  32 using System.Data;
  33 using System.Data.OleDb;
  34 using System.Collections.Generic;
  35 using System.Text;
  36
  37 namespace Mainsoft.Web.Security
  38 {
  39         internal class DerbyDBSchema
  40         {\r
  41                 const string _currentSchemaVersion = "1.0";\r
  42                 static object _lock = "DerbyDBSchema";
  43
  44                 #region schema string array
  45                 static string [] schemaElements = new string [] {
  46                         // Applications table
  47                         @"CREATE TABLE aspnet_Applications (
  48                                 ApplicationId                           char(36)            NOT NULL PRIMARY KEY,
  49                                 ApplicationName                         varchar(256)        NOT NULL UNIQUE,
  50                                 LoweredApplicationName                  varchar(256)        NOT NULL UNIQUE,
  51                                 Description                             varchar(256)
  52                         )",
  53                         @"CREATE INDEX aspnet_App_Idx ON aspnet_Applications(LoweredApplicationName)",
  54
  55                         // Users table
  56                         @"CREATE TABLE aspnet_Users (
  57                                 ApplicationId                           char(36)            NOT NULL,
  58                                 UserId                                  char(36)            NOT NULL PRIMARY KEY,
  59                                 UserName                                varchar(256)        NOT NULL,
  60                                 LoweredUserName                         varchar(256)        NOT NULL,
  61                                 MobileAlias                             varchar(16)         DEFAULT NULL,
  62                                 IsAnonymous                             int                 NOT NULL DEFAULT 0,
  63                                 LastActivityDate                        timestamp           NOT NULL,
  64
  65                                 CONSTRAINT Users_AppId_PK FOREIGN KEY (ApplicationId) REFERENCES aspnet_Applications(ApplicationId)
  66                         )",
  67                         @"CREATE UNIQUE INDEX aspnet_Usr_Idx ON aspnet_Users(ApplicationId, LoweredUserName)",
  68                         @"CREATE INDEX aspnet_Usr_Idx2 ON aspnet_Users(ApplicationId, LastActivityDate)",
  69
  70                         // Membership table
  71                         @"CREATE TABLE aspnet_Membership (
  72                                 ApplicationId                           char(36)            NOT NULL,
  73                                 UserId                                  char(36)            NOT NULL PRIMARY KEY,
  74                                 Password                                varchar(128)        NOT NULL,
  75                                 PasswordFormat                          int                 NOT NULL DEFAULT 0,
  76                                 PasswordSalt                            varchar(128)        NOT NULL,
  77                                 MobilePIN                               varchar(16),
  78                                 Email                                   varchar(256),
  79                                 LoweredEmail                            varchar(256),
  80                                 PasswordQuestion                        varchar(256),
  81                                 PasswordAnswer                          varchar(128),
  82                                 IsApproved                              int                 NOT NULL,
  83                                 IsLockedOut                             int                 NOT NULL,
  84                                 CreateDate                              timestamp           NOT NULL,
  85                                 LastLoginDate                           timestamp           NOT NULL,
  86                                 LastPasswordChangedDate                 timestamp           NOT NULL,
  87                                 LastLockoutDate                         timestamp           NOT NULL,
  88                                 FailedPasswordAttemptCount              int                 NOT NULL,
  89                                 FailedPwdAttemptWindowStart             timestamp           NOT NULL,
  90                                 FailedPwdAnswerAttemptCount             int                 NOT NULL,
  91                                 FailedPwdAnswerAttWindowStart           timestamp           NOT NULL,
  92                                 Comment                                 varchar(256),
  93
  94                                 CONSTRAINT Member_AppId_PK FOREIGN KEY (ApplicationId) REFERENCES aspnet_Applications(ApplicationId),
  95                                 CONSTRAINT UserId_PK FOREIGN KEY (UserId) REFERENCES aspnet_Users(UserId)
  96                         )",
  97                         @"CREATE INDEX aspnet_Mbr_idx ON aspnet_Membership(ApplicationId, LoweredEmail)",
  98
  99                         // Roles table
 100                         @"CREATE TABLE aspnet_Roles (
 101                                 ApplicationId                           char(36)            NOT NULL,
 102                                 RoleId                                  char(36)            NOT NULL PRIMARY KEY,
 103                                 RoleName                                varchar(256)        NOT NULL,
 104                                 LoweredRoleName                         varchar(256)        NOT NULL,
 105                                 Description                             varchar(256),
 106
 107                                 CONSTRAINT Roles_AppId_PK FOREIGN KEY (ApplicationId) REFERENCES aspnet_Applications(ApplicationId)
 108                         )",
 109                         @"CREATE UNIQUE INDEX aspnet_Rls_idx ON aspnet_Roles(ApplicationId, LoweredRoleName)",
 110
 111                         // UsersInRoles table
 112                         @"CREATE TABLE aspnet_UsersInRoles (
 113                                 UserId                                  char(36)            NOT NULL,
 114                                 RoleId                                  char(36)            NOT NULL,
 115
 116                                 CONSTRAINT RoleId_UserId_PK FOREIGN KEY (UserId) REFERENCES aspnet_Users (UserId),
 117                                 CONSTRAINT UserId_RoleId_PK FOREIGN KEY (RoleId) REFERENCES aspnet_Roles (RoleId)
 118                         )",
 119                         @"ALTER TABLE aspnet_UsersInRoles ADD PRIMARY KEY (UserId, RoleId)",
 120                         @"CREATE INDEX aspnet_UsrRls_idx ON aspnet_UsersInRoles(RoleId)",
 121
 122                         // Profile table
 123                         @"CREATE TABLE aspnet_Profile (
 124                                 UserId                                  char(36)            NOT NULL PRIMARY KEY,
 125                                 PropertyNames                           long varchar        NOT NULL,
 126                                 PropertyValuesString                    long varchar        NOT NULL,
 127                                 PropertyValuesBinary                    blob                NOT NULL,
 128                                 LastUpdatedDate                         timestamp           NOT NULL,
 129
 130                                 CONSTRAINT Profile_UserId_PK FOREIGN KEY (UserId) REFERENCES aspnet_Users (UserId)
 131                         )",
 132
 133                         // Pathes table
 134                         //@"CREATE TABLE aspnet_Paths (
 135                         //      ApplicationId                           char(36)            NOT NULL,
 136                         //      PathId                                  char(36)            NOT NULL PRIMARY KEY,
 137                         //      Path                                    varchar(256)        NOT NULL,
 138                         //      LoweredPath                             varchar(256)        NOT NULL,
 139                         //
 140                         //      CONSTRAINT Paths_AppId_FK FOREIGN KEY (ApplicationId) REFERENCES aspnet_Applications(ApplicationId)
 141                         //)",
 142                         //@"CREATE UNIQUE INDEX aspnet_Pth_idx ON aspnet_Paths(ApplicationId, LoweredPath)",
 143
 144                         // Personalization tables
 145                         //@"CREATE TABLE aspnet_PersonalizationAllUsers (
 146                         //      PathId                                  char(36)            NOT NULL PRIMARY KEY,
 147                         //      PageSettings                            blob                NOT NULL,
 148                         //      LastUpdatedDate                         timestamp           NOT NULL,
 149                         //
 150                         //      CONSTRAINT PrsUsr_PathId_PK FOREIGN KEY (PathId) REFERENCES aspnet_Paths (PathId)
 151                         //)",
 152                         //@"CREATE TABLE aspnet_PersonalizationPerUser (
 153                         //      Id                                      char(36)            NOT NULL PRIMARY KEY,
 154                         //      PathId                                  char(36)            NOT NULL,
 155                         //      UserId                                  char(36)            NOT NULL,
 156                         //      PageSettings                            blob                NOT NULL,
 157                         //      LastUpdatedDate                         timestamp           NOT NULL,
 158                         //
 159                         //      CONSTRAINT PrsPUser_PathId_FK FOREIGN KEY (PathId) REFERENCES aspnet_Paths (PathId),
 160                         //      CONSTRAINT PrsPUser_UserId_FK FOREIGN KEY (UserId) REFERENCES aspnet_Users (UserId)
 161                         //)",
 162                         //@"CREATE UNIQUE INDEX PrsPUser_idx1 ON aspnet_PersonalizationPerUser(PathId,UserId)",
 163                         //@"CREATE UNIQUE INDEX PrsPUser_idx2 ON aspnet_PersonalizationPerUser(UserId,PathId)"
 164
 165                         // Version table
 166                         @"CREATE TABLE aspnet_Version (
 167                                 SchemaVersion                           varchar(10)             NOT NULL
 168                         )",
 169                         @"CREATE INDEX aspnet_Version_Idx ON aspnet_Version(SchemaVersion)",
 170                         @"INSERT INTO aspnet_Version VALUES ('1.0')"
 171                 };
 172                 #endregion\r
 173 \r
 174                 public static void CheckSchema (string connectionString)\r
 175                 {\r
 176                         string schemaVersion = GetSchemaVersion (connectionString);\r
 177                         if (schemaVersion != null)\r
 178                                 if (string.CompareOrdinal (schemaVersion, _currentSchemaVersion) == 0)\r
 179                                         return;\r
 180                                 else\r
 181                                         throw new Exception ("Incorrect aspnetdb schema version.");\r
 182 \r
 183                         lock (_lock) {\r
 184                                 if (GetSchemaVersion (connectionString) != _currentSchemaVersion) {\r
 185                                         InitializeSchema (connectionString);\r
 186                                 }\r
 187                         }\r
 188                 }\r
 189 \r
 190                 static string GetSchemaVersion (string connectionString)\r
 191                 {\r
 192                         OleDbConnection connection = new OleDbConnection ();\r
 193                         connection.ConnectionString = connectionString;\r
 194 \r
 195                         try {\r
 196                                 connection.Open ();\r
 197                         }\r
 198                         catch (Exception) {\r
 199                                 return null;\r
 200                         }\r
 201 \r
 202                         using (connection) {\r
 203                                 OleDbCommand cmd = new OleDbCommand ("SELECT SchemaVersion FROM aspnet_Version", connection);\r
 204                                 try {\r
 205                                         using (OleDbDataReader reader = cmd.ExecuteReader ()) {\r
 206                                                 if (reader.Read ())\r
 207                                                         return reader.GetString (0);\r
 208                                         }\r
 209                                 }\r
 210                                 catch (Exception) { }\r
 211                                 return null;\r
 212                         }\r
 213                 }\r
 214 \r
 215                 static void InitializeSchema (string connectionString)\r
 216                 {\r
 217                         if (connectionString.ToLower ().IndexOf ("create=true") < 0) {\r
 218                                 if (!connectionString.Trim ().EndsWith (";"))\r
 219                                         connectionString += ";";\r
 220 \r
 221                                 connectionString += "create=true";\r
 222                         }\r
 223 \r
 224                         OleDbConnection connection = new OleDbConnection ();\r
 225                         connection.ConnectionString = connectionString;\r
 226 \r
 227                         connection.Open ();\r
 228 \r
 229                         using (connection) {\r
 230                                 for (int i = 0; i < schemaElements.Length; i++) {\r
 231                                         OleDbCommand cmd = new OleDbCommand (schemaElements [i], connection);\r
 232                                         cmd.ExecuteNonQuery ();\r
 233                                 }\r
 234                         }\r
 235                 }
 236
 237                 public static void RegisterUnloadHandler (string connectionString)
 238                 {
 239                         DerbyUnloadManager derbyMan = new DerbyUnloadManager (connectionString);
 240                         derbyMan.RegisterUnloadHandler ();
 241                 }
 242         }
 243
 244         internal class DerbyUnloadManager
 245         {
 246                 private string _releaseString = null;
 247
 248                 public DerbyUnloadManager (string connectionString)
 249                 {
 250                         _releaseString = connectionString;
 251
 252                         string [] parts = _releaseString.Split (';');
 253                         bool found = false;
 254
 255                         for (int i=0; i<parts.Length; i++)
 256                         {
 257                                 if (parts[i].ToLower().Trim().StartsWith("create"))
 258                                 {
 259                                         parts[i] = parts[i].ToLower().Trim().Replace("create", "shutdown");
 260                                         found = true;
 261                                         break;
 262                                 }
 263                         }
 264                         if (found)
 265                                 _releaseString = string.Join (";", parts);
 266                         else
 267                         {
 268                                 if (!_releaseString.Trim ().EndsWith (";"))
 269                                         _releaseString += ";";
 270
 271                                 _releaseString += "shutdown=true";
 272                         }
 273                 }
 274
 275                 public void UnloadHandler (object sender, EventArgs e)
 276                 {
 277                         OleDbConnection connection = new OleDbConnection (_releaseString);
 278
 279                         try {
 280                                 connection.Open ();
 281                         }
 282                         catch (Exception ex) {
 283 #if TRACE\r
 284                                 Console.Write (ex.ToString ());
 285 #endif
 286                         }
 287                 }
 288
 289                 public void RegisterUnloadHandler ()
 290                 {
 291                         AppDomain.CurrentDomain.DomainUnload += new EventHandler (UnloadHandler);
 292                 }
 293
 294                 public void UnregisterUnloadHandler ()
 295                 {
 296                         AppDomain.CurrentDomain.DomainUnload -= new EventHandler (UnloadHandler);
 297                 }
 298         }
 299 }
 300
 301 #endif