Vanilla 1 is no longer supported or maintained. If you need a copy, you can get it here.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.

Vanilla Gets PostgreSQL Support!

edited October 2007 in Vanilla 1.0 Help
After about a week of hacking around on Vanilla, the team here at Zoto has managed to get Vanilla running with PostgreSQL. We've tested this patch on a fresh install of Vanilla, and believe it to be worthy of being posted here.

Get the tarball here:

The tarball contains the following files:

N Framework.Class.PgSQL.php
M Framework.Class.SqlBuilder.php

M People.Class.Authenticator.php
M People.Class.UserManager.php

M Vanilla.Class.CategoryManager.php
M Vanilla.Class.CommentManager.php
M Vanilla.Class.Discussion.php
M Vanilla.Class.DiscussionManager.php
M Vanilla.Class.SearchManager.php

M pgsql.sql
M installer.php

To install, simply extract a *NEW* copy of Vanilla, rename it to "vanilla", and then install the files into the new copy thusly:

tar xvfz Vanilla.1.0.1-PostgreSQL.tar.gz
cd Vanilla.1.0.1-PostgreSQL
cp -R * ../vanilla/

Don't forget to do a "chown -R apache vanilla" and a "chmod -R ug+rw vanilla" to the Vanilla directory after you do the copy.

A few caveats...

- the installer should only be used on a fresh install of Vanilla
- the installer has line 145 commented out to ignore the fact that there may already be .php files in conf (don't ask)
- the upgrader.php file hasn't been touched, so it updating a previous install won't work
- the modifications to the library/Vanilla/ and library/People/ classes were necessary because not all SQL building is abstracted into SqlBuilder (excuses, excuses)
- all the double quoting (" ") was done to satisfy PostgreSQL's respecting of case sensitive field names
- the generalized double quoting in the SqlBuilder class has some messed up logic to handle the SQL building going on in the various classes (which it should be doing, FWIW)

Our recommendations are that someone update the SqlBuilder class to do escaping of field names to satisfy case sensitive databases, and make it work well with MySQL. We also recommend that *ALL* SQL queries (like the +1, =1 stuff in the People classes) be moved to a function(s) in the SqlBuilder class, so escaping doesn't have to occur in those files.

We'd do this ourselves, but a) we're lazy, and b) we have lots more code to write.

We took a little extra time to test and contribute this because we will be using Vanilla Forums on our site at (preview up at when we re-launch the service in a few months. We felt that a little give-back was necessary to the Vanilla community. We hope that others will do the same for their modifications.


The Zoto Team


  • Options
    Dont forget to add this to the addons site :)
    If you have a list of places where not all the SQL has been abstracted Mark might be interested to know...I think he's probably under the impression that it's all seperate and may just have missed a bit. I'm not sure though.
  • Options
    Thanks. I added it to the addons site.

    The changes to the class files are pretty minimal. It should be pretty evident from a diff on those files what changes are going to be necessary.

    If I had time enough to do it right now, I would have made it work with both MySQL and PostgreSQL - so we could get it included in the next release. If I have time later, and nobody else wants to do it in the meantime, I'll work to get it to that point.
  • Options
    edited November 2006
    Nice! Exactly as I requested. Please contact Mark so you can work together for an official PostgreSQL version. Thumbs up! Great! Between you are still using PhpBB.
  • Options
    TomTesterTomTester New
    edited November 2006
    Haha, another item we can "correct" on <a href="">this forum software comparison site</a>
  • Options
    MarkMark Vanilla Staff
    Awesome! I'm glad you guys got it worked out, kordless :) I'll see what I can do about getting this stuff into the next core release.
  • Options
    What is the main advantage of a PostgreSQL DB over MySQL (speed? size?) (I host at MT, they offer both, want to make the right decision before I launch)
  • Options
    PostgreSQL should be as fast as MySQL under most circumstances. However, most of the benchmark/comparisons done out there don't take into account that you must manually configure PostgreSQL before you see the performance gains, so many sites might erroneously report that it's "slower". There is plenty of documentation out there to help you tweak your particular system.

    As for the advantages, there are to many to list here. I'm not openly taking a dig at MySQL, BTW! I love MySQL for it's ease of use, but sometimes you need a database that does things like transactions, store procedures, full joins, constraints, cursors, views, etc. Also, and this is the most important issue for Zoto, we are able to use a multi-master setup with PGCluster, to ensure replication of all our important data.

    MySQL is definitely catching up in most areas that other enterprise level databases have been at for a while, and it's a good solution if you are just looking to run a blog/forum combo. With 5.0, most of the issues I list here are addressed, but it's still in beta. PostgreSQL also might give you a few headaches, and definitely has a less broad community than does MySQL, so if you are looking for something quick and fast, I'd do MySQL.

    Hope this helps!
  • Options
    Thanks. Don't need the headache, will stick to MySQL ;-)

    (Note: I didn't realize I almost caused a flame war until I started doing some research on-line and found out PGress vs MySQL is like PC vs Mac etc.)
  • Options
    Any updates on PostgreSQL support?
  • Options
    With Vanilla 1.1.3 out, any chances to get Postgres support in 1.1.4? Any major obstacles?
This discussion has been closed.