xref: /php-src/ext/pdo_pgsql/tests/copy_from.phpt (revision 4f84b159) 
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_copy_from (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_copy_from',$tableRows));
44
45$stmt = $db->query("select * from test_copy_from");
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_copy_from',$tableRowsWithDifferentNullValues,";","NULL"));
54$stmt = $db->query("select * from test_copy_from");
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_copy_from',$tableRowsWithDifferentNullValuesAndSelectedFields,";","NULL",'a,c'));
63$stmt = $db->query("select * from test_copy_from");
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_copy_from',$filename));
81
82$stmt = $db->query("select * from test_copy_from");
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_copy_from',$filenameWithDifferentNullValues,";","NULL"));
91$stmt = $db->query("select * from test_copy_from");
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_copy_from',$filenameWithDifferentNullValuesAndSelectedFields,";","NULL",'a,c'));
100$stmt = $db->query("select * from test_copy_from");
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_copy_from',"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--CLEAN--
130<?php
131require __DIR__ . '/../../../ext/pdo/tests/pdo_test.inc';
132$db = PDOTest::test_factory(__DIR__ . '/common.phpt');
133$db->query('DROP TABLE IF EXISTS test_copy_from CASCADE');
134?>
135--EXPECTF--
136Preparing test file and array for CopyFrom tests
137Testing pgsqlCopyFromArray() with default parameters
138bool(true)
139array(6) {
140  ["a"]=>
141  int(0)
142  [0]=>
143  int(0)
144  ["b"]=>
145  string(13) "test insert 0"
146  [1]=>
147  string(13) "test insert 0"
148  ["c"]=>
149  NULL
150  [2]=>
151  NULL
152}
153array(6) {
154  ["a"]=>
155  int(1)
156  [0]=>
157  int(1)
158  ["b"]=>
159  string(13) "test insert 1"
160  [1]=>
161  string(13) "test insert 1"
162  ["c"]=>
163  NULL
164  [2]=>
165  NULL
166}
167array(6) {
168  ["a"]=>
169  int(2)
170  [0]=>
171  int(2)
172  ["b"]=>
173  string(13) "test insert 2"
174  [1]=>
175  string(13) "test insert 2"
176  ["c"]=>
177  NULL
178  [2]=>
179  NULL
180}
181Testing pgsqlCopyFromArray() with different field separator and not null indicator
182bool(true)
183array(6) {
184  ["a"]=>
185  int(0)
186  [0]=>
187  int(0)
188  ["b"]=>
189  string(13) "test insert 0"
190  [1]=>
191  string(13) "test insert 0"
192  ["c"]=>
193  NULL
194  [2]=>
195  NULL
196}
197array(6) {
198  ["a"]=>
199  int(1)
200  [0]=>
201  int(1)
202  ["b"]=>
203  string(13) "test insert 1"
204  [1]=>
205  string(13) "test insert 1"
206  ["c"]=>
207  NULL
208  [2]=>
209  NULL
210}
211array(6) {
212  ["a"]=>
213  int(2)
214  [0]=>
215  int(2)
216  ["b"]=>
217  string(13) "test insert 2"
218  [1]=>
219  string(13) "test insert 2"
220  ["c"]=>
221  NULL
222  [2]=>
223  NULL
224}
225Testing pgsqlCopyFromArray() with only selected fields
226bool(true)
227array(6) {
228  ["a"]=>
229  int(0)
230  [0]=>
231  int(0)
232  ["b"]=>
233  NULL
234  [1]=>
235  NULL
236  ["c"]=>
237  NULL
238  [2]=>
239  NULL
240}
241array(6) {
242  ["a"]=>
243  int(1)
244  [0]=>
245  int(1)
246  ["b"]=>
247  NULL
248  [1]=>
249  NULL
250  ["c"]=>
251  NULL
252  [2]=>
253  NULL
254}
255array(6) {
256  ["a"]=>
257  int(2)
258  [0]=>
259  int(2)
260  ["b"]=>
261  NULL
262  [1]=>
263  NULL
264  ["c"]=>
265  NULL
266  [2]=>
267  NULL
268}
269Testing pgsqlCopyFromArray() with error
270Exception: SQLSTATE[42P01]: Undefined table: 7 %s:  %stest_error%s
271Testing pgsqlCopyFromFile() with default parameters
272bool(true)
273array(6) {
274  ["a"]=>
275  int(0)
276  [0]=>
277  int(0)
278  ["b"]=>
279  string(13) "test insert 0"
280  [1]=>
281  string(13) "test insert 0"
282  ["c"]=>
283  NULL
284  [2]=>
285  NULL
286}
287array(6) {
288  ["a"]=>
289  int(1)
290  [0]=>
291  int(1)
292  ["b"]=>
293  string(13) "test insert 1"
294  [1]=>
295  string(13) "test insert 1"
296  ["c"]=>
297  NULL
298  [2]=>
299  NULL
300}
301array(6) {
302  ["a"]=>
303  int(2)
304  [0]=>
305  int(2)
306  ["b"]=>
307  string(13) "test insert 2"
308  [1]=>
309  string(13) "test insert 2"
310  ["c"]=>
311  NULL
312  [2]=>
313  NULL
314}
315Testing pgsqlCopyFromFile() with different field separator and not null indicator
316bool(true)
317array(6) {
318  ["a"]=>
319  int(0)
320  [0]=>
321  int(0)
322  ["b"]=>
323  string(13) "test insert 0"
324  [1]=>
325  string(13) "test insert 0"
326  ["c"]=>
327  NULL
328  [2]=>
329  NULL
330}
331array(6) {
332  ["a"]=>
333  int(1)
334  [0]=>
335  int(1)
336  ["b"]=>
337  string(13) "test insert 1"
338  [1]=>
339  string(13) "test insert 1"
340  ["c"]=>
341  NULL
342  [2]=>
343  NULL
344}
345array(6) {
346  ["a"]=>
347  int(2)
348  [0]=>
349  int(2)
350  ["b"]=>
351  string(13) "test insert 2"
352  [1]=>
353  string(13) "test insert 2"
354  ["c"]=>
355  NULL
356  [2]=>
357  NULL
358}
359Testing pgsqlCopyFromFile() with only selected fields
360bool(true)
361array(6) {
362  ["a"]=>
363  int(0)
364  [0]=>
365  int(0)
366  ["b"]=>
367  NULL
368  [1]=>
369  NULL
370  ["c"]=>
371  NULL
372  [2]=>
373  NULL
374}
375array(6) {
376  ["a"]=>
377  int(1)
378  [0]=>
379  int(1)
380  ["b"]=>
381  NULL
382  [1]=>
383  NULL
384  ["c"]=>
385  NULL
386  [2]=>
387  NULL
388}
389array(6) {
390  ["a"]=>
391  int(2)
392  [0]=>
393  int(2)
394  ["b"]=>
395  NULL
396  [1]=>
397  NULL
398  ["c"]=>
399  NULL
400  [2]=>
401  NULL
402}
403Testing pgsqlCopyFromFile() with error
404Exception: SQLSTATE[42P01]: Undefined table: 7 %s:  %stest_error%s
405Testing pgsqlCopyFromFile() with non existing file
406Exception: SQLSTATE[HY000]: General error: 7 Unable to open the file
407