# 约束
from sqlalchemy import PrimaryKeyConstraint, UniqueConstraint, CheckConstraint, ForeignKeyConstraint
# 表和列
from sqlalchemy import Table, Column
metadata = MetaData()
# 表COOKIES
cookies = Table("COOKIES", metadata,
Column("cookie_id", Integer(), primary_key = True),
Column("cookie_name", String(50)), # index = True
Column("cookie_recipe_url", String(255)),
Column("cookie_sku", String(55)),
Column("quantity", Integer()),
Column("unit_cost", Numeric(12, 2)),
# 检查约束
CheckConstraint("unit_cost >= 0.0", name = "unit_cost_positive"),
# 索引
Index("ix_cookies_cookie_name", "cookie_name"),
Index("ix_cookie_sku_name", "cookie_sku", "cookie_name")
)
# Index("ix_cookies_cookie_name", cookies.c.cookie_name) # 使用Table中字段
# Index("ix_cookie_sku_name", cookies.c.cookie_sku, cookies.c.cookie_name)
# 表USERS
users = Table("USERS", metadata,
Column("user_id", Integer()), # primary_key = True
Column("username", String(15), nullable = False), # unique = True
Column("email_address", String(255), nullable = False),
Column("phone", String(20), nullable = False),
Column("password", String(25), nullable = False),
Column("create_on", DateTime(), default = datetime.now),
class Conference(db.Model): # type: ignore
conference_id = db.Column(db.Integer, primary_key=True)
full_name = db.Column(db.String(256), nullable=False)
informal_name = db.Column(db.String(256), nullable=False)
website = db.Column(db.String(512), nullable=False)
twitter_username = db.Column(db.String(15), nullable=True)
footer_text = db.Column(db.Text,
default="",
server_default="",
nullable=False)
talk_lengths = db.Column(JSONMutableList.as_mutable(JSON), nullable=False)
recording_release_url = db.Column(db.String(1024), nullable=False)
cfp_email = db.Column(db.String(256), nullable=False)
conduct_email = db.Column(db.String(256), nullable=False)
# Proposals window -- populate with naive datetimes in UTC
proposals_begin = db.Column(db.DateTime)
proposals_end = db.Column(db.DateTime)
# Voting window -- populate with naive datetimes in UTC
voting_begin = db.Column(db.DateTime)
voting_end = db.Column(db.DateTime)
# Proposal review window -- populate with naive datetimes in UTC
review_begin = db.Column(db.DateTime)
review_end = db.Column(db.DateTime)
created = db.Column(db.TIMESTAMP, nullable=False, default=datetime.utcnow)
updated = db.Column(db.TIMESTAMP,
nullable=False,
default=datetime.utcnow,
onupdate=datetime.utcnow)
__table_args__ = (
CheckConstraint(
"(proposals_begin IS NOT NULL and proposals_end IS NOT NULL) "
"OR (proposals_begin IS NULL AND proposals_end IS NULL)",
name="ck_proposals_window",
),
CheckConstraint(
"(voting_begin IS NOT NULL and voting_end IS NOT NULL) "
"OR (voting_begin IS NULL AND voting_end IS NULL)",
name="ck_voting_window",
),
)
@property
def proposal_window(self) -> Optional[TimeWindow]:
if self.proposals_begin and self.proposals_end:
return TimeWindow(self.proposals_begin, self.proposals_end)
return None
@property
def voting_window(self) -> Optional[TimeWindow]:
if self.voting_begin and self.voting_end:
return TimeWindow(self.voting_begin, self.voting_end)
return None
@property
def review_window(self) -> Optional[TimeWindow]:
if self.review_begin and self.review_end:
return TimeWindow(self.review_begin, self.review_end)
return None
@property
def creating_proposals_allowed(self) -> bool:
return self.proposal_window is not None and self.proposal_window.includes_now(
)
@property
def editing_proposals_allowed(self) -> bool:
# TODO: allow edits to accepted talks after proposal and voting windows
if self.voting_window and self.voting_allowed:
return False
elif self.creating_proposals_allowed or self.voting_window_after_now:
return True
return False
@property
def voting_allowed(self) -> bool:
return self.voting_window is not None and self.voting_window.includes_now(
)
@property
def voting_window_after_now(self) -> bool:
return self.voting_window is not None and self.voting_window.after_now(
)
@property
def review_allowed(self) -> bool:
return self.review_window is not None and self.review_window.includes_now(
)
@property
def review_window_after_now(self) -> bool:
return self.review_window is not None and self.review_window.after_now(
)
class Movie(Base):
__tablename__ = 'movie'
__table_args__ = (CheckConstraint('rating between 0 and 10'), )
id = Column(Integer, primary_key=True)
name = Column(String, unique=True)
rating = Column(Float)
def define_table_args(cls):
return super(Reservation, cls).define_table_args() + (CheckConstraint(
'quantity > 0', name='positive_qty'), )
class Group(DatabaseModel):
"""Model for a group on the site.
Trigger behavior:
Incoming:
- num_subscriptions will be incremented and decremented by insertions and
deletions in group_subscriptions.
"""
schema_class = GroupSchema
__tablename__ = "groups"
group_id: int = Column(BigInteger, primary_key=True)
path: Ltree = Column(LtreeType, nullable=False, index=True, unique=True)
created_time: datetime = Column(
TIMESTAMP(timezone=True),
nullable=False,
index=True,
server_default=text("NOW()"),
)
short_description: Optional[str] = Column(
Text,
CheckConstraint(
f"LENGTH(short_description) <= {SHORT_DESCRIPTION_MAX_LENGTH}",
name="short_description_length",
),
)
_sidebar_markdown: str = deferred(Column("sidebar_markdown", Text))
sidebar_rendered_html: str = deferred(Column(Text))
num_subscriptions: int = Column(Integer,
nullable=False,
server_default="0")
requires_permission_to_post_topics: bool = Column(Boolean,
nullable=False,
server_default="false")
is_user_treated_as_topic_source: bool = Column(Boolean,
nullable=False,
server_default="false")
common_topic_tags: list[str] = Column(TagList,
nullable=False,
server_default="{}")
important_topic_tags: list[str] = Column(TagList,
nullable=False,
server_default="{}")
# Create a GiST index on path as well as the btree one that will be created by the
# index=True/unique=True keyword args to Column above. The GiST index supports
# additional operators for ltree queries: @>, <@, @, ~, ?
__table_args__ = (Index("ix_groups_path_gist",
path,
postgresql_using="gist"), )
@hybrid_property
def sidebar_markdown(self) -> str:
"""Return the sidebar's markdown."""
return self._sidebar_markdown
@sidebar_markdown.setter # type: ignore
def sidebar_markdown(self, new_markdown: str) -> None:
"""Set the sidebar's markdown and render its HTML."""
if new_markdown == self.sidebar_markdown:
return
self._sidebar_markdown = new_markdown
if self._sidebar_markdown is not None:
self.sidebar_rendered_html = convert_markdown_to_safe_html(
new_markdown)
else:
self.sidebar_rendered_html = None
@property
def autocomplete_topic_tags(self) -> list[str]:
"""Return the topic tags that should be offered as autocomplete options."""
global_options = ["nsfw", "spoiler", "coronaviruses.covid19"]
return self.common_topic_tags + [
tag for tag in global_options if tag not in self.common_topic_tags
]
def __repr__(self) -> str:
"""Display the group's path and ID as its repr format."""
return f"<Group {self.path} ({self.group_id})>"
def __str__(self) -> str:
"""Use the group path for the string representation."""
return str(self.path)
def __lt__(self, other: "Group") -> bool:
"""Order groups by their string representation."""
return str(self) < str(other)
def __init__(self, path: str, short_desc: Optional[str] = None):
"""Create a new group."""
self.path = path
self.short_description = short_desc
def __acl__(self) -> AclType:
"""Pyramid security ACL."""
acl = []
# view:
# - all groups can be viewed by everyone
acl.append((Allow, Everyone, "view"))
# subscribe:
# - all groups can be subscribed to by logged-in users
acl.append((Allow, Authenticated, "subscribe"))
# topic.post:
# - only users with specifically-granted permission can post topics in groups
# that require permission to post
# - otherwise, all logged-in users can post
if self.requires_permission_to_post_topics:
acl.append((Allow, f"{self.group_id}:topic.post", "topic.post"))
acl.append((Deny, Everyone, "topic.post"))
acl.append((Allow, Authenticated, "topic.post"))
# wiki_page_create:
# - requires being granted the "wiki.edit" permission
acl.extend(
aces_for_permission(
required_permission="wiki.edit",
granted_permission="wiki_page_create",
group_id=self.group_id,
))
acl.append(DENY_ALL)
return acl
def is_subgroup_of(self, other: "Group") -> bool:
"""Return whether this group is a sub-group of the other one."""
# descendant_of() returns True if the ltrees are equal, so avoid that
if self.path == other.path:
return False
return self.path.descendant_of(other.path)
class Backup(Base):
'''Information about each backed-up data-set.
.. py:attribute:: host
Reference to the :py:class:`Host` that this is a backup of.
.. py:attribute:: backup_server
Reference to the :py:class:`BackupServer` that this backup is on.
.. py:attribute:: storage
Reference to the :py:class:`Storage` that this backup uses.
.. py:attribute:: start_time
When the backup started.
.. py:attribute:: end_time
When the backup completed.
.. py:attribute:: backup_pid
Process ID of the backup, or None if the backup is no longer running.
.. py:attribute:: generation
Generation of backup this was.
.. py:attribute:: successful
Was this backup considered successful?
.. py:attribute:: full_checksum
True if this backup did a full checksum run.
.. py:attribute:: harness_returncode
Return-code from the harness (which runs the rsync) process.
.. py:attribute:: snapshot_name
Storage-specific name of the backup snapshot.
'''
__tablename__ = 'backups'
id = Column(Integer, primary_key=True)
host_id = Column(Integer, ForeignKey('hosts.id'))
host = relationship(Host, order_by=id, backref='backups')
storage_id = Column(Integer, ForeignKey('storage.id'))
storage = relationship(Storage, order_by=id, backref='backups')
start_time = Column(DateTime, default=None)
end_time = Column(DateTime, default=None)
backup_pid = Column(Integer, default=None)
generation = Column(String,
CheckConstraint(
"generation = 'daily' or generation = 'weekly' "
"or generation = 'monthly'"),
nullable=False)
successful = Column(Boolean, default=None)
full_checksum = Column(Boolean, nullable=False)
harness_returncode = Column(Integer, default=None)
snapshot_name = Column(String)
def __init__(self, host, generation, full_checksum):
self.generation = generation
self.full_checksum = full_checksum
self.host = host
def __repr__(self):
return '<Backup(%s: %s@%s)>' % (self.id, self.host.hostname,
self.start_time)
def define_table_args(cls):
table_args = super(TestCheck, cls).define_table_args()
return table_args + (CheckConstraint('integer > 0',
name='test'), )
class Feature(Base):
__tablename__ = 'feature'
# basic attributes
id = Column(Integer, primary_key=True, index=True)
given_name = Column(String)
type = Column(Enum(types.GeenuffFeature))
start = Column(Integer, nullable=False)
start_is_biological_start = Column(Boolean, nullable=False)
end = Column(Integer, nullable=False)
end_is_biological_end = Column(Boolean, nullable=False)
is_plus_strand = Column(Boolean, nullable=False)
score = Column(Float)
source = Column(String)
phase = Column(Integer)
# any piece of coordinate always has just one seqid
coordinate_id = Column(Integer,
ForeignKey('coordinate.id'),
nullable=False)
coordinate = relationship('Coordinate', back_populates='features')
# relations
transcript_pieces = relationship(
'TranscriptPiece',
secondary=association_transcript_piece_to_feature,
back_populates='features')
proteins = relationship('Protein',
secondary=association_protein_to_feature,
back_populates='features')
__table_args__ = (
UniqueConstraint('coordinate_id',
'type',
'start',
'end',
'is_plus_strand',
'given_name',
name='unique_feature'),
CheckConstraint(
'start >= 0 and end >= -1 and phase >= 0 and phase < 3',
name='check_start_end_phase'),
# if start_is_biological_start is True, phase has to be 0
CheckConstraint(
'phase is NULL or (not start_is_biological_start or phase = 0)',
name='check_phase_bio_start'),
# check start/end order depending on is_plus_strand
CheckConstraint(
'(is_plus_strand and start <= end) or (not is_plus_strand and end <= start)',
name='start_end_order'))
def __repr__(self):
start_caveat = end_caveat = ''
if self.start_is_biological_start is None:
start_caveat = '?'
elif self.start_is_biological_start is False:
start_caveat = '*'
if self.end_is_biological_end is None:
end_caveat = '?'
elif self.end_is_biological_end is False:
end_caveat = '*'
s = ('<Feature, id: {pk}, given_name: \'{givenid}\', type: {type}, '
'{start}{start_caveat}--{end}{end_caveat}, on {coor}, '
'is_plus: {plus}, phase: {phase}>').format(
pk=self.id,
start_caveat=start_caveat,
end_caveat=end_caveat,
type=self.type.value,
start=self.start,
end=self.end,
coor=self.coordinate,
plus=self.is_plus_strand,
phase=self.phase,
givenid=self.given_name)
return s
def __table_args__(cls):
return (
UniqueConstraint('key', name='uq_%s_key' % cls.__tablename__),
CheckConstraint(
'create_date <= modify_date',
name='ck_%s_valid_timeline' % cls.__tablename__))
def name(cls):
"""The URL name of this object, unique within a parent container"""
if cls.__name_blank_allowed__:
return Column(Unicode(cls.__name_length__), nullable=False)
else:
return Column(Unicode(cls.__name_length__), CheckConstraint("name!=''"), nullable=False)
def name(cls):
"""The URL name of this instance, non-unique"""
if cls.__name_blank_allowed__:
return Column(Unicode(cls.__name_length__), nullable=False)
else:
return Column(Unicode(cls.__name_length__), CheckConstraint("name!=''"), nullable=False)
def name(cls):
"""The URL name of this object, unique across all instances of this model"""
if cls.__name_blank_allowed__:
return Column(Unicode(cls.__name_length__), nullable=False, unique=True)
else:
return Column(Unicode(cls.__name_length__), CheckConstraint("name!=''"), nullable=False, unique=True)
def __init__(self, sql, meta):
self.sql = sql
self.tb_guild_settings = Table(
"guild_settings",
meta,
Column("guild_id",
BigInteger,
ForeignKey("guilds.guild_id"),
primary_key=True),
Column("prefix", Unicode, nullable=True),
Column("max_delete_messages", SmallInteger),
Column("warn_manual_mod_action", Boolean),
Column("remove_other_roles", Boolean),
Column("mentionable_name_prefix", SmallInteger),
CheckConstraint(
"mentionable_name_prefix >= 0 AND mentionable_name_prefix <= 32",
name="mentionable_name_prefix_in_range",
),
)
self.tb_special_roles = Table(
"special_roles",
meta,
Column("guild_id",
BigInteger,
ForeignKey("guilds.guild_id"),
primary_key=True),
Column("member_role_id", BigInteger, nullable=True),
Column("guest_role_id", BigInteger, nullable=True),
Column("mute_role_id", BigInteger, nullable=True),
Column("jail_role_id", BigInteger, nullable=True),
Column("focus_role_id", BigInteger, nullable=True),
Column("nonpurge_role_id", BigInteger, nullable=True),
# Ensures special roles aren't assigned to @everyone
CheckConstraint(
"member_role_id is NULL OR member_role_id != guild_id",
name="special_role_member_not_everyone_check",
),
CheckConstraint(
"guest_role_id is NULL or guest_role_id != guild_id",
name="special_role_guest_not_everyone_check",
),
CheckConstraint(
"mute_role_id is NULL or mute_role_id != guild_id",
name="special_role_mute_not_everyone_check",
),
CheckConstraint(
"jail_role_id is NULL or jail_role_id != guild_id",
name="special_role_jail_not_everyone_check",
),
CheckConstraint(
"focus_role_id is NULL or focus_role_id != guild_id",
name="special_role_focus_not_everyone_check",
),
CheckConstraint(
"nonpurge_role_id is NULL or nonpurge_role_id != guild_id",
name="special_role_nonpurge_not_everyone_check",
),
# Ensures Guest and punishment roles aren't the same as the Member role
CheckConstraint(
"guest_role_id is NULL OR guest_role_id != member_role_id",
name="special_role_guest_not_member_check",
),
CheckConstraint(
"mute_role_id is NULL OR mute_role_id != member_role_id",
name="special_role_mute_not_member_check",
),
CheckConstraint(
"jail_role_id is NULL OR jail_role_id != member_role_id",
name="special_role_jail_not_member_check",
),
CheckConstraint(
"focus_role_id is NULL OR focus_role_id != member_role_id",
name="special_role_focus_not_member_check",
),
CheckConstraint(
"nonpurge_role_id is NULL OR nonpurge_role_id != member_role_id",
name="special_role_nonpurge_not_member_check",
),
)
self.tb_reapply_roles = Table(
"reapply_roles",
meta,
Column("guild_id",
BigInteger,
ForeignKey("guilds.guild_id"),
primary_key=True),
Column("auto_reapply", Boolean),
Column("role_ids", ARRAY(BigInteger)),
)
self.tb_tracking_blacklists = Table(
"tracking_blacklists",
meta,
Column("guild_id",
BigInteger,
ForeignKey("guilds.guild_id"),
index=True),
Column("type", Enum(LocationType)),
Column("data_id", BigInteger),
UniqueConstraint("guild_id",
"type",
"data_id",
name="tracking_blacklist_uq"),
CheckConstraint(
"type IN ('CHANNEL'::locationtype, 'USER'::locationtype)",
name="type_is_channel_or_user_check",
),
)
self.tb_optional_cog_settings = Table(
"optional_cog_settings",
meta,
Column("guild_id",
BigInteger,
ForeignKey("guilds.guild_id"),
primary_key=True),
Column("cog_name", String, primary_key=True),
Column("settings", JSON),
)
self.guild_settings_cache = {}
self.special_roles_cache = {}
self.reapply_roles_cache = {}
self.tracking_blacklist_cache = {}
self.optional_cog_settings_cache = {}
register_hook("on_guild_join", self.add_guild_settings)
register_hook("on_guild_join", self.add_special_roles)
register_hook("on_guild_join", self.add_reapply_roles)
class Contributor(Base):
"An Actor that made a specific contribution to a work"
__tablename__ = 'contributors'
__table_args__ = (
CheckConstraint('NOT(person_id IS NULL AND group_id IS NULL)'), )
id = Column(Integer, Sequence('contributors_id_seq'), primary_key=True)
role = Column(Unicode(32),
ForeignKey('contributor_role_schemes.key'),
index=True,
nullable=False)
work_id = Column(BigInteger,
ForeignKey('works.id'),
index=True,
nullable=False)
work = relationship('Work', back_populates='contributors', lazy='joined')
person_id = Column(BigInteger,
ForeignKey('persons.id'),
index=True,
nullable=True)
person = relationship('Person', lazy='joined')
group_id = Column(BigInteger,
ForeignKey('groups.id'),
index=True,
nullable=True)
group = relationship('Group', lazy='joined')
description = Column(UnicodeText, nullable=True)
during = Column(DateRangeType, nullable=True)
location = Column(UnicodeText, nullable=True)
position = Column(Integer)
affiliations = relationship('Affiliation',
back_populates='contributor',
lazy='joined')
def to_dict(self):
start_date = end_date = None
if self.during:
start_date, end_date = parse_duration(self.during)
if self.person is None:
person_name = None
else:
person_name = self.person.name
if self.group is None:
group_name = None
else:
group_name = self.group.name
result = {
'id': self.id,
'role': self.role,
'work_id': self.work_id,
'_work_name': self.work.title,
'person_id': self.person_id,
'_person_name': person_name,
'group_id': self.group_id,
'_group_name': group_name,
'start_date': start_date,
'end_date': end_date,
'description': self.description,
'location': self.location,
'position': self.position
}
result['affiliations'] = []
for affiliation in self.affiliations:
result['affiliations'].append(affiliation.to_dict())
return result
def update_dict(self, data):
start_date = data.pop('start_date', None)
end_date = data.pop('end_date', None)
set_attribute(self, 'during', DateInterval([start_date, end_date]))
for key, value in data.items():
if key.startswith('_'):
continue
set_attribute(self, key, value)
@classmethod
def from_dict(cls, data):
contributor = Contributor()
contributor.update_dict(data)
return contributor
class DbKey(Base):
"""
Database definitions for keys in Sqlalchemy format
Part of a wallet, and used by transactions
"""
__tablename__ = 'keys'
id = Column(Integer, Sequence('key_id_seq'), primary_key=True)
parent_id = Column(Integer, Sequence('parent_id_seq'))
name = Column(String(50), index=True)
account_id = Column(Integer, index=True)
depth = Column(Integer)
change = Column(Integer)
address_index = Column(Integer)
public = Column(String(255), index=True)
private = Column(String(255), index=True)
wif = Column(String(255), index=True)
compressed = Column(Boolean, default=True)
key_type = Column(String(10), default='bip32')
address = Column(String(255), index=True)
purpose = Column(Integer, default=44)
is_private = Column(Boolean)
path = Column(String(100))
wallet_id = Column(Integer, ForeignKey('wallets.id'), index=True)
wallet = relationship("DbWallet", back_populates="keys")
transaction_inputs = relationship("DbTransactionInput",
cascade="all,delete",
back_populates="key")
transaction_outputs = relationship("DbTransactionOutput",
cascade="all,delete",
back_populates="key")
balance = Column(Integer, default=0)
used = Column(Boolean, default=False)
network_name = Column(String, ForeignKey('networks.name'))
network = relationship("DbNetwork")
multisig_parents = relationship(
"DbKeyMultisigChildren",
backref='child_key',
primaryjoin=id == DbKeyMultisigChildren.child_id)
multisig_children = relationship(
"DbKeyMultisigChildren",
backref='parent_key',
order_by="DbKeyMultisigChildren.key_order",
primaryjoin=id == DbKeyMultisigChildren.parent_id)
__table_args__ = (
CheckConstraint(key_type.in_(['single', 'bip32', 'multisig']),
name='constraint_key_types_allowed'),
UniqueConstraint('wallet_id',
'public',
name='constraint_wallet_pubkey_unique'),
UniqueConstraint('wallet_id',
'private',
name='constraint_wallet_privkey_unique'),
UniqueConstraint('wallet_id',
'wif',
name='constraint_wallet_wif_unique'),
UniqueConstraint('wallet_id',
'address',
name='constraint_wallet_address_unique'),
)
def __repr__(self):
return "<DbKey(id='%s', name='%s', wif='%s'>" % (self.id, self.name,
self.wif)
def test_deferrable_table_check(self):
factory = lambda **kw: CheckConstraint('a < b', **kw)
self._test_deferrable(factory)
def factory(**kw):
return CheckConstraint("a < b", **kw)
from datetime import datetime
from sqlalchemy import (MetaData, Table, Column, Integer, Numeric, String,
DateTime, ForeignKey, Boolean, create_engine,
CheckConstraint)
metadata = MetaData()
cookies = Table('cookies', metadata,
Column('cookie_id', Integer(), primary_key=True),
Column('cookie_name', String(50), index=True),
Column('cookie_recipe_url', String(255)),
Column('cookie_sku', String(55)),
Column('quantity', Integer()),
Column('unit_cost', Numeric(12, 2)),
CheckConstraint('quantity > 0', name='quantity_positive'))
users = Table(
'users', metadata, Column('user_id', Integer(), primary_key=True),
Column('username', String(15), nullable=False, unique=True),
Column('email_address', String(255), nullable=False),
Column('phone', String(20), nullable=False),
Column('password', String(25), nullable=False),
Column('created_on', DateTime(), default=datetime.now),
Column('updated_on',
DateTime(),
default=datetime.now,
onupdate=datetime.now))
orders = Table('orders', metadata, Column('order_id', Integer()),
Column('user_id', ForeignKey('users.user_id')),
class SqlRun(Base):
"""
DB model for :py:class:`mlflow.entities.Run`. These are recorded in ``runs`` table.
"""
__tablename__ = 'runs'
run_uuid = Column(String(32), nullable=False)
"""
Run UUID: `String` (limit 32 characters). *Primary Key* for ``runs`` table.
"""
name = Column(String(250))
"""
Run name: `String` (limit 250 characters).
"""
source_type = Column(String(20),
default=SourceType.to_string(SourceType.LOCAL))
"""
Source Type: `String` (limit 20 characters). Can be one of ``NOTEBOOK``, ``JOB``, ``PROJECT``,
``LOCAL`` (default), or ``UNKNOWN``.
"""
source_name = Column(String(500))
"""
Name of source recording the run: `String` (limit 500 characters).
"""
entry_point_name = Column(String(50))
"""
Entry-point name that launched the run run: `String` (limit 50 characters).
"""
user_id = Column(String(256), nullable=True, default=None)
"""
User ID: `String` (limit 256 characters). Defaults to ``null``.
"""
status = Column(String(20),
default=RunStatus.to_string(RunStatus.SCHEDULED))
"""
Run Status: `String` (limit 20 characters). Can be one of ``RUNNING``, ``SCHEDULED`` (default),
``FINISHED``, ``FAILED``.
"""
start_time = Column(BigInteger, default=int(time.time()))
"""
Run start time: `BigInteger`. Defaults to current system time.
"""
end_time = Column(BigInteger, nullable=True, default=None)
"""
Run end time: `BigInteger`.
"""
source_version = Column(String(50))
"""
Source version: `String` (limit 50 characters).
"""
lifecycle_stage = Column(String(20), default=LifecycleStage.ACTIVE)
"""
Lifecycle Stage of run: `String` (limit 32 characters).
Can be either ``active`` (default) or ``deleted``.
"""
artifact_uri = Column(String(200), default=None)
"""
Default artifact location for this run: `String` (limit 200 characters).
"""
experiment_id = Column(Integer, ForeignKey('experiments.experiment_id'))
"""
Experiment ID to which this run belongs to: *Foreign Key* into ``experiment`` table.
"""
experiment = relationship('SqlExperiment',
backref=backref('runs', cascade='all'))
"""
SQLAlchemy relationship (many:one) with :py:class:`mlflow.store.dbmodels.models.SqlExperiment`.
"""
__table_args__ = (CheckConstraint(source_type.in_(SourceTypes),
name='source_type'),
CheckConstraint(status.in_(RunStatusTypes),
name='status'),
CheckConstraint(lifecycle_stage.in_(
LifecycleStage.view_type_to_stages(ViewType.ALL)),
name='runs_lifecycle_stage'),
PrimaryKeyConstraint('run_uuid', name='run_pk'))
def to_mlflow_entity(self):
"""
Convert DB model to corresponding MLflow entity.
:return: :py:class:`mlflow.entities.Run`.
"""
run_info = RunInfo(run_uuid=self.run_uuid,
run_id=self.run_uuid,
experiment_id=str(self.experiment_id),
user_id=self.user_id,
status=self.status,
start_time=self.start_time,
end_time=self.end_time,
lifecycle_stage=self.lifecycle_stage,
artifact_uri=self.artifact_uri)
run_data = RunData(
metrics=[m.to_mlflow_entity() for m in self.latest_metrics],
params=[p.to_mlflow_entity() for p in self.params],
tags=[t.to_mlflow_entity() for t in self.tags])
return Run(run_info=run_info, run_data=run_data)
from app import metadata, engineAdmin
import datetime
#--------tabella
users = Table('users', metadata,
Column('id', Integer, primary_key=True, autoincrement=True),
Column('name', String), Column('surname', String),
Column('email', String, unique=True, nullable=False),
Column('password', String, nullable=False))
clients = Table(
'clients', metadata,
Column('id', None, ForeignKey('users.id'), primary_key=True),
Column('credit', Float, nullable=False, default=0.0),
Column('birthDate', Date),
CheckConstraint(column('credit') >= 0, name='credit_ct_0'),
CheckConstraint(column('birthDate') < str(datetime.date.today()),
name='birth_ct_'))
managers = Table(
'managers', metadata,
Column('id', None, ForeignKey('users.id'), primary_key=True),
Column('admin', Boolean, nullable=False, default=False),
Column('financialReport', Float, default=0),
CheckConstraint(column('financialReport') >= 0, name='credit_mg_0'))
theaters = Table(
'theaters', metadata, Column('id', Integer, primary_key=True),
Column('seatsCapacity', Integer, nullable=False),
Column('available', Boolean, nullable=False, default=True),
CheckConstraint(column('seatsCapacity') >= 0, name='capacity_th_0'))
#
# You should have received a copy of the GNU Lesser General Public License
# along with eos. If not, see <http://www.gnu.org/licenses/>.
# ===============================================================================
from sqlalchemy import Table, Column, Integer, ForeignKey, CheckConstraint, Boolean, DateTime
from sqlalchemy.orm import relation, mapper
import datetime
from eos.db import saveddata_meta
from eos.saveddata.module import Module
from eos.saveddata.fit import Fit
modules_table = Table(
"modules", saveddata_meta, Column("ID", Integer, primary_key=True),
Column("fitID", Integer, ForeignKey("fits.ID"), nullable=False,
index=True), Column("itemID", Integer, nullable=True),
Column("dummySlot", Integer, nullable=True, default=None),
Column("chargeID", Integer),
Column("state", Integer, CheckConstraint("state >= -1"),
CheckConstraint("state <= 2")),
Column("projected", Boolean, default=False, nullable=False),
Column("position", Integer),
Column("created", DateTime, nullable=True, default=datetime.datetime.now),
Column("modified", DateTime, nullable=True,
onupdate=datetime.datetime.now),
CheckConstraint(
'("dummySlot" = NULL OR "itemID" = NULL) AND "dummySlot" != "itemID"'))
mapper(Module, modules_table, properties={"owner": relation(Fit)})
class serveces(Base):
__tablename__ = 'services'
email = Column(String(30),
ForeignKey('photographers.email'),
primary_key=True,
nullable=False)
object_shooting = Column(Float)
portrait_shooting = Column(Float)
wedding_photo_shoot = Column(Float)
family_photo_shot = Column(Float)
event_photography = Column(Float)
reportage_shooting = Column(Float)
childrens_photo_shoot = Column(Float)
interior_shooting = Column(Float)
photosession_love_story = Column(Float)
pregnant_photoshoot = Column(Float)
neither = Column(Float)
CheckConstraint('object_shooting > 0', name='check_object_shooting')
CheckConstraint('portrait_shooting > 0', name='check_portrait_shooting')
CheckConstraint('wedding_photo_shoot > 0',
name='check_wedding_photo_shoot')
CheckConstraint('family_photo_shot > 0', name='check_family_photo_shot')
CheckConstraint('event_photography > 0', name='check_event_photography')
CheckConstraint('reportage_shooting > 0', name='check_reportage_shooting')
CheckConstraint('childrens_photo_shoot > 0',
name='check_childrens_photo_shoot')
CheckConstraint('interior_shooting > 0', name='check_interior_shooting')
CheckConstraint('photosession_love_story > 0',
name='check_photosession_love_story')
CheckConstraint('pregnant_photoshoot > 0',
name='check_pregnant_photoshoot')
CheckConstraint('neither > 0', name='check_neither')
serveces_fk = relationship("photographers", foreign_keys=[email])
@classmethod
def add(cls,
email,
object_shooting=None,
portrait_shooting=None,
wedding_photo_shoot=None,
family_photo_shot=None,
event_photography=None,
reportage_shooting=None,
childrens_photo_shoot=None,
interior_shooting=None,
photosession_love_story=None,
pregnant_photoshoot=None,
neither=None):
Session = sessionmaker(bind=engine)
session = Session()
print("Added")
servece = serveces(email=email,
object_shooting=object_shooting,
portrait_shooting=portrait_shooting,
wedding_photo_shoot=wedding_photo_shoot,
family_photo_shot=family_photo_shot,
event_photography=event_photography,
reportage_shooting=reportage_shooting,
childrens_photo_shoot=childrens_photo_shoot,
interior_shooting=interior_shooting,
photosession_love_story=photosession_love_story,
pregnant_photoshoot=pregnant_photoshoot,
neither=neither)
session.add(servece)
session.commit()
class User(Base):
__tablename__ = 'users'
__table_args__ = (
ForeignKeyConstraint(['fk_institution'], ['data.institutions.id']),
# Create a constraint to make sure that there is an activation uuid if
# the user account is not active
CheckConstraint(
'(data.users.is_active = FALSE) = (data.users.activation_uuid IS '
'NOT NULL)',
name="data_users_activation_uuid_not_null"),
{
'schema': 'data'
})
id = Column(Integer, primary_key=True)
uuid = Column(UUID, nullable=False, unique=True)
username = Column(String(255), nullable=False, unique=True)
email = Column(String(255), nullable=False)
firstname = Column(String(255))
lastname = Column(String(255))
privacy = Column(Integer, nullable=False)
registration_timestamp = Column(DateTime(timezone=True), nullable=False)
is_active = Column(Boolean, nullable=False, server_default='FALSE')
activation_uuid = Column(UUID, nullable=True)
is_approved = Column(Boolean, nullable=False, server_default='FALSE')
fk_institution = Column(Integer)
changesets = relationship('Changeset', backref='user')
groups = relationship('Group',
secondary=users_groups,
backref=backref('users', order_by=id))
profiles = relationship('Profile',
secondary=users_profiles,
backref=backref('users', order_by=id))
comments = relationship('Comment', backref='user')
a_reviews = relationship('Activity', backref='user_review')
sh_reviews = relationship('Stakeholder', backref='user_review')
# password encryption
_password = Column('password', Unicode(64))
def _get_password(self):
return self._password
def _set_password(self, password):
self._password = hash_password(password)
password = property(_get_password, _set_password)
password = synonym('_password', descriptor=password)
@classmethod
def get_by_username(cls, username):
return DBSession.query(cls).filter(cls.username == username).first()
"""
Call this method to check if login credentials are correct.
Returns TRUE if correct.
"""
@classmethod
def check_password(cls, username, password):
user = cls.get_by_username(username)
if not user:
return False
# Check also if the user is activated and approved
active, approved = DBSession.query(
cls.is_active,
cls.is_approved).filter(cls.username == username).first()
# Return True if the password is correct and the user is active and
# approved
return (crypt.check(user.password, password) and active and approved)
def set_new_password(self):
"""
Creates and sets a new password.
"""
# The password length
password_length = 12
# Creates randomly a new password with letters and digits
new_password = ''.join([
random.choice(string.ascii_letters + string.digits)
for i in range(password_length)
])
# Set it
self._set_password(new_password)
# Return the new password uncrypted
return new_password
def __init__(self,
username,
password,
email,
firstname=None,
lastname=None,
privacy=None,
is_active=False,
activation_uuid=None,
is_approved=False,
registration_timestamp=None):
self.uuid = str(uuid.uuid4())
self.username = username
self.password = password
self.email = email
self.firstname = firstname
self.lastname = lastname
self.privacy = privacy if privacy is not None else 1
self.activation_uuid = activation_uuid
self.is_active = is_active
self.is_approved = is_approved
self.registration_timestamp = registration_timestamp
def __repr__(self):
return (
'<User> id [ %s ] | uuid [ %s ] | username [ %s ] | password [ ***'
' ] | email [ %s ]' %
(self.id, self.uuid, self.username, self.email))
class Group(DatabaseModel):
"""Model for a group on the site.
Trigger behavior:
Incoming:
- num_subscriptions will be incremented and decremented by insertions and
deletions in group_subscriptions.
"""
schema_class = GroupSchema
__tablename__ = "groups"
group_id: int = Column(Integer, primary_key=True)
path: Ltree = Column(LtreeType, nullable=False, index=True, unique=True)
created_time: datetime = Column(
TIMESTAMP(timezone=True),
nullable=False,
index=True,
server_default=text("NOW()"),
)
short_description: Optional[str] = Column(
Text,
CheckConstraint(
f"LENGTH(short_description) <= {SHORT_DESCRIPTION_MAX_LENGTH}",
name="short_description_length",
),
)
_sidebar_markdown: str = deferred(Column("sidebar_markdown", Text))
sidebar_rendered_html: str = deferred(Column(Text))
num_subscriptions: int = Column(Integer,
nullable=False,
server_default="0")
is_admin_posting_only: bool = Column(Boolean,
nullable=False,
server_default="false")
is_user_treated_as_topic_source: bool = Column(Boolean,
nullable=False,
server_default="false")
_common_topic_tags: List[Ltree] = Column("common_topic_tags",
ArrayOfLtree,
nullable=False,
server_default="{}")
# Create a GiST index on path as well as the btree one that will be created by the
# index=True/unique=True keyword args to Column above. The GiST index supports
# additional operators for ltree queries: @>, <@, @, ~, ?
__table_args__ = (Index("ix_groups_path_gist",
path,
postgresql_using="gist"), )
@hybrid_property
def common_topic_tags(self) -> List[str]:
"""Return the group's list of common topic tags."""
return [str(tag).replace("_", " ") for tag in self._common_topic_tags]
@common_topic_tags.setter # type: ignore
def common_topic_tags(self, new_tags: List[str]) -> None:
self._common_topic_tags = new_tags
@hybrid_property
def sidebar_markdown(self) -> str:
"""Return the sidebar's markdown."""
return self._sidebar_markdown
@sidebar_markdown.setter # type: ignore
def sidebar_markdown(self, new_markdown: str) -> None:
"""Set the sidebar's markdown and render its HTML."""
if new_markdown == self.sidebar_markdown:
return
self._sidebar_markdown = new_markdown
if self._sidebar_markdown is not None:
self.sidebar_rendered_html = convert_markdown_to_safe_html(
new_markdown)
else:
self.sidebar_rendered_html = None
def __repr__(self) -> str:
"""Display the group's path and ID as its repr format."""
return f"<Group {self.path} ({self.group_id})>"
def __str__(self) -> str:
"""Use the group path for the string representation."""
return str(self.path)
def __lt__(self, other: "Group") -> bool:
"""Order groups by their string representation."""
return str(self) < str(other)
def __init__(self, path: str, short_desc: Optional[str] = None):
"""Create a new group."""
self.path = path
self.short_description = short_desc
def __acl__(self) -> Sequence[Tuple[str, Any, str]]:
"""Pyramid security ACL."""
acl = []
# view:
# - all groups can be viewed by everyone
acl.append((Allow, Everyone, "view"))
# subscribe:
# - all groups can be subscribed to by logged-in users
acl.append((Allow, Authenticated, "subscribe"))
# post_topic:
# - only admins can post in admin-posting-only groups
# - otherwise, all logged-in users can post
if self.is_admin_posting_only:
acl.append((Allow, "admin", "post_topic"))
acl.append((Deny, Everyone, "post_topic"))
acl.append((Allow, Authenticated, "post_topic"))
# wiki_page_create
# - permission must be granted specifically
acl.append((Allow, "admin", "wiki_page_create"))
acl.append((Allow, "wiki", "wiki_page_create"))
acl.append(DENY_ALL)
return acl
def is_subgroup_of(self, other: "Group") -> bool:
"""Return whether this group is a sub-group of the other one."""
# descendant_of() returns True if the ltrees are equal, so avoid that
if self.path == other.path:
return False
return self.path.descendant_of(other.path)
class Vote(db.Model): # type: ignore
"""Public voting support for talks."""
created = db.Column(db.TIMESTAMP, nullable=False, default=datetime.utcnow)
updated = db.Column(db.TIMESTAMP,
nullable=False,
default=datetime.utcnow,
onupdate=datetime.utcnow)
talk_id = db.Column(db.Integer,
db.ForeignKey("talk.talk_id"),
primary_key=True)
user_id = db.Column(db.Integer,
db.ForeignKey("user.user_id"),
primary_key=True)
# A public id is used to expose a reference to a vote without
# leaking information about the talk. This helps prevent brigading
# and ballot stuffing.
public_id = db.Column(UUID(as_uuid=True), default=uuid.uuid4, unique=True)
value = db.Column(db.Integer)
# A talk can be skipped without a vote value.
# This allows a voter to come back to talks at a later time.
skipped = db.Column(db.Boolean)
talk = db.relationship("Talk", backref=db.backref("votes", lazy="dynamic"))
user = db.relationship("User", backref=db.backref("votes", lazy="dynamic"))
__table_args__ = (
# TODO: Is this the correct approach here? Should conferences be
# able to set their own voting scales?
CheckConstraint("value is NULL OR value IN (-1, 0, 1)",
name="ck_vote_values"), )
@classmethod
def clear_skipped(cls,
*,
user: User,
category: Category = None,
commit: bool = False) -> None:
"""Remove any skipped votes for the given user and category.
Args:
user: The user that owns the votes to be cleared. Required.
category: The category to clear skipped votes for. If
``None``, clear skipped votes regardless of category.
Defaults to ``None``.
commit: If ``True``, commit the SQLAlchemy session. If no
other changes are being made at a call site, this should
be set to ``True``. If other changes are being made,
however, it should be left as ``False`` to defer the
commit until after other database operations are
performed. Defaults to ``False``.
"""
query = db.session.query(cls).filter(
cls.skipped == True,
cls.user == user # noqa: E712
)
if category is not None:
query = query.filter(
Vote.talk_id == TalkCategory.talk_id,
TalkCategory.category_id == category.category_id,
)
query.delete(synchronize_session="fetch")
db.session.execute(query)
if commit:
db.session.commit()
class User(DeclarativeBase):
"""
User definition.
This is the user definition used by :mod:`repoze.who`, which requires at
least the ``email`` column.
"""
MIN_PASSWORD_LENGTH = 6
MAX_PASSWORD_LENGTH = 512
MAX_HASHED_PASSWORD_LENGTH = 128
MIN_PUBLIC_NAME_LENGTH = 3
MAX_PUBLIC_NAME_LENGTH = 255
MIN_EMAIL_LENGTH = 3
MAX_EMAIL_LENGTH = 255
MIN_USERNAME_LENGTH = 3
MAX_USERNAME_LENGTH = 255
MAX_IMPORTED_FROM_LENGTH = 32
MAX_TIMEZONE_LENGTH = 32
MIN_LANG_LENGTH = 2
MAX_LANG_LENGTH = 3
MAX_AUTH_TOKEN_LENGTH = 255
MAX_RESET_PASSWORD_TOKEN_HASH_LENGTH = 255
DEFAULT_ALLOWED_SPACE = 0
USERNAME_OR_EMAIL_REQUIRED_CONSTRAINT_NAME = "ck_users_username_email"
__tablename__ = "users"
# INFO - G.M - 2018-10-24 - force table to use utf8 instead of
# utf8bm4 for mysql only in order to avoid max length of key issue with
# long varchar in utf8bm4 column. This issue is related to email
# field and is uniqueness. As far we search, there is to be no way to apply
# mysql specific (which is ignored by other database)
# collation only on email field.
__table_args__ = (
CheckConstraint(
"NOT (email IS NULL AND username IS NULL)",
name=USERNAME_OR_EMAIL_REQUIRED_CONSTRAINT_NAME,
),
{
"mysql_charset": "utf8",
"mysql_collate": "utf8_general_ci"
},
)
user_id = Column(Integer,
Sequence("seq__users__user_id"),
autoincrement=True,
primary_key=True)
email = Column(Unicode(MAX_EMAIL_LENGTH), unique=True, nullable=True)
username = Column(Unicode(MAX_USERNAME_LENGTH), unique=True, nullable=True)
display_name = Column(Unicode(MAX_PUBLIC_NAME_LENGTH))
_password = Column("password",
Unicode(MAX_HASHED_PASSWORD_LENGTH),
nullable=True)
created = Column(DateTime, default=datetime.utcnow)
is_active = Column(Boolean, default=True, nullable=False)
is_deleted = Column(
Boolean,
default=False,
nullable=False,
server_default=sqlalchemy.sql.expression.literal(False),
)
imported_from = Column(Unicode(MAX_IMPORTED_FROM_LENGTH), nullable=True)
# timezone as tz database format
timezone = Column(Unicode(MAX_TIMEZONE_LENGTH),
nullable=False,
server_default="")
# lang in iso639 format
auth_type = Column(Enum(AuthType),
nullable=False,
server_default=AuthType.INTERNAL.name)
lang = Column(Unicode(MAX_LANG_LENGTH), nullable=True, default=None)
# TODO - G.M - 04-04-2018 - [auth] Check if this is already needed
# with new auth system
# TODO - G.M - 2018-08-22 - Think about hash instead of direct token
auth_token = Column(Unicode(MAX_AUTH_TOKEN_LENGTH))
auth_token_created = Column(DateTime)
reset_password_token_hash = Column(
Unicode(MAX_RESET_PASSWORD_TOKEN_HASH_LENGTH),
nullable=True,
default=None)
reset_password_token_created = Column(DateTime,
nullable=True,
default=None)
allowed_space = Column(BigInteger,
nullable=False,
server_default=str(DEFAULT_ALLOWED_SPACE))
profile = Column(Enum(Profile),
nullable=False,
server_default=Profile.NOBODY.name)
@hybrid_property
def email_address(self):
return self.email
@property
def public_name(self) -> str:
return self.display_name
@property
def avatar_url(self) -> typing.Optional[str]:
# TODO - G-M - 20-04-2018 - [Avatar] Add user avatar feature
return None
def __repr__(self):
return "<User: email=%s, username=%s display=%s>" % (
repr(self.email),
repr(self.username),
repr(self.display_name),
)
def __unicode__(self):
return self.display_name or self.email or self.username
@classmethod
def by_email_address(cls, email, dbsession):
"""Return the user object whose email address is ``email``."""
return dbsession.query(cls).filter_by(email=email).first()
@classmethod
def by_username(cls, username, dbsession):
"""Return the user object whose user name is ``username``."""
return dbsession.query(cls).filter_by(username=username).first()
@property
def login(self) -> str:
"""Return email or username if no email"""
return self.email or self.username
def _set_password(self, cleartext_password: typing.Optional[str]) -> None:
"""
Set ciphertext password from cleartext password.
Hash cleartext password on the fly,
Store its ciphertext version,
"""
if cleartext_password is None:
self._password = None
else:
self._password = self._hash(cleartext_password)
def _get_password(self) -> str:
"""Return the hashed version of the password."""
return self._password
password = synonym("_password",
descriptor=property(_get_password, _set_password))
def validate_password(self,
cleartext_password: typing.Optional[str]) -> bool:
"""
Check the password against existing credentials.
:param cleartext_password: the password that was provided by the user
to try and authenticate. This is the clear text version that we
will need to match against the hashed one in the database.
:type cleartext_password: unicode object.
:return: Whether the password is valid.
:rtype: bool
"""
if not self.password:
return False
return self._validate_hash(self.password, cleartext_password)
def get_display_name(self, remove_email_part: bool = False) -> str:
"""
Get a name to display from corresponding display_name, username or email.
:param remove_email_part: If True and display name based on email,
remove @xxx.xxx part of email in returned value
:return: display name based on user name or email.
"""
if self.display_name:
return self.display_name
if self.username:
return self.username
if remove_email_part:
at_pos = self.email.index("@")
return self.email[0:at_pos]
return self.email
def get_role(self, workspace: "Workspace") -> int:
for role in self.roles:
if role.workspace == workspace:
return role.role
return UserRoleInWorkspace.NOT_APPLICABLE
def get_active_roles(self) -> ["UserRoleInWorkspace"]:
"""
:return: list of roles of the user for all not-deleted workspaces
"""
roles = []
for role in self.roles:
if not role.workspace.is_deleted:
roles.append(role)
return roles
# Tokens ###
def reset_tokens(self):
self._generate_auth_token()
# disable reset_password token
self.reset_password_token_hash = None
self.reset_password_token_created = None
# Reset Password Tokens #
def generate_reset_password_token(self) -> str:
(
reset_password_token,
self.reset_password_token_created,
self.reset_password_token_hash,
) = self._generate_token(create_hash=True)
return reset_password_token
def validate_reset_password_token(self, token, validity_seconds) -> bool:
if not self.reset_password_token_created:
raise InvalidResetPasswordToken(
"reset password token is unvalid due to unknown creation date")
if not self._validate_date(self.reset_password_token_created,
validity_seconds):
raise ExpiredResetPasswordToken("reset password token has expired")
if not self._validate_hash(self.reset_password_token_hash, token):
raise InvalidResetPasswordToken("reset password token is unvalid")
return True
# Auth Token #
# TODO - G.M - 04-04-2018 - [auth] Check if this is already needed
# with new auth system
def _generate_auth_token(self) -> str:
self.auth_token, self.auth_token_created, _ = self._generate_token()
return self.auth_token
# TODO - G.M - 2018-08-23 - Should we store hash instead of direct stored
# auth token ?
def validate_auth_token(self, token, validity_seconds) -> bool:
return self.ensure_auth_token(validity_seconds) == token
def ensure_auth_token(self, validity_seconds) -> str:
"""
Create auth_token if None, regenerate auth_token if too old.
auth_token validity is set in
:return: actual valid auth token
"""
if not self.auth_token or not self.auth_token_created:
self._generate_auth_token()
return self.auth_token
if not self._validate_date(self.auth_token_created, validity_seconds):
self._generate_auth_token()
return self.auth_token
# Utils functions #
@classmethod
def _hash(cls, cleartext_password_or_token: str) -> str:
salt = sha256()
salt.update(os.urandom(60))
salt = salt.hexdigest()
hashed = sha256()
# Make sure password is a str because we cannot hash unicode objects
hashed.update((cleartext_password_or_token + salt).encode("utf-8"))
hashed = hashed.hexdigest()
ciphertext_password = salt + hashed
# Make sure the hashed password is a unicode object at the end of the
# process because SQLAlchemy _wants_ unicode objects for Unicode cols
# FIXME - D.A. - 2013-11-20 - The following line has been removed since using python3. Is this normal ?!
# password = password.decode('utf-8')
return ciphertext_password
@classmethod
def _validate_hash(cls, hashed: str,
cleartext_password_or_token: str) -> bool:
result = False
if hashed:
new_hash = sha256()
new_hash.update(
(cleartext_password_or_token + hashed[:64]).encode("utf-8"))
result = hashed[64:] == new_hash.hexdigest()
return result
@classmethod
def _generate_token(
cls,
create_hash=False
) -> typing.Union[str, datetime, typing.Optional[str]]:
token = str(uuid.uuid4().hex)
creation_datetime = datetime.utcnow()
hashed_token = None
if create_hash:
hashed_token = cls._hash(token)
return token, creation_datetime, hashed_token
@classmethod
def _validate_date(cls, date: datetime, validity_seconds: int) -> bool:
if not date:
return False
now_seconds = time.mktime(datetime.utcnow().timetuple())
auth_token_seconds = time.mktime(date.timetuple())
difference = now_seconds - auth_token_seconds
if difference > validity_seconds:
return False
return True
class Project(Base, Translatable):
__tablename__ = 'project'
id = Column(Integer, primary_key=True)
status_archived = 0
status_published = 1
status_draft = 2
status = Column(Integer, default=status_draft)
locale = 'en'
area_id = Column(Integer, ForeignKey('areas.id'))
created = Column(DateTime, default=datetime.datetime.utcnow)
author_id = Column(BigInteger, ForeignKey('users.id'))
author = relationship(User)
last_update = Column(DateTime, default=datetime.datetime.utcnow)
area = relationship(Area)
tasks = relationship(Task,
backref='project',
cascade="all, delete, delete-orphan")
license_id = Column(Integer, ForeignKey('licenses.id'))
zoom = Column(Integer) # is not None when project is auto-filled (grid)
imagery = Column(Unicode)
# priorities are:
# 0 - Urgent
# 1 - High
# 2 - Medium
# 3 - Low
priority = Column(Integer, default=2)
# percentage of done tasks
done = Column(Float, default=0)
# percentage of validated tasks
validated = Column(Float, default=0)
__table_args__ = (CheckConstraint(priority.in_(range(0, 4))), )
entities_to_map = Column(Unicode)
changeset_comment = Column(Unicode)
private = Column(Boolean, default=False)
allowed_users = relationship(User, secondary=project_allowed_users)
josm_preset = Column(Unicode)
due_date = Column(DateTime)
priority_areas = relationship(PriorityArea,
secondary=project_priority_areas)
def __init__(self, name, user=None):
self.name = name
self.author = user
# auto magically fills the area with tasks for the given zoom
def auto_fill(self, zoom):
self.zoom = zoom
geom_3857 = DBSession.execute(ST_Transform(self.area.geometry, 3857)) \
.scalar()
geom_3857 = shape.to_shape(geom_3857)
tasks = []
for i in get_tiles_in_geom(geom_3857, zoom):
multi = MultiPolygon([i[2]])
geometry = ST_Transform(shape.from_shape(multi, 3857), 4326)
tasks.append(Task(i[0], i[1], zoom, geometry))
self.tasks = tasks
def import_from_geojson(self, input):
geoms = parse_geojson(input)
tasks = []
for geom in geoms:
if not isinstance(geom, MultiPolygon):
geom = MultiPolygon([geom])
tasks.append(Task(None, None, None, 'SRID=4326;%s' % geom.wkt))
self.tasks = tasks
DBSession.add(self)
DBSession.flush()
bounds = DBSession.query(ST_Union(ST_Buffer(Task.geometry, 0.01))) \
.filter(Task.project_id == self.id).one()
self.area = Area(bounds[0])
return len(tasks)
def get_done(self):
total = DBSession.query(func.sum(ST_Area(Task.geometry))) \
.filter(
Task.project_id == self.id,
Task.cur_state.has(TaskState.state != TaskState.state_removed)
) \
.scalar()
done = DBSession.query(func.sum(ST_Area(Task.geometry))) \
.filter(
Task.project_id == self.id,
Task.cur_state.has(TaskState.state == TaskState.state_done)
) \
.scalar()
if not done:
done = 0
return round(done * 100 / total, 2) if total != 0 else 0
def get_validated(self):
total = DBSession.query(func.sum(ST_Area(Task.geometry))) \
.filter(
Task.project_id == self.id,
Task.cur_state.has(TaskState.state != TaskState.state_removed)
) \
.scalar()
validated = DBSession.query(func.sum(ST_Area(Task.geometry))) \
.filter(
Task.project_id == self.id,
Task.cur_state.has(
TaskState.state == TaskState.state_validated)
) \
.scalar()
if not validated:
validated = 0
return round(validated * 100 / total, 2) if total != 0 else 0
def to_bbox(self):
return shape.to_shape(self.area.geometry).bounds
# get the count of currently locked tasks
def get_locked(self):
query = DBSession.query(Task).options(joinedload(Task.cur_lock)) \
.filter(and_(Task.cur_lock.has(lock=True),
Task.project_id == self.id))
return query.count()
def to_feature(self):
properties = {}
properties['name'] = self.name
properties['description'] = self.description
properties['short_description'] = self.short_description
properties['instructions'] = self.instructions
properties['per_task_instructions'] = self.per_task_instructions
properties['status'] = self.status
properties['created'] = self.created.strftime('%FT%TZ')
if self.author:
properties['author'] = self.author.username
properties['last_update'] = self.last_update.strftime('%FT%TZ')
properties['license'] = self.license_id
properties['priority'] = self.priority
properties['done'] = self.done
properties['validated'] = self.validated
properties['changeset_comment'] = self.changeset_comment
return Feature(geometry=shape.to_shape(self.area.geometry),
id=self.id,
properties=properties)
for part_bound_low, part_bound_high in zip(partitions[:-1], partitions[1:]):
## RESIDUES
residues_tablename = 'pi_group_residues_biomol_le_{0}'.format(
part_bound_high)
# residues_rulename = residues_tablename + '_insert' ## TRIGGERS, NOT RULES!
residues_partition = Table(
residues_tablename,
metadata,
Column('pi_id', Integer, nullable=False, autoincrement=False),
Column('biomolecule_id', Integer, index=True, nullable=False),
Column('residue_id', Integer, nullable=False),
Column('path', PTree, nullable=False),
CheckConstraint(
"biomolecule_id > {0} AND biomolecule_id <= {1}".format(
part_bound_low, part_bound_high)),
postgresql_inherits=quoted_name(pi_group_res.fullname,
False), # new SQLAlchemy 1.0 feature
schema=schema)
PrimaryKeyConstraint(residues_partition.c.pi_id,
residues_partition.c.residue_id,
deferrable=True,
initially='deferred')
Index('idx_{}_path'.format(residues_tablename),
residues_partition.c.path,
postgresql_using='gist')
# neccessary to drop tables with sqlalchemy
residues_partition.add_is_dependent_on(pi_group_res)
class ItemLog(db.Model, BaseMixin, AuditableMixin):
"""Inventory log model to track usage
If a purchase is made, debit is vendor id and credit is item id
if a sale is mafe, debit is item id and credit is entity_id
"""
item_id = db.Column(db.String(50), db.ForeignKey("item.uuid"))
reference = db.Column(db.String(50)) # job id or vendor id
category = db.Column(db.String(50)) # sale or purchase
credit = db.Column(db.String(50))
debit = db.Column(db.String(50))
quantity = db.Column(db.Integer)
unit_cost = db.Column(db.String(50))
pay_type = db.Column(db.String(50))
on_credit = db.Column(db.Boolean, default=False)
credit_status = db.Column(db.String(50), default='NONE')
amount = db.Column(db.Numeric(20, 2), CheckConstraint("amount > 0.0"))
entity_id = db.Column(db.String(50), db.ForeignKey("entity.uuid"))
accounting_date = db.Column(db.Date, default=db.func.now())
accounting_period = db.Column(db.String(50), default=db.func.now())
entity = db.relationship("Entity")
item = db.relationship("Item")
def __init__(self, **kwargs):
super(ItemLog, self).__init__(**kwargs)
self.accounting_period = datetime.now().strftime("%Y-%m")
self.get_uuid()
def __repr__(self):
return "<ItemLog %s>" % self.item_id
@property
def debit_account(self):
sql = (
""" name FROM item_accounts where item_accounts.uuid ='"""
+ str(self.debit)
+ """'
"""
)
data = query(sql)
return data if data is None else data[0]["name"]
@property
def credit_account(self):
sql = (
""" name FROM item_accounts where item_accounts.uuid ='"""
+ str(self.credit)
+ """'"""
)
data = query(sql)
return data if data is None else data[0]["name"]
def is_valid(self):
"""validate the object"""
if self.category not in ('sale', 'purchase'):
return False, {"msg": "The category {0} doesn't exist".format(self.tran_type)}, 422
if not Item.get(uuid=self.credit) and not Item.get(uuid=self.debit):
return False, {"msg": "The supplied item id does not exist"}, 422
if ItemLog.get(reference=self.reference):
return False, {"msg": "The supplied reference already exists"}, 409
if ItemLog.get(reference=self.cheque_number):
return False, {"msg": "This transaction is already reversed"}, 409
if self.category == "reversal" and not ItemLog.get(
reference=self.cheque_number
):
return False, {"msg": "You can only reverse an existing transaction"}, 422
# check balance
item = Item.get(uuid=self.debit)
bal_after = int(item.quantity) - int(self.quantity)
app.logger.info(item.quantity, self.quantity)
if Item.get(uuid=self.item_id).name != 'labour' and self.category == 'sale' and float(bal_after) < 0.0:
return False, {
"msg": "Insufficient quantity on the {0} account {1}".format(item.name, commas(item.quantity))}, 409
if self.tran_type == "reversal":
orig = ItemLog.get(reference=self.cheque_number)
self.debit = orig.credit
self.credit = orig.debit
self.amount = orig.amount
self.entity_id = orig.entity_id
return True, self, 200
@staticmethod
def init_jobitem(job_item):
item_log = ItemLog(
item_id=job_item.item_id,
debit=job_item.item_id,
credit=job_item.entity_id,
reference=job_item.job_id,
category='sale',
quantity=job_item.quantity,
amount=job_item.cost,
unit_cost=job_item.unit_cost,
entity_id=job_item.entity_id,
)
valid, reason, status = item_log.is_valid()
if not valid:
raise Exception(reason.get('msg'), status)
return item_log
def transact(self):
"""
:rtype: object
If a customer is invoiced, value is debited off their account onto
the entity account, when they make a payment
1. Value is debited off the `escrow` onto the customer account
2. Value is also moved off the entity account onto the pay type account
In the future, the payment type account ought to be created per entity
"""
valid, reason, status = self.is_valid()
if not valid:
raise Exception(reason.get('msg'), status)
db.session.commit()
class GroupWikiPage(DatabaseModel):
"""Model for a wiki page in a group."""
schema_class = GroupWikiPageSchema
__tablename__ = "group_wiki_pages"
BASE_PATH = "/var/lib/tildes-wiki"
GITLAB_REPO_URL = "https://gitlab.com/tildes/tildes-wiki"
group_id: int = Column(Integer,
ForeignKey("groups.group_id"),
nullable=False,
primary_key=True)
path: str = Column(CIText, nullable=False, primary_key=True)
page_name: str = Column(
Text,
CheckConstraint(f"LENGTH(page_name) <= {PAGE_NAME_MAX_LENGTH}",
name="page_name_length"),
nullable=False,
)
created_time: datetime = Column(TIMESTAMP(timezone=True),
nullable=False,
server_default=text("NOW()"))
last_edited_time: Optional[datetime] = Column(TIMESTAMP(timezone=True),
index=True)
rendered_html: str = Column(Text, nullable=False)
group: Group = relationship("Group", innerjoin=True, lazy=False)
def __init__(self, group: Group, page_name: str, markdown: str,
user: User):
"""Create a new wiki page."""
self.group = group
self.page_name = page_name
self.path = convert_to_url_slug(page_name)
# prevent possible conflict with url for creating a new page
if self.slug == "new_page":
raise ValueError("Invalid page name")
if self.file_path.exists():
raise ValueError("Wiki page already exists")
# create the directory for the group if it doesn't already exist
self.file_path.parent.mkdir(mode=0o755, exist_ok=True)
self.edit(markdown, user, "Create page")
def __acl__(self) -> Sequence[Tuple[str, Any, str]]:
"""Pyramid security ACL."""
acl = []
# view:
# - all wiki pages can be viewed by everyone
acl.append((Allow, Everyone, "view"))
# edit:
# - permission must be granted specifically
acl.append((Allow, "admin", "edit"))
acl.append((Allow, "wiki", "edit"))
acl.append(DENY_ALL)
return acl
@property
def file_path(self) -> Path:
"""Return the full path to the page's file."""
return Path(self.BASE_PATH, self.relative_path)
@property
def relative_path(self) -> Path:
"""Return a relative path to the page's file."""
return Path(str(self.group.path), f"{self.path}.md")
@property
def slug(self) -> str:
"""Return the page's slug, which is also its filename with no extension."""
return self.file_path.stem
@property
def folders(self) -> List[PurePath]:
"""Return a list of the folders the page is inside (if any)."""
path = PurePath(self.path)
# the last element of .parents will be ".", chop that off
folders = list(path.parents)[:-1]
# reverse the list, since .parents goes "upwards" towards root
folders.reverse()
return folders
@property
def history_url(self) -> str:
"""Return a url to the page's edit history."""
return f"{self.GITLAB_REPO_URL}/commits/master/{self.relative_path}"
@property
def blame_url(self) -> str:
"""Return a url to the page's blame view."""
return f"{self.GITLAB_REPO_URL}/blame/master/{self.relative_path}"
@property
def markdown(self) -> Optional[str]:
"""Return the wiki page's markdown."""
try:
return self.file_path.read_text().rstrip("\r\n")
except FileNotFoundError:
return None
@markdown.setter
def markdown(self, new_markdown: str) -> None:
"""Write the wiki page's markdown to its file."""
# write the markdown to the file, appending a newline if necessary
if not new_markdown.endswith("\n"):
new_markdown = new_markdown + "\n"
self.file_path.write_text(new_markdown)
def edit(self, new_markdown: str, user: User, edit_message: str) -> None:
"""Set the page's markdown, render its HTML, and commit the repo."""
if new_markdown == self.markdown:
return
self.markdown = new_markdown
self.rendered_html = convert_markdown_to_safe_html(new_markdown)
self.rendered_html = add_anchors_to_headings(self.rendered_html)
self.last_edited_time = utc_now()
repo = Repository(self.BASE_PATH)
author = Signature(user.username, user.username)
repo.index.read()
repo.index.add(str(self.file_path.relative_to(self.BASE_PATH)))
repo.index.write()
# Prepend the group name and page path to the edit message - if you change the
# format of this, make sure to also change the page-editing template to match
edit_message = f"~{self.group.path}/{self.path}: {edit_message}"
repo.create_commit(
repo.head.name,
author,
author,
edit_message,
repo.index.write_tree(),
[repo.head.target],
)
