PHP code example of callismart / dbprism

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

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

    

callismart / dbprism example snippets

use Callismart\DBPrism\Database;
use Callismart\DBPrism\Adapters\MysqliAdapter;
use Callismart\DBPrism\DBConfigDTO;

$config = new DBConfigDTO([
    'host'     => 'localhost',
    'username' => 'root',
    'password' => 'secret',
    'dbname'   => 'myapp',
    'driver'   => 'mysql',
]);

$adapter = new MysqliAdapter($config);
$db = new Database($adapter);

// Insert
$user_id = $db->insert('users', [
    'name'  => 'John Doe',
    'email' => '[email protected]',
]);

// Fetch
$user = $db->get_row('SELECT * FROM users WHERE id = ?', [$user_id]);

// Update
$db->update('users', 
    ['status' => 'active'],
    ['id' => $user_id]
);

// Delete
$db->delete('users', ['id' => $user_id]);

use Callismart\DBPrism\Query\SQLBuilder;

$builder = new SQLBuilder($db->get_driver());

// Build a SELECT query
$intent = $builder->select('id', 'name', 'email')
    ->from('users')
    ->where('status', '=', 'active')
    ->where('created_at', '>', '2024-01-01')
    ->order_by('created_at', 'DESC')
    ->limit(10);

$sql = $intent->build();
$bindings = $intent->get_bindings();

$users = $db->get_results($sql, $bindings);

use Callismart\DBPrism\Adapters\MysqliAdapter;

$config = new DBConfigDTO([
    'host'     => 'localhost',
    'username' => 'root',
    'password' => 'secret',
    'dbname'   => 'myapp',
    'driver'   => 'mysql',
]);

$adapter = new MysqliAdapter($config);
$db = new Database($adapter);

use Callismart\DBPrism\Adapters\PostgresAdapter;

$config = new DBConfigDTO([
    'host'     => 'localhost',
    'username' => 'postgres',
    'password' => 'secret',
    'dbname'   => 'myapp',
    'driver'   => 'pgsql',
]);

$adapter = new PostgresAdapter($config);
$db = new Database($adapter);

use Callismart\DBPrism\Adapters\SqliteAdapter;

$config = new DBConfigDTO([
    'dbname' => '/path/to/database.sqlite',
    'driver' => 'sqlite',
]);

$adapter = new SqliteAdapter($config);
$db = new Database($adapter);

use Callismart\DBPrism\Adapters\PdoAdapter;

$config = new DBConfigDTO([
    'dsn'      => 'mysql:host=localhost;dbname=myapp',
    'username' => 'root',
    'password' => 'secret',
    'driver'   => 'pdo',
]);

$adapter = new PdoAdapter($config);
$db = new Database($adapter);

use Callismart\DBPrism\Adapters\WPDBAdapter;

$adapter = new WPDBAdapter();
$db = new Database($adapter);

$builder = new SQLBuilder('mysql');

$intent = $builder->select('id', 'name', 'email')
    ->from('users');

$sql = $intent->build();
// SELECT `id`, `name`, `email` FROM `users`;

$intent = $builder->select('*')
    ->from('orders')
    ->where('status', '=', 'completed')
    ->where('total', '>', 100);

$sql = $intent->build();
$bindings = $intent->get_bindings();

// Basic comparison
->where('age', '>=', 18)
->where('name', '!=', 'Admin')
->where('email', 'LIKE', '%@example.com')

// IS NULL / IS NOT NULL
->where_null('deleted_at')
->where_not_null('verified_at')

// Direct SQL operators
->where('deleted_at', 'IS NULL')
->where('verified_at', 'IS NOT NULL')

// IN / NOT IN
->where_in('status', ['active', 'pending'])
->where_not_in('role', ['banned', 'suspended'])

// BETWEEN / NOT BETWEEN
->where_between('age', 18, 65)
->where_not_between('created_at', '2023-01-01', '2023-12-31')

// OR conditions
->where('status', '=', 'active')
->or_where('status', '=', 'pending')

// Grouped conditions
->where_group(function($q) {
    $q->where('status', '=', 'active')
      ->or_where('status', '=', 'pending');
})

// Raw SQL
->where_raw('YEAR(created_at) = 2024', [])

// INNER JOIN
->join('orders', 'users.id', '=', 'orders.user_id')

// LEFT JOIN
->left_join('profiles', 'users.id', '=', 'profiles.user_id')

// RIGHT JOIN
->right_join('departments', 'employees.dept_id', '=', 'departments.id')

// CROSS JOIN
->cross_join('statuses')

$intent = $builder->select('category', 'COUNT(*) as total')
    ->from('products')
    ->group_by('category')
    ->order_by('total', 'DESC')
    ->limit(10)
    ->offset(0);

$sql = $intent->build();

$intent = $builder->insert('users')
    ->values([
        'name'       => 'John Doe',
        'email'      => '[email protected]',
        'password'   => hash('sha256', 'secret'),
    ]);

$sql = $intent->build();
$bindings = $intent->get_bindings();

$intent = $builder->insert('users')
    ->multi_values([
        ['name' => 'John Doe', 'email' => '[email protected]'],
        ['name' => 'Jane Smith', 'email' => '[email protected]'],
        ['name' => 'Bob Wilson', 'email' => '[email protected]'],
    ]);

$sql = $intent->build();

$intent = $builder->insert('users')
    ->set(['name' => 'John Doe', 'email' => '[email protected]']);

$intent = $builder->update('users')
    ->set([
        'status'     => 'inactive',
        'updated_at' => date('Y-m-d H:i:s'),
    ])
    ->where('id', '=', 1);

$sql = $intent->build();

$intent = $builder->update('users')
    ->set([
        'verified' => true,
        'verified_at' => date('Y-m-d H:i:s'),
    ])
    ->where('email_confirmed', '=', true)
    ->where_null('deleted_at')
    ->where_in('status', ['pending', 'new']);

$intent = $builder->delete('users')
    ->where('id', '=', 1);

$sql = $intent->build();

$intent = $builder->delete('sessions')
    ->where('user_id', '=', 5)
    ->where('expires_at', '<', date('Y-m-d H:i:s'));

use Callismart\DBPrism\Utils\Column;
use Callismart\DBPrism\Utils\ColumnType;
use Callismart\DBPrism\Utils\Constraint;

$builder = new SQLBuilder($db->get_driver());

$intent = $builder->create_table('users')
    ->add_columns([
        Column::make('id')
            ->type(ColumnType::BIG_INT)
            ->auto_increment()
            ->unsigned()
            ->          ->b->exec($sql);

$intent = $builder->create_table('posts')
    ->add_columns([
        Column::make('id')
            ->type(ColumnType::BIG_INT)
            ->auto_increment()
            ->unsigned()
            ->pe::VARCHAR)
            ->size(200)
            ->IMESTAMP')),
    ])
    ->add_constraints([
        Constraint::primary('posts_pk')
            ->on('id'),
        
        Constraint::foreign_key('posts_user_fk')
            ->on('user_id')
            ->references('users', 'id')
            ->on_delete('CASCADE')
            ->on_update('CASCADE'),
    ]);

$sql = $intent->build();
$db->exec($sql);

use Callismart\DBPrism\Utils\ColumnType;

// Integer types
->type(ColumnType::INT)->size(11)
->type(ColumnType::BIG_INT)->unsigned()
->type(ColumnType::TINY_INT)
->type(ColumnType::SMALL_INT)

// String types
->type(ColumnType::VARCHAR)->size(100)
->type(ColumnType::CHAR)->size(10)
->type(ColumnType::TEXT)

// Numeric types
->type(ColumnType::DECIMAL)->precision(10, 2)
->type(ColumnType::FLOAT)
->type(ColumnType::DOUBLE)

// Date/Time types
->type(ColumnType::DATE)
->type(ColumnType::TIME)
->type(ColumnType::DATETIME)
->type(ColumnType::TIMESTAMP)

// Other types
->type(ColumnType::BOOLEAN)
->type(ColumnType::JSON)
->type(ColumnType::ENUM)

Column::make('email')
    ->type(ColumnType::VARCHAR)
    ->size(100)
    ->crement()        // AUTO_INCREMENT
    ->default('active')       // DEFAULT value
    ->default(DefaultColumnValue::expression('CURRENT_TIMESTAMP'))

$intent = $builder->alter_table('users')
    ->rename_column('old_name', 'new_name');

$sql = $intent->build();
$db->exec($sql);

$intent = $builder->truncate_table('logs')
    ->restart_identity(true)
    ->cascade(false);

$sql = $intent->build();
$db->exec($sql);

$intent = $builder->drop_table('old_table')
    ->if_exists();

$sql = $intent->build();
$db->exec($sql);

use Callismart\DBPrism\Migrations\Helpers\TableHelper;

$helper = new TableHelper($db, new SQLBuilder($db->get_driver()), 'users');

// Rename table
$helper->rename('new_table_name');

// Truncate table
$helper->truncate(restart: true, cascade: false);

// Drop table
$helper->drop(exists_check: true);

// Drop index
$helper->drop_index('idx_created_at');

// Access column operations
$helper->column();

// Access constraint operations
$helper->constraint();

use Callismart\DBPrism\Migrations\Helpers\ColumnHelper;

$helper = new ColumnHelper($db, new SQLBuilder($db->get_driver()), 'users');

// Add column
$helper->add(Column::make('phone')
    ->type(ColumnType::VARCHAR)
    ->size(20)
    ->nullable()
);

// Drop column
$helper->drop('deprecated_field');

// Rename column
$helper->rename('old_name', 'new_name');

// Modify column
$helper->modify(Column::make('name')
    ->type(ColumnType::VARCHAR)
    ->size(255)
);

// Change column type
$helper->changeType('status', ColumnType::ENUM);

// Check operations
$helper->exists('email');           // bool
$helper->getType('email');          // string|null
$helper->list();                    // array of column names

use Callismart\DBPrism\Inspection\Inspector;

$inspector = new Inspector($db);

// List all tables
$tables = $inspector->get_all_tables();

// Check if table exists
if ($inspector->table_exists('users')) {
    echo "Table exists!";
}

// Get table metadata
$meta = $inspector->get_table_metadata('users');
// Returns: ['engine' => 'InnoDB', 'charset' => 'utf8mb4', 'collation' => '...', 'row_count' => 1250, 'comment' => '']

// Get all column names
$columns = $inspector->get_columns('users');

// Check if column exists
$inspector->column_exists('users', 'email');

// Get column type (normalized)
$type = $inspector->get_column_type('users', 'id');

// Get detailed column information
$details = $inspector->get_column_details('users');

// Check if column is nullable
$inspector->is_column_nullable('users', 'email');

// Get column default
$inspector->get_column_default('users', 'is_active');

// Get all indexes
$indexes = $inspector->get_indexes('users');

// Check if index exists
$inspector->has_index('users', 'idx_created_at');

// Get primary key
$pk = $inspector->get_primary_key('users');
// Returns: ['id'] or null

// Get all foreign keys
$fks = $inspector->get_foreign_keys('orders');

// Check if foreign key exists
$inspector->has_foreign_key('orders', 'fk_orders_user');

// Get unique constraints
$unique = $inspector->get_unique_constraints('users');

// Get check constraints
$checks = $inspector->get_check_constraints('products');

// Get database engine type
$engine = $inspector->get_engine_type();  // 'mysql', 'pgsql', 'sqlite'

// Get server version
$version = $inspector->get_server_version();

// Get protocol version
$protocol = $inspector->get_protocol_version();

// Get host info
$host = $inspector->get_host_info();

try {
    $result = $db->transactional(function() use ($db) {
        $order_id = $db->insert('orders', ['total' => 99.99]);
        $db->insert('order_items', ['order_id' => $order_id, 'product_id' => 5]);
        return $order_id;
    });
} catch (\Throwable $e) {
    error_log("Transaction failed: " . $e->getMessage());
}

   // ✓ Good
   $user = $db->get_row('SELECT * FROM users WHERE id = ?', [$id]);
   
   // ✗ Bad - SQL injection vulnerability
   $user = $db->get_row("SELECT * FROM users WHERE id = {$id}");
   

   $db->transactional(function() use ($db) {
       $order_id = $db->insert('orders', $order_data);
       foreach ($items as $item) {
           $db->insert('order_items', [...$item, 'order_id' => $order_id]);
       }
   });
   

   $inspector = new Inspector($db);
   if ($inspector->table_exists('users') && 
       $inspector->column_exists('users', 'email')) {
       // Safe to query
   }
   

   try {
       $result = $db->transactional(function() use ($db) {
           // operations
       });
   } catch (\Throwable $e) {
       error_log($e->getMessage());
   }