Explorer Database Schema

The current (as of 2019/09/26) schema extracted from PostgreSQL using pg_dump -s "cexplorer":

--
-- PostgreSQL database dump
--

-- Dumped from database version 11.5 (Debian 11.5-2)
-- Dumped by pg_dump version 11.5 (Debian 11.5-2)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: hash28type; Type: DOMAIN; Schema: public; Owner: nix
--

CREATE DOMAIN public.hash28type AS bytea
	CONSTRAINT hash28type_check CHECK ((octet_length(VALUE) = 28));


ALTER DOMAIN public.hash28type OWNER TO nix;

--
-- Name: hash32type; Type: DOMAIN; Schema: public; Owner: nix
--

CREATE DOMAIN public.hash32type AS bytea
	CONSTRAINT hash32type_check CHECK ((octet_length(VALUE) = 32));


ALTER DOMAIN public.hash32type OWNER TO nix;

--
-- Name: lovelace; Type: DOMAIN; Schema: public; Owner: nix
--

CREATE DOMAIN public.lovelace AS bigint
	CONSTRAINT lovelace_check CHECK (((VALUE >= 0) AND (VALUE <= '45000000000000000'::bigint)));


ALTER DOMAIN public.lovelace OWNER TO nix;

--
-- Name: txindex; Type: DOMAIN; Schema: public; Owner: nix
--

CREATE DOMAIN public.txindex AS smallint
	CONSTRAINT txindex_check CHECK (((VALUE >= 0) AND (VALUE < 1024)));


ALTER DOMAIN public.txindex OWNER TO nix;

--
-- Name: uinteger; Type: DOMAIN; Schema: public; Owner: nix
--

CREATE DOMAIN public.uinteger AS integer
	CONSTRAINT uinteger_check CHECK ((VALUE >= 0));


ALTER DOMAIN public.uinteger OWNER TO nix;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: block; Type: TABLE; Schema: public; Owner: nix
--

CREATE TABLE public.block (
    id bigint NOT NULL,
    hash public.hash32type NOT NULL,
    slot_no public.uinteger,
    block_no public.uinteger,
    previous bigint,
    merkel_root public.hash32type,
    slot_leader bigint NOT NULL,
    size public.uinteger NOT NULL,
    epoch_no public.uinteger
);


ALTER TABLE public.block OWNER TO nix;

--
-- Name: block_id_seq; Type: SEQUENCE; Schema: public; Owner: nix
--

CREATE SEQUENCE public.block_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.block_id_seq OWNER TO nix;

--
-- Name: block_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: nix
--

ALTER SEQUENCE public.block_id_seq OWNED BY public.block.id;


--
-- Name: meta; Type: TABLE; Schema: public; Owner: nix
--

CREATE TABLE public.meta (
    id bigint NOT NULL,
    protocol_const bigint NOT NULL,
    slot_duration bigint NOT NULL,
    start_time timestamp with time zone NOT NULL
);


ALTER TABLE public.meta OWNER TO nix;

--
-- Name: meta_id_seq; Type: SEQUENCE; Schema: public; Owner: nix
--

CREATE SEQUENCE public.meta_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.meta_id_seq OWNER TO nix;

--
-- Name: meta_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: nix
--

ALTER SEQUENCE public.meta_id_seq OWNED BY public.meta.id;


--
-- Name: schema_version; Type: TABLE; Schema: public; Owner: nix
--

CREATE TABLE public.schema_version (
    id integer NOT NULL,
    stage_one bigint NOT NULL,
    stage_two bigint NOT NULL,
    stage_three bigint NOT NULL
);


ALTER TABLE public.schema_version OWNER TO nix;

--
-- Name: schema_version_id_seq; Type: SEQUENCE; Schema: public; Owner: nix
--

CREATE SEQUENCE public.schema_version_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.schema_version_id_seq OWNER TO nix;

--
-- Name: schema_version_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: nix
--

ALTER SEQUENCE public.schema_version_id_seq OWNED BY public.schema_version.id;


--
-- Name: slot_leader; Type: TABLE; Schema: public; Owner: nix
--

CREATE TABLE public.slot_leader (
    id bigint NOT NULL,
    hash public.hash28type NOT NULL,
    desciption character varying NOT NULL
);


ALTER TABLE public.slot_leader OWNER TO nix;

--
-- Name: slot_leader_id_seq; Type: SEQUENCE; Schema: public; Owner: nix
--

CREATE SEQUENCE public.slot_leader_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.slot_leader_id_seq OWNER TO nix;

--
-- Name: slot_leader_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: nix
--

ALTER SEQUENCE public.slot_leader_id_seq OWNED BY public.slot_leader.id;


--
-- Name: tx; Type: TABLE; Schema: public; Owner: nix
--

CREATE TABLE public.tx (
    id bigint NOT NULL,
    hash public.hash32type NOT NULL,
    block bigint NOT NULL,
    fee public.lovelace NOT NULL
);


ALTER TABLE public.tx OWNER TO nix;

--
-- Name: tx_in; Type: TABLE; Schema: public; Owner: nix
--

CREATE TABLE public.tx_in (
    id bigint NOT NULL,
    tx_in_id bigint NOT NULL,
    tx_out_id bigint NOT NULL,
    tx_out_index public.txindex NOT NULL
);


ALTER TABLE public.tx_in OWNER TO nix;

--
-- Name: tx_out; Type: TABLE; Schema: public; Owner: nix
--

CREATE TABLE public.tx_out (
    id bigint NOT NULL,
    tx_id bigint NOT NULL,
    index public.txindex NOT NULL,
    address character varying NOT NULL,
    value public.lovelace NOT NULL
);


ALTER TABLE public.tx_out OWNER TO nix;

--
-- Name: transactioninput; Type: VIEW; Schema: public; Owner: nix
--

CREATE VIEW public.transactioninput AS
 SELECT tx_out.address,
    tx.hash AS sourcetxid,
    tx_in.tx_out_index AS sourcetxindex,
    tx_out.value
   FROM ((public.tx
     JOIN public.tx_out ON ((tx.id = tx_out.tx_id)))
     JOIN public.tx_in ON ((tx_in.tx_out_id = tx.id)));


ALTER TABLE public.transactioninput OWNER TO nix;

--
-- Name: transactionoutput; Type: VIEW; Schema: public; Owner: nix
--

CREATE VIEW public.transactionoutput AS
 SELECT tx_out.address,
    tx.hash AS txid,
    tx_out.index
   FROM (public.tx
     JOIN public.tx_out ON ((tx.id = tx_out.tx_id)));


ALTER TABLE public.transactionoutput OWNER TO nix;

--
-- Name: tx_id_seq; Type: SEQUENCE; Schema: public; Owner: nix
--

CREATE SEQUENCE public.tx_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.tx_id_seq OWNER TO nix;

--
-- Name: tx_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: nix
--

ALTER SEQUENCE public.tx_id_seq OWNED BY public.tx.id;


--
-- Name: tx_in_id_seq; Type: SEQUENCE; Schema: public; Owner: nix
--

CREATE SEQUENCE public.tx_in_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.tx_in_id_seq OWNER TO nix;

--
-- Name: tx_in_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: nix
--

ALTER SEQUENCE public.tx_in_id_seq OWNED BY public.tx_in.id;


--
-- Name: tx_out_id_seq; Type: SEQUENCE; Schema: public; Owner: nix
--

CREATE SEQUENCE public.tx_out_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.tx_out_id_seq OWNER TO nix;

--
-- Name: tx_out_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: nix
--

ALTER SEQUENCE public.tx_out_id_seq OWNED BY public.tx_out.id;


--
-- Name: utxo_view; Type: VIEW; Schema: public; Owner: nix
--

CREATE VIEW public.utxo_view AS
 SELECT tx_out.id,
    tx_out.tx_id,
    tx_out.index,
    tx_out.address,
    tx_out.value
   FROM (public.tx_out
     LEFT JOIN public.tx_in ON (((tx_out.tx_id = tx_in.tx_out_id) AND ((tx_out.index)::smallint = (tx_in.tx_out_index)::smallint))))
  WHERE (tx_in.tx_in_id IS NULL);


ALTER TABLE public.utxo_view OWNER TO nix;

--
-- Name: block id; Type: DEFAULT; Schema: public; Owner: nix
--

ALTER TABLE ONLY public.block ALTER COLUMN id SET DEFAULT nextval('public.block_id_seq'::regclass);


--
-- Name: meta id; Type: DEFAULT; Schema: public; Owner: nix
--

ALTER TABLE ONLY public.meta ALTER COLUMN id SET DEFAULT nextval('public.meta_id_seq'::regclass);


--
-- Name: schema_version id; Type: DEFAULT; Schema: public; Owner: nix
--

ALTER TABLE ONLY public.schema_version ALTER COLUMN id SET DEFAULT nextval('public.schema_version_id_seq'::regclass);


--
-- Name: slot_leader id; Type: DEFAULT; Schema: public; Owner: nix
--

ALTER TABLE ONLY public.slot_leader ALTER COLUMN id SET DEFAULT nextval('public.slot_leader_id_seq'::regclass);


--
-- Name: tx id; Type: DEFAULT; Schema: public; Owner: nix
--

ALTER TABLE ONLY public.tx ALTER COLUMN id SET DEFAULT nextval('public.tx_id_seq'::regclass);


--
-- Name: tx_in id; Type: DEFAULT; Schema: public; Owner: nix
--

ALTER TABLE ONLY public.tx_in ALTER COLUMN id SET DEFAULT nextval('public.tx_in_id_seq'::regclass);


--
-- Name: tx_out id; Type: DEFAULT; Schema: public; Owner: nix
--

ALTER TABLE ONLY public.tx_out ALTER COLUMN id SET DEFAULT nextval('public.tx_out_id_seq'::regclass);


--
-- Name: block block_pkey; Type: CONSTRAINT; Schema: public; Owner: nix
--

ALTER TABLE ONLY public.block
    ADD CONSTRAINT block_pkey PRIMARY KEY (id);


--
-- Name: meta meta_pkey; Type: CONSTRAINT; Schema: public; Owner: nix
--

ALTER TABLE ONLY public.meta
    ADD CONSTRAINT meta_pkey PRIMARY KEY (id);


--
-- Name: schema_version schema_version_pkey; Type: CONSTRAINT; Schema: public; Owner: nix
--

ALTER TABLE ONLY public.schema_version
    ADD CONSTRAINT schema_version_pkey PRIMARY KEY (id);


--
-- Name: slot_leader slot_leader_pkey; Type: CONSTRAINT; Schema: public; Owner: nix
--

ALTER TABLE ONLY public.slot_leader
    ADD CONSTRAINT slot_leader_pkey PRIMARY KEY (id);


--
-- Name: tx_in tx_in_pkey; Type: CONSTRAINT; Schema: public; Owner: nix
--

ALTER TABLE ONLY public.tx_in
    ADD CONSTRAINT tx_in_pkey PRIMARY KEY (id);


--
-- Name: tx_out tx_out_pkey; Type: CONSTRAINT; Schema: public; Owner: nix
--

ALTER TABLE ONLY public.tx_out
    ADD CONSTRAINT tx_out_pkey PRIMARY KEY (id);


--
-- Name: tx tx_pkey; Type: CONSTRAINT; Schema: public; Owner: nix
--

ALTER TABLE ONLY public.tx
    ADD CONSTRAINT tx_pkey PRIMARY KEY (id);


--
-- Name: block unique_block; Type: CONSTRAINT; Schema: public; Owner: nix
--

ALTER TABLE ONLY public.block
    ADD CONSTRAINT unique_block UNIQUE (hash);


--
-- Name: meta unique_meta; Type: CONSTRAINT; Schema: public; Owner: nix
--

ALTER TABLE ONLY public.meta
    ADD CONSTRAINT unique_meta UNIQUE (start_time);


--
-- Name: slot_leader unique_slot_leader; Type: CONSTRAINT; Schema: public; Owner: nix
--

ALTER TABLE ONLY public.slot_leader
    ADD CONSTRAINT unique_slot_leader UNIQUE (hash);


--
-- Name: tx unique_tx; Type: CONSTRAINT; Schema: public; Owner: nix
--

ALTER TABLE ONLY public.tx
    ADD CONSTRAINT unique_tx UNIQUE (hash);


--
-- Name: tx_in unique_txin; Type: CONSTRAINT; Schema: public; Owner: nix
--

ALTER TABLE ONLY public.tx_in
    ADD CONSTRAINT unique_txin UNIQUE (tx_out_id, tx_out_index);


--
-- Name: tx_out unique_txout; Type: CONSTRAINT; Schema: public; Owner: nix
--

ALTER TABLE ONLY public.tx_out
    ADD CONSTRAINT unique_txout UNIQUE (tx_id, index);


--
-- Name: block block_previous_fkey; Type: FK CONSTRAINT; Schema: public; Owner: nix
--

ALTER TABLE ONLY public.block
    ADD CONSTRAINT block_previous_fkey FOREIGN KEY (previous) REFERENCES public.block(id);


--
-- Name: block block_slot_leader_fkey; Type: FK CONSTRAINT; Schema: public; Owner: nix
--

ALTER TABLE ONLY public.block
    ADD CONSTRAINT block_slot_leader_fkey FOREIGN KEY (slot_leader) REFERENCES public.slot_leader(id);


--
-- Name: tx tx_block_fkey; Type: FK CONSTRAINT; Schema: public; Owner: nix
--

ALTER TABLE ONLY public.tx
    ADD CONSTRAINT tx_block_fkey FOREIGN KEY (block) REFERENCES public.block(id);


--
-- Name: tx_in tx_in_tx_in_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: nix
--

ALTER TABLE ONLY public.tx_in
    ADD CONSTRAINT tx_in_tx_in_id_fkey FOREIGN KEY (tx_in_id) REFERENCES public.tx(id);


--
-- Name: tx_in tx_in_tx_out_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: nix
--

ALTER TABLE ONLY public.tx_in
    ADD CONSTRAINT tx_in_tx_out_id_fkey FOREIGN KEY (tx_out_id) REFERENCES public.tx(id);


--
-- Name: tx_out tx_out_tx_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: nix
--

ALTER TABLE ONLY public.tx_out
    ADD CONSTRAINT tx_out_tx_id_fkey FOREIGN KEY (tx_id) REFERENCES public.tx(id);


--
-- PostgreSQL database dump complete
--