Developing a MySQL Workbench plugin
Having said that, most of the provided functionalities are intuitive and of daily use for developer and DBA staff alike. Moving beyond this rich out-of-the-box features set, Workbench empowers it's users to extend and develop their own custom features. For this purpose, we use both of the following:
- GRT: Generic RunTime is the internal system used by Workbench to hold model document data. It is also the mechanism by which Workbench can interact with Modules and Plugins.
- MForms: MForms is a small GUI toolkit library written for use in the MySQL Workbench project.
I've developed a small proof of concept to learn how to code a custom plugin. The purpose is to provide a simple refactoring feature for the script on the SQL editor (basically it's a find and replace):
To briefly explain the code, first we import all of the workbench (wb) modules, grt and mforms:
# import the wb moduleThen we declare the module information, its name, author and version:
from wb import *
# import the grt module
import grt
# import the mforms module for GUI stuff
import mforms
# define this Python module as a GRT module
ModuleInfo = DefineModule(name="Refactor", author="mjlmo", version="0.1")
@ModuleInfo.plugin("wb.text.refactor", caption = "Refactor Selection", input=[wbinputs.currentQueryBuffer()], pluginMenu= "SQL/Utilities")Finally, we insert the code to perform tha task in hand, in this case replace a selected text on the SQL editor with a given one on an input box. If there is no text selected, a message box will appear on screen stating that. Otherwise, we proceed with the refactoring operation.
@ModuleInfo.export(grt.INT, grt.classes.db_query_QueryBuffer)
To install this plugin, simply open MySQL Workbench, choose the "Scripting > Install Plugin/Module ..." option, browse to the "refactor_grt.py" file location and open it. Close and re-open the tool. There should be a new option on the "Tools > Utilities" menu called "Refactor Selection". Also, the plugin appear on the plugin manager:
You can read another full example regarding MySQL Workbench on how to define a module and define a plugin in Python here:
http://mysqlworkbench.org/workbench/doc/
MySQL - basic network security
Having secured our MySQL server and created a personal account to allow for remote administration, we can take one step further on blocking unwanted access to our database server. For this example, we'll continue to use the Ubuntu 14.04.2 LTS Server installed and configured on the previous posts.After booting up the server, running a network check for connections, lists our server listening on ports 22 and 3306 (mysql):
Let's adopt the following policy:
- SSH port is open to the network and available for sysadmin staff. Access control will be implemented on dedicated network firewalls;
- MySQL port is closed and has to be authorized on demand, on a client machine adress basis.
sudo ufw allow from 192.168.204.XXX to 192.168.204.132 port 3306
stuntman@MuSQL-Lab:~$ netstat -at
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 *:ssh *:* LISTEN
tcp 0 0 192.168.204.132:mysql *:* LISTEN
tcp 0 0 192.168.204.132:mysql 192.168.204.X:54617 ESTABLISHED
tcp 0 0 192.168.204.132:mysql 192.168.204.X:54618 ESTABLISHED
tcp 0 64 192.168.204.132:ssh 192.168.204.X:54483 ESTABLISHED
tcp6 0 0 [::]:ssh [::]:* LISTEN
stuntman@MuSQL-Lab:~$stuntman@MuSQL-Lab:~$ sudo ufw status
Status: active
To Action From
-- ------ ----
22/tcp ALLOW Anywhere
192.168.204.132 3306 ALLOW 192.168.204.X
22/tcp (v6) ALLOW Anywhere (v6)
jqBootstrap ignores required field custom error message
jqBootstrap is a JQuery validation framework for bootstrap forms. While using it to validate required fields, on the form submission I got a tooltip with a “required field” message but not the custom I had put on the markup.
In short, “required” is part of the HTML5 standard and it does what it’s supposed to, validates the input on a given field.
Said that and given that the “sniffHtml” setting didn’t do the trick, I got things done with two control events that customize the HTML5 behaviour:
<input id=“myHtmlControl” name=“myHtmlControl” type=“text” required oninvalid=“this.setCustomValidity(‘You really should write on this field.’)” onchange=“this.setCustomValidity(”)“>
Hope this saves someone time :)
SQL Server 2016 Installation Changes
The installation assistant of Microsoft SQL Server database had a major leap in customization features since the 2008 versions.
Again, on the yet to be released SQL Server 2016, the installation process offers the following new possibilities:
PolyBase allows you to use T-SQL statements to access data stored in Hadoop or Azure Blob Storage and query it in an adhoc fashion. However, installing Oracle Java Runtime Environment will be a requirement.
Concerning the TempDB, by default, setup will add as many tempdb files as the CPU count or 8, whichever is lower.
Care to try it out? Dowload it at:
https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016/
MySQL sample databases: for testing and training
Sometimes it's useful to have a set of data prepared to be used on a fresh MySQL install for testing purposes. Or you might be preparing some training or workshop and want to prepare examples with fake information. For MySQL there are some choices available:- Employees Sample Database: provides a combination of a large base of data (approximately 160MB) spread over six separate tables and consisting of 4 million records in total.
- Sakila Sample Database: is intended to provide a standard schema that can be used for examples in books, tutorials, articles, samples, and so forth. Sakila sample database also serves to highlight the latest features of MySQL such as Views, Stored Procedures, and Triggers.
- BIRT Sample Database: The schema is for Classic Models, a retailer of scale models of classic cars. The database contains typical business data such as customers, orders, order line items, products and so on.
JSON on the database? No thanks!
JSON is a wonderful thing for developers. It’s becoming more and more the de facto standard for information interchange and asynchronous operations inside applications. So much so that database engines are integrating it on their latest release. Examples of that are:
I wont even challenge the advantages and performance measurements on such primitives, but both from a database administrator perspective, as well as a developer’s one, I think this is a bad idea!
The major pitfalls on using this native integration would be:
- Injecting database vendor dependency on the application, since these enhancements work on specific primitives not part of SQL;
- Processing expense and memory allocation is shifted on to the database server, whose main role should be serving data not formatting or performing interpretations from it.
So you might end up with a great application that uses the most recent features from your specific RDBMS but from then on you are stuck with it and hope you don’t forget to perform the server sizing including the JSON processing load.
Still on Azure security...
Tired of so much skepticism on hybrid cloud security when compared to private infrastructures, let me ask how many of your corporate data centers, besides the usual VPN linked to the office, have MFA?
Watch and learn:
https://channel9.msdn.com/Blogs/Windows-Azure/WA-MFA-Overview?ocid=player
Save up to 50% on all books & eBooks - Microsoft Press
Discount code MOREPRESS confers a 35% discount off the list price of one product or 50% off the list price of two or more products purchased on microsoftpressstore.com in a single transaction.
Check it out here:
Discount offer expires September 9, 2015.
Print tab separated values as table using MySQL
Using mysql command line utility to get recordsets, the data rows alignment and line breaks are often a mess. You can use the command line tool on a mysql database server to get a set of rows into an output TSV file like so:shell> mysql -u your_user -p < your_statement.sql > data.csvI came up with the following python script to grab the output file and pretty print:You can put your own filename instead of 'data.csv'. And of course, this script also works for other TSV files that don't come from mysql. To run the script you should install the python tabulate package:
https://pypi.python.org/pypi/tabulate
Guide to protecting your virtual infrastructure and cloud-based applications on Azure
For those still aprehensive about how the cloud can preserve your data center security mechanisms, this is a must read:
Microsoft Azure Network Security
Learn about the numerous security controls available in Azure, and get guidelines for protecting Azure virtual machines and Azure cloud services in this new white paper.
No comments :
Post a Comment