Copyright	(c) Winterland, 2016
License	BSD
Maintainer	drkoster@qq.com
Stability	experimental
Portability	PORTABLE
Safe Haskell	None
Language	Haskell2010

Database.MySQL.Base

Contents

Setting up and control connection
Direct query
Prepared query statement
Helpers
Exceptions
MySQL protocol

Description

This module provide common MySQL operations,

NOTEs on Exceptions: This package use Exception to deal with unexpected situations, but you shouldn't try to catch them if you don't have a recovery plan, for example: there's no meaning to catch a ERRException during authentication unless you want to try different passwords. By using this library you will meet:

NetworkException: underline network is broken.
UnconsumedResultSet: you should consume previous resultset before sending new command.
ERRException: you receive a ERR packet when you shouldn't.
UnexpectedPacket: you receive a unexpected packet when you shouldn't.
DecodePacketException: there's a packet we can't decode.
WrongParamsCount: you're giving wrong number of params to renderParams.

Both UnexpectedPacket and DecodePacketException may indicate a bug of this library rather your code, so please report!

Synopsis

Setting up and control connection

data MySQLConn Source

MySQLConn wrap both InputStream and OutputStream for MySQL Packet.

You shouldn't use one MySQLConn in different thread, if you do that, consider protecting it with a MVar.

data ConnectInfo Source

Everything you need to establish a MySQL connection.

To setup a TLS connection, use module Database.MySQL.TLS or Database.MySQL.OpenSSL.

Constructors

ConnectInfo
Fields ciHost :: HostName ciPort :: PortNumber ciDatabase :: ByteString ciUser :: ByteString ciPassword :: ByteString

defaultConnectInfo :: ConnectInfo Source

A simple ConnectInfo targeting localhost with user=root and empty password.

connect :: ConnectInfo -> IO MySQLConn Source

Establish a MySQL connection.

connectDetail :: ConnectInfo -> IO (Greeting, MySQLConn) Source

Establish a MySQL connection with Greeting back, so you can find server's version .etc.

close :: MySQLConn -> IO () Source

Close a MySQL connection.

ping :: MySQLConn -> IO OK Source

Send a COM_PING.

Direct query

execute :: QueryParam p => MySQLConn -> Query -> [p] -> IO OK Source

Execute a MySQL query with parameters which don't return a result-set.

The query may contain placeholders ?, for filling up parameters, the parameters will be escaped before get filled into the query, please DO NOT enable NO_BACKSLASH_ESCAPES, and you should consider using prepared statement if this's not an one shot query.

executeMany :: QueryParam p => MySQLConn -> Query -> [[p]] -> IO [OK] Source

Execute a multi-row query which don't return result-set.

Leverage MySQL's multi-statement support to do batch insert/update/delete, you may want to use withTransaction to make sure it's atomic, and use sum . map okAffectedRows to get all affected rows count.

Since: 0.2.0.0

execute_ :: MySQLConn -> Query -> IO OK Source

Execute a MySQL query which don't return a result-set.

query_ :: MySQLConn -> Query -> IO ([ColumnDef], InputStream [MySQLValue]) Source

Execute a MySQL query which return a result-set.

queryVector_ :: MySQLConn -> Query -> IO (Vector ColumnDef, InputStream (Vector MySQLValue)) Source

Vector version of query_.

Since: 0.5.1.0

query :: QueryParam p => MySQLConn -> Query -> [p] -> IO ([ColumnDef], InputStream [MySQLValue]) Source

Execute a MySQL query which return a result-set with parameters.

Note that you must fully consumed the result-set before start a new query on the same MySQLConn, or an UnconsumedResultSet will be thrown. if you want to skip the result-set, use skipToEof.

queryVector :: QueryParam p => MySQLConn -> Query -> [p] -> IO (Vector ColumnDef, InputStream (Vector MySQLValue)) Source

Vector version of query.

Since: 0.5.1.0

Prepared query statement

prepareStmt :: MySQLConn -> Query -> IO StmtID Source

Ask MySQL to prepare a query statement.

prepareStmtDetail :: MySQLConn -> Query -> IO (StmtPrepareOK, [ColumnDef], [ColumnDef]) Source

Ask MySQL to prepare a query statement.

All details from COM_STMT_PREPARE Response are returned: the StmtPrepareOK packet, params's ColumnDef, result's ColumnDef.

executeStmt :: MySQLConn -> StmtID -> [MySQLValue] -> IO OK Source

Execute prepared query statement with parameters, expecting no resultset.

queryStmt :: MySQLConn -> StmtID -> [MySQLValue] -> IO ([ColumnDef], InputStream [MySQLValue]) Source

Execute prepared query statement with parameters, expecting resultset.

Rules about UnconsumedResultSet applied here too.

queryStmtVector :: MySQLConn -> StmtID -> [MySQLValue] -> IO (Vector ColumnDef, InputStream (Vector MySQLValue)) Source

Vector version of queryStmt

Since: 0.5.1.0

closeStmt :: MySQLConn -> StmtID -> IO () Source

Ask MySQL to closed a query statement.

resetStmt :: MySQLConn -> StmtID -> IO () Source

Ask MySQL to reset a query statement, all previous resultset will be cleared.

Helpers

withTransaction :: MySQLConn -> IO a -> IO a Source

Run querys inside a transaction, querys will be rolled back if exception arise.

Since: 0.2.0.0

class QueryParam a where Source

A type that may be used as a single parameter to a SQL query. Inspired from mysql-simple.

Methods

render :: a -> Put Source

Prepare a value for substitution into a query string.

Instances

QueryParam MySQLValue Source
QueryParam Param Source

data Param Source

A type to wrap a query parameter in to allow for single and multi-valued parameters.

The behavior of Param can be illustrated by following example:

   render $ One (MySQLText "hello") = hello
   render $ Many [MySQLText "hello", MySQLText "world"] = hello, world
   render $ Many [] = null

So you can now write a query like this: SELECT * FROM test WHERE _id IN (?, 888) and use Many Param to fill the hole. There's no equivalent for prepared statement sadly.

Constructors

One MySQLValue
Many [MySQLValue]

Instances

QueryParam Param Source

newtype Query Source

Query string type borrowed from mysql-simple.

This type is intended to make it difficult to construct a SQL query by concatenating string fragments, as that is an extremely common way to accidentally introduce SQL injection vulnerabilities into an application.

This type is an instance of IsString, so the easiest way to construct a query is to enable the OverloadedStrings language extension and then simply write the query in double quotes.

The underlying type is a ByteString, and literal Haskell strings that contain Unicode characters will be correctly transformed to UTF-8.

Constructors

Query
Fields fromQuery :: ByteString

Instances

Eq Query Source
Ord Query Source
Read Query Source
Show Query Source
IsString Query Source

renderParams :: QueryParam p => Query -> [p] -> Query Source

command :: MySQLConn -> Command -> IO OK Source

Send a Command which don't return a resultSet.

skipToEof :: InputStream a -> IO ()

Exceptions

data NetworkException Source

Constructors

NetworkException

Instances

Show NetworkException Source
Exception NetworkException Source

data UnconsumedResultSet Source

Constructors

UnconsumedResultSet

Instances

Show UnconsumedResultSet Source
Exception UnconsumedResultSet Source

data ERRException Source

Constructors

ERRException ERR

Instances

Show ERRException Source
Exception ERRException Source

data UnexpectedPacket Source

Constructors

UnexpectedPacket Packet

Instances

Show UnexpectedPacket Source
Exception UnexpectedPacket Source

data DecodePacketException Source

Constructors

DecodePacketFailed ByteString ByteOffset String

Instances

Show DecodePacketException Source
Exception DecodePacketException Source

data WrongParamsCount Source

Constructors

WrongParamsCount

Instances

Show WrongParamsCount Source
Exception WrongParamsCount Source

MySQL protocol

module Database.MySQL.Protocol.Auth

module Database.MySQL.Protocol.Command

module Database.MySQL.Protocol.ColumnDef

module Database.MySQL.Protocol.Packet

module Database.MySQL.Protocol.MySQLValue