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

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.

destroyDatabaseObject()

Disconnects and destroys the database connection.

For tests.

Signature

  • It does not return anything.

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 Zend_Db_Statement 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.

getColumnNamesFromTable()

Get columns information from table

Signature

  • It accepts the following parameter(s):
    • $table (string|array) — The name of the table you want to get the columns definition for.
  • It returns a Zend_Db_Statement value.

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.

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: booltrue 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: booltrue 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.

isQueryLogEnabled()

Signature

  • It returns a boolean value.

isOptimizeInnoDBSupported()

Signature

  • It accepts the following parameter(s):

    • $version
  • It does not return anything.