This module implements an connection abstraction, which can connect to the MySQL server by MqSQL Client/Server Protocol.

Imports

asyncdispatch, asyncnet, net, mysqlparser, error, query, strutils, deques

Types

AsyncMysqlConnection = ref object socket: AsyncSocket parser: PacketParser handshakePacket: HandshakePacket resultPacket: ResultPacket buf: array[MysqlBufSize, char] bufPos: int bufLen: int lock: RequestLock closed: bool: Asynchronous mysql connection. Source Edit

Consts

MysqlBufSize = 1024: Size of the internal buffer that is used by mysql connection. Source Edit
DefaultClientCharset = 33: Default charset used by MySQL Client/Server Protocol. This is called "collation" in the SQL-level of MySQL (like CHARSET_UTF8_GENERAL_CI). Source Edit
DefaultClientCapabilities = 521167: Default client capabilities flag bitmask used by MySQL Client/Server Protocol. Source Edit

Procs

proc closed(conn: AsyncMysqlConnection): bool {. raises: [], tags: [] .}

Returns whether conn is closed. Source Edit

proc close(conn: AsyncMysqlConnection) {. raises: [SslError, OSError, Exception], tags: [RootEffect] .}

Closes the database connection conn and releases the associated resources. When a connection is no longer needed, the user should close it. Source Edit

proc openMysqlConnection(domain: Domain = AF_INET; port = Port(3306); host = "127.0.0.1"; user: string; password: string; database: string; charset = DefaultClientCharset; capabilities = DefaultClientCapabilities): Future[ AsyncMysqlConnection] {. raises: [FutureError], tags: [RootEffect] .}

Opens a new database connection.

When establishing a connection, you can set the following options:

domain - the protocol family of the underly socket for this connection.
port - the port number to connect to.
host - the hostname of the database you are connecting to.
user - the MySQL user to authenticate as.
password - the password of the MySQL user.
database - name of the database to use for this connection.
charset - the charset for the connection. (Default: DefaultClientCharset). All available charset constants are in a sub-module called charset.
capabilities - the client capabilitis which is a flag bitmask. (Default: DefaultClientCapabilities). And this can be used to affect the connection's behavior. All available charset constants are in a sub-module called capabilities.

Source Edit

proc execQuery(conn: AsyncMysqlConnection; q: SqlQuery; finishCb: proc ( err: ref Exception): Future[void] {. closure, gcsafe .}; recvPacketCb: proc ( packet: ResultPacket): Future[void] {. closure, gcsafe .} = nil; recvPacketEndCb: proc (): Future[void] {. closure, gcsafe .} = nil; recvFieldCb: proc (field: string): Future[void] {. closure, gcsafe .} = nil) {. raises: [Exception, IndexError, FutureError, ValueError], tags: [RootEffect] .}

Executes the SQL statements in q.

This proc is especially useful when dealing with large result sets. The query process is made up of many different stages. At each stage, a different callback proc is called:

recvPacketCb - called when a SQL statement is beginning.
recvFieldCb - called when a complete field is made.
recvPacketEndCb - called when a SQL statement is finished.
finishCb - called when all SQL statements are finished or occur some errors.

For example, when the following statements are executed:

select host from user;
select id from test;
insert into test (name) values ('name1') where id = 1;

the query process is like this:

1. packet stage
receives a result packet from select host from user;, calls recvPacketCb, and the kind field of the argument packet is set to rpkResultSet.

2. field stage

receives a field(column) from select host from user;, calls recvFieldCb. Then, receives next field(column), calls recvFieldCb again.

recvFieldCb will be called again by again until there is no any field from select host from user;.

3. packet stage

receives a result packet from select id from test;, calls recvPacketCb, and the kind field of the argument packet is set to rpkResultSet.

4. field stage

receives a field(column) from select id from user;, calls recvFieldCb. Then, receives next field(column), calls recvFieldCb again.

recvFieldCb will be called again by again until there is no any field from select host from user;.

5. packet stage

receives a result packet from insert into test (name) values ('name1') where id = 1;, calls recvPacketCb, and the kind field of the argument packet is set to rpkOk.

6. finished stage

all SQL statements are finished, calls finishCb.

Notes: if any errors occur in the above steps, calls finishCb immediately and ignores other callback procs.

Source Edit

proc execQuery(conn: AsyncMysqlConnection; q: SqlQuery; bufferSize: int; finishCb: proc ( err: ref Exception): Future[void] {. closure, gcsafe .}; recvPacketCb: proc ( packet: ResultPacket): Future[void] {. closure, gcsafe .} = nil; recvPacketEndCb: proc (): Future[void] {. closure, gcsafe .} = nil; recvFieldCb: proc (buffer: string): Future[void] {. closure, gcsafe .} = nil; recvFieldEndCb: proc (): Future[void] {. closure, gcsafe .} = nil) {. raises: [Exception, IndexError, FutureError, ValueError], tags: [RootEffect] .}

Executes the SQL statements in q. bufferSize specifies the size of field buffer.

This proc is especially useful when dealing with large result sets. The query process is made up of many different stages. At each stage, a different callback proc is called:

recvPacketCb - called when a SQL statement is beginning.
recvFieldCb - called when the content of a field fill fully the internal buffer.
recvFieldEndCb - called when a complete field is made.
recvPacketEndCb - called when a SQL statement is finished.
finishCb - called when all SQL statements are finished or an error occurs.

Source Edit

proc execQuery(conn: AsyncMysqlConnection; q: SqlQuery; finishCb: proc ( err: ref Exception; replies: seq[tuple[packet: ResultPacket, rows: seq[string]]]): Future[void] {. closure, gcsafe .}) {. raises: [Exception, IndexError, FutureError, ValueError], tags: [RootEffect] .}

Executes the SQL statements in q.

This proc places all the results in memory. When dealing with large result sets, this can be inefficient and take up a lot of memory, so you can try the other two execQuery procs at this point.

finishCb - called when all SQL statements are finished or an error occurs.

Source Edit

proc execQuit(conn: AsyncMysqlConnection; finishCb: proc (err: ref Exception): Future[ void] {. closure, gcsafe .}) {. raises: [Exception, IndexError, FutureError, ValueError], tags: [RootEffect] .}

Notifies the mysql server that the connection is disconnected. Attempting to request the mysql server again will causes unknown errors.

finishCb - called when this task is finished or an error occurs.

Source Edit

proc execInitDb(conn: AsyncMysqlConnection; database: string; finishCb: proc ( err: ref Exception; reply: ResultPacket): Future[void] {. closure, gcsafe .}) {. raises: [Exception, IndexError, FutureError, ValueError], tags: [RootEffect] .}

Changes the default database on the connection.

Equivalent to use <database>;

finishCb - called when this task is finished or an error occurs.

Source Edit

proc execChangeUser(conn: AsyncMysqlConnection; user: string; password: string; database: string; charset = DefaultClientCharset; finishCb: proc ( err: ref Exception; reply: ResultPacket): Future[void] {. closure, gcsafe .}) {. raises: [Exception, IndexError, FutureError, ValueError], tags: [RootEffect] .}

Changes the user and causes the database specified by database to become the default (current) database on the connection specified by mysql. In subsequent queries, this database is the default for table references that include no explicit database specifier.

finishCb - called when this task is finished or an error occurs.

Source Edit

proc execPing(conn: AsyncMysqlConnection; finishCb: proc (err: ref Exception; reply: ResultPacket): Future[void] {. closure, gcsafe .}) {. raises: [Exception, IndexError, FutureError, ValueError], tags: [RootEffect] .}

Checks whether the connection to the server is working. Source Edit

Module connection

Imports

Types

Consts

Procs