Dr Nic

Showing off data on a timeline

I’m still trying to justify my effort writing the MagicCGI code. It let you get an XML or JSON feed for any database, with some basic conditionals, limits etc. The existing demo is for my blog database.

I think this one is kinda cool – showing off all your blog posts/articles on a timeline:

Can’t see the snazzy timeline above?

To get the timeline working with the schema output from the Magic CGI, I needed to write my own Timeline EventSource. Currently I don’t do anything fancy with the generated bubbles – I just use the defaults.

The MagicCGI query gets all wp_posts (WordPress schema) rows, where “post_status=publish” and only returns fields that are relevant (notably ignores the large post_content field holding the blog content):

http://drnicwilliams.com/cgi-bin/wp_drnicwilliams.cgi?
format=json&table=wp_posts&post_status=publish&
field=ID&field=post_title&field=post_date_gmt&
field=post_modified_gmt&field=guid&field=comment_count

The other cool thing I did here was to deploy it all with Capistrano (an html page + javascript libs). It even deploys/manages a copy of the Timeline trunk onto the server. This is the first time I’ve deployed a non-Ruby/Rails app using Capistrano, and once I got it set up it becomes much easier to manage than using an FTP app, etc.

How easy? cap1 update (note the dubious use of capistrano 1… I still… haven’t… converted… to 2.0…)

I’m not confident enough that my solution is sexy enough to outline in detail, so if you’re interested in deploying Javascript apps etc with Capistrano, just checkout the code (below) and look at the config/deploy.rb script. (note that I’ve disabled the deprec require statement as it assumes I’m wanted to run some mongrels etc, but deprec is very handy for setting up ssh and other fun stuff at the start).

So, no details here, just a fun example.

If the Timeline tickles your fancy, their website has lots of tutorials, and/or check out my html/javascript code.

svn co http://drnicwilliams.com/svn/blog_timeline/trunk blog_timeline

If the MagicCGI tickles your fancy, its also only on svn at the moment, though its docco should be pretty good. I think.

svn co http://rubyforge.org/var/svn/magicmodels/magic_cgi/trunk magic_cgi

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

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"?>
<result>
  <count type="integer">43</count>
</result>

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...
or
$ curl "http://drnicwilliams.com/cgi-bin/wp_drnicwilliams.cgi?table=wp_comments&user_id=1&action=count"
232

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"
963

Where are the user emails? Where are their passwords?

Hidden! See below.

Callbacks?

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"
someMethod(963);

What does wp_drnicwilliams.cgi look like?

Something a little like this…

#!/usr/local/bin/ruby
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)
    klass.find_or_count_by_params(params)
  end
  data
end

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

Sure. Its a library called MagicCGI.

What is MagicCGI?

Coming soon.

My railsconf sessions in my sidebar

Jesse Newland got the JSON API a day ago and already built a reusable Javascript widget to show conference session selections on his blog sidebar.

So I added it to my blog too!

Dr Nic MyConfPlan sidebar
Feel free to use his code, and write your own CSS to make it look sexy on your site.

Remember, its dynamic JSON (or XML) – so any changes you make to your conf plan will automatically show up next time the widget is displayed.

Doubly remember – use your JSON url not Jesse’s… its the link “json” at the top of each conference page, when you’re logged in. Alternately, just change jnewland to your username. Yeah, that’s simpler.

[Most of this text copied from original announcement]

Thanks Jesse!

Supporting JSON callbacks in Rails

Now you know how to write JavaScript widgets, now you need to know how to help others write widgets for your data.

Here’s a short-one act play to describe the issue at hand.

Actors in this play

You: You run a website that you built with Rails. Its got data in it. You’re a hero of the working class, a family man, and own a hybrid car, but you ride a bike to work. You vote, you contribute to the local neighbourhood newspaper, and you love Autumn because the leaves turn pretty colours. You’re not a fool.
Ulrich: Ulrich is a user of your site. Ulrich is a needy, web2.0 super-consumer. Ulrich started using your site when it was first profiled on TechCrunch, Ajaxian, and the Times magazine, but since then he’s found Theo’s website.
Theo: Theo runs a third-party website. Theo’s website has its own data too. It doesn’t have Ulrich’s data. Theo’s site wasn’t written about in TechCrunch, nor Ajaxian, nor even the local church foldover.

[Prelude] Theo just received his third email from Ulrich. Ulrich wants to port his data from Your website to Theo’s. Theo in turn, now has you on Skype…
You: I’m not very happy with the idea of giving up my data.
Theo: What happened to Open APIs? Making the user’s data transferrable with the user?
You: You read too many 37signals blog articles. Even Flickr – web two of all web two sites – doesn’t give up its data.
Theo: But some of my users…
You: You mean Ulrich. He’s emailed me too
Theo: Yeah, Ulrich. Ulrich wants to embed his data from your site into my site.
You: We already have a JSON API..
Theo: Its no good.
You: Works just fine. Returns lovely JavaScript data. Our users use it within our site to write their own widgets. Its one of our big draw cards.
Theo: Its no good. It doesn’t support callbacks.
You: What the?
Theo: Without a callback function there is no way my <script> tags can trigger any functionality when they retrieve the data. Ulrich could include his JavaScript widget into his page on mysite with your data if it was possible to specify a callback function in your API.
You: Instead of just raw JSON data?
Theo: Yeah. Lots of other sites offer it: CoComment, Delicious, Google Data, etc. They all allow callback=myFunction in the URL parameters.
You: [thinking to yourself] Rails don’t support callbacks out of the box. Bugger.
You: Sure, I’ll work on something.
Theo: Thanks, hopefully this gets Ulrich off my back. Should drive us both more traffic too.
You: The wikipedia page for JSON doesn’t mention callbacks.
Theo: Its a wiki. Add it yourself.
You: Smart arse.

The end.

Supporting JSON callbacks in Rails

Piece of cake.

But first, to support pure JSON API data objects from Rails, you have a handy to_json method on the Object class. So, in the partial/view you are rendering, e.g. person_json.rhtml (or person.rjs if you using MinusR plugin), you include:

<%= @object.to_json %>

Yeah, that was tough.

Now, for callback support. Change the above to:

<%= "#{params[:callback]}(" if params[:callback] -%>
<%= @object.to_json -%>
<%= ")" if params[:callback] -%>

Done.

Update: render_json method

Tim Lucas wrote a great article introducing a render_json method to add to your base ApplicationController. It will automatically return JSON data that you pass it, wrapped automatically in a callback or assigned to a variable (or both) if the parameters include a callback or variable value.

Read it, its good.