def create_indexes(self): """ method to create indexes on the database """ with db.engine.connect() as conn: with conn.execution_options(isolation_level='AUTOCOMMIT'): try: Pairing.create_index(conn) Pairing_Codes.create_index(conn) Imei.create_index(conn) Devices.create_index(conn) Owner.create_index(conn) except Exception as e: sys.exit(1)
def test_pairing_codes_index(db, session): """Verify that the Pairing_Codes model works correctly.""" with db.engine.connect() as conn: Pairing_Codes.create_index(conn) qry = session.execute( text(""" select * from pg_stat_user_indexes; """)).fetchall() for c in qry: if c.indexrelname == 'devices_paircodes_index': pc_index = True print(c.indexrelname) # noinspection PyUnboundLocalVariable assert pc_index
def complete_db_insertion(session, db, t_owner_id, t_contact, t_device_id, t_model, t_brand, t_serial, t_rat, t_paircode, t_imei_id, t_imei, t_mac=None): owner_add = Owner(id=t_owner_id, contact=t_contact) session.add(owner_add) device_add = Devices(id=t_device_id, model=t_model, brand=t_brand, serial_no=t_serial, rat=t_rat, owner_id=t_owner_id, mac=t_mac) session.add(device_add) paircode_add = Pairing_Codes(pair_code=t_paircode, is_active=True, device_id=t_device_id) session.add(paircode_add) imei_add = Imei(id=t_imei_id, imei=t_imei, device_id=t_device_id) session.add(imei_add) db.session.commit()
def rel_all(sender_no): """ Function to remove all pairs simultaneously including the primary pair """ try: rtn_msg = "" rel_all_cond = 3 pattern_msisdn = re.compile(r'923\d{9,12}') match_primary = pattern_msisdn.fullmatch(sender_no) if match_primary: chk_primary = Pairing.query.filter( db.and_(Pairing.msisdn == '{}'.format(sender_no), Pairing.is_primary == True)).all() # checking if request is originated from primary-pair if chk_primary: for q1 in chk_primary: if q1.end_date is None: rel_all_cond = 1 q1.end_date = '{}'.format(strftime("%Y-%m-%d")) if q1.imsi is not None and q1.add_pair_status: q1.change_type = 'REMOVE' q1.export_status = False db.session.commit() chk_sec_pairs = Pairing.query.filter( Pairing.primary_id == q1.id).all() # checking if secondary pairs exist under primary if chk_sec_pairs: for q2 in chk_sec_pairs: q2.end_date = '{}'.format(strftime("%Y-%m-%d")) if q2.imsi is not None and q2.add_pair_status: q2.change_type = 'REMOVE' q2.export_status = False db.session.commit() else: rel_all_cond = 2 else: rel_all_cond = 3 if rel_all_cond == 1: paircode = gen_paircode( ) # generating new pair-code & assigning it to the particular mobile device chk_dev_id = Imei.query.filter(Imei.id == q1.imei_id).first() add_pc = Pairing_Codes(pair_code=paircode, is_active=True, device_id=chk_dev_id.device_id) db.session.add(add_pc) db.session.commit() rtn_msg = "Release All-Pairs request is registered. New Pair Code is ({})".format( paircode) elif rel_all_cond == 2: chk_dev_id = Imei.query.filter(Imei.id == q1.imei_id).first() chk_paircode = Pairing_Codes.query.filter( Pairing_Codes.device_id == chk_dev_id.device_id, Pairing_Codes.is_active == True).first() rtn_msg = "Your new Pair-Code is ({}). " \ "Release-All request is already registered and will be implemented within 24-48 hours"\ .format(chk_paircode.pair_code) elif rel_all_cond == 3: rtn_msg = "Release-All request not made by Primary-MSISDN" return rtn_msg elif not match_primary: return "Primary MSISDN format is not correct" except Exception as e: db.session.rollback() finally: db.session.close()
def authority_input(contact_no, model, brand, serial_no, mac, rat, imei): """ Function to register device parameters from Authority's portal and assigns pair-code """ try: rtn_msg = "" chk_imei, chk_mac, chk_contact = False, False, False if mac is not None: mac_pat_1 = re.compile( r'[A-F0-9]{2}[:.-][A-F0-9]{2}[:.-][A-F0-9]{2}[:.-][A-F0-9]{2}[:.-][A-F0-9]{2}[:.-][A-F0-9]{2}' ) mac_match1 = mac_pat_1.fullmatch(mac) mac_pat_2 = re.compile( r'[A-F0-9]{3}[:.-][A-F0-9]{3}[:.-][A-F0-9]{3}[:.-][A-F0-9]{3}') mac_match2 = mac_pat_2.fullmatch(mac) mac_pat_3 = re.compile( r'[A-F0-9]{2}[:.-][A-F0-9]{2}[:.-][A-F0-9]{2}[:.-][A-F0-9]{2}[:.-][A-F0-9]{2}[:.-]' r'[A-F0-9]{2}[:.-][A-F0-9]{2}[:.-][A-F0-9]{2}') mac_match3 = mac_pat_3.fullmatch(mac) mac_pat_4 = re.compile( r'[A-F0-9]{4}[:.-][A-F0-9]{4}[:.-][A-F0-9]{4}[:.-][A-F0-9]{4}') mac_match4 = mac_pat_4.fullmatch(mac) if mac_match1 or mac_match2 or mac_match3 or mac_match4: chk_mac = True else: chk_mac = True pattern_rat = re.compile( r'(2G|3G|4G|5G)[,]?(2G|3G|4G|5G)?[,]?(2G|3G|4G|5G)?[,]?(2G|3G|4G|5G)?' ) match_rat = pattern_rat.fullmatch(rat) pattern_model_brand = re.compile(r'[a-zA-Z0-9_ .\'-]{1,1000}') match_model = pattern_model_brand.fullmatch(model) match_brand = pattern_model_brand.fullmatch(brand) pattern_serial_no = re.compile(r'[a-zA-Z0-9]{1,1000}') match_serial = pattern_serial_no.fullmatch(serial_no) if contact_no['CC'] == conf['CC']: if (contact_no['SN'].isdigit()) and (len(contact_no['SN']) < 13): contact_msisdn = contact_no['CC'] + contact_no['SN'] chk_contact = True for i in imei: pattern_imei = re.compile(r'[A-F0-9]{14,16}') match_imei = pattern_imei.fullmatch(i) if len(i) < 17 and match_imei: chk_imei = True else: chk_imei = False break if match_model and match_brand and match_serial and match_rat and chk_mac and chk_imei and chk_contact: chk_duplicate = Devices.query.filter( Devices.serial_no == '{}'.format(serial_no)).first() # to check if device is not already registered if chk_duplicate: rtn_msg = { "Error": "Device with same Serial number already exists" } return rtn_msg, 422 else: chk_owner_id = Owner.query.filter( Owner.contact == '{}'.format(contact_msisdn)).first() if not chk_owner_id: max_owner_id = db.session.query(func.max( Owner.id)).scalar() # query to get maximum owner_id if not max_owner_id: max_owner_id = 1 else: max_owner_id += 1 add_owner = Owner(id=max_owner_id, contact=contact_msisdn) db.session.add(add_owner) tmp_id = max_owner_id else: tmp_id = chk_owner_id.id max_dev_id = db.session.query(func.max( Devices.id)).scalar() # query to get maximum device_id if not max_dev_id: max_dev_id = 1 else: max_dev_id += 1 add_device = Devices(id=max_dev_id, model=model, brand=brand, serial_no=serial_no, mac=mac, rat=rat.strip(','), owner_id=tmp_id) db.session.add(add_device) max_imei_id = db.session.query(func.max( Imei.id)).scalar() # query to get maximum IMEI_id if not max_imei_id: max_imei_id = 1 else: max_imei_id += 1 for i in imei: add_imei = Imei(id=max_imei_id, imei=i, device_id=max_dev_id) db.session.add(add_imei) max_imei_id += 1 pair_code = gen_paircode() add_paircode = Pairing_Codes(pair_code=pair_code, is_active=True, device_id=max_dev_id) db.session.add(add_paircode) db.session.commit() rtn_msg = { "msg": "Device's information has been successfully loaded", "pair_code": pair_code } message = "Device has been registered with Authority. Your Activation Pair-Code is ({})".format( pair_code) payload = { 'username': '******', 'password': '******', 'smsc': 'at', 'from': '7787', 'to': contact_msisdn, 'text': message } r = requests.get(conf['kannel_sms'], params=payload) return rtn_msg, 200 elif not chk_contact: rtn_msg = {"Error": "Contact-MSISDN format is not correct"} return rtn_msg, 422 elif not match_model: rtn_msg = {"Error": "Model format is not correct"} return rtn_msg, 422 elif not match_brand: rtn_msg = {"Error": "Brand format is not correct"} return rtn_msg, 422 elif not match_rat: rtn_msg = {"Error": "RAT format is not correct"} return rtn_msg, 422 elif not match_serial: rtn_msg = {"Error": "Serial-Number format is not correct"} return rtn_msg, 422 elif not chk_mac: rtn_msg = {"Error": "MAC format is not correct"} return rtn_msg, 422 elif not chk_imei: rtn_msg = {"Error": "IMEI format is not correct"} return rtn_msg, 422 except Exception as e: db.session.rollback() finally: db.session.close()
def rel_all(sender_no): """ Function to remove all pairs simultaneously including the primary pair """ try: rtn_msg = "" rel_all_cond = 3 pattern_msisdn = re.compile(r'923\d{9,12}') match_primary = pattern_msisdn.fullmatch(sender_no) if match_primary: chk_primary = Pairing.query.filter(db.and_(Pairing.msisdn == '{}'.format(sender_no), Pairing.is_primary == True)).all() # checking if request is originated from primary-pair if chk_primary: for q1 in chk_primary: if q1.end_date is None: rel_all_cond = 1 q1.end_date = '{}'.format(strftime("%Y-%m-%d")) if q1.imsi is not None and q1.add_pair_status and q1.change_type == 'add'\ and q1.export_status is True: # Condition checks only those pairs be exported as "removed" in pair-list # which are added and already exported to DIRBS-CORE before removing q1.export_status = False q1.change_type = 'remove' elif q1.export_status is False and (q1.change_type is None or q1.change_type == 'add'): # Condition to avoid exporting this pair to DIRBS-CORE q1.export_status = None # pragma: no cover q1.change_type = None # pragma: no cover q1.old_imsi = None # pragma: no cover elif q1.imsi is None and q1.export_status is None \ and q1.change_type is None and q1.old_imsi is not None: # Condition for case where pair(s) is exported once and after that SIM-Change is requested # but before MNO provides new IMSI, Pair is deleted. q1.export_status = False q1.change_type = "remove" q1.imsi = q1.old_imsi q1.old_imsi = None db.session.commit() chk_sec_pairs = Pairing.query.filter(Pairing.primary_id == q1.id).all() # checking if secondary pairs exist under primary if chk_sec_pairs: for q2 in chk_sec_pairs: if q2.imsi is not None and q2.add_pair_status and q2.change_type == 'add'\ and q2.export_status and q2.end_date is None: # Condition checks only those pairs be exported as "removed" in pair-list # which are added and already exported to DIRBS-CORE before removing q2.export_status = False q2.change_type = 'remove' elif q2.export_status is False and (q2.change_type is None or q2.change_type == 'add'): # Condition to avoid exporting this pair to DIRBS-CORE q2.export_status = None # pragma: no cover q2.change_type = None # pragma: no cover q2.old_imsi = None # pragma: no cover elif q2.imsi is None and q2.export_status is None \ and q2.change_type is None and q2.old_imsi is not None: # Condition for case where pair(s) is exported once and after that SIM-Change is # requested but before MNO provides new IMSI, Pair is deleted. q2.export_status = False q2.change_type = "remove" q2.imsi = q2.old_imsi q2.old_imsi = None q2.end_date = '{}'.format(strftime("%Y-%m-%d")) db.session.commit() else: rel_all_cond = 2 else: rel_all_cond = 3 if rel_all_cond == 1: paircode = gen_paircode() # generating new pair-code & assigning it to the particular mobile device chk_dev_id = Imei.query.filter(Imei.id == q1.imei_id).first() add_pc = Pairing_Codes(pair_code=paircode, is_active=True, device_id=chk_dev_id.device_id) db.session.add(add_pc) db.session.commit() rtn_msg = "Release All-Pairs request is registered. New Pair Code is ({})".format(paircode) elif rel_all_cond == 2: chk_dev_id = Imei.query.filter(Imei.id == q1.imei_id).first() chk_paircode = Pairing_Codes.query.filter(Pairing_Codes.device_id == chk_dev_id.device_id, Pairing_Codes.is_active == True).first() rtn_msg = "Your new Pair-Code is ({}). " \ "Release-All request is already registered and will be implemented within 24-48 hours"\ .format(chk_paircode.pair_code) elif rel_all_cond == 3: rtn_msg = "Release-All request not made by Primary-MSISDN" return rtn_msg elif not match_primary: return "Primary MSISDN format is not correct" except Exception as e: db.session.rollback() finally: db.session.close()