class RideGeo(Base): __tablename__ = "ride_geo" __table_args__ = { "mysql_engine": "MyISAM", "mysql_charset": "utf8", } # MyISAM for spatial indexes ride_id = Column(BigInteger, ForeignKey("rides.id"), primary_key=True) start_geo = GeometryColumn(Point(2), nullable=False) end_geo = GeometryColumn(Point(2), nullable=False) def __repr__(self): return "<{0} ride_id={1} start={2}>".format(self.__class__.__name__, self.ride_id, self.start_geo)
class RideGeo(Base): __tablename__ = 'ride_geo' __table_args__ = { 'mysql_engine': 'MyISAM', 'mysql_charset': 'utf8' } # MyISAM for spatial indexes ride_id = Column(BigInteger, ForeignKey('rides.id'), primary_key=True) start_geo = GeometryColumn(Point(2), nullable=False) end_geo = GeometryColumn(Point(2), nullable=False) def __repr__(self): return '<{0} ride_id={1} start={2}>'.format(self.__class__.__name__, self.ride_id, self.start_geo)
class Feature(Base): """A GeoNames geogaphical feature. """ __tablename__ = "feature" geonameid = Column(Integer, primary_key=True) name = Column(Unicode) asciiname = Column(Unicode) alternatenames = Column(Unicode) latitude = Column(Float) longitude = Column(Float) feature_class = Column("feature class", Unicode) feature_code = Column("feature code", Unicode) country_code = Column("country code", Unicode) cc2 = Column(Unicode) admin1_code = Column("admin1 code", Unicode) admin2_code = Column("admin2 code", Unicode) admin3_code = Column("admin3 code", Unicode) admin4_code = Column("admin4 code", Unicode) population = Column(Integer) elevation = Column(Integer) gtopo30 = Column(Integer) timezone = Column(Unicode) modification_date = Column("modification date", Date) uf = Column(Unicode(2)) position = GeometryColumn(Point(2), comparator=SQLiteComparator)
class Spot(Base, GeometryTableMixIn): __tablename__ = 'spots' spot_id = Column(Integer, primary_key=True) spot_height = Column(Numeric(precision=10, scale=2, asdecimal=False)) spot_location = GeometryColumn(Point(2, spatial_index=False), nullable=True)
class Spot(Base, GeometryTableMixIn): __tablename__ = 'spots' spot_id = Column(Integer, primary_key=True) spot_height = Column(Numeric(asdecimal=False)) spot_goodness = Column(Boolean) spot_location = GeometryColumn(Point(2))
class Item(Base): __tablename__ = 'item' id = Column(Integer, primary_key=True) title = Column(String(50), nullable=False) price = Column(String(20), nullable=False) desc = Column(String(150), nullable=False) location = GeometryColumn(Point(2), nullable=False) user_id = Column(Integer, ForeignKey('user.id')) def __init__(self, title, price, desc, location=''): self.title = title self.price = price self.desc = desc self.location = location def json(self): item_json = { 'title': self.title, 'price': self.price, 'desc': self.desc, 'user': self.user.json() } if deployed_on_sae: item_json['latlng'] = { 'lat': dbsession.scalar(self.location.x), 'lng': dbsession.scalar(self.location.y) } return item_json
class StudentUnion(Base, GeometryTableMixIn): __tablename__ = 'student_union' __table_args__ = { "schema": 'foss4g', "autoload": True, "autoload_with": Session.bind } the_geom = GeometryColumn(Point(srid=4326))
class Museum(Base, GeometryTableMixIn): __tablename__ = 'museum' __table_args__ = { "schema": 'foss4g', "autoload": True, "autoload_with": Session.bind } the_geom = GeometryColumn(Point(srid=4326))
class BicycleRental(Base, GeometryTableMixIn): __tablename__ = 'bicycle_rental' __table_args__ = { "schema": 'foss4g', "autoload": True, "autoload_with": Session.bind } the_geom = GeometryColumn(Point(srid=4326))
class Place(Base): __tablename__ = 'places' id = Column(types.Integer, primary_key=True) name = Column(types.String, nullable=False) category_id = Column(types.Integer, ForeignKey('categories.cid')) category = relationship(Category) the_geom = GeometryColumn(Point(dimension=2, srid=4326))
class Trace(Base, ResourceMixin): __tablename__ = 'traces' geometry = GeometryColumn(Point(2)) altitude = Column(Float) device_timestamp = Column(DateTime) # The time from the device ride_id = Column(Integer, ForeignKey('rides.id')) ride = relationship('Ride', cascade='all, delete', primaryjoin='Ride.id==Trace.ride_id')
class User(Base): __tablename__ = 'users' id = Column(Integer, Sequence('user_id_seq'), primary_key=True) name = Column(String(50), nullable=False) password = Column(String(12), nullable=False) location = GeometryColumn(Point(2), nullable=False) def __init__(self, name, password, location): self.name = name self.password = password self.location = location
class Minarepo(Base): __tablename__ = "minarepo" id = Column('id', Integer, primary_key=True, autoincrement=True) type = Column("type", String(100)) user = Column("user", String(100)) geo = GeometryColumn("geo", Point(dimension=2, srid=4326), nullable=False, comparator=MySQLComparator) timestamp = Column("timestamp", DATETIME, nullable=False) image = Column("image", MEDIUMTEXT, nullable=True) comment = Column("comment", MEDIUMTEXT, nullable=True)
class Item(Base): __tablename__ = 'items' id = Column(Integer, Sequence('item_id_seq'), primary_key=True) title = Column(String(50), nullable=False) price = Column(String(12), nullable=False) desc = Column(String(200), nullable=False) location = GeometryColumn(Point(2), nullable=False) def __init__(self, title, price, desc, location): self.title = title self.price = price self.desc = desc self.location = location
class OSRMNode(Base): """Define a node in the routing network""" __tablename__ = "osrmnodes" osm_id = Column(Integer, primary_key=True) lat = Column(Integer) lon = Column(Integer) bollard = Column(Boolean) traffic_light = Column(Boolean) geom = GeometryColumn(Point(2), comparator=PGComparator) # Edges associated to this node in_edges = relationship("OSRMEdge", foreign_keys='OSRMEdge.sink') out_edges = relationship("OSRMEdge", foreign_keys='OSRMEdge.source') def __init__(self, osm_id, lat, lon, bollard, traffic_light): self.osm_id = osm_id self.lat = lat self.lon = lon self.bollard = bool(bollard) self.traffic_light = bool(traffic_light) self.geom = WKTSpatialElement("POINT(%0.6f %0.6f)" % (lon / 1E5, lat / 1E5))
class User(Member): __tablename__ = "member_user" __mapper_args__ = {'polymorphic_identity': 'user'} id = Column(String(32), ForeignKey('member.id', onupdate="cascade"), primary_key=True) last_check = Column( DateTime(), nullable=False, default=now, doc= "The last time the user checked their messages. You probably want to use the new_messages derived boolean instead." ) new_messages = Column(Boolean(), nullable=False, default=False) # FIXME: derived location_current = Golumn( Point(2), nullable=True, doc= "Current location, for geo-targeted assignments. Nullable for privacy") location_updated = Column(DateTime(), nullable=False, default=now) #dob = Column(DateTime(), nullable=True) # Needs to be stored in user settings but not nesiserally in the main db record email = Column(Unicode(250), nullable=True) email_unverified = Column(Unicode(250), nullable=True) summary_email_start = Column( DateTime(), nullable=True, doc= "users can opt into to haveing summary summary emails rather than an email on each notification" ) summary_email_interval = Column(Interval(), nullable=True) login_details = relationship("UserLogin", backref=('user'), cascade="all,delete-orphan") flaged = relationship( "FlaggedEntity", backref=backref('raising_member'), cascade="all,delete-orphan", primaryjoin="Member.id==FlaggedEntity.raising_member_id") __to_dict__ = copy.deepcopy(Member.__to_dict__) _extra_user_fields = { 'location_current': lambda member: location_to_string(member.location_home), 'location_updated': None, } __to_dict__['default'].update(_extra_user_fields) __to_dict__['full'].update(_extra_user_fields) def __unicode__(self): return self.name or self.id def hash(self): h = hashlib.md5(Member.hash(self)) for field in ("email", ): h.update(unicode(getattr(self, field)).encode('utf-8')) for login in self.login_details: h.update(login.token) return h.hexdigest() @property def email_normalized(self): return self.email or self.email_unverified
class Member(Base): "Abstract class" __tablename__ = "member" __type__ = Column(member_type) __mapper_args__ = { 'polymorphic_on': __type__, 'extension': CacheChangeListener() } _member_status = Enum("pending", "active", "suspended", name="member_status") id = Column(String(32), primary_key=True) name = Column(Unicode(250), nullable=False) join_date = Column(DateTime(), nullable=False, default=now) status = Column(_member_status, nullable=False, default="pending") avatar = Column(String(40), nullable=True) utc_offset = Column(Interval(), nullable=False, default="0 hours") location_home = Golumn(Point(2), nullable=True) payment_account_id = column_property( Column(Integer(), ForeignKey('payment_account.id'), nullable=True), extension=MemberPaymentAccountIdChangeListener()) #payment_account_id = Column(Integer(), ForeignKey('payment_account.id'), nullable=True) salt = Column(Binary(length=256), nullable=False, default=_generate_salt) description = Column(UnicodeText(), nullable=False, default=u"") verified = Column(Boolean(), nullable=False, default=False) extra_fields = Column(JSONType(mutable=True), nullable=False, default={}) num_following = Column(Integer(), nullable=False, default=0, doc="Controlled by postgres trigger") num_followers = Column(Integer(), nullable=False, default=0, doc="Controlled by postgres trigger") num_unread_messages = Column(Integer(), nullable=False, default=0, doc="Controlled by postgres trigger") num_unread_notifications = Column(Integer(), nullable=False, default=0, doc="Controlled by postgres trigger") last_message_timestamp = Column(DateTime(), nullable=True, doc="Controlled by postgres trigger") last_notification_timestamp = Column(DateTime(), nullable=True, doc="Controlled by postgres trigger") # AllanC - TODO - derived field trigger needed account_type = Column( account_types, nullable=False, default='free', doc="Controlled by Python MapperExtension event on PaymentAccount") flags = relationship( "FlaggedEntity", backref=backref('offending_member'), cascade="all,delete-orphan", primaryjoin="Member.id==FlaggedEntity.offending_member_id") content_edits = relationship("ContentEditHistory", backref=backref('member', order_by=id)) groups_roles = relationship( "GroupMembership", backref="member", cascade="all,delete-orphan", lazy='joined' ) #AllanC- TODO: needs eagerload group? does lazy=joined do it? ratings = relationship("Rating", backref=backref('member'), cascade="all,delete-orphan") feeds = relationship("Feed", backref=backref('member'), cascade="all,delete-orphan") # AllanC - I wanted to remove these but they are still used by actions.py because they are needed to setup the base test data following = relationship( "Member", primaryjoin="Member.id==Follow.follower_id", secondaryjoin= "(Member.id==Follow.member_id ) & (Follow.type!='trusted_invite')", secondary=Follow.__table__) followers = relationship( "Member", primaryjoin="Member.id==Follow.member_id", secondaryjoin= "(Member.id==Follow.follower_id) & (Follow.type!='trusted_invite')", secondary=Follow.__table__) followers_trusted = relationship( "Member", primaryjoin="Member.id==Follow.member_id", secondaryjoin= "(Member.id==Follow.follower_id) & (Follow.type=='trusted' )", secondary=Follow.__table__) assigments = relationship("MemberAssignment", backref=backref("member"), cascade="all,delete-orphan") # Content relation shortcuts #content = relationship( "Content", backref=backref('creator'), primaryjoin=and_("Member.id==Content.creator_id") )# ,"Content.__type__!='comment'" # cant get this to work, we want to filter out comments #, cascade="all,delete-orphan" #content_assignments = relationship("AssignmentContent") #content_articles = relationship( "ArticleContent") #content_drafts = relationship( "DraftContent") #See civicboom_init.py # content # content_assignments_active # content_assignments_previous # assignments_accepted = relationship("MemberAssignment", backref=backref("member"), cascade="all,delete-orphan") #interests = relationship("") #messages_to = relationship("Message", primaryjoin=and_(Message.source_id!=null(), Message.target_id==id ), backref=backref('target', order_by=id)) #messages_from = relationship("Message", primaryjoin=and_(Message.source_id==id , Message.target_id!=null()), backref=backref('source', order_by=id)) #messages_public = relationship("Message", primaryjoin=and_(Message.source_id==id , Message.target_id==null()) ) #messages_notification = relationship("Message", primaryjoin=and_(Message.source_id==null(), Message.target_id==id ) ) #groups = relationship("Group" , secondary=GroupMembership.__table__) # Could be reinstated with only "active" groups, need to add criteria __table_args__ = ( CheckConstraint("id ~* '^[a-z0-9_-]{4,}$'"), CheckConstraint("length(name) > 0"), CheckConstraint( "substr(extra_fields,1,1)='{' AND substr(extra_fields,length(extra_fields),1)='}'" ), {}) __to_dict__ = copy.deepcopy(Base.__to_dict__) __to_dict__.update({ 'default': { 'id': None, 'name': lambda member: member.name if member.name else member. id, # Normalize the member name and return username if name not present 'username': None, # AllanC - this should be depricated as it is a mirror of the id. This may need careful combing of the templates before removal 'avatar_url': None, 'type': lambda member: member.__type__, 'location_home': lambda member: '', #location_to_string(member.location_home) , # AllanC - this is remmed out because we do not want to show locations until we can have a text description or area 'num_followers': None, 'num_following': None, 'account_type': None, 'url': lambda member: member.__link__(), }, }) __to_dict__.update({'full': copy.deepcopy(__to_dict__['default'])}) __to_dict__['full'].update({ 'num_followers': None, 'utc_offset': lambda member: (member.utc_offset.days * 86400 + member.utc_offset.days), 'join_date': None, 'website': lambda member: member.extra_fields.get('website'), 'google_profile': lambda member: member.extra_fields.get('google_profile'), 'description': None, 'push_assignment': lambda member: member.extra_fields.get('push_assignment'), #'followers' : lambda member: [m.to_dict() for m in member.followers ] , #'following' : lambda member: [m.to_dict() for m in member.following ] , #'messages_public' : lambda member: [m.to_dict() for m in member.messages_public[:5] ] , #'assignments_accepted': lambda member: [a.to_dict() for a in member.assignments_accepted if a.private==False] , #'content_public' : lambda member: [c.to_dict() for c in member.content_public ] , #'groups_public' : lambda member: [update_dict(gr.group.to_dict(),{'role':gr.role}) for gr in member.groups_roles if gr.status=="active" and gr.group.member_visibility=="public"] , #AllanC - also duplicated in members_actions.groups ... can this be unifyed }) @property def username(self): import warnings warnings.warn("Member.username used", DeprecationWarning) return self.id _config = None # extra_fields_raw = synonym('extra_fields', descriptor=property(_get_extra_fields_raw, _set_extra_fields_raw)) @property def config(self): if self.extra_fields == None: self.extra_fields = {} if not self._config: self._config = _ConfigManager(self.extra_fields) return self._config def __unicode__(self): return "%s (%s)" % (self.name, self.id) def __str__(self): return unicode(self).encode('ascii', 'replace') def __link__(self): from civicboom.lib.web import url return url('member', id=self.id, sub_domain='www', qualified=True) def hash(self): h = hashlib.md5() for field in ( "id", "name", "join_date", "status", "avatar", "utc_offset"): #TODO: includes relationship fields in list? h.update(unicode(getattr(self, field)).encode('utf-8')) return h.hexdigest() def invalidate_cache(self, remove=False): from civicboom.lib.cache import invalidate_member invalidate_member(self, remove=remove) def action_list_for(self, member, **kwargs): action_list = [] #if self.can_message(member): # action_list.append('editable') if self != member: if 'push_assignment' in self.extra_fields: action_list.append('push_assignment') if self == member: action_list.append('settings') action_list.append('logout') if member.has_account_required('plus'): action_list.append('invite_trusted_followers') elif member and member.has_account_required('plus'): if self.is_following(member): if member.is_follower_trusted(self): action_list.append('follower_distrust') else: action_list.append('follower_trust') elif not member.is_follow_trusted_invitee(self): action_list.append('follower_invite_trusted') # GregM: if member and (member.is_following(self) or member.is_follow_trusted_inviter(self)): action_list.append('unfollow') if member and (not member.is_following(self) or member.is_follow_trusted_inviter(self)): if self != member: action_list.append('follow') if self != member: action_list.append('message') if member and member.__type__ == 'group' and not member.get_membership( self ): # If the observing member is a group, show invite to my group action action_list.append('invite') return action_list def send_email(self, **kargs): from civicboom.lib.communication.email_lib import send_email send_email(self, **kargs) def send_notification(self, m, **kwargs): import civicboom.lib.communication.messages as messages messages.send_notification(self, m, **kwargs) def send_notification_to_followers(self, m, private=False): followers_to = self.followers if private: followers_to = self.followers_trusted import civicboom.lib.communication.messages as messages messages.send_notification(followers_to, m) def follow(self, member, delay_commit=False): from civicboom.lib.database.actions import follow return follow(self, member, delay_commit=delay_commit) def unfollow(self, member, delay_commit=False): from civicboom.lib.database.actions import unfollow return unfollow(self, member, delay_commit=delay_commit) def follower_trust(self, member, delay_commit=False): from civicboom.lib.database.actions import follower_trust return follower_trust(self, member, delay_commit=delay_commit) def follower_distrust(self, member, delay_commit=False): from civicboom.lib.database.actions import follower_distrust return follower_distrust(self, member, delay_commit=delay_commit) # GregM: Added kwargs to allow for invite controller adding role (needed for group invite, trying to genericise things as much as possible) def follower_invite_trusted(self, member, delay_commit=False, **kwargs): from civicboom.lib.database.actions import follower_invite_trusted return follower_invite_trusted(self, member, delay_commit=delay_commit) def is_follower(self, member): #if not member: # return False #from civicboom.controllers.members import MembersController #member_search = MembersController().index #return bool(member_search(member=self, followed_by=member)['data']['list']['count']) from civicboom.lib.database.actions import is_follower return is_follower(self, member) def is_follower_trusted(self, member): from civicboom.lib.database.actions import is_follower_trusted return is_follower_trusted(self, member) def is_follow_trusted_invitee(self, member): # Was: is_follower_invited_trust from civicboom.lib.database.actions import is_follow_trusted_invitee as _is_follow_trusted_invitee return _is_follow_trusted_invitee(self, member) def is_follow_trusted_inviter(self, member): # Was: is_following_invited_trust from civicboom.lib.database.actions import is_follow_trusted_invitee as _is_follow_trusted_invitee return _is_follow_trusted_invitee(member, self) def is_following(self, member): #if not member: # return False #from civicboom.controllers.members import MembersController #member_search = MembersController().index #return bool(member_search(member=self, follower_of=member)['data']['list']['count']) from civicboom.lib.database.actions import is_follower return is_follower(member, self) @property def url(self): from civicboom.lib.web import url return url('member', id=self.id, qualified=True) @property def avatar_url(self, size=80): # if specified, use specified avatar if self.avatar: return wh_url("avatars", self.avatar) # for members with email addresses, fall back to gravatar if hasattr(self, "email"): email = self.email or self.email_unverified if email: hash = hashlib.md5(email.lower()).hexdigest() #default = "identicon" default = wh_url( "public", "images/default/avatar_%s.png" % self.__type__) args = urllib.urlencode({ 'd': default, 's': str(size), 'r': "pg" }) return "https://secure.gravatar.com/avatar/%s?%s" % (hash, args) # last resort, fall back to our own default return wh_url("public", "images/default/avatar_%s.png" % self.__type__) def add_to_interests(self, content): from civicboom.lib.database.actions import add_to_interests return add_to_interests(self, content) def has_account_required(self, required_account_type): return has_account_required(required_account_type, self.account_type) def can_publish_assignment(self): # AllanC - could be replaced with some form of 'get_permission('publish') ??? we could have lots of permissiong related methods ... just a thought #from civicboom.lib.constants import can_publish_assignment #return can_publish_assignment(self) #AllanC - TODO - check member payment level to acertain what the limit is - set limit to this users level # if not member.payment_level: limit = None from pylons import config if has_account_required('corp', self.account_type): pass elif has_account_required('plus', self.account_type): limit = config['payment.plus.assignment_limit'] elif has_account_required( 'free', self.account_type): #self.account_type == 'free': limit = config['payment.free.assignment_limit'] if not limit: return True if len(self.active_assignments_period) >= limit: return False return True #@property #def payment_account(self): # return self._payment_account #@payment_account.setter def set_payment_account(self, value, delay_commit=False): #self._payment_account = value from civicboom.lib.database.actions import set_payment_account return set_payment_account(self, value, delay_commit) # @property # AllanC - TODO this needs to be a derrived field # GregM - Done, MapperExtension on PaymentAccount updates this field! # def account_type(self): # if self.payment_account and self.payment_account.type: # return self.payment_account.type # return 'free' def delete(self): """ Not to be called in normal operation - this a convenience method for automated tests """ from civicboom.lib.database.actions import del_member del_member(self) def check_action_key(self, action, key): """ Check that this member was the one who generated the key to the specified action. """ return (key == self.get_action_key(action)) def flag(self, **kargs): """ Flag member as breaking T&C (can throw exception if fails) """ from civicboom.lib.database.actions import flag flag(self, **kargs) def get_action_key(self, action): """ Generate a key, anyone with this key is allowed to perform $action on behalf of this member. The key is the hash of (member.id, action, member.salt). Member.id is included because while the salt *should* be unique, the ID *is* unique by definition. The salt should be kept secret by the server, not even shown to the user who owns it -- thus when presented with a key, we can guarantee that this server is the one who generated it. If the key for a user/action pair is only given to that user after they've authenticated, then we can guarantee that anyone with that key has been given it by the user. Usage: ~~~~~~ Alice: key = alice.get_action_key('read article 42') bob.send_message("Hey bob, if you want to read article 42, "+ "tell the system I gave you this key: "+key) Bob: api.content.show(42, auth=(alice, key)) System: wanted_content = get_content(42) claimed_user = get_member(alice) if key == claimed_user.get_action_key('read article '+wanted_content.id): print(wanted_content) """ return hashlib.sha1(str(self.id) + action + self.salt).hexdigest()
class Spot(Base): __tablename__ = 'spots' id = Column(types.Integer, primary_key=True) name = Column(types.String, nullable=False) the_geom = GeometryColumn(Point(dimension=2, srid=4326))
class Spot(Base): __tablename__ = 'spots' spot_id = Column(Integer, primary_key=True) spot_height = Column(Numeric) spot_location = GeometryColumn(Point(2))
class Spot(Base): __tablename__ = 'spots' spot_id = Column(Integer, autoincrement=True, primary_key=True) spot_height = Column(Numeric) spot_location = GeometryColumn(Point(2), nullable=False)
class UnitPoint(Base): __tablename__ = 'unit_point' unit_id = Column(BigInteger, ForeignKey('unit.id'), primary_key=True) geom = GeometryColumn(Point(2)) unit = relation(Unit, uselist=False)
class Content(Base): """ Abstract class "private" means the content is only visible by trusted followers """ __tablename__ = "content" __type__ = Column(_content_type, nullable=False, index=True) __mapper_args__ = { 'polymorphic_on': __type__, 'extension': CacheChangeListener() } #_visiability = Enum("pending", "show", name="content_") _edit_lock = Enum("parent_owner", "group", "system", name="edit_lock_level") id = Column(Integer(), primary_key=True) title = Column(Unicode(250), nullable=False, default=u"Untitled") content = Column(UnicodeText(), nullable=False, default=u"", doc="The body of text") creator_id = Column(String(32), ForeignKey('member.id', onupdate="cascade"), nullable=False, index=True) parent_id = Column(Integer(), ForeignKey('content.id'), nullable=True, index=True) location = GeometryColumn( Point(2), nullable=True ) # FIXME: area rather than point? AllanC - Point for now, need to consider referenceing polygon areas in future? (more research nedeed) creation_date = Column(DateTime(), nullable=False, default=now) update_date = Column(DateTime(), nullable=False, default=now, doc="Controlled by postgres trigger") private = Column(Boolean(), nullable=False, default=False, doc="see class doc") license_id = Column(Unicode(32), ForeignKey('license.id'), nullable=False, default=u"CC-BY") visible = Column(Boolean(), nullable=False, default=True) edit_lock = Column(_edit_lock, nullable=True, default=None) extra_fields = Column(JSONType(mutable=True), nullable=False, default={}) num_responses = Column(Integer(), nullable=False, default=0) # Derived field - see postgress trigger num_comments = Column(Integer(), nullable=False, default=0) # Derived field - see postgress trigger # FIXME: remote_side is confusing? # AllanC - it would be great to just have 'parent', we get a list of responses from API (contnte_lists.repnses) # however :( without the 'responses' relationship deleting content goes nuts about orphas # Shish - do we want to cascade to delete replies? # AllanC - we want to cascade deleting of comments, but not full responses. Does the 'comments' cascade below over this? responses = relationship("Content", primaryjoin=id == parent_id, backref=backref('parent', remote_side=id, order_by=creation_date)) #parent = relationship("Content", primaryjoin=parent_id==id, remote_side=id) creator = relationship("Member", primaryjoin="Content.creator_id==Member.id", backref=backref('content', cascade="all,delete-orphan")) attachments = relationship("Media", backref=backref('attached_to'), cascade="all,delete-orphan") edits = relationship("ContentEditHistory", backref=backref('content', order_by=id), cascade="all,delete-orphan") tags = relationship("Tag", secondary=ContentTagMapping.__table__) license = relationship("License") comments = relationship( "CommentContent", order_by=creation_date.asc(), cascade="all", primaryjoin= "(CommentContent.id==Content.parent_id) & (Content.visible==True)") flags = relationship("FlaggedEntity", backref=backref('offending_content'), cascade="all,delete-orphan") __table_args__ = ( CheckConstraint("length(title) > 0"), CheckConstraint( "substr(extra_fields,1,1)='{' AND substr(extra_fields,length(extra_fields),1)='}'" ), {}) # used by obj_to_dict to create a string dictonary representation of this object __to_dict__ = copy.deepcopy(Base.__to_dict__) __to_dict__.update({ 'default': { 'id': None, 'type': lambda content: content.__type__, #'status' : None , 'parent_id': None, 'title': None, 'content_short': None, # this is a property # lambda content: "implement content_short postgress trigger" , 'creator_id': None, 'thumbnail_url': None, 'creation_date': None, 'update_date': None, 'location': lambda content: location_to_string(content.location), 'location_text': lambda content: None, # AllanC - this could be a requested feature later, it would need to be handled by a db trigger? 'distance': lambda content: None, # if we search for a location, the extra 'distance' column will overwrite this 'num_responses': None, 'num_comments': None, 'license_id': None, 'private': None, 'edit_lock': None, 'url': lambda content: content.__link__(), 'tags': lambda content: [tag.name for tag in content.tags ], # TODO: turn this into an array column }, }) # Single Content Item __to_dict__.update({'full': copy.deepcopy(__to_dict__['default'])}) __to_dict__['full'].update({ 'content': None, 'parent': lambda content: content.parent.to_dict(include_fields='creator') if content.parent else {}, 'creator': lambda content: content.creator.to_dict() if content.creator else None, 'attachments': lambda content: [media.to_dict() for media in content.attachments], #'responses' : lambda content: [response.to_dict(include_fields='creator') for response in content.responses ] , #'comments' : lambda content: [ comment.to_dict( ) for comment in content.comments ] , 'license': lambda content: content.license.to_dict(), 'root_parent': lambda content: content.root_parent.to_dict(include_fields='creator') if content.root_parent else None, 'extra_fields': None, 'langauge': lambda content: None, # AllanC - it is unknown what format this should take, content should have a language, so users can search for french content etc, this should default to the users current langauge }) #del __to_dict__['full']['parent_id'] #del __to_dict__['full']['creator_id'] #del __to_dict__['full']['license_id'] #del __to_dict__['full']['content_short'] # AllanC - this functionality was being duplicated by viewing templates by the templates themselfs truncating and stripping the content item - it is now a guarenteed field for all content list and object returns def __init__(self): self.extra_fields = { } # AllanC - for some holy reason even though the default is setup in the field list above, if the object isnt added to the session this dict is None - force an epty dict - extending class's must ensure that they call Content.__init__() in there own init methods def __unicode__(self): return self.title # + u" (" + self.__type__ + u")" def __link__(self): from civicboom.lib.web import url return url('content', id=self.id, sub_domain='www', qualified=True) #def __db_index__(self): # return self.id def clone(self, content): if content and content.id: for field in [ "title", "content", "creator", "parent_id", "location", "creation_date", "private", "license_id" ]: setattr(self, field, getattr(content, field)) def hash(self): h = hashlib.md5() # Problem? TODO? # What about pythons own hash(obj) method? # AllanC - creator, parent, attachments and license are realtions and WILL trigger an additional query in most cases. # we cant rely on just looking at creator_id etc as this may not be set until a commit # solutions on a postcard? # is there a way in SQLAlchemy to force and object to resolve ID's without a commit? # need to add hash to sub objects? like Media etc for field in ("id", "title", "content", "creator", "parent", "update_date", "status", "private", "license", "attachments" ): # AllanC: unfinished field list? include relations? h.update(str(getattr(self, field))) return h.hexdigest() def invalidate_cache(self, remove=False): from civicboom.lib.cache import invalidate_content invalidate_content(self, remove=remove) def action_list_for(self, member, role): assert not member or isinstance(member, Member), debug_type( member ) # just a double check in case any of the codebase pass's a member dict here accidentaly action_list = [] if self.editable_by(member): action_list.append('edit') if self.viewable_by(member): action_list.append('view') if member and self.private == False and self.creator != member: action_list.append('flag') if self.private == False: action_list.append('aggregate') if self.creator == member and has_role_required('editor', role): action_list.append('delete') return action_list def editable_by(self, member, **kwargs): """ Check to see if a member object has the rights to edit this content NOTE: This does not take into account the logged_in_personas role """ if self.edit_lock: return False if self.creator == member and has_role_required( 'editor', kwargs.get('role', 'admin')): return True # TODO check groups of creator to see if member is in the owning group return False def viewable_by(self, member): """ Check to see if a member object has the rights to view this content """ # TODO check groups of creator to see if member is in the owning group if self.editable_by(member): return True # Always allow content to be viewed by owners/editors if self.__type__ == "draft": return False # if draft, only editors (above) can see if self.__type__ == "comment": return self.parent.viewable_by( member) # if comment, show if we can see the parent article if self.visible == True: # GregM: If current content has a root parent then check viewable_by on root parent root = self.root_parent if root: return root.viewable_by(member) # GregM: If content is private check member is a trusted follower of content's creator # We NEED to check has accepted, invited etc. for requests if self.private == True: if self.creator.is_follower_trusted(member): return True elif self.__type__ == "assignment": from civicboom.lib.database.get_cached import get_assigned_to member_assignment = get_assigned_to(self, member) if member_assignment: if not member_assignment.member_viewed: from civicboom.model.meta import Session member_assignment.member_viewed = True Session.commit() return True return False return True return False def is_parent_owner(self, member): # TODO # Currently just check editable_by, but needs aditional checks to see if member is part of organisation if self.parent: return self.parent.editable_by(member) return False def flag(self, **kargs): """ Flag content as offensive or spam (can throw exception if fails) """ from civicboom.lib.database.actions import flag flag(self, **kargs) def delete(self): """ Delete the content from the DB, this will cause all cascade behaviour to take place Be very sure you want to do this! """ from civicboom.lib.database.actions import del_content del_content(self) def parent_disassociate(self): from civicboom.lib.database.actions import parent_disassociate return parent_disassociate(self) def aggregate_via_creator(self): """ Aggregate a summary of this content to Twitter, Facebook, LinkedIn, etc via the content creators user account """ from civicboom.lib.aggregation import aggregate_via_user if self.__type__ == 'article' or self.__type__ == 'assignment': return aggregate_via_user(self, self.creator) @property def root_parent(self): """ Find this piece of content's root parent (or False if this is the root!) """ from civicboom.lib.database.get_cached import find_content_root return find_content_root(self) @property def thumbnail_url(self): """ TODO If there is media attached return the first image? if no media, check content for youtube video and get thumbnail from that? (maybe process this before content commit?) else return the default image url: (could vary depending on type?) This should be saved in the DB as thumbnail_url as a shortcut! """ for media in self.attachments: thumbnail_url = media.thumbnail_url if thumbnail_url and thumbnail_url != "": return thumbnail_url thumbnail_type = self.__type__ if thumbnail_type == 'article' and self.parent_id: thumbnail_type = 'response' from civicboom.lib.helpers import wh_url return wh_url("public", "images/default/thumbnail_%s.png" % thumbnail_type) @property def url(self): from pylons import url, app_globals return url('content', id=self.id, qualified=True) @property def content_short(self): """ AllanC TODO: Derived field - Postgress trigger needed """ from cbutils.text import strip_html_tags return truncate(strip_html_tags(self.content).strip(), length=300, indicator='...', whole_word=True)
def _create_layer(self, public=False, none_area=False, attr_list=False, exclude_properties=False): """ This function is central for this test class. It creates a layer with two features, and associates a restriction area to it. """ import transaction import sqlahelper from sqlalchemy import func from sqlalchemy import Column, Table, types, ForeignKey from sqlalchemy.ext.declarative import declarative_base from geoalchemy import (GeometryDDL, GeometryExtensionColumn, Point, WKTSpatialElement) from c2cgeoportal.models import DBSession, Layer, RestrictionArea self.__class__._table_index = self.__class__._table_index + 1 id = self.__class__._table_index engine = sqlahelper.get_engine() if not self.metadata: self.metadata = declarative_base(bind=engine).metadata tablename = "table_%d" % id table = Table('%s_child' % tablename, self.metadata, Column('id', types.Integer, primary_key=True), Column('name', types.Unicode), schema='public') table.create() ins = table.insert().values(name=u'c1é') c1_id = engine.connect().execute(ins).inserted_primary_key[0] ins = table.insert().values(name=u'c2é') c2_id = engine.connect().execute(ins).inserted_primary_key[0] table = Table(tablename, self.metadata, Column('id', types.Integer, primary_key=True), Column('child_id', types.Integer, ForeignKey('public.%s_child.id' % tablename)), Column('name', types.Unicode), GeometryExtensionColumn('geom', Point(srid=21781)), schema='public') GeometryDDL(table) table.create() ins = table.insert().values(child_id=c1_id, name='foo', geom=func.ST_GeomFromText( 'POINT(5 45)', 21781)) engine.connect().execute(ins).inserted_primary_key[0] ins = table.insert().values(child_id=c2_id, name='bar', geom=func.ST_GeomFromText( 'POINT(6 46)', 21781)) engine.connect().execute(ins).inserted_primary_key[0] if attr_list: ins = table.insert().values(child_id=c2_id, name='aaa,bbb,foo', geom=func.ST_GeomFromText( 'POINT(6 46)', 21781)) engine.connect().execute(ins).inserted_primary_key[0] layer = Layer() layer.id = id layer.geoTable = tablename layer.public = public if exclude_properties: layer.excludeProperties = 'name' DBSession.add(layer) if not public: ra = RestrictionArea() ra.name = u'__test_ra' ra.layers = [layer] ra.roles = [self.role] ra.readwrite = True if not none_area: poly = 'POLYGON((4 44, 4 46, 6 46, 6 44, 4 44))' ra.area = WKTSpatialElement(poly, srid=21781) DBSession.add(ra) self.layer_ids.append(self.__class__._table_index) transaction.commit() return id
road_id = Column(Integer, primary_key=True) road_name = Column(Unicode(40)) road_geom = GeometryColumn(LineString(2, srid=4326, spatial_index=False), comparator=MySQLComparator, nullable=False) class Lake(Base): __tablename__ = 'lakes' lake_id = Column(Integer, primary_key=True) lake_name = Column(Unicode(40)) lake_geom = GeometryColumn(Polygon(2, srid=4326), comparator=MySQLComparator) spots_table = Table('spots', metadata, Column('spot_id', Integer, primary_key=True), Column('spot_height', Numeric(precision=10, scale=2)), GeometryExtensionColumn('spot_location', Point(2, srid=4326))) class Spot(object): def __init__(self, spot_id=None, spot_height=None, spot_location=None): self.spot_id = spot_id self.spot_height = spot_height self.spot_location = spot_location mapper(Spot, spots_table, properties={ 'spot_location': GeometryColumn(spots_table.c.spot_location, comparator=MySQLComparator)}) # enable the DDL extension, which allows CREATE/DROP operations # to work correctly. This is not needed if working with externally # defined tables.
class Point(Base, GeometryTableMixIn): __tablename__ = 'points' id = Column(types.Integer, primary_key=True) the_geom = GeometryColumn(Point(srid=4326))
class Spot(Base, GeometryTableMixIn): __tablename__ = 'spots_mf' spot_id = Column(Integer, Sequence('spots_mf_id_seq'), primary_key=True) spot_height = Column(Numeric(precision=10, scale=2, asdecimal=False)) spot_location = GeometryColumn(Point(2, diminfo=diminfo))