README

neoan3 app for mysqli connectivity

opinionated (yet configurable)
rapid development
secure (prepared statements & additional security)
"plug & play" in any (if any) framework

Designed for neoan3, but works as standalone

Prepared Statements

All queries are performed as prepared statements, running through additional security to sanitize columns.

Throughout this file you will find representation of SQL-functionality that uses a simplified SQL-logic. These examples are NOT the queries actually performed by the wrapper, but meant to provide a solid understanding of the capabilities of this wrapper.

Quick Start

Simple SELECT

// SELECT name FROM user WHERE id = 1
// returns e.g.: [0=>['name'=>'Adam']]

$user = Db::easy('user.name',['id'=>1]);

Simple INSERT

// INSERT INTO user(name,email) VALUES('Sam','sam@sam.example')

$insert = ['name'=>'Sam','email'=>'sam@sam.example'];
$newId = Db::user($insert);

Simple UPDATE

// UPDATE user SET name = 'Sam', email = 'sam@sam.example' WHERE id = 1

$update = ['name'=>'Sam','email'=>'sam@sam.example'];
Db::user($update, ['id'=>1]);

See test/test.php for some more quick start examples and/or dive into learning:

Installation

composer require neoan3-apps/db

require dirname(__FILE__).'/vendor/autoload.php'

use Neoan3\Apps\Db;

Db::setEnvironment([
    'name' => 'your_db',
    'user' => 'root',
    'password' => 'Som3S3cur3Pa55word'
])

/*
*    OR per defines:
*    define('db_host','localhost');
*    define('db_name','yourDB');
*    define('db_user','root');
*    define('db_password','Som3S3cur3Pa55word');
*/

try {
    $test = Db::ask('>NOW() as now'); 
} catch(DbExeption $e){
    die($e->getMessage());
}


/*
*    $test: [0=>['now'=>'2019-01-01 12:12:12']]
*/

Environment variables

Environment variables can either be set as global constants or using Db::setEnvironment()

/*
* When using Db::setEnvironment() the prepended 'db_' is ommitted.
*/

// set single variable:
Db::setEnvironment('name','test_db');

// set multiple variables:
Db::setEnvironment(['name'=>'test_db','password'=>'FooBar']);

We recommend defining these values in your frame when using neoan3.

Conventions

This tool was created with the recommended neoan3 database structure in mind. As such, the following assumptions are made for best usability (auto-joins etc.). ( See environment variables ):

tables use snake_case naming
columns use snake_case naming OR cameCase naming
primary keys are either int(11) auto_incremented OR binary(16)
primary keys are called "id", foreign relations are referred to as [table_name]_id OR [table_nameId]
when handling rows marked as deleted, the field name must be "delete_date" OR "deleteDate" (type can be DATE or DATETIME)

Getting started

Db::easy($selectorString [, $conditionArray, $callFunctions, $debug])

The easy-function converts a string into a prepared statement and executes it. It returns an array of associative arrays. The easy-markup is a simplified selector-string representing your database-structure.

See operandi & selectandi for added complexity

Db::ask($param1 [, $param1, $param1])

The ask-function can execute queries based on how it is used. It is best to start understanding the examples below. It returns an array of associative arrays.

/component/user/user.sql:

SELECT * FROM user WHERE CONCAT_WS(' ',first_name,last_name) LIKE {{name}}

With the ANY-action, SQL can be managed in editor-friendly formats. By default, the locator expects the neoan3 folder "component" (see Environment variables) and understands to following format:

/[file] = '/[db_file_location]/[file]/[file].sql' (like above)

or deeper variations like:

/[folder]/[file] = '/[db_file_location]/[folder]/[file].sql'

There are no considerations to be made regarding order of parameters. Naming of the target using curly brackets will ensure that only matching array keys are used.

This makes directly handling user-input save.

Magic Method Call

So why does the Db::ask have such a strange name? Because the ask-function is something that you don't have to worry about. It is what happens under the hood and can be replaced by calling it using magic methods.

As-declaration

Handling field-name modifications.

Conditional modifiers (operandi)

Common condition modifications can be applied by string-manipulation of the condition-array.

But what if the value passed to an operandi starts with a modifier? You can prepend the modifier '=' (equal) to prevent operandi from triggering! e.g.:

Db::ask('password',['password'=>'='.password_hash('123456', PASSWORD_DEFAULT)])

Value modifiers (selectandi)

Common value-modifications can be applied by string-manipulation of the select-statement. These modifiers should be used with the "as-declaration"

But what if the value passed to a selectandi starts with a modifier? You can prepend the modifier '=' (equal) to prevent operandi from triggering! We do not provide an example as this can only happen if your column names would start with a character in violation with a secure filter_character setup.

Db-easy markup

Let's face it: most of the time our queries are rather simple. Whenever we need more complex queries, there will always come the point of realizing that pure SQL would be easier to read than endlessly encapsulated arrays. This is where using Db::ask's "any"-actions come into play.

Db::easy does not target these scenarios, but rather specializes in the ever-day retrieval of a particular set of data. The selected columns are written in one single string.

Selecting multiple columns from a table

Multiple columns of one table are separated by a space:

// SELECT first_name, last_name FROM user

Db::easy('user.first_name user.last_name');

Simple joins

Joins are generated based on the order of the occurrence in the string from left to right, respecting the first used table as the "master". Easy can only perform JOINS if the recommended db structure is used. This means that foreign keys must be in the format "master"_id.

// SELECT 
//   user.first_name, 
//   user.last_name, 
//   user_email.email, 
//   user_password.confirm_date
// FROM user 
// JOIN user_email ON user_email.user_id = user.id
// JOIN user_password ON user_password.user_id = user.id

Db::easy('user.first_name user.last_name user_email.email user_password.confirm_date');

OOP & Testing

Although you want to write your own wrapper depending on the Interface your framework uses, this library comes with a simple wrapper out of the box to encourage dependency injection usage.

$environment = ['name' => 'my_db'];
$database = new \Neoan3\Apps\DbOOP($environment);
$database->easy('user.*', ['^delete_date']); //executes & returns Db::easy 
$database->smart('user', ['name'=>'sam', 'user_type'=>'admin']); //executes & returns Db::ask

Heads up

The general approach of the db-app has been applied for years. While the difference to common wrappers for mysqli of pdo seems rather big, developers are usually surprised of the low learning-curve and possibilities for faster development it offers.

Deprecation

The Db::data-function is considered unsafe without proper escaping and throws a deprecation-notice since 0.0.3

neoan3-apps / db

Maintainers

Details