PostgreSQL & PHP Tutorials - Rss Blogs Feed

Database Design Issues

chris@designmagick.com (Chris Smith) — Thu, 20 Apr 2006 10:15:52 -0400

Creating a good database design takes some thought. Sometimes, a normalized database won't perform the way you want it. Using some features of postgresql, you could get your performance back.

I decided to use some functions and triggers to keep queries nice and simple, but not lose any functionality and keep data redundancy to a minimum.

Read more..

Forum Progress Part II

chris@designmagick.com (Chris Smith) — Mon, 17 Apr 2006 09:27:34 -0400

The forum is very slowly getting put together. The basic admin areas are done:

- forums have group access
- groups can have users

Since most of the work is done on the frontend, I have been putting the design together and working out how to keep things in check.

Since postgresql has advanced features, I've decided to use some of those.

Instead of having to do a query to get thread counts and post counts per forum, I'll use a trigger to keep this info up to date when a thread or post is added to a particular forum.

I also use a plpgsql trigger to keep the "last post time", "last post author" and "last post id" (which makes navigating a particular forum a little easier).

Lastly, if a forum, group, or user gets deleted, then it will clean up after itself by using referential integrity checks and "on delete cascade" triggers. This will cause postgresql to delete anything that refers to a particular item.

Much easier than having to replicate that functionality in code and missing something!

Forum Progress

chris@designmagick.com (Chris Smith) — Sun, 02 Apr 2006 08:59:11 -0400

I haven't made much progress with the new forum yet, but I've worked out how to manage permissions.

At the bottom level will be users.

Users will be in groups.

Groups will have permissions in particular forums (so a user can be a forum moderator in a particular forum, but a simple user in another).

So permissions are basically worked out (writing the API will be a little bit of work but shouldn't be too difficult now I've worked out the logic behind it all).

Then, forums will (potentially) have subforums.

A forum will have many threads.

New project

chris@designmagick.com (Chris Smith) — Tue, 28 Mar 2006 06:02:52 -0500

I've decided to do something a little more constructive than random tutorials.

I've been looking for a forum to put on the site to make it a bit more interactive, but haven't been able to find anything suitable.

Either they are mysql-only or security-issue prone.

Fud-forum works with postgresql but doesn't take advantage of a lot of postgresql features (like subqueries) because it has to work in mysql and postgresql - so it does a ton of queries with every page.

So, what features am I looking for?

On the tech side:

- postgresql support
- clean, fast
- nice interface
- limited sql queries
- possibly caching.

On the design/interface side:
- clean
- template based
- fast

If you have any suggestions on where to start looking for such a forum, or have any feature suggestions send 'em through!

Might see if I can set up a cvs or subversion server and get others to help me out ;) Let me know if you're interested.

Comment Spam

chris@designmagick.com (Chris Smith) — Sun, 26 Mar 2006 07:10:06 -0500

I'm glad I set up comments with comment-spam in mind. They are not automatically authorized, and I added checks in to make sure you can't send mail through them (I don't get notified when a comment gets made, it's just put into the system without being authorized).

I'm starting to see a few spam messages come in through that way, maybe it's time for captcha to be added.. we'll see if it becomes more of a problem.

I just added a quick introduction to full-text indexing in postgresql, since it's not as easy as other systems (unfortunately). The main development site for full-text indexing has some good documentation, so it's a quick guide to get you started.

Log analyzer

chris@designmagick.com (Chris Smith) — Wed, 08 Mar 2006 07:02:28 -0500

I found a useful little tool the other day.

Practical Query Analyzer (written in ruby) will parse a postgresql or mysql log (though at last look mysql support was broken) and tell you what sort of queries are being run. This can help you work out where to concentrate on increasing speed or reducing the number of queries being run.

I use mysql a lot at work so I wrote a small perl script to give me the same output. My ruby skills are extremely basic so it seemed easier to do it in perl.

New tutorials on the way

chris@designmagick.com (Chris Smith) — Tue, 07 Mar 2006 06:42:39 -0500

Hi all,

I'm busy trying to come up with new tutorial ideas.

I'm in the middle of writing a tutorial about sql trees (this is a bit more advanced than the current ones on the site). They replace parent / id relationships and with larger systems can speed things up quite a bit.

I'm also in the process of writing a python script to analyze queries to check for indexes. This one is going to take a while since I'm new to python and reading postgresql explain output is tricky :)

If you have any ideas on tutorials - please send them through!

If you have any comments about the site or the content, send it through - I'm happy to get all feedback!

Mysql..

chris@designmagick.com (Chris Smith) — Thu, 02 Mar 2006 06:03:38 -0500

Bit of a strange topic to find on a site about postgresql.. but mysql has some strange ideas.

I found a mysql blog that details a rather strange bug.

Some queries are not logged into the system log.

I'm not really sure why but the poster doesn't seem to think it's a critical bug!?

If you want to see what queries are running against your database, you'd think the system log would capture everything.

I wonder if it could be modified to actually insert data using an "insert into select" query..

Blog Intro

chris@designmagick.com (Chris Smith) — Thu, 02 Mar 2006 05:58:59 -0500

Hey guys,

This blog is going to be about random stuff to do with the web - not necessarily php or postgresql.

I'm still looking for ideas on articles so send 'em through!