def get_previsions(cls, session, end_date=None):
""" Retrieve future validated requests per user """
# Searching for requests with an timeframe
# [NOW()] ---------- ([end_date])?
# exemples:
# <f --r1---- t>
# <f --r2-- t>
# <f ------r3-------- t>
# <f ----------- r4 -------------------- t>
# => Matching period are periods ending after NOW()
# and if an end_date is specified periods starting before it:
if end_date:
future_requests = session.query(cls.user_id,func.sum(cls.days)).\
filter(cls.date_to >= func.current_timestamp(),
cls.date_from < end_date,
cls.vacation_type_id == 1,
cls.status == 'APPROVED_ADMIN').\
group_by(cls.user_id).\
order_by(cls.user_id);
else:
future_requests = session.query(cls.user_id,func.sum(cls.days)).\
filter(cls.date_to >= func.current_timestamp(),
cls.vacation_type_id == 1,
cls.status == 'APPROVED_ADMIN').\
group_by(cls.user_id).\
order_by(cls.user_id);
ret = {}
for user_id, total in future_requests:
ret[user_id] = total
return ret
def test_three(self):
t = self.tables.t
actual_ts = testing.db.scalar(func.current_timestamp()) - datetime.timedelta(days=5)
self._test(
func.current_timestamp() - datetime.timedelta(days=5),
{"hour": actual_ts.hour, "year": actual_ts.year, "month": actual_ts.month},
)
def add_ralert_to_to_database(session):
for adven in ralert_to_add:
print("Adventure Name: \'%s\', Type: \'%d\'" % (adven[0], adven[1]))
new_alert = RescueAlert(adven[0],adven[1], func.current_timestamp(), func.current_timestamp())
user = session.query(User).filter_by(email=adven[2]).first()
user.rescue_alerts.append(new_alert)
session.add(user)
session.commit()
def test_twelve(self):
t = self.tables.t
actual_ts = testing.db.scalar(func.current_timestamp()).replace(tzinfo=None) - datetime.datetime(
2012, 5, 10, 12, 15, 25
)
self._test(
func.current_timestamp() - func.coalesce(t.c.dtme, func.current_timestamp()), {"day": actual_ts.days}
)
def valid_login(u_account):
"""Check if current login pass is acceptable.
User can login by name, mobile or email.
Returns a dict {id, name, privilege} if valid."""
query_str = ''
if u_account['name']:
query_str = 'name="' + u_account['name'] + '"'
elif u_account['mobile']:
query_str = 'mobile="' + u_account['mobile'] + '"'
elif u_account['email']:
query_str = 'email="' + u_account['email'] + '"'
if not query_str or not u_account['pwd']:
#Error user info, return 1 is safe
return '无效的用户名和密码格式'
query_str += ' and passwd="' + u_account['pwd'] + '"'
session = open_session()
try:
u = session.query(User).filter(query_str).first()
if u == None:
return '用户名或密码错误。'
u.lastactive = func.current_timestamp()
session.merge(u)
session.commit()
return {'id': u.id, 'name': u.name, 'privilege': u.privilege}
except Exception, ex:
print ex
return '出错了，请重试。'
def get_dag_duration_info():
'''get duration of currently running DagRuns
:return dag_info
'''
driver = Session.bind.driver
durations = {
'pysqlite': func.sum(
(func.julianday(func.current_timestamp()) - func.julianday(DagRun.start_date)) * 86400.0
),
'mysqldb': func.sum(func.timestampdiff(text('second'), DagRun.start_date, func.now())),
'default': func.sum(func.now() - DagRun.start_date)
}
duration = durations.get(driver, durations['default'])
with session_scope(Session) as session:
return session.query(
DagRun.dag_id,
DagRun.run_id,
duration.label('duration')
).group_by(
DagRun.dag_id,
DagRun.run_id
).filter(
DagRun.state == State.RUNNING
).all()
def register_changes(self, memento_changes):
"""Create rows in the memento table
:param memento_changes: an iterator over `memento_change` tuples that
need to be stored in the memento table.
"""
rows = list()
authentication_id = self._get_authentication_id()
for m in memento_changes:
if len(m.primary_key) == 1:
rows.append(
{
"model": m.model,
"primary_key": m.primary_key[0],
"previous_attributes": m.previous_attributes,
"memento_type": self.memento_id_by_type.get(m.memento_type, None),
"authentication_id": authentication_id,
}
)
if len(rows):
table = self._get_memento_table()
clause = table.insert(creation_date=func.current_timestamp())
try:
clause.execute(rows)
except exc.DatabaseError, e:
LOGGER.error("Programming Error, could not flush history", exc_info=e)
def test_compile(self):
for dialect in all_dialects(exclude=("sybase",)):
bindtemplate = BIND_TEMPLATES[dialect.paramstyle]
self.assert_compile(
func.current_timestamp(), "CURRENT_TIMESTAMP", dialect=dialect
)
self.assert_compile(func.localtime(), "LOCALTIME", dialect=dialect)
if dialect.name in ("firebird",):
self.assert_compile(
func.nosuchfunction(), "nosuchfunction", dialect=dialect
)
else:
self.assert_compile(
func.nosuchfunction(), "nosuchfunction()", dialect=dialect
)
# test generic function compile
class fake_func(GenericFunction):
__return_type__ = sqltypes.Integer
def __init__(self, arg, **kwargs):
GenericFunction.__init__(self, arg, **kwargs)
self.assert_compile(
fake_func("foo"),
"fake_func(%s)"
% bindtemplate
% {"name": "fake_func_1", "position": 1},
dialect=dialect,
)
functions._registry['_default'].pop('fake_func')
def test_compile(self):
for dialect in all_dialects(exclude=('sybase', )):
bindtemplate = BIND_TEMPLATES[dialect.paramstyle]
self.assert_compile(func.current_timestamp(),
"CURRENT_TIMESTAMP", dialect=dialect)
self.assert_compile(func.localtime(), "LOCALTIME", dialect=dialect)
if dialect.name in ('firebird',):
self.assert_compile(func.nosuchfunction(),
"nosuchfunction", dialect=dialect)
else:
self.assert_compile(func.nosuchfunction(),
"nosuchfunction()", dialect=dialect)
# test generic function compile
class fake_func(GenericFunction):
__return_type__ = sqltypes.Integer
def __init__(self, arg, **kwargs):
GenericFunction.__init__(self, arg, **kwargs)
self.assert_compile(
fake_func('foo'),
"fake_func(%s)" %
bindtemplate % {'name': 'fake_func_1', 'position': 1},
dialect=dialect)
def get_last_applied_membership_fee():
"""
Get the last applied membership fee.
:rtype: MembershipFee
"""
return MembershipFee.q.filter(
MembershipFee.ends_on <= func.current_timestamp()) \
.order_by(MembershipFee.ends_on.desc()).first()
def disable(self, ends_at=None):
if ends_at is None:
ends_at = object_session(self).query(func.current_timestamp()).scalar()
if self.begins_at is not None and self.begins_at > ends_at:
self.ends_at = self.begins_at
else:
self.ends_at = ends_at
def end_website_run(self, website_run_id):
upd = ( self.website_run.update().
where(
self.website_run.c.website_run_id == website_run_id ).
values(
end_time=func.current_timestamp() )
)
result = self.conn.execute(upd)
def test_constructor(self):
try:
func.current_timestamp('somearg')
assert False
except TypeError:
assert True
try:
func.char_length('a', 'b')
assert False
except TypeError:
assert True
try:
func.char_length()
assert False
except TypeError:
assert True
def test_rename_column_serv_compiled_default(self):
context = op_fixture('mysql')
op.alter_column('t1', 'c1', new_column_name="c2", existing_type=Integer,
existing_server_default=func.utc_thing(func.current_timestamp()))
# this is not a valid MySQL default but the point is to just
# test SQL expression rendering
context.assert_(
"ALTER TABLE t1 CHANGE c1 c2 INTEGER NULL DEFAULT utc_thing(CURRENT_TIMESTAMP)"
)
def upgrade():
print "... Create TABLE hebergement_video"
op.create_table(
'hebergement_video',
Column('heb_vid_pk', Integer, primary_key=True, unique=True,
nullable=False),
Column('heb_vid_url', String()),
Column('heb_vid_date', Date(), default=func.current_timestamp()),
Column('heb_vid_heb_fk', Integer, ForeignKey('hebergement.heb_pk'))
)
def play_with_time(session, name):
user = session.query(User).filter_by(email='*****@*****.**').first()
new_alert = RescueAlert(name,
AdventureType.SnowBoarding.value,
func.current_timestamp(),
get_time_plus_minutes(30))
#func.current_timestamp().op('AT TIME ZONE')('UTC'))
#func.current_timestamp())
user.rescue_alerts.append(new_alert)
session.add(user)
session.commit()
def test_no_paren_fns(self):
for fn, expected in [
(func.uid(), "uid"),
(func.UID(), "UID"),
(func.sysdate(), "sysdate"),
(func.row_number(), "row_number()"),
(func.rank(), "rank()"),
(func.now(), "CURRENT_TIMESTAMP"),
(func.current_timestamp(), "CURRENT_TIMESTAMP"),
(func.user(), "USER"),
]:
self.assert_compile(fn, expected)
def active(self, when=None):
"""
Tests if the membership overlaps with a given interval. If no interval
is given, it tests if the membership is active right now.
:param Interval when: interval in which the membership
:rtype: bool
"""
if when is None:
now = object_session(self).query(func.current_timestamp()).scalar()
when = single(now)
return when.overlaps(closed(self.begins_at, self.ends_at))
def create_table(self):
Table(
OPLOG_TABLENAME,
MetaData(bind = self.engine),
Column('id', Integer, autoincrement=True, nullable=False, primary_key=True),
Column('time', TIMESTAMP, default=func.current_timestamp(), nullable=False, index=True),
Column('user', VARCHAR(32), nullable=False, index=True),
Column('action', Text, nullable=False),
Column('args', Text, nullable=True),
Column('others', Text, nullable=True),
mysql_engine = 'InnoDB',
mysql_charset = 'utf8',
).create(checkfirst = True)
def _baseline_5_aggregates(self):
Animal = self.metadata.tables["Animal"]
Zoo = self.metadata.tables["Zoo"]
# TODO: convert to ORM
engine = self.metadata.bind
for x in range(ITERATIONS):
# views
view = engine.execute(select([Animal.c.Legs])).fetchall()
legs = sorted([x[0] for x in view])
expected = {
"Leopard": 73.5,
"Slug": 0.75,
"Tiger": None,
"Lion": None,
"Bear": None,
"Ostrich": 103.2,
"Centipede": None,
"Emperor Penguin": None,
"Adelie Penguin": None,
"Millipede": None,
"Ape": None,
"Tick": None,
}
for species, lifespan in engine.execute(
select([Animal.c.Species, Animal.c.Lifespan])
).fetchall():
assert lifespan == expected[species]
expected = ["Montr\xe9al Biod\xf4me", "Wild Animal Park"]
e = select(
[Zoo.c.Name],
and_(
Zoo.c.Founded != None, # noqa
Zoo.c.Founded <= func.current_timestamp(),
Zoo.c.Founded >= datetime.date(1990, 1, 1),
),
)
values = [val[0] for val in engine.execute(e).fetchall()]
assert set(values) == set(expected)
# distinct
legs = [
x[0]
for x in engine.execute(
select([Animal.c.Legs], distinct=True)
).fetchall()
]
legs.sort()
def test_rename_column_serv_compiled_default(self):
context = op_fixture("mysql")
op.alter_column(
"t1",
"c1",
existing_type=Integer,
server_default=func.utc_thing(func.current_timestamp()),
)
# this is not a valid MySQL default but the point is to just
# test SQL expression rendering
context.assert_(
"ALTER TABLE t1 ALTER COLUMN c1 "
"SET DEFAULT utc_thing(CURRENT_TIMESTAMP)"
)
def _baseline_5_aggregates(self):
Animal = self.metadata.tables['Animal']
Zoo = self.metadata.tables['Zoo']
# TODO: convert to ORM
engine = self.metadata.bind
for x in range(ITERATIONS):
# views
view = engine.execute(select([Animal.c.Legs])).fetchall()
legs = sorted([x[0] for x in view])
expected = {
'Leopard': 73.5,
'Slug': .75,
'Tiger': None,
'Lion': None,
'Bear': None,
'Ostrich': 103.2,
'Centipede': None,
'Emperor Penguin': None,
'Adelie Penguin': None,
'Millipede': None,
'Ape': None,
'Tick': None,
}
for species, lifespan in engine.execute(
select([Animal.c.Species, Animal.c.Lifespan])).fetchall():
assert lifespan == expected[species]
expected = ['Montr\xe9al Biod\xf4me', 'Wild Animal Park']
e = select([Zoo.c.Name],
and_(Zoo.c.Founded != None,
Zoo.c.Founded <= func.current_timestamp(),
Zoo.c.Founded >= datetime.date(1990,
1,
1)))
values = [val[0] for val in engine.execute(e).fetchall()]
assert set(values) == set(expected)
# distinct
legs = [
x[0]
for x in engine.execute(
select([Animal.c.Legs],
distinct=True)).fetchall()]
legs.sort()
def add_host_deployment(dep_id, host_id, user, status, package_id):
"""Add host deployment for a given host and deployment"""
host_dep = HostDeployment(
deployment_id=dep_id,
host_id=host_id,
user=user,
status=status,
realized=func.current_timestamp(),
package_id=package_id,
)
# Commit to DB immediately
Session.add(host_dep)
Session.commit()
return host_dep
def add_user(name, pwd, mobile='', email='', bindto=None, privilege=0):
"""Add a new user with name, password, mobile, email.
Once created successfully, return the user id, name and privilege."""
session = open_session()
try:
#If existed, it will raise error
user = User(name=name, passwd=pwd, mobile=mobile, email=email, lastactive=func.current_timestamp(), bindto=bindto, privilege=privilege)
session.add(user)
session.flush()
#Add an account to this user
a = Acct(uid=user.id)
session.add(a)
session.commit()
return {'id':user.id, 'name':user.name, 'privilege':user.privilege}
except Exception, ex:
session.rollback()
print ex
return False
def add_deployment(user):
"""Add deployment for a given package ID"""
dep = Deployment(
user=user,
# THIS NEEDS TO BE REMOVED ONCE THE NEW DEPLOY CODE
# IS IN PLACE - KEL 20150827
status='complete',
declared=func.current_timestamp()
)
# Commit to DB immediately
Session.add(dep)
Session.commit()
Session.flush() # Needed to get DeploymentID generated
return dep
def add_dataset_meta(name, file_name='', human_name='', description='',
val_attr='', count_q=False, area_q=False, dist_q=False,
temp_q=False, weighted_q=False, access_q=False,
voronoi=False, duration='interval', demo=False):
""" Add infotmation about a dataset in the meta table """
if human_name == '':
human_name = name
meta_table = Table('sf_meta', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
row = {'table_name': name, 'file_name': file_name,
'human_name': human_name,'description': description,
'last_update': func.current_timestamp(), 'val_attr': val_attr,
'count_q': count_q, 'area_q': area_q, 'dist_q': dist_q,
'temp_q': temp_q, 'weighted_q': weighted_q, 'access_q': access_q,
'voronoi': voronoi, 'duration': duration, 'demo': demo}
ins = meta_table.insert(row)
conn = engine.contextual_connect()
conn.execute(ins)
return 'Meta information for {0} inserted.'.format(name)
def search_location(self, loc):
shops = self.fb_bot.search_restaurant(loc.lat, loc.lon)
loc.found = len(shops)
updated = 0
for shop in shops:
updated += self._save_fb_shop(shop.get('id'), loc)
logger.info(
"Track_id: {track_id} Found: {found} Updated: {updated}".format(
track_id=loc.id_,
found=loc.found,
updated=updated))
loc.update_ts = func.current_timestamp()
try:
self.session.commit()
except exc.SQLAlchemyError as err:
self.session.rollback()
logger.error(err)
return updated
def _fetch_rating(self, fb_shop):
info = self.fb_bot.fetch(fb_shop.fbid)
if not info:
logger.error(
"fbid: %s FB API GraphMethodException" % fb_shop.fbid)
return 0
fb_shop.update_ts = func.current_timestamp()
fb_shop.likes = info.get('likes', 0)
fb_shop.checkins = info.get('checkins', 0)
fb_shop.talking = info.get('talking_about_count', 0)
try:
self.session.commit()
status = 1
except exc.SQLAlchemyError as err:
self.session.rollback()
logger.error(err)
status = 0
return status
def _update_by_type(session, table_type, info):
"""Update a record. Return if succeeded."""
record = session.query(tables[table_type]).get(int(info['id']))
if record == None:
#'**Record from ' + table_type + ' by ' + str(key) + ' not found'
return False
count = 0
for item, value in info.items():
if hasattr(record, item):
setattr(record, item, value)
count += 1
print '**Record from ' + table_type + ' by ' + str(key) + ' updated'
if count:
if table_type == 'user':
setattr(record, 'lastactive', func.current_timestamp())
session.merge(record)
return True
else:
return False
def add_package_definition(deploy_type, validation_type, name, path,
arch, build_type, build_host):
"""Add base definition for a package"""
pkg_def = PackageDefinition(
deploy_type=deploy_type,
validation_type=validation_type,
pkg_name=name,
path=path,
arch=arch,
build_type=build_type,
build_host=build_host,
created=func.current_timestamp()
)
Session.add(pkg_def)
Session.flush() # Needed to get pkg_ef_id generated
return pkg_def
def test_ansi_functions_with_args(self):
ct = func.current_timestamp("somearg")
self.assert_compile(ct, "CURRENT_TIMESTAMP(:current_timestamp_1)")
class Vulnerability(MainBase):
# __fulltext_columns__ = ('comment',)
__tablename__ = "vulnerability"
comment = Column(Text, nullable=False)
date_created = Column(DateTime,
default=func.current_timestamp(),
index=True)
exploit_exists = Column(Boolean, default=False)
# N.B. Don't use a ForeignKey constraint on NVD here as the nvd data might be
# updated dynamically (e.g. row deleted and then added by go-cve-dictionary).
cve_id = Column(String(255), nullable=True, unique=True)
creator_id = Column(Integer, ForeignKey(User.id), nullable=True)
creator = relationship(User)
resource_links = relationship(
VulnerabilityResources,
backref="vulnerability",
cascade="all, delete-orphan",
single_parent=True,
)
commits = relationship(
VulnerabilityGitCommits,
backref="vulnerability",
cascade="all, delete-orphan",
single_parent=True,
lazy="joined",
)
nvd = relationship(
"Nvd",
backref="vulns",
primaryjoin="remote(Nvd.cve_id) == foreign(Vulnerability.cve_id)",
)
_has_annotations = None
def set_has_annotations(self, status=True):
self._has_annotations = status
@hybrid_property
def has_annotations(self):
if self._has_annotations is not None:
return self._has_annotations
if self.commits:
for c in self.commits:
if c.comments:
self._has_annotations = True
return True
return False
@has_annotations.expression
def has_annotations(self):
return self.commits.any(VulnerabilityGitCommits.comments.any())
@property
def master_commit(self):
# TODO: refactor assumption that the first commit is the "master" one!
if self.commits:
return self.commits[0]
return None
def __repr__(self):
return f"Vulnerability Info({vars(self)})"
@classmethod
def get_by_id(cls, id):
return cls.query.filter_by(
id=id).options(default_vuln_view_options).first()
@classmethod
def get_by_cve_id(cls, cve_id):
return (cls.query.filter_by(
cve_id=cve_id).options(default_vuln_view_options).first())
@classmethod
def get_by_commit_hash(cls, commit_hash):
return (cls.query.join(
Vulnerability.commits,
aliased=True).filter_by(commit_hash=commit_hash).options(
default_vuln_view_options).first())
def to_json(self):
"""Prepare object for Json serialisation."""
products = [{
'vendor': v,
'product': p
} for v, p in self.nvd.get_products()]
cwes = [{'id': c.cwe_id, 'name': c.cwe_name} for c in self.nvd.cwes]
return {
'is_processed': True,
'comment': self.comment,
'cve_id': self.cve_id,
'exploit_exists': self.exploit_exists,
'has_annotations': self.has_annotations,
'master_commit': self.master_commit.to_json(),
'products': products,
'langs': self.nvd.get_languages(),
'description': self.nvd.description,
'cwes': cwes,
'score': self.nvd.score,
'references': [l.link for l in self.nvd.references]
}
def to_json_full(self):
"""Serialize object properties as dict."""
ressources = [
r.to_json() for r in self.resource_links
if self.resource_links is not None
]
commits = [
c.to_json() for c in self.commits if self.commits is not None
]
return {
'is_processed': True,
'date_created': self.date_created,
'date_modified': self.date_modified,
'comment': self.comment,
'exploit_exists': self.exploit_exists,
'cve_id': self.cve_id,
'creator':
self.creator.to_json() if self.creator is not None else None,
'resource_links': ressources,
'commits': commits,
'nvd': self.nvd._to_json_full() if self.nvd is not None else None,
'has_annotations': self.has_annotations
}
class Base(object):
__abstract__ = True
date_created = Column('date_created', DateTime, default=func.current_timestamp(), nullable=False)
date_modified = Column('date_modified', DateTime, default=func.current_timestamp(),
onupdate=func.current_timestamp(), nullable=False)
class Vulnerability(MainBase): # pylint: disable=too-many-public-methods
# __fulltext_columns__ = ('comment',)
__tablename__ = "vulnerability"
vcdb_id = Column(Integer, default=None, nullable=True)
version = Column(Integer, default=None, nullable=True)
prev_version = Column(Integer, default=None, nullable=True)
state = Column(Enum(VulnerabilityState),
default=VulnerabilityState.NEW,
nullable=False)
reviewer_id = Column(Integer,
ForeignKey(User.id, name="fk_reviewer_id"),
nullable=True)
reviewer = relationship(User, foreign_keys=[reviewer_id])
review_feedback = Column(Text)
feedback_reviewer_id = Column(
Integer,
ForeignKey(User.id,
name="fk_feedback_reviewer_id",
ondelete="RESTRICT"),
nullable=True,
)
feedback_reviewer = relationship(User, foreign_keys=[feedback_reviewer_id])
comment = Column(Text, nullable=False)
date_created = Column(DateTime,
default=func.current_timestamp(),
index=True)
exploit_exists = Column(Boolean, default=False)
# N.B. Don't use a ForeignKey constraint on NVD here as the nvd data might
# be updated dynamically (e.g. row deleted and then added by
# go-cve-dictionary).
cve_id = Column(String(255), nullable=True, index=True)
UniqueConstraint(vcdb_id, version, name="uk_vcdb_id_version")
creator_id = Column(Integer,
ForeignKey(User.id, ondelete="RESTRICT"),
nullable=True)
creator = relationship(User, foreign_keys=[creator_id])
resources = relationship(
VulnerabilityResources,
cascade="all, delete-orphan",
single_parent=True,
)
commits = relationship(
VulnerabilityGitCommits,
cascade="all, delete-orphan",
single_parent=True,
lazy="joined",
)
nvd = relationship(
"Nvd",
backref="vulns",
primaryjoin="remote(Nvd.cve_id) == foreign(Vulnerability.cve_id)",
)
products = relationship(
"Product",
secondary=vulnerable_products,
backref="vulnerabilities",
)
_has_annotations = None
def user_can_read(self, user: User):
return self.state == VulnerabilityState.PUBLISHED or self.is_creator(
user)
def user_can_update(self, user: User):
return self.is_creator(user)
def user_can_annotate(self, user: User):
return False # disable for now
return self.is_creator(user) # pylint: disable=unreachable
def user_can_delete(self, user: User):
return (self.is_creator(user) and self.state in (
VulnerabilityState.READY,
VulnerabilityState.NEW,
VulnerabilityState.NEEDS_IMPROVEMENT,
) or user.is_admin() and self.state == VulnerabilityState.ARCHIVED)
def reviewer_can_read(self, user: User):
return (self.state == VulnerabilityState.READY or self.state in (
VulnerabilityState.IN_REVIEW,
VulnerabilityState.REVIEWED,
VulnerabilityState.ARCHIVED,
) and self.is_reviewer(user))
_permissions = {
PredefinedRoles.ADMIN: {
MANAGE: ALL,
},
PredefinedRoles.USER: {
READ: user_can_read,
UPDATE: user_can_update,
DELETE: user_can_delete,
ANNOTATE: user_can_annotate,
},
PredefinedRoles.REVIEWER: {
READ: reviewer_can_read,
ASSIGN: True,
APPROVE: True,
REJECT: True,
},
}
def set_has_annotations(self, status: bool = True):
self._has_annotations = status
@hybrid_property
def has_annotations(self):
if self._has_annotations is not None:
return self._has_annotations
if self.commits:
for commit in self.commits:
if commit.comments:
self._has_annotations = True
return True
return False
@has_annotations.expression # type: ignore[no-redef]
def has_annotations(self):
return self.commits.any(VulnerabilityGitCommits.comments.any())
@property
def master_commit(self):
# TODO: refactor assumption that the first commit is the "master" one!
if self.commits and self.commits[0].commit_link:
return self.commits[0]
return None
def __repr__(self):
return f"Vulnerability Info({vars(self)})"
@classmethod
def get_by_vcdb_id(cls, id: str, published_only: bool = True): # pylint: disable=redefined-builtin
state_filter = None
if published_only:
state_filter = VulnerabilityState.PUBLISHED
return (cls.query.filter_by(
vcdb_id=id,
state=state_filter).options(default_vuln_view_options).order_by(
cls.version.desc()).first())
@classmethod
def get_by_id(cls, id: str): # pylint: disable=redefined-builtin
return cls.query.filter_by(
id=id).options(default_vuln_view_options).first()
@classmethod
def get_by_cve_id(cls, cve_id: str, published_only: bool = True):
state_filter = None
if published_only:
state_filter = VulnerabilityState.PUBLISHED
return (cls.query.filter_by(
cve_id=cve_id,
state=state_filter).options(default_vuln_view_options).first())
@classmethod
def get_by_commit_hash(cls, commit_hash: str):
return (cls.query.join(
Vulnerability.commits,
aliased=True).filter_by(commit_hash=commit_hash).options(
default_vuln_view_options).first())
def get_contributors(self):
return User.query.join(Vulnerability.creator).filter(
Vulnerability.vcdb_id == self.vcdb_id, Vulnerability.is_published)
def to_json(self):
"""Prepare object for Json serialisation."""
products = [{
"vendor": v,
"product": p
} for v, p in self.nvd.get_products()]
cwes = [{"id": c.cwe_id, "name": c.cwe_name} for c in self.nvd.cwes]
return {
"comment": self.comment,
"cve_id": self.cve_id,
"cwes": cwes,
"description": self.nvd.description,
"exploit_exists": self.exploit_exists,
"has_annotations": self.has_annotations,
"is_processed": True,
"langs": self.nvd.get_languages(),
"master_commit": self.master_commit.to_json(),
"products": products,
"references": [l.link for l in self.nvd.references],
"score": self.nvd.score,
}
def to_json_full(self):
"""Serialize object properties as dict."""
data = self.to_json()
data["resources"] = [
r.to_json() for r in self.resources if self.resources is not None
]
data["commits"] = [
c.to_json() for c in self.commits if self.commits is not None
]
data["nvd"] = self.nvd.to_json_raw_data(
) if self.nvd is not None else None
data["creator"] = self.creator.to_json(
) if self.creator is not None else None
data["date_created"] = self.date_created
data["date_modified"] = self.date_modified
return data
def update_state(self, new_state):
state_machine = VulnerabilityState(self)
state_machine.next_state(new_state)
self.state = new_state
@hybrid_property
def is_new(self):
return self.state == VulnerabilityState.NEW
@hybrid_property
def is_published(self):
return self.state == VulnerabilityState.PUBLISHED
@hybrid_property
def is_archived(self):
return self.state == VulnerabilityState.ARCHIVED
@hybrid_property
def needs_improvement(self):
return self.state == VulnerabilityState.NEEDS_IMPROVEMENT
@hybrid_property
def is_reviewable(self):
return self.state == VulnerabilityState.READY
@hybrid_property
def is_in_review(self):
return self.state == VulnerabilityState.IN_REVIEW
@hybrid_property
def is_publishable(self):
return self.state == VulnerabilityState.REVIEWED
def is_reviewer(self, reviewer):
return self.reviewer == reviewer
def is_creator(self, creator):
return self.creator == creator
@hybrid_property
def has_feedback(self):
return bool(self.review_feedback)
def make_reviewable(self):
self.update_state(VulnerabilityState.READY)
def accept_review(self, reviewer):
self.reviewer = reviewer
self.update_state(VulnerabilityState.IN_REVIEW)
def deny_review(self):
self.reviewer = None
self.update_state(VulnerabilityState.READY)
def accept_change(self):
self.update_state(VulnerabilityState.REVIEWED)
def deny_change(self, reviewer, reason):
self.feedback_reviewer = reviewer
self.review_feedback = reason
self.update_state(VulnerabilityState.NEEDS_IMPROVEMENT)
def archive_entry(self):
self.update_state(VulnerabilityState.ARCHIVED)
def return_to_review_pool(self):
self.reviewer = None
self.update_state(VulnerabilityState.READY)
def publish_change(self):
self.version = self.next_version_number()
# TODO: Assign a new vcdb_id if required at this point.
# This is mostly relevant for fully new entry proposals.
self.update_state(VulnerabilityState.PUBLISHED)
def next_version_number(self):
prev_version = (Vulnerability.query.filter_by(
vcdb_id=self.vcdb_id).with_entities(func.max(
Vulnerability.version)).first()[0])
if not prev_version:
prev_version = 0
return prev_version + 1
@staticmethod
def get_num_proposals_action_required(user): # pylint: disable=invalid-name
return Vulnerability.query.filter(
Vulnerability.creator == user,
Vulnerability.state.in_(
[VulnerabilityState.NEW,
VulnerabilityState.NEEDS_IMPROVEMENT]),
).count()
@staticmethod
def get_num_proposals_pending(user):
return Vulnerability.query.filter(
Vulnerability.creator == user,
Vulnerability.state.in_([
VulnerabilityState.READY,
VulnerabilityState.IN_REVIEW,
VulnerabilityState.REVIEWED,
]),
).count()
@staticmethod
def get_num_proposals_publishing_pending(): # pylint: disable=invalid-name
return Vulnerability.query.filter(
Vulnerability.state == VulnerabilityState.REVIEWED).count()
@staticmethod
def get_num_proposals_unassigned():
return Vulnerability.query.filter(
Vulnerability.state == VulnerabilityState.READY).count()
@staticmethod
def get_num_proposals_assigned():
return Vulnerability.query.filter(
Vulnerability.state == VulnerabilityState.IN_REVIEW).count()
@staticmethod
def get_num_proposals_assigned_to(user):
return Vulnerability.query.filter(
Vulnerability.reviewer == user,
Vulnerability.state == VulnerabilityState.IN_REVIEW,
).count()
def copy(self):
new_vuln = copy_obj(self)
new_vuln.commits = []
for commit in self.commits:
new_vuln.commits.append(commit.copy())
for resource in self.resources:
new_vuln.resources.append(resource.copy())
# N.B. We never copy NVD data as it's supposed to stay read only.
return new_vuln
users.c.name.like(bindparam('name', type_=String) + text("'%'")),
addresses.c.email_address.like(
bindparam('name', type_=String) + text("'@%'")))).select_from(
users.outerjoin(addresses)).order_by(addresses.c.id)
print(conn.execute(s, name='jack').fetchall())
# Functions
print("\nFunctions")
print(func.now())
print(func.concat('x', 'y'))
print(func.xyz_my_goofy_function())
print(
"Some functions are know by SQLAlchemy thus they don't get the parenthesis added after them"
)
print(func.current_timestamp())
# Below, we use the result function scalar() to just read the first column of the first row and then close the result
print(
"\nBelow, we use the result function scalar() to just read the first column of the first row and then close the result"
)
print(
conn.execute(
select([
func.max(addresses.c.email_address, type_=String).label('maxemail')
])).scalar())
# we can construct using “lexical” column objects as well as bind parameters:
print(
"\nwe can construct using “lexical” column objects as well as bind parameters:"
)
def __init__(self, request):
self.name = request.GET['query']
self.create_date = func.current_timestamp()
self.request = request
self.count = 0
def approve_update(update: Update, db: Session):
"""Add a comment to an update if it is ready for stable.
Check that the update is eligible to be pushed to stable but hasn't had comments from Bodhi to
this effect. Add a comment stating that the update may now be pushed to stable.
Args:
update: an update in testing that may be ready for stable.
"""
if not update.release.mandatory_days_in_testing and not update.autotime:
# If this release does not have any testing requirements and is not autotime,
# skip it
log.info(
f"{update.release.name} doesn't have mandatory days in testing")
return
# If this update was already commented, skip it
if update.has_stable_comment:
return
# If updates have reached the testing threshold, say something! Keep in mind
# that we don't care about karma here, because autokarma updates get their request set
# to stable by the Update.comment() workflow when they hit the required threshold. Thus,
# this function only needs to consider the time requirements because these updates have
# not reached the karma threshold.
if not update.meets_testing_requirements:
return
log.info(f'{update.alias} now meets testing requirements')
# Only send email notification about the update reaching
# testing approval on releases composed by bodhi
update.comment(db,
str(config.get('testing_approval_msg')),
author='bodhi',
email_notification=update.release.composed_by_bodhi)
notifications.publish(
update_schemas.UpdateRequirementsMetStableV1.from_dict(
dict(update=update)))
if update.autotime and update.days_in_testing >= update.stable_days:
log.info(f"Automatically marking {update.alias} as stable")
# For now only rawhide update can be created using side tag
# Do not add the release.pending_stable_tag if the update
# was created from a side tag.
if update.release.composed_by_bodhi:
update.set_request(db=db,
action=UpdateRequest.stable,
username="******")
# For updates that are not included in composes run by bodhi itself,
# mark them as stable
else:
# Single and Multi build update
conflicting_builds = update.find_conflicting_builds()
if conflicting_builds:
builds_str = str.join(", ", conflicting_builds)
update.comment(
db, "This update cannot be pushed to stable. "
f"These builds {builds_str} have a more recent "
f"build in koji's {update.release.stable_tag} tag.",
author="bodhi")
update.request = None
if update.from_tag is not None:
update.status = UpdateStatus.pending
update.remove_tag(
update.release.get_testing_side_tag(update.from_tag))
else:
update.status = UpdateStatus.obsolete
update.remove_tag(update.release.pending_testing_tag)
update.remove_tag(update.release.candidate_tag)
db.commit()
return
update.add_tag(update.release.stable_tag)
update.status = UpdateStatus.stable
update.request = None
update.pushed = True
update.date_stable = update.date_pushed = func.current_timestamp()
update.comment(
db,
"This update has been submitted for stable by bodhi",
author=u'bodhi')
# Multi build update
if update.from_tag:
# Merging the side tag should happen here
pending_signing_tag = update.release.get_pending_signing_side_tag(
update.from_tag)
testing_tag = update.release.get_testing_side_tag(
update.from_tag)
update.remove_tag(pending_signing_tag)
update.remove_tag(testing_tag)
update.remove_tag(update.from_tag)
koji = buildsys.get_session()
koji.deleteTag(pending_signing_tag)
koji.deleteTag(testing_tag)
# Removes the tag and the build target from koji.
koji.removeSideTag(update.from_tag)
else:
# Single build update
update.remove_tag(update.release.pending_testing_tag)
update.remove_tag(update.release.pending_stable_tag)
update.remove_tag(update.release.pending_signing_tag)
update.remove_tag(update.release.candidate_tag)
def test_eleven(self):
self._test(
func.current_timestamp() - func.current_timestamp(),
{"year": 0, "month": 0, "day": 0, "hour": 0},
)
def test_five(self):
t = self.tables.t
self._test(
func.coalesce(t.c.dtme, func.current_timestamp()),
overrides={"epoch": 1336652125.0},
)
def run(self, api_version: int, data: dict):
"""
Process the given message, updating relevant bugs and test cases.
Duplicate messages: if the server delivers the message multiple times,
the bugs and test cases are simply re-fetched and updated, so nothing
bad happens.
Args:
api_version: API version number.
data: Information about a new or edited update.
"""
action = data["action"]
alias = data['update'].get('alias')
log.info("Updates Handler handling %s, %s" % (alias, action))
# Go to sleep for a second to try and avoid a race condition
# https://github.com/fedora-infra/bodhi/issues/458
time.sleep(1)
with self.db_factory() as session:
update = Update.get(alias)
if not update:
raise BodhiException("Couldn't find alias '%s' in DB" % alias)
bugs = []
if action == "edit":
# If editing a Pending update, all of whose builds are signed, for a release
# which isn't composed by Bodhi (i.e. Rawhide), move it directly to Testing.
if not update.release.composed_by_bodhi \
and update.status == UpdateStatus.pending \
and update.signed:
log.info("Every build in the update is signed, set status to testing")
update.status = UpdateStatus.testing
update.date_testing = func.current_timestamp()
update.request = None
log.info(f"Update status of {update.display_name} has been set to testing")
for idx in data['new_bugs']:
bug = Bug.get(idx)
# Sanity check
if bug is None or bug not in update.bugs:
update_bugs_ids = [b.bug_id for b in update.bugs]
update.update_bugs(update_bugs_ids + [idx], session)
# Now, after update.update_bugs, bug with idx should exists in DB
bug = Bug.get(idx)
bugs.append(bug)
elif action == "testing":
bugs = update.bugs
else:
raise NotImplementedError("Should never get here.")
self.work_on_bugs(session, update, bugs)
self.fetch_test_cases(session, update)
if config['test_gating.required']:
with self.db_factory() as session:
update = Update.get(alias)
update.update_test_gating_status()
log.info("Updates Handler done with %s, %s" % (alias, action))
class Membership(IntegerIdModel):
begins_at = Column(DateTimeTz, nullable=True, index=True, server_default=func.current_timestamp())
ends_at = Column(DateTimeTz, nullable=True, index=True)
# many to one from Membership to Group
group_id = Column(Integer, ForeignKey(Group.id, ondelete="CASCADE"),
nullable=False, index=True)
group = relationship(Group, backref=backref("memberships",
cascade="all, delete-orphan",
order_by='Membership.id'))
# many to one from Membership to User
user_id = Column(Integer, ForeignKey(User.id, ondelete="CASCADE"),
nullable=False, index=True)
user = relationship(User, backref=backref("memberships",
cascade="all, delete-orphan"))
__table_args = (
CheckConstraint("begins_at IS NULL OR "
"ends_at IS NULL OR "
"begins_at <= ends_at")
)
@hybrid_method
def active(self, when=None):
"""
Tests if the membership overlaps with a given interval. If no interval
is given, it tests if the membership is active right now.
:param Interval when: interval in which the membership
:rtype: bool
"""
if when is None:
now = object_session(self).query(func.current_timestamp()).scalar()
when = single(now)
return when.overlaps(closed(self.begins_at, self.ends_at))
@active.expression
def active(cls, when=None):
"""
Tests if memberships overlap with a given interval. If no interval is
given, it tests if the memberships are active right now.
:param Interval when:
:return:
"""
if when is None:
now = session.utcnow()
when = single(now)
return and_(
or_(cls.begins_at == null(), literal(when.end) == null(),
cls.begins_at <= literal(when.end)),
or_(literal(when.begin) == null(), cls.ends_at == null(),
literal(when.begin) <= cls.ends_at)
).label("active")
@validates('ends_at')
def validate_ends_at(self, _, value):
if value is None:
return value
if self.begins_at is not None:
assert value >= self.begins_at,\
"begins_at must be before ends_at"
return value
@validates('begins_at')
def validate_begins_at(self, _, value):
if value is None:
return value
if self.ends_at is not None:
assert value <= self.ends_at,\
"begins_at must be before ends_at"
return value
def disable(self, ends_at=None):
if ends_at is None:
ends_at = object_session(self).query(func.current_timestamp()).scalar()
if self.begins_at is not None and self.begins_at > ends_at:
self.ends_at = self.begins_at
else:
self.ends_at = ends_at
class Spot(Base):
__tablename__ = 'spots'
spot_id = Column(Integer, primary_key=True)
king_id = Column(
Integer,
ForeignKey('user_infos.user_id',
use_alter=True,
name='fk_spot_kinginfo'))
queen_id = Column(
Integer,
ForeignKey('user_infos.user_id', use_alter=True, name='fk_spot_queen'))
name = Column(String(50))
lat = Column(Float)
lng = Column(Float)
description = Column(Text)
is_private = Column(Boolean)
price = Column(Float)
popularity = Column(Float)
created = Column(DateTime, default=func.current_timestamp())
assoc = relationship('SpotUserAssociation', backref='spot')
king = relationship('UserInfo',
primaryjoin='UserInfo.user_id==Spot.king_id')
queen = relationship('UserInfo',
primaryjoin='UserInfo.user_id==Spot.queen_id')
attrs = [
'spot_id',
'king_id',
'queen_id',
'name',
'lat',
'lng',
'description',
'is_private',
'price',
'popularity',
'created',
]
def __init__(self,
spot_id=None,
name=None,
lat=None,
lng=None,
description=None,
is_private=False,
price=None,
popularity=None,
created=None):
self.spot_id = spot_id
self.name = name
self.lat = lat
self.lng = lng
self.description = description
self.is_private = is_private
self.price = price
self.popularity = popularity
self.created = created
def __repr__(self):
return '<Spot {spot_id}>'.format(spot_id=self.spot_id)
class Author(db.Model):
"""
A document author, which may be a single person (in which case it's linked to a Person),
or it may be an agency.
"""
__tablename__ = "authors"
id = Column(Integer, primary_key=True)
name = Column(String(100), index=True, nullable=False, unique=True)
author_type_id = Column(Integer, ForeignKey('author_types.id'), nullable=False)
person_id = Column(Integer, ForeignKey('people.id', ondelete='SET NULL'))
created_at = Column(DateTime(timezone=True), index=True, unique=False, nullable=False, server_default=func.now())
updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.current_timestamp())
# Associations
author_type = relationship("AuthorType", lazy=False)
person = relationship("Person", lazy=False)
def json(self):
return {
'id': self.id,
'name': self.person.name if self.person else self.name,
'author_type': self.author_type.name,
}
def __repr__(self):
return "<Author id=%s, type=%s, name=\"%s\", person=%s>" % (self.id, self.author_type, self.person, self.name.encode('utf-8'))
@classmethod
def unknown(cls):
return cls.get_or_create('Unknown', AuthorType.unknown())
@classmethod
def get_or_create(cls, name, author_type, gender=None, race=None):
""" Get the author with this name or create it if it doesn't exist. """
a = Author.query.filter(Author.name == name).first()
if not a:
a = Author()
a.name = name[0:100]
a.author_type = author_type
if a.author_type.name in ['Journalist', 'Guest Writer']:
# create an associated person
a.person = Person.get_or_create(name, gender, race)
# force a db write (within the transaction) so subsequent lookups
# find this entity
db.session.add(a)
db.session.flush()
return a
def define_tables(cls, metadata):
default_generator = cls.default_generator = {"x": 50}
def mydefault():
default_generator["x"] += 1
return default_generator["x"]
def myupdate_with_ctx(ctx):
conn = ctx.connection
return conn.execute(sa.select(sa.text("13"))).scalar()
def mydefault_using_connection(ctx):
conn = ctx.connection
return conn.execute(sa.select(sa.text("12"))).scalar()
use_function_defaults = testing.against("postgresql", "mssql")
is_oracle = testing.against("oracle")
class MyClass(object):
@classmethod
def gen_default(cls, ctx):
return "hi"
class MyType(TypeDecorator):
impl = String(50)
def process_bind_param(self, value, dialect):
if value is not None:
value = "BIND" + value
return value
cls.f = 6
cls.f2 = 11
with testing.db.connect() as conn:
currenttime = cls.currenttime = func.current_date(type_=sa.Date)
if is_oracle:
ts = conn.scalar(
sa.select(
func.trunc(
func.current_timestamp(),
sa.literal_column("'DAY'"),
type_=sa.Date,
)
)
)
currenttime = cls.currenttime = func.trunc(
currenttime, sa.literal_column("'DAY'"), type_=sa.Date
)
def1 = currenttime
def2 = func.trunc(
sa.text("current_timestamp"),
sa.literal_column("'DAY'"),
type_=sa.Date,
)
deftype = sa.Date
elif use_function_defaults:
def1 = currenttime
deftype = sa.Date
if testing.against("mssql"):
def2 = sa.text("getdate()")
else:
def2 = sa.text("current_date")
ts = conn.scalar(func.current_date())
else:
def1 = def2 = "3"
ts = 3
deftype = Integer
cls.ts = ts
Table(
"default_test",
metadata,
# python function
Column("col1", Integer, primary_key=True, default=mydefault),
# python literal
Column(
"col2",
String(20),
default="imthedefault",
onupdate="im the update",
),
# preexecute expression
Column(
"col3",
Integer,
default=func.length("abcdef"),
onupdate=func.length("abcdefghijk"),
),
# SQL-side default from sql expression
Column("col4", deftype, server_default=def1),
# SQL-side default from literal expression
Column("col5", deftype, server_default=def2),
# preexecute + update timestamp
Column("col6", sa.Date, default=currenttime, onupdate=currenttime),
Column("boolcol1", sa.Boolean, default=True),
Column("boolcol2", sa.Boolean, default=False),
# python function which uses ExecutionContext
Column(
"col7",
Integer,
default=mydefault_using_connection,
onupdate=myupdate_with_ctx,
),
# python builtin
Column(
"col8",
sa.Date,
default=datetime.date.today,
onupdate=datetime.date.today,
),
# combo
Column("col9", String(20), default="py", server_default="ddl"),
# python method w/ context
Column("col10", String(20), default=MyClass.gen_default),
# fixed default w/ type that has bound processor
Column("col11", MyType(), default="foo"),
)
def utcnow():
return session.query(func.current_timestamp()).scalar()
def test_compare_current_timestamp_fn_w_binds(self):
self._compare_default_roundtrip(
DateTime(), func.timezone("utc", func.current_timestamp()))
class User(db.Model, UserMixin):
"""
A user who can login and work with Dexter.
"""
__tablename__ = "users"
id = Column(Integer, primary_key=True)
email = Column(String(50), index=True, nullable=False, unique=True)
first_name = Column(String(50), nullable=False)
last_name = Column(String(50), nullable=False)
admin = Column(Boolean, default=False)
disabled = Column(Boolean, default=False)
password = Column(String(100), default='')
default_analysis_nature_id = Column(Integer,
ForeignKey('analysis_natures.id'),
default=1,
nullable=False)
country_id = Column(Integer, ForeignKey('countries.id'), nullable=False)
created_at = Column(DateTime(timezone=True),
index=True,
unique=False,
nullable=False,
server_default=func.now())
updated_at = Column(DateTime(timezone=True),
server_default=func.now(),
onupdate=func.current_timestamp())
# associations
default_analysis_nature = relationship("AnalysisNature")
country = relationship("Country")
roles = db.relationship('Role',
secondary='roles_users',
backref=db.backref('users', lazy='dynamic'))
def short_name(self):
s = ""
if self.first_name:
s += self.first_name
if self.last_name:
if s:
s += " " + self.last_name[0] + "."
else:
s = self.last_name
if not s:
s = self.email
return s
def full_name(self):
s = '%s %s' % (self.first_name or '', self.last_name or '')
s = s.strip()
if not s:
s = self.email
return s
def __repr__(self):
return "<User email=%s>" % (self.email, )
# Flask-Security requires an active attribute
@property
def active(self):
return not self.disabled
@active.setter
def active(self, value):
self.disabled = not value
@classmethod
def create_defaults(self):
from . import Country
from flask_security.utils import encrypt_password
admin_user = User()
admin_user.first_name = "Admin"
admin_user.last_name = "Admin"
admin_user.admin = True
admin_user.email = "*****@*****.**"
admin_user.country = Country.query.filter(
Country.name == 'South Africa').one()
admin_user.password = encrypt_password('admin')
return [admin_user]
class User(BaseMixin, db.Model):
id = db.Column(db.Integer, primary_key=True)
uid = db.Column(db.String(200), nullable=False, unique=True)
name = db.Column(db.String(200), nullable=False)
uid = db.Column(db.String(200), nullable=False)
odesk_url = db.Column(db.String(200), nullable=False)
email = db.Column(db.String(200), nullable=False)
portrait_32_img = db.Column(db.String(200), nullable=True)
auth_token = db.Column(db.String(200), nullable=False, unique=True)
created_on = db.Column(db.DateTime, server_default=func.now())
updated_on = db.Column(db.DateTime, server_default=func.now(),
onupdate=func.current_timestamp())
@validates('email')
def validate_email(self, key, address):
assert '@' in address
return address
@classmethod
def get_hash(cls, token):
import hashlib
return hashlib.sha224(token).hexdigest()
@classmethod
def authenticate(cls, oauth_token, oauth_token_secret, oauth_verifier):
logging.debug(
'User Auth: try to authenticate with token %s', oauth_token)
from auth import OdeskAuth
auth = OdeskAuth()
_oauth_token, _oauth_token_secret = auth.authenticate(
oauth_token, oauth_token_secret, oauth_verifier)
info = auth.get_my_info(_oauth_token, _oauth_token_secret,
oauth_verifier)
user_info = auth.get_user_info(_oauth_token, _oauth_token_secret,
oauth_verifier)
logging.info(
'User Auth: authenticating user %s', info['user']['id'])
try:
user = User.query.filter_by(
uid=info['user']['id']).one()
except orm_exc.NoResultFound:
user = User()
user.uid = info['user']['id']
logging.debug('User Auth: new user %s added', user.uid)
import uuid
auth_token = str(uuid.uuid1())
user.auth_token = cls.get_hash(auth_token)
user.name = '{0} {1}'.format(
info['auth_user']['first_name'],
info['auth_user']['last_name'])
user.odesk_url = user_info['info']['profile_url']
user.portrait_32_img = user_info['info']['portrait_32_img']
user.email = info['user']['email']
user.save()
return auth_token, user
@classmethod
def get_auth_url(cls):
from auth import OdeskAuth
auth = OdeskAuth()
return auth.get_auth_url()
def __repr__(self):
return "%s <%s>" % (self.name, self.uid)
class UserMixin(FlaskUserMixin):
# login
uid = Column(GUID(), primary_key=True)
username = Column(String(32))
# profile
admin = Column(Boolean(), default=False)
face = Column(JSON_GEN())
points = Column(SmallInteger)
last_active = Column(TIMESTAMP,
server_default=func.now(),
onupdate=func.current_timestamp())
# oauth
access_token = Column(String(255), unique=True, nullable=False)
refresh_token = Column(String(255), index=True)
issued_at = Column(Integer, nullable=False, default=lambda: int(time()))
expires_in = Column(Integer, nullable=False, default=0)
def get_id(self):
return str(self.uid) if self.uid else None
@property
def expires_at(self):
if self.issued_at is None or self.expires_in is None:
# bugfix for records that have no expiry
return 0
return self.issued_at + self.expires_in
@property
def is_active(self):
return True
@property
def is_authenticated(self):
return self.uid is not None
@property
def is_anonymous(self):
return False
def get_user_id(self):
# used for oauth2
return self.uid
def __hash__(self):
return hash(self.uid)
def __repr__(self):
return "{}({}..)".format(self.username, str(self.uid)[0:4])
def update_token(self, th):
self.access_token = th.access_token
self.issued_at = th.issued_at
self.expires_in = th.expires_in
if hasattr(th, 'refresh_token'):
self.refresh_token = th.refresh_token
def update_user(self, user):
if self.access_token != user.access_token and user.access_token is not None:
self.update_token(user)
if hasattr(user, 'refresh_token'):
self.refresh_token = user.refresh_token
if user.username != self.username and user.username is not None:
self.username = user.username
if user.points != self.points and user.points is not None:
self.points = user.points
if user.face != self.face and user.face is not None:
self.face = user.face
if user.admin != self.admin and user.admin is not None:
self.admin = user.admin
if user.last_active != self.last_active and user.last_active is not None:
self.last_active = user.last_active
__all__ = [
'ATTR_TABLE', 'Attribute', 'and_', 'ENTITY_TABLE', 'Entity', 'func',
'METADATA', 'not_', 'or_', 'SESSION', 'select', 'VERSION',
'latest_version', 'CLUSTO_VERSIONING', 'Counter', 'ClustoVersioning',
'working_version', 'OperationalError', 'ClustoEmptyCommit'
]
METADATA = MetaData()
CLUSTO_VERSIONING = Table('clustoversioning',
METADATA,
Column('version', Integer, primary_key=True),
Column('timestamp',
TIMESTAMP,
default=func.current_timestamp(),
index=True),
Column('user', String(64), default=None),
Column('description', Text, default=None),
mysql_engine='InnoDB')
logging.basicConfig(format='%(asctime)s %(name)s %(levelname)s %(message)s')
audit_log = logging.getLogger('clusto.audit')
class ClustoEmptyCommit(Exception):
pass
class ClustoSession(sqlalchemy.orm.interfaces.SessionExtension):
def after_begin(self, session, transaction, connection):
class User(Base):
__tablename__ = 'users'
uid = Column(postgresql.UUID(as_uuid=True),
primary_key=True,
default=uuid.uuid4)
created_at = Column(TIMESTAMP,
server_default=func.now(),
onupdate=func.current_timestamp())
last_time = Column(TIMESTAMP,
server_default=func.now(),
onupdate=func.current_timestamp())
iso = Column(String(3))
wid = Column(postgresql.UUID(as_uuid=True))
username = Column(String(32))
email = Column(String(128))
salt = Column(String(128), nullable=True)
token = Column(String(128), nullable=True)
password = Column(String(128))
elo = Column(SmallInteger)
division = Column(SmallInteger)
def __init__(self, **kwargs):
self.uid = kwargs.get('uid')
self.email = kwargs.get('email')
self.username = kwargs.get('username')
self.iso = kwargs.get('iso')
self.wid = kwargs.get('wid')
self.salt = kwargs.get('salt')
self.token = kwargs.get('token')
self.password = kwargs.get('password')
self.elo = kwargs.get('elo')
self.division = kwargs.get('division')
# data conversion
if isinstance(self.wid, str):
self.wid = uuid.UUID(self.wid)
if isinstance(self.uid, str):
self.uid = uuid.UUID(self.uid)
def to_dict(self):
return {
"uid": self.uid,
"username": self.username,
"iso": self.iso,
"wid": str(self.wid) if self.wid else None,
"token": self.token,
"elo": self.elo,
"division": self.division,
}
def to_game_view(self):
return {
"username": self.username,
"division": self.division,
}
def __repr__(self):
return "{}({},{})".format(self.username, self.division, self.iso)
class Vulnerability(MainBase):
# __fulltext_columns__ = ('comment',)
__tablename__ = "vulnerability"
vcdb_id = Column(Integer, default=None, nullable=True)
version = Column(Integer, default=None, nullable=True)
prev_version = Column(Integer, default=None, nullable=True)
state = Column(Enum(VulnerabilityState),
default=VulnerabilityState.NEW,
nullable=False)
reviewer_id = Column(Integer,
ForeignKey(User.id, name='fk_reviewer_id'),
nullable=True)
reviewer = relationship(User, foreign_keys=[reviewer_id])
review_feedback = Column(Text)
feedback_reviewer_id = Column(Integer,
ForeignKey(User.id,
name='fk_feedback_reviewer_id'),
nullable=True)
feedback_reviewer = relationship(User, foreign_keys=[feedback_reviewer_id])
comment = Column(Text, nullable=False)
date_created = Column(DateTime,
default=func.current_timestamp(),
index=True)
exploit_exists = Column(Boolean, default=False)
# N.B. Don't use a ForeignKey constraint on NVD here as the nvd data might
# be updated dynamically (e.g. row deleted and then added by
# go-cve-dictionary).
cve_id = Column(String(255), nullable=True, index=True)
UniqueConstraint(vcdb_id, version, name='uk_vcdb_id_version')
creator_id = Column(Integer, ForeignKey(User.id), nullable=True)
creator = relationship(User, foreign_keys=[creator_id])
# TODO: Enable this once custom resource links are supported again.
# resource_links = relationship(
# VulnerabilityResources,
# backref="vulnerability",
# cascade="all, delete-orphan",
# single_parent=True,
# )
commits = relationship(
VulnerabilityGitCommits,
cascade="all, delete-orphan",
single_parent=True,
lazy="joined",
)
nvd = relationship(
"Nvd",
backref="vulns",
primaryjoin="remote(Nvd.cve_id) == foreign(Vulnerability.cve_id)",
)
_has_annotations = None
_permissions = {
PredefinedRoles.ADMIN: {
MANAGE: ALL,
},
PredefinedRoles.USER: {
READ: {
'state': VulnerabilityState.PUBLISHED,
},
UPDATE: lambda vuln, user: vuln.creator == user,
},
PredefinedRoles.REVIEWER: {
READ:
lambda vuln, user:
(vuln.state == VulnerabilityState.READY or vuln.state ==
VulnerabilityState.IN_REVIEW and vuln.reviewer == user),
ASSIGN:
True,
APPROVE:
True,
REJECT:
True,
}
}
def set_has_annotations(self, status: bool = True):
self._has_annotations = status
@hybrid_property
def has_annotations(self):
if self._has_annotations is not None:
return self._has_annotations
if self.commits:
for commit in self.commits:
if commit.comments:
self._has_annotations = True
return True
return False
@has_annotations.expression # type: ignore[no-redef]
def has_annotations(self):
return self.commits.any(VulnerabilityGitCommits.comments.any())
@property
def master_commit(self):
# TODO: refactor assumption that the first commit is the "master" one!
if self.commits and self.commits[0].commit_link:
return self.commits[0]
return None
def __repr__(self):
return f"Vulnerability Info({vars(self)})"
@classmethod
def get_by_vcdb_id(cls, id: str, published_only: bool = True): # pylint: disable=redefined-builtin
state_filter = None
if published_only:
state_filter = VulnerabilityState.PUBLISHED
return cls.query.filter_by(
vcdb_id=id,
state=state_filter).options(default_vuln_view_options).order_by(
cls.version.desc()).first()
@classmethod
def get_by_id(cls, id: str): # pylint: disable=redefined-builtin
return cls.query.filter_by(
id=id).options(default_vuln_view_options).first()
@classmethod
def get_by_cve_id(cls, cve_id: str, published_only: bool = True):
state_filter = None
if published_only:
state_filter = VulnerabilityState.PUBLISHED
return (cls.query.filter_by(
cve_id=cve_id,
state=state_filter).options(default_vuln_view_options).first())
@classmethod
def get_by_commit_hash(cls, commit_hash: str):
return (cls.query.join(
Vulnerability.commits,
aliased=True).filter_by(commit_hash=commit_hash).options(
default_vuln_view_options).first())
def to_json(self):
"""Prepare object for Json serialisation."""
products = [{
'vendor': v,
'product': p
} for v, p in self.nvd.get_products()]
cwes = [{'id': c.cwe_id, 'name': c.cwe_name} for c in self.nvd.cwes]
return {
'comment': self.comment,
'cve_id': self.cve_id,
'cwes': cwes,
'description': self.nvd.description,
'exploit_exists': self.exploit_exists,
'has_annotations': self.has_annotations,
'is_processed': True,
'langs': self.nvd.get_languages(),
'master_commit': self.master_commit.to_json(),
'products': products,
'references': [l.link for l in self.nvd.references],
'score': self.nvd.score,
}
def to_json_full(self):
"""Serialize object properties as dict."""
data = self.to_json()
# TODO: Enable this once custom resource_links are supported again.
#data['resource_links'] = [
# r.to_json() for r in self.resource_links
# if self.resource_links is not None
#]
data['commits'] = [
c.to_json() for c in self.commits if self.commits is not None
]
data['nvd'] = self.nvd._to_json_full(
) if self.nvd is not None else None
data['creator'] = self.creator.to_json(
) if self.creator is not None else None
data['date_created'] = self.date_created
data['date_modified'] = self.date_modified
return data
def update_state(self, new_state):
state_machine = VulnerabilityState(self)
state_machine.next_state(new_state)
self.state = new_state
def is_new(self):
return self.state == VulnerabilityState.NEW
def needs_improvement(self):
return self.state == VulnerabilityState.NEEDS_IMPROVEMENT
def is_reviewable(self):
return self.state == VulnerabilityState.READY
def is_in_review(self):
return self.state == VulnerabilityState.IN_REVIEW
def is_publishable(self):
return self.state == VulnerabilityState.REVIEWED
def is_reviewer(self, reviewer):
return self.reviewer == reviewer
def is_creator(self, creator):
return self.creator == creator
def has_feedback(self):
return bool(self.review_feedback)
def make_reviewable(self):
self.update_state(VulnerabilityState.READY)
def accept_review(self, reviewer):
self.reviewer = reviewer
self.update_state(VulnerabilityState.IN_REVIEW)
def deny_review(self):
self.reviewer = None
self.update_state(VulnerabilityState.READY)
def accept_change(self):
self.update_state(VulnerabilityState.REVIEWED)
def deny_change(self, reviewer, reason):
self.feedback_reviewer = reviewer
self.review_feedback = reason
self.update_state(VulnerabilityState.NEEDS_IMPROVEMENT)
def archive_entry(self):
self.update_state(VulnerabilityState.ARCHIVED)
def return_to_review_pool(self):
self.reviewer = None
self.update_state(VulnerabilityState.READY)
def publish_change(self):
self.version = self.next_version_number()
# TODO: Assign a new vcdb_id if required at this point. This is mostly relevant for fully new entry proposals.
self.update_state(VulnerabilityState.PUBLISHED)
def next_version_number(self):
prev_version = Vulnerability.query.filter_by(vcdb_id=self.vcdb_id) \
.with_entities(func.max(Vulnerability.version)) \
.first()[0]
if not prev_version:
prev_version = 0
return prev_version + 1
@staticmethod
def get_num_proposals_action_required(user):
return Vulnerability.query.filter(
Vulnerability.creator == user,
Vulnerability.state.in_(
[VulnerabilityState.NEW,
VulnerabilityState.NEEDS_IMPROVEMENT])).count()
@staticmethod
def get_num_proposals_pending(user):
return Vulnerability.query.filter(
Vulnerability.creator == user,
Vulnerability.state.in_([
VulnerabilityState.READY, VulnerabilityState.IN_REVIEW,
VulnerabilityState.REVIEWED
])).count()
@staticmethod
def get_num_proposals_publishing_pending():
return Vulnerability.query.filter(
Vulnerability.state == VulnerabilityState.REVIEWED).count()
@staticmethod
def get_num_proposals_unassigned():
return Vulnerability.query.filter(
Vulnerability.state == VulnerabilityState.READY).count()
@staticmethod
def get_num_proposals_assigned():
return Vulnerability.query.filter(
Vulnerability.state == VulnerabilityState.IN_REVIEW).count()
@staticmethod
def get_num_proposals_assigned_to(user):
return Vulnerability.query.filter(
Vulnerability.reviewer == user,
Vulnerability.state == VulnerabilityState.IN_REVIEW).count()
def copy(self):
new_vuln = copy_obj(self)
new_vuln.commits = []
for commit in self.commits:
new_vuln.commits.append(commit.copy())
# N.B. We never copy NVD data as it's supposed to stay read only.
return new_vuln
class Post(db.Model):
__tablename__ = 'post'
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String, nullable=False)
slug = db.Column(db.String, nullable=False, unique=True, index=True)
featured = db.Column(db.Boolean(), default=False, server_default=sql.expression.false(), nullable=False, index=True)
body = db.Column(db.Text)
date = db.Column(db.DateTime(timezone=True), index=True, unique=False, nullable=False, server_default=func.now())
files = db.relationship("PostFile", lazy='joined')
created_at = db.Column(db.DateTime(timezone=True), index=True, unique=False, nullable=False, server_default=func.now())
updated_at = db.Column(db.DateTime(timezone=True), server_default=func.now(), onupdate=func.current_timestamp())
@validates('slug')
def validate_slug(self, key, value):
return value.strip('/')
def __unicode__(self):
return unicode(self.title)
class Pod(db.Model):
"""Pod model."""
__tablename__ = 'pod'
id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
name = Column(String(255), nullable=False)
user_id = Column(UUID(as_uuid=True), ForeignKey('user.id', deferrable=True, initially='DEFERRED'), index=True, nullable=False)
base_id = Column(UUID(as_uuid=True), ForeignKey('base.id'), index=True)
user = relationship("User", back_populates="pod")
resources = relationship("Resource")
queue = relationship("Queue", uselist=False, back_populates="pod")
modules = relationship("Module", back_populates="pod")
researches = relationship("Research", back_populates="pod")
created_at = Column(DateTime, server_default=func.now(), nullable=False)
updated_at = Column(
DateTime, server_default=func.now(),
onupdate=func.current_timestamp(),
nullable=False,
)
def __init__(self, nickname):
"""Create a new pod."""
self.name = "{nickname}'s pod"
self.queue = Queue()
resources = []
for resource in ResourceTypes:
resources.append(Resource(resource.name))
self.resources = resources
def update_resource_production(self):
"""Update the production of each resource."""
return
def update_resources(self):
"""Update the current resource state in the db."""
for resource in self.resources:
time_diff = datetime.now() - resource.last_update
diff_in_seconds = time_diff.total_seconds()
resource_diff = diff_in_seconds * resource.production / 3600
resource.amount += resource_diff
if resource.amount < 0:
resource.amount = 0
elif resource.amount > resource.max_amount:
resource.amount = resource.max_amount
resource.last_update = datetime.now()
db.session.add(resource)
def get_by_name(self, name):
"""Get a resource by name."""
resource = next((r for r in self.resources if r.name == name), None)
if resource is None:
raise LookupError(f'No resource with name {name} found.')
return resource
def enough_resources(self, requirements) -> bool:
"""Check if there are enough resources for construction."""
enough = True
missing = {}
self.update_resources()
for requirement in requirements:
resource = self.get_by_name(requirement['type'])
amount = requirement['amount']
if resource is None:
print(f'Missing resource: {requirement["type"]}')
if resource.amount <= amount:
enough = False
missing[resource.name] = amount - resource.amount
return enough, missing
def subtract_resources(self, requirements) -> bool:
"""Check if there are enough resources for construction."""
for requirement in requirements:
resource = self.get_by_name(requirement['type'])
amount = requirement['amount']
if (resource.amount - amount) <= 0:
print(f"Can't afford resource {requirement['type']}: {resource.amount} of {amount}")
raise Exception
else:
resource.amount -= amount
db.session.add(resource)
db.session.commit()
return True
def add_resources(self, requirements) -> bool:
"""Check if there are enough resources for construction."""
for requirement in requirements:
resource = self.get_by_name(requirement['type'])
amount = requirement['amount']
resource.amount += amount
db.session.add(resource)
db.session.commit()
return True
def main(argv=sys.argv):
"""
Comment on updates that are eligible to be pushed to stable.
Queries for updates in the testing state that have a NULL request, looping over them looking for
updates that are eligible to be pushed to stable but haven't had comments from Bodhi to this
effect. For each such update it finds it will add a comment stating that the update may now be
pushed to stable.
This function is the entry point for the bodhi-approve-testing console script.
Args:
argv (list): A list of command line arguments. Defaults to sys.argv.
"""
logging.basicConfig(level=logging.ERROR)
if len(argv) != 2:
usage(argv)
settings = get_appsettings(argv[1])
initialize_db(settings)
db = Session()
buildsys.setup_buildsystem(config)
try:
testing = db.query(Update).filter_by(status=UpdateStatus.testing,
request=None)
for update in testing:
if not update.release.mandatory_days_in_testing and not update.autotime:
# If this release does not have any testing requirements and is not autotime,
# skip it
print(
f"{update.release.name} doesn't have mandatory days in testing"
)
continue
# If this update was already commented, skip it
if update.has_stable_comment:
continue
# If updates have reached the testing threshold, say something! Keep in mind
# that we don't care about karma here, because autokarma updates get their request set
# to stable by the Update.comment() workflow when they hit the required threshold. Thus,
# this function only needs to consider the time requirements because these updates have
# not reached the karma threshold.
if update.meets_testing_requirements:
print(f'{update.alias} now meets testing requirements')
# Only send email notification about the update reaching
# testing approval on releases composed by bodhi
update.comment(
db,
str(config.get('testing_approval_msg')),
author='bodhi',
email_notification=update.release.composed_by_bodhi)
notifications.publish(
update_schemas.UpdateRequirementsMetStableV1.from_dict(
dict(update=update)))
if update.autotime and update.days_in_testing >= update.stable_days:
print(f"Automatically marking {update.alias} as stable")
# For now only rawhide update can be created using side tag
# Do not add the release.pending_stable_tag if the update
# was created from a side tag.
if update.release.composed_by_bodhi:
update.set_request(db=db,
action=UpdateRequest.stable,
username="******")
# For updates that are not included in composes run by bodhi itself,
# mark them as stable
else:
# Single and Multi build update
conflicting_builds = update.find_conflicting_builds()
if conflicting_builds:
builds_str = str.join(", ", conflicting_builds)
update.comment(
db, "This update cannot be pushed to stable. "
f"These builds {builds_str} have a more recent "
f"build in koji's {update.release.stable_tag} tag.",
author="bodhi")
update.status = UpdateStatus.pending
update.request = None
update.remove_tag(
update.release.get_testing_side_tag(
update.from_tag))
db.commit()
continue
update.add_tag(update.release.stable_tag)
update.status = UpdateStatus.stable
update.request = None
update.pushed = True
update.date_stable = update.date_pushed = func.current_timestamp(
)
update.comment(
db,
"This update has been submitted for stable by bodhi",
author=u'bodhi')
# Multi build update
if update.from_tag:
# Merging the side tag should happen here
pending_signing_tag = update.release.get_pending_signing_side_tag(
update.from_tag)
testing_tag = update.release.get_testing_side_tag(
update.from_tag)
update.remove_tag(pending_signing_tag)
update.remove_tag(testing_tag)
update.remove_tag(update.from_tag)
koji = buildsys.get_session()
koji.deleteTag(pending_signing_tag)
koji.deleteTag(testing_tag)
# Removes the tag and the build target from koji.
koji.removeSideTag(update.from_tag)
else:
# Single build update
update.remove_tag(
update.release.pending_testing_tag)
update.remove_tag(
update.release.pending_stable_tag)
update.remove_tag(
update.release.pending_signing_tag)
update.remove_tag(update.release.candidate_tag)
db.commit()
except Exception as e:
print(str(e))
db.rollback()
Session.remove()
sys.exit(1)
def _baseline_4_expressions(self):
Zoo = self.metadata.tables["Zoo"]
Animal = self.metadata.tables["Animal"]
engine = self.metadata.bind
def fulltable(select):
"""Iterate over the full result table."""
return [list(row) for row in engine.execute(select).fetchall()]
for x in range(ITERATIONS):
assert len(fulltable(Zoo.select())) == 5
assert len(fulltable(Animal.select())) == ITERATIONS + 12
assert len(fulltable(Animal.select(Animal.c.Legs == 4))) == 4
assert len(fulltable(Animal.select(Animal.c.Legs == 2))) == 5
assert (
len(
fulltable(
Animal.select(
and_(Animal.c.Legs >= 2, Animal.c.Legs < 20)
)
)
)
== ITERATIONS + 9
)
assert len(fulltable(Animal.select(Animal.c.Legs > 10))) == 2
assert len(fulltable(Animal.select(Animal.c.Lifespan > 70))) == 2
assert (
len(fulltable(Animal.select(Animal.c.Species.startswith("L"))))
== 2
)
assert (
len(
fulltable(Animal.select(Animal.c.Species.endswith("pede")))
)
== 2
)
assert (
len(fulltable(Animal.select(Animal.c.LastEscape != None))) == 1
) # noqa
assert (
len(fulltable(Animal.select(None == Animal.c.LastEscape)))
== ITERATIONS + 11
) # noqa
# In operator (containedby)
assert (
len(fulltable(Animal.select(Animal.c.Species.like("%pede%"))))
== 2
)
assert (
len(
fulltable(
Animal.select(
Animal.c.Species.in_(["Lion", "Tiger", "Bear"])
)
)
)
== 3
)
# Try In with cell references
class thing(object):
pass
pet, pet2 = thing(), thing()
pet.Name, pet2.Name = "Slug", "Ostrich"
assert (
len(
fulltable(
Animal.select(
Animal.c.Species.in_([pet.Name, pet2.Name])
)
)
)
== 2
)
# logic and other functions
assert (
len(fulltable(Animal.select(Animal.c.Species.like("Slug"))))
== 1
)
assert (
len(fulltable(Animal.select(Animal.c.Species.like("%pede%"))))
== 2
)
name = "Lion"
assert (
len(
fulltable(
Animal.select(
func.length(Animal.c.Species) == len(name)
)
)
)
== ITERATIONS + 3
)
assert (
len(fulltable(Animal.select(Animal.c.Species.like("%i%"))))
== ITERATIONS + 7
)
# Test now(), today(), year(), month(), day()
assert (
len(
fulltable(
Zoo.select(
and_(
Zoo.c.Founded != None, # noqa
Zoo.c.Founded
< func.current_timestamp(_type=Date),
)
)
)
)
== 3
)
assert (
len(
fulltable(
Animal.select(
Animal.c.LastEscape
== func.current_timestamp(_type=Date)
)
)
)
== 0
)
assert (
len(
fulltable(
Animal.select(
func.date_part("year", Animal.c.LastEscape) == 2004
)
)
)
== 1
)
assert (
len(
fulltable(
Animal.select(
func.date_part("month", Animal.c.LastEscape) == 12
)
)
)
== 1
)
assert (
len(
fulltable(
Animal.select(
func.date_part("day", Animal.c.LastEscape) == 21
)
)
)
== 1
)
mysql.VARCHAR(length=_MAX_TWEET_USERNAME_LEN),
nullable=True,
server_default=""),
# Tweet contributors JSON object, NULL if none; also, NULL in legacy rows that
# predate this column.
# Source: contributors
Column("contributors", mysql.TEXT(convert_unicode=True), nullable=True),
# Timestamp when this row was stored
# NOTE: would have preferred DATETIME, but DATETIME with CURRENT_TIMESTAMP is
# not possible there until MySQL 5.6.5
Column("stored_at",
TIMESTAMP,
nullable=True,
server_default=func.current_timestamp()),
mysql_COLLATE=MYSQL_COLLATE,
mysql_CHARSET=MYSQL_CHARSET,
)
Index("created_at_idx", twitterTweets.c.created_at)
Index("stored_at_idx", twitterTweets.c.stored_at)
# Twitter metric data samples from twitter_direct_agent
# NOTE: some messages may match multiple metrics
twitterTweetSamples = Table(
"twitter_tweet_samples",
metadata,
# Sequence number; we save the sequence number of the most recently dispatched
# non-metric data item in the "emitted_non_metric_tracker" table
from sqlalchemy.orm import sessionmaker, scoped_session, relationship
from sqlalchemy.orm.exc import NoResultFound
from sqlalchemy import func
from sqlalchemy.dialects.postgresql import JSONB
from arrow.arrow import Arrow
from ztfperiodic.config import app
from flask_login.mixins import UserMixin
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy(app)
DBSession = scoped_session(sessionmaker())
EXECUTEMANY_PAGESIZE = 50000
utcnow = func.timezone('UTC', func.current_timestamp())
data_types = {
int: 'int',
float: 'float',
bool: 'bool',
dict: 'dict',
str: 'str',
list: 'list'
}
class Encoder(json.JSONEncoder):
"""Extends json.JSONEncoder with additional capabilities/configurations."""
def default(self, o):
if isinstance(o, (datetime, Arrow, date)):
class Vulnerability(MainBase):
# __fulltext_columns__ = ('comment',)
__tablename__ = "vulnerability"
vcdb_id = Column(Integer, default=None, nullable=True)
version = Column(Integer, default=None, nullable=True)
prev_version = Column(Integer, default=None, nullable=True)
state = Column(Enum(VulnerabilityState),
default=VulnerabilityState.NEW,
nullable=False)
reviewer_id = Column(Integer,
ForeignKey(User.id, name='fk_reviewer_id'),
nullable=True)
reviewer = relationship(User, foreign_keys=[reviewer_id])
review_feedback = Column(Text)
comment = Column(Text, nullable=False)
date_created = Column(DateTime,
default=func.current_timestamp(),
index=True)
exploit_exists = Column(Boolean, default=False)
# N.B. Don't use a ForeignKey constraint on NVD here as the nvd data might
# be updated dynamically (e.g. row deleted and then added by
# go-cve-dictionary).
cve_id = Column(String(255), nullable=True, index=True)
UniqueConstraint(vcdb_id, version, name='uk_vcdb_id_version')
creator_id = Column(Integer, ForeignKey(User.id), nullable=True)
creator = relationship(User, foreign_keys=[creator_id])
# TODO: Enable this once custom resource links are supported again.
# resource_links = relationship(
# VulnerabilityResources,
# backref="vulnerability",
# cascade="all, delete-orphan",
# single_parent=True,
# )
commits = relationship(
VulnerabilityGitCommits,
cascade="all, delete-orphan",
single_parent=True,
lazy="joined",
)
nvd = relationship(
"Nvd",
backref="vulns",
primaryjoin="remote(Nvd.cve_id) == foreign(Vulnerability.cve_id)",
)
_has_annotations = None
def set_has_annotations(self, status: bool = True):
self._has_annotations = status
@hybrid_property
def has_annotations(self):
if self._has_annotations is not None:
return self._has_annotations
if self.commits:
for commit in self.commits:
if commit.comments:
self._has_annotations = True
return True
return False
@has_annotations.expression # type: ignore[no-redef]
def has_annotations(self):
return self.commits.any(VulnerabilityGitCommits.comments.any())
@property
def master_commit(self):
# TODO: refactor assumption that the first commit is the "master" one!
if self.commits:
return self.commits[0]
return None
def __repr__(self):
return f"Vulnerability Info({vars(self)})"
@classmethod
def get_by_vcdb_id(cls, id: str): # pylint: disable=redefined-builtin
return cls.query.filter_by(
vcdb_id=id).options(default_vuln_view_options).first()
@classmethod
def get_by_id(cls, id: str): # pylint: disable=redefined-builtin
return cls.query.filter_by(
id=id).options(default_vuln_view_options).first()
@classmethod
def get_by_cve_id(cls, cve_id: str):
return (cls.query.filter_by(
cve_id=cve_id).options(default_vuln_view_options).first())
@classmethod
def get_by_commit_hash(cls, commit_hash: str):
return (cls.query.join(
Vulnerability.commits,
aliased=True).filter_by(commit_hash=commit_hash).options(
default_vuln_view_options).first())
def to_json(self):
"""Prepare object for Json serialisation."""
products = [{
'vendor': v,
'product': p
} for v, p in self.nvd.get_products()]
cwes = [{'id': c.cwe_id, 'name': c.cwe_name} for c in self.nvd.cwes]
return {
'comment': self.comment,
'cve_id': self.cve_id,
'cwes': cwes,
'description': self.nvd.description,
'exploit_exists': self.exploit_exists,
'has_annotations': self.has_annotations,
'is_processed': True,
'langs': self.nvd.get_languages(),
'master_commit': self.master_commit.to_json(),
'products': products,
'references': [l.link for l in self.nvd.references],
'score': self.nvd.score,
}
def to_json_full(self):
"""Serialize object properties as dict."""
data = self.to_json()
# TODO: Enable this once custom resource_links are supported again.
#data['resource_links'] = [
# r.to_json() for r in self.resource_links
# if self.resource_links is not None
#]
data['commits'] = [
c.to_json() for c in self.commits if self.commits is not None
]
data['nvd'] = self.nvd._to_json_full(
) if self.nvd is not None else None
data['creator'] = self.creator.to_json(
) if self.creator is not None else None
data['date_created'] = self.date_created
data['date_modified'] = self.date_modified
return data
class Tag(self.Base):
__tablename__ = 'tag'
id = Column(Integer, primary_key=True)
name = Column(Unicode)
updated_at = Column(DateTime, server_default=func.now(),
onupdate=func.current_timestamp())
