hashids-tsql

Hashids TSQL Generator

Generates TSQL hash encode functions compatible with hashids.org implementations.

Hashids

A small set of TSQL functions to generate YouTube-like hashes from one or many numbers. Use hashids when you do not want to expose your database ids to the user.

http://www.hashids.org/

What is it?

hashids (Hash ID's) creates short, unique, decryptable hashes from unsigned integers.

This project is a port to TSQL of the other projects found via http://www.hashids.org/. The .NET and Javascript versions of Hashids are the primary reference projects for this port.

The included hashids-tsql generator creates a custom set of TSQL functions to encode values with your chosen salt and other options.

Quick Start

The command below will generate a set of TSQL encode functions and test objects into the test.sql file. See the Generator Usage section below. (NOTE: npm install -g will actually work when I figure out how to release an npm package that publishes a global command/bin script!)

npm install -g hashids-tsql

hashids-tsql -t test.sql

Uses

The primary use case can be seen in the ComputedTest table where the table's HashId uses encode1 to hash the Id column once as part of the atomic INSERT of a record.

If every table were to use the same encode1 function, then any row in any table with Id = 1 would have the same hash. Therefore, encode2 is provided, which takes 2 numbers. So, TableX can call encode2(1, [Id]) and TableY can call encode2(2, [Id]) and so on...guaranteeing that the hash for each table's [Id] column don't collide.

Two more TSQL encode functions exist to hash a list of numbers. They are encodeList and encodeSplit.

Generator Usage

Usage: hashids-tsql [options] [file or directory/ path]

Options:

  -h, --help               output usage information
  -V, --version            output the version number
  -d, --database [name]    Database name. [HashidsTsql]
  -m, --schema [name]      Database schema. [hashids]
  -a, --ascii              Generate ASCII/varchar compatible function(s).
  -b, --bigint             Generate BIGINT compatible function(s).
  -e, --encodeOnly         Generate encode function(s) only.
  -s, --salt [value]       Salt. [random]
  -n, --minHashLength [n]  Minimum hash length. [0]
  -l, --alphabet [value]   Alphabet. [a-z,A-Z,1-9,0]
  -x, --fileExt [value]    Extension for output files. [sql]
  -t, --test               Generate test procedureds and tables.

Status

The SQL functions generated by hashids-tsql can currently encode numbers, but not decode them. The mssql database project (called HashidsTsql) contains a full set of pre-generated functions to test with. Also, see TODOs below.

TSQL Functions

TSQL does not have function overloading, so the single encode function that is common in other hashids.org libraries is instead represented here as a set of encode functions with slight variations in name, declaration and possibly even return value.

The basic forms of encode for TSQL are:

• encode1(int) string
• encode2(int, int) string
• encodeList(table) string
• encodeSplit(string, string) string

In TSQL, the encode functions that take 1 or 2 integers will be much more useful than the one that takes a table because typically, you don't want to construct a table variable just to pass 1 or 2 integers into a function.

There are multiple variations on each basic form, in order to return different int and string types (varchar, nvarchar, int, bigint). encode1A returns a varchar (ASCII) value. encode1B accepts a bigint. encode1BA accepts a bigint and returns a varchar (and so on).

TODO

• Replace No-UTF8-BOM-in-templates.txt note with usage of something like "strip-bom" in app.js
• Create all manner of automated tests, primarily to test against other implementations.
• Create TSQL functions for decoding and integrate them into the hashids-tsql generator.