Volunteer database
Yes, it's another database project! Whoopee!
Given the limited volunteer-hours available for hacking, koanhead has decided that we need to track volunteers and their hours more urgently than we need to track inventory (people are, after all, more important than are things.) Even though the Inventory tracking database project is way cooler.
Who
What
A webpage with the following:
- Secured sign-up form for new volunteers based on existing File:Volunteer application.pdf.
- Integration with our Mediawiki instance such that new volunteers automagically get a wiki account (and possibly accounts with our various other services. Kerberos SSO would be nice to implement...)
- Volunteer hours tracking, correlated with relevant Working Groups
Where
[Our GitHub] - if that link doesn't work here's the text -
http://freegeekseattle.org/wiki/index.php/Volunteer_database
How
Let's make a Mediawiki extension!
https://www.mediawiki.org/wiki/Manual:Developing_extensions
https://www.mediawiki.org/wiki/Extension_Matrix/database
Steps
Setup
alter user tables
to reflect additional information present on volunteer application
- https://upload.wikimedia.org/wikipedia/commons/thumb/4/42/MediaWiki_1.20_%2844edaa2%29_database_schema.svg/2500px-MediaWiki_1.20_%2844edaa2%29_database_schema.svg.png
- this is done once only, when the extension is installed
- maybe MW's database interface can handle this, maybe not:
- https://www.mediawiki.org/wiki/Manual:Database_access
- (it can, but the SQL for the created and altered tables needs be written manually and included as a .sql file to the setup script)
- alter table 'user' add column 'address' INT
- foreign key for 'locations' table
- create table 'addresses' yada yada
- alter table 'user' add column yada yada
We COULD just add all the stuff to the user table, but I'd rather normalize on addresses at least.
create volunteer hours table
| Sample table | ||||||
|---|---|---|---|---|---|---|
| WorkUnitid(int, primary key) | userid(foreign, Users.userid) | Location(foreign, Places.placeid) | ProgramID (foreign, Programs.programID) | Date | StartTime | StopTime |
| 1 | 1337 | 86 | 2 | 20140402 | 2100 | 2101 |
As proposed, this schema requires us to create two more tables: Places and Programs. Places is for storing address information (or other location information), as a given location could have multiple roles (vendor address, volunteer address and program location all in one? Could happen.)
| Locations | ||||||
|---|---|---|---|---|---|---|
| placeid(int, not null, primary key) | placeName (optional) | addressLine1 | addressLine2 | cityID (sure, let's have yet another table just for towns, why not?) | zip(ditto, I guess?) | coords |
| 99 | the Ole Hiway | State Route 99 | Suite Georgia Brown | 44 | 98105 | NULL |
Programs is for storing program information, so I guess it just needs fields for ID, name and maybe description.
All these tables and the correct relations ought to add up to 3rd normal form (at least in my head), but I can't prove it.
change user signup form
- this is done once only, when the extension is installed
- change Special:UserLogin accordingly with database alterations
- https://doc.wikimedia.org/mediawiki-core/master/php/html/SpecialUserlogin_8php.html
Execution
ensure that Special:Listuser and friends does not leak sensitive information
- implement as tests that fire when new data is submitted: build the relevant Special: pages and check them for the contents of selected fields
- also to make sure the data we want is displayed of course :^)
build volunteer hours tracking form and associated reports
- Special:VolunteerHoursInput or similar
- restricted to Bureaucrat role, or do we need a new role?