DEV Community: William L'Archeveque

Disabling foreign key constraints in MySQL

William L'Archeveque — Fri, 29 Jan 2021 14:02:24 +0000

I have recently made a backup of our Laravel Vapor MySQL database before a temporary deployment on our staging environment.

When I wanted to restore the MySQL database using (https://dbeaver.io/)[DBeaver], an open source GUI utility, I encountered an error because tables were referenced by foreign key constraints :

ERROR 3730 (HY000) at line 104: Cannot drop table 'articles' referenced by a foreign key constraint 'publications_article_id_foreign' on table 'publications'.

Foreign Keys offer you the option to have updates and deletes cascade from the parent (reference) table to the child table. I could have manually dropped every table in our database prior to restoring the data, but I instead decided to follow this simple trick :

Before restoring the data, I executed the following MySQL command :

SET FOREIGN_KEY_CHECKS=0;

I restored the data successfully and made sure to configure the FOREIGN_KEY_CHECKS again :

SET FOREIGN_KEY_CHECKS=1;

You can also add those commands respectively at the top and bottom of your .sql backup file.

Another way to bypass foreign constraints on database restore is to use DISABLE KEYS :

ALTER TABLE table_name DISABLE KEYS;

You can now restore the database.

You should reconfigure ENABLE KEYS after restoring the database :

ALTER TABLE table_name ENABLE KEYS;

Please note that DISABLE KEYS only works for MyISAM and not InnoDb storage engine.

Let me know if this article was helpful to you !

How to effectively and completely delete a git submodule

William L'Archeveque — Mon, 25 Jan 2021 14:49:32 +0000

I have been working on multiple projects included as Git submodules in a parent repository. Recently, I had to remove one project that we did not need anymore.

Side note : This parent repository is used to easily clone multiple projects in the same directory for new employees at our startup. The different projects are linked together and interact to each other as their code is executed in separate Docker containers.

Example project structure :

|- blog_main
    |- blog_api
    |- blog_frontend
    |- local_dev_infrastructure

Here is the way I could remove a submodule and commit the changes to the parent directory.

git submodule deinit <path_to_submodule>
git rm <path_to_submodule>
git commit -m "Removed submodule"
rm -rf .git/modules/<path_to_submodule>

Git error “fatal: refusing to merge unrelated histories” explanation

William L'Archeveque — Sun, 24 Jan 2021 18:47:36 +0000

When two unrelated projects are merged, you may encounter the fatal: refusing to merge unrelated histories Git error. This happens when projects are not aware of each other’s existence and have mismatching commit histories.

We may consider two cases that throw this :

You may have cloned a project for which the .git directory is deleted or corrupted. In this situation, Git is unaware of your local history and will throw this error when trying to push to or pull from the remote repository.
You may have created a new repository and added commits to it. Now you are trying to pull from a remote repository that already exists and already has commits. Git will throw the error in this case, because it does not know how the two projects are related.

"Refusing to merge unrelated histories" solution

You can solve this issue by adding the allow-unrelated-histories flag. After executing git pull or git merge, add the following flag:

git pull origin master --allow-unrelated-histories

Git provides more information regarding this :

https://github.com/git/git/blob/master/Documentation/RelNotes/2.9.0.txt#L58-L68

Cover Image : Markus Spiske (@markusspiske) from Unsplash

Custom YouTube thumbnail and play button for embedded iframe video

William L'Archeveque — Sun, 24 Jan 2021 18:31:54 +0000

I searched for the perfect solution to show a custom high quality thumbnail for my embed videos. I did not want to use the more complex Youtube Javascript API solution. Here is how I archived the obstacle of using a custom thumbnail or Youtube maximum resolution thumbnail and a custom play button.

The following code uses jQuery but this could be done in Vanilla Javascript.

Technical explanation to use a custom Youtube thumbnail

First, I integrate my Youtube video with a custom div and custom thumbnail if needed. The data attribute tags are used to provide the Youtube video id and the custom thumbnail URL.
Then, on page load, for each video on a page, the Javascript code creates a placeholder with the needed thumbnail (custom or Youtube maximum resolution one). A customized play button is added on top using CSS.
Finally, a click event listener is added to the placeholder to create an embed iframe when clicked. When showing the iframe the placeholder is hidden.

The explanation is short and sweet, but take a look at the code and let me know what you think !

Youtube Custom Thumbnail Image: Cédric VT (@cedric_photography) from Unsplash

Cover Image: PJ Gal-Szabo (@pjgalszabo) from Unsplash

Avoid long-running Gulp tasks with this simple Gulp caching plugin

William L'Archeveque — Sun, 24 Jan 2021 17:38:23 +0000

Gulp is killed because of a long-running process

I have been working on a Web application codebase that has multiple themes for different websites. When building the code during a deployment, the themes have multiple SCSS/CSS assets and scripts that go through Gulp pipes for cleaning, minifying and concatenating the code to a single build file.

Unfortunately, the build process has not been updated in years, and the theme folder structure is messed up (legacy code). So the more we added themes to our application, the longer the Gulp build process on deploy would take.

An awful 20–30 minutes on our different environments.

Yeah, production included.

Being an unacceptable situation, I analyzed how we could improve the Gulp process. Having 42 themes with multiple CSS assets and a ton of JavaScript files going through Gulp pipes, the total amount of parallel tasks where approximately 217. This for sure is not the perfect architecture for compiling assets. But in our world, nothing is perfect, so instead of doing a complete refactor of our application assets, we decided to improve the current build process.

Gulp Killed — The memory usage problem

When running long-running processes the gulp command may be killed by the server and this is annoying. Before we investigate for a solution, this is the problem that we encountered : gulp process is killed by the kernel because the memory of the server is running out.

One solution would be to add memory to the virtual machine or server. But this is not an option for us.

Let’s optimize!

Process Gulp tasks only if needed, thus, when assets have changed in our themes

He first idea that came to my mind when solving the problem was to process only the Gulp tasks that really needed to run and proceed to the next one if not. The trick would be to check if the task assets have been changed and proceed through pipes if a change was made since the last build.

Discovering existing Node solutions that did not do the trick

While searching for an appropriate solution to our problem, I discovered two Gulp plugins that seemed to be helpful. The first, gulp-changed checks against build files, for example, ES5 files generated by Babel.js from the source ES6 files. This didn’t help because we wanted the build process to be faster and not check the build files themselves.

gulp-changed-in-place was the second option, this plugin monitors source files, ES6 files for example. This allows you to do things like apply source formatting or linting only to changed files while working on them.

The second option seemed promising, although, it only sends files that were modified through Gulp pipes. When we needed to send all files if if one changed.

Complete Gulp tasks only if assets have changed with `gulp-process-if-modified`

With the inspiration provided by the previously mentioned Gulp plugins, I decided to code my own.

You can check it out here :

wlarch / gulp-process-if-modified

This gulp plugin will passthrough source files if they have been modified since last build.

gulp-process-if-modified

This gulp plugin will passthrough source files if they have been modified since last build.

Inspired by gulp-changed and gulp-changed-in-place

Avoid long running Gulp builds

Do not waste time during your build process when there is no reason to. This plugin will process all files in gulp.src() if at least one file was changed. This plugin uses the features of node-file-cache to keep track of the assets SHA1 hashed content in a cache.json file.

Alternatives depending on use case

gulp-changed : checks against build files, for example ES5 files generated by Babel.js from the source ES6 files.

gulp-changed-in-place : monitors source files, ES6 files for example. This allows you to do things like apply source formatting or linting only to changed files while you are working on them.

Install

$ npm install --save gulp-process-if-modified

Usage

var gulp = require('gulp');
var processIfModified = require('gulp-process-if-modified'

…

View on GitHub

Using the power of cache files and Gulp pipes, I was able to process only tasks for which assets have been modified since the last build. Every Gulp task have a cache file in which we compare the hashed content of assets and determine if a change was made.

It’s simple : if a change was made, process all assets. If not, process none.

This means that if we change a single CSS file for a theme, we won’t need to compile the 42 themes we have in our application but only the one that have modified assets (either Javascript or SCSS for example).

This decreased our build process duration from 25–20 minutes to 9–20 seconds ! 🤩

Most of the time when coding, we are motivated by existing open source code that we can adapt to our use case. In this case, I could not use the existing Gulp plugins. I did not reinvent the wheel, but did change the tire !

Let me know what you think and do not hesitate to comment on the code and provide feedback or pull requests.

Cover Image : Cris Saur (@crisaur) from Unplash

Sorting multidimensional PHP arrays by object values with accented character

William L'Archeveque — Sun, 24 Jan 2021 17:18:27 +0000

When working on a multilingual website, it often happens that we need to deal with special and accented characters. In Québec (Canada), Websites are most of the time bilingual because we speak French and English. This can cause a headache to developers when dealing with sorting arrays by alphabetical order due to “caractères spéciaux” (french for special characters).

I developed a few methods that can help overcome the difficulties of multidimensional array sorting.

The problem with sorting special characters

The best way to explain the problem is with an example. Let’s say we have a multidimensional array of category objects returned by an API that we want ordered alphabetically by category name for a specific language :

<?php

require 'StringHelper.php';

$helper = new StringHelper();
$categories = [];

$news = new stdClass();
$news->names = ["fr" => "Actualités", "en" => "News"];
$categories[] = $news;

$sports = new stdClass();
$sports->names = ["fr" => "Sports", "en" => "Sports"];
$categories[] = $sports;

$home = new stdClass();
$home->names = ["fr" => "Accueil", "en" => "Home"];
$categories[] = $home;

$events = new stdClass();
$events->names = ["fr" => "Événements", "en" => "Events"];
$categories[] = $events;

$special = new stdClass();
$special->names = ["fr" => "Spécial", "en" => "Special"];
$categories[] = $special;

// Alphabetically sorted result in french : Accueil, Actualités, Événement, Spécial, Sports
// Alphabetically sorted result in english : Events, Home, News, Special, Sports

// Original order
var_dump($categories);

$helper->alphabeticalCompareArrayByKey($categories, 'names', 'fr');

// Alphabetical order (french) by names
var_dump($categories);

It’s a challenge to order this array correctly because of the accented characters and because of the way the array is formatted (with an array of objects).

The solution for sorting an array of objects

I have solved this problem by creating two different functions in a StringHelper.php class that can be used in the application :

<?php

class StringHelper
{

 /**
  * Compare an associative multidimensionnal array by specific object value
  *
  * @param array &$array     Reference of the array to sort
  * @param string $element   Element to order by specific object key from
  * @param string $key       Sort array by this specified key of element
  * @return void
  */
  public static function alphabeticalCompareArrayByKey(&$array, string $element, string $key){
      usort($array, function($a, $b) use ($element, $key) {
          return strcasecmp(self::transliterateString($a->{$element}->[$key]), self::transliterateString($b->{$element}->[$key]));
      });
  }

  /**
   * Replace accented caracters in string
   *
   * Example :
   * echo transliterateString('Événenement'); // evenement
   *
   * @param string  String with accented caracters
   * @return string  Transliterated string
   */
  public static function transliterateString($string)
  {
      $transliterationTable = ['á' => 'a', 'Á' => 'A', 'à' => 'a', 'À' => 'A', 'ă' => 'a', 'Ă' => 'A', 'â' => 'a', 'Â' => 'A', 'å' => 'a', 'Å' => 'A', 'ã' => 'a', 'Ã' => 'A', 'ą' => 'a', 'Ą' => 'A', 'ā' => 'a', 'Ā' => 'A', 'ä' => 'ae', 'Ä' => 'AE', 'æ' => 'ae', 'Æ' => 'AE', 'ḃ' => 'b', 'Ḃ' => 'B', 'ć' => 'c', 'Ć' => 'C', 'ĉ' => 'c', 'Ĉ' => 'C', 'č' => 'c', 'Č' => 'C', 'ċ' => 'c', 'Ċ' => 'C', 'ç' => 'c', 'Ç' => 'C', 'ď' => 'd', 'Ď' => 'D', 'ḋ' => 'd', 'Ḋ' => 'D', 'đ' => 'd', 'Đ' => 'D', 'ð' => 'dh', 'Ð' => 'Dh', 'é' => 'e', 'É' => 'E', 'è' => 'e', 'È' => 'E', 'ĕ' => 'e', 'Ĕ' => 'E', 'ê' => 'e', 'Ê' => 'E', 'ě' => 'e', 'Ě' => 'E', 'ë' => 'e', 'Ë' => 'E', 'ė' => 'e', 'Ė' => 'E', 'ę' => 'e', 'Ę' => 'E', 'ē' => 'e', 'Ē' => 'E', 'ḟ' => 'f', 'Ḟ' => 'F', 'ƒ' => 'f', 'Ƒ' => 'F', 'ğ' => 'g', 'Ğ' => 'G', 'ĝ' => 'g', 'Ĝ' => 'G', 'ġ' => 'g', 'Ġ' => 'G', 'ģ' => 'g', 'Ģ' => 'G', 'ĥ' => 'h', 'Ĥ' => 'H', 'ħ' => 'h', 'Ħ' => 'H', 'í' => 'i', 'Í' => 'I', 'ì' => 'i', 'Ì' => 'I', 'î' => 'i', 'Î' => 'I', 'ï' => 'i', 'Ï' => 'I', 'ĩ' => 'i', 'Ĩ' => 'I', 'į' => 'i', 'Į' => 'I', 'ī' => 'i', 'Ī' => 'I', 'ĵ' => 'j', 'Ĵ' => 'J', 'ķ' => 'k', 'Ķ' => 'K', 'ĺ' => 'l', 'Ĺ' => 'L', 'ľ' => 'l', 'Ľ' => 'L', 'ļ' => 'l', 'Ļ' => 'L', 'ł' => 'l', 'Ł' => 'L', 'ṁ' => 'm', 'Ṁ' => 'M', 'ń' => 'n', 'Ń' => 'N', 'ň' => 'n', 'Ň' => 'N', 'ñ' => 'n', 'Ñ' => 'N', 'ņ' => 'n', 'Ņ' => 'N', 'ó' => 'o', 'Ó' => 'O', 'ò' => 'o', 'Ò' => 'O', 'ô' => 'o', 'Ô' => 'O', 'ő' => 'o', 'Ő' => 'O', 'õ' => 'o', 'Õ' => 'O', 'ø' => 'oe', 'Ø' => 'OE', 'ō' => 'o', 'Ō' => 'O', 'ơ' => 'o', 'Ơ' => 'O', 'ö' => 'oe', 'Ö' => 'OE', 'ṗ' => 'p', 'Ṗ' => 'P', 'ŕ' => 'r', 'Ŕ' => 'R', 'ř' => 'r', 'Ř' => 'R', 'ŗ' => 'r', 'Ŗ' => 'R', 'ś' => 's', 'Ś' => 'S', 'ŝ' => 's', 'Ŝ' => 'S', 'š' => 's', 'Š' => 'S', 'ṡ' => 's', 'Ṡ' => 'S', 'ş' => 's', 'Ş' => 'S', 'ș' => 's', 'Ș' => 'S', 'ß' => 'SS', 'ť' => 't', 'Ť' => 'T', 'ṫ' => 't', 'Ṫ' => 'T', 'ţ' => 't', 'Ţ' => 'T', 'ț' => 't', 'Ț' => 'T', 'ŧ' => 't', 'Ŧ' => 'T', 'ú' => 'u', 'Ú' => 'U', 'ù' => 'u', 'Ù' => 'U', 'ŭ' => 'u', 'Ŭ' => 'U', 'û' => 'u', 'Û' => 'U', 'ů' => 'u', 'Ů' => 'U', 'ű' => 'u', 'Ű' => 'U', 'ũ' => 'u', 'Ũ' => 'U', 'ų' => 'u', 'Ų' => 'U', 'ū' => 'u', 'Ū' => 'U', 'ư' => 'u', 'Ư' => 'U', 'ü' => 'ue', 'Ü' => 'UE', 'ẃ' => 'w', 'Ẃ' => 'W', 'ẁ' => 'w', 'Ẁ' => 'W', 'ŵ' => 'w', 'Ŵ' => 'W', 'ẅ' => 'w', 'Ẅ' => 'W', 'ý' => 'y', 'Ý' => 'Y', 'ỳ' => 'y', 'Ỳ' => 'Y', 'ŷ' => 'y', 'Ŷ' => 'Y', 'ÿ' => 'y', 'Ÿ' => 'Y', 'ź' => 'z', 'Ź' => 'Z', 'ž' => 'z', 'Ž' => 'Z', 'ż' => 'z', 'Ż' => 'Z', 'þ' => 'th', 'Þ' => 'Th', 'µ' => 'u', 'а' => 'a', 'А' => 'a', 'б' => 'b', 'Б' => 'b', 'в' => 'v', 'В' => 'v', 'г' => 'g', 'Г' => 'g', 'д' => 'd', 'Д' => 'd', 'е' => 'e', 'Е' => 'e', 'ё' => 'e', 'Ё' => 'e', 'ж' => 'zh', 'Ж' => 'zh', 'з' => 'z', 'З' => 'z', 'и' => 'i', 'И' => 'i', 'й' => 'j', 'Й' => 'j', 'к' => 'k', 'К' => 'k', 'л' => 'l', 'Л' => 'l', 'м' => 'm', 'М' => 'm', 'н' => 'n', 'Н' => 'n', 'о' => 'o', 'О' => 'o', 'п' => 'p', 'П' => 'p', 'р' => 'r', 'Р' => 'r', 'с' => 's', 'С' => 's', 'т' => 't', 'Т' => 't', 'у' => 'u', 'У' => 'u', 'ф' => 'f', 'Ф' => 'f', 'х' => 'h', 'Х' => 'h', 'ц' => 'c', 'Ц' => 'c', 'ч' => 'ch', 'Ч' => 'ch', 'ш' => 'sh', 'Ш' => 'sh', 'щ' => 'sch', 'Щ' => 'sch', 'ъ' => '', 'Ъ' => '', 'ы' => 'y', 'Ы' => 'y', 'ь' => '', 'Ь' => '', 'э' => 'e', 'Э' => 'e', 'ю' => 'ju', 'Ю' => 'ju', 'я' => 'ja', 'Я' => 'ja'];

      $transliteratedString = str_replace(array_keys($transliterationTable), array_values($transliterationTable), $string);

      return trim(strtolower($transliteratedString));
  }
}

The two functions look complicated at first sight, but they are not really.

Explanation and result (sorting the multidimensional array) 🧙‍♂️

The first function receives 3 parameters, the multidimensional array reference (or array of objects), the element and key that are used to sort the array.

Let’s say we want to order the previous array by french names. The parameters would be :

alphabeticalCompareArrayByKey($categories, 'names', 'fr');

As we are sending the array as a reference, no need to reassign it to a variable. The usort function sorts an array by values using a user-defined comparison function.

Our comparison function is a binary safe case-insensitive string comparison : strcasecmp().

Within the comparison we make sure the accented characters are replaced with the adequate ones (ie. é = e, â = a).

Our comparison will then be successful. 💪

The previous example only works with an array of objects, but you could easily adapt it to compare an array of array by modifying the strcasecmp() part by :

return strcasecmp(self::transliterateString($a[$element][$key]), self::transliterateString($b[$element][$key]));

Let me know if this article helped you to sort your sorting problems!

Cover Image : Edu Grande (@edgr) from Unsplash

DEV Community: William L'Archeveque

Disabling foreign key constraints in MySQL

How to effectively and completely delete a git submodule

Git error “fatal: refusing to merge unrelated histories” explanation

"Refusing to merge unrelated histories" solution

Custom YouTube thumbnail and play button for embedded iframe video

Technical explanation to use a custom Youtube thumbnail

Avoid long-running Gulp tasks with this simple Gulp caching plugin

Gulp is killed because of a long-running process

Gulp Killed — The memory usage problem

Process Gulp tasks only if needed, thus, when assets have changed in our themes

Discovering existing Node solutions that did not do the trick

Complete Gulp tasks only if assets have changed with gulp-process-if-modified

wlarch / gulp-process-if-modified

This gulp plugin will passthrough source files if they have been modified since last build.

gulp-process-if-modified

Avoid long running Gulp builds

Alternatives depending on use case

Install

Usage

Sorting multidimensional PHP arrays by object values with accented character

The problem with sorting special characters

The solution for sorting an array of objects

Explanation and result (sorting the multidimensional array) 🧙‍♂️

Complete Gulp tasks only if assets have changed with `gulp-process-if-modified`