Allowing Extensions to Modify Any Database Query

[BondGamer]

I wanted to create my first extension so I went about trying to do a simple one to prevent a topic view count increasing if viewed by the topic creator. But as far as I can tell this is impossible. There is no way to modify any specific database query. And you can't modify the files direction anymore.

What if every database query had a unique identity number attached to it? Then all you need is one hook in the database abstraction function and extensions can target specific database queries to modify and/or perform their own functions whenever that specific query is called. So now every database query will look like: sql_query($sql, 3745) and all an extension needs to do is specify if($query_id = 3745){do stuff}.

[Pony99CA]

There is no way to modify any specific database query. And you can't modify the files direction anymore.

That's not strictly true. phpBB is Open Source, so you are free to modify the code any way that you like. You won't get an Extension approved by the Extensions team if you do that, however.

To get back to your point, there are places in phpBB where the query is built up from smaller pieces, so maybe you need an extension there (if there isn't one already). For example, displaying posts in a topic probably creates the SQL ORDER BY clause as a piece using the user's currently selected sorting preference.

Not increasing the view count if a topic creator views the topic would probably be adding an AND clause something like AND user_id <> topic_creator as another piece of the query.

Steve

[Nicofuma]

I disagree with the initial proposal. If you want to edit an SQL query just require an event where the query is built and use this event to transmit and alter the query

[RMcGirr83]

Pretty sure the OP is talking about this within viewtopic.php

Code: Select all

// Update topic view and if necessary attachment view counters ... but only for humans and if this is the first 'page view'
if (isset($user->data['session_page']) && !$user->data['is_bot'] && (strpos($user->data['session_page'], '&t=' . $topic_id) === false || isset($user->data['session_created'])))
{
    $sql = 'UPDATE ' . TOPICS_TABLE . '
        SET topic_views = topic_views + 1, topic_last_view_time = ' . time() . "
        WHERE topic_id = $topic_id";
    $db->sql_query($sql);

    // Update the attachment download counts
    if (sizeof($update_count))
    {
        $sql = 'UPDATE ' . ATTACHMENTS_TABLE . '
            SET download_count = download_count + 1
            WHERE ' . $db->sql_in_set('attach_id', array_unique($update_count));
        $db->sql_query($sql);
    }
} 

not sure how one edits a SQL query UPDATE?

[BondGamer]

I disagree with the initial proposal. If you want to edit an SQL query just require an event where the query is built and use this event to transmit and alter the query

But then you would need an event at every single location where a SQL query is built. That is currently not the case. Putting events all over will bloat the files and duplicate code. It would be much cleaner for it to be part of the query function.

not sure how one edits a SQL query UPDATE?

In this extension's case I would have it cancel the entire SQL query by putting in a return to exit the function so it skips executing the database command.

[brunoais]

I disagree with the initial proposal. If you want to edit an SQL query just require an event where the query is built and use this event to transmit and alter the query

But then you would need an event at every single location where a SQL query is built. That is currently not the case. Putting events all over will bloat the files and duplicate code. It would be much cleaner for it to be part of the query function.

Not really... The event may be in the DBAL instead of in every place in the source code.
The issue here is that many events will be called unnecessarily if the dispatcher is in that place (inside DBAL). This means phpBB would easily become dead slow. That's definitely not what we want.

[Pony99CA]

I think what the OP wants is a master routine where each possible SQL query is identified by a unique ID and you can change the FROM, WHERE, SET, etc. clauses based on your needs.

For example, in the code snippet that RMcGirr83 posted, you have two queries, possibly named UPDATE_TOPIC_VIEW_COUNT and UPDATE_ATTACHMENT_DOWNLOAD_COUNT. Parameters might be the current values of the SET, FROM and WHERE clauses. For the OP's example, he could do one of two things (in pseudo-code):

1. Append an AND condition to the WHERE clause like I suggested -- IF $query = UPDATE_TOPIC_VIEW_COUNT THEN $where_clause = $where_clause & "AND" & %user_id & "<>" & topic_creator
2. Cancel the execution of the SQL completely -- IF $user_ID = $topic_starter THEN return

The question is how practical it would be to go through every query in phpBB and assign names to them and break them into pieces compared to creating events within a query as needed. There's also the question of whether some types of queries wouldn't lend themselves to this treatment.

Steve

[BondGamer]

Yeah, Pony explained it better than I did. It was just an idea to allow extensions the ability to execute code or add/modify database queries any place there is activity with the database. I don't know how useful this would be overall.

[nickvergessen]

What if every database query had a unique identity number attached to it?

That is somewhat impossible. What if 2 Pull Requests add queries, they create a conflict and one needs to be rebased.
Also the idea of having a map for the queries somewhere is somewhat scary