WordPress Template

Code to create the needed Postgres objects to sync with WordPress.

CREATE TYPE comment_status AS ENUM ('open', 'closed');

CREATE TYPE format AS ENUM ('standard', 'aside', 'chat', 'gallery', 'link', 'image', 'quote', 'status', 'video', 'audio');

CREATE TYPE media_type AS ENUM ('image', 'file');

CREATE TYPE ping_status AS ENUM ('open', 'closed');

CREATE TYPE status AS ENUM ('publish', 'future', 'draft', 'pending', 'private', 'acf-disabled', 'inherit');

CREATE TYPE taxonomy AS ENUM ('category', 'post_tag');

CREATE TABLE public."Categories"(
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
"Count" integer,
"Description" text,
"Link" text,
"Name" text,
"Slug" text,
"Taxonomy" taxonomy,
"WordPress.org Record ID" text
);

CREATE TABLE public."Media"(
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
"Alt text" text,
"Author" uuid,
"Caption" text,
"Comment status" comment_status,
"Date" timestamp without time zone,
"Description" text,
"Link" text,
"Media details" text,
"Media type" media_type,
"Mime type" text,
"Modified" timestamp without time zone,
"Ping status" ping_status,
"Slug" text,
"Source url" text,
"Status" status,
"Title" text,
"WordPress.org Record ID" text
);

CREATE TABLE public."Pages"(
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
"Author" uuid,
"Comment status" comment_status,
"Content" text,
"Date" timestamp without time zone,
"Excerpt" text,
"Featured media" uuid,
"Link" text,
"Menu order" integer,
"Modified" timestamp without time zone,
"Ping status" ping_status,
"Slug" text,
"Status" status,
"Title" text,
"WordPress.org Record ID" text
);

CREATE TABLE public."Posts"(
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
"Author" uuid,
"Categories" uuid,
"Comment status" comment_status,
"Content" text,
"Date" timestamp without time zone,
"Excerpt" text,
"Featured media" uuid,
"Format" format,
"Link" text,
"Modified" timestamp without time zone,
"Ping status" ping_status,
"Slug" text,
"Status" status,
"Sticky" boolean,
"Tags" uuid,
"Title" text,
"WordPress.org Record ID" text
);

CREATE TABLE public."Tags"(
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
"Count" integer,
"Description" text,
"Link" text,
"Name" text,
"Slug" text,
"Taxonomy" taxonomy,
"WordPress.org Record ID" text
);

CREATE TABLE public."Users"(
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
"Avatar urls" text,
"Description" text,
"Link" text,
"Name" text,
"Slug" text,
"Url" text,
"WordPress.org Record ID" text
);

ALTER TABLE public."Media"
ADD CONSTRAINT media_users_fkey
FOREIGN KEY ("Author")
REFERENCES public."Users"("id");

ALTER TABLE public."Pages"
ADD CONSTRAINT pages_media_fkey
FOREIGN KEY ("Featured media")
REFERENCES public."Media"("id");

ALTER TABLE public."Pages"
ADD CONSTRAINT pages_users_fkey
FOREIGN KEY ("Author")
REFERENCES public."Users"("id");

ALTER TABLE public."Posts"
ADD CONSTRAINT posts_categories_fkey
FOREIGN KEY ("Categories")
REFERENCES public."Categories"("id");

ALTER TABLE public."Posts"
ADD CONSTRAINT posts_media_fkey
FOREIGN KEY ("Featured media")
REFERENCES public."Media"("id");

ALTER TABLE public."Posts"
ADD CONSTRAINT posts_tags_fkey
FOREIGN KEY ("Tags")
REFERENCES public."Tags"("id");

ALTER TABLE public."Posts"
ADD CONSTRAINT posts_users_fkey
FOREIGN KEY ("Author")
REFERENCES public."Users"("id");

Useful for Experimentation and Testing

-- Clean up data:
TRUNCATE public."Pages" CASCADE;
TRUNCATE public."Posts" CASCADE;
TRUNCATE public."Media" CASCADE;
TRUNCATE public."Categories" CASCADE;
TRUNCATE public."Tags" CASCADE;
TRUNCATE public."Users" CASCADE;

-- Delete tables and data types:
DROP TABLE public."Pages";
DROP TABLE public."Posts";
DROP TABLE public."Media";
DROP TABLE public."Categories";
DROP TABLE public."Tags";
DROP TABLE public."Users";
DROP TYPE comment_status;
DROP TYPE format;
DROP TYPE media_type;
DROP TYPE ping_status;
DROP TYPE status;
DROP TYPE taxonomy;