class GroupProduct(db.Model): """Группы наименований""" id = db.Column(db.Integer(), primary_key=True) name = db.Column('name', db.String(50)) descr = db.Column('descr', db.String(240))
class User(db.Model, UserMixin): id = db.Column(db.Integer(), primary_key=True) email = db.Column(db.String(100), unique=True) password = db.Column(db.String(255)) active = db.Column(db.Boolean()) roles = db.relationship('Role', secondary=roles_users, backref=db.backref('user', lazy='dynamic'))
db.session.add(self) db.session.commit() return self def update(self): db.session.commit() return self def delete(self): db.session.delete(self) return db.session.commit() roles_users = db.Table( 'roles_users', # 用户权限中间表 db.Column('user_id', db.Integer(), db.ForeignKey('user.id')), db.Column('role_id', db.Integer(), db.ForeignKey('role.id'))) class Role(db.Model, RoleMixin): __tablename__ = "role" __table_args__ = {"useexisting": True} id = db.Column(db.Integer(), primary_key=True, default=generate_uuid) name = db.Column(db.String(80), unique=True) description = db.Column(db.String(255)) def __init__(self, name, description): self.name = name self.description = description
from app import db from flask_bcrypt import Bcrypt from flask import current_app import jwt from datetime import datetime, timedelta from sqlalchemy.orm import relationship, backref menu_meals = db.Table( 'menu_meals', db.Column('menu_id', db.Integer(), db.ForeignKey('menu.id')), db.Column('meal_id', db.Integer(), db.ForeignKey('meals.id'))) class User(db.Model): """This class defines the users table """ __tablename__ = 'users' id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(256), nullable=False) email = db.Column(db.String(256), nullable=False, unique=True) password = db.Column(db.String(256), nullable=False) caterer = db.Column(db.Boolean, default=False) orders = db.relationship( 'Order', order_by='Order.id', cascade="all, delete-orphan") def __init__(self, username, email, password): """Initialize the user with an email and a password.""" self.username = username self.email = email self.password = Bcrypt().generate_password_hash(password).decode()
class EstadoMateria(db.Model): __tablename__ = 'estado_materia' id = db.Column(db.Integer(), primary_key=True) estado = db.Column(db.String(70), nullable=False, server_default='')
class Project(db.Model): """ Definition of the Project model for the database. A Project is a collection of strains on which can then be applied pipelines. """ __tablename__ = "projects" __bind_key__ = 'innuendo_database' id = db.Column(db.Integer(), primary_key=True) name = db.Column(db.String(255), unique=True) is_removed = db.Column(db.String(255)) description = db.Column(db.Text()) timestamp = db.Column(db.DateTime) user_id = db.Column(db.Integer, db.ForeignKey('users.id')) pipelines = db.relationship('Pipeline', backref='project', lazy='dynamic') strains = db.relationship('Strain', secondary=projects_strains, backref=db.backref('project', lazy='dynamic'), lazy='dynamic') species_id = db.Column(db.Integer, db.ForeignKey('species.id')) def add_Strain(self, strain): """Add strain to the project This method allows adding a specific strain to a project by creating a relationship between a strain and the project. Parameters ---------- strain: str Strain identifier to perform queries on Returns ------- bool: Returns False if not successfully added. """ if not self.is_strain_added(strain): self.strains.append(strain) return self else: return False def remove_Strain(self, strain): """Remove strain from project This method allows removing a strain from a project by removing the relationships between the strain and the project. Parameters ---------- strain: str Strain identifier to remove from the project Returns ------- bool: Returns False if not successfully added. """ if self.is_strain_added(strain): self.strains.remove(strain) return self else: return False def is_strain_added(self, strain): return self.strains.filter( projects_strains.c.strains_id == strain.id).count() > 0 def project_strains(self): return Strain.query.join( projects_strains,(projects_strains.c.strains_id == Strain.id))\ .filter(projects_strains.c.project_id == self.id).all()
# encoding: utf-8 """ @author: lileilei @file: models.py @time: 2017/7/13 16:43 """ '''数据库''' from app import db import datetime from werkzeug.security import check_password_hash, generate_password_hash registrations = db.Table( 'registrations', db.Column('task_id', db.Integer(), db.ForeignKey('tasks.id')), db.Column('interfacetests_id', db.Integer(), db.ForeignKey('interfacetests.id'))) quanxianuser = db.Table( 'quanxianusers', db.Column('user_id', db.Integer(), db.ForeignKey('users.id')), db.Column('quanxians_id', db.Integer(), db.ForeignKey('quanxians.id'))) class Permisson: ADD = 0x01 EDIT = 0x02 DELETE = 0x04 ONEADMIN = 0x08 ADMIN = 0xff class Role(db.Model):
class Bookmark(db.Model): __tablename__ = 'bookmarks' id = db.Column(db.Integer, primary_key=True) url = db.Column(db.String()) title = db.Column(db.String()) note = db.Column(db.String()) visitCount = db.Column(db.Integer(), default=0) lastVisitStamp = db.Column(db.DateTime()) tags = db.relationship('Tag', order_by=Tag.name, secondary=bookmarksTags) # ------------------------------------ @classmethod def Get(cls, id): return Bookmark.query.get(id) @classmethod def Delete(cls, bm): db.session.delete(bm) db.session.commit() @classmethod def Select(cls, **kwargs): # What are we querying? if 'tag' in kwargs: # Use ilike() for case-insensitive search criterion = Bookmark.tags.any(Tag.name.ilike(kwargs['tag'])) result = Bookmark.query.filter(criterion) elif 'popular' in kwargs: result = db.session.query(Bookmark)\ .order_by(Bookmark.visitCount.desc(), Bookmark.title) else: result = db.session.query(Bookmark) # Limit? if 'limit' in kwargs: result = result.limit(kwargs['limit']) if result: bmList = result.all() else: bmList = [] return bmList @classmethod def FindAllTags(cls): """ Return a list of used tags """ tagList = db.session.query(Tag).outerjoin(bookmarksTags)\ .filter(bookmarksTags.c.bookmarkId != None)\ .order_by(Tag.name.desc())\ .all() return tagList """ # The following may be useful one day @classmethod def GetAllCounts(cls): " Return a dict of all tags and their use counts "" result = db.session.query(Tag, func.count(bookmarksTags.c.bookmarkId))\ .join(bookmarksTags)\ .group_by(Tag)\ .order_by(Tag.name) # Convert Tag objs to names nameList = [ (rec[0].name, rec[1]) for rec in result ] # Convert to dict with name => count nameDict = dict(nameList) return nameDict """ # ----------------------------------------------------- def __init__(self, url, title, **kwargs): super(Bookmark, self).__init__(**kwargs); self.url = url self.title = title def __repr__(self): return '<Bookmark ({}) ({})>'.format(self.id, self.title) def bumpVisitCount(self): self.visitCount += 1 self.lastVisitStamp = datetime.now() db.session.commit() return self.visitCount """
class Host(db.Model, CRUD): __tablename__ = 'hosts' id = db.Column(db.Integer(), primary_key=True) hostname = db.Column(db.String(64), nullable=False, unique=True) hwaddress = db.Column(db.String(64), nullable=False, unique=True) target = db.Column(db.String(64), nullable=True)
# encoding: utf-8 """ @author: lileilei @file: models.py @time: 2017/7/13 16:43 """ '''数据库''' from app import db import datetime from werkzeug.security import check_password_hash, generate_password_hash registrations = db.Table('registrations', db.Column('task_id', db.Integer(), db.ForeignKey('tasks.id')), db.Column('interfacetests_id', db.Integer() , db.ForeignKey('interfacetests.id'))) quanxianuser = db.Table('quanxianusers', db.Column('user_id', db.Integer(), db.ForeignKey('users.id')), db.Column('quanxians_id', db.Integer(), db.ForeignKey('quanxians.id'))) rely_case = db.Table('yilai', db.Column('case_id', db.Integer(), db.ForeignKey('interfacetests.id')), db.Column('cases_id', db.Integer(), db.ForeignKey('interfacetests.id')), db.Column('attred', db.String())) class Permisson: ADD = 0x01 EDIT = 0x02 DELETE = 0x04
''' User model ''' import json from flask_security import UserMixin from sqlalchemy import Boolean, Column, DateTime, Integer, String, Text from werkzeug.security import check_password_hash, generate_password_hash from app import db from app.users.models.role import Role roles_users = db.Table( 'roles_users', db.Column('user_id', db.Integer(), db.ForeignKey('users.id')), db.Column('role_id', db.Integer(), db.ForeignKey('roles.id'))) class User(db.Model, UserMixin): ''' Represents site's user ''' __tablename__ = 'users' # Identification id = Column(Integer, primary_key=True) username = Column(String(32), unique=True, nullable=False) email = Column(String(80)) password_hash = Column(String(200)) enabled = Column(Boolean, nullable=False) atomy_id = Column(String(10)) phone = Column(String(32))
class OpeningHours(db.Model): opening_id = db.Column(db.Integer(), primary_key=True) place_id = db.Column(db.Integer(), db.ForeignKey('places.id')) week_day = db.Column(db.Integer()) opens_at = db.Column(db.DateTime()) closes_at = db.Column(db.DateTime())
class Reading(db.Model): sensor_id = db.Column(db.String(64), db.ForeignKey('sensor.sensor_id'), primary_key=True) time = db.Column(db.DateTime, primary_key=True) calibration = db.Column(db.Boolean()) height = db.Column(db.Float()) lat = db.Column(db.Float()) lon = db.Column(db.Float()) lat_lon_sd = db.Column(db.Float()) uncal_pressure = db.Column(db.Float()) uncal_pressure_sd = db.Column(db.Float()) uncal_temperature = db.Column(db.Float()) uncal_temperature_sd = db.Column(db.Float()) sample_count = db.Column(db.Integer()) __table_args__ = (db.UniqueConstraint('sensor_id', 'time', name='sensor_time_uc'), ) def __repr__(self): return '<Reading {}>'.format(self.sensor_id, self.time) def save(self): db.session.add(self) db.session.commit() def jsonify(self): return { 'sensor_id': self.sensor_id, 'calibration': self.calibration, 'time': self.time, 'height': self.height, 'lat': self.lat, 'lon': self.lon, 'lat_lon_sd': self.lat_lon_sd, 'uncal_pressure': self.uncal_pressure, 'uncal_pressure_sd': self.uncal_pressure_sd, 'uncal_temprature': self.uncal_temperature, 'uncal_temprature_sd': self.uncal_temperature_sd, 'sample_count': self.sample_count } def csvify(self): return { self.sensor_id, self.calibration, self.time, self.duration, self.lat, self.lon, self.lat_lon_sd, self.uncal_pressure, self.uncal_pressure_sd, self.uncal_temperature, self.uncal_temperature_sd, self.sample_count } @staticmethod def saveJson(jsonItem): s_id = jsonItem.get('sensor_id') time = jsonItem.get('time') reading = Reading( sensor_id=s_id, calibration=jsonItem.get('calibration'), time=datetime.datetime.fromtimestamp(time), height=jsonItem.get('height'), lat=jsonItem.get('lat'), lon=jsonItem.get('lon'), lat_lon_sd=jsonItem.get('lat_lon_sd'), uncal_pressure=jsonItem.get('uncal_pressure'), uncal_pressure_sd=jsonItem.get('uncal_pressure_sd'), uncal_temperature=jsonItem.get('uncal_temperature'), uncal_temperature_sd=jsonItem.get('uncal_temperature_sd'), sample_count=jsonItem.get('sample_count')) reading.save() return reading @staticmethod def csv_headers(self): return { 'sensor_id', 'calibration', 'time', 'duration', 'lat', 'lon', 'lat_lon_sd', 'uncal_pressure', 'uncal_pressure_sd', 'uncal_temprature', 'uncal_temprature_sd', 'sample_count' } @staticmethod def get_all(): return Reading.query.all() @staticmethod def get_sensor(sensorId, count): return Reading.query.filter_by(sensor_id=sensorId).order_by( Reading.time.desc()).limit(count).all() @staticmethod def get_range(start, end): return Reading.query.filter( Reading.time.between(datetime.datetime.fromtimestamp(start), datetime.datetime.fromtimestamp(end))) @staticmethod def get_sensor_range(sensorId, start, end): return Reading.query.filter_by(sensor_id=sensorId).filter( Reading.time.between(datetime.datetime.fromtimestamp(start), datetime.datetime.fromtimestamp(end)))
class Status_pay(db.Model): __tablename__ = 'status_pay' id = db.Column(db.Integer(), primary_key=True) name = db.Column('name', db.String(20), nullable=False)
class BadgeTemplate(TimestampMixin, Model): __tablename__ = 'badge_template' id = db.Column(db.BigInteger(), primary_key=True) name = db.Column(db.UnicodeText(), nullable=False) description = db.Column(db.UnicodeText()) extends_id = db.Column( db.BigInteger(), db.ForeignKey('badge_template.id', onupdate='CASCADE', ondelete='RESTRICT')) extends = db.relationship('BadgeTemplate', remote_side=[id], backref='extended_by') min_age = db.Column(db.Integer()) max_age = db.Column(db.Integer()) no_match = db.Column(db.Boolean(), nullable=False, default=False, server_default='0') image = db.Column(db.UnicodeText()) badge_name_top = db.Column(db.Float()) badge_name_left = db.Column(db.Float()) badge_name_width = db.Column(db.Float()) badge_name_height = db.Column(db.Float()) badge_number_top = db.Column(db.Float()) badge_number_left = db.Column(db.Float()) badge_number_width = db.Column(db.Float()) badge_number_height = db.Column(db.Float()) level_top = db.Column(db.Float()) level_left = db.Column(db.Float()) level_width = db.Column(db.Float()) level_height = db.Column(db.Float()) timestamp_format = db.Column(db.UnicodeText()) timestamp_append_to_level = db.Column(db.Boolean(), nullable=False, default=False) timestamp_top = db.Column(db.Float()) timestamp_left = db.Column(db.Float()) timestamp_width = db.Column(db.Float()) timestamp_height = db.Column(db.Float()) arbitrary_text = db.Column(db.UnicodeText()) arbitrary_text_override_level = db.Column(db.Boolean(), nullable=False, default=False) arbitrary_text_top = db.Column(db.Float()) arbitrary_text_left = db.Column(db.Float()) arbitrary_text_width = db.Column(db.Float()) arbitrary_text_height = db.Column(db.Float()) css = db.Column(db.UnicodeText()) flags = db.relationship('Flag', secondary=BadgeTemplateToFlag.__table__, backref='badge_templates') levels = db.relationship('Level', secondary=BadgeTemplateToLevel.__table__, backref='badge_templates') def __str__(self): return self.name @property def image_url(self): if self.image: return url_for('index.upload', filename=self.image) @property def is_leaf(self): if getattr(self, '_is_leaf', None) is None: self._is_leaf = BadgeTemplate.query.filter( BadgeTemplate.extends == self).count() == 0 return self._is_leaf @property def inheritance_chain(self): b = self while b: yield b b = b.extends @property def cascaded_props(self): out = { 'id': self.id, 'name': self.name, 'description': self.description, 'extends_id': self.extends_id, 'extends': self.extends, 'min_age': self.min_age, 'max_age': self.max_age, 'no_match': self.no_match, # Cascaded via CSS 'badge_name_top': self.badge_name_top, 'badge_name_left': self.badge_name_left, 'badge_name_width': self.badge_name_width, 'badge_name_height': self.badge_name_height, 'badge_number_top': self.badge_number_top, 'badge_number_left': self.badge_number_left, 'badge_number_width': self.badge_number_width, 'badge_number_height': self.badge_number_height, 'level_top': self.level_top, 'level_left': self.level_left, 'level_width': self.level_width, 'level_height': self.level_height, 'timestamp_top': self.timestamp_top, 'timestamp_left': self.timestamp_left, 'timestamp_width': self.timestamp_width, 'timestamp_height': self.timestamp_height, 'arbitrary_text_top': self.arbitrary_text_top, 'arbitrary_text_left': self.arbitrary_text_left, 'arbitrary_text_width': self.arbitrary_text_width, 'arbitrary_text_height': self.arbitrary_text_height, # TODO: these should allow for an empty value to be properly overridden 'timestamp_append_to_level': self.timestamp_append_to_level, 'arbitrary_text_override_level': self.arbitrary_text_override_level, # Real overrides 'timestamp_format': None, 'arbitrary_text': None, 'image': None, # Fake properties 'level': None, 'timestamp': None, 'level_append': None, 'classes': '', } for tpl in self.inheritance_chain: out['classes'] += ' tplid_' + str(tpl.id) if tpl.timestamp_append_to_level: out['timestamp_append_to_level'] = tpl.timestamp_append_to_level if tpl.arbitrary_text_override_level: out['arbitrary_text_override_level'] = tpl.arbitrary_text_override_level if tpl.timestamp_format and not out['timestamp_format']: out['timestamp_format'] = tpl.timestamp_format if tpl.arbitrary_text and not out['arbitrary_text']: out['arbitrary_text'] = tpl.arbitrary_text if tpl.image and not out['image']: out['image'] = tpl.image_url if out['timestamp_format']: out['timestamp'] = arrow.now().format(out['timestamp_format']) if out['arbitrary_text'] and out['arbitrary_text_override_level']: out['level'] = out['arbitrary_text'] out['arbitrary_text'] = None if out['timestamp'] and out['timestamp_append_to_level']: out['level_append'] = ' ' + out['timestamp'] out['timestamp'] = None return out def matches(self, badge): """\ Determine whether this template matches a particular badge. The return value is the specificity of the match. Matches are: - Badge age is within the range configured (1 match) - Badge and template share a flag (1 match per shared flag) - Badge level is assigned to the template (1 match) - Badge is a leaf node (no children), but only if there are other matches (2 matches) """ if self.no_match: return 0 matches = [] age_weight = 3 min_age = self.min_age max_age = self.max_age if min_age is None: min_age = 0 age_weight -= 1 if max_age is None: max_age = 999 age_weight -= 1 # Note that min/max ages are both inclusive if badge.age >= min_age and badge.age <= max_age: matches.append(('age', age_weight)) else: # If the badge age isn't in the range, no match is possible matches.append(('age', -1)) if not self.flags: # No flags is a single match matches.append(('flags', 0.5)) else: matching_flags = 0 for t_flag in self.flags: for b_flag in badge.flags: if t_flag == b_flag: matching_flags += 1 if not matching_flags: # If the template has flags but the badge doesn't match any, no match is possible matches.append(('flags', -1)) else: matches.append(('flags', matching_flags)) if not self.levels: # No levels is a single match matches.append(('level', 0.5)) else: if badge.level not in self.levels: # If the template has levels but the badge doesn't have one of them, no match is possible matches.append(('level', -1)) else: matches.append(('level', 1)) if matches: if self.is_leaf: matches.append(('leaf', 2)) # logger.debug("Match badge %s against template %s, matches: %s", badge.name, self.name, matches) if len([v for v in matches if v[1] < 0]): return 0 return sum((v[1] for v in matches))
class Event(db.Model): __tablename__ = "event" id = db.Column(db.Integer(), primary_key=True) name = db.Column(db.String(50), nullable=False) description = db.Column(db.String(255), nullable=False) start_date = db.Column(db.DateTime(), nullable=False) end_date = db.Column(db.DateTime(), nullable=False) key = db.Column(db.String(255), nullable=False, unique=True) organisation_id = db.Column(db.Integer(), db.ForeignKey('organisation.id'), nullable=False) email_from = db.Column(db.String(255), nullable=False) url = db.Column(db.String(255), nullable=False) application_open = db.Column(db.DateTime(), nullable=False) application_close = db.Column(db.DateTime(), nullable=False) review_open = db.Column(db.DateTime(), nullable=False) review_close = db.Column(db.DateTime(), nullable=False) selection_open = db.Column(db.DateTime(), nullable=False) selection_close = db.Column(db.DateTime(), nullable=False) offer_open = db.Column(db.DateTime(), nullable=False) offer_close = db.Column(db.DateTime(), nullable=False) registration_open = db.Column(db.DateTime(), nullable=False) registration_close = db.Column(db.DateTime(), nullable=False) event_type = db.Column(db.Enum(EventType), nullable=False) organisation = db.relationship('Organisation', foreign_keys=[organisation_id]) application_forms = db.relationship('ApplicationForm') email_templates = db.relationship('EmailTemplate') event_roles = db.relationship('EventRole') def __init__(self, name, description, start_date, end_date, key, organisation_id, email_from, url, application_open, application_close, review_open, review_close, selection_open, selection_close, offer_open, offer_close, registration_open, registration_close, event_type): self.name = name self.description = description self.start_date = start_date self.end_date = end_date self.key = key self.organisation_id = organisation_id self.email_from = email_from self.url = url self.application_open = application_open self.application_close = application_close self.review_open = review_open self.review_close = review_close self.selection_open = selection_open self.selection_close = selection_close self.offer_open = offer_open self.offer_close = offer_close self.registration_open = registration_open self.registration_close = registration_close self.event_roles = [] self.event_type = event_type def set_name(self, new_name): self.name = new_name def set_description(self, new_description): self.description = new_description def set_start_date(self, new_start_date): self.start_date = new_start_date def set_end_date(self, new_end_date): self.end_date = new_end_date def set_application_open(self, new_application_open): self.application_open = new_application_open def set_application_close(self, new_application_close): self.application_close = new_application_close def set_review_open(self, new_review_open): self.review_open = new_review_open def set_review_close(self, new_review_close): self.review_close = new_review_close def set_selection_open(self, new_selection_open): self.selection_open = new_selection_open def set_selection_close(self, new_selection_close): self.selection_close = new_selection_close def set_offer_open(self, new_offer_open): self.offer_open = new_offer_open def set_offer_close(self, new_offer_close): self.offer_close = new_offer_close def set_registration_open(self, new_registration_open): self.registration_open = new_registration_open def set_registration_close(self, new_registration_close): self.registration_close = new_registration_close def get_application_form(self): return self.application_forms[0] def add_event_role(self, role, user_id): event_role = EventRole(role, user_id, self.id) self.event_roles.append(event_role) def update(self, name, description, start_date, end_date, key, organisation_id, email_from, url, application_open, application_close, review_open, review_close, selection_open, selection_close, offer_open, offer_close, registration_open, registration_close): self.name = name self.description = description self.start_date = start_date self.end_date = end_date self.key = key self.organisation_id = organisation_id self.email_from = email_from self.url = url self.application_open = application_open self.application_close = application_close self.review_open = review_open self.review_close = review_close self.selection_open = selection_open self.selection_close = selection_close self.offer_open = offer_open self.offer_close = offer_close self.registration_open = registration_open self.registration_close = registration_close @property def is_application_open(self): now = datetime.now() return now >= self.application_open and now < self.application_close @property def is_review_open(self): now = datetime.now() return now >= self.review_open and now < self.review_close @property def is_selection_open(self): now = datetime.now() return now >= self.selection_open and now < self.selection_close @property def is_offer_open(self): now = datetime.now() return now >= self.offer_open and now < self.offer_close @property def is_registration_open(self): now = datetime.now() return now >= self.registration_open and now < self.registration_close
import ldap import ldap.modlist as modlist from app import db from flask_security import UserMixin, RoleMixin from sqlalchemy.dialects.postgresql import ARRAY, JSON from config import LDAP_PROVIDER_URL, baseDN, LDAP_ADMIN_PASS from passlib.hash import ldap_md5 '''Models: - Defines every model to be used on the postgres database''' # Secondary role table roles_users = db.Table('roles_users', db.Column('user_id', db.Integer(), db.ForeignKey('users.id')), db.Column('role_id', db.Integer(), db.ForeignKey('roles.id')), info={'bind_key': 'innuendo_database'}) pipelines_workflows = db.Table('pipelines_workflows', db.Column('pipeline_id', db.Integer(), db.ForeignKey('pipelines.id')), db.Column('workflow_id', db.Integer(), db.ForeignKey('workflows.id')), info={'bind_key': 'innuendo_database'}) projects_strains = db.Table('projects_strains', db.Column('project_id', db.Integer(), db.ForeignKey('projects.id')), db.Column('strains_id', db.Integer(), db.ForeignKey('strains.id')), info={'bind_key': 'innuendo_database'})
class Role(db.Model): id = db.Column(db.Integer(), primary_key=True) name = db.Column(db.String(50), unique=True)
class UserRoles(db.Model): __tablename__ = 'user_roles' id = db.Column(db.Integer(), primary_key=True) user_id = db.Column(db.Integer(), db.ForeignKey('users.id', ondelete='CASCADE')) role_id = db.Column(db.Integer(), db.ForeignKey('roles.id', ondelete='CASCADE'))
class UserRoles(db.Model): id = db.Column(db.Integer(), primary_key=True) user_id = db.Column(db.Integer(), db.ForeignKey('user.id', ondelete='CASCADE')) role_id = db.Column(db.Integer(), db.ForeignKey('role.id', ondelete='CASCADE'))
class Role(db.Model): __tablename__ = 'roles' id = db.Column(db.Integer(), primary_key=True) name = db.Column(db.String(50), unique=True)
class Permission(db.Model): __tablename__ = 'permissions' id = db.Column(db.Integer(), primary_key=True) user_id = db.Column(db.Integer()) # for @roles_accepted() permission=db.Column(db.String(200),nullable=False) value = db.Column(db.String(200),nullable=False)
class Role(db.Model): id = db.Column(db.Integer(), primary_key=True) name = db.Column(db.String(20), unique=True) def __repr__(self): return '<Role {}>'.format(self.name)
class VideoModel(db.Model, BaseModel): __bind_key__ = "a_social" __tablename__ = "video" video_id = db.Column(db.Integer, primary_key=True) share_id = db.Column(db.Integer, default=0) user_id = db.Column(db.Integer, default=0) cover_id = db.Column(db.Integer, default=0) type = db.Column(db.Integer, default=0) video_url = db.Column(db.String(100), default="") video_width = db.Column(db.Integer, default=0) video_height = db.Column(db.Integer, default=0) screenshot_a = db.Column(db.String(100), default="") screenshot_b = db.Column(db.String(100), default="") screenshot_c = db.Column(db.String(100), default="") file_name = db.Column(db.String(32), default="") playing_time = db.Column(db.Integer, default=0) file_size = db.Column(db.Integer(), default=0) file_ext = db.Column(db.String(10), default="") file_format = db.Column(db.String(20), default="") hash_key = db.Column(db.String(32), default="") bitrate_mode = db.Column(db.String(32), default="") mime_type = db.Column(db.String(20), default="") status = db.Column(db.Integer, default=0) created_time = db.Column(db.DateTime, default=datetime.datetime.now) updated_time = db.Column(db.DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now) def __init__(self, params=None): if params and isinstance(params, dict): for key, value in params.items(): if hasattr(self, key): setattr(self, key, value) db.session.add(self) db.session.commit() @staticmethod def query_share_video_info(share_id): if not share_id: return None video = VideoModel.query.filter_by(share_id=share_id, status=1).first() return video @staticmethod def query_private_video(user_id, login_user_id, last_id=0): """ 顺序查询某人的私密视频列表 :param user_id: 用户id :param login_user_id: 登录用户id :param last_id: 最后一个视频id """ if not user_id or not login_user_id: return [] query = VideoModel.query.filter_by(user_id=user_id, type=31, status=1) if last_id: query = query.filter(VideoModel.video_id < last_id) result = query.order_by(VideoModel.video_id.desc()).limit(15).all() if not result: return [] return VideoModel.format_private_video_list(result, user_id, login_user_id) @staticmethod def query_random_private_video(user_id, login_user_id, last_id, offset=0, limit=15): """ 随机查询某人的私密视频 :param user_id: 用户id :param login_user_id: 登录用户id :param last_id: 最后一条数据id :param offset: 初始偏移 :param limit: 分页数量 """ invalid_video_list = OrderVideoModel.query_invalid_order_video_list( user_id, login_user_id) invalid_video_id_list = array_column(invalid_video_list, "video_id") result_1 = VideoModel.query_random_video_list(user_id, last_id, offset, limit, invalid_video_id_list) if len(result_1) == limit: return VideoModel.format_private_video_list( result_1, user_id, login_user_id) if last_id > offset or last_id == 0: last_id = 0.5 limit = limit - len(result_1) else: return VideoModel.format_private_video_list( result_1, user_id, login_user_id) result_2 = VideoModel.query_random_video_list(user_id, last_id, offset, limit, invalid_video_id_list) result_1 += result_2 return VideoModel.format_private_video_list(result_1, user_id, login_user_id) @staticmethod def query_random_video_list(user_id, last_id, offset, limit, invalid_video_id_list=list()): query = VideoModel.query.filter_by(user_id=user_id, type=31, status=1) if offset > 0: if last_id == 0: query = query.filter(VideoModel.video_id > offset) elif last_id > offset: query = query.filter(VideoModel.video_id > last_id) else: query = query.filter(VideoModel.video_id > last_id, VideoModel.video_id <= offset) if invalid_video_id_list: query = query.filter( VideoModel.video_id.notin_(invalid_video_id_list)) result = query.order_by(VideoModel.video_id.asc()).limit(limit).all() if not result: result = [] return result @staticmethod def format_video_info(video_model): if not video_model or not isinstance(video_model, VideoModel): return dict() from app.models.social.image import ImageModel from heron import app cover_model = ImageModel.query_image_by_id(video_model.cover_id) result = video_model.format_model( attr_list=["video_width", "video_height"]) result["video_url"] = app.config["VIDEO_HOST"] + video_model.video_url result["video_img"] = ImageModel.generate_image_url(cover_model, 'f') return result @staticmethod def format_private_video_list(video_list, user_id, login_user_id): """ 格式化私密视频列表 :param video_list: 原始私密视频模型 :param user_id: 私密视频所属用户id :param login_user_id: 登录用户id :return: 格式化后的私密视频列表 """ image_model_list = ImageModel.query_image_by_image_id_list( array_column(video_list, "cover_id")) image_model_dict = array_column_key(image_model_list, "image_id") video_id_list = array_column(video_list, "video_id") video_meta_list = VideoMetaModel.query_video_meta_list(video_id_list) video_meta_dict = array_column_key(video_meta_list, "video_id") video_pay_dict = dict() if user_id != login_user_id: video_pay_list = OrderVideoModel.query_order_video_list( login_user_id, video_id_list) video_pay_dict = array_column_key(video_pay_list, "video_id") result = [] for video_model in video_list: image_model = image_model_dict.get(video_model.cover_id, None) video_meta_model = video_meta_dict.get(video_model.video_id, None) if user_id == login_user_id: video_pay = 1 else: order_video = video_pay_dict.get(video_model.video_id, None) video_pay = 1 if order_video else 0 item = VideoModel.format_private_video_model( video_model, image_model, video_meta_model, video_pay) if item: result.append(item) return result @staticmethod def format_private_video_model(video_model=None, image_model=None, video_meta_model=None, pay=0): if not video_model: return dict() if not image_model: image_model = ImageModel.query_image_by_id(video_model.cover_id) if not image_model: return dict() if not video_meta_model: video_meta_model = VideoMetaModel.query_video_meta( video_model.video_id) if not video_meta_model: return dict() result = dict() result["pay"] = pay result["price"] = video_meta_model.price / 100 video_dict = VideoModel.format_video_info(video_model) video_dict["video_id"] = video_model.video_id if not pay: video_dict["video_img"] = ImageModel.generate_image_url( image_model, 'x') video_dict["video_url"] = "" result["video"] = video_dict return result
class FormaAprobacionMateria(db.Model): __tablename__ = 'forma_aprobacion_materia' id = db.Column(db.Integer(), primary_key=True) forma = db.Column(db.String(35), nullable=False, server_default='')
class Event(Base): __tablename__ = "event" __table_args__ = {'extend_existing': True} id = db.Column(db.Integer(), primary_key=True) start_date = db.Column(db.DateTime(), nullable=False) end_date = db.Column(db.DateTime(), nullable=False) key = db.Column(db.String(255), nullable=False, unique=True) organisation_id = db.Column(db.Integer(), db.ForeignKey('organisation.id'), nullable=False) email_from = db.Column(db.String(255), nullable=False) url = db.Column(db.String(255), nullable=False) application_open = db.Column(db.DateTime(), nullable=False) application_close = db.Column(db.DateTime(), nullable=False) review_open = db.Column(db.DateTime(), nullable=False) review_close = db.Column(db.DateTime(), nullable=False) selection_open = db.Column(db.DateTime(), nullable=False) selection_close = db.Column(db.DateTime(), nullable=False) offer_open = db.Column(db.DateTime(), nullable=False) offer_close = db.Column(db.DateTime(), nullable=False) registration_open = db.Column(db.DateTime(), nullable=False) registration_close = db.Column(db.DateTime(), nullable=False) event_type = db.Column(db.Enum(EventType), nullable=False) travel_grant = db.Column(db.Boolean(), nullable=False) miniconf_url = db.Column(db.String(100), nullable=True) event_translations = db.relationship('EventTranslation', lazy='dynamic') def __init__( self, names, descriptions, start_date, end_date, key, organisation_id, email_from, url, application_open, application_close, review_open, review_close, selection_open, selection_close, offer_open, offer_close, registration_open, registration_close, event_type, travel_grant, miniconf_url=None ): self.start_date = start_date self.end_date = end_date self.key = key self.organisation_id = organisation_id self.email_from = email_from self.url = url self.application_open = application_open self.application_close = application_close self.review_open = review_open self.review_close = review_close self.selection_open = selection_open self.selection_close = selection_close self.offer_open = offer_open self.offer_close = offer_close self.registration_open = registration_open self.registration_close = registration_close self.event_roles = [] self.event_type = event_type self.travel_grant = travel_grant self.miniconf_url = miniconf_url self.add_event_translations(names, descriptions) def add_event_translations(self, names, descriptions): for language in names: name = names[language] description = descriptions[language] event_translation = EventTranslation(name, description, language) self.event_translations.append(event_translation)
class Role(db.Model, RoleMixin): id = db.Column(db.Integer(), primary_key=True) name = db.Column(db.String(100), unique=True) description = db.Column(db.String(255))
params = db.Column(db.String(500)) #参数 rebacktype = db.Column(db.String(32)) #类型, update_time = db.Column(db.DateTime(), default=datetime.datetime.now()) #更新时间 status = db.Column(db.Boolean(), default=False) #状态,是否开启 delete = db.Column(db.Boolean(), default=False) #是否删除 ischeck = db.Column(db.Boolean(), default=False) #是否校验参数 is_headers = db.Column(db.Boolean(), default=False) #是否对headers进行校验 def __repr__(self): return self.name registrations = db.Table( 'registrations', db.Column('task_id', db.Integer(), db.ForeignKey('tasks.id')), db.Column('interfacetests_id', db.Integer(), db.ForeignKey('interfacetests.id'))) #此表多对多,对应的是测试用例和任务 class Task(db.Model): #定时任务的 __tablename__ = 'tasks' id = db.Column(db.Integer, primary_key=True) makeuser = db.Column(db.Integer(), db.ForeignKey('users.id')) #创建者 taskname = db.Column(db.String(52)) #任务名称 taskstart = db.Column(db.String(252)) #任务执行时间 taskmakedate = db.Column(db.DateTime(), default=datetime.datetime.now()) #任务的创建时间 taskrepor_to = db.Column(db.String(252)) #收件人邮箱 taskrepor_cao = db.Column(db.String(252), nullable=True) #抄送人邮箱 task_make_email = db.Column(db.String(252)) #维护本计划的人的邮箱
class AreaModel(db.Model): __tablename__ = 'area' areaId = db.Column(db.Integer, primary_key=True, autoincrement=True) areaName = db.Column(db.String(800), nullable=False) areaType = db.Column(db.Enum(AreaTypeEnum), nullable=False) electionId = db.Column(db.Integer, db.ForeignKey( Election.Model.__table__.c.electionId), nullable=False) _registeredVotersCount = db.Column(db.Integer(), nullable=True) _registeredPostalVotersCount = db.Column(db.Integer(), nullable=True) _registeredQuarantineVotersCount = db.Column(db.Integer(), nullable=True) _registeredDisplacedVotersCount = db.Column(db.Integer(), nullable=True) election = relationship(Election.Model, foreign_keys=[electionId]) # this relationship is used for persistence children = relationship( "AreaAreaModel", lazy="joined", primaryjoin="AreaModel.areaId==AreaAreaModel.parentAreaId") parents = relationship( "AreaAreaModel", lazy="joined", primaryjoin="AreaModel.areaId==AreaAreaModel.childAreaId") def __init__(self, areaName, electionId): super(AreaModel, self).__init__(areaName=areaName, electionId=electionId) db.session.add(self) db.session.flush() def add_parent(self, parentId): parentArea = get_by_id(areaId=parentId) parentArea.add_child(self.areaId) return self def add_child(self, childId): existing_mapping = AreaAreaModel.query.filter( AreaAreaModel.parentAreaId == self.areaId, AreaAreaModel.childAreaId == childId).one_or_none() if existing_mapping is None: areaParent = AreaAreaModel(parentAreaId=self.areaId, childAreaId=childId) db.session.add(areaParent) db.session.flush() return self def get_associated_areas_query(self, areaType, electionId=None): return get_associated_areas_query(areas=[self], areaType=areaType, electionId=electionId) def get_associated_areas(self, areaType, electionId=None): return self.get_associated_areas_query(areaType, electionId).all() def get_submissions(self, submissionType): return [ submission for submission in self.submissions if submission.submissionType is submissionType ] @hybrid_property def areaMapList(self): extended_election = self.election.get_extended_election() return extended_election.get_area_map(area=self) def get_registered_voters_count(self, vote_type=None): polling_stations_subquery = get_associated_areas_query( areas=[self], areaType=AreaTypeEnum.PollingStation).subquery() _registeredVotersCount = db.Column(db.Integer(), nullable=True) _registeredPostalVotersCount = db.Column(db.Integer(), nullable=True) _registeredQuarantineVotersCount = db.Column(db.Integer(), nullable=True) _registeredDisplacedVotersCount = db.Column(db.Integer(), nullable=True) if vote_type == NonPostal: registered_voters_column = coalesce( polling_stations_subquery.c._registeredVotersCount, 0) elif vote_type == Postal: registered_voters_column = coalesce( polling_stations_subquery.c._registeredPostalVotersCount, 0) elif vote_type == Quarantine: registered_voters_column = coalesce( polling_stations_subquery.c._registeredQuarantineVotersCount, 0) elif vote_type == Quarantine: registered_voters_column = coalesce( polling_stations_subquery.c._registeredDisplacedVotersCount, 0) else: registered_voters_column = coalesce(polling_stations_subquery.c._registeredVotersCount, 0) \ + coalesce(polling_stations_subquery.c._registeredPostalVotersCount, 0) \ + coalesce(polling_stations_subquery.c._registeredQuarantineVotersCount, 0) \ + coalesce(polling_stations_subquery.c._registeredDisplacedVotersCount, 0) total_registered_voters_count = db.session.query( func.sum(registered_voters_column)).scalar() return float(total_registered_voters_count) @hybrid_property def registeredVotersCount(self): polling_stations_subquery = get_associated_areas_query( areas=[self], areaType=AreaTypeEnum.PollingStation).subquery() total_registered_voters_count = db.session.query( func.sum( polling_stations_subquery.c._registeredVotersCount)).scalar() return total_registered_voters_count @hybrid_property def registeredPostalVotersCount(self): polling_stations_subquery = get_associated_areas_query( areas=[self], areaType=AreaTypeEnum.PollingStation).subquery() total_registered_postal_voters_count = db.session.query( func.sum(polling_stations_subquery.c._registeredPostalVotersCount) ).scalar() return total_registered_postal_voters_count __mapper_args__ = {'polymorphic_on': areaType}
from app import db from datetime import datetime from sqlalchemy.dialects import postgresql from flask_security import UserMixin, RoleMixin from sqlalchemy.sql.functions import current_timestamp roles_users = db.Table( 'roles_users', db.Column('admin_user_id', db.Integer(), db.ForeignKey('admin_user.id')), db.Column('admin_role_id', db.Integer(), db.ForeignKey('admin_role.id')), ) class AdminUser(db.Model, UserMixin): __tablename__ = 'admin_user' id = db.Column(db.Integer(), primary_key=True) login = db.Column(db.String(20), unique=True, nullable=False) password = db.Column(db.String(255), nullable=False) username = db.Column(db.String(100)) create = db.Column(db.DateTime(), default=current_timestamp()) email = db.Column(db.String(120), unique=True, nullable=False) active = db.Column(db.Boolean, default=False) descr = db.Column(db.String(255)) roles = db.relationship('AdminRole', secondary=roles_users, backref=db.backref('adminusers', lazy='dynamic')) def __init__(self, *args, **kwargs): super(AdminUser, self).__init__(*args, **kwargs) def __repr__(self):