def add_admin_acl(conn, comment_id, user_id, comment_admin_acr_id):
"""Create Comment Admin ACL user_id -> CycleTaskEntry.modified_by_id"""
sql = """
INSERT INTO access_control_list(
ac_role_id,
object_id,
object_type,
created_at,
updated_at,
modified_by_id,
parent_id_nn
)VALUES(
:ac_role_id,
:object_id,
"Comment",
NOW(),
NOW(),
:modified_by_id,
0
)
"""
conn.execute(
text(sql),
ac_role_id=comment_admin_acr_id,
object_id=comment_id,
modified_by_id=user_id,
)
acl_id = utils.last_insert_id(conn)
utils.add_to_objects_without_revisions(conn, acl_id, "AccessControlList")
create_acp(conn, user_id, acl_id)
def upgrade():
"""Upgrade database schema and/or data, creating a new revision."""
connection = op.get_bind()
user_id = migrator.get_migration_user_id(connection)
query = acr_propagation.ACR_TABLE.insert().values(
name="Other Contacts",
object_type="Control",
created_at=datetime.datetime.utcnow(),
updated_at=datetime.datetime.utcnow(),
modified_by_id=user_id,
internal=False,
non_editable=True,
mandatory=False,
read=True,
update=True,
delete=True,
)
result = connection.execute(query)
utils.add_to_objects_without_revisions(
connection,
result.lastrowid,
"AccessControlRole"
)
acr_propagation.propagate_roles(
propagation_rule.GGRC_NEW_ROLES_PROPAGATION,
with_update=True
)
update_control_recipients(connection)
def create_relationship(conn, comment_id, cgot_id, migrator_id):
"""Create relationship between new Comment and CycleTaskGroupObjectTask"""
sql = """
INSERT INTO relationships(
modified_by_id,
created_at,
updated_at,
source_id,
source_type,
destination_id,
destination_type
) VALUES (
:modified_by_id,
NOW(),
NOW(),
:source_id,
:source_type,
:destination_id,
:destination_type
)
"""
conn.execute(text(sql),
modified_by_id=migrator_id,
source_id=comment_id,
source_type="Comment",
destination_id=cgot_id,
destination_type="CycleTaskGroupObjectTask")
rel_id = utils.last_insert_id(conn)
utils.add_to_objects_without_revisions(conn, rel_id, "Relationship")
def create_relationship(connection, source_id, source_type, doc_id):
"""Create relationship for new document"""
inserted_id = None
try:
sql = """
INSERT INTO relationships (
source_id,
source_type,
destination_id,
destination_type,
created_at,
updated_at,
is_external)
VALUES (:source_id, :source_type, :doc_id,
'Document', NOW(), NOW(), '0');
"""
connection.execute(text(sql),
source_id=source_id,
source_type=source_type,
doc_id=doc_id)
inserted_id = utils.last_insert_id(connection)
utils.add_to_objects_without_revisions(connection, inserted_id,
"Relationship")
except IntegrityError:
print "Relationship between Document: {} and {}: {} exists already"\
.format(doc_id, source_type, source_id)
return inserted_id
def assign_reviewer(conn, reviewer_id, reviewer_acr_id, review_id,
migrator_id):
"""Add reviewer acl for given review"""
sql = """
INSERT INTO access_control_list (
person_id,
ac_role_id,
object_id,
object_type,
created_at,
modified_by_id,
updated_at,
parent_id_nn
) VALUES (
:reviewer_id, :reviewer_acr_id, :review_id, 'Review', NOW(), :migrator_id,
NOW(), '0'
)
"""
conn.execute(text(sql),
reviewer_id=reviewer_id,
reviewer_acr_id=reviewer_acr_id,
review_id=review_id,
migrator_id=migrator_id)
acl_id = utils.last_insert_id(conn)
utils.add_to_objects_without_revisions(conn, acl_id, "AccessControlList")
def create_custom_attribute(conn,
name,
definition_type,
for_object,
helptext=None,
placeholder=None,
is_mandatory=False):
""" Create custom attribute for given object."""
migrator_id = migrator.get_migration_user_id(conn)
conn.execute(sa.text("""
INSERT INTO custom_attribute_definitions(
modified_by_id, created_at, updated_at,
title, helptext, placeholder,
definition_type, attribute_type, mandatory
)
VALUES(
:modified_by_id, NOW(), NOW(),
:title, :helptext, :placeholder,
:definition_type, :attribute_type, :mandatory
);
"""),
modified_by_id=migrator_id,
title=name,
helptext=helptext,
placeholder=placeholder,
definition_type=for_object,
attribute_type=definition_type,
mandatory=is_mandatory)
cad_id = utils.last_insert_id(conn)
utils.add_to_objects_without_revisions(conn, cad_id,
"CustomAttributeDefinition")
def create_review(conn, obj_id, obj_type, reviewer_id, last_reviewed_at,
migrator_id):
"""Create review record in DB"""
sql = """
INSERT INTO reviews(
reviewable_id,
reviewable_type,
notification_type,
last_reviewed_by_id,
last_reviewed_at,
status,
updated_at,
modified_by_id,
created_by_id,
created_at
) VALUES (
:obj_id, :obj_type, 'email', :reviewer_id, :last_reviewed_at,
'Reviewed', NOW(), :migrator_id, :migrator_id, NOW()
)
"""
conn.execute(text(sql),
obj_id=obj_id,
obj_type=obj_type,
reviewer_id=reviewer_id,
last_reviewed_at=last_reviewed_at,
migrator_id=migrator_id)
review_id = utils.last_insert_id(conn)
utils.add_to_objects_without_revisions(conn, obj_id, obj_type)
return review_id
def create_acl(connection, old_acl, new_doc_id):
"""Create new acl"""
sql = """
INSERT IGNORE INTO access_control_list(
person_id,
ac_role_id,
object_id,
object_type,
modified_by_id,
context_id,
updated_at,
created_at)
VALUES (:person_id, :ac_role_id, :object_id,
:object_type, :modified_by_id, :context_id, NOW(), NOW());
"""
connection.execute(text(sql),
person_id=old_acl.person_id,
ac_role_id=old_acl.ac_role_id,
object_id=new_doc_id,
object_type="Document",
modified_by_id=old_acl.modified_by_id,
context_id=old_acl.context_id)
acl_id = utils.last_insert_id(connection)
utils.add_to_objects_without_revisions(connection, acl_id,
"AccessControlList")
def create_old_rel_del_revisions(conn, data):
"""Remove old relationship revisions."""
old_rel_ids = [d.id for d in data]
if old_rel_ids:
for rel_id in old_rel_ids:
utils.add_to_objects_without_revisions(conn, rel_id,
"Relationship", "deleted")
def create_evidence(connection, doc, migration_user_id):
"""Create evidence record"""
sql = """
INSERT INTO evidence (
modified_by_id,
created_at,
updated_at,
title,
link,
description,
context_id,
kind,
source_gdrive_id,
gdrive_id)
VALUES (:modified_by_id, NOW(), NOW(), :title, :link,
:description, :context_id, :kind, :source_gdrive_id, :gdrive_id)
"""
connection.execute(text(sql),
modified_by_id=migration_user_id,
title=doc.title,
link=doc.link,
description=doc.description,
context_id=doc.context_id,
kind=doc.kind,
source_gdrive_id=doc.source_gdrive_id,
gdrive_id=doc.gdrive_id)
evidence_id = utils.last_insert_id(connection)
slug = 'EVIDENCE-{}'.format(evidence_id)
set_evidence_slug(connection, evidence_id, slug)
utils.add_to_objects_without_revisions(connection, evidence_id, "Evidence")
return evidence_id
def copy_acl(connection, acl, evidence_id, evid_admin_role_id,
migration_user_id):
"""Create copy of ACL object"""
sql = """
INSERT INTO access_control_list (
person_id,
ac_role_id,
object_id,
object_type,
modified_by_id,
context_id,
parent_id,
created_at,
updated_at)
VALUES (:person_id, :ac_role_id, :evidence_id, :object_type,
:modified_by_id, :context_id, :parent_id, NOW(), NOW())
"""
connection.execute(text(sql),
person_id=acl.person_id,
ac_role_id=evid_admin_role_id,
evidence_id=evidence_id,
object_type='Evidence',
modified_by_id=migration_user_id,
context_id=acl.context_id,
parent_id=acl.parent_id)
acl_id = utils.last_insert_id(connection)
utils.add_to_objects_without_revisions(connection, acl_id,
"AccessControlList")
def create_relationship(connection, source_id, source_type, evid_id,
migration_user_id):
"""Create relationship for Evidence"""
sql = """
INSERT INTO relationships (
source_id,
source_type,
destination_id,
destination_type,
created_at,
updated_at,
modified_by_id,
is_external)
VALUES (:source_id, :source_type, :evid_id,
'Evidence', NOW(), NOW(), :modified_by_id, '0');
"""
connection.execute(text(sql),
source_id=source_id,
source_type=source_type,
evid_id=evid_id,
modified_by_id=migration_user_id)
inserted_id = utils.last_insert_id(connection)
utils.add_to_objects_without_revisions(connection, inserted_id,
"Relationship")
return inserted_id
def add_acls(connection, object_id, object_type, role_name, migrator_id,
user_ids):
"""Create new acl item"""
acr_id = get_acr_id(connection, object_type, role_name)
sql = """
INSERT INTO access_control_list(
person_id, ac_role_id, object_id,
object_type, created_at, updated_at, modified_by_id
)
VALUES(
:person_id, :ac_role_id, :object_id,
:object_type, NOW(), NOW(), :migrator_id
)
"""
for user_id in user_ids:
connection.execute(text(sql),
person_id=user_id,
ac_role_id=acr_id,
object_id=object_id,
object_type=object_type,
migrator_id=migrator_id)
acl_id = utils.last_insert_id(connection)
utils.add_to_objects_without_revisions(connection, acl_id,
"AccessControlList")
def _add_ext_comment_rev(connection, comment_id):
"""Adds revision for external comment.
Args:
connection: An instance of SQLAlchemy connection.
comment_id: Id of comment.
"""
utils.add_to_objects_without_revisions(
connection,
comment_id,
"ExternalComment",
)
def update_cycle_task_groups(conn, ctg_id, ctg_status, migrator_id):
"""Set status of cycle_task_group Verified"""
if ctg_status != "Verified":
sql = """
UPDATE cycle_task_groups SET
status='Verified',
modified_by_id=:migrator_id,
updated_at=NOW()
WHERE id=:ctg_id
"""
conn.execute(text(sql), ctg_id=ctg_id, migrator_id=migrator_id)
utils.add_to_objects_without_revisions(conn, ctg_id, "CycleTaskGroup",
"modified")
def update_cycle(conn, cycle_id, cycle_status, migrator_id):
"""Set status of cycle Verified"""
if cycle_status != "Verified":
sql = """
UPDATE cycles SET
status='Verified',
modified_by_id=:migrator_id,
updated_at=NOW(),
is_current='0'
WHERE id=:cycle_id
"""
conn.execute(text(sql), cycle_id=cycle_id, migrator_id=migrator_id)
utils.add_to_objects_without_revisions(conn, cycle_id, "Cycle",
"modified")
def create_people_profile_acp(conn, people_id, ac_list_id):
"""Create new people_profile_acl"""
sql = """
INSERT INTO access_control_people(
person_id, ac_list_id, created_at, updated_at
)
VALUES (
:person_id, :ac_list_id, NOW(), NOW()
)
"""
conn.execute(text(sql), person_id=people_id, ac_list_id=ac_list_id)
acp_id = utils.last_insert_id(conn)
utils.add_to_objects_without_revisions(conn, acp_id, "AccessControlPerson")
def _add_ext_comment_rel_rev(connection, rel_id):
"""Adds relationships revision for external comments.
Args:
connection: An instance of SQLAlchemy connection.
rel_id: Id of relationship for comment.
Returns:
-
"""
utils.add_to_objects_without_revisions(
connection,
rel_id,
"Relationship",
)
def add_person_profile_admin_acr(conn):
"""Create new admin person_profile access_control_roles"""
op.execute("""
INSERT INTO access_control_roles (
name, object_type, `read`, `update`, `delete`, created_at, updated_at,
mandatory, default_to_current_user, non_editable
)
VALUES (
'Admin', 'PersonProfile', '1', '1', '0', NOW(), NOW(),
'1', '1', '1'
)
""")
acr_id = utils.last_insert_id(conn)
utils.add_to_objects_without_revisions(conn, acr_id, "AccessControlRole")
def _remove_comment_rev(connection, comment_id):
"""Removes comment revision.
Args:
connection: An instance of SQLAlchemy connection.
comment_id: Id of comment.
Returns:
-
"""
utils.add_to_objects_without_revisions(
connection,
comment_id,
"Comment",
action="deleted",
)
def _remove_comment_rel_rev(connection, rel_id):
"""Removes relationships revision for comment.
Args:
connection: An instance of SQLAlchemy connection.
rel_id: Id of comment relationship.
Returns:
-
"""
utils.add_to_objects_without_revisions(
connection,
rel_id,
"Relationship",
action="deleted",
)
def create_ggrc_user(connection, email, name, migrator_id):
"""Create new ggrc user"""
sql = """
INSERT INTO people
(email, name, created_at, updated_at, modified_by_id)
VALUES
(:email, :name, NOW(), NOW(), :migrator_id)
"""
connection.execute(text(sql),
email=email,
name=name,
migrator_id=migrator_id)
user_id = utils.last_insert_id(connection)
make_creator(connection, user_id, migrator_id)
utils.add_to_objects_without_revisions(connection, user_id, "Person")
return user_id
def make_creator(connection, user_id, migrator_id):
"""Grant Global Creator permissions to given user"""
creator_role_id = get_global_creator_role_id(connection)
sql = """
INSERT INTO user_roles
(person_id, role_id, modified_by_id, created_at, updated_at)
VALUES
(:user_id, :creator_role_id, :migrator_id, NOW(), NOW())
"""
connection.execute(text(sql),
user_id=user_id,
creator_role_id=creator_role_id,
migrator_id=migrator_id)
user_role_id = utils.last_insert_id(connection)
utils.add_to_objects_without_revisions(connection, user_role_id,
"UserRole")
def create_people_profile_acl(conn, profile_id, ac_role_id):
"""Create new people_profile_acl"""
sql = """
INSERT INTO access_control_list(
object_id, object_type, ac_role_id, created_at, updated_at
)
VALUES (
:object_id, :object_type, :ac_role_id, NOW(), NOW()
)
"""
conn.execute(text(sql),
object_id=profile_id,
object_type='PersonProfile',
ac_role_id=ac_role_id)
acl_id = utils.last_insert_id(conn)
utils.add_to_objects_without_revisions(conn, acl_id, "AccessControlList")
return acl_id
def copy_document(connection, old_doc):
"""Create copy of document object and return it's id"""
sql = """
INSERT INTO documents (
modified_by_id,
created_at,
updated_at,
title,
link,
description,
context_id,
kind,
source_gdrive_id,
gdrive_id,
slug,
status,
recipients,
send_by_default
)
VALUES (:modified_by_id, NOW(), NOW(), :title, :link,
:description, :context_id, :kind,
:source_gdrive_id, :gdrive_id, :slug, :status,
:recipients, :send_by_default)
"""
connection.execute(text(sql),
modified_by_id=old_doc.modified_by_id,
title=old_doc.title,
link=old_doc.link,
description=old_doc.description,
context_id=old_doc.context_id,
source_gdrive_id=old_doc.source_gdrive_id,
gdrive_id=old_doc.gdrive_id,
status=old_doc.status,
recipients=old_doc.recipients,
send_by_default=old_doc.send_by_default,
slug="TMP_SLUG",
kind="FILE")
new_doc_id = utils.last_insert_id(connection)
update_document_slug(connection, new_doc_id)
utils.add_to_objects_without_revisions(connection, new_doc_id, "Document")
return new_doc_id
def _remove_comment(connection, comment_id):
"""Removes comment from Comment table.
Args:
connection: An instance of SQLAlchemy connection.
comment_id: Id of comment in Comment table.
Returns:
-
"""
connection.execute(
sa.text("""
DELETE FROM comments
WHERE id = :comment_id
"""),
comment_id=comment_id)
utils.add_to_objects_without_revisions(
connection,
comment_id,
"Comment",
action="deleted",
)
def process_object(connection, object_type, object_id, migrator_id):
"""Process role coping logic for single object"""
external_data = get_ggrc_data(connection, object_type, object_id)
if external_data:
try:
process_with_external_data(connection, object_id, object_type,
external_data, migrator_id)
except Exception as e: # pylint: disable=broad-except
update_synq_status(connection,
object_id,
object_type,
status="Failed: {}".format(e.message))
print "Processing external data for {} ID:{} failed:{}".format(
object_type, object_id, e.message)
else:
update_synq_status(connection, object_id, object_type, status="OK")
else:
process_with_ggrc_data(connection, object_id, object_type, migrator_id)
utils.add_to_objects_without_revisions(connection,
object_id,
object_type,
action="modified")
def map_review(conn, migrator_id, obj_id, obj_type, review_id):
"""Add relationship between review and reviewable"""
sql = """
INSERT INTO relationships (
modified_by_id,
created_at,
updated_at,
source_id,
source_type,
destination_id,
destination_type,
is_external
) VALUES (
:migrator_id, NOW(), NOW(), :obj_id, :obj_type, :review_id, 'Review', '0')
"""
conn.execute(text(sql),
migrator_id=migrator_id,
obj_id=obj_id,
obj_type=obj_type,
review_id=review_id)
relationship_id = utils.last_insert_id(conn)
utils.add_to_objects_without_revisions(conn, relationship_id,
"Relationship")
return relationship_id
def create_comment(conn, data):
"""Create new comment"""
sql = """
INSERT INTO comments(
description,
created_at,
modified_by_id,
updated_at,
assignee_type
)
VALUES (
:description, :created_at, :modified_by_id,
:updated_at, :assignee_type
)
"""
conn.execute(text(sql),
description=data.cte_description,
created_at=data.cte_created_at,
modified_by_id=data.cte_modified_by_id,
updated_at=data.cte_updated_at,
assignee_type=data.assignee_type)
comment_id = utils.last_insert_id(conn)
utils.add_to_objects_without_revisions(conn, comment_id, "Comment")
return comment_id
def create_acrs():
"""Create ACRs for KeyReport model"""
connection = op.get_bind()
migrator_id = migrator.get_migration_user_id(connection)
for name, read, update, delete, mandatory in KEY_REPORT_ACRS:
query = acr_propagation.ACR_TABLE.insert().values(
name=name,
object_type="KeyReport",
created_at=datetime.datetime.utcnow(),
updated_at=datetime.datetime.utcnow(),
modified_by_id=migrator_id,
internal=False,
non_editable=True,
mandatory=mandatory,
# Mandatory roles are default to user
default_to_current_user=mandatory,
my_work=1,
read=read,
update=update,
delete=delete,
)
result = connection.execute(query)
utils.add_to_objects_without_revisions(connection, result.lastrowid,
"AccessControlRole")
