People have asked us how to calculate running totals a number of times; not a lot but enough that we feel we should document the general technique.
This approach is fairly ANSI-SQL standard and involves using SELF JOINS. In a later article we shall describe how to calculate moving averages which
follows a similar technique but with some extra twists.
Note that the below examples can also be done with a correlated sub-select in the SELECT clause and in some cases that sometimes works better. Perhaps
we shall show that approach in a later issue.
We tend to prefer the look of the SELF JOIN though and in practice it is generally more efficient since its easier for planners to optimize and doesn't always result in a nested loop strategy.
Just feels a little cleaner and if you are totaling a lot of columns (e.g number of items, products) etc,
much more efficient.
We would like to thank Jeff Crumbley of IILogistics for providing many of these steps
and informing us that Microsoft has finally released a
64-bit OLEDB for ODBC driver.
For those who have not experienced the torture of this situation - let me start with a little background.
First if you are running SQL Server 2005 32-bit and wished to create a linked server to a PostgreSQL server, everything is hunky dory. If
however you had a SQL Server 2005 64-bit server, you ran into 2 very annoying obstacles.
Obstacle 1: There for a long-time was no 64-bit ODBC driver nor native driver for PostgreSQL. This obstacle was somewhat alleviated
when Fuurin Kazanbai made experimental compiled 64-bit PostgreSQL ODBC drivers available which work for AMD and Intel based processors.
Obstacle 2: All looked good in the world until you tried this in SQL Server 2005 64-bit and low and behold - you needed a 64-bit OLEDB provider
for ODBC to use it in SQL Server 2005 64-bit. Yes we waited patiently for years for this piece to be available. We still love you Microsoft.
Then as Jeff Crumbley pointed out - Microsoft released an OLEDB 64-bit provider for ODBC in early April 2008.
People coming from SQL Server and MySQL often complain about how you can't query other databases
from within a PostgreSQL database. In Microsoft SQL Server, there is a concept of querying across databases
on the same server
with dbname.dbo.sometable and querying across servers (even of different types e.g. Oracle) by setting up a linked server and doing
something such as servername.dbname.dbo.sometable or using the OPENROWSET(..) or OPENQUERY(...) syntax.
MySQL has a similar feature by using syntax dbname.sometable, but MySQL lacks schemas so there is no way to segregate a
database into nice buckets as you can with SQL Server and PostgreSQL.
In this article we shall provide some examples of using the contrib module - dblink to query local PostgreSQL databases and remote PostgreSQL databases.
DbLink is probably most comparable in structure to SQL Server's OpenRowset functionality.
It lacks the power of SQL Server's Linked Server
approach or OPENQUERY that allows for synchronized joins between linked servers/databases and local tables and updates/inserts on linked servers. This makes it not terribly useful
in cases where you need to join lots of data with local data. It does however come in handy for bulk copy operations from one database/server to another.
Now in this Part 2B series, we shall demonstrate the same REST server service using PHP
Setting up the PHP application
PHP already has the PostgreSQL drivers available as a .so (on Linux) or .dll on Windows. For windows users if you
are running PHP under IIS and in ISAPI mode, you will not be able to dynamically load libraries, so you need to enable php_pgsql in your PHP.ini file.
We tend to keep it enabled regardless of which platform we are on since a lot of our PHP development involves PostgreSQL. The extension is php_pgsql in the php.ini file
PHP has numerous database abstraction libraries to choose from. We are using the adodb abstraction library for PHP which can be downloaded from http://adodb.sourceforge.net/.