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            if id != 0 {
324                return Ok(id);
325            }
326        }
327        
328        // Fallback for SQLite when using SQLx Any driver
329        if let Ok(row) = sql::query("SELECT last_insert_rowid()").fetch_one(&mut *conn).await {
330            let id: i64 = row.try_get(0).unwrap_or(0);
331            if id != 0 {
332                return Ok(id);
333            }
334        }
335        
336        // Fallback for MySQL when using SQLx Any driver
337        if let Ok(row) = sql::query("SELECT LAST_INSERT_ID()").fetch_one(&mut *conn).await {
338            let id: i64 = row.try_get(0).unwrap_or(0);
339            if id != 0 {
340                return Ok(id);
341            }
342        }
343
344        Ok(0)
345    }
346
347    pub async fn update(&self, data: Value) -> Result<u64, sql::Error> {
348        let obj = data.as_object().ok_or_else(|| {
349            sql::Error::Protocol("Data update harus berupa JSON object".into())
350        })?;
351
352        let mut sets = Vec::new();
353        let mut binds = Vec::new();
354
355        for (col, val) in obj {
356            sets.push(format!("`{}` = ?", col));
357            binds.push(val.clone());
358        }
359
360        let mut sql = format!("UPDATE `{}` SET {}", self.table, sets.join(", "));
361
362        if !self.wheres.is_empty() {
363            sql.push_str(" WHERE ");
364            let mut parts = Vec::new();
365            for w in &self.wheres {
366                match w {
367                    WhereClause::Simple { column, operator, value } => {
368                        parts.push(format!("`{}` {} ?", column, operator));
369                        binds.push(value.clone());
370                    }
371                    WhereClause::Raw { sql: raw_sql, binds: raw_binds } => {
372                        parts.push(raw_sql.clone());
373                        binds.extend(raw_binds.clone());
374                    }
375                }
376            }
377            sql.push_str(&parts.join(" AND "));
378        }
379
380        let mut query = sql::query(&sql);
381        for b in &binds {
382            query = bind_query_json(query, b);
383        }
384
385        let result = query.execute(self.pool).await?;
386        Ok(result.rows_affected())
387    }
388
389    pub async fn delete(&self) -> Result<u64, sql::Error> {
390        let mut sql = format!("DELETE FROM `{}`", self.table);
391        let mut binds = Vec::new();
392
393        if !self.wheres.is_empty() {
394            sql.push_str(" WHERE ");
395            let mut parts = Vec::new();
396            for w in &self.wheres {
397                match w {
398                    WhereClause::Simple { column, operator, value } => {
399                        parts.push(format!("`{}` {} ?", column, operator));
400                        binds.push(value.clone());
401                    }
402                    WhereClause::Raw { sql: raw_sql, binds: raw_binds } => {
403                        parts.push(raw_sql.clone());
404                        binds.extend(raw_binds.clone());
405                    }
406                }
407            }
408            sql.push_str(&parts.join(" AND "));
409        }
410
411        let mut query = sql::query(&sql);
412        for b in &binds {
413            query = bind_query_json(query, b);
414        }
415
416        let result = query.execute(self.pool).await?;
417        Ok(result.rows_affected())
418    }
419}
420
421// -------------------------------------------------------------
422// Helper Value Binding & JSON mapping
423// -------------------------------------------------------------
424
425fn bind_query_json<'q>(
426    query: sql::query::Query<'q, sql::Any, sql::any::AnyArguments<'q>>,
427    val: &Value,
428) -> sql::query::Query<'q, sql::Any, sql::any::AnyArguments<'q>> {
429    match val {
430        Value::Null => query.bind(None::<String>),
431        Value::Bool(b) => query.bind(*b),
432        Value::Number(n) => {
433            if let Some(i) = n.as_i64() {
434                query.bind(i)
435            } else if let Some(f) = n.as_f64() {
436                query.bind(f)
437            } else {
438                query.bind(0.0f64)
439            }
440        }
441        Value::String(s) => query.bind(s.clone()),
442        _ => query.bind(val.to_string()),
443    }
444}
445
446pub fn row_to_json_value(row: &sql::any::AnyRow) -> Value {
447    let mut map = serde_json::Map::new();
448    for i in 0..row.len() {
449        let col = row.column(i);
450        let name = col.name();
451        let val = get_json_value(row, i);
452        map.insert(name.to_string(), val);
453    }
454    Value::Object(map)
455}
456
457fn get_json_value(row: &sql::any::AnyRow, index: usize) -> Value {
458    let type_name = row.column(index).type_info().name();
459    if type_name == "NULL" {
460        return Value::Null;
461    }
462
463    let type_name_upper = type_name.to_uppercase();
464    if type_name_upper.contains("DATETIME") || type_name_upper.contains("TIMESTAMP") || type_name_upper.contains("DATE") || type_name_upper.contains("TIME") {
465        if let Ok(Some(s)) = row.try_get::<Option<String>, _>(index) {
466            return Value::String(s);
467        }
468        if let Ok(Some(bytes)) = row.try_get::<Option<Vec<u8>>, _>(index) {
469            if let Ok(s) = String::from_utf8(bytes) {
470                return Value::String(s);
471            }
472        }
473        // If SQLx Any driver fails to decode, return Null instead of throwing/panicking
474        return Value::Null;
475    }
476
477    // IMPORTANT: Integer harus dicek SEBELUM bool.
478    // Di MySQL, SQLx Any driver bisa decode kolom INT sebagai bool (1 → true),
479    // yang menyebabkan id=1 terbaca sebagai true dan gagal deserialisasi ke i32.
480    if let Ok(Some(i)) = row.try_get::<Option<i64>, _>(index) {
481        return Value::Number(serde_json::Number::from(i));
482    }
483
484    if let Ok(Some(f)) = row.try_get::<Option<f64>, _>(index) {
485        if let Some(num) = serde_json::Number::from_f64(f) {
486            return Value::Number(num);
487        }
488    }
489
490    if let Ok(Some(b)) = row.try_get::<Option<bool>, _>(index) {
491        return Value::Bool(b);
492    }
493
494    if let Ok(Some(s)) = row.try_get::<Option<String>, _>(index) {
495        return Value::String(s);
496    }
497
498    if let Ok(Some(bytes)) = row.try_get::<Option<Vec<u8>>, _>(index) {
499        if let Ok(s) = String::from_utf8(bytes) {
500            return Value::String(s);
501        }
502    }
503
504    Value::Null
505}
rustbasic_core/database.rs

rustbasic_core/
database.rs
