PHP code example of marwen-brini / bob-the-builder

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

marwen-brini / bob-the-builder example snippets

// Hydrate models from database without checking fillable/guarded
$model->forceFill($databaseRow); // Bypasses mass assignment protection

$builder = $connection->table('users')
    ->enableExistsCache(120) // Cache for 2 minutes
    ->where('email', '[email protected]');

// First call - hits database
$exists = $builder->exists();

// Subsequent calls within 2 minutes - uses cache
$exists = $builder->exists(); // No database query!

// Global scopes automatically apply to relationships
$user->posts()->get(); // Includes global scopes from Post model

// Or disable for specific queries
$user->posts()->withoutGlobalScopes()->get();

use Bob\Database\Connection;

// Configure your database connection
$connection = new Connection([
    'driver' => 'mysql',
    'host' => '127.0.0.1',
    'port' => 3306,
    'database' => 'your_database',
    'username' => 'your_username',
    'password' => 'your_password',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
]);

// Start building queries
$users = $connection->table('users')
    ->where('active', true)
    ->where('age', '>=', 18)
    ->orderBy('name')
    ->limit(10)
    ->get();

// Option 1: Configure via connection config
$connection = new Connection([
    'driver' => 'mysql',
    'database' => 'mydb',
    // ... other config
    'fetch' => PDO::FETCH_OBJ,  // Return objects instead of arrays
]);

// Option 2: Change dynamically at runtime
$connection->setFetchMode(PDO::FETCH_OBJ);  // Use objects
$users = $connection->table('users')->get(); // Returns array of stdClass objects

$connection->setFetchMode(PDO::FETCH_ASSOC); // Back to arrays (default)
$users = $connection->table('users')->get(); // Returns array of associative arrays

// Get all records
$users = $connection->table('users')->get();

// Get specific columns
$users = $connection->table('users')
    ->select('id', 'name', 'email')
    ->get();

// Get first record
$user = $connection->table('users')
    ->where('email', '[email protected]')
    ->first();

// Get single value
$email = $connection->table('users')
    ->where('id', 1)
    ->value('email');

// Basic where
$users = $connection->table('users')
    ->where('status', 'active')
    ->get();

// Multiple conditions
$users = $connection->table('users')
    ->where('status', 'active')
    ->where('age', '>', 18)
    ->get();

// Or where
$users = $connection->table('users')
    ->where('role', 'admin')
    ->orWhere('role', 'moderator')
    ->get();

// Where in
$users = $connection->table('users')
    ->whereIn('id', [1, 2, 3])
    ->get();

// Where between
$users = $connection->table('users')
    ->whereBetween('age', [18, 65])
    ->get();

// Where null
$users = $connection->table('users')
    ->whereNull('deleted_at')
    ->get();

// Inner join
$users = $connection->table('users')
    ->join('posts', 'users.id', '=', 'posts.user_id')
    ->select('users.*', 'posts.title')
    ->get();

// Left join
$users = $connection->table('users')
    ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
    ->get();

// Multiple joins
$users = $connection->table('users')
    ->join('posts', 'users.id', '=', 'posts.user_id')
    ->join('comments', 'posts.id', '=', 'comments.post_id')
    ->get();

// Count
$count = $connection->table('users')->count();

// Sum
$total = $connection->table('orders')->sum('amount');

// Average
$avg = $connection->table('products')->avg('price');

// Min/Max
$min = $connection->table('products')->min('price');
$max = $connection->table('products')->max('price');

// Single record
$connection->table('users')->insert([
    'name' => 'John Doe',
    'email' => '[email protected]',
    'password' => bcrypt('password')
]);

// Multiple records
$connection->table('users')->insert([
    ['name' => 'John', 'email' => '[email protected]'],
    ['name' => 'Jane', 'email' => '[email protected]']
]);

// Insert and get ID
$id = $connection->table('users')->insertGetId([
    'name' => 'John Doe',
    'email' => '[email protected]'
]);

// Update records
$affected = $connection->table('users')
    ->where('id', 1)
    ->update(['status' => 'active']);

// Update with increment
$connection->table('posts')
    ->where('id', 1)
    ->update(['views' => $connection->raw('views + 1')]);

// Delete records
$deleted = $connection->table('users')
    ->where('status', 'inactive')
    ->delete();

// Delete by ID
$connection->table('users')->delete(5);

// Truncate table
$connection->table('users')->truncate();

// Basic transaction
$connection->transaction(function ($connection) {
    $connection->table('users')->insert([...]);
    $connection->table('posts')->insert([...]);
});

// Manual transaction control
$connection->beginTransaction();
try {
    // Your queries here
    $connection->commit();
} catch (Exception $e) {
    $connection->rollBack();
    throw $e;
}

// Transaction with retries
$connection->transaction(function ($connection) {
    // Your queries here
}, attempts: 3);

// Raw select
$users = $connection->table('users')
    ->select($connection->raw('COUNT(*) as user_count'))
    ->get();

// Raw where
$users = $connection->table('users')
    ->where('created_at', '>', $connection->raw('NOW() - INTERVAL 1 DAY'))
    ->get();

// Simple pagination
$page = 2;
$perPage = 15;

$users = $connection->table('users')
    ->page($page, $perPage)
    ->get();

// Manual limit/offset
$users = $connection->table('users')
    ->limit(10)
    ->offset(20)
    ->get();

$connection->table('users')->chunk(100, function ($users) {
    foreach ($users as $user) {
        // Process user
    }
});

// Stream millions of rows with minimal memory usage
foreach ($connection->table('users')->cursor() as $user) {
    // Process one user at a time
    // Memory usage stays constant regardless of dataset size
    processUser($user);
}

// Performance: 1M+ rows/second throughput
// Memory: ~8MB for 15,000 rows

// Enable query logging
$connection->enableQueryLog();

// Run queries
$users = $connection->table('users')->get();

// Get query log
$queries = $connection->getQueryLog();
print_r($queries);

// Get SQL without executing
$sql = $connection->table('users')
    ->where('active', true)
    ->toSql();
echo $sql; // select * from "users" where "active" = ?

// Get bindings
$bindings = $connection->table('users')
    ->where('active', true)
    ->getBindings();
print_r($bindings); // [true]

$connection = new Connection([
    'driver' => 'mysql',
    'host' => '127.0.0.1',
    'port' => 3306,
    'database' => 'database_name',
    'username' => 'username',
    'password' => 'password',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'options' => [
        PDO::ATTR_PERSISTENT => false,
    ]
]);

$connection = new Connection([
    'driver' => 'pgsql',
    'host' => '127.0.0.1',
    'port' => 5432,
    'database' => 'database_name',
    'username' => 'username',
    'password' => 'password',
    'prefix' => '',
    'schema' => 'public',
]);

$connection = new Connection([
    'driver' => 'sqlite',
    'database' => '/path/to/database.sqlite',
    'prefix' => '',
]);

// In-memory database (great for testing)
$connection = new Connection([
    'driver' => 'sqlite',
    'database' => ':memory:',
    'prefix' => '',
]);

use Bob\Database\Model;

class User extends Model
{
    protected $table = 'users';
    protected $primaryKey = 'id';
    protected $fillable = ['name', 'email', 'password'];

    // Define relationships
    public function posts()
    {
        return $this->hasMany(Post::class);
    }

    public function profile()
    {
        return $this->hasOne(Profile::class);
    }

    public function roles()
    {
        return $this->belongsToMany(Role::class, 'user_roles');
    }
}

class User extends Model
{
    public function profile()
    {
        return $this->hasOne(Profile::class);
    }
}

// Usage
$user = User::find(1);
$profile = $user->profile; // Automatically loads the profile

class User extends Model
{
    public function posts()
    {
        return $this->hasMany(Post::class);
    }
}

// Usage
$user = User::find(1);
$posts = $user->posts; // Returns a Collection of Post models

class Post extends Model
{
    public function author()
    {
        return $this->belongsTo(User::class, 'user_id');
    }
}

// Usage
$post = Post::find(1);
$author = $post->author; // Loads the User model

class User extends Model
{
    public function roles()
    {
        return $this->belongsToMany(Role::class, 'user_roles', 'user_id', 'role_id');
    }
}

// Usage
$user = User::find(1);
$roles = $user->roles; // Returns Collection of Role models

// Attach/Detach relationships
$user->roles()->attach($roleId);
$user->roles()->detach($roleId);
$user->roles()->sync([1, 2, 3]); // Sync to exact set of IDs

// Load users with their posts and comments
$users = User::with('posts.comments')->get();

// Multiple relationships
$users = User::with(['posts', 'profile', 'roles'])->get();

// Eager loading with constraints
$users = User::with(['posts' => function($query) {
    $query->where('published', true);
}])->get();

// Find by primary key
$user = User::find(1);

// Find or fail (throws exception)
$user = User::findOrFail(1);

// Create new record
$user = User::create([
    'name' => 'John Doe',
    'email' => '[email protected]'
]);

// Update existing
$user->update(['name' => 'Jane Doe']);

// Delete
$user->delete();

// Query builder integration
$users = User::where('active', true)
    ->orderBy('created_at', 'desc')
    ->limit(10)
    ->get();

// Aggregates
$count = User::where('role', 'admin')->count();
$avg = Product::avg('price');

$users = User::all();

// Collection methods
$admins = $users->filter(fn($user) => $user->role === 'admin');
$names = $users->pluck('name');
$grouped = $users->groupBy('role');
$sorted = $users->sortBy('created_at');

// Map over items
$emails = $users->map(fn($user) => $user->email);

// Check if collection contains item
$hasAdmin = $users->contains('role', 'admin');

use Bob\Schema\Schema;

// Create a new table
Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('email')->unique();
    $table->timestamp('email_verified_at')->nullable();
    $table->string('password');
    $table->rememberToken();
    $table->timestamps();
});

// Modify existing table
Schema::table('users', function (Blueprint $table) {
    $table->string('phone')->nullable()->after('email');
    $table->index('phone');
});

// Drop table
Schema::dropIfExists('users');

use Bob\Schema\Schema;

// Create WordPress-style post table
Schema::createWordPress('custom_posts', function (WordPressBlueprint $table) {
    $table->wpPost();           // All standard WordPress post columns
    $table->wpPostIndexes();    // Standard WordPress indexes
});

// Create custom meta table
Schema::createWordPress('custom_meta', function (WordPressBlueprint $table) {
    $table->wpMeta('custom');   // Creates: meta_id, custom_id, meta_key, meta_value
});

// Create WooCommerce order table
Schema::createWordPress('wc_custom_orders', function (WordPressBlueprint $table) {
    $table->wcOrder();          // All WooCommerce HPOS order columns
});

use Bob\Database\Migrations\Migration;
use Bob\Schema\Blueprint;
use Bob\Schema\Schema;

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('users');
    }
}

use Bob\Database\Migrations\MigrationRunner;
use Bob\Database\Migrations\MigrationRepository;
use Bob\Database\Connection;

$connection = new Connection([/* config */]);
$repository = new MigrationRepository($connection, 'migrations');
$runner = new MigrationRunner($connection, $repository, ['/path/to/migrations']);

// Run pending migrations
$runner->run();

// Rollback last batch
$runner->rollback();

// Rollback all migrations
$runner->reset();

// Drop all tables and re-run migrations
$runner->fresh();

// Rollback and re-run all migrations
$runner->refresh();

// Check migration status
$status = $runner->status();

use Bob\Schema\Inspector;

$inspector = new Inspector($connection);

// Get all tables
$tables = $inspector->getTables();

// Get table structure
$columns = $inspector->getColumns('users');
$indexes = $inspector->getIndexes('users');
$foreignKeys = $inspector->getForeignKeys('users');

// Generate migration from existing table
$migration = $inspector->generateMigration('users');
file_put_contents('2024_01_01_000000_create_users_table.php', $migration);

// Numeric types
$table->id();                      // Auto-incrementing BIGINT
$table->bigInteger('votes');
$table->integer('count');
$table->smallInteger('votes');
$table->tinyInteger('active');
$table->decimal('amount', 8, 2);
$table->float('amount');
$table->double('amount');

// String types
$table->string('name', 100);
$table->text('description');
$table->longText('content');
$table->char('code', 4);

// Date/Time types
$table->date('birthday');
$table->dateTime('created_at');
$table->timestamp('updated_at');
$table->time('sunrise');
$table->timestamps();              // created_at + updated_at

// Other types
$table->boolean('active');
$table->json('metadata');
$table->binary('data');
$table->uuid('identifier');
$table->enum('status', ['active', 'inactive']);

// Column modifiers
$table->string('email')->nullable();
$table->string('name')->default('Guest');
$table->string('slug')->unique();
$table->integer('position')->unsigned();
$table->text('bio')->comment('User biography');

// Indexes
$table->primary('id');
$table->unique('email');
$table->index('status');
$table->index(['user_id', 'created_at']);

// Foreign keys
$table->foreign('user_id')
    ->references('id')
    ->on('users')
    ->onDelete('cascade')
    ->onUpdate('cascade');

// Drop constraints
$table->dropPrimary('users_id_primary');
$table->dropUnique('users_email_unique');
$table->dropIndex('users_status_index');
$table->dropForeign('posts_user_id_foreign');

$baseQuery = $connection->table('users')->where('active', true);

// Clone for variations
$admins = $baseQuery->clone()->where('role', 'admin')->get();
$users = $baseQuery->clone()->where('role', 'user')->get();

$subquery = $connection->table('posts')
    ->select('user_id')
    ->where('published', true);

$users = $connection->table('users')
    ->whereIn('id', $subquery)
    ->get();

use Bob\Query\Grammar;

class CustomGrammar extends Grammar
{
    // Override methods for custom SQL generation
}

$connection->setQueryGrammar(new CustomGrammar());

use Bob\Database\Connection;

$connection = new Connection([
    'driver' => 'mysql',
    'host' => DB_HOST,
    'database' => DB_NAME,
    'username' => DB_USER,
    'password' => DB_PASSWORD,
    'prefix' => $wpdb->prefix,
]);

// Now use modern query building in WordPress!
$posts = $connection->table('posts')
    ->where('post_status', 'publish')
    ->orderBy('post_date', 'desc')
    ->limit(10)
    ->get();

use Bob\Database\Connection;

$connection = new Connection([
    'driver' => 'sqlite',
    'database' => 'database.sqlite',
]);

$users = $connection->table('users')
    ->where('active', true)
    ->get();

// In your service provider or bootstrap
$container->singleton(Connection::class, function () {
    return new Connection(config('database'));
});