github.com/EngineerKamesh/gofullstack@v0.0.0-20180609171605-d41341d7d4ee/volume4/section2/gopherface/config/gopherfacedb.sql (about) 1 /* ***************************************************************************** 2 // Setup preferences 3 // ****************************************************************************/ 4 SET NAMES utf8 COLLATE 'utf8_unicode_ci'; 5 SET time_zone = '-07:00'; 6 SET CHARACTER SET utf8; 7 8 /* ***************************************************************************** 9 // Remove database (if it already exists) 10 // ****************************************************************************/ 11 DROP DATABASE IF EXISTS gopherfacedb; 12 13 /* ***************************************************************************** 14 // Create new database 15 // ****************************************************************************/ 16 CREATE DATABASE gopherfacedb DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci; 17 USE gopherfacedb; 18 19 20 /* ***************************************************************************** 21 // Create the table(s) 22 // ****************************************************************************/ 23 24 DROP TABLE IF EXISTS user; 25 26 CREATE TABLE user ( 27 id TINYINT(1) UNSIGNED NOT NULL AUTO_INCREMENT, 28 username VARCHAR(18) NOT NULL, 29 uuid VARCHAR(64) NOT NULL, 30 first_name VARCHAR(64) NOT NULL, 31 last_name VARCHAR(64) NOT NULL, 32 password_hash CHAR(64) NOT NULL, 33 email VARCHAR(255) NOT NULL, 34 created_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 35 updated_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 36 UNIQUE (username), 37 PRIMARY KEY (id) 38 ); 39 40 41 DROP TABLE IF EXISTS user_profile; 42 43 CREATE TABLE user_profile( 44 uuid VARCHAR(64) NOT NULL, 45 about VARCHAR(255) NOT NULL DEFAULT "", 46 location VARCHAR(64) NOT NULL DEFAULT "", 47 interests VARCHAR(255) NOT NULL DEFAULT "", 48 profile_image_path VARCHAR(255) NOT NULL DEFAULT "", 49 created_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 50 updated_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 51 UNIQUE(uuid), 52 PRIMARY KEY (uuid) 53 ); 54 55 56 DROP TABLE IF EXISTS friend_relation; 57 58 CREATE TABLE friend_relation( 59 owner_uuid VARCHAR(64) NOT NULL, 60 friend_uuid VARCHAR(64) NOT NULL, 61 created_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 62 updated_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 63 UNIQUE KEY relation (owner_uuid, friend_uuid) 64 ); 65 66 67 DROP TABLE IF EXISTS post; 68 69 CREATE TABLE post( 70 uuid VARCHAR(64) NOT NULL, 71 title VARCHAR(65) NOT NULL, 72 body VARCHAR(255) NOT NULL, 73 mood INT, 74 created_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 75 updated_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 76 );