/ 
DBIx-Fast-0.17
/ DBIx::Fast::SQL

NAME

DBIx::Fast::SQL - Query and CRUD methods of DBIx::Fast

SYNOPSIS

# These methods are composed into DBIx::Fast itself:
my $rows = $db->all('SELECT * FROM users WHERE status = ?', 1);
my $id   = $db->insert('users', { name => 'Alice' });

DESCRIPTION

Object::Pad role composed into DBIx::Fast. It owns the statement state (sql, p, results, last_id, last_sql - same accessors as always) and provides every query and CRUD method. You never use this module directly: all methods below are called on the DBIx::Fast object.

Every method below takes raw SQL; values are passed as ? placeholders (or :name for "execute") and bound, never interpolated. For a task-oriented overview see "RAW SQL" in DBIx::Fast.

QUERY METHODS

all

$db->all('SELECT * FROM users WHERE id > ?', 10);
my $rows = $db->results;  # arrayref of hashrefs

Executes SQL and stores all rows in results.

hash

$db->hash('SELECT * FROM users WHERE id = ?', 1);
my $row = $db->results;  # hashref

Executes SQL and stores a single row in results.

val

my $name = $db->val('SELECT name FROM users WHERE id = ?', 1);

Executes SQL and returns a single scalar value: the first column of the first row. Intended for single-column SELECTs; with multiple columns the extra ones are ignored.

flat

my @names = $db->flat('SELECT name FROM users');

Executes SQL and returns a flat list of values.

array

$db->array('SELECT name FROM users');
my $names = $db->results;  # arrayref

Executes SQL and stores a single column as arrayref in results.

query

my $rs = $db->query('SELECT id, name FROM users WHERE active = ?', 1);
while (my $row = $rs->hash) {
    say "$row->{id}: $row->{name}";
}

Executes SQL with bind parameters and returns a DBIx::Fast::Result iterator. Unlike all, this does not load the entire result set into memory - rows are fetched one at a time via hash, array, all, or flat on the result object. The statement handle is automatically finished when the result object goes out of scope.

count

my $total = $db->count('users');
my $active = $db->count('users', { status => 1 });

Returns row count, optionally filtered by WHERE conditions.

exec

$db->exec('CREATE TABLE foo (id INT)');
$db->exec('INSERT INTO foo VALUES (?)', 42);
$db->exec('UPDATE foo SET id = ? WHERE id = ?', 99, 42);
my $sth = $db->exec('SELECT * FROM foo WHERE id > ?', 0);

The general-purpose raw-SQL method: runs any statement (DDL, DML or vendor-specific SQL) with optional ? bind parameters and returns the statement handle. Records the query in the profiler when one is active. For a SELECT you usually want all/hash/val/query instead, which fetch the rows for you.

execute

$db->execute('SELECT * FROM users WHERE name = :name', { name => 'Alice' });
$db->execute('SELECT * FROM users WHERE id = :id', { id => 1 }, 'hash');

Executes SQL with named parameter substitution (:name syntax). Third argument selects result type: arrayref (default) or hash.

CRUD METHODS

insert

$db->insert('users', { name => 'Alice', status => 1 });
$db->insert('users', { name => 'Alice' }, time => 'created_at');
my $id = $db->last_id;

Inserts a row using SQL::Abstract. Sets last_id automatically based on the database driver.

update

$db->update('users', {
    sen   => { name => 'Bob', status => 1 },
    where => { id => 1 },
});
$db->update('users', {
    sen   => { name => 'Bob' },
    where => { id => 1 },
}, time => 'updated_at');

Updates rows using SQL::Abstract. Pass time => 'column_name' to auto-set a timestamp column to now().

up

$db->up('users', { name => 'Bob' }, { id => 1 });
$db->up('users', { name => 'Bob' }, { id => 1 }, 'updated_at');

Shortcut for update. Arguments: table, data hashref, where hashref, optional time column name (positional).

delete

$db->delete('users', { id => 1 });

Deletes rows using SQL::Abstract.

upsert

$db->upsert('products',
    { sku => 'X', name => 'Widget', stock => 5, price => 9.99 },
    ['sku'],                  # conflict keys (UNIQUE / PRIMARY KEY)
    [qw(stock price)],        # optional: columns to update on conflict
);

Inserts the row, or updates the conflict-targeted columns if a row with matching conflict keys already exists. Translates to driver-native syntax (ON CONFLICT (...) DO UPDATE for PostgreSQL and SQLite 3.24+, ON DUPLICATE KEY UPDATE for MariaDB and MySQL).

If update_cols is omitted, every row column that is not in conflict_keys is updated. If after that the update set is empty, an exception is raised - use a regular insert if you do not want to update anything on conflict.

last_id is not updated by upsert because the operation may have been an insert or an update.

insert_many

$db->insert_many('orders', [
    { customer_id => 1, total => 99 },
    { customer_id => 2, total => 45 },
]);

Inserts multiple rows in a single statement. All rows must share the same column set (same keys); rows whose keys differ from the first one raise an exception. Returns the number of rows inserted. last_id is set to the first inserted row id (driver convention).

For very large arrays you should chunk the input manually to stay within the driver's max_allowed_packet / statement-size limits.

upsert_many

$db->upsert_many('products', \@rows, ['sku'], [qw(stock price)]);

Bulk INSERT with per-row conflict resolution, combining insert_many and upsert. Same column-uniformity rule as insert_many. Returns the number of rows in the batch.

make_sen

$db->sql('SELECT * FROM users WHERE name = :name AND age = :age');
$db->make_sen({ name => 'Alice', age => 30 });
# $db->sql is now 'SELECT * FROM users WHERE name = ? AND age = ?'
# $db->p is ['Alice', 30]

Replaces named placeholders (:name) with ? in order of appearance in the SQL string. Supports duplicate placeholders. Quoted string literals ('...' / "...") and PostgreSQL ::cast operators are left untouched.

Named parameters are intended for simple SQL: the literal-skipping is based on straight single/double quotes and does not understand backslash escapes ('a\'b'), PostgreSQL dollar-quoting ($$...$$), or SQL comments. For SQL using those constructs, use positional ? placeholders instead.

q

$db->q('SELECT * FROM users WHERE id = ?', 1);

Sets sql and p (bind parameters) for subsequent use.

execute_prepare

$db->sql('INSERT INTO users (name) VALUES (?)');
$db->execute_prepare('Alice');

Prepares and executes the current sql with bind parameters.

SEE ALSO

DBIx::Fast - constructor, accessors, subsystems and the SECURITY notes that apply to these methods.

AUTHOR

SeHarrys

LICENSE

This is free software under the Artistic License 2.0.