Subscription status table

Brad Fitzpatrick brad at danga.com
Fri Jul 7 23:53:45 UTC 2006


Inserting NULL allocates the next auto-increment value.


On Fri, 7 Jul 2006, Guillermo Roditi wrote:

> I can't seem to wrap my head around some code in _groupid_alloc in
> RosterStorage::SQLite (function / table definition below for
> convenience)
>
> It seems _groupid_alloc checks whether $userid already has a roster
> group named $name. and if not it creates it. This all makes sense
> except for the fact that groupid, the primary key seems to reference
> jidmap and is NOT NULL, so I dont get how creating it and INSERT ing
> groupid as NULL would work. WOuldnt you have to allocate a jidid first
> and then insert with that jidid as the groupid ?
>
> This whole setup has me very confused because users, contacts and
> rostergroups all  seem to be a jidmap object so i cant really wrap my
> head around what exactly goes in jidmap.jid. it would make sense for
> it to be a stringified jid, but what about groups?
>
> I am trying to clone the existing setup right now using DBIC before I
> extend it to meet my system and I'm already stuck. Ist here something
> I am missing here?
>
>
>
> CREATE TABLE rostergroup (
>               groupid       INTEGER PRIMARY KEY REFERENCES jidmap NOT NULL,
>               userid        INTEGER REFERENCES jidmap NOT NULL,
>               name          VARCHAR(255),
>               UNIQUE (userid, name)
>   )
>
> # to be called outside of a transaction, in auto-commit mode
> sub _groupid_alloc {
>     my ($self, $userid, $name) = @_;
>     my $dbh  = $self->{dbh};
>     my $id   = eval {
>         $dbh->selectrow_array("SELECT groupid FROM rostergroup WHERE
> userid=? AND name=?",
>                               undef, $userid, $name);
>     };
>     $logger->logdie("Failed to select from groupid: $@") if $@;
>     return $id if $id;
>
>     eval {
>         $dbh->do("INSERT INTO rostergroup (groupid, userid, name)
> VALUES (NULL, ?, ?)",
>                  undef, $userid, $name);
>     };
>     $logger->logdie("_groupid_alloc failed: $@") if $@;
>
>     $id = $dbh->last_insert_id(undef, undef, "rostergroup", "groupid")
>         or $logger->logdie("Failed to allocate a number in _groupid_alloc");
>
>     return $id;
> }
>
> On 7/7/06, Brad Fitzpatrick <brad at danga.com> wrote:
> > substates doesn't exist as a table, but basically there are 4 subscription
> > status (state) flags on a roster group item:
> >
> >    in
> >    out
> >    pend_in
> >    pent_out
> >
> > Those can be collapsed into a number, which is what
> > DJabberd::Subscription.pm does.  (object <-> number <-> object)
> >
> > In your rosterstorage class, just store the number provided to you, and
> > provide it back, and DJabberd will do the right thing.
> >
> >
> >
> > On Fri, 7 Jul 2006, Guillermo Roditi wrote:
> >
> > > I am trying to write a wroster class and I was looking in sqllite
> > > roster storage. i came accross this, which im still wrapping my head
> > > around since everything seems to be a jidmap entry. anyways, i guess
> > > my question boils down to, what the hell is substates and where is the
> > > definition for it?
> > >
> > > i just ran accross hookdocs.pm which is making my life a lot less
> > > miserable, but this one is still outstanding
> > >
> > > CREATE TABLE jidmap (
> > >               jidid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
> > >               jid   VARCHAR(255) NOT NULL,
> > >               UNIQUE (jid)
> > >              )
> > >
> > > CREATE TABLE roster (
> > >               userid        INTEGER REFERENCES jidmap NOT NULL,
> > >               contactid     INTEGER REFERENCES jidmap NOT NULL,
> > >               name          VARCHAR(255),
> > >               subscription  INTEGER NOT NULL REFERENCES substates DEFAULT 0,
> > >               PRIMARY KEY (userid, contactid)
> > >              )
> > >
> > > CREATE TABLE rostergroup (
> > >               groupid       INTEGER PRIMARY KEY REFERENCES jidmap NOT NULL,
> > >               userid        INTEGER REFERENCES jidmap NOT NULL,
> > >               name          VARCHAR(255),
> > >               UNIQUE (userid, name)
> > >              )
> > >
> > > CREATE TABLE groupitem (
> > >               groupid       INTEGER REFERENCES jidmap NOT NULL,
> > >               contactid     INTEGER REFERENCES jidmap NOT NULL,
> > >               PRIMARY KEY (groupid, contactid)
> > >              )
> > >
> > >
> >
>
>


More information about the Djabberd mailing list