This application is a test assignment implementation, done by Dainis Brjuhoveckis on September 2020.
It's basically a variation of the famous classic fizzbuzz, this time implemented using PHP 7, MySQL 8, Apache 2, docker-compose.
- Mysql 8+
- Create database
foo
in your local machine - Create a table
bar
with three integer columns (a,b,c) infoo
- Test task should be completed in PHP 7.4
- Fill the table
bar
with 1 million rows where:- column
a
contains the numbers from 1 to 1e6 - column
b
contains a % 3 - column
c
contains a % 5
- column
- Create an endpoint
/databases/foo/tables/bar/csv
which upon aGET
request responds with the contents of the corresponding table serialized as CSV and using HTTP chunked encoding. - Create an endpoint
/databases/foo/tables/bar/json
which upon aGET
request with parameterspage
andpage_size
responds with paginated contents of the corresponding table serialized as JSON. The endpoint must return a JSON array containing objects, which represent the table rows. - Write tests.
- Dockerized environment (docker-compose)
To build and run the app for the first time, run the following command from the project directory:
docker-compose build && docker-compose run composer install && docker-compose up -d
Next time app can be started with the usual docker-compose up:
docker-compose up -d
This fills table foo
.bar
with data:
docker-compose run php -e src/databases/foo/tables/bar/create.php
(Could take a minute or two).
docker-compose run phpunit --testdox
Typical output:
PHPUnit 9.3.10 by Sebastian Bergmann and contributors.
Fizz Buzz Lib
✔ Fizz buzz abc 1
✔ Fizz buzz abc 15
✔ Fizz buzz abc 1 to 7 with data set #0
✔ Fizz buzz abc 1 to 7 with data set #1
✔ Fizz buzz abc 1 to 7 with data set #2
✔ Fizz buzz abc 1 to 7 with data set #3
✔ Fizz buzz abc 1 to 7 with data set #4
✔ Fizz buzz abc 1 to 7 with data set #5
✔ Fizz buzz abc 1 to 7 with data set #6
Fizz Buzz Integration Db
✔ Table row count 1000000
✔ Table row a 1
✔ Table row a 1000000
✔ Table row a from 1 to 7
✔ Table rows a from 123456 to 123458
✔ Table rows a from 999998 to 1000000
Fizz Buzz Integration Web Api
✔ Json
✔ Json page 2 page size 3
✔ Csv headers
✔ Csv file contents
Time: 00:03.461, Memory: 86.39 MB
OK (19 tests, 34 assertions)
docker-compose down --rmi all && sudo rm -rf var vendor .phpunit.result.cache
The design motto for this application: “as simple as possible, but not simpler”, KISS and YAGNI.
No attempt was made to create another framework with configuration, routing, logging, dependency injection, etc. For this there are really awesome frameworks: Laravel, Symfony, Yii, Slim just to name a few. PHP language features like namespaces, classes, ... were not used this time, except in the unit tests.
App web service endpoint URL such as http://localhost:20080/databases/foo/tables/bar/json
suggests that foo
and bar
could actually be variable parameters (/databases/:database/tables/:table/json
), so the app could be used to view contents of any table in any database (schema) as long as MySQL user has the select permission. This parametrization could be implemented in numerous ways, for example by using mod_rewrite
(Apache web server module), but usually when there's request for such functionality, it's time to use a framework like Laravel or Symfony, where such URL params functionality is provided out of the box and easily implemented by app's code.
As of now, filling table foo.bar
with 1 000 000 records takes about 1 minute 20 seconds on author's machine.
There are several “tricks” to (maybe) make this run faster, these come to author's mind:
- Insert several rows at once
insert into bar (a, b, c) values (...)(...)(...)(...);
. - Insert just about 15 rows into table and then do
insert into bar (a, b, c) select (a+15, b, c) from bar where ...
until the necessary count is reached. - Create a file and then use MySQL's
LOAD DATA LOCAL INFILE ...
.
But that would make the application's logic more complex so the author avoided that.
GET http://localhost:20080/databases/foo/tables/bar/csv
Returns a file that contains all the records from database table foo.bar
.
Example:
a,b,c
1,1,1
2,2,2
3,0,3
4,1,4
<skipped>
999999,0,4
1000000,1,0
GET http://localhost:20080/databases/foo/tables/bar/json or GET http://localhost:20080/databases/foo/tables/bar/json?page=:page&page_size=:page_size (page and page_size can be any positive integer).
Examples: http://localhost:20080/databases/foo/tables/bar/json
[{"a":"3","b":"0","c":"3"},{"a":"4","b":"1","c":"4"}]
http://localhost:20080/databases/foo/tables/bar/json?page=3&page_size=5
[{"a":"11","b":"2","c":"1"},{"a":"12","b":"0","c":"2"},{"a":"13","b":"1","c":"3"},{"a":"14","b":"2","c":"4"},{"a":"15","b":"0","c":"0"}]
GET http://localhost:20080/databases/foo/tables/bar/destroy
Drops the table foo.bar
.
To re-create the data deleted by destroy, the create endpoint can be used (see next section).
GET http://localhost:20080/databases/foo/tables/bar/create
Creates table foo.bar
and fills it with data.
Logic:
- If there is table
foo.bar
, then does nothing. - If there is no table
foo.bar
, then creates the tablefoo.bar
and fills it with one million records.
Note that it runs for about 1 minute 20 seconds.
This app contains also image of Adminer, a tool for working with databases, such as selecting data from tables, creating tables, making exports and imports. It is available at http://localhost:28080/?server=mysql&username=foo&db=foo (Password is "foo").
When the application is running, clicking on select bar in Adminer would show the rows in table foo.bar
.
The php-apache image has XDebug configured, so it connects to port 9000 whenever PHP script is executed. That allows for debugging in Visual Studio Code, PHPStorm and so on.
On Linux Docker does not have special hostname entry host.docker.internal, so this is emulated by env variable:
export DOCKER_XDEBUG_REMOTE_HOST=$(ip addr show | grep "\binet\b.*\bdocker0\b" | awk '{print $2}' | cut -d '/' -f 1)
The above command could be added to ~/.bashrc to spare the need for running this every time when a fresh terminal window is opened.
As far as the author of this document knows, this is not required on Windows and Mac hosts, but that is not 100% certain as it is not tested by the author.
Example XDebug configuration for Visual Studio Code editor:
{
"version": "0.2.0",
"configurations": [
{
"name": "Listen for XDebug",
"type": "php",
"request": "launch",
"stopOnEntry": false,
"port": 9000,
"pathMappings": {
"/var/www/html": "${workspaceFolder}/src"
},
"xdebugSettings": {
"max_data": 65535,
"show_hidden": 1,
"max_children": 100,
"max_depth": 5
}
}
]
}
or
...
"pathMappings": {
"/var/www/html": "${cwd}/src"
}
...