One of the problems that comes with age is that there is so much 'baggage' filling valuable brain area that it sometimes requires a mental sweeping before trying to learn a new skill. I am still getting rid of FORTRAN and COBOL factoids but need to come up to speed on extending MySQL for some talks I am giving. So away with the PROCEDURE DIVISION stuff...
The MySQL Shell or Mysqlsh is very extensible and it is easy to create a plugin for some handy routines. Your routines can go in functions and there are examples below. The tricky part is that you have to tell Mysqlsh that you are creating an extension and then hook up your function(s) to that extension object. Finally that extension object has to be registered with the shell.
So below are two functions which query the MySQL World Database for the contents of the world.city and the world.country tables. The first thing for both of these functions is to make sure we are connected to the MySQL database instance or, in other words, that our shell has a session establish with the server. Then it is a quick query and dump the results.
Now the tricky stuff!
We need an object for our extension plugin_obj = shell.create_extension_object()
and then we provide the informationfor our functions with shell.add_extension_object_member(plugin_obj, "city", show_tables, {"brief" : "Dave's demo 2 - city"} ) and
shell.add_extension_object_member(plugin_obj, "countryinfo", show_country, {"brief" : "Dave's demo 2 - country"} ) before we tell the shell to use our new plugin with shell.register_global("world", plugin_obj, {"brief": "A 2nd demo for Dave"}) to tell the shell we want these functions available under the name world.
When a new Mysqlsh is session is started, we can use \h to list the available plugins. And we look for our new plugin.
If you type 'world' and then a TAB key, you will be able to see that the two functions are ready!
The you can type the name of the function desired and then the query will run.
I have omitted the output from the query. Please note that you can use plugins written in either JavaScript or Python and that you do can run programs written in either language from the other language mode.
The Code
""" Lists all the records in the world.city table
Simple function to query records in table
Args:
session (object): Option session object or use current mysqlsh session
Returns:
Nothing
"""
if session is None:
session = shell.get_session()
if session is None:
print("No session specified - pass a session or connect shell to database")
return
if session is not None:
r = session.run_sql("SELECT * FROM world.city")
shell.dump_rows(r)
def show_country(session=None):
if session is None:
session = shell.get_session()
if session is None:
print("No session specified - pass a session or connect shell to database")
return
if session is not None:
r = session.run_sql("SELECT * FROM world.country")
shell.dump_rows(r)
plugin_obj = shell.create_extension_object()
shell.add_extension_object_member(plugin_obj, "city", show_tables, {"brief" : "Dave's demo 2 - city"} )
shell.add_extension_object_member(plugin_obj, "countryinfo", show_country, {"brief" : "Dave's demo 2 - country"} )
shell.register_global("world", plugin_obj, {"brief": "A 2nd demo for Dave"})
