[Systers-dev] Moving from Pickle Membership to Store in the Postgresql Database

[Robin Jeffries]

On Sat, Jun 5, 2010 at 1:22 PM, Malveeka Tewari <malveeka at gmail.com> wrote:

> It's highly possible that we just need a new table instead of an entire
> database
>
> I'll take a look at the wiki and then confirm
>
> >From the pickle files, we need the user memberhship information -
> username,
> email address for subscription, password, all lists that the user is member
> of, user options and bounce info .
>

The primary email address is the user name (and we keep both the primary and
secondary emails in that database)
I think the password is there too.
It doesn't have the user options or any syncing with other lists (each list
has its own database tables).

I'm not sure what you mean by bounce info -- how many times messages have
bounced?  That's not there.

I think the biggest rethinking is that now we treat all email lists
separately.  How would the schemas have to change (and what would have to
change outside the single signon) to have one master copy.

I don't understand why you need to have the user options here -- people will
want to have different options for different lists, and that information
really belongs to mailman (you don't want to have to change the database for
each new application you add).

And I think the bounce information (if I'm understanding it correctly) needs
some serious thinking through.  Different applications may have different
ways they count bounces, so you may not be able to have a single "if it
bounces this many times, drop them" metric.  And if someone belongs to 10
systers.org lists, and the system sends out password reminders for each list
(or something similar) and they all bounce due to a temporary problem, we
wouldn't want to count that as 10 bounces.  So I think you need to think
through the various pros and cons of keeping bounce information in a central
place vs. for each list.

An alternative way to do this would be to just have a central table of
username (primary email) and password, and interpose for each app a method
where you send the single signon module the login credentials and it sends
you back valid/invalid.  That seems like a lot less work; what would be the
value of the additional things you are adding here?

And how does your database interact with the MM3.0 database?

(What my colleagues say about me: Never ask Robin a question.  You will get
10 questions back before you get an answer to yours :-)

Robin


>
> Thanks
> Malveeka
>
> On Sat, Jun 5, 2010 at 12:38 PM, Robin Jeffries <robin at jeffries.org>
> wrote:
>
> > But we already have the postgres database for the dlists.
> >
> > First, what do you need that isn't already in that database? (I think
> that
> > there is a diagram of the database tables on the systers wiki.  Anna made
> > something last summer, and I think that's where it ended up)  You will
> > certainly need to add information of your own, but that could just be an
> > additional table in that database.
> >
> > What's in the pickle files that you need?  You don't want everything
> there,
> > I assume.
> >
> > Second, if you do need a new database, we should start some communication
> > among the archives folks (Pinar is probably your starting contact), the
> > MM3.0 (Anna) and anyone else who is working on a database issue.  We will
> > need to do these independently for the summer, but we ought to be talking
> to
> > one another and understanding how to merge them, especially overlapping
> > information.  We don't want to have to update a user email address in 4
> > different places.
> >
> > Robin
> >
> > On Sat, Jun 5, 2010 at 12:31 PM, Malveeka Tewari <malveeka at gmail.com
> >wrote:
> >
> >> Hi Robin,
> >>
> >> For the single sign on project - we need to maintain the user membership
> >> info (including passwords, mail delivery options etc. ) in a database
> that
> >> can be accessed by the OpenID relay to enable easy password lookup for
> >> single sign on.
> >>
> >> Currently in MM 2.x all this data is stored in pickle files as python
> >> pickled objects. Storing this information in a separate db will make the
> >> Single Sign On code more modular and extensble.
> >>
> >> For now, we have code working that when a new list and new members are
> >> added
> >> the use data gets stored into the membership_database. However, we
> haven't
> >> written code to populate the database with information of existing
> users.
> >> This is not a critical step - we can also do it after we get the OpenID
> >> relay working for membership info for new members as well but this is
> >> something that will eventually need to be done if want existing Systers
> >> accounts to benefit from Single Sign On.
> >>
> >> We don't know of a good way to move the existing user info to the
> >> membership
> >> database, for now I've suggested Jaideep to write a python script
> that'll
> >> parse the .pck files and populate the database.
> >>
> >> (We probably will not need this  once we move to MM 3 because from
> Anna's
> >> discussions on MM - dev list,  I understand MM 3 will itself be storing
> >> information in a database - however it's not clear to me how Syster's
> will
> >> migrate from MM 2.x to MM 3 and so I've suggested Jaideep to get Single
> >> Sign
> >> on working for MM 2.x for now after storing user info in a separate
> >> database. After that most of the development will involve getting OpenID
> >> to
> >> work with the membership database independent of the MM version)
> >>
> >> We could do with some help on how to do this more efficiently or
> pointers
> >> to
> >> existing code that already does that
> >>
> >> Thanks!
> >> Malveeka
> >>
> >>
> >>
> >>
> >>
> >> On Sat, Jun 5, 2010 at 12:01 PM, Robin Jeffries <robin at jeffries.org>
> >> wrote:
> >>
> >> > Can you tell us why you are doing this?
> >> >
> >> > Robin
> >> >
> >> > On Fri, Jun 4, 2010 at 11:46 PM, Jaideep Khandelwal <
> jdk2588 at gmail.com
> >> > >wrote:
> >> >
> >> > > Hi,
> >> > > I have been working for moving the User Membership from the Pickle
> >> files
> >> > to
> >> > > the  Database in the Postgresql server, The mailman provides an
> option
> >> of
> >> > > dumping database with the use of the binary "dumpdb" that helps in
> >> > storing
> >> > > the data, now I seek some help in moving old list members
> information
> >> > into
> >> > > a
> >> > > database . Is there any existing method for moving the Pickle data
> >> file
> >> > > into
> >> > > an Database server.
> >> > >
> >> > > Thanks
> >> > >
> >> > > Regards
> >> > > Jaideep
> >> > >
> >> > >
> >> > > To unsubscribe from this conversation, send email to <
> >> > > systers-dev+mentor+unsubscribe at systers.org<systers-dev%2Bmentor%2Bunsubscribe at systers.org>
> <systers-dev%2Bmentor%2Bunsubscribe at systers.org<systers-dev%252Bmentor%252Bunsubscribe at systers.org>
> >
> >> <systers-dev%2Bmentor%2Bunsubscribe at systers.org<systers-dev%252Bmentor%252Bunsubscribe at systers.org>
> <systers-dev%252Bmentor%252Bunsubscribe at systers.org<systers-dev%25252Bmentor%25252Bunsubscribe at systers.org>
> >
> >> >
> >> > <systers-dev%2Bmentor%2Bunsubscribe at systers.org<systers-dev%252Bmentor%252Bunsubscribe at systers.org>
> <systers-dev%252Bmentor%252Bunsubscribe at systers.org<systers-dev%25252Bmentor%25252Bunsubscribe at systers.org>
> >
> >> <systers-dev%252Bmentor%252Bunsubscribe at systers.org<systers-dev%25252Bmentor%25252Bunsubscribe at systers.org>
> <systers-dev%25252Bmentor%25252Bunsubscribe at systers.org<systers-dev%2525252Bmentor%2525252Bunsubscribe at systers.org>
> >
> >> >
> >>
> >> > >>
> >> > > or visit <
> >> > >
> >> http://systers.org/mailman/options/systers-dev?override=67&preference=0
> >
> >> > > To contribute to this conversation, use your mailer's reply-all or
> >> > > reply-group command or send your message to
> >> > systers-dev+mentor at systers.org <systers-dev%2Bmentor at systers.org> <
> systers-dev%2Bmentor at systers.org <systers-dev%252Bmentor at systers.org>> <
> >> systers-dev%2Bmentor at systers.org <systers-dev%252Bmentor at systers.org> <
> systers-dev%252Bmentor at systers.org <systers-dev%25252Bmentor at systers.org>
> >><
> >> > systers-dev%2Bmentor at systers.org <systers-dev%252Bmentor at systers.org><
> systers-dev%252Bmentor at systers.org <systers-dev%25252Bmentor at systers.org>>
> <
> >> systers-dev%252Bmentor at systers.org<systers-dev%25252Bmentor at systers.org><
> systers-dev%25252Bmentor at systers.org<systers-dev%2525252Bmentor at systers.org>
> >
> >> >>
> >>
> >> > > To start a new conversation, send email to <
> >> systers-dev+new at systers.org <systers-dev%2Bnew at systers.org> <
> systers-dev%2Bnew at systers.org <systers-dev%252Bnew at systers.org>><
> >> systers-dev%2Bnew at systers.org <systers-dev%252Bnew at systers.org> <
> systers-dev%252Bnew at systers.org <systers-dev%25252Bnew at systers.org>>>
> >> > <systers-dev%2Bnew at systers.org <systers-dev%252Bnew at systers.org> <
> systers-dev%252Bnew at systers.org <systers-dev%25252Bnew at systers.org>> <
> >> systers-dev%252Bnew at systers.org <systers-dev%25252Bnew at systers.org> <
> systers-dev%25252Bnew at systers.org <systers-dev%2525252Bnew at systers.org>>>>
> >>
> >> > > >
> >> > > To unsubscribe entirely from systers-dev, send email to <
> >> > > systers-dev-request at systers.org> with subject unsubscribe.
> >> > >
> >> >
> >> >
> >> > To unsubscribe from this conversation, send email to <
> >> > systers-dev+mentor+unsubscribe at systers.org<systers-dev%2Bmentor%2Bunsubscribe at systers.org>
> <systers-dev%2Bmentor%2Bunsubscribe at systers.org<systers-dev%252Bmentor%252Bunsubscribe at systers.org>
> >
> >> <systers-dev%2Bmentor%2Bunsubscribe at systers.org<systers-dev%252Bmentor%252Bunsubscribe at systers.org>
> <systers-dev%252Bmentor%252Bunsubscribe at systers.org<systers-dev%25252Bmentor%25252Bunsubscribe at systers.org>
> >
> >> >>
> >> > or visit <
> >> >
> http://systers.org/mailman/options/systers-dev?override=67&preference=0
> >> >
> >> > To contribute to this conversation, use your mailer's reply-all or
> >> > reply-group command or send your message to
> >> systers-dev+mentor at systers.org <systers-dev%2Bmentor at systers.org> <
> systers-dev%2Bmentor at systers.org <systers-dev%252Bmentor at systers.org>><
> >> systers-dev%2Bmentor at systers.org <systers-dev%252Bmentor at systers.org> <
> systers-dev%252Bmentor at systers.org <systers-dev%25252Bmentor at systers.org>
> >>
> >> > To start a new conversation, send email to <
> systers-dev+new at systers.org <systers-dev%2Bnew at systers.org><
> systers-dev%2Bnew at systers.org <systers-dev%252Bnew at systers.org>>
> >> <systers-dev%2Bnew at systers.org <systers-dev%252Bnew at systers.org> <
> systers-dev%252Bnew at systers.org <systers-dev%25252Bnew at systers.org>>>
> >> > >
> >> > To unsubscribe entirely from systers-dev, send email to <
> >> > systers-dev-request at systers.org> with subject unsubscribe.
> >> >
> >>
> >>
> >> To unsubscribe from this conversation, send email to <
> >> systers-dev+mentor+unsubscribe at systers.org<systers-dev%2Bmentor%2Bunsubscribe at systers.org>
> <systers-dev%2Bmentor%2Bunsubscribe at systers.org<systers-dev%252Bmentor%252Bunsubscribe at systers.org>
> >>
> >> or visit <
> >> http://systers.org/mailman/options/systers-dev?override=67&preference=0
> >
> >> To contribute to this conversation, use your mailer's reply-all or
> >> reply-group command or send your message to
> >> systers-dev+mentor at systers.org <systers-dev%2Bmentor at systers.org> <
> systers-dev%2Bmentor at systers.org <systers-dev%252Bmentor at systers.org>>
> >> To start a new conversation, send email to <systers-dev+new at systers.org<systers-dev%2Bnew at systers.org>
> <systers-dev%2Bnew at systers.org <systers-dev%252Bnew at systers.org>>
> >> >
> >> To unsubscribe entirely from systers-dev, send email to <
> >> systers-dev-request at systers.org> with subject unsubscribe.
> >>
> >
> >
>
>
> To unsubscribe from this conversation, send email to <
> systers-dev+mentor+unsubscribe at systers.org<systers-dev%2Bmentor%2Bunsubscribe at systers.org>>
> or visit <
> http://systers.org/mailman/options/systers-dev?override=67&preference=0>
> To contribute to this conversation, use your mailer's reply-all or
> reply-group command or send your message to systers-dev+mentor at systers.org<systers-dev%2Bmentor at systers.org>
> To start a new conversation, send email to <systers-dev+new at systers.org<systers-dev%2Bnew at systers.org>
> >
> To unsubscribe entirely from systers-dev, send email to <
> systers-dev-request at systers.org> with subject unsubscribe.
>