4.2.2 on PostgreSQL

  • Jari Turkia

    Jari Turkia - 2009-12-06

    Hi All!

    It is simply impossible to install and use PhpGedView 4.2.2 with PostgreSQL back-end. There are issues with autocommit, invalid bindings and strict data typing on PDO.

    - The below patch disables autocommit. In PostgreSQL PDO this option simply cannot be used. Please consider how this could be implemented into PGV with more compatible manner.
    - In PostgreSQL it is impossible to bind anything else than parameter or select result. Binding a select column name cannot be done.
    - Data typing in PostgreSQL PDO is strict. It is impossible to use PHP float from PGV date-class in integer column. There is no implicit conversion.
    I don't know if this patch is complete, there may still be some issues that I haven't tested or found.

    Jari Turkia

        diff -aur PhpGedView-all-4.2.2.orig/includes/classes/class_date.php PhpGedView-all-4.2.2/includes/classes/class_date.php
        -- PhpGedView-all-4.2.2.orig/includes/classes/class_date.php 2009-08-01 08:21:48.000000000 +0300
        +++ PhpGedView-all-4.2.2/includes/classes/class_date.php 2009-12-06 22:44:35.000000000 +0200
        @@ -58,8 +58,8 @@
         function CalendarDate($date) {
         // Construct from an integer (a julian day number)
         if (is_numeric($date)) {
        - $this->minJD=$date;
        - $this->maxJD=$date;
        + $this->minJD=(int)$date;
        + $this->maxJD=(int)$date;
         list($this->y, $this->m, $this->d)=$this->JDtoYMD($date);
        @@ -68,12 +68,12 @@
         if (is_array($date)) {
         if (!is_null($this->MONTH_TO_NUM($date))) {
        - $this->m=$this->MONTH_TO_NUM($date);
        + $this->m=(int)$this->MONTH_TO_NUM($date);
         } else {
        - $this->y=$this->ExtractYear($date);
        + $this->y=(int)$this->ExtractYear($date);
        @@ -96,7 +96,7 @@
         $jd=$date->YMDtoJD($today, $date->m, $date->d==0?$today:$date->d);
         } else {
         // Complete date
        - $jd=floor(($date->maxJD+$date->minJD)/2);
        + $jd=(int)floor(($date->maxJD+$date->minJD)/2);
         list($this->y, $this->m, $this->d)=$this->JDtoYMD($jd);
         // New date has same precision as original date
        @@ -122,7 +122,7 @@
         $this->maxJD=$this->YMDtoJD($ny, $nm, 1)-1;
         } else {
         $this->minJD=$this->YMDtoJD($this->y, $this->m, $this->d);
        - $this->maxJD=$this->minJD;
        + $this->maxJD=(int)$this->minJD;
        @@ -537,7 +537,7 @@
        - return $d+floor((153*$m+2)/5)+365*$y+floor($y/4)-floor($y/100)+floor($y/400)-32045;
        + return (int)($d+floor((153*$m+2)/5)+365*$y+floor($y/4)-floor($y/100)+floor($y/400)-32045);
         static function JDtoYMD($j) {
        diff -aur PhpGedView-all-4.2.2.orig/includes/classes/class_pgv_db.php PhpGedView-all-4.2.2/includes/classes/class_pgv_db.php
        -- PhpGedView-all-4.2.2.orig/includes/classes/class_pgv_db.php 2009-08-01 08:21:40.000000000 +0300
        +++ PhpGedView-all-4.2.2/includes/classes/class_pgv_db.php 2009-12-06 22:58:13.000000000 +0200
        @@ -147,8 +147,7 @@
        - PDO::ATTR_AUTOCOMMIT=>true
         self::$AUTO_ID_TYPE ='SERIAL';
        diff -aur PhpGedView-all-4.2.2.orig/includes/functions/functions_db.php PhpGedView-all-4.2.2/includes/functions/functions_db.php
        -- PhpGedView-all-4.2.2.orig/includes/functions/functions_db.php 2009-08-01 08:21:22.000000000 +0300
        +++ PhpGedView-all-4.2.2/includes/functions/functions_db.php 2009-12-06 23:02:02.000000000 +0200
        @@ -699,19 +699,19 @@
        - "SELECT ?      AS type, COUNT(*) AS num FROM {$TBLPREFIX}individuals WHERE i_file=?".
        + "SELECT 'INDI'      AS type, COUNT(*) AS num FROM {$TBLPREFIX}individuals WHERE i_file=?".
         " UNION ALL ".
        - "SELECT ?      AS type, COUNT(*) AS num FROM {$TBLPREFIX}families    WHERE f_file=?".
        + "SELECT 'FAM'      AS type, COUNT(*) AS num FROM {$TBLPREFIX}families    WHERE f_file=?".
         " UNION ALL ".
        - "SELECT ?      AS type, COUNT(*) AS num FROM {$TBLPREFIX}other       WHERE o_file=?".
        + "SELECT 'NOTE'      AS type, COUNT(*) AS num FROM {$TBLPREFIX}other       WHERE o_file=?".
         " UNION ALL ".
        - "SELECT ?      AS type, COUNT(*) AS num FROM {$TBLPREFIX}sources     WHERE s_file=?".
        + "SELECT 'SOUR'      AS type, COUNT(*) AS num FROM {$TBLPREFIX}sources     WHERE s_file=?".
         " UNION ALL ".
        - "SELECT ?      AS type, COUNT(*) AS num FROM {$TBLPREFIX}media       WHERE m_gedfile=?".
        + "SELECT 'OBJE'      AS type, COUNT(*) AS num FROM {$TBLPREFIX}media       WHERE m_gedfile=?".
         " UNION ALL ".
         "SELECT o_type AS type, COUNT(*) as num FROM {$TBLPREFIX}other       WHERE o_file=? GROUP BY type"
        - ->execute(array('INDI', $ged_id, 'FAM', $ged_id, 'NOTE', $ged_id, 'SOUR', $ged_id, 'OBJE', $ged_id, $ged_id))
        + ->execute(array($ged_id, $ged_id, $ged_id, $ged_id, $ged_id, $ged_id))
        @@ -858,11 +858,11 @@
         if (is_null($statement)) {
        - "SELECT ? AS type, i_id AS xref, i_file AS ged_id, i_gedcom AS gedrec, i_isdead, i_sex ".
        + "SELECT 'INDI' AS type, i_id AS xref, i_file AS ged_id, i_gedcom AS gedrec, i_isdead, i_sex ".
         "FROM {$TBLPREFIX}individuals WHERE i_id=? AND i_file=?"
        - return $statement->execute(array('INDI', $xref, $ged_id))->fetchOneRow(PDO::FETCH_ASSOC);
        + return $statement->execute(array($xref, $ged_id))->fetchOneRow(PDO::FETCH_ASSOC);
         function fetch_family_record($xref, $ged_id) {
         global $TBLPREFIX;
        @@ -870,11 +870,11 @@
         if (is_null($statement)) {
        - "SELECT ? AS type, f_id AS xref, f_file AS ged_id, f_gedcom AS gedrec, f_husb, f_wife, f_chil, f_numchil ".
        + "SELECT 'FAM' AS type, f_id AS xref, f_file AS ged_id, f_gedcom AS gedrec, f_husb, f_wife, f_chil, f_numchil ".
         "FROM {$TBLPREFIX}families WHERE f_id=? AND f_file=?"
        - return $statement->execute(array('FAM', $xref, $ged_id))->fetchOneRow(PDO::FETCH_ASSOC);
        + return $statement->execute(array($xref, $ged_id))->fetchOneRow(PDO::FETCH_ASSOC);
         function fetch_source_record($xref, $ged_id) {
         global $TBLPREFIX;

  • Lester Caine

    Lester Caine - 2009-12-06

    It is also worth following the PDO development list. Currently there are a number of major blocks to any further development of PDO, and unless there is some agreement, then many of the database engines will not be fully supported by it. Posgresql is at least reasonably operation in PDO, but PDO2 will require some major work to allow even that to replace the generic drivers, and the MySQL developers are adding functions to their generic drivers, rather than aking new features available in PDO. There IS a question of the future of PDO at all!
    At the current time Firebird, Oracle and some of the other databases are simply not available in PDO, and the current holes in the design prevent adding them in a way that is fully compatible with MySQL and SQLite :(

  • Greg Roach

    Greg Roach - 2009-12-06

    Although we try to avoid vendor-specifc database code, none of the developers use PostgreSQL, so we don't test it.

    I've tried many times to install postgres, and never succeeded - so I got bored and gave up.

    My latest attempt gives the error "the service name is invalid".  Google suggests this is a common error, but nobody has a solution.

  • Mark Hattam

    Mark Hattam - 2009-12-07

    I'm trying (so far unsuccessfully) to get postgresql v8.4.1 working … at the moment I can't create a database because I don't exist, and I can't create a "role" or "user" because I don't exist. The postgresql web manual is sending me round in circles.
    I'll play a little longer.

  • Mark Hattam

    Mark Hattam - 2009-12-07

    An hour later … I've now got as far as PGV's install with a new config.php …

    without specifying a database port number I get

        Your current database configuration is bad. Please check your database connection parameters and configure again.
        SQLSTATE  could not translate host name "localhost" to address: nodename nor servname provided, or not known

    If I specify port 5432 which appears to be the port that PostgreSQL uses by default (goodness knows where you would change it), I get

        Your current database configuration is bad. Please check your database connection parameters and configure again.
        The auto-commit mode cannot be changed for this driver

    I've done a createdb from the shell as the postgres user … but haven't yet fathomed out phpPgAdmin's config.inc.php file … all phpPgAmin comes up with is

        Login disallowed for security reasons.
    when I try connecting as the username/password I've created.


  • Kevin G.

    Kevin G. - 2009-12-07

    I am getting the same messages as dxradio (Mark, above) now that I have moved to 4.2.2 from 4.2.1 pre-release.  The problem in my case not due to lack of knowledge about PostgreSQL, which is running just fine for my other applications that use it.

    The error messages are not very helpful in this case, and I have just about exhausted myself in trying to get PostgreSQL to work with PGV 4.2.2, but I can help you, Mark, in understanding some of the quirks in using PostgreSQL  if it helps us all make progress in raising PostgreSQL to the first-tier among supported databases.

    I think that better error-logging and error messages would be a great first step in understanding what is wrong.  Can someone on the PGV core team help by improving general error reporting?


  • Lester Caine

    Lester Caine - 2009-12-07

    Kevin - the error messages are an area of PDO that still needs major work. It is not something that the PGV developers have much control over. This is something that needs to be fixed at the PDO level. Since PDO is written in C it requires a much more in depth understanding of the core development of PHP, rather than just making changes in the PGV level of code. Personally I work with Firebird and the pdo_firebird driver needs even more work to allow it any chance of working with the current builds of PGV. Postgresql has a better chance of being made to work, and will need changes to the SQL in order to which is the PGV level, but you will also have to put up with the problems in PDO to get it working.

    http://lsces.co.uk/wiki/index.php?page=Port+of+phpgedview+back+to+ADOdb&highlight=phpgedview  has PGV working on Firebird, and has a number of changes to the SQL to support that, but I've not re-written the installer, so you have to have a blank database. This port should work with any of the ADOdb databases
    and may be useful to identify all the files you need to check to get Postgresql working.

  • Greg Roach

    Greg Roach - 2009-12-07

    Mark - I used the "PostGresPlus" installer, and used it to install the "Database Server" option.  Is this what you did?

  • Mark Hattam

    Mark Hattam - 2009-12-07

    Nope, I used the postgresql84-server port on Macports which built it from the source. Currently I see

        ps -ef | grep postgre
            0    49     1   0   0:00.04 ??         0:00.06 /opt/local/bin/daemondo -label=postgresql84-server -start-cmd /opt/local/etc/LaunchDaemons/org.macports.postgresql84-server/postgresql84-server.wrapper start ; -stop-cmd /opt/local/etc/LaunchDaemons/org.macports.postgresql84-server/postgresql84-server.wrapper stop ; -restart-cmd /opt/local/etc/LaunchDaemons/org.macports.postgresql84-server/postgresql84-server.wrapper restart ; -pid=none
          506   239     1   0   0:00.48 ??         0:00.58 /opt/local/lib/postgresql84/bin/postgres -D /opt/local/var/db/postgresql84/defaultdb
          506   242   239   0   0:01.16 ??         0:01.85 postgres: writer process                                                                                                                                                                                                                                                                                                                  
          506   243   239   0   0:01.07 ??         0:01.34 postgres: wal writer process                                                                                                                                                                                                                                                                                                              
          506   244   239   0   0:00.49 ??         0:00.69 postgres: autovacuum launcher process                                                                                                                                                                                                                                                                                                     
          506   245   239   0   0:01.18 ??         0:01.31 postgres: stats collector process      

    which is after a few restarts.

    I can su to the postgres user, I can create a db using createdb … but can't see how to get a postgres command line to actually shove some SQL in its direction, like create table, insert, select … somewhere there must be a "show databases" so you know what you've created. Even Oracle at work is easier than this.

  • Lester Caine

    Lester Caine - 2009-12-07

    dxradio - psql should give you an interactive window on your database. The manual for postgresql is pretty good at getting you to the right place, http://www.postgresql.org/docs/8.4/static/tutorial-accessdb.html for instance … You just need to know the name of the database you created.

    Most of the time we are just treating the database engine as a service, so your applications talk to it, but the command line tools can become a little messed up. psql is fairly safe, isql is used by a number of engines and invariably the wrong one is loaded, so I prefer a graphic admin console - but I'm not sure what is available for postgresql.

  • Greg Roach

    Greg Roach - 2009-12-07

    Mark - I should have remembered that you use macos.   I'm trying to install it on vista.

    Googling "postgresql install vista" just gives lots of reports of people failing to install it.

    wiki.postgresql.org doesn't offer any suggestions either.  Unfortuante most of the search hits for "vista" take me to the spanish pages.  Vista is presumably spanish for a  "database view" :-)

    While I would dearly love to fix the compatibility issues in PGV, I'm afraid that unless I can install PG, it isn't going to happen.

  • Mark Hattam

    Mark Hattam - 2009-12-07

    OK .. psql gives me a command line into postgresql … managed to insert and select now

        postgres=# create database pgv\g
        postgres=# \c pgv
        psql (8.4.1)
        You are now connected to database "pgv".
        pgv=# create table testtable (animal text,number integer)\g
        pgv=# insert into testtable (animal,number) values ('cat',3)\g
        INSERT 0 1
        pgv=# insert into testtable (animal,number) values ('dog',2)\g
        INSERT 0 1
        pgv=# select * from testtable\g
         animal | number
         cat    |      3
         dog    |      2
        (2 rows)

    No further forward though with getting phpGedView to "install" though. Same errors. More Googling and looking at http://php.net/manual/en/function.pg-connect.php and http://www.svn8.com/phpabc/ref.pdo.html seems to suggest that

        The auto-commit mode cannot be changed for this driver
    is a generic connection error. Is postgre in transactional mode or non-transactional? My simple inserts etc seemed to go through directly.

    Is there a useful log that can be enabled in postgre?

  • Greg Roach

    Greg Roach - 2009-12-07

    Well, I've tried uninstalling and re-installing a few times - but it doesn't help.  I still get the error "The service name is invalid" when I try to start the server.

    I've also looked in the postgres mailing list archives.

    This would appear to be a common error, and while the problem is reported many times, there is no solution.

    Does anyone know if Postgres actually runs on Vista - or am I wasting my time by trying?

  • Greg Roach

    Greg Roach - 2009-12-23

    Thanks to utopiabound for helping me to get Postgres running on my server.

    Using the latest SVN code, I can now configure, install, load, report, query, edit, etc.

    There are still some outstanding issues with some of the statistics queries (which I'll look at next).  These should be easy to fix.

    If there are any postgres users who can download and test the latest code, it would be very much appreciated.  The 4.2.3 release will be coming out in a few days, and if there are any other problems, it would be good to fix them before the release.

  • Mark Hattam

    Mark Hattam - 2009-12-24

    Postgres log on its way to you, Greg.

    There are some syntax errors reported after creating the tables in a new db.
    Also some apparent line "over length" errors, for records which I don't think are overlong.

    But it does seem to work well apart from my six invalid records. Strangely the given names of the invalid six are all rather similar.

    I'm running Postgres on MacOSX 10.6.2 using a MacPorts based installation (which I hadn't touched since the above entries on 7th Dec).

        postgresql84 @8.4.2_0 (active)
        postgresql84-server @8.4.2_0 (active)



Log in to post a comment.