db-cheatsheet

INSERT INTO users (username,password,passwordsalt,email) VALUES('usertest','testpass','testpasssalt','test@test.com');
INSERT INTO categories (name) VALUES('testCategory');

SELECT returns results.rows all rows as objects
INSERT returns array [0 = inserted data, 1 = affected rows]
Delete return object
UPDATE returns array [0 = inserted data, 1 = affected rows]

// Function to update modifiedDate with current timestamptz
CREATE OR REPLACE FUNCTION update_modifiedDate_column()
RETURNS TRIGGER AS $$
BEGIN
   NEW."modifiedDate" = now(); 
   RETURN NEW;
END;
$$ language 'plpgsql';

//trigger to be run for every table to run function update_modifiedDate_column
CREATE TRIGGER update_changetimestamp BEFORE UPDATE
ON comments FOR EACH ROW EXECUTE PROCEDURE 
update_modifiedDate_column();

(articles, comments)

CREATE OR REPLACE FUNCTION update_views_column()
RETURNS TRIGGER AS $$
BEGIN
   NEW."views" = views + 1; 
   RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_views BEFORE GET
ON views FOR EACH ROW EXECUTE PROCEDURE 
update_views_column();

   

-- *************** SqlDBM: PostgreSQL ****************;
-- ***************************************************;


-- ************************************** "users"

CREATE TABLE "users"
(
 "id"             SERIAL NOT NULL,
 "firstName"      varchar(255),
 "lastName"       varchar(255),
 "username"       varchar(255) NOT NULL,
 "about"          varchar(255),
 "dateRegistered" TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
 "password"       varchar(255) NOT NULL,
 "passwordsalt"   varchar(255),
 "email"          varchar(255) NOT NULL,
 "avatarURL"      varchar(255),
 CONSTRAINT "PK_users" PRIMARY KEY ( "id" )
);








-- ************************************** "categories"

CREATE TABLE "categories"
(
 "id"          SERIAL NOT NULL,
 "name"        varchar(255) NOT NULL,
 "description" varchar(255),
 "imageURL"    varchar(255),
 CONSTRAINT "PK_categories" PRIMARY KEY ( "id" )
);








-- ************************************** "articles"
CREATE TABLE "views"
(
"id"  bigint,
"views"  bigint DEFAULT 0,
CONSTRAINT "PK_views" PRIMARY KEY ( "id" ),
CONSTRAINT "FK_articles" FOREIGN KEY ( "id" ) REFERENCES "articles" ( "id" ) 
);
CREATE INDEX "fkIdx_articles" ON "views"
(
   "id"
);

CREATE TABLE "articles"
(
 "id"         SERIAL NOT NULL,
 "title"      varchar(255) NOT NULL,
 "bodyText"   varchar(255) NOT NULL,
 "summary"    varchar(255),
 "reg_date"    TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
 "modifiedDate"   TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
 "imageURL"   varchar(255),
 "published"  boolean NOT NULL,
 "authorId"   int NOT NULL,
 "categoryId" int NOT NULL,
 CONSTRAINT "PK_articles" PRIMARY KEY ( "id" ),
 CONSTRAINT "FK_89" FOREIGN KEY ( "authorId" ) REFERENCES "users" ( "id" ),
 CONSTRAINT "FK_92" FOREIGN KEY ( "categoryId" ) REFERENCES "categories" ( "id" )
);

CREATE INDEX "fkIdx_89" ON "articles"
(
 "authorId"
);

CREATE INDEX "fkIdx_92" ON "articles"
(
 "categoryId"
);








-- ************************************** "pins"

CREATE TABLE "pins"
(
 "id"        SERIAL NOT NULL,
 "articleId" int NOT NULL,
 "userId"    int NOT NULL,
 CONSTRAINT "PK_pins" PRIMARY KEY ( "id" ),
 CONSTRAINT "FK_107" FOREIGN KEY ( "articleId" ) REFERENCES "articles" ( "id" ),
 CONSTRAINT "FK_110" FOREIGN KEY ( "userId" ) REFERENCES "users" ( "id" )
);

CREATE INDEX "fkIdx_107" ON "pins"
(
 "articleId"
);

CREATE INDEX "fkIdx_110" ON "pins"
(
 "userId"
);








-- ************************************** "likes"

CREATE TABLE "likes"
(
 "id"        SERIAL NOT NULL,
 "articleId" int NOT NULL,
 "userId"    int NOT NULL,
 CONSTRAINT "PK_likes" PRIMARY KEY ( "id" ),
 CONSTRAINT "FK_101" FOREIGN KEY ( "articleId" ) REFERENCES "articles" ( "id" ),
 CONSTRAINT "FK_104" FOREIGN KEY ( "userId" ) REFERENCES "users" ( "id" )
);

CREATE INDEX "fkIdx_101" ON "likes"
(
 "articleId"
);

CREATE INDEX "fkIdx_104" ON "likes"
(
 "userId"
);








-- ************************************** "comments"

CREATE TABLE "comments"
(
 "id"        SERIAL NOT NULL,
 "bodyText"  varchar(255) NOT NULL,
 "reg_date"   TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
 "modifiedDate"   TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
 "authorId"  int NOT NULL,
 "articleId" int NOT NULL,
 CONSTRAINT "PK_comments" PRIMARY KEY ( "id" ),
 CONSTRAINT "FK_95" FOREIGN KEY ( "authorId" ) REFERENCES "users" ( "id" ),
 CONSTRAINT "FK_98" FOREIGN KEY ( "articleId" ) REFERENCES "articles" ( "id" )
);

CREATE INDEX "fkIdx_95" ON "comments"
(
 "authorId"
);

CREATE INDEX "fkIdx_98" ON "comments"
(
 "articleId"
);