Automatic configuration of SQLAlchemy driver in DevStack

This patch adds a new type of collector "sqlalchemy".
To enable it, add the following line into DevStack local.conf:

    OSPROFILER_COLLECTOR=sqlalchemy

OSProfiler DevStack plugin creates database for traces and
configures all services to use it.

SQLAlchemy driver is extend to be able to list traces.

Zuul job is added to run Tempest tests with sqlalchemy driver enabled.

Change-Id: Ia943d311d78f6dfd2e6bb884c8dca725b3b2a36b

5 files changed, 59 insertions, 14 deletions

diff --git a/.zuul.yaml b/.zuul.yaml
index 91c1dee..8799506 100644
--- a/.zuul.yaml
+++ b/.zuul.yaml
@@ -16,11 +16,13 @@
         - openstack-tox-functional
         - openstack-tox-functional-py36
         - tempest-smoke-py3-osprofiler-redis
+        - tempest-smoke-py3-osprofiler-sqlalchemy
     gate:
       jobs:
         - openstack-tox-functional
         - openstack-tox-functional-py36
         - tempest-smoke-py3-osprofiler-redis
+        - tempest-smoke-py3-osprofiler-sqlalchemy
 
 - job:
     name: tempest-smoke-py3-osprofiler-redis
@@ -28,7 +30,7 @@
     voting: false
     post-run: playbooks/osprofiler-post.yaml
     description: |
-      Run full tempest on py3 with profiling enabled
+      Run full tempest on py3 with profiling enabled (redis driver)
     required-projects:
       - openstack/osprofiler
     vars:
@@ -38,3 +40,20 @@
         OSPROFILER_HMAC_KEYS: SECRET_KEY
       devstack_plugins:
         osprofiler: https://opendev.org/openstack/osprofiler
+
+- job:
+    name: tempest-smoke-py3-osprofiler-sqlalchemy
+    parent: tempest-full-py3
+    voting: false
+    post-run: playbooks/osprofiler-post.yaml
+    description: |
+      Run full tempest on py3 with profiling enabled (sqlalchemy driver)
+    required-projects:
+      - openstack/osprofiler
+    vars:
+      tox_envlist: smoke
+      devstack_localrc:
+        OSPROFILER_COLLECTOR: sqlalchemy
+        OSPROFILER_HMAC_KEYS: SECRET_KEY
+      devstack_plugins:
+        osprofiler: https://opendev.org/openstack/osprofiler
diff --git a/devstack/README.rst b/devstack/README.rst
index da83baf..7469f79 100644
--- a/devstack/README.rst
+++ b/devstack/README.rst
@@ -17,6 +17,7 @@ that control this:
   * ``<empty>`` - default messaging driver is used
   * ``redis`` - Redis is installed
   * ``jaeger`` - Jaeger is installed
+  * ``sqlalchemy`` - SQLAlchemy driver is installed
 
   The default value of ``OSPROFILER_CONNECTION_STRING`` is set automatically
   depending on ``OSPROFILER_COLLECTOR`` value.
@@ -31,6 +32,7 @@ that control this:
   * ``mongodb://host:port`` - use MongoDB as trace storage
   * ``loginsight://username:password@host`` - use LogInsight as trace collector/storage
   * ``jaeger://host:port`` - use Jaeger as trace collector
+  * ``mysql+pymysql://username:password@host/profiler?charset=utf8`` - use SQLAlchemy driver with MySQL database
 
 
 To configure DevStack and enable OSProfiler edit ``${DEVSTACK_DIR}/local.conf``
diff --git a/devstack/lib/osprofiler b/devstack/lib/osprofiler
index 8a7351b..aaa67f3 100644
--- a/devstack/lib/osprofiler
+++ b/devstack/lib/osprofiler
@@ -112,9 +112,15 @@ function install_osprofiler_collector() {
     elif [ "$OSPROFILER_COLLECTOR" == "mongodb" ]; then
         install_mongodb
         OSPROFILER_CONNECTION_STRING=${OSPROFILER_CONNECTION_STRING:-"mongodb://localhost:27017"}
+    elif [ "$OSPROFILER_COLLECTOR" == "sqlalchemy" ]; then
+        local db=`database_connection_url osprofiler`
+        OSPROFILER_CONNECTION_STRING=${OSPROFILER_CONNECTION_STRING:-${db}}
+        recreate_database osprofiler
     else
         die $LINENO "OSProfiler collector $OSPROFILER_COLLECTOR is not supported"
     fi
+
+    echo ${OSPROFILER_CONNECTION_STRING} > $HOME/.osprofiler_connection_string
 }
 
 function configure_osprofiler() {
diff --git a/osprofiler/drivers/sqlalchemy_driver.py b/osprofiler/drivers/sqlalchemy_driver.py
index a8cefcf..daab1d0 100644
--- a/osprofiler/drivers/sqlalchemy_driver.py
+++ b/osprofiler/drivers/sqlalchemy_driver.py
@@ -102,6 +102,23 @@ class SQLAlchemyDriver(base.Driver):
             LOG.exception("Can not store osprofiler tracepoint {} "
                           "(base_id {})".format(trace_id, base_id))
 
+    def list_traces(self, fields=None):
+        try:
+            from sqlalchemy.sql import select
+        except ImportError:
+            raise exc.CommandError(
+                "To use this command, you should install 'SQLAlchemy'")
+        stmt = select([self._data_table])
+        seen_ids = set()
+        result = []
+        traces = self._conn.execute(stmt).fetchall()
+        for trace in traces:
+            if trace["base_id"] not in seen_ids:
+                seen_ids.add(trace["base_id"])
+                result.append({key: value for key, value in trace.items()
+                               if key in fields})
+        return result
+
     def get_report(self, base_id):
         try:
             from sqlalchemy.sql import select
diff --git a/playbooks/osprofiler-post.yaml b/playbooks/osprofiler-post.yaml
index 1455aad..7328c74 100644
--- a/playbooks/osprofiler-post.yaml
+++ b/playbooks/osprofiler-post.yaml
@@ -2,7 +2,7 @@
   vars:
     osprofiler_traces_dir: '/opt/stack/osprofiler-traces'
   tasks:
-    - name: Create OSProfiler home directory
+    - name: Create directory for traces
       become: True
       become_user: stack
       file:
@@ -11,8 +11,15 @@
         owner: stack
         group: stack
 
+    - name: Read connection string from a file
+      command: "cat /opt/stack/.osprofiler_connection_string"
+      register: osprofiler_connection_string
+
+    - debug:
+        msg: "OSProfiler connection string is: {{ osprofiler_connection_string.stdout }}"
+
     - name: Get list of traces
-      command: osprofiler trace list --connection-string redis://localhost:6379
+      command: "osprofiler trace list --connection-string {{ osprofiler_connection_string.stdout }}"
       become: True
       become_user: stack
       register: osprofiler_trace_list
@@ -20,31 +27,25 @@
     - debug:
         msg: "{{ osprofiler_trace_list }}"
 
-    - name: Save traces to file
+    - name: Save traces to files
       shell: |
-        osprofiler trace list --connection-string redis://localhost:6379 > {{ osprofiler_traces_dir }}/trace_list.txt
+        osprofiler trace list --connection-string {{ osprofiler_connection_string.stdout }} > {{ osprofiler_traces_dir }}/trace_list.txt
         cat {{ osprofiler_traces_dir }}/trace_list.txt | tail -n +4 | head -n -1 | awk '{print $2}' > {{ osprofiler_traces_dir }}/trace_ids.txt
 
         while read p; do
-          osprofiler trace show --connection-string redis://localhost:6379 --html $p > {{ osprofiler_traces_dir }}/trace-$p.html
+          osprofiler trace show --connection-string {{ osprofiler_connection_string.stdout }} --html $p > {{ osprofiler_traces_dir }}/trace-$p.html
         done < {{ osprofiler_traces_dir }}/trace_ids.txt
       become: True
       become_user: stack
 
-    - name: Gzip results files
+    - name: Gzip trace files
       become: yes
       become_user: stack
       shell: "gzip * -9 -q | true"
       args:
         chdir: '{{ osprofiler_traces_dir }}'
 
-    - name: List all files
-      shell:
-        cmd: "ls -al {{ osprofiler_traces_dir }}"
-      become: True
-      become_user: stack
-
-    - name: Copy osprofiler output
+    - name: Sync trace files to Zuul
       become: yes
       synchronize:
         src: "{{ osprofiler_traces_dir }}"