MySQL 5.1 Plugin Development

Andrew Hutchings, Sergei Golubchik

• Publisher: Packt Publishing
• Year: 2010
• eBook ISBN: 9781849510608
• Edition: 1
• Topic: Computer Science
• Subtopic: Desktop Applications
• Index: Computer Science

In Detail

MySQL has introduced a Plugin API with its latest version - a robust, powerful, and easy way of extending the server functionality with loadable modules on the fly. But until now anyone wishing to develop a plugin would almost certainly need to dig into the MySQL source code and search the Web for missing bits of the information.

This is the first book on the MySQL Plugin API. Written together with one of the Plugin API primary architects, it contains all the details you need to build a plugin. It shows what a plugin should contain and how to compile, install, and package it. Every chapter illustrates the material with thoroughly explained source code examples.

Starting from the basic features, common to all plugin types, and the structure of the plugin framework, this book will guide you through the different plugin types, from simple examples to advanced ones. Server monitoring, full-text search in JPEG comments, typo-tolerant searches, getting the list of all user variables, system usage statistics, or a complete storage engine with indexes - these and other plugins are developed in different chapters of this book, demonstrating the power and versatility of the MySQL Plugin API and explaining the intricate details of MySQL Plugin programming.

A hands-on guide for MySQL application developers to creating feature-rich plug-ins

Approach

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.

Who this book is for

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.

MySQL 5.1 Plugin Development

---

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

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 ...

Table of contents

• MySQL 5.1 Plugin Development