Partial Database Exports

Partial Database Exports allows a custom set of data to be exported from the current state of a VIP Platform environment’s database.

To generate and download a Partial Database Export file to a user’s local machine use the VIP-CLI command: vip export sql [options]. Command options allow specific tables to be included in the exported file. The VIP-CLI command can also reference a custom WP-CLI command or a JSON-formatted configuration file to retrieve a more specific set of data from the database for export.

Export tables by name

The name of one or more tables can be passed with the command option --table=<table_name> to include only those tables in the Partial Database Export file.

In this example, only the wp_posts table will be included in the Partial Database Export:

vip @example-app.develop export sql --table=wp_posts

To export more than one table to the same file, pass the --table option more than once in the same command for each requested table.

In this example, only the wp_3_posts table and the wp_5_comments table from the database of a WordPress multisite environment will be included in the Partial Database Export:

vip @example-app.develop export sql --table=wp_3_posts --table=wp_5_comments

Alternatively, multiple table names can be passed as a comma-separated list (no spaces between values):

vip @example-app.develop export sql --table=wp_3_posts,wp_5_comments

Export tables related to a network site

For WordPress multisite environments, the ID of one or more network sites can be passed in the VIP-CLI command to export all tables related to those sites.

The ID of one or more network sites can be passed with the command option --site-id=<site_id> to include only the tables related to that site in the Partial Database Export file.

In this example, only the tables related to site ID 7 will be included in the Partial Database Export:

vip @example-app.develop export sql --site-id=7

To export more than the tables for more than one site to the same file, pass the --site-id option more than once in the same command for each requested site.

In this example, only the tables related to site ID 7 and site ID 11 will be included in the Partial Database Export:

vip @example-app.develop export sql --site-id=7 --site-id=11

Alternatively, multiple network site IDs can be passed as a comma-separated list (no spaces between values):

vip @example-app.develop export sql --site-id=7,11

Export data specified by a custom WP-CLI command

The data to be included in a Partial Database Export file can be specified by a custom WP-CLI command that is added to an application’s codebase. The logic of the custom WP-CLI command should be configured to retrieve the desired set of data for the export. The custom WP-CLI command can also have custom options that help extend the usefulness of the custom WP-CLI command (e.g. an option that enables a user to retrieve the data by author or a specific range of time).

For this method to work as expected, the custom WP-CLI command must exist on the branch that deploys to the environment from which the Partial Database Export file will be exported.

In this example code for a custom WP-CLI command my-custom-data-export, the command is designed to retrieve a custom set of data. The option --days is added to allow a user to limit the data retrieved by a specific number of days in the past.

if ( defined( 'WP_CLI' ) && WP_CLI ) {
	WP_CLI::add_command( 'my-custom-data-export', function( $args, $assoc_args ) {
		global $wpdb;

		$days = WP_CLI\Utils\get_flag_value( $assoc_args, 'days', 30 );

		$export_data = array(
			'tables' => array()
		);

		$lastUpdatedPostsWhere = "post_date >= NOW() - INTERVAL $days DAY OR post_modified >= NOW() - INTERVAL $days DAY";

		// wp_posts
		$export_data['tables']['wp_posts'] = array(
			'where' => $lastUpdatedPostsWhere
		);

		$export_data['tables']['wp_postmeta'] = array(
			'where' => "meta_id IN (SELECT meta_id FROM wp_postmeta JOIN wp_posts ON (wp_postmeta.post_id = wp_posts.ID) WHERE $lastUpdatedPostsWhere)"
		);

		$export_data['tables']['wp_comments'] = array(
			'where' => "comment_post_ID IN (SELECT comment_post_ID FROM wp_comments JOIN wp_posts ON (wp_comments.comment_post_ID = wp_posts.ID) WHERE $lastUpdatedPostsWhere)"
		);

		// wp_users
		if ( WP_CLI\Utils\get_flag_value( $assoc_args, 'include-users', false ) ) {
			$export_data['tables']['wp_users'] = array();
		}

		// Output JSON
		WP_CLI::line( json_encode( $export_data, JSON_PRETTY_PRINT ) );
	} );
}

To use the custom WP-CLI command to configure the data of a generated Partial Database Export file, pass the name of the command within quotes to the --wpcli-command option when running the VIP-CLI command vip export sql.

For example:

// data from the last 30 days
vip @example-app.develop export sql --wpcli-command="my-custom-data-export"

To generate a file that includes the data specified by the custom WP-CLI command but to increase the number of days to 180, pass the name of the command and the argument --days=180 within quotes:

// data from the last 180 days
vip @example-app.develop export sql --wpcli-command="my-custom-data-export --days=180"

Export data specified by a configuration file

A configuration file formatted with valid JSON can be used to specify the data for export in the Partial Database Export file. To use the configuration file, pass the absolute or relative path to the file in the --config-file option when running the VIP-CLI command vip export sql.

For example:

vip @example-app.develop export sql --config-file=~/Users/example-user/Desktop/example-file.json

The configuration file must be located on the user’s local machine and be set to a type of data export: full, tables, site_ids, or wpcli_command.

full

Set the configuration file type to full in order to export a database in its entirety.

// Export the entire database

{
  "type": "full"
}

tables

Set the configuration file type to tables in order to export specific database tables. In this example, the wp_posts, wp_postmeta, wp_users, wp_terms, and wp_options tables will be exported in the Partial Database Export file.

// Export specific database tables

{
  "type": "tables",
  "tables": {
    "wp_posts": {},
    "wp_postmeta": {},
    "wp_users": {},
    "wp_terms": {},
    "wp_options": {}
  }
}

SQL dump options

More specific data from within the tables can be exported by adding SQL dump options to the configuration file.

Options that are allowed to be added to a configuration file set to tables type:

• insert-ignore (boolean) Write INSERT IGNORE rather than INSERT statements. This will prevent the insertion of a row if it potentially creates a duplicate key.
• no-create-info (boolean) Do not write CREATE TABLE statements that re-create each dumped table. Export data with INSERT statements instead.
• no-data (boolean) Do not include table contents. Only include table schema.
• replace (boolean) Write REPLACE statements rather than INSERT statements.
• skip-add-drop-table (boolean) Do not add a DROP TABLE statement before each CREATE TABLE statement.
• where (string) Only include rows selected by the given WHERE condition.

// Export specific data from the database tables

{
  "type": "tables",
  "tables": {
      "wp_posts": {
          "no-create-info": true, // Export data with INSERT statements but do not include CREATE TABLE statements
          "insert-ignore": true // Prevent the insertion of a row if it potentially creates a duplicate key
      },
      "wp_postmeta": {
          "no-data": true // Only table schema, no data
      },
      "wp_users": {
          "insert-ignore": true,
          "where": "ID IN (SELECT DISTINCT post_author FROM wp_posts WHERE post_status = 'publish')" // Any valid SQL WHERE clause
      },
      "wp_terms": {
          "no-data": true
      },
      "wp_options": {
          "skip-add-drop-table": true, // Export data but do not include DROP TABLE IF EXISTS statements
          "replace": true // Replace existing
      }
  }
}

site_ids

Set the configuration file type to site_ids in order to export only the tables related to the network sites of a WordPress multisite that are requested by ID. In this example, the tables related to site IDs 2, 5, and 10 will be exported in the Partial Database Export file.

// Export database tables related to specific network sites

{
  "type": "site_ids",
  "site_ids": [2, 5, 10]
}

Export specific data from network site tables

SQL dump options can also be added to a configuration file set to the site_ids type. This allows specific data from within the tables related to the requested network site(s) to be included in the Partial Database Export file.

// Export specific data from the tables related to the requested network sites

{
  "type": "site_ids",
  "site_ids": [2, 5, 10],
  "tables": {
    "wp_posts": {
      "where": "post_status = 'publish'"
    },
    "wp_options": {
      "insert-ignore": true
    }
  }
}

wpcli_command

Set the configuration file type to wpcli_command in order to apply the logic of a custom WP-CLI command to specify the data to be included in the Partial Database Export file.

The custom WP-CLI command referred to in the configuration file must exist within the branch that deploys to the environment from which the data will be exported. The logic of the custom WP-CLI command should be configured to retrieve the desired set of data for the export.

In this configuration file example, data based on the logic of the custom WP-CLI command my-custom-data-export will be retrieved from the past 180 days and exported.

// Export the entire database

{
  "type": "wpcli_command",
  "wpcli_command": "my-custom-data-export --days=180"
}