def listen_thread():
pubsub = pgpubsub.connect(database='realtime')
pubsub.listen('todo_items_table_update')
while True:
for event in pubsub.events(yield_timeouts=True):
if event is None:
pass
else:
with app.app_context():
process_message(event)
def subscribe(q_in, db_params, channels):
ps = pgpubsub.connect(**db_params)
for c in channels:
ps.listen(c)
# Each key in this dict should be the id() of the Queue object that we're
# posting to. Each value should be a tuple of (q_object, filter_callback)
subscribers = {}
while True:
# check q_in for any new subscribe/unsubscribe/exit messages.
try:
cmd = q_in.get_nowait()
if cmd[0] == SUBSCRIBE:
q = cmd[1]
should_send = cmd[2]
id_ = id(q)
subscribers[id_] = (q, should_send)
elif cmd[0] == UNSUBSCRIBE:
q = cmd[1]
try:
q.put(None)
except Full:
pass
id_ = (q)
subscribers.pop(id_, None)
elif cmd[0] == EXIT:
ps.close()
for q, _ in subscribers.values():
# Sending a None down a subscriber's queue will tell it that
# we're exiting.
try:
q.put(None)
except Full:
pass
break
except Empty:
pass
# check the pubsub for any new messages.
msgs = ps.get_events(select_timeout=PG_BLOCK_TIME)
if msgs:
for msg in msgs:
for id_, (q, should_send) in subscribers.items():
if should_send(msg):
try:
q.put(msg)
except Full:
pass
del subscribers
def pg_listen_thread():
with flask_app.app_context():
configure_app(flask_app)
pubsub = pgpubsub.connect(
dbname=flask_app.config['DATABASE_NAME'],
user=flask_app.config['DATABASE_USERNAME'],
password=flask_app.config['DATABASE_PASSWORD'],
host=flask_app.config['DATABASE_HOSTNAME'],
port=flask_app.config['DATABASE_PORT'])
pubsub.listen('table_update')
while True:
for event in pubsub.events(yield_timeouts=True):
if event is None:
pass
elif event.channel == 'table_update':
process_table_update(event)
def main(args):
print(args)
local_env = common.LocalEnvironment('PGSQL_USER', 'PGSQL_PASSWORD')
local_env.init()
pgsql_user = local_env.get_variable('PGSQL_USER')
pgsql_password = local_env.get_variable('PGSQL_PASSWORD')
yaml_config = common.read_config_file(args['<initfile>'])
print(common.jsonpretty(yaml_config))
db_host = yaml_config['globals']['database_host']
db_name = yaml_config['globals']['database_name']
pubsub = pgpubsub.connect(host=db_host,
user=pgsql_user,
password=pgsql_password,
database=db_name)
channel_id = args['<channel>']
if not yaml_config['channels'].get(channel_id):
raise NoSuchEventChannel(channel_id)
handler_module_name = yaml_config['globals']['handler_module']
project_dir = common.load_config_var(yaml_config['globals']['project_dir'])
sys.path.append(project_dir)
handlers = __import__(handler_module_name)
handler_function_name = yaml_config['channels'][channel_id][
'handler_function']
if not hasattr(handlers, handler_function_name):
raise NoSuchEventHandler(handler_function_name, handler_module_name)
handler_function = getattr(handlers, handler_function_name)
service_objects = common.ServiceObjectRegistry(
snap.initialize_services(yaml_config, logger))
pubsub.listen(channel_id)
print('listening on channel "%s"...' % channel_id)
for event in pubsub.events():
print(event.payload)
def Testpubsub():
import pgpubsub
postgresuser = parser.get('postgres', 'user')
postgrespass = parser.get('postgres', 'password')
pubsub = pgpubsub.connect(user=postgresuser,
password=postgrespass,
database='vera')
pubsub.listen('vera')
pubsub.listen('plegstatus')
for e in pubsub.events():
if e.channel == "vera":
verastatus = e.payload
"""Send this to another file with a function"""
a = json.loads(verastatus)
deviceid = a['device_id']
devicevariable = a['device_variable']
devicevalue = a['device_value']
lastupdated = a['last_updated']
logevent = ('Time:' + str(lastupdated) + ' ID:' + str(deviceid) +
' Variable:' + str(devicevariable) +
' Value:' + str(devicevalue))
socketio.emit('logging', {'data': logevent})
if (devicevariable == "Status"):
socketio.emit('Switches', {'data': verastatus})
print ("device:" + str(deviceid) + " Value:" +
str(devicevalue))
if devicevariable == "LoadLevelStatus":
socketio.emit('Dimmers', {'data': verastatus})
print ("device:" + str(deviceid) + " Value:" +
str(devicevalue))
if (devicevariable == "ArmedTripped"):
socketio.emit('Sensors', {'data': verastatus})
print ("device:" + str(deviceid) + " Value:" +
str(devicevalue))
if e.channel == "plegstatus":
socketio.emit('Pleg', {'data': e.payload})
print e.payload
def db_listen(channel: str, params: dict) -> None:
"""
Function using pgpubsub for subcribing the notification channel on postgresql database
"""
# creates a log file
logging.basicConfig(filename=f'{datetime.date.today()}_LOG.log',
level=logging.INFO,
format='%(message)s')
# connects to db
pubsub = pgpubsub.connect(user=params['user'], database=params['database'])
# subscribe to notification channel
pubsub.listen(channel)
print('Listening...')
for e in pubsub.events():
logging.info(f'{datetime.datetime.utcnow().isoformat()} {e.payload}')
pubsub.unlisten(channel)
def listen():
log.info(
'Opening database connection',
dict(database=os.environ['PGDB'],
user=os.environ['PGUSER'],
host=os.environ['PGHOST'],
port=os.environ['PGPORT']))
pub_sub = pgpubsub.connect(database=os.environ['PGDB'],
user=os.environ['PGUSER'],
host=os.environ['PGHOST'],
port=os.environ['PGPORT'],
password=os.environ['PGPASSWORD'])
channel_name = 'blocks'
log.debug('Listening to channel', channel_name=channel_name)
pub_sub.listen(channel_name)
while True:
log.debug('Entering event-loop')
for event in pub_sub.events(yield_timeouts=True):
if event is None:
pass
else:
log.debug('Event received', event_data=event)
process_message(event)
import pgpubsub
import psycopg2
import socket
from _thread import *
import threading
import json
import time
conn = psycopg2.connect(user='******',
database='sensors',
password='******',
host='XXXX')
cur = conn.cursor()
pubsub = pgpubsub.connect(user='******',
database='sensors',
password='******',
host='XXXX')
pubsub.listen('UPDATE_TEMP')
pubsub.listen('UPDATE_HUM')
host = ""
data = {"rowID": "", "room": "", "value": "", "type": ""}
dataJson = ""
port = 12345
s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
s.bind((host, port))
s.listen(5)
event = threading.Event()
lock = threading.Lock()
def threaded(c):
#!/usr/bin/python3
import logging
logging.basicConfig(filename='cloud_events.log', format='%(message)s', level=logging.INFO)
import pgpubsub, mysecrets
pubsub = pgpubsub.connect(host='35.233.160.178', user='******', database='main', password=mysecrets.DB_PASSWORD)
from cloudevents.http import CloudEvent, to_binary, to_structured
import requests, json
attributes = {
"type": "com.trifacta.lightweight",
"source": "35.233.160.178:5432",
}
import boto3
sns = boto3.client('sns', region_name='us-west-2', aws_access_key_id=mysecrets.KEY, aws_secret_access_key=mysecrets.SECRET)
pubsub.listen('cloud_events')
for e in pubsub.events():
event = CloudEvent(attributes, data=json.loads(e.payload))
headers, body = to_structured(event)
logging.info(body.decode())
sns.publish(TopicArn='arn:aws:sns:us-west-2:163305015547:cloud_events', Message=str(body))
#!/usr/bin/env python3
import pgpubsub
pubsub = pgpubsub.connect(user="******", database="dump")
pubsub.listen("ci_jobs_status_channel")
try:
for e in pubsub.events():
message = f"""
---------------------------------------
Payload: {e.payload}
channel: {e.channel}
PID: {e.pid}
---------------------------------------
"""
print(message)
except:
pubsub.unlisten("channel2")
print("I dont want to run deaf. I've stopped listening")
def connector(queue: Queue, config: Dict):
# Insert function and trigger to database
try:
connection = psycopg2.connect(user=config['pg']['user'],
password=config['pg']['password'],
host=config['pg']['host'],
port=config['pg']['port'],
database=config['pg']['database'])
if (connection):
print("Connected to postgres successfully!")
cursor = connection.cursor()
create_function_query_update = """CREATE OR REPLACE FUNCTION insertCaugthNotifyUpdate()
RETURNS TRIGGER AS $$
DECLARE
BEGIN
IF NEW.res_field = 'image_1920' AND NEW.res_model='hr.employee' AND OLD.store_fname <> NEW.store_fname THEN
PERFORM pg_notify('insertCaugthNotify', NEW.res_id || ',' || NEW.store_fname );
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;"""
create_function_query_insert = """CREATE OR REPLACE FUNCTION insertCaugthNotifyInsert()
RETURNS TRIGGER AS $$
DECLARE
BEGIN
IF NEW.res_field = 'image_1920' AND NEW.res_model='hr.employee' THEN
PERFORM pg_notify('insertCaugthNotify', NEW.res_id || ',' || NEW.store_fname );
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;"""
drop_trigger_query_update = "DROP TRIGGER IF EXISTS insertCaugthNotifyUpdate ON ir_attachment"
drop_trigger_query_insert = "DROP TRIGGER IF EXISTS insertCaugthNotifyInsert ON ir_attachment"
create_trigger_query_update = """CREATE TRIGGER insertCaugthNotifyUpdate
AFTER UPDATE ON ir_attachment
FOR EACH ROW EXECUTE PROCEDURE insertCaugthNotifyUpdate();
"""
create_trigger_query_insert = """CREATE TRIGGER insertCaugthNotifyInsert
AFTER INSERT ON ir_attachment
FOR EACH ROW EXECUTE PROCEDURE insertCaugthNotifyInsert();
"""
cursor.execute(create_function_query_update)
cursor.execute(create_function_query_insert)
print(
'insertCaugthNotifyUpdate() and insertCaugthNotifyInsert() function inserted'
)
cursor.execute(drop_trigger_query_insert)
cursor.execute(drop_trigger_query_update)
cursor.execute(create_trigger_query_update)
cursor.execute(create_trigger_query_insert)
print(
'insertCaugthNotifyUpdate and insertCaugthNotifyInsert trigger inserted'
)
connection.commit()
except (Exception, psycopg2.Error) as error:
print("Error while fetching data from PostgreSQL", error)
pubsub = pgpubsub.connect(user=config['pg']['user'],
database=config['pg']['database'],
password=config['pg']['password'])
if (pubsub):
print("Server is listening...")
pubsub.listen('"insertCaugthNotify"')
for e in pubsub.events():
print(e.payload)
id, path = e.payload.split(',')
employee_name_query = "SELECT name FROM hr_employee WHERE id = " + id + ";"
cursor.execute(employee_name_query)
# cursor.execute("SELECT name FROM hr_employee WHERE id = %s ;", (id))
record = cursor.fetchone()
for attribute in record:
print(attribute)
emp_name = attribute
source_path = config['path']['source'] + '/' + path
destination_path = config['path']['dest'] + '/' + emp_name
print(source_path)
print(destination_path)
dest = shutil.copy(source_path, destination_path)
queue.put((destination_path, emp_name))
print(dest)
with open("/usr/local/etc/postgres2bgp.config.json", 'r') as _:
config = json.loads(_.read())
eprint("Config loaded.")
conn_string = "dbname='{}' user='******' host='{}' password='******'".format(
config['db_name'], config['db_user'], config['db_host'], config['db_pass'])
with psycopg2.connect(conn_string) as conn:
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
cursor = conn.cursor()
eprint("Connected to database on", config['db_host'])
pubsub = pgpubsub.connect(conn_string)
pubsub.listen('ip_list_updated')
eprint("Connected to Postgres event notification subsystem")
prefixes_loaded = []
while True:
eprint("Going to insert/withdraw prefixes now.")
prefixes_to_load = []
cursor.execute(
"select distinct srcaddr from ti_event where now() > start_stamp and now() < end_stamp order by srcaddr;"
)
for row in cursor:
prefixes_to_load.append(netaddr.IPNetwork(row[0]))
cur.execute('''
SET search_path = billing, pg_catalog;
UPDATE billing.clients SET voip_limit_month = 0, voip_limit_day = 0, credit = 200000000
WHERE id = %(clientId)d''' % {'clientId': TEST_CLIENT_ID})
conn.commit()
# Ждём завершения синхронизации "центр->регион"
cur.execute('''
SET search_path = event, pg_catalog;
SELECT count(server_id) FROM queue WHERE server_id IN (%(regions)s);
''' % {'regions': ','.join(regionsList)})
rows = cur.fetchall()
if rows[0][0] > 0:
print 'Ждём, когда все регионы заполнят свои базы, забрав данные из центральной'
onsync = pgpubsub.connect(user='******', database='nispd_test')
onsync.listen('queuedeleted')
for e in onsync.events(select_timeout=10 * 60, yield_timeouts=True):
if e is None:
print 'ERROR: Синхронизация из центра в регионы неудачна:'
cur.execute('''
SET search_path = event, pg_catalog;
SELECT * FROM queue WHERE server_id IN (%(regions)s);
''' % {'regions': ','.join(regionsList)})
rows = cur.fetchall()
print rows
sys.exit(1)
else:
sys.stdout.write('.')
cur.execute('''