Skip to content

Instantly share code, notes, and snippets.

@meglio
meglio / optimal-sqlite.php
Created April 15, 2024 01:14
2-in-1 columns in SQLite tables
<?php
/**
* Returns current time in nanoseconds, which is a bigint (int64) and thus can be used as a primary key in SQLite.
*/
function get_sqlite_bigint_id_time_based(): int
{
// The challenge here is that:
// - microtime() returns current time accurate to the nearest microsecond, while we need it to nanosecond
// - hrtime() returns a very precise time accurate to the nanosecond and is recommended for performance measurements,
@meglio
meglio / extract_max_number_from_prefixed_value.sql
Last active July 4, 2021 04:29
Scans all text values from the given table/column names and, given a text prefix, finds the maximum natural number prefixed with that prefix.
drop function if exists maybe_to_natural_number;
create or replace function maybe_to_natural_number(str text) returns integer
language plpgsql immutable leakproof returns null on null input parallel safe as $$
begin
if regexp_match(str, '^\d+$') is null then
return null;
end if;
return str::int8;
end;
$$;
@meglio
meglio / useAutofocus.js
Created May 16, 2021 07:47
React hook to autofocus on an element by a query selector
import {useEffect, useState} from "react";
import trim from 'lodash/trim';
export default function useAutofocus() {
const [querySelector, setQuerySelector] = useState(null);
// Either a non-empty string OR null
let normalizedQuerySelector = querySelector;
if (typeof normalizedQuerySelector === 'string') {
@meglio
meglio / Idle.js
Created May 16, 2021 03:18
Fires an 'idleTimeSeconds' event when the page is idle for the certain amount of seconds
import $ from 'jquery';
export const IDLE_EVENT_NAME = 'idleTimeSeconds';
/**
* How often an 'idleTimeSeconds' event is fired on the document instance.
*
* @type {number}
*/
const IDLE_EVENT_RATE_SECONDS = 10;
@meglio
meglio / sanitizeRelativeRedirectUrl.php
Created May 14, 2021 11:06
Sanitizes relative URL to use for redirects through HTTP response headers - when the URL comes from an insecure input
<?php
public static function sanitizeRelativeRedirectUrl($url): ?string
{
$res = parse_url($url);
// On seriously malformed URLs, parse_url() may return FALSE
if (!is_array($res)) {
return null;
}
@meglio
meglio / Dockerfile
Created April 8, 2021 15:32
pgbadger in Docker controlled by Makefile
# - docker/pgbadger/Dockerfile
# - Makefile
FROM perl:5.32.1
LABEL "com.mycompany.container"="pgbadger"
ARG PGBADGER_VERSION=11.5
WORKDIR /pgbadger
COPY pgbadger-${PGBADGER_VERSION}.tar.gz /pgbadger/
<?php
/******** TEST 1 ********/
// SELECT * FROM email WHERE id = 100
$fetched = [
'id' => 100,
'subject' => 'Welcome!'
];
@meglio
meglio / no_gaps_time_ranges.sql
Last active May 1, 2020 11:35
A stored PostgreSQL function to generate time range series. Join your data to the function's result and group by date_from in order to build a time-series histogram without gaps even if there is no data in some time ranges.
CREATE OR REPLACE FUNCTION no_gaps_time_ranges(
in start_date timestamptz,
in interval_len_sec bigint,
in num_intervals bigint
)
RETURNS TABLE (date_from timestamptz, date_to timestamptz)
LANGUAGE plpgsql
IMMUTABLE
AS $function$
DECLARE
@meglio
meglio / CodeSanityChecker.php
Last active April 29, 2020 13:01
Sanity-checks user PHP code intended to be used for simple formulas / logic description in admin panels. Only use with trusted members - this is a soft protection and not a fully-fledged sandbox.
<?php
/**
* Sanity-checks PHP code that was already parsed into AST with using php-parser library.
*
* The AST must be represented as a multi-level array with scalar values.
*
* Example usage:
*
* <code>
<?php
namespace Toptal\SpeedCoding\Controller\Api;
use Exception;
use Respect\Validation\Validator as v;
use Toptal\SpeedCoding\Api\ApiResult;
use Toptal\SpeedCoding\Auth;
use Toptal\SpeedCoding\Challenge\EmailProcessor;
use Toptal\SpeedCoding\Challenge\TaskResultComparator;