Hooking up data loading
[Plugin] Awards »

Dragooon

  • I can code! Really!
  • polygon.com has to be one of the best sites I've seen recently.
  • Posts: 1,841
Hooking up data loading
« on September 25th, 2011, 10:46 AM »Last edited on May 30th, 2012, 07:03 PM
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 :
new wesqlQuery('example', array(
    
'select' => array('field''other_field'),
    
'from' => array('table'),
    
'where' => array('member = {int:member}'),
), array(
    
'member' => 1,
));
This will produce the fol. SQL query :
Code: [Select]
SELECT field, other_field
FROM table
WHERE member = 1
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
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',
));
Will produce this SQL query :
Code: [Select]
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'],
    );
}
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 :
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 :
Code: [Select]
(
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
)
The fol. code can be used
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.

 Class-DBQuery.zip - 4.89 kB, downloaded 52 times.

The way it's meant to be

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Re: Hooking up data loading
« Reply #1, on September 25th, 2011, 11:19 AM »
Well, I like the idea and general execution. I don't think I'd attach it to every query, I'd leave a number of queries alone with the existing infrastructure, without any need to expand *every* query.

Then it's just a case of extending the add-on manager to handle query hooks but that's more dependent on the query architecture to be honest.
When we unite against a common enemy that attacks our ethos, it nurtures group solidarity. Trolls are sensational, yes, but we keep everyone honest. | Game Memorial

Dragooon

  • I can code! Really!
  • polygon.com has to be one of the best sites I've seen recently.
  • Posts: 1,841

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Re: Hooking up data loading
« Reply #3, on September 25th, 2011, 11:49 AM »
The ones people actually want to modify, like the monster in loadMemberData, or the one for getting topics, or even the messages request query.

Stuff like the permissions loading queries wouldn't need to be hook able and would in fact be counterproductive to do so because the revised design I have in mind would do away with the need to actually hook onto it, and just use the data itself (like using the current hook in SMF, except with the facility to superset it)

Dragooon

  • I can code! Really!
  • polygon.com has to be one of the best sites I've seen recently.
  • Posts: 1,841
Re: Hooking up data loading
« Reply #4, on September 25th, 2011, 11:57 AM »
Okay, that makes sense. What do you think about the hooking? I still have to think how to handle the "different" style of loading that we see in loadMemberContext, prepareDisplayContext etc, but I don't see it being much more difficult than it already is.

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Re: Hooking up data loading
« Reply #5, on September 25th, 2011, 12:12 PM »
Well, either you do it that way, or expressly call a hook for each query and each result (as in by hand)

What occurs to me is that even if you extend a query, you will probably have to add a hook somewhere to make use of the result, which almost has to be case dependent.

As an example, the subs-board index queries to get boards. While you can extend the query and do something with the result directly, you still have to put the result into the relevant array, and maybe even something in the template to be able to make use of that array value.

On the other hand, if you extend the query in loadMemberData, the base information is still available in $user_profile even if you don't hook it in loadMemberContext.

Trouble is, I don't think there's a nice way to deal with it, except on a case by case basis.

Dragooon

  • I can code! Really!
  • polygon.com has to be one of the best sites I've seen recently.
  • Posts: 1,841
Re: Hooking up data loading
« Reply #6, on September 25th, 2011, 12:22 PM »
Quote
Trouble is, I don't think there's a nice way to deal with it, except on a case by case basis.
I'm sure it can be dealt with in a nice way, so that case by case basis is not required. Although it'll require some tricky handling, it can still be made part of the whole system.

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Re: Hooking up data loading
« Reply #7, on September 25th, 2011, 12:37 PM »
Making it not case by case requires not only standardising the queries but how the query data is used, cf. Display vs Subs-BoardIndex vs loadMemberData.

Dragooon

  • I can code! Really!
  • polygon.com has to be one of the best sites I've seen recently.
  • Posts: 1,841
Re: Hooking up data loading
« Reply #8, on September 25th, 2011, 12:38 PM »
I just went over loadMemberData and loadMemberContext as well as getBoardIndex, they fit perfectly with my query model. So does prepareDisplayContext. Would anyone want me to actually code the code?

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278

Dragooon

  • I can code! Really!
  • polygon.com has to be one of the best sites I've seen recently.
  • Posts: 1,841
Re: Hooking up data loading
« Reply #10, on September 25th, 2011, 12:44 PM »
The query class, implementation or both? I'd be happy to do both but in case you want to see something else earlier. I'll start in 15 minutes or so.

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Re: Hooking up data loading
« Reply #11, on September 25th, 2011, 03:09 PM »
Well, it's all interrelated, so I'd love to see both, though I'm conscious that I won't be able to really try it out yet.

Dragooon

  • I can code! Really!
  • polygon.com has to be one of the best sites I've seen recently.
  • Posts: 1,841
Re: Hooking up data loading
« Reply #12, on September 25th, 2011, 03:17 PM »
I'll attach the files, you can see how easy or difficult it is to code them. Give me a few hours, quite busy with other stuff. Apparently they preponed my IT exam to be held tomorrow, ah well, wasn't gonna study anyway.

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Re: Hooking up data loading
« Reply #13, on September 25th, 2011, 03:19 PM »
There's really no rush, I'm on my iPad now until Sunday due to family stuff, so unlikely to sit down in front of a desktop before then.

Dragooon

  • I can code! Really!
  • polygon.com has to be one of the best sites I've seen recently.
  • Posts: 1,841
Re: Hooking up data loading
« Reply #14, on September 25th, 2011, 10:47 PM »
I haven't had this kind of rush since quite a while, feels good to be back. Attached are the 2 files, Class-DBQuery.php and Subs-BoardIndex containing the roughest, most brutal test this class can face.

Now I haven't handle getBoardIndex the way it's meant to be handled, simple reason being that I couldn't figure out a way to. But that doesn't make it less extensible, all the parameters are still tossed around and it is still extensible, but it is sort of a "special case" in terms of implementation, but I didn't need to make any sort of adjustment in the query class itself. This is because of the way things are set, the categories and this_category array are tossed like burger on a pan, maybe I missed it, but it works fairly well.

The definitions for SQL Params are probably incomplete in the Class-DBQuery file, but contains the most common ones used : INSERT INTO, VALUES, SELECT, FROM, LEFT JOIN, INNER JOIN, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, UNION(I'm not too sure about this)
One's that still need to be added from the top of my head : REPLACE INTO, INSERT IGNORE INTO, RIGHT JOIN, and quite a few more.

I'll leave it at this, been coding for over 8 hours and it's 2 AM ATM.
Posted: September 25th, 2011, 10:40 PM

Okay, some technicalities, I've made params to be {param} now instead of {type:param}, I've moved type out of param to PHP declaration. Makes more sense and is probably more extensible. I've no idea.

I have also removed query_<name>_result hook in favour of in-line callback handling, this allows control over the order of flow as some functions need to be executed first.
Posted: September 25th, 2011, 10:44 PM

One more thing, I believe getBoardIndex is only one of it's kind, prepareDisplayContext and the likes are far more traditional. Maybe loadMemberContext is similar, I haven't dug into it.

 Sources.zip - 8.22 kB, downloaded 76 times.


[Plugin] Awards »