George Stephanis

Code monkey get up, get coffee…

Category: MySQL

  • Building Better Workflows with Claude and WordPress Studio

    Building Better Workflows with Claude and WordPress Studio

    One of the most beneficial aspects of Claude and agentic development is being able to give it a persistent memory of how to interact with the codebase and what the codebase is. This will let you work faster and more efficiently — after all, wouldn’t you work faster if you were given an introduction to a codebase instead of just shown the code and told to ‘figure it out’?

    For my own setup, I love working with WordPress Studio — it’s tightly integrated with Playground behind the scenes, and runs off an SQLite database, which makes it far less intrusive and likely to conflict with other local development environments or servers you may already have running — and trivial to sync between environments!

    .claude/settings.json

    Firstly, you can mitigate some of the interruptions about Claude prompting you for permission to do specific tasks, but granting them explicitly in advance. For example, we can add the following rules:

    settings.json
    JSON
    {
      "permissions": {
        "allow": [
          "WebFetch(domain:SITESUBDOMAIN.wp.local)",
          "WebFetch(domain:wordpress.org)",
          "Bash(sqlite3 wp-content/database/.ht.sqlite:*)",
          "Bash(studio:*)"
        ]
      }
    }

    Which will enable Claude to do things without needing to stop and confirm as frequently. Some of these might not make too much sense yet, but we’ll get to them below!

    CLAUDE.md

    CLAUDE.md is a file giving Claude a rough overview of what’s going on and directives. The sort of thing you want Claude to know and remember, but don’t want to have to repeat yourself on.

    CLAUDE.md
    Markdown
    @AGENTS.md

    There is also a movement to do a more general AGENTS.md file — most of these would cross-apply, and you can in fact include them in AGENTS.md and just include that in CLAUDE.md via

    CLAUDE.md
    Markdown
    ## What This Repository Is
    A WordPress 6.9.4 development environment using SQLite as the database backend, managed by [WordPress Studio](https://developer.wordpress.com/docs/developer-tools/studio/cli/). The active development focus is the `plugin-name` Gutenberg block plugin, which does things.
    **Local URL:** https://SITESUBDOMAIN.wp.local/

    A high-level summary of the installation and how Claude can interact with it. Providing a local URL to be queried lets it check front-end output as needed, instead of constantly asking you for the current state or handling contingent decision paths that aren’t actually relevant.

    CLAUDE.md
    Markdown
    ## Environment Management (WordPress Studio)
    The local server is managed by WordPress Studio. Key CLI commands:
    ```bash
    studio site start           # Start the local site
    studio site stop            # Stop the local site
    studio site status          # Check if the site is running
    studio wp <wp-cli-command>  # Run WP-CLI commands in the Studio context (no separate WP-CLI install needed)
    ```
    Full CLI reference: https://developer.wordpress.com/docs/developer-tools/studio/cli/

    Make sure to enable Studio CLI in Studio’s settings! Once that’s up and running, enabling WP-CLI through Studio can open up a world of tooling for Claude to use and run your setup.

    CLAUDE.md
    Markdown
    ## Plugin Development (plugin-name)
    All plugin source lives in `wp-content/plugins/plugin-name/`. This directory has its own git repository.
    @wp-content/plugins/plugin-name/CLAUDE.md

    I tend to work off a repository hosting the plugin itself, rather than the whole install in the repository. This lets the plugin host its own `CLAUDE.md` file that gets included in the root and describes the plugin’s inner workings, without duplicating that here and having multiple locations to keep updated.

    CLAUDE.md
    Markdown
    ## Architecture
    ### Database
    Uses SQLite instead of MySQL. The database file is at `wp-content/database/.ht.sqlite`. The SQLite Database Integration plugin (`wp-content/mu-plugins/sqlite-database-integration/`) provides the driver via the `wp-content/db.php` drop-in.
    The system `sqlite3` binary is available and can be used to query the database directly when needed:
    ```bash
    sqlite3 wp-content/database/.ht.sqlite "SELECT * FROM wp_options WHERE option_name = 'siteurl';"
    ```

    Often it’s easier to have Claude look directly at the database to validate assumptions or check why something’s linking the way it is. Rather than have it rediscover how to each time, let’s just give it a quick summary and directions for how-to!

    A Plugin’s CLAUDE.md

    As we saw above, it’s often useful to include the plugin’s CLAUDE.md in the installation’s — it may only dig two directories down to discover them on its own and miss something in wp-content/plugins/plugin-name/ otherwise.

    wp-content/plugins/plugin-name/CLAUDE.md
    Markdown
    ## Architecture

    This will vary from project to project — honestly, the easiest way to generate it is probably to have Claude generate phpdoc syntax for all your files and functions that don’t have it yet and then ask Claude to document it for you. This may not be perfect, but it’ll generally make a point of noting the salient details that it would want to find in the future.

    wp-content/plugins/plugin-name/CLAUDE.md
    Markdown
    ## Build System
    Uses `@wordpress/scripts` (wraps webpack + Babel). The `--blocks-manifest` flag auto-generates `build/blocks-manifest.php`.
    ```bash
    npm run build       # Production build
    npm run start       # Development watch mode
    npm run lint:js     # JavaScript lint
    npm run lint:css    # CSS/SCSS lint
    npm run format      # Auto-format code
    npm run plugin-zip  # Create distributable zip
    ```
    > Always run a build before testing PHP-side block registration changes — the manifest is generated at build time.
    ## PHP Linting (WPCS / PHPCS)
    PHP coding standards are enforced via [WordPress Coding Standards](https://github.com/WordPress/WordPress-Coding-Standards). Config is in `phpcs.xml.dist`.
    ```bash
    composer install          # First time — installs PHPCS + WPCS into vendor/
    composer lint:php         # Run PHPCS
    composer lint:php:fix     # Run PHPCBF (auto-fix)
    ```

    Document your build system, along with relevant commands for Claude to use.

    wp-content/plugins/plugin-name/CLAUDE.md
    Markdown
    ## Code Style
    This project follows **WordPress Core coding standards** throughout, enforced by linting.
    ### PHP
    - [WordPress PHP Coding Standards](https://developer.wordpress.org/coding-standards/wordpress-coding-standards/php/): Yoda conditions, `array()` (not `[]`), tabs for indentation, spaces inside parentheses, `snake_case` for functions/variables, `PascalCase` for classes.
    - Escape all output (`esc_html__()`, `esc_url()`, `esc_attr()`). Sanitize all input (`sanitize_text_field()`, `wp_unslash()`). Use `check_admin_referer()` for nonce verification before processing POST data.
    - No direct database calls; use WordPress option and transient APIs.
    ### JavaScript / JSX
    - [WordPress JavaScript Coding Standards](https://developer.wordpress.org/coding-standards/wordpress-coding-standards/javascript/): tabs for indentation, spaces inside braces and parentheses, single quotes.
    - Use `const`/`let`, never `var`.
    - All user-visible strings must be wrapped in `__()` or `sprintf()` from `@wordpress/i18n`. `sprintf()` calls require a `/* translators: ... */` comment **inside the same JSX expression block** as the call (not on a separate line).
    - Avoid flanking whitespace in translation strings — use `{ ' ' }` for explicit spaces between JSX nodes.
    - `/* global SomeGlobal */` comments are required for browser globals not in the default ESLint environment (e.g. `MutationObserver`), and for WP-localized script globals (e.g. `bolPardot`).
    ### CSS / SCSS
    - [WordPress CSS Coding Standards](https://developer.wordpress.org/coding-standards/wordpress-coding-standards/css/): tabs for indentation, space before `{`, lowercase properties.
    - Order pseudo-class selectors least-specific first: `&:disabled` before `&:focus` before `&:hover`.
    - Avoid `input, textarea` combined rules when textarea needs additional properties — use separate blocks to satisfy `no-descending-specificity`.
    - Use CSS custom property fallback chains: block-level `--bol-*` first, then `--global-palette*` (Kadence, if active), then `--wp--preset--*`, then a hardcoded default. Never rely on any single source being present.

    It’s worthwhile to set up not only this but also something like Prettier to normalize things as you go — and doing so will avoid some IDEs accidentally using spaces instead of tabs or the like and leading to inconsistencies and extra fixer commits down the road.

    wp-content/plugins/plugin-name/CLAUDE.md
    Markdown
    ## Debugging approach
    - **Check actual output before speculating.** When diagnosing a frontend issue, make an HTTP request to the local site (`https://pardot.wp.local/`) and inspect the rendered HTML or loaded CSS first. This is faster and more reliable than reasoning about what the output might be.
    - **Ask the user questions early.** If a problem has multiple possible causes, ask a targeted question rather than running through all hypotheses. The user can often point you straight to the answer.

    I’ve seen Claude get incredibly introspective sometimes — it will consider if the bug being reported actually existed, it will do entire rabbit holes of considering potential problems on a five minute loop, when it could just ask me a five second question and continue on.

    Agentics can’t take Vyvanse, so we’ll try to keep it on task and have it communicate when it needs to.

    wp-content/plugins/plugin-name/CLAUDE.md
    Markdown
    ## Before marking any task as done
    1. Run **both** linters and fix any reported issues:
    ```bash
    npm run lint:js && npm run lint:css
    composer lint:php
    ```
    2. If the task adds or changes user-visible behaviour, update the **Help tab** in `includes/class-bol-admin-page.php` (`render_help_tab()`).
    3. Update **`CLAUDE.md`** and **`.github/copilot-instructions.md`** with any architectural changes.
    4. If any translatable strings were added, changed, or removed (in PHP or JS), regenerate the POT file:
    ```bash
    studio wp --path=/Users/georgestephanis/Studio/pardot i18n make-pot wp-content/plugins/big-orange-pardot wp-content/plugins/big-orange-pardot/languages/big-orange-pardot.pot --domain=big-orange-pardot
    ```

    Modern projects have many moving parts — and something like this helps to keep all the associated bits linked together properly and updated as needed.

    The easiest way to set up WPCS is just to ask Claude (or Copilot, or others) to do it for you.

    Conclusion

    Hopefully this will give you some useful ideas for how to optimize your workflows and build tools. Have I missed some useful steps that you’ve found beneficial? Please let me know in the comments below and share your expertise with everyone!

  • Two Coins Bug

    There’s an old riddle I heard while growing up that I used to hate.

    You have two coins that add up to 30 cents, but one isn’t a nickel. What are the two coins?

    The answer, of course, is a quarter and a nickel. Like many riddles, it comes down to an assumption we make when parsing the question. In this case, it’s absolutely true that one of the coins isn’t a nickel — but the other one is.

    This riddle felt particularly salient to some work I was doing in WordPress this week, around the logic of MySQL queries and the WP_Meta_Query functionality.

    As sometimes an example can explain a bit more than theoretical, consider the following:

    Post ID: 50, has a title of "Fifty", and two separate rows in the postmeta table, both under the 'color' key -- 'red', and 'blue'.

Post ID: 51, has a title of "Fiftyone", and one row in the postmeta table, under the 'color' key -- 'red'

    How would you query for all posts that have color of red, but do not have color of blue? My first attempt was something along the lines of this:

    get_posts(
  array(
    'meta_query' => array(
      array(
        'key' => 'color',
        'compare' => '=',
        'value' => 'red',
      ),
      array(
        'key' => 'color',
        'compare' => '!=',
        'value' => 'blue',
      ),
    ),
  )
)

    However, to my initial surprise, this returned both ID 50 and ID 51! So I pulled up the query to see why that could be, and it turned out the generated SQL looked something like:

    SELECT *
FROM wp_posts
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id )
WHERE ( wp_postmeta.meta_key = 'color' AND wp_postmeta.meta_value = 'red' )
AND ( mt1.meta_key = 'color' AND mt1.meta_value != 'blue' )
GROUP BY wp_posts.ID

    (slightly simplified)

    Well, you would ask, how does that return post ID 50? Well, because both of the joins to the meta table wind up finding the same meta! We say not to return a result with a meta key of blue, but SQL interprets that as find me any entry with a meta key that is not blue — and as post ID 50 also has a meta key that is red (which is not blue) we’re in business!

    Anyway, finding the absence of something in the where clause is tricky — as the where clause specifies what you want to select. I’d looked and I’m pretty sure just using what’s already there in the WP_Meta_Query class it’s not possible to properly structure this, I’d done some work on it a decade ago, implementing the EXISTS and NOT EXISTS comparators.

    So we could get the result we wanted, if we rewrote the above MySQL to:

    SELECT *
FROM wp_posts
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
LEFT JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id AND mt1.meta_key = 'color' AND mt1.meta_value = 'blue' )
WHERE ( wp_postmeta.meta_key = 'color' AND wp_postmeta.meta_value = 'red' )
AND ( mt1.post_id IS NULL )
GROUP BY wp_posts.ID

    The rough idea is that it will attempt to do a left join instead of an inner join, which could not turn up any results from the joined table — so we explicitly want to query for rows where a column that would have to be there — post_id — being NULL to ensure that there was nothing for it to join to. Thereby ensuring it NO HAZ any blue values.

    So here’s what I wound up writing. It’s still rough, but functional thus far in my testing (so long as the NO HAZ isn’t the first parameter in the meta_query — as written the regex relies on the aliasing of the table name to parse it out). There’s probably some cleanup that it needs, however …

    /**
 * An extra filter to let our custom `NO HAZ` meta_compare work!
 */
function no_haz_meta_compare( $sql, $queries, $type, $primary_table, $primary_id_column, $context ) {
	global $wpdb;

	foreach ( $queries as $key => $query ) {
		if ( ! empty( $query['compare'] ) && 'NO HAZ' === $query['compare'] ) {

			// If we've got a NO HAZ query, pull out the table alias it's using for the join.
			$matches = array();
			$search  = sprintf(
				'#\( (\w+)\.meta_key = \'%1$s\' AND (\w+)\.meta_value = \'%2$s\' \)#',
				preg_quote( $query['key'], '#' ),
				preg_quote( $query['value'], '#' )
			);
			if ( preg_match( $search, $sql['where'], $matches ) ) {
				$table_alias = esc_sql( $matches[1] );

				// And then rewrite the JOIN bit to do a LEFT join instead of inner, so it can check for null (absence)
				$sql['join'] = str_replace(
					array(
						sprintf(
							"INNER JOIN {$wpdb->postmeta} AS {$table_alias} ON ( {$wpdb->posts}.ID = {$table_alias}.post_id )",
							$matches[1]
						),
						sprintf(
							"LEFT JOIN {$wpdb->postmeta} AS {$table_alias} ON ( {$wpdb->posts}.ID = {$table_alias}.post_id )",
							$matches[1]
						),
					),
					$wpdb->prepare(
						"LEFT JOIN {$wpdb->postmeta} AS {$table_alias} ON ( {$wpdb->posts}.ID = {$table_alias}.post_id AND {$table_alias}.meta_key = %s AND {$table_alias}.meta_value = %s )",
						$query['key'],
						$query['value']
					),
					$sql['join']
				);

				// And now yeet the original WHERE bit and swap it to testing for `post_id` in the postmeta table being null -- or confirming that none were found.
				$sql['where'] = str_replace(
					$matches[0],
					"( {$table_alias}.post_id IS NULL )",
					$sql['where']
				);

			}
		}
	}

	return $sql;
}
add_filter( 'get_meta_sql', 'no_haz_meta_compare', 10, 6 );

    which would permit the following to return only post id 51:

    get_posts(
  array(
    'meta_query' => array(
      array(
        'key' => 'color',
        'compare' => '=',
        'value' => 'red',
      ),
      array(
        'key' => 'color',
        'compare' => 'NO HAZ',
        'value' => 'blue',
      ),
    ),
  )
)

    The code would probably be a lot cleaner if it was in WP_Meta_Query proper, instead of regex parsing it all out — or if there was a filter in `WP_Meta_Query::get_sql_for_clause` to allow overriding — but hopefully this serves as a useful proof of concept for someone else hitting a similar issue down the road.

  • Final-ish legislation.sql table structure

    The final (until I add a sponsors table) db structure for the legislation.  Works with the existing import script, with added indexes for easier querying.

  • PHP Legislation Parser + Importer

    What I’ve got so far.  More to come, but wanted to get this out there.

  • Legislation DB Dump

    Still not quite the final DB structure I’d like, but this is available for data mining and trying to build something awesome out of.

  • OpenDataDay Hackathon DC!

    So I went down to DC this weekend to participate in the Open Data Day Hackathon!  There were some tremendous projects proposed, but the one that caught my eye from the start of the day was one proposed by Jim Harper of the Cato Institute to track down the genealogy of legislation put forth in congress.

    Basically, the goal is to programatically find similar passages in multiple bills.  This can be used for many purposes, including looking at sections in large omnibus bills and getting an idea if the things that get shoehorned in it have been proposed previously, and what happened then.

    So, our team largely consisted of  myself, Alexander Furnas of the Sunlight Foundation, and John Bloch of 10up, with guidance from Jim Harper (previously mentioned, of the Cato Institute), Molly Bohmer (also of the Cato Institute), and Kirsten Gullickson providing some clarification on the way the XML data we were working with was structured.

    I spent my time building a MySQL database and a PHP import script that could map all the relevant data from the XML files in to it.

    Alexander worked in Python primarily fleshing out a way of doing Latent Semantic Analysis on the data we’ve extracted to sort out what is similar to what, and where can we find meaning in it.

    John spent his time working on a front-end for the final dataset, to help end-users get something useful out of the data we’re building.

    The data that we were pulling from can be readily accessed by anyone through the Library of Congress at the following URLs:

    I’m currently putting some finishing touches on the DB structure, but when that’s done, I’ll be releasing that and the import script in a subsequent post, as well as a SQL dump for the final accumulated and sorted data — ripe for data mining.  As the day was wrapping up, I had someone come to me inquiring about data mining for references to money allocated in appropriations bills and the like, and I was able to very quickly do a MySQL query along the lines of

    SELECT * FROM `resolution_text` WHERE `text` LIKE '$%'

    to find anything that started with a dollar sign and then listed an amount over a very limited data set of three million rows or such.  The final data set will be much larger.

  • Magento Duplicate Orders MySQL Search

    A handy MySQL query to check a Magento DB for potential duplicate orders!

    SELECT `quote_id`,
	COUNT(`quote_id`) AS `qty_duplicates`,
	`increment_id` AS `first_increment_id`,
	GROUP_CONCAT( `increment_id` SEPARATOR ' | ' ) AS `increment_ids`,
	`created_at`,
	`state`,
	`status`,
	`customer_firstname`,
	`customer_lastname`,
	`customer_email`,
	`grand_total`
FROM `sales_flat_order`
GROUP BY `quote_id`
HAVING COUNT(`quote_id`) > 1
ORDER BY `created_at` ASC