def __init__(self, num_querys):
self.num_querys = num_querys
threading.Thread.__init__(self)
self.conn, self.cur = open_connection()
global before
if not before:
self.cur.execute(
"prepare query1 (varchar) as select email,name,ls.song as like_song_id,la.album as "
"like_album_id,lg.\"group\" as like_group_name,lp.playlist as like_playlit_id from \"user\" join "
"like_song ls on \"user\".email = $1 join like_album la on \"user\".email = $2 join like_group lg on "
"\"user\".email = $3 join like_playlist lp on \"user\".email = $4;"
)
self.cur.execute(
"prepare query2 as select * from playlists_table left join playlist p on playlists_table.playlist = "
"p.id join song s on playlists_table.song = $2 where playlists_table.playlist = $1;"
)
self.cur.execute(
"prepare query3 (int) as select public.album.id, name, year, prize from album right join "
"prizes_albums_table pat on album.id = $1 where public.album.type = 'concert';"
)
self.cur.execute(
"prepare query4 (int) as select id, name, year, a.creator, gt.creator from song right join "
"artists_table a on song.id = $1 right join groups_table gt on song.id = $1 where song.year between "
"1970 and 2000;")
self.cur.execute(
"prepare query5 (varchar) as select name, count(*) as num_of_songs from artist right join "
"artists_table a on artist.name = $1 group by name order by num_of_songs desc;"
)
self.cur.execute(
"prepare query6 (varchar) as select email, name, count(*) as num_of_like_songs from \"user\" right "
"join like_song ls on \"user\".email = $1 group by email, name having count(*) > 1 order by "
"num_of_like_songs desc;")
def add_likes():
cursor, conn = open_connection()
cursor.execute('SELECT count(*) FROM "user";')
length_user = cursor.fetchone()[0]
cursor.execute('SELECT id FROM song;')
id_song = cursor.fetchall()
cursor.execute('SELECT id FROM album;')
id_album = cursor.fetchall()
cursor.execute('SELECT email FROM "user";')
id_user = cursor.fetchall()
cursor.execute('SELECT name FROM "group";')
id_group = cursor.fetchall()
cursor.execute('SELECT id FROM playlist;')
id_playlist = cursor.fetchall()
for i in range(0, int(length_user / 4)):
cursor.execute(
'insert into like_song ("user",song) VALUES (%s, %s);commit;',
(random.choice(id_user), random.choice(id_song)))
cursor.execute(
'insert into like_album ("user",album) VALUES (%s, %s);commit;',
(random.choice(id_user), random.choice(id_album)))
cursor.execute(
'insert into like_group ("user","group") VALUES (%s, %s);commit;',
(random.choice(id_user), random.choice(id_group)))
cursor.execute(
'insert into like_playlist ("user",playlist) VALUES (%s, %s);commit;',
(random.choice(id_user), random.choice(id_playlist)))
close_connection(cursor, conn)
def add_users(n):
cursor, conn = open_connection()
gen_users = UserGenerator()
gen_users.get_primary_key_data(n)
for u in gen_users.data:
params = gen_users.get_params()
print(u, params, sep=': ')
cursor.execute(
'insert into "user" (email, name, icon_path, password) values(%s, %s, %s, %s);commit;',
(u, params.get('name'), params.get('icon_path'),
params.get('password')))
print(n, "Records added", sep=' ')
close_connection(cursor, conn)
def add_groups(n):
cursor, conn = open_connection()
gen_groups = GroupGenerator()
gen_groups.get_primary_key_data(n)
for g in gen_groups.data:
params = gen_groups.get_params()
print(g, params, sep=': ')
cursor.execute(
'insert into "group"(name, "desc", country, icon_path) values(%s, %s, %s, %s);commit;',
(g, params.get('desc'), params.get('country'),
params.get('icon_path')))
print(n, "Records added", sep=' ')
close_connection(cursor, conn)
def add_artists(n):
cursor, conn = open_connection()
gen_artists = ArtistGenerator()
gen_artists.get_primary_key_data(n)
for a in gen_artists.data:
params = gen_artists.get_params()
print(a, params, sep=': ')
cursor.execute(
'insert into artist (name, "desc", country, icon_path) values (%s, %s, %s, %s );commit;',
(a, params.get('desc'), params.get('country'),
params.get('icon_path')))
print(n, "Artists added", sep=' ')
close_connection(cursor, conn)
def add_songs(n):
cursor, conn = open_connection()
song_generator = SongGenerator()
data = song_generator.get_primary_key_data(n)
for s in data:
params = song_generator.get_params()
print('id: ', s, params, sep=': ')
cursor.execute(
'insert into song (id, name, path, album, year) values (%s, %s, %s, %s, %s);commit;',
(s, params.get('name'), params.get('path'), params.get('album'),
params.get('year')))
print(n, "Songs added", sep=' ')
close_connection(cursor, conn)
def add_prizers(n):
cursor, conn = open_connection()
gen_prizers = PrizeGenerator()
data = gen_prizers.get_primary_key_data(n)
for id in data:
params = gen_prizers.get_params()
print('id: ', id, params, sep=': ')
cursor.execute(
'insert into prize (id, name, year, description) values (%s, %s, %s, %s);commit;',
(id, params.get('name'), params.get('year'),
params.get('description')))
print(n, "Prizes added", sep=' ')
close_connection(cursor, conn)
def add_playlists_table():
cursor, conn = open_connection()
cursor.execute('SELECT id FROM playlist;')
id_playlist = cursor.fetchall()
cursor.execute('SELECT id FROM song;')
id_song = cursor.fetchall()
cursor.execute('SELECT count(*) FROM song;')
length_song = cursor.fetchone()[0]
for i in range(0, int(length_song / 4)):
cursor.execute(
'insert into playlists_table (playlist, song) VALUES (%s, %s);',
(random.choice(id_playlist), random.choice(id_song)))
close_connection(cursor, conn)
def add_playlists(n):
cursor, conn = open_connection()
gen_playlist = PlaylistGenerator()
data = gen_playlist.get_primary_key_data(n)
for d in data:
params = gen_playlist.get_params()
print('id: ', d, params, sep=': ')
cursor.execute(
'insert into playlist (id, name, quantity, author, duration, icon_path) VALUES (%s, %s, %s, %s, %s, '
'%s);commit;', (d, params.get('name'), params.get('quantity'),
params.get('author'), params.get('duration'),
params.get('icon_path')))
print(n, "Playlists added", sep=' ')
close_connection(cursor, conn)
def add_albums(n):
cursor, conn = open_connection()
gen_albums = AlbumGenerator()
data = gen_albums.get_primary_key_data(n)
for d in data:
params = gen_albums.get_params()
print('id: ', d, params, sep=': ')
cursor.execute(
'insert into album (id, name, year, duration, quantity, icon_path, type) values (%s, %s, %s, %s, %s, %s, %s);commit;',
(d, params.get('name'), params.get('year'), params.get('duration'),
params.get('quantity'), params.get('icon_path'),
params.get('type')))
print(n, "Albums added", sep=' ')
close_connection(cursor, conn)
def add_groups_table():
cursor, conn = open_connection()
cursor.execute('SELECT count(*) FROM song;')
length_song = cursor.fetchone()[0]
cursor.execute('SELECT id FROM song;')
id_song = cursor.fetchall()
cursor.execute('SELECT name FROM "group";')
id_group = cursor.fetchall()
for i in range(0, int(length_song / 4)):
cursor.execute(
'insert into groups_table (creator, song) VALUES (%s, %s);commit;',
(random.choice(id_group), random.choice(id_song)))
close_connection(cursor, conn)
def add_history_artist(n):
cursor, conn = open_connection()
cursor.execute('SELECT count(*) FROM artist;')
artist_length = cursor.fetchone()[0]
cursor.execute('SELECT name FROM artist;')
id_artist = cursor.fetchall()
cursor.execute('SELECT name FROM "group";')
id_group = cursor.fetchall()
for i in range(0, int(artist_length / 4)):
cursor.execute(
'insert into history_artist_table (artist, "group", start_date, end_date) values (%s, %s, %s, %s);commit;',
(random.choice(id_artist), random.choice(id_group),
random.randint(1500, 2020), random.randint(1500, 2020)))
print(n, "History added", sep=' ')
close_connection(cursor, conn)
class SongGenerator(BaseGenerator):
cursor, conn = open_connection()
cursor.execute('SELECT id FROM album;')
id_albums = cursor.fetchall()
close_connection(cursor, conn)
def get_primary_key_data(self, num):
return range(0, num - 1)
def get_params(self):
song = {
'name': self.person.first_name(),
'year': self.generic.datetime.year(),
'path': self.generic.path.project_dir(),
}
if bool(random.getrandbits(1)):
if bool(self.id_albums) is True:
song.update({'album': random.choice(self.id_albums)})
else:
song.update({'album': None})
print(song)
return song
import random
import threading
import time
import matplotlib.pyplot as plt
from ConstantsQeuries import query_1, query_2, query_3, query_4, query_5, query_6, query_list
from Utils import open_connection, drop_index
connection, cursor = open_connection()
curr_res_constant_threads = [[]]
curr_res_dynamic_threads = []
cursor.execute("select \"user\" from like_song;")
like_song_email = cursor.fetchall()
cursor.execute("select \"user\" from like_album;")
like_album_email = cursor.fetchall()
cursor.execute("select \"user\" from like_group;")
like_group_email = cursor.fetchall()
cursor.execute("select \"user\" from like_playlist;")
like_playlist_email = cursor.fetchall()
cursor.execute("select song from playlists_table;")
playlist_table_songs = cursor.fetchall()
cursor.execute("select album from prizes_albums_table;")
prizes_albums = cursor.fetchall()
cursor.execute("select id from song;")
songs_ids = cursor.fetchall()
cursor.execute("select name from \"group\";")
group_names = cursor.fetchall()
cursor.execute("select song from artists_table;")
art_songs = cursor.fetchall()