fluffos/testsuite/std/database.lpc

/**
 * @brief LPMUD数据库操作模块，使用链式调用优雅的增删改查
 * @author 雪风@mud.ren
 */
#ifdef __PACKAGE_DB__

// 数据库配置
nosave string db_host;
nosave string db_db;
nosave string db_user;
nosave int db_type = __DEFAULT_DB__;
// 数据库查询
nosave mixed db_handle;
// 错误消息
nosave string db_error;
/**
 * @brief The table which the query is targeting.
 *
 */
nosave string db_table;
// 数据表头
nosave string *db_table_column = ({});

/**
 * @brief SQL语句
 *
 * SELECT
 * [DISTINCT] <select_list>
 * FROM <left_table>
 * [<join_type> JOIN <right_table>]
 * [ON <join_condition>]
 * [WHERE <where_condition>]
 * [GROUP BY <group_by_list>]
 * [HAVING <having_condition>]
 * [ORDER BY <order_by_condition>]
 * [LIMIT <limit_number>]
 * [OFFSET <offset_number>]
 * [UNION [ALL]]
 * [SELECT ***]
 */
nosave string db_sql;
/**
 * @brief The columns that should be returned.
 *
 */
nosave string db_sql_columns = "*";
/**
 * @brief The where constraints for the query.
 *
 */
nosave string db_sql_where;
nosave string *db_sql_wheres = ({});
/**
 * @brief The groupings for the query.
 *
 */
nosave string db_sql_groups;
/**
 * @brief The having constraints for the query.
 *
 */
nosave string db_sql_havings;
/**
 * @brief The orderings for the query.
 *
 */
nosave string db_sql_orders;
/**
 * @brief The maximum number of records to return.
 *
 */
nosave int db_sql_limit;
/**
 * @brief The number of records to skip.
 *
 */
nosave int db_sql_offset;

// 状态和条件
nosave int db_distinct;
nosave int db_inRandomOrder;
nosave int db_withColumn;
nosave int db_autoClose = 1;
/**
 * @brief 数据库连接初始化
 *
 * @param host
 * @param db
 * @param user
 * @return void
 */
varargs void create(string host, string db, string user, int type)
{
    if (host)
    {
        db_host = host;
    }
    if (db)
    {
        db_db = db;
    }
    if (user)
    {
        db_user = user;
    }
    if (type)
    {
        db_type = type;
    }
}
/**
 * @brief 重置数据库链接配置
 *
 * @param db
 */
void setConnection(mapping db)
{
    db_host = db["host"];
    db_db = db["database"];
    db_user = db["user"];
    if (db["type"])
    {
        db_type = db["type"];
    }
}
/**
 * @brief 是否自动关闭数据库连接
 *
 * @param flag
 */
void setAutoClose(int flag)
{
    db_autoClose = flag;
}
// 重置查询
void resetSql()
{
    db_error = 0;
    db_withColumn = 0;
    db_distinct = 0;
    db_sql = "";
    db_sql_columns = "*";
    db_sql_where = "";
    db_sql_wheres = ({});
    db_sql_groups = "";
    db_sql_havings = "";
    db_sql_orders = "";
    db_sql_limit = 0;
    db_sql_offset = 0;
    db_inRandomOrder = 0;
}
/**
 * @brief 原生SQL调用
 *
 * @return this_object()
 */
object sql(string sql)
{
    resetSql();
    db_sql = sql;

    return this_object();
}
/**
 * @brief 构造数据表调用
 *
 * @param table
 * @return object
 */
object table(string table)
{
    resetSql();
    db_table = table;

    return this_object();
}
/**
 * @brief 使用DISTINCT过滤重复数据
 *
 * @return object
 */
object distinct()
{
    db_distinct = 1;

    return this_object();
}
/**
 * @brief 针对SQL展开数组的处理
 *
 * @param arr 需处理的数组
 * @param del 分隔符
 * @return string
 */
string implodeX(mixed *arr, string del)
{
    string s = "";

    foreach (mixed x in arr)
    {
        // debug_message(typeof(x));
        switch (typeof(x))
        {
        case "int":
        case "float":
            s += x + del;
            break;
        default:
            s += "'" + x + "'" + del;
            break;
        }
    }

    return s[0.. < sizeof(del) + 1];
}
/**
 * @brief where条件查询处理
 *
 * @param where
 * @param boolean 条件关系" AND "、" OR "...
 */
void addArrayOfWheres(mixed *where, string boolean)
{
    foreach (mixed *x in where)
    {
        if (arrayp(x) && sizeof(x) == 2)
        {
            db_sql_wheres += ({x[0] + "='" + x[1] + "'"});
        }
        else if (arrayp(x) && sizeof(x) == 3)
        {
            db_sql_wheres += ({x[0] + " " + x[1] + " '" + x[2] + "'"});
        }
    }
    if (boolean == " OR ")
    {
        db_sql_where += " OR " + implode(db_sql_wheres, " AND ");
    }
    else
    {
        db_sql_where = implode(db_sql_wheres, " AND ");
    }
}

object where(mixed *x...)
{
    // debug_message(sprintf("%O", x));
    if (sizeof(db_sql_where))
    {
        db_sql_where += " AND ";
    }
    if (arrayp(x[0]))
    {
        addArrayOfWheres(x[0], " AND ");
    }
    else if (sizeof(x) == 2)
    {
        db_sql_where += x[0] + "='" + x[1] + "'";
    }
    else if (sizeof(x) == 3)
    {
        db_sql_where += x[0] + " " + x[1] + " '" + x[2] + "'";
    }

    return this_object();
}

object orWhere(mixed *x...)
{
    if (arrayp(x[0]))
    {
        addArrayOfWheres(x[0], " OR ");
    }
    else if (sizeof(x) == 2)
    {
        db_sql_where += " OR " + x[0] + "='" + x[1] + "'";
    }
    else if (sizeof(x) == 3)
    {
        db_sql_where += " OR " + x[0] + " " + x[1] + " '" + x[2] + "'";
    }

    return this_object();
}

varargs object whereBetween(string column, mixed *x, int not)
{
    string between = " BETWEEN ";

    if (not)
    {
        between = " NOT BETWEEN ";
    }

    if (sizeof(db_sql_where))
    {
        db_sql_where += " AND ";
    }

    if (sizeof(x) == 2)
    {
        db_sql_where += column + between + x[0] + " AND " + x[1];
    }

    return this_object();
}

object whereNotBetween(string column, mixed *x)
{
    return whereBetween(column, x, 1);
}

varargs object orWhereBetween(string column, mixed *x, int not)
{
    string between = " BETWEEN ";

    if (not)
    {
        between = " NOT BETWEEN ";
    }

    if (sizeof(x) == 2)
    {
        db_sql_where += " OR " + column + between + x[0] + " AND " + x[1];
    }

    return this_object();
}

object orWhereNotBetween(string column, mixed *x)
{
    return orWhereBetween(column, x, 1);
}

varargs object whereNull(string column, int not)
{
    string null = " IS NULL";

    if (not)
    {
        null = " IS NOT NULL";
    }

    if (sizeof(db_sql_where))
    {
        db_sql_where += " AND ";
    }

    db_sql_where += column + null;

    return this_object();
}

object whereNotNull(string column)
{
    return whereNull(column, 1);
}

varargs object orWhereNull(string column, int not)
{
    string null = " IS NULL";

    if (not)
    {
        null = " IS NOT NULL";
    }

    db_sql_where += " OR " + column + null;

    return this_object();
}

object orWhereNotNull(string column)
{
    return orWhereNull(column, 1);
}

object whereIn(string column, mixed *x, int not)
{
    string notin = " IN ";

    if (not)
    {
        notin = " NOT IN ";
    }

    if (sizeof(db_sql_where))
    {
        db_sql_where += " AND ";
    }

    db_sql_where += column + notin + "(" + implodeX(x, ",") + ")";

    return this_object();
}

object whereNotIn(string column, mixed *x)
{
    return whereIn(column, x, 1);
}

object orWhereIn(string column, mixed *x, int not )
{
    string notin = " IN ";

    if (not)
    {
        notin = " NOT IN ";
    }

    db_sql_where += " OR " + column + notin + "(" + implodeX(x, ",") + ")";

    return this_object();
}

object orWhereNotIn(string column, mixed *x)
{
    return orWhereIn(column, x, 1);
}
/**
 * @brief 分组
 *
 * @param column
 * @return object
 */
object groupBy(string *column...)
{
    // todo
    return this_object();
}
/**
 * @brief 分组过滤
 *
 * @param column
 * @param operator
 * @param value
 * @param boolean
 * @return object
 */
object having(string column, string operator, mixed value, string boolean)
{
    // todo
    return this_object();
}
/**
 * @brief 排序
 *
 * @param column
 * @param order asc / desc
 * @return object
 */
varargs object orderBy(string column, string order)
{
    if (!nullp(column))
    {
        if (!stringp(order) || member_array(lower_case(order), ({"asc", "desc"})) < 0)
        {
            order = "ASC";
        }
        if (sizeof(db_sql_orders))
        {
            db_sql_orders += ",";
        }

        db_sql_orders += column + " " + upper_case(order);
    }

    return this_object();
}

object inRandomOrder()
{
    db_inRandomOrder = 1;

    return this_object();
}
/**
 * @brief limit处理
 *
 * @param n
 * @return object
 */
object limit(int n)
{
    db_sql_limit = n;

    return this_object();
}
/**
 * @brief offset处理
 *
 * @param n
 * @return object
 */
object offset(int n)
{
    db_sql_offset = n;

    return this_object();
}

object with(string str)
{
    switch (str)
    {
    case "column":
        db_withColumn = 1;
        break;

    default:
        break;
    }

    return this_object();
}

// 构造条件语句
private void db_sql_bindings()
{
    if (sizeof(db_sql_where))
    {
        db_sql += " WHERE " + db_sql_where;
    }

    if (sizeof(db_sql_orders))
    {
        db_sql += " ORDER BY " + db_sql_orders;
    }

    if (db_sql_limit)
    {
        db_sql += " LIMIT " + db_sql_limit;

        if (db_sql_offset)
        {
            db_sql += " OFFSET " + db_sql_offset;
        }
    }
}
// 构造查询语句
private string db_sql()
{
    if (!sizeof(db_sql))
    {
        if (db_distinct)
        {
            db_sql_columns = "DISTINCT " + db_sql_columns;
        }

        db_sql = "SELECT " + db_sql_columns + " FROM " + db_table;
        db_sql_bindings();
    }

    return db_sql;
}
/**
 * @brief 连接数据库并返回handle
 *
 */
private mixed connect()
{
    // 连接数据库
    if (!db_handle || stringp(db_handle))
    {
        db_handle = db_connect(db_host, db_db, db_user, db_type);
        /* error */
        if (stringp(db_handle))
            return db_error = db_handle;
        else
        {
            // 默认mysql编码
            // db_exec(db_handle, "set names utf8mb4");
        }
    }
    return db_handle;
}
/**
 * @brief 关闭数据库连接
 *
 */
varargs mixed close(int flag)
{
    if ((flag || db_autoClose) && intp(db_handle) && db_handle && db_close(db_handle))
    {
        db_handle = 0;
    }

    return db_handle;
}
/**
 * @brief 执行SQL语句并返回结果行数
 *
 */
varargs mixed exec()
{
    mixed rows;
    // 连接数据库
    if (stringp(connect()))
    {
        return db_error;
    }
    // 执行SQL语句
    rows = db_exec(db_handle, db_sql());
    /* error */
    if (stringp(rows))
    {
        close();
        return db_error = rows;
    }
    // 保存数据表头
    db_table_column = db_fetch(db_handle, 0);

    return rows;
}

varargs mixed get(string *columns...)
{
    mixed rows, *res;

    if (sizeof(columns))
    {
        db_sql_columns = implode(columns, ",");
    }

    rows = exec();
    /* error */
    if (stringp(db_error))
    {
        return db_error;
    }

    res = allocate(rows);
    for (int i = 1; i <= rows; i++)
    {
        res[i - 1] = db_fetch(db_handle, i);
    }
    close();

    if (db_inRandomOrder)
    {
        res = shuffle(res);
    }
    if (db_withColumn)
    {
        res = ({ db_table_column }) + res;
    }

    return res;
}

mixed pluck(string column)
{
    mixed *res, *arr = ({});
    int index;

    db_sql_columns = column;
    res = get();
    /* error */
    if (stringp(db_error))
    {
        return db_error;
    }
    index = member_array(column, db_table_column);
    for (int i = 0; i < sizeof(res); i++)
    {
        arr += ({res[i][index]});
    }

    return arr;
}

varargs mixed first(string *columns...)
{
    mixed rows, *res;
    int i = 1;

    if (sizeof(columns))
    {
        db_sql_columns = implode(columns, ",");
    }

    rows = exec();
    /* error */
    if (stringp(db_error))
    {
        return db_error;
    }

    if (db_inRandomOrder)
    {
        i = random(rows) + 1;
    }
    res = db_fetch(db_handle, i);
    close();

    return res;
}

mixed find(int id)
{
    return where("id", "=", id)->first();
}

mixed value(string column)
{
    mixed *res = first(column);
    int index = member_array(column, db_table_column);

    if (sizeof(res))
    {
        return res[index];
    }

    return "";
}

/**
 * @brief 数据库聚合函数
 *
 * @param func
 * @param column
 * @return private
 */
private mixed aggregate(string func, mixed column)
{
    mixed rows, *res;

    if (column)
    {
        db_sql = "SELECT " + func + "(" + column + ") FROM " + db_table;
        db_sql_bindings();
    }
    else
    {
        return "";
    }

    rows = exec();
    /* error */
    if (stringp(db_error))
    {
        return db_error;
    }

    res = db_fetch(db_handle, 1);
    close();

    return res[0];
}

varargs mixed count(mixed column)
{
    if (nullp(column))
    {
        column = 1;
    }

    return aggregate("COUNT", column);
}

mixed max(string column)
{
    return aggregate("MAX", column);
}

mixed min(string column)
{
    return aggregate("MIN", column);
}

mixed avg(string column)
{
    return aggregate("AVG", column);
}

mixed sum(string column)
{
    return aggregate("SUM", column);
}

/**
 * @brief 插入
 *
 */
mixed insert(mapping m)
{
    // 构造插入语句
    db_sql = "INSERT INTO " + db_table + " (" + implode(keys(m), ",") + ") VALUES (" + implodeX(values(m), ",") + ")";
    //执行SQL
    exec();
    /* error */
    if (stringp(db_error))
    {
        return db_error;
    }
    close();

    return 1;
}

/**
 * @brief 更新
 *
 */
mixed update(mapping m)
{
    mixed key, value;
    string sql = "";
    foreach (key, value in m)
    {
        value = typeof(value) == "string" ? "'" + value + "'" : value;
        sql += key + "=" + value + ",";
    }
    sql = sql[0.. < 2];
    // 构造更新语句
    db_sql = "UPDATE " + db_table + " SET " + sql;
    db_sql_bindings();
    //执行SQL
    exec();
    /* error */
    if (stringp(db_error))
    {
        return db_error;
    }
    close();

    return 1;
}

/**
 * @brief 删除
 *
 */
mixed delete()
{
    // 构造删除语句
    db_sql = "DELETE FROM " + db_table;
    db_sql_bindings();
    //执行SQL
    exec();
    /* error */
    if (stringp(db_error))
    {
        return db_error;
    }
    close();

    return 1;
}

/**
 * @brief 调试
 *
 */
string dump()
{
    string line = repeat_string("-*-", 20);
    return sprintf("\n%s\ndb_host = %s\ndb_db = %s\ndb_user = %s\ndb_handle = %d\ndb_error = %O\ndb_table = %s\ndb_table_column = %O\ndb_sql = %s\ndb_status = %s\n%s\n", line, db_host, db_db, db_user, db_handle, db_error, db_table, db_table_column, db_sql, db_status(), line);
}

#endif