Jump to:

17452 Posts in 4473 Topics by 1971 members

Archive

SilverStripe Forums » Archive » Failure in E-Commerce Module

Our old forums are still available as a read-only archive.

Moderators: martimiz, Sean, biapar, Willr, Ingo, simon_w

Page: 1 2 3 4
Go to End
Author Topic: 65460 Views
  • RichardVowles
    Avatar
    Community Member
    5 Posts

    Failure in E-Commerce Module Link to this post

    I am running the current version of both Silverstripe (2.2) and the E-Commerce module (0.5.1)? And using the PayPalPayment although I don't think this relates to that.

    There appears to be an error in the way the SQL is generated, notice the first SELECT is trying to request the data from Product_Live (which does exist) but it isn't listed in the FROM anywhere (not joined to).

    FATAL ERROR: DATABASE ERROR: Couldn't run query: SELECT `SiteTree_Live`.*, `Product_Live`.*, `SiteTree_Live`.ID, if(`SiteTree_Live`.ClassName,`SiteTree_Live`.ClassName,'SiteTree') AS RecordClassName, `SiteTree_Live_versions`.AuthorID, `SiteTree_Live_versions`.Version, `SiteTree_Live_versions`.RecordID AS ID FROM `SiteTree_Live` LEFT JOIN `Product_versions` ON `Product_versions`.RecordID = `SiteTree_Live_versions`.RecordID AND `Product_versions`.Version = `SiteTree_Live_versions`.Version WHERE (`SiteTree_Live`.RecordID = 7 AND `SiteTree_Live`.Version = 1) AND (`SiteTree_Live`.ClassName IN ('Product')) ORDER BY Sort | Unknown table 'Product_Live'
    At line 397 in C:\lighttpd\htdocs\sapphire\core\model\Database.php

    user_error(DATABASE ERROR: Couldn't run query: SELECT `SiteTree_Live`.*, `Product_Live`.*, `SiteTree_Live`.ID, if(`SiteTree_Live`.ClassName,`SiteTree_Live`.ClassName,'SiteTree') AS RecordClassName, `SiteTree_Live_versions`.AuthorID, `SiteTree_Live_versions`.Version, `SiteTree_Live_versions`.RecordID AS ID FROM `SiteTree_Live` LEFT JOIN `Product_versions` ON `Product_versions`.RecordID = `SiteTree_Live_versions`.RecordID AND `Product_versions`.Version = `SiteTree_Live_versions`.Version WHERE (`SiteTree_Live`.RecordID = 7 AND `SiteTree_Live`.Version = 1) AND (`SiteTree_Live`.ClassName IN ('Product')) ORDER BY Sort | Unknown table 'Product_Live',256)
    line 397 of Database.php

    Database->databaseError(Couldn't run query: SELECT `SiteTree_Live`.*, `Product_Live`.*, `SiteTree_Live`.ID, if(`SiteTree_Live`.ClassName,`SiteTree_Live`.ClassName,'SiteTree') AS RecordClassName, `SiteTree_Live_versions`.AuthorID, `SiteTree_Live_versions`.Version, `SiteTree_Live_versions`.RecordID AS ID FROM `SiteTree_Live` LEFT JOIN `Product_versions` ON `Product_versions`.RecordID = `SiteTree_Live_versions`.RecordID AND `Product_versions`.Version = `SiteTree_Live_versions`.Version WHERE (`SiteTree_Live`.RecordID = 7 AND `SiteTree_Live`.Version = 1) AND (`SiteTree_Live`.ClassName IN ('Product')) ORDER BY Sort | Unknown table 'Product_Live',256)
    line 106 of MySQLDatabase.php

    MySQLDatabase->query(SELECT `SiteTree_Live`.*, `Product_Live`.*, `SiteTree_Live`.ID, if(`SiteTree_Live`.ClassName,`SiteTree_Live`.ClassName,'SiteTree') AS RecordClassName, `SiteTree_Live_versions`.AuthorID, `SiteTree_Live_versions`.Version, `SiteTree_Live_versions`.RecordID AS ID FROM `SiteTree_Live` LEFT JOIN `Product_versions` ON `Product_versions`.RecordID = `SiteTree_Live_versions`.RecordID AND `Product_versions`.Version = `SiteTree_Live_versions`.Version WHERE (`SiteTree_Live`.RecordID = 7 AND `SiteTree_Live`.Version = 1) AND (`SiteTree_Live`.ClassName IN ('Product')) ORDER BY Sort,256)
    line 79 of DB.php

    ....

    SQLQuery->execute()
    line 642 of Versioned.php

    Versioned::get_version(Product,7,1)
    line 1005 of Order.php

    Order_Item->__construct(Array)
    line 1472 of DataObject.php

    DataObject->buildDataObjectSet(MySQLQuery,DataObjectSet,SQLQuery,Order_Item)
    line 1452 of DataObject.php

    DataObject->instance_get(OrderID = 11,,,,DataObjectSet,)
    line 1432 of DataObject.php

    DataObject::get(Order_Item,OrderID = 11)
    line 356 of Order.php

    Order->itemsFromDatabase()
    line 373 of Order.php

    Order->Items()

    call_user_func_array(Array,Array)
    line 524 of ViewableData.php

    ViewableData->cachedCall(Items,,)
    line 565 of ViewableData.php

    ViewableData->hasValue(Items)
    line 54 of .cacheC..lighttpd.htdocs.ecommerce.templates.Layout.CheckoutPage.ss

    include(C:\Documents and Settings\ddeveloper\Local Settings\Temp\silverstripe-cacheC--lighttpd-htdocs\.cacheC..lighttpd.htdocs.ecommerce.templates.Layout.CheckoutPage.ss)
    line 190 of SSViewer.php

    SSViewer->process(CheckoutPage_Controller)
    line 182 of SSViewer.php

    SSViewer->process(CheckoutPage_Controller)
    line 247 of Controller.php

    Controller->defaultAction(11,Array)
    line 218 of Controller.php

    Controller->run(Array)
    line 15 of ModelAsController.php

    ModelAsController->run(Array)
    line 76 of Director.php

    Director::direct(/checkout/11)
    line 104 of main.php

  • RichardVowles
    Avatar
    Community Member
    5 Posts

    Re: Failure in E-Commerce Module Link to this post

    Okay, this I have finally tracked down to Versioned.php, the following code being seriously broken because of earlier replacements:

    /**
        * Build a SQL query to get data from the _version table.
        * This function is similar in style to {@link DataObject::buildSQL}
        */
       function buildVersionSQL($filter = "", $sort = "") {
          $query = $this->owner->extendedSQL($filter,$sort);
          foreach($query->from as $table => $join) {
             if($join[0] == '`') $baseTable = str_replace('`','',$join);
             else $query->from[$table] = "LEFT JOIN `$table` ON `$table`.RecordID = `{$baseTable}_versions`.RecordID AND `$table`.Version = `{$baseTable}_versions`.Version";
             $query->renameTable($table, $table . '_versions');
          }
          $query->select[] = "`{$baseTable}_versions`.AuthorID, `{$baseTable}_versions`.Version, `{$baseTable}_versions`.RecordID AS ID";
          return $query;
       }

    1) The original code builds the query using SiteTree and Product as the table names.
    2) The _Live code then goes and finds the array (using Product) and replaces Product/SiteTree with Product_Live and SiteTree_Live.
    3) The code above then does another check, and sees that SiteTree uses a simple FROM element (where the name of the table starts with a quote) and assumes the name of the table is still SiteTree, replaces the FROM with SiteTree_Live_Versions!
    4) The code above sees that the Product $table in the from hash uses a join, so it replaces it with a Product_Versions, but never changes the select statement.

    So yeah - not sure how this code made it into the wild?

    Richard
    (using Delphi for PHP & the PHP debugger, and about 4 hours)

  • RichardVowles
    Avatar
    Community Member
    5 Posts

    Re: Failure in E-Commerce Module Link to this post

    And I'm gonna answer my own implicit question (how do you fix it). My proposal is this, in SQLQuery.php:

       function replaceText($old, $new) {
          if($this->select) foreach($this->select as $i => $item)
             $this->select[$i] = str_replace($old, $new, $item);

          if($this->from) {
    foreach($this->from as $i => $item) {
    if ( "`$i`" == $old )
    { // if this is the FROM related to the table we are replacing, then remove it from the array & replace with the new one
    unset( $this->from[$i] );
        $this->from[$new] = str_replace($old, $new, $item);
    }
    else
    $this->from[$i] = str_replace($old, $new, $item);
    }
    }

    But alas, my PHP is far too poor to actually have this work. But the intent is to make sure the From $table name in the associative array *always* reflect sthe actual name of the table in the query. There may be implications here I don't understand.

  • RichardVowles
    Avatar
    Community Member
    5 Posts

    Re: Failure in E-Commerce Module Link to this post

    No, that won't work either given the previous code (posting 2 expects to find the base table and add _versions to it given the name of the table in the associative array). Somehow the original table name & the changed table name need to both be stored...

  • Sam
    Avatar
    Administrator
    679 Posts

    Re: Failure in E-Commerce Module Link to this post

    Thanks for work investigating this, Richard. The crux of the issue is that, if buildVersionSQL is being used, Versioned's rewriting of the table name (from SiteTree to SiteTree_Live) needs to be disabled.

    The simplest solution is to temporarily clear Versioned::$reading_stage, before $this->owner->buildSQL() is called. That way, the '_Live' suffix won't get added.

    See Versioned::writeToStage() for an example of how this is done.

  • quicked
    Avatar
    Community Member
    32 Posts

    Re: Failure in E-Commerce Module Link to this post

    so that's it? No patches or fixes? So to use the ecommerce module you have to temporarily clear Versioned::$reading_stage, before $this->owner->buildSQL() is called?

  • quicked
    Avatar
    Community Member
    32 Posts

    Re: Failure in E-Commerce Module Link to this post

    Where is Versioned::writeToStage() found?

  • Sigurd
    Avatar
    Forum Moderator
    628 Posts

    Re: Failure in E-Commerce Module Link to this post

    Just to make it clear, the above error occurs when you are visiting the "checkout" page, where you wish to enter your name, payment info, etc.

    65460 Views
Page: 1 2 3 4
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.