PHPDBEditTk -

dal

Provides a high level database abstraction layer

private class dal

Provides a high level database abstraction layer
Provides functions to operate on a database system on twodifferent abstraction layers:

Public Method Summary
void	dal() db_connect constructor function
mixed	runsql(string $table, string $sql) Sends an SQL string to the database.
array	getnextrow(string $table) Fetches the next row from a resultset.
string	insert(string $table, array &$row) Inserts a record into a table based on database definitions.
string	update(string $table, array &$row) Updates a record into a table based on database definitions.
string	delete(string $table, string $key) Deletes a record from a table based on database definitions.
string	count(string $table, [ string $appendwhere, integer $use_select_view ]) Runs a count(*) statement on a table.
string	pack_field_value(string $table, string $fieldname, string $val) Pack a field into quotes or not.
string	pack_field_name(string $table, string $usedtblname, string $fieldname) Pack a fieldname.
string	assemble_where(string $table, string $op, array $val) Assemble a where statement
array	build_column_list(string $table) Runs a select statement on a single table or view.
array	fetch(string $table, [ string $lpk ]) Runs a simplified select statement on a single table or view.
array	ns_fetch_restricted(string $table, string $root_id, [ string $where, integer $use_select_view ]) Runs a nested set query on a table
void	bot(string $table) Starts a transaction
void	commit(string $table) Commits a transaction
void	rollback(string $table) Rolls back a transaction
void	block_sql_inject(mixed &$mixed) Blocks SQL injection attacks
void	debug_notice(string $sql, string $type) Handles printing of SQL statements for debugging

Private Method Summary
void	_open_db(string $table) Opens a connection to the database. Currently a persistent connection is always used.
string	_db_quoter(string $table, string $to_quote) Does the appropriate quoting for the database.
bool	field_include(string $ignore, string $use_select_view, string $is_select_viewonly) Check, if field should be included.
void	error(string $type, string $msg) Handles the error
void	update_with_key( $table, &$row, $currkey) Warning: documentation is missing.
void	build_limit_statement( $table, $use_table_name, $appendwhere, $order, $limitstart, $limitpgsize, $use_select_view) Warning: documentation is missing.
void	fetch_restricted( $table, $appendwhere, string $order, string $limitstart, string $limitpgsize, integer $use_select_view) Warning: documentation is missing.

Private Field Summary
mixed	$glink Stores the current database connection
mixed	$lsqlres Stores the last result resource id
array	$dbdef Stores the database definitions array
string	$tbl_prefix Stores a table prefix.
string	$error Error flag.
unknown	$access_roles
unknown	$dblinks

Public Method Details

dal

public void dal( )

db_connect constructor function
stores all necessary information in local variables

Returns

void

runsql

public mixed runsql( string $table, string $sql )

Sends an SQL string to the database.

Parameter

string	$table
	Specifies table to run the query on. This is used to specify the connection data

string	$sql
	SQL string

Returns

mixed

The resource id of the sql or the return value of any sql statement

getnextrow

public array getnextrow( string $table )

Fetches the next row from a resultset.
The Result identifier is read from the table definitions, where ithas been stored by runsql.

Parameter

string	$table
	Table to use. This is used to read the result identifier

Returns

array

One row of the resultset with all fields

insert

public string insert( string $table, array &$row )

Inserts a record into a table based on database definitions.
Builds an SQL INSERT string from the database definition known to theclass and fills the values from an associative array. Values can bedefined in the db definitions (fields->field->datatype) asint: will be casted to int
dec: will be casted to float
else: will be quoted and put in quotes.
A value 'NULL' will be inserted as NULL.

Also supports a creation timestamp which is prepared by the functionts_srv2utc.

Also supports dbignore (fields->field->dbignore => "yes"), whichlets the function ignore the field.

Returns a possible new autonumber entry.

Parameter

string	$table
	Tablename (as defined in db definition)

array	&$row
	@$row Associative array with the field names as keys and the array-values as values to be inserted

Returns

string

The quoted string

update

public string update( string $table, array &$row )

Updates a record into a table based on database definitions.
Builds an SQL UPDATE string from the database definition known to theclass and fills the values from an associative array. Values can bedefined in the db definitions (fields->field->datatype) asint: will be casted to int
dec: will be casted to float
else: will be quoted and put in quotes.
A value 'NULL' will be inserted as NULL.

Fields can also be filled with an array with one value, which willbe directly inserted into the update string. This can be used forthings like
<pre>update x set y = y + 2</pre>Also supports an update timestamp which is prepared by the functionts_srv2utc.

Also supports dbignore (fields->field->dbignore => "yes"), whichlets the function ignore the field.

Returns the number of rows affected.

Parameter

string	$table
	(as defined in db definition)

array	&$row
	array with the field names as keys and the array-values as values to be inserted

Returns

string

The quoted string

delete

public string delete( string $table, string $key )

Deletes a record from a table based on database definitions.
Deletes a record from a table by using the primary key specified inthe db definitions. If the key is specified as a combined key theparameter must be an ass. array with the key fields as keys and thekey values as array values.

Returns the number of rows affected.

Parameter

string	$table
	Tablename (as defined in db definition)

string	$key
	/array $key value of the primary key or associative array with the values of the primary key fields to be deleted.

Returns

string

Number of rows affected

count

public string count( string $table, [ string $appendwhere, integer $use_select_view ] )

Runs a count(*) statement on a table.
Runs a select count(*) from table statement. There is an optionalargument for a where clause.

Parameter

string	$table
	Tablename (as defined in db definition)

string	$appendwhere	`= >>""<<`
	a where clause which reduces the counted resultset.

integer	$use_select_view	`= >>0<<`
	1, if the alternative select table should be used, instead of the normal table.

Returns

string

The number of rows counted, including NULLs

pack_field_value

public string pack_field_value( string $table, string $fieldname, string $val )

Pack a field into quotes or not.
Checks for the data type of a field and packs it in quotes if applicable.Also, looks if the value of sqlpackage has been set and packs the field with it.

Parameter

string	$table
	Table to use

string	$fieldname
	Name of the field

string	$val
	Value of the field

Returns

string

pack_field_name

public string pack_field_name( string $table, string $usedtblname, string $fieldname )

Pack a fieldname.
Checks for a table prefix and adds it to the field. Uses the table name to preceedthe field.Also, looks if the value of sqlpackage has been set and packs the field with it.

Parameter

string	$table
	Table to use

string	$usedtblname
	Effective tablename to preceed the field

string	$fieldname
	Name of the field

Returns

string

assemble_where

public string assemble_where( string $table, string $op, array $val )

Assemble a where statement
Assembles a where statement for a list of fields

Parameter

string	$table
	Table to use

string	$op
	String, that represents the operator

array	$val
	Value of each field. This is an associative array that uses the field names as a key.

Returns

string

the SQL where string

build_column_list

public array build_column_list( string $table )

Runs a select statement on a single table or view.
Runs a select statement against a database table or view. Analternative view can be specified for fetch_restricted only.At leastthe where part must be specified. Optionally, an order by clause canbe specified.Additionally supports a limit-statement with SQL Server which isbased on a three times nested subquery. This is very slow with largeresultsets - use a selective where statement.

Also supports dbignore (fields->field->dbignore => "yes"), whichlets the function ignore the field.

Parameter

string	$table
	Tablename (as defined in db definition)

Returns

array

(ass.array) The resultset as an array of associative arrays.

fetch

public array fetch( string $table, [ string $lpk ] )

Runs a simplified select statement on a single table or view.
Runs a select statement against a database table or view. If nothingis specified there is no where clause used.

Also the order by clause is filled from the "sort_order" fieldin the database definitions. This has to be an array of fieldnames to specify the order. Specify "fieldname desc" for descendingorder.Also supports dbignore (fields->field->dbignore => "yes"), whichlets the function ignore the field.

Internally calls fetch_restricted()

Parameter

string	$table
	Tablename (as defined in db definition)

string	$lpk	`= >>""<<`
	/array $lpk a primary key value to specify one row in the resultset

Returns

array

/array(ass.array) The resultset as an array of associative arrays (for multiple rows) or as an associative array (for one row).

ns_fetch_restricted

public array ns_fetch_restricted( string $table, string $root_id, [ string $where, integer $use_select_view ] )

Runs a nested set query on a table
The nested set table must be based on numberic fields definingthe nesting and a root id, which can be of any type. The fieldsare defined by the definitions "nsl", "nsr", "nsrid"The function will deliver the whole tree, but further where conditionscan be defined with the where clause. It might probably be best to usethe Alias table "ns2" of the self reference to define any restrictionson the resultset.Also supports dbignore (fields->field->dbignore => "yes"), whichlets the function ignore the field.

Internally calls fetch_restricted()

Parameter

string	$table
	Tablename (as defined in db definition)

string	$root_id
	The root id of the tree.

string	$where	`= >>""<<`
	where-clause to restrict recordset

integer	$use_select_view	`= >>0<<`
	1, if the alternative select table should be used, instead of the normal table.

Returns

array

/array(ass.array) The resultset as an array of associative arrays (for multiple rows) or as an associative array (for one row).

bot

public void bot( string $table )

Starts a transaction
This is only a simple transaction handling yet.

Parameter

string	$table
	Table to run the transaction on. Used for connection data.

Returns

void

commit

public void commit( string $table )

Commits a transaction
This is only a simple transaction handling yet.

Parameter

string	$table
	Table to run the transaction on. Used for connection data.

Returns

void

rollback

public void rollback( string $table )

Rolls back a transaction
This is only a simple transaction handling yet.

Parameter

string	$table
	Table to run the transaction on. Used for connection data.

Returns

void

block_sql_inject

public void block_sql_inject( mixed &$mixed )

Blocks SQL injection attacks
This is done (primitively) by cutting of all date from the parameter, which isafter the first ";". Don't use it on data, which contains an ;. The function isapplied recursively on all elements of an array, normal or associative.

Parameter

mixed	&$mixed
	parameter to secure

Returns

void

debug_notice

public void debug_notice( string $sql, string $type )

Handles printing of SQL statements for debugging
Depending on several constants, this is used internally by theselect, insert, update, count, delete functions. It can also be used toadd sql debugging for routines that build their sql statements themselves.In can then be switched on or off by the appropriate constants

Parameter

string	$sql
	SQL string to print out

string	$type
	Type to check the constants for. Can be select, update, delete, insert or default value is used

Returns

void

Private Method Details

_open_db

private void _open_db( string $table )

Opens a connection to the database. Currently a persistent connection is always used.

Parameter

string	$table
	Tablename to open the db for. This is needed to determine, if a link is already open and what the connection data is

Returns

void

_db_quoter

private string _db_quoter( string $table, string $to_quote )

Does the appropriate quoting for the database.
This is capsuled here to allow an easy transition between databasesand their quoting

Parameter

string	$table
	Table to use this for. This determines - based on the database type, which quoting algorithm is used

string	$to_quote
	Parameter to be quoted

Returns

string

The quoted string

field_include

private bool field_include( string $ignore, string $use_select_view, string $is_select_viewonly )

Check, if field should be included.
Packages the functionality, if a field should be included in the query.At the moment this depends on the type of the query and if "dbselect" and/or"dbignore" are set for the field.

Parameter

string	$ignore
	"yes", if the field contains the dbignore setting

string	$use_select_view
	, true, if the caller wants to use the possibly different read-only view.

string	$is_select_viewonly
	"yes", if the field contains the dbselect setting

Returns

bool

true , if it should be included

error

private void error( string $type, string $msg )

Handles the error

Parameter

string	$type
	error type (unused)

string	$msg
	error message

Returns

void

update_with_key

private void update_with_key( $table, &$row, $currkey )

Warning: documentation is missing.

Parameter

	$table
	Warning: documentation is missing.

	&$row
	Warning: documentation is missing.

	$currkey
	Warning: documentation is missing.

Returns

void

build_limit_statement

private void build_limit_statement( $table, $use_table_name, $appendwhere, $order, $limitstart, $limitpgsize, $use_select_view )

Warning: documentation is missing.

Parameter

	$table
	Warning: documentation is missing.

	$use_table_name
	Warning: documentation is missing.

	$appendwhere
	Warning: documentation is missing.

	$order
	Warning: documentation is missing.

	$limitstart
	Warning: documentation is missing.

	$limitpgsize
	Warning: documentation is missing.

	$use_select_view
	Warning: documentation is missing.

Returns

void

fetch_restricted

private void fetch_restricted( $table, $appendwhere, string $order, string $limitstart, string $limitpgsize, integer $use_select_view )

Warning: documentation is missing.

Parameter

	$table
	Warning: documentation is missing.

	$appendwhere
	Warning: documentation is missing.

string	$order
	Warning: documentation is missing.

string	$limitstart
	Warning: documentation is missing.

string	$limitpgsize
	Warning: documentation is missing.

integer	$use_select_view
	Warning: documentation is missing.

Returns

void

Private Field Details
$glink
private mixed $glink >><< Stores the current database connection There is only one database connection at a time in the db_connectobject.
See Also	_open_db(), runsql()

$lsqlres
private mixed $lsqlres >><< Stores the last result resource id This is used when fetching resultsets row by row.
See Also	runsql(), getnextrow()

$dbdef
private array $dbdef >><< Stores the database definitions array Defines the tables and their fields, primary keys, standardsort orders etc...

$tbl_prefix
private string $tbl_prefix >><< Stores a table prefix. Defines a prefix which is added to all table names. This isespecially useful, when moving tables to a database, where the sametable names already exist.

$error
private string $error >><< Error flag. This flag is set to 1, if an error occured in runsql(). Otherwiseit is 0
See Also	runsql()

$access_roles
private unknown $access_roles >><<

$dblinks
private unknown $dblinks >><<

PHPDoc 1.0beta

PHPDBEditTk -

dal

private class dal

Public Method Summary

dal()

runsql(string $table, string $sql)

getnextrow(string $table)

insert(string $table, array &$row)

update(string $table, array &$row)

delete(string $table, string $key)

count(string $table, [ string $appendwhere, integer $use_select_view ])

pack_field_value(string $table, string $fieldname, string $val)

pack_field_name(string $table, string $usedtblname, string $fieldname)

assemble_where(string $table, string $op, array $val)

build_column_list(string $table)

fetch(string $table, [ string $lpk ])

ns_fetch_restricted(string $table, string $root_id, [ string $where, integer $use_select_view ])

bot(string $table)

commit(string $table)

rollback(string $table)

block_sql_inject(mixed &$mixed)

debug_notice(string $sql, string $type)

Private Method Summary

_open_db(string $table)

_db_quoter(string $table, string $to_quote)

field_include(string $ignore, string $use_select_view, string $is_select_viewonly)

error(string $type, string $msg)

update_with_key( $table, &$row, $currkey)

build_limit_statement( $table, $use_table_name, $appendwhere, $order, $limitstart, $limitpgsize, $use_select_view)

fetch_restricted( $table, $appendwhere, string $order, string $limitstart, string $limitpgsize, integer $use_select_view)

Private Field Summary

$glink

$lsqlres

$dbdef

$tbl_prefix

$error

$access_roles

$dblinks

Public Method Details

dal

runsql

getnextrow

insert

update

delete

count

pack_field_value

pack_field_name

assemble_where

build_column_list

fetch

ns_fetch_restricted

bot

commit

rollback

block_sql_inject

debug_notice

Private Method Details

_open_db

_db_quoter

field_include

error

update_with_key

build_limit_statement

fetch_restricted

Private Field Details

$glink

$lsqlres

$dbdef

$tbl_prefix

$error

$access_roles

$dblinks