Create User/Session Schema

[xHergz]

Description:

Create the tables to hold the information about a user obtained on a sign up and the session information from a log in.

Tasks:

• [x] Determine the schema
• [x] Create the User table
  • User_Id INT
  • Email VARCHAR(256)
  • Password_Hash VARCHAR(256)
  • Display_Name VARCHAR(50)
  • First_Name VARCHAR(100)
  • Last_Name VARCHAR(100)
  • Deactivated TINYINT
• [x] Create the Session table
  • Session_Id INT
  • User_Id INT
  • Session_Key VARCHAR (256)
  • Created DATETIME
  • Expires DATETIME
  • Ip_Address VARCHAR(45)
• [x] Create stored procedure to sign up a user
  • CreateUser(email, password_hash, display_name, first_name, last_name)
• [x] Create stored procedure to login a user
  • Login(username, password_hash)
  • Logout(session_key)
• [x] Determine stored procedures needed for user maintenance (ie change password etc)
  • UpdateUser(session_key, email, password_hash, display_name, first_name, last_name)
  • DeactivateUser(session_key, email)

    Tests:

• [x] Stored proc successfully adds a user Test Case 1
  • CALL CreateUser('test@email.com', 'aaaa', 'DisplayName', 'First', 'Last');
• [x] Stored proc successfully creates session Test Case 1
  • CALL CreateSession(<Id returned from creating user>, '192.168.0.1');
• [x] Stored proc successfully updates user Test Case 1
  • CALL UpdateUser('<Session key from creating session>', <Id returned from creating user>, NULL, NULL, 'NewDisplayName', NULL, NULL);
• [x] Stored proc successfully blocks duplicate display names Test Case 1
  • CALL UpdateUser('<Session key from creating session>', <Id returned from creating user>, NULL, NULL, 'NewDisplayName', NULL, NULL);
• [x] Stored proc successfully deactivates user Test Case 1
  • CALL DeactivateUser('<Session key from creating session>', <Id returned from creating user>);
• [x] Stored proc successfully deactivates session Test Case 1
  • CALL DeactivateSession('<Session key from creating session>');

[xHergz]

Added a System_Configuration table to hold some different config values. In this case there will need to be a value of how long to set the default session expiry.