Become a fan of Slashdot on Facebook

 


Forgot your password?
Close
typodupeerror
×
Book Reviews

MySQL 5.1 Plugin Development 44

Posted by samzenpus from the read-all-about-it dept.
Michael J. Ross writes "If you were to ask some database developers to cite their favorite strategies for expanding the functionality of the relational database management systems with which they work, you would probably hear a variety of answers. One individual might recommend the use of an alternate database engine optimized for the given application. Another might explain the many advantages of using stored procedures to replace SQL queries embedded in the source code of any programs that connect to databases. But one answer you likely would not receive involves changing the internals of the database engine itself. With the latest major release of MySQL, developers using that particular RDBMS are now able to extend the capabilities of the built-in database engines, by creating plug-ins. This is the topic of a book by Sergei Golubchik and Andrew Hutchings: MySQL 5.1 Plugin Development." Read on for the rest of Michael's review.
MySQL 5.1 Plugin Development
author Sergei Golubchik and Andrew Hutchings
pages 288 pages
publisher Packt Publishing
rating 9/10
reviewer Michael J. Ross
ISBN 978-1849510608
summary A practical and example-rich introduction to developing MySQL plug-ins.
This title was released by Packt Publishing on 26 August 2010, under the ISBN 978-1849510608. On the publisher's page for the book, visitors will find a detailed description, the table of contents, a sample chapter (the fourth one in the book, "Information Schema Plugins," as a PDF file), and links to purchase the print and/or electronic versions of the book. There is also a link for downloading all of the sample code used in the book, except for the commands found in the first chapter. The sample code is sufficient to demonstrate the complexity of the subject area, and thus it is good that both authors possess a lot of experience in all of the primary technologies discussed in the book — particularly MySQL. In fact, Sergei Golubchik was one of the principal architects of the MySQL Plug-in application programming interface (API). Incidentally, in the "About the Authors" section, we read that he "has continued as a MySQL AB employee since 2000," which makes it sound as though he is still employed there (now Oracle); but then we are told that he resigned to join a startup firm, which is a bit confusing.

The book spans 288 pages, most of which are organized into ten chapters, followed by an appendix and an index. The first chapter explains the details of how to compile, link, and install MySQL User Defined Functions (UDFs), as well as proper MySQL plug-ins. For those people working on Windows platforms, numerous screenshots are provided, showing how to work with Microsoft Visual Studio (which is freely available). Readers learn how to use MySQL command-line utilities for building and packaging plug-ins, and the options needed to do so. The subsequent chapter focuses on UDFs — both normal and aggregate ones — which are technically not part of the MySQL Plugin API, although they may be in the future. However, they can be thought of as precursors to true plug-ins, because they are written in C/C++, loaded dynamically at runtime, and extend the capabilities of the MySQL server — in this case, by being callable from within SQL statements. The authors explicate how to install and utilize UDFs, and provide several examples.

The book's remaining chapters explore different types of plug-ins, starting with the most basic kind of all, Daemon plug-ins, which can run code utilizing a dedicated thread in the mysqld server process. Readers are shown how Daemon plug-ins are structured — including their declarations, types, status variables, and configuration system variables. To demonstrate these components, the authors dissect four separate sample plug-ins, line by line, with a great deal of helpful commentary. The next two chapters, 4 and 5, delve into schema-related plug-ins, starting with those that create tables, and ending with more advanced topics, such as how to access and output information about a server's internal data structures. These two chapters present almost half a dozen examples, as equally detailed as those of the earlier material.

During the past several years, all relational database systems are seeing increased use of full-text parsing, for various purposes. Chapters 6 and 7 show the reader how to create plug-ins that supplement the full-text search capabilities already baked into MySQL. The first sample plug-in presented by the authors could be used by PHP programmers for parsing their scripts, while another sample could be used by developers who need to match user input (which may include typos), using a Soundex algorithm. The final three chapters cover many aspects of storage engines, ranging from a basic read-only engine to a more complex one that supports indexes. The book concludes with an appendix that surveys the primary enhancements to the Plug-in API that database developers may see in versions of MySQL after 5.1.

On the publisher's site, there are no reported errata, but here are some that I found in just the first couple pages, to get the list started: "class [a] to" (page 1), "on [a] MySQL fork" (page 2), and "ask [the] questions" (page 2). Also, countless phrases and sentences in the book are oddly constructed, with multi-word adjectives missing hyphens, commas used where semicolons are called for, and sometimes both mistakes committed in the same sentence, such as the very first sentence of the preface. In fact, the lead author admits that he prefers reading fiction to a dictionary. Regardless, the information and instruction provided by the authors are generally clear to the reader, and ably illustrated with the sample code.

The book and thus the reader benefit greatly from the extensive MySQL experience and knowledge of the authors, reflected in the depth of coverage of the various topics. MySQL 5.1 Plugin Development brings together valuable information that is otherwise tedious to find — scattered throughout the API source code, official documentation, and online forum threads. For any programmer interested in unleashing the full potential of their MySQL servers through the creation and use of plug-ins, this book is an essential resource.

Michael J. Ross is a freelance website developer and writer.

You can purchase MySQL 5.1 Plugin Development from amazon.com. Slashdot welcomes readers' book reviews -- to see your own review here, read the book review guidelines, then visit the submission page.

*

This discussion has been archived. No new comments can be posted.

MySQL 5.1 Plugin Development More

MySQL 5.1 Plugin Development

Comments Filter:

  • Awesome! (Score:1, Insightful)

    by Anonymous Coward

    Cram business logic that should properly exist at the application level into a crappy SQL platform!

    • Re: (Score:3, Interesting)

      by omnichad ( 1198475 )

      That's only partly true. For example - say you have "products" and "categories." When you delete a category, all products should either revert to a default category, no category, or move to an archive. That's a database question, not business logic. Why shouldn't the database handle the delete so that it handles the little details across multiple implementations.

      • Re:Awesome! (Score:5, Insightful)

        by JonySuede ( 1908576 ) on Monday November 01, 2010 @01:43PM (#34092688) Journal

        you can use a trigger and a stored proc for this.... but if you forget to document it in the written design doc, I will give you a terrible beating

        and by the way it is a business logic question, as an exercise I will let you find why

        • Didn't say it had to be a plugin - just arguing against this sort of thing being in application code.

      • When you delete a category, all products should either revert to a default category, no category, or move to an archive. That's a database question, not business logic. W

        A rule like that can only come from a business requirement in the first place -- or did the developer or DBA arbitrarily make that decision?

    • Re:Awesome! (Score:5, Insightful)

      by magarity ( 164372 ) on Monday November 01, 2010 @01:41PM (#34092650)

      Cram business logic that should properly exist at the application level into a crappy SQL platform!

      After you work in BI for a while you'll come to realize that where business logic is kept isn't nearly as important as that it's only kept in one place.

      • Re:Awesome! (Score:4, Informative)

        by Angst Badger ( 8636 ) on Monday November 01, 2010 @02:29PM (#34093358)

        After you work in BI for a while you'll come to realize that where business logic is kept isn't nearly as important as that it's only kept in one place.

        God yes. I used to hate the idea of stored procedures and the like because that meant losing control (as an application programmer). But over time, I've come to realize that what it really means is that I don't have to worry about all the other application programmers implementing the same goddamn thing in a dozen different places.

    • This book isn't about that. This book is about changing the storage and retrieval mechanism to best fit your applications data needs. Its the same sort of change as switching from a MS access database to a MS SQL Server, if you were moving your product from a desktop app to the web.

  • This might be a hit, like addons in Mozilla Firefox.

  • I think the ability to modify the functionality of the RDBMS could be very useful in some instances, however if the plugin is poorly crafted or maliciously crafted it could open up interesting new attack surfaces. Any thoughts from the security minded out there?

    • Re: (Score:1)

      by satuon ( 1822492 )

      It's true, but that is the problem of third-party software. The same issues hold for Firefox addons, and for the ability to run programs written by anyone in your PC, as opposed to, say, the iPad. It's up to the end-user to decide what to install and what not to install.

    • Yes, as with many plugin APIs there is the possibility of poor or malicious code opening up an attack vector. Off the top of my head I suspect the biggest risk would be from say bad string process in the Full-Text parser plugins which crashes the server. I would expect most plugins to come from in-house development where there is a special need for a unique setup and this type of user would be hacking code on the server anyway with the same (or worse) security implications. Andrew Hutchings (co-author)
      • From your experience with the platform, are there any safeguards in place that prevent plugins from breaking out of their place in memory and reeking havoc? Are the plugins allowed to modify the underlying data structure or is there a specific interface they use to access the data?
        • Unfortunately no... Well, you have the standard private/protected/etc... for the C++ parts. But I suspect it would not be difficult to get around those. The basic API has basic pre-defined functions, but for the more complex types (such as storage engines) you need to include some aspects of the original MySQL source code when compiling. The original idea of the MySQL Plugin API was to make it easier for people who already (or intend to) hack in raw code to the MySQL source. So the plugin is dynamicall
    • No more or less than any other plugin architecture; or even executing third party code at all. If you don't trust the provider, don't install it.
  • but do the addons account for full normalisation?

    • As with all things database, it's only as normalized as the meat between the chair and the keyboard wants it to be.

  • It would be nice to have something like mod_auth_kerb/ldap that worked with mysql. It's been a "bug" since 2004, and a requested feature for longer than that. I know not everyone has multiple users in a mysql DB, but quite a few do.

    • Re: (Score:3, Informative)

      by TheLinuxJedi ( 1924766 )
      Yes, this plugin type is in MariaDB and should be coming to MySQL soon. There are examples in the Appendix of the book. As a side note we already have authentication plugins in Drizzle (what I work on now). Andrew Hutchings (co-author)

      • It would be useful if there were a way to use all kinds of existing Apache plugins to deliver their functions to MySQL. Auth plugins are an obvious win; other functions not so obvious could still make code reuse deliver real productivity and wider functionality.

        Indeed, an interesting MySQL plugin would be an Apache httpd that could run regular Apache plugins. Or MySQL packaged as an Apache plugin, for inline SQL embedded in Apache server side scripting.

        In a similar vein, JBoss running as a plugin exposing

  • Database engines have to be highly reliable, and MySQL has some good ones. They're very difficult to write and get correct. There's considerable theory and formalism involved. Real Q/A is needed. This is not a place for "crowdsourcing".

  • I' guessing in which table to put the subwoofer...

  • Table Index Plugin? (Score:3, Interesting)

    by Doc Ruby ( 173196 ) on Monday November 01, 2010 @03:38PM (#34094330) Homepage Journal

    If all I wanted to change in MySQL was the function that searched a table index for matching records, how would I go about writing a plugin for just that operation? How about a plugin that only creates/updates an index table (perhaps in a nonstandard format, to be read by my index search plugin)?

    Is it feasible to replace just those two feature groups with a plugin, rather than the entire database engine?

    • Re: (Score:2, Insightful)

      by TheLinuxJedi ( 1924766 )
      At the moment you have two options. The first is the Full-Text parser plugins, but this probably won't help you if you are handling INTs and may not quite be flexible enough for what you are looking for. If this is what you are interested in you may want to look at how products like Sphinx search engine does it, as that has a MySQL plugin. The second is indeed the storage engine plugin API. You could theoretically take an existing engine (such as the InnoDB plugin) and modify it in plugin form to work h

  • How about a plugin that replaces any replaceable default MySQL feature with the execution of a stored procedure that's stored in that MySQL server instance?

    • 'Feature' is a very broad definition. But if you are talking about functions you could use Stored Functions and/or UDFs. These would, however, need a different name for the function call. Andrew Hutchings (co-author)

      • For example, could I write a plugin that intercepts the call to the implementation of "=" (eg. in "SELECT cola FROM tableb WHERE colb = colc"), that executes a Stored Function or UDF returning a boolean?

        • Ah, I see what you mean. Not yet, it would be a real gem if you could alter the lexical parser in a plugin but it is really difficult to do at the moment. The closest alternative would probably be mysql-proxy, in which you could write a LUA script to intercept that and do what you wanted.

          • So I take it that there's no option to replace the entire lexical parser code, or perhaps some larger functional block that includes the lexical parser, with a "plugin" (install-time selection, not compile-time) that just rewrites the "=" operator, replacing the default code that executes "=" the conventional way.

            • Not yet unfortunately, it would be quicker to edit the parts of the MySQL source than to try this via. a plugin right now. There is a book called Understanding MySQL Internals that could help with this if it something you wanted to look in to. I know that is something they want to eventually do at MySQL (and something we want to eventually do for Drizzle too), but it is much harder to implement than it sounds.

              • Thanks for this info.

                I've tried a few times to start up a project that inserts a MySQL engine between Linux filesystem calls and the storage subsystem, so the filesystem has a SQL API, but the teams quickly fell apart.

                Maybe with that UMI book and your plugin book I might find a better approach that would produce a result this time.

        • Re: (Score:1, Interesting)

          by Anonymous Coward

          Postgresql and Oracle let you overload equality on user-defined types in a manner that does precisely what you want. Postgresql's support for this is particularly robust, letting you overload any operator or define new ones, and overloaded equality and comparison operations play nice with indexes, in a way that the portable solution to this problem (selecting from a view) does not.

          Not trying to be a platform weenie here, just pointing out a solution you might be able to use if you're really looking for tha

  • MS SQLServer has a feature which structures a column as a BLOB type, that can be specified to store each record's field in a separate file managed by SQLServer. Those files in that column can have a separate fulltext index supporting matching and proximity operators. Is there a plugin for MySQL that delivers those kinds of features?

  • Is there a MySQL plugin that allows bulk insertion or other simple processing of MS Excel .XLS and .XLSX format files?

  • So does this mean we can custom extend the SQL language in MySQL, personally I like to be more aggregate functions, and ability to program aggregate functions at will.

    ---

    SQL Programming [feeddistiller.com] Feed @ Feed Distiller [feeddistiller.com]

    • As far as aggregate functions go, yes. The UDFs covered in Chapter 2 will do this. But if you want to modify the underlying SQL language (by adding new keywords that aren't functions for example) then no, this cannot yet be done with plugins.

Slashdot Top Deals

He who steps on others to reach the top has good balance.

Close