perf script: Add Python script to export to postgresql

Add a Python script to export to a postgresql database.

The script requires the Python pyside module and the Qt PostgreSQL
driver.  The packages needed are probably named "python-pyside" and
"libqt4-sql-psql"

The caller of the script must be able to create postgresql databases.

The script takes the database name as a parameter.  The database and
database tables are created.  Data is written to flat files which are
then imported using SQL COPY FROM.

Example:

  $ perf record ls
  ...
  $ perf script report export-to-postgresql lsdb
  2014-02-14 10:55:38.631431 Creating database...
  2014-02-14 10:55:39.291958 Writing to intermediate files...
  2014-02-14 10:55:39.350280 Copying to database...
  2014-02-14 10:55:39.358536 Removing intermediate files...
  2014-02-14 10:55:39.358665 Adding primary keys
  2014-02-14 10:55:39.658697 Adding foreign keys
  2014-02-14 10:55:39.667412 Done
  $ psql lsdb
  lsdb-# \d
              List of relations
   Schema |      Name       | Type  | Owner
  --------+-----------------+-------+-------
   public | comm_threads    | table | acme
   public | comms           | table | acme
   public | dsos            | table | acme
   public | machines        | table | acme
   public | samples         | table | acme
   public | samples_view    | view  | acme
   public | selected_events | table | acme
   public | symbols         | table | acme
   public | threads         | table | acme
  (9 rows)
  lsdb-# \d samples
         Table "public.samples"
      Column     |  Type   | Modifiers
  ---------------+---------+-----------
   id            | bigint  | not null
   evsel_id      | bigint  |
   machine_id    | bigint  |
   thread_id     | bigint  |
   comm_id       | bigint  |
   dso_id        | bigint  |
   symbol_id     | bigint  |
   sym_offset    | bigint  |
   ip            | bigint  |
   time          | bigint  |
   cpu           | integer |
   to_dso_id     | bigint  |
   to_symbol_id  | bigint  |
   to_sym_offset | bigint  |
   to_ip         | bigint  |
   period        | bigint  |
   weight        | bigint  |
   transaction   | bigint  |
   data_src      | bigint  |
  Indexes:
      "samples_pkey" PRIMARY KEY, btree (id)
  Foreign-key constraints:
      "commfk" FOREIGN KEY (comm_id) REFERENCES comms(id)
      "dsofk" FOREIGN KEY (dso_id) REFERENCES dsos(id)
      "evselfk" FOREIGN KEY (evsel_id) REFERENCES selected_events(id)
      "machinefk" FOREIGN KEY (machine_id) REFERENCES machines(id)
      "symbolfk" FOREIGN KEY (symbol_id) REFERENCES symbols(id)
      "threadfk" FOREIGN KEY (thread_id) REFERENCES threads(id)
      "todsofk" FOREIGN KEY (to_dso_id) REFERENCES dsos(id)
      "tosymbolfk" FOREIGN KEY (to_symbol_id) REFERENCES symbols(id)

  lsdb-# \d samples_view
                 View "public.samples_view"
        Column       |          Type           | Modifiers
  -------------------+-------------------------+-----------
   id                | bigint                  |
   time              | bigint                  |
   cpu               | integer                 |
   pid               | integer                 |
   tid               | integer                 |
   command           | character varying(16)   |
   event             | character varying(80)   |
   ip_hex            | text                    |
   symbol            | character varying(2048) |
   sym_offset        | bigint                  |
   dso_short_name    | character varying(256)  |
   to_ip_hex         | text                    |
   to_symbol         | character varying(2048) |
   to_sym_offset     | bigint                  |
   to_dso_short_name | character varying(256)  |

    lsdb=# select * from samples_view;

   id| time       |cpu | pid  | tid  |command| event  |   ip_hex      |           symbol    |sym_off| dso_name|to_ip_hex|to_symbol|to_sym_off|to_dso_name
   --+------------+----+------+------+-------+--------+---------------+---------------------+-------+---------+---------+---------+----------+----------
   1 |12202825015 | -1 | 7339 | 7339 |:17339 | cycles | fffff8104d24a |native_write_msr_safe|    10 | [kernel]| 0       | unknown |         0| unknown
   2 |12203258804 | -1 | 7339 | 7339 |:17339 | cycles | fffff8104d24a |native_write_msr_safe|    10 | [kernel]| 0       | unknown |         0| unknown
   3 |12203988119 | -1 | 7339 | 7339 |:17339 | cycles | fffff8104d24a |native_write_msr_safe|    10 | [kernel]| 0       | unknown |         0| unknown

My notes (which may be out-of-date) on setting up postgresql so you can
create databases:

fedora:

        $ sudo yum install postgresql postgresql-server python-pyside qt-postgresql
        $ sudo su - postgres -c initdb
        $ sudo service postgresql start
        $ sudo su - postgres
        $ createuser -s <your username>

I used the the unix user name in createuser.

If it fails, try createuser without -s and answer the following question
to allow your user to create tables:

        Shall the new role be a superuser? (y/n) y

ubuntu:

        $ sudo apt-get install postgresql
        $ sudo su - postgres
        $ createuser <your username>
        Shall the new role be a superuser? (y/n) y

You may want to disable automatic startup.  One way is to edit
/etc/postgresql/9.3/main/start.conf.  Another is to disable the init
script e.g. sudo update-rc.d postgresql disable

Signed-off-by: Adrian Hunter <adrian.hunter@intel.com>
Cc: David Ahern <dsahern@gmail.com>
Cc: Frederic Weisbecker <fweisbec@gmail.com>
Cc: Jiri Olsa <jolsa@redhat.com>
Cc: Namhyung Kim <namhyung@gmail.com>
Cc: Paul Mackerras <paulus@samba.org>
Cc: Peter Zijlstra <peterz@infradead.org>
Cc: Stephane Eranian <eranian@google.com>
Link: http://lkml.kernel.org/r/1414061124-26830-8-git-send-email-adrian.hunter@intel.com
Signed-off-by: Arnaldo Carvalho de Melo <acme@redhat.com>

2 files changed, 32 insertions, 0 deletions

diff --git a/tools/perf/scripts/python/bin/export-to-postgresql-record b/tools/perf/scripts/python/bin/export-to-postgresql-record
new file mode 100644
index 000000000000..221d66e05713
--- /dev/null
+++ b/tools/perf/scripts/python/bin/export-to-postgresql-record
@@ -0,0 +1,8 @@
+#!/bin/bash
+
+#
+# export perf data to a postgresql database. Can cover
+# perf ip samples (excluding the tracepoints). No special
+# record requirements, just record what you want to export.
+#
+perf record $@
diff --git a/tools/perf/scripts/python/bin/export-to-postgresql-report b/tools/perf/scripts/python/bin/export-to-postgresql-report
new file mode 100644
index 000000000000..a8fdd15f85bf
--- /dev/null
+++ b/tools/perf/scripts/python/bin/export-to-postgresql-report
@@ -0,0 +1,24 @@
+#!/bin/bash
+# description: export perf data to a postgresql database
+# args: [database name] [columns]
+n_args=0
+for i in "$@"
+do
+    if expr match "$i" "-" > /dev/null ; then
+	break
+    fi
+    n_args=$(( $n_args + 1 ))
+done
+if [ "$n_args" -gt 2 ] ; then
+    echo "usage: export-to-postgresql-report [database name] [columns]"
+    exit
+fi
+if [ "$n_args" -gt 1 ] ; then
+    dbname=$1
+    columns=$2
+    shift 2
+elif [ "$n_args" -gt 0 ] ; then
+    dbname=$1
+    shift
+fi
+perf script $@ -s "$PERF_EXEC_PATH"/scripts/python/export-to-postgresql.py $dbname $columns