def upsert(self, i):
"""Insert or update the user's info.
"""
# Clean up avatar_url
if i.avatar_url:
scheme, netloc, path, query, fragment = urlsplit(i.avatar_url)
fragment = ''
if netloc.endswith('gravatar.com'):
query = 's=128'
i.avatar_url = urlunsplit((scheme, netloc, path, query, fragment))
# Serialize extra_info
if isinstance(i.extra_info, ET.Element):
i.extra_info = xmltodict.parse(ET.tostring(i.extra_info))
i.extra_info = json.dumps(i.extra_info)
cols, vals = zip(*i.__dict__.items())
cols = ', '.join(cols)
placeholders = ', '.join(['%s'] * len(vals))
try:
# Try to insert the account
# We do this with a transaction so that if the insert fails, the
# participant we reserved for them is rolled back as well.
with self.db.get_cursor() as cursor:
username = reserve_a_random_username(cursor)
cursor.execute(
"""
INSERT INTO elsewhere
(participant, platform, {0})
VALUES (%s, %s, {1})
""".format(cols, placeholders), (username, self.name) + vals)
except IntegrityError:
# The account is already in the DB, update it instead
username = self.db.one(
"""
UPDATE elsewhere
SET ({0}) = ({1})
WHERE platform=%s AND user_id=%s
RETURNING participant
""".format(cols, placeholders), vals + (self.name, i.user_id))
# Propagate avatar_url to participant
self.db.run(
"""
UPDATE participants p
SET avatar_url = (
SELECT avatar_url
FROM elsewhere
WHERE participant = p.username
ORDER BY platform = 'github' DESC,
avatar_url LIKE '%%gravatar.com%%' DESC
LIMIT 1
)
WHERE p.username = %s
""", (username, ))
# Now delegate to get_account_from_db
return self.get_account_from_db(i.user_name)
def upsert(self, i):
"""Insert or update the user's info.
"""
# Clean up avatar_url
if i.avatar_url:
scheme, netloc, path, query, fragment = urlsplit(i.avatar_url)
fragment = ''
if netloc.endswith('githubusercontent.com') or \
netloc.endswith('gravatar.com'):
query = 's=128'
i.avatar_url = urlunsplit((scheme, netloc, path, query, fragment))
# Serialize extra_info
if isinstance(i.extra_info, ET.Element):
i.extra_info = xmltodict.parse(ET.tostring(i.extra_info))
i.extra_info = json.dumps(i.extra_info)
cols, vals = zip(*i.__dict__.items())
cols = ', '.join(cols)
placeholders = ', '.join(['%s']*len(vals))
try:
# Try to insert the account
# We do this with a transaction so that if the insert fails, the
# participant we reserved for them is rolled back as well.
with self.db.get_cursor() as cursor:
username = reserve_a_random_username(cursor)
cursor.execute("""
INSERT INTO elsewhere
(participant, platform, {0})
VALUES (%s, %s, {1})
""".format(cols, placeholders), (username, self.name)+vals)
except IntegrityError:
# The account is already in the DB, update it instead
username = self.db.one("""
UPDATE elsewhere
SET ({0}) = ({1})
WHERE platform=%s AND user_id=%s
RETURNING participant
""".format(cols, placeholders), vals+(self.name, i.user_id))
# Propagate avatar_url to participant
self.db.run("""
UPDATE participants p
SET avatar_url = (
SELECT avatar_url
FROM elsewhere
WHERE participant = p.username
ORDER BY platform = 'github' DESC,
avatar_url LIKE '%%gravatar.com%%' DESC
LIMIT 1
)
WHERE p.username = %s
""", (username,))
# Now delegate to get_account_from_db
return self.get_account_from_db(i.user_name)
def with_random_username(cls):
"""Return a new participant with a random username.
"""
with cls.db.get_cursor() as cursor:
username = reserve_a_random_username(cursor)
return cls.from_username(username)
def upsert(self, user_info):
"""Given a dict, return a tuple.
User_id is an immutable unique identifier for the given user on the
given platform. Username is the user's login/username on the given
platform. 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 username 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 platform. All
platform 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: (username [unicode], is_claimed [boolean],
is_locked [boolean], balance [Decimal]).
"""
typecheck(user_info, dict)
# Insert the account if needed.
# =============================
# Do this with a transaction so that if the insert fails, the
# participant we reserved for them is rolled back as well.
try:
with self.db.get_cursor() as cursor:
_username = reserve_a_random_username(cursor)
cursor.execute(
"INSERT INTO elsewhere " "(platform, user_id, participant) " "VALUES (%s, %s, %s)",
(self.platform, self.user_id, _username),
)
except IntegrityError:
pass
# Update their user_info.
# =======================
# Cast everything to unicode, because (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
#
# XXX This clobbers things, of course, such as booleans. See
# /on/bitbucket/%username/index.html
for k, v in user_info.items():
user_info[k] = unicode(v)
username = self.db.one(
"""
UPDATE elsewhere
SET user_info=%s
WHERE platform=%s AND user_id=%s
RETURNING participant
""",
(user_info, self.platform, self.user_id),
)
return (username,) + self.get_misc_info(username)
def take_over(self, account, have_confirmation=False):
"""Given an AccountElsewhere or a tuple (platform_name, user_id),
associate an elsewhere account.
Returns None or raises NeedConfirmation.
This method associates an account on another platform (GitHub, Twitter,
etc.) with the given Gittip participant. Every account elsewhere has an
associated Gittip participant account, even if its only a stub
participant (it allows us to track pledges to that account should they
ever decide to join Gittip).
In certain circumstances, we want to present the user with a
confirmation before proceeding to transfer the account elsewhere to
the new Gittip account; NeedConfirmation is the signal to request
confirmation. If it was the last account elsewhere connected to the old
Gittip account, then we absorb the old Gittip account into the new one,
effectively archiving the old account.
Here's what absorbing means:
- consolidated tips to and fro are set up for the new participant
Amounts are summed, so if alice tips bob $1 and carl $1, and
then bob absorbs carl, then alice tips bob $2(!) and carl $0.
And if bob tips alice $1 and carl tips alice $1, and then bob
absorbs carl, then bob tips alice $2(!) and carl tips alice $0.
The ctime of each new consolidated tip is the older of the two
tips that are being consolidated.
If alice tips bob $1, and alice absorbs bob, then alice tips
bob $0.
If alice tips bob $1, and bob absorbs alice, then alice tips
bob $0.
- all tips to and from the other participant are set to zero
- the absorbed username is released for reuse
- the absorption is recorded in an absorptions table
This is done in one transaction.
"""
if isinstance(account, AccountElsewhere):
platform, user_id = account.platform, account.user_id
else:
platform, user_id = account
CREATE_TEMP_TABLE_FOR_UNIQUE_TIPS = """
CREATE TEMP TABLE __temp_unique_tips ON COMMIT drop AS
-- Get all the latest tips from everyone to everyone.
SELECT DISTINCT ON (tipper, tippee)
ctime, tipper, tippee, amount
FROM tips
ORDER BY tipper, tippee, mtime DESC;
"""
CONSOLIDATE_TIPS_RECEIVING = """
-- Create a new set of tips, one for each current tip *to* either
-- the dead or the live account. If a user was tipping both the
-- dead and the live account, then we create one new combined tip
-- to the live account (via the GROUP BY and sum()).
INSERT INTO tips (ctime, tipper, tippee, amount)
SELECT min(ctime), tipper, %(live)s AS tippee, sum(amount)
FROM __temp_unique_tips
WHERE (tippee = %(dead)s OR tippee = %(live)s)
-- Include tips *to* either the dead or live account.
AND NOT (tipper = %(dead)s OR tipper = %(live)s)
-- Don't include tips *from* the dead or live account,
-- lest we convert cross-tipping to self-tipping.
AND amount > 0
-- Don't include zeroed out tips, so we avoid a no-op
-- zero tip entry.
GROUP BY tipper
"""
CONSOLIDATE_TIPS_GIVING = """
-- Create a new set of tips, one for each current tip *from* either
-- the dead or the live account. If both the dead and the live
-- account were tipping a given user, then we create one new
-- combined tip from the live account (via the GROUP BY and sum()).
INSERT INTO tips (ctime, tipper, tippee, amount)
SELECT min(ctime), %(live)s AS tipper, tippee, sum(amount)
FROM __temp_unique_tips
WHERE (tipper = %(dead)s OR tipper = %(live)s)
-- Include tips *from* either the dead or live account.
AND NOT (tippee = %(dead)s OR tippee = %(live)s)
-- Don't include tips *to* the dead or live account,
-- lest we convert cross-tipping to self-tipping.
AND amount > 0
-- Don't include zeroed out tips, so we avoid a no-op
-- zero tip entry.
GROUP BY tippee
"""
ZERO_OUT_OLD_TIPS_RECEIVING = """
INSERT INTO tips (ctime, tipper, tippee, amount)
SELECT ctime, tipper, tippee, 0 AS amount
FROM __temp_unique_tips
WHERE tippee=%s AND amount > 0
"""
ZERO_OUT_OLD_TIPS_GIVING = """
INSERT INTO tips (ctime, tipper, tippee, amount)
SELECT ctime, tipper, tippee, 0 AS amount
FROM __temp_unique_tips
WHERE tipper=%s AND amount > 0
"""
with self.db.get_cursor() as cursor:
# Load the existing connection.
# =============================
# Every account elsewhere has at least a stub participant account
# on Gittip.
rec = cursor.one("""
SELECT participant
, claimed_time IS NULL AS is_stub
FROM elsewhere
JOIN participants ON participant=participants.username
WHERE elsewhere.platform=%s AND elsewhere.user_id=%s
""", (platform, user_id), default=NotSane)
other_username = rec.participant
if self.username == other_username:
# this is a no op - trying to take over itself
return
# Make sure we have user confirmation if needed.
# ==============================================
# We need confirmation in whatever combination of the following
# three cases:
#
# - the other participant is not a stub; we are taking the
# account elsewhere away from another viable Gittip
# participant
#
# - the other participant has no other accounts elsewhere; taking
# away the account elsewhere will leave the other Gittip
# participant without any means of logging in, and it will be
# archived and its tips absorbed by us
#
# - we already have an account elsewhere connected from the given
# platform, and it will be handed off to a new stub
# participant
# other_is_a_real_participant
other_is_a_real_participant = not rec.is_stub
# this_is_others_last_account_elsewhere
nelsewhere = cursor.one( "SELECT count(*) FROM elsewhere "
"WHERE participant=%s"
, (other_username,)
)
assert nelsewhere > 0 # sanity check
this_is_others_last_account_elsewhere = (nelsewhere == 1)
# we_already_have_that_kind_of_account
nparticipants = cursor.one( "SELECT count(*) FROM elsewhere "
"WHERE participant=%s AND platform=%s"
, (self.username, platform)
)
assert nparticipants in (0, 1) # sanity check
we_already_have_that_kind_of_account = nparticipants == 1
need_confirmation = NeedConfirmation( other_is_a_real_participant
, this_is_others_last_account_elsewhere
, we_already_have_that_kind_of_account
)
if need_confirmation and not have_confirmation:
raise need_confirmation
# We have user confirmation. Proceed.
# ===================================
# There is a race condition here. The last person to call this will
# win. XXX: I'm not sure what will happen to the DB and UI for the
# loser.
# Move any old account out of the way.
# ====================================
if we_already_have_that_kind_of_account:
new_stub_username = reserve_a_random_username(cursor)
cursor.run( "UPDATE elsewhere SET participant=%s "
"WHERE platform=%s AND participant=%s"
, (new_stub_username, platform, self.username)
)
# Do the deal.
# ============
# If other_is_not_a_stub, then other will have the account
# elsewhere taken away from them with this call. If there are other
# browsing sessions open from that account, they will stay open
# until they expire (XXX Is that okay?)
cursor.run( "UPDATE elsewhere SET participant=%s "
"WHERE platform=%s AND user_id=%s"
, (self.username, platform, user_id)
)
# Fold the old participant into the new as appropriate.
# =====================================================
# We want to do this whether or not other is a stub participant.
if this_is_others_last_account_elsewhere:
# Take over tips.
# ===============
x, y = self.username, other_username
cursor.run(CREATE_TEMP_TABLE_FOR_UNIQUE_TIPS)
cursor.run(CONSOLIDATE_TIPS_RECEIVING, dict(live=x, dead=y))
cursor.run(CONSOLIDATE_TIPS_GIVING, dict(live=x, dead=y))
cursor.run(ZERO_OUT_OLD_TIPS_RECEIVING, (other_username,))
cursor.run(ZERO_OUT_OLD_TIPS_GIVING, (other_username,))
# Archive the old participant.
# ============================
# We always give them a new, random username. We sign out
# the old participant.
for archive_username in gen_random_usernames():
try:
username = cursor.one("""
UPDATE participants
SET username=%s
, username_lower=%s
, session_token=NULL
, session_expires=now()
WHERE username=%s
RETURNING username
""", ( archive_username
, archive_username.lower()
, other_username
), default=NotSane)
except IntegrityError:
continue # archive_username is already taken;
# extremely unlikely, but ...
# XXX But can the UPDATE fail in other ways?
else:
assert username == archive_username
break
# Record the absorption.
# ======================
# This is for preservation of history.
cursor.run( "INSERT INTO absorptions "
"(absorbed_was, absorbed_by, archived_as) "
"VALUES (%s, %s, %s)"
, ( other_username
, self.username
, archive_username
)
)
self.update_avatar()
def take_over(self, account, have_confirmation=False):
"""Given an AccountElsewhere or a tuple (platform_name, user_id),
associate an elsewhere account.
Returns None or raises NeedConfirmation.
This method associates an account on another platform (GitHub, Twitter,
etc.) with the given Gittip participant. Every account elsewhere has an
associated Gittip participant account, even if its only a stub
participant (it allows us to track pledges to that account should they
ever decide to join Gittip).
In certain circumstances, we want to present the user with a
confirmation before proceeding to transfer the account elsewhere to
the new Gittip account; NeedConfirmation is the signal to request
confirmation. If it was the last account elsewhere connected to the old
Gittip account, then we absorb the old Gittip account into the new one,
effectively archiving the old account.
Here's what absorbing means:
- consolidated tips to and fro are set up for the new participant
Amounts are summed, so if alice tips bob $1 and carl $1, and
then bob absorbs carl, then alice tips bob $2(!) and carl $0.
And if bob tips alice $1 and carl tips alice $1, and then bob
absorbs carl, then bob tips alice $2(!) and carl tips alice $0.
The ctime of each new consolidated tip is the older of the two
tips that are being consolidated.
If alice tips bob $1, and alice absorbs bob, then alice tips
bob $0.
If alice tips bob $1, and bob absorbs alice, then alice tips
bob $0.
- all tips to and from the other participant are set to zero
- the absorbed username is released for reuse
- the absorption is recorded in an absorptions table
This is done in one transaction.
"""
if isinstance(account, AccountElsewhere):
platform, user_id = account.platform, account.user_id
else:
platform, user_id = account
CREATE_TEMP_TABLE_FOR_UNIQUE_TIPS = """
CREATE TEMP TABLE __temp_unique_tips ON COMMIT drop AS
-- Get all the latest tips from everyone to everyone.
SELECT DISTINCT ON (tipper, tippee)
ctime, tipper, tippee, amount
FROM tips
ORDER BY tipper, tippee, mtime DESC;
"""
CONSOLIDATE_TIPS_RECEIVING = """
-- Create a new set of tips, one for each current tip *to* either
-- the dead or the live account. If a user was tipping both the
-- dead and the live account, then we create one new combined tip
-- to the live account (via the GROUP BY and sum()).
INSERT INTO tips (ctime, tipper, tippee, amount)
SELECT min(ctime), tipper, %(live)s AS tippee, sum(amount)
FROM __temp_unique_tips
WHERE (tippee = %(dead)s OR tippee = %(live)s)
-- Include tips *to* either the dead or live account.
AND NOT (tipper = %(dead)s OR tipper = %(live)s)
-- Don't include tips *from* the dead or live account,
-- lest we convert cross-tipping to self-tipping.
AND amount > 0
-- Don't include zeroed out tips, so we avoid a no-op
-- zero tip entry.
GROUP BY tipper
"""
CONSOLIDATE_TIPS_GIVING = """
-- Create a new set of tips, one for each current tip *from* either
-- the dead or the live account. If both the dead and the live
-- account were tipping a given user, then we create one new
-- combined tip from the live account (via the GROUP BY and sum()).
INSERT INTO tips (ctime, tipper, tippee, amount)
SELECT min(ctime), %(live)s AS tipper, tippee, sum(amount)
FROM __temp_unique_tips
WHERE (tipper = %(dead)s OR tipper = %(live)s)
-- Include tips *from* either the dead or live account.
AND NOT (tippee = %(dead)s OR tippee = %(live)s)
-- Don't include tips *to* the dead or live account,
-- lest we convert cross-tipping to self-tipping.
AND amount > 0
-- Don't include zeroed out tips, so we avoid a no-op
-- zero tip entry.
GROUP BY tippee
"""
ZERO_OUT_OLD_TIPS_RECEIVING = """
INSERT INTO tips (ctime, tipper, tippee, amount)
SELECT ctime, tipper, tippee, 0 AS amount
FROM __temp_unique_tips
WHERE tippee=%s AND amount > 0
"""
ZERO_OUT_OLD_TIPS_GIVING = """
INSERT INTO tips (ctime, tipper, tippee, amount)
SELECT ctime, tipper, tippee, 0 AS amount
FROM __temp_unique_tips
WHERE tipper=%s AND amount > 0
"""
with self.db.get_cursor() as cursor:
# Load the existing connection.
# =============================
# Every account elsewhere has at least a stub participant account
# on Gittip.
rec = cursor.one("""
SELECT participant
, claimed_time IS NULL AS is_stub
FROM elsewhere
JOIN participants ON participant=participants.username
WHERE elsewhere.platform=%s AND elsewhere.user_id=%s
""", (platform, user_id),
default=NotSane)
other_username = rec.participant
if self.username == other_username:
# this is a no op - trying to take over itself
return
# Make sure we have user confirmation if needed.
# ==============================================
# We need confirmation in whatever combination of the following
# three cases:
#
# - the other participant is not a stub; we are taking the
# account elsewhere away from another viable Gittip
# participant
#
# - the other participant has no other accounts elsewhere; taking
# away the account elsewhere will leave the other Gittip
# participant without any means of logging in, and it will be
# archived and its tips absorbed by us
#
# - we already have an account elsewhere connected from the given
# platform, and it will be handed off to a new stub
# participant
# other_is_a_real_participant
other_is_a_real_participant = not rec.is_stub
# this_is_others_last_account_elsewhere
nelsewhere = cursor.one(
"SELECT count(*) FROM elsewhere "
"WHERE participant=%s", (other_username, ))
assert nelsewhere > 0 # sanity check
this_is_others_last_account_elsewhere = (nelsewhere == 1)
# we_already_have_that_kind_of_account
nparticipants = cursor.one(
"SELECT count(*) FROM elsewhere "
"WHERE participant=%s AND platform=%s",
(self.username, platform))
assert nparticipants in (0, 1) # sanity check
we_already_have_that_kind_of_account = nparticipants == 1
need_confirmation = NeedConfirmation(
other_is_a_real_participant,
this_is_others_last_account_elsewhere,
we_already_have_that_kind_of_account)
if need_confirmation and not have_confirmation:
raise need_confirmation
# We have user confirmation. Proceed.
# ===================================
# There is a race condition here. The last person to call this will
# win. XXX: I'm not sure what will happen to the DB and UI for the
# loser.
# Move any old account out of the way.
# ====================================
if we_already_have_that_kind_of_account:
new_stub_username = reserve_a_random_username(cursor)
cursor.run(
"UPDATE elsewhere SET participant=%s "
"WHERE platform=%s AND participant=%s",
(new_stub_username, platform, self.username))
# Do the deal.
# ============
# If other_is_not_a_stub, then other will have the account
# elsewhere taken away from them with this call. If there are other
# browsing sessions open from that account, they will stay open
# until they expire (XXX Is that okay?)
cursor.run(
"UPDATE elsewhere SET participant=%s "
"WHERE platform=%s AND user_id=%s",
(self.username, platform, user_id))
# Fold the old participant into the new as appropriate.
# =====================================================
# We want to do this whether or not other is a stub participant.
if this_is_others_last_account_elsewhere:
# Take over tips.
# ===============
x, y = self.username, other_username
cursor.run(CREATE_TEMP_TABLE_FOR_UNIQUE_TIPS)
cursor.run(CONSOLIDATE_TIPS_RECEIVING, dict(live=x, dead=y))
cursor.run(CONSOLIDATE_TIPS_GIVING, dict(live=x, dead=y))
cursor.run(ZERO_OUT_OLD_TIPS_RECEIVING, (other_username, ))
cursor.run(ZERO_OUT_OLD_TIPS_GIVING, (other_username, ))
# Archive the old participant.
# ============================
# We always give them a new, random username. We sign out
# the old participant.
for archive_username in gen_random_usernames():
try:
username = cursor.one("""
UPDATE participants
SET username=%s
, username_lower=%s
, session_token=NULL
, session_expires=now()
WHERE username=%s
RETURNING username
""", (archive_username, archive_username.lower(),
other_username),
default=NotSane)
except IntegrityError:
continue # archive_username is already taken;
# extremely unlikely, but ...
# XXX But can the UPDATE fail in other ways?
else:
assert username == archive_username
break
# Record the absorption.
# ======================
# This is for preservation of history.
cursor.run(
"INSERT INTO absorptions "
"(absorbed_was, absorbed_by, archived_as) "
"VALUES (%s, %s, %s)",
(other_username, self.username, archive_username))
self.update_avatar()
def with_random_username(cls):
"""Return a new participant with a random username.
"""
with cls.db.get_cursor() as cursor:
username = reserve_a_random_username(cursor)
return cls.from_username(username)
