taeluf/lildb

A simple PDO wrapper with convenience methods for common SQL verbs like insert, update, delete, select

Maintainers

Details

gitlab.com/taeluf/php/lildb

Source

Issues

Installs: 147

Dependents: 4

Suggesters: 0

Security: 0

Stars: 0

Forks: 2

v0.1.x-dev 2023-12-07 23:27 UTC

Requires (Dev)

Suggests

  • taeluf/cli: install the dev dependency to use the cli features

MIT ddf862f95f75b436587ef5bcdb02d4d7712696d8

This package is auto-updated.

Last update: 2024-10-08 07:13:29 UTC

README

LilDb: A little database wrapper for PDO

Remove a lot of boilerplate code with wrapper functions for common sql verbs.

This documentation is generated with php/code-scrawl. For the README template file see .docsrc/README.src.md

Features

LilSql is new! See test/run/Sql.php for examples!

  • LilDb: Simple interface for common SQL verbs like SELECT, CREATE, UPDATE, DELETE
  • LilMigrations: Easy to use SQL Migrations from versioned directories
  • LilSql: Name queries in sql files with @query(some.name) and serialize them into a keyed array for later user
  • LilOrm: (Alpha version) A minimalist ORM for mapping arrays of data to objects with magic getters & some convenience methods

Install

composer require taeluf/lildb v0.1.x-dev

Usage

Instantiate LilDb or LilMigration & call the methods you need. For examples, see test/run/Tests.php and test/run/Migrations.php

Example (lildb + migration)

<?php  
$db = $this->file('test/input/migrate/db.sqlite');  
unlink($db);  
$migrations_dir = $this->file('test/input/migrate/');  
  
// init the database  
$ldb = \Tlf\LilDb::sqlite($db);  
$ldb->create('blog',  
    ['title'=>'varchar(200)']  
);  
$ldb->insert('blog',['title'=>'one']);  
  
// do the migration  
$lm = new \Tlf\LilMigrations($ldb->pdo, $migrations_dir);  
// $lm->migration_vars = ['some_var'=> 'some value']; # optionally expose variables to the migration files.  
$lm->migrate(0,1);  
  
// test that the table has been altered to have a 'description' field  
$this->compare(  
    $ldb->select('blog')[0],  
    ['title'=>'one',  
    'description'=>'',  
    ],  
);

Migration file test/input/migrate/v1/up.sql: 

ALTER TABLE blog  
    ADD COLUMN description TEXT;

class Tlf\LilDb

A lil tiny database class for common sql functions like CREATE, UPDATE, SELECT, DELETE

See source code at /code/LilDb.php

Constants

Properties

  • public \PDO $pdo; a pdo instance

Methods

  • static public function new(string $user, string $password, string $db, $host='localhost') Convenience method to initialize with pdo
  • static public function sqlite(string $dbName = ':memory:') Convenience method to initialize sqlite db in memory
  • static public function mysql($dbName = ':memory:') Convenience method to initialize mysql db in memory
  • public function __construct(\PDO $pdo) Initialize with a db handle

  • public function create(string $tableName, array $colDefinitions, bool $recreateIfExists=false) Create a new table if it doesn't exist.

  • public function query(string $sql, array $binds=[]) Execute an Sql statement & get rows back

  • public function select(string $tableName, array $whereCols=[]) Get rows from a table with the given $whereCols

  • public function insert(string $table, array $row) Insert a row into the database
    Converts array values to json

  • public function insertAll(string $table, array $rowSet)

  • public function update(string $table, array $newRowValues, string $idColumnName='id') Update an existing row. Shorthand for updateWhere() with the id column set as the where values.
  • public function updateWhere(string $table, array $newRowValues, array $whereVals)
  • public function delete(string $table, array $whereCols) Delete rows from a table
  • public function execute(string $sql, array $binds=[]) Execute an Sql statement & get a PDOStatement back
  • public function exec(string $sql, array $binds=[]) Alias for execute()
  • public function getPdo() get the pdo object
  • public function pdo() get the pdo object

  • static public function whereSqlFromCols(array $columns) Convert key=>value array into a 'WHERE' sql.

  • static public function keysToBinds(array $keyedValues) Convert an array ['key'=>$val, ':key2'=>$val] into binds: [':key'=>$val, ':key2'=>$val].

class Tlf\LilMigrations

A minimal class for handling sql migration. Create a migrations dir. Then create dirs like v1, v2 & create files up.sql, down.sql in each versioned dir. Migrating from 1 to 2 will execute v2/up.sql. From 3 down to 1 will execute v2/down.sql and v1/down.sql. You may also make files like v1/up-1.sql, v1/up-2.sql to execute multiple files in order.

You can create .config/ldb.json file with the relative 'dir' and 'db' settings

See source code at /code/LilMigrations.php

Constants

Properties

  • public \PDO $pdo; a pdo instance
  • public string $dir; the dir for migrations scripts.
  • public array $migration_vars = []; array<string, mixed> of variables to extract and make available to up.php and down.php migration files.

Methods

  • public function __construct(\PDO $pdo, string $dir) In $dir, there should be directories named 'v1', 'v2', 'v3', and so on.
    In the v1/v2/v3 dirs, there should be up.sql & down.sql files with valid SQL statements for whatever database you're using

  • static public function sqlite(string $dbName = ':memory:') Convenience method to initialize sqlite db in memory

  • public function migrate(int $old, int $new) Migrate from old version to new

  • public function run_migration_version($version, $up_or_down)

class Tlf\LilSql

Facilitates storing SQL on-disk in sql files and converting those sql files to an array of commands & then serializing that.

This is idealy a build tool, not a runtime tool, so you will unserialize(file_get_contents(...)) to load the array of queries

Or @query(some.name, delimiter) if you don't want to stop on a semi-colon

See source code at /code/LilSql.php

Constants

Properties

  • public $queries = []; array of queries to use/serialize (you should serialize them & skip this processing in production!)

Methods

  • public function load_files(string $dir, string $namespace_prefix='') Load sql files from the following dir into the queries
  • public function serialize(string $file)

  • public function parse_sql(string $sql, string $prefix='', &$queries []) Convert an SQL string containing multiple queries into an array. All queries must have an explicit identifier.

class Tlf\LilOrm

Minimal ORM implementation.

See source code at /code/LilOrm.php

Constants

Properties

  • public $_cache = [];

Methods

  • public function __construct($row)

  • public function one($name, $id, $id_column='id') Get an object with the given id

  • public function many($that, $id_column=null, $this_tablenull) Get an array of objects that point to this item