def get_participant_name():
"""Find or create the Participant representing Bountysource
TODO better way to accomplish this?
:returns:
username of the Bountysource participant
"""
row = db.fetchone("""
SELECT username
FROM participants
WHERE username=%s
""", (participant_name,))
if row:
return row['username']
else:
# create the participant
row = db.fetchone("""
INSERT INTO participants
(username, username_lower)
VALUES (%s, %s)
RETURNING username
""", (participant_name, participant_name))
if row:
return row['username']
def load_session(where, val):
from gittip import db
SQL = """\
SELECT p.id
, p.statement
, p.payment_method_token
, p.last_bill_result
, p.session_token
, p.session_expires
, p.ctime
, p.claimed_time
, p.is_admin
, p.redirect
, p.balance
, n.network
, n.user_info
FROM participants p
JOIN social_network_users n
ON n.participant_id = p.id
""" + where
rec = db.fetchone(SQL, (val,))
out = {}
if rec is not None:
out = rec
return out
def load_session(SESSION, val):
from gittip import db
rec = db.fetchone(SESSION, (val,))
out = {}
if rec is not None:
out = rec
return out
def change_participant_id(website, old, suggested):
"""Raise response return None.
We want to be pretty loose with usernames. Unicode is allowed. So are
spaces. Control characters aren't. We also limit to 32 characters in
length.
"""
for i, c in enumerate(suggested):
if i == 32:
raise Response(413) # Request Entity Too Large (more or less)
elif ord(c) < 128 and c not in ALLOWED_ASCII:
raise Response(400) # Yeah, no.
elif c not in ALLOWED_ASCII:
raise Response(400) # XXX Burned by an Aspen bug. :`-(
# https://github.com/whit537/aspen/issues/102
if website is not None and suggested in os.listdir(website.www_root):
raise Response(400)
if suggested != old:
rec = db.fetchone( "UPDATE participants SET id=%s WHERE id=%s " \
"RETURNING id", (suggested, old))
# May raise IntegrityError
assert rec is not None # sanity check
assert suggested == rec['id'] # sanity check
def check_membership(self, user):
return dear_god_why.fetchone(
"""
SELECT * FROM current_communities WHERE slug=%s AND participant=%s
""", (self.slug, user.username)) is not None
def get_participant_via_access_token(access_token):
"""From a Gittip access token, attempt to find an external account
:param user:
currently logged in user.
:param access_token:
access token generated by Gittip on account link redirect
:returns:
the participant, if found
"""
if access_token_valid(access_token):
# from the user id in access token, query participant database for a username,
# since that is the primary key user to find a Participant model.
# There is probably a better way to do this, like querying with the id itself.
parts = access_token.split('.')
participant_id = parts[0];
row = db.fetchone("""
SELECT username
FROM participants
WHERE id = %s
""", (participant_id))
if row:
username = row['username']
participant = Participant.query.get(username)
return participant
def load_session(where, val):
from gittip import db
SQL = """\
SELECT p.id
, p.statement
, p.stripe_customer_id
, p.balanced_account_uri
, p.last_bill_result
, p.last_ach_result
, p.session_token
, p.session_expires
, p.ctime
, p.claimed_time
, p.is_admin
, p.redirect
, p.balance
, p.goal
, n.network
, n.user_info
FROM participants p
JOIN social_network_users n
ON n.participant_id = p.id
""" + where
rec = db.fetchone(SQL, (val,))
out = {}
if rec is not None:
out = rec
return out
def change_participant_id(website, old, suggested):
"""Raise response return None.
We want to be pretty loose with usernames. Unicode is allowed. So are
spaces. Control characters aren't. We also limit to 32 characters in
length.
"""
for i, c in enumerate(suggested):
if i == 32:
raise Response(413) # Request Entity Too Large (more or less)
elif ord(c) < 128 and c not in ALLOWED_ASCII:
raise Response(400) # Yeah, no.
elif c not in ALLOWED_ASCII:
raise Response(400) # XXX Burned by an Aspen bug. :`-(
# https://github.com/whit537/aspen/issues/102
if website is not None and suggested in os.listdir(website.www_root):
raise Response(400)
if suggested != old:
rec = db.fetchone( "UPDATE participants SET id=%s WHERE id=%s " \
"RETURNING id", (suggested, old))
# May raise IntegrityError
assert rec is not None # sanity check
assert suggested == rec['id'] # sanity check
def get_participant_via_access_token(access_token):
"""From a Gittip access token, attempt to find an external account
:param user:
currently logged in user.
:param access_token:
access token generated by Gittip on account link redirect
:returns:
the participant, if found
"""
if access_token_valid(access_token):
# from the user id in access token, query participant database for a username,
# since that is the primary key user to find a Participant model.
# There is probably a better way to do this, like querying with the id itself.
parts = access_token.split('.')
participant_id = parts[0];
row = db.fetchone("""
SELECT username
FROM participants
WHERE id = %s
""", (participant_id))
if row:
username = row['username']
participant = Participant.query.get(username)
return participant
def load_session(where, val):
from gittip import db
SQL = "SELECT * FROM participants " + where
rec = db.fetchone(SQL, (val, ))
out = {}
if rec is not None:
out = rec
return out
def load_session(where, val):
from gittip import db
SQL = "SELECT * FROM participants " + where
rec = db.fetchone(SQL, (val,))
out = {}
if rec is not None:
out = rec
return out
def load_session(where, val):
from gittip import db
SQL = ("SELECT * FROM participants WHERE is_suspicious IS NOT true "
"AND " + where)
rec = db.fetchone(SQL, (val, ))
out = {}
if rec is not None:
out = rec
return out
def load_session(where, val):
from gittip import db
SQL =("SELECT * FROM participants WHERE is_suspicious IS NOT true "
"AND " + where)
rec = db.fetchone(SQL, (val,))
out = {}
if rec is not None:
out = rec
return out
def initialize_payday():
# Start Payday.
# =============
# We try to start a new Payday. If there is a Payday that hasn't finished
# yet, then the UNIQUE constraint on ts_end will kick in and notify us
# of that. In that case we load the existing Payday and work on it some
# more. We use the start time of the current Payday to synchronize our
# work.
try:
rec = db.fetchone("INSERT INTO paydays DEFAULT VALUES "
"RETURNING ts_start")
log("Starting a new payday.")
except IntegrityError: # Collision, we have a Payday already.
rec = db.fetchone("""
SELECT ts_start
FROM paydays
WHERE ts_end='1970-01-01T00:00:00+00'::timestamptz
""")
log("Picking up with an existing payday.")
assert rec is not None # Must either create or recycle a Payday.
payday_start = rec['ts_start']
log("Payday started at %s." % payday_start)
START_PENDING = """\
UPDATE participants
SET pending=0.00
WHERE pending IS NULL
"""
db.execute(START_PENDING)
log("Zeroed out the pending column.")
PARTICIPANTS = """\
SELECT id, balance, balanced_account_uri
FROM participants
WHERE claimed_time IS NOT NULL
"""
participants = db.fetchall(PARTICIPANTS)
log("Fetched participants.")
return participants, payday_start
def mark_payday_missing_funding():
STATS = """\
UPDATE paydays
SET ncc_missing = ncc_missing + 1
WHERE ts_end='1970-01-01T00:00:00+00'::timestamptz
RETURNING id
"""
assert_one_payday(db.fetchone(STATS))
def charge(participant_id, stripe_customer_id, amount):
"""Given two unicodes and a Decimal, return a boolean indicating success.
This is the only place where we actually charge credit cards. Amount should
be the nominal amount. We compute Gittip's fee in this function and add
it to amount.
"""
typecheck( participant_id, unicode
, stripe_customer_id, (unicode, None)
, amount, decimal.Decimal
)
if stripe_customer_id is None:
STATS = """\
UPDATE paydays
SET ncc_missing = ncc_missing + 1
WHERE ts_end='1970-01-01T00:00:00+00'::timestamptz
RETURNING id
"""
assert_one_payday(db.fetchone(STATS))
return False
# We have a purported stripe_customer_id. Try to use it.
# ======================================================
try_charge_amount = (amount + FEE[0]) * FEE[1]
try_charge_amount = try_charge_amount.quantize( FEE[0]
, rounding=decimal.ROUND_UP
)
charge_amount = try_charge_amount
also_log = ''
if charge_amount < MINIMUM:
charge_amount = MINIMUM # per Stripe
also_log = ', rounded up to $%s' % charge_amount
fee = try_charge_amount - amount
cents = int(charge_amount * 100)
msg = "Charging %s %d cents ($%s + $%s fee = $%s%s) ... "
msg %= participant_id, cents, amount, fee, try_charge_amount, also_log
try:
stripe.Charge.create( customer=stripe_customer_id
, amount=cents
, description=participant_id
, currency="USD"
)
err = False
log(msg + "succeeded.")
except stripe.StripeError, err:
log(msg + "failed: %s" % err.message)
def resolve_unclaimed(participant):
"""Given a participant dict, return an URL path.
"""
rec = db.fetchone("SELECT network, user_info FROM social_network_users "
"WHERE participant_id=%s", (participant['id'],))
if rec is None:
out = None
elif rec['network'] == 'github':
out = '/on/github/%s/' % rec['user_info']['login']
else:
assert rec['network'] == 'twitter'
out = '/on/twitter/%s/' % rec['user_info']['screen_name']
return out
def resolve_unclaimed(participant):
"""Given a participant dict, return an URL path.
"""
rec = db.fetchone(
"SELECT network, user_info FROM social_network_users "
"WHERE participant_id=%s", (participant['id'], ))
if rec is None:
out = None
elif rec['network'] == 'github':
out = '/on/github/%s/' % rec['user_info']['login']
else:
assert rec['network'] == 'twitter'
out = '/on/twitter/%s/' % rec['user_info']['screen_name']
return out
def resolve(user_id):
"""Given str, return a participant_id.
"""
FETCH = """\
SELECT participant_id
FROM social_network_users
WHERE network='twitter'
AND user_info -> 'user_id' = %s
""" # XXX Uniqueness constraint on screen_name?
rec = db.fetchone(FETCH, (user_id,))
if rec is None:
raise Exception("Twitter user %s has no participant." % (user_id))
return rec['participant_id']
def resolve(login):
"""Given two str, return a participant_id.
"""
FETCH = """\
SELECT participant_id
FROM social_network_users
WHERE network='github'
AND user_info -> 'login' = %s
""" # XXX Uniqueness constraint on login?
rec = db.fetchone(FETCH, (login,))
if rec is None:
raise Exception("Github user %s has no participant." % (login))
return rec['participant_id']
def resolve(login):
"""Given two str, return a participant_id.
"""
FETCH = """\
SELECT participant_id
FROM social_network_users
WHERE network='github'
AND user_info -> 'login' = %s
""" # XXX Uniqueness constraint on login?
rec = db.fetchone(FETCH, (login, ))
if rec is None:
raise Exception("Github user %s has no participant." % (login))
return rec['participant_id']
def payday_one(payday_start, participant):
"""Given one participant record, pay their day.
Charge each participants' credit card if needed before transfering money
between Gittip accounts.
"""
tips, total = get_tips_and_total( participant['id']
, for_payday=payday_start
)
typecheck(total, decimal.Decimal)
short = total - participant['balance']
if short > 0:
# The participant's Gittip account is short the amount needed to fund
# all their tips. Let's try pulling in money from their credit card. If
# their credit card fails we'll forge ahead, in case they have a
# positive Gittip balance already that can be used to fund at least
# *some* tips. The charge method will have set last_bill_result to a
# non-empty string if the card did fail.
charge(participant['id'], participant['balanced_account_uri'], short)
successful_tips = 0
for tip in tips:
result = log_tip(participant, tip, payday_start)
if result >= 0:
successful_tips += result
else:
break
# Update stats.
# =============
STATS = """\
UPDATE paydays
SET nparticipants = nparticipants + 1
, ntippers = ntippers + %s
, ntips = ntips + %s
WHERE ts_end='1970-01-01T00:00:00+00'::timestamptz
RETURNING id
"""
assert_one_payday(db.fetchone(STATS,
(1 if successful_tips > 0 else 0, successful_tips)))
def payday_one(payday_start, participant):
"""Given one participant record, pay their day.
Charge each participants' credit card if needed before transfering money
between Gittip accounts.
"""
tips, total = get_tips_and_total( participant['id']
, for_payday=payday_start
)
typecheck(total, decimal.Decimal)
short = total - participant['balance']
if short > 0:
charge(participant['id'], participant['pmt'], short)
ntips = 0
for tip in tips:
if tip['amount'] == 0:
continue
if not transfer(participant['id'], tip['tippee'], tip['amount']):
# The transfer failed due to a lack of funds for the
# participant. Don't try any further transfers.
log("FAILURE: $%s from %s to %s." % (tip['amount'], participant['id'], tip['tippee']))
break
log("SUCCESS: $%s from %s to %s." % (tip['amount'], participant['id'], tip['tippee']))
ntips += 1
# Update stats.
# =============
STATS = """\
UPDATE paydays
SET nparticipants = nparticipants + 1
, ntippers = ntippers + %s
, ntips = ntips + %s
WHERE ts_end='1970-01-01T00:00:00+00'::timestamptz
RETURNING id
"""
assert_one_payday(db.fetchone(STATS, (1 if ntips > 0 else 0, ntips)))
def payday_one(payday_start, participant):
"""Given one participant record, pay their day.
Charge each participants' credit card if needed before transfering money
between Gittip accounts.
"""
tips, total = get_tips_and_total(participant['id'],
for_payday=payday_start)
typecheck(total, decimal.Decimal)
short = total - participant['balance']
if short > 0:
charge(participant['id'], participant['pmt'], short)
ntips = 0
for tip in tips:
if tip['amount'] == 0:
continue
if not transfer(participant['id'], tip['tippee'], tip['amount']):
# The transfer failed due to a lack of funds for the
# participant. Don't try any further transfers.
log("FAILURE: $%s from %s to %s." %
(tip['amount'], participant['id'], tip['tippee']))
break
log("SUCCESS: $%s from %s to %s." %
(tip['amount'], participant['id'], tip['tippee']))
ntips += 1
# Update stats.
# =============
STATS = """\
UPDATE paydays
SET nparticipants = nparticipants + 1
, ntippers = ntippers + %s
, ntips = ntips + %s
WHERE ts_end='1970-01-01T00:00:00+00'::timestamptz
RETURNING id
"""
assert_one_payday(db.fetchone(STATS, (1 if ntips > 0 else 0, ntips)))
def payday():
"""This is the big one.
Settling the graph of Gittip balances is an abstract event called Payday.
On Payday, we want to use a participant's Gittip balance to settle their
tips due (pulling in more money via credit card as needed), but we only
want to use their balance at the start of Payday. Balance changes should be
atomic globally per-Payday.
This function runs every Friday. It is structured such that it can be run
again safely if it crashes.
"""
log("Greetings, program! It's PAYDAY!!!!")
# Start Payday.
# =============
# We try to start a new Payday. If there is a Payday that hasn't finished
# yet, then the UNIQUE constraint on ts_end will kick in and notify us
# of that. In that case we load the existing Payday and work on it some
# more. We use the start time of the current Payday to synchronize our
# work.
try:
rec = db.fetchone("INSERT INTO paydays DEFAULT VALUES "
"RETURNING ts_start")
log("Starting a new payday.")
except IntegrityError: # Collision, we have a Payday already.
rec = db.fetchone("SELECT ts_start FROM paydays WHERE ts_end='1970-01-01T00:00:00+00'::timestamptz")
log("Picking up with an existing payday.")
assert rec is not None # Must either create or recycle a Payday.
payday_start = rec['ts_start']
log("Payday started at %s." % payday_start)
START_PENDING = """\
UPDATE participants
SET pending=0.00
WHERE pending IS NULL
"""
db.execute(START_PENDING)
log("Zeroed out the pending column.")
PARTICIPANTS = """\
SELECT id, balance, stripe_customer_id
FROM participants
WHERE claimed_time IS NOT NULL
"""
participants = db.fetchall(PARTICIPANTS)
log("Fetched participants.")
# Drop to core.
# =============
# We are now locked for Payday. If the power goes out at this point then we
# will need to start over and reacquire the lock.
payday_loop(payday_start, participants)
# Finish Payday.
# ==============
# Transfer pending into balance for all users, setting pending to NULL.
# Close out the paydays entry as well.
with db.get_connection() as conn:
cursor = conn.cursor()
cursor.execute("""\
UPDATE participants
SET balance = (balance + pending)
, pending = NULL
""")
cursor.execute("""\
UPDATE paydays
SET ts_end=now()
WHERE ts_end='1970-01-01T00:00:00+00'::timestamptz
RETURNING id
""")
assert_one_payday(cursor.fetchone())
conn.commit()
log("Finished payday.")
def charge(participant_id, pmt, amount):
"""Given two unicodes and a Decimal, return a boolean indicating success.
This is the only place where we actually charge credit cards. Amount should
be the nominal amount. We compute Gittip's fee in this function and add
it to amount.
"""
typecheck(pmt, (unicode, None), participant_id, unicode, amount,
decimal.Decimal)
if pmt is None:
STATS = """\
UPDATE paydays
SET npmt_missing = npmt_missing + 1
WHERE ts_end='1970-01-01T00:00:00+00'::timestamptz
RETURNING id
"""
assert_one_payday(db.fetchone(STATS))
return False
# We have a purported payment method token. Try to use it.
# ========================================================
charge_amount = (amount + FEE[0]) * FEE[1]
charge_amount = charge_amount.quantize(FEE[0], rounding=decimal.ROUND_UP)
fee = charge_amount - amount
log("Charging %s $%s + $%s fee = $%s." %
(participant_id, amount, fee, charge_amount))
transaction = Processor.purchase(pmt, charge_amount, custom=participant_id)
# XXX If the power goes out at this point then Postgres will be out of sync
# with Samurai. We'll have to resolve that manually be reviewing the
# Samurai transaction log and modifying Postgres accordingly.
with db.get_connection() as conn:
cur = conn.cursor()
if transaction.errors:
last_bill_result = json.dumps(transaction.errors)
amount = decimal.Decimal('0.00')
STATS = """\
UPDATE paydays
SET npmt_failing = npmt_failing + 1
WHERE ts_end='1970-01-01T00:00:00+00'::timestamptz
RETURNING id
"""
cur.execute(STATS)
assert_one_payday(cur.fetchone())
else:
last_bill_result = ''
EXCHANGE = """\
INSERT INTO exchanges
(amount, fee, participant_id)
VALUES (%s, %s, %s)
"""
cur.execute(EXCHANGE, (amount, fee, participant_id))
STATS = """\
UPDATE paydays
SET nexchanges = nexchanges + 1
, exchange_volume = exchange_volume + %s
, exchange_fees_volume = exchange_fees_volume + %s
WHERE ts_end='1970-01-01T00:00:00+00'::timestamptz
RETURNING id
"""
cur.execute(STATS, (charge_amount, fee))
assert_one_payday(cur.fetchone())
# Update the participant's balance.
# =================================
# Credit card charges go immediately to balance, not to pending.
RESULT = """\
UPDATE participants
SET last_bill_result=%s
, balance=(balance + %s)
WHERE id=%s
"""
cur.execute(RESULT, (last_bill_result, amount, participant_id))
conn.commit()
return not bool(last_bill_result) # True indicates success
def upsert(network, user_id, username, user_info, claim=False):
"""Given str, unicode, unicode, and dict, return unicode and boolean.
Network is the name of a social network that we support (ASCII blah).
User_id is an immutable unique identifier for the given user on the given
social network. Username is the user's login/user_id on the given social
network. We will try to claim that for them here on Gittip. If their
username is already taken on Gittip then we give them a random one; they
can change it on their Gittip profile page. User_id and username may or
may not be the same. User is a dictionary of profile info per the named
network. All network dicts must have an id key that corresponds to the
primary key in the underlying table in our own db.
If claim is True, the return value is the participant_id. Otherwise it is a
tuple: (participant_id, claimed [boolean], balance).
"""
typecheck(network, str, user_id, (int, unicode), user_info, dict)
user_id = unicode(user_id)
# Record the user info in our database.
# =====================================
INSERT = """\
INSERT INTO social_network_users
(network, user_id)
VALUES (%s, %s)
"""
try:
db.execute(INSERT, (
network,
user_id,
))
except IntegrityError:
pass # That login is already in our db.
UPDATE = """\
UPDATE social_network_users
SET user_info=%s
WHERE user_id=%s
RETURNING participant_id
"""
for k, v in user_info.items():
# I believe hstore can take any type of value, but psycopg2 can't.
# https://postgres.heroku.com/blog/past/2012/3/14/introducing_keyvalue_data_storage_in_heroku_postgres/
# http://initd.org/psycopg/docs/extras.html#hstore-data-type
user_info[k] = unicode(v)
rec = db.fetchone(UPDATE, (user_info, user_id))
# Find a participant.
# ===================
if rec is not None and rec['participant_id'] is not None:
# There is already a Gittip participant associated with this account.
participant_id = rec['participant_id']
new_participant = False
else:
# This is the first time we've seen this user. Let's create a new
# participant for them, claiming their user_id for them if possible.
participant_id = claim_id(username)
new_participant = True
# Associate the social network user with the Gittip participant.
# ================================================================
ASSOCIATE = """\
UPDATE social_network_users
SET participant_id=%s
WHERE network=%s
AND user_id=%s
AND ( (participant_id IS NULL)
OR (participant_id=%s)
)
RETURNING participant_id
"""
log(u"Associating %s (%s) on %s with %s on Gittip." %
(username, user_id, network, participant_id))
rows = db.fetchall(ASSOCIATE,
(participant_id, network, user_id, participant_id))
nrows = len(list(rows))
assert nrows in (0, 1)
if nrows == 0:
# Against all odds, the account was otherwise associated with another
# participant while we weren't looking. Maybe someone paid them money
# at *just* the right moment. If we created a new participant then back
# that out.
if new_participant:
db.execute("DELETE FROM participants WHERE id=%s",
(participant_id, ))
rec = db.fetchone(
"SELECT participant_id FROM social_network_users "
"WHERE network=%s AND user_id=%s", (network, user_id))
if rec is not None:
# Use the participant associated with this account.
participant_id = rec['participant_id']
assert participant_id is not None
else:
# Okay, now this is just screwy. The participant disappeared right
# at the last moment! Log it and fail.
raise Exception("We're bailing on associating %s user %s (%s) with"
" a Gittip participant." %
(network, username, user_id))
# Record the participant as claimed if asked to.
# ==============================================
if claim:
CLAIM = """\
UPDATE participants
SET claimed_time=CURRENT_TIMESTAMP
WHERE id=%s
AND claimed_time IS NULL
"""
db.execute(CLAIM, (participant_id, ))
out = participant_id
else:
rec = db.fetchone(
"SELECT claimed_time, balance FROM participants "
"WHERE id=%s", (participant_id, ))
assert rec is not None
out = (participant_id, rec['claimed_time'] is not None, rec['balance'])
return out
def upsert(network, user_id, username, user_info):
"""Given str, unicode, unicode, and dict, return unicode and boolean.
Network is the name of a social network that we support (ASCII blah).
User_id is an immutable unique identifier for the given user on the given
social network. Username is the user's login/user_id on the given social
network. It is only used here for logging. Specifically, we don't reserve
their username for them on Gittip if they're new here. We give them a
random participant_id here, and they'll have a chance to change it if/when
they opt in. User_id and username may or may not be the same. User_info is
a dictionary of profile info per the named network. All network dicts must
have an id key that corresponds to the primary key in the underlying table
in our own db.
The return value is a tuple: (participant_id [unicode], is_claimed
[boolean], is_locked [boolean], balance [Decimal]).
"""
typecheck(network, str, user_id, (int, unicode), username, unicode,
user_info, dict)
user_id = unicode(user_id)
# Record the user info in our database.
# =====================================
INSERT = """\
INSERT INTO social_network_users
(network, user_id)
VALUES (%s, %s)
"""
try:
db.execute(INSERT, (
network,
user_id,
))
except IntegrityError:
pass # That login is already in our db.
UPDATE = """\
UPDATE social_network_users
SET user_info=%s
WHERE user_id=%s
RETURNING participant_id
"""
for k, v in user_info.items():
# Cast everything to unicode. I believe hstore can take any type of
# value, but psycopg2 can't.
# https://postgres.heroku.com/blog/past/2012/3/14/introducing_keyvalue_data_storage_in_heroku_postgres/
# http://initd.org/psycopg/docs/extras.html#hstore-data-type
user_info[k] = unicode(v)
rec = db.fetchone(UPDATE, (user_info, user_id))
# Find a participant.
# ===================
if rec is not None and rec['participant_id'] is not None:
# There is already a Gittip participant associated with this account.
participant_id = rec['participant_id']
new_participant = False
else:
# This is the first time we've seen this user. Let's create a new
# participant for them.
participant_id = get_a_participant_id()
new_participant = True
# Associate the social network user with the Gittip participant.
# ================================================================
ASSOCIATE = """\
UPDATE social_network_users
SET participant_id=%s
WHERE network=%s
AND user_id=%s
AND ( (participant_id IS NULL)
OR (participant_id=%s)
)
RETURNING participant_id, is_locked
"""
log(u"Associating %s (%s) on %s with %s on Gittip." %
(username, user_id, network, participant_id))
rows = db.fetchall(ASSOCIATE,
(participant_id, network, user_id, participant_id))
rows = list(rows)
nrows = len(rows)
assert nrows in (0, 1)
if nrows == 1:
is_locked = rows[0]['is_locked']
else:
# Against all odds, the account was otherwise associated with another
# participant while we weren't looking. Maybe someone paid them money
# at *just* the right moment. If we created a new participant then back
# that out.
if new_participant:
db.execute("DELETE FROM participants WHERE id=%s",
(participant_id, ))
rec = db.fetchone(
"SELECT participant_id, is_locked "
"FROM social_network_users "
"WHERE network=%s AND user_id=%s", (network, user_id))
if rec is not None:
# Use the participant associated with this account.
participant_id = rec['participant_id']
is_locked = rec['is_locked']
assert participant_id is not None
else:
# Okay, now this is just screwy. The participant disappeared right
# at the last moment! Log it and fail.
raise Exception("We're bailing on associating %s user %s (%s) with"
" a Gittip participant." %
(network, username, user_id))
rec = db.fetchone(
"SELECT claimed_time, balance FROM participants "
"WHERE id=%s", (participant_id, ))
assert rec is not None
return (participant_id, rec['claimed_time']
is not None, is_locked, rec['balance'])
def charge(participant_id, pmt, amount):
"""Given two unicodes and a Decimal, return a boolean indicating success.
This is the only place where we actually charge credit cards. Amount should
be the nominal amount. We compute Gittip's fee in this function and add
it to amount.
"""
typecheck( pmt, (unicode, None)
, participant_id, unicode
, amount, decimal.Decimal
)
if pmt is None:
STATS = """\
UPDATE paydays
SET npmt_missing = npmt_missing + 1
WHERE ts_end='1970-01-01T00:00:00+00'::timestamptz
RETURNING id
"""
assert_one_payday(db.fetchone(STATS))
return False
# We have a purported payment method token. Try to use it.
# ========================================================
charge_amount = (amount + FEE[0]) * FEE[1]
charge_amount = charge_amount.quantize(FEE[0], rounding=decimal.ROUND_UP)
fee = charge_amount - amount
log("Charging %s $%s + $%s fee = $%s."
% (participant_id, amount, fee, charge_amount))
transaction = Processor.purchase(pmt, charge_amount, custom=participant_id)
# XXX If the power goes out at this point then Postgres will be out of sync
# with Samurai. We'll have to resolve that manually be reviewing the
# Samurai transaction log and modifying Postgres accordingly.
with db.get_connection() as conn:
cur = conn.cursor()
if transaction.errors:
last_bill_result = json.dumps(transaction.errors)
amount = decimal.Decimal('0.00')
STATS = """\
UPDATE paydays
SET npmt_failing = npmt_failing + 1
WHERE ts_end='1970-01-01T00:00:00+00'::timestamptz
RETURNING id
"""
cur.execute(STATS)
assert_one_payday(cur.fetchone())
else:
last_bill_result = ''
EXCHANGE = """\
INSERT INTO exchanges
(amount, fee, participant_id)
VALUES (%s, %s, %s)
"""
cur.execute(EXCHANGE, (amount, fee, participant_id))
STATS = """\
UPDATE paydays
SET nexchanges = nexchanges + 1
, exchange_volume = exchange_volume + %s
, exchange_fees_volume = exchange_fees_volume + %s
WHERE ts_end='1970-01-01T00:00:00+00'::timestamptz
RETURNING id
"""
cur.execute(STATS, (charge_amount, fee))
assert_one_payday(cur.fetchone())
# Update the participant's balance.
# =================================
# Credit card charges go immediately to balance, not to pending.
RESULT = """\
UPDATE participants
SET last_bill_result=%s
, balance=(balance + %s)
WHERE id=%s
"""
cur.execute(RESULT, (last_bill_result, amount, participant_id))
conn.commit()
return not bool(last_bill_result) # True indicates success
def check_membership(self, user):
return dear_god_why.fetchone("""
SELECT * FROM current_communities WHERE slug=%s AND participant=%s
""", (self.slug, user.username)) is not None
def upsert(network, user_id, username, user_info):
"""Given str, unicode, unicode, and dict, return unicode and boolean.
Network is the name of a social network that we support (ASCII blah).
User_id is an immutable unique identifier for the given user on the given
social network. Username is the user's login/user_id on the given social
network. It is only used here for logging. Specifically, we don't reserve
their username for them on Gittip if they're new here. We give them a
random participant_id here, and they'll have a chance to change it if/when
they opt in. User_id and username may or may not be the same. User_info is
a dictionary of profile info per the named network. All network dicts must
have an id key that corresponds to the primary key in the underlying table
in our own db.
The return value is a tuple: (participant_id [unicode], is_claimed
[boolean], is_locked [boolean], balance [Decimal]).
"""
typecheck( network, str
, user_id, (int, unicode)
, username, unicode
, user_info, dict
)
user_id = unicode(user_id)
# Record the user info in our database.
# =====================================
INSERT = """\
INSERT INTO social_network_users
(network, user_id)
VALUES (%s, %s)
"""
try:
db.execute(INSERT, (network, user_id,))
except IntegrityError:
pass # That login is already in our db.
UPDATE = """\
UPDATE social_network_users
SET user_info=%s
WHERE user_id=%s
RETURNING participant_id
"""
for k, v in user_info.items():
# Cast everything to unicode. I believe hstore can take any type of
# value, but psycopg2 can't.
# https://postgres.heroku.com/blog/past/2012/3/14/introducing_keyvalue_data_storage_in_heroku_postgres/
# http://initd.org/psycopg/docs/extras.html#hstore-data-type
user_info[k] = unicode(v)
rec = db.fetchone(UPDATE, (user_info, user_id))
# Find a participant.
# ===================
if rec is not None and rec['participant_id'] is not None:
# There is already a Gittip participant associated with this account.
participant_id = rec['participant_id']
new_participant = False
else:
# This is the first time we've seen this user. Let's create a new
# participant for them.
participant_id = get_a_participant_id()
new_participant = True
# Associate the social network user with the Gittip participant.
# ================================================================
ASSOCIATE = """\
UPDATE social_network_users
SET participant_id=%s
WHERE network=%s
AND user_id=%s
AND ( (participant_id IS NULL)
OR (participant_id=%s)
)
RETURNING participant_id, is_locked
"""
log(u"Associating %s (%s) on %s with %s on Gittip."
% (username, user_id, network, participant_id))
rows = db.fetchall( ASSOCIATE
, (participant_id, network, user_id, participant_id)
)
rows = list(rows)
nrows = len(rows)
assert nrows in (0, 1)
if nrows == 1:
is_locked = rows[0]['is_locked']
else:
# Against all odds, the account was otherwise associated with another
# participant while we weren't looking. Maybe someone paid them money
# at *just* the right moment. If we created a new participant then back
# that out.
if new_participant:
db.execute( "DELETE FROM participants WHERE id=%s"
, (participant_id,)
)
rec = db.fetchone( "SELECT participant_id, is_locked "
"FROM social_network_users "
"WHERE network=%s AND user_id=%s"
, (network, user_id)
)
if rec is not None:
# Use the participant associated with this account.
participant_id = rec['participant_id']
is_locked = rec['is_locked']
assert participant_id is not None
else:
# Okay, now this is just screwy. The participant disappeared right
# at the last moment! Log it and fail.
raise Exception("We're bailing on associating %s user %s (%s) with"
" a Gittip participant."
% (network, username, user_id))
rec = db.fetchone( "SELECT claimed_time, balance FROM participants "
"WHERE id=%s"
, (participant_id,)
)
assert rec is not None
return ( participant_id
, rec['claimed_time'] is not None
, is_locked
, rec['balance']
)
def upsert(network, user_id, username, user_info, claim=False):
"""Given str, unicode, unicode, and dict, return unicode and boolean.
Network is the name of a social network that we support (ASCII blah).
User_id is an immutable unique identifier for the given user on the given
social network. Username is the user's login/user_id on the given social
network. We will try to claim that for them here on Gittip. If their
username is already taken on Gittip then we give them a random one; they
can change it on their Gittip profile page. User_id and username may or
may not be the same. User is a dictionary of profile info per the named
network. All network dicts must have an id key that corresponds to the
primary key in the underlying table in our own db.
If claim is True, the return value is the participant_id. Otherwise it is a
tuple: (participant_id, claimed [boolean], balance).
"""
typecheck( network, str
, user_id, (int, unicode)
, user_info, dict
)
user_id = unicode(user_id)
# Record the user info in our database.
# =====================================
INSERT = """\
INSERT INTO social_network_users
(network, user_id)
VALUES (%s, %s)
"""
try:
db.execute(INSERT, (network, user_id,))
except IntegrityError:
pass # That login is already in our db.
UPDATE = """\
UPDATE social_network_users
SET user_info=%s
WHERE user_id=%s
RETURNING participant_id
"""
for k, v in user_info.items():
# I believe hstore can take any type of value, but psycopg2 can't.
# https://postgres.heroku.com/blog/past/2012/3/14/introducing_keyvalue_data_storage_in_heroku_postgres/
# http://initd.org/psycopg/docs/extras.html#hstore-data-type
user_info[k] = unicode(v)
rec = db.fetchone(UPDATE, (user_info, user_id))
# Find a participant.
# ===================
if rec is not None and rec['participant_id'] is not None:
# There is already a Gittip participant associated with this account.
participant_id = rec['participant_id']
new_participant = False
else:
# This is the first time we've seen this user. Let's create a new
# participant for them, claiming their user_id for them if possible.
participant_id = claim_id(username)
new_participant = True
# Associate the social network user with the Gittip participant.
# ================================================================
ASSOCIATE = """\
UPDATE social_network_users
SET participant_id=%s
WHERE network=%s
AND user_id=%s
AND ( (participant_id IS NULL)
OR (participant_id=%s)
)
RETURNING participant_id
"""
log(u"Associating %s (%s) on %s with %s on Gittip."
% (username, user_id, network, participant_id))
rows = db.fetchall( ASSOCIATE
, (participant_id, network, user_id, participant_id)
)
nrows = len(list(rows))
assert nrows in (0, 1)
if nrows == 0:
# Against all odds, the account was otherwise associated with another
# participant while we weren't looking. Maybe someone paid them money
# at *just* the right moment. If we created a new participant then back
# that out.
if new_participant:
db.execute( "DELETE FROM participants WHERE id=%s"
, (participant_id,)
)
rec = db.fetchone( "SELECT participant_id FROM social_network_users "
"WHERE network=%s AND user_id=%s"
, (network, user_id)
)
if rec is not None:
# Use the participant associated with this account.
participant_id = rec['participant_id']
assert participant_id is not None
else:
# Okay, now this is just screwy. The participant disappeared right
# at the last moment! Log it and fail.
raise Exception("We're bailing on associating %s user %s (%s) with"
" a Gittip participant."
% (network, username, user_id))
# Record the participant as claimed if asked to.
# ==============================================
if claim:
CLAIM = """\
UPDATE participants
SET claimed_time=CURRENT_TIMESTAMP
WHERE id=%s
AND claimed_time IS NULL
"""
db.execute(CLAIM, (participant_id,))
out = participant_id
else:
rec = db.fetchone( "SELECT claimed_time, balance FROM participants "
"WHERE id=%s"
, (participant_id,)
)
assert rec is not None
out = (participant_id, rec['claimed_time'] is not None, rec['balance'])
return out
def payday_one(payday_start, participant):
"""Given one participant record, pay their day.
Charge each participants' credit card if needed before transfering money
between Gittip accounts.
"""
tips, total = get_tips_and_total( participant['id']
, for_payday=payday_start
)
typecheck(total, decimal.Decimal)
short = total - participant['balance']
if short > 0:
# The participant's Gittip account is short the amount needed to fund
# all their tips. Let's try pulling in money from their credit card. If
# their credit card fails we'll forge ahead, in case they have a
# positive Gittip balance already that can be used to fund at least
# *some* tips. The charge method will have set last_bill_result to a
# non-empty string if the card did fail.
charge(participant['id'], participant['stripe_customer_id'], short)
ntips = 0
for tip in tips:
msg = "$%s from %s to %s."
msg %= (tip['amount'], participant['id'], tip['tippee'])
if tip['amount'] == 0:
# The tips table contains a record for every time you click a tip
# button. So if you click $0.08 then $0.64 then $0.00, that
# generates three entries. We are looking at the last entry here,
# and it's zero.
continue
claimed_time = tip['claimed_time']
if claimed_time is None or claimed_time > payday_start:
# Gittip is opt-in. We're only going to collect money on a person's
# behalf if they opted-in by claiming their account before the
# start of this payday.
log("SKIPPED: %s" % msg)
continue
if not transfer(participant['id'], tip['tippee'], tip['amount']):
# The transfer failed due to a lack of funds for the participant.
# Don't try any further transfers.
log("FAILURE: %s" % msg)
break
log("SUCCESS: %s" % msg)
ntips += 1
# Update stats.
# =============
STATS = """\
UPDATE paydays
SET nparticipants = nparticipants + 1
, ntippers = ntippers + %s
, ntips = ntips + %s
WHERE ts_end='1970-01-01T00:00:00+00'::timestamptz
RETURNING id
"""
assert_one_payday(db.fetchone(STATS, (1 if ntips > 0 else 0, ntips)))
def payday():
"""This is the big one.
Settling the graph of Gittip balances is an abstract event called Payday.
On Payday, we want to use a participant's Gittip balance to settle their
tips due (pulling in more money via credit card as needed), but we only
want to use their balance at the start of Payday. Balance changes should be
atomic globally per-Payday.
This function runs every Friday. It is structured such that it can be run
again safely if it crashes.
"""
log("Greetings, program! It's PAYDAY!!!!")
# Start Payday.
# =============
# We try to start a new Payday. If there is a Payday that hasn't finished
# yet, then the UNIQUE constraint on ts_end will kick in and notify us
# of that. In that case we load the existing Payday and work on it some
# more. We use the start time of the current Payday to synchronize our
# work.
try:
rec = db.fetchone("INSERT INTO paydays DEFAULT VALUES "
"RETURNING ts_start")
log("Starting a new payday.")
except IntegrityError: # Collision, we have a Payday already.
rec = db.fetchone(
"SELECT ts_start FROM paydays WHERE ts_end='1970-01-01T00:00:00+00'::timestamptz"
)
log("Picking up with an existing payday.")
assert rec is not None # Must either create or recycle a Payday.
payday_start = rec['ts_start']
log("Payday started at %s." % payday_start)
START_PENDING = """\
UPDATE participants
SET pending=0.00
WHERE pending IS NULL
"""
db.execute(START_PENDING)
log("Zeroed out the pending column.")
PARTICIPANTS = """\
SELECT id, balance, payment_method_token AS pmt
FROM participants
"""
participants = db.fetchall(PARTICIPANTS)
log("Fetched participants.")
# Drop to core.
# =============
# We are now locked for Payday. If the power goes out at this point then we
# will need to start over and reacquire the lock.
payday_loop(payday_start, participants)
# Finish Payday.
# ==============
# Transfer pending into balance for all users, setting pending to NULL.
# Close out the paydays entry as well.
with db.get_connection() as conn:
cursor = conn.cursor()
cursor.execute("""\
UPDATE participants
SET balance = (balance + pending)
, pending = NULL
""")
cursor.execute("""\
UPDATE paydays
SET ts_end=now()
WHERE ts_end='1970-01-01T00:00:00+00'::timestamptz
RETURNING id
""")
assert_one_payday(cursor.fetchone())
conn.commit()
log("Finished payday.")
