data codes through eyeglasses

Log Laravel Eloquent SQL queries with parameter values

kyriakos

Frequently I find the need to see what SQL queries are actually generated magically by Eloquent. It really helps when I need to debug a query or figure out why its not performing well. Since I keep referring back to this very useful code snippet I thought its good to keep it here so its readily available for myself and anyone else. Add the following code in the boot() method of AppServiceProvider.php:

    public function boot() {
        if (\App::environment('local')) {
            DB::listen(
                static fn($query) => logger(
                    Str::replaceArray('?', array_map(fn($binding) => '\'' . $binding . '\'', $query->bindings),
                                      $query->sql
                    )
                )
            );
        }
    }

If you haven’t changed Laravel’s default logging configuration all queries executed will be logged in storage/logs/laravel.log. The full query including its parameter values will be included which makes it dead easy to then copy/paste and run EXPLAIN so you can see how you could optimize it. Note that this will only execute if APP_ENV is set to local so no sensitive information will be logged on your production logs.

Remove duplicate rows from table in PostgreSQL

kyriakos

I was recently looking for a quick way to delete duplicate rows from large table (around half a billion rows) in PostgreSQL. In my case the table didn’t have a primary key – it was the result of importing a bunch of CSV files using the copy command. After some googling it turned out that the quickest way is also the easiest to understand. So here is how its done:

-- Create a new table with only unique rows:
create table temp as
    select distinct on (lon,lat,label) * from buildings;

-- Check the before and after counts:
select count(*) as before from buildings;
select count(*) as after from temp;

-- Now drop the original table
drop table buildings;
-- Rename the temporary one.
alter table temp rename to buildings;

Essentially, a new table is created with the duplicated rows removed then the original table is dropped and the new table is renamed. Keep in mind that this method doesn’t specify which row is kept so be careful if that is important to you.

In this case I only needed to have unique values in columns lon, lat and label. You can replace those with what fits you case. If you want to do a full table compare (i.e. your imported CSV files have full duplicated rows) you can use * instead like this:

create table temp as
    select distinct * from buildings;

PHP 7.4 Preloading

kyriakos

A cool new feature coming up with PHP 7.4 will give a nice speed boost while solving one of the problems that hold PHP back. As explained in the internals RFC we’ll be able to specify a single PHP file in php.ini (under the opcache.preload directive) which will take care of loading all other PHP files that contain shared code and keeping them in memory before any application code is executed. The preloader PHP script can either use the include command the traditional way or the new function opcache_compile_file to force scripts to be compiled and kept in memory. It will also resolve any dependencies and load them accordingly which wasn’t possible before using the opcache. The shared code will then be available to any PHP scripts running on the same instance without having to do any include/require or pass through composer’s autoloader.

Performance gains from this feature will depend mainly on how much actual work is done during each request as opposed to bootstrapping the framework. Requests will short run-time should see the highest boost in performance.

While this feature will benefit anyone running their applications isolated it will probably not be available for shared hosting scenarios where the same PHP instance is used for multiple sites.

PHP 7.3 is out

kyriakos

Can be downloaded here http://php.net/releases/7_3_0.php

I personally would stick to 7.2 for now at least until the first point releases of 7.3 are out just to be on the safe side.

To install on Ubuntu 18.04:

sudo add-apt-repository ppa:ondrej/php
sudo apt-get update

deb http://ppa.launchpad.net/ondrej/php/ubuntu bionic main 
deb-src http://ppa.launchpad.net/ondrej/php/ubuntu bionic main

sudo apt-get install php7.3

Xdebug is also available but its currently under beta. Since you won’t be using that in production it doesn’t hurt to try.

Hiking in Amiantos, Cyprus [with photos]

kyriakos

On 19th March went on a hike to Amiantos area in Troodos. The trail started near the village of Kato Amiantos walked along the river reached the flooded entrance of the old asbestos mine and then climbed up the hill over the mine and then descended back to the river and followed the same path back. A total of around 12km of walking, and climbing up and down; this was probably the hardest hike I’ve done in my life and most certainly one of the hardest trails in Cyprus (I am trying to find a harder one just for the challenge).

Walking along the river side was easy; we had to cross the river a few times but it was not that hard, the challenge started when we reached the lake, at that point we took the wrong path and ended up having to climb up the hill which was quite steep. Once at the top we slowly started walking down when we realized we spent 5 hours in what was supposed to take us 3-4 hours. At that point we decided to take a different path so that we could make it off the mountain before sundown. The new path was quite hard, we were essentially climbing down the dry path rainwater created. Because the particular mountain peak is mostly made of sand, the water eroded a steep corridor around a metre wide and around 2 metres deep. We needed to be careful not to touch the walls cause the path would collapse and at the same time had to climb down drops of about 2-3m at a time. It took us another hour to get out of that and then walk along the river back to our starting point.

Was it fun? yes! will I do it again? definitely!

The river
Walking down the peak.
Trapped in the narrow corridor.
Trapped in the narrow corridor.
View from the top
View from the top
Above the mine
Above the mine
The lake at the peak.
The lake at the peak.
our crew
Our Crew! Had the camera shutter on a 2 second, barely made it in the picture.
The abandoned buildings viewed from the peak.
The abandoned buildings viewed from the peak.
Valentinos pointing where we should go.
Valentinos pointing where we should go.
We look happy cause we thought we went through the worst part, we were so wrong.
We look happy cause we thought we went through the worst part, we were so wrong.

Amazon Drive synced folder

Amazon Drive newest client supports folder syncing

kyriakos

Good news for data hoarders, Amazon Cloud Drive client popped up with an update today and to my surprise after installing what I assumed it was just a bug fix it included a highly requested feature – Folder Synchronization. A feature available in all other similar services such as Dropbox, Microsoft’s Onedrive and Google Drive. Even though Amazon Drive excels in one feature – offering unlimited space – until now it operated more as a backup service where you had to choose when, what and where to upload your files. Note that you could have sync functionality with Amazon Drive even before using third-party clients but it’s always nicer to have it built-in the official one. With the new version it automatically syncs any files you have in Amazon Drive’s online root folder and it allows you to pick any sub folders that are already backed up to be synced with your local Amazon Drive folder. In the meantime any new sub folders you create locally are automatically kept in sync.

Unfortunately syncing doesn’t have a placeholder functionality that OneDrive used to have and newer versions of DropBox implemented but at least they kept the option to back up files without having them synced.

Lefkara village photos

kyriakos

Lefkara is considered one of the most picturesque villages in Cyprus, located in the hills between Limassol and Larnaca it’s a must visit for anyone spending time in Cyprus. It’s a fairly large village with a lot of coffee shops and traditional taverns usually quite busy especially during weekends. Ideally try parking as soon as you find a spot cause streets are narrow and road traffic makes it driving even harder.

Took these photos back in September during an unplanned visit.

JPHP – Compile PHP directly to JVM (Java) Bytecode

kyriakos

I stumbled upon the JPHP project today which adds yet another direction to the PHP world. JPHP is an one man project by Dmitriy Zayceff who’s been working on it for the past 2 years and I must admit he must have spent a lot of time on it. Here’s some key points:

  • Compile PHP code into Java Virtual Machine bytecode (into JAVa’s .class files)
  • Use the standard PHP function library including some extensions.
  • Can use any JAVA class through PHP code (you need to write a wrapper for it but it works)
  • Create cross platform GUI or CLI applications
  • Develop Android apps in PHP
  • It executes faster than the current PHP branch and even faster than PHP7.
  • Allows for both stateful and stateless execution when used as a Web scripting language (i.e. you can either choose to execute as a long-running process with shared memory or reset with every request like standard PHP.
  • Can do just in time compilation of PHP files.

Project’s Github page.

Benchmarks can be found here. Page is in Japanese but all you need to look at is the two tables. The first shows the time taken for the 1st run of the benchmarks and the next run where the code is already pre-compiled and JPHP wins.

Chrome DevTools – Quickly use currently selected element in console

kyriakos

I am sure like me, there’s been many times when using Chrome Developer Tools you selected an DOM element in the Elements panel and wished you could easily get a reference for it so you can use it in the console. It turns out it was possible and super-easy all along by using the $0 magic variable in Console. $0 automatically holds a reference to the currently selected element in the Elements panel. As a bonus the using $_ instead you get access to the last evaluated expression whether its an object or a scalar. Here’s how it looks:

$0 and $_ in Chrome Developer Tools

$0 and $_ in Chrome Developer Tools

 

 

Source: DevTips Daily