xref: /PHP-7.4/ext/pdo_pgsql/tests/copy_from.phpt (revision d07a6fde) 
1--TEST--
2PDO PgSQL pgsqlCopyFromArray and pgsqlCopyFromFile
3--SKIPIF--
4<?php
5if (!extension_loaded('pdo') || !extension_loaded('pdo_pgsql')) die('skip not loaded');
6require __DIR__ . '/config.inc';
7require __DIR__ . '/../../../ext/pdo/tests/pdo_test.inc';
8PDOTest::skip();
9?>
10--FILE--
11<?php
12require __DIR__ . '/../../../ext/pdo/tests/pdo_test.inc';
13$db = PDOTest::test_factory(__DIR__ . '/common.phpt');
14$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
15$db->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);
16
17$db->exec('CREATE TABLE test (a integer not null primary key, b text, c integer)');
18
19echo "Preparing test file and array for CopyFrom tests\n";
20
21$tableRows = array();
22$tableRowsWithDifferentNullValues = array();
23
24for($i=0;$i<3;$i++) {
25	$firstParameter = $i;
26	$secondParameter = "test insert {$i}";
27	$tableRows[] = "{$firstParameter}\t{$secondParameter}\t\\N";
28	$tableRowsWithDifferentNullValues[] = "{$firstParameter};{$secondParameter};NULL";
29	$tableRowsWithDifferentNullValuesAndSelectedFields[] = "{$firstParameter};NULL";
30}
31$filename = 'test_pgsqlCopyFromFile.csv';
32$filenameWithDifferentNullValues = 'test_pgsqlCopyFromFileWithDifferentNullValues.csv';
33$filenameWithDifferentNullValuesAndSelectedFields = 'test_pgsqlCopyFromFileWithDifferentNullValuesAndSelectedFields.csv';
34
35file_put_contents($filename, implode("\n",$tableRows));
36file_put_contents($filenameWithDifferentNullValues, implode("\n",$tableRowsWithDifferentNullValues));
37file_put_contents($filenameWithDifferentNullValuesAndSelectedFields, implode("\n",$tableRowsWithDifferentNullValuesAndSelectedFields));
38
39echo "Testing pgsqlCopyFromArray() with default parameters\n";
40$db->beginTransaction();
41var_dump($db->pgsqlCopyFromArray('test',$tableRows));
42
43$stmt = $db->query("select * from test");
44foreach($stmt as $r) {
45	var_dump($r);
46}
47$db->rollback();
48
49echo "Testing pgsqlCopyFromArray() with different field separator and not null indicator\n";
50$db->beginTransaction();
51var_dump($db->pgsqlCopyFromArray('test',$tableRowsWithDifferentNullValues,";","NULL"));
52$stmt = $db->query("select * from test");
53foreach($stmt as $r) {
54	var_dump($r);
55}
56$db->rollback();
57
58echo "Testing pgsqlCopyFromArray() with only selected fields\n";
59$db->beginTransaction();
60var_dump($db->pgsqlCopyFromArray('test',$tableRowsWithDifferentNullValuesAndSelectedFields,";","NULL",'a,c'));
61$stmt = $db->query("select * from test");
62foreach($stmt as $r) {
63	var_dump($r);
64}
65$db->rollback();
66
67echo "Testing pgsqlCopyFromArray() with error\n";
68$db->beginTransaction();
69try {
70	var_dump($db->pgsqlCopyFromArray('test_error',$tableRowsWithDifferentNullValuesAndSelectedFields,";","NULL",'a,c'));
71} catch (Exception $e) {
72	echo "Exception: {$e->getMessage()}\n";
73}
74$db->rollback();
75
76echo "Testing pgsqlCopyFromFile() with default parameters\n";
77$db->beginTransaction();
78var_dump($db->pgsqlCopyFromFile('test',$filename));
79
80$stmt = $db->query("select * from test");
81foreach($stmt as $r) {
82	var_dump($r);
83}
84$db->rollback();
85
86echo "Testing pgsqlCopyFromFile() with different field separator and not null indicator\n";
87$db->beginTransaction();
88var_dump($db->pgsqlCopyFromFile('test',$filenameWithDifferentNullValues,";","NULL"));
89$stmt = $db->query("select * from test");
90foreach($stmt as $r) {
91	var_dump($r);
92}
93$db->rollback();
94
95echo "Testing pgsqlCopyFromFile() with only selected fields\n";
96$db->beginTransaction();
97var_dump($db->pgsqlCopyFromFile('test',$filenameWithDifferentNullValuesAndSelectedFields,";","NULL",'a,c'));
98$stmt = $db->query("select * from test");
99foreach($stmt as $r) {
100	var_dump($r);
101}
102$db->rollback();
103
104echo "Testing pgsqlCopyFromFile() with error\n";
105$db->beginTransaction();
106try {
107	var_dump($db->pgsqlCopyFromFile('test_error',$filenameWithDifferentNullValuesAndSelectedFields,";","NULL",'a,c'));
108} catch (Exception $e) {
109	echo "Exception: {$e->getMessage()}\n";
110}
111$db->rollback();
112
113echo "Testing pgsqlCopyFromFile() with non existing file\n";
114$db->beginTransaction();
115try {
116	var_dump($db->pgsqlCopyFromFile('test',"nonexisting/foo.csv",";","NULL",'a,c'));
117} catch (Exception $e) {
118	echo "Exception: {$e->getMessage()}\n";
119}
120$db->rollback();
121
122// Clean up
123foreach (array($filename, $filenameWithDifferentNullValues, $filenameWithDifferentNullValuesAndSelectedFields) as $f) {
124	@unlink($f);
125}
126?>
127--EXPECTF--
128Preparing test file and array for CopyFrom tests
129Testing pgsqlCopyFromArray() with default parameters
130bool(true)
131array(6) {
132  ["a"]=>
133  int(0)
134  [0]=>
135  int(0)
136  ["b"]=>
137  string(13) "test insert 0"
138  [1]=>
139  string(13) "test insert 0"
140  ["c"]=>
141  NULL
142  [2]=>
143  NULL
144}
145array(6) {
146  ["a"]=>
147  int(1)
148  [0]=>
149  int(1)
150  ["b"]=>
151  string(13) "test insert 1"
152  [1]=>
153  string(13) "test insert 1"
154  ["c"]=>
155  NULL
156  [2]=>
157  NULL
158}
159array(6) {
160  ["a"]=>
161  int(2)
162  [0]=>
163  int(2)
164  ["b"]=>
165  string(13) "test insert 2"
166  [1]=>
167  string(13) "test insert 2"
168  ["c"]=>
169  NULL
170  [2]=>
171  NULL
172}
173Testing pgsqlCopyFromArray() with different field separator and not null indicator
174bool(true)
175array(6) {
176  ["a"]=>
177  int(0)
178  [0]=>
179  int(0)
180  ["b"]=>
181  string(13) "test insert 0"
182  [1]=>
183  string(13) "test insert 0"
184  ["c"]=>
185  NULL
186  [2]=>
187  NULL
188}
189array(6) {
190  ["a"]=>
191  int(1)
192  [0]=>
193  int(1)
194  ["b"]=>
195  string(13) "test insert 1"
196  [1]=>
197  string(13) "test insert 1"
198  ["c"]=>
199  NULL
200  [2]=>
201  NULL
202}
203array(6) {
204  ["a"]=>
205  int(2)
206  [0]=>
207  int(2)
208  ["b"]=>
209  string(13) "test insert 2"
210  [1]=>
211  string(13) "test insert 2"
212  ["c"]=>
213  NULL
214  [2]=>
215  NULL
216}
217Testing pgsqlCopyFromArray() with only selected fields
218bool(true)
219array(6) {
220  ["a"]=>
221  int(0)
222  [0]=>
223  int(0)
224  ["b"]=>
225  NULL
226  [1]=>
227  NULL
228  ["c"]=>
229  NULL
230  [2]=>
231  NULL
232}
233array(6) {
234  ["a"]=>
235  int(1)
236  [0]=>
237  int(1)
238  ["b"]=>
239  NULL
240  [1]=>
241  NULL
242  ["c"]=>
243  NULL
244  [2]=>
245  NULL
246}
247array(6) {
248  ["a"]=>
249  int(2)
250  [0]=>
251  int(2)
252  ["b"]=>
253  NULL
254  [1]=>
255  NULL
256  ["c"]=>
257  NULL
258  [2]=>
259  NULL
260}
261Testing pgsqlCopyFromArray() with error
262Exception: SQLSTATE[42P01]: Undefined table: 7 %s:  %stest_error%s
263Testing pgsqlCopyFromFile() with default parameters
264bool(true)
265array(6) {
266  ["a"]=>
267  int(0)
268  [0]=>
269  int(0)
270  ["b"]=>
271  string(13) "test insert 0"
272  [1]=>
273  string(13) "test insert 0"
274  ["c"]=>
275  NULL
276  [2]=>
277  NULL
278}
279array(6) {
280  ["a"]=>
281  int(1)
282  [0]=>
283  int(1)
284  ["b"]=>
285  string(13) "test insert 1"
286  [1]=>
287  string(13) "test insert 1"
288  ["c"]=>
289  NULL
290  [2]=>
291  NULL
292}
293array(6) {
294  ["a"]=>
295  int(2)
296  [0]=>
297  int(2)
298  ["b"]=>
299  string(13) "test insert 2"
300  [1]=>
301  string(13) "test insert 2"
302  ["c"]=>
303  NULL
304  [2]=>
305  NULL
306}
307Testing pgsqlCopyFromFile() with different field separator and not null indicator
308bool(true)
309array(6) {
310  ["a"]=>
311  int(0)
312  [0]=>
313  int(0)
314  ["b"]=>
315  string(13) "test insert 0"
316  [1]=>
317  string(13) "test insert 0"
318  ["c"]=>
319  NULL
320  [2]=>
321  NULL
322}
323array(6) {
324  ["a"]=>
325  int(1)
326  [0]=>
327  int(1)
328  ["b"]=>
329  string(13) "test insert 1"
330  [1]=>
331  string(13) "test insert 1"
332  ["c"]=>
333  NULL
334  [2]=>
335  NULL
336}
337array(6) {
338  ["a"]=>
339  int(2)
340  [0]=>
341  int(2)
342  ["b"]=>
343  string(13) "test insert 2"
344  [1]=>
345  string(13) "test insert 2"
346  ["c"]=>
347  NULL
348  [2]=>
349  NULL
350}
351Testing pgsqlCopyFromFile() with only selected fields
352bool(true)
353array(6) {
354  ["a"]=>
355  int(0)
356  [0]=>
357  int(0)
358  ["b"]=>
359  NULL
360  [1]=>
361  NULL
362  ["c"]=>
363  NULL
364  [2]=>
365  NULL
366}
367array(6) {
368  ["a"]=>
369  int(1)
370  [0]=>
371  int(1)
372  ["b"]=>
373  NULL
374  [1]=>
375  NULL
376  ["c"]=>
377  NULL
378  [2]=>
379  NULL
380}
381array(6) {
382  ["a"]=>
383  int(2)
384  [0]=>
385  int(2)
386  ["b"]=>
387  NULL
388  [1]=>
389  NULL
390  ["c"]=>
391  NULL
392  [2]=>
393  NULL
394}
395Testing pgsqlCopyFromFile() with error
396Exception: SQLSTATE[42P01]: Undefined table: 7 %s:  %stest_error%s
397Testing pgsqlCopyFromFile() with non existing file
398Exception: SQLSTATE[HY000]: General error: 7 Unable to open the file
399