Issues (41)

code/PostgreSQLQueryBuilder.php (1 issue)

Severity
1
<?php
2
3
namespace SilverStripe\PostgreSQL;
4
5
use SilverStripe\ORM\Queries\SQLConditionalExpression;
6
use SilverStripe\ORM\Queries\SQLExpression;
7
use SilverStripe\ORM\Queries\SQLSelect;
8
use SilverStripe\ORM\Connect\DBQueryBuilder;
9
use InvalidArgumentException;
10
11
class PostgreSQLQueryBuilder extends DBQueryBuilder
12
{
13
    /**
14
     * Max table length.
15
     * Aliases longer than this will be re-written
16
     */
17
    const MAX_TABLE = 63;
18
19
    /**
20
     * Return the LIMIT clause ready for inserting into a query.
21
     *
22
     * @param SQLSelect $query The expression object to build from
23
     * @param array $parameters Out parameter for the resulting query parameters
24
     * @return string The finalised limit SQL fragment
25
     */
26
    public function buildLimitFragment(SQLSelect $query, array &$parameters)
27
    {
28
        $nl = $this->getSeparator();
29
30
        // Ensure limit is given
31
        $limit = $query->getLimit();
32
        if (empty($limit)) {
33
            return '';
34
        }
35
36
        // For literal values return this as the limit SQL
37
        if (! is_array($limit)) {
0 ignored issues
show
The condition is_array($limit) is always true.
Loading history...
38
            return "{$nl}LIMIT $limit";
39
        }
40
41
        // Assert that the array version provides the 'limit' key
42
        if (! array_key_exists('limit', $limit) || ($limit['limit'] !== null && ! is_numeric($limit['limit']))) {
43
            throw new InvalidArgumentException(
44
                'DBQueryBuilder::buildLimitSQL(): Wrong format for $limit: '. var_export($limit, true)
45
            );
46
        }
47
48
        if ($limit['limit'] === null) {
49
            $limit['limit'] = 'ALL';
50
        }
51
52
        $clause = "{$nl}LIMIT {$limit['limit']}";
53
        if (isset($limit['start']) && is_numeric($limit['start']) && $limit['start'] !== 0) {
54
            $clause .= " OFFSET {$limit['start']}";
55
        }
56
        return $clause;
57
    }
58
59
    public function buildSQL(SQLExpression $query, &$parameters)
60
    {
61
        $sql = parent::buildSQL($query, $parameters);
62
        return $this->rewriteLongIdentifiers($query, $sql);
63
    }
64
65
    /**
66
     * Find and generate table aliases necessary in the given query
67
     *
68
     * @param SQLConditionalExpression $query
69
     * @return array List of replacements
70
     */
71
    protected function findRewrites(SQLConditionalExpression $query)
72
    {
73
        $rewrites = [];
74
        foreach ($query->getFrom() as $alias => $from) {
75
            $table = is_array($from) ? $from['table'] : $from;
76
            if ($alias === $table || "\"{$alias}\"" === $table) {
77
                continue;
78
            }
79
            // Don't complain about aliases shorter than max length
80
            if (strlen($alias) <= self::MAX_TABLE) {
81
                continue;
82
            }
83
            $replacement = substr(sha1($alias), 0, 7) . '_' . substr($alias, 8 - self::MAX_TABLE);
84
            $rewrites["\"{$alias}\""] = "\"{$replacement}\"";
85
        }
86
        return $rewrites;
87
    }
88
89
    /**
90
     * Rewrite all ` AS "Identifier"` with strlen(Identifier) > 63
91
     *
92
     * @param SQLExpression $query
93
     * @param string $sql
94
     * @return string
95
     */
96
    protected function rewriteLongIdentifiers(SQLExpression $query, $sql)
97
    {
98
        // Check if this query has aliases
99
        if ($query instanceof SQLConditionalExpression) {
100
            $rewrites = $this->findRewrites($query);
101
            if ($rewrites) {
102
                return str_replace(array_keys($rewrites), array_values($rewrites), $sql);
103
            }
104
        }
105
        return $sql;
106
    }
107
}
108