tables_demo.php

<?php

/* 
This file includes example dataset generation settings for an image database, which is defined as follows:

	-- table of users
	create table users (
		id serial primary key,
		username varchar(10) unique not null,
		lastname text not null,
		firstname text not null,
		age integer check (age >= 0),
		height decimal(3,2) check (height > 0),
		join_date date not null
	);

	-- table of images
	create table images (
		id serial primary key,
		filename text not null,
		owner_id integer not null references users (id) on delete cascade on update cascade
	);

	-- table mapping images to faces of users that are shown in the images
	create table image_faces (
		user_id integer not null references users (id),
		image_id integer not null references images (id),
		certainty decimal(3,2) check (certainty between 0 and 1),
		primary key (user_id, image_id)
	);
*/

$settings = [	

	// ------------------------------------------------------------------------
    'users' => [
        'rows' => 100,
        'columns' => [
			// id serial primary key
            'id' => [
                'generator' => 'SerialGenerator',
                'options' => [ 
					'start' => 1 
				]
			],
			// username varchar(10) unique not null
            'username' => [
                'generator' => 'PatternGenerator',
                'options' => [
					'unique' => true,
					'pattern' => '%1$s%2$s',
					'generators' => [
						[ // %1
							'generator' => 'StringGenerator',
							'options' => [
								'alphabet' => 'abcdefghijklmnopqrstuvwxyz',
								'minLength' => 3,
								'maxLength' => 8,
								'distribution' => 'UniformDistribution'
							]
						], 
						[ // %2
							'generator' => 'NumberGenerator',
							'options' => [
								'min' => 0,
								'max' => 99,
								'distribution' => 'UniformDistribution',
								'decimals' => 0
							]
						]
					]
                ]
			],
			// lastname text not null
			'lastname' => [
				'generator' => 'CategoryGenerator',
				'options' => [
					'source' => 'data/lastnames.txt',
					'distribution' => 'ExponentialDistribution'
				]
			],
			// firstname text not null
			'firstname' => [
				'generator' => 'CategoryGenerator',
				'options' => [
					'source' => 'data/firstnames.txt',
					'distribution' => 'UniformDistribution'
				]
			],
			// age integer check (age >= 0)
			'age' => [
				'generator' => 'NumberGenerator',
				'options' => [
					'distribution' => 'NormalDistribution',
					'min' => 0,
					'max' => 85,
					'nulls' => .2
				]
			],
			// height decimal(3,2) check (height > 0)
			'height' => [
				'generator' => 'NumberGenerator',
				'options' => [
					'distribution' => 'NormalDistribution',
					'min' => 1.4,
					'max' => 2.2,
					'decimals' => 2,
					'nulls' => .3
				]
			],
			// join_date date not null
			'join_date' => [
				'generator' => 'DateGenerator',
				'options' => [
					'min' => '2010-01-01',
					'max' => '2018-12-31',
					'distribution' => 'NormalDistribution'
				]
			]	
        ]
	], 

	// ------------------------------------------------------------------------
	'images' => [
		'rows' => function($dataSet) {
			return $dataSet->getRowCount('users') * intval(round(rand(7, 12), 0));
		},
		'columns' => [
			// id serial primary key
			'id' => [
                'generator' => 'SerialGenerator',
                'options' => [ 
					'start' => 1 
				]
			],
			// filename text not null
			'filename' => [
                'generator' => 'PatternGenerator',
                'options' => [
					'pattern' => '%1$s.%2$s',
					'generators' => [
						[ // %1
							'generator' => 'StringGenerator',
							'options' => [
								'alphabet' => 'abcdefghijklmnopqrstuvwxyz012345678',
								'minLength' => 3,
								'maxLength' => 15,
								'distribution' => 'UniformDistribution'
							]
						], 
						[ // %2
							'generator' => 'CategoryGenerator',
							'options' => [
								'source' => ['jpg', 'png', 'docx', 'pdf', 'xlsx'],
								'distribution' => 'UniformDistribution'
							]
						]
					]
                ]
			],
			// owner_id integer not null references users (id) on delete cascade on update cascade
			'owner_id' => [
				'generator' => 'ForeignKeyGenerator',
				'options' => [
					'table' => 'users',
					'column' => 'id',
					'distribution' => 'ExponentialDistribution'
				]
			]
		]
	],
	
	// ------------------------------------------------------------------------
	'image_faces' => [
		'rows' => [
			// Rows are not given as a static number here; rather the rows are generated by distributions. The following array items are processed one after the other, each representing a column in the table. The first one must have 'probability' defined, which determines probability of a record of the referenced column appearing in this table
			// user_id integer not null references users (id) on delete cascade on update cascade
			[
				'field' => 'user_id',
				'probability' => .4, // 40% of users will appear in this foreign key column
				'options' => [
					'table' => 'users',
					'column' => 'id'
				],
				'uniqueCombinations' => true // if unique combinations are desired, this must be set to true
			],
			// image_id integer not null references images (id)
			[
				'field' => 'image_id',
				'combinations' => [ // each user appears in 1 to 10 images
					'min' => 1, // min must be at least one here!
					'max' => 10,
					'distribution' => 'ExponentialDistribution'
				],
				'generator' => 'ForeignKeyGenerator',
				'options' => [
					'table' => 'images',
					'column' => 'id',
					'distribution' => 'NormalDistribution'
				],
			]
		],
		'columns' => [
			// certainty decimal(3,2) check (certainty between 0 and 1)
			'certainty' => [
				'generator' => 'NumberGenerator',
				'options' => [
					'min' => 0,
					'max' => 1,
					'decimals' => 2,
					'nulls' => .7,
					'distribution' => 'NormalDistribution'
				]
			]
		]
	]
];

require_once 'lib/DataSet.php';
$ds = new DataSet($settings);
$ds->generate();
$ds->printSql();