def loadRoster(self):
"""Loads the roster for this JID. Must be used before calling
getAsTree().
"""
con = DB()
c = con.cursor()
# get the contactid, name and subscriptions
c.execute("SELECT roster.contactid, roster.name,\
roster.subscription,\
contactjids.jid cjid\
FROM roster\
JOIN jids AS userjids ON roster.userid = userjids.id\
JOIN jids AS contactjids ON roster.contactid = contactjids.id\
WHERE userjids.jid = ? AND\
roster.subscription != ?",
(self.jid, Subscription.NONE_PENDING_IN))
self.items = {}
for row in c:
self.addItem(row['contactid'],
RosterItem(row['cjid'], row['name'], row['subscription']))
# get the groups now for each cid
c.execute("SELECT rgi.contactid, rgs.name\
FROM rostergroups AS rgs\
JOIN rostergroupitems AS rgi ON rgi.groupid = rgs.groupid\
JOIN jids ON rgs.userid = jids.id\
WHERE jids.jid = ?", (self.jid,))
for row in c:
self.addGroup(row['contactid'], row['name'])
commitSQLiteTransaction(con, c)
def setSubscription(self, cid, sub):
"""Sets the subscription from the perspective of this user to a
contact with ID cid to sub, which is an id retrieved via the
Subscription class.
"""
con = DB()
c = con.cursor()
c.execute("UPDATE roster SET subscription = ?\
WHERE userid = ? AND contactid = ?", (sub, self.uid, cid))
commitSQLiteTransaction(con, c)
def setSubscription(self, cid, sub):
"""Sets the subscription from the perspective of this user to a
contact with ID cid to sub, which is an id retrieved via the
Subscription class.
"""
con = DB()
c = con.cursor()
c.execute(
"UPDATE roster SET subscription = ?\
WHERE userid = ? AND contactid = ?", (sub, self.uid, cid))
commitSQLiteTransaction(con, c)
def removeContact(self, cjid):
"""Removes the contact from this user's roster. Returns the contact's
id in the DB.
cjid -- bare JID or the contact as a string.
"""
con = DB()
c = con.cursor()
# get the contact's id
c.execute(
"SELECT jids.id\
FROM roster\
JOIN jids ON roster.contactid = jids.id\
WHERE roster.userid = ? AND jids.jid = ?", (self.uid, cjid))
res = c.fetchone()
if res:
cid = res[0]
else:
logging.info("[%s] Contact %s does not exist in roster of %s",
self.__class__, cjid, self.jid)
commitSQLiteTransaction(con, c)
con.close()
return False
# delete the contact from all groups it's in for this user
c.execute(
"DELETE FROM rostergroupitems\
WHERE rostergroupitems.groupid IN (\
SELECT rgs.groupid FROM rostergroups AS rgs\
JOIN rostergroupitems AS rgi ON rgi.groupid = rgs.groupid\
WHERE rgs.userid = ?\
) AND rostergroupitems.contactid = ?", (self.uid, cid))
# now delete the roster entry
c.execute(
"DELETE FROM roster\
WHERE userid = ? AND contactid = ?", (self.uid, cid))
commitSQLiteTransaction(con, c)
return cid
def removeContact(self, cjid):
"""Removes the contact from this user's roster. Returns the contact's
id in the DB.
cjid -- bare JID or the contact as a string.
"""
con = DB()
c = con.cursor()
# get the contact's id
c.execute("SELECT jids.id\
FROM roster\
JOIN jids ON roster.contactid = jids.id\
WHERE roster.userid = ? AND jids.jid = ?", (self.uid, cjid))
res = c.fetchone()
if res:
cid = res[0]
else:
logging.info("[%s] Contact %s does not exist in roster of %s",
self.__class__, cjid, self.jid)
commitSQLiteTransaction(con, c)
con.close()
return False
# delete the contact from all groups it's in for this user
c.execute("DELETE FROM rostergroupitems\
WHERE rostergroupitems.groupid IN (\
SELECT rgs.groupid FROM rostergroups AS rgs\
JOIN rostergroupitems AS rgi ON rgi.groupid = rgs.groupid\
WHERE rgs.userid = ?\
) AND rostergroupitems.contactid = ?", (self.uid, cid))
# now delete the roster entry
c.execute("DELETE FROM roster\
WHERE userid = ? AND contactid = ?", (self.uid, cid))
commitSQLiteTransaction(con, c)
return cid
def loadRoster(self):
"""Loads the roster for this JID. Must be used before calling
getAsTree().
"""
con = DB()
c = con.cursor()
# get the contactid, name and subscriptions
c.execute(
"SELECT roster.contactid, roster.name,\
roster.subscription,\
contactjids.jid cjid\
FROM roster\
JOIN jids AS userjids ON roster.userid = userjids.id\
JOIN jids AS contactjids ON roster.contactid = contactjids.id\
WHERE userjids.jid = ? AND\
roster.subscription != ?",
(self.jid, Subscription.NONE_PENDING_IN))
self.items = {}
for row in c:
self.addItem(
row['contactid'],
RosterItem(row['cjid'], row['name'], row['subscription']))
# get the groups now for each cid
c.execute(
"SELECT rgi.contactid, rgs.name\
FROM rostergroups AS rgs\
JOIN rostergroupitems AS rgi ON rgi.groupid = rgs.groupid\
JOIN jids ON rgs.userid = jids.id\
WHERE jids.jid = ?", (self.jid, ))
for row in c:
self.addGroup(row['contactid'], row['name'])
commitSQLiteTransaction(con, c)
def updateContact(self, cjid, groups=None, name=None, subscriptionId=None):
"""Adds or updates a contact in this user's roster. Returns the
contact's id in the DB.
groups can be None, which means that all groups are to be removed
Otherwise, groups is a list of groups the contact belongs to.
"""
name = name or ''
groups = groups or []
con = DB()
c = con.cursor()
# check if this is an update to an existing roster entry
c.execute(
"SELECT cjids.id cid \
FROM roster\
JOIN jids AS cjids ON cjids.id = roster.contactid\
JOIN jids AS ujids ON ujids.id = roster.userid\
WHERE ujids.jid = ? AND cjids.jid = ?", (self.jid, cjid))
res = c.fetchone()
if res:
# this is an update
# we update the subscription if it's given to us; o/w
# just update the name
cid = res[0]
if subscriptionId:
c.execute(
"UPDATE roster SET name = ?, subscription = ?\
WHERE contactid = ?", (name, cid, subscriptionId))
else:
c.execute(
"UPDATE roster SET name = ?\
WHERE contactid = ?", (name, cid))
else:
# this is a new roster entry
# check if the contact JID already exists in our DB
c.execute("SELECT id FROM jids WHERE jid = ?", (cjid, ))
res = c.fetchone()
if res:
cid = res[0]
else:
# create new JID entry
res = c.execute(
"INSERT INTO jids\
(jid, password)\
VALUES\
(?, '')", (cjid, ))
cid = res.lastrowid
c.execute(
"INSERT INTO roster\
(userid, contactid, name, subscription)\
VALUES\
(?, ?, ?, ?)",
(self.uid, cid, name, subscriptionId or Subscription.NONE))
# UPDATE GROUPS
# remove all group mappings for this contact and recreate
# them, since it's easier than figuring out what changed
c.execute(
"DELETE FROM rostergroupitems\
WHERE contactid = ? AND groupid IN\
(SELECT groupid FROM rostergroups WHERE\
userid = ?)", (cid, self.uid))
for groupName in groups:
# get the group id
c.execute(
"SELECT groupid\
FROM rostergroups\
WHERE userid = ? AND name = ?", (self.uid, groupName))
res = c.fetchone()
if res:
gid = res[0]
else:
# need to create the group
res = c.execute(
"INSERT INTO rostergroups\
(userid, name)\
VALUES\
(?, ?)", (self.uid, groupName))
gid = res.lastrowid
c.execute(
"INSERT INTO rostergroupitems\
(groupid, contactid)\
VALUES\
(?, ?)", (gid, cid))
commitSQLiteTransaction(con, c)
return cid
def updateContact(self, cjid, groups=None, name=None, subscriptionId=None):
"""Adds or updates a contact in this user's roster. Returns the
contact's id in the DB.
groups can be None, which means that all groups are to be removed
Otherwise, groups is a list of groups the contact belongs to.
"""
name = name or ''
groups = groups or []
con = DB()
c = con.cursor()
# check if this is an update to an existing roster entry
c.execute("SELECT cjids.id cid \
FROM roster\
JOIN jids AS cjids ON cjids.id = roster.contactid\
JOIN jids AS ujids ON ujids.id = roster.userid\
WHERE ujids.jid = ? AND cjids.jid = ?", (self.jid, cjid))
res = c.fetchone()
if res:
# this is an update
# we update the subscription if it's given to us; o/w
# just update the name
cid = res[0]
if subscriptionId:
c.execute("UPDATE roster SET name = ?, subscription = ?\
WHERE contactid = ?",
(name, cid, subscriptionId))
else:
c.execute("UPDATE roster SET name = ?\
WHERE contactid = ?",
(name, cid))
else:
# this is a new roster entry
# check if the contact JID already exists in our DB
c.execute("SELECT id FROM jids WHERE jid = ?", (cjid,))
res = c.fetchone()
if res:
cid = res[0]
else:
# create new JID entry
res = c.execute("INSERT INTO jids\
(jid, password)\
VALUES\
(?, '')", (cjid,))
cid = res.lastrowid
c.execute("INSERT INTO roster\
(userid, contactid, name, subscription)\
VALUES\
(?, ?, ?, ?)",
(self.uid, cid, name,
subscriptionId or Subscription.NONE))
# UPDATE GROUPS
# remove all group mappings for this contact and recreate
# them, since it's easier than figuring out what changed
c.execute("DELETE FROM rostergroupitems\
WHERE contactid = ? AND groupid IN\
(SELECT groupid FROM rostergroups WHERE\
userid = ?)", (cid, self.uid))
for groupName in groups:
# get the group id
c.execute("SELECT groupid\
FROM rostergroups\
WHERE userid = ? AND name = ?", (self.uid, groupName))
res = c.fetchone()
if res:
gid = res[0]
else:
# need to create the group
res = c.execute("INSERT INTO rostergroups\
(userid, name)\
VALUES\
(?, ?)", (self.uid, groupName))
gid = res.lastrowid
c.execute("INSERT INTO rostergroupitems\
(groupid, contactid)\
VALUES\
(?, ?)", (gid, cid))
commitSQLiteTransaction(con, c)
return cid
