Contents

About the Author

About the Technical Reviewers

Acknowledgments

Introduction

Part 1: Getting Started with MySQL Development

Chapter 1: MySQL and The Open Source Revolution

What Is Open Source Software?

Why Use Open Source Software?

Is Open Source Really a Threat to Commercial Software?

Legal Issues and the GNU Manifesto

Let the Revolution Continue!

Developing with MySQL

Why Modify MySQL?

What Can You Modify in MySQL? Are There Limits?

MySQL’s Dual License

So, Can You Modify MySQL or Not?

Guidelines for Modifying MySQL

A Real-World Example: TiVo

Summary

Chapter 2: The Anatomy of a Database System

Types of Database Systems

Object-Oriented Database Systems

Object-Relational Database Systems

Relational Database Systems

Relational Database System Architecture

Client Applications

Query Interface

Query Processing

Query Optimizer

Internal Representation of Queries

Query Execution

File Access

Query Results

Relational Database Architecture Summary

The MySQL Database System

MySQL System Architecture

SQL Interface

Parser

Query Optimizer

Query Execution

Query Cache

Cache and Buffers

File Access via Pluggable Storage Engines

Summary

Chapter 3: A Tour of the MySQL Source Code

Getting Started

Understanding the Licensing Options

Getting the Source Code

The MySQL Source Code

Getting Started

The mysqld_main() Function

Handling Connections and Creating Threads

Parsing the Query

Preparing the Query for Optimization

Optimizing the Query

Executing the Query

Supporting Libraries

Important Classes and Structures

MySQL Plugins

Coding Guidelines

General Guidelines

Documentation

Functions and Parameters

Naming Conventions

Spacing and Indenting

Documentation Utilities

Keeping an Engineering Logbook

Tracking Your Changes

Building the System for the First Time

Summary

Chapter 4: Test-Driven MySQL Development

Background

Why Test?

Benchmarking

Profiling

Introducing Software Testing

Functional Testing vs. Defect Testing

MySQL Testing

Using the MySQL Test Suite

MySQL Benchmarking

MySQL Profiling

Summary

Part 2: Extending MySQL

Chapter 5: Debugging

Debugging Explained

The origins of debugging

Debugging Techniques

Basic Process

Approaches to Debugging

Inline Debugging Statements

Error Handlers

External Debuggers

Stand-alone Debuggers

Interactive Debuggers

GNU Data Display Debugger

Bidirectional Debuggers

Debugging MySQL

Inline Debugging Statements

Error Handlers

Debugging in Linux

Using gdb

Using ddd

Debugging in Windows

Summary

Chapter 6: Embedded MySQL

Building Embedded Applications

What Is an Embedded System?

Types of Embedded Systems

Embedded Database Systems

Embedding MySQL

Methods of Embedding MySQL

Bundled Server Embedding

Deep Embedding (libmysqld)

Resource Requirements

Security Concerns

Advantages of MySQL Embedding

Limitations of MySQL Embedding

The MySQL C API

Getting Started

Most Commonly Used Functions

Creating an Embedded Server

Initializing the Server

Setting Options

Connecting to the Server

Running Queries

Retrieving Results

Cleanup

Disconnecting from and Finalizing the Server

Putting It All Together

Error Handling

Building Embedded MySQL Applications

Compiling the Library (libmysqld)

Compiling libmysqld on Linux

Compiling libmysqld on Windows

What About Debugging?

What About the Data?

Creating a Basic Embedded Server

Linux Example

Windows Example

What About Error Handling?

Embedded Server Application

The Interface

The Data and Database

Creating the Project

Design

Managed vs. unmanaged code

Database Engine Class

Customer Interface (Main Form)

Administration Interface (Administration Form)

Detecting Interface Requests

Compiling and Running

Summary

Chapter 7: Adding Functions and Commands to MySQL

Adding User-Defined Functions

CREATE FUNCTION Syntax

DROP FUNCTION Syntax

Creating a User-Defined Library

Adding a New User-Defined Function

Adding Native Functions

Compiling and Testing the New Native Function

Adding SQL Commands

Adding to the Information Schema

Summary

Chapter 8: Extending MySQL High Availability

What is Replication?

Why use Replication?

How Does Replication Achieve High Availability?

Basic Replication Setup

Requirements for Replication

Configuring the Master

Configuring the Slave

Connecting the Slave to the Master

Next Steps

The Binary Log

Row Formats

The mysqlbinlog Client

SHOW BINLOG EVENTS Command

Additional Resources

Replication Architecture

A Brief Tour of the Replication Source Code

Replication Source Code Files

Log Events Explained

Types of Log Events

Execution of Log Events

Extending Replication

Global Slave Stop Command

Slave Connect Logging

Summary

Chapter 9: Developing MySQL Plugins

MySQL Plugins Explained

Types of Plugins

Using MySQL Plugins

The MySQL Plugin API

Compiling MySQL Plugins

The RFID Authentication Plugin

Concept of Operations

RFID Module

Architecture of Authentication Plugins

Building the RFID Authentication Plugin

RFID Authentication in Action

Further Work

Summary

Chapter 10: Building Your Own Storage Engine

MySQL Storage Engine Overview

Storage Engine Development Process

Source Files Needed

Unexpected Help

The Handlerton

The Handler Class

A Brief Tour of a MySQL Storage Engine

The Spartan Storage Engine

Low-Level I/O Classes

The Spartan_data Class

The Spartan_index Class

Getting Started

Stage 1: Stubbing the Engine

Creating the Spartan Plugin Source Files

Adding the CMakeLists.txt File

Compiling the Spartan Engine

Testing Stage 1 of the Spartan Engine

Stage 2: Working with Tables

Updating the Spartan Source Files

Updating the Class File

Testing Stage 2 of the Spartan Engine

Stage 3: Reading and Writing Data

Updating the Spartan Source Files

Testing Stage 3 of the Spartan Engine

Stage 4: Updating and Deleting Data

Updating the Spartan Source Files

Testing Stage 4 of the Spartan Engine

Stage 5: Indexing the Data

Updating the Spartan Source Files

Testing Stage 5 of the Spartan Engine

Stage 6: Adding Transaction Support

Summary

Part 3: Advanced Database Internals

Chapter 11: Database System Internals

Query Execution

MySQL Query Execution Revisited

What Is a Compiled Query?

Exploring MySQL Internals

Getting Started Using MySQL for Experiments

Limitations and Concerns

The Database System Internals Experiment

Why an Experiment?

Overview of the Experiment Project

Components of the Experiment Project

Conducting the Experiments

Summary

Chapter 12: Internal Query Representation

The Query Tree

What Is a Theta-Join?

Query Transformation

DBXP Query Tree

Implementing DBXP Query Trees in MySQL

Files Added and Changed

Creating the Tests

Stubbing the DBXP_SELECT Command

Adding the Query Tree Class

Showing Details of the Query Tree

Summary

Chapter 13: Query Optimization

Types of Query Optimizers

Cost-Based Optimizers

Heuristic Optimizers

Semantic Optimizers

Parametric Optimizers

Heuristic Optimization Revisited

The DBXP Query Optimizer

Stubbing the DBXP_SELECT Command

Important MySQL Structures and Classes

TABLE Structure

The Field Class

Iterators

The DBXP Helper Classes

Modifications to the Existing Code

Details of the Heuristic Optimizer

Compiling and Testing the Code

Summary

Chapter 14: Query Execution

Query Execution Revisited

Project

Restrict

Join

Inner Join

Outer Join

Left Outer Join

Right Outer Join

Full Outer Join

Cross-Product

Union

Intersect

DBXP Query Execution

Designing the Tests

Updating the DBXP SELECT Command

The DBXP Algorithms

Project

Restrict

Join

Other Methods

The get_next() Method

The send_data() Method

The check_rewind() Method

Compiling and Testing the Code

Summary

Appendix

Bibliography

Database Theory

General

MySQL

Open Source

Websites

Sample Database

Chapter Exercise Notes

Chapter 12

Chapter 13

Chapter 14

Index