github.com/EngineerKamesh/gofullstack@v0.0.0-20180609171605-d41341d7d4ee/volume3/section5/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  );