ceilometer: postgresql fixes

Cherry picking two ceilometer master changes to address postgresql database
issues:

  https://bugs.launchpad.net/ceilometer/+bug/1241526
  https://review.openstack.org/#/c/49456/

Signed-off-by: Bruce Ashfield <bruce.ashfield@windriver.com>

3 files changed, 457 insertions, 0 deletions

diff --git a/meta-openstack/recipes-devtools/python/python-ceilometer/0001-Fix-for-get_resources-with-postgresql.patch b/meta-openstack/recipes-devtools/python/python-ceilometer/0001-Fix-for-get_resources-with-postgresql.patch
new file mode 100644
index 0000000..52cffb3
--- /dev/null
+++ b/meta-openstack/recipes-devtools/python/python-ceilometer/0001-Fix-for-get_resources-with-postgresql.patch
@@ -0,0 +1,36 @@
+From f28a381b58516018ca35cdf7b4e2879a5bcac6ad Mon Sep 17 00:00:00 2001
+From: Thomas Maddox <thomas.maddox@rackspace.com>
+Date: Mon, 21 Oct 2013 15:55:49 +0000
+Subject: [PATCH 1/2] Fix for get_resources with postgresql
+
+Add max_ts and min_ts to GROUP BY in sub-query, since they need to be aggregated to SELECT them.
+
+Closes-Bug: #1241526
+Change-Id: Ifdd2bc661b5da31bd40d1c3fa1fc442d7417399f
+(cherry picked from commit 0a98159bc9c727a89bd8c15347ac380a21acaa59)
+
+Signed-off-by: Bruce Ashfield <bruce.ashfield@windriver.com>
+---
+ ceilometer/storage/impl_sqlalchemy.py |    6 +++++-
+ 1 file changed, 5 insertions(+), 1 deletion(-)
+
+diff --git a/ceilometer/storage/impl_sqlalchemy.py b/ceilometer/storage/impl_sqlalchemy.py
+index a6e7d307e407..546c0c0e6553 100644
+--- a/ceilometer/storage/impl_sqlalchemy.py
++++ b/ceilometer/storage/impl_sqlalchemy.py
+@@ -361,7 +361,11 @@ class Connection(base.Connection):
+         ).filter(
+             Meter.resource_id == ts_subquery.c.resource_id,
+             Meter.timestamp == ts_subquery.c.max_ts
+-        ).group_by(Meter.resource_id).subquery()
++        ).group_by(
++            ts_subquery.c.resource_id,
++            ts_subquery.c.max_ts,
++            ts_subquery.c.min_ts
++        ).subquery()
+ 
+         query = session.query(
+             Meter,
+-- 
+1.7.10.4
+
diff --git a/meta-openstack/recipes-devtools/python/python-ceilometer/0002-enable-sql-metadata-query.patch b/meta-openstack/recipes-devtools/python/python-ceilometer/0002-enable-sql-metadata-query.patch
new file mode 100644
index 0000000..c96c631
--- /dev/null
+++ b/meta-openstack/recipes-devtools/python/python-ceilometer/0002-enable-sql-metadata-query.patch
@@ -0,0 +1,419 @@
+From 35488d1099c634d88d7e6c262eb9a6636ee2d7d8 Mon Sep 17 00:00:00 2001
+From: Gordon Chung <chungg@ca.ibm.com>
+Date: Wed, 2 Oct 2013 15:45:26 -0400
+Subject: [PATCH 2/2] enable sql metadata query
+
+explode metadata key/values to their own tables/rows (based on type).
+build a key string using dot notation similar to other nosql db
+and filter based on that.
+
+Blueprint: sqlalchemy-metadata-query
+Related-Bug: #1093625
+
+Change-Id: I2076e67b79448f98124a57b62b5bfed7aa8ae2ad
+(cherry picked from commit 1570462507eae1478123de25dbadc64b09c82af3)
+
+Signed-off-by: Bruce Ashfield <bruce.ashfield@windriver.com>
+---
+ ceilometer/storage/impl_sqlalchemy.py              |   79 +++++++++++++++++---
+ .../versions/020_add_metadata_tables.py            |   78 +++++++++++++++++++
+ ceilometer/storage/sqlalchemy/models.py            |   48 ++++++++++++
+ ceilometer/utils.py                                |   24 ++++++
+ doc/source/install/dbreco.rst                      |    4 +-
+ tests/api/v2/test_list_meters_scenarios.py         |    1 +
+ tests/test_utils.py                                |   16 ++++
+ 7 files changed, 239 insertions(+), 11 deletions(-)
+ create mode 100644 ceilometer/storage/sqlalchemy/migrate_repo/versions/020_add_metadata_tables.py
+
+diff --git a/ceilometer/storage/impl_sqlalchemy.py b/ceilometer/storage/impl_sqlalchemy.py
+index 546c0c0e6553..8d321eaaeffe 100644
+--- a/ceilometer/storage/impl_sqlalchemy.py
++++ b/ceilometer/storage/impl_sqlalchemy.py
+@@ -18,10 +18,12 @@
+ """SQLAlchemy storage backend."""
+ 
+ from __future__ import absolute_import
+-
+ import datetime
+ import operator
+ import os
++import types
++
++from sqlalchemy import and_
+ from sqlalchemy import func
+ from sqlalchemy import desc
+ from sqlalchemy.orm import aliased
+@@ -39,6 +41,10 @@ from ceilometer.storage.sqlalchemy.models import AlarmChange
+ from ceilometer.storage.sqlalchemy.models import Base
+ from ceilometer.storage.sqlalchemy.models import Event
+ from ceilometer.storage.sqlalchemy.models import Meter
++from ceilometer.storage.sqlalchemy.models import MetaBool
++from ceilometer.storage.sqlalchemy.models import MetaFloat
++from ceilometer.storage.sqlalchemy.models import MetaInt
++from ceilometer.storage.sqlalchemy.models import MetaText
+ from ceilometer.storage.sqlalchemy.models import Project
+ from ceilometer.storage.sqlalchemy.models import Resource
+ from ceilometer.storage.sqlalchemy.models import Source
+@@ -100,7 +106,40 @@ class SQLAlchemyStorage(base.StorageEngine):
+         return Connection(conf)
+ 
+ 
+-def make_query_from_filter(query, sample_filter, require_meter=True):
++META_TYPE_MAP = {bool: MetaBool,
++                 str: MetaText,
++                 unicode: MetaText,
++                 types.NoneType: MetaText,
++                 int: MetaInt,
++                 long: MetaInt,
++                 float: MetaFloat}
++
++
++def apply_metaquery_filter(session, query, metaquery):
++    """Apply provided metaquery filter to existing query.
++
++    :param session: session used for original query
++    :param query: Query instance
++    :param metaquery: dict with metadata to match on.
++    """
++
++    for k, v in metaquery.iteritems():
++        key = k[9:]  # strip out 'metadata.' prefix
++        try:
++            _model = META_TYPE_MAP[type(v)]
++        except KeyError:
++            raise NotImplementedError(_('Query on %(key)s is of %(value)s '
++                                        'type and is not supported') %
++                                      {"key": k, "value": type(v)})
++        else:
++            meta_q = session.query(_model).\
++                filter(and_(_model.meta_key == key,
++                            _model.value == v)).subquery()
++            query = query.filter_by(id=meta_q.c.id)
++    return query
++
++
++def make_query_from_filter(session, query, sample_filter, require_meter=True):
+     """Return a query dictionary based on the settings in the filter.
+ 
+     :param filter: SampleFilter instance
+@@ -134,7 +173,8 @@ def make_query_from_filter(query, sample_filter, require_meter=True):
+         query = query.filter_by(resource_id=sample_filter.resource)
+ 
+     if sample_filter.metaquery:
+-        raise NotImplementedError(_('metaquery not implemented'))
++        query = apply_metaquery_filter(session, query,
++                                       sample_filter.metaquery)
+ 
+     return query
+ 
+@@ -229,6 +269,21 @@ class Connection(base.Connection):
+             meter.message_signature = data['message_signature']
+             meter.message_id = data['message_id']
+ 
++            if rmetadata:
++                if isinstance(rmetadata, dict):
++                    for key, v in utils.dict_to_keyval(rmetadata):
++                        try:
++                            _model = META_TYPE_MAP[type(v)]
++                        except KeyError:
++                            LOG.warn(_("Unknown metadata type. Key (%s) will "
++                                       "not be queryable."), key)
++                        else:
++                            session.add(_model(id=meter.id,
++                                               meta_key=key,
++                                               value=v))
++
++            session.flush()
++
+     @staticmethod
+     def clear_expired_metering_data(ttl):
+         """Clear expired data from the backend storage system according to the
+@@ -306,8 +361,6 @@ class Connection(base.Connection):
+         # just fail.
+         if pagination:
+             raise NotImplementedError(_('Pagination not implemented'))
+-        if metaquery:
+-            raise NotImplementedError(_('metaquery not implemented'))
+ 
+         # (thomasm) We need to get the max timestamp first, since that's the
+         # most accurate. We also need to filter down in the subquery to
+@@ -331,6 +384,11 @@ class Connection(base.Connection):
+             ts_subquery = ts_subquery.filter(
+                 Meter.sources.any(id=source))
+ 
++        if metaquery:
++            ts_subquery = apply_metaquery_filter(session,
++                                                 ts_subquery,
++                                                 metaquery)
++
+         # Here we limit the samples being used to a specific time period,
+         # if requested.
+         if start_timestamp:
+@@ -409,8 +467,6 @@ class Connection(base.Connection):
+ 
+         if pagination:
+             raise NotImplementedError(_('Pagination not implemented'))
+-        if metaquery:
+-            raise NotImplementedError(_('metaquery not implemented'))
+ 
+         session = sqlalchemy_session.get_session()
+ 
+@@ -434,6 +490,11 @@ class Connection(base.Connection):
+         query_meter = session.query(Meter).\
+             join(subquery_meter, Meter.id == subquery_meter.c.id)
+ 
++        if metaquery:
++            query_meter = apply_metaquery_filter(session,
++                                                 query_meter,
++                                                 metaquery)
++
+         alias_meter = aliased(Meter, query_meter.subquery())
+         query = session.query(Resource, alias_meter).join(
+             alias_meter, Resource.id == alias_meter.resource_id)
+@@ -469,7 +530,7 @@ class Connection(base.Connection):
+ 
+         session = sqlalchemy_session.get_session()
+         query = session.query(Meter)
+-        query = make_query_from_filter(query, sample_filter,
++        query = make_query_from_filter(session, query, sample_filter,
+                                        require_meter=False)
+         if limit:
+             query = query.limit(limit)
+@@ -521,7 +582,7 @@ class Connection(base.Connection):
+         if groupby:
+             query = query.group_by(*group_attributes)
+ 
+-        return make_query_from_filter(query, sample_filter)
++        return make_query_from_filter(session, query, sample_filter)
+ 
+     @staticmethod
+     def _stats_result_to_model(result, period, period_start,
+diff --git a/ceilometer/storage/sqlalchemy/migrate_repo/versions/020_add_metadata_tables.py b/ceilometer/storage/sqlalchemy/migrate_repo/versions/020_add_metadata_tables.py
+new file mode 100644
+index 000000000000..085cd6b8f398
+--- /dev/null
++++ b/ceilometer/storage/sqlalchemy/migrate_repo/versions/020_add_metadata_tables.py
+@@ -0,0 +1,78 @@
++#
++# Copyright 2013 OpenStack Foundation
++# All Rights Reserved.
++#
++# Licensed under the Apache License, Version 2.0 (the "License"); you may
++# not use this file except in compliance with the License. You may obtain
++# a copy of the License at
++#
++#      http://www.apache.org/licenses/LICENSE-2.0
++#
++# Unless required by applicable law or agreed to in writing, software
++# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
++# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
++# License for the specific language governing permissions and limitations
++# under the License.
++import json
++
++from sqlalchemy import Boolean
++from sqlalchemy import Column
++from sqlalchemy import Float
++from sqlalchemy import ForeignKey
++from sqlalchemy import Integer
++from sqlalchemy import MetaData
++from sqlalchemy import String
++from sqlalchemy import Table
++from sqlalchemy import Text
++from sqlalchemy.sql import select
++
++from ceilometer import utils
++
++tables = [('metadata_text', Text, True),
++          ('metadata_bool', Boolean, False),
++          ('metadata_int', Integer, False),
++          ('metadata_float', Float, False)]
++
++
++def upgrade(migrate_engine):
++    meta = MetaData(bind=migrate_engine)
++    meter = Table('meter', meta, autoload=True)
++    meta_tables = {}
++    for t_name, t_type, t_nullable in tables:
++        meta_tables[t_name] = Table(
++            t_name, meta,
++            Column('id', Integer, ForeignKey('meter.id'), primary_key=True),
++            Column('meta_key', String(255), index=True, primary_key=True),
++            Column('value', t_type, nullable=t_nullable),
++            mysql_engine='InnoDB',
++            mysql_charset='utf8',
++        )
++        meta_tables[t_name].create()
++
++    for row in select([meter]).execute():
++        meter_id = row['id']
++        rmeta = json.loads(row['resource_metadata'])
++        for key, v in utils.dict_to_keyval(rmeta):
++            if isinstance(v, basestring) or v is None:
++                meta_tables['metadata_text'].insert().values(id=meter_id,
++                                                             meta_key=key,
++                                                             value=v)
++            elif isinstance(v, bool):
++                meta_tables['metadata_bool'].insert().values(id=meter_id,
++                                                             meta_key=key,
++                                                             value=v)
++            elif isinstance(v, (int, long)):
++                meta_tables['metadata_int'].insert().values(id=meter_id,
++                                                            meta_key=key,
++                                                            value=v)
++            elif isinstance(v, float):
++                meta_tables['metadata_float'].insert().values(id=meter_id,
++                                                              meta_key=key,
++                                                              value=v)
++
++
++def downgrade(migrate_engine):
++    meta = MetaData(bind=migrate_engine)
++    for t in tables:
++        table = Table(t[0], meta, autoload=True)
++        table.drop()
+diff --git a/ceilometer/storage/sqlalchemy/models.py b/ceilometer/storage/sqlalchemy/models.py
+index 45f98cb59553..8f890b3056d8 100644
+--- a/ceilometer/storage/sqlalchemy/models.py
++++ b/ceilometer/storage/sqlalchemy/models.py
+@@ -141,6 +141,54 @@ class Source(Base):
+     id = Column(String(255), primary_key=True)
+ 
+ 
++class MetaText(Base):
++    """Metering text metadata."""
++
++    __tablename__ = 'metadata_text'
++    __table_args__ = (
++        Index('ix_meta_text_key', 'meta_key'),
++    )
++    id = Column(Integer, ForeignKey('meter.id'), primary_key=True)
++    meta_key = Column(String(255), primary_key=True)
++    value = Column(Text)
++
++
++class MetaBool(Base):
++    """Metering boolean metadata."""
++
++    __tablename__ = 'metadata_bool'
++    __table_args__ = (
++        Index('ix_meta_bool_key', 'meta_key'),
++    )
++    id = Column(Integer, ForeignKey('meter.id'), primary_key=True)
++    meta_key = Column(String(255), primary_key=True)
++    value = Column(Boolean)
++
++
++class MetaInt(Base):
++    """Metering integer metadata."""
++
++    __tablename__ = 'metadata_int'
++    __table_args__ = (
++        Index('ix_meta_int_key', 'meta_key'),
++    )
++    id = Column(Integer, ForeignKey('meter.id'), primary_key=True)
++    meta_key = Column(String(255), primary_key=True)
++    value = Column(Integer, default=False)
++
++
++class MetaFloat(Base):
++    """Metering float metadata."""
++
++    __tablename__ = 'metadata_float'
++    __table_args__ = (
++        Index('ix_meta_float_key', 'meta_key'),
++    )
++    id = Column(Integer, ForeignKey('meter.id'), primary_key=True)
++    meta_key = Column(String(255), primary_key=True)
++    value = Column(Float, default=False)
++
++
+ class Meter(Base):
+     """Metering data."""
+ 
+diff --git a/ceilometer/utils.py b/ceilometer/utils.py
+index d5ca45f9654b..a00de72da8c5 100644
+--- a/ceilometer/utils.py
++++ b/ceilometer/utils.py
+@@ -81,3 +81,27 @@ def stringify_timestamps(data):
+     isa_timestamp = lambda v: isinstance(v, datetime.datetime)
+     return dict((k, v.isoformat() if isa_timestamp(v) else v)
+                 for (k, v) in data.iteritems())
++
++
++def dict_to_keyval(value, key_base=None):
++    """Expand a given dict to its corresponding key-value pairs.
++
++    Generated keys are fully qualified, delimited using dot notation.
++    ie. key = 'key.child_key.grandchild_key[0]'
++    """
++    val_iter, key_func = None, None
++    if isinstance(value, dict):
++        val_iter = value.iteritems()
++        key_func = lambda k: key_base + '.' + k if key_base else k
++    elif isinstance(value, (tuple, list)):
++        val_iter = enumerate(value)
++        key_func = lambda k: key_base + '[%d]' % k
++
++    if val_iter:
++        for k, v in val_iter:
++            key_gen = key_func(k)
++            if isinstance(v, dict) or isinstance(v, (tuple, list)):
++                for key_gen, v in dict_to_keyval(v, key_gen):
++                    yield key_gen, v
++            else:
++                yield key_gen, v
+diff --git a/doc/source/install/dbreco.rst b/doc/source/install/dbreco.rst
+index fe6032990ade..249cdc7d92c7 100644
+--- a/doc/source/install/dbreco.rst
++++ b/doc/source/install/dbreco.rst
+@@ -43,8 +43,8 @@ The following is a table indicating the status of each database drivers:
+ Driver             API querying                  API statistics      Alarms
+ ================== ============================= =================== ======
+ MongoDB            Yes                           Yes                 Yes
+-MySQL              Yes, except metadata querying Yes                 Yes
+-PostgreSQL         Yes, except metadata querying Yes                 Yes
++MySQL              Yes                           Yes                 Yes
++PostgreSQL         Yes                           Yes                 Yes
+ HBase              Yes                           Yes, except groupby No
+ DB2                Yes                           Yes                 No
+ ================== ============================= =================== ======
+diff --git a/tests/api/v2/test_list_meters_scenarios.py b/tests/api/v2/test_list_meters_scenarios.py
+index fe2c5b78db8f..3381e15dadc2 100644
+--- a/tests/api/v2/test_list_meters_scenarios.py
++++ b/tests/api/v2/test_list_meters_scenarios.py
+@@ -252,6 +252,7 @@ class TestListMeters(FunctionalTest,
+                          set(['meter.mine']))
+         self.assertEqual(set(r['resource_metadata']['is_public'] for r
+                              in data), set(['False']))
++        # FIXME(gordc): verify no false positive (Bug#1236496)
+ 
+     def test_list_meters_query_string_metadata(self):
+         data = self.get_json('/meters/meter.test',
+diff --git a/tests/test_utils.py b/tests/test_utils.py
+index e5185abb11b0..a14c657554f0 100644
+--- a/tests/test_utils.py
++++ b/tests/test_utils.py
+@@ -70,3 +70,19 @@ class TestUtils(tests_base.TestCase):
+ 
+     def test_decimal_to_dt_with_none_parameter(self):
+         self.assertEqual(utils.decimal_to_dt(None), None)
++
++    def test_dict_to_kv(self):
++        data = {'a': 'A',
++                'b': 'B',
++                'nested': {'a': 'A',
++                           'b': 'B',
++                           },
++                'nested2': [{'c': 'A'}, {'c': 'B'}]
++                }
++        pairs = list(utils.dict_to_keyval(data))
++        self.assertEqual(pairs, [('a', 'A'),
++                                 ('b', 'B'),
++                                 ('nested2[0].c', 'A'),
++                                 ('nested2[1].c', 'B'),
++                                 ('nested.a', 'A'),
++                                 ('nested.b', 'B')])
+-- 
+1.7.10.4
+
diff --git a/meta-openstack/recipes-devtools/python/python-ceilometer_git.bb b/meta-openstack/recipes-devtools/python/python-ceilometer_git.bb
index e727200..e35e67e 100644
--- a/meta-openstack/recipes-devtools/python/python-ceilometer_git.bb
+++ b/meta-openstack/recipes-devtools/python/python-ceilometer_git.bb
@@ -10,6 +10,8 @@ SRCNAME = "ceilometer"
 SRC_URI = "git://github.com/openstack/${SRCNAME}.git;branch=stable/havana \
            file://ceilometer.conf \
            file://ceilometer.init \
+           file://0001-Fix-for-get_resources-with-postgresql.patch \
+           file://0002-enable-sql-metadata-query.patch \
 "
 
 SRCREV="4d15cc05e9d2ada01b90e1d3c15427608cc49f54"