PostgreSQL enhancements for Doctrine

Hello Martin,

I use your package in my project, and I want to say BIG thanks for your work. Your package is really helpful.

And I want to suggest to you to add ILIKE function support. I can do it by myself, but it would be great if you will help me with understanding what is inside your package.

$ composer require martin-georgiev/postgresql-for-doctrine

Using version ^1.5 for martin-georgiev/postgresql-for-doctrine
...

Your requirements could not be resolved to an installable set of packages.

  Problem 1
    - Root composer.json requires php ^8.0 but your php version (7.4.12) does not satisfy that requirement.

I see that in your composer.json is specified:

"php": "^7.2|^8.0",

I think there should be a double pipe. Proof. And the same on stackoverflow, see the comment there.

this line would be a problem with array entries that are null, which is valid for the postgres arrays

https://github.com/martin-georgiev/postgresql-for-doctrine/blob/6267c5693218e3ac1d0cecb6ff65ecb6e279ea78/src/MartinGeorgiev/Doctrine/DBAL/Types/SmallIntArray.php#L32

Hello!

First off I want to say this package looks really awesome. I am trying out postgres for the first time because I want to be able to store and query json for a particular use case I have... However I'm having some issues figuring out how to actually use the tools provided in this package. A few examples would be really great, though I am grateful for your time putting the package together either way.

Thanks again!

Hi,

Could you please give me an example in Doctrine for this SQL query :

SELECT id, title
FROM my_table
where title->>'fr' like '%AAA%';

I've tried some syntaxe in DQL, but i don't find a way to use it.

Add possibility to cast any column to json (or jsonb) and get field as text

Also I've fixed table intends Closes https://github.com/martin-georgiev/postgresql-for-doctrine/issues/113

We are storing variable type of data in JSONB field. It can be object, integer, float or string. So we have an issue with Jsonb and primitive types. I think array type must be removed from these methods.

https://github.com/martin-georgiev/postgresql-for-doctrine/blob/master/src/MartinGeorgiev/Doctrine/DBAL/Types/Jsonb.php#L45 https://github.com/martin-georgiev/postgresql-for-doctrine/blob/master/src/MartinGeorgiev/Doctrine/DBAL/Types/JsonTransformer.php#L33

We want to be able to pass any kind of StringPrimary into the TO_JSONB function, however, I think it makes sense to be able to pass e.g. a SimpleArithmeticExpression as well, and it seems NewValue is the best corresponding type for that.

Also, even though we're only using it in TO_JSONB, obviously this makes sense for the other TO_* functions too.

Hi. I'm using your good library, but notice lack of date functions. So I added implementation for one of the most useful functions "OVERLAPS".

https://www.postgresql.org/docs/9.6/functions-datetime.html

I wanted to use your package in a project using PHP version 8.0.12.

Your package needs at least PHP 7.2 so I thought it would work, but composer stops the process and give me this :

Problem 1 - martin-georgiev/postgresql-for-doctrine v1.5.0 requires php ^7.2 -> your php version (8.0.12) does not satisfy that requirement. - martin-georgiev/postgresql-for-doctrine[v1.5.1, ..., v1.5.2] require doctrine/dbal ~2.5 -> found doctrine/dbal[v2.5.0, ..., 2.13.4] but the package is fixed to 3.1.3 (lock file version) by a partial update and that version does not match. Make sure you list it as an argument for the update command. - Root composer.json requires martin-georgiev/postgresql-for-doctrine ^1.5 -> satisfiable by martin-georgiev/postgresql-for-doctrine[v1.5.0, v1.5.1, v1.5.2].

Do you know why does the PHP requirements are not me ?

Thanks for letting me know, and fix bug if it is one

• Add requiresSQLCommentHint() to BaseType

See doctrine/DoctrineBundle#947 Fixes #50

• Drop null parameter on call to TestCase->setMethods()

The method is type hinted to array so null is throwing a type error

Not really an issue, but a suggestion, I've implemented FloatArray locally for a project:

class FloatArray extends BaseArray
{
    protected const TYPE_NAME = 'float[]';

    /**
     * @param mixed $item
     */
    public function isValidArrayItemForDatabase($item): bool
    {
        return (\is_float($item) || \is_int($item) || \is_string($item))
            && preg_match('/^-?[0-9]+(?:\.[0-9]+)?$/', (string) $item);
    }

    /**
     * @param float|int|string|null $item Whole number
     */
    public function transformArrayItemForPHP($item): ?float
    {
        if (null === $item) {
            return null;
        }

        $isInvalidPHPInt = false === preg_match('/^-?[0-9]+(?:\.[0-9]+)?$/', (string) $item);
        if ($isInvalidPHPInt) {
            throw new ConversionException(sprintf('Given value of %s content cannot be transformed to valid PHP float.', var_export($item, true)));
        }

        return (float) $item;
    }
}

In that case, I do not verify the number of digits (https://www.postgresql.org/docs/current/datatype-numeric.html) depending on real (float4) or double precision (float8). Doctrine natively not handle float4 (real) but only floa8(double precision), PHP have a precision of 14 digits. That's why I do not do the check (but in external library like here, seem important to do, if the user change the precision to 16 for exemple).

Name | Storage Size | Description | Range -- | -- | -- | -- real | 4 bytes | variable-precision, inexact | 6 decimal digits precision double precision | 8 bytes | variable-precision, inexact | 15 decimal digits precision

It seems there also have a min/max value like done with Integer array:

On most platforms, the real type has a range of at least 1E-37 to 1E+37 with a precision of at least 6 decimal digits. The double precision type typically has a range of around 1E-307 to 1E+308 with a precision of at least 15 digits. Values that are too large or too small will cause an error. Rounding might take place if the precision of an input number is too high. Numbers too close to zero that are not representable as distinct from zero will cause an underflow error.

1. Check the min/max is not really difficult I think, do the same like done with IntegerArray
2. Check the number of digits seen a more little difficult: convert to string, count number chars after the ".", and if scientific notation use the "E" ?

When I use the function like this array_agg(d.startDate ORDER BY d.startDate DESC), I get en error "[Syntax Error] line 0, col 64: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got 'ORDER'" Please fix it.

In reviewing the documentation for Postgres 14, I saw this:

PostgreSQL has supported manipulating JSON data since the release of PostgreSQL 9.2, though retrieval of values used a unique syntax. PostgreSQL 14 now lets you access JSON data using subscripts, e.g. a query like SELECT ('{ "postgres": { "release": 14 }}'::jsonb)['postgres']['release']; now works. This aligns PostgreSQL with syntax that is commonly recognized for retrieving information from JSON data. The subscripting framework added to PostgreSQL 14 can be generally extended to other nested data structures, and is also applied to the hstore data type in this release.

Range types, also first released in PostgreSQL 9.2, now have support for noncontiguous ranges through the introduction of the "multirange" data type. A multirange is an ordered list of ranges that are nonoverlapping, which lets developers write simpler queries for dealing with complex sequences of ranges. The range types native to PostgreSQL (dates, times, numbers) support multiranges, and other data types can be extended to use multirange support.

Are there parts of this bundle that alter the query syntax based on version? This is probably a minor enhancement that would only be relevant for debugging, but I'm curious if there's any benefit, beyond the syntax change, to leveraging this new feature from postgres 14.

I want to get sql like select (np.params::json)->> 'slug', np.params is a text field

DDL:

JSON_GET_FIELD_AS_TEXT(TO_JSON(np.params), 'slug')

Finally got an error: Doctrine\ORM\Query\QueryException: [Syntax Error] line 0, col 110: Error: Expected Doctrine\ORM\Query\Lexer::T_INPUT_PARAMETER, got 'np' What I do wrong?

I'd like to use this kind of postgres code Array_to_string(ARRAY['a', 'b', 'c'], ';') which gives a;b;c

but this does not parse in dql :

ARRAY_TO_STRING( ARRAY( 'a', 'b', 'c' ), ';')
-> [Syntax Error] line 0, col 70: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got ','