mcs/class/System.Web/SQLiteProviders_DatabaseSchema.sql

   1 CREATE TABLE "Users" (
   2         "pId"                                           character(36)           NOT NULL,
   3         "Username"                                      character varying(255)  NOT NULL,
   4         "ApplicationName"                               character varying(255)  NOT NULL,
   5         "Email"                                         character varying(128)  NULL,
   6         "Comment"                                       character varying(128)  NULL,
   7         "Password"                                      character varying(255)  NOT NULL,
   8         "PasswordQuestion"                              character varying(255)  NULL,
   9         "PasswordAnswer"                                character varying(255)  NULL,
  10         "IsApproved"                                    boolean                 NULL,
  11         "LastActivityDate"                              timestamptz             NULL,
  12         "LastLoginDate"                                 timestamptz             NULL,
  13         "LastPasswordChangedDate"                       timestamptz             NULL,
  14         "CreationDate"                                  timestamptz             NULL,
  15         "IsOnLine"                                      boolean                 NULL,
  16         "IsLockedOut"                                   boolean                 NULL,
  17         "LastLockedOutDate"                             timestamptz             NULL,
  18         "FailedPasswordAttemptCount"                    integer                 NULL,
  19         "FailedPasswordAttemptWindowStart"              timestamptz             NULL,
  20         "FailedPasswordAnswerAttemptCount"              integer                 NULL,
  21         "FailedPasswordAnswerAttemptWindowStart"        timestamptz             NULL,
  22         CONSTRAINT users_pkey PRIMARY KEY ("pId"),
  23         CONSTRAINT users_username_application_unique UNIQUE ("Username", "ApplicationName")
  24 );
  25
  26 CREATE INDEX users_email_index ON "Users" ("Email");
  27 CREATE INDEX users_islockedout_index ON "Users" ("IsLockedOut");
  28
  29 CREATE TABLE "Roles" (
  30         "Rolename"                                      character varying(255)  NOT NULL,
  31         "ApplicationName"                               character varying(255)  NOT NULL,
  32         CONSTRAINT roles_pkey PRIMARY KEY ("Rolename", "ApplicationName")
  33 );
  34
  35 CREATE TABLE "UsersInRoles" (
  36         "Username"                                      character varying(255)  NOT NULL,
  37         "Rolename"                                      character varying(255)  NOT NULL,
  38         "ApplicationName"                               character varying(255)  NOT NULL,
  39         CONSTRAINT usersinroles_pkey PRIMARY KEY ("Username", "Rolename", "ApplicationName"),
  40         CONSTRAINT usersinroles_username_fkey FOREIGN KEY ("Username", "ApplicationName") REFERENCES "Users" ("Username", "ApplicationName") ON DELETE CASCADE,
  41         CONSTRAINT usersinroles_rolename_fkey FOREIGN KEY ("Rolename", "ApplicationName") REFERENCES "Roles" ("Rolename", "ApplicationName") ON DELETE CASCADE
  42 );
  43
  44 CREATE TABLE "Profiles" (
  45         "pId"                                           character(36)           NOT NULL,
  46         "Username"                                      character varying(255)  NOT NULL,
  47         "ApplicationName"                               character varying(255)  NOT NULL,
  48         "IsAnonymous"                                   boolean                 NULL,
  49         "LastActivityDate"                              timestamptz             NULL,
  50         "LastUpdatedDate"                               timestamptz             NULL,
  51         CONSTRAINT profiles_pkey PRIMARY KEY ("pId"),
  52         CONSTRAINT profiles_username_application_unique UNIQUE ("Username", "ApplicationName"),
  53         CONSTRAINT profiles_username_fkey FOREIGN KEY ("Username", "ApplicationName") REFERENCES "Users" ("Username", "ApplicationName") ON DELETE CASCADE
  54 );
  55
  56 CREATE INDEX profiles_isanonymous_index ON "Profiles" ("IsAnonymous");
  57
  58 CREATE TABLE "ProfileData" (
  59         "pId"                                           character(36)           NOT NULL,
  60         "Profile"                                       character(36)           NOT NULL,
  61         "Name"                                          character varying(255)  NOT NULL,
  62         "ValueString"                                   text                    NULL,
  63         "ValueBinary"                                   bytea                   NULL,
  64         CONSTRAINT profiledata_pkey PRIMARY KEY ("pId"),
  65         CONSTRAINT profiledata_profile_name_unique UNIQUE ("Profile", "Name"),
  66         CONSTRAINT profiledata_profile_fkey FOREIGN KEY ("Profile") REFERENCES "Profiles" ("pId") ON DELETE CASCADE
  67 );
  68
  69 CREATE TABLE "Sessions" (
  70         "SessionId"                                     character varying(80)   NOT NULL,
  71         "ApplicationName"                               character varying(255)  NOT NULL,
  72         "Created"                                       timestamptz             NOT NULL,
  73         "Expires"                                       timestamptz             NOT NULL,
  74         "Timeout"                                       integer                 NOT NULL,
  75         "Locked"                                        boolean                 NOT NULL,
  76         "LockId"                                        integer                 NOT NULL,
  77         "LockDate"                                      timestamptz             NOT NULL,
  78         "Data"                                          text                    NULL,
  79         "Flags"                                         integer                 NOT NULL,
  80         CONSTRAINT sessions_pkey PRIMARY KEY ("SessionId", "ApplicationName")
  81 );