BlueShoes Application Framework - db

Bs_MySql

Bs_Object
   |
  +-- Bs_Db
     |
    +-- Bs_MySql

**********************************************************************

public class Bs_MySql extends Bs_Db

**********************************************************************
$Id: Bs_MySql.html,v 1.1.1.1 2003/07/04 15:17:18 andrej Exp $This is the API for mySQL it's an extention of the abstract class Bs_Db.class.phpA good understanding of the built in mySQL functions in php is needed to create clean, goodcode using these classes. please RTFphpM first. here i'll try to avoid copying the php docover and over again.Some collected knowledge:-------------------------o) When trying to INSERT or UPDATE and trying to put a large amount of text or data (blob)into a mySQL table you might run into problems. In mysql.err you might see:Packet too large (73904)To fix you just have to start up mySQL with the option -O max_allowed_packet=maxsizeYou would just replace maxsize with the max size you want to insert, the default is 65536.o) I think php's mySQL functions are bad documented. Sometimes the functions print outerror messages to the browser, but that's not always documented. Use the @ to suppresserrors for all those functions. Another problem are the return values. Don't trust them.Sometimes you can get an undocumented NULL on an error instead of the expected bool.Please make sure the coder can trust YOUR return values.o) In mySQL, databases and tables are file-based. so they are case sensitive, except onwindows. (Fields are always case insentitive, btw). Now if you issue a statement onwindows like: "create table MyTaBlEnAmE ..." the files generated are:mytablename.frm, mytablename.MYD, mytablename.MYI (!). The table names are written inlower case! So if you take your existing db's from linux, move them to a windowsserver, and then back to linux, your code is broken because all dbs and tables noware written lowercase. I hope mySQL is going to change this behavior soon...*NEVER* create more than 1 db/table with the same name but different cases.--sam Aug.2002:One way to avoid the above problem is to use mysqld with -O lower_case_table_names=0.By default this option is 1 on Windows and 0 on Unix. (Not tested)If you move MyISAM files from a Windows to a *nix disk, you may in some cases need touse the `mysql_fix_extensions' tool to fix-up the case of the file extensions in eachspecified database directory (lowercase `.frm', uppercase `.MYI' and `.MYD').`mysql_fix_extensions' can be found in the `script' subdirectory.o) Quick overview of the features of mySQLavailable | comments----------------------+------------------------------------------------------------------------transactions | using table/handler:| berkeley: table-lock.| gemini: row-lock. max record size currently 32kb. no gemini support| built into the shipped versions. not gpl yet.| innodb: row-lock. fast, similar to the myISAM type. looks like it's| the best one of these 3.| note: not sure if the blob data types of the 3 table types are| any good now. all 3 table types lack some functionality here| and there, and have some problems here and there, and need| some manual configuration after the mysql installation.fulltext indexing | it's kinda new.----------------------+------------------------------------------------------------------------not available | comments----------------------+------------------------------------------------------------------------subselects | :( not in V3.23.xx (planed for V4.1). See $this->subSelect() functionstored procedures | (planed for V4.1).triggers | *not* planned. Monty is against it because of the performance los.foreign keys | Quotation from the mySQL doc (march.2001):| "In the near future we will extend the FOREIGN KEY implementation so| that at least the information will be saved in the table specification| file and may be retrieved by mysqldump and ODBC. At a later stage we| will implement the foreign key constraints for application that can't| easily be coded to avoid them."views | planned, on the todo list.'foreign chars' | No support for spaces, foreign and special chars, and reserved words| in field, table and database names. MS-Access and Oracle support this.The following php mySQL functions are *NOT* implemented here because we don't want youto use them:mysql_fetch_object() => Use fetchRow() which makes use of mysql_fetch_assoc() or mysql_fetch_row()mysql_fetch_array() => Use fetchRow() which makes use of mysql_fetch_assoc() or mysql_fetch_row()mysql_result() => I wanted to code a function fetchCell($result, $row), thought it would besometimes nice to just check a field before getting the whole record. Butthe doc sais "Calls to mysql_result() should not be mixed with calls toother functions that deal with the result set." So better don't fuck with it.If you only want one field, well, only select one. Otherwise get the fullrecord using fetchRow(). Also see Bs_Db->getOne()mysql_db_query() => Use read("SELECT * FROM database.table") instead. mysql_db_query()actually SWITCHES the current database PERMANENTLY just like mysql_select_db()does.mysql_create_db() => I don't see an advantage for this compared to "create database database_name".mysql_drop_db() => I don't see an advantage for this compared to "drop database database_name".mysql_fetch_lengths() => I don't see a use for it. If you do, feel free to write a nice clean wrapper.There are a bunch of interesting C api functions in the mySQL manual that are not yet implemented in php.watch out for them...mysql_change_user() only php3mysql_real_escape_string() see $this->escapeString()mysql_info() Returns information about the most recently executed query.mysql_ping() Checks whether or not the connection to the server is working, reconnecting as necessary.mysql_reload() Tells the server to reload the grant tables.mysql_shutdown() Shuts down the database server.mysql_start_slave() Starts slave replication thread.mysql_stop_slave() Stops slave replication thread.mysql_stat() Returns the server status as a string.TODO----This class is accurate for mySQL 3.23.36 and it's manual 3.23.33. When a new mySQL version comes out/is used,please check the following methods to be up to date:fieldFlags(), hasFieldFlag(), fieldType(), getTableProperties(), getTableInfo(), getTableType(),tableHasTransactions(), serverSupportsTableType(), getIniVar(), getStatusVar()Make sure the existing code doesn't break because of your changes! thanx.Some intresting mySQL functionality that could be implemented here:show [full] columns from table;show processlist; KILL thread_id; //If you have the process privilege, you can see and kill all threads.Otherwise, you can see and kill only your own threads.Replication: show master status; show master logs; show slave status;mysql 4.0 alpha: Added SQL_CALC_FOUND_ROWS and FOUND_ROWS(). This makes it possible to know how many rows a query would have returned without a LIMIT clause.mysql 4.0 alpha: Changed output format of SHOW OPEN TABLES.Fixed that ALTER TABLE table_name RENAME new_table_name is as fast as RENAME TABLE. (=> implement that feature here...)dependencies: Bs_Db

Authors

Version 4.0.$id$

Copyright blueshoes.org

Methods inherited from Bs_Db

&getdbobject, bs_db, &getdsn, provides, getnumrecords, getfieldvalue, read, &rsread, countread, write, _vendorspecificwritemod, countwrite, idwrite, &getone, &getrow, &getcol, &getassoc, &getassoc2, &getall, assertextension, tostring, formatdatefordb, formatdatetimefordb, formattimestampfordb, "eargs, ismanipulation, &_raiseerror, geterrormessage, parsedsn, fetchrow, nativeerrormsg, _disconnect, fetchdbnames

Methods inherited from Bs_Object

isex, isexception, tostring, tohtml, persist, unpersist, bs_object, bbsetoutput, bbawake, bbisawake, bbxmsg, bbxfunctionstart, bbxfunctionend, bbxecho, bbxvar, bbxvardump, bbforcetrace, bbbufferstart, bbbufferget, bbbufferendflush, bbbufferendclean

Public Method Summary
void	Bs_MySql() Constructor.
void	autoCommit([ string $on ]) Turn autoCommit on or offWarning: documentation is missing.
void	startTransaction([ string $transactionId ]) Starts a transactionWarning: documentation is missing.
void	commit([ string $transactionId ]) Commit the current or given transaction.Warning: documentation is missing.
void	rollback([ string $transactionId ]) Rollback last queryWarning: documentation is missing.
int	connect(array $dsn, string $persistent) Connect to a database server and log in as the specified user.
bool	disconnect() Log out and disconnect from the database.
bool	selectDb(string $db) Select a MySQL database.
int	subSelect(string $queryWithSubquery) Handles a single nested SELECT statement to overcome that limitation in MySql
mixed	&fetchRow(int $result, string $fetchMode) Fetch a row and return it as vector or hash depending on the fetchMode.
int	numCols( $result) Get the number of columns (fields) from a result set.Warning: documentation is missing.
int	numRows( $result) Get the number of rows from a result set.Warning: documentation is missing.
int	affectedRows() Gets the number of rows affected by the data manipulation query.Warning: documentation is missing.
int	insertId() Returns the ID generated for an AUTO_INCREMENT column by the previous INSERT query on this thread.Warning: documentation is missing.
void	freeResult( $result) Free the internal resources associated with $result. Only needs to be called if you areWarning: documentation is missing.
string	fieldName(int $result, $offset) Get the field name at the specified offset from a result id.
string	tableName(int $result, $offset) Returns the name of the table that the field (specifed by an offset) is in.
string	tableName2(int $result, $offset) Get the name of the specified table in a result you got from mysql_list_tables().
string	databaseName(int $result, $offset) Get the name of the specified db in a result you got from mysql_list_dbs().
int	fieldLen(int $result, $offset) Returns the length of a field at the specified offset from a result id.
mixed	fieldFlags(int $result, string $offset, string $format) Get the flags of a field at the specified offset from a result id.
boolean	hasFieldFlag(int $result, string $offset, $flag) Tells whether the field has the given flag. The field is specified by offset in a result id.
string	fieldType(int $result, integer $offset) Get the type of a field at the specified offset from a result id.
void	listDatabases() returns a result identifier for the available databases.
void	listTables([ string $dbName ]) Retrieves information about the given table. (does it? is that text correct? --andrej)
void	listFields(string $tableName, [ string $dbName ]) Retrieves information about the given tablename.Warning: documentation is missing.
mixed	&fetchDatabaseNames([ string $format, string $useCache ]) Return the available db names.
mixed	&fetchTableNames([ string $dbName, string $format, string $useCache ]) Return the table names of the given database.
mixed	&fetchFieldNames(string $tblName, [ string $dbName, string $format, string $useCache ]) Return the field names of the given database table.
void	&getDbStructure([ string $dbName, string $useCache ]) Return a 2-D array of tables and fieldnames found in the given db.
object [unknown]	fetchField(int $result, string $offset) Returns an object containing field information (of the table structure).
bool	fieldExists(mixed $fieldName, string $tableName, [ string $dbName, string $useCache ]) Tells whether the db field (or fields) exist or not.
bool	tableExists(mixed $tableName, [ string $dbName, string $useCache ]) Tells whether the db table (or tables) exist or not.
bool	databaseExists(mixed $dbName, [ string $useCache ]) Tells whether the database (or databases) exist or not.
array	getTableStructure(string $tblName, [ string $dbName ]) Returns an array with information about the fields of the given db table.
bool	updateTableStructure(array $structure, string $tblName, [ string $dbName, integer $shrink ]) updates an existing db table to the desired structure.
array	getTableProperties(string $tblName, [ string $dbName ]) !!!!!!!!!!!!!!!!deprecated!!!!!!!!!!!!!!!!!!!!!!
void	getTableLastmod(string $tblName, [ string $dbName ]) returns the datetime of the last change to the data of the given db table.
void	getTablesLastmod(array $dbs) this is similar to getTableLastmod(), note the extra s.
array	getTableInfo(string $tblName, [ string $dbName ]) Returns a hash holding information about the given db table.
string	getTableType(string $tblName, [ string $dbName ]) Returns the table type of the specified db table.
bool	tableHasTransactions(string $tblName, [ string $dbName ])
bool	serverSupportsTableType(string $tableType) Tells whether the given table type is supported by the server or not.
mixed	getIniVar([ string $key, string $useCache ]) Return the value of the currently used ini setting var.
mixed	getStatusVar([ string $key, string $useCache ]) Return the value of the specified status var.
string	getClientInfo() Returns a string that represents the client library version.
string	getHostInfo() Returns a string describing the type of connection in use, including the server host name.
int	getProtocolInfo() Returns the protocol version used by current connection.
string	getServerInfo() Returns a string that represents the server version number.
string	escapeString(string $query) Escape string for mysql query.Warning: documentation is missing.
mixed	setPointer(int $result, $absolutPos) Sets the internal row pointer of the result id to point to the specified row number.Warning: documentation is missing.
void	isValidName(string $string) Tells if a name for a db, table or field is accepted by mysql or not.
int	isReservedWord(string $word) Tells if a word is a reserved word for mysql.
int	nativeErrorCode() Returns the numerical native error code from the previous MySQL operation or 0 (zero) if no error occured.Warning: documentation is missing.
string	&nativeErrorMsg() Returns the native error text from the previous MySQL operation or '' (empty string) if no error occured.
string	&nativeError() Returns the native error from the previous MySQL operation or '' (empty string) if no error occured.
mixed	_dbErrorToBsError(int $dbError) Maps error codes of the current dbms to bs-dbErrorCodes.

Private Method Summary
int	_query(string $query) Send a query to MySQL and return the result, no matter what it was.Warning: documentation is missing.
string	_createAlterLine(string $fieldName, array $fieldDef) creates an alter table line (part of it).
mixed	getOpenTables([ string $dbName, string $return ]) Returns the currently open tables in $db.
object an	_mysqlRaiseError([ string $errNo, string $file, string $line, string $weight, string $msg ]) Throw an error. This method makes use of Bs_Db->_raiseError().
void	fetchCell( $result, $row, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $this, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id, $id) Warning: documentation is missing.
void	tableInfo( $result, string $mode) Warning: documentation is missing.
void	switchUser( $user, $pass) Warning: documentation is missing.

Fields inherited from Bs_Db

$_connection, $_connected, $_persistent, $_dsninfo, $_lastquery, $bs_date, $_bs_system

Public Field Summary
array	$format some database specific formats. overwrites parent.

Private Field Summary
string	$_databaseNames cached result from fetchDatabaseNames().
string	$_tableNames cached result from fetchTableNames().
string	$_fieldNames cached result from fetchFieldNames().
string	$_iniVars cached result from getIniVar().
string	$_statusVars cached result from getStatusVar().
string	$_currentlyOpenTransactionID Holds the 'transaction ID'. (If an trans. ID was set).
array	$_features Associative array of capabilities for this DB implementation.

Private Constant Summary
BS_MYSQL_VERSION	>>`4.0.$x$`<<	Warning: documentation is missing.

Public Method Details

Bs_MySql

public void Bs_MySql( )

Constructor.

Returns

void

autoCommit

public void autoCommit( [ string $on ] )

Turn autoCommit on or off

Warning: documentation is missing.

Parameter

string	$on	`= >>FALSE<<`
	(opt) If TRUE turn on else off (default TRUE)

Returns

void

Throws Bs_Exception if not supported by this DB

startTransaction

public void startTransaction( [ string $transactionId ] )

Starts a transaction
As optional parameter you can pass a transaction ID.When working with transactions, I run often in the situation, that I have a 'Master' routine callingother sub-routines and some sub-routines start their own transation. Multiple transaction 'starts'don't bother the Db, the transaction just stays open *BUT* the first to call commit or rollback willterminate the transaction! To prevent sub-routines from closing the 'Masters' transaction you maystart a transaction and pass a 'transaction ID'. Folloing commits/rollbacks are ignored as long as itdosen't match the 'transaction ID' given at start.NOTE I: Be aware of the highter deadlocking risk, when using 'transaction ID'.NOTE II: Make sure that you end the open transaction when using 'transaction ID'.Otherwise the transaction will *stay open*.

Warning: documentation is missing.

Parameter

string	$transactionId	`= >>''<<`
	(opt). See text above.

Returns

void

Throws Bs_Exception if not supported by this DB or on any other error.

commit

public void commit( [ string $transactionId ] )

Commit the current or given transaction.

Warning: documentation is missing.

Parameter

string	$transactionId	`= >>''<<`
	(opt). See $this->startTransaction.

Returns

void

Throws Bs_Exception if not supported by this DB or on any other error.

rollback

public void rollback( [ string $transactionId ] )

Rollback last query

Warning: documentation is missing.

Parameter

string	$transactionId	`= >>''<<`
	(opt). See $this->startTransaction.

Returns

void

Throws Bs_Exception if not supported by this DB or on any other error.

connect

public int connect( array $dsn, string $persistent )

Connect to a database server and log in as the specified user.
use (select) the given db, if any.NOTE I: If we are already connected to somewhere (no matter where),the existing db connection gets closed first.NOTE II: It's possible to get back an exception but the connection was successfull.Probably the given default database could not be selected => BS_DB_ERROR_CANNOT_SELECT_DB

Parameter

array	$dsn
	. The parseDSN() is deactivated, would need cleanup.

string	$persistent
	Warning: documentation is missing.

Returns

int

resource id # on success

Throws Bs_Exception: one of BS_DB_ERROR_INVALID_DSN, BS_DB_ERROR_CONNECT_FAILED, BS_DB_ERROR_CANNOT_SELECT_DB

disconnect

public bool disconnect( )

Log out and disconnect from the database.

Returns

bool

TRUE if we already have been or are now disconnected, FALSE if we're on a persistant conn.

Throws NULL if we cannot tell in which state the connection is now. internally for this object, the connection is 'closed'.

selectDb

public bool selectDb( string $db )

Select a MySQL database.
this is a wrapper for mysql_select_db().

Parameter

string	$db
	a database name.

Returns

bool

true on success, false on failure.

subSelect

public int subSelect( string $queryWithSubquery )

Handles a single nested SELECT statement to overcome that limitation in MySql
as it isn't suported (yet).E.g.SELECT q,r,t FROM x WHERE y IN (SELECT ID FROM z WHERE ...) ORDER BY xxx;DELETE FROM x WHERE y IN (SELECT ID FROM z WHERE ...);NOTE I : The sub-select will only use the first colomn in the subselect !!To minimise the overhead, just fetch the 1 colomn in the subselect.NOTE II : The sub-select must be in parenthe and no parenthe may be used *in* the sub-select.MySql V3.23 states in 5.4.1 Sub-selects:"For more complicated subqueries you can often create temporary tables to hold the subquery.In some cases, *however* this option will not work. The most frequently encountered of thesecases arises with DELETE statements, for which standard SQL does not support joins(except in sub-selects).For this situation there are two options available until subqueries are supported by MySQL.The first option is to use a procedural programming language (such as Perl or PHP) to submita SELECT query to obtain the primary keys for the records to be deleted, and then use thesevalues to construct the DELETE statement (DELETE FROM ... WHERE ... IN (key1, key2, ...))."

Parameter

string	$queryWithSubquery
	the SQL query with sub-query

Returns

int

a valid MySQL result id

Throws Bs_Exception on error

Author(s) samuel blume <bs_php@infeer.com>

&fetchRow

public mixed &fetchRow( int $result, string $fetchMode )

Fetch a row and return it as vector or hash depending on the fetchMode.
Subsequent calls will return the next row in the result set, or NULL if there are no more rows.If fetchMode is BS_DB_FETCHMODE_ASSOC (default), it returns a hash array that correspondsto the fetched row else if it's BS_DB_FETCHMODE_ORDERED returns a vector starting at offset 0.A clean example usage of this method to fetch rows into an array:while ($row = $this->fetchRow($res, $fetchMode)) {if ($this->isException($row)) {$row->stackTrace('was here: currentFunction()', __FILE__, __LINE__);return $row;}$ret[] = $row;}

Parameter

int	$result
	(opt) (default BS_DB_FETCHMODE_ASSOC) see above.

string	$fetchMode
	Warning: documentation is missing.

Returns

mixed

vector , hash or NULL if there is no more data

Throws Bs_Exception on error.

numCols

public int numCols( $result )

Get the number of columns (fields) from a result set.

Warning: documentation is missing.

Parameter

	$result
	Warning: documentation is missing.

Returns

int

the number of columns per row in $result

Throws Bs_Exception with BS_DB_ERROR_INVALID_RS on an invalid $result param, Bs_Exception if mysql_num_fields() failed somehow

numRows

public int numRows( $result )

Get the number of rows from a result set.

Warning: documentation is missing.

Parameter

	$result
	Warning: documentation is missing.

Returns

int

the number of rows in $result

Throws Bs_Exception with BS_DB_ERROR_INVALID_RS on an invalid $result param, Bs_Exception if mysql_num_rows() failed somehow.

affectedRows

public int affectedRows( )

Gets the number of rows affected by the data manipulation query.
For other queries, this function returns FALSE.RTFM for mysql_affected_rows() !!!If the last query was a DELETE query with no WHERE clause, all of the recordswill have been deleted from the table but this function will return zero.hint I: use "DELETE FROM tbl WHERE 1=1"hint II: maybe you are looking for "TRUNCATE TABLE table_name" anyway.

Warning: documentation is missing.

Returns

int

number of rows affected by the last query or FALSE if none are affected or not a manipulation query.

Throws Bs_Exception if mysql_affected_rows() fails.

insertId

public int insertId( )

Returns the ID generated for an AUTO_INCREMENT column by the previous INSERT query on this thread.
Returns 0 if the previous query does not generate an AUTO_INCREMENT valueIf you need to save the value for later, be sure to call this function immediately after the querythat generates the value.This method is save, thanks to rick@surveyor.com for the information :)NOTE I : RTFM for mysql_insert_id()! There are some spcial cases!NOTE II: Returns FALSE if you use "INSERT DELAYED ..."I heared ppl complaining that recent versions only work if link_identifier is omitted.

Warning: documentation is missing.

Returns

int

A value>=1 (the ID) or 0 (no AUTO_INCREMENT value given) or FLASE (see above)

Throws Bs_Exception if mysql_insert_id() return an int >=1.

freeResult

public void freeResult( $result )

Free the internal resources associated with $result. Only needs to be called if you are
concerned about how much memory is being used for queries that return large result sets.All associated result memory is automatically freed at the end of the script's execution.

Warning: documentation is missing.

Parameter

	$result
	Warning: documentation is missing.

Returns

void

fieldName

public string fieldName( int $result, $offset )

Get the field name at the specified offset from a result id.
This is a wrapper for mysql_field_name().

Parameter

int	$result
	fieled offset in result beginning with 0.

	$offset
	Warning: documentation is missing.

Returns

string

the field name

Throws Bs_Exception with BS_DB_ERROR_INVALID_RS on an invalid $result param, Bs_Exception on a field index that doesn't exist or any other error.

See Also tableName(), databaseName(), listFields()

tableName

public string tableName( int $result, $offset )

Returns the name of the table that the field (specifed by an offset) is in.
This is a wrapper for mysql_field_table().

Parameter

int	$result
	fieled offset in result beginning with 0.

	$offset
	Warning: documentation is missing.

Returns

string

the table name

Throws Bs_Exception with BS_DB_ERROR_INVALID_RS on an invalid $result param, Bs_Exception on a field index that doesn't exist or any other error.

See Also fieldName(), databaseName(), listTables()

tableName2

public string tableName2( int $result, $offset )

Get the name of the specified table in a result you got from mysql_list_tables().
This is a wrapper for mysql_tablename().

Parameter

int	$result
	fieled offset in result beginning with 0.

	$offset
	Warning: documentation is missing.

Returns

string

the table name
@throw bs_exception with BS_DB_ERROR_INVALID_RS on an invalid $result param, bs_exception on a field index that doesn't exist or any other error.

See Also fieldName(), tableName(), databaseName(), listTables()

databaseName

public string databaseName( int $result, $offset )

Get the name of the specified db in a result you got from mysql_list_dbs().
This is a wrapper for mysql_db_name().I don't think you ever need this method ...

Parameter

int	$result
	fieled offset in result beginning with 0.

	$offset
	Warning: documentation is missing.

Returns

string

the db name

Throws Bs_Exception with BS_DB_ERROR_INVALID_RS on an invalid $result param, Bs_Exception on a field index that doesn't exist or any other error.

See Also fieldName(), tableName(), listDatabases()

fieldLen

public int fieldLen( int $result, $offset )

Returns the length of a field at the specified offset from a result id.
NOTE: Not the actual value length but the max possible field space:E.g. if a varchar(20) has the value 'foobar' it returns 20 not 6.This is a wrapper for mysql_field_len() AND mysql_fieldlen() .

Parameter

int	$result
	fieled offset in result beginning with 0.

	$offset
	Warning: documentation is missing.

Returns

int

the field length.

Throws Bs_Exception with BS_DB_ERROR_INVALID_RS on an invalid $result param, Bs_Exception on a field index that doesn't exist or any other error.

fieldFlags

public mixed fieldFlags( int $result, string $offset, string $format )

Get the flags of a field at the specified offset from a result id.
This is a wrapper for mysql_field_flags().The returned value is dependent on the $format param. E.g.:'string' => 'not_null primary_key blob''vector' => array('not_null', 'primary_key', 'blob')'hash' => array('not_null' => TRUE, 'primary_key' => TRUE, 'unique_key' => FALSE, ...)Please check the docs for mysql_field_flags to get a list of the current flags supported.By now, they are: (***IF THEY HAVE CHANGED, PLEASE UPDATE THE CODE OF THIS METHOD!***)"not_null", "primary_key", "unique_key", "multiple_key", "blob", "unsigned", "zerofill","binary", "enum", "auto_increment", "timestamp".I have read somewhere that the 'flags' blob, enum and timestamp are deprecated because theyare not flags but field types. You're descouraged to use them.

Parameter

int	$result
	fieled offset in result beginning with 0.

string	$offset
	the return type, can be 'string', 'array' or 'assoc'.

string	$format
	Warning: documentation is missing.

Returns

mixed

(based on the param $format) see description.

Throws Bs_Exception with BS_DB_ERROR_INVALID_RS on an invalid $result param, Bs_Exception on a field index that doesn't exist or any other error.

hasFieldFlag

public boolean hasFieldFlag( int $result, string $offset, $flag )

Tells whether the field has the given flag. The field is specified by offset in a result id.
This method uses $this->fieldFlags() to get the data. Check it's documentation!

Parameter

int	$result
	fieled offset in result beginning with 0.

string	$offset
	the flag name.

	$flag
	Warning: documentation is missing.

Returns

boolean

Throws Bs_Exception with BS_DB_ERROR_INVALID_RS on an invalid $result param, NULL if the flag is not known. maybe not supported in this mysql version...

fieldType

public string fieldType( int $result, integer $offset )

Get the type of a field at the specified offset from a result id.
This is a wrapper for mysql_field_type().CAUTION: The returned types are not the real mysql field types!!+----------------------------+| mysql type | returned as |+ ------------+--------------+| tinyint | int || smallint | int || mediumint | int || int | int || bigint | int || float | real || double | real || decimal | real || date | date || datetime | datetime || timestamp | timestamp || year | year || char | string || varchar | string || tinyblob | blob || blob | blob || mediumblob | blob || longblob | blob || enum | string || set | string |+----------------------------+

Parameter

int	$result
	fieled offset in result beginning with 0. default is 0.

integer	$offset
	Warning: documentation is missing.

Returns

string

Throws Bs_Exception with BS_DB_ERROR_INVALID_RS on an invalid $result param, Bs_Exception on a field index that doesn't exist or any other error.

listDatabases

public void listDatabases( )

returns a result identifier for the available databases.
note: with mysql you see all db's even if you have no access to them. that may be different in other rdbms.this is a wrapper for mysql_list_dbs().

Returns

void

Throws Bs_Exception if mysql_list_dbs() failed somehow.

listTables

public void listTables( [ string $dbName ] )

Retrieves information about the given table. (does it? is that text correct? --andrej)
This is a wrapper for mysql_list_tables().NOTE: Tables may exist but if you don't have the perms, you won't see them!

Parameter

string	$dbName	`= >>NULL<<`
	. If empty (e.g: '' or NULL or 0) then use the current dbName.

Returns

void

Throws Bs_Exception with BS_DB_ERROR_NEED_MORE_DATA if no $dbName available, Bs_Exception if mysql_list_tables() failed somehow.

listFields

public void listFields( string $tableName, [ string $dbName ] )

Retrieves information about the given tablename.
This is a wrapper for mysql_list_fields().NOTE: Fields may exist but if you don't have the perms, you won't see them!

Warning: documentation is missing.

Parameter

string	$tableName
	the db table name.

string	$dbName	`= >>NULL<<`
	. If empty (e.g: '' or NULL or 0) then use the current dbName.

Returns

void

Throws Bs_Exception with BS_DB_ERROR_NEED_MORE_DATA if no $dbName available, Bs_Exception if mysql_list_fields() failed somehow.

&fetchDatabaseNames

public mixed &fetchDatabaseNames( [ string $format, string $useCache ] )

Return the available db names.
If successfull the data will be cached for further calls.NOTE: You'll see all db's even if you don't have the rights to access them.The returned value is dependent on the $format param. E.g.:'vector' => array(db1, db2, db3)'string' => 'db1, db2, db3'

Parameter

string	$format	`= >>'vector'<<`
	the return type. can be one of 'vector' (default) or 'string'. see description.

string	$useCache	`= >>TRUE<<`
	default is TRUE. if set to true and the method has been executed successfully before, the previous result will be used/returned.

Returns

mixed

(see param $format)

&fetchTableNames

public mixed &fetchTableNames( [ string $dbName, string $format, string $useCache ] )

Return the table names of the given database.
If successfull the data will be cached for further calls.NOTE: Tables may exist but if you don't have the perms, you won't see them!The returned value is dependent on the $format param. E.g.:'vector' => array(table1, table2, table3)'string' => 'table1, table2, table3'

Parameter

string	$dbName	`= >>NULL,<<`
	. If empty (e.g: '' or NULL or 0) then use the current dbName.

string	$format	`= >>'vector'<<`
	the return type. can be one of 'vector' (default) or 'string'. see description.

string	$useCache	`= >>TRUE<<`
	default is TRUE. if set to true and the method has been executed successfully before for this db, the previous result will be used/returned.

Returns

mixed

(see param $format)

Throws Bs_Exception with BS_DB_ERROR_NEED_MORE_DATA if no $dbName available, Bs_Exception otherwise.

&fetchFieldNames

public mixed &fetchFieldNames( string $tblName, [ string $dbName, string $format, string $useCache ] )

Return the field names of the given database table.
If successfull the data will be cached for succeeding calls.NOTE: Fields may exist but if you don't have the perms, you won't see them!The returned value is dependent on the $format param. E.g.:'vector' => array(field1, field2, field3)'string' => 'field1, field2, field3' we are using comma instead of space here becausesome db's allow you to use spaces inside of field names.

Parameter

string	$tblName
	a db table name

string	$dbName	`= >>NULL,<<`
	. If empty (e.g: '' or NULL or 0) then use the current dbName.

string	$format	`= >>'vector'<<`
	the return type. Can be one of 'vector' (default) or 'string'. see description.

string	$useCache	`= >>TRUE<<`
	If TRUE (default) and the method has been executed successfully before for this db table, the previous result will be used/returned.

Returns

mixed

(see param $format)

Throws Bs_Exception with BS_DB_ERROR_NEED_MORE_DATA if no $dbName available, Bs_Exception otherwise.

&getDbStructure

public void &getDbStructure( [ string $dbName, string $useCache ] )

Return a 2-D array of tables and fieldnames found in the given db.
If successfull the data will be cached for succeeding calls.NOTE: Tables and fields may exist but if you don't have the perms, you won't see them!The returned value sample:array( 'table_1' => array('field_1', 'field_2', 'field_3'),'table_2' => array('field_A', 'field_B', 'field_C', 'field_D'));

Parameter

string	$dbName	`= >>NULL,<<`
	. If empty (e.g: '' or NULL or 0) then use the current dbName.

string	$useCache	`= >>TRUE<<`
	If TRUE (default) and the method has been executed successfully before for this db table, the previous result will be returned.

Returns

void

Throws Bs_Exception with BS_DB_ERROR_NEED_MORE_DATA if no $dbName available, Bs_Exception otherwise.

fetchField

public object [unknown] fetchField( int $result, string $offset )

Returns an object containing field information (of the table structure).
This has nothing to do with fetching data from a field, like fetchRow().You might be looking for something like php's mysql_result(), which is notsupported here for reasons mentioned in the header.See comments of unimplemented functions in the head of this file.This is a wrapper for mysql_fetch_field().

Parameter

int	$result
	if not given, the next field (that wasn't yet retrieved) will be used.

string	$offset
	Warning: documentation is missing.

Returns

object [unknown]

Throws Bs_Exception with BS_DB_ERROR_INVALID_RS on an invalid $result param, Bs_Exception if mysql_fetch_field() failed somehow.

fieldExists

public bool fieldExists( mixed $fieldName, string $tableName, [ string $dbName, string $useCache ] )

Tells whether the db field (or fields) exist or not.
NOTE I: Fields are not case sensitive in mysql (dbs and tables are, except on windows).This check is made case sensitive! So if your field is called 'myField',you'll get FALSE for 'myfield'.NOTE II: This method makes use of fetchFieldNames(). So if param $useCache is set to TRUE andfetchFieldNames() has been called before, the cached values will be used.

Parameter

mixed	$fieldName
	a db field name as a string, or an array with db field names.

string	$tableName
	a db table name

string	$dbName	`= >>NULL,<<`
	. If empty (e.g: '' or NULL or 0) then use the current dbName.

string	$useCache	`= >>TRUE<<`
	default is TRUE. see note II.

Returns

bool

true if all fields exist, false if at least one doesn't.

Throws Bs_Exception with BS_DB_ERROR_NEED_MORE_DATA if no $dbName available, Bs_Exception otherwise.

See Also tableExists(), databaseExists()

tableExists

public bool tableExists( mixed $tableName, [ string $dbName, string $useCache ] )

Tells whether the db table (or tables) exist or not.
NOTE I: Databases and tables are file-based in mysql. So they are case sensitive, except on winblows.So for windows we have to do a case insensitive check.NOTE II: This method makes use of fetchTableNames(). So if param $useCache is set to TRUE andfetchTableNames() has been called before, the cached values will be used.

Parameter

mixed	$tableName
	a db table name as a string, or an array with db table names.

string	$dbName	`= >>NULL,<<`
	. If empty (e.g: '' or NULL or 0) then use the current dbName.

string	$useCache	`= >>TRUE<<`
	default is TRUE. see note II.

Returns

bool

true if all tables exist, false if at least one doesn't.

Throws Bs_Exception with BS_DB_ERROR_NEED_MORE_DATA if no $dbName available, Bs_Exception otherwise.

See Also fieldExists(), databaseExists()

databaseExists

public bool databaseExists( mixed $dbName, [ string $useCache ] )

Tells whether the database (or databases) exist or not.
NOTE I: Databases and tables are file-based in mysql. So they are case sensitive, except on winblows.So for windows we have to do a case insensitive check.NOTE II: This method makes use of fetchDatabaseNames(). So if param $useCache is set to TRUE andfetchDatabaseNames() has been called before, the cached values will be used.

Parameter

mixed	$dbName
	a db name as a string, or an array with db names.

string	$useCache	`= >>TRUE<<`
	default is TRUE. see note II.

Returns

bool

true if all dbs exists, false if at least one doesn't.

Throws Bs_Exception

getTableStructure

public array getTableStructure( string $tblName, [ string $dbName ] )

Returns an array with information about the fields of the given db table.
example return value:array('fieldName' => array('type' => 'char','length' => 16 // field length OR if type=='enum' number of enums'enum' => mixed bool FALSE or array('Y','N') // if type=='enum' the enum values as array'default' => mixed //string or int or NULL.'notNull' => bool // true = not null, false = null'primaryKey' => bool'multipleKey' => bool'fulltext' => bool'unique' => bool'unsigned' => bool'zerofill' => bool'binary' => bool'autoIncrement' => bool'foreignKey' => array));'type': field type. possible values are: char, varchar, int, blob, ...'foreignKey': bool FALSE if it's none. hash with these keys if it's one:'db' => null (or string if it's different)'table' => string'multiple' => bool (IDs)

Parameter

string	$tblName
	a table name

string	$dbName	`= >>NULL<<`
	. If empty (e.g: '' or NULL) then use the current dbName.

Returns

array

(hash holding hashes, see above.)

Throws Bs_Exception with BS_DB_ERROR_NEED_MORE_DATA if no $dbName available, Bs_Exception otherwise.

updateTableStructure

public bool updateTableStructure( array $structure, string $tblName, [ string $dbName, integer $shrink ] )

updates an existing db table to the desired structure.
param $shrink:if we should 'shrink' the table in any way.0 = no1 = make fields smaller if needed2 = remove fields if needed3 = remove indexesyour user needs alter (maybe create, index, ...) rights to change the structure.

Parameter

array	$structure
	(like the return value of getTableStructure() so look there.)

string	$tblName
	a table name

string	$dbName	`= >>NULL,<<`
	. If empty (e.g: '' or NULL) then use the current dbName.

integer	$shrink	`= >>0<<`
	(see above.)

Returns

bool

(TRUE if changes have/had to be made, FALSE otherwise.)

Throws bs_exception (on the first error, does not continue.)

getTableProperties

public array getTableProperties( string $tblName, [ string $dbName ] )

!!!!!!!!!!!!!!!!deprecated!!!!!!!!!!!!!!!!!!!!!!
use getTableStructure() it's a better name and returns better/more info.Return an array with information about the fields of the given db table.array('fieldName' => array('Type' => 'char', // field type'Length' => '16' // field leng OR if type=='enum' amount of enums'Enum' => "'Y','N'" // if type=='enum' the enum values as string: "'value','value',..."'Null' => (bool?)'Key' => (bool?)'Default' => 'N''Extra' => string // binary auto_increment zerofill null unsigned));

Parameter

string	$tblName
	a table name

string	$dbName	`= >>NULL<<`
	. If empty (e.g: '' or NULL or 0) then use the current dbName.

Returns

array

(hash, see description.)

Throws Bs_Exception with BS_DB_ERROR_NEED_MORE_DATA if no $dbName available, Bs_Exception otherwise.

getTableLastmod

public void getTableLastmod( string $tblName, [ string $dbName ] )

returns the datetime of the last change to the data of the given db table.
changes are things like insert, update, delete, drop, whatever.uses getTableInfo().

Parameter

string	$tblName
	a db table name

string	$dbName	`= >>NULL<<`
	. If empty (e.g: '' or NULL) then use the current dbName.

Returns

void

Throws Bs_Exception with BS_DB_ERROR_NEED_MORE_DATA if no $dbName available, Bs_Exception otherwise.

See Also getTablesLastmod(), getTableInfo()

getTablesLastmod

public void getTablesLastmod( array $dbs )

this is similar to getTableLastmod(), note the extra s.
it returns the newest datetime of all the given db tables.all db's need to be on the same machine, and you cannot give a dsn for them.add that functionality when you need it. :)this method uses getTableLastmod().param $dbs:it's a vector holding hashes with the keys 'table' and 'db' where table is the table nameand db is the db name. if the db key is not set or empty, the default database is used.example:$dbs = array(array('table'=>'a', 'db'=>'test'),array('table'=>'b'));

Parameter

array	$dbs
	(vector holding hashes, see above)

Returns

void

Throws Bs_Exception with BS_DB_ERROR_NEED_MORE_DATA if no $dbName available, Bs_Exception otherwise. bool FALSE if there were no tables specified.

getTableInfo

public array getTableInfo( string $tblName, [ string $dbName ] )

Parameter

string	$tblName
	a db table name

string	$dbName	`= >>NULL<<`
	. If empty (e.g: '' or NULL) then use the current dbName.

Returns

array

assoc . array see description.

Throws Bs_Exception with BS_DB_ERROR_NEED_MORE_DATA if no $dbName available, Bs_Exception otherwise.

See Also tableHasTransactions(), getTableType()

getTableType

public string getTableType( string $tblName, [ string $dbName ] )

Returns the table type of the specified db table.

Parameter

string	$tblName
	a db table name

string

BlueShoes Application Framework - db

Bs_MySql

public class Bs_MySql extends Bs_Db

Methods inherited from Bs_Db

Methods inherited from Bs_Object

Public Method Summary

Bs_MySql()

autoCommit([ string $on ])

startTransaction([ string $transactionId ])

commit([ string $transactionId ])

rollback([ string $transactionId ])

connect(array $dsn, string $persistent)

disconnect()

selectDb(string $db)

subSelect(string $queryWithSubquery)

&fetchRow(int $result, string $fetchMode)

numCols( $result)

numRows( $result)

affectedRows()

insertId()

freeResult( $result)

fieldName(int $result, $offset)

tableName(int $result, $offset)

tableName2(int $result, $offset)

databaseName(int $result, $offset)

fieldLen(int $result, $offset)

fieldFlags(int $result, string $offset, string $format)

hasFieldFlag(int $result, string $offset, $flag)

fieldType(int $result, integer $offset)

listDatabases()

listTables([ string $dbName ])

listFields(string $tableName, [ string $dbName ])

&fetchDatabaseNames([ string $format, string $useCache ])

&fetchTableNames([ string $dbName, string $format, string $useCache ])

&fetchFieldNames(string $tblName, [ string $dbName, string $format, string $useCache ])

&getDbStructure([ string $dbName, string $useCache ])

fetchField(int $result, string $offset)

fieldExists(mixed $fieldName, string $tableName, [ string $dbName, string $useCache ])

tableExists(mixed $tableName, [ string $dbName, string $useCache ])

databaseExists(mixed $dbName, [ string $useCache ])

getTableStructure(string $tblName, [ string $dbName ])

updateTableStructure(array $structure, string $tblName, [ string $dbName, integer $shrink ])

getTableProperties(string $tblName, [ string $dbName ])

getTableLastmod(string $tblName, [ string $dbName ])

getTablesLastmod(array $dbs)

getTableInfo(string $tblName, [ string $dbName ])

getTableType(string $tblName, [ string $dbName ])

tableHasTransactions(string $tblName, [ string $dbName ])

serverSupportsTableType(string $tableType)

getIniVar([ string $key, string $useCache ])

getStatusVar([ string $key, string $useCache ])

getClientInfo()

getHostInfo()

getProtocolInfo()

getServerInfo()

escapeString(string $query)

setPointer(int $result, $absolutPos)

isValidName(string $string)

isReservedWord(string $word)

nativeErrorCode()

&nativeErrorMsg()

&nativeError()

_dbErrorToBsError(int $dbError)

Private Method Summary

_query(string $query)

_createAlterLine(string $fieldName, array $fieldDef)

getOpenTables([ string $dbName, string $return ])

_mysqlRaiseError([ string $errNo, string $file, string $line, string $weight, string $msg ])

tableInfo( $result, string $mode)

switchUser( $user, $pass)

Fields inherited from Bs_Db

Public Field Summary

$format

Private Field Summary

$_databaseNames

$_tableNames

$_fieldNames

$_iniVars

$_statusVars

$_currentlyOpenTransactionID