> On Jun 22, 2021, at 6:45 AM, Pierre <pierre-...@processus.org> wrote:
>
> Le 22/06/2021 à 11:28, Dan Ackroyd a écrit :
>> On Tue, 22 Jun 2021 at 10:25, Mike Schinkel <m...@newclarity.net> wrote:
>>> Should(n't?) PHP add a basic SQL builder class that can be extended for
>>> special cases, e.g. different flavors of SQL?
>>>
>> No. Or at least not yet.
>>
>> This type of thing is much better done in userland, where the api can
>> evolve at a fast rate, rather than being limited by the fixed release
>> schedule of PHP.
>
> Agreed, PHP is probably not the right place for an SQL builder, there's too
> many dialects, too many standard or non-standard features, in the end SQL
> builders almost always end-up being opinionated by its designer's original
> need, philosophy, or SQL usage habits, and tailored for users which use
> certain paradigms.
>
> An SQL query builder is already business domain tied, in some way. Of course
> most are very generic, but often don't handle properly SQL advanced or modern
> features, whereas some other where built for ORMs and such and may a reduced
> advanced SQL features that fits the original need.
>
> I don't wish to see an SQL query builder in PHP core, instead of favoring
> usage of modern SQL, it'll in my opinion (of course, that's subjective) lower
> the expectation of people and probably normalize SQL-92 being the only SQL
> spoken by people writing PHP (just exaggerating a bit).
I think we are talking two different things here. And I will admit I am
probably the one that is not using exactly the right term. I had envisioned
something, and then Dan said "SQL Builder" I used that term.
Let me clarify by giving what I am referring to a different name: A SQL Object
Model with parser and sanitizer functionality. One with a simple interface and
one that would need not be tied to or limited by any specific dialect of SQL.
To illustrate what I mean I created the following straw man example. It
assumes a hypothetical SqlObjectModel classes that needs to be instantiated
with an objects that implement hypothetical `SqlDialectInterface` and
`SqlSchemaInterface` instances passed to its constructor. The former defines
the rules for the MySql dialect, and the latter accesses the schema to validate
data and data types.
Some "dialects" and "validators" for very well-known and widely used database
servers such as MySQL could be implemented in PHP core, while others could be
implemented in userland (the database vendors would be good candidate to
implement them, actually.) I don't yet know what those interfaces should look
like but I am pretty sure that could be figured out.
So here is the example (I omitted error handling logic for brevity and clarity):
$sql = <<<SQL
SELECT
c.id AS company_id,
c.company_name,
jo.job_title,
COUNT(*) AS job_openings
FROM companies c
JOIN jobs_openings jo ON c.id=jo.company_id
WHERE 1=1
AND city_name = 'Nantes'
AND country_name='France'
AND job_openings > {openings}
GROUP BY
c.company_name
LIMIT
{limit}
SQL;
$conn = mysqli_connect(...);
$som = new SqlObjectModel(new MySqlDialect(), new MySqlSchema($conn));
$som->parse($sql);
$som->set( "limit", $_GET['limit'] );
$som->set( "openings", $_GET['openings'] );
$result = mysqli_query( $conn, $som->sql() );
The SQLObjectModel *could* potentially have additional features like the
ability to get and set the table name, list of fields, joins, where clauses,
etc. but the core value-add is not in building SQL but rather parsing and
sanitizing with properly escaped and quoted arguments so that known-safe SQL
could be generated.
Existing or new SQL builders could use this to take SqlObjectModel() and pass
its generated SQL through to ensure their SQL they will output is safe, which
by the way the string could now be tagged as "trusted" before the builder
returns the SQL to its developer, assuming an is_trusted() RFC were to pass.
Similarly, any ORM could ensure its SQL is fully sanitized and then pass on to
whatever functions are needed to execute the SQL.
So, absolutely nothing here would keep people from using "modern SQL," and/or
cause people to lower the expectation to normalize SQL-92. This could be a
generic query parser and sanitizer that could be configured by the "dialect"
and "schema" objects passed to it and its scope it small, simple and
straightforward.
I hope that clarifies what I was thinking?
-Mike
