FluffOS Docs

Appearance

database ​

Introduction ​

The database query builder provides a convenient, fluent interface to creating and running database queries. It can be used to perform most database operations in your mud and works perfectly with all of fluffos supported database systems.

Config ​

You can inherit /std/database.c and set config with setConnection(), for example:

c
inherit "/std/database";

void test()
{
    mixed res;
    // use SQLITE3
    mapping db = ([
        "host":"",
        "database":"/data/db.sqlite",
        "user":"",
        "type":__USE_SQLITE3__
    ]);
    database::setConnection(db);
    // database queries
    printf("%O\n", database::table("users")->get());
}

The database type can be __USE_MYSQL__、__USE_SQLITE3__、__USE_POSTGRE__ or __DEFAULT_DB__。

Or, clone database object like this:

c
void test()
{
    object db = new("/std/database", "", "/sqlite.db", "", __USE_SQLITE3__);
    printf("%O\n", db->table("users")->get());
}

Running Database Queries ​

Retrieving All Rows From A Table ​

You may use the table method provided by the /std/database.c to begin a query. The table method returns a fluent query builder instance for the given table, allowing you to chain more constraints onto the query and then finally retrieve the results of the query using the get method:

c
void test()
{
    object db = new("/std/database", "", "/sqlite.db", "", __USE_SQLITE3__);
    // Retrieving all rows from users table
    printf("%O\n", db->table("users")->get());
}

The get method return a 2D array.

Retrieving A Single Row / Column From A Table ​

If you just need to retrieve a single row from a database table, you may use the first method. This method will return an array:

c
    printf("%O\n", db->table("users")->first());

If you don't need an entire row, you may extract a single value from a record using the value method. This method will return the value of the column directly:

c
    printf("%s\n", db->table("users")->where("name", "mudren")->value("email"));

To retrieve a single row by its id column value, use the find method:

c
    printf("%O\n", db->table("users")->find(3));

Retrieving A List Of Column Values ​

If you would like to retrieve result containing the values of a single column, you may use the pluck method. In this example, we'll retrieve an array of user names:

c
    printf("%O\n", db->table("users")->pluck("name"));

Aggregates ​

The query builder also provides a variety of methods for retrieving aggregate values like count, max, min, avg, and sum. You may call any of these methods after constructing your query:

c
    printf("user_count = %d\n", db->table("users")->count());
    printf("max_age = %d\n", db->table("users")->max("age"));

Of course, you may combine these methods with other clauses to fine-tune how your aggregate value is calculated:

c
    printf("count = %d\n", db->table("users")->where("age", 14)->count());

Raw Expressions ​

Sometimes you may need to insert an arbitrary string into a query. To create a raw string expression, you may use the sql and exec method：

c
    object db = new("/std/database", "", "/sqlite.db", "", __USE_SQLITE3__);
    mixed res;
    db->sql("DROP TABLE IF EXISTS `users`")->exec();
    res = db->sql("CREATE TABLE IF NOT EXISTS `users` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,`name` varchar(25) NOT NULL,`bio` TEXT(255) DEFAULT NULL,`activated_at` timestamp DEFAULT NULL)")->exec();

Basic Where Clauses ​

Where Clauses ​

You may use the query builder's where method to add "where" clauses to the query. The most basic call to the where method requires three arguments. The first argument is the name of the column. The second argument is an operator, which can be any of the database's supported operators. The third argument is the value to compare against the column's value.

For example, the following query retrieves users where the value of the exp column is equal to 100 and the value of the age column is greater than 15:

c
res = database::table("users")
            ->where("exp", "=", 100)
            ->where("age", ">", 15)
            ->get();

For convenience, if you want to verify that a column is = to a given value, you may pass the value as the second argument to the where method. database will assume you would like to use the = operator:

c
    res = database::table("users")->where("age", 16)->get();

As previously mentioned, you may use any operator that is supported by your database system:

c
users = database::table("users")
                ->where("id", ">=", 100)
                ->get();

users = database::table("users")
                ->where("id", "<>", 100)
                ->get();

users = database::table("users")
                ->where("email", "like", "%@mud.ren")
                ->get();

You may also pass an array of conditions to the where function. Each element of the array should be an array containing the three arguments typically passed to the where method:

c
// WHERE user_id > 7 AND category_id = 4
res = db->table("topics")->where(({ ({"user_id", ">", 7}), ({"category_id", 4}) }))->get();

Or Where Clauses ​

When chaining together calls to the query builder's where method, the "where" clauses will be joined together using the and operator. However, you may use the orWhere method to join a clause to the query using the or operator. The orWhere method accepts the same arguments as the where method:

c
// WHERE user_id > 7 OR category_id = 4
res = db->table("topics")->where("user_id", ">", 7)->orWhere("category_id", 4)->get();

Additional Where Clauses ​

whereBetween / orWhereBetween ​

The whereBetween method verifies that a column's value is between two values:

c
users = db->table("users")->whereBetween("age", ({14, 18}))->get();

whereNotBetween / orWhereNotBetween ​

The whereNotBetween method verifies that a column's value lies outside of two values:

c
users = db->table("users")->whereNotBetween("age", ({14, 18}))->get();

whereIn / whereNotIn / orWhereIn / orWhereNotIn ​

The whereIn method verifies that a given column's value is contained within the given array. The whereNotIn method verifies that the given column's value is not contained in the given array.

c
users = db->table("users")->whereIn("age", ({14, 18, 24, 30}))->get();

whereNull / orWhereNull / whereNotNull / orWhereNotNull ​

The whereNull method verifies that the value of the given column is NULL, The whereNotNull method verifies that the column's value is not NULL:

c
users = db->table("users")->whereNull("updated_at")->get();

Ordering, Grouping, Limit & Offset ​

Ordering ​

The orderBy method allows you to sort the results of the query by a given column. The first argument accepted by the orderBy method should be the column you wish to sort by, while the second argument determines the direction of the sort and may be either asc or desc:

c
users = db->table("users")
            ->orderBy("name", "desc")
            ->get();

To sort by multiple columns, you may simply invoke orderBy as many times as necessary:

c
users = db->table("users")
            ->orderBy("name", "desc")
            ->orderBy("email", "asc")
            ->get();

Random Ordering ​

The inRandomOrder method may be used to sort the query results randomly. For example, you may use this method to fetch a random user:

c
randomUser = DB->table("users")
                ->inRandomOrder()
                ->get();

Limit & Offset ​

You may use the limit and offset methods to limit the number of results returned from the query or to skip a given number of results in the query:

c
users = db->table("users")
            ->offset(10)
            ->limit(5)
            ->get();

Insert Statements ​

The query builder also provides an insert method that may be used to insert records into the database table. The insert method accepts an mapping of column names and values:

c
db->table("users")->insert((["name":"test", "email":"test@mud.ren"]));

Update Statements ​

In addition to inserting records into the database, the query builder can also update existing records using the update method. The update method, like the insert method, accepts an mapping of column and value pairs indicating the columns to be updated. You may constrain the update query using where clauses:

c
db->table("users")->where("id", ">", 120)->limit(5)->update((["level":99]));

Delete Statements ​

The query builder's delete method may be used to delete records from the table. You may constrain delete statements by adding "where" clauses before calling the delete method:

c
db->table("users")->delete();
db->table("users")->where("id",">", 5)->limit(5)->delete();

Auto Close ​

By default, database will auto close connection and release db_handle, you can use setAutoClose(0) disable this and close the database connection with close(1)：

c
void test()
{
    object DB = new(CORE_DB, host, db, user);
    // close auto close
    DB->setAutoClose(0);

    // ...sql

    // close
    DB->close(1);
}

Debugging ​

You may use the dump methods while building a query to dump the current query bindings and SQL.

c
printf(db->dump());
-*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*-
db_host = 127.0.0.1
db_db = mud
db_user = root
db_handle = 1
db_error = 0
db_table = topics
db_table_column = ({ /* sizeof() == 2 */
"user_id",
"title"
})
db_sql = SELECT user_id,title FROM topics WHERE user_id > '7' AND category_id='4'
-*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*-