def get_by_lower_email(workspace_id, email):
query = """SELECT * FROM "user"
WHERE workspace_id = %i
AND lower(email) = lower(%s)
LIMIT 1""" % (workspace_id,
escape_pg_string(email))
r = Session.execute(query)
rr = r.fetchone()
Session.rollback()
return rr
def get_by_lower_email(workspace_id, email):
query = """SELECT * FROM "user"
WHERE workspace_id = %i
AND lower(email) = lower(%s)
LIMIT 1""" % (
workspace_id,
escape_pg_string(email),
)
r = Session.execute(query)
rr = r.fetchone()
Session.rollback()
return rr
def init_model(junk=None):
# Connect.
new_engine()
# Import models.
import_models()
# Setup elixir entities.
setup_all()
# Bind session to the engine.
Session.configure(bind=_engine)
def requested_in_last_seconds(workspace_id, user_id, max_seconds):
query = """SELECT * FROM wsrequest
WHERE workspace_id=%i
AND user_id=%i
AND (date_part('epoch', now()) - req_date) < %i
ORDER BY req_date DESC
LIMIT 1""" \
% ( workspace_id, user_id, max_seconds)
log.debug("requested_in_last_seconds(): query='%s'." % (query))
r = Session.execute(query)
rr = r.fetchone()
Session.rollback()
if rr:
return True
return False
def requested_in_last_seconds(workspace_id, user_id, max_seconds):
query = """SELECT * FROM wsrequest
WHERE workspace_id=%i
AND user_id=%i
AND (date_part('epoch', now()) - req_date) < %i
ORDER BY req_date DESC
LIMIT 1""" \
% ( workspace_id, user_id, max_seconds)
log.debug("requested_in_last_seconds(): query='%s'." % ( query ) )
r = Session.execute(query)
rr = r.fetchone()
Session.rollback()
if rr:
return True
return False
def from_evt(evt):
# Extract information from event.
workspace_id = evt.kws_id
# workspace id # = evt.anp.get_u64(0)
date = evt.anp.get_u64(1)
if evt.minor >= 3: request_id = evt.anp.get_u64(2)
else: request_id = evt.anp.get_u32(2)
user_id = evt.anp.get_u32(3)
# subject # = evt.anp.get_str(4)
timeout = evt.anp.get_u32(5)
# Workaround part A:
# Delete former requests with the same (workspace_id - request_id) key.
while 1:
chat_req = Session.query(ChatRequest).filter_by(workspace_id=workspace_id, request_id=request_id).first()
if not chat_req: break
chat_req.delete()
# Create a new chat request instance.
chat_req = ChatRequest()
# Fill chat request.
chat_req.workspace_id = workspace_id
chat_req.request_id = request_id
chat_req.req_date = date
chat_req.user_id = user_id
chat_req.timeout = timeout
return chat_req
def from_evt(evt):
# Extract information from event.
workspace_id = evt.kws_id
# workspace id # = evt.anp.get_u64(0)
date = evt.anp.get_u64(1)
if evt.minor >= 3: request_id = evt.anp.get_u64(2)
else: request_id = evt.anp.get_u32(2)
user_id = evt.anp.get_u32(3)
subject = evt.anp.get_str(4)
# Workaround part A:
# Delete former requests with the same (workspace_id - request_id) key.
while 1:
ws_req = Session.query(WSRequest).filter_by(
workspace_id=workspace_id, request_id=request_id).first()
if not ws_req: break
ws_req.delete()
# Create a new workspace request instance.
ws_req = WSRequest()
# Fill request.
ws_req.workspace_id = workspace_id
ws_req.request_id = request_id
ws_req.req_date = date
ws_req.user_id = user_id
ws_req.subject = subject
return ws_req
def from_evt(evt):
# Extract information from event.
workspace_id = evt.kws_id
# workspace id # = evt.anp.get_u64(0)
date = evt.anp.get_u64(1)
if evt.minor >= 3: request_id = evt.anp.get_u64(2)
else: request_id = evt.anp.get_u32(2)
user_id = evt.anp.get_u32(3)
subject = evt.anp.get_str(4)
# Workaround part A:
# Delete former requests with the same (workspace_id - request_id) key.
while 1:
ws_req = Session.query(WSRequest).filter_by(workspace_id=workspace_id, request_id=request_id).first()
if not ws_req: break
ws_req.delete()
# Create a new workspace request instance.
ws_req = WSRequest()
# Fill request.
ws_req.workspace_id = workspace_id
ws_req.request_id = request_id
ws_req.req_date = date
ws_req.user_id = user_id
ws_req.subject = subject
return ws_req
def accepted_lately(workspace_id, user_id):
max_seconds = 600
query = """SELECT * FROM chatrequest
WHERE workspace_id=%i
AND user_id=%i
AND accepted=True
AND (date_part('epoch', now()) - accepted_date) < %i
ORDER BY accepted_date DESC
LIMIT 1""" \
% ( workspace_id, user_id, max_seconds)
log.debug("accepted_in_last_seconds(): query='%s'." % ( query ) )
r = Session.execute(query)
rr = r.fetchone()
Session.rollback()
if rr:
return True
return False
def init_local():
# Change session engine bind, if needed.
if Session.get_bind(None) != _engine:
log.debug("init_local(): [Re-]configuring session bind.")
Session.configure(bind=_engine)
# Detect stale pool database connections.
# In case of failure, the pool will reconnect automatically.
# This is not needed when using only models, but it is needed when using either the engine
# or the session directly to do raw queries.
try:
Session.execute("SELECT 1")
except OperationalError, e:
if getattr(e, 'connection_invalidated') and e.connection_invalidated:
log.warn("init_local(): database connection was invalidated.")
else:
log.warn("init_local(): database exception: '%s'." % ( str(e) ) )
def update_registered_from_evt(evt):
workspace_id = evt.kws_id
# workspace_id # = evt.anp.get_u64(0)
# date # = evt.anp.get_u64(1)
user_id = evt.anp.get_u32(2)
real_name = evt.anp.get_str(3)
# ...
user = Session.query(User).filter_by(workspace_id=workspace_id, id=user_id).first()
user.real_name = real_name
user.evt_id = evt.evt_id
return user
def update_registered_from_evt(evt):
workspace_id = evt.kws_id
# workspace_id # = evt.anp.get_u64(0)
# date # = evt.anp.get_u64(1)
user_id = evt.anp.get_u32(2)
real_name = evt.anp.get_str(3)
# ...
user = Session.query(User).filter_by(workspace_id=workspace_id,
id=user_id).first()
user.real_name = real_name
user.evt_id = evt.evt_id
return user
def stop_from_evt(evt):
# Get informations from event.
workspace_id = evt.kws_id
# workspace_id # = evt.anp.get_u64(0)
# date # = evt.anp.get_u64(1)
user_id = evt.anp.get_u32(2)
session_id = evt.anp.get_u64(3)
# Get the corresponding vnc request.
vnc_sess = Session.query(VncSession).filter_by(workspace_id=workspace_id, session_id=session_id).first()
# Delete the session.
vnc_sess.delete()
return vnc_sess
def stop_from_evt(evt):
# Get informations from event.
workspace_id = evt.kws_id
# workspace_id # = evt.anp.get_u64(0)
# date # = evt.anp.get_u64(1)
user_id = evt.anp.get_u32(2)
session_id = evt.anp.get_u64(3)
# Get the corresponding vnc request.
vnc_sess = Session.query(VncSession).filter_by(
workspace_id=workspace_id, session_id=session_id).first()
# Delete the session.
vnc_sess.delete()
return vnc_sess
def accept_from_evt(evt):
# Extract informations from event.
workspace_id = evt.anp.get_u64(0)
date = evt.anp.get_u64(1)
if evt.minor >= 3: request_id = evt.anp.get_u64(2)
else: request_id = evt.anp.get_u32(2)
user_id = evt.anp.get_u32(3)
channel_id = evt.anp.get_u32(4)
# Get the corresponding chat request.
chat_req = Session.query(ChatRequest).filter_by(workspace_id=workspace_id, request_id=request_id).first()
# Update the chat request.
chat_req.accepted = True
chat_req.accepted_date = date
chat_req.channel_id = channel_id
# Workaround part B:
# Nothing to do - read the note above.
return chat_req
def delete_workspace(workspace_id):
# This part is done with the current transaction (commit must be done in the callers).
ws = Workspace.get_by(id=workspace_id)
if not ws: return
ws.deleted = True
Session.execute("DELETE FROM chatmessage WHERE workspace_id=%i" % ( workspace_id ))
Session.execute("DELETE FROM chatrequest WHERE workspace_id=%i" % ( workspace_id ))
Session.execute("DELETE FROM kfsnode WHERE workspace_id=%i" % ( workspace_id ))
Session.execute("DELETE FROM vncsession WHERE workspace_id=%i" % ( workspace_id ))
Session.execute("DELETE FROM wsrequest WHERE workspace_id=%i" % ( workspace_id ))
Session.execute("DELETE FROM \"user\" WHERE workspace_id=%i" % ( workspace_id ))
def reset_thread_local_session():
Session.remove()