{"action":"created","issue":{"url":"https://api.github.com/repos/RuddockHouse/RuddockWebsite/issues/110","labels_url":"https://api.github.com/repos/RuddockHouse/RuddockWebsite/issues/110/labels{/name}","comments_url":"https://api.github.com/repos/RuddockHouse/RuddockWebsite/issues/110/comments","events_url":"https://api.github.com/repos/RuddockHouse/RuddockWebsite/issues/110/events","html_url":"https://github.com/RuddockHouse/RuddockWebsite/pull/110","id":108084460,"number":110,"title":"Member table style cleanup","user":{"login":"dkong1796","id":1891237,"avatar_url":"https://avatars.githubusercontent.com/u/1891237?v=3","gravatar_id":"","url":"https://api.github.com/users/dkong1796","html_url":"https://github.com/dkong1796","followers_url":"https://api.github.com/users/dkong1796/followers","following_url":"https://api.github.com/users/dkong1796/following{/other_user}","gists_url":"https://api.github.com/users/dkong1796/gists{/gist_id}","starred_url":"https://api.github.com/users/dkong1796/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dkong1796/subscriptions","organizations_url":"https://api.github.com/users/dkong1796/orgs","repos_url":"https://api.github.com/users/dkong1796/repos","events_url":"https://api.github.com/users/dkong1796/events{/privacy}","received_events_url":"https://api.github.com/users/dkong1796/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":2,"created_at":"2015-09-24T08:34:10Z","updated_at":"2015-09-24T18:31:44Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/RuddockHouse/RuddockWebsite/pulls/110","html_url":"https://github.com/RuddockHouse/RuddockWebsite/pull/110","diff_url":"https://github.com/RuddockHouse/RuddockWebsite/pull/110.diff","patch_url":"https://github.com/RuddockHouse/RuddockWebsite/pull/110.patch"},"body":"- Replace `members_current` and `members_alumni` views with ones that only select the primary key (currently they are `SELECT *`)\r\n- Add new `members_extra` view for providing additional data from the members table. Currently this only contains a \"name\" column (logic for generating a name from `first_name`, `last_name`, and/or `alternate_name`). This is so that this logic is not repeated multiple times in the codebase.\r\n- Rename columns for better/consistent style\r\n- Drop unused/unnecessary columns\r\n\r\nResult schema:\r\n```\r\nMariaDB [ruddweb_test]> describe members;\r\n+--------------------+--------------+------+-----+---------+----------------+\r\n| Field              | Type         | Null | Key | Default | Extra          |\r\n+--------------------+--------------+------+-----+---------+----------------+\r\n| user_id            | int(11)      | NO   | PRI | NULL    | auto_increment |\r\n| uid                | varchar(10)  | NO   | UNI | NULL    |                |\r\n| first_name         | varchar(255) | NO   |     | NULL    |                |\r\n| last_name          | varchar(255) | NO   |     | NULL    |                |\r\n| alternate_name     | varchar(255) | YES  |     | NULL    |                |\r\n| birthday           | date         | YES  |     | NULL    |                |\r\n| email              | varchar(64)  | NO   |     | NULL    |                |\r\n| matriculation_year | year(4)      | YES  |     | NULL    |                |\r\n| graduation_year    | year(4)      | YES  |     | NULL    |                |\r\n| msc                | int(11)      | YES  |     | NULL    |                |\r\n| phone              | varchar(64)  | YES  |     | NULL    |                |\r\n| building           | varchar(32)  | YES  |     | NULL    |                |\r\n| room_number        | int(11)      | YES  |     | NULL    |                |\r\n| member_type        | int(11)      | NO   | MUL | NULL    |                |\r\n| major              | varchar(16)  | YES  |     | NULL    |                |\r\n| create_account_key | char(32)     | YES  |     | NULL    |                |\r\n+--------------------+--------------+------+-----+---------+----------------+\r\n```\r\n\r\nDatabase changes:\r\n```sql\r\nALTER TABLE members\r\nCHANGE fname first_name VARCHAR(255) NOT NULL;\r\n\r\nALTER TABLE members\r\nCHANGE lname last_name VARCHAR(255) NOT NULL;\r\n\r\nALTER TABLE members\r\nCHANGE nickname alternate_name VARCHAR(255) NOT NULL;\r\n\r\nALTER TABLE members\r\nDROP usenickname;\r\n\r\nALTER TABLE members\r\nCHANGE bday birthday DATE;\r\n\r\nALTER TABLE members\r\nDROP email2;\r\n\r\nALTER TABLE members\r\nDROP status;\r\n\r\nALTER TABLE members\r\nCHANGE matriculate_year matriculation_year YEAR;\r\n\r\nALTER TABLE members\r\nCHANGE grad_year graduation_year YEAR;\r\n\r\nALTER TABLE members\r\nCHANGE room_num room_number INTEGER;\r\n\r\nALTER TABLE members\r\nCHANGE membership_type member_type INTEGER NOT NULL;\r\n\r\nALTER TABLE members\r\nMODIFY uid VARCHAR(10) NOT NULL UNIQUE AFTER user_id;\r\n\r\nALTER TABLE members\r\nDROP isabroad;\r\n\r\nALTER TABLE members\r\nMODIFY msc INTEGER;\r\n\r\nDROP VIEW IF EXISTS members_current;\r\nDROP VIEW IF EXISTS members_alumni;\r\nDROP VIEW IF EXISTS members_extra;\r\n\r\nCREATE VIEW members_current AS\r\n  SELECT user_id\r\n  FROM members\r\n  WHERE NOW() < CONCAT(grad_year, '-07-01');\r\n\r\nCREATE VIEW members_alumni AS\r\n  SELECT user_id\r\n  FROM members\r\n  WHERE NOW() >= CONCAT(grad_year, '-07-01');\r\n\r\nCREATE VIEW members_extra AS\r\n  SELECT user_id, CONCAT(IFNULL(alternate_name, first_name), ' ', last_name) AS name\r\n  FROM members;\r\n```"},"comment":{"url":"https://api.github.com/repos/RuddockHouse/RuddockWebsite/issues/comments/143013109","html_url":"https://github.com/RuddockHouse/RuddockWebsite/pull/110#issuecomment-143013109","issue_url":"https://api.github.com/repos/RuddockHouse/RuddockWebsite/issues/110","id":143013109,"user":{"login":"dkong1796","id":1891237,"avatar_url":"https://avatars.githubusercontent.com/u/1891237?v=3","gravatar_id":"","url":"https://api.github.com/users/dkong1796","html_url":"https://github.com/dkong1796","followers_url":"https://api.github.com/users/dkong1796/followers","following_url":"https://api.github.com/users/dkong1796/following{/other_user}","gists_url":"https://api.github.com/users/dkong1796/gists{/gist_id}","starred_url":"https://api.github.com/users/dkong1796/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dkong1796/subscriptions","organizations_url":"https://api.github.com/users/dkong1796/orgs","repos_url":"https://api.github.com/users/dkong1796/repos","events_url":"https://api.github.com/users/dkong1796/events{/privacy}","received_events_url":"https://api.github.com/users/dkong1796/received_events","type":"User","site_admin":false},"created_at":"2015-09-24T18:31:44Z","updated_at":"2015-09-24T18:31:44Z","body":"Haven't actually changed any of the code to use the updated schema yet. I'm going to be adding tests for all state-preserving endpoints that don't already have tests, and testing things that actually change the db (see #109) is something I have some ideas for but won't be part of this pr."}}