1
<?php
2
/**
3
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
4
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
5
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
6
 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
7
 * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
8
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
9
 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
10
 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
11
 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
12
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
13
 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
14
 *
15
 * This software consists of voluntary contributions made by many individuals
16
 * and is licensed under the LGPL. For more information please see
17
 * <http://phing.info>.
18
 */
19

20
/**
21
 * Executes a series of SQL statements on a database using PDO.
22
 *
23
 * <p>Statements can
24
 * either be read in from a text file using the <i>src</i> attribute or from
25
 * between the enclosing SQL tags.</p>
26
 *
27
 * <p>Multiple statements can be provided, separated by semicolons (or the
28
 * defined <i>delimiter</i>). Individual lines within the statements can be
29
 * commented using either --, // or REM at the start of the line.</p>
30
 *
31
 * <p>The <i>autocommit</i> attribute specifies whether auto-commit should be
32
 * turned on or off whilst executing the statements. If auto-commit is turned
33
 * on each statement will be executed and committed. If it is turned off the
34
 * statements will all be executed as one transaction.</p>
35
 *
36
 * <p>The <i>onerror</i> attribute specifies how to proceed when an error occurs
37
 * during the execution of one of the statements.
38
 * The possible values are: <b>continue</b> execution, only show the error;
39
 * <b>stop</b> execution and commit transaction;
40
 * and <b>abort</b> execution and transaction and fail task.</p>
41
 *
42
 * This task can also be used as a Condition.
43
 *
44
 * @author  Hans Lellelid <hans@xmpl.org> (Phing)
45
 * @author  Jeff Martin <jeff@custommonkey.org> (Ant)
46
 * @author  Michael McCallum <gholam@xtra.co.nz> (Ant)
47
 * @author  Tim Stephenson <tim.stephenson@sybase.com> (Ant)
48
 * @package phing.tasks.ext.pdo
49
 */
50
class PDOSQLExecTask extends PDOTask implements Condition
51
{
52
    /**
53
     * Count of how many statements were executed successfully.
54
     *
55
     * @var int
56
     */
57
    private $goodSql = 0;
58

59
    /**
60
     * Count of total number of SQL statements.
61
     *
62
     * @var int
63
     */
64
    private $totalSql = 0;
65

66
    public const DELIM_ROW = "row";
67
    public const DELIM_NORMAL = "normal";
68
    public const DELIM_NONE = "none";
69

70
    /**
71
     * Database connection
72
     *
73
     * @var PDO
74
     */
75
    private $conn = null;
76

77
    /**
78
     * Files to load
79
     *
80
     * @var FileSet[]
81
     */
82
    private $filesets = [];
83

84
    /**
85
     * Files to load
86
     *
87
     * @var FileList[]
88
     */
89
    private $filelists = [];
90

91
    /**
92
     * Formatter elements.
93
     *
94
     * @var PDOSQLExecFormatterElement[]
95
     */
96
    private $formatters = [];
97

98
    /**
99
     * SQL statement
100
     *
101
     * @var PDOStatement
102
     */
103
    private $statement;
104

105
    /**
106
     * SQL input file
107
     *
108
     * @var PhingFile
109
     */
110
    private $srcFile;
111

112
    /**
113
     * SQL input command
114
     *
115
     * @var string
116
     */
117
    private $sqlCommand = "";
118

119
    /**
120
     * SQL transactions to perform
121
     */
122
    private $transactions = [];
123

124
    /**
125
     * SQL Statement delimiter (for parsing files)
126
     *
127
     * @var string
128
     */
129
    private $delimiter = ";";
130

131
    /**
132
     * The delimiter type indicating whether the delimiter will
133
     * only be recognized on a line by itself
134
     */
135
    private $delimiterType = self::DELIM_NONE;
136

137
    /**
138
     * Action to perform if an error is found
139
     **/
140
    private $onError = "abort";
141

142
    /**
143
     * Encoding to use when reading SQL statements from a file
144
     */
145
    private $encoding = null;
146

147
    /**
148
     * Fetch mode for PDO select queries.
149
     *
150
     * @var int
151
     */
152
    private $fetchMode;
153

154
    /**
155
     * Set the name of the SQL file to be run.
156
     * Required unless statements are enclosed in the build file
157
     *
158
     * @param PhingFile $srcFile
159
     */
160 1
    public function setSrc(PhingFile $srcFile)
161
    {
162 1
        $this->srcFile = $srcFile;
163
    }
164

165
    /**
166
     * Set an inline SQL command to execute.
167
     * NB: Properties are not expanded in this text.
168
     *
169
     * @param $sql
170
     */
171 0
    public function addText($sql)
172
    {
173 0
        $this->sqlCommand .= $sql;
174
    }
175

176
    /**
177
     * Adds a set of files (nested fileset attribute).
178
     *
179
     * @param FileSet $set
180
     */
181 0
    public function addFileset(FileSet $set)
182
    {
183 0
        $this->filesets[] = $set;
184
    }
185

186
    /**
187
     * Adds a set of files (nested filelist attribute).
188
     *
189
     * @param FileList $list
190
     */
191 0
    public function addFilelist(FileList $list)
192
    {
193 0
        $this->filelists[] = $list;
194
    }
195

196
    /**
197
     * Creates a new PDOSQLExecFormatterElement for <formatter> element.
198
     *
199
     * @return PDOSQLExecFormatterElement
200
     */
201 0
    public function createFormatter()
202
    {
203 0
        $fe = new PDOSQLExecFormatterElement($this);
204 0
        $this->formatters[] = $fe;
205

206 0
        return $fe;
207
    }
208

209
    /**
210
     * Add a SQL transaction to execute
211
     */
212 1
    public function createTransaction()
213
    {
214 1
        $t = new PDOSQLExecTransaction($this);
215 1
        $this->transactions[] = $t;
216

217 1
        return $t;
218
    }
219

220
    /**
221
     * Set the file encoding to use on the SQL files read in
222
     *
223
     * @param string $encoding the encoding to use on the files
224
     */
225 0
    public function setEncoding($encoding)
226
    {
227 0
        $this->encoding = $encoding;
228
    }
229

230
    /**
231
     * Set the statement delimiter.
232
     *
233
     * <p>For example, set this to "go" and delimitertype to "ROW" for
234
     * Sybase ASE or MS SQL Server.</p>
235
     *
236
     * @param string $delimiter
237
     */
238 1
    public function setDelimiter(string $delimiter): void
239
    {
240 1
        $this->delimiter = $delimiter;
241
    }
242

243
    /**
244
     * Get the statement delimiter.
245
     *
246
     * @return string
247
     */
248 1
    public function getDelimiter(): string
249
    {
250 1
        return $this->delimiter;
251
    }
252

253
    /**
254
     * Set the Delimiter type for this sql task. The delimiter type takes two
255
     * values - normal and row. Normal means that any occurrence of the delimiter
256
     * terminate the SQL command whereas with row, only a line containing just
257
     * the delimiter is recognized as the end of the command.
258
     *
259
     * @param string $delimiterType
260
     */
261 1
    public function setDelimiterType(string $delimiterType): void
262
    {
263 1
        $this->delimiterType = $delimiterType;
264
    }
265

266
    /**
267
     * Action to perform when statement fails: continue, stop, or abort
268
     * optional; default &quot;abort&quot;
269
     *
270
     * @param string $action continue|stop|abort
271
     */
272 0
    public function setOnerror($action): void
273
    {
274 0
        $this->onError = $action;
275
    }
276

277
    /**
278
     * Sets the fetch mode to use for the PDO resultset.
279
     *
280
     * @param  mixed $mode The PDO fetchmode integer or constant name.
281
     * @throws BuildException
282
     */
283 0
    public function setFetchmode($mode): void
284
    {
285 0
        if (is_numeric($mode)) {
286 0
            $this->fetchMode = (int) $mode;
287
        } else {
288 0
            if (defined($mode)) {
289 0
                $this->fetchMode = constant($mode);
290
            } else {
291 0
                throw new BuildException("Invalid PDO fetch mode specified: " . $mode, $this->getLocation());
292
            }
293
        }
294
    }
295

296
    /**
297
     * Gets a default output writer for this task.
298
     *
299
     * @return Writer
300
     */
301 0
    private function getDefaultOutput()
302
    {
303 0
        return new LogWriter($this);
304
    }
305

306
    /**
307
     * Load the sql file and then execute it.
308
     *
309
     * {@inheritdoc}
310
     *
311
     * @throws BuildException
312
     */
313 1
    public function main()
314
    {
315

316
        // Set a default fetchmode if none was specified
317
        // (We're doing that here to prevent errors loading the class is PDO is not available.)
318 1
        if ($this->fetchMode === null) {
319 1
            $this->fetchMode = PDO::FETCH_ASSOC;
320
        }
321

322
        // Initialize the formatters here.  This ensures that any parameters passed to the formatter
323
        // element get passed along to the actual formatter object
324 1
        foreach ($this->formatters as $fe) {
325 0
            $fe->prepare($this->getLocation());
326
        }
327

328 1
        $savedTransaction = [];
329 1
        for ($i = 0, $size = count($this->transactions); $i < $size; $i++) {
330 0
            $savedTransaction[] = clone $this->transactions[$i];
331
        }
332

333 1
        $savedSqlCommand = $this->sqlCommand;
334

335 1
        $this->sqlCommand = trim($this->sqlCommand);
336

337
        try {
338
            if (
339 1
                $this->srcFile === null
340 0
                && $this->sqlCommand === ""
341 0
                && empty($this->filesets)
342 0
                && empty($this->filelists)
343 0
                && count($this->transactions) === 0
344
            ) {
345 0
                throw new BuildException(
346
                    "Source file or fileset/filelist, "
347
                    . "transactions or sql statement "
348 0
                    . "must be set!",
349 0
                    $this->getLocation()
350
                );
351
            }
352

353 1
            if ($this->srcFile !== null && !$this->srcFile->exists()) {
354 0
                throw new BuildException("Source file does not exist!", $this->getLocation());
355
            }
356

357
            // deal with the filesets
358 1
            foreach ($this->filesets as $fs) {
359 0
                $ds = $fs->getDirectoryScanner($this->project);
360 0
                $srcDir = $fs->getDir($this->project);
361 0
                $srcFiles = $ds->getIncludedFiles();
362
                // Make a transaction for each file
363 0
                foreach ($srcFiles as $srcFile) {
364 0
                    $t = $this->createTransaction();
365 0
                    $t->setSrc(new PhingFile($srcDir, $srcFile));
366
                }
367
            }
368

369
            // process filelists
370 1
            foreach ($this->filelists as $fl) {
371 0
                $srcDir = $fl->getDir($this->project);
372 0
                $srcFiles = $fl->getFiles($this->project);
373
                // Make a transaction for each file
374 0
                foreach ($srcFiles as $srcFile) {
375 0
                    $t = $this->createTransaction();
376 0
                    $t->setSrc(new PhingFile($srcDir, $srcFile));
377
                }
378
            }
379

380
            // Make a transaction group for the outer command
381 1
            $t = $this->createTransaction();
382 1
            if ($this->srcFile) {
383 1
                $t->setSrc($this->srcFile);
384
            }
385 1
            $t->addText($this->sqlCommand);
386 1
            $this->conn = $this->getConnection();
387

388
            try {
389 1
                $this->statement = null;
390

391
                // Initialize the formatters.
392 1
                $this->initFormatters();
393

394
                try {
395
                    // Process all transactions
396 1
                    for ($i = 0, $size = count($this->transactions); $i < $size; $i++) {
397 1
                        if (!$this->isAutocommit()) {
398 0
                            $this->log("Beginning transaction", Project::MSG_VERBOSE);
399 0
                            $this->conn->beginTransaction();
400
                        }
401 1
                        $this->transactions[$i]->runTransaction();
402 1
                        if (!$this->isAutocommit()) {
403 0
                            $this->log("Committing transaction", Project::MSG_VERBOSE);
404 0
                            $this->conn->commit();
405
                        }
406
                    }
407 0
                } catch (Exception $e) {
408 0
                    $this->closeConnection();
409 0
                    throw $e;
410
                }
411 0
            } catch (IOException $e) {
412 0
                if (!$this->isAutocommit() && $this->conn !== null && $this->onError == "abort") {
413
                    try {
414 0
                        $this->conn->rollback();
415 0
                    } catch (PDOException $ex) {
416
                    }
417
                }
418 0
                $this->closeConnection();
419 0
                throw new BuildException($e->getMessage(), $this->getLocation());
420 0
            } catch (PDOException $e) {
421 0
                if (!$this->isAutocommit() && $this->conn !== null && $this->onError == "abort") {
422
                    try {
423 0
                        $this->conn->rollback();
424 0
                    } catch (PDOException $ex) {
425
                    }
426
                }
427 0
                $this->closeConnection();
428 0
                throw new BuildException($e->getMessage(), $this->getLocation());
429
            }
430

431
            // Close the formatters.
432 1
            $this->closeFormatters();
433

434 1
            $this->log(
435 1
                $this->goodSql . " of " . $this->totalSql .
436 1
                " SQL statements executed successfully"
437
            );
438 0
        } catch (Exception $e) {
439 0
            throw $e;
440 1
        } finally {
441 1
            $this->transactions = $savedTransaction;
442 1
            $this->sqlCommand = $savedSqlCommand;
443 1
            $this->closeConnection();
444
        }
445
    }
446

447
    /**
448
     * read in lines and execute them
449
     *
450
     * @param  Reader $reader
451
     * @throws BuildException
452
     */
453 1
    public function runStatements(Reader $reader)
454
    {
455 1
        if (self::DELIM_NONE == $this->delimiterType) {
456 0
            $splitter = new DummyPDOQuerySplitter($this, $reader);
457 1
        } elseif (self::DELIM_NORMAL == $this->delimiterType && 0 === strpos($this->getUrl(), 'pgsql:')) {
458 1
            $splitter = new PgsqlPDOQuerySplitter($this, $reader);
459
        } else {
460 1
            $splitter = new DefaultPDOQuerySplitter($this, $reader, $this->delimiterType);
461
        }
462

463
        try {
464 1
            while (null !== ($query = $splitter->nextQuery())) {
465 1
                $this->log("SQL: " . $query, Project::MSG_VERBOSE);
466 1
                $this->execSQL($query);
467
            }
468 0
        } catch (PDOException $e) {
469 0
            throw $e;
470
        }
471
    }
472

473
    /**
474
     * Whether the passed-in SQL statement is a SELECT statement.
475
     * This does a pretty simple match, checking to see if statement starts with
476
     * 'select' (but not 'select into').
477
     *
478
     * @param string $sql
479
     *
480
     * @return boolean Whether specified SQL looks like a SELECT query.
481
     */
482 0
    protected function isSelectSql($sql)
483
    {
484 0
        $sql = trim($sql);
485

486 0
        return (stripos($sql, 'select') === 0 && stripos($sql, 'select into ') !== 0);
487
    }
488

489
    /**
490
     * Exec the sql statement.
491
     *
492
     * @param $sql
493
     *
494
     * @throws BuildException
495
     * @throws Exception
496
     */
497 0
    protected function execSQL($sql)
498
    {
499
        // Check and ignore empty statements
500 0
        if (trim($sql) == "") {
501 0
            return;
502
        }
503

504
        try {
505 0
            $this->totalSql++;
506

507 0
            $this->statement = $this->conn->query($sql);
508 0
            $this->log($this->statement->rowCount() . " rows affected", Project::MSG_VERBOSE);
509

510
            // only call processResults() for statements that return actual data (such as 'select')
511 0
            if ($this->statement->columnCount() > 0) {
512 0
                $this->processResults();
513
            }
514

515 0
            $this->statement->closeCursor();
516 0
            $this->statement = null;
517

518 0
            $this->goodSql++;
519 0
        } catch (PDOException $e) {
520 0
            $this->log("Failed to execute: " . $sql, Project::MSG_ERR);
521 0
            if ($this->onError != "continue") {
522 0
                throw new BuildException("Failed to execute SQL", $e);
523
            }
524 0
            $this->log($e->getMessage(), Project::MSG_ERR);
525
        }
526
    }
527

528
    /**
529
     * Returns configured PDOResultFormatter objects
530
     * (which were created from PDOSQLExecFormatterElement objects).
531
     *
532
     * @return array PDOResultFormatter[]
533
     */
534 1
    protected function getConfiguredFormatters()
535
    {
536 1
        $formatters = [];
537 1
        foreach ($this->formatters as $fe) {
538 0
            $formatters[] = $fe->getFormatter();
539
        }
540

541 1
        return $formatters;
542
    }
543

544
    /**
545
     * Initialize the formatters.
546
     */
547 1
    protected function initFormatters()
548
    {
549 1
        $formatters = $this->getConfiguredFormatters();
550 1
        foreach ($formatters as $formatter) {
551 0
            $formatter->initialize();
552
        }
553
    }
554

555
    /**
556
     * Run cleanup and close formatters.
557
     */
558 1
    protected function closeFormatters()
559
    {
560 1
        $formatters = $this->getConfiguredFormatters();
561 1
        foreach ($formatters as $formatter) {
562 0
            $formatter->close();
563
        }
564
    }
565

566
    /**
567
     * Passes results from query to any formatters.
568
     *
569
     * @throws PDOException
570
     */
571 0
    protected function processResults()
572
    {
573
        try {
574 0
            $this->log("Processing new result set.", Project::MSG_VERBOSE);
575

576 0
            $formatters = $this->getConfiguredFormatters();
577

578 0
            while ($row = $this->statement->fetch($this->fetchMode)) {
579 0
                foreach ($formatters as $formatter) {
580 0
                    $formatter->processRow($row);
581
                }
582
            }
583 0
        } catch (Exception $x) {
584 0
            $this->log("Error processing reults: " . $x->getMessage(), Project::MSG_ERR);
585 0
            foreach ($formatters as $formatter) {
586 0
                $formatter->close();
587
            }
588 0
            throw $x;
589
        }
590
    }
591

592
    /**
593
     * Closes current connection
594
     */
595 1
    protected function closeConnection(): void
596
    {
597 1
        if ($this->conn) {
598 0
            unset($this->conn);
599 0
            $this->conn = null;
600
        }
601
    }
602

603
    /**
604
     * PDOSQLExecTask as condition
605
     *
606
     * Returns false when the database connection fails, and true otherwise.
607
     * This method only uses three properties: url (required), userId and
608
     * password.
609
     *
610
     * The database connection is not stored in a variable, this allow to
611
     * immediately close the connections since there's no reference to it.
612
     *
613
     * @author Jawira Portugal <dev@tugal.be>
614
     *
615
     * @return bool
616
     */
617 1
    public function evaluate(): bool
618
    {
619 1
        if (empty($this->getUrl())) {
620 1
            throw new BuildException('url is required');
621
        }
622

623 1
        $this->log('Trying to reach ' . $this->getUrl(), Project::MSG_DEBUG);
624

625
        try {
626 1
            new PDO($this->getUrl(), $this->getUserId(), $this->getPassword());
627 1
        } catch (PDOException $ex) {
628 1
            $this->log($ex->getMessage(), Project::MSG_VERBOSE);
629

630 1
            return false;
631
        }
632

633 1
        $this->log('Successful connection to ' . $this->getUrl(), Project::MSG_DEBUG);
634

635 1
        return true;
636
    }
637
}

Read our documentation on viewing source code .

Loading