Discover millions of ebooks, audiobooks, and so much more with a free trial

Only $9.99/month after trial. Cancel anytime.

MySQL 5.1 Plugin Development
MySQL 5.1 Plugin Development
MySQL 5.1 Plugin Development
Ebook523 pages3 hours

MySQL 5.1 Plugin Development

Rating: 0 out of 5 stars

()

Read preview

About this ebook

This book is written in a hands-on style that actively involves you while you read through its chapters. In every chapter you, along with the author, will be implementing one or more fully working example plugins. The process is accompanied with a detailed step-by-step explanation of the code, at the same time providing you with resources to create your own plugins. If you are a MySQL application developer or a programmer who wants to interface with MySQL server, this book is for you. Basic knowledge of C/C++, SQL, and MySQL in particular is assumed in the book, but no deep familiarity with any of the topics is required.
LanguageEnglish
Release dateAug 26, 2010
ISBN9781849510615
MySQL 5.1 Plugin Development

Related to MySQL 5.1 Plugin Development

Related ebooks

Information Technology For You

View More

Related articles

Reviews for MySQL 5.1 Plugin Development

Rating: 0 out of 5 stars
0 ratings

0 ratings0 reviews

What did you think?

Tap to rate

Review must be at least 10 words

    Book preview

    MySQL 5.1 Plugin Development - Andrew Hutchings

    Table of Contents

    MySQL 5.1 Plugin Development

    Credits

    About the Authors

    About the Reviewer

    Preface

    History of the Plugin API

    Idea of this book

    What this book covers

    What you need for this book

    Who this book is for

    Conventions

    Reader feedback

    Customer support

    Errata

    Piracy

    Questions

    1. Compiling and Using MySQL Plugins

    UDF libraries

    Linux

    Mac OS X

    Windows

    Installing a UDF

    Plugin libraries

    Linux

    Mac OS X

    Windows

    Installing a plugin

    Automatic builds, packaging

    UDFs and standalone plugins

    Plugins that are built from the MySQL source tree

    plug.in

    Makefile.am

    CMakeLists.txt

    Summary

    2. User Defined Functions

    Writing UDFs

    Why write UDFs

    Installing and using UDFs

    Defining UDFs

    Execution sequence of a UDF

    UDF error handling

    UDF security

    Gotchas with UDFs

    A constant integer output UDF

    An integer echoing UDF

    A simple static text output UDF

    A summing aggregate UDF

    Further reading

    Summary

    3. Daemon Plugins

    A look inside a Daemon plugin

    Why write a Daemon plugin

    Installing and using Daemon plugins

    The role of a version

    Defining Daemon plugins

    Status variables

    System variables

    A Hello World! Daemon plugin

    A system and status variables demo plugin

    A simple monitoring plugin

    System Status Variables plugin

    Summary

    4. Information Schema Plugins

    Why write Information Schema plugins

    Installing and using Information Schema plugins

    The creation of Information Schema plugins

    Defining Information Schema plugins

    A Static Table example

    A System Information plugin

    Summary

    5. Advanced Information Schema Plugins

    Accessing MySQL internals

    Condition pushdown

    Using condition pushdown

    A condition pushdown example

    A User Variables Information Schema plugin

    A Binary Logs Information Schema plugin

    Summary

    6. Full-text Parser Plugins

    The full-text parser plugin architecture

    Three roles of a full-text parser plugin

    Installing and using a full-text parser plugin

    Structure of a full-text parser plugin

    A PHP full-text parser

    Summary

    7. Practical Full-text Parsers

    Boolean parsers

    A Boolean full-text parser

    An Image Metadata processor

    How to access Exif data

    Writing the plugin

    Test run

    A Soundex full-text parser

    The Soundex algorithm

    The plugin

    Trying it out

    Summary

    8. Storage Engine Plugins

    Introducing storage engines

    A read-only storage engine

    ha_text.cc

    Summary

    9. HTML Storage Engine—Reads and Writes

    An idea of the HTML engine

    Flashback

    Creating, opening, and closing the table

    Reading data

    Updating the table

    Optimizing and analyzing

    What's left

    ha_html.h

    htmlutils.cc

    Compiling and linking

    Putting it all together

    Summary

    10. TOCAB Storage Engine — Implementing Indexes

    B-tree library

    Storage engine API for indexes

    Describing the engine

    Creating, opening, and closing the table

    Searching in the index

    Rows and keys

    Table scan and random access

    Inserting rows

    What's left

    Compiling and linking

    Putting it all together

    Possible extensions

    Summary

    A. Beyond MySQL 5.1

    Server services

    my_snprintf

    thd_alloc

    Audit plugins

    Authentication plugins

    How it works

    Authentication plugins—server side

    Authentication plugins—client side

    SQL extension by Storage Engine plugins

    Putting it to use

    Test drive

    Summary

    Index

    MySQL 5.1 Plugin Development


    MySQL 5.1 Plugin Development

    Copyright © 2010 Packt Publishing

    All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

    Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the authors, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.

    Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

    First published: August 2010

    Production Reference: 1190810

    Published by Packt Publishing Ltd. 32 Lincoln Road Olton Birmingham, B27 6PA, UK.

    ISBN 978-1-849510-60-8

    www.packtpub.com

    Cover Image by Asher Wishkerman (<[email protected]> )

    Credits

    Authors

    Sergei Golubchik

    Andrew Hutchings

    Reviewer

    Giuseppe Maxia

    Acquisition Editor

    Sarah Cullington

    Development Editor

    Swapna Verlekar

    Technical Editors

    Priya Darwani

    Chris Rodrigues

    Indexer

    Monica Ajmera Mehta

    Editorial Team Leader

    Akshara Aware

    Project Team Leader

    Ashwin Shetty

    Project Coordinator

    Zainab Bagasrawala

    Proofreader

    Kevin McGowan

    Graphics

    Geetanjali Sawant

    Production Coordinator

    Arvindkumar Gupta

    Cover Work

    Arvindkumar Gupta

    About the Authors

    Sergei Golubchik started modifying MySQL source code in 1998, and has continued as a MySQL AB employee since 2000. Working professionally with MySQL sources, he has had the opportunity to get to know and extend almost every part of the server code—from the SQL core to the utility functions. He was one of the primary architects of the Plugin API. After working for ten years in the ever-growing MySQL AB, and later in Sun Microsystems as a Principal Software Developer, he resigned to join a small startup company that works on a MariaDB—an extended version of the MySQL server, where he continues to do what he likes most—hack on MySQL, architecting, and developing MySQL/MariaDB Plugin API, making it even more powerful, safe, and easy to use.

    He works and lives in Germany, near Cologne, with his lovely wife and two kids.

    Andrew Hutchings is currently one of the top MySQL Support Engineers working at Oracle. He came from failing Computer Science at A-Level (British exams for 17-18 year olds) to working on, pretty much, every field of computing. His first development job was as an 8-bit assembly firmware developer for an environment monitoring company. He then went on to become a senior PHP and C/C++ developer as well as a DBA and system administrator for a large UK magazine chain. From there he was snapped up by Sun Microsystems as a MySQL Support Engineer specializing in MySQL Cluster and C/C++ APIs, much of this work involving deep analysis of the MySQL source code. Sun has since been bought by Oracle and Andrew is continuing his role there and was a tutorial speaker at the 2010 O’Reilly MySQL Conference & Expo. In his spare time Andrew is an active community developer of MySQL, MySQL Cluster, Drizzle, and MySQL Data Dumper (mydumper for short) as well as other small, related projects.

    I’d like to thank my wife, Natalie, and my children, Tomos and Oliver, for putting up with me while I was writing this book. I also wish to thank my colleagues, ex-colleagues, and others in the MySQL community (you know who you are) for their help and support in getting started with writing a book. And, of course, my co-author Sergei, without whom this whole book would not have been possible.

    About the Reviewer

    Giuseppe Maxia, a.k.a. The Data Charmer, is the MySQL Community Team Lead at Sun Microsystems. He is an active member of the MySQL community and a long time open source enthusiast. For the past 23 years he has worked in various IT related fields, with focus on databases, object-oriented programming, and system administration. He is fluent in Italian, English, Perl, SQL, Lua, C, Bash, and a good speaker of C++, French, Spanish, and Java.

    He works in cyberspace, with a virtual team.

    Preface

    Plugin based architecture is not something new, many popular software products use it. It is good both for the software product itself—if done properly it forces developers to structure the code and think about clean interfaces, which helps to keep the code maintainable over years—and for the users—as they can extend it without waiting for the vendor or choose from numerous third-party extensions.

    History of the Plugin API

    MySQL used to have pluggable extensions in a form of dynamically loaded functions since version 3.21.24 released in February 1998. Despite being quite limited in functionality, they were useful and people were using them. In early 2005, one of the authors of this book together with another MySQL developer, Sergey Vojtovich, were working on loadable parsers for MySQL full-text search, to be able to load a very specialized parser that one of their customers wanted. And Brian Aker, who was MySQL Director of Architecture at that time, suggested creating a unified interface for loadable modules. Based on this idea we developed the MySQL Plugin API—a generic framework that allowed loading of any functionality in the server—and Full-text Parser plugins were the first plugin type.

    Storage Engine API already existed in MySQL at that time—Michael Monty Widenius, the original author of MySQL, had it since the very first MySQL version, although he only added the handler class few years later, in 1999. This made Storage Engine plugins an easy target, and we added them as the next plugin type. Soon after that I, and another MySQL developer, Antony Curtis, extended Plugin API with the autotools support, the infamous plug.in file and MYSQL_PLUGIN_* macros that go in it, and implemented support for server variables, MYSQL_SYSVAR_* and MYSQL_THDVAR_* macros. Brian Aker added two more plugin types—Information Schema Table plugins and Daemon plugins.

    Life was going on even after MySQL 5.1 was released—Antony Curtis and I have developed Audit plugins. And very recently I and an external contributor, MIT student R.J. Silk, have completed the work on pluggable authentication and Authentication plugins were born.

    Meanwhile, Michael Monty Widenius had left MySQL and started a new company to work on MySQL fork, that he named MariaDB. Another former MySQL developer, Sanja Byelkin, and I have implemented the latest (at the time of writing) feature in the Storage Engine API, the engine defined attributes in the CREATE TABLE statement.

    Idea of this book

    Today, the MySQL Plugin API is a robust and proven feature. There are many third-party plugins both open and closed source, the most popular being Storage Engines, often accompanied by Information Schema tables, and Full-text parsers.

    However, the API documentation is not very helpful. If you are anything like me, you prefer fiction to a dictionary and a few good examples to a grammar description in the Backus-Naur form. The Plugin API documentation describes the functions and the structures but does not show how to use them. Tutorials, on the other hand, help the reader to understand how to use the API. Examples are important to illustrate the concepts and to bootstrap a new plugin project easily.

    This is where the idea of this book came from. We wanted to create a book that would allow readers to start writing plugins right away. With detailed tutorials and practical plugin examples, thoroughly explained line by line, highlighted common mistakes and clarified design decisions. And with code samples that you can start using in your projects. Not just the code you can copy, but more importantly, the code you understand—every line, every variable—as if you had written it yourself.

    But this book is not a reference manual. It does not contain an exhaustive list of all functions, classes, and macros of the MySQL Plugin API. The API is documented in the header files and in the MySQL manual. But to use it, you need to know what to look for. It is often said that asking the right question is half the right answer. This book teaches you to ask right questions. It gives detailed understanding—not just knowledge—of the MySQL Plugin API, and even if you will not have every piece of the puzzle, you will have most of them, you will know how they fit together, and you will be able to see the whole picture.

    What this book covers

    The book encourages consecutive reading, but chapters can be read in any order too. They are mostly independent, and, if needed, you can start reading from, for example, storage engine chapters without reading about full-text search parsers or UDFs. The book is structured as follows.

    Chapter 1, Compiling and Using MySQL Plugins lays the necessary foundation for the rest of the book, you will need it in all of the following chapters. It describes how to compile, link, and install UDFs and plugins. Even if you are only interested in, say, full-text parsers or storage engines, you may want to read this chapter first. It is not called Read Me First!!! only because we suspected that the editor may not have wanted a lot of exclamation marks in the chapter title.

    Chapter 2, User Defined Functions deals with UDFs - these dynamically loaded server extensions that first appeared in the server in 3.21.24, the great-grandparents of the MySQL Plugin API. Although, strictly speaking, UDFs are not MySQL Plugins—not part of the MySQL Plugin API—they are still used to load functionality in the server at runtime, just like plugins are, and sometimes they are used to complement the plugin functionality.

    Chapter 3, Daemon Plugins introduces the reader to the MySQL Plugin API. It talks about the most simple plugin type—Daemon plugins. It starts with the basic structure of a plugin—what a plugin declaration should look like, what plugin types are, and so on. Then it describes features common to all plugin types—initialization and de-initialization callbacks, status variables, and configuration system variables. After that it describes and analyzes line by line four Daemon plugin examples—from a simple plugin that prints Hello World! when loaded, to a system monitoring plugin that periodically logs the number of connections, to a system usage status plugin that displays the memory and I/O usage of the MySQL server.

    Chapter 4, Information Schema Plugins is dedicated to plugins that add tables to INFORMATION_SCHEMA. It describes all of the necessary data structures and ends with two plugin examples—a simple INFORMATION_SCHEMA table with versions of different MySQL subsystems and system usage statistics presented as an INFORMATION_SCHEMA table.

    Chapter 5, Advanced Information Schema Plugins delves more into the topic started in the previous chapter. It explains how to use condition pushdown and how to extract and display information from the server internal data structures. It presents three plugins that demonstrate condition pushdown, list all user variables, and all binary log files.

    Chapter 6, Full-text Parser Plugins is about plugins that extend the MySQL built-in full-text search. It describes all of the data structures and the code execution flow and illustrates all that with an example plugin that can parse PHP scripts.

    Chapter 7, Practical Full-text Parsers is devoted to the advanced applications of the plugins of this type. It explains how the search in Boolean mode works and contains more plugin examples—an Exif parser that allows users to search within embedded comments in image files, a Soundex parser that post-processes all words with a Soundex algorithm making the search invulnerable to typos and misspelled words, and a Boolean search parser plugin that supports AND and OR operators.

    Chapter 8, Storage Engine Plugins starts the discussion about the most complex and versatile plugin type in MySQL. It gives an overview of the main concepts of the Storage Engine API and thoroughly analyzes sources of the very simple read-only storage engine.

    Chapter 9, HTML Storage Engine - Reads and Writes continues the Storage Engine series. It presents a storage engine plugin that keeps table data in HTML tables and uses it to explain how to implement an updatable data stores.

    Chapter 10, TOCAB Storage Engine - Implementing Indexes concludes the Storage Engine part of the book. In this chapter, we develop a storage engine that supports indexes, using it to explain how the indexing part of the MySQL Storage Engine API works, how to build an engine that uses an external indexing library, and how to work around the incompatibilities of their APIs.

    Appendix talks about new MySQL Plugin API features, those that did not make it into MySQL 5.1. It describes Server Services, what they are and why they were introduced, the Audit plugins, the example of a plugin that audits security violations, Authentication plugins, with a plugin that uses USB devices to identify users, and engine attributes in the CREATE TABLE, demonstrating the feature with the help of the storage engine from Chapter 10.

    What you need for this book

    The book assumes basic knowledge of SQL and MySQL in particular, and until MySQL developers implement support for plugins in scripting languages, which would be great but can hardly happen any time soon, a certain level of familiarity with C, and for storage engines C++, will be required.

    Who this book is for

    We wrote this book for people who want to create MySQL plugins. They could be developers with a great idea for a new storage engine. But more often than not they will be application developers that need to solve a specific problem, whether it is searching text within Microsoft Word or Open Office documents, monitoring the database server with their company-wide monitoring framework, querying with SQL the multi-gigabyte files created with a 20 year old custom data storage library and joining them with new relational data, or adding MySQL to the company-wide single sign-on setup. All this and much more can be done with MySQL plugins.

    Conventions

    In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.

    Code words in text are shown as follows: The second argument of the name_init() function is a pointer to the UDF_ARGS structure.

    A block of code is set as follows:

    typedef struct st_field_info

    {

    const char* field_name;

    uint field_length;

    enum enum_field_types field_type;

    int value;

    uint field_flags;

    const char* old_name;

    uint open_method;

    } ST_FIELD_INFO;

    When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:

    static int tocab_init(void *p)

    {

    handlerton *tocab_hton = (handlerton *)p;

    tocab_hton->create = tocab_create_handler;

    tocab_hton->table_options = table_option_list;

     

    return 0;

    }

    Any command-line input or output is written as follows:

    shell$ mysql_config --cflags

    New terms and important words are shown in italics. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: Then in the C/C++ section we need to add the MySQL include path to Additional Include Directories.

    Note

    Warnings or important notes appear in a box like this.

    Tip

    Tips and tricks appear like this.

    Reader feedback

    Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.

    To send us general feedback, simply send an e-mail to<[email protected]>, and mention the book title via the subject of your message.

    If there is a book that you need and would like to see us publish, please send us a note in the SUGGEST A TITLE form on www.packtpub.com or e-mail.

    If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.

    Customer support

    Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.

    Tip

    Downloading the example code for this book

    You can download the example code files for all Packt books you have purchased from your account at http://www.PacktPub.com. If you purchased this book elsewhere, you can visit http://www.PacktPub.com/support and register to have the files e-mailed directly to you.

    Errata

    Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/support, selecting your book, clicking on the errata submission form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded on our website, or added to any list of existing errata, under the Errata section of that title. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.

    Enjoying the preview?
    Page 1 of 1