How to change the internal links of all the posts in wordpress

 When you change the url pattern of a Wordpress installation some internal links can still be pointing to the old pattern of urls.

To mitigate this problem you can use a plugin like redirection in order to redirect old pattern urls to the new ones. With this solution you can solve the problem of internal links and of external links too (that you don't have control).

However, it's recommended to change all the internal links inside your content to avoid innecessary redirections.

For this reason we've created a procedure to update the content of all the posts, looking for urls that already are redirected and replacing them for the new ones.

IMPORTANT: This procedure assume that you have already installed and configured the redirection plugin and you have good knowledge of Linux commands and MySQL.

The procedure is as follows:

- Dump the wp_posts table using mysqldump

- Backup this dump just in case

- Launch a query to get all the replacement commands

- Launch replacement commands to replace all the strings in the dump

- Upload the modified dump into mysql

1) Dump the wp_posts table to a file

mysqldump -u <user> -p<password> -h <host> <db> wp_posts > wp_posts.sql

2) Make a backup of the file, just in case...

cp wp_posts.sql wp_posts.bk.sql

3) Then launch this select using your favorite MySQL client or PHPMyAdmin.

The idea of the sql query is to get all the redirection redirects that are enabled in the Wordpress database (table wp_redirection_items) and generate a Linux command (sed) that substitute all the urls in the content from the original ones to the new ones.

select 
concat("sed -i 's/"
,replace(url,'/','\\/'),"/"
,replace(action_data,'/','\\/'),"/g' wp_posts.sql") cmd
from (
	select substr(url,1,length(url)-1) url
	, if(substr(action_data,length(action_data))='/', substr(action_data,1,length(action_data)-1), action_data) action_data
	, action_type, status, action_code
	, url original_url, action_data original_action_data
	from wp_redirection_items
	where url like '%/'
	union all
	select url
	, if(substr(action_data,length(action_data))='/', substr(action_data,1,length(action_data)-1), action_data) action_data
	, action_type, status, action_code
	, url original_url, action_data original_action_data
	from wp_redirection_items
	where url not like '%/'
) t 
where status='enabled'
and action_type='url'
and action_code=301
group by cmd;

A sample line that are returned by the sql query above is:

sed -i 's/\/blog\/2013\/03\/prepare-your-promotions-xxx/\/blog\/prepare-your-promotions-xxx/g' wp_posts.sql

4) Then execute all the lines in your Linux box it will replace all the urls that match /blog/2013/03/prepare-your-promotions-xxx by /blog\/prepare-your-promotions-xxx
because in my case I've changed the url pattern to avoid the date in the url.

Copy the results of all the lines into your Linux box in the same folder where you have generated the backup of the table wp_posts 
(you must have the utility sed installed (available in 99% of Linux installations)).

5) Upload the dump again

mysql -u <user> -p<password> -h <host> <db> < wp_posts.sql

Hope this help you.

Adding percentages to labels in amcharts5

 Amcharts5 is a very powerful graphics library, however the documentation is really scarce.

If you want to add the percentage of a value in some label you must use the undocumented valuePercentTotal attribute in the desired text label, for example.

legendValueText: "[bold {fill}]{value} {valuePercentTotal.formatNumber('0.00p')}[/]"

Generates a label like this:

This value can be used in different labels and can be formatted as the other attributes like "value".

Hope this helps

 

Post resum 2022

Com cada any deixo aquí un resum del meu any 2022 sense editar molt. Un any marcat pel meu canvi  de feina d'Omatech (després de gairebé 16 anys) cap a Easypromos, on em sento molt ben acollit.

Kilòmetres patinats: 238, cal millorar!

Exits:

- Adsmurai x15

Gadgets of the year (comprats o regalats):

- LG Gram

- Airpods Pro Max

- Navalla suïssa

Samarretes del any:

DESERT

Friky coses guays:

- n8n.io

- parceljs

- mermaid

- sidecar

- browsershot

- lando.dev

- bitsy

- Xata

- Redox

- Stackblitz

- loom

- matomo

- facebook/lexical

- redpanda

- mattermost

- loadforge

- filamentphp

- bun.sh

- zig

- orchid

- remix

- prisma.io

- sveltekit

- baserow.io

- plausible.io

- fresh.deno.dev

- railway.app

- astro

- openai and Dall-e

- simplemvc

- squirephp https://github.com/squirephp/squire

- sushi php

- https://laravel.initializer.dev/

- ponylang.io

- https://github.com/JuliaLang/julia

- VemtoApp

- jsoncrack

- qwick.builder.io

- NocoDB

- FrankenPHP

- glitch.com

- locust.io

- https://whatthediff.ai

- https://turbo.build/

- rectorphp

- twill

- bref.sh

- spiral.dev

- ulid https://github.com/ulid/spec

- Mitosis

- sulu.io

- alpine.js

- blitz.js

- synthesia

- Nanocrazor

Jocs als que m'he viciat aquest any:

- PUBG New State

- Plants vs zombies 2

- Tetris99

- Katamari

Grups guays que he descobert aquest any:

- Desenterradas

- Dry Cleaning

- Grasias

- Dan Romer

- Clise to Modern

- Robot Emilio

- Ovlov

- Big Thief

- Dexist

- Wet Leg

- Pillow Queens

- Nation of Language

- Yawners

- Ezekiel

- Mitski

- Just Mustard

- Portico quartet

- Lund quartet

- Whatever, Dad

- Cuchillas

- Radiator Hospital

- Secundaria

- High Vis

- Policias y Ladrones

- Videoclub

- El diablo de Shangai

- Indian Summer

- Porridge Radio

- La Paloma

- Childbirth

Concerts als que he anat:

- Ojete Calor al Sant Jordi Club

- Los Punsetes + Marcelo Criminal a l’Apolo

- Just Mustard + Calibra al Sidecar

- Menta + El Diablo de Shangai a la sala VOL.

- The Cure al Palau Sant Jordi

- McEnroe + El verbo odiado a l’Apolo2

Pelis que he vist:

- Spiderman no way home

- La mano de Dios

- Don’t look up

- While we’re young 

- Marriage Story

- Attack the block

- Sputnik

- Ad Astra

- O que arde

- Gone Girl

- The French Dispatch

- The Adam Project

- Driven

- El método Williams

- All eyes off me

- Déjame caer

- Los Cronocrimenes

- Monster

- Lizzie

- Tourists

- Utoya

- Dr. Strange en el multiverso de la locura

- Moonfall

- Eternals

- Iron Sky

- Heat

- Peninsula

- Dredd

- Oranges Sanguines

- Apnee

- Prey

- Day shift

- To your last death

- Incendies

- El reino de la belleza

- Softie

- Yo, Cristina F

- Satan’s Slave

- Blonde

- X

- Sissy

- Deep Water

- Host

- Planeta rojo

- As Bestas

- Barbarian

- Captive State

- Troll

- Mantícora

- Your Lost Memories

Series a les que m’he enganxat:

- The book of Bobba Fett

- Curb your enthusiasm s4, s5, s6, s7, s8, s9, s10, s11

- Celebrity Bake Off España

- Station Eleven

- Curb your entusiashm

- All of us are dead

- Peacemaker

- Euphoria s2

- Mai neva a ciutat s1, s2, s3

- The Dropout

- Winning time

- The Boys s3

- Locomia

- Crims s1, s2, s3

- Love, Death + Robots s1, s2

- Maricon perdido

- Stranger Things s4

- Children of Dune

- Narco Saints

- Dahmer

- La liga de los hombres extraordinarios

- Antidisturbios

- The Bear

- Autodefensa s1, s2

Curts:

- Cerdita

Llibres que he llegit:

- Conversaciones con Ana Curra

- Domain Driven Design in PHP

- Radical Candor

- Astrofísica para gente con prisas

- From zero to sold

Comics:

- Dinastia de X / Potencias de X

- Dawn of X

- Hierba

- The Hellfire gala

- X lives of Wolverine / X deaths of Wolverine

- The trial of Magneto

- Destiny of X

- La patrulla X y los Nuevos titanes

- X-Factor

- DUNE I

- I molts més amb Marvel Unlimited

Teatre:

- Faemino y Cansado. Quien tuvo, retuvo

Documentals:

- Autosuficientes

- Warning: This drug may kill you

- 4 hours at the Capitol

- Devastados por el cristal

- Arcadeology

- Almost Ghosts

- Jimmy Savile, a British Horror Story

- The push

- Tell me who I am

- Natalie Wood, entre bambalinas

- Val

- Jane Fonda en cinco actos

- We were once Kids

- The John Wayne Gacy Tapes

- Los Anarquistas

- Keep sweet pray and obey

- Wall Street: David contra Goliat

- Swiped

- Orgasm Inc.

- Ummo, La España alienígena

- Entre la terra i el cel: Guerra

- Criptomonedas: guía para principiantes

- La Sagrada Família

- Osel

- Salvar al Rey

Podcasts:

- itnig

- X-Rey s1, s2

- Le llamaban Padre

Sortides al Extranjer:

DESERT

Hotels als que he anat:

- Granados 83

- Hotel Petit Mirador

- Ultonia, Girona

Restaurants:

- Ikoya

- Kanada Ya

- La Nansa, Sitges

- Els pescadors

- Lluerna, Santa Coloma

- La barraca del Delta, Amposta

- El Racó del Port, Les cases d’Alcanar

- Draps, Girona

Sitges 2022:

38 pelis, 800kms, 16 peatges

Pelis vistes:

Dijous 6:

- Hunt

- King of screen

- The breach

- Blood flower

- Carnifex

- Deep fear

Divendres 7:

- Polaris

- After Yang

- Satan’s Slave Communion

- [REC] Terror sin pausa

- Flesheater

Dissabte 8:

- Kids vs Aliens

- Saloum

- Mal de ojo

- Flux gourmet

- The Lair

Diumenge 9:

- Tales from the Occult

- Resurrection

Dilluns 10:

- Family Dinner

- Rubikon

- Occhiali neri

Dimarts 11:

- Megalomaniac

- Slash back

- Incroyable mais vrai

- Emergency declaration

- The Knocking

- Christmas bloody Christmas

Dimecres 12:

- Shepherd

- Fumer fait tousser

Dijous 13:

- Glorious

- Social Distancing

- Pearl

Divendres 14:

- Harbinger

- Viejos

Dissabte 15:

- Wolfkin

- La exorcista

- Sisu

- Alienoid

Ranking millors pelis (de millor a pitjor):

- Sisu

- Fumer fait tousser

- Pearl

- Glorious

- Incroyable mais vrai

- Satan’s Slave Communion

- Resurrection

- Mal de ojo

- Megalomaniac

- Deep Fear

- (menció especial al documental de [REC])

Ranking pitjors pelis (de pitjor a… bé):

- Occhiali neri

- Social Distancing

- Tales from the Occult

- Carnifex

- Polaris

Grups vistos al Primavera Sound 2022:

Dijous:

Aiko el grupo - 18:15 - Ouigo

Les Savy Fav - 18:55 - Binance

Richard Dawson - 19:05 - Plenitude

Dinosaur Jr. - 20:00 - Cupra

Sharon Van Etten - 21:05 - Binance

Yo la tengo - 22:20 - Cupra

Tame Impala - 22:55 - Estrella Damm

Pavement - 00:40 - Pull & Bear

Divendres:

Pinpilinpussies - 16:30 - Ouigo

Cariño - 17:15 - Binance

Beach Bunny - 18:00 - Cupra

Manel - 19:00 - Pull & Bear

Fontaines DC - 20:05 - Estrella Damm

Shellac - 20:50 - Plenitude

Beck - 21:20 - Pull & Bear

Parquet Tours - 22:35 - Plenitude

Jehnny Beth - 23:30 - Ouigo

Dissabte:

Die Katapult - 18:45 - Tous

Automatic - 19:55 - Ouigo

Black Country New Road - 20:40 - Binance

Nick Cave and the bad seeds - 22:00 - Pull & Bear

Bauhaus - 22:55 - Binance

Gorillaz - 00:15 - Estrella Damm

Que tingueu un molt bon any 2023!

Agustí

Query to get drop and create foreign keys pointing to a table

When making DB scripts that massively manipulate big tables in MySQL sometimes you want do disable all foreign keys pointing to a particular table, make some operations and then enable the foreign keys again.

You can query the mysql information schema to get the metainformation of the foreign keys and generate the appropiate statements.

Just change the $db-schema-name and $table-name in this query and you can use the drop_cmd column that contains all the drop foreign keys statements and the create_cmd column that contains the add constraint statements to enable them again

SELECT r.CONSTRAINT_SCHEMA db, r.REFERENCED_TABLE_NAME referenced_table_name
, r.CONSTRAINT_NAME, r.TABLE_NAME
, c.COLUMN_NAME, c.REFERENCED_COLUMN_NAME
, r.UPDATE_RULE, r.DELETE_RULE
, CONCAT ('alter table `',r.CONSTRAINT_SCHEMA,'`.', r.TABLE_NAME, ' drop foreign key ',r.CONSTRAINT_NAME,';') drop_cmd 
, CONCAT ('alter table `',r.CONSTRAINT_SCHEMA,'`.', r.TABLE_NAME
, ' add constraint ', r.CONSTRAINT_NAME
,' foreign key (',c.COLUMN_NAME,')'
,' references ', r.REFERENCED_TABLE_NAME, '(',c.REFERENCED_COLUMN_NAME,')'
,' on delete ', r.delete_rule
,' on update ', r.update_rule
,';') create_cmd 
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS r
, INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE r.CONSTRAINT_SCHEMA = '$db-schema-name'
AND r.REFERENCED_TABLE_NAME = '$table-name'
AND c.REFERENCED_TABLE_SCHEMA = r.CONSTRAINT_SCHEMA
AND c.REFERENCED_TABLE_NAME = r.REFERENCED_TABLE_NAME
AND c.CONSTRAINT_NAME = r.CONSTRAINT_NAME
ORDER BY r.CONSTRAINT_NAME

Hope this will help you!

Beware of accumulating expired Octane cached items

When using swoole tables as Octane cache in Laravel, be aware that the expired cached items are never really deleted. 

The get method of the cache ignores the expired items but they are never flushed if you don't explicitly delete them. 

To avoid the Swoole Cache table to grow ad aeternum you must do some cleanup from time to time.

Define a new class that will do the clean up every minute using the Tick functionality of the Swoole extension. 

<?php

namespace xxxxxx;

use Illuminate\Support\Facades\Cache;

use Illuminate\Support\Facades\Log;

use Laravel\Octane\Facades\Octane;

use Illuminate\Support\Facades\App;

class CacheHouseKeeping {

public static function clean () {

  $table=App::make('octane.cacheTable');

    $i=$deleted=0;

    Log::info("** Current date=".date('Y-m-d H:i:s')." expiring old cached items");

    foreach ($table as $key => $record) {

    if ($record['expiration']<time()) {

Log::debug("EXPIRING key=$key value=".$record['value'].' expiration='.date('Y-m-d H:i:s', $record['expiration']));

        $table->del($key);

        $deleted++;

      }

      $i++;

    }

    Log::info("** Total keys scanned $i, deleted=$deleted remaining=".($i-$deleted)." finished ".date('Y-m-d H:i:s'));

  }

public static function boot() {

  Octane::tick('cache-housekeeping-ticker', fn () => self::getAndReport())

    ->seconds(60);

  }

}

And in the file app/Providers/RouteServiceProvider.php call the boot method of the class in order to set the Ticker. 

... 

CacheHouseKeeping::boot(); 

... 

Then, every minute the clean function will be executed and will clean all the expired items in the cache.

The main lines works as follows: 

1. Get the cache Swoole table from the application service container (the App Facade).

$table=App::make('octane.cacheTable'); 

2. Loop through all the items in the swoole cache table records

foreach ($table as $key => $record) { 

3. Check the expiration date 

if ($record['expiration']<time()) {

4. Delete the expired key 

$table->del($key); 

Hope this help you to keep the Octane Swoole Cache under control.

How to detect real client ip in swoole laravel octane without nginx but behind a reverse proxy

 

When using Laravel Octane without nginx and you're behind a load balancer like Google Cloud Platform's or Amazon AWS or other reverse proxy that sets X-FORWARDED-FOR header, you must check this header to get the real client IP.

I've tried setting TrustedProxies etc. with no succes. And Laravel Octane didn't get it right when you call request()->ip()

Finally I've found the solution getting the server parameter HTTP_X_FORWARDED_FOR from the request and getting the first IP from a comma separated list. 

Check in your particular case if maybe the client IP is set in other header like HTTP_REAL_IP or similar using dd(request()->server) to get all the parameters bag.

You can use this sample class (adjust your namespace):

<?php

namespace YourOrg\Helpers;

class Ip {

    public static function get () {

        $XFFip=request()->server->get('HTTP_X_FORWARDED_FOR');

        if ($XFFip) {

            $ips = explode(',', $XFFip);

            $ips = array_map('trim', $ips);

            $ip = $ips[0];        

        } else {

            $ip=request()->ip();

        }

        return $ip;

    }

}

Then anywhere in your code you can use:

use YourOrg\Helpers\Ip;

...

echo Ip::get();

Have fun

Post resum 2021

Gadgets of the year (comprats o regalats):

desert

Kilòmetres patinats: 930

Samarretes del any:

sadako gameboy

sushi kill bill

starters friends

scarlet witch, no more mornings

Friky coses guays:

craftcms

workerman

newman

wrk

swoole

roadrunner

meilisearch

rasa

krakend.io (lura)

Utopia

phpinsights v2

hotwire turbo

phpsandbox.io

getporter.dev

neatnik.net/view-source

SvelteKit

Frontity

https://laravel-news.com/rich-text-for-laravel

pixijs.io

Astro

solid.js

asyncapi

monaco-editor

gather.town

api platform

react-admin

lean-admin.dev

builderio/qwik

builderio/partytown

lighthouse-php

locust.io

Jocs als que m'he viciat aquest any:

Sparkle 2

Puyo puyo tetris

PUBG New State

Grups guays que he descobert aquest any:

Alcest

Pantocrator

Melenas

Martes Niebla

Marta Movidas

Nueve desconocidos

Patio Rosemary

The Goon Sax

L.A. Exes

Menta

Margaritas podridas

Disco Las Palmeras!

Maw

Beladrone

Airiel

shane

Dharmacide

Cruz de Navajas

Ciudad lineal

Depresión sonora

Último día

Plataforma

Chill Mafia Records

Sofia

Luz Futuro

Mausoleo

Somos la herencia

Cruhda

Nunca Nada

VVV

Margarita quebeada

Viuda

Paralelo

El destello

Quiets

Galería interior

Concerts als que he anat:

desert

Pelis que he vist:

La autopsia de Jane Doe

Hellboy (2019)

JT Leroy: Engañando a Hollywood

Mank

Primos

Widows

Wonder Woman

The Big Short

El escándalo

Deadpool 2

Odio por Dani Rovira

The assistant

Las ventajas de ser un marginado

Cherry

Space Sweepers

Zach Snyder’s Justice League

Capa caída

Josep

Die Hard 4.0

Hangover

Hangover 2

Hangover 3

Robocop 2014

Spiderman far from home

Lego Movie 2

Los juicios de Nuremberg

Don Jon

The Circle

Army of dead

Sucker Punch

Shiva baby

Mitchells vs the machines

Jay and Silent Bob: The reboot

Midsommar

The night eats the world

The eight night

War of tomorrow

Terminator: Genisys

Man on the moon

A quiet place

Heavy Metal

It follows

Reindeerspoting

The lost boys

1987

John Wick 3, Parabellum

Night in Paradise

El padrino

El padrino 2

Cruella DeVil

El padrino 3

Dune

Wyrmwood

The last of wolves

Ali G indahouse

Destello bravio

Tesla

Crash

Sound of violence

The thing

The lighthouse

Veteran

Kenshin

My hero academia, hero’s world mission

Dolores. La verdad sobre el caso Wanninkhof

Greetings from Tromaville

Zombies en el cañaveral

Holiday

Red State

Bohemian Rapshody

Joker

Green room

Freud: The secret passion

The Show

Atrapado en el túnel

Free guy

Frances Ha

Venom

A scanner darkly

Black Widow

Ron da error

Pelis vistes a Sitges:

Hunter Hunter

Alien on stage

Caveat

Werewolves wihin

La Pasajera

Witch hunt

Censor

Le calendrier

Mona Lisa and the blood moon

Wyrmwood apocalypse

Broadcasting signal intrussion

Realidad virtual

Cliff Walkers

The amusement park

The deep house

The Sadness

The boy behind the door

Llanto Maldito

The trip

The knocking

Lamb

in the earth

the exorcism of god

Superhost

The power

El páramo

Hand rolled cigarrete

Limbo

Barbarians

Antlers

Eight for silver

The medium

The Samejima incident

She will

Nitram

Demonic

Last night in soho

The great yokai wars, Guardians

Son

Tides

Dashcam

Top 5 i bottom 5 Sitges:

Top 10 (de millor a pitjor):

The Sadness

Last night in soho

Dashcam

Alien on stage

Censor

The trip

Limbo

Lamb

Son

The deep house

Bottom 5 (de millor a pitjor):

Caveat

Broadcasting signal intrussion

The boy behind the door

Llanto Maldito

The knocking

Curts:

desert

Llibres que he llegit:

The mythical man-month

Object Oriented Programming, Timothy Budd

Clean Code, Robert C. Martin

It doesn’t have to be crazy at work, Jason Fried, David Heinemeier

Ready player two, Ernest Cline

Dune (rellegit)

Comics:

molts, he perdut el compte

Series a les que m'he enganxat (sense ordre particular):

La casa de papel s1,s2,s3,s4,s5

Squid game

The Soprano s1-s6

Seinfield s1-s9

Sweet Home

The morning show s1

Unorthodox

High Score

Servant s1,s2

It’s a sin

Wandavision

The Boys s2

Raised by wolves

Calls

Snowpiercer s2

Invincible

Mrs. Fletcher

The Crown s4

Ted Lasso

The royal house of windsor

Falcon and the winter Soldier

Snabba Cash

Queen’s Gambit

Jupiter’s Legacy

El vecino s1, s2

Loki

Sweet Tooth

Inside Job

The billion dollar code

Borat’s American Lockdown

Black Summer s1,s2

Todo lo otro s1

black Mirror s1,s2,s3

Station Eleven

The Silent sea

Hellbound

Hawkeye

Curb your enthusiasm s1,s2,s3

Teatre:

Faemino y Cansado. Quien tuvo, retuvo

Documentals:

High Score

El Pepe a supreme life

Brexit at closed doors

Pobreza en Estados Unidos

Power of Grayskull

Allen vs. Farrow

Billie Eilish, the world is little blurry

Wrinkles the clown

El Víbora, Solo para supervivientes

El Terrat, los primeros 30

Lolo Rico, la mirada no inventada

The Reagan show

El culo del mundo

Friends: The reunion

Barça Dreams

Dirty Money s1, s2

Becoming Warren Buffet

Heroin, Cape Cod, USA

Spielberg

Q into the storm

La Familia

Lo and behold: el inicio de Internet

Los bots del dinero

El arma perfecta

Galácticos

El chico más bello del mundo

Jodorosky’s dune

La dura cerdad sobre la dictadura de Franco

El fin de ETA

Story of plastic

Cryptopia: Bitcoin, Blockchains and the future of the Internet

San Julian: el poder de la ilustración

What happenes to Brittany Murphy?

Springboard

NYC Epicenters 9/11 -> 2021

Sortides al Extranjer:

desert

Millors Hotels als que he anat:

Hostal Sa Tuna

Millors restaurants:

Yatai

La forquilla (2)

Dolsotbap

Devil’s Kitchen

Hostal Sa Tuna

The ultimate purge logs bash script for Laravel

Let's assume that you have a very large log table that can grow up to millions of records.

 In this example the table is named activity_logs and VERY IMPORTANT it should have an index in created_at to allow us to remove records by date.

If you put this shell script in the root folder of your Laravel project it will take the database credentials from your .env file and purgue the log table day by day, from an starting date to an end date (in the example from 6 months ago to 2 months ago)

#### Contents of purge_logs.sh

dbhost=$(grep -oP '^DB_HOST=\K.*' .env)
dbpass=$(grep -oP '^DB_PASSWORD=\K.*' .env)
dbname=$(grep -oP '^DB_DATABASE=\K.*' .env)
dbusername=$(grep -oP '^DB_USERNAME=\K.*' .env)

i=$(date --date="6 months ago" +"%Y-%m-%d")
limit=$(date --date="2 months ago" +"%Y-%m-%d")
while [ "$i" != "$limit" ]; do
    echo $i limit $limit
    tomorrow=$(date -I -d "$i + 1 day")

    mysql -u $dbusername -p$dbpass -h $dbhost --default-character-set=utf8 $dbname << EOF
delete from activity_logs
where
created_at>='$i'
and created_at<'$tomorrow'
EOF

    sleep 5
    i=$tomorrow
done

#### end of purge_logs.sh

Explanation

Those lines take the db parameters from the .env file and make them available to the script.

dbhost=$(grep -oP '^DB_HOST=\K.*' .env)
dbpass=$(grep -oP '^DB_PASSWORD=\K.*' .env)
dbname=$(grep -oP '^DB_DATABASE=\K.*' .env)
dbusername=$(grep -oP '^DB_USERNAME=\K.*' .env)

Then configure the starting date and the limit

i=$(date --date="6 months ago" +"%Y-%m-%d")
limit=$(date --date="2 months ago" +"%Y-%m-%d")

You can change the parameters as long as they are valid date params, for example you can use "today", "tomorrow", "2 weeks ago", ...

In the next two lines we'll start the loop and echo the dates for debug purposes, and guess the next day in the variable "tomorrow", beware, the limit should be greater than the start date, if you don't want an infinite loop :)

while [ "$i" != "$limit" ]; do
    echo $i limit $limit
    tomorrow=$(date -I -d "$i + 1 day")

 Then launch mysql with the script to delete the records between $i and $tomorrow

     mysql -u $dbusername -p$dbpass -h $dbhost --default-character-set=utf8 $dbname << EOF
delete from activity_logs
where
created_at>='$i'
and created_at<'$tomorrow'
EOF

 In the last lines we'll sleep for a while to avoid stressing our server and recalculate the $i date

    sleep 5
    i=$tomorrow
done

Finally you can add a line in your crontab to run the script once a day for example:

10 0 * * * cd root_folder; ./purge_logs.sh

Hope this help

 

Post resum 2020

Gadgets of the year (comprats o regalats):
Cap

Samarretes del any:
Ink Snorlax
Dark Star Summer Camp
LeChuck Grog
Offline Park
2020 bad review
psycho star wars
raise spirits up
I don’t always test my code but when I do, I do it in production
I am an engineer to save time, just assume I’m aleays right


Friky coses guays:
Stimulus
Sails.js
nedb
Redox
Tactician
inertia.js
svelte.dev
structurizr.com
strapi.io
dgraph.io
deno.land
https://www.biotech-foods.com
beekeeperstudio.io


Jocs als que m'he viciat aquest any:
Fallout Shellter
PUBG mobile
COD mobile

Grups guays que he descobert aquest any:
Bigott
Lisasinson
Crushed Beacks
Pet Shimmers
Alvvays
Comando Suzie
Soviet Soviet
Anabel lee
Topographies
Automatic
Chelsea Wolfe
Somos la herencia
Ambros Chapel
Luna y panorama de los insectos
Marcelo Criminal
Ela Minus
Décima víctima

Concerts als que he anat:
RIDE + Crushed Beaks
Sandy (Alex G) + Pet shimmers

Pelis que he vist:
Girl
The drug king
I’m not a serial killer
Toy Story 4
Captain Marvel
Thor el reino oscuro
Thor Ragnarok
Guardians of the galaxy
Guardians of the galaxy vol 2
Ant-man
Captain America winter soldier
Captain America Civil War
Time to hunt
Avengers Age of Ultron
Dr. Strange
Avengers Infinity War
Guns Akimbo
Grabbers
I love you, stupid
Rampant
The Clobberfield Paradox
Da 5 Bloods
Dolor y gloria
Velvet buzzsaw
Shin Godzilla
You were never really here
Mortal engines
Canino
The rules of attraction
Jupiter’s moon
1984
Greyhound
The old guard
Snowpiercer
Tiempo después
1917
System error
Extraterrestre
Outbreak
American Pickle
Illang la brigada del lobo
The wandering earth
#Alive
Aquaman
Take shelter
Showgirls
Borat 2
The Purge: Anarchy
Padre no hay más que uno
La caída del imperio Americano
La edad de la ignorancia
Please stand by
De Pollos y hombres
Old boy
On the rocks
Blade runner 2049
Dans la brume
Under the skin

Pelis vistes a Sitges:
The night
Vicious fun
La funeraria
Blackwater: Abyss
The pale door
Archenemy
Kubrick by Kubrick
Bumperkleef
Malnazidos
Lucky
Spirit walk
La vampira de Barcelona
Mandibules
The queen of black magic
Boys from country hell
No matarás
The old ways
Mosquito state
May the devil take you 2
She dies tomorrow
Tin Can
A perfect enemy
Le Nuee
Soul
Ghost in the shell
Snowden

Top 5 i bottom 5 Sitges:

Top 5 (de millor a pitjor):
Mandibules
Bumperkleef
Le Nuee
The queen of black magic
A perfect enemy

Bottom 5 (de millor a pitjor):
She dies tomorrow
Lucky
The pale door
La funeraria
Tin Can

Curts:
Cap

Llibres que he llegit:
Open, Andre Agassi
Sapiens
Historias de terror, Liz Phair
El consentimiento, Vanessa Sprinzora

Comics:
The Boys, tomos 1,2,3
Guardians of the galaxy vol1, vol2
Raven
Snowpiercer
X-Men la saga Messiah Complex

Series a les que m'he enganxat (per ordre d'adiccio):
Dracula
Watchmen
Star Trek TNG s5,s6,s7
Picard
Kingdom s2
The Mandalorian
Upload
The Last Dance
La conjura contra America
Silicon Valley s6
See
The Purge s1, s2
Snowpiercer
Community
Dietland
Utopia s1, s2
El colapso
Watchmen
El fin de la comedia
Seinfeld
Little Britain USA
Norsemen s1, s2
Pure
Dragonslayer666
Días de mierda
Patria
Utopia 2020 us
Star Trek Discovery s3
Euphoria
Parlement
The Terror s1
30 monedas

Teatre:
Berto, cuanta tontería

Documentals:
McMillions
One child nation
Jeffrey Epstein: Filthy rich
Polansky, se busca
Garbo, the spy
Catalunya Barcelona: La historia catalana de Barcelona
Deep water
Eugenio
Sesión Salvaje
El Palmar de Troya
You don’t Nomi
The forum
Too funny to fail
El desafio ETA
Banksters
Oeconomia
James May, our man in Japan
This giant beast that is global economy

Sortides al Extranjer:

Cap

Millors Hotels als que he anat:
Sa Tuna

Millors restaurants:
Gorria (2)
Xerta
Lluerna
Ferran Cerro
Direkte

Post resum 2019

Exits:
omatech (parcial)

Gadgets of the year (comprats o regalats):
tado, smart air conditioning remote controller
Activ5
AirpodsPro

Samarretes del any:
Harry Pawter
Arrakis Surf
The Masters


Friky coses guays:
Cypress.io
omatech/laravel-imaginator
omatech/mage
editorjs.io
loom
flourish.studio
inertia.js
handsome tables
activ5
lucid architecture
apache kafka streams
quasar framework
debezium.io
openfaas
kubeless
Livewire
outklip.com
loadimpact.com
reqtest.com
phpspy
trix

Jocs als que m'he viciat aquest any:
Have Ho

Grups guays que he descobert aquest any:
Juanita y los feos
Cosmo K
Pablo prisma y las Piramides
La Plata
Axolotes Mexicanos
Las ligas menores
Confeti de Odio
Autoescuela
Rata negra
Joan as police woman
Schwervon!
American Football
Pantones
Sierra
Anari
Turquoise Days
Soccer Mommy
Siete70
Putilatex
Stephen Malkmus and the Jicks
Rojuu
Futuro terror
She passed away
Lebanon Hanover
Hawaiian Gremlins
Apartamentos Acapulco

Millors concerts als que he anat:
Petroleo al RAZZ
Yo la tengo al Apolo
Florence and the machine al Palau Sant Jordi
Ojete calor al RAZZ
Primavera sound 2019
Los Punsetes al RAZZ
Pixies al Sant Jordi Club

Millors pelis que he vist:
La pianiste
Bird box
Spiderman nuevo universo
The favourite
One cut of the dead
Blackkklansman
The dead don’t die
Brexit
Once upon a time in Hollywood
Train to Busan
Terminator: Genesis
Passengers
Farenheit 451
Scape from New York
Scape from LA
The offspring
The woman
Darkest hour
Parasite
The founder
Star wars the rise of Skywalker

Altres pelis que he vist:
Roma
Detective Pikachu
Demon hunter Nezha

Pelis vistes a Sitges:
Code8
One piece Stampede
Bodies at rest
Ready or not
Little monsters
Dark encounter
Ventajas de viajar en tren
Antrum
The room
The deeper you dig
Vivarium
The Nest
Judy and punch
L’Angle mort
Snatchers
Body at Brighton rock
Amigo
Daniel isn’t real
Misterio de la noche
Corporate animals
I trapped the devil
Super me
The mute
El hoyo
After midnight
J’ai perdu mon corps
Come to daddy
Girl on the third floor
The gangster, the cop and the devil
synchronic
Depraved
Swallow
The cleansing hour
Extra ordinary
Le Daim
Yves
Dawn
Sadako
The wild goose lake
Colour out of space
Hail Satan?
Memory, The origins of Alien


Top 10 i bottom 10 Sitges:

Top 10:
1. Ventajas de viajar en tren
2. El hoyo
3. Amigo
4. Swallow
5. Extra ordinary
6. Little monsters
7. Snatchers
8. Ready or not
9. The cleansing hour
10. Colour out of space

Bottom 10 (de mejor a peor):
10. Girl on the third floor
9. Bodies at rest
8. Body at Brighton Rock
7. Sadako
6. Dark encounter
5. Depraved
4. L'Angle mort
3. I trapped the devil
2. Misterio de la noche
1. Antrum

Curts:
Get your brain back
Limbo
Experimentos para transformar la fe en energia
Maw
Things Were
Better Before
Imaginario
Coda Sacra
Bath Bomb
I’m the doorway
Five course meal


Llibres que he llegit i m'han agradat:
El bosque oscuro
El fin de la muerte
Pensar rápido, pensar despacio
How to win friends and influence people
Hola mundo

Comics:
La muerte de Lobezno
Mandela y el general


Series a les que m'he enganxat (per ordre d'adiccio):
The boys s1
Star trek discovery s2
Flea Bag s2
Years and years
Kingdom s1
Game of thrones s8
Stranger things s3
The Witcher s1
Mind Hunters s2
Chernobil s1
Big little lies s1, s2, s3
Peaky Blinders s5
The crown s1, s2, s3
Star Trek TNG s1 s2 s3 s4
Silicon Valley s4,s5
The deuce s2
Love s1 s2
El Pionero
The alienist s1
Mira lo que has hecho s1 s2
Electric Dreams s1
Mars s1
El caso Alcasser
Another life s1
The umbrella academy s1

Teatre:
El Dolor, Marguerite Duras al TNC

Documentals:
Desenterrando Sad Hill
La guerra del wolframio
Las dos catalunyas
Jane Fonda in 5 acts
Generation wealth
Leaving Neverland
Global Junk Food
The Creepy Line
Wild Wild Country
The inventor: Out for blood in Silicon Valley
The Big Hack
En la mente de Robin Williams
HAK_MTL
Kurt and Courtney
ww2 in color

Sortides al Extranjer:
Madrid

Millors Hotels als que he anat:
Double tree by Hilton, Girona

Millors restaurants:
Celler de can Roca
Massana
Hofmann
Gorria
Minyam
Echegaray
Dos palillos
Els pescadors