packages/ztd-query-postgres/tests/Unit/Sql/Sampling/PgSqlTableSampleParserTest.php

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Sql\Sampling;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\TestWith;
9use PHPUnit\Framework\Attributes\UsesClass;
10use PHPUnit\Framework\TestCase;
11use ZtdQuery\Exception\UnsupportedSqlException;
12use ZtdQuery\Platform\Postgres\Sql\Relation\PgSqlSelectRelationParser;
13use ZtdQuery\Platform\Postgres\Sql\Sampling\PgSqlTableSample;
14use ZtdQuery\Platform\Postgres\Sql\Sampling\PgSqlTableSampleMethod;
15use ZtdQuery\Platform\Postgres\Sql\Sampling\PgSqlTableSampleParser;
16
17#[CoversClass(PgSqlTableSampleParser::class)]
18#[UsesClass(PgSqlSelectRelationParser::class)]
19#[UsesClass(PgSqlTableSample::class)]
20#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\PgSqlLexerProfile::class)]
21#[CoversClass(\ZtdQuery\Platform\Postgres\Sql\Sampling\SampleClause::class)]
22#[CoversClass(\ZtdQuery\Platform\Postgres\Sql\Sampling\SampleTokens::class)]
23#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Relation\FromClause::class)]
24#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Relation\RelationReference::class)]
25#[UsesClass(PgSqlTableSampleMethod::class)]
26final class PgSqlTableSampleParserTest extends TestCase
27{
28    public function testParsesSchemaAliasExpressionAndRepeatableSeed(): void
29    {
30        $sql = 'SELECT d.id FROM public.data AS d TABLESAMPLE BERNOULLI(50 + $1) REPEATABLE( 42.5 )';
31        $samples = (new PgSqlTableSampleParser())->parse($sql);
32
33        self::assertCount(1, $samples);
34        self::assertSame('data', $samples[0]->tableName);
35        self::assertSame('public.data', $samples[0]->sourceSql);
36        self::assertSame('AS d', $samples[0]->aliasSql);
37        self::assertSame(PgSqlTableSampleMethod::Bernoulli, $samples[0]->method);
38        self::assertSame('50 + $1', $samples[0]->percentageSql);
39        self::assertSame('42.5', $samples[0]->seedSql);
40        self::assertSame('public.data AS d TABLESAMPLE BERNOULLI(50 + $1) REPEATABLE( 42.5 )', substr(
41            $sql,
42            $samples[0]->startOffset,
43            $samples[0]->endOffset - $samples[0]->startOffset,
44        ));
45    }
46
47    public function testParsesMultipleSamplesAndNestedSelects(): void
48    {
49        $samples = (new PgSqlTableSampleParser())->parse(
50            'SELECT * FROM data TABLESAMPLE SYSTEM (100) '
51            . 'JOIN (SELECT * FROM logs TABLESAMPLE BERNOULLI (25)) sampled_logs ON TRUE',
52        );
53
54        self::assertCount(2, $samples);
55        self::assertSame(['data', 'logs'], array_column($samples, 'tableName'));
56        self::assertSame(PgSqlTableSampleMethod::System, $samples[0]->method);
57        self::assertSame(PgSqlTableSampleMethod::Bernoulli, $samples[1]->method);
58    }
59
60    public function testFindsSampleAfterUnsampledJoinSource(): void
61    {
62        $samples = (new PgSqlTableSampleParser())->parse(
63            'SELECT * FROM data JOIN logs TABLESAMPLE bernoulli ( 25 ) ON TRUE',
64        );
65
66        self::assertCount(1, $samples);
67        self::assertSame('logs', $samples[0]->tableName);
68        self::assertSame(PgSqlTableSampleMethod::Bernoulli, $samples[0]->method);
69        self::assertSame('25', $samples[0]->percentageSql);
70    }
71
72    public function testRemovesInheritanceMarkerFromAliasText(): void
73    {
74        $samples = (new PgSqlTableSampleParser())->parse(
75            'SELECT * FROM data * sampled TABLESAMPLE SYSTEM (10)',
76        );
77
78        self::assertCount(1, $samples);
79        self::assertSame('sampled', $samples[0]->aliasSql);
80    }
81
82    public function testReturnsNoSamplesForOrdinaryRelations(): void
83    {
84        self::assertSame([], (new PgSqlTableSampleParser())->parse('SELECT * FROM data JOIN logs ON TRUE'));
85        self::assertSame(
86            [],
87            (new PgSqlTableSampleParser())->parse(
88                'SELECT * FROM (SELECT * FROM data) sampled TABLESAMPLE SYSTEM (10)',
89            ),
90        );
91    }
92
93    public function testRejectsCustomSamplingMethod(): void
94    {
95        $this->expectException(UnsupportedSqlException::class);
96        $this->expectExceptionMessage('TABLESAMPLE method not supported');
97
98        (new PgSqlTableSampleParser())->parse('SELECT * FROM data TABLESAMPLE SYSTEM_ROWS (10)');
99    }
100
101    #[TestWith(['SELECT * FROM data TABLESAMPLE BERNOULLI'])]
102    #[TestWith(['SELECT * FROM data TABLESAMPLE BERNOULLI 10'])]
103    #[TestWith(['SELECT * FROM data TABLESAMPLE BERNOULLI ()'])]
104    #[TestWith(['SELECT * FROM data TABLESAMPLE BERNOULLI (10, 20)'])]
105    #[TestWith(['SELECT * FROM data TABLESAMPLE BERNOULLI (10) REPEATABLE'])]
106    #[TestWith(['SELECT * FROM data TABLESAMPLE BERNOULLI (10) REPEATABLE 42'])]
107    #[TestWith(['SELECT * FROM data TABLESAMPLE BERNOULLI (10) REPEATABLE ()'])]
108    #[TestWith(['SELECT * FROM data TABLESAMPLE BERNOULLI (10) REPEATABLE (1, 2)'])]
109    public function testRejectsMalformedPercentageAndRepeatableExpressions(string $sql): void
110    {
111        $this->expectException(UnsupportedSqlException::class);
112
113        (new PgSqlTableSampleParser())->parse($sql);
114    }
115
116    #[TestWith(['SELECT * FROM data TABLESAMPLE BERNOULLI 10', 'TABLESAMPLE opening parenthesis'])]
117    #[TestWith(['SELECT * FROM data TABLESAMPLE BERNOULLI (10', 'TABLESAMPLE closing parenthesis'])]
118    #[TestWith([
119        'SELECT * FROM data TABLESAMPLE BERNOULLI (10) REPEATABLE 42',
120        'REPEATABLE opening parenthesis',
121    ])]
122    #[TestWith([
123        'SELECT * FROM data TABLESAMPLE BERNOULLI (10) REPEATABLE (42',
124        'REPEATABLE closing parenthesis',
125    ])]
126    public function testDistinguishesMalformedOpeningAndClosingParentheses(string $sql, string $message): void
127    {
128        $this->expectException(UnsupportedSqlException::class);
129        $this->expectExceptionMessage($message);
130
131        (new PgSqlTableSampleParser())->parse($sql);
132    }
133}
134