def downgrade():
op.create_table(
'bugs',
Column(u'id', INTEGER(), primary_key=True, nullable=False),
Column(u'status', TEXT()),
Column(u'resolution', TEXT()),
Column(u'short_desc', TEXT()),
)
class GraphicsDevice(DeclarativeBase):
__tablename__ = 'graphics_device'
graphics_device_id = Column(
u'graphics_device_id', INTEGER(), primary_key=True, nullable=False)
vendor_hex = Column(u'vendor_hex', TEXT())
adapter_hex = Column(u'adapter_hex', TEXT())
vendor_name = Column(u'vendor_name', TEXT())
adapter_name = Column(u'adapter_name', TEXT())
class Signature(DeclarativeBase):
__tablename__ = 'signatures'
# column definitions
first_build = Column(u'first_build', NUMERIC())
first_report = Column(u'first_report', TIMESTAMP(timezone=True))
signature = Column(u'signature', TEXT(), index=True, unique=True)
signature_id = Column(u'signature_id', INTEGER(),
primary_key=True, nullable=False)
class BugAssociation(DeclarativeBase):
__tablename__ = 'bug_associations'
# column definitions
bug_id = Column(u'bug_id',
INTEGER(),
primary_key=True,
nullable=False,
index=True)
signature = Column(u'signature', TEXT(), primary_key=True, nullable=False)
class SignatureProduct(DeclarativeBase):
__tablename__ = 'signature_products'
# column definitions
first_report = Column(u'first_report', TIMESTAMP(timezone=True))
product_version_id = Column(u'product_version_id',
INTEGER(),
primary_key=True,
nullable=False,
autoincrement=False,
index=True)
signature_id = Column(u'signature_id',
INTEGER(),
ForeignKey('signatures.signature_id'),
primary_key=True,
nullable=False)
# relationship definitions
signatures = relationship(
'Signature',
primaryjoin='SignatureProduct.signature_id==Signature.signature_id')
class BuildAdu(DeclarativeBase):
__tablename__ = 'build_adu'
# column definitions
product_version_id = Column(u'product_version_id',
INTEGER(),
primary_key=True,
nullable=False,
autoincrement=False)
build_date = Column(u'build_date',
DATE(),
primary_key=True,
nullable=False)
adu_date = Column(u'adu_date', DATE(), primary_key=True, nullable=False)
os_name = Column(u'os_name', CITEXT(), primary_key=True, nullable=False)
adu_count = Column(u'adu_count', INTEGER(), nullable=False)
__table_args__ = (Index('build_adu_key',
product_version_id,
build_date,
adu_date,
os_name,
unique=True), )
class ProductAdu(DeclarativeBase):
__tablename__ = 'product_adu'
# column definitions
adu_count = Column(u'adu_count',
BIGINT(),
nullable=False,
server_default=text('0'))
adu_date = Column(u'adu_date', DATE(), primary_key=True, nullable=False)
os_name = Column(u'os_name', CITEXT(), primary_key=True, nullable=False)
product_version_id = Column(u'product_version_id',
INTEGER(),
primary_key=True,
nullable=False,
autoincrement=False)
class ProductVersionBuild(DeclarativeBase):
__tablename__ = 'product_version_builds'
# column definitions
build_id = Column(u'build_id', NUMERIC(), primary_key=True, nullable=False)
platform = Column(u'platform', TEXT(), primary_key=True, nullable=False)
product_version_id = Column(u'product_version_id', INTEGER(), ForeignKey(
'product_versions.product_version_id'), primary_key=True, nullable=False)
repository = Column(u'repository', CITEXT())
# relationship definitions
product_versions = relationship(
'ProductVersion',
primaryjoin='ProductVersionBuild.product_version_id==ProductVersion.product_version_id'
)
class Signature(DeclarativeBase):
__tablename__ = 'signatures'
# column definitions
first_build = Column(u'first_build', NUMERIC())
first_report = Column(u'first_report', TIMESTAMP(timezone=True))
signature = Column(u'signature', TEXT(), index=True, unique=True)
signature_id = Column(u'signature_id', INTEGER(),
primary_key=True, nullable=False)
# relationship definitions
products = relationship(
'Product',
primaryjoin='Signature.signature_id==SignatureProductsRollup.signature_id',
secondary='SignatureProductsRollup',
secondaryjoin='SignatureProductsRollup.product_name==Product.product_name')
class Crontabber(DeclarativeBase):
__tablename__ = 'crontabber'
# column definitions
app_name = Column(u'app_name', TEXT(), primary_key=True, nullable=False)
next_run = Column(u'next_run', TIMESTAMP(timezone=True))
first_run = Column(u'first_run', TIMESTAMP(timezone=True))
last_run = Column(u'last_run', TIMESTAMP(timezone=True))
last_success = Column(u'last_success', TIMESTAMP(timezone=True))
error_count = Column(u'error_count', INTEGER(), server_default=text('0'))
depends_on = Column(u'depends_on', ARRAY(TEXT()))
last_error = Column(u'last_error', JSON())
ongoing = Column(u'ongoing', TIMESTAMP(timezone=True))
__table_args__ = (Index('crontabber_app_name_idx', app_name,
unique=True), )
class Swarms(Base):
__tablename__ = "swarms"
id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
health_status_id = Column(UUID(as_uuid=True),
ForeignKey(SwarmHealthStatuses.id),
nullable=False)
queen_year = Column(INTEGER(), nullable=False)
user_id = Column(UUID(as_uuid=True), ForeignKey(Users.id), nullable=False)
created_at = Column(TIMESTAMP(), default=datetime.datetime.utcnow)
updated_at = Column(TIMESTAMP(),
default=datetime.datetime.utcnow,
onupdate=datetime.datetime.utcnow)
deleted_at = Column(TIMESTAMP(), nullable=True)
user = relationship(Users, backref="swarms_healths")
health = relationship(SwarmHealthStatuses)
class CrontabberLog(DeclarativeBase):
__tablename__ = 'crontabber_log'
# column definitions
id = Column(u'id', INTEGER(), primary_key=True, nullable=False)
app_name = Column(u'app_name', TEXT(), nullable=False)
log_time = Column(u'log_time', TIMESTAMP(timezone=True), nullable=False,
server_default=text('NOW()'))
duration = Column(u'duration', INTERVAL())
success = Column(u'success', TIMESTAMP(timezone=True))
exc_type = Column(u'exc_type', TEXT())
exc_value = Column(u'exc_value', TEXT())
exc_traceback = Column(u'exc_traceback', TEXT())
__table_args__ = (
Index('crontabber_log_app_name_idx', app_name),
Index('crontabber_log_log_time_idx', log_time),
)
class RawAdiLogs(DeclarativeBase):
__tablename__ = 'raw_adi_logs'
# column definitions
report_date = Column(u'report_date', DATE())
product_name = Column(u'product_name', TEXT())
product_os_platform = Column(u'product_os_platform', TEXT())
product_os_version = Column(u'product_os_version', TEXT())
product_version = Column(u'product_version', TEXT())
build = Column(u'build', TEXT())
build_channel = Column(u'build_channel', TEXT())
product_guid = Column(u'product_guid', TEXT())
count = Column(u'count', INTEGER())
__mapper_args__ = {
"primary_key":
(report_date, product_name, product_os_platform, product_os_version,
product_version, build, build_channel, product_guid, count)
}
def test_no_attributes(self):
expected = []
result = list(get_type_attributes(INTEGER()))
self.assertEqual(expected, result)
class ProductVersion(DeclarativeBase):
__tablename__ = 'product_versions'
# column definitions
product_version_id = Column(u'product_version_id',
INTEGER(),
primary_key=True,
nullable=False)
product_name = Column(u'product_name',
CITEXT(),
ForeignKey('products.product_name'),
nullable=False,
index=True)
major_version = Column(u'major_version', MAJOR_VERSION(), index=True)
release_version = Column(u'release_version', CITEXT(), nullable=False)
version_string = Column(u'version_string', CITEXT(), nullable=False)
beta_number = Column(u'beta_number', INTEGER())
version_sort = Column(u'version_sort',
TEXT(),
nullable=False,
server_default="0",
index=True)
build_date = Column(u'build_date', DATE(), nullable=False)
sunset_date = Column(u'sunset_date', DATE(), nullable=False)
featured_version = Column(u'featured_version',
BOOLEAN(),
nullable=False,
server_default=text('False'))
build_type = Column(u'build_type',
CITEXT(),
nullable=False,
server_default='release') # DEPRECATED
has_builds = Column(u'has_builds', BOOLEAN())
is_rapid_beta = Column(u'is_rapid_beta',
BOOLEAN(),
server_default=text('False'))
rapid_beta_id = Column(u'rapid_beta_id', INTEGER(),
ForeignKey('product_versions.product_version_id'))
build_type_enum = Column(u'build_type_enum', build_type_enum())
# Above is a transition definition.
# We will rename build_type_enum to build_type once old CITEXT column
# is fully deprecated, also make this part of the primary key later. It
# will look like this:
# build_type = Column(
# u'build_type_enum', build_type_enum(), nullable=False, server_default='release')
version_build = Column(u'version_build', TEXT()) # Replaces 'beta_number'
__table_args__ = (Index('product_version_version_key',
product_name,
version_string,
unique=True), )
# relationship definitions
products = relationship(
'Product',
primaryjoin=
'ProductVersion.product_version_id==ProductVersion.rapid_beta_id',
secondary='ProductVersion',
secondaryjoin='ProductVersion.product_name==Product.product_name')
product_versions = relationship(
'ProductVersion',
primaryjoin=
'ProductVersion.rapid_beta_id==ProductVersion.product_version_id')
def downgrade():
"""
Because the writing of this migration coincides with the removal of
raw_sql/procs/update_gccrashes.sql and raw_sql/procs/backfill_gccrashes.sql
we can't rely on loading those files from disc.
So for the downgrade we'll just simply re-execute the necessary SQL.
"""
op.create_table(
'gccrashes',
Column(u'report_date', TIMESTAMP(timezone=True), nullable=False),
Column(u'product_version_id', INTEGER(), nullable=False),
Column(u'build', NUMERIC(), nullable=True),
Column(u'gc_count_madu', REAL(), nullable=False),
)
op.execute("""
CREATE OR REPLACE FUNCTION backfill_gccrashes(
updateday date, check_period interval DEFAULT '01:00:00'::interval) RETURNS boolean
LANGUAGE plpgsql
AS $$
BEGIN
-- function for administrative backfilling of gccrashes
-- designed to be called by backfill_matviews
DELETE FROM gccrashes WHERE report_date = updateday;
PERFORM update_gccrashes(updateday, false, check_period);
RETURN TRUE;
END;$$;
""")
op.execute("""
CREATE OR REPLACE FUNCTION update_gccrashes(
updateday date,
checkdata boolean DEFAULT true,
check_period interval DEFAULT '01:00:00'::interval
) RETURNS boolean
LANGUAGE plpgsql
SET client_min_messages TO 'ERROR'
AS $$
BEGIN
-- this procedure goes through raw crashes and creates a matview with count of
-- is_gc annotated crashes per build ID
-- designed to be run only once for each day
-- check that it hasn't already been run
IF checkdata THEN
PERFORM 1 FROM gccrashes
WHERE report_date = updateday LIMIT 1;
IF FOUND THEN
RAISE NOTICE 'gccrashes has already been run for the day %.',updateday;
RETURN FALSE;
END IF;
END IF;
-- check if reports_clean is complete
IF NOT reports_clean_done(updateday, check_period) THEN
IF checkdata THEN
RAISE NOTICE 'Reports_clean has not been updated to the end of %',updateday;
RETURN FALSE;
ELSE
RAISE INFO 'reports_clean not updated';
RETURN FALSE;
END IF;
END IF;
INSERT INTO gccrashes (
report_date,
product_version_id,
build,
gc_count_madu
)
WITH raw_crash_filtered AS (
SELECT
uuid
, json_object_field_text(r.raw_crash, 'IsGarbageCollecting')
as is_garbage_collecting
FROM
raw_crashes r
WHERE
date_processed BETWEEN updateday::timestamptz
AND updateday::timestamptz + '1 day'::interval
)
SELECT updateday
, product_version_id
, build
, crash_madu(sum(
CASE WHEN r.is_garbage_collecting = '1' THEN 1 ELSE 0 END), sum(adu_count), 1
) as gc_count_madu
FROM reports_clean
JOIN product_versions USING (product_version_id)
JOIN build_adu USING (product_version_id)
LEFT JOIN raw_crash_filtered r ON r.uuid::text = reports_clean.uuid
WHERE utc_day_is(date_processed, updateday)
AND tstz_between(date_processed, build_date(build), sunset_date)
AND product_versions.build_type = 'nightly'
AND tstz_between(adu_date, build_date(build), sunset_date)
AND adu_count > 0
AND build_date(build) = build_adu.build_date
AND date_processed - build_date(build) < '7 days'::interval
AND length(build::text) >= 10
GROUP BY build, product_version_id
ORDER BY build;
RETURN TRUE;
END;
$$;
""")
load_stored_proc(op, ['backfill_matviews.sql'])
class Correlations(DeclarativeBase):
__tablename__ = 'correlations'
# column definitions
id = Column(u'id',
INTEGER(),
primary_key=True,
autoincrement=True,
nullable=False)
product_version_id = Column(u'product_version_id',
INTEGER(),
nullable=False,
autoincrement=False,
index=True)
platform = Column(u'platform', TEXT(), primary_key=True, nullable=False)
signature_id = Column(u'signature_id',
INTEGER(),
primary_key=False,
nullable=False,
index=True)
key = Column(u'key', TEXT(), nullable=False)
count = Column(u'count',
INTEGER(),
nullable=False,
server_default=text('0'))
notes = Column(u'notes',
TEXT(),
primary_key=False,
nullable=False,
server_default='')
date = Column(u'date',
DATE(),
primary_key=False,
nullable=False,
index=True)
payload = Column(u'payload', JSON())
# When looking for signatures by the correlations you need to query by:
# product_version_id
# platform
# date
# key
#
# When looking for correlations for a specific signature you need:
# product_version_id
# platform
# key
# date
# signature
#
__table_args__ = (
Index(
'correlations_signatures_idx',
product_version_id,
platform,
key,
date,
),
Index('correlations_signature_idx',
product_version_id,
platform,
key,
date,
signature_id,
unique=True),
)
