Download the PHP package vielhuber/dbhelper without Composer

🍗 dbhelper 🍗

dbhelper is a small php wrapper for mysql/postgres/sqlite databases.

installation

install once with composer:

then add this to your project:

usage

logging

dbhelper can support setting up a mature logging system on mysql/postgres databases.

setup_logging() does four things:

• it creates a logging table (if not exists)
• it appends a single column updated_by to every table in the database (if not exists)
• it creates triggers for all insert/update/delete events (if not exists)
• it deletes old logging entries based on the delete_older option

you should run this method after a schema change (e.g. in your migrations) and you can also run it on a daily basis via cron. it is recommened to exclude blob/bytea columns.

the logging table has the following schema:

• id: unique identifier of that single change
• log_event: insert/update/delete
• log_table: name of the table of the modified row
• log_key: key of the modified row
• log_column: column of the modified row
• log_value: value of the modified row
• log_uuid: unique identifier of that row change
• updated_by: who did make that change
• updated_at: date and time of the event

we now have to adjust our queries. updated_by must be populated by the web application on all insert/update queries and our logging table must be manually populated before delete queries:

instead of all this we can let dbhelper magically do the heavy lifting on every insert/update/delete for us:

dbhelper then automatically injects the updated_by column on all insert/update statements and inserts a log entry before every delete query (all queries are handled, even those who are sent with $db->query).

important note: if we manipulate data outside of our web application, the triggers also work, except with accurate values in updated_by. this is especially true for delete statements (they also work without the manual insert query upfront).

call the following helper functions, if you (temporarily) need to disable logging by triggers:

that's it – happy logging.

wordpress support

this also works for wordpress (using wpdb, prepared statements and stripslashes_deep under the hood):

locking in sqlite

sqlite is nice but database locking can be tricky.\ dbhelper provides a default timeout of 60 seconds, which prevents most database locks.\ you can manually define a timeout in the connect() function.\ checkout the following sqlite lock tests:

• php tests/lock/run.php 1: runs into database locking
• php tests/lock/run.php 120: does not run into database locking

also consider enabling wal via $db->query('PRAGMA journal_mode=WAL;');.

return values

as return values after fetching results dbhelper usually returns associative arrays.\ if you use it with wordpress, objects are returned.\ dbhelper throws exceptions on all occured errors.\ on an insert operation, the primary key (id) is returned.\ on any delete, update or even query operation, the number of affected rows are returned.

static version

here is also a static version with static function calls (this makes sense, if you use a single instance of dbhelper):