def update_templates_definitions(connection, migrator_id):
"""Updates assessment templates default_people value."""
template_table = all_models.AssessmentTemplate.__table__
template_entities = connection.execute(template_table.select().where(
template_table.c.template_object_type == "Control").where(
template_table.c.default_people.ilike("%Primary Contacts%")
| template_table.c.default_people.ilike("%Secondary Contacts%"))
).fetchall()
if template_entities:
for old_role_name, new_role_name in ROLE_MAPPING.iteritems():
connection.execute(template_table.update().where(
template_table.c.template_object_type == "Control").values(
default_people=func.replace(
template_table.c.default_people, old_role_name,
new_role_name),
updated_at=datetime.datetime.utcnow(),
modified_by_id=migrator_id))
if template_entities:
template_ids = [entity.id for entity in template_entities]
utils.add_to_objects_without_revisions_bulk(connection,
template_ids,
"AssessmentTemplate",
action="modified")
def update_control_cavs(connection):
"""Parse cavs from html to markdown."""
cavs_data = connection.execute(sa.text("""
select cav.id, cav.attribute_value, cav.attributable_id
from custom_attribute_values cav
join custom_attribute_definitions cad
on cad.id = cav.custom_attribute_id
where cad.definition_type = "control"
and attribute_value REGEXP :reg_exp
"""),
reg_exp=REGEX_HTML).fetchall()
controls_ids = {data[2] for data in cavs_data}
cavs_ids = {data[0] for data in cavs_data}
cavs_table = sa.sql.table(
'custom_attribute_values',
sa.Column('id', sa.Integer()),
sa.Column('attribute_value', sa.Text, nullable=False),
sa.Column('updated_at', sa.DateTime, nullable=False),
)
for cav_id, attribute_value, _ in cavs_data:
op.execute(cavs_table.update().values(
attribute_value=parse_html(attribute_value),
updated_at=datetime.datetime.utcnow(),
).where(cavs_table.c.id == cav_id))
utils.add_to_objects_without_revisions_bulk(
connection,
cavs_ids,
"CustomAttributeValue",
"modified",
)
return controls_ids
def migrate_url_to_reference_url(connection):
"""After the document epic document object should have 2 kinds
REFERENCE_URL - for urls
FILE - for gdrive files
"""
migration_user_id = migrator.get_migration_user_id(connection)
doc_ids = connection.execute(
text("SELECT d.id FROM documents d WHERE d.kind='URL'")).fetchall()
doc_ids = [d.id for d in doc_ids]
utils.add_to_objects_without_revisions_bulk(connection, doc_ids, "Document")
sql = """
UPDATE documents SET
kind='REFERENCE_URL',
modified_by_id=:modified_by_id,
updated_at=NOW()
WHERE kind='URL'
"""
connection.execute(text(sql),
modified_by_id=migration_user_id)
connection.execute(text("""
ALTER TABLE documents MODIFY
kind enum('FILE','REFERENCE_URL') NOT NULL DEFAULT 'REFERENCE_URL';
"""))
def update_control_cavs(connection):
"""Parse cavs from html to markdown."""
cavs_data = connection.execute(
sa.text("""
select cav.id, cav.attribute_value, cav.attributable_id
from custom_attribute_values cav
join custom_attribute_definitions cad
on cad.id = cav.custom_attribute_id
where cad.definition_type = "control"
and attribute_value REGEXP :reg_exp
"""),
reg_exp=REGEX_HTML
).fetchall()
controls_ids = {data[2] for data in cavs_data}
cavs_ids = {data[0] for data in cavs_data}
cavs_table = sa.sql.table(
'custom_attribute_values',
sa.Column('id', sa.Integer()),
sa.Column('attribute_value', sa.Text, nullable=False),
sa.Column('updated_at', sa.DateTime, nullable=False),
)
for cav_id, attribute_value, _ in cavs_data:
op.execute(cavs_table.update().values(
attribute_value=parse_html(attribute_value),
updated_at=datetime.datetime.utcnow(),
).where(cavs_table.c.id == cav_id))
utils.add_to_objects_without_revisions_bulk(
connection, cavs_ids, "CustomAttributeValue", "modified",
)
return controls_ids
def update_comments(connection):
"""Parse comments from html to markdown."""
comments_data = connection.execute(
sa.text("""
SELECT c.id, c.description
FROM comments as c
JOIN relationships as r
ON r.source_type = "Comment" and r.source_id = c.id
and r.destination_type = "Control"
WHERE c.description REGEXP :reg_exp
UNION
SELECT c.id, c.description
FROM comments as c
JOIN relationships as r
ON r.destination_type = "Comment" and r.destination_id = c.id
and r.source_type = "Control"
where c.description REGEXP :reg_exp
"""),
reg_exp=REGEX_HTML
).fetchall()
comments_ids = [c_id for c_id, _ in comments_data]
comments_table = sa.sql.table(
'comments',
sa.Column('id', sa.Integer()),
sa.Column('description', sa.Text, nullable=True),
sa.Column('updated_at', sa.DateTime, nullable=False),
)
for comment_id, description in comments_data:
op.execute(comments_table.update().values(
description=parse_html(description),
updated_at=datetime.datetime.utcnow(),
).where(comments_table.c.id == comment_id))
utils.add_to_objects_without_revisions_bulk(
connection, comments_ids, "Comment", "modified",
)
def update_revisions(connection, commentable_entities, model_name):
"""Updates revisions for updated entities."""
commentable_ids = [entity.id for entity in commentable_entities]
# add objects to objects without revisions
if commentable_ids:
utils.add_to_objects_without_revisions_bulk(connection, commentable_ids,
model_name)
def upgrade():
"""Upgrade database schema and/or data, creating a new revision."""
connection = op.get_bind()
program_sql = """
SELECT p.id
FROM programs AS p
LEFT JOIN revisions AS r
ON r.resource_type='Program' AND r.resource_id=p.id
WHERE r.id IS NULL
"""
programs = connection.execute(sa.text(program_sql)).fetchall()
programs_ids = [o.id for o in programs]
if programs_ids:
utils.add_to_objects_without_revisions_bulk(connection, programs_ids,
'Program')
audit_sql = """
SELECT a.id
FROM audits AS a
LEFT JOIN revisions AS r
ON r.resource_type='Audit' AND r.resource_id=a.id
WHERE r.id IS NULL
"""
audits = connection.execute(sa.text(audit_sql)).fetchall()
audits_ids = [o.id for o in audits]
if audits_ids:
utils.add_to_objects_without_revisions_bulk(connection, audits_ids,
'Audit')
def upgrade():
"""Upgrade database schema and/or data, creating a new revision."""
connection = op.get_bind()
program_sql = """
SELECT p.id
FROM programs AS p
LEFT JOIN revisions AS r
ON r.resource_type='Program' AND r.resource_id=p.id
WHERE r.id IS NULL
"""
programs = connection.execute(sa.text(program_sql)).fetchall()
programs_ids = [o.id for o in programs]
if programs_ids:
utils.add_to_objects_without_revisions_bulk(connection, programs_ids,
'Program')
audit_sql = """
SELECT a.id
FROM audits AS a
LEFT JOIN revisions AS r
ON r.resource_type='Audit' AND r.resource_id=a.id
WHERE r.id IS NULL
"""
audits = connection.execute(sa.text(audit_sql)).fetchall()
audits_ids = [o.id for o in audits]
if audits_ids:
utils.add_to_objects_without_revisions_bulk(connection, audits_ids,
'Audit')
def update_templates_definitions(connection, migrator_id):
"""Updates assessment templates default_people value."""
template_table = sa.sql.table(
"assessment_templates",
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('template_object_type', sa.String(length=250), nullable=True),
sa.Column('default_people', sa.Text(), nullable=False),
sa.Column('updated_at', sa.DateTime, nullable=False),
sa.Column('modified_by_id', sa.Integer, nullable=True)
)
template_entities = connection.execute(
template_table.select().where(
template_table.c.template_object_type == "Control"
).where(template_table.c.default_people.ilike("%Primary Contacts%") |
template_table.c.default_people.ilike("%Secondary Contacts%"))
).fetchall()
if template_entities:
for old_role_name, new_role_name in ROLE_MAPPING.iteritems():
connection.execute(template_table.update().where(
template_table.c.template_object_type == "Control"
).values(
default_people=func.replace(
template_table.c.default_people, old_role_name, new_role_name
),
updated_at=datetime.datetime.utcnow(),
modified_by_id=migrator_id
))
if template_entities:
template_ids = [entity.id for entity in template_entities]
utils.add_to_objects_without_revisions_bulk(
connection, template_ids, "AssessmentTemplate", action="modified"
)
def update_recipients(connection, migrator_id):
"""Updates recipients field for Control model. """
control_table = sa.sql.table(
"controls",
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('recipients', sa.String(length=250), nullable=True),
sa.Column('updated_at', sa.DateTime, nullable=False),
sa.Column('modified_by_id', sa.Integer, nullable=True)
)
# replace all None data with empty string for recipients field
for old_role_name, new_role_name in ROLE_MAPPING.iteritems():
connection.execute(control_table.update().values(
recipients=func.replace(control_table.c.recipients,
old_role_name, new_role_name),
updated_at=datetime.datetime.utcnow(),
modified_by_id=migrator_id
))
control_entities = connection.execute(
control_table.select().where(control_table.c.recipients != '')
).fetchall()
if control_entities:
control_ids = [entity.id for entity in control_entities]
utils.add_to_objects_without_revisions_bulk(connection, control_ids,
"Control", action="modified")
def update_templates_definitions(connection, migrator_id):
"""Updates assessment templates default_people value."""
template_table = sa.sql.table(
"assessment_templates", sa.Column('id', sa.Integer(), nullable=False),
sa.Column('template_object_type', sa.String(length=250),
nullable=True),
sa.Column('default_people', sa.Text(), nullable=False),
sa.Column('updated_at', sa.DateTime, nullable=False),
sa.Column('modified_by_id', sa.Integer, nullable=True))
template_entities = connection.execute(template_table.select().where(
template_table.c.template_object_type == "Control").where(
template_table.c.default_people.ilike("%Primary Contacts%")
| template_table.c.default_people.ilike("%Secondary Contacts%"))
).fetchall()
if template_entities:
for old_role_name, new_role_name in ROLE_MAPPING.iteritems():
connection.execute(template_table.update().where(
template_table.c.template_object_type == "Control").values(
default_people=func.replace(
template_table.c.default_people, old_role_name,
new_role_name),
updated_at=datetime.datetime.utcnow(),
modified_by_id=migrator_id))
if template_entities:
template_ids = [entity.id for entity in template_entities]
utils.add_to_objects_without_revisions_bulk(connection,
template_ids,
"AssessmentTemplate",
action="modified")
def migrate_url_to_reference_url(connection):
"""After the document epic document object should have 2 kinds
REFERENCE_URL - for urls
FILE - for gdrive files
"""
migration_user_id = migrator.get_migration_user_id(connection)
doc_ids = connection.execute(
text("SELECT d.id FROM documents d WHERE d.kind='URL'")).fetchall()
doc_ids = [d.id for d in doc_ids]
utils.add_to_objects_without_revisions_bulk(connection, doc_ids,
"Document")
sql = """
UPDATE documents SET
kind='REFERENCE_URL',
modified_by_id=:modified_by_id,
updated_at=NOW()
WHERE kind='URL'
"""
connection.execute(text(sql), modified_by_id=migration_user_id)
connection.execute(
text("""
ALTER TABLE documents MODIFY
kind enum('FILE','REFERENCE_URL') NOT NULL DEFAULT 'REFERENCE_URL';
"""))
def upgrade():
"""Upgrade database schema and/or data, creating a new revision."""
connection = op.get_bind()
acps = get_acp_without_revision(connection)
acps_ids = [acp.id for acp in acps]
utils.add_to_objects_without_revisions_bulk(connection,
acps_ids,
'AccessControlPerson')
def update_comment_relationships():
"""Replace 'Comment' object_type with 'ExternalComment' for relationships."""
connection = op.get_bind()
old_rels = connection.execute("""
SELECT id, source_type, source_id, destination_type, destination_id
FROM relationships
WHERE (source_type = 'Comment' AND destination_type = 'Control') OR
(destination_type = 'Comment' AND source_type = 'Control');
""").fetchall()
if old_rels:
migrator_id = utils.get_migration_user_id(connection)
for _, s_type, s_id, d_type, d_id in old_rels:
connection.execute(
sa.text("""
INSERT INTO relationships(
source_type, source_id, destination_type, destination_id,
modified_by_id, created_at, updated_at, is_external
)
VALUES(
:source_type, :source_id, :dest_type, :dest_id,
:modified_by_id, NOW(), NOW(), TRUE
);
"""),
source_type='ExternalComment' if s_type == 'Comment' else s_type,
source_id=s_id,
dest_type='ExternalComment' if d_type == 'Comment' else d_type,
dest_id=d_id,
modified_by_id=migrator_id,
)
new_rels = connection.execute("""
SELECT id
FROM relationships
WHERE source_type = 'ExternalComment' OR
destination_type = 'ExternalComment';
""").fetchall()
new_rel_ids = [rel.id for rel in new_rels]
utils.add_to_objects_without_revisions_bulk(
connection,
new_rel_ids,
"Relationship",
)
old_rel_ids = [rel.id for rel in old_rels]
connection.execute(
sa.text("""
DELETE FROM relationships
WHERE id IN :relationship_ids;
"""),
relationship_ids=old_rel_ids,
)
utils.add_to_objects_without_revisions_bulk(
connection,
old_rel_ids,
"Relationship",
action="deleted",
)
def create_revisions(conn, control_ids, assertion_ids):
"""Creates revisions for modified controls and their new assertions."""
if control_ids and assertion_ids:
utils.add_to_objects_without_revisions_bulk(
conn, control_ids, 'Control', action='modified'
)
utils.add_to_objects_without_revisions_bulk(
conn, assertion_ids, 'Categorization', action='created'
)
def upgrade():
"""Upgrade database schema and/or data, creating a new revision."""
connection = op.get_bind()
issues_for_update = get_issues_without_due_date(connection)
issues_ids = [issue['id'] for issue in issues_for_update]
for issue_id in issues_ids:
due_date = get_revision_due_date(connection, issue_id)
set_due_date(connection, issue_id, due_date)
utils.add_to_objects_without_revisions_bulk(connection, issues_ids, "Issue",
"modified")
def remove_cycle_task_entries(conn, old_comment_data):
"""Remove CycleTaskEntry data"""
cte_ids = [d.cte_id for d in old_comment_data]
if cte_ids:
conn.execute(
text("DELETE FROM cycle_task_entries WHERE id IN :cte_ids"),
cte_ids=cte_ids)
utils.add_to_objects_without_revisions_bulk(conn, cte_ids,
"CycleTaskEntry",
"deleted")
def _add_revisions(connection, cads):
"""Adds CADs to objects without revisions.
Args:
connection: sqlalchemy.engine.Connection object.
cads: List of CADs objects.
"""
cad_ids = [cad.id for cad in cads]
utils.add_to_objects_without_revisions_bulk(
connection, cad_ids, "ExternalCustomAttributeDefinition", "created")
def remove_task_group_objects(conn, data):
"""Remove TaskGroupOIbject data"""
tgo_ids = [d.tgo_id for d in data]
if tgo_ids:
conn.execute(
sa.text("DELETE FROM task_group_objects WHERE id IN :tgo_ids"),
tgo_ids=tgo_ids
)
utils.add_to_objects_without_revisions_bulk(
conn, tgo_ids, "TaskGroupObject", "deleted"
)
def remove_cycle_task_entries(conn, old_comment_data):
"""Remove CycleTaskEntry data"""
cte_ids = [d.cte_id for d in old_comment_data]
if cte_ids:
conn.execute(
text("DELETE FROM cycle_task_entries WHERE id IN :cte_ids"),
cte_ids=cte_ids
)
utils.add_to_objects_without_revisions_bulk(
conn, cte_ids, "CycleTaskEntry", "deleted"
)
def upgrade():
"""Upgrade database schema and/or data, creating a new revision."""
conn = op.get_bind()
invalid_acr = get_invalid_acrs()
if invalid_acr.count() > 0:
invalid_acr_ids = [x.id for x in invalid_acr]
add_to_objects_without_revisions_bulk(conn, invalid_acr_ids, acr,
"deleted")
delete_invalid_acr(conn, models_names)
def upgrade():
"""Upgrade database schema and/or data, creating a new revision."""
connection = op.get_bind()
update_comments(connection)
controls_ids_cavs = update_control_cavs(connection)
controls_ids_attr = update_control_attr(connection)
controls_ids = controls_ids_attr.union(controls_ids_cavs)
if controls_ids:
utils.add_to_objects_without_revisions_bulk(
connection, controls_ids, "Control", "modified",
)
def create_revisions(conn, control_ids, assertion_ids):
"""Creates revisions for modified controls and their new assertions."""
if control_ids and assertion_ids:
utils.add_to_objects_without_revisions_bulk(conn,
control_ids,
'Control',
action='modified')
utils.add_to_objects_without_revisions_bulk(conn,
assertion_ids,
'Categorization',
action='created')
def create_external_comments_revisions():
"""Create revisions for external comments."""
connection = op.get_bind()
result = connection.execute("""
SELECT id
FROM external_comments;
""").fetchall()
if result:
result = [i[0] for i in result]
utils.add_to_objects_without_revisions_bulk(
connection,
result,
"ExternalComment"
)
def _add_revisions(connection, obj_type):
"""Adds CAVs to objects without revisions.
Args:
connection: sqlalchemy.engine.Connection object.
obj_type: String representation of object type.
"""
cav_ids = _get_external_cav_ids(connection, obj_type)
utils.add_to_objects_without_revisions_bulk(
connection,
cav_ids,
"ExternalCustomAttributeValue",
"created"
)
def upgrade():
"""Upgrade database schema and/or data, creating a new revision."""
op.add_column("controls", sa.Column("kind", sa.String(250), nullable=True))
op.add_column("controls", sa.Column("means", sa.String(250), nullable=True))
op.add_column(
"controls",
sa.Column("verify_frequency", sa.String(250), nullable=True)
)
op.execute("""
UPDATE controls c
JOIN options o ON o.id = c.kind_id AND o.role = 'control_kind'
SET c.kind = o.title;
""")
op.execute("""
UPDATE controls c
JOIN options o ON o.id = c.means_id AND o.role = 'control_means'
SET c.means = o.title;
""")
op.execute("""
UPDATE controls c
JOIN options o ON o.id = c.verify_frequency_id AND
o.role = 'verify_frequency'
SET c.verify_frequency = o.title;
""")
op.drop_column("controls", "kind_id")
op.drop_column("controls", "means_id")
op.drop_column("controls", "verify_frequency_id")
connection = op.get_bind()
result = connection.execute("""
SELECT id
FROM options
WHERE role IN ('control_kind', 'control_means', 'verify_frequency');
""")
objects = result.fetchall()
objects_ids = [o.id for o in objects]
if objects_ids:
utils.add_to_objects_without_revisions_bulk(
connection,
objects_ids,
"Option",
action='deleted',
)
op.execute("""
DELETE FROM options
WHERE role IN ('control_kind', 'control_means', 'verify_frequency');
""")
def upgrade():
"""Upgrade database schema and/or data, creating a new revision."""
conn = op.get_bind()
migrator_id = utils.migrator.get_migration_user_id(conn)
people_without_profiles = get_people_without_profiles(conn)
profile_ids = []
for person in people_without_profiles:
profile_id = create_missing_profile(
conn, person.id, person_profile.default_last_seen_date(),
migrator_id)
profile_ids.append(profile_id)
utils.add_to_objects_without_revisions_bulk(conn, profile_ids,
"PersonProfile")
def upgrade():
"""Upgrade database schema and/or data, creating a new revision."""
connection = op.get_bind()
update_comments(connection)
risks_ids_cavs = update_risk_cavs(connection)
risks_ids_attr = update_risk_attr(connection)
risks_ids = risks_ids_attr.union(risks_ids_cavs)
if risks_ids:
utils.add_to_objects_without_revisions_bulk(
connection,
risks_ids,
"Risk",
"modified",
)
def _add_object_revisions(connection, cavs, obj_type):
"""Adds CAVs related objects to objects without revisions.
Args:
connection: sqlalchemy.engine.Connection object.
obj_type: String representation of object type.
cavs: List of CAVs objects.
"""
obj_ids = set(cav.attributable_id for cav in cavs)
utils.add_to_objects_without_revisions_bulk(
connection,
obj_ids,
obj_type.title(),
"modified"
)
def upgrade():
"""Upgrade database schema and/or data, creating a new revision."""
conn = op.get_bind()
ids = get_users_without_name(conn)
utils.add_to_objects_without_revisions_bulk(conn,
ids,
obj_type="Person",
action="modified")
migrator_id = utils.migrator.get_migration_user_id(conn)
op.execute("""
UPDATE people
SET name=SUBSTRING_INDEX(email, '@', 1), modified_by_id={migrator_id}
WHERE name=''
""".format(migrator_id=migrator_id))
def update_control_recipients(connection):
"""Update recipients for existing controls."""
op.execute("""
UPDATE controls
SET recipients = concat(recipients, ',Other Contacts')
""")
controls = connection.execute("""SELECT id FROM controls;""").fetchall()
control_ids = [c.id for c in controls]
utils.add_to_objects_without_revisions_bulk(
connection,
control_ids,
"Control",
action="modified",
)
def remove_comments(conn, data):
"""Removes comments from Comment table.
Args:
conn: An instance of SQLAlchemy connection.
data: List of comment ids to be deleted.
Returns:
-
"""
comment_ids = [d.id for d in data]
if comment_ids:
conn.execute(sa.text("""
DELETE FROM comments
WHERE id IN :comment_ids
"""),
comment_ids=comment_ids)
utils.add_to_objects_without_revisions_bulk(conn, comment_ids,
"Comment", "deleted")
def update_recipients(connection, migrator_id):
"""Updates recipients field for Control model. """
control_table = all_models.Control.__table__
for old_role_name, new_role_name in ROLE_MAPPING.iteritems():
connection.execute(control_table.update().values(
recipients=func.replace(control_table.c.recipients, old_role_name,
new_role_name),
updated_at=datetime.datetime.utcnow(),
modified_by_id=migrator_id))
control_entities = connection.execute(control_table.select().where(
control_table.c.recipients != '')).fetchall()
if control_entities:
control_ids = [entity.id for entity in control_entities]
utils.add_to_objects_without_revisions_bulk(connection,
control_ids,
"Control",
action="modified")
def migrate_docs(connection, migration_user_id):
"""Migrate documents.FILE to documents.URL"""
audit_doc_ids = get_audit_docs_ids(connection)
assessment_doc_ids = get_assessment_docs_ids(connection)
invalid_gdrive_url_doc_ids = get_invalid_gdrive_url_doc_ids(connection)
doc_ids = audit_doc_ids + assessment_doc_ids + invalid_gdrive_url_doc_ids
if doc_ids:
sql = """
UPDATE documents SET
document_type = 'URL',
modified_by_id = :modified_by_id
WHERE id IN :doc_ids
"""
connection.execute(text(sql), modified_by_id=migration_user_id,
doc_ids=doc_ids)
utils.add_to_objects_without_revisions_bulk(connection, doc_ids,
"Document", "modified")
def upgrade():
"""Upgrade database schema and/or data, creating a new revision."""
connection = op.get_bind()
proposals_to_cleanup = connection.execute(
sa.text("""
SELECT id, content
FROM proposals
WHERE content LIKE :proposal_content;
"""), proposal_content='%task_group_objects%').fetchall()
ids = []
for proposal in proposals_to_cleanup:
ids.append(proposal.id)
content = json.loads(proposal.content)
content['mapping_list_fields'].pop('task_group_objects', None)
connection.execute(
sa.text("""UPDATE proposals SET content=:content WHERE id=:id;"""),
content=json.dumps(content), id=proposal.id)
if ids:
utils.add_to_objects_without_revisions_bulk(
connection, ids, "Proposal", action="modified")
def upgrade():
"""Upgrade database schema and/or data, creating a new revision."""
connection = op.get_bind()
proposals_to_cleanup = connection.execute(
sa.text("""
SELECT id, content
FROM proposals
WHERE content LIKE :proposal_content;
"""), proposal_content='%task_group_objects%').fetchall()
ids = []
for proposal in proposals_to_cleanup:
ids.append(proposal.id)
content = json.loads(proposal.content)
content['mapping_list_fields'].pop('task_group_objects', None)
connection.execute(
sa.text("""UPDATE proposals SET content=:content WHERE id=:id;"""),
content=json.dumps(content), id=proposal.id)
if ids:
utils.add_to_objects_without_revisions_bulk(
connection, ids, "Proposal", action="modified")
def upgrade():
"""Upgrade database schema and/or data, creating a new revision."""
connection = op.get_bind()
models = [
'Assessment',
'Control',
]
for model in models:
query_sql = """
SELECT a.id
FROM %ss AS a
LEFT JOIN revisions AS r
ON r.resource_type='%s' AND r.resource_id=a.id
WHERE r.id IS NULL
""" % (model.lower(), model)
objects = connection.execute(sa.text(query_sql)).fetchall()
objects_ids = [o.id for o in objects]
if objects_ids:
utils.add_to_objects_without_revisions_bulk(connection, objects_ids,
model)
def add_missing_slugs(connection):
"""Generate missing slugs"""
migration_user_id = migrator.get_migration_user_id(connection)
doc_ids = connection.execute(
text("SELECT d.id FROM documents d WHERE d.slug=''")).fetchall()
doc_ids = [d.id for d in doc_ids]
utils.add_to_objects_without_revisions_bulk(connection, doc_ids,
"Document", action='modified')
op.execute('SET SESSION SQL_SAFE_UPDATES = 0')
sql = """
UPDATE documents SET
slug=CONCAT("DOCUMENT-",id),
modified_by_id=:modified_by_id,
updated_at=NOW()
WHERE slug=''
"""
connection.execute(text(sql),
modified_by_id=migration_user_id)
def migrate_docs(connection, migration_user_id):
"""Migrate documents.FILE to documents.URL"""
audit_doc_ids = get_audit_docs_ids(connection)
assessment_doc_ids = get_assessment_docs_ids(connection)
invalid_gdrive_url_doc_ids = get_invalid_gdrive_url_doc_ids(connection)
doc_ids = audit_doc_ids + assessment_doc_ids + invalid_gdrive_url_doc_ids
if doc_ids:
sql = """
UPDATE documents SET
document_type = 'URL',
modified_by_id = :modified_by_id
WHERE id IN :doc_ids
"""
connection.execute(text(sql),
modified_by_id=migration_user_id,
doc_ids=doc_ids)
utils.add_to_objects_without_revisions_bulk(connection, doc_ids,
"Document", "modified")
def upgrade():
"""Upgrade database schema and/or data, creating a new revision."""
connection = op.get_bind()
models = [
'Assessment',
'Control',
]
for model in models:
query_sql = """
SELECT a.id
FROM %ss AS a
LEFT JOIN revisions AS r
ON r.resource_type='%s' AND r.resource_id=a.id
WHERE r.id IS NULL
""" % (model.lower(), model)
objects = connection.execute(sa.text(query_sql)).fetchall()
objects_ids = [o.id for o in objects]
if objects_ids:
utils.add_to_objects_without_revisions_bulk(
connection, objects_ids, model)
def add_missing_slugs(connection):
"""Generate missing slugs"""
migration_user_id = migrator.get_migration_user_id(connection)
doc_ids = connection.execute(
text("SELECT d.id FROM documents d WHERE d.slug=''")).fetchall()
doc_ids = [d.id for d in doc_ids]
utils.add_to_objects_without_revisions_bulk(connection,
doc_ids,
"Document",
action='modified')
op.execute('SET SESSION SQL_SAFE_UPDATES = 0')
sql = """
UPDATE documents SET
slug=CONCAT("DOCUMENT-",id),
modified_by_id=:modified_by_id,
updated_at=NOW()
WHERE slug=''
"""
connection.execute(text(sql), modified_by_id=migration_user_id)
def update_comments(connection):
"""Parse comments from html to markdown.
Args:
connection: SQLAlchemy connection.
"""
comments_data = connection.execute(sa.text("""
SELECT c.id, c.description
FROM comments AS c
JOIN relationships AS r
ON r.source_type = "Comment" AND r.source_id = c.id
AND r.destination_type = "Risk"
WHERE c.description REGEXP :reg_exp
UNION
SELECT c.id, c.description
FROM comments AS c
JOIN relationships AS r
ON r.destination_type = "Comment" AND r.destination_id = c.id
AND r.source_type = "Risk"
WHERE c.description REGEXP :reg_exp
"""),
reg_exp=REGEX_HTML).fetchall()
comments_ids = [c_id for c_id, _ in comments_data]
comments_table = sa.sql.table(
'comments',
sa.Column('id', sa.Integer()),
sa.Column('description', sa.Text, nullable=True),
sa.Column('updated_at', sa.DateTime, nullable=False),
)
for comment_id, description in comments_data:
op.execute(comments_table.update().values(
description=parse_html(description),
updated_at=datetime.datetime.utcnow(),
).where(comments_table.c.id == comment_id))
utils.add_to_objects_without_revisions_bulk(
connection,
comments_ids,
"Comment",
"modified",
)
def upgrade():
"""Upgrade database schema and/or data, creating a new revision."""
connection = op.get_bind()
proposals_to_update = connection.execute(
sa.text("""
SELECT id, content
FROM proposals
WHERE instance_type='Control'
AND content LIKE '%key_control%'
OR content LIKE'%fraud_related%';
""")).fetchall()
ids = []
for proposal in proposals_to_update:
ids.append(proposal.id)
content = json.loads(proposal.content)
# modify values for `key_control`
if 'key_control' in content['fields']:
if content['fields']['key_control'] == '1':
content['fields']['key_control'] = True
elif content['fields']['key_control'] == '0':
content['fields']['key_control'] = False
# modify values for `fraud_related`
if 'fraud_related' in content['fields']:
if content['fields']['fraud_related'] == '1':
content['fields']['fraud_related'] = True
elif content['fields']['fraud_related'] == '0':
content['fields']['fraud_related'] = False
connection.execute(
sa.text("""UPDATE proposals SET content=:content WHERE id=:id;"""),
content=json.dumps(content),
id=proposal.id)
utils.add_to_objects_without_revisions_bulk(
connection,
ids,
"Proposal",
action="modified",
)
def update_risk_cavs(connection):
"""Parse cavs from html to markdown.
Args:
connection: SQLAlchemy connection.
Returns:
ids of risks for which cavs where updated.
"""
cavs_data = connection.execute(sa.text("""
SELECT cav.id, cav.attribute_value, cav.attributable_id
FROM custom_attribute_values AS cav
JOIN custom_attribute_definitions AS cad
ON cad.id = cav.custom_attribute_id
WHERE cad.definition_type = "risk"
AND attribute_value REGEXP :reg_exp
"""),
reg_exp=REGEX_HTML).fetchall()
risks_ids = {data[2] for data in cavs_data}
cavs_ids = {data[0] for data in cavs_data}
cavs_table = sa.sql.table(
'custom_attribute_values',
sa.Column('id', sa.Integer()),
sa.Column('attribute_value', sa.Text, nullable=False),
sa.Column('updated_at', sa.DateTime, nullable=False),
)
for cav_id, attribute_value, _ in cavs_data:
op.execute(cavs_table.update().values(
attribute_value=parse_html(attribute_value),
updated_at=datetime.datetime.utcnow(),
).where(cavs_table.c.id == cav_id))
utils.add_to_objects_without_revisions_bulk(
connection,
cavs_ids,
"CustomAttributeValue",
"modified",
)
return risks_ids
def upgrade():
"""Upgrade database schema and/or data, creating a new revision."""
connection = op.get_bind()
bugs = connection.execute(
'SELECT isi.id, isi.cc_list, GROUP_CONCAT(p.email) '
'AS auditors '
'FROM access_control_list acl '
'INNER JOIN access_control_roles acr ON acr.id = acl.ac_role_id '
'INNER JOIN assessments asmt ON asmt.audit_id = acl.object_id '
'INNER JOIN people p ON p.id = acl.person_id '
'INNER JOIN issuetracker_issues isi ON asmt.id = isi.object_id '
'WHERE acr.name = "Auditors" AND isi.object_type = "Assessment" '
'AND isi.cc_list != "" '
'GROUP BY isi.id, isi.cc_list '
'HAVING auditors != ""'
).fetchall()
update_bugs = {}
for bug_id, cc_list, auditors in bugs:
auditors = set(auditor.strip() for auditor in auditors.split(','))
emails = set(cc_list.split(','))
new_emails = set(email for email in emails if email not in auditors)
if emails != new_emails:
cc_list = ','.join(new_emails)
update_bugs[bug_id] = cc_list
if update_bugs:
for bug_id, cc_list in update_bugs.items():
op.execute('UPDATE issuetracker_issues isi SET isi.cc_list = "{}" '
'WHERE isi.id = {}'.format(cc_list, bug_id))
utils.add_to_objects_without_revisions_bulk(
connection, update_bugs.keys(), 'IssuetrackerIssue', 'modified'
)
def upgrade():
"""Upgrade database schema and/or data, creating a new revision."""
connection = op.get_bind()
proposals_to_update = connection.execute(
sa.text("""
SELECT id, content
FROM proposals
WHERE instance_type='Control'
AND content LIKE '%key_control%'
OR content LIKE'%fraud_related%';
""")).fetchall()
ids = []
for proposal in proposals_to_update:
ids.append(proposal.id)
content = json.loads(proposal.content)
# modify values for `key_control`
if 'key_control' in content['fields']:
if content['fields']['key_control'] == '1':
content['fields']['key_control'] = True
elif content['fields']['key_control'] == '0':
content['fields']['key_control'] = False
# modify values for `fraud_related`
if 'fraud_related' in content['fields']:
if content['fields']['fraud_related'] == '1':
content['fields']['fraud_related'] = True
elif content['fields']['fraud_related'] == '0':
content['fields']['fraud_related'] = False
connection.execute(
sa.text("""UPDATE proposals SET content=:content WHERE id=:id;"""),
content=json.dumps(content), id=proposal.id)
utils.add_to_objects_without_revisions_bulk(
connection, ids, "Proposal", action="modified",
)
