VeriLib

Code:

module vsql

import strings

// generate stmt to sql string
pub fn (mut db DB) gen_sql() string {
	mut s := strings.new_builder(200)
	stmt := db.stmt
	match stmt.typ {
		.select_ {
			s.write_string('select ')
			if stmt.is_distinct {
				s.write_string('distinct ')
			}
			for i, f in stmt.aggregate_fn {
				if f.is_distinct {
					s.write_string('distinct ')
				}
				s.write_string('${f.name}(')
				s.write_string('$f.column_name)')
				s.write_string(' ')
				if f.column_alias != '' {
					s.write_string('as ')
					s.write_string('$f.column_alias,')
					if i == stmt.aggregate_fn.len - 1 {
						s.go_back(1)
						s.write_string(' ')
					}
				}
			}
			if stmt.columns.len == 0 && stmt.aggregate_fn.len == 0 {
				s.write_string('* ')
			} else {
				for i, column in stmt.columns {
					s.write_string('$column.name')
					if column.alias != '' {
						s.write_string(' as ')
						s.write_string('$column.alias,')
					} else {
						s.write_string(',')
					}
					if i == stmt.columns.len - 1 {
						s.go_back(1)
						s.write_string(' ')
					}
				}
			}
			s.write_string('from ')
			s.write_string('$stmt.table_name ')
			if stmt.table_alias != '' {
				s.write_string('as $stmt.table_alias ')
			}
			// where statement
			db.write_where(stmt.where, mut &s)
			// join statement
			if stmt.join_raw != '' {
				s.write_string('$stmt.join_raw ')
			} else {
				if stmt.join.len > 0 {
					for j in stmt.join {
						s.write_string('$j.typ ')
						s.write_string('$j.table_name ')
						if j.table_alias != '' {
							s.write_string('as $j.table_alias ')
						}
						if j.join_condition != '' { // cross join will be ''
							s.write_string('on $j.join_condition ')
						}
					}
				}
			}
			// offset
			if stmt.offset > 0 {
				s.write_string('offset $stmt.offset ')
			}
			// limit
			if stmt.limit > 0 {
				s.write_string('limit $stmt.limit ')
			}
			// order by statement
			if stmt.order_by_raw != '' {
				s.write_string('order by ')
				s.write_string('$stmt.order_by_raw ')
			} else if stmt.order_by.len > 0 {
				s.write_string('order by ')
				for o in stmt.order_by {
					s.write_string('$o.column $o.order,')
				}
			}
			// group by statement
			if stmt.group_by_raw != '' {
				s.write_string('group by ')
				s.write_string('$stmt.group_by_raw ')
			} else if stmt.group_by.len > 0 {
				s.write_string('group by ')
				for col in stmt.group_by {
					s.write_string('$col,')
				}
				s.go_back(1)
				s.write_string(' ')
			}
			// having
			if stmt.having != '' {
				s.write_string('having $stmt.having ')
			}
			// union statement
			if stmt.union_stmts.len > 0 {
				for us in stmt.union_stmts {
					s.write_string('$stmt.union_type ')
					s.write_string('$us ')
				}
			}
			s.go_back(1)
		}
		.insert {
			s.write_string('insert into ')
			s.write_string('$stmt.table_name ')
			// write data
			s.write_string('(')
			mut keys := []string{}
			mut vals := []string{}
			for key, val in stmt.data {
				keys << key
				vals << val
			}
			for key in keys {
				s.write_string('$key,')
			}
			s.go_back(1)
			s.write_string(')')
			s.write_string(' values ')
			s.write_string('(')
			for len, val in vals {
				s.write_string("'$val'")
				if len < vals.len - 1 {
					s.write_string(',')
				}
			}
			s.write_string(')')
			// write returning
			if stmt.returning.len != 0 {
				s.write_string(' returning ')
				for r in stmt.returning {
					s.write_string('$r,')
				}
				s.go_back(1)
			}
		}
		.update {
			s.write_string('update ')
			s.write_string('$stmt.table_name ')
			s.write_string('set ')
			for key, val in stmt.data {
				s.write_string("$key='$val',")
			}
			s.go_back(1)
			s.write_string(' ')
			// where statement
			db.write_where(stmt.where, mut &s)
			// write returning
			if stmt.returning.len != 0 {
				s.write_string('returning ')
				for r in stmt.returning {
					s.write_string('$r,')
				}
				s.go_back(1)
			}
		}
		.delete {
			s.write_string('delete from ')
			s.write_string('$stmt.table_name ')
			// where statement
			db.write_where(stmt.where, mut &s)
		}
		.create_database {
			s.write_string('create database $stmt.db_name')
		}
		.create_table {}
		.alter_table {}
		.rename_table {
			s.write_string('alter table $stmt.table_name rename to $stmt.new_table_name')
		}
		.drop_table {
			s.write_string('drop table $stmt.table_name')
		}
		.truncate_table {
			s.write_string('truncate table $stmt.table_name')
		}
	}
	return s.str()
}

// write where clause for select,update,delete
pub fn (mut db DB) write_where(where []Where, mut s strings.Builder) {
	// where statement
	if where.len > 0 {
		s.write_string('where')
		mut operator := ''
		for pos, w in where {
			// if where is the second where clause,operator is and
			if pos >= 1 && w.operator == '' {
				operator = 'and'
			} else {
				operator = w.operator
			}
			match w.typ {
				'where' {
					s.write_string('$operator ($w.condition) ')
				}
				'where_in' {
					mut range_str := ''
					for i, r in w.range {
						if i < w.range.len - 1 {
							range_str += '$r,'
						} else {
							range_str += '$r'
						}
					}
					s.write_string('$operator ($w.column_name in ($range_str)) ')
				}
				'where_null' {
					s.write_string('$operator ($w.column_name is null) ')
				}
				'where_between' {
					s.write_string('$operator ($w.column_name between ${w.range[0]} and ${w.range[1]}) ')
				}
				'where_exists' {
					s.write_string('$operator exists ($w.exist_stmt) ')
				}
				'where_raw' {
					s.write_string(' $w.condition ')
				}
				else {
					panic('unknown where type')
				}
			}
		}
	}
}

// generate create table stmt to sql string
pub fn (t &Table) gen_table_sql() string {
	mut s := strings.new_builder(200)
	s.write_string('create table $t.name (')
	if t.columns.len == 0 {
		s.write_string(');')
		return s.str()
	}
	s.writeln('')
	for column in t.columns {
		s.write_string('$column.name ')
		s.write_string('$column.typ ')
		if column.default_value != '' {
			s.write_string("default '$column.default_value' ")
		}
		if column.is_increment {
			s.write_string('serial ')
		}
		if column.is_not_null {
			s.write_string('not null ')
		}
		if column.is_primary {
			s.write_string('primary key ')
		}
		if column.is_unique {
			s.write_string('unique ')
		}
		if column.index != '' {
			s.write_string('index $column.index ')
		}
		// if column.reference != '' {
		// s.write_string('references \'$column.reference\' ')
		// }
		if column.is_first {
		}
		if column.after != '' {
		}
		if column.collate != '' {
		}
		if column.check != '' {
			s.write_string('check ($column.check) ')
		}
		s.go_back(1)
		s.writeln(',')
	}
	if t.primarys.len == 0 && t.uniques.len == 0 && t.checks.len == 0 {
		s.go_back(2)
	}
	//
	// table constraint
	if t.primarys.len > 0 || t.uniques.len > 0 || t.indexs.len > 0 || t.checks.len > 0 {
		s.writeln('')
	}
	// primary key
	if t.primarys.len > 0 {
		s.write_string('primary key (')
		for column in t.primarys {
			s.write_string('$column,')
		}
		s.go_back(1)
		s.writeln('),')
	}
	// unique
	if t.uniques.len > 0 {
		s.write_string('unique (')
		for column in t.uniques {
			s.write_string('$column,')
		}
		s.go_back(1)
		s.writeln('),')
	}
	if t.indexs.len > 0 {
		// TODO
	}
	// check
	if t.checks.len > 0 {
		for c in t.checks {
			s.writeln('check ($c),')
		}
		s.go_back(2)
	}
	s.writeln('')
	s.write_string(');')
	return s.str()
}

xxxxxxxxxx
 
module vsql
​
import strings
​
// generate stmt to sql string
pub fn (mut db DB) gen_sql() string {
    mut s := strings.new_builder(200)
    stmt := db.stmt
    match stmt.typ {
        .select_ {
            s.write_string('select ')
            if stmt.is_distinct {
                s.write_string('distinct ')
            }
            for i, f in stmt.aggregate_fn {
                if f.is_distinct {
                    s.write_string('distinct ')
                }
                s.write_string('${f.name}(')
                s.write_string('$f.column_name)')
                s.write_string(' ')
                if f.column_alias != '' {
                    s.write_string('as ')
                    s.write_string('$f.column_alias,')
                    if i == stmt.aggregate_fn.len - 1 {
                        s.go_back(1)
                        s.write_string(' ')
                    }
                }
            }
            if stmt.columns.len == 0 && stmt.aggregate_fn.len == 0 {
                s.write_string('* ')
            } else {
                for i, column in stmt.columns {
                    s.write_string('$column.name')
                    if column.alias != '' {
                        s.write_string(' as ')
                        s.write_string('$column.alias,')
                    } else {
                        s.write_string(',')
                    }
                    if i == stmt.columns.len - 1 {
                        s.go_back(1)
                        s.write_string(' ')
                    }
                }
            }
            s.write_string('from ')
            s.write_string('$stmt.table_name ')
            if stmt.table_alias != '' {
                s.write_string('as $stmt.table_alias ')
            }
            // where statement
            db.write_where(stmt.where, mut &s)
            // join statement
            if stmt.join_raw != '' {
                s.write_string('$stmt.join_raw ')
            } else {
                if stmt.join.len > 0 {
                    for j in stmt.join {
                        s.write_string('$j.typ ')
                        s.write_string('$j.table_name ')
                        if j.table_alias != '' {
                            s.write_string('as $j.table_alias ')
                        }
                        if j.join_condition != '' { // cross join will be ''
                            s.write_string('on $j.join_condition ')
                        }
                    }
                }
            }
            // offset
            if stmt.offset > 0 {
                s.write_string('offset $stmt.offset ')
            }
            // limit
            if stmt.limit > 0 {
                s.write_string('limit $stmt.limit ')
            }
            // order by statement
            if stmt.order_by_raw != '' {
                s.write_string('order by ')
                s.write_string('$stmt.order_by_raw ')
            } else if stmt.order_by.len > 0 {
                s.write_string('order by ')
                for o in stmt.order_by {
                    s.write_string('$o.column $o.order,')
                }
            }
            // group by statement
            if stmt.group_by_raw != '' {
                s.write_string('group by ')
                s.write_string('$stmt.group_by_raw ')
            } else if stmt.group_by.len > 0 {
                s.write_string('group by ')
                for col in stmt.group_by {
                    s.write_string('$col,')
                }
                s.go_back(1)
                s.write_string(' ')
            }
            // having
            if stmt.having != '' {
                s.write_string('having $stmt.having ')
            }
            // union statement
            if stmt.union_stmts.len > 0 {
                for us in stmt.union_stmts {
                    s.write_string('$stmt.union_type ')
                    s.write_string('$us ')
                }
            }
            s.go_back(1)
        }
        .insert {
            s.write_string('insert into ')
            s.write_string('$stmt.table_name ')
            // write data
            s.write_string('(')
            mut keys := []string{}
            mut vals := []string{}
            for key, val in stmt.data {
                keys << key
                vals << val
            }
            for key in keys {
                s.write_string('$key,')
            }
            s.go_back(1)
            s.write_string(')')
            s.write_string(' values ')
            s.write_string('(')
            for len, val in vals {
                s.write_string("'$val'")
                if len < vals.len - 1 {
                    s.write_string(',')
                }
            }
            s.write_string(')')
            // write returning
            if stmt.returning.len != 0 {
                s.write_string(' returning ')
                for r in stmt.returning {
                    s.write_string('$r,')
                }
                s.go_back(1)
            }
        }
        .update {
            s.write_string('update ')
            s.write_string('$stmt.table_name ')
            s.write_string('set ')
            for key, val in stmt.data {
                s.write_string("$key='$val',")
            }
            s.go_back(1)
            s.write_string(' ')
            // where statement
            db.write_where(stmt.where, mut &s)
            // write returning
            if stmt.returning.len != 0 {
                s.write_string('returning ')
                for r in stmt.returning {
                    s.write_string('$r,')
                }
                s.go_back(1)
            }
        }
        .delete {
            s.write_string('delete from ')
            s.write_string('$stmt.table_name ')
            // where statement
            db.write_where(stmt.where, mut &s)
        }
        .create_database {
            s.write_string('create database $stmt.db_name')
        }
        .create_table {}
        .alter_table {}
        .rename_table {
            s.write_string('alter table $stmt.table_name rename to $stmt.new_table_name')
        }
        .drop_table {
            s.write_string('drop table $stmt.table_name')
        }
        .truncate_table {
            s.write_string('truncate table $stmt.table_name')
        }
    }
    return s.str()
}
​
// write where clause for select,update,delete
pub fn (mut db DB) write_where(where []Where, mut s strings.Builder) {
    // where statement
    if where.len > 0 {
        s.write_string('where')
        mut operator := ''
        for pos, w in where {
            // if where is the second where clause,operator is and
            if pos >= 1 && w.operator == '' {
                operator = 'and'
            } else {
                operator = w.operator
            }
            match w.typ {
                'where' {
                    s.write_string('$operator ($w.condition) ')
                }
                'where_in' {
                    mut range_str := ''
                    for i, r in w.range {
                        if i < w.range.len - 1 {
                            range_str += '$r,'
                        } else {
                            range_str += '$r'
                        }
                    }
                    s.write_string('$operator ($w.column_name in ($range_str)) ')
                }
                'where_null' {
                    s.write_string('$operator ($w.column_name is null) ')
                }
                'where_between' {
                    s.write_string('$operator ($w.column_name between ${w.range[0]} and ${w.range[1]}) ')
                }
                'where_exists' {
                    s.write_string('$operator exists ($w.exist_stmt) ')
                }
                'where_raw' {
                    s.write_string(' $w.condition ')
                }
                else {
                    panic('unknown where type')
                }
            }
        }
    }
}
​
// generate create table stmt to sql string
pub fn (t &Table) gen_table_sql() string {
    mut s := strings.new_builder(200)
    s.write_string('create table $t.name (')
    if t.columns.len == 0 {
        s.write_string(');')
        return s.str()
    }
    s.writeln('')
    for column in t.columns {
        s.write_string('$column.name ')
        s.write_string('$column.typ ')
        if column.default_value != '' {
            s.write_string("default '$column.default_value' ")
        }
        if column.is_increment {
            s.write_string('serial ')
        }
        if column.is_not_null {
            s.write_string('not null ')
        }
        if column.is_primary {
            s.write_string('primary key ')
        }
        if column.is_unique {
            s.write_string('unique ')
        }
        if column.index != '' {
            s.write_string('index $column.index ')
        }
        // if column.reference != '' {
        // s.write_string('references \'$column.reference\' ')
        // }
        if column.is_first {
        }
        if column.after != '' {
        }
        if column.collate != '' {
        }
        if column.check != '' {
            s.write_string('check ($column.check) ')
        }
        s.go_back(1)
        s.writeln(',')
    }
    if t.primarys.len == 0 && t.uniques.len == 0 && t.checks.len == 0 {
        s.go_back(2)
    }
    //
    // table constraint
    if t.primarys.len > 0 || t.uniques.len > 0 || t.indexs.len > 0 || t.checks.len > 0 {
        s.writeln('')
    }
    // primary key
    if t.primarys.len > 0 {
        s.write_string('primary key (')
        for column in t.primarys {
            s.write_string('$column,')
        }
        s.go_back(1)
        s.writeln('),')
    }
    // unique
    if t.uniques.len > 0 {
        s.write_string('unique (')
        for column in t.uniques {
            s.write_string('$column,')
        }
        s.go_back(1)
        s.writeln('),')
    }
    if t.indexs.len > 0 {
        // TODO
    }
    // check
    if t.checks.len > 0 {
        for c in t.checks {
            s.writeln('check ($c),')
        }
        s.go_back(2)
    }
    s.writeln('')
    s.write_string(');')
    return s.str()
}

View Code

Dependency Graph: Preview

File Explorer:

View Code

Delete Confirmation

Confirmation Required

Confirmation Required

Confirmation Required

Confirmation Required

Dependency Graph: Preview

File Explorer: