SQL Improvements

Details of modifications which users can incorporate within Enuuk Auction Platform free of charge
Post Reply
RWAP
Site Admin
Posts: 750
Joined: Fri Jan 08, 2010 2:23 am
Location: Stoke-on-Trent
Contact:

SQL Improvements

Post by RWAP » Fri Jan 15, 2010 8:54 am

The SQL code is causing issues on my server - initially from the point of view of speed, but then with errors and some entries in the database being deleted (particularly payment methods and the category for some reason).

It is probably worth looking at the SQL code being used in the classes and looking to re-write parts of this to gain improvements...

>> Updated to take account of v2.1 <<

In class\Message.php
Find:

Code: Select all

    static private function listInboxMessages(MyPDO $DB, User $user)
    {
        $tmpArr = array();
        $dataMsg = self::getListFromDB($DB,' where toUser="'.$user->id.'" and visibility in("'.self::VISIBLE_BOTH.'","'.self::VISIBLE_RECEIVER.'") ');
        if($dataMsg){
            foreach ($dataMsg as $data) {
                $tmpObj = self::getInstanceFromDB($DB,$data['id']);
                if($tmpObj instanceof Message){
                    $tmpArr[]= $tmpObj;
                }
            }
        }
        return $tmpArr;
    }
Replace this with:

Code: Select all

    static private function listInboxMessages(MyPDO $DB, User $user)
    {
        $dataMsg = self::getListFromDB($DB,' where toUser="'.$user->id.'" and visibility in("'.self::VISIBLE_BOTH.'","'.self::VISIBLE_RECEIVER.'") ');
        if($dataMsg){
            foreach ($dataMsg as &$data){
                $obj = new self;
            	foreach ($data as $k=>$v)
                {
                	if($k == 'toUser'  || $k== 'fromUser'){
                    		$k = '_'.$k;
                    		$user = User::getInstanceFromDB($DB,$v);
                    		if($user instanceof User) $obj->$k = $user;
                	}else{
                    		$k = '_'.$k;
                    		$obj->$k = $v;
                	}
            	}
            	$data=$obj;
            }
            return ($dataMsg);
        }
        return "";
    }
Find:

Code: Select all

    static private function listSentMessages(MyPDO $DB, User $user)
    {
        $tmpArr = array();
        $dataMsg = self::getListFromDB($DB,' where fromUser="'.$user->id.'" and visibility in("'.self::VISIBLE_BOTH.'","'.self::VISIBLE_SENDER.'") ');
        if($dataMsg){
            foreach($dataMsg as $data) {
            $tmpObj =  self::getInstanceFromDB($DB,$data['id']);
                if($tmpObj instanceof Message){
                    $tmpArr[]= $tmpObj;
                }
            }
            return ($tmpArr);
        }else{
            return ($tmpArr);
        }
    }
Replace this with:

Code: Select all

    static private function listSentMessages(MyPDO $DB, User $user)
    {
        $dataMsg = self::getListFromDB($DB,' where fromUser="'.$user->id.'" and visibility in("'.self::VISIBLE_BOTH.'","'.self::VISIBLE_SENDER.'") ');
        if($dataMsg){
            foreach ($dataMsg as &$data){
                $obj = new self;
            	foreach ($data as $k=>$v)
                {
                	if($k == 'toUser'  || $k== 'fromUser'){
                    		$k = '_'.$k;
                    		$user = User::getInstanceFromDB($DB,$v);
                    		if($user instanceof User) $obj->$k = $user;
                	}else{
                    		$k = '_'.$k;
                    		$obj->$k = $v;
                	}
            	}
            	$data=$obj;
            }
            return ($dataMsg);
        }
        return "";
    }
In class\DB.php

Find:

Code: Select all

                    self::$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
Add below this:

Code: Select all

                    self::$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

RWAP
Site Admin
Posts: 750
Joined: Fri Jan 08, 2010 2:23 am
Location: Stoke-on-Trent
Contact:

Re: SQL Improvements

Post by RWAP » Sat Jan 16, 2010 1:42 pm

Just a slight addition to the above to correct a bug:

In class\Message.php,
Find:

Code: Select all

    static public function getListFromDB(MyPDO $DB, $restrictions='')
    {
        $sql = 'select id,toUser,fromUser, subject,message,status,visibility from '.self::DB_TABLE.''.$restrictions;

Change to:

Code: Select all

    static public function getListFromDB(MyPDO $DB, $restrictions='')
    {
        $sql = 'select id,toUser,fromUser, subject,message,messageTime,status,visibility from '.self::DB_TABLE.''.$restrictions;
and In themes\default\messageList.php

Find:

Code: Select all

                                if(count($inbox)){
                                foreach($inbox as $msg){
Change this to:

Code: Select all

                                if($inbox){
                                foreach($inbox as $msg){
Find:

Code: Select all

                                if(count($sent)){
                                foreach($sent as $msg){
Change this to:

Code: Select all

                                if($sent){
                                foreach($sent as $msg){

RWAP
Site Admin
Posts: 750
Joined: Fri Jan 08, 2010 2:23 am
Location: Stoke-on-Trent
Contact:

Re: SQL Improvements

Post by RWAP » Sun May 02, 2010 1:20 am

We have also noticed that every now and then auctions would have a negative relisting value and possibly lose their category.

This is difficult to put your finger on, but we think it may be down to when an owner relists an offer.
The relist routine resets the relisting figure to offer->relisting+1, it then saves everything about the offer and triggers the cron job to relist the offer.

However, it is possible that someone else is viewing the site at the same time, and themselves trigger the cron job, which in turn causes a race between the two routines. OK we are talking miliseconds, but it is the only explanation we can find!

It may also explain why sometimes, when you relist an offer it appears to work, but when you go to the offer details, it is still listed as closed.

We therefore suggest the following changes:

Updated to take account of v3.5

In class\Offer.php
Find:

Code: Select all

    static public function getListOnOneCategory(MyPDO $DB, $categoryId, $onlyActives = false)
    {
        return self::getListFromDB($DB,'where categoryId='.intval($categoryId).
                                    ( $onlyActives ? ' and offers.active=1 ' : '').
                                    'order by endDate');
    }
Add below this:

Code: Select all

    /**
    *   Updates the number of times an item has been relisted
    *   @param  MyPDO   $DB database instance
    *
    */
    public function updateRelistingOnDB(MyPDO $DB)
    {
        $query = $DB->prepare('update  '.self::DB_TABLE.' set relisting=:relisting where id=:id');
        $return = $query->execute(array(':relisting'=>$this->relisting,':id'=>$this->id));
        unset($query);
        return $return;
    }
In class\action\User.php
Find:

Code: Select all

        if($par['action'] == 'relist'){
            $offer = Offer::getInstanceFromDB($this->db, intval($par['offerId']));
            if($offer){
                if($offer->item->user->id == $this->context->loggedUser->id){
                    $offer->relisting = $offer->relisting + 1;
                    $offer->saveToDB($this->db);
                    Cron::relistOffers($this->db, $this->context);
                    $this->context->message = _('Auction reactivated successfully');
Change this to:

Code: Select all

        if($par['action'] == 'relist'){
            $offer = Offer::getInstanceFromDB($this->db, intval($par['offerId']));
            if($offer){
                if($offer->item->user->id == $this->context->loggedUser->id){
                    $offer->relisting = 1;
                    $offer->updateRelistingOnDB($this->db);
                    if (substr($this->context->siteOptions['cronTime'], 0, -1) != substr(time(), 0, -1)) {
                    	Cron::setCronWorking($this->db);
                        Cron::relistOffers($this->db, $this->context);
                    }
                    $this->context->message = _('Auction reactivated successfully');
Now a minor change is just needed in the cron job:

Find:

Code: Select all

    private static function setCronWorking($DB)
    {
        $sql = 'update '.SiteOptions::DB_TABLE.' set value="'.time().'" where name="cronTime"';
        $DB->query($sql);
    }
Change this to:

Code: Select all

    public static function setCronWorking($DB)
    {
        $sql = 'update '.SiteOptions::DB_TABLE.' set value="'.time().'" where name="cronTime"';
        $DB->query($sql);
    }

RWAP
Site Admin
Posts: 750
Joined: Fri Jan 08, 2010 2:23 am
Location: Stoke-on-Trent
Contact:

Re: SQL Improvements

Post by RWAP » Tue May 11, 2010 1:30 pm

A further improvement here is to class\Offer.php again:

Find:

Code: Select all

    static public function getListOnOneCategory(MyPDO $DB, $categoryId, $onlyActives = false)
    {
        return self::getListFromDB($DB,'where categoryId='.intval($categoryId).
                                    ( $onlyActives ? ' and offers.active=1 ' : '').
                                    'order by endDate');
    }
Add below this:

Code: Select all

    /**
    *   Updates the active state of an auction in the Database
    *   @param  MyPDO   $DB database instance
    *
    */
    public function activate(MyPDO $DB)
    {
        $query = $DB->prepare('update '.self::DB_TABLE.' set active=:active where id=:id');
        $return = $query->execute(array(':active'=>$this->active,':id'=>$this->id));
        unset($query);
        return $return;
    }

Then a couple of changes to use this rather than the whole of Offer::SavetoDB()

In class/Cron.php
Find:

Code: Select all

        	foreach($offers as $v){
            		$o = Offer::getInstanceFromDB($DB, $v['id']);
            		$o->active = 1;
            		$o->savetoDB($DB);
        	}
Change this to:

Code: Select all

        	foreach($offers as $v){
            		$o = Offer::getInstanceFromDB($DB, $v['id']);
            		$o->active = 1;
            		$o->activate($DB);
        	}

Then, in class\action\Offer.php
Find:

Code: Select all

        //display it if it's time to start
        if(strtotime($offer->startDate) <= time()){
            $offer->active = 1;
            $offer->savetoDB($this->db);
        }
Change this to:

Code: Select all

        //display it if it's time to start
        if(strtotime($offer->startDate) <= time()){
            $offer->active = 1;
            $offer->activate($this->db);
        }

RWAP
Site Admin
Posts: 750
Joined: Fri Jan 08, 2010 2:23 am
Location: Stoke-on-Trent
Contact:

Re: SQL Improvements

Post by RWAP » Wed Jun 09, 2010 2:02 pm

Again, a further improvement here is to class\Offer.php:

Find:

Code: Select all

    static public function getListOnOneCategory(MyPDO $DB, $categoryId, $onlyActives = false)
    {
        return self::getListFromDB($DB,'where categoryId='.intval($categoryId).
                                    ( $onlyActives ? ' and offers.active=1 ' : '').
                                    'order by endDate');
    }
Add below this:

Code: Select all

    /**
    *   Updates the draft state of an auction in the Database
    *   @param  MyPDO   $DB database instance
    *
    */
    public function setDraft(MyPDO $DB)
    {
        $query = $DB->prepare('update  '.self::DB_TABLE.' set draft=:draft where id=:id');
        $return = $query->execute(array(':draft'=>$this->draft,':id'=>$this->id));
        unset($query);
        return $return;
    }

Then a small change to use this rather than the whole of Offer::SavetoDB()

In class/Action/Offer.php
Find:

Code: Select all

                    }elseif($this->calculateFees($offer)){ //no draft and fees
                        //set it as draft while user doesn't pay fees
                        $offer->draft = 1;
                        $offer->saveToDB($this->db);
Change this to:

Code: Select all

                    }elseif($this->calculateFees($offer)){ //no draft and fees
                        //set it as draft while user doesn't pay fees
                        $offer->draft = 1;
                        $offer->setDraft($this->db);
Find:

Code: Select all

            //publish
            $offer->draft = 0;
            $offer->saveToDB($this->db);
Change this to:

Code: Select all

            //publish
            $offer->draft = 0;
            $offer->setDraft($this->db);
Find:

Code: Select all

                                if($par['draft']){
                                    $this->details();

                                }elseif($this->calculateFees($offer)){ //no draft and fees
                                    //set it as draft while user doesn't pay fees
                                    $offer->draft = 1;
                                    $offer->saveToDB($this->db);
Change this to:

Code: Select all

                                if($par['draft']){
                                    $this->details();

                                }elseif($this->calculateFees($offer)){ //no draft and fees
                                    //set it as draft while user doesn't pay fees
                                    $offer->draft = 1;
                                    $offer->setDraft($this->db);

RWAP
Site Admin
Posts: 750
Joined: Fri Jan 08, 2010 2:23 am
Location: Stoke-on-Trent
Contact:

Re: SQL Improvements

Post by RWAP » Tue Jun 15, 2010 7:36 pm

I would also make one minor improvement to class\StandardAuction.php

Find:

Code: Select all

        //case: buy now
        if($this->buyNowPrice && $b->value == $this->buyNowPrice){
            $b->offer = $this; //just in case
            $this->bids[0] = $b;
            $this->currentPrice = $b->value;
            $this->finishOffer();
            return true;
        }
Change this to:

Code: Select all

        //case: buy now
        if($this->buyNowPrice>0 && $b->value == $this->buyNowPrice){
            $b->offer = $this; //just in case
            $this->bids[0] = $b;
            $this->currentPrice = $b->value;
            $this->finishOffer();
            return true;
        }

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest