def get_balance(user_id):
con = db.get_connection_func()
cur = con.cursor()
cur.execute("SELECT balance from "
"wallet where owner_id = %s", (user_id, ))
row = cur.fetchone()
con.close()
return row[0]
def add_balance(user_id, amount):
con = db.get_connection_func()
cur = con.cursor()
cur.execute("SELECT balance from wallet where owner_id = %s", (user_id, ))
row = cur.fetchone()
cur.execute("update wallet set balance =%s where owner_id = %s",
(float(row[0]) + float(amount), user_id))
con.commit()
con.close()
def add_ssh(user_id, ssh):
con = db.get_connection_func()
cur = con.cursor()
cur.execute(
"insert into ssh(name, content, owner_id)"
" values (%s, %s, %s)", (ssh.name, ssh.content, user_id))
con.commit()
con.close()
def owns(user_id, user_config_id):
con = db.get_connection_func()
cur = con.cursor()
cur.execute(
"select id from user_config "
"where id = %s and owner_id = %s", (user_config_id, user_id))
row = cur.fetchone()
con.close()
return row
def is_admin(user_id):
con = db.get_connection_func()
cur = con.cursor()
cur.execute("select role " "from \"user\" " "where id = %s", (user_id, ))
row = cur.fetchone()
con.close()
return row[0] == 0 # todo check
def get_user_id(nasional_number):
con = db.get_connection_func()
cur = con.cursor()
cur.execute("SELECT id from \"user\" where nasional_number = %s",
(nasional_number, ))
row = cur.fetchone()
con.close()
if row:
return row[0]
else:
return 0
def register_user(nasional_number, fullname, address, password):
salt = uuid.uuid4().hex
hashed_password = hashlib.sha512((password + salt).encode()).hexdigest()
con = db.get_connection_func()
cur = con.cursor()
cur.execute(
"insert into registering_users_view (nasional_number, fullname, address, pass, salt)"
"values (%s,%s,%s,%s,%s)",
(nasional_number, fullname, address, hashed_password, salt))
con.commit()
con.close()
def add_snapshot(user_id, user_config_id, size):
if not (resources_crud.owns(user_id, user_config_id)):
raise Exception('access denied')
con = db.get_connection_func()
cur = con.cursor()
cur.execute(
"insert into \"snapshot\"(create_date, size, user_config_id)"
"values (%s, %s, %s)",
(psycopg2.TimestampFromTicks(time.time()), size, user_config_id))
con.commit()
con.close()
def add_user_resource(user_id, ordered):
con = db.get_connection_func()
cur = con.cursor()
if ordered.ssh_id == 'None':
ordered.ssh_id = None
cur.execute(
"insert into user_config (os, ram, cores, disk, cpu_freq, bound_rate,"
" ssh_id, owner_id, offered_config_id)"
"values (%s, %s, %s, %s, %s, %s,%s, %s, %s)",
(ordered.os, ordered.ram, ordered.cores, ordered.disk,
ordered.cpu_freq, ordered.bound_rate, ordered.ssh_id, user_id,
ordered.offered_config_id))
con.commit()
con.close()
def update_offered_resource(user_id, offered_id, new_offered):
if not (user_crud.is_admin(user_id)):
raise Exception('access denied')
con = db.get_connection_func()
cur = con.cursor()
cur.execute(
"update offered_config set os = %s , ram = %s , cores = %s , "
" disk = %s , cpu_freq = %s , bound_rate = %s "
"where id = %s ",
(new_offered.os, new_offered.ram, new_offered.cores, new_offered.disk,
new_offered.cpu_freq, new_offered.bound_rate, offered_id))
con.commit()
con.close()
def add_offered_resource(user_id, offered_config):
if not (user_crud.is_admin(user_id)):
raise Exception('access denied')
con = db.get_connection_func()
cur = con.cursor()
cur.execute(
"insert into offered_config(os, ram, cores, disk, cpu_freq, bound_rate)"
"values (%s, %s, %s, %s, %s, %s)",
(offered_config.os, offered_config.ram, offered_config.cores,
offered_config.disk, offered_config.cpu_freq,
offered_config.bound_rate))
con.commit()
con.close()
def get_user_resource_info(user_id, ordered_id):
con = db.get_connection_func()
cur = con.cursor()
cur.execute(
"select id, os, ram, cores, disk, cpu_freq, bound_rate,"
" ssh_id, owner_id, daily_cost, offered_config_id"
" from user_config "
" where owner_id = %s and id = %s ", (user_id, ordered_id))
row = cur.fetchone()
p1 = Ordered(row[0], row[1], row[2], row[3], row[4], row[5], row[6],
row[7], row[8], row[9], row[10])
con.close()
return p1
def get_offered_resources():
con = db.get_connection_func()
cur = con.cursor()
cur.execute("select id, os, ram, cores, disk, cpu_freq, bound_rate"
" from offered_config ")
rows = cur.fetchall()
offers = []
for row in rows:
p1 = Offered(row[0], row[1], row[2], row[3], row[4], row[5], row[6])
offers.append(p1)
con.close()
return offers
def get_snapshots(user_config_id):
con = db.get_connection_func()
cur = con.cursor()
cur.execute(
"select id, create_date, size, user_config_id from \"snapshot\" "
"where user_config_id = %s ", (user_config_id, ))
rows = cur.fetchall()
snapshots = []
for row in rows:
p1 = Snapshot(row[0], row[1], row[2], row[3])
snapshots.append(p1)
con.close()
return snapshots
def update_user_resource(user_id, ordered_id, new_ordered):
con = db.get_connection_func()
cur = con.cursor()
if new_ordered.ssh_id == 'None':
new_ordered.ssh_id = None
cur.execute(
"update user_config set os = %s , ram = %s , cores = %s , "
" disk = %s , cpu_freq = %s , bound_rate = %s ,"
" ssh_id = %s , offered_config_id = %s"
"where id = %s and owner_id = %s",
(new_ordered.os, new_ordered.ram, new_ordered.cores, new_ordered.disk,
new_ordered.cpu_freq, new_ordered.bound_rate, new_ordered.ssh_id,
new_ordered.offered_config_id, ordered_id, user_id))
con.commit()
con.close()
def login_user(nasional_number, password):
con = db.get_connection_func()
cur = con.cursor()
cur.execute(
"SELECT pass, salt from registering_users_view where nasional_number = %s",
(nasional_number, ))
row = cur.fetchone()
con.close()
if row:
hashed = row[0]
salt = row[1]
password = hashlib.sha512((password + salt).encode()).hexdigest()
if hashed == password:
return True
return False
else:
return False
def get_all_users(size, page):
con = db.get_connection_func()
cur = con.cursor()
cur.execute(
"select id, nasional_number, fullname, address, register_date "
"from \"user\" "
"order by register_date desc "
"limit %s offset %s", (size, size * (page - 1)))
rows = cur.fetchall()
users = []
for row in rows:
p1 = User(row[0], row[1], row[2], row[3], row[4])
users.append(p1)
con.close()
return users
def get_all_ssh(ui, user_id):
con = db.get_connection_func()
cur = con.cursor()
cur.execute("select id, name, content from ssh"
" where owner_id = %s", (user_id, ))
rows = cur.fetchall()
sshs = []
for row in rows:
p1 = SSH(row[0], row[1], row[2])
sshs.append(p1)
con.close()
# ui.textEdit_9.insertPlainText(" id name content")
ui.textEdit_9.setText("")
ui.textEdit_9.insertPlainText("id name")
for ssh in sshs:
ui.textEdit_9.insertPlainText("%s %s" % (ssh.ssh_id, ssh.name))
return sshs
def get_user_resources(user_id):
con = db.get_connection_func()
cur = con.cursor()
cur.execute(
"select id, os, ram, cores, disk, cpu_freq, bound_rate,"
" ssh_id, owner_id, daily_cost, offered_config_id"
" from user_config "
" where owner_id = %s ", (user_id, ))
rows = cur.fetchall()
ordered = []
for row in rows:
p1 = Ordered(row[0], row[1], row[2], row[3], row[4], row[5], row[6],
row[7], row[8], row[9], row[10])
ordered.append(p1)
con.close()
return ordered
