sms-proj/postgres-db-schema.sql

# Database layout
Here is some code.
-- Database: smsproject

-- DROP DATABASE IF EXISTS smsproject;

CREATE DATABASE smsproject
    WITH
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'C'
    LC_CTYPE = 'C'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

CREATE TABLE account (
	id		serial NOT NULL PRIMARY KEY,
	name	VARCHAR(50) NOT NULL DEFAULT '0',
	email	VARCHAR(75) NOT NULL DEFAULT '0'
);

-- CREATE UNIQUE INDEX FK_destination_account ON account (account_id);


CREATE TYPE v_status AS ENUM ('Y','N','P');
CREATE TYPE call_direction AS ENUM('inbound','outbound');
CREATE TYPE phone_num_type AS ENUM('mobile','home', 'office', 'other');

CREATE TABLE destination (
	id				serial NOT NULL PRIMARY KEY,
	dest_did 		VARCHAR(11) NOT NULL DEFAULT '0',
	verify_status 	v_status NOT NULL DEFAULT 'N',
	idpin 			INT NULL DEFAULT '0',
	account_id 		INT NOT NULL DEFAULT '0',
	CONSTRAINT FK_destination_account FOREIGN KEY (account_id) REFERENCES account (id)
);

CREATE TABLE dids (
	id			serial PRIMARY KEY,
	number 		TEXT NOT NULL,
	provider 	VARCHAR(18) NULL DEFAULT '0',
	account_id 	INT NULL DEFAULT NULL,
	CONSTRAINT FK_dids_account FOREIGN KEY (account_id) REFERENCES account (id)
);

INSERT INTO dids (number,provider,account_id) VALUES ('17605551212','Flowroute',1);


CREATE TABLE messages (
	id					serial NOT NULL PRIMARY KEY,
	account_id 		INT NULL DEFAULT NULL,
	timestamp 		TIMESTAMP NOT NULL,
	pid 			VARCHAR(64) NULL DEFAULT NULL,
	provider_timestamp VARCHAR(36) NULL DEFAULT NULL,
	direction 		call_direction DEFAULT 'inbound',
	source_number	VARCHAR(11) NOT NULL,
	dest_number 	VARCHAR(11) NOT NULL,
	cost 			VARCHAR(10) NOT NULL DEFAULT '0.00',
	body 			TEXT NOT NULL,
	status 			VARCHAR(30) NOT NULL DEFAULT 'pending',
	CONSTRAINT FK_messages_account FOREIGN KEY (account_id) REFERENCES account (id)
);

##########Update V2
# Adding token and other infos.
ALTER TABLE account ADD COLUMN refresh_token bytea NULL;
ALTER TABLE account ADD COLUMN google_id VARCHAR(255) NULL UNIQUE;
ALTER TABLE account ADD COLUMN verified_email BOOL NOT NULL DEFAULT False;

##########Update V3
# Adding last modified and created, as well as changing the timestamp
# This requires loss of all logs. Oops.
ALTER TABLE account ADD COLUMN created TIMESTAMP DEFAULT CURRENT_TIMESTAMP;



CREATE OR REPLACE FUNCTION update_last_modified_column() 
RETURNS TRIGGER AS $$
BEGIN
    NEW.last_modified = now();
    RETURN NEW; 
END;
$$ language 'plpgsql';


ALTER TABLE account ADD COLUMN last_modified TIMESTAMP;
CREATE TRIGGER update_last_modtime BEFORE UPDATE ON account FOR EACH ROW EXECUTE PROCEDURE  update_last_modified_column();



##########Update V4
# Add an entirely new table contactlist
CREATE TABLE contacts (
	id	 			serial NOT NULL PRIMARY KEY,
	account_id 		INT NULL DEFAULT NULL,
	last_modified 	TIMESTAMP,
	created 		TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	archived 		BOOL NOT NULL DEFAULT '0',
	fullname 		VARCHAR(122) NOT NULL,
	email			VARCHAR(200) NULL
);

CREATE TRIGGER update_last_modtime BEFORE UPDATE ON contacts FOR EACH ROW EXECUTE PROCEDURE  update_last_modified_column();

CREATE TABLE phonebase (
	id 					serial NOT NULL PRIMARY KEY,
	contact_id 			INT NULL DEFAULT NULL,
	phone_number		VARCHAR(15) NOT NULL,
	number_type 		phone_num_type NULL DEFAULT 'mobile',
	archived			BOOL NOT NULL DEFAULT '0',
	CONSTRAINT pb_accountassoc FOREIGN KEY (contact_id) REFERENCES account (id)
);

########### Update V5
# Adding password and username support.
ALTER TABLE account ADD COLUMN username VARCHAR(255) NULL UNIQUE;
ALTER TABLE account ADD COLUMN passwd VARCHAR(255) NULL;

########## UPDATE V6
ALTER TABLE account DROP COLUMN google_id;
ALTER TABLE account DROP COLUMN username;
ALTER TABLE account DROP COLUMN refresh_token;
ALTER TABLE account ADD COLUMN loginid VARCHAR(255) NULL UNIQUE;
ALTER TABLE account ADD COLUMN picture_url VARCHAR(255) NULL;
ALTER TABLE messages ADD COLUMN is_read BOOL NOT NULL DEFAULT '0';

ALTER TABLE account ADD COLUMN subscription_token VARCHAR(50) NULL DEFAULT NULL;