use std::ops::Index;
use chrono::Local;
use json::{array, JsonValue, object};
use mysql::{Pool, PooledConn};
use mysql::consts::ColumnType;
use mysql::prelude::{Queryable};
use mysql::Value::NULL;
use crate::config::{Config, Params};
use crate::db::{Request};
use crate::mode::Mode;
use crate::model::ModelTable;
use crate::thread_pool;
pub struct Mysql {
pub config: Config,
pub params: Params,
pub transaction: i32,
db: PooledConn,
}
impl Mysql {
pub fn connect(config: Config, mode: Mode) -> Self {
let data = Pool::new(config.clone().get_dsn().as_str()).unwrap();
let conn = data.get_conn().unwrap();
Self {
config: config.clone(),
params: Params::default(mode),
transaction: 0,
db: conn,
}
}
fn query(&mut self, sql: String) -> (bool, JsonValue) {
match self.db.query_iter(sql.clone()) {
Ok(e) => {
let mut list = array![];
let mut index = 0;
e.for_each(|row| {
let r = row.unwrap();
let mut data = object! {};
for item in r.columns().iter() {
let field = item.name_str();
let field = field.to_string();
let field = field.as_str();
data[field] = match item.column_type() {
ColumnType::MYSQL_TYPE_TINY => {
let data = r.get::<bool, _>(field.clone()).unwrap();
JsonValue::from(data)
}
ColumnType::MYSQL_TYPE_FLOAT => {
let data = r.get::<f64, _>(field.clone()).unwrap();
JsonValue::from(data)
}
ColumnType::MYSQL_TYPE_LONGLONG => {
let data = r.get::<i64, _>(field.clone()).unwrap();
JsonValue::from(data)
}
ColumnType::MYSQL_TYPE_BLOB => {
let data = r.index(field.clone()).clone();
if data == NULL {
JsonValue::from("".to_string())
} else {
let data = r.get::<String, _>(field.clone()).unwrap();
JsonValue::from(data)
}
}
ColumnType::MYSQL_TYPE_VAR_STRING | ColumnType::MYSQL_TYPE_STRING => {
let data = r.get::<String, _>(field.clone()).unwrap();
JsonValue::from(data)
}
_ => {
println!("未知 {} {:?}", field.clone(), item.column_type());
JsonValue::from("".to_string())
}
};
}
list.push(data).unwrap();
index += 1;
});
return (true, list);
}
Err(e) => {
println!("{}", e.to_string());
return (false, JsonValue::from(e.to_string()));
}
}
}
fn execute(&mut self, sql: String) -> (bool, JsonValue) {
match self.db.exec_iter(sql.clone(), {}) {
Ok(e) => {
return (true, JsonValue::from(e.affected_rows()));
}
Err(e) => {
return (false, JsonValue::from(e.to_string()));
}
}
}
}
impl Request for Mysql {
fn table_create(&mut self, data: ModelTable) -> bool {
let mut sql = String::new();
let mut unique_fields = String::new();
let mut unique_name = String::new();
let mut unique = String::new();
for item in data.unique.iter() {
if unique_fields == "" {
unique_fields = format!("`{}`", item);
unique_name = format!("unique_{}", item);
} else {
unique_fields = format!("{},`{}`", unique_fields, item);
unique_name = format!("{}_{}", unique_name, item);
}
unique = format!("UNIQUE KEY `{}` ({})", unique_name, unique_fields);
}
let mut index = String::new();
for row in data.index.iter() {
let mut index_fields = String::new();
let mut index_name = String::new();
for item in row.iter() {
if index_fields == "" {
index_fields = format!("`{}`", item);
index_name = format!("index_{}", item);
} else {
index_fields = format!("{},`{}`", index_fields, item);
index_name = format!("{}_{}", index_name, item);
}
}
if index == "" {
index = format!("INDEX `{}` ({})", index_name, index_fields);
} else {
index = format!("{},\r\nINDEX `{}` ({})", index, index_name, index_fields);
}
}
for (name, field) in data.fields.entries() {
let row = df_fields::field("mysql", name, field.clone());
sql = format!("{} {},\r\n", sql, row);
}
sql = format!("{} PRIMARY KEY(`{}`)", sql, data.primary_key);
if unique != "" {
sql = format!("{},\r\n{}", sql, unique);
}
if index != "" {
sql = format!("{},\r\n{}", sql, index);
}
let collate = format!("{}_bin", self.config.charset);
let sql = format!("CREATE TABLE IF NOT EXISTS {} (\r\n{}\r\n) ENGINE = InnoDB CHARSET = '{}' COLLATE '{}' comment '{}';", data.table, sql, self.config.charset, collate, data.title);
let (state, data) = self.execute(sql);
if state {
println!("{}", data);
return false;
} else {
return false;
}
}
fn table_update(&mut self, _data: ModelTable) -> bool {
todo!()
}
fn table_info(&mut self, table: &str) -> JsonValue {
let sql = format!("PRAGMA table_info({})", table);
let (state, data) = self.query(sql);
if state {
return data;
} else {
return object! {};
}
}
fn table_is_exist(&mut self, name: &str) -> bool {
let sql = format!("SELECT count(*) as count FROM sqlite_master WHERE type='table' AND name='{}'", name);
let (state, data) = self.query(sql);
if state {
if data[0]["count"].as_i64().unwrap() > 0 {
return true;
}
return false;
} else {
return false;
}
}
fn table(&mut self, name: &str) -> &mut Mysql {
self.params = Params::default(Mode::Mysql);
self.params.table = format!("{}{}", self.config.prefix, name.to_string());
self
}
fn order(&mut self, field: &str, by: bool) -> &mut Self {
self.params.order[field] = {
if by {
"DESC"
} else {
"ASC"
}
}.into();
self
}
fn group(&mut self, field: &str) -> &mut Self {
self.params.group[field] = field.into();
self
}
fn distinct(&mut self, distinct: bool) -> &mut Self {
self.params.distinct = distinct.into();
self
}
fn json(&mut self, field: &str) -> &mut Self {
self.params.json[field] = field.into();
self
}
fn column(&mut self, field: &str) -> JsonValue {
self.field(field);
self.group(field);
let sql = self.params.select_sql();
let (state, data) = self.query(sql);
if state {
let mut list = array![];
for item in data.members() {
list.push(item[field].clone()).unwrap()
}
return list;
} else {
return array![];
}
}
fn where_and(&mut self, field: &str, compare: &str, value: JsonValue) -> &mut Self {
match compare {
"between" => {
self.params.where_and.push(format!("{} between {} AND {}", field, value[0], value[1]).into());
}
"notin" => {
let mut text = String::new();
for item in value.members() {
text = format!("{},'{}'", text, item);
}
text = text.trim_start_matches(",").into();
self.params.where_and.push(format!("{} not in ({})", field, text).into());
}
"in" => {
let mut text = String::new();
if value.is_array() {
for item in value.members() {
text = format!("{},'{}'", text, item);
}
} else {
let value = value.as_str().unwrap();
let value: Vec<&str> = value.split(",").collect();
for item in value.iter() {
text = format!("{},'{}'", text, item);
}
}
text = text.trim_start_matches(",").into();
self.params.where_and.push(format!("{} {} ({})", field, compare, text).into());
}
_ => {
self.params.where_and.push(format!("{} {} '{}'", field, compare, value).into());
}
}
self
}
fn where_or(&mut self, field: &str, compare: &str, value: JsonValue) -> &mut Self {
match compare {
"between" => {
self.params.where_or.push(format!("{} between {} AND {}", field, value[0], value[1]).into());
}
"notin" => {
let mut text = String::new();
for item in value.members() {
text = format!("{},'{}'", text, item);
}
text = text.trim_start_matches(",").into();
self.params.where_or.push(format!("{} not in ({})", field, text).into());
}
"in" => {
let mut text = String::new();
if value.is_array() {
for item in value.members() {
text = format!("{},'{}'", text, item);
}
} else {
let value = value.as_str().unwrap();
let value: Vec<&str> = value.split(",").collect();
for item in value.iter() {
text = format!("{},'{}'", text, item);
}
}
text = text.trim_start_matches(",").into();
self.params.where_or.push(format!("{} {} ({})", field, compare, text).into());
}
_ => {
self.params.where_or.push(format!("{} {} '{}'", field, compare, value).into());
}
}
self
}
fn count(&mut self) -> i64 {
self.params.fields["count"] = format!("count(*) as count").into();
let sql = self.params.select_sql();
let (state, data) = self.query(sql);
if state {
return data[0]["count"].as_i64().unwrap();
} else {
return 0;
}
}
fn max(&mut self, field: &str) -> JsonValue {
self.params.fields[field] = format!("max({00}) as {00}", field).into();
let sql = self.params.select_sql();
let (state, data) = self.query(sql);
if state {
if data.len() > 1 {
return data.clone();
}
return data[0][field].clone().into();
} else {
return array![];
}
}
fn min(&mut self, field: &str) -> JsonValue {
self.params.fields[field] = format!("min({00}) as {00}", field).into();
let sql = self.params.select_sql();
let (state, data) = self.query(sql);
if state {
if data.len() > 1 {
return data;
}
return data[0][field].clone().into();
} else {
return array![];
}
}
fn sum(&mut self, field: &str) -> JsonValue {
self.params.fields[field] = format!("sum({00}) as {00}", field).into();
let sql = self.params.select_sql();
let (state, data) = self.query(sql);
if state {
if data.len() > 1 {
return data;
}
return data[0][field].clone().into();
} else {
return array![];
}
}
fn avg(&mut self, field: &str) -> JsonValue {
self.params.fields[field] = format!("avg({00}) as {00}", field).into();
let sql = self.params.select_sql();
let (state, data) = self.query(sql);
if state {
if data.len() > 1 {
return data;
}
return data[0][field].clone().into();
} else {
return array![];
}
}
fn select(&mut self) -> JsonValue {
let sql = self.params.select_sql();
let (state, data) = self.query(sql.clone());
if state {
return data.clone();
} else {
if self.config.debug {
println!("{:?}", data);
}
return array![];
}
}
fn find(&mut self) -> JsonValue {
self.params.page = 1;
self.params.limit = 1;
let sql = self.params.select_sql();
let (state, data) = self.query(sql.clone());
if state {
return data[0].clone();
} else {
if self.config.debug {
println!("{:#?}", data);
}
return object! {};
}
}
fn insert(&mut self, mut data: JsonValue) -> i32 {
let mut fields = String::new();
let mut values = String::new();
if !self.config.keyauto && data["id"].is_empty() {
data["id"] = JsonValue::from(Local::now().timestamp_nanos())
}
for (field, value) in data.entries() {
fields = format!("{},{}", fields, field);
values = format!("{},\"{}\"", values, value);
}
fields = fields.trim_start_matches(",").parse().unwrap();
values = values.trim_start_matches(",").parse().unwrap();
let sql = format!("INSERT INTO {} ({}) VALUES ({});", self.params.table, fields, values);
let (state, data) = self.execute(sql);
if state {
return data.as_i32().unwrap();
} else {
if self.config.debug {
println!("{:?}", data);
}
return 0;
}
}
fn insert_all(&mut self, mut data: JsonValue) -> JsonValue {
let mut fields = String::new();
if !self.config.keyauto && data[0]["id"].is_empty() {
data[0]["id"] = format!("{:X}", Local::now().timestamp_nanos()).into();
}
for (field, _) in data[0].entries() {
fields = format!("{},{}", fields, field);
}
fields = fields.trim_start_matches(",").parse().unwrap();
let mut p = thread_pool::Pool::new(10);
let keyauto = self.config.keyauto;
for list in data.members() {
let mut item = list.clone();
p.execute(move |pcindex| {
if !keyauto && item["id"].is_empty() {
let id = format!("{:X}{:X}", Local::now().timestamp_nanos(), pcindex);
item["id"] = id.into();
}
let mut row = "".to_string();
for (_, v) in item.entries() {
if row == "" {
row = format!("\"{}\"", v);
} else {
row = format!("{},\"{}\"", row, v);
}
}
row = format!("({})", row);
JsonValue::from(array![item["id"].clone(),row])
});
}
let (ids_list, mut values) = p.insert_all();
values = values.trim_start_matches(",").parse().unwrap();
let sql = format!("INSERT INTO {} ({}) VALUES {};", self.params.table, fields, values);
let (state, data) = self.execute(sql);
if state {
return JsonValue::from(ids_list);
} else {
if self.config.debug {
println!("{:?}", data);
}
return array![];
}
}
fn page(&mut self, page: i32, limit: i32) -> &mut Self {
self.params.page = page;
self.params.limit = limit;
self
}
fn update(&mut self, data: JsonValue) -> i32 {
let mut values = String::new();
for (field, value) in data.entries() {
values = format!("{},{}=\"{}\"", values, field, value);
}
values = values.trim_start_matches(",").parse().unwrap();
let sql = format!("UPDATE {} SET {} {};", self.params.table.clone(), values, self.params.where_sql());
let (state, data) = self.execute(sql);
if state {
return data.as_i32().unwrap();
} else {
if self.config.debug {
println!("{:?}", data);
}
return 0;
}
}
fn delete(&mut self) -> i32 {
let sql = format!("DELETE FROM {} {};", self.params.table.clone(), self.params.where_sql());
let (state, data) = self.execute(sql);
match state {
true => {
data.as_i32().unwrap()
}
false => {
if self.config.debug {
println!("delete 失败>>>{:?}", data);
}
0
}
}
}
fn field(&mut self, name: &str) -> &mut Self {
self.params.fields[name] = name.into();
self
}
fn transaction(&mut self) -> bool {
if self.transaction > 0 {
self.transaction += 1;
return true;
}
let sql = "BEGIN";
let (state, _) = self.query(sql.to_string());
if state {
self.transaction += 1;
return true;
} else {
return false;
}
}
fn commit(&mut self) -> bool {
if self.transaction > 1 {
self.transaction -= 1;
return true;
}
let sql = "COMMIT";
let (state, _) = self.query(sql.to_string());
if state {
self.transaction -= 1;
return true;
} else {
return false;
}
}
fn rollback(&mut self) -> bool {
let sql = "ROLLBACK";
let (state, _) = self.query(sql.to_string());
if state {
self.transaction = 0;
return true;
} else {
return false;
}
}
}