Devart Forums

Code: Select all

CREATE TABLE sessions (
  sessionid VARCHAR(80) NOT NULL,
  applicationname VARCHAR(255) NOT NULL,
  created TIMESTAMP NOT NULL,
  expires TIMESTAMP NOT NULL,
  lockdate TIMESTAMP NOT NULL,
  lockid INT NOT NULL,
  timeout INT NOT NULL,
  locked BIT NOT NULL,
  sessionitems TEXT,
  flags INT NOT NULL,

  CONSTRAINT sessions_sessionid_pk PRIMARY KEY (sessionid)
);

CREATE TABLE aspnet_users (
  userid BIT(128) NOT NULL,
  applicationname VARCHAR(255) NOT NULL,
  username VARCHAR(255),
  lastactivitydate TIMESTAMP,
  isanonymous BIT,
  CONSTRAINT aspnet_users_userid_pk PRIMARY KEY (userid),
  CONSTRAINT aspnet_users_username_pk UNIQUE (username, applicationname)
);


CREATE TABLE membership (
  userid BIT(128) NOT NULL,
  password VARCHAR(255),
  email VARCHAR(255),
  passwordquestion VARCHAR(255),
  passwordanswer VARCHAR(255),
  comments VARCHAR(255),
  isapproved BIT,
  islockedout BIT,
  creationdate TIMESTAMP,
  lastlogindate TIMESTAMP,
  lastpasswordchangeddate TIMESTAMP,
  lastlockoutdate TIMESTAMP,
  failedpasswordattemptcount INT,
  failedpasswordattemptstart TIMESTAMP,
  failedpasswordanswercount INT,
  failedpasswordanswerstart TIMESTAMP,

  CONSTRAINT membership_userid_pk PRIMARY KEY (userid),
  CONSTRAINT membership_userid_ref FOREIGN KEY (userid) REFERENCES aspnet_users (userid)
);

CREATE TABLE roles (
  applicationname VARCHAR(255) NOT NULL,
  roleid BIT(128) NOT NULL,
  rolename VARCHAR(255) NOT NULL,

  CONSTRAINT roles_roleid_pk PRIMARY KEY (roleid),
  CONSTRAINT roles_rolename_key UNIQUE (rolename, applicationname)
);

CREATE TABLE usertorole (
  userid BIT(128),
  roleid BIT(128),

  CONSTRAINT usertorole_userid_ref FOREIGN KEY (userid) REFERENCES aspnet_users (userid),
  CONSTRAINT usertorole_roleid_ref FOREIGN KEY (roleid) REFERENCES roles (roleid),
  CONSTRAINT useridroleid_key UNIQUE (userid, roleid)
);


CREATE TABLE profiles (
  userid BIT(128),
  propertynames            BYTEA NOT NULL,
  propertyvaluesstring     BYTEA NOT NULL,
  propertyvaluesbinary     BYTEA NULL,
  lastupdateddate          TIMESTAMP NOT NULL,

  CONSTRAINT profiles_userid_ref FOREIGN KEY (userid) REFERENCES aspnet_users (userid),
  CONSTRAINT profiles_userid_key UNIQUE (userid)
);

Code: Select all

DROP TABLE usertorole;
DROP TABLE sessions;
DROP TABLE membership;
DROP TABLE roles;
DROP TABLE profiles;
DROP TABLE aspnet_users;