def test_notifications_without_destination(self, mocked_ready):
"""Test that notifications without a destination aren't sent."""
mocked_ready.side_effect = [True, True]
notifications = self.notifications
notifications[0].destination["webhook"] = None
notifications[1].destination["webhook"] = None
outbox = Outbox(notifications)
self.assertEqual(0, outbox.send_notifications())
class SMTPPlugin(object):
def __init__(self, config):
self.outbox = Outbox(server=config.host,
username=config.user,
password=config.password,
port=config.get('port', 25),
mode=config.get('mode', None))
def send(self, subject, message, recipients):
self.outbox.send(Email(subject=subject, html_body=message, recipients=recipients))
def test_outbox_login():
o = Outbox('username', 'password', 'server', 1234)
with mock.patch('smtplib.SMTP') as SMTP:
smtp = SMTP.return_value
o._login()
print(smtp.mock_calls)
smtp.set_debuglevel.assert_any_call(False)
smtp.starttls.assert_any_call()
smtp.login.assert_any_call('username', 'password')
def __init__(self):
# self.maxRowPerWorker = env.VEN_MAX_ROW_PER_WORKER
self.workerAddress = env.VEN_WORKER_ADDR
self.sinkAddr = env.SINK_ADDR
self.context = zmq.Context()
self.sender = self.context.socket(zmq.PUSH)
self.sender.bind(self.workerAddress)
self.processId = os.getpid()
self.system = platform.system()
self.db = DatabaseConnection(env.DB_HOST, env.DB_UNAME,
env.DB_PASSWORD, env.DB_NAME)
self.syslog = SystemLog()
self.outbox = Outbox(self.db)
def test_merge_notifications_with_same_destination_but_different_report(
self):
"""Test that the metrics are merged into the correct notification."""
report = dict(title="different_title", url="https://differentreport")
metric1 = dict(metric_name="new_metric 1")
metric2 = dict(metric_name="new_metric 2")
metrics1 = [metric1, metric2]
new_notifications = [Notification(report, metrics1, "uuid1", {})]
outbox = Outbox(self.notifications)
outbox.add_notifications(new_notifications)
self.assertEqual(
outbox.notifications[0].metrics,
[self.metric_notification_data1, self.metric_notification_data2],
)
def _send_email(config_file, subject, body):
with open(config_file, 'r') as f:
config = json.load(f)
port = config["port"]
server = config["server"]
fromaddr = config["fromaddr"]
toaddrs = config["toaddrs"]
username = config["username"]
password = config["password"]
outbox = Outbox(username=username, password=password, server=server, port=port)
email = Email(subject=subject, body=body, recipients=toaddrs)
outbox.send(email)
def __init__(self):
self.syncDB = DatabaseConnection(env.DB_HOST, env.DB_UNAME,
env.DB_PASSWORD, env.DB_NAME)
# self.statusDB = DatabaseConnection(
# env.DB_HOST, env.DB_UNAME, env.DB_PASSWORD, env.DB_NAME)
self.limitRow = env.LIMIT_PROC_ROW
self.outbox = Outbox(self.syncDB)
self.systemlog = SystemLog()
self.inbox = Inbox(self.syncDB)
self.outbox = Outbox(self.syncDB)
self.clientIdStartFrom = 10
self.updateToZeroHistory = set([])
self.PKFileName = 'pk'
self.nextPriToProcess = dict()
self.PRIFileName = 'pri'
def test_outbox_send():
message = Email(['*****@*****.**'], 'subject', 'body')
o = Outbox('username', 'password', 'server', 1234, debug=True)
import email.mime.multipart
with mock.patch.object(email.mime.multipart.MIMEMultipart, 'as_string', lambda self: 'foo'):
with mock.patch('smtplib.SMTP') as SMTP:
smtp = SMTP.return_value
o.send(message, [Attachment('foo', fileobj=StringIO('foo'))])
smtp.set_debuglevel.assert_any_call(True)
smtp.starttls.assert_any_call()
smtp.login.assert_any_call('username', 'password')
smtp.sendmail.assert_any_call('username', message.recipients, 'foo')
smtp.quit.assert_any_call()
def _send_email(config_file, subject, body):
with open(config_file, 'r') as f:
config = json.load(f)
port = config["port"]
server = config["server"]
fromaddr = config["fromaddr"]
toaddrs = config["toaddrs"]
username = config["username"]
password = config["password"]
outbox = Outbox(username=username,
password=password,
server=server,
port=port)
email = Email(subject=subject, body=body, recipients=toaddrs)
outbox.send(email)
def send(self,
recipient_list,
subject='Sem assunto',
attachment_list='False'):
emailRegex = re.compile(
r'''([a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+(\.[a-zA-Z]{2,4}))''',
re.VERBOSE)
valid_recipients = []
invalid_recipients = []
for i in recipient_list:
email = emailRegex.findall(i)
if email:
valid_recipients.append(email)
else:
invalid_recipients.append(email)
try:
with Outbox(username=smtp_user,
password=smtp_pass,
server=smtp_server,
port=smtp_port,
mode='SSL') as outbox:
if attachment_list == 'False':
attachments = []
for i in attachment_list:
attachments.append(
Attachment(str(i), fileobj=open(i, 'rb')))
outbox.send(Email(
subject=subject,
html_body='<b>SOME REALLY NICE SENTIMENT</b>',
recipients=valid_recipients),
attachments=attachments)
response = self.get_mail_sent_ok_message()
if len(invalid_recipients) > 0:
response = response + '\n' + self.get_invalid_mails_filtered_by_regex(
)
for invalid in invalid_recipients:
response = response + '\n' + invalid
return response
else:
outbox.send(
Email(subject=subject,
html_body='<b>SOME REALLY NICE SENTIMENT</b>',
recipients=valid_recipients))
response = self.get_mail_sent_ok_message()
if len(invalid_recipients) > 0:
response = response + '\n' + self.get_invalid_mails_filtered_by_regex(
)
for invalid in invalid_recipients:
response = response + '\n' + invalid
return response
finally:
pass
def __init__(self, dbhost, dbusername, dbpass, dbname, sinkaddr, skey,
ivkey):
self.key = skey
self.iv = ivkey
self.context = zmq.Context()
self.receiver = self.context.socket(zmq.PULL)
self.receiver.bind(sinkaddr)
self.syslog = SystemLog()
self.db = DatabaseConnection(dbhost, dbusername, dbpass, dbname)
self.inbox = Inbox(self.db)
self.outbox = Outbox(self.db)
async def notify(log_level: int = None) -> NoReturn:
"""Notify our users periodically of the number of red metrics."""
logging.getLogger().setLevel(log_level or logging.ERROR)
sleep_duration = int(os.environ.get("NOTIFIER_SLEEP_DURATION", 60))
api_version = "v3"
reports_url = (
f"http://{os.environ.get('SERVER_HOST', 'localhost')}:"
f"{os.environ.get('SERVER_PORT', '5001')}/api/{api_version}/reports")
data_model = await retrieve_data_model(api_version)
most_recent_measurement_seen = datetime.max.replace(tzinfo=timezone.utc)
outbox = Outbox()
notification_finder = NotificationFinder(data_model)
while True:
record_health()
logging.info("Determining notifications...")
try:
async with aiohttp.ClientSession(raise_for_status=True,
trust_env=True) as session:
response = await session.get(reports_url)
json = await response.json()
except Exception as reason: # pylint: disable=broad-except
logging.error("Could not get reports from %s: %s", reports_url,
reason)
json = dict(reports=[])
notifications = notification_finder.get_notifications(
json, most_recent_measurement_seen)
outbox.add_notifications(notifications)
outbox.send_notifications()
most_recent_measurement_seen = most_recent_measurement_timestamp(json)
logging.info("Sleeping %.1f seconds...", sleep_duration)
await asyncio.sleep(sleep_duration)
def send_email():
outbox = Outbox(username=config.username, password=config.password,server=config.server, port=config.port)
conn = sqlite3.connect(config.database_directory+'article_db')
cursor = conn.cursor()
to_send = cursor.execute('SELECT headline,rubric,body,rowid FROM article_tbl WHERE delivered = "False"').fetchall()
if len(to_send) > 0:
html_email = PyH('Economist Articles')
html_email << a(img(src="http://www.economist.com/rights/images/logo+economist.gif"),name="top")
contents = html_email << div()
for i, thing in enumerate(to_send):
content_object = contents << div()
content_object << a(thing[0],href="#%i" % i,style="font-family:Verdana;color:blue")
content_object << br()
content_object << span(" %s" % thing[1],style="font-family:Verdana;")
content_object << p('')
html_email << h2(thing[0],style="font-family:Verdana;") << a(name="%i" % i)
html_email << h3(thing[1],style="font-family:Verdana;")
html_email << p(thing[2],style="font-family:Verdana;")
html_email << p('') << a("Top",href="#top",style="font-family:Verdana;")
html_email << p('')
html_email << p('Made with <3 by oneschirm',style="font-family:Verdana;")
outbox.send(Email(subject='Economist Articles - %s' % datetime.strftime(datetime.today(),'%m/%d/%Y, %H:%M %Z'), html_body=html_email.render(),recipients=config.recipients))
for thing in to_send:
cursor.execute('UPDATE article_tbl SET delivered="True" WHERE rowid = ?', (thing[3],))
conn.commit()
conn.close()
def send(account: Account, to: str, subject: str, body: str, attachment,
attachment_name):
if not body:
raise EmptyBody
with Outbox(server=account.smtp_host,
port=account.smtp_port,
username=account.address,
password=account.password,
mode='SSL' if account.smtp_ssl else None) as connection:
email = Email(recipients=to, subject=subject, body=body)
attachments = []
if attachment:
attachments.append(Attachment(attachment_name, attachment))
try:
connection.send(email, attachments)
except SMTPRecipientsRefused:
raise IncorrectAddress
def send_report(report):
message = ''
for server_key, server_report in report.items():
message += "SERVER %s: \n\n" % server_key
if isinstance(server_report, dict):
remote_report = server_report.get("remote")
del (server_report["remote"])
for db_name, db_report in server_report.items():
message += "\tDB %s: %s\n" % (db_name, db_report)
if remote_report:
message += "\n\t[Remote server (scp)]: %s\n" % remote_report
else:
message += "%s" % server_report
message += "\n\n"
print(message)
try:
import socket
from outbox import Outbox, Email, Attachment
hostname = socket.gethostname()
smtp = namedtuple('smtp', settings.smtp.keys())(**settings.smtp)
attachments = [
Attachment('backup.log', fileobj=open(settings.logfile))
]
outbox = Outbox(username=smtp.user,
password=smtp.password,
server=smtp.host,
port=smtp.port,
mode='SSL')
message = "HOST: %s\n\n" % hostname + message
outbox.send(Email(subject='[%s] Daily backup report' % hostname,
body=message,
recipients=settings.emails),
attachments=attachments)
# if report sent, we can remove log file
os.unlink(settings.logfile)
except Exception as e:
logger.error("Can't send report via email")
logger.exception(e)
def test_merge_nothing_into_nothing(self):
"""Test that notifications are merged, even if the destination is empty."""
outbox = Outbox()
outbox.add_notifications([])
self.assertEqual(outbox.notifications, [])
def test_merge_nothing_into_notifications(self):
"""Test that notifications are merged, even if no new metrics are given to be added."""
outbox = Outbox(self.notifications)
outbox.add_notifications([])
self.assertEqual(outbox.notifications, self.notifications)
def test_merge_notifications_into_nothing(self):
"""Test that notifications are merged, even if the outbox is currently empty."""
outbox = Outbox()
outbox.add_notifications(self.notifications)
self.assertEqual(outbox.notifications, self.notifications)
def test_deletion_of_notifications(self, mocked_send, mocked_ready):
"""Test that notifications are deleted after sending."""
mocked_ready.side_effect = [True, True]
mocked_send.side_effect = [None, None]
outbox = Outbox(self.notifications)
self.assertEqual(2, outbox.send_notifications())
class Ventilator:
context = None
sender = None
processId = None
system = None
def __init__(self):
# self.maxRowPerWorker = env.VEN_MAX_ROW_PER_WORKER
self.workerAddress = env.VEN_WORKER_ADDR
self.sinkAddr = env.SINK_ADDR
self.context = zmq.Context()
self.sender = self.context.socket(zmq.PUSH)
self.sender.bind(self.workerAddress)
self.processId = os.getpid()
self.system = platform.system()
self.db = DatabaseConnection(env.DB_HOST, env.DB_UNAME,
env.DB_PASSWORD, env.DB_NAME)
self.syslog = SystemLog()
self.outbox = Outbox(self.db)
# ///////////////////////////////////////////
def setWorker(self, numberOfWorker):
for i in range(numberOfWorker):
os.system('py worker.py ' + self.processId)
# ///////////////////////////////////////////
def killWorker(self):
file = open("process/" + self.processId)
workers = file.read()
print(workers)
# ///////////////////////////////////////////
def send(self, data):
sink = self.context.socket(zmq.PUSH)
sink.connect(self.sinkAddr)
i = 1
for item in data:
print("---------------------")
print(f"Outbox ID: {item['outbox_id']}")
print(f"Type: {item['msg_type']}")
# proses mengecek pesan yang valid
# pengecekan dilakukan agar sebuah pesan tidak kembali ke pengirimnya
# atau terjadi looping data terus menerus
isValid = False
invalidReason = ''
if (item['msg_type'] == 'INS' or item['msg_type'] == 'DEL'
or item['msg_type'] == 'UPD'):
if (item['msg_type'] == 'INS' or item['msg_type'] == 'DEL'):
# mengecek pesan ins valid menggunakan
# PK, client_unique_id dan nama tabel
# sistem tidak akan mengirim data yang sama balik lagi ke pengirimnya
isInsideInboxQuery = "select * from tb_sync_inbox where client_unique_id={} and sync_token = '{}' and table_name = '{}' and msg_type='{}'".format(
item['client_unique_id'], item['sync_token'],
item['table_name'], item['msg_type'])
elif (item['msg_type'] == 'UPD'):
isInsideInboxQuery = "select * from tb_sync_inbox where client_unique_id={} and sync_token = '{}' and table_name = '{}' and msg_type='{}'".format(
item['client_unique_id'], item['sync_token'],
item['table_name'], item['msg_type'])
inbox = self.db.executeFetchAll(isInsideInboxQuery)
if (inbox['execute_status']):
clients = [
client['client_unique_id'] for client in inbox['data']
]
print(clients)
if (item['client_unique_id'] not in clients):
isValid = True
else:
self.syslog.insert("ventilator-valid-msg",
"Error get data from outbox")
else:
isValid = True
# print(isValid)
# sys.exit()
print("Status: ", end="")
if (isValid):
# filter DEL msg type
# jangan kirim pesan DEL jika row yang di DEL belum selesai
if (not env.MASTER_MODE and item['msg_type'] == 'DEL'):
checkPRIQuery = """
select * from tb_sync_inbox where msg_type = 'PRI'
and status='waiting'
and table_name = '{}' and row_id = '{}'
"""
checkPRIRes = self.db.executeFetchAll(
checkPRIQuery.format(item['table_name'],
item['query']))
if (checkPRIRes['execute_status']):
if (len(checkPRIRes['data']) > 0):
print("PRI not yet process")
continue
else:
print("check PRI fails")
continue
print('Valid')
packet = {
'client_id': item['client_unique_id'],
'client_key': item['client_key'],
'client_iv': item['client_iv'],
'client_port': item['client_port'],
'client_ip': item['client_ip'],
'msg_type': item['msg_type'],
'row_id': item['row_id'],
'table_name': item['table_name'],
'msg_id': item['outbox_id'],
'occur_at': item['occur_at'],
'sync_token': item['sync_token'],
'first_time_occur_at': item['first_time_occur_at'],
'query': item['query'],
'priority': item['priority'],
'timestamp':
item['created_at'].strftime("%Y-%m-%d %H:%M:%S")
}
nextRetryAt = datetime.datetime.now() + datetime.timedelta(
seconds=30)
nextRetryAt = nextRetryAt.strftime('%Y-%m-%d %H:%M:%S')
if (item['msg_type'] == 'ACK'):
status = 'arrived'
else:
status = 'sent'
self.outbox.update(
data={
'priority': 3,
'status': status,
'retry_again_at': nextRetryAt
},
where_clause={'outbox_id': item['outbox_id']})
self.sender.send_json(packet)
file = open("sendtime.txt", 'a')
file.write(f"{time.time()}\n")
file.close()
else:
invalidReason = 'Loop'
print('Invalid, Reason: {}'.format(invalidReason))
# self.outbox.update(data={'status': 'canceled'}, where_clause={
# 'outbox_id': item['outbox_id']})
query = "update tb_sync_outbox set status='canceled' where outbox_id = {}".format(
item['outbox_id'])
self.db.executeCommit(query)
def test_send_notifications(self, mocked_send, mocked_ready):
"""Test that notifications can be sent."""
mocked_ready.side_effect = [True, False]
outbox = Outbox(self.notifications)
outbox.send_notifications()
mocked_send.assert_called_once()
class Sync:
def __init__(self):
self.syncDB = DatabaseConnection(env.DB_HOST, env.DB_UNAME,
env.DB_PASSWORD, env.DB_NAME)
# self.statusDB = DatabaseConnection(
# env.DB_HOST, env.DB_UNAME, env.DB_PASSWORD, env.DB_NAME)
self.limitRow = env.LIMIT_PROC_ROW
self.outbox = Outbox(self.syncDB)
self.systemlog = SystemLog()
self.inbox = Inbox(self.syncDB)
self.outbox = Outbox(self.syncDB)
self.clientIdStartFrom = 10
self.updateToZeroHistory = set([])
self.PKFileName = 'pk'
self.nextPriToProcess = dict()
self.PRIFileName = 'pri'
def getClient(self):
sql = "select * from tb_sync_client"
return self.syncDB.executeFetchAll(sql)
def _getPrimaryKeyColumn(self, table):
db_name = env.DB_NAME
sql = """
select COLUMN_NAME from information_schema.COLUMNS
where TABLE_SCHEMA='{}' and TABLE_NAME='{}'
and COLUMN_KEY='PRI'
""".format(db_name, table)
res = self.syncDB.executeFetchOne(sql)
return res['data']['COLUMN_NAME']
def setPriority(self, id, table, priority):
db_name = env.DB_NAME
sql = """
select COLUMN_NAME from information_schema.COLUMNS
where TABLE_SCHEMA='{}' and TABLE_NAME='{}'
and COLUMN_KEY='PRI'
""".format(db_name, table)
res = self.syncDB.executeFetchOne(sql)
if (res['execute_status']):
primary_key = res['data']['COLUMN_NAME']
# update primary key
sql = "update {} set priority={} where {}={}"
update = self.syncDB.executeCommit(
sql.format(table, priority, primary_key, id))
if (update):
# update PK success
print("Updated Priority")
else:
self.systemlog.insert(
"Sync.setPriority",
json.dumps(self.syncDB.getLastCommitError()))
def processInsert(self, data):
print(f"Inbox ID: {data['inbox_id']}")
print(f"Type: {data['msg_type']}")
# mengirim bahwa pesan sedang di proses
# self.sendStatusUpdate(data, 'PROC')
insert = self.syncDB.executeCommit(data['query'])
rowId = self.syncDB.lastRowId
if (insert):
# hanya master yang mengirim NEEDPK ke slave
# if(env.MASTER_MODE):
# self.sendStatusUpdate(data, 'NEEDPK')
print("Status: OK")
# set result primary key to table inbox
insert = self.inbox.update(
data={
'result_primary_key': rowId,
},
where_clause={'inbox_id': data['inbox_id']})
# if the msg is sent from master
# update primary key right away
if (data['master_status'] == 1):
# insert to inbox jadi akan dikerjakan di proses selanjutnya
inbox = {
'row_id': rowId,
'table_name': data['table_name'],
'msg_type': 'PRI',
'msg_id': 0,
'query': data['row_id'],
'client_unique_id': data['client_unique_id'],
'master_status': 0,
'priority': 1
}
if (not self.inbox.insert(inbox)):
print(self.syncDB.getLastCommitError())
elif (env.MASTER_MODE):
# master akan mengirim PK hasil insert ke
# slave pengirim pesan insert
msg = {
'row_id': data['row_id'], # local row id
'table_name': data['table_name'],
'msg_type': 'PRI',
'query': rowId, # receiver outbox_id
'client_unique_id': data['client_unique_id'],
'msg_id': 0,
'priority': 1
}
tes = self.outbox.insert(msg)
# print(tes)
if (not tes):
print(self.syncDB.getLastCommitError())
self.setAsProcessed(data['inbox_id'])
else:
# set priority menjadi 3
self.setPriority(data['inbox_id'], 'tb_sync_inbox', 3)
print('error, downgrade priority')
return True
def getZeroPKHistory(self):
file = open(self.PKFileName, 'r')
file_value = file.read()
if (file_value):
self.updateToZeroHistory = set(literal_eval(file_value))
file.close()
def getPriToProcess(self):
file = open(self.PRIFileName, 'r')
file_value = file.read()
if (file_value):
self.nextPriToProcess = literal_eval(file_value)
file.close()
def updateZeroPKHistory(self):
file = open(self.PKFileName, 'w')
file.write(str(list(self.updateToZeroHistory)))
file.close()
def updatePriToProcess(self):
file = open(self.PRIFileName, 'w')
file.write(str(self.nextPriToProcess))
file.close()
def processPrimaryKey(self, data):
print(f"Inbox ID: {data['inbox_id']}")
print(f"Type: {data['msg_type']}")
if (data['row_id'] == int(data['query'])):
self.setAsProcessed(data['inbox_id'])
print("Status: OK Same PK")
return True
self.getPriToProcess()
print(self.nextPriToProcess)
if (data['table_name'] in self.nextPriToProcess):
if (int(data['query']) !=
self.nextPriToProcess[data['table_name']]):
print(
f"Status: {data['query']}/{self.nextPriToProcess[data['table_name']]}"
)
return True
self.getZeroPKHistory()
# check apakah pri ini ada di history update 0
row_id = data['row_id']
if (len(self.updateToZeroHistory) > 0):
# mencari apakah ada history\
code = f"{data['table_name']}{data['row_id']}"
zeroExecMode = False
if (code in self.updateToZeroHistory):
print("Mode: 0 Exec")
data['row_id'] = 0
res = self.doUpdatePK(data)
if (res):
self.updateToZeroHistory.remove(code)
else:
# skip
res = self.doUpdatePK(data)
else:
# langsung eksekusi update
res = self.doUpdatePK(data)
print("Status: ", end="")
print("OK") if res else print("ERROR")
self.updateZeroPKHistory()
# mencari nama kolom primary key
# print(db_name)
def doUpdatePK(self, data):
db_name = env.DB_NAME
sql = """
select COLUMN_NAME from information_schema.COLUMNS
where TABLE_SCHEMA='{}' and TABLE_NAME='{}'
and COLUMN_KEY='PRI'
""".format(db_name, data['table_name'])
res = self.syncDB.executeFetchOne(sql)
if (res['execute_status']):
primary_key = res['data']['COLUMN_NAME']
update_from = data['row_id']
update_to = data['query']
print(f"From: {update_from} To: {update_to}")
sql = "update {} set {}={} where {}={}"
update = self.syncDB.executeCommit(
sql.format(data['table_name'], primary_key, update_to,
primary_key, update_from))
if (update):
# set status outbox menjadi done
if (update_from == 0):
self.nextPriToProcess.pop(data['table_name'])
else:
self.nextPriToProcess[data['table_name']] = update_from
self.updatePriToProcess()
if (data['msg_id'] == 0):
# pesan PRI di generate oleh slave
# mengambil pesan INS
insMsg = self.syncDB.executeFetchOne(
f"select * from tb_sync_inbox where row_id={data['query']} and msg_type='INS' and table_name='{data['table_name']}'"
)
self.sendStatusUpdate(insMsg['data'], 'DONE')
else:
# pesan PRI yang diterima dari master
updateQ = f"update tb_sync_outbox set status='done' where table_name='{data['table_name']}' and msg_type='INS' and row_id = {data['row_id']}"
self.syncDB.executeCommit(updateQ)
# update PK success
# cek pesan lain yang menggunakan PK lama
# update ke PK baru
if (not env.MASTER_MODE):
check = "select * from tb_sync_outbox where (status = 'waiting' or status='canceled') and (msg_type = 'DEL' or msg_type='UPD') and row_id = {}"
res = self.syncDB.executeFetchAll(
check.format(data['row_id']))
if (res['execute_status']):
# update ke PK yang benar
for msg in res['data']:
query = "update tb_sync_outbox set row_id={}, status='waiting' where outbox_id={}"
updated = self.syncDB.executeCommit(
query.format(data['query'], msg['outbox_id']))
if (not updated):
print(self.syncDB.getLastCommitError()['msg'])
else:
print("CHECK PESAN LAIN ERROR: {}".format(
res['error_data']['msg']))
self.setAsProcessed(data['inbox_id'])
return True
else:
# update to zero history
self.setPriority(data['inbox_id'], 'tb_sync_inbox', 3)
allowToAdd = True
for item in self.updateToZeroHistory:
if (data['table_name'] in item):
allowToAdd = False
break
if (allowToAdd):
self.nextPriToProcess[data['table_name']] = update_from
self.updatePriToProcess()
code = f"{data['table_name']}{data['row_id']}"
self.updateToZeroHistory.add(code)
update = self.syncDB.executeCommit(
sql.format(data['table_name'], primary_key, 0,
primary_key, update_from))
return False
# ubah primary key goal menjadi 0
def processUpdate(self, data):
# self.sendStatusUpdate(data, "PROC")
print(f"Inbox ID: {data['inbox_id']}")
print(f"Type: {data['msg_type']}")
# cek apakah pesan ini lebih baru dibantingkan data sekarnag
primary_key = self._getPrimaryKeyColumn(data['table_name'])
row_data = self.syncDB.executeFetchOne(
f"select * from {data['table_name']} where {primary_key}={data['row_id']}"
)
print(
f"{row_data['data']['last_action_at']} : {data['first_time_occur_at']}"
)
if (row_data['data']['last_action_at'] < data['first_time_occur_at']):
# data yang di proses adalah data baru
execute = self.syncDB.executeCommit(data['query'])
if (not execute):
print("Status: ERROR")
else:
self.setAsProcessed(data['inbox_id'])
self.sendStatusUpdate(data, "DONE")
print("Status: OK")
else:
# data yang di proses adlaah data lama
self.setAsProcessed(data['inbox_id'])
self.sendStatusUpdate(data, "DONE")
print("Status: OLD DATA")
def processDelete(self, data):
# self.sendStatusUpdate(data, "PROC")
# cek apakah ada inbox yang bertipe PRI
# berdasarkan primari key yang masuk
# jika ada mata update inbox tersebut jadi terproses
# jika tidak ada lakukan delete seperti biasa
print(f"Inbox ID: {data['inbox_id']}")
print(f"Type: {data['msg_type']}")
checkQuery = """
select count(inbox_id) as total from tb_sync_inbox where msg_type = 'PRI'
and status = 'waiting'
and table_name = '{}'
and query = '{}'
"""
result = self.syncDB.executeFetchOne(
checkQuery.format(data['table_name'], data['query']))
if (result['execute_status']):
if (result['data']['total'] > 0):
print('Skip, total PRI: {}'.format(result['data']['total']))
else:
dltQuery = "delete from {} where {}={}"
pkColumnName = self._getPrimaryKeyColumn(data['table_name'])
delete = self.syncDB.executeCommit(
dltQuery.format(data['table_name'], pkColumnName,
data['row_id']))
if (delete):
self.sendStatusUpdate(data, "DONE")
self.setAsProcessed(data['inbox_id'])
print("Status: OK")
else:
self.setPriority(data['inbox_id'], 'tb_sync_inbox', 3)
print("Status: ERROR")
def processAck(self, data):
print(f"Inbox ID: {data['inbox_id']}")
print(f"Type: {data['msg_type']}")
obox = self.syncDB.executeFetchOne(
f"select * from tb_sync_outbox where outbox_id = {data['query']}")
ack = True
if (obox['data']):
if (obox['data']['msg_type'] == 'INS'):
status = 'need_pk_update'
else:
status = 'arrived'
ack = self.outbox.update(data={'status': status},
where_clause={'outbox_id': data['query']})
# ack = self.syncDB.executeCommit(
# f"update tb_sync_outbox set status='{status}' where outbox_id={data['query']}")
# ackQuery = "update tb_sync_outbox set is_arrived=1, status='arrived' where outbox_id = {}".format(
# data['query'])
# ack = self.syncDB.executeCommit(ackQuery)
if (not ack):
self.outbox.update(data={'status': 'error'},
where_clause={'outbox_id': data['msg_id']})
print("Status: ERROR")
# errorQuery = 'update tb_sync_outbox set is_error=1 where outbox_id = {}'.format(
# data['msg_id'])
# self.syncDB.executeCommit(errorQuery)
# self.systemlog.insert("processACK", "Gagal update ACK ID#{} ERROR: {}".format(
# data['inbox_id'], self.statusDB.getLastCommitError()['msg']))
else:
self.setAsProcessed(data['inbox_id'])
print("Status: OK")
def processReg(self, data):
print(f"Inbox ID: {data['inbox_id']}")
print(f"Type: {data['msg_type']}")
if (env.MASTER_MODE):
time.sleep(0.2)
regData = data['query'].split('#')
reg = {}
for item in regData:
attributes = item.split(':')
reg[attributes[0]] = attributes[1]
# cek apakah ip address sudah terdaftar
checkQuery = f"select count(*) as total from tb_sync_client where client_ip = '{reg['ip_address']}'"
check = self.syncDB.executeFetchOne(checkQuery)
if (check['data']['total'] > 0):
outbox = {
'row_id': 0,
'table_name': '',
'msg_type': 'REG',
'msg_id': 0,
'query':
f"status:ERROR#reason:IP Address sudah digunakan#for:{data['msg_id']}",
'client_unique_id': 0,
'client_ip': reg['ip_address'],
'client_port': reg['port'],
'client_key': reg['secret_key'],
'client_iv': reg['iv_key']
}
self.outbox.insert(outbox)
self.setAsProcessed(data['inbox_id'])
else:
client_id_check_q = "select ifnull(max(client_unique_id), 0) as id from tb_sync_client"
client_id = self.syncDB.executeFetchOne(client_id_check_q)
if (client_id['data']['id'] == 0):
client_id = self.clientIdStartFrom
else:
client_id = client_id['data']['id'] + 1
sql = f"insert into tb_sync_client(client_unique_id, client_key, client_iv, client_port, client_ip) values({client_id}, '{reg['secret_key']}', '{reg['iv_key']}', {reg['port']}, '{reg['ip_address']}')"
inserted = self.syncDB.executeCommit(sql)
if (not inserted):
self.setPriority(data['inbox_id'], 'tb_sync_inbox', 3)
else:
outbox = {
'row_id': 0,
'table_name': '',
'msg_type': 'REG',
'msg_id': 0,
'query':
f"status:OK#id:{client_id}#for:{data['msg_id']}",
'client_unique_id': client_id
}
self.outbox.insert(outbox)
self.setAsProcessed(data['inbox_id'])
print("Status: OK")
else:
outbox = {
'row_id': 0,
'table_name': '',
'msg_type': 'REG',
'msg_id': 0,
'query':
f"status:ERROR#reason:Host bukan master#for:{data['msg_id']}",
'client_unique_id': 0,
'client_ip': reg['ip_address'],
'client_port': reg['port'],
'client_key': reg['secret_key'],
'client_iv': reg['iv_key']
}
self.outbox.insert(outbox)
self.setAsProcessed(data['inbox_id'])
print(f'Status: ERROR')
def getData(self):
self.syncDB.connect()
sql = "(select * from tb_sync_inbox where status = 'waiting' and msg_type <> 'PRI' order by priority asc, inbox_id asc, occur_at asc)"
if (self.limitRow > 0):
sql += f' limit {self.limitRow}'
self.getPriToProcess()
additionalQuery = ""
excludeTables = ""
# tambah query untuk mendapatkan pri yang harus diproses
if (len(self.nextPriToProcess) > 0):
for item in self.nextPriToProcess:
additionalQuery += f" union (select * from tb_sync_inbox where status = 'waiting' and msg_type = 'PRI' and table_name = '{item}' and query = '{self.nextPriToProcess[item]}' order by first_time_occur_at asc, priority asc)"
if (excludeTables != ''):
excludeTables += f" or table_name <> '{item}'"
else:
excludeTables += f"table_name <> '{item}'"
# buat query untuk mengambil PRI masing2 tabel kecuali excluded table
if (excludeTables == ''):
# mengambil pesan PK masing2 1 pada setiap tabel
# additionalQuery = ''
additionalQuery += f" union (SELECT * FROM tb_sync_inbox WHERE msg_type = 'PRI' AND STATUS='waiting' GROUP BY table_name ORDER BY first_time_occur_at ASC, priority ASC)"
else:
additionalQuery += f" union (select * from tb_sync_inbox where status = 'waiting' and msg_type = 'PRI' and ({excludeTables}) group by table_name order by first_time_occur_at asc, priority asc)"
# print(sql + additionalQuery)
data = self.syncDB.executeFetchAll(sql + additionalQuery, False)
self.syncDB.close()
return data
def getStatusInbox(self):
sql = "select * from tb_sync_inbox where status = 'waiting' and (msg_type = 'ACK' or msg_type = 'DONE') order by priority asc, inbox_id asc, occur_at asc"
if (self.limitRow > 0):
sql += f' {self.limitRow}'
data = self.syncDB.executeFetchAll(sql)
return data
def getSyncInbox(self):
sql = "select * from tb_sync_inbox where status = 'waiting' and (msg_type = 'INS' or msg_type = 'UPD' or msg_type = 'DEL' or msg_type = 'REG' or msg_type = 'PRI') order by priority asc, inbox_id asc, occur_at asc"
if (self.limitRow > 0):
sql += f' {self.limitRow}'
data = self.syncDB.executeFetchAll(sql)
return data
def setAsProcessed(self, id, status='done'):
set = self.inbox.update(data={'status': status},
where_clause={'inbox_id': id})
# query = 'update tb_sync_inbox set is_process=1 where inbox_id = {}'.format(
# id)
# print(set)
def sendStatusUpdate(self, data, status):
return self.outbox.insert({
'row_id': data['row_id'], # local row id
'table_name': data['table_name'],
'msg_type': status,
'query': data['msg_id'], # receiver outbox_id
'client_unique_id': data['client_unique_id'],
'msg_id': 0,
'priority': 1
})
def updateOutboxStatus(self, id, status, inbox_id):
upd = self.syncDB.executeCommit(
f"update tb_sync_outbox set status='{status}' where outbox_id={id}"
)
if (upd):
self.setAsProcessed(inbox_id)
else:
self.setPriority(inbox_id, 'tb_sync_inbox', 3)
def canProcessMsg(self, data):
watchedMsgType = ['INS', 'UPD', 'DEL']
if (data['msg_type'] not in watchedMsgType):
return True
# cek apakah ada pesan watchedMsgType yang blm selesai
# sebelum inbox_id ini
# jika slave, harus memastika semua outbox nya selesai di proses di master
# lalu eksekusi inbox
if (not env.MASTER_MODE):
previousMsgs = self.syncDB.executeFetchOne(
"select count(*) as total from tb_sync_outbox where (msg_type = 'INS' or msg_type='UPD' or msg_type='DEL') and status <> 'done'"
)
if (previousMsgs['data']['total'] > 0):
return False
else:
return True
print(previousMsgs)
def process(self, inbox):
if (inbox):
for item in inbox:
# proses pesan selain INS, UPD dan DEL terlebih dahulu
# jgn proses pesan utama jika masih ada pesan INS UPD DEL yang belum selesai
# jika proses adalah INS UPD DEL, lakukan pengecekan pesan tertunda
delayMsgInboxQ = "select count(*) from tb_sync_inbox where status "
print("[{}] -> #{}".format(
datetime.datetime.now().strftime("%d-%m-%Y %H:%M:%S"),
item['msg_id']),
end=" ")
msgType = item['msg_type']
if (msgType == 'INS'):
self.processInsert(item)
elif (msgType == 'UPD'):
self.processUpdate(item)
elif (msgType == 'DEL'):
self.processDelete(item)
elif (msgType == 'ACK'):
self.processAck(item)
elif (msgType == "PRI"):
self.processPrimaryKey(item)
elif (msgType == 'REG'):
self.processReg(item)
elif (msgType == 'PROC'):
print(
self.updateOutboxStatus(item['query'], "processing",
item['inbox_id']))
elif (msgType == 'NEEDPK'):
print(
self.updateOutboxStatus(item['query'],
"need_pk_update",
item['inbox_id']))
elif (msgType == 'DONE'):
done = self.statusDB.executeCommit(
f"update tb_sync_outbox set status = 'done' where outbox_id = {item['query']}"
)
if (done):
print(
self.statusDB.executeCommit(
f"update tb_sync_inbox set status='done' where inbox_id={item['inbox_id']}"
))
else:
print("False")
# print(self.updateOutboxStatus(
# item['query'], "done", item['inbox_id']))
else:
self.syncDB.insError("Msg type not found for id=" +
str(item['inbox_id']))
# print(f"finish at: {time.time()}")
file = open("proctime.text", 'a')
file.write(f"{time.time()}\n")
file.close()
else:
time.sleep(0.3)
def __init__(self, config):
self.outbox = Outbox(server=config.host,
username=config.user,
password=config.password,
port=config.get('port', 25),
mode=config.get('mode', None))
import time
import zmq
import os
import sys
import json
import env
from encryption import AES256
from DatabaseConnection import DatabaseConnection
import time
from outbox import Outbox
import datetime
uniqueId = env.UNIQUE_ID
db = DatabaseConnection(env.DB_HOST, env.DB_UNAME,
env.DB_PASSWORD, env.DB_NAME)
outbox = Outbox(db)
# if(len(sys.argv) == 2):
# fileName = sys.argv[1]
# else:
# fileName = 'worker_process.id'
# print(fileName)
context = zmq.Context()
# f = open("process/"+fileName, "a+")
# f.write(str(os.getpid()) + "\n")
# f.close()
# Socket to receive messages on
receiver = context.socket(zmq.PULL)
receiver.connect("tcp://localhost:5557")