Fabricate custom SQLite functions

Fabricate uses SQLite. 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)

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.

For example:

fab_add_typos(product_description)	Returns the product_description with typos at the default rate of 10%. For each character, there is a 10% probability that the character is modified.
fab_add_typos(product_description, 'rate', 0.05)	Returns the product_description with typos. For each character, there is a 5% probability that the character is modified.
fab_add_typos(product_description, 'distance', 3)	Returns the product_description with typos. The result has a Levenshtein distance of 3 from the original value.

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.

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

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

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

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

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')