test.php

开源MARC数据处理

<?php
/* 
V4.21 20 Mar 2004  (c) 2000-2004 John Lim ([e-mail]). All rights reserved.
  Released under both BSD license and Lesser GPL library license. 
  Whenever there is any discrepancy between the two licenses, 
  the BSD license will take precedence. 
  Set tabs to 4 for best viewing.
	
  Latest version is available at http://php.weblogs.com/
*/

error_reporting(E_ALL);

$ADODB_FLUSH = true;

define('ADODB_ASSOC_CASE',0);

if (PHP_VERSION < 5) include_once('../adodb-pear.inc.php');
//--------------------------------------------------------------------------------------
//define('ADODB_ASSOC_CASE',1);
//
function Err($msg)
{
	print "<b>$msg</b><br>";
	flush();
}

function CheckWS($conn)
{
global $ADODB_EXTENSION;

	include_once('../session/adodb-session.php');
	if (defined('CHECKWSFAIL')){ echo " TESTING $conn ";flush();}
	$saved = $ADODB_EXTENSION;
	$db = ADONewConnection($conn);
	$ADODB_EXTENSION = $saved;
	if (headers_sent()) {
		print "<p><b>White space detected in adodb-$conn.inc.php or include file...</b></p>";
		//die();
	}
}

function do_strtolower(&$arr)
{
	foreach($arr as $k => $v) {
		$arr[$k] = strtolower($v);
	}
}


function CountExecs($db, $sql, $inputarray)
{
global $EXECS;  $EXECS++;
}

function CountCachedExecs($db, $secs2cache, $sql, $inputarray)
{
global $CACHED; $CACHED++;
}

// the table creation code is specific to the database, so we allow the user 
// to define their own table creation stuff

function testdb(&$db,$createtab="create table ADOXYZ (id int, firstname char(24), lastname char(24), created date)")
{
GLOBAL $ADODB_vers,$ADODB_CACHE_DIR,$ADODB_FETCH_MODE, $HTTP_GET_VARS,$ADODB_COUNTRECS;

?>	<form method=GET>
	</p>
	<table width=100% ><tr><td bgcolor=beige>&nbsp;</td></tr></table>
	</p>
<?php  
	$create =false;
	/*$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
	
	$rs = $db->Execute('select lastname,firstname,lastname,id from adoxyz');
	$arr = $rs->GetAssoc();
	echo "<pre>";print_r($arr);
	die();*/
	
	GLOBAL $EXECS, $CACHED;
	
	$EXECS = 0;
	$CACHED = 0;
	if ((rand()%3) == 0) @$db->Execute("delete from adodb_logsql");
	$db->debug=1;
	
	$db->fnExecute = 'CountExecs';
	$db->fnCacheExecute = 'CountCachedExecs';
	
	if (empty($_GET['nolog'])) {
		echo "<h3>SQL Logging enabled</h3>";
		$db->LogSQL();/*
		$sql =
"SELECT t1.sid, t1.sid, t1.title, t1.hometext, t1.notes, t1.aid, t1.informant, 
t2.url, t2.email, t1.catid, t3.title, t1.topic, t4.topicname, t4.topicimage, 
t4.topictext, t1.score, t1.ratings, t1.counter, t1.comments, t1.acomm 
FROM `nuke_stories` `t1`, `nuke_authors` `t2`, `nuke_stories_cat` `t3`, `nuke_topics` `t4` 
	WHERE ((t2.aid=t1.aid) AND (t3.catid=t1.catid) AND (t4.topicid=t1.topic) 
	AND ((t1.alanguage='german') OR (t1.alanguage='')) AND (t1.ihome='0')) 
	ORDER BY t1.time DESC";
		$db->SelectLimit($sql);
		echo $db->ErrorMsg();*/
	}
	$ADODB_CACHE_DIR = dirname(TempNam('/tmp','testadodb'));
	$db->debug = false;
	
	//print $db->UnixTimeStamp('2003-7-22 23:00:00');
	
	$phpv = phpversion();
	if (defined('ADODB_EXTENSION')) $ext = ' &nbsp; Extension '.ADODB_EXTENSION.' installed';
	else $ext = '';
	print "<h3>ADODB Version: $ADODB_vers Host: <i>$db->host</i> &nbsp; Database: <i>$db->database</i> &nbsp; PHP: $phpv $ext</h3>";
	flush();
	$arr = $db->ServerInfo();
	print_r($arr);
	echo "<br>";
	$e = error_reporting(E_ALL-E_WARNING);
	flush();
	print "<i>date1</i> (1969-02-20) = ".$db->DBDate('1969-2-20');
	print "<br><i>date1</i> (1999-02-20) = ".$db->DBDate('1999-2-20');
	print "<br><i>date1.1</i> 1999 = ".$db->DBDate("'1999'");
	print "<br><i>date2</i> (1970-1-2) = ".$db->DBDate(24*3600)."<p>";
	print "<i>ts1</i> (1999-02-20 13:40:50) = ".$db->DBTimeStamp('1999-2-20 1:40:50 pm');
	print "<br><i>ts1.1</i> (1999-02-20 13:40:00) = ".$db->DBTimeStamp('1999-2-20 13:40');
	print "<br><i>ts2</i> (1999-02-20) = ".$db->DBTimeStamp('1999-2-20');
	print "<br><i>ts3</i> (1970-1-2 +/- timezone) = ".$db->DBTimeStamp(24*3600);
	print "<br> Fractional TS (1999-2-20 13:40:50.91): ".$db->DBTimeStamp($db->UnixTimeStamp('1999-2-20 13:40:50.91+1'));
	 $dd = $db->UnixDate('1999-02-20');
	print "<br>unixdate</i> 1999-02-20 = ".date('Y-m-d',$dd)."<p>";
	print "<br><i>ts4</i> =".($db->UnixTimeStamp("19700101000101")+8*3600);
	print "<br><i>ts5</i> =".$db->DBTimeStamp($db->UnixTimeStamp("20040110092123"));
	print "<br><i>ts6</i> =".$db->UserTimeStamp("20040110092123");
	print "<br><i>ts6</i> =".$db->DBTimeStamp("20040110092123");
	flush();
	// mssql too slow in failing bad connection
	if (false && $db->databaseType != 'mssql') {
		print "<p>Testing bad connection. Ignore following error msgs:<br>";
		$db2 = ADONewConnection();
		$rez = $db2->Connect("bad connection");
		$err = $db2->ErrorMsg();
		print "<i>Error='$err'</i></p>";
		if ($rez) print "<b>Cannot check if connection failed.</b> The Connect() function returned true.</p>";
	}
	error_reporting($e);
	flush();
	
	//$ADODB_COUNTRECS=false;
	$rs=$db->Execute('select * from adoxyz order by id');

	//print_r($rs);
	//OCIFetchStatement($rs->_queryID,$rez,0,-1);//,OCI_ASSOC | OCI_FETCHSTATEMENT_BY_ROW);
	//print_r($rez);
	//die();
	if($rs === false) $create = true;
	else $rs->Close();
		
	//if ($db->databaseType !='vfp') $db->Execute("drop table ADOXYZ");
	
	if ($create) {
		if (false && $db->databaseType == 'ibase') {
			print "<b>Please create the following table for testing:</b></p>$createtab</p>";
			return;
		} else {
			$db->debug = 1;
			$e = error_reporting(E_ALL-E_WARNING);
			$db->Execute($createtab);
			error_reporting($e);
		}
	}
	$rs = &$db->Execute("delete from ADOXYZ"); // some ODBC drivers will fail the drop so we delete
	if ($rs) {
		if(! $rs->EOF) print "<b>Error: </b>RecordSet returned by Execute('delete...') should show EOF</p>";
		$rs->Close();
	} else print "err=".$db->ErrorMsg();

	print "<p>Test select on empty table, FetchField when EOF, and GetInsertSQL</p>";
	$rs = &$db->Execute("select id,firstname from ADOXYZ where id=9999");
	if ($rs && !$rs->EOF) print "<b>Error: </b>RecordSet returned by Execute(select...') on empty table should show EOF</p>";
	if ($rs->EOF && ($o = $rs->FetchField(0))) {
		$record['id'] = 99;
		$record['firstname'] = 'John';
		$sql =  $db->GetInsertSQL($rs, $record);
		if ($sql != "INSERT INTO ADOXYZ ( id, firstname ) VALUES ( 99, 'John' )") Err("GetInsertSQL does not work on empty table");
	} else {
		Err("FetchField does not work on empty recordset, meaning GetInsertSQL will fail...");
	}
	if ($rs) $rs->Close();
	flush();
	//$db->debug=true;	
	print "<p>Testing Commit: ";
	$time = $db->DBDate(time());
	if (!$db->BeginTrans()) {
		print '<b>Transactions not supported</b></p>';
		if ($db->hasTransactions) Err("hasTransactions should be false");
	} else { /* COMMIT */
		if (!$db->hasTransactions) Err("hasTransactions should be true");
		if ($db->transCnt != 1) Err("Invalid transCnt = $db->transCnt (should be 1)");
		$rs = $db->Execute("insert into ADOXYZ (id,firstname,lastname,created) values (99,'Should Not','Exist (Commit)',$time)");
		if ($rs && $db->CommitTrans()) {
			$rs->Close();
			$rs = &$db->Execute("select * from ADOXYZ where id=99");
			if ($rs === false || $rs->EOF) {
				print '<b>Data not saved</b></p>';
				$rs = &$db->Execute("select * from ADOXYZ where id=99");
				print_r($rs);
				die();
			} else print 'OK</p>';
			if ($rs) $rs->Close();
		} else {
			if (!$rs) {
				print "<b>Insert failed</b></p>";
				$db->RollbackTrans();
			} else print "<b>Commit failed</b></p>";
		}
		if ($db->transCnt != 0) Err("Invalid transCnt = $db->transCnt (should be 0)");
		
		/* ROLLBACK */	
		if (!$db->BeginTrans()) print "<p><b>Error in BeginTrans</b>()</p>";
		print "<p>Testing Rollback: ";
		$db->Execute("insert into ADOXYZ (id,firstname,lastname,created) values (100,'Should Not','Exist (Rollback)',$time)");
		if ($db->RollbackTrans()) {
			$rs = $db->Execute("select * from ADOXYZ where id=100");
			if ($rs && !$rs->EOF) print '<b>Fail: Data should rollback</b></p>';
			else print 'OK</p>';
			if ($rs) $rs->Close();
		} else
			print "<b>Commit failed</b></p>";
			
		$rs = &$db->Execute('delete from ADOXYZ where id>50');
		if ($rs) $rs->Close();
		
		if ($db->transCnt != 0) Err("Invalid transCnt = $db->transCnt (should be 0)");
	}
	
	if (1) {
		print "<p>Testing MetaDatabases()</p>";
		print_r( $db->MetaDatabases());
		
		print "<p>Testing MetaTables() and MetaColumns()</p>";
		$a = $db->MetaTables();
		if ($a===false) print "<b>MetaTables not supported</b></p>";
		else {
			print "Array of tables and views: "; 
			foreach($a as $v) print " ($v) ";
			print '</p>';
		}
		
		$a = $db->MetaTables('VIEW');
		if ($a===false) print "<b>MetaTables not supported</b></p>";
		else {
			print "Array of views: "; 
			foreach($a as $v) print " ($v) ";
			print '</p>';
		}
		
		$a = $db->MetaTables(false,false,'aDo%');
		if ($a===false) print "<b>MetaTables not supported</b></p>";
		else {
			print "Array of ado%: "; 
			foreach($a as $v) print " ($v) ";
			print '</p>';
		}
		
		$a = $db->MetaTables('TABLE');
		if ($a===false) print "<b>MetaTables not supported</b></p>";
		else {
			print "Array of tables: "; 
			foreach($a as $v) print " ($v) ";
			print '</p>';
		}
		
		$db->debug=1;
		$a = $db->MetaColumns('ADOXYZ');
		if ($a===false) print "<b>MetaColumns not supported</b></p>";
		else {
			print "<p>Columns of ADOXYZ: <font size=1><br>";
			foreach($a as $v) {print_r($v); echo "<br>";}
			echo "</font>";
		}
		
		print "<p>Testing MetaIndexes</p>";
		$a = $db->MetaIndexes('ADOXYZ',true);
		if ($a===false) print "<b>MetaIndexes not supported</b></p>";
		else {
			print "<p>Indexes of ADOXYZ: <font size=1><br>";
			foreach($a as $v) {print_r($v); echo "<br>";}
			echo "</font>";
		}
		print "<p>Testing MetaPrimaryKeys</p>";
		$a = $db->MetaPrimaryKeys('ADOXYZ');
		var_dump($a);
	}
	$rs = &$db->Execute('delete from ADOXYZ');
	if ($rs) $rs->Close();
	
	$db->debug = false;
	
	
	switch ($db->databaseType) {
	case 'postgres7':
	case 'postgres64':
	case 'postgres':
	case 'ibase':
		print "<p>Encode=".$db->BlobEncode("abc\0d\"'
ef")."</p>";//'

		print "<p>Testing Foreign Keys</p>";
		$arr = $db->MetaForeignKeys('adoxyz',false,true);
		print_r($arr);
		if (!$arr) Err("Bad MetaForeignKeys");
		break;
	
	case 'odbc_mssql':
	case 'mssqlpo':
		print "<p>Testing Foreign Keys</p>";
		$arr = $db->MetaForeignKeys('Orders',false,true);
		print_r($arr);
		if (!$arr) Err("Bad MetaForeignKeys");
		if ($db->databaseType == 'odbc_mssql') break;
	
	case 'mssql': 
	
		
/*
ASSUME Northwind available...

CREATE PROCEDURE SalesByCategory
	@CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'
AS
IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998' 
BEGIN
	SELECT @OrdYear = '1998'
END

SELECT ProductName,
	TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)
FROM [Order Details] OD, Orders O, Products P, Categories C
WHERE OD.OrderID = O.OrderID 
	AND OD.ProductID = P.ProductID 
	AND P.CategoryID = C.CategoryID
	AND C.CategoryName = @CategoryName
	AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName
GO
*/
		print "<h4>Testing Stored Procedures for mssql</h4>";
		$saved = $db->debug;
		$db->debug=true;
		
		$cat = 'Dairy Products';
		$yr = '1998';
		
		$stmt = $db->PrepareSP('SalesByCategory');
		$db->InParameter($stmt,$cat,'CategoryName');
		$db->InParameter($stmt,$yr,'OrdYear');
		$rs = $db->Execute($stmt);
		rs2html($rs);
		
		$cat = 'Grains/Cereals';
		$yr = 1998;
		
		$stmt = $db->PrepareSP('SalesByCategory');
		$db->InParameter($stmt,$cat,'CategoryName');
		$db->InParameter($stmt,$yr,'OrdYear');
		$rs = $db->Execute($stmt);
		rs2html($rs);