From f5c4671bfbad96bf346bd7e9a21fc4317b4959df Mon Sep 17 00:00:00 2001 From: Indrajith K L Date: Sat, 3 Dec 2022 17:00:20 +0530 Subject: Adds most of the tools --- v_windows/v/vlib/mysql/README.md | 30 +++ v_windows/v/vlib/mysql/_cdefs.c.v | 101 ++++++++++ v_windows/v/vlib/mysql/_cdefs_nix.c.v | 11 ++ v_windows/v/vlib/mysql/_cdefs_windows.c.v | 5 + v_windows/v/vlib/mysql/consts.v | 13 ++ v_windows/v/vlib/mysql/enums.v | 78 ++++++++ v_windows/v/vlib/mysql/mysql.v | 239 ++++++++++++++++++++++++ v_windows/v/vlib/mysql/mysql_orm_test.v | 77 ++++++++ v_windows/v/vlib/mysql/orm.v | 296 ++++++++++++++++++++++++++++++ v_windows/v/vlib/mysql/result.v | 153 +++++++++++++++ v_windows/v/vlib/mysql/stmt.c.v | 233 +++++++++++++++++++++++ v_windows/v/vlib/mysql/utils.v | 26 +++ 12 files changed, 1262 insertions(+) create mode 100644 v_windows/v/vlib/mysql/README.md create mode 100644 v_windows/v/vlib/mysql/_cdefs.c.v create mode 100644 v_windows/v/vlib/mysql/_cdefs_nix.c.v create mode 100644 v_windows/v/vlib/mysql/_cdefs_windows.c.v create mode 100644 v_windows/v/vlib/mysql/consts.v create mode 100644 v_windows/v/vlib/mysql/enums.v create mode 100644 v_windows/v/vlib/mysql/mysql.v create mode 100644 v_windows/v/vlib/mysql/mysql_orm_test.v create mode 100644 v_windows/v/vlib/mysql/orm.v create mode 100644 v_windows/v/vlib/mysql/result.v create mode 100644 v_windows/v/vlib/mysql/stmt.c.v create mode 100644 v_windows/v/vlib/mysql/utils.v (limited to 'v_windows/v/vlib/mysql') diff --git a/v_windows/v/vlib/mysql/README.md b/v_windows/v/vlib/mysql/README.md new file mode 100644 index 0000000..3d0ab97 --- /dev/null +++ b/v_windows/v/vlib/mysql/README.md @@ -0,0 +1,30 @@ +For Linux, you need to install `MySQL development` package and `pkg-config`. +For Windows, install [the installer](https://dev.mysql.com/downloads/installer/) , +then copy the `include` and `lib` folders to `\thirdparty\mysql`. + +## Basic Usage + +```v oksyntax +import 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() +``` diff --git a/v_windows/v/vlib/mysql/_cdefs.c.v b/v_windows/v/vlib/mysql/_cdefs.c.v new file mode 100644 index 0000000..23aebed --- /dev/null +++ b/v_windows/v/vlib/mysql/_cdefs.c.v @@ -0,0 +1,101 @@ +module mysql + +[typedef] +struct C.MYSQL { +} + +[typedef] +struct C.MYSQL_RES { +} + +[typedef] +struct C.MYSQL_FIELD { + name &byte // Name of column + org_name &byte // Original column name, if an alias + table &byte // Table of column if column was a field + org_table &byte // Org table name, if table was an alias + db &byte // Database for table + catalog &byte // Catalog for table + def &byte // 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 // Div flags + decimals u32 // Number of decimals in field + charsetnr u32 // Character set + @type int // Type of field. See mysql_com.h for types +} + +fn C.mysql_init(mysql &C.MYSQL) &C.MYSQL + +fn C.mysql_real_connect(mysql &C.MYSQL, host &char, user &char, passwd &char, db &char, port u32, unix_socket &char, client_flag ConnectionFlag) &C.MYSQL + +fn C.mysql_query(mysql &C.MYSQL, q &byte) int + +fn C.mysql_real_query(mysql &C.MYSQL, q &byte, len u32) int + +fn C.mysql_select_db(mysql &C.MYSQL, db &byte) int + +fn C.mysql_change_user(mysql &C.MYSQL, user &byte, password &byte, db &byte) bool + +fn C.mysql_affected_rows(mysql &C.MYSQL) u64 + +fn C.mysql_options(mysql &C.MYSQL, option int, arg voidptr) int + +fn C.mysql_get_option(mysql &C.MYSQL, option int, arg voidptr) int + +fn C.mysql_list_tables(mysql &C.MYSQL, wild &byte) &C.MYSQL_RES + +fn C.mysql_num_fields(res &C.MYSQL_RES) int + +fn C.mysql_num_rows(res &C.MYSQL_RES) u64 + +fn C.mysql_autocommit(mysql &C.MYSQL, mode bool) + +fn C.mysql_refresh(mysql &C.MYSQL, options u32) int + +fn C.mysql_reset_connection(mysql &C.MYSQL) int + +fn C.mysql_ping(mysql &C.MYSQL) int + +fn C.mysql_store_result(mysql &C.MYSQL) &C.MYSQL_RES + +fn C.mysql_fetch_row(res &C.MYSQL_RES) &&byte + +fn C.mysql_fetch_fields(res &C.MYSQL_RES) &C.MYSQL_FIELD + +fn C.mysql_free_result(res &C.MYSQL_RES) + +fn C.mysql_real_escape_string(mysql &C.MYSQL, to &byte, from &byte, len u64) u64 + +// fn C.mysql_real_escape_string_quote(mysql &C.MYSQL, to &byte, from &byte, len u64, quote byte) u64 (Don't exist in mariadb) + +fn C.mysql_close(sock &C.MYSQL) + +// INFO & VERSION +fn C.mysql_info(mysql &C.MYSQL) &byte + +fn C.mysql_get_host_info(mysql &C.MYSQL) &byte + +fn C.mysql_get_server_info(mysql &C.MYSQL) &byte + +fn C.mysql_get_server_version(mysql &C.MYSQL) u64 + +fn C.mysql_get_client_version() u64 + +fn C.mysql_get_client_info() &byte + +// DEBUG & ERROR INFO +fn C.mysql_error(mysql &C.MYSQL) &byte + +fn C.mysql_errno(mysql &C.MYSQL) int + +fn C.mysql_dump_debug_info(mysql &C.MYSQL) int + +fn C.mysql_debug(debug &byte) diff --git a/v_windows/v/vlib/mysql/_cdefs_nix.c.v b/v_windows/v/vlib/mysql/_cdefs_nix.c.v new file mode 100644 index 0000000..1462e89 --- /dev/null +++ b/v_windows/v/vlib/mysql/_cdefs_nix.c.v @@ -0,0 +1,11 @@ +module mysql + +// Need to check if mysqlclient is not there and use mariadb as alternative because newer system doesn't support mysql 8.0 as default + +$if $pkgconfig('mysqlclient') { + #pkgconfig mysqlclient +} $else { + #pkgconfig mariadb +} + +#include # Please install the mysqlclient development headers diff --git a/v_windows/v/vlib/mysql/_cdefs_windows.c.v b/v_windows/v/vlib/mysql/_cdefs_windows.c.v new file mode 100644 index 0000000..9e18696 --- /dev/null +++ b/v_windows/v/vlib/mysql/_cdefs_windows.c.v @@ -0,0 +1,5 @@ +module mysql + +#flag windows -I@VEXEROOT/thirdparty/mysql/include +#flag windows @VEXEROOT/thirdparty/mysql/lib/libmysql.dll +#include # Please install https://dev.mysql.com/downloads/installer/ , then put the include/ and lib/ folders in thirdparty/mysql diff --git a/v_windows/v/vlib/mysql/consts.v b/v_windows/v/vlib/mysql/consts.v new file mode 100644 index 0000000..2e9962a --- /dev/null +++ b/v_windows/v/vlib/mysql/consts.v @@ -0,0 +1,13 @@ +module mysql + +// MYSQL REFRESH FLAGS +pub const ( + refresh_grant = u32(C.REFRESH_GRANT) + refresh_log = u32(C.REFRESH_LOG) + refresh_tables = u32(C.REFRESH_TABLES) + refresh_hosts = u32(C.REFRESH_HOSTS) + refresh_status = u32(C.REFRESH_STATUS) + refresh_threads = u32(C.REFRESH_THREADS) + refresh_slave = u32(C.REFRESH_SLAVE) + refresh_master = u32(C.REFRESH_MASTER) +) diff --git a/v_windows/v/vlib/mysql/enums.v b/v_windows/v/vlib/mysql/enums.v new file mode 100644 index 0000000..e2bfef5 --- /dev/null +++ b/v_windows/v/vlib/mysql/enums.v @@ -0,0 +1,78 @@ +module mysql + +pub 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 +} + +pub fn (f FieldType) str() string { + return match f { + .type_decimal { 'decimal' } + .type_tiny { 'tiny' } + .type_short { 'short' } + .type_long { 'long' } + .type_float { 'float' } + .type_double { 'double' } + .type_null { 'null' } + .type_timestamp { 'timestamp' } + .type_longlong { 'longlong' } + .type_int24 { 'int24' } + .type_date { 'date' } + .type_time { 'time' } + .type_datetime { 'datetime' } + .type_year { 'year' } + .type_newdate { 'newdate' } + .type_varchar { 'varchar' } + .type_bit { 'bit' } + .type_timestamp2 { 'timestamp2' } + .type_datetime2 { 'datetime2' } + .type_time2 { 'time2' } + .type_json { 'json' } + .type_newdecimal { 'newdecimal' } + .type_enum { 'enum' } + .type_set { 'set' } + .type_tiny_blob { 'tiny_blob' } + .type_medium_blob { 'medium_blob' } + .type_long_blob { 'long_blob' } + .type_blob { 'blob' } + .type_var_string { 'var_string' } + .type_string { 'string' } + .type_geometry { 'geometry' } + } +} + +pub fn (f FieldType) get_len() u32 { + return match f { + .type_blob { 262140 } + else { 0 } + } +} diff --git a/v_windows/v/vlib/mysql/mysql.v b/v_windows/v/vlib/mysql/mysql.v new file mode 100644 index 0000000..90e7008 --- /dev/null +++ b/v_windows/v/vlib/mysql/mysql.v @@ -0,0 +1,239 @@ +module mysql + +// 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 +pub enum ConnectionFlag { + // CAN_HANDLE_EXPIRED_PASSWORDS = C.CAN_HANDLE_EXPIRED_PASSWORDS + 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_optional_resultset_metadata = C.CLIENT_OPTIONAL_RESULTSET_METADATA + client_ssl = C.CLIENT_SSL + client_remember_options = C.CLIENT_REMEMBER_OPTIONS +} + +struct SQLError { + msg string + code int +} + +// TODO: Documentation +pub struct Connection { +mut: + conn &C.MYSQL = C.mysql_init(0) +pub mut: + host string = '127.0.0.1' + port u32 = 3306 + username string + password string + dbname string + flag ConnectionFlag +} + +// connect - create a new connection to the MySQL server. +pub fn (mut conn Connection) connect() ?bool { + instance := C.mysql_init(conn.conn) + conn.conn = C.mysql_real_connect(instance, conn.host.str, conn.username.str, conn.password.str, + conn.dbname.str, conn.port, 0, conn.flag) + if isnil(conn.conn) { + return error_with_code(get_error_msg(instance), get_errno(instance)) + } + return true +} + +// query - make an SQL query and receive the results. +// `query()` cannot be used for statements that contain binary data; +// Use `real_query()` instead. +pub fn (conn Connection) query(q string) ?Result { + if C.mysql_query(conn.conn, q.str) != 0 { + return error_with_code(get_error_msg(conn.conn), get_errno(conn.conn)) + } + res := C.mysql_store_result(conn.conn) + return Result{res} +} + +// real_query - make 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()`. +pub fn (mut conn Connection) real_query(q string) ?Result { + if C.mysql_real_query(conn.conn, q.str, q.len) != 0 { + return error_with_code(get_error_msg(conn.conn), get_errno(conn.conn)) + } + res := C.mysql_store_result(conn.conn) + return Result{res} +} + +// select_db - change the default database for database queries. +pub fn (mut conn Connection) select_db(dbname string) ?bool { + if C.mysql_select_db(conn.conn, dbname.str) != 0 { + return error_with_code(get_error_msg(conn.conn), get_errno(conn.conn)) + } + return true +} + +// change_user - change 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. +pub fn (mut conn Connection) change_user(username string, password string, dbname string) ?bool { + mut ret := true + if dbname != '' { + ret = C.mysql_change_user(conn.conn, username.str, password.str, dbname.str) + } else { + ret = C.mysql_change_user(conn.conn, username.str, password.str, 0) + } + if !ret { + return error_with_code(get_error_msg(conn.conn), get_errno(conn.conn)) + } + return ret +} + +// affected_rows - return the number of rows changed/deleted/inserted +// by the last `UPDATE`, `DELETE`, or `INSERT` query. +pub fn (conn &Connection) affected_rows() u64 { + return C.mysql_affected_rows(conn.conn) +} + +// autocommit - turns on/off the auto-committing mode for the connection. +// When it is on, then each query is commited right away. +pub fn (mut conn Connection) autocommit(mode bool) { + C.mysql_autocommit(conn.conn, mode) +} + +// 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]`. +pub fn (conn &Connection) tables(wildcard string) ?[]string { + cres := C.mysql_list_tables(conn.conn, wildcard.str) + if isnil(cres) { + return error_with_code(get_error_msg(conn.conn), get_errno(conn.conn)) + } + res := Result{cres} + mut tables := []string{} + for row in res.rows() { + tables << row.vals[0] + } + return tables +} + +// 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. +pub fn (conn &Connection) escape_string(s string) string { + unsafe { + to := malloc_noscan(2 * s.len + 1) + C.mysql_real_escape_string(conn.conn, to, s.str, s.len) + return to.vstring() + } +} + +// 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()`. +pub fn (mut conn Connection) set_option(option_type int, val voidptr) { + C.mysql_options(conn.conn, option_type, val) +} + +// get_option - return the value of an option, settable by `set_option`. +// https://dev.mysql.com/doc/c-api/5.7/en/mysql-get-option.html +pub fn (conn &Connection) get_option(option_type int) ?voidptr { + ret := voidptr(0) + if C.mysql_get_option(conn.conn, option_type, &ret) != 0 { + return error_with_code(get_error_msg(conn.conn), get_errno(conn.conn)) + } + return ret +} + +// refresh - flush the tables or caches, or resets replication server +// information. The connected user must have the `RELOAD` privilege. +pub fn (mut conn Connection) refresh(options u32) ?bool { + if C.mysql_refresh(conn.conn, options) != 0 { + return error_with_code(get_error_msg(conn.conn), get_errno(conn.conn)) + } + return true +} + +// reset - resets the connection, and clear the session state. +pub fn (mut conn Connection) reset() ?bool { + if C.mysql_reset_connection(conn.conn) != 0 { + return error_with_code(get_error_msg(conn.conn), get_errno(conn.conn)) + } + return true +} + +// ping - pings a server connection, or tries to reconnect if the connection +// has gone down. +pub fn (mut conn Connection) ping() ?bool { + if C.mysql_ping(conn.conn) != 0 { + return error_with_code(get_error_msg(conn.conn), get_errno(conn.conn)) + } + return true +} + +// close - closes the connection. +pub fn (mut conn Connection) close() { + C.mysql_close(conn.conn) +} + +// 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 +pub fn (conn &Connection) info() string { + return resolve_nil_str(C.mysql_info(conn.conn)) +} + +// get_host_info - returns a string describing the type of connection in use, +// including the server host name. +pub fn (conn &Connection) get_host_info() string { + return unsafe { C.mysql_get_host_info(conn.conn).vstring() } +} + +// get_server_info - returns a string representing the MySQL server version. +// For example, `8.0.24`. +pub fn (conn &Connection) get_server_info() string { + return unsafe { C.mysql_get_server_info(conn.conn).vstring() } +} + +// 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`. +pub fn (conn &Connection) get_server_version() u64 { + return C.mysql_get_server_version(conn.conn) +} + +// dump_debug_info - instructs the server to write debugging information +// to the error log. The connected user must have the `SUPER` privilege. +pub fn (mut conn Connection) dump_debug_info() ?bool { + if C.mysql_dump_debug_info(conn.conn) != 0 { + return error_with_code(get_error_msg(conn.conn), get_errno(conn.conn)) + } + return true +} + +// get_client_info - returns client version information as a string. +pub fn get_client_info() string { + return unsafe { C.mysql_get_client_info().vstring() } +} + +// get_client_version - returns the client version information as an integer. +pub fn get_client_version() u64 { + return C.mysql_get_client_version() +} + +// 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 +pub fn debug(debug string) { + C.mysql_debug(debug.str) +} diff --git a/v_windows/v/vlib/mysql/mysql_orm_test.v b/v_windows/v/vlib/mysql/mysql_orm_test.v new file mode 100644 index 0000000..2acb182 --- /dev/null +++ b/v_windows/v/vlib/mysql/mysql_orm_test.v @@ -0,0 +1,77 @@ +import orm +import mysql + +fn test_mysql_orm() { + mut mdb := mysql.Connection{ + host: 'localhost' + port: 3306 + username: 'root' + password: '' + dbname: 'mysql' + } + mdb.connect() or { panic(err) } + db := orm.Connection(mdb) + db.create('Test', [ + orm.TableField{ + name: 'id' + typ: 7 + attrs: [ + StructAttribute{ + name: 'primary' + }, + StructAttribute{ + name: 'sql' + has_arg: true + kind: .plain + arg: 'serial' + }, + ] + }, + orm.TableField{ + name: 'name' + typ: 18 + attrs: [] + }, + orm.TableField{ + name: 'age' + typ: 7 + }, + ]) or { panic(err) } + + db.insert('Test', orm.QueryData{ + fields: ['name', 'age'] + data: [orm.string_to_primitive('Louis'), orm.int_to_primitive(101)] + }) or { panic(err) } + + res := db.@select(orm.SelectConfig{ + table: 'Test' + has_where: true + fields: ['id', 'name', 'age'] + types: [7, 18, 8] + }, orm.QueryData{}, orm.QueryData{ + fields: ['name', 'age'] + data: [orm.Primitive('Louis'), i64(101)] + types: [18, 8] + is_and: [true, true] + kinds: [.eq, .eq] + }) or { panic(err) } + + id := res[0][0] + name := res[0][1] + age := res[0][2] + + assert id is int + if id is int { + assert id == 1 + } + + assert name is string + if name is string { + assert name == 'Louis' + } + + assert age is i64 + if age is i64 { + assert age == 101 + } +} diff --git a/v_windows/v/vlib/mysql/orm.v b/v_windows/v/vlib/mysql/orm.v new file mode 100644 index 0000000..3a19a16 --- /dev/null +++ b/v_windows/v/vlib/mysql/orm.v @@ -0,0 +1,296 @@ +module mysql + +import orm +import time + +type Prims = byte | f32 | f64 | i16 | i64 | i8 | int | string | u16 | u32 | u64 + +// sql expr + +pub fn (db Connection) @select(config orm.SelectConfig, data orm.QueryData, where orm.QueryData) ?[][]orm.Primitive { + query := orm.orm_select_gen(config, '`', false, '?', 0, where) + mut ret := [][]orm.Primitive{} + mut stmt := db.init_stmt(query) + stmt.prepare() ? + + mysql_stmt_binder(mut stmt, where) ? + mysql_stmt_binder(mut stmt, data) ? + if data.data.len > 0 || where.data.len > 0 { + stmt.bind_params() ? + } + + mut status := stmt.execute() ? + num_fields := stmt.get_field_count() + metadata := stmt.gen_metadata() + fields := stmt.fetch_fields(metadata) + + mut dataptr := []Prims{} + + for i in 0 .. num_fields { + f := unsafe { fields[i] } + match FieldType(f.@type) { + .type_tiny { + dataptr << byte(0) + } + .type_short { + dataptr << u16(0) + } + .type_long { + dataptr << u32(0) + } + .type_longlong { + dataptr << u64(0) + } + .type_float { + dataptr << f32(0) + } + .type_double { + dataptr << f64(0) + } + .type_string { + dataptr << '' + } + else { + dataptr << byte(0) + } + } + } + + mut vptr := []&char{} + + for d in dataptr { + vptr << d.get_data_ptr() + } + + unsafe { dataptr.free() } + + lens := []u32{len: int(num_fields), init: 0} + stmt.bind_res(fields, vptr, lens, num_fields) + stmt.bind_result_buffer() ? + stmt.store_result() ? + + mut row := 0 + + for { + status = stmt.fetch_stmt() ? + + if status == 1 || status == 100 { + break + } + row++ + data_list := buffer_to_primitive(vptr, config.types) ? + ret << data_list + } + + stmt.close() ? + + return ret +} + +// sql stmt + +pub fn (db Connection) insert(table string, data orm.QueryData) ? { + query := orm.orm_stmt_gen(table, '`', .insert, false, '?', 1, data, orm.QueryData{}) + mysql_stmt_worker(db, query, data, orm.QueryData{}) ? +} + +pub fn (db Connection) update(table string, data orm.QueryData, where orm.QueryData) ? { + query := orm.orm_stmt_gen(table, '`', .update, false, '?', 1, data, where) + mysql_stmt_worker(db, query, data, where) ? +} + +pub fn (db Connection) delete(table string, where orm.QueryData) ? { + query := orm.orm_stmt_gen(table, '`', .delete, false, '?', 1, orm.QueryData{}, where) + mysql_stmt_worker(db, query, orm.QueryData{}, where) ? +} + +pub fn (db Connection) last_id() orm.Primitive { + query := 'SELECT last_insert_rowid();' + id := db.query(query) or { + Result{ + result: 0 + } + } + return orm.Primitive(id.rows()[0].vals[0].int()) +} + +// table +pub fn (db Connection) create(table string, fields []orm.TableField) ? { + query := orm.orm_table_gen(table, '`', false, 0, fields, mysql_type_from_v, false) or { + return err + } + mysql_stmt_worker(db, query, orm.QueryData{}, orm.QueryData{}) ? +} + +pub fn (db Connection) drop(table string) ? { + query := 'DROP TABLE `$table`;' + mysql_stmt_worker(db, query, orm.QueryData{}, orm.QueryData{}) ? +} + +fn mysql_stmt_worker(db Connection, query string, data orm.QueryData, where orm.QueryData) ? { + mut stmt := db.init_stmt(query) + stmt.prepare() ? + mysql_stmt_binder(mut stmt, data) ? + mysql_stmt_binder(mut stmt, where) ? + if data.data.len > 0 || where.data.len > 0 { + stmt.bind_params() ? + } + stmt.execute() ? + stmt.close() ? +} + +fn mysql_stmt_binder(mut stmt Stmt, d orm.QueryData) ? { + for data in d.data { + stmt_binder_match(mut stmt, data) + } +} + +fn stmt_binder_match(mut stmt Stmt, data orm.Primitive) { + match data { + bool { + stmt.bind_bool(&data) + } + i8 { + stmt.bind_i8(&data) + } + i16 { + stmt.bind_i16(&data) + } + int { + stmt.bind_int(&data) + } + i64 { + stmt.bind_i64(&data) + } + byte { + stmt.bind_byte(&data) + } + u16 { + stmt.bind_u16(&data) + } + u32 { + stmt.bind_u32(&data) + } + u64 { + stmt.bind_u64(&data) + } + f32 { + stmt.bind_f32(unsafe { &f32(&data) }) + } + f64 { + stmt.bind_f64(unsafe { &f64(&data) }) + } + string { + stmt.bind_text(data) + } + time.Time { + stmt.bind_int(&int(data.unix)) + } + orm.InfixType { + stmt_binder_match(mut stmt, data.right) + } + } +} + +fn buffer_to_primitive(data_list []&char, types []int) ?[]orm.Primitive { + mut res := []orm.Primitive{} + + for i, data in data_list { + mut primitive := orm.Primitive(0) + match types[i] { + 5 { + primitive = *(&i8(data)) + } + 6 { + primitive = *(&i16(data)) + } + 7, -1 { + primitive = *(&int(data)) + } + 8 { + primitive = *(&i64(data)) + } + 9 { + primitive = *(&byte(data)) + } + 10 { + primitive = *(&u16(data)) + } + 11 { + primitive = *(&u32(data)) + } + 12 { + primitive = *(&u64(data)) + } + 13 { + primitive = *(&f32(data)) + } + 14 { + primitive = *(&f64(data)) + } + 15 { + primitive = *(&bool(data)) + } + orm.string { + primitive = unsafe { cstring_to_vstring(&char(data)) } + } + orm.time { + timestamp := *(&int(data)) + primitive = time.unix(timestamp) + } + else { + return error('Unknown type ${types[i]}') + } + } + res << primitive + } + + return res +} + +fn mysql_type_from_v(typ int) ?string { + str := match typ { + 5, 9, 16 { + 'TINYINT' + } + 6, 10 { + 'SMALLINT' + } + 7, 11, orm.time { + 'INT' + } + 8, 12 { + 'BIGINT' + } + 13 { + 'FLOAT' + } + 14 { + 'DOUBLE' + } + orm.string { + 'TEXT' + } + -1 { + 'SERIAL' + } + else { + '' + } + } + if str == '' { + return error('Unknown type $typ') + } + return str +} + +fn (p Prims) get_data_ptr() &char { + return match p { + string { + p.str + } + else { + &char(&p) + } + } +} diff --git a/v_windows/v/vlib/mysql/result.v b/v_windows/v/vlib/mysql/result.v new file mode 100644 index 0000000..e9e1909 --- /dev/null +++ b/v_windows/v/vlib/mysql/result.v @@ -0,0 +1,153 @@ +module mysql + +pub struct Result { + result &C.MYSQL_RES +} + +pub struct Row { +pub mut: + vals []string +} + +pub 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 +} + +// fetch_row - fetches the next row from a result. +pub fn (r Result) fetch_row() &&byte { + return C.mysql_fetch_row(r.result) +} + +// n_rows - returns the number of rows from a result. +pub fn (r Result) n_rows() u64 { + return C.mysql_num_rows(r.result) +} + +// n_fields - returns the number of columns from a result. +pub fn (r Result) n_fields() int { + return C.mysql_num_fields(r.result) +} + +// rows - returns array of rows, each containing an array of values, +// one for each column. +pub fn (r Result) rows() []Row { + mut rows := []Row{} + nr_cols := r.n_fields() + for rr := r.fetch_row(); rr; rr = r.fetch_row() { + mut row := Row{} + for i in 0 .. nr_cols { + if unsafe { rr[i] == 0 } { + row.vals << '' + } else { + row.vals << mystring(unsafe { &byte(rr[i]) }) + } + } + rows << row + } + return rows +} + +// maps - returns an array of maps, each containing a set of +// field name: field value pairs. +pub fn (r Result) maps() []map[string]string { + mut array_map := []map[string]string{} + rows := r.rows() + fields := r.fields() + for i in 0 .. rows.len { + mut map_val := map[string]string{} + for j in 0 .. fields.len { + map_val[fields[j].name] = rows[i].vals[j] + } + array_map << map_val + } + return array_map +} + +// fields - returns an array of fields/columns. +// The definitions apply primarily for columns of results, +// such as those produced by `SELECT` statements. +pub fn (r Result) fields() []Field { + mut fields := []Field{} + nr_cols := r.n_fields() + orig_fields := C.mysql_fetch_fields(r.result) + for i in 0 .. nr_cols { + unsafe { + fields << Field{ + name: mystring(orig_fields[i].name) + org_name: mystring(orig_fields[i].org_name) + table: mystring(orig_fields[i].table) + org_table: mystring(orig_fields[i].org_table) + db: mystring(orig_fields[i].db) + catalog: mystring(orig_fields[i].catalog) + def: resolve_nil_str(orig_fields[i].def) + length: orig_fields.length + max_length: orig_fields.max_length + name_length: orig_fields.name_length + org_name_length: orig_fields.org_name_length + table_length: orig_fields.table_length + org_table_length: orig_fields.org_table_length + db_length: orig_fields.db_length + catalog_length: orig_fields.catalog_length + def_length: orig_fields.def_length + flags: orig_fields.flags + decimals: orig_fields.decimals + charsetnr: orig_fields.charsetnr + type_: FieldType(orig_fields.@type) + } + } + } + return fields +} + +// str - serializes the field +pub fn (f Field) str() string { + return ' +{ + name: "$f.name" + org_name: "$f.org_name" + table: "$f.table" + org_table: "$f.org_table" + db: "$f.db" + catalog: "$f.catalog" + def: "$f.def" + length: $f.length + max_length: $f.max_length + name_length: $f.name_length + org_name_length: $f.org_name_length + table_length: $f.table_length + org_table_length: $f.org_table_length + db_length: $f.db_length + catalog_length: $f.catalog_length + def_length: $f.def_length + flags: $f.flags + decimals: $f.decimals + charsetnr: $f.charsetnr + type: $f.type_.str() +} +' +} + +// free - frees the memory used by a result +[unsafe] +pub fn (r &Result) free() { + C.mysql_free_result(r.result) +} diff --git a/v_windows/v/vlib/mysql/stmt.c.v b/v_windows/v/vlib/mysql/stmt.c.v new file mode 100644 index 0000000..ee91746 --- /dev/null +++ b/v_windows/v/vlib/mysql/stmt.c.v @@ -0,0 +1,233 @@ +module mysql + +[typedef] +struct C.MYSQL_STMT { + mysql &C.MYSQL + stmt_id u32 +} + +[typedef] +struct C.MYSQL_BIND { +mut: + buffer_type int + buffer voidptr + buffer_length u32 + length &u32 +} + +const ( + mysql_type_decimal = C.MYSQL_TYPE_DECIMAL + mysql_type_tiny = C.MYSQL_TYPE_TINY + mysql_type_short = C.MYSQL_TYPE_SHORT + mysql_type_long = C.MYSQL_TYPE_LONG + mysql_type_float = C.MYSQL_TYPE_FLOAT + mysql_type_double = C.MYSQL_TYPE_DOUBLE + mysql_type_null = C.MYSQL_TYPE_NULL + mysql_type_timestamp = C.MYSQL_TYPE_TIMESTAMP + mysql_type_longlong = C.MYSQL_TYPE_LONGLONG + mysql_type_int24 = C.MYSQL_TYPE_INT24 + mysql_type_date = C.MYSQL_TYPE_DATE + mysql_type_time = C.MYSQL_TYPE_TIME + mysql_type_datetime = C.MYSQL_TYPE_DATETIME + mysql_type_year = C.MYSQL_TYPE_YEAR + mysql_type_varchar = C.MYSQL_TYPE_VARCHAR + mysql_type_bit = C.MYSQL_TYPE_BIT + mysql_type_timestamp22 = C.MYSQL_TYPE_TIMESTAMP + mysql_type_json = C.MYSQL_TYPE_JSON + mysql_type_newdecimal = C.MYSQL_TYPE_NEWDECIMAL + mysql_type_enum = C.MYSQL_TYPE_ENUM + mysql_type_set = C.MYSQL_TYPE_SET + mysql_type_tiny_blob = C.MYSQL_TYPE_TINY_BLOB + mysql_type_medium_blob = C.MYSQL_TYPE_MEDIUM_BLOB + mysql_type_long_blob = C.MYSQL_TYPE_LONG_BLOB + mysql_type_blob = C.MYSQL_TYPE_BLOB + mysql_type_var_string = C.MYSQL_TYPE_VAR_STRING + mysql_type_string = C.MYSQL_TYPE_STRING + mysql_type_geometry = C.MYSQL_TYPE_GEOMETRY + mysql_no_data = C.MYSQL_NO_DATA +) + +fn C.mysql_stmt_init(&C.MYSQL) &C.MYSQL_STMT +fn C.mysql_stmt_prepare(&C.MYSQL_STMT, &char, u32) int +fn C.mysql_stmt_bind_param(&C.MYSQL_STMT, &C.MYSQL_BIND) bool +fn C.mysql_stmt_execute(&C.MYSQL_STMT) int +fn C.mysql_stmt_close(&C.MYSQL_STMT) bool +fn C.mysql_stmt_free_result(&C.MYSQL_STMT) bool +fn C.mysql_stmt_error(&C.MYSQL_STMT) &char +fn C.mysql_stmt_result_metadata(&C.MYSQL_STMT) &C.MYSQL_RES + +fn C.mysql_stmt_field_count(&C.MYSQL_STMT) u16 +fn C.mysql_stmt_bind_result(&C.MYSQL_STMT, &C.MYSQL_BIND) bool +fn C.mysql_stmt_fetch(&C.MYSQL_STMT) int +fn C.mysql_stmt_next_result(&C.MYSQL_STMT) int +fn C.mysql_stmt_store_result(&C.MYSQL_STMT) int + +struct Stmt { + stmt &C.MYSQL_STMT = &C.MYSQL_STMT(0) + query string +mut: + binds []C.MYSQL_BIND + res []C.MYSQL_BIND +} + +pub fn (db Connection) init_stmt(query string) Stmt { + return Stmt{ + stmt: C.mysql_stmt_init(db.conn) + query: query + binds: []C.MYSQL_BIND{} + } +} + +pub fn (stmt Stmt) prepare() ? { + res := C.mysql_stmt_prepare(stmt.stmt, stmt.query.str, stmt.query.len) + if res != 0 && stmt.get_error_msg() != '' { + return stmt.error(res) + } +} + +pub fn (stmt Stmt) bind_params() ? { + res := C.mysql_stmt_bind_param(stmt.stmt, &C.MYSQL_BIND(stmt.binds.data)) + if res && stmt.get_error_msg() != '' { + return stmt.error(1) + } +} + +pub fn (stmt Stmt) execute() ?int { + res := C.mysql_stmt_execute(stmt.stmt) + if res != 0 && stmt.get_error_msg() != '' { + return stmt.error(res) + } + return res +} + +pub fn (stmt Stmt) next() ?int { + res := C.mysql_stmt_next_result(stmt.stmt) + if res > 0 && stmt.get_error_msg() != '' { + return stmt.error(res) + } + return res +} + +pub fn (stmt Stmt) gen_metadata() &C.MYSQL_RES { + return C.mysql_stmt_result_metadata(stmt.stmt) +} + +pub fn (stmt Stmt) fetch_fields(res &C.MYSQL_RES) &C.MYSQL_FIELD { + return C.mysql_fetch_fields(res) +} + +pub fn (stmt Stmt) fetch_stmt() ?int { + res := C.mysql_stmt_fetch(stmt.stmt) + if res !in [0, 100] && stmt.get_error_msg() != '' { + return stmt.error(res) + } + return res +} + +pub fn (stmt Stmt) close() ? { + if !C.mysql_stmt_close(stmt.stmt) && stmt.get_error_msg() != '' { + return stmt.error(1) + } + if !C.mysql_stmt_free_result(stmt.stmt) && stmt.get_error_msg() != '' { + return stmt.error(1) + } +} + +fn (stmt Stmt) get_error_msg() string { + return unsafe { cstring_to_vstring(&char(C.mysql_stmt_error(stmt.stmt))) } +} + +pub fn (stmt Stmt) error(code int) IError { + msg := stmt.get_error_msg() + return IError(&SQLError{ + msg: '$msg ($code) ($stmt.query)' + code: code + }) +} + +fn (stmt Stmt) get_field_count() u16 { + return C.mysql_stmt_field_count(stmt.stmt) +} + +pub fn (mut stmt Stmt) bind_bool(b &bool) { + stmt.bind(mysql.mysql_type_tiny, b, 0) +} + +pub fn (mut stmt Stmt) bind_byte(b &byte) { + stmt.bind(mysql.mysql_type_tiny, b, 0) +} + +pub fn (mut stmt Stmt) bind_i8(b &i8) { + stmt.bind(mysql.mysql_type_tiny, b, 0) +} + +pub fn (mut stmt Stmt) bind_i16(b &i16) { + stmt.bind(mysql.mysql_type_short, b, 0) +} + +pub fn (mut stmt Stmt) bind_u16(b &u16) { + stmt.bind(mysql.mysql_type_short, b, 0) +} + +pub fn (mut stmt Stmt) bind_int(b &int) { + stmt.bind(mysql.mysql_type_long, b, 0) +} + +pub fn (mut stmt Stmt) bind_u32(b &u32) { + stmt.bind(mysql.mysql_type_long, b, 0) +} + +pub fn (mut stmt Stmt) bind_i64(b &i64) { + stmt.bind(mysql.mysql_type_longlong, b, 0) +} + +pub fn (mut stmt Stmt) bind_u64(b &u64) { + stmt.bind(mysql.mysql_type_longlong, b, 0) +} + +pub fn (mut stmt Stmt) bind_f32(b &f32) { + stmt.bind(mysql.mysql_type_float, b, 0) +} + +pub fn (mut stmt Stmt) bind_f64(b &f64) { + stmt.bind(mysql.mysql_type_double, b, 0) +} + +pub fn (mut stmt Stmt) bind_text(b string) { + stmt.bind(mysql.mysql_type_string, b.str, u32(b.len)) +} + +pub fn (mut stmt Stmt) bind(typ int, buffer voidptr, buf_len u32) { + stmt.binds << C.MYSQL_BIND{ + buffer_type: typ + buffer: buffer + buffer_length: buf_len + length: 0 + } +} + +pub fn (mut stmt Stmt) bind_res(fields &C.MYSQL_FIELD, dataptr []&char, lens []u32, num_fields int) { + for i in 0 .. num_fields { + len := FieldType(unsafe { fields[i].@type }).get_len() + stmt.res << C.MYSQL_BIND{ + buffer_type: unsafe { fields[i].@type } + buffer: dataptr[i] + length: &lens[i] + buffer_length: &len + } + } +} + +pub fn (mut stmt Stmt) bind_result_buffer() ? { + res := C.mysql_stmt_bind_result(stmt.stmt, &C.MYSQL_BIND(stmt.res.data)) + if res && stmt.get_error_msg() != '' { + return stmt.error(1) + } +} + +pub fn (mut stmt Stmt) store_result() ? { + res := C.mysql_stmt_store_result(stmt.stmt) + if res != 0 && stmt.get_error_msg() != '' { + return stmt.error(res) + } +} diff --git a/v_windows/v/vlib/mysql/utils.v b/v_windows/v/vlib/mysql/utils.v new file mode 100644 index 0000000..2d63327 --- /dev/null +++ b/v_windows/v/vlib/mysql/utils.v @@ -0,0 +1,26 @@ +module mysql + +// get_error_msg - returns error message from MySQL instance. +fn get_error_msg(conn &C.MYSQL) string { + return unsafe { C.mysql_error(conn).vstring() } +} + +// get_errno - returns error number from MySQL instance. +fn get_errno(conn &C.MYSQL) int { + return C.mysql_errno(conn) +} + +// resolve_nil_str - returns an empty string if passed value is a nil pointer. +fn resolve_nil_str(ptr &byte) string { + if isnil(ptr) { + return '' + } + return unsafe { ptr.vstring() } +} + +[inline] +fn mystring(b &byte) string { + unsafe { + return b.vstring() + } +} -- cgit v1.2.3