Author: Derrick

  • Converting CSV to SQL

    Converting CSV to SQL

    I was recently working on an issue that required me to dig through gigabytes of CSV files to look for patterns and data points. I had no intention of bringing all of this data in to Excel, because there’s not enough RAM on earth for that to work. Instead I thought it would be easier to dump the data into MariaDB (MySQL would be fine as well) and just write some simple SQL statements.

    There were a few things I tried, such as LOAD DATA INFILE and other SQL type things. Nothing really worked properly, so instead I decided to just convert the CSV files into SQL statements I could import.

    This is a bad idea! Don’t ever let this touch a live production database!

    I wrote this little PHP script to convert all of the CSV files in a directory to SQL:

    <?php
    $files    = glob( './*.{csv}', GLOB_BRACE );
    $db_table = 'csv_table';
    
    foreach ( $files as $file ) {
    	$row       = 0;
    	$handle    = fopen( $file, 'rb' );
    	$row_names = '';
    
    	if ( false !== $handle ) {
    		while ( false !== ( $data = fgetcsv( $handle ) ) ) {
    			$row++;
    			$values = '';
    
    			if ( 1 === $row ) {
    				// Do Header things.
    				$index  = 0;
    
    				echo sprintf( 'DROP TABLE IF EXISTS `%s`;' . PHP_EOL, $db_table );
    				echo sprintf( 'CREATE TABLE `%s` (' . PHP_EOL, $db_table );
    
    				foreach ( $data as $key => $value ) {
    					$index++;
    					$data[ $key ] = strtolower( preg_replace( '/[^A-Za-z0-9]/', '_', $value ) );
    
    					// Add a comma if we're not at the end of the array.
    					if ( count( $data ) !== $index ) {
    						echo sprintf( '	`%s` text NOT NULL,' . PHP_EOL, $data[ $key ] );
    					} else {
    						echo sprintf( '	`%s` text NOT NULL' . PHP_EOL, $data[ $key ] );
    					}
    				}
    
    				echo ') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;' . PHP_EOL;
    				$row_names = '(`' . implode( '`,`', $data ) . '`)';
    				continue;
    			}
    
    			foreach ( $data as $key => $value ) {
    				$data[ $key ] = fake_sql_escape( $value );
    			}
    
    			$values = '(\'' . implode( '\',\'', $data ) . '\')';
    
    			echo sprintf( 'INSERT INTO `%s` %s VALUES %s;' . PHP_EOL, $db_table, $row_names, $values );
    		}
    		fclose( $handle );
    	}
    }
    
    function fake_sql_escape( $value ) {
    	// https://stackoverflow.com/questions/1162491/alternative-to-mysql-real-escape-string-without-connecting-to-db/1163091#1163091
    	// Totaly not safe for production!
    	$return = '';
    	$length = strlen( $value );
    
    	for ( $i = 0; $i < $length; ++$i ) {
    		$char = $value[ $i ];
    		$ord  = ord( $char );
    		if (
    			"'" !== $char &&
    			'"' !== $char &&
    			'\\' !== $char &&
    			$ord >= 32 &&
    			$ord <= 126
    		) {
    			$return .= $char;
    		} else {
    			$return .= '\\x' . dechex( $ord );
    		}
    	}
    	return $return;
    }Code language: PHP (php)

    Borrowing the example airtravel.csv from https://people.sc.fsu.edu/~jburkardt/data/csv/csv.html this is what the output looks like:

    DROP TABLE IF EXISTS `csv_table`;
    CREATE TABLE `csv_table` (
    	`month` text NOT NULL,
    	`1958` text NOT NULL,
    	`1959` text NOT NULL,
    	`1960` text NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('JAN','  340','  360','  417');
    INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('FEB','  318','  342','  391');
    INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('MAR','  362','  406','  419');
    INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('APR','  348','  396','  461');
    INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('MAY','  363','  420','  472');
    INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('JUN','  435','  472','  535');
    INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('JUL','  491','  548','  622');
    INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('AUG','  505','  559','  606');
    INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('SEP','  404','  463','  508');
    INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('OCT','  359','  407','  461');
    INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('NOV','  310','  362','  390');
    INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('DEC','  337','  405','  432');Code language: JavaScript (javascript)

    And if you’re too lazy to see what the input CSV looks like (I would be) here it is, in its LGPL glory:

    "Month", "1958", "1959", "1960"
    "JAN",  340,  360,  417
    "FEB",  318,  342,  391
    "MAR",  362,  406,  419
    "APR",  348,  396,  461
    "MAY",  363,  420,  472
    "JUN",  435,  472,  535
    "JUL",  491,  548,  622
    "AUG",  505,  559,  606
    "SEP",  404,  463,  508
    "OCT",  359,  407,  461
    "NOV",  310,  362,  390
    "DEC",  337,  405,  432Code language: JavaScript (javascript)

    There’s a lot of ways that this could be made better, but it works in a quick pinch. If you need something similar, feel free to take this as a starting point.

  • Quick Tip: Looping a command in bash

    Quick Tip: Looping a command in bash

    I recently came across the need to watch my disk space while running a slow program to make sure I didn’t run out. If I did, the command would fail silently and I’d have to start over again.

    This can easily be done with this short snippet:

    watch -n10 bash -c "df -h | ack disk1s5"Code language: JavaScript (javascript)
    Every 10.0s: bash -c df -h | ack disk1s5                                                                                  mbp.local: Mon Jul 20 15:09:51 2020
    
    /dev/disk1s5           488245288  10940872 144033352     8%   488237 4881964643    0%   /

    The important part here is disk1s5, which is the device file for the partition I wanted to watch. If you need to find this, it can be done simply by running the df as a whole:

    $ df
    Filesystem             1K-blocks      Used Available Capacity  iused      ifree %iused  Mounted on
    /dev/disk1s5           488245288  10940872 144035124     8%   488237 4881964643    0%   /
    devfs                        191       191         0   100%      662          0  100%   /dev
    /dev/disk1s1           488245288 331456068 144035124    70%  1379027 4881073853    0%   /System/Volumes/Data
    /dev/disk1s4           488245288   1048596 144035124     1%        1 4882452879    0%   /private/var/vm
    map auto_home                  0         0         0   100%        0          0  100%   /System/Volumes/Data/home
    /dev/disk1s3           488245288    516448 144035124     1%       48 4882452832    0%   /Volumes/RecoveryCode language: PHP (php)

    That is all.

    Photo by Wendy Wei from Pexels

  • Half-Baked Plugins: Embeds for Twitch and Gutenberg

    Half-Baked Plugins: Embeds for Twitch and Gutenberg

    In my forever attempt to learn and understand Gutenberg, React, and ES6 (which I am failing at horribly) I sometimes build WordPress plugins just for the learning experience. I don’t have any full intent on releasing these to the WordPress.org Plugin Repository because I honestly don’t feel like dealing with end users and support. I’m a horrible person, I know.

    One that I’ve got in my barrel of cobwebs is Embeds for Twitch and Gutenberg

    Allows Twitch channels, videos, and clips to be auto-embedded.
    https://github.com/emrikol/embed-twitch
    1 forks.
    1 stars.
    6 open issues.

    Recent commits:

    This allows you to embed Twitch channels, videos, and clips as a Gutenberg block in WordPress. Here’s some examples:

    Twitch Channel

    Twitch Video

    Twitch Clip

    Note: It seems that sometime between me finishing the current version of the plugin and the publishing of this post, Clips are borked. 🤷‍♂️

    Stay tuned for our next episode of Half-Baked Plugins where I do stupid things with images.

  • Deleting Old Post Revisions in WordPress with WP-CLI

    Deleting Old Post Revisions in WordPress with WP-CLI

    Recently I’ve been working with a client who’s site we’re going to soon be migrating. To help with any downtime, we’ve been looking at reducing their database size, which is something around 50-60 gigabytes. After looking through the database, one easy win would be to purge as many post revisions as possible, since their wp_posts table was about 50% revisions 😱

    We couldn’t just mass delete anything with a revision post type though, because the client had some specific needs:

    • Keep all revisions for posts from the last 12 months.
    • Keep all revisions that were made after the post was published.
    • Keep a backup of all revisions deleted.

    To do this, I crafted a custom WP-CLI command to purge these revisions. I’ve accidentally deleted the final version of the script, since it was only a one-run thing, but here’s an earlier version that could be a good starting point for anyone else that has a similar need to prune revisions:

    /**
     * WP-CLI command that deletes pre-publish post revisions for posts older than 12 months.
     *
     * @subcommand prune-revisions [--live] [--verbose]
     */
    public function prune_revisions( $args, $assoc_args ) {
    	global $wpdb;
    
    	$live    = (bool) $assoc_args[ 'live' ];
    	$verbose = (bool) $assoc_args[ 'verbose' ];
    	$offset  = 0;
    	$limit   = 500;
    	$count   = 0;
    	$deletes = 0;
    
    	if ( $live ) {
    		$output_file = sanitize_file_name( sprintf( 'prune-revisions-backup_%d_%d.csv', get_bloginfo( 'name' ), current_time( 'timestamp', true ) ) );
    		$handle      = fopen( $output_file, 'wb' );
    
    		if ( false === $handle ) {
    			WP_CLI::error( sprintf( 'Error opening %s for writing!', $output_file ) );
    		}
    
    		// Headers.
    		$csv_headers = array(
    			'ID',
    			'post_author',
    			'post_date',
    			'post_date_gmt',
    			'post_content',
    			'post_title',
    			'post_excerpt',
    			'post_status',
    			'comment_status',
    			'ping_status',
    			'post_password',
    			'post_name',
    			'to_ping',
    			'pinged',
    			'post_modified',
    			'post_modified_gmt',
    			'post_content_filtered',
    			'post_parent',
    			'guid',
    			'menu_order',
    			'post_type',
    			'post_mime_type',
    			'comment_count',
    			'filter',
    		);
    
    		fputcsv(
    			$handle,
    			$csv_headers
    		);
    	}
    
    	$count_sql      = 'SELECT COUNT(ID) FROM ' . $wpdb->posts . ' WHERE post_type = "revision"';
    	$revision_count = (int) $wpdb->get_row( $count_sql, ARRAY_N )[0];
    	$progress       = \WP_CLI\Utils\make_progress_bar( sprintf( 'Checking %s revisions', number_format( $revision_count ) ), $revision_count );
    
    	do {
    		$sql       = $wpdb->prepare( 'SELECT ID FROM ' . $wpdb->posts . ' WHERE post_type = "revision" LIMIT %d,%d', $offset, $limit );
    		$revisions = $wpdb->get_results( $sql );
    
    		foreach ( $revisions as $revision ) {
    			$count++;
    			$post_parent_id = wp_get_post_parent_id( $revision->ID );
    
    			// Fail on either 0 or false.
    			if ( false == $post_parent_id ) {
    				WP_CLI::warning( sprintf( 'Revision %d does not have a parent!  Skipping!', $revision->ID ) );
    				continue;
    			}
    
    			$revision_modified   = get_post_modified_time( 'U', false, $revision->ID );
    			$parent_publish_time = get_post_time( 'U', false, $post_parent_id );
    
    			if ( $parent_publish_time < current_time( 'timestamp') - ( MONTH_IN_SECONDS * 12 ) ) {
    				// Post is older than 12 months, safe to delete pre-publish revisions.
    				if ( $revision_modified < $parent_publish_time ) {
    					if ( $live ) {
    						// We're doing it live!
    						WP_CLI::log( sprintf( 'Deleting revision %d for post %d. (%d%% done)', $revision->ID, $post_parent_id, ( $count / $revision_count ) * 100 ) );
    
    						// Backup data!
    						$output = [];
    						$data   = get_post( $revision->ID );
    
    						// Validate the field is set, just in case.  IDK how it couldn't be.
    						foreach ( $csv_headers as $field ) {
    							$output = isset( $data->$field ) ? $data->$field : '';
    						}
    
    						fputcsv( $handle, $output );
    
    						$did_delete = wp_delete_post_revision( $revision->ID );
    
    						// Something went wrong while deleting the revision?
    						if ( false === $did_delete || is_wp_error( $did_delete ) ) {
    							WP_CLI::warning( sprintf( 'Revision %d for post %d DID NOT DELETE! wp_delete_post_revision returned:', $revision->ID, $post_parent_id ) );
    						}
    						$deletes++;
    
    						// Pause after lots of db modifications.
    						if ( 0 === $deletes % 50 ) {
    							if ( $verbose ) {
    								WP_CLI::log( sprintf( 'Current Deletes: %d', $deletes ) );
    							}
    							sleep( 1 );
    						}
    					} else {
    						// Not live, just output info.
    						WP_CLI::log( sprintf( 'Will delete revision %d for post %d.', $revision->ID, $post_parent_id ) );
    					}
    				} else {
    					// Revision is after the post has been published.
    					if ( $verbose ) {
    						WP_CLI::log( sprintf( 'Post-Publish: Will NOT delete Revision %d for post %d.', $revision->ID, $post_parent_id ) );
    					}
    				}
    			} else {
    				// Post is too new to prune.
    				if ( $verbose ) {
    					WP_CLI::log( sprintf( 'Too-New: Will NOT delete Revision %d for post %d.', $revision->ID, $post_parent_id ) );
    				}
    			}
    		}
    
    		// Pause after lots of db reads.
    		if ( 0 === $count % 5000 ) {
    			if ( $verbose ) {
    				WP_CLI::log( sprintf( 'Current Count: %d', $count ) );
    			}
    			sleep( 1 );
    		}
    
    		// Free up memory.
    		$this->stop_the_insanity();
    
    		// Paginate.
    		if ( count( $revisions ) ) {
    			$offset += $limit;
    			$progress->tick( $limit );
    		} else {
    			WP_CLI::warning( 'Possible MySQL Error, retrying in 10 seconds!' );
    			sleep( 10 );
    		}
    
    	} while ( $count < $revision_count );
    
    	$progress->finish();
    
    	if ( $live ) {
    		fclose( $handle );
    		WP_CLI::success( sprintf( 'Deleted %d revisions', $deleted ) );
    	} else {
    		WP_CLI::success( sprintf( 'Processed %d revisions', $revision_count ) );
    	}
    }Code language: PHP (php)
  • Five Years

    Five Years

    This past Monday marked my five year anniversary at Automattic, working for WordPress VIP. It seems forever ago, but at 9:59AM on February 11, 2015 I emailed jobs@automattic.com with my resume built as a child theme to twentyfifteen. Somehow I must have tricked them because 22 days later (which is pretty fast for a reply from Automattic back then 😬), interviewed, code tested, trialed, and ended up sticking around.

    I’m not going to go through the boring details of how hiring works at Automattic. Many great friends and bloggers better than me have written about it. I would though like to highlight a few of my favorite parts of my five year journey.

    The first few weeks and months were a bit surreal, trying to just get caught up to speed in the chaos. Spring turned into Summer, and I came across my first real moment of panic: Travel.

    I was asked to go to New York to meet with some clients, and attend the BigWP meetup. Now, for those of you that don’t know me, I’m born and raised from Nowhere, USA where we don’t really travel much. Luckily, it ended up great! BigWP was a blast, and meeting up with coworkers in real life, traveling around NY, and visiting clients really set in stone that the work that I was doing mattered to people.

    https://x.com/crushgear/status/643925830742212608

    It wasn’t a month later that I was off again on another adventure: my first Grand Meetup. The Grand Meetup is the once-a-year gathering of everyone from Automattic where we teach, learn, work, play, and really connect. For being a fully distributed company, this face to face time invigorates me more than anything and gets me excited about the work we still have to do. So, in October of 2015 I went to Park City.

    https://x.com/WordPressVIP/status/654794938903691264

    At that time VIP felt like a good size, and tiny in comparison to the larger company of 400 Automatticians:

    Fast forward a few more months and I’m headed to my first team meetup, in Barcelona. By now, I’ve flown a few times, so I’ve got this down, but overseas, that was another frightening first.

    https://x.com/mattoperry/status/694521200240910337

    That meetup in Barcelona, my first meetup, will have a special place in my heart forever. I don’t think anything can replace that feeling of fear, excitement, esprit de corps, and so many other emotions. From having coffee at a cafe at nearly midnight with a couple of friends just chatting about life, to actually working together to build our business plans for the year ahead–even thinking back about it now, it seems surreal.

    Right. Now this was all within the first six months. At this rate, this post will end up a small book. I’ll just hit some yearly highlights

    2016:

    The VIP Workshop in Napa

    https://x.com/WordPressVIP/status/730089745494085632

    BigWP NYC again!

    https://x.com/WordPressVIP/status/760966657787461632

    Whistler Grand Meetup

    https://x.com/ChrisHardie/status/778455679409369088

    2016 Election Traffic Boom, what a night!

    https://x.com/WordPressVIP/status/798268044455936000

    End the year with one more BigWP

    https://x.com/WordPressVIP/status/805918087585337344

    2017:

    Team meetup in Glasgow

    https://x.com/crushgear/status/887610056769982465

    Grand Meetup in Whistler

    https://x.com/andrealee_b/status/909472934825156608

    2018:

    Team meetup in Lisbon

    First VIP Grand Meetup on Montreal

    2019:

    Client meetings in San Francisco

    Team meetup in Playa del Carmen

    https://x.com/automattic/status/1093632259272441856

    Client meetings in New York

    VIP Grand Meetup in Rotterdam

    https://x.com/JoshProTweets/status/1185468743826190336

    2020:

    Team meetup in Athens

    While I love the work that I do, and I find fulfillment in it, what really sets Automattic and WordPress VIP apart is the people. We come from a wide variety of backgrounds and places, and every single person is extremely talented, kind, and just plain amazing. I wouldn’t want to be anywhere else.

    By the way, we’re hiring! 😉

  • Wisps, a WordPress Plugin

    Wisps, a WordPress Plugin

    Last year I had a need for an editable JSON file that was retrievable via HTTP. Of course there’s a million ways that I could do this, but the easiest I thought of would be to have it inside of WordPress, since all of the people that needed access to edit the file already had edit access to a specific site. So I built a plugin.

    Doing this inside WordPress already brings a lot of benefits with little to no effort:

    1. User Management
    2. Revision History
    3. oEmbed Support
    4. Permalinks
    5. Syntax Highlighting Code Editor
    6. Self-Hosted Data

    Possibly more benefits as well, depending on the setup, such as caching.

    I’ve tweaked the plugin some, and I’m almost ready to submit it to the WordPress.org Plugin Repository. I just need to do the hard part of figuring out artwork. Ugh.

    Introducing Wisps:

    Wisps are embeddable and sharable code snippets for WordPress.

    With Wisps, you can have code snippets similar to Gist, Pastebin, or similar code sharing sites. Using the built-in WordPress code editor, you can write snippets to post and share. This has the benefit of WordPress revisions, auto-drafts, etc to keep a record of how code changes.

    Wisps can be downloaded by appending /download/ to the permalink, or viewed raw by adding /view/ or /raw/. There is full oEmbed support so you can just paste in a link to a wisp in the editor and it will be fully embedded.

    PrismJS is used for syntax highlighting for oEmbeds.

    You can add Wisp support to your theme either by modifying the custom post type page-wisp.php template, which will continue to display Wisps in the loop securely, or you can use add_theme_support( 'wisps' ) to tell the plugin to not automatically escape the output. You can then do what you like, such as potentially adding frontend support for syntax highlighting.

    Here’s what the oEmbed data looks like:

    (Yeah, I totally stole the design from Gists, because I’m not talented 😬)

    View the example Wisp

    View it raw

    Download it

    Currently available on GitHub

    Hopefully one day available on the WordPress.org Plugin Repository 🙂

    If you give it a try and have any suggestions, or issues drop me a line here or on GitHub!

  • Open source ngrok alternative

    Open source ngrok alternative

    During a client onsite last year, I was first introduced to ngrok. Ngrok provides “secure introspectable tunnels to localhost.” The free tier of ngrok provides temporary, random subdomains to use. This is fine most of the time, but kind of causes problems for things like Jetpack that require persistent domain names for connecting.

    While I could shell out the $5/month for the lowest paid tier of ngrok, I would still be limited to a certain number of domains and connections.

    While looking for an alternative to ngrok, I came across sish. Sish is “an open source serveo/ngrok alternative. HTTP(S)/WS(S)/TCP Tunnels to localhost using only SSH.” To be honest, I don’t understand most of those words, but that won’t stop me!

    I of course needed a server somewhere to run this on, so I ran over to DigitalOcean and decided to spend my $5/month on a general purpose VPS instead (Here’s my DigitalOcean referral link if you’re so inclined).

    What follows is my notes I took to install sish and get it up and running. I can’t guarantee they’re perfect, and I don’t feel like deleting my VPS and starting over again just to make sure 🙂 If you see something wrong, feel free to comment me a correction or question.

    Step 1: Make a wildcard subdomain (ex *.sish.example.com)

    Step 2: Set up a DigitalOcean droplet.

    Step 3. Log in and run:

    # Make a house a home
    echo "export PS1=\"\\[\\033[38;5;33m\\]\\u\\[\$(tput sgr0)\\]\\[\\033[38;5;11m\\]@\\[\$(tput sgr0)\\]\\[\\033[38;5;33m\\]\\H\\[\$(tput sgr0)\\]\\[\\033[38;5;15m\\]:\\[\$(tput sgr0)\\]\\[\\033[38;5;11m\\]\\w\\[\$(tput sgr0)\\]\\[\\033[38;5;15m\\]\\\\$ \\[\$(tput sgr0)\\]\"" >> ~/.bashrc
    source ~/.bashrc
    apt update
    apt upgrade
    apt install ack-grep mc byobu git curl locate
    updatedb
    
    # Security
    ufw allow http
    ufw allow https
    ufw allow ssh
    ufw allow 2222
    ufw --force enable
    apt install fail2ban
    
    # Create swapfile
    fallocate -l 1G /swapfile
    chmod 600 /swapfile
    mkswap /swapfile
    swapon /swapfile
    echo '/swapfile none swap sw 0 0' | tee -a /etc/fstab
    
    # Install Docker
    apt install apt-transport-https ca-certificates curl gnupg-agent software-properties-common
    curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -
    add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/ubuntu/ $(lsb_release -cs) stable"
    apt update
    apt install docker-ce docker-ce-cli containerd.io
    
    # Install Certbot
    certbot-auto certonly --manual -d *.sish.example.com --agree-tos --no-bootstrap --preferred-challenges dns-01 --server https://acme-v02.api.letsencrypt.org/directory
    certbot certonly –manual -d *.sish.example.com –agree-tos –no-bootstrap –manual-public-ip-logging-ok –preferred-challenges dns-01 –server https://acme-v02.api.letsencrypt.org/directory
    
    # Install Keys
    curl https://github.com/my_github_username.keys > ~/sish/pubkeys/my_github_username
    cp -f /etc/letsencrypt/live/sish.example.com-0001/* ~/sish/ssl/
    ssh-keygen
    ln -s ~/.ssh/id_rsa ~/sish/ssh_key
    
    # Run Sish
    cat << "EOF" > /root/sish/docker-start.sh
    /usr/bin/docker run --name sish \
      -v ~/sish/ssl:/ssl \
      -v ~/sish/keys:/keys \
      -v ~/sish/pubkeys:/pubkeys \
      --restart unless-stopped \
      --net=host antoniomika/sish:latest \
      -sish.addr=sish.example.com:2222 \
      -sish.adminenabled=true \
      -sish.auth=false \
      -sish.bindrandom=false \
      -sish.domain=sish.example.com \
      -sish.forcerandomsubdomain=false \
      -sish.http=:80 \
      -sish.https=:443 \
      -sish.httpsenabled=true \
      -sish.httpspems=/ssl \
      -sish.keysdir=/pubkeys \
      -sish.pkloc=/keys/ssh_key \
      -sish.redirectrootlocation=https://example.com/ \
      -sish.serviceconsoleenabled=true
    EOF
    
    cat << EOF > /etc/systemd/system/docker-sish.service
    # Thanks to https://blog.container-solutions.com/running-docker-containers-with-systemd
    
    [Unit]
    Description=Sish container
    Requires=docker.service
    After=docker.service
    
    [Service]
    TimeoutStartSec=0
    Restart=always
    ExecStartPre=-/usr/bin/docker stop sish
    ExecStartPre=-/usr/bin/docker rm sish
    ExecStartPre=/usr/bin/docker pull antoniomika/sish:latest
    ExecStart=/bin/bash /root/sish/docker-start.sh
    ExecStop=/usr/bin/docker stop sish
    RemainAfterExit=true
    
    [Install]
    WantedBy=default.target
    EOF
    
    systemctl enable docker-sish
    systemctl start docker-sishCode language: PHP (php)

    From here, I can now set up a shortcut program to run locally to start a tunnel:

    cat << "EOF" > /usr/local/bin/sish
    #/bin/bash
    ssh -p 2222 -R $1:80:localhost:80 root@sish.example.com
    EOF
    chmod +x /usr/local/bin/sishCode language: PHP (php)

  • Debugging WordPress Hooks: Speed

    Debugging WordPress Hooks: Speed

    If you google debugging WordPress hooks you’ll find a lot of information.

    About 1,180,000 results

    Let’s add another one.

    WordPress hooks are powerful, but also complex under the hood. There’s plenty of topics I could talk about here, but right now I’m only going to talk about speed. How long does it take for a hook to fire and return?

    Some cool work in this area has already been done thanks to Debug Bar Slow Actions and Query Monitor, but outside of something like Xdebug or New Relic, you’ll have a hard time figuring out how long each individual hook callback takes without modifying either WordPress core or each hook call.

    … or maybe not …

    While doing some client debugging for my job at WordPress VIP (did I mention we’re hiring?) I came across the need to do this exact thing. I’ve just finished the code that will make this happen, and I’m releasing it into the wild for everyone to benefit.

    What’s the problem we’re trying to solve? Well, this client has a sporadic issue where posts saving in the admin time out and sometimes fail. We’ve ruled out some potential issues, and are looking at a save_post hook going haywire.

    Now I can capture every single save_post action, what the callback was, and how long it took. Here’s an example for this exact post:

    START: 10:save_post, (Callback: `delete_get_calendar_cache`)
    STOP: 10:save_post, Taken 132.084μs (Callback: `delete_get_calendar_cache`)
    START: 10:save_post, (Callback: `sharing_meta_box_save`)
    STOP: 10:save_post, Taken 15.974μs (Callback: `sharing_meta_box_save`)
    START: 10:save_post, (Callback: `Jetpack_Likes_Settings::meta_box_save`)
    STOP: 10:save_post, Taken 19.073μs (Callback: `Jetpack_Likes_Settings::meta_box_save`)
    START: 10:save_post, (Callback: `SyntaxHighlighter::mark_as_encoded`)
    STOP: 10:save_post, Taken 19.073μs (Callback: `SyntaxHighlighter::mark_as_encoded`)
    START: 10:save_post, (Callback: `AMP_Post_Meta_Box::save_amp_status`)
    STOP: 10:save_post, Taken 16.928μs (Callback: `AMP_Post_Meta_Box::save_amp_status`)
    START: 20:save_post, (Callback: `Publicize::save_meta`)
    STOP: 20:save_post, Taken 428.915μs (Callback: `Publicize::save_meta`)
    START: 9000:save_post, (Callback: `Debug_Bar_Slow_Actions::time_stop`)
    STOP: 9000:save_post, Taken 11.921μs (Callback: `Debug_Bar_Slow_Actions::time_stop`)
    START: 1:save_post, (Callback: `The_SEO_Framework\Load::_update_post_meta`)
    STOP: 1:save_post, Taken 6.016ms (Callback: `The_SEO_Framework\Load::_update_post_meta`)
    START: 1:save_post, (Callback: `The_SEO_Framework\Load::_save_inpost_primary_term`)
    STOP: 1:save_post, Taken 1.535ms (Callback: `The_SEO_Framework\Load::_save_inpost_primary_term`)
    START: 10:save_post, (Callback: `delete_get_calendar_cache`)
    STOP: 10:save_post, Taken 179.052μs (Callback: `delete_get_calendar_cache`)
    START: 10:save_post, (Callback: `sharing_meta_box_save`)
    STOP: 10:save_post, Taken 247.002μs (Callback: `sharing_meta_box_save`)
    START: 10:save_post, (Callback: `Jetpack_Likes_Settings::meta_box_save`)
    STOP: 10:save_post, Taken 25.988μs (Callback: `Jetpack_Likes_Settings::meta_box_save`)
    START: 10:save_post, (Callback: `The_SEO_Framework\Load::delete_excluded_ids_cache`)
    STOP: 10:save_post, Taken 185.966μs (Callback: `The_SEO_Framework\Load::delete_excluded_ids_cache`)
    START: 10:save_post, (Callback: `SyntaxHighlighter::mark_as_encoded`)
    STOP: 10:save_post, Taken 15.020μs (Callback: `SyntaxHighlighter::mark_as_encoded`)
    START: 10:save_post, (Callback: `AMP_Post_Meta_Box::save_amp_status`)
    STOP: 10:save_post, Taken 12.875μs (Callback: `AMP_Post_Meta_Box::save_amp_status`)
    START: 20:save_post, (Callback: `Publicize::save_meta`)
    STOP: 20:save_post, Taken 377.893μs (Callback: `Publicize::save_meta`)
    START: 9000:save_post, (Callback: `Debug_Bar_Slow_Actions::time_stop`)
    STOP: 9000:save_post, Taken 14.067μs (Callback: `Debug_Bar_Slow_Actions::time_stop`)Code language: CSS (css)

    So how does it work?

    1. We add an all action that will fire for every other action.
    2. In the all callback, we make sure we’re looking for the correct hook.
    3. We then build an array to store some data, use the $wp_filter global to fill out information such as the priority and the callback, and store the start time.
    4. Next we have to add a new action to run for our hook right before the callback we want to time. We use the fact that, even though add_action() is supposed to use an int for the priority, it will also accept a string. We add new hooks, and re-prioritze all of the existing hooks with floats that are stringified.
    5. This allows us to capture the start time and end time of each individual callback, instead of the priority group as a whole.

    Of course, this does add a tiny bit of overhead, and could cause some problems if any other plugins use stringified hook priorities, or other odd issues–so be careful 🙂

    Finally, here’s the code:

    class VIP_Hook_Timeline {
    	public $hook;
    	public $callbacks     = [];
    	public $callback_mod  = 0.0001;
    	public $callback_mods = [];
    
    	public function __construct( $hook ) {
    		$this->hook = $hook;
    		add_action( 'all', array( $this, 'start' ) );
    	}
    
    	public function start() {
    		// We only want to get a timeline for one hook.
    		if ( $this->hook !== current_filter() ) {
    			return;
    		}
    
    		global $wp_filter;
    
    		// Iterate over each priority level and set up array.
    		foreach( $wp_filter[ $this->hook ] as $priority => $callback ) {
    			// Make the mod counter if not exists.
    			if ( ! isset( $this->callback_mods[ $priority ] ) ) {
    				$this->callback_mods[ $priority ] = $priority - $this->callback_mod;
    			}
    
    			// Make the array if not exists.
    			if ( ! is_array( $this->callbacks[ $priority ] ) ) {
    				$this->callbacks[ $priority ] = [];
    			}
    
    			// Iterate over each callback and set up array.
    			foreach( array_keys( $callback ) as $callback_func ) {
    				if ( ! is_array( $this->callbacks[ $priority ][ $callback_func ] ) ) {
    					$this->callbacks[ $priority ][ $callback_func ] = [ 'start' => 0, 'stop' => 0 ];
    				}
    			}
    		}
    
    		foreach( $this->callbacks as $priority => $callback ) {
    			foreach ( array_keys( $callback ) as $callback_func ) {
    
    				// Get data befmore we move things around.
    				$human_callback = $this->get_human_callback( $wp_filter[ $this->hook ][ $priority ][$callback_func] );
    
    				// Modify the priorities.
    				$pre_callback_priority = $this->callback_mods[ $priority ];
    				$this->callback_mods[ $priority ] = $this->callback_mods[ $priority ] + $this->callback_mod;
    
    				$new_callback_priority = $this->callback_mods[ $priority ];
    				$this->callback_mods[ $priority ] = $this->callback_mods[ $priority ] + $this->callback_mod;
    
    				$post_callback_priority = $this->callback_mods[ $priority ];
    				$this->callback_mods[ $priority ] = $this->callback_mods[ $priority ] + $this->callback_mod;
    
    				// Move the callback to our "new" priority.
    				if ( $new_callback_priority != $priority ) {
    					$wp_filter[ $this->hook ]->callbacks[ strval( $new_callback_priority ) ][ $callback_func ] = $wp_filter[ $this->hook ]->callbacks[ $priority ][ $callback_func ];
    					unset( $wp_filter[ $this->hook ]->callbacks[ $priority ][ $callback_func ] );
    					if ( empty( $wp_filter[ $this->hook ]->callbacks[ $priority ] ) ) {
    						unset( $wp_filter[ $this->hook ]->callbacks[ $priority ] );
    					}
    				}
    
    				// Add a new action right before the one we want to debug to capture start time.
    				add_action( $this->hook, function( $value = null ) use ( $callback_func, $priority, $human_callback ) {
    					$this->callbacks[ $priority ][ $callback_func ]['start'] = microtime( true );
    
    					// Uncomment this if you just want to dump data to the PHP error log, otherwise add your own logic.
    					//$message = 'START: %d:%s, (Callback: `%s`)';
    					// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
    					//error_log( sprintf( $message,
    					//	$priority,
    					//	$this->hook,
    					//	$human_callback,
    					//) );
    
    					// Just in case it's a filter, return.
    					return $value;
    				}, strval( $pre_callback_priority ) );
    
    				// Add a new action right after the one we want to debug to capture end time.
    				add_action( $this->hook, function( $value = null ) use ( $callback_func, $priority, $human_callback ) {
    					$this->callbacks[ $priority ][ $callback_func ]['stop'] = microtime( true );
    
    					// Uncomment this if you just want to dump data to the PHP error log, otherwise add your own logic.
    					//$message = 'STOP: %d:%s, Taken %s (Callback: `%s`)';
    					// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
    					//error_log( sprintf( $message,
    					//	$priority,
    					//	$this->hook,
    					//	$this->get_human_diff( $priority, $callback_func ),
    					//	$human_callback,
    					//) );
    
    					// Just in case it's a filter, return.
    					return $value;
    				}, strval( $post_callback_priority ) );
    				
    			}
    		}
    	}
    
    	public function get_human_callback( $callback ) {
    		$human_callback = '[UNKNOWN HOOK]';
    		if ( is_array( $callback['function'] ) && count( $callback['function'] ) == 2 ) {
    			list( $object_or_class, $method ) = $callback['function'];
    			if ( is_object( $object_or_class ) ) {
    				$object_or_class = get_class( $object_or_class );
    			}
    			$human_callback =  sprintf( '%s::%s', $object_or_class, $method );
    		} elseif ( is_object( $callback['function'] ) ) {
    			// Probably an anonymous function.
    			$human_callback =  get_class( $callback['function'] );
    		} else {
    			$human_callback =  $callback['function'];
    		}
    		return $human_callback;
    	}
    
    	public function get_start( $priority, $callback_func ) {
    		return (float) $this->callbacks[ $priority ][ $callback_func ]['start'];
    	}
    
    	public function get_stop( $priority, $callback_func ) {
    		return (float) $this->callbacks[ $priority ][ $callback_func ]['stop'];
    	}
    
    	public function get_diff( $priority, $callback_func ) {
    		return (float) ( $this->get_stop( $priority, $callback_func ) - $this->get_start( $priority, $callback_func ) );
    	}
    
    	public function get_human_diff( $priority, $callback_func ) {
    		$seconds = $this->get_diff( $priority, $callback_func );
    
    		// Seconds.
    		if ( $seconds >= 1 || $seconds == 0 ) {
    			return number_format( $seconds, 3 ) . 's';
    		}
    
    		// Milliseconds.
    		if ( $seconds >= .001 ) {
    			return number_format( $seconds * 1000, 3 ) . 'ms';
    		}
    
    		// Microseconds.
    		if ( $seconds >= .000001 ) {
    			return number_format( $seconds * 1000000, 3 ) . 'μs';
    		}
    
    		// Nanoseconds.
    		if ( $seconds >= .000000001 ) {
    			// WOW THAT'S FAST!
    			return number_format( $seconds * 1000000000, 3 ) . 'ns';
    		}
    
    		return $seconds . 's?';
    	}
    }
    new VIP_Hook_Timeline( 'save_post' );Code language: PHP (php)