--
-- COMPRESS 
--
-- TEST CASE1: CREATE TABLE AS
-- CASE 1.1
CREATE TABLE compress_10 (
	a00 int,
	a01 int,
	a02 int
) ;
SELECT relcmprs  FROM pg_class WHERE relname = 'compress_10';
CREATE TABLE compress_11 as SELECT * FROM compress_10;
SELECT relcmprs  FROM pg_class WHERE relname = 'compress_11';
ALTER TABLE compress_11 SET COMPRESS;
SELECT relcmprs  FROM pg_class WHERE relname = 'compress_11';
-- CASE 1.2 WHEN COMPRESS
DROP TABLE compress_11;
ALTER TABLE compress_10 SET COMPRESS;
SELECT relcmprs  FROM pg_class WHERE relname = 'compress_10';
CREATE TABLE compress_11 as SELECT * FROM compress_10;
SELECT relcmprs  FROM pg_class WHERE relname = 'compress_11';
ALTER TABLE compress_11 SET COMPRESS;
SELECT relcmprs  FROM pg_class WHERE relname = 'compress_11';
-- CASE 1.3 USER SPECIFY
DROP TABLE compress_11;
ALTER TABLE compress_10 SET NOCOMPRESS;
SELECT relcmprs  FROM pg_class WHERE relname = 'compress_10';
CREATE TABLE compress_11 as SELECT a00, a01 FROM compress_10;
SELECT relcmprs  FROM pg_class WHERE relname = 'compress_11';
ALTER TABLE compress_11 SET NOCOMPRESS;
SELECT relcmprs  FROM pg_class WHERE relname = 'compress_11';
-- CASE 1.4  USER SPECIFY
DROP TABLE compress_11;
CREATE TABLE compress_11(b00, b01) SELECT a00, a01 FROM compress_10;
SELECT relcmprs  FROM pg_class WHERE relname = 'compress_11';
ALTER TABLE compress_11 SET NOCOMPRESS;
SELECT relcmprs  FROM pg_class WHERE relname = 'compress_11';
-- CLEAN UP
DROP TABLE compress_11;
DROP TABLE compress_10;
-- CASE 1.5
CREATE TABLE compress_10 (
	a00 int DELTA,
	a01 int DICTIONARY,
	a02 int NOCOMPRESS,
	a03 varchar(100) PREFIX,
	a04 int
) ;
CREATE TABLE compress_11 as SELECT * FROM compress_10;
SELECT attname, attcmprmode FROM pg_attribute WHERE attname LIKE 'a%' and attrelid = (SELECT oid FROM pg_class WHERE relname='compress_10') ORDER BY attname;
SELECT attname, attcmprmode FROM pg_attribute WHERE attname LIKE 'a%' and attrelid = (SELECT oid FROM pg_class WHERE relname='compress_11') ORDER BY attname;
-- CLEAN UP
DROP TABLE compress_11;
DROP TABLE compress_10;
-- TEST CASE2: CREATE TABLE OF type_name
-- CASE 1.1
CREATE TYPE compress_type_12 AS (name text, salary numeric);
SELECT relcmprs  FROM pg_class WHERE relname = 'compress_type_12';
CREATE TABLE compress_12 OF compress_type_12 (
PRIMARY KEY (name),
salary WITH OPTIONS DEFAULT 1000
);
SELECT relcmprs  FROM pg_class WHERE relname = 'compress_12';
ALTER TABLE compress_12 SET COMPRESS;
SELECT relcmprs  FROM pg_class WHERE relname = 'compress_12';
-- CASE 1.2
DROP TABLE compress_12;
CREATE TABLE compress_12 OF compress_type_12 (
PRIMARY KEY (name),
salary WITH OPTIONS DEFAULT 1000
) ;
SELECT relcmprs  FROM pg_class WHERE relname = 'compress_12';
ALTER TABLE compress_12 SET NOCOMPRESS;
SELECT relcmprs  FROM pg_class WHERE relname = 'compress_12';
-- CLEAN UP
DROP TABLE compress_12;
-- CASE 1.3 COPY FROM
CREATE TYPE compress_type_13 AS (
	a00 INT ,
	a01 INT ,
	a02 INT ,
	a03 INT ,
	a04 INT ,
	a05 INT ,
	a06 INT ,
	a07 INT ,
	a08 INT ,
	a09 INT
);
CREATE TABLE compress_14 OF compress_type_13 ;
CREATE TABLE compress_13(
	a00 INT ,
	a01 INT ,
	a02 INT ,
	a03 INT ,
	a04 INT ,
	a05 INT ,
	a06 INT ,
	a07 INT ,
	a08 INT ,
	a09 INT);
INSERT INTO compress_13 VALUES (100, 200, 300, 400, 500, 600, 700, 800, 900, 1000);
INSERT INTO compress_13 SELECT * FROM compress_13;
INSERT INTO compress_13 SELECT * FROM compress_13;
INSERT INTO compress_13 SELECT * FROM compress_13;
INSERT INTO compress_13 SELECT * FROM compress_13;
INSERT INTO compress_13 SELECT * FROM compress_13;
INSERT INTO compress_13 SELECT * FROM compress_13;
INSERT INTO compress_13 SELECT * FROM compress_13;
INSERT INTO compress_13 SELECT * FROM compress_13;
INSERT INTO compress_13 SELECT * FROM compress_13;
INSERT INTO compress_13 SELECT * FROM compress_13;
INSERT INTO compress_13 SELECT * FROM compress_13;
INSERT INTO compress_13 SELECT * FROM compress_13;
INSERT INTO compress_13 SELECT * FROM compress_13;
INSERT INTO compress_13 SELECT * FROM compress_13;
COPY compress_13 TO '@abs_srcdir@/data/compress_13.tmp.data';
COPY compress_14 FROM '@abs_srcdir@/data/compress_13.tmp.data';
SELECT (SELECT pg_relation_size('compress_14')) < (SELECT pg_relation_size('compress_13'));
SELECT (SELECT COUNT(*) FROM compress_13) - (SELECT COUNT(*) FROM compress_14);
(SELECT * FROM compress_13) MINUS ALL (SELECT * FROM compress_14);
(SELECT * FROM compress_14) MINUS ALL (SELECT * FROM compress_13);
-- CLEAN UP
DROP TABLE compress_14;
DROP TABLE compress_13;
DROP TYPE compress_type_13;
\! rm @abs_srcdir@/data/compress_13.tmp.data
-- TEST CASE3: CREATE TABLE IF NOT EXISTS OF type_name
CREATE TABLE IF NOT EXISTS compress_15 OF compress_type_12 (
PRIMARY KEY (name),
salary WITH OPTIONS DEFAULT 1000
);
ALTER TABLE compress_15 SET COMPRESS;
DROP TABLE compress_15;
DROP TYPE compress_type_12;
-- TEST CASE4: CREATE TABLE using LIKE
CREATE TABLE compress_17 (
	a00 int DELTA,
	a01 int DICTIONARY,
	a02 int NOCOMPRESS,
	a03 varchar(100) PREFIX,
	a04 int
) ;
CREATE TABLE compress_16 (LIKE compress_17);
-- CASE 4.1
SELECT relcmprs  FROM pg_class WHERE relname = 'compress_16';
SELECT relcmprs  FROM pg_class WHERE relname = 'compress_17';
SELECT attname, attcmprmode FROM pg_attribute WHERE attname LIKE 'a%' and attrelid = (SELECT oid FROM pg_class WHERE relname='compress_17') ORDER BY attname;
SELECT attname, attcmprmode FROM pg_attribute WHERE attname LIKE 'a%' and attrelid = (SELECT oid FROM pg_class WHERE relname='compress_16') ORDER BY attname;
-- CASE 4.2
DROP TABLE compress_16;
ALTER TABLE compress_17 SET NOCOMPRESS;
CREATE TABLE compress_16 (LIKE compress_17) ;
SELECT relcmprs  FROM pg_class WHERE relname = 'compress_16';
SELECT relcmprs  FROM pg_class WHERE relname = 'compress_17';
SELECT attname, attcmprmode FROM pg_attribute WHERE attname LIKE 'a%' and attrelid = (SELECT oid FROM pg_class WHERE relname='compress_17') ORDER BY attname;
SELECT attname, attcmprmode FROM pg_attribute WHERE attname LIKE 'a%' and attrelid = (SELECT oid FROM pg_class WHERE relname='compress_16') ORDER BY attname;
-- CLEAN UP
DROP TABLE compress_17;
DROP TABLE compress_16;
-- CASE5: ALTER TABLE ADD COLUMN
CREATE TABLE compress_18 (
	a00 int DELTA,
	a01 int DICTIONARY,
	a02 int NOCOMPRESS,
	a03 varchar(100) PREFIX,
	a04 int
) ;
ALTER TABLE compress_18 ADD COLUMN a05 int DELTA not null;
SELECT attname, attcmprmode FROM pg_attribute WHERE attname LIKE 'a%' and attrelid = (SELECT oid FROM pg_class WHERE relname='compress_18') ORDER BY attname;
DROP TABLE compress_18;