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

rustbasic_core/
database.rs
