def test_check_constraint_copy(self):
m = MetaData()
t = Table("tbl", m, Column("a", Integer), Column("b", Integer))
ck = CheckConstraint(t.c.a > 5)
ck2 = ck.copy()
assert ck in t.constraints
assert ck2 not in t.constraints
def test_check_constraint_copy(self):
r = lambda x: x
c = CheckConstraint("foo bar",
name='name',
initially=True,
deferrable=True,
_create_rule = r)
c2 = c.copy()
eq_(c2.name, 'name')
eq_(str(c2.sqltext), "foo bar")
eq_(c2.initially, True)
eq_(c2.deferrable, True)
assert c2._create_rule is r
class HeatShield(DeclarativeBase):
__tablename__ = 'heat_shield'
id = Column(Integer, primary_key=True)
name = Column('name', String)
parachute_descent = relationship('ParachuteDescent',
back_populates='heat_shield') # link to parachute descent
hs_geometry = Column('hs_geometry', String, CheckConstraint("hs_geometry IN ('70 deg cone')"))
hs_diameter = Column('hs_diameter', Float)
hs_diameter_unit = Column('hs_diameter_unit', String, CheckConstraint("hs_diameter_unit IN ('in','cm','m')"))
hs_tps = Column('hs_tps', String, CheckConstraint("hs_tps IN ('SLA-561')"))
hs_thickness = Column('hs_thickness', Float)
hs_thickness_unit = Column('hs_thickness_unit', String, CheckConstraint("hs_thickness_unit IN ('in','cm','m')"))
hs_total_integrated_heating = Column('hs_total_integrated_heating', Integer)
hs_total_integrated_heating_unit = Column('hs_total_integrated_heating_unit', String,
CheckConstraint("hs_total_integrated_heating_unit IN ('J/m^2')"))
hs_peak_heat_rate = Column('hs_peak_heat_rate', Integer)
hs_peak_heat_rate_unit = Column('hs_peak_heata_rate_unit', String,
CheckConstraint("hs_peak_heata_rate_unit IN ('W/m^2')"))
hs_peak_stagnation_pressure = Column('hs_peak_stagnation_pressure', Integer)
hs_peak_stagnation_pressure_unit = Column('hs_peak_stagnation_pressure_unit', String,
CheckConstraint("hs_peak_stagnation_pressure_unit IN ('Pa','psi')"))
class User(DatabaseModel):
"""Model for a user's account on the site.
Trigger behavior:
Incoming:
- num_unread_notifications will be incremented and decremented by insertions,
deletions, and updates to is_unread in comment_notifications.
- num_unread_messages will be incremented and decremented by insertions,
deletions, and updates to unread_user_ids in message_conversations.
- last_exemplary_label_time will be set when a row for an exemplary label is
inserted into comment_labels.
Internal:
- deleted_time will be set when is_deleted is set to true
- banned_time will be set when is_banned is set to true
"""
schema_class = UserSchema
__tablename__ = "users"
user_id: int = Column(Integer, primary_key=True)
username: str = Column(CIText, nullable=False, unique=True)
password_hash: str = deferred(Column(Text, nullable=False))
email_address_hash: Optional[str] = deferred(Column(Text))
email_address_note: Optional[str] = deferred(
Column(
Text,
CheckConstraint(
f"LENGTH(email_address_note) <= {EMAIL_ADDRESS_NOTE_MAX_LENGTH}",
name="email_address_note_length",
),
))
two_factor_enabled: bool = Column(Boolean,
nullable=False,
server_default="false")
two_factor_secret: Optional[str] = deferred(Column(Text))
two_factor_backup_codes: List[str] = deferred(Column(ARRAY(Text)))
created_time: datetime = Column(
TIMESTAMP(timezone=True),
nullable=False,
index=True,
server_default=text("NOW()"),
)
num_unread_messages: int = Column(Integer,
nullable=False,
server_default="0")
num_unread_notifications: int = Column(Integer,
nullable=False,
server_default="0")
inviter_id: int = Column(Integer, ForeignKey("users.user_id"))
invite_codes_remaining: int = Column(Integer,
nullable=False,
server_default="0")
track_comment_visits: bool = Column(Boolean,
nullable=False,
server_default="false")
collapse_old_comments: bool = Column(Boolean,
nullable=False,
server_default="true")
auto_mark_notifications_read: bool = Column(Boolean,
nullable=False,
server_default="false")
interact_mark_notifications_read: bool = Column(Boolean,
nullable=False,
server_default="true")
open_new_tab_external: bool = Column(Boolean,
nullable=False,
server_default="false")
open_new_tab_internal: bool = Column(Boolean,
nullable=False,
server_default="false")
open_new_tab_text: bool = Column(Boolean,
nullable=False,
server_default="false")
theme_default: str = Column(Text)
is_deleted: bool = Column(Boolean,
nullable=False,
server_default="false",
index=True)
deleted_time: Optional[datetime] = Column(TIMESTAMP(timezone=True))
is_banned: bool = Column(Boolean, nullable=False, server_default="false")
banned_time: Optional[datetime] = Column(TIMESTAMP(timezone=True))
permissions: Any = Column(JSONB(none_as_null=True))
home_default_order: Optional[TopicSortOption] = Column(
ENUM(TopicSortOption))
home_default_period: Optional[str] = Column(Text)
_filtered_topic_tags: List[Ltree] = Column("filtered_topic_tags",
ArrayOfLtree,
nullable=False,
server_default="{}")
comment_label_weight: Optional[float] = Column(REAL)
last_exemplary_label_time: Optional[datetime] = Column(
TIMESTAMP(timezone=True))
_bio_markdown: str = deferred(
Column(
"bio_markdown",
Text,
CheckConstraint(f"LENGTH(bio_markdown) <= {BIO_MAX_LENGTH}",
name="bio_markdown_length"),
))
bio_rendered_html: str = deferred(Column(Text))
@hybrid_property
def filtered_topic_tags(self) -> List[str]:
"""Return the user's list of filtered topic tags."""
return [
str(tag).replace("_", " ") for tag in self._filtered_topic_tags
]
@filtered_topic_tags.setter # type: ignore
def filtered_topic_tags(self, new_tags: List[str]) -> None:
self._filtered_topic_tags = new_tags
@hybrid_property
def bio_markdown(self) -> str:
"""Return the user bio's markdown."""
return self._bio_markdown
@bio_markdown.setter # type: ignore
def bio_markdown(self, new_markdown: str) -> None:
"""Set the user bio's markdown and render its HTML."""
if new_markdown == self.bio_markdown:
return
self._bio_markdown = new_markdown
if self._bio_markdown is not None:
self.bio_rendered_html = convert_markdown_to_safe_html(
new_markdown, HTMLSanitizationContext.USER_BIO)
else:
self.bio_rendered_html = None
def __repr__(self) -> str:
"""Display the user's username and ID as its repr format."""
return f"<User {self.username} ({self.user_id})>"
def __str__(self) -> str:
"""Use the username for the string representation."""
return self.username
def __init__(self, username: str, password: str):
"""Create a new user account."""
self.username = username
self.password = password # type: ignore
def __acl__(self) -> Sequence[Tuple[str, Any, str]]:
"""Pyramid security ACL."""
acl = []
# view:
# - everyone can view all users
acl.append((Allow, Everyone, "view"))
# view_history:
# - only allow logged-in users to look through user history
acl.append((Allow, Authenticated, "view_history"))
# view_info:
# - can't view info (registration date, bio, etc.) for deleted/banned users
# - otherwise, everyone can view
if self.is_banned or self.is_deleted:
acl.append((Deny, Everyone, "view_info"))
acl.append((Allow, Everyone, "view_info"))
# message:
# - deleted and banned users can't be messaged
# - otherwise, logged-in users can message anyone except themselves
if self.is_banned or self.is_deleted:
acl.append((Deny, Everyone, "message"))
acl.append((Deny, self.user_id, "message"))
acl.append((Allow, Authenticated, "message"))
# ban:
# - admins can ban non-deleted users except themselves
if self.is_deleted:
acl.append((Deny, Everyone, "ban"))
acl.append(
(Deny, self.user_id, "ban")) # required so users can't self-ban
acl.append((Allow, "admin", "ban"))
# grant the user all other permissions on themself
acl.append((Allow, self.user_id, ALL_PERMISSIONS))
acl.append(DENY_ALL)
return acl
@property
def password(self) -> NoReturn:
"""Return an error since reading the password isn't possible."""
raise AttributeError("Password is write-only")
@password.setter
def password(self, value: str) -> None:
# need to do manual validation since some password checks depend on checking the
# username at the same time (for similarity)
self.schema.validate({"username": self.username, "password": value})
self.password_hash = hash_string(value)
def is_correct_password(self, password: str) -> bool:
"""Check if the password is correct for this user."""
return is_match_for_hash(password, self.password_hash)
def change_password(self, old_password: str, new_password: str) -> None:
"""Change the user's password from the old one to a new one."""
if not self.is_correct_password(old_password):
raise ValueError("Old password was not correct")
if new_password == old_password:
raise ValueError("New password is the same as old password")
# disable mypy on this line because it doesn't handle setters correctly
self.password = new_password # type: ignore
def is_correct_two_factor_code(self, code: str) -> bool:
"""Verify that a TOTP/backup code is correct."""
totp = TOTP(self.two_factor_secret)
code = code.strip().replace(" ", "").lower()
# some possible user input (such as unicode) can cause an error in the totp
# library, catch that and treat it the same as an invalid code
try:
is_valid_code = totp.verify(code)
except TypeError:
is_valid_code = False
if is_valid_code:
return True
elif self.two_factor_backup_codes and code in self.two_factor_backup_codes:
# Need to set the attribute so SQLAlchemy knows it changed
self.two_factor_backup_codes = [
backup_code for backup_code in self.two_factor_backup_codes
if backup_code != code
]
return True
return False
@property
def email_address(self) -> NoReturn:
"""Return an error since reading the email address isn't possible."""
raise AttributeError("Email address is write-only")
@email_address.setter
def email_address(self, value: Optional[str]) -> None:
"""Set the user's email address (will be stored hashed)."""
if not value:
self.email_address_hash = None
return
# convert the address to lowercase to avoid potential casing issues
value = value.lower()
self.email_address_hash = hash_string(value)
@property
def num_unread_total(self) -> int:
"""Return total number of unread items (notifications + messages)."""
return self.num_unread_messages + self.num_unread_notifications
@property
def auth_principals(self) -> List[str]:
"""Return the user's authorization principals (used for permissions)."""
principals: List[str] = []
# start with any principals manually defined in the permissions column
if not self.permissions:
pass
elif isinstance(self.permissions, str):
principals = [self.permissions]
elif isinstance(self.permissions, list):
principals = self.permissions
else:
raise ValueError("Unknown permissions format")
# give the user the "comment.label" permission if they're over a week old
if utc_now() - self.created_time > timedelta(days=7):
principals.append("comment.label")
return principals
@property
def is_admin(self) -> bool:
"""Return whether the user has admin permissions."""
return "admin" in self.auth_principals
def is_label_available(self, label: CommentLabelOption) -> bool:
"""Return whether the user has a particular label available."""
if label == CommentLabelOption.EXEMPLARY:
if not self.last_exemplary_label_time:
return True
return utc_now() - self.last_exemplary_label_time > timedelta(
hours=8)
return True
class Configuration(DeclarativeBase):
"""Represents a stored configuration for the application"""
__tablename__ = 'config'
__table_args__ = (
ForeignKeyConstraint(
['program_name'],
['programs.name'],
onupdate='cascade', ondelete='set null'),
ForeignKeyConstraint(
['program_name', 'season_number'],
['seasons.program_name', 'seasons.number'],
onupdate='cascade', ondelete='set null'),
{}
)
id = Column(Integer, primary_key=True)
source = Column(Unicode(300), nullable=False, default='/dev/dvd')
target = Column(Unicode(300), nullable=False, default=os.path.expanduser('~/Videos'))
temp = Column(Unicode(300), nullable=False, default=tempfile.gettempdir())
template = Column(Unicode(300), nullable=False, default='{program} - {id} - {name}.mp4')
id_template = Column(Unicode(100), nullable=False, default='{season}x{episode:02d}')
_duration_min = Column('duration_min', Integer, nullable=False, default=40)
_duration_max = Column('duration_max', Integer, nullable=False, default=50)
program_name = Column(Unicode(200))
season_number = Column(Integer)
subtitle_format = Column(Unicode(6),
CheckConstraint("subtitle_format in ('none', 'vobsub', 'cc', 'any')"),
nullable=False, default='none')
audio_mix = Column(Unicode(6),
CheckConstraint("audio_mix in ('mono', 'stereo', 'dpl1', 'dpl2')"),
nullable=False, default='dpl2')
decomb = Column(Unicode(4),
CheckConstraint("decomb in ('off', 'on', 'auto')"),
nullable=False, default='off')
audio_all = Column(Boolean, nullable=False, default=False)
audio_langs = relationship('AudioLanguage', backref='config')
subtitle_all = Column(Boolean, nullable=False, default=False)
subtitle_default = Column(Boolean, nullable=False, default=False)
subtitle_langs = relationship('SubtitleLanguage', backref='config')
video_style = Column(Unicode(10),
CheckConstraint("video_style in ('tv', 'film', 'animation')"),
nullable=False, default='tv')
dvdnav = Column(Boolean, nullable=False, default=True)
duplicates = Column(Unicode(5),
CheckConstraint("duplicates in ('all', 'first', 'last')"),
nullable=False, default='all')
paths = relationship('ConfigPath', backref='config')
program = relationship('Program')
season = relationship('Season',
primaryjoin='and_('
'Season.program_name == Configuration.program_name, '
'Season.number == foreign(Configuration.season_number)'
')')
def _get_duration_min(self):
return timedelta(minutes=self._duration_min)
def _set_duration_min(self, value):
self._duration_min = value.seconds / 60
duration_min = synonym('_duration_min',
descriptor=property(_get_duration_min, _set_duration_min))
def _get_duration_max(self):
return timedelta(minutes=self._duration_max)
def _set_duration_max(self, value):
self._duration_max = value.seconds / 60
duration_max = synonym('_duration_max',
descriptor=property(_get_duration_max, _set_duration_max))
def in_audio_langs(self, lang):
"""Returns True if lang is a selected audio language"""
return any(l.lang == lang for l in self.audio_langs)
def in_subtitle_langs(self, lang):
"""Returns True if lang is a selected subtitle language"""
return any(l.lang == lang for l in self.subtitle_langs)
def get_path(self, name):
"""Returns the configured path of the specified utility"""
session = Session.object_session(self)
return session.query(ConfigPath).\
filter(ConfigPath.config_id == self.id).\
filter(ConfigPath.name == name).one().path
def set_path(self, name, value):
"""Sets the configured path of the specified utility"""
session = Session.object_session(self)
session.query(ConfigPath).\
filter(ConfigPath.config_id == self.id).\
filter(ConfigPath.name == name).one().path = value
session.commit()
def __repr__(self):
return "<Configuration(...)>"
def dervingMut(m_email):
class Widget(QDialog):
def __init__(self, parent=None):
super(Widget, self).__init__(parent)
self.setWindowTitle("Materiaaluitgifte muteren")
self.setWindowIcon(QIcon('./images/logos/logo.jpg'))
self.setFont(QFont('Arial', 10))
self.Dervingnummer = QLabel()
derving = QComboBox()
derving.setFixedWidth(200)
derving.setFont(QFont("Times", 10))
derving.setStyleSheet("color: black; background-color: #F8F7EE")
derving.addItem(' Kies soort Derving')
derving.addItem('1. Incourant')
derving.addItem('2. Magazijn verschillen.')
derving.addItem('3. Beschadiging')
derving.addItem('4. Houdbaarheid')
derving.activated[str].connect(self.dervingChanged)
self.Artikelnummer = QLabel()
artEdit = QLineEdit()
artEdit.setFixedWidth(150)
artEdit.setFont(QFont("Arial",10))
artEdit.textChanged.connect(self.artChanged)
reg_ex = QRegExp("^[2]{1}[0-9]{8}$")
input_validator = QRegExpValidator(reg_ex, artEdit)
artEdit.setValidator(input_validator)
self.Hoeveelheid = QLabel()
hoevEdit = QLineEdit()
hoevEdit.setFixedWidth(150)
hoevEdit.setFont(QFont("Arial",10))
hoevEdit.textChanged.connect(self.hoevChanged)
reg_ex = QRegExp("^[-+]?[0-9]*\.?[0-9]+$")
input_validator = QRegExpValidator(reg_ex, hoevEdit)
hoevEdit.setValidator(input_validator)
grid = QGridLayout()
grid.setSpacing(20)
lbl = QLabel()
pixmap = QPixmap('./images/logos/verbinding.jpg')
lbl.setPixmap(pixmap)
grid.addWidget(lbl ,0, 0)
logo = QLabel()
pixmap = QPixmap('./images/logos/logo.jpg')
logo.setPixmap(pixmap)
grid.addWidget(logo , 0, 2, 1, 1, Qt.AlignRight)
lbl1 = QLabel('Dervingnummer')
lbl1.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
grid.addWidget(lbl1, 1, 0)
grid.addWidget(derving, 1, 1)
lbl2 = QLabel('Artikelnummer')
lbl2.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
grid.addWidget(lbl2, 2, 0)
grid.addWidget(artEdit, 2, 1)
lbl3 = QLabel('Hoeveelheid')
lbl3.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
grid.addWidget(lbl3, 3, 0)
grid.addWidget(hoevEdit, 3 , 1)
self.setLayout(grid)
self.setGeometry(500, 300, 150, 150)
applyBtn = QPushButton('Muteren')
applyBtn.clicked.connect(self.accept)
grid.addWidget(applyBtn, 5, 1, 1 , 2, Qt.AlignRight)
applyBtn.setFont(QFont("Arial",10))
applyBtn.setFixedWidth(100)
applyBtn.setStyleSheet("color: black; background-color: gainsboro")
cancelBtn = QPushButton('Sluiten')
cancelBtn.clicked.connect(lambda: windowSluit(self, m_email))
grid.addWidget(cancelBtn,5, 1, Qt.AlignCenter)
cancelBtn.setFont(QFont("Arial",10))
cancelBtn.setFixedWidth(100)
cancelBtn.setStyleSheet("color: black; background-color: gainsboro")
grid.addWidget(QLabel('\u00A9 2017 all rights reserved [email protected]'), 6, 0, 1, 3, Qt.AlignCenter)
def dervingChanged(self, text):
self.Dervingnummer.setText(text)
def artChanged(self,text):
self.Artikelnummer.setText(text)
def hoevChanged(self,text):
self.Hoeveelheid.setText(text)
def returnderving(self):
return self.Dervingnummer.text()
def returnart(self):
return self.Artikelnummer.text()
def returnhoev(self):
return self.Hoeveelheid.text()
@staticmethod
def getData(parent=None):
dialog = Widget(parent)
dialog.exec_()
return [dialog.returnderving(), dialog.returnart(), dialog.returnhoev()]
window = Widget()
data = window.getData()
mhoev = 0
if not data[0] or data[0][0] == ' ':
ongInvoer()
dervingMut(m_email)
elif data[0][0] == '1':
mderving = '999999990'
momschr = 'Incourant'
elif data[0][0] == '2':
mderving = '999999989'
momschr = 'Magazijn verschillen'
elif data[0][0] == '3':
mderving = '999999977'
momschr = 'Beschadiging'
elif data[0][0] == '4':
mderving = '999999965'
momschr = 'Houdbaarheid'
if data[1] and len(data[1]) == 9 and _11check(data[1]):
martikelnr = int(data[1])
else:
foutInvoer()
dervingMut(m_email)
if data[2]:
mhoev = float(data[2])
else:
foutHoev()
dervingMut(m_email)
metadata = MetaData()
artikelen = Table('artikelen', metadata,
Column('artikelID', Integer(), primary_key=True),
Column('artikelomschrijving', String),
Column('artikelprijs', Float),
Column('art_voorraad', Float, CheckConstraint('art_voorraad >= 0')),
Column('art_min_voorraad', Float),
Column('bestelstatus', Boolean))
derving = Table('derving', metadata,
Column('dervingID', Integer, primary_key=True),
Column('artikelID', None, ForeignKey('artikelen.artikelID')),
Column('categorienummer', None, ForeignKey('werken.werknoID')),
Column('omschrijving', String),
Column('hoeveelheid', Float),
Column('boekdatum', String),
Column('waarde', Float))
engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem')
con = engine.connect()
sel = select([artikelen]).where(artikelen.c.artikelID == martikelnr)
transaction = con.begin()
result = con.execute(sel).first()
if not result:
geenRecord()
dervingMut(m_email)
martprijs = result[2]
martvrd = result[3]
martminvrd = result[4]
mboekd = str(datetime.datetime.now())[0:10]
if martvrd - mhoev <= martminvrd:
martbestst = False
else:
martbestst = True
if mhoev <= 0:
foutHoev()
dervingMut(m_email)
try:
stmt = update(artikelen).where(artikelen.c.artikelID == martikelnr).values(\
art_voorraad = artikelen.c.art_voorraad - mhoev, bestelstatus = martbestst)
con.execute(stmt)
con = engine.connect()
dervingnr=(con.execute(select([func.max(derving.c.dervingID,\
type_=Integer).label('dervingnr')])).scalar())
dervingnr += 1
ins = insert(derving).values(dervingID = dervingnr, artikelID =\
martikelnr, categorienummer = mderving, hoeveelheid = -mhoev, boekdatum = mboekd,\
waarde = mhoev*martprijs, omschrijving = momschr)
con.execute(ins)
transaction.commit()
invoerOK()
dervingMut(m_email)
except IntegrityError:
transaction.rollback()
negVoorraad()
dervingMut(m_email)
con.close
class EngineSettingsTable(BaseModel):
__tablename__ = "engine_settings"
global_lock = Column(Boolean(), default=False, nullable=False, primary_key=True)
running_processes = Column(Integer(), default=0, nullable=False)
__table_args__: tuple = (CheckConstraint(running_processes >= 0, name="check_running_processes_positive"), {})
def og_add_interval(eng, meta, table_name, cols={}, dbsuffix=""):
"""og_add_interval(eng, meta, table_name, cols={}, dbsuffix="")
Create drillhole interval tables in the metadata, eg. assay or log.
You may need the same ``dbsuffix`` used to create the table definitions.
Default culumns BHID, FROM, TO, and Comments
will be automatically created. To add extra columns use ``cols``,
a dictionary with column definition. Two options are available:
a) To add a new column without external reference
{Column1_name:{'coltypes':sqlalchemy.Data_Type, 'nullable': True/False},
Column2_name:{'coltypes':sqlalchemy.Data_Type, 'nullable': True/False},
...
ColumnN_name:{'coltypes':sqlalchemy.Data_Type, 'nullable': True/False}}
b) To add a new column with external reference
{Column1_name:{'coltypes':String,
'nullable': True,
'foreignkey':{'column':reference_table.reference_column,
'ondelete':'RESTRICT',
'onupdate':'CASCADE'}},
...}
Parameters
----------
eng : sqlalchemy engine
active connection to a database
meta : sqlalchemy metadata
container object that keeps together many different features of a database
table_name : str
table name
cols : dict (default {})
definition of new non default columns
Example
-------
>>> og_add_interval(eng, meta,
table_name = 'assay',
dbsuffix="Historic",
cols={'SampleID':{'coltypes':String,
'nullable': False,
'foreignkey':{'column':'assay_certificate.SampleID',
'ondelete':'RESTRICT',
'onupdate':'CASCADE'}},
'Au_visual':{'coltypes':Float, 'nullable': True}})
"""
if dbsuffix != "":
collar_tb_name = dbsuffix + "_collar"
else:
collar_tb_name = "collar"
# create interval table
interval = Table(
table_name, meta,
Column('BHID',
None,
ForeignKey(column=collar_tb_name + '.BHID',
ondelete='CASCADE',
onupdate='CASCADE',
name='chk_bhid'),
primary_key=True),
Column('FROM', Float, nullable=False, primary_key=True),
Column('TO', Float, nullable=False), Column('Comments', String),
CheckConstraint('"TO" > "FROM"', name='check_interv'))
for col in cols:
if 'foreignkey' in cols[col]:
fk = ForeignKey(
column=cols[col]['foreignkey']['column'],
ondelete=cols[col]['foreignkey']['ondelete'],
onupdate=cols[col]['foreignkey']['onupdate'],
)
tmpcol = Column(col, None, fk, nullable=cols[col]['nullable'])
else:
tmpcol = Column(col,
cols[col]['coltypes'],
nullable=cols[col]['nullable'])
interval.append_column(tmpcol)
class Project(SitemapMixin, db.ModelBase):
__tablename__ = "packages"
__table_args__ = (CheckConstraint(
"name ~* '^([A-Z0-9]|[A-Z0-9][A-Z0-9._-]*[A-Z0-9])$'::text",
name="packages_valid_name",
), )
__repr__ = make_repr("name")
name = Column(Text, primary_key=True, nullable=False)
normalized_name = orm.column_property(func.normalize_pep426_name(name))
stable_version = Column(Text)
autohide = Column(Boolean, server_default=sql.true())
comments = Column(Boolean, server_default=sql.true())
bugtrack_url = Column(Text)
hosting_mode = Column(Text, nullable=False, server_default="pypi-only")
created = Column(
DateTime(timezone=False),
nullable=False,
server_default=sql.func.now(),
)
has_docs = Column(Boolean)
upload_limit = Column(Integer, nullable=True)
last_serial = Column(Integer, nullable=False, server_default=sql.text("0"))
allow_legacy_files = Column(
Boolean,
nullable=False,
server_default=sql.false(),
)
users = orm.relationship(
User,
secondary=Role.__table__,
backref="projects",
)
releases = orm.relationship(
"Release",
backref="project",
cascade="all, delete-orphan",
order_by=lambda: Release._pypi_ordering.desc(),
)
def __getitem__(self, version):
session = orm.object_session(self)
try:
return (session.query(Release).filter((Release.project == self) & (
Release.version == version)).one())
except NoResultFound:
raise KeyError from None
def __acl__(self):
session = orm.object_session(self)
acls = []
# Get all of the users for this project.
query = session.query(Role).filter(Role.project == self)
query = query.options(orm.lazyload("project"))
query = query.options(orm.joinedload("user").lazyload("emails"))
for role in sorted(
query.all(),
key=lambda x: ["Owner", "Maintainer"].index(x.role_name)):
acls.append((Allow, role.user.id, ["upload"]))
return acls
@property
def documentation_url(self):
# TODO: Move this into the database and elimnate the use of the
# threadlocal here.
request = get_current_request()
# If the project doesn't have docs, then we'll just return a None here.
if not self.has_docs:
return
return request.route_url("legacy.docs", project=self.name)
class PromoCode(MagModel):
"""
Promo codes used by attendees to purchase badges at discounted prices.
Attributes:
code (str): The actual textual representation of the promo code. This
is what the attendee would have to type in during registration to
receive a discount. `code` may not be an empty string or a string
consisting entirely of whitespace.
discount (int): The discount amount that should be applied to the
purchase price of a badge. The interpretation of this value
depends on the value of `discount_type`. In any case, a value of
0 equates to a full discount, i.e. a free badge.
discount_str (str): A human readable description of the discount.
discount_type (int): The type of discount this promo code will apply.
Valid values are:
* 0 `_FIXED_DISCOUNT`: `discount` is interpreted as a fixed
dollar amount by which the badge price should be reduced. If
`discount` is 49 and the badge price is normally $100, then
the discounted badge price would be $51.
* 1 `_FIXED_PRICE`: `discount` is interpreted as the actual badge
price. If `discount` is 49, then the discounted badge price
would be $49.
* 2 `_PERCENT_DISCOUNT`: `discount` is interpreted as a percentage
by which the badge price should be reduced. If `discount` is
20 and the badge price is normally $50, then the discounted
badge price would $40 ($50 reduced by 20%). If `discount` is
100, then the price would be 100% off, i.e. a free badge.
group (relationship): An optional relationship to a PromoCodeGroup
object, which groups sets of promo codes to make attendee-facing
"groups"
cost (int): The cost of this promo code if and when it was bought
as part of a PromoCodeGroup.
expiration_date (datetime): The date & time upon which this promo code
expires. An expired promo code may no longer be used to receive
discounted badges.
is_free (bool): True if this promo code will always cause a badge to
be free. False if this promo code may not cause a badge to be free.
Note:
It's possible for this value to be False for a promo code that
still reduces a badge's price to zero. If there are some other
discounts that also reduce a badge price (like an age discount)
then the price may be pushed down to zero.
is_expired (bool): True if this promo code is expired, False otherwise.
is_unlimited (bool): True if this promo code may be used an unlimited
number of times, False otherwise.
is_valid (bool): True if this promo code is still valid and may be
used again, False otherwise.
normalized_code (str): A normalized version of `code` suitable for
database queries. Normalization converts `code` to all lowercase
and removes dashes ("-").
used_by (list): List of attendees that have used this promo code.
Note:
This property is declared as a backref in the Attendee class.
uses_allowed (int): The total number of times this promo code may be
used. A value of None means this promo code may be used an
unlimited number of times.
uses_allowed_str (str): A human readable description of
uses_allowed.
uses_count (int): The number of times this promo code has already
been used.
uses_count_str (str): A human readable description of uses_count.
uses_remaining (int): Remaining number of times this promo code may
be used.
uses_remaining_str (str): A human readable description of
uses_remaining.
"""
_FIXED_DISCOUNT = 0
_FIXED_PRICE = 1
_PERCENT_DISCOUNT = 2
_DISCOUNT_TYPE_OPTS = [(_FIXED_DISCOUNT, 'Fixed Discount'),
(_FIXED_PRICE, 'Fixed Price'),
(_PERCENT_DISCOUNT, 'Percent Discount')]
_AMBIGUOUS_CHARS = {
'0': 'OQD',
'1': 'IL',
'2': 'Z',
'5': 'S',
'6': 'G',
'8': 'B'
}
_UNAMBIGUOUS_CHARS = string.digits + string.ascii_uppercase
for _, s in _AMBIGUOUS_CHARS.items():
_UNAMBIGUOUS_CHARS = re.sub('[{}]'.format(s), '', _UNAMBIGUOUS_CHARS)
code = Column(UnicodeText)
discount = Column(Integer, nullable=True, default=None)
discount_type = Column(Choice(_DISCOUNT_TYPE_OPTS),
default=_FIXED_DISCOUNT)
expiration_date = Column(UTCDateTime, default=c.ESCHATON)
uses_allowed = Column(Integer, nullable=True, default=None)
cost = Column(Integer, nullable=True, default=None)
group_id = Column(UUID,
ForeignKey('promo_code_group.id', ondelete='SET NULL'),
nullable=True)
group = relationship(PromoCodeGroup,
backref='promo_codes',
foreign_keys=group_id,
cascade='save-update,merge,refresh-expire,expunge')
__table_args__ = (Index('uq_promo_code_normalized_code',
func.replace(
func.replace(func.lower(code), '-', ''), ' ',
''),
unique=True),
CheckConstraint(func.trim(code) != '',
name='ck_promo_code_non_empty_code'))
_repr_attr_names = ('code', )
@classmethod
def normalize_expiration_date(cls, dt):
"""
Converts the given datetime to 11:59pm local in the event timezone.
"""
if isinstance(dt, six.string_types):
if dt.strip():
dt = dateparser.parse(dt)
else:
dt = c.ESCHATON
if dt.tzinfo:
dt = dt.astimezone(c.EVENT_TIMEZONE)
return c.EVENT_TIMEZONE.localize(
dt.replace(hour=23, minute=59, second=59, tzinfo=None))
@property
def discount_str(self):
if self.discount_type == self._FIXED_DISCOUNT and self.discount == 0:
# This is done to account for Art Show Agent codes, which use the PromoCode class
return 'No discount'
elif not self.discount:
return 'Free badge'
if self.discount_type == self._FIXED_DISCOUNT:
return '${} discount'.format(self.discount)
elif self.discount_type == self._FIXED_PRICE:
return '${} badge'.format(self.discount)
else:
return '%{} discount'.format(self.discount)
@hybrid_property
def is_expired(self):
return self.expiration_date < localized_now()
@is_expired.expression
def is_expired(cls):
return cls.expiration_date < localized_now()
@property
def is_free(self):
return not self.discount or (
self.discount_type == self._PERCENT_DISCOUNT
and self.discount >= 100) or (self.discount_type
== self._FIXED_DISCOUNT
and self.discount >= c.BADGE_PRICE)
@hybrid_property
def is_unlimited(self):
return not self.uses_allowed
@is_unlimited.expression
def is_unlimited(cls):
return cls.uses_allowed == None # noqa: E711
@hybrid_property
def is_valid(self):
return not self.is_expired and (self.is_unlimited
or self.uses_remaining > 0)
@is_valid.expression
def is_valid(cls):
return (cls.expiration_date >= localized_now()) \
& ((cls.uses_allowed == None) | (cls.uses_remaining > 0)) # noqa: E711
@hybrid_property
def normalized_code(self):
return self.normalize_code(self.code)
@normalized_code.expression
def normalized_code(cls):
return func.replace(func.replace(func.lower(cls.code), '-', ''), ' ',
'')
@property
def uses_allowed_str(self):
uses = self.uses_allowed
return 'Unlimited uses' if uses is None else '{} use{} allowed'.format(
uses, '' if uses == 1 else 's')
@hybrid_property
def uses_count(self):
return len(self.used_by)
@uses_count.expression
def uses_count(cls):
from uber.models.attendee import Attendee
return select([
func.count(Attendee.id)
]).where(Attendee.promo_code_id == cls.id).label('uses_count')
@property
def uses_count_str(self):
uses = self.uses_count
return 'Used by {} attendee{}'.format(uses, '' if uses == 1 else 's')
@hybrid_property
def uses_remaining(self):
return None if self.is_unlimited else self.uses_allowed - self.uses_count
@uses_remaining.expression
def uses_remaining(cls):
return cls.uses_allowed - cls.uses_count
@property
def uses_remaining_str(self):
uses = self.uses_remaining
return 'Unlimited uses' if uses is None else '{} use{} remaining'.format(
uses, '' if uses == 1 else 's')
@presave_adjustment
def _attribute_adjustments(self):
# If 'uses_allowed' is empty, then this is an unlimited use code
if not self.uses_allowed:
self.uses_allowed = None
# If 'discount' is empty, then this is a full discount, free badge
if self.discount == '':
self.discount = None
self.code = self.code.strip() if self.code else ''
if not self.code:
# If 'code' is empty, then generate a random code
self.code = self.generate_random_code()
else:
# Replace multiple whitespace characters with a single space
self.code = re.sub(r'\s+', ' ', self.code)
# Always make expiration_date 11:59pm of the given date
self.expiration_date = self.normalize_expiration_date(
self.expiration_date)
def calculate_discounted_price(self, price):
"""
Returns the discounted price based on the promo code's `discount_type`.
Args:
price (int): The badge price in whole dollars.
Returns:
int: The discounted price. The returned number will never be
less than zero or greater than `price`. If `price` is None
or a negative number, then the return value will always be 0.
"""
if not self.discount or not price or price < 0:
return 0
discounted_price = price
if self.discount_type == self._FIXED_DISCOUNT:
discounted_price = price - self.discount
elif self.discount_type == self._FIXED_PRICE:
discounted_price = self.discount
elif self.discount_type == self._PERCENT_DISCOUNT:
discounted_price = int(price * ((100.0 - self.discount) / 100.0))
return min(max(discounted_price, 0), price)
@classmethod
def _generate_code(cls, generator, count=None):
"""
Helper method to limit collisions for the other generate() methods.
Arguments:
generator (callable): Function that returns a newly generated code.
count (int): The number of codes to generate. If `count` is `None`,
then a single code will be generated. Defaults to `None`.
Returns:
If an `int` value was passed for `count`, then a `list` of newly
generated codes is returned. If `count` is `None`, then a single
`str` is returned.
"""
from uber.models import Session
with Session() as session:
# Kind of inefficient, but doing one big query for all the existing
# codes will be faster than a separate query for each new code.
old_codes = set(s for (s, ) in session.query(cls.code).all())
# Set an upper limit on the number of collisions we'll allow,
# otherwise this loop could potentially run forever.
max_collisions = 100
collisions = 0
codes = set()
while len(codes) < (1 if count is None else count):
code = generator().strip()
if not code:
break
if code in codes or code in old_codes:
collisions += 1
if collisions >= max_collisions:
break
else:
codes.add(code)
return (codes.pop() if codes else None) if count is None else codes
@classmethod
def generate_random_code(cls, count=None, length=9, segment_length=3):
"""
Generates a random promo code.
With `length` = 12 and `segment_length` = 3::
XXX-XXX-XXX-XXX
With `length` = 6 and `segment_length` = 2::
XX-XX-XX
Arguments:
count (int): The number of codes to generate. If `count` is `None`,
then a single code will be generated. Defaults to `None`.
length (int): The number of characters to use for the code.
segment_length (int): The length of each segment within the code.
Returns:
If an `int` value was passed for `count`, then a `list` of newly
generated codes is returned. If `count` is `None`, then a single
`str` is returned.
"""
# The actual generator function, called repeatedly by `_generate_code`
def _generate_random_code():
letters = ''.join(
random.choice(cls._UNAMBIGUOUS_CHARS) for _ in range(length))
return '-'.join(textwrap.wrap(letters, segment_length))
return cls._generate_code(_generate_random_code, count=count)
@classmethod
def generate_word_code(cls, count=None):
"""
Generates a promo code consisting of words from `PromoCodeWord`.
Arguments:
count (int): The number of codes to generate. If `count` is `None`,
then a single code will be generated. Defaults to `None`.
Returns:
If an `int` value was passed for `count`, then a `list` of newly
generated codes is returned. If `count` is `None`, then a single
`str` is returned.
"""
from uber.models import Session
with Session() as session:
words = PromoCodeWord.group_by_parts_of_speech(
session.query(PromoCodeWord).order_by(
PromoCodeWord.normalized_word).all())
# The actual generator function, called repeatedly by `_generate_code`
def _generate_word_code():
code_words = []
for part_of_speech, _ in PromoCodeWord._PART_OF_SPEECH_OPTS:
if words[part_of_speech]:
code_words.append(random.choice(words[part_of_speech]))
return ' '.join(code_words)
return cls._generate_code(_generate_word_code, count=count)
@classmethod
def disambiguate_code(cls, code):
"""
Removes ambiguous characters in a promo code supplied by an attendee.
Arguments:
code (str): A promo code as typed by an attendee.
Returns:
str: A copy of `code` with all ambiguous characters replaced by
their unambiguous equivalent.
"""
code = cls.normalize_code(code)
if not code:
return ''
for unambiguous, ambiguous in cls._AMBIGUOUS_CHARS.items():
ambiguous_pattern = '[{}]'.format(ambiguous.lower())
code = re.sub(ambiguous_pattern, unambiguous.lower(), code)
return code
@classmethod
def normalize_code(cls, code):
"""
Normalizes a promo code supplied by an attendee.
Arguments:
code (str): A promo code as typed by an attendee.
Returns:
str: A copy of `code` converted to all lowercase, with dashes ("-")
and whitespace characters removed.
"""
if not code:
return ''
return re.sub(r'[\s\-]+', '', code.lower())
def _create_layer(
self,
public=False,
none_area=False,
attr_list=False,
exclude_properties=False,
metadatas=None,
geom_type=False,
):
"""This function is central for this test class. It creates
a layer with two features, and associates a restriction area
to it."""
import transaction
from geoalchemy2 import Geometry, WKTElement
from sqlalchemy import CheckConstraint, Column, ForeignKey, Table, types
from sqlalchemy.ext.declarative import declarative_base
from c2cgeoportal_commons.models import DBSession
from c2cgeoportal_commons.models.main import LayerWMS, OGCServer, RestrictionArea
if self._tables is None:
self._tables = []
self.__class__._table_index += 1
id = self.__class__._table_index
engine = DBSession.c2c_rw_bind
connection = engine.connect()
if not self.metadata:
self.metadata = declarative_base(bind=engine).metadata
tablename = "table_{0:d}".format(id)
table1 = Table(
"{0!s}_child".format(tablename),
self.metadata,
Column("id", types.Integer, primary_key=True),
Column("name", types.Unicode),
schema="public",
)
if geom_type:
table1.append_column(Column("geom", Geometry("POINT", srid=21781)))
else:
table1.append_column(Column("geom", Geometry(srid=21781)))
self._tables.append(table1)
table2 = Table(
tablename,
self.metadata,
Column("id", types.Integer, primary_key=True),
Column("child_id", types.Integer,
ForeignKey("public.{0!s}_child.id".format(tablename))),
Column("name", types.Unicode),
Column(
"email",
types.Unicode,
CheckConstraint(
"""email ~* '^[A-Za-z0-9._%%-]
+@[A-Za-z0-9.-]+[.][A-Za-z]+$'""",
name="proper_email",
),
),
Column("last_update_user", types.Unicode),
Column("last_update_date", types.DateTime),
schema="public",
)
if geom_type:
table2.append_column(Column("geom", Geometry("POINT", srid=21781)))
else:
table2.append_column(Column("geom", Geometry(srid=21781)))
self._tables.append(table2)
table1.drop(checkfirst=True)
table2.drop(checkfirst=True)
table1.create()
table2.create()
ins = table1.insert().values(name="c1é")
c1_id = connection.execute(ins).inserted_primary_key[0]
ins = table1.insert().values(name="c2é")
c2_id = connection.execute(ins).inserted_primary_key[0]
ins = table2.insert().values(child_id=c1_id,
name="foo",
geom=WKTElement("POINT(5 45)", 21781))
connection.execute(ins)
ins = table2.insert().values(child_id=c2_id,
name="bar",
geom=WKTElement("POINT(6 46)", 21781))
connection.execute(ins)
if attr_list:
ins = table2.insert().values(child_id=c2_id,
name="aaa,bbb,foo",
geom=WKTElement("POINT(6 46)", 21781))
connection.execute(ins)
ogc_server = DBSession.query(OGCServer).filter(
OGCServer.name == "__test_ogc_server").one()
layer = LayerWMS()
layer.id = id
layer.name = str(id)
layer.ogc_server = ogc_server
layer.geo_table = tablename
layer.public = public
layer.interface = [self.main]
if exclude_properties:
layer.exclude_properties = "name"
if metadatas:
layer.metadatas = metadatas
DBSession.add(layer)
if not public:
ra = RestrictionArea()
ra.name = "__test_ra"
ra.layers = [layer]
ra.roles = [self.role]
ra.readwrite = True
if not none_area:
poly = "POLYGON((4 44, 4 46, 6 46, 6 44, 4 44))"
ra.area = WKTElement(poly, srid=21781)
DBSession.add(ra)
transaction.commit()
self.layer_ids.append(id)
return id
class VariableUsage(AlchemyProxy):
"""Represent a variable usage during program slicing"""
__tablename__ = "variable_usage"
__table_args__ = (
PrimaryKeyConstraint("trial_id", "id"),
ForeignKeyConstraint(["trial_id"],
["trial.id"], ondelete="CASCADE"),
ForeignKeyConstraint(["trial_id", "activation_id"],
["function_activation.trial_id",
"function_activation.id"], ondelete="CASCADE"),
ForeignKeyConstraint(["trial_id", "activation_id", "variable_id"],
["variable.trial_id",
"variable.activation_id",
"variable.id"], ondelete="CASCADE"),
)
trial_id = Column(Integer, index=True)
activation_id = Column(Integer, index=True)
variable_id = Column(Integer, index=True)
id = Column(Integer, index=True) # pylint: disable=invalid-name
line = Column(Integer)
context = Column(Text, CheckConstraint("context IN ('Load', 'Del')"))
trial = backref_one("trial") # Trial.variable_usages
activation = backref_one("activation") # Activation.variables_usages
variable = backref_one("variable") # Variable.usages
prolog_description = PrologDescription("usage", (
PrologTrial("trial_id", link="variable.trial_id"),
PrologAttribute("activation_id", link="variable.activation_id"),
PrologAttribute("variable_id", link="variable.id"),
PrologAttribute("id"),
PrologRepr("name", attr_name="variable.name"),
PrologAttribute("line"),
), description=(
"informs that in a given trial (*trial_id*),\n"
"during a specific function activation (*activation_id*),\n"
"in a specific *line* of code,\n"
"a variable *name* was accessed (read, delete)."
))
def __init__(self, *args, **kwargs):
if args and isinstance(args[0], relational.base):
obj = args[0]
trial_ref = obj.id
elif args:
trial_ref = kwargs.get("trial_ref", args[0])
else:
trial_ref = kwargs.get("trial_ref", None)
session = relational.session
obj = VariableUsage.load_variableusage(trial_ref, session=session)
if obj is not None:
super(VariableUsage, self).__init__(obj)
def __repr__(self):
name = self.variable.name
return (
"VariableUsage({self.trial_id}, {self.activation_id}, "
"{self.variable_id}, {self.id}, {name}, {self.line}, "
"{self.context})"
).format(**locals())
def __str__(self):
name = self.variable.name
return "(L{self.line}, {name}, <{self.context}>)".format(**locals())
@classmethod # query
def load_variableusage(cls, trial_ref, session=None):
"""Load variableusage by variableusage reference
Find reference on trials id and tags name
"""
session = session or relational.session
result = session.query(cls.m).filter(cls.m.trial_id == trial_ref)
return result.first()
def pull_content(cls, tid, session=None):
session = session or relational.session
ttrial = cls.__table__
result = session.query(ttrial).filter(ttrial.c.trial_id == tid).all()
return result
def push_content(cls, id, reslist, session=None):
session = session or relational.session
ttrial = cls.__table__
for res in reslist:
result = session.execute(
ttrial.insert(),
{"trial_id": id, "activation_id": res.activation_id, "variable_id": res.variable_id,"id": res.id, "line": res.line, "context": res.context}
)
session.commit()
DateTime, delete, ForeignKey, insert, Integer,
MetaData, Numeric, select, String, Table, update)
from sqlalchemy.exc import IntegrityError
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),
from sqlalchemy.dialects.postgresql import INET
from sqlalchemy.dialects.postgresql import UUID
meta = MetaData()
customer_table = Table('customer', meta, Column('id',
Integer,
primary_key=True),
Column('name', String), Column('email', String),
Column('verified', Boolean), UniqueConstraint('email'))
order_table = Table(
'order', meta, Column('id', Integer, primary_key=True),
Column('customer_id', Integer, ForeignKey('customer.id')),
Column('info', String),
Column('status', String, CheckConstraint("status in ('open', 'closed')")))
# Regression test for https://github.com/cockroachdb/cockroach/issues/26993
index_table = Table('index', meta, Column('index', Integer, primary_key=True))
view_table = Table('view', meta, Column('view', Integer, primary_key=True))
class IntrospectionTest(fixtures.TestBase):
def teardown_method(self, method):
meta.drop_all(testing.db)
def setup_method(self):
meta.create_all(testing.db)
def test_create_metadata(self):
# Create a metadata via introspection on the live DB.
def tables_for_meta(meta):
Table('global', meta, Column('key', TEXT, primary_key=True),
Column('value', TEXT, nullable=True))
Table(
'branches', meta,
Column('branch',
TEXT,
ForeignKey('branches.parent'),
primary_key=True,
default='trunk'),
Column('parent', TEXT, default='trunk', nullable=True),
Column('parent_turn', INT, default=0),
Column('parent_tick', INT, default=0),
Column('end_turn', INT, default=0), Column('end_tick', INT, default=0),
CheckConstraint('branch<>parent'))
Table('turns', meta, Column('branch', TEXT, primary_key=True),
Column('turn', INT, primary_key=True), Column('end_tick', INT),
Column('plan_end_tick', INT))
Table(
'graphs', meta, Column('graph', TEXT, primary_key=True),
Column('type', TEXT, default='Graph'),
CheckConstraint(
"type IN ('Graph', 'DiGraph', 'MultiGraph', 'MultiDiGraph')"))
Table(
'keyframes', meta,
Column('graph', TEXT, ForeignKey('graphs.graph'), primary_key=True),
Column('branch',
TEXT,
ForeignKey('branches.branch'),
primary_key=True,
default='trunk'),
Column('turn', INT, primary_key=True, default=0),
Column('tick', INT, primary_key=True,
default=0), Column('nodes', TEXT), Column('edges', TEXT),
Column('graph_val', TEXT))
Table(
'graph_val', meta,
Column('graph', TEXT, ForeignKey('graphs.graph'), primary_key=True),
Column('key', TEXT, primary_key=True),
Column('branch',
TEXT,
ForeignKey('branches.branch'),
primary_key=True,
default='trunk'),
Column('turn', INT, primary_key=True, default=0),
Column('tick', INT, primary_key=True, default=0),
Column('value', TEXT, nullable=True))
Table(
'nodes', meta,
Column('graph', TEXT, ForeignKey('graphs.graph'), primary_key=True),
Column('node', TEXT, primary_key=True),
Column('branch',
TEXT,
ForeignKey('branches.branch'),
primary_key=True,
default='trunk'),
Column('turn', INT, primary_key=True, default=0),
Column('tick', INT, primary_key=True, default=0),
Column('extant', BOOLEAN))
Table(
'node_val', meta, Column('graph', TEXT, primary_key=True),
Column('node', TEXT, primary_key=True),
Column('key', TEXT, primary_key=True),
Column('branch',
TEXT,
ForeignKey('branches.branch'),
primary_key=True,
default='trunk'),
Column('turn', INT, primary_key=True, default=0),
Column('tick', INT, primary_key=True, default=0),
Column('value', TEXT, nullable=True),
ForeignKeyConstraint(['graph', 'node'], ['nodes.graph', 'nodes.node']))
Table(
'edges', meta,
Column('graph', TEXT, ForeignKey('graphs.graph'), primary_key=True),
Column('orig', TEXT, primary_key=True),
Column('dest', TEXT, primary_key=True),
Column('idx', INT, primary_key=True),
Column('branch',
TEXT,
ForeignKey('branches.branch'),
primary_key=True,
default='trunk'),
Column('turn', INT, primary_key=True, default=0),
Column('tick', INT, primary_key=True, default=0),
Column('extant', BOOLEAN),
ForeignKeyConstraint(['graph', 'orig'], ['nodes.graph', 'nodes.node']),
ForeignKeyConstraint(['graph', 'dest'], ['nodes.graph', 'nodes.node']))
Table(
'edge_val', meta, Column('graph', TEXT, primary_key=True),
Column('orig', TEXT, primary_key=True),
Column('dest', TEXT, primary_key=True),
Column('idx', INT, primary_key=True),
Column('key', TEXT, primary_key=True),
Column('branch',
TEXT,
ForeignKey('branches.branch'),
primary_key=True,
default='trunk'),
Column('turn', INT, primary_key=True, default=0),
Column('tick', INT, primary_key=True, default=0),
Column('value', TEXT, nullable=True),
ForeignKeyConstraint(
['graph', 'orig', 'dest', 'idx'],
['edges.graph', 'edges.orig', 'edges.dest', 'edges.idx']))
Table('plans', meta, Column('id', INT, primary_key=True),
Column('branch', TEXT), Column('turn', INT), Column('tick', INT))
Table('plan_ticks', meta, Column('plan_id', INT, primary_key=True),
Column('turn', INT, primary_key=True),
Column('tick', INT, primary_key=True),
ForeignKeyConstraint(('plan_id', ), ('plans.id', )))
return meta.tables
def _sql_ck_fixture(self, table_args=(), table_kwargs={}):
m = MetaData()
t = Table('tname', m, Column('id', Integer, primary_key=True),
Column('email', String()))
t.append_constraint(CheckConstraint(t.c.email.like('%@%')))
return ApplyBatchImpl(t, table_args, table_kwargs)
default=datetime.now,
onupdate=datetime.now))
orders = Table('orders', metadata,
Column('order_id', Integer(), primary_key=True),
Column('user_id', ForeignKey('users.user_id')),
Column('shipped', Boolean(), default=False))
line_items = Table('line_items', metadata,
Column('line_items_id', Integer(), primary_key=True),
Column('order_id', ForeignKey('orders.order_id')),
Column('cookie_id', ForeignKey('cookies.cookie_id')),
Column('quantity', Integer()),
Column('extended_cost', Numeric(12, 2)))
CheckConstraint('unit_cost >= 0.00', name='unit_cost_positive')
Index('ix_cookies_cookie_name', 'cookie_name')
metadata.create_all(engine)
columns = [
orders.c.order_id, users.c.username, users.c.phone, cookies.c.cookie_name,
line_items.c.quantity, line_items.c.extended_cost
]
cookiemon_orders = select(columns)
cookiemon_orders = cookiemon_orders.select_from(
orders.join(users).join(line_items).join(cookies)).where(
users.c.username == 'cookiemon')
result = connection.execute(cookiemon_orders).fetchall()
for row in result:
print(row)
class PromoCodeWord(MagModel):
"""
Words used to generate promo codes.
Attributes:
word (str): The text of this promo code word.
normalized_word (str): A normalized version of `word`, suitable for
database queries.
part_of_speech (int): The part of speech that `word` is.
Valid values are:
* 0 `_ADJECTIVE`: `word` is an adjective
* 1 `_NOUN`: `word` is a noun
* 2 `_VERB`: `word` is a verb
* 3 `_ADVERB`: `word` is an adverb
part_of_speech_str (str): A human readable description of
`part_of_speech`.
"""
_ADJECTIVE = 0
_NOUN = 1
_VERB = 2
_ADVERB = 3
_PART_OF_SPEECH_OPTS = [(_ADJECTIVE, 'adjective'), (_NOUN, 'noun'),
(_VERB, 'verb'), (_ADVERB, 'adverb')]
_PARTS_OF_SPEECH = dict(_PART_OF_SPEECH_OPTS)
word = Column(UnicodeText)
part_of_speech = Column(Choice(_PART_OF_SPEECH_OPTS), default=_ADJECTIVE)
__table_args__ = (Index(
'uq_promo_code_word_normalized_word_part_of_speech',
func.lower(func.trim(word)),
part_of_speech,
unique=True),
CheckConstraint(
func.trim(word) != '',
name='ck_promo_code_word_non_empty_word'))
_repr_attr_names = ('word', )
@hybrid_property
def normalized_word(self):
return self.normalize_word(self.word)
@normalized_word.expression
def normalized_word(cls):
return func.lower(func.trim(cls.word))
@property
def part_of_speech_str(self):
return self._PARTS_OF_SPEECH[self.part_of_speech].title()
@presave_adjustment
def _attribute_adjustments(self):
# Replace multiple whitespace characters with a single space
self.word = re.sub(r'\s+', ' ', self.word.strip())
@classmethod
def group_by_parts_of_speech(cls, words):
"""
Groups a list of words by their part_of_speech.
Arguments:
words (list): List of `PromoCodeWord`.
Returns:
OrderedDict: A dictionary of words mapped to their part of speech,
like this::
OrderedDict([
(0, ['adjective1', 'adjective2']),
(1, ['noun1', 'noun2']),
(2, ['verb1', 'verb2']),
(3, ['adverb1', 'adverb2'])
])
"""
parts_of_speech = OrderedDict([
(i, []) for (i, _) in PromoCodeWord._PART_OF_SPEECH_OPTS
])
for word in words:
parts_of_speech[word.part_of_speech].append(word.word)
return parts_of_speech
@classmethod
def normalize_word(cls, word):
"""
Normalizes a word.
Arguments:
word (str): A word as typed by an admin.
Returns:
str: A copy of `word` converted to all lowercase, and multiple
whitespace characters replaced by a single space.
"""
return re.sub(r'\s+', ' ', word.strip().lower())
class Booking(Base):
account_id = db.Column(db.Integer,
db.ForeignKey("account.id"),
nullable=False,
index=True)
resource_id = db.Column(db.Integer,
db.ForeignKey("resource.id"),
nullable=False,
index=True)
start_dt = db.Column(db.DateTime, nullable=False, index=True)
end_dt = db.Column(db.DateTime, nullable=False, index=True)
price = db.Column(db.Numeric,
db.CheckConstraint("price >= 0"),
nullable=False)
__table_args__ = (CheckConstraint("start_dt < end_dt",
name="time_direction"), )
def __init__(self, account_id, resource_id, start_dt, end_dt):
self.account_id = account_id
self.resource_id = resource_id
self.start_dt = start_dt
self.end_dt = end_dt
self.calculate_price()
def __str__(self):
start_dt = check_type_dt(self.start_dt)
start_date = start_dt.strftime("%Y-%m-%d")
return (PRICE + ", %s, %s, %s") % (self.price, self.account,
self.resource, start_date)
def str_no_account(self):
start_dt = check_type_dt(self.start_dt).strftime("%Y-%m-%d %H:%M")
return (PRICE + ", %s, %s") % (self.price, self.resource, start_dt)
def start_date_str(self):
return check_type_dt(self.start_dt).strftime("%Y-%m-%d")
def start_time_str(self):
return check_type_dt(self.start_dt).strftime("%H:%M")
def end_date_str(self):
return check_type_dt(self.end_dt).strftime("%Y-%m-%d")
def end_time_str(self):
return check_type_dt(self.end_dt).strftime("%H:%M")
def price_str(self):
return PRICE % self.price
def calculate_price(self):
time_span = self.end_dt - self.start_dt
hours = Decimal(time_span.total_seconds()) / 3600
price = Resource.query.get(self.resource_id).price * hours
self.price = price.quantize(Decimal('.01'), rounding=ROUND_DOWN)
@staticmethod
def is_free_time_slot(b):
query = ("SELECT id FROM booking "
"WHERE resource_id = :resource_id "
"AND end_dt > :start_dt "
"AND start_dt < :end_dt")
stmt = (text(query + " AND id <> :booking_id").params(
booking_id=b.id,
resource_id=b.resource_id,
start_dt=b.start_dt,
end_dt=b.end_dt)
if b.id else text(query).params(resource_id=b.resource_id,
start_dt=b.start_dt,
end_dt=b.end_dt))
return not db.engine.execute(stmt).fetchone()
@staticmethod
def get_allowed_by_account(invoice_id=None):
if not current_user.is_authenticated:
return []
query = ("SELECT * FROM booking WHERE (account_id IN "
"(SELECT account.id FROM account "
"INNER JOIN admin "
"ON account.community_id = admin.community_id "
"WHERE admin.account_id = :user_id) "
"OR account_id = :user_id) "
"AND id NOT IN "
"(SELECT booking_id FROM invoice_booking")
params = {"user_id": current_user.get_id()}
if invoice_id:
query += " WHERE invoice_id <> :invoice_id"
params["invoice_id"] = invoice_id
query += ") ORDER BY start_dt"
stmt = text(query).params(params)
return db.session.query(Booking).from_statement(stmt).all()
@staticmethod
def get_allowed_by_resource(from_dt=None,
to_dt=None,
resource_ids=None,
filter_not_in_invoice=False):
if not current_user.is_authenticated:
return []
query = ("SELECT * FROM booking WHERE (resource_id IN "
"(SELECT DISTINCT community_resource.resource_id "
"FROM community_resource INNER JOIN admin "
"ON admin.community_id = community_resource.community_id "
"WHERE admin.account_id = :user_id) "
"OR resource_id IN "
"(SELECT community_resource.resource_id "
"FROM community_resource INNER JOIN account "
"ON account.community_id = community_resource.community_id "
"WHERE account.id = :user_id))")
params = {"user_id": current_user.get_id()}
if from_dt:
query += " AND end_dt > :from_dt"
params["from_dt"] = from_dt
if to_dt:
query += " AND start_dt < :to_dt"
params["to_dt"] = to_dt
if resource_ids:
query += " AND resource_id IN (:resource_id_0"
params["resource_id_0"] = resource_ids[0]
list_length = len(resource_ids)
if list_length > 1:
for i in range(1, list_length):
query += ", :resource_id_%d" % i
params["resource_id_%d" % i] = resource_ids[i]
query += ")"
if filter_not_in_invoice:
query += (" AND id NOT IN "
"(SELECT DISTINCT booking_id FROM invoice_booking)")
query += " ORDER BY start_dt"
stmt = text(query).params(params)
return db.session.query(Booking).from_statement(stmt).all()
class Battle(db.Model):
__tablename__ = 'battles'
id = db.Column(db.Integer, primary_key=True)
creator_id = db.Column(db.Integer, db.ForeignKey('users.id'), index=True)
creator = db.relationship('User',
uselist=False,
backref=db.backref("battles"))
name = db.Column(db.String(64), index=True, nullable=False)
description = db.Column(db.Text())
created_on = db.Column(db.DateTime, default=datetime.now)
latitude = db.Column(
db.Float,
CheckConstraint('(-90 <= latitude) AND (latitude <= 90)'),
nullable=False)
longitude = db.Column(
db.Float,
CheckConstraint('(-180 <= longitude) AND (longitude <= 180)'),
nullable=False)
radius = db.Column(db.Integer, nullable=False,
server_default="100") # FIXME no default
@hybrid_method
def distance_to(self, latitude, longitude):
from haversine import haversine
return haversine((latitude, longitude),
(self.latitude, self.longitude)) * 1000
@staticmethod
def get_in_radius(latitude, longitude, radius):
"""
:param longitude: longitude of the center point
:param latitude: latitude of the center point
:param radius: in meters
:return: List of battles in given radius from (latitude, longitude)
"""
return db.session.query(Battle).filter(
Battle.distance_to(latitude, longitude) < radius).all()
@staticmethod
def get_list(count=None):
"""
Return all battles if count is not provided,
otherwise only given number of battles.
:param count: Number of battles to get.
:return: List of Battle objects.
"""
if not count:
battles = Battle.query.all()
else:
battles = Battle.query.limit(count).all()
return battles
def get_entries(self, count=None):
"""
Get all entries of the battle.
:return: List of Entry objects.
"""
query = Entry.query.filter_by(battle_id=self.id).order_by(
desc(Entry._rating))
if count:
return query.limit(count).all()
else:
return query.all()
def get_voting(self, user_id):
"""
Get two entries to vote using sophisticated algorithm.
:return: Tuple of two entries if conditions are met. Otherwise false.
"""
entries = [e for e in self.get_entries() if e.user_id != user_id]
user = User.get_or_404(user_id=user_id)
if len(entries) >= 2:
entry1 = random.choice(entries)
entry2 = random.choice(entries)
rating1 = entry1.rating
rating2 = entry2.rating
impatience = 0
while entry1.id == entry2.id or quality_1vs1(rating1, rating2) < 0.3 - impatience or \
(user.is_voted(entry1.battle_id, entry1.id, entry2.id) and impatience < 1): # FIXME
entry1 = random.choice(entries)
entry2 = random.choice(entries)
impatience += 0.01
if not user.is_voted(entry1.battle_id, entry1.id,
entry2.id) and not entry1.id == entry2.id:
return entry1, entry2
return None
def __repr__(self):
return "<Battle {}>".format(self.id)
class CalculationDefaultSettings(Base):
"""Default settings for calculations.
The settings are hard-linked to code and test and
optionally linked to other objects of a calculation,
acting as conditions for which to use the respective setting.
Since NULL is not comparable (NULL == NULL = False), we coalesce NULL values in
the structure and structure set columns to a 0 UUID, a 0 respectively before the
uniqueness constraint is applied. Even if improbable, we further ensure that the
structure and structure set columns can not contain that 0 UUID/0 integer.
"""
id = UUIDPKColumn()
priority = Column(Integer, nullable=False, server_default='0')
code_id = Column(UUID(as_uuid=True), ForeignKey('code.id'), nullable=False)
code = relationship("Code", backref="default_settings")
test_id = Column(Integer, ForeignKey('test.id'), nullable=False)
test = relationship("Test", backref="default_settings")
structure_id = Column(UUID(as_uuid=True), ForeignKey('structure.id'))
structure = relationship("Structure", backref="default_settings")
structure_set_id = Column(Integer, ForeignKey('structure_set.id'))
structure_set = relationship("StructureSet", backref="default_settings")
basis_set_id = Column(UUID(as_uuid=True), ForeignKey('basis_set.id'))
basis_set = relationship("BasisSet", backref="default_settings")
basis_set_family_id = Column(Integer, ForeignKey('basis_set_family.id'))
basis_set_family = relationship("BasisSetFamily", backref="default_settings")
pseudopotential_id = Column(UUID(as_uuid=True), ForeignKey('pseudopotential.id'))
pseudopotential = relationship("Pseudopotential", backref="default_settings")
pseudopotential_family_id = Column(Integer, ForeignKey('pseudopotential_family.id'))
pseudopotential_family = relationship("PseudopotentialFamily", backref="default_settings")
settings = Column(JSONB)
constraint_attrs = [
"code", "test",
"structure", "structure_set",
"basis_set", "basis_set_family",
"pseudopotential", "pseudopotential_family",
]
__table_args__ = (
Index('unique_calculation_default_settings_idx',
priority,
code_id,
test_id,
coalesce(structure_id, str(uuid.UUID(int=0))),
coalesce(structure_set_id, 0),
coalesce(basis_set_id, str(uuid.UUID(int=0))),
coalesce(basis_set_family_id, 0),
coalesce(pseudopotential_id, str(uuid.UUID(int=0))),
coalesce(pseudopotential_family_id, 0),
unique=True),
CheckConstraint(structure_id != str(uuid.UUID(int=0))),
CheckConstraint(structure_set_id != 0),
CheckConstraint(basis_set_id != str(uuid.UUID(int=0))),
CheckConstraint(basis_set_family_id != 0),
CheckConstraint(pseudopotential_id != str(uuid.UUID(int=0))),
CheckConstraint(pseudopotential_family_id != 0),
# ensure that a setting is not restricted to both a structure and a structure set:
CheckConstraint(or_(structure_set_id == None, structure_id == None)),
)
def __repr__(self):
args = ["id={}, priority={}".format(self.id, self.priority)]
for attr in self.constraint_attrs:
if getattr(self, attr) is not None:
args.append("{}='{}'".format(attr, getattr(self, attr + '_id')))
return "<CalculationDefaultSettings({})>".format(", ".join(args))
from sqlalchemy import MetaData
metadata = MetaData()
from datetime import datetime
from sqlalchemy import Table, Column, Integer, String, Numeric
from sqlalchemy import CheckConstraint, Index, insert
cookies = Table(
'cookies',
metadata,
Column('cookie_id', Integer(), primary_key=True),
Column('cookie_name', String(50)), # “, index=True”可被加进去，内敛的index
Column('cookie_recipe_url', String(255)),
Column('cookie_sku', String(55)),
Column('quantity', Integer()),
Column('unit_cost', Numeric(12, 2)))
CheckConstraint('unit_cost >= 0.00',
name='unit_cost_positive') # Defining the key explicitly
Index('ix_cookies_cookie_name',
'cookie_name') # Defining an index using an explicit construction type
Index('ix_test', cookies.c.cookie_sku, cookies.c.cookie_name
) # If we want to select by "cookie_SKU" and "cookie_name"
# Example 2-1. Single insert as a method
ins = cookies.insert().values(
cookie_name="chocolate chip",
cookie_recipe_url="http://some.aweso.me/cookie/recipe.html",
cookie_sku="CC01",
quantity="12",
unit_cost="0.50")
# print(str(ins))
print(
ins.compile().params
def test_render_check_constraint_literal(self):
eq_ignore_whitespace(
autogenerate._render_check_constraint(
CheckConstraint("im a constraint"), self.autogen_context),
"sa.CheckConstraint('im a constraint')")
from sqlalchemy.exc import IntegrityError
from sqlalchemy_core.db_config import db
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),
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"),
)
@staticmethod
def get_attribute_name(mlflow_attribute_name):
"""
Resolves an MLflow attribute name to a `SqlRun` attribute name.
"""
# Currently, MLflow Search attributes defined in `SearchUtils.VALID_SEARCH_ATTRIBUTE_KEYS`
# share the same names as their corresponding `SqlRun` attributes. Therefore, this function
# returns the same attribute name
return mlflow_attribute_name
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)
class Project(SitemapMixin, db.Model):
__tablename__ = "projects"
__table_args__ = (CheckConstraint(
"name ~* '^([A-Z0-9]|[A-Z0-9][A-Z0-9._-]*[A-Z0-9])$'::text",
name="projects_valid_name",
), )
__repr__ = make_repr("name")
name = Column(Text, nullable=False)
normalized_name = orm.column_property(func.normalize_pep426_name(name))
created = Column(
DateTime(timezone=False),
nullable=False,
server_default=sql.func.now(),
index=True,
)
has_docs = Column(Boolean)
upload_limit = Column(Integer, nullable=True)
last_serial = Column(Integer, nullable=False, server_default=sql.text("0"))
zscore = Column(Float, nullable=True)
total_size = Column(BigInteger, server_default=sql.text("0"))
users = orm.relationship(User,
secondary=Role.__table__,
backref="projects")
releases = orm.relationship(
"Release",
backref="project",
cascade="all, delete-orphan",
order_by=lambda: Release._pypi_ordering.desc(),
passive_deletes=True,
)
events = orm.relationship("ProjectEvent",
backref="project",
cascade="all, delete-orphan",
lazy=True)
def __getitem__(self, version):
session = orm.object_session(self)
canonical_version = packaging.utils.canonicalize_version(version)
try:
return (session.query(Release).filter(
Release.project == self,
Release.canonical_version == canonical_version,
).one())
except MultipleResultsFound:
# There are multiple releases of this project which have the same
# canonical version that were uploaded before we checked for
# canonical version equivalence, so return the exact match instead
try:
return (session.query(Release).filter(
Release.project == self, Release.version == version).one())
except NoResultFound:
# There are multiple releases of this project which have the
# same canonical version, but none that have the exact version
# specified, so just 404
raise KeyError from None
except NoResultFound:
raise KeyError from None
def __acl__(self):
session = orm.object_session(self)
acls = [
(Allow, "group:admins", "admin"),
(Allow, "group:moderators", "moderator"),
]
# Get all of the users for this project.
query = session.query(Role).filter(Role.project == self)
query = query.options(orm.lazyload("project"))
query = query.options(orm.joinedload("user").lazyload("emails"))
query = query.join(User).order_by(User.id.asc())
for role in sorted(
query.all(),
key=lambda x: ["Owner", "Maintainer"].index(x.role_name)):
if role.role_name == "Owner":
acls.append(
(Allow, str(role.user.id), ["manage:project", "upload"]))
else:
acls.append((Allow, str(role.user.id), ["upload"]))
return acls
def record_event(self, *, tag, ip_address, additional=None):
session = orm.object_session(self)
event = ProjectEvent(project=self,
tag=tag,
ip_address=ip_address,
additional=additional)
session.add(event)
session.flush()
return event
@property
def documentation_url(self):
# TODO: Move this into the database and eliminate the use of the
# threadlocal here.
request = get_current_request()
# If the project doesn't have docs, then we'll just return a None here.
if not self.has_docs:
return
return request.route_url("legacy.docs", project=self.name)
@property
def all_versions(self):
return (orm.object_session(self).query(
Release.version, Release.created, Release.is_prerelease,
Release.yanked).filter(Release.project == self).order_by(
Release._pypi_ordering.desc()).all())
@property
def latest_version(self):
return (orm.object_session(self).query(
Release.version, Release.created, Release.is_prerelease).filter(
Release.project == self, Release.yanked.is_(False)).order_by(
Release.is_prerelease.nullslast(),
Release._pypi_ordering.desc()).first())
def define_table_args(cls):
table_args = super(TestCheck, cls).define_table_args()
return table_args + (CheckConstraint('integer > 0',
name='test'), )
Column('username', String(50), nullable=False),
Column('email', String(200), nullable=False),
Column('address', String(200), nullable=False),
Column('town', String(50), nullable=False),
Column('created_on', DateTime(), default=datetime.now),
Column('updated_on',
DateTime(),
default=datetime.now,
onupdate=datetime.now))
items = Table('items', metadata, Column('id', Integer(), primary_key=True),
Column('name', String(200), nullable=False),
Column('cost_price', Numeric(10, 2), nullable=False),
Column('selling_price', Numeric(10, 2), nullable=False),
Column('quantity', Integer(), nullable=False),
CheckConstraint('quantity > 0', name='quantity_check'))
orders = Table('orders', metadata, Column('id', Integer(), primary_key=True),
Column('customer_id', ForeignKey('customers.id')),
Column('date_placed', DateTime(), default=datetime.now),
Column('date_shipped', DateTime()))
order_lines = Table('order_lines', metadata,
Column('id', Integer(), primary_key=True),
Column('order_id', ForeignKey('orders.id')),
Column('item_id', ForeignKey('items.id')),
Column('quantity', Integer()))
metadata.create_all(engine)
ins = customers.insert().values(first_name='John',
'h': 'hidden',
's': 'staff-note',
}, length=20),
nullable=False,
server_default=''),
Column('hidden_by', Integer(), nullable=True),
default_fkey(['userid'], ['login.userid'], name='comments_userid_fkey'),
default_fkey(['target_user'], ['login.userid'],
name='comments_target_user_fkey'),
default_fkey(['target_sub'], ['submission.submitid'],
name='comments_target_sub_fkey'),
default_fkey(['parentid'], ['comments.commentid'],
name='comments_parentid_fkey'),
default_fkey(['hidden_by'], ['login.userid'],
name='comments_hidden_by_fkey'),
CheckConstraint('(target_user IS NOT NULL) != (target_sub IS NOT NULL)',
name='comments_target_check'),
)
Index('ind_comments_target_user_commentid',
comments.c.target_user,
comments.c.commentid,
postgresql_where=comments.c.target_user != None)
Index('ind_comments_target_sub_commentid',
comments.c.target_sub,
comments.c.commentid,
postgresql_where=comments.c.target_sub != None)
commishclass = Table(
'commishclass',
metadata,
Column('classid', Integer(), primary_key=True, nullable=False),
class Build(Base):
__tablename__ = 'build'
__table_args__ = (
CheckConstraint('state IN (2, 3, 5)', name='build_state_check'),
CheckConstraint('state = 2 OR repo_id IS NOT NULL',
name='build_repo_id_check'),
CheckConstraint('state = 2 OR version IS NOT NULL',
name='build_version_check'),
CheckConstraint('state = 2 OR release IS NOT NULL',
name='build_release_check'),
CheckConstraint('NOT real OR state <> 2',
name='build_real_complete_check'),
)
STATE_MAP = {'running': 2, 'complete': 3, 'canceled': 4, 'failed': 5}
RUNNING = STATE_MAP['running']
COMPLETE = STATE_MAP['complete']
CANCELED = STATE_MAP['canceled']
FAILED = STATE_MAP['failed']
REV_STATE_MAP = {v: k for k, v in STATE_MAP.items()}
FINISHED_STATES = [COMPLETE, FAILED, CANCELED]
STATES = [RUNNING] + FINISHED_STATES
KOJI_STATE_MAP = {
'CLOSED': COMPLETE,
'CANCELED': CANCELED,
'FAILED': FAILED
}
id = Column(Integer, primary_key=True)
package_id = Column(Integer, ForeignKey('package.id', ondelete='CASCADE'))
package = None # backref
state = Column(Integer, nullable=False, default=RUNNING)
task_id = Column(Integer, nullable=False)
started = Column(DateTime)
finished = Column(DateTime)
epoch = Column(Integer)
version = Column(String)
release = Column(String)
repo_id = Column(Integer)
cancel_requested = Column(Boolean, nullable=False, server_default=false())
# deps_resolved is null before the build resolution is attempted
deps_resolved = Column(Boolean)
build_arch_tasks = relationship(KojiTask,
backref='build',
order_by=KojiTask.arch,
passive_deletes=True)
# was the build done by koschei or was it real build done by packager
real = Column(Boolean, nullable=False, server_default=false())
dependency_keys = deferred(Column(CompressedKeyArray))
@property
def state_string(self):
return self.REV_STATE_MAP[self.state]
@property
def srpm_nvra(self):
# pylint:disable=no-member
return dict(name=self.package.name,
version=self.version,
release=self.release,
arch='src')
@property
def taskinfo_url(self):
if self.real:
koji_config = get_config('secondary_koji_config')
else:
koji_config = get_config('koji_config')
return '{}/taskinfo?taskID={}'.format(koji_config['weburl'],
self.task_id)
def __repr__(self):
# pylint: disable=W1306
return ('{b.id} (name={b.package.name}, state={b.state_string}, '
'task_id={b.task_id})').format(b=self)
class User(Base):
"""
Represents a User account on bloodtide.net. A User account is tied
to a unique phone number. Users can be full, half, or staff.
A full User can log into the website using a password, create games,
and become a Game moderator.
A half User can participate in Games using SMS but cannot access
the website. A half User can become a full User by adding a
password to their account.
A staff User is a member of the bloodtide staff. These Users can
log into the administrative interface and manage Games.
Users can have their account banned, meaning that they cannot login
or participate in ANY games. This is not the same as banning phone
numbers.
"""
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
fname = Column(String(32), nullable=False)
lname = Column(String(32), nullable=False)
user_type = Column(Enum('administrator',
'moderator',
'full',
'half',
name="user_type"),
nullable=False)
number_verified = Column(Boolean, default=False, nullable=False)
phone_number = Column(String(16),
CheckConstraint('char_length(phone_number)>=7'),
nullable=False,
unique=True)
# join_date for half users is the time a User row is added to the database
# for full users, when validated becomes true
join_date = Column(DateTime())
verification_code = Column(String(6))
password = Column(String)
banned = Column(Boolean, default=False, nullable=False)
created_games = relationship("Game",
backref="creator",
primaryjoin="User.id==Game.creator_id")
owns_games = relationship("Game",
backref="owner",
primaryjoin="User.id==Game.owner_id")
players = relationship("Player", backref="user")
def __init__(self, phone_number, fname, lname, user_type, **kwargs):
"""
Create one of three types of Users: half, full, or staff.
All Users require a phone_number, first name, and last name.
Full Users and staff require a kwarg `password' that has already
been hashed.
Valid user_types are: 'half', 'full', 'moderator', or
'administrator'.
"""
assert user_type in ['half', 'full', 'administrator', 'moderator']
self.phone_number = phone_number
self.fname = fname
self.lname = lname
if user_type == 'half':
self.user_type = 'half'
self.join_date = datetime.utcnow()
self.number_verified = True
else:
assert kwargs.get('password') is not None
self.password = kwargs.get('password')
self.user_type = user_type
# TODO: Actual verification code.
self.verification_code = "abcdef" # generate_code(6).lower()
self.number_verified = False
def name(self):
""" First + last names properly capitalized. """
return "%s %s" % (self.fname.capitalize(), self.lname.capitalize())
def verify_number(self):
"""
Verifies a User's phone_number. This method assumes that a user
has already provided a valid verification code.
"""
assert self.number_verified is False
assert self.user_type in ['full', 'moderator', 'administrator']
self.number_verified = True
self.verification_code = None
self.join_date = datetime.utcnow()
def player_in(self, game):
""" Returns True if this User is a Player of the Game. """
return game.id in [p.game_id for p in self.players]
def player_type_in(self, game, ptype='administrator'):
"""
Returns True if this User is a Player of a specific Type of
Game.
Valid types are: 'administrator', 'moderator', 'normal', or
'banned'.
"""
assert ptype in ['administrator', 'moderator', 'normal', 'banned']
return game.id in ([
p.game_id for p in self.players if p.player_type == ptype
])
# The next 4 methods implement the FlaskLogin interface.
# TODO: Flask login isn't used yet.
# def is_authenticated(self):
# return True
# def is_active(self):
# return not self.banned
# def is_anonymous(self):
# return False
# def get_id(self):
# return unicode(self.id)
def __repr__(self):
return "<User('%s', '%s, '%s', '%s')>" % (
(self.id, self.name(), self.phone_number, self.user_type))
class User(Base):
__tablename__ = 'users'
__table_args__ = (
CheckConstraint("account_state = 'locked_temp' or locked_until is null", name='complete_requires_closed'),
)
uid = bb_id_column()
# group-id, Nutzergruppe, nicht Nutzerrank,
# z.B. group-id=3 sind Plebs, group-id=6 sind Mods, group-id=1 sind Admins.
gid = Column(Integer)
# Keine Konsistenz garantiert, insbesondere keine Einmaligkeit (zumindest theoretisch)
# Der zuletzt gesehene Name
name = Column(Unicode)
name_timestamp = Column(TIMESTAMP, default=datetime.datetime.utcfromtimestamp(0))
# [str]
aliases = Column(JSONB, default=lambda: [])
user_profile_exists = Column(Boolean, default=False)
tied = Column(Integer, ForeignKey('user_tiers.tied'))
avid = Column(Integer, ForeignKey('avatars.avid'))
registered = Column(TIMESTAMP)
online_status = Column(Unicode)
last_seen = Column(TIMESTAMP) # null: private
account_state = Column(Enum(AccountState))
locked_until = Column(Unicode)
tier = relationship('UserTier')
avatar = relationship('Avatar')
@classmethod
def from_xml(cls, session, user_tag, timestamp: datetime.datetime):
uid = int(user_tag.attrib['id'])
try:
gid = int(user_tag.attrib['group-id'])
except KeyError:
gid = None # not all <user> tags have this
current_name = user_tag.text
user = session.query(cls).get(uid)
# Too lazy for tz-aware timestamps in the DB
timestamp = timestamp.replace(tzinfo=None)
if user:
if user.name != current_name:
if timestamp >= user.name_timestamp:
if user.name not in user.aliases:
user.aliases.append(user.name)
flag_modified(user, 'aliases')
user.name = current_name
user.name_timestamp = timestamp
else:
if current_name not in user.aliases:
user.aliases.append(current_name)
flag_modified(user, 'aliases')
else:
user = cls(
uid=uid, gid=gid, name=current_name, name_timestamp=timestamp,
)
session.add(user)
return user
@property
def names(self):
return [self.name] + self.aliases