query_sql.inc

PHPLOB注释详细版 使用模板技术的好帮手 PHP最有用的东东了

<?php
/*
 * Query generation for PHP3
 *
 * (C) Copyright 1998 Alex Aulbach
 *     Credits: Gerard Hickey <[e-mail]>
 *              I took many ideas from his SQL.inc, thanks! :-)
 *     The idea is of this class is based in November 1997,
 *     it was a collection of functions for PHP/FI and mSQL.
 *
 * $Id: query_sql.inc,v 1.1.1.1 2000/04/17 16:40:12 kk Exp $
 *
 */


/*
The Query-class is an enhancement to the db_*-classes. Currently It supports
mySQL an Oracle but it is easy expandable. See down.

It always needs the class DB_Sql!

Query is fully upward compatible with DB_Sql. Example:

OLD:                                NEW:

require("db_mysql.inc");            require("db_mysql.inc");
class hugobla extends DB_sql {}     require("query_sql.inc");
$db = new hugobla;                  class hugobla extends Query {}
                                    $db = new hugobla;

It just provides a number of new functions.

The Query-class is inteded to help you with creating selects, inserts,
updates, where-clauses etc. Not just *simple* selects, but longer ones. It
is indeed a great help for tables with more than 10-20 columns. But it can
also be used for simple and small selects. The inbuilt checks help you
programming saver.

Here is an example:

file: insert.php3
------------------
<?
## gets data from my form and inserts it into db

require("prepend.inc"); ## here the classes are loaded and configured
$db = new hugobla;
$db->query($db->insert_plain_Clause("mytable",$db->capture_vars("mytable"),ARRAY()));
echo "Values inserted";

?>

file: insert2.php3
-------------------
<?
## gets data from my form and inserts it into db with a new INDEX
## myindex is defined as INT AUTO_INCREMENT PRIMARY KEY
## (this is mysql, in oracle you have to define a trigger)
## mytime is defined as DATETIME (DATE in oracle)

require("prepend.inc"); ## here the classes are loaded and configured
$db = new hugobla;
$mytime="SYSDATE()";
$db->query($db->insert_plain_Clause("mytable",$db->capture_vars("mytable"),
     ARRAY(myindex=>'NULL',mytime='func')));
echo "Values inserted: "$db->last_insert_id();

?>

This example is nice, cause you see how easy it can be used. :-)

The best thing is, that you don't have to care, if a field is a string or a
number. The values are automatically converted into the right form. The type
of the vars are read from the table. Stringvalues are encapsulated with '
(configurable) and escaped (the code for this is currently not good - we are
assuming, that PHP is configured not to encapsulate strings), int-
and real-values are casted. It can handle "NULL"-values, function-statements
or other values for insertion.

You will make less errors.

mySQL and most other DB's accept a a short form of insert-clause (INSERT
INTO bla VALUES (...)). The Query-class will always make the longer form
(INSERT INTO BLA (...) VALUES (...)). This makes it possible to use ALTER
TABLE-commands without changing the program! E.g. changing a field in a
table from NUMBER to VARCHAR(10) is fully encapsulated with this class.

The class supports currently only mysql and oracle. I think the differences
between the DBs are encapsulated enough in the db_* classes, so it is
possible to handle the remaining small differences inside this class (this
affects mainly the function sql2phptype() ) and it could be easiely extended
(asuming, that the metadata()-function of the db_*-class works correctly).
In this case it is important, that the $type-variable in the db_*.inc-class
is correctly set.


TODO-list:
- A method to create querys like the LIMIT-clause in mySQL. For Oracle
  this works:

  select linenum, foo, bar
  from (select rownum as linenum, foo, bar from
           (select foo,bar from chaos order by bar) )
  where linenum between 11 and 20;

- cleaner escaping, handling of \ and NUL (current code is bullshit)
  Some ideas?

- Little Alta-Vista: add functions to create a where clause from a search
  string with rules to handle searching for more than one word.
  half automatic generating search patterns into a where-clause
  simple search engine support, simple support for semi full-text-search

- automatic configurable manipulation of values, eg. 
  triming of strings (delete whitespace at begin and end)
  also : TOUPPER, TOLOWER etc

- SELECT-Clause (GROUP BY, HAVING, JOIN...)

- make new functions insert_Clause() etc. which inserts only the
  fields they got from your call (the current will do "plain" insert)

- where_Clause() - creating WHERE for select, update, exists etc.

- serv all queries directly into db, return just the handle
  (hm, how to deal with the DB-handle?)

- Return a 2-dimensional (Table-compatible) field from select (not so important)

- The sql2phptype() can be used to help creating automatic input forms
  for a table

DEPENDING:
- db_mysql: new function metatabledata(), which returns the table-info from
  current selected table (will return multiple table-columns with a join)
- db_mysql: perhaps the function sql2phptype() should be placed there?


For developers of new db_*.inc: the function metadata() is very important
for the correct work of this class. T

*/

class Query extends DB_Sql {

	## DONT FORGET to set the variables from DB_Sql! See there!

	## For debugging: if set to TRUE the Query is printed out,
	## before executing or returning 
	var $Q_Debug=false;

	## set this to another value, if you want to hide it
	## in your HTML-code
	## example: var $Q_Debug_print="\n<!-- QDebug: %s -->\n";
	var $Q_Debug_print="<BR><B>QDebug:</B>\n%s\n<BR>\n";

	## Set this to TRUE if you only want to test, which query
	## will be created (ideal in combination with $Q_Debug)
	## This depends only functions which will make changes
	var $No_Write=false;

	## currently unused, this var is just an idea for later use.
	var $Backslash_N_is_NULL = false;

	## Metacache: see funtcion metadata_buffered()
	var $meta_cache_off = false;

	## This is the char, which will be replaced by \char.
	## PHP3 seems to be NOT binary-safe, so not quoting
	## for \0  (some ideas?)
	## we use ereg_replace to do the replacements.
	## with PHP3.0.6 you should replace this with str_replace()!
	##   Quoting = 1 -> normal quoting using AddSlashes
	##             2 -> Replace \' to '' - needed eg. for sybase or oracle
	var $Quoting=1;
	var $Quotechar="'";
	
	var $StrLengTrunc = false;
	var $StrLengWarn = false;

	###########################
	## _QDebug
	function _QDebug ($str) {
		if ($this->Q_Debug) {
			printf($this->Q_Debug_print,$str);
		}
	}

	###########################
	## Set DB-Classname
	## This is only a 3rd posibility for setting the classname
	##
	function set_db_class ($db_class) {
		$this->Database=$db_class;
	}


	###########################
	## This function gets a datatype from the DB and returns an
	## equivalent datatype for PHP
	##
	## It returns also a subtype for a string
	##
	function sql2phptype ($type,$format='') {
		## $this->type is currently either "mysql" or "oracle"
		switch ($this->type) { 
			case "mysql":
				switch ($type) {
					case "var string":
					case "string" :
					case "char" :
						return(Array("string",""));
						break;
					case "timestamp" :
					case "datetime" :
					case "date" :
					case "time" :
						return(Array("string","date"));
						break;
					case "blob" :
						return(Array("string","blob"));
						break;
					case "real" :
						return(Array("double",""));
						break;
					case "long" :
					default :
						return(Array("int",""));
						break;
				}
				break;
			case "oracle":
				switch ($type) {
					case "VARCHAR2" :
					case "VARCHAR" :
					case "CHAR" :
						return(Array("string",""));
						break;
					case "DATE" :
						return(Array("string","date"));
						break;
					case "BLOB" :
					case "CLOB" :
					case "BFILE" :
					case "RAW" :
					case "LONG" :
					case "LONG RAW" :
						return(Array("string","blob"));
						break;
					case "NUMBER" :
						if ($format) {
							return(Array("double",""));
						} else {
							return(Array("int",""));
						}
						break;
					default :
						$this->halt("sql2phptype(): Type is not a valid value: '$type'");
						break;
				}
				break;
			default:
				$this->halt("sql2phptype(): DB-type is not a valid value: ".$this->type);
				break;
		}
	}


	#######################################
	## This function returns a PHP-variable depending
	## on type. E.g. a string is returned as 'string'
	##
	## The parameters mean
	## $val - the value
	##        There is a special case: If value is "NULL" and
	##        the type is not "string" or subtype is empty, then
	##        a value "NULL" is inserted. This let you just spare
	##        a little bit work with $special
	##
	## $meta - the meta information for this field (that's what
	##         is returned by metadata() from DB_sql-class, but just one
	##         single row, e.g. $meta[2], not hole $meta).
	##
	## $special - Overwrites the type of the var if set. Some special
	##            meanings:
	##            "NULL" means, that this value must be set to "NULL"
	##            "func" means, that $val should be untouched -
	##            e.g. to insert the value of a SQL-function
	##            [ INSERT INTO bla VALUES ( time=NOW() ) ]
	##

	function convert ($val,$meta,$special="") {
		list($type,$subtype)=$this->sql2phptype($meta["type"],$meta["format"]);
		if (($val == "NULL" &&
		    ($type != "string" || $type[1] != "")) ||
		    $special == "NULL") {
			$type="NULL";
		} else {
			if ($special) {
				$type=$special;
				if ($type!="func") {
					$val=$type;
					$type="func";
				}
			}
		}
		switch ($type) {
			case "string" :
				$val=(string)$val;
				if ($this->Quoting) {
					$val=AddSlashes($val);
				}
				if ($this->Quoting==2) {
					$val=str_replace("\\'","''",$val);
				}
				if ($subtype!='date' &&
				    ( $this->StrLengTrunc || $this->StrLengWarn ) ) {
					if ( strlen($val) > $meta[len] ) {
						if ($this->StrLengWarn) {
							echo "<BR>STRING TOO LONG: '$meta[name]'";
							if ($this->StrLengTrunc) {
								echo ", TRUNCATING!";
							}
						}
						if ($this->StrLengTrunc) {
							$val=substr($val,0,$meta[len]);
						}
					}
				}
				$val=$this->Quotechar . $val . $this->Quotechar;
				break;
			case "int" :
				$val=(int)$val;
				break;
			case "double" :
				$val=(double)$val;
				break;
			case "NULL" :
				$val="NULL";
				break;
			case "func" :
				$val=(string)$val;
				break;
			default :
				echo "UNKNOWN TYPE: $type<BR>";
		}
		$this->_QDebug("Val: $meta[name] => $val<BR>");
		return(Array($val,$meta["name"]));
	}


	##
	## Function to generate a plain INSERT-Clause
	## ("plain" means, that every field in the table will
	##  be set to a value, default is '' or 0 if nothing said
	##  in $special)
	##
	## $fields  is an assoc. Array consisting out of
	##          table_name => value-pairs
	## $special is an assoc. field which will commit special
	##          handling to convert() (See there)
	## $check   could be "strong" or "soft".
	##          "soft" won't tell you if there were to less
	##          or too much fields (good for debuging)
	##
	## returns the insert clause. It's on you to modify it
	## and send it to your DB
	##
	function insert_plain_Clause ($table,$fields,$special,$check="soft") {
		$meta=$this->metadata_buffered($table);

		for ($i=0; $i < $meta["num_fields"]; $i++) {