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

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

175 lines 8.0 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	* v3: listing_id column — the public MLS number (RESO ListingId, e.g.
31	* "TB8541851") a visitor reads off a sign or flyer. It is NOT listing_key
32	* (the feed's internal record key) and NOT mls_id (which MLS connection the
33	* row came from). Indexed so the "MLS #" search box is an exact-match lookup.
34	*
35	* v4: featured column — 1 when the site owner ticked "Featured listing" on the
36	* property edit screen (post meta 'mlsimport_featured'), else 0. It is editorial,
37	* not MLS data, so the RESO map never fills it; Mlsimport_Standalone_Row::upsert()
38	* copies it from the post meta on every write. Indexed because "featured first"
39	* sorts on it. UNSIGNED so a future weight (3 > 1 > 0) needs no migration.
40	*/
41	private const DB_VERSION = '4';
42
43	/**
44	* Fully-qualified table name (with the site's table prefix).
45	*
46	* @return string
47	*/
48	public static function table_name(): string {
49	global $wpdb;
50	return $wpdb->prefix . 'mlsimport_listings';
51	}
52
53	/**
54	* Create or upgrade the table via dbDelta(), then record the schema version.
55	*
56	* @return void
57	*/
58	public static function create(): void {
59	global $wpdb;
60
61	// dbDelta() lives in the admin upgrade file, not loaded on the front end.
62	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
63
64	// Prefixed table name + the site's charset/collation for the CREATE TABLE.
65	$table = self::table_name();
66	$collate = $wpdb->get_charset_collate();
67
68	// v2 pre-step: mls_id was VARCHAR(64) DEFAULT '' in v1 (declared but
69	// never written). dbDelta converts the column to INT below; normalize
70	// any '' values to '0' first so the type conversion is lossless.
71	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
72	if ( $table === $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) ) {
73	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
74	$mls_id_column = $wpdb->get_row( "SHOW COLUMNS FROM {$table} LIKE 'mls_id'" );
75	if ( $mls_id_column && false !== stripos( (string) $mls_id_column->Type, 'char' ) ) {
76	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
77	$wpdb->query( "UPDATE {$table} SET mls_id = '0' WHERE mls_id = ''" );
78	}
79	}
80
81	// One row per listing; one column per filterable/sortable field, plus indexes
82	// on the common filter/sort combinations and a FULLTEXT index for keywords.
83	$sql = "CREATE TABLE {$table} (
84	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
85	listing_key VARCHAR(64) NOT NULL DEFAULT '',
86	post_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
87	mls_id INT NOT NULL DEFAULT 0,
88	modification_timestamp DATETIME DEFAULT NULL,
89	price DECIMAL(15,2) DEFAULT NULL,
90	bedrooms DECIMAL(5,1) DEFAULT NULL,
91	bathrooms DECIMAL(5,1) DEFAULT NULL,
92	living_area DECIMAL(12,2) DEFAULT NULL,
93	lot_size DECIMAL(12,2) DEFAULT NULL,
94	year_built SMALLINT UNSIGNED DEFAULT NULL,
95	latitude DECIMAL(10,7) DEFAULT NULL,
96	longitude DECIMAL(10,7) DEFAULT NULL,
97	city VARCHAR(128) NOT NULL DEFAULT '',
98	state VARCHAR(64) NOT NULL DEFAULT '',
99	zip VARCHAR(16) NOT NULL DEFAULT '',
100	property_type VARCHAR(64) NOT NULL DEFAULT '',
101	listing_type VARCHAR(32) NOT NULL DEFAULT '',
102	status VARCHAR(32) NOT NULL DEFAULT '',
103	list_date DATETIME DEFAULT NULL,
104	garage_spaces SMALLINT DEFAULT NULL,
105	stories SMALLINT DEFAULT NULL,
106	hoa_fee DECIMAL(10,2) DEFAULT NULL,
107	days_on_market INT DEFAULT NULL,
108	subdivision VARCHAR(128) NOT NULL DEFAULT '',
109	listing_id VARCHAR(32) NOT NULL DEFAULT '',
110	featured TINYINT UNSIGNED NOT NULL DEFAULT 0,
111	search_text TEXT,
112	PRIMARY KEY (id),
113	UNIQUE KEY mls_listing_key (mls_id, listing_key),
114	KEY listing_key_lookup (listing_key),
115	KEY post_id (post_id),
116	KEY modification_timestamp (modification_timestamp),
117	KEY status_type_price (status, property_type, price),
118	KEY city_status_price (city, status, price),
119	KEY lat_lng (latitude, longitude),
120	KEY price (price),
121	KEY list_date (list_date),
122	KEY listing_id (listing_id),
123	KEY featured (featured),
124	FULLTEXT KEY search_text (search_text)
125	) {$collate};";
126
127	// dbDelta diffs the schema and creates/alters columns/indexes as needed.
128	dbDelta( $sql );
129
130	// v2 post-step: dbDelta only ADDS indexes, it never drops removed ones.
131	// The v1 bare unique index (named 'listing_key') would keep enforcing
132	// single-MLS uniqueness under the new composite key, so once the
133	// composite index exists the legacy one is dropped explicitly.
134	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
135	$index_names = (array) $wpdb->get_col( "SHOW INDEX FROM {$table}", 2 );
136	if ( in_array( 'mls_listing_key', $index_names, true ) && in_array( 'listing_key', $index_names, true ) ) {
137	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
138	$wpdb->query( "ALTER TABLE {$table} DROP INDEX listing_key" );
139	}
140
141	// v3 post-step: fill the new listing_id column for listings imported before
142	// it existed. Standalone stores every RESO field as 'mlsimport_<Field>' post
143	// meta, so the MLS number is already on the post. Only blank rows are set,
144	// which makes the step idempotent (a re-run changes nothing) and leaves any
145	// value the importer has since written alone. Listings imported without
146	// ListingId in the field selection have no meta and stay blank.
147	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
148	$wpdb->query( "UPDATE {$table} L INNER JOIN {$wpdb->postmeta} M ON M.post_id = L.post_id AND M.meta_key = 'mlsimport_ListingId' SET L.listing_id = LEFT( M.meta_value, 32 ) WHERE L.listing_id = '' AND M.meta_value <> ''" );
149
150	// v4 post-step: the "Featured listing" checkbox existed (and saved its post
151	// meta) before this column did, so carry any already-ticked listing across.
152	// Only rows still at 0 are touched, so a re-run changes nothing.
153	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
154	$wpdb->query( "UPDATE {$table} L INNER JOIN {$wpdb->postmeta} M ON M.post_id = L.post_id AND M.meta_key = 'mlsimport_featured' SET L.featured = 1 WHERE L.featured = 0 AND M.meta_value = '1'" );
155
156	// Record the schema version so maybe_upgrade() can skip until the next bump.
157	update_option( 'mlsimport_listings_db_version', self::DB_VERSION );
158	}
159
160	/**
161	* Run create() only when the stored schema version is out of date. Cheap
162	* enough to call on every admin_init.
163	*
164	* @return void
165	*/
166	public static function maybe_upgrade(): void {
167	// Stored version matches the code's DB_VERSION: nothing to do.
168	if ( get_option( 'mlsimport_listings_db_version' ) === self::DB_VERSION ) {
169	return;
170	}
171	// Out of date (or never created): (re)run the idempotent CREATE TABLE.
172	self::create();
173	}
174	}
175