tag

[dlrandy]

typescript configuration

[dlrandy]

CREATE SEQUENCE blog_user_seq;
CREATE TABLE "user" (
  "id" BIGINT NOT NULL PRIMARY KEY DEFAULT NEXTVAL ('blog_user_seq'),
  "firstName" VARCHAR(50) NULL DEFAULT NULL,
  "lastName" VARCHAR(50) NULL DEFAULT NULL,
  "mobile" VARCHAR(15) NULL,
  "email" VARCHAR(50) NULL,
  "passwordHash" VARCHAR(32) NOT NULL,
  "registeredAt" TIME NOT NULL,
  "lastLogin" TIME NULL DEFAULT NULL,
  "intro" TEXT NULL DEFAULT NULL,
  "profile" TEXT NULL DEFAULT NULL,
  "avatar" TEXT NULL DEFAULT NULL
  );

CREATE SEQUENCE blog_post_seq;

CREATE TABLE "post" (
  "id" BIGINT NOT NULL PRIMARY KEY DEFAULT NEXTVAL ('blog_post_seq'),
  "authorId" BIGINT NOT NULL,
  "parentId" BIGINT NULL DEFAULT NULL,
  "title" VARCHAR(75) NOT NULL,
  "metaTitle" VARCHAR(100) NULL,
  "slug" VARCHAR(100) NOT NULL,
  "summary" TEXT NULL,
  "published" INT NOT NULL DEFAULT 0,
  "createdAt" TIME NOT NULL,
  "updatedAt" TIME NULL DEFAULT NULL,
  "publishedAt" TIME NULL DEFAULT NULL,
  "content" TEXT NULL DEFAULT NULL,
  CONSTRAINT "fk_post_user"
    FOREIGN KEY ("authorId")
    REFERENCES "user" ("id")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

ALTER TABLE "post" 
ADD CONSTRAINT "fk_post_parent"
  FOREIGN KEY ("parentId")
  REFERENCES "post" ("id")
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

CREATE SEQUENCE blog_post_meta_seq;
CREATE TABLE "post_meta" (
 "id" BIGINT NOT NULL PRIMARY KEY DEFAULT NEXTVAL ('blog_post_meta_seq'),
  "postId" BIGINT NOT NULL,
  "key" VARCHAR(50) NOT NULL,
  "content" TEXT NULL DEFAULT NULL,
  CONSTRAINT "fk_meta_post"
    FOREIGN KEY ("postId")
    REFERENCES "post" ("id")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
CREATE SEQUENCE blog_post_comment_seq;
CREATE TABLE "post_comment" (
"id" BIGINT NOT NULL PRIMARY KEY DEFAULT NEXTVAL ('blog_post_comment_seq'),
  "postId" BIGINT NOT NULL,
  "parentId" BIGINT NULL DEFAULT NULL,
  "title" VARCHAR(100) NOT NULL,
  "published" INT NOT NULL DEFAULT 0,
  "createdAt" TIME NOT NULL,
  "publishedAt" TIME NULL DEFAULT NULL,
  "content" TEXT NULL DEFAULT NULL,
  CONSTRAINT "fk_comment_post"
    FOREIGN KEY ("postId")
    REFERENCES "post" ("id")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

ALTER TABLE "post_comment" 
ADD CONSTRAINT "fk_comment_parent"
  FOREIGN KEY ("parentId")
  REFERENCES "post_comment" ("id")
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

CREATE SEQUENCE blog_category_seq;
CREATE TABLE "category" (
"id" BIGINT NOT NULL PRIMARY KEY DEFAULT NEXTVAL ('blog_category_seq'),
  "parentId" BIGINT NULL DEFAULT NULL,
  "title" VARCHAR(75) NOT NULL,
  "metaTitle" VARCHAR(100) NULL DEFAULT NULL,
  "slug" VARCHAR(100) NOT NULL,
  "content" TEXT NULL DEFAULT NULL
  );

ALTER TABLE "category" 
ADD CONSTRAINT "fk_category_parent"
  FOREIGN KEY ("parentId")
  REFERENCES "category" ("id")
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

CREATE TABLE "post_category" (
  "postId" BIGINT NOT NULL,
  "categoryId" BIGINT NOT NULL,
  CONSTRAINT "fk_pc_post"
    FOREIGN KEY ("postId")
    REFERENCES "post" ("id")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT "fk_pc_category"
    FOREIGN KEY ("categoryId")
    REFERENCES "category" ("id")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

-- https://mysql.tutorials24x7.com/blog/guide-to-design-a-database-for-blog-management-in-mysql