BigDb Documentation for library developers

BigDb is a database layer designed for extension, such that developers can build easy-to-use database libraries. Those libraries contain Orms, migrations, and stored queries. If you want to DEVELOP one of those libraries, this is the right place to be.

If you want to USE one of those libraries, see Documentation for end users.

Introduction

The standard setup involves subclassing Tlf\BigDb, specifying a namespace to load BigOrm subclasses from, and a fixed directory structure. However, you can hack together your whole own setup if you prefer. For a hacky-own-solution, review the Source Code and Tests. Any musts below are for the standard setup.

For any of your subclasses, look for methods with the @override tag in the docblock. Try grep -R @override to find such methods.

Directory structure

This is a sample directory structure for a BigDb library.

- MyDb.php # Subclass of Tlf\BigDb, and must be at the root of your database library.  
- orm/ # Tlf\BigOrm subclasses SHOULD go in here, but don't have to - that's up to your autoloader.  
    - Article.php  
    - Author.php  
- sql/ # Stored queries must go here  
    - article.sql # sql queries for the table `article`  
    - author.sql # queries for `author` table.  
- migrate/  
    - v1/ # Your initial database setup. You can use .sql OR .php.   
        up.sql # a raw .sql file for migration.   
        down.php # optional. Reverse migrations aren't always needed  
    - v2/ # Second version of your database. v3 is 3rd, and so-on  
        ...

Tlf\BigDb subclass

You MUST subclass BigDb and define the namespace, or override the relevant methods. With this standard setup, a table like article MUST map to Tlf\BigDb\Test\Orm\Article. (simply ucfirst($table_name))

<?php  
namespace Tlf\BigDb\Test;  
  
class ArticlesDb extends \Tlf\BigDb {  
  
    protected string $orm_namespace = 'Tlf\\BigDb\\Test\\Orm';  
}

Alternatively, you can override public function get_orm_namespace(): string, or public function get_orm_class(string $table): string for an entirely custom orm class loader.

Common BigDb methods to overridde

public function get_root_dir(): ?string - Override this if your BigDb subclass is NOT at the root dir of your library.
public function get_orm_class(string $table): string - Override if the namespace + ucfirst($table_name) mappings will not work for you.
public function get_migration_dir(): string - Override if your migration dir is not at your root_dir/migrate/
public function get_migration_vars(): array - Override to make additional vars available to your library's migrations. Default passes ['db'=>$this], so up.php and down.php scripts have access to the BigDb instance via the variable $db.

Uncommon BigDb methods to override

public function migrate(int $version_from, int $version_to) - Override to use a different migration scheme than LilDb's LilMigrations class
public function init_sql() - override to use a different sql storage system than LilDb's LilSql. If you do this, set statements to $this->sql[$query_key] = 'SOME SQL STATEMENT';, and you would probably want to override recompile_sql() as well.
insert(...), update, delete, select - You can override these if you don't want to use LilDb as a backend.
public function row_to_orm(string $table, array $row): \Tlf\BigOrm

Tlf\BigOrm subclass

You must subclass Tlf\BigOrm to have an object representation of database rows, and override public function set_from_db(array $row) and public function get_db_row(): array. You may want to subclass Tlf\BigFormOrm and also override set_from_form(array $data, mixed $form_id = null).

Optionally, you can setup save & delete hooks as well. You should also define properties & getters & setters.

BigOrm overrides

public string $table;: Set this to a string table name.
public function set_from_db(array $row): Use the database row to initialize the properties
public function get_db_row(): array: Create an array of column=>value pairs to save to the database
public function onWillSave(array $row): array: Called during save() before INSERT or UPDATE is performed, and returns the correct (optionally modified) row to save. This method is to be overridden.
public function onDidSave(array $row): Called after save(). This method is to be overridden.
public function onWillDelete(array $row): bool: Hook called before an item is deleted. Return false to stop deletion, true to continue
public function onDidDelete(array $row): Hook called after an item is deleted from database.

BigFormOrm methods/Overrides

public function set_from_form(array $data, mixed $form_id = null): Override this if you're subclassing BigFormOrm. Similar to set_from_db, but for user-supplied data.
sanitize(mixed $input): mixed - return strings with html removed, basically.
slugify(string $text): string - remove all chars except alpha, numeric, and hyphen (-). Convert to lowercase.

Additional BigOrm tips & methods

Define property getters & setters that will be called in any scope where the property itself is not accessible. For a prop $prop, getProp() and setProp($value) will be called, if $prop is not accessible. Typically, define $prop as protected type $prop, so it is defined, but getter/setter is called when $item->prop is accessed from outside the class. Magic getters/setters don't work on public properties.
bin_to_uuid(string $uuid): string converts a mysql binary uuid to a string uuid
uuid_to_bin(string $uuid): string converts a mysql string uuid to a mysql-compatible binary uuid
save() store the item in the database
delete() removes the item from the database
refresh() uses $item->id to re-load the row from the database & calls set_from_db($row) to refresh the object
is_saved():bool return true/false whether the item has been previously saved to the database. Does NOT check if the database row has been updated to match the item's current state
table(): string return the name of the database table this item maps to. Returns $this->table if table property is set.
Lazy/Alternate approach: (not recommended) Create a dynamic/generic, re-usable subclass of BigOrm, then override BigDb::row_to_orm(string $table, array $row): \Tlf\BigOrm. With this, every row could be under a single dynamic Orm class, and you wouldn't have to write an Orm for every table.

BigOrm Example

For a fully functional (& slightly different) example see test/input/BigOrm/Article.php and the test class at test/run/BigOrm.php.

<?php  
  
class Article extends \Tlf\BigFormOrm {  
  
    public int $id;  
    public string $title;  
    public string $body;  
    public string $uuid;  
    public \DateTime $createdAt;  
    public \DateTime $updatedAt;  
    public Status $status;  
    public string $slug;  
  
  
    protected int $author_id;  
    // author & url are both protected so that getAuthor() & getUrl() work. The database stores `slug` and `author_id`  
    protected Author $author;  
    protected string $url;  
  
  
    public function set_from_db(array $row){  
        // each property is mapped from a column. created_at & updated_at are converted to DateTime objects & uuid is converted from binary to string  
    }  
  
    public function set_from_form(array $data, mixed $form_id = null){  
        // similar to set_from_db, except we're coercing (& sanitizing) user-submitted data.   
    }  
  
    public function get_db_row(): array {  
        // each property is mapped to a database column. created_at is converted to a mysql-friendly string, uuid converted to binary, and so-on.  
    }  
  
    public function setAuthor(Author $author){   
        $this->author = $author;  
    }  
  
    public function getAuthor(): Author {  
        // lazy-load $this->author by using $this->author_id, and return $this->author  
        if (isset($this->author))return $this->author;  
        return $this->author = $this->db->get('author', ['id'=>$this->author_id])[0];  
    }  
  
    public function getUrl(): string {  
        return '/article/'.$this->slug.'/';  
    }  
  
    public function onWillSave(array $row): array {  
        // return the row that should actually be saved  
    }  
  
    public function onDidSave(array $row) {  
        $this->refresh(); // reloads the row from the database, which populates anything generated mysql-side (like datetime columns & uuid)  
    }  
  
    public function onWillDelete(array $row): bool {  
        return true; // return false to stop deletion  
    }  
  
    public function onDidDelete(array $row) {  
        // cleanup, such as deleting article body if it is stored on disk & not in the database  
    }  
}

SQL

The SQL files are entirely built around taeluf/lildb's LilSql class.

In the sql dir, you'll define files like article.sql, author.sql, and others. Typically, one for each table, but the file/key structure is up to you.

You'll define a key, optional stop-string (default is ;), and then the query (followed by the stop-string)

article.sql:

-- @query(create, -- END)  
DROP TABLE IF EXISTS `article`;  
CREATE TABLE IF NOT EXISTS `article` (  
    `id` int(11) PRIMARY KEY,  
    `title` varchar(256),  
    `description` TEXT,  
    `slug` varchar(256),  
    `created_at` datetime,   
    `status` varchar(30),  
    `related_article_id` int(11)  
) ;   
-- END  
  
-- @query(get_public)  
SELECT * FROM `article` WHERE `status` LIKE 'public';

Above you see the line -- @query(create, -- END). After, you see a DROP TABLE statement and a CREATE TABLE statement. Then the line -- END. Since the file is article.sql, this (multi) query can be accessed via article.create through $bigdb->exec('article', 'create').

Alternatively, you can provide the full key article.create via $bigdb->exec('article', 'article.create'). This is more useful if you have a utility.sql file that queries from the article table. In that case, you might use $bigdb->query('article', 'utility.get_all_articles').

$bigdb->query('article', 'get_public') will return an array of article orms where the status is public.

Additionally, these queries can have bindable paramaters - HOWEVER, the params are str_replaced after a PDO::quote() on the values, instead of using proper prepare/bind. This pseudo-bind allows bindable params in all types of statements, but the pdo::quote() limits you to raw values.

Migrations

Migrations are built around taeluf/lildb's LilMigrations class. Create migrations dir migrate/. Then create dirs like v1, v2 & create files up.sql, down.sql or up.php & down.php 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.

If you use .sql files for your migrations, then they should just be standard sql. If you use .php files for your migrations, then they should not be versioned. You may put additional .sql files in the migration dir that are not named up or down. In this case, your php script can load those files.

Arg $db is available to up.php and down.php files. To make other vars available to your migration, override public function get_migration_vars(): array in your \Tlf\BigDb subclass.

Example up.php:

<?php  
$db->exec('article.create');  
$db->exec('author.create');  
$db->exec('tag.create');  
  
$db->exec('article.insert_sample_data');

One drawback with this approach is that article.create, author.create, etc are not versioned, so the migration may break in the future. A benefit of this approach is simplicity & only defining queries in one place. A safer approach would be to add the v1 create statements to the migration version dir.

Taeluf.com