Le 22/06/2021 à 14:45, Mike Schinkel a écrit :
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 <http://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 <http://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
Actually, it makes more sense.
It's even funnier if you consider it, it's more or less what I'm doing
in my own SQL builder for find and replace placeholders, after the query
builder actually built the query. I'm not replacing placeholders with
values, but I'm replacing my own API arbitrary placeholders with those
expected by the underlaying dialect or low-level API (I'm not naive
enough to concatenate user strings in SQL, I'm leaving the hard escaping
work to the server or lower-level db connector API using prepared
queries when the low level driver doesn't handle it properly).
But that said, what you describe already intersects with most SQL
builders own features, and since they all have very different
architecture, I'm not sure this kind of helper would be that much
beneficial. But that something that could make sense to be discussed.
In all cases, thanks for answering.
Regards,
--
Pierre
