Skip to content
This repository has been archived by the owner on Jan 22, 2022. It is now read-only.

Latest commit

 

History

History
103 lines (56 loc) · 3.18 KB

README.md

File metadata and controls

103 lines (56 loc) · 3.18 KB
Raw
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.


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.

Database-Filler database


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?

Database-Filler execute

Database Requirements

  1. The script expects the database schema to already exist in MySQL (mysql -u root -p < test.sql).
  2. All table names and column names in the MySQL schema require back-ticks.
  3. 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.