Allowing Extensions to Modify Any Database Query

General discussion of development ideas and the approaches taken in the 3.x branch of phpBB. The next feature release of phpBB 3 will be 3.3/Proteus.
Forum rules
Please do not post support questions regarding installing, updating, or upgrading phpBB 3.2.x. If you need support for phpBB 3.2.x please visit the 3.2.x Support Forum on phpbb.com.

If you have questions regarding writing extensions please post in Extension Writers Discussion to receive proper guidance from our staff and community.
Post Reply
BondGamer
Registered User
Posts: 112
Joined: Mon Dec 15, 2003 8:20 pm
Contact:

Allowing Extensions to Modify Any Database Query

Post by BondGamer » Sun Dec 07, 2014 4:38 pm

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}.

User avatar
Pony99CA
Registered User
Posts: 986
Joined: Sun Feb 08, 2009 2:35 am
Location: Hollister, CA
Contact:

Re: Allowing Extensions to Modify Any Database Query

Post by Pony99CA » Tue Dec 09, 2014 3:13 am

BondGamer wrote: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
Silicon Valley Pocket PC (http://www.svpocketpc.com)
Creator of manage_bots and spoof_user (ask me)
Need hosting for a small forum with full cPanel & MySQL access? Contact me or PM me.

Nicofuma
3.2 Release Manager
3.2 Release Manager
Posts: 299
Joined: Sun Apr 13, 2014 1:40 am
Location: Paris

Re: Allowing Extensions to Modify Any Database Query

Post by Nicofuma » Tue Dec 09, 2014 10:47 am

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
Member of the phpBB Development-Team
No Support via PM

User avatar
RMcGirr83
Registered User
Posts: 357
Joined: Fri Mar 09, 2007 1:51 am
Contact:

Re: Allowing Extensions to Modify Any Database Query

Post by RMcGirr83 » Tue Dec 09, 2014 11:21 am

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?
Do not hire Christian Bullock he won't finish the job and will keep your money

BondGamer
Registered User
Posts: 112
Joined: Mon Dec 15, 2003 8:20 pm
Contact:

Re: Allowing Extensions to Modify Any Database Query

Post by BondGamer » Tue Dec 09, 2014 1:42 pm

Nicofuma wrote: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.
RMcGirr83 wrote: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.

User avatar
brunoais
Registered User
Posts: 964
Joined: Fri Dec 18, 2009 3:55 pm

Re: Allowing Extensions to Modify Any Database Query

Post by brunoais » Tue Dec 09, 2014 4:17 pm

BondGamer wrote:
Nicofuma wrote: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.

User avatar
Pony99CA
Registered User
Posts: 986
Joined: Sun Feb 08, 2009 2:35 am
Location: Hollister, CA
Contact:

Re: Allowing Extensions to Modify Any Database Query

Post by Pony99CA » Wed Dec 10, 2014 1:43 am

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
Silicon Valley Pocket PC (http://www.svpocketpc.com)
Creator of manage_bots and spoof_user (ask me)
Need hosting for a small forum with full cPanel & MySQL access? Contact me or PM me.

BondGamer
Registered User
Posts: 112
Joined: Mon Dec 15, 2003 8:20 pm
Contact:

Re: Allowing Extensions to Modify Any Database Query

Post by BondGamer » Wed Dec 10, 2014 1:52 am

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.

User avatar
nickvergessen
Former Team Member
Posts: 733
Joined: Sun Oct 07, 2007 11:54 am
Location: Stuttgart, Germany
Contact:

Re: Allowing Extensions to Modify Any Database Query

Post by nickvergessen » Sun Dec 14, 2014 9:39 pm

BondGamer wrote: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
Member of the Development-TeamNo Support via PM

Post Reply