FRED™  3.0
FRED™: Framework for Rapid and Easy Development
Db Class Reference

Database access layer (PDO based). More...

Inheritance diagram for Db:
Component Thing

Public Member Functions

 createPdo ($attributes=null)
 
 date ($time=null)
 Convert a Unix timestamp to database date format. More...
 
 dateTime ($time=null)
 Convert a Unix timestamp to database date+time format. More...
 
 isSelection ($sql)
 Check if an SQL statement is a select statement. More...
 
 keyColumn ($table)
 Primary key column. More...
 
 begin ()
 Begin a transaction. More...
 
 rollBack ()
 Roll a transaction back. More...
 
 commit ()
 Commit a transaction. More...
 
 transaction ($callback, $throw=true)
 Wrap a callback function in a transaction. More...
 
 lastInsertId ()
 Get the last auto-incremented ID. More...
 
 statement ($sql, $args=null, $pdo=null)
 Create a PDO statement. More...
 
 execute ($sql, $args=null, $pdo=null)
 Execute an SQL statement. More...
 
 query ($sql, $args=null, $pdo=null)
 Execute an SQL statement. More...
 
 fetch ($statement)
 Fetch a row from an SQL statement. More...
 
 all ($sql, $args=null)
 Return al rows from an SQL statement. More...
 
 single ($sql, $args=null, $auto=true)
 Return a single row from an SQL statement. More...
 
 record ($sql, $args=null)
 Returns an array from an SQL statement. More...
 
 each ($callback, $sql, $args=null)
 Run a callback function for every row in an SQL resultset. More...
 
 multiColumns ($columns)
 Expand an array with scalar values and arrays to multiple arrays. More...
 
 prepareWhere (&$where, &$args=null, $glue='and')
 Prepare a where statement. More...
 
 limit ($sql, $limit, $offset=null)
 Limit an SQL statement. More...
 
 scout ($table, $columns=null, $where=null, $args=null)
 Pre-select records (will be used by select()). More...
 
 insert ($table, $columns, $replace_if_exists=false)
 Insert a record. More...
 
 replace ($table, $columns)
 Replace a record (insert if not exists). More...
 
 select ($table, $columns=' *', $where=null, $args=null, $limit=null, $offset=null)
 Select records. More...
 
 update ($table, $columns, $where=null, $args=null, $insert_if_not_exists=false)
 Update one or more record(s). More...
 
 delete ($table, $where=null, $args=null)
 Delete one or more record(s). More...
 
 exists ($table, $where=null, $args=null)
 Check if a record exists that meets the requirement. More...
 
 count ($table, $where=null, $args=null)
 The number of records that meet the requirements. More...
 
 min ($table, $column, $where=null, $args=null)
 Smallest value from records that meet the requirements. More...
 
 max ($table, $column, $where=null, $args=null)
 Biggest value from records that meet the requirements. More...
 
 average ($table, $column, $where=null, $args=null)
 Average value from records that meet the requirements. More...
 
 sum ($table, $column, $where=null, $args=null)
 Total value from records that meet the requirements. More...
 
 database ()
 Name of the current database. More...
 
 tables ()
 All tables in current database. More...
 
 columns ($table, $database=null)
 Column properties for a table. More...
 
 optimize ($table=null)
 Optimize table(s). More...
 
 explain ($sql, $args=null)
 Explain plan. More...
 
 processes ()
 Running processes. More...
 
 kill ($id)
 Kill a process. More...
 
 __call ($func_name, $params)
 
 __invoke ($sql, $args=null)
 
 __sleep ()
 
- Public Member Functions inherited from Component
 __construct ($fred, $config=null)
 
 __destruct ()
 
 clientConfig ()
 Public configuration. More...
 
 config ($key, $default=null)
 Retrieve a config value. More...
 
 ping ()
 Ping function. More...
 
 filemtime ($filename)
 Filemtime with session cache. More...
 
 component ($name)
 Get a component (local or default). More...
 
 components (... $names)
 Get multiple components in an array. More...
 
- Public Member Functions inherited from Thing
 propertyExists ($property)
 Check if a property exists (public or published). More...
 
 constants ($prefix=null)
 Return all constants. More...
 
 get ($key)
 Get one or more properties. More...
 
 set ($key, $value=null)
 Set one or more properties. More...
 
 __get ($key)
 
 __set ($key, $value)
 

Data Fields

const ACTION_INSERT = 'insert'
 
const ACTION_REPLACE = 'replace'
 
const ACTION_UPDATE = 'update'
 
const ACTION_DELETE = 'delete'
 
const EVENT_OPEN = 'db:open'
 
const EVENT_LOG_CHANGE = 'db:logChange'
 
const EVENT_LOG_ADD_ID = 'db:logAddId'
 
 $multiOperators = ['^' => 'or'
 
 $statTime = 3600
 Operators (key) and glue (value) for multi column where conditions. More...
 
 $statPrefix = 'db'
 
 $logTimes
 Query time above which to add a note to the log (key = prio, value = edge; higher times first). More...
 
 $logTimeSignificant = 3
 Number of significant figures (rounding). More...
 
 $logTables = []
 Tables (key) and key columns (value) to log changes for. More...
 
 $defTables = null
 Table definition to use (table name). More...
 
 $migrateClassName = __CLASS__ . '\\Migrate'
 Class name for the migration tool. More...
 
 $queryCount = 0
 Number of queries executed. More...
 
 $queryTime = 0
 Total time spent on queries (seconds). More...
 
 $allowInsert = true
 Allow inserts (for secondary processes like logs). More...
 
- Data Fields inherited from Component
 $filemtimeTtl = 0
 
- Data Fields inherited from Thing
const HIDDEN = 0
 Property is hidden. More...
 
const READABLE = 1
 Property is readable. More...
 
const WRITEABLE = 2
 Property is writeable. More...
 
const READWRITE = 3
 Property is readable and writeable. More...
 

Protected Member Functions

 startTimer ()
 
 checkTimer ($sql, $args)
 
 prepareArgs (&$sql, &$args)
 
 logChange ($table, $action, $key, $old, $new)
 Log a change. More...
 
 logAddId ($id)
 Add a auto-increment ID to the latest change log. More...
 
 logChanges ($table, $action, $columns, $where, $args=null)
 Log the changes for an action. More...
 
 aggregate ($table, $function, $where=null, $args=null)
 
 getMigrate ()
 
 getPdo ()
 
 getVersion ()
 
- Protected Member Functions inherited from Component
 init ()
 
 done ()
 
 getSession ()
 
- Protected Member Functions inherited from Thing
 publish ($property, $visibility=self::READABLE)
 Publish a property (or hide it again). More...
 
 configure ($config)
 Configure the object. More...
 
 _get ($key)
 Default getter if no specific setter is defined, and the property is also not published (readable). More...
 
 _set ($key, $value)
 Default setter if no specific setter is defined, and the property is also not published (writeable). More...
 

Protected Attributes

 $_connection = []
 
 $_attributes = []
 
 $_version = null
 
 $_pdo = null
 
 $_migrate = null
 
 $_startTime = null
 
 $_duploCount = []
 
 $_logAddId = false
 
 $_scout = []
 
- Protected Attributes inherited from Component
 $_fred = null
 
 $_config = null
 
 $_name = null
 
 $_components = []
 Local components (key = component name, value = component). More...
 
 $_session = null
 
- Protected Attributes inherited from Thing
 $_published = []
 Published properties (key = name of property, value = visibility). More...
 

Detailed Description

Database access layer (PDO based).

Definition at line 8 of file Db.php.

Member Function Documentation

◆ __call()

__call (   $func_name,
  $params 
)

Definition at line 805 of file Db.php.

◆ __invoke()

__invoke (   $sql,
  $args = null 
)

Definition at line 828 of file Db.php.

◆ __sleep()

__sleep ( )

Definition at line 832 of file Db.php.

◆ aggregate()

aggregate (   $table,
  $function,
  $where = null,
  $args = null 
)
protected

Definition at line 626 of file Db.php.

◆ all()

all (   $sql,
  $args = null 
)

Return al rows from an SQL statement.

Parameters
string$sqlSQL statement to execute.
array$argsVariables to bind to the statement.
Returns
array All rows (false on failure).

Definition at line 244 of file Db.php.

◆ average()

average (   $table,
  $column,
  $where = null,
  $args = null 
)

Average value from records that meet the requirements.

Parameters
string$tableTable name.
string$columnColumn to get value from.
string | array$whereWhere.
string | array$argsArguments (assoc.array) or extra statement for the where.
Returns
mixed Average value.

Definition at line 672 of file Db.php.

◆ begin()

begin ( )

Begin a transaction.

Definition at line 95 of file Db.php.

◆ checkTimer()

checkTimer (   $sql,
  $args 
)
protected

Definition at line 141 of file Db.php.

◆ columns()

columns (   $table,
  $database = null 
)

Column properties for a table.

Parameters
string$tableName of the table.
string$databaseName of the database (current database when empty).
Returns
array Key = column name, value = assoc.array with properties.

Definition at line 706 of file Db.php.

◆ commit()

commit ( )

Commit a transaction.

Definition at line 107 of file Db.php.

◆ count()

count (   $table,
  $where = null,
  $args = null 
)

The number of records that meet the requirements.

Parameters
string$tableTable name.
string | array$whereWhere.
string | array$argsArguments (assoc.array) or extra statement for the where.
Returns
int Number of rows.

Definition at line 639 of file Db.php.

◆ createPdo()

createPdo (   $attributes = null)

Definition at line 47 of file Db.php.

◆ database()

database ( )

Name of the current database.

Returns
string

Definition at line 690 of file Db.php.

◆ date()

date (   $time = null)

Convert a Unix timestamp to database date format.

Parameters
int$timeTimestamp (empty = now).
Returns
string Date as a string.

Definition at line 65 of file Db.php.

◆ dateTime()

dateTime (   $time = null)

Convert a Unix timestamp to database date+time format.

Parameters
int$timeTimestamp (empty = now).
Returns
string Date+time as a string.

Definition at line 73 of file Db.php.

◆ delete()

delete (   $table,
  $where = null,
  $args = null 
)

Delete one or more record(s).

Parameters
string$tableTable name.
string | array$whereWhere.
string | array$argsArguments (assoc.array) or extra statement for the where.
Returns
int The number of affected rows (0 = failure, 1 or more = success).

Definition at line 606 of file Db.php.

◆ each()

each (   $callback,
  $sql,
  $args = null 
)

Run a callback function for every row in an SQL resultset.

A seperate conection is used without buffering. This makes it possible to process huge datasets one record at a time, without running out of memory.

Parameters
callable$callbackCallback function (first and only parameter is the row). If the function returns explicitly false the execution of further rows is halted.
string$sqlSQL statement to execute.
array$argsVariables to bind to the statement.
Returns
int Number of records processed (false on error).

Definition at line 307 of file Db.php.

◆ execute()

execute (   $sql,
  $args = null,
  $pdo = null 
)

Execute an SQL statement.

Parameters
string$sqlSQL statement to execute.
array$argsVariables to bind to the statement.
PDO$pdoSpecific PDO connection to use.
Returns
int The number of affected rows.

Definition at line 198 of file Db.php.

◆ exists()

exists (   $table,
  $where = null,
  $args = null 
)

Check if a record exists that meets the requirement.

Parameters
string$tableTable name.
string | array$whereWhere.
string | array$argsArguments (assoc.array) or extra statement for the where.
Returns
bool True if a record exists.

Definition at line 620 of file Db.php.

◆ explain()

explain (   $sql,
  $args = null 
)

Explain plan.

Parameters
string$sqlSQL statement to explain.
array$argsVariables to bind to the statement.
Returns
array Steps in the plan.

Definition at line 754 of file Db.php.

◆ fetch()

fetch (   $statement)

Fetch a row from an SQL statement.

Parameters
PDOStatement$statement
Returns
array

Definition at line 234 of file Db.php.

◆ getMigrate()

getMigrate ( )
protected

Definition at line 786 of file Db.php.

◆ getPdo()

getPdo ( )
protected

Definition at line 794 of file Db.php.

◆ getVersion()

getVersion ( )
protected

Definition at line 799 of file Db.php.

◆ insert()

insert (   $table,
  $columns,
  $replace_if_exists = false 
)

Insert a record.

Parameters
string$tableTable name.
array$columnsColumns (key = column name, value = value or values - for multiple records).
bool$replace_if_existsIf true, an existing record will be replaced (if alse, an exception will be thrown when a record with the same key already exists).
Returns
int The number of affected rows (0 = failure, 1 = success).

Definition at line 505 of file Db.php.

◆ isSelection()

isSelection (   $sql)

Check if an SQL statement is a select statement.

Parameters
string$sqlSQL statement.
Returns
bool True if it is a select statement.

Definition at line 81 of file Db.php.

◆ keyColumn()

keyColumn (   $table)

Primary key column.

Parameters
string$tableFor this table.
Returns
string Column name.

Definition at line 89 of file Db.php.

◆ kill()

kill (   $id)

Kill a process.

Parameters
int$idProcess ID.
Returns
bool True on success.

Definition at line 775 of file Db.php.

◆ lastInsertId()

lastInsertId ( )

Get the last auto-incremented ID.

Returns
string

Definition at line 133 of file Db.php.

◆ limit()

limit (   $sql,
  $limit,
  $offset = null 
)

Limit an SQL statement.

Parameters
string$sqlOriginal SQL statement.
int$limitLimit (number of rows).
int$offsetOffset.
Returns
string SQL statement with limit.

Definition at line 425 of file Db.php.

◆ logAddId()

logAddId (   $id)
protected

Add a auto-increment ID to the latest change log.

Parameters
string$idThe last auto-incremented ID (the key is stored in $this->_logAddId).

Definition at line 443 of file Db.php.

◆ logChange()

logChange (   $table,
  $action,
  $key,
  $old,
  $new 
)
protected

Log a change.

Parameters
string$tableTable name.
string$actionChange type (see ACTION_* constants).
array$keyKey of the changed record (key = column name, value = value).
array$oldOriginal values for changed columns (key = column name, value = value).
array$newNew values for changed columns (key = column name, value = value).

Definition at line 436 of file Db.php.

◆ logChanges()

logChanges (   $table,
  $action,
  $columns,
  $where,
  $args = null 
)
protected

Log the changes for an action.

Parameters
string$tableTable name.
string$actionChange type (see ACTION_* constants).
array$columnsNew values (key = column name, value = value).
string | array$whereWhere.
string | array$argsArguments (assoc.array) or extra statement for the where.

Definition at line 454 of file Db.php.

◆ max()

max (   $table,
  $column,
  $where = null,
  $args = null 
)

Biggest value from records that meet the requirements.

Parameters
string$tableTable name.
string$columnColumn to get value from.
string | array$whereWhere.
string | array$argsArguments (assoc.array) or extra statement for the where.
Returns
mixed Biggest value.

Definition at line 661 of file Db.php.

◆ min()

min (   $table,
  $column,
  $where = null,
  $args = null 
)

Smallest value from records that meet the requirements.

Parameters
string$tableTable name.
string$columnColumn to get value from.
string | array$whereWhere.
string | array$argsArguments (assoc.array) or extra statement for the where.
Returns
mixed Smallest value.

Definition at line 650 of file Db.php.

◆ multiColumns()

multiColumns (   $columns)

Expand an array with scalar values and arrays to multiple arrays.

Parameters
array$columnsColumns (key = column name, value = array with values or single scalar value).
Returns
array Array of column arrays (key = column name, value = value).

Definition at line 326 of file Db.php.

◆ optimize()

optimize (   $table = null)

Optimize table(s).

Parameters
string$tableTable to optimize (empty for all tables).

Definition at line 741 of file Db.php.

◆ prepareArgs()

prepareArgs ( $sql,
$args 
)
protected

Definition at line 161 of file Db.php.

◆ prepareWhere()

prepareWhere ( $where,
$args = null,
  $glue = 'and' 
)

Prepare a where statement.

Parameters
string | array$whereIf this a string, it is used directly as the where clause. If it is an assoc.array, it is translated to a where statement. Key => value pairs are translated as follow:
  • 'key' => 'value' : "key = 'value'" (or "key is null" when the value is null, or "key in (...)" when the value is an array).
  • 'key<>' => 'value' : "key <> 'value'" (or "key is not null" when the value is null, or "key not in (...)" when the value is an array).
  • 'key~' => 'value' : "key like 'value'"
  • 'key^' => [key1 => value1,key2 => value2] : "(key1 = value1 or key2 = value2)" (same rules as above apply for the 'or' part).
string | array$argsIf the $where is a string, and this is also, this value is added to the where. Otherwise the arguments resulting from the translation will be added to this array.
string$glueGlue to combine seperate arguments.

Definition at line 355 of file Db.php.

◆ processes()

processes ( )

Running processes.

Returns
array Key = process ID, value = array with info.

Definition at line 761 of file Db.php.

◆ query()

query (   $sql,
  $args = null,
  $pdo = null 
)

Execute an SQL statement.

Parameters
string$sqlSQL statement to execute.
array$argsVariables to bind to the statement.
PDO$pdoSpecific PDO connection to use.
Returns
PDOStatement (false on failure).

Definition at line 220 of file Db.php.

◆ record()

record (   $sql,
  $args = null 
)

Returns an array from an SQL statement.

If the query returns only one column, the result is an array of those values. With two columns, the first one becomes the key, and the second one the value of an assoc.array. With three or more columns the first column will be the key, and the others the value.

Parameters
string$sqlSQL statement to execute.
array$argsVariables to bind to the statement.
Returns
array

Definition at line 281 of file Db.php.

◆ replace()

replace (   $table,
  $columns 
)

Replace a record (insert if not exists).

Parameters
string$tableTable name.
array$columnsColumns (key = column name, value = value or values - for multiple records).
Returns
int The number of affected rows (0 = failure, 1 = success).

Definition at line 538 of file Db.php.

◆ rollBack()

rollBack ( )

Roll a transaction back.

Definition at line 101 of file Db.php.

◆ scout()

scout (   $table,
  $columns = null,
  $where = null,
  $args = null 
)

Pre-select records (will be used by select()).

Parameters
string$tableTable name.
array$columnsColumns to select from the table. Leaving this empty will clear the cache for this table.
string | array$whereWhere.
string | array$argsArguments (assoc.array) or extra statement for the where.
Returns
array Selected rows (null on clear).

Definition at line 487 of file Db.php.

◆ select()

select (   $table,
  $columns = '*',
  $where = null,
  $args = null,
  $limit = null,
  $offset = null 
)

Select records.

Parameters
string$tableTable name.
array$columnsColumns to select from the table. Prefix the first columns with a '+' to make this the key.
string | array$whereWhere.
string | array$argsArguments (assoc.array) or extra statement for the where.
int | bool$limitLimit the number of rows (0 = no limit).
int$offsetOffset.
Returns
array Single value when limit is true, single row when limit is 1, otherwise an array of records.
See also
prepareWhere()

Definition at line 552 of file Db.php.

◆ single()

single (   $sql,
  $args = null,
  $auto = true 
)

Return a single row from an SQL statement.

Returns a single value if the resulting row has only one column.

Parameters
string$sqlSQL statement to execute.
array$argsVariables to bind to the statement.
bool$autoSet to false to always return a row.
Returns
mixed Row (multi column) or value (single column).

Definition at line 263 of file Db.php.

◆ startTimer()

startTimer ( )
protected

Definition at line 137 of file Db.php.

◆ statement()

statement (   $sql,
  $args = null,
  $pdo = null 
)

Create a PDO statement.

Parameters
string$sqlSQL statement to execute.
array$argsVariables to bind to the statement.
PDO$pdoSpecific PDO connection to use.
Returns
PDOStatement (false on failure).

Definition at line 180 of file Db.php.

◆ sum()

sum (   $table,
  $column,
  $where = null,
  $args = null 
)

Total value from records that meet the requirements.

Parameters
string$tableTable name.
string$columnColumn to get value from.
string | array$whereWhere.
string | array$argsArguments (assoc.array) or extra statement for the where.
Returns
mixed Total value.

Definition at line 683 of file Db.php.

◆ tables()

tables ( )

All tables in current database.

Returns
array

Definition at line 697 of file Db.php.

◆ transaction()

transaction (   $callback,
  $throw = true 
)

Wrap a callback function in a transaction.

Parameters
callable$callbackCallback function (first and only parameter is this instance).
bool$throwIf true, an exception is re-thrown after the rollback.
Returns
bool True on success, false on error (and $throw set to false).

Definition at line 116 of file Db.php.

◆ update()

update (   $table,
  $columns,
  $where = null,
  $args = null,
  $insert_if_not_exists = false 
)

Update one or more record(s).

Parameters
string$tableTable name.
array$columnsColumns to update (key = column name, value = new value).
string | array$whereWhere.
string | array$argsArguments (assoc.array) or extra statement for the where.
bool$insert_if_not_existsIf true, a new record will be inserted when there is not yet an existing record.
Returns
int The number of affected rows (0 = failure, 1 or more = success).

Definition at line 582 of file Db.php.

Field Documentation

◆ $_attributes

$_attributes = []
protected

Definition at line 37 of file Db.php.

◆ $_connection

$_connection = []
protected

Definition at line 36 of file Db.php.

◆ $_duploCount

$_duploCount = []
protected

Definition at line 43 of file Db.php.

◆ $_logAddId

$_logAddId = false
protected

Definition at line 44 of file Db.php.

◆ $_migrate

$_migrate = null
protected

Definition at line 40 of file Db.php.

◆ $_pdo

$_pdo = null
protected

Definition at line 39 of file Db.php.

◆ $_scout

$_scout = []
protected

Definition at line 45 of file Db.php.

◆ $_startTime

$_startTime = null
protected

Definition at line 42 of file Db.php.

◆ $_version

$_version = null
protected

Definition at line 38 of file Db.php.

◆ $allowInsert

$allowInsert = true

Allow inserts (for secondary processes like logs).

Definition at line 34 of file Db.php.

◆ $defTables

$defTables = null

Table definition to use (table name).

Definition at line 29 of file Db.php.

◆ $logTables

$logTables = []

Tables (key) and key columns (value) to log changes for.

Definition at line 28 of file Db.php.

◆ $logTimes

$logTimes
Initial value:
= [
Log::CRITICAL => 10.0

Query time above which to add a note to the log (key = prio, value = edge; higher times first).

Definition at line 22 of file Db.php.

◆ $logTimeSignificant

$logTimeSignificant = 3

Number of significant figures (rounding).

Definition at line 27 of file Db.php.

◆ $migrateClassName

$migrateClassName = __CLASS__ . '\\Migrate'

Class name for the migration tool.

Definition at line 30 of file Db.php.

◆ $multiOperators

$multiOperators = ['^' => 'or'

Definition at line 19 of file Db.php.

◆ $queryCount

$queryCount = 0

Number of queries executed.

Definition at line 32 of file Db.php.

◆ $queryTime

$queryTime = 0

Total time spent on queries (seconds).

Definition at line 33 of file Db.php.

◆ $statPrefix

$statPrefix = 'db'

Definition at line 21 of file Db.php.

◆ $statTime

$statTime = 3600

Operators (key) and glue (value) for multi column where conditions.

Query time above which to increment a stat counter for the specific query (id = SQL).

Definition at line 20 of file Db.php.

◆ ACTION_DELETE

const ACTION_DELETE = 'delete'

Definition at line 13 of file Db.php.

◆ ACTION_INSERT

const ACTION_INSERT = 'insert'

Definition at line 10 of file Db.php.

◆ ACTION_REPLACE

const ACTION_REPLACE = 'replace'

Definition at line 11 of file Db.php.

◆ ACTION_UPDATE

const ACTION_UPDATE = 'update'

Definition at line 12 of file Db.php.

◆ EVENT_LOG_ADD_ID

const EVENT_LOG_ADD_ID = 'db:logAddId'

Definition at line 17 of file Db.php.

◆ EVENT_LOG_CHANGE

const EVENT_LOG_CHANGE = 'db:logChange'

Definition at line 16 of file Db.php.

◆ EVENT_OPEN

const EVENT_OPEN = 'db:open'

Definition at line 15 of file Db.php.


The documentation for this class was generated from the following file: