From d8025ff93d66ec1ff7a6ffd90783e877e9eaafff Mon Sep 17 00:00:00 2001 From: Dan Goodliffe Date: Sun, 13 Jun 2021 02:42:26 +0100 Subject: Support creating a PostgreSQL mock DB and filling it with a schema script --- db/schema.sql | 187 +++++++++++++++++++++++++++++++++++++++++++++ test/Jamfile.jam | 1 + test/test-postgresql.cpp | 8 ++ test/testdb-postgresql.cpp | 11 +++ test/testdb-postgresql.h | 1 + 5 files changed, 208 insertions(+) create mode 100644 db/schema.sql diff --git a/db/schema.sql b/db/schema.sql new file mode 100644 index 0000000..667fc48 --- /dev/null +++ b/db/schema.sql @@ -0,0 +1,187 @@ +-- +-- PostgreSQL database dump +-- + +-- Dumped from database version 12.7 +-- Dumped by pg_dump version 13.3 + +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: mygrate; Type: SCHEMA; Schema: -; Owner: - +-- + +CREATE SCHEMA mygrate; + + +SET default_tablespace = ''; + +SET default_table_access_method = heap; + +-- +-- Name: source; Type: TABLE; Schema: mygrate; Owner: - +-- + +CREATE TABLE mygrate.source ( + source_id integer NOT NULL, + host text NOT NULL, + username text NOT NULL, + password text NOT NULL, + port integer NOT NULL, + filename text NOT NULL, + "position" bigint NOT NULL, + serverid integer NOT NULL, + table_schema name NOT NULL COLLATE pg_catalog."default" +); + + +-- +-- Name: source_id_seq; Type: SEQUENCE; Schema: mygrate; Owner: - +-- + +ALTER TABLE mygrate.source ALTER COLUMN source_id ADD GENERATED BY DEFAULT AS IDENTITY ( + SEQUENCE NAME mygrate.source_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1 +); + + +-- +-- Name: table_columns; Type: TABLE; Schema: mygrate; Owner: - +-- + +CREATE TABLE mygrate.table_columns ( + column_id integer NOT NULL, + column_name name NOT NULL, + mysql_ordinal integer NOT NULL, + table_id integer NOT NULL +); + + +-- +-- Name: table_columns_column_id_seq1; Type: SEQUENCE; Schema: mygrate; Owner: - +-- + +ALTER TABLE mygrate.table_columns ALTER COLUMN column_id ADD GENERATED ALWAYS AS IDENTITY ( + SEQUENCE NAME mygrate.table_columns_column_id_seq1 + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1 +); + + +-- +-- Name: tables; Type: TABLE; Schema: mygrate; Owner: - +-- + +CREATE TABLE mygrate.tables ( + table_id integer NOT NULL, + table_name name NOT NULL, + source_id integer NOT NULL +); + + +-- +-- Name: tables_table_id_seq1; Type: SEQUENCE; Schema: mygrate; Owner: - +-- + +ALTER TABLE mygrate.tables ALTER COLUMN table_id ADD GENERATED ALWAYS AS IDENTITY ( + SEQUENCE NAME mygrate.tables_table_id_seq1 + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1 +); + + +-- +-- Name: table_columns pk_table_columns; Type: CONSTRAINT; Schema: mygrate; Owner: - +-- + +ALTER TABLE ONLY mygrate.table_columns + ADD CONSTRAINT pk_table_columns PRIMARY KEY (column_id); + + +-- +-- Name: tables pk_tables; Type: CONSTRAINT; Schema: mygrate; Owner: - +-- + +ALTER TABLE ONLY mygrate.tables + ADD CONSTRAINT pk_tables PRIMARY KEY (table_id); + + +-- +-- Name: source source_pkey; Type: CONSTRAINT; Schema: mygrate; Owner: - +-- + +ALTER TABLE ONLY mygrate.source + ADD CONSTRAINT source_pkey PRIMARY KEY (source_id); + + +-- +-- Name: source uni_schema; Type: CONSTRAINT; Schema: mygrate; Owner: - +-- + +ALTER TABLE ONLY mygrate.source + ADD CONSTRAINT uni_schema UNIQUE (table_schema); + + +-- +-- Name: tables uni_source_table; Type: CONSTRAINT; Schema: mygrate; Owner: - +-- + +ALTER TABLE ONLY mygrate.tables + ADD CONSTRAINT uni_source_table UNIQUE (source_id, table_name); + + +-- +-- Name: table_columns uni_table_columnname; Type: CONSTRAINT; Schema: mygrate; Owner: - +-- + +ALTER TABLE ONLY mygrate.table_columns + ADD CONSTRAINT uni_table_columnname UNIQUE (table_id, column_name); + + +-- +-- Name: table_columns uni_table_columnord; Type: CONSTRAINT; Schema: mygrate; Owner: - +-- + +ALTER TABLE ONLY mygrate.table_columns + ADD CONSTRAINT uni_table_columnord UNIQUE (table_id, mysql_ordinal); + + +-- +-- Name: table_columns fk_table_columns_table; Type: FK CONSTRAINT; Schema: mygrate; Owner: - +-- + +ALTER TABLE ONLY mygrate.table_columns + ADD CONSTRAINT fk_table_columns_table FOREIGN KEY (table_id) REFERENCES mygrate.tables(table_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: tables fk_table_source; Type: FK CONSTRAINT; Schema: mygrate; Owner: - +-- + +ALTER TABLE ONLY mygrate.tables + ADD CONSTRAINT fk_table_source FOREIGN KEY (table_id) REFERENCES mygrate.source(source_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- PostgreSQL database dump complete +-- + diff --git a/test/Jamfile.jam b/test/Jamfile.jam index 691a68f..8025839 100644 --- a/test/Jamfile.jam +++ b/test/Jamfile.jam @@ -4,6 +4,7 @@ project : requirements ../lib//mygrate boost_unit_test_framework/shared BOOST_TEST_DYN_LINK + ROOT=\\\"$(root)\\\" ; lib testdb : diff --git a/test/test-postgresql.cpp b/test/test-postgresql.cpp index 38feb3c..faafcc0 100644 --- a/test/test-postgresql.cpp +++ b/test/test-postgresql.cpp @@ -82,3 +82,11 @@ BOOST_AUTO_TEST_CASE(mock) auto rs = MyGrate::DbStmt<"SELECT CURRENT_DATABASE()">::execute(&mdb); BOOST_CHECK_EQUAL(rs->at(0, 0).get().substr(0, 13), "mygrate_test_"); } + +BOOST_AUTO_TEST_CASE(mock_schema) +{ + MyGrate::Testing::PqConnDB db {ROOT "/db/schema.sql"}; + auto mdb = db.mock(); + auto rs = MyGrate::DbStmt<"SELECT COUNT(*) FROM mygrate.source">::execute(&mdb); + BOOST_CHECK_EQUAL(rs->at(0, 0).operator unsigned int(), 0); +} diff --git a/test/testdb-postgresql.cpp b/test/testdb-postgresql.cpp index 55f163f..c59640a 100644 --- a/test/testdb-postgresql.cpp +++ b/test/testdb-postgresql.cpp @@ -1,6 +1,7 @@ #include "testdb-postgresql.h" #include #include +#include #include #include @@ -15,6 +16,16 @@ namespace MyGrate { query(("CREATE DATABASE " + mockname).c_str()); } + PqConnDB::PqConnDB(const std::string & schemaFile) : PqConnDB() + { + auto mdb = mock(); + + std::stringstream buffer; + buffer << std::ifstream(schemaFile).rdbuf(); + + mdb.query(buffer.str().c_str()); + } + PqConnDB::~PqConnDB() { query(("DROP DATABASE IF EXISTS " + mockname).c_str()); diff --git a/test/testdb-postgresql.h b/test/testdb-postgresql.h index fc73d2a..c1cd14a 100644 --- a/test/testdb-postgresql.h +++ b/test/testdb-postgresql.h @@ -8,6 +8,7 @@ namespace MyGrate { class PqConnDB : public Output::Pq::PqConn { public: PqConnDB(); + PqConnDB(const std::string & schemaFile); ~PqConnDB(); Output::Pq::PqConn mock() const; -- cgit v1.2.3