Firefox PHP

PostgreSQL

Posted by Dan Langille 
All files from this thread

File Name File Size   Posted by Date  
phorum-pgsql-ree-2010-02-14.tar.gz 51.7 KB open | download Rick 02/15/2010 Read message
PostgreSQL
January 02, 2008 12:51PM
With reference to previous PostgreSQL work: [www.phorum.org]

I'm ready to start in on PostgreSQL with 5.2. The 5.1 stuff never made it to production.

There are several people who want PostgreSQL and others who would use Phorum if PostgreSQL was an option.

I'll do the coding work, but I need support from others in the form of:

- code reviewers
- testers
- other coders

I don't have a schedule, but it may appear at any time. I know I have a flight next weekend, during which I can work on this. Perhaps I'll get an opportunity sooner.

--
Phorum Developer - PostgreSQL on FreeBSD
[www.langille.org]
Re: PostgreSQL
January 02, 2008 03:53PM
This was already pointed out in the chat, but this is possibly interesting for other database porting projects as well. The 5.2 database layer has been rewritten from scratch to let all database operations go through one function (phorum_db_interact). Porting the database layer to a different database system has become a lot easier because of this. Some effort was also made to use as much ANSI compatible database code as possible, without losing certain MySQL optimizations.

I think that porting the database layer requires:

- Copying include/db/mysql.php to include/db/nameoftheotherdbsystem.php
- Implementing a phorum_db_interact() for the new database system *
- Updating the CREATE TABLE and CREATE INDEX statements for the new system
- Implementing REPLACE DELAYED INTO statement differently (from the search system)
- Possibly implemented a different search altogether

*) The MySQL layer has a subdirectory include/db/mysql/ that contains multiple files. Each of those has its own phorum_db_interact() implementation for different cases. If you only need to support a single database layer phorum_db_interact() function, then you can also put that one right in the nameoftheotherdbsystem.php file.

Maybe there are more steps, but these are the ones that I remember from a test port to sqlite which I did once (took me about an hour back then).


Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
Re: PostgreSQL
January 02, 2008 10:20PM
Heh, I was just about to come here and ask...

I got started tonight. Will continue another time.

thanks

--
Phorum Developer - PostgreSQL on FreeBSD
[www.langille.org]
Re: PostgreSQL
January 03, 2008 03:39AM
I can test the code. In case of problems I'll review where the problem may appear, but I am not a DB programmer.
Re: PostgreSQL
January 03, 2008 09:40AM
covex: Thanks. I'll post something here when there is a URL you can download. It will probably be a small tarball with a few files and instructions.

--
Phorum Developer - PostgreSQL on FreeBSD
[www.langille.org]
ready for testing
January 03, 2008 07:08PM
Most things are working.

See [toll.unixathome.org] for details

--
Phorum Developer - PostgreSQL on FreeBSD
[www.langille.org]
Re: PostgreSQL
January 04, 2008 06:48PM
Finished. Searching is in there now.

Please report any bugs.

--
Phorum Developer - PostgreSQL on FreeBSD
[www.langille.org]
Re: PostgreSQL
April 16, 2008 06:15PM
Is the code now in the repository?

I'm currently running a phorum 5.2.6a using mysql with around 46k messages and 200k users (from the main community already ported to postgres).

Is there a easy way to migrate from mysql to postgres? I could imagine something like mysqldump myphorumdb | psql phorumdb
or something like that... or are there other things I'll have to keep in mind?

thx for the great work!
Re: PostgreSQL
April 16, 2008 06:33PM
Quote

or are there other things I'll have to keep in mind?

Yeah, the schemas between mysql and postgresql aren't compatible.


Thomas Seifert
Re: PostgreSQL
May 30, 2008 09:10AM
Made one quick modification to the configuration in /include/db/postgresql/postgresql.php to take the "port" option into account

located at line 74

Original:
        $conn_string = '';
        if ($PHORUM['DBCONFIG']['server'])   $conn_string .= ' host='     .  $PHORUM['DBCONFIG']['server'];
        if ($PHORUM['DBCONFIG']['user'])     $conn_string .= ' user='     .  $PHORUM['DBCONFIG']['user'];
        if ($PHORUM['DBCONFIG']['password']) $conn_string .= ' password=' .  $PHORUM['DBCONFIG']['password'];
        if ($PHORUM['DBCONFIG']['name'])     $conn_string .= ' dbname='   .  $PHORUM['DBCONFIG']['name'];

New:
$conn_string = '';
        if ($PHORUM['DBCONFIG']['server'])   $conn_string .= ' host='     .  $PHORUM['DBCONFIG']['server'];
        if ($PHORUM['DBCONFIG']['port'])     $conn_string .= ' port='     .  $PHORUM['DBCONFIG']['port'];
        if ($PHORUM['DBCONFIG']['user'])     $conn_string .= ' user='     .  $PHORUM['DBCONFIG']['user'];
        if ($PHORUM['DBCONFIG']['password']) $conn_string .= ' password=' .  $PHORUM['DBCONFIG']['password'];
        if ($PHORUM['DBCONFIG']['name'])     $conn_string .= ' dbname='   .  $PHORUM['DBCONFIG']['name'];

Other than that, so far it's working with Phorum 5.2.8.

I'm developing a CMS around this setup, so if I find any other bugs or missing functionality, I'll be sure and post fixes ;)
Re: PostgreSQL
August 09, 2008 10:52PM
Hello,

I have a question about postgresql support. I notice in this thread there is
a comment that postgresql works in 5.2.8, there is also a note about a bug
fix related to the port that the DB might use. BUT, I look into SVN trunk
and see the lastest version is 7 months old. What is up with that?
Where is the lates postgresql stuff?

Also, will postgresql support ever be official? That is will it ever be
in a mainline release?

Thanks to the community for all the hard work. I've spent the last month
really learning about phorum and look forward to switching from phpBB.
Re: PostgreSQL
August 10, 2008 08:36AM
If a postgresql/php developer stands up and can provide code and full time support, then I see no reason why it could not be a part of the core Phorum releases. Right now it is based on drive-by support, so there is nobody working on it full time.

If one of the developers would switch to postgresql, then this would change things of course, but right now we all are using MySQL 5. The most likely dev to switch to PostgreSQL would be me, since I do a lot of my corporate database development in PostgreSQL. However, up to now I haven't seen a real need for PostgreSQL on my live Phorum installs.


Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
Re: PostgreSQL
August 10, 2008 05:23PM
Like Maurice said, someone has to step up.

I did some work quite some time ago, but I don't have the time to keep it up to date.

--
Phorum Developer - PostgreSQL on FreeBSD
[www.langille.org]
Re: PostgreSQL
September 03, 2008 11:14AM
Quote
MythMonk
Other than that, so far it's working with Phorum 5.2.8.

Can I ask a "hopefully" simple question? Do you just get the postgresql specific files from
head and place them into the appropriate directories of a 5.2.8 install?
Re: PostgreSQL
September 13, 2008 10:09PM
Yeah, all you have to do is unpack the the archive from Dan's site into your Phorum install, and create your config.php appropriately.

One thing I ran across while setting up a production site with this today, was that some of the admin stuff is broken - ie, everything under the "Global Settings" header on the admin page won't save. I'm working on this issue right now. If I get it working satisfactorily, I'll post updates.
Re: PostgreSQL
September 25, 2008 05:27PM
I too just went through the pain of getting postgresql to work with Phorum. Using the above I was able to get it working. Like MythMonk mentioned things seemed to be working but the admin section settings weren't saving. What I found was that Dan's postgresql.php file has an error in: phorum_db_update_settings() function (include/db/postgresql.php is the file). The code attempts an insert initially and if an error is detected, it then attempts an update. I found that once the settings exist, the insert throws and error, but it doesn't make it back to phorum_db_update_settings() so it merrily completes (without calling the update). All I did was to reverse the order of the statements, so the update is attempted first, if it fails, attempt the insert. This seems to be working. I haven't had time to figure out why the error didn't make it to the caller.

Mike
Re: PostgreSQL
September 25, 2008 05:37PM
Normally, an error makes the system stop, since errors should never occur. But in the core of the database layer, a system is used to let the core function (phorum_db_interact()) ignore certain errors. This way, the main db layer code can tell the interact function to ignore duplicate key errors (which would be needed for doing the settings updates or inserts like they are implemented now).

My guess is that the functionality for ignoring duplicate key errors is not implemented correctly (maybe it's using an incorrect error code to check against for this case). The file in which this is implemented, would be include/db/postgresql/postgresql.php.

Reversing the order might work, but it is incorrect. Two concurrent Phorum processes might run into a race condition:

 process 1           process 2

 TRY UPDATE
     |
     |               TRY UPDATE
     v                    |
UPDATE FAILS              |
     |                    v
     |              UPDATE FAILS
     v                    |
 TRY INSERT               |
     |                    v
     |                TRY INSERT
     v                    |
 INSERT OK - - - - - - - >| duplicate key error
                          v
                     INSERT FAILS

When doing it the other way around, it is a safe operation:

 process 1           process 2

 TRY INSERT
     |
     |               TRY INSERT
     v                    |
  INSERT OK - - - - - - ->| duplicate key error
                          v
                    INSERT FAILS
                          |
                          |
                          v
                      TRY UPDATE
                          |
                          |
                          v
                      UPDATE OK


Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
Re: PostgreSQL
November 30, 2008 07:23AM
There's a trick you can use while developing in MySQL and do keep any ISO database into account.
SET SESSION sql_mode = 'TRADITIONAL,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,PIPES_AS_CONCAT'
It makes MySQL behave almost like all other databases that run conform ANIS/ISO SQL Standards.

Will setup a testbed here
Re: PostgreSQL
December 01, 2008 12:14PM
Quote
DJ Maze
SET SESSION sql_mode = 'TRADITIONAL,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,PIPES_AS_CONCAT'

Or set sql_strict_mode to 1 in the config file.

Brian - Cowboy Ninja Coder - Personal Blog - Twitter
Re: PostgreSQL
December 18, 2008 09:23PM
Rather than trying to back phorum into just postgresql why not back it into something like adodb/adodblite and let users choose what database they wish to run. As long as the sql is kept strict it should then work in just about anything and then you don't need a dedicated 'postgresql' programmer/supporter you just support the adodb layer? (I'm currently looking at backing phorum into either oracle or postgresql and will probably do that via adodb)
Re: PostgreSQL
December 19, 2008 02:49PM
Quote
DarkKlown
Rather than trying to back phorum into just postgresql why not back it into something like adodb/adodblite and let users choose what database they wish to run. As long as the sql is kept strict it should then work in just about anything and then you don't need a dedicated 'postgresql' programmer/supporter you just support the adodb layer? (I'm currently looking at backing phorum into either oracle or postgresql and will probably do that via adodb)

Because we want it to work. Because we want it to work faster than a snail in the snow. Because we want to use SQL that is not super lame-o.

"Standard" SQL is a myth. Even if you use adodb, the SQL you write will either be crippled or will not work on other RDBS. Phorum 3 had LOTS of "if(mysql)" and "if(postgresql)" in it. It was quite nasty.

If you are bound and determined to use something other than native functions, please, oh please, use PDO. PDO will not kill your performance like adodb will. Every line of code you put between your call and mysql_query, pg_query or whatever makes things slower.

See:

While I am at it, prepared statements suck too. Just so we get that in there.

Ok, my rant is over now. I feel better.

Brian - Cowboy Ninja Coder - Personal Blog - Twitter
Re: PostgreSQL
December 19, 2008 05:38PM
adodb is indeed slow but adodb-lite is alot better, also with the php-adodb extension things speed up alot more. I understand your reasoning however, the main reason i choose phorum over other message systems was purely based on how quick it is it.

I'm just starting to get uncomfortable with all the people movements happening at mysql and the rumor they are going to be spending less devel time on the free version of mysql to focus on enterprise. Personally i don't have $3,000 a year to spend on a database (tho the MySQL Query Analyzer in enterprise monitor does look sexy), also table size is starting to become a issue with performance and the stats show postgres to be able to cope so it would be nice to move that way depending on how much work would be required (i'm rather lazy).

I've not really had a play with PDO so i'll have a go with that.
Re: PostgreSQL
December 20, 2008 12:47AM
Well, don't worry. As soon as Drizzle is ready, Phorum will have support for it. I am on the mailing list and am sort of a fly on the wall voice for the power users.

Brian - Cowboy Ninja Coder - Personal Blog - Twitter
Re: PostgreSQL
February 02, 2009 11:18PM
OK, here is a follow up. I have attempted to use Postgresql with Phroum and find
that upon trying to create the admin user -right after the tables are made- I get an error
that seems to be saying that fields which are defined as int's can't be set because they
have paren's around the values. Below is the error displayed in my browser.

I am willing to work thru this and/or other Postgresql issues and contribute fixes if warranted. I only ask for a little guidence.

Also, I am working from head as of a few days ago. I have also tried the two other postgresql.php files
from Dan's site, both exhibit the same behaviour.

Thanks,

Here is the warning
======================================================================

Phorum Database Error
Sorry, a Phorum database error occurred.
Please try again later!
Error:
ERROR: invalid input syntax for integer: "" (22P02): UPDATE ph__users SET username = '', password = '', email = '', active = '', admin = '', date_added = '', date_last_active = '', password_temp = '', display_name = '' WHERE user_id = 3
Backtrace:
Function phorum_database_error called at
{path to Phorum}/include/db/postgresql/postgresql.php:168
----
Function phorum_db_interact called at
{path to Phorum}/include/db/postgresql.php:3623
----
Function phorum_db_user_save called at
{path to Phorum}/include/db/postgresql.php:3527
----
Function phorum_db_user_add called at
{path to Phorum}/include/api/user.php:721
----
Function phorum_api_user_save called at
{path to Phorum}/include/admin/install.php:191
----
Function require_once called at
{path to Phorum}/admin.php:101
----
Re: PostgreSQL
February 03, 2009 12:02AM
I think the problem is that postgres doesn't have things like auto_increment and some of the NOW() functions that mysql also offers are lacking (or aren't seen as important by the psql developers). Also for interger fields you must actually put '0' and not '' like in mysql where it thinks (hey '', that's 0).

After porting over my database and making lots of changes to sql all over the place i kinda moved on, and instead focused efforts at caching query results for the speed boost.
Re: PostgreSQL
February 05, 2009 12:37PM
Anyone?
Re: PostgreSQL
April 11, 2009 05:29PM
Quote

postgres doesn't have things like auto_increment

It does have that feature; it's just not spelled the same.

Quote

NOW() functions that mysql also offers are lacking[/quoet]

Such as?
Re: PostgreSQL
April 11, 2009 05:57PM
I need to convert a phorum 3 site. Am I correct in assuming I'll have to write the conversion software myself?
Re: PostgreSQL
April 11, 2009 07:36PM
PostgreSQL does not spell auto incremental functionality different. It uses sequences for handling the auto increments. There is a simple keyword that one can use at table create time ("serial" IIRC), but under the hood this creates a sequence and set the field's default value to nextval(sequence). So yes, you can accomplish the same kind of thing, but it's more than a different spelling alone.

When you are running Phorum 3 using PostgreSQL, then I'm not sure if there is already a conversion script available. You might have to take the default upgrade script from the "scripts" directory in the Phorum distro and modify that for PostgreSQL. Possibly, somebody has such a script lying around already or can tell us to what extend the existing conversion script can be used. I have no experience with that.


Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
Re: PostgreSQL
April 11, 2009 08:25PM
Quote

You might have to take the default upgrade script from the "scripts" directory in the Phorum distro and modify that for PostgreSQL.

I'm working my way through it now. Lots of fun ;)
Sorry, only registered users may post in this forum.

Click here to login