1--source have_odbc_postgresql.inc 2#--source include/not_embedded.inc 3 4# 5# To configure your system to be able to run this test, 6# follow through the following steps: 7# 8# 1. Install and configure PostgreSQL database to stat on the system startup 9# 10# 2. Create user, database, schema and tables to be used by mtr: 11# psql -U postgres < odbc_postgresql.sql 12# 13# 3. Install PostgreSQL ODBC Driver. 14# - On CentOS, Fedora: 15# sudo yum install postgresql-odbc 16# - On Ubuntu, Debian: 17# sudo apt-get install odbc-postgresql 18# 19# 4. Create a data source with the name "ConnectEnginePostgresql" 20# - On Windows: use odbcadm.exe 21# - On Linux: put these lines into /etc/odbc.ini 22# 23#[ConnectEnginePostgresql] 24#Description=PostgreSQL DSN for ConnectSE 25#Driver=PostgreSQL (should the path to the driver so file) 26#Database=mtr 27#Servername=localhost 28#Port=5432 29# 30# 5. Allow user "mtr" to connect to the database "mtr" 31# Add this line into the begginning of pg_hba.conf 32# (usually /var/lib/pgsql/data/pg_hba.conf on Linux): 33#host mtr mtr 127.0.0.1/32 password 34# 35# 6. Restart the server: 36# sudo service postgresql restart 37# 38# 39 40SET NAMES utf8; 41 42--echo # 43--echo # Checking CATFUNC=Tables 44--echo # 45--echo 46--echo # All tables in all schemas 47CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' CATFUNC=Tables; 48SELECT * FROM t1 ORDER BY Table_Schema, Table_Name; 49DROP TABLE t1; 50 51--echo # All tables in all schemas 52CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' CATFUNC=Tables TABNAME='%.%.%'; 53SELECT * FROM t1 ORDER BY Table_Schema, Table_Name; 54DROP TABLE t1; 55 56--echo # All tables in all schemas 57CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' CATFUNC=Tables TABNAME='%.%'; 58SELECT * FROM t1 ORDER BY Table_Schema, Table_Name; 59DROP TABLE t1; 60 61--echo # All tables in the default schema ("public") 62CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' CATFUNC=Tables TABNAME='%'; 63SELECT * FROM t1 ORDER BY Table_Schema, Table_Name; 64DROP TABLE t1; 65 66--echo # All tables "t1" in all schemas 67CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' CATFUNC=Tables TABNAME='%.%.t1'; 68SELECT * FROM t1 ORDER BY Table_Schema, Table_Name; 69DROP TABLE t1; 70 71--echo # All tables "t1" in all schemas 72CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' CATFUNC=Tables TABNAME='%.t1'; 73SELECT * FROM t1 ORDER BY Table_Schema, Table_Name; 74DROP TABLE t1; 75 76--echo # Table "t1" in the default schema ("public") 77CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' CATFUNC=Tables TABNAME='t1'; 78SELECT * FROM t1 ORDER BY Table_Schema, Table_Name; 79DROP TABLE t1; 80 81--echo # Table "t1" in the schema "public" 82CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' CATFUNC=Tables TABNAME='%.public.t1'; 83SELECT * FROM t1 ORDER BY Table_Schema, Table_Name; 84DROP TABLE t1; 85 86--echo # Table "t1" in the schema "schema1" 87CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' CATFUNC=Tables TABNAME='%.schema1.t1'; 88SELECT * FROM t1 ORDER BY Table_Schema, Table_Name; 89DROP TABLE t1; 90 91--echo # All tables "t1" in all schemas (Catalog name is ignored by PostgreSQL) 92CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' CATFUNC=Tables TABNAME='xxx.%.t1'; 93SELECT * FROM t1 ORDER BY Table_Schema, Table_Name; 94DROP TABLE t1; 95 96--echo # 97--echo # Checking CATFUNC=Columns 98--echo # 99--echo 100 101# 102# For some reasons SQLColumn (unlike SQLTables) include columns of system 103# tables from the schemas like "information_schema", "pg_catalog", "pg_toast". 104# So we add the "Table_Schema IN ('public','schema1')" clause into some queries. 105# 106 107--echo # All columns in the schemas "public" and "schema1" 108CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' CATFUNC=Columns; 109SELECT * FROM t1 WHERE Table_Schema IN ('public','schema1') ORDER BY Table_Schema, Table_Name; 110DROP TABLE t1; 111 112--echo # All columns in the schemas "public" and "schema1" 113CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' CATFUNC=Columns TABNAME='%.%.%'; 114SELECT * FROM t1 WHERE Table_Schema IN ('public','schema1') ORDER BY Table_Schema, Table_Name; 115DROP TABLE t1; 116 117--echo # All tables "t1" in all schemas 118CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' CATFUNC=Columns TABNAME='%.%.t1'; 119SELECT * FROM t1 ORDER BY Table_Schema, Table_Name; 120DROP TABLE t1; 121 122--echo # Table "t1" in the schema "public" 123CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' CATFUNC=Columns TABNAME='%.public.t1'; 124SELECT * FROM t1 ORDER BY Table_Schema, Table_Name; 125DROP TABLE t1; 126 127--echo # Table "t1" in the schema "schema1" 128CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' CATFUNC=Columns TABNAME='%.schema1.t1'; 129SELECT * FROM t1 ORDER BY Table_Schema, Table_Name; 130DROP TABLE t1; 131 132--echo # All tables "t1" in all schemas (Catalog name is ignored by PostgreSQL) 133CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr' CATFUNC=Columns TABNAME='xxx.%.t1'; 134SELECT * FROM t1 ORDER BY Table_Schema, Table_Name; 135DROP TABLE t1; 136 137 138--echo # 139--echo # Checking tables 140--echo # 141--echo 142 143--echo # Table "t1" in the default schema ("public") 144CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr'; 145SHOW CREATE TABLE t1; 146SELECT * FROM t1; 147 148CREATE TABLE t2 AS SELECT * FROM t1; 149SHOW CREATE TABLE t2; 150SELECT * FROM t2; 151DROP TABLE t2; 152 153CREATE VIEW v1 AS SELECT * FROM t1; 154SELECT * FROM v1; 155DROP VIEW v1; 156DROP TABLE t1; 157 158--echo # Table "t1" in the schema "public" 159CREATE TABLE t1 ENGINE=CONNECT TABNAME='public.t1' TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr'; 160SHOW CREATE TABLE t1; 161SELECT * FROM t1; 162DROP TABLE t1; 163 164--echo # Table "t1" in the schema "schema1" 165CREATE TABLE t1 ENGINE=CONNECT TABNAME='schema1.t1' CHARSET=utf8 DATA_CHARSET=utf8 TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr'; 166SHOW CREATE TABLE t1; 167SELECT * FROM t1; 168 169CREATE TABLE t2 AS SELECT * FROM t1; 170SHOW CREATE TABLE t2; 171SELECT * FROM t2; 172DROP TABLE t2; 173 174CREATE VIEW v1 AS SELECT * FROM t1; 175SELECT * FROM v1; 176DROP VIEW v1; 177DROP TABLE t1; 178 179--echo # View "v1" in the schema "schema1" 180CREATE TABLE t1 ENGINE=CONNECT TABNAME='schema1.v1' CHARSET=utf8 DATA_CHARSET=utf8 TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr'; 181SHOW CREATE TABLE t1; 182SELECT * FROM t1; 183 184CREATE TABLE t2 AS SELECT * FROM t1; 185SHOW CREATE TABLE t2; 186SELECT * FROM t2; 187DROP TABLE t2; 188 189CREATE VIEW v1 AS SELECT * FROM t1; 190SELECT * FROM v1; 191DROP VIEW v1; 192DROP TABLE t1; 193 194--echo # Table "t2" in the schema "schema1" 195CREATE TABLE t1 ENGINE=CONNECT TABNAME='schema1.t2' CHARSET=utf8 DATA_CHARSET=utf8 TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEnginePostgresql;UID=mtr;PWD=mtr'; 196SHOW CREATE TABLE t1; 197SELECT * FROM t1; 198 199CREATE TABLE t2 AS SELECT * FROM t1; 200SHOW CREATE TABLE t2; 201SELECT * FROM t2; 202DROP TABLE t2; 203 204CREATE VIEW v1 AS SELECT * FROM t1; 205SELECT * FROM v1; 206DROP VIEW v1; 207DROP TABLE t1; 208