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