db.mysql #

Purpose:

The db.mysql module can be used to develop software that connects to the popular open source MySQL or MariaDB database servers.

Local setup of a development server:

To run the mysql module tests, or if you want to just experiment, you can use the following command to start a development version of MySQL using docker:

docker run -p 3306:3306 --name some-mysql -e MYSQL_ALLOW_EMPTY_PASSWORD=1 -e MYSQL_ROOT_PASSWORD= -d mysql:latest

The above command will start a server instance without any password for its root account, available to mysql client connections, on tcp port 3306.

You can test that it works by doing: mysql -uroot -h127.0.0.1 . You should see a mysql shell (use exit to end the mysql client session).

Use docker container stop some-mysql to stop the server.

Use docker container rm some-mysql to remove it completely, after it is stopped.

Installation of development dependencies:

For Linux, you need to install MySQL development package and pkg-config.

For Windows, install the installer , then copy the include and lib folders to <V install directory>\thirdparty\mysql.

Troubleshooting

If you encounter weird errors (your program just exits right away, without printing any messages, even though you have println('hi') statements in your fn main()), when trying to run a program that does import db.mysql on windows, you may need to copy the .dll file: thirdparty/mysql/lib/libmysql.dll, into the folder of the executable too (it should be right next to the .exe file).

This is a temporary workaround, until we have a more permanent solution, or at least more user friendly errors for that situation.

Basic Usage

import db.mysql

// Create connection
mut connection := mysql.Connection{
    username: 'root'
    dbname:   'mysql'
}
// Connect to server
connection.connect()?
// Change the default database
connection.select_db('db_users')?
// Do a query
get_users_query_result := connection.query('SELECT * FROM users')?
// Get the result as maps
for user in get_users_query_result.maps() {
    // Access the name of user
    println(user['name'])
}
// Free the query result
get_users_query_result.free()
// Close the connection if needed
connection.close()

fn connect(config Config) !DB

connect attempts to establish a connection to a MySQL server.

fn debug(debug string)

debug does a DBUG_PUSH with the given string. debug() uses the Fred Fish debug library. To use this function, you must compile the client library to support debugging. See https://dev.mysql.com/doc/c-api/8.0/en/mysql-debug.html

fn get_client_info() string

get_client_info returns client version information as a string.

fn get_client_version() u64

get_client_version returns the client version information as an integer.

enum ConnectionFlag {
	client_compress         = C.CLIENT_COMPRESS
	client_found_rows       = C.CLIENT_FOUND_ROWS
	client_ignore_sigpipe   = C.CLIENT_IGNORE_SIGPIPE
	client_ignore_space     = C.CLIENT_IGNORE_SPACE
	client_interactive      = C.CLIENT_INTERACTIVE
	client_local_files      = C.CLIENT_LOCAL_FILES
	client_multi_results    = C.CLIENT_MULTI_RESULTS
	client_multi_statements = C.CLIENT_MULTI_STATEMENTS
	client_no_schema        = C.CLIENT_NO_SCHEMA
	client_odbc             = C.CLIENT_ODBC
	client_ssl              = C.CLIENT_SSL
	client_remember_options = C.CLIENT_REMEMBER_OPTIONS
}

Values for the capabilities flag bitmask used by the MySQL protocol. See more on https://dev.mysql.com/doc/dev/mysql-server/latest/group__group__cs__capabilities__flags.html#details

enum FieldType {
	type_decimal
	type_tiny
	type_short
	type_long
	type_float
	type_double
	type_null
	type_timestamp
	type_longlong
	type_int24
	type_date
	type_time
	type_datetime
	type_year
	type_newdate
	type_varchar
	type_bit
	type_timestamp2
	type_datetime2
	type_time2
	type_json = 245
	type_newdecimal
	type_enum
	type_set
	type_tiny_blob
	type_medium_blob
	type_long_blob
	type_blob
	type_var_string
	type_string
	type_geometry
}

FieldType is a list of all supported MYSQL field types.

fn (f FieldType) str() string

str returns a text representation of the field type f.

fn (f FieldType) get_len() u32

get_len returns the length in bytes, for the given field type f. Should be deleted after the time type reimplementation.

struct C.MYSQL {
}

struct C.MYSQL_BIND {
mut:
	buffer_type   int
	buffer        voidptr
	buffer_length u32
	length        &u32
}

struct C.MYSQL_FIELD {
	name             &u8 // Name of column
	org_name         &u8 // Original column name, if an alias
	table            &u8 // Table of column if column was a field
	org_table        &u8 // Org table name, if table was an alias
	db               &u8 // Name of the database that the field comes from
	catalog          &u8 // Catalog for table
	def              &u8 // Default value (set by `mysql_list_fields`)
	length           int // Width of column (create length)
	max_length       int // Max width for selected set
	name_length      u32
	org_name_length  u32
	table_length     u32
	org_table_length u32
	db_length        u32
	catalog_length   u32
	def_length       u32
	flags            u32 // Bit-flags that describe the field
	decimals         u32 // Number of decimals in field
	charsetnr        u32 // Character set
	type             int // Type of field. See enums.v for types
}

struct C.MYSQL_RES {
}

struct C.MYSQL_STMT {
	mysql   &C.MYSQL
	stmt_id u32
}

struct Config {
pub mut:
	host     string = '127.0.0.1'
	port     u32    = 3306
	username string
	password string
	dbname   string
	flag     ConnectionFlag
}

struct DB {
mut:
	conn &C.MYSQL = unsafe { nil }
}

fn (db &DB) affected_rows() u64

affected_rows returns the number of rows changed, deleted, or inserted by the last statement if it was an UPDATE, DELETE, or INSERT.

fn (mut db DB) autocommit(mode bool) !

autocommit turns on/off the auto-committing mode for the connection. When it is on, then each query is committed right away.

fn (mut db DB) change_user(username string, password string, dbname string) !bool

change_user changes the mysql user for the connection. Passing an empty string for the dbname parameter, resultsg in only changing the user and not changing the default database for the connection.

fn (mut db DB) close()

close closes the connection.

fn (db &DB) commit() !

commit commits the current transaction.

fn (db DB) create(table string, fields []orm.TableField) !

create is used internally by V's ORM for processing table creation queries (DDL)

fn (db DB) delete(table string, where orm.QueryData) !

delete is used internally by V's ORM for processing DELETE queries

fn (db DB) drop(table string) !

drop is used internally by V's ORM for processing table destroying queries (DDL)

fn (mut db DB) dump_debug_info() !bool

dump_debug_info instructs the server to write debugging information to the error log. The connected user must have the SUPER privilege.

fn (db &DB) escape_string(s string) string

escape_string creates a legal SQL string for use in an SQL statement. The s argument is encoded to produce an escaped SQL string, taking into account the current character set of the connection.

fn (db &DB) exec(query string) ![]Row

exec executes the query on the given db, and returns an array of all the results, or an error on failure

fn (db &DB) exec_none(query string) int

exec_none executes the query on the given db, and returns the integer MySQL result code Use it, in case you don't expect any row results, but still want a result code. e.g. for queries like these: INSERT INTO ... VALUES (...)

fn (db &DB) exec_one(query string) !Row

exec_one executes the query on the given db, and returns either the first row from the result, if the query was successful, or an error

fn (db &DB) exec_param(query string, param string) ![]Row

exec_param executes the query with one parameter provided as an ? in the query It returns either the full result set, or an error on failure

fn (db &DB) exec_param_many(query string, params []string) ![]Row

exec_param_many executes the query with parameters provided as ?'s in the query It returns either the full result set, or an error on failure

fn (db &DB) get_host_info() string

get_host_info returns a string describing the type of connection in use, including the server host name.

fn (db &DB) get_option(option_type int) !voidptr

get_option returns the value of an option, settable by set_option. https://dev.mysql.com/doc/c-api/5.7/en/mysql-get-option.html

fn (db &DB) get_server_info() string

get_server_info returns a string representing the MySQL server version. For example, 8.0.24.

fn (db &DB) get_server_version() u64

get_server_version returns an integer, representing the MySQL server version. The value has the format XYYZZ where X is the major version, YY is the release level (or minor version), and ZZ is the sub-version within the release level. For example, 8.0.24 is returned as 80024.

fn (db &DB) info() string

info returns information about the most recently executed query. See more on https://dev.mysql.com/doc/c-api/8.0/en/mysql-info.html

fn (db DB) init_stmt(query string) Stmt

init_stmt creates a new statement, given the query.

fn (db DB) insert(table string, data orm.QueryData) !

insert is used internally by V's ORM for processing INSERT queries

fn (db DB) last_id() int

last_id is used internally by V's ORM for post-processing INSERT queries

fn (mut db DB) ping() !bool

ping pings a server connection, or tries to reconnect if the connection has gone down.

fn (db &DB) prepare(query string) !StmtHandle

prepare takes in a query string, returning a StmtHandle that can then be used to execute the query as many times as needed, which must be closed manually by the user Placeholders are represented by ?

fn (db &DB) query(q string) !Result

query executes the SQL statement pointed to by the string q. It cannot be used for statements that contain binary data; Use real_query() instead.

fn (mut db DB) real_query(q string) !Result

real_query makes an SQL query and receive the results. real_query() can be used for statements containing binary data. (Binary data may contain the \0 character, which query() interprets as the end of the statement string). In addition, real_query() is faster than query().

fn (mut db DB) refresh(options u32) !bool

refresh flush the tables or caches, or resets replication server information. The connected user must have the RELOAD privilege.

fn (mut db DB) reset() !bool

reset resets the connection, and clear the session state.

fn (db DB) select(config orm.SelectConfig, data orm.QueryData, where orm.QueryData) ![][]orm.Primitive

select is used internally by V's ORM for processing SELECT queries.

fn (mut db DB) select_db(dbname string) !bool

select_db causes the database specified by db to become the default (current) database on the connection specified by mysql.

fn (mut db DB) set_option(option_type int, val voidptr)

set_option sets extra connect options that affect the behavior of a connection. This function may be called multiple times to set several options. To retrieve the current values for an option, use get_option().

fn (db &DB) tables(wildcard string) ![]string

tables returns a list of the names of the tables in the current database, that match the simple regular expression specified by the wildcard parameter. The wildcard parameter may contain the wildcard characters % or _. If an empty string is passed, it will return all tables. Calling tables() is similar to executing query SHOW TABLES [LIKE wildcard].

fn (db DB) update(table string, data orm.QueryData, where orm.QueryData) !

update is used internally by V's ORM for processing UPDATE queries

fn (db &DB) use_result()

use_result reads the result of a query used after invoking mysql_real_query() or mysql_query(), for every statement that successfully produces a result set (SELECT, SHOW, DESCRIBE, EXPLAIN, CHECK TABLE, and so forth). This reads the result of a query directly from the server without storing it in a temporary table or local buffer, mysql_use_result is faster and uses much less memory than C.mysql_store_result(). You must mysql_free_result() after you are done with the result set.

struct Field {
	name             string
	org_name         string
	table            string
	org_table        string
	db               string
	catalog          string
	def              string
	length           int
	max_length       int
	name_length      u32
	org_name_length  u32
	table_length     u32
	org_table_length u32
	db_length        u32
	catalog_length   u32
	def_length       u32
	flags            u32
	decimals         u32
	charsetnr        u32
	type             FieldType
}

fn (f Field) str() string

str serializes the field.

struct Result {
pub:
	result &C.MYSQL_RES = unsafe { nil }
}

fn (r Result) fetch_row() &&u8

fetch_row fetches the next row from a result.

fn (r Result) n_rows() u64

n_rows returns the number of rows from a result.

fn (r Result) n_fields() int

n_fields returns the number of columns from a result.

fn (r Result) rows() []Row

rows returns array of rows, each containing an array of values, one for each column.

fn (r Result) maps() []map[string]string

maps returns an array of maps, each containing a set of field name: field value pairs.

fn (r Result) fields() []Field

fields returns an array of fields/columns. The definitions apply primarily for columns of results, such as those produced by SELECT statements.

fn (r &Result) free()

free frees the memory used by a result.

struct Row {
pub mut:
	vals []string
}

struct Stmt {
	stmt  &C.MYSQL_STMT = &C.MYSQL_STMT(unsafe { nil })
	query string
mut:
	binds []C.MYSQL_BIND
	res   []C.MYSQL_BIND
}

fn (s &Stmt) str() string

str returns a text representation of the given mysql statement s.

fn (stmt Stmt) prepare() !

prepare a statement for execution.

fn (stmt Stmt) bind_params() !

bind_params binds all the parameters in stmt.

fn (stmt Stmt) execute() !int

execute executes the given stmt and waits for the result.

fn (stmt Stmt) next() !int

next retrieves the next available result from the execution of stmt

fn (stmt Stmt) gen_metadata() &C.MYSQL_RES

gen_metadata executes mysql_stmt_result_metadata over the given stmt It requires that the statement has produced a result set, since the metadata will be for that result set. See https://dev.mysql.com/doc/c-api/5.7/en/mysql-stmt-result-metadata.html

fn (stmt Stmt) fetch_fields(res &C.MYSQL_RES) &C.MYSQL_FIELD

fetch_fields retrieves the fields from the metadata result of the execution of stmt. See https://dev.mysql.com/doc/c-api/5.7/en/mysql-fetch-fields.html See also Result.n_fields for the size of the returned C array.

fn (stmt Stmt) fetch_stmt() !int

fetch_stmt fetches the next row in the result set. It returns the status of the execution of mysql_stmt_fetch . See https://dev.mysql.com/doc/c-api/5.7/en/mysql-stmt-fetch.html

fn (stmt Stmt) close() !

close disposes the prepared stmt. The statement becomes invalid, and should not be used anymore after this call. If the current statement has pending or unread results, this method cancels them too. See https://dev.mysql.com/doc/c-api/5.7/en/mysql-stmt-close.html

fn (stmt Stmt) error(code int) IError

error returns a proper V error with a human readable description, given the error code returned by MySQL

fn (mut stmt Stmt) bind_bool(b &bool)

bind_bool binds a single boolean value to the statement stmt

fn (mut stmt Stmt) bind_byte(b &u8)

bind_byte binds a single byte value to the statement stmt

fn (mut stmt Stmt) bind_u8(b &u8)

bind_u8 binds a single u8 value to the statement stmt

fn (mut stmt Stmt) bind_i8(b &i8)

bind_i8 binds a single i8 value to the statement stmt

fn (mut stmt Stmt) bind_i16(b &i16)

bind_i16 binds a single i16 value to the statement stmt

fn (mut stmt Stmt) bind_u16(b &u16)

bind_u16 binds a single u16 value to the statement stmt

fn (mut stmt Stmt) bind_int(b &int)

bind_int binds a single int value to the statement stmt

fn (mut stmt Stmt) bind_u32(b &u32)

bind_u32 binds a single u32 value to the statement stmt

fn (mut stmt Stmt) bind_i64(b &i64)

bind_i64 binds a single i64 value to the statement stmt

fn (mut stmt Stmt) bind_u64(b &u64)

bind_u64 binds a single u64 value to the statement stmt

fn (mut stmt Stmt) bind_f32(b &f32)

bind_f32 binds a single f32 value to the statement stmt

fn (mut stmt Stmt) bind_f64(b &f64)

bind_f64 binds a single f64 value to the statement stmt

fn (mut stmt Stmt) bind_text(b string)

bind_text binds a single string value to the statement stmt

fn (mut stmt Stmt) bind_null()

bind_null binds a single NULL value to the statement stmt

fn (mut stmt Stmt) bind(typ int, buffer voidptr, buf_len u32)

bind binds a single value pointed by buffer, to the statement stmt. The buffer length must be passed as well in buf_len.

Note: it is more convenient to use one of the other bind_XYZ methods.

fn (mut stmt Stmt) bind_res(fields &C.MYSQL_FIELD, dataptr []&u8, lengths []u32, num_fields int)

bind_res will store one result in the statement stmt

fn (mut stmt Stmt) bind_result_buffer() !

bind_result_buffer binds one result value, by calling mysql_stmt_bind_result . See https://dev.mysql.com/doc/c-api/8.0/en/mysql-stmt-bind-result.html

fn (mut stmt Stmt) store_result() !

store_result will buffer the complete result set from the execution of stmt on the client side.

Note: result sets are produced by calling mysql_stmt_execute() to executed prepared statements for SQL statements such as SELECT, SHOW, DESCRIBE, and EXPLAIN. By default, result sets for successfully executed prepared statements are not buffered on the client, and mysql_stmt_fetch() fetches them one at a time from the server. Note 2: call store_result, after binding data buffers with bind_result_buffer, and before calling fetch_stmt to fetch rows. See https://dev.mysql.com/doc/c-api/8.0/en/mysql-stmt-store-result.html

fn (mut stmt Stmt) fetch_column(bind &C.MYSQL_BIND, column int) !

fetch_column fetches one column from the current result set row. bind provides the buffer where data should be placed. It should be set up the same way as for mysql_stmt_bind_result(). column indicates which column to fetch. The first column is numbered 0.

struct StmtHandle {
	stmt &C.MYSQL_STMT = &C.MYSQL_STMT(unsafe { nil })
	db   DB
}

A StmtHandle is created through prepare, it will be bound to one DB connection and will become unusable if the connection is closed

fn (stmt &StmtHandle) execute(params []string) ![]Row

execute takes in an array of params that will be bound to the statement, followed by it's execution Returns an array of Rows, which will be empty if nothing is returned from the query, or possibly an error value

fn (stmt &StmtHandle) close()

close acts on a StmtHandle to close the mysql Stmt meaning it is no longer available for use