# Fabricate custom SQLite functions

Fabricate uses [SQLite](https://www.sqlite.org/). Whenever you provide a SQL expression, you can use any expression that SQLite supports.

Fabricate also provides some additional functions that you can use to generate data.

## Adding typos to a string

To add typos to a string, use the `fab_add_typos` function:

`fab_add_typos(input, unit, value, operation)`

Where:

* `input` is the string to add the typos to.
* `unit` is the type of unit to use to configure how the typos are added.\
  \
  The allowed values are `distance` or `rate`. The default value is `rate`.\
  \
  `distance` indicates to provide a maximum number of characters to insert, delete, or substitute.\
  \
  `rate` indicates to provide a probability of a typo occurring in a given character. This simulates human typing errors by randomly inserting, deleting, or substituting characters.
* `value` is the distance or rate value.
  * When `unit` is `distance`, `value` is an integer. The default value is 1.
  * When `unit` is `rate`, `value` is a float. The default value is 0.1, which indicates a 10% probability.
* `operation` is an optional parameter to determine how the typos are created. If you do not provide the operation parameter, then Fabricate uses all of the operations. Use `|` to separate multiple operations.\
  \
  The available operations are:
  * `insert` - To create the typos, Fabricate inserts characters. For example, changes "chart" to "choart".
  * `delete` - To create the typos, Fabricate deletes characters. For example, changes "chart" to "chrt".
  * `replace` - To create the typos, Fabricate replaces characters. For example, changes "chart" to "cvart".

For example:

<table data-header-hidden><thead><tr><th valign="top"></th><th valign="top"></th></tr></thead><tbody><tr><td valign="top"><code>fab_add_typos(product_description)</code></td><td valign="top">Returns the <code>product_description</code> with typos at the default rate of 10%.<br><br>For each character, there is a 10% probability that the character is modified.<br><br>Fabricate uses all of the available operations.</td></tr><tr><td valign="top"><code>fab_add_typos(product_description, 'rate', 0.05)</code></td><td valign="top">Returns the <code>product_description</code> with typos.<br><br>For each character, there is a 5% probability that the character is modified.<br><br>Fabricate uses all of the available operations.</td></tr><tr><td valign="top"><code>fab_add_typos(product_description, 'distance', 3)</code></td><td valign="top">Returns the <code>product_description</code> with typos.<br><br>The result has a Levenshtein distance of 3 from the original value.<br><br>Fabricate uses all of the available operations.</td></tr><tr><td valign="top"><code>fab_add_typos(product_description, 'distance', 3, 'insert|delete')</code></td><td valign="top">Returns the <code>product_description</code> with typos.<br><br>The result has a Levenshtein distance of 3 from the original value.<br><br>To create the typos, Fabricate only inserts and deletes characters. It does not replace characters.</td></tr></tbody></table>

## Calling a Fabricate generator

To call a Fabricate generator, use the `fab_gen` function:

`fab_gen(generator, ...params)`

Where:

* `generator` is the name of the generator.
* `params` are the generator parameters.

The `fab_gen` function currently supports the Number generator. The following examples show how to generate numbers using the different distribution options.

### Auto-increment sequence

To generate a sequence of numbers, starting at a given number and increasing by a specified type value:

`fab_gen('number', 'auto_increment', start?: integer, step?: integer)`

For example, to generate a sequence that starts at 1 and increments by 1:

`fab_gen('number', 'auto_increment', 1, 1)`

### Uniform distribution

To generate a random number:

* Between given minimum and maximum values
* With the specified number of decimal places

`fab_gen('number', 'uniform', min: integer, max: integer, decimals?: integer)`

For example, to generate a random number:

* Between 1 and 10
* With 2 decimal places

`fab_gen('number', 'uniform', 1, 10, 2)`

### Normal distribution

To generate a random number from a normal distribution:

* With the given mean and standard deviation
* Between given minimum and maximum values
* With the specified number of decimal places

`fab_gen('number', 'normal', mean: float, std_dev: float, min?: float, max?: float, decimals?: integer)`

For example, to generate a normal distribution:

* With a mean of 5 and a standard deviation of 1
* Between 1 and 10
* With 2 decimal places

`fab_gen('number', 'normal', 5, 1, 1, 10, 2)`

### Binomial distribution

To generate a random number from a binomial distribution:

* For a specified number of tests
* With the given probability of success

`fab_gen('number', 'binomial', trials, success_probability)`

For example, to generate a binomial distribution:

* For 50 tests
* With 0.5 probability of success

`fab_gen('number', 'binomial', 50, 0.5)`

### Geometric distribution

To generate a random number from a geometric distribution:

* With the given probability of success
* Between given minimum and maximum values

`fab_gen('number', 'geometric', p: float, min?: integer, max?: integer)`

For example, to generate a geometric distribution:

* With 0.3 probability of success
* Between 1 and 10

`fab_gen('number', 'geometric', 0.3, 1, 10)`

### Exponential distribution

Generates a random number from an exponential distribution:

* With the given mean
* Between given minimum and maximum values
* With the specified number of decimal places

`fab_gen('number', 'exponential', mean: float, min?: float, max?: float, decimals?: integer)`

For example, to generate an exponential distribution:

* With a mean of 1
* Between 1 and 10
* With 2 decimal places

`fab_gen('number', 'exponential', 1, 1, 10, 2)`

### Poisson distribution

To generate a random number from a Poisson distribution:

* With the given mean
* Between given minimum and maximum values
* With the specified number of decimal places

`fab_gen('number', 'poisson', mean: float, min?: integer, max?: integer, decimals?: integer)`

For example, to generate a Poisson distribution:

* With a mean of 5
* Between 1 and 10

`fab_gen('number', 'poisson', 5, 1, 10)`

## Getting and appending Luhn check digits

### Returning the Luhn check digit

To return the Luhn check digit for an input string.

`fab_luhn_check_digit(input)`

For example, to return the Luhn check digit for the string '123456':

`fab_luhn_check_digit('123456')`

### Appending the Luhn check digit

To append the Luhn check digit to an input string.

`fab_append_luhn_check_digit(input)`

For example, to return the string '123456' with the Luhn check digit appended:

`fab_append_luhn_check_digit('123456')`

## Generating a random integer

To generate a random 64-bit integer, optionally between a specified minimum and maximum value:

`fab_random(min?, max?)`

You can use this to randomize the order in select statements.

While SQLite provides a built-in `random()` function, it only produces a single random value. `The fab_random()` function produces a different random value for each row.&#x20;

For example, for each customer, to pull a random `order_id` from the `Orders` table:

{% code overflow="wrap" %}
```sql
update Customers
set order_id = Orders.order_id from ( 
  select Orders.order_id, customer_id, ROW_NUMBER() OVER (partition by customer_id order by fab_random()) as row_num 
  from Orders ) 
as Orders where Orders.customer_id = Customers.customer_id and row_num=1
```
{% endcode %}

To generate a random number between 1 and 100, and use that value to set the probability for the following conditions:

* 20% chance of returning null
* 80% chance of returning the `user_id`

{% code overflow="wrap" %}
```sql
with random_value as ( select fab_random(0,100) as value ) 
  select 
    case 
      when random_value.value < 20 then null 
      else user_id 
    end 
  from random_value
```
{% endcode %}

## Getting the hexadecimal and hash versions of a string

### Getting the hexadecimal representation

To return the hexadecimal representation of an input string:

`hex(input)`

For example, to return the hexadecimal representation of the string 'hello':

`hex('hello')`

### Getting the MDB hash of a string

To return the MD5 hash of an input string as a hexadecimal string:

`md5_hex(input)`

For example, to return the MD5 hash of the string 'hello':

`md5_hex('hello')`

### Getting the SHA-1 hash of a string

To return the SHA-1 hash of an input string as a hexadecimal string:

`sha1_hex(input)`

For example, to return the SHA-1 hash of the string 'hello':

`sha1_hex('hello')`

### Getting the SHA-256 hash of a string

To return the SHA-256 hash of an input string as a hexadecimal string:

`ha256_hex(input)`

For example, to return the SHA-256 hash of the string 'hello':

`sha256_hex('hello')`
