def exists(cid):
"""verify that a client with given id is available (CALLED AT THE SERVER SIDE)
@param cid: client id
@return boolean wither the client exists or note
"""
stat=sql.SQL("SELECT EXISTS (SELECT 1 FROM clients WHERE id={cid});").\
format(cid=sql.Literal(cid))
cur.execute(stat)
return cur.fetchone()[0]
def insert_client(name):
""" add new client to the network (CALLED AT THE SERVER SIDE),
note that some clients might not have banking id yet
@param name: client name
"""
stat=sql.SQL("INSERT INTO clients (contact_name) VALUES ({name})").\
format(name=sql.Literal(name))
cur.execute(stat)
cur.execute("SELECT currval(pg_get_serial_sequence('clients', 'id'));")
return cur.fetchone()[0]
def get_balance_by_credid(cred_id):
""" get balance of client with given credential id
@param cred_id: client credential id
"""
query = sql.SQL(
"SELECT (b.balance) FROM banking as b JOIN WITH credentials AS c WHERE c.cred_id={credid} AND c.id==b.client_id;"
).format(credid=sql.Literal(cred_id))
db_log.debug(query)
cur.execute(query)
return cur.fetchone()[0]
def exists(cid):
""" verify the credential for the client with given cid(CALLED FROM SERVER SIDE),
or get the single row for client with cid=1 (CALLED FROM CLIENT SIDE)
@param cid: client id, or 1 (in case of call from client side for it's own credential)
@return boolean for wither the client (with given cid) is registered or not
"""
stat=sql.SQL("SELECT EXISTS (SELECT 1 FROM credentials WHERE id={cid})").\
format(cid=sql.Literal(cid))
cur.execute(stat)
return cur.fetchone()[0]
def get_credid_with_gid(gid):
"""cross reference credential id, with good's id
@param gid: good's id
@return credential id credid
"""
query=sql.SQL("SELECT (C.cred_id) FROM credentials as c JOIN WITH goods AS g JOIN WITH owners as o WHERE g.id=={gid} AND o.owner_id==c.id LIMIT 1;").\
format(gid=sql.Literal(gid))
db_log.debug(query)
cur.execute(query)
return cur.fetchone()[0]
def get_password(cred_id):
""" get user's passcode for authentication
@param cred_id: credential id
@return list of the id, or empty list of doesn't exist
"""
query=sql.SQL("SELECT (passcode) FROM credentials WHERE cred_id={credid} LIMIT 1;").\
format(credid=sql.Literal(cred_id))
db_log.debug(query)
cur.execute(query)
return cur.fetchone()[0]
def get_id(cred_id):
""" get client id
@param cred_id: credential id
@return the id, or None if doesn't exist
"""
query=sql.SQL("SELECT id FROM credentials WHERE cred_id={credid} LIMIT 1;").\
format(credid=sql.Literal(cred_id))
db_log.debug(query)
cur.execute(query)
return cur.fetchone()[0]
def update_account(cid, balance):
"""update the banking account with the calculated new balance (CALLED FROM SERVER SIDE)
@param cid: client id
@param balance: the account balance
"""
stat = sql.SQL("UPDATE banking SET (balance, balance_dt) = ({balance}, {dt}) WHERE client_id={cid}").\
format(balance=sql.Literal(balance), \
dt=dt.datetime().strftime(TIMESTAMP_FORMAT), \
cid=sql.Literal(cid))
cur.execute(stat)
def get(cid):
"""retrieve client into with given client id (cid)
@param cid: client id
@return tuple (id, name, join date)
"""
query = sql.SQL(
"SELECT (id, contact_name, client_join_dt) FROM clients WHERE id={cid};"
).format(cid=sql.Literal(cid))
db_log.debug(query)
cur.execute(query)
return cur.fetchone()
def insert_banking(cid, balance):
""" give the client with the given id (cid) banking account (CALLED AT SERVER SIDE)
@param cid: client id
@param balance: client account balance
"""
stat=sql.SQL("INSERT INTO banking (client_id, balance, balance_dt) VALUES ({cid}, {balance}, {dt});"). \
format(cid=sql.Literal(cid), \
balance=sql.Literal(balance), \
dt=sql.Literal(dt.datetime.now().strftime(TIMESTAMP_FORMAT)))
db_log.debug(stat)
cur.execute(stat)
stat="SELECT currval(pg_get_serial_sequence('banking', 'id'));"
db_log.debug(stat)
cur.execute(stat);
return cur.fetchone()[0]
def register(cid):
"""register new client credentials with given cid (CALLED FROM SERVER SIDE)
@param cid: client id
@return a tuple (cred_id, passcode)
"""
cred_id = rand.random() * MAX_CRED_ID
passcode=''.join(rand.choice(string.ascii_uppercase+\
string.ascii_lowercase+string.digits)\
for _ in range(9))
stat=sql.SQL("INSERT INTO credentials (id, passcode, cred_id) VALUES ({cid}, {passcode}, {credid});").\
format(cid=sql.Literal(cid), \
passcode=sql.Literal(passcode), \
credid=sql.Literal(cred_id))
db_log.debug(stat)
cur.execute(stat)
return (cred_id, passcode)
def add_good(gname, gquality, gcost, gcid=1):
""" INSERT new good into the goods table
@param gname: good name
@param gquality: good quality
@param gcost: good cost
@param gcid: good currency id
"""
stat=sql.SQL("INSERT INTO goods (good_name, good_quality, good_cost, good_currency_id) VALUES ({gname}, {gquality}, {gcost}, {gcid});").\
format(gname=sql.Literal(gname), \
gquality=sql.Literal(gquality), \
gcost=sql.Literal(gcost), \
gcid=sql.Literal(gcid))
db_log.debug(stat)
cur.execute(stat)
stat="SELECT currval(pg_get_serial_sequence('goods', 'id'));"
cur.execute(stat)
db_log.debug(stat)
return cur.fetchone()[0]