def process_user_status_list(self, lst_ol, lst_out):
empty_temp_user_state = 'delete from social.temp_users_last_state;'
insert_user_logon = 'insert into social.users_login(social_net_id, user_id, login_date, online_status)' \
' select t_s.social_net_id, t_s.user_id, now(), t_s.online_status' \
' from social.temp_users_last_state t_s' \
' inner join social.users_last_state s on s.social_net_id = t_s.social_net_id' \
' and s.user_id = t_s.user_id' \
' and s.online_status <> t_s.online_status'
update_user_state = 'update social.users_last_state t' \
' set online_status = t_s.online_status, state_date = now() '\
' from social.temp_users_last_state t_s ' \
' where t.social_net_id = t_s.social_net_id '\
' and t.user_id = t_s.user_id' \
' and exists (select 1 ' \
' from social.users_last_state s ' \
' where s.social_net_id = t_s.social_net_id '\
' and s.user_id = t_s.user_id ' \
' and s.online_status <> t_s.online_status); ' \
insert_user_state = 'insert into social.users_last_state(social_net_id, user_id, online_status ,state_date) ' \
' select t_s.social_net_id, t_s.user_id, t_s.online_status, now()' \
' from social.temp_users_last_state t_s' \
' where not exists(select 1 ' \
' from social.users_last_state s ' \
' where s.social_net_id = t_s.social_net_id ' \
' and s.user_id = t_s.user_id) ' \
# Очистка временной таблицы
self.db.exec_query(empty_temp_user_state)
f = IteratorFile(
(u'{}\t{}\t{}'.format(self.api.social_id, x, 1) for x in lst_ol))
self.db.copy_from_file(f, 'social.temp_users_last_state',
('social_net_id', 'user_id', 'online_status'))
f = IteratorFile(
(u'{}\t{}\t{}'.format(self.api.social_id, x, -1) for x in lst_out))
self.db.copy_from_file(f, 'social.temp_users_last_state',
('social_net_id', 'user_id', 'online_status'))
self.db.exec_query(insert_user_logon)
self.db.exec_query(update_user_state)
self.db.exec_query(insert_user_state)
self.db.exec_query('commit')
return 0
def add_albums(cursor: RealDictCursor, albums_data):
f = IteratorFile(
("{}\t{}\t{}\t{}".format(*album) for album in albums_data))
cursor.copy_from(f,
'album',
columns=(['name', 'date', 'studio_id', 'band_id']))
def add_bands(cursor: RealDictCursor, bands_amount: int, bands):
f = IteratorFile(("{}\t{}".format(bands[randint(1,
len(bands) - 1)],
get_random_date())
for _ in range(bands_amount)))
cursor.copy_from(f, 'band', columns=(['name', 'band_establishment']))
def add_users(cursor: RealDictCursor, users_data):
f = IteratorFile(("{}\t{}\t{}\t{}".format(*user) for user in users_data))
cursor.copy_from(
f,
'user_account',
columns=(['first_name', 'last_name', 'email', 'address_id']))
def add_playlists(cursor: RealDictCursor, playlists_amount: int, playlists):
f = IteratorFile(
("{}\t{}".format(playlists[randint(1,
len(playlists) - 1)],
get_random_date()) for _ in range(playlists_amount)))
cursor.copy_from(f, 'playlist', columns=(['name', 'date']))
def add_subscriptions(cursor: RealDictCursor, subscriptions):
f = IteratorFile(
("{}\t{}\t{}".format(*subscription) for subscription in subscriptions))
cursor.copy_from(f,
'subscription',
columns=(['date', 'user_id', 'band_id']))
def add_artists(cursor: RealDictCursor, artists_data):
f = IteratorFile(
("{}\t{}\t{}\t{}".format(*artist) for artist in artists_data))
cursor.copy_from(
f,
'artist',
columns=(['first_name', 'last_name', 'gender', 'instrument']))
def add_songs(cursor: RealDictCursor, songs_data):
f = IteratorFile(
("{}\t{}\t{}\t{}\t{}".format(*song) for song in songs_data))
cursor.copy_from(
f,
'song',
columns=(['name', 'length', 'views', 'album_id', 'genre_id']))
def add_addresses(cursor: RealDictCursor, addresses_amount: int,
countries_amount: int, cities_amount: int,
street_amount: int):
max_local_number = 1000
f = IteratorFile(("{}\t{}\t{}\t{}".format(randint(1, street_amount),
randint(1, max_local_number),
randint(1, cities_amount),
randint(1, countries_amount))
for _ in range(addresses_amount)))
cursor.copy_from(
f,
'address',
columns=(['street_id', 'local_number', 'city_id', 'country_id']))
def add_studios(cursor: RealDictCursor, studios_data):
f = IteratorFile(("{}\t{}".format(*studio) for studio in studios_data))
cursor.copy_from(f, 'studio', columns=(['name', 'address_id']))
def add_genres(cursor: RealDictCursor, genres):
f = IteratorFile(("{}".format(x) for x in genres))
cursor.copy_from(f, 'genre', columns=(['type']))
def add_countries(cursor: RealDictCursor, countries):
f = IteratorFile(("{}".format(x) for x in countries))
cursor.copy_from(f, 'country', columns=(['name']))
def add_members_to_bands(cursor: RealDictCursor, bands_members):
f = IteratorFile(
("{}\t{}".format(*band_member) for band_member in bands_members))
cursor.copy_from(f, 'artist_band', columns=(['artist_id', 'band_id']))
def add_songs_to_playlists(cursor: RealDictCursor, songs_playlists):
f = IteratorFile(
("{}\t{}".format(*user_playlist) for user_playlist in songs_playlists))
cursor.copy_from(f, 'song_playlist', columns=(['song_id', 'playlist_id']))
def add_user_playlists(cursor: RealDictCursor, user_playlists):
f = IteratorFile(
("{}\t{}".format(*user_playlist) for user_playlist in user_playlists))
cursor.copy_from(f, 'user_playlist', columns=(['user_id', 'playlist_id']))
def collect_online_users(self):
# Получаем список пользователей
user_sql = 'select ua.user_id, ua.token, ua.session_secret, sa.action_type' \
' from schedule.scheduled_action sa' \
' inner join social.users_auth ua on ua.social_net_id = sa.social_net_id' \
' and ua.user_id = sa.user_id' \
' where sa.social_net_id = 3' \
' order by ua.user_id'
insert_sql = "insert into social_stat.friends_online_stat " \
"values('{0}', '{1}', to_timestamp('{2}', 'DD Mon YYYY HH24:MI:SS'), '{3}', '{4}')"
crt_temp_user_list = 'create temporary table handled_users( ' \
'social_net_id integer, ' \
'user_id bigint ' \
') ' \
'on commit delete rows;' \
'create index temp_1 on temp_user_list_item(social_net_id, user_id);'
empty_temp_user_list = 'delete from social.temp_user_list_item;'
insert_user_list = 'insert into social.user_list_item(user_list_id, social_net_id, user_id, add_date)' \
'select user_list_id, social_net_id, user_id, now()' \
' from social.temp_user_list_item'
user_list = 'insert into social.user_list_item(user_lists_id, social_net_id, user_id, add_date)' \
'values (%(user_lists_id)s, %(social_net_id)s, %(user_id)s, %(add_date)s)'
clear_user_list = 'delete from social.temp_user_list_item t ' \
' where EXISTS (select 1 ' \
' from social.user_list_item l ' \
' where l.social_net_id = t.social_net_id' \
' and l.user_id = t.user_id)'
insert_new_friends1 = 'insert into social.user_relations(social_net_id, user_id, rel_user_id, relation_date, ' \
' relation_type_id) ' \
'select t.social_net_id, %(user_id)s, t.user_id, %(relation_date)s, %(relation_type_id)s ' \
' from social.temp_user_list_item t ' \
' where not exists (select 1 ' \
' from social.user_relations r ' \
' where r.social_net_id = t.social_net_id ' \
' and r.rel_user_id = t.user_id ' \
' and r.user_id = %(user_id)s ' \
' and r.relation_type_id = 1) '
insert_new_friends = 'insert into social.user_relations(social_net_id, user_id, rel_user_id, relation_date, ' \
' relation_type_id) ' \
'select t.social_net_id, %(user_id)s, t.user_id, %(relation_date)s, %(relation_type_id)s ' \
' from social.temp_user_list_item t ' \
' left join social.user_relations r on r.social_net_id = t.social_net_id ' \
' and r.rel_user_id = t.user_id ' \
' and r.user_id = %(user_id)s ' \
' and r.relation_date = ' \
' (select max(r_m.relation_date) ' \
' from social.user_relations r_m ' \
' where r_m.social_net_id = r.social_net_id ' \
' and r_m.user_id = r.user_id ' \
' and r_m.rel_user_id = r.rel_user_id ' \
' and r_m.relation_type_id in (1, 5, 6)) ' \
' where coalesce(r.relation_type_id, -1) in (-1, 6) '
insert_out_friends = 'insert into social.user_relations(social_net_id, user_id, rel_user_id, relation_date, ' \
' relation_type_id) ' \
'select r.social_net_id, r.user_id, r.rel_user_id, %(relation_date)s, %(relation_type_id)s ' \
' from social.user_relations r ' \
' where r.user_id = %(user_id)s ' \
' and r.relation_date = ' \
' (select max(r_m.relation_date) ' \
' from social.user_relations r_m ' \
' where r_m.social_net_id = r.social_net_id ' \
' and r_m.user_id = r.user_id ' \
' and r_m.rel_user_id = r.rel_user_id ' \
' and r_m.relation_type_id in (1, 5, 6)) ' \
' and r.relation_type_id in (1, 5) ' \
' and not exists (select 1 ' \
' from social.temp_user_list_item t ' \
' where t.social_net_id = r.social_net_id ' \
' and t.user_id = r.rel_user_id )'
for act in self.db.do_query_all(user_sql):
if act[3] == 'friends.online':
# запрос количества друзей
self.api.token = act[1]
self.api.session_secret_key = act[2]
online_header, online_frnd = self.api.getOnlineFriends()
total_header, total_frnd = self.api.getFriends()
date_str = online_header['date'].split(', ')[1]
req_date = datetime.strptime(date_str[:-4],
'%d %b %Y %H:%M:%S')
self.db.exec_query(
insert_sql.format(3, act[0], date_str, len(online_frnd),
len(total_frnd)))
self.db.exec_query('commit')
# Добавление пользователей в список наблюдения
# db.exec_query(crt_temp_user_list)
f = IteratorFile((u'{}\t{}\t{}'.format(LIST_USER_LOGON,
self.api.social_id, x)
for x in total_frnd))
self.db.copy_from_file(
f, 'social.temp_user_list_item',
('user_list_id', 'social_net_id', 'user_id'))
self.db.exec_query(clear_user_list)
self.db.exec_query(insert_user_list)
self.db.exec_query(empty_temp_user_list)
self.db.exec_query('commit')
# Получаем список обрабатываемых пользователей
get_user_list = 'select t.social_net_id, t.user_id ' \
' from social.user_list_item t' \
' where t.user_list_id = %(list)s'
ul = self.db.do_query_all_params(get_user_list,
{'list': LIST_USER_LOGON})
# Добавление информации о друзьях
self.process_user_status_list(
online_frnd,
[x for x in total_frnd if x not in online_frnd])
# Удаление обработанных пользователей
ul = [x for x in ul if x not in total_frnd]
# Добавление друзей в список отношений
self.db.exec_query(empty_temp_user_list)
f = IteratorFile((u'{}\t{}\t{}'.format(LIST_USER_LOGON,
self.api.social_id, x)
for x in total_frnd))
self.db.copy_from_file(
f, 'social.temp_user_list_item',
('user_list_id', 'social_net_id', 'user_id'))
self.db.exec_query_params(
insert_new_friends, {
'user_id': act[0],
'relation_date': req_date,
'relation_type_id': 1
})
self.db.exec_query_params(
insert_out_friends, {
'user_id': act[0],
'relation_date': req_date,
'relation_type_id': 6
})
self.db.exec_query('commit')
def add_streets(cursor: RealDictCursor, streets):
f = IteratorFile(("{}".format(x) for x in streets))
cursor.copy_from(f, 'street', columns=(['name']))