class UserSIP(Base): __tablename__ = 'usersip' id = Column(Integer, nullable=False) tenant_uuid = Column(String(36), ForeignKey('tenant.uuid', ondelete='CASCADE'), nullable=False) name = Column(String(40), nullable=False) type = Column(Enum('friend', 'peer', 'user', name='useriax_type', metadata=Base.metadata), nullable=False) username = Column(String(80)) secret = Column(String(80), nullable=False, server_default='') md5secret = Column(String(32), nullable=False, server_default='') context = Column(String(39)) language = Column(String(20)) accountcode = Column(String(20)) amaflags = Column(Enum('default', 'omit', 'billing', 'documentation', name='useriax_amaflags', metadata=Base.metadata), nullable=False, server_default='default') allowtransfer = Column(Integer) fromuser = Column(String(80)) fromdomain = Column(String(255)) subscribemwi = Column(Integer, nullable=False, server_default='0') buggymwi = Column(Integer) call_limit = Column('call-limit', Integer, nullable=False, server_default='10') callerid = Column(String(160)) fullname = Column(String(80)) cid_number = Column(String(80)) maxcallbitrate = Column(Integer) insecure = Column( Enum('port', 'invite', 'port,invite', name='usersip_insecure', metadata=Base.metadata)) nat = Column( Enum('no', 'force_rport', 'comedia', 'force_rport,comedia', 'auto_force_rport', 'auto_comedia', 'auto_force_rport,auto_comedia', name='usersip_nat', metadata=Base.metadata)) promiscredir = Column(Integer) usereqphone = Column(Integer) videosupport = Column( Enum('no', 'yes', 'always', name='usersip_videosupport', metadata=Base.metadata)) trustrpid = Column(Integer) sendrpid = Column(String(16)) allowsubscribe = Column(Integer) allowoverlap = Column(Integer) dtmfmode = Column( Enum('rfc2833', 'inband', 'info', 'auto', name='usersip_dtmfmode', metadata=Base.metadata)) rfc2833compensate = Column(Integer) qualify = Column(String(4)) g726nonstandard = Column(Integer) disallow = Column(String(100)) allow = Column(Text) autoframing = Column(Integer) mohinterpret = Column(String(80)) useclientcode = Column(Integer) progressinband = Column( Enum('no', 'yes', 'never', name='usersip_progressinband', metadata=Base.metadata)) t38pt_udptl = Column(Integer) t38pt_usertpsource = Column(Integer) rtptimeout = Column(Integer) rtpholdtimeout = Column(Integer) rtpkeepalive = Column(Integer) deny = Column(String(31)) permit = Column(String(31)) defaultip = Column(String(255)) host = Column(String(255), nullable=False, server_default='dynamic') port = Column(Integer) regexten = Column(String(80)) subscribecontext = Column(String(80)) vmexten = Column(String(40)) callingpres = Column(Integer) parkinglot = Column(Integer) protocol = Column(enum.trunk_protocol, nullable=False, server_default='sip') category = Column(Enum('user', 'trunk', name='useriax_category', metadata=Base.metadata), nullable=False) outboundproxy = Column(String(1024)) transport = Column(String(255)) remotesecret = Column(String(255)) directmedia = Column(String(20)) callcounter = Column(Integer) busylevel = Column(Integer) ignoresdpversion = Column(Integer) session_timers = Column( 'session-timers', Enum('originate', 'accept', 'refuse', name='usersip_session_timers', metadata=Base.metadata)) session_expires = Column('session-expires', Integer) session_minse = Column('session-minse', Integer) session_refresher = Column( 'session-refresher', Enum('uac', 'uas', name='usersip_session_refresher', metadata=Base.metadata)) callbackextension = Column(String(255)) timert1 = Column(Integer) timerb = Column(Integer) qualifyfreq = Column(Integer) contactpermit = Column(String(1024)) contactdeny = Column(String(1024)) unsolicited_mailbox = Column(String(1024)) use_q850_reason = Column(Integer) encryption = Column(Integer) snom_aoc_enabled = Column(Integer) maxforwards = Column(Integer) disallowed_methods = Column(String(1024)) textsupport = Column(Integer) commented = Column(Integer, nullable=False, server_default='0') _options = Column("options", ARRAY(String, dimensions=2), nullable=False, default=list, server_default='{}') __table_args__ = ( PrimaryKeyConstraint('id'), UniqueConstraint('name'), Index('usersip__idx__category', 'category'), CheckConstraint( directmedia.in_( ['no', 'yes', 'nonat', 'update', 'update,nonat', 'outgoing'])), )
class Thing(db.Model): id = db.Column(db.Integer, nullable=False, primary_key=True) user_id = db.Column(db.Integer, nullable=False) category = db.Column(Enum(Category), nullable=False) title = db.Column(db.String(512), nullable=False) url = db.Column(db.String(2048), nullable=True, default='') extended = db.Column(JSON, nullable=False) shared = db.Column(db.Boolean, nullable=False, default=True) tags = db.Column(JSONB, nullable=False) time = db.Column(db.DateTime, nullable=False, default=datetime.utcnow) @property def user(self): return User.query.get(self.user_id) @property def note(self): _note = ThingNote.query.get(self.id) if not _note: return return _note @property def search_keywords(self): if self.category == Category.concept: return self.title.replace(' ', '+') return ('+'.join([self.category.display_name, self.title])).replace(' ', '+') @property def reference(self): if not self.url: return 'https://duckduckgo.com/?q=' + self.search_keywords return self.url @classmethod def get_recent_all_things(cls, time=None, limit=20): query = cls.query.filter_by(shared=True) if time: query = query.filter(cls.time < time) return query.order_by(cls.time.desc()).limit(limit).all() @classmethod def get_recent_user_things(cls, user_id, offset, limit): return (Thing.query.filter_by(user_id=user_id) .order_by(cls.time.desc()).offset(offset).limit(limit).all()) @classmethod def get_recent_user_categorized_things(cls, user_id, category, offset, limit, include_private=False): base_query = Thing.query.filter_by(user_id=user_id, category=category) if not include_private: base_query = base_query.filter_by(shared=True) return base_query.order_by(cls.time.desc()).offset(offset).limit(limit).all() @classmethod def get_recent_user_tagged_things(cls, user_id, tags, offset, limit, include_private=False): base_query = Thing.query.filter_by(user_id=user_id).filter(Thing.tags.contains(tags)) if not include_private: base_query = base_query.filter_by(shared=True) return base_query.order_by(cls.time.desc()).offset(offset).limit(limit).all() @classmethod def get_recent_tagged_things(cls, tags, offset, limit): return Thing.query.filter_by(shared=True).filter(Thing.tags.contains(tags)).order_by(cls.time.desc()).all() @classmethod def get_user_things_cnt(cls, user_id): return Thing.query.filter_by(user_id=user_id).count() def to_simplejson(self): return dict(id=self.id, user_id=self.user_id, category=self.category.name, title=self.title, extended=self.extended, shared=self.shared, url=self.url, tags=self.tags, time=self.time.isoformat(), note=(self.note and self.note.text or '')) def is_visible_by(self, user): is_anonymous = bool(hasattr(user, 'is_anonymous') and user.is_anonymous) if is_anonymous: return self.shared if self.user.is_private or not self.shared: return self.user_id == user.id or f'.@{user.username}' in self.tags return True @classmethod def get_public_tagset(cls, things): tags = [([tag for tag in t.tags if not tag.startswith('.')] or []) for t in things] if not tags: return set() return set(reduce(add, tags)) @classmethod def get_user_total_count(cls, user_id): return Thing.query.filter_by(user_id=user_id).count()
class OGCServer(Base): __tablename__ = 'ogc_server' __table_args__ = {'schema': _schema} __colanderalchemy_config__ = { 'title': _('OGC Server'), 'plural': _('OGC Servers') } __c2cgeoform_config__ = { 'duplicate': True } id = Column(Integer, primary_key=True, info={ 'colanderalchemy': { 'widget': HiddenWidget() } }) name = Column(Unicode, info={ 'colanderalchemy': {'title': _('Name')} }) description = Column(Unicode, info={ 'colanderalchemy': {'title': _('Description')} }) url = Column(Unicode, nullable=False, info={ 'colanderalchemy': {'title': _('Basic URL')} }) url_wfs = Column(Unicode, info={ 'colanderalchemy': {'title': _('WFS URL')} }) type = Column( Enum( OGCSERVER_TYPE_MAPSERVER, OGCSERVER_TYPE_QGISSERVER, OGCSERVER_TYPE_GEOSERVER, OGCSERVER_TYPE_OTHER, native_enum=False ), nullable=False, info={ 'colanderalchemy': { 'title': _('Server type'), 'widget': SelectWidget( values=( (OGCSERVER_TYPE_MAPSERVER, OGCSERVER_TYPE_MAPSERVER), (OGCSERVER_TYPE_QGISSERVER, OGCSERVER_TYPE_QGISSERVER), (OGCSERVER_TYPE_GEOSERVER, OGCSERVER_TYPE_GEOSERVER), (OGCSERVER_TYPE_OTHER, OGCSERVER_TYPE_OTHER) ) ) } }) image_type = Column( Enum( 'image/jpeg', 'image/png', native_enum=False ), nullable=False, info={ 'colanderalchemy': { 'title': _('Image type'), 'widget': SelectWidget( values=( ('image/jpeg', 'image/jpeg'), ('image/png', 'image/png') )), 'column': 2} }) auth = Column( Enum( OGCSERVER_AUTH_NOAUTH, OGCSERVER_AUTH_STANDARD, OGCSERVER_AUTH_GEOSERVER, OGCSERVER_AUTH_PROXY, native_enum=False), nullable=False, info={ 'colanderalchemy': { 'title': _('Authentication type'), 'widget': SelectWidget( values=( (OGCSERVER_AUTH_NOAUTH, OGCSERVER_AUTH_NOAUTH), (OGCSERVER_AUTH_STANDARD, OGCSERVER_AUTH_STANDARD), (OGCSERVER_AUTH_GEOSERVER, OGCSERVER_AUTH_GEOSERVER), (OGCSERVER_AUTH_PROXY, OGCSERVER_AUTH_PROXY) ) ), 'column': 2 } }) wfs_support = Column(Boolean, info={ 'colanderalchemy': {'title': _('WFS support'), 'column': 2} }) is_single_tile = Column(Boolean, info={ 'colanderalchemy': {'title': _('Is single tile'), 'column': 2} }) def __init__( self, name: str='', description: Optional[str]=None, url: str='https://wms.example.com', url_wfs: str=None, type_: str='mapserver', image_type: str='image/png', auth: str='Standard auth', wfs_support: bool=True, is_single_tile: bool=False ) -> None: self.name = name self.description = description self.url = url self.url_wfs = url_wfs self.type = type_ self.image_type = image_type self.auth = auth self.wfs_support = wfs_support self.is_single_tile = is_single_tile def __unicode__(self) -> str: return self.name or '' # pragma: no cover
class ConditionType(ModelBase): """ Holds type and constants name of data attached to particular run. """ # Constants to represent the type of the field BOOL_FIELD = "bool" JSON_FIELD = "json" STRING_FIELD = "string" FLOAT_FIELD = "float" INT_FIELD = "int" TIME_FIELD = "time" BLOB_FIELD = "blob" __tablename__ = 'condition_types' """Name of the database table""" id = Column(Integer, primary_key=True) """DB id""" name = Column(String(255), nullable=False) """Key. Or constant name""" value_type = Column(Enum(JSON_FIELD, STRING_FIELD, FLOAT_FIELD, INT_FIELD, BOOL_FIELD, TIME_FIELD, BLOB_FIELD, native_enum=False), nullable=False, default=STRING_FIELD) """Type of constant. Might be one of: JSON_FIELD, STRING_FIELD, FLOAT_FIELD, INT_FIELD, BOOL_FIELD, TIME_FIELD, BLOB_FIELD""" created = Column(DateTime, default=datetime.datetime.now) """Time of creation (set automatically)""" values = relationship("Condition", back_populates="type", lazy="dynamic", cascade="all") """Values of this type attached to runs. Since there are many runs, the load strategy is lazy="dynamic" this means that values are queryable. Example: ------- db = rcdb.ConfigurationProvider("mysql://[email protected]/rcdb") condition_type = db.get_condition_type("total_event_count") #get condition values for condition_type.values.filter(Condition.run.number > 5000) """ description = Column(String(255), nullable=True) @property def run_query(self): return object_session(self).query(Run) \ .join(Condition, Condition.run_number == Run.number) \ .filter(Condition.type == self) def get_condition_alias_value_field(self, alias): """ Gets appropriate aliased(Condition).xxx_value field according to type """ field = None if self.value_type == ConditionType.INT_FIELD: field = alias.int_value if self.value_type == ConditionType.STRING_FIELD \ or self.value_type == ConditionType.JSON_FIELD \ or self.value_type == ConditionType.BLOB_FIELD: field = alias.text_value if self.value_type == ConditionType.FLOAT_FIELD: field = alias.float_value if self.value_type == ConditionType.BOOL_FIELD: field = alias.bool_value if self.value_type == ConditionType.TIME_FIELD: field = alias.time_value return field def get_value_field_name(self): """ Gets appropriate aliased(Condition).xxx_value field according to type """ name = None if self.value_type == ConditionType.INT_FIELD: name = 'int_value' if self.value_type == ConditionType.STRING_FIELD \ or self.value_type == ConditionType.JSON_FIELD \ or self.value_type == ConditionType.BLOB_FIELD: name = 'text_value' if self.value_type == ConditionType.FLOAT_FIELD: name = 'float_value' if self.value_type == ConditionType.BOOL_FIELD: name = 'bool_value' if self.value_type == ConditionType.TIME_FIELD: name = 'time_value' return name @hybrid_property def value_field(self): """ Gets appropriate Condition.xxx_value field according to type """ field = None if self.value_type == ConditionType.INT_FIELD: field = Condition.int_value if self.value_type == ConditionType.STRING_FIELD \ or self.value_type == ConditionType.JSON_FIELD \ or self.value_type == ConditionType.BLOB_FIELD: field = Condition.text_value if self.value_type == ConditionType.FLOAT_FIELD: field = Condition.float_value if self.value_type == ConditionType.BOOL_FIELD: field = Condition.bool_value if self.value_type == ConditionType.TIME_FIELD: field = Condition.time_value return field def convert_value(self, value): # validate value if self.value_type == ConditionType.FLOAT_FIELD: try: value = float(value) except ValueError as err: message = "Condition type '{}' awaits float as value. {}".format( self, err) raise ValueError(message) elif self.value_type == ConditionType.INT_FIELD: try: value = int(value) except ValueError as err: message = "Condition type '{}' awaits int as value. {}".format( self, err) raise ValueError(message) elif self.value_type == ConditionType.TIME_FIELD and not isinstance( value, datetime.datetime): message = "Condition type '{}' awaits datetime as value. '{}' is given".format( self, type(value)) raise ValueError(message) elif self.value_type == ConditionType.BOOL_FIELD: try: value = bool(value) except ValueError as err: message = "Condition type '{}' awaits bool as value. {}".format( self, err) raise ValueError(message) return value def values_are_equal(self, left_value, right_value): """Function compares 2 values and return true if values are differ. The function takes in account floating point comparison if value_type is FLOAT_FIELD Also it validates both values :param left_value: Left value. Could be Condition object :param right_value: Right value. Could be Condition object""" if isinstance(left_value, Condition): left_value = left_value.value if isinstance(right_value, Condition): right_value = right_value.value left_value = self.convert_value(left_value) right_value = self.convert_value(right_value) # if value is float, use precision if self.value_type == ConditionType.FLOAT_FIELD: return is_close(left_value, right_value) else: return left_value == right_value def __repr__(self): return "<ConditionType id='{}', name='{}', value_type={}>" \ .format(self.id, self.name, self.value_type)
Column('code', String(64), nullable=False), ) # OAuth2 Tables oauth2_client = Table( 'oauth2_client', db_metadata, Column('id', Integer, primary_key=True, nullable=False), Column('client_id', String(63), nullable=False), Column('name', String(63), nullable=True), Column('secret', String(255), nullable=True), Column('user_id', String(63), ForeignKey(user.c.user_id), nullable=True), Column( 'grant_type', Enum("authorization_code", "password", "client_credentials", "refresh_token"), nullable=False, ), Column('response_type', Enum("code", "token"), nullable=False), Column('scopes', Text, nullable=False), # comma-separated list of allowed scopes Column('redirect_uris', Text, nullable=False), # comma-separated list of allowed redirect URIs UniqueConstraint('client_id', name='uix_1'), ) oauth2_token = Table( 'oauth2_token', db_metadata, Column('id', Integer, primary_key=True, nullable=False), Column('client_id',
from sqlalchemy.orm.session import Session # This is craptacularly retarded. Sqlite is retarded # see https://bitbucket.org/zzzeek/sqlalchemy/issues/2074/map-biginteger-type-to-integer-to-allow @compiles(BigInteger, 'sqlite') def bi_c(element, compiler, **kw): return "INTEGER" dlstate_enum = Enum('new', 'fetching', 'processing', 'complete', 'error', 'removed', 'disabled', 'specialty_deferred', 'specialty_ready', 'not_set', name='dlstate_enum') from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class ArtItem(Base): __versioned__ = {} __tablename__ = 'art_item'
class Box(DatabaseObject): ''' Box definition ''' uuid = Column(String(36), unique=True, nullable=False, default=lambda: str(uuid4())) corporation_id = Column(Integer, ForeignKey('corporation.id'), nullable=False) category_id = Column(Integer, ForeignKey('category.id'), nullable=True) _name = Column(Unicode(32), unique=True, nullable=False) _operating_system = Column(Unicode(16)) _description = Column(Unicode(1024)) _difficulty = Column(Unicode(16)) game_level_id = Column(Integer, ForeignKey('game_level.id'), nullable=False) _avatar = Column(String(64)) garbage = Column(String(32), unique=True, nullable=False, default=lambda: urandom(16).encode('hex')) teams = relationship("Team", secondary=team_to_box, backref=backref("box", lazy="select")) hints = relationship("Hint", backref=backref("box", lazy="select"), cascade="all,delete,delete-orphan") flags = relationship("Flag", backref=backref("box", lazy="select"), cascade="all,delete,delete-orphan", order_by="Flag._order") flag_submission_type = Column(Enum(FlagsSubmissionType), default=FlagsSubmissionType.CLASSIC) ip_addresses = relationship("IpAddress", backref=backref("box", lazy="select"), cascade="all,delete,delete-orphan") @classmethod def all(cls): ''' Returns a list of all objects in the database ''' return dbsession.query(cls).all() @classmethod def by_id(cls, _id): ''' Returns a the object with id of _id ''' return dbsession.query(cls).filter_by(id=_id).first() @classmethod def by_uuid(cls, _uuid): ''' Return and object based on a uuid ''' return dbsession.query(cls).filter_by(uuid=unicode(_uuid)).first() @classmethod def by_name(cls, name): ''' Return the box object whose name is "name" ''' return dbsession.query(cls).filter_by(_name=unicode(name)).first() @classmethod def by_category(cls, _cat_id): ''' Return the box object whose category is "_cat_id" ''' return dbsession.query(cls).filter_by(category_id=int(_cat_id)).all() @classmethod def by_garbage(cls, _garbage): return dbsession.query(cls).filter_by(garbage=_garbage).first() @classmethod def by_ip_address(cls, ip_addr): ''' Returns a box object based on an ip address, supports both ipv4 and ipv6 ''' ip = IpAddress.by_address(ip_addr) return ip.box if ip is not None else None @classmethod def flaglist(self, box_id=None): flags = self.by_id(box_id).flags flaglist = {} for flag in flags: flaglist[flag.uuid] = flag.name return flaglist @property def name(self): return self._name @name.setter def name(self, value): if not 3 <= len(unicode(value)) <= 32: raise ValidationError("Name must be 3 - 32 characters") self._name = unicode(value) @property def operating_system(self): return self._operating_system if self._operating_system else '?' @operating_system.setter def operating_system(self, value): self._operating_system = unicode(value) @property def description(self): if self._description is None: self._description = "" ls = [] if 0 < len(self._description): text = self._description.replace("\r\n", "\n").strip() ls.append("%s" % text) else: ls.append("No information on file.") if self.difficulty != "Unknown": ls.append("Reported Difficulty: %s" % self.difficulty) if ls[-1] and not ls[-1].encode('utf8').endswith("\n"): ls[-1] = ls[-1] + "\n" return unicode("\n\n".join(ls)) @description.setter def description(self, value): if value is None: return "" if 1025 < len(value): raise ValidationError( "Description cannot be greater than 1024 characters") self._description = unicode(value) @property def difficulty(self): return self._difficulty if self._difficulty and len( self._difficulty) else u"Unknown" @difficulty.setter def difficulty(self, value): if value is None: return if 17 < len(value): raise ValidationError( "Difficulty cannot be greater than 16 characters") self._difficulty = unicode(value) @property def avatar(self): if self._avatar is not None: return self._avatar else: avatar = get_new_avatar('box') if not avatar.startswith("default_"): self._avatar = avatar dbsession.add(self) dbsession.commit() return avatar @avatar.setter def avatar(self, image_data): if self.uuid is None: self.uuid = str(uuid4()) if len(image_data) < (1024 * 1024): ext = imghdr.what("", h=image_data) if ext in ['png', 'jpeg', 'gif', 'bmp' ] and not is_xss_image(image_data): if self._avatar is not None and os.path.exists( options.avatar_dir + '/upload/' + self._avatar): os.unlink(options.avatar_dir + '/upload/' + self._avatar) file_path = str(options.avatar_dir + '/upload/' + self.uuid + '.' + ext) image = Image.open(StringIO.StringIO(image_data)) cover = resizeimage.resize_cover(image, [500, 250]) cover.save(file_path, image.format) self._avatar = 'upload/' + self.uuid + '.' + ext else: raise ValidationError( "Invalid image format, avatar must be: .png .jpeg .gif or .bmp" ) else: raise ValidationError("The image is too large") @property def ipv4s(self): ''' Return a list of all ipv4 addresses ''' return filter(lambda ip: ip.version == 4, self.ip_addresses) @property def ipv6s(self): ''' Return a list of all ipv6 addresses ''' return filter(lambda ip: ip.version == 6, self.ip_addresses) @property def visable_ips(self): return filter(lambda ip: ip.visable is True, self.ip_addresses) @property def source_code(self): return SourceCode.by_box_id(self.id) def get_garbage_cfg(self): return "[Bot]\nname = %s\ngarbage = %s\n" % (self.name.encode('hex'), self.garbage) def to_xml(self, parent): ''' Convert object to XML ''' box_elem = ET.SubElement(parent, "box") box_elem.set("gamelevel", str(self.game_level.number)) ET.SubElement(box_elem, "name").text = self.name ET.SubElement(box_elem, "operatingsystem").text = self._operating_system ET.SubElement(box_elem, "description").text = self._description ET.SubElement(box_elem, "flag_submission_type").text = FlagsSubmissionType( self.flag_submission_type).name ET.SubElement(box_elem, "difficulty").text = self._difficulty ET.SubElement(box_elem, "garbage").text = self.garbage if self.category_id: ET.SubElement(box_elem, "category").text = Category.by_id( self.category_id).category flags_elem = ET.SubElement(box_elem, "flags") flags_elem.set("count", str(len(self.flags))) for flag in self.flags: flag.to_xml(flags_elem) hints_elem = ET.SubElement(box_elem, "hints") count = 0 for hint in self.hints: if hint.flag_id is None: hint.to_xml(hints_elem) count += 1 hints_elem.set("count", str(count)) ips_elem = ET.SubElement(box_elem, "ipaddresses") ips_elem.set("count", str(len(self.ip_addresses))) for ip in self.ip_addresses: ip.to_xml(ips_elem) avatarfile = os.path.join(options.avatar_dir, self.avatar) if self.avatar and os.path.isfile(avatarfile): with open(avatarfile, mode='rb') as _avatar: data = _avatar.read() ET.SubElement(box_elem, "avatar").text = data.encode('base64') else: ET.SubElement(box_elem, "avatar").text = "none" def to_dict(self): ''' Returns editable data as a dictionary ''' corp = Corporation.by_id(self.corporation_id) game_level = GameLevel.by_id(self.game_level_id) cat = Category.by_id(self.category_id) if cat: category = cat.uuid else: category = "" return { 'name': self.name, 'uuid': self.uuid, 'corporation': corp.uuid, 'category': category, 'operating_system': self.operating_system, 'description': self._description, 'difficulty': self.difficulty, 'game_level': game_level.uuid, 'flag_submission_type': self.flag_submission_type, 'flaglist': self.flaglist(self.id) } def __repr__(self): return u'<Box - name: %s>' % (self.name, ) def __str__(self): return self.name.encode('ascii', 'ignore')
# You should have received a copy of the GNU General Public License # along with faf. If not, see <http://www.gnu.org/licenses/>. from typing import Any, Dict, List, Union from sqlalchemy.orm import relationship from sqlalchemy.sql.schema import Column, ForeignKey, UniqueConstraint, Index from sqlalchemy.types import Boolean, DateTime, Enum, Integer, String from .custom_types import Semver from .generic_table import GenericTable from .project import ProjRelease OpSysReleaseStatus = Enum("INVALID_STATUS_CODE", "ACTIVE", "ADDED", "APPROVED", "AWAITING_BRANCH", "AWAITING_DEVELOPMENT", "AWAITING_QA", "AWAITING_PUBLISH", "AWAITING_REVIEW", "EOL", "DENIED", "MAINTENANCE", "MODIFIED", "OBSOLETE", "ORPHANED", "OWNED", "REJECTED", "REMOVED", "UNDER_DEVELOPMENT", "UNDER_REVIEW", "DEPRECATED", name="opsysrelease_status") class Arch(GenericTable): __tablename__ = "archs" __table_args__ = (UniqueConstraint('name'),) id = Column(Integer, primary_key=True) name = Column(String(8), nullable=False) def __str__(self) -> str: return str(self.name)
class Tracker(Base): """ Tracker model """ __tablename__ = 'tracker' URL_CONSTRUCTORS = { 'trac': trac_ticket_url, 'cookie_trac': trac_ticket_url, 'bugzilla': bugzilla_ticket_url, 'igozilla': bugzilla_ticket_url, 'rockzilla': bugzilla_ticket_url, 'bitbucket': bitbucket_ticket_url, 'pivotaltracker': pivotaltracker_ticket_url, 'unfuddle': unfuddle_ticket_url, 'github': github_ticket_url, 'jira': jira_ticket_url, } NEW_BUG_URL_CONSTRUCTORS = { 'trac': trac_new_ticket_url, 'cookie_trac': trac_new_ticket_url, 'bugzilla': bugzilla_new_ticket_url, 'igozilla': bugzilla_new_ticket_url, 'rockzilla': bugzilla_new_ticket_url, 'bitbucket': bitbucket_new_ticket_url, 'pivotaltracker': pivotaltracker_new_ticket_url, 'unfuddle': unfuddle_new_ticket_url, 'github': github_new_ticket_url, 'jira': jira_new_ticket_url, } id = Column(Integer, primary_key=True, nullable=False, index=True) type = Column(Enum("bugzilla", "trac", "cookie_trac", "igozilla", "bitbucket", "rockzilla", "pivotaltracker", "harvest", 'unfuddle', 'github', name='tracker_type_enum'), nullable=False) name = Column(String, nullable=False, unique=True) url = Column(String, nullable=False, unique=True) mailer = Column(String, nullable=True, unique=True) credentials = orm.relationship('TrackerCredentials', backref='tracker', lazy='dynamic') projects = orm.relationship('Project', backref='tracker', lazy='dynamic') def get_bug_url(self, id): """ Calculate URL for bug 'id' on this tracker """ constructor = self.URL_CONSTRUCTORS[self.type] return constructor(self.url, id) def get_new_bug_url(self, project_selector, component_selector): """ Returns url for create new bug in project """ constructor = self.NEW_BUG_URL_CONSTRUCTORS[self.type] return constructor(self.url, project_selector, component_selector) @reify def logins_mapping(self): return TrackerCredentials.get_logins_mapping(self)
class MeetmeFeatures(Base): __tablename__ = 'meetmefeatures' __table_args__ = ( PrimaryKeyConstraint('id'), UniqueConstraint('meetmeid'), UniqueConstraint('name'), Index('meetmefeatures__idx__context', 'context'), Index('meetmefeatures__idx__number', 'confno'), ) id = Column(Integer, nullable=False) meetmeid = Column(Integer, nullable=False) name = Column(String(80), nullable=False) confno = Column(String(40), nullable=False) context = Column(String(39), nullable=False) admin_typefrom = Column( Enum('none', 'internal', 'external', 'undefined', name='meetmefeatures_admin_typefrom', metadata=Base.metadata)) admin_internalid = Column(Integer) admin_externalid = Column(String(40)) admin_identification = Column(Enum( 'calleridnum', 'pin', 'all', name='meetmefeatures_admin_identification', metadata=Base.metadata), nullable=False) admin_mode = Column(Enum('listen', 'talk', 'all', name='meetmefeatures_mode', metadata=Base.metadata), nullable=False) admin_announceusercount = Column(Integer, nullable=False, server_default='0') admin_announcejoinleave = Column(Enum( 'no', 'yes', 'noreview', name='meetmefeatures_announcejoinleave', metadata=Base.metadata), nullable=False) admin_moderationmode = Column(Integer, nullable=False, server_default='0') admin_initiallymuted = Column(Integer, nullable=False, server_default='0') admin_musiconhold = Column(String(128)) admin_poundexit = Column(Integer, nullable=False, server_default='0') admin_quiet = Column(Integer, nullable=False, server_default='0') admin_starmenu = Column(Integer, nullable=False, server_default='0') admin_closeconflastmarkedexit = Column(Integer, nullable=False, server_default='0') admin_enableexitcontext = Column(Integer, nullable=False, server_default='0') admin_exitcontext = Column(String(39)) user_mode = Column(Enum('listen', 'talk', 'all', name='meetmefeatures_mode', metadata=Base.metadata), nullable=False) user_announceusercount = Column(Integer, nullable=False, server_default='0') user_hiddencalls = Column(Integer, nullable=False, server_default='0') user_announcejoinleave = Column(Enum( 'no', 'yes', 'noreview', name='meetmefeatures_announcejoinleave', metadata=Base.metadata), nullable=False) user_initiallymuted = Column(Integer, nullable=False, server_default='0') user_musiconhold = Column(String(128)) user_poundexit = Column(Integer, nullable=False, server_default='0') user_quiet = Column(Integer, nullable=False, server_default='0') user_starmenu = Column(Integer, nullable=False, server_default='0') user_enableexitcontext = Column(Integer, nullable=False, server_default='0') user_exitcontext = Column(String(39)) talkeroptimization = Column(Integer, nullable=False, server_default='0') record = Column(Integer, nullable=False, server_default='0') talkerdetection = Column(Integer, nullable=False, server_default='0') noplaymsgfirstenter = Column(Integer, nullable=False, server_default='0') durationm = Column(Integer) closeconfdurationexceeded = Column(Integer, nullable=False, server_default='0') nbuserstartdeductduration = Column(Integer) timeannounceclose = Column(Integer) maxusers = Column(Integer, nullable=False, server_default='0') startdate = Column(Integer) emailfrom = Column(String(255)) emailfromname = Column(String(255)) emailsubject = Column(String(255)) emailbody = Column(Text, nullable=False) preprocess_subroutine = Column(String(39)) description = Column(Text, nullable=False) commented = Column(Integer, server_default='0')
def __init__(self, enum): self.enum = enum self.impl = Enum( *enum.values(), name="ck%s" % re.sub( '([A-Z])', lambda m: "_" + m.group(1).lower(), enum.__name__))
''' Created on Mar 5, 2012 @package: internationalization @copyright: 2011 Sourcefabric o.p.s. @license: http://www.gnu.org/licenses/gpl-3.0.txt @author: Gabriel Nistor Contains the SQL alchemy meta for source API. ''' from .metadata_internationalization import meta from ..api.source import Source, TYPE_PYTHON, TYPE_JAVA_SCRIPT from .file import File from ally.support.sqlalchemy.mapper import mapperModel from sqlalchemy.schema import Table, Column, ForeignKey from sqlalchemy.types import Enum # -------------------------------------------------------------------- table = Table('inter_source', meta, Column('fk_file_id', ForeignKey(File.Id), primary_key=True, key='Id'), Column('type', Enum(TYPE_PYTHON, TYPE_JAVA_SCRIPT), nullable=False, key='Type'), mysql_engine='InnoDB' ) Source = mapperModel(Source, table, inherits=File)
class Task(Base): """Class to store a task. """ __tablename__ = 'tasks' __table_args__ = ( UniqueConstraint('contest_id', 'num'), UniqueConstraint('contest_id', 'name'), ForeignKeyConstraint( ("id", "active_dataset_id"), ("datasets.task_id", "datasets.id"), onupdate="SET NULL", ondelete="SET NULL", # Use an ALTER query to set this foreign key after # both tables have been CREATEd, to avoid circular # dependencies. use_alter=True, name="fk_active_dataset_id"), CheckConstraint("token_gen_initial <= token_gen_max"), ) # Auto increment primary key. id = Column( Integer, primary_key=True, # Needed to enable autoincrement on integer primary keys that # are referenced by a foreign key defined on this table. autoincrement='ignore_fk') # Number of the task for sorting. num = Column(Integer, nullable=True) # Contest (id and object) owning the task. contest_id = Column(Integer, ForeignKey(Contest.id, onupdate="CASCADE", ondelete="CASCADE"), nullable=True, index=True) contest = relationship(Contest, back_populates="tasks") # Short name and long human readable title of the task. name = Column(Codename, nullable=False, unique=True) title = Column(Unicode, nullable=False) # The names of the files that the contestant needs to submit (with # language-specific extensions replaced by "%l"). submission_format = Column(FilenameSchemaArray, nullable=False, default=[]) # The language codes of the statements that will be highlighted to # all users for this task. primary_statements = Column(ARRAY(String), nullable=False, default=[]) # The parameters that control task-tokens follow. Note that their # effect during the contest depends on the interaction with the # parameters that control contest-tokens, defined on the Contest. # The "kind" of token rules that will be active during the contest. # - disabled: The user will never be able to use any token. # - finite: The user has a finite amount of tokens and can choose # when to use them, subject to some limitations. Tokens may not # be all available at start, but given periodically during the # contest instead. # - infinite: The user will always be able to use a token. token_mode = Column(Enum(TOKEN_MODE_DISABLED, TOKEN_MODE_FINITE, TOKEN_MODE_INFINITE, name="token_mode"), nullable=False, default=TOKEN_MODE_DISABLED) # The maximum number of tokens a contestant is allowed to use # during the whole contest (on this tasks). token_max_number = Column(Integer, CheckConstraint("token_max_number > 0"), nullable=True) # The minimum interval between two successive uses of tokens for # the same user (on this task). token_min_interval = Column( Interval, CheckConstraint("token_min_interval >= '0 seconds'"), nullable=False, default=timedelta()) # The parameters that control generation (if mode is "finite"): # the user starts with "initial" tokens and receives "number" more # every "interval", but their total number is capped to "max". token_gen_initial = Column(Integer, CheckConstraint("token_gen_initial >= 0"), nullable=False, default=2) token_gen_number = Column(Integer, CheckConstraint("token_gen_number >= 0"), nullable=False, default=2) token_gen_interval = Column( Interval, CheckConstraint("token_gen_interval > '0 seconds'"), nullable=False, default=timedelta(minutes=30)) token_gen_max = Column(Integer, CheckConstraint("token_gen_max > 0"), nullable=True) # Maximum number of submissions or user_tests allowed for each user # on this task during the whole contest or None to not enforce # this limitation. max_submission_number = Column( Integer, CheckConstraint("max_submission_number > 0"), nullable=True) max_user_test_number = Column(Integer, CheckConstraint("max_user_test_number > 0"), nullable=True) # Minimum interval between two submissions or user_tests for this # task, or None to not enforce this limitation. min_submission_interval = Column( Interval, CheckConstraint("min_submission_interval > '0 seconds'"), nullable=True) min_user_test_interval = Column( Interval, CheckConstraint("min_user_test_interval > '0 seconds'"), nullable=True) # What information users can see about the evaluations of their # submissions. Offering full information might help some users to # reverse engineer task data. feedback_level = Column(Enum(FEEDBACK_LEVEL_FULL, FEEDBACK_LEVEL_RESTRICTED, name="feedback_level"), nullable=False, default=FEEDBACK_LEVEL_RESTRICTED) # The scores for this task will be rounded to this number of # decimal places. score_precision = Column(Integer, CheckConstraint("score_precision >= 0"), nullable=False, default=0) # Score mode for the task. score_mode = Column(Enum(SCORE_MODE_MAX_TOKENED_LAST, SCORE_MODE_MAX, SCORE_MODE_MAX_SUBTASK, name="score_mode"), nullable=False, default=SCORE_MODE_MAX_TOKENED_LAST) # Active Dataset (id and object) currently being used for scoring. # The ForeignKeyConstraint for this column is set at table-level. active_dataset_id = Column(Integer, nullable=True) active_dataset = relationship( 'Dataset', foreign_keys=[active_dataset_id], # Use an UPDATE query *after* an INSERT query (and *before* a # DELETE query) to set (and unset) the column associated to # this relationship. post_update=True) # These one-to-many relationships are the reversed directions of # the ones defined in the "child" classes using foreign keys. statements = relationship( "Statement", collection_class=attribute_mapped_collection("language"), cascade="all, delete-orphan", passive_deletes=True, back_populates="task") attachments = relationship( "Attachment", collection_class=attribute_mapped_collection("filename"), cascade="all, delete-orphan", passive_deletes=True, back_populates="task") datasets = relationship( "Dataset", # Due to active_dataset_id, SQLAlchemy cannot unambiguously # figure out by itself which foreign key to use. foreign_keys="[Dataset.task_id]", cascade="all, delete-orphan", passive_deletes=True, back_populates="task") submissions = relationship("Submission", cascade="all, delete-orphan", passive_deletes=True, back_populates="task") user_tests = relationship("UserTest", cascade="all, delete-orphan", passive_deletes=True, back_populates="task")
class DataFrameColumn(BaseModel): __tablename__ = 'df_column' _name = Column('name', String(100)) _type = Column('type', Enum(ColumnType), default=Enum) _is_nullable = Column('is_nullable', Boolean, default=False) _array_dimensions = Column('array_dimensions', String(100)) _metadata_id = Column('metadata_id', Integer, ForeignKey('df_metadata.id')) _dataset = relationship("DataFrameMetadata", back_populates="_columns") __table_args__ = ( UniqueConstraint('name', 'metadata_id'), {} ) def __init__(self, name: str, type: ColumnType, is_nullable: bool = False, array_dimensions: List[int] = [], metadata_id: int = None): self._name = name self._type = type self._is_nullable = is_nullable self._array_dimensions = str(array_dimensions) self._metadata_id = metadata_id @property def id(self): return self._id @property def name(self): return self._name @property def type(self): return self._type @property def is_nullable(self): return self._is_nullable @property def array_dimensions(self): return literal_eval(self._array_dimensions) @array_dimensions.setter def array_dimensions(self, value): self._array_dimensions = str(value) @property def metadata_id(self): return self._metadata_id @metadata_id.setter def metadata_id(self, value): self._metadata_id = value def __str__(self): column_str = "Column: (%s, %s, %s, " % (self._name, self._type.name, self._is_nullable) column_str += "[" column_str += ', '.join(['%d'] * len(self.array_dimensions)) \ % tuple(self.array_dimensions) column_str += "])" return column_str def __eq__(self, other): return self.id == other.id and \ self.metadata_id == other.metadata_id and \ self.is_nullable == other.is_nullable and \ self.array_dimensions == other.array_dimensions and \ self.name == other.name and \ self.type == other.type
from sqlalchemy.orm import sessionmaker from sqlalchemy.ext.declarative import declarative_base import sqlalchemy.exc SQLALCHEMY_DATABASE_URI = 'sqlite:///db_sqlite.db' Base = declarative_base() engine = create_engine(SQLALCHEMY_DATABASE_URI) Session = scoped_session(sessionmaker()) Session.configure(bind=engine) dlstate_enum = Enum('new', 'fetching', 'complete', 'error', name='dlstate_enum') @contextlib.contextmanager def session_context(): session = Session() try: yield session finally: Session.remove() class WebPages(Base):
class Party(Group): members = relationship('Member', backref='party') __mapper_args__ = { 'polymorphic_identity':'party', } def __repr__(self): return 'Party {}: {} ({})'.format(self.id,self.name,self.abbr) ######################### # Voting ######################### VoteOptionsType = Enum('Ja','Nej','Avstår','Frånvarande',name='vote_options') class Vote(Base): __tablename__ = 'votes' id = Column(Integer, primary_key=True) member_id = Column(Integer, ForeignKey('members.id')) polled_point_id = Column(Integer, ForeignKey('committee_report_points.id')) vote_option = Column(VoteOptionsType) def __repr__(self): return '{}: {}'.format(self.member.__repr__(),self.vote_option.__repr__()) ######################### # Documents #########################
class SubmissionResult(Base): """Class to store the evaluation results of a submission. """ # Possible statuses of a submission result. COMPILING and # EVALUATING do not necessarily imply we are going to schedule # compilation and evalution for these submission results: for # example, they might be for datasets not scheduled for # evaluation, or they might have passed the maximum number of # tries. If a submission result does not exists for a pair # (submission, dataset), its status can be implicitly assumed to # be COMPILING. COMPILING = 1 COMPILATION_FAILED = 2 EVALUATING = 3 SCORING = 4 SCORED = 5 __tablename__ = 'submission_results' __table_args__ = (UniqueConstraint('submission_id', 'dataset_id'), ) # Primary key is (submission_id, dataset_id). submission_id = Column(Integer, ForeignKey(Submission.id, onupdate="CASCADE", ondelete="CASCADE"), primary_key=True) submission = relationship(Submission, back_populates="results") dataset_id = Column(Integer, ForeignKey(Dataset.id, onupdate="CASCADE", ondelete="CASCADE"), primary_key=True) dataset = relationship(Dataset) # Now below follow the actual result fields. # Compilation outcome (can be None = yet to compile, "ok" = # compilation successful and we can evaluate, "fail" = # compilation unsuccessful, throw it away). compilation_outcome = Column(Enum("ok", "fail", name="compilation_outcome"), nullable=True) # The output from the sandbox (to allow localization the first item # of the list is a format string, possibly containing some "%s", # that will be filled in using the remaining items of the list). compilation_text = Column(ARRAY(String), nullable=False, default=[]) # Number of failures during compilation. compilation_tries = Column(Integer, nullable=False, default=0) # The compiler stdout and stderr. compilation_stdout = Column(Unicode, nullable=True) compilation_stderr = Column(Unicode, nullable=True) # Other information about the compilation. compilation_time = Column(Float, nullable=True) compilation_wall_clock_time = Column(Float, nullable=True) compilation_memory = Column(BigInteger, nullable=True) # Worker shard and sandbox where the compilation was performed. compilation_shard = Column(Integer, nullable=True) compilation_sandbox = Column(Unicode, nullable=True) # Evaluation outcome (can be None = yet to evaluate, "ok" = # evaluation successful). At any time, this should be equal to # evaluations != []. evaluation_outcome = Column(Enum("ok", name="evaluation_outcome"), nullable=True) # Number of failures during evaluation. evaluation_tries = Column(Integer, nullable=False, default=0) # Score as computed by ScoringService. Null means not yet scored. score = Column(Float, nullable=True) # Score details. It's a JSON-like structure containing information # that is given to ScoreType.get_html_details to generate an HTML # snippet that is shown on AWS and, if the user used a token, on # CWS to display the details of the submission. # For example, results for each testcases, subtask, etc. score_details = Column(JSONB, nullable=True) # The same as the last two fields, but only showing information # visible to the user (assuming they did not use a token on this # submission). public_score = Column(Float, nullable=True) public_score_details = Column(JSONB, nullable=True) # Subtask scores as computed by ScoringService. # Null means not yes scored. # This is essential when score mode is SCORE_MODE_MAX_SUBTASK. subtask_scores = Column(ARRAY(Float), nullable=True) public_subtask_scores = Column(ARRAY(Float), nullable=True) # Ranking score details. It is a list of strings that are going to # be shown in a single row in the table of submission in RWS. ranking_score_details = Column(ARRAY(String), nullable=True) # These one-to-many relationships are the reversed directions of # the ones defined in the "child" classes using foreign keys. executables = relationship( "Executable", collection_class=attribute_mapped_collection("filename"), cascade="all, delete-orphan", passive_deletes=True, back_populates="submission_result") evaluations = relationship("Evaluation", cascade="all, delete-orphan", passive_deletes=True, back_populates="submission_result") def get_status(self): """Return the status of this object. """ if not self.compiled(): return SubmissionResult.COMPILING elif self.compilation_failed(): return SubmissionResult.COMPILATION_FAILED elif not self.evaluated(): return SubmissionResult.EVALUATING elif not self.scored(): return SubmissionResult.SCORING else: return SubmissionResult.SCORED def get_evaluation(self, testcase): """Return the Evaluation of this SR on the given Testcase, if any testcase (Testcase): the testcase the returned evaluation will belong to. return (Evaluation|None): the (only!) evaluation of this submission result on the given testcase, or None if there isn't any. """ # Use IDs to avoid triggering a lazy-load query. assert self.dataset_id == testcase.dataset_id # XXX If self.evaluations is already loaded we can walk over it # and spare a query. # (We could use .one() and avoid a LIMIT but we would need to # catch a NoResultFound exception.) return self.sa_session.query(Evaluation)\ .filter(Evaluation.submission_result == self)\ .filter(Evaluation.testcase == testcase)\ .first() def get_max_evaluation_resources(self): """Return the maximum time and memory used by this result return (float|None, int|None): max used time in seconds and memory in bytes, or None if data is incomplete or unavailable. """ t, m = None, None if self.evaluated() and self.evaluations: for ev in self.evaluations: if ev.execution_time is not None \ and (t is None or t < ev.execution_time): t = ev.execution_time if ev.execution_memory is not None \ and (m is None or m < ev.execution_memory): m = ev.execution_memory return (t, m) def compiled(self): """Return whether the submission result has been compiled. return (bool): True if compiled, False otherwise. """ return self.compilation_outcome is not None @staticmethod def filter_compiled(): """Return a filtering expression for compiled submission results. """ return SubmissionResult.compilation_outcome.isnot(None) def compilation_failed(self): """Return whether the submission result did not compile. return (bool): True if the compilation failed (in the sense that there is a problem in the user's source), False if not yet compiled or compilation was successful. """ return self.compilation_outcome == "fail" @staticmethod def filter_compilation_failed(): """Return a filtering expression for submission results failing compilation. """ return SubmissionResult.compilation_outcome == "fail" def compilation_succeeded(self): """Return whether the submission compiled. return (bool): True if the compilation succeeded (in the sense that an executable was created), False if not yet compiled or compilation was unsuccessful. """ return self.compilation_outcome == "ok" @staticmethod def filter_compilation_succeeded(): """Return a filtering expression for submission results passing compilation. """ return SubmissionResult.compilation_outcome == "ok" def evaluated(self): """Return whether the submission result has been evaluated. return (bool): True if evaluated, False otherwise. """ return self.evaluation_outcome is not None @staticmethod def filter_evaluated(): """Return a filtering lambda for evaluated submission results. """ return SubmissionResult.evaluation_outcome.isnot(None) def needs_scoring(self): """Return whether the submission result needs to be scored. return (bool): True if in need of scoring, False otherwise. """ return (self.compilation_failed() or self.evaluated()) and \ not self.scored() def scored(self): """Return whether the submission result has been scored. return (bool): True if scored, False otherwise. """ return all( getattr(self, k) is not None for k in [ "score", "subtask_scores", "public_score", "public_subtask_scores", "ranking_score_details" ]) @staticmethod def filter_scored(): """Return a filtering lambda for scored submission results. """ return ((SubmissionResult.score.isnot(None)) & (SubmissionResult.score_details.isnot(None)) & (SubmissionResult.public_score.isnot(None)) & (SubmissionResult.public_score_details.isnot(None)) & (SubmissionResult.ranking_score_details.isnot(None))) def invalidate_compilation(self): """Blank all compilation and evaluation outcomes, and the score. """ self.invalidate_evaluation() self.compilation_outcome = None self.compilation_text = [] self.compilation_tries = 0 self.compilation_time = None self.compilation_wall_clock_time = None self.compilation_memory = None self.compilation_shard = None self.compilation_sandbox = None self.executables = {} def invalidate_evaluation(self): """Blank the evaluation outcome, all evaluations, and the score. """ self.invalidate_evaluation_result() self.evaluations = [] def invalidate_evaluation_result(self): """Blank the evaluation outcome and the score. """ self.invalidate_score() self.evaluation_outcome = None self.evaluation_tries = 0 def invalidate_score(self): """Blank the score. """ self.score = None self.score_details = None self.public_score = None self.public_score_details = None self.ranking_score_details = None def set_compilation_outcome(self, success): """Set the compilation outcome based on the success. success (bool): if the compilation was successful. """ self.compilation_outcome = "ok" if success else "fail" def set_evaluation_outcome(self): """Set the evaluation outcome (always ok now). """ self.evaluation_outcome = "ok"
class SpeakerAppointment(Appointment): position = Column(Enum('Talman','Förste vice talman','Andre vice talman','Tredje vice talman',name='speaker_positions')) __mapper_args__ = { 'polymorphic_identity':'speaker_appointment' }
class Place(Base): """ (nestable) Groups of nearby physical rooms """ __tablename__ = 'places' id = Column(Integer, primary_key=True) name = Column(Unicode(255), nullable=False) parent_id = Column(Integer, ForeignKey(id)) type = Column(Enum("staircase", "building", "road", name='place_type')) latitude = Column(Float) longitude = Column(Float) parent = relationship(lambda: Place, backref="children", remote_side=[id]) __table_args__ = (UniqueConstraint(parent_id, name, name='_child_name_uc'), ) @property def path(self): if self.parent is None: # don't list the root node return [] else: return self.parent.path + [self] def __repr__(self): return "<Place {}>".format(' / '.join(c.name for c in self.path)) def pretty_name(self, relative_to=None): """ Produce a pretty name relative to a Place. Traversing down the tree of Clusters, changing `relative_to` as we go gives: 1 Mortimer road House 1 or Tree court, Staircase S Staircase S """ def get_parent(x): if x.parent == relative_to: return None return x.parent if self.type == 'staircase': parent = get_parent(self) if parent: return '{}, {} Staircase'.format( parent.pretty_name(relative_to), self.name) else: return '{} Staircase'.format(self.name) if self.type == 'building': road = self.parent if road and road.type == 'road': # add the road name if road != relative_to: return "{} {}".format(self.name, road.name) else: return "House {}".format(self.name) return self.name @property def geocoords(self): current = self while current: if current.latitude and current.longitude: return current.latitude, current.longitude current = current.parent @property def all_rooms_q(self): from sqlalchemy.orm import joinedload from sqlalchemy.orm.strategy_options import Load # circular import is guarded by function from . import Room, RoomListing, Occupancy # this should autoload all the subplaces of this place descendant_places = (object_session(self).query(Place).filter( Place.id == self.id).cte(name='descendant_places', recursive=True)) descendant_places = descendant_places.union_all( object_session(self).query(Place).filter( Place.parent_id == descendant_places.c.id)) # make this not ridiculously slow opts = (Load(Room).joinedload(Room.listing_for).joinedload( RoomListing.occupancies).load_only(Occupancy.resident_id), Load(Room).joinedload(Room.listing_for).subqueryload( RoomListing.audience_types), Load(Room).joinedload( Room.listing_for).undefer(RoomListing.bad_listing), Load(Room).subqueryload(Room.stats)) return object_session(self).query(Room).join( descendant_places).options(*opts)
def __init__(self, enum): self.enum = enum constraint = 'ck{0}'.format( re.sub('([A-Z])', lambda m: '_' + m.group(1).lower(), enum.__name__)) self.impl = Enum(*enum.values(), name=constraint)
class Box(DatabaseObject): """ Box definition """ uuid = Column(String(36), unique=True, nullable=False, default=lambda: str(uuid4())) corporation_id = Column(Integer, ForeignKey("corporation.id"), nullable=False) category_id = Column(Integer, ForeignKey("category.id"), nullable=True) _name = Column(Unicode(32), unique=True, nullable=False) _operating_system = Column(Unicode(16)) _description = Column(Unicode(1024)) _capture_message = Column(Unicode(1024)) _difficulty = Column(Unicode(16)) game_level_id = Column(Integer, ForeignKey("game_level.id"), nullable=False) _avatar = Column(String(64)) _value = Column(Integer, nullable=True) _locked = Column(Boolean, default=False, nullable=False) garbage = Column( String(32), unique=True, nullable=False, default=lambda: encode(urandom(16), "hex"), ) teams = relationship( "Team", secondary=team_to_box, back_populates="boxes", lazy="select" ) hints = relationship( "Hint", backref=backref("box", lazy="select"), cascade="all,delete,delete-orphan", ) flags = relationship( "Flag", backref=backref("box", lazy="select"), cascade="all,delete,delete-orphan", order_by="desc(-Flag._order)", ) flag_submission_type = Column( Enum(FlagsSubmissionType), default=FlagsSubmissionType.CLASSIC ) ip_addresses = relationship( "IpAddress", backref=backref("box", lazy="select"), cascade="all,delete,delete-orphan", ) @classmethod def all(cls): """ Returns a list of all objects in the database """ return dbsession.query(cls).all() @classmethod def by_id(cls, _id): """ Returns a the object with id of _id """ return dbsession.query(cls).filter_by(id=_id).first() @classmethod def by_uuid(cls, _uuid): """ Return and object based on a uuid """ return dbsession.query(cls).filter_by(uuid=str(_uuid)).first() @classmethod def by_name(cls, name): """ Return the box object whose name is "name" """ return dbsession.query(cls).filter_by(_name=str(name)).first() @classmethod def by_category(cls, _cat_id): """ Return the box object whose category is "_cat_id" """ return dbsession.query(cls).filter_by(category_id=int(_cat_id)).all() @classmethod def by_garbage(cls, _garbage): return dbsession.query(cls).filter_by(garbage=_garbage).first() @classmethod def by_ip_address(cls, ip_addr): """ Returns a box object based on an ip address, supports both ipv4 and ipv6 """ ip = IpAddress.by_address(ip_addr) return ip.box if ip is not None else None @classmethod def flaglist(self, box_id=None): flags = self.by_id(box_id).flags flaglist = OrderedDict() for flag in flags: flaglist[flag.uuid] = flag.name return flaglist @property def name(self): return self._name @name.setter def name(self, value): if not 3 <= len(str(value)) <= 32: raise ValidationError("Name must be 3 - 32 characters") self._name = str(value) @property def operating_system(self): return self._operating_system if self._operating_system else "?" @operating_system.setter def operating_system(self, value): self._operating_system = str(value) @property def description(self): if self._description is None: self._description = "" ls = [] if 0 < len(self._description): text = self._description.replace("\r\n", "\n").strip() ls.append("%s" % text) else: ls.append("No information on file.") if self.difficulty != "Unknown": ls.append("Reported Difficulty: %s" % self.difficulty) if not encode(ls[-1], "utf-8").endswith(b"\n"): ls[-1] = ls[-1] + "\n" return str("\n\n".join(ls)) @description.setter def description(self, value): if value is None: return "" if 1025 < len(value): raise ValidationError("Description cannot be greater than 1024 characters") self._description = str(value) @property def difficulty(self): return ( self._difficulty if self._difficulty and len(self._difficulty) else "Unknown" ) @difficulty.setter def difficulty(self, value): if value is None: return if 17 < len(value): raise ValidationError("Difficulty cannot be greater than 16 characters") self._difficulty = str(value) @property def capture_message(self): return self._capture_message if self._capture_message else "" @capture_message.setter def capture_message(self, value): self._capture_message = str(value) @property def value(self): if not self._value: return 0 return self._value @value.setter def value(self, value): try: self._value = abs(int(value)) except ValueError: raise ValidationError("Reward value must be an integer") @property def locked(self): """ Determines if an admin has locked an box. """ return self._locked @locked.setter def locked(self, value): """ Setter method for _lock """ assert isinstance(value, bool) self._locked = value @property def avatar(self): if self._avatar is not None: return self._avatar else: avatar = get_new_avatar("box") if not avatar.startswith("default_"): self._avatar = avatar dbsession.add(self) dbsession.commit() return avatar @avatar.setter def avatar(self, image_data): if self.uuid is None: self.uuid = str(uuid4()) if len(image_data) < (1024 * 1024): ext = imghdr.what("", h=image_data) if ext in ["png", "jpeg", "gif", "bmp"] and not is_xss_image(image_data): try: if self._avatar is not None and os.path.exists( options.avatar_dir + "/upload/" + self._avatar ): os.unlink(options.avatar_dir + "/upload/" + self._avatar) file_path = str( options.avatar_dir + "/upload/" + self.uuid + "." + ext ) image = Image.open(io.BytesIO(image_data)) cover = resizeimage.resize_cover(image, [500, 250]) cover.save(file_path, image.format) self._avatar = "upload/" + self.uuid + "." + ext except Exception as e: raise ValidationError(e) else: raise ValidationError( "Invalid image format, avatar must be: .png .jpeg .gif or .bmp" ) else: raise ValidationError("The image is too large") @property def ipv4s(self): """ Return a list of all ipv4 addresses """ return [ip for ip in self.ip_addresses if ip.version == 4] @property def ipv6s(self): """ Return a list of all ipv6 addresses """ return [ip for ip in self.ip_addresses if ip.version == 6] @property def visible_ips(self): return [ip for ip in self.ip_addresses if ip.visible is True] @property def source_code(self): return SourceCode.by_box_id(self.id) def get_garbage_cfg(self): try: hex_name = encode(self.name).hex() except AttributeError: hex_name = encode(self.name, "hex") return "[Bot]\nname = %s\ngarbage = %s\n" % (hex_name, self.garbage) def is_complete(self, user): boxcomplete = True for boxflag in self.flags: if user.team and boxflag not in user.team.flags: boxcomplete = False break return boxcomplete def to_xml(self, parent): """ Convert object to XML """ box_elem = ET.SubElement(parent, "box") box_elem.set("gamelevel", "%s" % str(self.game_level.number)) ET.SubElement(box_elem, "name").text = self.name ET.SubElement(box_elem, "operatingsystem").text = self._operating_system ET.SubElement(box_elem, "description").text = self._description ET.SubElement(box_elem, "capture_message").text = self.capture_message ET.SubElement(box_elem, "value").text = str(self.value) ET.SubElement(box_elem, "flag_submission_type").text = FlagsSubmissionType( self.flag_submission_type ).name ET.SubElement(box_elem, "difficulty").text = self._difficulty ET.SubElement(box_elem, "garbage").text = str(self.garbage) if self.category_id: ET.SubElement(box_elem, "category").text = Category.by_id( self.category_id ).category flags_elem = ET.SubElement(box_elem, "flags") flags_elem.set("count", "%s" % str(len(self.flags))) for flag in self.flags: flag.to_xml(flags_elem) hints_elem = ET.SubElement(box_elem, "hints") count = 0 for hint in self.hints: if hint.flag_id is None: hint.to_xml(hints_elem) count += 1 hints_elem.set("count", "%s" % str(count)) ips_elem = ET.SubElement(box_elem, "ipaddresses") ips_elem.set("count", "%s" % str(len(self.ip_addresses))) for ip in self.ip_addresses: ip.to_xml(ips_elem) avatarfile = os.path.join(options.avatar_dir, self.avatar) if self.avatar and os.path.isfile(avatarfile): with open(avatarfile, mode="rb") as _avatar: data = _avatar.read() ET.SubElement(box_elem, "avatar").text = encode(data, "base64") else: ET.SubElement(box_elem, "avatar").text = "none" def to_dict(self): """ Returns editable data as a dictionary """ corp = Corporation.by_id(self.corporation_id) game_level = GameLevel.by_id(self.game_level_id) cat = Category.by_id(self.category_id) if cat: category = cat.uuid else: category = "" return { "name": self.name, "uuid": self.uuid, "corporation": corp.uuid, "category": category, "operating_system": self.operating_system, "description": self._description, "capture_message": self.capture_message, "difficulty": self.difficulty, "game_level": game_level.uuid, "flag_submission_type": self.flag_submission_type, "flaglist": self.flaglist(self.id), "value": self.value, } def __repr__(self): return "<Box - name: %s>" % (self.name,) def __str__(self): return self.name
class LayerV1(Layer): # Deprecated in v2 __label__ = _(u"Layer") __plural__ = _(u"Layers") __tablename__ = "layerv1" __table_args__ = {"schema": _schema} __acl__ = [ (Allow, AUTHORIZED_ROLE, ALL_PERMISSIONS), ] __mapper_args__ = {"polymorphic_identity": "layerv1"} id = Column(Integer, ForeignKey(_schema + ".layer.id"), primary_key=True) is_checked = Column(Boolean, default=True, label=_(u"Checked")) # by default icon = Column(Unicode, label=_(u"Icon")) # on the tree layer_type = Column(Enum("internal WMS", "external WMS", "WMTS", "no 2D", native_enum=False), label=_(u"Type")) url = Column(Unicode, label=_(u"Base URL")) # for externals image_type = Column(Enum("image/jpeg", "image/png", native_enum=False), label=_(u"Image type")) # for WMS style = Column(Unicode, label=_(u"Style")) dimensions = Column(Unicode, label=_(u"Dimensions")) # for WMTS matrix_set = Column(Unicode, label=_(u"Matrix set")) # for WMTS wms_url = Column(Unicode, label=_(u"WMS server URL")) # for WMTS wms_layers = Column(Unicode, label=_(u"WMS layers")) # for WMTS query_layers = Column(Unicode, label=_(u"Query layers")) # for WMTS kml = Column(Unicode, label=_(u"KML 3D")) # for kml 3D is_single_tile = Column(Boolean, label=_(u"Single tile")) # for extenal WMS legend = Column(Boolean, default=True, label=_(u"Display legend")) # on the tree legend_image = Column(Unicode, label=_(u"Legend image")) # fixed legend image legend_rule = Column( Unicode, label=_(u"Legend rule")) # on wms legend only one rule is_legend_expanded = Column(Boolean, default=False, label=_(u"Legend expanded")) min_resolution = Column( Float, label=_(u"Min resolution")) # for all except internal WMS max_resolution = Column( Float, label=_(u"Max resolution")) # for all except internal WMS disclaimer = Column(Unicode, label=_(u"Disclaimer")) # data attribute field in which application can find a human identifiable name or number identifier_attribute_field = Column(Unicode, label=_(u"Identifier attribute field")) time_mode = Column(Enum("disabled", "value", "range", native_enum=False), default="disabled", nullable=False, label=_(u"Time mode")) time_widget = Column(Enum("slider", "datepicker", native_enum=False), default="slider", nullable=True, label=_(u"Time widget")) def __init__(self, name=u"", public=True, icon=u"", layer_type=u"internal WMS"): Layer.__init__(self, name=name, public=public) self.icon = icon self.layer_type = layer_type
class MantisBug(GenericTable): __tablename__ = "mantisbugs" __table_args__ = (UniqueConstraint("external_id", "tracker_id"), ) # Object ID id = Column(Integer, primary_key=True) summary = Column(String(256), nullable=False) status = Column(Enum(*BUG_STATES, name="mantisbug_status"), nullable=False) resolution = Column(Enum(*BUG_RESOLUTIONS, name="mantisbug_resolution"), nullable=True) duplicate_id = Column(Integer, ForeignKey("{0}.id".format(__tablename__)), nullable=True, index=True) creation_time = Column(DateTime, nullable=False) last_change_time = Column(DateTime, nullable=False) # ID in the external bug tracker external_id = Column(Integer, nullable=False, index=True) # ID of the bug tracker tracker_id = Column(Integer, ForeignKey("{0}.id".format(Bugtracker.__tablename__)), nullable=False) opsysrelease_id = Column(Integer, ForeignKey("{0}.id".format( OpSysRelease.__tablename__)), nullable=False, index=True) component_id = Column(Integer, ForeignKey("{0}.id".format( OpSysComponent.__tablename__)), nullable=False, index=True) tracker = relationship(Bugtracker, backref="mantis_bugs") opsysrelease = relationship(OpSysRelease) component = relationship(OpSysComponent) def __str__(self): return '{0}#{1}'.format(self.tracker.abbr, self.external_id) def order(self): return BUG_STATES.index(self.status) @property def url(self): return "{0}{1}".format(self.tracker.web_url, self.external_id) @property def serialize(self): return { 'id': self.id, 'summary': self.summary, 'status': self.status, 'resolution': self.resolution, 'duplicate_id': self.duplicate_id, 'creation_time': self.creation_time, 'last_change_time': self.last_change_time, 'external_id': self.external_id, 'tracker_id': self.tracker_id, 'opsysrelease_id': self.opsysrelease_id, 'component_id': self.component_id, 'type': 'MANTIS' }
class Task(Base): """Class to store a task. """ __tablename__ = 'tasks' __table_args__ = ( UniqueConstraint('contest_id', 'num'), UniqueConstraint('contest_id', 'name'), ForeignKeyConstraint( ("id", "active_dataset_id"), ("datasets.task_id", "datasets.id"), onupdate="SET NULL", ondelete="SET NULL", # Use an ALTER query to set this foreign key after # both tables have been CREATEd, to avoid circular # dependencies. use_alter=True, name="fk_active_dataset_id"), CheckConstraint("token_gen_initial <= token_gen_max"), ) # Auto increment primary key. id = Column( Integer, primary_key=True, # Needed to enable autoincrement on integer primary keys that # are referenced by a foreign key defined on this table. autoincrement='ignore_fk') # Number of the task for sorting. num = Column(Integer, nullable=False) # Contest (id and object) owning the task. contest_id = Column(Integer, ForeignKey(Contest.id, onupdate="CASCADE", ondelete="CASCADE"), nullable=False, index=True) contest = relationship(Contest, backref=backref( 'tasks', collection_class=ordering_list('num'), order_by=[num], cascade="all, delete-orphan", passive_deletes=True)) # Short name and long human readable title of the task. name = Column(Unicode, nullable=False) title = Column(Unicode, nullable=False) # A JSON-encoded lists of strings: the language codes of the # statements that will be highlighted to all users for this task. primary_statements = Column(String, nullable=False, default="[]") # The parameters that control task-tokens follow. Note that their # effect during the contest depends on the interaction with the # parameters that control contest-tokens, defined on the Contest. # The "kind" of token rules that will be active during the contest. # - disabled: The user will never be able to use any token. # - finite: The user has a finite amount of tokens and can choose # when to use them, subject to some limitations. Tokens may not # be all available at start, but given periodically during the # contest instead. # - infinite: The user will always be able to use a token. token_mode = Column(Enum("disabled", "finite", "infinite", name="token_mode"), nullable=False, default="disabled") # The maximum number of tokens a contestant is allowed to use # during the whole contest (on this tasks). token_max_number = Column(Integer, CheckConstraint("token_max_number > 0"), nullable=True) # The minimum interval between two successive uses of tokens for # the same user (on this task). token_min_interval = Column( Interval, CheckConstraint("token_min_interval >= '0 seconds'"), nullable=False, default=timedelta()) # The parameters that control generation (if mode is "finite"): # the user starts with "initial" tokens and receives "number" more # every "interval", but their total number is capped to "max". token_gen_initial = Column(Integer, CheckConstraint("token_gen_initial >= 0"), nullable=False, default=2) token_gen_number = Column(Integer, CheckConstraint("token_gen_number >= 0"), nullable=False, default=2) token_gen_interval = Column( Interval, CheckConstraint("token_gen_interval > '0 seconds'"), nullable=False, default=timedelta(minutes=30)) token_gen_max = Column(Integer, CheckConstraint("token_gen_max > 0"), nullable=True) # Maximum number of submissions or user_tests allowed for each user # on this task during the whole contest or None to not enforce # this limitation. max_submission_number = Column( Integer, CheckConstraint("max_submission_number > 0"), nullable=True) max_user_test_number = Column(Integer, CheckConstraint("max_user_test_number > 0"), nullable=True) # Minimum interval between two submissions or user_tests for this # task, or None to not enforce this limitation. min_submission_interval = Column( Interval, CheckConstraint("min_submission_interval > '0 seconds'"), nullable=True) min_user_test_interval = Column( Interval, CheckConstraint("min_user_test_interval > '0 seconds'"), nullable=True) # The scores for this task will be rounded to this number of # decimal places. score_precision = Column(Integer, CheckConstraint("score_precision >= 0"), nullable=False, default=0) # Active Dataset (id and object) currently being used for scoring. # The ForeignKeyConstraint for this column is set at table-level. active_dataset_id = Column(Integer, nullable=True) active_dataset = relationship( 'Dataset', foreign_keys=[active_dataset_id], # XXX In SQLAlchemy 0.8 we could remove this: primaryjoin='Task.active_dataset_id == Dataset.id', # Use an UPDATE query *after* an INSERT query (and *before* a # DELETE query) to set (and unset) the column associated to # this relationship. post_update=True)
owner = relationship( "Human", secondary="persona_control", primaryjoin= "and_(Persona.id==PersonaControl.persona_id, PersonaControl.rel=='owns')", single_parent=True, uselist=False) def __repr__(self): return "<Persona {0!r} on {1!r}>".format( self.names, [account.service.name for account in self.accounts]) PERSONARELATIONSHIP = Enum("owns", "participates", "suspected", name="_persona_rel") class PersonaControl(Base, UUIDed): """Associates Humans and Personas with PERSONARELATIONSHIPs. This allows us to somewhat decouple the idea of relating personas to humans from a hardcoded schema which may be subject to refactoring or future evolution while retaining indexable relations. """ human_id = Column(UUID, ForeignKey("human.id")) human = relationship("Human")
class UserReferral(Base): __tablename__ = 'UserReferral' requester = Column('requester', BIGINT, ForeignKey('User.id'), primary_key=True) reference = Column('reference', BIGINT, ForeignKey('User.id')) status = Column('status', Enum(ReferralStatus), nullable=False, default=ReferralStatus.PENDING) time = Column('time', DateTime, default=datetime.datetime.utcnow, nullable=False) def __init__(self, requester: int=None, reference: int=None, status: ReferralStatus=None, time: datetime=None): self.requester = requester self.reference = reference self.status = status self.time = time @classmethod def add(cls, requester: int, reference: int) -> 'UserReferral': user_referral = UserReferral(requester, reference) db.session.add(user_referral) db.session.commit() db.session.refresh(user_referral) return user_referral @classmethod def get_all_by_reference(cls, session, reference_id: int) -> List['UserReferral']: assert reference_id > 0, 'Invalid reference id' return session.query(UserReferral).filter_by(reference=reference_id).all() @classmethod def get_referral(cls, requester_id: int) -> Optional['UserReferral']: assert requester_id > 0 referral = db.session.query(cls).filter_by(requester=requester_id).one_or_none() return referral @classmethod def get_referrals(cls, reference_id: int) -> List['UserReferral']: assert reference_id > 0 referrals = db.session.query(cls).filter_by(reference=reference_id).all() return referrals @classmethod def get_approved(cls, reference_id: int) -> List['UserReferral']: assert reference_id > 0 referrals = db.session.query(cls)\ .filter_by(reference=reference_id)\ .filter_by(status=ReferralStatus.APPROVED)\ .all() return referrals @classmethod def get_pending(cls, reference_id: int) -> List['UserReferral']: assert reference_id > 0 referrals = db.session.query(cls)\ .filter_by(reference=reference_id)\ .filter_by(status=ReferralStatus.PENDING)\ .all() return referrals def approve(self): user = User.get_by_id(self.requester) user.status = UserStatus.APPROVED self.status = ReferralStatus.APPROVED db.session.commit() db.session.refresh(self) def deny(self): self.status = ReferralStatus.DENIED db.session.commit() db.session.refresh(self) def json(cls, deep: bool=False) -> Dict[str, Any]: return dict( requester=User.get_by_id(cls.requester).json(), reference=cls.reference, status=cls.status.value, timestamp=cls.time.isoformat() )
class LayerV1(Layer): # Deprecated in v2 __tablename__ = 'layerv1' __table_args__ = {'schema': _schema} __colanderalchemy_config__ = { 'title': _('Layer V1'), 'plural': _('Layers V1') } __c2cgeoform_config__ = { 'duplicate': True } __mapper_args__ = {'polymorphic_identity': 'layerv1'} id = Column(Integer, ForeignKey(_schema + '.layer.id'), primary_key=True, info={ 'colanderalchemy': { 'missing': None, 'widget': HiddenWidget() }} ) layer = Column(Unicode, info={ 'colanderalchemy': { 'title': _('Layer name') }}) is_checked = Column(Boolean, default=True, info={ 'colanderalchemy': { 'title': _('Is checked'), 'group': 'tree' }}) # by default icon = Column(Unicode, info={ 'colanderalchemy': { 'title': _('Icon'), 'group': 'tree' }}) # on the tree layer_type = Column( Enum( 'internal WMS', 'external WMS', 'WMTS', 'no 2D', native_enum=False), info={ 'colanderalchemy': { 'title': _('Layer type'), 'widget': SelectWidget( values=( ('', _('- Select -')), ('internal WMS', _('internal WMS')), ('external WMS', _('external WMS')), ('WMTS', _('WMTS')), ('no 2D', _('no 2D')) ))} }) url = Column(Unicode, info={ 'colanderalchemy': { 'title': _('URL'), 'group': 'external' }}) # for externals image_type = Column( Enum( 'image/jpeg', 'image/png', native_enum=False), info={ 'colanderalchemy': { 'title': _('Image type'), 'widget': SelectWidget( values=( ('', _('- Select -')), ('image/jpeg', 'image/jpeg'), ('image/png', 'image/png') ))} }) # for WMS style = Column(Unicode, info={ 'colanderalchemy': { 'title': _('Style') }}) dimensions = Column(Unicode, info={ 'colanderalchemy': { 'title': _('Dimensions'), 'group': 'WMTS' }}) # for WMTS matrix_set = Column(Unicode, info={ 'colanderalchemy': { 'title': _('Matrix set'), 'group': 'WMTS' }}) # for WMTS wms_url = Column(Unicode, info={ 'colanderalchemy': { 'title': _('WMS URL'), 'group': 'WMTS' }}) # for WMTS wms_layers = Column(Unicode, info={ 'colanderalchemy': { 'title': _('WMS layers'), 'group': 'WMTS' }}) # for WMTS query_layers = Column(Unicode, info={ 'colanderalchemy': { 'title': _('Query layers'), 'group': 'WMTS' }}) # for WMTS kml = Column(Unicode, info={ 'colanderalchemy': { 'title': _('KML'), 'group': 'other' }}) # for kml 3D is_single_tile = Column(Boolean, info={ 'colanderalchemy': { 'title': _('Is single tile'), 'group': 'external' }}) # for extenal WMS legend = Column(Boolean, default=True, info={ 'colanderalchemy': { 'title': _('Legend'), 'group': 'tree' }}) # on the tree legend_image = Column(Unicode, info={ 'colanderalchemy': { 'title': _('Legend image'), 'group': 'tree' }}) # fixed legend image legend_rule = Column(Unicode, info={ 'colanderalchemy': { 'title': _('Legend rule'), 'group': 'tree' }}) # on wms legend only one rule is_legend_expanded = Column(Boolean, default=False, info={ 'colanderalchemy': { 'title': _('Is legend expanded'), 'group': 'tree' }}) min_resolution = Column(Float, info={ 'colanderalchemy': { 'title': _('Min resolution'), 'group': 'other' }}) # for all except internal WMS max_resolution = Column(Float, info={ 'colanderalchemy': { 'title': _('Max resolution'), 'group': 'other' }}) # for all except internal WMS disclaimer = Column(Unicode, info={ 'colanderalchemy': { 'title': _('Disclaimer'), 'group': 'other' }}) # data attribute field in which application can find a human identifiable name or number identifier_attribute_field = Column(Unicode, info={ 'colanderalchemy': { 'title': _('Identifier field'), 'group': 'other' }}) time_mode = Column(Enum( 'disabled', 'value', 'range', native_enum=False), default='disabled', nullable=False, info={ 'colanderalchemy': { 'title': _('Time mode'), 'group': 'other', 'widget': SelectWidget(values=( ('disabled', _('Disabled')), ('value', _('Value')), ('range', _('Range')) )) }} ) time_widget = Column( Enum('slider', 'datepicker', native_enum=False), default='slider', nullable=True, info={ 'colanderalchemy': { 'title': _('Time widget'), 'group': 'other', 'widget': SelectWidget(values=( ('slider', _('Slider')), ('datepicker', _('Datepicker')) )) }} ) def __init__( self, name: str='', public: bool=True, icon: str='', layer_type: str='internal WMS' ) -> None: Layer.__init__(self, name=name, public=public) self.layer = name self.icon = icon self.layer_type = layer_type
class User(Base, Entity): __tablename__ = 'User' id = Column('id', BIGINT, primary_key=True, autoincrement=True) created = Column('created', DateTime, nullable=False, default=datetime.datetime.utcnow) email = Column('email', VARCHAR(255), unique=True, nullable=False) password = Column('password', Password, nullable=False) status = Column('status', Enum(UserStatus), nullable=False, default=UserStatus.REQUESTED) name = Column('name', VARCHAR(255), unique=False, nullable=True) active = Column('active', BOOLEAN, nullable=False, default=False) disabled = Column('disabled', BOOLEAN, nullable=False, default=False) expo_token = Column('expo_token', VARCHAR(255), nullable=True) login_count = Column('login_count', INT, nullable=False) pitt_pantry = Column('pitt_pantry', BOOLEAN, nullable=False, default=False) eagerness = Column('eagerness', INT, nullable=False, default=3) email_subscription = Column('email_subscription', BOOLEAN, nullable=False, default=True) primary_affiliation = Column('primary_affiliation', SMALLINT, ForeignKey("PrimaryAffiliation.id"), unique=False, nullable=True) # mappings roles = association_proxy('_user_roles', 'role') expo_tokens = association_proxy('_user_expo_tokens', 'token') food_preferences = association_proxy('_user_foodpreferences', 'food_preference') recommended_events = association_proxy('_user_recommended_events', 'event') accepted_events = association_proxy('_user_accepted_events', 'event') checkedin_events = association_proxy('_user_checkedin_events', 'event') affiliation = relationship("PrimaryAffiliation", foreign_keys=[primary_affiliation]) def __init__( self, id: int=None, email: str=None, password: str=None, status: UserStatus=None, name: str=None, active: bool=False, disabled: bool=False, login_count: int=0, expo_token: str=None, pitt_pantry: bool=False, eagerness: int=3, primary_affiliation: int=None): self.id = id self.created = datetime.datetime.utcnow() self.email = email self.password = password self.status = status self.name = name self.active = active self.disabled = disabled self.login_count = login_count self.expo_token = expo_token self.pitt_pantry = pitt_pantry self.eagerness = eagerness self.primary_affiliation = primary_affiliation @property def valid(self): """Check if user is valid i.e., active, ACCEPTED, and not disables """ return self.active and self.status is UserStatus.ACCEPTED and not self.disabled @property def is_admin(self) -> bool: return 'Admin' in [r.name for r in self.roles] @property def is_host(self) -> bool: return 'Host' in [r.name for r in self.roles] @classmethod def get_by_email(cls, session, email: str) -> Optional['User']: """ Get user by email :param session: database session :param email: user email :return: user """ return session.query(cls)\ .filter_by(email=email)\ .one_or_none() @classmethod def create(cls, session, user: '******', roles: List['Role']=None) -> Optional['User']: """ Create new user and add to database Will always be given the role User :param session: database session :param user: user object :param role: user role (always adds User by default) :return: user, or none if error """ if User.get_by_email(session, user.email) is not None: return None session.add(user) session.commit() session.refresh(user) session.add(UserRole(user.id, Role.get_by_name(session, 'User').id)) if roles is not None: for role in roles: session.add(UserRole(user.id, role.id)) return user @classmethod def verify_credentials(cls, session, email: str, password: str) -> bool: user = User.get_by_email(session, email) if user is None: return False return bcrypt_sha256.verify(password, user.password) @classmethod def next_users_to_permit(cls, session) -> List['User']: limit = int(Property.get_by_name(session, 'user.threshold').value) return session.query(cls)\ .filter(cls.status == UserStatus.REQUESTED)\ .order_by(cls.id.asc())\ .limit(limit)\ .all() def verify_password(self, password: str) -> bool: return bcrypt_sha256.verify(password, self.password)
class LayerWMS(DimensionLayer): __tablename__ = 'layer_wms' __table_args__ = {'schema': _schema} __colanderalchemy_config__ = { 'title': _('WMS Layer'), 'plural': _('WMS Layers') } __c2cgeoform_config__ = { 'duplicate': True } __mapper_args__ = {'polymorphic_identity': 'l_wms'} id = Column(Integer, ForeignKey(_schema + '.layer.id', ondelete='CASCADE'), primary_key=True, info={ 'colanderalchemy': { 'missing': None, 'widget': HiddenWidget() }}) ogc_server_id = Column(Integer, ForeignKey(_schema + '.ogc_server.id'), nullable=False, info={ 'colanderalchemy': { 'title': _('OGC server'), 'column': 2, 'widget': deform_ext.RelationSelect2Widget( OGCServer, 'id', 'name', order_by='name', default_value=('', _('- Select -'))) }}) layer = Column(Unicode, nullable=False, info={ 'colanderalchemy': { 'title': _('WMS layer name'), 'column': 2 }}) style = Column(Unicode, info={ 'colanderalchemy': { 'title': _('Style'), 'column': 2 }}) time_mode = Column(Enum( 'disabled', 'value', 'range', native_enum=False), default='disabled', nullable=False, info={ 'colanderalchemy': { 'title': _('Time mode'), 'column': 2, 'widget': SelectWidget(values=( ('disabled', _('Disabled')), ('value', _('Value')), ('range', _('Range')) )) }} ) time_widget = Column(Enum( 'slider', 'datepicker', native_enum=False), default='slider', nullable=False, info={ 'colanderalchemy': { 'title': _('Time widget'), 'column': 2, 'widget': SelectWidget(values=( ('slider', _('Slider')), ('datepicker', _('Datepicker')) )) }} ) # relationship with OGCServer ogc_server = relationship( 'OGCServer', info={ 'colanderalchemy': { 'title': _('OGC server'), 'exclude': True }} ) def __init__( self, name: str='', layer: str='', public: bool=True, time_mode: str='disabled', time_widget: str='slider' ) -> None: DimensionLayer.__init__(self, name=name, public=public) self.layer = layer self.time_mode = time_mode self.time_widget = time_widget @staticmethod def get_default(dbsession: Session) -> DimensionLayer: return dbsession.query(LayerWMS).filter(LayerWMS.name == 'wms-defaults').one_or_none()
class Record(db.Model): __tablename__ = "records" __table_args__ = (db.UniqueConstraint('user_id', 'sport_id', 'record_type', name='user_sports_records'), ) id = db.Column(db.Integer, primary_key=True, autoincrement=True) user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False) sport_id = db.Column(db.Integer, db.ForeignKey('sports.id'), nullable=False) activity_id = db.Column(db.Integer, db.ForeignKey('activities.id'), nullable=False) record_type = db.Column(Enum(*record_types, name="record_types")) activity_date = db.Column(db.DateTime, nullable=False) _value = db.Column("value", db.Integer, nullable=True) def __str__(self): return (f'<Record {self.sports.label} - ' f'{self.record_type} - ' f"{self.activity_date.strftime('%Y-%m-%d')}>") def __init__(self, activity, record_type): self.user_id = activity.user_id self.sport_id = activity.sport_id self.activity_id = activity.id self.record_type = record_type self.activity_date = activity.activity_date @hybrid_property def value(self): if self._value is None: return None if self.record_type == 'LD': return datetime.timedelta(seconds=self._value) elif self.record_type in ['AS', 'MS']: return float(self._value / 100) else: # 'FD' return float(self._value / 1000) @value.setter def value(self, val): self._value = convert_value_to_integer(self.record_type, val) def serialize(self): if self.value is None: value = None elif self.record_type in ['AS', 'FD', 'MS']: value = float(self.value) else: # 'LD' value = str(self.value) return { "id": self.id, "user_id": self.user_id, "sport_id": self.sport_id, "activity_id": self.activity_id, "record_type": self.record_type, "activity_date": self.activity_date, "value": value, }