Support for insert/delete/update wrappers

[hemberger]

As originally requested in #472 (comment), I was wondering if it would be possible to support Doctrine-style wrappers for write operations.

For example, an insert method is converted into a query as follows:

$conn->insert('user', ['username' => 'jwage']);
// INSERT INTO user (username) VALUES (?) (jwage)

This conversion is described explicitly in the help text for the following methods:

Doctrine\DBAL\Connection::delete
Doctrine\DBAL\Connection::insert
Doctrine\DBAL\Connection::update

If supported, my expectation would be that PHPStan could identify if any of the table or column names did not exist.

In addition to the Doctrine methods, I also have custom wrappers that use a similar interface (i.e. table name as first argument, column name-value array for any additional arguments), but perform a query that Doctrine doesn't have a convenience wrapper for, e.g.

$myconn->replace('user', ['username' => 'jwage']);
// REPLACE INTO user (username) VALUES (?) (jwage)

As such, I would find it very useful to have some generic way to support interfaces like these. I have no idea what would be easiest in terms of implementation, but I was imagining the following possibilities:

1. A SyntaxError class that could be specified in the PHPStan config, e.g.

   class: staabm\PHPStanDba\Rules\SyntaxErrorInQueryWrapperRule # hopefully w/ a better name!
   tags: [phpstan.rules.rule]
   arguments:
       classMethods:
           - 'My\Connection::replace'

2. Parameter types similar to class-string that could identify table/column names, e.g.

   /**
    * @param phpstandba-table-string $table
    * @param array<phpstandba-column-string, mixed> $params
    */
   public function replace(string $table, array $params)

I'm happy to help in whatever way I can, though I should note that I'm a complete newbie when it comes to extending PHPStan, so would benefit greatly from some pointers if you want me to submit a PR. :)

Thank you for your time, and for all the work you've done on this amazing project. I'm super excited to finally have some confidence in my SQL queries!

[staabm]

Thanks for your kind words. Maybe you could consider sponsoring my open source work :-).

Atm the phpstan-dba api is based on sql queries.

We could add a new reflection api for the database schema so we are able to built new rules in which database table and column names are used in such a plain way.

[hemberger]

Unfortunately, my PHP projects are open source volunteer work that generate negative income, so I cannot commit to being a sponsor at this time. That said, I'll gladly buy you a "coffee" from time to time!

I'd be willing to try to contribute to this effort. Is there any infrastructure already in place for such a feature? Do you have any pointers for what I could look at to get started? Thanks again!

[staabm]

That said, I'll gladly buy you a "coffee" from time to time!

thank you for that morning coffee today :-).

I'd be willing to try to contribute to this effort. Is there any infrastructure already in place for such a feature? Do you have any pointers for what I could look at to get started?

Atm we have QueryReflection and several AdapterReflection (like DibiReflection), but no generic SchemaReflection yet.
I think a good start could be creating a new src/SchemaReflection folder and add simple classes which can represent tables and their columns (value objects) and a new SchemaReflection class which can be used to lookup the schema and can return said value objects.

please keep it simple. having just the table and column names for now, seems like a good first start.
we can things like native column types etc. later.

[staabm]

fyi, I am in the process of building the SchemaReflection part in #505

[staabm]

Schema reflection landed on the main branch. You can use it to lookup table/column names and report errors.

[hemberger]

Great, thank you! I am working on a PR for this now.