<?PHP
/**
* Suppress "error - 0 - No summary was found for this file" on phpdoc generation
*
* @package WPDataAccess\Utilities
*/
namespace WPDataAccess\Utilities {
use WPDataAccess\Data_Dictionary\WPDA_Dictionary_Exist;
use WPDataAccess\Plugin_Table_Models\WPDA_Logging_Model;
use WPDataAccess\Plugin_Table_Models\WPDA_Media_Model;
use WPDataAccess\Plugin_Table_Models\WPDA_Publisher_Model;
use WPDataAccess\Plugin_Table_Models\WPDA_Design_Table_Model;
use WPDataAccess\Plugin_Table_Models\WPDA_Table_Settings_Model;
use WPDataAccess\Plugin_Table_Models\WPDA_User_Menus_Model;
use WPDataAccess\Plugin_Table_Models\WPDP_Page_Model;
use WPDataAccess\Plugin_Table_Models\WPDP_Project_Model;
use WPDataAccess\Plugin_Table_Models\WPDP_Project_Design_Table_Model;
use WPDataAccess\WPDA;
/**
* Class WPDA_Repository
*
* Recreate repository objects.
*
* @author Peter Schulz
* @since 1.0.0
*/
class WPDA_Repository {
const CREATE_TABLE = array(
'wpda_logging' => array(
'create_table_logging.sql',
),
'wpda_menus' => array(
'create_table_menus.sql',
),
'wpda_table_settings' => array(
'create_table_table_settings.sql',
),
'wpda_table_design' => array(
'create_table_table_design.sql',
'create_table_table_design_alter1.sql',
'create_table_table_design_alter3.sql',
'create_table_table_design_alter3.sql',
),
'wpda_publisher' => array( // Old plugin table name: do NOT change!
'create_table_publisher.sql',
),
'wpda_media' => array(
'create_table_media.sql',
),
'wpda_project' => array(
'create_table_project.sql',
),
'wpda_project_page' => array(
'create_table_project_page.sql',
),
'wpda_project_table' => array(
'create_table_project_table.sql',
),
'wpda_csv_uploads' => array(
'create_table_csv_uploads.sql',
),
'wpda_app' => array(
'create_table_app.sql',
),
'wpda_app_container' => array(
'create_table_app_container.sql',
),
'wpda_app_apps' => array(
'create_table_app_apps.sql',
),
);
const DROP_TABLE = array(
'wpda_logging' => array(
'drop_table_logging.sql',
),
'wpda_menus' => array(
'drop_table_menus.sql',
),
'wpda_table_settings' => array(
'drop_table_table_settings.sql',
),
'wpda_table_design' => array(
'drop_table_table_design.sql',
),
'wpda_publisher' => array( // Old plugin table name: do NOT change!
'drop_table_publisher.sql',
),
'wpda_media' => array(
'drop_table_media.sql',
),
'wpda_project' => array(
'drop_table_project.sql',
),
'wpda_project_page' => array(
'drop_table_project_page.sql',
),
'wpda_project_table' => array(
'drop_table_project_table.sql',
),
'wpda_csv_uploads' => array(
'drop_table_csv_uploads.sql',
),
'wpda_app' => array(
'drop_table_app.sql',
),
'wpda_app_container' => array(
'drop_table_app_container.sql',
),
'wpda_app_apps' => array(
'drop_table_app_apps.sql',
),
);
protected $sql_repository_dir = '';
public function __construct() {
$this->sql_repository_dir = plugin_dir_path( dirname( __FILE__ ) ) . '../admin/repository/';
}
/**
* Recreate repository (save as much data as possible)
*
* @since 2.0.11
*/
public function recreate() {
global $wpdb;
$suppress = $wpdb->suppress_errors( true );
// Remove foreign key constraints on app tables
$wpdb->query(
"ALTER TABLE {$wpdb->prefix}wpda_app_apps DROP FOREIGN KEY `wp_wpda_app_apps_ibfk_1`"
);
$wpdb->query(
"ALTER TABLE {$wpdb->prefix}wpda_app_apps DROP FOREIGN KEY `wp_wpda_app_apps_ibfk_2`"
);
$wpdb->query(
"AlTER TABLE {$wpdb->prefix}wpda_app_container DROP FOREIGN KEY `wp_wpda_app_container_ibfk_1`"
);
$bck_postfix = WPDA_Restore_Repository::BACKUP_TABLE_EXTENSION . date( 'YmdHis' );
foreach ( static::CREATE_TABLE as $key => $value ) {
$table_name = $wpdb->prefix . $key;
// Check if table exists
$table_exists = new WPDA_Dictionary_Exist( $wpdb->dbname, $table_name );
$table_check = $table_exists->table_exists( false );
$bck_table_name = null;
$same_cols = null;
if ( $table_check ) {
// Create backup table
$bck_table_name = $wpdb->prefix . $key . $bck_postfix;
$wpdb->query(
$wpdb->prepare(
'create table `%1s` as select * from `%1s`', // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders
array(
WPDA::remove_backticks( $bck_table_name ),
WPDA::remove_backticks( $table_name ),
)
)
);
}
if ( $table_check ) {
// Create temporary table to check for changes
if ( $this->run_script( $value[0], '_new' ) ) {
// Check if table structure was changed
$table_diff = $wpdb->get_row(
$wpdb->prepare(
'select column_name,ordinal_position,data_type,column_type from (
select column_name, ordinal_position, data_type, column_type, count(1) rowcount
from information_schema.columns
where table_schema = %s
and table_name in (%s,%s)
group by column_name, ordinal_position, data_type, column_type
having count(1) = 1
) A',
$wpdb->dbname,
$table_name,
"{$table_name}_new"
)
);
// Drop check table
$this->run_script( static::DROP_TABLE[ $key ][0], '_new' );
if ( $table_diff !== null ) {
// Drop old repository table
$this->run_script( static::DROP_TABLE[ $key ][0], '' );
// Create new repository table
foreach ( $value as $sql_file ) {
$this->run_script( $sql_file );
}
// Get columns matching old and new repository table columns to restore as many values as possible
$same_cols = $wpdb->get_results(
$wpdb->prepare(
'select c1.column_name as column_name
from information_schema.columns c1
where c1.table_schema = %s
and c1.table_name = %s
and c1.column_name in (
select c2.column_name
from information_schema.columns c2
where c2.table_schema = %s
and c2.table_name = %s
)
',
$wpdb->dbname,
$table_name,
$wpdb->dbname,
$bck_table_name
),
'ARRAY_A'
);
// Restore repository table data
$selected_columns = '';
foreach ( $same_cols as $same_col ) {
$selected_columns .= $same_col['column_name'] . ',';
}
$selected_columns = substr( $selected_columns, 0, strlen( $selected_columns ) - 1 );
$wpdb->query(
$wpdb->prepare(
'insert into `%1s` (%1s) select %1s from `%1s`', // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders
array(
WPDA::remove_backticks( $table_name ),
$selected_columns,
$selected_columns,
WPDA::remove_backticks( $bck_table_name ),
)
)
);
}
}
} else {
// Create table
foreach ( $value as $sql_file ) {
$this->run_script( $sql_file );
}
}
if ( 'on' !== WPDA::get_option( WPDA::OPTION_MR_KEEP_BACKUP_TABLES ) ) {
// Drop backup table
$this->run_script( static::DROP_TABLE[ $key ][0], $bck_postfix );
}
}
$this->cleanup();
$this->remove_old_backups();
$wpdb->suppress_errors( $suppress );
}
public function remove_old_backups() {
if ( 'on' === WPDA::get_option( WPDA::OPTION_MR_KEEP_BACKUP_TABLES ) ) {
$backup_tables_kept = WPDA::get_option( WPDA::OPTION_MR_BACKUP_TABLES_KEPT );
$base_table_name = WPDA_Publisher_Model::get_base_table_name() . WPDA_Restore_Repository::BACKUP_TABLE_EXTENSION;
global $wpdb;
$rows = $wpdb->get_results(
$wpdb->prepare(
'
select table_name as table_name
from information_schema.tables
where table_schema = %s
and table_name like %s
order by 1 desc
',
array(
$wpdb->dbname,
"{$base_table_name}%",
)
),
'ARRAY_A'
);
for ( $i = $backup_tables_kept; $i < count( $rows ); $i ++ ) {//phpcs:ignore - 8.1 proof
$backup_date = substr( $rows[ $i ]['table_name'], strlen( $base_table_name ) );
$this->remove_backup( $backup_date );
}
}
}
public function remove_backup( $backup_date ) {
global $wpdb;
$backup_date_sanitized = WPDA::remove_backticks( $backup_date );
$suppress = $wpdb->suppress_errors( true );
$extension = WPDA_Restore_Repository::BACKUP_TABLE_EXTENSION;
foreach ( self::CREATE_TABLE as $key => $value ) {
$wpdb->query(
$wpdb->prepare(
'drop table `%1s`', // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders
array(
WPDA::remove_backticks( "{$wpdb->prefix}{$key}{$extension}{$backup_date_sanitized}" ),
)
)
);
}
$wpdb->suppress_errors( $suppress );
}
/**
* Cleanup plugin repository tables
*/
protected function cleanup() {
global $wpdb;
// Remove previous data table list table settings
$wpdb->query(
"delete from {$wpdb->prefix}usermeta where meta_key like '%columnshidden%' and meta_key like '%wpda_publisher%'"
);
// Remove previous project page list table settings
$wpdb->query(
"delete from {$wpdb->prefix}usermeta where meta_key like '%columnshidden%' and meta_key like '%wpda_project%'"
);
// Allow column to contain null values
$wpdb->query(
"alter table {$wpdb->prefix}wpda_project_page modify page_allow_full_export enum('yes','no') null default 'no'"
);
// Remove material sort icons from data tables
$wpdb->query(
"update {$wpdb->prefix}wpda_publisher set pub_sort_icons = 'default' where pub_sort_icons = 'plugin'"
);
$wpdb->query(
"alter table {$wpdb->prefix}wpda_publisher modify pub_sort_icons enum('default','none')"
);
// Remove plugin configuration settings: wrong values installed with 5.0.0 (needs to be reset on next deployment)
delete_option( 'wpda_plugin_navigation' );
delete_option( 'wpda_plugin_hide_notices' );
}
/**
* Create repository
*
* @since 1.0.0
*/
public function create() {
foreach ( static::CREATE_TABLE as $key => $value ) {
foreach ( $value as $sql_file ) {
$this->run_script( $sql_file );
}
}
}
/**
* Drop repository
*
* @since 1.0.0
*/
public function drop() {
foreach ( static::DROP_TABLE as $key => $value ) {
foreach ( $value as $sql_file ) {
$this->run_script( $sql_file );
}
}
}
/**
* Run SQL script file
*
* @param string $sql_file SQL script file name
* @param string $wpda_postfix WPDA postfix
*
* @return mixed Result of the query taken from the SQL script file
*
* @since 2.0.11
*/
public function run_script( $sql_file, $wpda_postfix = '' ) {
$sql_repository_file = $this->sql_repository_dir . $sql_file;
$sql_repository_handle = fopen( $sql_repository_file, 'r' );
if ( $sql_repository_handle ) {
// Read file content and close handle.
$sql_repository_file_content = fread( $sql_repository_handle, filesize( $sql_repository_file ) );
fclose( $sql_repository_handle );
global $wpdb;
// Replace WP prefix and WPDA prefix.
$sql_repository_file_content = str_replace( '{wp_prefix}', $wpdb->prefix, $sql_repository_file_content );
$sql_repository_file_content = str_replace( '{wpda_prefix}', 'wpda', $sql_repository_file_content ); // for backward compatibility
$sql_repository_file_content = str_replace( '{wpda_postfix}', $wpda_postfix, $sql_repository_file_content );
$sql_repository_file_content = str_replace( '{wpda_collate}', $wpdb->get_charset_collate(), $sql_repository_file_content );
// Run script from admin/repository.
return $wpdb->query( $sql_repository_file_content ); // phpcs:ignore WordPress.DB.PreparedSQL
}
}
/**
* Inform user if repository is invalid
*
* @since 1.0.0
*/
public function inform_user() {
if ( ! is_admin() ) {
return;
}
if ( isset( $_REQUEST['setup_error'] ) && 'off' === $_REQUEST['setup_error'] ) {
// Turn off menu management not available message.
WPDA::set_option( WPDA::OPTION_WPDA_SETUP_ERROR, 'off' );
} else {
if ( 'off' !== WPDA::get_option( WPDA::OPTION_WPDA_SETUP_ERROR ) ) {
// Check if repository tables exist.
if ( ! WPDA_User_Menus_Model::table_exists() ||
! WPDA_Design_Table_Model::table_exists() ||
! WPDP_Project_Design_Table_Model::table_exists() ||
! WPDA_Publisher_Model::table_exists() ||
! WPDA_Logging_Model::table_exists() ||
! WPDA_Media_Model::table_exists() ||
! WPDP_Project_Model::table_exists() ||
! WPDP_Page_Model::table_exists() ||
! WPDA_Table_Settings_Model::table_exists()
) {
$msg = new WPDA_Message_Box(
array(
'message_text' =>
__( 'Some features of WP Data Access are currently not available.', 'wp-data-access' ) .
' ' .
__( 'ACTION', 'wp-data-access' ) .
': ' .
'<a href="?page=wpdataaccess&tab=repository">' . __( 'Recreate repository', 'wp-data-access' ) . '</a>' .
' ' .
__( 'to to solve this problem.', 'wp-data-access' ) .
' [' .
'<a href="?' . esc_url( sanitize_text_field( wp_unslash( $_SERVER['QUERY_STRING'] ) ) ) . '&setup_error=off">' . __( 'do not show this message again', 'wp-data-access' ) . '</a>' .
']',
)
);
$msg->box();
}
}
}
}
public function create_new_backup() {
global $wpdb;
$bck_postfix = WPDA_Restore_Repository::BACKUP_TABLE_EXTENSION . date( 'YmdHis' );
foreach ( static::CREATE_TABLE as $key => $value ) {
$table_name = $wpdb->prefix . $key;
// Check if table exists
$table_exists = new WPDA_Dictionary_Exist( $wpdb->dbname, $table_name );
if ( $table_exists->table_exists( false ) ) {
// Create backup table
$wpdb->query(
$wpdb->prepare(
'create table `%1s` as select * from `%1s`', // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders
array(
WPDA::remove_backticks( $wpdb->prefix . $key . $bck_postfix ),
WPDA::remove_backticks( $table_name ),
)
)
); // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnquotedComplexPlaceholder
}
}
}
}
}