Is your RDBMS letting you down?

Posted by Patrice Neff Tue, 19 Jun 2007

Saw an interesting presentation on Google Video about data mining in the TV world at Rentrak.

They talk about how they mine lots of data and get real-time reports from them. Basically it comes down to using a patched/extended PostgreSQL plus a simplified query language that they convert to SQL.

In that context it might be interesting to check out Bizgres, though it was not mentioned in the presentation. The Bizgres web site says:
bq. The Bizgres Project aims to make PostgreSQL the world’s most robust open source database for Business Intelligence.

Using TextMate for SQL queries

Posted by Patrice Neff Tue, 05 Sep 2006

TextMate offers an SQL bundle. That bundle can also be used to query MySQL and PostgreSQL databases directly from TextMate.

The configuration for the database access is done though environment variables. Specifically the following ones in the case of PostgreSQL:

  • PGHOST: Database host
  • PGPORT: Postgres port (usually the default of 5432 just works)
  • PGUSER: User name
  • PGDATABASE: Database to run queries on
  • TM_DB_SERVER: Tells TextMate to use PostgreSQL.

You can either set up these environment variables in the TextMate settings or – much more helpful – per project. In the project drawer deselect all files (by clicking on an empty spot) and then open the properties. There you can add project-specific environment variables.

Next open a file in the SQL mode. I now do that by having a specific directory in my project with SQL files. In that file prepare your statements, then select the one you want to run and press Ctrl+Shift+Q (or select the bundle item “Execute Selection as Query”).

Two queries that may be helpful:


— list tables
\dt

— describe table
\d tablename