mysql-native ~issue65

A Boost-licensed native D client driver for MySQL and MariaDB.

This package attempts to provide composite objects and methods that will allow a wide range of common database operations, but be relatively easy to use. It has no dependencies on GPL header files or libraries, instead communicating directly with the server via the published client/server protocol.

This package supports both Phobos sockets and Vibe.d sockets. It will automatically use the correct type based on whether Vibe.d is used in your project. (If you use DUB, this is completely seamless. Otherwise, you can use -version=Have_vibe_d_core to force Vibe.d sockets instead of Phobos ones.)

See .travis.yml for a list of officially supported D compiler versions.

In this document:

• API
• Basic example
• Additional notes
• Developers - How to run the test suite

See also:

• API Reference
• Migrating to v2.0.0

API

API Reference

The primary interfaces:

• Connection: Connection to the server, and querying and setting of server parameters.
• MySQLPool: Connection pool, for Vibe.d users.
• exec(): Plain old SQL statement that does NOT return rows (like INSERT/UPDATE/CREATE/etc), returns number of rows affected
• query(): Execute an SQL statement that DOES return rows (ie, SELECT) and handle the rows one at a time, as an input range.
• queryRow(): Execute an SQL statement and get the first row.
• queryValue(): Execute an SQL statement and get the first value in the first row.
• prepare(): Create a prepared statement
• Prepared: A prepared statement, optionally pass it to the exec/query function in place of an SQL string.
• Row: One "row" of results, used much like an array of Variant.
• ResultRange: An input range of rows. Convert to random access with std.array.array().

Also note the MySQL <-> D type mappings tables

Basic example

import std.array : array;
import std.variant;
import mysql;

void main(string[] args)
{
	// Connect
	auto connectionStr = "host=localhost;port=3306;user=yourname;pwd=pass123;db=mysqln_testdb";
	if(args.length > 1)
		connectionStr = args[1];
	Connection conn = new Connection(connectionStr);
	scope(exit) conn.close();

	// Insert
	ulong rowsAffected = conn.exec(
		"INSERT INTO `tablename` (`id`, `name`) VALUES (1, 'Ann'), (2, 'Bob')");

	// Query
	ResultRange range = conn.query("SELECT * FROM `tablename`");
	Row row = range.front;
	Variant id = row[0];
	Variant name = row[1];
	assert(id == 1);
	assert(name == "Ann");

	range.popFront();
	assert(range.front[0] == 2);
	assert(range.front[1] == "Bob");

	// Simplified prepared statements
	ResultRange bobs = conn.query(
		"SELECT * FROM `tablename` WHERE `name`=? OR `name`=?",
		"Bob", "Bobby");
	bobs.close(); // Skip them
	
	Row[] rs = conn.query( // Same SQL as above, but only prepared once and is reused!
		"SELECT * FROM `tablename` WHERE `name`=? OR `name`=?",
		"Bob", "Ann").array; // Get ALL the rows at once
	assert(rs.length == 2);
	assert(rs[0][0] == 1);
	assert(rs[0][1] == "Ann");
	assert(rs[1][0] == 2);
	assert(rs[1][1] == "Bob");

	// Full-featured prepared statements
	Prepared prepared = conn.prepare("SELECT * FROM `tablename` WHERE `name`=? OR `name`=?");
	prepared.setArgs("Bob", "Bobby");
	bobs = conn.query(prepared);
	bobs.close(); // Skip them

	// Nulls
	conn.exec(
		"INSERT INTO `tablename` (`id`, `name`) VALUES (?,?)",
		null, "Cam"); // Can also take Nullable!T
	range = conn.query("SELECT * FROM `tablename` WHERE `name`='Cam'");
	assert( range.front[0].type == typeid(typeof(null)) );
}

Additional notes

This requires MySQL server v4.1.1 or later, or a MariaDB server. Older versions of MySQL server are obsolete, use known-insecure authentication, and are not supported by this package.

Normally, MySQL clients connect to a server on the same machine via a Unix socket on *nix systems, and through a named pipe on Windows. Neither of these conventions is currently supported. TCP is used for all connections.

For historical reference, see the old homepage for the original release of this project. Note, however, that version has become out-of-date.

Developers - How to run the test suite

This package contains various unittests and integration tests. To run them, run run-tests.

The first time you run run-tests, it will automatically create a file testConnectionStr.txt in project's base diretory and then exit. This file is deliberately not contained in the source repository because it's specific to your system.

Open the testConnectionStr.txt file and verify the connection settings inside, modifying them as needed, and if necessary, creating a test user and blank test schema in your MySQL database.

The tests will completely clobber anything inside the db schema provided, but they will ONLY modify that one db schema. No other schema will be modified in any way.

After you've configured the connection string, run run-tests again and their tests will be compiled and run, first using Phobos sockets, then using Vibe sockets.