• Intro
• Downloads
• Community
• Development
• News
• TALK

Maemo.org team/Creating an election

Revision as of 10:26, 7 March 2011

These are the steps required to create a new election for the Community Council - essentially the same process applies for a referendum.

Get list of maemo.org accounts

Request a dump of maemo.org accounts including karma and account creation date from Henri or Neithan at the karma cut-off date. The format for each row is:

username,"Real name",firstname,surname,emails(; separated),"join date",karma,talk profile URL

Create database if necessary

The schema is:

DROP TABLE IF EXISTS `electorate`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `electorate` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(20) DEFAULT NULL,
`firstname` varchar(50) NOT NULL DEFAULT '',
`lastname` varchar(50) NOT NULL DEFAULT '',
`email` varchar(50) NOT NULL DEFAULT '',
`karma` int(11) NOT NULL DEFAULT '0',
`creation_date` datetime DEFAULT NULL,
`talk_id` varchar(70) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=32822 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

Create election in the database

For the Q3 2010 election, that means running the following on the maemoelections database on test.maemo.org:

/* Fields are ID, type ('elections' or 'referendum'), name, start date, end date, # of seats, question */
insert into elections values(10, 'elections', 'Maemo Community Council elections, Q3 2010', '2010-09-16 00:00:00', '2010-09-22 23:59:59', 5, 'In order of preference, which of the following candidates do you want to represent you on the Maemo Community Council?');

/* Fields are id, election id, name - id is auto-increment */
INSERT INTO election_choices VALUES
  (0, 10, 'Andrea Grandi (andy80)'),
  (0, 10, 'Tim Samoff (timsamoff)'),
  (0, 10, 'Rüdiger Schiller (chemist)'),
  (0, 10, 'Cosimo Kroll (zehjotkah)'),
  (0, 10, 'Andrew Flegg (Jaffa)'),
  (0, 10, 'Stephen Gadsby (sjgadsby)'),
  (0, 10, 'Felipe Crochik (fcrochik)'),
  (0, 10, 'Robin Burchell (w00t)'),
  (0, 10, 'Kathy Smith (revdkathy)'),
  (0, 10, 'Attila Csipa (achipa)')
;

Insert electorate into database

Insert the rows into your database using the following (note, we ignore the talk profile URL now, but there is still a column for it in the database):

/* Truncate old electorate table and old temp tokens */
TRUNCATE electorate;
TRUNCATE election_tmp_tokens;
 
/* Load data file using LOAD DATA INFILE (or mysqlimport) */
LOAD DATA LOCAL INFILE '/path/to/data/users.csv' INTO TABLE electorate
   FIELDS terminated BY ',' OPTIONALLY ENCLOSED BY '"'
   (username,@dummy,firstname,lastname,@email,creation_date,karma,@talk)
   SET email=substring_index(@email,';',1), 
       talk_id=substring_index(@talk,'=',-1)
;
/* This is a complicated line, worth explaining.
 * LOAD DATA INFILE basically does a series of inserts, one for each 
 * line of data in the input file.
 * The part in brackets matches fields or variables to entries in the 
 * data line, with separator, quote and line ending bevahiour as specified.
 * We drop the "Real name" field. The email field contains semi-colon
 * separated list of all the email addresses associated with the account,
 * so we search the field for a semicolon, and just take the first one.
 * The talk field contains a URL with a talk_id after "=", which is what we
 *  want, so we look for = and take everything after the first occurrence.
 */
 
/* Delete from electorate anyone with insufficient karma */
DELETE FROM electorate WHERE karma < 10;
/* Delete accounts created too recently (3 months before end of election) */
DELETE FROM electorate WHERE creation_date > '2010-06-23';

It is also possible to filter on older talk_ids, even if the maemo account was created recently. Ask Reggie for the cut-off Talk user id for the electorate date, and include the cut-off in the "delete from" for recently created accounts.

Extract eligible candidates

Generating the list:

 mysql -e 'select username, firstname, lastname, karma  from electorate where karma>100 order by lastname'> eligible.txt

Awk script to generate the table:

Generate election tokens and send out emails

Tokens and emails are created with scripts in the maemo2midgard repository - create-tmp-tokens.pl and mail-instructions.pl Database credentials are in a private local file called config.pl

We need to modify the email instructions (instructions.txt) every election with the candidate list, links to election materials, etc. First we generate the tmp tokens, insert them in the database by running the resulting file through the mysql client, and then using the instructions and the generated maildata.txt file as inputs, we mail the instructions out.

./create-tmp-tokens.pl <election_id> <tokens_file> <maildata_file>
mysql -u<dbuser> -p<dbpasswd> -h<dbhost> < <tokens_file>
./mail-instructions.pl <maildata_file> <instructions>

And we're all done! The election will run until its closing date, at which point you can generate the results

Resending tokens

Sometimes token emails get caught in spam filters, or accidentally deleted, or the first email address associated with their account is dead, or whatever. You can find the anonymous token associated with someone as long as they have not voted yet.

In a Plain Old mysql client, run the following query:

select e.id, e.username, e.firstname, e.lastname, e.email,
       t.election_id, t.tmp_token
from electorate as e, election_tmp_token as t
where e.id=t.member_id and e.username='<username>';

You can also look using "LIKE" on the username, first name or last name if you need to. This will give you the email/tmp_token pairing which the person can then use to vote, and which you can then send on to them, after verifying that they are who they say they are.