hmazter.com

Web and application development

2018-09-25

Using database views as tables for Laravel Eloquent

I found myself in a situation where I had a legacy database which I was going to build a new Laravel application against. The database did not have a naming convention that played nice with Laravel and it did include a lot of tables and columns that was not in use anymore.

After some Googling and reading about how to alias column names in Laravel, I got the idea "How about putting a database view on the base table and use that as table for the Eloquent model". I started with some basic benchmarking to see if it was even a viable solution.

Benchmarking

I did some very simple benchmarking by creating a Laravel app that had 2 model, one directly against the database table and one against a database view containing a subset of the columns. Filled the database with 10 000 rows and fetch with the model based on the table, the model based on the view, using the DB facade to fetch from the table and the view.

Here is the repository if you want to check out what i did.

Benchmark result

php artisan fetch-posts-from-view  0.50s user 0.07s system 97% cpu 0.583 total
php artisan fetch-posts-from-table  0.50s user 0.07s system 98% cpu 0.575 total
php artisan fetch-posts-from-table-query  0.16s user 0.06s system 95% cpu 0.227 total
php artisan fetch-posts-from-view-query  0.17s user 0.07s system 93% cpu 0.251 total

I ran those a couple of time and they gave it pretty much the same result every time.

Performance Conclusion

Using those numbers I think my idea of using a view for the models in Laravel is okay from a performance perspective. There are other things that has a lot of more impact then just that, for example how the query is written, the hydration of objects, etc.

Migrations

Since database views only live "in memory" and is not permanently created (but they do live trough server restarts), I needed a easy way to create the views and change them over time without having to put the whole definition in a migration file every time.

I landed in a solution where I wrote a basic artisan command that takes all files from a folder, in this case database/views that I created, and creates a view with the name of the file and the select statement described in the file.

// app/Console/Commands/DbCreateViews.php

foreach ($this->filesystem->files() as $filename) {
    $viewName = str_replace('.sql', '', $filename);
    $viewContent = $this->filesystem->get($filename);

    $this->line("Creating database view $viewName");
    DB::statement("DROP VIEW IF EXISTS $viewName");
    DB::statement("CREATE VIEW $viewName AS $viewContent");
}

Example view file:

# posts.sql

SELECT postid          AS id,
       posttitle       AS title,
       postcategoryid  AS category_id,
       creationdate    AS created_at,
       lastupdated     AS updated_at,
       removeddate     AS deleted_at
FROM tbl_post

Summary

I pretty happy with the outcome from this. It seems that its no performance overhead for the views. Looks like Laravel has no problem using a database view for a model. Ad it simplifies the table and column naming a lot.


2018-08-11

Move from wordpress to static site with Jigsaw

I have read about the static site generator Jigsaw the last couple of years. And after seeing a lot of hype around Netlify lately, I was thinking it could be a good idea the convert my blog running on Wordpress to a static site with Jigsaw. Jigsaw builds on Laravel components and uses the templating engine from Laravel; Blade. Since I'm mostly do my web development in with Laravel, this was a good choice for me. Jigsaw also supports Markdown for pages, which is good since I wanted to write the actual blog posts in Markdown. Seeing Michael Dyrynda's post and video about getting Jigsaw up and running on Netlify I took that as a sign that now was the time to do it.

Getting started

I started with the Jigsaw installation and installed and initialized a new folder

mkdir hmazter.com
cd hmazter.com
composer init
composer require tightenco/jigsaw
./vendor/bin/jigsaw init

Export existing posts and convert to markdown

I don't have that many posts on this site, but those I have I wanted to keep on the new site. After some searching I found the post about converting the exported xml from a wordpress site to markdown: https://prefetch.net/blog/2017/11/24/exporting-wordpress-posts-to-markdown/

I exported the xml from my site, used pip to install Pelican to use pelican-import and tried the command from the post. Problem arouse! pelican-import wanted pandoc installed on the system to handle the markdown conversion. I installed it, but the version of pandoc that was installed with brew was not compatible with the version of pelican-import. I found a workaround in a Github issue, to modify the source and install it from there.

Cloned the Pelican github repo and updated pelican/tools/pelican_import.py as follow:

-        parse_raw = '--parse-raw' if not strip_raw else ''
-        cmd = ('pandoc --normalize {0} --from=html'
-               ' --to={1} -o "{2}" "{3}"')
+        parse_raw = '-raw_html' if not strip_raw else ''
+        cmd = ('pandoc  --from=html'
+               ' --to={1}{0} -o "{2}" "{3}"')

And then installed with python3 setup.py install. After that, the command to convert the posts to markdown worked!

In the posts there is also parts to automatically fix the front matter in the markdown. But since i only had 9 posts I thought it would be easier to just add and edit the parts needed by hand

Layout, post template and post collection

I created a very basic master layout, source/_layouts/master.blade.php and a template for the posts to extend source/_layouts/post.blade.php. Then created a collection in the config to hold all blog posts.

// config.php

return [
    'collections' => [
        'posts' => [
            'path' => '{date|Y/m}/{slug}',
            'sort' => '-date'
        ],
    ],
];

What this does is takes all files from the folder source/_posts, sorts them on the variable date in the front matter and outputs them with a path in the format year/month/slug. I used this path format to match the format I used on my Wordpress site.

The posts is written in markdown, extends the post layout, that in turn extends the master layout.

After I had a working version of the blog with post I added a simple, and according to me, elegant design with Bulma.io.

I did all this with yarn watch running to get all assets auto updated, but also the static site rebuilt and reloaded in the browser.

Pagination of posts

On my previous wordpress blog the index page was a pagination of all the posts and I wanted similar behaviour here. That was pretty straight forward, Jigsaw includes pagination functionality. Just specify which collection and pagination count on a page and the loop over the result.

---
pagination:
    collection: posts
    perPage: 3
---

// ...

@foreach ($pagination->items as $post)
    <h1 class="title">
        <a href="{{ $post->getUrl() }}">{{ $post->title }}</a>
    </h1>
    // ...
@endforeach

I also added a widget to the sidebar listing the latest posts.

Deploy to Netlify

Now I hade enought of a site to deploy it for testing. Signed up at Netlify with Github. Pushed the code to my (then private) Github repository. And created a "New site from Git" in Netlify.

During set up of the new site a specified the build command to yarn production which works similiar to the previous mentioned yarn watch that it builds the assets (css and js) in production mode this time and also generated the static site. The output from the build is placed in the folder build_production, that is our "Publish directory" in the set up of the site.

Environment

From what I read, Netlify defaults to PHP version 5.6 for builds. Jigsaw uses Laravel components that requires at least PHP 7.1.3. We can tell Netlify which PHP version we want with the environment variable PHP_VERSION. This variable can be set in the settings for a site or using the configuration file netlify.toml. For me it is always a good idea to have "infrastructure" configuration in the same place as the code, which means I go with the toml-file:

[build.environment]
  PHP_VERSION = "7.2"

Custom domain name

Time to point hmazter.com at the netlify-site.

This took some fiddling around. I use AWS Route53 for DNS and ended up with this:

Name Type Value
hmazter.com. A 104.198.14.52
www.hmazter.com. CNAME hmazter.netlify.com

When this was set and propagated trough DNS and enabled and forced HTTPS on the site with the one-click in Netlify.

Now this blog is served as static files, build with Jigsaw and hostad at Netlify.


2016-12-22

Running a Laravel powered Messenger bot on Elastic Beanstalk

Intro

I read Mark Zuckerbergs blog post about his home AI Jarvis, and was inspired. Not to build my own home AI, that would have been a too large project for me right now, but to check out Facebook Messenger bot.

I headed to the Quick Start section of Messenger Platform and started following that.
Set up a new Facebook Page, easy enough, just select a fitting category for my test. Then create a new Facebook app and set up.
Here was my first obstacle, the webhook for the FB app needs to point to a public HTTPS URL. Let's figure out a way to easy fix that.

AWS Elastic Beanstalk

Recently I have been testing and using more and more of the AWS tools and services. Been using simple EC2 instances and S3 for years, but this felt like a good opportunity to test out Elastic Beanstalk (EBS). I knew I wanted to use PHP and Laravel for this project, which I like most right now and am most efficient with. I had seen that the AMI used for EBS was updated to support PHP 7.0 now, which wasn't the case last time I was thinking about testing EBS.

So I created a new Laravel project. Googled and read up on how to use the eb cli tool to deploy. This was my findings after some testing:

  • eb init - to create a new application
  • eb create - to create a new environment for the app
  • Use git to commit the code locally
  • eb deploy - to deploy the most recent commit to the EBS

It took some tweaking and testing with the configuration files for EBS to get the document root to for the web server to point to: /public. Ended up with this file (phpsettings.config) in the .ebextensions:

option_settings:
  aws:elasticbeanstalk:container:php:phpini:
    document_root: /public

After that I hade a working Laravel install on the machine. I saw a link in the sidebar to use a custom domain with the EBS, and since I bought a domain a couple of weeks ago in Route 53 for these sort of things I used that one by adding a CNAME for the subdomain I chose pointing to the full elaticbeanstalk.com URL. Now I had the app running on my custom domain, but the requirement for the webhook was HTTPS.

HTTPS support

To get https, my first thought was Let's encrypt which I have used before. After some quick Googling I saw something that reminded me of that AWS has some certificate service, Amazon Certificate Manager (ACM). After looking that up I set out to create a new wildcard certificate for my domain.

Problem though, to verify the domain an email is sent to a couple of addresses on that domain (admin, hostmaster, etc) and I don't have mail running on that domain. My first thought, AWS must have a solution for that. And right so, Simple Email Service (SES) can be set up to receive emails on a domain, verify the domain by adding some TXT records and then add the MX record for SES. Then set up a rule to store all incoming emails to a S3 bucket.

Re-sent the verification email for the domain SSL certificate, and I got some files in the S3 bucket, opened one, took the verify URL and followed that. I got a message, verification successful. It took a couple of minutes before the certification manager showed the domain as verified.

Since I set up EBS with a single instance with a load balancer (ELB) (may have not been necessary, but that was the default setting in eb init) I needed to add a HTTPS listener to the load balancer. Listening on port 443 externally and passing it to port 80 on the EC2 instance and configure it with my new certificate. I did not get it to work directly, that was because my security group for the load balancer did not allow HTTPS traffic. After an update of the security group I had the app running on the EBS with the load balancer handling both HTTP and HTTPS traffic.

Start coding the bot app

As I continued to follow the Quick Start guide for Messenger Platform I saw the nodejs example. I know enough js to fully understand the example and translate the functionality to PHP and fit in the Laravel architecture.
The first part was the actual verification of the webhook. A GET request via HTTPS to a chosen URL, in the example they used /webhook and that worked for me. Passed some query parameters, verify against my secret token and then respond with another of the query parameters. In the example they where written as hub.verify_token and hub.challange and I took that as some form of array in the parameters, but after some testing I understood that it was just a string that included a period in the name. Laravel replaces dots in query parameters with underscore, to not conflict with the dot-notation for arrays.

//WebhookController.php

// route: GET /webhook
public function webhookVerify(Request $request)
{
    $mode = $request->query('hub_mode');
    $verifyToken = $request->query('hub_verify_token');

    if ($mode === 'subscribe' && $verifyToken === config('services.messenger.verify_token')) {
        Log::info('Validating webhook');
        return (string)$request->query('hub_challenge');
    }

    Log::error('Failed validation. Make sure the validation tokens match.', [
        'mode' => $mode,
        'verify_token' => $verifyToken,
    ]);
    return abort(403);
}

I deployed the updated code to EBS with git commit and eb deploy. Then I entered the URL in the FB webhook form, and the verification was successful, and I subscribed the webhook to my FB page.

Next part was to handle POST requests to the webhook URL with messages. Before I started that, I went to the FB page a wrote a message in the chat. I saw in the access log of the web server on the EBS that I got a POST request to the /webhook URL.

I updated my code to just log all data sent with the request and then respond with a 200 status, which is a requirement of the messenger platform.
Then followed the example code to handle incoming text messages and translating that to PHP.
Right now my code only echos back the message or responds with some different messages for some key words.

// WbhookController.php

// route: POST /webhook
public function incoming(Request $request)
{
    // Make sure this is a page subscription
    if ($request->input('object') === 'page') {
        $entries = $request->input('entry');
        // Iterate over each entry - there may be multiple if batched
        foreach ($entries as $entry) {
            // Iterate over each messaging event
            foreach ($entry['messaging'] as $event) {
                if ($event['message']) {
                    $this->receivedMessage($event);
                } else {
                    Log::info('Webhook received unknown event: ', $event);
                }
            }
        }

        return response('', 200);
    }
}

private function receivedMessage(array $event)
{
    $message = $event['message'];
    $messageText = $message['text'] ?? false;
    if ($messageText) {
        // just echo the text we received.
        $this->sendTextMessage($senderID, $messageText);
    }
}

private function sendTextMessage($recipientId, $messageText)
{
    $messageData = [
        'recipient' => [
            'id' => $recipientId,
        ],
        'message' => [
            'text' => $messageText,
        ]
    ];

    $this->callSendAPI($messageData);
}

private function callSendAPI($messageData)
{
    $client = new Client();
    $client->post('https://graph.facebook.com/v2.6/me/messages', [
        'query' => [
            'access_token' => config('services.messenger.page_access_token')
        ],
        'json' => $messageData
    ]);
}

Closing

Still a long way to go to actually doing something useful with the bot, but it's a good proof of concept and a good opportunity to try out some new technologies and services.

Some things that could be improved:

  • Code cleanup and refactor when I know what the bot is going to do.
  • Figure out how to pass environment variables to the EBS (for PHP) without commit them either as the .env file or with the .ebextensions .config-files.
  • For a dev/PoC environment the load balancer with configured autoscaling was probably unnecessary.
  • Maybe use some of the pre-made bots/packages as a starting point.