Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Try Vanilla Forums Cloud product
Vanilla 2.6 is here! It includes security fixes and requires PHP 7.0. We have therefore ALSO released Vanilla 2.5.2 with security patches if you are still on PHP 5.6 to give you additional time to upgrade.
Please upgrade to 2.3 here. The 2.2 and earlier branches are no longer being updated.

I Like This - Reset 'Likes' every month

This discussion is related to the I Like This ( Plugin ) addon.

I'm using the excellent I Like This plugin as the basis for a simple reputation system. It adds two variables to the user table: 'Liked' and 'ILiked'. It's not the original intention of the plugin but I'm using 'Liked' to record the all-time score and 'ILiked' to track a monthly tally.

Is it possible to write a plugin that automatically resets everyone's 'ILiked' to 0 at the end of each calendar month? I will be happy to pay for the work because I don't know where to start on this one.

Comments

  • 50sQuiff50sQuiff ✭✭
    edited November 2012

    I've solved part one of this problem: a cron job that executes at the end of each month:

    59 23 * * * [ date -d tomorrow +%d -eq '01' ] && wget -q http://myforum.com/cronjob

    Now I need to create a plugin that handles the request to that page, validates the IP address of the request, then resets everyone's ILiked count to 0.

  • why are you using wget for a cronjob? just curious. If it is already on the server that is.

    grep is your friend.

  • 50sQuiff50sQuiff ✭✭
    edited November 2012

    I was thinking of triggering a pageload in Vanilla. But if the cronjob is running on the same server I guess I could just execute a "php randomtask.php" command? Just figuring these things out for the first time here.

  • You can write a plugin that uses the cronjob functionality of this plugin: http://vanillaforums.org/addon/cronjobs-plugin

    The documentation should be pretty clear. In your plugin you will have to execute the corresponding SQL command.

  • @50sQuiff

    yes that would make more sense. Unless you want specifically to trigger a server request, to load the framework, in which case you can do an internal request. If you are not using the framework or server environment variables just standalone code then php randomtask.php would do.

    grep is your friend.

    businessdad
  • I'm at the stage now where I just need to issue the right SQL command but I can't figure out how from within the framework. I want to set the the entire ILiked column to 0 for all records in the User table. Can anyone help?

  • UPDATE GDN_User SET ILiked = '0';

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

  • 50sQuiff50sQuiff ✭✭
    edited November 2012

    Peregrine, I'm being a bit of a dunce but how do I execute that from within a Vanilla plugin? I've become familiar with building queries but I don't know how to run an arbitrary command like that.

    I decided to go down the plugin route because cheap shared hosts won't allow cronjobs, so I'll need to trigger the reset remotely. I'm validating the IP address of the requestor so there shouldn't be a problem there.

    By the way, the "I Like This" plugin can be gamed in its current form. If someone copies a 'Like' URL and loads it repeatedly they can increase the Like count of the recipient indefinitely. I've written a workaround for this that doesn't increase the number of DB queries. If anyone wants my (messy) code just PM me.

    I've moved the query that grabs the Likes for a given post to earlier in the main Like handling function. This then enables you to validate the Like based on whether the user has already Liked the post or not. As a consequence, when it comes to rendering the Like counter we're dealing with a stale array that doesn't include the new Like. So I pass a flag to the rendering function and then fake the new Like by just adding 1 to the total.

    I hope that helps someone.

  • This is how you should be able to execute SQL-commands in vanilla:

    $SQL = Gdn::SQL();
    $SQL
       ->Update('User')
       ->Set('ILiked', '0')
       ->Put();
    
    50sQuiff
  • It works! Thank you sirs.

  • x00x00 MVP
    edited November 2012

    if you are doing it per request then why don't you do it based on session? Just reset each user at a time, save some overhead. Save the purge date.

    grep is your friend.

  • Most cheap host don't have crontab, though many of them have some crappy cron job system, which could be adequate. Never used myself.

    You could also use a remote cron service, but you would need secure it. Otherwise it could be abused.

    grep is your friend.

  • @x00 said:
    if you are doing it per request then why don't you do it based on session? Just reset each user at a time, save some overhead. Save the purge date.

    That would make it impossible to rank posters though. People who don't log in won't have their scores reset.

  • true, if you have a small forum then fine. I'm just saying someone is going to wait a little bit longer.

    The idea of crons is you do them when most people aren't around.

    grep is your friend.

  • hbfhbf wiki guy? MVP

    @50sQuiff said:
    Peregrine, I'm being a bit of a dunce but how do I execute that from within a Vanilla plugin? I've become familiar with building queries but I don't know how to run an arbitrary command like that.

    I decided to go down the plugin route because cheap shared hosts won't allow cronjobs, so I'll need to trigger the reset remotely. I'm validating the IP address of the requestor so there shouldn't be a problem there.

    By the way, the "I Like This" plugin can be gamed in its current form. If someone copies a 'Like' URL and loads it repeatedly they can increase the Like count of the recipient indefinitely. I've written a workaround for this that doesn't increase the number of DB queries. If anyone wants my (messy) code just PM me.

    I've moved the query that grabs the Likes for a given post to earlier in the main Like handling function. This then enables you to validate the Like based on whether the user has already Liked the post or not. As a consequence, when it comes to rendering the Like counter we're dealing with a stale array that doesn't include the new Like. So I pass a flag to the rendering function and then fake the new Like by just adding 1 to the total.

    I hope that helps someone.

    the loophole you identified can be shored up cleanly in the DiscussionController_Like_Create section just by checking if the user is allowed to like something or not. I patched that up for someone, but it was tied to karma rules and not really appropriate for re-distribution. If i get around to doing an update i'll apply a more generic patch.

    BTW - if you have any intention of sharing your plugin, you should really change the data model so you aren't using my table columns. otherwise anyone who tries to run both plugins will encounter nothing but trouble. its super easy to do, just look at the bottom of the default.php and the likemodel.php

  • businessdadbusinessdad Stealth contributor MVP

    @x00 said:
    Most cheap host don't have crontab, though many of them have some crappy cron job system, which could be adequate. Never used myself.

    You could also use a remote cron service, but you would need secure it. Otherwise it could be abused.

    @x00 You're right, that's why the CronJob plugin supports an identification key, IP filtering and a throttling mechanism. I worked with framework providing unsecured cron functionality and I always got amazed on how easy it could be to bring the site down by just firing cron.php over and over again.

  • Somewhat related to this topic: I'm showing a ranking of posters in the sidebar, by all-time Likes and monthly Likes.

    Are the 'Liked' and 'ILiked' columns in the User table indexed automagically or are these queries going to be slow? If they're not indexed, how do I change that?

  • businessdadbusinessdad Stealth contributor MVP

    I believe that it's quite easy to verify: check if the two tables have indexes on those columns. If they don't, then they are not going to have them, unlesse the plugin follows some special logic to decide when to alter the tables.

    If you want to index these columns, just edit the table structure with your favourite tool, or even just run an ALTER TABLE statement.

  • Confirmed - they're not indexed. Now, I wonder how I can get them indexed from within the Framework. I'll be exploring that this evening but if anyone already knows the answer... ;)

  • businessdadbusinessdad Stealth contributor MVP

    I'm not sure I understand what do you mean with "getting them indexed from within the Framework". Index are created, once off, on the table itself. If plugin doesn't do it, you either have to do it manually, or alter the code to add such indexes. However, such operation is normally executed on installation, therefore, even if you modified the code, you would have to disable/enable the plugin.

  • hbfhbf wiki guy? MVP

    I did not index these columns within the plugin setup method. you can do that through phpmyadmin or whatever mysql admin tool you use. it's not really necessary to modify the plugin code to do it for you.

  • 50sQuiff50sQuiff ✭✭
    edited November 2012

    @businessdad said:
    I'm not sure I understand what do you mean with "getting them indexed from within the Framework". Index are created, once off, on the table itself. If plugin doesn't do it, you either have to do it manually, or alter the code to add such indexes. However, such operation is normally executed on installation, therefore, even if you modified the code, you would have to disable/enable the plugin.

    I would've thought it's fairly obvious what I meant. When you install Vanilla, do you have to use phpMyAdmin to index the UserID and Email columns? No, but they're indexed all right. So somewhere in the Garden/Vanilla framework the User table is being created with certain columns indexed.

    I'll be grepping around to find out how to do this. In case anyone already knows, do share.

  • businessdadbusinessdad Stealth contributor MVP

    @50sQuiff The framework itself doesn't take care of creating indexes on fields added by plugins. As I explained in my previous post, it's the plugin itself that, by using the Schema object, creates and alters the required tables.

    How can you do that?
    You can do it by using Gdn::Database()->Database->Structure() object. Example:

    Gdn::Database()->Database->Structure()->Query('ALTER TABLE MyTable ADD INDEX ...');

    50sQuiff
  • Thanks for that BD!

    I think you're arguing semantics though. You're adding the index via the framework there, rather than using an external tool. Ideal. I'll post back with the results later.

  • Also, some research and testing is required to see if this is the correct decision, given the potential impact of indexing on these constantly updating columns.

  • @50sQuiff said:
    Also, some research and testing is required to see if this is the correct decision, given the potential impact of indexing on these constantly updating columns.

    you can always delete the indexes if things go south performance wise, it won't be any worse than the initial setup.

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

  • 50sQuiff50sQuiff ✭✭
    edited November 2012

    The answer was exceedingly simple. Declare your columns indexed as follows:

    GDN::Structure() ->Table('AllLikes') ->PrimaryKey('ID') ->Column('CommentID', 'int(11)', TRUE, 'index') ->Column('DiscussionID', 'int(11)', TRUE, 'index') ->Column('UserID', 'int(11)', FALSE) ->Set(TRUE);

  • hbfhbf wiki guy? MVP
    edited November 2012

    @50sQuiff said:
    The answer was exceedingly simple. Declare your columns indexed as follows:

    GDN::Structure() ->Table('AllLikes') ->PrimaryKey('ID') ->Column('CommentID', 'int(11)', TRUE, 'index') ->Column('DiscussionID', 'int(11)', TRUE, 'index') ->Column('UserID', 'int(11)', FALSE) ->Set(TRUE);

    you then have to disable, then re-enable the plugin.

    BTW - indexing isnt free.

  • hbfhbf wiki guy? MVP

    also, the code above doesn't impact the indexing of the user table. that is the code which creates the AllLikes table.

  • 50sQuiff50sQuiff ✭✭
    edited November 2012

    I know and... I know. I was just posting back so anyone searching for how to set up indexes in their plugins knows what to do.

Sign In or Register to comment.