search

liqiusheng / silverstripe-ecommerce

Automatically exported from code.google.com/p/silverstripe-ecommerce
0 stars 0 forks source link

Syntax Error on dev/build with PostgreSQL #45

Closed GoogleCodeExporter closed 8 years ago

GoogleCodeExporter commented 8 years ago 
Specific error is caused by: 
DB::query(SHOW COLUMNS FROM `Order` LIKE 'Shipping')
Line 742 of Order.php

Modules:
ecommerce trunk
payment 0.3
silverstripe 2.4.0
postgresql 0.9.1

System:
Ubuntu 10.04 with Postgres 8.4.3.

I understand that you guys probably use MySQL, but I picked the SilverStripe 
CMS specifically because of it's excellent support for PostGreSQL.

It seems a pity that this module will be awesome (!!!), but I can't use it yet.

Here's the equivalent for Postgres:

SELECT column_name FROM information_schema.columns WHERE table_name ='Order' 
AND column_name = 'Shipping';

I'm new to the code (silverstripe AND php!) so I can't patch, but I can assist 
with any postgres specific SQL queries.

Original issue reported on code.google.com by tculs...@gmail.com on 18 Sep 2010 at 1:38

GoogleCodeExporter commented 8 years ago 
I have got the build to quickly work by just changing the line for postgres, 
but now of course MySQL wouldn't work.

I also had to change WHERE clauses and ORDER BY clauses where they also had 
errors. Specifically Postgres doesn't accept the ` character, it has to be a ". 
This means some escapes are necessary in php.

I could carry on, but I suspect there will be many other cases like this, and 
I'd like to make it SQL generic rather than specific to Postgresql or MySQL.

Original comment by tculs...@gmail.com on 18 Sep 2010 at 2:04

GoogleCodeExporter commented 8 years ago 
See http://doc.silverstripe.org/database-abstraction for a few hints;)

Original comment by tculs...@gmail.com on 18 Sep 2010 at 2:17

GoogleCodeExporter commented 8 years ago 
Well here's the first one....

 if(defined('DB::USE_ANSI_SQL')) {
                        $exist = DB::query("SELECT column_name FROM information_schema.columns WHERE table_name ='Order' AND column_name = 'Shipping'")->numRecords();
                }
                else {
                        $exist = DB::query("SHOW COLUMNS FROM `Order` LIKE 'Shipping'")->numRecords();
                }

Original comment by tculs...@gmail.com on 18 Sep 2010 at 3:26

GoogleCodeExporter commented 8 years ago 
VERY bad code also added to postgresql/code/PostgreSQLDatabase.php:

line 174: $handle = pg_query($this->dbConn, str_replace('`', '"', $sql));

The original queries should be fixed and then this line can be removed.

Original comment by tculs...@gmail.com on 18 Sep 2010 at 3:47

GoogleCodeExporter commented 8 years ago 
But now I go into the CMS and click on "Example Product" and get an aggregator 
problem (known because MySQL is slack on GROUP BY requirements), so there are a 
few deeper problems (all fixable!)....

ERROR [Warning]: pg_query(): Query failed: ERROR:  column 
"Product_ProductGroups.ProductID" must appear in the GROUP BY clause or be used 
in an aggregate function
IN POST /admin/getitem?ID=9&ajax=1
Line 174 in /var/www/tonyculshaw.com/postgresql/code/PostgreSQLDatabase.php

.......

Trace
=====
<ul>pg_query(Resource id #85,SELECT "SiteTree"."ClassName", 
"SiteTree"."Created", "SiteTree"."LastEdited", "SiteTree"."URLSegment", 
"SiteTree"."Title", "SiteTree"."MenuTitle", "SiteTree"."Content", 
"SiteTree"."MetaTitle", "SiteTree"."MetaDescription", 
"SiteTree"."MetaKeywords", "SiteTree"."ExtraMeta", "SiteTree"."ShowInMenus", 
"SiteTree"."ShowInSearch", "SiteTree"."HomepageForDomain", 
"SiteTree"."ProvideComments", "SiteTree"."Sort", "SiteTree"."HasBrokenFile", 
"SiteTree"."HasBrokenLink", "SiteTree"."Status", "SiteTree"."ReportClass", 
"SiteTree"."CanViewType", "SiteTree"."CanEditType", "SiteTree"."ToDo", 
"SiteTree"."Version", "SiteTree"."Priority", "SiteTree"."ParentID", 
"ProductGroup"."ChildGroupsPermission", "SiteTree"."ID", CASE WHEN 
"SiteTree"."ClassName" IS NOT NULL THEN "SiteTree"."ClassName" ELSE 'SiteTree' 
END AS "RecordClassName", CASE WHEN "ProductID" IS NULL THEN '0' ELSE '1' END 
AS Checked FROM "SiteTree" LEFT JOIN "ProductGroup" ON "ProductGroup"."ID" = 
"SiteTree"."ID"  LEFT JOIN "Product_ProductGroups" ON ("SiteTree"."ID" = 
"Product_ProductGroups"."ProductGroupID" AND "ProductID" = '9') WHERE 
("SiteTree"."ClassName" IN ('ProductGroup')) GROUP BY "SiteTree"."ClassName", 
"SiteTree"."Created", "SiteTree"."LastEdited", "SiteTree"."URLSegment", 
"SiteTree"."Title", "SiteTree"."MenuTitle", "SiteTree"."Content", 
"SiteTree"."MetaTitle", "SiteTree"."MetaDescription", 
"SiteTree"."MetaKeywords", "SiteTree"."ExtraMeta", "SiteTree"."ShowInMenus", 
"SiteTree"."ShowInSearch", "SiteTree"."HomepageForDomain", 
"SiteTree"."ProvideComments", "SiteTree"."Sort", "SiteTree"."HasBrokenFile", 
"SiteTree"."HasBrokenLink", "SiteTree"."Status", "SiteTree"."ReportClass", 
"SiteTree"."CanViewType", "SiteTree"."CanEditType", "SiteTree"."ToDo", 
"SiteTree"."Version", "SiteTree"."Priority", "SiteTree"."ParentID", 
"ProductGroup"."ChildGroupsPermission", "SiteTree"."ID", CASE WHEN 
"SiteTree"."ClassName" IS NOT NULL THEN "SiteTree"."ClassName" ELSE 'SiteTree' 
END ORDER BY "Sort" LIMIT 30)
line 174 of PostgreSQLDatabase.php

Original comment by tculs...@gmail.com on 18 Sep 2010 at 3:50

GoogleCodeExporter commented 8 years ago 
I think that's enough for one day!

Original comment by tculs...@gmail.com on 18 Sep 2010 at 3:52

GoogleCodeExporter commented 8 years ago 
Tony, just let us know what the status is on this one.  Probably fixed?

Original comment by nfranc...@gmail.com on 29 Nov 2010 at 4:56

GoogleCodeExporter commented 8 years ago 
Yes, this one is fixed.

Original comment by tculs...@gmail.com on 29 Nov 2010 at 7:13