aboutsummaryrefslogtreecommitdiff
path: root/v_windows/v/vlib/orm
diff options
context:
space:
mode:
Diffstat (limited to 'v_windows/v/vlib/orm')
-rw-r--r--v_windows/v/vlib/orm/README.md85
-rw-r--r--v_windows/v/vlib/orm/orm.v473
-rw-r--r--v_windows/v/vlib/orm/orm_fn_test.v272
-rw-r--r--v_windows/v/vlib/orm/orm_test.v316
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
+}