Schema¶

CREATE TABLE tag (
	id SERIAL NOT NULL, 
	name VARCHAR(30), 
	CONSTRAINT pk_tag PRIMARY KEY (id)
)


CREATE UNIQUE INDEX ix_tag_name ON tag (name)

CREATE TABLE "user" (
	id SERIAL NOT NULL, 
	name VARCHAR(100) NOT NULL, 
	CONSTRAINT pk_user PRIMARY KEY (id)
)

CREATE TABLE email (
	id SERIAL NOT NULL, 
	address VARCHAR(254) NOT NULL, 
	user_id INTEGER NOT NULL, 
	verified BOOLEAN NOT NULL, 
	CONSTRAINT pk_email PRIMARY KEY (id), 
	CONSTRAINT fk_email_user_id_user FOREIGN KEY(user_id) REFERENCES "user" (id)
)


CREATE INDEX ix_email_user_id ON email (user_id)

CREATE TABLE entry (
	id SERIAL NOT NULL, 
	ctime TIMESTAMP WITHOUT TIME ZONE NOT NULL, 
	mtime TIMESTAMP WITHOUT TIME ZONE, 
	author_id INTEGER NOT NULL, 
	type VARCHAR NOT NULL, 
	CONSTRAINT pk_entry PRIMARY KEY (id), 
	CONSTRAINT fk_entry_author_id_user FOREIGN KEY(author_id) REFERENCES "user" (id)
)

CREATE TABLE facebookuser (
	id VARCHAR(40) NOT NULL, 
	name VARCHAR(100) NOT NULL, 
	user_id INTEGER NOT NULL, 
	CONSTRAINT pk_facebookuser PRIMARY KEY (id), 
	CONSTRAINT fk_facebookuser_user_id_user FOREIGN KEY(user_id) REFERENCES "user" (id)
)


CREATE UNIQUE INDEX ix_facebookuser_user_id ON facebookuser (user_id)

CREATE TABLE token (
	value VARCHAR(255) NOT NULL, 
	user_id INTEGER, 
	CONSTRAINT pk_token PRIMARY KEY (value), 
	CONSTRAINT fk_token_user_id_user FOREIGN KEY(user_id) REFERENCES "user" (id)
)


CREATE INDEX ix_token_user_id ON token (user_id)

CREATE TABLE article (
	id INTEGER NOT NULL, 
	title VARCHAR(80) NOT NULL, 
	content TEXT NOT NULL, 
	views INTEGER NOT NULL, 
	popularity FLOAT NOT NULL, 
	CONSTRAINT pk_article PRIMARY KEY (id), 
	CONSTRAINT fk_article_id_entry FOREIGN KEY(id) REFERENCES entry (id)
)

CREATE TABLE comment (
	id INTEGER NOT NULL, 
	parent_id INTEGER NOT NULL, 
	content TEXT NOT NULL, 
	CONSTRAINT pk_comment PRIMARY KEY (id), 
	CONSTRAINT fk_comment_id_entry FOREIGN KEY(id) REFERENCES entry (id), 
	CONSTRAINT fk_comment_parent_id_entry FOREIGN KEY(parent_id) REFERENCES entry (id)
)

CREATE TABLE vote (
	user_id INTEGER NOT NULL, 
	entry_id INTEGER NOT NULL, 
	value SMALLINT CONSTRAINT vote_is_one CHECK (value = 1 OR value = -1), 
	CONSTRAINT pk_vote PRIMARY KEY (user_id, entry_id), 
	CONSTRAINT fk_vote_user_id_user FOREIGN KEY(user_id) REFERENCES "user" (id), 
	CONSTRAINT fk_vote_entry_id_entry FOREIGN KEY(entry_id) REFERENCES entry (id)
)


CREATE INDEX ix_vote_entry_id ON vote (entry_id)

CREATE TABLE articletag (
	article_id INTEGER NOT NULL, 
	tag_id INTEGER NOT NULL, 
	CONSTRAINT pk_articletag PRIMARY KEY (article_id, tag_id), 
	CONSTRAINT fk_articletag_article_id_article FOREIGN KEY(article_id) REFERENCES article (id), 
	CONSTRAINT fk_articletag_tag_id_tag FOREIGN KEY(tag_id) REFERENCES tag (id)
)

CREATE TABLE issue (
	id INTEGER NOT NULL, 
	is_anonymous BOOLEAN NOT NULL, 
	CONSTRAINT pk_issue PRIMARY KEY (id), 
	CONSTRAINT fk_issue_id_article FOREIGN KEY(id) REFERENCES article (id)
)

CREATE TABLE solution (
	id INTEGER NOT NULL, 
	issue_id INTEGER, 
	CONSTRAINT pk_solution PRIMARY KEY (id), 
	CONSTRAINT fk_solution_id_article FOREIGN KEY(id) REFERENCES article (id), 
	CONSTRAINT fk_solution_issue_id_issue FOREIGN KEY(issue_id) REFERENCES issue (id)
)