Editing Maemo.org team/Creating an election
Warning: You are not logged in.
Your IP address will be recorded in this page's edit history.
The edit can be undone.
Please check the comparison below to verify that this is what you want to do, and then save the changes below to finish undoing the edit.
Latest revision | Your text | ||
Line 1: | Line 1: | ||
These are the steps required to create a new election for the [[Community Council]] - essentially the same process applies for a referendum. | 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 | ; Get list of maemo.org accounts | ||
Line 78: | Line 8: | ||
: The schema is: | : The schema is: | ||
- | + | DROP TABLE IF EXISTS `electorate`; | |
- | DROP TABLE IF EXISTS `electorate`; | + | /*!40101 SET @saved_cs_client = @@character_set_client */; |
- | /*!40101 SET @saved_cs_client = @@character_set_client */; | + | /*!40101 SET character_set_client = utf8 */; |
- | /*!40101 SET character_set_client = utf8 */; | + | CREATE TABLE `electorate` ( |
- | CREATE TABLE `electorate` ( | + | `id` int(11) NOT NULL AUTO_INCREMENT, |
- | `id` int(11) NOT NULL AUTO_INCREMENT, | + | `username` varchar(20) DEFAULT NULL, |
- | `username` varchar(20) DEFAULT NULL, | + | `firstname` varchar(50) NOT NULL DEFAULT '', |
- | `firstname` varchar(50) NOT NULL DEFAULT '', | + | `lastname` varchar(50) NOT NULL DEFAULT '', |
- | `lastname` varchar(50) NOT NULL DEFAULT '', | + | `email` varchar(50) NOT NULL DEFAULT '', |
- | `email` varchar(50) NOT NULL DEFAULT '', | + | `karma` int(11) NOT NULL DEFAULT '0', |
- | `karma` int(11) NOT NULL DEFAULT '0', | + | `creation_date` datetime DEFAULT NULL, |
- | `creation_date` datetime DEFAULT NULL, | + | `talk_id` varchar(70) DEFAULT NULL, |
- | `talk_id` varchar(70) DEFAULT NULL, | + | PRIMARY KEY (`id`) |
- | PRIMARY KEY (`id`) | + | ) ENGINE=MyISAM AUTO_INCREMENT=32822 DEFAULT CHARSET=latin1; |
- | ) ENGINE=MyISAM AUTO_INCREMENT=32822 DEFAULT CHARSET=latin1; | + | /*!40101 SET character_set_client = @saved_cs_client */; |
- | /*!40101 SET character_set_client = @saved_cs_client */; | + | /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; |
- | /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; | + | |
- | + | ||
; Create election in the database | ; Create election in the database | ||
Line 102: | Line 30: | ||
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?'); | 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 */ | |
- | /* Fields are id, election id, name - id is auto-increment */ | + | insert into election_choices values |
- | insert into election_choices values | + | |
(0, 10, 'Andrea Grandi (andy80)'), | (0, 10, 'Andrea Grandi (andy80)'), | ||
(0, 10, 'Tim Samoff (timsamoff)'), | (0, 10, 'Tim Samoff (timsamoff)'), | ||
Line 115: | Line 42: | ||
(0, 10, 'Kathy Smith (revdkathy)'), | (0, 10, 'Kathy Smith (revdkathy)'), | ||
(0, 10, 'Attila Csipa (achipa)') | (0, 10, 'Attila Csipa (achipa)') | ||
- | ; | + | ; |
- | + | ||
; Insert electorate into database | ; 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): | : 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 old electorate table and old temp tokens */ | + | truncate election_tmp_tokens; |
- | truncate electorate; | + | |
- | truncate election_tmp_tokens; | + | |
- | /* Load data file using LOAD DATA INFILE (or mysqlimport) */ | + | /* Load data file using LOAD DATA INFILE (or mysqlimport) */ |
- | load data local infile '/path/to/data/users.csv' into table electorate | + | load data local infile '/path/to/data/users.csv' into table electorate |
fields terminated by ',' optionally enclosed by '"' | fields terminated by ',' optionally enclosed by '"' | ||
(username,@dummy,firstname,lastname,@email,creation_date,karma,@talk) | (username,@dummy,firstname,lastname,@email,creation_date,karma,@talk) | ||
set email=substring_index(@email,';',1), | set email=substring_index(@email,';',1), | ||
talk_id=substring_index(@talk,'=',-1) | talk_id=substring_index(@talk,'=',-1) | ||
- | ; | + | ; |
- | /* This is a complicated line, worth explaining. | + | /* 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 anyone with insufficient karma */ |
- | delete from electorate where karma < 10; | + | delete from electorate where karma < 10; |
- | /* 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 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. | 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. | ||
Line 160: | Line 83: | ||
Awk script to generate the table: | Awk script to generate the table: | ||
- | <source lang=" | + | <source lang="awk"> |
BEGIN{ FS="\t"; | BEGIN{ FS="\t"; | ||
print "{|\n|-\n! Username\n! Real name\n! Karma"; | print "{|\n|-\n! Username\n! Real name\n! Karma"; | ||
Line 186: | Line 109: | ||
And we're all done! The election will run until its closing date, at which point you can [[../Generating election results | generate the results]] | And we're all done! The election will run until its closing date, at which point you can [[../Generating election results | 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. | 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: | In a Plain Old mysql client, run the following query: | ||
- | |||
select e.id, e.username, e.firstname, e.lastname, e.email, | select e.id, e.username, e.firstname, e.lastname, e.email, | ||
t.election_id, t.tmp_token | t.election_id, t.tmp_token | ||
from electorate as e, election_tmp_token as t | from electorate as e, election_tmp_token as t | ||
where e.id=t.member_id and e.username='<username>'; | 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. | 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. | ||
[[Category:Community]] | [[Category:Community]] |
Learn more about Contributing to the wiki.