<?php
/**
* Suppress "error - 0 - No summary was found for this file" on phpdoc generation
*
* @package WPDataProjects\Utilities
*/
namespace WPDataProjects\Utilities {
use WPDataAccess\WPDA;
use WPDataAccess\Data_Dictionary\WPDA_List_Columns_Cache;
use WPDataAccess\Plugin_Table_Models\WPDP_Project_Model;
use WPDataAccess\Plugin_Table_Models\WPDP_Page_Model;
use WPDataAccess\Plugin_Table_Models\WPDP_Project_Design_Table_Model;
/**
* Class WPDP_Export_Project
*
* @author Peter Schulz
* @since 2.0.0
*/
class WPDP_Export_Project {
/**
* Project ID
*
* @var string
*/
protected $project_id = null;
/**
* Options set to be exported
*
* @var array
*/
protected $optionsets = array();
/**
* Main method to start export
*
* This method checks arguments and starts the export according to the arguments provided.
*
* @since 1.0.0
*/
public function export() {
// Get arguments.
$this->project_id = isset( $_REQUEST['project_id'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['project_id'] ) ) : null; // input var okay.
if ( null !== $this->project_id ) {
// Check if export is allowed.
$wp_nonce = isset( $_REQUEST['wpnonce'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['wpnonce'] ) ) : '?'; // input var okay.
if ( ! wp_verify_nonce( $wp_nonce, 'wpdp-export-project-' . $this->project_id ) ) {
wp_die();
}
$this->export_tables();
} else {
$this->wrong_arguments();
}
}
/**
* Export project tables
*
* @since 2.0.0
*/
protected function export_tables() {
$this->header( $this->project_id );
$this->db_begin();
global $wpdb;
$this->insert_rows(
WPDP_Project_Model::get_base_table_name(),
$wpdb->prepare( 'where project_id = %d', array( $this->project_id ) ),
WPDP_Project_Model::BASE_TABLE_NAME
);
$this->insert_rows(
WPDP_Page_Model::get_base_table_name(),
$wpdb->prepare( 'where project_id = %d', array( $this->project_id ) ),
WPDP_Page_Model::BASE_TABLE_NAME
);
if ( count( $this->optionsets ) > 0 ) {//phpcs:ignore - 8.1 proof
$this->get_child_optionsets();
$this->insert_optionsets();
}
$this->db_end();
}
/**
* Set export header (filename)
*
* @param $project_id
*/
protected function header( $project_id ) {
WPDA::sent_header( 'text/plain; charset=utf-8', null, "wpda_project_{$project_id}.sql" );
}
/**
* Set MySQL environment
*
* @since 2.0.0
*/
protected function db_begin() {
global $wpdb;
echo "/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;\n";
echo "/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;\n";
echo "/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;\n";
echo '/*!40101 SET NAMES ' . esc_attr( $wpdb->charset ) . " */;\n\n";
}
/**
* Write insert into statement
*
* @param string $table_name Database table name.
* @param string $where SQL where clause.
*
* @since 2.0.0
*/
public function insert_rows( $table_name, $where, $table_name_without_prefix ) {
global $wpdb;
$save_suppress_errors = $wpdb->suppress_errors;
$wpdb->suppress_errors = true;
$query = "select * from $table_name $where";
$rows = $wpdb->get_results( $query, 'ARRAY_A' ); // phpcs:ignore WordPress.DB.PreparedSQL
if ( $wpdb->num_rows > 0 ) {
// Prepare row export: get column names and data types.
$wpda_list_columns = WPDA_List_Columns_Cache::get_list_columns( '', $table_name ); // repository tables only
$table_columns = $wpda_list_columns->get_table_columns();
// Create array for fast column_name based access.
$column_data_types = $this->column_data_types( $table_columns );
// Exports rows.
echo "--\n";
echo '-- Export table `' . esc_attr( $table_name ) . "`\n";
echo "--\n";
echo "INSERT INTO `{wp_prefix}{$table_name_without_prefix}` "; // phpcs:ignore WordPress.Security.EscapeOutput
$process_first_row = true;
foreach ( $rows[0] as $column_name => $column_value ) {
if (
! ( WPDP_Project_Model::get_base_table_name() === $table_name && 'project_id' === $column_name ) &&
! ( WPDP_Page_Model::get_base_table_name() === $table_name && 'page_id' === $column_name )
) {
echo $process_first_row ? '(' : ', ';
echo '`' . esc_attr( $column_name ) . '`';
$process_first_row = false;
}
}
echo ') VALUES ';
$first_row = true;
foreach ( $rows as $row ) {
if ( ! $first_row ) {
echo ',';
} else {
$first_row = false;
}
echo "\n(";
$keys = array_keys( $row );//phpcs:ignore - 8.1 proof
$last_column = end( $keys );//phpcs:ignore - 8.1 proof
foreach ( $row as $column_name => $column_value ) {
if (
! ( WPDP_Project_Model::get_base_table_name() === $table_name && 'project_id' === $column_name ) &&
! ( WPDP_Page_Model::get_base_table_name() === $table_name && 'page_id' === $column_name )
) {
if ( $this->is_numeric( $column_data_types[ $column_name ] ) ) {
if ( WPDP_Page_Model::get_base_table_name() === $table_name && 'project_id' === $column_name ) {
echo '@PROJECT_ID';
} else {
if ( null === $column_value ) {
echo 'null';
} else {
echo esc_attr( $column_value );
}
}
} else {
if (
(
(
WPDP_Page_Model::get_base_table_name() === $table_name &&
'page_schema_name' === $column_name
) ||
(
WPDP_Project_Design_Table_Model::get_base_table_name() === $table_name &&
'wpda_schema_name' === $column_name
)
) &&
( '' === $column_value || $wpdb->dbname === $column_value )
) {
echo "'{wp_schema}'";
} else {
if ( null === $column_value ) {
echo 'null';
} else {
echo "'" . esc_sql( $column_value ) . "'";
}
}
}
if ( $column_name !== $last_column ) {
echo ',';
}
}
}
echo ')';
if ( WPDP_Page_Model::get_base_table_name() === $table_name ) {
$this->add_optionset(
$row['page_schema_name'],
$row['page_table_name'],
$row['page_setname']
);
}
}
echo ';';
if ( WPDP_Project_Model::get_base_table_name() === $table_name ) {
echo "\n";
echo 'SET @PROJECT_ID = LAST_INSERT_ID();';
}
echo "\n\n";
} else {
// Empty table, nothing to export.
echo "--\n";
echo '-- No rows to export from empty table `' . esc_attr( $table_name ) . "`\n";
echo "--\n\n";
}
$wpdb->suppress_errors = $save_suppress_errors;
}
protected function add_optionset( $schema_name, $table_name, $setname ) {
if ( ! isset( $this->optionsets[ $schema_name ][ $table_name ][ $setname ] ) ) {
$this->optionsets[ $schema_name ][ $table_name ][ $setname ] = true;
}
}
protected function insert_optionsets() {
$where = 'where ';
foreach ( $this->optionsets as $schema_name => $tables ) {
$where .= "(`wpda_schema_name`='{$schema_name}' and (";
$first_optionset = true;
foreach ( $tables as $table_name => $optionsets ) {
foreach ( $optionsets as $optionset => $dummy ) {
if ( ! $first_optionset ) {
$where .= ' or ';
}
$where .= "(`wpda_table_name`='{$table_name}' and `wpda_table_setname`='$optionset')";
$first_optionset = false;
}
}
$where .= '))';
}
$this->insert_rows(
WPDP_Project_Design_Table_Model::get_base_table_name(),
$where,
WPDP_Project_Design_Table_Model::BASE_TABLE_NAME
);
}
protected function get_child_optionsets() {
foreach ( $this->optionsets as $schema_name => $tables ) {
foreach ( $tables as $table_name => $optionsets ) {
foreach ( $optionsets as $optionset => $dummy ) {
$set = WPDP_Project_Design_Table_Model::static_query( $schema_name, $table_name, $optionset );
if ( isset( $set->relationships ) ) {
foreach ( $set->relationships as $relationship ) {
if ( isset( $relationship->relation_type ) ) {
if ( '1n' === $relationship->relation_type ) {
if ( isset( $relationship->target_table_name ) ) {
$this->add_optionset(
$schema_name,
$relationship->target_table_name,
$optionset
);
}
} elseif ( 'nm' === $relationship->relation_type ) {
if ( isset( $relationship->relation_table_name ) ) {
$this->add_optionset(
$schema_name,
$relationship->relation_table_name,
$optionset
);
}
}
}
}
}
}
}
}
}
/**
* Save column data types
*
* This method creates a named array for all column names of a table in form:
* 'column_name' => 'data_type'
*
* Argument $table_columns can be retrieved from WPDA_List_Columns->set_table_columns(). It must be prepared
* however with the idea that the instance of WPDA_List_Columns can be reused for best performance.
*
* In fact this is just an array conversion.
*
* @param array $table_columns Column_names and data_types of a table (table name not used here).
*
* @return array Named array 'column_name' => 'data_type' for all columns in the table.
* @since 2.0.0
*/
protected function column_data_types( $table_columns ) {
$column_data_types = array();
foreach ( $table_columns as $column_value ) {
$column_data_types[ $column_value['column_name'] ] = $column_value['data_type'];
}
return $column_data_types;
}
/**
* Check if data type is numeric
*
* @param string $data_type Data type (simple).
*
* @return bool
* @since 2.0.0
*/
protected function is_numeric( $data_type ) {
return ( 'number' === WPDA::get_type( $data_type ) );
}
/**
* Set back MySQL environment
*
* @since 2.0.0
*/
protected function db_end() {
echo "/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;\n";
echo "/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;\n";
echo "/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;\n";
}
/**
* Processing on invalid arguments
*
* @since 2.0.0
*/
protected function wrong_arguments() {
wp_die();
}
}
}