# pgcrockford - Crockford Base32 encoding for PostgreSQL unsigned integers

The pgcrockford extension provides Base 32 encoded integers as a
PostgreSQL base type. From [Douglas Crockford's
requirements][crockford] for the specification, the encoding should

> - Be human readable and machine readable.
> - Be compact. Humans have difficulty in manipulating long strings of arbitrary symbols.
> - Be error resistant. Entering the symbols must not require keyboarding gymnastics.
> - Be pronounceable. Humans should be able to accurately transmit the symbols to other humans using a telephone.

[crockford]: https://www.crockford.com/base32.html

The motivation for `pgcrockford` is to provide a more humane display of
values used for surrogate keys while preserving the efficiency of an
integer implementation: `crockford` values are represented as
alpha-numeric strings while using unsigned integers for their
implementation.

## Releases and Dependencies

Latest release: 0.8.34

Tested with PostgreSQL 11.

## Installation

    make
    make install
    make installcheck

```sql
CREATE SCHEMA crockford;
CREATE EXTENSION crockford WITH SCHEMA crockford;
```

> Note: The extension doesn't require a dedicated schema, but it's
> good practice to utilize schemas to namespace modules.


## Usage

The `pgcrockford` extension provides 3 base types: 2-byte, 4-byte, and
8-byte implementations.

 - `crockford2` (2-byte)
 - `crockford4` (4-byte)
 - `crockford8` (8-byte)

```sql
-- interpret '10' as a crockford literal
SELECT '10'::crockford.crockford4;
-- 10

-- cast integer 10 to crockford
SELECT 10::crockford.crockford4;
-- A
```

```sql
CREATE TABLE store.widgets (
  widget_id crockford.crockford4 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  widget_name text UNIQUE NOT NULL
);

INSERT INTO store.widgets (widget_name)
  SELECT 'widget' || n FROM generate_series(1,64) AS _ (n);

SELECT * FROM store.widgets ORDER BY widget_id DESC LIMIT 10;
/*
 widget_id | widget_name
-----------+-------------
 20        | widget64
 1Z        | widget63
 1Y        | widget62
 1X        | widget61
 1W        | widget60
 1V        | widget59
 1T        | widget58
 1S        | widget57
 1R        | widget56
 1Q        | widget55
(10 rows)
*/
```

### Getting funky with representation

One of the drawbacks of using sequences as surrogate keys is that
there's nothing to distinguish an id value of one table from an id
value of another. If both `store.widget` and `store.locations` have
`id` surrogate key columns, is `1232` a `widget_id` or a
`location_id`? Setting aside debates of whether one should use
surrogate keys or column naming conventions, you can leverage the
alpha-numeric representation of the `crockford` types to encode the
type in the id value.

```sql
CREATE FUNCTION store.next_widget_id()
RETURNS crockford.crockford4
LANGUAGE sql AS
$body$
  SELECT 'W00000' + nextval('store.widgets_widget_id_seq');
$body$;

CREATE FUNCTION store.next_location_id()
RETURNS crockford.crockford4
LANGUAGE sql AS
$body$
  SELECT 'L00000' + nextval('store.locations_location_id_seq');
$body$;
```

These functions will provide 33,554,431 unique values each
(`W00001`..`WZZZZZ` and `L00001`..`LZZZZZ` respectively) before
encroaching on the encoded "type".

#### Operators and search_path

If you load crockford into a schema that's not on the `search_path`,
the crockford-specific operators won't be available without specifying
the schema explicitly. This is [normal behavior for
PostgreSQL][operator-schema], but may be suprising if you haven't seen
it before.

```sql

SELECT 'A'::crockford.crockford4 + 1;
-- 11

-- specify the schema explicitly using OPERATOR
SELECT 'A'::crockford.crockford4 OPERATOR(crockford.+) 1;
-- B

SET search_path TO crockford;

SELECT 'A'::crockford4 + 1;
-- B
```

If you're using crockford a lot, you'll likely want to either
explicitly set the `search_path` to include the crockford extension
schema or set the [default `search_path` for your
database][alter-database]. For example,

```sql
ALTER ROLE grzm SET search_path TO 'crockford';
```
You can also [set `search_path` per role][alter-role]:


```sql
ALTER DATABASE crockford_test SET search_path to 'crockford';
```

The fine PostgreSQL manual includes more discussion of [schema
usage][schema-usage].

[operator-schema]: https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
[schema-usage]: https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATTERNS
[alter-database]: https://www.postgresql.org/docs/current/sql-alterdatabase.html
[alter-role]: https://www.postgresql.org/docs/current/sql-alterrole.html

## Thanks!

The `pgcrockford` extension takes inspiration from Peter Eisentraut's
[`pguint`][pguint] library, both for using unisigned integers as the
underlying implementation and also for generating some of the code
that implements it (see [generate.py](generate.py)).

I also found Manuel Kniep's series (parts [1][adjust-ext-1],
[2][adjust-ext-2], and [3][adjust-ext-3], [4][adjust-ext-4], and
[5][adjust-ext-5]) on writing PostgreSQL Extensions useful as
well--and not only because it focused on writing
[`pg-base36`][pg-base36].

[pguint]: https://github.com/petere/pguint
[adjust-ext-1]: http://big-elephants.com/2015-10/writing-postgres-extensions-part-i/
[adjust-ext-2]: http://big-elephants.com/2015-10/writing-postgres-extensions-part-ii/
[adjust-ext-3]: http://big-elephants.com/2015-10/writing-postgres-extensions-part-iii/
[adjust-ext-4]: http://big-elephants.com/2015-11/writing-postgres-extensions-part-iv/
[adjust-ext-5]: http://big-elephants.com/2015-11/writing-postgres-extensions-part-v/
[pg-base36]: https://github.com/adjust/pg-base36


## Licence

© 2019 Michael Glaesemann

Released under the PostgreSQL License. See LICENSE file for details.