> On Jun 22, 2021, at 6:45 AM, Pierre <pierre-php@processus.org> wrote:
>
> Le 22/06/2021 à 11:28, Dan Ackroyd a écrit :
>> On Tue, 22 Jun 2021 at 10:25, Mike Schinkel <mike@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