<?php
/**
* Utilities for interacting with the CiviCRM database.
*
* ## EXAMPLES
*
* $ wp civicrm db cli
*
* Welcome to the MySQL monitor. Commands end with ; or \g.
* Your MySQL connection id is 180
* Server version: 5.7.34 MySQL Community Server (GPL)
*
* mysql>
*
* $ wp civicrm db config --format=table
* +----------+----------------+
* | Field | Value |
* +----------+----------------+
* | phptype | mysqli |
* | dbsyntax | mysqli |
* | username | db_username |
* | password | db_password |
* | protocol | tcp |
* | hostspec | localhost |
* | port | false |
* | socket | false |
* | database | civicrm_dbname |
* | new_link | true |
* +----------+----------------+
*
* $ wp civicrm db query 'select id,name from civicrm_group;'
* +----+---------------------------+
* | id | name |
* +----+---------------------------+
* | 1 | Administrators |
* | 4 | Advisory Board |
* | 2 | Newsletter Subscribers |
* | 3 | Summer Program Volunteers |
* +----+---------------------------+
*
* @since 5.69
*/
class CLI_Tools_CiviCRM_Command_DB extends CLI_Tools_CiviCRM_Command {
/**
* Drop all CiviCRM tables, views, functions and stored procedures from the database.
*
* ## OPTIONS
*
* [--also-include=<also-include>]
* : A comma separated list of additional tables to drop based on wildcard search.
*
* [--yes]
* : Answer yes to the confirmation message.
*
* ## EXAMPLES
*
* # Clear all CiviCRM entities from the database.
* $ wp civicrm db clear
* Dropping CiviCRM database tables...
* ...
*
* # Use an extra wildcard when some table names are not registered with CiviCRM.
* # In this case, also clear tables for the "Canadian Tax Receipts" extension.
* $ wp civicrm db clear --also-include='cdntaxreceipts_*'
* Dropping CiviCRM database tables...
* ...
*
* @since 5.69
*
* @param array $args The WP-CLI positional arguments.
* @param array $assoc_args The WP-CLI associative arguments.
*/
public function clear($args, $assoc_args) {
// Let's give folks a chance to bail.
WP_CLI::confirm(WP_CLI::colorize('%GAre you sure you want to clear all CiviCRM entities from the database?%n'), $assoc_args);
// Get all CiviCRM database entities.
$functions = $this->cividb_functions_get();
$procedures = $this->cividb_procedures_get();
$tables = $this->cividb_tables_get($assoc_args);
$views = $this->cividb_views_get($assoc_args);
// Get an instance of wpdb with CiviCRM credentials.
$cividb = $this->cividb_get();
$cividb->query('SET FOREIGN_KEY_CHECKS = 0');
// Drop all the CiviCRM database tables.
if (!empty($tables)) {
WP_CLI::log('Dropping CiviCRM database tables...');
foreach ($tables as $table) {
$query = 'DROP TABLE IF EXISTS ' . \WP_CLI\Utils\esc_sql_ident($table);
WP_CLI::debug($query, 'civicrm');
$cividb->query($query);
}
WP_CLI::success('CiviCRM database tables dropped.');
}
// Drop all the the CiviCRM database views.
if (!empty($views)) {
WP_CLI::log('Dropping CiviCRM database views...');
foreach ($views as $view) {
$query = 'DROP VIEW IF EXISTS ' . \WP_CLI\Utils\esc_sql_ident($view);
WP_CLI::debug($query, 'civicrm');
$cividb->query($query);
}
WP_CLI::success('CiviCRM database views dropped.');
}
// Drop all the the CiviCRM database functions.
if (!empty($functions)) {
WP_CLI::log('Dropping CiviCRM database functions...');
foreach ($functions as $function) {
$query = 'DROP FUNCTION IF EXISTS ' . \WP_CLI\Utils\esc_sql_ident($function);
WP_CLI::debug($query, 'civicrm');
$cividb->query($query);
}
WP_CLI::success('CiviCRM database functions dropped.');
}
// Drop all the the CiviCRM database procedures.
if (!empty($procedures)) {
WP_CLI::log('Dropping CiviCRM database procedures...');
foreach ($procedures as $procedure) {
$query = 'DROP PROCEDURE IF EXISTS ' . \WP_CLI\Utils\esc_sql_ident($procedure);
WP_CLI::debug($query, 'civicrm');
$cividb->query($query);
}
WP_CLI::success('CiviCRM database procedures dropped.');
}
$cividb->query('SET FOREIGN_KEY_CHECKS = 1');
}
/**
* Quickly enter the MySQL command line.
*
* ## EXAMPLES
*
* $ wp civicrm db cli
*
* Welcome to the MySQL monitor. Commands end with ; or \g.
* Your MySQL connection id is 180
* Server version: 5.7.34 MySQL Community Server (GPL)
*
* mysql>
*
* @since 5.69
*
* @param array $args The WP-CLI positional arguments.
* @param array $assoc_args The WP-CLI associative arguments.
*/
public function cli($args, $assoc_args) {
// Get CiviCRM credentials.
$dsn = $this->cividb_dsn_get();
$mysql_args = [
'host' => $dsn['hostspec'],
'database' => $dsn['database'],
'user' => $dsn['username'],
'pass' => $dsn['password'],
];
\WP_CLI\Utils\run_mysql_command('mysql --no-defaults', $mysql_args);
}
/**
* Show the CiviCRM database connection details.
*
* ## OPTIONS
*
* [--format=<format>]
* : Render output in a particular format.
* ---
* default: table
* options:
* - table
* - json
* - pretty
* ---
*
* ## EXAMPLES
*
* $ wp civicrm db config --format=table
* +----------+----------------+
* | Field | Value |
* +----------+----------------+
* | phptype | mysqli |
* | dbsyntax | mysqli |
* | username | db_username |
* | password | db_password |
* | protocol | tcp |
* | hostspec | localhost |
* | port | false |
* | socket | false |
* | database | civicrm_dbname |
* | new_link | true |
* +----------+----------------+
*
* @alias conf
*
* @since 5.69
*
* @param array $args The WP-CLI positional arguments.
* @param array $assoc_args The WP-CLI associative arguments.
*/
public function config($args, $assoc_args) {
// Get CiviCRM credentials.
$dsn = $this->cividb_dsn_get();
$format = \WP_CLI\Utils\get_flag_value($assoc_args, 'format', 'table');
switch ($format) {
// Pretty-print output.
case 'pretty':
WP_CLI::log(print_r($dsn, TRUE));
break;
// Display output as json.
case 'json':
WP_CLI::log(json_encode($dsn));
break;
// Display output as table (default).
case 'table':
default:
$assoc_args['format'] = $format;
$assoc_args['fields'] = array_keys($dsn);
$formatter = $this->formatter_get($assoc_args);
$formatter->display_item($dsn);
}
}
/**
* Get a string which connects to the CiviCRM database.
*
* ## EXAMPLES
*
* $ wp civicrm db connect
* mysql --database=civicrm_db_name --host=db_host --user=db_username --password=db_password
*
* @since 5.69
*
* @param array $args The WP-CLI positional arguments.
* @param array $assoc_args The WP-CLI associative arguments.
*/
public function connect($args, $assoc_args) {
// Get CiviCRM credentials.
$dsn = $this->cividb_dsn_get();
$command = sprintf(
'mysql --database=%s --host=%s --user=%s --password=%s',
$dsn['database'],
$dsn['hostspec'],
$dsn['username'],
$dsn['password']
);
if (isset($dsn['port']) && !empty($dsn['port'])) {
$command .= ' --port=' . $dsn['port'];
}
WP_CLI::log($command);
}
/**
* Drop an entire CiviCRM database.
*
* It is not possible to drop the CiviCRM database when it is shared with WordPress.
*
* To create a fresh CiviCRM database, use `wp civicrm db import` to load an existing
* database file or make sure you have the CiviCRM plugin installed (e.g. using the
* `wp civicrm core install` command) and then call `wp civicrm core activate`.
*
* ## OPTIONS
*
* [--yes]
* : Answer yes to the confirmation message.
*
* ## EXAMPLES
*
* # Drop the CiviCRM database.
* $ wp civicrm db drop
*
* # Drop the CiviCRM database without the confirm message.
* $ wp civicrm db drop --yes
*
* @since 5.69
*
* @param array $args The WP-CLI positional arguments.
* @param array $assoc_args The WP-CLI associative arguments.
*/
public function drop($args, $assoc_args) {
// Use "wp civicrm db is-shared" to check if the CiviCRM database is shared with WordPress.
$command = 'civicrm db is-shared --show-result';
$options = ['launch' => FALSE, 'return' => TRUE];
$shared = WP_CLI::runcommand($command, $options);
// Bail if sharing database with WordPress.
if (!empty($shared)) {
WP_CLI::error('You cannot drop the CiviCRM database when it is shared with WordPress.');
}
// Let's give folks a chance to bail.
WP_CLI::confirm(WP_CLI::colorize('%GAre you sure you want to drop the CiviCRM database?%n'), $assoc_args);
// Get CiviCRM credentials.
$dsn = $this->cividb_dsn_get();
// Use "wp civicrm db query" to drop the CiviCRM database.
$command = 'civicrm db query ' . sprintf('DROP DATABASE IF EXISTS %s', $dsn['database']);
$options = ['launch' => FALSE, 'return' => FALSE];
WP_CLI::runcommand($command, $options);
}
/**
* Drop the CiviCRM tables and views from the database.
*
* ## OPTIONS
*
* [--tables-only]
* : Drop only tables.
*
* [--views-only]
* : Drop only views.
*
* [--also-include=<also-include>]
* : A comma separated list of additional tables to drop based on wildcard search.
*
* [--yes]
* : Answer yes to the confirmation message.
*
* ## EXAMPLES
*
* # Drop all CiviCRM tables and views.
* $ wp civicrm db drop-tables
*
* # Drop just the CiviCRM tables.
* $ wp civicrm db drop-tables --tables-only
*
* # Drop just the CiviCRM views.
* $ wp civicrm db drop-tables --views-only
*
* # Use an extra wildcard when some table names are not registered with CiviCRM.
* # In this case, also drop tables for the "Canadian Tax Receipts" extension.
* $ wp civicrm db drop-tables --also-include='cdntaxreceipts_*' --tables-only
*
* @subcommand drop-tables
*
* @since 5.69
*
* @param array $args The WP-CLI positional arguments.
* @param array $assoc_args The WP-CLI associative arguments.
*/
public function drop_tables($args, $assoc_args) {
// Grab associative arguments.
$tables_only = (bool) \WP_CLI\Utils\get_flag_value($assoc_args, 'tables-only', FALSE);
$views_only = (bool) \WP_CLI\Utils\get_flag_value($assoc_args, 'views-only', FALSE);
// Let's give folks a chance to bail.
if (empty($views_only) && empty($tables_only)) {
$message = 'tables and views';
}
elseif (!empty($views_only)) {
$message = 'views';
}
elseif (!empty($tables_only)) {
$message = 'tables';
}
WP_CLI::confirm(sprintf(WP_CLI::colorize('%GAre you sure you want to drop the CiviCRM %s from the database?%n'), $message), $assoc_args);
// Get CiviCRM tables and views.
$tables = $this->cividb_tables_get($assoc_args);
$views = $this->cividb_views_get($assoc_args);
// Get an instance of wpdb with CiviCRM credentials.
$cividb = $this->cividb_get();
$cividb->query('SET FOREIGN_KEY_CHECKS = 0');
// Drop all the CiviCRM database tables.
if (empty($views_only)) {
WP_CLI::log('Dropping CiviCRM database tables...');
foreach ($tables as $table) {
$query = 'DROP TABLE IF EXISTS ' . \WP_CLI\Utils\esc_sql_ident($table);
WP_CLI::debug($query, 'civicrm');
$cividb->query($query);
}
WP_CLI::success('CiviCRM database tables dropped.');
}
// Drop all the the CiviCRM database views.
if (empty($tables_only)) {
WP_CLI::log('Dropping CiviCRM database views...');
foreach ($views as $view) {
$query = 'DROP VIEW ' . \WP_CLI\Utils\esc_sql_ident($view);
WP_CLI::debug($query, 'civicrm');
$cividb->query($query);
}
WP_CLI::success('CiviCRM database views dropped.');
}
$cividb->query('SET FOREIGN_KEY_CHECKS = 1');
}
/**
* Dump the whole database that CiviCRM has credentials for and print to STDOUT or save to a file.
*
* This command is useful on servers where the user may not have direct access to the `mysqldump`
* command and the user wants to dump the entire database in which the CiviCRM tables reside.
* For more granular exports of the CiviCRM tables, functions, procedures and views, use the
* `wp civicrm db export` command instead.
*
* ## OPTIONS
*
* [--tables=<tables>]
* : The comma separated list of specific tables to export. Excluding this parameter will export all tables in the database.
*
* [--result-file=<result-file>]
* : The path to the saved file.
*
* ## EXAMPLES
*
* $ wp civicrm db dump
*
* $ wp civicrm db dump --result-file=/tmp/civi-db.sql
* Success: Exported to /tmp/civi-db.sql
*
* @since 5.69
*
* @param array $args The WP-CLI positional arguments.
* @param array $assoc_args The WP-CLI associative arguments.
*/
public function dump($args, $assoc_args) {
// Grab associative arguments.
$tables = \WP_CLI\Utils\get_flag_value($assoc_args, 'tables', FALSE);
// Get CiviCRM credentials.
$dsn = $this->cividb_dsn_get();
// Build command and escaped shell arguments.
$mysqldump_binary = \WP_CLI\Utils\force_env_on_nix_systems('mysqldump');
$command = $mysqldump_binary . " --opt --triggers --routines --events --host={$dsn['hostspec']} --user={$dsn['username']} --password='{$dsn['password']}' %s";
$command_esc_args = [$dsn['database']];
if (!empty($tables)) {
$requested_tables = explode(',', $tables);
unset($assoc_args['tables']);
$command .= ' --tables';
foreach ($requested_tables as $table) {
$command .= ' %s';
$command_esc_args[] = trim($table);
}
}
// Process command and escaped shell arguments.
$escaped_command = call_user_func_array(
'\WP_CLI\Utils\esc_cmd',
array_merge(
[$command],
$command_esc_args
)
);
\WP_CLI\Utils\run_mysql_command($escaped_command, $assoc_args);
// Maybe show some feedback.
$result_file = \WP_CLI\Utils\get_flag_value($assoc_args, 'result-file', FALSE);
if (!empty($result_file)) {
WP_CLI::success(sprintf('Exported to %s', $assoc_args['result-file']));
}
}
/**
* Export the whole CiviCRM database and print to STDOUT or save to a file.
*
* By default, CiviCRM loads its tables into the WordPress database but it is also possible
* to configure CiviCRM to have its own database. To keep things contained, this command
* only exports the tables, views, triggers, routines and events that are part of CiviCRM.
*
* ## OPTIONS
*
* [--tables=<tables>]
* : Comma separated list of tables to export based on wildcard search. Excluding this parameter will export all CiviCRM tables in the database.
*
* [--result-file=<result-file>]
* : The path to the saved file. Excluding this parameter will export to STDOUT.
*
* [--also-include=<also-include>]
* : A comma separated list of additional wildcards to search.
*
* ## EXAMPLES
*
* # Export database to STDOUT.
* $ wp civicrm db export
* -- MySQL dump 10.13 Distrib 5.7.34, for osx11.0 (x86_64)
* --
* -- Host: localhost Database: civicrm_db
* -- ------------------------------------------------------
* -- Server version 5.7.34
* ...
*
* # Export database to file.
* $ wp civicrm db export --result-file=/tmp/civi-db.sql
* Success: Exported to /tmp/civi-db.sql
*
* # Restrict the exported tables using a wildcard argument as a filter.
* $ wp civicrm db export --tables='*_log' --result-file=/tmp/civi-db.sql
* Success: Exported to /tmp/civi-db.sql
*
* # Use an extra wildcard when some table names are not registered with CiviCRM.
* $ wp civicrm db export --also-include='cdntaxreceipts_*' --result-file=/tmp/civi-db.sql
* Success: Exported to /tmp/civi-db.sql
*
* # Restrict the exported tables using a wildcard argument as a filter.
* # Also uses an extra wildcard when some table names are not registered with CiviCRM.
* # In this case, also exports tables for the "Canadian Tax Receipts" extension.
* $ wp civicrm db export --tables='*_log' --also-include='cdntaxreceipts_*' --result-file=/tmp/civi-db.sql
* Success: Exported to /tmp/civi-db.sql
*
* @since 5.69
*
* @param array $args The WP-CLI positional arguments.
* @param array $assoc_args The WP-CLI associative arguments.
*/
public function export($args, $assoc_args) {
// Grab associative arguments.
$tables = \WP_CLI\Utils\get_flag_value($assoc_args, 'tables', FALSE);
$also_include = (string) \WP_CLI\Utils\get_flag_value($assoc_args, 'also-include', '');
// Get CiviCRM credentials.
$dsn = $this->cividb_dsn_get();
// Do we want only certain CiviCRM tables?
$table_args = '';
if (!empty($tables)) {
$requested_tables = explode(',', $tables);
foreach ($requested_tables as $table) {
$table_args .= ' ' . trim($table);
}
unset($assoc_args['tables']);
}
// Maybe add extra filters.
$also_include_args = '';
if (!empty($also_include)) {
$also_include_args = " --also-include={$also_include}";
unset($assoc_args['also-include']);
}
// Get the list of tables.
$tables_command = "civicrm db tables{$table_args}{$also_include_args} --format=csv";
WP_CLI::debug('Tables Command: ' . $tables_command, 'civicrm');
$options = ['launch' => FALSE, 'return' => TRUE];
$tables = WP_CLI::runcommand($tables_command, $options);
$tables = explode(',', $tables);
// Build command and escaped shell arguments.
$mysqldump_binary = \WP_CLI\Utils\force_env_on_nix_systems('mysqldump');
$command = $mysqldump_binary . " --opt --triggers --routines --events --host={$dsn['hostspec']} --user={$dsn['username']} --password='{$dsn['password']}' %s";
$command_esc_args = [$dsn['database']];
$command .= ' --tables';
foreach ($tables as $table) {
$command .= ' %s';
$command_esc_args[] = trim($table);
}
// Process command and escaped shell arguments.
$escaped_command = call_user_func_array(
'\WP_CLI\Utils\esc_cmd',
array_merge(
[$command],
$command_esc_args
)
);
WP_CLI::debug('Final "mysqldump" Command: ' . $escaped_command, 'civicrm');
\WP_CLI\Utils\run_mysql_command($escaped_command, $assoc_args);
// Maybe show some feedback.
$result_file = \WP_CLI\Utils\get_flag_value($assoc_args, 'result-file', FALSE);
if (!empty($result_file)) {
WP_CLI::success(sprintf('Exported to %s', $assoc_args['result-file']));
}
}
/**
* Get the list of CiviCRM functions in the database.
*
* ## OPTIONS
*
* [<function>...]
* : List functions based on wildcard search, e.g. 'civicrm_*' or 'civicrm_event?'.
*
* [--format=<format>]
* : Render output in a particular format.
* ---
* default: list
* options:
* - list
* - json
* - csv
* ---
*
* ## EXAMPLES
*
* $ wp civicrm db functions
* civicrm_strip_non_numeric
*
* @since 5.69
*
* @param array $args The WP-CLI positional arguments.
* @param array $assoc_args The WP-CLI associative arguments.
*/
public function functions($args, $assoc_args) {
// Grab associative arguments.
$format = \WP_CLI\Utils\get_flag_value($assoc_args, 'format', 'list');
// Let's use an instance of wpdb with CiviCRM credentials.
$cividb = $this->cividb_get();
// Default query.
$dsn = $this->cividb_dsn_get();
$functions_sql = "SHOW FUNCTION STATUS WHERE Db = '{$dsn['database']}'";
// Perform query.
$functions = $cividb->get_col($functions_sql, 1);
// Filter by `$args` wildcards.
if ($args) {
$functions = $this->names_filter($args, $functions);
}
// Render output.
if ('csv' === $format) {
WP_CLI::log(implode(',', $functions));
}
elseif ('json' === $format) {
$json = json_encode($functions);
if (JSON_ERROR_NONE !== json_last_error()) {
WP_CLI::error(sprintf(WP_CLI::colorize('Failed to encode JSON: %Y%s.%n'), json_last_error_msg()));
}
echo $json . "\n";
}
else {
foreach ($functions as $function) {
WP_CLI::log($function);
}
}
}
/**
* Loads a whole CiviCRM database.
*
* ## OPTIONS
*
* [--load-file=<load-file>]
* : The path to the database file.
*
* ## EXAMPLES
*
* $ wp civicrm db import /tmp/civicrm.sql
*
* @alias load
*
* @since 5.69
*
* @param array $args The WP-CLI positional arguments.
* @param array $assoc_args The WP-CLI associative arguments.
*/
public function import($args, $assoc_args) {
// Grab associative arguments.
$load_file = \WP_CLI\Utils\get_flag_value($assoc_args, 'load-file', FALSE);
// Get CiviCRM credentials.
$dsn = $this->cividb_dsn_get();
$mysql_args = [
'host' => $dsn['hostspec'],
'database' => $dsn['database'],
'user' => $dsn['username'],
'pass' => $dsn['password'],
'execute' => 'SOURCE ' . $load_file,
];
\WP_CLI\Utils\run_mysql_command('/usr/bin/env mysql', $mysql_args);
}
/**
* Check if CiviCRM shares a database with WordPress.
*
* ## OPTIONS
*
* [--show-result]
* : Print the result to STDOUT. Note that the echoed boolean is the reverse of the exit status.
*
* ## EXAMPLES
*
* # Check if CiviCRM shares a database with WordPress. Exit status 0 if shared, otherwise 1.
* $ wp civicrm db is-shared
* $ echo $?
* 0
*
* # Show whether CiviCRM shares a database with WordPress. Prints 1 if shared, otherwise 0.
* $ wp civicrm db is-shared --show-result
* 1
*
* # Shell command that shows if CiviCRM shares a database with WordPress.
* if wp civicrm db is-shared; then echo "Yup"; else echo "Nope"; fi
* Nope
*
* @subcommand is-shared
*
* @since 5.69
*
* @param array $args The WP-CLI positional arguments.
* @param array $assoc_args The WP-CLI associative arguments.
*/
public function is_shared($args, $assoc_args) {
// Grab associative arguments.
$show = (bool) \WP_CLI\Utils\get_flag_value($assoc_args, 'show-result', FALSE);
// Get CiviCRM credentials.
$dsn = $this->cividb_dsn_get();
// True when host and database name are identical.
$shared = FALSE;
if ($dsn['hostspec'] === DB_HOST && $dsn['database'] === DB_NAME) {
$shared = TRUE;
}
// Exit with code if not showing result.
if (empty($show)) {
if (!empty($shared)) {
WP_CLI::halt(0);
}
else {
WP_CLI::halt(1);
}
}
// Show result.
if (empty($shared)) {
echo "0\n";
}
else {
echo "1\n";
}
}
/**
* Get the list of CiviCRM stored procedures in the database.
*
* ## OPTIONS
*
* [<procedure>...]
* : List procedures based on wildcard search, e.g. 'civicrm_*'.
*
* [--format=<format>]
* : Render output in a particular format.
* ---
* default: list
* options:
* - list
* - json
* - csv
* ---
*
* ## EXAMPLES
*
* $ wp civicrm db procedures
*
* @since 5.69
*
* @param array $args The WP-CLI positional arguments.
* @param array $assoc_args The WP-CLI associative arguments.
*/
public function procedures($args, $assoc_args) {
// Grab associative arguments.
$format = \WP_CLI\Utils\get_flag_value($assoc_args, 'format', 'list');
// Let's use an instance of wpdb with CiviCRM credentials.
$cividb = $this->cividb_get();
// Default query.
$dsn = $this->cividb_dsn_get();
$procedures_sql = "SHOW PROCEDURE STATUS WHERE Db = '{$dsn['database']}'";
// Perform query.
$procedures = $cividb->get_col($procedures_sql, 1);
// Filter by `$args` wildcards.
if ($args) {
$procedures = $this->names_filter($args, $procedures);
}
// Render output.
if ('csv' === $format) {
WP_CLI::log(implode(',', $procedures));
}
elseif ('json' === $format) {
$json = json_encode($procedures);
if (JSON_ERROR_NONE !== json_last_error()) {
WP_CLI::error(sprintf(WP_CLI::colorize('Failed to encode JSON: %Y%s.%n'), json_last_error_msg()));
}
echo $json . "\n";
}
else {
foreach ($procedures as $procedure) {
WP_CLI::log($procedure);
}
}
}
/**
* Perform a query on the CiviCRM database.
*
* ## OPTIONS
*
* <query>
* : The SQL query to perform.
*
* ## EXAMPLES
*
* $ wp civicrm db query 'select id,name from civicrm_group;'
* +----+---------------------------+
* | id | name |
* +----+---------------------------+
* | 1 | Administrators |
* | 4 | Advisory Board |
* | 2 | Newsletter Subscribers |
* | 3 | Summer Program Volunteers |
* +----+---------------------------+
*
* @since 5.69
*
* @param array $args The WP-CLI positional arguments.
* @param array $assoc_args The WP-CLI associative arguments.
*/
public function query($args, $assoc_args) {
if (!isset($args[0])) {
WP_CLI::error('No query specified.');
}
$query = $args[0];
// Get CiviCRM credentials.
$dsn = $this->cividb_dsn_get();
$mysql_args = [
'host' => $dsn['hostspec'],
'database' => $dsn['database'],
'user' => $dsn['username'],
'pass' => $dsn['password'],
'execute' => $query,
];
\WP_CLI\Utils\run_mysql_command('/usr/bin/env mysql --no-defaults', $mysql_args);
}
/**
* Gets a set of CiviCRM tables in the database.
*
* ## OPTIONS
*
* [<table>...]
* : List tables based on wildcard search, e.g. 'civicrm_*_group' or 'civicrm_event?'.
*
* [--tables-only]
* : Restrict returned tables to those that are not views.
*
* [--views-only]
* : Restrict returned tables to those that are views.
*
* [--also-include=<also-include>]
* : A comma separated list of additional wildcards to search.
*
* [--format=<format>]
* : Render output in a particular format.
* ---
* default: list
* options:
* - list
* - json
* - csv
* ---
*
* ## EXAMPLES
*
* # Use a wildcard to get matching table names from the set of CiviCRM tables.
* $ wp civicrm db tables 'civicrm_*_group' --tables-only
* civicrm_campaign_group
* civicrm_custom_group
* civicrm_dedupe_rule_group
* civicrm_mailing_group
* civicrm_option_group
* civicrm_uf_group
*
* # Use wildcards to get matching view names from the set of CiviCRM multi-lingual views.
* % wp civicrm db tables 'civicrm_*_group_de_de' 'civicrm_*_group_en_us' --views-only
* civicrm_custom_group_de_de
* civicrm_custom_group_en_us
* civicrm_option_group_de_de
* civicrm_option_group_en_us
* civicrm_uf_group_de_de
* civicrm_uf_group_en_us
*
* # Use an extra wildcard when some table names are not registered with CiviCRM.
* # In this case, include tables for the "Canadian Tax Receipts" extension.
* $ wp civicrm db tables '*_log' --also-include='cdntaxreceipts_*' --tables-only
* cdntaxreceipts_log
* civicrm_action_log
* civicrm_job_log
* civicrm_log
* civicrm_membership_log
* civicrm_system_log
* civirule_rule_log
*
* # When CiviCRM shares a database with WordPress, use an extra wildcard to include
* # WordPress tables in a query. Here `$wpdb->prefix` is set to the default 'wp_'.
* % wp civicrm db tables '*_user*' --also-include='wp_*' --tables-only
* civicrm_user_job
* log_civicrm_user_job
* wp_usermeta
* wp_users
*
* @since 5.69
*
* @param array $args The WP-CLI positional arguments.
* @param array $assoc_args The WP-CLI associative arguments.
*/
public function tables($args, $assoc_args) {
// Grab associative arguments.
$tables_only = (bool) \WP_CLI\Utils\get_flag_value($assoc_args, 'tables-only', FALSE);
$views_only = (bool) \WP_CLI\Utils\get_flag_value($assoc_args, 'views-only', FALSE);
$also_include = (string) \WP_CLI\Utils\get_flag_value($assoc_args, 'also-include', '');
$format = (string) \WP_CLI\Utils\get_flag_value($assoc_args, 'format', 'list');
// Bail if incompatible args have been supplied.
if (!empty($tables_only) && !empty($views_only)) {
WP_CLI::error('You cannot supply --tables-only and --views-only at the same time.');
}
// Let's use an instance of wpdb with CiviCRM credentials.
$cividb = $this->cividb_get();
// Default query.
$tables_sql = 'SHOW TABLES';
// Override query with table type restriction if needed.
if (!empty($tables_only)) {
$tables_sql = 'SHOW FULL TABLES WHERE Table_Type = "BASE TABLE"';
}
elseif (!empty($views_only)) {
$tables_sql = 'SHOW FULL TABLES WHERE Table_Type = "VIEW"';
}
// Perform query.
$tables = $cividb->get_col($tables_sql, 0);
// Pre-filter with CiviCRM tables and views only.
$pre_filter = [
'civicrm_*',
'log_civicrm_*',
'snap_civicrm_*',
];
// Add in any extra wildcard filters.
if (!empty($also_include)) {
$wildcards = explode(',', $also_include);
foreach ($wildcards as $wildcard) {
$pre_filter[] = trim($wildcard);
}
}
// Pre-filter now.
$tables = $this->names_filter($pre_filter, $tables);
// When tables are part of the query, add tables that are present in civicrm tables.
if (empty($views_only)) {
$civicrm_tables = array_keys(CRM_Core_DAO_AllCoreTables::tables());
foreach ($civicrm_tables as $table) {
if (!in_array($table, $tables)) {
$tables[] = $table;
}
}
}
// Filter by `$args` wildcards.
if ($args) {
$tables = $this->names_filter($args, $tables);
}
// Render output.
if ('csv' === $format) {
WP_CLI::log(implode(',', $tables));
}
elseif ('json' === $format) {
$json = json_encode($tables);
if (JSON_ERROR_NONE !== json_last_error()) {
WP_CLI::error(sprintf(WP_CLI::colorize('Failed to encode JSON: %Y%s.%n'), json_last_error_msg()));
}
echo $json . "\n";
}
else {
foreach ($tables as $table) {
WP_CLI::log($table);
}
}
}
// ----------------------------------------------------------------------------
// Private methods.
// ----------------------------------------------------------------------------
/**
* Gets the instance of wpdb with CiviCRM credentials.
*
* @since 5.69
*
* @return object $cividb The instance of wpdb with CiviCRM credentials.
*/
private function cividb_get() {
// Return instance if we have it.
static $cividb;
if (isset($cividb)) {
return $cividb;
}
// Let's use an instance of wpdb with CiviCRM credentials.
$dsn = $this->cividb_dsn_get();
$cividb = new wpdb($dsn['username'], $dsn['password'], $dsn['database'], $dsn['hostspec']);
return $cividb;
}
/**
* Gets the CiviCRM database credentials.
*
* @since 5.69
*
* @return array $dsn The array of CiviCRM database credentials.
*/
private function cividb_dsn_get() {
// Bootstrap CiviCRM.
$this->bootstrap_civicrm();
// Bail if we can't fetch database credentials.
if (!defined('CIVICRM_DSN')) {
WP_CLI::error('CIVICRM_DSN is not defined.');
}
// Parse the CiviCRM credentials.
$dsn = DB::parseDSN(CIVICRM_DSN);
return $dsn;
}
/**
* Gets the CiviCRM database functions.
*
* @since 5.69
*
* @return array $functions The array of CiviCRM database functions.
*/
private function cividb_functions_get() {
// Use "wp civicrm db functions" to find the CiviCRM database functions.
$command = "civicrm db functions 'civicrm_*' --format=json";
$options = ['launch' => FALSE, 'return' => TRUE];
$core_functions = WP_CLI::runcommand($command, $options);
// Convert to array.
$functions = json_decode($core_functions, TRUE);
if (JSON_ERROR_NONE !== json_last_error()) {
WP_CLI::error(sprintf(WP_CLI::colorize('Failed to decode JSON: %y%s.%n'), json_last_error_msg()));
}
return $functions;
}
/**
* Gets the CiviCRM database procedures.
*
* @since 5.69
*
* @return array $procedures The array of CiviCRM database procedures.
*/
private function cividb_procedures_get() {
// Use "wp civicrm db procedures" to find the CiviCRM database procedures.
$command = 'civicrm db procedures --format=json';
$options = ['launch' => FALSE, 'return' => TRUE];
$core_procedures = WP_CLI::runcommand($command, $options);
// Convert to array.
$procedures = json_decode($core_procedures, TRUE);
if (JSON_ERROR_NONE !== json_last_error()) {
WP_CLI::error(sprintf(WP_CLI::colorize('Failed to decode JSON: %y%s.%n'), json_last_error_msg()));
}
return $procedures;
}
/**
* Gets the CiviCRM database tables.
*
* @since 5.69
*
* @param array $assoc_args The WP-CLI associative arguments.
* @return array $tables The array of CiviCRM database tables.
*/
private function cividb_tables_get($assoc_args) {
// Maybe add extra filters.
$also_include = (string) \WP_CLI\Utils\get_flag_value($assoc_args, 'also-include', '');
$also_include_args = '';
if (!empty($also_include)) {
$also_include_args = " --also-include={$also_include}";
}
// Use "wp civicrm db tables" to find the CiviCRM database tables.
$command = "civicrm db tables{$also_include_args} --tables-only --format=json";
$options = ['launch' => FALSE, 'return' => TRUE];
$core_tables = WP_CLI::runcommand($command, $options);
// Convert to array.
$tables = json_decode($core_tables, TRUE);
if (JSON_ERROR_NONE !== json_last_error()) {
WP_CLI::error(sprintf(WP_CLI::colorize('Failed to decode JSON: %y%s.%n'), json_last_error_msg()));
}
return $tables;
}
/**
* Gets the CiviCRM database views.
*
* @since 5.69
*
* @param array $assoc_args The WP-CLI associative arguments.
* @return array $views The array of CiviCRM database views.
*/
private function cividb_views_get($assoc_args) {
// Maybe add extra filters.
$also_include = (string) \WP_CLI\Utils\get_flag_value($assoc_args, 'also-include', '');
$also_include_args = '';
if (!empty($also_include)) {
$also_include_args = " --also-include={$also_include}";
}
// Use "wp civicrm db tables" to find the CiviCRM database views.
$command = "civicrm db tables 'civicrm_*'{$also_include_args} --views-only --format=json";
$options = ['launch' => FALSE, 'return' => TRUE];
$core_views = WP_CLI::runcommand($command, $options);
// Convert to array.
$views = json_decode($core_views, TRUE);
if (JSON_ERROR_NONE !== json_last_error()) {
WP_CLI::error(sprintf(WP_CLI::colorize('Failed to decode JSON: %y%s.%n'), json_last_error_msg()));
}
return $views;
}
/**
* Filters an array of CiviCRM database entity names.
*
* @since 5.69
*
* @param array $wildcards The array of wildcards.
* @param array $tables The array of CiviCRM table names.
* @return array $filtered The filtered array of CiviCRM table names.
*/
private function names_filter($wildcards, $tables) {
// Build filtered array.
$args_tables = [];
foreach ($wildcards as $wildcard) {
if (FALSE !== strpos($wildcard, '*') || FALSE !== strpos($wildcard, '?')) {
$args_tables = array_merge(
$args_tables,
array_filter(
$tables,
function ($v) use ($wildcard) {
// WP-CLI itself uses fnmatch() so ignore the civilint warning.
// phpcs:disable
return fnmatch($wildcard, $v);
// phpcs:enable
}
)
);
}
else {
$args_tables[] = $wildcard;
}
}
// Clean up.
$args_tables = array_values(array_unique($args_tables));
$filtered = array_values(array_intersect($tables, $args_tables));
return $filtered;
}
}