Author: Andrew Minion

Shimming MySQL Functions into SQLite for Laravel CI/CD Testing

Posted on by Andrew Minion

Colin DeCarlo presented a talk at Laracon Online where among other useful tips, he demonstrated how to shim MySQL functions in an SQLite database (e.g., add functions that MySQL has but SQLite does not).

Here are two examples that I just needed in a project (FLOOR and DATEDIFF):

use Illuminate\Support\Facades\DB;

DB::getPdo()->sqliteCreateFunction('floor', fn ($value) => floor($value));
DB::getPdo()->sqliteCreateFunction('datediff', fn ($date1, $date2) => Carbon::parse($date1)->diff(Carbon::parse($date2))->days);

Redirect to Original URL with Laravel Socialite

Posted on by Andrew Minion

We’re using Laravel Socialite with a self-hosted GitLab instance to authenticate users for an internal tool.

Every time the session times out, the OAuth flow redirects the user back to the default dashboard, regardless of what URL the user originally requested.

However, Laravel provides a url.intended session key with the original URL; here’s how I used it, with a fallback to the dashboard URL:

return redirect()->to(
    session()->get('url.intended', route('dashboard')
);

Database Platform Comparisons for Laravel Feature Tests

Posted on by Andrew Minion

TL;DR: MySQL significantly outperforms MariaDB in my automated test suite.

The Problem

This Twitter thread prompted me to do a bit of research on database platforms for Laravel automated tests.

I’ve recently been building an ecommerce app based on Laravel. Partway through development, we added geometry fields to a couple of tables in order to determine distances. I’ve been using this spatial package, so SQLite was not an option for my test suite.

As soon as I switched the testing database driver from SQLite to MariaDB, my tests immediately took an extra 12–13 seconds to run, regardless of whether I ran the entire test suite, a single file, or just one test.

This significantly lengthened the feedback loop when making changes to code and re-running tests.

So when I saw Jack Ellis mention that he uses MySQL for his test suite, it made me curious if he had the same issue.

He said that one of his test files runs 39 tests in < 2 seconds, so apparently it’s not been a problem for him.

Context

  • I’m using the LazilyRefreshDatabase trait added in Laravel 8.62.0 on my entire test suite
  • I’m using squashed migrations
  • Many of my tables have constrained foreign keys referencing other tables

Comparisons

I decided to do some digging; here are comparisons using four different platforms for the same test in my application.

MariaDB

I’ve been using MariaDB as the main database platform on my development machine for years. Currently I’m on version 10.6.4.

  • A single test took 14s to run
  • An entire file with 16 tests took 19s to run

In-Memory SQLite Database

I temporarily disabled the geometry features and tried the in-memory SQLite database (DB_CONNECTION=:memory:); it performed much better for the same tests:

  • A single test too <1s to run
  • An entire file with 16 tests took ~7s to run

SQLite File Database

I then tried with an SQLite file (DB_CONNECTION=sqlite), and it performed about the same:

  • A single test took ~1s to run
  • An entire file with 16 tests took ~7s to run

MySQL 8

I have an installation of MySQL 8 set up for one app that uses some specific MySQL 8 and I figured why not give that a try too.

Here are the results:

  • A single test took ~1.5s to run
  • An entire file with 16 tests took ~6.5s to run

Summary

For some reason, MariaDB takes approximately 12–13 seconds to tear down and recreate the database before starting to run tests, but MySQL is much faster.

While testing MariaDB, I opened the raw data directory for the database, and noticed chunks of files being removed and recreated at a time, so perhaps the foreign key constraints are (part of) the culprit here.

I do have 77 databases with ~3800 tables in my MariaDB installation built up from various projects over the years. It seems unlikely, but theoretically possible, that the server size could be part of the problem too.

I think I’ll experiment with switching back to MySQL as my development platform of choice.

Have you run into this same issue? Have any tips or tricks? Let me know in the comments.

Retrieving Route and Parameters from an Arbitrary URL in Laravel

Posted on by Andrew Minion

I build an oEmbed provider in a Laravel application the other day and needed to parse an arbitrary URL to determine the route and parameters passed in order to determine the response.

Since I already had the routes built for the possible URLs, I didn’t want to duplicate code and re-parse them.

Here’s how I ended up retrieving the route and parameters:

Migrating sermons from Sermon Manager for WordPress to SermonAudio

Posted on by Andrew Minion

I build a Laravel-based command-line utility to import sermons from the Sermon Manager for WordPress plugin and migrate them into SermonAudio.

If it’s useful to you, see this repository for setup and usage details: https://gitlab.com/andrewminion/sermon-manager-to-sermon-audio

Trello to Excel

Posted on by Andrew Minion

I was trying to export a Trello board to a spreadsheet and include all the cards and checklists but couldn’t find a good way to do that, so I wrote one!

Originally this project was based on Laravel Zero, a command-line Laravel framework. It accepted a JSON file exported from Trello and exports an Excel spreadsheet:

  • A board becomes an Excel file
  • Each list becomes a worksheet
  • Each card becomes a row in the worksheet
  • Checklist items become individual rows with their name, completion status, and due date

It still does that, but it’s also available now as a webapp for anybody to use simply by logging in with Trello!

Try it out here: https://trello-to-excel.andrewrminion.com/

You can find the source code here: https://gitlab.com/andrewminion/trello-to-excel

Example

Counting Distinct Values in a Single Field

Posted on by Andrew Minion

A quick MySQL snippet to count how many times a value appears in a single field—much easier to grok than multiple JOINs.

SELECT
COUNT(CASE WHEN meta_value = 'value1' THEN 1 END) AS value1,
COUNT(CASE WHEN meta_value = 'value2' THEN 1 END) AS value2
FROM wp_post_meta;

/** Results

| value1 | value2 |
|--------|--------|
| 75     | 56     |
*/

Local Development and WordPress Uploads

Posted on by Andrew Minion

One common issue with local development is how to handle uploaded files.

You could copy the entire wp-content/uploads/ directory but that can use up a lot of disk space for little benefit.

Another option is to rewrite all HTTP requests for missing local images to the appropriate URLs on the live site.

Here’s how to do it:

Nginx

Find your nginx config file1 and add this line above the location / { block:

location /wp-content/uploads/ {
    try_files $uri $uri/;

    rewrite ^/wp-content/uploads/(.*)$ https://{live site domain}/wp-content/uploads/$1;
}

Or if for some reason you need to rewrite all files to the live site instead of just those that are missing, add this in the location / { block, ideally as the first line:

rewrite ^/wp-content/uploads/(.*)$ https://{live site domain}/wp-content/uploads/$1;

Apache

Add this line to the .htaccess file:

RewriteRule ^wp-content/uploads/(.*)$ http://{live site domain}/wp-content/uploads/$1 [NC,L]

Notes

  1. If you’re using Laravel Valet: look in ~/.config/valet/Nginx/ for a file with the same name as your site’s domain.
    If you’re using Laravel Herd: look in ~/Library/Application Support/Herd/config/valet/Nginx/ for a file with the same name as your site’s domain.

Using psysh in Shared Hosting or Limited User Environments

Posted on by Andrew Minion

When using psysh or Laravel’s php artisan tinker in a limited user’s environment, you may run into this error:

Unable to create PsySH runtime directory. Make sure PHP is able to write to {some directory path} in order to continue.

This is caused by psysh trying to use a path that’s not accessible to the user.

To fix, add a file at ~/.config/psysh/config.php with this content:

<?php
return [
    'runtimeDir' => '~/tmp'
];

The Color of Compromise

Posted on by Andrew Minion

by Jemar Tisby

Description

The Color of Compromise takes readers on a historical journey: from America’s early colonial days through slavery and the Civil War, covering the tragedy of Jim Crow laws and the victories of the Civil Rights era, to today’s Black Lives Matter movement. Author Jemar Tisby reveals the obvious—and the far more subtle—ways the American church has compromised what the Bible teaches about human dignity and equality.

Tisby uncovers the roots of sustained injustice in the American church, highlighting the cultural and institutional tables that need to be turned in order to bring about real and lasting progress between black and white people. Through a story-driven survey of American Christianity’s racial past, he exposes the concrete and chilling ways people of faith have actively worked against racial justice, as well as the deafening silence of the white evangelical majority. Tisby shows that while there has been progress in fighting racism, historically the majority of the American church has failed to speak out against this evil. This ongoing complicity is a stain upon the church, and sadly, it continues today.

Tisby does more than diagnose the problem, however. He charts a path forward with intriguing ideas that further the conversation as he challenges us to reverse these patterns and systems of complicity with bold, courageous, and immediate action. The Color of Compromise provides an accurate diagnosis for a racially divided American church and suggests creative ways to foster a more equitable and inclusive environment among God’s people.

My Thoughts

This was a fairly quick read. Eight of the eleven chapters are a historical overview of racism and slavery in the United States, highlighting the assumptions and actions of both the culture and the church.

The final two chapters examine how the church and Christians are responding to current events, and offers a number of both small and large practical actions that we can take to combat racism.

This is a must-read for anyone in the American church. It is challenging and convicting to understand that doing nothing is to continue being complicit in racial systems.

If you read nothing else, at least read these two quotes:

Black lives matter does not mean that only black lives matter; it means that black lives matter too. Given the racist patterns of devaluing black lives in America’s past, it is not obvious to many black people that everyone values black life.

— Location 3,084

[M]any evangelicals have distanced themselves from or even opposed both the Black Lives Matter organization and the phrase. But the American evangelical church has yet to form a movement as viable and potent that addresses the necessary concept that black lives do indeed matter.

— Location 3,101, emphasis added

Quotes

One of the challenges we face in discussions of racism today is that the conversation about race has shifted since the civil rights era. Legislation has rendered the most overt acts of racism legally punishable. Hate crimes of various forms still occur, but most American Christians would call these acts evil. Yet the legacy of racism persists, albeit in different forms.

— Location 2,993

Sociologists Michael Emerson and Christian Smith studied white evangelical ideas about race in their book Divided by Faith. To frame their study, they used the concept of a “racialized” society which they defined as a society “wherein race matters profoundly for differences in life experiences, life opportunities and social relationships.”

— Location 2,996

Emerson and Smith go on to explain that discrimination in a racialized society is increasingly covert, embedded in the normal operations of institutions, and it avoids direct racial terminology, making it invisible to most white people.

— Location 3,001

Black lives matter does not mean that only black lives matter; it means that black lives matter too. Given the racist patterns of devaluing black lives in America’s past, it is not obvious to many black people that everyone values black life.

— Location 3,084

[M]any evangelicals have distanced themselves from or even opposed both the Black Lives Matter organization and the phrase. But the American evangelical church has yet to form a movement as viable and potent that addresses the necessary concept that black lives do indeed matter.

— Location 3,101, emphasis added

Many Christians may agree with the principle that black lives matter, but they still wonder whether they should get involved with an organization that espouses beliefs contrary to his or her religious convictions. There is no single answer that will fit every person’s situation. … Ultimately, the organizations with which one chooses to affiliate in the cause of antiracism is a matter of conscience. The only wrong action is inaction.

— Locations 3,168–3,174

Christian complicity with racism in the twenty-first century looks different than complicity with racism in the past. It looks like Christians responding to black lives matter with the phrase all lives matter. It looks like Christians consistently supporting a president whose racism has been on display for decades. It looks like Christians telling black people and their allies that their attempts to bring up racial concerns are “divisive.” It looks like conversations on race that focus on individual relationships and are unwilling to discuss systemic solutions. Perhaps Christian complicity in racism has not changed much after all. Although the characters and the specifics are new, many of the same rationalizations for racism remain.

— Location 3,291

If the twenty-first century is to be different from the previous four centuries, then the American church must exercise even more creativity and effort to break down racial barriers than it took to erect them in the first place.

— Location 3,318

View on Bookshop

Note: this is an affiliate link, so I may get a small commission if you purchase using the link. This commission does not affect the content of this post.