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")