Firefox PHP

PostgreSQL preview available

Posted by Dan Langille 
PostgreSQL preview available
January 02, 2006 11:44AM
Gidday folks,

Last night I happened to talk to Brian, and so I started on the PostgreSQL port of 5.1.5-beta.

I know these things work:

creating a new Phorum installation (tables, etc)
creating/deleting a new forum
new users
new posts
replies
personal messages

I know there is still work to do. Specifically, mysql_unbuffered_query() has not been dealt with. And full text search is not implemented. I won't be getting back to this stuff for a few days at least. So you folks can play with my development site at [bast.unixathome.org]

--
Phorum Developer - PostgreSQL on FreeBSD
[www.langille.org]



Edited 1 time(s). Last edit at 01/02/2006 03:41PM by brianlmoon.
Re: PostgreSQL preview available
January 02, 2006 12:35PM
A few notes on the choice of data types when converting from MySQL to PostgreSQL:

All auto_increment fields now use serial8

Fields such as these:

user_newflags_table.group_id
groups_table.group_id
forum_group_xref_table.forum_id
user_group_xref_table.user_id
files_table.file_id
banlist_table.id

were previously int(11) and are now bigint.

mediumtext is now text

tinyint(whatever size) is now smallint.

I can't think of any other changes.

later.

--
Phorum Developer - PostgreSQL on FreeBSD
[www.langille.org]
Re: PostgreSQL preview available
January 02, 2006 07:30PM
If you tried earlier, and couldn't get in, please try again. I've fixed the port 8080 issue. All links should work now.

--
Phorum Developer - PostgreSQL on FreeBSD
[www.langille.org]
Changes from the MySQL version
January 02, 2006 07:35PM
I noticed that phorum_messages.closed is defined as an integer. However, in PHP it is stored as a boolean. I changed the code for PostgreSQL as follows:

column definition: closed boolean NOT NULL default false

phorum_db_post_message(): " . ($message['closed'] ? 'TRUE' : 'FALSE');

NOTE: the above change is out of context, see my next post titled: Columns/Values for an explanation.

phorum_db_update_message: Added an is_bool to the loop:
        foreach($message as $field => $value){
            if (is_numeric($value) && !in_array($field,$PHORUM['string_fields'])){
                $fields[] = "$field=$value";
            } elseif (is_array($value)){
                $fields[] = "$field='".pg_escape_string(serialize($value))."'";
            } elseif (is_bool($value)) {
                $fields[] = "$field=" . ($value ? 'TRUE' : 'FALSE');
            } else {
                $value = pg_escape_string($value);
                $fields[] = "$field='$value'";
            }
        }
Those brackets around the expression are required because the . operator is a higher precedence.


thomas: added code tags for readability.

--
Phorum Developer - PostgreSQL on FreeBSD
[www.langille.org]



Edited 1 time(s). Last edit at 01/02/2006 07:38PM by ts77.
Columns/Values
January 02, 2006 07:38PM
The MySQL syntax for inserting a row allows you do to this:

Insert into $table set a=1, b=2, etc. Here's the original:
    $sql = "Insert into $table set
            forum_id = {$message['forum_id']},
            datestamp=$NOW,
            thread={$message['thread']},
            parent_id={$message['parent_id']},
            author='{$message['author']}',
            subject='{$message['subject']}',
            email='{$message['email']}',
            ip='{$message['ip']}',
            user_id={$message['user_id']},
            moderator_post={$message['moderator_post']},
            status={$message['status']},
            sort={$message['sort']},
            msgid='{$message['msgid']}',
            body='{$message['body']}',
            closed={$message['closed']}
            $metaval";

    // if in conversion we need the message-id too
    if($convert && isset($message['message_id'])) {
        $sql.=",message_id=".$message['message_id'];
    }

    if(isset($message['modifystamp'])) {
        $sql.=",modifystamp=".$message['modifystamp'];
    }

    if(isset($message['viewcount'])) {
        $sql.=",viewcount=".$message['viewcount'];
    }


    $res = mysql_query($sql, $conn);
That's far from standard SQL. I've changed the PostgreSQL code to do something like this:

    $columns = 'forum_id, datestamp, thread, parent_id, author, subject, email, ip, user_id, moderator_post, status, sort, msgid, body, closed';
    $values  = "{$message['forum_id']},
            $NOW,
            {$message['thread']},
            {$message['parent_id']},
            '{$message['author']}',
            '{$message['subject']}',
            '{$message['email']}',
            '{$message['ip']}',
            {$message['user_id']},
            {$message['moderator_post']},
            {$message['status']},
            {$message['sort']},
            '{$message['msgid']}',
            '{$message['body']}',
            " . ($message['closed'] ? 'TRUE' : 'FALSE');

    if (isset($message['meta'])){
        $columns .= ', meta';
        $values  .= ", '" . $message['meta'] . "'";
    }

    // if in conversion we need the message-id too
    if ($convert && isset($message['message_id'])) {
        $columns .= ', message_id';
        $values  .= ', ' . $message['message_id'];
    }

    if (isset($message['modifystamp'])) {
        $columns .= ', modifystamp';
        $values  .= ', ' . $message['modifystamp'];
    }

    if (isset($message['viewcount'])) {
        $columns .= ', viewcount';
        $values  .= ', ' . $message['viewcount'];
    }

    $sql = "INSERT INTO $table ($columns) values ($values)";

    $res = pg_query($conn, $sql);

--
Phorum Developer - PostgreSQL on FreeBSD
[www.langille.org]



Edited 1 time(s). Last edit at 01/03/2006 12:42AM by DanLangille.
phorum_db_maxpacketsize
January 03, 2006 12:26AM
The function phorum_db_maxpacketsize() appears to be MySQL specific. There doesn't seem to be any way around calling. So I started doing things like this in posting.php:

    $php_limit = ini_get('upload_max_filesize') * 1024;

    #
    # MySQL has a max packet size issue.  PostgreSQL does not.
    #
    if ($PHORUM['DBCONFIG']['type'] == 'mysql') {
        $db_limit = phorum_db_maxpacketsize() / 1024 * 0.6;
    } else {
        $db_limit = $php_limit;
    }


Similar changes here made to:

include/admin/sanity_checks/upload_limits.php
include/admin/newforum.php
include/posting/action_attachments.php

--
Phorum Developer - PostgreSQL on FreeBSD
[www.langille.org]



Edited 1 time(s). Last edit at 01/03/2006 12:43AM by DanLangille.
Re: PostgreSQL preview available
January 03, 2006 05:22AM
This is not the way I'd like to see it implemented. The idea of a db layer is to have abstraction. Now there's knowledge again in the posting.php script.

Please do implement a phorum_db_maxpacketsize() function instead. Let it return NULL for postgresql, to signal the caller that there no usable value. I will patch the code to accept the NULL value.


Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
Re: PostgreSQL preview available
January 03, 2006 07:42AM
Quote
mmakaay
Please do implement a phorum_db_maxpacketsize() function instead. Let it return NULL for postgresql, to signal the caller that there no usable value. I will patch the code to accept the NULL value.

OK, when I see the changes to the code, I'll update mine and implement phorum_db_maxpacketsize()
Re: PostgreSQL preview available
January 03, 2006 08:00AM
I committed some changes to the svn tree in changeset 722, which make NULL a valid return value for phorum_db_maxpacketsize(). As you can see, you have to change your code at more than one place.


Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
Re: phorum_db_maxpacketsize
January 03, 2006 11:55AM
Quote
DanLangille
if ($PHORUM['DBCONFIG']['type'] == 'mysql') {

That was the Phorum 3 way, but I want to never see that in Phorum 5. If PG does not support some query to get this value, it should just return some arbitrary number or something. I can't imagine that PG does not have something like max packet size though.
Re: PostgreSQL preview available
January 11, 2006 11:19PM
OK! We have search working!

On the flight from Montreal to Washington DC, I had time to convert the search functions to use PostgreSQL. The fixes are still sitting on my laptop. I hope to commit them sometime over the next couple of days.

:)

--
Phorum Developer - PostgreSQL on FreeBSD
[www.langille.org]
Re: PostgreSQL preview available
January 17, 2006 03:12PM
When creating new folders for PM SQL error occurs, because pgsql does not support SET option for INSERT:

1137527940: ERROR: syntax error at or near "SET" at character 31: INSERT INTO p
horum_pm_folders SET user_id=3, foldername='novamapa'

in phorum_db_pm_create_folder:

/**
* This function creates a new folder for a user.
* @param foldername - The name of the folder to create.
* @param user_id - The user to create the folder for or
* NULL to use the current user (default).
*/
function phorum_db_pm_create_folder($foldername, $user_id = NULL)
{
$PHORUM = $GLOBALS["PHORUM"];

$conn = phorum_db_postgresql_connect();

if ($user_id == NULL) $user_id = $PHORUM['user']['user_id'];
settype($user_id, "int");

/** Mysql
$sql = "INSERT INTO {$PHORUM['pm_folders_table']} SET " .
"user_id=$user_id, " .
"foldername='".pg_escape_string($foldername)."'";
**/

$sql = "INSERT INTO {$PHORUM['pm_folders_table']} " .
"(user_id,foldername) " .
VALUES ($user_id,"'" . pg_escape_string($foldername). "')";

$res = pg_query($conn, $sql);
if ($err = pg_last_error()) phorum_db_pg_last_error("$err: $sql");
return $res;
}
Re: PostgreSQL preview available
February 20, 2006 08:14PM
Judging by the latest cvs snapshot, this conversion of the insert/set syntax needs to be done to phorum_db_pm_buddy_add as well:

$sql = "INSERT INTO {$PHORUM["pm_buddies_table"]} (user_id, buddy_user_id) values ( " .
$user_id.", " .
$buddy_user_id.")";


Would wrapping phorum_db_delete_message in a transaction work well to replace the commented lock tables? If that's not enough, PostgreSQL does support exclusive table locking within transactions.
Re: PostgreSQL preview available
February 21, 2006 12:25AM
Quote
ftzdomino
Would wrapping phorum_db_delete_message in a transaction work well to replace the commented lock tables? If that's not enough, PostgreSQL does support exclusive table locking within transactions.

The lock has been removed from the mysql layer. It was handled another way by first disabling the message before the delete operation starts.

Brian - Cowboy Ninja Coder - Personal Blog - Twitter
Re: PostgreSQL preview available
February 21, 2006 12:47AM
Quote
ftzdomino
Judging by the latest cvs snapshot, this conversion of the insert/set syntax needs to be done to phorum_db_pm_buddy_add as well:

$sql = "INSERT INTO {$PHORUM["pm_buddies_table"]} (user_id, buddy_user_id) values ( " .
$user_id.", " .
$buddy_user_id.")";

I agree. Thanks. I'll fix that up.

--
Phorum Developer - PostgreSQL on FreeBSD
[www.langille.org]
Re: PostgreSQL preview available
February 26, 2006 10:01AM
function phorum_db_newflag_add_read needs VALUES set adjustment too:

if($cnt) {
$insert_sql="INSERT INTO ".$PHORUM['user_newflags_table']." (user_id,forum_id,message_id) VALUES".join(",",$values);

// fire away
$conn = phorum_db_postgresql_connect();
$res = pg_query($conn, $insert_sql);

if ($err = pg_last_error()) phorum_db_pg_last_error("$err: $insert_sql");
}

needs to be converted to series of INSERTS like:

if($cnt) {
$conn = phorum_db_postgresql_connect();
foreach ($values as $val) {
$insert_sql = "INSERT INTO {$PHORUM['user_newflags_table']} (user_id,forum_id,message_id) VALUES $val";
$res = pg_query($conn, $insert_sql);
if ($err = pg_last_error()) phorum_db_pg_last_error("$err: $insert_sql");
}
}

(taken from Ales Podboj)
Re: PostgreSQL preview available
March 20, 2006 08:18PM
Two more details to correct:

1.) removing (commenting) 'mysql_use_ft' configuration setting: if ( $PHORUM['DBCONFIG']['mysql_use_ft'] )
because PostgreSQL doesn't have MATCH-AGAINST:

--------- lines 943 - 953, 967 ('}'): ---------

if($PHORUM["DBCONFIG"]["mysql_use_ft"]){

if($match_type=="ALL" && count($terms)>1){
$against="+".pg_escape_string(implode(" +", $terms));
} else {
$against=pg_escape_string(implode(" ", $terms));
}

$clause="MATCH (search_text) AGAINST ('$against' IN BOOLEAN MODE)";

} else {

----------------------------

2.) In function phorum_db_sanitychecks()
inside return texts word 'MySQL'
should be replaced with 'PostgreSQL'.



Edited 3 time(s). Last edit at 03/20/2006 08:32PM by Ales.
Re: PostgreSQL preview available
January 11, 2007 11:36PM
Now I really wish I'd check cvs before starting this very thing today. <sigh>
Re: PostgreSQL preview available
January 11, 2007 11:38PM
We need more PG folks. DVL has done a pretty good job, but the more eyeballs the better.

Brian - Cowboy Ninja Coder - Personal Blog - Twitter
Re: PostgreSQL preview available
January 11, 2007 11:48PM
If you have any questions, ask away.

--
Phorum Developer - PostgreSQL on FreeBSD
[www.langille.org]
Re: PostgreSQL preview available
January 11, 2007 11:49PM
What are you doing about mysql's full text search?
Re: PostgreSQL preview available
January 12, 2007 12:05AM
Me? Nothing. PostgreSQL has pretty good text search, even without full text. You can it it out at [www.freshports.org]

--
Phorum Developer - PostgreSQL on FreeBSD
[www.langille.org]
Re: PostgreSQL preview available
January 12, 2007 10:24PM
Here's a problem:

Could not create tables, database said:
ERROR: syntax error at or near "(" at character 1320
CREATE TABLE phorum_users ( user_id serial8, username varchar(50) NOT NULL default '', password varchar(50) NOT NULL default '',cookie_sessid_lt varchar(50) NOT NULL default '', sessid_st varchar(50) NOT NULL default '', sessid_st_timeout integer NOT NULL default 0, password_temp varchar(50) NOT NULL default '', email varchar(100) NOT NULL default '', email_temp varchar(110) NOT NULL default '', hide_email smallint NOT NULL default 0, active smallint NOT NULL default 0, user_data text NOT NULL default '', signature text NOT NULL default '', threaded_list smallint NOT NULL default 0, posts integer NOT NULL default 0, admin smallint NOT NULL default 0, threaded_read smallint NOT NULL default 0, date_added integer NOT NULL default 0, date_last_active integer NOT NULL default 0, last_active_forum integer NOT NULL default 0, hide_activity smallint NOT NULL default 0,show_signature smallint DEFAULT 0 NOT NULL, email_notify smallint DEFAULT 0 NOT NULL, pm_email_notify smallint DEFAULT 1 NOT NULL, tz_offset smallint DEFAULT -99 NOT NULL,is_dst smallint DEFAULT 0 NOT NULL ,user_language VARCHAR( 100 ) NOT NULL default '',user_template VARCHAR( 100 ) NOT NULL default '', moderator_data text NOT NULL default '', moderation_email smallint not null default 1, settings_data text NOT NULL DEFAULT '' PRIMARY KEY (user_id))

Looks like a missing comma before the PRIMARY KEY
Re: PostgreSQL preview available
January 13, 2007 06:58PM
Any plans for a Phorum 3 conversion from Postgresql? Looks like the only thing available right now is for mysql.
Re: Changes from the MySQL version
January 13, 2007 08:22PM
Quote
DanLangille
I noticed that phorum_messages.closed is defined as an integer. However, in PHP it is stored as a boolean. I changed the code for PostgreSQL as follows:

column definition: closed boolean NOT NULL default false

phorum_db_post_message(): " . ($message['closed'] ? 'TRUE' : 'FALSE');

This also needs to be done in phorum_db_update_message.
Re: PostgreSQL preview available
January 14, 2007 11:23AM
Quote
eeek
Any plans for a Phorum 3 conversion from Postgresql? Looks like the only thing available right now is for mysql.

Somebody just needs to write the phorum3_in.php file to work with postgresql. The main phorum3to5 script I believe is not db dependent and only uses the phorum db api.

In theory, the phorum3_in.php script could be written to use the old Phorum 3 db classes. But, I have not tried this out.

Brian - Cowboy Ninja Coder - Personal Blog - Twitter
Re: PostgreSQL preview available
January 14, 2007 01:30PM
Quote
eeek
Could not create tables, database said:
ERROR: syntax error at or near "(" at character 1320
[...]
Looks like a missing comma before the PRIMARY KEY

I fixed the problem in the 5.2 development version. The next snapshot release should fix this problem.


Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
Re: PostgreSQL preview available
January 14, 2007 02:13PM
Quote
brianlmoon
Somebody just needs to write the phorum3_in.php file to work with postgresql.

Ok. Right now I'm working on a new project. When I get that working I'll be looking to convert my old phorums and if nobody else has written the script by then, I'll do so.
Re: PostgreSQL preview available
January 14, 2007 03:53PM
Dan, I did some major overhaul of postgresql.php. Removing mysqlisms, introduced async queries in order to get better error handling from PHP, etc. The code still needs testing as I am not satisfied with it yet. Would you be willing to take a look at it?

I also did 3/4 of phorum3 to phorum5 convert for pgsql. The missing part is sequence handling and error reporting.
Re: PostgreSQL preview available
January 31, 2007 02:44AM
Just hit another bug:

ERROR: syntax error at or near "SET" at character 31: INSERT INTO phorum_pm_folders SET user_id=2, foldername='stuff'

I'll try to track it down.
Sorry, only registered users may post in this forum.

Click here to login