Database.MySQL.Base

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!

Setting up and control connection

data MySQLConn

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

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
• ciCharset :: Word8

Instances

Show ConnectInfo

Defined in Database.MySQL.Connection

Methods

showsPrec :: Int -> ConnectInfo -> ShowS

show :: ConnectInfo -> String

showList :: [ConnectInfo] -> ShowS

defaultConnectInfo :: ConnectInfo

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

Default charset is set to utf8_general_ci to support older(< 5.5.3) MySQL versions, but be aware this is a partial utf8 encoding, you may want to use defaultConnectInfoMB4 instead to support full utf8 charset(emoji, etc.). You can query your server's support with SELECT id, collation_name FROM information_schema.collations ORDER BY id;

defaultConnectInfoMB4 :: ConnectInfo

defaultConnectInfo with charset set to utf8mb4_unicode_ci

This is recommanded on any MySQL server version >= 5.5.3.

connect :: ConnectInfo -> IO MySQLConn

Establish a MySQL connection.

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

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

close :: MySQLConn -> IO ()

Close a MySQL connection.

ping :: MySQLConn -> IO OK

Send a COM_PING.

Direct query

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

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]

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

executeMany_ :: MySQLConn -> Query -> IO [OK]

Execute multiple querys (without param) which don't return result-set.

This's useful when your want to execute multiple SQLs without params, e.g. from a SQL dump, or a table migration plan.

Since: 0.8.4.0

execute_ :: MySQLConn -> Query -> IO OK

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

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

Execute a MySQL query which return a result-set.

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

Vector version of query_.

Since: 0.5.1.0

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

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))

Vector version of query.

Since: 0.5.1.0

Prepared query statement

prepareStmt :: MySQLConn -> Query -> IO StmtID

Ask MySQL to prepare a query statement.

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

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

Execute prepared query statement with parameters, expecting no resultset.

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

Execute prepared query statement with parameters, expecting resultset.

Rules about UnconsumedResultSet applied here too.

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

Vector version of queryStmt

Since: 0.5.1.0

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

Ask MySQL to closed a query statement.

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

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

Helpers

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

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

Since: 0.2.0.0

class QueryParam a where

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

Methods

render :: a -> Put

Prepare a value for substitution into a query string.

Instances

QueryParam MySQLValue

Defined in Database.MySQL.Query

Methods

render :: MySQLValue -> Put

QueryParam Param

Defined in Database.MySQL.Query

Methods

render :: Param -> Put

data Param

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

Defined in Database.MySQL.Query

Methods

render :: Param -> Put

newtype Query

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

IsString Query

Defined in Database.MySQL.Query

Methods

fromString :: String -> Query

Read Query

Defined in Database.MySQL.Query

Methods

readsPrec :: Int -> ReadS Query

readList :: ReadS [Query]

readPrec :: ReadPrec Query

readListPrec :: ReadPrec [Query]

Show Query

Defined in Database.MySQL.Query

Methods

showsPrec :: Int -> Query -> ShowS

show :: Query -> String

showList :: [Query] -> ShowS

Eq Query

Defined in Database.MySQL.Query

Methods

(==) :: Query -> Query -> Bool

(/=) :: Query -> Query -> Bool

Ord Query

Defined in Database.MySQL.Query

Methods

compare :: Query -> Query -> Ordering

(<) :: Query -> Query -> Bool

(<=) :: Query -> Query -> Bool

(>) :: Query -> Query -> Bool

(>=) :: Query -> Query -> Bool

max :: Query -> Query -> Query

min :: Query -> Query -> Query

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

command :: MySQLConn -> Command -> IO OK

Send a Command which don't return a resultSet.

skipToEof :: InputStream a -> IO ()

Drives an InputStream to end-of-stream, discarding all of the yielded values.

Exceptions

data NetworkException

Constructors

NetworkException

Instances

Exception NetworkException

Defined in Database.MySQL.Connection

Methods

toException :: NetworkException -> SomeException

fromException :: SomeException -> Maybe NetworkException

displayException :: NetworkException -> String

Show NetworkException

Defined in Database.MySQL.Connection

Methods

showsPrec :: Int -> NetworkException -> ShowS

show :: NetworkException -> String

showList :: [NetworkException] -> ShowS

data UnconsumedResultSet

Constructors

UnconsumedResultSet

Instances

Exception UnconsumedResultSet

Defined in Database.MySQL.Connection

Methods

toException :: UnconsumedResultSet -> SomeException

fromException :: SomeException -> Maybe UnconsumedResultSet

displayException :: UnconsumedResultSet -> String

Show UnconsumedResultSet

Defined in Database.MySQL.Connection

Methods

showsPrec :: Int -> UnconsumedResultSet -> ShowS

show :: UnconsumedResultSet -> String

showList :: [UnconsumedResultSet] -> ShowS

data ERRException

Constructors

ERRException ERR

Instances

Exception ERRException

Defined in Database.MySQL.Connection

Methods

toException :: ERRException -> SomeException

fromException :: SomeException -> Maybe ERRException

displayException :: ERRException -> String

Show ERRException

Defined in Database.MySQL.Connection

Methods

showsPrec :: Int -> ERRException -> ShowS

show :: ERRException -> String

showList :: [ERRException] -> ShowS

data UnexpectedPacket

Constructors

UnexpectedPacket Packet

Instances

Exception UnexpectedPacket

Defined in Database.MySQL.Connection

Methods

toException :: UnexpectedPacket -> SomeException

fromException :: SomeException -> Maybe UnexpectedPacket

displayException :: UnexpectedPacket -> String

Show UnexpectedPacket

Defined in Database.MySQL.Connection

Methods

showsPrec :: Int -> UnexpectedPacket -> ShowS

show :: UnexpectedPacket -> String

showList :: [UnexpectedPacket] -> ShowS

data DecodePacketException

Constructors

DecodePacketFailed ByteString ByteOffset String

Instances

Exception DecodePacketException

Defined in Database.MySQL.Protocol.Packet

Methods

toException :: DecodePacketException -> SomeException

fromException :: SomeException -> Maybe DecodePacketException

displayException :: DecodePacketException -> String

Show DecodePacketException

Defined in Database.MySQL.Protocol.Packet

Methods

showsPrec :: Int -> DecodePacketException -> ShowS

show :: DecodePacketException -> String

showList :: [DecodePacketException] -> ShowS

data WrongParamsCount

Constructors

WrongParamsCount

Instances

Exception WrongParamsCount

Defined in Database.MySQL.Query

Methods

toException :: WrongParamsCount -> SomeException

fromException :: SomeException -> Maybe WrongParamsCount

displayException :: WrongParamsCount -> String

Show WrongParamsCount

Defined in Database.MySQL.Query

Methods

showsPrec :: Int -> WrongParamsCount -> ShowS

show :: WrongParamsCount -> String

showList :: [WrongParamsCount] -> ShowS

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