rustbasic_core/
database.rs

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

rustbasic_core/
database.rs
