I created a weather forecasting Web API with Laravel 8.83.11. The source code is available at the following github repository.
https://github.com/fukagai-takuya/weather-forecast
# The weather forecast data is obtained from the external site via Web API and stored in a database. This is a simple Web API program using Laravel.
# An overview of the program and how to check its operation is described here.
# This blog page explains the source code and the commands that were used when the program was created. This is a continuation from the previous page.
# All dates and times are in UTC.
1. Database
Laravel currently supports the following databases.
- MariaDB 10.2+
- MySQL 5.7+
- PostgreSQL 10.0+
- SQLite 3.8.8+
- SQL Server 2017+
SQLite was used in this program.
1.1. Preparation of the SQLite environment
Install SQLite and php drivers for SQLite connections with the following command.
$ sudo apt install sqlite3 $ sudo apt-get install php7.4-sqlite3
I prepared an empty database file with the following command.
$ touch database/database.sqlite
The database can be accessed with the following command.
$ sqlite3 database/database.sqlite
Next, I modified the settings related to DB in the .env file as follows.
DB_CONNECTION=sqlite #DB_HOST=127.0.0.1 #DB_PORT=3306 #DB_DATABASE=laravel #DB_USERNAME=root #DB_PASSWORD=
1.2. Database: Migrations
Create a database table “weather_data” to store weather forecast data.
1.2.1. Prepare a class that creates the database table “weather_data” with the following command.
php artisan make:migration weather_data
In my case, the following file database/migrations/2022_05_06_013400_weather_data.php was output.
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class WeatherData extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
//
}
...
}
Modify the above file as follows.
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class WeatherData extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('weather_data', function (Blueprint $table) {
$table->id()->autoIncrement();
$table->integer('dt')->unique();
$table->string('dt_txt', 19)->unique();
$table->string('new_york_main', 255)->nullable();
$table->string('new_york_description', 255)->nullable();
$table->string('london_main', 255)->nullable();
$table->string('london_description', 255)->nullable();
$table->string('paris_main', 255)->nullable();
$table->string('paris_description', 255)->nullable();
$table->string('berlin_main', 255)->nullable();
$table->string('berlin_description', 255)->nullable();
$table->string('tokyo_main', 255)->nullable();
$table->string('tokyo_description', 255)->nullable();
$table->timestamp('updated_at')->useCurrent();
$table->timestamp('created_at')->useCurrent();
});
DB::unprepared('CREATE TRIGGER weather_data_updated_at AFTER UPDATE ON weather_data
BEGIN
UPDATE weather_data SET updated_at = CURRENT_TIMESTAMP WHERE rowid == NEW.rowid;
END;');
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
//
}
}
The weather_data table to be created have column names, “id, dt, dt_txt, new_york_main, …, tokyo_description, updated_at, created_at”.
The table columns are qualified by column modifiers as described here.
- id is an ID number that is assigned sequentially one by one when records are registered. The autoIncrement() method is used to assign an integer that is incremented by 1.
- dt is an integer column that stores the Unix timestamps of the weather forecast data. To ensure that there is only one record with the same timestamp, the unique() method is used to specify that the data is unique.
- dt_txt is a column to store the date and time corresponding to dt as a text string of 19 one-byte characters, such as “2022-05-13 10:25:49”. Since there is only one record with the same date and time, this data is also specified to be unique.
- new_york_main, …, tokyo_description store English words that describe the weather, such as Rain, moderate rain, etc. The maximum length of the string is 255 characters.
- updated_at stores the date and time of update. created_at stores the date and time of data registration. The datetime is specified as their data type. The initial value is the time when the data was registered (inserted), that is spcefied by useCurrent() method.
- I tried to use useCurrentOnUpdate() method for updated_at so that when data is updated, it is updated with the date and time of the update. It, however, did not seem to work with SQLite. I added the following code to support this.
DB::unprepared('CREATE TRIGGER weather_data_updated_at AFTER UPDATE ON weather_data
BEGIN
UPDATE weather_data SET updated_at = CURRENT_TIMESTAMP WHERE rowid == NEW.rowid;
END;');
Execute the following command to create a database table, weather_data.
$ php artisan migrate
The above command will create the weather_data table on the database.
1.2.2. The structure of the created database table, weather_data can be checked with the following command on SQLite.
$ sqlite3 database/database.sqlite SQLite version 3.31.1 2020-01-27 19:55:54 Enter ".help" for usage hints. sqlite> .schema weather_data
The output is as follows.
CREATE TABLE IF NOT EXISTS "weather_data" ("id" integer not null primary key autoincrement, "dt" integer not null, "dt_txt" varchar not null, "new_york_main" varchar, "new_york_description" varchar, "london_main" varchar, "london_description" varchar, "paris_main" varchar, "paris_description" varchar, "berlin_main" varchar, "berlin_description" varchar, "tokyo_main" varchar, "tokyo_description" varchar, "updated_at" datetime default CURRENT_TIMESTAMP not null, "created_at" datetime default CURRENT_TIMESTAMP not null);
CREATE UNIQUE INDEX "weather_data_dt_unique" on "weather_data" ("dt");
CREATE UNIQUE INDEX "weather_data_dt_txt_unique" on "weather_data" ("dt_txt");
CREATE TRIGGER weather_data_updated_at AFTER UPDATE ON weather_data
BEGIN
UPDATE weather_data SET updated_at = CURRENT_TIMESTAMP WHERE rowid == NEW.rowid;
END;
2. Queues
2.1. Create a program to queue and execute jobs.
# Queued jobs are executed by launching Supervisor as described at section 5.3. on this page.
Create a Job class with the following command as described here.
$ php artisan make:job WeatherForecastInquiryJob
The following file app/Jobs/WeatherForecastInquiryJob.php will be generated.
<?php
namespace App\Jobs;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldBeUnique;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
class WeatherForecastInquiryJob implements ShouldQueue
{
use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;
/**
* Create a new job instance.
*
* @return void
*/
public function __construct()
{
//
}
/**
* Execute the job.
*
* @return void
*/
public function handle()
{
//
}
}
The following code that issues a WeatherForecastInquiryEvent has been added to the handle() method of the above code.
When WeatherForecastInquiryJob is retrieved from the queue and processed, WeatherForecastInquiryEvent is issued to retrieve weather forecast data from the external site.
...
use App\Events\WeatherForecastInquiryEvent;
class WeatherForecastInquiryJob implements ShouldQueue
{
...
public function handle()
{
WeatherForecastInquiryEvent::dispatch();
}
}
2.2. Prepare the database table, jobs that is used as a queue.
I followed the procedure described here.
Prepare a database table to store the queue with the following command.
$ php artisan queue:table
A file database/migrations/2022_05_05_074622_create_jobs_table.php with the following contents was generated.
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateJobsTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('jobs', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('queue')->index();
$table->longText('payload');
$table->unsignedTinyInteger('attempts');
$table->unsignedInteger('reserved_at')->nullable();
$table->unsignedInteger('available_at');
$table->unsignedInteger('created_at');
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('jobs');
}
}
The following command creates a database table, jobs to store the queue.
$ php artisan migrate
Specify database to QUEUE_CONNECTION in the .env file as follows.
QUEUE_CONNECTION=database
If QUEUE_CONNECTION is sync as shown below, the Job will be executed immediately without being stored in the queue.
QUEUE_CONNECTION=sync
In order to launch WeatherForecastInquiryJob at 6 hour intervals, app/Console/Kernel.php was modified as follows.
<?php
namespace App\Console;
use App\Jobs\WeatherForecastInquiryJob;
use Illuminate\Console\Scheduling\Schedule;
use Illuminate\Foundation\Console\Kernel as ConsoleKernel;
class Kernel extends ConsoleKernel
{
/**
* Define the application's command schedule.
*
* @param \Illuminate\Console\Scheduling\Schedule $schedule
* @return void
*/
protected function schedule(Schedule $schedule)
{
$schedule->job(new WeatherForecastInquiryJob)->everySixHours();
}
...
}
To actually run the Task Scheduler at the specified time interval, configure the crontab to run the “php artisan schedule:run” command at one-minute intervals, as described here. If only one crontab setting is registered as shown below, it is possible to execute multiple tasks at multiple time intervals by modifying the source code described in app/Console/Kernel.php.
* * * * * cd /path-to-weather-forecast-project && php artisan schedule:run >> /dev/null 2>&1
4. Registering a Job to the queue and test its execution.
4.1. To test the registration of a Job to the queue and its execution, modify the schedule method in app/Console/Kernel.php as follows.
protected function schedule(Schedule $schedule)
{
// $schedule->job(new WeatherForecastInquiryJob)->everySixHours();
$schedule->job(new WeatherForecastInquiryJob)->everyMinute();
}
4.2. Test Job registration to the queue
Execute the following command to check the contents of the jobs table. At first, nothing is registered, so the output is as follows.
$ sqlite3 database/database.sqlite SQLite version 3.31.1 2020-01-27 19:55:54 Enter ".help" for usage hints. sqlite> select * from jobs; sqlite>
Check the contents of the jobs table after running “php artisan schedule:run” three times as shown below.
$ php artisan schedule:run [2022-05-22T08:49:44+00:00] Running scheduled command: App\Jobs\WeatherForecastInquiryJob $ php artisan schedule:run [2022-05-22T08:49:45+00:00] Running scheduled command: App\Jobs\WeatherForecastInquiryJob $ php artisan schedule:run [2022-05-22T08:49:46+00:00] Running scheduled command: App\Jobs\WeatherForecastInquiryJob
The following command will check the contents of the jobs table and show the three registered jobs.
$ sqlite3 database/database.sqlite
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> select * from jobs;
18|default|{"uuid":"978dbb48-9095-46ec-9865-05e38416197b","displayName":"App\\Jobs\\WeatherForecastInquiryJob","job":"Illuminate\\Queue\\CallQueuedHandler@call","maxTries":null,"maxExceptions":null,"failOnTimeout":false,"backoff":null,"timeout":null,"retryUntil":null,"data":{"commandName":"App\\Jobs\\WeatherForecastInquiryJob","command":"O:34:\"App\\Jobs\\WeatherForecastInquiryJob\":10:{s:3:\"job\";N;s:10:\"connection\";N;s:5:\"queue\";N;s:15:\"chainConnection\";N;s:10:\"chainQueue\";N;s:19:\"chainCatchCallbacks\";N;s:5:\"delay\";N;s:11:\"afterCommit\";N;s:10:\"middleware\";a:0:{}s:7:\"chained\";a:0:{}}"}}|0||1653209384|1653209384
19|default|{"uuid":"17851f0d-9ba3-4d3a-8dca-1045364aff16","displayName":"App\\Jobs\\WeatherForecastInquiryJob","job":"Illuminate\\Queue\\CallQueuedHandler@call","maxTries":null,"maxExceptions":null,"failOnTimeout":false,"backoff":null,"timeout":null,"retryUntil":null,"data":{"commandName":"App\\Jobs\\WeatherForecastInquiryJob","command":"O:34:\"App\\Jobs\\WeatherForecastInquiryJob\":10:{s:3:\"job\";N;s:10:\"connection\";N;s:5:\"queue\";N;s:15:\"chainConnection\";N;s:10:\"chainQueue\";N;s:19:\"chainCatchCallbacks\";N;s:5:\"delay\";N;s:11:\"afterCommit\";N;s:10:\"middleware\";a:0:{}s:7:\"chained\";a:0:{}}"}}|0||1653209385|1653209385
20|default|{"uuid":"85d7c4fc-e5e1-4007-ad20-1be01071a7da","displayName":"App\\Jobs\\WeatherForecastInquiryJob","job":"Illuminate\\Queue\\CallQueuedHandler@call","maxTries":null,"maxExceptions":null,"failOnTimeout":false,"backoff":null,"timeout":null,"retryUntil":null,"data":{"commandName":"App\\Jobs\\WeatherForecastInquiryJob","command":"O:34:\"App\\Jobs\\WeatherForecastInquiryJob\":10:{s:3:\"job\";N;s:10:\"connection\";N;s:5:\"queue\";N;s:15:\"chainConnection\";N;s:10:\"chainQueue\";N;s:19:\"chainCatchCallbacks\";N;s:5:\"delay\";N;s:11:\"afterCommit\";N;s:10:\"middleware\";a:0:{}s:7:\"chained\";a:0:{}}"}}|0||1653209386|1653209386
The following command displays the data registered in the weather_data table. The last registered data was last modified at “2022-05-22 08:48:54”.
sqlite> select id, updated_at from weather_data; ... 680|2022-05-22 08:48:54
Execute the “php artisan queue:work” command to run the Jobs registered in the jobs table. The processes to retrieve and execute Jobs from the queue are executed three times. Each Job retrieves weather forecast data from the external site and updates the weather forecast data.
$ php artisan queue:work [2022-05-22 09:02:50][18] Processing: App\Jobs\WeatherForecastInquiryJob [2022-05-22 09:02:51][18] Processed: App\Jobs\WeatherForecastInquiryJob [2022-05-22 09:02:51][19] Processing: App\Jobs\WeatherForecastInquiryJob [2022-05-22 09:02:52][19] Processed: App\Jobs\WeatherForecastInquiryJob [2022-05-22 09:02:52][20] Processing: App\Jobs\WeatherForecastInquiryJob [2022-05-22 09:02:53][20] Processed: App\Jobs\WeatherForecastInquiryJob
When I displayed the contents of the jobs table with the following command, nothing was displayed. This is because all the Jobs that have been registered in the queue have been removed from the queue.
Next, I checked the last update time of the weather forecast data with the last registered id of 680 in weather_data. It was updated to “2022-05-22 09:02:53”, the completed time of the last Job that was retrieved from the queue.
$ sqlite3 database/database.sqlite SQLite version 3.31.1 2020-01-27 19:55:54 Enter ".help" for usage hints. sqlite> select * from jobs; sqlite> select id, updated_at from weather_data where id = '680'; 680|2022-05-22 09:02:53
4.3. Execute Job synchronously
As noted in Note2, if sync is specified at QUEUE_CONNECTION of the .env file, the Job will be executed immediately without being stored in a queue.
QUEUE_CONNECTION=sync
Execute “php artisan schedule:run” command.
$ php artisan schedule:run [2022-05-22T09:21:17+00:00] Running scheduled command: App\Jobs\WeatherForecastInquiryJob
Check the database with the following command. Unlike the previous step, the jobs table is empty even before the “php artisan queue:work” command is executed.
Next, I checked the last update time of the weather forecast data. It was the same as the time “php artisan schedule:run” command was executed, “2022-05-22 09:21:17”.
$ sqlite3 database/database.sqlite SQLite version 3.31.1 2020-01-27 19:55:54 Enter ".help" for usage hints. sqlite> select * from jobs; sqlite> select id, updated_at from weather_data where id = '680'; 680|2022-05-22 09:21:17
5.1. Modify phpunit.xml as follows.
Only the modified part is described.
Before modification
<!-- <server name="DB_CONNECTION" value="sqlite"/> --> <!-- <server name="DB_DATABASE" value=":memory:"/> --> <server name="QUEUE_CONNECTION" value="sync"/>
After modification
<server name="DB_CONNECTION" value="sqlite"/> <server name="DB_DATABASE" value=":memory:"/> <server name="QUEUE_CONNECTION" value="database"/>
5.2. The following command creates a Feature test class.
$ php artisan make:test WeatherForecastInquiryTest
A file tests/Feature/WeatherForecastInquiryTest.php will be generated.
<?php
namespace Tests\Feature;
use Illuminate\Foundation\Testing\RefreshDatabase;
use Illuminate\Foundation\Testing\WithFaker;
use Tests\TestCase;
class WeatherForecastInquiryTest extends TestCase
{
/**
* A basic feature test example.
*
* @return void
*/
public function test_example()
{
$response = $this->get('/');
$response->assertStatus(200);
}
}
tests/Feature/WeatherForecastInquiryTest.php has been modified as follows.
This is a simple example of a Feature test based on the Laravel documentation HTTP Tests and Console Tests.
<?php
namespace Tests\Feature;
use Illuminate\Foundation\Testing\RefreshDatabase;
use Illuminate\Foundation\Testing\WithFaker;
use Illuminate\Support\Facades\Schema;
use Tests\TestCase;
use DateTime;
use DateTimeZone;
class WeatherForecastInquiryTest extends TestCase
{
use RefreshDatabase;
/**
* A basic feature test example.
*
* @return void
*/
public function test_example()
{
$response = $this->get('/');
$response->assertStatus(200);
}
public function testGetWeatherForecast(): void
{
$response = $this->get('/api/get-weather-forecast');
$response->assertStatus(200);
}
public function testGetWeatherForecastEmptyInput(): void
{
$response = $this->getJson('/api/get-weather-forecast');
$response->assertStatus(200)
->assertJson([
'Result' => 'Failed',
'Error' => 'Format Error',
'Date' => ''
]);
}
public function testGetWeatherForecastFormatError(): void
{
$date = '1234567890';
$response = $this->getJson('/api/get-weather-forecast?date=' . $date);
$response->assertStatus(200)
->assertJson([
'Result' => 'Failed',
'Error' => 'Format Error',
'Date' => $date
]);
}
public function testGetWeatherForecastIncorrectDate(): void
{
$date = '2022-99-99 03:00:00';
$response = $this->getJson('/api/get-weather-forecast?date=' . $date);
$response->assertStatus(200)
->assertJson([
'Result' => 'Failed',
'Error' => 'Incorrect Date',
'Date' => $date
]);
}
public function testGetWeatherForecastDataWillNotBeFound(): void
{
$date = '9999-05-02 03:00:00';
$response = $this->getJson('/api/get-weather-forecast?date=' . $date);
$response->assertStatus(200)
->assertJson([
'Result' => 'Failed',
'Error' => 'No weather data was found for the specified date.',
'Date' => $date
]);
}
public function testGetWeatherForecastDataWillBeFound(): void
{
$now = new DateTime("now", new DateTimeZone('UTC'));
$tomorrow = $now->modify("+1 day");
$date = $tomorrow->format("Y-m-d H:i:s");
$response = $this->getJson('/api/get-weather-forecast?date=' . $date);
$response->assertStatus(200)
->assertJsonStructure([
'Result',
'id',
'dt',
'dt_txt',
'new_york_main',
'new_york_description',
'london_main',
'london_description',
'paris_main',
'paris_description',
'berlin_main',
'berlin_description',
'tokyo_main',
'tokyo_description',
'created_at',
'updated_at',
]);
}
public function testWeatherDataTableColumns()
{
$this->assertTrue(
Schema::hasColumns('weather_data', [
'id',
'dt',
'dt_txt',
'new_york_main',
'new_york_description',
'london_main',
'london_description',
'paris_main',
'paris_description',
'berlin_main',
'berlin_description',
'tokyo_main',
'tokyo_description',
'created_at',
'updated_at',
]), 1
);
}
public function testArtisanSheduleRunCommand()
{
$this->artisan('schedule:run')->assertExitCode(0);
}
public function testArtisanMigrateFreshCommand()
{
$this->artisan('migrate:fresh')->assertExitCode(0);
}
}
5.3. Execution of a test command
Enter the following command to run the Feature test.
$ php artisan test
If all tests pass, the following message is output to the console.
PASS Tests\Unit\ExampleTest ✓ example PASS Tests\Feature\ExampleTest ✓ example PASS Tests\Feature\WeatherForecastInquiryTest ✓ example ✓ get weather forecast ✓ get weather forecast empty input ✓ get weather forecast format error ✓ get weather forecast incorrect date ✓ get weather forecast data will not be found ✓ get weather forecast data will be found ✓ weather data table columns ✓ artisan shedule run command ✓ artisan migrate fresh command Tests: 12 passed Time: 2.96s