API Reference

Db

Piwik\

Db

Contains SQL related helper functions for Matomo (formerly Piwik)'s MySQL database.

Plugins should always use this class to execute SQL against the database.

Examples

$rows = Db::fetchAll("SELECT col1, col2 FROM mytable WHERE thing = ?", array('thingvalue'));
foreach ($rows as $row) {
    doSomething($row['col1'], $row['col2']);
}

$value = Db::fetchOne("SELECT MAX(col1) FROM mytable");
doSomethingElse($value);

Db::query("DELETE FROM mytable WHERE id < ?", array(23));

Properties

This class defines the following properties:

$lockPrivilegeGranted

Cached result of isLockprivilegeGranted function.

Public so tests can simulate the situation where the lock tables privilege isn't granted.

Signature

  • It is a bool value.

Methods

The class defines the following methods:

get()

Returns the database connection and creates it if it hasn't been already.

Signature

  • Returns: Piwik\Tracker\Db|Piwik\Db\AdapterInterface|Db —

getReader()

Since Matomo Matomo

Returns the database connection and creates it if it hasn't been already. Make sure to not write any data on the reader and only use the connection to read data.

Signature

  • Returns: Piwik\Tracker\Db|Piwik\Db\AdapterInterface|Db —

getDatabaseConfig()

Returns an array with the Database connection information.

Signature

  • It accepts the following parameter(s):

    • $dbConfig (array|null) —

  • It returns a array value.

createDatabaseObject()

Connects to the database.

Shouldn't be called directly, use get() instead.

Signature

  • It accepts the following parameter(s):
    • $dbConfig (array|null) — Connection parameters in an array. Defaults to the [database] INI config section.
  • It does not return anything or a mixed result.

createReaderDatabaseObject()

Since Matomo Matomo

Connects to the reader database.

Shouldn't be called directly, use get() instead.

Signature

  • It accepts the following parameter(s):
    • $dbConfig (array|null) — Connection parameters in an array. Defaults to the [database] INI config section.
  • It does not return anything or a mixed result.

destroyDatabaseObject()

Disconnects and destroys the database connection.

For tests.

Signature

  • It does not return anything or a mixed result.

exec()

Executes an unprepared SQL query. Recommended for DDL statements like CREATE, DROP and ALTER. The return value is DBMS-specific. For MySQLI, it returns the number of rows affected. For PDO, it returns a Zend_Db_Statement object.

Signature

  • It accepts the following parameter(s):

    • $sql (string) — The SQL query.

  • Returns: integer|Zend_Db_Statement —

  • It throws one of the following exceptions:

    • Exception — If there is an error in the SQL.

query()

Executes an SQL query and returns the Zend_Db_Statement for the query.

This method is meant for non-query SQL statements like INSERT and `UPDATE. If you want to fetch data from the DB you should use one of the fetch... functions.

Signature

  • It accepts the following parameter(s):
    • $sql (string) — The SQL query.
    • $parameters (array) — Parameters to bind in the query, eg, array(param1 => value1, param2 => value2).
  • It returns a Zend_Db_Statement value.
  • It throws one of the following exceptions:
    • Exception — If there is a problem with the SQL or bind parameters.

fetchAll()

Executes an SQL SELECT statement and returns all fetched rows from the result set.

Signature

  • It accepts the following parameter(s):

    • $sql (string) — The SQL query.
    • $parameters (array) — Parameters to bind in the query, eg, array(param1 => value1, param2 => value2).

  • Returns: array — The fetched rows, each element is an associative array mapping column names with column values.

  • It throws one of the following exceptions:
    • Exception — If there is a problem with the SQL or bind parameters.

fetchRow()

Executes an SQL SELECT statement and returns the first row of the result set.

Signature

  • It accepts the following parameter(s):

    • $sql (string) — The SQL query.
    • $parameters (array) — Parameters to bind in the query, eg, array(param1 => value1, param2 => value2).

  • Returns: array — The fetched row, each element is an associative array mapping column names with column values.

  • It throws one of the following exceptions:
    • Exception — If there is a problem with the SQL or bind parameters.

fetchOne()

Executes an SQL SELECT statement and returns the first column value of the first row in the result set.

Signature

  • It accepts the following parameter(s):
    • $sql (string) — The SQL query.
    • $parameters (array) — Parameters to bind in the query, eg, array(param1 => value1, param2 => value2).
  • It returns a string value.
  • It throws one of the following exceptions:
    • Exception — If there is a problem with the SQL or bind parameters.

fetchAssoc()

Executes an SQL SELECT statement and returns the entire result set indexed by the first selected field.

Signature

  • It accepts the following parameter(s):

    • $sql (string) — The SQL query.
    • $parameters (array) — Parameters to bind in the query, eg, array(param1 => value1, param2 => value2).

  • Returns: array — eg, array('col1value1' => array('col2' => '...', 'col3' => ...), 'col1value2' => array('col2' => '...', 'col3' => ...))

  • It throws one of the following exceptions:
    • Exception — If there is a problem with the SQL or bind parameters.

deleteAllRows()

Deletes all desired rows in a table, while using a limit. This function will execute many DELETE queries until there are no more rows to delete.

Use this function when you need to delete many thousands of rows from a table without locking the table for too long.

Example

// delete all visit rows whose ID is less than a certain value, 100000 rows at a time
$idVisit = // ...
Db::deleteAllRows(Common::prefixTable('log_visit'), "WHERE idvisit <= ?", "idvisit ASC", 100000, array($idVisit));

Signature

  • It accepts the following parameter(s):

    • $table (string) — The name of the table to delete from. Must be prefixed (see Common::prefixTable()).
    • $where (string) — The where clause of the query. Must include the WHERE keyword.
    • $orderBy (string) — The column to order by and the order by direction, eg, idvisit ASC.
    • $maxRowsPerQuery (int) — The maximum number of rows to delete per DELETE query.
    • $parameters (array) — Parameters to bind for each query.

  • Returns: int — The total number of rows deleted.

optimizeTables()

Runs an OPTIMIZE TABLE query on the supplied table or tables.

Tables will only be optimized if the [General] enable_sql_optimize_queries INI config option is set to 1.

Signature

  • It accepts the following parameter(s):
    • $tables (string|array) — The name of the table to optimize or an array of tables to optimize. Table names must be prefixed (see Common::prefixTable()).
    • $force (bool) — If true, the OPTIMIZE TABLE query will be run even if InnoDB tables are being used.
  • It returns a bool value.

dropTables()

Drops the supplied table or tables.

Signature

  • It accepts the following parameter(s):
    • $tables (string|array) — The name of the table to drop or an array of table names to drop. Table names must be prefixed (see Common::prefixTable()).
  • It returns a Zend_Db_Statement value.

dropAllTables()

Drops all tables

Signature

  • It does not return anything or a mixed result.

lockTables()

Locks the supplied table or tables.

NOTE: Matomo does not require the LOCK TABLES privilege to be available. Matomo should still work if it has not been granted.

Signature

  • It accepts the following parameter(s):
    • $tablesToRead (string|array) — The table or tables to obtain 'read' locks on. Table names must be prefixed (see Common::prefixTable()).
    • $tablesToWrite (string|array) — The table or tables to obtain 'write' locks on. Table names must be prefixed (see Common::prefixTable()).
  • It returns a Zend_Db_Statement value.

unlockAllTables()

Releases all table locks.

NOTE: Matomo does not require the LOCK TABLES privilege to be available. Matomo should still work if it has not been granted.

Signature

  • It returns a Zend_Db_Statement value.

segmentedFetchFirst()

Performs a SELECT statement on a table one chunk at a time and returns the first successfully fetched value.

This function will execute a query on one set of rows in a table. If nothing is fetched, it will execute the query on the next set of rows and so on until the query returns a value.

This function will break up a SELECT into several smallerSELECTs and should be used when performing aSELECTthat can take a long time to finish. Using several smallerSELECT`s will ensure that the table will not be locked for too long.

Example

// find the most recent visit that is older than a certain date
$dateStart = // ...
$sql = "SELECT idvisit
      FROM $logVisit
     WHERE '$dateStart' > visit_last_action_time
       AND idvisit <= ?
       AND idvisit > ?
  ORDER BY idvisit DESC
     LIMIT 1";

// since visits
return Db::segmentedFetchFirst($sql, $maxIdVisit, 0, -self::$selectSegmentSize);

Signature

  • It accepts the following parameter(s):
    • $sql (string) — The SQL to perform. The last two conditions of the WHERE expression must be as follows: 'id >= ? AND id < ?' where id is the int id of the table.
    • $first (int) — The minimum ID to loop from.
    • $last (int) — The maximum ID to loop to.
    • $step (int) — The maximum number of rows to scan in one query.
    • $params (array) — Parameters to bind in the query, eg, array(param1 => value1, param2 => value2)
  • It returns a string value.

segmentedFetchOne()

Performs a SELECT on a table one chunk at a time and returns an array of every fetched value.

This function will break up a SELECT query into several smaller queries by using only a limited number of rows at a time. It will accumulate the results of each smaller query and return the result.

This function should be used when performing a SELECT that can take a long time to finish. Using several smaller queries will ensure that the table will not be locked for too long.

Signature

  • It accepts the following parameter(s):

    • $sql (string) — The SQL to perform. The last two conditions of the WHERE expression must be as follows: 'id >= ? AND id < ?' where id is the int id of the table.
    • $first (int) — The minimum ID to loop from.
    • $last (int) — The maximum ID to loop to.
    • $step (int) — The maximum number of rows to scan in one query.
    • $params (array) — Parameters to bind in the query, array(param1 => value1, param2 => value2)

  • Returns: array — An array of primitive values.

segmentedFetchAll()

Performs a SELECT on a table one chunk at a time and returns an array of every fetched row.

This function will break up a SELECT query into several smaller queries by using only a limited number of rows at a time. It will accumulate the results of each smaller query and return the result.

This function should be used when performing a SELECT that can take a long time to finish. Using several smaller queries will ensure that the table will not be locked for too long.

Signature

  • It accepts the following parameter(s):

    • $sql (string) — The SQL to perform. The last two conditions of the WHERE expression must be as follows: 'id >= ? AND id < ?' where id is the int id of the table.
    • $first (int) — The minimum ID to loop from.
    • $last (int) — The maximum ID to loop to.
    • $step (int) — The maximum number of rows to scan in one query.
    • $params (array) — Parameters to bind in the query, array( param1 => value1, param2 => value2)

  • Returns: array — An array of rows that includes the result set of every smaller query.

segmentedQuery()

Performs a UPDATE or DELETE statement on a table one chunk at a time.

This function will break up a query into several smaller queries by using only a limited number of rows at a time.

This function should be used when executing a non-query statement will take a long time to finish. Using several smaller queries will ensure that the table will not be locked for too long.

Signature

  • It accepts the following parameter(s):
    • $sql (string) — The SQL to perform. The last two conditions of the WHERE expression must be as follows: 'id >= ? AND id < ?' where id is the int id of the table.
    • $first (int) — The minimum ID to loop from.
    • $last (int) — The maximum ID to loop to.
    • $step (int) — The maximum number of rows to scan in one query.
    • $params (array) — Parameters to bind in the query, array(param1 => value1, param2 => value2)
  • It does not return anything or a mixed result.

getDbLock()

Attempts to get a named lock. This function uses a timeout of 1s, but will retry a set number of times.

Signature

  • It accepts the following parameter(s):

    • $lockName (string) — The lock name.
    • $maxRetries (int) — The max number of times to retry.

  • Returns: bool — true if the lock was obtained, false if otherwise.

  • It throws one of the following exceptions:

releaseDbLock()

Releases a named lock.

Signature

  • It accepts the following parameter(s):

    • $lockName (string) — The lock name.

  • Returns: bool — true if the lock was released, false if otherwise.

isLockPrivilegeGranted()

Checks whether the database user is allowed to lock tables.

Signature

  • It returns a bool value.

enableQueryLog()

Signature

  • It accepts the following parameter(s):

    • $enable (bool) —

  • It does not return anything or a mixed result.

isQueryLogEnabled()

Signature

  • It returns a boolean value.

isOptimizeInnoDBSupported()

Signature

  • It accepts the following parameter(s):

    • $version

  • It does not return anything or a mixed result.