01_object_structure.sql

--
-- PostgreSQL database dump
--

-- Dumped from database version 10.3 (Debian 10.3-1.pgdg70+1)
-- Dumped by pg_dump version 10.3 (Debian 10.3-1.pgdg70+1)

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 client_min_messages = warning;
SET row_security = off;

--
-- Name: solar_system; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA solar_system;


--
-- Name: SCHEMA solar_system; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON SCHEMA solar_system IS 'Solar system related entities.';


--
-- Name: object_id_seq; Type: SEQUENCE; Schema: solar_system; Owner: -
--

CREATE SEQUENCE solar_system.object_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: orbital_object; Type: TABLE; Schema: solar_system; Owner: -
--

CREATE TABLE solar_system.orbital_object (
    object_id integer DEFAULT nextval('solar_system.object_id_seq'::regclass) NOT NULL,
    name text NOT NULL,
    mean_distance bigint,
    orbital_host_id integer,
    CONSTRAINT distance_must_be_positive CHECK ((mean_distance > 0)),
    CONSTRAINT if_it_orbits_then_it_has_a_distance CHECK (((mean_distance IS NULL) OR (orbital_host_id IS NOT NULL)))
);


--
-- Name: TABLE orbital_object; Type: COMMENT; Schema: solar_system; Owner: -
--

COMMENT ON TABLE solar_system.orbital_object IS 'Object orbital informations.';


--
-- Name: COLUMN orbital_object.object_id; Type: COMMENT; Schema: solar_system; Owner: -
--

COMMENT ON COLUMN solar_system.orbital_object.object_id IS 'Technical identifier for orbital object.';


--
-- Name: COLUMN orbital_object.name; Type: COMMENT; Schema: solar_system; Owner: -
--

COMMENT ON COLUMN solar_system.orbital_object.name IS 'Unique identifier for the solar system object.';


--
-- Name: COLUMN orbital_object.mean_distance; Type: COMMENT; Schema: solar_system; Owner: -
--

COMMENT ON COLUMN solar_system.orbital_object.mean_distance IS 'Mean distance in kilometers between the object and the object it is satellite of. Mean distance is the semi-major axis.';


--
-- Name: COLUMN orbital_object.orbital_host_id; Type: COMMENT; Schema: solar_system; Owner: -
--

COMMENT ON COLUMN solar_system.orbital_object.orbital_host_id IS 'Identifer of the orbital object this object is satellite of.';


--
-- Name: CONSTRAINT distance_must_be_positive ON orbital_object; Type: COMMENT; Schema: solar_system; Owner: -
--

COMMENT ON CONSTRAINT distance_must_be_positive ON solar_system.orbital_object IS 'The distance to the orbital host must be strictly positive.
TODO: when storing the radius of the orbital object, we might ensure the distance is strictly higher than the host’s radius.';


--
-- Name: CONSTRAINT if_it_orbits_then_it_has_a_distance ON orbital_object; Type: COMMENT; Schema: solar_system; Owner: -
--

COMMENT ON CONSTRAINT if_it_orbits_then_it_has_a_distance ON solar_system.orbital_object IS 'Both columns "orbital_host" and "distance" must be set. In the special case of the object being the root object of the system (ie: the sun), both columns must be set to null.
If no host object then no distance.';


--
-- PostgreSQL database dump complete
--

02_object_data.sql

--
-- PostgreSQL database dump
--

-- Dumped from database version 10.3 (Debian 10.3-1.pgdg70+1)
-- Dumped by pg_dump version 10.3 (Debian 10.3-1.pgdg70+1)

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 client_min_messages = warning;
SET row_security = off;

--
-- Data for Name: orbital_object; Type: TABLE DATA; Schema: solar_system; Owner: -
--

INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (1, 'sun', NULL, NULL);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (2, 'mercure', 57909176, 1);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (3, 'venus', 108208930, 1);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (4, 'earth', 149597887, 1);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (5, 'moon', 384399, 4);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (6, 'mars', 227936637, 1);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (7, 'phobos', 9377, 6);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (8, 'deimos', 23460, 6);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (9, 'jupiter', 778412027, 1);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (10, 'io', 421800, 9);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (11, 'ganymède', 1070400, 9);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (12, 'callisto', 1882700, 9);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (13, 'europe', 671100, 9);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (14, 'saturne', 1421179772, 1);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (15, 'titan', 1221870, 14);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (16, 'thétys', 294992, 14);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (17, 'dioné', 377400, 14);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (18, 'japet', 3560840, 14);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (19, 'mimas', 185600, 14);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (20, 'encelade', 238020, 14);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (21, 'uranus', 2876679082, 1);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (22, 'miranda', 129900, 21);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (23, 'ariel', 190900, 21);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (24, 'umbriel', 266000, 21);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (25, 'titania', 435910, 21);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (26, 'obéron', 583520, 21);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (27, 'neptune', 4503443661, 1);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (28, 'triton', 354759, 27);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (29, 'néréide', 5513400, 27);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (30, 'larissa', 73548, 27);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (32, 'charon', 17181, 31);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (31, 'pluto', 5900898440, 1);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (34, 'ceres', 414103605, 1);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (35, 'eris', 10120200000, 1);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (36, 'dysnomia', 37580, 35);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (37, 'makémaké', 6846000000, 1);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (38, 'quaoar', 6489054000, 1);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (39, 'sedna', 78450000000, 1);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (40, 'hauméa', 6484000000, 1);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (41, 'orcus', 5862440000, 1);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (42, 'vanth', 8980, 41);
INSERT INTO solar_system.orbital_object (object_id, name, mean_distance, orbital_host_id) VALUES (43, '2007 OR10', 10021700000, 1);


--
-- Name: object_id_seq; Type: SEQUENCE SET; Schema: solar_system; Owner: -
--

SELECT pg_catalog.setval('solar_system.object_id_seq', 43, true);


--
-- PostgreSQL database dump complete
--

03_object_keys.sql

--
-- PostgreSQL database dump
--

-- Dumped from database version 10.3 (Debian 10.3-1.pgdg70+1)
-- Dumped by pg_dump version 10.3 (Debian 10.3-1.pgdg70+1)

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 client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

--
-- Name: orbital_object orbital_object_pkey; Type: CONSTRAINT; Schema: solar_system; Owner: -
--

ALTER TABLE ONLY solar_system.orbital_object
    ADD CONSTRAINT orbital_object_pkey PRIMARY KEY (object_id);


--
-- Name: orbital_object orbital_object_orbital_host_id_fkey; Type: FK CONSTRAINT; Schema: solar_system; Owner: -
--

ALTER TABLE ONLY solar_system.orbital_object
    ADD CONSTRAINT orbital_object_orbital_host_id_fkey FOREIGN KEY (orbital_host_id) REFERENCES solar_system.orbital_object(object_id);


--
-- PostgreSQL database dump complete
--