Difference between revisions of "Manual:Scripting"
| Line 414: | Line 414: | ||
id = tempTrigger(target, [[selectString("]] .. target .. [[", 1) fg("gold") resetFormat()]]) | id = tempTrigger(target, [[selectString("]] .. target .. [[", 1) fg("gold") resetFormat()]]) | ||
| − | |||
=ATCP= | =ATCP= | ||
Revision as of 21:02, 10 June 2011
Scripting with Mudlet
Lua tables can basically be considered multidimensional arrays and dictionaries at the same time. If we have the table matches, matches[1] is the first element, matches[n] the n-th element.
a = "Tom"
matches[1] = "Betty"
b = matches[1]
c = a .. b and e will equal "TomBetty"
To output a table you can use a convenience function printTable( name ). This function is defined in LuaGlobal.lua and can be found in your home directory under ./mudlet (note the leading dot ) Profiles and mudlet_documentation.html are stored in this directory as well.
Lua interface functions to Mudlet - or how do I access triggers, timers etc. from Lua scripts
How to get data from regex capture groups? Regular expression capture groups (e.g. "(\d+)" ) are passed on to Lua scripts as a Lua table matches. To make use of this information inside Lua scripts, you need to specify the number of the capture group within the regex.
Example: You have (\d+) weapons and they are (?:(\b\w+\W+)+)
This regex contains 3 capture groups, but only the 2 green colored ones contain data as the red capture group is a non-capturing group. Consequently, your Lua script gets passed only 2 instead of 3 capture groups and matches[4] is undefined.
In your Lua script you may write following program in order to print the number and status of your weapons on the screen:
You have (\d+) weapons and they are (?:(\b\w+\W+)+)
<lua>
number_of_weapons = matches[2] status_of_weapons = matches[3] notice = number_of_weapons .. status_of_weapons echo( notice ) send( "put weapons in backpack" )
-- the following 2 lines color the first capture -- group red and the second group blue -- see below for details
selectCaptureGroup( 2 ) setFgColor( 255,0,0 )
selectCaptureGroup( 3 ) setFgColor( 0,0,255 )</lua>
The best way is to use selectCaptureGroup( number ) to select the proper capture group and then perform your actions on it e.g. replace(), highlight etc. Note: Both selectCaptureGroup() and matches[n] start with group 1 (which is the whole match. The defined capture groups start with 2).
How to select all occurrences of "Tom" and highlight them?
You add a function like this to a script containing you main function definitions. Note that script items differ from all other "scripts" in triggers, timers, actions etc. because they require you to put your code in proper functions that can be called by your other trigger-scripts, timer-scripts etc. via normal function calls. Trigger-scripts, timer-scripts etc. cannot contain any function definitions because they are automatically generated functions themselves because this makes usage a lot easier.
To come back to our question how to select all occurrences of "Tom" and highlight them:
<lua>
function setBgColorAll( word, r, g, b )
i = 0
word_count = 1
while i > -1 do
i = selectString(word, word_count)
if i == -1 then
return
end
word_count = word_count +1
setBgColor( r, g, b )
end
end
</lua>
Then you simply define a substring matching trigger on the word "Tom" and in the trigger script you call above function:
<lua>setBgColorAll("Tom", 255,50,50)</lua>
Sending commands to the MUD or printing information messages on the screen
To print information messages on the session screen you can use the echo( message ) function, or insertText( text). Currently, it only takes one string as argument.
To send a command to the MUD, you can use the send( command ) function. Note: everything you send via send() will be processed by the alias processing unit. In Alias scripts the command that is being sent to the MUD is contained in the variable command that you can change in the context of Alias scripts. Alias take regular expressions, as well. As a result, you can use following regex and script to talk like Yoda: Perl regex:
say (\w+).*(\w*).*(.*)
script:
send( "say " .. matches[3] .." " .. matches[1] .." ".. matches[2] )
Note: The variable "command" contains what was entered in the command line or issued via the expandAlias( ) function. If you use expandAlias( command ) inside an alias script the command would be doubled. You have to use send( ) inside an alias script to prevent recursion. This will send the data directly and bypass the alias expansion.
Changing text from the MUD or reformatting text (highlight, make bold etc.)
When sending commands to the MUD - from now on referred to as output stream - alias scripts find the command that was issued by the user stored in the variable "command".
By manipulating the value, the command can easily be changed before it is being sent to the MUD.
However, things get much more complicated with the data received from the MUD – from now on referred to as input stream. Before triggers can be run on the MUD data, Mudlet has to strip all format codes from the text and store it in data structures associated with the text. Consequently, the text that is being passed on to the trigger processing unit is a small subset of the data received from the MUD. If you want to edit, replace, delete or reformat text from within your trigger scripts you have to keep this in mind if you don’t want to lose all text format information such as colors etc.
As the text is linked with data structures containing the format of the text, the cursor position inside the line is important if data is being changed. You select a word or a sequence of characters from the line and then issue commands to do actions on the selected data.
Replacing the word "Tom" with "Betty" in the line: Jim, Tom and Lucy are learning a new spell. This could be done with following script:
selectString("Tom",1)
replace("Betty")
Things get more complicated if there are two or more occurrences of "Tom" in the line e.g. Jim and Tom like magic. Jim, Tom and Lucy are learning a new spell.
The above example code would select the first occurrence of "Tom" in this line and ignore the second. If you want to work on the the second occurrence of "Tom" you have to specify the occurrence number in the call to select().
selectString( "Tom", 2 )
replace( "Betty" )
This code would change the second "Tom" and leave the first "Tom" alone. The function call
replaceAll( "Betty" )
will replace all occurrences of "Tom" with "Betty" in the line if "Tom" has been selected before. replaceAll() is a convenience function defined in LuaGlobal.lua.
Colorization example: You want to change to color of the words "ugly monster" to red on a white background.
You add a new trigger and define the regex: ugly monster In the script you write:
selectString("ugly monster", 1 )
setFgColor(255,0,0)
setBgColor(255,255,255)
resetFormat()
Another means to select text is to select a range of characters by specifying cursor positions. If we have following line: Jim and Tom like magic. Jim, Tom and Lucy are learning a new spell.
selectSection( 28, 3 )
This example would select the second Tom. The first argument to selectSection is the cursor position within the line and the second argument is the length of the selection.
selectCaptureGroup( number )
Deleting Text - Gagging
This function selects the captureGroup number if you use Perl regular expressions containing capture groups. The first capture group starts with index 1.
deleteLine()
This function deletes the current line - or any line where the cursor is currently placed. You can use repeated calls to this function to effectively erase the entire text buffer. If you want to delete or gag certain words only, you can select the text that you want to delete and then replace it with an empty string e.g:
If you get this line form the MUD: "Mary and Tom walk to the diner."
selectString( "Tom", 1 )
replace( "" )
Then the output will be changed to: "Mary and walk to the diner."
Cursor Movement and Cursor Placement
moveCursor( windowName, x, y ) This will move the user cursor of window windowName to the absolute (x/y) coordinates in the text.
moveCursor( "main", 20, 3950 ) will move the cursor on the 20th character from the left on line number 3950. To determine the current cursor position you can use getLineNumber() and getColumnNumber() as well as getLastLineNumber() to get the number of the last line in the text buffer. moveCursorEnd("main") will move the cursor of the main display to end of the buffer. This is always a new line of size 1 containing the character \n.
number_of_last_line_in_text = getLineCount() returns the number of the last line of the text in the console buffer. This number will change as soon as a new \n is printed either by the user or when a new line arrives from the MUD. All lines from the MUD are terminated with \n which is called line feed or the new line character. This control character ends the current line and move the cursor to the beginning of the next line, thus creating a new, empty line below the line that contains the \n.
line_number_of_the_current_cursor_position = getLineNumber()
column_number_of_the_current_cursor_position = getColumnNumber()
luaTable_containing_textlines = getLines( absolute_line_number_from, absolute_line_number_to ) this will return a Lua table containing all lines between the absolute positions from and to. NOTE: This function uses absolute line numbers, not relative ones like in moveCursor(). This little demo script shows you how to use cursor functions:
moveCursor() return true or false depending on whether the move was possible.
User defined dockable windows
You may want to use dock windows to display information you gathered in your scripts, or you may want to use them as chat windows etc. Adding a user defined window:
openUserWindow( string window_name )
echoUserWindow( string window_name, string text )
setWindowSize( int x, int y )
clearWindow( string window_name )
Dynamic Timers
tempTimer( double timeout, string lua code_to_execute, string/float/int timer_name ) disableTimer( name ) enableTimer( name )
Dynamic Triggers
triggerID = tempTrigger( regex, code ) creates a fast substring matching trigger triggerID = tempRegexTrigger( regex, code ) creates a regular expression matching trigger
Registering Event Handlers and Raising Events
raiseEvent( string name, args )
As an example, your prompt trigger could raise an onPrompt event if you want to attach 2 functions to it. In your prompt trigger, all you’d need to do is raiseEvent("onPrompt") Now we go about creating functions that attach to the event. Lets say the first one is check_health_stuff() and the other is check_salve_stuff(). We would like these to be executed when the event is raised. So create a script and give it a name of check_health_stuff. In the Add user defined event handler, type onPrompt, and press enter to add it to the list. In the script box, create: function check_health_stuff()blah blah end. When the onPrompt event comes along, that script catches it, and does check_health_stuff() for you.
Default events raised by Mudlet
Mudlet itself also creates events for your scripts to hook on. The following events are generated currently:
sysWindowResizeEvent
Raised when the main window is resized, with the new height and width coordinates passed to the event. A common usecase for this event is to move/resize your UI elements according to the new dimensions. Example
This sample code will echo whenever a resize happened with the new dimensions:
<lua> function resizeEvent( event, x, y )
echo("RESIZE EVENT: event="..event.." x="..x.." y="..y.."\n")
end </lua>
sysWindowMousePressEvent
Raised when a mouse button is pressed down anywhere on the main window (note that a click is composed of a mouse press and mouse release). The button number and the x,y coordinates of the click are reported. Example
<lua> function onClickHandler( event, button, x, y )
echo("CLICK event:"..event.." button="..button.." x="..x.." y="..y.."\n")
end </lua>
sysWindowMouseReleaseEvent
Raised when a mouse button is released after being pressed down anywhere on the main window (note that a click is composed of a mouse press and mouse release). See sysWindowMousePressEvent for example use.
sysIrcMessage
Raised when you see or receive an IRC message. The speakers name, channel and their message will follow as arguments.
<lua> function onIrcMessage(_, person, channel, message)
echo(string.format('(%s) %s says, "%s"\n', channel, person, message))
end </lua>
Added to Mudlet in an unreleased version
ATCP events
Mudlets ATCP implementation generates events for each message that comes, allowing you to trigger on them easily. Since ATCP messages vary in name, event names will vary as well. See the atcp section on how to use them.
Handling Tables in Lua
Nick Gammon has written a very nice overview on how to deal with Lua tables. You can find it here: http://www.gammon.com.au/forum/bbshowpost.php?bbsubject_id=6036. How to use multilinematches[n][m]
(The following example can be tested on the MUD batmud.bat.org)
In the case of a multiline trigger with these 2 Perl regex as conditions:
^You have (\w+) (\w+) (\w+) (\w+) ^You are (\w+).*(\w+).*
The command "score" generates the following output on batMUD:
You have an almost non-existent ability for avoiding hits. You are irreproachably kind. You have not completed any quests. You are refreshed, hungry, very young and brave. Conquer leads the human race. Hp:295/295 Sp:132/132 Ep:182/181 Exp:269 >
If you add this script to the trigger:
showMultimatches()
The script, i.e. the call to the function showMultimatches() generates this output: <lua>
-------------------------------------------------------
The table multimatches[n][m] contains:
-------------------------------------------------------
regex 1 captured: (multimatches[1][1-n])
key=1 value=You have not completed any quests
key=2 value=not
key=3 value=completed
key=4 value=any
key=5 value=quests
regex 2 captured: (multimatches[2][1-n])
key=1 value=You are refreshed, hungry, very young and brave
key=2 value=refreshed
key=3 value=young
key=4 value=and
key=5 value=brave
-------------------------------------------------------
</lua> The function showMultimatches() prints out the content of the table multimatches[n][m]. You can now see what the table multimatches[][] contains in this case. The first trigger condition (=regex 1) got as the first full match "You have not completed any quests". This is stored in multimatches[1][1] as the value of key=1 in the sub-table matches[1] which, in turn, is the value of key=1 of the table multimatches[n][m].
The structure of the table multimatches: <lua> multimatches {
1 = {
matches[1] of regex 1
matches[2] of regex 1
matches[3] of regex 1
...
matches[m] of regex 1 },
2 = {
matches[1] of regex 2
matches[2] of regex 2
...
matches[m] of regex 2 },
... ...
n = {
matches[1] of regex n
matches[2] of regex n
...
matches[m] of regex n }
} </lua> The sub-table matches[n] is the same table matches[n] you get when you have a standard non-multiline trigger. The value of the first key, i. e. matches[1], holds the first complete match of the regex. Subsequent keys hold the respective capture groups. For example: Let regex = "You have (\d+) gold and (\d+) silver" and the text from the MUD = "You have 5 gold and 7 silver coins in your bag." Then matches[1] contains "You have 5 gold and 7 silver", matches[2] = "5" and matches[3] = "7". In your script you could do:
<lua> myGold = myGold + tonumber( matches[2] ) mySilver = mySilver + tonumber( matches[3] ) </lua>
However, if you’d like to use this script in the context of a multiline trigger, matches[] would not be defined as there are more than one regex. You need to use multimatches[n][m] in multiline triggers. Above script would look like this if above regex would be the first regex in the multiline trigger:
<lua> myGold = myGold + tonumber( multimatches[1][2] ) mySilver = mySilver + tonumber( multimatches[1][3] ) </lua>
What makes multiline triggers really shine is the ability to react to MUD output that is spread over multiple lines and only fire the action (=run the script) if all conditions have been fulfilled in the specified amount of lines.
Event System
Mudlet-raised events
Mudlet itself also creates events for your scripts to hook on. The following events are generated currently:
sysWindowResizeEvent
Raised when the main window is resized, with the new height and width coordinates passed to the event. A common usecase for this event is to move/resize your UI elements according to the new dimensions. Example
This sample code will echo whenever a resize happened with the new dimensions:
<lua> function resizeEvent( event, x, y )
echo("RESIZE EVENT: event="..event.." x="..x.." y="..y.."\n")
end </lua>
sysWindowMousePressEvent
Raised when a mouse button is pressed down anywhere on the main window (note that a click is composed of a mouse press and mouse release). The button number and the x,y coordinates of the click are reported. Example
<lua> function onClickHandler( event, button, x, y )
echo("CLICK event:"..event.." button="..button.." x="..x.." y="..y.."\n")
end </lua>
sysWindowMouseReleaseEvent
Raised when a mouse button is released after being pressed down anywhere on the main window (note that a click is composed of a mouse press and mouse release). See sysWindowMousePressEvent for example use.
sysIrcMessage
Raised when you see or receive an IRC message. The speakers name, channel and their message will follow as arguments.
<lua> function onIrcMessage(_, person, channel, message)
echo(string.format('(%s) %s says, "%s"\n', channel, person, message))
end </lua>
Added to Mudlet in an unreleased version
GMCP
GMCP is a protocol for MUD servers to communicate information with MUD clients in a separate channel from the one which carries all of the text that makes up the game itself. Mudlet can be configured to use GMCP by clicking on the Settings button (or Options->Preferences in the menu, or <alt>p). The option is on the General tab. Once GMCP is enabled, you will need to reconnect to the MUD so that Mudlet can inform the server it is ready to receive GMCP information. Mudlet will automatically enable some GMCP modules for you once GMCP has been enabled. To get an idea of what information is available, you can use <lua> display(gmcp) </lua>
Managing GMCP Modules
While some GMCP modules are enabled by Mudlet by default when you connect with a GMCP enabled MUD, others may not be 'standard' modules and are instead specific to the MUD itself. In order to provide a way to manage GMCP modules without scripts causing modules in use by other scripts to be disabled, the gmod lua module has been included with Mudlet (rc2.0+).
Registering User
While this step is no longer strictly required, you can register your 'user' with gmod using <lua> gmod.registerUser("MyUser") </lua> However, your user will be automatically registered if you enable or disable any modules using it. Which leads us to...
Enabling Modules
Enabling a GMCP module is as easy as: <lua> gmod.enableModule("MyUser", "Module.Name") </lua>
If MyUser has not been registered previously, then they will be automatically registered when you call this function. An example of a module which would need to be enabled this way is the IRE.Rift module provided by IRE MUDs. <lua> gmod.enableModule("MyUser", "IRE.Rift") </lua>
Disabling Modules
Disabling a GMCP module is just as easy as enabling it: <lua> gmob.disableModule("MyUser", "Module.Name") </lua> The main difference being that the module will be turned on as soon as you enable it if it is not already enabled. If you disable it, it will not be disabled with the server until every user of that module has disabled it. This prevents script A from disabling modules that script B may still be using.
Scripting howtos
How to highlight my current target?
You can put the following script into your targetting alias:
if id then killTrigger(id) end id = tempTrigger(target, selectString(" .. target .. ", 1) fg("gold") resetFormat())
Where target is your target variable. Note that you have to use the full name, capitalized. If you’d like the script to auto-capitalize for you, you can use this version*:
target = target:title() if id then killTrigger(id) end id = tempTrigger(target, selectString(" .. target .. ", 1) fg("gold") resetFormat())
ATCP
Since version 1.0.6, Mudlet includes support for ATCP. This is primarily available on IRE-based MUDs, but Mudlets impelementation is generic enough such that any it should work on others.
The latest ATCP data is stored in the atcp table. Whenever new data arrives, the previous is overwritten. An event is also raised for each ATCP message that arrives. To find out the available messages available in the atcp table and the event names, you can use display(atcp).
Note that while the typical message comes in the format of Module.Submodule, ie Char.Vitals or Room.Exits, in Mudlet the dot is removed - so it becomes CharVitals and RoomExits. Here’s an example:
room_number = tonumber(atcp.RoomNum) echo(room_number)
Triggering on ATCP events
If you’d like to trigger on ATCP messages, then you need to create scripts to attach handlers to the ATCP messages. The ATCP handler names follow the same format as the atcp table - RoomNum, RoomExits, CharVitals and so on.
While the concept of handlers for events is to be explained elsewhere in the manual, the quick rundown is this - place the event name you’d like your script to listen to into the Add User Defined Event Handler: field and press the + button to register it. Next, because scripts in Mudlet can have multiple functions, you need to tell Mudlet which function should it call for you when your handler receives a message. You do that by setting the Script name: to the function name in the script you’d like to be called.
For example, if you’d like to listen to the RoomExits event and have it call the process_exits() function - register RoomExits as the event handler, make the script name be process_exits, and use this in the script:
<lua> function process_exits(event, args)
echo("Called event: " .. event .. "\nWith args: " .. args)
end </lua>
Feel free to experiment with this to achieve the desired results. A ATCP demo package is also available on the forums for using event handlers and parsing its messages into Lua datastructures.
Aardwolf’s 102 subchannel
Similar to ATCP, Aardwolf includes a hidden channel of information that you can access in Mudlet since 1.1.1. Mudlet deals with it in the same way as with ATCP, so for full usage instructions see the ATCP section. All data is stored in the channel102 table, such you can do
display(channel102)
To see all the latest information that has been received. The event to create handlers on is titled channel102Message, and you can use the sendTelnetChannel102(msg) function to send text via the 102 channel back to Aardwolf.
db: Mudlet DB Frontend
The DB package is meant to provide easier access to a database, so you don’t have to know SQL or use the luasql module to set and get at your data. However, it does require that the luasql module be compiled and included in Mudlet to function - and this all is available since Mudlet 1.0.6.
Creating a Database
Before you can store anything in a database, you need to create one. You may have as many independent databases as you wish, with each having as many unique tables-- what we will call sheets in this package, so as to avoid confusion with Lua tables - think spreadsheets.
To create a database, you use the db:create() function, passing it the name of your database and a Lua table containing its schema configuration. A schema is a mold for your database - it defines what goes where. Using the spreadsheet example, ths would mean that you’d define what goes into each column. A simple example:
<lua> db:create("people", {friends={"name", "city", "notes"}, enemies={"name", "city", "notes"}}) </lua>
This will create a database which contains two sheets: one named friends, the other named enemies. Each has three columns, name, city and notes-- and the datatype of each are strings, though the types are very flexible and can be changed basically whenever you would like. It’ll be stored in a file named Database_people.db in your Mudlet config directory on the hard drive should you want to share it.
It’s okay to run this function repeatedly, or to place it at the top-level of a script so that it gets run each time the script is saved: the DB package will not wipe out or clear the existing data in this case. More importantly, this allows you to add columns to an existing sheet. If you change that line to:
<lua> db:create("people", {friends={"name", "city", "notes"}, enemies={"name", "city", "notes", "enemied"}}) </lua>
It will notice that there is a new column on enemies, and add it to the existing sheet-- though the value will end up as nil for any rows which are already present. Similarly, you can add whole new sheets this way. It is not presently possible to -remove- columns or sheets without deleting the database and starting over.
A note on column or field names: you may not create a field which begins with an underscore. This is strictly reserved to the db package for special use.
Adding Data
To add data to your database, you must first obtain a reference (variable) for it. You do that with the db:get_database function, such as:
local mydb = db:get_database("people")
The database object contains certain convenience functions (discussed later, but all are preceded with an underscore), but also a reference to every sheet that currently exists within the database. You then use the db:add() function to add data to the specified sheet.
db:add(mydb.friends, {name="Ixokai", city="Magnagora"})
If you would like to add multiple rows at once to the same table, you can do that by just passing in multiple tables:
db:add(mydb.friends,
{name="Ixokai", city="Magnagora"},
{name="Vadi", city="New Celest"},
{name="Heiko", city="Hallifax", notes="The Boss"}
)
Notice that by default, all columns of every table are considered optional-- if you don’t include it in the add, then it will be set to its default value (which is nil by default)
For those familiar with databases: with the DB package, you don’t have to worry about committing or rolling back any changes, it will commit after each action automatically. If you would like more control then this, see Transactions below.
You also cannot control what is the primary key of any sheets managed with DB, nor do you have to create one. Each row will get a unique integer ID that automatically increments, and this field can be accessed as "_row_id".
Querying
Putting data in isn’t any fun if you can’t get it out. If you want every row from the sheet, you can do:
db:fetch(mydb.friends)
But rarely is that actually useful; usually you want to get only select data. For example, you only want to get people from the city of Magnagora. To do that you need to specify what criteria the system should use to determine what to return to you. It looks like this:
db:fetch(mydb.friends, db:eq(mydb.friends.city, "Magnagora"))
So the basic command is - db:fetch(_sheet_, _what to filter by_)
The following filter operations are defined:
db:eq(field, value[, case_insensitive]) -- Defaults to case insensitive, pass true as the last arg to
reverse this behavior.
db:not_eq(field, value[, case_insensitive) -- Not Equal To
db:lt(field, value) -- Less Than
db:lte(field, value) -- Less Than or Equal to.
db:gt(field, value) -- Greater Than
db:gte(field, value) -- Greater Than or Equal To
db:is_nil(field) -- If the column is nil
db:is_not_nil(field) -- If the column is not nil
db:like(field, pattern) -- A simple matching pattern. An underscore matches any single character,
and a percent(%) matches zero or more characters. Case insensitive.
db:not_like(field, pattern) -- As above, except it'll give you everything but what you ask for.
db:between(field, lower_bound, upper_bound) -- Tests if the field is between the given bounds (numbers only).
db:not_between(field, lower_bound, upper_bound) -- As above, only... not.
db:in_(field, table) -- Tests if the field is in the values of the table. NOTE the trailing underscore!
db:not_in(field, table) -- Tests if the field is NOT in the values of the table *
The db:in_ operator takes a little more explanation. Given a table, it tests if any of the values in the table are in the sheet. For example:
db:in_(mydb.friends.city, {"Magnagora", "New Celest"})
It tests if city == "Magnagora" OR city == "New Celest", but with a more concise syntax for longer lists of items.
There are also two logical operators:
db:AND(operation1, ..., operationN)
db:OR(operation1, operation2)
You may pass multiple operations to db:fetch in a table array, and they will be joined together with an AND by default. For example:
db:fetch(mydb.friends,
{db:eq(mydb.friends.city, "Magnagora"), db:like(mydb.friends.name, "X%")}
)
This will return every record in the sheet which is in the city of Magnagora, and has a name that starts with an X. Again note that in LIKE patterns, a percent is zero or more characters — this is the same effect as "X.*" in pcre patterns. Similarly, an underscore matches any single characters and so is the same as a dot in pcre.
Passing multiple expressions in an array to db:fetch is just a convenience, as its exactly the same as:
db:fetch(mydb.friends, db:AND(db:eq(mydb.friends.city, "Magnagora"), db:like(mydb.friends.name, "I%")))
The db:OR operation only takes two arguments, and will check to see if either of the two is true. You can nest these logical operators as deeply as you need to.
You can also just pass in a string directly to db:fetch, but you have to be very careful as this will be passed straight to the SQL layer. If you don’t know any SQL then you want to avoid this… for example, in SQL there’s a very big difference between double and single quotes. If you don’t know that, then stick to the db functions. But an example is:
db:fetch(mydb.friends, "city == 'Magnagora'")
Now, the return value of db:fetch() is always a table array that contains a table dictionary representing the full contents of all matching rows in the sheet. These are standard Lua tables, and you can perform all normal Lua operations on them. For example, to find out how many total items are contained in your results, you can simply do #results. If a request from the friends sheet were to return one row that you stored in the results variable, it would look like this if passed into the display() function:
table {
1: table {
'name': 'Bob',
'city': 'Magnagora',
'notes': 'Trademaster of Tailoring'
}
}
And if you were to echo(#results), it would show 1.
The order of the returned rows from db:fetch is generally the same as the order in which you entered them into the database, but no actual guarantees are made to this. If you care about the order then you can pass one or two optional parameters after the query to db:fetch() to control this.
The first table array of fields that indicate the column names to sort by; the second is a flag to switch from the default ascending(smallest to largest) sort, to a descending(largest to smallest) sort. For example:
db:fetch(mydb.friends, db:eq(mydb.friends.city, "Magnagora"), {mydb.friends.city})
This will return all your friends in Magnagora, sorted by their name, from smallest to largest. To reverse this, you would simply do:
db:fetch(mydb.friends, db:eq(mydb.friends.city, "Magnagora"), {mydb.friends.city}, true)
Including more then one field in the array will indicate that in the case that two rows have the same value, the second field should be used to sort them.
If you would like to return ALL rows from a sheet, but still sort them, you can do that by passing nil into the query portion. For example:
db:fetch(mydb.friends, nil, {mydb.friends.city, mydb.friends.name})
This will return every friend you have, sorted first by city and then their name.
Indexes and Types
The sheets we’ve defined thus far are very simple, but you can take more control over the process if you need to. For example, you may assign default values and types to columns, and the DB package will attempt to coerce them as appropriate. To do that, you change your db:create() call as:
<lua> db:create("people", {
friends={"name", "city", "notes"},
enemies={
name="",
city="",
notes="",
enemied="",
kills=0
}
}) </lua>
This is almost the same as the original definition, but we’ve defined that our first four fields are strings with a default value of blank, and the new kills field which is an integer that starts off at 0. The only way to set a datatype is to set a default value at this time.
Please note, beneath the DB package is SQLite, and SQLite is very data-type neutral. It doesn’t really care very much if you break those rules and put an integer in a string field or vice-versa, but the DB package will — to a limited degree — attempt to convert as appropriate, especially for the operations that work on numbers.
You may also create both standard and unique indexes. A unique index enforces that a certain criteria can only happen once in the sheet. Now, before you go and make indexes, pause and consider. There is no right or wrong answer when it comes to what to index: it depends on what kind of queries you do regularly. If in doubt, don’t make an index. Indexes will speed up reading data from the sheet, but they will slow down writing data.
To add an index, pass either the _index or _unique keys in the table definition. An example:
<lua> db:create("people", {
friends={"name", "city", "notes"},
enemies={
name="",
city="",
notes="",
enemied="",
kills=0,
_index = { "city" },
_unique = { "name" }
}
}) </lua>
You can also create compound indexes, which is a very advanced thing to do. One could be: _unique = { {"name", "city"} }
This would produce an effect that there could be only one "Bob" in Magnagora, but he and "Bob" in Celest could coexist happily.
Now, bear in mind: _index = { "name", "city"} creates two indexes in this sheet. One on the city field, one on the name field. But, _index = { {"name", "city"} } creates one index: on the combination of the two. Compound indexes help speed up queries which frequently scan two fields together, but don’t help if you scan one or the other.
The admonition against making indexes willy-nilly holds double for compound indexes: do it only if you really need to!
Uniqueness
As was specified, the _unique key can be used to create a unique index. This will make it so a table can only have one record which fulfills that index. If you use an index such as _unique = { "name" } then names must be unique in the sheet. However, if you use an index such as _unique = { {"name", "city"} } then you will allow more then one person to have the same name — but only one per city.
Now, if you use db:add() to insert a record which would violate the unique constraint, a hard error will be thrown which will stop your script. Sometimes that level of protection is too much, and in that case you can specify how the db layer handles violations.
There are three possible ways in which the layer can handle such violations; the default is to FAIL and error out.
You can also specify that the db layer should IGNORE any commands that would cause the unique constraint to be violated, or the new data should REPLACE the existing row.
For example:
<lua> db:add(mydb.enemies, {name="Bob", city="Sacramento"}) db:add(mydb.enemies, {name="Bob", city="San Francisco"}) </lua>
With the name field being declared to be unique, these two commands can’t succeed normally. The first db:add() will create a record with the name of Bob, and the second would cause the uniqueness of the name field to be violated. With the default behavior (FAIL), the second db:add() call will raise an error and halt the script.
If you want the IGNORE behavior, the second command will not cause any errors and it will simply fail silently. Bob’s city will still be Sacramento.
With the REPLACE behavior, the second command will cause its data to completely overwrite and replace the first record. Bob’s city will now be San Francisco.
A word of caution with REPLACE, given:
<lua> db:add(mydb.enemies, {name="Bob", city="Sacramento", notes="This is something."}) db:add(mydb.enemies, {name="Bob", city="San Francisco"}) </lua>
With the REPLACE behavior, the second record will overwrite the first-- but the second record does not have the notes field set. So Bob will now not have any notes. It doesn’t -just- replace existing fields with new ones, it replaces the entire record.
To specify which behavior the db layer should use, add a _violations key to the table definition:
<lua> db:create("people", {
friends={"name", "city", "notes"},
enemies={
name="",
city="",
notes="",
enemied="",
kills=0,
_index = { "city" },
_unique = { "name" },
_violations = "IGNORE"
}
}) </lua>
Note that the _violations behavior is sheet-specific. Timestamps
In addition to strings and floats, the db module also has basic support for timestamps. In the database itself this is recorded as an integer (seconds since 1970) called an epoch, but you can easily convert them to strings for display, or even time tables to use with Lua’s built-in time support.
The most common use for the Timestamp type is where you want the database to automatically record the current time whenever you insert a new row into a sheet. The following example illustrates that:
<lua> local mydb = db:create("combat_log",
{
kills = {
name = "",
area = "",
killed = db:Timestamp("CURRENT_TIMESTAMP"),
_index = { {"name", "killed"} }
}
}
)
db:add(mydb.kills, {name="Drow", area="Undervault"})
results = db:fetch(mydb.kills) display(results) </lua>
The result of that final display would show you this on a newly created sheet:
<lua> table {
1: table {
'_row_id': 1
'area': 'Undervault'
'name': 'Drow'
'killed': table {
'_timestamp': 1264317670
}
}
} </lua>
As you can see from this output, the killed fields contains a timestamp-- and that timestamp is stored as an epoch value. For your convienance, the db.Timestamp type offers three functions to get the value of the timestamp in easy formats. They are as_string, as_number and as_table, and are called on the timestamp value itself.
The as_number function returns the epoch number, and the as_table function returns a time table. The as_string function returns a string representation of the timestamp, with a default format of "%m-%d-%Y %H:%M:%S". You can override this format to anything you would like. Details of what you can do with epoch values, time tables, and what format codes you can use are specified in the Lua manual at: http://www.lua.org/pil/22.1.html for the Lua date/time functions.
A quick example of the usage of these functions is:
<lua> results = db:fetch(mydb.kills) for _, row in ipairs(results) do
echo("You killed " .. row.name .. " at: " .. row.killed:as_string() .."\n")
end </lua>
Deleting
The db:delete function is used to delete rows from the sheet. It takes two arguments, the first being the sheet you are deleting and the second a query string built using the same functions used to build db:fetch() queries.
For example, to delete all your enemies in the city of Magnagora, you would do:
db:delete(mydb.enemies, db:eq(mydb.enemies.city, "Magnagora"))
Be careful in writing these! You may inadvertantly wipe out huge chunks of your sheets if you don’t have the query parameters set just to what you need them to be. Its advised that you first run a db:fetch() with those parameters to test out the results they return.
As a convienence, you may also pass in a result table that was previously retrieved via db:fetch and it will delete only that record from the table. For example, the following will get all of the enemies in Magnagora, and then delete the first one:
results = db:fetch(mydb.enemies, db:eq(mydb.enemies.city, "Magnagora"))
db:delete(mydb.enemies, db:eq(mydb.enemies._row_id, results[1]._row_id))
That is equivelent to:
db:delete(mydb.enemies, results[1])
You can even pass a number directly to db:delete if you know what _row_id you want to purege.
A final note of caution: if you want to delete all the records in a sheet, you can do so by only passing in the table reference. To try to protect you from doing this inadvertantly, you must also pass true as the query after:
db:delete(mydb.enemies, true)
Updating
If you make a change to a table that you have received via db:fetch(), you can save those changes back to the database by doing:
db:update(mydb.enemies, results[1])
A more powerful (and somewhat dangerous, be careful!) function to make changes to the database is db:set, which is capable of making sweeping changes to a column in every row of a sheet. Beware, if you have previously obtained a table from db:fetch, that table will NOT represent this change.
The db:set() function takes two arguments: the field to change, the value to set it to, and the db:fetch() like query to indicate which rows should be affected. If you pass true as the last argument, ALL rows will be changed.
To clear out the notes of all of our friends in Magnagora, we could do:
db:set(mydb.friends.notes, "", db:eq(mydb.friends.notes, "Magnagora"))
Be careful in writing these!
Transactions
As was specified earlier, by default the db module commits everything immediately whenever you make a change. For power-users, if you would like to control transactions yourself, the following functions are provided on your database instance:
local mydb = db:get_database("my_database")
mydb._begin()
mydb._commit()
mydb._rollback()
mydb._end()
Once you issue a mydb._begin() command, autocommit mode will be turned off and stay off until you do a mydb._end(). Thus, if you want to always use transactions explicitly, just put a mydb._begin() right after your db:create() and that database will always be in manual commit mode.
Viewing and editing the database contents
A good tool to view and edit the database contents in raw form is SQlite Sorcerer (free and available for Linux, Windows, Mac).