Database.MySQL.Base

A low-level client library for the MySQL database, implemented as bindings to the C mysqlclient API.

The C library is thread-safe, but uses thread-local state. Therefore, if these bindings are used in a multi-threaded program, "bound" threads should be used (see Control.Concurrent). In addition, explicit calls to initLibrary, and possibly initThread and endThread may be needed in a multi-threaded program.

Licensing

Important licensing note: This library is BSD-licensed under the terms of the MySQL FOSS License Exception http://www.mysql.com/about/legal/licensing/foss-exception/.

Since this library links against the GPL-licensed mysqlclient library, a non-open-source application that uses it may be subject to the terms of the GPL.

Our rules for managing Connection and Result values are unfortunately complicated, thanks to MySQL's lifetime rules.

At the C libmysqlclient level, a single MYSQL connection may cause multiple MYSQL_RES result values to be created over the course of multiple queries, but only one of these MYSQL_RES values may be alive at a time. The programmer is responsible for knowing when to call mysql_free_result.

Meanwhile, up in Haskell-land, we'd like both Connection and Result values to be managed either manually or automatically. In particular, we want finalizers to tidy up after a messy programmer, and we'd prefer it if people didn't need to be mindful of calling mysql_free_result. This means that we must wrestle with the lifetime rules. An obvious approach would be to use some monad and type magic to enforce those rules, but then we'd end up with an awkward API.

Instead, we allow Result values to stay alive for arbitrarily long times, while preserving the right to mark them as invalid. When a Result is marked invalid, its associated MYSQL_RES is freed, and can no longer be used.

Since all functions over Result values are in the IO monad, we don't risk disrupting pure code by introducing this notion of invalidity. If code tries to use an invalid Result, a MySQLError will be thrown. This should not occur in normal code, so there should be no need to use isResultValid to test a Result for validity.

Each of the following functions will invalidate a Result:

• close
• freeResult
• nextResult
• storeResult
• useResult

A Result must be able to keep a Connection alive so that a streaming Result constructed by useResult can continue to pull data from the server, but a Connection must (a) be able to cause the MYSQL_RES behind a Result to be deleted at a moment's notice, while (b) not artificially prolonging the life of either the Result or its MYSQL_RES.

data ConnectInfo

Constructors

ConnectInfo

Fields

• connectHost :: String
• connectPort :: Word16
• connectUser :: String
• connectPassword :: String
• connectDatabase :: String
• connectOptions :: [Option]
• connectPath :: FilePath
• connectSSL :: Maybe SSLInfo

Instances

Eq ConnectInfo

Defined in Database.MySQL.Base

Methods

(==) :: ConnectInfo -> ConnectInfo -> Bool

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

Read ConnectInfo

Defined in Database.MySQL.Base

Methods

readsPrec :: Int -> ReadS ConnectInfo

readList :: ReadS [ConnectInfo]

readPrec :: ReadPrec ConnectInfo

readListPrec :: ReadPrec [ConnectInfo]

Show ConnectInfo

Defined in Database.MySQL.Base

Methods

showsPrec :: Int -> ConnectInfo -> ShowS

show :: ConnectInfo -> String

showList :: [ConnectInfo] -> ShowS

data SSLInfo

Constructors

SSLInfo

Fields

• sslKey :: FilePath
• sslCert :: FilePath
• sslCA :: FilePath
• sslCAPath :: FilePath
• sslCiphers :: String

  Comma-separated list of cipher names.

Instances

Eq SSLInfo

Defined in Database.MySQL.Base

Methods

(==) :: SSLInfo -> SSLInfo -> Bool

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

Read SSLInfo

Defined in Database.MySQL.Base

Methods

readsPrec :: Int -> ReadS SSLInfo

readList :: ReadS [SSLInfo]

readPrec :: ReadPrec SSLInfo

readListPrec :: ReadPrec [SSLInfo]

Show SSLInfo

Defined in Database.MySQL.Base

Methods

showsPrec :: Int -> SSLInfo -> ShowS

show :: SSLInfo -> String

showList :: [SSLInfo] -> ShowS

type Seconds = Word

data Protocol

Constructors

TCP

Socket

Pipe

Memory

Instances

Enum Protocol

Defined in Database.MySQL.Base.Types

Methods

succ :: Protocol -> Protocol

pred :: Protocol -> Protocol

toEnum :: Int -> Protocol

fromEnum :: Protocol -> Int

enumFrom :: Protocol -> [Protocol]

enumFromThen :: Protocol -> Protocol -> [Protocol]

enumFromTo :: Protocol -> Protocol -> [Protocol]

enumFromThenTo :: Protocol -> Protocol -> Protocol -> [Protocol]

Eq Protocol

Defined in Database.MySQL.Base.Types

Methods

(==) :: Protocol -> Protocol -> Bool

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

Read Protocol

Defined in Database.MySQL.Base.Types

Methods

readsPrec :: Int -> ReadS Protocol

readList :: ReadS [Protocol]

readPrec :: ReadPrec Protocol

readListPrec :: ReadPrec [Protocol]

Show Protocol

Defined in Database.MySQL.Base.Types

Methods

showsPrec :: Int -> Protocol -> ShowS

show :: Protocol -> String

showList :: [Protocol] -> ShowS

data Option

Constructors

ConnectTimeout Seconds

Compress

NamedPipe

InitCommand ByteString

ReadDefaultFile FilePath

ReadDefaultGroup ByteString

CharsetDir FilePath

CharsetName String

LocalInFile Bool

Protocol Protocol

SharedMemoryBaseName ByteString

ReadTimeout Seconds

WriteTimeout Seconds

UseRemoteConnection

UseEmbeddedConnection

GuessConnection

ClientIP ByteString

SecureAuth Bool

ReportDataTruncation Bool

Reconnect Bool

SSLVerifyServerCert Bool

FoundRows

IgnoreSIGPIPE

IgnoreSpace

Interactive

LocalFiles

MultiResults

MultiStatements

NoSchema

Instances

Eq Option

Defined in Database.MySQL.Base.Types

Methods

(==) :: Option -> Option -> Bool

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

Read Option

Defined in Database.MySQL.Base.Types

Methods

readsPrec :: Int -> ReadS Option

readList :: ReadS [Option]

readPrec :: ReadPrec Option

readListPrec :: ReadPrec [Option]

Show Option

Defined in Database.MySQL.Base.Types

Methods

showsPrec :: Int -> Option -> ShowS

show :: Option -> String

showList :: [Option] -> ShowS

defaultConnectInfo :: ConnectInfo

Default information for setting up a connection.

Defaults are as follows:

• Server on localhost
• User root
• No password
• Database test
• Character set utf8

Use as in the following example:

connect defaultConnectInfo { connectHost = "db.example.com" }

defaultSSLInfo :: SSLInfo

Default (empty) information for setting up an SSL connection.

data Connection

Connection to a MySQL database.

data Result

Result of a database query.

data Type

Column types supported by MySQL.

Constructors

Decimal

Tiny

Short

Long

Float

Double

Null

Timestamp

LongLong

Int24

Date

Time

DateTime

Year

NewDate

VarChar

Bit

NewDecimal

Enum

Set

TinyBlob

MediumBlob

LongBlob

Blob

VarString

String

Geometry

Instances

Enum Type

Defined in Database.MySQL.Base.Types

Methods

succ :: Type -> Type

pred :: Type -> Type

toEnum :: Int -> Type

fromEnum :: Type -> Int

enumFrom :: Type -> [Type]

enumFromThen :: Type -> Type -> [Type]

enumFromTo :: Type -> Type -> [Type]

enumFromThenTo :: Type -> Type -> Type -> [Type]

Eq Type

Defined in Database.MySQL.Base.Types

Methods

(==) :: Type -> Type -> Bool

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

Show Type

Defined in Database.MySQL.Base.Types

Methods

showsPrec :: Int -> Type -> ShowS

show :: Type -> String

showList :: [Type] -> ShowS

data Row

A row cursor, used by rowSeek and rowTell.

data MySQLError

The constructors of MySQLError are not currently exported, but they have a consistent set of field names which are exported. These fields are:

 errFunction :: String
 errNumber   :: Int
 errMessage  :: String

Instances

Eq MySQLError

Defined in Database.MySQL.Base

Methods

(==) :: MySQLError -> MySQLError -> Bool

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

Show MySQLError

Defined in Database.MySQL.Base

Methods

showsPrec :: Int -> MySQLError -> ShowS

show :: MySQLError -> String

showList :: [MySQLError] -> ShowS

Exception MySQLError

Defined in Database.MySQL.Base

Methods

toException :: MySQLError -> SomeException

fromException :: SomeException -> Maybe MySQLError

displayException :: MySQLError -> String

Connection management

connect :: ConnectInfo -> IO Connection

Connect to a database.

close :: Connection -> IO ()

Close a connection, and mark any outstanding Result as invalid.

autocommit :: Connection -> Bool -> IO ()

Turn autocommit on or off.

By default, MySQL runs with autocommit mode enabled. In this mode, as soon as you modify a table, MySQL stores your modification permanently.

ping :: Connection -> IO ()

changeUser :: Connection -> String -> String -> Maybe String -> IO ()

selectDB :: Connection -> String -> IO ()

setCharacterSet :: Connection -> String -> IO ()

Connection information

threadId :: Connection -> IO Word

serverInfo :: Connection -> IO String

hostInfo :: Connection -> IO String

protocolInfo :: Connection -> IO Word

characterSet :: Connection -> IO String

sslCipher :: Connection -> IO (Maybe String)

serverStatus :: Connection -> IO String

Querying

query :: Connection -> ByteString -> IO ()

insertID :: Connection -> IO Word64

Return the value generated for an AUTO_INCREMENT column by the previous INSERT or UPDATE statement.

See http://dev.mysql.com/doc/refman/5.5/en/mysql-insert-id.html

Escaping

escape :: Connection -> ByteString -> IO ByteString

Results

fieldCount :: Either Connection Result -> IO Int

Return the number of fields (columns) in a result.

• If Left Connection, returns the number of columns for the most recent query on the connection.
• For Right Result, returns the number of columns in each row of this result.

The number of columns may legitimately be zero.

affectedRows :: Connection -> IO Int64

Working with results

isResultValid :: Result -> IO Bool

Check whether a Result is still valid, i.e. backed by a live MYSQL_RES value.

freeResult :: Result -> IO ()

Immediately free the MYSQL_RES value associated with this Result, and mark the Result as invalid.

storeResult :: Connection -> IO Result

Retrieve a complete result.

Any previous outstanding Result is first marked as invalid.

useResult :: Connection -> IO Result

Initiate a row-by-row retrieval of a result.

Any previous outstanding Result is first marked as invalid.

fetchRow :: Result -> IO [Maybe ByteString]

fetchFields :: Result -> IO [Field]

dataSeek :: Result -> Int64 -> IO ()

rowSeek :: Result -> Row -> IO Row

rowTell :: Result -> IO Row

Multiple results

nextResult :: Connection -> IO Bool

Read the next statement result. Returns True if another result is available, False otherwise.

This function marks the current Result as invalid, if one exists.

Transactions

commit :: Connection -> IO ()

Commit the current transaction.

rollback :: Connection -> IO ()

Roll back the current transaction.

General information

clientInfo :: String

clientVersion :: Word

Concurrency

initLibrary :: IO ()

Call mysql_library_init

A single-threaded program can rely on an implicit initialisation done when making the first connection, but a multi-threaded one should call initLibrary separately, and it should be done before other threads might call into this library, since this function is not thread-safe. See https://ro-che.info/articles/2015-04-17-safe-concurrent-mysql-haskell and https://dev.mysql.com/doc/refman/5.7/en/c-api-threaded-clients.html for details.

initThread :: IO ()

Call mysql_thread_init

Again a single-threaded program does not need to call this explicitly. Even in a multi-threaded one, if each connection is made, used, and destroyed in a single thread, it is sufficient to rely on the connect call to do an implicit thread initialisation. But in other cases, for example when using a connection pool, each thread requires explicit initialisation. See https://ro-che.info/articles/2015-04-17-safe-concurrent-mysql-haskell and https://dev.mysql.com/doc/refman/5.7/en/c-api-threaded-clients.html for details.

endThread :: IO ()

Call mysql_thread_end

This is needed at thread exit to avoid a memory leak, except when using a non-debug build of at least version 5.7.9 of the MySQL library. See https://dev.mysql.com/doc/refman/5.7/en/mysql-thread-end.html. The threads in question are the OS threads, so calling this function is likely to be important when using large numbers of bound threads (see Control.Concurrent). Unbound threads - those created with forkIO and friends - share a small number of OS threads, so in those it is hard to call this function safely, and there is little benefit in doing so, but in any case using this library in unbound threads is not recommended (see https://ro-che.info/articles/2015-04-17-safe-concurrent-mysql-haskell).