def get_duplicates(db, param):
param.dupe_filter = esfilter2sqlwhere(db, {"terms": {"dupe": param.bug_list}})
param.dupe_of_filter = esfilter2sqlwhere(db, {"terms": {"dupe_of": param.bug_list}})
return db.query("""
SELECT dupe AS bug_id
, CAST(null AS signed) AS modified_ts
, CAST(null AS CHAR) AS modified_by
, 'dupe_of' AS field_name
, CAST(dupe_of AS SIGNED) AS new_value
, CAST(null AS SIGNED) AS old_value
, CAST(null AS signed) AS attach_id
, 2 AS _merge_order
FROM duplicates d
WHERE
{{dupe_filter}}
UNION
SELECT dupe_of
, null
, null
, 'dupe_by'
, CAST(dupe AS SIGNED)
, null
, null
, 2
FROM duplicates d
WHERE
{{dupe_of_filter}}
ORDER BY bug_id
""", param)
def get_dependencies(db, param):
param.blocks_filter = esfilter2sqlwhere(db, {"terms": {"blocked": param.bug_list}})
param.dependson_filter = esfilter2sqlwhere(db, {"terms": {"dependson": param.bug_list}})
return db.query("""
SELECT blocked AS bug_id
, CAST(null AS signed) AS modified_ts
, CAST(null AS CHAR) AS modified_by
, 'dependson' AS field_name
, CAST(dependson AS SIGNED) AS new_value
, CAST(null AS SIGNED) AS old_value
, CAST(null AS signed) AS attach_id
, 2 AS _merge_order
FROM dependencies d
WHERE
{{blocks_filter}}
UNION
SELECT dependson dependson
, null
, null
, 'blocked'
, CAST(blocked AS SIGNED)
, null
, null
, 2
FROM dependencies d
WHERE
{{dependson_filter}}
ORDER BY bug_id
""", param)
def get_new_activities(db, param):
get_screened_whiteboard(db)
if param.allow_private_bugs:
param.screened_fields = SQL(SCREENED_FIELDDEFS)
else:
param.screened_fields = SQL([-1])
#TODO: CF_LAST_RESOLVED IS IN PDT, FIX IT
param.bug_filter = esfilter2sqlwhere(db, {"terms": {"a.bug_id": param.bug_list}})
param.mixed_case_fields = SQL(MIXED_CASE)
param.screened_whiteboard = esfilter2sqlwhere(db, {"terms": {"m.group_id": SCREENED_BUG_GROUP_IDS}})
param.whiteboard_field = STATUS_WHITEBOARD_FIELD_ID
output = db.query("""
SELECT
a.bug_id,
UNIX_TIMESTAMP(CONVERT_TZ(bug_when, 'US/Pacific','UTC'))*1000 AS modified_ts,
lower(login_name) AS modified_by,
replace(field.`name`, '.', '_') AS field_name,
CAST(
CASE
WHEN a.fieldid IN {{screened_fields}} THEN '[screened]'
WHEN m.bug_id IS NOT NULL AND a.fieldid={{whiteboard_field}} AND added IS NOT NULL AND trim(added)<>'' THEN '[screened]'
WHEN a.fieldid IN {{mixed_case_fields}} THEN trim(added)
WHEN trim(added)='' THEN NULL
ELSE lower(trim(added))
END
AS CHAR CHARACTER SET utf8) AS new_value,
CAST(
CASE
WHEN a.fieldid IN {{screened_fields}} THEN '[screened]'
WHEN m.bug_id IS NOT NULL AND a.fieldid={{whiteboard_field}} AND removed IS NOT NULL AND trim(removed)<>'' THEN '[screened]'
WHEN a.fieldid IN {{mixed_case_fields}} THEN trim(removed)
WHEN trim(removed)='' THEN NULL
ELSE lower(trim(removed))
END
AS CHAR CHARACTER SET utf8) AS old_value,
attach_id,
9 AS _merge_order
FROM
bugs_activity a
JOIN
profiles p ON a.who = p.userid
JOIN
fielddefs field ON a.fieldid = field.`id`
LEFT JOIN
bug_group_map m on m.bug_id=a.bug_id AND {{screened_whiteboard}}
WHERE
{{bug_filter}}
# NEED TO QUERY ES TO GET bug_version_num OTHERWISE WE NEED ALL HISTORY
# AND bug_when >= {{start_time_str}}
ORDER BY
a.bug_id,
bug_when DESC,
attach_id
""", param)
return output
def get_comments(db, param):
if not param.bug_list:
return []
if param.allow_private_bugs:
param.comment_field = SQL("'[screened]' comment")
param.bug_filter = esfilter2sqlwhere(
db, {"and": [{
"terms": {
"bug_id": param.bug_list
}
}]})
else:
param.comment_field = SQL("c.thetext comment")
param.bug_filter = esfilter2sqlwhere(
db, {
"and": [{
"terms": {
"bug_id": param.bug_list
}
}, {
"term": {
"isprivate": 0
}
}]
})
try:
comments = db.query(
"""
SELECT
c.comment_id,
c.bug_id,
p.login_name modified_by,
UNIX_TIMESTAMP(CONVERT_TZ(bug_when, 'US/Pacific','UTC'))*1000 AS modified_ts,
{{comment_field}},
c.isprivate
FROM
longdescs c
LEFT JOIN
profiles p ON c.who = p.userid
LEFT JOIN
longdescs_tags t ON t.comment_id=c.comment_id AND t.tag <> 'deleted'
WHERE
{{bug_filter}} AND
bug_when >= {{start_time_str}}
""", param)
return comments
except Exception, e:
Log.error("can not get comment data", e)
def get_comments_by_id(db, comments, param):
"""
GET SPECIFIC COMMENTS
"""
if param.allow_private_bugs:
return []
param.comments_filter = esfilter2sqlwhere(db, {"and": [
{"term": {"isprivate": 0}},
{"terms": {"c.comment_id": comments}}
]})
try:
comments = db.query("""
SELECT
c.comment_id,
c.bug_id,
p.login_name modified_by,
UNIX_TIMESTAMP(CONVERT_TZ(bug_when, 'US/Pacific','UTC'))*1000 AS modified_ts,
c.thetext comment,
c.isprivate
FROM
longdescs c
LEFT JOIN
profiles p ON c.who = p.userid
LEFT JOIN
longdescs_tags t ON t.comment_id=c.comment_id AND t.tag <> 'deleted'
WHERE
{{comments_filter}}
""", param)
return comments
except Exception, e:
Log.error("can not get comment data", e)
def get_all_cc_changes(db, bug_list):
CC_FIELD_ID = 37
if not bug_list:
return []
return db.query("""
SELECT
bug_id,
CAST({{max_time}} AS signed) AS modified_ts,
CAST(null AS CHAR) AS new_value,
lower(CAST(p.login_name AS CHAR CHARACTER SET utf8)) AS old_value
FROM
cc
LEFT JOIN
profiles p ON cc.who = p.userid
WHERE
{{bug_filter}}
UNION ALL
SELECT
a.bug_id,
UNIX_TIMESTAMP(CONVERT_TZ(bug_when, 'US/Pacific','UTC'))*1000 AS modified_ts,
lower(CAST(trim(added) AS CHAR CHARACTER SET utf8)) AS new_value,
lower(CAST(trim(removed) AS CHAR CHARACTER SET utf8)) AS old_value
FROM
bugs_activity a
WHERE
a.fieldid = {{cc_field_id}} AND
{{bug_filter}}
""", {
"max_time": MAX_TIME,
"cc_field_id": CC_FIELD_ID,
"bug_filter": esfilter2sqlwhere(db, {"terms": {"bug_id": bug_list}})
})
def get_attachments(db, param):
"""
GET ALL CURRENT ATTACHMENTS
"""
if param.allow_private_bugs:
param.attachments_filter = SQL("1=1") #ALWAYS TRUE, ALLOWS ALL ATTACHMENTS
else:
param.attachments_filter = SQL("isprivate=0")
param.bug_filter = esfilter2sqlwhere(db, {"terms": {"bug_id": param.bug_list}})
output = db.query("""
SELECT bug_id
, UNIX_TIMESTAMP(CONVERT_TZ(a.creation_ts, 'US/Pacific','UTC'))*1000 AS modified_ts
, lower(login_name) AS modified_by
, UNIX_TIMESTAMP(CONVERT_TZ(a.creation_ts, 'US/Pacific','UTC'))*1000 AS created_ts
, login_name AS created_by
, ispatch AS 'attachments_ispatch'
, isobsolete AS 'attachments_isobsolete'
, isprivate AS 'attachments_isprivate'
, mimetype AS 'attachments_mimetype'
, attach_id
FROM
attachments a
JOIN profiles p ON a.submitter_id = p.userid
WHERE
{{bug_filter}} AND
{{attachments_filter}}
ORDER BY
bug_id,
attach_id,
a.creation_ts
""", param)
return flatten_attachments(output)
def get_flags(db, param):
param.bug_filter = esfilter2sqlwhere(db,
{"terms": {
"bug_id": param.bug_list
}})
return db.query(
"""
SELECT bug_id
, UNIX_TIMESTAMP(CONVERT_TZ(f.creation_date, 'US/Pacific','UTC'))*1000 AS modified_ts
, ps.login_name AS modified_by
, 'flagtypes_name' AS field_name
, CONCAT(ft.`name`,status,IF(requestee_id IS NULL,'',CONCAT('(',pr.login_name,')'))) AS new_value
, CAST(null AS CHAR) AS old_value
, attach_id
, 8 AS _merge_order
FROM
flags f
JOIN `flagtypes` ft ON f.type_id = ft.id
JOIN profiles ps ON f.setter_id = ps.userid
LEFT JOIN profiles pr ON f.requestee_id = pr.userid
WHERE
{{bug_filter}}
ORDER BY
bug_id
""", param)
def diff(db, table, old_record, new_record):
"""
UPDATE bugs_activity WITH THE CHANGES IN RECORDS
"""
now = milli2string(db, CNV.datetime2milli(get_current_time(db)))
changed = set(old_record.keys()) ^ set(new_record.keys())
changed |= set([k for k, v in old_record.items() if v != new_record[k]])
if table != u"bugs":
prefix = table + u"."
else:
prefix = u""
for c in changed:
fieldid=db.query("SELECT id FROM fielddefs WHERE name={{field_name}}", {"field_name": prefix + c})[0].id
if fieldid == None:
Log.error("Expecting a valid field name")
activity = Struct(
bug_id=old_record.bug_id,
who=1,
bug_when=now,
fieldid=fieldid,
removed=old_record[c],
added=new_record[c],
attach_id=old_record.attach_id,
comment_id=old_record.comment_id
)
db.insert("bugs_activity", activity)
db.execute("UPDATE bugs SET delta_ts={{now}} WHERE {{where}}", {
"now":now,
"where":esfilter2sqlwhere(db, {"term":{"bug_id":old_record.bug_id}})
})
def get_screened_whiteboard(db):
if not SCREENED_BUG_GROUP_IDS:
groups = db.query(
"SELECT id FROM groups WHERE {{where}}", {
"where":
esfilter2sqlwhere(
db, {"terms": {
"name": SCREENED_WHITEBOARD_BUG_GROUPS
}})
})
globals()["SCREENED_BUG_GROUP_IDS"] = Q.select(groups, "id")
def get_comments(db, param):
if not param.bug_list:
return []
if param.allow_private_bugs:
param.comment_field = SQL("'[screened]' comment")
param.bug_filter = esfilter2sqlwhere(db, {"and": [
{"terms": {"bug_id": param.bug_list}}
]})
else:
param.comment_field = SQL("c.thetext comment")
param.bug_filter = esfilter2sqlwhere(db, {"and": [
{"terms": {"bug_id": param.bug_list}},
{"term": {"isprivate": 0}}
]})
try:
comments = db.query("""
SELECT
c.comment_id,
c.bug_id,
p.login_name modified_by,
UNIX_TIMESTAMP(CONVERT_TZ(bug_when, 'US/Pacific','UTC'))*1000 AS modified_ts,
{{comment_field}},
c.isprivate
FROM
longdescs c
LEFT JOIN
profiles p ON c.who = p.userid
LEFT JOIN
longdescs_tags t ON t.comment_id=c.comment_id AND t.tag <> 'deleted'
WHERE
{{bug_filter}} AND
bug_when >= {{start_time_str}}
""", param)
return comments
except Exception, e:
Log.error("can not get comment data", e)
def get_dependencies(db, param):
param.blocks_filter = esfilter2sqlwhere(
db, {"terms": {
"blocked": param.bug_list
}})
param.dependson_filter = esfilter2sqlwhere(
db, {"terms": {
"dependson": param.bug_list
}})
return db.query(
"""
SELECT blocked AS bug_id
, CAST(null AS signed) AS modified_ts
, CAST(null AS CHAR) AS modified_by
, 'dependson' AS field_name
, CAST(dependson AS SIGNED) AS new_value
, CAST(null AS SIGNED) AS old_value
, CAST(null AS signed) AS attach_id
, 2 AS _merge_order
FROM dependencies d
WHERE
{{blocks_filter}}
UNION
SELECT dependson dependson
, null
, null
, 'blocked'
, CAST(blocked AS SIGNED)
, null
, null
, 2
FROM dependencies d
WHERE
{{dependson_filter}}
ORDER BY bug_id
""", param)
def get_duplicates(db, param):
param.dupe_filter = esfilter2sqlwhere(db,
{"terms": {
"dupe": param.bug_list
}})
param.dupe_of_filter = esfilter2sqlwhere(
db, {"terms": {
"dupe_of": param.bug_list
}})
return db.query(
"""
SELECT dupe AS bug_id
, CAST(null AS signed) AS modified_ts
, CAST(null AS CHAR) AS modified_by
, 'dupe_of' AS field_name
, CAST(dupe_of AS SIGNED) AS new_value
, CAST(null AS SIGNED) AS old_value
, CAST(null AS signed) AS attach_id
, 2 AS _merge_order
FROM duplicates d
WHERE
{{dupe_filter}}
UNION
SELECT dupe_of
, null
, null
, 'dupe_by'
, CAST(dupe AS SIGNED)
, null
, null
, 2
FROM duplicates d
WHERE
{{dupe_of_filter}}
ORDER BY bug_id
""", param)
def get_bug_groups(db, param):
param.bug_filter = esfilter2sqlwhere(db, {"terms": {"bug_id": param.bug_list}})
return db.query("""
SELECT bug_id
, CAST(null AS signed) AS modified_ts
, CAST(null AS CHAR) AS modified_by
, 'bug_group' AS field_name
, lower(CAST(g.`name` AS CHAR)) AS new_value
, CAST(null AS CHAR) AS old_value
, CAST(null AS signed) AS attach_id
, 2 AS _merge_order
FROM bug_group_map bg
JOIN groups g ON bg.group_id = g.id
WHERE
{{bug_filter}}
""", param)
def get_bug_see_also(db, param):
param.bug_filter = esfilter2sqlwhere(db, {"terms": {"bug_id": param.bug_list}})
return db.query("""
SELECT bug_id
, CAST(null AS signed) AS modified_ts
, CAST(null AS CHAR) AS modified_by
, 'see_also' AS field_name
, CAST(`value` AS CHAR) AS new_value
, CAST(null AS CHAR) AS old_value
, CAST(null AS signed) AS attach_id
, 2 AS _merge_order
FROM bug_see_also
WHERE
{{bug_filter}}
ORDER BY bug_id
""", param)
def get_keywords(db, param):
param.bug_filter = esfilter2sqlwhere(db, {"terms": {"bug_id": param.bug_list}})
return db.query("""
SELECT bug_id
, NULL AS modified_ts
, NULL AS modified_by
, 'keywords' AS field_name
, lower(kd.name) AS new_value
, NULL AS old_value
, NULL AS attach_id
, 2 AS _merge_order
FROM keywords k
JOIN keyworddefs kd ON k.keywordid = kd.id
WHERE
{{bug_filter}}
ORDER BY bug_id
""", param)
def get_tracking_flags(db, param):
param.bug_filter = esfilter2sqlwhere(db, {"terms": {"bug_id": param.bug_list}})
return db.query("""
SELECT
bug_id,
CAST({{start_time}} AS signed) AS modified_ts,
lower(f.name) AS field_name,
lower(t.value) AS new_value,
1 AS _merge_order
FROM
tracking_flags_bugs t
JOIN
tracking_flags f on f.id=t.tracking_flag_id
WHERE
{{bug_filter}}
ORDER BY
bug_id
""", param)
def get_cc(db, param):
param.bug_filter = esfilter2sqlwhere(db, {"terms": {"bug_id": param.bug_list}})
return db.query("""
SELECT bug_id
, CAST(null AS signed) AS modified_ts
, CAST(null AS CHAR) AS modified_by
, 'cc' AS field_name
, lower(CAST(p.login_name AS CHAR)) AS new_value
, CAST(null AS CHAR) AS old_value
, CAST(null AS signed) AS attach_id
, 2 AS _merge_order
FROM
cc
JOIN
profiles p ON cc.who = p.userid
WHERE
{{bug_filter}}
""", param)
def get_comments_by_id(db, comments, param):
"""
GET SPECIFIC COMMENTS
"""
if param.allow_private_bugs:
return []
param.comments_filter = esfilter2sqlwhere(db, {
"and": [{
"term": {
"isprivate": 0
}
}, {
"terms": {
"c.comment_id": comments
}
}]
})
try:
comments = db.query(
"""
SELECT
c.comment_id,
c.bug_id,
p.login_name modified_by,
UNIX_TIMESTAMP(CONVERT_TZ(bug_when, 'US/Pacific','UTC'))*1000 AS modified_ts,
c.thetext comment,
c.isprivate
FROM
longdescs c
LEFT JOIN
profiles p ON c.who = p.userid
LEFT JOIN
longdescs_tags t ON t.comment_id=c.comment_id AND t.tag <> 'deleted'
WHERE
{{comments_filter}}
""", param)
return comments
except Exception, e:
Log.error("can not get comment data", e)
def get_bug_groups(db, param):
param.bug_filter = esfilter2sqlwhere(db,
{"terms": {
"bug_id": param.bug_list
}})
return db.query(
"""
SELECT bug_id
, CAST(null AS signed) AS modified_ts
, CAST(null AS CHAR) AS modified_by
, 'bug_group' AS field_name
, lower(CAST(g.`name` AS CHAR)) AS new_value
, CAST(null AS CHAR) AS old_value
, CAST(null AS signed) AS attach_id
, 2 AS _merge_order
FROM bug_group_map bg
JOIN groups g ON bg.group_id = g.id
WHERE
{{bug_filter}}
""", param)
def get_bug_see_also(db, param):
param.bug_filter = esfilter2sqlwhere(db,
{"terms": {
"bug_id": param.bug_list
}})
return db.query(
"""
SELECT bug_id
, CAST(null AS signed) AS modified_ts
, CAST(null AS CHAR) AS modified_by
, 'see_also' AS field_name
, CAST(`value` AS CHAR) AS new_value
, CAST(null AS CHAR) AS old_value
, CAST(null AS signed) AS attach_id
, 2 AS _merge_order
FROM bug_see_also
WHERE
{{bug_filter}}
ORDER BY bug_id
""", param)
def get_flags(db, param):
param.bug_filter = esfilter2sqlwhere(db, {"terms": {"bug_id": param.bug_list}})
return db.query("""
SELECT bug_id
, UNIX_TIMESTAMP(CONVERT_TZ(f.creation_date, 'US/Pacific','UTC'))*1000 AS modified_ts
, ps.login_name AS modified_by
, 'flagtypes_name' AS field_name
, CONCAT(ft.`name`,status,IF(requestee_id IS NULL,'',CONCAT('(',pr.login_name,')'))) AS new_value
, CAST(null AS CHAR) AS old_value
, attach_id
, 8 AS _merge_order
FROM
flags f
JOIN `flagtypes` ft ON f.type_id = ft.id
JOIN profiles ps ON f.setter_id = ps.userid
LEFT JOIN profiles pr ON f.requestee_id = pr.userid
WHERE
{{bug_filter}}
ORDER BY
bug_id
""", param)
def diff(db, table, old_record, new_record):
"""
UPDATE bugs_activity WITH THE CHANGES IN RECORDS
"""
now = milli2string(db, CNV.datetime2milli(get_current_time(db)))
changed = set(old_record.keys()) ^ set(new_record.keys())
changed |= set([k for k, v in old_record.items() if v != new_record[k]])
if table != u"bugs":
prefix = table + u"."
else:
prefix = u""
for c in changed:
fieldid = db.query(
"SELECT id FROM fielddefs WHERE name={{field_name}}",
{"field_name": prefix + c})[0].id
if fieldid == None:
Log.error("Expecting a valid field name")
activity = Struct(bug_id=old_record.bug_id,
who=1,
bug_when=now,
fieldid=fieldid,
removed=old_record[c],
added=new_record[c],
attach_id=old_record.attach_id,
comment_id=old_record.comment_id)
db.insert("bugs_activity", activity)
db.execute(
"UPDATE bugs SET delta_ts={{now}} WHERE {{where}}", {
"now": now,
"where": esfilter2sqlwhere(db,
{"term": {
"bug_id": old_record.bug_id
}})
})
def get_keywords(db, param):
param.bug_filter = esfilter2sqlwhere(db,
{"terms": {
"bug_id": param.bug_list
}})
return db.query(
"""
SELECT bug_id
, NULL AS modified_ts
, NULL AS modified_by
, 'keywords' AS field_name
, lower(kd.name) AS new_value
, NULL AS old_value
, NULL AS attach_id
, 2 AS _merge_order
FROM keywords k
JOIN keyworddefs kd ON k.keywordid = kd.id
WHERE
{{bug_filter}}
ORDER BY bug_id
""", param)
def get_attachments(db, param):
"""
GET ALL CURRENT ATTACHMENTS
"""
if param.allow_private_bugs:
param.attachments_filter = SQL(
"1=1") #ALWAYS TRUE, ALLOWS ALL ATTACHMENTS
else:
param.attachments_filter = SQL("isprivate=0")
param.bug_filter = esfilter2sqlwhere(db,
{"terms": {
"bug_id": param.bug_list
}})
output = db.query(
"""
SELECT bug_id
, UNIX_TIMESTAMP(CONVERT_TZ(a.creation_ts, 'US/Pacific','UTC'))*1000 AS modified_ts
, lower(login_name) AS modified_by
, UNIX_TIMESTAMP(CONVERT_TZ(a.creation_ts, 'US/Pacific','UTC'))*1000 AS created_ts
, login_name AS created_by
, ispatch AS 'attachments_ispatch'
, isobsolete AS 'attachments_isobsolete'
, isprivate AS 'attachments_isprivate'
, mimetype AS 'attachments_mimetype'
, attach_id
FROM
attachments a
JOIN profiles p ON a.submitter_id = p.userid
WHERE
{{bug_filter}} AND
{{attachments_filter}}
ORDER BY
bug_id,
attach_id,
a.creation_ts
""", param)
return flatten_attachments(output)
def get_all_cc_changes(db, bug_list):
CC_FIELD_ID = 37
if not bug_list:
return []
return db.query(
"""
SELECT
bug_id,
CAST({{max_time}} AS signed) AS modified_ts,
CAST(null AS CHAR) AS new_value,
lower(CAST(p.login_name AS CHAR CHARACTER SET utf8)) AS old_value
FROM
cc
LEFT JOIN
profiles p ON cc.who = p.userid
WHERE
{{bug_filter}}
UNION ALL
SELECT
a.bug_id,
UNIX_TIMESTAMP(CONVERT_TZ(bug_when, 'US/Pacific','UTC'))*1000 AS modified_ts,
lower(CAST(trim(added) AS CHAR CHARACTER SET utf8)) AS new_value,
lower(CAST(trim(removed) AS CHAR CHARACTER SET utf8)) AS old_value
FROM
bugs_activity a
WHERE
a.fieldid = {{cc_field_id}} AND
{{bug_filter}}
""", {
"max_time": MAX_TIME,
"cc_field_id": CC_FIELD_ID,
"bug_filter": esfilter2sqlwhere(db,
{"terms": {
"bug_id": bug_list
}})
})
def get_cc(db, param):
param.bug_filter = esfilter2sqlwhere(db,
{"terms": {
"bug_id": param.bug_list
}})
return db.query(
"""
SELECT bug_id
, CAST(null AS signed) AS modified_ts
, CAST(null AS CHAR) AS modified_by
, 'cc' AS field_name
, lower(CAST(p.login_name AS CHAR)) AS new_value
, CAST(null AS CHAR) AS old_value
, CAST(null AS signed) AS attach_id
, 2 AS _merge_order
FROM
cc
JOIN
profiles p ON cc.who = p.userid
WHERE
{{bug_filter}}
""", param)
def get_tracking_flags(db, param):
param.bug_filter = esfilter2sqlwhere(db,
{"terms": {
"bug_id": param.bug_list
}})
return db.query(
"""
SELECT
bug_id,
CAST({{start_time}} AS signed) AS modified_ts,
lower(f.name) AS field_name,
lower(t.value) AS new_value,
1 AS _merge_order
FROM
tracking_flags_bugs t
JOIN
tracking_flags f on f.id=t.tracking_flag_id
WHERE
{{bug_filter}}
ORDER BY
bug_id
""", param)
def test_recent_private_stuff_does_not_show(self):
self.settings.param.allow_private_bugs = False
File(self.settings.param.first_run_time).delete()
File(self.settings.param.last_run_time).delete()
database.make_test_instance(self.settings.bugzilla)
es = elasticsearch.make_test_instance("candidate",
self.settings.real.bugs)
es_c = elasticsearch.make_test_instance("candidate_comments",
self.settings.real.comments)
bz_etl.main(self.settings, es, es_c)
#MARK SOME STUFF PRIVATE
with DB(self.settings.bugzilla) as db:
#BUGS
private_bugs = set(Random.sample(self.settings.param.bugs, 3))
Log.note("The private bugs are {{bugs}}", {"bugs": private_bugs})
for b in private_bugs:
database.add_bug_group(db, b, BUG_GROUP_FOR_TESTING)
#COMMENTS
comments = db.query("SELECT comment_id FROM longdescs").comment_id
marked_private_comments = Random.sample(comments, 5)
for c in marked_private_comments:
database.mark_comment_private(db, c, isprivate=1)
#INCLUDE COMMENTS OF THE PRIVATE BUGS
implied_private_comments = db.query(
"""
SELECT comment_id FROM longdescs WHERE {{where}}
""", {
"where":
esfilter2sqlwhere(db, {"terms": {
"bug_id": private_bugs
}})
}).comment_id
private_comments = marked_private_comments + implied_private_comments
Log.note("The private comments are {{comments}}",
{"comments": private_comments})
#ATTACHMENTS
attachments = db.query("SELECT bug_id, attach_id FROM attachments")
private_attachments = Random.sample(attachments, 5)
Log.note("The private attachments are {{attachments}}",
{"attachments": private_attachments})
for a in private_attachments:
database.mark_attachment_private(db, a.attach_id, isprivate=1)
if not File(self.settings.param.last_run_time).exists:
Log.error("last_run_time should exist")
bz_etl.main(self.settings, es, es_c)
Thread.sleep(2) # MUST SLEEP WHILE ES DOES ITS INDEXING
verify_no_private_bugs(es, private_bugs)
verify_no_private_attachments(es, private_attachments)
verify_no_private_comments(es_c, private_comments)
#MARK SOME STUFF PUBLIC
with DB(self.settings.bugzilla) as db:
for b in private_bugs:
database.remove_bug_group(db, b, BUG_GROUP_FOR_TESTING)
bz_etl.main(self.settings, es, es_c)
#VERIFY BUG IS PUBLIC, BUT PRIVATE ATTACHMENTS AND COMMENTS STILL NOT
Thread.sleep(2) # MUST SLEEP WHILE ES DOES ITS INDEXING
verify_public_bugs(es, private_bugs)
verify_no_private_attachments(es, private_attachments)
verify_no_private_comments(es_c, marked_private_comments)
def get_new_activities(db, param):
get_screened_whiteboard(db)
if param.allow_private_bugs:
param.screened_fields = SQL(SCREENED_FIELDDEFS)
else:
param.screened_fields = SQL([-1])
#TODO: CF_LAST_RESOLVED IS IN PDT, FIX IT
param.bug_filter = esfilter2sqlwhere(
db, {"terms": {
"a.bug_id": param.bug_list
}})
param.mixed_case_fields = SQL(MIXED_CASE)
param.screened_whiteboard = esfilter2sqlwhere(
db, {"terms": {
"m.group_id": SCREENED_BUG_GROUP_IDS
}})
param.whiteboard_field = STATUS_WHITEBOARD_FIELD_ID
output = db.query(
"""
SELECT
a.bug_id,
UNIX_TIMESTAMP(CONVERT_TZ(bug_when, 'US/Pacific','UTC'))*1000 AS modified_ts,
lower(login_name) AS modified_by,
replace(field.`name`, '.', '_') AS field_name,
CAST(
CASE
WHEN a.fieldid IN {{screened_fields}} THEN '[screened]'
WHEN m.bug_id IS NOT NULL AND a.fieldid={{whiteboard_field}} AND added IS NOT NULL AND trim(added)<>'' THEN '[screened]'
WHEN a.fieldid IN {{mixed_case_fields}} THEN trim(added)
WHEN trim(added)='' THEN NULL
ELSE lower(trim(added))
END
AS CHAR CHARACTER SET utf8) AS new_value,
CAST(
CASE
WHEN a.fieldid IN {{screened_fields}} THEN '[screened]'
WHEN m.bug_id IS NOT NULL AND a.fieldid={{whiteboard_field}} AND removed IS NOT NULL AND trim(removed)<>'' THEN '[screened]'
WHEN a.fieldid IN {{mixed_case_fields}} THEN trim(removed)
WHEN trim(removed)='' THEN NULL
ELSE lower(trim(removed))
END
AS CHAR CHARACTER SET utf8) AS old_value,
attach_id,
9 AS _merge_order
FROM
bugs_activity a
JOIN
profiles p ON a.who = p.userid
JOIN
fielddefs field ON a.fieldid = field.`id`
LEFT JOIN
bug_group_map m on m.bug_id=a.bug_id AND {{screened_whiteboard}}
WHERE
{{bug_filter}}
# NEED TO QUERY ES TO GET bug_version_num OTHERWISE WE NEED ALL HISTORY
# AND bug_when >= {{start_time_str}}
ORDER BY
a.bug_id,
bug_when DESC,
attach_id
""", param)
return output
def get_screened_whiteboard(db):
if not SCREENED_BUG_GROUP_IDS:
groups = db.query("SELECT id FROM groups WHERE {{where}}", {
"where": esfilter2sqlwhere(db, {"terms": {"name": SCREENED_WHITEBOARD_BUG_GROUPS}})
})
globals()["SCREENED_BUG_GROUP_IDS"] = Q.select(groups, "id")
def get_bugs(db, param):
try:
get_bugs_table_columns(db, db.settings.schema)
get_screened_whiteboard(db)
#TODO: CF_LAST_RESOLVED IS IN PDT, FIX IT
def lower(col):
if col.column_type.startswith("varchar"):
return "lower(" + db.quote_column(col.column_name) + ") " + db.quote_column(col.column_name)
else:
return db.quote_column(col.column_name)
param.bugs_columns = Q.select(bugs_columns, "column_name")
param.bugs_columns_SQL = SQL(",\n".join([lower(c) for c in bugs_columns]))
param.bug_filter = esfilter2sqlwhere(db, {"terms": {"b.bug_id": param.bug_list}})
param.screened_whiteboard = esfilter2sqlwhere(db, {"and": [
{"exists": "m.bug_id"},
{"terms": {"m.group_id": SCREENED_BUG_GROUP_IDS}}
]})
if param.allow_private_bugs:
param.sensitive_columns = SQL("""
'[screened]' short_desc,
'[screened]' bug_file_loc
""")
else:
param.sensitive_columns = SQL("""
short_desc,
bug_file_loc
""")
bugs = db.query("""
SELECT
b.bug_id,
UNIX_TIMESTAMP(CONVERT_TZ(b.creation_ts, 'US/Pacific','UTC'))*1000 AS modified_ts,
lower(pr.login_name) AS modified_by,
UNIX_TIMESTAMP(CONVERT_TZ(b.creation_ts, 'US/Pacific','UTC'))*1000 AS created_ts,
lower(pr.login_name) AS created_by,
lower(pa.login_name) AS assigned_to,
lower(pq.login_name) AS qa_contact,
lower(prod.`name`) AS product,
lower(comp.`name`) AS component,
CASE WHEN {{screened_whiteboard}} AND b.status_whiteboard IS NOT NULL AND trim(b.status_whiteboard)<>'' THEN '[screened]' ELSE trim(lower(b.status_whiteboard)) END status_whiteboard,
{{sensitive_columns}},
{{bugs_columns_SQL}}
FROM
bugs b
LEFT JOIN
profiles pr ON b.reporter = pr.userid
LEFT JOIN
profiles pa ON b.assigned_to = pa.userid
LEFT JOIN
profiles pq ON b.qa_contact = pq.userid
LEFT JOIN
products prod ON prod.id = product_id
LEFT JOIN
components comp ON comp.id = component_id
LEFT JOIN
bug_group_map m ON m.bug_id = b.bug_id
WHERE
{{bug_filter}}
""", param)
#bugs IS LIST OF BUGS WHICH MUST BE CONVERTED TO THE DELTA RECORDS FOR ALL FIELDS
output = []
for r in bugs:
flatten_bugs_record(r, output)
return output
except Exception, e:
Log.error("can not get basic bug data", e)
def test_recent_private_stuff_does_not_show(self):
self.settings.param.allow_private_bugs = False
File(self.settings.param.first_run_time).delete()
File(self.settings.param.last_run_time).delete()
database.make_test_instance(self.settings.bugzilla)
es = elasticsearch.make_test_instance("candidate", self.settings.real.bugs)
es_c = elasticsearch.make_test_instance("candidate_comments", self.settings.real.comments)
bz_etl.main(self.settings, es, es_c)
#MARK SOME STUFF PRIVATE
with DB(self.settings.bugzilla) as db:
#BUGS
private_bugs = set(Random.sample(self.settings.param.bugs, 3))
Log.note("The private bugs are {{bugs}}", {"bugs": private_bugs})
for b in private_bugs:
database.add_bug_group(db, b, BUG_GROUP_FOR_TESTING)
#COMMENTS
comments = db.query("SELECT comment_id FROM longdescs").comment_id
marked_private_comments = Random.sample(comments, 5)
for c in marked_private_comments:
database.mark_comment_private(db, c, isprivate=1)
#INCLUDE COMMENTS OF THE PRIVATE BUGS
implied_private_comments = db.query("""
SELECT comment_id FROM longdescs WHERE {{where}}
""", {
"where": esfilter2sqlwhere(db, {"terms":{"bug_id":private_bugs}})
}).comment_id
private_comments = marked_private_comments + implied_private_comments
Log.note("The private comments are {{comments}}", {"comments": private_comments})
#ATTACHMENTS
attachments = db.query("SELECT bug_id, attach_id FROM attachments")
private_attachments = Random.sample(attachments, 5)
Log.note("The private attachments are {{attachments}}", {"attachments": private_attachments})
for a in private_attachments:
database.mark_attachment_private(db, a.attach_id, isprivate=1)
if not File(self.settings.param.last_run_time).exists:
Log.error("last_run_time should exist")
bz_etl.main(self.settings, es, es_c)
Thread.sleep(2) # MUST SLEEP WHILE ES DOES ITS INDEXING
verify_no_private_bugs(es, private_bugs)
verify_no_private_attachments(es, private_attachments)
verify_no_private_comments(es_c, private_comments)
#MARK SOME STUFF PUBLIC
with DB(self.settings.bugzilla) as db:
for b in private_bugs:
database.remove_bug_group(db, b, BUG_GROUP_FOR_TESTING)
bz_etl.main(self.settings, es, es_c)
#VERIFY BUG IS PUBLIC, BUT PRIVATE ATTACHMENTS AND COMMENTS STILL NOT
Thread.sleep(2) # MUST SLEEP WHILE ES DOES ITS INDEXING
verify_public_bugs(es, private_bugs)
verify_no_private_attachments(es, private_attachments)
verify_no_private_comments(es_c, marked_private_comments)
def get_bugs(db, param):
try:
get_bugs_table_columns(db, db.settings.schema)
get_screened_whiteboard(db)
#TODO: CF_LAST_RESOLVED IS IN PDT, FIX IT
def lower(col):
if col.column_type.startswith("varchar"):
return "lower(" + db.quote_column(
col.column_name) + ") " + db.quote_column(col.column_name)
else:
return db.quote_column(col.column_name)
param.bugs_columns = Q.select(bugs_columns, "column_name")
param.bugs_columns_SQL = SQL(",\n".join(
[lower(c) for c in bugs_columns]))
param.bug_filter = esfilter2sqlwhere(
db, {"terms": {
"b.bug_id": param.bug_list
}})
param.screened_whiteboard = esfilter2sqlwhere(
db, {
"and": [{
"exists": "m.bug_id"
}, {
"terms": {
"m.group_id": SCREENED_BUG_GROUP_IDS
}
}]
})
if param.allow_private_bugs:
param.sensitive_columns = SQL("""
'[screened]' short_desc,
'[screened]' bug_file_loc
""")
else:
param.sensitive_columns = SQL("""
short_desc,
bug_file_loc
""")
bugs = db.query(
"""
SELECT
b.bug_id,
UNIX_TIMESTAMP(CONVERT_TZ(b.creation_ts, 'US/Pacific','UTC'))*1000 AS modified_ts,
lower(pr.login_name) AS modified_by,
UNIX_TIMESTAMP(CONVERT_TZ(b.creation_ts, 'US/Pacific','UTC'))*1000 AS created_ts,
lower(pr.login_name) AS created_by,
lower(pa.login_name) AS assigned_to,
lower(pq.login_name) AS qa_contact,
lower(prod.`name`) AS product,
lower(comp.`name`) AS component,
CASE WHEN {{screened_whiteboard}} AND b.status_whiteboard IS NOT NULL AND trim(b.status_whiteboard)<>'' THEN '[screened]' ELSE trim(lower(b.status_whiteboard)) END status_whiteboard,
{{sensitive_columns}},
{{bugs_columns_SQL}}
FROM
bugs b
LEFT JOIN
profiles pr ON b.reporter = pr.userid
LEFT JOIN
profiles pa ON b.assigned_to = pa.userid
LEFT JOIN
profiles pq ON b.qa_contact = pq.userid
LEFT JOIN
products prod ON prod.id = product_id
LEFT JOIN
components comp ON comp.id = component_id
LEFT JOIN
bug_group_map m ON m.bug_id = b.bug_id
WHERE
{{bug_filter}}
""", param)
#bugs IS LIST OF BUGS WHICH MUST BE CONVERTED TO THE DELTA RECORDS FOR ALL FIELDS
output = []
for r in bugs:
flatten_bugs_record(r, output)
return output
except Exception, e:
Log.error("can not get basic bug data", e)
