Manual:Database Functions

Database Functions

These database functions make using a database with Mudlet easier. They are optional, if you are an expert in SQL, you can use LuaSQL's sqlite driver directly within Mudlet - see it's manual here.

db:add

db:add(sheet reference, table1, …, tableN)

Adds one or more new rows to the specified sheet. If any of these rows would violate a UNIQUE index, a lua error will be thrown and execution will cancel. As such it is advisable that if you use a UNIQUE index, you test those values before you attempt to insert a new row.

Example

<lua> --Each table is a series of key-value pairs to set the values of the sheet, --but if any keys do not exist then they will be set to nil or the default value. db:add(mydb.enemies, {name="Bob Smith", city="San Francisco"}) db:add(mydb.enemies,

    {name="John Smith", city="San Francisco"},
    {name="Jane Smith", city="San Francisco"},
    {name="Richard Clark"})

</lua>

As you can see, all fields are optional.

db:aggregate

db:aggregate(field reference, aggregate function, query)

Returns the result of calling the specified aggregate function on the field and its sheet. The query is optional.

The supported aggregate functions are:

• COUNT - Returns the total number of records that are in the sheet or match the query.
• AVG - Returns the average of all the numbers in the specified field.
• MAX - Returns the highest number in the specified field.
• MIN - Returns the lowest number in the specified field.
• TOTAL - Returns the value of adding all the contents of the specified field.

Example

<lua> local mydb = db:get_database("my database") echo(db:aggregate(mydb.enemies.name, "count")) </lua>

db:AND

db:AND(sub-expression1, …, sub-expressionN)

Returns a compound database expression that combines all of the simple expressions passed into it; these expressions should be generated with other db: functions such as db:eq, db:like, db:lt and the like.

This compound expression will only find items in the sheet if all sub-expressions match.

db:between

db:between(field reference, lower_bound, upper_bound)

Returns a database expression to test if the field in the sheet is a value between lower_bound and upper_bound. This only really makes sense for numbers and Timestamps.

db:create

db:create(database name, schema table)

Creates and/or modifies an existing database. This function is safe to define at a top-level of a Mudlet script: in fact it is reccommended you run this function at a top-level without any kind of guards. If the named database does not exist it will create it. If the database does exist then it will add any columns or indexes which didn’t exist before to that database. If the database already has all the specified columns and indexes, it will do nothing.

The database will be called Database_<sanitized database name>.db and will be stored in the Mudlet configuration directory.

Database tables are called sheets consistantly throughout this documentation, to avoid confusion with Lua tables.

The schema table must be a Lua table array containing table dictionaries that define the structure and layout of each sheet

Example

<lua> local mydb = db:create("combat_log", { kills = {

         	name = "",
         	area = "",
         	killed = db:Timestamp("CURRENT_TIMESTAMP"),
         	_index = { {"name", "area"} }
        	},
         	enemies = {
         		name = "",
               	city = "",
               	reason = "",
               	enemied = db:Timestamp("CURRENT_TIMESTAMP"),
               	_index = { "city" },
               	_unique = { "name" },
               	_violations = "IGNORE"

} }) </lua>

The above will create a database with two sheets; the first is kills and is used to track every successful kill, with both where and when the kill happened. It has one index, a compound inde tracking the combination of name and area. The second sheet has two indexes, but one is unique: it isn’t possible to add two items to the enemies sheet with the same name.

For sheets with unique indexes, you may specify a _violations key to indicate how the db layer handle cases where the unique index is violated. The options you may use are:

• FAIL - the default. A hard error is thrown, cancelling the script.
• IGNORE - The command that would add a record that violates uniqueness just fails silently.
• REPLACE - The old record which matched the unique index is dropped, and the new one is added to replace it.

Returns a reference of an already existing database. This instance can be used to get references to the sheets (and from there, fields) that are defined within the database. You use these references to construct queries.

If a database has a sheet named enemies, you can obtain a reference to that sheet by simply doing:

<lua> local mydb = db:get_database("my database") local enemies_ref = mydb.enemieslocal name_ref = mydb.enemies.name </lua>

db:delete

db:delete(sheet reference, query)

Deletes rows from the specified sheet. The argument for query tries to be intelligent:

• If it is a simple number, it deletes a specific row by _row_id
• If it is a table that contains a _row_id (e.g., a table returned by db:get) it deletes just that record.
• Otherwise, it deletes every record which matches the query pattern which is specified as with b:get.
• If the query is simply true, then it will truncate the entire contents of the sheet.

Example

<lua> enemies = db:fetch(mydb.enemies) db:delete(mydb.enemies, enemies[1])

db:delete(mydb.enemies, enemies[1]._row_id) db:delete(mydb.enemies, 5) db:delete(mydb.enemies, db:eq(mydb.enemies.city, "San Francisco")) db:delete(mydb.enemies, true) </lua>

Those deletion commands will do in order:

1. one When passed an actual result table that was obtained from db:fetch, it will delete the record for that table.
2. two When passed a number, will delete the record for that _row_id. This example shows getting the row id from a table.
3. three As above, but this example just passes in the row id directly.
4. four Here, we will delete anything which matches the same kind of query as db:fetch uses-- namely, anyone who is in the city of San Francisco.
5. five And finally, we will delete the entire contents of the enemies table.

db:eq

db:eq(field reference, value)

Returns a database expression to test if the field in the sheet is equal to the value.

db:exp

db:exp(string)

Returns the string as-is to the database.

Use this function with caution, but it is very useful in some circumstances. One of the most common of such is incrementing an existing field in a db:set() operation, as so:

<lua> db:set(mydb.enemies, db:exp("kills + 1"), db:eq(mydb.enemies.name, "Ixokai")) </lua>

This will increment the value of the kills field for the row identified by the name Ixokai.

But there are other uses, as the underlining database layer provides many functions you can call to do certain things. If you want to get a list of all your enemies who have a name longer then 10 characters, you may do:

<lua> db:fetch(mydb.enemies, db:exp("length(name) > 10")) </lua>

Again, take special care with this, as you are doing SQL syntax directly and the library can’t help you get things right.

db:fetch

db:fetch(sheet reference, query, order_by, descending)

Returns a table array containing a table for each matching row in the specified sheet. All arguments but sheet are optional. If query is nil, the entire contents of the sheet will be returned.

Query is a string which should be built by calling the various db: expression functions, such as db:eq, db:AND, and such. You may pass a SQL WHERE clause here if you wish, but doing so is very dangerous. If you don’t know SQL well, its best to build the expression.

Query may also be a table array of such expressions, if so they will be AND’d together implicitly.

The results that are returned are not in any guaranteed order, though they are usually the same order as the records were inserted. If you want to rely on the order in any way, you must pass a value to the order_by field. This must be a table array listing the fields you want to sort by. It can be { mydb.kills.area }, or { mydb.kills.area, mydb.kills.name }

The results are returned in ascending (smallest to largest) order; to reverse this pass true into the final field.

Example

<lua> db:fetch(mydb.enemies, nil, {mydb.enemies.city, mydb.enemies.name}) db:fetch(mydb.enemies, db:eq(mydb.enemies.city, "San Francisco")) db:fetch(mydb.kills,

    {db:eq(mydb.kills.area, "Undervault"),
    db:like(mydb.kills.name, "%Drow%")}

) </lua>

The first will fetch all of your enemies, sorted first by the city they reside in and then by their name.

The second will fetch only the enemies which are in San Francisco.

The third will fetch all the things you’ve killed in Undervault which have Drow in their name.

db:gt

db:gt(field reference, value)

Returns a database expression to test if the field in the sheet is greater than to the value.

db:get_database

db:get_database(database_name)

Returns your database name.

Example

<lua> local mydb = db:get_database("my database") </lua>

db:gte

db:gte(field reference, value)

Returns a database expression to test if the field in the sheet is greater than or equal to the value.

db:in_

db:in_(field reference, table array)

Returns a database expression to test if the field in the sheet is one of the values in the table array.

First, note the trailing underscore carefully! It is required.

The following example illustrates the use of in_:

<lua> local mydb = db:get_database("my database") local areas = {"Undervault", "Hell", "Purgatory"}

db:fetch(mydb.kills, db:in_(mydb.kills.area, areas)) </lua>

This will obtain all of your kills which happened in the Undervault, Hell or Purgatory. Every db:in_ expression can be written as a db:OR, but that quite often gets very complex.

db:is_nil

db:is_nil(field reference, value)

Returns a database expression to test if the field in the sheet is nil.

db:is_not_nil

db:is_not_nil(field reference, value)

Returns a database expression to test if the field in the sheet is not nil.

db:like

db:like(field reference, pattern)

returns a database expression to test if the field in the sheet matches the specified pattern.

LIKE patterns are not case-sensitive, and allow two wild cards. The first is an underscore which matches any single one character. The second is a percent symbol which matches zero or more of any character.

LIKE with "_" is therefore the same as the "." regular expression.

LIKE with "%" is therefore the same as ".*" regular expression.

db:lt

db:lt(field reference, value)

Returns a database expression to test if the field in the sheet is less than the value.

db:lte

db:lte(field reference, value)

Returns a database expression to test if the field in the sheet is less than or equal to the value.

db:merge_unique

db:merge_unique(sheet reference, table array)

Merges the specified table array into the sheet, modifying any existing rows and adding any that don’t exist.

This function is a convenience utility that allows you to quickly modify a sheet, changing existing rows and add new ones as appropriate. It ONLY works on sheets which have a unique index, and only when that unique index is only on a single field. For more complex situations you’ll have to do the logic yourself.

The table array may contain tables that were either returned previously by db:fetch, or new tables that you’ve constructed with the correct fields, or any mix of both. Each table must have a value for the unique key that has been set on this sheet.

For example, consider this database

<lua> local mydb = db:create("peopledb",

    {
         friends = {
              name = "",
              race = "",
              level = 0,
              city = "",
              _index = { "city" },
              _unique = { "name" }
         }

); </lua>

Here you have a database with one sheet, which contains your friends, their race, level, and what city they live in. Let’s say you want to fetch everyone who lives in San Francisco, you could do:

<lua> local results = db:fetch(mydb.friends, db:eq(mydb.friends.city, "San Francisco")) </lua>

The tables in results are static, any changes to them are not saved back to the database. But after a major radioactive cataclysm rendered everyone in San Francisco a mutant, you could make changes to the tables as so:

<lua> for _, friend in ipairs(results) do

    friend.race = "Mutant"

end </lua>

If you are also now aware of a new arrival in San Francisco, you could add them to that existing table array:

<lua> results[#results+1] = {name="Bobette", race="Mutant", city="San Francisco"} </lua>

And commit all of these changes back to the database at once with:

<lua> db:merge_unique(mydb.friends, results) </lua>

The db:merge_unique function will change the city values for all the people who we previously fetched, but then add a new record as well.

db:not_between

db:not_between(field reference, lower_bound, upper_bound)

Returns a database expression to test if the field in the sheet is not a value between lower_bound and upper_bound. This only really makes sense for numbers and Timestamps.

db:not_eq

db:not_eq(field reference, value)

Returns a database expression to test if the field in the sheet is NOT equal to the value.

db:not_in

db:not_in(field reference, table array)

Returns a database expression to test if the field in the sheet is not one of the values in the table array.

See also: db:in_

db:not_like

db:not_like(field reference, pattern)

Returns a database expression to test if the field in the sheet does not match the specified pattern.

LIKE patterns are not case-sensitive, and allow two wild cards. The first is an underscore which matches any single one character. The second is a percent symbol which matches zero or more of any character.

LIKE with "_" is therefore the same as the "." regular expression.

LIKE with "%" is therefore the same as ".*" regular expression.

db:OR

db:OR(sub-expression1, sub-expression2)

Returns a compound database expression that combines both of the simple expressions passed into it; these expressions should be generated with other db: functions such as db:eq, db:like, db:lt and the like.

This compound expression will find any item that matches either the first or the second sub-expression.

db:set

db:set(field reference, value, query)

The db:set function allows you to set a certain field to a certain value across an entire sheet. Meaning, you can change all of the last_read fields in the sheet to a certain value, or possibly only the last_read fields which are in a certain city. The query argument can be any value which is appropriate for db:fetch, even nil which will change the value for the specified column for EVERY row in the sheet.

For example, consider a situation in which you are tracking how many times you find a certain type of egg during Easter. You start by setting up your database and adding an Eggs sheet, and then adding a record for each type of egg.

Example

<lua> local mydb = db:create("egg database", {eggs = {color = "", last_found = db.Timestamp(false), found = 0}})

       db:add(mydb.eggs,
               {color = "Red"},
               {color = "Blue"},
               {color = "Green"},
               {color = "Yellow"},
               {color = "Black"}
       )

</lua>

Now, you have three columns. One is a string, one a timestamp (that ends up as nil in the database), and one is a number.

You can then set up a trigger to capture from the mud the string, "You pick up a (.*) egg!", and you end up arranging to store the value of that expression in a variable called "myegg".

To increment how many we found, we will do this:

<lua> myegg = "Red" -- We will pretend a trigger set this.

       db:set(mydb.eggs.found, db:exp("found + 1"), db:eq(mydb.eggs.color, myegg))
       db:set(mydb.eggs.last_found, db.Timestamp("CURRENT_TIMESTAMP"), db:eq(mydb.eggs.color, myegg))

</lua>

This will go out and set two fields in the Red egg sheet; the first is the found field, which will increment the value of that field (using the special db:exp function). The second will update the last_found field with the current time.

Once this contest is over, you may wish to reset this data but keep the database around. To do that, you may use a more broad use of db:set as such:

<lua> db:set(mydb.eggs.found, 0) db:set(mydb.eggs.last_found, nil) </lua>

db:update

db:update(sheet reference, table)

This function updates a row in the specified sheet, but only accepts a row which has been previously obtained by db:fetch. Its primary purpose is that if you do a db:fetch, then change the value of a field or tow, you can save back that table.

Example

<lua> local mydb = db:get_database("my database") local bob = db:fetch(mydb.friends, db:eq(mydb.friends.name, "Bob"))[1] bob.notes = "He's a really awesome guy." db:update(mydb.friends, bob) </lua>

This obtains a database reference, and queries the friends sheet for someone named Bob. As this returns a table array containing only one item, it assigns that one item to the local variable named bob. We then change the notes on Bob, and pass it into db:update() to save the changes back.