def copy_db(_id): session = Session() obj = session.query(Poll).filter_by(id=_id).first() id_poll = obj.id session.expunge(obj) obj.id = None obj.name = '(Копія) ' + obj.name obj.status = 'develop' obj.count_of_complete = 0 obj.total_count = 0 make_transient(obj) session.add(obj) session.flush() question = session.query(Question).filter_by(fk_poll=id_poll).all() for arg in question: session.expunge(arg) question_id = arg.id arg.id = None arg.fk_poll = obj.id make_transient(arg) session.add(arg) varaint = session.query(Variant).filter_by(fk_question=question_id).all() for res in varaint: session.expunge(res) res.id = None res.fk_question = arg.id make_transient(res) session.add(res) session.commit() session.close()
def cmd_mm(bot: telegram.Bot, update: telegram.Update, session: db.Session): user = session.query(db.Telegram).filter_by(telegram_id=update.message.from_user.id).one_or_none() if user is None: reply(bot, update, strings.LINK.ERRORS.ROYALNET_NOT_LINKED) return match = re.match(r"/(?:mm|matchmaking)(?:@royalgamesbot)?(?: (?:([0-9]+)-)?([0-9]+))? (?:per )?([A-Za-z0-9!\-_. ]+)(?:.*\n(.+))?", update.message.text) if match is None: reply(bot, update, strings.MATCHMAKING.ERRORS.INVALID_SYNTAX) return min_players, max_players, match_name, match_desc = match.group(1, 2, 3, 4) db_match = db.Match(timestamp=datetime.datetime.now(), match_title=match_name, match_desc=match_desc, min_players=min_players, max_players=max_players, creator=user) session.add(db_match) session.flush() inline_keyboard = IKMarkup([([IKButton(strings.MATCHMAKING.BUTTONS[key], callback_data=key)]) for key in strings.MATCHMAKING.BUTTONS]) message = bot.send_message(config["Telegram"]["announcement_group"], db_match.generate_text(session=session), parse_mode="HTML", reply_markup=inline_keyboard) db_match.message_id = message.message_id session.commit()
def poll_save(data): session = Session() if data['id'] != '': id_poll = data['id'] session.query(Poll).filter_by(id=id_poll).delete() poll = Poll(id=id_poll, name=data['name'], count_of_complete=0, total_count=0, status='develop', description=data['description']) session.add(poll) else: poll = Poll(name=data['name'], count_of_complete=0, total_count=0, status='develop', description=data['description']) session.add(poll) session.flush() id_poll = poll.id for arg in data['questions']: print(arg) question = Question(question=arg['question'], type_=arg['type_'], fk_poll=id_poll) session.add(question) session.flush() id_question = question.id for res in arg['answers']: variant = Variant(text=res, fk_question=id_question) session.add(variant) session.commit() session.close() return id_poll
def cmd_vote(bot: telegram.Bot, update: telegram.Update, session: db.Session): user = session.query(db.Telegram).filter_by(telegram_id=update.message.from_user.id).one_or_none() if user is None: bot.send_message(update.message.chat.id, "⚠ Il tuo account Telegram non è registrato al RYGdb!" " Registrati con `/register@royalgamesbot <nomeutenteryg>`.", parse_mode="Markdown") return try: _, mode, question = update.message.text.split(" ", 2) except IndexError: bot.send_message(update.message.chat.id, "⚠ Non hai specificato tutti i parametri necessari!" "Sintassi: `/vote@royalgamesbot <public|secret> <domanda>`", parse_mode="Markdown") return if mode == "public": vote = db.VoteQuestion(question=question, anonymous=False) elif mode == "secret": vote = db.VoteQuestion(question=question, anonymous=True) else: bot.send_message(update.message.chat.id, "⚠ Non hai specificato una modalità valida!" "Sintassi: `/vote@royalgamesbot <public|secret> <domanda>`", parse_mode="Markdown") return session.add(vote) session.flush() inline_keyboard = IKMarkup([[IKButton("🔵 Sì", callback_data="vote_yes")], [IKButton("🔴 No", callback_data="vote_no")], [IKButton("⚫️ Astieniti", callback_data="vote_abstain")]]) message = bot.send_message(update.message.chat.id, vote.generate_text(session=session), reply_markup=inline_keyboard, parse_mode="HTML") vote.message_id = message.message_id session.commit()
def candidate_to_worker_sql(_id, data, sid, card_number): """ Прийняття кандидата на роботу :param _id: ід кандидата :param data: обєк з даними про кандидата :param sid: sid отриманий з АД :param card_number: отриманий при створенню користувача в 1с """ session = Session() mail = data['AD']['givenName-En'] + '.' + data['AD'][ 'snEn'] + '@busmarket.ua' id_dep = session.query(Department1c.id).filter( Department1c.name == data['department']).first() id_position = session.query( Position.id).filter(Position.name == data['position']).first() id_branch = session.query( Branch.id).filter(Branch.name == data['branch']).first() # photo = data['photo'] # Добавляння фото # if photo != '': # document = Document(name='фотографія', file_type='', file=photo.encode('utf-8'), type_='', fk_person=_id) # db.session.add(document) data['person']['birthday'] = data['person']['birthday'][0:10] data['person']['type_'] = '' data['person']['date_of_issue'] = data['person']['date_of_issue'][0:10] data['worker']['started_to_work'] = data['worker']['started_to_work'][0:10] data['worker']['sid'] = sid data['worker']['fk_position'] = id_position[0] data['worker']['fk_branch'] = id_branch[0] data['worker']['fk_person'] = _id data['worker']['fk_department'] = id_dep[0] if data['permissions']['email'] == '1': data['person']['email'] = mail.lower() data['worker']['email'] = mail.lower() session.query(Person).filter_by(id=_id).update(data['person']) # створення працівника worker = Worker(**data['worker']) session.add(worker) session.flush() id_worker = worker.id # заповнення зарплати salary = Salary(salary=data['salary'], fk_position=id_position[0], fk_worker=id_worker) session.add(salary) session.commit() vacation_user(id_worker) return
def on_post(self, req, resp): doc = req.context['doc'] users=Session.query(User).all() unique=True for user in users: if doc['email'] == user.email: unique=False if unique: user = User(name=doc['name'], email=doc['email'].lower(), signedin=False,registerTime=datetime.datetime.today()) print(datetime.datetime.today()) user.salt = bcrypt.gensalt() user.pw_hash = bcrypt.hashpw(doc['password'].encode('utf-8'), user.salt) s=smtplib.SMTP_SSL('smtp.gmail.com',465) s.ehlo() s.login('*****@*****.**','helloworld@ppclub') code=randint(1000000,10000000) user.code=code msg=MIMEText('Hi '+user.name+', your verification URL is: '+'http://192.168.1.127:8000/confirmation/'+str(code)) msg['From']='*****@*****.**' msg['To']=user.email msg['Subject']='PhoenixNow Account Confirmation' s.send_message(msg) s.close() Session.add(user) Session.flush() Session.commit() req.context['user'] = user.id req.context['result'] = {"result": "success", "action": "register"} else: user=get_user(req,resp) td=datetime.timedelta(minutes=30) if datetime.datetime.today()-td<user.registerTime or user.emailVerified==True: description = "User was already made" title = "User creation conflict" raise falcon.HTTPConflict(title=title, description=description) else: Session.delete(user) Session.flush() user = User(name=doc['name'], email=doc['email'], signedin=False,registerTime=datetime.datetime.today()) print(datetime.datetime.today()) user.salt = bcrypt.gensalt() user.pw_hash = bcrypt.hashpw(doc['password'].encode('utf-8'), user.salt) s=smtplib.SMTP_SSL('smtp.gmail.com',465) s.ehlo() s.login('*****@*****.**','helloworld@ppclub') code=randint(1000000,10000000) user.code=code msg=MIMEText('Hi '+user.name+', your verification URL is: '+'http://192.168.1.127:8000/confirmation/'+str(code)) msg['From']='*****@*****.**' msg['To']=user.email msg['Subject']='PhoenixNow Account Confirmation' s.send_message(msg) s.close() Session.add(user) Session.flush() Session.commit() req.context['user'] = user.id req.context['result'] = {"result": "success", "action": "register"}
def city_list(): """ Заповнення міста тарегіону в БД """ users_list = [] if load_users_from_ad(): process_ad_response(ad.response, users_list, (('userAccountControl', [], False), ('cityUa', [], False))) session = Session() for arg in users_list: if not session.query(City).filter(City.name == arg['cityUa']).all(): region = Region(name=arg['stUa'], name_en=arg['st'], country='Україна', country_en='Ukraine') session.add(region) session.flush() city = City(name=arg['cityUa'], name_en=arg['l'], fk_region=region.id) session.add(city) session.commit() session.close()
def create_new_worker_sql(data, sid, card_number): """ Створення нового користувача в sql :param data: обєкт з анкети :param sid: sid з АД :param card_number: з 1с """ session = Session() mail = data['AD']['givenName-En'] + '.' + data['AD'][ 'snEn'] + '@busmarket.ua' id_dep = session.query(Department1c.id).filter( Department1c.name == data['department']).first() id_position = session.query( Position.id).filter(Position.name == data['position']).first() id_branch = session.query( Branch.id).filter(Branch.name == data['branch']).first() data['person']['birthday'] = data['person']['birthday'][0:10] data['person']['date_of_issue'] = data['person']['date_of_issue'][0:10] data['worker']['started_to_work'] = data['worker']['started_to_work'][0:10] if data['permissions']['email'] == '1': data['person']['email'] = mail.lower() data['worker']['email'] = mail.lower() data['worker']['card_number'] = card_number # photo = data['photo'] person = Person(**data['person']) session.add(person) session.flush() # if photo != '': # document = Document(name='фотографія', file_type='', file=photo.encode('utf-8'), type_='', fk_person=person.id) # db.session.add(document) data['worker']['sid'] = sid data['worker']['fk_person'] = person.id data['worker']['fk_position'] = id_position[0] data['worker']['fk_branch'] = id_branch[0] data['worker']['fk_department'] = id_dep[0] worker = Worker(**data['worker']) session.add(worker) session.flush() id_worker = worker.id salary = Salary(salary=data['salary'], fk_position=id_position[0], fk_worker=id_worker) session.add(salary) # Додавання освіти for arg in data['education']: arg['fk_person'] = person.id education = Education(**arg) session.add(education) # Додавання інформації про військову придатність data['military']['fk_person'] = person.id military = MilitaryService(**data['military']) session.add(military) # Додавання інформації про сімю for arg in data['family']: arg['fk_person'] = person.id arg['birthday'] = arg['birthday'][0:10] family = Family(**arg) session.add(family) # Додавання інформації про досвід роботи for arg in data['workExp']: arg['fk_person'] = person.id experience = Experience(**arg) session.add(experience) session.commit() vacation_user(id_worker) return person.id
def save_anketa_individual(data): session = Session() # data['person']['birthday'] = current_date(data['person']['birthday']) data['person']['birthday'] = data['person']['birthday'][0:10] if data['person']['date_of_issue'] != '': # data['person']['date_of_issue'] = current_date(data['person']['date_of_issue']) data['person']['date_of_issue'] = data['person']['date_of_issue'][0:10] else: data['person'].pop('date_of_issue') person = Person(**data['person']) session.add(person) session.flush() # photo = data['photo'] # if photo != '': # document = Document(name='фотографія', file_type='', file=photo.encode('utf-8'), type_='', fk_person=person.id) # db.session.add(document) data['military']['fk_person'] = person.id military = MilitaryService(**data['military']) session.add(military) for arg in data['language']: arg['fk_person'] = person.id language = Language(**arg) session.add(language) for arg in data['family']: arg['fk_person'] = person.id if arg['birthday'] != '': # arg['birthday'] = current_date(arg['birthday']) arg['birthday'] = arg['birthday'][0:10] else: arg.pop('birthday') family = Family(**arg) session.add(family) for arg in data['education']: arg['fk_person'] = person.id education = Education(**arg) session.add(education) for arg in data['workExp']: arg['fk_person'] = person.id workExp = Experience(**arg) session.add(workExp) for arg in data['recomendations']: arg['fk_person'] = person.id recomendations = Recommendation(**arg) session.add(recomendations) data['priority']['fk_person'] = person.id priority = Priority(**data['priority']) session.add(priority) for arg in data['other'].keys(): try: id_ = int(arg) if type(data['other'][arg]) == dict: for res in data['other'][arg].values(): answer = Answer(answer=res, fk_question=id_, fk_person=person.id) session.add(answer) elif data['other'][arg] != '': answer = Answer(answer=data['other'][arg], fk_question=id_, fk_person=person.id) session.add(answer) except: pass session.commit() return person.id
class TestStore(testing.TestCase): def setUp(self): super(TestStore, self).setUp() reset_db() self.app = app self.sess = Session() self.simulate_post('/register', params={ 'name': 'test', 'password': '******' }) auth = self.simulate_post('/auth', params={ 'name': 'test', 'password': '******' }, headers={'Content-Type': 'application/json'}) self.headers = { 'Authorization': 'JWT {}'.format(auth.json['access_token']) } # r = self.simulate_get('/', headers=self.access_token) def tearDown(self): self.sess.close() def test_store_not_found(self): r = self.simulate_get('/store/test', headers=self.headers) self.assertEqual(r.status_code, 404) def test_store_found(self): store = StoreModel(name='testStore') self.sess.add(store) self.sess.commit() r = self.simulate_get('/store/testStore', headers=self.headers) self.assertEqual(r.status_code, 200) self.assertDictEqual({'name': 'testStore', 'items': []}, r.json) def test_store_with_items_found(self): store = StoreModel(name='testStore') self.sess.add(store) self.sess.commit() self.sess.flush() item = ItemModel(name='testItem', price=12.99, store_id=store.id) self.sess.add(item) self.sess.commit() r = self.simulate_get('/store/testStore', headers=self.headers) self.assertEqual(r.status_code, 200) self.assertDictEqual( { 'name': 'testStore', 'items': [{ 'name': 'testItem', 'price': 12.99 }] }, r.json) def test_delete_store(self): store = StoreModel(name='testStore') self.sess.add(store) self.sess.commit() r = self.simulate_delete('/store/testStore', headers=self.headers) self.assertEqual(r.status_code, 200) self.assertDictEqual({'message': 'Store deleted'}, r.json) def test_create_store(self): r = self.simulate_post('/store/test', headers=self.headers) self.assertEqual(r.status_code, 201) self.sess.query(StoreModel).filter(StoreModel.name == 'test').first() self.assertDictEqual({'name': 'test', 'items': []}, r.json) def test_create_duplicate_store(self): self.simulate_post('/store/test', headers=self.headers) r = self.simulate_post('/store/test', headers=self.headers) self.assertEqual(r.status_code, 400) def test_list_store(self): store = StoreModel(name='testStore') self.sess.add(store) self.sess.commit() r = self.simulate_get('/stores', headers=self.headers) self.assertDictEqual({'stores': [{ 'name': 'testStore', 'items': [] }]}, r.json) def test_list_store_with_item(self): store = StoreModel(name='testStore') self.sess.add(store) self.sess.commit() self.sess.flush() item = ItemModel(name='testItem', price=12.99, store_id=store.id) self.sess.add(item) self.sess.commit() r = self.simulate_get('/stores', headers=self.headers) self.assertDictEqual( { 'stores': [{ 'name': 'testStore', 'items': [{ 'name': 'testItem', 'price': 12.99 }] }] }, r.json)
def load_from_1c(arg, status): """ Заповнення базу mysql з 1с працівниками :param arg: обєкт працівника з 1с :param status: active/dissmised """ session = Session() dep = session.query(Department1c.id).filter( Department1c.name == arg['department']).first() if not dep: id_dep = 1 else: id_dep = dep[0] id_position = session.query( Position.id).filter(Position.name == arg['position']).first() if 'Львів' in arg['department']: id_branch = 6 elif 'Київ' in arg['department']: id_branch = 2 elif 'Хмельницьк' in arg['department']: id_branch = 7 elif 'Чернівц' in arg['department']: id_branch = 8 else: id_branch = 1 person = Person(name=arg['name'], surname=arg['surname'], middle_name=arg['middlename'], birthday=arg['birthday'][0:10], marital_status=arg['married'], place_of_residence=arg['address_of_residence'], registration=arg['place_of_residence'], ipn=arg['IPN'], mobile_phone=arg['phone'], gender=arg['gender']) session.add(person) session.flush() worker = Worker(sid=arg['SID'], name_ua=arg['name'], surname_ua=arg['surname'], middle_name_ua=arg['middlename'], card_number=arg['personnel_number'], status=status, work_schedule=arg['schedule'], started_to_work=arg['startdate'][0:10], finished_to_work=arg['enddate'][0:10], fk_position=id_position[0], fk_branch=id_branch, fk_person=person.id, fk_department=id_dep) session.add(worker) session.flush() salary = Salary(salary=arg['salary'], fk_position=id_position[0], fk_worker=worker.id) session.add(salary) if arg['passport'] != []: session.query(Person).filter_by(id=person.id).update({ "passport_id": arg['passport'][0]['doc_series'] + arg['passport'][0]['doc_number'], "date_of_issue": arg['passport'][0]['doc_date'][0:10], "issued_by": arg['passport'][0]['doc_issuedby'] }) if arg['education'] != []: for res in arg['education']: education = Education(institution_name=res['institution'], education_type=res['type_education'], specialty=res['specialty'], faculty=res['faculty'], fk_person=person.id) session.add(education) session.commit() session.close()
class TraceReader: """ This class will load a trace file and parse it. It will store everything in a database. """ def __init__(self, filename, database_name="default_trace"): self.filename = filename self.address_resolver = AddressResolver() self.session = Session() self.cache = {} self.cache["chunks"] = {} self.cache["modules"] = {} # these are the events that we currently load from the trace file self.allowed_events = ["L", "W", "R", "I", "A", "X", "F", "D", "H"] """ try: conn = engine.connect() conn.execute("commit") conn.execute("create database %s" % (database_name)) except ProgrammingError: raise RuntimeWarning("Error while creating database %s, it already exists" % (database_name)) finally: conn.close() """ def parse(self, match_events=""): self.f = open(self.filename, "r") info("Parsing trace file %s" % self.filename) i = 0 start_time = time() print self.session.query(MemoryChunk).filter( MemoryChunk.timestamp == 0xcafecafe) # First pass. Collect functions and chunks for line in self.f: event = line[1] i += 1 if (i % 100000) == 0: print "Number of lines processed %d in %d seconds" % ( i, time() - start_time) if (event not in match_events) and (match_events != ""): continue if event not in self.allowed_events: continue elif event == "H": # Basic Block hit event. fields = line.split(":") address = int(fields[1], 16) image = self.address_resolver.get_image(address) rva = image.get_offset(address) # Get the backing module. There should be just one module, otherwise an exception will be raised. module = self.session.query(Module).filter( Module.name == image.name).one() new_object = BasicBlockHit(module, rva) elif event == "D": # Destructor function detected. # Sample line: D:RVA fields = line.split(":") address = int(fields[1], 16) image = self.address_resolver.get_image(address) rva = image.get_offset(address) # Get the backing module. There should be just one module, otherwise an exception will be raised. module = self.session.query(Module).filter( Module.name == image.name).one() new_object = Destructor(module, rva) elif event == "F": # Function detected # Sample line: F:RVA fields = line.split(":") address = int(fields[1], 16) image = self.address_resolver.get_image(address) rva = image.get_offset(address) # Get the backing module. There should be just one module, otherwise an exception will be raised. module = self.session.query(Module).filter( Module.name == image.name).one() new_object = Function(module, rva) elif event == "L": # Loaded a new image. # Sample line: L:ld-linux-x86-64.so.2:0x00007fbeb7461000:0x00007fbeb76832c7 fields = line.split(":") img_name = fields[1] img_lo = int(fields[2], 16) img_hi = int(fields[3], 16) # Add a new image to the address resolver. Used to get the RVA of most things self.address_resolver.loaded_image( LoadedImage(img_name, img_lo, img_hi)) new_object = Module(img_name, img_lo, img_hi) elif event == "A": # Interesting heap chunk. One that has been identified as a potential object # Sample line: A:0x0000000001a2c010:0x000000000000000c:0x000000000000000c fields = line.strip().split(":") chunk_addr = int(fields[1], 16) chunk_size = int(fields[2], 16) timestamp = int(fields[3], 16) new_object = MemoryChunk(timestamp, chunk_size) elif event == "X": # Resolved indirect jump/call. # Sample line: X:ID:RVA:ID:RVA fields = line.strip().split(":") ins_address = int(fields[1], 16) branch_address = int(fields[2], 16) image = self.address_resolver.get_image(ins_address) ins_rva = image.get_offset(ins_address) try: branch_image = self.address_resolver.get_image( branch_address) branch_rva = branch_image.get_offset(branch_address) branch_module = self.session.query(Module).filter( Module.name == branch_image.name).one() except AttributeError: branch_rva = 0 branch_module = Module("InvalidModule", 0, 0) module = self.session.query(Module).filter( Module.name == image.name).one() new_object = ResolvedBranch(module, ins_rva, branch_module, branch_rva) else: continue self.session.add(new_object) self.session.flush() # Commit current changes. self.session.commit() return i = 0 start_time = time() self.f.seek(0) print "Second pass analysis" # Second pass analysis for line in self.f: event = line[1] i += 1 if (i % 1000) == 0: print "Number of lines processed %d in %d seconds" % ( i, time() - start_time) if (event not in match_events) and (match_events != ""): continue if event not in self.allowed_events: continue elif event == "I": # Interesting address. Might be a method. At least it deals with the chunk. # Sample line: I:0x00000000004006b8:0x0000000000000001:0x0000000000000000 fields = line.strip().split(":") address = int(fields[1], 16) timestamp = int(fields[2], 16) offset = int(fields[3], 16) image = self.address_resolver.get_image(address) rva = image.get_offset(address) # Get the function that used the chunk. There must be only one function, otherwise an exception will be raised. function = self.session.query(Function).filter( Module.name == image.name).filter( Function.rva == rva).one() chunk = self.session.query(MemoryChunk).filter( MemoryChunk.timestamp == timestamp).one() new_object = FunctionUse(function, chunk, offset) elif event == "W" or event == "R": # Write or read to an interesting chunk. This indicates a field inside the chunk # Sample line: W:0x004776c6:0x01e01558:0x01e01558:0x00000004:0x00000019:0x004777f4:0x00000016 fields = line.split(":") address = int(fields[1], 16) chunk_addr = int(fields[2], 16) write_addr = int(fields[3], 16) write_size = int(fields[4], 16) timestamp = int(fields[5], 16) content = pack("<Q", int(fields[6], 16)) image = self.address_resolver.get_image(address) rva = image.get_offset(address) type = MemoryAccess.WRITE if event == "R": type = MemoryAccess.READ if not self.cache["chunks"].has_key(timestamp): chunk = self.session.query(MemoryChunk).filter( MemoryChunk.timestamp == timestamp).one() self.cache["chunks"][timestamp] = chunk else: chunk = self.cache["chunks"][timestamp] if not self.cache["modules"].has_key(image.name): module = self.session.query(Module).filter( Module.name == image.name).one() self.cache["modules"][image.name] = module else: module = self.cache["modules"][image.name] #module = self.session.query(Module).filter(Module.name == image.name).one() offset = write_addr - chunk_addr new_object = MemoryAccess(type, module, rva, chunk, offset, write_size, content) else: continue self.session.add(new_object) self.session.flush() self.session.commit() self.f.close()