Random data generator for MySQL and PostgreSQL

Forked from https://github.com/Percona-Lab/mysql_random_data_load

This tool aims to produce a quick working environment to reproduce a query execution behavior in order to optimize it. It is meant for cases where we cannot access real data, only schema and cardinalities.

Based on the table(s) schema and a query, it will generate random data with respect to fields, foreign keys defined in databases, foreign keys infered from the query pattern, (plan: from existing cardinalities and distributions).

Usage

random-data-load run --engine=(mysql|pg) --rows=INT-64 (--query=SELECT ...|--table=table_name) [options...]

Example

Using the following schema,

CREATE TABLE public.orders (
    order_id integer primary key generated always as identity,
    shipping_address text NOT NULL,
    country text,
    zip text NOT NULL,
    currency character varying(3) NOT NULL,
    email character varying(100) NOT NULL
);

CREATE TABLE public.products (
    id varchar(30) primary key,
    product text NOT NULL,
    price numeric NOT NULL,
    material text,
    feature text,
    company text
);

CREATE TABLE public.order_items (
    product_no varchar(30) NOT NULL,
    order_id integer NOT NULL
);

To debug the following query:

select sum(p.price), count(oi.*) from orders o join order_items oi on o.order_id=oi.order_id join products p on p.id = oi.product_no where o.currency='EUR';

An example of usage:

$ time ./random-data-load run --engine=pg --host=127.0.0.1 --user=sbtest --password=sbtest --database=postgres --port=5432 --bulk-size=4500 --rows=500000 --default-relationship=binomial --coin-flip-percent=1  --query="select sum(p.price), count(oi.*) from orders o join order_items oi on o.order_id=oi.order_id join products p on p.id = oi.product_no where o.currency='EUR';" 
Writing orders (337500/500000) rows...
Writing orders (500000/500000) rows...
Writing products (500000/500000) rows...
Writing order_items (499500/500000) rows...

real	0m16,168s
user	0m16,549s
sys	0m1,181s

postgres=# select sum(p.price), count(oi.*) from orders o join order_items oi on o.order_id=oi.order_id join products p on p.id = oi.product_no where o.currency='EUR';
     sum     | count 
-------------+-------
 1595.505421 |  3231
(1 row)

postgres=# select * from products limit 10;
          id          |             product              |  price   | material  |     feature      |            company             
----------------------+----------------------------------+----------+-----------+------------------+--------------------------------
 sfkes5nhpegtt977ae2b | Mighty Desk Lamp Quick           | 0.043675 | carbon    | impact-resistant | PeerJ
 uht6n748y9ghghe7gdqa | Practical Ashtray                | 0.684435 | slate     | plug-and-play    | EMC
 fyyf5kgkdj7d87aa7g2c | Incredible Memory-Enabled Grater | 0.007092 | tungsten  | wrinkle-free     | Outline
 cetyjbc84bgfdrjrdrm2 | Self-Adjusting Alarm             | 0.710173 | limestone | led-backlit      | Wolters Kluwer
 mbk78nvxqqpmc3yeep24 | Steam-Powered Rocking Chair      | 0.235886 | silver    | resistant        | ConnectEDU
 aawjj9ce27q88mm3fysg | Vinyl Bag                        | 0.067065 | iron      | interactive      | The Advisory Board Company
 4fpym2hnm45erv9c5hdw | Artistic Window Blind            | 0.759076 |           | resistant        | IVES Group Inc
 rgxvextkvyz8nhw79btp | Treasure Chest Anti-Slip Quick   | 0.825359 | paper     |                  | Business Monitor International
 t6ng73kmpe7esnjugf66 | Tactical-Revolutionary Cooker    | 0.427905 | composite | rust-proof       | LoopNet
 y35yfc7m2stt6zxh4pqz | Lawn Mower Hemp Express          | 0.181523 |           | energy-efficient | SpaceCurve
(10 rows)

postgres=# select * from orders limit 10;
 order_id |     shipping_address      |  country   |  zip  | currency |            email             
----------+---------------------------+------------+-------+----------+------------------------------
   414763 | 93265 North Rampville     | Belgium    | 17807 | GMD      | mollyhoffman@maxwell.biz
   414764 | 4359 North Summitburgh    | Egypt      | 25582 | VES      | arnoldwilkinson@gislason.org
   414765 | 28909 Ranchmouth          | Mauritania | 32167 | ANG      | kendallgleichner@pena.biz
   414766 | 8214 North Keyton         | Ecuador    | 72284 | AZN      | aaronvillarreal@lambert.info
   414767 | 657 Loafbury              |            | 63499 | BND      | clairedooley@gross.name
   414768 | 826 East Tunnelview       | Réunion    | 20814 | CDF      | ardenhamilton@barnett.org
   414769 | 176 Lake Underpassborough | Gambia     | 81642 | CHF      | adriancummings@knight.org
   414770 | 87086 Rowhaven            | Armenia    | 68902 | MZN      | dexterstanton@payne.com
   414771 | 5421 West Lodgeshire      |            | 54406 | EGP      | ezekielrivera@matthews.io
   414772 | 50778 Lake Unionsside     | Kuwait     | 30627 | GYD      | christaball@cruz.biz
(10 rows)

Options

Common options:

Option	Description
--engine	mysql/pg
--host	Host name/ip
--user	Username
--password	Password
--port	Port number
--quiet	Do not print progress bar
--dry-run	Print queries to the standard output instead of inserting them into the db
--debug	Show some debug information
--pprof	Generate pprof trace at --cpu-prof-path. Also opens port 6060 for pprof go tool
--version	Show version and exit
--rows-per-table	Number of rows to insert per-table. Will have priority over --rows
--bulk-size	Number of rows per INSERT statement (Default: 1000)
--workers	how many workers to spawn. Only the random generation and sampling are parallelized. Insert queries are executed one at a time (Default: 3)
--table	Table to insert to. When using --query, --table will be used to restrict the tables to insert to.
--query	Providing a query will analyze its schema usage, insert recursively into tables, and identify implicit joins
--no-skip-fields	Disable field whitelist system. When using a --query, it will get the list of fields being used as a whitelist in order to generate the minimal sets of fields required, unless --no-skip-fields is being used or any * has been found.
--null-freq	Define how frequent nullable fields should be NULL
--null-freq-map	Define how frequent nullable fields should be NULL for a given column. Will have priority over --null-freq. The format is "--null-freq-map=t1.c1=73;t1.c2=4" to set 73% or 4% of NULL for respective columns
--values-freq-map	Inject arbitrary values at fixed frequencies. The format is "--values-freq-map=t1.c1=val1:0.75,val2:0.23;t1.c2=10:0.99" so that val1 will be on 75% of rows and val2 on 23% for column c1
--stat-file	Scan a column statistics export and reuse its null_frac, most_common_vals and most_common_freqs instead of setting --null-freq-map and --values-freq-map by hand. Use the export-stat subcommand to get the command producing that file
--min-generated-time	Generated timestamps will be after this date. Format is RFC3339. Will default to --max-generated-time - 1 year
--max-generated-time	Generated timestamps will be before this date. Format is RFC3339. Will default to now()

Foreign key sampling options:

Option	Description
--add-fk	Add foreign keys, if they are not explicitely created in the table schema. It can complement the foreign keys guessed from the --query, or be used to manually define foreign keys when using --no-fk-guess too. Format: --add-fk="parent_table.col1[,col2...]=child_table.colx[,coly...][; additional fk ]". Example: --add-fk="customers.id,created_at=purchases.customer_id,created_at;purchases.id=items.purchase_id"
--no-fk-guess	Do not try to guess foreign keys from the --query missing in the schema. When a query is provided, it will analyze the expected JOINs and try to respect dependencies even when foreign keys are not explicitely created in the database objects. This flag will make the tool stick to the constraints defined in the database only, unless you add foreign keys manually with --add-foreign-keys.
--default-relationship	Will define the default foreign-key relationship to apply. Possible values: binomial,sequential. The default relation can be overriden with other parameters --binomial or --sequential
--binomial	Defines a 1-N foreign key relationships using repeated coin flips. Postgres' tablesamples Bernouilli or mysql RAND() < 0.1 (can be tuned with --coin-flip-percent). Format should be "parent_table=child_table". E.g: --binomial="customers=orders;orders=items"
--coin-flip-percent	When used with --binomial, it will set the likeliness of each rows to be sampled or not. 10 would mean each rows have only 10% chance to be selected when sampling a parent table. Using large values will favor hot rows: the coin flips are done with a table full scan, with a limit set at --bulk-size, so with a large percent chance most of the time the first rows will be selected. No effects when used with --sequential (Default: 1)
--sequential	Defines a sequential foreign key links relationships. Format should be "parent_table=child_table". E.g: --sequential="citizens=ssns"
--normal	Defines a 1-N foreign key relationships using box-muller transformation to provide normal distribution. Slow method needing full table scans for each samples.
--normal-stddev	Standard deviation to the normal law. Will default to 1/10 of the table size
--normal-mean	Mean of the normal law. Will default to the middle of the table, --rows/2
--pareto	Defines a 1-N foreign key relationships using zipf (pareto) distribution. Slow method needing full table scans for each samples
--pareto-s	Zipf slope parameter. Must be above 1. Higher value will mean faster decay, so first rows will be hotter
--pareto-v	Must be >=1. Directly map to V, https://pkg.go.dev/math/rand#Zipf.

Example

Continuing the example with orders, products and order_items:

-- how many times products are present in order_items
postgres=# select oi.product_no, count(*) from order_items oi group by 1 order by 2 desc limit 10;
      product_no      | count 
----------------------+-------
 gg476vcr2fa9pdmhazhb |     9
 7vzsn676dzyyyb3b2wv8 |     9
 sny5dzjhjp2zhk6zbxad |     8
 eemd8eng9d8sgk2m2zeg |     8
 4eahk4nur48t8bcmqq35 |     8
 b5cemgse4ybzkbxuqwdf |     8
 7yv82qvg3g5mgpvfggv4 |     8
 h3zhu5kwm2frqkgb3c5p |     8
 3hjg6w6nmrx2z5g66z2d |     8
 akjkd45a7k4h3mcwrsg7 |     8
(10 rows)

-- how many unique products
postgres=# select count(distinct oi.product_no) from order_items oi;        
 count  
--------
 303943
(1 row)


-- how many unique order ids in order_items. 500k is because of --sequential and --rows being equal between tables
postgres=# select count(distinct oi.order_id) from order_items oi;
 count  
--------
 500000
(1 row)

Changing the data distribution with a higher --coin-flip-percent:

postgres=# truncate products, orders, order_items;
TRUNCATE TABLE

./random-data-load run --engine=pg (...) --coin-flip-percent=30 (...)

-- still a similar result
postgres=# select sum(p.price), count(oi.*) from orders o join order_items oi on o.order_id=oi.order_id join products p on p.id = oi.product_no where o.currency='EUR';
     sum     | count 
-------------+-------
 1559.053189 |  3110
(1 row)

-- But the data repartity of product ids is different, some products are more "hot"
postgres=# select oi.product_no, count(*) from order_items oi group by 1 order by 2 desc limit 10;
      product_no      | count 
----------------------+-------
 2cqz6jvnz7avrt59ahgm |    53
 2vf499qtfkd34th5bat2 |    52
 2rnv6yhj47k3m29svggq |    51
 2kqhvjk99c7pftfjqn4n |    50
 2ev2dmtajgh49k9cdupv |    50
 2kph4hmd2w29n2dsmh8r |    50
 284tqufe3psbbyd6r5kb |    50
 29gp22hggwygagdsvx7g |    50
 2ajgrfbe6ww3neg6xc3f |    49
 2afye7ytsxz6afyhr6ku |    49
(10 rows)

-- There's way less diversity of products, ~485k products don't even have 1 order
postgres=# select count(distinct oi.product_no) from order_items oi;
 count 
-------
 15357
(1 row)

-- order ids sampling is still sequential, so identical
postgres=# select count(distinct oi.order_id) from order_items oi;
 count  
--------
 500000
(1 row)

If 15k referenced products isn't diverse enough, we can work with higher --bulk-size. This is because sampling is limited to --bulk-size with a LIMIT BY --bulk-size, so low --bulk-size with high --coin-flip-percent will ultimately lead to the very first sampled rows repeated too often

postgres=# truncate order_items;
TRUNCATE TABLE

-- we'll restrict to just order_items not to re-insert orders or products.
./random-data-load run --engine=pg (...) --coin-flip-percent=30 --bulk-size=30000 --table=order_items (...)


-- more product diversity
postgres=# select count(distinct oi.product_no) from order_items oi;        
 count  
--------
 100395
(1 row)

-- which will mean a lesser "max" usage of a single product. Higher --coin-flip-percent  could force hotter rows again
postgres=# select oi.product_no, count(*) from order_items oi group by 1 order by 2 desc limit 10;
      product_no      | count 
----------------------+-------
 68uzayu85vgbcfy2fand |    14
 2x6pg2wztdeq6gxsj7je |    13
 4dmzwejn2g8kfx5ak774 |    13
 6jy36mxygtyf3yvqph6f |    13
 7yfnr2nsqqeud5d9543w |    13
 4ncnh6nr2km6ddwya7wn |    13
 2h26zpsmsucgg4a2gnrh |    13
 22gkgw4egqemr5ht5fhx |    12
 594p72pt9wjmva3xwnm6 |    12
 48jsvueqqhw9webchje7 |    12
(10 rows)

Foreign keys support

If a field has Foreign Keys constraints, random-data-load will get samples from the referenced tables in order to insert valid values for the field.
To enforce orders, an arbitrary 'ORDER BY 1' is made. This is so that --sequential can create 1-1 relationship, and to better master the eventual distribution of --binomial.

Composites foreign keys are supported. With very low chances to sample rows, we might sample too little. The tool will loop until it sampled enough rows to fill the next bulk insert.

1. sequential relationships will sample with LIMIT and OFFSET:

SELECT <field[, field2]> FROM <referenced schema>.<referenced table> ORDER BY 1 LIMIT <--bulk-size> OFFSET y

This isn't the fastest method but it works for every types and compound primary keys. The value of the current OFFSET is protected by mutex to prevents frequent duplicates.

2. binomial relations will sample differently between postgres and mysql

2.1 For postgres it relies on TABLESAMPLE

SELECT <field[, field2]> FROM <referenced schema>.<referenced table> TABLESAMPLE BERNOUILLI (<--coin-flip-percent>) ORDER BY 1 LIMIT <--bulk-size>

2.2 For mysql, it relies on RAND()

SELECT <field[, field2]> FROM <referenced schema>.<referenced table> WHERE rand() < (<--coin-flip-percent>/100) ORDER BY 1 LIMIT <--bulk-size>

3. Pareto and normal distribution Both methods are implemented using row_number() Postgres uses row_number()

select <fields,..> from (SELECT columns, ROW_NUMBER() OVER (ORDER BY <fields...>) as rownumber FROM table ) f where rownumber IN (x1, x2, ...) and <checking fields not to be null> order by 1 limit <--bulk-size>

While MySQL is still implemented with user variables to retain mysql 5.7 compatibility

select <fields,...> from table, (SELECT @rownumber := 0) f where (@rownumber := @rownumber + 1) IN (x1, x2, ...) and <checking fields not to be null> order by 1 limit <--bulk-size>

3.1 Pareto "pareto" is actually using zipf random number generation. The slope can be tuned with --pareto-s such as higher value will mean faster decay. The other parameter --pareto-v is not documented in its related go stddlib package for now. First rows will be hotter and sampled far more commonly, but it will nonetheless retain a long "tail" over the whole table.

3.2 Normal "normal" is actually implemented using box-muller transformation (reproducing "normal" distribution from 2 uniformly random float numbers between 0.0 and 1.0) It will mostly sample around the --normal-mean based on --normal-stddev, and very few rows on the outlier parts.

Guessing implicit foreign keys from queries

If no foreign keys are explicitely defined in the schema, but the query requires columns to match, random-data-load will infer the foreign keys and insert valid values so that the query returns rows. Can be disabled with --no-fk-guess

An estimation can be made using:

random-data-load query --query="$(cat huge_select.sql)"

Foreign keys are guessed from:

• JOINs with an ON clause, parenthesised or not
• JOINs written implicitely, with the condition in the WHERE clause: FROM x, y WHERE x.a = y.b
• correlated subqueries: WHERE EXISTS (SELECT 1 FROM y WHERE y.a = x.b)
• semi-joins: WHERE x.a IN (SELECT y.b FROM y)

References are followed through subqueries and CTEs down to the real tables they read, so a query joining on a derived result generates data in the underlying tables:

WITH recent AS (SELECT order_id FROM orders WHERE currency = 'EUR')
SELECT count(*) FROM recent r JOIN order_items oi ON r.order_id = oi.order_id;

generates orders and order_items, and the foreign key lands on orders.order_id. The same holds for derived tables, for a CTE reading from an earlier CTE, and through renamings, whether by a column alias or a CTE column list. When a CTE or subquery is a UNION, one foreign key is generated per branch, since the value may come from any of them. A recursive CTE contributes its anchor branch; the self-reference is ignored.

A condition spanning several columns is generated as one composite foreign key rather than one key per column, so that a child row takes all its columns from the same parent row:

FROM purchases p JOIN items i ON p.id = i.purchase_id AND p.created_at = i.created_at

It will not guess a foreign key for:

• conditions other than equality: ON x.a > y.b does not require the values to match
• negated conditions, including NOT IN, which ask for the values to stay apart
• values that cannot be traced back to a column, such as an aggregate or an expression in a subquery's SELECT list. These are reported with a warning naming the condition, since the query will not return rows without them
• JOINs using a USING clause. Write the condition with ON, or declare it with --add-fk
• JOIN conditions using ambiguous columns, without expliciting to what table it belongs. Example FROM x JOIN y ON apple=pear instead of FROM x JOIN y ON x.apple=y.pear

Conditions on either side of an OR are kept as separate single-column keys, never merged into a composite one: only one of them has to hold, so merging would demand more of the data than the query does.

Reusing the data distribution of a real database

Setting --null-freq-map and --values-freq-map by hand means knowing the shape of the production data in the first place. Postgres already measured it: pg_stats holds a null_frac, a most_common_vals and a most_common_freqs for every analyzed column, and those three are exactly what the two options take.

export-stat prints the command that dumps them. It reads nothing but pg_stats, so it is safe to hand over to whoever has access to the database being copied:

random-data-load export-stat --engine=pg --query="select o.total from customers c join orders o on c.id = o.customer_id" --database=shop

# Reads pg_stats and writes nothing. Run it on the database whose data
# distribution you want to reproduce, then pass the file to:
#   random-data-load run --stat-file=pg_stats.json ...
psql -X -q -A -t -d shop -f - > pg_stats.json <<'SQL'
SELECT coalesce(json_agg(s), '[]'::json)
  FROM (SELECT schemaname, tablename, attname, null_frac,
               (most_common_vals::text::text[]) AS most_common_vals,
               most_common_freqs
          FROM pg_stats
         WHERE schemaname = 'public'
           AND lower(tablename) IN ('customers', 'orders')
           AND lower(attname) IN ('c', 'customer_id', 'customers', 'id', 'o', 'orders', 'total')) s;
SQL

The dump is narrowed down to the tables and columns the --query uses, the same whitelist that decides which fields get generated. Without a --query, or with one selecting a *, it covers every column of the tables instead. --max-common-vals caps how many common values each column contributes, since postgres stores up to default_statistics_target of them.

Feeding it back needs nothing else, --table or --query aside:

random-data-load run --engine=pg --database=shop --query="..." --rows=100000 --stat-file=pg_stats.json

A few things worth knowing:

• the dump only carries statistics, never a row. most_common_vals does hold real column values, though, so it is production data and should be treated as such
• values are matched to a table and a column of the run, case-insensitively. Anything the run does not insert into is ignored
• --null-freq-map, --values-freq-map and the literals taken from --query win. A value they already give a frequency to keeps it, and is not counted twice
• a column postgres recorded no NULL for gets none, rather than falling back to --null-freq
• null_frac is scaled up before use. A row is drawn as NULL first and then overwritten when a common value is drawn, so a column whose values cover 60% of its rows only keeps its NULLs on the other 40%. What ends up in the generated table is the null_frac that was measured
• frequencies that add up to more than 1 are warned about, and NULL then takes whatever share is left

--engine=mysql is refused for now rather than exporting something unusable: information_schema.COLUMN_STATISTICS only holds histograms, and only for the columns someone explicitly ran ANALYZE TABLE ... UPDATE HISTOGRAM ON against. On MySQL, set the frequencies by hand with --null-freq-map and --values-freq-map.

Skipping fields that are not relevant to the query

When using --query, random-data-load will avoid generating or sampling fields that are not necessary for the query to run. It can be disabled with --no-skip-fields. It will also disable itself if it encounter any * , since the full length of the row would have consequences on the query execution.

Better field generation based on field names

Very, very minimal for now, based on simple regexes.

	emailRe     = regexp.MustCompile(`email`)
	firstNameRe = regexp.MustCompile(`first.*name`)
	lastNameRe  = regexp.MustCompile(`last.*name`)
	nameRe      = regexp.MustCompile(`name`)
	phoneRe     = regexp.MustCompile(`phone`)
	ssn         = regexp.MustCompile(`ssn`)
	zipRe       = regexp.MustCompile(`zip`)
	colorRe     = regexp.MustCompile(`color`)
	ipAddressRe = regexp.MustCompile(`^ip.*(?:address)*`)
	addressRe   = regexp.MustCompile(`address`)
	stateRe     = regexp.MustCompile(`state`)
	cityRe      = regexp.MustCompile(`city`)
	countryRe   = regexp.MustCompile(`country`)
	genderRe    = regexp.MustCompile(`gender`)
	urlRe       = regexp.MustCompile(`url`)
	domainre    = regexp.MustCompile(`domain`)
	productName = regexp.MustCompile(`product`)
	description = regexp.MustCompile(`description`)
	feature     = regexp.MustCompile(`feature`)
	material    = regexp.MustCompile(`material`)
	currency    = regexp.MustCompile(`currency`)
	company     = regexp.MustCompile(`company`)
	language    = regexp.MustCompile(`language`)

They will use an associated gofakeit generator, https://github.com/brianvoe/gofakeit

Supported fields:

Field type	Generated values
bool	false ~ true
tinyint	0 ~ 0xFF
smallint	0 ~ 0XFFFF
mediumint	0 ~ 0xFFFFFF
int - integer	0 ~ 0xFFFFFFFF
bigint	0 ~ 0xFFFFFFFFFFFFFFFF
float	0 ~ 1e8
decimal(m,n)	0 ~ 10^(m-n)
double	0 ~ 1000
char(n)	up to n random chars
varchar(n)	up to n random chars
date	between --min-generated-time and --max-generated-time
datetime	between --min-generated-time and --max-generated-time
timestamp	between --min-generated-time and --max-generated-time
time	00:00:00 ~ 23:59:59
year	Current year - 1 ~ current year
tinyblob	up to 100 chars random paragraph
tinytext	up to 100 chars random paragraph
blob	up to --max-text-size chars random paragraph
text	up to --max-text-size chars random paragraph
mediumblob	up to --max-text-size chars random paragraph
mediumtext	up to --max-text-size chars random paragraph
longblob	up to --max-text-size chars random paragraph
longtext	up to --max-text-size chars random paragraph
enum	A random item from the valid items list
set	A random item from the valid items list

Valuable types currently not implemented:

• JSONs
• Geospatial
• Vectors

How to download the precompiled binaries

There are binaries available for each version for Linux and Darwin. You can find compiled binaries for each version in the releases tab:

https://github.com/Percona-Lab/random-data-load/releases

To do

General:

• better datetime random generation. It should be flexible over its range
• use more gofakeit generators with regexes to generate "legit" data when possible
• helpers to get schema (generate pgdump/mysqldump commands, get index stats, ...)
• protect against foreign key cycles. Both explicits and implicits (avoid generating implicits that would end up causing loops)
• detect selfpointing foreign keys
• using --values-freq-map to make query parameters work

Sampling:

• normal law through box-muller, select sqrt(-2*log(random()))sin(2pi()*random());
• pareto laws
• have some graph to show --coin-flip-percent with --bulk-size

Stepping stones to fully reproduce cardinalities:

• incorporating arbitrary values with fixed frequency into the bulk inserts
• table-per-table override for --rows, --null-frequency
• coin-flip-percent per relationship basis. Current thought: adding it to --binomial this way --binomial="parent=child:70" to set the coinflip to 70 for this link
• parse col/index stats (cardinality + most_common_elems + most_common_freqs for postgres, cardinalities for MySQL)
• estimate/decide sampling method+tuning based on stats

Without clear plan:

• More random algorithms (as of now, no good implementations has been found for pareto that wouldn't provoke huge runtime and/or huge memory consumption, unless implemented fields are restricted to integers)
• guessing joins on subqueries/cte. Joins wouldn't be based on columns, but on expressions
• be able to "suplement" existing foreign keys with additional columns ?

Version history

Unreleased

• foreign keys are now guessed through subqueries and CTEs, projected down onto the real tables they read, including UNION branches, recursive CTEs, and columns renamed by an alias or a CTE column list
• foreign keys are guessed from implicit JOINs written in the WHERE clause, from correlated EXISTS subqueries, and from IN (subquery) semi-joins
• a multi-column JOIN condition now produces a single composite foreign key instead of one key per column, so a child row no longer mixes columns from different parent rows
• only equality conditions produce a foreign key; range and negated conditions no longer invent one
• a JOIN condition that cannot be traced to real columns is now reported with a warning naming the condition, instead of being dropped silently
• fixed a crash on schema-qualified columns in a JOIN condition, e.g. ON public.orders.order_id = oi.order_id
• fixed a query-guessed foreign key being added a second time when the schema already declared it as part of a composite key, which produced an INSERT listing a column twice
• columns read only inside a CTE are no longer left out of the generated fields
• run --stat-file reads a column statistics export and sets the null and value frequencies from null_frac, most_common_vals and most_common_freqs
• new export-stat subcommand, printing the command that exports those statistics for the tables and columns a --query uses. Only --engine=pg for now
• injected values are now escaped before reaching the INSERT, so a value holding a quote no longer breaks the statement
• --query-param-freq=0 no longer registers the query literals at a frequency of zero, it now leaves them out entirely

0.2.3

• NULL and/or fixed values can be injected at tunable rates
• --rows can be overriden per tables
• improved virtual join handling to enable columns used for many foreign keys
• query parameters are being inserted at tunable frequencies so that query can work as is
• protection against circular dependencies
• self-referencing tables handling through splitting the tables in two. Half the table will reference the other half

0.2.0

• Support for postgres
• parallelism
• bool types
• uniform foreign key patterns
• skipping unecessary columns and backfilling missing foreign keys through query analysis

0.1.10

• Fixed argument validations
• Fixed ~/.my.cnf loading

0.1.10

• Fixed connection parameters for MySQL 5.7 (set driver's AllowNativePasswords: true)

0.1.9

• Added support for bunary and varbinary columns
• By default, read connection params from ${HOME}/.my.cnf

0.1.8

• Fixed error for triggers created with MySQL 5.6
• Added Travis-CI
• Code clean up

0.1.7

• Support for MySQL 8.0
• Added --print parameter
• Added --version parameter
• Removed qps parameter

0.1.6

• Improved generation speed (up to 50% faster)
• Improved support for TokuDB (Thanks Agustin Gallego)
• Code refactored
• Improved debug logging
• Added Query Per Seconds support (experimental)

0.1.5

• Fixed handling of NULL collation for index parser

0.1.4

• Fixed handling of time columns
• Improved support of GENERATED columns

0.1.3

• Fixed handling of nulls

0.1.2

• New table parser able to retrieve all the information for fields, indexes and foreign keys constraints.
• Support for foreign keys constraints
• Added some tests

0.1.1

• Fixed random data generation

0.1.0

• Initial version