Skip to content

Instantly share code, notes, and snippets.

@jperl

jperl/create_function_plv8_cuid.sql

Forked from notakaos/create_function_plv8_cuid.sql
Created January 22, 2021 01:21
Show Gist options
  • Save jperl/d6835cbc1698771cf8f5c5c0d468948a to your computer and use it in GitHub Desktop.
Save jperl/d6835cbc1698771cf8f5c5c0d468948a to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @notakaos notakaos revised this gist Oct 29, 2019. 1 changed file with 57 additions and 45 deletions.
    102 changes: 57 additions & 45 deletions create_function_plv8_cuid.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -13,54 +13,65 @@ create extension if not exists "pgcrypto";
    -- initialize
    CREATE OR REPLACE FUNCTION plv8_init() RETURNS void AS $$

    plv8.global = {
    c: 0,
    blockSize: 4,
    base: 36,
    discreteValues: Math.pow(this.base, this.blockSize),

    pad(num, size) {
    const s = '000000000' + num;
    return s.substr(s.length - size);
    },

    fingerprint() {
    const padding = 2;
    const pid = plv8.execute(`select pg_backend_pid() as pid`)[0].pid;
    const pidWithPadding = this.pad(pid.toString(36, padding));
    const hostname = plv8.execute(`select inet_server_addr() as hostname`)[0].hostname;
    const length = hostname.length;
    const hostId =
    this.pad(
    hostname
    .split('')
    .reduce((prev, char) => +prev + char.charCodeAt(0), +length + 36)
    .toString(36)
    , padding);
    return pid + hostId;
    },

    getRandomValue() {
    const lim = Math.pow(2, 32) - 1;
    const len = 4;
    const num = plv8.execute(`select ('x' || right(public.gen_random_bytes($1)::text, 8))::bit(32)::int as num`, [len])[0].num;
    return Math.abs(num / lim);
    },

    randomBlock() {
    return pad((getRandomValue() * this.discreteValues << 0).toString(this.base), this.blockSize);
    },

    safeCounter() {
    this.c = this.c < discreteValues ? this.c : 0;
    this.c++;
    return this.c - 1;
    }
    const blockSize = 4;
    const base = 36;
    const discreteValues = Math.pow(base, blockSize);
    let c = 0;

    function pad(num, size) {
    const s = '000000000' + num;
    return s.substring(s.length - size);
    }

    function fingerprint() {
    const padding = 2;
    const pid = plv8.execute(`select pg_backend_pid() as pid`)[0].pid;
    const pidWithPadding = pad(pid.toString(36, padding));
    const hostname = plv8.execute(`select inet_server_addr() as hostname`)[0].hostname;
    const length = hostname.length;
    const hostId =
    pad(
    hostname
    .split('')
    .reduce((prev, char) => +prev + char.charCodeAt(0), +length + 36)
    .toString(36)
    , padding);
    return pid + hostId;
    }

    function getRandomValue() {
    const lim = Math.pow(2, 32) - 1;
    const len = 4;
    const num = plv8.execute(`select ('x' || right(public.gen_random_bytes($1)::text, 8))::bit(32)::int as num`, [len])[0].num;
    return Math.abs(num / lim);
    }

    function randomBlock() {
    return pad((getRandomValue() * discreteValues << 0).toString(base), blockSize);
    }

    function safeCounter() {
    c = c < discreteValues ? c : 0;
    c++;
    return c - 1;
    }

    plv8.global = {
    c,
    blockSize,
    base,
    discreteValues,

    pad,
    fingerprint,
    getRandomValue,
    randomBlock,
    safeCounter
    };

    $$ LANGUAGE plv8 STRICT;

    -- SET plv8.start_proc = 'plv8_init';
    -- RESET plv8.start_proc
    alter database mydb set plv8.start_proc to plv8_init;

    CREATE OR REPLACE FUNCTION cuid() RETURNS text AS $$
    @@ -72,5 +83,6 @@ CREATE OR REPLACE FUNCTION cuid() RETURNS text AS $$
    const print = fingerprint();
    const random = randomBlock() + randomBlock();

    // return `letter: ${letter}, timestamp: ${timestamp}, counter: ${counter}, print: ${print}, random: ${random}`;
    return letter + timestamp + counter + print + random;
    $$ LANGUAGE plv8 STRICT;
    $$ LANGUAGE plv8 STRICT;
  2. @notakaos notakaos revised this gist Oct 29, 2019. 2 changed files with 8 additions and 8 deletions.
    4 changes: 2 additions & 2 deletions create_function_plv8_cuid.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -63,7 +63,7 @@ $$ LANGUAGE plv8 STRICT;
    -- SET plv8.start_proc = 'plv8_init';
    alter database mydb set plv8.start_proc to plv8_init;

    CREATE OR REPLACE FUNCTION plv8_cuid() RETURNS text AS $$
    CREATE OR REPLACE FUNCTION cuid() RETURNS text AS $$
    const { base, blockSize, safeCounter, fingerprint, randomBlock, pad } = plv8.global;

    const letter = 'c';
    @@ -73,4 +73,4 @@ CREATE OR REPLACE FUNCTION plv8_cuid() RETURNS text AS $$
    const random = randomBlock() + randomBlock();

    return letter + timestamp + counter + print + random;
    $$ LANGUAGE plv8 STRICT;
    $$ LANGUAGE plv8 STRICT;
    12 changes: 6 additions & 6 deletions log.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,17 +1,17 @@
    mydb=# select plv8_cuid();
    plv8_cuid
    mydb=# select cuid();
    cuid
    ----------------------------
    ck2ag1r6h00ln320f2ejrthj1p
    (1 row)

    mydb=# select plv8_cuid();
    plv8_cuid
    mydb=# select cuid();
    cuid
    ----------------------------
    ck2ag1rkq00lo320f2g3ro0o7o
    (1 row)

    mydb=# select plv8_cuid();
    plv8_cuid
    mydb=# select cuid();
    cuid
    ----------------------------
    ck2ag1rsg00lp320f2f32ccfi3
    (1 row)
  3. @notakaos notakaos revised this gist Oct 29, 2019. 1 changed file with 11 additions and 11 deletions.
    22 changes: 11 additions & 11 deletions create_function_plv8_cuid.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -20,17 +20,17 @@ CREATE OR REPLACE FUNCTION plv8_init() RETURNS void AS $$
    discreteValues: Math.pow(this.base, this.blockSize),

    pad(num, size) {
    var s = '000000000' + num;
    const s = '000000000' + num;
    return s.substr(s.length - size);
    },

    fingerprint() {
    var padding = 2;
    var pid = plv8.execute(`select pg_backend_pid() as pid`)[0].pid;
    var pidWithPadding = this.pad(pid.toString(36, padding));
    var hostname = plv8.execute(`select inet_server_addr() as hostname`)[0].hostname;
    var length = hostname.length;
    var hostId =
    const padding = 2;
    const pid = plv8.execute(`select pg_backend_pid() as pid`)[0].pid;
    const pidWithPadding = this.pad(pid.toString(36, padding));
    const hostname = plv8.execute(`select inet_server_addr() as hostname`)[0].hostname;
    const length = hostname.length;
    const hostId =
    this.pad(
    hostname
    .split('')
    @@ -41,9 +41,9 @@ CREATE OR REPLACE FUNCTION plv8_init() RETURNS void AS $$
    },

    getRandomValue() {
    var lim = Math.pow(2, 32) - 1;
    var len = 4;
    var num = plv8.execute(`select ('x' || right(public.gen_random_bytes($1)::text, 8))::bit(32)::int as num`, [len])[0].num;
    const lim = Math.pow(2, 32) - 1;
    const len = 4;
    const num = plv8.execute(`select ('x' || right(public.gen_random_bytes($1)::text, 8))::bit(32)::int as num`, [len])[0].num;
    return Math.abs(num / lim);
    },

    @@ -56,7 +56,7 @@ CREATE OR REPLACE FUNCTION plv8_init() RETURNS void AS $$
    this.c++;
    return this.c - 1;
    }
    };
    }

    $$ LANGUAGE plv8 STRICT;

  4. @notakaos notakaos revised this gist Oct 29, 2019. 1 changed file with 50 additions and 45 deletions.
    95 changes: 50 additions & 45 deletions create_function_plv8_cuid.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -10,62 +10,67 @@ create extension if not exists "pgcrypto";
    -- Connect a database
    \c mydb

    -- for initialize
    -- initialize
    CREATE OR REPLACE FUNCTION plv8_init() RETURNS void AS $$

    pad = function pad(num, size) {
    var s = '000000000' + num;
    return s.substr(s.length - size);
    }
    plv8.global = {
    c: 0,
    blockSize: 4,
    base: 36,
    discreteValues: Math.pow(this.base, this.blockSize),

    fingerprint = function fingerprint() {
    var padding = 2;
    var pid = plv8.execute(`select pg_backend_pid() as pid`)[0].pid;
    var pidWithPadding = pad(pid.toString(36, padding));
    var hostname = plv8.execute(`select inet_server_addr() as hostname`)[0].hostname;
    var length = hostname.length;
    var hostId =
    pad(
    hostname
    .split('')
    .reduce((prev, char) => +prev + char.charCodeAt(0), +length + 36)
    .toString(36)
    , padding);
    return pid + hostId;
    }
    pad(num, size) {
    var s = '000000000' + num;
    return s.substr(s.length - size);
    },

    getRandomValue = function getRandomValue() {
    var lim = Math.pow(2, 32) - 1;
    var len = 4;
    var num = plv8.execute(`select ('x' || right(public.gen_random_bytes($1)::text, 8))::bit(32)::int as num`, [len])[0].num;
    return Math.abs(num / lim);
    }
    fingerprint() {
    var padding = 2;
    var pid = plv8.execute(`select pg_backend_pid() as pid`)[0].pid;
    var pidWithPadding = this.pad(pid.toString(36, padding));
    var hostname = plv8.execute(`select inet_server_addr() as hostname`)[0].hostname;
    var length = hostname.length;
    var hostId =
    this.pad(
    hostname
    .split('')
    .reduce((prev, char) => +prev + char.charCodeAt(0), +length + 36)
    .toString(36)
    , padding);
    return pid + hostId;
    },

    c = 0;
    blockSize = 4;
    base = 36;
    discreteValues = Math.pow(base, blockSize);
    getRandomValue() {
    var lim = Math.pow(2, 32) - 1;
    var len = 4;
    var num = plv8.execute(`select ('x' || right(public.gen_random_bytes($1)::text, 8))::bit(32)::int as num`, [len])[0].num;
    return Math.abs(num / lim);
    },

    randomBlock = function randomBlock() {
    return pad((getRandomValue() * discreteValues << 0).toString(base), blockSize);
    }
    randomBlock() {
    return pad((getRandomValue() * this.discreteValues << 0).toString(this.base), this.blockSize);
    },

    safeCounter = function safeCounter() {
    c = c < discreteValues ? c : 0;
    c++;
    return c - 1;
    }
    safeCounter() {
    this.c = this.c < discreteValues ? this.c : 0;
    this.c++;
    return this.c - 1;
    }
    };

    $$ LANGUAGE plv8 STRICT;


    -- SET plv8.start_proc = 'plv8_init';
    alter database mydb set plv8.start_proc to plv8_init;

    CREATE OR REPLACE FUNCTION plv8_cuid() RETURNS text AS $$
    var letter = 'c';
    var timestamp = (new Date().getTime()).toString(base);
    var counter = pad(safeCounter().toString(base), blockSize);
    var print = fingerprint();
    var random = randomBlock() + randomBlock();
    const { base, blockSize, safeCounter, fingerprint, randomBlock, pad } = plv8.global;

    const letter = 'c';
    const timestamp = (new Date().getTime()).toString(base);
    const counter = pad(safeCounter().toString(base), blockSize);
    const print = fingerprint();
    const random = randomBlock() + randomBlock();

    return letter + timestamp + counter + print + random;
    $$ LANGUAGE plv8 STRICT;
    $$ LANGUAGE plv8 STRICT;
  5. @notakaos notakaos revised this gist Oct 29, 2019. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions create_function_plv8_cuid.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,9 +1,9 @@
    -- original code: https://github.com/ericelliott/cuid

    -- Add "plv8" extension
    -- Add the "plv8" extension
    create extension if not exists "plv8";

    -- Add "pgcrypto" extension
    -- Add the "pgcrypto" extension
    create extension if not exists "pgcrypto";
    \dx

  6. @notakaos notakaos revised this gist Oct 29, 2019. 1 changed file with 3 additions and 0 deletions.
    3 changes: 3 additions & 0 deletions create_function_plv8_cuid.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -2,6 +2,9 @@

    -- Add "plv8" extension
    create extension if not exists "plv8";

    -- Add "pgcrypto" extension
    create extension if not exists "pgcrypto";
    \dx

    -- Connect a database
  7. @notakaos notakaos created this gist Oct 28, 2019.
    68 changes: 68 additions & 0 deletions create_function_plv8_cuid.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,68 @@
    -- original code: https://github.com/ericelliott/cuid

    -- Add "plv8" extension
    create extension if not exists "plv8";
    \dx

    -- Connect a database
    \c mydb

    -- for initialize
    CREATE OR REPLACE FUNCTION plv8_init() RETURNS void AS $$

    pad = function pad(num, size) {
    var s = '000000000' + num;
    return s.substr(s.length - size);
    }

    fingerprint = function fingerprint() {
    var padding = 2;
    var pid = plv8.execute(`select pg_backend_pid() as pid`)[0].pid;
    var pidWithPadding = pad(pid.toString(36, padding));
    var hostname = plv8.execute(`select inet_server_addr() as hostname`)[0].hostname;
    var length = hostname.length;
    var hostId =
    pad(
    hostname
    .split('')
    .reduce((prev, char) => +prev + char.charCodeAt(0), +length + 36)
    .toString(36)
    , padding);
    return pid + hostId;
    }

    getRandomValue = function getRandomValue() {
    var lim = Math.pow(2, 32) - 1;
    var len = 4;
    var num = plv8.execute(`select ('x' || right(public.gen_random_bytes($1)::text, 8))::bit(32)::int as num`, [len])[0].num;
    return Math.abs(num / lim);
    }

    c = 0;
    blockSize = 4;
    base = 36;
    discreteValues = Math.pow(base, blockSize);

    randomBlock = function randomBlock() {
    return pad((getRandomValue() * discreteValues << 0).toString(base), blockSize);
    }

    safeCounter = function safeCounter() {
    c = c < discreteValues ? c : 0;
    c++;
    return c - 1;
    }

    $$ LANGUAGE plv8 STRICT;

    alter database mydb set plv8.start_proc to plv8_init;

    CREATE OR REPLACE FUNCTION plv8_cuid() RETURNS text AS $$
    var letter = 'c';
    var timestamp = (new Date().getTime()).toString(base);
    var counter = pad(safeCounter().toString(base), blockSize);
    var print = fingerprint();
    var random = randomBlock() + randomBlock();

    return letter + timestamp + counter + print + random;
    $$ LANGUAGE plv8 STRICT;
    17 changes: 17 additions & 0 deletions log.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,17 @@
    mydb=# select plv8_cuid();
    plv8_cuid
    ----------------------------
    ck2ag1r6h00ln320f2ejrthj1p
    (1 row)

    mydb=# select plv8_cuid();
    plv8_cuid
    ----------------------------
    ck2ag1rkq00lo320f2g3ro0o7o
    (1 row)

    mydb=# select plv8_cuid();
    plv8_cuid
    ----------------------------
    ck2ag1rsg00lp320f2f32ccfi3
    (1 row)