PHP Database Fill: Fill records of MySQL tables with test data

Recommend this page to a friend!

Download .zip

Info

Example

View files (6)

Download .zip

Reputation

Support forum (1)

Blog

Links

Last Updated		Ratings				Unique User Downloads		Download Rankings
2024-01-10 (3 days ago)		Not enough user ratings				Total: 399 This week: 6		All time: 6,563 This week: 50

Version		License		PHP version		Categories
`database-fill` 0.67		GNU General Publi...		5.3		PHP 5, Databases, Testing

Description

Author

Martin Latter

This class can fill records of MySQL tables with test data.

It takes the SQL dump file of a MySQL database and parses it to extract the list of database tables.

The class executes SQL statements to insert records on the MySQL database tables.

The new record field values can be generated randomly or set to a constant value.

The number of records to insert, the range of characters to use in random data and the character set encoding are configurable parameters.

Martin Latter

Performance

Level

Name:	Martin Latter `<contact>`
Classes:	8 packages by Martin Latter
Country:	United Kingdom

Level 4

Innovation award

Nominee: 5x

Example


<?php



/**

    * Example to set-up and call databasefiller.class.php

    * Martin Latter, 14/12/2014

*/



declare(strict_types=1);



date_default_timezone_set('Europe/London');

ini_set('memory_limit', '256M'); # for inserting a large number of rows ($aConfiguration['num_rows'])



require('classes/databasefiller.class.php');

header('Content-Type: text/html; charset=utf-8');





/**

    * Configuration array settings to pass to databasefiller.class.php

*/



$aConfiguration =

[

    # output type toggle

    'debug' => false, # set TRUE for verbose screen output and no database insertion, FALSE for database insertion



    # number of rows to insert

    'num_rows' => 10,

        // optimise mysqld variables in my.cnf/my.ini files when inserting a large number of rows (e.g. 50000)



    # database details

    'host'     => 'localhost',

    'database' => 'dbfilltest',

    'username' => 'USERNAME',

    'password' => 'PASSWORD',



    # schema file

    'schema_file' => 'test.sql',



    # database connection encoding

    'encoding' => 'utf8', # latin1 / utf8 etc



    # random data toggle - set to false for a much faster fixed character fill - but ... no unique indexes permitted

    'random_data' => true,



    # random character range: ASCII integer values

    'low_char' => 33,

    'high_char' => 126,



    // 'incremental_ints' => true,

    // 'populate_primary_key' => true, # experimental



    # CLI usage: rows of SQL generated before displaying progress percentage

    'row_counter_threshold' => 1000

];





$oDF = new DatabaseFiller($aConfiguration);



echo $oDF->displayMessages();

Details

This repo is shoved into retirement. The schema file parsing and single-threaded nature of PHP, along with high memory usage makes this package obsolete.

More efficient alternatives are MySQL-Filler and mysql_random_data_load.

<br>

<hr>

<br>

Database Filler

Populate MySQL database tables with test data by parsing the SQL schema file.

Purpose

Database table population without using any real or sensitive data:
Schema design and development: + Check table field population with specified datatypes, potential data truncation etc. + Test connection encoding and character encoding, and data insertion speeds.

<br>

[1]: https://tinram.github.io/images/databasefiller-data.png ![Database-Filler database][1]

<br>

Background

Originally, I needed to populate a database containing 14 complex tables. Tools such as Spawner are ideal for populating small tables, but in this case, specifying the datatypes for 300+ fields to initiate Spawner would have been insanity.

Instead, why not parse the SQL schema?

[2]: https://tinram.github.io/images/databasefiller-execute.png ![Database-Filler execute][2]

Database Requirements

The script expects the database schema to already exist in MySQL (`mysql -u root -p < test.sql`).
All table names and column names in the MySQL schema require back-ticks.
Unique keys must be removed from tables when the configuration array option random_data is set to false.

Other

The majority of MySQL datatypes are supported.
Any foreign keys are disabled on data population.
Random character generation is slow in PHP, and such slowness further depends on field length, number of fields, and the number of rows being generated.
Multiple INSERTs are added in a single query, which is quite fast. The number of INSERTs per second will depend on MySQL configuration settings (the defaults are not optimised), datatype / length inserted, system load, operating system, hardware etc.

Further Options

Configuration boolean toggles (false by default):

incremental_ints + make added integers incremental, enabling simplistic integer foreign keys.
populate_primary_key + populate a primary key field, e.g. a UUID used as a primary key (experimental, supports only some definitions).

Set-up

Ensure the database already exists in MySQL e.g. for the test schema:

    mysql -u root -p < test.sql

Adjust the array connection details and parameters in the file databasefiller_example.php

Then execute this file with PHP on the command-line:

    php databasefiller_example.php

or run the file through a web server e.g.

    http://localhost/Database-Filler/databasefiller_example.php

Other Projects

SQLParser would have been used in this project had it been available in 2014.

License

Database Filler is released under the GPL v.3.

Files

File	Role	Description
`classes` (1 file)
`basketball.sql`	Data	Auxiliary data
`databasefiller_example.php`	Example	Example script
`LICENSE`	Lic.	Auxiliary data
`README.md`	Doc.	Auxiliary data
`test.sql`	Data	Auxiliary data