This a full listing of the current triggers/aliases/display
Create Table
<triggers>
<trigger
enabled="y"
group="Inventory"
keep_evaluating="y"
match="[* logged in.]"
name="Table_Creation"
send_to="12"
sequence="99"
>
<send>DatabaseOpen ("db", GetInfo (66) .. "InventoryDB.sqlite", 6)
rc = DatabaseExec ("db", [[
CREATE TABLE IF NOT EXISTS items(
item_id INTEGER NOT NULL PRIMARY KEY autoincrement,
character_name TEXT NOT NULL,
description TEXT NOT NULL,
quantity INT DEFAULT 1
);
]])
DatabaseClose ("db") -- close it
</send>
</trigger>
</triggers>
Inserter
<triggers>
<trigger
enabled="y"
expand_variables="y"
match="You get *."
send_to="12"
sequence="100"
>
<send>require "tprint"
DatabaseOpen ("db", GetInfo (66) .. "InventoryDB.sqlite", 6)
-- put some data into the database
rc = DatabaseExec ("db",
[[
UPDATE items
SET quantity = quantity + 1
WHERE character_name = '@Current_Character'
AND description = '%1';
]])
-- finished with the statement
DatabaseFinalize ("db")
DatabaseClose ("db") -- close it</send>
</trigger>
</triggers>
Deleter
<triggers>
<trigger
enabled="y"
expand_variables="y"
match="You drop *."
send_to="12"
sequence="100"
>
<send>require "tprint"
DatabaseOpen ("db", GetInfo (66) .. "InventoryDB.sqlite", 6)
-- put some data into the database
rc = DatabaseExec ("db",
[[
UPDATE items
SET quantity = quantity - 1
WHERE character_name = '@Current_Character'
AND description = '%1';
]])
-- finished with the statement
DatabaseFinalize ("db")
-- put some data into the database
rc = DatabaseExec ("db",
[[
DELETE FROM items
WHERE character_name = '@Current_Character'
AND description = '%1'
AND quantity <= 0;
]])
-- finished with the statement
DatabaseFinalize ("db")
DatabaseClose ("db") -- close it</send>
</trigger>
</triggers>
Display Call
<aliases>
<alias
name="Inventory_Display"
match="database"
enabled="y"
group="Inventory"
send_to="12"
sequence="100"
>
<send>require "tprint"
DatabaseOpen ("db", GetInfo (66) .. "InventoryDB.sqlite", 6)
-- prepare a query
DatabasePrepare ("db", "SELECT character_name, description, quantity FROM items ORDER BY character_name")
-- prints the header
print (string.format ("%-15s %-65s %-30s", " Character", " Item", "Quantity"))
print ("+------------------------------------------------------------------------------------------+")
-- execute to get the first row
rc = DatabaseStep ("db") -- read first row
-- now loop, displaying each row, and getting the next one
while rc == 100 do
print ("")
names = DatabaseColumnText ("db", 1)
items = DatabaseColumnText ("db", 2)
quant = DatabaseColumnText ("db", 3)
--print (" ", names, " ", items)
print (string.format ("%-1s %-14s %-64s %-29s", " ", names, items, quant))
rc = DatabaseStep ("db") -- read next row
end -- while loop
-- finished with the statement
DatabaseFinalize ("db")
DatabaseClose ("db") -- close it</send>
</alias>
</aliases>
Output
Character Item Quantity
+------------------------------------------------------------------------------------------+
Artemis a scorched length of bamboo 1
Artemis a small brass lantern 3
Artemis a steel-shod quarterstaff 3
Artemis a jester's rod 1
Artemis a thorned staff 3
Now that actual display was made up but thats how it displays. With the current triggers from above when I drop an item it reduces the quantity by 1, and if the new quantity is 0 or less, it deletes it from the table. If I pick up another "a thorned staff" it'll update the quantity to 4. I haven't looked at using SELECT too much just yet, but will be working on that soon. |