Repository: incubator-hawq Updated Branches: refs/heads/master 0bb0a2074 -> 4de3d107c
HAWQ-905. Add feature test for temp table with new test framework Project: http://git-wip-us.apache.org/repos/asf/incubator-hawq/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-hawq/commit/4de3d107 Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq/tree/4de3d107 Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq/diff/4de3d107 Branch: refs/heads/master Commit: 4de3d107c5042ea8beab4b265c52276c27de9727 Parents: 0bb0a20 Author: ivan <iw...@pivotal.io> Authored: Thu Jul 28 11:04:40 2016 +0800 Committer: ivan <iw...@pivotal.io> Committed: Thu Jul 28 11:04:40 2016 +0800 ---------------------------------------------------------------------- src/test/feature/lib/sql_util.cpp | 40 +++-- src/test/feature/lib/sql_util.h | 10 +- src/test/feature/query/ans/temp.ans | 238 ++++++++++++++++++++++++++ src/test/feature/query/sql/temp.sql | 163 ++++++++++++++++++ src/test/feature/query/test_temp.cpp | 19 ++ src/test/feature/toast/ans/goh_toast.ans | 4 - 6 files changed, 458 insertions(+), 16 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/4de3d107/src/test/feature/lib/sql_util.cpp ---------------------------------------------------------------------- diff --git a/src/test/feature/lib/sql_util.cpp b/src/test/feature/lib/sql_util.cpp index 35e8ffe..c11e519 100644 --- a/src/test/feature/lib/sql_util.cpp +++ b/src/test/feature/lib/sql_util.cpp @@ -20,7 +20,7 @@ using std::string; namespace hawq { namespace test { -SQLUtility::SQLUtility() +SQLUtility::SQLUtility(SQLUtilityMode mode) : testRootPath(getTestRootPath()), test_info(::testing::UnitTest::GetInstance()->current_test_info()) { auto getConnection = [&] () { @@ -35,15 +35,30 @@ SQLUtility::SQLUtility() }; getConnection(); - schemaName = - string(test_info->test_case_name()) + "_" + test_info->name(); - exec("DROP SCHEMA IF EXISTS " + schemaName + " CASCADE"); - exec("CREATE SCHEMA " + schemaName); + if (MODE_SCHEMA == mode) { + schemaName = string(test_info->test_case_name()) + "_" + test_info->name(); + exec("DROP SCHEMA IF EXISTS " + schemaName + " CASCADE"); + exec("CREATE SCHEMA " + schemaName); + + } else { + schemaName = HAWQ_DEFAULT_SCHEMA; + databaseName = "db_" + string(test_info->test_case_name()) + "_" + test_info->name(); + std::transform(databaseName.begin(), databaseName.end(), databaseName.begin(), ::tolower); + exec("DROP DATABASE IF EXISTS " + databaseName); + exec("CREATE DATABASE " + databaseName); + } } SQLUtility::~SQLUtility() { - if (!test_info->result()->Failed()) - exec("DROP SCHEMA " + schemaName + " CASCADE"); + if (!test_info->result()->Failed()) { + if (schemaName != HAWQ_DEFAULT_SCHEMA) { + exec("DROP SCHEMA " + schemaName + " CASCADE"); + } + + if (!databaseName.empty()) { + exec("DROP DATABASE " + databaseName); + } + } } void SQLUtility::exec(const string &sql) { @@ -145,7 +160,7 @@ void SQLUtility::execSQLFile(const string &sqlFile, const string SQLUtility::generateSQLFile(const string &sqlFile) { const string originSqlFile = testRootPath + "/" + sqlFile; - const string newSqlFile = "/tmp/" + schemaName + ".sql"; + const string newSqlFile = "/tmp/" + string(test_info->test_case_name()) + "_" + test_info->name() + ".sql"; std::fstream in; in.open(originSqlFile, std::ios::in); if (!in.is_open()) { @@ -156,9 +171,12 @@ const string SQLUtility::generateSQLFile(const string &sqlFile) { if (!out.is_open()) { EXPECT_TRUE(false) << "Error opening file " << newSqlFile; } - out << "-- start_ignore" << std::endl - << "SET SEARCH_PATH=" + schemaName + ";" << std::endl - << "-- end_ignore" << std::endl; + out << "-- start_ignore" << std::endl; + out << "SET SEARCH_PATH=" + schemaName + ";" << std::endl; + if (!databaseName.empty()) { + out << "\\c " << databaseName << std::endl; + } + out << "-- end_ignore" << std::endl; string line; while (getline(in, line)) { out << line << std::endl; http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/4de3d107/src/test/feature/lib/sql_util.h ---------------------------------------------------------------------- diff --git a/src/test/feature/lib/sql_util.h b/src/test/feature/lib/sql_util.h index ea75aa3..6e8439a 100644 --- a/src/test/feature/lib/sql_util.h +++ b/src/test/feature/lib/sql_util.h @@ -12,6 +12,7 @@ #define HAWQ_PORT (getenv("PGPORT") ? getenv("PGPORT") : "5432") #define HAWQ_USER (getenv("PGUSER") ? getenv("PGUSER") : "") #define HAWQ_PASSWORD (getenv("PGPASSWORD") ? getenv("PGPASSWORD") : "") +#define HAWQ_DEFAULT_SCHEMA ("public") namespace hawq { namespace test { @@ -22,9 +23,15 @@ struct FilePath { std::string fileSuffix; }; +enum SQLUtilityMode { + MODE_SCHEMA, + MODE_DATABASE, + MODE_MAX_NUM +}; + class SQLUtility { public: - SQLUtility(); + SQLUtility(SQLUtilityMode mode = MODE_SCHEMA); ~SQLUtility(); // Execute sql command @@ -86,6 +93,7 @@ class SQLUtility { private: std::string schemaName; + std::string databaseName; std::unique_ptr<hawq::test::PSQL> conn; std::string testRootPath; const ::testing::TestInfo *const test_info; http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/4de3d107/src/test/feature/query/ans/temp.ans ---------------------------------------------------------------------- diff --git a/src/test/feature/query/ans/temp.ans b/src/test/feature/query/ans/temp.ans new file mode 100755 index 0000000..56d96aa --- /dev/null +++ b/src/test/feature/query/ans/temp.ans @@ -0,0 +1,238 @@ +-- +-- TEMP +-- Test temp relations and indexes +-- +-- test temp table/index masking +CREATE TABLE temptest(col int); +CREATE TABLE +-- CREATE INDEX i_temptest ON temptest(col); +CREATE TEMP TABLE temptest(tcol int); +CREATE TABLE +-- CREATE INDEX i_temptest ON temptest(tcol); +SELECT * FROM temptest; + tcol +------ +(0 rows) + +-- DROP INDEX i_temptest; +DROP TABLE temptest; +DROP TABLE +SELECT * FROM temptest; + col +----- +(0 rows) + +-- DROP INDEX i_temptest; +DROP TABLE temptest; +DROP TABLE +-- test temp table selects +CREATE TABLE temptest(col int); +CREATE TABLE +INSERT INTO temptest VALUES (1); +INSERT 0 1 +CREATE TEMP TABLE temptest(tcol float); +CREATE TABLE +INSERT INTO temptest VALUES (2.1); +INSERT 0 1 +SELECT * FROM temptest; + tcol +------ + 2.1 +(1 row) + +DROP TABLE temptest; +DROP TABLE +SELECT * FROM temptest; + col +----- + 1 +(1 row) + +DROP TABLE temptest; +DROP TABLE +-- test temp table deletion +CREATE TEMP TABLE temptest(col int); +CREATE TABLE +\c db_testtemp_basictest +You are now connected to database "db_testtemp_basictest" as user "ivan". +SELECT * FROM temptest; +psql:/tmp/TestTemp_BasicTest.sql:56: ERROR: relation "temptest" does not exist +LINE 1: SELECT * FROM temptest; + ^ +-- Test ON COMMIT DELETE ROWS +CREATE TEMP TABLE temptest(col int) ON COMMIT DELETE ROWS; +CREATE TABLE +BEGIN; +BEGIN +INSERT INTO temptest VALUES (1); +INSERT 0 1 +INSERT INTO temptest VALUES (2); +INSERT 0 1 +SELECT * FROM temptest; + col +----- + 1 + 2 +(2 rows) + +COMMIT; +COMMIT +SELECT * FROM temptest; + col +----- +(0 rows) + +DROP TABLE temptest; +DROP TABLE +BEGIN; +BEGIN +CREATE TEMP TABLE temptest(col) ON COMMIT DELETE ROWS AS SELECT 1; +SELECT 1 +SELECT * FROM temptest; + col +----- + 1 +(1 row) + +COMMIT; +COMMIT +SELECT * FROM temptest; + col +----- +(0 rows) + +DROP TABLE temptest; +DROP TABLE +-- Test ON COMMIT DROP +BEGIN; +BEGIN +CREATE TEMP TABLE temptest(col int) ON COMMIT DROP; +CREATE TABLE +INSERT INTO temptest VALUES (1); +INSERT 0 1 +INSERT INTO temptest VALUES (2); +INSERT 0 1 +SELECT * FROM temptest; + col +----- + 1 + 2 +(2 rows) + +COMMIT; +COMMIT +SELECT * FROM temptest; +psql:/tmp/TestTemp_BasicTest.sql:95: ERROR: relation "temptest" does not exist +LINE 1: SELECT * FROM temptest; + ^ +BEGIN; +BEGIN +CREATE TEMP TABLE temptest(col) ON COMMIT DROP AS SELECT 1; +SELECT 1 +SELECT * FROM temptest; + col +----- + 1 +(1 row) + +COMMIT; +COMMIT +SELECT * FROM temptest; +psql:/tmp/TestTemp_BasicTest.sql:103: ERROR: relation "temptest" does not exist +LINE 1: SELECT * FROM temptest; + ^ +-- ON COMMIT is only allowed for TEMP +CREATE TABLE temptest(col int) ON COMMIT DELETE ROWS; +psql:/tmp/TestTemp_BasicTest.sql:107: ERROR: ON COMMIT can only be used on temporary tables +CREATE TABLE temptest(col) ON COMMIT DELETE ROWS AS SELECT 1; +psql:/tmp/TestTemp_BasicTest.sql:108: ERROR: ON COMMIT can only be used on temporary tables +-- Test foreign keys +-- BEGIN; +-- CREATE TEMP TABLE temptest1(col int PRIMARY KEY) DISTRIBUTED BY (col); +-- CREATE TEMP TABLE temptest2(col int REFERENCES temptest1) +-- ON COMMIT DELETE ROWS; +-- INSERT INTO temptest1 VALUES (1); +-- INSERT INTO temptest2 VALUES (1); +-- COMMIT; +-- SELECT * FROM temptest1; +-- SELECT * FROM temptest2; +-- BEGIN; +-- CREATE TEMP TABLE temptest3(col int PRIMARY KEY) ON COMMIT DELETE ROWS DISTRIBUTED BY (col); +-- CREATE TEMP TABLE temptest4(col int REFERENCES temptest3); +-- COMMIT; +-- Test manipulation of temp schema's placement in search path +create table public.whereami (f1 text); +CREATE TABLE +insert into public.whereami values ('public'); +INSERT 0 1 +create temp table whereami (f1 text); +CREATE TABLE +insert into whereami values ('temp'); +INSERT 0 1 +-- create function public.whoami() returns text +-- as $$select 'public'::text$$ language sql; +-- create function pg_temp.whoami() returns text +-- as $$select 'temp'::text$$ language sql; +-- default should have pg_temp implicitly first, but only for tables +select * from whereami; + f1 +------ + temp +(1 row) + +-- select whoami(); +-- can list temp first explicitly, but it still doesn't affect functions +set search_path = pg_temp, public; +SET +select * from whereami; + f1 +------ + temp +(1 row) + +-- select whoami(); +-- or put it last for security +set search_path = public, pg_temp; +SET +select * from whereami; + f1 +-------- + public +(1 row) + +-- select whoami(); +-- you can invoke a temp function explicitly, though +-- select pg_temp.whoami(); +drop table public.whereami; +DROP TABLE +-- Test querying column using pg_temp schema +create table pg_temp.test (row integer, count integer); +CREATE TABLE +insert into pg_temp.test values (1, 10), (2, 20), (3, 30); +INSERT 0 3 +select avg(pg_temp.test.count) from pg_temp.test; + avg +----- + 20 +(1 row) + +select avg(test.count) from pg_temp.test; + avg +----- + 20 +(1 row) + +select avg(count) from pg_temp.test; + avg +----- + 20 +(1 row) + +select case when pg_temp.test.count = 30 then 30 when pg_temp.test.count = 20 then 20 else 10 end from pg_temp.test; + case +------ + 10 + 20 + 30 +(3 rows) + http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/4de3d107/src/test/feature/query/sql/temp.sql ---------------------------------------------------------------------- diff --git a/src/test/feature/query/sql/temp.sql b/src/test/feature/query/sql/temp.sql new file mode 100644 index 0000000..3f889bb --- /dev/null +++ b/src/test/feature/query/sql/temp.sql @@ -0,0 +1,163 @@ +-- +-- TEMP +-- Test temp relations and indexes +-- + +-- test temp table/index masking + +CREATE TABLE temptest(col int); + +-- CREATE INDEX i_temptest ON temptest(col); + +CREATE TEMP TABLE temptest(tcol int); + +-- CREATE INDEX i_temptest ON temptest(tcol); + +SELECT * FROM temptest; + +-- DROP INDEX i_temptest; + +DROP TABLE temptest; + +SELECT * FROM temptest; + +-- DROP INDEX i_temptest; + +DROP TABLE temptest; + +-- test temp table selects + +CREATE TABLE temptest(col int); + +INSERT INTO temptest VALUES (1); + +CREATE TEMP TABLE temptest(tcol float); + +INSERT INTO temptest VALUES (2.1); + +SELECT * FROM temptest; + +DROP TABLE temptest; + +SELECT * FROM temptest; + +DROP TABLE temptest; + +-- test temp table deletion + +CREATE TEMP TABLE temptest(col int); + +\c db_testtemp_basictest + +SELECT * FROM temptest; + +-- Test ON COMMIT DELETE ROWS + +CREATE TEMP TABLE temptest(col int) ON COMMIT DELETE ROWS; + +BEGIN; +INSERT INTO temptest VALUES (1); +INSERT INTO temptest VALUES (2); + +SELECT * FROM temptest; +COMMIT; + +SELECT * FROM temptest; + +DROP TABLE temptest; + +BEGIN; +CREATE TEMP TABLE temptest(col) ON COMMIT DELETE ROWS AS SELECT 1; + +SELECT * FROM temptest; +COMMIT; + +SELECT * FROM temptest; + +DROP TABLE temptest; + +-- Test ON COMMIT DROP + +BEGIN; + +CREATE TEMP TABLE temptest(col int) ON COMMIT DROP; + +INSERT INTO temptest VALUES (1); +INSERT INTO temptest VALUES (2); + +SELECT * FROM temptest; +COMMIT; + +SELECT * FROM temptest; + +BEGIN; +CREATE TEMP TABLE temptest(col) ON COMMIT DROP AS SELECT 1; + +SELECT * FROM temptest; +COMMIT; + +SELECT * FROM temptest; + +-- ON COMMIT is only allowed for TEMP + +CREATE TABLE temptest(col int) ON COMMIT DELETE ROWS; +CREATE TABLE temptest(col) ON COMMIT DELETE ROWS AS SELECT 1; + +-- Test foreign keys +-- BEGIN; +-- CREATE TEMP TABLE temptest1(col int PRIMARY KEY) DISTRIBUTED BY (col); +-- CREATE TEMP TABLE temptest2(col int REFERENCES temptest1) +-- ON COMMIT DELETE ROWS; +-- INSERT INTO temptest1 VALUES (1); +-- INSERT INTO temptest2 VALUES (1); +-- COMMIT; +-- SELECT * FROM temptest1; +-- SELECT * FROM temptest2; + +-- BEGIN; +-- CREATE TEMP TABLE temptest3(col int PRIMARY KEY) ON COMMIT DELETE ROWS DISTRIBUTED BY (col); +-- CREATE TEMP TABLE temptest4(col int REFERENCES temptest3); +-- COMMIT; + +-- Test manipulation of temp schema's placement in search path + +create table public.whereami (f1 text); +insert into public.whereami values ('public'); + +create temp table whereami (f1 text); +insert into whereami values ('temp'); + +-- create function public.whoami() returns text +-- as $$select 'public'::text$$ language sql; + +-- create function pg_temp.whoami() returns text +-- as $$select 'temp'::text$$ language sql; + +-- default should have pg_temp implicitly first, but only for tables +select * from whereami; +-- select whoami(); + +-- can list temp first explicitly, but it still doesn't affect functions +set search_path = pg_temp, public; +select * from whereami; +-- select whoami(); + +-- or put it last for security +set search_path = public, pg_temp; +select * from whereami; +-- select whoami(); + +-- you can invoke a temp function explicitly, though +-- select pg_temp.whoami(); + +drop table public.whereami; + +-- Test querying column using pg_temp schema +create table pg_temp.test (row integer, count integer); +insert into pg_temp.test values (1, 10), (2, 20), (3, 30); +select avg(pg_temp.test.count) from pg_temp.test; +select avg(test.count) from pg_temp.test; +select avg(count) from pg_temp.test; + +select case when pg_temp.test.count = 30 then 30 when pg_temp.test.count = 20 then 20 else 10 end from pg_temp.test; + http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/4de3d107/src/test/feature/query/test_temp.cpp ---------------------------------------------------------------------- diff --git a/src/test/feature/query/test_temp.cpp b/src/test/feature/query/test_temp.cpp new file mode 100644 index 0000000..8f1497b --- /dev/null +++ b/src/test/feature/query/test_temp.cpp @@ -0,0 +1,19 @@ +#include "gtest/gtest.h" + +#include "lib/sql_util.h" + +using std::string; + +class TestTemp: public ::testing::Test +{ + public: + TestTemp() { } + ~TestTemp() {} +}; + +TEST_F(TestTemp, BasicTest) +{ + hawq::test::SQLUtility util(hawq::test::MODE_DATABASE); + util.execSQLFile("query/sql/temp.sql", + "query/ans/temp.ans"); +} http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/4de3d107/src/test/feature/toast/ans/goh_toast.ans ---------------------------------------------------------------------- diff --git a/src/test/feature/toast/ans/goh_toast.ans b/src/test/feature/toast/ans/goh_toast.ans index 42a6a3b..2f971ce 100644 --- a/src/test/feature/toast/ans/goh_toast.ans +++ b/src/test/feature/toast/ans/goh_toast.ans @@ -1,7 +1,3 @@ --- start_ignore -SET SEARCH_PATH=TestToast_BasicTest; -SET --- end_ignore CREATE TABLE toastable_ao(a text, b varchar, c int) with(appendonly=true, compresslevel=1) distributed randomly; CREATE TABLE -- INSERT