For those who are interested in working on a MySQL and/or Postgres backend end for Mailman 3, I'd like to get some feedback on *very basic* notions. First, for me, the whole reason I want to see this SQL thing happen is so that I can have the three primary data tables at my "SQL select" disposal so that I can build Neat Things. Those data tables being 1) list configuration data, 2) member data, and 3) message archives.
What kind of Neat Things require this, you ask? Well, let's say I wanna build this wonderful tool called "EZ-Groups." It has all sorts of great stuff: list info w/ archives, user profiles, documents, a calendar. You name it. All the stuff I want to have to build my happy communities. I wanna log into my EZ-Groups web site, which is powered by Mailman 3, of course. And once I log in, on the left hand side I want to see all of my groups (lists) listed on the left hand side. I then want to click on one of them and voila!, I see all sorts of info about the list. The purpose, how many members, and, of course, an index of most recent messages. I want to click on a message subject to view the message. I might even want to see profile information about the person who sent that message. If the list is configured as "members can view subscriber list" then I want to see a link which will allow me to see the list of subscribers ( I also get to see it if Im logged in as the list admin). And I want to do this - and much more - all on one site, with a common header and one display area. Basically a one-stop place for me to engage my various communities.
Are you still reading this? If so, maybe you've thought about this stuff before. Or you're thinking "this kinda sounds like Yahoogroups." And if you have tried to build something like this before with an MLM that has various bits of data here and there in hard-to-get-at places, you know it's not a walk in the park. As it turns, out my colleagues and I *have* built something like this. We were able to do so with Lyris as the backend, a MS SQL database which contains all of the Lyris data - and I mean all of it - and the ColdFusion scripting language. This set up has allowed me to write some pretty neat Lyris data-accessing apps since 1998.
I want to do this same kind of thing with Mailman 3. And so I want, at the very least, to have those three aforementioned tables of data:
lists members messages
Can anyone think of any reason why we would not want to have these three tables in a SQL-enabled Mailman 3. What other tables might you want to see? Or fields that might not be found in the above three tables? May I suggest that you be creative, think ahead, and don't restrict yourself by notions of what an MLM is in the here-and-now. If we can first agree on tables, maybe we can move forward and work on the core field sets for each one. And this will in turn give us something to chew on at the sprint. Barry does this approach make sense?
Also, would it be better to set up a Wiki or another list somewhere for this topic (SQL-enabling Mailman3)?
Thanks, Kevin
On 5 Feb 2004 at 23:08, Kevin McCann wrote:
I want to do this same kind of thing with Mailman 3. And so I want, at the very least, to have those three aforementioned tables of data:
lists members messages
Can anyone think of any reason why we would not want to have these three tables in a SQL-enabled Mailman 3. What other tables might you want to see? Or fields that might not be found in the above three tables? May I suggest that you be creative, think ahead, and don't restrict yourself by notions of what an MLM is in the here-and-now. If we can first agree on tables, maybe we can move forward and work on the core field sets for each one. And this will in turn give us something to chew on at the sprint. Barry does this approach make sense?
Funny you should mention this Kevin. :)
I am a new Mailman admin and I was thinking about hacking something together to put my lists archives in a DB (MySQL more than likely).
My first question to you is how normalized do you want to get? The archives, my gut is telling me that the message should be split over several tables. I'm just not sure how at the moment.
I haven't really started planning anything, just rolling ideas around to myself., reading RFCs, Python Tutorials and the like.
-- Bryan Carbonnell - carbonnb@sympatico.ca I've learned.... That one should keep his words both soft and tender, because tomorrow he may have to eat them.
On February 5, 2004 08:08 pm, Kevin McCann wrote:
I want to do this same kind of thing with Mailman 3. And so I want, at the very least, to have those three aforementioned tables of data:
lists members messages
Ok. Here is how I see these on a general level:
members lists
- a member belongs to any number of lists
- a member has access permissions that denote status which can be assigned either on a per list or per group basis
- list are arbitrary collections of members
- lists can be summed into larger groups on the fly as appropriate
- lists do not have to be static (for example "all members since this date")
messages
- messages are tied to a specific user but associated with a given list
Important directives:
The user should be the core entity, not the list. A list should be treated as an association with users. This makes the flow of "what lists am I on?" and "what has Jane Doe posted?" a natural one.
Bounce handling should no longer have anything to do with a specific list.
All bounces should be for the entire installation. You should have one password per installation, one log in per installation, etc.Messages can be to more than one list since "lists" can be unions. This solves the "how do I notify everyone?" problem.
So with these ideas in mind (obviously far from exhaustive, I am just thinking out loud) you would need the following tables:
members lists
- holds profile, global permissions, global status
- holds list type (static, dynamic, union)
- holds list status (active, inactive)
- holds list attributes (moderated, unmoderated, etc.)
- holds description details
- holds template customizations
membership
- holds list id
- holds member id
- holds relationship (read only, post, moderated, last post, admin, whatever you want)
messages
- holds member id
- holds message body (reference to file?)
- holds message headers
- holds message status
message_to_list
- holds message id
- holds list id
Now I can think of lots of other stuff as well but I am just not sure how much you plan to push into MySQL for MM3. If *everything* configuration wise is going unto MySQL, then you will need further tables too for the glue and for some other settings.
What specific fields need to be in these tables of course is a whole other bag of hammers.
Cheers
-- ---> (culture) http://industrial.org : (label) http://deterrent.net ---> (community) http://ampfea.org : (hire me) http://codegrunt.com ---> (send EEEI news to) infosuck@industrial.org ---> Whomever dies with the most URLs wins!!!!!!!!!!!!!
On Feb 5, 2004, at 9:04 PM, moron wrote:
- The user should be the core entity, not the list. A list should be treated as an association with users.
not quite how I've implemented it. I prefer that the list and the user be peer objects. A user has an existance in the system, and there are a series of lists also on the system, and you connect them via relations. In SQL-ese:
user table user_ID (primary key) [suite of user data]
lists table list_ID (primary key)) [suite of list data]
subscriptions user_id, list_id
in a many-many relationship, no primary key. Neither side of that is subservient to the other, nor does it need to be. the relation is a two item table, both indexed. by creating the primary key to be those two fields combined and set as UNIQUE, you avoid duplicates.
Now, it's usually convenient to add more data to that table than just that relation; I normally also store a subscribed flag (ENUM Y|N), and a created/modified timestamp. why not use DELETE to remove a subscription? you can, but by doing an UPDATE (subscription = "N", modified = now()), you have data left around for reporting and statistics, and then you can delete them in one operation later when you want to optimize tables.
If you want to add aliases to a user record, this mode makes that easy, since you simply add which alias in the user record to mail to to the subscriptions table, and post to it; then accept from any email in the user's record for posting.
- Bounce handling should no longer have anything to do with a specific list. All bounces should be for the entire installation. You should have one password per installation, one log in per installation, etc.
agreed. a bounce on one system should affect all. undels flag the email in the user record, not the subscription. whether you fallback to a secondary alias (if we support those) or probe or something, it affects the user, not the subscription.
- Messages can be to more than one list since "lists" can be unions.
This solves the "how do I notify everyone?" problem.
be careful here, down this road lies madness. trust me, umbrella lists and unions aren't as simple as this. And in reality, umbrella lists are a lot less needed than people think.
participants (4)
-
Bryan Carbonnell -
Chuq Von Rospach -
Kevin McCann -
moron