This page keeps some notes on mediawiki and openid.
See also earlier wordpress notes. A wordpress installation can expose a list of OpenIDs; not sure how to get just the ones who have made comments or posts that are accepted and non-spammers, though.
Main question: Can we use external permissioning data and update MediaWiki by script to put non-spammer 'trusted' people in a helpful admin group?
MediaWiki keeps a simple user_groups table that puts a user in an admin group:
mysql> select * from user_groups; +---------+------------+ | ug_user | ug_group | +---------+------------+ | 2 | bot | | 213 | bot | | 1 | bureaucrat | | 2 | bureaucrat | | 213 | bureaucrat | | 1 | sysop | | 2 | sysop | | 213 | sysop | +---------+------------+ 8 rows in set (0.00 sec)
MediaWiki also keeps a bit more information about each user. Note that OpenID users have a username derrived from their URI. Here is a db with very few administrators:
mysql> select user_id, user_name, user_real_name, user_email, ug_group, ug_user from user, user_groups where user.user_id = user_groups.ug_user; +---------+------------+----------------+-------------------+------------+---------+ | user_id | user_name | user_real_name | user_email | ug_group | ug_user | +---------+------------+----------------+-------------------+------------+---------+ | 2 | Danbri.org | | | bot | 2 | | 213 | DanBri | Dan Brickley | danbri@danbri.org | bot | 213 | | 1 | WikiSysop | | | bureaucrat | 1 | | 2 | Danbri.org | | | bureaucrat | 2 | | 213 | DanBri | Dan Brickley | danbri@danbri.org | bureaucrat | 213 | | 1 | WikiSysop | | | sysop | 1 | | 2 | Danbri.org | | | sysop | 2 | | 213 | DanBri | Dan Brickley | danbri@danbri.org | sysop | 213 | +---------+------------+----------------+-------------------+------------+---------+
There we see three users; 'DanBri', 'Danbri.org' (an openid-backed one) and the generic WikiSysop account.
Investigating the openid one, we see:
mysql> select user_real_name, user_email, uoi_openid, user_id, user_name, ug_group from user, user_groups, user_openid WHERE user.user_id = user_openid.uoi_user AND user_groups.ug_user = user.user_id ; +----------------+------------+--------------------+---------+------------+------------+ | user_real_name | user_email | uoi_openid | user_id | user_name | ug_group | +----------------+------------+--------------------+---------+------------+------------+ | | | http://danbri.org/ | 2 | Danbri.org | bot | | | | http://danbri.org/ | 2 | Danbri.org | bureaucrat | | | | http://danbri.org/ | 2 | Danbri.org | sysop | +----------------+------------+--------------------+---------+------------+------------+ 3 rows in set (0.00 sec)
But imagine we know more about the OpenID users, in this case it's just this 'Danbri.org' character.
Meanwhile, over in the userid_openid table, there are 100 users. Could we take some out-of-band information about these folk, and do an update on user_group setting the ones we trust to be bureaucrats? What's the simplest way to manage this, all within SQL or do the SQL update script generation externally, so SQL never sees the external info?
mysql> select * from user_openid; +----------------------------------------------------+----------+ | uoi_openid | uoi_user | +----------------------------------------------------+----------+ | http://danbri.org/ | 2 | | http://www.w3.org/People/Berners-Lee/ | 3 | | http://kidehen.idehen.net/dataspace/person/kidehen | 6 | | http://www.wasab.dk/morten/ | 7 | | http://sukinkot.livejournal.com/ | 8 | | http://openid.osgeo.org/user/crschmidt | 9 | | http://melvster.com/ | 10 | | http://www.wikier.org/ | 11 | | http://guaka.myopenid.com/ | 12 | | =mark.szpakowski | 13 | | http://sandos.pip.verisignlabs.com/ | 14 | | http://andrewk.myopenid.com/ | 15 | | http://tommorris.org/ | 16 | | http://grey.teardrop.myopenid.com/ | 18 | | http://vtrapu.wordpress.com/ | 19 | | http://www.kanzaki.com/ | 20 | | http://openid.cz/jspetrak | 21 | | http://polleres.myopenid.com/ | 26 | | http://www.stephenhui.net/ | 29 | | http://mrflippy.net/mrflippy/id/ | 31 | | http://openid.claimid.com/michaeljpastor | 32 | | http://openid.aol.com/kibennyng | 33 | | http://artem.chertov.name/ | 34 | | http://rn7.net/w/Accueil/raw_html | 35 | | http://openid.claimid.com/rmarkwhite | 36 | | http://www.groetjesthuis.nl/ | 37 | | http://ian.pip.verisignlabs.com/ | 38 | | http://olivier.mehani.name/ | 39 | | http://mammo.pip.verisignlabs.com/ | 40 | | http://kronkltd.net/ | 41 | | http://alexandre.alapetite.net/ | 42 | | http://cdkloos.openid.es/ | 44 | | http://lovenjointlocks.myopenid.com/ | 45 | | http://openid.aol.com/ge0rgy0 | 46 | | http://www.gtaero.net/MyID.config.php | 48 | | http://bart.vanbrabant.eu/ | 49 | | http://dangrig.myopenid.com/ | 52 | | http://bengee.myopenid.com/ | 55 | | http://denny.vrandecic.de/ | 56 | | http://cnmac.pip.verisignlabs.com/ | 57 | | http://www.sergeychernyshev.com/ | 59 | | http://eughenes.myopenid.com/ | 62 | | http://lazutkin.com/ | 67 | | http://www.kryogenix.org/days/ | 68 | | http://blackowl.openid.ne.jp/ | 70 | | http://duncan.mackenzie.name/ | 71 | | http://ismael.olea.org/ | 73 | | http://rodneyrichardson.pip.verisignlabs.com/ | 75 | | http://rob.cakebread.info/ | 76 | | http://mtd.bhaq.org.au/ | 78 | | http://aabs.wordpress.com/ | 84 | | http://sehrgut.co.uk/ | 86 | | http://ansell.pip.verisignlabs.com/ | 89 | | http://www.freewebs.com/rhysa4openid/ | 91 | | http://bagofspoons.net/ | 94 | | http://sina.khakbaz.com/id/ | 95 | | http://aukcje.myopenid.com/ | 96 | | http://roland.schijvenaars.myopenid.com/ | 103 | | http://darac.livejournal.com/ | 104 | | http://yh.myopenid.com/ | 106 | | http://openid.claimid.com/gzub | 107 | | http://tjcrowley.livejournal.com/ | 109 | | http://openid.blogs.es/wikier | 110 | | http://robertc.myopenid.com/ | 111 | | http://chaoskaizer.myopenid.com/ | 112 | | http://openid.aol.com/opensrcsf | 115 | | http://downlode.org/ | 118 | | http://openid.brandonwerner.com/bbjwerner | 120 | | http://klaus.seistrup.dk/ | 122 | | http://openid.zacharywhitley.com/ | 124 | | http://benl.co.uk/ | 127 | | http://openid-provider.appspot.com/mt8168 | 128 | | http://evan.prodromou.name/ | 129 | | http://szaman.openid.pl/ | 130 | | http://matt.lee.name/ | 131 | | http://petschni.myopenid.com/ | 137 | | =aldon.hynes | 138 | | http://anthonybroadcrawford.myopenid.com/ | 141 | | http://openid.claimid.com/sal | 142 | | http://eatabrick.org/ | 148 | | =david.reindl | 152 | | http://www.openidfrance.fr/mglcel | 153 | | http://evanp.myopenid.com/ | 158 | | http://komunamu.myopenid.com/ | 162 | | http://tuuli.info/ | 165 | | http://eisi.myopenid.com/ | 169 | | http://billybill.myopenid.com/ | 170 | | http://sabre.inria.fr/openid/index.php?u=adil | 175 | | http://grawity.myopenid.com/ | 176 | | http://josschuurmans.myvidoop.com/ | 189 | | http://getopenid.com/smk | 190 | | http://eyeblood.openid.pl/ | 199 | | http://ofaurax.free.fr/ | 202 | | http://openid.aol.com/Vermilion0087 | 203 | | http://karmona.blogspot.com/ | 204 | | http://www.barrucadu.co.uk/ | 205 | | http://stuartyeates.myopenid.com/ | 206 | | http://bandri.hyves.nl/#MDA5MzkzZD | 215 | | http://foaf.me/openid4.me/http://foaf.me/ah1%23me | 216 | | http://foaf.me/openid4.me/http://foaf.me/ah2%23me | 218 | +----------------------------------------------------+----------+ 100 rows in set (0.00 sec)
To be continued ....
(er, well help welcome here, with SQL ... inserts vs updates etc...)
Updating SQL groups
OK, so imagine we've got evidence from beyond SQL that some user with openid http://me.example.com/ deserves Bureaucrat permissions. We don't know what rows are already in the user_groups table.
What do we do?
- MySQL REPLACE? http://dev.mysql.com/doc/refman/5.0/en/replace.html
- MySQL INSERT ON DUPLICATE KEY? http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
- Something else?
To explore this, just grab a current MediaWiki install and the OpenID extension. If you want a copy of the FOAF db, let me know... --Danbri.org 07:29, 23 October 2009 (UTC)
Sample code
From tobyink in IRC:
INSERT INTO user_groups SELECT o.uoi_user, 'bureaucrat' FROM user_openid o LEFT JOIN user_groups g ON o.uoi_user=g.ug_user AND g.ug_group='bureaucrat' WHERE g.ug_user IS NULL AND TrustLevel(o.uoi_openid) > 0
tobyink: You'd set that up to run on a trigger (any changes to the openid table and it fires). TrustLevel would be a user-defined function. In MySQL I think [http://dev.mysql.com/doc/refman/5.1/en/adding-udf.html UDFs] can only be written in C. :-(
danbri: how to rewrite that to check if the value is in some concrete enumerated list
tobyink: AND o.uoi_openid IN ('http://foo', 'http://bar', ... )
So our current best guess is:
INSERT INTO user_groups
SELECT o.uoi_user, 'bureaucrat'
FROM user_openid o
LEFT JOIN user_groups g
ON o.uoi_user=g.ug_user
AND g.ug_group='bureaucrat'
WHERE g.ug_user IS NULL
AND o.uoi_openid IN ('http://foo', 'http://bar', ... )
now all we need to do is generate this from some likely list of sources...
See wordpress notes.
sys = "echo 'select url from wp_openid_identities' | mysql -u wpuser -p#{pwd} danbri_wordpress"
... is this enough, or need to be more careful to find just good openids?
An extended version of the WP query, that only returns openids with at least one good comment, would be:
sys = "echo 'select url from wp_openid_identities oi join wp_comments c on oi.user_id=c.user_id where comment_approved=1' | mysql -u wpuser -p#{pwd} danbri_wordpress"
