Alright, this has been discussed in private a few months ago, but back then it was less relevant due to the absence of a proper add-on manager. But since Arantor has made a brand new and nothing short of awesome Add-on manager coupled with Nao's enhanced theme skeleton system, this topic is now more relevant than ever.
One of the few remaining major flaw with the current hook system is that queries and data loading from MySQL cannot be hooked in any form unless the result itself is passed as a hook, but that requires creating innumerable amount of hooks. Even then it wouldn't solve the modifying query problem. One easy solution is just to add an ID to every query and pass the string to hooks, so that they can be modified via string manipulation, but that is a very bad idea because it can easily mess up query bad.
Another idea I had is to objectify the query, such that every SQL statement is an individual variable, this way add-ons can just add to those arrays in order to add there own statements.
Update : Upon further discussion I have a more concrete implementation made, here's an update summarizing the current progress. Please read the whole update before providing your opinions, any feedback is appreciated.
The aim of the current solution is to vaguely objectify the queries, by "vaguely" I mean that only the major portion of the query, i.e. "SELCT, WHERE etc" statement's SQL lines are passed. The SQL passed for those statement itself is not objectified. Although possible, doing so would severely limit down the syntax and offer hardly any more advantages.
Basic query functions
wesqlQuery::__construct(string $name, array $sql_params, array $params)
This is the beginning point of any query, an unique name must be passed. "sql_params" is an array containing various SQL structs and their corresponding sql statements. Every individual SQL statement of a SQL struct must be passed as an individual array value. "params" is your traditional query parameter, the parameters are defined in same way as the correct. Example :
This will produce the fol. SQL query :
Code: [Select] As you can see, 2 fields are passed as individual array members of the select array value passed to SQL params, same way, multiple where statements can also be done same way
Will produce this SQL query :
Code: [Select]
Passing extra options to SQL structs
Some special options such as "SELECT DISTINCT" or "UNION ALL" can also be used, detailed documentation for every struct and their extra options can be found later. In order to pass extra options to structs, the struct array should contain 2 child arrays, first one should contain the SQL statements, second one should contain the parameters passed.
Example(Only shows the select part, rest is assumed) :
wesqlQuery::bindSQLParam(string $name, array $value, array $extraOptions)
Instead of passing all the SQL params through __construct, one can bind individual SQL structs using this method. Please remember that values are not replaced by this method but are appended to the existing methods.
Example :
extraOptions behave similarly to above
wesqlQuery::bindParam(string $name, mixed $value)
Like above, query parameters can also be added through this method.
wesqlQuery::execute()
Executes a query, throws an error if any.
wesqlQuery::fetch(bool $run_callbacks = true, array $hook_parameters)
Fetches the next row, if run_callbacks is set to true it'll also run the row through all the callbacks and then return the resulting row. More on callbacks and hooks later.
Hooking and modifying queries
The major point of objectifying queries is to be able to modify them through hooks without running string functions and/or conflicting with others. For every query made, a new hook called "query_<name>" is called as soon as a query is executed. Only one parameter is passed and that is the instance of the query itself, from there you cannot execute the query again as that will throw an error. You have access to all the bind functions as well as other helper functions, but fetch or other functions which rely on result would not be available.
Here's an example of a query, to which an add-on adds a new SELECT field as well as a WHERE clause
What about results?
Modifying query is useless until it can actually be accessed by the template via the result that is stored. Worry not, we have result callbacks. Each result callback is passed every row fetched individually, and it's supposed to modify the row in a nice and suitable manner.
Parameters passed : array &$row, array $original_row + Any other parameter passed by the query through fetch's second argument
Result callback must use the second argument for reference and must modify the first argument, which is referenced to the original variable and is automatically picked up. The callback is not required to return anything.
Example (Continuing from the previous one) :
Guidelines for general query fetching
The general flow of query fetching is that a query is run, a while loop is executed fetching the rows and the information from rows are added to the main array in a differently formatted way than originally fetched. The main array is then passed on to templates.
Now this has one fundamental flaw when implementing with this query model, which is if a new column is fetched, it will not be added since a new array is formed and just be there hanging. The simple solution is that the initial assignation should be handled by a result callback itself, and then the row would be carried over and modified by later addons. Example :
As you can see, the row is modified by a callback and then appended to the main array($values).
An addon can extend this as :
Now the values in $values array would have another field called "var" having values from "another_field" in the MySQL table.
Miscellaneous
Sub-queries
Sub queries are also important to handle properly, instead of being passed as a string, they are now treated as an individual parameter with type called "query". Another wesqlQuery is passed for sub-queries. Add-ons can also hook into this query same way as above, but result callbacks will not be called(Instead they'll be called for the main query).
Example :
Unions
Unions are treated similarly to sub-queries, 2 wesqlQuery objects must be passed to a main query under union SQL struct
Example :
A fairly complex builder example
Why not?
For the fol. SQL query :
Code: [Select] The fol. code can be used
I've attached the current Class-DBQuery.php containing wesqlQuery and other required classes.
One of the few remaining major flaw with the current hook system is that queries and data loading from MySQL cannot be hooked in any form unless the result itself is passed as a hook, but that requires creating innumerable amount of hooks. Even then it wouldn't solve the modifying query problem. One easy solution is just to add an ID to every query and pass the string to hooks, so that they can be modified via string manipulation, but that is a very bad idea because it can easily mess up query bad.
Another idea I had is to objectify the query, such that every SQL statement is an individual variable, this way add-ons can just add to those arrays in order to add there own statements.
Update : Upon further discussion I have a more concrete implementation made, here's an update summarizing the current progress. Please read the whole update before providing your opinions, any feedback is appreciated.
The aim of the current solution is to vaguely objectify the queries, by "vaguely" I mean that only the major portion of the query, i.e. "SELCT, WHERE etc" statement's SQL lines are passed. The SQL passed for those statement itself is not objectified. Although possible, doing so would severely limit down the syntax and offer hardly any more advantages.
Basic query functions
wesqlQuery::__construct(string $name, array $sql_params, array $params)
This is the beginning point of any query, an unique name must be passed. "sql_params" is an array containing various SQL structs and their corresponding sql statements. Every individual SQL statement of a SQL struct must be passed as an individual array value. "params" is your traditional query parameter, the parameters are defined in same way as the correct. Example :
new wesqlQuery('example', array(
'select' => array('field', 'other_field'),
'from' => array('table'),
'where' => array('member = {int:member}'),
), array(
'member' => 1,
));
SELECT field, other_field
FROM table
WHERE member = 1
new wesqlQuery('example', array(
'select' => array('field', 'other_field'),
'from' => array('table'),
'where' => array(
'member = {int:member}',
'field' = {string:somefieldval}',
),
), array(
'member' => 1,
'somefieldval' => 'test',
));
SELECT field, other_field
FROM table
WHERE member = 1
AND field = 'test'
Passing extra options to SQL structs
Some special options such as "SELECT DISTINCT" or "UNION ALL" can also be used, detailed documentation for every struct and their extra options can be found later. In order to pass extra options to structs, the struct array should contain 2 child arrays, first one should contain the SQL statements, second one should contain the parameters passed.
Example(Only shows the select part, rest is assumed) :
new wesqlQuery('example', array(
'select' => array(
array('field', 'other_field'),
array('distinct' => true),
),
));
wesqlQuery::bindSQLParam(string $name, array $value, array $extraOptions)
Instead of passing all the SQL params through __construct, one can bind individual SQL structs using this method. Please remember that values are not replaced by this method but are appended to the existing methods.
Example :
$query->bindSQLParam('where', 'member = 1');
extraOptions behave similarly to above
$query->bindSQLParam('select', array('test', 'field'), array('distinct' => true));
wesqlQuery::bindParam(string $name, mixed $value)
Like above, query parameters can also be added through this method.
wesqlQuery::execute()
Executes a query, throws an error if any.
wesqlQuery::fetch(bool $run_callbacks = true, array $hook_parameters)
Fetches the next row, if run_callbacks is set to true it'll also run the row through all the callbacks and then return the resulting row. More on callbacks and hooks later.
Hooking and modifying queries
The major point of objectifying queries is to be able to modify them through hooks without running string functions and/or conflicting with others. For every query made, a new hook called "query_<name>" is called as soon as a query is executed. Only one parameter is passed and that is the instance of the query itself, from there you cannot execute the query again as that will throw an error. You have access to all the bind functions as well as other helper functions, but fetch or other functions which rely on result would not be available.
Here's an example of a query, to which an add-on adds a new SELECT field as well as a WHERE clause
new wesqlQuery('sample', array(
'select' => array('field'),
'from' => array('table'),
));
add_hook('query_sample', 'query_sample_hook', false);
function query_sample_hook(wesqlQuery $query)
{
$query->bindSQLParam('select', 'another_field');
$query->bindSQLParam('where', 'field = {string:var}');
$query->bindParam('var', 'test');
}
What about results?
Modifying query is useless until it can actually be accessed by the template via the result that is stored. Worry not, we have result callbacks. Each result callback is passed every row fetched individually, and it's supposed to modify the row in a nice and suitable manner.
Parameters passed : array &$row, array $original_row + Any other parameter passed by the query through fetch's second argument
Result callback must use the second argument for reference and must modify the first argument, which is referenced to the original variable and is automatically picked up. The callback is not required to return anything.
Example (Continuing from the previous one) :
function query_sample_hook(wesqlQuery $query)
{
$query->bindSQLParam('select', 'another_field');
$query->bindSQLParam('where', 'field = {string:var}');
$query->bindParam('var', 'test');
$query->addResultCallback('query_sample_result_callback');
}
function query_sample_result_callback(&$row, $original)
{
$row['var'] = $original['another_field'];
}
Guidelines for general query fetching
The general flow of query fetching is that a query is run, a while loop is executed fetching the rows and the information from rows are added to the main array in a differently formatted way than originally fetched. The main array is then passed on to templates.
Now this has one fundamental flaw when implementing with this query model, which is if a new column is fetched, it will not be added since a new array is formed and just be there hanging. The simple solution is that the initial assignation should be handled by a result callback itself, and then the row would be carried over and modified by later addons. Example :
$query = new wesqlQuery('sample', array(
'select' => array('field', 'some_field'),
'from' => 'table',
));
$query->addResultCallback('query_sample_initial_result');
$values = array();
while ($row = $query->fetch())
$values[] = $row;
function query_sample_initial_result(&$row, $original)
{
$row = array(
'id' => $row['field'],
'field' => $row['some_field'],
);
}
An addon can extend this as :
add_hook('query_sample', 'query_sample_hook', false);
function query_sample_hook(wesqlQuery $query)
{
$query->bindSQLParam('select', 'another_field');
$query->bindSQLParam('where', 'field = {string:var}');
$query->bindParam('var', 'test');
$query->addResultCallback('query_sample_result_callback');
}
function query_sample_result_callback(&$row, $original)
{
$row['var'] = $original['another_field'];
}
Now the values in $values array would have another field called "var" having values from "another_field" in the MySQL table.
Miscellaneous
Sub-queries
Sub queries are also important to handle properly, instead of being passed as a string, they are now treated as an individual parameter with type called "query". Another wesqlQuery is passed for sub-queries. Add-ons can also hook into this query same way as above, but result callbacks will not be called(Instead they'll be called for the main query).
Example :
new wesqlQuery('example', array(
'select' => array('field', 'other_field'),
'from' => array('table'),
'where' => array(
'member = {query:member_query}',
),
), array(
'member_query' => new wesqlQuery('example_member', array(
'select' => array('id_member'),
'from' => '{db_prefix}members',
)),
));
Unions
Unions are treated similarly to sub-queries, 2 wesqlQuery objects must be passed to a main query under union SQL struct
Example :
new wesqlQuery('example', array(
'union' => array(new wesqlQuery(....), new wesqlQuery(.....)),
));
A fairly complex builder example
Why not?
For the fol. SQL query :
(
SELECT DISTINCT t1.field1, t2.field2
FROM wedge_table1 AS t1
INNER JOIN wedge_table2 AS t2 ON (t1.field3 = t2.field4)
INNER JOIN wedge_table4 AS t4 ON (t4.field7 = t2.field7)
LEFT JOIN wedge_table3 AS t3 ON (t3.mem = t2.mem)
WHERE t1.member = 5 AND (t2.field6 = 0 OR t3.field6 = 0)
GROUP BY t1.field1
HAVING t1.field1 = 'test'
ORDER BY t1.member ASC
LIMIT 1, 50
) UNION ALL (
SELECT IFNULL(m.name, mem.name) AS name, mem.id_member
FROM wedge_mems AS m
INNER JOIN wedge_members AS mem ON (m.id_member = mem.id_member)
WHERE m.id_member IN (
SELECT p.id_member
FROM wedge_players AS p
WHERE p.field IN ('hockey', 'cricket')
)
ORDER BY mem.id_member DESC
)
new wesqlQuery('example', array(
'union' => array(
new wesqlQuery('example_sub1', array(
'select' => array(
array('t1.field1', 't2.field2'),
array('distinct' => true),
),
'from' => '{db_prefix}table1 AS t1',
'inner_join' => array(
'{db_prefix}table2 AS t2 ON (t1.field3 = t2.field4)',
'{db_prefix}table4 AS t4 ON (t4.field7 = t2.field7)',
),
'left_join' => '{db_prefix}table3 AS t3 ON (t3.mem = t2.mem)',
'where' => array(
't1.member = {member}',
'(t2.field6 = 0 OR t3.field6 = 0)',
),
'group_by' => 't1.field1',
'having' => 't1.field1 = {field1}',
'order_by' => 't1.member ASC',
'limit' => '1, 50',
), array(
'member' => array('int', 5),
'field1' => array('string', 'test'),
)
),
new wesqlQuery('example_sub2', array(
'select' => array('IFNULL(m.name, mem.name) AS name', 'mem.id_member'),
'from' => '{db_prefix}mems AS m',
'inner_join' => '{db_prefix}members AS mem ON (m.id_member = mem.id_member)',
'where' => 'mem.id_member IN ({mem_player_query})',
'order_by' => 'mem.id_member DESC',
), array(
'mem_player_query' => array('query', new wesqlQuery('example_sub2_sub', array(
'select' => 'p.id_member',
'from' => '{db_prefix}players AS p',
'where' => 'p.pfield IN ({fields})',
), array(
'fields' => array('array_string', array('hockey', 'cricket')),
)
)),
)
)
),
));
I've attached the current Class-DBQuery.php containing wesqlQuery and other required classes.