get_charset_collate(); /** * Create all listings table * details - for extra info that may be needed in future * */ $listings_table_name = $wpdb->prefix . 'lfzw_listings'; $listing_sql = "CREATE TABLE IF NOT EXISTS $listings_table_name ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, list_id VARCHAR(255) NOT NULL UNIQUE, title TEXT, address TEXT, city VARCHAR(100), state VARCHAR(100), zip VARCHAR(20), beds FLOAT, baths FLOAT, price VARCHAR(100), image TEXT, latitude DECIMAL(10, 6), longitude DECIMAL(10, 6), list_url TEXT, details JSON DEFAULT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) $charset_collate;"; dbDelta( $listing_sql ); /** * Create details table * Separated from listings table to reduce load time for all listings * Contains foreign key pointing to list_id of listings table (one-to-one mapping) * */ $details_table_name = $wpdb->prefix . 'lfzw_details'; $details_sql = "CREATE TABLE IF NOT EXISTS $details_table_name ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, list_id VARCHAR(255) NOT NULL UNIQUE, about TEXT, area VARCHAR(100), details_url TEXT, contact_info JSON DEFAULT NULL, metadata JSON DEFAULT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (list_id) REFERENCES {$wpdb->prefix}" . "lfzw_listings(list_id) ON DELETE CASCADE ) $charset_collate;"; dbDelta( $details_sql ); /** * Create images table * Contains foreign key pointing to list_id of listings table (many-to-one mapping) * */ $image_table_name = $wpdb->prefix . 'lfzw_images'; $sql = "CREATE TABLE IF NOT EXISTS {$image_table_name} ( id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, list_id VARCHAR(100) NOT NULL, image_url TEXT NOT NULL, image_alt TEXT DEFAULT NULL, PRIMARY KEY (id), FOREIGN KEY (list_id) REFERENCES {$listings_table_name}(list_id) ON DELETE CASCADE ) $charset_collate;"; dbDelta($sql); } }