408 lines
9.2 KiB
PHP
408 lines
9.2 KiB
PHP
<?php
|
|
/**
|
|
* Database Base Class
|
|
*
|
|
* Abstract base class for all MonsterInsights database tables.
|
|
* Provides common functionality for table operations, CRUD methods,
|
|
* and schema management.
|
|
*
|
|
* @since 9.11.0
|
|
* @package MonsterInsights
|
|
*/
|
|
|
|
// Exit if accessed directly
|
|
if ( ! defined( 'ABSPATH' ) ) {
|
|
exit;
|
|
}
|
|
|
|
/**
|
|
* Abstract base class for database tables.
|
|
*
|
|
* @since 9.11.0
|
|
*/
|
|
abstract class MonsterInsights_DB_Base {
|
|
|
|
/**
|
|
* The table name (without prefix).
|
|
*
|
|
* @since 9.11.0
|
|
* @var string
|
|
*/
|
|
protected $table_name = '';
|
|
|
|
/**
|
|
* The table version.
|
|
*
|
|
* @since 9.11.0
|
|
* @var string
|
|
*/
|
|
protected $version = '1.0.0';
|
|
|
|
/**
|
|
* The primary key column.
|
|
*
|
|
* @since 9.11.0
|
|
* @var string
|
|
*/
|
|
protected $primary_key = 'id';
|
|
|
|
/**
|
|
* Get the table name (with prefix).
|
|
*
|
|
* @since 9.11.0
|
|
* @return string Full table name including prefix.
|
|
*/
|
|
public function get_table_name() {
|
|
global $wpdb;
|
|
return $wpdb->prefix . $this->table_name;
|
|
}
|
|
|
|
/**
|
|
* Get the table name without prefix.
|
|
*
|
|
* @since 9.11.0
|
|
* @return string Table name without prefix.
|
|
*/
|
|
public function get_bare_table_name() {
|
|
return $this->table_name;
|
|
}
|
|
|
|
/**
|
|
* Get the table schema SQL.
|
|
*
|
|
* Must be implemented by child classes.
|
|
*
|
|
* @since 9.11.0
|
|
* @return string CREATE TABLE SQL statement.
|
|
*/
|
|
abstract public function get_schema();
|
|
|
|
/**
|
|
* Get array of column names and their definitions.
|
|
*
|
|
* Must be implemented by child classes.
|
|
*
|
|
* @since 9.11.0
|
|
* @return array Array of column definitions.
|
|
*/
|
|
abstract public function get_columns();
|
|
|
|
/**
|
|
* Check if the table exists in the database.
|
|
*
|
|
* @since 9.11.0
|
|
* @return bool True if table exists, false otherwise.
|
|
*/
|
|
public function table_exists() {
|
|
global $wpdb;
|
|
$table_name = $this->get_table_name();
|
|
|
|
// Check if table exists
|
|
$result = $wpdb->get_var(
|
|
$wpdb->prepare(
|
|
'SHOW TABLES LIKE %s',
|
|
$table_name
|
|
)
|
|
);
|
|
|
|
return $result === $table_name;
|
|
}
|
|
|
|
/**
|
|
* Check if a column exists in the table.
|
|
*
|
|
* @since 9.11.0
|
|
* @param string $column Column name to check.
|
|
* @return bool True if column exists, false otherwise.
|
|
*/
|
|
public function column_exists( $column ) {
|
|
global $wpdb;
|
|
$table_name = $this->get_table_name();
|
|
|
|
$result = $wpdb->get_results(
|
|
$wpdb->prepare(
|
|
'SHOW COLUMNS FROM `%s` LIKE %s',
|
|
$table_name,
|
|
$column
|
|
)
|
|
);
|
|
|
|
return ! empty( $result );
|
|
}
|
|
|
|
/**
|
|
* Create the table using dbDelta.
|
|
*
|
|
* @since 9.11.0
|
|
* @return bool True on success, false on failure.
|
|
*/
|
|
public function create_table() {
|
|
global $wpdb;
|
|
|
|
// Load WordPress upgrade functions
|
|
require_once ABSPATH . 'wp-admin/includes/upgrade.php';
|
|
|
|
// Get schema SQL
|
|
$sql = $this->get_schema();
|
|
|
|
// Create/update table
|
|
$result = dbDelta( $sql );
|
|
|
|
// Verify table was created
|
|
$created = $this->table_exists();
|
|
|
|
// Update table version if successful
|
|
if ( $created ) {
|
|
$this->update_table_version( $this->version );
|
|
}
|
|
|
|
return $created;
|
|
}
|
|
|
|
/**
|
|
* Get the stored table version from options.
|
|
*
|
|
* @since 9.11.0
|
|
* @return string|false Table version or false if not set.
|
|
*/
|
|
public function get_table_version() {
|
|
$option_name = 'monsterinsights_' . $this->table_name . '_version';
|
|
return get_option( $option_name, false );
|
|
}
|
|
|
|
/**
|
|
* Update the table version in options.
|
|
*
|
|
* @since 9.11.0
|
|
* @param string $version Version to store.
|
|
* @return bool True on success, false on failure.
|
|
*/
|
|
public function update_table_version( $version ) {
|
|
$option_name = 'monsterinsights_' . $this->table_name . '_version';
|
|
return update_option( $option_name, $version, false );
|
|
}
|
|
|
|
/**
|
|
* Insert a row into the table.
|
|
*
|
|
* @since 9.11.0
|
|
* @param array $data Associative array of column => value pairs.
|
|
* @return int|false The number of rows affected, or false on error.
|
|
*/
|
|
public function insert( $data ) {
|
|
global $wpdb;
|
|
$table_name = $this->get_table_name();
|
|
|
|
$result = $wpdb->insert( $table_name, $data );
|
|
|
|
if ( $result ) {
|
|
return $wpdb->insert_id;
|
|
}
|
|
|
|
return false;
|
|
}
|
|
|
|
/**
|
|
* Update a row in the table.
|
|
*
|
|
* @since 9.11.0
|
|
* @param int $id Primary key value.
|
|
* @param array $data Associative array of column => value pairs to update.
|
|
* @return int|false The number of rows updated, or false on error.
|
|
*/
|
|
public function update( $id, $data ) {
|
|
global $wpdb;
|
|
$table_name = $this->get_table_name();
|
|
|
|
return $wpdb->update(
|
|
$table_name,
|
|
$data,
|
|
array( $this->primary_key => $id )
|
|
);
|
|
}
|
|
|
|
/**
|
|
* Delete a row from the table.
|
|
*
|
|
* @since 9.11.0
|
|
* @param int $id Primary key value.
|
|
* @return int|false The number of rows deleted, or false on error.
|
|
*/
|
|
public function delete( $id ) {
|
|
global $wpdb;
|
|
$table_name = $this->get_table_name();
|
|
|
|
return $wpdb->delete(
|
|
$table_name,
|
|
array( $this->primary_key => $id )
|
|
);
|
|
}
|
|
|
|
/**
|
|
* Get a single row by primary key.
|
|
*
|
|
* @since 9.11.0
|
|
* @param int $id Primary key value.
|
|
* @return object|null Row object or null if not found.
|
|
*/
|
|
public function get( $id ) {
|
|
global $wpdb;
|
|
$table_name = $this->get_table_name();
|
|
|
|
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name and primary key are safely constructed.
|
|
return $wpdb->get_row(
|
|
$wpdb->prepare(
|
|
"SELECT * FROM {$table_name} WHERE {$this->primary_key} = %d",
|
|
$id
|
|
)
|
|
);
|
|
// phpcs:enable
|
|
}
|
|
|
|
/**
|
|
* Get rows with optional filtering.
|
|
*
|
|
* @since 9.11.0
|
|
* @param array $args {
|
|
* Optional. Query arguments.
|
|
*
|
|
* @type array $where WHERE clause conditions as column => value pairs.
|
|
* @type string $orderby Column to order by.
|
|
* @type string $order ASC or DESC.
|
|
* @type int $limit Number of rows to return.
|
|
* @type int $offset Number of rows to skip.
|
|
* }
|
|
* @return array Array of row objects.
|
|
*/
|
|
public function get_rows( $args = array() ) {
|
|
global $wpdb;
|
|
$table_name = $this->get_table_name();
|
|
|
|
// Parse arguments
|
|
$defaults = array(
|
|
'where' => array(),
|
|
'orderby' => $this->primary_key,
|
|
'order' => 'DESC',
|
|
'limit' => 100,
|
|
'offset' => 0,
|
|
);
|
|
$args = wp_parse_args( $args, $defaults );
|
|
|
|
// Build WHERE clause
|
|
$where_clauses = array();
|
|
$where_values = array();
|
|
|
|
if ( ! empty( $args['where'] ) ) {
|
|
foreach ( $args['where'] as $column => $value ) {
|
|
$where_clauses[] = "`{$column}` = %s";
|
|
$where_values[] = $value;
|
|
}
|
|
}
|
|
|
|
$where_sql = '';
|
|
if ( ! empty( $where_clauses ) ) {
|
|
$where_sql = 'WHERE ' . implode( ' AND ', $where_clauses );
|
|
}
|
|
|
|
// Build ORDER BY clause
|
|
$order = strtoupper( $args['order'] ) === 'ASC' ? 'ASC' : 'DESC';
|
|
$orderby_sql = "ORDER BY `{$args['orderby']}` {$order}";
|
|
|
|
// Build LIMIT clause
|
|
$limit_sql = '';
|
|
if ( $args['limit'] > 0 ) {
|
|
$limit_sql = $wpdb->prepare( 'LIMIT %d', $args['limit'] );
|
|
|
|
if ( $args['offset'] > 0 ) {
|
|
$limit_sql .= $wpdb->prepare( ' OFFSET %d', $args['offset'] );
|
|
}
|
|
}
|
|
|
|
// Build final query
|
|
$sql = "SELECT * FROM {$table_name} {$where_sql} {$orderby_sql} {$limit_sql}";
|
|
|
|
// Prepare and execute
|
|
if ( ! empty( $where_values ) ) {
|
|
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Query is dynamically built with safe table name.
|
|
$sql = $wpdb->prepare( $sql, $where_values );
|
|
}
|
|
|
|
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Query is dynamically built with safe table name.
|
|
return $wpdb->get_results( $sql );
|
|
}
|
|
|
|
/**
|
|
* Count rows with optional filtering.
|
|
*
|
|
* @since 9.11.0
|
|
* @param array $where WHERE clause conditions as column => value pairs.
|
|
* @return int Number of rows.
|
|
*/
|
|
public function count( $where = array() ) {
|
|
global $wpdb;
|
|
$table_name = $this->get_table_name();
|
|
|
|
// Build WHERE clause
|
|
$where_clauses = array();
|
|
$where_values = array();
|
|
|
|
if ( ! empty( $where ) ) {
|
|
foreach ( $where as $column => $value ) {
|
|
$where_clauses[] = "`{$column}` = %s";
|
|
$where_values[] = $value;
|
|
}
|
|
}
|
|
|
|
$where_sql = '';
|
|
if ( ! empty( $where_clauses ) ) {
|
|
$where_sql = 'WHERE ' . implode( ' AND ', $where_clauses );
|
|
}
|
|
|
|
// Build and execute query
|
|
$sql = "SELECT COUNT(*) FROM {$table_name} {$where_sql}";
|
|
|
|
if ( ! empty( $where_values ) ) {
|
|
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Query is dynamically built with safe table name.
|
|
$sql = $wpdb->prepare( $sql, $where_values );
|
|
}
|
|
|
|
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Query is dynamically built with safe table name.
|
|
return (int) $wpdb->get_var( $sql );
|
|
}
|
|
|
|
/**
|
|
* Truncate the table (delete all rows).
|
|
*
|
|
* @since 9.11.0
|
|
* @return bool True on success, false on failure.
|
|
*/
|
|
public function truncate() {
|
|
global $wpdb;
|
|
$table_name = $this->get_table_name();
|
|
|
|
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is safely constructed.
|
|
return $wpdb->query( "TRUNCATE TABLE {$table_name}" ) !== false;
|
|
}
|
|
|
|
/**
|
|
* Drop the table from the database.
|
|
*
|
|
* @since 9.11.0
|
|
* @return bool True on success, false on failure.
|
|
*/
|
|
public function drop_table() {
|
|
global $wpdb;
|
|
$table_name = $this->get_table_name();
|
|
|
|
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is safely constructed.
|
|
$result = $wpdb->query( "DROP TABLE IF EXISTS {$table_name}" );
|
|
|
|
// Delete version option
|
|
if ( $result !== false ) {
|
|
delete_option( 'monsterinsights_' . $this->table_name . '_version' );
|
|
}
|
|
|
|
return $result !== false;
|
|
}
|
|
}
|