
    D6i:                         d Z ddlmZ ddlmZ ddlZddlZddlZddl	m
Z
 ddlmZ ddlmZ dd	lmZmZmZ  e       Z G d
 de
      Zd Zd Z G d d      Z G d d      Z e       Zej5                          d Zy)a#  
SQLAlchemy-Utils provides way of automatically calculating aggregate values of
related models and saving them to parent model.

This solution is inspired by RoR counter cache,
`counter_culture`_ and `stackoverflow reply by Michael Bayer`_.

Why?
----

Many times you may have situations where you need to calculate dynamically some
aggregate value for given model. Some simple examples include:

- Number of products in a catalog
- Average rating for movie
- Latest forum post
- Total price of orders for given customer

Now all these aggregates can be elegantly implemented with SQLAlchemy
column_property_ function. However when your data grows calculating these
values on the fly might start to hurt the performance of your application. The
more aggregates you are using the more performance penalty you get.

This module provides way of calculating these values automatically and
efficiently at the time of modification rather than on the fly.


Features
--------

* Automatically updates aggregate columns when aggregated values change
* Supports aggregate values through arbitrary number levels of relations
* Highly optimized: uses single query per transaction per aggregate column
* Aggregated columns can be of any data type and use any selectable scalar
  expression


.. _column_property:
    https://docs.sqlalchemy.org/en/latest/orm/mapped_sql_expr.html#using-column-property
.. _counter_culture: https://github.com/magnusvk/counter_culture
.. _stackoverflow reply by Michael Bayer:
    https://stackoverflow.com/a/13765857/520932


Simple aggregates
-----------------

::

    from sqlalchemy_utils import aggregated


    class Thread(Base):
        __tablename__ = 'thread'
        id = sa.Column(sa.Integer, primary_key=True)
        name = sa.Column(sa.Unicode(255))

        @aggregated('comments', sa.Column(sa.Integer))
        def comment_count(self):
            return sa.func.count('1')

        comments = sa.orm.relationship(
            'Comment',
            backref='thread'
        )


    class Comment(Base):
        __tablename__ = 'comment'
        id = sa.Column(sa.Integer, primary_key=True)
        content = sa.Column(sa.UnicodeText)
        thread_id = sa.Column(sa.Integer, sa.ForeignKey(Thread.id))


    thread = Thread(name='SQLAlchemy development')
    thread.comments.append(Comment('Going good!'))
    thread.comments.append(Comment('Great new features!'))

    session.add(thread)
    session.commit()

    thread.comment_count  # 2



Custom aggregate expressions
----------------------------

Aggregate expression can be virtually any SQL expression not just a simple
function taking one parameter. You can try things such as subqueries and
different kinds of functions.

In the following example we have a Catalog of products where each catalog
knows the net worth of its products.

::


    from sqlalchemy_utils import aggregated


    class Catalog(Base):
        __tablename__ = 'catalog'
        id = sa.Column(sa.Integer, primary_key=True)
        name = sa.Column(sa.Unicode(255))

        @aggregated('products', sa.Column(sa.Integer))
        def net_worth(self):
            return sa.func.sum(Product.price)

        products = sa.orm.relationship('Product')


    class Product(Base):
        __tablename__ = 'product'
        id = sa.Column(sa.Integer, primary_key=True)
        name = sa.Column(sa.Unicode(255))
        price = sa.Column(sa.Numeric)

        catalog_id = sa.Column(sa.Integer, sa.ForeignKey(Catalog.id))


Now the net_worth column of Catalog model will be automatically whenever:

* A new product is added to the catalog
* A product is deleted from the catalog
* The price of catalog product is changed


::


    from decimal import Decimal


    product1 = Product(name='Some product', price=Decimal(1000))
    product2 = Product(name='Some other product', price=Decimal(500))


    catalog = Catalog(
        name='My first catalog',
        products=[
            product1,
            product2
        ]
    )
    session.add(catalog)
    session.commit()

    session.refresh(catalog)
    catalog.net_worth  # 1500

    session.delete(product2)
    session.commit()
    session.refresh(catalog)

    catalog.net_worth  # 1000

    product1.price = 2000
    session.commit()
    session.refresh(catalog)

    catalog.net_worth  # 2000




Multiple aggregates per class
-----------------------------

Sometimes you may need to define multiple aggregate values for same class. If
you need to define lots of relationships pointing to same class, remember to
define the relationships as viewonly when possible.


::


    from sqlalchemy_utils import aggregated


    class Customer(Base):
        __tablename__ = 'customer'
        id = sa.Column(sa.Integer, primary_key=True)
        name = sa.Column(sa.Unicode(255))

        @aggregated('orders', sa.Column(sa.Integer))
        def orders_sum(self):
            return sa.func.sum(Order.price)

        @aggregated('invoiced_orders', sa.Column(sa.Integer))
        def invoiced_orders_sum(self):
            return sa.func.sum(Order.price)

        orders = sa.orm.relationship('Order')

        invoiced_orders = sa.orm.relationship(
            'Order',
            primaryjoin=
                'sa.and_(Order.customer_id == Customer.id, Order.invoiced)',
            viewonly=True
        )


    class Order(Base):
        __tablename__ = 'order'
        id = sa.Column(sa.Integer, primary_key=True)
        name = sa.Column(sa.Unicode(255))
        price = sa.Column(sa.Numeric)
        invoiced = sa.Column(sa.Boolean, default=False)
        customer_id = sa.Column(sa.Integer, sa.ForeignKey(Customer.id))


Many-to-Many aggregates
-----------------------

Aggregate expressions also support many-to-many relationships. The usual use
scenarios includes things such as:

1. Friend count of a user
2. Group count where given user belongs to

::


        user_group = sa.Table('user_group', Base.metadata,
            sa.Column('user_id', sa.Integer, sa.ForeignKey('user.id')),
            sa.Column('group_id', sa.Integer, sa.ForeignKey('group.id'))
        )


        class User(Base):
            __tablename__ = 'user'
            id = sa.Column(sa.Integer, primary_key=True)
            name = sa.Column(sa.Unicode(255))

            @aggregated('groups', sa.Column(sa.Integer, default=0))
            def group_count(self):
                return sa.func.count('1')

            groups = sa.orm.relationship(
                'Group',
                backref='users',
                secondary=user_group
            )


        class Group(Base):
            __tablename__ = 'group'
            id = sa.Column(sa.Integer, primary_key=True)
            name = sa.Column(sa.Unicode(255))



        user = User(name='John Matrix')
        user.groups = [Group(name='Group A'), Group(name='Group B')]

        session.add(user)
        session.commit()

        session.refresh(user)
        user.group_count  # 2


Multi-level aggregates
----------------------

Aggregates can span across multiple relationships. In the following example
each Catalog has a net_worth which is the sum of all products in all
categories.


::


    from sqlalchemy_utils import aggregated


    class Catalog(Base):
        __tablename__ = 'catalog'
        id = sa.Column(sa.Integer, primary_key=True)
        name = sa.Column(sa.Unicode(255))

        @aggregated('categories.products', sa.Column(sa.Integer))
        def net_worth(self):
            return sa.func.sum(Product.price)

        categories = sa.orm.relationship('Category')


    class Category(Base):
        __tablename__ = 'category'
        id = sa.Column(sa.Integer, primary_key=True)
        name = sa.Column(sa.Unicode(255))

        catalog_id = sa.Column(sa.Integer, sa.ForeignKey(Catalog.id))

        products = sa.orm.relationship('Product')


    class Product(Base):
        __tablename__ = 'product'
        id = sa.Column(sa.Integer, primary_key=True)
        name = sa.Column(sa.Unicode(255))
        price = sa.Column(sa.Numeric)

        category_id = sa.Column(sa.Integer, sa.ForeignKey(Category.id))


Examples
--------

Average movie rating
^^^^^^^^^^^^^^^^^^^^

::


    from sqlalchemy_utils import aggregated


    class Movie(Base):
        __tablename__ = 'movie'
        id = sa.Column(sa.Integer, primary_key=True)
        name = sa.Column(sa.Unicode(255))

        @aggregated('ratings', sa.Column(sa.Numeric))
        def avg_rating(self):
            return sa.func.avg(Rating.stars)

        ratings = sa.orm.relationship('Rating')


    class Rating(Base):
        __tablename__ = 'rating'
        id = sa.Column(sa.Integer, primary_key=True)
        stars = sa.Column(sa.Integer)

        movie_id = sa.Column(sa.Integer, sa.ForeignKey(Movie.id))


    movie = Movie('Terminator 2')
    movie.ratings.append(Rating(stars=5))
    movie.ratings.append(Rating(stars=4))
    movie.ratings.append(Rating(stars=3))
    session.add(movie)
    session.commit()

    movie.avg_rating  # 4



TODO
----

* Special consideration should be given to `deadlocks`_.


.. _deadlocks:
    https://mina.naguib.ca/blog/2010/11/22/postgresql-foreign-key-deadlocks.html

    )defaultdict)WeakKeyDictionaryN)declared_attr)_FunctionGenerator   )get_column_key)chained_joinpath_to_relationshipsselect_correlated_expressionc                   $     e Zd Z fdZd Z xZS )AggregatedAttributec                 j    t        |   |g|i | |j                  | _        || _        || _        y N)super__init____doc__columnrelationship)selffgetr   r   argskwargs	__class__s         ^/home/azureuser/techstart-app/venv/lib/python3.12/site-packages/sqlalchemy_utils/aggregates.pyr   zAggregatedAttribute.__init__  s4    ///||(    c                     | j                   | j                  | j                  f}|t        vr|gt        |<   | j                  S t        |   j	                  |       | j                  S r   )r   r   r   aggregated_attrsappend)descr   clsvalues       r   __get__zAggregatedAttribute.__get__  s]    D--t{{;&&%*GS! {{ S!((/{{r   )__name__
__module____qualname__r   r"   __classcell__)r   s   @r   r   r     s    )r   r   c                 l   | j                   }| j                  |d   d   }|d   d   }n|d   d   }|d   d   }t        | j                  |      }g }|D ]  }	 |j	                  t        ||               |r|j                  |      S y # t        j                  j                  j                  $ r Y ^w xY w)Nr   r   )local_remote_pairs	secondaryr   mapperr   getattrsaormexcObjectDeletedErrorin_)propobjectspairsparent_columnfetched_columnkeyvaluesobjs           r   local_conditionr9     s    ##E~~!aq!aq!
n
5CF 	MM'#s+,   ((  vvzz,, 		s   B		'B32B3c                     t        | t        j                  j                  j                        r| S t        | t
              r% | t        j                  j                  d            S  | |      S )N1)
isinstancer,   sqlvisitors	Visitabler   text)exprclass_s     r   aggregate_expressionrC     sL    $112	D,	-BFFKK$%%F|r   c                   (    e Zd Zd Zed        Zd Zy)AggregatedValuec                     || _         || _        || _        t        t	        t        ||                  | _        t        ||      | _        y r   )	rB   attrpathlistreversedr
   relationshipsrC   rA   )r   rB   rG   rH   rA   s        r   r   zAggregatedValue.__init__  s?    		!(+@v+N"OP(v6	r   c                     t        | j                  | j                  | j                  | j                  d   j
                  j                        }|j                         S )Nr   )r   rB   rA   rH   rK   r*   scalar_subquery)r   querys     r   aggregate_queryzAggregatedValue.aggregate_query  sI    ,KKDIIt/A/A!/D/K/K/R/R
 $$&&r   c                    | j                   j                  }|j                         j                  | j                  | j
                  i      }t        | j                        dk(  r9| j                  d   j                  }t        ||      }||j                  |      S y | j                  d   j                  }|j                  }|d   d   }|d   d   }	t        | j                  d   j                  |      }|j|j                  |j                  t        j                  |	      j                  t!        t#        | j                               j                  |                  S y )Nr   r   )rB   	__table__updater7   rG   rO   lenrK   propertyr9   wherer(   r0   r,   selectselect_fromr	   rJ   )
r   r2   tablerN   r1   	condition	property_remote_pairslocalremotes
             r   update_queryzAggregatedValue.update_query  s9   %%%%tyy$2F2F&GHt!!"a'%%b)22D'g6I${{9-- % **2.77I$77L OA&E!!_Q'F'(:(:1(=(F(FPI${{II		&)$\8D<N<N3O%PQy)  %r   N)r#   r$   r%   r   rU   rO   r_    r   r   rE   rE     s     7 ' 'r   rE   c                   *    e Zd Zd Zd Zd Zd Zd Zy)AggregationManagerc                 $    | j                          y r   )resetr   s    r   r   zAggregationManager.__init__  s    

r   c                 ,    t        t              | _        y r   )r   rI   generator_registryre   s    r   rd   zAggregationManager.reset  s    "-d"3r   c                 $   t         j                  j                  t         j                  j                  d| j
                         t         j                  j                  t         j                  j                  j                  d| j                         y )Nafter_configuredafter_flush)	r,   eventlistenr-   Mapperupdate_generator_registrysessionSessionconstruct_aggregate_queriesre   s    r   register_listenersz%AggregationManager.register_listeners  sU    
FFMM-t/M/M	
 	FFNN""M43S3S	
r   c           
          t         j                         D ]f  \  }}|D ]\  \  }}}t        ||| ||            }|j                  d   j                  j
                  }| j                  |   j                  |       ^ h y )N)rB   rG   rH   rA   r   )r   itemsrE   rK   r*   rB   rg   r   )r   rB   attrsrA   rH   r   r!   r6   s           r   rn   z,AggregationManager.update_generator_registry  s    -335 	;MFE&+ ;"dF'!TV ))!,33::'',33E:;	;r   c                 6   t        t              }|D ]4  }| j                  D ]#  }t        ||      s||   j	                  |       % 6 |j                         D ]>  \  }}| j                  |   D ]'  }|j                  |      }||j                  |       ) @ y r   )r   rI   rg   r<   r   rt   r_   execute)	r   ro   ctxobject_dictr8   rB   r2   aggregate_valuerN   s	            r   rq   z.AggregationManager.construct_aggregate_queries  s    !$' 	4C11 4c6*'..s34	4
  +002 	+OFG#'#:#:6#B +'44W=$OOE*+	+r   N)r#   r$   r%   r   rd   rr   rn   rq   r`   r   r   rb   rb     s    4
;+r   rb   c                       fd}|S )a  
    Decorator that generates an aggregated attribute. The decorated function
    should return an aggregate select expression.

    :param relationship:
        Defines the relationship of which the aggregate is calculated from.
        The class needs to have given relationship in order to calculate the
        aggregate.
    :param column:
        SQLAlchemy Column object. The column definition of this aggregate
        attribute.
    c                     t        |       S r   )r   )funcr   r   s    r   wrapszaggregated.<locals>.wraps  s    "4v>>r   r`   )r   r   r~   s   `` r   
aggregatedr   
  s    ? Lr   )r   collectionsr   weakrefr   
sqlalchemyr,   sqlalchemy.eventsqlalchemy.ormsqlalchemy.ext.declarativer   sqlalchemy.sql.functionsr   functions.ormr   rK   r	   r
   r   r   r   r9   rC   rE   rb   managerrr   r   r`   r   r   <module>r      s   iV $ %    4 7 )  %& -  ),/ /d#+ #+L 
    r   