rustbasic_core/
database.rs

1use crate::Config;
2#[cfg(feature = "mysql")]
3use crate::colored::Colorize;
4use crate::sql::{self, AnyPool};
5use serde_json::Value;
6use serde::de::DeserializeOwned;
7
8pub async fn connect(cfg: &Config) -> AnyPool {
9    let db_url = if cfg.db_connection == "mysql" {
10        format!(
11            "mysql://{}:{}@{}:{}/{}",
12            cfg.db_username, cfg.db_password, cfg.db_host, cfg.db_port, cfg.db_database
13        )
14    } else {
15        format!("sqlite:database/{}.sqlite?mode=rwc", cfg.db_database)
16    };
17
18    sql::any::install_default_drivers();
19
20    let db_url_ref: &str = &db_url;
21    match AnyPool::connect(db_url_ref).await {
22        Ok(pool) => pool,
23        Err(e) => {
24            let err_msg = e.to_string();
25            #[cfg(feature = "mysql")]
26            if (err_msg.contains("1049") || err_msg.contains("Unknown database")) && cfg.db_connection == "mysql" {
27                println!("{}", "⚠️  Database tidak ditemukan. Mencoba membuat database baru...".yellow());
28                
29                let root_url = format!(
30                    "mysql://{}:{}@{}:{}",
31                    cfg.db_username, cfg.db_password, cfg.db_host, cfg.db_port
32                );
33                
34                if let Ok(pool) = sql::MySqlPool::connect(&root_url).await {
35                    let create_query = format!("CREATE DATABASE IF NOT EXISTS `{}`", cfg.db_database);
36                    if sql::query(&create_query).execute(&pool).await.is_ok() {
37                        println!("✅ Database '{}' berhasil dibuat.", cfg.db_database.green());
38                        return AnyPool::connect(&db_url).await.expect("Gagal terhubung setelah membuat database");
39                    }
40                }
41            }
42            let _ = err_msg; // suppress unused warning when mysql feature is disabled
43            panic!("Gagal terhubung ke database: {:?}", e);
44        }
45    }
46}
47
48// -------------------------------------------------------------
49// 📑 Query Builder (DB)
50// -------------------------------------------------------------
51
52#[derive(Clone)]
53pub enum WhereClause {
54    Simple { column: String, operator: String, value: Value },
55    Raw { sql: String, binds: Vec<Value> },
56}
57
58#[derive(Clone)]
59pub struct OrderClause {
60    column: String,
61    direction: String,
62}
63
64#[derive(Clone)]
65pub struct QueryBuilder<'a> {
66    pool: &'a AnyPool,
67    table: String,
68    wheres: Vec<WhereClause>,
69    orders: Vec<OrderClause>,
70    limit: Option<usize>,
71}
72
73pub struct DB;
74
75impl DB {
76    pub fn table<'a>(pool: &'a AnyPool, name: &str) -> QueryBuilder<'a> {
77        QueryBuilder::new(pool, name)
78    }
79}
80
81impl<'a> QueryBuilder<'a> {
82    pub fn new(pool: &'a AnyPool, table: &str) -> Self {
83        Self {
84            pool,
85            table: table.to_string(),
86            wheres: Vec::new(),
87            orders: Vec::new(),
88            limit: None,
89        }
90    }
91
92    pub fn where_(mut self, column: &str, value: impl serde::Serialize) -> Self {
93        let val = serde_json::to_value(value).unwrap_or(Value::Null);
94        self.wheres.push(WhereClause::Simple {
95            column: column.to_string(),
96            operator: "=".to_string(),
97            value: val,
98        });
99        self
100    }
101
102    pub fn where_op(mut self, column: &str, operator: &str, value: impl serde::Serialize) -> Self {
103        let val = serde_json::to_value(value).unwrap_or(Value::Null);
104        self.wheres.push(WhereClause::Simple {
105            column: column.to_string(),
106            operator: operator.to_string(),
107            value: val,
108        });
109        self
110    }
111
112    pub fn where_raw(mut self, sql: &str, binds: Vec<Value>) -> Self {
113        self.wheres.push(WhereClause::Raw {
114            sql: sql.to_string(),
115            binds,
116        });
117        self
118    }
119
120    pub fn where_in(self, column: &str, values: Vec<impl serde::Serialize>) -> Self {
121        if values.is_empty() {
122            return self.where_raw("1 = 0", vec![]);
123        }
124        let placeholders: Vec<&str> = (0..values.len()).map(|_| "?").collect();
125        let sql = format!("`{}` IN ({})", column, placeholders.join(", "));
126        let binds: Vec<Value> = values.iter()
127            .map(|v| serde_json::to_value(v).unwrap_or(Value::Null))
128            .collect();
129        self.where_raw(&sql, binds)
130    }
131
132    pub fn pool(&self) -> &'a AnyPool {
133        self.pool
134    }
135
136
137    pub fn order_by(mut self, column: &str, direction: &str) -> Self {
138        self.orders.push(OrderClause {
139            column: column.to_string(),
140            direction: direction.to_string(),
141        });
142        self
143    }
144
145    pub fn limit(mut self, limit: usize) -> Self {
146        self.limit = Some(limit);
147        self
148    }
149
150    fn to_select_sql(&self) -> (String, Vec<Value>) {
151        let mut sql = format!("SELECT * FROM `{}`", self.table);
152        let mut binds = Vec::new();
153
154        if !self.wheres.is_empty() {
155            sql.push_str(" WHERE ");
156            let mut parts = Vec::new();
157            for w in &self.wheres {
158                match w {
159                    WhereClause::Simple { column, operator, value } => {
160                        parts.push(format!("`{}` {} ?", column, operator));
161                        binds.push(value.clone());
162                    }
163                    WhereClause::Raw { sql: raw_sql, binds: raw_binds } => {
164                        parts.push(raw_sql.clone());
165                        binds.extend(raw_binds.clone());
166                    }
167                }
168            }
169            sql.push_str(&parts.join(" AND "));
170        }
171
172        if !self.orders.is_empty() {
173            sql.push_str(" ORDER BY ");
174            let parts: Vec<String> = self.orders.iter()
175                .map(|o| format!("`{}` {}", o.column, o.direction))
176                .collect();
177            sql.push_str(&parts.join(", "));
178        }
179
180        if let Some(limit) = self.limit {
181            sql.push_str(&format!(" LIMIT {}", limit));
182        }
183
184        (sql, binds)
185    }
186
187    pub async fn first<T: DeserializeOwned>(&self) -> Result<Option<T>, sql::Error> {
188        let mut builder = self.clone();
189        builder.limit = Some(1);
190        let (sql, binds) = builder.to_select_sql();
191
192        let mut query = sql::query(&sql);
193        for b in &binds {
194            query = bind_query_json(query, b);
195        }
196
197        let row_opt = query.fetch_optional(self.pool).await?;
198        if let Some(row) = row_opt {
199            let val = row_to_json_value(&row);
200            let parsed = serde_json::from_value::<T>(val)
201                .map_err(|e| sql::Error::Protocol(format!("Deserialization error: {}", e)))?;
202            Ok(Some(parsed))
203        } else {
204            Ok(None)
205        }
206    }
207
208    pub async fn get<T: DeserializeOwned>(&self) -> Result<Vec<T>, sql::Error> {
209        let (sql, binds) = self.to_select_sql();
210
211        let mut query = sql::query(&sql);
212        for b in &binds {
213            query = bind_query_json(query, b);
214        }
215
216        let rows = query.fetch_all(self.pool).await?;
217        let mut result = Vec::new();
218        for row in rows {
219            let val = row_to_json_value(&row);
220            let parsed = serde_json::from_value::<T>(val)
221                .map_err(|e| sql::Error::Protocol(format!("Deserialization error: {}", e)))?;
222            result.push(parsed);
223        }
224        Ok(result)
225    }
226
227    pub async fn count(&self) -> Result<i64, sql::Error> {
228        let mut sql = format!("SELECT COUNT(*) FROM `{}`", self.table);
229        let mut binds = Vec::new();
230
231        if !self.wheres.is_empty() {
232            sql.push_str(" WHERE ");
233            let mut parts = Vec::new();
234            for w in &self.wheres {
235                match w {
236                    WhereClause::Simple { column, operator, value } => {
237                        parts.push(format!("`{}` {} ?", column, operator));
238                        binds.push(value.clone());
239                    }
240                    WhereClause::Raw { sql: raw_sql, binds: raw_binds } => {
241                        parts.push(raw_sql.clone());
242                        binds.extend(raw_binds.clone());
243                    }
244                }
245            }
246            sql.push_str(&parts.join(" AND "));
247        }
248
249        let mut query = sql::query(&sql);
250        for b in &binds {
251            query = bind_query_json(query, b);
252        }
253
254        let row = query.fetch_one(self.pool).await?;
255        let count_val: i64 = row.try_get(0).unwrap_or(0);
256        Ok(count_val)
257    }
258
259    pub async fn insert(&self, data: Value) -> Result<(), sql::Error> {
260        let obj = data.as_object().ok_or_else(|| {
261            sql::Error::Protocol("Data insert harus berupa JSON object".into())
262        })?;
263
264        let mut columns = Vec::new();
265        let mut placeholders = Vec::new();
266        let mut binds = Vec::new();
267
268        for (col, val) in obj {
269            columns.push(format!("`{}`", col));
270            placeholders.push("?");
271            binds.push(val.clone());
272        }
273
274        let sql = format!(
275            "INSERT INTO `{}` ({}) VALUES ({})",
276            self.table,
277            columns.join(", "),
278            placeholders.join(", ")
279        );
280
281        let mut query = sql::query(&sql);
282        for b in &binds {
283            query = bind_query_json(query, b);
284        }
285
286        query.execute(self.pool).await?;
287        Ok(())
288    }
289
290    pub async fn insert_get_id(&self, data: Value) -> Result<i64, sql::Error> {
291        let obj = data.as_object().ok_or_else(|| {
292            sql::Error::Protocol("Data insert harus berupa JSON object".into())
293        })?;
294
295        let mut columns = Vec::new();
296        let mut placeholders = Vec::new();
297        let mut binds = Vec::new();
298
299        for (col, val) in obj {
300            columns.push(format!("`{}`", col));
301            placeholders.push("?");
302            binds.push(val.clone());
303        }
304
305        let sql = format!(
306            "INSERT INTO `{}` ({}) VALUES ({})",
307            self.table,
308            columns.join(", "),
309            placeholders.join(", ")
310        );
311
312        let mut conn = self.pool.acquire().await?;
313
314        let mut query = sql::query(&sql);
315        for b in &binds {
316            query = bind_query_json(query, b);
317        }
318
319        let result = query.execute(&mut *conn).await?;
320        if let Some(id) = result.last_insert_id() {
321            if id != 0 {
322                return Ok(id);
323            }
324        }
325        
326        // Fallback for SQLite when using SQLx Any driver
327        if let Ok(row) = sql::query("SELECT last_insert_rowid()").fetch_one(&mut *conn).await {
328            let id: i64 = row.try_get(0).unwrap_or(0);
329            if id != 0 {
330                return Ok(id);
331            }
332        }
333        
334        // Fallback for MySQL when using SQLx Any driver
335        if let Ok(row) = sql::query("SELECT LAST_INSERT_ID()").fetch_one(&mut *conn).await {
336            let id: i64 = row.try_get(0).unwrap_or(0);
337            if id != 0 {
338                return Ok(id);
339            }
340        }
341
342        Ok(0)
343    }
344
345    pub async fn update(&self, data: Value) -> Result<u64, sql::Error> {
346        let obj = data.as_object().ok_or_else(|| {
347            sql::Error::Protocol("Data update harus berupa JSON object".into())
348        })?;
349
350        let mut sets = Vec::new();
351        let mut binds = Vec::new();
352
353        for (col, val) in obj {
354            sets.push(format!("`{}` = ?", col));
355            binds.push(val.clone());
356        }
357
358        let mut sql = format!("UPDATE `{}` SET {}", self.table, sets.join(", "));
359
360        if !self.wheres.is_empty() {
361            sql.push_str(" WHERE ");
362            let mut parts = Vec::new();
363            for w in &self.wheres {
364                match w {
365                    WhereClause::Simple { column, operator, value } => {
366                        parts.push(format!("`{}` {} ?", column, operator));
367                        binds.push(value.clone());
368                    }
369                    WhereClause::Raw { sql: raw_sql, binds: raw_binds } => {
370                        parts.push(raw_sql.clone());
371                        binds.extend(raw_binds.clone());
372                    }
373                }
374            }
375            sql.push_str(&parts.join(" AND "));
376        }
377
378        let mut query = sql::query(&sql);
379        for b in &binds {
380            query = bind_query_json(query, b);
381        }
382
383        let result = query.execute(self.pool).await?;
384        Ok(result.rows_affected())
385    }
386
387    pub async fn delete(&self) -> Result<u64, sql::Error> {
388        let mut sql = format!("DELETE FROM `{}`", self.table);
389        let mut binds = Vec::new();
390
391        if !self.wheres.is_empty() {
392            sql.push_str(" WHERE ");
393            let mut parts = Vec::new();
394            for w in &self.wheres {
395                match w {
396                    WhereClause::Simple { column, operator, value } => {
397                        parts.push(format!("`{}` {} ?", column, operator));
398                        binds.push(value.clone());
399                    }
400                    WhereClause::Raw { sql: raw_sql, binds: raw_binds } => {
401                        parts.push(raw_sql.clone());
402                        binds.extend(raw_binds.clone());
403                    }
404                }
405            }
406            sql.push_str(&parts.join(" AND "));
407        }
408
409        let mut query = sql::query(&sql);
410        for b in &binds {
411            query = bind_query_json(query, b);
412        }
413
414        let result = query.execute(self.pool).await?;
415        Ok(result.rows_affected())
416    }
417}
418
419// -------------------------------------------------------------
420// Helper Value Binding & JSON mapping
421// -------------------------------------------------------------
422
423fn bind_query_json<'q>(
424    query: sql::query::Query<'q, sql::Any, sql::any::AnyArguments<'q>>,
425    val: &Value,
426) -> sql::query::Query<'q, sql::Any, sql::any::AnyArguments<'q>> {
427    match val {
428        Value::Null => query.bind(None::<String>),
429        Value::Bool(b) => query.bind(*b),
430        Value::Number(n) => {
431            if let Some(i) = n.as_i64() {
432                query.bind(i)
433            } else if let Some(f) = n.as_f64() {
434                query.bind(f)
435            } else {
436                query.bind(0.0f64)
437            }
438        }
439        Value::String(s) => query.bind(s.clone()),
440        _ => query.bind(val.to_string()),
441    }
442}
443
444pub fn row_to_json_value(row: &sql::any::AnyRow) -> Value {
445    let mut map = serde_json::Map::new();
446    for i in 0..row.len() {
447        let col = row.column(i);
448        let name = col.name();
449        let val = get_json_value(row, i);
450        map.insert(name.to_string(), val);
451    }
452    Value::Object(map)
453}
454
455fn get_json_value(row: &sql::any::AnyRow, index: usize) -> Value {
456    let type_name = row.column(index).type_info().name();
457    if type_name == "NULL" {
458        return Value::Null;
459    }
460
461    let type_name_upper = type_name.to_uppercase();
462    if type_name_upper.contains("DATETIME") || type_name_upper.contains("TIMESTAMP") || type_name_upper.contains("DATE") || type_name_upper.contains("TIME") {
463        if let Ok(Some(s)) = row.try_get::<Option<String>, _>(index) {
464            return Value::String(s);
465        }
466        if let Ok(Some(bytes)) = row.try_get::<Option<Vec<u8>>, _>(index) {
467            if let Ok(s) = String::from_utf8(bytes) {
468                return Value::String(s);
469            }
470        }
471        // If SQLx Any driver fails to decode, return Null instead of throwing/panicking
472        return Value::Null;
473    }
474
475    // IMPORTANT: Integer harus dicek SEBELUM bool.
476    // Di MySQL, SQLx Any driver bisa decode kolom INT sebagai bool (1 → true),
477    // yang menyebabkan id=1 terbaca sebagai true dan gagal deserialisasi ke i32.
478    if let Ok(Some(i)) = row.try_get::<Option<i64>, _>(index) {
479        return Value::Number(serde_json::Number::from(i));
480    }
481
482    if let Ok(Some(f)) = row.try_get::<Option<f64>, _>(index) {
483        if let Some(num) = serde_json::Number::from_f64(f) {
484            return Value::Number(num);
485        }
486    }
487
488    if let Ok(Some(b)) = row.try_get::<Option<bool>, _>(index) {
489        return Value::Bool(b);
490    }
491
492    if let Ok(Some(s)) = row.try_get::<Option<String>, _>(index) {
493        return Value::String(s);
494    }
495
496    if let Ok(Some(bytes)) = row.try_get::<Option<Vec<u8>>, _>(index) {
497        if let Ok(s) = String::from_utf8(bytes) {
498            return Value::String(s);
499        }
500    }
501
502    Value::Null
503}
rustbasic_core/database.rs

rustbasic_core/
database.rs
