CREATE TABLE IF NOT EXISTS POSTS(
ID SERIAL PRIMARY KEY,
AUTHOR_ID INTEGER NOT NULL,
TITLE VARCHAR(100) NOT NULL,
SUMMARY VARCHAR(500),
PUBLISHED BOOLEAN DEFAULT FALSE,
CREATED_ON timestamp DEFAULT CURRENT_TIMESTAMP,
UPDATED_ON timestamp DEFAULT CURRENT_TIMESTAMP,
LIKE_COUNT INTEGER DEFAULT 0,
VIEWS INTEGER DEFAULT 0,
BODY TEXT,
FOREIGN KEY (AUTHOR_ID)
REFERENCES USERS (ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
CREATE TABLE IF NOT EXISTS COMMENTS(
ID SERIAL PRIMARY KEY,
AUTHOR_ID INTEGER NOT NULL,
CREATED_ON timestamp DEFAULT CURRENT_TIMESTAMP,
UPDATED_ON timestamp DEFAULT CURRENT_TIMESTAMP,
BODY TEXT,
FOREIGN KEY (AUTHOR_ID)
REFERENCES USERS (ID)
ON DELETE NO ACTION
);
"post_likes_post_id_fkey" FOREIGN KEY (post_id) REFERENCES post(id) ON DELETE CASCADE
Create Triggers
For Updating LIKE Counter in Post
CREATE FUNCTION updateLike() RETURNS TRIGGER
AS $BODY$
BEGIN
IF (TG_OP = 'INSERT') THEN
UPDATE POSTS
set LIKE_COUNT = LIKE_COUNT + 1
where id = new.POST_ID;
RETURN NEW;
ELSIF (TG_OP='DELETE') THEN
UPDATE POSTS
set LIKE_COUNT = LIKE_COUNT - 1
where id = old.POST_ID;
RETURN OLD;
END IF;
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql;
CREATE TRIGGER UPDATE_LIKE_COUNT
AFTER INSERT OR DELETE ON POST_LIKES
FOR EACH ROW EXECUTE PROCEDURE updateLike();
For Updating Post Count in tags
CREATE FUNCTION updatePostCount() RETURNS TRIGGER
AS $BODY$
BEGIN
IF (TG_OP = 'INSERT') THEN
UPDATE TAGS
set TOTAL_POST = TOTAL_POST + 1
where id = new.TAG_ID;
RETURN NEW;
ELSIF (TG_OP='DELETE') THEN
UPDATE TAGS
set TOTAL_POST = TOTAL_POST - 1
where id = old.TAG_ID;
RETURN OLD;
END IF;
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql;
CREATE TRIGGER UPDATE_TAG_POST_COUNT
AFTER INSERT OR DELETE ON POST_TAGS
FOR EACH ROW EXECUTE PROCEDURE updatePostCount();
type Comment struct {
ID int `json:"id,omitempty"`
AuthorID int `json:"author_id,omitempty"`
Body string `json:"body,omitempty"`
CreatedOn time.Time `json:"created_on,omitempty"`
UpdatedOn time.Time `json:"updated_on,omitempty"`
}
type Post struct {
ID int `json:"id"`
AuthorID int `json:"author_id"`
Title string `json:"title"`
Summary string `json:"summary,omitempty"`
Body string `json:"body,omitempty"`
Published bool `json:"published,omitempty"`
CreatedOn time.Time `json:"created_on,omitempty"`
UpdatedOn time.Time `json:"updated_on,omitempty"`
LikeCount int `json:"like_count,omitempty"`
Views int `json:"views,omitempty"`
}
type Tag struct {
ID int `json:"id,omitempty"`
Title string `json:"title,omitempty"`
Summary string `json:"summary,omitempty"`
TotalPost int `json:"total_post,omitempty"`
}