Adds most of the toolsHEADmaster

4 files changed, 1146 insertions, 0 deletions

diff --git a/v_windows/v/vlib/orm/README.md b/v_windows/v/vlib/orm/README.md
new file mode 100644
index 0000000..5cfa1fd
--- /dev/null
+++ b/v_windows/v/vlib/orm/README.md
@@ -0,0 +1,85 @@
+# ORM
+
+## Attributes
+
+### Structs
+
+- `[table: 'name']` sets a custom table name
+
+### Fields
+
+- `[primary]` sets the field as the primary key
+- `[unique]` sets the field as unique
+- `[unique: 'foo']` adds the field to a unique group
+- `[skip]` field will be skipped
+- `[sql: type]` sets the type which is used in sql (special type `serial`)
+- `[sql: 'name']` sets a custom column name for the field
+
+## Usage
+
+```v ignore
+struct Foo {
+    id   int    [primary; sql: serial]
+    name string [nonull]
+}
+```
+
+### Create
+
+```v ignore
+sql db {
+    create table Foo
+}
+```
+
+### Drop
+
+```v ignore
+sql db {
+    drop table Foo
+}
+```
+
+### Insert
+
+```v ignore
+var := Foo{
+    name: 'abc'
+}
+
+sql db {
+    insert var into Foo
+}
+```
+
+### Update
+
+```v ignore
+sql db {
+    update Foo set name = 'cde' where name == 'abc'
+}
+```
+
+### Delete
+```v ignore
+sql db {
+    delete from Foo where id > 10
+}
+```
+
+### Select
+```v ignore
+result := sql db {
+    select from Foo where id == 1
+}
+```
+```v ignore
+result := sql db {
+    select from Foo where id > 1 limit 5
+}
+```
+```v ignore
+result := sql db {
+    select from Foo where id > 1 order by id
+}
+```
diff --git a/v_windows/v/vlib/orm/orm.v b/v_windows/v/vlib/orm/orm.v
new file mode 100644
index 0000000..960e176
--- /dev/null
+++ b/v_windows/v/vlib/orm/orm.v
@@ -0,0 +1,473 @@
+module orm
+
+import time
+
+pub const (
+	num64    = [8, 12]
+	nums     = [5, 6, 7, 9, 10, 11, 16]
+	float    = [13, 14]
+	string   = 18
+	time     = -2
+	type_idx = {
+		'i8':     5
+		'i16':    6
+		'int':    7
+		'i64':    8
+		'byte':   9
+		'u16':    10
+		'u32':    11
+		'u64':    12
+		'f32':    13
+		'f64':    14
+		'bool':   16
+		'string': 18
+	}
+	string_max_len = 2048
+)
+
+pub type Primitive = InfixType | bool | byte | f32 | f64 | i16 | i64 | i8 | int | string |
+	time.Time | u16 | u32 | u64
+
+pub enum OperationKind {
+	neq // !=
+	eq // ==
+	gt // >
+	lt // <
+	ge // >=
+	le // <=
+}
+
+pub enum MathOperationKind {
+	add // +
+	sub // -
+	mul // *
+	div // /
+}
+
+pub enum StmtKind {
+	insert
+	update
+	delete
+}
+
+pub enum OrderType {
+	asc
+	desc
+}
+
+fn (kind OperationKind) to_str() string {
+	str := match kind {
+		.neq { '!=' }
+		.eq { '=' }
+		.gt { '>' }
+		.lt { '<' }
+		.ge { '>=' }
+		.le { '<=' }
+	}
+	return str
+}
+
+fn (kind OrderType) to_str() string {
+	return match kind {
+		.desc {
+			'DESC'
+		}
+		.asc {
+			'ASC'
+		}
+	}
+}
+
+pub struct QueryData {
+pub:
+	fields []string
+	data   []Primitive
+	types  []int
+	kinds  []OperationKind
+	is_and []bool
+}
+
+pub struct InfixType {
+pub:
+	name     string
+	operator MathOperationKind
+	right    Primitive
+}
+
+pub struct TableField {
+pub:
+	name        string
+	typ         int
+	is_time     bool
+	default_val string
+	is_arr      bool
+	attrs       []StructAttribute
+}
+
+pub struct SelectConfig {
+pub:
+	table      string
+	is_count   bool
+	has_where  bool
+	has_order  bool
+	order      string
+	order_type OrderType
+	has_limit  bool
+	primary    string = 'id' // should be set if primary is different than 'id' and 'has_limit' is false
+	has_offset bool
+	fields     []string
+	types      []int
+}
+
+pub interface Connection {
+	@select(config SelectConfig, data QueryData, where QueryData) ?[][]Primitive
+	insert(table string, data QueryData) ?
+	update(table string, data QueryData, where QueryData) ?
+	delete(table string, where QueryData) ?
+	create(table string, fields []TableField) ?
+	drop(talbe string) ?
+	last_id() Primitive
+}
+
+pub fn orm_stmt_gen(table string, para string, kind StmtKind, num bool, qm string, start_pos int, data QueryData, where QueryData) string {
+	mut str := ''
+
+	mut c := start_pos
+
+	match kind {
+		.insert {
+			mut values := []string{}
+
+			for _ in 0 .. data.fields.len {
+				// loop over the length of data.field and generate ?0, ?1 or just ? based on the $num parameter for value placeholders
+				if num {
+					values << '$qm$c'
+					c++
+				} else {
+					values << '$qm'
+				}
+			}
+
+			str += 'INSERT INTO $para$table$para ('
+			str += data.fields.map('$para$it$para').join(', ')
+			str += ') VALUES ('
+			str += values.join(', ')
+			str += ')'
+		}
+		.update {
+			str += 'UPDATE $para$table$para SET '
+			for i, field in data.fields {
+				str += '$para$field$para = '
+				if data.data.len > i {
+					d := data.data[i]
+					if d is InfixType {
+						op := match d.operator {
+							.add {
+								'+'
+							}
+							.sub {
+								'-'
+							}
+							.mul {
+								'*'
+							}
+							.div {
+								'/'
+							}
+						}
+						str += '$d.name $op $qm'
+					} else {
+						str += '$qm'
+					}
+				} else {
+					str += '$qm'
+				}
+				if num {
+					str += '$c'
+					c++
+				}
+				if i < data.fields.len - 1 {
+					str += ', '
+				}
+			}
+			str += ' WHERE '
+		}
+		.delete {
+			str += 'DELETE FROM $para$table$para WHERE '
+		}
+	}
+	if kind == .update || kind == .delete {
+		for i, field in where.fields {
+			str += '$para$field$para ${where.kinds[i].to_str()} $qm'
+			if num {
+				str += '$c'
+				c++
+			}
+			if i < where.fields.len - 1 {
+				str += ' AND '
+			}
+		}
+	}
+	str += ';'
+	return str
+}
+
+pub fn orm_select_gen(orm SelectConfig, para string, num bool, qm string, start_pos int, where QueryData) string {
+	mut str := 'SELECT '
+
+	if orm.is_count {
+		str += 'COUNT(*)'
+	} else {
+		for i, field in orm.fields {
+			str += '$para$field$para'
+			if i < orm.fields.len - 1 {
+				str += ', '
+			}
+		}
+	}
+
+	str += ' FROM $para$orm.table$para'
+
+	mut c := start_pos
+
+	if orm.has_where {
+		str += ' WHERE '
+		for i, field in where.fields {
+			str += '$para$field$para ${where.kinds[i].to_str()} $qm'
+			if num {
+				str += '$c'
+				c++
+			}
+			if i < where.fields.len - 1 {
+				if where.is_and[i] {
+					str += ' AND '
+				} else {
+					str += ' OR '
+				}
+			}
+		}
+	}
+
+	str += ' ORDER BY '
+	if orm.has_order {
+		str += '$para$orm.order$para '
+		str += orm.order_type.to_str()
+	} else {
+		str += '$para$orm.primary$para '
+		str += orm.order_type.to_str()
+	}
+
+	if orm.has_limit {
+		str += ' LIMIT ?'
+		if num {
+			str += '$c'
+			c++
+		}
+	}
+
+	if orm.has_offset {
+		str += ' OFFSET ?'
+		if num {
+			str += '$c'
+			c++
+		}
+	}
+
+	str += ';'
+	return str
+}
+
+pub fn orm_table_gen(table string, para string, defaults bool, def_unique_len int, fields []TableField, sql_from_v fn (int) ?string, alternative bool) ?string {
+	mut str := 'CREATE TABLE IF NOT EXISTS $para$table$para ('
+
+	if alternative {
+		str = 'IF NOT EXISTS (SELECT * FROM sysobjects WHERE name=$para$table$para and xtype=${para}U$para) CREATE TABLE $para$table$para ('
+	}
+
+	mut fs := []string{}
+	mut unique_fields := []string{}
+	mut unique := map[string][]string{}
+	mut primary := ''
+
+	for field in fields {
+		if field.is_arr {
+			continue
+		}
+		mut no_null := false
+		mut is_unique := false
+		mut is_skip := false
+		mut unique_len := 0
+		// mut fkey := ''
+		mut field_name := sql_field_name(field)
+		for attr in field.attrs {
+			match attr.name {
+				'primary' {
+					primary = field.name
+				}
+				'unique' {
+					if attr.arg != '' {
+						if attr.kind == .string {
+							unique[attr.arg] << field_name
+							continue
+						} else if attr.kind == .number {
+							unique_len = attr.arg.int()
+							is_unique = true
+							continue
+						}
+					}
+					is_unique = true
+				}
+				'nonull' {
+					no_null = true
+				}
+				'skip' {
+					is_skip = true
+				}
+				/*'fkey' {
+					if attr.arg != '' {
+						if attr.kind == .string {
+							fkey = attr.arg
+							continue
+						}
+					}
+				}*/
+				else {}
+			}
+		}
+		if is_skip {
+			continue
+		}
+		mut stmt := ''
+		mut ctyp := sql_from_v(sql_field_type(field)) or {
+			field_name = '${field_name}_id'
+			sql_from_v(7) ?
+		}
+		if ctyp == '' {
+			return error('Unknown type ($field.typ) for field $field.name in struct $table')
+		}
+		stmt = '$para$field_name$para $ctyp'
+		if defaults && field.default_val != '' {
+			stmt += ' DEFAULT $field.default_val'
+		}
+		if no_null {
+			stmt += ' NOT NULL'
+		}
+		if is_unique {
+			mut f := 'UNIQUE($para$field_name$para'
+			if ctyp == 'TEXT' && def_unique_len > 0 {
+				if unique_len > 0 {
+					f += '($unique_len)'
+				} else {
+					f += '($def_unique_len)'
+				}
+			}
+			f += ')'
+			unique_fields << f
+		}
+		fs << stmt
+	}
+	if primary == '' {
+		return error('A primary key is required for $table')
+	}
+	if unique.len > 0 {
+		for k, v in unique {
+			mut tmp := []string{}
+			for f in v {
+				tmp << '$para$f$para'
+			}
+			fs << '/* $k */UNIQUE(${tmp.join(', ')})'
+		}
+	}
+	fs << 'PRIMARY KEY($para$primary$para)'
+	fs << unique_fields
+	str += fs.join(', ')
+	str += ');'
+	return str
+}
+
+fn sql_field_type(field TableField) int {
+	mut typ := field.typ
+	if field.is_time {
+		return -2
+	}
+	for attr in field.attrs {
+		if attr.kind == .plain && attr.name == 'sql' && attr.arg != '' {
+			if attr.arg.to_lower() == 'serial' {
+				typ = -1
+				break
+			}
+			typ = orm.type_idx[attr.arg]
+			break
+		}
+	}
+	return typ
+}
+
+fn sql_field_name(field TableField) string {
+	mut name := field.name
+	for attr in field.attrs {
+		if attr.name == 'sql' && attr.has_arg && attr.kind == .string {
+			name = attr.arg
+			break
+		}
+	}
+	return name
+}
+
+// needed for backend functions
+
+pub fn bool_to_primitive(b bool) Primitive {
+	return Primitive(b)
+}
+
+pub fn f32_to_primitive(b f32) Primitive {
+	return Primitive(b)
+}
+
+pub fn f64_to_primitive(b f64) Primitive {
+	return Primitive(b)
+}
+
+pub fn i8_to_primitive(b i8) Primitive {
+	return Primitive(b)
+}
+
+pub fn i16_to_primitive(b i16) Primitive {
+	return Primitive(b)
+}
+
+pub fn int_to_primitive(b int) Primitive {
+	return Primitive(b)
+}
+
+pub fn i64_to_primitive(b i64) Primitive {
+	return Primitive(b)
+}
+
+pub fn byte_to_primitive(b byte) Primitive {
+	return Primitive(b)
+}
+
+pub fn u16_to_primitive(b u16) Primitive {
+	return Primitive(b)
+}
+
+pub fn u32_to_primitive(b u32) Primitive {
+	return Primitive(b)
+}
+
+pub fn u64_to_primitive(b u64) Primitive {
+	return Primitive(b)
+}
+
+pub fn string_to_primitive(b string) Primitive {
+	return Primitive(b)
+}
+
+pub fn time_to_primitive(b time.Time) Primitive {
+	return Primitive(b)
+}
+
+pub fn infix_to_primitive(b InfixType) Primitive {
+	return Primitive(b)
+}
diff --git a/v_windows/v/vlib/orm/orm_fn_test.v b/v_windows/v/vlib/orm/orm_fn_test.v
new file mode 100644
index 0000000..e79b175
--- /dev/null
+++ b/v_windows/v/vlib/orm/orm_fn_test.v
@@ -0,0 +1,272 @@
+import orm
+
+fn test_orm_stmt_gen_update() {
+	query := orm.orm_stmt_gen('Test', "'", .update, true, '?', 0, orm.QueryData{
+		fields: ['test', 'a']
+		data: []
+		types: []
+		kinds: []
+	}, orm.QueryData{
+		fields: ['id', 'name']
+		data: []
+		types: []
+		kinds: [.ge, .eq]
+	})
+	assert query == "UPDATE 'Test' SET 'test' = ?0, 'a' = ?1 WHERE 'id' >= ?2 AND 'name' = ?3;"
+}
+
+fn test_orm_stmt_gen_insert() {
+	query := orm.orm_stmt_gen('Test', "'", .insert, true, '?', 0, orm.QueryData{
+		fields: ['test', 'a']
+		data: []
+		types: []
+		kinds: []
+	}, orm.QueryData{})
+	assert query == "INSERT INTO 'Test' ('test', 'a') VALUES (?0, ?1);"
+}
+
+fn test_orm_stmt_gen_delete() {
+	query := orm.orm_stmt_gen('Test', "'", .delete, true, '?', 0, orm.QueryData{
+		fields: ['test', 'a']
+		data: []
+		types: []
+		kinds: []
+	}, orm.QueryData{
+		fields: ['id', 'name']
+		data: []
+		types: []
+		kinds: [.ge, .eq]
+	})
+	assert query == "DELETE FROM 'Test' WHERE 'id' >= ?0 AND 'name' = ?1;"
+}
+
+fn get_select_fields() []string {
+	return ['id', 'test', 'abc']
+}
+
+fn test_orm_select_gen() {
+	query := orm.orm_select_gen(orm.SelectConfig{
+		table: 'test_table'
+		fields: get_select_fields()
+	}, "'", true, '?', 0, orm.QueryData{})
+
+	assert query == "SELECT 'id', 'test', 'abc' FROM 'test_table' ORDER BY 'id' ASC;"
+}
+
+fn test_orm_select_gen_with_limit() {
+	query := orm.orm_select_gen(orm.SelectConfig{
+		table: 'test_table'
+		fields: get_select_fields()
+		has_limit: true
+	}, "'", true, '?', 0, orm.QueryData{})
+
+	assert query == "SELECT 'id', 'test', 'abc' FROM 'test_table' ORDER BY 'id' ASC LIMIT ?0;"
+}
+
+fn test_orm_select_gen_with_where() {
+	query := orm.orm_select_gen(orm.SelectConfig{
+		table: 'test_table'
+		fields: get_select_fields()
+		has_where: true
+	}, "'", true, '?', 0, orm.QueryData{
+		fields: ['abc', 'test']
+		kinds: [.eq, .gt]
+		is_and: [true]
+	})
+
+	assert query == "SELECT 'id', 'test', 'abc' FROM 'test_table' WHERE 'abc' = ?0 AND 'test' > ?1 ORDER BY 'id' ASC;"
+}
+
+fn test_orm_select_gen_with_order() {
+	query := orm.orm_select_gen(orm.SelectConfig{
+		table: 'test_table'
+		fields: get_select_fields()
+		has_order: true
+		order_type: .desc
+	}, "'", true, '?', 0, orm.QueryData{})
+
+	assert query == "SELECT 'id', 'test', 'abc' FROM 'test_table' ORDER BY '' DESC;"
+}
+
+fn test_orm_select_gen_with_offset() {
+	query := orm.orm_select_gen(orm.SelectConfig{
+		table: 'test_table'
+		fields: get_select_fields()
+		has_offset: true
+	}, "'", true, '?', 0, orm.QueryData{})
+
+	assert query == "SELECT 'id', 'test', 'abc' FROM 'test_table' ORDER BY 'id' ASC OFFSET ?0;"
+}
+
+fn test_orm_select_gen_with_all() {
+	query := orm.orm_select_gen(orm.SelectConfig{
+		table: 'test_table'
+		fields: get_select_fields()
+		has_limit: true
+		has_order: true
+		order_type: .desc
+		has_offset: true
+		has_where: true
+	}, "'", true, '?', 0, orm.QueryData{
+		fields: ['abc', 'test']
+		kinds: [.eq, .gt]
+		is_and: [true]
+	})
+
+	assert query == "SELECT 'id', 'test', 'abc' FROM 'test_table' WHERE 'abc' = ?0 AND 'test' > ?1 ORDER BY '' DESC LIMIT ?2 OFFSET ?3;"
+}
+
+fn test_orm_table_gen() {
+	query := orm.orm_table_gen('test_table', "'", true, 0, [
+		orm.TableField{
+			name: 'id'
+			typ: 7
+			default_val: '10'
+			attrs: [
+				StructAttribute{
+					name: 'primary'
+				},
+				StructAttribute{
+					name: 'sql'
+					has_arg: true
+					arg: 'serial'
+					kind: .plain
+				},
+			]
+		},
+		orm.TableField{
+			name: 'test'
+			typ: 18
+		},
+		orm.TableField{
+			name: 'abc'
+			typ: 8
+			default_val: '6754'
+		},
+	], sql_type_from_v, false) or { panic(err) }
+	assert query == "CREATE TABLE IF NOT EXISTS 'test_table' ('id' SERIAL DEFAULT 10, 'test' TEXT, 'abc' INT64 DEFAULT 6754, PRIMARY KEY('id'));"
+
+	alt_query := orm.orm_table_gen('test_table', "'", true, 0, [
+		orm.TableField{
+			name: 'id'
+			typ: 7
+			default_val: '10'
+			attrs: [
+				StructAttribute{
+					name: 'primary'
+				},
+				StructAttribute{
+					name: 'sql'
+					has_arg: true
+					arg: 'serial'
+					kind: .plain
+				},
+			]
+		},
+		orm.TableField{
+			name: 'test'
+			typ: 18
+		},
+		orm.TableField{
+			name: 'abc'
+			typ: 8
+			default_val: '6754'
+		},
+	], sql_type_from_v, true) or { panic(err) }
+	assert alt_query == "IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='test_table' and xtype='U') CREATE TABLE 'test_table' ('id' SERIAL DEFAULT 10, 'test' TEXT, 'abc' INT64 DEFAULT 6754, PRIMARY KEY('id'));"
+
+	unique_query := orm.orm_table_gen('test_table', "'", true, 0, [
+		orm.TableField{
+			name: 'id'
+			typ: 7
+			default_val: '10'
+			attrs: [
+				StructAttribute{
+					name: 'primary'
+				},
+				StructAttribute{
+					name: 'sql'
+					has_arg: true
+					arg: 'serial'
+					kind: .plain
+				},
+			]
+		},
+		orm.TableField{
+			name: 'test'
+			typ: 18
+			attrs: [
+				StructAttribute{
+					name: 'unique'
+				},
+			]
+		},
+		orm.TableField{
+			name: 'abc'
+			typ: 8
+			default_val: '6754'
+		},
+	], sql_type_from_v, false) or { panic(err) }
+	assert unique_query == "CREATE TABLE IF NOT EXISTS 'test_table' ('id' SERIAL DEFAULT 10, 'test' TEXT, 'abc' INT64 DEFAULT 6754, PRIMARY KEY('id'), UNIQUE('test'));"
+
+	mult_unique_query := orm.orm_table_gen('test_table', "'", true, 0, [
+		orm.TableField{
+			name: 'id'
+			typ: 7
+			default_val: '10'
+			attrs: [
+				StructAttribute{
+					name: 'primary'
+				},
+				StructAttribute{
+					name: 'sql'
+					has_arg: true
+					arg: 'serial'
+					kind: .plain
+				},
+			]
+		},
+		orm.TableField{
+			name: 'test'
+			typ: 18
+			attrs: [
+				StructAttribute{
+					name: 'unique'
+					has_arg: true
+					arg: 'test'
+					kind: .string
+				},
+			]
+		},
+		orm.TableField{
+			name: 'abc'
+			typ: 8
+			default_val: '6754'
+			attrs: [
+				StructAttribute{
+					name: 'unique'
+					has_arg: true
+					arg: 'test'
+					kind: .string
+				},
+			]
+		},
+	], sql_type_from_v, false) or { panic(err) }
+	assert mult_unique_query == "CREATE TABLE IF NOT EXISTS 'test_table' ('id' SERIAL DEFAULT 10, 'test' TEXT, 'abc' INT64 DEFAULT 6754, /* test */UNIQUE('test', 'abc'), PRIMARY KEY('id'));"
+}
+
+fn sql_type_from_v(typ int) ?string {
+	return if typ in orm.nums {
+		'INT'
+	} else if typ in orm.num64 {
+		'INT64'
+	} else if typ in orm.float {
+		'DOUBLE'
+	} else if typ == orm.string {
+		'TEXT'
+	} else if typ == -1 {
+		'SERIAL'
+	} else {
+		error('Unknown type $typ')
+	}
+}
diff --git a/v_windows/v/vlib/orm/orm_test.v b/v_windows/v/vlib/orm/orm_test.v
new file mode 100644
index 0000000..c5e4fe1
--- /dev/null
+++ b/v_windows/v/vlib/orm/orm_test.v
@@ -0,0 +1,316 @@
+// import os
+// import pg
+// import term
+import time
+import sqlite
+
+struct Module {
+	id           int    [primary; sql: serial]
+	name         string
+	nr_downloads int
+	user         User
+}
+
+[table: 'userlist']
+struct User {
+	id             int    [primary; sql: serial]
+	age            int
+	name           string [sql: 'username']
+	is_customer    bool
+	skipped_string string [skip]
+}
+
+struct Foo {
+	age int
+}
+
+struct TestTime {
+	id     int       [primary; sql: serial]
+	create time.Time
+}
+
+fn test_orm_sqlite() {
+	db := sqlite.connect(':memory:') or { panic(err) }
+	db.exec('drop table if exists User')
+	sql db {
+		create table User
+	}
+
+	name := 'Peter'
+
+	sam := User{
+		age: 29
+		name: 'Sam'
+	}
+
+	peter := User{
+		age: 31
+		name: 'Peter'
+	}
+
+	k := User{
+		age: 30
+		name: 'Kate'
+		is_customer: true
+	}
+
+	sql db {
+		insert sam into User
+		insert peter into User
+		insert k into User
+	}
+
+	c := sql db {
+		select count from User where id != 1
+	}
+	assert c == 2
+
+	nr_all_users := sql db {
+		select count from User
+	}
+	assert nr_all_users == 3
+	println('nr_all_users=$nr_all_users')
+	//
+	nr_users1 := sql db {
+		select count from User where id == 1
+	}
+	assert nr_users1 == 1
+	println('nr_users1=$nr_users1')
+	//
+	nr_peters := sql db {
+		select count from User where id == 2 && name == 'Peter'
+	}
+	assert nr_peters == 1
+	println('nr_peters=$nr_peters')
+	//
+	nr_peters2 := sql db {
+		select count from User where id == 2 && name == name
+	}
+	assert nr_peters2 == 1
+	nr_peters3 := sql db {
+		select count from User where name == name
+	}
+	assert nr_peters3 == 1
+	peters := sql db {
+		select from User where name == name
+	}
+	assert peters.len == 1
+	assert peters[0].name == 'Peter'
+	one_peter := sql db {
+		select from User where name == name limit 1
+	}
+	assert one_peter.name == 'Peter'
+	assert one_peter.id == 2
+	//
+	user := sql db {
+		select from User where id == 1
+	}
+	println(user)
+	assert user.name == 'Sam'
+	assert user.id == 1
+	assert user.age == 29
+	//
+	users := sql db {
+		select from User where id > 0
+	}
+	println(users)
+	assert users.len == 3
+	assert users[0].name == 'Sam'
+	assert users[1].name == 'Peter'
+	assert users[1].age == 31
+	//
+	users2 := sql db {
+		select from User where id < 0
+	}
+	println(users2)
+	assert users2.len == 0
+	//
+	users3 := sql db {
+		select from User where age == 29 || age == 31
+	}
+	println(users3)
+	assert users3.len == 2
+	assert users3[0].age == 29
+	assert users3[1].age == 31
+	//
+	missing_user := sql db {
+		select from User where id == 8777
+	}
+	println('missing_user:')
+	println(missing_user) // zero struct
+	//
+	new_user := User{
+		name: 'New user'
+		age: 30
+	}
+	sql db {
+		insert new_user into User
+	}
+
+	// db.insert<User>(user2)
+	x := sql db {
+		select from User where id == 4
+	}
+	println(x)
+	assert x.age == 30
+	assert x.id == 4
+	assert x.name == 'New user'
+	//
+	kate := sql db {
+		select from User where id == 3
+	}
+	assert kate.is_customer == true
+	//
+	customer := sql db {
+		select from User where is_customer == true limit 1
+	}
+	assert customer.is_customer == true
+	assert customer.name == 'Kate'
+	//
+	sql db {
+		update User set age = 31 where name == 'Kate'
+	}
+
+	kate2 := sql db {
+		select from User where id == 3
+	}
+	assert kate2.age == 31
+	assert kate2.name == 'Kate'
+	//
+	sql db {
+		update User set age = 32, name = 'Kate N' where name == 'Kate'
+	}
+
+	mut kate3 := sql db {
+		select from User where id == 3
+	}
+	assert kate3.age == 32
+	assert kate3.name == 'Kate N'
+	//
+	/*
+	sql db {
+		update User set age = age + 1, name = 'Kate N' where name == 'Kate'
+	}
+	kate3 = sql db {
+		select from User where id == 3
+	}
+	println(kate3)
+	assert kate3.age == 32
+	assert kate3.name == 'Kate N'
+	*/
+	new_age := 33
+	sql db {
+		update User set age = new_age, name = 'Kate N' where id == 3
+	}
+
+	kate3 = sql db {
+		select from User where id == 3
+	}
+	assert kate3.age == 33
+	assert kate3.name == 'Kate N'
+	//
+	foo := Foo{34}
+	sql db {
+		update User set age = foo.age, name = 'Kate N' where id == 3
+	}
+
+	kate3 = sql db {
+		select from User where id == 3
+	}
+	assert kate3.age == 34
+	assert kate3.name == 'Kate N'
+	//
+	no_user := sql db {
+		select from User where id == 30
+	}
+	assert no_user.name == '' // TODO optional
+	assert no_user.age == 0
+	//
+	two_users := sql db {
+		select from User limit 2
+	}
+	assert two_users.len == 2
+	assert two_users[0].id == 1
+	//
+	y := sql db {
+		select from User limit 2 offset 1
+	}
+	assert y.len == 2
+	assert y[0].id == 2
+	//
+	offset_const := 2
+	z := sql db {
+		select from User limit 2 offset offset_const
+	}
+	assert z.len == 2
+	assert z[0].id == 3
+	oldest := sql db {
+		select from User order by age desc limit 1
+	}
+	assert oldest.age == 34
+	offs := 1
+	second_oldest := sql db {
+		select from User order by age desc limit 1 offset offs
+	}
+	assert second_oldest.age == 31
+	sql db {
+		delete from User where age == 34
+	}
+
+	updated_oldest := sql db {
+		select from User order by age desc limit 1
+	}
+	assert updated_oldest.age == 31
+
+	db.exec('insert into User (name, age) values (NULL, 31)')
+	null_user := sql db {
+		select from User where id == 5
+	}
+	assert null_user.name == ''
+
+	age_test := sql db {
+		select from User where id == 1
+	}
+
+	assert age_test.age == 29
+
+	sql db {
+		update User set age = age + 1 where id == 1
+	}
+
+	mut first := sql db {
+		select from User where id == 1
+	}
+
+	assert first.age == 30
+
+	sql db {
+		update User set age = age * 2 where id == 1
+	}
+
+	first = sql db {
+		select from User where id == 1
+	}
+
+	assert first.age == 60
+
+	sql db {
+		create table TestTime
+	}
+
+	tnow := time.now()
+
+	time_test := TestTime{
+		create: tnow
+	}
+
+	sql db {
+		insert time_test into TestTime
+	}
+
+	data := sql db {
+		select from TestTime where create == tnow
+	}
+
+	assert data.len == 1
+}