Table Of ContentMySQL 5.1 Plugin Development
Extend MySQL to suit your needs with this unique guide
into the world of MySQL plugins
Sergei Golubchik
Andrew Hutchings
BIRMINGHAM - MUMBAI
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 Editorial Team Leader
Sergei Golubchik Akshara Aware
Andrew Hutchings
Project Team Leader
Ashwin Shetty
Reviewer
Giuseppe Maxia
Project Coordinator
Zainab Bagasrawala
Acquisition Editor
Sarah Cullington
Proofreader
Kevin McGowan
Development Editor
Swapna Verlekar
Graphics
Geetanjali Sawant
Technical Editors
Priya Darwani
Chris Rodrigues Production Coordinator
Arvindkumar Gupta
Indexer
Monica Ajmera Mehta 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.
Table of Contents
Preface 1
Chapter 1: Compiling and Using MySQL Plugins 9
UDF libraries 9
Linux 10
Mac OS X 10
Windows 10
Installing a UDF 15
Plugin libraries 15
Linux 15
Mac OS X 16
Windows 16
Installing a plugin 19
Automatic builds, packaging 19
UDFs and standalone plugins 19
Plugins that are built from the MySQL source tree 20
plug.in 21
Makefile.am 22
CMakeLists.txt 24
Summary 24
Chapter 2: User Defined Functions 25
Writing UDFs 25
Why write UDFs 26
Installing and using UDFs 27
Defining UDFs 31
Execution sequence of a UDF 37
UDF error handling 37
UDF security 38
Gotchas with UDFs 38
Table of Contents
A constant integer output UDF 39
An integer echoing UDF 41
A simple static text output UDF 43
A summing aggregate UDF 46
Further reading 50
Summary 50
Chapter 3: Daemon Plugins 51
A look inside a Daemon plugin 51
Why write a Daemon plugin 51
Installing and using Daemon plugins 52
The role of a version 54
Defining Daemon plugins 54
Status variables 58
System variables 60
A Hello World! Daemon plugin 65
A system and status variables demo plugin 68
A simple monitoring plugin 72
System Status Variables plugin 76
Summary 81
Chapter 4: Information Schema Plugins 83
Why write Information Schema plugins 83
Installing and using Information Schema plugins 84
The creation of Information Schema plugins 85
Defining Information Schema plugins 86
A Static Table example 91
A System Information plugin 95
Summary 100
Chapter 5: Advanced Information Schema Plugins 101
Accessing MySQL internals 101
Condition pushdown 102
Using condition pushdown 103
A condition pushdown example 105
A User Variables Information Schema plugin 110
A Binary Logs Information Schema plugin 115
Summary 119
Chapter 6: Full-text Parser Plugins 121
The full-text parser plugin architecture 122
Three roles of a full-text parser plugin 122
Installing and using a full-text parser plugin 123
Structure of a full-text parser plugin 124
[ ii ]
Table of Contents
A PHP full-text parser 130
Summary 136
Chapter 7: Practical Full-text Parsers 137
Boolean parsers 137
A Boolean full-text parser 139
An Image Metadata processor 145
How to access Exif data 145
Writing the plugin 146
Test run 150
A Soundex full-text parser 152
The Soundex algorithm 153
The plugin 154
Trying it out 157
Summary 159
Chapter 8: Storage Engine Plugins 161
Introducing storage engines 161
A read-only storage engine 162
ha_text.cc 164
Summary 179
Chapter 9: HTML Storage Engine—Reads and Writes 181
An idea of the HTML engine 181
Flashback 182
Creating, opening, and closing the table 184
Reading data 187
Updating the table 190
Optimizing and analyzing 193
What's left 194
ha_html.h 195
htmlutils.cc 197
Compiling and linking 198
Putting it all together 199
Summary 201
Chapter 10: TOCAB Storage Engine—Implementing Indexes 203
B-tree library 203
Storage engine API for indexes 205
Describing the engine 208
Creating, opening, and closing the table 213
Searching in the index 217
Rows and keys 224
Table scan and random access 230
[ iii ]
