mlsimport / includes / standalone / class-mlsimport-standalone-table.php

class-mlsimport-standalone-table.php in MLSImport: IDX Plugin & MLS Plugin for Real Estate Listings 7.2.1, at includes/standalone/class-mlsimport-standalone-table.php

145 lines 5.7 KB

Raw Download Zip

1	<?php
2	/**
3	* Standalone (theme_id 990) mlsimport_listings flat search table.
4	*
5	* One row per listing, one column per filterable/sortable field (§8). Created
6	* via dbDelta() and versioned by the mlsimport_listings_db_version option, so
7	* activation and the guarded admin_init upgrade are idempotent.
8	*
9	* @package Mlsimport
10	*/
11
12	if ( ! defined( 'ABSPATH' ) ) {
13	exit;
14	}
15
16	/**
17	* Creates and upgrades the standalone listings search table.
18	*/
19	class Mlsimport_Standalone_Table {
20
21	/**
22	* Schema version. Bump when the CREATE TABLE below changes.
23	*
24	* v2 (issue #274): mls_id becomes INT NOT NULL DEFAULT 0 and listing
25	* identity becomes composite — UNIQUE (mls_id, listing_key). RESO only
26	* guarantees ListingKey unique WITHIN one MLS, so with multiple MLS
27	* connections the old bare unique key would let MLS B silently overwrite
28	* MLS A's row on a key collision (decision #266).
29	*/
30	private const DB_VERSION = '2';
31
32	/**
33	* Fully-qualified table name (with the site's table prefix).
34	*
35	* @return string
36	*/
37	public static function table_name(): string {
38	global $wpdb;
39	return $wpdb->prefix . 'mlsimport_listings';
40	}
41
42	/**
43	* Create or upgrade the table via dbDelta(), then record the schema version.
44	*
45	* @return void
46	*/
47	public static function create(): void {
48	global $wpdb;
49
50	// dbDelta() lives in the admin upgrade file, not loaded on the front end.
51	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
52
53	// Prefixed table name + the site's charset/collation for the CREATE TABLE.
54	$table = self::table_name();
55	$collate = $wpdb->get_charset_collate();
56
57	// v2 pre-step: mls_id was VARCHAR(64) DEFAULT '' in v1 (declared but
58	// never written). dbDelta converts the column to INT below; normalize
59	// any '' values to '0' first so the type conversion is lossless.
60	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
61	if ( $table === $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) ) {
62	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
63	$mls_id_column = $wpdb->get_row( "SHOW COLUMNS FROM {$table} LIKE 'mls_id'" );
64	if ( $mls_id_column && false !== stripos( (string) $mls_id_column->Type, 'char' ) ) {
65	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
66	$wpdb->query( "UPDATE {$table} SET mls_id = '0' WHERE mls_id = ''" );
67	}
68	}
69
70	// One row per listing; one column per filterable/sortable field, plus indexes
71	// on the common filter/sort combinations and a FULLTEXT index for keywords.
72	$sql = "CREATE TABLE {$table} (
73	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
74	listing_key VARCHAR(64) NOT NULL DEFAULT '',
75	post_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
76	mls_id INT NOT NULL DEFAULT 0,
77	modification_timestamp DATETIME DEFAULT NULL,
78	price DECIMAL(15,2) DEFAULT NULL,
79	bedrooms DECIMAL(5,1) DEFAULT NULL,
80	bathrooms DECIMAL(5,1) DEFAULT NULL,
81	living_area DECIMAL(12,2) DEFAULT NULL,
82	lot_size DECIMAL(12,2) DEFAULT NULL,
83	year_built SMALLINT UNSIGNED DEFAULT NULL,
84	latitude DECIMAL(10,7) DEFAULT NULL,
85	longitude DECIMAL(10,7) DEFAULT NULL,
86	city VARCHAR(128) NOT NULL DEFAULT '',
87	state VARCHAR(64) NOT NULL DEFAULT '',
88	zip VARCHAR(16) NOT NULL DEFAULT '',
89	property_type VARCHAR(64) NOT NULL DEFAULT '',
90	listing_type VARCHAR(32) NOT NULL DEFAULT '',
91	status VARCHAR(32) NOT NULL DEFAULT '',
92	list_date DATETIME DEFAULT NULL,
93	garage_spaces SMALLINT DEFAULT NULL,
94	stories SMALLINT DEFAULT NULL,
95	hoa_fee DECIMAL(10,2) DEFAULT NULL,
96	days_on_market INT DEFAULT NULL,
97	subdivision VARCHAR(128) NOT NULL DEFAULT '',
98	search_text TEXT,
99	PRIMARY KEY (id),
100	UNIQUE KEY mls_listing_key (mls_id, listing_key),
101	KEY listing_key_lookup (listing_key),
102	KEY post_id (post_id),
103	KEY modification_timestamp (modification_timestamp),
104	KEY status_type_price (status, property_type, price),
105	KEY city_status_price (city, status, price),
106	KEY lat_lng (latitude, longitude),
107	KEY price (price),
108	KEY list_date (list_date),
109	FULLTEXT KEY search_text (search_text)
110	) {$collate};";
111
112	// dbDelta diffs the schema and creates/alters columns/indexes as needed.
113	dbDelta( $sql );
114
115	// v2 post-step: dbDelta only ADDS indexes, it never drops removed ones.
116	// The v1 bare unique index (named 'listing_key') would keep enforcing
117	// single-MLS uniqueness under the new composite key, so once the
118	// composite index exists the legacy one is dropped explicitly.
119	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
120	$index_names = (array) $wpdb->get_col( "SHOW INDEX FROM {$table}", 2 );
121	if ( in_array( 'mls_listing_key', $index_names, true ) && in_array( 'listing_key', $index_names, true ) ) {
122	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
123	$wpdb->query( "ALTER TABLE {$table} DROP INDEX listing_key" );
124	}
125
126	// Record the schema version so maybe_upgrade() can skip until the next bump.
127	update_option( 'mlsimport_listings_db_version', self::DB_VERSION );
128	}
129
130	/**
131	* Run create() only when the stored schema version is out of date. Cheap
132	* enough to call on every admin_init.
133	*
134	* @return void
135	*/
136	public static function maybe_upgrade(): void {
137	// Stored version matches the code's DB_VERSION: nothing to do.
138	if ( get_option( 'mlsimport_listings_db_version' ) === self::DB_VERSION ) {
139	return;
140	}
141	// Out of date (or never created): (re)run the idempotent CREATE TABLE.
142	self::create();
143	}
144	}
145