← All changes | includes/base/db.php +369 -23 1.2.2 → trunk View file →

		@@ -20,8 +20,20 @@
20	20	$this->token = WPMCS_TOKEN;
21	21	}
22	22
23	23	/**
	24	+ * Get table name with correct DB prefix (multisite-aware)
	25	+ *
	26	+ * @param int|null $blog_id Optional. Blog ID for multisite.
	27	+ * @return string
	28	+ */
	29	+ public static function get_table_name( $blog_id = null ) {
	30	+ global $wpdb;
	31	+ $prefix = is_multisite() ? $wpdb->get_blog_prefix( $blog_id ?? get_current_blog_id() ) : $wpdb->prefix;
	32	+ return $prefix . WPMCS_ITEM_TABLE;
	33	+ }
	34	+
	35	+ /**
24	36	* Create Database Table
25	37	*
26	38	*/
27	39	public function create_table() {
		@@ -28,50 +40,384 @@
28	40	global $wpdb;
29	41
30	42	require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
31	43
32		- $table_name = WPMCS_ITEM_TABLE;
	44	+ $table_name = self::get_table_name();
33	45	$queries = array();
34	46	$charset_collate = $wpdb->get_charset_collate();
35	47	if($wpdb->get_var("SHOW TABLES LIKE '$table_name'") != $table_name) {
36	48	$queries[] = "
37	49	CREATE TABLE {$table_name} (
38		- id BIGINT(20) NOT NULL AUTO_INCREMENT,
39		- provider varchar(18) NOT NULL,
40		- region varchar(255) NOT NULL,
41		- storage varchar(255) NOT NULL,
42		- source_id bigint(20) NOT NULL,
43		- source_path varchar(1024) NOT NULL,
44		- source_type varchar(18) NOT NULL,
45		- url varchar(1024) NOT NULL,
46		- `key` varchar(1024) NOT NULL,
47		- is_private tinyint(1) NOT NULL DEFAULT 0,
48		- extra longtext DEFAULT NULL,
49		- PRIMARY KEY (id),
50		- UNIQUE KEY uidx_url (url(190), id),
51		- UNIQUE KEY uidx_key (`key`(190), id),
52		- UNIQUE KEY uidx_source_path (source_path(190), id)
53		- ) $charset_collate;";
	50	+ id BIGINT(20) NOT NULL AUTO_INCREMENT,
	51	+ provider VARCHAR(18) NOT NULL,
	52	+ region VARCHAR(255) NOT NULL,
	53	+ storage VARCHAR(255) NOT NULL,
	54	+ source_id BIGINT(20) NOT NULL,
	55	+ source_path VARCHAR(1024) NOT NULL,
	56	+ source_type VARCHAR(18) NOT NULL,
	57	+ url VARCHAR(1024) NOT NULL,
	58	+ `key` VARCHAR(1024) NOT NULL,
	59	+ original_source_path VARCHAR(1024) NOT NULL,
	60	+ original_key VARCHAR(1024) NOT NULL,
	61	+ is_private TINYINT(1) NOT NULL DEFAULT 0,
	62	+ extra LONGTEXT DEFAULT NULL,
	63	+
	64	+ PRIMARY KEY (id),
	65	+
	66	+ UNIQUE KEY uidx_url (url(190), id),
	67	+ UNIQUE KEY uidx_key (`key`(190), id),
	68	+ UNIQUE KEY uidx_source_path (source_path(190), id),
	69	+ UNIQUE KEY uidx_original_source_path (original_source_path(190), id),
	70	+ UNIQUE KEY uidx_original_key (original_key(190), id),
	71	+ KEY idx_item_lookup (
	72	+ source_id,
	73	+ source_type,
	74	+ provider,
	75	+ storage,
	76	+ region
	77	+ ),
	78	+ KEY idx_source_path_provider (
	79	+ provider,
	80	+ storage,
	81	+ region,
	82	+ source_path(190)
	83	+ )
	84	+ ) $charset_collate;";
54	85	}
55	86	dbDelta( $queries );
56	87	}
57	88
58	89	/**
	90	+ * Create tables across all sites (on activation)
	91	+ */
	92	+ public static function handle_tables($network_wide = false) {
	93	+ // If network-wide activation, create tables for all sites in multisite
	94	+ if (is_multisite() && $network_wide) {
	95	+ $sites = get_sites(['number' => 1000, 'fields' => 'ids']); // Adjust number as needed
	96	+ // If no sites found, return early
	97	+ if (is_wp_error($sites) || !is_array($sites) || empty($sites)) {
	98	+ return;
	99	+ }
	100	+
	101	+ // Create table for each site in multisite
	102	+ foreach ($sites as $site_id) {
	103	+ switch_to_blog($site_id);
	104	+ self::instance()->create_table();
	105	+ restore_current_blog();
	106	+ }
	107	+ } else {
	108	+ self::instance()->create_table();
	109	+ }
	110	+ }
	111	+
	112	+ /**
59	113	* Database Upgrade
60	114	* @since 1.0.0
61	115	*/
62	116	public function do_database_upgrade() {
63	117	global $wpdb;
64		- $table_name = WPMCS_ITEM_TABLE;
	118	+ $table_name = self::get_table_name();
65	119	$current_version = get_option( $this->token.'_db_version' );
	120	+ $latest_version = WPMCS_DB_VERSION;
66	121
67		- switch($current_version) {
68		- case '1.0.0': null;
69		- break;
70		- default: null;
	122	+ if ( $current_version === false ) {
	123	+ $current_version = $latest_version;
71	124	}
72	125
73		- update_option( $this->token.'_db_version', WPMCS_DB_VERSION, true );
	126	+ /**
	127	+ * If DB version below 1.0.1
	128	+ * @since 1.2.11
	129	+ *
	130	+ * This is to handle the migration of Google Cloud credentials from file to JSON string.
	131	+ */
	132	+ if (version_compare($current_version, '1.0.1', '<')) {
	133	+ $credentials = Utils::get_credentials();
	134	+ if (isset($credentials['service']) && $credentials['service'] === 'gcloud') {
	135	+ $config = $credentials['config'] ?? null;
	136	+ if($config) {
	137	+ $config_json_path = $config['config_json']['path'] ?? null;
	138	+ if (!empty($config_json_path) && file_exists($config_json_path)) {
	139	+ $config_json = file_get_contents($config_json_path);
	140	+ if (!empty($config_json)) {
	141	+ $credentials['config']['config_json'] = $config_json;
	142	+ Utils::update_credentials($credentials);
	143	+ $updated = Utils::update_option('credentials', $credentials, Schema::getConstant('GLOBAL_SETTINGS_KEY'));
	144	+ if ($updated) {
	145	+ unlink($config_json_path);
	146	+ }
	147	+ }
	148	+ }
	149	+ }
	150	+ }
	151	+ }
	152	+
	153	+ /**
	154	+ * If DB version below 1.0.2
	155	+ *
	156	+ * @since 1.2.12
	157	+ * This is to handle the migration of media library counter from the global settings to the specific media library key.
	158	+ */
	159	+ if (version_compare($current_version, '1.0.2', '<')) {
	160	+ $settings_key = Schema::getConstant('COUNTER_KEY');
	161	+ $media_library = Utils::get_option( '', [], $settings_key );
	162	+ if (is_array($media_library) && !empty($media_library)) {
	163	+ Utils::update_option('', ['media_library' => $media_library ?? []], $settings_key);
	164	+ }
	165	+ }
	166	+
	167	+
	168	+ /**
	169	+ * If DB version below 1.0.3
	170	+ *
	171	+ * @since 1.2.13
	172	+ * This is to handle the migration of media library counter from the global settings to the specific media library key.
	173	+ * Re-Create plugin specific upload directory
	174	+ */
	175	+ if (version_compare($current_version, '1.0.3', '<')) {
	176	+ Counter::instance()->fetch_and_update();
	177	+ }
	178	+
	179	+ /**
	180	+ * If DB version below 1.0.4
	181	+ *
	182	+ * @since 1.3.1
	183	+ * This is to handle the removal of htaccess from uploads folder and adding htaccess to plugin specific upload directory
	184	+ */
	185	+ if (version_compare($current_version, '1.0.4', '<')) {
	186	+ $upload_dir = wp_upload_dir();
	187	+ $upload_base = $upload_dir['basedir'];
	188	+
	189	+ if (is_dir($upload_base)) {
	190	+ $files_to_delete = array(
	191	+ $upload_base . '/.htaccess',
	192	+ $upload_base . '/' . Schema::getConstant('UPLOADS') . '/.htaccess',
	193	+ $upload_base . '/' . Schema::getConstant('UPLOADS') .'/index.php',
	194	+ );
	195	+ foreach ($files_to_delete as $file) {
	196	+ if (file_exists($file)) {
	197	+ @unlink($file); // deletes the file
	198	+ }
	199	+ }
	200	+ }
	201	+
	202	+ // Re-Create plugin specific upload directory
	203	+ do_action('wpmcs_create_plugin_dir');
	204	+ }
	205	+
	206	+
	207	+ /**
	208	+ * If DB version below 1.0.5
	209	+ *
	210	+ * @since 1.3.5
	211	+ * Schema adjustments:
	212	+ * - Add original_source_path AFTER `key`
	213	+ * - Add original_key AFTER original_source_path
	214	+ * - Add required indexes
	215	+ */
	216	+ if ( version_compare( $current_version, '1.0.5', '<' ) ) {
	217	+ global $wpdb;
	218	+
	219	+ $table_name = self::get_table_name();
	220	+
	221	+ /**
	222	+ * 1. Add original_source_path AFTER `key`
	223	+ */
	224	+ $exists = $wpdb->get_var(
	225	+ $wpdb->prepare(
	226	+ "SHOW COLUMNS FROM {$table_name} LIKE %s",
	227	+ 'original_source_path'
	228	+ )
	229	+ );
	230	+
	231	+ if ( ! $exists ) {
	232	+ $wpdb->query(
	233	+ "ALTER TABLE {$table_name}
	234	+ ADD COLUMN original_source_path VARCHAR(1024) NOT NULL
	235	+ AFTER `key`"
	236	+ );
	237	+ }
	238	+
	239	+ /**
	240	+ * 2. Add original_key AFTER original_source_path
	241	+ */
	242	+ $exists = $wpdb->get_var(
	243	+ $wpdb->prepare(
	244	+ "SHOW COLUMNS FROM {$table_name} LIKE %s",
	245	+ 'original_key'
	246	+ )
	247	+ );
	248	+
	249	+ if ( ! $exists ) {
	250	+ $wpdb->query(
	251	+ "ALTER TABLE {$table_name}
	252	+ ADD COLUMN original_key VARCHAR(1024) NOT NULL
	253	+ AFTER original_source_path"
	254	+ );
	255	+ }
	256	+
	257	+ /**
	258	+ * 3. Unique index for original_source_path
	259	+ */
	260	+ $exists = $wpdb->get_var(
	261	+ $wpdb->prepare(
	262	+ "SHOW INDEX FROM {$table_name} WHERE Key_name = %s",
	263	+ 'uidx_original_source_path'
	264	+ )
	265	+ );
	266	+
	267	+ if ( ! $exists ) {
	268	+ $wpdb->query(
	269	+ "ALTER TABLE {$table_name}
	270	+ ADD UNIQUE KEY uidx_original_source_path (original_source_path(190), id)"
	271	+ );
	272	+ }
	273	+
	274	+ /**
	275	+ * 4. Unique index for original_key
	276	+ */
	277	+ $exists = $wpdb->get_var(
	278	+ $wpdb->prepare(
	279	+ "SHOW INDEX FROM {$table_name} WHERE Key_name = %s",
	280	+ 'uidx_original_key'
	281	+ )
	282	+ );
	283	+
	284	+ if ( ! $exists ) {
	285	+ $wpdb->query(
	286	+ "ALTER TABLE {$table_name}
	287	+ ADD UNIQUE KEY uidx_original_key (original_key(190), id)"
	288	+ );
	289	+ }
	290	+
	291	+ /**
	292	+ * 5. Item lookup index (get by source_id)
	293	+ */
	294	+ $exists = $wpdb->get_var(
	295	+ $wpdb->prepare(
	296	+ "SHOW INDEX FROM {$table_name} WHERE Key_name = %s",
	297	+ 'idx_item_lookup'
	298	+ )
	299	+ );
	300	+
	301	+ if ( ! $exists ) {
	302	+ $wpdb->query(
	303	+ "ALTER TABLE {$table_name}
	304	+ ADD KEY idx_item_lookup (
	305	+ source_id,
	306	+ source_type,
	307	+ provider,
	308	+ storage,
	309	+ region
	310	+ )"
	311	+ );
	312	+ }
	313	+
	314	+ /**
	315	+ * 6. Provider-aware source_path prefix index
	316	+ */
	317	+ $exists = $wpdb->get_var(
	318	+ $wpdb->prepare(
	319	+ "SHOW INDEX FROM {$table_name} WHERE Key_name = %s",
	320	+ 'idx_source_path_provider'
	321	+ )
	322	+ );
	323	+
	324	+ if ( ! $exists ) {
	325	+ $wpdb->query(
	326	+ "ALTER TABLE {$table_name}
	327	+ ADD KEY idx_source_path_provider (
	328	+ provider,
	329	+ storage,
	330	+ region,
	331	+ source_path(190)
	332	+ )"
	333	+ );
	334	+ }
	335	+
	336	+ // Update upgrade version option only for who had previous version to trigger initial upgrade tracking
	337	+ update_option( $this->token . '_db_upgrade_version', '0.0.0', false );
	338	+ }
	339	+
	340	+ /**
	341	+ * If DB version below 1.0.6
	342	+ *
	343	+ * @since 1.3.12
	344	+ * Flatten recursively nested wpmcs_status_data caused by legacy set_status writes.
	345	+ */
	346	+ if ( version_compare( $current_version, '1.0.6', '<' ) ) {
	347	+ $this->repair_nested_status_data();
	348	+ }
	349	+
	350	+
	351	+
	352	+ update_option( $this->token.'_db_version', $latest_version, true );
	353	+ }
	354	+
	355	+ /**
	356	+ * One-time repair for recursively nested wpmcs_status_data.
	357	+ * Flattens status entries caused by legacy set_status writes that used get_option directly.
	358	+ *
	359	+ * @since 1.3.12
	360	+ * @return void
	361	+ */
	362	+ private function repair_nested_status_data() {
	363	+ $meta_name = Schema::getConstant('STATUS_KEY');
	364	+ $current_settings = Utils::get_option('status', [], $meta_name);
	365	+
	366	+ if (!is_array($current_settings) || empty($current_settings)) {
	367	+ return;
	368	+ }
	369	+
	370	+ $known_keys = ['cdnRead', 'storageCredentials'];
	371	+ $collected = [];
	372	+ $this->collect_status_entries($current_settings, $collected);
	373	+
	374	+ if (empty($collected)) {
	375	+ return;
	376	+ }
	377	+
	378	+ $normalized = [];
	379	+ foreach ($known_keys as $key) {
	380	+ if (isset($collected[$key])) {
	381	+ $normalized[$key] = $collected[$key];
	382	+ }
	383	+ }
	384	+
	385	+ if (empty($normalized) || $normalized === $current_settings) {
	386	+ return;
	387	+ }
	388	+
	389	+ Utils::update_option('status', $normalized, $meta_name);
	390	+ }
	391	+
	392	+ /**
	393	+ * Collect status entries from all nesting levels, preferring the latest check.
	394	+ * @since 1.3.12
	395	+ * @param array $settings
	396	+ * @param array $collected
	397	+ * @return void
	398	+ */
	399	+ private function collect_status_entries($settings, &$collected) {
	400	+ if (!is_array($settings)) {
	401	+ return;
	402	+ }
	403	+
	404	+ $known_keys = ['cdnRead', 'storageCredentials'];
	405	+
	406	+ foreach ($known_keys as $key) {
	407	+ if (isset($settings[$key]) && is_array($settings[$key]) && array_key_exists('status', $settings[$key])) {
	408	+ $entry = $settings[$key];
	409	+ $checked = isset($entry['lastChecked']) ? (int) $entry['lastChecked'] : 0;
	410	+
	411	+ if (!isset($collected[$key]) || $checked >= ($collected[$key]['lastChecked'] ?? 0)) {
	412	+ $collected[$key] = $entry;
	413	+ }
	414	+ }
	415	+ }
	416	+
	417	+ if (isset($settings['status']) && is_array($settings['status'])) {
	418	+ $this->collect_status_entries($settings['status'], $collected);
	419	+ }
74	420	}
75	421
76	422	/**
77	423	* Ensures only one instance of Class is loaded or can be loaded.