File: README.md

package info (click to toggle)
pgcharts 1.0+2017-09-16-1
  • links: PTS, VCS
  • area: non-free
  • in suites: bullseye, buster, sid
  • size: 25,228 kB
  • sloc: lisp: 1,779; java: 1,601; xml: 330; jsp: 162; makefile: 123; php: 70; sql: 64; sh: 39
file content (125 lines) | stat: -rw-r--r-- 3,904 bytes parent folder | download
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
# PostgreSQL Charts

## Screen Shots

It's intended as a visual project. Here's what it looks like.

### Query editing pane

![pgcharts-query](https://raw.github.com/dimitri/pgcharts/master/propaganda/pgcharts-query.png)

### Chart pane

![pgcharts-bar-chart](https://raw.github.com/dimitri/pgcharts/master/propaganda/pgcharts-bar-chart.png)

### Query listing pane

![pgcharts-query-list](https://raw.github.com/dimitri/pgcharts/master/propaganda/pgcharts-query-list.png)

### Chart only pane

So that you can give an URL to just the chart for your coworkers to see (and
download as a PDF, PNG, JPEG or SVG document):

![pgcharts-chart](https://raw.github.com/dimitri/pgcharts/master/propaganda/pgcharts-chart.png)

## Description

The *pgcharts* projects is a little web application that takes as input an
SQL query text and outputs its data in one of the following forms:

  - HTML table
  - Column Chart
  - Bar Chart
  - Pie Chart
  - Donut Chart
  
With more to come (TODO):

  - Area Chart
  - Line Chart
  - Stacked Area Chart
  - Stacked Bar Chart
  - Grouped Bar Chart
  - CSV file

# Initial Setup

The *pgcharts* application needs its own PostgreSQL database to be able to
register user queries and their charts setup:

    $ createdb pgcharts
    $ pgcharts setup pgsql://localhost/pgcharts
    
Then you can start the service, which defaults to listening to
[http://localhost:9042/]():

    $ pgcharts start
    $ open http://localhost:9042/

Now, you can use *pgcharts* from your browser. Issue new query, save them
away, and see nice charts from their results!

# Registering databases

Once the *pgcharts* database has been created, it's necessary to
***register*** the database servers you want to run queries against:
    
    $ pgcharts register pgsql://user:pass@host/dbname
    $ pgcharts register pgsql://user:pass@host/seconddbname?sslmode=require
    
The *sslmode* option accepts the following values: `disable`, `allow`,
`prefer` and `require`. The `allow` and `prefer` options are implements in
the same way, translating to the
[Postmodern](https://marijnhaverbeke.nl/postmodern/postmodern.html)
PostgreSQL driver's value `:try`, where `:try` means *if the server supports
it*.

# Implementation

pgchart needs a database where to handle its own data, as it is storing a
list of database connections (where to run the queries) and a list of
queries (with a name and a tags list).

TODO: see about storing query results on the *pgcharts* database so that
      it's possible to get back to them later. Maybe with some ways to run
      the query again and compare?

# Security

The *pgcharts* web service offers no security implementation, no user role
management or privileges. To keep the service secure, users are only allowed
to query against *registered* database servers.

To register a database server to *pgcharts*, the command line interface must
be used, so only the service administrator is in a position to register new
database servers.

# Usage

pgcharts is a self-contained web application. As such, when you start the
command line application, it starts its own web server that you can connect
to.

# Install

The *pgcharts* application has been written in Common Lisp and uses a bunch
of librairies that are available through the *Quicklisp* distribution
system. The included `Makefile` cares about building a self-contained binary
for you, and can be used as following:

    $ <install recent sbcl>
    $ make
    $ ./build/bin/pgcharts --help

Note that the self-contained binary also includes static web resources such
as *jquery*, *bootstrap*, *Highcharts* and *codemirror*.

## Build Dependencies

You need a recent enough [SBCL](http://sbcl.org/) Common Lisp compiler to be
able to compile pgcharts. It's easy to install on Linux, MacOSX and Windows.

    debian$ sudo apt-get install sbcl
    centos$ sudo yum install sbcl
    macosx$ brew install sbcl