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/sqlite/README.md | 16 ++ v_windows/v/vlib/sqlite/orm.v | 162 ++++++++++++++++++++ v_windows/v/vlib/sqlite/sqlite.v | 236 ++++++++++++++++++++++++++++++ v_windows/v/vlib/sqlite/sqlite_orm_test.v | 70 +++++++++ v_windows/v/vlib/sqlite/sqlite_test.v | 31 ++++ v_windows/v/vlib/sqlite/stmt.v | 74 ++++++++++ 6 files changed, 589 insertions(+) create mode 100644 v_windows/v/vlib/sqlite/README.md create mode 100644 v_windows/v/vlib/sqlite/orm.v create mode 100644 v_windows/v/vlib/sqlite/sqlite.v create mode 100644 v_windows/v/vlib/sqlite/sqlite_orm_test.v create mode 100644 v_windows/v/vlib/sqlite/sqlite_test.v create mode 100644 v_windows/v/vlib/sqlite/stmt.v (limited to 'v_windows/v/vlib/sqlite') diff --git a/v_windows/v/vlib/sqlite/README.md b/v_windows/v/vlib/sqlite/README.md new file mode 100644 index 0000000..d7462c6 --- /dev/null +++ b/v_windows/v/vlib/sqlite/README.md @@ -0,0 +1,16 @@ +# Install SQLite Dependency + +**Fedora 31**: + +`sudo dnf -y install sqlite-devel` + + +**Ubuntu 20.04**: + +`sudo apt install -y libsqlite3-dev` + + +**Windows**: +- Download the source zip from [SQLite Downloads](https://sqlite.org/download.html) +- Create a new `sqlite` subfolder inside `v/thirdparty` +- Extract the zip into that folder diff --git a/v_windows/v/vlib/sqlite/orm.v b/v_windows/v/vlib/sqlite/orm.v new file mode 100644 index 0000000..f2e9310 --- /dev/null +++ b/v_windows/v/vlib/sqlite/orm.v @@ -0,0 +1,162 @@ +module sqlite + +import orm +import time + +// sql expr + +pub fn (db DB) @select(config orm.SelectConfig, data orm.QueryData, where orm.QueryData) ?[][]orm.Primitive { + query := orm.orm_select_gen(config, '`', true, '?', 1, where) + stmt := db.new_init_stmt(query) ? + mut c := 1 + sqlite_stmt_binder(stmt, where, query, mut c) ? + sqlite_stmt_binder(stmt, data, query, mut c) ? + + defer { + stmt.finalize() + } + + mut ret := [][]orm.Primitive{} + + if config.is_count { + step := stmt.step() + if step !in [sqlite_row, sqlite_ok, sqlite_done] { + return db.error_message(step, query) + } + count := stmt.sqlite_select_column(0, 8) ? + ret << [count] + return ret + } + for { + step := stmt.step() + if step == sqlite_done { + break + } + if step != sqlite_ok && step != sqlite_row { + break + } + mut row := []orm.Primitive{} + for i, typ in config.types { + primitive := stmt.sqlite_select_column(i, typ) ? + row << primitive + } + ret << row + } + return ret +} + +// sql stmt + +pub fn (db DB) insert(table string, data orm.QueryData) ? { + query := orm.orm_stmt_gen(table, '`', .insert, true, '?', 1, data, orm.QueryData{}) + sqlite_stmt_worker(db, query, data, orm.QueryData{}) ? +} + +pub fn (db DB) update(table string, data orm.QueryData, where orm.QueryData) ? { + query := orm.orm_stmt_gen(table, '`', .update, true, '?', 1, data, where) + sqlite_stmt_worker(db, query, data, where) ? +} + +pub fn (db DB) delete(table string, where orm.QueryData) ? { + query := orm.orm_stmt_gen(table, '`', .delete, true, '?', 1, orm.QueryData{}, where) + sqlite_stmt_worker(db, query, orm.QueryData{}, where) ? +} + +pub fn (db DB) last_id() orm.Primitive { + query := 'SELECT last_insert_rowid();' + id := db.q_int(query) + return orm.Primitive(id) +} + +// table +pub fn (db DB) create(table string, fields []orm.TableField) ? { + query := orm.orm_table_gen(table, '`', true, 0, fields, sqlite_type_from_v, false) or { + return err + } + sqlite_stmt_worker(db, query, orm.QueryData{}, orm.QueryData{}) ? +} + +pub fn (db DB) drop(table string) ? { + query := 'DROP TABLE `$table`;' + sqlite_stmt_worker(db, query, orm.QueryData{}, orm.QueryData{}) ? +} + +// helper + +fn sqlite_stmt_worker(db DB, query string, data orm.QueryData, where orm.QueryData) ? { + stmt := db.new_init_stmt(query) ? + mut c := 1 + sqlite_stmt_binder(stmt, data, query, mut c) ? + sqlite_stmt_binder(stmt, where, query, mut c) ? + stmt.orm_step(query) ? + stmt.finalize() +} + +fn sqlite_stmt_binder(stmt Stmt, d orm.QueryData, query string, mut c &int) ? { + for data in d.data { + err := bind(stmt, c, data) + + if err != 0 { + return stmt.db.error_message(err, query) + } + c++ + } +} + +fn bind(stmt Stmt, c &int, data orm.Primitive) int { + mut err := 0 + match data { + i8, i16, int, byte, u16, u32, bool { + err = stmt.bind_int(c, int(data)) + } + i64, u64 { + err = stmt.bind_i64(c, i64(data)) + } + f32, f64 { + err = stmt.bind_f64(c, unsafe { *(&f64(&data)) }) + } + string { + err = stmt.bind_text(c, data) + } + time.Time { + err = stmt.bind_int(c, int(data.unix)) + } + orm.InfixType { + err = bind(stmt, c, data.right) + } + } + return err +} + +fn (stmt Stmt) sqlite_select_column(idx int, typ int) ?orm.Primitive { + mut primitive := orm.Primitive(0) + + if typ in orm.nums || typ == -1 { + primitive = stmt.get_int(idx) + } else if typ in orm.num64 { + primitive = stmt.get_i64(idx) + } else if typ in orm.float { + primitive = stmt.get_f64(idx) + } else if typ == orm.string { + primitive = stmt.get_text(idx).clone() + } else if typ == orm.time { + d := stmt.get_int(idx) + primitive = time.unix(d) + } else { + return error('Unknown type $typ') + } + + return primitive +} + +fn sqlite_type_from_v(typ int) ?string { + return if typ in orm.nums || typ < 0 || typ in orm.num64 || typ == orm.time { + 'INTEGER' + } else if typ in orm.float { + 'REAL' + } else if typ == orm.string { + 'TEXT' + } else { + error('Unknown type $typ') + } +} diff --git a/v_windows/v/vlib/sqlite/sqlite.v b/v_windows/v/vlib/sqlite/sqlite.v new file mode 100644 index 0000000..4bbe40e --- /dev/null +++ b/v_windows/v/vlib/sqlite/sqlite.v @@ -0,0 +1,236 @@ +module sqlite + +$if freebsd || openbsd { + #flag -I/usr/local/include + #flag -L/usr/local/lib +} +$if windows { + #flag windows -I@VEXEROOT/thirdparty/sqlite + #flag windows -L@VEXEROOT/thirdparty/sqlite + #flag windows @VEXEROOT/thirdparty/sqlite/sqlite3.o +} $else { + #flag -lsqlite3 +} + +#include "sqlite3.h" + +const ( + sqlite_ok = 0 + sqlite_error = 1 + sqlite_row = 100 + sqlite_done = 101 +) + +struct C.sqlite3 { +} + +struct C.sqlite3_stmt { +} + +struct Stmt { + stmt &C.sqlite3_stmt + db &DB +} + +struct SQLError { + msg string + code int +} + +// +pub struct DB { +pub mut: + is_open bool +mut: + conn &C.sqlite3 +} + +pub fn (db DB) str() string { + return 'sqlite.DB{ conn: ' + ptr_str(db.conn) + ' }' +} + +pub struct Row { +pub mut: + vals []string +} + +// +fn C.sqlite3_open(&char, &&C.sqlite3) int + +fn C.sqlite3_close(&C.sqlite3) int + +fn C.sqlite3_last_insert_rowid(&C.sqlite3) i64 + +// +fn C.sqlite3_prepare_v2(&C.sqlite3, &char, int, &&C.sqlite3_stmt, &&char) int + +fn C.sqlite3_step(&C.sqlite3_stmt) int + +fn C.sqlite3_finalize(&C.sqlite3_stmt) int + +// +fn C.sqlite3_column_name(&C.sqlite3_stmt, int) &char + +fn C.sqlite3_column_text(&C.sqlite3_stmt, int) &byte + +fn C.sqlite3_column_int(&C.sqlite3_stmt, int) int + +fn C.sqlite3_column_int64(&C.sqlite3_stmt, int) i64 + +fn C.sqlite3_column_double(&C.sqlite3_stmt, int) f64 + +fn C.sqlite3_column_count(&C.sqlite3_stmt) int + +// +fn C.sqlite3_errstr(int) &char + +fn C.sqlite3_errmsg(&C.sqlite3) &char + +fn C.sqlite3_free(voidptr) + +// connect Opens the connection with a database. +pub fn connect(path string) ?DB { + db := &C.sqlite3(0) + code := C.sqlite3_open(&char(path.str), &db) + if code != 0 { + return IError(&SQLError{ + msg: unsafe { cstring_to_vstring(&char(C.sqlite3_errstr(code))) } + code: code + }) + } + return DB{ + conn: db + is_open: true + } +} + +// close Closes the DB. +// TODO: For all functions, determine whether the connection is +// closed first, and determine what to do if it is +pub fn (mut db DB) close() ?bool { + code := C.sqlite3_close(db.conn) + if code == 0 { + db.is_open = false + } else { + return IError(&SQLError{ + msg: unsafe { cstring_to_vstring(&char(C.sqlite3_errstr(code))) } + code: code + }) + } + return true // successfully closed +} + +// Only for V ORM +fn get_int_from_stmt(stmt &C.sqlite3_stmt) int { + x := C.sqlite3_step(stmt) + if x != C.SQLITE_OK && x != C.SQLITE_DONE { + C.puts(C.sqlite3_errstr(x)) + } + + res := C.sqlite3_column_int(stmt, 0) + C.sqlite3_finalize(stmt) + return res +} + +// Returns last insert rowid +// https://www.sqlite.org/c3ref/last_insert_rowid.html +pub fn (db DB) last_insert_rowid() i64 { + return C.sqlite3_last_insert_rowid(db.conn) +} + +// Returns a single cell with value int. +pub fn (db DB) q_int(query string) int { + stmt := &C.sqlite3_stmt(0) + C.sqlite3_prepare_v2(db.conn, &char(query.str), query.len, &stmt, 0) + C.sqlite3_step(stmt) + + res := C.sqlite3_column_int(stmt, 0) + C.sqlite3_finalize(stmt) + return res +} + +// Returns a single cell with value string. +pub fn (db DB) q_string(query string) string { + stmt := &C.sqlite3_stmt(0) + defer { + C.sqlite3_finalize(stmt) + } + C.sqlite3_prepare_v2(db.conn, &char(query.str), query.len, &stmt, 0) + C.sqlite3_step(stmt) + + val := unsafe { &byte(C.sqlite3_column_text(stmt, 0)) } + return if val != &byte(0) { unsafe { tos_clone(val) } } else { '' } +} + +// Execute the query on db, return an array of all the results, alongside any result code. +// Result codes: https://www.sqlite.org/rescode.html +pub fn (db DB) exec(query string) ([]Row, int) { + stmt := &C.sqlite3_stmt(0) + C.sqlite3_prepare_v2(db.conn, &char(query.str), query.len, &stmt, 0) + nr_cols := C.sqlite3_column_count(stmt) + mut res := 0 + mut rows := []Row{} + for { + res = C.sqlite3_step(stmt) + // Result Code SQLITE_ROW; Another row is available + if res != 100 { + // C.puts(C.sqlite3_errstr(res)) + break + } + mut row := Row{} + for i in 0 .. nr_cols { + val := unsafe { &byte(C.sqlite3_column_text(stmt, i)) } + if val == &byte(0) { + row.vals << '' + } else { + row.vals << unsafe { tos_clone(val) } + } + } + rows << row + } + C.sqlite3_finalize(stmt) + return rows, res +} + +// Execute a query, handle error code +// Return the first row from the resulting table +pub fn (db DB) exec_one(query string) ?Row { + rows, code := db.exec(query) + if rows.len == 0 { + return IError(&SQLError{ + msg: 'No rows' + code: code + }) + } else if code != 101 { + return IError(&SQLError{ + msg: unsafe { cstring_to_vstring(&char(C.sqlite3_errstr(code))) } + code: code + }) + } + return rows[0] +} + +pub fn (db DB) error_message(code int, query string) IError { + msg := unsafe { cstring_to_vstring(&char(C.sqlite3_errmsg(db.conn))) } + return IError(&SQLError{ + msg: '$msg ($code) ($query)' + code: code + }) +} + +// In case you don't expect any result, but still want an error code +// e.g. INSERT INTO ... VALUES (...) +pub fn (db DB) exec_none(query string) int { + _, code := db.exec(query) + return code +} + +/* +TODO +pub fn (db DB) exec_param(query string, param string) []Row { +} +*/ + +pub fn (db DB) create_table(table_name string, columns []string) { + db.exec('create table if not exists $table_name (' + columns.join(',\n') + ')') +} diff --git a/v_windows/v/vlib/sqlite/sqlite_orm_test.v b/v_windows/v/vlib/sqlite/sqlite_orm_test.v new file mode 100644 index 0000000..efbd9fe --- /dev/null +++ b/v_windows/v/vlib/sqlite/sqlite_orm_test.v @@ -0,0 +1,70 @@ +import orm +import sqlite + +fn test_sqlite_orm() { + sdb := sqlite.connect(':memory:') or { panic(err) } + db := orm.Connection(sdb) + 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: 8 + }, + ]) or { panic(err) } + + db.insert('Test', orm.QueryData{ + fields: ['name', 'age'] + data: [orm.string_to_primitive('Louis'), orm.i64_to_primitive(100)] + }) 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(100)] + 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 == 100 + } +} diff --git a/v_windows/v/vlib/sqlite/sqlite_test.v b/v_windows/v/vlib/sqlite/sqlite_test.v new file mode 100644 index 0000000..f1e9db3 --- /dev/null +++ b/v_windows/v/vlib/sqlite/sqlite_test.v @@ -0,0 +1,31 @@ +import sqlite + +fn test_sqlite() { + $if !linux { + return + } + mut db := sqlite.connect(':memory:') or { panic(err) } + assert db.is_open + db.exec('drop table if exists users') + db.exec("create table users (id integer primary key, name text default '');") + db.exec("insert into users (name) values ('Sam')") + assert db.last_insert_rowid() == 1 + db.exec("insert into users (name) values ('Peter')") + assert db.last_insert_rowid() == 2 + db.exec("insert into users (name) values ('Kate')") + assert db.last_insert_rowid() == 3 + nr_users := db.q_int('select count(*) from users') + assert nr_users == 3 + name := db.q_string('select name from users where id = 1') + assert name == 'Sam' + users, mut code := db.exec('select * from users') + assert users.len == 3 + assert code == 101 + code = db.exec_none('vacuum') + assert code == 101 + user := db.exec_one('select * from users where id = 3') or { panic(err) } + println(user) + assert user.vals.len == 2 + db.close() or { panic(err) } + assert !db.is_open +} diff --git a/v_windows/v/vlib/sqlite/stmt.v b/v_windows/v/vlib/sqlite/stmt.v new file mode 100644 index 0000000..da07e82 --- /dev/null +++ b/v_windows/v/vlib/sqlite/stmt.v @@ -0,0 +1,74 @@ +module sqlite + +fn C.sqlite3_bind_double(&C.sqlite3_stmt, int, f64) int +fn C.sqlite3_bind_int(&C.sqlite3_stmt, int, int) int +fn C.sqlite3_bind_int64(&C.sqlite3_stmt, int, i64) int +fn C.sqlite3_bind_text(&C.sqlite3_stmt, int, &char, int, voidptr) int + +// Only for V ORM +fn (db DB) init_stmt(query string) (&C.sqlite3_stmt, int) { + // println('init_stmt("$query")') + stmt := &C.sqlite3_stmt(0) + err := C.sqlite3_prepare_v2(db.conn, &char(query.str), query.len, &stmt, 0) + return stmt, err +} + +fn (db DB) new_init_stmt(query string) ?Stmt { + stmt, err := db.init_stmt(query) + if err != sqlite_ok { + return db.error_message(err, query) + } + return Stmt{stmt, unsafe { &db }} +} + +fn (stmt Stmt) bind_int(idx int, v int) int { + return C.sqlite3_bind_int(stmt.stmt, idx, v) +} + +fn (stmt Stmt) bind_i64(idx int, v i64) int { + return C.sqlite3_bind_int64(stmt.stmt, idx, v) +} + +fn (stmt Stmt) bind_f64(idx int, v f64) int { + return C.sqlite3_bind_double(stmt.stmt, idx, v) +} + +fn (stmt Stmt) bind_text(idx int, s string) int { + return C.sqlite3_bind_text(stmt.stmt, idx, voidptr(s.str), s.len, 0) +} + +fn (stmt Stmt) get_int(idx int) int { + return C.sqlite3_column_int(stmt.stmt, idx) +} + +fn (stmt Stmt) get_i64(idx int) i64 { + return C.sqlite3_column_int64(stmt.stmt, idx) +} + +fn (stmt Stmt) get_f64(idx int) f64 { + return C.sqlite3_column_double(stmt.stmt, idx) +} + +fn (stmt Stmt) get_text(idx int) string { + b := &char(C.sqlite3_column_text(stmt.stmt, idx)) + return unsafe { b.vstring() } +} + +fn (stmt Stmt) get_count() int { + return C.sqlite3_column_count(stmt.stmt) +} + +fn (stmt Stmt) step() int { + return C.sqlite3_step(stmt.stmt) +} + +fn (stmt Stmt) orm_step(query string) ? { + res := stmt.step() + if res != sqlite_ok && res != sqlite_done && res != sqlite_row { + return stmt.db.error_message(res, query) + } +} + +fn (stmt Stmt) finalize() { + C.sqlite3_finalize(stmt.stmt) +} -- cgit v1.2.3