Database Class

The Database class is the main entry point for database operations in the Kotchasan Framework. It provides connection management, Query Builder creation, and transaction handling capabilities.

Table of Contents

• Overview
• Installation and Setup
• Basic Usage
• Query Builders
• Transaction Management
• Cache Management
• All Methods
• Usage Examples
• Important Notes
• Related Classes
• Setup
• Main Features
• Advanced Examples
• API Reference
• Gotchas / Common Mistakes
• Additional Resources

Overview

The Database class acts as a bridge between your application and the database, providing:

• Multi-database connection management
• Query Builder creation for SELECT, INSERT, UPDATE, DELETE operations
• Transaction management
• Query caching capabilities
• Operation logging

Installation and Setup

Database Configuration

Create a settings/database.php file for database configuration:

return [
    'default' => [
        'driver' => 'mysql',
        'host' => 'localhost',
        'database' => 'kotchasan_db',
        'username' => 'root',
        'password' => '',
        'prefix' => 'app_',
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci'
    ],
    'cache' => [
        'driver' => 'mysql',
        'host' => 'localhost',
        'database' => 'cache_db',
        'username' => 'cache_user',
        'password' => 'cache_pass'
    ],
    'tables' => [
        'users' => 'members',
        'posts' => 'articles'
    ]
];

Configuration via Code

use Kotchasan\Database;

// Configure database connections
Kotchasan\Database::config([
    'default' => [
        'driver' => 'mysql',
        'host' => 'localhost',
        'database' => 'myapp',
        'username' => 'user',
        'password' => 'pass'
    ]
]);

Basic Usage

Creating Database Instance

use Kotchasan\Database;

// Create instance with default connection
$db = Kotchasan\Database::create();

// Create instance with specific connection
$cacheDb = Kotchasan\Database::create('cache');

Simple Data Retrieval

// Get all records from table
$users = $db->all('users');

// Get first record
$user = $db->first('users');

// Get records with conditions
$activeUsers = $db->get('users', ['status' => 'active']);

Query Builders

SELECT Query

// Basic SELECT
$users = $db->select(['id', 'name', 'email'])
    ->from('users')
    ->where(['status' => 'active'])
    ->orderBy('name')
    ->limit(10)
    ->fetchAll();

// SELECT with JOIN
$posts = $db->select(['p.title', 'u.name as author'])
    ->from('posts', 'p')
    ->leftJoin('users u', 'p.user_id = u.id')
    ->where(['p.published' => 1])
    ->fetchAll();

// SELECT with Subquery
$subquery = $db->select('COUNT(*)')
    ->from('comments')
    ->where(['post_id', '=', \Kotchasan\Database\Sql::column('p.id')]);

$posts = $db->select(['title', [$subquery, 'comment_count']])
    ->from('posts', 'p')
    ->fetchAll();

INSERT Query

// Insert single row
$result = $db->insert('users')
    ->values([
        'name' => 'John Doe',
        'email' => 'john@example.com',
        'created_at' => date('Y-m-d H:i:s')
    ])
    ->execute();

$userId = $db->lastInsertId();

// Insert multiple rows
$db->insert('users')
    ->rows([
        ['name' => 'Alice', 'email' => 'alice@example.com'],
        ['name' => 'Bob', 'email' => 'bob@example.com']
    ])
    ->execute();

UPDATE Query

// Update with conditions
$db->update('users')
    ->set([
        'last_login' => date('Y-m-d H:i:s'),
        'login_count' => \Kotchasan\Database\Sql::raw('login_count + 1')
    ])
    ->where(['id' => $userId])
    ->execute();

// Update with complex conditions
$db->update('posts')
    ->set(['status' => 'published'])
    ->where(['author_id' => $authorId])
    ->where(['created_at', '>=', '2024-01-01'])
    ->execute();

DELETE Query

// Delete with conditions
$db->delete('users')
    ->where(['status' => 'inactive'])
    ->where(['last_login', '<', '2023-01-01'])
    ->execute();

// Delete with LIMIT
$db->delete('logs')
    ->where(['level' => 'debug'])
    ->orderBy('created_at')
    ->limit(1000)
    ->execute();

Transaction Management

Basic Transaction

// Begin transaction
$db->beginTransaction();

try {
    // Perform multiple operations
    $db->insert('orders')
        ->values(['user_id' => $userId, 'total' => $total])
        ->execute();

    $orderId = $db->lastInsertId();

    $db->insert('order_items')
        ->values(['order_id' => $orderId, 'product_id' => $productId])
        ->execute();

    // Commit transaction
    $db->commit();

} catch (Exception $e) {
    // Rollback transaction
    $db->rollback();
    throw $e;
}

Transaction with Callback

$result = $db->transaction(function($db) use ($userId, $productId) {
    // Operations within transaction
    $orderId = $db->insert('orders')
        ->values(['user_id' => $userId])
        ->execute();

    $db->insert('order_items')
        ->values(['order_id' => $orderId, 'product_id' => $productId])
        ->execute();

    return $orderId;
});

Cache Management

Cache Configuration

use Kotchasan\Database;

// Configure cache with array
$queryCache = Kotchasan\Database::configureCache([
    'driver' => 'file',
    'path' => '/tmp/cache'
], 3600); // TTL 1 hour

// Configure cache with Cache instance
use Kotchasan\Cache\FileCache;

$cache = new FileCache('/tmp/cache');
$queryCache = Kotchasan\Database::setCache($cache, 7200); // TTL 2 hours

Using Cache

// Query will be cached automatically
$users = $db->select('*')
    ->from('users')
    ->where(['status' => 'active'])
    ->cache(300) // Cache for 5 minutes
    ->fetchAll();

All Methods

Static Methods

Instance Methods

Usage Examples

User Management System

class UserManager
{
    private $db;

    public function __construct()
    {
        $this->db = Kotchasan\Database::create();
    }

    public function createUser($userData)
    {
        return $this->db->transaction(function($db) use ($userData) {
            // Create user
            $userId = $db->insert('users')
                ->values([
                    'username' => $userData['username'],
                    'email' => $userData['email'],
                    'password' => password_hash($userData['password'], PASSWORD_DEFAULT),
                    'created_at' => date('Y-m-d H:i:s')
                ])
                ->execute();

            // Create profile
            $db->insert('user_profiles')
                ->values([
                    'user_id' => $userId,
                    'first_name' => $userData['first_name'],
                    'last_name' => $userData['last_name']
                ])
                ->execute();

            return $userId;
        });
    }

    public function getUserWithProfile($userId)
    {
        return $this->db->select([
                'u.id', 'u.username', 'u.email',
                'p.first_name', 'p.last_name'
            ])
            ->from('users', 'u')
            ->leftJoin('user_profiles p', 'u.id = p.user_id')
            ->where(['u.id' => $userId])
            ->first();
    }

    public function getActiveUsers($limit = 10)
    {
        return $this->db->select(['id', 'username', 'email', 'last_login'])
            ->from('users')
            ->where(['status' => 'active'])
            ->orderBy('last_login', 'DESC')
            ->limit($limit)
            ->cache(300) // Cache for 5 minutes
            ->fetchAll();
    }
}

Blog Management System

class BlogManager
{
    private $db;

    public function __construct()
    {
        $this->db = Kotchasan\Database::create();
    }

    public function getPostsWithCommentCount($page = 1, $perPage = 10)
    {
        $offset = ($page - 1) * $perPage;

        // Subquery for comment count
        $commentCount = $this->db->select('COUNT(*)')
            ->from('comments')
            ->where(['post_id', '=', \Kotchasan\Database\Sql::column('p.id')])
            ->where(['status' => 'approved']);

        return $this->db->select([
                'p.id', 'p.title', 'p.content', 'p.created_at',
                'u.username as author',
                [$commentCount, 'comment_count']
            ])
            ->from('posts', 'p')
            ->leftJoin('users u', 'p.author_id = u.id')
            ->where(['p.status' => 'published'])
            ->orderBy('p.created_at', 'DESC')
            ->limit($perPage, $offset)
            ->fetchAll();
    }

    public function publishPost($postId, $authorId)
    {
        return $this->db->transaction(function($db) use ($postId, $authorId) {
            // Check permissions
            $post = $db->select('author_id')
                ->from('posts')
                ->where(['id' => $postId])
                ->first();

            if (!$post || $post->author_id != $authorId) {
                throw new Exception('Unauthorized');
            }

            // Update status
            $db->update('posts')
                ->set([
                    'status' => 'published',
                    'published_at' => date('Y-m-d H:i:s')
                ])
                ->where(['id' => $postId])
                ->execute();

            // Log activity
            $db->insert('activity_log')
                ->values([
                    'user_id' => $authorId,
                    'action' => 'post_published',
                    'target_id' => $postId,
                    'created_at' => date('Y-m-d H:i:s')
                ])
                ->execute();

            return true;
        });
    }
}

Error Handling

class DatabaseErrorHandler
{
    private $db;

    public function __construct()
    {
        $this->db = Kotchasan\Database::create();

        // Set up logger
        $logger = new FileLogger('/var/log/database.log');
        Kotchasan\Database::setLogger($logger);
    }

    public function safeInsert($table, $data)
    {
        try {
            $result = $this->db->insert($table)
                ->values($data)
                ->execute();

            if (!$result) {
                $error = $this->db->getLastError();
                throw new Exception("Insert failed: {$error}");
            }

            return $this->db->lastInsertId();

        } catch (Exception $e) {
            // Log error
            error_log("Kotchasan\Database error: " . $e->getMessage());

            // Re-throw exception
            throw $e;
        }
    }

    public function getLastQuery()
    {
        return $this->db->getLastQuery();
    }
}

Important Notes

1. Database Configuration: Should be set in settings/database.php for security
2. Transactions: Use for operations requiring data integrity
3. Caching: Use for queries that don't change frequently
4. Error Handling: Always use try-catch and log errors
5. Performance: Use indexes and LIMIT when necessary

Related Classes

• DB - Utility class for simple database operations
• QueryBuilder - SQL Query builder
• Connection - Database connection management
• Cache - Query caching system