Loading...
 
Skip to main content

Architecture / Installation

Forum List Topic List

 
Thread actions
Print this page
  • Print all pages

    • TikiWiki 2.0.Rx

    Posted by tasmaniac

    Me again.

    Dont know if this is bug or intentional so thought I'd come here first. I realize that tikiwiki2.0 is still not classed as stable etc but...

    In R1 and R2 all the db/tiki-2.0-.....sql files are the same (ie I use postgresql so would expect the tike-2.0-psql.sql file to reflect a postgresql file but it has mysql syntax (eg ENGINE=MYISAM)

    Problem 2 I created anew dbase to do a fresh clean install. Stick in the dbase information thru the web install script and it returns a Fatal Error Call to member function fetchRow() on a non-object in /var/www/tikiwiki-2r2/installer/tiki-installer.php on line 45
    Is this a case of a I've missed a step or is it a case of tikiwiki is only allowed to use MySQL now?

    Tasmaniac

    Reads: 1177
    Posted by tasmaniac

    OK this is getting worse.

    I managed to get tikiwiki2 dbase installed onto postgresql. But now it wont allow me to access the pages at all
    TikiWiki is unabled to connect to the database.

    The Following error was returned:

    ERROR: Syntax error at or near "`" at character 21.


    Yes the database is running. Yes the database login is correct . No I didnt complete the Installer as such because it wont allow me to go any further.

    I know the login credentials are correct because I added an echo in somewhere and it returns the correct user/passwd/database.

    Also on checking the postgres log the error is on
    select `login` from `users_users` limit 1

    So it looks to me that whatever the name of the function is that does the conversion from ` (backtick) to " (double quote) is not being called with the postgres driver.

    In the situation I want to use this in I dont have the option of installing MySQL

    So any help would be appreciated. (Also I am will to be the guinea pig for postgres tests)


    Peter J


    Posted by tasmaniac

    OK looks like I'm talking to myself as far as postgresql and tikiwiki goes.

    But this is what I have had to do so far to get tikiwiki2.0RC2 to run with postgres.

    (Please note I got no idea what I'm doing)

    Edit and fix up the db/tiki-pgsql.sql file. (to many changes to list)

    db/tikidb.php
    line 152 (in class tikiDB)
    added:-
    if ($db_tiki == "postgres7") {
    $dbconn_login = '"login"';
    $dbconn_table = '"users_users"';
    } else {
    $dbconn_login ='`login`';
    $dbconn_table ='`users_users"';
    }
    changed line (new) 160 :-
    .... -> Execute('Select ' . $dbconn_login . ' from ' . $dbconn_table . ' limit 1')))

    There is probably a better way but this allowed me to connect with either postgres or mysql (yes I installed mysql on a test box).

    lib/search/searchlib.php
    :- (into find_exact_trackers (485'ish))
    add $ADODB_LASTDB to the globals
    changed line 531 from s.`page` like concat(t.`itemId`, '#%')";
    to
    if ($ADODB_LASTDB == "postgres7") {
    $query .= "s.`page` like (t.`itemId` || '#%')";
    } else {
    $query .= "s.`page` like concat(t.`itemId`, '#%')";
    }
    (not sure if this works properly as yet but at least the search isnt erroring now)

    lib/tikilib.php
    line 447 inserted a couple of case statements to get rid of the varchar = integer errors
    case "postgres7" :
    $switch($type) {
    case "int" :
    return "$var::integer";
    break;
    case "string" :
    return "$var::char";
    break;
    }

    lib/search/refresh-functions.php
    line 32+
    on $f_id and $f_content had to add ` (backticks) around the values so postgres got the caps etc.

    line 165
    added
    $f_id = str_replace("`", "", $f_id);
    $f_content = str_replace("`", "", $f_content);

    this was to strip the previously applied ` after the dbase had finished with it.

    As i said I dont know much about php so I dont know if this will break anything else where. I am not in any way assiciated with tiki-wiki development(thankfully) I just thought I would add my 2cents worth seeing as no-one else seems to be able to help with postgresql/tikiwiki

    PeterJ

    (oh above so far I have only verified the connection works with both postgresql and mysql. Only basic check on the search part so far.)

    line 736 :- added p.`is_html` to the group by clause.


    Posted by tasmaniac

    Well after a lot of editing searching more editing I have managed to get tikiwiki r2 to work with postgres 8.3.

    to get around the non-existant FROM_UNIXTIME and TO_DAYS i added three functions to my postgresql.

    From the small test bed I've tried the 'changes' made havent stopped it from working with mysql. And as it is only going to run on an internal site I'm not to worried bout updates break it.

    The only things that did cause a big headache where the group by clauses ( as postgres expects everything to be in it or used in otherways in DISTINCT)

    Oh well I just thought I'd close this thread out even tho no one seems to have been around to comment.

    Peter J

    Posted by Gary Cunningham-Lee

    Thanks for posting your information and sorry there wasn't any response. I don't know if any of the current developers use anything but MySQL but someone might pick up the ball on this and when they do this will be helpful.

    -- Gary

    Posted by tasmaniac

    OK I know I said my last post was it etc but... 😊

    I decided to give RC 4 a go (glutton for punishment I know.)

    In the hope that either a the current developers will have time to add/test 'some' of the things on current release or b/ someone can point easier ways to do things I decided to post the current changes I had to make to RC 4. (I'm sure there are more but it is a start)

    tiki wiki R4

    db/tiki-db.php
    line 153 added - for some reason the convert ` to " doesnt get called here so it gets sent as `login`.
    if $($db_tike == "postgres7") {
    $dbconn_login = '"login" from "users_users"';
    } else {
    $dbconn_login = '`login` from `users_users`';
    }
    line 161 changed or (!@$dbtiki->Execute.... to ....->Execute('select ' .$dbconn_login . ' limit 1')))

    lib/tikilib.php
    line 510 changed - as realName to as `realName`
    line 3058
    changed — from (tiki_articles tiki_article_types) LEFT JOIN users_users on ....
    to — from tiki_article_types users_users INNER JOIN tiki_articles on ....
    Will this work on mysql or bigger dbases tho?
    function set_lastUpdate
    added global $ADODB_LASTDB;
    if ($ADODB_LASTDB == "postgres7") {
    $sqlcast = "::integer"; }
    then changed line 4437 to
    $query ="update `tiki_preferences` ........ value = value" . $sqlcast . "+1 .....

    lib/tikidblib.php
    line 447 function sql_cast
    added a case for postgres7
    case "postgres7" :
    switch ($type) {
    case "int" :
    return "$var::integer";
    break;
    case "string";
    return "$var::char";
    break;
    } // do I need a case for float??

    line 167 (replace_note) took noteId out of insert query. (noteId is autonumber so it isnt required(is it?))

    lib/commentslib.php
    line 951 had to add commentDate to select statement as it returned 'bind message supplies 3 parameters but prepared statement "" requires 2. (could I just delete the 3rd parameter? (int)$res'lastPostData''commentDate' ??)

    line 660 added a.archived to group by clause.
    line 1096 "and .... to " and........
    line 2088 fixed typo in threadid and put ` around same

    lib/faq/faqlib.php
    line 143 ` around max(questionId)

    lib/directory/dirlib.php
    line 411 ` around siteId

    lib/quizzes/quizlib.php
    line 184 added ` around quizId
    line 35100 added position to select (postgre requires the group by in the select)
    line 69120 added ` around answerUploadId

    lib/sheet/grid.php
    returns an adodb_perf error.
    Non static method adodb_perf::table() should not be called statically: tikiwiki/lib/adodb/adodb-perf.inc.php(70) (maybe I havent set the sheet up right?)

    line 1925 added ` around sheetId

    lib/newsletter/nllib.php
    line 63111 ` around isUser

    lib/search/searchlib.php
    line 483 (find_exact_trackers)
    added $ADODB_LASTDB to globals

    line 522 added
    if ($ADODB_LASTDB == "postgres7") {
    $concatsql = "(t.`itemId` || '#%')";
    } else {
    $concatsql = "concat(t.`itemId` '#%');
    }

    line 533 changed from s.`page` like concat(t.`itemId` '#%'); to s.`page` like " . $concatsql;
    line 737 added p.`is_html`

    lib/search/refresh-functions.php
    line 111 changed $f_id = 'pageName'; to $f_id = '`pageName`';
    line 112 added ` around pageName
    all f_id and $f_content vars in funtion refresh_index needed ` around the camel case names (will thi break something else tho?)

    lib/dcs/dcslib.php
    had to add ` around contentId in GROUP BY lines

    lib/freetag/freetaglib.php
    line 298 WHERE fto. etc etc had to add a GROUP BY clause and include all the columns from tiki_objects.

    line 247 had to change HAVING uniques = ?; to HAVING 'uniques' = ?;

    line 11751178117912631266 and heaps more had to add ` around tagId and objId objectId and others

    1173 (function cleanup_tags)
    added global $ADODB_LASTDB;
    if ($ADODB_LASTDB == "postgres7") {
    $sqlcast = "::integer"; }
    then line 1182 AND `tagId` NOT IN(SELECT `objId`" . $sqlcast . "........

    lib/calendar/tikicalanderlib.php
    line 98 (function get_object_cal_infos)
    added global $ADODB_LASTDB;
    if ($ADODB_LASTDB == "postgres7") {
    $sqlcast = "::integer"; }
    line 109 (case 'forum') after c.object (chat 279) added in ' . $sqlcast . '
    line 111 (case 'nl') added n.name to the end of GROUP BY clause

    lib/multilingual/multilinguallib.php
    line 123 (function getTranslations)
    added global $ADODB_LASTDB;
    if ($ADODB_LASTDB == "postgres7") {
    $sqlcast = "::char"; }

    then line 128 - .... and t1.`type` = ? .... changed to... and t1.`type`" . $sqlcast . "......

    /lib/structures/structlib.php
    global $ADODB_LASTDB;
    if ($ADODB_LASTDB == "postgres7";) {
    $sqlcast = "::integer"; }
    line 326 tuw.`object`" . $sqlcast . "......

    /lib/logs/loglib.php
    global $ADODB_LASTDB;
    if ($ADODB_LASTDB == "postgres7";) {
    $sqlcast = "::integer"; }
    line 851 uu.`userId` . $sqlcast . "......

    lib/wiki/wikilib.php
    line 727 fixed typo in query pagename should be pageName (char 232) also added p.userpcreator to group by clause

    lib/webmail/contactlib.php
    line 190 changed group by to c.`contactId`c.`lastName`c.`firstName`c.emailc.nicknamec.user
    banning system uses the wrong date format for postgres (need to think on a way around it for postgres/mysql)

    modules/mod-since_last_visit_new.php
    line 232 change lastModif> to `lastModif` >

    lib/Galaxia/src
    /GUI/GUI.php
    Heaps of backticks ` required around fields.
    line 110 if ($where) had to add
    $where = str_replace("." ".`" $where); // is there an easier way?
    $where = str_replace("=" "`=" $where); // this added ` around field name

    line 117 had to add ga.`flowNum` ga.`activityId` to select list so they could be used in group by

    line 133 had to add a GROUP BY ga.`flowNum` ga.`activityId`

    /ProcessManager/ActivityManager.php
    lots of ` around fields
    line 550 590 added ` around $key
    /ProcessManager/InstanceManager.php
    a few ` around fields
    line 177 193 added ` around $key
    /ProcessManager/RoleManager.php
    lots of ` around fields
    /ProcessManager/ProcessManager.php
    line 32 584 `` around fields

    /ProcessMonitor/ProcessMonitor.php
    a few ` around fields
    line 240 in the if($where) I added
    $where = str_replace("=" "`=" str_replace("." ".`" $where));
    One problem I can see with the above (maybe) what happens if it isnt = but < > etc? Anyone tell me a better way to do it?

    also had to add a couple of functions to postgres to handle TO_DAYS and FROM_UNIXTIME

    one thing I can think of to make things easier is there anyway to access sql_cast function from all the libs or to dangerous?

    PeterJ (hoping someone is reading it at least)

    Posted by tasmaniac

    Yep replying to my own posts yet again. Just some further notes (If anyone is interested)

    lib/tracker/trackerlib.php **new
    function_list_item

      • line 566 added $ADODB_LASTDB to global
      • line 698 inserted -

    if | ($csort_mode 'lastModif'
    $csort_mode = 'tti.`' . $csort_mode . '`';

      • line 709 added -

    if ($ADODB_LASTDB == 'postgres7') {
    $query = 'SELECT tti.`itemID`, max(tti.`trackeId`), max(tti.`created`), max(tti.`status`) as status, max(tti.`lastModif`), max(ttif.`value`),max(ttf.`type`), '
    .' max(' .($numsort)? "right(lpad($csort_mode,40,'0'),40)" : $csort_mode .') as `sortvalue`'
    . 'FROM ' . $base_tables.$sort_tables.$cat_table ;
    } else {
    $query = (the one that was here) }
    $query = $query
    .$mid
    . ' GROUP BY tti.`itemId`'
    etc

      • line 1948 added ` around a couple of fields


    Changed From previous post
    lib/freetag/freetaglib.php

        • changed this line 298 WHERE fto. etc etc had to add a GROUP BY clause and include all the columns from tiki_objects.
        • now reverted 298 back to the original ORDER BY without GROUP BY
        • line 295 added count(o.name) as name to $query_cant

    (seems to work in postgres and mysql)

        • line 247 had to change HAVING uniques = ?; to HAVING count('uniques') = ?;


    My problem is I only have a small dataset so cant be sure that everything works in a big data set.

    Also with the freetags how do you use them with trackers?

    Also dont know if its been report but you only seem to be able to search the contacts by email (or am I missing something again.) this is in mysql(original code base) and postgres.
    Even running the query directly in mysql doesnt return the expected result.

    Peter J