def create_mapper(barcoded_location_tbl, rack_barcoded_location_tbl):
    "Mapper factory."
    bl = barcoded_location_tbl
    rbl = rack_barcoded_location_tbl.alias()
    m = mapper(BarcodedLocation, barcoded_location_tbl,
               id_attribute='barcoded_location_id',
               slug_expression=lambda cls: as_slug_expression(cls.name),
               properties=dict(
                label=column_property(
                    barcoded_location_tbl.c.label,
                    comparator_factory=CaseInsensitiveComparator
                    ),
                device=relationship(Device, uselist=False,
                                    back_populates='locations'),
                location_rack=relationship(BarcodedLocationRack,
                    uselist=False,
                    back_populates='location',
#                    lazy='joined',
                    cascade='all,delete,delete-orphan',
                    single_parent=True
                    ),
                empty=
                  column_property(~exists(# the symbol "~" negates the clause
                    select([rbl.c.barcoded_location_id],
                      bl.c.barcoded_location_id == rbl.c.barcoded_location_id
                      )),
                    deferred=True
                    ),
                )
               )
    return m

def create_mapper(tag_tbl, tag_domain_tbl, tag_predicate_tbl, tag_value_tbl,
                  tagging_tbl):
    "Mapper factory."
    m = mapper(Tag,
               tag_tbl,
               id_attribute='tag_id',
               slug_expression=lambda cls: as_slug_expression(
                                        func.concatenate(cls.domain, ':',
                                                         cls.predicate, '=',
                                                         cls.value)),
               extension=TagMapperExtension(tag_domain_tbl,
                                            tag_predicate_tbl, tag_value_tbl),
               properties=
                    dict(tagged=relationship(Tagged,
                                             secondary=tagging_tbl,
                                             back_populates='tags'),
                         domain=column_property(
                                    select([tag_domain_tbl.c.domain]) \
                                    .where(tag_tbl.c.tag_domain_id ==
                                        tag_domain_tbl.c.tag_domain_id)
                                    ),
                         predicate=column_property(
                                    select([tag_predicate_tbl.c.predicate]) \
                                    .where(tag_tbl.c.tag_predicate_id ==
                                        tag_predicate_tbl.c.tag_predicate_id)
                                    ),
                         value=column_property(
                                    select([tag_value_tbl.c.value]) \
                                    .where(tag_tbl.c.tag_value_id ==
                                        tag_value_tbl.c.tag_value_id)
                                    ),
                         )
               )
    return m

    def test_cascading_extensions(self):
        t1 = self.tables.t1

        ext_msg = []

        class Ex1(sa.orm.AttributeExtension):
            def set(self, state, value, oldvalue, initiator):
                ext_msg.append("Ex1 %r" % value)
                return "ex1" + value

        class Ex2(sa.orm.AttributeExtension):
            def set(self, state, value, oldvalue, initiator):
                ext_msg.append("Ex2 %r" % value)
                return "ex2" + value

        class A(fixtures.BasicEntity):
            pass

        class B(A):
            pass

        class C(B):
            pass

        mapper(
            A,
            t1,
            polymorphic_on=t1.c.type,
            polymorphic_identity="a",
            properties={"data": column_property(t1.c.data, extension=Ex1())},
        )
        mapper(B, polymorphic_identity="b", inherits=A)
        mc = mapper(
            C, polymorphic_identity="c", inherits=B, properties={"data": column_property(t1.c.data, extension=Ex2())}
        )

        a1 = A(data="a1")
        b1 = B(data="b1")
        c1 = C(data="c1")

        eq_(a1.data, "ex1a1")
        eq_(b1.data, "ex1b1")
        eq_(c1.data, "ex2c1")

        a1.data = "a2"
        b1.data = "b2"
        c1.data = "c2"
        eq_(a1.data, "ex1a2")
        eq_(b1.data, "ex1b2")
        eq_(c1.data, "ex2c2")

        eq_(ext_msg, ["Ex1 'a1'", "Ex1 'b1'", "Ex2 'c1'", "Ex1 'a2'", "Ex1 'b2'", "Ex2 'c2'"])

def create_mapper(refseq_gene_tbl, molecule_design_gene_tbl,
                  molecule_design_set_gene_tbl,
                  molecule_design_tbl,
                  molecule_design_pool_tbl):
    "Mapper factory."
    rsg = refseq_gene_tbl
    mdg = molecule_design_gene_tbl
    mdsg = molecule_design_set_gene_tbl
    md = molecule_design_tbl
    mdp = molecule_design_pool_tbl
    m = mapper(Gene, rsg,
        id_attribute='gene_id',
        slug_expression=lambda cls: as_slug_expression(cls.accession),
        properties=dict(
            accession=column_property(
                rsg.c.accession,
                comparator_factory=CaseInsensitiveComparator
                ),
            locus_name=column_property(
                rsg.c.locus_name,
                comparator_factory=CaseInsensitiveComparator
                ),
            molecule_designs=
                relationship(
                    MoleculeDesign, viewonly=True,
                    secondary=mdg,
                    primaryjoin=(mdg.c.gene_id == rsg.c.gene_id),
                    secondaryjoin=(md.c.molecule_design_id ==
                                            mdg.c.molecule_design_id),
                    foreign_keys=(mdg.c.molecule_design_id,
                                  mdg.c.gene_id),
                    back_populates='genes',
                    ),
            molecule_design_pools=
                relationship(
                    MoleculeDesignPool, viewonly=True,
                    secondary=mdsg,
                    primaryjoin=(mdsg.c.gene_id == rsg.c.gene_id),
                    secondaryjoin=(mdsg.c.molecule_design_set_id ==
                                            mdp.c.molecule_design_set_id),
                    foreign_keys=(mdsg.c.molecule_design_set_id,
                                  mdsg.c.gene_id),
                    ),
            species=relationship(Species, uselist=False,
                                 back_populates='genes',
#                                 lazy='joined'
                                 ),
            ),
        )
    Gene.name = synonym('locus_name')
    return m

def create_mapper(experiment_metadata_tbl,
                  experiment_metadata_iso_request_tbl):
    "Mapper factory."
    m = mapper(
           ExperimentMetadata, experiment_metadata_tbl,
           id_attribute='experiment_metadata_id',
           slug_expression=lambda cls: as_slug_expression(cls.label),
           properties=
             dict(label=column_property(
                      experiment_metadata_tbl.c.label,
                      comparator_factory=CaseInsensitiveComparator
                      ),
                  experiment_design=relationship(ExperimentDesign,
                                    uselist=False,
                                    cascade='all,delete,delete-orphan',
                                    back_populates='experiment_metadata'),
                  subproject=relationship(Subproject, uselist=False),
                  lab_iso_request=relationship(LabIsoRequest, uselist=False,
                            secondary=experiment_metadata_iso_request_tbl,
                            back_populates='experiment_metadata',
                            cascade='all,delete,delete-orphan',
                            single_parent=True),

                  experiment_metadata_type=relationship(ExperimentMetadataType,
                                                        uselist=False),
                  ),
           )
    return m

 def address_count(cls):
     counter(cls.id)
     return column_property(
         select([func.count(Address.id)]).
         where(Address.user_id == cls.id).
         as_scalar()
     )

def create_mapper(iso_request_tbl, worklist_series_iso_request_tbl,
                  iso_request_pool_set_tbl):
    "Mapper factory."
    wsir = worklist_series_iso_request_tbl
    m = mapper(IsoRequest, iso_request_tbl,
               id_attribute='iso_request_id',
               properties=
                    dict(owner=column_property(iso_request_tbl.c.owner,
                                comparator_factory=CaseInsensitiveComparator
                                ),
                         isos=relationship(Iso, back_populates='iso_request'),
                         worklist_series=relationship(WorklistSeries,
                                uselist=False, secondary=wsir,
                                cascade='all,delete,delete-orphan',
                                single_parent=True),
                         molecule_design_pool_set=relationship(
                                MoleculeDesignPoolSet,
                                secondary=iso_request_pool_set_tbl,
                                uselist=False, single_parent=True,
                                cascade='all,delete,delete-orphan')
                    ),
               polymorphic_on=iso_request_tbl.c.iso_type,
               polymorphic_identity=ISO_TYPES.BASE,
               )
    return m

def create_mapper(rack_specs_tbl):
    "Mapper factory."
    rs = rack_specs_tbl
    polymorphic_select = select([
        rs,
        (case([(rs.c.has_movable_subitems,
                literal(RACK_SPECS_TYPES.TUBE_RACK_SPECS))],
              else_=literal(RACK_SPECS_TYPES.PLATE_SPECS))).label(
                                                            'rackspecs_type')
        ],
        ).alias('rackspecs')
    m = mapper(RackSpecs, polymorphic_select,
            id_attribute='rack_specs_id',
            slug_expression=lambda cls: as_slug_expression(cls.name),
            properties=dict(
                manufacturer=relationship(Organization),
                shape=relationship(RackShape, uselist=False,
                                   back_populates='specs'),
                rack_specs_type=
                        column_property(polymorphic_select.c.rackspecs_type),
                ),
            polymorphic_on=polymorphic_select.c.rackspecs_type,
            polymorphic_identity=RACK_SPECS_TYPES.RACK_SPECS,
            )
    RackSpecs.has_tubes = synonym('has_movable_subitems')
    return m

    def test_attrs_props_prop_added_after_configure(self):
        class AnonClass(object):
            pass

        from sqlalchemy.orm import mapper, column_property
        from sqlalchemy.ext.hybrid import hybrid_property
        m = mapper(AnonClass, self.tables.users)

        eq_(
            set(inspect(AnonClass).attrs.keys()),
            set(['id', 'name']))
        eq_(
            set(inspect(AnonClass).all_orm_descriptors.keys()),
            set(['id', 'name']))

        m.add_property('q', column_property(self.tables.users.c.name))

        def desc(self):
            return self.name
        AnonClass.foob = hybrid_property(desc)

        eq_(
            set(inspect(AnonClass).attrs.keys()),
            set(['id', 'name', 'q']))
        eq_(
            set(inspect(AnonClass).all_orm_descriptors.keys()),
            set(['id', 'name', 'q', 'foob']))

def create_mapper(rack_tbl, rack_barcoded_location_tbl):
    "Mapper factory."
    rbl = rack_barcoded_location_tbl
    m = mapper(Rack, rack_tbl,
        id_attribute='rack_id',
        slug_expression=lambda cls: as_slug_expression(cls.barcode),
        properties=dict(
            label=column_property(
                rack_tbl.c.label,
                comparator_factory=CaseInsensitiveComparator
                ),
            specs=relationship(RackSpecs,
                               innerjoin=True, uselist=False),
            status=relationship(ItemStatus,
                                innerjoin=True, uselist=False),
            location_rack=relationship(BarcodedLocationRack,
                                         uselist=False,
                                         back_populates='rack',
                                         cascade='all,delete,delete-orphan'),
            _location=relationship(BarcodedLocation, viewonly=True,
                uselist=False,
                secondary=rbl,
                foreign_keys=(rbl.c.rack_id, rbl.c.barcoded_location_id),
                ),
            ),
        polymorphic_on=rack_tbl.c.rack_type,
        polymorphic_identity=RACK_TYPES.RACK,
        )
    return m

    def iri_class_decorator(klass):
        iri_hpropname = '_'+iri_propname
        setattr(klass, iri_hpropname,
                column_property(id_to_iri(getattr(klass, iri_id_colname))))

        def iri_accessor(self):
            return getattr(self, iri_hpropname)

        def iri_expression(klass):
            return id_to_iri(getattr(klass, iri_id_colname))

        def iri_setter(self, val):
            setattr(self, iri_hpropname, val)
            setattr(self, iri_id_colname, iri_to_id(val))

        def iri_deleter(self):
            setattr(self, iri_id_colname, None)

        col = getattr(klass, iri_id_colname)
        if not col.property.columns[0].nullable:
            iri_deleter = None
        prop = hybrid_property(
            iri_accessor, iri_setter, iri_deleter, iri_expression)
        setattr(klass, iri_propname, prop)
        return klass

def GeometryColumn(*args, **kw):
    """Define a declarative column property with GIS behavior.
    
    This just produces orm.column_property() with the appropriate
    extension and comparator_factory arguments.  The given arguments
    are passed through to Column.  The declarative module extracts
    the Column for inclusion in the mapped table.
    
    This method can also be used for non-declarative mappings to 
    set the properties for a geometry column when defining the mapping.
    
    """
    if kw.has_key("comparator"):
        comparator = kw.pop("comparator")
    else:
        comparator = SpatialComparator
    
    if isinstance(args[0], GeometryExtensionColumn):
        # if used for non-declarative, use the column of the table definition
        column = args[0]
        args = args[1:]
    else:
        # if used for declarative, create a new column
        column = GeometryExtensionColumn(*args, **kw) 
    
    return column_property(
        column, 
        extension=SpatialAttribute(), 
        comparator_factory=comparator
    )

def sessionmaker(dbconfig):
    dbconfig = dbconfig.copy()
    conn_str = dbconfig.pop('url')
    if 'schema' in dbconfig:
        schema = dbconfig.pop('schema')
    else:
        schema = None

    engine = create_engine(conn_str, **dbconfig)
    mappers, tables, Session = reflect(engine, models, schema)

    # add mapper relationships
    mappers['Data'].add_properties({
        'versions': relationship(models.Version,
                                 lazy='dynamic',
                                 backref=backref('ref',
                                                 lazy='joined'))
    })

    mappers['Version'].add_properties({
        'data': deferred(tables['version'].c['data']),
        'size': column_property(func.length(tables['version'].c['data']))
    })

    Session.class_.mappers = mappers

    return Session

 def __declare_last__(cls):
     alias = cls.__table__.alias()
     cls.calc_is_current_version = column_property(
         select([func.max(alias.c.version_id) == cls.version_id]).where(
             alias.c.id == cls.id
         )
     )

 def mapper_kw (self) :
     return {'inherits' : self.classes['element'],
             'polymorphic_identity' : self.name,
             'properties' : {'_id'      : column_property(self.table.c.id, self.tables['element'].c.id),
                             '_charges' : self.table.c.charges,
                             '_errors'  : self.table.c.errors,
                             'io'       : relationship(self.classes['neural_network_io'],
                                                       foreign_keys=self.tables['neural_network_io'].c.layer_id)}}

def GISColumn(*args, **kw):
  """Define a declarative column property with GIS behavior.
  This just produces orm.column_property() with the appropriate
  extension and comparator_factory arguments.  The given arguments
  are passed through to Column.  The declarative module extracts
  the Column for inclusion in the mapped table.
  """
  return column_property(
    Column(*args, **kw), 
    extension=GisAttribute(), 
    comparator_factory=GisComparator
  )

def GeometryColumn(*args, **kw):
    """Define a declarative column property with GIS behavior.
    
    This just produces orm.column_property() with the appropriate
    extension and comparator_factory arguments.  The given arguments
    are passed through to Column.  The declarative module extracts
    the Column for inclusion in the mapped table.
    
    """
    sfs = False
    if kw.has_key("sfs"): sfs = kw.pop("sfs")
    if sfs:
        return column_property(
                Column(*args, **kw), 
                extension=SpatialAttribute(), 
                comparator_factory=SFSComparator
        )
    return column_property(
        Column(*args, **kw), 
        extension=SpatialAttribute(), 
        comparator_factory=SQLMMComparator
    )

def includeme(config):
    tables = config.registry['metadata'].tables

    orm.mapper(Account, tables['account'],
        properties={
            'count_content': orm.column_property(
                sql.select(
                    [sql.func.count()],
                    tables['account'].c.id == tables['content'].c.owner_id
                ).label('count_content'),
                deferred=True
            )
        })

def create_mapper(organization_tbl):
    "Mapper factory."
    m = mapper(Organization, organization_tbl,
               id_attribute='organization_id',
               slug_expression=lambda cls: as_slug_expression(cls.name),
               properties=dict(
                      name=column_property(
                          organization_tbl.c.name,
                          comparator_factory=CaseInsensitiveComparator
                          ),
                      ),
                  )
    return m

    def __declare_last__(cls):
        super(cls, cls).__declare_last__()

        s_alias = cls.__table__.alias()
        cls.normalized_score = column_property(
            select([
                (cls.score - func.min(s_alias.c.score)) / (func.max(s_alias.c.score) - func.min(s_alias.c.score)) * 100
            ]).
            select_from(join(Answer, s_alias, s_alias.c.answer_id == Answer.id)).
            where(and_(
                Answer.active == True,
                s_alias.c.assignment_id == cls.assignment_id,
            ))
        )