You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hawq.apache.org by iw...@apache.org on 2016/07/28 03:05:17 UTC
incubator-hawq git commit: HAWQ-905. Add feature test for temp table
with new test framework
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