Slashdot Mirror
Slimmed Down MySQL Offshoot Drizzle is Built For the Web
Incon writes "Builder AU reports that Brian Aker, MySQL's director of architecture, has unveiled Drizzle, a database project aimed at powering websites with massive concurrency as well as trimming superfluous functionality from MySQL. Drizzle will have a micro-kernel architecture with code being removed from the Drizzle core and moved through interfaces into modules. Aker has already selected particular functionality for removal: modes, views, triggers, prepared statements, stored procedures, query cache, data conversion inserts, access control lists and some data types."
One of the big reasons they're popular is security. Without stored procedures, to allow a program (or the programmer who wrote it) access to a given data set, you'd have to grant it SELECT privileges on the table(s) containing that data. With a stored procedure, you just grant it permission to run that procedure, which might only return a subset of the data in the table(s).
Quick example: you have two tables, employees and employee_reviews. The employee table contains a unique ID, the employee's name, their salary, their start date, and other data. The employee_reviews has a foreign key linked to the employee's unique ID, the score for their latest review, and the text of the review. Without using stored procedures, to provide access to a given program to display the employee's name and the text of the review, it would need SELECT access on both tables; that exposes the employee's salary, which (we'll assume for this example) violates company policy.
With a stored procedure, though, you don't have this dilemma. The procedure would just select the appropriate columns and return them. This protects the employee's privacy and abiding by company policies.
God invented whiskey so the Irish would not rule the world.
Prepared Statements:
* You can avoid SQL injection (improved security)
* You can use "bind variables" (improved DB performance, improved security - see above)
Stored Procedures:
* You can write a transaction API in the database, and leave all that "ghastly" SQL out of your Java/PHP/languageOfChoice.
* Your data will outlive the cool-hip-language-of-the-day. Keeping that transaction API in the database means you don't have to rewrite all the data access/business rules when you want to change languages for your application.
There are more reasons, but these are the big ones.
Why, oh why, didn't I take the Blue Pill?
You need to keep in mind the difference between a Prepared Statement and a Stored Procedure.
A Stored Procedure is a function stored in the database that you call from the application. There are pros and cons, which I won't go into here. Personally I don't like them but I can see why some people love them.
A Prepared Statement, however, is the most useful tool you can use to protect yourself from SQL injection and every program should use them all the time. If there were a way to get rid of NON-prepared statements I think that'd be the right thing to do but I'd never use a DB that didn't support prepared statements. Let me illustrate the difference (Java-like pseudo-code):
Connection con = getConnection();
Statement stmt = con.createStatement("Select * from users where username = '" + username + "' and password = '" + password + "');
ResultSet rs = stmt.executeQuery();
PreparedStatement pstmt = con.prepareStatement("Select * from users where username = ? and password = ?"); // bind parameters
pstmt.setString(1, username);
pstmt.setString(2, password);
ResultSet rs = pstmt.executeQuery();
The PreparedStatement example looks like more code to write, and it is (But you can write utility functions to simplify this if you want).
But there are two advantages:
1. You can re-use the PreparedStatement if you need to run the query more than once with different parameters and this saves processing.
2. The bound parameters are automatically converted internally to parameters so that the SQL doesn't have to parse the parameters themselves. This protects you from SQL injection. If the query above had been used in the application's login module, anyone could log in if they typed in the administrator's username and the password ' OR 1 = 1; --
The reason is that in the first example the password contains SQL code. The SQL parser can't tell the difference between your parameter (the password) and the code because you passed it in as all one string. In the second case the parser sees that there is a variable and binds the value to that variable during execution of the statement. The variable isn't parsed as code.
Someone reading this is probably thinking "Just escape the parameters manually!", which helps, but by always using prepared statements and binding parameters you will never miss a parameter or have to worry about bugs in the escaping routine (PHP's mysql module has had several bugs in those routines).
I can't stress this benefit of prepared statements enough. It is so critical that we don't hire people at my company who don't understand this problem.
Sorry, it is incomprehensible that this sort of project would be started.
The problem with MySQL, to BEGIN WITH, is that it doesn't support enough SQL or the SQL it does support well enough, to construct efficient queries. What ends up happening is that you move your "data logic" to your application and out of your database. This means the database handles simpler queries, but returns more data. While these simple queries appear faster, they hit more data on the disk and actually cause the system to become I/O bound.
"Real" databases handle the "data logic" close to the data and can estimate the most efficient access to the data needed, thus REDUCING the I/O bottleneck, making more complex queries more efficient than simple queries. CPU time is virtually free with respect to data access.
Every time I see some Java, PHP, or .NET guy go off about MySQL being faster, I just shake my head. Data access is a real science grounded in math and the physical realities of actual computers and storage devices. A "good" database has YEARS of research and unless you are a god (and you are not) it will be very hard for you to beat it.
I've been in the business for about 28 years and I don't understand why software developers have this blind spot about databases. Maybe it is a "not written by me" attitude, but I just don't get it. A "good" database has so many facilities to make your data access efficient and fast as hell. Yet, most developers that I have to direct, simply refuse to learn about databases, specifically SQL. They go out of their way to write elaborate functionality in their language of choice that could have been constructed in a moderately interesting SQL query, that could be wrapped in a function and been more efficient.
The "drizzle" product is just another avoidance of an important semester of computer science that people don't want to understand and will ultimately create even more poorly designed web sites.