Maemo.org team/Creating an election

Line 53: Line 53:
  /* Delete accounts created too recently (3 months before end of election) */
  /* Delete accounts created too recently (3 months before end of election) */
  delete from electorate where creation_date > '2010-06-23';
  delete from electorate where creation_date > '2010-06-23';
-
+
 
-
/* It is also possible to filter on older talk_ids, even if the maemo
+
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.
-
  * account was created recently. Ask Reggie for the cut-off Talk user id
+
-
  * for the electorate date. */
+
; Generate election tokens and send out emails
; Generate election tokens and send out emails

Revision as of 12:36, 16 December 2010

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 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 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.

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.