Example #1
0
def user_vote_change_comments(period=None, user=None):
    rel = Vote.rel(Account, Comment)
    type = tdb.rel_types_id[rel._type_id]
    # rt = rel table
    # dt = data table
    rt, account_tt, comment_tt, dt = type.rel_table

    aliases = tdb.alias_generator()
    author_dt = dt.alias(aliases.next())

    amount = sa.cast(rt.c.name, sa.Integer)
    cols = [
        author_dt.c.value,
        sa.func.sum(sa.case([(amount > 0, amount)], else_=0)),
        sa.func.sum(sa.case([(amount < 0, amount * -1)], else_=0)),
    ]

    query = sa.and_(
        author_dt.c.thing_id == rt.c.rel_id, author_dt.c.key == "author_id", comment_tt.c.thing_id == rt.c.thing2_id
    )
    if period is not None:
        earliest = datetime.now(g.tz) - timedelta(0, period)
        query.clauses.extend((rt.c.date >= earliest, comment_tt.c.date >= earliest))
    if user is not None:
        query.clauses.append(author_dt.c.value == str(user._id))

    s = sa.select(cols, query, group_by=author_dt.c.value)

    rows = s.execute().fetchall()
    return [(int(r[0]), (r[1], r[2])) for r in rows]
Example #2
0
def user_karma_adjustments(period=None, user=None):
    acct_info = tdb.types_id[Account._type_id]
    acct_thing, acct_data = acct_info.thing_table, acct_info.data_table[0]
    adj_info = tdb.types_id[KarmaAdjustment._type_id]
    adj_thing, adj_data = adj_info.thing_table, adj_info.data_table[0]

    aliases = tdb.alias_generator()
    adj_data_2 = adj_data.alias(aliases.next())

    amount = sa.cast(adj_data_2.c.value, sa.Integer)
    cols = [
        adj_data.c.value,
        sa.func.sum(sa.case([(amount > 0, amount)], else_=0)),
        sa.func.sum(sa.case([(amount < 0, amount * -1)], else_=0)),
    ]

    query = sa.and_(
        adj_data.c.thing_id == adj_thing.c.thing_id,
        adj_data.c.key == "account_id",
        adj_data.c.thing_id == adj_data_2.c.thing_id,
        adj_data_2.c.key == "amount",
    )
    if period is not None:
        earliest = datetime.now(g.tz) - timedelta(0, period)
        query.clauses.append(adj_thing.c.date >= earliest)
    if user is not None:
        query.clauses.append(adj_data.c.value == str(user._id))

    s = sa.select(cols, query, group_by=adj_data.c.value)

    rows = s.execute().fetchall()
    return [(int(r[0]), (r[1], r[2])) for r in rows]
Example #3
0
def query_person_contacts():
    return (
        DBSession.query(
            Person.id.label('person_id'),
            func.array_to_string(
                func.array_agg(
                    case([(Contact.contact_type == 'phone', Contact.contact)])
                ),
                ', '
            ).label('phone'),
            func.array_to_string(
                func.array_agg(
                    case([(Contact.contact_type == 'email', Contact.contact)])
                ),
                ', '
            ).label('email'),
            func.array_to_string(
                func.array_agg(
                    case([(Contact.contact_type == 'skype', Contact.contact)])
                ),
                ', '
            ).label('skype'),
        )
        .join(Contact, Person.contacts)
        .group_by(Person.id)
    )
Example #4
0
 def _manage_position_gap(self, connection, session_objs, tree_id, target, size):
   """Manages spaces in the tree identified by ``tree_id`` by changing the
   values of the left and right columns by ``size`` after the given
   ``target`` point."""
   options = self._tree_options
   connection.execute(
     options.table.update()
       .values({
         options.left_field:  sqlalchemy.case(
           [(options.left_field > target, options.left_field + size)],
           else_ = options.left_field),
         options.right_field: sqlalchemy.case(
           [(options.right_field > target, options.right_field + size)],
           else_ = options.right_field),
       })
       .where(
         (options.tree_id_field == tree_id) &
         ((options.left_field  > target) |
          (options.right_field > target))
       ))
   for obj in session_objs:
     obj_tree_id = getattr(obj, options.tree_id_field.name)
     if obj_tree_id != tree_id: continue
     obj_left    = getattr(obj, options.left_field.name)
     obj_right   = getattr(obj, options.right_field.name)
     if obj_left  > target:
       setattr(obj, options.left_field.name,  obj_left  + size)
     if obj_right > target:
       setattr(obj, options.right_field.name, obj_right + size)
Example #5
0
def before_insert(mapper, connection, instance):
    if not instance.parent:
        instance.left = 1
        instance.right = 2
    else:
        personnel = mapper.mapped_table
        right_most_sibling = connection.scalar(
            select([personnel.c.rgt]).
                where(personnel.c.emp == instance.parent.emp)
        )

        connection.execute(
            personnel.update(
                personnel.c.rgt >= right_most_sibling).values(
                    lft=case(
                        [(personnel.c.lft > right_most_sibling,
                            personnel.c.lft + 2)],
                        else_=personnel.c.lft
                    ),
                    rgt=case(
                        [(personnel.c.rgt >= right_most_sibling,
                                personnel.c.rgt + 2)],
                            else_=personnel.c.rgt
                      )
            )
        )
        instance.left = right_most_sibling
        instance.right = right_most_sibling + 1
    def _ops_for_date_range(self, balance_uids, start_date, end_date, tags = [], change_categories = []):
        model = self.get_sa_model()
        db = self.get_sa_session()

        conditions = [or_(*[model.BalanceChange.balance_uid == balance_uid for balance_uid in balance_uids])]
        if start_date:
            conditions.append(model.BalanceChange.occurred_on >= start_date)

        if end_date:
            conditions.append(model.BalanceChange.occurred_on <= end_date)

        if isinstance(tags, list) and len(tags) > 0:
            conditions.extend([model.BalanceChange.tags.any(tag=tag.strip().lower()) for tag in tags if tag is not None and tag.strip() != ''])

        if isinstance(change_categories, list) and len(change_categories) > 0:
            conditions.extend([model.BalanceChange.change_category_uid == value.strip() for value in change_categories if value is not None and value.strip() != ''])

        try:
            summary = db.execute(select([
                    func.coalesce(func.sum(
                        case([[model.BalanceChange.amount<0, model.BalanceChange.amount]], else_=0).label("expenses"))), 
                    func.coalesce(func.sum(
                        case([[model.BalanceChange.amount>0, model.BalanceChange.amount]], else_=0).label("incomes")))
                ], 
                and_(*conditions),
                from_obj=[model.balance_changes_table])).fetchone()
            
            return {
                "expenses": summary[0],
                "incomes": summary[1],
            }
        except:
            log.error(_("Can't get summary"), exc_info=1)
            return 0
Example #7
0
    def generate():
        users = db_session.query(User.first_name,
                                 User.last_name,
                                 case([(User.email != None,
                                 User.email)], else_=''),
                                 case([(User.dni != None,
                                 User.dni)], else_=''),
                                 case([(User.phone != None,
                                 User.phone)], else_=''),
                                 case([(User.enabled == '1', 'yes')],
                                 else_='no'),
                                 case([(Department.name != None,
                                 Department.name)], else_=''),
                                 func.date_format(User.created_at,
                                 '%Y-%m-%d %H:%i:%s')
                                 ).outerjoin(Department)

        row_columns = ('Name', 'Lastname', 'Email', 'Dni',
                       'Phone', 'Complete', 'Department', 'Created_at',)

        if enabled:
            if enabled == '1':
                users = users.filter(User.enabled == True)
            if enabled == '0':
                users = users.filter(User.enabled == False)

        yield ','.join(row_columns) + '\n'
        for row in users.all():
            yield ','.join(row) + '\n'
Example #8
0
    def _unparent_and_close_gap(self, node, connection, table):
        gap_size = node.rgt - node.lft + 1
        lft_rgt_change = gap_lft = node.lft - 1
        maxrgt = connection.scalar(func.max(table.c.rgt))

        connection.execute(
            table.update().values(
                lft = case([
                        (and_(table.c.lft >= node.lft, table.c.lft <= node.rgt), 
                                table.c.lft - lft_rgt_change),
                        (table.c.lft > gap_lft,
                                table.c.lft - gap_size),
                    ],
                    else_ = table.c.lft
                ),
                rgt = case([
                        (and_(table.c.rgt >= node.lft, table.c.rgt <= node.rgt),
                                table.c.rgt - lft_rgt_change),
                        (table.c.rgt > gap_lft,
                                table.c.rgt - gap_size)
                    ],
                    else_ = table.c.rgt
                ),
                parent_id = case([(table.c.id == node.id, None)],
                        else_ = table.c.parent_id
                )
            )
        )
        node.lft = maxrgt - 1
        node.rgt = maxrgt
Example #9
0
    def reports(self):
        c.title = "Sales Dashboard"
        c.items = c.user.programs

        if len(c.items) > 0:
            nullToken = c.nullToken
            
            """Each row is (program_id, date, numPurchases, numAffiliatePurchases, affiliateTotal, purchaseTotal """
            #JOIN was unnecessary
            #c.orders = Session_.query(Order.product_id, func.datediff(Order.date, c.lowerDate), func.count('*').label('total'), func.sum(Order.isReturned, type_=Integer).label('numReturns'), func.count(Order.affiliate_user_id).label('numAffiliatePurchases')).join((Product, and_(Order.seller_user_id==c.user.id, Order.product_id==Product.id))).filter(Order.date >= c.lowerDate).filter(Order.date < c.upperDate).group_by(Order.product_id).group_by(Order.date).all()
            c.orders = Session_.query(Order.program_id,
                                      func.datediff(Order.date, c.lowerDate),
                                      func.count('*').label('numPurchases'), 
                                      func.count(Order.affiliate_user_id).label('numAffiliatePurchases'),
                                      func.sum(case([(Order.affiliate_user_id != None, Order.amount)], else_=0)).label('affiliateTotal'),
                                      func.sum(Order.amount).label('purchaseTotal'),
                                      ).filter(Order.merchant_user_id==c.user.id).filter(Order.date >= c.lowerDate).filter(Order.date <= c.upperDate).group_by(Order.program_id).group_by(Order.date).all()
            #c.days = Session_.query(func.datediff(Transaction.date, c.lowerDate), func.sum(Transaction.amount)).filter(Transaction.user_id==c.user.id).filter(Transaction.date >= c.lowerDate).filter(Transaction.date < c.upperDate).group_by(Transaction.date).all()
 
            """SINGLE PRODUCT FOR OWNER"""
            #c.orders = Session_.query(Order.product_id, func.datediff(Order.date, c.lowerDate), func.count('*').label('total'), func.sum(Order.isReturned, type_=Integer).label('numReturns'), func.count(Order.affiliate_user_id).label('numAffiliatePurchases')).filter(Order.product_id==c.product.id).filter(Order.date >= c.lowerDate).filter(Order.date < c.upperDate).group_by(Order.product_id).group_by(Order.date).all()
            #c.impressions = Session_.query(Impression.product_id, func.datediff(Impression.date, c.lowerDate), func.count('*').label('total'), func.sum(case([(Impression.affiliate_ts != nullToken, 1)],else_=0)), func.sum(case([(Impression.order_ts != nullToken, 1)],else_=0)), func.sum(case([(and_(Impression.affiliate_ts != nullToken, Impression.order_ts != nullToken), 1)],else_=0)).label('buyConversions'), func.sum(case([(and_(Impression.purchase_ts != nullToken, Impression.order_ts != nullToken), 1)],else_=0)).label('purchaseConversions')).filter(Impression.product_id==c.product.id).filter(Impression.date >= c.lowerDate).filter(Impression.date < c.upperDate).group_by(Impression.product_id).group_by(Impression.date).all()
            
            
            """Each row is (product_id, date, affiliateViews, conversionTime)"""
            c.impressions = Session_.query(Impression.program_id, 
                                           func.datediff(Impression.date, c.lowerDate), 
                                           func.sum(case([(Impression.affiliate_ts != nullToken, 1)],else_=0)), 
                                           func.avg(case([(and_(Impression.purchase_ts != nullToken, Impression.affiliate_ts != nullToken), func.time_to_sec(func.timediff(Impression.purchase_ts,Impression.affiliate_ts)))],else_=0), 
        
                                    ).label('purchaseConversions')).join((Program, and_(c.user.id==Program.merchant_user_id, Impression.program_id==Program.id))).filter(Impression.date >= c.lowerDate).filter(Impression.date <= c.upperDate).group_by(Impression.program_id).group_by(Impression.date).all()
            logging.info(c.impressions)
            self.__Temp(c)
        return render('/account/accountSummary.mak')
    def testcase_with_dict(self):
        query = select(
            [
                case(
                    {
                        info_table.c.pk < 3: 'lessthan3',
                        info_table.c.pk >= 3: 'gt3',
                    }, else_='other'),
                info_table.c.pk, info_table.c.info
            ],
            from_obj=[info_table])
        assert query.execute().fetchall() == [
            ('lessthan3', 1, 'pk_1_data'),
            ('lessthan3', 2, 'pk_2_data'),
            ('gt3', 3, 'pk_3_data'),
            ('gt3', 4, 'pk_4_data'),
            ('gt3', 5, 'pk_5_data'),
            ('gt3', 6, 'pk_6_data')
        ]

        simple_query = select(
            [
                case(
                    {1: 'one', 2: 'two', },
                    value=info_table.c.pk, else_='other'),
                info_table.c.pk
            ],
            whereclause=info_table.c.pk < 4,
            from_obj=[info_table])

        assert simple_query.execute().fetchall() == [
            ('one', 1),
            ('two', 2),
            ('other', 3),
        ]
Example #11
0
def strategy_outline(name,goal):
    P=models.Projects.query.all()
    project=models.Projects.query.filter_by(id=name).first() 
    pgoal=models.Goals.query.filter_by(id=goal).first() 
    S=pgoal.strategies.all()
    sform=strategy_form(request.values)
    delete_form=DeleteRow_form()
    q_sum = (db.session.query(
    Projects.id.label("project_id"),
    func.sum(case([(Tasks.complete == True, 1)], else_=0)).label("x"),
    func.sum(case([(and_(Tasks.deadline != None, Tasks.completeDate != None, Tasks.deadline > Tasks.completeDate), 1)], else_=0)).label("y"),
    func.count(Tasks.id).label("total"),
    Strategies.id.label("strategy_id"),
    Goals.id.label("goal_id"),
    ).join(Goals, Projects.goals).outerjoin(Strategies, Goals.strategies).outerjoin(Tasks, Strategies.tasks).group_by(Projects.id,Goals.id,Strategies.id).filter(Goals.id == goal) )
    if request.method == 'POST' and sform.submit.data:
        print sform.validate()
        if sform.validate() == False:
            flash('Failed Field validation.')
            flash_errors(sform)
            return redirect(url_for('strategy_outline',name=name,goal=goal))
        else:
            p=models.Strategies(strategy=sform.strategy.data,goa=pgoal)
            db.session.add(p)
            db.session.commit()
            return redirect(url_for('strategy_outline',name=name,goal=goal))
    if request.method == 'POST' and  delete_form.submitd.data:
        pstratrow = delete_form.row_id.data
        pstrat=models.Strategies.query.filter_by(id=pstratrow).first()
        db.session.delete(pstrat)
        db.session.commit()
        return redirect(url_for('strategy_outline',name=name,goal=goal))
    return render_template("index_for_strategy.html",project=project,S=S,sform=sform,pgoal=pgoal,P=P,zipit=zip(S,q_sum),delete_form=delete_form)
Example #12
0
def project_outline(name):
    # name=request.args.get('name')
    P=models.Projects.query.all()
    project=models.Projects.query.filter_by(id=name).first() 
    G=project.goals.all()
    gform=goal_form(request.values)
    delete_form=DeleteRow_form()
    q_sum = (db.session.query(
    Projects.id.label("project_id"),
    Goals.id.label("goal_id"),
    func.sum(case([(Tasks.complete == True, 1)], else_=0)).label("x"),
    func.sum(case([(and_(Tasks.deadline != None, Tasks.completeDate != None, Tasks.deadline > Tasks.completeDate), 1)], else_=0)).label("y"),
    func.count(Tasks.id).label("total"),
    ).join(Goals, Projects.goals).outerjoin(Strategies, Goals.strategies).outerjoin(Tasks, Strategies.tasks).group_by(Projects.id,Goals.id).filter(Projects.id == name) )
    if request.method == 'POST' and  gform.submit.data:
        if gform.validate() == False:
            flash('Failed Field validation.')
            flash_errors(gform)
            return redirect(url_for('project_outline', name=name))
        else:
            p=models.Goals(goal=gform.goal.data,proj=project)
            db.session.add(p)
            db.session.commit()
            return redirect(url_for('project_outline', name=name))
    if request.method == 'POST' and  delete_form.submitd.data:
        pstratrow = delete_form.row_id.data
        pstrat=models.Goals.query.filter_by(id=pstratrow).first()
        db.session.delete(pstrat)
        db.session.commit()
        return redirect(url_for('project_outline',name=name))            
    # if request.method == 'POST' and  delete_form.submit.data:
    #     delete_row=
    return render_template("index_for_goal.html",project=project,G=G,gform=gform,P=P,zipit=zip(G,q_sum),delete_form=delete_form)
Example #13
0
def context_relationship_query(contexts):
  """Load a list of objects related to the given contexts

  Args:
    contexts (list(int)): A list of context ids
  Returns:
    objects (list((id, type, None))): Related objects
  """
  if not len(contexts):
    return []

  _context = aliased(all_models.Context, name="c")
  _relationship = aliased(all_models.Relationship, name="rl")

  headers = (case([
      (_relationship.destination_type == _context.related_object_type,
       _relationship.source_id.label('id'))
  ], else_=_relationship.destination_id.label('id')),
      case([
          (_relationship.destination_type == _context.related_object_type,
           _relationship.source_type.label('type'))
      ], else_=_relationship.destination_type.label('type')),
      literal(None))

  return db.session.query(*headers).join(_context, and_(
      _context.id.in_(contexts),
      _relationship.destination_id == _context.related_object_id,
      _relationship.destination_type == _context.related_object_type,
  )).union(db.session.query(*headers).join(_context, and_(
      _context.id.in_(contexts),
      _relationship.source_id == _context.related_object_id,
      _relationship.source_type == _context.related_object_type,
  ))).all()
Example #14
0
    def before_insert(self, mapper, connection, instance):
        if instance.lft and instance.rgt:
            return

        table = instance.nested_object_table()
        if not instance.parent_id:
            max = connection.scalar(func.max(table.c.rgt))
            instance.lft = max + 1
            instance.rgt = max + 2
        else:
            right_most_sibling = connection.scalar(
                select([table.c.rgt]).where(table.c.id==instance.parent_id)
            )
            connection.execute(
                table.update(table.c.rgt>=right_most_sibling).values(
                    lft = case(
                            [(table.c.lft>right_most_sibling, table.c.lft + 2)],
                            else_ = table.c.lft
                          ),
                    rgt = case(
                            [(table.c.rgt>=right_most_sibling, table.c.rgt + 2)],
                            else_ = table.c.rgt
                          )
                )
            )
            instance.lft = right_most_sibling
            instance.rgt = right_most_sibling + 1
    def _get_dep_statuses(self, ti, session, dep_context):
        TI = airflow.models.TaskInstance
        TR = airflow.models.TriggerRule

        # Checking that all upstream dependencies have succeeded
        if not ti.task.upstream_list:
            yield self._passing_status(
                reason="The task instance did not have any upstream tasks.")
            raise StopIteration

        if ti.task.trigger_rule == TR.DUMMY:
            yield self._passing_status(reason="The task had a dummy trigger rule set.")
            raise StopIteration

        # TODO(unknown): this query becomes quite expensive with dags that have many
        # tasks. It should be refactored to let the task report to the dag run and get the
        # aggregates from there.
        qry = (
            session
            .query(
                func.coalesce(func.sum(
                    case([(TI.state == State.SUCCESS, 1)], else_=0)), 0),
                func.coalesce(func.sum(
                    case([(TI.state == State.EXCLUDED, 1)], else_=0)), 0),
                func.coalesce(func.sum(
                    case([(TI.state == State.SKIPPED, 1)], else_=0)), 0),
                func.coalesce(func.sum(
                    case([(TI.state == State.FAILED, 1)], else_=0)), 0),
                func.coalesce(func.sum(
                    case([(TI.state == State.UPSTREAM_FAILED, 1)], else_=0)), 0),
                func.count(TI.task_id),
            )
            .filter(
                TI.dag_id == ti.dag_id,
                TI.task_id.in_(ti.task.upstream_task_ids),
                TI.execution_date == ti.execution_date,
                TI.state.in_([
                    State.SUCCESS, State.FAILED, State.EXCLUDED,
                    State.UPSTREAM_FAILED, State.SKIPPED]),
            )
        )

        successes, excluded, skipped, failed, upstream_failed, done = qry.first()

        # Add excluded tasks into successful tasks as they are equivalent for
        # dependency purposes. This is done in this way, not using the
        # state_for_dependents function, due to the constraints of SQLAlchemy
        # queries.
        successes = successes + excluded

        for dep_status in self._evaluate_trigger_rule(
                ti=ti,
                successes=successes,
                skipped=skipped,
                failed=failed,
                upstream_failed=upstream_failed,
                done=done,
                flag_upstream_failed=dep_context.flag_upstream_failed,
                session=session):
            yield dep_status
Example #16
0
def before_insert(mapper, connection, instance):

    print "making adjustments before insertion"
    # If the new term has no parent, connect to root
    if instance.parent == None:
        category = mapper.mapped_table
        values = connection.execute(select([category]).where(category.c.name == "TREE_ROOT")).first().values()
        parent = Category()
        parent.name = values[0]
        parent.level = values[2]
        parent.left = values[3]
        parent.right = values[4]
        instance.parent = parent
    category = mapper.mapped_table

    # Find right most sibling's right value
    right_most_sibling = connection.scalar(select([category.c.rgt]).where(category.c.name == instance.parent.name))

    # Update all values greater than rightmost sibiling
    connection.execute(
        category.update(category.c.rgt >= right_most_sibling).values(
            # Update if left bound in greater than rightmost sibling
            lft=case([(category.c.lft > right_most_sibling, category.c.lft + 2)], else_=category.c.lft),
            # Update if right bound is greater than right most sibling
            rgt=case([(category.c.rgt >= right_most_sibling, category.c.rgt + 2)], else_=category.c.rgt),
        )
    )
    instance.left = right_most_sibling
    instance.right = right_most_sibling + 1
    instance.level = instance.parent.level + 1
    def test_text_doesnt_explode(self):

        for s in [
            select(
                [
                    case(
                        [
                            (
                                info_table.c.info == 'pk_4_data',
                                text("'yes'"))],
                        else_=text("'no'"))
                ]).order_by(info_table.c.info),

            select(
                [
                    case(
                        [
                            (
                                info_table.c.info == 'pk_4_data',
                                literal_column("'yes'"))],
                        else_=literal_column("'no'")
                    )]
            ).order_by(info_table.c.info),

        ]:
            if testing.against("firebird"):
                eq_(s.execute().fetchall(), [
                    ('no ', ), ('no ', ), ('no ', ), ('yes', ),
                    ('no ', ), ('no ', ),
                ])
            else:
                eq_(s.execute().fetchall(), [
                    ('no', ), ('no', ), ('no', ), ('yes', ),
                    ('no', ), ('no', ),
                ])
def upgrade():
    op.create_check_constraint('client_user_id_or_org_id', 'client',
        sa.case([(column('user_id') != None, 1)], else_=0) + sa.case([(column('org_id') != None, 1)], else_=0) == 1  # NOQA
        )

    op.create_check_constraint('permission_user_id_or_org_id', 'permission',
        sa.case([(column('user_id') != None, 1)], else_=0) + sa.case([(column('org_id') != None, 1)], else_=0) == 1  # NOQA
        )
  def _join_relationships(cls, id_):
    """Make a self-join of Relationship table to find common mappings.

    Returns a query with results for [(related_type, similar_id, similar_type)]
    where similar_id and similar_type describe a second-tier mapped object and
    related_type is the type of a common mapped object between "object" and
    "similar".
    """
    # get all Relationships for self
    object_to_related = db.session.query(Relationship).filter(
        or_(and_(Relationship.source_type == cls.__name__,
                 Relationship.source_id == id_),
            and_(Relationship.destination_type == cls.__name__,
                 Relationship.destination_id == id_))).subquery()

    # define how to get id and type of "related" objects
    related_id_case = (case([(and_(object_to_related.c.source_id == id_,
                                   object_to_related.c.source_type ==
                                   cls.__name__),
                              object_to_related.c.destination_id)],
                            else_=object_to_related.c.source_id)
                       .label("related_id"))
    related_type_case = (case([(and_(object_to_related.c.source_id == id_,
                                     object_to_related.c.source_type ==
                                     cls.__name__),
                                object_to_related.c.destination_type)],
                              else_=object_to_related.c.source_type)
                         .label("related_type"))

    related_to_similar = aliased(Relationship, name="related_to_similar")

    # self-join Relationships to get "similar" id and type; save "related" type
    # to get the weight of this relationship later
    return [
        db.session.query(
            related_type_case,
            related_to_similar.destination_id.label("similar_id"),
            related_to_similar.destination_type.label("similar_type"),
        ).join(
            related_to_similar,
            and_(related_id_case == related_to_similar.source_id,
                 related_type_case == related_to_similar.source_type),
        ).filter(
            related_to_similar.source_type != "Snapshot"
        ),
        db.session.query(
            related_type_case,
            related_to_similar.source_id.label("similar_id"),
            related_to_similar.source_type.label("similar_type"),
        ).join(
            related_to_similar,
            and_(related_id_case == related_to_similar.destination_id,
                 related_type_case == related_to_similar.destination_type),
        ).filter(
            related_to_similar.destination_type != "Snapshot"
        )
    ]
Example #20
0
def graphs_stats():
    P=models.Projects.query.all()
    q_sum = (db.session.query(
    Projects.id.label("project_id"),
    func.sum(case([(Tasks.complete == True, 1)], else_=0)).label("x"),
    func.sum(case([(and_(Tasks.deadline != None, Tasks.completeDate != None, Tasks.deadline > Tasks.completeDate), 1)], else_=0)).label("y"),
    func.count(Tasks.id).label("total"),
    ).outerjoin(Goals, Projects.goals).outerjoin(Strategies, Goals.strategies).outerjoin(Tasks, Strategies.tasks).group_by(Projects.id))   
    return render_template("graph_stats.html", P=P,q_sum=q_sum,zipit=zip(P,q_sum))
Example #21
0
  def _inter_tree_move_and_close_gap(self, connection, session_objs, node,
    new_tree_id, left_right_change, depth_change, parent_id=None):
    """Removes ``node`` from its current tree, with the given set of changes
    being applied to ``node`` and its descendants, closing the gap left by
    moving ``node`` as it does so.

    If ``parent_id`` is ``None``, this indicates that ``node`` is being moved
    to a brand new tree as its root node, and will thus have its parent field
    set to ``NULL``. Otherwise, ``node`` will have ``parent_id`` set for its
    parent field."""
    options = self._tree_options

    tree_id  = getattr(node, options.tree_id_field.name)
    left     = getattr(node, options.left_field.name)
    right    = getattr(node, options.right_field.name)
    depth    = getattr(node, options.depth_field.name)
    gap_size = right - left + 1

    connection.execute(
      options.table.update()
        .values({
          options.parent_id_field: sqlalchemy.case(
            [(options.pk_field == getattr(node, options.pk_field.name), parent_id)],
            else_ = options.parent_id_field),
          options.tree_id_field:   sqlalchemy.case(
            [((options.left_field >= left) & (options.left_field <= right), new_tree_id)],
            else_ = options.tree_id_field),
          options.left_field:      sqlalchemy.case(
            [((options.left_field >= left) & (options.left_field <= right), options.left_field + left_right_change),
             ((options.left_field >  right),                                options.left_field - gap_size)],
            else_ = options.left_field),
          options.right_field:     sqlalchemy.case(
            [((options.right_field >= left) & (options.right_field <= right), options.right_field + left_right_change),
             ((options.right_field >  right),                                 options.right_field - gap_size)],
            else_ = options.right_field),
          options.depth_field:     sqlalchemy.case(
            [((options.left_field >= left) & (options.left_field <= right), options.depth_field + depth_change)],
            else_ = options.depth_field),
        })
        .where(options.tree_id_field == tree_id))
    for obj in session_objs:
      obj_tree_id = getattr(obj, options.tree_id_field.name)
      if obj_tree_id != tree_id: continue
      obj_left    = getattr(obj, options.left_field.name)
      obj_right   = getattr(obj, options.right_field.name)
      obj_depth   = getattr(obj, options.depth_field.name)
      if   obj_left >= left and obj_left <= right:
        setattr(obj, options.tree_id_field.name, new_tree_id)
        setattr(obj, options.left_field.name,    obj_left  + left_right_change)
        setattr(obj, options.depth_field.name,   obj_depth + depth_change)
      elif obj_left > right:
        setattr(obj, options.left_field.name,    obj_left  - gap_size)
      if   obj_right >= left and obj_right <= right:
        setattr(obj, options.right_field.name,   obj_right + left_right_change)
      elif obj_right > right:
        setattr(obj, options.right_field.name,   obj_right - gap_size)
    setattr(node, options.parent_id_field.name,  parent_id)
    def _get_permissions_query(self, session, identifier):
        """
        select domain, json_agg(parts) as permissions from
            (select domain, row_to_json(r) as parts from
                    (select domain, action, array_agg(distinct target) as target from
                        (select (case when domain is null then '*' else domain end) as domain,
                                (case when target is null then '*' else target end) as target,
                                array_agg(distinct (case when action is null then '*' else action end)) as action
                           from permission
                          group by domain, target
                         ) x
                      group by domain, action)
              r) parts
        group by domain;
        """
        thedomain = case([(Domain.name == None, "*")], else_=Domain.name)
        theaction = case([(Action.name == None, "*")], else_=Action.name)
        theresource = case([(Resource.name == None, "*")], else_=Resource.name)

        action_agg = func.array_agg(theaction.distinct())

        stmt1 = (
            session.query(
                Permission.domain_id,
                thedomain.label("domain"),
                Permission.resource_id,
                theresource.label("resource"),
                action_agg.label("action"),
            )
            .select_from(User)
            .join(role_membership_table, User.pk_id == role_membership_table.c.user_id)
            .join(role_permission_table, role_membership_table.c.role_id == role_permission_table.c.role_id)
            .join(Permission, role_permission_table.c.permission_id == Permission.pk_id)
            .outerjoin(Domain, Permission.domain_id == Domain.pk_id)
            .outerjoin(Action, Permission.action_id == Action.pk_id)
            .outerjoin(Resource, Permission.resource_id == Resource.pk_id)
            .filter(User.identifier == identifier)
            .group_by(Permission.domain_id, Domain.name, Permission.resource_id, Resource.name)
        ).subquery()

        stmt2 = (
            session.query(stmt1.c.domain, stmt1.c.action, func.array_agg(stmt1.c.resource.distinct()).label("resource"))
            .select_from(stmt1)
            .group_by(stmt1.c.domain, stmt1.c.action)
        ).subquery()

        stmt3 = (
            session.query(stmt2.c.domain, func.row_to_json(as_row(stmt2)).label("parts")).select_from(stmt2)
        ).subquery()

        final = (
            session.query(stmt3.c.domain, cast(func.json_agg(stmt3.c.parts), Text))
            .select_from(stmt3)
            .group_by(stmt3.c.domain)
        )

        return final
Example #23
0
    def _get_future_sids_for_root_symbol(self, root_symbol, as_of_date_ns):
        fc_cols = self.futures_contracts.c

        return list(map(
            itemgetter('sid'),
            sa.select((fc_cols.sid,)).where(
                (fc_cols.root_symbol == root_symbol) &

                # Filter to contracts that are still valid. If both
                # exist, use the one that comes first in time (i.e.
                # the lower value). If either notice_date or
                # expiration_date is NaT, use the other. If both are
                # NaT, the contract cannot be included in any chain.
                sa.case(
                    [
                        (
                            fc_cols.notice_date == pd.NaT.value,
                            fc_cols.expiration_date >= as_of_date_ns
                        ),
                        (
                            fc_cols.expiration_date == pd.NaT.value,
                            fc_cols.notice_date >= as_of_date_ns
                        )
                    ],
                    else_=(
                        sa.func.min(
                            fc_cols.notice_date,
                            fc_cols.expiration_date
                        ) >= as_of_date_ns
                    )
                )
            ).order_by(
                # If both dates exist sort using minimum of
                # expiration_date and notice_date
                # else if one is NaT use the other.
                sa.case(
                    [
                        (
                            fc_cols.expiration_date == pd.NaT.value,
                            fc_cols.notice_date
                        ),
                        (
                            fc_cols.notice_date == pd.NaT.value,
                            fc_cols.expiration_date
                        )
                    ],
                    else_=(
                        sa.func.min(
                            fc_cols.notice_date,
                            fc_cols.expiration_date
                        )
                    )
                ).asc()
            ).execute().fetchall()
        ))
    def test_literal_interpretation(self):
        t = table('test', column('col1'))

        assert_raises(exc.ArgumentError, case, [("x", "y")])

        self.assert_compile(
            case([("x", "y")], value=t.c.col1),
            "CASE test.col1 WHEN :param_1 THEN :param_2 END")
        self.assert_compile(
            case([(t.c.col1 == 7, "y")], else_="z"),
            "CASE WHEN (test.col1 = :col1_1) THEN :param_1 ELSE :param_2 END")
Example #25
0
    def test_literal_interpretation(self):
        t = table("test", column("col1"))

        self.assert_compile(
            case([("x", "y")], value=t.c.col1),
            "CASE test.col1 WHEN :param_1 THEN :param_2 END",
        )
        self.assert_compile(
            case([(t.c.col1 == 7, "y")], else_="z"),
            "CASE WHEN (test.col1 = :col1_1) THEN :param_1 ELSE :param_2 END",
        )
Example #26
0
 def related_assignables():
   """Header for the mapped_objects join"""
   return db.session.query(
       case([
           (rel2.destination_type == rel1.destination_type,
            rel2.source_id)
       ], else_=rel2.destination_id).label('id'),
       case([
           (rel2.destination_type == rel1.destination_type,
            rel2.source_type)
       ], else_=rel2.destination_type).label('type'),
       rel1.context_id if context_not_role else literal('R')
   ).select_from(rel1)
Example #27
0
    def build_column(self, sql_table):
        if self.column_name:
            expr = case(value=sql_table.c[self.column_name], whens=self.whens, else_=self.else_)
        else:
            whens = {}
            for when, then in self.whens.items():
                if isinstance(then, basestring):
                    whens[text(when)] = text(then)
                else:
                    whens[text(when)] = then

            expr = case(whens=whens, else_=self.else_)

        return self.aggregate_fn(expr).label(self.alias)
Example #28
0
def add_column(conn, table, column_name, default_value=None):
    """
    Adds given column to `audit.activity` table jsonb data columns.

    In the following example we reflect the changes made to our schema to
    activity table.

    ::

        import sqlalchemy as sa
        from alembic import op
        from postgresql_audit import add_column


        def upgrade():
            op.add_column('article', sa.Column('created_at', sa.DateTime()))
            add_column(op, 'article', 'created_at')


    :param conn:
        An object that is able to execute SQL (either SQLAlchemy Connection,
        Engine or Alembic Operations object)
    :param table:
        The table to remove the column from
    :param column_name:
        Name of the column to add
    :param default_value:
        The default value of the column
    """
    activity_table = get_activity_table()
    data = {column_name: default_value}
    query = (
        activity_table.update()
        .values(
            old_data=sa.case(
                [(activity_table.c.old_data.isnot(None), jsonb_merge(activity_table.c.old_data, data))], else_=None
            ),
            changed_data=sa.case(
                [
                    (
                        sa.and_(activity_table.c.changed_data.isnot(None), activity_table.c.verb != "update"),
                        jsonb_merge(activity_table.c.changed_data, data),
                    )
                ],
                else_=activity_table.c.changed_data,
            ),
        )
        .where(activity_table.c.table_name == table)
    )
    return conn.execute(query)
Example #29
0
 def specified_month_all( self, trans, **kwd ):
     params = util.Params( kwd )
     msg = ''
     monitor_email = params.get( 'monitor_email', '*****@*****.**' )
     year, month = map( int, params.get( 'month', datetime.utcnow().strftime( "%Y-%m" ) ).split( "-" ) )
     start_date = date( year, month, 1 )
     end_date = start_date + timedelta( days=calendar.monthrange( year, month )[1] )
     month_label = start_date.strftime( "%B" )
     year_label = start_date.strftime( "%Y" )
     q = sa.select( ( sa.func.date( galaxy.model.Job.table.c.create_time ).label( 'date' ),
                      sa.func.sum( sa.case( [( galaxy.model.User.table.c.email == monitor_email, 1 )], else_=0 ) ).label( 'monitor_jobs' ),
                      sa.func.count( galaxy.model.Job.table.c.id ).label( 'total_jobs' ) ),
                    whereclause = sa.and_( galaxy.model.Job.table.c.create_time >= start_date,
                                           galaxy.model.Job.table.c.create_time < end_date ),
                    from_obj = [ sa.outerjoin( galaxy.model.Job.table, 
                                               galaxy.model.History.table ).outerjoin( galaxy.model.User.table ) ],
                    group_by = [ 'date' ],
                    order_by = [ sa.desc( 'date' ) ] )
     jobs = []
     for row in q.execute():
         jobs.append( ( row.date.strftime( "%A" ),
                        row.date,
                        row.total_jobs - row.monitor_jobs,
                        row.monitor_jobs,
                        row.total_jobs,
                        row.date.strftime( "%d" ) ) )
     return trans.fill_template( 'jobs_specified_month_all.mako', 
                                 month_label=month_label, 
                                 year_label=year_label, 
                                 month=month, 
                                 jobs=jobs, 
                                 msg=msg ) 
Example #30
0
    def test_searched_case(self):
        self.con.table("alltypes")
        st = self.con.meta.tables["alltypes"]

        expr = self._case_search_case()
        cases = [(expr, sa.case([(st.c.f > L(0), st.c.d * L(2)), (st.c.c < L(0), st.c.a * L(2))], else_=sa.null()))]
        self._check_expr_cases(cases)
Example #31
0
 def e_mag(cls):
     """The error on the magnitude of the photometry point."""
     return sa.case(
         [(
             sa.and_(cls.flux != 'NaN', cls.flux > 0,
                     cls.fluxerr > 0),  # noqa: E711
             2.5 / sa.func.ln(10) * cls.fluxerr / cls.flux,
         )],
         else_=None,
     )
Example #32
0
def _regex_extract(t, expr):
    string, pattern, index = map(t.translate, expr.op().args)
    result = sa.case(
        [(
            sa.func.textregexeq(string, pattern),
            sa.func.regex_extract(string, pattern, index + 1),
        )],
        else_='',
    )
    return result
Example #33
0
 def snr(self):
     """Signal-to-noise ratio of this Photometry point."""
     return sa.case(
         [(
             sa.and_(self.flux != 'NaN', self.fluxerr != 'NaN',
                     self.fluxerr != 0),  # noqa
             self.flux / self.fluxerr,
         )],
         else_=None,
     )
Example #34
0
 def mag(cls):
     """The magnitude of the photometry point in the AB system."""
     return sa.case(
         [
             (
                 sa.and_(cls.flux != 'NaN', cls.flux > 0),  # noqa
                 -2.5 * sa.func.log(cls.flux) + PHOT_ZP,
             )
         ],
         else_=None,
     )
Example #35
0
def test_nested_labels(engine, table):
    col = table.c.integer
    exprs = [
        sqlalchemy.func.sum(
            sqlalchemy.func.sum(col.label("inner")).label("outer")).over(),
        sqlalchemy.func.sum(
            sqlalchemy.case([[sqlalchemy.literal(True),
                              col.label("inner")]]).label("outer")),
        sqlalchemy.func.sum(
            sqlalchemy.func.sum(
                sqlalchemy.case([[
                    sqlalchemy.literal(True),
                    col.label("inner")
                ]]).label("middle")).label("outer")).over(),
    ]
    for expr in exprs:
        sql = str(expr.compile(engine))
        assert "inner" not in sql
        assert "middle" not in sql
        assert "outer" not in sql
Example #36
0
 def addBinStatusStr(query):
     return query.add_column(sqlalchemy.case([
     ((ProBin.status == 0), ProBin.BinStatuses[0]),
     ((ProBin.status == 1), ProBin.BinStatuses[1]),
     ((ProBin.status == 2), ProBin.BinStatuses[2]),
     ((ProBin.status == 3), ProBin.BinStatuses[3]),
     ((ProBin.status == 4), ProBin.BinStatuses[4]),
     ((ProBin.status == 5), ProBin.BinStatuses[5]),
     ((ProBin.status == 6), ProBin.BinStatuses[6]),
     ], 
     else_ = 'Unknown').label('binstatusstr'))
Example #37
0
    def test_searched_case(self):
        self.con.table('alltypes')
        st = self.con.meta.tables['alltypes']

        expr = self._case_search_case()
        cases = [
            (expr, sa.case([(st.c.f > L(0), st.c.d * L(2)),
                            (st.c.c < L(0), st.c.a * L(2))],
                           else_=sa.null())),
        ]
        self._check_expr_cases(cases)
Example #38
0
def aggregate_upvotes():
    """Aggregate upvotes and downvotes by feedback URL.
    """
    rows = Feedback.query.with_entities(
        Feedback.url,
        sa.func.sum(sa.case([(Feedback.upvote == True, 1)
                             ])).label('upvotes'),  # noqa
        sa.func.sum(sa.case([(Feedback.upvote == False, 1)
                             ])).label('downvotes'),  # noqa
        sa.func.count(Feedback.upvote).label('count'),
    ).group_by(Feedback.url)
    return jsonify({
        'results': {
            row.url: {
                'upvotes': row.upvotes,
                'downvotes': row.downvotes,
            }
            for row in rows
        }
    })
Example #39
0
def build_type_query(type_, result_spec):
    model = ggrc.models.get_model(type_)
    mapper = model._sa_class_manager.mapper
    columns = []
    columns_indexes = {}
    if len(list(mapper.self_and_descendants)) == 1:
        type_column = sqlalchemy.literal(mapper.class_.__name__)
    else:
        # Handle polymorphic types with CASE
        type_column = sqlalchemy.case(
            value=mapper.polymorphic_on,
            whens={
                val: mapper.class_.__name__
                for val, mapper in mapper.polymorphic_map.items()
            })
    columns.append(type_column)
    columns_indexes['type'] = 0
    columns.append(model.id)
    columns_indexes['id'] = 1
    columns.append(mapper.c.context_id)
    columns_indexes['context_id'] = 2
    columns.append(mapper.c.updated_at)
    columns_indexes['updated_at'] = 3

    conditions = {}
    for keys, vals in result_spec.items():
        for key in keys:
            if key not in columns_indexes:
                columns_indexes[key] = len(columns)
                columns.append(mapper.c[key])
        conditions.setdefault(keys, []).extend(vals.keys())

    where_clauses = []
    for keys, vals in conditions.items():
        if len(keys) == 1:
            # If the key is singular, use `IN (...)`
            where_clauses.append(columns[columns_indexes[keys[0]]].in_(
                [v[0] for v in vals]))
        else:
            # If multiple keys, build `OR` of multiple `AND` clauses
            clauses = []
            cols = [columns[columns_indexes[k]] for k in keys]
            for val in vals:
                # Now build OR clause with (key, val) pairs
                clause = []
                for i, _ in enumerate(val):
                    clause.append(cols[i] == val[i])
                clauses.append(sqlalchemy.and_(*clause))
            where_clauses.append(sqlalchemy.or_(*clauses))
    where_clause = sqlalchemy.or_(*where_clauses)

    query = db.session.query(*columns).filter(where_clause)

    return columns_indexes, query
Example #40
0
    def _argmax(self, result):
        if len(result.cols) == 1:
            return Result([sa.literal(0)], self.from_obj)
        features = self._make_cte(result)
        max_val = Result([_max(features.cols)], features.from_obj)
        max_val = self._make_cte(max_val, ['_max'])

        argmax = sa.case([(col == max_val.cols[0], i)
                          for i, col in enumerate(features.cols[:-1])],
                         else_=len(features.cols) - 1)
        return Result([argmax], _merge(features.from_obj, max_val.from_obj))
Example #41
0
        def inner_wrapper(self, column, mostly=None, result_format=None, *args, **kwargs):
            if result_format is None:
                result_format = self.default_expectation_args["result_format"]

            result_format = parse_result_format(result_format)

            if result_format['result_format'] == 'COMPLETE':
                unexpected_count_limit = None
            else:
                unexpected_count_limit = result_format['partial_unexpected_count']

            expected_condition = func(self, column, *args, **kwargs)

            count_query = sa.select([
                sa.func.count().label('element_count'),
                sa.func.sum(
                    sa.case([(sa.column(column) == None, 1)], else_=0)
                ).label('null_count'),
                sa.func.sum(
                    sa.case([(sa.not_(expected_condition), 1)], else_=0)
                ).label('unexpected_count')
            ]).select_from(sa.table(self.table_name))

            count_results = self.engine.execute(count_query).fetchone()

            unexpected_query_results = self.engine.execute(
                sa.select([sa.column(column)]).select_from(sa.table(self.table_name)).where(sa.not_(expected_condition)).limit(unexpected_count_limit)
            )

            nonnull_count = count_results['element_count'] - count_results['null_count']
            maybe_limited_unexpected_list = [x[column] for x in unexpected_query_results.fetchall()]
            success_count = nonnull_count - count_results['unexpected_count']
            success, percent_success = self._calc_map_expectation_success(success_count, nonnull_count, mostly)

            return_obj = self._format_column_map_output(
                result_format, success,
                count_results['element_count'], nonnull_count,
                maybe_limited_unexpected_list, None
            )

            return return_obj
Example #42
0
    def test_simple_case(self):
        self.con.table('alltypes')
        st = self.con.meta.tables['alltypes']

        expr = self._case_simple_case()

        cases = [
            (expr, sa.case([(st.c.g == L('foo'), L('bar')),
                            (st.c.g == L('baz'), L('qux'))],
                           else_='default')),
        ]
        self._check_expr_cases(cases)
Example #43
0
def _typeof(t, expr):
    arg, = expr.op().args
    sa_arg = t.translate(arg)
    typ = sa.cast(sa.func.pg_typeof(sa_arg), sa.TEXT)

    # select pg_typeof('asdf') returns unknown so we have to check the child's
    # type for nullness
    return sa.case([
        ((typ == 'unknown') & (arg.type() != dt.null), 'text'),
        ((typ == 'unknown') & (arg.type() == dt.null), 'null'),
    ],
                   else_=typ)
Example #44
0
def stat_plural(stats, count):
    plural = sqlalchemy.func.coalesce(
        stats.c.plural,
        sqlalchemy.func.coalesce(stats.c.singular,
                                 stats.c.string_id).concat("s"))
    if count is not None:
        return sqlalchemy.case(
            {1: sqlalchemy.func.coalesce(stats.c.singular, stats.c.string_id)},
            value=count,
            else_=plural,
        )
    return plural
Example #45
0
    def _assignees(self):
        """Attribute that is used to load all assigned People eagerly."""
        rel = relationship.Relationship

        assignee_id = sa.case(
            [(rel.destination_type
              == person.Person.__name__, rel.destination_id)],
            else_=rel.source_id,
        )
        assignable_id = sa.case(
            [(rel.destination_type == person.Person.__name__, rel.source_id)],
            else_=rel.destination_id,
        )

        return db.relationship(
            person.Person,
            primaryjoin=lambda: self.id == assignable_id,
            secondary=rel.__table__,
            secondaryjoin=lambda: person.Person.id == assignee_id,
            viewonly=True,
        )
Example #46
0
def test_null_in_empty_set_is_false(faux_conn):
    stmt = select([
        sqlalchemy.case(
            [(
                sqlalchemy.null().in_(
                    sqlalchemy.bindparam("foo", value=(), expanding=True)),
                sqlalchemy.true(),
            )],
            else_=sqlalchemy.false(),
        )
    ])
    in_(faux_conn.execute(stmt).fetchone()[0], (False, 0))
Example #47
0
    def _get_pairs(self, basicPairs):
        """
        get cash_on_hand_end_period info from the latest financial report
        per candidate_id/candidate_election_year/cmte_id

        When newlest financial report not filed yet, the financial data columns will be
        null, take the data from the previous filing for calculation.

        However, when the latest fincial report filed, but COH is null (the null will be
        converted to 0 in final calculation) should be used.
        """

        # Window params
        window_p = {
            'partition_by': [
                basicPairs.c.candidate_id,
                basicPairs.c.candidate_election_year,
                basicPairs.c.committee_id,
            ],
            'order_by': [
                sa.case(
                    [
                        (
                            basicPairs.c.coverage_end_date.isnot(None),
                            basicPairs.c.two_year_period,
                        )
                    ]
                )
                .desc()
                .nullslast()
            ],
        }

        pairs = db.session.query(
            basicPairs.c.candidate_id,
            basicPairs.c.name,
            basicPairs.c.party_full,
            basicPairs.c.incumbent_challenge_full,
            basicPairs.c.office,
            basicPairs.c.two_year_period,
            basicPairs.c.candidate_election_year,
            basicPairs.c.committee_id,
            basicPairs.c.receipts.label('receipts'),
            basicPairs.c.disbursements.label('disbursements'),
            sa.func.first_value(basicPairs.c.last_cash_on_hand_end_period)
            .over(**window_p)
            .label('last_cash_on_hand_end_period'),
            sa.func.first_value(basicPairs.c.coverage_end_date)
            .over(**window_p)
            .label('coverage_end_date'),
            basicPairs.c.candidate_pcc_id,
        )
        return pairs
Example #48
0
def new_codes():
    delimiter = sa.bindparam("delimiter", " – ")
    places = sa.func.regexp_split_to_array(
        sa.func.trim(
            sa.func.regexp_replace(service.c.description, r"\((.*)\)", " ")),
        delimiter)
    places.type = sa.ARRAY(sa.Text, dimensions=1)

    s0 = sa.select([service.c.id,
                    places.label("places"), service.c.line]).alias("s0")

    length = sa.func.array_length(s0.c.places, 1)
    short_desc = sa.case([(length == 1, s0.c.places[1])],
                         else_=s0.c.places[1] + delimiter +
                         s0.c.places[length])

    s1 = sa.select([s0.c.id,
                    short_desc.label("short_desc"), s0.c.line]).alias("s1")

    line = sa.func.regexp_replace(sa.func.lower(s1.c.line), r"[^A-Za-z0-9\.]+",
                                  "-", "g")
    desc = sa.func.regexp_replace(sa.func.lower(s1.c.short_desc),
                                  r"[^A-Za-z0-9\.]+", "-", "g")
    code = sa.case([(sa.func.length(line) <= 5, line + "-" + desc)],
                   else_=line)

    s2 = sa.select([s1.c.id, s1.c.short_desc, code.label("code")]).alias("s2")

    row_num = sa.func.row_number().over(partition_by=s2.c.code,
                                        order_by=s2.c.id)
    with_row_num = s2.c.code + "-" + sa.cast(row_num, sa.Text)
    new_code = sa.case([(row_num == 1, s2.c.code)], else_=with_row_num)

    query = sa.select([
        s2.c.id.label("id"),
        s2.c.short_desc.label("short_description"),
        new_code.label("code")
    ]).alias("dest_codes")

    return query
Example #49
0
def store_challenge_results(users, challenge, stats):
    with model.engine.connect() as conn:
        conn.execute(model.challenges.update().values(
            num_games=model.challenges.c.num_games + 1,
            status=sqlalchemy.case(
                [
                    (model.challenges.c.num_games >= 30,
                     model.ChallengeStatus.FINISHED.value),
                ],
                else_=model.ChallengeStatus.CREATED.value,
            ),
        ).where(model.challenges.c.id == challenge))

        for user in users:
            # 4 points for 1st place, 3 for 2nd, etc.
            points = len(users) - user["rank"] + 1
            if len(users) == 2:
                points += 2

            if user["player_tag"] in stats.players:
                ships_produced = stats.players[
                    user["player_tag"]].ships_produced
                attacks_made = stats.players[user["player_tag"]].attacks_total
            else:
                ships_produced = attacks_made = 0

            conn.execute(model.challenge_participants.update().values(
                points=model.challenge_participants.c.points + points,
                ships_produced=model.challenge_participants.c.ships_produced +
                ships_produced,
                attacks_made=model.challenge_participants.c.attacks_made +
                attacks_made,
            ).where(
                (model.challenge_participants.c.challenge_id == challenge)
                & (model.challenge_participants.c.user_id == user["user_id"])))

        challenge_row = conn.execute(
            model.challenges.select(
                model.challenges.c.id == challenge)).first()

        if challenge_row and \
           challenge_row["status"] == model.ChallengeStatus.FINISHED.value:
            winner = conn.execute(
                model.challenge_participants.select().order_by(
                    model.challenge_participants.c.points.desc(),
                    model.challenge_participants.c.ships_produced.desc(),
                    model.challenge_participants.c.attacks_made.desc(),
                )).first()
            conn.execute(model.challenges.update().values(
                finished=sqlalchemy.sql.func.now(),
                winner=winner["user_id"],
            ).where(model.challenges.c.id == challenge))
Example #50
0
 def sql_read(self, column):
     # 3. Reading - SQL layer - append with call to .AsBinaryZM() to convert MS binary to WKB.
     # POINT EMPTY is handled specially since SQL Server returns WKB(MULTIPOINT EMPTY) for (POINT EMPTY).AsBinaryZM()
     return sa.case(
         (
             sa.and_(
                 InstanceFunction("STGeometryType", column) == "Point",
                 InstanceFunction("STIsEmpty", column) == 1,
             ),
             sa.literal_column(self.EMPTY_POINT_WKB, type_=self),
         ),
         else_=InstanceFunction("AsBinaryZM", column, type_=self),
     )
Example #51
0
def get_type_select_column(model):
    """Get column name,taking into account polymorphic types."""
    mapper = model._sa_class_manager.mapper  # pylint: disable=protected-access
    if mapper.polymorphic_on is None:
        type_column = sa.literal(mapper.class_.__name__)
    else:
        # Handle polymorphic types with CASE
        type_column = sa.case(value=mapper.polymorphic_on,
                              whens={
                                  val: m.class_.__name__
                                  for val, m in mapper.polymorphic_map.items()
                              })
    return type_column
Example #52
0
 def printf(cls, bibfile, bibitem, *, label: str = 'printf'):
     return sa.case(
         (sa.and_(cls.pages != None, cls.trigger != None),
          sa.func.printf('**%s:%s**:%s<trigger "%s">', bibfile.name,
                         bibitem.bibkey, cls.pages, cls.trigger)),
         (cls.pages != None,
          sa.func.printf('**%s:%s**:%s', bibfile.name, bibitem.bibkey,
                         cls.pages)),
         (cls.trigger != None,
          sa.func.printf('**%s:%s**<trigger "%s">', bibfile.name,
                         bibitem.bibkey, cls.trigger)),
         else_=sa.func.printf('**%s:%s**', bibfile.name,
                              bibitem.bibkey)).label(label)
Example #53
0
  def _manage_space(self, size, target, tree_id, session):
    """Manages spaces in the tree identified by ``tree_id`` by changing the
    values of the left and right columns by ``size`` after the given
    ``target`` point."""
    options = self._tree_options

    expr = options.table.update() \
      .values({
        options.left_field:  sqlalchemy.case(
          [(options.left_field > target, options.left_field + size)],
          else_ = options.left_field),
        options.right_field: sqlalchemy.case(
          [(options.right_field > target, options.right_field + size)],
          else_ = options.right_field),
      }) \
      .where(
        (options.tree_id_field == tree_id) &
        ((options.left_field  > target) |
         (options.right_field > target))
      )

    session.execute(expr)
    def expect_column_median_to_be_between(self,
                                           column,
                                           min_value=None,
                                           max_value=None,
                                           result_format=None,
                                           include_config=False,
                                           catch_exceptions=None,
                                           meta=None):

        if min_value is None and max_value is None:
            raise ValueError("min_value and max_value cannot both be None")

        # Inspiration from https://stackoverflow.com/questions/942620/missing-median-aggregate-function-in-django
        count_query = self.engine.execute(
            sa.select([
                sa.func.count().label("element_count"),
                sa.func.sum(sa.case([(sa.column(column) == None, 1)],
                                    else_=0)).label('null_count')
            ]).select_from(sa.table(self.table_name)))

        element_values = self.engine.execute(
            sa.select(column).order_by(column).where(
                sa.column(column) != None).select_from(
                    sa.table(self.table_name)))

        # Fetch the Element count, null count, and sorted/null dropped column values
        elements = count_query.fetchone()
        column_values = list(element_values.fetchall())

        # The number of non-null/non-ignored values
        nonnull_count = elements['element_count'] - elements['null_count']

        if nonnull_count % 2 == 0:
            # An even number of column values: take the average of the two center values
            column_median = (
                column_values[nonnull_count // 2 - 1][0] +  # left center value
                column_values[nonnull_count // 2][0]  # right center value
            ) / 2.0  # Average center values
        else:
            # An odd number of column values, we can just take the center value
            column_median = column_values[nonnull_count //
                                          2][0]  # True center value

        return {
            'success': ((min_value is None) or (min_value <= column_median))
            and ((max_value is None) or (column_median <= max_value)),
            'result': {
                'observed_value': column_median
            }
        }
Example #55
0
 def bulk_update_ehr_status_with_session(session, parameter_sets):
     query = (sqlalchemy.update(ParticipantSummary).where(
         ParticipantSummary.participantId ==
         sqlalchemy.bindparam('pid')).values({
             ParticipantSummary.ehrStatus.name:
             EhrStatus.PRESENT,
             ParticipantSummary.ehrUpdateTime:
             sqlalchemy.bindparam('receipt_time'),
             ParticipantSummary.ehrReceiptTime:
             sqlalchemy.case([(ParticipantSummary.ehrReceiptTime == None,
                               sqlalchemy.bindparam('receipt_time'))],
                             else_=ParticipantSummary.ehrReceiptTime),
         }))
     return session.execute(query, parameter_sets)
Example #56
0
 def printf(cls):
     return sa.case([
         (sa.and_(cls.pages != None, cls.trigger != None),
          sa.func.printf('**%s:%s**:%s<trigger "%s">', cls.bibfile,
                         cls.bibkey, cls.pages, cls.trigger)),
         (cls.pages != None,
          sa.func.printf('**s:%s**:%s', cls.bibfile, cls.bibkey,
                         cls.pages)),
         (cls.trigger != None,
          sa.func.printf('**%s:%s**<trigger "%s">', cls.bibfile, cls.bibkey,
                         cls.trigger)),
     ],
                    else_=sa.func.printf('**%s:%s**', cls.bibfile,
                                         cls.bibkey))
Example #57
0
def load():
    engine = sa.create_engine(CONNECTION)
    metadata = sa.MetaData(bind=engine)

    table = metadata.Table("customer", reflect=True)

    target_type = sa.case(CUSTOMER_TYPE_MAPPING,
                          else_="unknown")

    selection = [table.c.id, table.c.name, table.c.type]

    source = sa.select(selection, from_obj=table)

    ...
Example #58
0
def top_users():
    type = tdb.types_id[Account._type_id]
    tt, dt = type.thing_table, type.data_table[0]

    aliases = tdb.alias_generator()
    karma = dt.alias(aliases.next())

    cases = [(karma.c.key.like('%_link_karma'),
              sa.cast(karma.c.value, sa.Integer) * g.post_karma_multiplier)]

    for subreddit in subreddits_with_custom_karma_multiplier():
        key = "%s_link_karma" % subreddit.name
        cases.insert(0,
                     (karma.c.key == key, sa.cast(karma.c.value, sa.Integer) *
                      subreddit.post_karma_multiplier))

    s = sa.select([tt.c.thing_id],
                  sa.and_(tt.c.spam == False, tt.c.deleted == False,
                          karma.c.thing_id == tt.c.thing_id,
                          karma.c.key.like('%_karma')),
                  group_by=[tt.c.thing_id],
                  order_by=sa.desc(
                      sa.func.sum(
                          sa.case(cases,
                                  else_=sa.cast(karma.c.value, sa.Integer)))),
                  limit=10)
    # Translation of query:
    # SELECT
    #  reddit_thing_account.thing_id
    # FROM
    #   reddit_thing_account,
    #   reddit_data_account
    # WHERE
    #  (reddit_thing_account.spam = 'f' AND
    #   reddit_thing_account.deleted = 'f' AND
    #   reddit_thing_account.thing_id = reddit_data_account.thing_id AND
    #   reddit_data_account.key LIKE '%_karma')
    # GROUP BY
    #   reddit_thing_account.thing_id
    # ORDER BY
    #  sum(
    #    CASE
    #      WHEN reddit_data_account.key = 'lesswrong_link_karma' THEN
    #        CAST(reddit_data_account.value AS INTEGER) * 10
    #      ELSE CAST(reddit_data_account.value AS INTEGER)
    #    END
    #  ) DESC
    # LIMIT 10
    rows = s.execute().fetchall()
    return [r.thing_id for r in rows]
Example #59
0
 def jsonf(cls,
           *,
           sort_keys: bool,
           optional: bool = False,
           label: str = 'jsonf'):
     mapping = json_object(isohid=cls.isohid,
                           comment_type=cls.comment_type,
                           ethnologue_versions=cls.ethnologue_versions,
                           comment=cls.comment,
                           sort_keys_=sort_keys)
     if optional:
         return sa.case((cls.languoid_id == None, None),
                        else_=mapping).label(label)
     return mapping.label(label)
Example #60
0
def fetch_payroll_report():
    from app.models import TimeLog

    return (db.select([
        TimeLog.employee_id.label('employee_id'),
        sa.func.year(TimeLog.date).label('year'),
        sa.func.month(TimeLog.date).label('month'),
        (sa.func.day(TimeLog.date) <= 15).label('first_half'),
        sa.func.sum(
            sa.case(value=TimeLog.job_group,
                    whens={
                        'A': TimeLog.hours_worked * 20.0,
                        'B': TimeLog.hours_worked * 30.0
                    })).label('amount_paid')
    ]).group_by('employee_id', 'year', 'month', 'first_half'))