def _add_ordering(sql_query, table, column_type, column_name, order):
    # Special case for this column, which sorts contigs correctly:
    if column_name == 'contig':
        get_contig_num = cast(
            text("SUBSTRING({} FROM '\d+')".format(table.c.contig)),
            type_=Integer)
        starts_with_chr = (text("SUBSTRING({} FROM '^chr(\d+)')"
                                .format(table.c.contig)) != literal(''))
        starts_with_number = (text("SUBSTRING({} FROM '^\d+')"
                                   .format(table.c.contig)) != literal(''))
        # 10000 used here to mean "should be at the end of all the numbers",
        # assuming we never hit a chromosome number >= 10000.
        contig_num_col = case(
            [(starts_with_chr, get_contig_num),
             (starts_with_number, get_contig_num)],
            else_=literal(10000)
        )
        contig_len_col = func.length(table.c.contig)
        contig_col = table.c.contig
        if order == 'desc':
            contig_len_col = desc(contig_len_col)
            contig_col = desc(contig_col)
        return sql_query.order_by(contig_num_col, contig_len_col, contig_col)
    sqla_type = vcf_type_to_sqla_type(column_type)
    column = cast(table.c[column_name], type_=sqla_type)
    column = {'asc': asc(column), 'desc': desc(column)}.get(order)
    return sql_query.order_by(column)

 def construct_search(self, field_name):
     if field_name.startswith('^'):
         return literal(field_name[1:]).startswith
     elif field_name.startswith('='):
         return literal(field_name[1:]).op('=')
     else:
         return literal(field_name).contains

  def _generate_stmt(self, id) :
    name = self.name
    bar_thickness = self.bar_thickness
    staff_symbol = self.staff_symbol
    staff_space = self.staff_space
    n_lines = self.n_lines
    line_stencil = self.line_stencil
    
    bar_lines_to_stencils = select([
      name.c.id.label('id'),
      literal('bar_line_to_stencil').label('writer'),
      literal(0).label('sub_id'),
      literal(0.0).label('x0'),
      literal(0.0).label('y0'),
      literal(0.0).label('x1'),
      (staff_space.c.val * (n_lines.c.val - 1)).label('y1'),
      bar_thickness.c.val.label('thickness'),
    ]).where(safe_eq_comp(name.c.id, id)).\
    where(and_(name.c.val == 'bar_line',
                  name.c.id == bar_thickness.c.id,
                  name.c.id == staff_symbol.c.id,
                  staff_symbol.c.val == staff_space.c.id,
                  staff_symbol.c.val == n_lines.c.id,
                  )).\
    cte(name='bar_lines_to_stencils')

    self.register_stmt(bar_lines_to_stencils)

    self.insert = simple_insert(line_stencil, bar_lines_to_stencils)

    def test_ne_operator(self):

        self.assert_compile(
            literal(5) != literal(10),
            '%(param_1)s <> %(param_2)s',
            checkparams={'param_1': 5, 'param_2': 10}
        )

  def _generate_stmt(self, id) :
    font_name = self.font_name
    font_size = self.font_size
    unicode = self.unicode
    glyph_box = self.glyph_box
    glyph_stencil = self.glyph_stencil
    alignment_directive = self.alignment_directive
    writer = self.writer
    extra_eq = self.extra_eq
    
    generics_to_stencils = select([
      font_name.c.id.label('id'),
      literal(writer).label('writer'),
      literal(0).label('sub_id'),
      font_name.c.val.label('font_name'),
      font_size.c.val.label('font_size'),
      unicode.c.val.label('unicode'),
      case([(alignment_directive.c.x != None, glyph_box.c.x + (alignment_directive.c.x * glyph_box.c.width))], else_=0).label('x'),
      case([(alignment_directive.c.y != None, glyph_box.c.y + glyph_box.c.height - (alignment_directive.c.y * glyph_box.c.height))], else_=0).label('y'),
    ]).select_from(font_name.outerjoin(alignment_directive, onclause = alignment_directive.c.id == font_name.c.id)).\
        where(safe_eq_comp(font_name.c.id, id)).\
        where(and_(glyph_box.c.name == font_name.c.val,
                  glyph_box.c.unicode == unicode.c.val,
                  font_name.c.id == font_size.c.id,
                  font_name.c.id == unicode.c.id,
                  *extra_eq
                  )).\
    cte(name='generics_to_stencils')

    self.register_stmt(generics_to_stencils)

    self.insert = simple_insert(glyph_stencil, generics_to_stencils)

 def limit_clause(self, select):
     limit, offset = select._limit, select._offset
     if limit:
         if offset:
             return ' \nLIMIT %s, %s' % (
                 self.process(expression.literal(offset)),
                 self.process(expression.literal(limit)))
         return ' \nLIMIT %s' % self.process(expression.literal(limit))
     return ''

 def limit_clause(self, select, **kw):
     text = ""
     if select._limit is not None and select._offset is None:
         text += "\n LIMIT 0, {}".format(select._limit)
     else:
         text += "\n LIMIT %s, %s" % (
             self.process(sql.literal(select._offset)),
             self.process(sql.literal(select._limit)))
     return text

  def _generate_stmt(self, id) :
    #print "@@ON ID", id
    ## ugggh for y_position
    ledger_line = self.ledger_line
    n_lines = self.n_lines
    staff_space = self.staff_space
    staff_symbol = self.staff_symbol
    rhythmic_head_width = self.rhythmic_head_width
    y_position = self.y_position
    line_stencil = self.line_stencil

    ledger_line_to_line_stencil = select([
      ledger_line.c.id.label('id'),
      literal('ledger_line_to_line_stencil').label('writer'),
      literal(0).label('sub_id'),
      literal(-0.6).label('x0'),
      (case([(ledger_line.c.val < 0, staff_space.c.val * n_lines.c.val)], else_ = - staff_space.c.val) - y_position.c.val).label('y0'),
      (rhythmic_head_width.c.val + 1.0).label('x1'),
      (case([(ledger_line.c.val < 0, staff_space.c.val * n_lines.c.val)], else_ = - staff_space.c.val) - y_position.c.val).label('y1'),
      literal(0.13).label('thickness')
    ]).\
    where(safe_eq_comp(ledger_line.c.id, id)).\
    where(func.abs(ledger_line.c.val) > 0).\
    where(n_lines.c.id == staff_symbol.c.val).\
    where(staff_space.c.id == staff_symbol.c.val).\
    where(y_position.c.id == ledger_line.c.id).\
    where(staff_symbol.c.id == ledger_line.c.id).\
    where(rhythmic_head_width.c.id == staff_symbol.c.id).\
    cte(name="ledger_line_to_line_stencil", recursive = True)

    #where(safe_eq_comp(ledger_line.c.id, id))

    self.register_stmt(ledger_line_to_line_stencil)

    ledger_line_to_line_stencil_prev = ledger_line_to_line_stencil.alias(name="ledger_line_to_line_stencil_prev")

    ledger_line_to_line_stencil = ledger_line_to_line_stencil.union_all(
     select([
       ledger_line_to_line_stencil_prev.c.id,
       literal('ledger_line_to_line_stencil'),
       ledger_line_to_line_stencil_prev.c.sub_id + 1,
       ledger_line_to_line_stencil_prev.c.x0,
       ledger_line_to_line_stencil_prev.c.y0 + (staff_space.c.val * - 1.0 * ledger_line.c.val / func.abs(ledger_line.c.val)),
       ledger_line_to_line_stencil_prev.c.x1,
       ledger_line_to_line_stencil_prev.c.y1 + (staff_space.c.val * -1.0 * ledger_line.c.val / func.abs(ledger_line.c.val)),
       ledger_line_to_line_stencil_prev.c.thickness
     ]).\
     where(staff_space.c.id == staff_symbol.c.val).\
     where(staff_symbol.c.id == ledger_line_to_line_stencil_prev.c.id).\
     where(ledger_line_to_line_stencil_prev.c.id == ledger_line.c.id).\
     where(ledger_line_to_line_stencil_prev.c.sub_id < func.abs(ledger_line.c.val) - 1)
    )

    self.register_stmt(ledger_line_to_line_stencil)

    self.insert = simple_insert(line_stencil, ledger_line_to_line_stencil)

def listing(request, *args, **kwargs):
    db = pyfaf.storage.getDatabase()
    params = dict(request.REQUEST)
    params.update(kwargs)
    form = ReportFilterForm(db, params)

    filters = { 'new'       : (lambda q: q.filter(Report.problem_id==None)),
                'processed' : (lambda q: q.filter(Report.problem_id!=None)) }

    states = None
    for s in form.get_status_selection():
        # if 's' isn't in filters exceptions is thrown
        # it is intended behaviour - someone has to take care about it
        subquery = filters[s](db.session.query(
                                Report.id.label('id'),
                                literal(s.upper()).label('status')))
        states = states.union_all(subquery) if states else subquery

    # if list of statuses is empty the states variable is None
    # it means that no reports are to be selected
    # hope that there will never be a Report with id equal to -1
    if not states:
        states = (db.session.query(literal(-1).label('id'),
                                   literal('').label('status')))

    states = states.subquery()

    opsysrelease_id = form.os_release_id
    reports = (db.session.query(Report.id, literal(0).label('rank'),
            states.c.status, Report.first_occurence.label('created'),
            Report.last_occurence.label('last_change'),
            OpSysComponent.name.label('component'), Report.type)
        .join(ReportOpSysRelease)
        .join(OpSysComponent)
        .filter(states.c.id==Report.id)
        .filter((ReportOpSysRelease.opsysrelease_id==opsysrelease_id) |
            (opsysrelease_id==-1))
        .order_by(desc('last_change')))

    component_ids = form.get_component_selection()
    if component_ids:
        reports = reports.filter(Report.component_id.in_(component_ids))

    reports = reports.all()

    i = 1
    for rep in reports:
        rep.rank = i
        i += 1

    reports = paginate(reports, request)
    forward = {'reports' : reports,
               'form'  : form}

    return render_to_response('reports/list.html',
        forward, context_instance=RequestContext(request))

    def batch_get_all_groups(self, spec_filters, group_filters, user_group_filters):
        '''
        Get a list of groups by querying the group table and/or the user_group table.
        Take the union of the two results.  This method performs the general query:
        - q0: use spec_filters on the public group
        - q1: use spec_filters and group_filters on group
        - q2: use spec_filters and user_group_filters on user_group
        return union(q0, q1, q2)
        '''
        fetch_cols = [cl_group.c.uuid, cl_group.c.name, cl_group.c.owner_id]
        fetch_cols0 = fetch_cols + [cl_group.c.owner_id.label('user_id'), literal(False).label('is_admin')]
        fetch_cols1 = fetch_cols + [cl_group.c.owner_id.label('user_id'), literal(True).label('is_admin')]
        fetch_cols2 = fetch_cols + [cl_user_group.c.user_id, cl_user_group.c.is_admin]

        q0 = None
        q1 = None
        q2 = None

        if spec_filters:
            spec_clause = self.make_kwargs_clause(cl_group, spec_filters)
            q0 = select(fetch_cols0).where(spec_clause)
            q1 = select(fetch_cols1).where(spec_clause)
            q2 = select(fetch_cols2).where(spec_clause).where(cl_group.c.uuid == cl_user_group.c.group_uuid)
        if True:
            if q0 is None:
                q0 = select(fetch_cols0)
            q0 = q0.where(cl_group.c.uuid == self.public_group_uuid)
        if group_filters:
            group_clause = self.make_kwargs_clause(cl_group, group_filters)
            if q1 is None:
                q1 = select(fetch_cols1)
            q1 = q1.where(group_clause)
        if user_group_filters:
            user_group_clause = self.make_kwargs_clause(cl_user_group, user_group_filters)
            if q2 is None:
                q2 = select(fetch_cols2).where(cl_group.c.uuid == cl_user_group.c.group_uuid)
            q2 = q2.where(user_group_clause)

        # Union
        q0 = union(*filter(lambda q : q is not None, [q0, q1, q2]))

        with self.engine.begin() as connection:
            rows = connection.execute(q0).fetchall()
            if not rows:
                return []
            for i, row in enumerate(rows):
                row = dict(row)
                # TODO: remove these conversions once database schema is changed from int to str
                if isinstance(row['user_id'], int): row['user_id'] = str(row['user_id'])
                if isinstance(row['owner_id'], int): row['owner_id'] = str(row['owner_id'])
                rows[i] = row
            values = {row['uuid']: dict(row) for row in rows}
            return [value for value in values.itervalues()]

    def get(self):
        # pylint: disable=singleton-comparison
        # Cannot use `is` in SQLAlchemy filters

        key = "address:%s" % ["public", "all"][self.deep_visible()]

        value = self.cache.get(key)
        if value:
            self.write(value)
            return

        address_list = self.orm.query(
            Address.address_id,
            func.coalesce(Address.latitude, Address.manual_latitude),
            func.coalesce(Address.longitude, Address.manual_longitude),
        ).filter(func.coalesce(
            Address.latitude, Address.manual_latitude,
            Address.longitude, Address.manual_longitude
        ) != None)

        org_list = address_list \
            .join((org_address,
                   Address.address_id == org_address.c.address_id)) \
            .join((Org, Org.org_id == org_address.c.org_id)) \
            .add_columns(Org.org_id, Org.name, literal("org"))

        event_list = address_list \
            .join((event_address,
                   Address.address_id == event_address.c.address_id)) \
            .join((Event, Event.event_id == event_address.c.event_id)) \
            .add_columns(Event.event_id, Event.name, literal("event"))

        today = datetime.datetime.now().date()
        event_list = event_list.filter(Event.start_date >= today)

        if not (self.moderator and self.deep_visible()):
            org_list = org_list.filter(Org.public == True)
            event_list = event_list.filter(Event.public == True)

        address_list = org_list.union(event_list)

        obj_list = []
        for result in address_list.all():
            obj_list.append(dict(list(zip([
                "address_id", "latitude", "longitude",
                "entity_id", "name", "entity"
            ], result))))

        value = self.dump_json(obj_list)
        self.cache.set(key, value)

        self.write(value)

 def limit_clause(self, select):
     text = ""
     if select._limit is not None and select._offset is None:
         text += "\n LIMIT " + self.process(sql.literal(select._limit))
     elif select._limit is not None and select._offset is not None:
         text += "\n LIMIT %s, %s" % (
             self.process(sql.literal(select._offset)),
             self.process(sql.literal(select._limit)))
     elif select._offset is not None:
         raise exc.CompileError(
             "Cannot compile LIMIT clause, SELECT couldn't have only OFFSET"
             " clause without LIMIT")
     return text

  def _generate_stmt(self, id) :
    font_name = self.font_name
    font_size = self.font_size
    dots = self.dots
    dot_width = self.dot_width
    rhythmic_head_width = self.rhythmic_head_width
    rhythmic_event_to_dot_padding = self.rhythmic_event_to_dot_padding
    glyph_stencil = self.glyph_stencil

    rhythmic_event_to_dot_padding_a = rhythmic_event_to_dot_padding.alias(name="rhythmic_event_to_dot_padding_default")

    dots_to_stencil = select([
      dots.c.id.label('id'),
      literal('dots_to_stencil').label('writer'),
      literal(0).label('sub_id'),
      font_name.c.val.label('font_name'),
      font_size.c.val.label('font_size'),
      literal("U+E1E7").label('unicode'),
      (rhythmic_head_width.c.val + case([(rhythmic_event_to_dot_padding.c.val != None, rhythmic_event_to_dot_padding.c.val)], else_ = rhythmic_event_to_dot_padding_a.c.val)).label('x'),
      literal(0).label('y')
    ]).select_from(dots.outerjoin(rhythmic_event_to_dot_padding, onclause = rhythmic_event_to_dot_padding.c.id == dots.c.id)).\
    where(safe_eq_comp(dots.c.id, id)).\
    where(dots.c.id == font_name.c.id).\
    where(dots.c.id == font_size.c.id).\
    where(dots.c.id == rhythmic_head_width.c.id).\
    where(dots.c.val > 0).\
    where(rhythmic_event_to_dot_padding_a.c.id == -1).\
    cte(name='dots_to_stencil', recursive = True)

    self.register_stmt(dots_to_stencil)

    dots_to_stencil_left = dots_to_stencil.alias(name="dots_to_stencil_left")
    
    dots_to_stencil = dots_to_stencil.union_all(
      select([
       dots_to_stencil_left.c.id,
       dots_to_stencil_left.c.writer,
       dots_to_stencil_left.c.sub_id + 1,
       dots_to_stencil_left.c.font_name,
       dots_to_stencil_left.c.font_size,
       dots_to_stencil_left.c.unicode,
       (dots_to_stencil_left.c.x + (1.0 * dot_width.c.val / dots.c.val)),
       literal(0).label('y')
     ]).\
     where(dots.c.id == dot_width.c.id).\
     where(dots.c.id == dots_to_stencil_left.c.id).\
     where(dots.c.val > dots_to_stencil_left.c.sub_id + 1)
    )

    self.insert = simple_insert(glyph_stencil, dots_to_stencil)

    def query_current_year(self, session):
        self.event_name = c.EVENT_NAME_AND_YEAR

        # TODO: we're hacking the timezone info out of ESCHATON (final day of event). probably not the right thing to do
        self.end_date = c.DATES['ESCHATON'].replace(hour=0, minute=0, second=0, microsecond=0, tzinfo=None)

        # return registrations where people actually paid money
        # exclude: dealers
        reg_per_day = session.query(
                func.date_trunc(literal('day'), Attendee.registered),
                func.count(func.date_trunc(literal('day'), Attendee.registered))
            ) \
            .outerjoin(Attendee.group) \
            .filter(
                (
                    (Attendee.group_id != None) &
                    (Attendee.paid == c.PAID_BY_GROUP) &  # if they're paid by group
                    (Group.tables == 0) &                 # make sure they aren't dealers
                    (Group.amount_paid > 0)               # make sure they've paid something
                ) | (                                     # OR
                    (Attendee.paid == c.HAS_PAID)         # if they're an attendee, make sure they're fully paid
                )
            ) \
            .group_by(func.date_trunc(literal('day'), Attendee.registered)) \
            .order_by(func.date_trunc(literal('day'), Attendee.registered)) \
            .all()

        # now, convert the query's data into the format we need.
        # SQL will skip days without registrations
        # we need all self.num_days_to_report days to have data, even if it's zero

        # create 365 elements in the final array
        self.registrations_per_day = self.num_days_to_report * [0]

        for reg_data in reg_per_day:
            day = reg_data[0]
            reg_count = reg_data[1]

            day_offset = self.num_days_to_report - (self.end_date - day).days
            day_index = day_offset - 1

            if day_index < 0 or day_index >= self.num_days_to_report:
                log.info('ignoring some analytics data because it\'s not in range of the year before c.ESCHATON. either c.ESCHATON is set incorrectly or you have registrations starting 1 year before ESCHATON, or occuring after ESCHATON. day_index=' + str(day_index))
                continue

            self.registrations_per_day[day_index] = reg_count

        self.compute_cumulative_sum_from_registrations_per_day()

 def __init__(self, name, expr=literal(1), distinct=False):
     if distinct:
         agg = aggregates.count_distinct
     else:
         agg = aggregates.count
     super(CountMeasure, self).__init__(name, expr,
                                            agg=agg)

    def is_blocked(cls, uri):
        """Return True if the given URI is blocked."""

        if cls.query.filter(expression.literal(uri).like(cls.uri)).all():
            return True
        else:
            return False

 def wrapped(*args):
     largs = []
     for arg in args:
         if not isinstance(arg, expression.ClauseElement):
             arg = expression.literal(arg)
         largs.append(arg)
     return fn(*largs)

    def aggregate(self, query, field, rel_table, aggregator_name):
        logger.info('aggregating field %s on %s using %s', field, rel_table, aggregator_name)
        specify_model = datamodel.get_table(field.relatedModelName, strict=True)
        aggregatorNode = self.getAggregatorDef(specify_model, aggregator_name)
        if aggregatorNode is None:
            logger.warn("aggregator is not defined")
            return literal("<Aggregator not defined.>")
        logger.debug("using aggregator: %s", ElementTree.tostring(aggregatorNode))
        formatter_name = aggregatorNode.attrib.get('format', None)
        separator = aggregatorNode.attrib.get('separator', ',')
        order_by = aggregatorNode.attrib.get('orderfieldname', '')

        orm_table = getattr(models, field.relatedModelName)
        order_by = [getattr(orm_table, order_by)] if order_by != '' else []

        join_column = list(inspect(getattr(orm_table, field.otherSideName)).property.local_columns)[0]
        subquery = QueryConstruct(
            collection=query.collection,
            objectformatter=self,
            query=orm.Query([]).select_from(orm_table) \
                             .filter(join_column == getattr(rel_table, rel_table._id)) \
                             .correlate(rel_table)
        )
        subquery, formatted = self.objformat(subquery, orm_table, formatter_name)
        aggregated = blank_nulls(group_concat(formatted, separator, *order_by))
        return subquery.query.add_column(aggregated).as_scalar()

def recordset(collection, user, user_agent, recordset_info):
    "Create a record set from the records matched by a query."
    spquery = recordset_info['fromquery']
    tableid = spquery['contexttableid']

    with models.session_context() as session:
        recordset = models.RecordSet()
        recordset.timestampCreated = datetime.now()
        recordset.version = 0
        recordset.collectionMemberId = collection.id
        recordset.dbTableId = tableid
        recordset.name = recordset_info['name']
        if 'remarks' in recordset_info:
            recordset.remarks = recordset_info['remarks']
        recordset.type = 0
        recordset.createdByAgentID = user_agent.id
        recordset.SpecifyUserID = user.id
        session.add(recordset)
        session.flush()
        new_rs_id = recordset.recordSetId

        model = models.models_by_tableid[tableid]
        id_field = getattr(model, model._id)

        field_specs = field_specs_from_json(spquery['fields'])

        query, __ = build_query(session, collection, user, tableid, field_specs)
        query = query.with_entities(id_field, literal(new_rs_id)).distinct()
        RSI = models.RecordSetItem
        ins = insert(RSI).from_select((RSI.recordId, RSI.RecordSetID), query)
        session.execute(ins)

    return new_rs_id

    def auto_movie(self, q):
        orig_q = q
        q = q.lstrip().lower().split(' ')
        full_words, q = q[:-1], q[-1]
        if 'the' in full_words:
            full_words.remove('the')

        target = literal(' ').op('||')(Movie.title)

        filters = []
        for word in full_words:
            filters.append(target.ilike('%% %s %%' % word))

        filters.append(target.ilike('%% %s%%' % q))
        if len(filters) > 1:
            filters = and_(*filters)
        else:
            filters = filters[0]


        res = self.session.query(Movie.id, Movie.title, Rating.rating)\
                  .outerjoin((Rating, and_(Rating.movie_id == Movie.id,
                                           Rating.user == self.user)))\
                  .filter(filters)\
                  .order_by(func.similarity(func.lower(target), orig_q).desc())\
                  .limit(7).all()
        self.return_json(res)