Navigation

← Previous Changeset
Next Changeset →

Changeset 256

Timestamp:

10/20/07 22:29:48 (9 months ago)

Author:

hans

Message:

#153 and #154 -- Adding some changes from Albert to the PDOSQLExecTask. (I'm actually not sure if this fixes #153, but it's intended to ...)

Files:

branches/2.3/classes/phing/tasks/ext/PDOSQLExecTask.php (modified) (19 diffs)

Legend:

: Unmodified
: Added
: Removed
: Modified
: Copied
: Moved

branches/2.3/classes/phing/tasks/ext/PDOSQLExecTask.php

r248	r256
54	54	class PDOSQLExecTask extends PDOTask {
55	55
56		private $goodSql = 0;
57		private $totalSql = 0;
58
59		const DELIM_ROW = "row";
60		const DELIM_NORMAL = "normal";
	56	private $goodSql = 0;
	57	private $totalSql = 0;
	58
	59	const DELIM_ROW = "row";
	60	const DELIM_NORMAL = "normal";
61	61
62	62	/**
…	…
94	94	*/
95	95	private $delimiter = ";";
96
	96
97	97	/**
98	98	* The delimiter type indicating whether the delimiter will
…	…
100	100	*/
101	101	private $delimiterType = "normal"; // can't use constant just defined
102
	102
103	103	/**
104	104	* Print SQL results.
…	…
116	116	private $output = null;
117	117
118
	118
119	119	/**
120	120	* Action to perform if an error is found
121	121	**/
122	122	private $onError = "abort";
123
	123
124	124	/**
125	125	* Encoding to use when reading SQL statements from a file
…	…
131	131	*/
132	132	private $append = false;
133
	133
	134	/**
	135	* Fetch mode for PDO select queries.
	136	* @var int
	137	*/
	138	private $fetchMode;
	139
134	140	/**
135	141	* Set the name of the SQL file to be run.
136	142	* Required unless statements are enclosed in the build file
137	143	*/
138		public function setSrc(PhingFile $srcFile) {
139		$this->srcFile = $srcFile;
140		}
141
	144	public function setSrc(PhingFile $srcFile) {
	145	$this->srcFile = $srcFile;
	146	}
	147
142	148	/**
143	149	* Set an inline SQL command to execute.
…	…
145	151	*/
146	152	public function addText($sql) {
147		$this->sqlCommand .= $sql;
148		}
149
	153	$this->sqlCommand .= $sql;
	154	}
	155
150	156	/**
151	157	* Adds a set of files (nested fileset attribute).
152	158	*/
153	159	public function addFileset(FileSet $set) {
154		$this->filesets[] = $set;
	160	$this->filesets[] = $set;
155	161	}
156	162
…	…
159	165	*/
160	166	public function createTransaction() {
161		$t = new PDOSQLExecTransaction($this);
162		$this->transactions[] = $t;
163		return $t;
164		}
165
	167	$t = new PDOSQLExecTransaction($this);
	168	$this->transactions[] = $t;
	169	return $t;
	170	}
	171
166	172	/**
167	173	* Set the file encoding to use on the SQL files read in
…	…
170	176	*/
171	177	public function setEncoding($encoding) {
172		$this->encoding = $encoding;
173		}
174
	178	$this->encoding = $encoding;
	179	}
	180
175	181	/**
176	182	* Set the statement delimiter.
…	…
183	189	public function setDelimiter($delimiter)
184	190	{
185		$this->delimiter = $delimiter;
	191	$this->delimiter = $delimiter;
186	192	}
187	193
…	…
196	202	public function setDelimiterType($delimiterType)
197	203	{
198		$this->delimiterType = $delimiterType;
199		}
200
	204	$this->delimiterType = $delimiterType;
	205	}
	206
201	207	/**
202	208	* Set the print flag.
…	…
206	212	public function setPrint($print)
207	213	{
208		$this->print = (boolean) $print;
209		}
210
	214	$this->print = (boolean) $print;
	215	}
	216
211	217	/**
212	218	* Print headers for result sets from the
…	…
215	221	*/
216	222	public function setShowheaders($showheaders) {
217		$this->showheaders = (boolean) $showheaders;
	223	$this->showheaders = (boolean) $showheaders;
218	224	}
219	225
…	…
224	230	*/
225	231	public function setOutput(PhingFile $output) {
226		$this->output = $output;
	232	$this->output = $output;
227	233	}
228	234
…	…
233	239	*/
234	240	public function setAppend($append) {
235		$this->append = (boolean) $append;
236		}
237
238
	241	$this->append = (boolean) $append;
	242	}
	243
	244
239	245	/**
240	246	* Action to perform when statement fails: continue, stop, or abort
…	…
242	248	*/
243	249	public function setOnerror($action) {
244		$this->onError = $action;
	250	$this->onError = $action;
	251	}
	252
	253	/**
	254	* Sets the fetch mode to use for the PDO resultset.
	255	* @param mixed $mode The PDO fetchmode integer or constant name.
	256	*/
	257	public function setFetchmode($mode) {
	258	if (is_numeric($mode)) {
	259	$this->fetchMode = (int) $mode;
	260	} else {
	261	if (defined($mode)) {
	262	$this->fetchMode = constant($mode);
	263	} else {
	264	throw new BuildException("Invalid PDO fetch mode specified: " . $mode, $this->getLocation());
	265	}
	266	}
245	267	}
246	268
…	…
250	272	*/
251	273	public function main() {
252
253		$savedTransaction = array();
254		for($i=0,$size=count($this->transactions); $i < $size; $i++) {
255		$savedTransaction[] = clone $this->transactions[$i];
256		}
257
258		$savedSqlCommand = $this->sqlCommand;
259
260		$this->sqlCommand = trim($this->sqlCommand);
261
262		try {
263		if ($this->srcFile === null && $this->sqlCommand === ""
264		&& empty($this->filesets)) {
265		if (count($this->transactions) === 0) {
266		throw new BuildException("Source file or fileset, "
267		. "transactions or sql statement "
268		. "must be set!", $this->location);
269		}
270		}
271
272		if ($this->srcFile !== null && !$this->srcFile->exists()) {
273		throw new BuildException("Source file does not exist!", $this->location);
274		}
275
276		// deal with the filesets
277		foreach($this->filesets as $fs) {
278		$ds = $fs->getDirectoryScanner($this->project);
279		$srcDir = $fs->getDir($this->project);
280		$srcFiles = $ds->getIncludedFiles();
281		// Make a transaction for each file
282		foreach($srcFiles as $srcFile) {
283		$t = $this->createTransaction();
284		$t->setSrc(new PhingFile($srcDir, $srcFile));
285		}
286		}
287
288		// Make a transaction group for the outer command
289		$t = $this->createTransaction();
290		if ($this->srcFile) $t->setSrc($this->srcFile);
291		$t->addText($this->sqlCommand);
292		$this->conn = $this->getConnection();
293
294		try {
295
296		$this->statement = null;
297
298		$out = null;
299
300		try {
301
302		if ($this->output !== null) {
303		$this->log("Opening output file " . $this->output, Project::MSG_VERBOSE);
304		$out = new BufferedWriter(new FileWriter($this->output->getAbsolutePath(), $this->append));
305		}
306
307		// Process all transactions
308		for ($i=0,$size=count($this->transactions); $i < $size; $i++) {
309		if (!$this->isAutocommit()) {
310		$this->log("Beginning transaction", Project::MSG_VERBOSE);
311		$this->conn->beginTransaction();
312		}
313		$this->transactions[$i]->runTransaction($out);
314		if (!$this->isAutocommit()) {
315		$this->log("Commiting transaction", Project::MSG_VERBOSE);
316		$this->conn->commit();
317		}
318		}
319		if ($out) $out->close();
320		} catch (Exception $e) {
321		if ($out) $out->close();
322		throw $e;
323		}
324		} catch (IOException $e) {
325		if (!$this->isAutocommit() && $this->conn !== null && $this->onError == "abort") {
326		try {
327		$this->conn->rollback();
328		} catch (SQLException $ex) {}
329		}
330		throw new BuildException($e->getMessage(), $this->location);
331		} catch (SQLException $e){
332		if (!$this->isAutocommit() && $this->conn !== null && $this->onError == "abort") {
333		try {
334		$this->conn->rollback();
335		} catch (SQLException $ex) {}
336		}
337		throw new BuildException($e->getMessage(), $this->location);
338		}
339
340		$this->log($this->goodSql . " of " . $this->totalSql .
	274
	275	// Set a default fetchmode if none was specified
	276	// (We're doing that here to prevent errors loading the class is PDO is not available.)
	277	if ($this->fetchMode === null) {
	278	$this->fetchMode = PDO::FETCH_BOTH;
	279	}
	280
	281	$savedTransaction = array();
	282	for($i=0,$size=count($this->transactions); $i < $size; $i++) {
	283	$savedTransaction[] = clone $this->transactions[$i];
	284	}
	285
	286	$savedSqlCommand = $this->sqlCommand;
	287
	288	$this->sqlCommand = trim($this->sqlCommand);
	289
	290	try {
	291	if ($this->srcFile === null && $this->sqlCommand === ""
	292	&& empty($this->filesets)) {
	293	if (count($this->transactions) === 0) {
	294	throw new BuildException("Source file or fileset, "
	295	. "transactions or sql statement "
	296	. "must be set!", $this->location);
	297	}
	298	}
	299
	300	if ($this->srcFile !== null && !$this->srcFile->exists()) {
	301	throw new BuildException("Source file does not exist!", $this->location);
	302	}
	303
	304	// deal with the filesets
	305	foreach($this->filesets as $fs) {
	306	$ds = $fs->getDirectoryScanner($this->project);
	307	$srcDir = $fs->getDir($this->project);
	308	$srcFiles = $ds->getIncludedFiles();
	309	// Make a transaction for each file
	310	foreach($srcFiles as $srcFile) {
	311	$t = $this->createTransaction();
	312	$t->setSrc(new PhingFile($srcDir, $srcFile));
	313	}
	314	}
	315
	316	// Make a transaction group for the outer command
	317	$t = $this->createTransaction();
	318	if ($this->srcFile) $t->setSrc($this->srcFile);
	319	$t->addText($this->sqlCommand);
	320	$this->conn = $this->getConnection();
	321
	322	try {
	323
	324	$this->statement = null;
	325
	326	$out = null;
	327
	328	try {
	329
	330	if ($this->output !== null) {
	331	$this->log("Opening output file " . $this->output, Project::MSG_VERBOSE);
	332	$out = new BufferedWriter(new FileWriter($this->output->getAbsolutePath(), $this->append));
	333	}
	334
	335	// Process all transactions
	336	for ($i=0,$size=count($this->transactions); $i < $size; $i++) {
	337	if (!$this->isAutocommit()) {
	338	$this->log("Beginning transaction", Project::MSG_VERBOSE);
	339	$this->conn->beginTransaction();
	340	}
	341	$this->transactions[$i]->runTransaction($out);
	342	if (!$this->isAutocommit()) {
	343	$this->log("Commiting transaction", Project::MSG_VERBOSE);
	344	$this->conn->commit();
	345	}
	346	}
	347	if ($out) $out->close();
	348	} catch (Exception $e) {
	349	if ($out) $out->close();
	350	throw $e;
	351	}
	352	} catch (IOException $e) {
	353	if (!$this->isAutocommit() && $this->conn !== null && $this->onError == "abort") {
	354	try {
	355	$this->conn->rollback();
	356	} catch (SQLException $ex) {}
	357	}
	358	throw new BuildException($e->getMessage(), $this->location);
	359	} catch (SQLException $e){
	360	if (!$this->isAutocommit() && $this->conn !== null && $this->onError == "abort") {
	361	try {
	362	$this->conn->rollback();
	363	} catch (SQLException $ex) {}
	364	}
	365	throw new BuildException($e->getMessage(), $this->location);
	366	}
	367
	368	$this->log($this->goodSql . " of " . $this->totalSql .
341	369	" SQL statements executed successfully");
342		} catch (Exception $e) {
343		$this->transactions = $savedTransaction;
344		$this->sqlCommand = $savedSqlCommand;
345		throw $e;
346		}
347		// finally {
348		$this->transactions = $savedTransaction;
349		$this->sqlCommand = $savedSqlCommand;
350
	370	} catch (Exception $e) {
	371	$this->transactions = $savedTransaction;
	372	$this->sqlCommand = $savedSqlCommand;
	373	throw $e;
	374	}
	375	// finally {
	376	$this->transactions = $savedTransaction;
	377	$this->sqlCommand = $savedSqlCommand;
	378
351	379	}
352	380
…	…
356	384	* @throws SQLException, IOException
357	385	*/
358		public function runStatements(Reader $reader, $out = null) {
359		$sql = "";
360		$line = "";
361		$in = new BufferedReader($reader);
362		try {
363		while (($line = $in->readLine()) !== null) {
364		$line = trim($line);
365		$line = ProjectConfigurator::replaceProperties($this->project, $line,
366		$this->project->getProperties());
367
368		if (StringHelper::startsWith("//", $line) \|\|
369		StringHelper::startsWith("--", $line) \|\|
370		StringHelper::startsWith("#", $line)) {
371		continue;
372		}
373
374		if (strlen($line) > 4
375		&& strtoupper(substr($line,0, 4)) == "REM ") {
376		continue;
377		}
378
379		$sql .= " " . $line;
380		$sql = trim($sql);
381
382		// SQL defines "--" as a comment to EOL
383		// and in Oracle it may contain a hint
384		// so we cannot just remove it, instead we must end it
385		if (strpos($line, "--") !== false) {
386		$sql .= "\n";
387		}
388
389		if ($this->delimiterType == self::DELIM_NORMAL
390		&& StringHelper::endsWith($this->delimiter, $sql)
391		\|\| $this->delimiterType == self::DELIM_ROW
392		&& $line == $this->delimiter) {
393		$this->log("SQL: " . $sql, Project::MSG_VERBOSE);
394		$this->execSQL(StringHelper::substring($sql, 0, strlen($sql) - strlen($this->delimiter) - 1), $out);
395		$sql = "";
396		}
397		}
398
399		// Catch any statements not followed by ;
400		if ($sql !== "") {
401		$this->execSQL($sql, $out);
402		}
403		} catch (SQLException $e) {
404		throw new BuildException("Error running statements", $e);
405		}
406		}
407
408
	386	public function runStatements(Reader $reader, Writer $out = null) {
	387	$sql = "";
	388	$line = "";
	389	$in = new BufferedReader($reader);
	390	try {
	391	while (($line = $in->readLine()) !== null) {
	392	$line = trim($line);
	393	$line = ProjectConfigurator::replaceProperties($this->project, $line,
	394	$this->project->getProperties());
	395
	396	if (StringHelper::startsWith("//", $line) \|\|
	397	StringHelper::startsWith("--", $line) \|\|
	398	StringHelper::startsWith("#", $line)) {
	399	continue;
	400	}
	401
	402	if (strlen($line) > 4
	403	&& strtoupper(substr($line,0, 4)) == "REM ") {
	404	continue;
	405	}
	406
	407	$sql .= " " . $line;
	408	$sql = trim($sql);
	409
	410	// SQL defines "--" as a comment to EOL
	411	// and in Oracle it may contain a hint
	412	// so we cannot just remove it, instead we must end it
	413	if (strpos($line, "--") !== false) {
	414	$sql .= "\n";
	415	}
	416
	417	if ($this->delimiterType == self::DELIM_NORMAL
	418	&& StringHelper::endsWith($this->delimiter, $sql)
	419	\|\| $this->delimiterType == self::DELIM_ROW
	420	&& $line == $this->delimiter) {
	421	$this->log("SQL: " . $sql, Project::MSG_VERBOSE);
	422	$this->execSQL(StringHelper::substring($sql, 0, strlen($sql) - strlen($this->delimiter) - 1), $out);
	423	$sql = "";
	424	}
	425	}
	426
	427	// Catch any statements not followed by ;
	428	if ($sql !== "") {
	429	$this->execSQL($sql, $out);
	430	}
	431	} catch (SQLException $e) {
	432	throw new BuildException("Error running statements", $e);
	433	}
	434	}
	435
	436	/**
	437	* Whether the passed-in SQL statement is a SELECT statement.
	438	* This does a pretty simple match, checking to see if statement starts with
	439	* 'select' (but not 'select into').
	440	*
	441	* @param string $sql
	442	* @return boolean Whether specified SQL looks like a SELECT query.
	443	*/
	444	protected function isSelectSql($sql)
	445	{
	446	$sql = trim($sql);
	447	return (stripos($sql, 'select') === 0 && stripos($sql, 'select into ') !== 0);
	448	}
	449
409	450	/**
410	451	* Exec the sql statement.
411	452	* @throws SQLException
412	453	*/
413		protected function execSQL($sql, $out = null) {
414		// Check and ignore empty statements
415		if (trim($sql) == "") {
416		return;
417		}
418
419		try {
420		$this->totalSql++;
421		# FIXME - currently, this only works for update statements
422		#if (!$this->statement->execute($sql)) {
423		$this->statement = $this->conn->prepare($sql);
424		$this->statement->execute();
425		$this->log($this->statement->rowCount() . " rows affected", Project::MSG_VERBOSE);
426		#} else {
427		# if ($this->print) {
428		# $this->printResults($out);
429		# }
430		#}
431
432		$this->goodSql++;
433
434		} catch (SQLException $e) {
435		$this->log("Failed to execute: " . $sql, Project::MSG_ERR);
436		if ($this->onError != "continue") {
437		throw new BuildException("Failed to execute SQL", $e);
438		}
439		$this->log($e->getMessage(), Project::MSG_ERR);
440		}
441		}
442
	454	protected function execSQL($sql, Writer $out = null) {
	455	// Check and ignore empty statements
	456	if (trim($sql) == "") {
	457	return;
	458	}
	459
	460	try {
	461	$this->totalSql++;
	462
	463	# FIXME - currently, this only works for update statements
	464	$this->statement = $this->conn->prepare($sql);
	465	$this->statement->execute();
	466	$this->log($this->statement->rowCount() . " rows affected", Project::MSG_VERBOSE);
	467
	468	if ($this->isSelectSql($sql) && $this->print) {
	469	$this->printResults($out);
	470	} else {
	471	$this->statement->closeCursor();
	472	}
	473
	474	$this->goodSql++;
	475
	476	} catch (SQLException $e) {
	477	$this->log("Failed to execute: " . $sql, Project::MSG_ERR);
	478	if ($this->onError != "continue") {
	479	throw new BuildException("Failed to execute SQL", $e);
	480	}
	481	$this->log($e->getMessage(), Project::MSG_ERR);
	482	}
	483	}
	484
443	485	/**
444	486	* print any results in the statement.
445	487	* @throw SQLException
446	488	*/
447		protected function printResults($out = null) {
448
449		$rs = null;
450
451		if ($rs !== null) {
452
453		$this->log("Processing new result set.", Project::MSG_VERBOSE);
454
455		$line = "";
456
457		$colsprinted = false;
458
459		while ($row = $this->statement->fetch()) {
460
461		if (!$colsprinted && $this->showheaders) {
462		$first = true;
463		foreach($row as $fieldName => $ignore) {
464		if ($first) $first = false; else $line .= ",";
465		$line .= $fieldName;
466		}
467		if ($out !== null) {
468		$out->write($line);
469		$out->newLine();
470		} else {
471		print($line.PHP_EOL);
472		}
473		$line = "";
474		$colsprinted = true;
475		} // if show headers
476
477		$first = true;
478		foreach($row as $columnValue) {
479
480		if ($columnValue != null) {
481		$columnValue = trim($columnValue);
482		}
483
484		if ($first) {
485		$first = false;
486		} else {
487		$line .= ",";
488		}
489		$line .= $columnValue;
490		}
491
492		if ($out !== null) {
493		$out->write($line);
494		$out->newLine();
495		} else {
496		print($line . PHP_EOL);
497		}
498		$line = "";
499
500		} // while rs->next()
501		}
502
503		print(PHP_EOL);
504		if ($out !== null) $out->newLine();
	489	protected function printResults(Writer $out = null) {
	490
	491	$this->log("Processing new result set.", Project::MSG_VERBOSE);
	492
	493	$line = "";
	494
	495	$colsprinted = false;
	496
	497	while ($row = $this->statement->fetch($this->fetchMode)) {
	498
	499	if (!$colsprinted && $this->showheaders) {
	500	$first = true;
	501	foreach($row as $fieldName => $ignore) {
	502	if ($first) $first = false; else $line .= ",";
	503	$line .= $fieldName;
	504	}
	505	if ($out !== null) {
	506	$out->write($line);
	507	$out->newLine();
	508	} else {
	509	print($line.PHP_EOL);
	510	}
	511	$line = "";
	512	$colsprinted = true;
	513	} // if show headers
	514
	515	$first = true;
	516	foreach($row as $columnValue) {
	517
	518	if ($columnValue != null) {
	519	$columnValue = trim($columnValue);
	520	}
	521
	522	if ($first) {
	523	$first = false;
	524	} else {
	525	$line .= ",";
	526	}
	527	$line .= $columnValue;
	528	}
	529
	530	if ($out !== null) {
	531	$out->write($line);
	532	$out->newLine();
	533	} else {
	534	print($line . PHP_EOL);
	535	}
	536	$line = "";
	537
	538	}
	539
	540	// Addresses some issues w/ PDO
	541	// See: http://verens.com/archives/2006/10/19/pdosqlite-gotcha/
	542	$this->statement = null;
	543
	544	if ($out !== null) {
	545	$out->newLine();
	546	} else {
	547	print(PHP_EOL);
	548	}
505	549	}
506	550	}
…	…
515	559	class PDOSQLExecTransaction {
516	560
517		private $tSrcFile = null;
518		private $tSqlCommand = "";
519		private $parent;
520
521		function __construct($parent)
522		{
523		// Parent is required so that we can log things ...
524		$this->parent = $parent;
525		}
526
527		public function setSrc(PhingFile $src)
528		{
529		$this->tSrcFile = $src;
530		}
531
532		public function addText($sql)
533		{
534		$this->tSqlCommand .= $sql;
535		}
	561	private $tSrcFile = null;
	562	private $tSqlCommand = "";
	563	private $parent;
	564
	565	function __construct($parent)
	566	{
	567	// Parent is required so that we can log things ...
	568	$this->parent = $parent;
	569	}
	570
	571	public function setSrc(PhingFile $src)
	572	{
	573	$this->tSrcFile = $src;
	574	}
	575
	576	public function addText($sql)
	577	{
	578	$this->tSqlCommand .= $sql;
	579	}
536	580
537	581	/**
…	…
540	584	public function runTransaction($out = null)
541	585	{
542		if (!empty($this->tSqlCommand)) {
543		$this->parent->log("Executing commands", Project::MSG_INFO);
544		$this->parent->runStatements(new StringReader($this->tSqlCommand), $out);
545		}
546
547		if ($this->tSrcFile !== null) {
548		$this->parent->log("Executing file: " . $this->tSrcFile->getAbsolutePath(),
549		Project::MSG_INFO);
550		$reader = new FileReader($this->tSrcFile);
551		$this->parent->runStatements($reader, $out);
552		$reader->close();
553		}
	586	if (!empty($this->tSqlCommand)) {
	587	$this->parent->log("Executing commands", Project::MSG_INFO);
	588	$this->parent->runStatements(new StringReader($this->tSqlCommand), $out);
	589	}
	590
	591	if ($this->tSrcFile !== null) {
	592	$this->parent->log("Executing file: " . $this->tSrcFile->getAbsolutePath(),
	593	Project::MSG_INFO);
	594	$reader = new FileReader($this->tSrcFile);
	595	$this->parent->runStatements($reader, $out);
	596	$reader->close();
	597	}
554	598	}
555	599	}

Navigation

Changeset 256

Legend:

branches/2.3/classes/phing/tasks/ext/PDOSQLExecTask.php

Download in other formats: