In our big-brother-esque age, we like to have everything under control. While in real life it may not always be a good thing, we certainly want to know what happens to our data. I've seen all sort of solutions to log database changes, many of them in PHP or some other language, but IMHO that's not how it should be done. Since the DBMS itself often offers the tools we need, let's see how to use them effectively.
Database table auditing seems to be an hot topic: there are ad-hoc tools , there's an example in the postgresql manual and of course there are blog posts about it. Nothing new under the sun.
But I'd still like to talk about it :-), since we're going to see a different approach, and learn something about PostgreSQL triggers and functions in the process, aren't we?
One of the most powerful features of PostgreSQL is its support for user-defined functions written in various programming languages, including pure SQL, C, Perl, Python, and PHP. Perhaps the most common programming language for PostgreSQL functions, however, is PL/pgSQL (don't ask me to pronounce it), because it comes with PostgreSQL and is easy to set up.
A materialized view is defined as a table which is actually physically stored on disk, but is really just a view of other database tables. In PostgreSQL, like many database systems, when data is retrieved from a traditional view it is really executing the underlying query or queries that build that view. This is great for better representation of data for users, but does not do anything to help performance.
PostgreSQL is a high-quality, full-featured open source database that runs on a huge number of platforms. The number of possible combinations of machine architecture, operating system, system libraries, compilers, and configuration sets is truly staggering. It also has just increased significantly with the recent release of the 8.0 version, which supports Windows natively for the first time. One of the problems that the PostgreSQL project faces, as many other similar projects do, is how to know whether some change has broken things on some platform. We don't have the resources to run every possible combination, nor even a tiny proportion of them. On several occasions it has happened that breakage only became apparent some time after a change went in. We created PostgreSQL BuildFarm to address that difficulty.
You can and should automate routine database tasks, to remove the possibility of user error and to let yourself focus on other, more interesting work. If you use PostgreSQL on Unix or Linux, you can combine the shell with PostgreSQL's psql client to automate database chores.
There are a number of ways to get your database-backed Web application to run faster on the same hardware--allowing you to postpone upgrades for at least a little while, thus cutting costs. One way involves examining how your applications are interacting with the database. Tom Copeland explains how to "tune the queries" for a PostgreSQL database.
Getting free software and installing can sometimes be a daunting task. Not so with PostgreSQL.
Cornelia Boenigk, Autorin des im dpunkt-Verlag im August erscheinenden deutschsprachigen PHP und PostgreSQL Buch "PostgreSQL. Einführung und Praxis. Anwendungsentwicklung mit PHP4" hat uns netterweise ein Einstiegs-Tutorial über PostgreSQL und PHP zur Verfügung gestellt.
PHP is the most widely used Apache module available and provides a strong platform for Web application development. However, most people who use PHP with open source databases use PHP with MySQL. As an invitation to using PostgreSQL, I have written the following article on using PHP and PostgreSQL. There are already a lot of PHP articles out there on the Web, so I'm not going to cover the basic concepts here. Instead, I'll jump right to the chase and show the fundamentals of using PHP with PostgreSQL.
In this article, I'll guide you through the other half of the conversion process and explain how to take existing MySQL PHP code and make it Postgres-friendly.
