DatabaseInterface.php
PHP
Path: src/Database/DatabaseInterface.php
<?php
namespace mini\Database;
/**
* Database interface for the Mini framework
*
* Provides a clean abstraction over database operations while maintaining
* the simple, ergonomic API that makes database work pleasant.
*/
interface DatabaseInterface
{
/**
* Execute a SELECT query and return a composable Query
*
* Returns a Query that can be iterated, further composed, or used
* for updates/deletes (if single-table). Rows are returned as stdClass objects.
*
* Example:
* ```php
* // Iterate directly
* foreach (db()->query('SELECT * FROM users WHERE active = ?', [1]) as $row) {
* echo $row->name;
* }
*
* // Compose further
* $admins = db()->query('SELECT * FROM users')
* ->eq('role', 'admin')
* ->order('name')
* ->limit(10);
*
* // Use for delete (single-table queries only)
* db()->delete(db()->query('SELECT * FROM users')->eq('status', 'inactive'));
* ```
*
* @param string $sql SQL query with parameter placeholders
* @param array $params Parameters to bind to the query
* @return Query Composable query object
*/
public function query(string $sql, array $params = []): Query;
/**
* Execute query and return first row only as object
*
* @param string $sql SQL query with parameter placeholders
* @param array $params Parameters to bind to the query
* @return object|null Row as object, or null if no results
*/
public function queryOne(string $sql, array $params = []): ?object;
/**
* Execute query and return first column of first row
*
* Useful for COUNT(), MAX(), single field lookups, etc.
*
* @param string $sql SQL query with parameter placeholders
* @param array $params Parameters to bind to the query
* @return mixed The field value, or null if no results
*/
public function queryField(string $sql, array $params = []): mixed;
/**
* Execute query and return first column values as array
*
* Useful for getting arrays of IDs, names, etc.
*
* @param string $sql SQL query with parameter placeholders
* @param array $params Parameters to bind to the query
* @return array Array of scalar values from first column
*/
public function queryColumn(string $sql, array $params = []): array;
/**
* Execute a statement (INSERT, UPDATE, DELETE)
*
* @param string $sql SQL statement with parameter placeholders
* @param array $params Parameters to bind to the statement
* @return int Number of affected rows
*/
public function exec(string $sql, array $params = []): int;
/**
* Get the last inserted row ID
*
* @return string|null The row ID of the last inserted row, or null
*/
public function lastInsertId(): ?string;
/**
* Check if a table exists in the database
*
* Note: Implementation may be database-specific
*
* @param string $tableName Name of the table to check
* @return bool True if table exists, false otherwise
*/
public function tableExists(string $tableName): bool;
/**
* Execute a closure within a database transaction
*
* Starts a transaction, executes the closure, and commits if successful.
* If the closure throws an exception, the transaction is rolled back.
*
* The closure receives the DatabaseInterface as its first parameter.
*
* Example:
* ```php
* db()->transaction(function(DatabaseInterface $db) {
* $db->exec("INSERT INTO users (name) VALUES (?)", ['John']);
* $db->exec("INSERT INTO logs (action) VALUES (?)", ['user_created']);
* });
* ```
*
* @param \Closure $task The task to execute within the transaction
* @return mixed The return value of the closure
* @throws \RuntimeException If the backend doesn't support transactions
* @throws \RuntimeException If called while already in a transaction (nested transactions not supported)
* @throws \Throwable Re-throws any exception from the closure after rollback
*/
public function transaction(\Closure $task): mixed;
/**
* Get the SQL dialect for this database connection
*
* Used by PartialQuery to generate dialect-specific SQL.
* Implementations should detect the dialect from the connection.
*
* @return SqlDialect SQL dialect enum
*/
public function getDialect(): SqlDialect;
/**
* Quotes a value for safe use in SQL query strings
*
* WARNING: Quoting parameters this way is prone to vulnerabilities
* if done incorrectly - especially if the database connection has
* a different character set than expected. Prefer using parameterized
* queries via the query() and exec() methods whenever possible. This
* function is provided for edge cases, debugging and dynamic SQL generation.
*
* @param mixed $value Value to quote
* @return string Quoted value safe for SQL
*/
public function quote(mixed $value): string;
/**
* Quotes an identifier (table name, column name) for safe use in SQL
*
* Handles reserved words and special characters in identifiers.
*
* @param string $identifier Table or column name
* @return string Quoted identifier safe for SQL
*/
public function quoteIdentifier(string $identifier): string;
/**
* Delete rows matching a query
*
* Respects WHERE clauses and LIMIT from the query.
* Ignores SELECT, ORDER BY, and OFFSET.
*
* Example:
* ```php
* $deleted = db()->delete(User::inactive());
* ```
*
* @param Query|PartialQuery $query Query defining which rows to delete
* @return int Number of affected rows
*/
public function delete(Query|PartialQuery $query): int;
/**
* Update rows matching a query
*
* Respects WHERE clauses and LIMIT from the query.
* Ignores SELECT, ORDER BY, and OFFSET.
*
* Use string for raw SQL expressions:
* ```php
* db()->update($query, 'login_count = login_count + 1')
* db()->update($query, 'last_seen = ?, status = ?', [$now, 'active'])
* ```
*
* Use array for simple assignments (values passed as-is):
* ```php
* db()->update($query, ['status' => 'archived', 'updated_at' => date('Y-m-d H:i:s')])
* ```
*
* WARNING: Values are NOT converted automatically. You must handle
* conversion yourself (dates to strings, objects to JSON, etc).
*
* @param Query|PartialQuery $query Query defining which rows to update
* @param string|array $set Either raw SQL expression or ['column' => 'value'] array
* @param array $params Parameters for placeholders in SQL expression (only used when $set is string)
* @return int Number of affected rows
*/
public function update(Query|PartialQuery $query, string|array $set, array $params = []): int;
/**
* Insert a new row into a table
*
* Performs a simple INSERT operation. Use this when you know the row doesn't exist.
* For INSERT or UPDATE behavior, use upsert() instead.
*
* Example:
* ```php
* db()->insert('users', ['name' => 'John', 'email' => 'john@example.com']);
* $userId = db()->lastInsertId();
* ```
*
* WARNING: Values are NOT converted automatically. You must handle
* conversion yourself (dates to strings, JSON encoding, etc).
*
* @param string $table Table name
* @param array $data Associative array of column => value pairs
* @return string The last insert ID (for auto-increment columns)
* @throws \InvalidArgumentException If data is empty
*/
public function insert(string $table, array $data): string;
/**
* Insert a row, or update if conflict on unique columns
*
* Performs an "UPSERT" operation. If a row with the specified unique
* column values already exists, updates that row instead of inserting.
*
* Example:
* ```php
* db()->upsert('users', ['email' => 'john@example.com', 'name' => 'John'], 'email');
* db()->upsert('user_prefs', ['user_id' => 1, 'key' => 'theme', 'value' => 'dark'], 'user_id', 'key');
* ```
*
* Note: MySQL's ON DUPLICATE KEY UPDATE ignores $conflictColumns and triggers
* on any unique constraint. Behavior may differ across databases.
*
* @param string $table Table name
* @param array $data Associative array of column => value pairs
* @param string ...$conflictColumns Column(s) that define uniqueness
* @return int Number of affected rows
*/
public function upsert(string $table, array $data, string ...$conflictColumns): int;
/**
* Create a VirtualDatabase with shadowed tables
*
* Returns a VirtualDatabase where specified tables are replaced with mock data
* while all other tables remain accessible. Perfect for testing - shadow specific
* tables while keeping real data for others.
*
* For PDODatabase: Creates VirtualDatabase with all real tables (as PartialQuery),
* then shadows with provided tables. Allows JOINs between mock and real data.
*
* For VirtualDatabase: Inherits all existing tables, shadows with provided ones.
*
* Example:
* ```php
* // Shadow users table with mock data, keep real orders
* $testDb = $db->withTables(['users' => $mockUsers]);
*
* // Can now JOIN mock users with real orders
* $testDb->query('SELECT u.name, o.amount FROM users u JOIN orders o ON u.id = o.user_id');
* ```
*
* @param array<string, \mini\Table\Contracts\TableInterface> $tables Table name => TableInterface to shadow
* @return DatabaseInterface VirtualDatabase with real + shadowed tables
*/
public function withTables(array $tables): DatabaseInterface;
/**
* Get database schema as a TableInterface
*
* Returns schema metadata as a queryable table without creating temp tables.
* The table can be filtered, iterated, and composed like any other TableInterface.
*
* Schema columns:
* - table_name: Name of the table
* - name: Column name or index name
* - type: 'column', 'primary', 'unique', or 'index'
* - data_type: Data type for columns (null for indexes)
* - is_nullable: 1 if nullable, 0 if NOT NULL (null for indexes)
* - default_value: Default value expression (null for indexes)
* - ordinal: Position (1-based for columns, null for indexes)
* - extra: For indexes: comma-separated list of indexed columns
*
* Example:
* ```php
* // Get all columns for a table
* $columns = db()->getSchema()->eq('type', 'column')->eq('table_name', 'users');
*
* // List all tables
* $tables = db()->getSchema()->eq('type', 'column')->select('table_name')->distinct();
*
* // Find all indexes
* $indexes = db()->getSchema()->in('type', ['primary', 'unique', 'index']);
*
* // Find primary key for a table
* $pk = db()->getSchema()->eq('table_name', 'users')->eq('type', 'primary')->one();
* ```
*
* @return \mini\Table\Contracts\TableInterface Schema table
*/
public function getSchema(): \mini\Table\Contracts\TableInterface;
}