Gammon Forum : MUSHclient : Lua : Keeping track of items.

Register forum user name Search FAQ

Gammon Forum

Entire forum

MUSHclient

Lua

Keeping track of items.

Keeping track of items.

It is now over 60 days since the last post. This thread is closed. Refresh page

Pages: 1 2 3

Posted by

Nick Gammon Australia (22,985 posts)

bio Forum Administrator

Date

Reply #15 on Sun 24 Aug 2014 10:34 PM (UTC)

Message

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.

- Nick Gammon

www.gammon.com.au, www.mushclient.com

Posted by Panaku (34 posts) bio

Date Reply #16 on Sun 24 Aug 2014 11:02 PM (UTC)

Message

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.

Posted by Panaku (34 posts) bio

Date Reply #17 on Sun 24 Aug 2014 11:02 PM (UTC)

Message

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.

Posted by Panaku (34 posts) bio

Date Reply #18 on Mon 25 Aug 2014 12:42 AM (UTC)

Message

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.

Posted by Nick Gammon Australia (22,985 posts) bio Forum Administrator

Date Reply #19 on Mon 25 Aug 2014 04:37 AM (UTC)

Message

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>

Template:pasting

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

- Nick Gammon

www.gammon.com.au, www.mushclient.com

Posted by Panaku (34 posts) bio

Date Reply #20 on Mon 25 Aug 2014 05:17 AM (UTC)

Message

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

Posted by

Nick Gammon Australia (22,985 posts)

bio Forum Administrator

Date

Reply #21 on Mon 25 Aug 2014 06:09 AM (UTC)

Message

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

www.gammon.com.au, www.mushclient.com

Posted by

Nick Gammon Australia (22,985 posts)

bio Forum Administrator

Date

Reply #22 on Mon 25 Aug 2014 06:10 AM (UTC)

Message

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.

- Nick Gammon

www.gammon.com.au, www.mushclient.com

Posted by Panaku (34 posts) bio

Date Reply #23 on Mon 25 Aug 2014 08:32 AM (UTC)

Message

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

Posted by Nick Gammon Australia (22,985 posts) bio Forum Administrator

Date Reply #24 on Mon 25 Aug 2014 08:20 PM (UTC)

Message

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

www.gammon.com.au, www.mushclient.com

Posted by

Nick Gammon Australia (22,985 posts)

bio Forum Administrator

Date

Reply #25 on Mon 25 Aug 2014 08:38 PM (UTC)

Message

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

www.gammon.com.au, www.mushclient.com

Posted by Nick Gammon Australia (22,985 posts) bio Forum Administrator

Date Reply #26 on Mon 25 Aug 2014 08:51 PM (UTC)

Message

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

- Nick Gammon

www.gammon.com.au, www.mushclient.com

Posted by

Panaku (34 posts)

Date

Reply #27 on Mon 25 Aug 2014 10:03 PM (UTC)

Message

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.

Posted by Panaku (34 posts) bio

Date Reply #28 on Tue 26 Aug 2014 12:14 AM (UTC)

Message

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.

Posted by Nick Gammon Australia (22,985 posts) bio Forum Administrator

Date Reply #29 on Tue 26 Aug 2014 04:03 AM (UTC)

Message

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

- Nick Gammon

www.gammon.com.au, www.mushclient.com

The dates and times for posts above are shown in Universal Co-ordinated Time (UTC).

To show them in your local time you can join the forum, and then set the 'time correction' field in your profile to the number of hours difference between your location and UTC time.

64,975 views.

This is page 2, subject is 3 pages long: 1 2 3

It is now over 60 days since the last post. This thread is closed. Refresh page

Go to topic: Search the forum

Quick links: MUSHclient. MUSHclient help. Forum shortcuts. Posting templates. Lua modules. Lua documentation.

Information and images on this site are licensed under the Creative Commons Attribution 3.0 Australia License unless stated otherwise.

Comments to: Gammon Software support
Forum RSS feed ( https://gammon.com.au/rss/forum.xml )