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