mindplay-dk / sql

Database framework and query builder
Other
17 stars 6 forks source link

Bugs: refactor query-builders - remove bad portions of APIs #45

Closed mindplay-dk closed 5 years ago

mindplay-dk commented 5 years ago

The documentation on the ProjectQuery class is very wrong.

The idea that SELECT, UPDATE and DELETE queries are all "projection queries" is wrong.

The only thing these query-types have in common, is the WHERE clause.

Only SELECT queries actually support projections, which doesn't mean much more than simply selecting which columns to include in the resulting projection.

The ORDER, JOIN and LIMIT clauses are all specific to the SELECT query-builders.

Removing these features from the query-builders is technically a breaking change - however, we can safely version these changes as a bugfix, since these feature wouldn't have produced any working code in the first place.

Here's an overview of the clauses supported by all 4 query-types for PostgreSQL and MySQL:

SELECT {projection}
  (DISTINCT)
  FROM
  WHERE
  GROUP BY
  HAVING
  ORDER BY
  LIMIT
  OFFSET
  mysql: [ FOR { UPDATE | SHARE } [OF tbl_name [, tbl_name] ...] [NOWAIT | SKIP LOCKED] | LOCK IN SHARE MODE ]
  pgsql: [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]

INSERT
  INTO {table}
  VALUES ({row,...})
  mysql: ON DUPLICATE KEY UPDATE
  pgsql: ON CONFLICT
  pgsql: RETURNING

UPDATE
  (mysql: IGNORE)
  SET
  (pgsql: FROM)
  WHERE
  pgsql: RETURNING
  (mysql: ORDER BY)
  (mysql: LIMIT)

DELETE
  (mysql: IGNORE)
  FROM
  (pgsql: USING)
  WHERE
  (mysql: ORDER BY)
  (mysql: LIMIT)
  pgsql: RETURNING

Clauses designated pgsql or mysql are specific to those query-builders.

The clauses in () parens aren't currently supported by the query-builders.

Remove the ProjectionQuery class, and move the WHERE clause builder to a trait.

mindplay-dk commented 5 years ago

Minor correct to the above: MySQL does support non-standard LIMIT and ORDER BY clauses for UPDATE and DELETE statements - removing these features would be a breaking change, so we should probably introduce MySQLUpdateQuery and MySQLDeleteQuery classes to preserve these features.

mindplay-dk commented 5 years ago

In master now.