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 initNoRosterItemsDB():
con = DB(TEST_NOROSTER_NAME)
c = con.cursor()
try:
c.execute("CREATE TABLE jids (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\
jid TEXT NOT NULL,\
password TEXT NOT NULL,\
UNIQUE(jid))")
c.execute("CREATE TABLE roster (userid INTEGER REFERENCES jids NOT NULL,\
contactid INTEGER REFERENCES jids NOT NULL,\
name TEXT,\
subscription INTEGER DEFAULT 0,\
PRIMARY KEY (userid, contactid)\
)")
c.execute("CREATE TABLE rostergroups (groupid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\
userid INTEGER REFERENCES jids NOT NULL,\
name TEXT NOT NULL,\
UNIQUE(userid, name)\
)")
c.execute("CREATE TABLE rostergroupitems\
(groupid INTEGER REFERENCES rostergroup NOT NULL,\
contactid INTEGER REFERENCES jids NOT NULL,\
PRIMARY KEY (groupid, contactid))")
c.execute("INSERT INTO jids (jid, password) VALUES ('bob@localhost', 'test')")
c.execute("INSERT INTO jids (jid, password) VALUES ('alice@localhost', 'test')")
con.commit()
except sqlite.OperationalError, e:
if e.message.find('already exists') >= 0: pass
else: raise
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 populateDB():
"""Creates a sample database"""
con = DB()
c = con.cursor()
try:
c.execute(
"CREATE TABLE jids (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\
jid TEXT NOT NULL,\
password TEXT NOT NULL,\
UNIQUE(jid))")
c.execute(
"CREATE TABLE roster (userid INTEGER REFERENCES jids NOT NULL,\
contactid INTEGER REFERENCES jids NOT NULL,\
name TEXT,\
subscription INTEGER DEFAULT 0,\
PRIMARY KEY (userid, contactid)\
)")
c.execute(
"CREATE TABLE rostergroups (groupid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\
userid INTEGER REFERENCES jids NOT NULL,\
name TEXT NOT NULL,\
UNIQUE(userid, name)\
)")
c.execute("CREATE TABLE rostergroupitems\
(groupid INTEGER REFERENCES rostergroup NOT NULL,\
contactid INTEGER REFERENCES jids NOT NULL,\
PRIMARY KEY (groupid, contactid))")
c.execute(
"INSERT INTO jids (jid, password) VALUES ('tro@localhost', 'test')"
)
c.execute(
"INSERT INTO jids (jid, password) VALUES ('dv@localhost', 'test')")
c.execute(
"INSERT INTO jids (jid, password) VALUES ('bob@localhost', 'test')"
)
c.execute(
"INSERT INTO jids (jid, password) VALUES ('alice@localhost', 'test')"
)
con.commit()
# c.execute("INSERT INTO roster (userid, contactid, subscription) VALUES (1, 2, 8)")
# c.execute("INSERT INTO roster (userid, contactid, subscription) VALUES (2, 1, 8)")
# c.execute("INSERT INTO rostergroups (userid, name) VALUES (1, 'friends')")
# c.execute("INSERT INTO rostergroups (userid, name) VALUES (1, 'weirdos')")
# c.execute("INSERT INTO rostergroupitems (groupid, contactid) VALUES (1, 2)")
except sqlite.OperationalError, e:
if e.message.find('already exists') >= 0: pass
else: raise
def populateDB():
"""Creates a sample database"""
con = DB()
c = con.cursor()
try:
c.execute("CREATE TABLE IF NOT EXISTS jids (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\
jid TEXT NOT NULL,\
password TEXT NOT NULL,\
UNIQUE(jid))")
c.execute("CREATE TABLE IF NOT EXISTS roster (userid INTEGER REFERENCES jids NOT NULL,\
contactid INTEGER REFERENCES jids NOT NULL,\
name TEXT,\
subscription INTEGER DEFAULT 0,\
PRIMARY KEY (userid, contactid)\
)")
c.execute("CREATE TABLE IF NOT EXISTS offline (fromid INTEGER REFERENCES jids NOT NULL,\
toid INTEGER REFERENCES jids NOT NULL,\
time TIMESTAMP,\
content TEXT\
)")
c.execute("CREATE TABLE IF NOT EXISTS rostergroups (groupid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\
userid INTEGER REFERENCES jids NOT NULL,\
name TEXT NOT NULL,\
UNIQUE(userid, name)\
)")
c.execute("CREATE TABLE IF NOT EXISTS rostergroupitems\
(groupid INTEGER REFERENCES rostergroup NOT NULL,\
contactid INTEGER REFERENCES jids NOT NULL,\
PRIMARY KEY (groupid, contactid))")
c.execute("INSERT OR IGNORE INTO jids (jid, password) VALUES ('foo@localhost', 'foo')")
c.execute("INSERT OR IGNORE INTO jids (jid, password) VALUES ('bar@localhost', 'bar')")
c.execute("INSERT OR IGNORE INTO jids (jid, password) VALUES ('test@localhost', 'test')")
c.execute("INSERT OR IGNORE INTO jids (jid, password) VALUES ('admin@localhost', 'admin')")
con.commit()
# c.execute("INSERT INTO roster (userid, contactid, subscription) VALUES (1, 2, 8)")
# c.execute("INSERT INTO roster (userid, contactid, subscription) VALUES (2, 1, 8)")
# c.execute("INSERT INTO rostergroups (userid, name) VALUES (1, 'friends')")
# c.execute("INSERT INTO rostergroups (userid, name) VALUES (1, 'weirdos')")
# c.execute("INSERT INTO rostergroupitems (groupid, contactid) VALUES (1, 2)")
except sqlite.OperationalError, e:
if e.message.find('already exists') >= 0: pass
else: raise e
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 handle(self, tree, msg, lastRetVal=None):
# generate error response tree
def get_error_tree(iq, type, code, tag):
result = Element('iq', {'type': 'error', 'id': iq.get('id')})
result.append(iq)
err_tree = Element('error', {'type': type, 'code': code,})
SubElement(err_tree, tag, {'xmlns': 'urn:ietf:params:xml:ns:xmpp-stanzas'})
result.append(err_tree)
return result
if len(tree) > 0:
# get the original iq msg
origIQ = tree
else:
logging.warning("[%s] Original <iq> missing:\n%s",
self.__class__, tostring(tree))
return lastRetVal
id = origIQ.get('id')
if id:
try:
username_tree = origIQ[0][0]
password_tree = origIQ[0][1]
if username_tree.tag == '{jabber:iq:register}username' and password_tree.tag == '{jabber:iq:register}password':
username = username_tree.text
password = password_tree.text
# ruling out illegal username
if not re.match("^[a-zA-Z0-9_.-]+$", username):
raise Exception('Username not accepted')
# write to database
try:
con = DB()
with closing(con.cursor()) as cursor:
cursor.execute("INSERT INTO jids (jid, password) VALUES ('%s@%s', '%s')" %
(username, msg.conn.server.hostname, password))
con.commit()
res = Element('iq', {'type': 'result', 'id': id})
query = deepcopy(origIQ[0])
query.insert(0, Element('registered'))
res.append(query)
return chainOutput(lastRetVal, res)
# conflict response
except sqlite.IntegrityError as e:
if e.message.find('column jid is not unique') >= 0:
logging.warning("[%s] Username conflict in <iq>:\n%s",
self.__class__, str(e), tostring(origIQ))
res = get_error_tree(origIQ, 'cancel', '409', 'conflict')
return chainOutput(lastRetVal, res)
else:
raise e
else:
raise Exception('IQ missing registration fields')
# error response
except Exception as e:
error_string = str(e)
logging.warning("[%s] Register failed '%s' in <iq>:\n%s",
self.__class__, str(e) if error_string else 'Unknown error',
tostring(origIQ))
res = get_error_tree(origIQ, 'modify', '406', 'not-acceptable')
return chainOutput(lastRetVal, res)
else:
logging.warning("[%s] No id in <iq>:\n%s",
self.__class__, tostring(origIQ))
return lastRetVal
def act():
d = msg.conn.data
retVal = lastRetVal
jid = d['user']['jid']
resource = d['user']['resource']
roster = Roster(jid)
presTree = deepcopy(tree)
presTree.set('from', '%s/%s' % (jid, resource))
probes = []
init_rosters = []
offline_msgs = []
if tree.get('to') is None and not d['user']['active']:
# initial presence
# TODO: we don't need to do it every time. we can cache the
# data after the first resource is active and just resend
# that to all new resources
d['user']['active'] = True
# get jids of the contacts whose status we're interested in
cjids = roster.getPresenceSubscriptions()
probeTree = Element('presence', {
'type': 'probe',
'from' : '%s/%s' \
% (jid, resource)
})
# TODO: replace this with a more efficient router handler
for cjid in cjids:
probeTree.set('to', cjid)
probeRouteData = {
'to' : cjid,
'data' : deepcopy(probeTree)
}
probes.append(probeRouteData)
# they're sent first. see below
# send initial roster list to this user
rosterTree = Element('presence', {
'type': 'unavailable',
'to' : '%s/%s' \
% (jid, resource)
})
for cjid in cjids:
rosterTree.set('from', cjid)
rosterRouterData = {
'to' : '%s/%s' % (jid, resource),
'data' : deepcopy(rosterTree)
}
init_rosters.append(rosterRouterData)
# send offline message to this user
try:
con = DB()
result = []
to_jid = JID(jid)
with closing(con.cursor()) as cursor:
cursor.execute("SELECT fromid, time, content FROM offline WHERE toid = %d ORDER BY time DESC" %
(to_jid.getNumId()))
con.commit()
result = cursor.fetchall()
with closing(con.cursor()) as cursor:
cursor.execute("DELETE FROM offline WHERE toid = %d" %
(to_jid.getNumId()))
con.commit()
for fromid, time, content in result:
fromJID = JID(fromid, True).getBare()
toJID = '%s/%s' % (jid, resource)
reply = Element('message', {
'to': toJID,
'from': fromJID,
'type': 'chat'
})
body = Element('body')
body.text = content
reply.append(body)
delay = Element('delay', {
'xmlns': 'urn:xmpp:delay',
'from': fromJID,
'stamp': time.strftime("%Y-%m-%dT%H:%M:%SZ")
})
reply.append(delay)
routeData = {
'to' : toJID,
'data': reply
}
offline_msgs.append(routeData)
logging.debug("[%s] Sending %d offline messages to %s", self.__class__, len(offline_msgs), to_jid.getBare())
except Exception as e:
logging.warning("[%s] Failed to read offline messages: %s", self.__class__, str(e))
# broadcast to other resources of this user
retVal = self.broadcastToOtherResources(presTree, msg, retVal, jid, resource)
elif tree.get('to') is not None:
# TODO: directed presence
return
elif tree.get('type') == 'unavailable':
# broadcast to other resources of this user
d['user']['active'] = False
retVal = self.broadcastToOtherResources(presTree, msg, retVal, jid, resource)
# record this stanza as the last presence sent from this client
lastPresence = deepcopy(tree)
lastPresence.set('from', '%s/%s' % (jid, resource))
d['user']['lastPresence'] = lastPresence
# lookup contacts interested in presence
cjids = roster.getPresenceSubscribers()
# TODO: replace this with another router handler that would send
# it out to all cjids in a batch instead of queuing a handler
# for each
for cjid in cjids:
presTree.set('to', cjid)
presRouteData = {
'to' : cjid,
'data' : deepcopy(presTree)
}
retVal = chainOutput(retVal, presRouteData)
msg.setNextHandler('route-server')
# send the probes first
for probe in probes:
msg.setNextHandler('route-server')
retVal = chainOutput(retVal, probe)
# send initial rosters
for init_roster in init_rosters:
msg.setNextHandler('route-client')
retVal = chainOutput(retVal, init_roster)
# send offline messages
for offline_msg in offline_msgs:
msg.setNextHandler('route-client')
retVal = chainOutput(retVal, offline_msg)
return retVal
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
