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

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

114 lines 3.5 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	private const DB_VERSION = '1';
25
26	/**
27	* Fully-qualified table name (with the site's table prefix).
28	*
29	* @return string
30	*/
31	public static function table_name(): string {
32	global $wpdb;
33	return $wpdb->prefix . 'mlsimport_listings';
34	}
35
36	/**
37	* Create or upgrade the table via dbDelta(), then record the schema version.
38	*
39	* @return void
40	*/
41	public static function create(): void {
42	global $wpdb;
43
44	// dbDelta() lives in the admin upgrade file, not loaded on the front end.
45	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
46
47	// Prefixed table name + the site's charset/collation for the CREATE TABLE.
48	$table = self::table_name();
49	$collate = $wpdb->get_charset_collate();
50
51	// One row per listing; one column per filterable/sortable field, plus indexes
52	// on the common filter/sort combinations and a FULLTEXT index for keywords.
53	$sql = "CREATE TABLE {$table} (
54	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
55	listing_key VARCHAR(64) NOT NULL DEFAULT '',
56	post_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
57	mls_id VARCHAR(64) NOT NULL DEFAULT '',
58	modification_timestamp DATETIME DEFAULT NULL,
59	price DECIMAL(15,2) DEFAULT NULL,
60	bedrooms DECIMAL(5,1) DEFAULT NULL,
61	bathrooms DECIMAL(5,1) DEFAULT NULL,
62	living_area DECIMAL(12,2) DEFAULT NULL,
63	lot_size DECIMAL(12,2) DEFAULT NULL,
64	year_built SMALLINT UNSIGNED DEFAULT NULL,
65	latitude DECIMAL(10,7) DEFAULT NULL,
66	longitude DECIMAL(10,7) DEFAULT NULL,
67	city VARCHAR(128) NOT NULL DEFAULT '',
68	state VARCHAR(64) NOT NULL DEFAULT '',
69	zip VARCHAR(16) NOT NULL DEFAULT '',
70	property_type VARCHAR(64) NOT NULL DEFAULT '',
71	listing_type VARCHAR(32) NOT NULL DEFAULT '',
72	status VARCHAR(32) NOT NULL DEFAULT '',
73	list_date DATETIME DEFAULT NULL,
74	garage_spaces SMALLINT DEFAULT NULL,
75	stories SMALLINT DEFAULT NULL,
76	hoa_fee DECIMAL(10,2) DEFAULT NULL,
77	days_on_market INT DEFAULT NULL,
78	subdivision VARCHAR(128) NOT NULL DEFAULT '',
79	search_text TEXT,
80	PRIMARY KEY (id),
81	UNIQUE KEY listing_key (listing_key),
82	KEY post_id (post_id),
83	KEY modification_timestamp (modification_timestamp),
84	KEY status_type_price (status, property_type, price),
85	KEY city_status_price (city, status, price),
86	KEY lat_lng (latitude, longitude),
87	KEY price (price),
88	KEY list_date (list_date),
89	FULLTEXT KEY search_text (search_text)
90	) {$collate};";
91
92	// dbDelta diffs the schema and creates/alters columns/indexes as needed.
93	dbDelta( $sql );
94
95	// Record the schema version so maybe_upgrade() can skip until the next bump.
96	update_option( 'mlsimport_listings_db_version', self::DB_VERSION );
97	}
98
99	/**
100	* Run create() only when the stored schema version is out of date. Cheap
101	* enough to call on every admin_init.
102	*
103	* @return void
104	*/
105	public static function maybe_upgrade(): void {
106	// Stored version matches the code's DB_VERSION: nothing to do.
107	if ( get_option( 'mlsimport_listings_db_version' ) === self::DB_VERSION ) {
108	return;
109	}
110	// Out of date (or never created): (re)run the idempotent CREATE TABLE.
111	self::create();
112	}
113	}
114