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_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 _add_message(sess_id, head_uuid, parent_uuid, owner_uuid, from_uuid,
to_uuid, msg_type_id, sharing_level_id, body,
notify_via_email):
"""
use this function to add a message to the message table, may or may
not need to be combined with a message type specific table.
all messages expect a specific message_type_id... there is no such
thing as a default message type. invite, mood, yo are examples
of specific message types.
do not export this function. all message types will have their own
function to create clean separation and to create explicit usage
"""
#todo:tony:at some point data functions will need to become transactional
stmt = _SQL_INSERT_INTO_MESSAGE
#unique msg id generator
#todo:tony:revisit if all msg id's need to be unique for all msg types
clean_msg_uuid = uuid.uuid4()
msg_uuid = UUID_adapter(clean_msg_uuid)
#todo:tony:msg_type_id needs a struct
#todo:tony:sharing_level_id needs a struct
params = {
'id': msg_uuid,
'sess_id': sess_id,
'head_id': head_uuid,
'parent_id': parent_uuid,
'owner_u_id': owner_uuid,
'from_u_id': from_uuid,
'to_u_id': to_uuid,
'msg_type_id': msg_type_id,
'sharing_level_id': sharing_level_id,
'body': body
}
#save to db
msg_id = UUID_adapter(list(sendtodb(stmt, params))[0]['id'])
#save any tokens
_manage_tokens(msg_id, body)
if notify_via_email:
user_data.do_notify_via_email_of_new_message(from_uuid, to_uuid,
clean_msg_uuid)
pages.data_stream.notify(to_uuid)
return msg_id
def do_put_traj(sess_id, traj_id, data):
"""
data: a dictionary of all necessary data
"""
#print "do_put_traj:sess_id: "+str(sess_id)+": traj_id:"+str(traj_id)
data['xsess_id'] = UUID_adapter(sess_id) #avoid byref crap
data['xtraj_id'] = UUID_adapter(traj_id) #avoid byref crap
if do_get_traj(traj_id) == None:
#print "location_data:do_put_traj:add traj: "+str(traj_id)
do_add_traj(traj_id, data)
else:
#print "location_data:do_put_traj:update traj: "+str(traj_id)
do_update_traj(traj_id, data)
def do_get_usr_search_results(u_id, search_text):
# only does a pattern match on screen_name and name, returns all users
# when search_text is None (this limited algo is temporary)
u_id = UUID_adapter(u_id)
a_id = UUID_adapter(ANONYMOUS_USER)
if search_text == None: search_text = ''
sql = '''
select *
from _user
where (screen_name like '%{{st}}%' or
name like '%{{st}}%') and
u_id != {{u_id}} and u_id != {{a_id}}
'''
return sendtodb(
pystache.render(sql, {'st':search_text, 'u_id':u_id, 'a_id':a_id}))
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 _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 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_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 do_add_place_2_clust(clust_id, data):
#print "do_add_place_2_clust:start"
sql = """
insert into location_clust_2_place_map (
clust_id,
place_id,
proximity,
dist
) values (
%(xclust_id)s,
%(xplace_id)s,
%(proximity)s,
%(dist_delta)s
)
"""
#print str(clust_id), str(data)
data['xclust_id'] = UUID_adapter(clust_id)
data['xplace_id'] = UUID_adapter(data['place_id'])
sendtodb(sql, data)
def do_batch_add_place_2_clust(batch):
#print "do_batch_add_place_2_clust:start"
sql = """
insert into location_clust_2_place_map (
clust_id,
place_id,
proximity,
dist
) values
"""
value_tmpl = "(%(clust_id)s, %(place_id)s, %(proximity)s, %(dist_delta)s)"
visited = False
terminator = ' '
counter = 0
for item in batch:
counter += 1
clust_id = item['clust_id']
place = item['place']
#print str(clust_id), str(item)
args = {
'clust_id': UUID_adapter(clust_id),
'place_id': UUID_adapter(place['place_id']),
'proximity': place['proximity'],
'dist_delta': place['dist_delta']
}
#print str(place['dist_delta'])
if visited: terminator = ', '
sql = sql + terminator + (value_tmpl % args)
visited = True
#print 'batch items', str(counter)
#print sql
sendtodb(sql, {})
def do_post_invite(sess_id, from_u_id, to_u_id, note):
#use the same head_id to link these two messages for later invite response
h_uuid = UUID_adapter(uuid.uuid4())
print from_u_id == to_u_id
# one entry for sender
print 'invite from'
_do_add_invite(sess_id, h_uuid, from_u_id, from_u_id, to_u_id, note, False)
# one entry for receiver
print 'invite to'
_do_add_invite(sess_id, h_uuid, to_u_id, from_u_id, to_u_id, note, True)
def do_get_traj(traj_id):
#print "location:data:do_get_traj"
sql = """
select *
from location_traj
where traj_id = %(traj_id)s
"""
traj_uuid = UUID_adapter(traj_id)
cur = sendtodb(sql, {'traj_id': traj_uuid}).fetchall()
for row in cur:
return dict(row.items())
return None
def do_get_clust(clust_id):
#print "location:data:do_get_clust"
sql = """
select *
from location_clust
where clust_id = %(clust_id)s
"""
clust_uuid = UUID_adapter(clust_id)
cur = sendtodb(sql, {'clust_id': clust_uuid}).fetchall()
for row in cur:
return dict(row.items())
return None
def do_add_place(u_id, data, redomap=True):
"""
only works with defaults for now
"""
#print 'location_data:do_add_place'
sql = """
insert into place (
place_id,
u_id,
latitude,
longitude,
altitude,
descr,
dist_at
) values (
%(place_id)s,
%(u_id)s,
%(latitude)s,
%(longitude)s,
%(altitude)s,
%(descr)s,
%(dist_at)s
)
"""
data = data[0]
data['place_id'] = UUID_adapter(uuid.uuid4())
data['u_id'] = UUID_adapter(u_id)
try:
at_dist = int(data['at_dist'])
except:
print 'defaulting at_dist to: ', str(loc.PLACE_AT_THLD)
data['at_dist'] = loc.PLACE_AT_THLD #default
print "adding place:", str(data)
sendtodb(sql, data)
#process mappings
if redomap: do_redo_clust_2_place_mappings(u_id)
def do_get_latest_mood_age(u_id):
sql = '''select age(CURRENT_TIMESTAMP, created_timestamp) as moodage
from message
where message_type_id = 0
and owner_u_id = %(u_id)s
order by created_timestamp desc limit 1
'''
u_id = UUID_adapter(u_id)
cur = db.sendtodb(sql % {'u_id': u_id})
age = -1
for row in cur:
age = row['moodage'].days
return age
def start_processing():
print "start_processing"
#get a complete list of location sesscur 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
"""
sesscur = db.sendtodb(ssql, {'screen_name': user})
#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 a.sess_id = %(sess_id)s
order by b.sess_id, a.time asc
"""
for x in [0, 1]:
print x
for sess in sesscur:
print "fetching next set of locations for session: " + str(
sess['sess_id'])
sess_id = UUID_adapter(sess['sess_id'])
sess_locs = []
loccur = db.sendtodbssc("cursor_" + str(x), lsql, {
'screen_name': user,
'sess_id': sess_id
})
#loccur = db.sendtodb(lsql, {'screen_name':user,'sess_id':sess_id})
for record in loccur:
sess_locs.append(dict(record.items()))
loccur.close()
sesscur.close()
print "watiting before exit"
time.sleep(10)
def do_put_clust(traj_id, clust_id, data):
"""
data: a dictionary of all necessary data
"""
#print "do_put_clust:traj_id: "+str(traj_id)
data['xtraj_id'] = UUID_adapter(traj_id) #avoid byref crap
data['xclust_id'] = UUID_adapter(clust_id) #avoid byref crap
if do_get_clust(clust_id) == None:
#print "location_data:do_put_clust:add clust: "+str(clust_id)
do_add_clust(clust_id, data)
else:
#print "location_data:do_put_clust:update clust: "+str(clust_id)
do_update_clust(clust_id, data)
#print "do_put_clust:process places:started"
#process places
do_remove_all_places_from_clust(clust_id)
#this changed and now only expect to get a single @place vs @, near, area
places = data['clust_places']
for place in places:
do_add_place_2_clust(clust_id, place)
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 do_post_mood(sess_id, me_uuid, mood_type_id, sharing_level_id, body):
"""
can only post moods to self right now
"""
#head and parent are same
head_uuid = UUID_adapter(uuid.uuid4())
#add base message
msg_uuid = _add_message(sess_id, head_uuid, head_uuid, me_uuid, me_uuid,
me_uuid, MSG_TYPE_ID_MOOD, sharing_level_id, body,
False)
stmt = '''insert into mood_message (message_id, mood_type_id)
values (%(message_id)s, %(mood_type_id)s)'''
sendtodb(stmt, {'message_id': msg_uuid, 'mood_type_id': mood_type_id})
def do_get_usr_sharing_status(u_id):
sql = '''
select psl.u_id,
psl.shareable_item_id,
psl.sharing_level_id,
si.descr as shareable_item_descr
from personal_sharing_level as psl
join shareable_item as si
on psl.shareable_item_id = si.id
where psl.u_id=%(u_id)s
'''
u_id = UUID_adapter(u_id)
data = {}
for row in sendtodb(sql % {'u_id':u_id}):
data[row['shareable_item_descr']] = row['sharing_level_id']
return data
def do_get_latest_mood_imgfile(u_id):
sql = '''
select image_filename
from mood_type
where mt_id = (select mood_type_id
from mood_message_view
where
mood_message_view.from_u_id = mood_message_view.to_u_id and
mood_message_view.to_u_id = %(u_id)s
order by created_timestamp desc limit 1)
'''
u_id = UUID_adapter(u_id)
cur = db.sendtodb(sql % {'u_id': u_id})
if cur != None:
for row in cur:
return row['image_filename']
return None
def do_update_usr_sharing_status(u_id, shareable_item_descr, sharing_level_id):
shareable_item_id = \
system_data.do_get_sys_shareable_item_id(shareable_item_descr)
sql = '''
update personal_sharing_level
set sharing_level_id = %(sharing_level_id)s
where u_id = %(u_id)s
and shareable_item_id=%(shareable_item_id)s
'''
u_id = UUID_adapter(u_id)
data = {
'u_id':u_id,
'sharing_level_id':sharing_level_id,
'shareable_item_id':shareable_item_id
}
sendtodb(sql % data)
def do_post_yo_proto(sess_id, from_uuid, to_uuid, note, parent_msg_id=None):
sql = '''
insert into yo_message (
message_id
) values (
%(message_id)s
)
'''
"""
yo messages are by default bi-laterally shared between the sender
and the receiver. we will likely restrict the ability to send a yo
based on the relationship's sharing level, but for right now
everyone can send a yo to anyone in the system by going
to another user's profile page and clicking YO!
the default sharing_level_id = 0 (private) will be interpreted
to mean that the Yo! message is private between the two parties.
"""
head_uuid = UUID_adapter(uuid.uuid4())
#todo:tony:investigate copy-on-write pattern for messages
#one entry for sender
msg_id = _add_message(sess_id, head_uuid, head_uuid, from_uuid, from_uuid,
to_uuid, MSG_TYPE_ID_YO, _DEFAULT_SHARING_LEVEL,
note, False)
sendtodb(sql, {'message_id': msg_id})
if parent_msg_id is not None:
_do_add_yo_mapping(msg_id, parent_msg_id)
#one entry for receiver
msg_id = _add_message(sess_id, head_uuid, head_uuid, to_uuid, from_uuid,
to_uuid, MSG_TYPE_ID_YO, _DEFAULT_SHARING_LEVEL,
note, True)
sendtodb(sql, {'message_id': msg_id})
if parent_msg_id is not None:
_do_add_yo_mapping(msg_id, parent_msg_id)
def do_get_avg_mood_imgfile(u_id, days):
sql = '''
select image_filename
from mood_type
where value = (
select round(avg(mood_type.value)) as avg_mood
from mood_message_view
join mood_type on mood_message_view.mood_type_id = mood_type.mt_id
where
created_timestamp > CURRENT_TIMESTAMP - interval '%(days)s day' and
mood_message_view.from_u_id = mood_message_view.to_u_id and
mood_message_view.to_u_id = %(u_id)s
)
'''
u_id = UUID_adapter(u_id)
cur = db.sendtodb(sql % {'u_id': u_id, 'days': days})
if cur != None:
for row in cur:
return row['image_filename']
return None
def do_get_last_n_session_locations(
sess_id,
time,
fifo=False,
n=None,
):
#print "do_get_last_n_session_locations"
"""
n: return a list of up to n of the latest session locations;
leave blank or set to None to return all
"""
sql = """
with last_n_locations as (
select * from location
where sess_id = %(sess)s
and time < TIMESTAMP %(time)s
order by time desc
"""
if n != None: sql = sql + " limit " + str(n)
sql = sql + ")"
sql = sql + """
select * from last_n_locations order by time
"""
if fifo:
sql = sql + " asc"
else:
sql = sql + " desc"
locations = []
cur = sendtodb(sql, {
'time': time,
'sess': UUID_adapter(sess_id)
}).fetchall()
for row in cur:
#print "do_get_last_n_session_locations:time:"+str(row['time'])
locations.append(dict(row.items()))
return locations
def do_get_visit_history_detail(u_id,
from_date=None,
to_date=None,
min_clust=None):
#print "do_get_cluster_history:"+str(u_id)
sql = """
with locations as (
select
to_char(clust_entry_time, 'MM-DD-YY') as entry_date,
to_char(clust_entry_time, 'Dy') as entry_day,
to_char(clust_entry_time, 'HH12:MIam') as entry_time,
to_char(clust_exit_time, 'HH12:MIam') as exit_time,
to_char(clust_exit_time, 'Dy') as exit_day,
clust_accum_time as total_time,
traj_entry_time,
clust_points,
clust_entry_time,
clust_exit_time,
clust_accum_time,
clust_avg_lat,
clust_avg_lon,
e.place_id,
e.proximity,
case
when e.proximity = 0 then '@'
when e.proximity = 1 then '?'
when e.proximity = 2 then '??'
end as proxchar
from
_user a,
_user_session b,
location_traj c,
location_clust d
left join location_clust_2_place_map e
on d.clust_id = e.clust_id
where
a.u_id = b.u_id
and b.sess_id = c.sess_id
and c.traj_id = d.traj_id
and a.u_id = %(u_id)s
order by
traj_entry_time desc, clust_entry_time desc
)
select
a.*,
case
when (extract(epoch from total_time)/3600) < 1 then
to_char(extract(epoch from total_time/3600*60), '999D9') || 'm'
when (extract(epoch from total_time)/3600) >= 1
and (extract(epoch from total_time)/3600) <= 24 then
to_char(extract(epoch from total_time)/3600, '999D9') || 'h'
else
to_char(extract(epoch from total_time)/3600/24, '999D9') || 'd'
end as total_time,
coalesce(b.descr, 'undefined') || coalesce(proxchar, '') as place
from
locations a left join place b on a.place_id = b.place_id
where
clust_accum_time >= interval '%(mins)s minutes'
"""
date_range = ' '
if from_date is not None and to_date is not None:
date_range = \
" and (clust_entry_time::DATE >= DATE '"+str(from_date) + "'" + \
" and clust_entry_time::DATE <= DATE '"+str(to_date) + "')"
elif from_date is not None and to_date is None:
date_range = \
" and (clust_entry_time::DATE >= DATE '"+str(from_date) + "')"
elif from_date is None and to_date is not None:
date_range = \
" and (clust_entry_time::DATE <= DATE '"+str(to_date) + "')"
sql = sql + date_range + """
order by
traj_entry_time desc, clust_entry_time desc
"""
u_id = UUID_adapter(u_id)
return sendtodb(sql, {
'u_id': u_id,
'date_range': date_range,
'mins': _INTERVAL_MINS
})
def do_get_usr_info(u_id):
sql = ''' select * from _user where u_id=%(u_id)s '''
u_id = UUID_adapter(u_id)
return list(sendtodb(sql % {'u_id':u_id}))[0]
def do_update_usr_relationship(u_id, other_u_id, sharing_level_id):
u_id = UUID_adapter(u_id)
o_u_id = UUID_adapter(other_u_id)
sendtodb(_SQL_UPDATE_RELATIONSHIP %
{'u_id':u_id, 'o_u_id':o_u_id, 'sharing_level_id':sharing_level_id})
def do_get_usr_relationships(u_id, sharing_level_id):
sql = 'select * from relationships_view where owner_u_id=%(u_id)s'
if sharing_level_id > -1 : sql = sql + ' and sharing_level_id=%(slid)s'
u_id = UUID_adapter(u_id)
return sendtodb(sql % {'u_id':u_id, 'slid':sharing_level_id})