Hi all!
I’ve been reading through the requested features for the new client on the wiki here:
I think all the stuff on that link is excellent so far. I’d also like to request a feature that I think will be a really cool timesaver for DBAs and developers using Drizzle.
Macro Support
Remember, “way back when” you used Microsoft Excel and were able to start recording your actions, then when you stopped recording, Excel would store a “macro” of your actions that you could subsequently replay?
I think this would be incredibly useful for folks who do repetitive work in the console.
Sure, I know, I know…the first reaction folks will say is “but HEY, you guys removed stored procedures!” Yeah, yeah… but the feature I’m proposing here is different from stored procedures in the following ways:
- It’s entirely client-side. There is no server-side storage/cache, processing, parsing, or anything.
- It’s not limited to a small subset of SQL that stored procedures (at least in MySQL) are currently limited to. Anything the new client can do would be able to go into a macro.
- Since the client is in Python, the macros are themselves re-writable in a scripting language. This gives the recorded macros incredible flexibility.
- No fussing with SQL stored procedure permissions at runtime (you know, the silly INVOKER/DEFINER crap)
- Ability to interact with result sets in the macro. Just try doing that easily in a SQL stored procedure. Using CURSORs is incredibly clunk and ugly. Applying a Python function or closure/lambda on each of a result set is elegant and easy.
Imagine the following rough example interface…
drizzle> RECORD MACRO "sales_report_with_email" (to_email);
macro recording started.
drizzle> mode python;
in python mode.
python> import datetime
python> today= datetime.datetime.now().isoformat()
python> filename= "%s-%s-%s" % ("sales", to_email, today)
python> Ctrl-D
drizzle> SELECT * FROM sales
WHERE manager = @to_email; > csv(@filename);
drizzle> mode python;
In python mode.
python> report_txt= open(filename, "r+b").read()
python> import smtplib
python> mailserver = smtplib.SMTP('localhost')
python> mailserver.sendmail('theboss@company.com', to_email, report_txt)
python> mailserver.quit()
python> print "Mail sent to %s\n" % to_email
python> Ctrl-D
drizzle> STOP MACRO;
Macro "sales_report_with_email" saved.
drizzle> macro("sales_report_with_email", "myboss@company.com");
Mail sent to myboss@company.com
Pretty powerful, eh?
If you follow the flow above, you will notice the only real trick to solve is passing the macro’s arguments into the console’s variable array, and from the console’s variable array into the Python interpreter’s variable scope. But this is a fairly simple problem to solve…
Thoughts? Suggestions? If you’ve got comments, please feel free to share here, or on the Drizzle Discussion mailing list, or even update the wiki pages posted above. Thanks! 🙂