Creating A Trigger On A Second Database Using Laravel Migrations

Problem:

I have a client whose application works with two separate databases.  One of the functions within the application copies data from database 1, to database 2 in a different format.  Additionally, when inserting into database 2, a trigger is called on database 2 to pull a pre-generated "identifier" and save that along with the entry into database 2 row. Whew.

Well, it works in production and dev, but I need to know how to be able to test this piece, as the client controls both databases...ideally, I'd like to create a migration that creates the trigger, so that when I perform my phpunit tests, I can replicate the same intended behavior.

How?

Within the migration, you have to specify the connection, then call the "unprepared" function.

Example:

public function up()
   {     

    DB::connection('whateverYourSecondDatabaseConnectionIs')->unprepared("
       create trigger GetIDFromOtherTable
         BLAH BLAH BLAH TRIGGER INFO
       ;");

   }

public function down()
{
    \Illuminate\Support\Facades\DB::connection('whateverYourSecondDatabaseConnectionIs')->unprepared("
    DROP trigger if EXISTS GetIDFromOtherTable;");
}

Laravel 5.5 - Flysystem - SFTP - Connection Closed By Server

Recently, I had an error occur where I had to connect to the same SFTP server twice within an hour and access separate directories.  The first connection would always work, but the second failed with a "ErrorException: Connection Closed By Server".

After TREMENDOUS amounts of searching, I stumbled over Gabor Javorsky's article "Laravel 5.6, SFTP, connection hygiene and you!"

The basic gist is that the disconnect isn't being called on the SFTP server for me automatically.  My fix was writing a one line helper function to disconnect the file system after the file has been obtained.

~~~

/**
* $this->remoteDisk is a variable set at  the  top  of  the   file,  which contains 
* the reference to the disk's configuration name.
* 
* The logException function is a helper that I  made that formats the exception and stores
* in my database.
*
* I am not here for your public vs private vs protected discussions.
**/

public function closeConnection()
{
    try {
        Storage::disk($this->remoteDisk)->getAdapter()->disconnect();
        return true;
    } catch (\Exception $ex) {
        logException((new \ReflectionClass($this))->getShortName(), 'Generic Exception', 0, (new \ReflectionClass($this))->getShortName(), $ex->getMessage());
        return false;
    }

}

~~~

Single Best Addition To Your Laravel Testing Suite

https://hackernoon.com/how-i-destroyed-the-staging-database-b435f98569ab

Marco Aurelio Deleu documents how to prevent mistakenly overwriting your production or development database by adding a few checks to the start of the testing application. 

Laravel 5.5 - Multiple Web Route Files (Splitting Them Up)

### Issue

My web.php routes file is huge, and could use a little love.

### MUCH BETTER SOLUTION THAN WHAT I DID CAN BE FOUND HERE:

https://medium.com/@thesourav/organize-your-laravel-routes-for-better-and-maintainable-code-4ad9b76aed0f

### ;TLDR

Split the files into separate php files, then go to your RoutesServiceProvider, and append the new files there.

### Solution (Images below the directions)

First, I created a new folder under routes called "web"...I figured that if I implemented an API in the future, I wouldn't want them co-mingled..so best to keep separate.

1. I split out separate .php files based on what made sense to me:
   1. Security  = logging in / updating password / password reset
   2. Errors = this was my original test, but decided to keep it...a centralized place for all my error routes (all 4 of them ;)
   3. Admin - this is the big one...and might get split off again as I have an admin "api" that is used for vue ajax requests / graphing stuff.  Perhaps might be changed to an "admin" folder, with "web" and "api" within that....but I digress..
2. Once all files are created, go to the app/providers/RouteServiceProviders.php file, and update your mapWebRoutes function to allow for the inclusion of the separate files:

```

protected function mapWebRoutes()
{
    Route::group([
        'middleware' => 'web',
        'namespace' => $this->namespace,
    ], function ($router) {
        require base_path('routes/web/errors.php');
        require base_path('routes/web/security.php');
        require base_path('routes/web/admin.php');
        require base_path('routes/web.php');
    });
}

```

Image 1: web.php

Image 2: The split out security.php file within the /web folder

Image 3: The updated RouteServiceProvider

Laravel 5.5 - Custom Validator - Check SECOND UNIQUE Column Of Database

Issue

Laravel 5.5 has the ability to create custom validators, and all the examples I have seen are items like "make sure it's not more than 5 characters", "make sure it's a proper phone number", etc.  Those are GREAT.  However, I needed my validator to be a bit more for a specific situation.

Here's the situation:

I have a Growers table, and each grower has an autogenerated "id" field, but can also have an "assigned_grower_id" as a second column.  This value can be a number, text, whatever - but it must be unique. Examples of this might be "store number 2345" or "My Favorite Grower" . 

 I have setup the migration so that the table has the row "unique" tag placed on it.  When building the database using "php artisan migrate" the column will be built with the unique requirement and an index will be built based off that column.  GREAT.

When building the create function in my controller, I can use Laravel's built in "unique" constraint because I want to make sure that the "assigned_grower_id" does not already exist in the database. Cool.  All Good.

Request - "Users need to have the ability to update the assigned grower id."

Herein lies the problem.  When creating the "update" function off my controller, I can no longer use the "unique" constraint again.  This is because the user may NOT be updating the existing "assigned_grower_id" but I need to check for it.  This is to make sure someone hasn't accidentally deleted it OR making sure that they are not passing through a value already assigned to another grower.  We can keep the "required" option but not the unique.  BUMMER.

Problem I need to solve

Let's say a grower exists with an assigned_grower_id of "My Favorite Grower" andanother grower is set with an assigned_grower_id of "My Second Favorite Grower".   Both of these will work.

Now, suppose we want to change the second grower's assigned_grower_id to "My Favorite Grower". 

Since we only have the "required" validator on this field (and not the unique validator), Laravel would allow the request through to the database.  The request would fail at the database level (because we assigned the unique constraint on the table itself) so the data would NOT get into the database.  However, the database returns a nasty "unique constraint violation" which we then have to handle, and figure out how to add it to Laravel's default ErrorBag.  NAW. I think not. Custom Validators TO THE RESCUE.

What's the overall validation that needs to happen:

1. If a user is updating a grower, and passes through an assigned_grower_id
2. We need to look in the database and make sure that the assigned_grower_id is NOT already in use by ANY GROWER OTHER THAN THE CURRENT GROWER FROM OUR REQUEST.
   1. This check will allow a user to pass through the grower's current assigned_grower_id.
3. This custom validator needs to be usable throughout my application
   1. Creating a one off custom "VerifyAssignedGrowerIDHasNotBeenUsed" is a waste of time. 
   2. Creating a "NotAlreadyAssigned" validator that works anywhere...well...(kisses fingers like chef).

Solution

Image 1:

App\Rules\NotAlreadyAssigned.php

Image 2:

Image 3

1. Create a new rule using "php artisan make:rule NotAlreadyAssigned"  (image 1)
2. We will need to pass two pieces of information into this validator: (image 2)
   1. The model that we need to query
      1. I am calling this "modelToCheck"
   2. The "allowed ID" of the grower that is allowed to have the assigned grower id
      1. I am calling this "allowedId"
3. Create protected variables for the items we'll be passing in, and then set them up in your constructor. (image 2)
   1. When we pass in the model we need, the validator converts it to a string. This is important for the handle method.
4. In the handle method, we'll need to convert the string "model" back into a class.  To do that we use the app($this->modelToCheck) function, and pass in our model variable that we set in the constructor. (image 3)
   1. This gets us all of the capabilities as if we had called \App\Grower::class, which means we can now use Eloquent to filter some results. YEAH BOYYYEEE.
5. Our first where clause checks to see if the 'assigned_grower_id' that was passed in exists in the database already.  (image 3)
   1. Remember, it might exist because our CURRENT grower already has it assigned OR another grower could be using it.
6. Our second clause filters out the "allowedID".  (image 3)
   1. This is the ID of the current grower that we are updating.
   2. If this grower already has this assigned_grower_id assigned, then we'll remove it from the returned results.
7. This will leave us only with growers that don't have our current primary key ID, but DO have the assigned_grower_id that can be set to anything. (image 3)
8. We apply a "->count()" to this. Anything greater than 0 means another grower is currently using that "assigned_grower_id" and we will return "false" for the validation. (image 3)

OK How Do We Call This Monstrosity?

Within Your Controller's Update function:

Notice that we are passing in the "Grower::class" and $growerId.  We will be able to reuse this validator for ANY other models we wish to test by passing in the same type of information.  For example, if we needed to verify a second column for a "Customer", we'd pass in Customer::class, and $customerId (or whatever the id is of the current model you are attempting to update).

How Does One TEST This Thing?

1. In the above method, "$this->editGrower" is a helper command that just takes the grower that I have created already, and posts the update to the appropriate end point.  

Laravel Horizon Not Updating For Additional Queues

FULL STOP.  The single best thing you can do is go read William Vicary's article on setting up long running jobs with Laravel Horizon.  It's amazing, and will walk you through a complete setup. He does it BETTER and more succinctly that I did.

https://medium.com/@williamvicary/long-running-jobs-with-laravel-horizon-7655e34752f7

Issue

Laravel Horizon Not Updating For Additional Queues

;TLDR

1. When adding a new queue to your app/config/queue.php file (see QUEUE image):

2. Make sure you ALSO add it to the app/config/horizon.php file underneath the supervisor for production and/or local depending on your needs (see HORIZON image):

3. Restart horizon (php artisan horizon:terminate, then php artisan horizon) 

QUEUE Image:

HORIZON Image:

Background

I created a new queue within redis that is meant to hold all my jobs that are responsible for pulling data in from various external API’s, and restarted the Horizon process. I dispatched a job that used the new queue, and saw the job appear on the Horizon queue with the yellowish-orange (technical term) pause button…which means it was kicked off. Five minutes later, it was still that color…and on prior runs, this process took approximately 5 seconds.

RUH ROH RAGGY:

LINK TO ARTICLE

Solution

I couldn’t find anything in the Laravel docs regarding providing additional details on “adding more queues to Horizon” - but notably, I could have just missed them (I didn’t).

I did find a Deep Dive on Horizon by Mohamed Said, which had the following intriguing picture:

I added my ‘external-api’ queue to both entries for the horizon supervisor and rebooted Horizon. The queues initially loaded in their same state (yellow-orange pause button), but immediately changed to the green checkmark.

KISSES FINGERS LIKE CHEF:

Laravel 5.5 - Setting The Queue On A Scheduled Job

 ;TLDR

When using Laravel, and you would like to push a scheduled job onto a specific queue, pass the queue as a second parameter in the scheduled job request:

protected function schedule(Schedule $schedule)
{

$schedule->command('horizon:snapshot')->everyFiveMinutes();
$schedule->job(new PopulateCustomerSurveys,'external-api')->dailyAt('04:00');
$schedule->job(new PopulateConsumerSurveys,'external-api')->dailyAt('04:05');
$schedule->job(new PopulateCustomerSurveyResponses,'external-api')->hourlyAt('10');
$schedule->job(new PopulateConsumerSurveyResponses,'external-api')->hourlyAt('32');
}

Background

In trying to build for the future, I am creating multiple queues. Right now, I have a “default” queue, and an “external-api” queue. In the future, I could see adding a “notification-email” queue / “notification-sms”, etc, etc…but right now it’s important to be able to specify the queue I want to run it on. 

Turns out there was a recent addition to Laravel 5.5 that allows this (woot).

Link to EXACT THING ON GITHUB

Laravel 5.5.14 - PHPUnit 6 - Policy - Action Not Authorized

Issue

After upgrading to Laravel 5.5.14 from 5.4, I received errors on two tests that “This action is unauthorized”. Both tests were delete commands, and both employ the use of policies.

;TLDR

Make sure that you have a function named for the action you are authorizing against. In this case, I was calling:

$this->authorize('delete',$contactType);

did not have a “Delete” function created within the ContactType Policy.

Background

In this application, I am only allowing Site Administrators to remove the Contact Types. Therefore, in the “Before” function, I am checking if the user is a Site Administrator. If he is, the allow the action to occur. If he is NOT, then proceed to the actual method and review the authorization there:

public function before($user, $ability)
{
if (! $user->getSelectedRole()) {
return false;
}

if ($user->isSiteAdministrator()) {
return true;
}
}

did not have a “Delete” function created, as no other user type is allowed to perform this action…and if the function didn’t exist, it returned false. Cool.

Solution

In 5.5, I guess a change was made to check that the requested function exists first, then process the “before” method. I’m not 100% on this as the issue, but I simply corrected the error by creating a “delete” method within the policy:

/**
 * Determine whether the user can delete contact types.
 * @param\App\User $user
 * @return mixed
 */
public function delete(User $user)
{
return false;
}

Laravel - JSON - Failed Authorization Returns HTML Instead of JSON

ISSUE:

JSON requests to backend that fail middleware checks return HTML page vs JSON Object

;TLDR:

Within each middleware check created, add a JSON check for the request, and included the appropriate message as well as the status code.

DETAILS:

I am in the process of changing the CRUD operations of my template to Vue/axios instead of old school page posts to a backend controller. 

Prior to this change, I was using a combination of middleware to verify access to the controller, and individual policies for each of the commands (Create,Read,Update,Delete).  For example, I may want a certain class of user to be able to view the resources, but only administrators can update the resource.  I grant both roles the "view-{modelName}" permission, where {modelName} is the actual model name of the resource (location, company, etc).  Then I grant the admin role the permission of 'edit-model', and use the policy of the resource to validate their access.

In changing over to a json POST, I'm now expecting a JSON response - which works great for successful submissions and validation errors.  However, I was finding that in my authorization tests that verify a specific user can not access a resource, I noticed that my JSON requests were always returning the HTML "Insufficient Privileges" page instead of the JSON version.

This is because the user verification was not passing my middleware permissions check. It was working as intended, but was only setup to return the HTML response and NOT JSON.

CODE:

App/Controllers/Middleware/CheckPermission.php:

public function handle($request, Closure $next, ...$permissions)
{
    $userRole = $request->user()->getSelectedRole();

    if ($userRole !== null) {
        foreach ($permissions as $permission) {
            if ($userRole->can($permission))
            {
                return $next($request);
            }
        }
    }
    // here is the change I added
    if ($request->expectsJson())
    {
        return response()->json(['success' => false,'message' =>"Sorry, you are not authorized"], 403);
    }

    return redirect('/insufficient-privileges');
}

Laravel EagerLoading ALWAYS INCLUDE...

ISSUE:

There are certain models that have relationships that I ALWAYS want loaded.  

QUICK FIX: 

In the model for the item, add a protected $with variable.  See the following code:

Class MyModel extends Model {
protected $with = ['relation'];
}

In my case, I have a Location model, and the associated model is an Address model.  Locations can have multiple addresses, and MUST have at least one address.  In my case, the code would look like the following:

Class Location extends Model {
protected $with = ['addresses'];
}

Now, whenever I return a location object, it will automatically include the associated addresses with it.

Caveat?

This definitely isn't the solution for EVERY associated relationship - only the ones that are likely to be needed 80% or more of the time.  Perhaps even a little less.  However, the solution that Eager Loading provides outweighs most of the concerns that might come up from loading a small amount of resources every time.