コード例 #1
0
ファイル: roster.py プロジェクト: Kiesco08/pjabberd
    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)
コード例 #2
0
ファイル: test_xmpp.py プロジェクト: stack-coder/pjabberd
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
コード例 #3
0
ファイル: roster.py プロジェクト: Kiesco08/pjabberd
 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)
コード例 #4
0
 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)
コード例 #5
0
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
コード例 #6
0
ファイル: pjabberd.py プロジェクト: linusyang/pjabberd
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
コード例 #7
0
    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
コード例 #8
0
ファイル: roster.py プロジェクト: Kiesco08/pjabberd
    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
コード例 #9
0
    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)
コード例 #10
0
ファイル: iq.py プロジェクト: linusyang/pjabberd
    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
コード例 #11
0
ファイル: presence.py プロジェクト: linusyang/pjabberd
        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
コード例 #12
0
    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
コード例 #13
0
ファイル: roster.py プロジェクト: Kiesco08/pjabberd
    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