We already have the Condition classes tracked in their own issue, because they have some useful functionality when combining different logical conditions, but this issue covers all the other parts of a query that might be output in a different way between different database drivers.

For example:

• like comparison uses slightly different syntax in MySQL to SQLite and Postgres (field like 'gr?g' in MySQL vs. field like 'gr_g' in others)

// TODO: More examples to flesh out the syntactical differences between the major vendors.

Extends Insert as it works exactly the same, apart from updating clashing primary keys.

https://dev.mysql.com/doc/refman/8.0/en/replace.html

For future use of SqlBuilder classes, various Conditional classes need creating:

• Equals (with null support), GreaterThan, LessThan
• Between (with date support)
• MultipleCondition that takes MatchAny and MatchAll

We've got the Query classes working nicely, but now's the time to move out the functionality to something that can be built automatically by the Database Repository.

Builder classes:

• SelectBuilder
• UpdateBuilder
• DeleteBuilder
• InsertBuilder

On the back of this, we need to have the Conditional functionality added to.

Rough notes:

class SelectBuilder extends SqlBuilder {
	const DEFAULT_LIMIT = 100;

	private ?string $prefix;
	/** @var string[] */
	private array $columnArray;
	private string $from;

	private Condition $where;
	/** @var string[] */
	private array $groupBy;
	private Condition $having;

	private int $limit;
	private int $offset;

	public function __construct(string $prefix = null) {
		$this->prefix = $prefix;
		$this->limit = self::DEFAULT_LIMIT;
		$this->offset = 0;
	}

	public function __toString():string {
		$sql = "";
		if(isset($this->prefix)) {
			$sql .= $this->prefix . "\n";
		}
		$sql .= "select";
		foreach($this->columnArray as $i => $col) {
			if($i > 0) {
				$sql .= ",";
			}
			$sql .= "\n\t$col";
		}
		$sql .= "\n\nfrom";
		$sql .= "\n\t" . $this->from;

		if(isset($this->where)) {
			$sql .= "\n\nwhere";
			foreach($this->where as $condition) {
				$sql .= "\n\t$condition";
			}
		}

		return $sql;
	}

	public function select(string...$columns):self {
		$this->columnArray = $columns;
		return $this;
	}

	public function from(string $tableName):self {
		$this->from = $tableName;
		return $this;
	}

	public function where(Condition $condition):self {
		$this->where = $condition;
		return $this;
	}

	public function groupBy(string...$groupBy):self {
		$this->groupBy = $groupBy;
		return $this;
	}

	public function having(Condition $condition):self {
		$this->having = $condition;
		return $this;
	}
}

abstract class Condition {}

abstract class MatchMultipleCondition extends Condition {
	/** @var array<Condition> */
	private array $conditionArray;

	public function __construct(Condition...$conditions) {
		$this->conditionArray = $conditions;
	}
}

class MatchAny extends MatchMultipleCondition {}

class MatchAll extends MatchMultipleCondition {}

abstract class SingleCondition extends Condition {
	public function __construct(
		public readonly string $propertyName,
		public readonly mixed $value,
	) {}
}

class Equals extends SingleCondition {}

class GreaterThan extends SingleCondition {}

class LessThan extends SingleCondition {}

Rather than returning an array for everything, return an object of the appropriate type.

For example:

UpdateQuery::set():AssignmentList

class UpdateStudentName extends UpdateQuery {
  public function set():AssignmentList {
    return new AssignmentList(
      "name",
      new Assignment("updatedAt", new NowDate()),
    );
  }
}

Required for full ORM usage.

In theory this should not be a problem, as a Query object can be represented as a string, but it may be necessary to wrap brackets, etc.

In the builder classes, it should be possible to get a reference to the object that represents the query.

SelectBuilder::getQuery(); should return the class the extends the SqlQuery, which is currently only managed within the __toString() function.

Don't return an array from "set" functions, like InsertQuery::set, instead return an implementation of abstract class ParameterCollection.

An array can also be returned, which will require a doc block until PHP 8 is supported with union type array|ParameterCollection.

For example, delete from tablename - a delete query can't not have a from table.

Missing functionality - no create table. This is required for full ORM usage.

• On duplicate key update.
• Partitions.
• Insert columns.
• Insert values.

https://dev.mysql.com/doc/refman/8.0/en/insert.html

Inner, left, right, full outer ... They should all be handled.

This is tracked in its own issue due to table operations coming with their own set of driver-specific complexities.

• Create
• Alter
• Drop

Dependabot needs to be tamed, as per PhpGt/WebEngine#568

Required for full ORM usage.

The main example I want to take a look at is limit(). It can only ever be a single integer, so should it really always return an array?

While we're looking at return types, make sure that string[] is always the case, and clearly indicate where a subquery can be present within a returned array.

• Low priority
• Ignore errors
• Order by
• Limit
• Multiple tables
• Set assignments

To save on repetitive queries:

class getCustomerList extends SqlQuery {

	public function select () {
		return [
    			'id',
			'name'
			'status'
		];
	}

	public function from {
		return 'customer';
		// or another SqlQuery class:
		// return new getNestedQuery();
	}

	public function order {
		'created desc';
	}

}

class getCustomerListUnprocessed extends getCustomerList {

	public function where () {
		return array_merge(parent::where(), [
			'processed is null'
		]);
	}

}

https://dev.mysql.com/doc/refman/8.0/en/insert-select.html

• Low priority, quick, ignore errors
• Partitions
• Where
• Order by
• Limit

https://dev.mysql.com/doc/refman/8.0/en/delete.html

Idea:

For indexed parameters (with the ? character):

return [
    "?uuid",
    "?name",
    "createdAt" => "now()",
]

For named parameters (with :-prefixed named):

return [
    ":uuid",
    ":name",
    "createdAt" => "now()",
]