1b41f6c9c6f565bd6861117c46c6c35edfbd108e2jamesrenimport common 2b41f6c9c6f565bd6861117c46c6c35edfbd108e2jamesrenfrom autotest_lib.database import db_utils 3b41f6c9c6f565bd6861117c46c6c35edfbd108e2jamesren 4543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesrenUP_SQL = """ 5543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesrenCREATE INDEX afe_drone_sets_drones_droneset_ibfk 6543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesrenON afe_drone_sets_drones (droneset_id); 7543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesren 8543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesrenALTER TABLE afe_drone_sets_drones 9543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesrenDROP KEY afe_drone_sets_drones_unique; 10543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesren 11543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesrenALTER TABLE afe_drone_sets_drones 12543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesrenADD CONSTRAINT afe_drone_sets_drones_unique 13543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesrenUNIQUE KEY (drone_id); 14b41f6c9c6f565bd6861117c46c6c35edfbd108e2jamesren""" 15543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesren 16b41f6c9c6f565bd6861117c46c6c35edfbd108e2jamesren# On first migration to 62, this key will be deleted automatically. However, if 17b41f6c9c6f565bd6861117c46c6c35edfbd108e2jamesren# you migrate to 62, then down to 61, then back to 62, this key will remain. 18b41f6c9c6f565bd6861117c46c6c35edfbd108e2jamesrenDROP_KEY_SQL = """ 19543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesrenALTER TABLE afe_drone_sets_drones 20543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesrenDROP KEY afe_drone_sets_drones_drone_ibfk; 21543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesren""" 22543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesren 23543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesrenDOWN_SQL = """ 24543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesrenCREATE INDEX afe_drone_sets_drones_drone_ibfk 25543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesrenON afe_drone_sets_drones (drone_id); 26543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesren 27543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesrenALTER TABLE afe_drone_sets_drones 28543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesrenDROP KEY afe_drone_sets_drones_unique; 29543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesren 30543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesrenALTER TABLE afe_drone_sets_drones 31543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesrenADD CONSTRAINT afe_drone_sets_drones_unique 32543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesrenUNIQUE KEY (droneset_id, drone_id); 33543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesren 34543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesrenALTER TABLE afe_drone_sets_drones 35543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesrenDROP KEY afe_drone_sets_drones_droneset_ibfk; 36543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesren""" 37543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesren 38543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesren 39543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesrendef migrate_up(manager): 40543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesren query = ('SELECT * FROM afe_drone_sets_drones ' 41543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesren 'GROUP BY drone_id HAVING COUNT(*) > 1') 42543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesren rows = manager.execute(query) 43543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesren if rows: 44861b2d54aec24228cdb3895dbc40062cb40cb2adEric Li raise Exception('Some drones are associated with more than one drone ' 45861b2d54aec24228cdb3895dbc40062cb40cb2adEric Li 'set. Please remove all duplicates before running this ' 46861b2d54aec24228cdb3895dbc40062cb40cb2adEric Li 'migration.') 47543d9fb64c2978a8094ca52b9045f43a3b54ff4bjamesren manager.execute_script(UP_SQL) 48b41f6c9c6f565bd6861117c46c6c35edfbd108e2jamesren 49b41f6c9c6f565bd6861117c46c6c35edfbd108e2jamesren if db_utils.check_index_exists(manager, 'afe_drone_sets_drones', 50b41f6c9c6f565bd6861117c46c6c35edfbd108e2jamesren 'afe_drone_sets_drones_drone_ibfk'): 51b41f6c9c6f565bd6861117c46c6c35edfbd108e2jamesren manager.execute(DROP_KEY_SQL) 52