//
//------------------ DOK FILE --------------------------------------------------
+Header	core.net.mysql
=pkg	core.net.mysql
=title	core.net.mysql
=short	a mysql client
=desc
This library provides a connection to mysql databases.

The following example connects to a local database and displays the list of utf* character sets supported. This example will work on any mysql database as the table CHARACTER_SETS always exists. However it is just a SELECT.

It uses the [[tablePrettyPrint]] function from core.util.table to print a sql-friendly array of results.
It demonstrates how to use the await function to make it synchronous.

	use core.net.mysql;;
	use core.util.table;;
	
	fun queryCb (h, reply, rows, join)=
		match reply with
			okReply -> let mysqlColumns (h) -> columns in (
				echoLn ["columns: ", strJoin (" | ", columns)];
				echoLn ["lastInsert: ", mysqlLastInsert (h)];
				joinSend (join, columns::fifoList (rows))
			),
			errorReply code sqlState msg -> (
				echoLn strFormat ("error: * * *", code, sqlState, msg);
				joinSend (join, nil)
			),
			rowReply row -> (
				fifoIn (rows, row);
				nil
			);;
	
	fun mysqlClient (server, port, login, password, database, request, join) =
		let fifoCreate() -> rows in
		mysqlConnect (server, port, login, password, database, nil, lambda (h, reply)=
			match reply with
			okReply -> (
				echoLn "connected";
				echoLn strFormat ("protocol *, version *", mysqlProtocol (h), mysqlVersion (h));
				mysqlQuery (h, request, lambda (reply)= queryCb (h, reply, rows, join));
				nil
			),
			errorReply code sqlState msg -> (
				echoLn strFormat ("error: * * *", code, sqlState, msg);
				joinSend (join, nil)
			)
		);;
	
	fun main()=
		let ["127.0.0.1", 3306, "root", "1234", "information_schema"]
			-> [ip, port, login, password, database] in
		let strBuild ([
			"SELECT * FROM CHARACTER_SETS where CHARACTER_SET_NAME like ", sql ("utf%")
		])	-> request in
		let await (lambda (join)= mysqlClient (ip, port, login, password, database, request, join))
			-> result in
		tablePrettyPrint (result);
		echoLn "done";;

And here is the result you should get:
>connected
>protocol 10, version 5.7.31
>columns: CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN
>lastInsert: 0
>|CHARACTER_SET_NAME|DEFAULT_COLLATE_NAME|     DESCRIPTION|MAXLEN|
>|------------------|--------------------|----------------|------|
>|              utf8|     utf8_general_ci|   UTF-8 Unicode|     3|
>|           utf8mb4|  utf8mb4_general_ci|   UTF-8 Unicode|     4|
>|             utf16|    utf16_general_ci|  UTF-16 Unicode|     4|
>|           utf16le|  utf16le_general_ci|UTF-16LE Unicode|     4|
>|             utf32|    utf32_general_ci|  UTF-32 Unicode|     4|
>done
//
//
//------------------ PROTO ----------------------------------------------------- mysqlConvertColumns
+Proto	mysqlConvertColumns
=type	fun Mysql list Str -> list Int
=mode	function
=pkg	core.net.mysql
=impl	mcy
=link	
=desc
As the mysql database returns an array of strings for each row of data, it is important to know the position of each field in this array.
This function takes a list of field names and returns the corresponding list of indexes (aka positions) in the array, starting from zero.
//
+arg	client
=type	Mysql
=desc	A mysql client
//
+arg	lFields
=type	list Str
=desc	A list of field names
//
+arg	result
=type	list Int
=desc	A list of indexes
//
//
//------------------ PROTO ----------------------------------------------------- sql
+Proto	sql
=type	fun a1 -> Str
=mode	function
=pkg	core.net.mysql
=impl	mcy
=link	
=desc
This function converts the value into a valid sql string, with escaped characters and quotes around.
	> sql ("foobar")
>-> Str: "'foobar'"
	> sql ("foo'bar")
>-> Str: "'foo\\'bar'"

The name of this function is very short on purpose as it may be heavily used.
//
+arg	val
=type	a1
=desc	Any value
//
+arg	result
=type	Str
=desc	A well formatted value
//
//
//------------------ PROTO ----------------------------------------------------- mysqlLastInsert
+Proto	mysqlLastInsert
=type	fun Mysql -> Int
=mode	function
=pkg	core.net.mysql
=impl	mcy
=link	
=desc	After an INSERT with an autoinc field, this function returns the last value of this field.
//
+arg	client
=type	Mysql
=desc	A mysql client
//
+arg	result
=type	Int
=desc	An integer
//
//
//------------------ PROTO ----------------------------------------------------- mysqlColumns
+Proto	mysqlColumns
=type	fun Mysql -> array Str
=mode	function
=pkg	core.net.mysql
=impl	mcy
=link	
=desc	After a request is successfull, this function returns the array of field names.
//
+arg	client
=type	Mysql
=desc	A mysql client
//
+arg	result
=type	array Str
=desc	A list of strings
//
//
//------------------ PROTO ----------------------------------------------------- mysqlVersion
+Proto	mysqlVersion
=type	fun Mysql -> Str
=mode	function
=pkg	core.net.mysql
=impl	mcy
=link	
=desc	This function returns the version number of the database server. For example: "5.7.31".
//
+arg	client
=type	Mysql
=desc	A mysql client
//
+arg	result
=type	Str
=desc	A string
//
//
//------------------ PROTO ----------------------------------------------------- mysqlProtocol
+Proto	mysqlProtocol
=type	fun Mysql -> Int
=mode	function
=pkg	core.net.mysql
=impl	mcy
=link	
=desc	This function returns the protocol number of the database server. For example: 10.
//
+arg	client
=type	Mysql
=desc	A mysql client
//
+arg	result
=type	Int
=desc	An integer
//
//
//------------------ PROTO ----------------------------------------------------- mysqlQuit
+Proto	mysqlQuit
=type	fun Mysql -> a1
=mode	function
=pkg	core.net.mysql
=impl	mcy
=link	
=desc	This function aborts the connection.
//
+arg	client
=type	Mysql
=desc	A mysql client
//
+arg	result
=type	a1
=desc	Always nil
//
//
//------------------ PROTO ----------------------------------------------------- mysqlQuery
+Proto	mysqlQuery
=type	fun Mysql Str -> list array array Str
=mode	function
=pkg	core.net.mysql
=impl	mcy
=link	
=desc
This function sends a query through the mysql client.
This is a synchronous function: the result is a list of arrays, one for each query in the command string.
In this list, each array is a table of strings whose first line is either:
-	"table"
-	"ok", "lastInsert", last insert id 
-	"error", state, error code, error message
When the first line is "table", then the second line is the column names. Data are available from the third line.

Only one request may be sent through a single mysql client. A concurrent request on the same client will fail with [[ERROR_BUSY]] without interfering with the ongoing request.
//
+arg	client
=type	Mysql
=desc	A mysql client
//
+arg	command
=type	Str
=desc	A sql request
//
+arg	result
=type	list array array Str
=desc	The result
//
//
//------------------ PROTO ----------------------------------------------------- mysqlSetCharset
+Proto	mysqlSetCharset
=type	fun Mysql Int -> Int
=mode	function
=pkg	core.net.mysql
=impl	mcy
=link	
=desc
This function redefines the charset to be used with this mysql client.
The default value is UTF8.
//
+arg	client
=type	Mysql
=desc	A mysql client
//
+arg	charset
=type	Int
=desc	An integer defining a charset. For example 33 for UTF8, 8 for LATIN1.
//
+arg	result
=type	Int
=desc	The same charset value.
//
//
//------------------ PROTO ----------------------------------------------------- mysqlConnect
+Proto	mysqlConnect
=type	fun Str Int Str Str Str Bool -> Mysql
=mode	function
=pkg	core.net.mysql
=impl	mcy
=link	
=desc
This function starts a connection to a database.
This is a synchronous function. Once the mysql client is known, it is possible to send requests with [[mysqlQuery]], one after the other.
//
+arg	server
=type	Str
=desc	A server name or an IP address
//
+arg	port
=type	Int
=desc	A port number
//
+arg	login
=type	Str
=desc	A login string
//
+arg	password
=type	Str
=desc	A password string
//
+arg	database
=type	Str
=desc	A database name
//
+arg	tls
=type	Bool
=desc	A boolean. When true, the network connection is encrypted with tls. Else it is an unencrypted connection.
//
+arg	result
=type	Mysql
=desc	The resulting mysql client structure
//
//
//------------------ PROTO ----------------------------------------------------- Mysql
+Proto	Mysql
=type	Mysql
=mode	structure
=pkg	core.net.mysql
=impl	mcy
=link	
=desc	A type for Mysql client
//
//
//------------------ PROTO ----------------------------------------------------- ERROR_CONNECT
+Proto	ERROR_CONNECT
=type	Int
=mode	constant
=pkg	core.net.mysql
=impl	mcy
=link	
=desc	Constant for mysql error
//
//
//------------------ PROTO ----------------------------------------------------- ERROR_BUSY
+Proto	ERROR_BUSY
=type	Int
=mode	constant
=pkg	core.net.mysql
=impl	mcy
=link	
=desc	Constant for mysql error
//
//
//------------------ PROTO ----------------------------------------------------- ERROR_CLOSED
+Proto	ERROR_CLOSED
=type	Int
=mode	constant
=pkg	core.net.mysql
=impl	mcy
=link	
=desc	Constant for mysql error
//
//
//------------------ PROTO ----------------------------------------------------- ERROR_PACKET
+Proto	ERROR_PACKET
=type	Int
=mode	constant
=pkg	core.net.mysql
=impl	mcy
=link	
=desc	Constant for mysql error