use chrono::Local;
use json::{array, JsonValue, object};
use log::{error};
use crate::config::{Config, Params};
use crate::db::{Request};
use sqlite::{Connection, State, Type};
use crate::mode::Mode;
use crate::model::ModelTable;
use crate::thread_pool;
pub struct Sqlite {
pub config: Config,
pub params: Params,
transaction: i32,
db: Connection,
}
impl Sqlite {
pub fn connect(config: Config, mode: Mode) -> Self {
let sqlite_con = match sqlite::open(config.clone().get_dsn().as_str()) {
Ok(e) => e,
Err(e) => {
error!("{} {}", e.to_string(), config.clone().get_dsn().as_str());
sqlite::open(config.clone().get_dsn().as_str()).unwrap()
}
};
Self {
config: config.clone(),
params: Params::default(mode),
transaction: 0,
db: sqlite_con,
}
}
fn query(&mut self, sql: String) -> (bool, JsonValue) {
let mut statement = self.db.prepare(sql.clone()).unwrap();
let mut data = array![];
if self.config.debug {
println!("sql:{:?}", sql.clone());
}
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();
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 => {
let data = statement.read::<String, _>(field.as_str()).unwrap();
list[field.as_str()] = JsonValue::from(data.clone());
}
}
}
Err(e) => {
if self.config.debug {
println!("Err:{:?}", e);
}
}
}
}
data.push(list).unwrap();
}
(true, data)
}
fn execute(&mut self, sql: String) -> (bool, JsonValue) {
if self.config.debug {
println!("sql:{:?}", sql.clone());
}
match self.db.execute(sql.clone()) {
Ok(_) => {
let count = self.db.change_count();
let total_count = self.db.total_change_count();
if self.config.debug {
println!("count:{} total_count:{}", count, total_count);
}
(true, JsonValue::from(count))
}
Err(e) => {
if self.config.debug {
println!("Err:{}", e);
}
(false, JsonValue::from(e.to_string()))
}
}
}
}
impl Request for Sqlite {
fn table(&mut self, name: &str) -> &mut Sqlite {
self.params = Params::default(Mode::Sqlite);
self.params.table = format!("{}{}", self.config.prefix, name.to_string());
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;
}
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 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: 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!("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_{}", 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);
}
sql = format!("{} PRIMARY KEY(`{}`)", sql, data.primary_key);
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());
println!("索引创建:{}", state);
}
if unique != "" {
let (state, _) = self.execute(unique.clone());
println!("唯一索引创建:{}", state);
}
return true;
} else {
return false;
}
}
fn table_update(&mut self, _data: ModelTable) -> bool {
return false;
}
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, distinct: bool) -> &mut Self {
self.params.distinct = distinct.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 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());
}
_ => {
self.params.where_and.push(format!("{} {} '{}'", field, compare, value).into());
}
}
self
}
fn transaction(&mut self) -> bool {
if self.transaction > 0 {
self.transaction += 1;
return true;
}
let sql = "BEGIN";
let (state, _) = self.execute(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.execute(sql.to_string());
if state {
self.transaction -= 1;
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;
return true;
} else {
return false;
}
}
}