def setup_mappers(cls):
        foo = cls.tables.foo
        subfoo = cls.tables.subfoo

        mapper(Foo, foo)
        mapper(SubFoo, subfoo, inherits=Foo)
        MutableDict.associate_with_attribute(Foo.data)

    def define_tables(cls, metadata):
        import json

        class JSONEncodedDict(TypeDecorator):
            impl = VARCHAR(50)

            def process_bind_param(self, value, dialect):
                if value is not None:
                    value = json.dumps(value)

                return value

            def process_result_value(self, value, dialect):
                if value is not None:
                    value = json.loads(value)
                return value

        MutableDict = cls._type_fixture()
        MutableDict.associate_with(JSONEncodedDict)

        Table('foo', metadata,
            Column('id', Integer, primary_key=True,
                            test_needs_autoincrement=True),
            Column('data', JSONEncodedDict),
            Column('unrelated_data', String(50))
        )

def upgrade():
    meta_catalogstar = sa.Column('meta', MutableDict.as_mutable(JSON),
                                 default={})
    op.add_column('catalog_star', meta_catalogstar)

    meta_obs = sa.Column('meta', MutableDict.as_mutable(JSON),
                         default={})
    op.add_column('observation', meta_obs)

def associate_with(sqltype):
    # TODO(leizhang) When we removed sqlalchemy 0.7 dependence
    # we can import MutableDict directly and remove ./mutable.py
    try:
        from sqlalchemy.ext.mutable import MutableDict as sa_MutableDict
        sa_MutableDict.associate_with(Json)
    except ImportError:
        from heat.db.sqlalchemy.mutable import MutableDict
        MutableDict.associate_with(Json)

 def document(self):
     d = MutableDict()
     d.update(self._document)
     d.update({
         'kind': self.kind,
         'id': self.id,
         'source': self.source,
         'timestamp': self.timestamp,
     })
     return d

def downgrade():
    op.drop_column('catalog', 'metajson')
    meta = sa.Column('meta', MutableDict.as_mutable(HSTORE),
                     nullable=False,
                     default={},
                     index=True)
    op.add_column('catalog', meta)

    def define_tables(cls, metadata):
        import json

        class JSONEncodedDict(TypeDecorator):
            impl = VARCHAR(50)

            def process_bind_param(self, value, dialect):
                if value is not None:
                    value = json.dumps(value)

                return value

            def process_result_value(self, value, dialect):
                if value is not None:
                    value = json.loads(value)
                return value

        MutableDict = cls._type_fixture()

        Table(
            "foo",
            metadata,
            Column(
                "id", Integer, primary_key=True, test_needs_autoincrement=True
            ),
            Column("data", MutableDict.as_mutable(JSONEncodedDict)),
            Column("non_mutable_data", JSONEncodedDict),
            Column("unrelated_data", String(50)),
        )

    def define_tables(cls, metadata):
        MutableDict = cls._type_fixture()

        mutable_pickle = MutableDict.as_mutable(PickleType)
        Table(
            'foo', metadata,
            Column(
                'id', Integer, primary_key=True,
                test_needs_autoincrement=True),
            Column('data', mutable_pickle, default={}),
        )

    def __init__(self, name=u"", type=u"node", id=None, schema=None, attrs=None, system_attrs=None, orderpos=None):
        self.name = name
        if not isinstance(type, unicode):
            warn("type arg of Node should be unicode (hint: don't create nodes with Node(type='{}')!)".format(type), DeprecationWarning)

        if "/" in type:
            warn("use separate type and schema parameters instead of 'type/schema'", DeprecationWarning)
            type, schema = type.split("/")

        self.type = type
        self.attrs = MutableDict()
        self.system_attrs = MutableDict()
        if id:
            self.id = id
        if schema:
            self.schema = schema
        if attrs:
            self.attrs.update(attrs)
        if system_attrs:
            self.system_attrs.update(system_attrs)
        if orderpos:
            self.orderpos = orderpos

            return cls(value)
        return super(cls).coerce(key, value)


class NestedMutable(Mutable):
    """SQLAlchemy `mutable` extension with nested change tracking."""
    @classmethod
    def coerce(cls, key, value):
        """Convert plain dictionary to NestedMutable."""
        if value is None:
            return value
        if isinstance(value, cls):
            return value
        if isinstance(value, dict):
            return NestedMutableDict.coerce(key, value)
        if isinstance(value, list):
            return NestedMutableList.coerce(key, value)
        return super(cls).coerce(key, value)


class MutableJson(JSONType):
    """JSON type for SQLAlchemy with change tracking at top level."""


class NestedMutableJson(JSONType):
    """JSON type for SQLAlchemy with nested change tracking."""


MutableDict.associate_with(MutableJson)
NestedMutable.associate_with(NestedMutableJson)

@author: peterb
'''
from blueshed.model_helpers.sqla_views import view
from blueshed.model_helpers.sql_extensions import JSONEncodedDict
from sqlalchemy.ext.mutable import MutableDict
from sqlalchemy.sql.expression import select, join
from sqlalchemy.sql.functions import func

from examples.simple.model import *
from blueshed.model_helpers.access_model import Person, Permission,\
    person_permissions_permission


Person._token = Column(String(80))
Person._preferences = Column(MutableDict.as_mutable(JSONEncodedDict(255)))
Person.firstname = Column(String(80))
Person.lastname = Column(String(80))
Person.photo = Column(String(128))

'''
    An example View
'''
q = select([Person.id.label('id'), 
            Person.email.label('email'),
            func.count(Permission.id).label('permission_count')]).\
            select_from(join(Person,
                             person_permissions_permission,
                             Person.id==person_permissions_permission.c.permissions_id).\
                        join(Permission,
                             Permission.id==person_permissions_permission.c.permission_id)).\

class Node(DeclarativeBase, NodeMixin):

    """Base class for Nodes which holds all SQLAlchemy fields definitions
    """
    __metaclass__ = BaseNodeMeta
    __tablename__ = "node"
    __versioned__ = {
        "base_classes": (NodeVersionMixin, MtVersionBase, DeclarativeBase),
        "exclude": ["subnode", "system_attrs"]
    }

    id = C(Integer, node_id_seq, server_default=node_id_seq.next_value(), primary_key=True)
    type = C(Unicode, index=True)
    schema = C(Unicode, index=True)
    name = C(Unicode, index=True)
    orderpos = C(Integer, default=1, index=True)
    fulltext = deferred(C(Unicode))
    # indicate that this node is a subnode of a content type node
    # subnode exists just for performance reasons and is updated by the database
    # unversioned
    subnode = C(Boolean, server_default="false")

    attrs = deferred(C(MutableDict.as_mutable(JSONB)))
    # Migration from old mediatum: all attributes starting with "system." go here.
    # We should get rid of most (all?) such attributes in the future.
    # unversioned
    system_attrs = deferred(C(MutableDict.as_mutable(JSONB)))

    @hybrid_property
    def a_expr(self):
        """ see: Attributes"""
        raise Exception("node.a_expr")
        if "_attributes_accessor" not in self.__dict__:
            setattr(self, "_attributes_accessor", Attributes(self, "attrs"))
        return self._attributes_accessor

    @a_expr.expression
    def a(self):
        """ see: AttributesExpression"""
        if "_attributes_accessor" not in self.__dict__:
            setattr(self, "_attributes_accessor", AttributesExpressionAdapter(self, "attrs"))
        return self._attributes_accessor

    @a.setter
    def a_set(self, value):
        raise NotImplementedError("immutable!")


    @hybrid_property
    def sys(self):
        """ see: Attributes"""
        if "_system_attributes_accessor" not in self.__dict__:
            setattr(self, "_system_attributes_accessor", Attributes(self, "system_attrs"))
        return self._system_attributes_accessor

    @sys.expression
    def sys_expr(self):
        """ see: AttributesExpression"""
        if "_system_attributes_accessor" not in self.__dict__:
            setattr(self, "_system_attributes_accessor", AttributesExpressionAdapter(self, "system_attrs"))
        return self._system_attributes_accessor

    @a.setter
    def sys_set(self, value):
        raise NotImplementedError("immutable!")


    def __init__(self, name=u"", type=u"node", id=None, schema=None, attrs=None, system_attrs=None, orderpos=None):
        self.name = name
        if not isinstance(type, unicode):
            warn("type arg of Node should be unicode (hint: don't create nodes with Node(type='{}')!)".format(type), DeprecationWarning)

        if "/" in type:
            warn("use separate type and schema parameters instead of 'type/schema'", DeprecationWarning)
            type, schema = type.split("/")

        self.type = type
        self.attrs = MutableDict()
        self.system_attrs = MutableDict()
        if id:
            self.id = id
        if schema:
            self.schema = schema
        if attrs:
            self.attrs.update(attrs)
        if system_attrs:
            self.system_attrs.update(system_attrs)
        if orderpos:
            self.orderpos = orderpos

    @property
    def slow_content_children_for_all_subcontainers(self):
        """
        !!! very slow, use content_children_for_all_subcontainers instead!!!
        Collects all Content nodes in all subcontainers of this node.
        This excludes content nodes that are children of other content nodes.
        """
        warn("very slow, use content_children_for_all_subcontainers instead", DeprecationWarning)
        from contenttypes.data import Content
        from core import db
#.........这里部分代码省略.........

            return json.loads(value)


class AstonFrameBinary(TypeDecorator):
    impl = LargeBinary

    def process_bind_param(self, value, dialect):
        if value is not None:
            return value.compress()

    def process_result_value(self, value, dialect):
        if value is not None:
            return decompress(value)


MutableDict.associate_with(JSONDict)

Base = declarative_base()


def initialize_sql(engine):
    DBSession = scoped_session(sessionmaker(expire_on_commit=False))
    DBSession.configure(bind=engine)
    Base.metadata.bind = engine
    Base.metadata.create_all(engine)
    return DBSession


def quick_sqlite(filename):
    from sqlalchemy import create_engine

    impl = HSTORE

    def process_bind_param(self, value, dialect):
        if not isinstance(value, dict):
            return value
        ret = {k: json.dumps(v) for k, v in value.items()}
        return ret

    def process_result_value(self, value, dialect):
        if not value:
            return MutableDict()
        ret = MutableDict({k: json.loads(v) for k, v in value.items()})
        return ret


MutableDict.associate_with(JSONValuesColumn)


class IntegerEnumColumn(types.TypeDecorator):
    impl = types.INTEGER

    def __init__(self, enum_values):
        super(IntegerEnumColumn, self).__init__()
        self.enum_values = enum_values
        self.reverse_enum_values = reverse_dict(enum_values)

    def process_bind_param(self, value, dialect):
        return self.reverse_enum_values.get(value, value)

    def process_result_value(self, value, dialect):
        return self.enum_values.get(value, value)

#XXX NOTICE XXX DO NOT NAME THINGS types.py it breaks EVERYTHING
from sqlalchemy.types import PickleType
from sqlalchemy.dialects import postgres,postgresql
from sqlalchemy.ext.mutable import MutableDict

def array_base(column_type):
    array = PickleType()
    array.with_variant(postgres.ARRAY(column_type), 'postgres')
    array.with_variant(postgres.ARRAY(column_type), 'postgresql')
    return array

Array=array_base

_DictType = MutableDict.as_mutable(PickleType)
_DictType.with_variant(MutableDict.as_mutable(postgres.HSTORE), 'postgres')
#_DictType.with_variant(MutableDict.as_mutable(postgresql.HSTORE), 'postgresql')
#_DictType.with_variant(MutableDict.as_mutable(postgresql.HSTORE), 'psycopg2')
#_DictType.with_variant(MutableDict.as_mutable(postgresql.HSTORE), 'postgresql+psycopg2')
DictType=_DictType #FIXME not working as hstore :/


__all__=[
    'Array',
    'DictType',
]

#ArrayFloat = PickleType()
#ArrayFloat.with_variant(postgresql.ARRAY(Float), 'postgresql')

#ArrayString = PickleType()
#ArrayString.with_variant(postgresql.ARRAY(String), 'postgresql')

    impl = VARCHAR

    def process_bind_param(self, value, dialect):
        if value is not None:
            value = str(value)

        return value

    def process_result_value(self, value, dialect):
        if value is not None:
            value = ast.literal_eval(value)
        return value

ARRAY_TYPE = Array()
JSON_TYPE = MutableDict.as_mutable(JSONEncodedDict)

BASE = declarative_base()

class Product(BASE):
    """docstring for Product."""

    __tablename__ = "products"

    id = Column('id', Integer, primary_key=True, autoincrement=False)
    style_no = Column('style_no', String)
    title = Column('title', String)
    keywords = Column('keywords', ARRAY_TYPE)
    owner = Column('owner', String)
    modify_time = Column('modify_time', Date)
    update = Column('update', Date, default=date.today)

    _null = None
    _type = object

    def process_bind_param(self, value, dialect):
        return json.dumps(value)

    def process_literal_param(self, value, dialect):
        return value

    def process_result_value(self, value, dialect):
        try:
            value = json.loads(value)
        except (ValueError, TypeError):
            value = self._null
        return value


class List(Json):
    _null = []
    _type = list


class Dict(Json):
    _null = {}
    _type = dict


MutableDict.associate_with(Dict)

Base = declarative_base()

def get_data_element_postgres_extensions(task, indexes):
    if indexes:
        q = task.__class__.data[indexes]
    else:
        q = task.__class__.data

    s = object_session(task)
    tup = s.query(q).filter_by(id=task.id).one()
    return tup[0]


class json_array_length(GenericFunction):
    type = Integer


def get_data_size_postgres_extensions(task, indexes):
    if indexes:
        q = task.__class__.data[indexes]
    else:
        q = task.__class__.data

    s = object_session(task)
    tup = s.query(json_array_length(q)).filter_by(id=task.id).one()
    return tup[0]

MutableJSONDict = MutableDict.as_mutable(psqlJSON)
JSON = psqlJSON
get_data_element = get_data_element_postgres_extensions
get_data_size = get_data_size_postgres_extensions

 def hstore_table_for(self, name):
     return sa.Table(name, self.Base.metadata,
                     sa.Column('id', sa.Integer, primary_key=True),
                     sa.Column('data', MutableDict.as_mutable(pg.HSTORE)))

class Json(types.TypeDecorator):
    impl = types.Text

    def process_bind_param(self, value, dialect):
        return dumps(value)

    def process_result_value(self, value, dialect):
        return loads(value)

# TODO(leizhang) When we removed sqlalchemy 0.7 dependence
# we can import MutableDict directly and remove ./mutable.py
try:
    from sqlalchemy.ext.mutable import MutableDict as sa_MutableDict
    sa_MutableDict.associate_with(Json)
except ImportError:
    from heat.db.sqlalchemy.mutable import MutableDict
    MutableDict.associate_with(Json)


class HeatBase(object):
    """Base class for Heat Models."""
    __table_args__ = {'mysql_engine': 'InnoDB'}
    __table_initialized__ = False
    created_at = sqlalchemy.Column(sqlalchemy.DateTime,
                                   default=timeutils.utcnow)
    updated_at = sqlalchemy.Column(sqlalchemy.DateTime,
                                   onupdate=timeutils.utcnow)

    def save(self, session=None):