Dr Nic

MagicCGI shows OpenID user count

OpenID count

In the last 20 days, 43 people have used OpenID to leave comments. That’s very cool.

Corollary: add OpenID login to your blog.

Even cooler – Dynamic counter

The screen shot comes from the Comments form.

If you look at the comments form in a week, month, year, the counter above have be changed from its original value 43.

No fancy WordPress plugins (I don’t do PHP)

No Apache tricks. (I don’t know any Apache tricks)

Instead with Javascript/HTML attached to JSON attached to a RubyCGI script attached to my WordPress database via ActiveRecords and some magic.

I call it MagicCGI. I also call it Frigging Scary.

Try the following:

$ curl -v "http://drnicwilliams.com/cgi-bin/wp_drnicwilliams.cgi?table=wp_openid_identities&action=count&format=json"
< Content-Type: txt/json

Or some XML?

$ curl -v "http://drnicwilliams.com/cgi-bin/wp_drnicwilliams.cgi?table=wp_openid_identities&action=count&format=xml"
< Content-Type: txt/xml
<?xml version="1.0" encoding="UTF-8"?>
  <count type="integer">43</count>

Raw data?

$ curl "http://drnicwilliams.com/cgi-bin/wp_drnicwilliams.cgi?table=wp_posts&field=post_title&limit=5&order=post_title"
[{"attributes": {"post_title": "OpenID count"}},
 {"attributes": {"post_title": "MagicCGI shows OpenID user count"}},
 {"attributes": {"post_title": "map_by_method now works with ActiveRecord associations"}},
 {"attributes": {"post_title": "Feedburner"}},
 {"attributes": {"post_title": "One year on the InterTubes"}}

Getting kinda scary now, I think.

Ooh, just how much magic?

Want a list of available tables to play with?

$ curl "http://drnicwilliams.com/cgi-bin/wp_drnicwilliams.cgi?meta=tables"
[{table_name: 'wp_users'},...]
  • add &format=xml to XML output; JSON is default
  • add &meta=columns to include the column schema definitions
  • add &table_name=wp_posts for each table you want (instead of all the tables)

E.g. to see the columns for wp_posts and no other table, in XML:

$ curl "http://drnicwilliams.com/cgi-bin/wp_drnicwilliams.cgi?meta=tables&meta=columns&table_name=wp_posts&format=xml"

A list of all urls and internal user_ids for users/OpenID users/registered commenters?

$ curl "http://drnicwilliams.com/cgi-bin/wp_drnicwilliams.cgi?table=wp_users&field=user_url&field=id"
[{"attributes": {"id": "1", "user_url": "http://drnicwilliams.com"}}, ...

So, now we know Dr Nic = user id 1.

Oh oh oh, how about a list of comments for a specific user?

$ curl "http://drnicwilliams.com/cgi-bin/wp_drnicwilliams.cgi?table=wp_comments&user_id=1"
...comments by Dr Nic...
$ curl "http://drnicwilliams.com/cgi-bin/wp_drnicwilliams.cgi?table=wp_comments&user_id=1&action=count"

Dr Nic’s commented in his own blog 232 times? Out of how many comments?

$ curl "http://drnicwilliams.com/cgi-bin/wp_drnicwilliams.cgi?table=wp_comments&action=count"

Where are the user emails? Where are their passwords?

Hidden! See below.


JSON URLs support callback=someCallbackMethod and/or variable=someLocalVariable.

$ curl "http://drnicwilliams.com/cgi-bin/wp_drnicwilliams.cgi?table=wp_comments&action=count&callback=someMethod"

What does wp_drnicwilliams.cgi look like?

Something a little like this…

require 'magic_cgi'         # loads the render magic, model magic, and meta-model magic
include Render

require 'magic_cgi/config/wordpress'  # connect to DB using WordPress installation (wp-config.php)
MagicCGI::Config::Wordpress.establish_connection "/path/to/drnicwilliams/web/public"

# The following is defaulted for WordPress connections:
MagicCGI::Config.hidden_tables |= %w[wp_openid_nonces wp_openid_associations wp_usermeta wp_tla_rss_map wp_tla_data]
MagicCGI::Config.hidden_columns['wp_users'] = %w[user_email user_pass user_activation_key]
MagicCGI::Config.hidden_columns['wp_openid_identities'] = %w[hash]
MagicCGI::Config.hidden_columns['wp_comments'] = %w[comment_author_email comment_author_IP]
MagicCGI::Config.hidden_columns['wp_posts'] = %w[post_password]

render do |params|
  data = DbTable.from_params(params)
  data ||= begin
    table_name = params['table'].first || 'wp_users'
    model_name = ActiveRecord::Base.class_name(table_name)
    klass = MagicCGI::MagicModel.create_class(model_name, ActiveRecord::Base)

Can I write my own CGI scripts for my own DBs?

Sure. Its a library called MagicCGI.

What is MagicCGI?

Coming soon.

Related posts:

  1. Showing off data on a timeline I’m still trying to justify my effort writing the MagicCGI...
  2. Why supporting multiple OpenIDs per User is useful for users… Web apps/services go down for maintenance (expected or erroneously) all...
  3. RailsRumble hates OpenID There are 146 RailsRumble entrants. %w[rubygems hpricot open-uri].each { |l|...
  4. Sample Rails app: multi-OpenIDs per user Last time, on “Dr Nic loves OpenID”… Dr Nic had...
  5. One App, One User Account and Multiple OpenIDs Summary: Its the future, and its not Facebook. Learn it....

6 Responses to “MagicCGI shows OpenID user count”

  1. Tom says:

    That’s cool.

  2. stevebeyer says:

    Well lets make it 47

  3. Pauli says:

    I’m waiting breathlessly for an available MagicCGI.

    — just so you know.

  4. Dr Nic says:

    @Pauli [via] – oh cool – I didn’t figure anyone cared so I kept it to myself :) I’ll release it soon then.

  5. Pauli says:

    It’s been a bit over a month, so I thought I’d check back – and I see you’ve posted the SVN for it.

    Thanks a bunch!