In this post, I want to introduce and teach you how to solve the problem with automation. As you already know that in my post related to Cambodia Geography, I manually export the data which is time-consuming and not a good appropriate method for maintenance. The solution which I proposed here as an automation tool to solve this problem is to create a task schedule running a job of exporting data. This job (or sometimes called cronjob) will run every week on Sunday to dump all the updated data from the table for downloading. This is just an example and also an idea for you to think that some tasks which you faced in your daily work can be able automated to gain some benefit such as time, $$, or correctness of the application. 

There are some real-world examples in which they use task scheduling such as membership expiration checking, daily account balance closing, and email marketing sending, etc. I really hope that based on this example you can have an idea and implement your own automation tool or process. 

Step 1: In the Laravel project, you create a custom command file

In function handle() is where you will write your code to execution your automation. In my case, I will execute the command exporting or dump of MySQL database. Before that, you have to define signature and description and this is for the usage of php artisan with the signature to run the task scheduling job.

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;

class MySqlDump extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'db:dump';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Runs the mysqldump utility using info from .env';

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $host = env('DB_HOST');
        $port = env('DB_PORT');
        $username = env('DB_USERNAME');
        $password = env('DB_PASSWORD');
        $database = env('DB_DATABASE');
        $tables = 'tb_province tb_district tb_commune tb_village';
        $file = public_path().'/sql/cambodia_geography_sql.sql';

        $command = sprintf('mysqldump -h %s -P %s -u %s -p\'%s\' %s > %s', $host, $port, $username, $password, $database . ' ' . $tables, $file);

        exec($command);

        $filejson = public_path().'/json/cambodia_geography_json.json';

        $commandjson = sprintf('mysqldump -h %s -P %s -u %s -p\'%s\' %s | sqldump-to > %s', $host, $port, $username, $password, $database . ' ' . $tables, $filejson);

        exec($commandjson);
    }
}

As you can see in the code above we use the command mysqldump (which is built-in from MySQL) to dump data with sql format, but for JSON format we need to install additional add-on software called "sqldump-to" and below are the steps to setup

sudo apt install npm

npm install -g sqldump-to

Step 2: Adding the created custom command into array commands of kernel.php file

<?php

namespace App\Console;

use Illuminate\Console\Scheduling\Schedule;
use Illuminate\Foundation\Console\Kernel as ConsoleKernel;

class Kernel extends ConsoleKernel
{
    /**
     * The Artisan commands provided by your application.
     *
     * @var array
     */
    protected $commands = [
        //
        // add the MySqlDump command here
        \App\Console\Commands\MySqlDump::class,
    ];

    /**
     * Define the application's command schedule.
     *
     * @param  \Illuminate\Console\Scheduling\Schedule  $schedule
     * @return void
     */
    protected function schedule(Schedule $schedule)
    {
        // $schedule->command('inspire')->hourly();
    }

    /**
     * Register the commands for the application.
     *
     * @return void
     */
    protected function commands()
    {
        $this->load(__DIR__.'/Commands');

        require base_path('routes/console.php');
    }
}

You can try to run "php artisan your_signature" => in my case "php artisan db:dump" in your terminal to run the job. When deploying to production you have to follow step 3 below to setup your corn service on the server.

Step 3: Create a task scheduling  (Cronjob) every Sunday of the week

sudo crontab -e

0 0 * * 0 cd /var/www/html/geo-cambodia/src && sudo php artisan db:dump >> /dev/null 2>&1

THANK YOU !!!

Find Us @
Facebook
Telegram
Twitter
LinkedIn