DEV Community: Insolita

Postgres And Birthday Dates

Insolita — Sat, 09 Oct 2021 13:35:15 +0000

Processing user`s dates of birthday is a often task, but sometimes newbies has difficult with it. So, I decide to write a few snippets.

For ex. we have a table

CREATE TABLE users ( id serial primary key, name varchar(100) not null, birthday date );

1. Find users who have birthdays at this month

SELECT * FROM users WHERE date_part('month', birthday) = date_part('month', CURRENT_DATE)

2. Find users who have birthdays today

SELECT * FROM users WHERE date_part('day', birthday) = date_part('day', CURRENT_DATE) AND date_part('month', birthday) = date_part('month', CURRENT_DATE)

2.1 Find users who have birthdays tomorrow

SELECT * FROM users WHERE date_part('day', birthday) = date_part('day', CURRENT_DATE + INTERVAL '1day') AND date_part('month', birthday) = date_part('month', CURRENT_DATE + INTERVAL '1day')

3. Find users who have birthdays during some period, for. ex. in recent month

WITH lastDayOfMonth as ( SELECT (date_trunc('month', CURRENT_DATE) + interval '1 month' - interval '1 day')::date as ldm ) SELECT * FROM users, lastDayOfMonth WHERE ( date_part('month', birthday) = date_part('month', CURRENT_DATE) AND date_part('day', birthday) BETWEEN date_part('day', CURRENT_DATE) AND date_part('day', ldm) ) OR ( date_part('month', birthday) = date_part('month', CURRENT_DATE + interval '1 month') AND date_part('day', birthday) BETWEEN 1 AND date_part('day', CURRENT_DATE) );

But this method will be works only for month interval.
If we need birthdays in recent 10 days, or 45 days... for more flexible calculation will better to use a days number of year. But we need take care about valid handling between years - for ex. 20 days since 25 december - is an interval 25.12.2021 - 14.01.2022

WITH lastDayOfYear AS ( SELECT (date_trunc('year', CURRENT_DATE) + interval '1 year' - interval '1 day')::date AS ldy ) SELECT * FROM users, lastDayOfYear WHERE ( -- if year is same date_part('year', CURRENT_DATE) = date_part('year', CURRENT_DATE + interval '20days') AND date_part('doy', birthday) BETWEEN date_part('doy', CURRENT_DATE) AND date_part('doy', CURRENT_DATE + interval '20days') ) OR ( date_part('year', CURRENT_DATE) < date_part('year', CURRENT_DATE + interval '20days') AND ( date_part('doy', birthday) BETWEEN date_part('doy', CURRENT_DATE) AND date_part('doy', ldy) OR date_part('doy', birthday) < (20 - date_part('day', age(ldy, CURRENT_DATE))) ) );

4. Find users with age in some interval, for example, users with age between 30 and 40 years old.

It is easy to solve on backend side, when you can prepare sql

php <?php $from = date('Y-m-d', strtotime('-30years')); $to = date('Y-m-d', strtotime('-40years')); $sql = "SELECT * FROM users WHERE birthday BETWEEN '$from' AND '$to'";

But sometimes direct query may be more useful - you can do it with postgres function age that calculates an interval between dates

SQL SELECT * FROM users WHERE age(birthday) BETWEEN interval '30 years' AND interval '40 years';

You can play with these queries in Db-Fiddle

Unused Scanner 2.3 released

Insolita — Thu, 15 Apr 2021 16:42:12 +0000

Now distributes as phar archive also! You can download phar from the releases page
improved argument parsing
added option for show app version only
fixed silent output - it really silent now, only status code returned
removed nesbot/carbon dependency

Try it now at https://github.com/Insolita/unused-scanner

Linux tools for check disk usage and folders size

Insolita — Fri, 13 Nov 2020 00:53:00 +0000

1. Native ones

du - Summarize disk usage of the set of FILEs, recursively for directories.

Most popular commands:
du -sh /path - show total summary for a defined path

du -h -d 1 /path - show directory sizes with custom depth (-d option)

du -h -d 1 /path | sort -hr - same as previous, but with sort from largest to lowest

df - report file system disk space usage

Output result looks like:

2. Dust - Rust-written du improve.

Show result as a pretty tree with sort (Ascending by default)

Repository: https://github.com/bootandy/dust

Output can be limited by depth and reversed from largest to lowest

dust -r -d 1

Also, it has an interesting feature - showing by file count instead of files size

3. Ncdu - N-curses du version

Site: https://dev.yorhel.nl/ncdu

Install on Ubuntu: apt install ncdu

Install on ArchLinux: sudo pacman -S ncdu

It scans the defined directory, show disk usage, and bring the ability to navigate by child directories

4. Dutree - yet another du alternative, written with rust

Repository: https://github.com/nachoparker/dutree

Installation: cargo install dutree

5. Pydf - df clone written with python

Behavior is similar to native df, but with more pretty colorized output

Repository: https://github.com/garabik/pydf

Install on Ubuntu/Debian: apt install pydf

Install on ArchLinux: sudo pacman -S pydf

6. Duf - Go-written better df alternative

Repository: https://github.com/muesli/duf

Install on Ubuntu/Debian: apt install duf

Install on ArchLinux: yay -S duf

It is really fast, has a pretty look, and has a killer feature - the ability to output as JSON, that can be useful for monitoring

How to flip array keys and values in the code editor?

Insolita — Mon, 02 Nov 2020 04:04:43 +0000

Easy, with regex search!

Press Ctrl+R for show replace bar
Enable regex mode (button looks like .* near search field)
Use regex in search field: '(.+)'\s=>\s'(.+)',
Use expression for replace: '$2' => '$1',

You can see a tooltip with a value that will be after replace

It is possible even you have different quotes and spaces between arrow

Use regex
(?:'|")(.+)(?:'|")\s{0,}=>\s{0,}(?:'|")(.+)(?:'|"),

And the same replace expression '$2' => '$1',

Named capture groups also available:

Use regex
(?:'|")(?<src>.+)(?:'|")\s{0,}=>\s{0,}(?:'|")(?<tgt>.+)(?:'|"),

And the same replace expression '${tgt}' => '${src}',

Coloro - a console tool for show texts with highlighted colors

Insolita — Thu, 13 Aug 2020 21:17:38 +0000

Let me introduce "coloro" - a simple console tool for output text with highlighted hex colors. I wrote it when playing with my polybar and bspwm design.

Source available at https://github.com/Insolita/coloro

It can highlight text from arg

Highlight text from file

Highlight text from pipe

And even highlight text from clipboard

( -n option allow to parse colors without hashtag prefix )

Notes for migrating an app from Mysql to Postgres

Insolita — Sun, 26 Jul 2020 21:39:17 +0000

Actually, in most cases, we work with databases through abstraction layers of ORM/ActiveRecord libraries, which give us the ability to work with different databases in the same manner. That can make an illusion that we can easily be switching to another database system. Just run a new database system, change database driver in framework settings, and upload data dump to the new database. So, it can be the truth for some simple cases. But for larger projects, we forced to use raw database queries from time to time. It happens for different reasons - when we want to add better optimization or want to use database-specific features, or when we need a complex query. My article is about several differences between MySQL and PostgreSQL that we should keep in mind if we want to migrate from one to another. And if any kind of raw SQL queries are in our application, we should pay special attention to it.

Quotes

For MySQL, we can use single quotes '' as well as double quotes "" for strings and backticks for system data - table names, column names.

For PostgreSQL, we must use only single quotes for strings and double quotes for table, column names, etc...

Comments

MySQL supports ANSI standard comment prefix '--' double-hyphen and also hashtags. PostgreSQL understands only double-hyphen prefix.

Case Sensitiveness

MySQL under Linux os is case sensitive for column, index, stored routine, and event names. And it is not sensitive for database and table names.
Imagine, that we have table "my_table" with columns "name" and "myText"

select name, myText from my_table; -- ok
select Name, mytext from my_table; -- ok
select name, myText from My_Table; -- wrong

Postgres is case-sensitive for quoted system names and insensitive for unquoted (But all unquoted names casts to lowercase)

select name, myText from my_table; -- wrong
select name, myText from "My_Table"; -- wrong

select name, "myText" from my_table; -- ok
select NAME, "myText" from my_table; -- ok
select name, "myText" from My_Table; -- ok

Operators

MySQL use AND,OR as well as alises &&, || - for logical operations.
Postgres use only AND, OR. The operator || used for string concatenations.

MySQL use ^ for byte XOR operation.
Postgres use ^ for exponentiation and # for byte XOR.

Postgres haven't SOUNDS LIKE operator. (But you can use fuzzystrmatch extension, that can soundex and even more)

MySQL LIKE operator is case-insensitive. Postgres LIKE operator case-sensitive. Use ILIKE operator for an ignore-case search.

Strictness

MySQL got real strictness mode settings only since 5.7 version. So if your MySQL version less than 5.7, or you use it without strict-mode - you must pay attention for sure that your application well-validate data size/length accordingly column limits. Because Postgres is strict for this.
Also, one of the often mistakes that come from MySQL non-strictness is Grouping queries without explicit columns aggregating.

I assume that we have a table users

create table users(
 id bigserial primary key,
 name varchar(255) not null,
 age smallint default 0 null,
 department varchar(20) not null
);

MySql in non-strict mode allows to make queries like

select *, count(id) from users group by department;
select name, age, count(id), department from users group by department;

Postgres requires explicit aggregation for columns that are not a part of GROUP BY. So valid query should be looks like:

select avg(age), array_agg(name),count(id), department from users group by department;

Data Types

I make review only for several ones. You can find more details in the official documentation. MySQl PostgreSQL

The BOOL type. In the MySQL it just an alias for TINYINT(1) and can take only 0, false, 1, true values;
In the Postgres - bool is a separated type. It can be filled with values: true, false, 'true', 'false','yes', 'no', 't', 'f', 'y', 'n', '0', '1'. But it can't accept unquoted 0 and 1 values, which may become a problem for a data migration from MySQL. You should wrap 0 and 1 values in quotes, or add explicit type casting 0::bool, 1::bool.
There are no TINYINT type in Postgres. The minimal integer type is 2-byte SMALLINT with values in range from -32768 to +32767
Postgres haven't the UNSIGNED option. If you need to restrict data, you can add CHECK constraint.

CREATE TABLE users (
  id serial primary key, 
  name  varchar(255),
  age smallint CHECK(age > 0) 
);
-- OR
ALTER TABLE users ADD CHECK(age >0)

Postgres functions for date and time manipulations are really different. If you use some of them in MySQL, don't forget to replace it with analogs
Postgres haven't the feature ON UPDATE CURRENT_TIMESTAMP that allows automatically update timestamp value on every row update. You should write the trigger function for the same behavior, or make it on the application level.
JSON. There are no problems for move data from the MySQL json field to Postgres. But syntax for operations with json in queries absolutely different. Also, you should know that Postgres offers a more efficient JSONB type. (What a difference?)
Postgres haven't a SET column type. The nearest alternative is Array with additions constraints.
BYTEA - is a Postgres alternative for MySQL BINARY type. But it has known problems with data conversion. But solutions are known also.
ENUM. Postgres support it, but the way for it creation is different. You should define own TYPE as ENUM, and then declare it for a column.

CREATE TYPE job_state AS ENUM ('pending', 'active', 'fail', 'success');
CREATE TABLE jobs (
    id bigserial,
    name varchar(255),
    payload jsonb,
    state  job_state
);
INSERT INTO jobs (name, payload, state) 
VALUES ('do it', {"foo": "bar"}, 'pending')

Creating/Updating Table Schema

Unlike MySQL, Postgres can wrap in transactions not only data modifications but the table structure operations too. And it is useful for migrations.
Postgres can't change column positions. ... ADD COLUMN * AFTER, ... ADD COLUMN * BEFORE are not supported
There are differences for column change operations In MySql, it can be used with one query:

ALTER TABLE jobs MODIFY name varchar(100) NULL DEFAULT 'cron_job' COMMENT 'job name';

Same for PostgreSQL will be like:

ALTER TABLE jobs ALTER COLUMN name TYPE varchar(100);
ALTER TABLE jobs ALTER COLUMN name DROP NOT NULL; 
ALTER TABLE jobs ALTER COLUMN name SET DEFAULT 'cron_jobs';
COMMENT ON COLUMN "jobs"."name" IS 'job name';

MySQL doesn't support default values for columns with types BLOB, TEXT, JSON, GEOMETRY. Postgres can set it.

Generated Columns

MySQL supports generated columns, that calculate self-value based on values of other columns automatically. It looks like

ALTER TABLE my_box ADD COLUMN volume integer as (width * height * len);

PostgreSQL with version < 12 doesn't support it. But since 12 it can same with syntax like:

ALTER TABLE my_box ADD COLUMN volume integer GENERATED ALWAYS AS (width * height * len) STORED;

Autoincrement Index

Unlike MySQL, Postgres allows us to has more than one autoincremented column (and even with different increment settings). It called sequences.
How to get last inserted id?

You can create the insert query with an option for return id.

INSERT INTO users (name, age) VALUES ('Joe', 20) RETURNING id;

You can use functions currval() for last inserted id and nextval() for next id.

SELECT currval(pg_get_serial_sequence('users', 'id'));
SELECT nextval(pg_get_serial_sequence('users', 'id'));
-- pg_get_serial_sequence is a function for return sequence name by table name and index column name. In most cases it called like tablename_columnname_id_seq.

How to change the next increment value?

(It could become a necessary operation if you fill table ids manually)

SELECT setval(
pg_get_serial_sequence('users', 'id'),
 (SELECT MAX(id) FROM users)
);

The TRUNCATE operation doesn't flush sequence by default. It could create unwanted effects. Use the truncate command with an explicit option

TRUNCATE TABLE table_name RESTART IDENTITY;

Full-text Search

If you use full-text search functional with MySQL, you should be ready for serious work to migrate, because Postgres analog is really different.
I just publish a link to the official documentation.

How to move the data?

PgChameleon https://pgchameleon.org/documents/
PgLoader https://pgloader.io/ and other converters
via framework and ORM with both db connections. Read from one, modify and insert into another.

So, seems that is all, that I remember from my experience. If you also have an experience in migration applications from one DB system to another - welcome to comments.

How to use Invoker with docker containers?

Insolita — Sun, 26 Jul 2020 15:07:13 +0000

What is Invoker?
Invoker - is the universal data management tool for laravel/eloquent based applications You can see it features at https://invoker.dev/

It can work with local applications and with remote by ssh connection. But, unfortunately, it hasn't abilities for integration with apps that developed in dockers out of the box, because it distributed as executable gui application.
So we need to add an ssh service for our docker-compose.

If you use separated containers for cli and fpm, you should use cli version as a base for a new ssh container. Otherwise, use your common php container.
If you use prepared php image:
- create new directory, named ssh in you folder with docker config
- Inside this directory create new Dockerfile

FROM {your php image}

USER root
RUN apt-get update && apt-get install -y openssh-server \
    && mkdir /var/run/sshd \
    && echo 'root:secret' | chpasswd \
    && sed -i 's/PermitRootLogin prohibit-password/PermitRootLogin yes/' /etc/ssh/sshd_config \
    && sed -i 's/#PasswordAuthentication/PasswordAuthentication/' /etc/ssh/sshd_config \
    && sed -i 's/#PermitRootLogin/PermitRootLogin/' /etc/ssh/sshd_config \
    && sed 's@session\s*required\s*pam_loginuid.so@session optional pam_loginuid.so@g' -i /etc/pam.d/sshd \
    && apt-get clean \
    &&  rm -rf /var/lib/apt/lists/* /tmp/* /var/tmp/*

ENV NOTVISIBLE "in users profile"
RUN echo "export VISIBLE=now" >> /etc/profile

EXPOSE 22
CMD ["/usr/sbin/sshd", "-D"]

If you already use Dockerfile for php-cli service, copy it inside created ssh directory, remove CMD, ENTRYPOINT and EXPOSE instructions if it exists, and append ssh instructions above

add record in docker-compose.yml

services:
  sshd:
    build:
       context: your_docker_dir/ssh
    restart: on-failure
    ports:
    - '2266:22'
    links:
      - db # dabase service
      - php-cli  #php service
    volumes:
#for docker-compose < 3.x you can use volumes_from
      - data:/app  
    networks:
      - your_network  #if exists

Run docker-compose up -d,
Open Invoker, press "Set up SSH connection", and fill

Next, you can explore your project models.

If you use docker-compose with version >= 3.4 - you can combine Dokerfile instructions like below

FROM php:7.4-cli as php_base #use preferred php image
# Place here your php installation and configuration instructions
FROM php_base AS php_ssh

USER root
RUN apt-get update && apt-get install -y openssh-server \
    && mkdir /var/run/sshd \
    && echo 'root:secret' | chpasswd \
    && sed -i 's/PermitRootLogin prohibit-password/PermitRootLogin yes/' /etc/ssh/sshd_config \
    && sed -i 's/#PasswordAuthentication/PasswordAuthentication/' /etc/ssh/sshd_config \
    && sed -i 's/#PermitRootLogin/PermitRootLogin/' /etc/ssh/sshd_config \
    && sed 's@session\s*required\s*pam_loginuid.so@session optional pam_loginuid.so@g' -i /etc/pam.d/sshd \
    && apt-get clean \
    &&  rm -rf /var/lib/apt/lists/* /tmp/* /var/tmp/*

ENV NOTVISIBLE "in users profile"
RUN echo "export VISIBLE=now" >> /etc/profile

EXPOSE 22
CMD ["/usr/sbin/sshd", "-D"]

And use it in docker-compose like:

services:
  php-cli:
    build:
      context: docker/php
      target: php_base
    restart: on-failure
    links:
      - db
      - redis
    volumes:
      - data:/app
    environment:
      - TZ=${DB_TIMEZONE}
      - PHP_XDEBUG_ENABLED=1
      - XDEBUG_CONFIG=remote_host=host.docker.internal
      - PHP_IDE_CONFIG="serverName=Docker"
    networks:
      - appnet
  sshd:
    build:
       context: docker/php
       target: php_ssh
    restart: on-failure
    ports:
    - '2266:22'
    links:
      - db
      - php-cli
    volumes:
      - data:/app
    networks:
      - appnet

Don't forget to take care about keep ssh service only for the local environment!

Which Http Client is Faster for Web Scraping

Insolita — Sat, 02 May 2020 18:06:40 +0000

Inspired by an article Fast Web Scraping With ReactPHP I've decided to make a benchmark for checking, how much it faster than some other popular libraries, like Guzzle, which also can create async requests via multicurl and Amphp that is another non-blocking php framework, that contains http-client

I don't want to make a synthetic benchmark and prefer for my test more practical task - is loading different real urls from the defined list (part of them may be broken), scrape it titles, and save into a file.

During development, I've faced certain difficulties, which did not allow me to create my test clients completely similar. Each client has its own specific features, especially amphp, and also I have not so big experience with async libraries such as reactphp and amphp.

So, you can see the repository with test stuff and benchmark results here
https://github.com/Insolita/php-async-benchmarks All tests were written with php7.4. Each check was run 10 times, and I publish min, max, and average execution time. I should add a notice that the concrete numbers not so important because it depends on internet speed, server config, etc.., and you can have another result. Only their relative differences has a value

Firsts results really surprised me. ReactPhp works 2 times slower than the Guzzle. I rechecked it again and again, but the numbers stayed the same. But with the increasing number of queries, its performance becomes better and better. On the other hand, amphp performance becomes slower and slower and I even exclude it from the last measurement. (It depends on its specific, at the documentation, I find out only one way for concurrent requests https://amphp.org/http-client/concurrent, probably exists better way or additional libraries that also allows to queue promises smarter (like a clue/reactphp-mq), but I have not found it)

In summary, ReactPhp can be a good decision, when you need to fetch many thousands of urls, especially when you keep it as a separate worker, that will receive tasks by socket/Redis or http api. Amphp can be good when you need to fetch a little batch of urls, 5-10-50 asap. Also it can become better with additional wrappers. The Guzzle is awesome.

UPD: Just see the power of the Open Source community in action! One of maintainers of the Amphp, Niklas Keller, thanks to my benchmark find out and fix the bug. And now, thanks to the help of Dmitry Balabka and Niklas Keller - the performance metric of Amphp http-client was significantly improved!

UPD2: An outsider becomes a winner! Thanks to improvements, Amphp metrics look good at small batches as well as big! I'm intrigued, will reactphp team to offer improvements for increase their http-client speed?

UPD3: Yes! ReactPHP team accept the challenge, and also start to work on the fix!

Interesting! It looks like your list of servers contains a bunch of entries that don't strictly conform to HTTP specs. I'm currently working on fix for @ReactPHP here: https://t.co/1gwIR2s4fp
— Christian Lück (@another_clue) May 4, 2020