Jump to:

10929 Posts in 2613 Topics by 1809 members

All other Modules

SilverStripe Forums » All other Modules » Installing the postgresql module

Discuss all other Modules here.

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

Page: 1
Go to End
Author Topic: 2127 Views
  • argh
    Avatar
    Community Member
    4 Posts

    Installing the postgresql module Link to this post

    Hi,

    I have tried to apply what was suggested by StarCrescent and Geoff in http://www.silverstripe.org/installing-silverstripe/show/252952 but I didn't manage to make it work.

    Since I didn't understand what Geoff meant by "Run /dev/build on your project and all the tables should be created", I think I have missed something.

    Here follows the procedure I have applied (in vain):

    1. I have created SilverStripe-dedicated postgresql database and user.

    2. I have downloaded the PostGresql module postgresql-trunk-r69947 at http://www.silverstripe.org/postgresql-module/ and extracted the files into the silverstripe/postgresql-trunk-r69947 directory (ie along side cms, sapphire,...).

    3. I have downloaded install_pg.php and config-form-pg.html from http://open.silverstripe.com/ticket/3362 and installed them into the silverstripe directory.

    4. I have modified silverstripe/check-php.php, replacing header("Location: install.php"); with header("Location: install_pg.php");

    5. I have modifed silverstripe/postgresql-trunk-r69947/code/PostgreSQLDatabase.php, replacing the "pgsql_error" occurence (line 124) with "user_error".

    6. Using a browser I have connected to my silverstripe site and proceed with the Silverstripe/PostGresql installation.

    The installation has failed with an error on the following query (URLSegment doesn't exist - but I don't have investigated this one for the moment):
    SELECT "SiteTree".*, "GhostPage".*, "ErrorPage".*, "RedirectorPage".*, "VirtualPage".*, "SiteTree"."ID", CASE WHEN "SiteTree"."ClassName" IS NOT NULL THEN "SiteTree"."ClassName" ELSE 'SiteTree' END 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" WHERE (URLSegment = 'home') ORDER BY "Sort" LIMIT 1

    After refreshing, the installation has failed with an error on the following query (in fact this query is using a Mysql syntax not a Postgresql one):

    ALTER TABLE "PageComment" CHANGE "ClassName" "ClassName" varchar(255) not null default 'PageComment' check ("ClassName" in ('PageComment')), CHANGE "Created" "Created" timestamp, CHANGE "LastEdited" "LastEdited" timestamp, CHANGE "Name" "Name" varchar(200), CHANGE "Comment" "Comment" text, CHANGE "IsSpam" "IsSpam" boolean not null default false, CHANGE "NeedsModeration" "NeedsModeration" boolean not null default false, CHANGE "CommenterURL" "CommenterURL" varchar(255), CHANGE "ParentID" "ParentID" numeric(8) not null default 0, CHANGE "AuthorID" "AuthorID" numeric(8) not null default 0

    There is something wrong with the "alterTable" function from silverstripe/postgresql-trunk-r69947/code/PostgreSQLDatabase.php ;
    the alteredFields values are not compatible with postgresql.
    // snippet
    if($alteredFields) foreach($alteredFields as $k => $v) $alterList[] .= "CHANGE \"$k\" \"$k\" $v";

    Any idea ?

    Thank you in advance for your help

  • Geoff
    Avatar
    Core Development Team
    8 Posts

    Re: Installing the postgresql module Link to this post

    Hi,

    When you have a SilverStripe project, to rebuild the classes and associated database tables, you run yoursite.com/dev/build?flush=1 . It used to be called db/build, but recent releases have changed it to dev/build.

    Hope this helps,
    Geoff

  • Geoff
    Avatar
    Core Development Team
    8 Posts

    Re: Installing the postgresql module Link to this post

    For some reason, 'URLSegment' is not being escaped with quote marks like the rest of the query. If you can find the point at which this needs to be changed, let me know and I'll fix it.

    Also, the alter table function (and alter indexes function) need work. It's a known bug, and it's on the immediate to-do list.

    Geoff

  • argh
    Avatar
    Community Member
    4 Posts

    Re: Installing the postgresql module Link to this post

    Hi,

    Thank you Geoff for your answer. It is working, but I am still stuck because I need an admin account to be created (I have to dig this part).

    1. You are right concerning the URLSegment problem:

    Have a look to the "sapphire/core/model/Translatable.php" file (functions onBeforeWrite and alternateGetByUrl).

    In the function onBeforeWrite, I have replaced the line (maybe, it is not the best place to do so):

    $existingOriginalPage = Translatable::get_one_by_lang('SiteTree', Translatable::default_lang(), "URLSegment = '{$SQL_URLSegment}'");

    By

    $existingOriginalPage = Translatable::get_one_by_lang('SiteTree', Translatable::default_lang(), "\"URLSegment\" = '{$SQL_URLSegment}'");

    In the function alternateGetByUrl, I have replaced (maybe, it is not the best place to do so):

    $record = DataObject::get_one('SiteTree', "URLSegment = '{$SQL_URLSegment}'");

    By

    $record = DataObject::get_one('SiteTree', "\"URLSegment\" = '{$SQL_URLSegment}'");

    I guess that this problem may occur each time the get_one_by_lang and the get_one functions are called.

    2. After having made the corrections, I have re-run the installation and get about the same error:
    SELECT ID FROM SiteTree WHERE (OriginalID = 1)

    In the "sapphire/core/model/Translatable.php" have a look to the function findOriginalIDs:

    I have modified this function as follows:
    function findOriginalIDs() {
    if(!$this->isTranslation()) {
    $query = new SQLQuery("\"ID\"",
    "\"". ClassInfo::baseDataClass($this->owner->class) . "\"",
    array("\"OriginalID\" = ".$this->owner->ID)
    );
    $ret = $query->execute()->column();

    } else {
    return array();
    }
    }

    I guess that this problem may also occur each time the SQLQuery is called.

    3. After having modified the function findOriginalIDs, it seems that the column "OriginalID" is missing the table "SiteTree".

    4. Concerning the "alter table" problem, let me know if I can help.

    Argh

  • Geoff
    Avatar
    Core Development Team
    8 Posts

    Re: Installing the postgresql module Link to this post

    Thanks! I have added the URLSegment fix into trunk.

    If your version of SilverStripe is a few days old, then the OriginalID issue has probably been fixed by now.

    If you want to help on the alter table issue, please email me (geoff at silverstripe.com) and I'll let you know what the current state of things are.

    Geoff

    2127 Views
Page: 1
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.