I was just thinking that instead of just the normal query/insert functions, that each one of the normal types of queries should have their own function. Reason being is that then it makes it much easier to use a master/slave setup.
So it would add select(), delete(), update() to Database. Then a plugin can handle the query/server selection. A raw query() would go to the master because you don't know what it is going to do. Everything except for a select() would go to the master because they change rows. This is, of course, very easy to do with one of the many tools out there, but I also think that this would make plugins easier for certain types of queries.
https://gist.github.com/joshuaadickerson/9336643
Honestly db-related I'm not sure what's the "state of the art", but considering this level of methods splitting (that would be quite interesting I think), it would probably be more consistent and flexible to follow the schema provided by the current db::insert(), so instead of passing the "raw" query, pass arrays of fields so that the db-layer could reassemble them the way it likes.
For example update could be:
public function update($query_id = '', $table, $columns, $data, $conditions = '', $disable_trans = false, $connection = null)
{
global $db_prefix;
$connection = $this->getConnection($connection);
// With nothing to insert, simply return.
if (empty($data))
return;
// Replace the prefix holder with the actual prefix.
$table = str_replace('{db_prefix}', $db_prefix, $table);
$updateData = '';
foreach ($columns as $columnName => $type)
{
if (isset($data[$columnName]))
{
$updateData .= '
' . $columnName . ' = ';
// Are we restricting the length?
if (strpos($type, 'string-') !== false)
$updateData .= sprintf('SUBSTRING({string:%1$s}, 1, ' . substr($type, 7) . '), ', $columnName);
else
$updateData .= sprintf('{%1$s:%2$s}, ', $type, $columnName);
}
}
$updateData = substr($updateData, 0, -2);
if ($conditions !== '')
$conditions = 'WHERE ' . $conditions;
// Create an array consisting of only the columns.
$indexed_columns = array_keys($columns);
// Here's where the variables are injected to the query.
$updateRows = array();
foreach ($data as $dataRow)
$insertRows[] = $this->quote($updateData, array_combine($indexed_columns, $dataRow), $connection);
$data['db_error_skip'] = $table === $db_prefix . 'log_errors';
// Do the insert.
$this->query($query_id, '
UPDATE ' . $table . '
SET' . $updateData . '
' $conditions,
$data,
$connection
);
}
I'm more of the way of using something more similar to prepared statements (like the way I wrote it) because you are essentially future proofing it and allowing for more advanced queries.
How would the current query's change to support this, or do they stay the same?
You'd remove the SELECT, UPDATE, DELETE. Everything else is the same. You could even add a check to see if that were in there until you wanted to change all of the queries. Though, if you're changing the $db->query() to $db->select() it doesn't make much sense to not remove SELECT from the query itself.