A Database API Implementation for MySQL

A MySQL Implementation of the Stdb API

This is an implementation of the database connectivity API defined in the Stdb-Core package. It uses the MySQL protocol driver in StdbMysqlProcol project

See also: StdbCore (required), StdbMysqlProtocol (required)

Features:

• Implements StdbCore API facilities like connection, prepared statement, metadata, resultset and so on


• Transaction facilities


• Prepared statement is implemented as server side statements in MySQL


• Cursor based row fetch option from the server with configurable fetch size


• Non cursor based row fetch for collecting all rows in the result set


• Automatic mapping of row data into native Smalltalk types


• Option to retrieve rows as strings instead of using native Smalltalk types


• Exceptions on protocol level error responses from the server with SQL state information

Known limitations:

• Do not run the unit tests in MySQL strict mode. Some tests depend upon generating warning conditions and loose type conversions, and will fail in strict mode.
  
• The underlying protocol driver is capable of handle multiple responses (e.g., result sets), although the API level implementation does not expose this capability yet.


• No API level implementation of the callable statement. This is planned for the future. Meanwhile one can use the connection's sql execution facility to call stored procedures.


• The metadata API is limited to result set metadata only. No database level metadata facility is available at present.


• The SQLError exceptions are not subdivided further into fine grained exception categories based upon SQL state.

Packages required:

• Stdb-Core: This provides the API defintions and delegates the implementations.


• Stdb-MysqlImpl: The MySQL implementation of the Stdb API. This is an adapter to the underlying protocol driver.


• Stdb-MysqlProtocol: The MySQL client server wire protocol implementation.

Package Loading Dependencies:

• Stdb-MysqlImpl requires the Stdb-Core package and the Stdb-MysqlProtocol package, since it uses (a) the API classes from the core, and (b) the MySQL wire protocol implementation.


• Stdb-MysqlProtocol does not depend upon the other two packages for loading.


• Stdb-Core does not depend upon the other two packages for loading.

The package loading order should be: (1) Stdb-MysqlProtocol and Stdb-Core (both must be loaded, but can be loaded in any order), followed by (2) Stdb-MysqlImpl when loading from the Montecello repository.

Where the tests are:

The protocol driver and the API implementations have a comprehensive test suite. All tests are included in the packages. There are almost 200 tests covering many aspects of the MySQL protocol and the Stdb API implementation. Bulk of these tests are for the protocol driver, while the rest are for the Stdb API implementation.

Development & Test Environments

The code has been developed & tested in the following environment(s):

• Mac OS X 10.5.8


• Mysql server 5.0.67


• Squeak 3.10 with Seaside 2.8.3 dev image


• Squeak 4.2 (All-in-one)


• Pharo 1.1.1

How to run the tests:

Majority of the tests assume the presence of a MySQL server already running in default host, i.e., the localhost. It is also necessary to create the test username and password used, along with granting appropriate permissions to that user. The test specification is defined in the MysqlTestResource class side. If you are using a non-default configuration for the tests then you will have to change those settings.

You will need to be a user with admin privileges (e.g., root) in order to successfully set up the test user account.

• If you are connecting to the MySQL server on the localhost, then issue the following commands to set up the test user account:
  
  
  create user 'stdbtestuser'@'localhost' identified by 'stdbtestpass';
  </br/>
  grant all privileges on stdbtestschema.* to 'stdbtestuser'@'localhost';
  
  
  








• If you are connecting to the MySQL server in another host, then issue the following commands to set up the test user account:
  
  
  create user 'stdbtestuser'@'%' identified by 'stdbtestpass';
  
  
  grant all privileges on stdbtestschema.* to 'stdbtestuser'@'%';