Qreport

Executes a SQL query into a report table.

Add this line to your application's Gemfile:

gem 'qreport'

And then execute:

$ bundle

Or install it yourself as:

$ gem install qreport

Qreport rewrites a plain SQL query so that its result set can populate a report table. It automatically creates the report table based on a signature of the column names and types of the query result. It can also add additional columns to the report table for other uses, for example: batch processing. New queries, rollups and reports can be built from previous reports.

Currently supports PostgreSQL.

We have users that write articles. Generate a report named "users_with_articles" of all users that have written an article in N days:

SELECT u.id AS "user_id"
FROM   users u
WHERE 
  EXISTS(SELECT * FROM articles a 
         WHERE a.user_id = u.id AND a.created_on >= NOW() - INTERVAL '30 days')

Create a Qreport::ReportRun:

conn = Qreport::Connection.new(...)
report_run = Qreport::ReportRun.new(:name => :users_with_articles)
report_run.sql = <<"END"
  SELECT u.id AS "user_id"
  FROM   users u
  WHERE
    EXISTS(SELECT * FROM articles a
           WHERE a.user_id = u.id AND a.created_on >= NOW() - INTERVAL '30 days')
END
report_run.run! conn

Qreport translates the query above into:

SELECT 0 AS "qr_run_id"
     , nextval('qr_row_seq') AS "qr_row_id"
     , u.id AS "user_id"
FROM users u
WHERE 
  EXISTS(SELECT * FROM articles a
         WHERE a.user_id = u.id AND a.created_on >= NOW() - INTERVAL '30 days')

Then analyzes the columns names and types of this query to produce a result signature. The result signature is hashed, e.g.: "x2yu78i". The result signature hash is used to create a unique report table name: e.g. "users_with_articles_x2yu78i". The qr_report_runs table keeps track of each report run. A record is inserted into the qr_report_runs table with a unique id. Qreport then executes:

CREATE TABLE users_with_articles_x2yu78i AS
SELECT 123 AS "qr_run_id"
     , nextval('qr_row_seq') AS "qr_row_id"
     , u.id AS "user_id"
FROM users u
WHERE 
  EXISTS(SELECT * FROM articles a
         WHERE a.user_id = u.id AND a.created_on >= NOW() - INTERVAL '30 days')

The ReportRun object state is updated:

report_run.id # => Integer
report_run.nrows # => Integer
report_run.started_at # => Time
report_run.finished_at # => Time

Subsequent queries with the same column signature will use "INSERT INTO users_with_articles_x2yu78i".

Report queries can be parameterized using embedded ":word" tags. Parameter arguments are saved in the report run table.

report_run.arguments = {
  :interval => '30 days',
}
report_run.run! <<"END"
SELECT u.id AS "user_id"
FROM   users u
WHERE
  EXISTS(SELECT * FROM articles a
         WHERE a.user_id = u.id AND a.created_on >= NOW() - INTERVAL :interval)
END

Arguments can also represent "matching" patterns using a ":~" tag. Example: a Range of Time values matching a.created_on:

t = Time.now
report_run.arguments = {
  :interval => (t - 86400) ... t,
}
report_run.run! <<"END"
SELECT * FROM articles a WHERE :~ {{:interval}} {{a.created_on}}
END

Example setup:

$ sudo -u postgresql psql postgres=# create role test login password 'test'; CREATE ROLE postgres=# create database test owner test; CREATE DATABASE postgres=# \q $ PGHOST=localhost PGUSER=test PGDATABASE=test PGPASSWORD=... rake

1. Fork it
2. Create your feature branch (git checkout -b my-new-feature)
3. Commit your changes (git commit -am 'Add some feature')
4. Push to the branch (git push origin my-new-feature)
5. Create new Pull Request