view release on metacpan or  search on metacpan

t/152-sql-consistency.t  view on Meta::CPAN

#!/usr/bin/env perl

use strict;
use warnings;

use Test::More;
use Test::Deep;
use File::Temp;

use lib 't/lib';

use IO::Async::Loop;
use DBIx::Class::Async::Schema;

my $loop = IO::Async::Loop->new;
my ($fh, $db_file) = File::Temp::tempfile(UNLINK => 1);

my $schema = DBIx::Class::Async::Schema->connect(
    "dbi:SQLite:dbname=$db_file", undef, undef, {},
    {
        workers      => 2,
        schema_class => 'TestSchema',
        async_loop   => $loop,
        cache_ttl    => 60,
    },
);

ok($schema, 'Schema created successfully');

$schema->await($schema->deploy({ add_drop_table => 1 }));

subtest 'SQL consistency with default columns' => sub {
    my %seen_sqls;
    my $iterations = 10;

    for my $i (1..$iterations) {
        my $rs = $schema->resultset('User')->search({});
        my $query = $rs->as_query;
        my $sql = normalise_sql(extract_sql($query));
        $seen_sqls{$sql}++;
    }

    is(scalar keys %seen_sqls, 1,
        'Default column query generates consistent SQL across all iterations');

    my ($sql) = keys %seen_sqls;
    like($sql, qr/SELECT .+ FROM users/,
        'SQL contains expected SELECT FROM structure');

    my $columns = extract_columns($sql);
    ok(length($columns) > 0, 'SQL contains column list');
};

subtest 'SQL consistency with explicit columns' => sub {
    my %seen_sqls;
    my $iterations = 10;
    my @explicit_cols = qw/id name email age active settings balance/;

    for my $i (1..$iterations) {
        my $rs = $schema->resultset('User')->search({}, {
            columns => \@explicit_cols
        });
        my $query = $rs->as_query;
        my $sql = normalise_sql(extract_sql($query));
        $seen_sqls{$sql}++;
    }

    is(scalar keys %seen_sqls, 1,
        'Explicit column query generates consistent SQL across all iterations');

    my ($sql) = keys %seen_sqls;
    like($sql, qr/SELECT .+ FROM users/,
        'SQL contains expected SELECT FROM structure');

    my $columns = extract_columns($sql);
    for my $col (@explicit_cols) {
        like($columns, qr/\b$col\b/, "SQL includes column '$col'");
    }
};

subtest 'Column order remains stable' => sub {
    my @column_orders;

    for my $i (1..5) {
        my $rs = $schema->resultset('User')->search({});
        my $query = $rs->as_query;
        my $sql = normalise_sql(extract_sql($query));
        my $columns = extract_columns($sql);
        push @column_orders, $columns;
    }

    # Check all orders are identical
    my $first_order = $column_orders[0];
    my $all_same = 1;
    for my $order (@column_orders) {
        if ($order ne $first_order) {
            $all_same = 0;
            last;
        }
    }

    ok($all_same, 'Column order is identical across multiple invocations');
    ok(length($first_order) > 0, 'Column order is non-empty');
};

subtest 'Chained search generates consistent SQL' => sub {
    my %seen_sqls;

    for my $i (1..5) {
        my $rs = $schema->resultset('User')
                        ->search({ active => 1 })
                        ->search({ age => { '>' => 18 } });
        my $query = $rs->as_query;
        my $sql = normalise_sql(extract_sql($query));
        $seen_sqls{$sql}++;
    }

    is(scalar keys %seen_sqls, 1,
        'Chained search generates consistent SQL');

    my ($sql) = keys %seen_sqls;
    like($sql, qr/active.*age|age.*active/i,
        'SQL contains both search conditions');
};

subtest 'Attribute merging is deterministic' => sub {
    my %seen_sqls;

    for my $i (1..5) {