WebDev

Laravel Seeding Large Amounts of Data From a File

Environment:

  • PHP 8.0
  • MySQL 8.0
  • Laravel 8.0

I recently had to pre-populate a database with a large amount of data. The data consisted of a list of around 800 brand names. To do this in a Seeder class was obviously going to make a very large seeder file, hard to maintain if that list changed during development. I wanted to use an external file to seed the data. This would allow me to export the names from an Excel file or just about any other way the list could be provided to me. Here’s how I did it. Maybe it will help you if you have a similar situation.

The list was provided as an Excel file with one long column of brand names.

First things first, I exported the data as a simple csv file, which resulted in a basic list of each item on it’s own line, without commas since there was only one column.

Next, I opened that file in Sublime Text (my go-to text editor these days) for cleanup. I found some garbage in the names, probably due to the way the list was generated. For example, one of the lines was this:

“Febreze Linen & Sky,"

The comma would be a problem and I didn’t want the quotes around the text. Using replace all, I could fix those up.

The last step in preparing the file was to make one long comma separated list. I replaced the new-lines with , (no space), and saved it in the /public/seed-data folder of the project. Note: This is the root level public folder, not the /storage/app/public folder although you could use that too.

Next, I opened the BrandSeeder file. If you don’t already have it, you could create it using

php artisan make:seeder BrandSeeder.

I’ll show the code expanded a little more than my final version to allow for comments explaining each line.

public function run()
{
    // Using public_path() points to the /public folder
    //   instead of the /storage/app/public folder.
    $path = public_path('seed-data/Brands_list.csv');
    
    // Use File::get() instead of Storage::get() to retrieve
    //   the contents of the file.
    $fileContents = File::get($path);
    
    // The contents are brand1,brand2,brand3,etc... so splitting it
    //   with commas works perfect.
    $items = explode(',', $fileContents);
    
    // Iterate over the array and insert the values into the table.
    foreach ($items as $item) {
        DB::table('brands')->insert(['brand_name' => $item]);
    }
}

Here’s the file after removing comments and combining actions for brevity:

<?php

namespace Database\Seeders;

use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\File;

class BrandSeeder extends Seeder
{
    public function run()
    {
        $items = explode(',', File::get(public_path('seed-data/Brands_list.csv')));
        foreach ($items as $item) {
            DB::table('brands')->insert(['brand_name' => $item]);
        }
    }
}

You can test your seed file using

php artisan db:seed --class=BrandSeeder

I use MySQL Workbench to view the table contents.

I hope this helps!

Uncategorized

Deleting files exactly in windows cmd or .bat

Background

I recently needed to delete a set of files after compiling them using a .bat file script to help with a build process. The previous step in the process generated .jsxbin from jsx files. These are compiled binary versions of plain-text ExtendScript source code. I used the VSCode ExtendScript Debugger extension which has very limited capabilities. It moves recursively through a directory provides the jsxbin files in the same folder as the jsx, without deleting the jsx files. There isn’t an option to do anything different so, I deal with it.

Initial thought

I’ve been in software for quite a while and never thought this problem would be so hard. My initial thought was to use this:

del /S *.jsx

Delete recursively any file that ends in .jsx…

Wrong. It also deleted the jsxbin files. The Windows operating system has been around for a while but apparently it still doesn’t have a quick command to delete exactly what you tell it to.

This isn’t a new problem, I thought to myself. There must be an equally simple way to tell it not to delete files with an extension other than jsx.

Nope. At least, I never found it.

The Solution

So, based on what I found after reading various Stack Overflow posts, I wrote a bat file just for this purpose. It needs some modification to make it more generic but for now, it does what’s necessary.

@echo off
pushd %1
for /f "eol=: delims=" %%F in ('dir /b /s *.jsx^|findstr /lie ".jsx"') do del %%~fF
popd

That’s right. All that is to replace del /S *.jsx.

Saving that in a bat file called delext.bat, I can now enter something like

delext.bat C:\path\to\directory

and it will delete all the jsx files it finds, recursively, without deleting the jsxbin.

If I have the time, I’ll update this post to show a more generic version.

WebDev

Automatic “latest version” link

If you have software that you version and want to have a single link that will always download the latest version, here is a way to do that.

We create installers with the naming convention <product name>_major.minor.build.pkg or exe. For example, SiliconConnectorForBoxCC_2.0.12.pkg. I created a php script that would parse the files it finds and return the latest version.

<?php
/**
 * latestmac.php
 * Returns the file with the latest version number.
 * IMPORTANT: Assumes filename_1.2.3.pkg format, meaning
 * an underscore separates the version from the rest,
 * and it has .pkg (4 characters) after the version.
 */

$files1 = scandir(".");
$file_read = ["pkg"];
$filearray = [];

function compareVersions($file1, $file2) {
 $version1 = substr(explode('_', $file1)[1], 0, -4);
 $version2 = substr(explode('_', $file2)[1], 0, -4);
 return version_compare($version1, $version2);
}

foreach ($files1 as $key => $value) {
 if (!in_array($value, array('.', '..'))) {
  $type = explode('.', $value);
  $type = array_reverse($type);
  if (in_array($type[0], $file_read)) {
   $filearray[] = $value;
  }
 }
}

uasort($filearray, 'compareVersions');
$filename = $filearray[sizeof($filearray)-1];

if (file_exists($filename)) {
 header('Pragma: public');
 header('Expires: 0');
 header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
 header('Cache-Control: private', false); // required for certain browsers
 header('Content-Type: application/octet-stream');
 header('Content-Disposition: attachment; filename="'. basename($filename) . '";');
 header('Content-Length: ' . filesize($filename));
 readfile($filename);
 exit;
}

?>

Visiting the url (i.e., example.com/downloads/latestmac.php) will trigger the file to be downloaded.

Uncategorized

Coloring shell scripts

Adding color to shell scripts is actually quite easy. Here’s an excellent page describing how: http://misc.flogisoft.com/bash/tip_colors_and_formatting

I would add that it becomes easier if you use variables. Then, it’s almost like a css class for your terminal output. For example, if I want to make bold green success text output by one of my scripts, I might do something like this:

#!/bin/bash
SUCCESS='\033[1;32m' # Bold;Green
NC='\033[0m' # No Color
# ...commands
echo "Almost there..."
echo -e "${SUCCESS}Success!${NC}" #shows in bold green

The -e after echo enables the parsing of escape sequences. It’s important to have there.

There are a lot more ways you can use the formatting for your scripts. Explore and enjoy!

WebDev

Laravel installation on MediaTemple

Here are the steps I took to get a Laravel project up and running on MediaTemple’s Grid Service. At the time of this writing, I was using Laravel 5.2.29.

Throughout this tutorial, I’ll use some example values so I don’t have to provide my own info. The GRID server will use 123456 as it’s id, which gets used in a number of places. I’ll use example.com as the domain you’ll be installing Laravel to.

SSH to your GRID server

Open your GRID Control Panel and select View Your Guide from the Server Guide panel. You’ll see the Access Domain listed there under the Domains heading. Scrolling down, you’ll find the SSH heading where you can see the Server Administrator Username and change it’s password if you need to. Make note of this info or keep that page open to use for reference later.

SSH to your server using the SSH Server Administrator Username and the gridserver Access Domain.

marty$ ssh example.com@s123456.gridserver.com
The authenticity of host 's123456.gridserver.com (205.186.179.218)' can't be established.
RSA key fingerprint is f3:12:b3:42:cb:02:b9:99:07:d2:5b:c4:4d:42:6e:eb.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 's123456.gridserver.com' (RSA) to the list of known hosts.
Password:

You must use the administrator SSH account to connect via SSH. If you use the link in the Server Guide panel to Add/Edit FTP & SSH Users, those users will not have access to the domains folder. Don’t ask my why you would add an SSH user that couldn’t access anything outside it’s home directory but it’s irrelevant. You’ll probably need to accept the connection if it’s new. The password here is the password you would have set for the Server Administrator Username you saw in the View Your Guide page, not your MediaTemple login.

You need to be sure you’re using the latest PHP version and you can check that with php -v. If your php version doesn’t meet the minimum requirements, you can set it in the control panel. If the php version used in your current SSH session is not the latest, you can set the version used in your SSH session by creating a .bash_profile file in the home directory. At the time of this writing, the latest available on MediaTemple was 5.5.31.

Git your project files

If you have your Laravel site already created you can get your files up on the server the traditional ftp way but Git is available on the servers so if your project is stored in Git (recommended) then you can have a nice way to keep it up to date by checking out the master branch. NOTE: The version of Git installed on the shared server I am using is 1.7.10.4, which was released back in  2012. This is incredibly disappointing to me and their support says they have no plans to update it.

Since you already have a folder in the domains folder named after your domain, you can back it up using the mv command. Change to your domains folder, backup your existing domain folder and clone your project using

example.com@n11:~$ cd domains
example.com@n11:~/domains$ mv example.com example.com.backup
example.com@n11:~/domains$ git clone https://github.com/Username/projectName.git example.com

You’ll be prompted for your GitHub username and password. Enter those and it will download your project files into the domain folder.

Install Composer

To get composer, you will probably need to add these lines to your php.ini file.

# allows php to open urls
allow_url_fopen = On
open_basedir = ""

Now, you’ll be able to download composer. You can use this post for great instructions on how to accomplish this: https://forum.mediatemple.net/topic/6927-here-is-how-to-install-and-use-composer/

In a nutshell, you’ll use these command after changing into the example.com folder.

example.com@n11:~/domains$ cd example.com
example.com@n11:~/domains/example.com$ php -r "readfile('https://getcomposer.org/installer');" > composer-setup.php
php -r "if (hash('SHA384', file_get_contents('composer-setup.php')) === '7228c001f88bee97506740ef0888240bd8a760b046ee16db8f4095c0d8d525f2367663f22a46b48d072c816e7fe19959') { echo 'Installer verified'; } else { echo 'Installer corrupt'; unlink('composer-setup.php'); } echo PHP_EOL;"
php composer-setup.php
php -r "unlink('composer-setup.php');"

Composer should download and successfully install.

If you try something like php composer.phar self-update and if it doesn’t work, check that it has executable permissions by using chmod +x composer.phar.

Now you should be able to use php composer.phar install, right? In my case, no. I received this error when using composer install:

[ErrorException]
 is_dir(): open_basedir restriction in effect. File(/etc/pki/tls/certs) is not within the allowed path(s): (/nfs:/tmp:/usr/local:/etc/apache2/gs-bin)

To solve that error, I had to change the open_basedir setting in the /etc/php.ini to open_basedir = "". I mentioned adding this at the beginning of this section.

Now you have to get the ca-certificate. This post gave me the info: https://github.com/composer/composer/issues/2798

example.com@n11:~/domains$ wget http://curl.haxx.se/ca/cacert.pem
example.com@n11:~/domains$ wget https://github.com/bagder/ca-bundle/blob/master/ca-bundle.crt

Then add these lines to the bottom of the php.ini file. You can use the File Manager to accomplish this. See https://mediatemple.net/community/products/grid/204403894/how-can-i-edit-the-php.ini-file for more details on how to edit the php.ini file.

# Cert locations
curl.cainfo=/home/123456/domains/ca-bundle.crt
openssl.cafile=/home/123456/domains/cacert.pem

We also need to set some environment variables since composer doesn’t seem to pick up the cert location from the php.ini. I’m a little fuzzy on this stuff so perhaps the previous step isn’t actually needed..? Open ~/.bash_profile on the server and add the following lines

export SSL_CERT_FILE=/home/123456/domains/example.com/cacert.pem
export SSL_CERT_DIR=/home/123456/domains/example.com/
# also, be sure you have an alias to composer

These commands set the environment variables so composer knows where to find them. The following command makes them active in your current session.

example.com@n11:~/domains$ source ~/.bash_profile

At this point, I was able to use composer install and it worked, almost.

example.com@n11:~/domains/example.com$ php composer.phar install
Loading composer repositories with package information
Installing dependencies (including require-dev) from lock file
Nothing to install or update
Generating autoload files
> Illuminate\Foundation\ComposerScripts::postInstall
> php artisan optimize

Parse error: syntax error, unexpected T_CLASS, expecting T_STRING or T_VARIABLE or '$' in /nfs/c08/h03/mnt/118186/domains/example.com/artisan on line 31
Script php artisan optimize handling the post-install-cmd event returned with an error


 [RuntimeException]
 Error Output:


install [--prefer-source] [--prefer-dist] [--dry-run] [--dev] [--no-dev] [--no-plugins] [--no-custom-installers] [--no-autoloader] [--no-scripts] [--no-progress] [-v|vv|vvv|--verbose] [-o|--optimize-autoloader] [-a|--classmap-authoritative] [--ignore-platform-reqs] [--] [<packages>]...

Everything seemed to go okay until that last bit there where php artisan optimize fails. After that ran, I just ran php artisan optimize and it worked fine. I don’t know much more about that error since it didn’t cause any issues after that.

You need to create a symbolic link to the public directory named html since that is the default directory MediaTemple uses.

example.com@n11:~/domains/example.com$ ln -s public html

Now if you cd html, it should take you to the public folder.

Create your database

Make your database from the MediaTemple admin. From the Grid control panel it’s in the lower left under Databases. Use the Manage Databases link to view your databases and add a new one. Let’s say you call it “exampledb”. You’ll probably end up with something like db123456_exampledb. You’ll need the database internal hostname which probably looks something like internal-db.s123456.gridserver.com. You can find that on the Users & Settings link at the top. Put the internal hostname, username and password into the .env file. You will probably have to copy the .env.example file as .env. I also had to set  CACHE_DRIVER=array in the .env file.

example.com@n11:~/domains/example.com$ cp .env.example .env
example.com@n11:~/domains/example.com$ nano .env

After you’ve made the changes, use ^O to save the file, then ^X to exit. To populate the APP_KEY in the .env file, use the standard laravel artisan command.

example.com@n11:~/domains/example.com$ php artisan key:generate

Then migrate.

example.com@n11:~/domains/example.com$ php artisan migrate

Test your site

At this point, your Laravel application should show and run correctly.

Figuring all this out took hours online with MediaTemple support and searching the web for clues to each problem I ran into. Hopefully, this can save you some time. Please comment if there is a mistake in the steps. I bounced around so much, I wouldn’t be surprised if I missed a step when writing the blog post.

 

Uncategorized

How to create a default date range for google search

1 Comment

Something I have been wanting a LONG time has been the ability to set my google searches to default to Past Year. Since I do a lot of software related searches, results from 5 years ago are usually irrelevant.

I did a search on the structure of google search urls and found this post: http://www.our-picks.com/archives/2007/01/30/google-search-urls-revealed-or-how-to-create-your-own-search-url/

Here’s how to set up Chrome to use the past year as your default search range:

  1. Go to settings.
  2. Under the Search heading, click the Manage Search Engines… button.
  3. Scroll to the bottom of the page, so you see the 3 fields for entering your own search engine.
  4. Put these in the three fields: 
    1. GoogleYear
    2. search.google.com
    3. {google:baseURL}search?q=%s&as_qdr=y&{google:RLZ}{google:acceptedSuggestion}{google:originalQueryForSuggestion}{google:assistedQueryStats}{google:searchFieldtrialParameter}{google:searchClient}{google:sourceId}{google:instantExtendedEnabledParameter}ie={inputEncoding}
  5. Click the “Make default” button that appears at the end of the row you just made.
  6. Click DONE and you’re set. Now your omnibox will always default search results to the past year unless you change it the usual way from the Search Tools at the top of the results page.

For other browsers, you can do something similar with a bookmark to the following: http://www.google.com/search?as_qdr=y or by setting up a custom search engine similarly.

Enjoy!