From d74a7b7aec40d83b940dbfe10850734075aa6c5c Mon Sep 17 00:00:00 2001 From: Dan Goodliffe Date: Sun, 27 Jun 2021 18:59:21 +0100 Subject: Add an existing table to the config --- lib/input/sql/selectColumns.sql | 5 +++++ lib/output/pq/sql/insertColumn.sql | 2 ++ lib/output/pq/sql/insertTable.sql | 3 +++ lib/output/pq/sql/selectSourceSchema.sql | 3 +++ lib/output/pq/updateDatabase.cpp | 31 +++++++++++++++++++++++++++++++ lib/output/pq/updateDatabase.h | 5 +++++ test/Jamfile.jam | 2 ++ test/sql/createTestTable.sql | 14 ++++++++++++++ test/sql/selectTestTable.sql | 2 ++ test/test-e2e.cpp | 9 +++++++++ 10 files changed, 76 insertions(+) create mode 100644 lib/input/sql/selectColumns.sql create mode 100644 lib/output/pq/sql/insertColumn.sql create mode 100644 lib/output/pq/sql/insertTable.sql create mode 100644 lib/output/pq/sql/selectSourceSchema.sql create mode 100644 test/sql/createTestTable.sql create mode 100644 test/sql/selectTestTable.sql diff --git a/lib/input/sql/selectColumns.sql b/lib/input/sql/selectColumns.sql new file mode 100644 index 0000000..a8a224c --- /dev/null +++ b/lib/input/sql/selectColumns.sql @@ -0,0 +1,5 @@ +SELECT column_name, is_nullable = 'YES', column_type, column_key = 'PRI' +FROM information_schema.columns +WHERE table_schema = DATABASE() +AND table_name = ? +ORDER BY ordinal_position diff --git a/lib/output/pq/sql/insertColumn.sql b/lib/output/pq/sql/insertColumn.sql new file mode 100644 index 0000000..b8efde9 --- /dev/null +++ b/lib/output/pq/sql/insertColumn.sql @@ -0,0 +1,2 @@ +INSERT INTO mygrate.table_columns(column_name, mysql_ordinal, table_id) +VALUES($1, $2, $3) diff --git a/lib/output/pq/sql/insertTable.sql b/lib/output/pq/sql/insertTable.sql new file mode 100644 index 0000000..4b559d0 --- /dev/null +++ b/lib/output/pq/sql/insertTable.sql @@ -0,0 +1,3 @@ +INSERT INTO mygrate.tables(table_name, source_id) +VALUES($1, $2) +RETURNING table_id diff --git a/lib/output/pq/sql/selectSourceSchema.sql b/lib/output/pq/sql/selectSourceSchema.sql new file mode 100644 index 0000000..a583c4f --- /dev/null +++ b/lib/output/pq/sql/selectSourceSchema.sql @@ -0,0 +1,3 @@ +SELECT table_schema +FROM mygrate.source s +WHERE s.source_id = $1 diff --git a/lib/output/pq/updateDatabase.cpp b/lib/output/pq/updateDatabase.cpp index 52388be..389d47a 100644 --- a/lib/output/pq/updateDatabase.cpp +++ b/lib/output/pq/updateDatabase.cpp @@ -1,19 +1,25 @@ #include "updateDatabase.h" #include "pqConn.h" +#include "typeMapper.h" #include #include #include #include #include +#include #include +#include #include #include +#include #include +#include #include #include #include #include #include +#include namespace MyGrate::Output::Pq { UpdateDatabase::UpdateDatabase(const char * const str, uint64_t s) : @@ -56,4 +62,29 @@ namespace MyGrate::Output::Pq { }); return UpdateDatabase(pq->connstr.c_str(), source_id); } + + void + UpdateDatabase::addTable(Input::MySQLConn * conn, const char * tableName) + { + auto cols = input::sql::selectColumns::execute(conn, tableName); + verify(cols->rows() > 0, "Table has no rows"); + Tx {this}([&] { + const auto table_id = **output::pq::sql::insertTable::execute(this, tableName, source); + std::stringstream ct; + scprintf<"CREATE TABLE %?.%?(">(ct, schema, tableName); + TypeMapper tm; + for (auto col : *cols) { + output::pq::sql::insertColumn::execute(this, col[0], col.currentRow(), table_id); + if (col.currentRow()) + ct << ','; + scprintf<"%? %?">(ct, col[0], tm.map(col[2], scprintf<"%?.%?">(tableName, col[0]))); + if (!col[1]) + ct << " not null"; + if (col[3]) + ct << " primary key"; + } + ct << ")"; + this->query(ct.str().c_str()); + }); + } } diff --git a/lib/output/pq/updateDatabase.h b/lib/output/pq/updateDatabase.h index 3d3e80d..dd7aaa0 100644 --- a/lib/output/pq/updateDatabase.h +++ b/lib/output/pq/updateDatabase.h @@ -6,6 +6,9 @@ #include #include +namespace MyGrate::Input { + class MySQLConn; +} namespace MyGrate::Output::Pq { struct ColumnDef { std::string name; @@ -35,6 +38,8 @@ namespace MyGrate::Output::Pq { static UpdateDatabase createNew(PqConn *, const char * host, const char * un, const char * pw, unsigned short p, const char * db, int sid, const char * sc); + void addTable(Input::MySQLConn *, const char * tableName); + const uint64_t source; const std::string schema; diff --git a/test/Jamfile.jam b/test/Jamfile.jam index d0b0e70..8486a14 100644 --- a/test/Jamfile.jam +++ b/test/Jamfile.jam @@ -8,6 +8,7 @@ project : requirements ; lib testdb : + [ glob-tree *.sql : bin ] [ glob testdb-*.cpp ] : static ; @@ -18,4 +19,5 @@ run test-streams.cpp ; run test-misc.cpp ; run test-mysql.cpp : : : testdb ; run test-postgresql.cpp : -- : ../db/schema.sql : testdb ; +run test-e2e.cpp : -- : ../db/schema.sql : testdb testdb ; run test-mapping.cpp : : : testdb testdb ; diff --git a/test/sql/createTestTable.sql b/test/sql/createTestTable.sql new file mode 100644 index 0000000..fdc1c06 --- /dev/null +++ b/test/sql/createTestTable.sql @@ -0,0 +1,14 @@ +CREATE TABLE session( + id int(10) unsigned not null auto_increment, + session_id varchar(255) not null, + username varchar(10) not null collate utf8_bin, + user_lvl enum('standard', 'reseller', 'sysadmin', 'groupadm') not null default 'standard', + ip_addr varchar(255) not null, + port varchar(255) not null, + created datetime not null, + modified datetime not null, + last_action varchar(255) null default null, + + constraint `PRIMARY` primary key(id), + constraint session_id unique(session_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; diff --git a/test/sql/selectTestTable.sql b/test/sql/selectTestTable.sql new file mode 100644 index 0000000..2543786 --- /dev/null +++ b/test/sql/selectTestTable.sql @@ -0,0 +1,2 @@ +SELECT id, session_id, user_lvl, ip_addr, port, created, modified, last_action +FROM testout.session diff --git a/test/test-e2e.cpp b/test/test-e2e.cpp index fe57e17..9906a0b 100644 --- a/test/test-e2e.cpp +++ b/test/test-e2e.cpp @@ -1,9 +1,12 @@ #define BOOST_TEST_MODULE EndToEnd +#include #include #include "testdb-mysql.h" #include "testdb-postgresql.h" #include +#include +#include BOOST_AUTO_TEST_CASE(e2e) { @@ -13,10 +16,16 @@ BOOST_AUTO_TEST_CASE(e2e) PqConnDB pq {ROOT "/db/schema.sql"}; auto pqm = pq.mock(); + auto mym = my.mock(); auto out = MyGrate::Output::Pq::UpdateDatabase::createNew(&pqm, MySQLDB::SERVER, MySQLDB::USER, MySQLDB::PASSWORD, MySQLDB::PORT, my.mockname.c_str(), 100, target_schema); BOOST_CHECK_EQUAL(out.source, 1); auto src = out.getSource(); BOOST_REQUIRE(src); + + MyGrate::sql::createTestTable::execute(&mym); + out.addTable(&mym, "session"); + + BOOST_CHECK_EQUAL(MyGrate::sql::selectTestTable::execute(&pqm)->rows(), 0); } -- cgit v1.2.3