If you are familiar with both openark kit and common_schema, you’ll notice I’ve incorporated some functionality already working in openark kit into common_schema, essentially rewriting what used to be a Python script into SQL/QueryScript.
What was my reasoning for rewriting good code? I wish to explain that, and provide with a couple examples.
I’m generally interested in pushing as much functionality into the MySQL server. When using an external script, one:
- Needs the right dependencies (OS, Perl/Python version, Perl/Python modules).
- Needs to provide with connection params,
- Needs to get acquainted with a lot of command line options,
- Is limited by whatever command line options are provided.
- Has to invoke that script (duh!) to get the work done.
This last bullet is not so trivial: it means you can’t work some operation with your favorite GUI client, because it has no notion of your Perl script; does not run on the same machine where your Python code resides; simply can’t run those scripts for you.
With server-side code, functionality is accessible via any client. You run your operation via a query (e.g. CALL some_procedure). That can be done from your GUI client, your command line client, your event scheduler, your cronjob, all equally. You only need access to your MySQL server, which is trivial.
Of course, server side scripting is limited. Some stuff simply can’t be written solely on server side. If you want to consult your replicating slave; gracefully take action on user’s Ctrl+C, send data over the web, you’ll have to do it with an external tool. There are actually a lot of surprising limitations to things one would assume are possible on server side. You may already know how frustrated I am by the fact one can hardly get info from SHOW commands.
But, when it works, it shines
Let’s review a couple examples. The first one is nearly trivial. The second less so. Continue reading » “common_schema over traditional scripts”