Download the PHP package eltaline/bulk without Composer

PDO Bulk Library PDO Bulk Library

Tools for easy use bulk/batch inserts with support ON CONFLICT/ON DUPLICATE KEY/DO NOTHING/INSERT IGNORE, RETURNING sql operators and simple math/concatenation logic.

Contains helper classes for interacting with databases.

Currently supported PDO Postgresql/MySQL.

Installation

Install from composer:

Or install by local file:

Requirements

This library requires PHP 7.1 or later.

Package overview

This package contains several helpers:

PgSQL: PSLIns, PSLInsNth, PSLInsUpd, PSLDel. MySQL: MSLIns, MSLInsNth, MSLInsUpd, MSLDel.

These classes built on top of PDO, allow you to speed up database rows insertion & deletion by performing multiple(bulk) operations per query, with this API.

Testing table scheme

Create table for testing in PostgreSQL/MySQL.

Description important fucntions

Flush function is important, and need to be used everywhere at the end of work with queue. Do write last queue buffer and reset queue buffer.

Queue functions queue() or queuearray() can be used only separately in one loop.

Description optional functions

Can use optional counters in logic.

Reset function for buffer and counters, do reset queue buffer and counters.

PSLIns & MSLIns

This class takes advantage of the bulk insert to empty/temp tables.

• Implements INSERT ...
• Supported RETURNING operator

To use it, create a PSLIns or MSLIns instance with:

• your PDO connection object
• the number of inserts to perform per bulk query
• the name of your table
• the name of the columns to insert
• optional set empty array(not used for INSERT, but need empty if set RETURNING options)
• optional set empty array(not used for INSERT, but need empty if set RETURNING options)
• optional set RETURNING field or fields or '*'
• optional set PDO fetch mode for RETURNING clause(by default empty)

PSLInsNth & MSLInsNth

This class takes advantage of the bulk insert without duplicate key errors in tables.

• Implements ON CONFLICT DO NOTHING and INSERT IGNORE
• Supported RETURNING operator

To use it, create a PSLInsNth or MSLInsNth instance with:

• your PDO connection object
• the number of inserts to perform per bulk query
• the name of your table
• the name of the columns to insert
• optional set empty array(not used for INSERT, but need empty if set RETURNING options)
• optional set empty array(not used for INSERT, but need empty if set RETURNING options)
• optional set RETURNING field or fields or '*'
• optional set PDO fetch mode for RETURNING clause(by default empty)

PSLInsUpd & MSLInsUpd

This class takes advantage of the bulk insert with simple math logic in tables.

• Implements ON CONFLICT (unqiue/composite key) DO UPDATE SET ... and ON DUPLICATE KEY UPDATE ...
• Supported + - / math operators (column+column, column-column, column/column , columncolumn)
• Supported | concatenation operator (column|column|;) where ; is separator
• Supported RETURNING operator

To use it, create a PSLInsUpd or MSLInsUpd instance with:

• your PDO connection object
• the number of inserts to perform per bulk query
• the name of your table
• the name of the columns to insert
• the name of the columns as primary key of table (unique/composite)
• the name of the columns for update or column names with format column+column for update and value addition
• optional set RETURNING field or fields or '*'
• optional set PDO fetch mode for RETURNING clause(by default empty)

PSLDel & MSLDel

This class takes advantage of the bulk delete by value`s of column or columns.

• Implements DELETE ...
• Supported RETURNING operator

To use it, create a PSLDel or MSLDel instance with:

• your PDO connection object
• the number of inserts to perform per bulk query
• the name of your table
• the name of the column or columns for where statement
• optional set empty array(not using now for DELETE, but need empty if set RETURNING options)
• optional set empty array(not using now for DELETE, but need empty if set RETURNING options)
• optional set RETURNING field or fields or '*'
• optional set PDO fetch mode for RETURNING clause(by default empty)

Beginning

Transactions

You can also use transaction for speed up bulk query.

Additionally use try with catch (Exception $e) for rollback transaction.

Simple Operations

Supported in next classes:

PgSQL: PSLIns, PSLInsNth, PSLInsUpd, PSLDel. MySQL: MSLIns, PSLInsNth, MSLInsUpd, MSLDel.

• Classes PSLInsNth & MSLInsNth implements ON CONFLICT DO NOTHING & INSERT IGNORE clauses.

Format: (connection object, queue size, table, [values columns])

Format: (connection object, queue size, table, [values columns])

Format: (connection object, queue size, table, [values columns], [conflict/duplicate columns], [update columns])

Format: (connection object, queue size, table, [values columns])

Full example:

Advanced Operations

Supported in next classes:

PgSQL: PSLInsUpd. MySQL: MSLInsUpd.

• Supported math/concatenation operations on fields with previous values.

Format: (connection object, queue size, table, [values columns], [conflict/duplicate columns], [update columns])

Full example:

Returning Operations

Supported in next classes:

PgSQL: PSLIns, PSLInsNth, PSLInsUpd, PSLDel. MySQL: MSLIns, MSLInsNth, MSLInsUpd, MSLDel.

Use one of supported class with RETURNING operator.

• Last argument is PDO fetch mode
• Also can use advanced math/concatenation operations together with returning operations

Supported fetch modes:

Format: (connection object, queue size, table, [values columns], [not used], [not used], [returning columns], [fetch mode])

Format: (connection object, queue size, table, [values columns], [not used], [not used], [returning columns], [fetch mode])

Format: (connection object, queue size, table, [values columns], [conflict/duplicate columns], [update columns], [returning columns], [fetch mode])

Format: (connection object, queue size, table, [values columns], [not using now], [not using now], [returning columns], [fetch mode])

Full example:

RAW Sql

For setting columns in RAW Sql format, You need add suffix :IS_RAW in column names. Example:

Performance tips

To get the maximum performance out of this library, you should:

• wrap your operations in a transaction
• disable emulation of prepared statements (PDO::ATTR_EMULATE_PREPARES=false)

These two tips combined can get you up to 50% more throughput in terms of inserts per second.

Recommendations

When using transactions, I recommend not forget use over this helpers - try and catch with throw and .

Limitations

Be careful when raising the number of operations per bulk query, as you might hit these limits.

$ins = new PSL...($pdo, 1000, 'tablename', ['columnname']);

Recommended use this library with 100-1000 queries per bulk query insertions.

• PHP's [memory_limit]
• MySQL's [max_allowed_packet]
• PDO also has a limit of 65535 query parameters per statement, effectively limiting the number of operations per query to floor(65535 / number of columns).

Maximum 65535 query parameters is allowed. Ex. 65535 / 10 columns ~= 10922 (is max queries per 1 bulk query).

END