Error when adding "name" and "permalink" as fields to an Advanced Content Type -- Implement dbDelta() in PodsAPI

[coding-panda]

Description

When adding adding "name" as a field to an Advanced Content Type, it displays a SQL error, but after saving and refreshing the page, it will appear. Same problem to "permalink"

Version

2.8.5

Testing Instructions

1. create an Advanced Content Type table
2. add "name" as a field

Screenshots / Screencast

Possible Workaround

Leave "name" and "permalink" as default fields like pods 2.7, or remove the errors and so they can just be added.

Site Health Information

`
### wp-core ###

version: 5.8.2
site_language: en_GB
user_language: en_GB
timezone: Europe/London
permalink: /%postname%/
https_status: false
multisite: false
user_registration: 0
blog_public: 0
default_comment_status: open
environment_type: production
user_count: 1
dotorg_communication: true

### wp-paths-sizes ###

wordpress_path: F:\xampp\htdocs\pprf-from-user
wordpress_size: 47.11 MB (49395080 bytes)
uploads_path: F:\xampp\htdocs\pprf-from-user/wp-content/uploads
uploads_size: 1.10 MB (1158652 bytes)
themes_path: F:\xampp\htdocs\pprf-from-user/wp-content/themes
themes_size: 8.02 MB (8413309 bytes)
plugins_path: F:\xampp\htdocs\pprf-from-user/wp-content/plugins
plugins_size: 66.75 MB (69987474 bytes)
database_size: 5.08 MB (5324800 bytes)
total_size: 128.06 MB (134279315 bytes)

### wp-active-theme ###

name: Twenty Twenty (twentytwenty)
version: 1.8
author: the WordPress team
author_website: https://en-gb.wordpress.org/
parent_theme: none
theme_features: core-block-patterns, widgets-block-editor, automatic-feed-links, custom-background, post-thumbnails, custom-logo, title-tag, html5, align-wide, responsive-embeds, customize-selective-refresh-widgets, editor-color-palette, editor-font-sizes, editor-styles, widgets, menus, editor-style
theme_path: F:\xampp\htdocs\pprf-from-user/wp-content/themes/twentytwenty
auto_update: Disabled

### wp-themes-inactive (3) ###

Twenty Nineteen: version: 2.1, author: WordPress Team, Auto-updates disabled
Twenty Seventeen: version: 2.8, author: the WordPress team, Auto-updates disabled
Twenty Twenty-One: version: 1.4, author: the WordPress team, Auto-updates disabled

### wp-plugins-active (2) ###

Panda Pods Repeater Field: version: 1.4.11, author: Dongjie Xu, Auto-updates disabled
Pods - Custom Content Types and Fields: version: 2.8.5, author: Pods Framework Team, Auto-updates disabled

### wp-plugins-inactive (2) ###

Elementor: version: 3.4.8, author: Elementor.com, Auto-updates disabled
Elementor Pro: version: 3.4.1, author: Elementor.com, Auto-updates disabled

### wp-media ###

image_editor: WP_Image_Editor_GD
imagick_module_version: Not available
imagemagick_version: Not available
imagick_version: Not available
file_uploads: File uploads is turned off
post_max_size: 512M
upload_max_filesize: 5500M
max_effective_size: 512 MB
max_file_uploads: 20
gd_version: bundled (2.1.0 compatible)
gd_formats: GIF, JPEG, PNG, WebP, BMP, XPM
ghostscript_version: not available

### wp-server ###

server_architecture: Windows NT 10.0 AMD64
httpd_software: Apache/2.4.47 (Win64) OpenSSL/1.1.1k PHP/8.0.6
php_version: 8.0.6 64bit
php_sapi: apache2handler
max_input_variables: 1000
time_limit: 1800
memory_limit: 512M
max_input_time: 600
upload_max_filesize: 5500M
php_post_max_size: 512M
curl_version: 7.75.0 OpenSSL/1.1.1k
suhosin: false
imagick_availability: false
pretty_permalinks: true
htaccess_extra_rules: false

### wp-database ###

extension: mysqli
server_version: 10.4.19-MariaDB
client_version: mysqlnd 8.0.6

### wp-constants ###

WP_HOME: undefined
WP_SITEURL: undefined
WP_CONTENT_DIR: F:\xampp\htdocs\pprf-from-user/wp-content
WP_PLUGIN_DIR: F:\xampp\htdocs\pprf-from-user/wp-content/plugins
WP_MEMORY_LIMIT: 40M
WP_MAX_MEMORY_LIMIT: 512M
WP_DEBUG: true
WP_DEBUG_DISPLAY: false
WP_DEBUG_LOG: true
SCRIPT_DEBUG: true
WP_CACHE: false
CONCATENATE_SCRIPTS: undefined
COMPRESS_SCRIPTS: undefined
COMPRESS_CSS: undefined
WP_LOCAL_DEV: undefined
DB_CHARSET: utf8mb4
DB_COLLATE: undefined

### wp-filesystem ###

wordpress: writable
wp-content: writable
uploads: writable
plugins: writable
themes: writable

### pods ###

pods-server-software: Apache/2.4.47 (Win64) OpenSSL/1.1.1k PHP/8.0.6
pods-user-agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/94.0.4606.61 Safari/537.36
pods-session-save-path: F:\xampp\tmp
pods-session-save-path-exists: Yes
pods-session-save-path-writable: Yes
pods-session-max-lifetime: 1440
pods-opcode-cache-apc: No
pods-opcode-cache-memcached: No
pods-opcode-cache-opcache: No
pods-opcode-cache-redis: No
pods-object-cache-apc: No
pods-object-cache-apcu: No
pods-object-cache-memcache: No
pods-object-cache-memcached: No
pods-object-cache-redis: No
pods-memory-current-usage: 48.983M
pods-memory-current-usage-real: 8.000M
pods-network-wide: No
pods-install-location: F:\xampp\htdocs\pprf-from-user\wp-content\plugins\pods/
pods-developer: No
pods-tableless-mode: No
pods-relationship-table-enabled: Yes
pods-light-mode: No
pods-strict: No
pods-allow-deprecated: Yes
pods-api-cache: Yes
pods-shortcode-allow-evaluate-tags: Yes
pods-sessions: Enable sessions
pods-can-use-sessions: Yes

`

Pods Package

{
    "meta": {
        "version": "2.8.5",
        "build": 1638125568
    },
    "pods": [
        {
            "name": "new_table",
            "id": 2615,
            "label": "New tables",
            "description": "",
            "type": "pod",
            "storage": "table",
            "label_singular": "New table",
            "public": "1",
            "show_ui": "1",
            "pod_index": "name",
            "_migrated_28": "1",
            "required": "0",
            "unique": "0",
            "groups": [
                {
                    "name": "details",
                    "id": 2616,
                    "label": "Details",
                    "description": "",
                    "weight": 0,
                    "fields": [
                        {
                            "name": "name",
                            "id": 2617,
                            "label": "name",
                            "description": "",
                            "weight": 0,
                            "type": "text",
                            "sister_id": "-- Select One --",
                            "required": "0",
                            "unique": "0",
                            "text_allowed_html_tags": "strong em a ul ol li b i",
                            "text_max_length": "255",
                            "roles_allowed": "administrator"
                        },
                        {
                            "name": "permalink",
                            "id": 2620,
                            "label": "permalink",
                            "description": "",
                            "weight": 3,
                            "type": "text",
                            "sister_id": "-- Select One --",
                            "required": "0",
                            "unique": "0",
                            "text_allowed_html_tags": "strong em a ul ol li b i",
                            "text_max_length": "255",
                            "roles_allowed": "administrator"
                        }
                    ]
                }
            ]
        }
    ]
}

[sc0ttkclark]

I think the solution here is to switch from individual alter statements and use dbDelta() going forward as it's more resilient for issues like these.

I'm going to move this to the next release but noting the path to fixing as follows:

• Update PodsAPI::save_pod_table_schema() method to include new parameter to set which field is being updated (if updating a specific field), maybe run ALTER TABLE query to rename field
• Update PodsAPI::save_pod_table_schema() method to run rename table query if name changed (first) and then run dbDelta() to ensure table schema matches up to full definitions of all fields
• Update PodsAPI::save_pod_table_schema() method to include a filter to use plain ALTER TABLE queries (default: use dbDelta) for updating field definitions -- if a specific field is provided to the method, it should run just that ALTER TABLE query for that one field based on logic provided
• Update PodsAPI class queries to ALTER TABLE and CREATE TABLE should all be calling PodsAPI::save_pod_table_schema()