SilverStripe Forum

• Overview
• User help
• Tutorials
• Documentation
• Forums
• IRC
• Mailing Lists
• Showcase
• Bugs
• How to Contribute
• Donate
• Roadmap
• Search (new!)

Page: 1 , 2 , 3 , 4	go to end	Reply
Author	Topic: Failure in E-Commerce Module	58735 views

• RichardVowles
  
  Community Member
  5 posts
  

  Failure in E-Commerce Module

  4 December 2007 at 1:15pm

  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
  
  Community Member
  5 posts
  

  Re: Failure in E-Commerce Module

  4 December 2007 at 8:39pm

  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
  
  Community Member
  5 posts
  

  Re: Failure in E-Commerce Module

  4 December 2007 at 9:27pm

  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
  
  Community Member
  5 posts
  

  Re: Failure in E-Commerce Module

  4 December 2007 at 10:36pm

  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
  
  Core Development Team
  482 posts
  

  Re: Failure in E-Commerce Module

  5 December 2007 at 10:55am

  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
  
  Community Member
  32 posts
  

  Re: Failure in E-Commerce Module

  15 January 2008 at 8:28am

  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
  
  Community Member
  32 posts
  

  Re: Failure in E-Commerce Module

  15 January 2008 at 8:31am

  Where is Versioned::writeToStage() found?

• Sigurd
  
  Core Development Team
  546 posts
  2 SilverStripe Sites

  Re: Failure in E-Commerce Module

  16 January 2008 at 10:52am

  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.

		58735 views
	go to top	Reply Next >