Quick Databases for PHP
, (*1)
PHP7 command line console application to create/drop databases & users as well as execute
lists of SQL scripts. This will also generate a PHP file that returns an
instance of PDO., (*2)
Currently supports creating MySQL environments with a default character set of
UTF-8 and utf8_unicode_ci collation., (*3)
The purge command is for Microsoft SQL Server., (*4)
Please report all bugs on the Github issues page., (*5)
Environments
- Development
- Testing Quality Assurance
- user with all privileges
- user with only stored procedure execute privileges
- Production
- user with only stored procedure execute privileges
Naming Schema
Database names begin with a maximum 7 character name of your choosing, and end
with a randomly generated 5 character identifier. The first letter of the ID
designates its environment D (Development), T (Test), or P (Production)., (*6)
This allows you to easily spin up alternate environments for development and
testing on the same server without conflict., (*7)
Usernames are the same as the database name ending with an _A or an _E to
designate permissions (All or Execute respectively).
The privileged accounts ending with _A (User ALL)
are intended for use with DDL in development and testing.
The execute only accounts are designated by _E (User EXECUTE)
and are intended for use by the application in testing and production.
This follows the principle of least privilege whereby
all DML is wrapped within explicit parameterized stored procedures., (*8)
The reason names are limited to 7 characters is because up until MySQL 5.7.8
usernames could only be 16 characters long. Now they can be 32, but this
application currently constrains that for backwards compatibility., (*9)
Examples
- Development
- Database:
example_D4JAOb
- Privileged User:
example_D4JAOb_A
- Test
- Database:
example_TzWwAo
- Privileged User:
example_TzWwAo_A
- Application User:
example_TzWwAo_E
- Production
- Database:
example_PNITvJ
- Application User:
example_PNITvJ_E
The generated PHP file will return an instance of PDO and looks like this:, (*10)
<?php
return call_user_func(function(){
$hostname = 'localhost';
$database = 'example_D4JAOb';
$username = 'example_D4JAOb_A';
$password = '8is+G?Gkg.BNW_}9B5kmjPyr02G~Z2lO';
$pdo = new PDO("mysql:host=$hostname;
charset=UTF8;
dbname=$database",
$username,
$password
);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
return $pdo;
});
The anonymous function allows for easy inclusion in any PHP script
without conflicting with variable names., (*11)
<?php
$pdo = require __DIR__.'/example_D4JAOb_A.pdo.php';
$sql = 'SELECT * FROM Users';
$array = $pdo->query($sql)->fetchAll();
Getting Started
Registered on packagist for easy global installation using composer., (*12)
composer global require jpuck/qdbp
Make sure your $PATH contains the global bin directory,
because composer doesn't automatically modify your $PATH variable.
However, composer will tell you the location of the global bin directory:, (*13)
composer global config bin-dir --absolute
You can then [add that location to your shell profile or rc so that it's always available][14].
For example, if you're running Ubuntu 16.04 with bash, then this might work:, (*14)
echo 'export PATH="$PATH:$HOME/.config/composer/vendor/bin"' >> ~/.bashrc
After installing, run without any arguments to see a list of commands., (*15)
qdbp
Use the -h flag with any command to get help with usage., (*16)
qdbp <command> -h
Examples
Create a development environment with name prefix dbname on localhost:, (*17)
qdbp create -e dev dbname
Create a production environment with name prefix dbname on a server
located at mysql.example.com:, (*18)
qdbp create -e prod -H mysql.example.com dbname
Execute an SQL script:, (*19)
qdbp execute /path/to/ddl.sql
Execute an SQL script using the generated credentials file:, (*20)
qdbp execute -p /path/to/example_D4JAOb_A.pdo.php /path/to/ddl.sql
Execute a list of SQL scripts:, (*21)
qdbp execute -p example_D4JAOb_A.pdo.php /path/to/sql.lst
A list of SQL scripts to be executed can contain files in the same directory,
relative paths outside the directory, or absolute paths anywhere on the system.
For example, the contents of sql.lst could look like this:, (*22)
drop_tables.sql
../ddl.sql
/var/www/project/SQL/stored_procedures.sql
Wallogit.com
