Created
October 5, 2020 20:36
-
-
Save kbatuigas/4ed526fec01befe5be4ad88d5a928181 to your computer and use it in GitHub Desktop.
DB schema SQL script exported from pgModeler
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- Database generated with pgModeler (PostgreSQL Database Modeler). | |
| -- pgModeler version: 0.9.2 | |
| -- PostgreSQL version: 11.0 | |
| -- Project Site: pgmodeler.io | |
| -- Model Author: --- | |
| -- object: devuser | type: ROLE -- | |
| -- DROP ROLE IF EXISTS devuser; | |
| CREATE ROLE devuser WITH | |
| INHERIT | |
| LOGIN | |
| ENCRYPTED PASSWORD '********'; | |
| -- ddl-end -- | |
| -- Database creation must be done outside a multicommand file. | |
| -- These commands were put in this file only as a convenience. | |
| -- -- object: ddapp | type: DATABASE -- | |
| -- -- DROP DATABASE IF EXISTS ddapp; | |
| -- CREATE DATABASE ddapp | |
| -- ENCODING = 'UTF8' | |
| -- LC_COLLATE = 'C' | |
| -- LC_CTYPE = 'C' | |
| -- TABLESPACE = pg_default | |
| -- OWNER = postgres; | |
| -- -- ddl-end -- | |
| -- | |
| -- object: public.person | type: TABLE -- | |
| -- DROP TABLE IF EXISTS public.person CASCADE; | |
| CREATE TABLE public.person ( | |
| user_id integer NOT NULL, | |
| discord_id text, | |
| zoom_id text, | |
| birthdate date, | |
| CONSTRAINT person_pk PRIMARY KEY (user_id) | |
| ); | |
| -- ddl-end -- | |
| ALTER TABLE public.person OWNER TO devuser; | |
| -- ddl-end -- | |
| -- -- object: public.player_player_id_seq | type: SEQUENCE -- | |
| -- -- DROP SEQUENCE IF EXISTS public.player_player_id_seq CASCADE; | |
| -- CREATE SEQUENCE public.player_player_id_seq | |
| -- INCREMENT BY 1 | |
| -- MINVALUE 1 | |
| -- MAXVALUE 2147483647 | |
| -- START WITH 1 | |
| -- CACHE 1 | |
| -- NO CYCLE | |
| -- OWNED BY NONE; | |
| -- -- ddl-end -- | |
| -- -- ALTER SEQUENCE public.player_player_id_seq OWNER TO postgres; | |
| -- -- ddl-end -- | |
| -- | |
| -- object: public.pc | type: TABLE -- | |
| -- DROP TABLE IF EXISTS public.pc CASCADE; | |
| CREATE TABLE public.pc ( | |
| pc_id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1 ), | |
| user_id_person integer, | |
| name text, | |
| class_level smallint, | |
| id_pc_class smallint NOT NULL, | |
| id_alignment smallint NOT NULL, | |
| id_race smallint NOT NULL, | |
| strength smallint, | |
| dexterity smallint, | |
| constitution smallint, | |
| intelligence smallint, | |
| wisdom smallint, | |
| charisma smallint, | |
| armor_class smallint, | |
| initiative smallint, | |
| hp smallint, | |
| xp smallint, | |
| equipment text, | |
| spells text, | |
| treasure text, | |
| CONSTRAINT pc_pk PRIMARY KEY (pc_id) | |
| ); | |
| -- ddl-end -- | |
| COMMENT ON TABLE public.pc IS E'Cannot use "character" (reserved word)'; | |
| -- ddl-end -- | |
| ALTER TABLE public.pc OWNER TO devuser; | |
| -- ddl-end -- | |
| -- -- object: public.pc_pc_id_seq | type: SEQUENCE -- | |
| -- -- DROP SEQUENCE IF EXISTS public.pc_pc_id_seq CASCADE; | |
| -- CREATE SEQUENCE public.pc_pc_id_seq | |
| -- INCREMENT BY 1 | |
| -- MINVALUE 1 | |
| -- MAXVALUE 2147483647 | |
| -- START WITH 1 | |
| -- CACHE 1 | |
| -- NO CYCLE | |
| -- OWNED BY NONE; | |
| -- -- ddl-end -- | |
| -- -- ALTER SEQUENCE public.pc_pc_id_seq OWNER TO postgres; | |
| -- -- ddl-end -- | |
| -- | |
| -- object: public.campaign | type: TABLE -- | |
| -- DROP TABLE IF EXISTS public.campaign CASCADE; | |
| CREATE TABLE public.campaign ( | |
| campaign_id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1 ), | |
| pc_id_pc integer, | |
| name text, | |
| dates daterange, | |
| rating smallint, | |
| url text, | |
| notes text, | |
| CONSTRAINT campaign_url_valid CHECK (("position"(url, 'http'::text) = 1)), | |
| CONSTRAINT campaign_pk PRIMARY KEY (campaign_id) | |
| ); | |
| -- ddl-end -- | |
| ALTER TABLE public.campaign OWNER TO devuser; | |
| -- ddl-end -- | |
| -- -- object: public.campaign_campaign_id_seq | type: SEQUENCE -- | |
| -- -- DROP SEQUENCE IF EXISTS public.campaign_campaign_id_seq CASCADE; | |
| -- CREATE SEQUENCE public.campaign_campaign_id_seq | |
| -- INCREMENT BY 1 | |
| -- MINVALUE 1 | |
| -- MAXVALUE 2147483647 | |
| -- START WITH 1 | |
| -- CACHE 1 | |
| -- NO CYCLE | |
| -- OWNED BY NONE; | |
| -- -- ddl-end -- | |
| -- -- ALTER SEQUENCE public.campaign_campaign_id_seq OWNER TO postgres; | |
| -- -- ddl-end -- | |
| -- | |
| -- -- object: public.dm_dm_id_seq | type: SEQUENCE -- | |
| -- -- DROP SEQUENCE IF EXISTS public.dm_dm_id_seq CASCADE; | |
| -- CREATE SEQUENCE public.dm_dm_id_seq | |
| -- INCREMENT BY 1 | |
| -- MINVALUE 1 | |
| -- MAXVALUE 2147483647 | |
| -- START WITH 1 | |
| -- CACHE 1 | |
| -- NO CYCLE | |
| -- OWNED BY NONE; | |
| -- -- ddl-end -- | |
| -- -- ALTER SEQUENCE public.dm_dm_id_seq OWNER TO postgres; | |
| -- -- ddl-end -- | |
| -- | |
| -- object: public.pc_class | type: TABLE -- | |
| -- DROP TABLE IF EXISTS public.pc_class CASCADE; | |
| CREATE TABLE public.pc_class ( | |
| id smallint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT BY 1 MINVALUE 1 MAXVALUE 32767 START WITH 1 CACHE 1 ), | |
| name text, | |
| CONSTRAINT pc_class_pk PRIMARY KEY (id) | |
| ); | |
| -- ddl-end -- | |
| ALTER TABLE public.pc_class OWNER TO devuser; | |
| -- ddl-end -- | |
| -- -- object: public.pc_class_id_seq | type: SEQUENCE -- | |
| -- -- DROP SEQUENCE IF EXISTS public.pc_class_id_seq CASCADE; | |
| -- CREATE SEQUENCE public.pc_class_id_seq | |
| -- INCREMENT BY 1 | |
| -- MINVALUE 1 | |
| -- MAXVALUE 32767 | |
| -- START WITH 1 | |
| -- CACHE 1 | |
| -- NO CYCLE | |
| -- OWNED BY NONE; | |
| -- -- ddl-end -- | |
| -- -- ALTER SEQUENCE public.pc_class_id_seq OWNER TO postgres; | |
| -- -- ddl-end -- | |
| -- | |
| -- object: public.alignment | type: TABLE -- | |
| -- DROP TABLE IF EXISTS public.alignment CASCADE; | |
| CREATE TABLE public.alignment ( | |
| id smallint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT BY 1 MINVALUE 1 MAXVALUE 32767 START WITH 1 CACHE 1 ), | |
| name text, | |
| CONSTRAINT alignment_pk PRIMARY KEY (id) | |
| ); | |
| -- ddl-end -- | |
| ALTER TABLE public.alignment OWNER TO devuser; | |
| -- ddl-end -- | |
| -- -- object: public.alignment_id_seq | type: SEQUENCE -- | |
| -- -- DROP SEQUENCE IF EXISTS public.alignment_id_seq CASCADE; | |
| -- CREATE SEQUENCE public.alignment_id_seq | |
| -- INCREMENT BY 1 | |
| -- MINVALUE 1 | |
| -- MAXVALUE 32767 | |
| -- START WITH 1 | |
| -- CACHE 1 | |
| -- NO CYCLE | |
| -- OWNED BY NONE; | |
| -- -- ddl-end -- | |
| -- -- ALTER SEQUENCE public.alignment_id_seq OWNER TO postgres; | |
| -- -- ddl-end -- | |
| -- | |
| -- object: public.race | type: TABLE -- | |
| -- DROP TABLE IF EXISTS public.race CASCADE; | |
| CREATE TABLE public.race ( | |
| id smallint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT BY 1 MINVALUE 1 MAXVALUE 32767 START WITH 1 CACHE 1 ), | |
| name text, | |
| CONSTRAINT race_pk PRIMARY KEY (id) | |
| ); | |
| -- ddl-end -- | |
| ALTER TABLE public.race OWNER TO devuser; | |
| -- ddl-end -- | |
| -- -- object: public.race_id_seq | type: SEQUENCE -- | |
| -- -- DROP SEQUENCE IF EXISTS public.race_id_seq CASCADE; | |
| -- CREATE SEQUENCE public.race_id_seq | |
| -- INCREMENT BY 1 | |
| -- MINVALUE 1 | |
| -- MAXVALUE 32767 | |
| -- START WITH 1 | |
| -- CACHE 1 | |
| -- NO CYCLE | |
| -- OWNED BY NONE; | |
| -- -- ddl-end -- | |
| -- -- ALTER SEQUENCE public.race_id_seq OWNER TO postgres; | |
| -- -- ddl-end -- | |
| -- | |
| -- object: public.person_campaign | type: TABLE -- | |
| -- DROP TABLE IF EXISTS public.person_campaign CASCADE; | |
| CREATE TABLE public.person_campaign ( | |
| is_dm bool, | |
| campaign_id_campaign integer NOT NULL, | |
| notes text, | |
| user_id_person integer NOT NULL, | |
| CONSTRAINT person_campaign_pk PRIMARY KEY (campaign_id_campaign,user_id_person) | |
| ); | |
| -- ddl-end -- | |
| ALTER TABLE public.person_campaign OWNER TO devuser; | |
| -- ddl-end -- | |
| -- object: person_fk | type: CONSTRAINT -- | |
| -- ALTER TABLE public.person_campaign DROP CONSTRAINT IF EXISTS person_fk CASCADE; | |
| ALTER TABLE public.person_campaign ADD CONSTRAINT person_fk FOREIGN KEY (user_id_person) | |
| REFERENCES public.person (user_id) MATCH FULL | |
| ON DELETE RESTRICT ON UPDATE CASCADE; | |
| -- ddl-end -- | |
| -- object: campaign_fk | type: CONSTRAINT -- | |
| -- ALTER TABLE public.person_campaign DROP CONSTRAINT IF EXISTS campaign_fk CASCADE; | |
| ALTER TABLE public.person_campaign ADD CONSTRAINT campaign_fk FOREIGN KEY (campaign_id_campaign) | |
| REFERENCES public.campaign (campaign_id) MATCH FULL | |
| ON DELETE RESTRICT ON UPDATE CASCADE; | |
| -- ddl-end -- | |
| -- object: person_fk | type: CONSTRAINT -- | |
| -- ALTER TABLE public.pc DROP CONSTRAINT IF EXISTS person_fk CASCADE; | |
| ALTER TABLE public.pc ADD CONSTRAINT person_fk FOREIGN KEY (user_id_person) | |
| REFERENCES public.person (user_id) MATCH FULL | |
| ON DELETE SET NULL ON UPDATE CASCADE; | |
| -- ddl-end -- | |
| -- object: pc_class_fk | type: CONSTRAINT -- | |
| -- ALTER TABLE public.pc DROP CONSTRAINT IF EXISTS pc_class_fk CASCADE; | |
| ALTER TABLE public.pc ADD CONSTRAINT pc_class_fk FOREIGN KEY (id_pc_class) | |
| REFERENCES public.pc_class (id) MATCH FULL | |
| ON DELETE RESTRICT ON UPDATE CASCADE; | |
| -- ddl-end -- | |
| -- object: alignment_fk | type: CONSTRAINT -- | |
| -- ALTER TABLE public.pc DROP CONSTRAINT IF EXISTS alignment_fk CASCADE; | |
| ALTER TABLE public.pc ADD CONSTRAINT alignment_fk FOREIGN KEY (id_alignment) | |
| REFERENCES public.alignment (id) MATCH FULL | |
| ON DELETE RESTRICT ON UPDATE CASCADE; | |
| -- ddl-end -- | |
| -- object: race_fk | type: CONSTRAINT -- | |
| -- ALTER TABLE public.pc DROP CONSTRAINT IF EXISTS race_fk CASCADE; | |
| ALTER TABLE public.pc ADD CONSTRAINT race_fk FOREIGN KEY (id_race) | |
| REFERENCES public.race (id) MATCH FULL | |
| ON DELETE RESTRICT ON UPDATE CASCADE; | |
| -- ddl-end -- | |
| -- object: pc_fk | type: CONSTRAINT -- | |
| -- ALTER TABLE public.campaign DROP CONSTRAINT IF EXISTS pc_fk CASCADE; | |
| ALTER TABLE public.campaign ADD CONSTRAINT pc_fk FOREIGN KEY (pc_id_pc) | |
| REFERENCES public.pc (pc_id) MATCH FULL | |
| ON DELETE SET NULL ON UPDATE CASCADE; | |
| -- ddl-end -- | |
| -- object: grant_93cdfb8c7f | type: PERMISSION -- | |
| GRANT CONNECT,TEMPORARY | |
| ON DATABASE ddapp | |
| TO PUBLIC; | |
| -- ddl-end -- | |
| -- object: grant_cb72eeed37 | type: PERMISSION -- | |
| GRANT CREATE,CONNECT,TEMPORARY | |
| ON DATABASE ddapp | |
| TO postgres; | |
| -- ddl-end -- | |
| -- object: grant_b9637297c1 | type: PERMISSION -- | |
| GRANT CREATE,CONNECT,TEMPORARY | |
| ON DATABASE ddapp | |
| TO devuser; | |
| -- ddl-end -- |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment