Skip to main content ↑ ↓
Topic: Database functions (Read 2427 times) previous topic - next topic
0 Members and 1 Guest are viewing this topic.

Database functions

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.

Re: Database functions

Reply #2 –

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:
Code: [Select]
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
	);
}
Bugs creator.
Features destroyer.
Template killer.

Re: Database functions

Reply #3 –

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.

Re: Database functions

Reply #4 –

How would the current query's change to support this, or do they stay the same?

Re: Database functions

Reply #5 –

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.