Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

All other Modules /

Discuss all other Modules here.

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

Sapphire SQL Syntax error when installing Subsites Module


Reply


3 Posts   1589 Views

Avatar
zalzadore

Community Member, 20 Posts

7 March 2010 at 4:10am

Hi,
With both the tar'd 0.1 and trunk versions of the Subsites module on Silverstripe 2.3.6 I get an SQL syntax error when I try to rebuild the database. The following is one of the errors from db/build?flush=1 (and any subsequent access to the silverstripe site):

[User Error] Couldn't run query: SELECT `SiteTree`.*, `GhostPage`.*, `ErrorPage`.*, `RedirectorPage`.*, `VirtualPage`.*, `SubsitesVirtualPage`.*, `SiteTree`.ID, if(`SiteTree`.ClassName,`SiteTree`.ClassName,'SiteTree') AS RecordClassName FROM `SiteTree` LEFT JOIN `GhostPage` ON `GhostPage`.ID = `SiteTree`.ID LEFT JOIN `ErrorPage` ON `ErrorPage`.ID = `SiteTree`.ID LEFT JOIN `RedirectorPage` ON `RedirectorPage`.ID = `SiteTree`.ID LEFT JOIN `VirtualPage` ON `VirtualPage`.ID = `SiteTree`.ID LEFT JOIN `SubsitesVirtualPage` ON `SubsitesVirtualPage`.ID = `SiteTree`.ID WHERE (HomepageForDomain REGEXP '(, ^) *localhost *(,|$)') AND ("SiteTree"."SubsiteID" IN (0)) ORDER BY Sort LIMIT 1 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '."SubsiteID" IN (0)) ORDER BY Sort LIMIT 1' at line 1
GET /bean/

Line 401 in /home/adam/public_html/bean/sapphire/core/model/MySQLDatabase.php

If I execute the query manually and remove the quotes from around "SiteTree"."SubsiteID" (i.e. make it simply SiteTree.SubsiteID) it executes without an error. Unfortunately I'm not familiar enough with Sapphire to take this any further.

Am I missing something obvious?

Avatar
zalzadore

Community Member, 20 Posts

7 March 2010 at 5:06am

Edited: 07/03/2010 5:10am

In having a squiz at the Subsites PHP code I found double quotes around column and table names causing the problem:

SiteConfigSubsites.php: $query->where[] = "\"$tableName\".\"SubsiteID\" IN ($subsiteID)";
SiteTreeSubsites.php: $query->where[] = "\"$tableName\".\"SubsiteID\" IN ($subsiteID)";
SiteTreeSubsites.php: $candidatePage = DataObject::get_one("SiteTree", "\"URLSegment\" = '" . urldecode( $rest). "' AND \"SubsiteID\" = " . $subsiteID, false);

Furthermore the code in the current Subsites module is liberally sprinkled with double quotes and, at least in my limited understanding of SQL syntax I think it's pretty broken (at least on MySQL 5.1 on Linux):

GroupSubsites.php: DB::query('INSERT INTO "Group_Subsites" ("GroupID", "SubsiteID") SELECT "ID", "SubsiteID" FROM "Group" WHERE "SubsiteID" > 0');

Wouldn't the above code just insert a number of rows with the strings 'ID' and 'SubsiteID' into the table Group_Subsites? (assuming a table with the name 'Group' was allowed)

Am I missing something?

Avatar
spronkey

Community Member, 1 Post

11 March 2010 at 12:36pm

Well, I'm getting the same errors as you, and to my eyes it looks pretty dodgy.

MySQL supports double quotes for object identifiers ONLY when ANSI_QUOTES mode is on.

I'm also not familiar with the internals, but surely this should be using some kind of configured constant for the correct database type...?