class Result(Base): """ defines our result table """ __tablename__ = 'tuning_statistics' name = Column(Text, primary_key=True) version = Column(Text, primary_key=True) method = Column(Text, primary_key=True) content = Column(mutable_json_type(nested=True, dbtype=JSONB)) curve = Column(mutable_json_type(nested=True, dbtype=JSONB)) profiles = Column(mutable_json_type(nested=True, dbtype=JSONB)) parameters = Column(mutable_json_type(nested=True, dbtype=JSONB))
class Run(Model, ModelMixin): __tablename__ = "runs" artifacts = relationship("Artifact") component = Column(Text, index=True) created = Column(DateTime, default=datetime.utcnow, index=True) # this is metadata but it is a reserved attr data = Column(mutable_json_type(dbtype=PortableJSON(), nested=True)) duration = Column(Float, index=True) env = Column(Text, index=True) project_id = Column(PortableUUID(), ForeignKey("projects.id"), index=True) results = relationship("Result") source = Column(Text, index=True) start_time = Column(DateTime, default=datetime.utcnow, index=True) summary = Column(mutable_json_type(dbtype=PortableJSON())) artifacts = relationship("Artifact", backref="run")
class Artifact(Model, FileMixin): __tablename__ = "artifacts" result_id = Column(PortableUUID(), ForeignKey("results.id"), index=True) run_id = Column(PortableUUID(), ForeignKey("runs.id"), index=True) filename = Column(Text, index=True) data = Column(mutable_json_type(dbtype=PortableJSON(), nested=True)) upload_date = Column(DateTime, default=datetime.utcnow, index=True)
class Import(Model, ModelMixin): __tablename__ = "imports" file = relationship("ImportFile") filename = Column(Text, index=True) format = Column(Text, index=True) data = Column(mutable_json_type(dbtype=PortableJSON(), nested=True)) status = Column(Text, index=True)
class DeletedPost(db.Model): __tablename__ = 'deleted_posts' id = db.Column(db.Integer, primary_key=True) json_column = db.Column(mutable_json_type(dbtype=JSON, nested=True), nullable=False) __mapper_args__ = { "order_by": id.desc() }
class ReportFile(Model, FileMixin): __tablename__ = "report_files" report_id = Column(PortableUUID(), ForeignKey("reports.id"), nullable=False, index=True) filename = Column(Text, index=True) data = Column(mutable_json_type(dbtype=PortableJSON(), nested=True))
class WidgetConfig(Model, ModelMixin): __tablename__ = "widget_configs" navigable = Column(Boolean, index=True) params = Column(mutable_json_type(dbtype=PortableJSON())) project_id = Column(PortableUUID(), ForeignKey("projects.id"), index=True) dashboard_id = Column(PortableUUID(), ForeignKey("dashboards.id"), index=True) title = Column(Text, index=True) type = Column(Text, index=True) weight = Column(Integer, index=True) widget = Column(Text, index=True)
class Report(Model, ModelMixin): __tablename__ = "reports" created = Column(DateTime, default=datetime.utcnow, index=True) download_url = Column(Text, index=True) filename = Column(Text, index=True) mimetype = Column(Text, index=True) name = Column(Text, index=True) params = Column(mutable_json_type(dbtype=PortableJSON())) project_id = Column(PortableUUID(), ForeignKey("projects.id"), index=True) file = relationship("ReportFile") status = Column(Text, index=True) url = Column(Text, index=True) view_url = Column(Text, index=True)
class Activity(db.Model): __tablename__ = "activities" activity_id = db.Column(db.Integer, autoincrement=True, primary_key=True) activity_name = db.Column(db.String()) min_cost = db.Column(db.Integer) max_cost = db.Column(db.Integer) min_age = db.Column(db.Integer) max_age = db.Column(db.Integer) location = db.Column(db.String()) effort_rating = db.Column(db.String()) keywords = db.Column(db.String()) activity_description = db.Column(mutable_json_type(dbtype=db.JSON, nested=True)) timestamp = db.Column( db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow ) interests = db.relationship("Interest", secondary="activities_interests", back_populates="activities") users = db.relationship("User", secondary="users_activities", back_populates="activities") materials = db.relationship("Material", secondary="activities_materials", back_populates="activities") time_periods = db.relationship("TimePeriod", secondary="activities_time_periods", back_populates="activities") comments = db.relationship("Comment", secondary="activities_comments", back_populates="activities") activities_interests = db.relationship("ActivityInterest", back_populates="activities", cascade="all, delete") activities_time_periods= db.relationship("ActivityTimePeriod", back_populates="activities", cascade="all, delete") activities_comments= db.relationship("ActivityComment", back_populates="activities", cascade="all, delete") users_activities= db.relationship("UserActivity", back_populates="activities", cascade="all, delete") activities_materials= db.relationship("ActivityMaterial", back_populates="activities", cascade="all, delete") def __repr__(self): return f'<Activity activity_id={self.activity_id} name={self.activity_name}>'
class Booking(db.Model, BaseMixin): """Store the information about the booking. Note that FH does not provide for the time being the created_by value so we have to make it up out of the value we find in affiliate_company in the response. Otherwise we assume it as booked by a generic staff user. """ __table_name__ = "booking" id = db.Column(db.BigInteger, primary_key=True) voucher_number = db.Column(db.String(64)) display_id = db.Column(db.String(64), nullable=False) note_safe_html = db.Column(db.Text) agent = db.Column(db.String(64)) confirmation_url = db.Column(db.String(255)) customer_count = db.Column(db.SmallInteger, nullable=False) uuid = db.Column(db.String(40), nullable=False, unique=True) dashboard_url = db.Column(db.String(264)) note = db.Column(db.Text) pickup = db.Column(db.String(64)) status = db.Column(db.String(64)) created_by = db.Column(db.String(64), nullable=False, default="staff") # price fields receipt_subtotal = db.Column(db.Integer) receipt_taxes = db.Column(db.Integer) receipt_total = db.Column(db.Integer) amount_paid = db.Column(db.Integer) invoice_price = db.Column(db.Integer) # Price displays receipt_subtotal_display = db.Column(db.String(64)) receipt_taxes_display = db.Column(db.String(64)) receipt_total_display = db.Column(db.String(64)) amount_paid_display = db.Column(db.String(64)) invoice_price_display = db.Column(db.String(64)) desk = db.Column(db.String(64)) is_eligible_for_cancellation = db.Column(db.Boolean) is_subscribed_for_sms_updates = db.Column(db.Boolean) arrival = db.Column(db.String(64)) rebooked_to = db.Column(db.String(64)) rebooked_from = db.Column(db.String(64)) external_id = db.Column(db.String(64)) # implement Postgres' JSONB field MutableJson = mutable_json_type(dbtype=JSONB, nested=False) order = db.Column(MutableJson) # Foreign key fields availability_id = db.Column(db.BigInteger, db.ForeignKey("availability.id"), nullable=False) company_id = db.Column(db.BigInteger, db.ForeignKey("company.id"), nullable=False) affiliate_company_id = db.Column(db.BigInteger, db.ForeignKey("company.id")) # Reverse relationships availability = db.relationship("Availability", back_populates="bookings") customers = db.relationship("Customer", back_populates="booking")
class User(db.Model, DomainObject, UserMixin): '''A registered user of the PYBOSSA system''' __tablename__ = 'user' id = Column(Integer, primary_key=True) #: UTC timestamp of the user when it's created. created = Column(Text, default=make_timestamp) email_addr = Column(Unicode(length=254), unique=True, nullable=False) #: Name of the user (this is used as the nickname). name = Column(Unicode(length=254), unique=True, nullable=False) #: Fullname of the user. fullname = Column(Unicode(length=500), nullable=False) #: Language used by the user in the PYBOSSA server. locale = Column(Unicode(length=254), default='en', nullable=False) api_key = Column(String(length=36), default=make_uuid, unique=True) passwd_hash = Column(Unicode(length=254), unique=True) ldap = Column(Unicode, unique=True) admin = Column(Boolean, default=False) pro = Column(Boolean, default=False) privacy_mode = Column(Boolean, default=True, nullable=False) restrict = Column(Boolean, default=False, nullable=False) category = Column(Integer) flags = Column(Integer) ckan_api = Column(String, unique=True) newsletter_prompted = Column(Boolean, default=False) valid_email = Column(Boolean, default=False) confirmation_email_sent = Column(Boolean, default=False) subscribed = Column(Boolean, default=False) consent = Column(Boolean, default=False) info = Column(mutable_json_type(dbtype=JSONB, nested=True), default=dict()) user_pref = Column(JSONB) notified_at = Column(Date, default=None) # Relationships task_runs = relationship(TaskRun, backref='user') projects = relationship(Project, backref='owner') blogposts = relationship(Blogpost, backref='owner') def get_id(self): '''id for login system. equates to name''' return self.name def set_password(self, password): self.passwd_hash = signer.generate_password_hash(password) def check_password(self, password): # OAuth users do not have a password if self.passwd_hash: return signer.check_password_hash(self.passwd_hash, password) return False @classmethod def public_attributes(self): """Return a list of public attributes.""" return [ 'created', 'name', 'fullname', 'info', 'n_answers', 'registered_ago', 'rank', 'score', 'locale' ] @classmethod def public_info_keys(self): """Return a list of public info keys.""" default = ['avatar', 'container', 'extra', 'avatar_url'] extra = current_app.config.get('USER_INFO_PUBLIC_FIELDS') if extra: return list(set(default).union(set(extra))) else: return default
return "%.32x" % value.int def process_result_value(self, value, dialect): if value is None: return value else: if not isinstance(value, uuid.UUID): value = uuid.UUID(value) return value class JSON(TypeDecorator): """ Platform-independent JSON type Uses PostgreSQL's JSONB type, otherwise falls back to standard JSON """ impl = SQLJSON def load_dialect_impl(self, dialect): if dialect.name == "postgresql": return dialect.type_descriptor(JSONB(astext_type=Text())) else: return dialect.type_descriptor(SQLJSON()) # Mutable version of JSON field # https://docs.sqlalchemy.org/en/13/core/type_basics.html?highlight=json#sqlalchemy.types.JSON MutableJSON = mutable_json_type(dbtype=JSON, nested=True)
from flask_sqlalchemy import SQLAlchemy from flask_migrate import Migrate from sqlalchemy.dialects.postgresql import UUID, JSON, JSONB from sqlalchemy.exc import SQLAlchemyError from sqlalchemy.orm.attributes import InstrumentedAttribute, flag_modified from sqlalchemy.orm.exc import NoResultFound, MultipleResultsFound from sqlalchemy_json import mutable_json_type from .app import app def add(entity): db.session.add(entity) return entity db = SQLAlchemy(app, session_options={'autoflush': False}) migrate = Migrate(app, db) db.add = add db.now = datetime.now db.uuid4 = lambda: str(uuid4()) db.UUID = UUID db.JSON = mutable_json_type(dbtype=JSON, nested=True) db.JSONB = mutable_json_type(dbtype=JSONB, nested=True) db.SQLAlchemyError = SQLAlchemyError db.InstrumentedAttribute = InstrumentedAttribute db.flag_modified = flag_modified db.NoResultFound = NoResultFound db.MultipleResultsFound = MultipleResultsFound
class Group(Model, ModelMixin): __tablename__ = "groups" name = Column(Text, index=True) projects = relationship("Project") data = Column(mutable_json_type(dbtype=PortableJSON(), nested=True))