use std::collections::HashMap;
use std::sync::Mutex;
use chrono::Local;
use json::{array, JsonValue, object};
use lazy_static::lazy_static;
use log::{error, info};
use mysql::Pool;
use sqlite::{Connection as Connect, State, Type};
use crate::{Mode, Params, pools, Table};
use crate::Connection;
lazy_static! {
static ref DB: Mutex<HashMap<String,Connect>> =Mutex::new(HashMap::new());
static ref TX: Mutex<HashMap<String,i32>> =Mutex::new(HashMap::new());
}
#[derive(Clone, Debug)]
pub struct Sqlite {
pub connections: HashMap<String, Connection>,
pub connection: Connection,
pub default: String,
pub params: Params,
pub transaction: i32,
}
impl Sqlite {
pub fn connect(connection: Connection, default: String) {
match sqlite::open(connection.clone().get_dsn().as_str()) {
Ok(e) => {
TX.lock().unwrap().insert(default.clone(), 0);
DB.lock().unwrap().insert(default.clone(), e);
}
Err(e) => {
error!("sqlite 启动失败: {} {}", e.to_string(), connection.clone().get_dsn().as_str());
}
};
}
fn query(&mut self, sql: String) -> (bool, JsonValue) {
let db = DB.lock().unwrap();
let db = db.get(&*self.default).unwrap().clone();
if self.connection.debug {
info!("sql:{:?}", sql.clone());
}
let mut data = array![];
match db.prepare(sql.clone()) {
Ok(mut statement) => {
while let State::Row = statement.next().unwrap() {
let mut list = object! {};
for field in statement.column_names().iter() {
match statement.column_type(field.as_str()) {
Ok(types) => {
match types.clone() {
Type::String => {
let data = statement.read::<String, _>(field.as_str()).unwrap();
match data.as_str() {
"false" => {
list[field.as_str()] = JsonValue::from(false);
}
"true" => {
list[field.as_str()] = JsonValue::from(true);
}
_ => {
list[field.as_str()] = JsonValue::from(data.clone());
}
}
}
Type::Integer => {
let data = statement.read::<i64, _>(field.as_str()).unwrap();
list[field.as_str()] = JsonValue::from(data.clone());
}
Type::Float => {
let data = statement.read::<f64, _>(field.as_str()).unwrap();
list[field.as_str()] = JsonValue::from(data.clone());
}
Type::Binary => {
let data = statement.read::<String, _>(field.as_str()).unwrap();
list[field.as_str()] = JsonValue::from(data.clone());
}
Type::Null => {
match statement.read::<String, _>(field.as_str()) {
Ok(data) => {
list[field.as_str()] = JsonValue::from(data.clone());
}
Err(_) => {
match statement.read::<f64, _>(field.as_str()) {
Ok(data) => {
if data == 0.0 {
list[field.as_str()] = JsonValue::from("");
} else {
list[field.as_str()] = JsonValue::from(data.clone());
}
}
Err(_) => {
match statement.read::<i64, _>(field.as_str()) {
Ok(data) => {
if data == 0 {
list[field.as_str()] = JsonValue::from("");
} else {
list[field.as_str()] = JsonValue::from(data.clone());
}
}
Err(e) => {
if self.connection.debug {
info!("Type:{} {:?}", field.as_str(),e);
}
}
}
}
}
}
}
}
}
}
Err(e) => {
if self.connection.debug {
info!("Err:{:?}", e);
}
}
}
}
data.push(list).unwrap();
}
return (true, data);
}
Err(_) => {
return (false, data);
}
};
}
fn execute(&mut self, sql: String) -> (bool, JsonValue) {
if self.connection.debug {
info!("sql: {}", sql.clone());
}
let db = DB.lock().unwrap();
let db = db.get(&*self.default).unwrap().clone();
match db.execute(sql.clone()) {
Ok(_) => {
let count = db.change_count();
if self.connection.debug {
info!("count:{} total_count:{}", count, db.total_change_count());
}
if sql.contains("INSERT") {
(true, JsonValue::from(count))
} else {
(true, JsonValue::from(count))
}
}
Err(e) => {
if self.connection.debug {
info!("Err:{}", e);
}
(false, JsonValue::from(e.to_string()))
}
}
}
}
impl Mode for Sqlite {
fn table(&mut self, name: &str) -> &mut Sqlite {
self.params = Params::default(self.connection.mode.as_str());
self.params.table = format!("{}{}", self.connection.prefix, name.to_string());
self
}
fn count(&mut self) -> JsonValue {
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"].clone();
} else {
return JsonValue::from(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;
}
return data[0][field].clone().into();
} else {
return JsonValue::from(0.0);
}
}
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 JsonValue::from(0.0);
}
}
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 JsonValue::from(0);
}
}
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 JsonValue::from(0);
}
}
fn select(&mut self) -> JsonValue {
let sql = self.params.select_sql();
if self.params.sql {
return JsonValue::from(sql.clone());
}
let (state, data) = self.query(sql.clone());
if state {
return data.clone();
} else {
if self.connection.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.connection.debug {
println!("{:?}", data);
}
return object! {};
}
}
fn insert(&mut self, mut data: JsonValue) -> JsonValue {
let mut fields = String::new();
let mut values = String::new();
if !self.params.autoinc && data["id"].is_empty() {
data["id"] = JsonValue::from(Local::now().timestamp_micros());
}
for (field, value) in data.entries() {
fields = format!("{},{}", fields, field);
if value.is_string() {
if value.to_string().contains('"') {
values = format!("{},'{}'", values, value);
} else if value.to_string().contains("'") {
values = format!("{},\"{}\"", values, value);
} else {
values = format!("{},'{}'", values, value);
}
} else if value.is_number() {
values = format!("{},{}", values, value);
} else if value.is_boolean() {
values = format!("{},{}", values, value);
} else {
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);
if self.params.sql {
return JsonValue::from(sql.clone());
}
let (state, ids) = self.execute(sql);
match state {
true => {
if self.params.autoinc {
let (state, ids) = self.query(format!("select max(id) as id from {}", self.params.table));
match state {
true => {
return ids[0]["id"].clone();
}
false => {
if self.connection.debug {
info!("{:?}", ids);
}
return JsonValue::from("");
}
}
}
return data["id"].clone();
}
false => {
if self.connection.debug {
info!("{:?}", ids);
}
return JsonValue::from("");
}
}
}
fn insert_all(&mut self, mut data: JsonValue) -> JsonValue {
let mut fields = String::new();
if !self.params.autoinc && 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 = pools::Pool::new(10);
let keyauto = self.params.autoinc;
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 values = "".to_string();
for (_, value) in item.entries() {
if value.is_string() {
if value.to_string().contains('"') {
values = format!("{},'{}'", values, value);
} else if value.to_string().contains("'") {
values = format!("{},\"{}\"", values, value);
} else {
values = format!("{},'{}'", values, value);
}
} else if value.is_number() {
values = format!("{},{}", values, value);
} else if value.is_boolean() {
values = format!("{},{}", values, value);
} else {
values = format!("{},\"{}\"", values, value);
}
}
values = format!("({})", values.trim_start_matches(","));
JsonValue::from(array![item["id"].clone(),values])
});
}
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);
if self.params.sql {
return JsonValue::from(sql.clone());
}
let (state, data) = self.execute(sql);
match state {
true => {
if self.params.autoinc {
let (state, ids) = self.query(format!("SELECT id FROM {} GROUP BY id ORDER BY id DESC LIMIT {} OFFSET 0", self.params.table, ids_list.len()));
match state {
true => {
let mut idlist = array![];
for item in ids.members() {
idlist.push(item["id"].clone()).unwrap();
}
return idlist;
}
false => {
if self.connection.debug {
info!("{:?}", ids);
}
return array![];
}
}
}
return JsonValue::from(ids_list);
}
false => {
if self.connection.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) -> JsonValue {
let mut values = vec![];
for (field, value) in data.entries() {
if value.is_string() {
if value.to_string().contains('"') && value.to_string().contains("'") {
values.push(format!("{}=\"{}\"", field, value));
} else if value.to_string().contains("'") {
values.push(format!("{}=\"{}\"", field, value));
} else if value.to_string().contains('"') {
values.push(format!("{}='{}'", field, value));
} else {
values.push(format!("{}='{}'", field, value));
}
} else if value.is_number() || value.is_boolean() || value.is_null() {
values.push(format!("{}={}", field, value));
} else {
values.push(format!("{}='{}'", field, value));
}
}
let values = values.join(",");
let sql = format!("UPDATE {} SET {} {};", self.params.table.clone(), values, self.params.where_sql());
if self.params.sql {
return JsonValue::from(sql.clone());
}
let (state, data) = self.execute(sql);
if state {
return data;
} else {
if self.connection.debug {
info!("{:?}", data);
}
return JsonValue::from(0);
}
}
fn delete(&mut self) -> JsonValue {
let sql = format!("DELETE FROM {} {};", self.params.table.clone(), self.params.where_sql());
if self.params.sql {
return JsonValue::from(sql.clone());
}
let (state, data) = self.execute(sql);
match state {
true => {
data
}
false => {
if self.connection.debug {
println!("delete 失败>>>{:?}", data);
}
JsonValue::from(0)
}
}
}
fn field(&mut self, name: &str) -> &mut Self {
self.params.fields[name] = name.into();
self
}
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_create(&mut self, data: Table) -> 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_{}", data.table, item);
} else {
unique_fields = format!("{},{}", unique_fields, item);
unique_name = format!("{}_{}", unique_name, item);
}
unique = format!("CREATE UNIQUE INDEX {} on {} ({})", unique_name, data.table, unique_fields);
}
let mut index = vec![];
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_{}", data.table, item);
} else {
index_fields = format!("{},{}", index_fields, item);
index_name = format!("{}_{}", index_name, item);
}
}
index.push(format!("CREATE INDEX {} on {} ({});", index_name, data.table, index_fields));
}
for (name, field) in data.fields.entries() {
let row = df_fields::field("sqlite", name, field.clone());
sql = format!("{} {},\r\n", sql, row);
}
if !data.auto {
sql = format!("{} PRIMARY KEY(`{}`)", sql, data.primary_key);
} else {
sql = sql.trim_end_matches(",\r\n").to_string();
}
let sql = format!("CREATE TABLE {} (\r\n{}\r\n);", data.table, sql);
let (state, _) = self.execute(sql);
if state {
for sql in index.iter() {
let (state, _) = self.execute(sql.clone());
info!("索引创建:{}", state);
}
if unique != "" {
let (state, _) = self.execute(unique.clone());
info!("唯一索引创建:{}", state);
}
return true;
} else {
return false;
}
}
fn table_update(&mut self, data: Table) -> bool {
let mut sql = String::new();
let table = data.table.clone();
let (_, fields_list) = self.query(format!("pragma table_info ('{}')", table));
let mut field_old = object! {};
for item in fields_list.members() {
field_old[item["name"].as_str().unwrap()] = item.clone();
}
let mut put = vec![];
let mut add = vec![];
for (name, _) in data.fields.entries() {
if !field_old[name].is_empty() {
put.push(name);
} else {
add.push(name);
}
}
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_{}", data.table, item);
} else {
unique_fields = format!("{},{}", unique_fields, item);
unique_name = format!("{}_{}", unique_name, item);
}
unique = format!("CREATE UNIQUE INDEX {} on {} ({})", unique_name, data.table, unique_fields);
}
let mut index = vec![];
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_{}", data.table, item);
} else {
index_fields = format!("{},{}", index_fields, item);
index_name = format!("{}_{}", index_name, item);
}
}
index.push(format!("CREATE INDEX {} on {} ({});", index_name, data.table, index_fields));
}
for (name, field) in data.fields.entries() {
let row = df_fields::field("sqlite", name, field.clone());
sql = format!("{} {},\r\n", sql, row);
}
if !data.auto {
sql = format!("{} PRIMARY KEY(`{}`)", sql, data.primary_key);
} else {
sql = sql.trim_end_matches(",\r\n").to_string();
}
if add.len() == 0 {
return true;
}
let sql = format!("CREATE TABLE {}_tmp (\r\n{}\r\n);", data.table, sql);
let (state, _) = self.execute(sql);
return match state {
true => {
let sql = format!("insert INTO {}_tmp ({}) select {01} from {00};", data.table, put.join(","));
let (state, _) = self.execute(sql);
match state {
true => {
let sql = format!("drop table {};", data.table);
let (state, _) = self.execute(sql);
match state {
true => {
let sql = format!("alter table {}_tmp rename to {00};", data.table);
let (state, _) = self.execute(sql);
match state {
true => {
for sql in index.iter() {
let (state, _) = self.execute(sql.clone());
info!("索引创建:{}", state);
}
if unique != "" {
let (state, _) = self.execute(unique.clone());
info!("唯一索引创建:{}", state);
}
}
false => {}
}
return state;
}
false => {}
}
return state;
}
false => {}
}
state
}
false => {
let sql = format!("drop table {}_tmp", data.table);
let (state, _) = self.execute(sql);
state
}
};
}
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 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) -> &mut Self {
self.params.distinct = true.into();
self
}
fn column(&mut self, field: &str) -> JsonValue {
self.field(field);
self.group(field);
let sql = self.params.select_sql();
if self.params.sql {
return JsonValue::from(sql.clone());
}
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 json(&mut self, field: &str) -> &mut Self {
self.params.json[field] = field.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 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());
}
"=" => {
if value.is_null() {
self.params.where_and.push(format!("{} {} {}", field, "IS", value).into());
} else {
self.params.where_and.push(format!("{} {} '{}'", field, compare, value).into());
}
}
_ => {
if value.is_null() {
self.params.where_and.push(format!("{} {} {}", field, compare, value).into());
} else {
self.params.where_and.push(format!("{} {} '{}'", field, compare, value).into());
}
}
}
self
}
fn transaction(&mut self) -> bool {
self.transaction = TX.lock().unwrap().get(&*self.default.clone()).unwrap().clone();
if self.transaction > 0 {
self.transaction += 1;
TX.lock().unwrap().insert(self.default.clone(), self.transaction);
return true;
}
let sql = "BEGIN";
let (state, _) = self.execute(sql.to_string());
if state {
self.transaction += 1;
TX.lock().unwrap().insert(self.default.clone(), self.transaction);
return true;
} else {
return false;
}
}
fn commit(&mut self) -> bool {
if self.transaction > 1 {
self.transaction -= 1;
TX.lock().unwrap().insert(self.default.clone(), self.transaction);
return true;
}
let sql = "COMMIT";
let (state, _) = self.execute(sql.to_string());
if state {
self.transaction -= 1;
TX.lock().unwrap().insert(self.default.clone(), self.transaction);
return true;
} else {
return false;
}
}
fn rollback(&mut self) -> bool {
let sql = "ROLLBACK";
let (state, _) = self.execute(sql.to_string());
if state {
self.transaction = 0;
TX.lock().unwrap().insert(self.default.clone(), self.transaction);
return true;
} else {
return false;
}
}
fn sql(&mut self, sql: String) -> JsonValue {
let (_, data) = self.query(sql.clone());
return data;
}
fn pool(&mut self) -> Pool {
todo!()
}
fn fetch_sql(&mut self) -> &mut Self {
self.params.sql = true;
self
}
fn autoinc(&mut self) -> &mut Self {
self.params.autoinc = true;
self
}
fn where_column(&mut self, field_a: &str, compare: &str, field_b: &str) -> &mut Self {
self.params.where_column = format!("`{}` {} `{}`", field_a, compare, field_b);
self
}
fn value(&mut self, field: &str) -> JsonValue {
self.params.fields = object! {};
self.params.fields[field] = field.into();
self.params.page = 1;
self.params.limit = 1;
let sql = self.params.select_sql();
if self.params.sql {
return JsonValue::from(sql.clone());
}
let (state, data) = self.query(sql.clone());
return match state {
true => {
data[0][field].clone()
}
false => {
if self.connection.debug {
println!("{:?}", data);
}
JsonValue::Null
}
};
}
fn hidden(&mut self, name: &str) -> &mut Self {
let hidden: Vec<&str> = name.split(",").collect();
let sql = format!("PRAGMA table_info({})", self.params.table);
let (_, data) = self.query(sql);
for item in data.members() {
let name = item["name"].as_str().unwrap();
if !hidden.contains(&name) {
self.params.fields[name] = name.into();
}
}
self
}
}