A key feature for effective automation and creation of AI systems is persisted SQL databases. The native support of sqlite databases puts CMUD ahead of most competing MUD clients in this regard.
Standard SQL language queries are fully supported as are the equivalent of an INSERT in the security-critical manner of parameters to avoid SQL injection attacks.
For MUDding purposes, there’s generally no need to look beyond the local file sqlite support. The native engine, used properly, scales well beyond the size of databases actually used in text games.
The following sample in zscript demonstrates following basic database operations:
- Create a new database
- Create a table with two text columns
- Insert a dummy record
- Retrieve the dummy record
- Insert a genuine record using security-safe methods
- Display the record using a realistic search
The example is intended to lead towards a script to automate/bot inventory handling such as selling commodity items. For that purpose, the key data are a short description, such as might appear in an inventory list, and a keyword to refer to it by.
In actual use, these steps will of course be separated. The creation of the database and table in particular are generally not something that will be done often.
//THIS DEMO MAY ONLY BE RUN ONCE: TO RUN IT AGAIN, YOU MUST DELETE THE
// testdb.db FILE IT CREATES IN THE DATA DIRECTORY. OTHERWISE, THE TABLE
// CREATION CALL WILL CAUSE A CMUD ERROR WHEN THE SQL STATEMENT FAILS
#SQLDB testdb.db
//creating the table doesn't return a query result
%sql("testdb.db","CREATE TABLE objkeywords (keyword TEXT, shortname TEXT);")
//insert a placeholder row so that we can later get a row to do more inserts
%sql("testdb.db","INSERT INTO objkeywords (keyword,shortname) VALUES ('','');")
//we can insert values directly as above, but if we get a handle on an existing row then we can can use safe functions to populate column values instead of having bug/security issues with characters that could be SQL escapes
#VAR row %sql(testdb,"SELECT * FROM objkeywords;")
//we can call this directly. It adds a row and causes row to point to the new row
@row.Insert
//syntax like @row.Item("keyword") = "hat" is rejected
%comset(row, "Item", "hat", "keyword")
%comset(row, "Item", "a hat that lets everyone know you aren't afraid of anything", "shortname")
@row.Post
//this shouldn't be a persistent variable
#UNVAR row
//retrieve all of the actual data rows and display them
#VAR row %sql(testdb,"SELECT * FROM objkeywords WHERE NOT keyword = ''")
#WHILE (!@row.Eof()) {#PRINT @row.Item("keyword") : @row.Item("shortname");#CALL @row.Next}
#UNVAR row
#SQLCLOSE "testdb.db"
This code is not intended to be used directly other than as a sample of how to use the sql and sqlite database functionality of CMUD.