User DB lookup

From PluginWiki

This is quite a complex feature. Take a deep breath.

You can maintain a Yahoo database with information about particular members. Some groups do this already, but the plugin makes it easier to do so, and integrates it with what you'll see when you're looking at the group's pages.

If you're one of those people for whom a picture paints a thousand words, then have a look at User_DB_lookup_in_pictures - which shows screengrabs of

a designed table
a configured db (to return information drawn from the table at 1 above) in the db tab of the plugin
a members details in the format of 1 above
the whole thing processed for one member - therefore pulling together what you see in 2 and 3 above.

The screengrabs are intended to complement (not replace) the full explanation on this page, and show what you should have achieved at a specific point.

Designing

How many DBs to use?

The easiest thing to do is just to use one database. You'd do that if:

You just moderate one group.
You moderate several groups, and can agree with any other moderators that they will share the database with you.

But you might need to use multiple databases:

You want to keep different databases for different purposes - e.g. one database for strikes, and one for bans.
If you moderate several groups which each use different databases.
If there's a database for use across multiple groups - for example, if your modsquad has such a database.

Not sure? Start off with one - you can add more later.

Designing the fields in your DB

You need to decide which fields you're going to store in your DB.

If you're setting up a DB from scratch, then a basic structure would be to have the following fields (or 'columns' in Yahoo's terminology):

Yahoo ID. This is the Yahoo ID your DB entry refers to, or for members who don't have Yahoo IDs, it uses part of their email address. You must have this.
Date. This is the date the entry was made.
Moderator. This is the moderator who made the entry.
Group. This is the group the member is a member of. You might not think you need this field, but you should add it for future use.
Comment. This is the note you want to record about the member. It's generally simpler to have multiple entries in the DB for the same member rather than have multiple comment fields.

Some people also store email address, though that's not really necessary once you store the Yahoo ID. Remember also that people can change their email address.

If you already have a DB, then that's already been decided - but you may need to add more ones just for the plugin:

Moderator might not be present.
Group probably isn't present.

There's a screengrab of a designed table here.

Setting up your DB

We'll start off assuming that you use just a single database. Using multiple ones is explained at the bottom of this page.

Database Location

Now you must decide where to keep the database.

Ignore the Add and Delete buttons for now - they're to do with multiple databases.

There are two places to keep groups:

In the main group itself. If you do this, you must be very careful that your members don't have permissions to see or modify it. But this is the option you might choose if you moderate multiple independent groups. In this case you leave the group URL blank.
In a separate group. If you have a separate group for the moderators, that's a good place. Or you could create a group specifically for this purpose. This also allows you to share the same database across multiple groups, which can be useful if you work closely with your neighbouring groups.

Some notes about the value to put in:

You should configure just the top-level group, e.g. http://groups.yahoo.com/group/edinburghfreegle_mods.
Without the database part.
No trailing slash.
This must be the primary URL for the group. For example, if you have a UK group, then you'll see that if you go to http://groups.yahoo.com/group/UKGroup you'll see that Yahoo redirects to http://uk.groups.yahoo.com/group/UKGroup'. You need to make sure you use the URL that Yahoo redirects to, i.e. in this example the http://uk....

You can also tell the plugin to annotate the database page with user information - see Annotating the DB page. But that adds extra complexity, so if you're just getting to grips with this function, leave that till later.

Group Table

You also need to configure which database you're going to use - because a Yahoo group can contain more than one database.

You need to find out which Yahoo "table" your database is in. You can find this by moving your mouse over the table name in Yahoo, and looking at the taskbar on the bottom:

Then configure that number into the plugin's configuration:

Columns to Display

Now you need to configure which columns from the database should be displayed by the plugin, when it's pulling this information into other Yahoo pages.

For each column you want to display, you need to find the number associated with it, again by moving your mouse over it.

...and then configure it into the plugin in one of the column slots:

The order the columns appear here is the order they'll be displayed.

Auto-inserting Fields

The final part of configuration - and you're glad to hear that, aren't you? - is auto-inserting fields. As you'll see, the plugin provides links to add members to the DB - and when it's doing that, it can fill in some useful fields for you.

Whether or not you have these fields depends on the design of your database, and again you'll need to know the column number associated with them. The fields you can insert are:

Yahoo id. Every DB should have this, but the column it's in may vary. Enter the column number in the space.
Email. You can record this as well as the Yahoo ID, though it's usually not necessary (particularly since people can change them).
Moderator. This is the ID of the moderator who added the member to the database - so when you're adding an entry it will be you, but when you're looking at them it may be other mods for this group. You can choose to either automatically insert your Yahoo name, or use a specific value (for example a more friendly name). Enter the column number in the first space. If you want to use your Yahoo name, click the "Auto" button, if you want to use something different, click "Use" and enter the name or initials you want to insert in the second space.
Date. This is the date the member was added to the DB. Place the column number in the first box, and the date format where indicated. You can play with the format - use whatever separators you prefer, and put the following special keywords in the any order:
- dd is the number of the day in the month.
- mmm means a month in alphabetic format e.g. Jan
- mm means a month in numerical format e.g. 01
- yyyy is a 4-digit year, e.g. 2007
- yy is a 2-digit year, e.g. 07
Group Name means the name of the group that the member is in. If you're only moderating one group or you don't share the database across groups, this might seem pointless - but put it in anyway. That way if you move to moderating multiple groups and sharing the database across them, your database will already be set up correctly. Again, enter the database column number. You should also specify a default group name to use if no entry exists in this column.
Defaults are useful if you usually put the same value into a column, and you want that to be filled in as a default value in your Add. Enter the column number, and the value you want to be filled in. As with all other values, you can modify this before you actually add the record. You can also use $crossgroup in here to substitute in cross-posted groups - so if you have a message marked as a crosspost, and then click the link to add a record to the database, you can include the groups the message was crossposted to.

There's a screengrab here of what you should see at the end of this stage.

Moderating more than one group

If you mod more than one group (using a different Yahooid for each group) and each group has its own specific database, it is possible to use the UserDB lookup function across multiple groups.

There are two different ways to do this, depending on whether your databases are precisely identical or not. If in doubt, use the second method.

Identical Databases

The table in each group must be identical in terms of column name and order. Also the number of the table you are querying must be identical - sometimes the only way to get the table numbers identical is to create and delete tables until you get to the correct number.

When you want to tell the plugin where to look for the table, do Tools->Add-ons->ModPlugin->Options->DB, then in the top line where it says "lookup group URL" leave this blank. The plugin works out which group you are logged into as moderator and looks up the correct database.

Different Databases

Main difference - tables can be configured independently for each group, and can have different fields in them (which can be useful if you're querying databases originally set up differently).

Do Tools->Add-ons-ModPlugin->Options->DB. In the N'ame field type a short name which identifies your first group (I'll use SCAR for the purposes of this demo) and click Add and Save Changes, then where it says URL leave it blank and click Save Changes. Now you can configure the table number, columns to return etc. as they apply just to the SCAR group. You can leave the group name box in the auto-inserted field configuration empty. When you've finished, Click OK.

To add the database for your second group, repeat the process as in the above paragraph, but use a different name. I'm going to call it BRID, and again leave the URL field blank. Then configure the table number, columns to return etc. as they apply just to the BRID group.

When you bring up a page in your group, you will see two links ... Add to SCAR and Add to BRID. Hover your mouse over either link and it will show the url of the database in your current group. The member information which it returns (and you can click on) is from the database of the group you are currently in.

Using your DB

At this stage, if you have the plugin enabled when you open your Members or Pending Members page, this is a screengrab of what you should see.

Once you've configured your database, you'll start to see the plugin add new things.

The DB entries you've made for a particular user, along with links to either Delete that DB entry, or to Edit it.
An Add to DB link to add a new user to the DB - which will bring up a page with any auto-inserted fields that you configured.

Here are some samples - though obviously what goes in them depends on what fields you have in your database.

Adding a record

Here's a sample Add screen, with auto-inserted fields.

You can add multiple entries for the same member - this is generally simpler than trying to have multiple fields for multiple comments.

Deleting a record

If you hit delete, then you'll get a new tab showing this briefly:

(Yes, it really should say Edit above.)

And then you should see the delete confirmation:

Editing a record

If you hit edit, you'll get a new tab with lookup screen, then the Edit page:

If you've got everything right in the preceding steps, this is a screengrab of what the record should look like.

Using Multiple Databases

Ok, so now you know how to use a single database. But you might have multiple different databases on your different groups. So now let's look at using multiple databases.

Adding another Database

As follows:

You type the name of another database into the Name box. You should make this quite short - it will get used in the Add to link that gets displayed.
Configure the other boxes. The important thing to note here is that the table, and other fields, may be completely different between your two databases - they don't have to match.
Then click Add.
Then click Save Changes.

Selecting which Database to configure

Once you've got multiple databases, they will appear in the drop-down list.

You can select which database you're configuring via this drop-down list. When you select a new one, it will fill in the other boxes on the page with the configuration for this database.

If you modify the fields, make sure you click Save Changes before selecting a new database to configure, otherwise it will lose your changes.

Deleting a Database