Confused with database and models

Aviram

Another question:

Why should I have a table containing balances and a table for transactions when I can just have a table for transactions and then calculate balances by that?

hgtonight

Denormalization usually occurs for cache optimization. The calculation isn't free in terms of resources. The select is nearly free.

Aviram

Thanks!
So I'm starting to create my database but for some reason the test I try to run doesn't work, I call for a function to add stars (like karma) to user from user etc.

The transaction gets inserted successfully but the User columns I'm trying to fill become empty or 0 after running it

Another question is why Setup runs twice? (I see that because 2 transaction gets added everytime I install the plugin)

Also for some reason I can't disable the plugin from the dashboard, I can only enable it (I use config meanwhile).

Aviram

OK I dug thru the forums and found that my plugin didn't disable because it's name and it's folder name needed to match case-sensitive. but other problems still occur

Edit: I think I found out (I had a typo) but I'm still wondering why the function I call in setup is called twice.

Edit2: OK So now my code inserts the Starslevel but the other columns it doesn't.

R_J

To answer "why does my plugin do this and that" questions without seeing the code is really hard

Aviram

@R_J
Oh crap! I was sure I pastebin'd it!!
Sorry, here it is: http://pastebin.com/bJe3LYxf

R_J

I'm not quite sure what part of your code is not working as expected but it is one of those update or insert sqls, right? Have you checked them if they are looking the way you expect them to look?

For debugging, add those lines to your config:
$Configuration['Garden']['Errors']['MasterView'] = 'deverror.master.php'
$Configuration['Garden']['Debug'] = TRUE;

Instead of using var_dump or something like that, you could use Vanillas function decho().

Aviram

@R_J :

I'm not sure how to use decho to debug the query I'm sending but here's my attempt:

decho(Gdn::SQL()->Update('User')
->Set(array('Stars' => "Stars + {$Amount}", "Starsgiven" => "Starsgiven + 1","StarLevel" => $Level),FALSE)
->Where('UserID',$Receiver)
->Put());

This doesn't print anything . The problem is nothing gets saved in this query except for the StarLevel.

R_J

If you've set all those config settings, you could see every sql at the end of each page (if you are logged in as admin).
But if you want to Get the Select statement of any sql you've built, just use ->GetSelect() instead of ->Put()

$Query = GDN::SQL();
$Query->Update('User')
   ->Set(array('Stars' => "Stars + {$Amount}",
      "Starsgiven" => "Starsgiven + 1",
      "StarLevel" => $Level),
      FALSE
   )
   ->Where('UserID',$Receiver);
decho($Query->GetSelect());

Aviram

@R_J :
I think decho doesn't output anything to me unless I use die() because it's in the Setup() procedure.

Anyway I couldn't fix it so I tried another syntax for using set, don't know why Set with array didn't work for me, but this does:

$Level = $this->GetUserLevel(50);
Gdn::SQL()->Update('User')
->Set('Stars' , "Stars + ".$Amount,FALSE)
->Set("Starsgiven","Starsgiven + 1",FALSE)
->Set("StarLevel" , $Level)
->Where('UserID',$Receiver)
->Put();



But I still wonder why my function is called twice within Setup when enabling the plugin??

R_J

I've tested your plugin and reduced your setup to that call (in fact I have reduced the hole plugin to that):

public function Setup() {

   $Receiver = 1;
   $Sender = 1;
   $Amount = 5;
   $DiscussionID = 1;
   $CommentID =1;

   Gdn::SQL()->Insert('StarsTrans',
      array(
         'ReceiveUserID'=>$Receiver,
         'InsertUserID'=>$Sender,
         'Date'=>Gdn_Format::ToDateTime(),
         'Amount'=>$Amount,
         'DiscussionID'=>$DiscussionID,
         'CommentID'=>$CommentID
      )
   );
}

I can confirm that this code will be executed twice but I do not have the slightest idea why, sorry...

peregrine

. never mind.

Aviram

@R_J: Thanks but the part of the code you pasted always worked.. the part that didn't work was Set with array as parameter for fields and values.

But nevermind that, I have another question for the great minded here.

I need to make a cronjob that calls a PHP function to reset the sending stars limit.

Each user has his level stored in the database and each levels gets different amounts of green stars and yellow stars.

So my question is what would be the best way to iterate over all users and reset their limits?

Should I create UserModel and iterate all users? If anyone have any good idea I'd be happy to hear.

BTW I think after I get this plugin done I should summarize this thread and make it sort of an article for creating a plugin.

peregrine

make it sort of an article for creating a plugin.

you could put the article in the plugin, when you upload it to the add-ons section.

Aviram

@peregrine:


Yup, but first I need to finish it then document my code properly, I wrote half of it while being sleepy so it doesn't stand good coder's standards

R_J

I've not tested if the above piece of code works, but if Setup is really called twice which seems to be the case...

@Aviram said:
Each user has his level stored in the database and each levels gets different amounts of green stars and yellow stars.

So my question is what would be the best way to iterate over all users and reset their limits?

I guess you where sleepy when you've asked that question, too

update gdn_user set starslimit = 10 or with the query builder:

Gdn::SQL()->Update('User')
   ->Set('starslimit', 10)
   ->Put()

Setting up an additional cron job will make it hard for others to use your plugin. Maybe you could realize the reset individually with a check of some date stamp whenever someone wants to give a star?

peregrine

@R_J said:

Maybe you could realize the reset individually with a check of some date stamp whenever someone wants to give a star?

you could update on posts and signins for each individual instead of a massive update - it would be the least amount of resource use and would give the user access to them.

peregrine

I'm still confused.

why would you even bother putting updates, inserts, etc in setup

just call structure and

just put a default value for the column variable when structuring the database.

->Column('Superstars', 'int', '10')

Aviram

@peregrine:


It was just to test. I'm not actually doing any updates on the Setup.

@R_J:

You misunderstood me, each user has his own limit that depends on his level. I made a column in the User table called Greens and one called Yellows. Those are two types of stars a user can give to another and he has limited amounts of them every day based on his level, so I can't use one query of update to reset...

R_J

If it is based on user levels you would have to use one query per level. Maybe it could even be caalculated? update gdn_user set starslimit = 10 + (level * 2) or something like that. Setting it with sql would be way more faster than looping through all users.
But as said before, I'd look for a plugin only solution and that will exclude cron jobs.