prefix . 'astr_pages_history'; $posts_table = $wpdb->prefix . 'astr_posts_history'; $plugins_table = $wpdb->prefix . 'astr_plugins_history'; $snapshots_table = $wpdb->prefix . 'astr_customizer_snapshots'; $settings_table = $wpdb->prefix . 'astr_customizer_settings'; $snapshot_settings_table = $wpdb->prefix . 'astr_customizer_snapshot_settings'; $snapshot_meta_table = $wpdb->prefix . 'astr_customizer_snapshot_meta'; $charset_collate = $wpdb->get_charset_collate(); require_once ABSPATH . 'wp-admin/includes/upgrade.php'; // === Pages history === $sql_pages = " CREATE TABLE {$pages_table} ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, post_id BIGINT UNSIGNED NOT NULL, revision_id BIGINT UNSIGNED NOT NULL, post_title TEXT NOT NULL, post_content LONGTEXT NOT NULL, post_type VARCHAR(20) DEFAULT 'page', status VARCHAR(20) NOT NULL, user_id BIGINT UNSIGNED, user_name VARCHAR(255), time DATETIME NOT NULL, PRIMARY KEY (id), KEY revision_id (revision_id), KEY post_id (post_id) ) {$charset_collate}; "; // === Posts history === $sql_posts = " CREATE TABLE {$posts_table} ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, post_id BIGINT UNSIGNED NOT NULL, revision_id BIGINT UNSIGNED NOT NULL, post_title TEXT NOT NULL, post_content LONGTEXT NOT NULL, post_type VARCHAR(20) DEFAULT 'post', status VARCHAR(20) NOT NULL, user_id BIGINT UNSIGNED, user_name VARCHAR(255), time DATETIME NOT NULL, PRIMARY KEY (id), KEY revision_id (revision_id), KEY post_id (post_id) ) {$charset_collate}; "; // === Plugins history === $sql_plugins = " CREATE TABLE {$plugins_table} ( id mediumint(9) NOT NULL AUTO_INCREMENT, plugin_path varchar(255) NOT NULL, plugin_name varchar(255) NOT NULL, user_id bigint(20) unsigned NOT NULL, user_name varchar(255) NOT NULL, action varchar(20) NOT NULL, time datetime NOT NULL, old_version varchar(50) DEFAULT NULL, new_version varchar(50) DEFAULT NULL, extra_info TEXT NULL, PRIMARY KEY (id) ) {$charset_collate}; "; $sql_snapshots = " CREATE TABLE {$snapshots_table} ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, theme VARCHAR(255) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, user_name VARCHAR(255) NOT NULL, time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, change_type VARCHAR(50) NOT NULL, action VARCHAR(50) NOT NULL DEFAULT 'update', PRIMARY KEY (id), KEY change_type (change_type), KEY action (action), KEY time (time) ) {$charset_collate}; "; $sql_settings = " CREATE TABLE {$settings_table} ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, theme VARCHAR(255) NOT NULL, setting_key VARCHAR(191) NOT NULL, PRIMARY KEY (id), UNIQUE KEY theme_setting (theme, setting_key) ) {$charset_collate}; "; $sql_snapshot_settings = " CREATE TABLE {$snapshot_settings_table} ( snapshot_id BIGINT UNSIGNED NOT NULL, setting_id BIGINT UNSIGNED NOT NULL, old_value LONGTEXT NOT NULL, new_value LONGTEXT NOT NULL, PRIMARY KEY (snapshot_id, setting_id), KEY snapshot_id (snapshot_id), KEY setting_id (setting_id) ) {$charset_collate}; "; $sql_snapshot_meta_table = " CREATE TABLE {$snapshot_meta_table} ( snapshot_id BIGINT UNSIGNED NOT NULL, meta_key VARCHAR(100) NOT NULL, meta_value LONGTEXT NOT NULL, PRIMARY KEY (snapshot_id, meta_key) ) {$charset_collate}; "; // === Execute all === dbDelta( $sql_pages ); dbDelta( $sql_posts ); dbDelta( $sql_plugins ); dbDelta( $sql_snapshots ); dbDelta( $sql_settings ); dbDelta( $sql_snapshot_settings ); dbDelta( $sql_snapshot_meta_table ); // === Update schema version === update_option( 'astr_db_version', ASTR_DB_VERSION ); // === Add foreign keys safely === astr_add_foreign_keys(); } function astr_add_foreign_keys() { global $wpdb; $tables = [ 'snapshot_settings' => [ 'table' => $wpdb->prefix . 'astr_customizer_snapshot_settings', 'keys' => [ 'fk_snapshot_settings_snapshot' => " FOREIGN KEY (snapshot_id) REFERENCES {$wpdb->prefix}astr_customizer_snapshots(id) ON DELETE CASCADE ", 'fk_snapshot_settings_setting' => " FOREIGN KEY (setting_id) REFERENCES {$wpdb->prefix}astr_customizer_settings(id) ON DELETE CASCADE ", ], ], 'snapshot_meta' => [ 'table' => $wpdb->prefix . 'astr_customizer_snapshot_meta', 'keys' => [ 'fk_snapshot_meta_snapshot' => " FOREIGN KEY (snapshot_id) REFERENCES {$wpdb->prefix}astr_customizer_snapshots(id) ON DELETE CASCADE ", ], ], ]; foreach ( $tables as $data ) { foreach ( $data['keys'] as $name => $definition ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $exists = $wpdb->get_var( $wpdb->prepare( " SELECT CONSTRAINT_NAME FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = DATABASE() AND TABLE_NAME = %s AND CONSTRAINT_NAME = %s ", $data['table'], $name ) ); if ( ! $exists ) { // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter $wpdb->query( " ALTER TABLE {$data['table']} ADD CONSTRAINT {$name} {$definition} " ); } } } }