Keeping track of items.

Posted by Panaku on Sun 24 Aug 2014 05:58 AM — 31 posts, 108,544 views.

Panaku Sun 24 Aug 2014 05:58 AM #0

Was gone for a while handing personal things, but I'm back and ready to get this working. I'm at the same point I was when I left off. There is an issue in the MUD I'm playing and guild/faction housing has been removed for the time being which means I have what is best described as 'bank' characters. I however have thousands of items spread across about a hundred characters with no easy way to keep track of who has what. So I'm trying to get a script working that will record what item is picked up or dropped and on which characters. Anyone have an suggestion?

Nick Gammon Australia Forum Administrator Sun 24 Aug 2014 06:10 AM #1

Are these logged on from the same "world" file?

Anyway, a SQLite3 database is probably the safest. See the pages about databases and SQL:

http://www.gammon.com.au/db
http://www.gammon.com.au/sql

Depending on how "good" you want your data to be you could have one simple table, or a number of tables.

A simple table (effectively like a spreadsheet) could record something like:

Character name (that owns the item)
Item description
Quantity

That could be fine if you have an automated way of keeping track of things, otherwise you might record it once as "sword, bronze" and another time as "bronze sword". This may or may not matter.

Then a simple query could locate instances of (say) swords, and list who is holding them.

You would obviously want to keep track of when you got an item (eg. a mob drop), if you sold it, or if you transferred it.

Panaku Sun 24 Aug 2014 06:24 AM #2

They're all from the same world file, there is about 50 characters on my account, each of them with a host of items being held for when needed. I don't care to much if its an overall table or individual ones for each character just as long as I don't have to log into each character in sequence and look over what they have every time I need something specific. I'll take a look at the links and see what I can come up with but I've never worked with databases before.

Panaku Sun 24 Aug 2014 06:55 AM #3

<triggers>
  <trigger
   enabled="y"
   match="You get *."
   send_to="12"
   sequence="100"
  >
  <send>require "tprint"

DatabaseOpen ("db", GetInfo (66) .. "mytestdb.sqlite", 6)

rc = DatabaseExec ("db", [[
DROP TABLE IF EXISTS weapons;
CREATE TABLE weapons(
        weapon_id INTEGER NOT NULL PRIMARY KEY autoincrement,
        name  TEXT NOT NULL,
        damage INT default 10,
        weight REAL
      );
      ]])
      

-- put some data into the database
rc = DatabaseExec ("db", 
  [[
  INSERT INTO weapons (name, damage) VALUES ('%1', 70);
  ]])

-- prepare a query
DatabasePrepare ("db", "SELECT * from weapons ORDER BY name")

-- find the column names
names = DatabaseColumnNames ("db")
tprint (names)

-- 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 ("")
  values = DatabaseColumnValues ("db")
  tprint (values)

  rc = DatabaseStep ("db")  -- read next row

end -- while loop

-- finished with the statement
DatabaseFinalize ("db")

DatabaseClose ("db")  -- close it</send>
  </trigger>
</triggers>

Using that I'm having items I get added to the weapons database, and using this alias I'm calling the database

<aliases>
  <alias
   match="database"
   enabled="y"
   send_to="12"
   sequence="100"
  >
  <send>require "tprint"

DatabaseOpen ("db", GetInfo (66) .. "mytestdb.sqlite", 6)    

-- prepare a query
DatabasePrepare ("db", "SELECT * from weapons ORDER BY name")

-- find the column names
names = DatabaseColumnNames ("db")
tprint (names)

-- 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 ("")
  values = DatabaseColumnValues ("db")
  tprint (values)

  rc = DatabaseStep ("db")  -- read next row

end -- while loop

-- finished with the statement
DatabaseFinalize ("db")

DatabaseClose ("db")  -- close it</send>
  </alias>
</aliases>

Which is working, I've noticed the trigger is removing the table then recreating it, which isn't doing much good. But I'm only taking in so much so fast about how this is all working so I've yet to edit the example too much.

Panaku Sun 24 Aug 2014 06:57 AM #4

I've completely removed the section --

rc = DatabaseExec ("db", [[
 DROP TABLE IF EXISTS weapons;
 CREATE TABLE weapons(
        weapon_id INTEGER NOT NULL PRIMARY KEY autoincrement,
        name  TEXT NOT NULL,
        damage INT default 10,
        weight REAL
      );
      ]])

and it's recording everything to the weapons table now as I wanted, just have to look how to remove weapons from the table when I drop them/sell them/give them away. Then I need to make it log which character an item is on.

Nick Gammon Australia Forum Administrator Sun 24 Aug 2014 07:07 AM #5

In your case only one table, I think, not one per character. I was really thinking of a table for characters, and a table for item names, but that is probably over-complicating it.

Stick with one table.

It might look like this:


CREATE TABLE items(
        item_id INTEGER NOT NULL PRIMARY KEY autoincrement,
        character_name TEXT NOT NULL,
        description  TEXT NOT NULL,
        quantity INT DEFAULT 1
      );

Then add (INSERT) a new item with the character name being the current toon, the description being the item, and the quantity being the number (if relevant).

To remove items use delete.

eg,


DELETE FROM items 
  WHERE character_name = 'Gandalf' 
  AND description = 'yellow wand';

Use this with caution because this would delete all yellow wands owned by Gandalf, not just one of them.

This can all be made to work but you might want to read up on SQL theory a bit, to understand better what you are doing.

Panaku Sun 24 Aug 2014 07:29 AM #6

Modified the original trigger to this --

<triggers>
  <trigger
   enabled="y"
   expand_variables="y"
   match="You get *."
   send_to="12"
   sequence="100"
  >
  <send>require "tprint"

DatabaseOpen ("db", GetInfo (66) .. "mytestdb.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
      );
      ]])

-- put some data into the database
rc = DatabaseExec ("db", 
  [[
  INSERT INTO items (character_name, description) VALUES ('@Current_Character', '%1');
  ]])

-- prepare a query
DatabasePrepare ("db", "SELECT * from items ORDER BY character_name")

-- find the column names
names = DatabaseColumnNames ("db")
tprint (names)

-- 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 ("")
  values = DatabaseColumnValues ("db")
  tprint (values)

  rc = DatabaseStep ("db")  -- read next row

end -- while loop

-- finished with the statement
DatabaseFinalize ("db")

DatabaseClose ("db")  -- close it</send>
  </trigger>
</triggers>

And the testing alias to --

<aliases>
  <alias
   match="database"
   enabled="y"
   send_to="12"
   sequence="100"
  >
  <send>require "tprint"

DatabaseOpen ("db", GetInfo (66) .. "mytestdb.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
      );
      ]])

-- prepare a query
DatabasePrepare ("db", "SELECT * from items ORDER BY character_name")

-- find the column names
names = DatabaseColumnNames ("db")
tprint (names)

-- 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 ("")
  values = DatabaseColumnValues ("db")
  tprint (values)

  rc = DatabaseStep ("db")  -- read next row

end -- while loop

-- finished with the statement
DatabaseFinalize ("db")

DatabaseClose ("db")  -- close it</send>
  </alias>
</aliases>

This is now listing the database like so --

1=13
2="Artemis"
3="a small brass lantern"
4=1

1=14
2="Flynn"
3="a welding mask"
4=1

Would be able to list it in a easier to read fashion such as --

Character          Item
------------------------------------------
Artemis            a small brass lantern
Flynn              a welding mask

Nick Gammon Australia Forum Administrator Sun 24 Aug 2014 07:50 AM #7

You only have to create the database once, ever. So get rid of the CREATE TABLE.

Quote:

Would be able to list it in a easier to read fashion such as --

Well you have the data there, just display it the way you want to.

Panaku Sun 24 Aug 2014 08:07 AM #8

I set all instance of the CREATE TABLE to "CREATE TABLE IF NOT EXISTS" for two reasons. One being I plan on giving this inventory tracker to the other players because they're in the same situation I'm in, ton of items and now where in game to store it. And they won't have the table or know how to create it themselves. The other reason being I have an alias set up the delete the table so I can do some debugging while I'm working everything out.

Is there another reason to remove the CREATE TABLE? My brain tells me if I do remove it then the database wouldn't ever work unless you manually made the table

Panaku Sun 24 Aug 2014 09:15 AM #9

For testing purposes I've been using this alias to check the database --

<aliases>
  <alias
   match="database"
   enabled="y"
   send_to="12"
   sequence="100"
  >
  <send>require "tprint"

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
      );
      ]])

-- prepare a query
DatabasePrepare ("db", "SELECT character_name, description FROM items ORDER BY character_name")

-- find the column names
names = DatabaseColumnNames ("db")
-- ***DROPPED OUT*** tprint (names)

-- 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 ("")
  values = DatabaseColumnValues ("db")
  tprint (values)
  rc = DatabaseStep ("db")  -- read next row

end -- while loop

-- finished with the statement
DatabaseFinalize ("db")

DatabaseClose ("db")  -- close it</send>
  </alias>
</aliases>

This is displaying


1="Aerin"
2="a copper medallion"

1="Aerin"
2="black muslin wrappings"

1="Artemis"
2="a scorched length of bamboo"

1="Artemis"
2="a small brass lantern"

1="Renald"
2="a bo staff"

1="Renald"
2="a wooden mallet"

Ultimately I was able to remove a bit of useless information but its still not being displayed very smoothly, and reading over both of the links you originally offered didn't seem to help me too much though I was able to change how it was displaying from previously to how it is now which is an improvement. Being able to display it side by side like I mentioned previously would be the best option I think, but even if I can drop out the numbers and quotes that would be good.

Another issue I think I'm going to run into is a how this particular MUD tracks multiples of items. Instead of your traditional "(2) small brass lantern" it displays "two small brass lanterns" Which means with the current DELETE setup it'll either no erase anything if I had two lanterns logged but only dropped one, or if I pick them up separately so they show up as two different items dropping both deletes nothing and dropping one deletes both (this you mentioned). I was looking over http://www.gammon.com.au/forum/?id=9650 and came across the section about events and was wondering if those could be incorporated to tack on an ID number to each item so that when I delete it can delete only one item accordingly.

Also, thank you for your quick replies and all the help you've given so far.

Nick Gammon Australia Forum Administrator Sun 24 Aug 2014 10:55 AM #10

Panaku said:

Is there another reason to remove the CREATE TABLE? My brain tells me if I do remove it then the database wouldn't ever work unless you manually made the table

Just trying to save time, as otherwise every time you do something you are trying to create a table which probably exists. You could put the create table "if not exists" somewhere like in the world "on connect" part rather than for every trigger.

Quote:

... but its still not being displayed very smoothly ...

Read up a bit on string.format. You just need to take the data (from the "values" table) and display it in a single line. That is pretty straightforward. I could tell you but you will learn more by attempting it for yourself.

Panaku Sun 24 Aug 2014 11:26 AM #11

These are the new triggers and aliases I have in use and its coming along pretty smoothly.

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)

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
      );
      ]])

-- drop some data into the database
rc = DatabaseExec ("db", 
  [[
  DELETE FROM items 
  WHERE character_name = '@Current_Character' 
  AND description = '%1';
  ]])

-- finished with the statement
DatabaseFinalize ("db")

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)

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
      );
      ]])

-- put some data into the database
rc = DatabaseExec ("db", 
  [[
  INSERT INTO items (character_name, description) VALUES ('@Current_Character', '%1');
  ]])

-- finished with the statement
DatabaseFinalize ("db")

DatabaseClose ("db")  -- close it</send>
  </trigger>
</triggers>

Database Viewer

<aliases>
  <alias
   match="database"
   enabled="y"
   send_to="12"
   sequence="100"
  >
  <send>require "tprint"

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
      );
      ]])

-- prepare a query
DatabasePrepare ("db", "SELECT character_name, description FROM items ORDER BY character_name")

-- find the column names
names = DatabaseColumnNames ("db")
-- ***DROPPED OUT*** tprint (names)

-- prints the header
print ("  Character                Item  ")
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)
  print (" ", names, "          ", items)
  rc = DatabaseStep ("db")  -- read next row

end -- while loop

-- finished with the statement
DatabaseFinalize ("db")

DatabaseClose ("db")  -- close it</send>
  </alias>
</aliases>

Database Output

  Character                Item  
+--------------------------------------+

  Aerin            a copper medallion

  Aerin            black muslin wrappings

  Artemis            a scorched length of bamboo

  Artemis            a small brass lantern

  Ibauthwyn            a lump of indigo

  Renald            a bo staff

  Renald            a wooden mallet

As far as the output goes there are only a few personal changes I'd like to make so I'm going to put those on the back burner and focus on the functionality.

First off, I have a trigger running already on world connect to open up my second account and can add the CREATE TABLE to that trigger, do I need to run the database open/finalize/close on that trigger?

The second issue is deleting only one instance of an item of certain characters. I thought about adding in triggers for when "You get two small brass lanterns." it would do a loop 2x and insert "a small brass lantern" each time but that would require a lot of work to differentiate between items that will be listed with a/an.

Let's say my inventory is "You are carrying a thorned staff, a small brass lantern, a thorned staff, a scorched length of bamboo, a small brass lantern, a steel-shod
quarterstaff, a thorned staff, two steel-shod quarterstaves, and a small brass lantern." and I drop a thorned staff (just one of them), is there a way to delete only one of the 3 instead of all 3?

Nick Gammon Australia Forum Administrator Sun 24 Aug 2014 07:50 PM #12

First, you can make your list line up neatly by using a length specifier.

eg.


print (string.format ("%-20s %-20s", "foo", "bar"))

Output:


foo                  bar

Quote:

... is there a way to delete only one of the 3 instead of all 3?

Yes, update the quantity. *


UPDATE items 
  SET quantity = quantity - 1
  WHERE character_name = 'Gandalf' 
  AND description = 'yellow wand';

Then you might delete it if the quantity is zero. Or do that occasionally later (just don't show items with <= 0 quantity in your listing).

* And if you get a new one, add one to the quantity rather than subtracting one.

Quote:
do I need to run the database open/finalize/close on that trigger?

You should open the database once - this is a slow operation. You can omit closing it, as MUSHclient will close it eventually.

Or do this:

* World connect: open database and create tables if necessary

* World disconnect: close database

The finalize is part of Prepare/Finalize pair. If you do a DatabasePrepare you should do a DatabaseFinalize.

I still think you should move all those opens/creates into a "on connect" world handler like I mention above. They just clutter things up and hide what you are doing.

If you use a script file just make a world connect and world disconnect handler. Their names go into the scripting configuration tab.

eg.


function OnWorldConnect ()
  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
      );
      ]])
end -- OnWorldConnect

function OnWorldDisconnect ()
  DatabaseClose ("db")  -- close it
end -- OnWorldDisconnect

Then take those lines out of your triggers and aliases.

Nick Gammon Australia Forum Administrator Sun 24 Aug 2014 09:02 PM #13

Quote:

  INSERT INTO items (character_name, description) VALUES ('@Current_Character', '%1');

Be cautious here of character names or descriptions that contain quotes (eg. "Nick's sword"). That will throw an error here (and you are not detecting errors).

Better is to "fix" the SQL by replacing quotes with two quotes. An example is on one of those linked pages:


-- Quote the argument, replacing single quotes with two lots of single quotes.
-- If nil supplied, return NULL (not quoted).
function fixsql (s)
  if s then
    return "'" .. (string.gsub (s, "'", "''")) .. "'"
  else
    return "NULL"
  end -- if
end -- fixsql

Now you can replace:


rc = DatabaseExec ("db", 
  [[
  INSERT INTO items (character_name, description) VALUES ('@Current_Character', '%1');
  ]])

With:


rc = DatabaseExec ("db", string.format (
  [[
  INSERT INTO items (character_name, description) VALUES (%%s, %%s);
  ]], 
  fixsql (GetVariable ("Current_Character")), 
  fixsql ("%1"))
)

if rc ~= sqlite3.OK then
  ColourNote ("red", "", "Error adding item %1: " .. DatabaseError("db"))
end -- if

Panaku Sun 24 Aug 2014 09:32 PM #14

I had actually updated everything a few hours after my last post and came up with something very similar to these --

Table Creator(Current)

<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 0
      );
      ]])

DatabaseClose ("db")  -- close it

</send>
  </trigger>
</triggers>

Inserter(Current)

<triggers>
  <trigger
   enabled="y"
   expand_variables="y"
   group="Inventory"
   match="You get *."
   name="Inserter"
   send_to="12"
   sequence="100"
  >
  <send>require "tprint"

DatabaseOpen ("db", GetInfo (66) .. "InventoryDB.sqlite", 6)

-- put some data into the database
rc = DatabaseExec ("db", 
  [[
  INSERT INTO items (character_name, description, quantity) VALUES ('@Current_Character', '%1', +1);
  ]])

-- finished with the statement
DatabaseFinalize ("db")

DatabaseClose ("db")  -- close it</send>
  </trigger>
</triggers>

Deleter(Not Current)

<triggers>
  <trigger
   enabled="y"
   expand_variables="y"
   group="Inventory"
   match="You drop *."
   name="Deleter"
   send_to="12"
   sequence="100"
  >
  <send>require "tprint"

DatabaseOpen ("db", GetInfo (66) .. "InventoryDB.sqlite", 6)

-- drop some data into the database
rc = DatabaseExec ("db", 
  [[
  DELETE FROM items 
  WHERE character_name = '@Current_Character' 
  AND description = '%1';
  ]])

-- finished with the statement
DatabaseFinalize ("db")

DatabaseClose ("db")  -- close it</send>
  </trigger>
</triggers>

Display

<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>

I dropped out the create table from each individual trigger/alias and left it only to check when you first connect to the world and log in to your account.

As of right now everything is running as individual triggers/aliases and are not part of a single script file. When I tried to omit the open and close from each trigger/alias nothing was working.

I have since modified my output with the help of your string .format example and have something much pleasing to the eyes. I also adding in tracking of quantity when I get an item. I ran into a issue where using --

<triggers>
  <trigger
   enabled="y"
   expand_variables="y"
   group="Inventory"
   match="You get *."
   name="Inserter"
   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>

If the item isn't already in the table it can't be updated, but if I have the item added to the table everytime, it'll update the quantity and make listing for the same item. Is there a way to do "INSERT INTO IF NOT EXIST" or something like that. Also using the code --

UPDATE items 
  SET quantity = quantity - 1
  WHERE character_name = '@Current_Character' 
  AND description = '%1';

Wasn't changing any of the information that was being displayed.

There are only a few select items in the game that use single quotes, player names can't have them at all so I'll look into that fix after a bit.

Nick Gammon Australia Forum Administrator Sun 24 Aug 2014 10:34 PM #15

Quote:

Wasn't changing any of the information that was being displayed.

Are you displaying the quantity? Your last example did not.

I suggest you start checking return codes like I showed. If you have an error in your SQL it will silently fail.

Quote:

Is there a way to do "INSERT INTO IF NOT EXIST" or something like that.

You need to do a search (SELECT) to find if the item exists already. If it exists you can add or subtract one from the quantity. If not you need to insert it.

You should do that in your inserter anyway, otherwise you will get lots of similar items, rather than a single one with a larger quantity.

Panaku Sun 24 Aug 2014 11:02 PM #16

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 &lt;= 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.

Panaku Sun 24 Aug 2014 11:02 PM #17

I took your advice about quotes and changed my Inserter accordingly to this --

<triggers>
  <trigger
   expand_variables="y"
   group="Inventory"
   match="You get *."
   name="Inserter"
   send_to="12"
   sequence="100"
  >
  <send>-- Quote the argument, replacing single quotes with two lots of single quotes.
-- If nil supplied, return NULL (not quoted).
function fixsql (s)
  if s then
    return "'" .. (string.gsub (s, "'", "''")) .. "'"
  else
    return "NULL"
  end -- if
end -- fixsql

require "tprint"

DatabaseOpen ("db", GetInfo (66) .. "InventoryDB.sqlite", 6)

-- put some data into the database
rc = DatabaseExec ("db", string.format (
  [[
  INSERT INTO items (character_name, description, quantity) VALUES (%%s, %%s, %%s);
  ]], 
  fixsql (GetVariable ("Current_Character")), 
  fixsql ("%1"),
  fixsql ("+1"))
)

if rc ~= sqlite3.OK then
  ColourNote ("red", "", "Error adding item %1: " .. DatabaseError("db"))
end -- if

-- finished with the statement
DatabaseFinalize ("db")

DatabaseClose ("db")  -- close it</send>
  </trigger>
</triggers>

This fix is working and is actually how I managed to add in to add the base for every item into the database. I turned of the inserter at the top and turned this one one and picked up one of each item. If I use this trigger here to pick up multiple items it'll just create a new line for each item and display the quantity as one. I haven't figured out how to convert the quote fix from working with "INSERT INTO items" to working with "UPDATE items".

Overall the project is coming along a lot more smoothly than my last attempt though.

Panaku Mon 25 Aug 2014 12:42 AM #18

Attempting to check if the entry exists, this is one such example I've tried which is managing to do absolutely nothing.

IF EXISTS (SELECT * FROM items WHERE description = '%1')
    UPDATE items
    SET quantity = quantity + 1
    WHERE character_name = '@Current_Character'
    AND description = '%1'
ELSE
    INSERT INTO items (character_name, description, quantity) VALUES ('@Current_Character', '%1', +1);

I tried a few different setups none of which seemed to work.

Nick Gammon Australia Forum Administrator Mon 25 Aug 2014 04:37 AM #19

You need two steps. First a SELECT to see if the item is there. We may as well get the item ID at the time (the unique database row number) so we can use it in the UPDATE.

Failing that (no item found) we do an INSERT.


<triggers>
  <trigger
   custom_colour="2"
   enabled="y"
   expand_variables="y"
   group="Inventory"
   match="You get *."
   name="Inserter"
   send_to="12"
   sequence="100"
  >
  <send>

-- Quote the argument, replacing single quotes with two lots of single quotes.
-- If nil supplied, return NULL (not quoted).
function fixsql (s)
  if s then
    return "'" .. (string.gsub (s, "'", "''")) .. "'"
  else
    return "NULL"
  end -- if
end -- fixsql

-- see if item already there
DatabasePrepare ("db", 
  string.format ("SELECT item_id FROM items WHERE character_name = %%s AND description = %%s ",
                fixsql (GetVariable ("Current_Character")), 
                fixsql ("%1")))
    
-- find first row
local rc = DatabaseStep ("db")  -- read first row
local id = nil
if rc == sqlite3.ROW then
  id = DatabaseColumnValue ("db", 1)  -- get the ID of that row
elseif rc ~= sqlite3.OK and rc ~= sqlite3.DONE then
  ColourNote ("red", "", "Error finding item %1: " .. DatabaseError("db"))
end -- if

-- finished with the statement
DatabaseFinalize ("db")

-- if we found an existing item, update it

if id then
  rc = DatabaseExec ("db", string.format ("UPDATE items SET quantity = quantity + 1 WHERE item_id = %i", id))
else
  -- otherwise add to the database
  rc = DatabaseExec ("db", string.format ("INSERT INTO items (character_name, description, quantity) VALUES (%%s, %%s, 1)", 
    fixsql (GetVariable ("Current_Character")), 
    fixsql ("%1")))
end -- if

if rc ~= sqlite3.OK then
  ColourNote ("red", "", "Error adding item %1: " .. DatabaseError("db"))
end -- if

</send>
  </trigger>
</triggers>

For advice on how to copy the above, and paste it into MUSHclient, please see Pasting XML.

Panaku Mon 25 Aug 2014 05:17 AM #20

You get a thorned staff.
Error finding item a thorned staff: database id not found
Error adding item a thorned staff: database id not found

Not that I understood most of what you provided did, at least not the id parts

Nick Gammon Australia Forum Administrator Mon 25 Aug 2014 06:09 AM #21

I took out the DatabaseOpen and DatabaseClose because I really think you should do that once only.

Opening and closing the database every time is very wasteful.

If the database is open, it will work.

See reply #12.

http://www.gammon.com.au/forum/?id=12564&reply=12#reply12

Nick Gammon Australia Forum Administrator Mon 25 Aug 2014 06:10 AM #22

Panaku said:

Not that I understood most of what you provided did, at least not the id parts

Every item in the database has an "id" (unique key). Once you select (look up) something, you can use that ID to change that thing, rather than some other thing. Think of it like a Social Security number, passport number, credit card number, etc. It is unique.

Panaku Mon 25 Aug 2014 08:32 AM #23

Once I get all the issues worked out and have it in full working order I'll be moving it over into a single script file at which point I'll incorporate what you suggested back in reply #12, but until then it has been giving me problems if I don't include the open and close calls.

For the time being I think I just have one little thing to change before this is virtually finished then it's onto adding in additional triggers to catch if I'm given items or give the m to someone else but that'll all mostly be copy and paste work.

I took what you gave me for the Inserter and reworked it to work with my deleter partially.

<triggers>
  <trigger
   enabled="y"
   expand_variables="y"
   match="You drop *."
   send_to="12"
   sequence="100"
  >
  <send>DatabaseOpen ("db", GetInfo (66) .. "InventoryDB.sqlite", 6)

-- Quote the argument, replacing single quotes with two lots of single quotes.
-- If nil supplied, return NULL (not quoted).
function fixsql (s)
  if s then
    return "'" .. (string.gsub (s, "'", "''")) .. "'"
  else
    return "NULL"
  end -- if
end -- fixsql

-- see if item already there
DatabasePrepare ("db", 
  string.format ("SELECT item_id FROM items WHERE character_name = %%s AND description = %%s ",
                fixsql (GetVariable ("Current_Character")), 
                fixsql ("%1")))
    
-- find first row
local rc = DatabaseStep ("db")  -- read first row
local id = nil
if rc == sqlite3.ROW then
  id = DatabaseColumnValue ("db", 1)  -- get the ID of that row
elseif rc ~= sqlite3.OK and rc ~= sqlite3.DONE then
  ColourNote ("red", "", "Error finding item %1: " .. DatabaseError("db"))
end -- if

-- finished with the statement
DatabaseFinalize ("db")

-- Update the quantity of the item dropped
rc = DatabaseExec ("db", string.format ("UPDATE items SET quantity = quantity - 1 WHERE item_id = %i", id))

if rc ~= sqlite3.OK then
  ColourNote ("red", "", "Error adding item %1: " .. DatabaseError("db"))
end -- if

-- delete some data from 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>

This appropriately lowers the quantity for items that include a quote, but I don't quite no what to reword it to work with the delete call. I tried this here --


rc = DatabaseExec ("db", string.format (
  [[
  DELETE FROM items (character_name, description, quantity) VALUES (%%s, %%s, %%s);
  ]], 
  fixsql (GetVariable ("Current_Character")), 
  fixsql ("%1"),
  fixsql ("<=0"))
)

which produced this errors 3 times in a row then stopped giving the error but still won't delete

Run-time error
World: Blood Dusk 1
Immediate execution
[string "Trigger: "]:34: bad argument #2 to 'format' (number expected, got nil)
stack traceback:
        [C]: in function 'format'
        [string "Trigger: "]:34: in main chunk

Nick Gammon Australia Forum Administrator Mon 25 Aug 2014 08:20 PM #24

What you had there is not the syntax for deleting.

It is something like:


DELETE FROM table WHERE condition

I've modified what you had to return the ID and quantity (and also check the quantity is one or more).

Now, based on the quantity it either subtracts one, or if you only have one, it deletes it.


<triggers>
  <trigger
   enabled="y"
   expand_variables="y"
   match="You drop *."
   send_to="12"
   sequence="100"
  >
  <send>

DatabaseOpen ("db", GetInfo (66) .. "InventoryDB.sqlite", 6)

-- Quote the argument, replacing single quotes with two lots of single quotes.
-- If nil supplied, return NULL (not quoted).
function fixsql (s)
  if s then
    return "'" .. (string.gsub (s, "'", "''")) .. "'"
  else
    return "NULL"
  end -- if
end -- fixsql

-- see if item already there
DatabasePrepare ("db", 
  string.format ("SELECT item_id, quantity FROM items WHERE character_name = %%s AND description = %%s AND quantity &gt;= 1",
                fixsql (GetVariable ("Current_Character")), 
                fixsql ("%1")))
    
-- find first row
local rc = DatabaseStep ("db")  -- read first row
local id = nil
if rc == sqlite3.ROW then
  id = DatabaseColumnValue ("db", 1)  -- get the ID of that row
  quantity = tonumber (DatabaseColumnValue ("db", 2))  -- get the quantity of that row
elseif rc ~= sqlite3.OK and rc ~= sqlite3.DONE then
  ColourNote ("red", "", "Error finding item %1: " .. DatabaseError("db"))
end -- if

-- finished with the statement
DatabaseFinalize ("db")

-- if we found an existing item, update it

if id then
  -- last one? delete from database
  if quantity == 1 then
    rc = DatabaseExec ("db", string.format ("DELETE FROM items WHERE item_id = %i", id))
  else
    rc = DatabaseExec ("db", string.format ("UPDATE items SET quantity = quantity - 1 WHERE item_id = %i", id))
  end -- if
else
  ColourNote ("orange", "", "No item %1 found in the database")
end -- if

if rc ~= sqlite3.OK and rc ~= sqlite3.DONE then
  ColourNote ("red", "", "Error removing item %1: " .. DatabaseError("db"))
end -- if

DatabaseClose ("db")  -- close it

</send>
  </trigger>
</triggers>

Nick Gammon Australia Forum Administrator Mon 25 Aug 2014 08:38 PM #25

Quote:

Once I get all the issues worked out and have it in full working order I'll be moving it over into a single script file...

OK, well bear in mind that in places where I had %%s you need to change them to %s. This is because in the "send to script" the percent signs need to be doubled unless you are referring to wildcards.

Nick Gammon Australia Forum Administrator Mon 25 Aug 2014 08:51 PM #26

Quote:

SELECT character_name, description, quantity FROM items ORDER BY character_name

You can also order by description, which might be helpful, or maybe description first, to help find things:


SELECT character_name, description, quantity FROM items ORDER BY description, character_name

Panaku Mon 25 Aug 2014 10:03 PM #27

It's all working! Now just to do some tweaking and adding. Going to switch it over to a single script, run the open and close calls just once. I think I'm going to add in a new Column and sort by that which is location -- vault, inventory, pouch, backpack, etc.

Have to add in triggers to catch being given items, giving the m away, throwing them, and more as well.

Panaku Tue 26 Aug 2014 12:14 AM #28

Updated the entire to an .xml file and using it as a plugin. I couldn't seem to get this to work in the .xml file --

function OnWorldConnect ()
  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
      );
      ]])
end -- OnWorldConnect

function OnWorldDisconnect ()
  DatabaseClose ("db")  -- close it
end -- OnWorldDisconnect

Instead I just added in two triggers

<!-- Opens the database and creates the table when you log in to your account. -->
<triggers>
  <trigger
   enabled="y"
   group="Inventory"
   keep_evaluating="y"
   match="[* logged in.]"
   name="Table_Creation"
   send_to="12"
   sequence="1"
  >
  <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
      );
      ]])

</send>
  </trigger>
</triggers>


<!-- Closes the database when you log out of the game. -->
<triggers>
  <trigger
   enabled="y"
   group="Inventory"
   match="Disconnecting."
   name="Closer"
   send_to="12"
   sequence="1"
  >
  <send>
  DatabaseClose ("db")  -- close it
</send>
  </trigger>
</triggers>

and I removed all the open/close calls from the other triggers and aliases and it's working no problem so far. Would be happy to provide the final piece for anyone to use for their own needs if they want it.

Nick Gammon Australia Forum Administrator Tue 26 Aug 2014 04:03 AM #29

In plugins the connect and disconnect functions have to have fixed names as documented here:

http://www.gammon.com.au/scripts/doc.php?general=plugin_callbacks

Specifically:


function OnPluginConnect ()
end -- function

function OnPluginDisconnect ()
end -- function

Sounds cool, how about posting the whole thing so we can play with it?

You need to ensure that square brackets inside your post are not misinterpreted by "escaping" them.

To do this, copy the code (plugin) to the clipboard
Then use MUSHclient's Edit menu -> "Convert Clipboard Forum Codes" (Shift+Ctrl+Alt+Q)
Paste the converted code into the forum posting (between the [code] and [/code] tags).

Panaku Tue 26 Aug 2014 04:31 AM #30

I haven't added the function calls or the additional triggers yet, and I think I'm going to be adding a few more columns to the table as well. Below is the plugin as it is now, it works just as well as the triggers by themselves.

Wouldn't you know, the admin over hears what I'm working on and says that he isn't going to have the time right now to finish all the changes to the guild/faction system he wanted and is putting them back in the game making this virtually useless for the time being. HOWEVER! I'll just modify the set up to track when I put items into my vaults/treasure chests/cabinets and add in a location column instead of the character column. I'll probably make two tables total for this, the original one being for items on characters and the new table for Wealth Containers in my hall. All in all, once I get it finished and log everything into the database, I should never have to spend more than a few seconds finding needed items again.

The plugin exceeds the maximum character limit by quite a lot and while I don't really want to draw away from the forums here, I want to post it in two halves even less so here it is -- http://pastebin.com/DC0R4bxt