def remove_session(isloggedin, user, session):
if isloggedin:
db.sendtodb('''
update _user_session
set timedout = True
where u_id = %(user_id)s and sess_id = %(sessid)s ''', \
{'user_id':user, 'sessid':session})
def add_user(isuser, user, password):
if not isuser:
cur = db.sendtodb('''
insert into _user (
u_id,
screen_name,
email)
values (
uuid_generate_v4(),
%(usersn)s,
uuid_generate_v4()
)
returning u_id
''',
{'usersn':user})
set_password(user, password, isuser)
u_id = UUID_adapter(list(cur)[0]['u_id'])
#todo:tony:new shareable_item rows after user is created are an issue
#todo:tony:either rethink completely or add a trigger (yuk)
#initialize all currently defined shareable items with the
#default sharing level that is setup for each item
cur = db.sendtodb('''
insert into personal_sharing_level
select %(u_id)s, id, default_sharing_level_id
from shareable_item
''' % {'u_id':u_id})
return u_id
return None
def is_loggedin(session):
remove_stale_sessions()
if session != None and session != '':
res = None
try:
res = db.sendtodb('''
select
u_id,
last_seen
from _user_session
where sess_id = %(sessid)s
and timedout != True
''', {'sessid':session})
except psycopg2.DataError:
return False, 'noexist'
reslist = list(res)
if len(reslist) == 1:
for item in reslist:
last_seen = item['last_seen']
now = datetime.utcnow()
if last_seen + _TIMEOUT > now:
cur = db.sendtodb('''
update _user_session
set last_seen =
(select current_timestamp at time zone 'utc')
where sess_id=%(sessid)s
''', {'sessid':session})
return True, 'updated'
else:
return False, 'timedout'
return False, 'nosession'
def do_update_usr_profile(u_id, name, nameopt, email, emailopt, country,
countryopt, phone, phoneopt, picopt, moodcur,
moodlim, moodall):
sql = '''
update _user
set name = '%(name)s',
email = '%(email)s',
country = '%(country)s',
phone = '%(phone)s'
where u_id = %(u_id)s
'''
# todo:tony: have lib.db auto handle uuid types
uuid_id = UUID_adapter(u_id)
data = {'name':str(name),
'email':str(email),
'country':str(country),
'phone':str(phone),
'u_id':uuid_id}
sendtodb( sql % data)
# todo:tony: shareable_item descr needs to be globalized (yuk)
# todo:tony: make these updates dynamic?
do_update_usr_sharing_status(u_id, 'profile.name', nameopt)
do_update_usr_sharing_status(u_id, 'profile.email', emailopt)
do_update_usr_sharing_status(u_id, 'profile.country', countryopt)
do_update_usr_sharing_status(u_id, 'profile.phone', phoneopt)
do_update_usr_sharing_status(u_id, 'profile.pic', picopt)
do_update_usr_sharing_status(u_id, 'mood.current', moodcur)
do_update_usr_sharing_status(u_id, 'mood.history_limited', moodlim)
do_update_usr_sharing_status(u_id, 'mood.history_all', moodall)
def do_insert_usr_relationship(u_id, other_u_id):
xuuid = UUID_adapter(uuid.uuid4())
u_id = UUID_adapter(u_id)
o_u_id = UUID_adapter(other_u_id)
sendtodb(_SQL_INSERT_INTO_RELATIONSHIP %
{'id':xuuid, 'u_id':u_id, 'o_u_id':o_u_id, 'sharing_level_id':4})
def _delete_yo_message(msg_id):
msg_id = UUID_adapter(msg_id)
stmt = '''delete from yo_message where message_id = %(id)s'''
sendtodb(stmt, {
'id': msg_id,
})
def _delete_message(msg_id):
_delete_tokens(msg_id)
stmt = '''delete from message where id = %(id)s'''
sendtodb(stmt, {
'id': msg_id,
})
def do_add_clust(traj_id, data):
#print "location_data:do_add_clust"
sql = """
insert into location_clust(
traj_id,
clust_id,
clust_entry_time,
clust_exit_time,
clust_points,
clust_accum_time,
clust_accum_dist,
clust_avg_vel,
clust_avg_ke,
clust_avg_lat,
clust_avg_lon
) values (
%(xtraj_id)s,
%(xclust_id)s,
TIMESTAMP %(clust_entry_time)s,
TIMESTAMP %(clust_exit_time)s,
%(clust_points)s,
INTERVAL %(clust_accum_time)s,
%(clust_accum_dist)s,
%(clust_avg_vel)s,
%(clust_avg_ke)s,
%(clust_avg_lat)s,
%(clust_avg_lon)s
)
"""
data['clust_entry_time'] = "'" + str(data['clust_entry_time']) + "'"
data['clust_exit_time'] = "'" + str(data['clust_exit_time']) + "'"
sendtodb(sql, data)
def do_post_yo_toggle(msg_id, show_all):
sql = '''
update yo_message set show_all = %(show_all)s
where message_id = %(message_id)s
'''
toggle = False if show_all == 'True' else True
sendtodb(sql, {'message_id': msg_id, 'show_all': toggle})
def do_update_mood(mood_msg_id, mood_type_id, sharing_level_id, body):
msg_id = UUID_adapter(mood_msg_id)
_update_message(msg_id, body, sharing_level_id)
stmt = '''update mood_message set mood_type_id = %(mood_type_id)s
where message_id = %(message_id)s'''
sendtodb(stmt, {'mood_type_id': mood_type_id, 'message_id': msg_id})
def _update_message(msg_id, body, sharing_level_id):
sendtodb(_SQL_UPDATE_MESSAGE, {
'id': msg_id,
'body': body,
'sharing_level_id': sharing_level_id
})
#save any tokens
_manage_tokens(msg_id, body)
def do_remove_all_traj(u_id):
sql = """
delete from location_traj
where traj_id in (
select traj_id
from location_traj a, _user_session b
where a.sess_id=b.sess_id
and b.u_id = %(u_id)s)
"""
sendtodb(sql, {'u_id': u_id})
def do_post_yo_reply(head_uuid, u_id, note):
"""
head_uuid: the message id of the originating yo
u_id: the person replying
note: the reply text
"""
sql = '''
insert into yo_reply (
message_id,
from_u_id,
body,
time_stamp,
been_viewed
)
values (
%(message_id)s,
%(from_u_id)s,
%(body)s,
(select current_timestamp at time zone 'utc'),
%(been_viewed)s
)
'''
#get the message id's that match the head, expect 2, sender & receiver
cur = sendtodb('select * from message_view where head_id=%(id)s',
{'id': head_uuid})
for row in cur:
params = {}
params['message_id'] = row['id']
params['message_head_id'] = row['head_id']
params['from_u_id'] = u_id
params['body'] = note
#the message is marked as viewed from the get-go for the sender
params['been_viewed'] = u_id == row['owner_u_id']
sendtodb(sql, params)
_update_message_timestamp(row['id'])
#determine the other party
to_u_id = \
row['to_u_id'] if u_id == row['from_u_id'] else row['from_u_id']
#only send an email to the receiver of the yo reply
if to_u_id == row['owner_u_id']:
#now send an email notification
user_data.do_notify_via_email_of_new_message(
u_id, to_u_id, row['id'])
#send an application notification
pages.data_stream.notify(to_u_id)
def do_remove_all_places_from_all_clusts(u_id):
#print "do_remove_all_places_from_all_clusts", str(u_id)
sql = """
delete from location_clust_2_place_map
where place_id in (
select place_id
from place a
where u_id = %(u_id)s
)
"""
sendtodb(sql, {'u_id': u_id})
def _update_message_timestamp(msg_id):
'''
unarchive any message that has it's updated_timestamp updated
'''
sql = '''update message
set updated_timestamp =
(select current_timestamp at time zone 'utc'),
is_archived = false
where id = %(msg_id)s
'''
sendtodb(sql, {'msg_id': msg_id})
def _do_add_yo_mapping(yo_msg_id, parent_msg_id):
sql = '''
insert into yo_2_parent_message_map (
parent_message_id,
yo_message_id
) values (
%(parent_msg_id)s,
%(yo_msg_id)s
)
'''
#one row for the yo owner
sendtodb(sql, {'yo_msg_id': yo_msg_id, 'parent_msg_id': parent_msg_id})
def do_get_latest_user_location(u_id, minutes=None):
#do this to avoid table scan when no matching rows
#(hack after trying for an hour to do correctly)
args = {'u_id': u_id, 'minutes': minutes}
sql = """
select count(*) as items
from
location a,
_user_session b
where
a.sess_id = b.sess_id and
b.u_id = %(u_id)s
"""
result = sendtodb(sql, args)
for item in result:
if item['items'] == 0: return None
#only here if there are matching rows
sql = """
with priorloc as (
select
a.*,
""" + _SQL_CASE_TIME_DELTA + """
from
location a
where a.sess_id in (select b.sess_id from _user_session b where b.u_id = %(u_id)s)
order by time desc
limit 1
)
"""
if minutes is not None:
sql += """
select * from priorloc
where (current_timestamp - time) <= interval '%(minutes)s minutes'
"""
else:
sql += """
select * from priorloc
"""
#print sql % {'u_id':u_id, 'minutes':minutes}
result = sendtodb(sql, {'u_id': u_id, 'minutes': minutes})
for item in result:
return dict(item.items())
return None
def do_get_screen_name_email(screen_name):
cur = sendtodb('select email from _user where screen_name = %(sn)s',
{'sn': screen_name})
curlist = list(cur)
if len(curlist) == 1:
return curlist[0]['email']
return None
def do_get_email(u_id):
cur = sendtodb('select email from _user where u_id = %(uuid)s',
{'uuid': u_id})
curlist = list(cur)
if len(curlist) == 1:
return curlist[0]['email']
return None
def do_get_user_sn(u_id):
cur = sendtodb('select screen_name from _user where u_id = %(uuid)s',
{'uuid': u_id})
curlist = list(cur)
if len(curlist) == 1:
return curlist[0]['screen_name']
return None
def do_set_mood_loc_address(msg_id, loc):
stmt = '''
update mood_message
set fuzzy_location = %(loc)s
where message_id = %(msg_id)s
'''
return sendtodb(stmt, {'msg_id': msg_id, 'loc': loc})
def do_get_usr_relationship(owner_u_id, other_u_id):
sql = 'select * from relationships_view where owner_u_id=%(own_u_id)s' + \
' and other_u_id=%(oth_u_id)s'
own_u_id = UUID_adapter(owner_u_id)
oth_u_id = UUID_adapter(other_u_id)
for row in sendtodb(sql % {'own_u_id':own_u_id, 'oth_u_id':oth_u_id}):
return row
def do_set_mood_loc_weather(msg_id, weather):
stmt = '''
update mood_message
set fuzzy_weather = %(weather)s
where message_id = %(msg_id)s
'''
return sendtodb(stmt, {'msg_id': msg_id, 'weather': weather})
def get_invite_tokens(u_id):
cur = sendtodb('''
select invite_token, is_used from invite_token where owner = %(u_id)s
order by is_used
''', {'u_id' : u_id})
return cur
def do_get_a_yo_message(msg_id):
sql = '''
select
m.id as msg_id,
m.sess_id as msg_sess_id,
m.head_id as msg_head_id,
m.owner_u_id as msg_owner_u_id,
to_u.u_id as msg_to_u_id,
to_u.screen_name as msg_to_sn,
from_u.u_id as msg_from_u_id,
from_u.screen_name as msg_from_sn,
m.sharing_level_id as sharing_level_id,
to_char(m.created_timestamp,
'Day Month DD YYYY HH12:MIpm TZ') as msg_time,
m.body as msg_body,
m.show_all as show_all
from yo_message_view as m
join _user as to_u
on m.to_u_id = to_u.u_id
join _user as from_u
on m.from_u_id = from_u.u_id
'''
stmt = sql + ' where m.id=%(msg_id)s ' ''
return sendtodb(stmt, {
'msg_id': msg_id,
})
def do_get_yo_message_reply_count(msg_id, new_only=False):
sql = '''
select count(*) from yo_reply where message_id = %(message_id)s
'''
if new_only:
sql = sql + ' and been_viewed = false'
return list(sendtodb(sql, {'message_id': msg_id}))[0]['count']
def do_get_location(loc_id):
sql = """
select * from location where loc_id = %(loc_id)s
"""
cur = sendtodb(sql, {'loc_id': loc_id})
for loc in cur:
return dict(loc.items())
def do_update_clust(clust_id, data):
#print "location_data:do_update_clust"
sql = """
update location_clust
set clust_exit_time = TIMESTAMP %(clust_exit_time)s,
clust_points = %(clust_points)s,
clust_accum_time = INTERVAL %(clust_accum_time)s,
clust_accum_dist = %(clust_accum_dist)s,
clust_avg_vel = %(clust_avg_vel)s,
clust_avg_ke = %(clust_avg_ke)s,
clust_avg_lat = %(clust_avg_lat)s,
clust_avg_lon = %(clust_avg_lon)s
where clust_id = %(xclust_id)s
"""
data['clust_exit_time'] = "'" + str(data['clust_exit_time']) + "'"
sendtodb(sql, data)
def start_processing():
print "start_processing"
#remove prior processing results for user
location_data.do_remove_all_traj(u_id)
#print "get a complete list of location sessions for a user"
ssql = """
select distinct a.sess_id
from location a,
_user_session b,
_user c
where a.sess_id = b.sess_id
and b.u_id = c.u_id
and c.screen_name = %(screen_name)s
"""
sessions = db.sendtodb(ssql, {'screen_name': user})
#sql to get a complete list of locations for a session
#ordered by ascending time (oldest to newest)
lsql = """
select a.*, c.u_id, c.screen_name
from location a,
_user_session b,
_user c
where a.sess_id = b.sess_id
and b.u_id = c.u_id
and c.screen_name = %(screen_name)s
and a.sess_id = %(sess_id)s
order by b.sess_id, a.time asc
"""
for sess in sessions:
print ""
print ""
print "fetching next set of locations for session: " + str(
sess['sess_id'])
sess_id = UUID_adapter(sess['sess_id'])
sess_locs = []
for record in db.sendtodb(lsql, {
'screen_name': user,
'sess_id': sess_id
}):
sess_locs.append(dict(record.items()))
process_locations(sess['sess_id'], sess_locs)
def render_post_profilepic(isloggedin, user, request, useruuid):
if not isloggedin:
return comn.sp('You are not logged in', isloggedin, user)
else:
binary = psycopg2.Binary(request.files.get('datafile').file.read())
db.sendtodb(
'''
update _user
set
profile_pic = %(picdata)s,
profile_pic_mimetype = %(mimetype)s
where u_id = %(uuid)s
''', {
'picdata': binary,
'mimetype': getattr(request.files.get('datafile'), 'type'),
'uuid': useruuid
})
