132 lines
4.9 KiB
PL/PgSQL
132 lines
4.9 KiB
PL/PgSQL
-- This file was automatically created by Diesel to setup helper functions
|
|
-- and other internal bookkeeping. This file is safe to edit, any future
|
|
-- changes will be added to existing projects as new migrations.
|
|
|
|
|
|
|
|
|
|
-- Sets up a trigger for the given table to automatically set a column called
|
|
-- `updated_at` whenever the row is modified (unless `updated_at` was included
|
|
-- in the modified columns)
|
|
--
|
|
-- # Example
|
|
--
|
|
-- ```sql
|
|
-- CREATE TABLE users (id SERIAL PRIMARY KEY, updated_at TIMESTAMP NOT NULL DEFAULT NOW());
|
|
--
|
|
-- SELECT diesel_manage_updated_at('users');
|
|
-- ```
|
|
CREATE OR REPLACE FUNCTION diesel_manage_updated_at(_tbl regclass) RETURNS VOID AS $$
|
|
BEGIN
|
|
EXECUTE format('CREATE TRIGGER set_updated_at BEFORE UPDATE ON %s
|
|
FOR EACH ROW EXECUTE PROCEDURE diesel_set_updated_at()', _tbl);
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE OR REPLACE FUNCTION diesel_set_updated_at() RETURNS trigger AS $$
|
|
BEGIN
|
|
IF (
|
|
NEW IS DISTINCT FROM OLD AND
|
|
NEW.updated_at IS NOT DISTINCT FROM OLD.updated_at
|
|
) THEN
|
|
NEW.updated_at := current_timestamp;
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- nanoid function
|
|
CREATE EXTENSION IF NOT EXISTS pgcrypto;
|
|
|
|
-- The `nanoid()` function generates a compact, URL-friendly unique identifier.
|
|
-- Based on the given size and alphabet, it creates a randomized string that's ideal for
|
|
-- use-cases requiring small, unpredictable IDs (e.g., URL shorteners, generated file names, etc.).
|
|
-- While it comes with a default configuration, the function is designed to be flexible,
|
|
-- allowing for customization to meet specific needs.
|
|
DROP FUNCTION IF EXISTS nanoid(int, text, float);
|
|
CREATE OR REPLACE FUNCTION nanoid(
|
|
size int DEFAULT 24, -- The number of symbols in the NanoId String. Must be greater than 0.
|
|
alphabet text DEFAULT '_-0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', -- The symbols used in the NanoId String. Must contain between 1 and 255 symbols.
|
|
additionalBytesFactor float DEFAULT 1.6 -- The additional bytes factor used for calculating the step size. Must be equal or greater then 1.
|
|
)
|
|
RETURNS text -- A randomly generated NanoId String
|
|
LANGUAGE plpgsql
|
|
VOLATILE
|
|
LEAKPROOF
|
|
PARALLEL SAFE
|
|
AS
|
|
$$
|
|
DECLARE
|
|
alphabetArray text[];
|
|
alphabetLength int := 64;
|
|
mask int := 63;
|
|
step int := 34;
|
|
BEGIN
|
|
IF size IS NULL OR size < 1 THEN
|
|
RAISE EXCEPTION 'The size must be defined and greater than 0!';
|
|
END IF;
|
|
|
|
IF alphabet IS NULL OR length(alphabet) = 0 OR length(alphabet) > 255 THEN
|
|
RAISE EXCEPTION 'The alphabet can''t be undefined, zero or bigger than 255 symbols!';
|
|
END IF;
|
|
|
|
IF additionalBytesFactor IS NULL OR additionalBytesFactor < 1 THEN
|
|
RAISE EXCEPTION 'The additional bytes factor can''t be less than 1!';
|
|
END IF;
|
|
|
|
alphabetArray := regexp_split_to_array(alphabet, '');
|
|
alphabetLength := array_length(alphabetArray, 1);
|
|
mask := (2 << cast(floor(log(alphabetLength - 1) / log(2)) as int)) - 1;
|
|
step := cast(ceil(additionalBytesFactor * mask * size / alphabetLength) AS int);
|
|
|
|
IF step > 1024 THEN
|
|
step := 1024; -- The step size % can''t be bigger then 1024!
|
|
END IF;
|
|
|
|
RETURN nanoid_optimized(size, alphabet, mask, step);
|
|
END
|
|
$$;
|
|
|
|
-- Generates an optimized random string of a specified size using the given alphabet, mask, and step.
|
|
-- This optimized version is designed for higher performance and lower memory overhead.
|
|
-- No checks are performed! Use it only if you really know what you are doing.
|
|
DROP FUNCTION IF EXISTS nanoid_optimized(int, text, int, int);
|
|
CREATE OR REPLACE FUNCTION nanoid_optimized(
|
|
size int, -- The desired length of the generated string.
|
|
alphabet text, -- The set of characters to choose from for generating the string.
|
|
mask int, -- The mask used for mapping random bytes to alphabet indices. Should be `(2^n) - 1` where `n` is a power of 2 less than or equal to the alphabet size.
|
|
step int -- The number of random bytes to generate in each iteration. A larger value may speed up the function but increase memory usage.
|
|
)
|
|
RETURNS text -- A randomly generated NanoId String
|
|
LANGUAGE plpgsql
|
|
VOLATILE
|
|
LEAKPROOF
|
|
PARALLEL SAFE
|
|
AS
|
|
$$
|
|
DECLARE
|
|
idBuilder text := '';
|
|
counter int := 0;
|
|
bytes bytea;
|
|
alphabetIndex int;
|
|
alphabetArray text[];
|
|
alphabetLength int := 64;
|
|
BEGIN
|
|
alphabetArray := regexp_split_to_array(alphabet, '');
|
|
alphabetLength := array_length(alphabetArray, 1);
|
|
|
|
LOOP
|
|
bytes := gen_random_bytes(step);
|
|
FOR counter IN 0..step - 1
|
|
LOOP
|
|
alphabetIndex := (get_byte(bytes, counter) & mask) + 1;
|
|
IF alphabetIndex <= alphabetLength THEN
|
|
idBuilder := idBuilder || alphabetArray[alphabetIndex];
|
|
IF length(idBuilder) = size THEN
|
|
RETURN idBuilder;
|
|
END IF;
|
|
END IF;
|
|
END LOOP;
|
|
END LOOP;
|
|
END
|
|
$$;
|